public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
string queryString = @"select OrganizationID,VariableID,Power from [zc_nxjc_byc_byf].[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(queryString.ToString(), parameters.ToArray());
foreach (DataRow dr in dt.Rows)
{
DataItem itemPower = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableID"].ToString().Trim() + ">Power",
Value = dr["Power"].ToString().Trim()
};
results.Add(itemPower);
}
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 [NXJC].[dbo].[tz_Material] as A,[NXJC].[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);
baseString.Append(" from ").Append("[zc_nxjc_byc_byf].[dbo]." + 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",
Value = resultDt.Rows[0][item].ToString().Trim()
};
results.Add(dataItem);
}
}
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+B.MonthValue) AS CumulantMonth
FROM RealtimeIncrementCumulant AS A,
(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
WHERE 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 = _nxjcFactory.Query(baseString.ToString(), parameters.ToArray());
foreach (DataRow dr in dt.Rows)
{
DataItem itemClass = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Class",
Value = dr["CumulantClass"].ToString().Trim()
};
DataItem itemDay = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Day",
Value = dr["CumulantDay"].ToString().Trim()
};
DataItem itemMonth = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Month",
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>();
string queryString = @"select OrganizationID,VariableID,FormulaValue,DenominatorValue from [zc_nxjc_byc_byf].[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(baseString.ToString(), 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 formulaValue = 0;
decimal.TryParse(item["FormulaValue"].ToString().Trim(), out formulaValue);
DataItem itemElectricityConsumption = new DataItem
{
ID = item["OrganizationID"].ToString().Trim() + ">" + item["VariableID"].ToString().Trim() + ">" + "ElectricityConsumption",
Value = denominatorValue == 0?"0":(formulaValue / denominatorValue).ToString() //产量为0时将电耗致为0
};
results.Add(itemElectricityConsumption);
//}
}
}
return(results);
}
public IEnumerable <DataItem> GetDataItem(string organizationId, params string[] variableIds)
{
IList <DataItem> results = new List <DataItem>();
string sqlSource = @"select * from (SELECT A.OrganizationID,A.VariableId,A.CumulantClass,A.CumulantLastClass,A.CumulantDay,(A.CumulantDay+B.MonthValue) AS CumulantMonth
FROM RealtimeIncrementCumulant AS A,
(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
WHERE A.VariableId=B.VariableId and A.OrganizationID=B.OrganizationID) AS E
where E.OrganizationID=@organizationId";
//cdy修改开始
//StringBuilder sqlSourceBase = new StringBuilder(sqlSource);
//IList<SqlParameter> sourceparameters = new List<SqlParameter>();
//sourceparameters.Add(new SqlParameter("@organizationId", organizationId));
//ParametersHelper.AddParamsCondition(sqlSourceBase, sourceparameters, variableIds);
//DataTable sourceDt = _nxjcFactory.Query(sqlSourceBase.ToString(), sourceparameters.ToArray());
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"].ToString().Trim()
};
results.Add(itemClass);
DataItem itemDay = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Day",
Value = dr["CumulantDay"].ToString().Trim()
};
results.Add(itemDay);
DataItem itemMonth = new DataItem
{
ID = dr["OrganizationID"].ToString().Trim() + ">" + dr["VariableId"].ToString().Trim() + ">Month",
Value = dr["CumulantMonth"].ToString().Trim()
};
results.Add(itemMonth);
}
return(results);
}
