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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LeaRun.Application.IService.PipeNetworkManage;
using LeaRun.Data;
using System.Data;
using LeaRun.Util.WebControl;
using LeaRun.Util.Log;
using LeaRun.Application.Entity.PipeNetworkManage;
using LeaRun.Util;
namespace LeaRun.Application.Service.PipeNetworkManage
{
    public class RealTimeMonitoringService : IRealTimeMonitoringService
    {
        SqlHelper sqlHelper = new SqlHelper("PipeNetworkDb");
        Log log = LogFactory.GetLogger(typeof(RealTimeMonitoringService));
        /// <summary>
        /// 实时列表 
        /// </summary>
        /// <param name="devId">监测点</param>
        /// <returns></returns>
        public string GetRealTimeData(string devId, Pagination pagination)
        {

            string strSql = "Select MeterAssessmentId,MeterAssessmentName From MeterAssessmentBase Where  MeterAssessmentId in (" + devId + ")";
            DateTime dateTime = DateTime.Now;
            int total = pagination.records;
            DataTable dt = sqlHelper.ExecuteDataTable(strSql, "MeterAssessmentId", true, pagination.rows, pagination.page, out total, null);
            pagination.records = total;

            StringBuilder builder = new StringBuilder();
            builder.Append("[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string deviceId = dt.Rows[i]["MeterAssessmentId"].ToString();
                string name = dt.Rows[i]["MeterAssessmentName"].ToString();
                builder.Append("{");
                builder.Append("\"ID\":\"").Append(deviceId).Append("\",");
                builder.Append("\"DevName\":\"").Append(name).Append("\",");
                string strTableName = "MeterAssessment_" + deviceId;
                if (sqlHelper.DBTableIsExists(strTableName))
                {
                    strSql = "SELECT TOP 1 GetDateTime,NetCumulativeFlow,PositiveCumulativeFlow,NegativeCumulativeFlow,InstantaneousFlow,Pressure,PressureUpAlarm,PressureDownAlarm,BatteryVoltageValue,BoxDoorAlarm FROM " + strTableName + " order BY GetDateTime desc  ";

                    DataTable table = sqlHelper.ExecuteDataTable(strSql, CommandType.Text, null);
                    if (table.Rows.Count > 0)
                    {
                        builder.Append("\"采集时间\":\"").Append(table.Rows[0]["GetDateTime"]).Append("\",");
                        builder.Append("\"净累计流量\":\"").Append(table.Rows[0]["NetCumulativeFlow"]).Append("\",");
                        builder.Append("\"正累计流量\":\"").Append(table.Rows[0]["PositiveCumulativeFlow"]).Append("\",");
                        builder.Append("\"负累计流量\":\"").Append(table.Rows[0]["NegativeCumulativeFlow"]).Append("\",");
                        builder.Append("\"瞬时流量\":\"").Append(table.Rows[0]["InstantaneousFlow"]).Append("\",");
                        builder.Append("\"压力\":\"").Append(table.Rows[0]["Pressure"]).Append("\",");
                        builder.Append("\"压力上限报警\":\"").Append(table.Rows[0]["PressureUpAlarm"]).Append("\",");
                        builder.Append("\"压力下限报警\":\"").Append(table.Rows[0]["PressureDownAlarm"]).Append("\",");
                        builder.Append("\"电池电压\":\"").Append(table.Rows[0]["BatteryVoltageValue"]).Append("\",");
                        builder.Append("\"测控箱开门报警\":\"").Append(table.Rows[0]["BoxDoorAlarm"]).Append("\"");
                    }
                    else
                    {
                        builder.Append("\"采集时间\":\"").Append("--").Append("\",");
                        builder.Append("\"净累计流量\":\"").Append("--").Append("\",");
                        builder.Append("\"正累计流量\":\"").Append("--").Append("\",");
                        builder.Append("\"负累计流量\":\"").Append("--").Append("\",");
                        builder.Append("\"瞬时流量\":\"").Append("--").Append("\",");
                        builder.Append("\"压力\":\"").Append("--").Append("\",");
                        builder.Append("\"压力上限报警\":\"").Append("--").Append("\",");
                        builder.Append("\"压力下限报警\":\"").Append("--").Append("\",");
                        builder.Append("\"电池电压\":\"").Append("--").Append("\",");
                        builder.Append("\"测控箱开门报警\":\"").Append("--").Append("\"");
                    }
                }
                else
                {
                    builder.Append("\"采集时间\":\"").Append("--").Append("\",");
                    builder.Append("\"净累计流量\":\"").Append("--").Append("\",");
                    builder.Append("\"正累计流量\":\"").Append("--").Append("\",");
                    builder.Append("\"负累计流量\":\"").Append("--").Append("\",");
                    builder.Append("\"瞬时流量\":\"").Append("--").Append("\",");
                    builder.Append("\"压力\":\"").Append("--").Append("\",");
                    builder.Append("\"压力上限报警\":\"").Append("--").Append("\",");
                    builder.Append("\"压力下限报警\":\"").Append("--").Append("\",");
                    builder.Append("\"电池电压\":\"").Append("--").Append("\",");
                    builder.Append("\"测控箱开门报警\":\"").Append("--").Append("\"");
                }
                builder.Append("}");
                if (i < dt.Rows.Count - 1)
                    builder.Append(",");
            }
            builder.Append("]");
            return builder.ToString();
        }



