public DataTable GetItemcodeDataAll(ReportQueryModelNew model, DataTable dtItemCode)
{
int objectid;
float total1 = 0;
float total2 = 0;
float total3 = 0;
float total4 = 0;
// 电
float decITEMCOAL1 = 0;
// 水
float decITEMCOAL2 = 0;
// 气
float decITEMCOAL3 = 0;
// 煤
float decITEMCOAL4 = 0;
if (dtItemCode.Rows[0]["ITEMCOAL"] != DBNull.Value)
{
decITEMCOAL1 = float.Parse(dtItemCode.Rows[0]["ITEMCOAL"].ToString());
}
if (dtItemCode.Rows[1]["ITEMCOAL"] != DBNull.Value)
{
decITEMCOAL2 = float.Parse(dtItemCode.Rows[1]["ITEMCOAL"].ToString());
}
if (dtItemCode.Rows[2]["ITEMCOAL"] != DBNull.Value)
{
decITEMCOAL3 = float.Parse(dtItemCode.Rows[2]["ITEMCOAL"].ToString());
}
if (dtItemCode.Rows[3]["ITEMCOAL"] != DBNull.Value)
{
decITEMCOAL4 = float.Parse(dtItemCode.Rows[3]["ITEMCOAL"].ToString());
}
float totalzhm = 0;
DataTable dt = CreateTable.CreateBaseDataTableAll();
//if (!new CreateTable().ObjectIsConfigAll(model, out objectid))
//{
// return null;
//}
try
{
string objectname = Getobjectname(model.objectid);
string itemcodename = Getitemcodename(model.itemcode);
////List<int> lstCountId = new List<int>();
////for (int jCount = 0; jCount < dtItemCode.Rows.Count; jCount++)
////{
//// int countId = GetObjectCountId(model.objectid, model.objecttype, CreateTable.ConvertBaseCountType(model.timeunit), int.Parse(dtItemCode.Rows[jCount]["ItemCodeId"].ToString()));
//// lstCountId.Add(countId);
////}
BaseDataModelNew DataModel = new BaseDataModelNew();
DataModel.Starttime = model.startime;
DataModel.Endtime = model.endtime;
DataModel.ObjectId = int.Parse(model.objectid);
DataModel.Unit = CreateTable.ConvertBaseCountType(model.timeunit).ToString();
#region 处理加工
DataTable[] dtdata = DataCommon.GetBaseDataAll(DataModel, dtItemCode);
#endregion
if (model.timeunit != ReportStyleNew.YearStyle)
{
#region 日月
for (DateTime i = model.startime; i <= model.endtime; i = (model.timeunit == ReportStyleNew.DayStyle) ? i.AddHours(1) : i.AddDays(1))
{
//if (model.timeunit == ReportStyleNew.DayStyle && i.Hour==0)
//{
// continue;
//}
DataRow dr = dt.NewRow();
dr[1] = CreateTable.ReportFormatDate(i.ToString(), model.timeunit);
dr[2] = model.objectid;
dr[3] = objectname;
dr[4] = i;
dr[5] = i.AddDays(1);
//dr[6] = itemcodename;
//dr[7] = 0;
dr[6] = 0;
dr[7] = 0;
dr[8] = 0;
dr[9] = 0;
dr[10] = 0;
DataTable dt1 = dtdata[0];
DataTable dt2 = dtdata[1];
DataTable dt3 = dtdata[2];
DataTable dt4 = dtdata[3];
#region 电
if (dt1.Rows.Count > 0)
{
for (int j = 0; j < dt1.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dt1.Rows[j]["Starttime"]).Hour == i.Hour)
{
//dr[1] = CreateTable.ReportFormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[6] = Math.Round(float.Parse(dt1.Rows[j]["CountValue"].ToString()), 2);
total1 += float.Parse(dr[6].ToString());
}
}
else
{
if (Convert.ToDateTime(dt1.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.ReportFormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[6] = Math.Round(float.Parse(dt1.Rows[j]["CountValue"].ToString()), 2);
total1 += float.Parse(dr[6].ToString());
break;
}
}
}
}
#endregion
#region 水
if (dt2.Rows.Count > 0)
{
for (int j = 0; j < dt2.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dt2.Rows[j]["Starttime"]).Hour == i.Hour)
{
dr[7] = Math.Round(float.Parse(dt2.Rows[j]["CountValue"].ToString()), 2);
total2 += float.Parse(dr[7].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dt2.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.ReportFormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[7] = Math.Round(float.Parse(dt2.Rows[j]["CountValue"].ToString()), 2);
total2 += float.Parse(dr[7].ToString());
break;
}
}
}
}
#endregion
#region 气
if (dt3.Rows.Count > 0)
{
for (int j = 0; j < dt3.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dt3.Rows[j]["Starttime"]).Hour == i.Hour)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[8] = Math.Round(float.Parse(dt3.Rows[j]["CountValue"].ToString()), 2);
total3 += float.Parse(dr[8].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dt3.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[8] = Math.Round(float.Parse(dt3.Rows[j]["CountValue"].ToString()), 2);
total3 += float.Parse(dr[8].ToString());
break;
}
}
}
}
#endregion
#region 供暖
if (dt4.Rows.Count > 0)
{
for (int j = 0; j < dt4.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dt4.Rows[j]["Starttime"]).Hour == i.Hour)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[9] = Math.Round(float.Parse(dt4.Rows[j]["CountValue"].ToString()), 2);
total4 += float.Parse(dr[9].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dt4.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[9] = Math.Round(float.Parse(dt4.Rows[j]["CountValue"].ToString()), 2);
total4 += float.Parse(dr[9].ToString());
break;
}
}
}
}
dr[10] = float.Parse(dr[6].ToString()) * decITEMCOAL1 +
float.Parse(dr[7].ToString()) * decITEMCOAL2 +
float.Parse(dr[8].ToString()) * decITEMCOAL3 +
float.Parse(dr[9].ToString()) * decITEMCOAL4;
#endregion
dt.Rows.Add(dr);
}
#endregion
}
else
{
#region 年
for (DateTime i = model.startime; i <= model.endtime; i = i.AddMonths(1))
{
//if (model.timeunit == ReportStyleNew.DayStyle && i.Hour==0)
//{
// continue;
//}
DataRow dr = dt.NewRow();
dr[1] = CreateTable.ReportFormatDate(i.ToString(), model.timeunit);
dr[2] = model.objectid;
dr[3] = objectname;
dr[4] = i;
dr[5] = i.AddDays(1);
//dr[6] = itemcodename;
//dr[7] = 0;
dr[6] = 0;
dr[7] = 0;
dr[8] = 0;
dr[9] = 0;
DataTable dt1 = dtdata[0];
DataTable dt2 = dtdata[1];
DataTable dt3 = dtdata[2];
DataTable dt4 = dtdata[3];
#region 电
if (dt1.Rows.Count > 0)
{
for (int j = 0; j < dt1.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dt1.Rows[j]["ENDTIME"]).Hour == i.Hour)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[6] = Math.Round(float.Parse(dt1.Rows[j]["CountValue"].ToString()), 2);
total1 += float.Parse(dr[6].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dt1.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[6] = Math.Round(float.Parse(dt1.Rows[j]["CountValue"].ToString()), 2);
total1 += float.Parse(dr[6].ToString());
break;
}
}
}
}
#endregion
#region 水
if (dt2.Rows.Count > 0)
{
for (int j = 0; j < dt2.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dt2.Rows[j]["ENDTIME"]).Hour == i.Hour)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[7] = Math.Round(float.Parse(dt2.Rows[j]["CountValue"].ToString()), 2);
total2 += float.Parse(dr[7].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dt2.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[7] = Math.Round(float.Parse(dt2.Rows[j]["CountValue"].ToString()), 2);
total2 += float.Parse(dr[7].ToString());
break;
}
}
}
}
#endregion
#region 气
if (dt3.Rows.Count > 0)
{
for (int j = 0; j < dt3.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dt3.Rows[j]["ENDTIME"]).Hour == i.Hour)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[8] = Math.Round(float.Parse(dt3.Rows[j]["CountValue"].ToString()), 2);
total3 += float.Parse(dr[8].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dt3.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[8] = Math.Round(float.Parse(dt3.Rows[j]["CountValue"].ToString()), 2);
total3 += float.Parse(dr[8].ToString());
break;
}
}
}
}
#endregion
#region 供暖
if (dt4.Rows.Count > 0)
{
for (int j = 0; j < dt4.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dt4.Rows[j]["ENDTIME"]).Hour == i.Hour)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[9] = Math.Round(float.Parse(dt4.Rows[j]["CountValue"].ToString()), 2);
total4 += float.Parse(dr[9].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dt4.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[9] = Math.Round(float.Parse(dt4.Rows[j]["CountValue"].ToString()), 2);
total4 += float.Parse(dr[9].ToString());
break;
}
}
}
}
#endregion
dr[10] = float.Parse(dr[6].ToString()) * decITEMCOAL1 +
float.Parse(dr[7].ToString()) * decITEMCOAL2 +
float.Parse(dr[8].ToString()) * decITEMCOAL3 +
float.Parse(dr[9].ToString()) * decITEMCOAL4;
dt.Rows.Add(dr);
}
#endregion
}
#region
DataRow totaldr = dt.NewRow();
totaldr[1] = CreateTable.ReportFormatDate(model.startime.ToString(), model.timeunit) + "-" +
CreateTable.ReportFormatDate(model.endtime.ToString(),
model.timeunit);
totaldr[2] = model.objectid;
totaldr[3] = objectname;
totaldr[4] = "-";
totaldr[5] = "-";
//totaldr[6] = itemcodename;
totaldr[6] = Math.Round(total1, 2).ToString();
totaldr[7] = Math.Round(total2, 2).ToString();
totaldr[8] = Math.Round(total3, 2).ToString();
totaldr[9] = Math.Round(total4, 2).ToString();
// 加转换煤
totaldr[10] = Math.Round((total1 * decITEMCOAL1 + total2 * decITEMCOAL2 + total3 * decITEMCOAL3 + total4 * decITEMCOAL4), 2).ToString();
dt.Rows.Add(totaldr);
#endregion
return dt;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
//return null;
}
}
public DataTable GetItemcodeData(ReportQueryModelNew model)
{
int objectid;
float total = 0;
DataTable dt = CreateTable.CreateBaseDataTable();
//if (!new CreateTable().ObjectIsConfig(model, out objectid))
//{
// return null;
//}
try
{
string objectname = Getobjectname(model.objectid);
string itemcodename = Getitemcodename(model.itemcode);
BaseDataModelNew DataModel = new BaseDataModelNew();
DataModel.Starttime = model.startime;
DataModel.Endtime = model.endtime;
DataModel.ObjectId = int.Parse(model.objectid);
DataModel.ItemCode = model.itemcode;
DataModel.Unit = CreateTable.ConvertBaseCountType(model.timeunit).ToString();
// Datamodel.timeunit = CreateTable.ConvertBaseCountType(model.timeunit).ToString(); //
#region 处理加工
DataTable dtdata = DataCommon.GetBaseDataItem(DataModel);
#region 非年
if (model.timeunit != ReportStyleNew.YearStyle) // 非年
{
//if (model.timeunit == ReportStyleNew.DayStyle)
//{
// model.startime = model.startime.AddHours(1);
//}
for (DateTime i = model.startime; i <= model.endtime; i = (model.timeunit == ReportStyleNew.DayStyle) ? i.AddHours(1) : i.AddDays(1))
{
//if (model.timeunit == ReportStyleNew.DayStyle && i.Hour==0)
//{
// continue;
//}
DataRow dr = dt.NewRow();
dr[1] = CreateTable.ReportFormatDate(i.ToString(), model.timeunit);
dr[2] = model.objectid;
dr[3] = objectname;
dr[4] = i;
dr[5] = i.AddDays(1);
dr[6] = itemcodename;
dr[7] = 0;
if (dtdata.Rows.Count > 0)
{
for (int j = 0; j < dtdata.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dtdata.Rows[j]["Starttime"]).Hour == i.Hour)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[7] = Math.Round(float.Parse(dtdata.Rows[j]["CountValue"].ToString()), 2);
total += float.Parse(dr[7].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dtdata.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.FormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[7] = Math.Round(float.Parse(dtdata.Rows[j]["CountValue"].ToString()), 2);
total += float.Parse(dr[7].ToString());
break;
}
}
}
}
dt.Rows.Add(dr);
}
}
#endregion
#region 年
else // 年
{
for (DateTime i = model.startime; i <= model.endtime; i = i.AddMonths(1))
{
DataRow dr = dt.NewRow();
dr[1] = CreateTable.ReportFormatDate(i.ToString(), model.timeunit);
dr[2] = model.objectid;
dr[3] = objectname;
dr[4] = i;
dr[5] = i.AddDays(1);
dr[6] = itemcodename;
dr[7] = 0;
if (dtdata.Rows.Count > 0)
{
for (int j = 0; j < dtdata.Rows.Count; j++)
{
if (model.timeunit == ReportStyleNew.DayStyle)
{
if (Convert.ToDateTime(dtdata.Rows[j]["Starttime"]).Hour == i.Hour)
{
//dr[1] = CreateTable.ReportFormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[7] = Math.Round(float.Parse(dtdata.Rows[j]["CountValue"].ToString()), 2);
total += float.Parse(dr[7].ToString());
break;
}
}
else
{
if (Convert.ToDateTime(dtdata.Rows[j]["Starttime"]).Date == i.Date)
{
//dr[1] = CreateTable.ReportFormatDate(dtdata.Rows[j]["Starttime"].ToString(), model.timeunit);
dr[7] = Math.Round(float.Parse(dtdata.Rows[j]["CountValue"].ToString()), 2);
total += float.Parse(dr[7].ToString());
break;
}
}
}
}
dt.Rows.Add(dr);
}
}
#endregion
DataRow totaldr = dt.NewRow();
totaldr[1] = CreateTable.ReportFormatDate(model.startime.ToString(), model.timeunit) + "-" +
CreateTable.ReportFormatDate(model.endtime.ToString(),
model.timeunit);
totaldr[2] = model.objectid;
totaldr[3] = objectname;
totaldr[4] = "-";
totaldr[5] = "-";
totaldr[6] = itemcodename;
totaldr[7] = Math.Round(total, 2).ToString();
dt.Rows.Add(totaldr);
#endregion
return dt;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
//return null;
}
}
/// <summary>
/// 获取表的基础数据集
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable[] GetBaseDataAll(BaseDataModelNew model, DataTable dtItemCode)
{
DataTable[] dtList = new DataTable[4];
try
{
for (int iCount = 0; iCount < dtItemCode.Rows.Count; iCount++)
{
DataTable dtold = new DataTable();
List<string> Tcount = new List<string>();
Tcount = GetTcountNames(model, Tcount);
for (int i = 0; i < Tcount.Count; i++)
{
string basesql = "";
string sql = "";
switch (model.Unit)
{
case "1":
basesql = " select top 1 CountID,Value24 as value,ItemCode from \"{0}\" where CountID={1} and CONVERT(varchar(100), CDate, 23)='{2}' and ItemCode='{3}'";
break;
case "2":
basesql = " select top 1 CountID,Value365 as value,ItemCode from \"{0}\" where CountID={1} and ItemCode='{3}'";
sql = string.Format(basesql, new object[] { Tcount[i], model.ObjectId, model.Starttime.ToString("yyyy-MM-dd"), dtItemCode.Rows[iCount]["ItemCodeId"].ToString() });
break;
case "3":
basesql = " select top 1 CountID,Value30 as value,ItemCode from \"{0}\" where CountID={1} and ItemCode='{3}'";
break;
}
sql = string.Format(basesql, new object[] { Tcount[i], model.ObjectId, model.Starttime.ToString("yyyy-MM-dd"), dtItemCode.Rows[iCount]["itemcodenumber"].ToString() });
DataTable dts = SqlHelper.Query(sql).Tables[0];
DataTable dtNew = GetNewDataByDts(model, dts);
dtold = i.Equals(0) ? dtNew.Clone() : dtold;
dtold = UniteDataTable(dtold, dtNew.Copy());
dtList[iCount] = dtold;
}
}
}
catch (Exception ex)
{
if (ex.ToString().IndexOf("对象名") > -1)
{
throw new Exception("缺少对应的数据表！");
}
else if (ex.ToString().IndexOf("列名") > -1)
{
throw new Exception("缺少对应的数据列！");
}
else
{
throw new Exception("数据表发生异常！");
}
}
return dtList;
}
/// <summary>
/// 根据查询的起始时间获取需要取的表的表名集合
/// </summary>
/// <param name="model">查询条件实体类</param>
/// <param name="Tcount">返回的表的集合</param>
/// <returns></returns>
private static List<string> GetTcountNames(BaseDataModelNew model, List<string> Tcount)
{
if (model.Unit == "1") // 小时
{
//取的是小时数据，不存在跨天
//Tcount.Add("TS_DataCenter_Area_Hour_" + model.Starttime.Year.ToString());
if (model.ObjectType == 0)
{
Tcount.Add("TS_DataCenter_Area_Hour_" + model.Starttime.Year.ToString());
}
else
{
Tcount.Add("TS_DataCenter_Device_Hour_" + model.Starttime.Year.ToString());
}
}
else if (model.Unit == "3") // 年
{
//Tcount.Add("TS_DataCenter_Area_Month_" + model.Starttime.Year.ToString());
//// 夸年。
//if (model.Starttime.Year < model.Endtime.Year)
//{
// Tcount.Add("TS_DataCenter_Area_Month_" + model.Starttime.Year.ToString());
//}
//
if (model.Starttime.Year.Equals(model.Endtime.Year))
{
// 同年
if (model.ObjectType == 0)
{
Tcount.Add("TS_DataCenter_Area_Month_" + model.Starttime.Year.ToString());
}
else
{
Tcount.Add("TS_DataCenter_Device_Month_" + model.Starttime.ToString());
}
}
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.ObjectType == 0)
{
Tcount.Add("TS_DataCenter_Area_Month_" + temp.Year.ToString());
}
else
{
Tcount.Add("TS_DataCenter_Device_Month_" + temp.Year.ToString());
}
temp = temp.AddYears(1);
}
}
}
else
{
//如果是取按天的数据,存在跨年不跨年
//if (model.Starttime.Year.Equals(model.Endtime.Year) && model.Starttime.Month.Equals(model.Endtime.Month))
//{
// //同月
// Tcount.Add("TS_COUNT_DAY_" + model.Starttime.Year.ToString());
//}
if (model.Starttime.Year.Equals(model.Endtime.Year))
{
if (model.ObjectType == 0)
{
//同月
Tcount.Add("TS_DataCenter_Area_Day_" + model.Starttime.Year.ToString());
}
else
{
//同月
Tcount.Add("TS_DataCenter_Device_Day_" + model.Starttime.Year.ToString());
}
}
else
{
//跨月
//DateTime temp = model.Starttime;
for (int i = int.Parse(model.Starttime.Year.ToString()), j = int.Parse(model.Endtime.Year.ToString()); i <= j; i++)
{
Tcount.Add("TS_DataCenter_Area_Day_" + i);
}
//DateTime temp = model.Starttime;
//while ((temp.Year * 12 + temp.Month) <= model.Endtime.Year * 12 + model.Endtime.Month)
//// while (Convert.ToDateTime(temp.ToString("yyyy-MM-")+model.endtime.Day.ToString()) <= model.endtime)
//{
// Tcount.Add("TS_COUNT_DAY_" + temp.Year.ToString() + (temp.Month > 9 ? temp.Month.ToString() : "0" + temp.Month.ToString()));
// temp = temp.AddMonths(1);
//}
}
}
//if (model.Starttime.Year.Equals(model.Endtime.Year) && model.Starttime.Month.Equals(model.Endtime.Month))
//{
// // 同月
// //Tcount.Add("TS_COUNT_" + model.Starttime.Year.ToString() + (model.Starttime.Month > 9 ? model.Starttime.Month.ToString() : "0" + model.Starttime.Month.ToString()));
//}
//else
//{
// DateTime temp = model.Starttime;
// while ((temp.Year * 12 + temp.Month) <= model.Endtime.Year * 12 + model.Endtime.Month)
// // while (Convert.ToDateTime(temp.ToString("yyyy-MM-")+model.endtime.Day.ToString()) <= model.endtime)
// {
// Tcount.Add("TS_COUNT_" + temp.Year.ToString() + (temp.Month > 9 ? temp.Month.ToString() : "0" + temp.Month.ToString()));
// temp = temp.AddMonths(1);
// }
//}
return Tcount;
}
/// <summary>
/// 根据条件获取返回的实际DataTable
/// </summary>
/// <param name="model">BaseDataModelNew</param>
/// <param name="strValue">值</param>
/// <returns></returns>
private static DataTable GetNewDataByDts(BaseDataModelNew model, DataTable dts)
{
DataTable dtNew = CreateDateValueTable();
// 有数据。
if (dts.Rows.Count > 0)
{
string strValue = dts.Rows[0]["Value"].ToString();
string[] strList = strValue.Split(',');
for (int strCount = 0; strCount < strList.Length; strCount++)
{
string[] strItem = strList[strCount].Split('_');
if (strItem.Length > 1)
{
#region ss
DataRow drItem = dtNew.NewRow();
switch (model.Unit)
{
case "1": // 小时
if ((DateTime.Parse(strItem[0]) >= model.Starttime) && (DateTime.Parse(strItem[0]) <= model.Endtime))
{
drItem["Starttime"] = DateTime.Parse(strItem[0]);
drItem["Endtime"] = DateTime.Parse(strItem[0]).AddHours(1);
drItem["CountValue"] = Decimal.Parse(strItem[1]);
dtNew.Rows.Add(drItem);
}
break;
case "2": // 天
if ((DateTime.Parse(strItem[0]) >= model.Starttime) && (DateTime.Parse(strItem[0]) <= model.Endtime))
{
drItem["Starttime"] = DateTime.Parse(strItem[0]);
drItem["Endtime"] = DateTime.Parse(strItem[0]).AddDays(1);
drItem["CountValue"] = Decimal.Parse(strItem[1]);
dtNew.Rows.Add(drItem);
}
break;
case "3": // 年
if ((DateTime.Parse(strItem[0]) >= model.Starttime) && (DateTime.Parse(strItem[0]) <= model.Endtime))
{
drItem["Starttime"] = DateTime.Parse(strItem[0]);
drItem["Endtime"] = DateTime.Parse(strItem[0]).AddMonths(1);
drItem["CountValue"] = Decimal.Parse(strItem[1]);
dtNew.Rows.Add(drItem);
}
break;
default:
break;
}
#endregion
}
}
}
return dtNew;
}
/// <summary>
/// 获取表的基础数据集
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetBaseDataItem(BaseDataModelNew model)
{
DataTable dtold = new DataTable();
try
{
List<string> Tcount = new List<string>();
Tcount = GetTcountNames(model, Tcount);
for (int i = 0; i < Tcount.Count; i++)
{
string basesql = "";
string sql = "";
switch (model.Unit)
{
case "1":
basesql = " select top 1 CountID,Value24 as value,ItemCode from \"{0}\" where CountID='{1}' and CONVERT(varchar(100), CDate, 23)='{2}' and ItemCode='{3}'";
break;
case "2":
basesql = " select top 1 CountID,Value365 as value,ItemCode from \"{0}\" where CountID={1} and ItemCode='{3}'";
sql = string.Format(basesql, new object[] { Tcount[i], model.ObjectId, model.Starttime.ToString("yyyy-MM-dd"), model.ItemCode });
break;
case "3":
basesql = " select top 1 CountID,Value30 as value,ItemCode from \"{0}\" where CountID={1} and ItemCode='{3}'";
break;
default:
basesql = " select top 1 CountID,Value24 as value,ItemCode from \"{0}\" where CountID='{1}' and CONVERT(varchar(100), CDate, 23)='{2}' and ItemCode='{3}'";
break;
}
// sql = string.Format(basesql, new object[] { Tcount[i], model.Starttime, model.Endtime, model.ItemCode });
sql = string.Format(basesql, new object[] { Tcount[i], model.ObjectId, model.Starttime.ToString("yyyy-MM-dd"), model.ItemCode });
var cmd = new DataCommand("GetBaseDataItem", new SqlCustomDbCommand());
cmd.ReplaceParameterValue("#SQLSTR#", sql);
//DataTable dts = SqlHelper.Query(sql).Tables[0];
DataTable dts = cmd.ExecuteDataSet().Tables[0];
DataTable dtNew = GetNewDataByDts(model, dts);
dtold = i.Equals(0) ? dtNew.Clone() : dtold;
dtold = UniteDataTable(dtold, dtNew.Copy());
}
return dtold;
}
catch (Exception ex)
{
if (ex.ToString().IndexOf("对象名") > -1)
{
throw new Exception("缺少对应的数据表！");
}
else if (ex.ToString().IndexOf("列名") > -1)
{
throw new Exception("缺少对应的数据列！");
}
else
{
throw new Exception("数据表发生异常！");
}
}
}