public static DataTable GetRealTimeAlarmData(string organizationId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @"select C.[OrganizationID] ,B.LevelCode,B.Name+C.Name as Name ,C.[EnergyConsumptionType] ,C.[StartTime] as AlarmDateTime ,C.[TimeSpan] ,C.[LevelCode] ,C.[StandardValue] ,C.[ActualValue] ,C.[Superscale] ,C.[Reason] ,C.[VariableID] from system_TenDaysRealtimeAlarm A,system_Organization B,shift_EnergyConsumptionAlarmLog C where A.OrganizationID=B.OrganizationID and A.KeyId=C.EnergyConsumptionAlarmLogID and (A.AlarmType='EnergyConsumption' or A.AlarmType='Power' or A.AlarmType='CoalConsumption') and A.OrganizationID like @organizationId+'%' order by C.[StartTime] desc"; SqlParameter parameter = new SqlParameter("@organizationId", organizationId); DataTable table = dataFactory.Query(mySql, parameter); return table; }
public static DataTable GetHistoryAlarmData(string organizationId,string startTime,string endTime,string variableId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); List<SqlParameter> parameterList = new List<SqlParameter>(); string mySql = @" select A.[OrganizationID] ,B.LevelCode,B.Name+A.Name as Name ,A.[EnergyConsumptionType] ,A.[StartTime] as AlarmDateTime ,A.[TimeSpan] ,A.[LevelCode] ,A.[StandardValue] ,A.[ActualValue] ,A.[Superscale] ,A.[Reason] ,A.[VariableID] FROM [NXJC].[dbo].[shift_EnergyConsumptionAlarmLog] A,[NXJC].[dbo].[system_Organization] B where A.[OrganizationID]=B.[OrganizationID] and A.[OrganizationID] like @organizationId+'%' and (A.[StartTime]>=@startTime and A.[StartTime]<=@endTime) and A.[EnergyConsumptionType]=@variableId order by A.[StartTime] desc"; parameterList.Add(new SqlParameter("@startTime", startTime)); parameterList.Add(new SqlParameter("@endTime", endTime)); parameterList.Add(new SqlParameter("@organizationId", organizationId)); parameterList.Add(new SqlParameter("@variableId", variableId)); SqlParameter[] parameters = parameterList.ToArray(); DataTable table = dataFactory.Query(mySql, parameters); return table; }
public static string GetAmmeterDatabaseName(string organizationId) { if (ammeterDatabases.ContainsKey(organizationId)) { return ammeterDatabases[organizationId]; } string connectionString = NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string queryString = @"SELECT [system_Database].[MeterDatabase] FROM [system_Organization] INNER JOIN [system_Database] ON [system_Organization].[DatabaseID] = [system_Database].[DatabaseID] WHERE [system_Organization].[OrganizationID] = @organizationId "; SqlParameter[] parameters = new SqlParameter[]{ new SqlParameter("organizationId", organizationId) }; DataTable dt = dataFactory.Query(queryString, parameters); if (dt.Rows.Count == 0) { throw new ArgumentException("无该组织机构ID对应的数据"); } string ammeterDatabaseName = dt.Rows[0]["MeterDatabase"].ToString().Trim(); ammeterDatabases.Add(organizationId, ammeterDatabaseName); return ammeterDatabaseName; }
public static DataTable GetDataCollectionHistory(string origanizationId, string startTime, string endtime, string eventType) { string connnectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connnectionString); //********以下为正式代码 // string mySql = @"select vDate,TypeEvents,NameEvents from system_StatusLog // where OrganizationID=@organizaationID // and vDate>=@time // and vDate<=@time1 // and TypeEvents like '%'+@TypeEvent+ '%'"; //SqlParameter[] parameters ={ // new SqlParameter("organizaationID",origanizationId), // new SqlParameter("time",startTime), // new SqlParameter("time1",endtime), // new SqlParameter("TypeEvent",eventType), // }; //*******临时代码 string mySql = @"select vDate,TypeEvents,NameEvents from system_StatusLog where vDate>=@time and vDate<=@time1 and TypeEvents like '%'+@TypeEvent+ '%'"; SqlParameter[] parameters ={ new SqlParameter("time",startTime), new SqlParameter("time1",endtime), new SqlParameter("TypeEvent",eventType), }; //********* DataTable table = dataFactory.Query(mySql, parameters); return table; }
/// <summary> /// 辅助电量分摊 /// </summary> /// <param name="source">数据源表</param> /// <param name="result">结果表</param> /// <param name="monthKeyId">keyId(若为日均摊则为日keyId,若为月均摊则为月keyId)</param> public static void ToShare(DataTable source,DataTable result,string monthKeyId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); //*****计算分摊电量 string sqlShare = @"SELECT A.VariableId,A.OrganizationID,A.VariableName,A.ValueType,A.ValueFormula FROM balance_Energy_ShareTemplate AS A WHERE A.Enabled='True'"; DataTable template = dataFactory.Query(sqlShare); string[] arrayFields = { "TotalPeakValleyFlat", "MorePeak", "Peak", "Valley", "MoreValley", "Flat", "First", "Second", "Third" , "TotalPeakValleyFlatB", "MorePeakB", "PeakB", "ValleyB", "MoreValleyB", "FlatB", "FirstB", "SecondB", "ThirdB"}; //平衡 string[] arrayFieldsB = { "TotalPeakValleyFlatB", "MorePeakB", "PeakB", "ValleyB", "MoreValleyB", "FlatB", "FirstB", "SecondB", "ThirdB" }; DataTable share = ShareCalculateService.CalculateByOrganizationId(source, template, "ValueFormula", arrayFields); //END string sqlLine = @"SELECT A.OrganizationID,(B.VariableId+'_ElectricityQuantity') AS VariableId FROM tz_Formula AS A,formula_FormulaDetail AS B WHERE A.KeyID=B.KeyID AND B.LevelType='ProductionLine'"; //找出产线级别的数据 DataTable productLine = dataFactory.Query(sqlLine); foreach (DataRow dr in productLine.Rows) { foreach (DataRow row in result.Rows) { //只有等于产线级别的数据才做处理 if (dr["VariableId"].ToString().Trim() == row["VariableId"].ToString().Trim() && dr["OrganizationID"].ToString().Trim() == row["OrganizationID"].ToString().Trim()) { //找出分摊的 DataRow[] myRows = share.Select("OrganizationID='" + dr["OrganizationID"].ToString().Trim() + "'"); foreach (DataRow shareRow in myRows) { //循环列(带B的) foreach (string item in arrayFieldsB) { //将分摊的电量加到产线上 row[item] = ShareCalculateService.MyToDecimal(row[item]) + ShareCalculateService.MyToDecimal(shareRow[item]); } } } } } foreach (DataRow dr in share.Rows) { DataRow row = result.NewRow(); row["VariableItemId"] = Guid.NewGuid().ToString(); row["PublicVariableId"] = row["KeyId"] = monthKeyId; row["VariableId"] = dr["VariableId"]; row["VariableName"] = dr["VariableName"]; row["OrganizationID"] = dr["OrganizationID"]; row["ValueType"] = dr["ValueType"]; foreach (string item in arrayFields) { row[item] = dr[item]; } result.Rows.Add(row); } }
/// <summary> /// 获取设备停机原因字典 /// </summary> /// <returns></returns> public static DataTable GetMachineHaltReason() { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("MachineHaltReason"); return factory.Query(query); }
public static DataTable GetAmmetersByFactoryId(int factoryId) { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("AmmeterContrast"); return factory.Query(query); }
public static DataTable GetProductLines() { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("ProductLine"); return factory.Query(query); }
public static DataTable GetDailyProductionData(string myOrganizationId, string myDateTime) { ISqlServerDataFactory _dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString); DataTable m_EquipmentCommonInfoTable = GetEquipmentCommonInfo(myOrganizationId, _dataFactory); GetDailyProductionPlanData(ref m_EquipmentCommonInfoTable, myOrganizationId, myDateTime, _dataFactory); GetOutputData(ref m_EquipmentCommonInfoTable, myOrganizationId, myDateTime, _dataFactory); GetRunTimeData(ref m_EquipmentCommonInfoTable, myOrganizationId, myDateTime, _dataFactory); return m_EquipmentCommonInfoTable; }
/// <summary> /// 获取停机记录 /// </summary> /// <param name="productLineId"></param> /// <param name="start"></param> /// <param name="end"></param> /// <returns></returns> public static DataTable GetHaltLog(int productLineId) { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("MachineHaltLog"); return factory.Query(query); }
/// <summary> /// 获取员工信息 /// </summary> /// <returns></returns> public static DataTable GetStaffInfo() { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("StaffInfo"); return factory.Query(query); }
public static void ProcessMartieralsClass(DataTable balanceTable) { Dictionary<string, string> dictionary = new Dictionary<string, string>(); dictionary.Add("甲班", "FirstB"); dictionary.Add("乙班", "SecondB"); dictionary.Add("丙班", "ThirdB"); string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @"select B.ShiftDate,A.Class,A.OrganizationID, A.VariableId, A.DataValue from balance_BalanceMartieralsClass A,shift_WorkingTeamShiftLog B where A.WorkingTeamShiftLogID=B.WorkingTeamShiftLogID and CONVERT(varchar(10),B.ShiftDate,20)=@date"; SingleBasicData singleBasicData = SingleBasicData.Creat(); string date = singleBasicData.Date; SqlParameter parameter = new SqlParameter("date", date); DataTable sourceTable = dataFactory.Query(mySql, parameter); foreach (DataRow dr in sourceTable.Rows) { //信息列表 string t_class = dr["Class"].ToString().Trim(); string t_classField = dictionary[t_class];//班组对应的字段 string t_organizationId = dr["OrganizationID"].ToString().Trim(); string t_variableId = dr["VariableId"].ToString().Trim(); decimal t_value = 0; decimal.TryParse(dr["DataValue"].ToString().Trim(), out t_value); // DataRow[] rows = balanceTable.Select(string.Format("OrganizationID='{0}' and VariableId='{1}'",t_organizationId,t_variableId)); if (rows.Count() == 0) { continue; } else if (rows.Count() >= 2) { throw new Exception("数据错误"); } else if(rows.Count()==1) { rows[0][t_classField] = t_value;//将值写到balanceTable中 decimal total=Convert.ToDecimal(rows[0]["TotalPeakValleyFlat"]); //各个期所占比例 decimal peakRatio = total == 0 ? 0 : Convert.ToDecimal(rows[0]["Peak"]) / total; //峰期 decimal morePeakRatio = total == 0 ? 0 : Convert.ToDecimal(rows[0]["MorePeak"]) / total; //深峰期 decimal valleyRatio = total == 0 ? 0 : Convert.ToDecimal(rows[0]["Valley"]) / total; //谷期 decimal moreValleyRatio = total == 0 ? 0 : Convert.ToDecimal(rows[0]["MoreValley"]) / total; //深谷 decimal flatRatio = total == 0 ? 0 : Convert.ToDecimal(rows[0]["Flat"]) / total; //平期 rows[0]["TotalPeakValleyFlatB"] = Convert.ToDecimal(rows[0]["FirstB"]) + Convert.ToDecimal(rows[0]["SecondB"]) + Convert.ToDecimal(rows[0]["ThirdB"]); //峰谷平分摊盘库量 rows[0]["PeakB"] = Convert.ToDecimal(rows[0]["TotalPeakValleyFlatB"]) * peakRatio; rows[0]["MorePeakB"] = Convert.ToDecimal(rows[0]["TotalPeakValleyFlatB"]) * morePeakRatio; rows[0]["ValleyB"] = Convert.ToDecimal(rows[0]["TotalPeakValleyFlatB"]) * valleyRatio; rows[0]["MoreValleyB"] = Convert.ToDecimal(rows[0]["TotalPeakValleyFlatB"]) * moreValleyRatio; rows[0]["FlatB"] = Convert.ToDecimal(rows[0]["TotalPeakValleyFlatB"]) * flatRatio; } } }
/// <summary> /// 按分厂ID获取所有公式组 /// </summary> /// <param name="factoryId"></param> /// <returns></returns> public static DataTable GetFormulaGroupsByFactoryId(int factoryId) { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("FormulaGroup"); query.AddCriterion("FactoryID", factoryId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal); return factory.Query(query); }
/// <summary> /// 按生产线ID获取所电耗报警设置 /// </summary> /// <param name="productLineId"></param> /// <returns></returns> public static DataTable GetElectricityConsumptionAlarmSettingsByFactoryId(int productLineId) { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("ElectricityConsumptionAlarmParameterSetting"); query.AddCriterion("ProductLineID", productLineId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal); return factory.Query(query); }
/// <summary> /// 按生产线ID获取班组 /// </summary> /// <param name="productLineId"></param> /// <returns></returns> public static DataTable GetWorkingTeamByProductLineId(int productLineId) { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("WorkingTeam"); query.AddCriterion("ProductLineID", productLineId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal); return factory.Query(query); }
/// <summary> /// 按起始时间段获取停机记录 /// </summary> /// <param name="productLineId"></param> /// <param name="start"></param> /// <param name="end"></param> /// <returns></returns> public static DataTable GetHaltLog(int productLineId, DateTime start) { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("MachineHaltLog"); query.AddCriterion("HaltTime", start, SqlServerDataAdapter.Infrastruction.CriteriaOperator.MoreThanOrEqual); return factory.Query(query); }
public static Dictionary<string, decimal> GetReportData(string organizationId, string dateTime) { ISqlServerDataFactory _dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString); Dictionary<string, decimal> dictionary = new Dictionary<string, decimal>(); //时间处理 string[] timeArray = dateTime.Split('-'); //月时间 DateTime monthStart = new DateTime(Convert.ToInt16(timeArray[0]), Convert.ToInt16(timeArray[1]), 1); DateTime monthEnd = monthStart.AddMonths(1).AddDays(-1); //年时间 DateTime yearStart = new DateTime(Convert.ToInt16(timeArray[0]),1,1); DateTime yearEnd = yearStart.AddYears(1).AddDays(-1); string mySql = @"select B.VariableId,SUM(B.TotalPeakValleyFlatB) as Value from tz_Balance A, balance_Energy B,system_Organization C, (select LevelCode from system_Organization A where OrganizationID=@organizationId) D where A.BalanceId=B.KeyId and B.OrganizationID=C.OrganizationID and B.ValueType='MaterialWeight' and C.LevelCode like D.LevelCode+'%' and A.TimeStamp>@startDate and A.TimeStamp<@endDate group by B.VariableId"; SqlParameter[] monthParameters = { new SqlParameter("organizationId", organizationId), new SqlParameter("startDate",monthStart.ToString("yyyy-MM-dd")), new SqlParameter("endDate",monthEnd.ToString("yyyy-MM-dd"))}; DataTable monthData = _dataFactory.Query(mySql, monthParameters); foreach (DataRow dr in monthData.Rows) { if (!dictionary.Keys.Contains(dr["VariableId"].ToString().Trim() + ">month")) { dictionary.Add(dr["VariableId"].ToString().Trim() + ">month", dr["value"] is DBNull ? 0 : Convert.ToDecimal(dr["value"])); } } SqlParameter[] yeraParameters ={ new SqlParameter("organizationId", organizationId), new SqlParameter("startDate",yearStart.ToString("yyyy-MM-dd")), new SqlParameter("endDate",yearEnd.ToString("yyyy-MM-dd"))}; DataTable yearData = _dataFactory.Query(mySql, yeraParameters); foreach (DataRow dr in yearData.Rows) { if (!dictionary.Keys.Contains(dr["VariableId"].ToString().Trim() + ">month")) { dictionary.Add(dr["VariableId"].ToString().Trim() + ">year", dr["value"] is DBNull ? 0 : Convert.ToDecimal(dr["value"])); } } //DataTable result = new DataTable(); //result = monthData.Copy(); //result.Merge(yearData); return dictionary; }
private void btnTest_Click(object sender, EventArgs e) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); SingleBasicData singleBasicData = SingleBasicData.Creat(); DateTime date = new DateTime(2015, 02, 02); singleBasicData.Init("zc_nxjc_byc_byf","2015-02-02"); SingleTimeService singleTimeService = SingleTimeService.Creat(); singleTimeService.Init(dataFactory); DataTable table = DailyMaterialChangeSummation.GetMaterialChange(); }
private void InitializeDictionary() { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("ViewsDictionary"); DataTable dt = factory.Query(query); foreach (DataRow row in dt.Rows) dictionary.Add(row["English"].ToString(), row["Chinese"].ToString()); }
public static DataTable GetHistoryHaltAlarmData(string organizationId, string MainMachine, string startTime, string endTime) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @"select A.OrganizationID as LevelCode,'LeafNode' as Type,B.Name,A.EquipmentName,A.Label as MasterLabel,A.StartTime as StartTime,A.ReasonText,A.HaltTime as HaltTime,A.RecoverTime, convert(varchar,DATEDIFF(MINUTE,A.StartTime,A.HaltTime)/60/24)+'天'+convert(varchar,DATEDIFF(Minute,A.StartTime,A.HaltTime)/60-DATEDIFF(MINUTE,A.StartTime,A.HaltTime)/60/24*24)+'时'+convert(varchar,DATEDIFF(minute,A.StartTime,A.HaltTime)-DATEDIFF(minute,A.StartTime,A.HaltTime)/60/24*24*60-(DATEDIFF(Minute,A.StartTime,A.HaltTime)/60-DATEDIFF(MINUTE,A.StartTime,A.HaltTime)/60/24*24)*60)+'分' as RunTime from [dbo].[shift_MachineHaltLog] A,system_Organization B where A.OrganizationID=B.OrganizationID and A.OrganizationID=@organizationId and A.StartTime>=@startTime and A.StartTime<=@endTime and A.Label=@mainMachine group by A.EquipmentName,A.StartTime,A.HaltTime,A.OrganizationID,LevelCode,B.Name,A.Label,A.ReasonText,Type,A.RecoverTime order by EquipmentName,StartTime desc"; SqlParameter[] parameters = { new SqlParameter("@organizationId", organizationId), new SqlParameter("@startTime", startTime), new SqlParameter("@endTime", endTime), new SqlParameter("@mainMachine", MainMachine) }; DataTable originalTable = dataFactory.Query(mySql, parameters); int length = originalTable.Rows.Count; if (length > 1) { for(int j=0;j<length;j++) { if (Convert.ToString(originalTable.Rows[0]["HaltTime"]) != "") { TimeSpan runningSpan = Convert.ToDateTime(originalTable.Rows[0]["HaltTime"]) - Convert.ToDateTime(originalTable.Rows[0]["StartTime"]); string runningTime = runningSpan.Days.ToString() + "天" + runningSpan.Hours.ToString() + "时" + runningSpan.Minutes.ToString() + "分"; originalTable.Rows[0]["RunTime"] = runningTime; }else { TimeSpan runningSpan = DateTime.Now - Convert.ToDateTime(originalTable.Rows[0]["StartTime"]); string runningTime = runningSpan.Days.ToString() + "天" + runningSpan.Hours.ToString() + "时" + runningSpan.Minutes.ToString() + "分"; originalTable.Rows[0]["HaltTime"] = DBNull.Value; originalTable.Rows[0]["RunTime"] = runningTime; } } } else { DataRow[] rows = originalTable.Select("LevelCode='" + organizationId + "'and MasterLabel='" + MainMachine + "'"); foreach (DataRow rw in rows) { originalTable.Rows.Remove(rw); } originalTable.AcceptChanges(); } return originalTable; }
/// <summary> /// 根据组织机构查询工序名称和设备名称 /// </summary> /// <param name="organizationId"></param> /// <returns></returns> public static DataTable GetAlarmItem(string organizationId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @"select A.OrganizationID,B.VariableId,B.LevelCode,B.Name as text,C.Type, case when len(B.LevelCode)=5 then 'closed' else 'open' end as state from [dbo].[tz_Formula] A,[dbo].[formula_FormulaDetail] B,system_Organization C where A.KeyID=B.KeyID and A.OrganizationID=C.OrganizationID and A.OrganizationID=@organizationId order by OrganizationID,B.LevelCode"; SqlParameter parameter = new SqlParameter("@organizationId", organizationId); DataTable table = dataFactory.Query(mySql, parameter); return table; }
/// <summary> /// 按生产线ID获取标签 /// </summary> /// <param name="productLineId"></param> /// <returns></returns> public static DataTable GetLabelsByProductLineId(int productLineId) { string connectionString = ConnectionStringFactory.GetByProductLineId(productLineId); ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("ContrastTable"); DataTable dt = factory.Query(query); foreach (DataRow row in dt.Rows) { row["ViewName"] = ViewsDictionary.Current.GetChineseSafely(row["ViewName"].ToString().Trim()); } return dt; }
public static DateTime GetLogTime(string Ip) { string connnectionString = ConnectionStringFactory.NXJCConnectionString;//类下边的一个属性 返回连接字符串 ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connnectionString);//定义一个接口 string mySql = @"select A.IP, max(convert(varchar(19),A.vDate,20)) as vDate from system_StatusLog A where A.IP=@ip and A.TypeEvents='正常通知' group by A.IP"; SqlParameter parameter = new SqlParameter("ip", Ip); DataTable table1 = dataFactory.Query(mySql, parameter); DateTime lastTime = Convert.ToDateTime(table1.Rows[0]["vDate"]); return lastTime; }
public static DataTable GetAlarmData(string organizationId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @"select A.KeyID,A.OrganizationID,B.LevelCode,B.VariableId,B.Name,B.AlarmType,B.EnergyAlarmValue,B.AlarmType as AlarmTypeE, B.CoalDustConsumptionAlarm,B.AlarmType as AlarmTypeP,B.PowerAlarmValue,B.AlarmType as AlarmTypeC from tz_Formula A,formula_FormulaDetail B,system_Organization C where A.KeyID=B.KeyID and A.OrganizationID=C.OrganizationID and A.OrganizationID=@organizationId and B.VariableId<>(case when C.Type ='熟料' then 'clinkerElectricityGeneration' else '' end) and B.VariableId<>(case when C.Type ='熟料' then 'electricityOwnDemand' else '' end)"; // and C.LevelCode like (SELECT LevelCode FROM system_Organization where OrganizationID=@organizationId)+'%' // order by B.LevelCode"; SqlParameter parameter = new SqlParameter("organizationId",organizationId); DataTable alarmInfoTable=dataFactory.Query(mySql,parameter); //报警类型名 DataColumn alarmTypeColumn = new DataColumn("AlarmTypeName",typeof(string)); alarmTypeColumn.DefaultValue = "无"; alarmInfoTable.Columns.Add(alarmTypeColumn); foreach (DataRow dr in alarmInfoTable.Rows) { string alarmType = dr["AlarmType"].ToString(); switch (alarmType) { case "1": dr["AlarmTypeName"] = "能耗报警"; break; case "2": dr["AlarmTypeName"] = "功率报警"; break; case "3": dr["AlarmTypeName"] = "能耗报警,功率报警"; break; default: dr["AlarmTypeName"] = "无"; break; } //只有熟料产线的产线级别才有煤耗报警 if (dr["VariableId"].ToString().Trim() == "clinker") { dr["AlarmTypeName"] = dr["AlarmTypeName"].ToString().Trim() + ",煤耗报警"; } } return alarmInfoTable; }
/// <summary> /// 根据KeyId和日期保存数据 /// </summary> /// <param name="organizationId">[分厂]组织机构ID</param> /// <param name="keyId">引领表KeyId</param> /// <param name="saveDate">保存日期</param> public static void SaveMonthBalanceDetailData(string myFactoryOrganizationId, DateTime saveDate) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); SingleBasicData singleBasicData = SingleBasicData.Creat(); singleBasicData.Init(myFactoryOrganizationId, saveDate.ToString("yyyy-MM-dd")); singleBasicData.InitMonthlyData(saveDate); SingleTimeService singleTimeService = SingleTimeService.Creat(); singleTimeService.Init(dataFactory); DataTable tzBalance = TzBalanceService.GetMonthyTzBalance(); //电量(包括分摊电量,产线级别的电量带B的字段为加上分摊电量后的值)产量表 DataTable electricityMaterialWeight = MonthlyService.GetElectricityQuantityMaterialWeight(); string sql = @"SELECT A.VariableId,B.OrganizationID,(B.Name+A.VariableName) AS Name,A.ValueType,A.ValueFormula FROM balance_Energy_Template AS A,system_Organization AS B WHERE A.ProductionLineType=B.Type AND (A.ValueType='ElectricityConsumption' OR A.ValueType='CoalConsumption') AND A.Enabled='True' AND B.LevelCode like(select LevelCode from system_Organization where OrganizationID='{0}')+'%'"; DataTable template = dataFactory.Query(string.Format(sql, singleBasicData.OrganizationId)); string[] columns ={"TotalPeakValleyFlat", "MorePeak", "Peak", "Valley", "MoreValley", "Flat", "First", "Second", "Third", "TotalPeakValleyFlatB", "MorePeakB", "PeakB", "ValleyB", "MoreValleyB", "FlatB", "FirstB", "SecondB", "ThirdB"}; DataTable consumptionTemp = EnergyConsumption.EnergyConsumptionCalculate.CalculateByOrganizationId(electricityMaterialWeight, template, "ValueFormula", columns); DataTable consumption = singleBasicData.BalanceTable.Clone(); foreach (DataRow dr in consumptionTemp.Rows) { DataRow row = consumption.NewRow(); row["VariableItemId"] = Guid.NewGuid().ToString(); foreach (DataColumn item in consumptionTemp.Columns) { string name = item.ColumnName; if (name == "ValueFormula") continue; if (name == "Name") row["VariableName"] = dr[name]; else row[name] = dr[name]; row["PublicVariableId"] = row["KeyId"] = singleBasicData.MonthlyKeyId; } consumption.Rows.Add(row); } electricityMaterialWeight.Merge(consumption); int w = dataFactory.Save("tz_Balance", tzBalance); int n = dataFactory.Save("balance_Energy", electricityMaterialWeight); }
/// <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 static DataTable GetDataInfo(string[] levelCodes) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @"select O.Name,Servers,A.IP,A.Timestamp as Timestamp,A.Status from system_StatusNet A, ( SELECT A.OrganizationID,A.Name FROM system_Organization as A WHERE {0} ) AS O where A.BranchFactory=O.OrganizationID order by A.BranchFactory,A.Servers DESC"; //SqlParameter parameter = new SqlParameter("organizationId", organizationId); StringBuilder levelCodesParameter = new StringBuilder(); foreach (var levelCode in levelCodes) { levelCodesParameter.Append("A.LevelCode like "); levelCodesParameter.Append("'"); levelCodesParameter.Append(levelCode + "%");//这用的右like 只要左边的符合条件 就查出所有的 组织机构分厂是O2 下边产线 都是O2开头 levelCodesParameter.Append("'"); levelCodesParameter.Append(" OR "); //这个or这儿 左右两边各加了一个空格符 所以下一句去除的时候 去了四个字符 A。levecode like ‘levelcode "%" ' or A.levelcode... or . } levelCodesParameter.Remove(levelCodesParameter.Length - 4, 4); DataTable table = dataFactory.Query(string.Format(mySql, levelCodesParameter.ToString())); //DataTable table = dataFactory.Query(mySql, parameter); //DataRow row = table.NewRow(); //创建与此表相同架构的 新的行 if (table.Rows.Count > 0) { foreach (DataRow dr in table.Rows) { DateTime columntime = Convert.ToDateTime(dr["Timestamp"]); TimeSpan timespan = DateTime.Now - columntime;//时间间隔类型 double secondtimespan = timespan.TotalSeconds; if (secondtimespan > 30) { dr["Status"] = "不能连接"; string ip = Convert.ToString(dr["IP"]); DateTime time1 = DataLog.GetLogTime(ip);//寻找失联时间 dr["Timestamp"] = time1; } } } return table; }
public static DataTable GetMainMachineClassList(string mOrganizationID) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @" SELECT A.[EquipmentId] ,A.[EquipmentName] ,A.[EquipmentCommonId] ,B.[OrganizationID]+','+B.[VariableName] as Variable ,B.[VariableDescription] FROM [NXJC].[dbo].[equipment_EquipmentDetail] A, [NXJC].[dbo].[system_MasterMachineDescription] B where A.[EquipmentId]=B.[ID] and A.[Enabled]=1 and A.[OrganizationId]=@mOrganizationID order by A.DisplayIndex "; SqlParameter param = new SqlParameter("@mOrganizationID", mOrganizationID); DataTable Table = dataFactory.Query(mySql, param); return Table; }
//public static DataTable JsonToDataTable(string json) //{ // //string[] rowsData = EasyUIJsonParser.Utility.JsonPickArray(json, "rows"); // return EasyUIJsonParser.TreeGridJsonParser.JsonToDataTable(json); //} public static int SaveAlarmValues(string organizationId, DataTable saveDataTable) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); int m_UpdateRowCount = 0; if (saveDataTable != null) { saveDataTable.Columns.Remove("id"); saveDataTable.Columns.Remove("OrganizationID"); saveDataTable.Columns.Remove("Name"); saveDataTable.Columns.Remove("AlarmType"); saveDataTable.Columns.Remove("AlarmTypeName"); m_UpdateRowCount = dataFactory.Update("formula_FormulaDetail", saveDataTable, new string[] { "KeyID", "VariableId", "LevelCode" }); RestartDataCollection(organizationId); //自动重启数采软件 } return m_UpdateRowCount; }
private void button1_Click(object sender, EventArgs e) { //string connectionString = "Data Source=192.168.186.48;Initial Catalog=NXJC;User ID=sa;Password=111"; string connectionString = "Data Source=CORPHISH;Initial Catalog=NXJC;User ID=sa;Password=cdy"; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string sqlSource = @"SELECT B.OrganizationID, B.VariableId,B.[First],B.[Second],B.[Third] FROM tz_Balance AS A,balance_Energy AS B WHERE A.BalanceId=B.KeyId AND A.TimeStamp='2015-02-11' AND B.ValueType<>'ElectricityConsumption' --AND B.OrganizationID='zc_nxjc_byc_byf_clinker01'"; string sqlTemplate = @"SELECT A.VariableName AS VariableName,A.ValueFormula AS ValueFormula FROM balance_Energy_Template AS A WHERE A.ValueType='ElectricityConsumption'"; DataTable source = dataFactory.Query(sqlSource); DataTable teplate = dataFactory.Query(sqlTemplate); string[] columns={"First","Second"}; // DataTable result= EnergyConsumption.EnergyConsumptionCalculate.Calculate(source, teplate, "ValueFormula", columns); string sqlTemplete02 = @"SELECT A.VariableId,B.OrganizationID,(B.Name+A.VariableName) AS Name,A.ValueType,A.ValueFormula FROM balance_Energy_Template AS A,system_Organization AS B WHERE A.ProductionLineType=B.Type AND A.ValueType='ElectricityConsumption' AND A.Enabled='True'"; DataTable template02 = dataFactory.Query(sqlTemplete02); DataTable result02 = EnergyConsumption.EnergyConsumptionCalculate.CalculateByOrganizationId(source, template02, "ValueFormula", columns); string sqlTemplate03 = @"SELECT SO.Name,SO.LevelCode,DETAIL.* FROM system_Organization AS SO LEFT JOIN ( SELECT A.OrganizationID,A.Name,B.VariableName,D.LevelCode,B.ValueFormula FROM system_Organization AS A,balance_Energy_Template AS B,tz_Formula AS C,formula_FormulaDetail AS D WHERE A.Type=B.ProductionLineType AND A.OrganizationID=C.OrganizationID AND C.KeyID=D.KeyID AND D.VariableId+'_ElectricityConsumption'=B.VariableId AND A.Type<>'余热发电' AND C.Type=2 AND C.ENABLE='True' AND C.State=0 ) AS DETAIL ON SO.OrganizationID=DETAIL.OrganizationID WHERE SO.LevelCode LIKE 'O03%' AND ISNULL(SO.Type,'')<>'余热发电' ORDER BY SO.LevelCode,DETAIL.LevelCode"; DataTable template03 = dataFactory.Query(sqlTemplate03); DataTable result03 = EnergyConsumption.EnergyConsumptionCalculate.CalculateByOrganizationId(source, template03, "ValueFormula", columns); }
public static DataTable GetAssessmentResultdetailTable(string mAssessmentId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); string mySql = @"SELECT A.[Id] ,A.[AssessmentId] ,B.[Name] ,A.[ObjectId] ,A.[OrganizaitonID] ,A.[WeightedValue] ,A.[BestValue] ,A.[WorstValue] ,A.[AssessmenScore] ,A.[WeightedAverageCredit] FROM [dbo].[assessment_ShiftAssessmentResultDetail] A,[dbo].[assessment_AssessmentCatalogue] B where A.[AssessmentId]=B.[AssessmentId] and A.KeyId=@mAssessmentId"; SqlParameter para = new SqlParameter("@mAssessmentId", mAssessmentId); DataTable dt = factory.Query(mySql, para); return(dt); }
public static DataTable GetPublisherMonitorListTable(string mlinkServer, string mpublicattionId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); DataTable table = new DataTable(); if (mpublicattionId.Equals("")) { string mySql = @"select '['+[publisher_db]+']:'+ [publication] as publisher, * from [{0}].[distribution].[dbo].[MSpublications] order by publisher_db"; mySql = string.Format(mySql, mlinkServer); table = dataFactory.Query(mySql); } else { string mySql = @"select '['+[publisher_db]+']:'+ [publication] as publisher, * from [{0}].[distribution].[dbo].[MSpublications] where publication_id={1} order by publisher_db"; mySql = string.Format(mySql, mlinkServer, mpublicattionId); table = dataFactory.Query(mySql); } return(table); }
/// <summary> /// 根据组织机构ID获得十天实时报警信息 /// </summary> /// <param name="organizationId">组织机构ID</param> /// <returns></returns> public static DataTable GetRealtimeAlarmByOrganizationId(string organizationId) { string connectionstring = ConnectionStringFactory.NXJCConnectionString; SqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionstring); string SqlStr = @"SELECT ST.OrganizationID,SE.Name FROM system_TenDaysRealtimeAlarm AS ST, shift_EnergyConsumptionAlarmLog AS SE WHERE ST.KeyId=SE.EnergyConsumptionAlarmLogID AND ST.AlarmType='EnergyConsumption' AND ST.Eanbled='true' AND ST.OrganizationID IN ( SELECT SO.OrganizationID FROM system_Organization AS SO WHERE SO.LevelCode LIKE ( SELECT LevelCode FROM system_Organization WHERE OrganizationID=@organizationId)+'%' ) "; SqlParameter parameter = new SqlParameter("organizationId", organizationId); DataTable resultTable = _dataFactory.Query(SqlStr, parameter); //DataTable resultTable = new DataTable(); return(resultTable); }
public static int ToAddAssessmentVersion(string mProductionID, string mWorkingSectionID, string mName, string mType, string mCreator, string mRemark) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); string mySql = @"INSERT INTO [dbo].[tz_Assessment] ([KeyId] ,[Name] ,[Type] ,[OrganizationID] ,[WorkingSectionID] ,[Remark] ,[Creator] ,[CreateTime]) VALUES (@mKeyId ,@mName ,@mType ,@mProductionID ,@mWorkingSectionID ,@mRemark ,@mCreator ,@mTime)"; SqlParameter[] para = { new SqlParameter("@mKeyId", System.Guid.NewGuid().ToString()), new SqlParameter("@mProductionID", mProductionID), new SqlParameter("@mWorkingSectionID", mWorkingSectionID), new SqlParameter("@mName", mName), new SqlParameter("@mType", mType), new SqlParameter("@mCreator", mCreator), new SqlParameter("@mRemark", mRemark), new SqlParameter("@mTime", DateTime.Now.ToString()) }; int result = factory.ExecuteSQL(mySql, para); return(result); }
public static int EditSectionWorking(string mShiftDescriptionID, string mWorkingSectionID, string mShifts, string mStartTime, string mEndTime, string mRemark) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); string mySql = @"UPDATE [dbo].[system_WorkingSectionShiftDescription] SET [WorkingSectionID] = @mWorkingSectionID ,[Shifts] =@mShifts ,[StartTime] =@mStartTime ,[EndTime] = @mEndTime ,[Remark] = @mRemark WHERE [ShiftDescriptionID] =@mShiftDescriptionID"; SqlParameter[] para = { new SqlParameter("@mShiftDescriptionID", mShiftDescriptionID), new SqlParameter("@mWorkingSectionID", mWorkingSectionID), new SqlParameter("@mShifts", mShifts), new SqlParameter("@mStartTime", mStartTime), new SqlParameter("@mEndTime", mEndTime), new SqlParameter("@mRemark", mRemark) }; int dt = factory.ExecuteSQL(mySql, para); return(dt); }
/// <summary> /// 按分厂ID获取数据库名(优先从缓存中查找) /// </summary> /// <param name="factoryID"></param> /// <returns></returns> private static string GetCatalogByFactoryID(int factoryID) { if (MeterCatalogDaictionary.ContainsKey(factoryID)) { return(MeterCatalogDaictionary[factoryID]); } string connectionString = ConfigurationManager.ConnectionStrings["ManagementData"].ConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("DataBaseContrast"); query.AddCriterion("FactoryID", factoryID, CriteriaOperator.Equal); DataTable dt = factory.Query(query); if (dt.Rows.Count == 0) { throw new ApplicationException("没有此分公司"); } MeterCatalogDaictionary.Add(factoryID, dt.Rows[0]["DataBaseName"].ToString().Trim()); return(MeterCatalogDaictionary[factoryID]); }
/// <summary> /// 保存停机原因 /// </summary> public static void SaveMachineHaltReasons(DataTable data) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Delete delete = new Delete("system_MachineHaltReason"); factory.Remove(delete); Query query = new Query("system_MachineHaltReason"); DataTable m_ReasonTable = factory.Query(query); if (query != null) { data.Columns.Add("OrganizationID", typeof(string)); foreach (DataColumn Column in m_ReasonTable.Columns) { int m_ColumnIndex = Column.Ordinal; data.Columns[Column.ColumnName].SetOrdinal(m_ColumnIndex); } } factory.Save("system_MachineHaltReason", data); }
public static DataTable GetPowerAvgDataTable(string organizationId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @"SELECT A.[EquipmentGroupId] ,A.[Name] ,A.[OrganizationID] ,A.[Formula] ,A.[MaterialId] ,A.[DisplayIndex] ,A.[Enabled] ,B.[Name]+C.[Name] as [NewName] FROM [dbo].[equipment_CustomerPowerContrast] A,[dbo].[tz_Material] B,[dbo].[material_MaterialDetail] C where A.[OrganizationID]='zc_nxjc_byc_byf' and A.[MaterialId]=C.[MaterialId] and B.KeyID=C.KeyID and A.[Enabled]=1 order by [DisplayIndex]"; SqlParameter sqlParameter = new SqlParameter("@organizationId", organizationId); DataTable table = dataFactory.Query(mySql, sqlParameter); return(table); }
/// <summary> /// 删除设备 /// </summary> /// <param name="variableId"></param> /// <param name="organizationId"></param> /// <returns></returns> public static string RemoveEquipment(string variableId, string organizationId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string removeSql = @"DELETE FROM system_EquipmentAccount WHERE VariableId=@VariableId AND OrganizationID=@OrganizationID"; SqlParameter[] removeParameters = { new SqlParameter("VariableId", variableId), new SqlParameter("OrganizationID", organizationId) }; int n = dataFactory.ExecuteSQL(removeSql, removeParameters); string removeFormulaSql = @"DELETE FROM formula_FormulaDetail WHERE VariableId=@VariableId AND KeyID=(select KeyID from tz_Material where OrganizationID=@OrganizationID)"; SqlParameter[] removeFormulaParameters = { new SqlParameter("VariableId", variableId), new SqlParameter("OrganizationID", organizationId) }; int m = dataFactory.ExecuteSQL(removeFormulaSql, removeFormulaParameters); if (0 == n) { return("删除设备失败"); } else { return("删除成功"); } }
public static DataTable GetAssessmentVersionDefine(string mOrganizationId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); string mySql = @"SELECT A.[KeyId] ,A.[Name] ,A.[Type] ,B.[Name] as [OrganizationName] ,A.[OrganizationID] ,C.[WorkingSectionName] ,A.[WorkingSectionID] ,A.[Remark] ,A.[Creator] ,A.[CreateTime] FROM [dbo].[tz_Assessment] A,[dbo].[system_Organization] B,[dbo].[system_WorkingSection] C where A.[OrganizationID]=B.[OrganizationID] and A.[WorkingSectionID]=C.[WorkingSectionID] and A.[OrganizationID]=@mOrganizationId"; SqlParameter para = new SqlParameter("@mOrganizationId", mOrganizationId); DataTable table = factory.Query(mySql, para); return(table); }
public static StatisticResult GetAmmeterStatisticData(string organizationId, string variableId) { string nxjcConn = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory nxjcFactory = new SqlServerDataFactory(nxjcConn); string ammeterConn = ConnectionStringFactory.GetAmmeterConnectionString(organizationId); ISqlServerDataFactory ammeterFactory = new SqlServerDataFactory(ammeterConn); MeterStatisticsHelper meterStatistics = new MeterStatisticsHelper(nxjcFactory, ammeterFactory); FormulaHelper formulaHelper = new FormulaHelper(); VariableInfo variableInfo = GetLevelCodeByOrganizationId(organizationId, variableId); formulaHelper.Claculate(organizationId, variableInfo.levelcode); IDictionary <string, string> ammeterDetail = formulaHelper.ammeterDictionary; IDictionary <string, string> materialDetail = formulaHelper.materialDictionary; string myDenominatorFormula = formulaHelper.GetDenominatorFormulaJson(organizationId, variableId); //myDenominatorFormula=myDenominatorFormula==""?"无":myDenominatorFormula; DataTable data = meterStatistics.GetMeterStatictisticsData(organizationId, variableInfo, 10, ammeterDetail, materialDetail); DataTable equipmentInfoTable = new DataTable(); if (variableInfo.leveltype == "MainMachine") { equipmentInfoTable = meterStatistics.GetEquipmentInfo(organizationId, variableInfo); } StatisticResult result = new StatisticResult { formula = meterStatistics.AmmeterFormula, denominatorFormula = myDenominatorFormula, data = data, EquipmentInfoData = equipmentInfoTable, PFormula = formulaHelper.PDictionary, GFormula = formulaHelper.GDictionary }; return(result); }
public static DataTable GetElectricityUsageGroupByHour(string organizationId, string levelcode, DateTime startTime, DateTime endTime) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string queryString = @"SELECT YEAR([vDate]) AS [Year], MONTH([vDate]) AS [Month], DAY([vDate]) AS [Day], DATEPART(HOUR, [vDate]) AS [Hour], SUM([FormulaValue]) AS [Sum] FROM [{0}].[dbo].[HistoyFormulaValue] WHERE [OrganizationID] = @organizationId AND [LevelCode] = @levelcode AND [vDate] >= @startTime AND [vDate] <= @endTime GROUP BY YEAR([vDate]), MONTH([vDate]), DAY([vDate]), DATEPART(HOUR, [vDate]) "; SqlParameter[] parameters = new SqlParameter[]{ new SqlParameter("organizationId", organizationId), new SqlParameter("levelcode", levelcode), new SqlParameter("startTime", startTime), new SqlParameter("endTime", endTime) }; return dataFactory.Query(string.Format(queryString, ConnectionStringFactory.GetAmmeterDatabaseName(organizationId)), parameters); }
public static DataTable GetAssessmentVersionDetailTable(string mKeyId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); string mySql = @"SELECT A.[Id] ,A.[AssessmentId] ,C.Name ,A.[ObjectId] ,B.[Name] as [OrganizaitonName] ,A.[OrganizaitonID] ,A.[KeyId] ,A.[WeightedValue] ,A.[BestValue] ,A.[WorstValue] ,A.[Enabled] FROM [dbo].[assessment_AssessmentDetail] A,[dbo].[system_Organization] B,[dbo].[assessment_AssessmentCatalogue] C where A.[OrganizaitonID]=B.[OrganizationID] and A.[AssessmentId]=C.[AssessmentId] and A.[KeyId]=@mKeyId"; SqlParameter para = new SqlParameter("@mKeyId", mKeyId); DataTable table = factory.Query(mySql, para); return(table); }
public static DataTable GetHistoryStaffSignInTable(string mOrganizationID, string mStaffId, string mStartTime, string mEndTime) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); string mySql = @"SELECT A.[RecordId] ,A.[vDate] ,A.[StaffID] ,C.[Name] as StaffName ,A.[OrganizationID] ,A.[WorkingSectionID] ,B.[WorkingSectionName] ,A.[Shifts] ,A.[Creator] ,A.[CreateTime] ,A.[Remark] FROM [dbo].[shift_staffSignInRecord] A,[dbo].[system_WorkingSection] B, [dbo].[system_StaffInfo] C where A.[WorkingSectionID]=B.[WorkingSectionID] and A.[StaffID]=C.[StaffInfoID] and A.OrganizationID=@mOrganizationID and A.[StaffID]=@mStaffId and convert(datetime,[vDate])>=convert(datetime,@mStartTime) and convert(datetime,[vDate])<=convert(datetime,@mEndTime) order by convert(datetime,[vDate])"; SqlParameter[] parameter = { new SqlParameter("@mOrganizationID", mOrganizationID), new SqlParameter("@mStaffId", mStaffId), new SqlParameter("@mStartTime", mStartTime), new SqlParameter("@mEndTime", mEndTime) }; DataTable dt = factory.Query(mySql, parameter); return(dt); }
public static string GetDataBaseName(string myOrganizationId) { string connectionstring = ConnectionStringFactory.NXJCConnectionString; SqlServerDataFactory m_dataFactory = new SqlServerDataFactory(connectionstring); string m_Sql = @"select B.MeterDatabase from system_Organization A,system_Database B where A.OrganizationID = '{0}' and A.DatabaseID = B.DatabaseID"; m_Sql = string.Format(m_Sql, myOrganizationId); try { DataTable m_DataBaseTable = m_dataFactory.Query(m_Sql); string m_DataBaseName = ""; if (m_DataBaseTable != null && m_DataBaseTable.Rows.Count > 0) { m_DataBaseName = m_DataBaseTable.Rows[0]["MeterDatabase"].ToString(); } return(m_DataBaseName); } catch (Exception) { return(""); } }
/// <summary> /// 获得报警颜色信息 /// </summary> /// <param name="organizationId"></param> /// <returns></returns> public static SyetemColor GetAlarmColorInfo(string organizationId) { ISqlServerDataFactory dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString); string mySql = @"select * from system_Color A where A.OrganizationId=@organizationId" ; SqlParameter parameter = new SqlParameter("organizationId", organizationId); DataTable table = dataFactory.Query(mySql, parameter); SyetemColor colorObj = new SyetemColor(); colorObj.OrganizationId = table.Rows[0]["OrganizationId"].ToString().Trim(); colorObj.Color_HH = table.Rows[0]["Color_HH"].ToString().Trim(); colorObj.Color_H = table.Rows[0]["Color_H"].ToString().Trim(); colorObj.Color_LL = table.Rows[0]["Color_LL"].ToString().Trim(); colorObj.Color_L = table.Rows[0]["Color_L"].ToString().Trim(); colorObj.Color_BarBackground = table.Rows[0]["Color_BarBackground"].ToString().Trim(); colorObj.Color_BarForeground = table.Rows[0]["Color_BarForeground"].ToString().Trim(); colorObj.Color_MaxRangeAlarm = table.Rows[0]["Color_MaxRangeAlarm"].ToString().Trim(); colorObj.Color_MinRangeAlarm = table.Rows[0]["Color_MinRangeAlarm"].ToString().Trim(); return(colorObj); }
/// <summary> /// 按生产线ID获取数据库名(优先从缓存中查找) /// </summary> /// <param name="productLineId"></param> /// <returns></returns> private static string GetCatalogByProductLineId(int productLineId) { if (ProcessCatalogDictionary.ContainsKey(productLineId)) { return(ProcessCatalogDictionary[productLineId]); } string connectionString = ConfigurationManager.ConnectionStrings["ManagementData"].ConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Query query = new Query("DataBaseContrast"); query.AddCriterion("ProductLineId", productLineId, CriteriaOperator.Equal); DataTable dt = factory.Query(query); if (dt.Rows.Count == 0) { throw new ApplicationException("没有此生产线"); } ProcessCatalogDictionary.Add(productLineId, dt.Rows[0]["DataBaseName"].ToString().Trim()); return(ProcessCatalogDictionary[productLineId]); }
/// <summary> /// 保存公式 /// </summary> /// <param name="groupId"></param> /// <param name="data"></param> public static void SaveFormulas(Guid groupId, DataTable data) { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); // 删除现存公式 ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); Delete delete = new Delete("Formula"); delete.AddCriterions("GroupID", groupId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal); factory.Remove(delete); // 插入所有公式 using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = connection.CreateCommand(); connection.Open(); foreach (DataRow row in data.Rows) { command.CommandText = "INSERT INTO Formula (GroupID, LevelCode, Name, Formula) VALUES ('" + groupId.ToString() + "', '" + row["LevelCode"].ToString() + "','" + row["Name"].ToString() + "','" + row["Formula"].ToString() + "')"; command.ExecuteNonQuery(); } } }
public static DataTable GetRowMaterialProcessElectricityUsageByMonth(string searchDate, string[] myOganizationIds) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string m_VariableId = "rawMaterialsPreparation_ElectricityQuantity"; string m_OrganizationCondition = ""; for (int i = 0; i < myOganizationIds.Length; i++) { if (i == 0) { m_OrganizationCondition = "'" + myOganizationIds[i] + "'"; } else { m_OrganizationCondition = m_OrganizationCondition + ",'" + myOganizationIds[i] + "'"; } } string queryString = @"Select M.Name as CompanyName ,Z.OrganizationID as OrganizationId , case when Z.OrganizationID = M.OrganizationID then '合计' else replace(replace(replace(replace(N.Name,'号','#'),'窑',''),'熟料',''),'线','') end as ProductLine ,Z.runLevel , Z.PeakB , Z.ValleyB , Z.FlatB , Z.TotalPeakValleyFlatB ,case when Z.TotalPeakValleyFlatB <> 0 then convert(varchar(32),convert(decimal(18,2),100 * Z.PeakB / Z.TotalPeakValleyFlatB)) + '%' else '0' end as PeakBproportion ,case when Z.TotalPeakValleyFlatB <> 0 then convert(varchar(32),convert(decimal(18,2),100 * Z.ValleyB / Z.TotalPeakValleyFlatB)) + '%' else '0' end as ValleyBproportion ,case when Z.TotalPeakValleyFlatB <> 0 then convert(varchar(32),convert(decimal(18,2),100 * Z.FlatB / Z.TotalPeakValleyFlatB)) + '%' else '0' end as FlatBproportion ,0 as runLevelChangedCount from system_Organization M, system_Organization N ,(Select B.OrganizationID as OrganizationID ,D.LevelType ,sum(B.PeakB) as PeakB ,sum(B.ValleyB) as ValleyB ,sum(B.FlatB) as FlatB ,sum(B.TotalPeakValleyFlatB) as TotalPeakValleyFlatB ,0.00 as runLevel from tz_Balance A, balance_Energy B, system_Organization C, system_Organization D where A.StaticsCycle = 'day' and A.TimeStamp like '{0}%' and A.BalanceId = B.KeyId and B.ValueType = 'ElectricityQuantity' and B.VariableId='{2}' and B.OrganizationID = D.OrganizationID and C.OrganizationID in ({1}) and D.levelCode like C.LevelCode + '%' and D.LevelType = 'ProductionLine' group by B.OrganizationID, D.LevelType, B.VariableID union all Select E.OrganizationID as OrganizationID ,E.LevelType ,sum(B.PeakB) as PeakB ,sum(B.ValleyB) as ValleyB ,sum(B.FlatB) as FlatB ,sum(B.TotalPeakValleyFlatB) as TotalPeakValleyFlatB ,null as runLevel from tz_Balance A, balance_Energy B, system_Organization C, system_Organization D, system_Organization E where A.StaticsCycle = 'day' and A.TimeStamp like '{0}%' and A.BalanceId = B.KeyId and B.ValueType = 'ElectricityQuantity' and B.VariableId='{2}' and B.OrganizationID = D.OrganizationID and C.OrganizationID in ({1}) and D.levelCode like C.LevelCode + '%' and D.LevelType = 'ProductionLine' and charindex(E.LevelCode, D.LevelCode) > 0 and E.LevelType = 'Company' group by E.OrganizationID, E.LevelType) Z where N.OrganizationID = Z.OrganizationID and charindex(M.LevelCode, N.LevelCode) > 0 and M.LevelType = 'Company' order by M.LevelCode, ProductLine"; DataTable table = dataFactory.Query(string.Format(queryString, searchDate, m_OrganizationCondition, m_VariableId)); GetEquipmentRunRate(ref table, searchDate, m_OrganizationCondition, dataFactory); return(table); }
/// <summary> /// 获得基础数据 /// </summary> /// <param name="organizationId"></param> /// <param name="startTime"></param> /// <param name="endTime"></param> /// <returns></returns> private static DataTable CreatBaseTable(string organizationId, string startTime, string endTime) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string meterDatabase; string sql = @"select B.MeterDatabase from system_Organization A,system_Database B where A.DatabaseID=B.DatabaseID and A.OrganizationID=@organizationId"; SqlParameter parameter = new SqlParameter("organizationId", organizationId); DataTable t_table = dataFactory.Query(sql, parameter); if (t_table.Rows.Count == 0) { throw new Exception("没有找到对应的分厂数据库"); } else { meterDatabase = t_table.Rows[0]["MeterDatabase"].ToString().Trim(); } string myFieldSql = @"select A.LevelCode,A.OrganizationID, A.AmmeterNumber,A.Remarks,B.AmmeterName, B.ElectricRoom, B.CommunicationProtocol, B.AmmeterAddress, B.CommPort, B.CT,B.PT, B.PowerFieldNameSave, B.Status, C.ElectricRoomName from {0}.dbo.AmmeterModifyCoefficientReference A left join {0}.dbo.AmmeterContrast B on (A.OrganizationID=B.OrganizationID and A.AmmeterNumber=B.AmmeterNumber) left join [{0}].[dbo].[ElectricRoomContrast] C on B.ElectricRoom=C.ElectricRoom where B.[EnabledFlag]=1 or B.[EnabledFlag] is null"; DataTable fieldTable = dataFactory.Query(string.Format(myFieldSql, meterDatabase)); DataColumn column = new DataColumn("Value", typeof(decimal)); column.DefaultValue = 0; fieldTable.Columns.Add(column); //子节点电量和 DataColumn columnChildrenValue = new DataColumn("ChildrenValue", typeof(decimal)); columnChildrenValue.DefaultValue = 0; fieldTable.Columns.Add(columnChildrenValue); //差值 DataColumn columnDValue = new DataColumn("DValue", typeof(decimal)); columnDValue.DefaultValue = 0; fieldTable.Columns.Add(columnDValue); //系数 DataColumn columnRatio = new DataColumn("Ratio", typeof(decimal)); columnRatio.DefaultValue = 1; fieldTable.Columns.Add(columnRatio); StringBuilder fieldBuilder = new StringBuilder(); foreach (DataRow dr in fieldTable.Rows) { string t_formula = dr["AmmeterNumber"].ToString().Trim(); string resultFormula = t_formula; IEnumerable <string> variableList = Regex.Split(t_formula, @"[+\-*/()]+") .Except((IEnumerable <string>) new string[] { "" }) .Select(p => p = Regex.Replace(p, @"^([0-9]+)([\.]([0-9]+))?$", "")) .Except((IEnumerable <string>) new string[] { "" }); foreach (string item in variableList) { resultFormula = resultFormula.Replace(item, item.Trim() + "Energy"); } fieldBuilder.Append("SUM("); fieldBuilder.Append(resultFormula); fieldBuilder.Append(") as '"); fieldBuilder.Append(dr["AmmeterNumber"].ToString().Trim()); fieldBuilder.Append("',"); } fieldBuilder.Remove(fieldBuilder.Length - 1, 1); string myValueSql = @"select {0} from {1}.dbo.HistoryAmmeterIncrement A where CONVERT(varchar(10),A.vDate,20)>=@startTime and CONVERT(varchar(10),A.vDate,20)<=@endTime"; SqlParameter[] parameters = { new SqlParameter("startTime", startTime), new SqlParameter("endTime", endTime) }; DataTable valueTable = dataFactory.Query(string.Format(myValueSql, fieldBuilder.ToString(), meterDatabase), parameters); if (valueTable.Rows.Count == 1) { foreach (DataRow dr in fieldTable.Rows) { string ammeterNum = dr["AmmeterNumber"].ToString().Trim(); dr["Value"] = valueTable.Rows[0][ammeterNum]; } } return(fieldTable); }
public static DataTable GetElectricityConsumption(DataTable myVariableFormulaTable, string myKeyColumn, string myStartDate, string myEndDate) { //string m_ElectricityConsumptionFlag = "_ElectricityConsumption"; List <string> m_VariableIdList = new List <string>(); //////////////////////构造表结构/////////////////////// DataTable m_ElectricityConsumptionTable = new DataTable(); int m_ValueZone = -1; for (int i = 0; i < myVariableFormulaTable.Columns.Count; i++) { if (myVariableFormulaTable.Columns[i].ColumnName == myKeyColumn) { m_ValueZone = i + 1; m_ElectricityConsumptionTable.Columns.Add(myVariableFormulaTable.Columns[i].ColumnName, typeof(string)); } else if (m_ValueZone != -1) { m_ElectricityConsumptionTable.Columns.Add(myVariableFormulaTable.Columns[i].ColumnName, typeof(decimal)); } else { m_ElectricityConsumptionTable.Columns.Add(myVariableFormulaTable.Columns[i].ColumnName, myVariableFormulaTable.Columns[i].DataType); } } if (m_ValueZone != -1) { for (int i = 0; i < myVariableFormulaTable.Rows.Count; i++) { for (int j = m_ValueZone; j < myVariableFormulaTable.Columns.Count; j++) { if (myVariableFormulaTable.Rows[i][j] != DBNull.Value && myVariableFormulaTable.Rows[i][j].ToString() != "") { MatchCollection m_Collection = Regex.Matches(myVariableFormulaTable.Rows[i][j].ToString(), @"[\w\.]+(?:\s*\([\s\S]*?((?'op'\([\s\S]*?)*(?'-op'\)[\s\S]*?)*)*(?(op)(?!))\))?"); foreach (Match myItem in m_Collection) { if (!m_VariableIdList.Contains(myItem.Value)) { m_VariableIdList.Add(myItem.Value); } } } } } } string m_ConnectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory m_DataFactory = new SqlServerDataFactory(m_ConnectionString); DataTable m_SourceTable = GetVariableValue(myVariableFormulaTable, m_VariableIdList, myKeyColumn, myStartDate, myEndDate, m_DataFactory); DataTable m_FormulaTable = GetConsumptionFormula(m_VariableIdList, m_DataFactory); Dictionary <string, string> m_KeyColumnName = GetProductionLineName(myVariableFormulaTable, myKeyColumn, m_DataFactory); DataTable m_ConsumptionValue = EnergyConsumption.EnergyConsumptionCalculate.CalculateByOrganizationId(m_SourceTable, m_FormulaTable, "ValueFormula", new string[] { "TotalPeakValleyFlatB" }); for (int i = 0; i < myVariableFormulaTable.Rows.Count; i++) { DataRow m_NewValueRow = m_ElectricityConsumptionTable.NewRow(); m_NewValueRow[0] = myVariableFormulaTable.Rows[i][0].ToString(); //赋值第一列的信息 if (m_KeyColumnName.ContainsKey(myVariableFormulaTable.Rows[i][myKeyColumn].ToString())) //赋值第二列的产线名称 { m_NewValueRow[myKeyColumn] = m_KeyColumnName[myVariableFormulaTable.Rows[i][myKeyColumn].ToString()]; } GetResult(ref m_NewValueRow, myVariableFormulaTable.Rows[i], myKeyColumn, m_ConsumptionValue, m_ValueZone, myVariableFormulaTable.Columns.Count, "TotalPeakValleyFlatB"); m_ElectricityConsumptionTable.Rows.Add(m_NewValueRow); } return(m_ElectricityConsumptionTable); }
//分品种库存入库(调用了徐一帅的算法有多余数据) private static DataTable GetMaterialChangeDataTable(string mOrganizationId, string productionLine, DateTime startTime, DateTime endTime) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); DataTable table = new DataTable(); if (productionLine == "cementmill") { string Allsql = @"(SELECT A.[OrganizationID] ,C.[Name] ,B.[MaterialColumn] ,A.[ChangeStartTime] ,A.[ChangeEndTime] ,B.[VariableId] ,B.[MaterialDataBaseName] ,B.[MaterialDataTableName] ,'' as [LevelCode] ,'leafnode' as [NodeType] FROM [NXJC].[dbo].[material_MaterialChangeLog] A,[NXJC].[dbo].[material_MaterialChangeContrast] B,[NXJC].[dbo].[system_Organization] C where A.OrganizationID like @mOrganizationId + '%' and A.OrganizationID=C.OrganizationID and B.[ContrastID]=A.[ContrastID] and A.[VariableType]='Cement' and LOWER(A.EventValue) = LOWER(B.Valid) and B.[VariableId] != '自产/外购熟料' and ((A.[ChangeStartTime]<=@startTime and A.[ChangeEndTime]>=@startTime) or (A.[ChangeStartTime]>=@startTime and A.[ChangeEndTime]<=@endTime) or (A.[ChangeStartTime]<=@startTime and A.[ChangeEndTime]>=@endTime) or (A.[ChangeStartTime]<=@endTime and A.[ChangeEndTime]>=@endTime) or (A.[ChangeStartTime]<=@endTime and A.[ChangeEndTime] is NULL)) ) union all (SELECT A.[OrganizationID],C.[Name],'' as [MaterialColumn],'' as [ChangeStartTime],'' as [ChangeEndTime],'' as [VariableId] ,'' as [MaterialDataBaseName],'' as [MaterialDataTableName],'' as [LevelCode], 'node' as [NodeType] from [NXJC].[dbo].[material_MaterialChangeLog] A,[NXJC].[dbo].[material_MaterialChangeContrast] B,[NXJC].[dbo].[system_Organization] C where A.OrganizationID like @mOrganizationId + '%' and A.OrganizationID=C.OrganizationID and B.[ContrastID]=A.[ContrastID] and A.[VariableType]='Cement' and LOWER(A.EventValue) = LOWER(B.Valid) and B.[VariableId] != '自产/外购熟料' and ((A.[ChangeStartTime]<=@startTime and A.[ChangeEndTime]>=@startTime) or (A.[ChangeStartTime]>=@startTime and A.[ChangeEndTime]<=@endTime) or (A.[ChangeStartTime]<=@startTime and A.[ChangeEndTime]>=@endTime) or (A.[ChangeStartTime]<=@endTime and A.[ChangeEndTime]>=@endTime) or (A.[ChangeStartTime]<=@endTime and A.[ChangeEndTime] is NULL)) --order by A.[OrganizationID] group by A.[OrganizationID],C.[Name]) order by A.[OrganizationID],A.[ChangeStartTime]" ; SqlParameter[] Allparameter = { new SqlParameter("mOrganizationId", mOrganizationId), new SqlParameter("startTime", startTime), new SqlParameter("endTime", endTime) }; table = dataFactory.Query(Allsql, Allparameter); } else { string sql = @"(SELECT A.[OrganizationID] ,C.[Name] ,B.[MaterialColumn] ,A.[ChangeStartTime] ,A.[ChangeEndTime] ,B.[VariableId] ,B.[MaterialDataBaseName] ,B.[MaterialDataTableName] ,'' as [LevelCode] ,'leafnode' as [NodeType] FROM [NXJC].[dbo].[material_MaterialChangeLog] A,[NXJC].[dbo].[material_MaterialChangeContrast] B,[NXJC].[dbo].[system_Organization] C where A.OrganizationID=@productionLine and A.OrganizationID=C.OrganizationID and B.[ContrastID]=A.[ContrastID] and A.[VariableType]='Cement' and LOWER(A.EventValue) = LOWER(B.Valid) and B.[VariableId] != '自产/外购熟料' and ((A.[ChangeStartTime]<=@startTime and A.[ChangeEndTime]>=@startTime) or (A.[ChangeStartTime]>=@startTime and A.[ChangeEndTime]<=@endTime) or (A.[ChangeStartTime]<=@startTime and A.[ChangeEndTime]>=@endTime) or (A.[ChangeStartTime]<=@endTime and A.[ChangeEndTime]>=@endTime) or (A.[ChangeStartTime]<=@endTime and A.[ChangeEndTime] is NULL)) union all (SELECT A.[OrganizationID] ,C.[Name] ,'' as [MaterialColumn] ,'' as [ChangeStartTime] ,'' as [ChangeEndTime] ,'' as [VariableId] ,'' as [MaterialDataBaseName] ,'' as [MaterialDataTableName] ,'' as [LevelCode] ,'node' as [NodeType] FROM [NXJC].[dbo].[material_MaterialChangeLog] A,[NXJC].[dbo].[material_MaterialChangeContrast] B,[NXJC].[dbo].[system_Organization] C where A.OrganizationID=@productionLine and A.OrganizationID=C.OrganizationID and B.[ContrastID]=A.[ContrastID] and A.[VariableType]='Cement' and LOWER(A.EventValue) = LOWER(B.Valid) and B.[VariableId] != '自产/外购熟料' and ((A.[ChangeStartTime]<=@startTime and A.[ChangeEndTime]>=@startTime) or (A.[ChangeStartTime]>=@startTime and A.[ChangeEndTime]<=@endTime) or (A.[ChangeStartTime]<=@startTime and A.[ChangeEndTime]>=@endTime) or (A.[ChangeStartTime]<=@endTime and A.[ChangeEndTime]>=@endTime) or (A.[ChangeStartTime]<=@endTime and A.[ChangeEndTime] is NULL)) group by A.[OrganizationID],C.[Name])) order by A.[ChangeStartTime]"; SqlParameter[] parameter = { new SqlParameter("productionLine", productionLine), new SqlParameter("startTime", startTime), new SqlParameter("endTime", endTime) }; table = dataFactory.Query(sql, parameter); DataRow row; row = table.NewRow(); row["OrganizationID"] = productionLine; row["LevelCode"] = "M01"; } table.Columns.Add("Production"); table.Columns.Add("Formula"); table.Columns.Add("Consumption"); int count = table.Rows.Count; for (int j = 0; j < count; j++) { if (table.Rows[j]["ChangeEndTime"].ToString() == "") { table.Rows[j]["ChangeEndTime"] = endTime; } DateTime m_startTime = Convert.ToDateTime(table.Rows[j]["ChangeStartTime"].ToString().Trim()); DateTime m_endTime = Convert.ToDateTime(table.Rows[j]["ChangeEndTime"].ToString().Trim()); if (DateTime.Compare(m_startTime, Convert.ToDateTime(startTime)) == -1) { table.Rows[j]["ChangeStartTime"] = startTime; } if (DateTime.Compare(m_endTime, Convert.ToDateTime(endTime)) == 1) { table.Rows[j]["ChangeEndTime"] = endTime; } } for (int i = 0; i < count; i++) { string nodeType = table.Rows[i]["NodeType"].ToString().Trim(); if (nodeType == "leafnode") { string materialDataBaseName = table.Rows[i]["MaterialDataBaseName"].ToString().Trim(); string materialDataTableName = table.Rows[i]["MaterialDataTableName"].ToString().Trim(); string changeStartTime = table.Rows[i]["ChangeStartTime"].ToString().Trim(); string changeEndTime = table.Rows[i]["ChangeEndTime"].ToString().Trim(); string materialColumn = table.Rows[i]["MaterialColumn"].ToString().Trim(); string m_productionLine = table.Rows[i]["OrganizationID"].ToString().Trim(); //string mProductionLine = table.Rows[i]["OrganizationID"].ToString().Trim(); // string mSql = @"select cast(sum(A.{0}) as decimal(18,2)) as [MaterialProduction] // ,cast(sum(B.[FormulaValue]) as decimal(18,2)) as [Formula] // from {1}.[dbo].{2} A,{1}.[dbo].[HistoryFormulaValue] B // where A.[vDate]>=@changeStartTime // and A.[vDate]<=@changeEndTime // and B.[OrganizationID]=@productionLine // and B.[vDate]>=@changeStartTime // and B.[vDate]<=@changeEndTime"; string mSql = @"select cast(sum([FormulaValue]) as decimal(18,2)) as [Formula] from {0}.[dbo].[HistoryFormulaValue] where vDate>=@changeStartTime and vDate<=@changeEndTime and variableId = 'cementPreparation' and [OrganizationID]=@m_productionLine" ; SqlParameter[] para = { new SqlParameter("m_productionLine", m_productionLine), new SqlParameter("changeStartTime", changeStartTime), new SqlParameter("changeEndTime", changeEndTime) }; DataTable passTable = dataFactory.Query(string.Format(mSql, materialDataBaseName), para); string mFormula = passTable.Rows[0]["Formula"].ToString().Trim(); string mSsql = @"select cast(sum({0}) as decimal(18,2)) as [MaterialProduction] from {1}.[dbo].{2} where vDate>=@changeStartTime and vDate<=@changeEndTime"; SqlParameter[] paras = { new SqlParameter("changeStartTime", changeStartTime), new SqlParameter("changeEndTime", changeEndTime) }; DataTable resultTable = dataFactory.Query(string.Format(mSsql, materialColumn, materialDataBaseName, materialDataTableName), paras); string mProduction = resultTable.Rows[0]["MaterialProduction"].ToString().Trim(); table.Rows[i]["Production"] = mProduction; table.Rows[i]["Formula"] = mFormula; } } //增加层次码 int mcode = 0; for (int i = 0; i < table.Rows.Count; i++) { string id = table.Rows[i]["NodeType"].ToString(); if (id == "node") { string nodeCode = "M01" + (++mcode).ToString("00"); table.Rows[i]["LevelCode"] = nodeCode; int mleafcode = 0; for (int j = 0; j < table.Rows.Count; j++) { if (table.Rows[j]["OrganizationID"].ToString().Trim() == table.Rows[i]["OrganizationID"].ToString().Trim() && table.Rows[j]["NodeType"].ToString().Equals("leafnode")) { table.Rows[j]["LevelCode"] = nodeCode + (++mleafcode).ToString("00"); } } } } DataColumn stateColumn = new DataColumn("state", typeof(string)); table.Columns.Add(stateColumn); //此处代码是控制树开与闭的 //foreach (DataRow dr in table.Rows) //{ // if (dr["NodeType"].ToString() == "node") // { // dr["state"] = "closed"; // } // else // { // dr["state"] = "open"; // } //} //计算电耗和产线总计 for (int i = 0; i < table.Rows.Count; i++) { if (table.Rows[i]["Production"].ToString().Trim() != "0.00" && table.Rows[i]["Production"].ToString().Trim() != "") { string mFormula = table.Rows[i]["Formula"].ToString().Trim(); if (mFormula == "") { mFormula = "0"; } double lastFormula = Convert.ToDouble(mFormula); string mProduction = table.Rows[i]["Production"].ToString().Trim(); //if (mProduction == "") //{ // mProduction = "0"; //} double lastProduction = Convert.ToDouble(mProduction); double mConsumption = Convert.ToDouble((lastFormula / lastProduction).ToString("0.00")); //string lastConsumption = Convert.ToString(mConsumption); table.Rows[i]["Consumption"] = mConsumption; } if (table.Rows[i]["NodeType"].ToString() == "leafnode" && (table.Rows[i]["Production"].ToString().Trim() == "0.00" || table.Rows[i]["Production"].ToString().Trim() == "")) { string mConsumption = ""; table.Rows[i]["Consumption"] = mConsumption; } //string firstName = table.Rows[i]["Name"].ToString().Trim(); //string secondName = table.Rows[i + 1]["Name"].ToString().Trim(); } for (int i = 0; i < table.Rows.Count;) { string m_Name = table.Rows[i]["Name"].ToString(); DataRow[] m_SubRoot = table.Select("Name = '" + m_Name + "'"); int length = m_SubRoot.Length; double sumProduction = 0; double sumFormula = 0; double sumConsumption = 0; for (int j = 0; j < length; j++) { string mmProduction = m_SubRoot[j]["Production"].ToString().Trim(); if (mmProduction == "") { mmProduction = "0"; } double m_Prodcution = Convert.ToDouble(mmProduction); sumProduction = sumProduction + m_Prodcution; string mmFormula = m_SubRoot[j]["Formula"].ToString().Trim(); if (mmFormula == "") { mmFormula = "0"; } double m_formula = Convert.ToDouble(mmFormula); sumFormula = sumFormula + m_formula; string mmConsumption = m_SubRoot[j]["Consumption"].ToString().Trim(); if (mmConsumption == "") { mmConsumption = "0"; } double m_consumption = Convert.ToDouble(mmConsumption); sumConsumption = sumConsumption + m_consumption; } table.Rows[i]["Production"] = sumProduction; table.Rows[i]["Formula"] = sumFormula; table.Rows[i]["Consumption"] = Convert.ToDouble((sumFormula / sumProduction).ToString("0.00")); i = i + length; } return(table); }
//计算出入库量 private static Decimal GetInputQuantity(DateTime endTime, DataTable BenchmarksInformation, string organizationID, DataTable InputWarehouse) { int tableRowsCount = InputWarehouse.Rows.Count; Decimal inputNygl = 0; Decimal inputSigleNygl = 0; Decimal BenchmarksValue = Convert.ToDecimal(BenchmarksInformation.Rows[0][0]); //基准库存值 DateTime BenchmarksTime = Convert.ToDateTime(BenchmarksInformation.Rows[0][1]); //基准库存时间 for (int i = 0; i < tableRowsCount; i++) { string variableIdName = Convert.ToString(InputWarehouse.Rows[i][0]); string specsName = Convert.ToString(InputWarehouse.Rows[i][1]); string dataTableName = Convert.ToString(InputWarehouse.Rows[i][2]); string multiple = Convert.ToString(InputWarehouse.Rows[i][4]); string offset = Convert.ToString(InputWarehouse.Rows[i][5]); if (dataTableName == "material_MaterialChangeContrast") { DataTable table = GetMaterialChangeDataTable(organizationID, "cementmill", BenchmarksTime, endTime); foreach (DataRow dr in table.Rows) { if (dr["VariableId"].ToString() == variableIdName) { try { inputSigleNygl = Convert.ToDecimal(dr["Production"]) * Convert.ToDecimal(multiple) + Convert.ToDecimal(offset); } catch { inputSigleNygl = 0; } } else { continue; } inputNygl = inputNygl + inputSigleNygl; } } else if (dataTableName == "VWB_WeightNYGL") { if (specsName == "") { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactoryNew = new SqlServerDataFactory(connectionString); string mySql = @"select sum(Value) as InputSigle from [dbo].[VWB_WeightNYGL] where @BenchmarksTime<[StatisticalTime] and [StatisticalTime]<@endTime and OrganizationID=@OrganizationID and VariableId=@VariableId "; SqlParameter[] myParameter = { new SqlParameter("@endTime", endTime), new SqlParameter("@BenchmarksTime", BenchmarksTime), new SqlParameter("@OrganizationID", organizationID), new SqlParameter("@VariableId", variableIdName) }; DataTable table = dataFactoryNew.Query(mySql, myParameter); try { inputSigleNygl = Convert.ToDecimal(table.Rows[0][0]) * Convert.ToDecimal(multiple) + Convert.ToDecimal(offset); } catch { inputSigleNygl = 0; } inputNygl = inputNygl + inputSigleNygl; } else { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactoryNew = new SqlServerDataFactory(connectionString); string mySql = @"select sum(Value) as InputSigle from [dbo].[VWB_WeightNYGL] where @BenchmarksTime<[StatisticalTime] and [StatisticalTime]<@endTime and OrganizationID=@OrganizationID and VariableId=@VariableId and [VariableSpecs]=@specsName "; SqlParameter[] myParameter = { new SqlParameter("@endTime", endTime), new SqlParameter("@BenchmarksTime", BenchmarksTime), new SqlParameter("@OrganizationID", organizationID), new SqlParameter("@VariableId", variableIdName), new SqlParameter("@specsName", specsName) }; DataTable table = dataFactoryNew.Query(mySql, myParameter); try { inputSigleNygl = Convert.ToDecimal(table.Rows[0][0]) * Convert.ToDecimal(multiple) + Convert.ToDecimal(offset); } catch { inputSigleNygl = 0; } inputNygl = inputNygl + inputSigleNygl; } } else if (dataTableName == "HistoryDCSIncrement") { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactoryNew = new SqlServerDataFactory(connectionString); string m_DataBaseName = GetDataBaseNameByOrganizationId(organizationID, dataFactoryNew); string mySql = @"select sum({0}) as InputSigle from {1}.[dbo].[HistoryDCSIncrement] where @BenchmarksTime<[vDate] and [vDate]<@endTime"; SqlParameter[] myParameter = { new SqlParameter("@endTime", endTime), new SqlParameter("@BenchmarksTime", BenchmarksTime) }; DataTable table = dataFactoryNew.Query(string.Format(mySql, variableIdName, m_DataBaseName), myParameter); try { inputSigleNygl = Convert.ToDecimal(table.Rows[0][0]) * Convert.ToDecimal(multiple) + Convert.ToDecimal(offset); } catch { inputSigleNygl = 0; } inputNygl = inputNygl + inputSigleNygl; } } return(inputNygl); }
public static DataTable CreatOrganizationTree(string[] levelCodes) { string connectionstring = ConnectionStringFactory.NXJCConnectionString; SqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionstring); //首先获得所有的授权的分厂数据库名 string meterDbSql = @"select A.LevelCode,A.Name,B.MeterDatabase from system_Organization A,system_Database B where A.DataBaseID=B.DataBaseID and ({0}) and A.LevelType='Factory'"; StringBuilder levelCodeBuild = new StringBuilder(); foreach (string code in levelCodes) { levelCodeBuild.Append("LevelCode like '"); levelCodeBuild.Append(code); levelCodeBuild.Append("%' or "); } levelCodeBuild.Remove(levelCodeBuild.Length - 4, 4); DataTable meterDb = dataFactory.Query(string.Format(meterDbSql, levelCodeBuild.ToString())); string myStr = @"union select (A.LevelCode+'01') as LevelCode,A.OrganizationID,D.DisplayIndex,REPLACE(D.ElectricRoomName, CHAR(13) + CHAR(10), '<br>') ElectricRoom,('ElectricRoom') as LevelType,'open' as state from system_Organization A left join (select B.ElectricRoom,C.ElectricRoomName,B.OrganizationID,C.DisplayIndex from [{0}].[dbo].AmmeterContrast B left join [{0}].[dbo].ElectricRoomContrast C on B.ElectricRoom=C.ElectricRoom) D on A.OrganizationID=D.OrganizationID where A.LevelType='Factory' and A.LevelCode like '{1}%' --数据授权使用 and D.ElectricRoom is not null and D.ElectricRoom<>'' group by LevelCode,A.OrganizationID,D.DisplayIndex,D.ElectricRoomName,LevelType "; StringBuilder childQuery = new StringBuilder(); StringBuilder criteria = new StringBuilder(); foreach (DataRow dr in meterDb.Rows) { childQuery.Append(string.Format(myStr, dr["MeterDatabase"].ToString().Trim(), dr["LevelCode"].ToString().Trim())); criteria.Append(string.Format("CHARINDEX(C.LevelCode,'{0}')>0 or CHARINDEX('{0}',C.LevelCode)>0", dr["LevelCode"].ToString().Trim())); criteria.Append(" or "); } criteria.Remove(criteria.Length - 4, 4); string mySql = @"select C.LevelCode,C.OrganizationID,'' as DisplayIndex,C.Name,C.LevelType,(case when C.LevelType='Factory' then 'closed' else 'open' end) as state from system_Organization C where (C.LevelType='Company' or C.LevelType='Factory') and ({0}) {1} order by LevelCode"; string aaa = string.Format(mySql, criteria.ToString(), childQuery.ToString()); DataTable result = dataFactory.Query(string.Format(mySql, criteria.ToString(), childQuery.ToString())); int i = 0; //处理levelCode foreach (DataRow dr in result.Rows) { if (dr["LevelType"].ToString().Trim() == "Company" || dr["LevelType"].ToString().Trim() == "Factory") { i = 0; } else { string t_levelCode = dr["LevelCode"].ToString().Trim(); dr["LevelCode"] = t_levelCode.Substring(0, t_levelCode.Length - 2) + i.ToString("00"); i++; } } return(result); }
public static DataTable GetSubscribeMonitorListTable(string mlinkServer, string mpublicattionId) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); DataTable table = new DataTable(); if (mpublicattionId.Equals("")) { string mySql = @"SELECT B.[publisher], A.[publisher_database_id] ,A.[publisher_id] ,A.[publisher_db] ,A.[publication_id] ,A.[subscriber_id] ,A.[subscriber_db] ,A.[subscription_type] ,A.[sync_type] ,A.[status] ,A.[snapshot_seqno_flag] ,A.[independent_agent] ,A.[loopback_detection] ,A.[agent_id] ,A.[update_mode] ,A.[nosync_type] FROM [{0}].[distribution].[dbo].[MSsubscriptions] A,(select '['+publisher_db+']:'+publication as publisher ,[publication_id] from [{1}].[distribution].[dbo].[MSpublications]) B where A.subscriber_id>0 and A.[publication_id]=B.[publication_id] group by A.[publisher_database_id] ,A.[publisher_id] ,A.[publisher_db] ,A.[publication_id] ,A.[subscriber_id] ,A.[subscriber_db] ,A.[subscription_type] ,A.[sync_type] ,A.[status] ,A.[snapshot_seqno_flag] ,A.[independent_agent] ,A.[loopback_detection] ,A.[agent_id] ,A.[update_mode] ,A.[nosync_type],B.[publisher] order by [publisher_database_id],[publication_id] "; mySql = string.Format(mySql, mlinkServer, mlinkServer); table = dataFactory.Query(mySql); } else { string mySql = @"SELECT B.[publisher], A.[publisher_database_id] ,A.[publisher_id] ,A.[publisher_db] ,A.[publication_id] ,A.[subscriber_id] ,A.[subscriber_db] ,A.[subscription_type] ,A.[sync_type] ,A.[status] ,A.[snapshot_seqno_flag] ,A.[independent_agent] ,A.[loopback_detection] ,A.[agent_id] ,A.[update_mode] ,A.[nosync_type] FROM [{0}].[distribution].[dbo].[MSsubscriptions] A,(select '['+publisher_db+']:'+publication as publisher ,[publication_id] from [{1}].[distribution].[dbo].[MSpublications]) B where A.subscriber_id>0 and A.[publication_id]=B.[publication_id] and A.[publication_id]={2} group by A.[publisher_database_id] ,A.[publisher_id] ,A.[publisher_db] ,A.[publication_id] ,A.[subscriber_id] ,A.[subscriber_db] ,A.[subscription_type] ,A.[sync_type] ,A.[status] ,A.[snapshot_seqno_flag] ,A.[independent_agent] ,A.[loopback_detection] ,A.[agent_id] ,A.[update_mode] ,A.[nosync_type],B.[publisher] order by [publisher_database_id],[publication_id] "; mySql = string.Format(mySql, mlinkServer, mlinkServer, mpublicattionId); table = dataFactory.Query(mySql); } return(table); }
public FormulaEnergyService(string connString) { _dataFactory = new SqlServerDataFactory(connString); }
public static DataTable GetCustomerPowerDataTable(string organizationId, string startTime, string endTime, string particleSize) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mySql = @"SELECT [EquipmentGroupId] ,[Name] ,[OrganizationID] ,[Formula] ,[MaterialId] ,[DisplayIndex] ,[Enabled] FROM [dbo].[equipment_CustomerPowerContrast] where [OrganizationID]=@organizationId and [Enabled]=1 order by [DisplayIndex]"; SqlParameter sqlParameter = new SqlParameter("@organizationId", organizationId); DataTable table = dataFactory.Query(mySql, sqlParameter); table.Columns.Add("AvgPower"); table.Columns.Add("SumPower"); table.Columns.Add("Production"); int count = table.Rows.Count; for (int i = 0; i < count; i++) { if (organizationId == "zc_nxjc_qtx_efc") { organizationId = "Db_02_01"; } string mformula = table.Rows[i]["Formula"].ToString().Trim(); TimeSpan ts = Convert.ToDateTime(endTime) - Convert.ToDateTime(startTime); int c = (int)(ts.TotalMinutes); double mcount = 0; double addTime = 0; if (particleSize == "tenMinute") { mcount = Math.Ceiling((double)c / (double)10); //mcount = c / 10; addTime = 10; } if (particleSize == "hour") { mcount = Math.Ceiling((double)c / (double)60); addTime = 60; } if (particleSize == "day") { mcount = Math.Ceiling((double)c / (double)1440); addTime = 1440; } double sum_formula = 0.00; string m_Time = startTime; for (int j = 0; j < mcount; j++) { //m_Time = Convert.ToDateTime(m_Time).AddMinutes(10).ToString(); string sql = @"select avg(A.Formula) as Formula from (select vDate,{0} as Formula from {1}.[dbo].[HistoryAmmeter] where vDate>=@startTime and vDate<=@endTime) A where A.vDate>@start_m_Time and A.vDate<=@m_Time"; SqlParameter[] m_para = { new SqlParameter("@startTime", startTime), new SqlParameter("@endTime", endTime), new SqlParameter("@start_m_Time", m_Time), new SqlParameter("@m_Time", Convert.ToDateTime(m_Time).AddMinutes(10).ToString()) }; DataTable m_table = dataFactory.Query(string.Format(sql, mformula, organizationId), m_para); string avgFormula = m_table.Rows[0]["Formula"].ToString().Trim(); if (avgFormula == "") { avgFormula = "0"; } double m_formula = Convert.ToDouble(avgFormula); //string sum_formula = m_formula; sum_formula = sum_formula + m_formula; m_Time = Convert.ToDateTime(m_Time).AddMinutes(addTime).ToString(); } string sum2_formula = sum_formula.ToString("0.00"); string lastAvgFormula = (sum_formula / mcount).ToString("0.00"); table.Rows[i]["AvgPower"] = lastAvgFormula; table.Rows[i]["SumPower"] = sum2_formula; //接下来是查询产量 string materialId = table.Rows[i]["MaterialId"].ToString().Trim(); string mSql = @"SELECT [MaterialId] ,[VariableId] ,[Name] ,[KeyID] ,[Type] ,[Unit] ,[MaterialErpCode] ,[TagTableName] ,[Formula] ,[Coefficient] ,[Visible] FROM [NXJC].[dbo].[material_MaterialDetail] where [MaterialId]=@materialId"; SqlParameter para = new SqlParameter("@materialId", materialId); DataTable materialTable = dataFactory.Query(mSql, para); string materialFormula = materialTable.Rows[0]["Formula"].ToString(); string lastSql = @"select cast(sum({0}) as decimal(18,2)) as SumDcs from {1}.[dbo].[HistoryDCSIncrement] where vDate>=@startTime and vDate<=@endTime"; SqlParameter[] lastPara = { new SqlParameter("@startTime", startTime), new SqlParameter("@endTime", endTime), }; DataTable resultTable = dataFactory.Query(string.Format(lastSql, materialFormula, organizationId), lastPara); string production = resultTable.Rows[0]["SumDcs"].ToString(); table.Rows[i]["Production"] = production; } return(table); }
//过磅明细 public static DataTable GetWeighingDetailTable(string mOrganizationId, string mMaterialId, string mStartTime, string mEndTime, string mSelectTime) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); string mMaterialID = mMaterialId.Substring(1); DataTable table = new DataTable(); if (mSelectTime == "firstWeight") { string mySql = @"select [BillNO] ,[Gross] ,[Tare] ,[Suttle] ,[Cars] ,[carsName] ,('A'+[Material]) [Material] ,[MaterialName] ,[ggxh] ,(case when [Type]=0 then '采购' when [Type]=3 then '销售' when [Type]=5 then '调拨' end) [Type] ,[companyid] ,[TORORGID] ,[TORORGNAME] ,[FROMORGID] ,[FROMORGNAME] ,(case when [reservationchar7]=10 then '调出' when [reservationchar7]=11 then '调入' end) [reservationchar7] ,[weightdate] ,[lightdate] ,('A'+[lydh1]) [lydh1] ,('A'+[sales_lydd1]) [sales_lydd1] ,('A'+[lydh2]) [lydh2] ,('A'+[sales_lydd2]) [sales_lydd2] ,(case when [sales_gblx]='RD' then '退货' when [sales_gblx]='DE' then '普通销售' end) [sales_gblx] from [extern_interface].[dbo].[WB_WeightNYGL] where [OrganizationID]=@mOrganizationId and [Material]=@mMaterialId and (case when [weightdate]<[lightdate] then [weightdate] else [lightdate] end)>@mStartTime and (case when [weightdate]<[lightdate] then [weightdate] else [lightdate] end)<@mEndTime order by [weightdate]"; SqlParameter[] sqlParameter = { new SqlParameter("@mOrganizationId", mOrganizationId), new SqlParameter("@mMaterialId", mMaterialID), new SqlParameter("@mStartTime", mStartTime), new SqlParameter("@mEndTime", mEndTime) }; table = dataFactory.Query(mySql, sqlParameter); } else if (mSelectTime == "endWeight") { string mySql = @"select [BillNO] ,[Gross] ,[Tare] ,[Suttle] ,[Cars] ,[carsName] ,('A'+[Material]) [Material] ,[MaterialName] ,[ggxh] ,(case when [Type]=0 then '采购' when [Type]=3 then '销售' when [Type]=5 then '调拨' end) [Type] ,[companyid] ,[TORORGID] ,[TORORGNAME] ,[FROMORGID] ,[FROMORGNAME] ,(case when [reservationchar7]=10 then '调出' when [reservationchar7]=11 then '调入' end) [reservationchar7] ,[weightdate] ,[lightdate] ,('A'+[lydh1]) [lydh1] ,('A'+[sales_lydd1]) [sales_lydd1] ,('A'+[lydh2]) [lydh2] ,('A'+[sales_lydd2]) [sales_lydd2] ,(case when [sales_gblx]='RD' then '退货' when [sales_gblx]='DE' then '普通销售' end) [sales_gblx] from [extern_interface].[dbo].[WB_WeightNYGL] where [OrganizationID]=@mOrganizationId and [Material]=@mMaterialID and (case when [weightdate]>[lightdate] then [weightdate] else [lightdate] end)>@mStartTime and (case when [weightdate]>[lightdate] then [weightdate] else [lightdate] end)<@mEndTime order by [weightdate]"; SqlParameter[] sqlParameter = { new SqlParameter("@mOrganizationId", mOrganizationId), new SqlParameter("@mMaterialID", mMaterialID), new SqlParameter("@mStartTime", mStartTime), new SqlParameter("@mEndTime", mEndTime) }; table = dataFactory.Query(mySql, sqlParameter); } return(table); }
// public static DataTable GetAssessmentResultTableByDay(string mProductionID, string mWorkingSectionID, string mStaffId, string mGroupId, string mStartTime, string mEndTime, string mVersionId, string mStatisticalCycle) // { // string connectionString = ConnectionStringFactory.NXJCConnectionString; // ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); // string mySql = @"SELECT [KeyId] // ,[Name] // ,[Type] // ,[OrganizationID] // ,[WorkingSectionID] // FROM [NXJC_DEV].[dbo].[tz_Assessment] // where [OrganizationID]=@mOrganizationID // and [WorkingSectionID]=@mWorkingSectionID"; // SqlParameter[] para = { new SqlParameter("@mOrganizationID", mProductionID) , // new SqlParameter("@mWorkingSectionID", mWorkingSectionID) // }; // DataTable dt = factory.Query(mySql, para); // return dt; // } public static DataTable GetAssessmentResultTable(string mProductionID, string mWorkingSectionID, string mGroupId, string mStartTime, string mEndTime, string mStatisticalCycle) { DataTable resultTable = generationTableTemplate(mProductionID, mWorkingSectionID, mGroupId, mStartTime, mEndTime, mStatisticalCycle); string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString); DataTable table = new DataTable(); if (mStatisticalCycle.Equals("month") || mStatisticalCycle.Equals("day")) { string mySql = @" select C.Name,convert(char(10),A.StartTime,120) as [Time],B.[AssessmenScore] from [dbo].[tz_ShiftAssessmentResult] A,(select KeyId,sum([AssessmenScore]) as [AssessmenScore] from [dbo].[assessment_ShiftAssessmentResultDetail] where KeyId in( select KeyId from [dbo].[tz_ShiftAssessmentResult] where [OrganizationID]=@mProductionID and [WorkingSectionID]=@mWorkingSectionID and [GroupId]=@mGroupId and [StartTime]>=convert(datetime,@mStartTime) and [StartTime]<=convert(datetime,@mEndTime) ) group by KeyId,[AssessmenScore]) B,[dbo].[system_StaffInfo] C where A.KeyId=B.KeyId and A.StaffID=C.StaffInfoID order by Name,Time"; SqlParameter[] para = { new SqlParameter("@mProductionID", mProductionID), new SqlParameter("@mWorkingSectionID", mWorkingSectionID), new SqlParameter("@mGroupId", mGroupId), new SqlParameter("@mStartTime", mStartTime), new SqlParameter("@mEndTime", mEndTime) }; table = factory.Query(mySql, para); } int rowNum = 0; if (table.Rows.Count > 0) { resultTable.Rows.Add(table.Rows[0]["Name"].ToString().Trim()); resultTable.Rows[rowNum][table.Rows[0]["Time"].ToString().Trim()] = table.Rows[0]["AssessmenScore"]; decimal averageValue = Convert.ToDecimal(table.Rows[0]["AssessmenScore"]); resultTable.Rows[rowNum]["总分"] = averageValue; for (int i = 0; i < table.Rows.Count - 1; i++) { if (table.Rows[i + 1]["Name"].ToString().Trim().Equals(table.Rows[i]["Name"].ToString().Trim())) { resultTable.Rows[rowNum][table.Rows[i + 1]["Time"].ToString().Trim()] = table.Rows[i + 1]["AssessmenScore"]; averageValue = averageValue + Convert.ToDecimal(table.Rows[i + 1]["AssessmenScore"]); resultTable.Rows[rowNum]["总分"] = averageValue; } else { ++rowNum; resultTable.Rows.Add(table.Rows[i + 1]["Name"].ToString().Trim()); resultTable.Rows[rowNum][table.Rows[i + 1]["Time"].ToString().Trim()] = table.Rows[i + 1]["AssessmenScore"]; averageValue = Convert.ToDecimal(table.Rows[i + 1]["AssessmenScore"]); resultTable.Rows[rowNum]["总分"] = averageValue; } } DataView dv = resultTable.DefaultView; dv.Sort = "总分 desc"; resultTable = dv.ToTable(); for (int i = 0; i < resultTable.Rows.Count; i++) { resultTable.Rows[i]["排名"] = i + 1; } } return(resultTable); }
/// <summary> /// 获得实时数据 /// </summary> /// <param name="viewName"></param> /// <returns></returns> public IEnumerable <DataItem> GetRealtimeDatas(string organizationId, IList <string> variableNames) { IList <DataItem> result = new List <DataItem>(); /* //////////////////////////////////////////////////////////////////////////////////////////// * 以下是从数据库DCS实时表中查询能源监控电流和设备运行状态。 * 修改目的:修改为电流全部从电表实时表中读取,设备状态从WebService中获取。 * 修改人:马亮 * 修改时间:2016-08-28 */ //修改对照表中,表名称字段为Current标识的才从数据库里查询(表示电表电流而非DCS电流) string ammeterConn = ConnectionStringFactory.GetAmmeterConnectionString(organizationId); //string ammeterConn = "Data Source=QH-20150320GFTA;Initial Catalog=zc_nxjc_byc_byf;Integrated Security=True;"; _dataFactory = new SqlServerDataFactory(ammeterConn); DataTable m_AmmeterTable = null; if ("Current" == _type) //当前只有电流有可能从电表实时表中取数 { //DebugHelper.TestStart(); IDictionary <string, List <FieldInformation> > fieldInformations = GetFeildInformation(organizationId, variableNames, "Current", "Realtime"); //DebugHelper.TestStop("获取字段信息时间(ms):"); //DebugHelper.TestStart(); m_AmmeterTable = GetDataItemTable(fieldInformations); //DebugHelper.TestStop("获取数据信息时间(ms):"); //DebugHelper.TestStart(); } //此模块是修改能源监控数据中读取DCS实时表改为从WebService获取。 ISqlServerDataFactory m_dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString); string m_FactoryOrganizationId = GetFactoryOrganizationId(organizationId, m_dataFactory); Dictionary <string, string> m_BooleanTags = GetTags(organizationId, variableNames, "Current", "bit", _dataFactory); Dictionary <string, string> m_AnalogTags = GetTags(organizationId, variableNames, "Current", "real", _dataFactory); Dictionary <string, bool> m_BooleanResult = GetBooleanResult(m_FactoryOrganizationId, m_BooleanTags.Values.ToArray()); Dictionary <string, decimal> m_AnalogResult = GetAnalogResult(m_FactoryOrganizationId, m_AnalogTags.Values.ToArray()); DataTable m_DCSValueTable = GetResultTable(m_BooleanTags, m_AnalogTags, m_BooleanResult, m_AnalogResult); /////////////////////////////////////////////////////////////////////////////////////////////////////////////// DataTable table = MergeTable(m_AmmeterTable, m_DCSValueTable); string[] idList = GetTableColumnName(table); // foreach (var item in idList) { string t_value = "0"; if (_type == "RunningState") { t_value = table.Rows[0][item] is DBNull ? "NULL" : Convert.ToDecimal(table.Rows[0][item]).ToString().Trim(); } else { t_value = MyObjectToString(table.Rows[0][item]); //if (table.Rows[0][item] is bool) //{ // t_value = table.Rows[0][item] is DBNull ? "0" : Convert.ToDecimal(table.Rows[0][item]).ToString(); //} //else //{ //t_value = table.Rows[0][item] is DBNull ? "0" : (Convert.ToDecimal(table.Rows[0][item]) == 0 ? "0" : Convert.ToDecimal(table.Rows[0][item]).ToString("#").Trim()); //} } result.Add(new DataItem { ID = organizationId + ">" + item + ">" + _type, Value = t_value//table.Rows[0][item] is DBNull ? "0" : Convert.ToDecimal(table.Rows[0][item]).ToString("#").Trim() }); } //DebugHelper.TestStop("处理键值对的时间(ms):"); return(result); }