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

using LeaRun.Application.IService.PipeNetworkManage;
using LeaRun.Data.Repository;
using LeaRun.Util.WebControl;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using LeaRun.Util;
using LeaRun.Data;
using LeaRun.Util.Extension;
using System;
using System.Text;
using LeaRun.Util.Enums;
using LeaRun.Application.Entity.PipeNetworkManage;
using System.Data.SqlClient;

namespace LeaRun.Application.Service.PipeNetworkManage
{
    /// <summary>
    /// 版 本 6.1
    /// Copyright (c) 2013-2016 上海力软信息技术有限公司
    /// 创 建：administrator
    /// 日 期：2017-08-05 09:35
    /// 描 述：历史记录模板
    /// </summary>
    public class HistoryService : IHistoryService
    {
        //数据操作类
        SqlHelper sqlHelper = new SqlHelper("PipeNetworkDb");


        /// <summary>
        /// 查询设备历史记录
        /// </summary>
        /// <param name="deviceId">设备ID</param>
        /// <param name="startT">开始时间</param>
        /// <param name="endT">结束时间</param>
        /// <param name="pagination">分页参数</param>
        /// <returns></returns>
        public string GetGridData(string deviceId, string startT, string endT, Pagination pagination)
        {
            try
            {
                string selSql = "Select MeterAssessmentId,MeterAssessmentName From MeterAssessmentBase Where  MeterAssessmentId in (" + deviceId + ")";

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

                StringBuilder returnJson = new StringBuilder();
                var strSql = new StringBuilder();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string id = dt.Rows[i]["MeterAssessmentId"].ToString();
                    string name = dt.Rows[i]["MeterAssessmentName"].ToString();
                    string strTableName = "MeterAssessment_" + id;
                    strSql.Append("Select '" + name + "' as name, * From " + strTableName + " Where GetDateTime between '" + startT + "' and '" + endT + "' ");
                    if (i < dt.Rows.Count - 1)
                        strSql.Append(" union ");

                }

                int total = pagination.records;
                DataTable dtResult = sqlHelper.ExecuteDataTable(strSql.ToString(), "MeterAssessmentId", true, pagination.rows, pagination.page, out total, null);
                pagination.records = total;

                for (int row = 0; row < dtResult.Rows.Count; row++)
                {
                    returnJson.Append("{");
                    returnJson.Append("\"测点名称\":\"").Append(dtResult.Rows[row]["name"].ToString()).Append("\",");
                    returnJson.Append("\"采集时间\":\"").Append(dtResult.Rows[row]["GetDateTime"].ToString()).Append("\",");
                    returnJson.Append("\"瞬时流量\":\"").Append(dtResult.Rows[row]["InstantaneousFlow"].ToString()).Append("\",");
                    returnJson.Append("\"电池电压\":\"").Append(dtResult.Rows[row]["BatteryVoltageValue"].ToString()).Append("\",");
                    returnJson.Append("\"正累计流量\":\"").Append(dtResult.Rows[row]["PositiveCumulativeFlow"].ToString()).Append("\",");
                    returnJson.Append("\"负累计流量\":\"").Append(dtResult.Rows[row]["NegativeCumulativeFlow"].ToString()).Append("\",");
                    returnJson.Append("\"净累计流量\":\"").Append(dtResult.Rows[row]["NetCumulativeFlow"].ToString()).Append("\",");
                    returnJson.Append("\"压力\":\"").Append(dtResult.Rows[row]["Pressure"].ToString()).Append("\"");
                    returnJson.Append("}");
                    returnJson.Append(",");
                }
                string returnData = returnJson.ToString();
                return "[" + returnData.TrimEnd(',') + "]";
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 查询设备历史记录
        /// </summary>
        /// <param name="deviceId">设备ID</param>
        /// <param name="startT">开始时间</param>
        /// <param name="endT">结束时间</param>     
        /// <returns></returns>
        public DataTable GetHistoryData(string deviceId, string startT, string endT)
        {
            try
            {
                string selSql = "Select MeterAssessmentId,MeterAssessmentName From MeterAssessmentBase Where  MeterAssessmentId in (" + deviceId + ")";

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

                StringBuilder returnJson = new StringBuilder();
                var strSql = new StringBuilder();

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string id = dt.Rows[i]["MeterAssessmentId"].ToString();
                        string name = dt.Rows[i]["MeterAssessmentName"].ToString();
                        string strTableName = "MeterAssessment_" + id;
                        strSql.Append("Select '" + name + "' as name, * From " + strTableName + " Where GetDateTime between '" + startT + "' and '" + endT + "'");
                        if (i < dt.Rows.Count - 1)
                            strSql.Append(" union ");
                    }
                    strSql.Append(" order by MeterAssessmentId,GetDateTime ASC ");
                    DataTable dtResult = sqlHelper.ExecuteDataTable(strSql.ToString(), CommandType.Text, null);
                    return dtResult;
                }
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 凌晨流量Json
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="dtStart"></param>
        /// <param name="flowType">0 入口  1 出口</param>
        /// <param name="lineType"></param>
        /// <param name="datas"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public string GetDaybreakJsonData(MeterEntity entity, DateTime dtStart, string lineType, List<string> datas, int size, int flowType)
        {
            var json = "";
            try
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("{\"devName\":\"").Append(entity.MeterAssessmentName).Append("\",\"lineType\":\"").Append(lineType).Append("\",\"flowType\":\"").Append(flowType).Append("\",\"devData\":");
                builder.Append("[");
                DateTime dtTime = dtStart;
                for (int i = 0; i <= size; i++)
                {
                    builder.Append("{");
                    DateTime dtS = dtStart.AddMinutes(i * 30).AddMinutes(-1);
                    DateTime dtE = dtStart.AddMinutes(i * 30).AddMinutes(1);
                    DateTime dtT = dtStart.AddMinutes(i * 30);

                    var sql = "Select Top 1 InstantaneousFlow From " + entity.TableName + " Where InstantaneousFlow is not null And GetDateTime >='" + dtS.ToString("yyyy-MM-dd HH:mm") + "' And GetDateTime <='" + dtE.ToString("yyyy-MM-dd HH:mm") + "'";
                    DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                    if (dt.Rows.Count > 0)
                    {
                        builder.Append("\"Flow\":").Append(dt.Rows[0]["InstantaneousFlow"].ToString()).Append(",\"RTime\":\"").Append(dtT.ToString("yyyy-MM-dd HH:mm")).Append("\"");
                        datas.Add(dt.Rows[0]["InstantaneousFlow"].ToString());
                    }
                    else
                    {
                        builder.Append("\"Flow\":\"-\"").Append(",\"RTime\":\"").Append(dtT.ToString("yyyy-MM-dd HH:mm")).Append("\"");
                        datas.Add("-");
                    }
                    builder.Append("}");
                    if (i < size)
                    {
                        builder.Append(",");
                    }
                }
                builder.Append("]}");

                json = builder.ToString();
            }
            catch (Exception ex)
            {
                json = "";
            }
            return json;
        }


