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;
}
}
///
/// 消息订阅列表
///
///
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;
}
}
///
/// 更新消息订阅状态
///
///
///
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;
}
}
///
/// 更新设备绑定状态
///
///
///
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;
}
}
}
}