NBDXSY/LeaRun.Application/LeaRun.Application.Service/NBManage/NBSubscribeListenerService.cs

using LeaRun.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using LeaRun.Util;
using Newtonsoft.Json.Linq;

namespace LeaRun.Application.Service.NBManage
{
    public class NBSubscribeListenerService
    {
        SqlHelper sqlHelper = new SqlHelper("NBDB");

        public void GetMeterHistory()
        {
            try
            {// 更新表维护  看看有无新设备加入
                string taskSql = "SELECT * FROM RMRS_MeterInfo AS rmi WHERE rmi.IOT_Code IS NOT NULL AND rmi.IOT_Code NOT IN ( SELECT rmt.IOT_Code  FROM dbo.RMRS_HistoryTask AS rmt)";
                DataTable dtIsNewMeter = sqlHelper.ExecuteDataTable(taskSql, CommandType.Text, null);
                if (dtIsNewMeter.Rows.Count > 0)
                {
                    //新设备插入 维护表
                    for (int row = 0; row < dtIsNewMeter.Rows.Count; row++)
                    {
                        string IOT_Code = dtIsNewMeter.Rows[row]["IOT_Code"].ToString();
                        string insertTask = "INSERT INTO RMRS_HistoryTask(IOT_Code,LastUpdateTime) VALUES('" + IOT_Code + "','2018/04/01 00:00:00')";
                        sqlHelper.ExecuteNoParams(insertTask, CommandType.Text);
                    }
                }
                
                string selTask = "SELECT * FROM dbo.RMRS_HistoryTask";
                DataTable dtTasl = sqlHelper.ExecuteDataTable(selTask, CommandType.Text, null);
                for (int row = 0; row < dtTasl.Rows.Count; row++)
                {
                    DateTime dtLastTime = DateTime.Parse(dtTasl.Rows[row]["LastUpdateTime"].ToString());
                    DateTime endTime = DateTime.Now;
                    string IOT_Code = dtTasl.Rows[row]["IOT_Code"].ToString();
                    //根据IOT_Code 查找设备信息
                    string selMeter = "SELECT * FROM RMRS_MeterInfo AS rmi WHERE rmi.IOT_Code = '" + IOT_Code + "'";
                    DataTable dtMeter = sqlHelper.ExecuteDataTable(selMeter, CommandType.Text, null);
                    if (dtMeter.Rows.Count != 1)
                    {
                        continue;
                    }
                    string meterId = dtMeter.Rows[0]["MeterID"].ToString();

                    //华为平台数据

                    JObject jObj = NBCommon.GetDeviceHistoryData(IOT_Code, dtLastTime.ToString(), endTime.ToString());
                    
                    DataTable dtHistory = NBCommon.FormatJObjectHistoryData(jObj,"1");
                    //历史数据保存
                    for (int i = 0; i < dtHistory.Rows.Count; i++)
                    {
                        string total = dtHistory.Rows[i]["total"].ToString();//净累计
                        string read_time = dtHistory.Rows[i]["read_time"].ToString();//读取时间
                        string fa_status = dtHistory.Rows[i]["fa_status"].ToString();//阀门状态
                        string batteryLevel = dtHistory.Rows[i]["batteryLevel"].ToString();//电池电压
                        string total_up = dtHistory.Rows[i]["total_up"].ToString();//正累计
                        string total_down = dtHistory.Rows[i]["total_down"].ToString();//负累计
                        string instance = dtHistory.Rows[i]["instance"].ToString();//瞬时流量
                        string battery_status = dtHistory.Rows[i]["battery_status"].ToString();//电池状态
                        string attack = dtHistory.Rows[i]["attack"].ToString();//磁攻击报警
                        string attack_ever = dtHistory.Rows[i]["attack_ever"].ToString();//曾经磁攻击报警
                        string q4 = dtHistory.Rows[i]["q4"].ToString();//水流量超过水表Q4值
                        string rsrp = dtHistory.Rows[i]["rsrp"].ToString();//信号强度
                        string ecl = dtHistory.Rows[i]["ecl"].ToString();//信号覆盖等级
                        string snr = dtHistory.Rows[i]["snr"].ToString();//信噪比

                        if (read_time == "2000-00-00 00:00:00")
                        {
                            continue;
                        }

                        string insertHisSql = "INSERT INTO RMRS_HistoryRecord (MeterID,ValveStatus,Reading,ReadingDT,Total,TotalUp,TotalDown,Instance,BatteryStatus,Attack,AttackEver,Q4,Rsrp,Ecl,Snr,BatteryLevel) VALUES("
                                              + meterId + ",'" + fa_status + "' , " + total + ",'" + read_time + "',"+total+","+total_up+","+total_down+","+instance+", '"+battery_status+"' , '"+attack+"' , '"+attack_ever+"' , '"+q4+"' , '"+rsrp+"' , '"+ecl+"' , '"+snr+"' , '"+batteryLevel+"')";                      
                        sqlHelper.ExecuteNoQuery(insertHisSql, null);
                        //将最新数据同步更新到MeterInfo 表中
                        string upSQL = "UPDATE RMRS_MeterInfo SET NowReading = '"+ total + "', NowRadingDT = '"+ read_time + "', BatteryVoltage = " + batteryLevel + "WHERE MeterID = " +meterId;
                        sqlHelper.ExecuteNoParams(upSQL,CommandType.Text);
                    }

                    //taskHistory 表维护
                    string udpateTask = "UPDATE RMRS_HistoryTask SET LastUpdateTime = '" + DateTime.Now.ToString() + "' WHERE IOT_Code='" + IOT_Code + "'";
                    sqlHelper.ExecuteNoParams(udpateTask, CommandType.Text);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 消息订阅列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetNotiryLists()
        {
            try
            {
                string selSql = "SELECT * FROM dbo.RMRS_ApplicationNotify";
                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                return dtResult;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 更新消息订阅状态
        /// </summary>
        /// <param name="notityId"></param>
        /// <returns></returns>
        public bool UpdateNotifyStatus(string notityId)
        {
            try
            {
                string updateSql = "UPDATE RMRS_ApplicationNotify SET NotiryStatus='已订阅', NotifyTime=GETDATE() WHERE NotifyId = " + notityId ;

                return sqlHelper.ExecuteNonQuery(updateSql,CommandType.Text,null)>0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 更新设备绑定状态
        /// </summary>
        /// <param name="iot_code"></param>
        /// <returns></returns>
        public bool UpdateMeterStatus(string iot_code)
        {
            try
            {
                string updateSql = "UPDATE RMRS_MeterInfo SET	CommunicationState = 1 WHERE IOT_Code = '"+iot_code+"'";
                string updateforBatchRegister = "UPDATE RMRS_NBBatchRegister SET	CommunicationState = 1 WHERE DeviceID = '" + iot_code + "'";
                sqlHelper.ExecuteNoParams(updateforBatchRegister, CommandType.Text);
                return sqlHelper.ExecuteNoParams(updateSql,CommandType.Text)>0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}