private static void SearchByComplexQuery()
{
var lq = new LinkQuery
{
LinkTypeId = "ProductItems",
Direction = LinkDirection.OutBound,
SourceEntityTypeId = "Product",
TargetEntityTypeId = "Item"
};
var AllProductsLinkedToItems = remoteManager.DataService.LinkSearch(lq, LoadLevel.DataAndLinks);
var sq = new SystemQuery
{
Created = DateTime.Today.AddDays(-1),
CreatedOperator = Operator.GreaterThan
};
var complexQuery = new ComplexQuery
{
LinkQuery = lq,
SystemQuery = sq
};
var allProductsLinkedToItemsThatWasCreatedToday =
remoteManager.DataService.Search(complexQuery, LoadLevel.DataAndLinks);
}
public async Task <DocumentDbResult <Project> > GetProjectsAsync(ComplexQuery query)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
if (!query.Count &&
(query.Top == null || query.Top > Constants.MaxProjects))
{
query.Top = Constants.MaxProjects;
}
var fin = _translator.Translate(query);
if (query.Count)
{
var count = await _repoCounter.GetListAsync(fin).ConfigureAwait(false);
return(new DocumentDbResult <Project>(count.Sum(x => x.Number), null));
}
query.Count = true;
var finc = _translator.Translate(query);
var count2 = (await _repoCounter.GetListAsync(finc).ConfigureAwait(false)).Sum(x => x.Number);
query.Count = false;
var result = await GetListAsync(fin).ConfigureAwait(false);
return(new DocumentDbResult <Project>(count2, result));
}
void IComplexQueryEntity.AdjustComplexQuery(ComplexQuery query)
{
query.Add(new Contact
{
ReturnBehavior = ReturnBehavior.OnlySpecified,
ContactID = new IntReturn(),
Email = new StringReturn()
});
}
public string GetUserNameById(string id)
{
ComplexQuery cmquery = new ComplexQuery();
cmquery.AddNeedField("users", "USER_NAME");
cmquery.AddCriterion("USER_ID", id, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(cmquery);
return(table.Rows[0][0].ToString().Trim());
}
public string GetReportNameBy(int id)
{
ComplexQuery cmquery = new ComplexQuery();
cmquery.AddNeedField("Report", "Name");
cmquery.AddCriterion("ID", id, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(cmquery);
return(table.Rows[0][0].ToString().Trim());
}
private static void SearchByCriteria()
{
Criteria itemCriteria = new Criteria
{
FieldTypeId = "ItemNumber",
Operator = Operator.BeginsWith,
Value = ITEM_NUMBER
};
ComplexQuery x = new ComplexQuery();
List <Entity> entities = remoteManager.DataService.Search(itemCriteria, LoadLevel.Shallow);
}
/// <summary>
/// 获得分厂ID和名字
/// </summary>
/// <returns></returns>
public string GetFactory()
{
ComplexQuery query = new ComplexQuery();
query.AddNeedField("Factory", "ID");
query.AddNeedField("Factory", "Name");
DataTable dt = dataFactory.Query(query);
if (dt.Rows.Count == 0)
{
throw new Exception("查无数据");
}
return(ComboboxJsonParser.DataTableToJson(dt, "ID", "Name"));
}
public void DocumentFilterSample()
{
var query = new ComplexQuery(
null,
"id eq 'moeta(42)'",
1,
null,
null,
"dev",
false);
var documentTranslator = new DocumentTranslator("test", Helpers.Enums.DocumentType.Data, "records", 3);
var sqlQuery = documentTranslator.Translate(query);
Assert.AreEqual("select top 30 c.identifier, c.robot from c where c.title = 'moeta' and c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery);
}
public void ProjectSelectSample()
{
var query = new ComplexQuery(
"id, robot",
"title eq 'moeta'",
30,
null,
null,
"dev",
false);
var projectTranslator = new ProjectTranslator();
var sqlQuery = projectTranslator.Translate(query);
Assert.AreEqual("select top 30 c.identifier, c.robot from c where c.title = 'moeta' and c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery);
}
public void ProjectSelectSample3()
{
var query = new ComplexQuery(
"a,b,c",
null,
null,
null,
null,
"dev",
false);
var projectTranslator = new ProjectTranslator();
var sqlQuery = projectTranslator.Translate(query);
Assert.AreEqual("select c.a,c.b,c.c from c where c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery);
}
/// <summary>
/// 获得视图中所有变量的Id值
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
private ArrayList GetParametorsId(string viewName)
{
ArrayList result = new ArrayList();
ComplexQuery cmpquery = new ComplexQuery();
cmpquery.AddNeedField("ContrastTable", "VariableName");
cmpquery.AddCriterion("ViewName", viewName, CriteriaOperator.Equal);
DataTable data = dataFactory.Query(cmpquery);
foreach (DataRow row in data.Rows)
{
result.Add(row["VariableName"].ToString().Trim());
}
return(result);
}
public void DocumentCountSample()
{
var query = new ComplexQuery(
"id, englishName",
"title eq 'moeta' and property/field ne 'val' or number ge 5 and (likes ne 3 or special lt 3)",
30,
null,
"_created asc, createdDateTime desc",
"dev",
true);
var documentTranslator = new DocumentTranslator("testproject", Helpers.Enums.DocumentType.Content, "articles", null);
var sqlQuery = documentTranslator.Translate(query);
Assert.AreEqual("select count(1) from c where c.title = 'moeta' and c.property.field <> 'val' or c.number >= 5 and (c.likes <> 3 or c.special < 3) and c.entity = 'kotori/document' and c.instance = 'dev' and c.projectId = 'kotori://api/projects/testproject' and c.documentTypeId = 'kotori://api/projects/testproject/content/articles' ", sqlQuery);
}
public void ProjectCountSample()
{
var query = new ComplexQuery(
"id, englishName",
"title eq 'moeta' and property/field ne 'val' or number ge 5 and (likes ne 3 or special lt 3)",
30,
null,
"_created asc, createdDateTime desc",
"dev",
true);
var projectTranslator = new ProjectTranslator();
var sqlQuery = projectTranslator.Translate(query);
Assert.AreEqual("select count(1) from c where c.title = 'moeta' and c.property.field <> 'val' or c.number >= 5 and (c.likes <> 3 or c.special < 3) and c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery);
}
public void ProjectSelectSampleWithTransformation()
{
var query = new ComplexQuery(
"",
"id eq 'yuri-yuri'",
null,
null,
null,
"dev",
false);
var projectTranslator = new ProjectTranslator();
var sqlQuery = projectTranslator.Translate(query);
Assert.AreEqual("select * from c where c.identifier = 'kotori://api/projects/yuri-yuri' and c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery);
}
/// <summary>
/// 获得视图中所有变量的Id值
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
public System.Collections.ArrayList GetVariableId(int productLineId, string viewName)
{
string connectionString = ConnectionStringFactory.GetConnectionStringByProductLineID(productLineId, DatabaseType.DCSSystemDatabase);
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
ArrayList result = new ArrayList();
ComplexQuery cmpquery = new ComplexQuery();
cmpquery.AddNeedField("ContrastTable", "VariableName");
cmpquery.AddCriterion("ViewName", viewName, CriteriaOperator.Equal);
DataTable data = dataFactory.Query(cmpquery);
foreach (DataRow row in data.Rows)
{
result.Add(row["VariableName"].ToString().Trim());
}
return(result);
}
public string GetEletricalRoom(int factoryId)
{
try
{
string connString = ConnectionStringFactory.GetConnectionStringByFactoryID(factoryId, DatabaseType.EnergyDatabase);
ISqlServerDataFactory factory = new SqlServerDataFactory(connString);
ComplexQuery query = new ComplexQuery();
query.AddNeedField("ElectricalRoom", "ID");
query.AddNeedField("ElectricalRoom", "Name");
DataTable dt = factory.Query(query);
return(ComboboxJsonParser.DataTableToJson(dt, "ID", "Name"));
}
catch
{
return("[]");
}
}
public async Task <Project> GetProjectAsync(string instance, string id)
{
if (instance == null)
{
throw new ArgumentNullException(nameof(instance));
}
if (string.IsNullOrEmpty(id))
{
return(null);
}
var query = new DynamicQuery("id eq @id", new { id });
var complex = new ComplexQuery(null, query.ToSqlQuery(), 1, null, null, instance);
var fin = _translator.Translate(complex);
var result = await GetFirstOrDefaultAsync(new DynamicQuery(fin)).ConfigureAwait(false);
return(result);
}
/// <summary>
/// 获得实时数据的table表
/// </summary>
/// <param name="dataSetInformations"></param>
/// <returns></returns>
private DataTable GetDataItemTable(IEnumerable <DataSetInformation> dataSetInformations)
{
//DataItem result = new DataItem();
ComplexQuery cmpquery = new ComplexQuery();
foreach (var item in dataSetInformations)
{
cmpquery.AddNeedField(item.TableName, item.FieldName, item.ViewId);
}
cmpquery.JoinCriterion = new JoinCriterion
{
DefaultJoinFieldName = "v_date",
JoinType = JoinType.FULL_JOIN
};
cmpquery.TopNumber = 1;
//cmpquery.OrderByClause = new OrderByClause("realtime_line_data.v_date", true);
DataTable table = dataFactory.Query(cmpquery);
return(table);
}
private DataItem GetDataItem(DataSetInformation dataSetInformation)
{
DataItem result = new DataItem();
ComplexQuery cmpquery = new ComplexQuery();
cmpquery.AddNeedField(dataSetInformation.TableName, dataSetInformation.FieldName, dataSetInformation.FieldName);
cmpquery.TopNumber = 1;
cmpquery.AddOrderByClause("v_date", true);
DataTable table = dataFactory.Query(cmpquery);
foreach (DataRow item in table.Rows)
{
result = new DataItem
{
ID = dataSetInformation.ViewId,
Value = item[dataSetInformation.FieldName].ToString().Trim()
};
}
return(result);
}
/// <summary>
/// 返回最近两天的历史数据表
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
private DataTable GetHistoryDataTable(string viewName)
{
bool flag = false;
DataTable result = new DataTable();
IList <NeedField> needFields = new List <NeedField>();
IEnumerable <DataSetInformation> dataSetInfor = GetDataSetInformation(viewName);
foreach (var item in dataSetInfor)
{
if (flag == false)
{
needFields.Add(new NeedField
{
FieldName = "v_date",
TableName = item.TableName,
VariableName = "date"
});
flag = true;
}
needFields.Add(new NeedField
{
FieldName = item.FieldName,
TableName = item.TableName,
VariableName = item.ViewId
});
}
JoinCriterion joinCriterion = new JoinCriterion
{
DefaultJoinFieldName = "v_date",
JoinType = JoinType.FULL_JOIN
};
ComplexQuery cmpquery = new ComplexQuery(needFields, joinCriterion);
//cmpquery.AddCriterion("v_date", DateTime.Now.AddDays(1).Date, CriteriaOperator.LessThan);
//cmpquery.AddCriterion("v_date", DateTime.Now.AddDays(-1).Date, CriteriaOperator.MoreThan);
result = dataFactory.Query(cmpquery);
return(result);
}
/// <summary>
/// 获得实时数据的table表
/// </summary>
/// <param name="dataPathInfor"></param>
/// <returns></returns>
public DataTable GetDataItemTable(int productLineId, IEnumerable <DataPathInformation> dataPathInfor)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
ComplexQuery cmpquery = new ComplexQuery();
foreach (var item in dataPathInfor)
{
cmpquery.AddNeedField(item.TableName, item.FieldName, item.ViewId);
}
cmpquery.JoinCriterion = new JoinCriterion
{
DefaultJoinFieldName = "v_date",
JoinType = JoinType.FULL_JOIN
};
cmpquery.TopNumber = 1;
//cmpquery.OrderByClause = new OrderByClause("realtime_line_data.v_date", true);
DataTable table = dataFactory.Query(cmpquery);
return(table);
}
public static void TranslateIntoComplexQuery(ComplexQuery complexQuery, SqlCommand command)
{
string baseString = "SELECT ";
StringBuilder result = new StringBuilder();
result.Append(baseString);
if (complexQuery.IsDictinct == true)
{
result.Append("DISTINCT ");
}
if (complexQuery.TopNumber != 0)
{
result.Append(TranslateHelper.GetTopNumber(complexQuery.TopNumber));
}
foreach (var item in complexQuery.NeedFields)
{
result.Append(TranslateHelper.GetFieldString(item));
}
result.Remove(result.Length - 1, 1);
result.Append(TranslateHelper.GetJoinConditionString(complexQuery.NeedFields, complexQuery.JoinCriterion));
if (complexQuery.Criterions.Count() > 0)
{
TranslateHelper.GetCriterionString(result, complexQuery.Criterions, command, complexQuery.SqlOperator);
}
if (complexQuery.OrderByClauses.Count > 0)
{
result.Append(TranslateHelper.GetStringFromOrderByClause(complexQuery.OrderByClauses));
}
command.CommandText = result.ToString();
}
public static void TranslateIntoComplexQuery(ComplexQuery complexQuery, SqlCommand command)
{
string baseString = "SELECT ";
StringBuilder result = new StringBuilder();
result.Append(baseString);
if (complexQuery.IsDictinct == true)
{
result.Append("DISTINCT ");
}
if (complexQuery.TopNumber!= 0)
{
result.Append(TranslateHelper.GetTopNumber(complexQuery.TopNumber));
}
foreach (var item in complexQuery.NeedFields)
{
result.Append(TranslateHelper.GetFieldString(item));
}
result.Remove(result.Length - 1, 1);
result.Append(TranslateHelper.GetJoinConditionString(complexQuery.NeedFields, complexQuery.JoinCriterion));
if (complexQuery.Criterions.Count() > 0)
{
TranslateHelper.GetCriterionString(result, complexQuery.Criterions, command, complexQuery.SqlOperator);
}
if (complexQuery.OrderByClauses.Count > 0)
{
result.Append(TranslateHelper.GetStringFromOrderByClause(complexQuery.OrderByClauses));
}
command.CommandText = result.ToString();
}
public List <Entity> Search(ComplexQuery query, LoadLevel level)
{
throw new NotImplementedException();
}
/// <summary>
/// Initialize properties.
/// </summary>
/// <param name="instance">Instance.</param>
/// <param name="query">Query.</param>
public void Init(string instance, ComplexQuery query)
{
Instance = instance;
Query = query;
}
public WorkAreaFolder UpdateSharedWorkAreaQuery(Guid id, ComplexQuery query)
{
throw new NotImplementedException();
}
static void Main(string[] args)
{
string conn = "Data Source=QH-20140814XCYI;Initial Catalog=Test;Integrated Security=True";
SqlServerDataFactory dataFactory = new SqlServerDataFactory(conn);
DataTable dttest = new DataTable();
DataColumn dc = new DataColumn("d", typeof(string));
dttest.Columns.Add(dc);
DataColumn dc1 = new DataColumn("c", typeof(int));
dttest.Columns.Add(dc1);
DataRow dr = dttest.NewRow();
dr["c"] = 2;
dr["d"] = "123ss";
dttest.Rows.Add(dr);
DataRow dr1 = dttest.NewRow();
dr1["c"] = 3;
dr1["d"] = "123ss";
dttest.Rows.Add(dr1);
//dataFactory.Insert("B", dttest);
string[] key = { "c" };
dataFactory.Update("B", dttest, key);
Console.ReadKey();
//////////////////////////////////////delete
Delete delete = new Delete("tableName");
delete.AddCriterions("a", "zhangxin", "1", CriteriaOperator.MoreThan);
delete.AddCriterions("a", "zhangdi", 2, CriteriaOperator.LessThan);
delete.AddCriterions("b", "2", CriteriaOperator.Equal);
delete.AddSqlOperator(SqlOperator.OR);
SqlCommand deleteCmd = new SqlCommand();
DeleteTranslator.TranslateIntoDelete(delete, deleteCmd);
Console.WriteLine(deleteCmd.CommandText);
Console.WriteLine(deleteCmd.Parameters.Count);
//Console.ReadKey();
//////////////////////////////////////////////////
Test test = new Test
{
a = "1",
b = "2",
c = "3"
};
Insert <Test> insert = new Insert <Test>("tableName", test);
insert.AddExcludeField("a");
SqlCommand insertCmd = new SqlCommand();
SaveTranslator.TranslateIntoInsert <Test>(insert, insertCmd);
Console.WriteLine(insertCmd.CommandText);
Console.WriteLine(insertCmd.Parameters.Count);
//Console.ReadKey();
////////////////////////////////////////////////////////////
Test test1 = new Test
{
a = "1",
b = "2",
c = "3"
};
Update <Test> update = new Update <Test>("taleName", test1);
update.AddCriterion("a", "a", "1", CriteriaOperator.Equal);
update.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
update.AddExcludeField("c");
update.AddSqlOperator(SqlOperator.AND);
SqlCommand updateCmd = new SqlCommand();
SaveTranslator.TranslateIntoUpdate <Test>(update, updateCmd);
Console.WriteLine(updateCmd.CommandText);
Console.WriteLine(updateCmd.Parameters.Count);
//Console.ReadKey();
////////////////////////////////////////////////////////////
Query query = new Query("tableName");
query.AddCriterion("a.b", "1", CriteriaOperator.Equal);
//query.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
query.AddCriterion("c", "____", CriteriaOperator.Like);
query.AddOrderByClause(new OrderByClause("a", false));
query.AddOrderByClause(new OrderByClause("b", true));
//query.AddSqlOperator(SqlOperator.AND);
SqlCommand queryCmd = new SqlCommand();
QueryTranslator.TranslateIntoSelect(query, queryCmd);
Console.WriteLine(queryCmd.CommandText);
Console.WriteLine(queryCmd.Parameters.Count);
//Console.ReadKey();
//////////////////////////////////////////////////////////////
ComplexQuery cmquery = new ComplexQuery();
IList <NeedField> list = new List <NeedField>();
NeedField needField = new NeedField
{
TableName = "tableName1",
FieldName = "a",
VariableName = ""
};
list.Add(needField);
cmquery.NeedFields = list;
cmquery.AddNeedField("tableName2", "b");
cmquery.AddNeedField("tableName3", "c", "test");
IDictionary <string, string> testjoin = new Dictionary <string, string>();
testjoin.Add("tableName1", "c1");
testjoin.Add("tableName2", "c2");
testjoin.Add("tableName3", "c3");
//cmquery.AddJoinCriterion("assss", JoinType.FULL_JOIN);
cmquery.AddJoinCriterion(testjoin, JoinType.INNER_JOIN);
//cmquery.IsDictinct = true;
//cmquery.TopNumber = 9;
cmquery.AddCriterion("a", "a", "1", CriteriaOperator.Equal);
cmquery.AddCriterion("b", "b", "2", CriteriaOperator.Equal);
cmquery.AddCriterion("c", "c", "3", CriteriaOperator.Like);
List <OrderByClause> orders = new List <OrderByClause>();
orders.Add(new OrderByClause("a", true));
orders.Add(new OrderByClause("b", false));
cmquery.OrderByClauses = orders;
SqlCommand cmqueryCmd = new SqlCommand();
ComplexQueryTranslator.TranslateIntoComplexQuery(cmquery, cmqueryCmd);
Console.WriteLine(cmqueryCmd.CommandText);
Console.WriteLine(cmqueryCmd.Parameters.Count);
//Console.ReadKey();
///////////////////////////////////////////////////////////
ComplexQuery testquery = new ComplexQuery();
Console.WriteLine(testquery.TopNumber);
Console.WriteLine(DateTime.Today);
Console.WriteLine(DateTime.Now.AddDays(-1).Date);
//Console.ReadKey();
///////////////////////////////////////////////////////
//Query testQuery = new Query("B");
//SqlServerDataFactory dataFactory = new SqlServerDataFactory("Data Source=QH-20140814XCYI;Initial Catalog=Test;Integrated Security=True");
//IEnumerable<BModle> result = dataFactory.Query<BModle>(testQuery);
//foreach (var item in result)
//{
// //BModle a = (BModle)item;
// Console.WriteLine(item.d);
// Console.WriteLine(item.c);
//}
//Console.WriteLine(result.Count());
//Console.ReadKey();
JoinCriterion joinCri = new JoinCriterion();
Console.WriteLine("++" + joinCri.DefaultJoinFieldName + "++");
Console.ReadKey();
Criterion testcri = new Criterion("name", "", 1, CriteriaOperator.Equal);
Console.WriteLine(testcri.ParameterName);
Console.ReadKey();
//SqlServerDataFactory dataFactory = new SqlServerDataFactory("");
//SqlParameter a = new SqlParameter();
//SqlParameter b = new SqlParameter("", typeof(int));
//dataFactory.Query("", a, b);
//dataFactory.Query("");
//SqlParameter[] arry = { new SqlParameter(), new SqlParameter("", typeof(int)) };
//dataFactory.Query("", arry);
}
public IEnumerable <Report> GetByComplexQuery(ComplexQuery complexQuery)
{
throw new NotImplementedException();
}
