/// <summary>
/// 复杂检索数据库数据，返回泛型集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="complexQuery"></param>
/// <returns></returns>
public IEnumerable <T> Query <T>(ComplexQuery complexQuery)
{
IList <T> result = new List <T>();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
ComplexQueryTranslator.TranslateIntoComplexQuery(complexQuery, cmd);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(Mapper.DynamicMap <T>(reader));
}
}
catch (Exception ex)
{
throw new Exception(ex.Source + ":" + ex.Message);
}
}
return(result);
}
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();
}
/// <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");
}
/// <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.GetByFactoryId(factoryId);
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 "[]";
}
}
/// <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;
}
/// <summary>
/// 查询一张表中指定字段的数据
/// </summary>
/// <param name="complexQuery"></param>
/// <returns></returns>
public DataTable Query(ComplexQuery complexQuery)
{
DataTable result = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
ComplexQueryTranslator.TranslateIntoComplexQuery(complexQuery, cmd);
try
{
//conn.Open();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(result);
}
catch (Exception ex)
{
throw new Exception(ex.Source + ":" + ex.Message);
}
}
return(result);
}
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>
/// 获得视图中所有变量的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;
}
/// <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="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;
}
public string GetProductLineNameBy(int id)
{
ComplexQuery cmquery = new ComplexQuery();
cmquery.AddNeedField("ProductLine", "Name");
cmquery.AddCriterion("ID", id, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(cmquery);
return table.Rows[0][0].ToString().Trim();
}
public IEnumerable<Report> GetByComplexQuery(ComplexQuery complexQuery)
{
throw new NotImplementedException();
}