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