/// <summary>
/// 获取表的基础数据集
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetBaseData(BaseDataModel model)
{
var dtold = new DataTable();
var tcount = new List<string>();
var cmdName = string.Empty;
tcount = GetTcountNames(model, tcount);
switch (model.Unit)
{
case ChartUnit.unit_hour:
cmdName = "getBaseDataByHour";
break;
case ChartUnit.unit_day:
cmdName = "getBaseDataByDate";
break;
case ChartUnit.unit_month:
cmdName = "getBaseDataByMonth";
break;
default:
cmdName = "getBaseDataByMonth";
break;
}
//var cmd = new DataCommand(cmdName, new SqlCustomDbCommand());
//cmd.SetParameterValue("@countid", model.ObjectId);
//cmd.SetParameterValue("@itemcode", model.ItemCode);
for (var i = 0; i < tcount.Count; i++)
{
var cmd = new DataCommand(cmdName, new SqlCustomDbCommand());
cmd.SetParameterValue("@countid", model.ObjectId);
cmd.SetParameterValue("@itemcode", model.ItemCode);
cmd.ReplaceParameterValue("#TableName#", tcount[i]);
//cmd.SetParameterValue("#TableName#", tcount[i]);
//object param = cmd.GetParameterValue("#TableName#");
//cmd.SetParameterValue("@tname", tcount[i]);
DataTable dts;
if (model.Unit == ChartUnit.unit_hour)
{
cmd.SetParameterValue("@cdate", model.Starttime.ToString("yyyy-MM-dd"));
}
try
{
dts = cmd.ExecuteDataSet().Tables[0];
dtold = i.Equals(0) ? dts.Clone() : dtold;
dtold = UniteDataTable(dtold, dts.Copy());
}
catch (Exception e)
{
//throw new Exception(e.Message);
}
}
return MakerData(dtold, model);
}
/// <summary>
/// 能耗集合
/// </summary>
/// <param name="model">查询的对象封装</param>
/// <returns></returns>
public static BaseResult GetBaseEneryDataList(BaseQueryModel model)
{
BaseResult Res = new BaseResult();
Res.BaseLayerObjectResults = new Dictionary<string, BaseData>();
//Res.DeviceResults = new Dictionary<Model.Device, BaseData>();
bool IsDevice = model.IsDevice == 0 ? false : true;
var datas = new DataTable();
if (model != null && model.ObjectList.Count > 0)
{
foreach (var objects in model.ObjectList)
{
decimal tempCount = 0;
BaseDataModel bmodel = new BaseDataModel();
bmodel.Starttime = model.Starttime;
if (model.Unit == ChartUnit.unit_hour)
{
bmodel.Endtime = (model.Starttime == model.Endtime)
? model.Starttime.AddDays(1).AddHours(-1)
: model.Endtime;
}
else
{
bmodel.Endtime = model.Endtime;
}
bmodel.IsDevice = model.IsDevice;
bmodel.ItemCode = model.ItemCode;
bmodel.ObjectId = objects;
bmodel.Unit = model.Unit;
datas = GetBaseData(bmodel);
BaseData bsDatas = new BaseData();
bsDatas.itemCode = new Model.Itemcode();
var ItemCodeList = new DAL.Itemcode().GetItemcodeList(string.Format(" and ItemCodeNumber='{0}'", model.ItemCode), string.Format(" order by ItemcodeID"));
bsDatas.itemCode = ItemCodeList.Count > 0 ? ItemCodeList[0] : null;
Model.BaseLayerObject ObjectInfo = new Model.BaseLayerObject();
Model.Device DeviceObjectInfo = new Model.Device();
if (!IsDevice)
{
var infoList = new DAL.BaseLayerObject().GetBaseLayerObjectList(string.Format(" and LayerObjectID={0}", objects), string.Format(" order by LayerObjectID"));
ObjectInfo = infoList.Count > 0 ? infoList[0] : null;
bsDatas.Datas = new List<DataItems>();
foreach (DataRow item in datas.Rows)
{
tempCount += decimal.Parse(item["CountValue"].ToString());
bsDatas.Datas.Add(new DataItems()
{
DatePick = item["Starttime"].ToString(),
DataValue = decimal.Parse(item["CountValue"].ToString()),
CoalDataValue = decimal.Parse(item["CountValue"].ToString()) * (decimal)bsDatas.itemCode.ItemCoal,
Co2DataValue = decimal.Parse(item["CountValue"].ToString()) * (decimal)bsDatas.itemCode.ItemCO2,
MoneyDataValue = decimal.Parse(item["CountValue"].ToString()) * (decimal)bsDatas.itemCode.ItemMoney,
DataValueAndDept = item["CountValue"].ToString() + bsDatas.itemCode.Unit
});
}
bsDatas.baseLayerObject = ObjectInfo;
}
else
{
var infoList = new DAL.Device().GetDeviceList(string.Format(" and deviceid={0}", objects), string.Format(" order by DeviceID"));
DeviceObjectInfo = infoList.Count > 0 ? infoList[0] : null;
bsDatas.Datas = new List<DataItems>();
foreach (DataRow item in datas.Rows)
{
tempCount += decimal.Parse(item["CountValue"].ToString());
bsDatas.Datas.Add(new DataItems()
{
DatePick = item["Starttime"].ToString(),
DataValue = decimal.Parse(item["CountValue"].ToString()),
CoalDataValue = decimal.Parse(item["CountValue"].ToString()) *(decimal)bsDatas.itemCode.ItemCoal,
Co2DataValue = decimal.Parse(item["CountValue"].ToString()) * (decimal)bsDatas.itemCode.ItemCO2,
MoneyDataValue = decimal.Parse(item["CountValue"].ToString()) * (decimal)bsDatas.itemCode.ItemMoney,
DataValueAndDept = item["CountValue"].ToString() + bsDatas.itemCode.Unit
});
}
bsDatas.device = DeviceObjectInfo;
}
bsDatas.Total = tempCount;
bsDatas.ConvertDataValueList = new System.Collections.Hashtable();
bsDatas.ConvertDataValueList.Add("ITEMCOAL", decimal.Round(tempCount * (decimal)bsDatas.itemCode.ItemCoal, 4).ToString());
bsDatas.ConvertDataValueList.Add("ITEMCO2", decimal.Round(tempCount * (decimal)bsDatas.itemCode.ItemCO2, 4).ToString());
bsDatas.ConvertDataValueList.Add("ITEMMONEY", decimal.Round(tempCount * (decimal)bsDatas.itemCode.ItemMoney, 4).ToString());
Res.BaseLayerObjectResults.Add(
!IsDevice ? ObjectInfo.LayerObjectID.ToString() : DeviceObjectInfo.DeviceID.ToString(), bsDatas);
}
return Res;
}
return null;
}
private static DataTable MakerData(DataTable dt, BaseDataModel model)
{
var valueList = new StringBuilder();
if (dt != null)
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
switch (model.Unit)
{
case ChartUnit.unit_hour:
valueList.AppendFormat(",{0}", dt.Rows[i]["Value24"].ToString());
break;
case ChartUnit.unit_month:
valueList.AppendFormat(",{0}", dt.Rows[i]["Value30"].ToString());
break;
case ChartUnit.unit_day:
valueList.AppendFormat(",{0}", dt.Rows[i]["Value365"].ToString());
break;
default:
valueList.AppendFormat(",{0}", dt.Rows[i]["Value30"].ToString());
break;
}
}
var listV = valueList.ToString().Length > 0
? valueList.ToString().Substring(1).Split(',')
: null;
if (listV == null) return null;
DataTable dataTable = TableViews.BaseTable.CreateBigBaseDataTable();
Dictionary<string, decimal> YearsDir = new Dictionary<string, decimal>();
var year = model.Endtime.Year - model.Starttime.Year + 1;
for (int y = model.Starttime.Year; y < model.Endtime.Year + 1; y++)
{
YearsDir.Add(y.ToString(), 0);
}
decimal[] YearData = new decimal[year];
if (model.Unit == ChartUnit.unit_year)
{
var count = 0;
foreach (var yd in YearsDir)
{
foreach (var l in listV)
{
if (l != "")
{
if (Convert.ToDateTime(l.Split('_')[0]).Year == int.Parse(yd.Key))
{
YearData[count] = YearData[count] + decimal.Parse(l.Split('_')[1]);
}
}
}
count++;
}
for (int i = 0; i < YearsDir.Count; i++)
{
DataRow dr = dataTable.NewRow();
dr["CountID"] = model.ObjectId;
dr["Starttime"] = model.Starttime.AddYears(i).ToString("yyyy-1-1");
dr["Endtime"] = model.Starttime.AddYears(i + 1).ToString("yyyy-1-1");
dr["CountValue"] = YearData[i];
dataTable.Rows.Add(dr);
}
}
else
{
foreach (string t in listV)
{
if (t != "")
{
//model
switch (model.Unit)
{
case ChartUnit.unit_day:
if (Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Starttime).Days >= 0 &&
Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Endtime).Days <= 0)
{
DataRow dr = dataTable.NewRow();
dr["CountID"] = dt.Rows[0]["CountID"].ToString();
dr["Starttime"] = t.Split('_')[0];
dr["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddDays(1);
dr["CountValue"] = t.Split('_')[1];
dataTable.Rows.Add(dr);
}
break;
case ChartUnit.unit_hour:
if (Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Starttime).Hours >= 0 &&
Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Endtime).Hours <= 0)
{
DataRow dr = dataTable.NewRow();
dr["CountID"] = dt.Rows[0]["CountID"].ToString();
dr["Starttime"] = t.Split('_')[0];
dr["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddHours(1);
dr["CountValue"] = t.Split('_')[1];
dataTable.Rows.Add(dr);
}
break;
case ChartUnit.unit_month:
if (Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Starttime).Days >= 0 &&
Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Endtime).Days <= 0)
{
DataRow dr = dataTable.NewRow();
dr["CountID"] = dt.Rows[0]["CountID"].ToString();
dr["Starttime"] = t.Split('_')[0];
dr["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddMonths(1);
dr["CountValue"] = t.Split('_')[1];
dataTable.Rows.Add(dr);
}
break;
//case ChartUnit.unit_year:
// if (Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Starttime).Days >= 0 &&
// Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Endtime).Days <= 0)
// {
// DataRow dr = dataTable.NewRow();
// dr["CountID"] = dt.Rows[0]["CountID"].ToString();
// dr["Starttime"] = t.Split('_')[0];
// dr["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddMonths(1);
// dr["CountValue"] = t.Split('_')[1];
// dataTable.Rows.Add(dr);
// }
// break;
}
}
}
}
return dataTable;
}
}
return dt;
}
/// <summary>
/// 根据查询的起始时间获取需要取的表的表名集合
/// </summary>
/// <param name="model">查询条件实体类</param>
/// <param name="tcount">返回的表的集合</param>
/// <returns></returns>
private static List<string> GetTcountNames(BaseDataModel model, List<string> tcount)
{
if (model.Unit == ChartUnit.unit_hour)
{
if (model.IsDevice == 0)
{
tcount.Add("TS_DataCenter_Area_Hour_" + model.Starttime.Year.ToString(CultureInfo.InvariantCulture));
}
else
{
tcount.Add("TS_DataCenter_Device_Hour_" + model.Starttime.Year.ToString(CultureInfo.InvariantCulture));
}
}
else if (model.Unit == ChartUnit.unit_day)
{
if (model.Starttime.Year.Equals(model.Endtime.Year))
{
// 同年
if (model.IsDevice == 0)
{
tcount.Add("TS_DataCenter_Area_Day_" + model.Starttime.Year.ToString(CultureInfo.InvariantCulture));
}
else
{
tcount.Add("TS_DataCenter_Device_Day_" + model.Starttime.Year.ToString(CultureInfo.InvariantCulture));
}
}
else
{
DateTime temp = model.Starttime;
while (temp.Year <= model.Endtime.Year)
// while (Convert.ToDateTime(temp.ToString("yyyy-MM-")+model.endtime.Day.ToString()) <= model.endtime)
{
if (model.IsDevice == 0)
{
tcount.Add("TS_DataCenter_Area_Day_" + temp.Year.ToString(CultureInfo.InvariantCulture));
}
else
{
tcount.Add("TS_DataCenter_Device_Day_" + temp.Year.ToString(CultureInfo.InvariantCulture));
}
temp = temp.AddYears(1);
}
}
}
else
{
if (model.Starttime.Year.Equals(model.Endtime.Year))
{
// 同年
if (model.IsDevice == 0)
{
tcount.Add("TS_DataCenter_Area_Month_" + model.Starttime.Year.ToString(CultureInfo.InvariantCulture));
}
else
{
tcount.Add("TS_DataCenter_Device_Month_" + model.Starttime.Year.ToString(CultureInfo.InvariantCulture));
}
}
else
{
DateTime temp = model.Starttime;
while (temp.Year <= model.Endtime.Year)
{
if (model.IsDevice == 0)
{
tcount.Add("TS_DataCenter_Area_Month_" + temp.Year.ToString(CultureInfo.InvariantCulture));
}
else
{
tcount.Add("TS_DataCenter_Device_Month_" + temp.Year.ToString(CultureInfo.InvariantCulture));
}
temp = temp.AddYears(1);
}
}
}
return tcount;
}
private static DataTable MakerData(DataTable dt, BaseDataModel model)
{
var valueList = new StringBuilder();
if (dt != null)
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
switch (model.Unit)
{
case ChartUnit.unit_hour:
valueList.AppendFormat(",{0}", dt.Rows[i]["Value24"].ToString());
break;
case ChartUnit.unit_month:
valueList.AppendFormat(",{0}", dt.Rows[i]["Value30"].ToString());
break;
case ChartUnit.unit_day:
valueList.AppendFormat(",{0}", dt.Rows[i]["Value365"].ToString());
break;
default:
valueList.AppendFormat(",{0}", dt.Rows[i]["Value30"].ToString());
break;
}
}
string strValue = valueList.ToString().Replace(",,", ",");
var listV = strValue.Length > 0
? strValue.Substring(1).Split(',')
: null;
//listV = from m in listV where m != "" OrderBy(m=>m).ToArray();
listV = listV.Where(m => m != "").OrderBy(m => m).ToArray();
// listV OrderBy(m=>m).ToArray();
if (listV == null) return null;
DataTable dataTable = TableViews.BaseTable.CreateBigBaseDataTable();
Dictionary<string, decimal> YearsDir = new Dictionary<string, decimal>();
var year = model.Endtime.Year - model.Starttime.Year + 1;
for (int y = model.Starttime.Year; y < model.Endtime.Year + 1; y++)
{
YearsDir.Add(y.ToString(), 0);
}
decimal[] YearData = new decimal[year];
if (model.Unit == ChartUnit.unit_year)
{
var count = 0;
foreach (var yd in YearsDir)
{
foreach (var l in listV)
{
if (l != "")
{
if (Convert.ToDateTime(l.Split('_')[0]).Year == int.Parse(yd.Key))
{
YearData[count] = YearData[count] + decimal.Parse(l.Split('_')[1]);
}
}
}
count++;
}
for (int i = 0; i < YearsDir.Count; i++)
{
DataRow dr = dataTable.NewRow();
dr["CountID"] = model.ObjectId;
dr["Starttime"] = model.Starttime.AddYears(i).ToString("yyyy-1-1");
dr["Endtime"] = model.Starttime.AddYears(i + 1).ToString("yyyy-1-1");
dr["CountValue"] = YearData[i];
dataTable.Rows.Add(dr);
}
}
else
{
int hourCount = 0;
foreach (string t in listV)
{
if (t != "")
{
#region 组合数据
switch (model.Unit)
{
case ChartUnit.unit_day:
if (Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Starttime).Days >= 0 &&
Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Endtime).Days <= 0)
{
int Count = 0;
if ((Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Starttime).Days != 0) &&(hourCount > 0))
{
TimeSpan ts = Convert.ToDateTime(listV[hourCount].Split('_')[0]) - Convert.ToDateTime(listV[hourCount - 1].Split('_')[0]);
Count = ts.Days;
}
for (int row = 1; row < Count; row++)
{
DataRow drAdd = dataTable.NewRow();
drAdd["CountID"] = dt.Rows[0]["CountID"].ToString();
drAdd["Starttime"] = Convert.ToDateTime(t.Split('_')[0]).AddDays(row - 1);
drAdd["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddDays(row);
drAdd["CountValue"] = 0;
dataTable.Rows.Add(drAdd);
}
DataRow dr = dataTable.NewRow();
dr["CountID"] = dt.Rows[0]["CountID"].ToString();
dr["Starttime"] = t.Split('_')[0];
dr["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddDays(1);
dr["CountValue"] = t.Split('_')[1];
dataTable.Rows.Add(dr);
}
break;
case ChartUnit.unit_hour:
if (Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Starttime).Hours >= 0 &&
Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Endtime).Hours <= 0)
{
int Count = 0;
if (hourCount > 0)
{
Count = Convert.ToDateTime(listV[hourCount].Split('_')[0]).Hour - Convert.ToDateTime(listV[hourCount - 1].Split('_')[0]).Hour;
}
for (int row = 1; row < Count; row++)
{
DataRow drAdd = dataTable.NewRow();
drAdd["CountID"] = dt.Rows[0]["CountID"].ToString();
drAdd["Starttime"] = Convert.ToDateTime(t.Split('_')[0]).AddHours(row - 1);
drAdd["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddHours(row);
drAdd["CountValue"] = 0;
dataTable.Rows.Add(drAdd);
}
DataRow dr = dataTable.NewRow();
dr["CountID"] = dt.Rows[0]["CountID"].ToString();
dr["Starttime"] = t.Split('_')[0];
dr["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddHours(1);
dr["CountValue"] = t.Split('_')[1];
dataTable.Rows.Add(dr);
}
break;
case ChartUnit.unit_month:
if (Convert.ToDateTime(t.Split('_')[0]).Subtract(DateTime.Parse(model.Starttime.ToString("yyyy-MM-01"))).Days >= 0 &&
Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Endtime).Days <= 0)
{
int Count = 0;
if (hourCount > 0)
{
int yearCha = Convert.ToDateTime(listV[hourCount].Split('_')[0]).Year - Convert.ToDateTime(listV[hourCount - 1].Split('_')[0]).Year;
int monthCha = Convert.ToDateTime(listV[hourCount].Split('_')[0]).Month - Convert.ToDateTime(listV[hourCount - 1].Split('_')[0]).Month;
Count = yearCha * 12 + monthCha;
}
for (int row = 1; row < Count; row++)
{
DataRow drAdd = dataTable.NewRow();
drAdd["CountID"] = dt.Rows[0]["CountID"].ToString();
drAdd["Starttime"] = Convert.ToDateTime(t.Split('_')[0]).AddMonths(row - 1);
drAdd["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddMonths(row);
drAdd["CountValue"] = 0;
dataTable.Rows.Add(drAdd);
}
DataRow dr = dataTable.NewRow();
dr["CountID"] = dt.Rows[0]["CountID"].ToString();
dr["Starttime"] = t.Split('_')[0];
dr["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddMonths(1);
dr["CountValue"] = t.Split('_')[1];
dataTable.Rows.Add(dr);
}
break;
//case ChartUnit.unit_year:
// if (Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Starttime).Days >= 0 &&
// Convert.ToDateTime(t.Split('_')[0]).Subtract(model.Endtime).Days <= 0)
// {
// DataRow dr = dataTable.NewRow();
// dr["CountID"] = dt.Rows[0]["CountID"].ToString();
// dr["Starttime"] = t.Split('_')[0];
// dr["Endtime"] = Convert.ToDateTime(t.Split('_')[0]).AddMonths(1);
// dr["CountValue"] = t.Split('_')[1];
// dataTable.Rows.Add(dr);
// }
// break;
}
#endregion
}
hourCount++;
}
}
return dataTable;
}
}
return dt;
}
