NBDXSY/LeaRun.Application/LeaRun.Application.Service/WaterWellManage/DayReportService.cs

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
    }
}