/// <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);
}
}
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;
}
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;
}
public static DataTable GetProductLines()
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("ProductLine");
return factory.Query(query);
}
/// <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);
}
/// <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);
}
public static DataTable GetAmmetersByFactoryId(int factoryId)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("AmmeterContrast");
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);
}
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 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="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>
/// 按分厂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>
/// 按起始时间段获取停机记录
/// </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);
}
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;
}
public static DataTable GetWarehousenameList(string mOrganizationId)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
string mySql = @"SELECT [Id]
,[Name]
,[OrganizationID]
FROM [inventory_Warehouse] where OrganizationID=@mOrganizationId order by levelcode asc";
SqlParameter para = new SqlParameter("@mOrganizationId", mOrganizationId);
DataTable dt = factory.Query(mySql, para);//
return(dt);
}
public static DataTable GetEDRoomList(string organizationId)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
SqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionString);
string meterDbName = GetMeterDatabaseByOrganizationId.GetMeterDatabaseName(organizationId);
string sql = @"select A.ElectricRoom AS ElectricRoom, B.ElectricRoomName AS ElectricRoomName
from (SELECT DISTINCT ElectricRoom FROM [{0}].[dbo].[AmmeterContrast] {1}) A
left join [{0}].[dbo].[ElectricRoomContrast] B on A.ElectricRoom=B.ElectricRoom
order by B.DisplayIndex" ;
DataTable EDRoomListTable = new DataTable();
EDRoomListTable = _dataFactory.Query(string.Format(sql, meterDbName, "where EnabledFlag=1 "));
return(EDRoomListTable);
}
public static DataTable GetPublicationTable(string serverName)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = @"select publisher_database_id,'['+publisher_db+']:'+publication as publisher,subscriber_id,subscriber_db,creation_date
from [distribution].[dbo].[MSdistribution_agents]
where subscriber_id>0
group by publisher_db,publication,publisher_database_id,subscriber_db,subscriber_id,creation_date
order by publisher_db,publication";
SqlParameter parameter = new SqlParameter("serverName", serverName);
DataTable originalTable = dataFactory.Query(mySql, parameter);
return(originalTable);
}
private void InitializeDictionary()
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
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());
}
}
private static DataTable GetOrganizationInfo(string myOrganizationId)
{
string _connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory _dataFactory = new SqlServerDataFactory(_connectionString);
string m_Sql = @"select A.LevelCode as LevelCode, A.LevelType as LevelType from system_Organization A
where A.OrganizationID = @OrganizationID";
List <SqlParameter> m_Parameters = new List <SqlParameter>();
m_Parameters.Add(new SqlParameter("@OrganizationID", myOrganizationId));
DataTable table = _dataFactory.Query(m_Sql, m_Parameters.ToArray());
return(table);
}
/// <summary>
/// 获得该分厂有哪些电气室
/// </summary>
/// <param name="organizationId">组织机构ID</param>
/// <returns></returns>
public static DataTable GetElectricRoom(string organizationId)
{
//电表数据库名
string managementDatabaseName = GetMeterDatabaseByOrganizationId.GetMeterDatabaseName(organizationId);
string connectionstring = ConnectionStringFactory.NXJCConnectionString;
SqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionstring);
string sqlStr = @"SELECT A.ElectricRoom, B.ElectricRoomName
FROM (SELECT DISTINCT ElectricRoom FROM [{0}].[dbo].[AmmeterContrast] WHERE EnabledFlag=1) A
left join
[{0}].[dbo].ElectricRoomContrast AS B on A.ElectricRoom = B.ElectricRoom
ORDER by B.DisplayIndex";
return(_dataFactory.Query(string.Format(sqlStr, managementDatabaseName)));
}
public static DataTable GetIndexDataTable(string myConsumpation, string mykeyId, string myStandardType, string myInOutSide)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = "";
if (myInOutSide == "Inside")
{
mySql = @"select t3.Name as OrganizationName,t2.Name as VariableName,t1.*from [analyse_KPI_Standard_Detail] t1,
(select distinct (case when B.LevelType = 'ProductionLine' then C.Type + B.Name else B.Name end) as Name, B.VariableId from tz_Formula A, formula_FormulaDetail B, system_Organization C
where A.ENABLE = 1
and A.State = 0
and A.Type = 2
and A.KeyID = B.KeyID
and (B.LevelType = 'ProductionLine' or B.LevelType = 'Process')
and A.OrganizationID = C.OrganizationID
and C.LevelType = 'ProductionLine'
and C.Type <> '余热发电'
and C.LevelCode like 'O0201%')t2
,[dbo].[system_Organization] t3
where t1.KeyId=@KeyId
and t1.StandardType=@myStandardType
and t1.Enabled=1
and t1.VariableId=t2.VariableId
and t1.OrganizationID=t3.OrganizationID" ;
}
else if (myInOutSide == "Outside")
{
mySql = @"select t2.Name as VariableName,t1.*from [analyse_KPI_Standard_Detail] t1,
(select distinct (case when B.LevelType = 'ProductionLine' then C.Type + B.Name else B.Name end) as Name, B.VariableId from tz_Formula A, formula_FormulaDetail B, system_Organization C
where A.ENABLE = 1
and A.State = 0
and A.Type = 2
and A.KeyID = B.KeyID
and (B.LevelType = 'ProductionLine' or B.LevelType = 'Process')
and A.OrganizationID = C.OrganizationID
and C.LevelType = 'ProductionLine'
and C.Type <> '余热发电'
and C.LevelCode like 'O0201%')t2
where t1.KeyId=@KeyId
and t1.StandardType=@myStandardType
and t1.Enabled=1
and t1.OrganizationID is null
and t1.VariableId=t2.VariableId" ;
}
SqlParameter[] Param = { new SqlParameter("@KeyId", mykeyId), new SqlParameter("@myStandardType", myStandardType) };
DataTable table = dataFactory.Query(mySql, Param);
return(table);
}
public static DataTable GetConsumpationTypeTable(string myStandardId)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = @"select CASE WHEN StatisticalMethod = 'Comprehensive' THEN '综合能耗'
WHEN StatisticalMethod = 'Entity' THEN '标准能耗'
ELSE '其他' END as Consumpation,KeyId, Row_Number() over ( order by StatisticalMethod ) as id
from [dbo].[analyse_KPI_Standard]
where StandardId=@StandardId";
SqlParameter Param = new SqlParameter("@StandardId", myStandardId);
DataTable table = dataFactory.Query(mySql, Param);
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;
}
/// <summary>
/// 获取电表对照表的内容
/// </summary>
/// <param name="electricRoomName">电气室名</param>
/// <returns></returns>
static private DataTable GetTableByElectricRoom(string organizationId, string electricRoomName)
{
string managementDatabaseName = GetMeterDatabaseByOrganizationId.GetMeterDatabaseName(organizationId);
string connectionstring = ConnectionStringFactory.NXJCConnectionString;
SqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionstring);
string sqlStr = @"SELECT A.AmmeterName,A.AmmeterNumber,A.CT,A.PT,A.AmmeterAddress,A.Status,A.TimeStatusChange
FROM [{0}].[dbo].AmmeterContrast AS A
WHERE A.ElectricRoom=@ElectricRoom
and A.EnabledFlag = 1"
;
SqlParameter paramater = new SqlParameter("ElectricRoom", electricRoomName);
return(_dataFactory.Query(string.Format(sqlStr, managementDatabaseName), paramater));
}
//获取出库总信息
private static DataTable GetOutputWarehouse(DataTable BenchmarksInformation, DateTime endTime, string organizationID)
{
DateTime BenchmarksTime = Convert.ToDateTime(BenchmarksInformation.Rows[0][1]); //基准库存时间
string BenchmarksId = Convert.ToString(BenchmarksInformation.Rows[0][2]); //仓库ID
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = @"select VariableId,specs,DataTableName,WarehousingType,Multiple,Offset from [dbo].[inventory_WareHousingContrast]
where WarehouseId=@BenchmarksId and WarehousingType='Output'
";
SqlParameter[] myParameter = { new SqlParameter("@BenchmarksId", BenchmarksId), new SqlParameter("@BenchmarksTime", BenchmarksTime), new SqlParameter("@endTime", endTime) };
DataTable table = dataFactory.Query(mySql, myParameter);
return(table);
}
/// <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;
}
public static DataTable GetAssessmentCatalogueTable()
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
string mySql = @"SELECT [AssessmentId]
,[Name]
,[Type]
,[ValueType]
,[Enabled]
FROM [dbo].[assessment_AssessmentCatalogue]
where [Enabled]=1";
DataTable dt = factory.Query(mySql);
return(dt);
}
//获取盘库基准信息
private static DataTable GetBenchmarksInformation(DateTime startTime, DataTable IdInformation)
{
string ID = Convert.ToString(IdInformation.Rows[0][0]);
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = @"select Value,TimeStamp as TimeStamp,WarehouseId from [dbo].[inventory_CheckWarehouse]
where TimeStamp=@startTime and WarehouseId=@IdInformation
order by TimeStamp desc
";
SqlParameter[] myParameter = { new SqlParameter("@startTime", startTime), new SqlParameter("@IdInformation", ID) };
DataTable table = dataFactory.Query(mySql, myParameter);
return(table);
}
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>
/// 保存设备
/// </summary>
/// <param name="VariableId"></param>
/// <param name="OrganizationID"></param>
/// <param name="EquipmentName"></param>
/// <param name="MonitorType"></param>
/// <param name="PowerSupply"></param>
/// <param name="VoltageGrade"></param>
/// <param name="RatedCT"></param>
/// <param name="AmmeterCode"></param>
/// <param name="ActualCT"></param>
/// <param name="Power"></param>
/// <param name="Unit"></param>
/// <param name="PowerSupplyPosition"></param>
/// <param name="Remarks"></param>
/// <returns></returns>
public static string SaveEquipment(string VariableId, string OrganizationID, string EquipmentName, int MonitorType, string PowerSupply, string VoltageGrade,
string RatedCT, string AmmeterCode, string ActualCT, string Power, string Unit, string Current, string PowerSupplyPosition, string Remarks)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string myCheckSql = @"SELECT * FROM system_EquipmentAccount where VariableId=@VariableId AND OrganizationID=@OrganizationID";
SqlParameter[] m_checkParameters = { new SqlParameter("@VariableId", VariableId),
new SqlParameter("@OrganizationID", OrganizationID) };
DataTable table = dataFactory.Query(myCheckSql, m_checkParameters);
if (table.Rows.Count > 0)
{
return("该设备已经存在");
}
string mySql = @"INSERT INTO system_EquipmentAccount
(EquipmentItemId, VariableId, OrganizationID, EquipmentName, MonitorType, PowerSupply, VoltageGrade,
RatedCT, AmmeterCode, ActualCT, [Power], Unit, [Current],PowerSupplyPosition, Remarks)
values(@EquipmentItemId, @VariableId, @OrganizationID, @EquipmentName, @MonitorType, @PowerSupply, @VoltageGrade,
@RatedCT, @AmmeterCode, @ActualCT, @Power, @Unit,@Current,@PowerSupplyPosition, @Remarks)";
Guid EquipmentItemId = Guid.NewGuid();
SqlParameter[] m_Parameters = { new SqlParameter("@EquipmentItemId", EquipmentItemId),
new SqlParameter("@VariableId", VariableId),
new SqlParameter("@OrganizationID", OrganizationID),
new SqlParameter("@EquipmentName", EquipmentName),
new SqlParameter("@MonitorType", MonitorType),
new SqlParameter("@PowerSupply", PowerSupply),
new SqlParameter("@VoltageGrade", VoltageGrade),
new SqlParameter("@RatedCT", RatedCT),
new SqlParameter("@AmmeterCode", AmmeterCode),
new SqlParameter("@ActualCT", ActualCT),
new SqlParameter("@Power", Power),
new SqlParameter("@Unit", Unit),
new SqlParameter("@Current", Current),
new SqlParameter("@PowerSupplyPosition", PowerSupplyPosition),
new SqlParameter("@Remarks", Remarks) };
int num = dataFactory.ExecuteSQL(mySql, m_Parameters);
if (num == 0)
{
return("保存失败");
}
else
{
return("保存成功");
}
}
/// <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 string GetPageID(string nodeID)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string SqlPage = @"SELECT [PAGE_ID]FROM [IndustryEnergy_SH].[dbo].[content] A where A.[NODE_ID]=@nodeID
";
SqlParameter mPara = new SqlParameter("nodeID", nodeID);
DataTable dtPage = dataFactory.Query(SqlPage, mPara);
string mPageId = "";
if (dtPage.Rows.Count != 0)
{
mPageId = dtPage.Rows[0]["PAGE_ID"].ToString();
}
return(mPageId);
}
/// <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 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 string GetReasonStatisticsTypeInfo()
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory _dataFactory = new SqlServerDataFactory(connectionString);
string m_Sql = @"SELECT A.* from system_MachineHaltReasonStatisticsType A where A.Enabled = 1 order by A.LevelCode";
m_Sql = string.Format(m_Sql);
try
{
DataTable m_EquipmentCommonInfoTable = _dataFactory.Query(m_Sql);
string m_ReturnString = EasyUIJsonParser.DataGridJsonParser.DataTableToJson(m_EquipmentCommonInfoTable);
return(m_ReturnString);
}
catch
{
return("{\"rows\":[],\"total\":0}");
}
}
public static DataTable GetWorkingSectionGridList(string mOrganizationID)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
string mySql = @"SELECT A.[WorkingSectionID],A.[Type]
,A.[WorkingSectionName]
,A.[OrganizationID]
,B.Name as OrganizationName
FROM [dbo].[system_WorkingSection] A,[dbo].[system_Organization] B
where A.[OrganizationID]=B.[OrganizationID]
and A.Enabled=1
and A.OrganizationID like @mOrganizationID+'%'
order by B.LevelCode,A.Type" ;
SqlParameter para = new SqlParameter("@mOrganizationID", mOrganizationID);
DataTable dt = factory.Query(mySql, para);
return(dt);
}
public static DataTable GetObjectNameListTable()
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = @"select distinct (case when B.LevelType = 'ProductionLine' then C.Type + B.Name else B.Name end) as Name, B.VariableId from tz_Formula A, formula_FormulaDetail B, system_Organization C
where A.ENABLE = 1
and A.State = 0
and A.Type = 2
and A.KeyID = B.KeyID
and (B.LevelType = 'ProductionLine' or B.LevelType = 'Process')
and A.OrganizationID = C.OrganizationID
and C.LevelType = 'ProductionLine'
and C.LevelCode like 'O0201%'
order by B.VariableId";
DataTable table = dataFactory.Query(mySql);
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 GetMaterialDataTable(string organizationId)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = @"SELECT A.[OrganizationID]
,A.[Name] + B.[Name] as [Name]
,A.[CreatedDate]
,A.[Type]
,B.[MaterialId]
FROM [NXJC].[dbo].[tz_Material] A,[NXJC].[dbo].[material_MaterialDetail] B
where A.[OrganizationID] like 'zc_nxjc_byc_byf'+'%'
and A.KeyID=B.KeyID
and A.[Enable]=1" ;
SqlParameter sqlParameter = new SqlParameter("@organizationId", organizationId);
DataTable table = dataFactory.Query(mySql, sqlParameter);
return(table);
}
/// <summary>
/// 初始化电能与产量数据库字典
/// </summary>
private static void InitializeMeterCatalogDictionary()
{
MeterCatalogDaictionary.Clear();
string connectionString = ConfigurationManager.ConnectionStrings["ManagementData"].ConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("DataBaseContrast");
query.AddCriterion("FactoryID", 0, CriteriaOperator.MoreThanOrEqual);
DataTable dt = factory.Query(query);
foreach (DataRow row in dt.Rows)
{
ProcessCatalogDictionary.Add(int.Parse(row["FactoryID"].ToString()), row["DataBaseName"].ToString().Trim());
}
}
/// <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 GetSystemVariableTypeList(string nodeID, string organizationId)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mPageId = GetPageID(nodeID);//获取page_ID
DataTable resultTable = new DataTable();
// string mSql = @"(SELECT A.[VariableId],A.[VariableName]
// FROM system_EnergyDataManualInputContrast A,
// [IndustryEnergy_SH].[dbo].[content] B
// WHERE
// A.[GroupId]=B.[PAGE_ID]
// AND B.[PAGE_ID]=@m_pageid
// AND A.[Enabled]=1)
// union
// (SELECT [VariableId],[VariableName]
// FROM system_EnergyDataManualInputContrast
// WHERE
// [GroupId] is NULL
// AND [Enabled]=1)";
string mSql = @"SELECT D.OrganizationID + A.[VariableId] as ItemId, A.[VariableId],D.Name + '_' + A.[VariableName] as [VariableName],A.[TYPE], D.LevelCode, D.OrganizationID
FROM system_EnergyDataManualInputContrast A,
[IndustryEnergy_SH].[dbo].[content] B, system_Organization C, system_Organization D
WHERE C.OrganizationID = @m_organizationId
AND D.LevelCode like C.LevelCode + '%'
AND A.[Type] = D.[Type]
AND A.[GroupId]=B.[PAGE_ID]
AND B.[PAGE_ID]=@m_pageid
AND A.[Enabled]=1
union
SELECT C.OrganizationID + A.[VariableId] as ItemId, A.[VariableId], C.Name + '_' + A.[VariableName] as [VariableName],A.[TYPE], C.LevelCode, C.OrganizationID
FROM system_EnergyDataManualInputContrast A, system_Organization B, system_Organization C
WHERE B.OrganizationID = @m_organizationId
AND C.LevelCode like B.LevelCode + '%'
AND A.[Type] = C.[Type]
AND A.[GroupId] is NULL
AND A.[Enabled]=1
order by [LevelCode], [Type], [VariableId]";
SqlParameter[] para = new SqlParameter[] { new SqlParameter("m_pageid", mPageId)
, new SqlParameter("m_organizationId", organizationId) };
resultTable = dataFactory.Query(mSql, para);
//}
return(resultTable);
}
/// <summary>
/// 获取用电量
/// </summary>
/// <param name="productLineId"></param>
/// <param name="formulaGroupId"></param>
/// <returns></returns>
public static DataTable GetElectricPowerCount(int productLineId, Guid formulaGroupId)
{
double peakUnitPrice = 0.6876;
double valleyUnitPrice = 0.4310;
double flatUnitPrice = 0.2744;
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("table_ElectricPowerCount");
query.AddCriterion("ProductLineID", "productLineId", productLineId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
query.AddCriterion("FormulaGroupID", "formulaGroupId", formulaGroupId.ToString(), SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
DataTable result = factory.Query(query);
DataColumn PeakCost = new DataColumn("PeakCost");
result.Columns.Add(PeakCost);
DataColumn ValleyCost = new DataColumn("ValleyCost");
result.Columns.Add(ValleyCost);
DataColumn FlatCost = new DataColumn("FlatCost");
result.Columns.Add(FlatCost);
DataColumn TotalCost = new DataColumn("TotalCost");
result.Columns.Add(TotalCost);
foreach (DataRow row in result.Rows)
{
double peakCost = int.Parse(row["PeakPower"].ToString()) * peakUnitPrice;
double valleyCost = int.Parse(row["ValleyPower"].ToString()) * valleyUnitPrice;
double flatCost = int.Parse(row["FlatPower"].ToString()) * flatUnitPrice;
double totalCost = peakCost + valleyCost + flatCost;
row["PeakCost"] = peakCost;
row["ValleyCost"] = valleyCost;
row["FlatCost"] = flatCost;
row["TotalCost"] = totalCost;
}
return(result);
}
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 string GetEletricalRoom(int factoryId)
{
try
{
string connString = ConnectionStringFactory.GetConnectionStringByFactoryID(factoryId, DatabaseType.EnergyDatabase);
ISqlServerDataFactory factory = new SqlServerDataFactory(connString);
ComplexQuery query = new ComplexQuery();
query.AddNeedField("ElectricalRoom", "ID");
query.AddNeedField("ElectricalRoom", "Name");
DataTable dt = factory.Query(query);
return(ComboboxJsonParser.DataTableToJson(dt, "ID", "Name"));
}
catch
{
return("[]");
}
}
public static void AddRelationshipToTzbalance(DataRow tzRow)
{
SingleBasicData singleBasicData=SingleBasicData.Creat();
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = @"select A.OrganizationID,A.WorkingTeam,A.ShiftDate
from system_ShiftArrangement A
where A.OrganizationID=@organizationId";
SqlParameter parameter = new SqlParameter("organizationId", singleBasicData.OrganizationId);
DataTable ruleTable = dataFactory.Query(mySql, parameter);
string t_ruleAll = ConfigService.GetConfig("ScheduleRule");
string regexString = @"{0}:\[.*?\]";
Regex reg = new Regex(string.Format(regexString,singleBasicData.OrganizationId));
Match match=reg.Match(t_ruleAll);
string t_match = match.Value;
string[] ruleArray=new string[]{};
if (t_match.Contains('['))
{
string t_rule=t_match.Substring(t_match.IndexOf('[')+1).TrimEnd(']');
ruleArray = t_rule.Split(',','，');
}
foreach (DataRow dr in ruleTable.Rows)
{
string workingTeam = dr["WorkingTeam"].ToString().Trim();
DateTime firstworkingDate=DateTime.Parse(dr["ShiftDate"].ToString().Trim());
string shif = AotuCalculate(ruleArray, firstworkingDate, DateTime.Parse(singleBasicData.Date));
switch (shif)
{
case "甲班":
tzRow["FirstWorkingTeam"] = workingTeam;
break;
case "乙班":
tzRow["SecondWorkingTeam"] = workingTeam;
break;
case "丙班":
tzRow["ThirdWorkingTeam"] = workingTeam;
break;
default:
continue;
//break;
}
}
}
public string GetEletricalRoom(int factoryId)
{
try
{
string connString = ConnectionStringFactory.GetByFactoryId(factoryId);
ISqlServerDataFactory factory = new SqlServerDataFactory(connString);
ComplexQuery query = new ComplexQuery();
query.AddNeedField("ElectricalRoom", "ID");
query.AddNeedField("ElectricalRoom", "Name");
DataTable dt = factory.Query(query);
return ComboboxJsonParser.DataTableToJson(dt, "ID", "Name");
}
catch
{
return "[]";
}
}
public static DataTable GetAssessmentVersionTable(string mOrganizationID, string mWorkingSectionID)
{
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", mOrganizationID),
new SqlParameter("@mWorkingSectionID", mWorkingSectionID) };
DataTable dt = factory.Query(mySql, para);
return(dt);
}
public static DataTable GetAllAssessmentResultTable(string mProductionID, string mWorkingSectionID, string mGroupId, string mStartTime, string mEndTime, string mStatisticalCycle)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
string mySql = @" SELECT A.[KeyId]
,B.Name as [ObjectName]
,B.[StatisticalCycle]
,CASE WHEN B.[StatisticalCycle]='day' THEN '日'
WHEN B.[StatisticalCycle]='month' THEN '月'
ELSE '年'
END as [CycleType]
,A.[Name]
,A.[StaffID]
,C.[Name] as [StaffName]
,A.[OrganizationID]
,A.[WorkingSectionID]
,A.[StartTime]
,CASE WHEN B.[StatisticalCycle]='day' THEN convert(char(10),A.[StartTime],120)
WHEN B.[StatisticalCycle]='month' THEN convert(char(7),A.[StartTime],120)
ELSE null
END as [Time]
,A.[EndTime]
,A.[TimeStamp]
FROM [dbo].[tz_ShiftAssessmentResult] A,[dbo].[assessment_ ShiftAssessmentResultGroup] B,[dbo].[system_StaffInfo] C
where A.[GroupId]=B.[GroupId]
and A.[StaffID]=C.[StaffInfoID]
and A.[GroupId]=@mGroupId
and A.[OrganizationID]=@mProductionID
and A.[WorkingSectionID]=@mWorkingSectionID
and [StartTime]>=convert(datetime,@mStartTime)
and [StartTime]<=convert(datetime,@mEndTime)
order by [Time] desc ";
SqlParameter[] para = { new SqlParameter("@mProductionID", mProductionID),
new SqlParameter("@mWorkingSectionID", mWorkingSectionID),
new SqlParameter("@mGroupId", mGroupId),
new SqlParameter("@mStartTime", mStartTime),
new SqlParameter("@mEndTime", mEndTime) };
DataTable dt = factory.Query(mySql, para);
return(dt);
}
private static DataTable GetAssessmentCatalogue()
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
string mySql = @"SELECT [AssessmentId]
,[Name]
,[Type]
,[ValueType]
,[Enabled]
,[Creator]
,[CreateTime]
,[Remark]
FROM [dbo].[assessment_AssessmentCatalogue]
where [Enabled]=1";
DataTable AssessmentCatalogueTable = factory.Query(mySql);
return(AssessmentCatalogueTable);
}
public static DataTable GetSnapShotAgentTable(string serverName)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string mySql = @"select * from
(select *, ROW_NUMBER() OVER ( PARTITION BY publisher ORDER BY run_requested_date DESC ) rid
FROM(select A.id,'['+A.publisher_db+']:'+A.publication as publisher,B.run_requested_date,B.start_execution_date,
B.last_executed_step_date,B.next_scheduled_run_date,A.[publication_type]
from [{0}].[distribution].[dbo].[MSsnapshot_agents] A,[{1}].[msdb].[dbo].[sysjobactivity] B
where A.job_id=B.job_id)as T1
) as T2
where rid=1
order by run_requested_date desc";
mySql = string.Format(mySql, serverName, serverName);
DataTable originalTable = dataFactory.Query(mySql);
return(originalTable);
}
public static DataTable GetMainMachineInfo(string myOrganizationId)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
string m_Sql = @"Select B.EquipmentCommonId as EquipmentId
,B.Name as Name
,'0' as EquipmentCommonId
,'' as VariableId
,'' as OrganizationId
,'' as OutputFormula
,B.DisplayIndex as DisplayIndex
FROM equipment_EquipmentCommonInfo B
where B.EquipmentCommonId in
(select distinct A.EquipmentCommonId
from equipment_EquipmentDetail A, analyse_KPI_OrganizationContrast C
where A.Enabled = 1
and A.OrganizationID = C.FactoryOrganizationID
and C.OrganizationID = '{0}')
union all
Select UPPER(A.EquipmentId) as EquipmentId
,A.EquipmentName as Name
,A.EquipmentCommonId as EquipmentCommonId
,A.VariableId as VariableId
,A.OrganizationId as OrganizationId
,A.OutputFormula as OutputFormula
,A.DisplayIndex as DisplayIndex
FROM equipment_EquipmentDetail A, analyse_KPI_OrganizationContrast C
where A.Enabled = 1
and A.OrganizationID = C.FactoryOrganizationID
and C.OrganizationID = '{0}'
order by DisplayIndex";
m_Sql = string.Format(m_Sql, myOrganizationId);
try
{
return(dataFactory.Query(m_Sql));
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 获得视图变量路径信息
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
public IEnumerable<DataPathInformation> GetDataPaths(int productLineId, string viewName)
{
string connectionString = ConnectionStringFactory.GetConnectionStringByProductLineID(productLineId, DatabaseType.DCSSystemDatabase);
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
IList<DataPathInformation> results = new List<DataPathInformation>();
Query query = new Query("ContrastTable");
query.AddCriterion("ViewName", "viewName", viewName, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(query);
foreach (DataRow item in table.Rows)
{
results.Add(new DataPathInformation
{
ViewId = item["VariableName"].ToString().Trim(),
FieldName = item["FieldName"].ToString().Trim(),
TableName = item["TableName"].ToString().Trim()
});
}
return results;
}
public static DataTable GetAmmeterErrorData(string organizationId, string startTime, string endTime)
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
// string mySql = @"select A.OrganizationID,A.vDate, A.Description
// from system_Error A
// where A.vDate>=@startTime
// and A.vDate<=@endTime
// and A.OrganizationID=@organizationId";
// SqlParameter[] parameters = { new SqlParameter("startTime", startTime), new SqlParameter("endTime", endTime),
// new SqlParameter("organizationId", organizationId)};
string mySql = @"select top(1000) A.vDate, A.Description
from system_Error A
where A.vDate>=@startTime
and A.vDate<=@endTime
order by A.vDate desc";
SqlParameter[] parameters = { new SqlParameter("startTime", startTime), new SqlParameter("endTime", endTime)};
DataTable table = dataFactory.Query(mySql, parameters);
return table;
}