public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
// string queryString = @"SELECT D.OrganizationID,C.VariableId,C.CumulantClass,C.CumulantLastClass,C.CumulantDay,SUM(C.CumulantDay+D.TotalPeakValleyFlat) AS CumulantMonth
// FROM RealtimeIncrementCumulant AS C,
// (select A.OrganizationID,B.VariableId,SUM(B.TotalPeakValleyFlat) as TotalPeakValleyFlat
// from tz_Balance as A,balance_Energy as B
// where A.BalanceId=B.KeyId
// AND A.TimeStamp>=CONVERT(varchar(8),GETDATE(),20)+'01'
// group by A.OrganizationID,B.VariableId) AS D
// WHERE C.VariableId=D.VariableId
// AND (C.VariableId='cement_CementOutput' OR C.VariableId='clinker_ClinkerOutput' OR C.VariableId='cementmill_ElectricityQuantity'
// OR C.VariableId='clinker_ElectricityQuantity' OR C.VariableId='clinker_PulverizedCoalInput')
// AND D.OrganizationID=@myOrganizationID
// GROUP BY D.OrganizationID,C.VariableId,C.CumulantClass,C.CumulantLastClass,C.CumulantDay";
// SqlParameter parameter = new SqlParameter("myOrganizationID", organizationId);
string queryString = @"select B.OrganizationID as OrganizationId, B.LevelCode, B.LevelType from system_Organization A, system_Organization B
where A.OrganizationID = @OrganizationID
and B.LevelCode like A.LevelCode + '%'
and (B.Type = '熟料' or B.Type = '水泥磨')";
IList <SqlParameter> queryStringparameters = new List <SqlParameter>();
queryStringparameters.Add(new SqlParameter("@OrganizationID", organizationId));
//ParametersHelper.AddParamsCondition(sqlSourceBase, sourceparameters, variableIds);
DataTable m_DataTable_OrganizationId = _nxjcFactory.Query(queryString, queryStringparameters.ToArray());
List <string> m_OrganizationIds = new List <string>();
if (m_DataTable_OrganizationId != null)
{
for (int i = 0; i < m_DataTable_OrganizationId.Rows.Count; i++)
{
m_OrganizationIds.Add(m_DataTable_OrganizationId.Rows[i]["OrganizationId"].ToString());
}
}
DataTable dt = ParametersHelper.GetSumCDMBalanceEnergyValue(m_OrganizationIds, _nxjcFactory, variableIds);
foreach (DataRow dr in dt.Rows)
{
DataItem itemClass = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">SumClass",
Value = dr["CumulantClass"].ToString().Trim()
};
DataItem itemDay = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">SumDay",
Value = dr["CumulantDay"].ToString().Trim()
};
DataItem itemMonth = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">SumMonth",
Value = dr["CumulantMonth"].ToString().Trim()
};
results.Add(itemClass);
results.Add(itemDay);
results.Add(itemMonth);
}
return(results);
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
SingletonForDataBase singleton = SingletonForDataBase.GetInstance();
Dictionary <string, string> myDictionary = (Dictionary <string, string>)singleton.AddFactoryDB(organizationId);
string queryString = @"select OrganizationID,VariableID,CoalDustConsumption from [{0}].[dbo].[RealtimeFormulaValue]
where OrganizationID=@organizationId";
StringBuilder baseString = new StringBuilder(queryString);
IList <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@organizationId", organizationId));
//SqlParameter[] parameters = { new SqlParameter("@organizationId", organizationId + "%") };
ParametersHelper.AddParamsCondition(baseString, parameters, variableIds);
DataTable dt = _companyFactory.Query(string.Format(queryString, myDictionary[organizationId].Trim()), parameters.ToArray());
foreach (DataRow dr in dt.Rows)
{
DataItem itemCoalDustConsumption = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableID"].ToString().Trim() + ">PulverizedCoalInput",
Value = dr["CoalDustConsumption"] is DBNull ? "0" : Convert.ToDecimal(dr["CoalDustConsumption"]).ToString("#.00").Trim()
};
results.Add(itemCoalDustConsumption);
}
return(results);
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
//ParametersHelper.AddParamsCondition(sqlSourceBase, sourceparameters, variableIds);
DataTable sourceDt = ParametersHelper.GetCDMBalanceEnergyValue(organizationId, _nxjcFactory);
//SqlParameter parameter = new SqlParameter("organizationId",organizationId);
//DataTable sourceDt = _nxjcFactory.Query(sqlSource, parameter);
//cdy修改结束
string sqlTemplate = @"select * from (SELECT A.OrganizationID,B.VariableID,B.ValueFormula
FROM system_Organization AS A,balance_Energy_Template AS B
WHERE A.Type=B.ProductionLineType
AND B.ValueType='ElectricityConsumption'
OR B.ValueType='CoalConsumption'
AND B.Enabled='True') as C
where C.OrganizationID=@organizationId";
StringBuilder sqlTemplateBase = new StringBuilder(sqlTemplate);
IList <SqlParameter> templateparameters = new List <SqlParameter>();
templateparameters.Add(new SqlParameter("@organizationId", organizationId));
ParametersHelper.AddParamsCondition(sqlTemplateBase, templateparameters, variableIds);
DataTable templateDt = _nxjcFactory.Query(sqlTemplateBase.ToString(), templateparameters.ToArray());
string[] columns = { "CumulantClass", "CumulantDay", "CumulantMonth" };
DataTable resultDt = EnergyConsumption.EnergyConsumptionCalculate.CalculateByOrganizationId(sourceDt, templateDt, "ValueFormula", columns);
foreach (DataRow dr in resultDt.Rows)
{
DataItem itemClass = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Class",
Value = dr["CumulantClass"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantClass"]).ToString("#.00").Trim()
};
results.Add(itemClass);
DataItem itemDay = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Day",
Value = dr["CumulantDay"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantDay"]).ToString("#.00").Trim()
};
results.Add(itemDay);
DataItem itemMonth = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Month",
Value = dr["CumulantMonth"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantMonth"]).ToString("#.00").Trim()
};
results.Add(itemMonth);
}
return(results);
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
// string queryString = @"select * from (SELECT A.OrganizationID,A.VariableId,A.CumulantClass,A.CumulantLastClass,A.CumulantDay,(A.CumulantDay+(case when B.MonthValue is null then 0 else B.MonthValue end)) AS CumulantMonth
// FROM RealtimeIncrementCumulant AS A
// left join
// (select D.OrganizationID,D.VariableId,sum(D.TotalPeakValleyFlat) as MonthValue
// from tz_Balance as C, balance_Energy as D
// where C.BalanceId=D.KeyId and TimeStamp>=CONVERT(varchar(8),GETDATE(),20)+'01'
// group by D.OrganizationID, VariableId) AS B
// on A.VariableId=B.VariableId and A.OrganizationID=B.OrganizationID) AS E
// where E.OrganizationID=@organizationId";
// StringBuilder baseString = new StringBuilder(queryString);
// IList<SqlParameter> parameters = new List<SqlParameter>();
// parameters.Add(new SqlParameter("@organizationId", organizationId));
// ParametersHelper.AddParamsCondition(baseString, parameters, variableIds);
DataTable dt = ParametersHelper.GetCDMBalanceEnergyValue(organizationId, _nxjcFactory, variableIds);
foreach (DataRow dr in dt.Rows)
{
DataItem itemClass = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Class",
Value = dr["CumulantClass"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantClass"]).ToString("#")
};
DataItem itemDay = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Day",
Value = dr["CumulantDay"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantDay"]).ToString("#")
};
DataItem itemMonth = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Month",
Value = dr["CumulantMonth"] is DBNull?"0":Convert.ToDecimal(dr["CumulantMonth"]).ToString("#").Trim()
};
results.Add(itemClass);
results.Add(itemDay);
results.Add(itemMonth);
}
return(results);
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
SingletonForDataBase singleton = SingletonForDataBase.GetInstance();
Dictionary <string, string> myDictionary = (Dictionary <string, string>)singleton.AddFactoryDB(organizationId);
string queryString = @"select OrganizationID,VariableID,CoalDustConsumption,DenominatorValue from [{0}].[dbo].[RealtimeFormulaValue]
where OrganizationID=@organizationId";
StringBuilder baseString = new StringBuilder(queryString);
IList <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@organizationId", organizationId));
ParametersHelper.AddParamsCondition(baseString, parameters, variableIds);
DataTable dt = _companyFactory.Query(string.Format(baseString.ToString(), myDictionary[organizationId].Trim()), parameters.ToArray());
foreach (DataRow item in dt.Rows)
{
if (!Convert.IsDBNull(item["DenominatorValue"]))
{
decimal denominatorValue = 0;
decimal.TryParse(item["DenominatorValue"].ToString().Trim(), out denominatorValue);
//if (denominatorValue != 0)
//{
decimal coalDustConsumption = 0;
decimal.TryParse(item["CoalDustConsumption"].ToString().Trim(), out coalDustConsumption);
coalDustConsumption = coalDustConsumption * 1000;
DataItem itemCoalConsumption = new DataItem
{
ID = item["OrganizationID"].ToString().Trim() + ">" + item["VariableID"].ToString().Trim() + ">" + "CoalConsumption",
Value = denominatorValue == 0 ? "0" : Convert.ToDecimal((coalDustConsumption / denominatorValue)).ToString("#.00").Trim() //产量为0时将煤耗致为0
};
results.Add(itemCoalConsumption);
//}
}
}
return(results);
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
string materialDetailString = @"select A.OrganizationID,B.VariableId,B.TagTableName,B.Formula
from [dbo].[tz_Material] as A,[dbo].[material_MaterialDetail] as B
where A.KeyID=B.KeyID and A.OrganizationID=@organizationId";
StringBuilder materialStringBase = new StringBuilder(materialDetailString);
IList <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@organizationId", organizationId));
ParametersHelper.AddParamsCondition(materialStringBase, parameters, variableIds);
DataTable materialDetailTable = _companyFactory.Query(materialStringBase.ToString(), parameters.ToArray());
StringBuilder baseString = new StringBuilder("select top 1");
string baseTableName = "";
IList <string> variables = new List <string>();
if (materialDetailTable.Rows.Count > 0)
{
foreach (DataRow dr in materialDetailTable.Rows)
{
baseString.Append(string.Format(" {0} as {1},", dr["Formula"].ToString().Trim(), dr["VariableId"].ToString().Trim()));
variables.Add(dr["VariableId"].ToString().Trim());
if (baseTableName == "")
{
baseTableName = dr["TagTableName"].ToString().Trim();
}
}
baseString.Remove(baseString.Length - 1, 1);
SingletonForDataBase singleton = SingletonForDataBase.GetInstance();
Dictionary <string, string> myDictionary = (Dictionary <string, string>)singleton.AddFactoryDB(organizationId);
baseString.Append(" from ").Append(string.Format("[{0}].[dbo].", myDictionary[organizationId].Trim()) + baseTableName).Append(" order by vDate desc");
DataTable resultDt = _companyFactory.Query(baseString.ToString());
foreach (var item in variables)
{
DataItem dataItem = new DataItem
{
ID = organizationId + ">" + item + ">Material"//resultDt.Rows[0][item] is DBNull?"0": Convert.ToDecimal(resultDt.Rows[0][item]).ToString("#.00").Trim()
};
decimal defaultValue = 0;
if (resultDt.Rows[0][item] is DBNull)
{
defaultValue = 0;
}
else
{
defaultValue = Convert.ToDecimal(resultDt.Rows[0][item]);
}
if (defaultValue < CorrectionValue.OutputCorrectionValue)
{
dataItem.Value = "0";
}
else
{
dataItem.Value = defaultValue.ToString("#.00");
}
results.Add(dataItem);
}
}
return(results);
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
// string sqlSource = @"SELECT C.OrganizationID,C.VariableId,SUM(C.CumulantClass) AS CumulantClass,SUM(C.CumulantLastClass) AS CumulantLastClass,SUM(C.CumulantDay) AS CumulantDay,SUM(C.CumulantDay+(case when D.TotalPeakValleyFlat is null then 0 else D.TotalPeakValleyFlat end)) AS CumulantMonth
// FROM RealtimeIncrementCumulant AS C
// left join
// (select A.OrganizationID,B.VariableId,SUM(B.TotalPeakValleyFlat) as TotalPeakValleyFlat
// from tz_Balance as A,balance_Energy as B
// where A.BalanceId=B.KeyId
// AND A.TimeStamp>=CONVERT(varchar(8),GETDATE(),20)+'01'
// group by A.OrganizationID,B.VariableId) AS D
// on C.VariableId=D.VariableId
// where (C.VariableId='cement_CementOutput' OR C.VariableId='clinker_ClinkerOutput' OR C.VariableId='cementmill_ElectricityQuantity'
// OR C.VariableId='clinker_ElectricityQuantity' OR C.VariableId='clinker_PulverizedCoalInput')
// -- AND D.OrganizationID=@myOrganizationID
// GROUP BY C.OrganizationID,C.VariableId";
string queryString = @"select B.OrganizationID as OrganizationId, B.LevelCode, B.LevelType from system_Organization A, system_Organization B
where A.OrganizationID = @OrganizationID
and B.LevelCode like A.LevelCode + '%'
and (B.Type = '熟料' or B.Type = '水泥磨')";
IList <SqlParameter> queryStringparameters = new List <SqlParameter>();
queryStringparameters.Add(new SqlParameter("@OrganizationID", organizationId));
//ParametersHelper.AddParamsCondition(sqlSourceBase, sourceparameters, variableIds);
DataTable m_DataTable_OrganizationId = _nxjcFactory.Query(queryString, queryStringparameters.ToArray());
List <string> m_OrganizationIds = new List <string>();
if (m_DataTable_OrganizationId != null)
{
for (int i = 0; i < m_DataTable_OrganizationId.Rows.Count; i++)
{
m_OrganizationIds.Add(m_DataTable_OrganizationId.Rows[i]["OrganizationId"].ToString());
}
}
string[] m_VariableIds = new string[] { "cement_CementOutput", "clinker_ClinkerOutput", "cementmill_ElectricityQuantity", "clinker_ElectricityQuantity", "clinker_PulverizedCoalInput" };
DataTable sourceDt = ParametersHelper.GetSumCDMBalanceEnergyValue(m_OrganizationIds, _nxjcFactory, m_VariableIds);
// string sqlSource = @"SELECT LEFT(G.Levelcode,5),C.VariableId,SUM(C.CumulantClass) AS CumulantClass,SUM(C.CumulantLastClass) AS CumulantLastClass,SUM(C.CumulantDay) AS CumulantDay,SUM(C.CumulantDay+(case when D.TotalPeakValleyFlat is null then 0 else D.TotalPeakValleyFlat end)) AS CumulantMonth
// FROM RealtimeIncrementCumulant AS C
// left join
// (select A.OrganizationID,B.VariableId,SUM(B.TotalPeakValleyFlat) as TotalPeakValleyFlat
// from tz_Balance as A,balance_Energy as B
// where A.BalanceId=B.KeyId
// AND A.TimeStamp>=CONVERT(varchar(8),GETDATE(),20)+'01'
// group by A.OrganizationID,B.VariableId) AS D
// on C.VariableId=D.VariableId
// left join system_Organization AS G
// on C.OrganizationID=G.OrganizationID
// where (C.VariableId='cement_CementOutput' OR C.VariableId='clinker_ClinkerOutput' OR C.VariableId='cementmill_ElectricityQuantity'
// OR C.VariableId='clinker_ElectricityQuantity' OR C.VariableId='clinker_PulverizedCoalInput')
// AND (G.LevelCode like (select LevelCode from system_Organization where OrganizationID=@myOrganizationID)+'%')
// GROUP BY LEFT(G.Levelcode,5),C.VariableId";
// SqlParameter parameter = new SqlParameter("myOrganizationID", organizationId);
// DataTable sourceDt = _nxjcFactory.Query(sqlSource, parameter);
//string m_OrganizationId = sourceDt.Rows[0]["OrganizationID"].ToString().Trim();
string sqlTemplate = @"SELECT A.VariableId,A.ValueFormula
FROM balance_Energy_Template AS A
WHERE
A.VariableId='clinker_CoalConsumption'
OR A.VariableId='cementmill_ElectricityConsumption'
OR A.VariableId='clinker_ElectricityConsumption'" ;
DataTable templateDt = _nxjcFactory.Query(sqlTemplate);
DataRow row = templateDt.NewRow();
row["VariableId"] = "cementmill_CoalConsumption";
row["ValueFormula"] = "[clinker_PulverizedCoalInput]*1000/[cement_CementOutput]";
templateDt.Rows.Add(row);
string[] columns = { "CumulantClass", "CumulantDay", "CumulantMonth" };
DataTable resultDt = EnergyConsumption.EnergyConsumptionCalculate.Calculate(sourceDt, templateDt, "ValueFormula", columns);
DataColumn column = new DataColumn("OrganizationID", typeof(string));
column.DefaultValue = organizationId;
resultDt.Columns.Add(column);
foreach (DataRow dr in resultDt.Rows)
{
DataItem itemClass = new DataItem
{
ID = organizationId + ">" + dr["VariableId"].ToString().Trim() + ">SumClass",
Value = dr["CumulantClass"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantClass"]).ToString("#.00").Trim()
};
results.Add(itemClass);
DataItem itemDay = new DataItem
{
ID = organizationId + ">" + dr["VariableId"].ToString().Trim() + ">SumDay",
Value = dr["CumulantDay"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantDay"]).ToString("#.00").Trim()
};
results.Add(itemDay);
DataItem itemMonth = new DataItem
{
ID = organizationId + ">" + dr["VariableId"].ToString().Trim() + ">SumMonth",
Value = dr["CumulantMonth"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantMonth"]).ToString("#.00").Trim()
};
results.Add(itemMonth);
}
return(results);
}
/// <summary>
/// 增加分步电耗公式显示功能
/// </summary>
/// <param name="organizationId"></param>
/// <param name="variableId"></param>
/// <param name="statisticType"></param>
/// <param name="variableIds"></param>
/// <returns></returns>
public Standard_GB16780_2012.Model_CaculateValue GetSumProcessFormula(string organizationId, string variableId, string statisticType, string[] variableIds)
{
string m_StatisticType = "";
Standard_GB16780_2012.Model_CaculateValue m_Model_CaculateValue = new Standard_GB16780_2012.Model_CaculateValue();
switch (statisticType)
{
case "SumProcessMonth":
m_StatisticType = "CumulantMonth";
break;
case "SumProcessDay":
m_StatisticType = "CumulantDay";
break;
case "SumProcessClass":
m_StatisticType = "CumulantClass";
break;
}
Dictionary <string, string> m_VariableContrastDic = new Dictionary <string, string>();
string queryString = @"select B.OrganizationID as OrganizationId, B.LevelCode, B.LevelType from system_Organization A, system_Organization B
where A.OrganizationID = @OrganizationID
and B.LevelCode like A.LevelCode + '%'
and (B.Type = '熟料' or B.Type = '水泥磨')";
IList <SqlParameter> queryStringparameters = new List <SqlParameter>();
queryStringparameters.Add(new SqlParameter("@OrganizationID", organizationId));
//ParametersHelper.AddParamsCondition(sqlSourceBase, sourceparameters, variableIds);
DataTable m_DataTable_OrganizationId = _nxjcFactory.Query(queryString, queryStringparameters.ToArray());
List <string> m_OrganizationIds = new List <string>();
if (m_DataTable_OrganizationId != null)
{
for (int i = 0; i < m_DataTable_OrganizationId.Rows.Count; i++)
{
m_OrganizationIds.Add(m_DataTable_OrganizationId.Rows[i]["OrganizationId"].ToString());
}
}
DataTable sourceDt = ParametersHelper.GetSumCDMBalanceEnergyValue(m_OrganizationIds, _nxjcFactory);
////////////////////增加辅助电量的各班以及当日的值/////////////////
DataTable auxiliaryProductionDt = GetSumAuxiliaryProductionQuantity(m_OrganizationIds.ToArray());
////////////////////////////////////////////////////////////////////
string m_OrganizationId = organizationId;//sourceDt.Rows[0]["OrganizationID"].ToString().Trim();
/////////////////////////分步电耗均摊辅助用电/////////////////////
string sqlTemplate = @"SELECT A.VariableId, A.ValueFormula, replace(A.VariableName,'电耗','分步电耗') as VariableName
FROM balance_Energy_Template AS A
WHERE (A.ValueType='ElectricityConsumption'
OR A.ValueType='CoalConsumption')
AND A.VariableId = '{0}'";
sqlTemplate = string.Format(sqlTemplate, variableId);
DataTable templateDt = _nxjcFactory.Query(sqlTemplate);
////////////////////////增加电量和产量标签对照表///////////////////
string m_Sql_VariableContrast = @"select A.VariableId, A.Name
from material_MaterialDetail A
group by A.VariableId, A.Name
union all
select A.VariableId + '_ElectricityQuantity' as VariableId, A.Name + '电量' as Name
from formula_FormulaDetail A
where A.LevelType <> 'MainMachine'
group by A.VariableId, A.Name";
DataTable m_VariableContrastTable = _nxjcFactory.Query(m_Sql_VariableContrast);
if (m_VariableContrastTable != null)
{
for (int i = 0; i < m_VariableContrastTable.Rows.Count; i++)
{
if (!m_VariableContrastDic.ContainsKey(m_VariableContrastTable.Rows[i]["VariableId"].ToString()))
{
m_VariableContrastDic.Add(m_VariableContrastTable.Rows[i]["VariableId"].ToString(), m_VariableContrastTable.Rows[i]["Name"].ToString());
}
}
}
////////////////分步电耗增加辅助电量均摊功能////////////////////
List <string> m_VariableQuantityArray = new List <string>(variableIds.Length);
for (int i = 0; i < variableIds.Length; i++)
{
if (!m_VariableQuantityArray.Contains(variableIds[i].Replace("ElectricityConsumption", "ElectricityQuantity")))
{
string m_VariableQuantityItem = variableIds[i].Replace("ElectricityConsumption", "ElectricityQuantity");
m_VariableQuantityArray.Add(m_VariableQuantityItem);
//形成公式
Standard_GB16780_2012.Model_CaculateFactor m_CaculateFactorTemp = new Standard_GB16780_2012.Model_CaculateFactor();
m_CaculateFactorTemp.FactorName = m_VariableQuantityItem;
m_CaculateFactorTemp.FactorValue = 0.0m;
m_Model_CaculateValue.CaculateFactor.Add(m_CaculateFactorTemp);
if (m_VariableContrastDic.ContainsKey(m_VariableQuantityItem))
{
if (m_Model_CaculateValue.CaculateFormula == "") //形成公式
{
m_Model_CaculateValue.CaculateFormula = "(" + m_VariableContrastDic[m_VariableQuantityItem];
}
else
{
m_Model_CaculateValue.CaculateFormula = m_Model_CaculateValue.CaculateFormula + "+" + m_VariableContrastDic[m_VariableQuantityItem];
}
}
else
{
if (m_Model_CaculateValue.CaculateFormula == "") //形成公式
{
m_Model_CaculateValue.CaculateFormula = "(" + m_VariableQuantityItem;
}
else
{
m_Model_CaculateValue.CaculateFormula = m_Model_CaculateValue.CaculateFormula + "+" + m_VariableQuantityItem;
}
}
}
}
if (templateDt != null && sourceDt != null)
{
string m_NumeratorVariable = "";
string m_DenominatorVariable = "";
if (templateDt.Rows.Count > 0)
{
m_Model_CaculateValue.CaculateName = templateDt.Rows[0]["VariableName"].ToString();
string m_ValueFormula = templateDt.Rows[0]["ValueFormula"].ToString().Replace("[", "").Replace("]", "");
m_NumeratorVariable = m_ValueFormula.Substring(0, m_ValueFormula.IndexOf('/'));
m_DenominatorVariable = m_ValueFormula.Substring(m_ValueFormula.IndexOf('/') + 1);
m_Model_CaculateValue.CaculateFormula = m_Model_CaculateValue.CaculateFormula + ")";
if (m_VariableContrastDic.ContainsKey(m_NumeratorVariable))
{
m_Model_CaculateValue.CaculateFormula = "(" + m_VariableContrastDic[m_NumeratorVariable] + "+辅助电量*" + m_VariableContrastDic[m_NumeratorVariable] + "/" + m_Model_CaculateValue.CaculateFormula + ")";
}
else
{
m_Model_CaculateValue.CaculateFormula = "(" + m_NumeratorVariable + "+辅助电量*" + m_NumeratorVariable + "/" + m_Model_CaculateValue.CaculateFormula + ")";
}
if (m_VariableContrastDic.ContainsKey(m_DenominatorVariable))
{
m_Model_CaculateValue.CaculateFormula = m_Model_CaculateValue.CaculateFormula + "/" + m_VariableContrastDic[m_DenominatorVariable];
}
else
{
m_Model_CaculateValue.CaculateFormula = m_Model_CaculateValue.CaculateFormula + "/" + m_DenominatorVariable;
}
}
Standard_GB16780_2012.Model_CaculateFactor m_CaculateFactorAuxiliaryProductionTemp = new Standard_GB16780_2012.Model_CaculateFactor();
m_CaculateFactorAuxiliaryProductionTemp.FactorName = "auxiliaryProduction_ElectricityQuantity";
m_CaculateFactorAuxiliaryProductionTemp.FactorValue = 0.0m;
Standard_GB16780_2012.Model_CaculateFactor m_CaculateFactorDenominatorTemp = new Standard_GB16780_2012.Model_CaculateFactor();
m_CaculateFactorDenominatorTemp.FactorName = m_DenominatorVariable;
m_CaculateFactorDenominatorTemp.FactorValue = 0.0m;
m_Model_CaculateValue.CaculateFactor.Add(m_CaculateFactorDenominatorTemp);
m_Model_CaculateValue.CaculateFactor.Add(m_CaculateFactorAuxiliaryProductionTemp);
for (int i = 0; i < sourceDt.Rows.Count; i++)
{
for (int j = 0; j < m_Model_CaculateValue.CaculateFactor.Count; j++)
{
if (m_Model_CaculateValue.CaculateFactor[j].FactorName == sourceDt.Rows[i]["VariableId"].ToString())
{
m_Model_CaculateValue.CaculateFactor[j].FactorValue = m_Model_CaculateValue.CaculateFactor[j].FactorValue + (decimal)sourceDt.Rows[i][m_StatisticType];
}
}
}
if (auxiliaryProductionDt != null && auxiliaryProductionDt.Rows.Count > 0)
{
for (int i = 0; i < m_Model_CaculateValue.CaculateFactor.Count; i++)
{
if (m_Model_CaculateValue.CaculateFactor[i].FactorName == "auxiliaryProduction_ElectricityQuantity")
{
string m_StatisticTypeTemp = m_StatisticType == "CumulantMonth" ? "CumulantDay" : m_StatisticType;
m_Model_CaculateValue.CaculateFactor[i].FactorValue = m_Model_CaculateValue.CaculateFactor[i].FactorValue + (decimal)auxiliaryProductionDt.Rows[0][m_StatisticTypeTemp];
}
}
}
for (int i = 0; i < m_Model_CaculateValue.CaculateFactor.Count; i++)
{
if (m_VariableContrastDic.ContainsKey(m_Model_CaculateValue.CaculateFactor[i].FactorName))
{
m_Model_CaculateValue.CaculateFactor[i].FactorName = m_VariableContrastDic[m_Model_CaculateValue.CaculateFactor[i].FactorName];
}
}
}
return(m_Model_CaculateValue);
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
// string sqlSource = @"SELECT A.OrganizationID,A.VariableId,SUM(A.CumulantClass) AS CumulantClass,SUM(A.CumulantLastClass) AS CumulantLastClass,SUM(A.CumulantDay) AS CumulantDay,SUM(A.CumulantDay+(case when B.MonthValue is null then 0 else B.MonthValue end)) AS CumulantMonth
// FROM RealtimeIncrementCumulant AS A
// left join
// (select C.OrganizationID,D.VariableId,sum(D.TotalPeakValleyFlat) as MonthValue
// from tz_Balance as C, balance_Energy as D
// where C.BalanceId=D.KeyId and TimeStamp>=CONVERT(varchar(8),GETDATE(),20)+'01'
// group by C.OrganizationID, D.VariableId) AS B
// on A.VariableId=B.VariableId
// where B.OrganizationID=@myOrganizationID
//GROUP BY A.OrganizationID,A.VariableId";
string queryString = @"select B.OrganizationID as OrganizationId, B.LevelCode, B.LevelType from system_Organization A, system_Organization B
where A.OrganizationID = @OrganizationID
and B.LevelCode like A.LevelCode + '%'
and (B.Type = '熟料' or B.Type = '水泥磨')";
IList <SqlParameter> queryStringparameters = new List <SqlParameter>();
queryStringparameters.Add(new SqlParameter("@OrganizationID", organizationId));
//ParametersHelper.AddParamsCondition(sqlSourceBase, sourceparameters, variableIds);
DataTable m_DataTable_OrganizationId = _nxjcFactory.Query(queryString, queryStringparameters.ToArray());
List <string> m_OrganizationIds = new List <string>();
if (m_DataTable_OrganizationId != null)
{
for (int i = 0; i < m_DataTable_OrganizationId.Rows.Count; i++)
{
m_OrganizationIds.Add(m_DataTable_OrganizationId.Rows[i]["OrganizationId"].ToString());
}
}
DataTable sourceDt = ParametersHelper.GetSumCDMBalanceEnergyValue(m_OrganizationIds, _nxjcFactory);
////////////////////增加辅助电量的各班以及当日的值/////////////////
DataTable auxiliaryProductionDt = GetSumAuxiliaryProductionQuantity(m_OrganizationIds.ToArray());
////////////////////////////////////////////////////////////////////
// string sqlSource = @"SELECT LEFT(G.Levelcode,5),A.VariableId,SUM(A.CumulantClass) AS CumulantClass,SUM(A.CumulantLastClass) AS CumulantLastClass,SUM(A.CumulantDay) AS CumulantDay,SUM(A.CumulantDay+(case when B.MonthValue is null then 0 else B.MonthValue end)) AS CumulantMonth
// FROM RealtimeIncrementCumulant AS A
// left join
// (select C.OrganizationID,D.VariableId,sum(D.TotalPeakValleyFlat) as MonthValue
// from tz_Balance as C, balance_Energy as D
// where C.BalanceId=D.KeyId and TimeStamp>=CONVERT(varchar(8),GETDATE(),20)+'01'
// group by C.OrganizationID, D.VariableId) AS B
// on A.VariableId=B.VariableId
// left join system_Organization AS G
// on A.OrganizationID=G.OrganizationID
// where (G.LevelCode like (select LevelCode from system_Organization where OrganizationID=@myOrganizationID)+'%')
// GROUP BY LEFT(G.Levelcode,5),A.VariableId";
// SqlParameter parameter = new SqlParameter("myOrganizationID", organizationId);
// DataTable sourceDt = _nxjcFactory.Query(sqlSource,parameter);
string m_OrganizationId = organizationId;//sourceDt.Rows[0]["OrganizationID"].ToString().Trim();
/////////////////////////分步电耗均摊辅助用电/////////////////////
string sqlTemplate = @"SELECT A.VariableId, A.ValueFormula
FROM balance_Energy_Template AS A
WHERE (A.ValueType='ElectricityConsumption'
OR A.ValueType='CoalConsumption')
AND A.VariableId in ({0})";
////////////增加标签筛选VariableId in
string m_VariableIdString = "";
for (int i = 0; i < variableIds.Length; i++)
{
if (i == 0)
{
m_VariableIdString = "'" + variableIds[i] + "'";
}
else
{
m_VariableIdString = m_VariableIdString + ",'" + variableIds[i] + "'";
}
}
sqlTemplate = string.Format(sqlTemplate, m_VariableIdString);
DataTable templateDt = _nxjcFactory.Query(sqlTemplate);
////////////////分步电耗增加辅助电量均摊功能////////////////////
List <string> m_VariableQuantityArray = new List <string>(variableIds.Length);
for (int i = 0; i < variableIds.Length; i++)
{
if (!m_VariableQuantityArray.Contains(variableIds[i].Replace("ElectricityConsumption", "ElectricityQuantity")))
{
m_VariableQuantityArray.Add(variableIds[i].Replace("ElectricityConsumption", "ElectricityQuantity"));
}
}
if (templateDt != null && sourceDt != null)
{
decimal m_SumProcessValueClass = 0.0m;
decimal m_SumProcessValueDay = 0.0m;
decimal m_SumProcessValueMonth = 0.0m;
decimal m_AuxiliaryProductionValueClass = 0.0m;
decimal m_AuxiliaryProductionValueDay = 0.0m;
decimal m_AuxiliaryProductionValueMonth = 0.0m;
for (int i = 0; i < sourceDt.Rows.Count; i++)
{
if (m_VariableQuantityArray.Contains(sourceDt.Rows[i]["VariableId"].ToString()))
{
m_SumProcessValueClass = m_SumProcessValueClass + (decimal)sourceDt.Rows[i]["CumulantClass"];
m_SumProcessValueDay = m_SumProcessValueDay + (decimal)sourceDt.Rows[i]["CumulantDay"];
m_SumProcessValueMonth = m_SumProcessValueMonth + (decimal)sourceDt.Rows[i]["CumulantMonth"];
}
if (sourceDt.Rows[i]["VariableId"].ToString() == "auxiliaryProduction_ElectricityQuantity")
{
m_AuxiliaryProductionValueClass = m_AuxiliaryProductionValueClass + (decimal)sourceDt.Rows[i]["CumulantClass"];
m_AuxiliaryProductionValueDay = m_AuxiliaryProductionValueDay + (decimal)sourceDt.Rows[i]["CumulantDay"];
m_AuxiliaryProductionValueMonth = m_AuxiliaryProductionValueMonth + (decimal)sourceDt.Rows[i]["CumulantMonth"];
if (auxiliaryProductionDt != null && auxiliaryProductionDt.Rows.Count > 0)
{
m_AuxiliaryProductionValueClass = m_AuxiliaryProductionValueClass + (decimal)auxiliaryProductionDt.Rows[0]["CumulantClass"];
m_AuxiliaryProductionValueDay = m_AuxiliaryProductionValueDay + (decimal)auxiliaryProductionDt.Rows[0]["CumulantDay"];
m_AuxiliaryProductionValueMonth = m_AuxiliaryProductionValueMonth + (decimal)auxiliaryProductionDt.Rows[0]["CumulantDay"];
}
}
}
for (int i = 0; i < sourceDt.Rows.Count; i++)
{
if (m_VariableQuantityArray.Contains(sourceDt.Rows[i]["VariableId"].ToString()))
{
if (m_SumProcessValueClass != 0)
{
sourceDt.Rows[i]["CumulantClass"] = (decimal)sourceDt.Rows[i]["CumulantClass"] + (decimal)sourceDt.Rows[i]["CumulantClass"] * m_AuxiliaryProductionValueClass / m_SumProcessValueClass;
}
if (m_SumProcessValueDay != 0)
{
sourceDt.Rows[i]["CumulantDay"] = (decimal)sourceDt.Rows[i]["CumulantDay"] + (decimal)sourceDt.Rows[i]["CumulantDay"] * m_AuxiliaryProductionValueDay / m_SumProcessValueDay;
}
if (m_SumProcessValueMonth != 0)
{
sourceDt.Rows[i]["CumulantMonth"] = (decimal)sourceDt.Rows[i]["CumulantMonth"] + (decimal)sourceDt.Rows[i]["CumulantMonth"] * m_AuxiliaryProductionValueMonth / m_SumProcessValueMonth;
}
}
}
}
//////////////////////////////////////////////////////////////////////////////////
string[] columns = { "CumulantClass", "CumulantDay", "CumulantMonth" };
DataTable resultDt = EnergyConsumption.EnergyConsumptionCalculate.Calculate(sourceDt, templateDt, "ValueFormula", columns);
DataColumn column = new DataColumn("OrganizationID", typeof(string));
column.DefaultValue = m_OrganizationId;
resultDt.Columns.Add(column);
foreach (DataRow dr in resultDt.Rows)
{
DataItem itemClass = new DataItem
{
ID = organizationId + ">" + dr["VariableId"].ToString().Trim() + ">SumProcessClass",
Value = dr["CumulantClass"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantClass"]).ToString("#.00").Trim()
};
results.Add(itemClass);
DataItem itemDay = new DataItem
{
ID = organizationId + ">" + dr["VariableId"].ToString().Trim() + ">SumProcessDay",
Value = dr["CumulantDay"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantDay"]).ToString("#.00").Trim()
};
results.Add(itemDay);
DataItem itemMonth = new DataItem
{
ID = organizationId + ">" + dr["VariableId"].ToString().Trim() + ">SumProcessMonth",
Value = dr["CumulantMonth"] is DBNull ? "0" : Convert.ToDecimal(dr["CumulantMonth"]).ToString("#.00").Trim()
};
results.Add(itemMonth);
}
return(results);
}
/// <summary>
/// 获得年月日BalanceEnery信息
/// </summary>
/// <param name="myOrganizationId">组织机构ID</param>
/// <param name="myNxjcFactory">数据库适配器</param>
/// <returns>BalanceEnery信息</returns>
public static DataTable GetCDMBalanceEnergyValue(string myOrganizationId, ISqlServerDataFactory myNxjcFactory, params string[] myVariableIds)
{
string sqlSource = @"Select A.OrganizationID, A.VariableId,
(case when A.CumulantClass<=@correctionValue then 0 else A.CumulantClass end) as CumulantClass,
(case when A.CumulantLastClass<=@correctionValue then 0 else A.CumulantLastClass end) as CumulantLastClass,
(case when A.CumulantDay<=@correctionValue then 0 else A.CumulantDay end) as CumulantDay,
(case when (A.CumulantDay+(case when B.MonthValue is null then 0 else B.MonthValue end))<=@correctionValue then 0
else (A.CumulantDay+(case when B.MonthValue is null then 0 else B.MonthValue end)) end) AS CumulantMonth
from
(select M.OrganizationID, M.VariableId,
sum(M.CumulantClass) as CumulantClass,
sum(M.CumulantLastClass) as CumulantLastClass,
sum(M.CumulantDay) as CumulantDay
from
(SELECT distinct C.OrganizationID, D.OrganizationID as DetailOrganizationID, D.VariableId,
(D.CumulantClass) as CumulantClass,
(D.CumulantLastClass) as CumulantLastClass,
(D.CumulantDay) as CumulantDay
FROM (select a.[OrganizationID],b.[ClinkerOrganizationID]
from
(select [OrganizationID],[ClinkerOrganizationID]=convert(xml,' <root> <v>'+replace([ClinkerOrganizationID],',',' </v> <v>')+' </v> </root>') from [analyse_KPI_OrganizationContrast])a
outer apply
(select [ClinkerOrganizationID]=C.v.value('.','nvarchar(100)') from a.[ClinkerOrganizationID].nodes('/root/v')C(v))b
where a.[OrganizationID] = @organizationId) as C, RealtimeIncrementCumulant AS D
where D.OrganizationID = C.OrganizationID or (D.OrganizationID = C.ClinkerOrganizationID and D.Type <> 'Formula')) M
group by M.OrganizationID, M.VariableId) A
left join
(select M.OrganizationID, M.VariableId, sum(M.MonthValue) as MonthValue
from
(select distinct D.OrganizationID, D.VariableId,(D.TotalPeakValleyFlat) as MonthValue
from tz_Balance as C, balance_Energy as D,
(select a.[OrganizationID],b.[ClinkerOrganizationID]
from
(select [OrganizationID],[ClinkerOrganizationID]=convert(xml,' <root> <v>'+replace([ClinkerOrganizationID],',',' </v> <v>')+' </v> </root>') from [dbo].[analyse_KPI_OrganizationContrast])a
outer apply
(select [ClinkerOrganizationID]=C.v.value('.','nvarchar(100)') from a.[ClinkerOrganizationID].nodes('/root/v')C(v))b
where a.[OrganizationID] = @organizationId
) as E
where C.BalanceId=D.KeyId and TimeStamp>=CONVERT(varchar(8),GETDATE(),20)+'01'
and C.StaticsCycle = 'day'
and (D.OrganizationID =E.OrganizationID or (D.OrganizationID = E.ClinkerOrganizationID and D.ValueType = 'MaterialWeight'))) M
group by M.OrganizationID, M.VariableId) B on A.OrganizationID = B.OrganizationID and A.VariableId = B.VariableId
where A.OrganizationID = @organizationId";
//cdy修改开始
//StringBuilder sqlSourceBase = new StringBuilder(sqlSource);
if (myVariableIds != null && myVariableIds.Length > 0)
{
StringBuilder baseString = new StringBuilder(sqlSource);
IList <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@organizationId", myOrganizationId));
parameters.Add(new SqlParameter("@correctionValue", CorrectionValue.OutputCorrectionValue));
ParametersHelper.AddParamsCondition(baseString, parameters, myVariableIds, "A");
string m_Sql = baseString.ToString();
DataTable dt = myNxjcFactory.Query(m_Sql, parameters.ToArray());
return(dt);
}
else
{
IList <SqlParameter> sourceparameters = new List <SqlParameter>();
sourceparameters.Add(new SqlParameter("@organizationId", myOrganizationId));
sourceparameters.Add(new SqlParameter("@correctionValue", CorrectionValue.OutputCorrectionValue));
//ParametersHelper.AddParamsCondition(sqlSourceBase, sourceparameters, variableIds);
DataTable sourceDt = myNxjcFactory.Query(sqlSource, sourceparameters.ToArray());
return(sourceDt);
}
}
public static DataTable GetSumCDMBalanceEnergyValue(List <string> myOrganizationIds, ISqlServerDataFactory myNxjcFactory, params string[] myVariableIds)
{
string m_OrganizationIds = "";
string sqlSource = @"SELECT A.VariableId,
sum(case when A.CumulantClass<=@correctionValue then 0 else A.CumulantClass end) as CumulantClass,
sum(case when A.CumulantLastClass<=@correctionValue then 0 else A.CumulantLastClass end) as CumulantLastClass,
sum(case when A.CumulantDay<=@correctionValue then 0 else A.CumulantDay end) as CumulantDay,
sum((case when (A.CumulantDay+(case when B.MonthValue is null then 0 else B.MonthValue end))<=@correctionValue then 0
else (A.CumulantDay+(case when B.MonthValue is null then 0 else B.MonthValue end)) end)) AS CumulantMonth
FROM RealtimeIncrementCumulant AS A
LEFT JOIN
(select D.OrganizationID,D.VariableId,sum(D.TotalPeakValleyFlat) as MonthValue
from tz_Balance as C, balance_Energy as D
where C.BalanceId=D.KeyId and TimeStamp>=CONVERT(varchar(8),GETDATE(),20)+'01'
and C.StaticsCycle = 'day'
and D.OrganizationID in ({0})
group by D.OrganizationID, D.VariableId) AS B
ON A.VariableId=B.VariableId and A.OrganizationID=B.OrganizationID
where A.OrganizationID in ({0})";
//cdy修改开始
//StringBuilder sqlSourceBase = new StringBuilder(sqlSource);
if (myOrganizationIds != null)
{
for (int i = 0; i < myOrganizationIds.Count; i++)
{
if (i == 0)
{
m_OrganizationIds = "'" + myOrganizationIds[i] + "'";
}
else
{
m_OrganizationIds = m_OrganizationIds + ",'" + myOrganizationIds[i] + "'";
}
}
if (m_OrganizationIds == "")
{
m_OrganizationIds = "''";
}
}
if (myVariableIds != null && myVariableIds.Length > 0)
{
sqlSource = string.Format(sqlSource, m_OrganizationIds);
StringBuilder baseString = new StringBuilder(sqlSource);
IList <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@correctionValue", CorrectionValue.OutputCorrectionValue));
ParametersHelper.AddParamsCondition(baseString, parameters, myVariableIds, "A");
string m_Sql = baseString.ToString();
m_Sql = m_Sql + " group by A.VariableId";
DataTable dt = myNxjcFactory.Query(m_Sql, parameters.ToArray());
return(dt);
}
else
{
sqlSource = string.Format(sqlSource, m_OrganizationIds);
IList <SqlParameter> sourceparameters = new List <SqlParameter>();
sourceparameters.Add(new SqlParameter("@correctionValue", CorrectionValue.OutputCorrectionValue));
//ParametersHelper.AddParamsCondition(sqlSourceBase, sourceparameters, variableIds);
sqlSource = sqlSource + " group by A.VariableId";
DataTable sourceDt = myNxjcFactory.Query(sqlSource, sourceparameters.ToArray());
return(sourceDt);
}
}
