/// <summary>
/// 本月用电信息
/// </summary>
/// <param name="module_id"></param>
/// <returns></returns>
public DataTable GetMonthUseVal(int module_id)
{
DataTable dtSource = dal.GetMonthUseVal(module_id);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
#region 获取存储数据
DateTime fm = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
DateTime to = fm.AddMonths(1).AddDays(-1);
decimal allUseVal = 0, allUseAmt = 0;
//
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), fm, to, "month", splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
decimal price = CommFunc.ConvertDBNullToDecimal(curDr["Price"]);
scale = scale == 0 ? 2 : scale;
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
decimal useAmt = Math.Round(useVal * price, 2, MidpointRounding.AwayFromZero);
allUseVal = allUseVal + useVal;
allUseAmt = allUseAmt + useAmt;
}
#endregion
DataTable dtRst = new DataTable();
dtRst.Columns.Add("UseVal", typeof(System.Decimal));
dtRst.Columns.Add("UseAmt", typeof(System.Decimal));
dtRst.Rows.Add(new object[] { allUseVal, allUseAmt });
return(dtRst);
}
/// <summary>
/// 获取回路的实时曲线数据
/// </summary>
/// <param name="co_id">回路ID号</param>
/// <returns></returns>
public object GetRealChart(int co_id)
{
DataTable dtSource = dal.GetRealChart(co_id, "");
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
string moduleName = "";
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
moduleName = CommFunc.ConvertDBNullToString(dr["ModuleName"]);
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
DateTime today2 = DateTime.Now.AddHours(-1); DateTime today1 = new DateTime(today2.Year, today2.Month, today2.Day);
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), today1, today2, "hour", splitTyQuery.ToString());
List <decimal> todayList = new List <decimal>();
int nn = today2.Hour;
while (nn-- >= 0)
{
todayList.Add(0);
}
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
todayList[tagTime.Hour] = CommFunc.ConvertDBNullToDecimal(todayList[tagTime.Hour]) + useVal;
}
return(new { moduleName = moduleName, list = todayList });
}
/// <summary>
/// 获取全部回路当天日数据
/// </summary>
private void GetTodayData()
{
if (DT_SOURCE == null)
{
GetModuleData();
}
DateTime start = DateTime.Today;
DateTime end = DateTime.Today;
DT_USE_TODAY = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), start, end, "day", splitTyQuery.ToString());
}
/// <summary>
/// 获取全部回路小时数据
/// </summary>
private void GetHourData(DateTime date)
{
if (DT_SOURCE == null)
{
GetModuleData();
}
DateTime today1, today2;
today1 = date;
today2 = today1.AddHours(71);
DT_USE_HOUR = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), today1, today2, "hour", splitTyQuery.ToString());
}
private void GetYdModuleCollectValData(string periods, string splitMdQuery, DateTime start, DateTime end, ref DataTable dtRst, ref DataTable dtSource)
{
DataTable dtUse = null;
if (periods.ToLower().Equals("FirstVal".ToLower()))
{
dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery, start, start, "day");
}
else
{
dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery, end, end, "day");
}
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
scale = scale == 0 ? 2 : scale;
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
DateTime tagtime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]) * multiply;
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]) * multiply;
firstVal = Math.Round(firstVal, scale, MidpointRounding.AwayFromZero);
lastVal = Math.Round(lastVal, scale, MidpointRounding.AwayFromZero);
//
DataRow addDr = dtRst.Rows.Find(new object[] { dr["Module_id"], dr["ModuleAddr"] });
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["Module_id"] = dr["Module_id"];
addDr["ModuleAddr"] = dr["ModuleAddr"];
addDr["Start"] = start;
addDr["End"] = end;
addDr["ModuleName"] = curDr["ModuleName"];
addDr["RoomName"] = curDr["CoName"];
addDr["StrcName"] = curDr["CoStrcName"];
dtRst.Rows.Add(addDr);
}
if (periods.ToLower().Equals("FirstVal".ToLower()))
{
addDr["FirstVal"] = firstVal;
}
else
{
addDr["LastVal"] = lastVal;
}
}
}
public List <YdRepHisVModel> GetYdRepHisList(DateTime dtFirst, DateTime dtLast, string CoStrcName, string CoName, int IsMultiply, params string[] funTypeParams)
{
List <YdRepHisVModel> list = new List <YdRepHisVModel>();
var dtModules = dal.GetModuleListByOrg(CoStrcName, CoName, funTypeParams);
var modulesEnum = from s1 in dtModules.AsEnumerable()
select new
{
Module_id = CommFunc.ConvertDBNullToInt32(s1["Module_id"]),
ModuleName = CommFunc.ConvertDBNullToString(s1["ModuleName"]),
ModuleAddr = CommFunc.ConvertDBNullToString(s1["ModuleAddr"]),
Co_id = CommFunc.ConvertDBNullToInt32(s1["Co_id"]),
CoStrcName = CommFunc.ConvertDBNullToString(s1["CoStrcName"]),
CoName = CommFunc.ConvertDBNullToString(s1["CoName"]),
Multiply = CommFunc.ConvertDBNullToDecimal(s1["Multiply"]),
FunType = CommFunc.ConvertDBNullToString(s1["FunType"])
};
if (modulesEnum == null || modulesEnum.Count() == 0)
{
return(list);
}
var modulesList = modulesEnum.ToList();
HashSet <int> ds = new HashSet <int>();
for (int i = 0; i < modulesList.Count; i++)
{
ds.Add(modulesList[i].Module_id);
}
var dtSourceFirst = WholeBLL.GetCoreQueryData(this.Ledger, string.Join(",", ds), dtFirst, dtFirst, "day");
var dtSourceLast = WholeBLL.GetCoreQueryData(this.Ledger, string.Join(",", ds), dtLast, dtLast, "day");
var sourceFirst = from a in dtSourceFirst.AsEnumerable()
select new
{
Tagtime = CommFunc.ConvertDBNullToDateTime(a["Tagtime"]),
Co_id = CommFunc.ConvertDBNullToInt32(a["Co_id"]),
Fun_id = CommFunc.ConvertDBNullToInt32(a["Fun_id"]),
Module_id = CommFunc.ConvertDBNullToInt32(a["Module_id"]),
ModuleAddr = CommFunc.ConvertDBNullToString(a["ModuleAddr"]),
FirstVal = CommFunc.ConvertDBNullToDecimal(a["FirstVal"]),
LastVal = CommFunc.ConvertDBNullToDecimal(a["LastVal"])
};
var sourceLast = from a in dtSourceLast.AsEnumerable()
select new
{
Tagtime = CommFunc.ConvertDBNullToDateTime(a["Tagtime"]),
Co_id = CommFunc.ConvertDBNullToInt32(a["Co_id"]),
Fun_id = CommFunc.ConvertDBNullToInt32(a["Fun_id"]),
Module_id = CommFunc.ConvertDBNullToInt32(a["Module_id"]),
ModuleAddr = CommFunc.ConvertDBNullToString(a["ModuleAddr"]),
FirstVal = CommFunc.ConvertDBNullToDecimal(a["FirstVal"]),
LastVal = CommFunc.ConvertDBNullToDecimal(a["LastVal"])
};
var sourceFirstList = sourceFirst.ToList();
var sourceLastList = sourceLast.ToList();
for (int i = 0; i < modulesList.Count; i++)
{
var model = new YdRepHisVModel();
model.ID = i + 1;
model.Co_id = modulesList[i].Co_id;
model.CoName = modulesList[i].CoName;
model.CoStrcName = modulesList[i].CoStrcName;
model.Date = dtFirst.ToString("yyyy-MM-dd") + "~" + dtLast.ToString("yyyy-MM-dd");
model.Multiply = modulesList[i].Multiply.ToString("f2");
model.FirstVal = "NULL";
model.LastVal = "NULL";
model.UseVal = "NULL";
model.ModuleName = modulesList[i].ModuleName;
model.ModuleAddr = modulesList[i].ModuleAddr;
model.Module_id = modulesList[i].Module_id;
var firstObj = sourceFirstList.Where(c => c.Co_id == model.Co_id && c.Module_id == model.Module_id && c.ModuleAddr == model.ModuleAddr).FirstOrDefault();
if (firstObj != null)
{
model.FirstVal = IsMultiply == 1 ? (firstObj.FirstVal * modulesList[i].Multiply).ToString("f2") : firstObj.FirstVal.ToString("f2");
}
var lastObj = sourceLastList.Where(c => c.Co_id == model.Co_id && c.Module_id == model.Module_id && c.ModuleAddr == model.ModuleAddr).FirstOrDefault();
if (lastObj != null)
{
model.LastVal = IsMultiply == 1 ? (lastObj.LastVal * modulesList[i].Multiply).ToString("f2") : lastObj.LastVal.ToString("f2");
}
if (firstObj != null && lastObj != null)
{
model.UseVal = IsMultiply == 1 ? ((lastObj.LastVal - firstObj.FirstVal) * modulesList[i].Multiply).ToString("f2") : (lastObj.LastVal - firstObj.FirstVal).ToString("f2");
}
list.Add(model);
}
return(list);
}
public DataTable GetRptPSWayList(int areaId, DateTime start, DateTime end, string psWay)
{
#region 表结构
DataTable dtRst = new DataTable();
dtRst.Columns.Add("StationId", typeof(System.Int32)); // 机房ID号
dtRst.Columns.Add("PSWay", typeof(System.String)); // 供电方式
dtRst.Columns.Add("StationNo", typeof(System.String)); // 机房编号
dtRst.Columns.Add("Start", typeof(System.DateTime)); //当前开始时间
dtRst.Columns.Add("End", typeof(System.DateTime)); //当前结束时间
dtRst.Columns.Add("StationName", typeof(System.String)); // 机房名称
dtRst.Columns.Add("StrcName", typeof(System.String)); // 所属区域
dtRst.Columns.Add("Address", typeof(System.String)); // 机房地址
dtRst.Columns.Add("PSWayName", typeof(System.String)); // 机房类型名称
dtRst.Columns.Add("UseVal", typeof(System.Decimal)); //使用电值
dtRst.Columns.Add("LastTime", typeof(System.String)); // 最后录入时间
#endregion
#region 填充基础数据
StringBuilder splitMdQuery = new StringBuilder();
DataTable dtSource = dal.GetRptPSWayData(areaId, psWay);
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["StationId"], dtRst.Columns["PSWay"] };
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
foreach (DataRow dr in dtSource.Rows)
{
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitMdQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["Module_id"]), ",")))
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
}
DataRow addDr = dtRst.Rows.Find(new object[] { dr["Parent_id"], dr["PSWay"] });
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["StationId"] = dr["Parent_id"];
addDr["PSWay"] = dr["PSWay"];
addDr["StationNo"] = dr["CoNo"];
addDr["Start"] = start;
addDr["End"] = end;
addDr["StationName"] = dr["CoName"];
addDr["StrcName"] = dr["CoStrcName"];
addDr["Address"] = dr["CustAddr"];
addDr["PSWayName"] = CommFunc.GetEnumDisplay(typeof(Switch), CommFunc.ConvertDBNullToString(dr["PSWay"]));
dtRst.Rows.Add(addDr);
}
}
#endregion
#region 填充采集数据
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), start, end, "day");
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
int stationId = CommFunc.ConvertDBNullToInt32(curDr["Parent_id"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
DateTime tagtime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = (lastVal - firstVal) * multiply;
scale = scale == 0 ? 2 : scale;
useVal = Math.Round(useVal, scale, MidpointRounding.AwayFromZero);
DataRow addDr = dtRst.Rows.Find(new object[] { stationId, curDr["PSWay"] });
if (addDr == null)
{
continue;
}
addDr["UseVal"] = CommFunc.ConvertDBNullToDecimal(addDr["UseVal"]) + useVal;
if (CommFunc.ConvertDBNullToDateTime(addDr["LastTime"]) < CommFunc.ConvertDBNullToDateTime(dr["LastTime"]))
{
addDr["LastTime"] = dr["LastTime"];
}
}
#endregion
return(dtRst);
}
public DataTable GetYdModuleUseValList(int co_id, int meter_id, DateTime time, string dataType)
{
DateTime start = time, end = time;
int cnt = dataType.Equals("hour") ? 24 : dataType.Equals("day") ? cnt = time.AddMonths(1).AddDays(-1).Day : 12;
if (dataType.Equals("hour"))
{
start = new DateTime(time.Year, time.Month, time.Day);
end = new DateTime(time.Year, time.Month, time.Day, 23, 59, 59);
cnt = 24;
}
else if (dataType.Equals("day"))
{
start = new DateTime(time.Year, time.Month, 1);
end = start.AddMonths(1).AddDays(-1);
cnt = end.Day;
}
else
{
start = new DateTime(time.Year, 1, 1);
end = start.AddYears(1).AddDays(-1);
cnt = 12;
}
StringBuilder splitMdQuery = new StringBuilder();
DataTable dtRst = this.GetYdModuleUseValTabSchema();
DataTable dtSource = dal.GetYdModuleListParams(co_id, meter_id);
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["Module_id"], dtRst.Columns["ModuleAddr"] };
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
foreach (DataRow dr in dtSource.Rows)
{
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitMdQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["Module_id"]), ",")))
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
}
}
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), start, end, dataType);
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
scale = scale == 0 ? 2 : scale;
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
DateTime tagtime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = (lastVal - firstVal) * multiply;
useVal = Math.Round(useVal, scale, MidpointRounding.AwayFromZero);
DataRow addDr = dtRst.Rows.Find(new object[] { dr["Module_id"], dr["ModuleAddr"] });
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["Module_id"] = dr["Module_id"];
addDr["ModuleAddr"] = dr["ModuleAddr"];
addDr["Cnt"] = cnt;
addDr["ModuleName"] = curDr["ModuleName"];
addDr["RoomName"] = curDr["CoName"];
addDr["StrcName"] = curDr["CoStrcName"];
dtRst.Rows.Add(addDr);
}
string clsName = "V" + (dataType.Equals("hour") ? tagtime.Hour + 1 : dataType.Equals("day") ? tagtime.Day : tagtime.Month).ToString().PadLeft(2, '0');
addDr[clsName] = CommFunc.ConvertDBNullToDecimal(addDr[clsName]) + useVal;
}
return(dtRst);
}
/// <summary>
/// 获取分项统计
/// </summary>
/// <param name="co_id">支路ID号</param>
/// <param name="time">时间</param>
/// <param name="dataType">类型 日=day月=month年year</param>
/// <returns></returns>
public object GetEnergyUseVal(int co_id, DateTime time, string dataType, string moduleName)
{
DataTable dtRst = this.GetEnergyUseValTabSchema();
int cnt = 24;
DateTime fm = time, to = time;
if (dataType.ToLower().Equals("day"))
{
fm = new DateTime(time.Year, time.Month, time.Day);
to = new DateTime(time.Year, time.Month, time.Day, 23, 59, 0);
cnt = 24;
dataType = "hour";
}
else if (dataType.ToLower().Equals("month"))
{
fm = new DateTime(time.Year, time.Month, 1);
to = fm.AddMonths(1).AddDays(-1);
cnt = to.Day;
dataType = "day";
}
else if (dataType.ToLower().Equals("year"))
{
fm = new DateTime(time.Year, 1, 1);
to = new DateTime(time.Year, 12, 1);
cnt = 12;
dataType = "month";
}
else
{
throw new Exception("查询类型错误");
}
DataTable dtSource = dal.GetEnergyUseVal(co_id, moduleName);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["Module_id"], dtRst.Columns["Fun_id"] };
//
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
DataRow addDr = dtRst.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["Module_id"] = dr["Module_id"];
addDr["Fun_id"] = dr["Fun_id"];
addDr["Cnt"] = cnt;
addDr["ModuleName"] = dr["ModuleName"];
dtRst.Rows.Add(addDr);
}
}
#region 获取存储数据
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), fm, to, dataType, splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
DataRow addDr = dtRst.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (addDr == null)
{
continue;
}
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
if (dataType.Equals("hour"))
{
addDr["V" + (tagTime.Hour + 1).ToString().PadLeft(2, '0')] = CommFunc.ConvertDBNullToDecimal(addDr["V" + (tagTime.Hour + 1).ToString().PadLeft(2, '0')]) + useVal;
}
else if (dataType.Equals("day"))
{
addDr["V" + tagTime.Day.ToString().PadLeft(2, '0')] = CommFunc.ConvertDBNullToDecimal(addDr["V" + tagTime.Day.ToString().PadLeft(2, '0')]) + useVal;
}
else if (dataType.Equals("month"))
{
addDr["V" + tagTime.Month.ToString().PadLeft(2, '0')] = CommFunc.ConvertDBNullToDecimal(addDr["V" + tagTime.Month.ToString().PadLeft(2, '0')]) + useVal;
}
addDr["UseVal"] = CommFunc.ConvertDBNullToDecimal(addDr["UseVal"]) + useVal;
}
#endregion
#region 获取结果集数据
var res1 = from s1 in dtRst.AsEnumerable()
select new
{
ModuleName = CommFunc.ConvertDBNullToString(s1["ModuleName"]),
Cnt = CommFunc.ConvertDBNullToInt32(s1["Cnt"]),
V01 = CommFunc.ConvertDBNullToDecimal(s1["V01"]),
V02 = CommFunc.ConvertDBNullToDecimal(s1["V02"]),
V03 = CommFunc.ConvertDBNullToDecimal(s1["V03"]),
V04 = CommFunc.ConvertDBNullToDecimal(s1["V04"]),
V05 = CommFunc.ConvertDBNullToDecimal(s1["V05"]),
V06 = CommFunc.ConvertDBNullToDecimal(s1["V06"]),
V07 = CommFunc.ConvertDBNullToDecimal(s1["V07"]),
V08 = CommFunc.ConvertDBNullToDecimal(s1["V08"]),
V09 = CommFunc.ConvertDBNullToDecimal(s1["V09"]),
V10 = CommFunc.ConvertDBNullToDecimal(s1["V10"]),
V11 = CommFunc.ConvertDBNullToDecimal(s1["V11"]),
V12 = CommFunc.ConvertDBNullToDecimal(s1["V12"]),
V13 = CommFunc.ConvertDBNullToDecimal(s1["V13"]),
V14 = CommFunc.ConvertDBNullToDecimal(s1["V14"]),
V15 = CommFunc.ConvertDBNullToDecimal(s1["V15"]),
V16 = CommFunc.ConvertDBNullToDecimal(s1["V16"]),
V17 = CommFunc.ConvertDBNullToDecimal(s1["V17"]),
V18 = CommFunc.ConvertDBNullToDecimal(s1["V18"]),
V19 = CommFunc.ConvertDBNullToDecimal(s1["V19"]),
V20 = CommFunc.ConvertDBNullToDecimal(s1["V20"]),
V21 = CommFunc.ConvertDBNullToDecimal(s1["V21"]),
V22 = CommFunc.ConvertDBNullToDecimal(s1["V22"]),
V23 = CommFunc.ConvertDBNullToDecimal(s1["V23"]),
V24 = CommFunc.ConvertDBNullToDecimal(s1["V24"]),
V25 = CommFunc.ConvertDBNullToDecimal(s1["V25"]),
V26 = CommFunc.ConvertDBNullToDecimal(s1["V26"]),
V27 = CommFunc.ConvertDBNullToDecimal(s1["V27"]),
V28 = CommFunc.ConvertDBNullToDecimal(s1["V28"]),
V29 = CommFunc.ConvertDBNullToDecimal(s1["V29"]),
V30 = CommFunc.ConvertDBNullToDecimal(s1["V30"]),
V31 = CommFunc.ConvertDBNullToDecimal(s1["V31"]),
};
return(res1.ToList());
#endregion
}
public object GetEnergyForDayX261()
{
DateTime fm = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
DateTime to = DateTime.Now.AddHours(-1);
List <decimal> dd = new List <decimal>();
int nn = to.Hour;
while (nn-- >= 0)
{
dd.Add(0);
}
DataTable dtRst = new DataTable();
dtRst.Columns.Add("ModuleAddr", typeof(System.String));
dtRst.Columns.Add("ModuleName", typeof(System.String));
dtRst.Columns.Add("ParentName", typeof(System.String));
dtRst.Columns.Add("UseVal", typeof(System.Decimal));
dtRst.Columns.Add("UseObj", typeof(System.Object));
dtRst.Rows.Add("R6B2.X261.ⅠE", "X261 OP30/40", "焊接X261 OP30/40", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B1.1#.LJ", "1# X261 OP30/40", "焊接X261 OP30/40", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B1.2#.LJ", "2# X261 OP30/40", "焊接X261 OP30/40", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B2.X261.ⅡE", "X261 OP45/50", "焊接X261 OP45/50", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B1.3#.LJ", "3# X261 OP45/50", "焊接X261 OP45/50", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B1.4#.LJ", "4# X261 OP45/50", "焊接X261 OP45/50", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B2.X261.ⅢE", "X261 OP60/70", "焊接X261 OP60/70", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B1.5#.LJ", "5# X261 OP60/70", "焊接X261 OP60/70", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B1.6#.LJ", "6# X261 OP60/70", "焊接X261 OP60/70", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B2.X261.ⅣE", "X261 OP80/85", "焊接X261 OP80/85", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B1.7#.LJ", "7# X261 OP80/85", "焊接X261 OP80/85", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
dtRst.Rows.Add("R6B1.8#.LJ", "8# X261 OP80/85", "焊接X261 OP80/85", 0, JsonHelper.Deserialize <List <decimal> >(JsonHelper.Serialize(dd)));
StringBuilder strAddrs = new StringBuilder();
foreach (DataRow dr in dtRst.Rows)
{
if (!string.IsNullOrEmpty(strAddrs.ToString()))
{
strAddrs.Append(",");
}
strAddrs.Append(CommFunc.ConvertDBNullToString(dr["ModuleAddr"]));
}
//
DataTable dtSource = dal.GetEnergyForDayX261(strAddrs.ToString());
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["ModuleAddr"] };
//
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), fm, to, "hour", splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
DataRow addDr = dtRst.Rows.Find(curDr["ModuleAddr"]);
if (addDr == null)
{
continue;
}
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
addDr["UseVal"] = CommFunc.ConvertDBNullToDecimal(addDr["UseVal"]) + useVal;
List <decimal> rr = addDr["UseObj"] as List <decimal>;
if (rr == null)
{
continue;
}
rr[tagTime.Hour] = rr[tagTime.Hour] + useVal;
addDr["UseObj"] = rr;
}
var res1 = from s1 in dtRst.AsEnumerable()
group s1 by new { ParentName = CommFunc.ConvertDBNullToString(s1["ParentName"]) } into g1
select new {
ParentName = g1.Key.ParentName,
UseVal = g1.Sum(p => CommFunc.ConvertDBNullToDecimal(p["UseVal"]))
};
List <object> result = new List <object>();
foreach (var s1 in res1)
{
var res2 = from s2 in dtRst.AsEnumerable()
where CommFunc.ConvertDBNullToString(s2["ParentName"]).Equals(s1.ParentName)
select new
{
name = CommFunc.ConvertDBNullToString(s2["ModuleName"]),
data = s2["UseObj"] as List <decimal>,
};
result.Add(new { name = s1.ParentName, data = res2.ToList() });
}
return(result);
}
public object GetPduFunTypes(int co_id, string funTypes)
{
//string funType = string.Join(",", funTypes.ToArray());
string funType = funTypes;//
DateTime today2 = DateTime.Now.AddHours(-1); DateTime today1 = new DateTime(today2.Year, today2.Month, today2.Day);
Dictionary <string, List <decimal> > rr = new Dictionary <string, List <decimal> >();
foreach (string str in funTypes.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
List <decimal> todayList = new List <decimal>();
if (rr.TryGetValue(str, out todayList) == false)
{
todayList = new List <decimal>();
int nn = today2.Hour;
while (nn-- >= 0)
{
todayList.Add(0);
}
rr.Add(str, todayList);
}
}
//
DataTable dtSource = dal.GetPduFunTypes(co_id, funType);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
}
//if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitMdQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["Module_id"]), ",")))
//{
// if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
// splitMdQuery.Append(",");
// splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
//}
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
DataTable dtUse1 = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), today1, today2, "hour", splitTyQuery.ToString());
decimal todayUse = 0;
foreach (DataRow dr in dtUse1.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
int isChrg = CommFunc.ConvertDBNullToInt32(curDr["IsChrg"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
//
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = 0;
if (isChrg == 1)
{
useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
//
List <decimal> todayList = new List <decimal>();
if (rr.TryGetValue(CommFunc.ConvertDBNullToString(curDr["FunType"]), out todayList) == true)
{
todayList[tagTime.Hour] = CommFunc.ConvertDBNullToDecimal(todayList[tagTime.Hour]) + useVal;
todayUse = todayUse + useVal;
}
}
else
{
useVal = lastVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
//
List <decimal> todayList = new List <decimal>();
if (rr.TryGetValue(CommFunc.ConvertDBNullToString(curDr["FunType"]), out todayList) == true)
{
todayList[tagTime.Hour] = CommFunc.ConvertDBNullToDecimal(todayList[tagTime.Hour]) > useVal?CommFunc.ConvertDBNullToDecimal(todayList[tagTime.Hour]) : useVal;
todayUse = todayUse > useVal ? todayUse : useVal;
}
}
}
List <object> dd = new List <object>();
foreach (var ss in rr)
{
dd.Add(new { funType = ss.Key, list = ss.Value });
}
return(dd);
}
/// <summary>
/// 按小时显示图形
/// </summary>
/// <param name="Room_id"></param>
/// <param name="dtTime"></param>
/// <returns></returns>
public DataTable GetYdMonitorInDetailOnChartsData(int Co_id, int Module_id, string ModuleAddr, DateTime Start, DateTime End, string DateType)
{
DataTable dtRst = new DataTable();
dtRst.Columns.Add("TagTime", typeof(System.DateTime));
dtRst.Columns.Add("Time", typeof(System.String));
dtRst.Columns.Add("UseVal", typeof(System.Decimal));
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["TagTime"] };
StringBuilder strKey1 = new StringBuilder();
DataTable dtEz = dal.GetYdMonitorOnEzInfo(Module_id);
int fun_id = 0, scale = 0;
decimal multiply = 0;
if (dtEz.Rows.Count > 0)
{
fun_id = CommFunc.ConvertDBNullToInt32(dtEz.Rows[0]["Fun_id"]);
scale = CommFunc.ConvertDBNullToInt32(dtEz.Rows[0]["Scale"]);
multiply = CommFunc.ConvertDBNullToDecimal(dtEz.Rows[0]["Multiply"]);
}
DateTime start = new DateTime(Start.Year, Start.Month, Start.Day, Start.Hour, 0, 0), end = new DateTime(End.Year, End.Month, End.Day, End.Hour, 0, 0);
#region 填充主数据
while (start <= end)
{
strKey1.Clear();
if (DateType.Equals("hour"))
{
strKey1.Append(start.Hour.ToString().PadLeft(2, '0') + "时");
}
if (DateType.Equals("day"))
{
strKey1.Append(start.Day.ToString().PadLeft(2, '0') + "日");
}
if (DateType.Equals("month"))
{
strKey1.Append(start.Month.ToString().PadLeft(2, '0') + "月");
}
if (DateType.Equals("year"))
{
strKey1.Append(start.Month.ToString().PadLeft(2, '0') + "月");
}
DataRow addDr = dtRst.Rows.Find(new object[] { start });
if (addDr == null)
{ //增加需显示的表
addDr = dtRst.NewRow();
addDr["TagTime"] = start;
addDr["Time"] = strKey1.ToString();
dtRst.Rows.Add(addDr);
}
if (DateType.Equals("hour"))
{
start = start.AddHours(1);
}
if (DateType.Equals("day"))
{
start = start.AddDays(1);
}
if (DateType.Equals("month"))
{
start = start.AddMonths(1);
}
if (DateType.Equals("year"))
{
start = start.AddMonths(1);
}
}
#endregion
DataTable dtSource = WholeBLL.GetCoreQueryData(this.Ledger, Module_id.ToString(), Start, End, DateType);
foreach (DataRow dr in dtSource.Rows)
{
if (CommFunc.ConvertDBNullToInt32(dr["Fun_id"]) != fun_id)
{
continue;
}
if (CommFunc.ConvertDBNullToInt32(dr["Co_id"]) != Co_id)
{
continue;
}
if (!CommFunc.ConvertDBNullToString(dr["ModuleAddr"]).Equals(ModuleAddr))
{
continue;
}
DataRow curDr = dtRst.Rows.Find(dr["TagTime"]);
if (curDr == null)
{
continue;
}
decimal useVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]) - CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
useVal = useVal * multiply;
if (scale != 0)
{
useVal = Math.Round(useVal, scale, MidpointRounding.AwayFromZero);
}
curDr["UseVal"] = useVal;
}
return(dtRst);
}
/// <summary>
/// 获取分项用能数据
/// </summary>
/// <returns></returns>
public object GetDayForEnergy(int isDefine)
{
DataTable dtRst = new DataTable();
dtRst.Columns.Add("EnergyItemCode", typeof(System.String));
dtRst.Columns.Add("UseVal", typeof(System.Decimal));
dtRst.Columns.Add("Unit", typeof(System.String));
dtRst.Columns.Add("EnergyItemName", typeof(System.String));
//
DataTable dtSource = dal.GetDayForEnergy(isDefine);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["EnergyItemCode"] };
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
string mmName = "";
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
if (!string.IsNullOrEmpty(CommFunc.ConvertDBNullToString(dr["EnergyItemCode"])))
{
DataRow addDr = dtRst.Rows.Find(dr["EnergyItemCode"]);
if (addDr == null)
{
addDr = dtRst.Rows.Add(new object[] { dr["EnergyItemCode"], 0, dr["Unit"], dr["EnergyItemName"] });
}
}
mmName = CommFunc.ConvertDBNullToString(dr["MmName"]);
}
DateTime today2 = DateTime.Now; DateTime today1 = new DateTime(today2.Year, today2.Month, today2.Day);
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), today1, today2, "day", splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
DataRow addDr = dtRst.Rows.Find(curDr["EnergyItemCode"]);
if (addDr == null)
{
continue;
}
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
addDr["UseVal"] = CommFunc.ConvertDBNullToDecimal(addDr["UseVal"]) + useVal;
}
if (dtRst.Rows.Count == 0)
{
dtRst.Rows.Add(new object[] { isDefine.ToString(), 0, "", mmName });
}
var res1 = from s1 in dtRst.AsEnumerable()
select new
{
EnergyItemCode = CommFunc.ConvertDBNullToString(s1["EnergyItemCode"]),
UseVal = CommFunc.ConvertDBNullToDecimal(s1["UseVal"]),
Unit = CommFunc.ConvertDBNullToString(s1["Unit"]),
EnergyItemName = CommFunc.ConvertDBNullToString(s1["EnergyItemName"]),
};
return(res1.ToList());
}
public DataTable GetMonthAnalysis(int module_id, DateTime time)
{
DataTable dtRst = new DataTable();
dtRst.Columns.Add("Month", typeof(System.Int32)); /*月份*/
dtRst.Columns.Add("UseVal", typeof(System.Decimal)); /*当月电力*/
dtRst.Columns.Add("UseAmt", typeof(System.Decimal)); /*当月电力*/
dtRst.Columns.Add("Step", typeof(System.Int32));
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["Month"] };
//
DataTable dtSource = dal.GetPayUseVal(module_id);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
#region 本月获取存储数据
DateTime fm = new DateTime(time.Year, 1, 1);
DateTime to = fm.AddYears(1).AddDays(-1);
//
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), fm, to, "month", splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
if (CommFunc.ConvertDBNullToInt32(curDr["Co_id"]) != CommFunc.ConvertDBNullToInt32(dr["Co_id"])) /*必须相同房间才计算*/
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
decimal price = CommFunc.ConvertDBNullToDecimal(curDr["Price"]);
scale = scale == 0 ? 2 : scale;
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
decimal useAmt = Math.Round(useVal * price, 2, MidpointRounding.AwayFromZero);
///////////////////////////////
DataRow addDr = dtRst.Rows.Find(tagTime.Month);
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["Month"] = tagTime.Month;
addDr["UseVal"] = 0;
addDr["UseAmt"] = 0;
addDr["Step"] = 1;
dtRst.Rows.Add(addDr);
}
addDr["UseVal"] = CommFunc.ConvertDBNullToDecimal(addDr["UseVal"]) + useVal;
addDr["UseAmt"] = CommFunc.ConvertDBNullToDecimal(addDr["UseAmt"]) + useAmt;
}
int i = 0;
while (++i <= 12)
{
DataRow addDr = dtRst.Rows.Find(i);
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["Month"] = i;
addDr["UseVal"] = 0;
addDr["UseAmt"] = 0;
addDr["Step"] = 0;/*不存在月份*/
dtRst.Rows.Add(addDr);
}
}
#endregion
return(dtRst);
}
/// <summary>
/// 获取用能数据
/// 返回参数
/// 第一个是当天的小时用能
/// 第二个是昨日的小时用能
/// 第三个当天的用能总和
/// 最后是同期比
/// </summary>
/// <param name="isDefine"></param>
/// <returns></returns>
public object GetDayForChart(int isDefine)
{
DataTable dtSource = dal.GetMdList(isDefine);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
DateTime today2 = DateTime.Now.AddHours(-1); DateTime today1 = new DateTime(today2.Year, today2.Month, today2.Day);
DateTime yesterday1 = today1.AddDays(-1); DateTime yesterday2 = new DateTime(yesterday1.Year, yesterday1.Month, yesterday1.Day, 23, 59, 0);
DataTable dtUse1 = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), today1, today2, "hour", splitTyQuery.ToString());
DataTable dtUse2 = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), yesterday1, yesterday2, "hour", splitTyQuery.ToString());
List <decimal> todayList = new List <decimal>();
List <decimal> ysdayList = new List <decimal>()
{
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
};
int nn = today2.Hour;
while (nn-- >= 0)
{
todayList.Add(0);
}
decimal todayUse = 0, yesterdayUse = 0;
foreach (DataRow dr in dtUse1.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
todayList[tagTime.Hour] = CommFunc.ConvertDBNullToDecimal(todayList[tagTime.Hour]) + useVal;
todayUse = todayUse + useVal;
}
foreach (DataRow dr in dtUse2.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
ysdayList[tagTime.Hour] = CommFunc.ConvertDBNullToDecimal(ysdayList[tagTime.Hour]) + useVal;
if (tagTime.Hour <= today2.Hour)
{
yesterdayUse = yesterdayUse + useVal;
}
}
decimal tongBiZeng = Math.Round((yesterdayUse == 0 ? 0 : ((todayUse - yesterdayUse) / yesterdayUse)) * 100, 2, MidpointRounding.AwayFromZero);
return(new { todayList = todayList, ysdayList = ysdayList, todayUse = todayUse, tongBiZeng = tongBiZeng.ToString("f2") + "%" });
}
/// <summary>
/// 获取分项统计(比亚迪)
/// </summary>
/// <param name="co_id">支路ID号</param>
/// <param name="time">时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="dataType">类型 日=day月=month年year</param>
/// <returns></returns>
public object GetEnergyItemForByd(int co_id, DateTime time, DateTime?endTime, string dataType)
{
DataTable dtRst = this.GetEnergyItemTimeTabSchema();
int cnt = 24;
DateTime fm = time, to = time;
if (dataType.ToLower().Equals("day"))
{
fm = new DateTime(time.Year, time.Month, time.Day, time.Hour, time.Minute, time.Second);
to = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, endTime.Value.Hour, endTime.Value.Minute, endTime.Value.Second);
cnt = (int)(to - fm).TotalHours + 1;
dataType = "hour";
dtRst = this.GetEnergyItemTimeTabSchemaForByd(fm, to);
}
else if (dataType.ToLower().Equals("month"))
{
fm = new DateTime(time.Year, time.Month, 1);
to = fm.AddMonths(1).AddDays(-1);
cnt = to.Day;
dataType = "day";
}
else if (dataType.ToLower().Equals("year"))
{
fm = new DateTime(time.Year, 1, 1);
to = new DateTime(time.Year, 12, 1);
cnt = 12;
dataType = "month";
}
else
{
throw new Exception("查询类型错误");
}
DataTable dtSource = dal.GetEnergyMdList(co_id, "");
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["EnergyItemCode"] };
//
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
if (dr["EnergyItemCode"].ToString().Equals("01C00") || dr["EnergyItemCode"].ToString().Equals("01D00"))
{
dr["EnergyItemCode"] = "01A00";
dr["EnergyItemName"] = "照明插座用电";
}
DataRow addDr = dtRst.Rows.Find(dr["EnergyItemCode"]);
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["EnergyItemCode"] = dr["EnergyItemCode"];
addDr["Cnt"] = cnt;
addDr["EnergyItemName"] = dr["EnergyItemName"];
addDr["ParentItemCode"] = dr["ParentItemCode"];
addDr["ParentItemName"] = dr["ParentItemName"];
dtRst.Rows.Add(addDr);
}
}
#region 获取存储数据
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), fm, to, dataType, splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
DataRow addDr = dtRst.Rows.Find(curDr["EnergyItemCode"]);
if (addDr == null)
{
continue;
}
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
if (dataType.Equals("hour"))
{
addDr[tagTime.ToString("yyyyMMdd-HH")] = CommFunc.ConvertDBNullToDecimal(addDr[tagTime.ToString("yyyyMMdd-HH")]) + useVal;
}
//addDr["V" + (tagTime.Hour + 1).ToString().PadLeft(2, '0')] = CommFunc.ConvertDBNullToDecimal(addDr["V" + (tagTime.Hour + 1).ToString().PadLeft(2, '0')]) + useVal;
else if (dataType.Equals("day"))
{
addDr["V" + tagTime.Day.ToString().PadLeft(2, '0')] = CommFunc.ConvertDBNullToDecimal(addDr["V" + tagTime.Day.ToString().PadLeft(2, '0')]) + useVal;
}
else if (dataType.Equals("month"))
{
addDr["V" + tagTime.Month.ToString().PadLeft(2, '0')] = CommFunc.ConvertDBNullToDecimal(addDr["V" + tagTime.Month.ToString().PadLeft(2, '0')]) + useVal;
}
addDr["UseVal"] = CommFunc.ConvertDBNullToDecimal(addDr["UseVal"]) + useVal;
}
#endregion
#region 获取结果集数据
//var res1 = from s1 in dtRst.AsEnumerable()
// select new
// {
// EnergyItemName = CommFunc.ConvertDBNullToString(s1["EnergyItemName"]),
// Cnt = CommFunc.ConvertDBNullToInt32(s1["Cnt"]),
// V01 = CommFunc.ConvertDBNullToDecimal(s1["V01"]),
// V02 = CommFunc.ConvertDBNullToDecimal(s1["V02"]),
// V03 = CommFunc.ConvertDBNullToDecimal(s1["V03"]),
// V04 = CommFunc.ConvertDBNullToDecimal(s1["V04"]),
// V05 = CommFunc.ConvertDBNullToDecimal(s1["V05"]),
// V06 = CommFunc.ConvertDBNullToDecimal(s1["V06"]),
// V07 = CommFunc.ConvertDBNullToDecimal(s1["V07"]),
// V08 = CommFunc.ConvertDBNullToDecimal(s1["V08"]),
// V09 = CommFunc.ConvertDBNullToDecimal(s1["V09"]),
// V10 = CommFunc.ConvertDBNullToDecimal(s1["V10"]),
// V11 = CommFunc.ConvertDBNullToDecimal(s1["V11"]),
// V12 = CommFunc.ConvertDBNullToDecimal(s1["V12"]),
// V13 = CommFunc.ConvertDBNullToDecimal(s1["V13"]),
// V14 = CommFunc.ConvertDBNullToDecimal(s1["V14"]),
// V15 = CommFunc.ConvertDBNullToDecimal(s1["V15"]),
// V16 = CommFunc.ConvertDBNullToDecimal(s1["V16"]),
// V17 = CommFunc.ConvertDBNullToDecimal(s1["V17"]),
// V18 = CommFunc.ConvertDBNullToDecimal(s1["V18"]),
// V19 = CommFunc.ConvertDBNullToDecimal(s1["V19"]),
// V20 = CommFunc.ConvertDBNullToDecimal(s1["V20"]),
// V21 = CommFunc.ConvertDBNullToDecimal(s1["V21"]),
// V22 = CommFunc.ConvertDBNullToDecimal(s1["V22"]),
// V23 = CommFunc.ConvertDBNullToDecimal(s1["V23"]),
// V24 = CommFunc.ConvertDBNullToDecimal(s1["V24"]),
// V25 = CommFunc.ConvertDBNullToDecimal(s1["V25"]),
// V26 = CommFunc.ConvertDBNullToDecimal(s1["V26"]),
// V27 = CommFunc.ConvertDBNullToDecimal(s1["V27"]),
// V28 = CommFunc.ConvertDBNullToDecimal(s1["V28"]),
// V29 = CommFunc.ConvertDBNullToDecimal(s1["V29"]),
// V30 = CommFunc.ConvertDBNullToDecimal(s1["V30"]),
// V31 = CommFunc.ConvertDBNullToDecimal(s1["V31"]),
// };
List <object> dd = new List <object>();
//Dictionary<string, object> dd = new Dictionary<string, object>();
var res2 = from s1 in dtRst.AsEnumerable()
where !string.IsNullOrEmpty(CommFunc.ConvertDBNullToString(s1["ParentItemCode"]))
group s1 by new { ParentItemCode = CommFunc.ConvertDBNullToString(s1["ParentItemCode"]) } into g1
select new
{
ParentItemCode = g1.Key.ParentItemCode,
ParentItemName = CommFunc.ConvertDBNullToString(g1.First()["ParentItemName"])
};
foreach (var p1 in res2)
{
var res3 = from s1 in dtRst.AsEnumerable()
where CommFunc.ConvertDBNullToString(s1["ParentItemCode"]).Equals(p1.ParentItemCode)
select new
{
EnergyItemName = CommFunc.ConvertDBNullToString(s1["EnergyItemName"]),
UseVal = CommFunc.ConvertDBNullToDecimal(s1["UseVal"]),
};
dd.Add(new { Key = p1.ParentItemName, Value = res3.ToList() });
//dd.Add(p1.ParentItemName, res3.ToList());
}
object rst = new { rows = dtRst, pie = dd.ToList() };
return(rst);
#endregion
}
/// <summary>
/// 账单
/// </summary>
/// <returns></returns>
public DataTable GetBill()
{
DataTable dtSource = dal.GetMdInfo();
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
DataTable dtRst = dal.GetBill();
dtRst.Columns.Add("EleUseVal", typeof(System.Decimal));
dtRst.Columns.Add("ElePrice", typeof(System.Decimal));
dtRst.Columns.Add("EleUseAmt", typeof(System.Decimal));
foreach (DataRow drRst in dtRst.Rows)
{
DateTime firstTime = CommFunc.ConvertDBNullToDateTime(drRst["FirstTime"]);
DateTime lastTime = CommFunc.ConvertDBNullToDateTime(drRst["LastTime"]);
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), firstTime, lastTime, "day", splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
decimal price = CommFunc.ConvertDBNullToDecimal(curDr["Price"]);
scale = scale == 0 ? 2 : scale;
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
decimal useAmt = Math.Round(useVal * price, 2, MidpointRounding.AwayFromZero);
drRst["EleUseVal"] = CommFunc.ConvertDBNullToDecimal(drRst["EleUseVal"]) + useVal;
drRst["ElePrice"] = price;
drRst["EleUseAmt"] = CommFunc.ConvertDBNullToDecimal(drRst["EleUseAmt"]) + useAmt;
}
}
return(dtRst);
}
public DataTable GetPduEnergyPie(int coid)
{
DataTable dtRst = new DataTable();
dtRst.Columns.Add("Parent_id", typeof(System.Int32));
dtRst.Columns.Add("UseVal", typeof(System.Decimal));
dtRst.Columns.Add("CoName", typeof(System.String));
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["Parent_id"] };
///////////
DataTable dtSource = dal.GetPduEnergyPie(coid);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
StringBuilder splitMdQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
DataRow addDr = dtRst.Rows.Find(dr["Parent_id"]);
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["Parent_id"] = dr["Parent_id"];
addDr["CoName"] = dr["CoName"];
addDr["UseVal"] = 0;
dtRst.Rows.Add(addDr);
}
}
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), DateTime.Now, DateTime.Now, "day", "E");
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
int co_id = CommFunc.ConvertDBNullToInt32(curDr["Co_id"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
if (CommFunc.ConvertDBNullToInt32(dr["Co_id"]) != co_id)
{
continue;
}
DataRow addDr = dtRst.Rows.Find(curDr["Parent_id"]);
if (addDr == null)
{
continue;
}
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
addDr["UseVal"] = CommFunc.ConvertDBNullToDecimal(addDr["UseVal"]) + useVal;
}
return(dtRst);
}
/// <summary>
/// 历史数据
/// </summary>
///// <param name="co_id">用电单元ID号</param>
/// <param name="module_id">回路ID号</param>
/// <param name="funType">采集项目</param>
/// <param name="dataType">查询类型</param>
/// <param name="startTime">开始日期</param>
/// <returns></returns>
public DataTable GetHisData(int module_id, string funType, string dataType, DateTime start, DateTime end)
{
dataType = dataType.ToLower();
DataTable dtRst = this.GetYdHisTableScheam();
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["TagTime"], dtRst.Columns["Module_id"], dtRst.Columns["Fun_id"] };
DataTable dtSource = dal.GetYdMontionOnList(module_id, funType);
StringBuilder strSplit = new StringBuilder();
StringBuilder strKey1 = new StringBuilder();
int cnt = 0, RowId = 0;
if (dataType.Equals("hour"))
{
cnt = 24;
}
if (dataType.Equals("day"))
{
DateTime td1 = new DateTime(start.Year, end.Month, 1);
cnt = td1.AddMonths(1).AddDays(-1).Day;
}
if (dataType.Equals("month"))
{
cnt = 12;
}
//
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(strSplit.ToString()))
{
strSplit.Append(",");
}
strSplit.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
//计算时间
DateTime begin = new DateTime(start.Year, start.Month, start.Day, start.Hour, 0, 0), over = new DateTime(end.Year, end.Month, end.Day, end.Hour, 0, 0);
#region 填充主数据
while (begin <= over)
{
strKey1.Clear();
if (dataType.Equals("hour"))
{
strKey1.Append(begin.ToString("yyyy-MM-dd"));
}
if (dataType.Equals("day"))
{
strKey1.Append(begin.ToString("yyyy-MM"));
}
if (dataType.Equals("month"))
{
strKey1.Append(begin.ToString("yyyy"));
}
if (dataType.Equals("year"))
{
strKey1.Append(begin.ToString("yyyy"));
}
DataRow addDr = dtRst.Rows.Find(new object[] { begin, dr["Module_id"], dr["Fun_id"] });
if (addDr == null)
{ //增加需显示的表
addDr = dtRst.NewRow();
addDr["RowId"] = ++RowId;
addDr["TagTime"] = begin;
addDr["Module_id"] = dr["Module_id"];
addDr["Fun_id"] = dr["Fun_id"];
addDr["FunType"] = dr["FunType"];
addDr["FunName"] = dr["FunName"];
addDr["ModuleAddr"] = dr["ModuleAddr"];
addDr["ModuleName"] = dr["ModuleName"];
addDr["Multiply"] = dr["Multiply"];
addDr["Co_id"] = dr["Co_id"];
addDr["CoStrcName"] = dr["CoStrcName"];
addDr["CoName"] = dr["CoName"];
addDr["TagTimeS"] = strKey1.ToString();
//addDr["Fun_id"] = dr["Fun_id"];
addDr["Scale"] = dr["Scale"];
addDr["Cnt"] = cnt;
addDr["Value"] = 0;
dtRst.Rows.Add(addDr);
}
if (dataType.Equals("hour"))
{
begin = begin.AddHours(1);
}
else if (dataType.Equals("day"))
{
begin = begin.AddDays(1);
}
else if (dataType.Equals("month"))
{
begin = begin.AddMonths(1);
}
else
{
break;
}
}
#endregion
}
#region 获取历史数据
DataTable dtTag = WholeBLL.GetCoreQueryData(this.Ledger, strSplit.ToString(), start, end, dataType, funType);
foreach (DataRow dr in dtTag.Rows)
{
DateTime tagtime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
DateTime key1 = tagtime;
if (dataType.Equals("hour"))
{
key1 = new DateTime(tagtime.Year, tagtime.Month, tagtime.Day);
}
if (dataType.Equals("day"))
{
key1 = new DateTime(tagtime.Year, tagtime.Month, 1);
}
if (dataType.Equals("month"))
{
key1 = new DateTime(tagtime.Year, 1, 1);
}
if (dataType.Equals("year"))
{
key1 = new DateTime(tagtime.Year, 1, 1);
}
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
DataRow rst = dtRst.Rows.Find(new object[] { tagtime, dr["Module_id"], dr["Fun_id"] });
if (rst == null)
{
continue;
}
if (CommFunc.ConvertDBNullToInt32(dr["Fun_id"]) != CommFunc.ConvertDBNullToInt32(rst["Fun_id"]))
{
continue;
}
if (CommFunc.ConvertDBNullToInt32(dr["Co_id"]) != CommFunc.ConvertDBNullToInt32(rst["Co_id"]))
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(rst["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(rst["Multiply"]);
decimal useVal = lastVal;
if (CommFunc.ConvertDBNullToString(rst["FunType"]).Equals("E"))
{
useVal = (lastVal - firstVal) * multiply;
if (scale != 0)
{
useVal = Math.Round(useVal, scale, MidpointRounding.AwayFromZero);
}
}
rst["Value"] = useVal;
//int tagRow = 0;
//if (dataType.Equals("hour"))
// tagRow = tagtime.Hour;
//if (dataType.Equals("day"))
// tagRow = tagtime.Day - 1;
//if (dataType.Equals("month"))
// tagRow = tagtime.Month - 1;
//rst["h" + tagRow.ToString().PadLeft(2, '0')] = useVal; // dr["LastVal"];
}
#endregion
return(dtRst);
}
/// <summary>
/// 运行历史报表
/// </summary>
/// <param name="CoStrcName"></param>
/// <param name="CoName"></param>
/// <param name="DateType"></param>
/// <param name="Start"></param>
/// <param name="End"></param>
/// <returns></returns>
public DataTable GetYdMonitorOnList(string CoStrcName, string CoName, string DateType, DateTime Start, DateTime End, int Co_id)
{
DataTable dtRst = this.GetYdMotionTableScheam();
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["TagTime"], dtRst.Columns["Module_id"] };
DataTable dtSource = dal.GetYdMontionOnList(CoStrcName, CoName, Co_id);
StringBuilder strSplit = new StringBuilder();
StringBuilder strKey1 = new StringBuilder();
int cnt = 0, RowId = 0;
if (DateType.Equals("hour"))
{
cnt = 24;
}
if (DateType.Equals("day"))
{
DateTime td1 = new DateTime(Start.Year, Start.Month, 1);
cnt = td1.AddMonths(1).AddDays(-1).Day;
}
if (DateType.Equals("month"))
{
cnt = 12;
}
if (DateType.Equals("other"))
{
cnt = 1;
}
//
foreach (DataRow dr in dtSource.Rows)
{
if (!string.IsNullOrEmpty(strSplit.ToString()))
{
strSplit.Append(",");
}
strSplit.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
//计算时间
DateTime start = new DateTime(Start.Year, Start.Month, Start.Day, Start.Hour, 0, 0), end = new DateTime(End.Year, End.Month, End.Day, End.Hour, 0, 0);
#region 填充主数据
while (start <= end)
{
strKey1.Clear();
if (DateType.Equals("hour"))
{
strKey1.Append(start.ToString("yyyy-MM-dd"));
}
if (DateType.Equals("day"))
{
strKey1.Append(start.ToString("yyyy-MM"));
}
if (DateType.Equals("month"))
{
strKey1.Append(start.ToString("yyyy"));
}
if (DateType.Equals("year"))
{
strKey1.Append(start.ToString("yyyy"));
}
if (DateType.Equals("other"))
{
strKey1.Append(start.ToString("yyyy-MM-dd"));
}
DataRow addDr = dtRst.Rows.Find(new object[] { start, dr["Module_id"] });
if (addDr == null)
{ //增加需显示的表
addDr = dtRst.NewRow();
addDr["RowId"] = ++RowId;
addDr["TagTime"] = start;
addDr["Module_id"] = dr["Module_id"];
addDr["ModuleAddr"] = dr["ModuleAddr"];
addDr["ModuleName"] = dr["ModuleName"];
addDr["Multiply"] = dr["Multiply"];
addDr["Co_id"] = dr["Co_id"];
addDr["CoStrcName"] = dr["CoStrcName"];
addDr["CoName"] = dr["CoName"];
addDr["TagTimeS"] = strKey1.ToString();
addDr["Fun_id"] = dr["Fun_id"];
addDr["Scale"] = dr["Scale"];
addDr["Cnt"] = cnt;
dtRst.Rows.Add(addDr);
//
}
if (DateType.Equals("hour"))
{
start = start.AddDays(1);
}
if (DateType.Equals("day"))
{
start = start.AddMonths(1);
}
if (DateType.Equals("month"))
{
start = start.AddYears(1);
}
if (DateType.Equals("other"))
{
start = start.AddDays(1);
}
}
#endregion
}
if (DateType.Equals("other"))
{
DateType = "day";
}
#region 获取历史数据
int others = 0;
DataTable dtTag = WholeBLL.GetCoreQueryData(this.Ledger, strSplit.ToString(), Start, End, DateType);
foreach (DataRow dr in dtTag.Rows)
{
DateTime tagtime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
DateTime key1 = tagtime;
if (DateType.Equals("hour"))
{
key1 = new DateTime(tagtime.Year, tagtime.Month, tagtime.Day);
}
if (DateType.Equals("day"))
{
key1 = new DateTime(tagtime.Year, tagtime.Month, 1);
}
if (DateType.Equals("month"))
{
key1 = new DateTime(tagtime.Year, 1, 1);
}
if (DateType.Equals("year"))
{
key1 = new DateTime(tagtime.Year, 1, 1);
}
if (DateType.Equals("other"))
{
key1 = new DateTime(tagtime.Year, 1, 1);
}
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
DataRow rst = dtRst.Rows.Find(new object[] { key1, dr["Module_id"] });
if (rst == null)
{
continue;
}
if (CommFunc.ConvertDBNullToInt32(dr["Fun_id"]) != CommFunc.ConvertDBNullToInt32(rst["Fun_id"]))
{
continue;
}
if (CommFunc.ConvertDBNullToInt32(dr["Co_id"]) != CommFunc.ConvertDBNullToInt32(rst["Co_id"]))
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(rst["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(rst["Multiply"]);
decimal useVal = (lastVal - firstVal) * multiply;
if (scale != 0)
{
useVal = Math.Round(useVal, scale, MidpointRounding.AwayFromZero);
}
int tagRow = 0;
if (DateType.Equals("hour"))
{
tagRow = tagtime.Hour;
}
if (DateType.Equals("day"))
{
tagRow = tagtime.Day - 1;
}
if (DateType.Equals("month"))
{
tagRow = tagtime.Month - 1;
}
if (DateType.Equals("other"))
{
tagRow = others;
}
rst["h" + tagRow.ToString().PadLeft(2, '0')] = useVal; // dr["LastVal"];
others = others + 1;
}
#endregion
//////
return(dtRst);
}
/// <summary>
/// 获取分项统计
/// </summary>
/// <param name="co_id">支路ID号</param>
/// <param name="time">时间</param>
/// <param name="dataType">类型 日=day月=month年year</param>
/// <returns></returns>
public DataTable GetEnergyHisdata(int co_id, DateTime start, DateTime end, string moduleName, string funType)
{
DateTime dtFirst = new DateTime(start.Year, start.Month, start.Day, 0, 0, 0);
DateTime dtLast = new DateTime(end.Year, end.Month, end.Day, 23, 59, 59);
DataTable dtRst = new DataTable();
dtRst.Columns.Add("TagTime", typeof(System.DateTime));
dtRst.Columns.Add("Module_id", typeof(System.Int32));
dtRst.Columns.Add("Fun_id", typeof(System.Int32));
dtRst.Columns.Add("ModuleAddr", typeof(System.String));
dtRst.Columns.Add("ModuleName", typeof(System.String));
dtRst.Columns.Add("MeterNo", typeof(System.String));
dtRst.Columns.Add("LastVal", typeof(System.Decimal));
dtRst.Columns.Add("FunName", typeof(System.String));
dtRst.Columns.Add("CoName", typeof(System.String));
dtRst.PrimaryKey = new DataColumn[] { dtRst.Columns["TagTime"], dtRst.Columns["Module_id"], dtRst.Columns["Fun_id"] };
DataTable dtSource = dal.GetEnergyHisdata(co_id, moduleName, funType);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitMdQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["Module_id"]), ",")))
{
if (!string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(",");
}
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
}
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
DateTime ucStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1));
long ucUnit = 10000000;
#region 获取历史数据
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), dtFirst, dtLast, "hour", splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
//
DateTime tagtime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
//int interval = CommFunc.ConvertDBNullToInt32(dr["Interval"]);
string dataCfg = CommFunc.ConvertDBNullToString(dr["DataCfg"]);
dataCfg = string.IsNullOrEmpty(dataCfg) ? "0:0" : dataCfg;
foreach (string s in dataCfg.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
string[] val = s.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
if (val.Length != 2)
{
continue;
}
long ticks = CommFunc.ConvertDBNullToLong(val[0]);
decimal value = CommFunc.ConvertDBNullToDecimal(val[1]);
if (CommFunc.ConvertDBNullToString(curDr["FunType"]).Equals(V0Fun.E.ToString()) || CommFunc.ConvertDBNullToString(curDr["FunType"]).Equals(V0Fun.EAnt.ToString()) || CommFunc.ConvertDBNullToInt32(curDr["IsChrg"]) == 1)
{
value = value + firstVal;
}
long ts = ticks + ((tagtime - ucStart).Ticks / 10000);
TimeSpan toNow = new TimeSpan(ticks * ucUnit + ((tagtime - ucStart).Ticks));
DateTime tickTime = ucStart.Add(toNow); //tagtime.AddTicks(ticks);
DataRow addDr = dtRst.Rows.Find(new object[] { tickTime, dr["Module_id"], dr["Fun_id"] });
if (addDr == null)
{
addDr = dtRst.NewRow();
addDr["TagTime"] = tickTime;
addDr["Module_id"] = dr["Module_id"];
addDr["Fun_id"] = dr["Fun_id"];
addDr["ModuleAddr"] = curDr["ModuleAddr"];
addDr["ModuleName"] = curDr["ModuleName"];
addDr["FunName"] = curDr["FunName"];
addDr["CoName"] = curDr["CoName"];
addDr["MeterNo"] = curDr["MeterNo"];
dtRst.Rows.Add(addDr);
}
addDr["LastVal"] = value.ToString("f" + scale);
}
}
#endregion
return(dtRst);
}
public object GetEnergy(int co_id, string dataType, DateTime dataTime)
{
DataTable dtSource = dal.GetEnergy(co_id);
dtSource.PrimaryKey = new DataColumn[] { dtSource.Columns["Module_id"], dtSource.Columns["Fun_id"] };
StringBuilder splitMdQuery = new StringBuilder();
StringBuilder splitTyQuery = new StringBuilder();
foreach (DataRow dr in dtSource.Rows)
{
if (string.IsNullOrEmpty(splitMdQuery.ToString()))
{
splitMdQuery.Append(CommFunc.ConvertDBNullToString(dr["Module_id"]));
}
if (!System.Text.RegularExpressions.Regex.IsMatch(string.Format("{0}{1}{2}", ",", splitTyQuery.ToString(), ","), string.Format("{0}{1}{2}", ",", CommFunc.ConvertDBNullToString(dr["FunType"]), ",")))
{
if (!string.IsNullOrEmpty(splitTyQuery.ToString()))
{
splitTyQuery.Append(",");
}
splitTyQuery.Append(CommFunc.ConvertDBNullToString(dr["FunType"]));
}
}
string ddType = dataType;
DateTime start = dataTime, end = dataTime;
int count = 0, cc = 0;
if (dataType.ToLower().Equals("day"))
{
ddType = "hour";
start = new DateTime(dataTime.Year, dataTime.Month, dataTime.Day);
if (start == new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day))
{
end = new DateTime(dataTime.Year, dataTime.Month, dataTime.Day, DateTime.Now.Hour, 59, 59).AddHours(-1);
}
else
{
end = new DateTime(dataTime.Year, dataTime.Month, dataTime.Day, 23, 59, 59);
}
cc = end.Hour + 1;
count = 24;
}
else if (dataType.ToLower().Equals("month"))
{
ddType = "day";
start = new DateTime(dataTime.Year, dataTime.Month, 1);
if (start == new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1))
{
end = new DateTime(dataTime.Year, dataTime.Month, DateTime.Now.Day);
}
else
{
end = start.AddMonths(1).AddDays(-1);
}
cc = end.Day;
count = start.AddMonths(1).AddDays(-1).Day;
}
else if (dataType.ToLower().Equals("year"))
{
ddType = "month";
start = new DateTime(dataTime.Year, 1, 1);
if (start == new DateTime(DateTime.Now.Year, 1, 1))
{
end = new DateTime(dataTime.Year, DateTime.Now.Month, 1);
}
else
{
end = start.AddMonths(1).AddDays(-1);
}
cc = end.Month;
count = 12;
}
//增加
Dictionary <string, List <decimal> > dic = new Dictionary <string, List <decimal> >();
foreach (var ss in splitTyQuery.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
int cin = cc;
List <decimal> dd = new List <decimal>();
while (--cin >= 0)
{
dd.Add(0);
}
dic.Add(ss, dd);
}
//
decimal allUse = 0;
DataTable dtUse = WholeBLL.GetCoreQueryData(this.Ledger, splitMdQuery.ToString(), start, end, ddType, splitTyQuery.ToString());
foreach (DataRow dr in dtUse.Rows)
{
DataRow curDr = dtSource.Rows.Find(new object[] { dr["Module_id"], dr["Fun_id"] });
if (curDr == null)
{
continue;
}
int scale = CommFunc.ConvertDBNullToInt32(curDr["Scale"]);
decimal multiply = CommFunc.ConvertDBNullToDecimal(curDr["Multiply"]);
scale = scale == 0 ? 2 : scale;
DateTime tagTime = CommFunc.ConvertDBNullToDateTime(dr["TagTime"]);
decimal firstVal = CommFunc.ConvertDBNullToDecimal(dr["FirstVal"]);
decimal lastVal = CommFunc.ConvertDBNullToDecimal(dr["LastVal"]);
decimal useVal = lastVal - firstVal;
useVal = Math.Round(useVal * multiply, scale, MidpointRounding.AwayFromZero);
List <decimal> dd = null;
if (dic.TryGetValue(CommFunc.ConvertDBNullToString(curDr["FunType"]), out dd) == false)
{
continue;
}
//
if (ddType.Equals("hour"))
{
dd[tagTime.Hour] = CommFunc.ConvertDBNullToDecimal(dd[tagTime.Hour]) + useVal;
}
else if (ddType.Equals("day"))
{
dd[tagTime.Day - 1] = CommFunc.ConvertDBNullToDecimal(dd[tagTime.Day - 1]) + useVal;
}
else if (ddType.Equals("month"))
{
dd[tagTime.Month - 1] = CommFunc.ConvertDBNullToDecimal(dd[tagTime.Month - 1]) + useVal;
}
allUse = allUse + useVal;
}
//object ss;
List <object> rr = new List <object>();
foreach (var pk in dic)
{
rr.Add(new { name = pk.Key, list = pk.Value });
}
return(new { data = rr, count = count });
}
