123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244 |
- 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
- {
- /// <summary>
- /// 水源井日报表
- /// </summary>
- public class DayReportService
- {
- SqlHelper sqlHelper = new SqlHelper("WaterWellDb");
- #region 查询一日数据
- /// <summary>
- /// 查询一日数据
- /// </summary>
- /// <param name="waterWellId"></param>
- /// <param name="date"></param>
- /// <returns></returns>
- public List<DayReportEntity> GetDayReport(string waterWellId, string date)
- {
- if(string.IsNullOrEmpty(waterWellId.Trim())) {
- return new List<DayReportEntity>();
- }
- 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<DayReportEntity>());
- }
- else
- {
- List<DayReportEntity> list = DataHelper.DataTableToT<DayReportEntity>(dt);
- return AnalysisData(list);
- }
- }
- catch (Exception)
- {
- throw;
- }
- }
- #endregion
- #region 格式化数据
- /// <summary>
- /// 格式化数据
- /// </summary>
- /// <param name="list"></param>
- /// <returns></returns>
- private List<DayReportEntity> AnalysisData(List<DayReportEntity> list)
- {
- List<DayReportEntity> listArr = new List<DayReportEntity>();
- 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 获取子数据
- /// <summary>
- /// 获取子数据
- /// </summary>
- /// <param name="time"></param>
- /// <param name="day"></param>
- /// <param name="waterWellId"></param>
- /// <returns></returns>
- public List<ReportSubEntity> GetSubDayReport(string time, string day, string waterWellId)
- {
- if (string.IsNullOrEmpty(time.Trim()) || string.IsNullOrEmpty(day.Trim()) || string.IsNullOrEmpty(waterWellId.Trim()))
- {
- return new List<ReportSubEntity>();
- }
- 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<ReportSubEntity>();
- }
- else
- {
- List<ReportSubEntity> list = DataHelper.DataTableToT<ReportSubEntity>(dt);
- return list;
- }
- }
- catch (Exception)
- {
- throw;
- }
- }
-
- #endregion
- }
- }
|