/// <summary>
/// 获取数据
/// </summary>
/// <param name="variableId"></param>
/// <param name="startTime"></param>
/// <param name="stopTime"></param>
/// <param name="timeSpanInMin"></param>
/// <returns></returns>
public IDictionary <string, decimal> GetData(string variableId, DateTime startTime, DateTime stopTime, int timeSpanInMin)
{
DataTable dt = new DataTable();
SqlServerDataFactory dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString);
VariableParams vp = new VariableParams(variableId);
string[] tagsArray = vp.VariableName.Split(',');//相关标签没有的空中，格式：tag1,tag2,,tag4,tag5,tag6,tag7
//去掉标签的dcs前缀
for (int i = 0; i < tagsArray.Length; i++)
{
if (tagsArray[i].Trim() == "")
{
continue;
}
int index = tagsArray[i].IndexOf('_');
tagsArray[i] = tagsArray[i].Substring(index + 1);
}
//if (tagsArray.Length != 7)
//{
// throw new Exception("RelatedTags标签不规范！");
//}
string mySql = @"select {3}.vDate,{0} from {1} Where {2} {3}.vDate>@startTime and {3}.vDate<@stopTime";
StringBuilder sqlBuilder = new StringBuilder(mySql);
StringBuilder fieldBuilder = new StringBuilder();
StringBuilder tableBuilder = new StringBuilder();
StringBuilder innerBuilder = new StringBuilder("(");
// 获取变量对应的表名与列名
// [0]：数据库名 [1]：表格名 [2]：列名
List <TagInfo> tableNameAndFieldName = GetTableNameAndFieldNameByVariableId(variableId);
List <string> tableList = new List <string>();
string template = "[{0}].[dbo].[{1}]";
foreach (TagInfo tagItem in tableNameAndFieldName)
{
string temp = string.Format(template, tagItem.DBName, tagItem.TableName);
if (!tableList.Contains(temp))
{
tableList.Add(temp);
}
fieldBuilder.Append(temp + ".[" + tagItem.FieldName + "],");
}
fieldBuilder.Remove(fieldBuilder.Length - 1, 1);
for (int i = 0; i < tableList.Count; i++)
{
string item = tableList[i];
tableBuilder.Append(item);
tableBuilder.Append(",");
if (i != 0)
{
innerBuilder.Append(tableList[0] + ".vDate =" + tableList[i] + ".vDate");
innerBuilder.Append(" or ");
}
}
tableBuilder.Remove(tableBuilder.Length - 1, 1);
if (tableList.Count > 1)
{
innerBuilder.Remove(innerBuilder.Length - 4, 4);
}
innerBuilder.Append(") and");
if (tableList.Count == 1)
{
innerBuilder.Clear();
}
SqlParameter[] parameters = { new SqlParameter("startTime", startTime), new SqlParameter("stopTime", stopTime) };
string m_SqlTemp = string.Format(mySql, fieldBuilder.ToString(), tableBuilder.ToString(), innerBuilder.ToString(), tableList[0]);
dt = dataFactory.Query(m_SqlTemp, parameters);
DataColumn resultColumn = new DataColumn("Result", typeof(int));
dt.Columns.Add(resultColumn);
foreach (DataRow dr in dt.Rows)
{
int num = tagsArray.Length;
int resultStatus = 0;
for (int i = 0; i < num; i++)
{
if (Convert.ToBoolean(dr[tagsArray[i].Trim()]))
{
resultStatus += (int)Math.Pow(2, num - 1 - i);
}
}
dr["Result"] = resultStatus;
#region
//if (tagsArray[5].Trim() != "" && Convert.ToBoolean(dr[tagsArray[5].Trim()]) == true)
//{
// dr["Result"] = 5;
//}
//else if (tagsArray[1].Trim() != "" && Convert.ToBoolean(dr[tagsArray[1].Trim()]) == true)
//{
// dr["Result"] = 1;
//}
//else if (tagsArray[2].Trim() != "" && Convert.ToBoolean(dr[tagsArray[2].Trim()]) == true)
//{
// dr["Result"] = 2;
//}
//else if (tagsArray[3].Trim() != "" && Convert.ToBoolean(dr[tagsArray[3].Trim()]) == true)
//{
// dr["Result"] = 3;
//}
//else if (tagsArray[4].Trim() != "" && Convert.ToBoolean(dr[tagsArray[4].Trim()]) == true)
//{
// dr["Result"] = 4;
//}
//else if (tagsArray[0].Trim() != "" && Convert.ToBoolean(dr[tagsArray[0].Trim()]) == true)
//{
// dr["Result"] = 0;
//}
//else if (tagsArray[6].Trim() != "" && Convert.ToBoolean(dr[tagsArray[6].Trim()]) == true)
//{
// dr["Result"] = 6;
//}
//else
//{
// dr["Result"] = 6;
//}
#endregion
}
//using (SqlConnection connection = new SqlConnection(this.connectionString))
//{
// SqlCommand command = connection.CreateCommand();
// command.CommandText = string.Format(COMMAND_FORMAT, tableNameAndFieldName[2], tableNameAndFieldName[0], tableNameAndFieldName[1]);
// command.Parameters.Add(new SqlParameter("startTime", startTime));
// command.Parameters.Add(new SqlParameter("stopTime", stopTime));
// command.Parameters.Add(new SqlParameter("organizationId", vp.OrganizationId));
// command.Parameters.Add(new SqlParameter("variableName", vp.VariableName));
// using (SqlDataAdapter adapter = new SqlDataAdapter(command))
// {
// adapter.Fill(dt);
// }
//}
DataTable resultTable = dt.DefaultView.ToTable(false, "vDate", "Result");
return(Utility.ConvertData(resultTable));
}