        /// <summary>
        /// 实时分析
        /// </summary>
        /// <param name="departId">部门</param>      
        /// <param name="devId">监测点</param>
        /// <param name="selField">实时分析的字段</param>
        /// <param name="fieldName">字段名称</param>
        /// <returns></returns>
        public string GetRealTimeAnalysis(Pagination pagination, string selField, string fieldName)
        {
            try
            {
                string strSql = " Select MeterAssessmentId,MeterAssessmentName From MeterAssessmentBase ";

                DateTime dateTime = DateTime.Now;

                int total = pagination.records;

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

                pagination.records = total;

                StringBuilder builder = new StringBuilder();
                builder.Append("[");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string deviceId = dt.Rows[i]["MeterAssessmentId"].ToString();
                    string name = dt.Rows[i]["MeterAssessmentName"].ToString();
                    builder.Append("{");
                    builder.Append("\"ID\":\"").Append(deviceId).Append("\",");
                    builder.Append("\"DevName\":\"").Append(name).Append("\",");
                    builder.Append("\"传感器\":\"").Append(fieldName).Append("\",");
                    string strTableName = "MeterAssessment_" + deviceId;
                    if (sqlHelper.DBTableIsExists(strTableName))
                    {
                        //取昨天跟今天两天得数据
                        strSql = "SELECT * ,DATEDIFF(DAY,GETDATE(),GetDateTime) AS TimeDiff FROM " + strTableName + " WHERE DATEDIFF(DAY,GETDATE(),GetDateTime) IN (0,-1) ORDER BY GetDateTime DESC";

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

                        //今天数据取得
                        DataRow[] todayData = table.Select("TimeDiff = 0");
                        DataRow[] yestodayData = table.Select("TimeDiff = -1");

                        if (yestodayData.Length > 0)
                        {
                            builder.Append(GetDadaFromData(yestodayData, selField, "昨日"));
                        }
                        else
                        {
                            builder.Append("\"昨日最高\":\"").Append("--").Append("\",");
                            builder.Append("\"昨日最高时间\":\"").Append("--").Append("\",");
                            builder.Append("\"昨日最低\":\"").Append("--").Append("\",");
                            builder.Append("\"昨日最低时间\":\"").Append("--").Append("\",");
                            builder.Append("\"昨日平均\":\"").Append("--").Append("\",");
                            builder.Append("\"昨日振幅\":\"").Append("--").Append("\",");
                        }

                        if (todayData.Length > 0)
                        {
                            builder.Append(GetDadaFromData(todayData, selField, "今日"));
                            builder.Append("\"上传时间\":\"").Append(Convert.ToDateTime(todayData[0]["GetDateTime"]).ToString("hh:ss")).Append("\",");
                            builder.Append("\"" + fieldName + "\":\"").Append(todayData[0][selField]).Append("\"");
                        }
                        else
                        {
                            builder.Append("\"今日最高\":\"").Append("--").Append("\",");
                            builder.Append("\"今日最高时间\":\"").Append("--").Append("\",");
                            builder.Append("\"今日最低\":\"").Append("--").Append("\",");
                            builder.Append("\"今日最低时间\":\"").Append("--").Append("\",");
                            builder.Append("\"今日平均\":\"").Append("--").Append("\",");
                            builder.Append("\"今日振幅\":\"").Append("--").Append("\",");
                            builder.Append("\"上传时间\":\"").Append("--").Append("\",");
                            builder.Append("\"" + fieldName + "\":\"").Append("--").Append("\"");
                        }
                    }
                    builder.Append("}");
                    if (i < dt.Rows.Count - 1)
                        builder.Append(",");
                }
                builder.Append("]");
                return builder.ToString();
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                return "";
            }

        }


