using LeaRun.Application.Entity.WaterWellManage;
using LeaRun.Data;
using LeaRun.Util;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LeaRun.Application.Service.WaterWellManage
{
///
/// 水源井日报表
///
public class DayReportService
{
SqlHelper sqlHelper = new SqlHelper("WaterWellDb");
#region 查询一日数据
///
/// 查询一日数据
///
///
///
///
public List GetDayReport(string waterWellId, string date)
{
if(string.IsNullOrEmpty(waterWellId.Trim())) {
return new List();
}
try
{
StringBuilder sb = new StringBuilder(@"
select
WaterWellId,
Convert(Char(10), GetDateTime ,23) Day,
CONVERT(varchar(20), datepart(HH,getDateTime), 0) + '时' Time,
Convert(decimal(18,2), MAX(VoltageA)) MaxVoltageA ,
Convert(decimal(18,2), MIN(VoltageA)) MinVoltageA,
Convert(decimal(18,2), AVG(VoltageA)) AvgVoltageA,
Convert(decimal(18,2), MAX(VoltageB)) MaxVoltageB,
Convert(decimal(18,2), MIN(VoltageB)) MinVoltageB,
Convert(decimal(18,2), AVG(VoltageB)) AvgVoltageB,
Convert(decimal(18,2), MAX(VoltageC)) MaxVoltageC,
Convert(decimal(18,2), MIN(VoltageC)) MinVoltageC,
Convert(decimal(18,2), AVG(VoltageC)) AvgVoltageC,
Convert(decimal(18,2), MAX(CurrentA)) MaxCurrentA,
Convert(decimal(18,2), MIN(CurrentA)) MinCurrentA,
Convert(decimal(18,2), AVG(CurrentA)) AvgCurrentA,
Convert(decimal(18,2), MAX(CurrentB)) MaxCurrentB,
Convert(decimal(18,2), MIN(CurrentB)) MinCurrentB,
Convert(decimal(18,2), AVG(CurrentB)) AvgCurrentB,
Convert(decimal(18,2), MAX(CurrentC)) MaxCurrentC,
Convert(decimal(18,2), MIN(CurrentC)) MinCurrentC,
Convert(decimal(18,2), AVG(CurrentC)) AvgCurrentC,
Convert(decimal(18,2), MAX(ActiveElectricalEnergy)) MaxActiveElectricalEnergy,
Convert(decimal(18,2), MIN(ActiveElectricalEnergy)) MinActiveElectricalEnergy,
Convert(decimal(18,2), MAX(ActiveElectricalEnergy) - MIN(ActiveElectricalEnergy)) IncrementActiveElectricalEnergy,
Convert(decimal(18,2), MAX(NetAccumulatedCurrent)) MaxNetAccumulatedCurrent,
Convert(decimal(18,2), MIN(NetAccumulatedCurrent)) MinNetAccumulatedCurrent,
Convert(decimal(18,2), MAX(NetAccumulatedCurrent) - MIN(NetAccumulatedCurrent)) IncrementNetAccumulatedCurrent,
Convert(decimal(18,2), MAX(Pressure)) MaxPressure,
Convert(decimal(18,2), MIN(Pressure)) MinPressure,
Convert(decimal(18,2), AVG(Pressure)) AvgPressure,
Convert(decimal(18,2), MAX(InstantaneousFlow)) MaxInstantaneousFlow,
Convert(decimal(18,2), MIN(InstantaneousFlow)) MinInstantaneousFlow,
Convert(decimal(18,2), AVG(InstantaneousFlow)) AvgInstantaneousFlow,
Convert(decimal(18,2), MAX(Turbidity)) MaxTurbidity,
Convert(decimal(18,2), MIN(Turbidity)) MinTurbidity,
Convert(decimal(18,2), AVG(Turbidity)) AvgTurbidity,
Convert(decimal(18,2), MAX(Temperature)) MaxTemperature,
Convert(decimal(18,2), MIN(Temperature)) MinTemperature,
Convert(decimal(18,2), AVG(Temperature)) AvgTemperature,
Convert(decimal(18,2), MAX(HeightOfLiquidLevel)) MaxHeightOfLiquidLevel,
Convert(decimal(18,2), MIN(HeightOfLiquidLevel)) MinHeightOfLiquidLevel,
Convert(decimal(18,2), AVG(HeightOfLiquidLevel)) AvgHeightOfLiquidLevel,
Convert(decimal(18,2), MAX(Frequency)) MaxFrequency,
Convert(decimal(18,2), MIN(Frequency)) MinFrequency,
Convert(decimal(18,2), AVG(Frequency)) AvgFrequency
from ");
sb.Append("WaterWell_" + waterWellId);
sb.Append(" where Convert(Char(10), GetDateTime ,23) = '" + date + "' ");
sb.Append(@"
group by datepart(HH,getDateTime), WaterWellId, Convert(Char(10), GetDateTime ,23)
order by datepart(HH,getDateTime) asc ");
DataTable dt = sqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, null);
if (dt.Rows.Count == 0)
{
return AnalysisData(new List());
}
else
{
List list = DataHelper.DataTableToT(dt);
return AnalysisData(list);
}
}
catch (Exception)
{
throw;
}
}
#endregion
#region 格式化数据
///
/// 格式化数据
///
///
///
private List AnalysisData(List list)
{
List listArr = new List();
for (int i = 0; i < 24; i++)
{
bool isAdd = false;
for (int j = 0; j < list.Count; j++)
{
if (list[j].Time.Equals(i + "时"))
{
listArr.Add(list[j]);
isAdd = true;
}
}
if (!isAdd)
{
DayReportEntity dayReportEntity = new DayReportEntity();
dayReportEntity.Time = i + "时";
dayReportEntity.MaxVoltageA = "--";
dayReportEntity.MinVoltageA = "--";
dayReportEntity.AvgVoltageA = "--";
dayReportEntity.MaxVoltageB = "--";
dayReportEntity.MinVoltageB = "--";
dayReportEntity.AvgVoltageB = "--";
dayReportEntity.MaxVoltageC = "--";
dayReportEntity.MinVoltageC = "--";
dayReportEntity.AvgVoltageC = "--";
dayReportEntity.MaxCurrentA = "--";
dayReportEntity.MinCurrentA = "--";
dayReportEntity.AvgCurrentA = "--";
dayReportEntity.MaxCurrentB = "--";
dayReportEntity.MinCurrentB = "--";
dayReportEntity.AvgCurrentB = "--";
dayReportEntity.MaxCurrentC = "--";
dayReportEntity.MinCurrentC = "--";
dayReportEntity.AvgCurrentC = "--";
dayReportEntity.MaxActiveElectricalEnergy = "--";
dayReportEntity.MinActiveElectricalEnergy = "--";
dayReportEntity.IncrementActiveElectricalEnergy = "--";
dayReportEntity.MaxNetAccumulatedCurrent = "--";
dayReportEntity.MinNetAccumulatedCurrent = "--";
dayReportEntity.IncrementNetAccumulatedCurrent = "--";
dayReportEntity.MaxPressure = "--";
dayReportEntity.MinPressure = "--";
dayReportEntity.AvgPressure = "--";
dayReportEntity.MaxInstantaneousFlow = "--";
dayReportEntity.MinInstantaneousFlow = "--";
dayReportEntity.AvgInstantaneousFlow = "--";
dayReportEntity.MaxTurbidity = "--";
dayReportEntity.MinTurbidity = "--";
dayReportEntity.AvgTurbidity = "--";
dayReportEntity.MaxTemperature = "--";
dayReportEntity.MinTemperature = "--";
dayReportEntity.AvgTemperature = "--";
dayReportEntity.MaxHeightOfLiquidLevel = "--";
dayReportEntity.MinHeightOfLiquidLevel = "--";
dayReportEntity.AvgHeightOfLiquidLevel = "--";
dayReportEntity.MaxFrequency = "--";
dayReportEntity.MinFrequency = "--";
dayReportEntity.AvgFrequency = "--";
listArr.Add(dayReportEntity);
}
}
return listArr;
}
#endregion
#region 获取子数据
///
/// 获取子数据
///
///
///
///
///
public List GetSubDayReport(string time, string day, string waterWellId)
{
if (string.IsNullOrEmpty(time.Trim()) || string.IsNullOrEmpty(day.Trim()) || string.IsNullOrEmpty(waterWellId.Trim()))
{
return new List();
}
StringBuilder sb = new StringBuilder("select * from " + "WaterWell_" + waterWellId);
sb.Append(" where Convert(Char(10), GetDateTime ,23) = '" + day + "' and ");
sb.Append(" datepart(HH,getDateTime) = " + time.Split('时')[0]);
try
{
DataTable dt = sqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, null);
if (dt.Rows.Count == 0)
{
return new List();
}
else
{
List list = DataHelper.DataTableToT(dt);
return list;
}
}
catch (Exception)
{
throw;
}
}
#endregion
}
}