public DataTable GetClient(string ClientID)
{
DBDatabase db;
db = base.Database;
DBQuery select;
select = DBQuery.SelectAll(TzAccount.Client.Table).From(TzAccount.Client.Table)
.WhereField(TzAccount.Client.Table, TzAccount.Client.ClientID.Name, Compare.Equals, DBConst.String(ClientID));
return(db.GetDatatable(select));
//DBExecQuery ex = DBQuery.Exec("").WithParamValue(null);
//db.ExecuteNonQuery(ex);
}
public DataTable GetSecurityGroup(string clientid, string groupID)
{
DBQuery select;
// DBComparison baseType= DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.BaseType .Name), DBConst.Int32(pBaseType));
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.ClientID.Name), DBConst.String(clientid));
DBComparison group = DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.SecurityGroupID.Name), DBConst.String(groupID));
select = DBQuery.SelectAll(TzAccount.SecurityGroup.Table).From(TzAccount.SecurityGroup.Table)
.WhereAll(client, group);
return(db.GetDatatable(select));
}
/// <summary>
///
/// </summary>
/// <param name="UserName"></param>
/// <param name="Password"></param>
/// <returns></returns>
public DataTable GetUserByEmail(string UserName)
{
DBDatabase db;
db = base.Database;
DBQuery select;
select = DBQuery.SelectAll(TzAccount.User.Table).From(TzAccount.User.Table)
.WhereField(TzAccount.User.Table, TzAccount.User.UserName.Name, Compare.Equals, DBConst.String(UserName));
return(db.GetDatatable(select));
}
public void Test_03_ScriptOfSelectCustomersWithOrdersOver500()
{
DBSelectQuery orderValue = DBQuery.Select()
.Sum(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue")
.And(DBFunction.IsNull("OrderID", DBConst.Const("No ID")))
.Field("O", "customerID")
.From("Orders").As("O")
.InnerJoin("Order Details").As("OD")
.On("OD", "OrderID", Compare.Equals, "O", "OrderID")
.GroupBy("O", "CustomerID");
DBQuery q = DBQuery.SelectTopN(10)
.Field("CUST", "CompanyName").As("Company Name")
.Field("CUST", "CustomerID").As("Customer ID")
.And(DBFunction.IsNull("CUST", "ContactName", DBConst.Const("No Contact"))).As("Contact")
.Sum("ORD", "OrderValue").As("Total Value")
.From("Customers").As("CUST")
.InnerJoin(orderValue).As("ORD")
.On("ORD", "CustomerID", Compare.Equals, "CUST", "CustomerID")
.WhereField("ORD", "OrderValue", Compare.GreaterThanEqual, DBConst.Const(500.0))
.GroupBy("CUST", "CompanyName").And("CUST", "CustomerID")
.OrderBy("Total Value", Order.Descending);
string sql = this.OutputSql(q, "SelectCustomersWithOrdersOver500", true);
string xml = this.OutputXML(q, "SelectCustomersWithOrdersOver500");
ValidateXml(xml, sql, "SelectCustomersWithOrdersOver500");
}
public static int GetNextID(string pEntityID, string conn, int LastIndex)
{
try
{
int iNext;
DataBase db = new DataBase();
db.InitDbs(conn);
DataTable dtResult = new DataTable();
DBQuery select;
DBComparison EntityID = DBComparison.Equal(DBField.Field("EntityID"), DBConst.String(pEntityID));
// DBConst dbclientid = DBConst.String("ClientID");
select = DBQuery.SelectAll("sys_Entity").From("sys_Entity")
.WhereAll(EntityID);
dtResult = db.Database.GetDatatable(select);
if ((dtResult.Rows.Count > 0))
{
if (!dtResult.Rows[0].IsNull("NextID"))
{
iNext = System.Convert.ToInt32(dtResult.Rows[0]["NextID"]) + LastIndex;
UpdateNextIndex(conn, pEntityID, iNext);
}
else
{
iNext = LastIndex;
UpdateNextIndex(conn, pEntityID, iNext);
}
}
else
{
iNext = LastIndex;
DBConst eID = DBConst.String(pEntityID);
DBConst next = DBConst.Int32(iNext);
DBQuery insert = DBQuery.InsertInto("sys_Entity").Fields(
"EntityID", "NextID"
)
.Values(
eID,
next
);
int val = 0;
using (DbTransaction trans = db.Database.BeginTransaction())
{
val = db.Database.ExecuteNonQuery(trans, insert);
trans.Commit();
}
//// 'Pradeep added this code on july 10 2014
//mSQLBuilder = new SMRHRT.Data.SQLBuilder(SQLType.SQL_INSERT, DB);
//{
// var withBlock = mSQLBuilder;
// withBlock.AddTable("sys_entity");
// withBlock.AddField("sys_Entity", "EntityID", FieldType.FLD_STRING, null/* Conversion error: Set to default value for this argument */, pEntityID.ToString());
// withBlock.AddField("sys_Entity", "NextID", FieldType.FLD_NUMBER, null/* Conversion error: Set to default value for this argument */, System.Convert.ToString(1));
// withBlock.AddField("sys_Entity", "LastUPD", FieldType.FLD_DATETIME, null/* Conversion error: Set to default value for this argument */, System.Convert.ToString(DateAndTime.Today));
//}
//iNext = DB.ExecuteActionSQL(mSQLBuilder);
}
return(iNext);
}
catch (Exception dEx)
{
throw dEx;
}
}
protected internal DataTable GetClientAttribute(int clientID, string componentID)
{
string[] cid = componentID.Split(',');
List <DBClause> cids = new List <DBClause>();
for (int i = 0; i < cid.Length; i++)
{
cids.Add(DBConst.String(cid[i]));
}
DBComparison client = DBComparison.Equal(DBField.Field(TalentozSchemaClientInfo.ClientID.Name), DBConst.Int32(clientID));
DBComparison comp = DBComparison.In(DBField.Field(TalentozSchemaClientInfo.ComponentID.Name), cids.ToArray());
DBQuery select = DBQuery.SelectAll().From(TalentozSchemaClientInfo.Table).WhereAll(client, comp);
return(db.GetDatatable(select));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable GetTables(string clientid, string serverid)
{
DBQuery select;
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.ClientServer.ClientID.Name), DBConst.String(clientid));
DBComparison server = DBComparison.Equal(DBField.Field(TzAccount.ClientServer.ServerID.Name), DBConst.String(serverid));
DBConst dbclientid = DBConst.String(clientid);
select = DBQuery.SelectAll(TzAccount.Tables.Table).From(TzAccount.Tables.Table)
.WhereAll(client, server);
return(db.GetDatatable(select));
}
public bool Remove(string clientID, string serverID)
{
DBDatabase db;
db = base.Database;
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.ClientServer.ClientID.Name), DBConst.String(clientID));
DBComparison server = DBComparison.Equal(DBField.Field(TzAccount.ClientServer.ServerID.Name), DBConst.String(serverID));
DBQuery del = DBQuery.DeleteFrom(TzAccount.ClientServer.Table)
.WhereAll(client, server);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
private void prepareStatement()
{
foreach (TableField tf in DataSchema.Fields)
{
if (tf.FieldAlias != "")
{
select.Field(tf.Table, tf.Field).As(tf.FieldAlias);
}
else
{
select.Field(tf.Table, tf.Field).As(tf.Field);
}
}
if (DataSchema.Joins.Count == 0)
{
select.From(DataSchema.Table);
}
int index = 0;
foreach (Join j in DataSchema.Joins)
{
if (index == 0)
{
select.InnerJoin(j.TableName.Trim()).As(j.TableName.Trim()).On(j.TableName.Trim(), j.JoinField.Trim(), Compare.Equals, j.JoinTable.Trim(), j.JoinField.Trim());
}
else
{
select.And(j.TableName.Trim(), j.Field.Trim(), Compare.Equals, j.JoinTable.Trim(), j.JoinField.Trim());
}
}
index = 0;
foreach (WhereStatement ws in DataSchema.Wheres)
{
if (index == 0)
{
index = 1;
select.WhereField(ws.TableName.Trim(), ws.FieldName.Trim(), GetCompare(ws.Operator), DBConst.String(ws.FieldValue.Trim()));
}
else
{
select.AndWhere(ws.TableName.Trim(), ws.FieldName.Trim(), GetCompare(ws.Operator), DBConst.String(ws.FieldValue.Trim()));
}
}
}
public void GetDatatable(Tech.QScript.Syntax.SQuery sQuery)
{
DBSelectQuery select = DBQuery.Select();
bool isAdded = false;
foreach (Syntax.Query.Table t in sQuery.tables)
{
foreach (Syntax.Query.TableField tf in t.Fields)
{
if (t.TableAlias != "")
{
select.Field(t.TableAlias.Trim(), tf.FieldName.Trim()).As(tf.FieldName.Trim());
}
else
{
select.Field(t.TableName.Trim(), tf.FieldName.Trim()).As(tf.FieldName.Trim());
}
}
}
foreach (Syntax.Query.Table t in sQuery.tables)
{
if (t.Relations.Count == 0)
{
if (isAdded == false)
{
select.From(t.TableName.Trim()).As(t.TableAlias.Trim());
isAdded = true;
}
}
isAdded = false;
int indx = 0;
foreach (Syntax.Query.Relation r in t.Relations)
{
if (indx == 0)
{
select.InnerJoin(t.TableName.Trim()).As(t.TableAlias.Trim()).On(r.lefttable.Trim(), r.LeftField.Trim(), GetCompare(r.Operator), r.RightTable.Trim(), r.RightField.Trim());
}
else if (t.Relations.Count > 1)
{
select.And(r.lefttable.Trim(), r.LeftField.Trim(), GetCompare(r.Operator), r.RightTable.Trim(), r.RightField.Trim());
}
indx = indx + 1;
}
}
//foreach (Syntax.Query.Table t in sQuery.from.Tables )
//{
// select.From(t.TableName ).As(t.TableName );
//}
int index = 0;
foreach (Syntax.Query.Where where in sQuery.Conditions)
{
if (where.Type == Syntax.Query.ConditionType.filter)
{
if (where.JoinType == Syntax.Query.JoinType.And)
{
if (index == 0)
{
select.WhereField(where.lefttable.Trim(), where.LeftField.Trim(), GetCompare(where.Operator), DBConst.String(getValue(where.Operator, where.Value.Trim())));
}
else
{
select.AndWhere(where.lefttable.Trim(), where.LeftField.Trim(), GetCompare(where.Operator), DBConst.String(getValue(where.Operator, where.Value.Trim())));
}
}
else if (where.JoinType == Syntax.Query.JoinType.or)
{
DBField lff = DBField.Field(where.lefttable, where.LeftField.Trim());
DBField rff = DBField.Field(where.RightTable, where.RightField.Trim());
DBComparison d = DBComparison.Equal(lff, DBConst.String(where.Value.Trim()));
select.OrWhere(d);
}
else if (where.JoinType == Syntax.Query.JoinType.None)
{
DBField lff = DBField.Field(where.lefttable, where.LeftField.Trim());
DBField rff = DBField.Field(where.RightTable, where.RightField.Trim());
DBComparison d = DBComparison.Equal(lff, DBConst.String(where.Value.Trim()));
select.OrWhereNone(d);
}
index = index + 1;
}
}
var data = Database.GetDatatable(select);
query = select.ToSQLString(Database);
dataTable
= (System.Data.DataTable)data;
}
public DataTable GetServer(string clientid)
{
DBDatabase db;
db = base.Database;
DBQuery select;
select = DBQuery.SelectAll(TzAccount.ClientServer.Table).From(TzAccount.ClientServer.Table)
.WhereField(TzAccount.ClientServer.Table, TzAccount.ClientServer.ClientID.Name, Compare.Equals, DBConst.String(clientid));
return(db.GetDatatable(select));
}
public DataTable GetComponentPrivilege(string clientid, string groupid)
{
DBQuery select;
DBComparison dbcomponentType = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.ComponentType.Name), DBConst.Int32(1));
// DBComparison user = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.UserID.Name), DBConst.String(userid));
DBComparison SecurityGroupID = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.SecurityGroupID.Name), DBConst.String(groupid));
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.ClientID.Name), DBConst.String(clientid));
select = DBQuery.SelectAll(TzAccount.SecurityGroup.Table).From(TzAccount.SecurityGroup.Table)
.WhereAll(dbcomponentType, SecurityGroupID, Client);
return(db.GetDatatable(select));
}
// public string Save( DataTable dt
// )
// {
// string clientid;
// string groupid;
//// string userid,
// string componentid;
// int componentype;
// bool add;
// bool edit;
// bool view;
// bool remove;
// string a = Shared.generateID();
// var insert = DBQuery.InsertInto(TzAccount.SecurityPrivilege.Table)
// .Fields(TzAccount.SecurityPrivilege.PrivilegeID.Name,
// TzAccount.SecurityPrivilege.ClientID.Name,
// TzAccount.SecurityPrivilege.SecurityGroupID.Name,
// //TzAccount.SecurityPrivilege.UserID.Name,
// TzAccount.SecurityPrivilege.SecurityComponentID.Name,
// TzAccount.SecurityPrivilege.ComponentType.Name,
// TzAccount.SecurityPrivilege.Add.Name,
// TzAccount.SecurityPrivilege.Edit.Name,
// TzAccount.SecurityPrivilege.Remove.Name,
// TzAccount.SecurityPrivilege.View.Name
// ).
// Values(DBConst.String(a),
// DBConst.String(clientid),
// DBConst.String(groupid),
// // DBConst.String(userid),
// DBConst.String(componentid),
// DBConst.Int32(componentype),
// DBConst.Const(DbType.Boolean, add),
// DBConst.Const(DbType.Boolean, edit),
// DBConst.Const(DbType.Boolean, view),
// DBConst.Const(DbType.Boolean, remove));
// int val = 0;
// using (DbTransaction trans = db.BeginTransaction())
// {
// val = db.ExecuteNonQuery(trans, insert);
// trans.Commit();
// }
// if (val > 0)
// {
// return a;
// }
// else
// {
// return "";
// }
// }
/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="privilegeid"></param>
/// <param name="groupid"></param>
/// <param name="componentID"></param>
/// <param name="add"></param>
/// <param name="edit"></param>
/// <param name="view"></param>
/// <param name="remove"></param>
/// <returns></returns>
public bool update(string clientid,
string privilegeid,
string groupid,
string componentID,
bool add,
bool edit,
bool view,
bool remove)
{
DBComparison privilege = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.PrivilegeID.Name), DBConst.String(privilegeid));
DBComparison component = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.SecurityComponentID.Name), DBConst.String(componentID));
DBComparison SecurityGroupID = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.SecurityGroupID.Name), DBConst.String(groupid));
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.ClientID.Name), DBConst.String(clientid));
DBQuery update = DBQuery.Update(TzAccount.SecurityPrivilege.Table).Set(
TzAccount.SecurityPrivilege.Add.Name, DBConst.Const(DbType.Boolean, add)
).Set(
TzAccount.SecurityPrivilege.Edit.Name, DBConst.Const(DbType.Boolean, edit)
).Set(
TzAccount.SecurityPrivilege.View.Name, DBConst.Const(DbType.Boolean, view)
).Set(
TzAccount.SecurityPrivilege.Remove.Name, DBConst.Const(DbType.Boolean, remove)).
WhereAll(SecurityGroupID, Client, privilege, component);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public string Save(string clientName,
string clientNo,
string address,
string state,
string country,
string email,
string phone,
string orgName,
bool status,
string host)
{
DBDatabase db;
db = base.Database;
string a = Shared.generateID();
DBConst dbClientID = DBConst.String(a);
DBConst dbClientName = DBConst.String(clientName);
DBConst dbClientNo = DBConst.String(clientNo);
DBConst dbAddress = DBConst.String(address);
DBConst dbState = DBConst.String(state);
DBConst dbCountry = DBConst.String(country);
DBConst dbstatus = DBConst.Const(DbType.Boolean, status);
DBConst dbEmail = DBConst.String(email);
DBConst dbPhone = DBConst.String(phone);
DBConst dbHost = DBConst.String(host);
DBConst dbOrgName = DBConst.String(orgName);
DBQuery insert = DBQuery.InsertInto(TzAccount.Client.Table).Fields(
TzAccount.Client.ClientID.Name,
TzAccount.Client.ClientName.Name,
TzAccount.Client.ClientNo.Name,
TzAccount.Client.Country.Name,
TzAccount.Client.State.Name,
TzAccount.Client.PhoneNo.Name,
TzAccount.Client.Email.Name,
TzAccount.Client.Status.Name,
TzAccount.Client.OrganizationName.Name,
TzAccount.Client.Host.Name,
TzAccount.Client.Address.Name).Values(
dbClientID,
dbClientName,
dbClientNo,
dbCountry,
dbState,
dbPhone,
dbEmail,
dbstatus,
dbOrgName,
dbHost,
dbAddress
);
int val = 0;
using (DbTransaction trans = db.BeginTransaction()) {
val = db.ExecuteNonQuery(trans, insert);
trans.Commit();
}
if (val > 0)
{
return(a);
}
else
{
return("");
}
}
protected internal bool UpdateComponentLookUp(string componentID, string attributeID, string componentLookup, string displayName)
{
// DBComparison client = DBComparison.Equal(DBField.Field(TalentozSchemaInfo.ClientID.Name), DBConst.Int32(clientID));
//DBComparison comp = DBComparison.Equal(DBField.Field(TalentozSchemaInfo.ComponentID.Name), DBConst.String(componentID));
DBComparison att = DBComparison.Equal(DBField.Field(TalentozSchemaInfo.FieldID.Name), DBConst.String(attributeID));
DBQuery update = DBQuery.Update(TalentozSchemaInfo.Table)
.Set(TalentozSchemaInfo.LookupComponent.Name, DBConst.String(componentLookup))
.AndSet(TalentozSchemaInfo.ComponentLookupDisplayName.Name, DBConst.String(displayName)).WhereAll(att);
if (db.ExecuteNonQuery(update) > 0)
{
return(true);
}
else
{
return(false);
}
}
public void _11_UpdateSubSelectAndParameterXmlTest()
{
DBParam cid = DBParam.ParamWithValue("cid", 10);
DBQuery thirtyDaysOrders = DBQuery.Select()
.Sum(DBField.Field("order_value")).From("ORDER").As("O")
.WhereField("order_date", Compare.GreaterThan, DBConst.DateTime(DateTime.Today.AddDays(-30)))
.AndWhere("customer_id", Compare.Equals, cid);
DBQuery q = DBQuery.Update("CUSTOMERS").Set("customer_name", DBConst.String("new name"))
.AndSet("customer_address", DBConst.String("new address"))
.AndSet("order_value", thirtyDaysOrders)
.WhereField("customer_id", Compare.Equals, cid);
q = SerializeAndDeserialzeQueryToMatch(q, "Sub Select Update");
}
protected internal DataTable GetComponent(string compID)
{
DBComparison comp = DBComparison.Equal(DBField.Field(TalentozSchema.ComponentID.Name), DBConst.String(compID));
DBComparison tb = DBComparison.Equal(DBField.Field(TalentozSchema.TableName.Name), DBConst.String(compID));
DBComparison cname = DBComparison.Equal(DBField.Field(TalentozSchema.ComponentName.Name), DBConst.String(compID));
DBQuery select = DBQuery.SelectAll().From(TalentozSchema.Table).WhereAny(comp, tb, cname).OrderBy(TalentozSchema.ComponentName.Name, Order.Ascending);
return(db.GetDatatable(select));
}
public void _05_AggregateXmlTest()
{
DBQuery q = DBQuery.Select().Field(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue")
.From("Orders").As("0");
q = SerializeAndDeserialzeQueryToMatch(q, "Aggregate test");
}
protected internal bool ClearTemplate(int clientID)
{
DBComparison client = DBComparison.Equal(DBField.Field(TalentozSchemaClientInfo.ClientID.Name), DBConst.Int32(clientID));
DBQuery deleteView = DBQuery.DeleteFrom(TalentozTemplate.Table).WhereAll(client);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozImportLog.Table).WhereAll(client);;
db.ExecuteNonQuery(deleteView);
return(true);
}
public void _06_FullComplexInnerSelectXmlTest()
{
DBSelectQuery orderValue = DBQuery.Select()
.Sum(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue")
.And(DBFunction.IsNull("OrderID", DBConst.Const("No ID")))
.Field("O", "customerID")
.From("Orders").As("O")
.InnerJoin("Order Details").As("OD")
.On("OD", "OrderID", Compare.Equals, "O", "OrderID")
.GroupBy("O", "CustomerID");
DBQuery q = DBQuery.SelectTopN(10)
.Field("CUST", "CompanyName").As("Company Name")
.Field("CUST", "CustomerID").As("Customer ID")
.And(DBFunction.IsNull("CUST", "ContactName", DBConst.Const("No Contact"))).As("Contact")
.Sum("ORD", "OrderValue").As("Total Value")
.From("Customers").As("CUST")
.InnerJoin(orderValue).As("ORD")
.On("ORD", "CustomerID", Compare.Equals, "CUST", "CustomerID")
.WhereField("ORD", "OrderValue", Compare.GreaterThanEqual, DBConst.Const(500.0))
.GroupBy("CUST", "CompanyName").And("CUST", "CustomerID")
.OrderBy("Total Value", Order.Descending);
q = SerializeAndDeserialzeQueryToMatch(q, "Complex Inner Select Test");
}
public void Test_19_TableHints()
{
DBQuery hinted = DBQuery.SelectAll()
.From("dbo", "Categories").WithHint(DBTableHint.NoLock).WithHint(DBTableHint.Index, "pk_Categories")
.InnerJoin("dbo", "Customers").WithHint(DBTableHint.ReadUncommitted)
.On("Categories", "category_id", Compare.Equals, "Customers", "category_id")
.WhereIn(DBField.Field("Categroies", "category_id"), DBConst.Int32(1), DBConst.Int32(2), DBConst.Int32(3))
.WithQueryOption(DBQueryOption.Fast, 10).WithQueryOption(DBQueryOption.HashJoin);
this.OutputAndValidate(hinted, " Hinted joined select query");
}
public void _07_SimpleDeleteXmlTest()
{
DBQuery q = DBQuery.DeleteFrom("Customers").WhereField("customer_id", Compare.Equals, DBConst.Int32(2));
q = SerializeAndDeserialzeQueryToMatch(q, "Simple Delete");
}
public static DataTable GetLookup(int clientID, string FieldInstanceIDs, string conn)
{
DataBase db = new DataBase();
db.InitDbs(conn);
List <DBClause> items = new List <DBClause>();
var fids = FieldInstanceIDs.Split(',');
foreach (string itm in fids)
{
items.Add(DBConst.Const(itm));
}
DBQuery select;
DBComparison client = DBComparison.Equal(DBField.Field("ClientID"), DBConst.Int32(clientID));
DBComparison fieldinstance = DBComparison.In(DBField.Field("FieldInstanceID"), items.ToArray());
DBConst dbclientid = DBConst.String("ClientID");
select = DBQuery.SelectAll("sys_FieldInstanceLookUp").From("sys_FieldInstanceLookUp")
.WhereAll(client, fieldinstance);
return(db.Database.GetDatatable(select));
}
protected internal string SaveClientAttribute(string id, string name, string displayName, string componentID,
bool isRequired, bool isUnique, bool isCore,
bool isReadonly,
bool isSecured,
int lookupId,
int type,
int length,
string defaultValue,
string fileExtension,
bool isNull,
bool isPrimary,
bool isAuto,
string lookupComponent,
string componentDisplayField, int clientid)
{
string attID = id;
Tech.Data.Query.DBQuery dBQuery = DBQuery.InsertInto(TalentozSchemaClientInfo.Table)
.Field(TalentozSchemaClientInfo.ComponentID.Name).Field(TalentozSchemaClientInfo.FieldID.Name).Field(TalentozSchemaClientInfo.AttributeName.Name)
.Field(TalentozSchemaClientInfo.DisplayName.Name).Field(TalentozSchemaClientInfo.IsRequired.Name).Field(TalentozSchemaClientInfo.IsUnique.Name)
.Field(TalentozSchemaClientInfo.IsCore.Name).Field(TalentozSchemaClientInfo.IsReadOnly.Name).Field(TalentozSchemaClientInfo.IsSecured.Name)
.Field(TalentozSchemaClientInfo.LookUpID.Name).Field(TalentozSchemaClientInfo.AttributeType.Name).Field(TalentozSchemaClientInfo.Length.Name)
.Field(TalentozSchemaClientInfo.DefaultValue.Name).Field(TalentozSchemaClientInfo.FileExtension.Name)// .Field(TalentozSchemaClientInfo.RegExp.Name)
.Field(TalentozSchemaClientInfo.IsNullable.Name)
.Field(TalentozSchemaClientInfo.ISPrimaryKey.Name)
.Field(TalentozSchemaClientInfo.IsAuto.Name)
.Field(TalentozSchemaClientInfo.LookupComponent.Name)
.Field(TalentozSchemaClientInfo.ComponentLookupDisplayName.Name)
.Field(TalentozSchemaClientInfo.LastUPD.Name)
.Field(TalentozSchemaClientInfo.ClientID.Name)
.Values(DBConst.String(componentID),
DBConst.String(attID),
DBConst.String(name),
DBConst.String(displayName),
DBConst.Const(System.Data.DbType.Boolean, isRequired),
DBConst.Const(System.Data.DbType.Boolean, isUnique),
DBConst.Const(System.Data.DbType.Boolean, isCore),
DBConst.Const(System.Data.DbType.Boolean, isReadonly),
DBConst.Const(System.Data.DbType.Boolean, isSecured),
DBConst.Const(System.Data.DbType.Int32, lookupId),
DBConst.Const(System.Data.DbType.Int32, type),
DBConst.Const(System.Data.DbType.Int32, length),
DBConst.Const(System.Data.DbType.String, defaultValue),
DBConst.Const(System.Data.DbType.String, fileExtension),
DBConst.Const(System.Data.DbType.Boolean, isNull),
DBConst.Const(System.Data.DbType.Boolean, isPrimary),
DBConst.Const(System.Data.DbType.Boolean, isAuto),
DBConst.Const(System.Data.DbType.String, lookupComponent),
DBConst.Const(System.Data.DbType.String, componentDisplayField),
DBConst.Const(System.Data.DbType.DateTime, DateTime.Today),
DBConst.Const(System.Data.DbType.Int32, clientid)
);
if (db.ExecuteNonQuery(dBQuery) > 0)
{
return(attID);
}
else
{
return("");
}
}
public bool RemoveAllPrivileges(string groupID, string clientid)
{
DBComparison SecurityGroupID = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.SecurityGroupID.Name), DBConst.String(groupID));
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.ClientID.Name), DBConst.String(clientid));
DBQuery del = DBQuery.DeleteFrom(TzAccount.SecurityPrivilege.Table)
.WhereAll(SecurityGroupID, Client);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
protected internal bool UpdateComponent(string componentID, string name, int type, string title, string category, string primarykey, string entityKEy)
{
// DBComparison client = DBComparison.Equal(DBField.Field(TalentozSchemaInfo.ClientID.Name), DBConst.Int32(clientID));
DBComparison comp = DBComparison.Equal(DBField.Field(TalentozSchema.ComponentID.Name), DBConst.String(componentID));
// DBComparison att = DBComparison.Equal(DBField.Field(TalentozSchemaInfo.FieldID.Name), DBConst.String(attributeID));
DBQuery update = DBQuery.Update(TalentozSchema.Table)
.Set(TalentozSchema.ComponentName.Name, DBConst.String(name))
.AndSet(TalentozSchema.Title.Name, DBConst.String(title))
.AndSet(TalentozSchema.Category.Name, DBConst.String(category))
.AndSet(TalentozSchema.EntityKey.Name, DBConst.String(entityKEy))
.AndSet(TalentozSchema.PrimaryKeys.Name, DBConst.String(primarykey)).WhereAll(comp);
if (db.ExecuteNonQuery(update) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Update(string clientid, string groupID,
string groupName,
string description,
int context,
bool isBase)
{
DBComparison SecurityGroupID = DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.SecurityGroupID.Name), DBConst.String(groupID));
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.ClientID.Name), DBConst.String(clientid));
DBQuery update = DBQuery.Update(TzAccount.SecurityGroup.Table).Set(
TzAccount.SecurityGroup.GroupName.Name, DBConst.String(groupName)
).Set(
TzAccount.SecurityGroup.Description.Name, DBConst.String(description)
).Set(
TzAccount.SecurityGroup.IsBaseType.Name, DBConst.Const(DbType.Boolean, isBase)
).Set(
TzAccount.SecurityGroup.Context.Name, DBConst.Int32(context)).WhereAll(SecurityGroupID, Client);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
protected internal bool UpdateComponentClientAttribute(int clientID, string componentID, string attributeID,
string displayName,
bool isRequired, bool isUnique, bool isCore,
bool isReadonly,
bool isSecured,
int lookupId,
int type,
int length,
string defaultValue,
string fileExtension,
string lookupComponent,
string componentDisplayField)
{
DBComparison client = DBComparison.Equal(DBField.Field(TalentozSchemaClientInfo.ClientID.Name), DBConst.Int32(clientID));
DBComparison comp = DBComparison.Equal(DBField.Field(TalentozSchemaClientInfo.ComponentID.Name), DBConst.String(componentID));
DBComparison att = DBComparison.Equal(DBField.Field(TalentozSchemaClientInfo.FieldID.Name), DBConst.String(attributeID));
DBQuery update = DBQuery.Update(TalentozSchemaClientInfo.Table).Set(DBField.Field(TalentozSchemaClientInfo.DisplayName.Name), DBConst.String(displayName))
.AndSet(DBField.Field(TalentozSchemaClientInfo.IsRequired.Name), DBConst.Const(DbType.Boolean, isRequired))
.AndSet(DBField.Field(TalentozSchemaClientInfo.IsUnique.Name), DBConst.Const(DbType.Boolean, isUnique))
.AndSet(DBField.Field(TalentozSchemaClientInfo.IsCore.Name), DBConst.Const(DbType.Boolean, isCore))
.AndSet(DBField.Field(TalentozSchemaClientInfo.IsReadOnly.Name), DBConst.Const(DbType.Boolean, isReadonly))
.AndSet(DBField.Field(TalentozSchemaClientInfo.IsSecured.Name), DBConst.Const(DbType.Boolean, isSecured))
.AndSet(DBField.Field(TalentozSchemaClientInfo.AttributeType.Name), DBConst.Int32(type))
.AndSet(DBField.Field(TalentozSchemaClientInfo.LookUpID.Name), DBConst.Int32(lookupId))
.AndSet(DBField.Field(TalentozSchemaClientInfo.DefaultValue.Name), DBConst.String(defaultValue))
.AndSet(DBField.Field(TalentozSchemaClientInfo.LookupComponent.Name), DBConst.String(lookupComponent))
.AndSet(DBField.Field(TalentozSchemaClientInfo.ComponentLookupDisplayName.Name), DBConst.String(componentDisplayField))
.AndSet(DBField.Field(TalentozSchemaClientInfo.FileExtension.Name), DBConst.String(fileExtension))
.AndSet(DBField.Field(TalentozSchemaClientInfo.Length.Name), DBConst.Int32(length))
.WhereAll(client, comp, att);
if (db.ExecuteNonQuery(update) > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataTable GetAllExportEvents(string clientid)
{
DBDatabase db;
db = base.Database;
DBQuery select;
select = DBQuery.SelectAll(TzAccount.ImportExportEvents.Table).From(TzAccount.ImportExportEvents.Table)
.WhereField(TzAccount.ImportExportEvents.Table, TzAccount.ImportExportEvents.ClientID.Name, Compare.Equals, DBConst.String(clientid)).
AndWhere(TzAccount.ImportExportEvents.Table, TzAccount.ImportExportEvents.Type.Name, Compare.Equals, DBConst.Int32(2))
.OrderBy(TzAccount.ImportExportEvents.EventDateTime.Name, Order.Descending);
return(db.GetDatatable(select));
}
/// <summary>
///
/// </summary>
/// <param name="scriptID"></param>
/// <returns></returns>
public DataTable GetScript(string scriptID)
{
DBDatabase db;
db = base.Database;
DBQuery select;
select = DBQuery.SelectAll(TzAccount.DataScript.Table).From(TzAccount.DataScript.Table)
.WhereField(TzAccount.DataScript.Table, TzAccount.DataScript.ScriptID.Name, Compare.Equals, DBConst.String(scriptID));
return(db.GetDatatable(select));
}