        /// <summary>
        /// 获取其中的 最大值 最小值  平均值
        /// </summary>
        /// <param name="rows"></param>
        /// <returns></returns>
        public StringBuilder GetDadaFromData(DataRow[] rows, string selField, string name)
        {
            double maxValue = 0;
            string maxValueTime = "";
            double minValue = 0;
            string minValueTime = "";
            double avgValue = 0;
            double addPercent = 0;
            double allValue = 0;

            StringBuilder sbBuilder = new StringBuilder();

            for (int row = 0; row < rows.Length; row++)
            {
                double thisValue = Convert.ToDouble(rows[row][selField].ToString()==""?"0":rows[row][selField].ToString());
                allValue += thisValue;
                if (row == 0)
                {
                    minValue = maxValue = thisValue;
                    minValueTime = maxValueTime = rows[row]["GetDateTime"].ToString();
                }

                if (maxValue < thisValue)
                {
                    maxValue = thisValue;
                    maxValueTime = rows[row]["GetDateTime"].ToString();
                }

                if (minValue > thisValue)
                {
                    minValue = thisValue;
                    minValueTime = rows[row]["GetDateTime"].ToString();
                }
            }
            avgValue = allValue / rows.Length;
            addPercent = (maxValue - minValue) / maxValue;

            sbBuilder.Append("\"" + name + "最高\":\"").Append(maxValue.ToString("0.0")).Append("\",");
            sbBuilder.Append("\"" + name + "最高时间\":\"").Append(Convert.ToDateTime(maxValueTime).ToString("MM-dd HH:ss")).Append("\",");
            sbBuilder.Append("\"" + name + "最低\":\"").Append(minValue.ToString("0.0")).Append("\",");
            sbBuilder.Append("\"" + name + "最低时间\":\"").Append(Convert.ToDateTime(minValueTime).ToString("MM-dd HH:ss")).Append("\",");
            sbBuilder.Append("\"" + name + "平均\":\"").Append(avgValue.ToString("0.0")).Append("\",");
            sbBuilder.Append("\"" + name + "振幅\":\"").Append(addPercent.ToString("0.0")).Append("\",");

            return sbBuilder;
        }


        /// <summary>
        ///  获取数据
        /// </summary>
        /// <param name="tableNames"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public List<RealCurvesEntity> GetRealCurve(List<string> tableNames, string type)
        {
            StringBuilder sb = new StringBuilder("");
            try
            {
                for (int i = 0; i < tableNames.Count; i++)
                {
                    sb.Append("select mab.MeterAssessmentId, mab.MeterAssessmentName, '" + type + "' Type, " + tableNames[i] + "." + type + " Value, " + tableNames[i] + ".GetDateTime Date from ");
                    sb.Append(tableNames[i] + " INNER JOIN MeterAssessmentBase AS mab ON mab.MeterAssessmentId = " + tableNames[i] + ".MeterAssessmentId where DateDiff(DAY," + tableNames[i] + ".GetDateTime,getdate())=0 ");
                    if (i < tableNames.Count - 1)
                    {
                        sb.Append(" union ");
                    }
                }
                sb.Append(" ORDER BY [Date] ASC ");
                DataTable dt = sqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, null);
                List<RealCurvesEntity> list = DataHelper.DataTableToT<RealCurvesEntity>(dt);
                return list == null ? new List<RealCurvesEntity>() : list;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}