public static DataTable GetEquipmentCommonInfo(string myOrganizationId, ISqlServerDataFactory myDataFactory)
{
string m_Sql = @"Select M.* from (
select distinct A.EquipmentCommonId as EquipmentId, A.Name, A.DisplayIndex as DisplayIndex, '0' as ParentEquipmentId
from equipment_EquipmentCommonInfo A, equipment_EquipmentDetail B, system_Organization C, system_Organization D
where A.EquipmentCommonId = B.EquipmentCommonId
and B.OrganizationID = C.OrganizationID
and B.Enabled = 1
and D.OrganizationID = '{0}'
and C.LevelCode like D.LevelCode + '%'
union all
select A.EquipmentId as EquipmentId, A.EquipmentName as Name,99999 as DisplayIndex, A.EquipmentCommonId as ParentEquipmentId
from equipment_EquipmentDetail A, system_Organization C, system_Organization D
where A.OrganizationID = C.OrganizationID
and A.Enabled = 1
and D.OrganizationID = '{0}'
and C.LevelCode like D.LevelCode + '%') M
order by M.ParentEquipmentId, M.DisplayIndex, M.Name";
m_Sql = string.Format(m_Sql, myOrganizationId);
try
{
DataTable m_EquipmentInfoTable = myDataFactory.Query(m_Sql);
return m_EquipmentInfoTable;
}
catch
{
return null;
}
}
private string GetFactoryOrganizationId(string myOrganizationId, ISqlServerDataFactory myDataFactory)
{
string m_Sql = @"SELECT A.OrganizationID
,A.FactoryOrganizationID
FROM analyse_KPI_OrganizationContrast A
where OrganizationID = '{0}'";
m_Sql = string.Format(m_Sql, myOrganizationId);
try
{
DataTable m_FactoryOrganizationTable = myDataFactory.Query(m_Sql);
if (m_FactoryOrganizationTable != null && m_FactoryOrganizationTable.Rows.Count > 0)
{
return(m_FactoryOrganizationTable.Rows[0]["FactoryOrganizationID"] != DBNull.Value ? m_FactoryOrganizationTable.Rows[0]["FactoryOrganizationID"].ToString() : "");
}
else
{
return("");
}
}
catch
{
return("");
}
}
public IEnumerable <DataItem> GetPlaybackDatas(DateTime myTime, string organizationId, IList <string> variableNames)
{
IList <DataItem> result = new List <DataItem>();
string ammeterConn = ConnectionStringFactory.GetAmmeterConnectionString(organizationId);
_dataFactory = new SqlServerDataFactory(ammeterConn);
IDictionary <string, List <FieldInformation> > fieldInformations = GetFeildInformation(organizationId, variableNames, "History");
DataTable table = GetDataItemTable(myTime, fieldInformations);
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 = table.Rows[0][item] is DBNull ? "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()
});
}
return(result);
}
public static DataTable GetEquipmentCommonInfo(string myOrganizationId, ISqlServerDataFactory myDataFactory)
{
string m_Sql = @"Select M.* from (
select distinct A.EquipmentCommonId as EquipmentId, A.Name, A.DisplayIndex as DisplayIndex, '0' as ParentEquipmentId
from equipment_EquipmentCommonInfo A, equipment_EquipmentDetail B, system_Organization C, system_Organization D
where A.EquipmentCommonId = B.EquipmentCommonId
and B.OrganizationID = C.OrganizationID
and B.Enabled = 1
and D.OrganizationID = '{0}'
and C.LevelCode like D.LevelCode + '%'
union all
select A.EquipmentId as EquipmentId, A.EquipmentName as Name,99999 as DisplayIndex, A.EquipmentCommonId as ParentEquipmentId
from equipment_EquipmentDetail A, system_Organization C, system_Organization D
where A.OrganizationID = C.OrganizationID
and A.Enabled = 1
and D.OrganizationID = '{0}'
and C.LevelCode like D.LevelCode + '%') M
order by M.ParentEquipmentId, M.DisplayIndex, M.Name";
m_Sql = string.Format(m_Sql, myOrganizationId);
try
{
DataTable m_EquipmentInfoTable = myDataFactory.Query(m_Sql);
return(m_EquipmentInfoTable);
}
catch
{
return(null);
}
}
public MonitorShellService(string nxjcconnString, string companyconnString, string dcsconnString)
{
if (nxjcconnString != "")
{
_nxjcFactory = new SqlServerDataFactory(nxjcconnString);
}
if (companyconnString != "")
{
_companyFactory = new SqlServerDataFactory(companyconnString);
}
if (dcsconnString != "")
{
_dcsFactory = new SqlServerDataFactory(dcsconnString);
}
}
private List<Buffer_RemoteWebService> _RemoteWebServiceBuffer; //建立远程更新webservice缓冲区
#endregion Fields
#region Constructors
public SetRealtimeData()
{
_IsClosing = false;
string connectionString = ConnectionStringFactory.NXJCConnectionString;
_dataFactory = new SqlServerDataFactory(connectionString);
UpdateInterval = ConfigService.UpdateInterval;
MinQueryInterval = ConfigService.MinQueryInterval;
DBThreadItemsCount = ConfigService.DBThreadItemsCount;
_FactoryOrganizationId = ConfigService.FactoryOrganizationId;
_LocalWebServiceBuffer = new List<Buffer_LocalWebService>();
_RemoteWebServiceBuffer = new List<Buffer_RemoteWebService>();
RealtimeMaxQueryThreadTime = 0;
RealtimeMaxQueryThreadCount = 0;
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
string dcsConn = ConnectionStringFactory.GetDCSConnectionString(organizationId);
_dataFactory = new SqlServerDataFactory(dcsConn);
EnergyContrastHelper contrastHelper = new EnergyContrastHelper(_type);
foreach (var item in contrastHelper.GetRealtimeDatas(organizationId, variableIds))
{
results.Add(item);
}
//foreach (var item in GetRealtimeDatas(organizationId))
//{
// results.Add(item);
//}
return(results);
}
public ProductLineRepository()
{
connectionString = ConnectionStringFactory.NXJCConnectionString;
dataFactory = new SqlServerDataFactory(connectionString);
}
public MeterDataDisplayService()
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
dataFactory = new SqlServerDataFactory(connectionString);
}
public BasicDataHelper(string connString)
{
_dataFactory = new SqlServerDataFactory(connString);
}
public Function_TerminalAlarm()
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
_dataFactory = new SqlServerDataFactory(connectionString);
}
public WorkingTeamService()
{
connString = ConnectionStringFactory.GetNXJCConnectionString();
dataFactory = new SqlServerDataFactory(connString);
}
//////////////////////////////////以下是改为Webservice新建方法///////////////////////////////
private Dictionary <string, string> GetTags(string myOrganizationId, IList <string> myVariableNames, string myTableName, string myValueType, ISqlServerDataFactory myDataFactory)
{
Dictionary <string, string> m_Tags = new Dictionary <string, string>();
string m_Sql = "";
string m_VariableName = "''";
if (myVariableNames != null)
{
for (int i = 0; i < myVariableNames.Count; i++)
{
if (i == 0)
{
m_VariableName = "'" + myVariableNames[i] + "'";
}
else
{
m_VariableName = m_VariableName + ",'" + myVariableNames[i] + "'";
}
}
}
//DCS的处理方法
if ("DCS" == _type)
{
m_Sql = @"SELECT ltrim(rtrim(A.TagName)) as TagName
,ltrim(rtrim(A.DCSName)) as DCSName
,ltrim(rtrim(A.FieldName)) as FieldName
,ltrim(rtrim(A.TagName)) as VariableName
FROM View_DCSContrast A
where A.DataTypeStandard = '{1}'
and A.TagName in ({0})";
m_Sql = string.Format(m_Sql, m_VariableName, myValueType);
}
else
{
m_Sql = @"SELECT ltrim(rtrim(A.TagName)) as TagName
,ltrim(rtrim(A.DCSName)) as DCSName
,ltrim(rtrim(A.FieldName)) as FieldName
,ltrim(rtrim(B.VariableName)) as VariableName
FROM View_DCSContrast A, MonitorContrast B
where A.DataTypeStandard = '{3}'
and A.TableName = B.TableName
and A.VariableName = B.FieldName
and A.[DBName]=B.[DatabaseName]
and B.TableName <> '{1}'
and B.type = '{0}'
and B.OrganizationID = '{4}'
and B.VariableName in ({2})";
}
m_Sql = string.Format(m_Sql, _type, myTableName, m_VariableName, myValueType, myOrganizationId);
try
{
DataTable m_TagsTable = myDataFactory.Query(m_Sql);
if (m_TagsTable != null)
{
for (int i = 0; i < m_TagsTable.Rows.Count; i++)
{
string m_KeyId = m_TagsTable.Rows[i]["VariableName"] != null ? m_TagsTable.Rows[i]["VariableName"].ToString() : "";
string m_KeyValue = m_TagsTable.Rows[i]["TagName"] != null ? m_TagsTable.Rows[i]["TagName"].ToString() : "";
if (!m_Tags.ContainsKey(m_KeyId) && m_KeyId != "" && m_KeyValue != "")
{
m_Tags.Add(m_KeyId, m_KeyValue);
}
}
}
}
catch
{
}
return(m_Tags);
}
static ConnectionStringFactory()
{
NXJCConnectionString = ApplicationSettingsFactory.GetApplicationSettings().NXJCConnectionString;
dataFactory = new SqlServerDataFactory(NXJCConnectionString);
Initialize();
}
private static void GetDailyProductionPlanData(ref DataTable myEquipmentCommonInfoTable, string myOrganizationId, string myDateTime, ISqlServerDataFactory myDataFactory)
{
////////初始化列////////////
if (myEquipmentCommonInfoTable != null)
{
myEquipmentCommonInfoTable.Columns.Add("Output_Plan", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("TimeOutput_Plan", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunTime_Plan", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunRate_Plan", typeof(decimal));
string[] m_MonthArray = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };
DateTime m_DateTime = DateTime.Parse(myDateTime);
string m_Sql = @"Select B.EquipmentId, C.QuotasID, C.Type, B.{2} as Value from tz_Plan A, plan_ProductionYearlyPlan B, plan_ProductionPlan_Template C, system_Organization D, system_Organization E
where A.Date = '{1}'
and A.OrganizationID = D.OrganizationID
and A.PlanType = 'Production'
and A.Statue = 1
and A.KeyId = B.KeyId
and B.QuotasID = C.QuotasID
and (C.QuotasID like '%产量%' or C.QuotasID like '%台时产量%' or C.QuotasID like '%运转率%' or C.QuotasID like '%运转时间%')
and C.Type in ('MaterialWeight','EquipmentUtilization')
and (C.OrganizationID = D.OrganizationID or C.OrganizationID is null)
and D.OrganizationID = '{0}'
and E.LevelCode like D.LevelCode + '%'";
m_Sql = string.Format(m_Sql, myOrganizationId, m_DateTime.Year.ToString(), m_MonthArray[m_DateTime.Month - 1]);
try
{
DataTable m_DailyProductionPlanTable = myDataFactory.Query(m_Sql);
for (int i = 0; i < myEquipmentCommonInfoTable.Rows.Count; i++)
{
myEquipmentCommonInfoTable.Rows[i]["Output_Plan"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["TimeOutput_Plan"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunTime_Plan"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunRate_Plan"] = 0.0m;
if (m_DailyProductionPlanTable != null)
{
for (int j = 0; j < m_DailyProductionPlanTable.Rows.Count; j++)
{
string m_QuotasID = m_DailyProductionPlanTable.Rows[j]["QuotasID"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["QuotasID"].ToString() : "";
if (m_QuotasID.Contains("产量") && myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_DailyProductionPlanTable.Rows[j]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[i]["Output_Plan"] = m_DailyProductionPlanTable.Rows[j]["Value"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["Value"] : 0;
}
else if (m_QuotasID.Contains("台时产量") && myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_DailyProductionPlanTable.Rows[j]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[i]["TimeOutput_Plan"] = m_DailyProductionPlanTable.Rows[j]["Value"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["Value"] : 0;
}
else if (m_QuotasID.Contains("运转时间") && myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_DailyProductionPlanTable.Rows[j]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[i]["RunTime_Plan"] = m_DailyProductionPlanTable.Rows[j]["Value"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["Value"] : 0;
}
else if (m_QuotasID.Contains("运转率") && myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_DailyProductionPlanTable.Rows[j]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[i]["RunRate_Plan"] = m_DailyProductionPlanTable.Rows[j]["Value"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["Value"] : 0;
}
}
}
}
}
catch
{
}
}
}
public ReportDataHelper()
{
connString = ConnectionStringFactory.NXJCConnectionString;
dataFactory = new SqlServerDataFactory(connString);
}
public MachineHaltReasonService()
{
string connString = ConnectionStringFactory.GetNXJCConnectionString();
dataFactory = new SqlServerDataFactory(connString);
}
/// <summary>
/// 设置峰谷平时间条件属性
/// </summary>
/// <param name="factoryOrganizationId"></param>
/// <param name="dataFactory"></param>
private void InitPVFTimeCriterion(ISqlServerDataFactory dataFactory)
{
//****如果没有该期段则初始化为“1=0”
peakTimeCriterion =morePeakTimeCriterion=valleyTimeCriterion=moreValleyTimeCriterion=flatTimeCriterion= "1=0";
//********
string sqlStr = @"SELECT b.StartTime,b.EndTime,b.Type
FROM system_PVF AS a,system_PVF_Detail as b
WHERE a.KeyID=b.KeyID AND
a.OrganizationID=@factoryOrganizationId AND
a.Flag='True'";
SqlParameter parameter = new SqlParameter("factoryOrganizationId", singleBasicDate.OrganizationId);
//峰谷平源数据
DataTable pvfTable = dataFactory.Query(sqlStr, parameter);
if (pvfTable.Rows.Count == 0)
{
StreamWriter sw = File.AppendText(singleBasicDate.Path);
sw.WriteLine("没有定义峰谷平时间段！");
sw.Flush();
sw.Close();
}
IList<string> timeList = new List<string>();
foreach (DataRow dr in pvfTable.Rows)
{
string mType = dr["Type"].ToString().Trim();
if (timeList.Contains(mType))
continue;//如果已有该类型则不添加
else
timeList.Add(mType);
}
foreach (string item in timeList)
{
string date = singleBasicDate.Date;
DataRow[] rows = pvfTable.Select("Type='" + item + "'");
switch (item)
{
case "峰期":
peakTimeCriterion = ConvertToSqlStr(rows, date);
break;
case "尖峰期":
morePeakTimeCriterion = ConvertToSqlStr(rows, date);
break;
case "谷期":
valleyTimeCriterion = ConvertToSqlStr(rows, date);
break;
case "深谷期":
moreValleyTimeCriterion = ConvertToSqlStr(rows, date);
break;
case "平期":
flatTimeCriterion = ConvertToSqlStr(rows, date);
break;
default:
StreamWriter sw = File.AppendText(singleBasicDate.Path);
sw.WriteLine("请检查数据库中峰谷平填写名称！");
sw.Flush();
sw.Close();
break;
}
}
}
/// <summary>
/// 设置早中晚班时间查询条件
/// </summary>
/// <param name="dataFactory"></param>
private void InitFSTTimeCriterion(ISqlServerDataFactory dataFactory)
{
//****如果没有该期段则初始化为“1=0”
firstTimeCriterion = secondTimeCriterion = thirdTimeCriterion = "1=0";
//****end***********
string sqlStr = @"SELECT StartTime,EndTime,Shifts
FROM system_ShiftDescription
WHERE OrganizationID=@organizationId";
SqlParameter parameter = new SqlParameter("organizationId", singleBasicDate.OrganizationId);
DataTable fstTable = dataFactory.Query(sqlStr, parameter);
if (fstTable.Rows.Count == 0)
{
StreamWriter sw = File.AppendText(singleBasicDate.Path);
sw.WriteLine("没有甲乙丙班时间段！");
sw.Flush();
sw.Close();
}
IList<string> shiftsList = new List<string>();
foreach (DataRow dr in fstTable.Rows)
{
string shift = dr["Shifts"].ToString().Trim();
if (shiftsList.Contains(shift))
continue;//如果已有该类型则不添加
else
shiftsList.Add(shift);
}
foreach (string item in shiftsList)
{
string date = singleBasicDate.Date;
DataRow[] rows = fstTable.Select("Shifts='" + item + "'");
switch (item)
{
case "甲班":
firstTimeCriterion = ConvertToSqlStr(rows, date,"甲班");
break;
case "乙班":
secondTimeCriterion = ConvertToSqlStr(rows, date,"乙班");
break;
case "丙班":
thirdTimeCriterion = ConvertToSqlStr(rows, date,"丙班");
break;
default:
throw new Exception("数据库中甲乙丙班名称填写有误！");
}
}
}
/// <summary>
/// 初始化时间规则
/// </summary>
/// <param name="factoryOrganizationId">分厂级别组织机构ID</param>
public void Init(ISqlServerDataFactory dataFactory)
{
InitPVFTimeCriterion(dataFactory);
InitFSTTimeCriterion(dataFactory);
}
public RealtimePowerProvider(string companyconnString)
{
_companyFactory = new SqlServerDataFactory(companyconnString);
}
private static void GetOutputData(ref DataTable myEquipmentCommonInfoTable, string myOrganizationId, string myDateTime, ISqlServerDataFactory myDataFactory)
{
if (myEquipmentCommonInfoTable != null)
{
myEquipmentCommonInfoTable.Columns.Add("Output_Day", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("Output_Month", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("Output_Year", typeof(decimal));
DateTime m_DateTime = DateTime.Parse(myDateTime);
string m_Sql = @"Select P.VariableId,P.VariableName, P.Value as ValueDay, Q.Value as ValueMonth, M.Value as ValueYear from
(Select B.VariableId, B.VariableName, sum(B.TotalPeakValleyFlatB) as Value from tz_Balance A, balance_Production B, system_Organization C, system_Organization D
where A.OrganizationID = C.OrganizationID
and A.StaticsCycle = 'day'
and A.TimeStamp >= '{1}'
and A.TimeStamp <= '{1}'
and A.BalanceId = B.KeyId
and B.VariableType = 'EquipmentOutput'
and B.ValueType = 'MaterialWeight'
and D.OrganizationID = '{0}'
and C.LevelCode like D.LevelCode + '%'
group by B.VariableId, B.VariableName) P,
(Select B.VariableId, B.VariableName, sum(B.TotalPeakValleyFlatB) as Value from tz_Balance A, balance_Production B, system_Organization C, system_Organization D
where A.OrganizationID = C.OrganizationID
and A.StaticsCycle = 'day'
and A.TimeStamp >= '{2}'
and A.TimeStamp <= '{1}'
and A.BalanceId = B.KeyId
and B.VariableType = 'EquipmentOutput'
and B.ValueType = 'MaterialWeight'
and D.OrganizationID = '{0}'
and C.LevelCode like D.LevelCode + '%'
group by B.VariableId, B.VariableName) Q,
(Select B.VariableId, B.VariableName, sum(B.TotalPeakValleyFlatB) as Value from tz_Balance A, balance_Production B, system_Organization C, system_Organization D
where A.OrganizationID = C.OrganizationID
and A.StaticsCycle = 'day'
and A.TimeStamp >= '{3}'
and A.TimeStamp <= '{1}'
and A.BalanceId = B.KeyId
and B.VariableType = 'EquipmentOutput'
and B.ValueType = 'MaterialWeight'
and D.OrganizationID = '{0}'
and C.LevelCode like D.LevelCode + '%'
group by B.VariableId, B.VariableName) M
where P.VariableId = Q.VariableId
and Q.VariableId = M.VariableId";
m_Sql = string.Format(m_Sql, myOrganizationId, m_DateTime.ToString("yyyy-MM-dd"), m_DateTime.ToString("yyyy-MM-01"), m_DateTime.ToString("yyyy-01-01"));
try
{
DataTable m_DailyProductionResultTable = myDataFactory.Query(m_Sql);
for (int i = 0; i < myEquipmentCommonInfoTable.Rows.Count; i++)
{
myEquipmentCommonInfoTable.Rows[i]["Output_Day"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["Output_Month"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["Output_Year"] = 0.0m;
if (m_DailyProductionResultTable != null)
{
for (int j = 0; j < m_DailyProductionResultTable.Rows.Count; j++)
{
string m_EquipmentId = m_DailyProductionResultTable.Rows[j]["VariableId"].ToString();
if (myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_EquipmentId)
{
myEquipmentCommonInfoTable.Rows[i]["Output_Day"] = m_DailyProductionResultTable.Rows[j]["ValueDay"] != DBNull.Value ? m_DailyProductionResultTable.Rows[j]["ValueDay"] : 0;
myEquipmentCommonInfoTable.Rows[i]["Output_Month"] = m_DailyProductionResultTable.Rows[j]["ValueMonth"] != DBNull.Value ? m_DailyProductionResultTable.Rows[j]["ValueMonth"] : 0;
myEquipmentCommonInfoTable.Rows[i]["Output_Year"] = m_DailyProductionResultTable.Rows[j]["ValueYear"] != DBNull.Value ? m_DailyProductionResultTable.Rows[j]["ValueYear"] : 0;
}
}
}
}
}
catch
{
}
}
}
public UsersRepository()
{
connectionString = ApplicationSettingsFactory.GetApplicationSettings().IndustryEnergy_SHConnectionString;
dataFactory = new SqlServerDataFactory(connectionString);
}
private static void GetRunTimeData(ref DataTable myEquipmentCommonInfoTable, string myOrganizationId, string myDateTime, ISqlServerDataFactory myDataFactory)
{
DateTime m_DateTime = DateTime.Parse(myDateTime);
if (myEquipmentCommonInfoTable != null)
{
myEquipmentCommonInfoTable.Columns.Add("RunTime_Day", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunRate_Day", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("TimeOutput_Day", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunTime_Month", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunRate_Month", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("TimeOutput_Month", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunTime_Year", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunRate_Year", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("TimeOutput_Year", typeof(decimal));
DataRow[] m_EquipmentCommonRows = myEquipmentCommonInfoTable.Select("ParentEquipmentId = '0'");
for (int i = 0; i < myEquipmentCommonInfoTable.Rows.Count; i++)
{
myEquipmentCommonInfoTable.Rows[i]["RunTime_Day"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunRate_Day"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["TimeOutput_Day"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunTime_Month"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunRate_Month"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["TimeOutput_Month"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunTime_Year"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunRate_Year"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["TimeOutput_Year"] = 0.0m;
}
for (int i = 0; i < m_EquipmentCommonRows.Length; i++)
{
/////////////////日统计////////////////
DataTable m_EquipmentUtilizationTableDay = RunIndicators.EquipmentRunIndicators.GetEquipmentUtilizationByCommonId(new string[] { "运转率", "运转时间", "台时产量" }, m_EquipmentCommonRows[i]["EquipmentId"].ToString(), myOrganizationId, m_DateTime.ToString("yyyy-MM-dd"), m_DateTime.ToString("yyyy-MM-dd"), myDataFactory);
if (m_EquipmentUtilizationTableDay != null)
{
for (int m = 0; m < myEquipmentCommonInfoTable.Rows.Count; m++)
{
for (int n = 0; n < m_EquipmentUtilizationTableDay.Rows.Count; n++)
{
if (myEquipmentCommonInfoTable.Rows[m]["EquipmentId"].ToString() == m_EquipmentUtilizationTableDay.Rows[n]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[m]["RunRate_Day"] = m_EquipmentUtilizationTableDay.Rows[n]["运转率"] != DBNull.Value ? m_EquipmentUtilizationTableDay.Rows[n]["运转率"] : 0;
myEquipmentCommonInfoTable.Rows[m]["RunTime_Day"] = m_EquipmentUtilizationTableDay.Rows[n]["运转时间"] != DBNull.Value ? m_EquipmentUtilizationTableDay.Rows[n]["运转时间"] : 0;
myEquipmentCommonInfoTable.Rows[m]["TimeOutput_Day"] = m_EquipmentUtilizationTableDay.Rows[n]["台时产量"] != DBNull.Value ? m_EquipmentUtilizationTableDay.Rows[n]["台时产量"] : 0;
break;
}
}
}
}
/////////////////月统计////////////////
DataTable m_EquipmentUtilizationTableMonth = RunIndicators.EquipmentRunIndicators.GetEquipmentUtilizationByCommonId(new string[] { "运转率", "运转时间", "台时产量" }, m_EquipmentCommonRows[i]["EquipmentId"].ToString(), myOrganizationId, m_DateTime.ToString("yyyy-MM-01"), m_DateTime.ToString("yyyy-MM-dd"), myDataFactory);
if (m_EquipmentUtilizationTableMonth != null)
{
for (int m = 0; m < myEquipmentCommonInfoTable.Rows.Count; m++)
{
for (int n = 0; n < m_EquipmentUtilizationTableMonth.Rows.Count; n++)
{
if (myEquipmentCommonInfoTable.Rows[m]["EquipmentId"].ToString() == m_EquipmentUtilizationTableMonth.Rows[n]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[m]["RunRate_Month"] = m_EquipmentUtilizationTableMonth.Rows[n]["运转率"] != DBNull.Value ? m_EquipmentUtilizationTableMonth.Rows[n]["运转率"] : 0;
myEquipmentCommonInfoTable.Rows[m]["RunTime_Month"] = m_EquipmentUtilizationTableMonth.Rows[n]["运转时间"] != DBNull.Value ? m_EquipmentUtilizationTableMonth.Rows[n]["运转时间"] : 0;
myEquipmentCommonInfoTable.Rows[m]["TimeOutput_Month"] = m_EquipmentUtilizationTableMonth.Rows[n]["台时产量"] != DBNull.Value ? m_EquipmentUtilizationTableMonth.Rows[n]["台时产量"] : 0;
break;
}
}
}
}
/////////////////年统计////////////////
DataTable m_EquipmentUtilizationTableYear = RunIndicators.EquipmentRunIndicators.GetEquipmentUtilizationByCommonId(new string[] { "运转率", "运转时间", "台时产量" }, m_EquipmentCommonRows[i]["EquipmentId"].ToString(), myOrganizationId, m_DateTime.ToString("yyyy-01-01"), m_DateTime.ToString("yyyy-MM-dd"), myDataFactory);
if (m_EquipmentUtilizationTableYear != null)
{
for (int m = 0; m < myEquipmentCommonInfoTable.Rows.Count; m++)
{
for (int n = 0; n < m_EquipmentUtilizationTableYear.Rows.Count; n++)
{
if (myEquipmentCommonInfoTable.Rows[m]["EquipmentId"].ToString() == m_EquipmentUtilizationTableYear.Rows[n]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[m]["RunRate_Year"] = m_EquipmentUtilizationTableYear.Rows[n]["运转率"] != DBNull.Value ? m_EquipmentUtilizationTableYear.Rows[n]["运转率"] : 0;
myEquipmentCommonInfoTable.Rows[m]["RunTime_Year"] = m_EquipmentUtilizationTableYear.Rows[n]["运转时间"] != DBNull.Value ? m_EquipmentUtilizationTableYear.Rows[n]["运转时间"] : 0;
myEquipmentCommonInfoTable.Rows[m]["TimeOutput_Year"] = m_EquipmentUtilizationTableYear.Rows[n]["台时产量"] != DBNull.Value ? m_EquipmentUtilizationTableYear.Rows[n]["台时产量"] : 0;
break;
}
}
}
}
}
}
}
/// <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);
}
public KPIMaintainService()
{
string connString = ConnectionStringFactory.GetNXJCConnectionString();
dataFactory = new SqlServerDataFactory(connString);
}
private static void GetDailyProductionPlanData(ref DataTable myEquipmentCommonInfoTable, string myOrganizationId, string myDateTime, ISqlServerDataFactory myDataFactory)
{
////////初始化列////////////
if (myEquipmentCommonInfoTable != null)
{
myEquipmentCommonInfoTable.Columns.Add("Output_Plan", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("TimeOutput_Plan", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunTime_Plan", typeof(decimal));
myEquipmentCommonInfoTable.Columns.Add("RunRate_Plan", typeof(decimal));
string[] m_MonthArray = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };
DateTime m_DateTime = DateTime.Parse(myDateTime);
string m_Sql = @"Select B.EquipmentId, C.QuotasID, C.Type, B.{2} as Value from tz_Plan A, plan_ProductionYearlyPlan B, plan_ProductionPlan_Template C, system_Organization D, system_Organization E
where A.Date = '{1}'
and A.OrganizationID = D.OrganizationID
and A.PlanType = 'Production'
and A.Statue = 1
and A.KeyId = B.KeyId
and B.QuotasID = C.QuotasID
and (C.QuotasID like '%产量%' or C.QuotasID like '%台时产量%' or C.QuotasID like '%运转率%' or C.QuotasID like '%运转时间%')
and C.Type in ('MaterialWeight','EquipmentUtilization')
and (C.OrganizationID = D.OrganizationID or C.OrganizationID is null)
and D.OrganizationID = '{0}'
and E.LevelCode like D.LevelCode + '%'";
m_Sql = string.Format(m_Sql, myOrganizationId, m_DateTime.Year.ToString(), m_MonthArray[m_DateTime.Month - 1]);
try
{
DataTable m_DailyProductionPlanTable = myDataFactory.Query(m_Sql);
for (int i = 0; i < myEquipmentCommonInfoTable.Rows.Count; i++)
{
myEquipmentCommonInfoTable.Rows[i]["Output_Plan"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["TimeOutput_Plan"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunTime_Plan"] = 0.0m;
myEquipmentCommonInfoTable.Rows[i]["RunRate_Plan"] = 0.0m;
if (m_DailyProductionPlanTable != null)
{
for (int j = 0; j < m_DailyProductionPlanTable.Rows.Count; j++)
{
string m_QuotasID = m_DailyProductionPlanTable.Rows[j]["QuotasID"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["QuotasID"].ToString() : "";
if (m_QuotasID.Contains("产量") && myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_DailyProductionPlanTable.Rows[j]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[i]["Output_Plan"] = m_DailyProductionPlanTable.Rows[j]["Value"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["Value"] : 0;
}
else if (m_QuotasID.Contains("台时产量") && myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_DailyProductionPlanTable.Rows[j]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[i]["TimeOutput_Plan"] = m_DailyProductionPlanTable.Rows[j]["Value"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["Value"] : 0;
}
else if (m_QuotasID.Contains("运转时间") && myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_DailyProductionPlanTable.Rows[j]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[i]["RunTime_Plan"] = m_DailyProductionPlanTable.Rows[j]["Value"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["Value"] : 0;
}
else if (m_QuotasID.Contains("运转率") && myEquipmentCommonInfoTable.Rows[i]["EquipmentId"].ToString() == m_DailyProductionPlanTable.Rows[j]["EquipmentId"].ToString())
{
myEquipmentCommonInfoTable.Rows[i]["RunRate_Plan"] = m_DailyProductionPlanTable.Rows[j]["Value"] != DBNull.Value ? m_DailyProductionPlanTable.Rows[j]["Value"] : 0;
}
}
}
}
}
catch
{
}
}
}
public StaffInfoService()
{
string connString = ConnectionStringFactory.GetNXJCConnectionString();
dataFactory = new SqlServerDataFactory(connString);
}
public Function_SaveAndSendAlarm()
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
_dataFactory = new SqlServerDataFactory(connectionString);
}
public CementTypeConvertCoefficientService()
{
string connString = ConnectionStringFactory.GetNXJCConnectionString();
dataFactory = new SqlServerDataFactory(connString);
}
public FormulaYearRepository()
{
connectionString = ConnectionStringFactory.NXJCConnectionString;
dataFactory = new SqlServerDataFactory(connectionString);
}
public DataSetProvider(string connectionString)
{
dataFactory = new SqlServerDataFactory(connectionString);
}
public Function_CollectionInterfaceAlarm()
{
string connectionString = ConnectionStringFactory.NXJCConnectionString;
_dataFactory = new SqlServerDataFactory(connectionString);
}
public UpdateParameters(string nxjcConnString)
{
_dataFactory = new SqlServerDataFactory(nxjcConnString);
}