/// <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)); }