        /// <summary>
        /// 获取某一段时间的流量
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>      
        /// <returns></returns>
        public string GetInsData(MeterEntity entity, string dtStart, string dtEnd)
        {
            var json = "0";
            try
            {                
                var sql = "Select (MAX(NetCumulativeFlow)-MIN(NetCumulativeFlow)) NetCumulativeFlow From " + entity.TableName + " Where InstantaneousFlow is not null And InstantaneousFlow<>0 And GetDateTime >='" + dtStart + "' And GetDateTime <='" + dtEnd + "'";
                DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                if (dt.Rows.Count > 0)
                {
                    json= dt.Rows[0]["NetCumulativeFlow"].ToString();
                }               
            }
            catch (Exception ex)
            {
                json = "";
            }
            return json==""?"0":json;
        }
        /// <summary>
        /// 获取某一段时间的流量
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>      
        /// <returns></returns>
        public string GetInsData(MeterEntity entity, string dtStart, string dtEnd,List<string> datas)
        {
            var json = "";
            try
            {
                var sql = "Select (MAX(NetCumulativeFlow)-MIN(NetCumulativeFlow)) NetCumulativeFlow From " + entity.TableName + " Where InstantaneousFlow is not null And InstantaneousFlow<>0 And GetDateTime >='" + dtStart + "' And GetDateTime <='" + dtEnd + "'";
                DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                if (dt.Rows.Count > 0)
                {
                    datas.Add(dt.Rows[0]["NetCumulativeFlow"].ToString());
                }
            }
            catch (Exception ex)
            {
                json = "";
            }
            return json;
        }
        /// <summary>
        /// 获取K线数据
        /// </summary>
        /// <param name="devId"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="interval"></param>
        /// <returns></returns>
        public string GetFlowKData(string devId, string start, string end, string interval)
        {
            var json = "[]";
            try
            {
                SqlParameter[] param = new SqlParameter[] {
                    new SqlParameter("@sTime",Convert.ToDateTime(start)),
                    new SqlParameter("@eTime",Convert.ToDateTime(end)),
                    new SqlParameter("@devId",devId),
                    new SqlParameter("@interval",Convert.ToInt32(interval)),
            };
                DataTable dt = sqlHelper.ExecuteDataTable("dbo.Proc_GetFlowKData", CommandType.StoredProcedure, param);
                json = Json.ToJson(dt);
            }
            catch (Exception ex)
            {
                json = "[]";
            }
            return json;
        }
    }
}