NBDXSY/LeaRun.Application/LeaRun.Application.Service/PipeNetworkManage/MeterService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LeaRun.Application.IService.PipeNetworkManage;
using System.Data;
using LeaRun.Data;
using LeaRun.Application.Entity.PipeNetworkManage;
using System.Data;
using LeaRun.Util;
using Newtonsoft.Json;
using LeaRun.Util.WebControl;
using LeaRun.Util.Extension;
using LeaRun.Util.Log;
using LeaRun.Application.Service.DMAManage;
namespace LeaRun.Application.Service.PipeNetworkManage
{
    public class MeterService : IMeterService
    {
        SqlHelper sqlHelper = new SqlHelper("PipeNetworkDb");
        DifferenceAnalysisService differenceAnalysisService = new DifferenceAnalysisService();
        Log log = LogFactory.GetLogger(typeof(MeterService));

        #region 表具类型-service
        /// <summary>
        /// 获取所有的 考核表 类型
        /// </summary>
        /// <returns></returns>
        public string GetMeterType()
        {
            try
            {
                string strSql = "SELECT MeterAssessmentType, TypeName, MeterType, Caliber, Q1, Q2, Q3, Q4, [Range], Manufacturer, PressureLoss, ManufacturerPhone, Memo  FROM MeterType ";

                DataTable dtResult = sqlHelper.ExecuteDataTable(strSql, CommandType.Text, null);

                StringBuilder returnJson = new StringBuilder();
                for (int row = 0; row < dtResult.Rows.Count; row++)
                {
                    returnJson.Append("{");
                    returnJson.Append("\"ID\":\"").Append(dtResult.Rows[row]["MeterAssessmentType"].ToString()).Append("\",");
                    returnJson.Append("\"类型名称\":\"").Append(dtResult.Rows[row]["TypeName"].ToString()).Append("\",");
                    returnJson.Append("\"类型Value\":\"").Append(dtResult.Rows[row]["MeterType"].ToString()).Append("\",");
                    returnJson.Append("\"类型\":\"").Append(GetMeterType(dtResult.Rows[row]["MeterType"].ToString())).Append("\",");
                    returnJson.Append("\"口径\":\"").Append("DN" + dtResult.Rows[row]["Caliber"].ToString()).Append("\",");
                    returnJson.Append("\"起始流量\":\"").Append(dtResult.Rows[row]["Q1"].ToString()).Append("\",");
                    returnJson.Append("\"分界流量\":\"").Append(dtResult.Rows[row]["Q2"].ToString()).Append("\",");
                    returnJson.Append("\"常用流量\":\"").Append(dtResult.Rows[row]["Q3"].ToString()).Append("\",");
                    returnJson.Append("\"过载流量\":\"").Append(dtResult.Rows[row]["Q4"].ToString()).Append("\",");
                    returnJson.Append("\"量程\":\"").Append(dtResult.Rows[row]["Range"].ToString()).Append("\",");
                    returnJson.Append("\"压力损失\":\"").Append(dtResult.Rows[row]["PressureLoss"].ToString()).Append("\",");
                    returnJson.Append("\"生产厂家\":\"").Append(dtResult.Rows[row]["Manufacturer"].ToString()).Append("\",");
                    returnJson.Append("\"厂家电话\":\"").Append(dtResult.Rows[row]["ManufacturerPhone"].ToString()).Append("\",");
                    returnJson.Append("\"备注\":\"").Append(dtResult.Rows[row]["Memo"].ToString()).Append("\"");
                    returnJson.Append("}");
                    returnJson.Append(",");
                }

                string returnData = returnJson.ToString();

                return "[" + returnData.TrimEnd(',') + "]";
            }
            catch (Exception ex)
            {
                return "";
            }
        }
        /// <summary>
        ///  获取指定的 表类型列表
        /// </summary>
        /// <param name="pagination">分页参数</param>
        /// <param name="queryJson">查询条件</param>
        /// <returns></returns>
        public IEnumerable<MeterTypeEntity> GetMeterType(Pagination pagination, string queryJson)
        {
            try
            {
                string strSql = "SELECT *  FROM MeterType ";

                var queryParam = queryJson.ToJObject();

                if (!queryParam["keyword"].IsEmpty())
                {
                    string keyord = queryParam["keyword"].ToString();

                    strSql += " WHERE TypeName LIKE '%" + keyord + "%'";
                }

                int total = pagination.records;

                DataTable dtResult = sqlHelper.ExecuteDataTable(strSql, "MeterAssessmentType", true, pagination.rows, pagination.page, out total, null);

                pagination.records = total;
                #region 废弃
                //DataTable dtResult = sqlHelper.ExecuteDataTable(strSql, CommandType.Text, null);

                //StringBuilder returnJson = new StringBuilder();
                //for (int row = 0; row < dtResult.Rows.Count; row++)
                //{
                //    returnJson.Append("{");
                //    returnJson.Append("\"ID\":\"").Append(dtResult.Rows[row]["MeterAssessmentType"].ToString()).Append("\",");
                //    returnJson.Append("\"类型名称\":\"").Append(dtResult.Rows[row]["TypeName"].ToString()).Append("\",");
                //    returnJson.Append("\"类型Value\":\"").Append(dtResult.Rows[row]["MeterType"].ToString()).Append("\",");
                //    returnJson.Append("\"类型\":\"").Append(GetMeterType(dtResult.Rows[row]["MeterType"].ToString())).Append("\",");
                //    returnJson.Append("\"口径\":\"").Append("DN" + dtResult.Rows[row]["Caliber"].ToString()).Append("\",");
                //    returnJson.Append("\"起始流量\":\"").Append(dtResult.Rows[row]["Q1"].ToString()).Append("\",");
                //    returnJson.Append("\"分界流量\":\"").Append(dtResult.Rows[row]["Q2"].ToString()).Append("\",");
                //    returnJson.Append("\"常用流量\":\"").Append(dtResult.Rows[row]["Q3"].ToString()).Append("\",");
                //    returnJson.Append("\"过载流量\":\"").Append(dtResult.Rows[row]["Q4"].ToString()).Append("\",");
                //    returnJson.Append("\"量程\":\"").Append(dtResult.Rows[row]["Range"].ToString()).Append("\",");
                //    returnJson.Append("\"压力损失\":\"").Append(dtResult.Rows[row]["PressureLoss"].ToString()).Append("\",");
                //    returnJson.Append("\"生产厂家\":\"").Append(dtResult.Rows[row]["Manufacturer"].ToString()).Append("\",");
                //    returnJson.Append("\"厂家电话\":\"").Append(dtResult.Rows[row]["ManufacturerPhone"].ToString()).Append("\",");
                //    returnJson.Append("\"备注\":\"").Append(dtResult.Rows[row]["Memo"].ToString()).Append("\"");
                //    returnJson.Append("}");
                //    returnJson.Append(",");
                //}

                //string returnData = returnJson.ToString();

                //return "[" + returnData.TrimEnd(',') + "]";
                #endregion

                return DataHelper.DataTableToT<MeterTypeEntity>(dtResult);

            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                return null;

            }
        }

        /// <summary>
        /// 获取类型列表
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MeterTypeEntity> GetMeterTypeList()
        {
            try
            {
                string strSql = "SELECT *  FROM MeterType ";

                DataTable dtResult = sqlHelper.ExecuteDataTable(strSql, CommandType.Text, null);

                return DataHelper.DataTableToT<MeterTypeEntity>(dtResult);
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 获取表具类型
        /// </summary>
        /// <param name="meterTypeId"></param>
        /// <returns></returns>
        public MeterTypeEntity GetMeterTypeEntity(int meterTypeId)
        {
            try
            {
                string selSql = "SELECT * FROM MeterType WHERE MeterAssessmentType = " + meterTypeId;

                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);

                List<MeterTypeEntity> lists = DataHelper.DataTableToT<MeterTypeEntity>(dtResult);

                return lists[0];

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 根据数据库保存的值，获取类型名称
        /// </summary>
        /// <param name="meterType">类型值</param>
        /// <returns>类型名</returns>
        private string GetMeterType(string meterType)
        {
            switch (meterType)
            {
                case "0":
                    return "水平旋翼式水表";
                case "1":
                    return "水平螺翼式水表";
                case "2":
                    return "垂直螺翼式水表";
                case "3":
                    return "超声波水表";
                case "4":
                    return "电磁流量计";
                default:
                    return "";
            }
        }

        /// <summary>
        /// 保存表具类型
        /// </summary>
        /// <param name="meterType">类型实体</param>
        /// <returns></returns>
        public bool SaveMeterType(MeterTypeEntity meterType)
        {
            try
            {
                //string insertSql = "INSERT INTO MeterType(TypeName,	MeterType, Caliber,	Q1,	Q2,	Q3,	Q4,	[Range], PressureLoss, Manufacturer, ManufacturerPhone,	Memo)Values('" +
                //                    meterType.TypeName + "'," + meterType.MeterType + "," + meterType.Caliber + "," + meterType.Q1 + "," + meterType.Q2 + "," + meterType.Q3 + "," + meterType.Q4 + "," +
                //                    meterType.Range + "," + meterType.PressureLoss + ",'" + meterType.Manufacturer + "','" + meterType.ManufacturerPhone + "','" + meterType.Memo + "')";

                string insertSql = LeaRun.Util.DataHelper.ModelToInsertSql<MeterTypeEntity>(meterType, "MeterType");

                return sqlHelper.ExecuteNoParams(insertSql, CommandType.Text) == 1 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 修改表具类型
        /// </summary>
        /// <param name="meterType">类型实体</param>
        /// <returns></returns>
        public bool UpdateMeterType(MeterTypeEntity meterType)
        {
            try
            {
                //string insertSql = "UPDATE MeterType SET TypeName = '" + meterType.TypeName + "', MeterType= " + meterType.MeterType + ", Caliber= " + meterType.Caliber +
                //                    ", Caliber= " + meterType.Caliber + ", Q1= " + meterType.Q1 + ", Q2= " + meterType.Q2 + ", Q3= " + meterType.Q3 + ", Q4= " + meterType.Q4 +
                //                    ", [Range]= " + meterType.Range + ", PressureLoss= " + meterType.PressureLoss + ", Manufacturer= '" + meterType.Manufacturer + "'," +
                //                    "ManufacturerPhone= '" + meterType.ManufacturerPhone + "'," + "Memo= '" + meterType.Memo + "' WHERE MeterAssessmentType=" + meterType.MeterAssessmentType;

                string updateSql = DataHelper.ModelToUpdateSql<MeterTypeEntity>(meterType, "MeterType");

                return sqlHelper.ExecuteNoParams(updateSql, CommandType.Text) == 1 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 删除表数据类型
        /// </summary>
        /// <param name="meterAssessmentType"></param>
        /// <returns></returns>
        public string DelteMemterType(int meterAssessmentType)
        {
            try
            {
                //删除前判断 考核表有数据 则提示不能删除

                string selSql = "SELECT * FROM MeterAssessmentBase WHERE MeterTypeId = " + meterAssessmentType;

                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);

                if (dtResult.Rows.Count > 0)
                {
                    return "此类型下有表数据，不能删除！";
                }

                string delSql = "DELETE FROM MeterType WHERE MeterAssessmentType=" + meterAssessmentType;

                return sqlHelper.ExecuteNoParams(delSql, CommandType.Text) > 0 ? "删除成功" : "删除失败";

            }
            catch (Exception ex)
            {
                return "删除失败";
            }
        }

        #endregion

        #region 表设备信息-service

        /// <summary>
        /// 获取表设备信息列表
        /// </summary>
        /// <returns></returns>
        public string GetMeterList(Pagination pagination, string queryJson)
        {

            string selectSql = "SELECT * FROM dbo.MeterAssessmentBase WHERE DeleteMark=0";//未删除

            int total = pagination.records;

            DataTable dtResult = sqlHelper.ExecuteDataTable(selectSql, "MeterAssessmentId", true, pagination.rows, pagination.page, out total, null);

            pagination.records = total;

            //DataTable dtResult = sqlHelper.ExecuteDataTable(selectSql, CommandType.Text, null);

            dtResult.Columns.Add("MeterTypeName", System.Type.GetType("System.String"));

            // 数据格式化
            for (int row = 0; row < dtResult.Rows.Count; row++)
            {
                int meterTypeId = int.Parse(dtResult.Rows[row]["MeterTypeId"].ToString());
                dtResult.Rows[row]["MeterTypeName"] = GetMeterTypeNameById(meterTypeId);
            }

            return JsonConvert.SerializeObject(dtResult);
        }

        /// <summary>
        /// 获取表设备信息列表
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MeterEntity> GetMeters()
        {
            try
            {
                string selectSql = "SELECT * FROM dbo.MeterAssessmentBase WHERE DeleteMark=0";//未删除

                DataTable dtResult = sqlHelper.ExecuteDataTable(selectSql, CommandType.Text, null);

                return DataHelper.DataTableToT<MeterEntity>(dtResult);
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 获取表具类型名称
        /// </summary>
        /// <returns></returns>
        public string GetMeterTypeNameById(int meterTypeId)
        {
            try
            {
                string selectSql = "SELECT TypeName FROM MeterType WHERE MeterAssessmentType= " + meterTypeId;

                DataTable dtResult = sqlHelper.ExecuteDataTable(selectSql, CommandType.Text);

                return dtResult.Rows[0][0].ToString();
            }
            catch (Exception ex)
            {
                return "";
            }
        }

        /// <summary>
        /// 保存表设备信息
        /// </summary>
        /// <param name="meter">表设备实体</param>
        /// <returns></returns>
        public bool SaveMeter(MeterEntity meter)
        {
            try
            {
                //string insertSql = DataHelper.ModelToInsertSql<MeterEntity>(meter, "MeterAssessmentBase");

                string insertSql = "INSERT INTO MeterAssessmentBase(MeterAssessmentName,MeterTypeId,ManufacturerCode,MeterAssessmentCode,CompanyId,LngAndLat,IsKaoHe)VALUES('"
                                    + meter.MeterAssessmentName + "'," + meter.MeterTypeId + ",'" + meter.ManufacturerCode + "','" + meter.MeterAssessmentCode + "','" + meter.CompanyId + "','" + meter.LngAndLat + "', " + meter.IsKaoHe + ")";


                return sqlHelper.ExecuteNoParams(insertSql, CommandType.Text) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 更新表设备信息
        /// </summary>
        /// <param name="meter">表设备实体</param>
        /// <returns></returns>
        public bool UpdateMeter(MeterEntity meter)
        {
            try
            {
                //string updateSql = DataHelper.ModelToUpdateSql<MeterEntity>(meter, "MeterAssessmentBase");

                string updateSql = "UPDATE MeterAssessmentBase SET	MeterAssessmentName ='" + meter.MeterAssessmentName
                                    + "' , MeterTypeId=" + meter.MeterTypeId
                                    + " , ManufacturerCode='" + meter.ManufacturerCode
                                    + "' , LngAndLat='" + meter.LngAndLat
                                    + "' , MeterAssessmentCode='" + meter.MeterAssessmentCode + "' ,IsKaoHe = " + meter.IsKaoHe + " WHERE  MeterAssessmentId = " + meter.MeterAssessmentId;

                return sqlHelper.ExecuteNoParams(updateSql, CommandType.Text) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// 删除表设备信息
        /// </summary>
        /// <param name="MeterAssessmentId">设备信息ID</param>
        /// <returns></returns>
        public bool DeleteMeter(int meterAssessmentId)
        {
            //标记位更新
            try
            {
                string deleteSql = "UPDATE MeterAssessmentBase SET DeleteMark=1 WHERE MeterAssessmentId=" + meterAssessmentId;

                return sqlHelper.ExecuteNoParams(deleteSql, CommandType.Text) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 获取表设备信息
        /// </summary>
        /// <param name="meterAssessmentId"></param>
        /// <returns></returns>
        public MeterEntity GetMeterEntity(int meterAssessmentId)
        {
            try
            {
                string selSql = "SELECT * FROM MeterAssessmentBase WHERE MeterAssessmentId = " + meterAssessmentId;

                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);

                List<MeterEntity> lists = DataHelper.DataTableToT<MeterEntity>(dtResult);

                return lists[0];

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        #endregion

        #region  水务宝 用到的方法

        /// <summary>
        /// 获取所属公司的表
        /// </summary>
        /// <param name="companyId">公司ID</param>
        /// <param name="isKaoHe">1.考核表  0.贸易表  2.无所谓</param>
        /// <returns></returns>
        public IEnumerable<MeterEntity> GetMeterEntityByCompanyId(string companyId, int isKaoHe = 2)
        {
            try
            {
                string selSql = "SELECT * FROM MeterAssessmentBase WHERE CompanyId = '" + companyId + "'";

                if (isKaoHe != 2)
                {
                    selSql += " AND IsKaoHe = " + isKaoHe;
                }

                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);

                List<MeterEntity> lists = DataHelper.DataTableToT<MeterEntity>(dtResult);

                return lists;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 特定时间段内，平均压力小于某个值的 设备
        /// </summary>
        /// <param name="pressValue"></param>
        /// <param name="startT"></param>
        /// <param name="endT"></param>
        /// <returns></returns>
        public IEnumerable<MeterEntity> GetMeterByPressLessValue(decimal pressValue, string startT, string endT, IEnumerable<MeterEntity> entities)
        {
            try
            {
                List<MeterEntity> returnEntity = new List<MeterEntity>();
                foreach (var item in entities)
                {
                    string tableName = item.TableName;

                    //取历史记录
                    string selSql = "SELECT avg(Pressure) as avgPressure FROM " + tableName + " WHERE GetDateTime BETWEEN '" + startT + "' AND '" + endT + "' AND  Pressure IS NOT NULL AND Pressure <> 0 ";

                    DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                    if (dtResult.Rows.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(dtResult.Rows[0]["avgPressure"].ToString()))
                        {
                            decimal pressure = decimal.Parse(dtResult.Rows[0]["avgPressure"].ToString());
                            if (pressure <= pressValue)
                            {
                                returnEntity.Add(item);
                            }
                        }
                    }
                }
                return returnEntity;

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 特定时间段内，最小流量小于某个值的 设备
        /// </summary>
        /// <param name="pressValue"></param>
        /// <param name="startT"></param>
        /// <param name="endT"></param>
        /// <returns></returns>
        public IEnumerable<MeterEntity> GetMeterByMinFlowLessValue(decimal flowValue, string startT, string endT, IEnumerable<MeterEntity> entities)
        {
            try
            {
                List<MeterEntity> returnEntity = new List<MeterEntity>();
                foreach (var item in entities)
                {
                    string tableName = item.TableName;

                    //取历史记录
                    string selSql = "SELECT min(InstantaneousFlow) as minInstantaneousFlow FROM " + tableName + " WHERE GetDateTime BETWEEN '" + startT + "' AND '" + endT + "' AND  InstantaneousFlow IS NOT NULL AND InstantaneousFlow <> 0 ";

                    DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                    if (dtResult.Rows.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(dtResult.Rows[0]["minInstantaneousFlow"].ToString()))
                        {
                            decimal minFlow = decimal.Parse(dtResult.Rows[0]["minInstantaneousFlow"].ToString());
                            if (minFlow <= flowValue)
                            {
                                returnEntity.Add(item);
                            }
                        }
                    }
                }
                return returnEntity;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 特定时间段内，电池电压小于某个值的 设备
        /// </summary>
        /// <param name="voltageValue"></param>
        /// <param name="startT"></param>
        /// <param name="endT"></param>
        /// <returns></returns>
        public IEnumerable<MeterEntity> GetMeterByBatteryVoltageValue(decimal voltageValue, IEnumerable<MeterEntity> entities)
        {
            try
            {
                List<MeterEntity> returnEntity = new List<MeterEntity>();
                foreach (var item in entities)
                {
                    if (item.BatteryVoltageValue!=null)
                    {
                        decimal batteryVoltageValue = (decimal)item.BatteryVoltageValue;
                        if (batteryVoltageValue <= voltageValue)
                        {
                            returnEntity.Add(item);
                        }
                    }
                }
                return returnEntity;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 根据口径筛选设备
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="caliberValue">口径值</param>
        /// <param name="chooseType">1.大于 2.等于 3小于</param>
        /// <returns></returns>
        public IEnumerable<MeterEntity> GetMeterEntityByCaliber(IEnumerable<MeterEntity> entities, int caliberValue, int chooseType)
        {
            try
            {
                List<MeterEntity> returnEntity = new List<MeterEntity>();
                foreach (var item in entities)
                {
                    int meterTypeId = item.MeterTypeId;

                    //取表设备类型信息
                    string selSql = " SELECT * FROM MeterType WHERE MeterAssessmentType = " + meterTypeId;

                    DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                    if (dtResult.Rows.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(dtResult.Rows[0]["Caliber"].ToString()))
                        {
                            int Caliber = int.Parse(dtResult.Rows[0]["Caliber"].ToString());

                            switch (chooseType)
                            {
                                case 1:
                                    if (Caliber > caliberValue) { returnEntity.Add(item); }
                                    break;
                                case 2:
                                    if (Caliber == caliberValue) { returnEntity.Add(item); }
                                    break;
                                case 3:
                                    if (Caliber < caliberValue) { returnEntity.Add(item); }
                                    break;
                            }
                        }
                    }
                }
                return returnEntity;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 获取有故障的设备
        /// 瞬时流量 压力 净累计都为0 视为故障
        /// 最近更新时间大于一天的 视为故障
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public IEnumerable<MeterEntity> GetFaultMeter(IEnumerable<MeterEntity> entities)
        {
            try
            {
                List<MeterEntity> returnEntity = new List<MeterEntity>();
                foreach (var item in entities)
                {
                    DateTime? getDataTime = item.GetDateTime;
                    decimal? instantaneousFlow = item.InstantaneousFlow;//瞬时流量
                    decimal? pressure = item.Pressure;//压力
                    decimal? negativeCumulativeFlow = item.NegativeCumulativeFlow;//净累计

                    decimal shusD = 0, jingLeiJiD = 0, yaLiD = 0;

                    if (getDataTime != null)
                    {
                        if ((DateTime.Now - (DateTime)getDataTime).Days > 1)
                        {
                            returnEntity.Add(item);
                            continue;
                        }
                    }

                    if (instantaneousFlow != null)
                    {
                        shusD = (decimal)instantaneousFlow;
                    }
                    if (pressure != null)
                    {
                        yaLiD = (decimal)pressure;
                    }
                    if (negativeCumulativeFlow != null)
                    {
                        jingLeiJiD = (decimal)negativeCumulativeFlow;
                    }
                    if (shusD == 0 && jingLeiJiD == 0 && yaLiD == 0)
                    {
                        returnEntity.Add(item);
                    }
                }
                return returnEntity;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 计算漏失
        /// </summary>
        /// <param name="entity">表设备</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="continuous">连续用水量</param>
        /// <param name="minFlowRate">校核按钮所使用的值  最小</param>
        /// <param name="type">校核 状态0 初始化  1.点击校核按钮</param>
        /// <param name="leakRate">漏损倍率</param>
        /// <returns></returns>
        public CalcLeakEntity GetCalcLeakEntity(MeterEntity entity, string startT, string endT, string continuous, string minFlowRate, string type)
        {
            decimal leakRate;
            if (entity.LeakRate == null)
            {
                leakRate = 0;
            }
            else
            {
                leakRate = (decimal)entity.LeakRate;
            }

            CalcLeakEntity model = new CalcLeakEntity();
            //获取时间段内的 平均 最大 最小流量
            string strSql = "SELECT AVG(InstantaneousFlow) avgInstantaneousFlow,MIN(InstantaneousFlow) minInstantaneousFlow,MAX(InstantaneousFlow) maxInstantaneousFlow FROM " + entity.TableName + " WHERE InstantaneousFlow<>0 AND InstantaneousFlow IS NOT NULL AND GetDateTime BETWEEN '" + startT + "' AND '" + endT + "'";
            DataTable dtAvg = sqlHelper.ExecuteDataTable(strSql, CommandType.Text, null);
            model.ContinuousFlow = continuous;
            string strAvg = dtAvg.Rows[0]["avgInstantaneousFlow"].ToString();
            if (!string.IsNullOrEmpty(strAvg))
            {
                decimal avgFlow = Math.Round(decimal.Parse(strAvg), 2);
                decimal minFlow = decimal.Parse(dtAvg.Rows[0]["minInstantaneousFlow"].ToString());
                decimal maxFlow = decimal.Parse(dtAvg.Rows[0]["maxInstantaneousFlow"].ToString());
                model.CalMinFlow = minFlow.ToString();//最小流量
                if (entity.IsKaoHe == 1) //如果是考核表计算
                {
                    MeterTypeEntity meterTypeEntity = GetMeterTypeEntity(entity.MeterTypeId);
                    if (meterTypeEntity.Caliber >= 100 && meterTypeEntity.Caliber <= 1000)//100-1000
                    {
                        

                        if (leakRate == 0)
                        {
                            model.ContinuousFlow = Math.Round(avgFlow / 3.5m, 4).ToString();
                        }
                        else
                        {
                            model.ContinuousFlow = Math.Round(avgFlow / leakRate, 4).ToString();
                        }
                    }
                }
                continuous = model.ContinuousFlow;
                DateTime dtStart = DateTime.Parse(startT);
                DateTime dtEnd = DateTime.Parse(endT);
                List<decimal> totalMinFlow = new List<decimal>();//计算平均最小流量
                int days = (dtEnd - dtStart).Days;
                model.IntervalDays = days.ToString();
                if (type.Equals("1"))
                {
                    minFlow = decimal.Parse(minFlowRate);
                }
                else
                {
                    for (int i = 0; i < days; i++)
                    {
                        DateTime dtTomorrow = dtStart.AddDays(1);
                        strSql = "Select avg(InstantaneousFlow) as avgInstantaneousFlow, MIN(InstantaneousFlow) as minInstantaneousFlow From " + entity.TableName + " Where InstantaneousFlow<>0 and InstantaneousFlow is not null And GetDateTime between '" + dtStart.ToString("yyyy-MM-dd 00:00") + "' and '" + dtTomorrow.ToString("yyyy-MM-dd 00:00") + "'";
                        dtStart = dtStart.AddDays(1);
                        DataTable dtMin = sqlHelper.ExecuteDataTable(strSql, CommandType.Text, null);
                        if (dtMin.Rows.Count > 0)
                        {
                            if (!string.IsNullOrEmpty(dtMin.Rows[0]["minInstantaneousFlow"].ToString()))
                            {
                                totalMinFlow.Add(decimal.Parse(dtMin.Rows[0]["minInstantaneousFlow"].ToString()));
                            }
                        }
                    }
                }
                if (totalMinFlow.Count > 0)
                {
                    decimal totalM = 0;
                    foreach (var item in totalMinFlow)
                    {
                        totalM += item;
                    }
                    minFlow = Math.Round(totalM / totalMinFlow.Count, 3);
                    minFlow = minFlow * 0.82M;
                }

                //疑似漏水量,疑似漏失率,夜间允许漏水量,期间供水量,期间售水量,连续用水量
                decimal suspectedLeakFlow, suspectedLeakRate, nightAllowLeakFlow, waterSupply, salesWater, continuousWater;
                continuousWater = 0;
                continuous = "0";//2018/04/02 更改 
                if (string.IsNullOrEmpty(continuous))
                {
                    continuousWater = 0;
                }
                else
                {
                    continuousWater = decimal.Parse(continuous);
                }
                model.ContinuousFlow = continuousWater.ToString();
                //计算疑似漏失量    实际最小流量-连续或转输用水-平均瞬时/5
                decimal suspectedLeakFlowNum =decimal.Parse(Config.GetValue("suspectedLeakFlow"));
                suspectedLeakFlow = minFlow - continuousWater - avgFlow / suspectedLeakFlowNum;
                suspectedLeakFlow = Math.Round(suspectedLeakFlow, 2);
                bool color = false;
                //如果漏失量小于零
                if (suspectedLeakFlow < 0)
                {
                    suspectedLeakFlow = 0;
                    color = true;
                }
                if (avgFlow != 0)
                {
                    suspectedLeakRate = suspectedLeakFlow / avgFlow * 100;
                }
                else
                {
                    suspectedLeakRate = 0;
                }
                if (suspectedLeakRate < 0)
                {
                    suspectedLeakRate = 0;
                    color = true;
                }
                nightAllowLeakFlow = minFlow - suspectedLeakFlow;
                nightAllowLeakFlow = avgFlow / 5;
                if (nightAllowLeakFlow < 0)
                {
                    nightAllowLeakFlow = 0;
                    color = true;
                }

                waterSupply = avgFlow * 24;
                salesWater = waterSupply - suspectedLeakFlow * 24;
                waterSupply = waterSupply * days;
                salesWater = salesWater * days;
                decimal sysWaterSupply = 0, sysSalesWater = 0;
                differenceAnalysisService.GetSysWaterSupply(entity.MeterAssessmentId.ToString(), startT, endT, ref sysWaterSupply, ref sysSalesWater); //根据DMA分区 
                model.Color = color;
                decimal lastSysWaterSupply = 0, lastSalesWater = 0;
                string lastStart = Convert.ToDateTime(startT).AddMonths(-1).ToString("yyyy-MM-10 09:00");
                string lastEnd = Convert.ToDateTime(startT).ToString("yyyy-MM-10 09:00");
                differenceAnalysisService.GetSysWaterSupply(entity.MeterAssessmentId.ToString(), lastStart, lastEnd, ref lastSysWaterSupply, ref lastSalesWater);
                model.SuspectedLeakFlow = Math.Round(suspectedLeakFlow, 2).ToString();
                model.SuspectedLeakRate = Math.Round(suspectedLeakRate, 2).ToString();
                model.NightAllowLeakFlow = Math.Round(nightAllowLeakFlow, 2).ToString();
                model.WaterSupply = Math.Round(waterSupply, 0).ToString();
                model.SalesWater = Math.Round(salesWater, 0).ToString();
                model.AvgFlow = dtAvg.Rows[0]["avgInstantaneousFlow"].ToString();
                model.MinFlow = Math.Round(minFlow, 2).ToString();
                model.MaxFlow = Math.Round(maxFlow, 2).ToString();
                model.SysWaterSupply = Math.Round(sysWaterSupply, 0).ToString();
                model.SysSalesWater = Math.Round(sysSalesWater, 0).ToString();

                model.LastSysWaterSupply = Math.Round(lastSysWaterSupply, 0).ToString();
                model.LastSysSalesWater = Math.Round(lastSalesWater, 0).ToString();
                model.LastStart = lastStart;
                model.LastEnd = lastEnd;
                model.LastIntervalDays = (DateTime.Parse(lastEnd) - DateTime.Parse(lastStart)).Days.ToString();
            }
            return model;
        }

        /// <summary>
        /// 计算设备漏失率低于某个值
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public IEnumerable<MeterEntity> GetDeviceLossMoreThanValue(IEnumerable<MeterEntity> entities, decimal value)
        {
            try
            {
                List<MeterEntity> returnEntity = new List<MeterEntity>();

                var start = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
                var end = DateTime.Now.ToString("yyyy-MM-dd");

                foreach (var item in entities)
                {

                    CalcLeakEntity clacLeakEntity = GetCalcLeakEntity(item, start, end, "0", "", "0");

                    if (!string.IsNullOrEmpty(clacLeakEntity.SuspectedLeakRate) && !clacLeakEntity.SuspectedLeakRate.Equals("--"))
                    {
                        if (decimal.Parse(clacLeakEntity.SuspectedLeakRate) >= 3)
                        {                           
                            returnEntity.Add(item);
                        }
                    }
                }
                return returnEntity;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 更新贸易表的连续用水量
        /// </summary>
        /// <param name="continueUserWater"></param>
        /// <param name="meterAssessmentId"></param>
        /// <returns></returns>
        public bool UpdateMaoYiMeterContinueUserWater(string continueUserWater,string meterAssessmentId)
        {
            try
            {             
                string updateSql = "UPDATE MeterAssessmentBase SET	ContinueUserWater =" + continueUserWater + " WHERE  MeterAssessmentId = " + meterAssessmentId;

                return sqlHelper.ExecuteNoParams(updateSql, CommandType.Text) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 设置考核表报警参数
        /// </summary>
        /// <param name="meterId">设备ID</param>
        /// <param name="continueUseWater">连续用水量</param>
        /// <param name="supplySaleDValue">产销差</param>
        /// <returns></returns>
        public bool SetMeterAlermParam(string meterId, string continueUseWater, string supplySaleDValue)
        {
            try
            {
                string updateSql = "UPDATE MeterAssessmentBase SET	ContinueUserWater =" + continueUseWater + ",SupplySaleDValue = "+ supplySaleDValue + " WHERE  MeterAssessmentId = " + meterId;

                return sqlHelper.ExecuteNoParams(updateSql, CommandType.Text) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 更新报警开关
        /// </summary>
        /// <param name="meterId">设备ID</param>
        /// <param name="alermType">报警类型 0-1-1|2|3|4  第一位 报警开关  第二位 大类型   第三位 小类型</param>       
        /// <returns></returns>
        public bool SetMeterAlermState(string meterId, string alermType)
        {
            try
            {
                string updateSql = "UPDATE MeterAssessmentBase SET	AlermType = '" + alermType + "' WHERE  MeterAssessmentId = " + meterId;

                return sqlHelper.ExecuteNoParams(updateSql, CommandType.Text) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }


    #endregion
}