Boonlive_RD_Web/Web_F305_Mqtt_Prod
8
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Web_F305_Mqtt_Prod/MQTTServerSideAPI/SQLiteDBHelper.cs

2881 lines
125 KiB
C#
Raw Permalink Normal View History

初始化
2025-11-26 11:32:30 +08:00
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SQLite;
using Newtonsoft.Json.Linq;
using System.Text;
using System.Globalization;
using System.Web.UI.WebControls.WebParts;
using System.Net;
using Newtonsoft.Json;
using MySql.Data.MySqlClient;
using MySqlX.XDevAPI;
using MQTTServerSideAPI.Controllers;
using System.Xml.Linq;
using static System.Data.Entity.Infrastructure.Design.Executor;
using System.Web.Helpers;
using Org.BouncyCastle.Asn1.X509;
using static MQTTServerSideAPI.SQLiteDBHelper;
using Google.Protobuf.WellKnownTypes;
using System.Text.RegularExpressions;
using Org.BouncyCastle.Asn1.Cms;
using static Google.Protobuf.Reflection.FieldOptions.Types;
using MQTTServerSideAPI.Models;
using System.Net.NetworkInformation;
using StackExchange.Redis;
using Org.BouncyCastle.Ocsp;
using System.Web.UI;
namespace MQTTServerSideAPI
{
public static class SQLiteDBHelper
{
// public static string strSqliteConn = @"Data Source=C:\sqllite\data\mydb.db;Version=3";
public static string strSqliteConn = @"Server=blv-cloud-db.mysql.rds.aliyuncs.com;Database=mqqtmysql;Uid=blv_rcu;Pwd=fnadiaJDIJ7546;charset=utf8;port=3307;";
public static int Loning(loginuser info)
{
DataSet ds = new DataSet();
string strSqlCmd = "select * from userinfo where name='" + info.user_name + "' and pwd ='" + info.password + "'";
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return 1;
}
else
{
return 0;
}
}
catch
{
return 0;
}
}
#region Get Record
public static List<string> GetAllCallin()
{
List<string> listRet = new List<string>();
string curline = "";
DataSet ds = GetAllCallinRecord();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
curline = "";
curline += "DeviceName:" + row["DeviceName"] + ",";
curline += "Online:" + row["Online"] + ",";
curline += "districtinfo:" + row["districtinfo"] + ",";
curline += "LastUpdateTime:" + row["LastUpdateTime"] + ",";
listRet.Add(curline);
}
}
return listRet;
}
public static List<string> GetAllCallinBYName(string Name, int zhuant)
{
List<string> listRet = new List<string>();
string curline = "";
DataSet ds = new DataSet();
ds = GetAllCallinRecordBYName(Name, zhuant);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
curline = "";
curline += "DeviceName：" + row["DeviceName"] + ",";
curline += "Online：" + row["Online"] + ",";
curline += "districtinfo：" + row["districtinfo"] + ",";
curline += "LastUpdateTime：" + row["LastUpdateTime"] + ",";
curline += "powerintakestatus：" + row["powerintakestatus"] + ",";
curline += "Power：" + row["Power"] + ",";
curline += "pir1status：" + row["pir1status"] + ",";
curline += "pir2status：" + row["pir2status"] + ",";
curline += "pir3status：" + row["pir3status"] + ",";
curline += "pir4status：" + row["pir4status"] + ",";
curline += "gatemagnetstatus：" + row["gatemagnetstatus"] + ",";
curline += "pir5status：" + row["pir5status"] + ",";
curline += "pir6status：" + row["pir6status"] + ",";
curline += "pir7status：" + row["pir7status"] + ",";
curline += "pir8status：" + row["pir8status"] + ",";
curline += "powerOnEquipment：" + row["powerOnEquipment"] + ",";
curline += "equipmenttime：" + row["equipmenttime"] + ",";
curline += "powerOnEquipmentinfo：" + row["powerOnEquipmentinfo"] + ",";
curline += "yingjianbanbinfo：" + row["yingjianbanbinfo"] + ",";
curline += "ruanjianbanbinfo：" + row["ruanjianbanbinfo"] + ",";
listRet.Add(curline);
}
}
return listRet;
}
public static List<string> GetFilterCallin(string productid, string devicename, int shijian)
{
List<string> listRet = new List<string>();
DataSet ds = new DataSet()
; //1. get all
if (shijian == 0)
{
ds = GetAllCallinRecord();
}
else
{
ds = GetAllCallinRecordBYTime(productid, devicename, shijian);
}
//2. filter
string curline = "";
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable logs = ds.Tables[0];
EnumerableRowCollection<DataRow> query =
from log in logs.AsEnumerable()
where log.Field<string>("productid") == productid && log.Field<string>("devicename") == devicename
select log;
foreach (DataRow row in query)
{
curline = "";
curline += row["timeappreceive"] + "|";
//curline += "productid:" + row["productid"] + ",";
//curline += "devicename:" + row["devicename"] + ",";
//curline += "topic:" + row["topic"] + ",";
//curline += "payload:" + row["payload"] + ",";
curline += AnalysisByte(row["payload"].ToString(), row["devicename"].ToString());
//curline += "payload:" + AnalysisByte("pkwAf7vmZCwApwEEvQAAXEOuR0FAAACdQgAAAAAAAAAAAAAAAAAAAAAAAAA=") + ",";
//平台参数，暂时不需要
//curline += "seq:" + row["seq"] + ",";
//curline += "timestamp:" + row["timestamp"] + ",";
//curline += "timemills:" + row["timemills"];
listRet.Add(curline);
}
}
return listRet;
}
public class FixedHeaderPart
{
public int FixedFlag { get; set; }
public int SN { get; set; }
public UInt32 TimeStamp { get; set; }
public int LEN { get; set; }
public byte CHECK { get; set; }
public int PowerStatus { get; set; }
public int CMD { get; set; }
}
public class HeartBeatPara
{
public int RSSI { get; set; }
public float voltage { get; set; }
public float power { get; set; }
public float energyconsumption { get; set; }
public int doorlockonline { get; set; }
public string strHexdoorlockstatus { get; set; }
public bool isLockFsfsOn { get; set; }
public bool isLockLowCapIndOn { get; set; }
public bool isLockFakIndOn { get; set; }
public int doorlockcapacity { get; set; }
}
public static String HearBeatToString(HeartBeatPara para)
{
string retStr = "";
retStr += "RSSI值：" + para.RSSI + ",";
retStr += "电压：" + para.voltage.ToString("F", CultureInfo.InvariantCulture) + "伏,";
retStr += "功率：" + para.power.ToString("F", CultureInfo.InvariantCulture) + "瓦,";
retStr += "能耗：" + para.energyconsumption.ToString("F", CultureInfo.InvariantCulture) + "瓦时,";
retStr += "门锁在线：" + para.doorlockonline + ",";
retStr += "门锁状态： 反锁方舌" + (para.isLockFsfsOn ? "开启" : "关闭") + " 低电量预警" + (para.isLockLowCapIndOn ? "开启" : "关闭") + " 假锁预警" + (para.isLockFakIndOn ? "开启" : "关闭") + ",";
retStr += "门锁电量：" + para.doorlockcapacity + "%,";
return retStr;
}
public class DoorLockActionPara
{
public int dooronlinestatus { get; set; }
public int doorstatus { get; set; }
public int doorcapacity { get; set; }
}
public static String DoorLockActionToString(DoorLockActionPara para)
{
Dictionary<int, string> dicDoorStat = new Dictionary<int, string>();
dicDoorStat.Add(1, "外开门");
dicDoorStat.Add(2, "内开门");
dicDoorStat.Add(3, "反锁方舌开启");
dicDoorStat.Add(4, "反锁方舌关闭");
dicDoorStat.Add(5, "关门");
dicDoorStat.Add(6, "机械钥匙开锁");
dicDoorStat.Add(7, "低电量预警");
dicDoorStat.Add(8, "假锁预警");
dicDoorStat.Add(9, "蓝牙开锁");
dicDoorStat.Add(10, "刷卡开锁");
dicDoorStat.Add(11, "其他开锁");
string retStr = "";
retStr += "门锁在线状态:" + (para.dooronlinestatus == 1 ? "在线" : "离线") + ",";
retStr += "门锁状态:" + dicDoorStat[para.doorstatus] + ",";
retStr += "门锁电量：" + para.doorcapacity + "%,";
return retStr;
}
public class PIRActionPara
{
public string Pirmovementname { get; set; }
public int PIRAction { get; set; }
public int PIRTotalTriggerTimes { get; set; }
public int PIR1Times { get; set; }
public int PIR2Times { get; set; }
public int PIR3Times { get; set; }
public int PIR4Times { get; set; }
public int PIR5Times { get; set; }
public int PIR6Times { get; set; }
public int PIR7Times { get; set; }
public int PIR8Times { get; set; }
public int PIR1OnlineStatus { get; set; }
public int PIR2OnlineStatus { get; set; }
public int PIR3OnlineStatus { get; set; }
public int PIR4OnlineStatus { get; set; }
public int PIR5OnlineStatus { get; set; }
public int PIR6OnlineStatus { get; set; }
public int PIR7OnlineStatus { get; set; }
public int PIR8OnlineStatus { get; set; }
public int PIR1Capacity { get; set; }
public int PIR2Capacity { get; set; }
public int PIR3Capacity { get; set; }
public int PIR4Capacity { get; set; }
public int PIR5Capacity { get; set; }
public int PIR6Capacity { get; set; }
public int PIR7Capacity { get; set; }
public int PIR8Capacity { get; set; }
}
public static String PIRActionToString(PIRActionPara para)
{
Dictionary<int, string> dicDoorStat = new Dictionary<int, string>();
dicDoorStat.Add(1, "PIR设备离线");
dicDoorStat.Add(2, "设备上线");
dicDoorStat.Add(3, "关门触发设备唤醒");
dicDoorStat.Add(4, "PIR激活后超时时间到触发休眠");
dicDoorStat.Add(5, "PIR激活后触发次数到触发休眠");
dicDoorStat.Add(6, "PIR低电量报警");
string retStr = "";
retStr += "PIR动作：" + dicDoorStat[para.PIRAction] + ",";
para.Pirmovementname = dicDoorStat[para.PIRAction];
retStr += "PIR总触发次数：" + para.PIRTotalTriggerTimes + ",";
retStr += "PIR1次数：" + para.PIR1Times + ",";
retStr += "PIR2次数：" + para.PIR2Times + ",";
retStr += "PIR3次数：" + para.PIR3Times + ",";
retStr += "PIR4次数：" + para.PIR4Times + ",";
retStr += "PIR1在线状态：" + para.PIR1OnlineStatus + ",";
retStr += "PIR2在线状态：" + para.PIR2OnlineStatus + ",";
retStr += "PIR3在线状态：" + para.PIR3OnlineStatus + ",";
retStr += "PIR4在线状态：" + para.PIR4OnlineStatus + ",";
retStr += "PIR1电量：" + para.PIR1Capacity + "%,";
retStr += "PIR2电量：" + para.PIR2Capacity + "%,";
retStr += "PIR3电量：" + para.PIR3Capacity + "%,";
retStr += "PIR4电量：" + para.PIR4Capacity + "%,";
retStr += "PIR5次数：" + para.PIR5Times + ",";
retStr += "PIR6次数：" + para.PIR6Times + ",";
retStr += "PIR7次数：" + para.PIR7Times + ",";
retStr += "PIR8次数：" + para.PIR8Times + ",";
retStr += "PIR5在线状态：" + para.PIR5OnlineStatus + ",";
retStr += "PIR6在线状态：" + para.PIR6OnlineStatus + ",";
retStr += "PIR7在线状态：" + para.PIR7OnlineStatus + ",";
retStr += "PIR8在线状态：" + para.PIR8OnlineStatus + ",";
retStr += "PIR5电量：" + para.PIR5Capacity + "%,";
retStr += "PIR6电量：" + para.PIR6Capacity + "%,";
retStr += "PIR7电量：" + para.PIR7Capacity + "%,";
retStr += "PIR8电量：" + para.PIR8Capacity + "%,";
return retStr;
}
public class IntervalMessagePara
{
public int RSSISignal { get; set; }
public float CheckV { get; set; }
public float CheckP { get; set; }
public float Energy { get; set; }
public int LockOnlineStatus { get; set; }
public int PIR1OnlineStatus { get; set; }
public int PIR2OnlineStatus { get; set; }
public int PIR3OnlineStatus { get; set; }
public int PIR4OnlineStatus { get; set; }
public int PIR5OnlineStatus { get; set; }
public int PIR6OnlineStatus { get; set; }
public int PIR7OnlineStatus { get; set; }
public int PIR8OnlineStatus { get; set; }
public int LockCapacity { get; set; }
public int PIR1Capacity { get; set; }
public int PIR2Capacity { get; set; }
public int PIR3Capacity { get; set; }
public int PIR4Capacity { get; set; }
public int PIR5Capacity { get; set; }
public int PIR6Capacity { get; set; }
public int PIR7Capacity { get; set; }
public int PIR8Capacity { get; set; }
public int HearBeatSecond { get; set; }
public int IntervalMessageSecond { get; set; }
public int PIRContinueWorkingTime { get; set; }
public int PIRTriggerTimes { get; set; }
public int Protocolversionnumber { get; set; }
public int GatemagnetStatus { get; set; }
public int GatemagnetCurrentStatus { get; set; }
public int GatemagnetCapacity { get; set; }
public int PIR1Frequency { get; set; }
public int PIR2Frequency { get; set; }
public int PIR3Frequency { get; set; }
public int PIR4Frequency { get; set; }
public int PIR5Frequency { get; set; }
public int PIR6Frequency { get; set; }
public int PIR7Frequency { get; set; }
public int PIR8Frequency { get; set; }
public int Grsrpignal { get; set; }
public int csqpignal { get; set; }
}
public static String IntervalMessageToString(IntervalMessagePara para)
{
string retStr = "";
retStr += "RSSI信号：" + para.RSSISignal + ",";
retStr += "电压：" + para.CheckV + ",";
retStr += "功率：" + para.CheckP + ",";
retStr += "能耗:" + para.Energy + ",";
retStr += "锁在线状态:" + para.LockOnlineStatus + ",";
retStr += "PIR1状态:" + para.PIR1OnlineStatus + ",";
retStr += "PIR2状态:" + para.PIR2OnlineStatus + ",";
retStr += "PIR3状态:" + para.PIR3OnlineStatus + ",";
retStr += "PIR4状态:" + para.PIR4OnlineStatus + ",";
retStr += "电量:" + para.LockCapacity + ",";
retStr += "电量:" + para.PIR1Capacity + ",";
retStr += "电量:" + para.PIR2Capacity + ",";
retStr += "电量:" + para.PIR3Capacity + ",";
retStr += "电量:" + para.PIR4Capacity + ",";
retStr += "心跳包时间：" + para.HearBeatSecond + "s,";
retStr += "上报时间：" + para.IntervalMessageSecond + "s,";
retStr += "PIR工作时间：" + para.PIRContinueWorkingTime + "s,";
retStr += "PIR触发次数：" + para.PIRTriggerTimes + ",";
retStr += "协议版本号：" + para.Protocolversionnumber + ",";
retStr += "门磁在线状态:" + para.GatemagnetStatus + ",";
retStr += "当前状态:" + para.GatemagnetCurrentStatus + ",";
retStr += "电量:" + para.GatemagnetCapacity + ",";
retStr += "PIR5状态:" + para.PIR5OnlineStatus + ",";
retStr += "PIR6状态:" + para.PIR6OnlineStatus + ",";
retStr += "PIR7状态:" + para.PIR7OnlineStatus + ",";
retStr += "PIR8状态:" + para.PIR8OnlineStatus + ",";
retStr += "电量:" + para.PIR5Capacity + ",";
retStr += "电量:" + para.PIR6Capacity + ",";
retStr += "电量:" + para.PIR7Capacity + ",";
retStr += "电量:" + para.PIR8Capacity + ",";
retStr += "PIR5 次数:" + para.PIR5Frequency + ",";
retStr += "PIR6 次数:" + para.PIR6Frequency + ",";
retStr += "PIR7 次数:" + para.PIR7Frequency + ",";
retStr += "PIR8 次数:" + para.PIR8Frequency + ",";
retStr += "rsrp信号强度:" + para.Grsrpignal + ",";
retStr += "csq信号强度:" + para.csqpignal + ",";
return retStr;
}
public class CatchElectricityPara
{
public int action { get; set; }
public string movement { get; set; }
}
public static String CatchElecToString(CatchElectricityPara para)
{
Dictionary<int, string> dicDoorStat = new Dictionary<int, string>();
dicDoorStat.Add(0, "本地操作断电");
dicDoorStat.Add(1, "服务器命令断电");
dicDoorStat.Add(2, "本地操作取电");
dicDoorStat.Add(3, "开门触发取电");
dicDoorStat.Add(4, "服务器命令取电");
dicDoorStat.Add(5, "PIR触发取电");
dicDoorStat.Add(6, "PIR超时断电");
dicDoorStat.Add(7, "门磁开门取电");
dicDoorStat.Add(8, "电源保护断电");
dicDoorStat.Add(9, "上电同步取电");
string retStr = "";
retStr += "取电动作：" + dicDoorStat[para.action] + ",";
para.movement = dicDoorStat[para.action];
return retStr;
}
public class RequestServerPara
{
public int paratype { get; set; }
}
public static String RequestServerToString(RequestServerPara para)
{
Dictionary<int, string> dicDoorStat = new Dictionary<int, string>();
dicDoorStat.Add(1, "获取 MAC 地址信息");
dicDoorStat.Add(2, "获取时间");
string retStr = "";
retStr += "获取参数：" + dicDoorStat[para.paratype] + ",";
return retStr;
}
public class ServerSetConfigResponsePara
{
public int downparatype { get; set; }
public int downresult { get; set; }
}
public static String ServerSetConfigResponseToString(ServerSetConfigResponsePara para)
{
Dictionary<int, string> dicDoorStat = new Dictionary<int, string>();
dicDoorStat.Add(1, "设置 MAC 地址信息");
dicDoorStat.Add(2, "设置 PIR 参数");
dicDoorStat.Add(3, "设置心跳包时间与定期上报时间");
dicDoorStat.Add(4, "设置超功率检测判定");
Dictionary<int, string> dicDoorStat1 = new Dictionary<int, string>();
dicDoorStat1.Add(0, "无错误");
dicDoorStat1.Add(1, "未知参数");
string retStr = "";
retStr += "下发参数：" + dicDoorStat[para.downparatype] + ",";
retStr += "下发结果：" + dicDoorStat1[para.downresult] + ",";
return retStr;
}
public class DeviceInfoPara
{
public int infotype { get; set; }
public int md4gsoftverlow { get; set; }
public int md4gsoftverhigh { get; set; }
public int md4ghardver { get; set; }
public string md4gIMEI { get; set; }
public string md4gSIMICCID { get; set; }
public int blesoftverlow { get; set; }
public int blesoftverhigh { get; set; }
public int blehardver { get; set; }
public string bleMAC { get; set; }
public int pirsoftverlow { get; set; }
public int pirsoftverhigh { get; set; }
public int pirhardver { get; set; }
public string pirMAC { get; set; }
public string lockMAC { get; set; }
public string pir1MAC { get; set; }
public string pir2MAC { get; set; }
public string pir3MAC { get; set; }
public string pir4MAC { get; set; }
public string pir5MAC { get; set; }
public string pir6MAC { get; set; }
public string pir7MAC { get; set; }
public string pir8MAC { get; set; }
public string GatemagnetMAC { get; set; }
public int Gatemagnetgsoftverlow { get; set; }
public int Gatemagnetgsoftverhigh { get; set; }
public int Gatemagnetghardver { get; set; }
public int pir4gsoftverlow { get; set; }
public int pir4gsoftverhigh { get; set; }
public int pir4ghardver { get; set; }
public int pir3gsoftverlow { get; set; }
public int pir3gsoftverhigh { get; set; }
public int pir3ghardver { get; set; }
public int pir2gsoftverlow { get; set; }
public int pir2gsoftverhigh { get; set; }
public int pir2ghardver { get; set; }
public int pir1gsoftverlow { get; set; }
public int pir1gsoftverhigh { get; set; }
public int pir1ghardver { get; set; }
public int pir5gsoftverlow { get; set; }
public int pir5gsoftverhigh { get; set; }
public int pir5ghardver { get; set; }
public int pir6gsoftverlow { get; set; }
public int pir6gsoftverhigh { get; set; }
public int pir6ghardver { get; set; }
public int pir7gsoftverlow { get; set; }
public int pir7gsoftverhigh { get; set; }
public int pir7ghardver { get; set; }
public int pir8gsoftverlow { get; set; }
public int pir8gsoftverhigh { get; set; }
public int pir8ghardver { get; set; }
public int GatemagnetPower { get; set; }
public int pir1Power { get; set; }
public int pir2Power { get; set; }
public int pir3Power { get; set; }
public int pir4Power { get; set; }
public int pir5Power { get; set; }
public int pir6Power { get; set; }
public int pir7Power { get; set; }
public int pir8Power { get; set; }
public int maxelectricPower { get; set; }
public int protectionTime { get; set; }
//门磁开门断电使能状态
public int Dmdopoffe { get; set; }
//门磁关门取电使能状态
public int Mdoapte { get; set; }
public int Grsrpignal { get; set; }
public int csqpignal { get; set; }
public int IntervalMessageSecond { get; set; }
}
// P1 ~P4：经度(浮点数)
//P5 ~P8：纬度(浮点数)
//P9 ~P12：当前服务小区的ECI（整数）
//P13 ~P16：当前服务小区的TAC(整数)
//P17 ~P20：当前服务小区的eNBID(整数)
public class jizhanxx
{
public int infotype { get; set; }
//经度
public float longitude { get; set; }
//纬度
public float latitude { get; set; }
public int ECI { get; set; }
public int TAC { get; set; }
public int eNBID { get; set; }
public string jizhaninfo { get; set; }
public string didian { get; set; }
}
public class DoorMagneticReport
{
public int DoorMagneticOnlineStatues { get; set; }
public int DoorMagneticStatues { get; set; }
public int DoorMagneticQuantity { get; set; }
}
//public static string jizha (){
//}
public static String DeviceInfoResponseToString(DeviceInfoPara para)
{
Dictionary<int, string> dicDoorStat = new Dictionary<int, string>();
dicDoorStat.Add(1, "4G 版本信息");
dicDoorStat.Add(2, "BLE 版本信息");
dicDoorStat.Add(3, "RF 版本信息");
dicDoorStat.Add(4, "RF MAC地址信息");
dicDoorStat.Add(5, "RF版本信息");
dicDoorStat.Add(6, "RF通讯成功率");
dicDoorStat.Add(7, "超功率检测判定");
string retStr = "";
retStr += "消息类型：" + dicDoorStat[para.infotype] + ",";
if (para.infotype == 1)//4G模块
{
retStr += "4G软件版本低字节：" + para.md4gsoftverlow + ",";
retStr += "4G软件版本高字节：" + para.md4gsoftverhigh + ",";
retStr += "4G硬件版本：" + para.md4ghardver + ",";
retStr += "IMEI：" + para.md4gIMEI + ",";
retStr += "SIM ICCID：" + para.md4gSIMICCID + ",";
retStr += "门磁开门取电使能状态：" + para.Mdoapte + ",";
retStr += "门磁关门断电使能状态：" + para.Dmdopoffe + ",";
retStr += "定期上报时间：" + para.IntervalMessageSecond + ",";
retStr += "rsrp 接收信号强度：" + para.Grsrpignal + ",";
retStr += "csq 信号强度：" + para.csqpignal + ",";
////paradv. = BitConverter.ToUInt16(btPara, 41);
////paradv. = (int)btPara[43];
////paradv. = (int)btPara[44];
}
else if (para.infotype == 2)
{
retStr += "ble软件版本低字节：" + para.blesoftverlow + ",";
retStr += "ble软件版本高字节：" + para.blesoftverhigh + ",";
retStr += "ble硬件版本：" + para.blehardver + ",";
retStr += "ble MAC：" + para.bleMAC + ",";
}
else if (para.infotype == 3)
{
retStr += "pir软件版本低字节：" + para.pirsoftverlow + ",";
retStr += "pir软件版本高字节：" + para.pirsoftverhigh + ",";
retStr += "pir硬件版本：" + para.pirhardver + ",";
retStr += "pir MAC：" + para.pirMAC + ",";
}
else if (para.infotype == 4)
{
retStr += "锁 MAC：" + para.lockMAC + ",";
retStr += "pir 1 MAC：" + para.pir1MAC + ",";
retStr += "pir 2 MAC：" + para.pir2MAC + ",";
retStr += "pir 3 MAC：" + para.pir3MAC + ",";
retStr += "pir 4 MAC：" + para.pir4MAC + ",";
retStr += "门磁 MAC：" + para.GatemagnetMAC + ",";
retStr += "pir 5 MAC：" + para.pir5MAC + ",";
retStr += "pir 6 MAC：" + para.pir6MAC + ",";
retStr += "pir 7 MAC：" + para.pir7MAC + ",";
retStr += "pir 8 MAC：" + para.pir8MAC + ",";
}
else if (para.infotype == 5)
{
retStr += "门磁软件低版本：" + para.Gatemagnetgsoftverlow + ",";
retStr += "门磁软件高版本：" + para.Gatemagnetgsoftverhigh + ",";
retStr += "门磁硬件版本：" + para.Gatemagnetghardver + ",";
retStr += "PIR1软件低版本：" + para.pir1gsoftverlow + ",";
retStr += "PIR1软件高版本：" + para.pir1gsoftverhigh + ",";
retStr += "PIR1硬件版本：" + para.pir1ghardver + ",";
retStr += "PIR2软件低版本：" + para.pir2gsoftverlow + ",";
retStr += "PIR2软件高版本：" + para.pir2gsoftverhigh + ",";
retStr += "PIR2硬件版本：" + para.pir2ghardver + ",";
retStr += "PIR3软件低版本：" + para.pir3gsoftverlow + ",";
retStr += "PIR3软件高版本：" + para.pir3gsoftverhigh + ",";
retStr += "PIR3硬件版本：" + para.pir3ghardver + ",";
retStr += "PIR4软件低版本：" + para.pir4gsoftverlow + ",";
retStr += "PIR4软件高版本：" + para.pir4gsoftverhigh + ",";
retStr += "PIR4硬件版本：" + para.pir4ghardver + ",";
retStr += "PIR5软件低版本：" + para.pir5gsoftverlow + ",";
retStr += "PIR5软件高版本：" + para.pir5gsoftverhigh + ",";
retStr += "PIR5硬件版本：" + para.pir5ghardver + ",";
retStr += "PIR6软件低版本：" + para.pir6gsoftverlow + ",";
retStr += "PIR6软件高版本：" + para.pir6gsoftverhigh + ",";
retStr += "PIR6硬件版本：" + para.pir6ghardver + ",";
retStr += "PIR7软件低版本：" + para.pir7gsoftverlow + ",";
retStr += "PIR7软件高版本：" + para.pir7gsoftverhigh + ",";
retStr += "PIR7硬件版本：" + para.pir7ghardver + ",";
retStr += "PIR8软件低版本：" + para.pir8gsoftverlow + ",";
retStr += "PIR8软件高版本：" + para.pir8gsoftverhigh + ",";
retStr += "PIR8硬件版本：" + para.pir8ghardver + ",";
}
else if (para.infotype == 6)
{
retStr += "门磁通讯成功率：" + para.GatemagnetPower + "%,";
retStr += "PIR1通讯成功率：" + para.pir1Power + "%,";
retStr += "PIR2通讯成功率：" + para.pir2Power + "%,";
retStr += "PIR3通讯成功率：" + para.pir3Power + "%,";
retStr += "PIR4通讯成功率：" + para.pir4Power + "%,";
retStr += "PIR5通讯成功率：" + para.pir5Power + "%,";
retStr += "PIR6通讯成功率：" + para.pir6Power + "%,";
retStr += "PIR7通讯成功率：" + para.pir7Power + "%,";
retStr += "PIR8通讯成功率：" + para.pir8Power + "%,";
}
else if (para.infotype == 7)
{
retStr += "允许最大用电功率：" + para.maxelectricPower + "W,";
retStr += "保护判定时间：" + para.protectionTime + "s,";
}
return retStr;
}
public class DeviceStartPara
{
public string reason;
public int verLow;
public int verHigh;
public int verHardware;
public string strHex;
}
public static String DeviceStartInfoToString(DeviceStartPara para)
{
string retStr = "";
retStr += "启动原因：" + para.reason + ",";
retStr += "版本高字节：" + para.verLow + ",";
retStr += "版本低字节：" + para.verHigh + ",";
retStr += "硬件版本：" + para.verHardware + ",";
retStr += "启动异常信息内容：" + para.strHex + ",";
return retStr;
}
//public static string count = "";
public static String AnalysisByte(string base64Str, string devicename)
{
string retStr = "";
try
{
byte[] btTmp = Convert.FromBase64String(base64Str);
retStr += "原始数据： " + GetHexStringFromByteArray(btTmp) + ",";
FixedHeaderPart headerPart = AnalysisFixedHeader(btTmp);
//0 固定头
if (btTmp[0] != 0xA6)
{
Logger.LogCalloutanaSide("\r,收到一包数据，首字节标志不是0xa6\r,内容为：" + String.Join(",", btTmp));
return "Error Format !!!";
}
//10 当前取电状态
string strResult = "";
headerPart.PowerStatus = btTmp[10];
if (btTmp[10] == 0x00)
strResult = "无效";
else if (btTmp[10] == 0x01)
strResult = "取电";
else if (btTmp[10] == 0x02)
strResult = "断电";
else
strResult = "无效值";
retStr += "解包后数据 包序号:" + headerPart.SN + ",";
//retStr += "timpstamp:" + ConvertTimeStampToDateTime(headerPart.TimeStamp).ToString("yyyy-MM-dd HH:mm:ss") + ".";
retStr += "length：" + headerPart.LEN + ",";
//retStr += "check:" + headerPart.CHECK + ".,";
retStr += "设备当前取电状态:" + strResult + ",";
//string yuanshidata = GetHexStringFromByteArray(btTmp);
//11 命令
byte[] parab4 = { };
byte[] btPara = { };
switch (btTmp[11])
{
case 0x01: //设备上报心跳包
HeartBeatPara para = AnalysisHeartbeatPara(btTmp.Skip(12).ToArray());
retStr += "设备心跳包 ," + HearBeatToString(para);
break;
case 0x02: //设备上报门锁动作
DoorLockActionPara parad = AnalysisDoorlockActionPara(btTmp.Skip(12).ToArray());
retStr += "设备上报门锁动作 , " + DoorLockActionToString(parad);
break;
case 0x03: //设备上报PIR动作
PIRActionPara paraPIR = AnalysisPIRActionPara(btTmp.Skip(12).ToArray());
retStr += "设备上报PIR动作 , " + PIRActionToString(paraPIR);
break;
case 0x04: //设备上报定期动作
IntervalMessagePara paraInt = AnalysisIntervalMessagePara(btTmp.Skip(12).ToArray());
retStr += "设备定期上报 ," + IntervalMessageToString(paraInt);
break;
case 0x05: //设备上报取电动作
CatchElectricityPara parace = AnalysisCatchElectrPara(btTmp.Skip(12).ToArray());
retStr += "设备取电动作上报 ," + CatchElecToString(parace);
break;
case 0x06: //设备请求云端信息
RequestServerPara parars = AnalysisReqSvrPara(btTmp.Skip(12).ToArray());
retStr += "设备获取云端服务器参数 ," + RequestServerToString(parars);
break;
case 0x07: //设备回复服务器设定参数动作
ServerSetConfigResponsePara parasscr = AnalysisRespOfSvrSetConfigPara(btTmp.Skip(12).ToArray());
retStr += "云端服务器设置参数 ," + ServerSetConfigResponseToString(parasscr);
break;
case 0x08: //设备回复服务器控制取电下发
//暂时没有参数。
retStr += "服务器控制取电下发 ,";
break;
case 0x09: //设备回复服务器查询设备消息
DeviceInfoPara paradv = AnalysisDeviceInfoPara(btTmp.Skip(12).ToArray());
retStr += "服务器查询设备信息 ," + DeviceInfoResponseToString(paradv);
break;
case 0x0C: //设备启动信息
DeviceStartPara paradvst = AnalysisDeviceStartPara(btTmp.Skip(12).ToArray());
retStr += "设备启动上报服务器信息 ," + DeviceStartInfoToString(paradvst);
break;
case 0x0D: // 获取设备位置信息
jizhanxx paradvst1 = Obtaindevicelocationinformation(btTmp.Skip(12).ToArray());
retStr += "获取设备位置信息 ," + jizha(paradvst1);
if (paradvst1 != null)
{
if (paradvst1.infotype == 0)
{
string strSqlCmd = "select DeviceName from deviceinformation where `Online`= 1 and DeviceName LIKE " + "'%" + devicename + "%'";
DataSet ds = new DataSet();
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
string sql = "update deviceinformation set districtinfo = '" + paradvst1.didian + "' where DeviceName = '" + row["DeviceName"] + "'";
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
//var strSqlname = sql;
cmd.CommandText = sql;
int retRecAffected = cmd.ExecuteNonQuery();
}
}
}
}
}
}
break;
case 0x0E://门磁动作上报
DoorMagneticReport paradvste = DoorMagneticReportinformation(btTmp.Skip(12).ToArray());
retStr += "门磁动作上报 ," + DoorMagnetic(paradvste);
break;
}
}
catch (Exception ex)
{
Logger.LogCalloutanaSide("\r,----------Exception in AnalysisByte-----------\r, Exception: " + ex.ToString());
}
return retStr;
}
public static DoorMagneticReport DoorMagneticReportinformation(byte[] btTmp)
{
// public int DoorMagneticOnlineStatues { get; set; }
//public int DoorMagneticStatues { get; set; }
//public int DoorMagneticQuantity { get; set; }
DoorMagneticReport doorMagneticReport = new DoorMagneticReport();
doorMagneticReport.DoorMagneticOnlineStatues = (int)btTmp[0];
doorMagneticReport.DoorMagneticStatues = (int)btTmp[1];
doorMagneticReport.DoorMagneticQuantity = (int)btTmp[2];
return doorMagneticReport;
}
public static string DoorMagnetic(DoorMagneticReport sds)
{
//Dictionary<int, string> dicDoorStat = new Dictionary<int, string>();
//dicDoorStat.Add(1, "开门");
//dicDoorStat.Add(2, "BLE 版本信息");
//dicDoorStat.Add(3, "RF 版本信息");
//dicDoorStat.Add(4, "RF MAC地址信息");
//dicDoorStat.Add(5, "RF版本信息");
//dicDoorStat.Add(6, "RF通讯成功率");
//dicDoorStat.Add(7, "超功率检测判定");
//string retStr = "";
string retStr = "";
retStr += "门磁在线状态:" + sds.DoorMagneticOnlineStatues + ",";
retStr += "门磁状态:" + sds.DoorMagneticStatues + ",";
retStr += "门磁电量:" + sds.DoorMagneticQuantity + "%,";
return retStr;
}
public static FixedHeaderPart AnalysisFixedHeader(byte[] btTmp)
{
FixedHeaderPart headerPart = new FixedHeaderPart();
//0 固定头
if (btTmp[0] != 0xA6)
{
Logger.LogCalloutanaSide("\r,收到一包数据，首字节标志不是0xa6\r,内容为：" + String.Join(",", btTmp));
return headerPart;
}
headerPart.FixedFlag = 0xA6;
//1,2 SN
byte[] b12 = btTmp.Skip(1).Take(2).ToArray();
//Array.Reverse(b12); //服务器机器和嵌入式设备都是小端序列
headerPart.SN = BitConverter.ToUInt16(b12, 0); // 第二个参数表示开始位置
//3,4,5,6 时间戳
headerPart.TimeStamp = BitConverter.ToUInt32(btTmp, 3);
//7,8 长度
byte[] b78 = btTmp.Skip(7).Take(2).ToArray();
//Array.Reverse(b78); //服务器机器和嵌入式设备都是小端序列
headerPart.LEN = BitConverter.ToUInt16(b78, 0); // 第二个参数表示开始位置
//9 校验和
headerPart.CHECK = btTmp[9];
//10 当前取电状态
headerPart.PowerStatus = btTmp[10];
headerPart.CMD = btTmp[11];
return headerPart;
}
public static HeartBeatPara AnalysisHeartbeatPara(byte[] btPara)
{
byte[] parab4 = { };
HeartBeatPara para = new HeartBeatPara();
//0 RSSI值，有符号字节值
para.RSSI = (sbyte)btPara[0];
//1234 电压 浮点数
parab4 = btPara.Skip(1).Take(4).ToArray();
para.voltage = BitConverter.ToSingle(parab4, 0);
//5678 功率 浮点数
parab4 = btPara.Skip(5).Take(4).ToArray();
para.power = BitConverter.ToSingle(parab4, 0);
//9101112 能耗 浮点数
parab4 = btPara.Skip(9).Take(4).ToArray();
para.energyconsumption = BitConverter.ToSingle(parab4, 0);
//13 门锁是否在线
para.doorlockonline = (int)btPara[13];
//14 门锁在线的话，门的状态
para.strHexdoorlockstatus = GetHexStringFromByteArray(new byte[] { btPara[14] });
para.isLockFsfsOn = false;
if ((btPara[14] & 0x01) == 0x01)
{
para.isLockFsfsOn = true;
}
para.isLockLowCapIndOn = false;
if ((btPara[14] & 0x02) == 0x02)
{
para.isLockLowCapIndOn = true;
}
para.isLockFakIndOn = false;
if ((btPara[14] & 0x04) == 0x04)
{
para.isLockFakIndOn = true;
}
para.doorlockcapacity = (int)btPara[15];
return para;
}
public static DoorLockActionPara AnalysisDoorlockActionPara(byte[] btPara)
{
DoorLockActionPara parad = new DoorLockActionPara();
parad.dooronlinestatus = (int)btPara[0];
parad.doorstatus = (int)btPara[1];
parad.doorcapacity = (int)btPara[2];
return parad;
}
public static PIRActionPara AnalysisPIRActionPara(byte[] btPara)
{
PIRActionPara paraPIR = new PIRActionPara();
if (btPara.Length > 14)
{
paraPIR.PIRAction = (int)btPara[0];
paraPIR.PIRTotalTriggerTimes = (int)btPara[1];
paraPIR.PIR1Times = (int)btPara[2];
paraPIR.PIR2Times = (int)btPara[3];
paraPIR.PIR3Times = (int)btPara[4];
paraPIR.PIR4Times = (int)btPara[5];
paraPIR.PIR1OnlineStatus = (int)btPara[6];
paraPIR.PIR2OnlineStatus = (int)btPara[7];
paraPIR.PIR3OnlineStatus = (int)btPara[8];
paraPIR.PIR4OnlineStatus = (int)btPara[9];
paraPIR.PIR1Capacity = (int)btPara[10];
paraPIR.PIR2Capacity = (int)btPara[11];
paraPIR.PIR3Capacity = (int)btPara[12];
paraPIR.PIR4Capacity = (int)btPara[13];
paraPIR.PIR5Times = (int)btPara[14];
paraPIR.PIR6Times = (int)btPara[15];
paraPIR.PIR7Times = (int)btPara[16];
paraPIR.PIR8Times = (int)btPara[17];
paraPIR.PIR5OnlineStatus = (int)btPara[18];
paraPIR.PIR6OnlineStatus = (int)btPara[19];
paraPIR.PIR7OnlineStatus = (int)btPara[20];
paraPIR.PIR8OnlineStatus = (int)btPara[21];
paraPIR.PIR5Capacity = (int)btPara[22];
paraPIR.PIR6Capacity = (int)btPara[23];
paraPIR.PIR7Capacity = (int)btPara[24];
paraPIR.PIR8Capacity = (int)btPara[25];
}
else
{
paraPIR.PIRAction = (int)btPara[0];
paraPIR.PIRTotalTriggerTimes = (int)btPara[1];
paraPIR.PIR1Times = (int)btPara[2];
paraPIR.PIR2Times = (int)btPara[3];
paraPIR.PIR3Times = (int)btPara[4];
paraPIR.PIR4Times = (int)btPara[5];
paraPIR.PIR1OnlineStatus = (int)btPara[6];
paraPIR.PIR2OnlineStatus = (int)btPara[7];
paraPIR.PIR3OnlineStatus = (int)btPara[8];
paraPIR.PIR4OnlineStatus = (int)btPara[9];
paraPIR.PIR1Capacity = (int)btPara[10];
paraPIR.PIR2Capacity = (int)btPara[11];
paraPIR.PIR3Capacity = (int)btPara[12];
paraPIR.PIR4Capacity = (int)btPara[13];
}
return paraPIR;
}
public static IntervalMessagePara AnalysisIntervalMessagePara(byte[] btPara)
{
IntervalMessagePara paraInt = new IntervalMessagePara();
if (btPara.Length == 40)
{
paraInt.RSSISignal = (sbyte)btPara[0];
paraInt.CheckV = BitConverter.ToSingle(btPara.Skip(1).Take(4).ToArray(), 0);
paraInt.CheckP = BitConverter.ToSingle(btPara.Skip(5).Take(4).ToArray(), 0);
paraInt.Energy = BitConverter.ToSingle(btPara.Skip(9).Take(4).ToArray(), 0);
paraInt.LockOnlineStatus = (int)btPara[13];
paraInt.PIR1OnlineStatus = (int)btPara[14];
paraInt.PIR2OnlineStatus = (int)btPara[15];
paraInt.PIR3OnlineStatus = (int)btPara[16];
paraInt.PIR4OnlineStatus = (int)btPara[17];
paraInt.LockCapacity = (int)btPara[18];
paraInt.PIR1Capacity = (int)btPara[19];
paraInt.PIR2Capacity = (int)btPara[20];
paraInt.PIR3Capacity = (int)btPara[21];
paraInt.PIR4Capacity = (int)btPara[22];
paraInt.HearBeatSecond = BitConverter.ToUInt16(btPara, 23);
paraInt.IntervalMessageSecond = BitConverter.ToUInt16(btPara, 25);
paraInt.PIRContinueWorkingTime = BitConverter.ToUInt16(btPara, 27);
paraInt.PIRTriggerTimes = BitConverter.ToUInt16(btPara, 29);
paraInt.Protocolversionnumber = BitConverter.ToUInt16(btPara, 31);
//BitConverter.ToUInt16(btPara, 30);
paraInt.GatemagnetStatus = (int)btPara[33];
paraInt.GatemagnetCurrentStatus = (int)btPara[34];
paraInt.GatemagnetCapacity = (int)btPara[35];
paraInt.PIR1Frequency = (int)btPara[36];
paraInt.PIR2Frequency = (int)btPara[37];
paraInt.PIR3Frequency = (int)btPara[38];
paraInt.PIR4Frequency = (int)btPara[39];
}
else if (btPara.Length > 40)
{
paraInt.RSSISignal = (sbyte)btPara[0];
paraInt.CheckV = BitConverter.ToSingle(btPara.Skip(1).Take(4).ToArray(), 0);
paraInt.CheckP = BitConverter.ToSingle(btPara.Skip(5).Take(4).ToArray(), 0);
paraInt.Energy = BitConverter.ToSingle(btPara.Skip(9).Take(4).ToArray(), 0);
paraInt.LockOnlineStatus = (int)btPara[13];
paraInt.PIR1OnlineStatus = (int)btPara[14];
paraInt.PIR2OnlineStatus = (int)btPara[15];
paraInt.PIR3OnlineStatus = (int)btPara[16];
paraInt.PIR4OnlineStatus = (int)btPara[17];
paraInt.LockCapacity = (int)btPara[18];
paraInt.PIR1Capacity = (int)btPara[19];
paraInt.PIR2Capacity = (int)btPara[20];
paraInt.PIR3Capacity = (int)btPara[21];
paraInt.PIR4Capacity = (int)btPara[22];
paraInt.HearBeatSecond = BitConverter.ToUInt16(btPara, 23);
paraInt.IntervalMessageSecond = BitConverter.ToUInt16(btPara, 25);
paraInt.PIRContinueWorkingTime = BitConverter.ToUInt16(btPara, 27);
paraInt.PIRTriggerTimes = BitConverter.ToUInt16(btPara, 29);
paraInt.Protocolversionnumber = BitConverter.ToUInt16(btPara, 31);
//BitConverter.ToUInt16(btPara, 30);
paraInt.GatemagnetStatus = (int)btPara[33];
paraInt.GatemagnetCurrentStatus = (int)btPara[34];
paraInt.GatemagnetCapacity = (int)btPara[35];
paraInt.PIR1Frequency = (int)btPara[36];
paraInt.PIR2Frequency = (int)btPara[37];
paraInt.PIR3Frequency = (int)btPara[38];
paraInt.PIR4Frequency = (int)btPara[39];
paraInt.PIR5Frequency = (int)btPara[40];
paraInt.PIR6Frequency = (int)btPara[41];
paraInt.PIR7Frequency = (int)btPara[42];
paraInt.PIR8Frequency = (int)btPara[43];
paraInt.PIR5OnlineStatus = (int)btPara[44];
paraInt.PIR6OnlineStatus = (int)btPara[45];
paraInt.PIR7OnlineStatus = (int)btPara[46];
paraInt.PIR8OnlineStatus = (int)btPara[47];
paraInt.PIR5Capacity = (int)btPara[48];
paraInt.PIR6Capacity = (int)btPara[49];
paraInt.PIR7Capacity = (int)btPara[50];
paraInt.PIR8Capacity = (int)btPara[51];
paraInt.Grsrpignal = (sbyte)btPara[52];
paraInt.csqpignal = (int)btPara[53];
}
else
{
paraInt.RSSISignal = (sbyte)btPara[0];
paraInt.CheckV = BitConverter.ToSingle(btPara.Skip(1).Take(4).ToArray(), 0);
paraInt.CheckP = BitConverter.ToSingle(btPara.Skip(5).Take(4).ToArray(), 0);
paraInt.Energy = BitConverter.ToSingle(btPara.Skip(9).Take(4).ToArray(), 0);
paraInt.LockOnlineStatus = (int)btPara[13];
paraInt.PIR1OnlineStatus = (int)btPara[14];
paraInt.PIR2OnlineStatus = (int)btPara[15];
paraInt.PIR3OnlineStatus = (int)btPara[16];
paraInt.PIR4OnlineStatus = (int)btPara[17];
paraInt.LockCapacity = (int)btPara[18];
paraInt.PIR1Capacity = (int)btPara[19];
paraInt.PIR2Capacity = (int)btPara[20];
paraInt.PIR3Capacity = (int)btPara[21];
paraInt.PIR4Capacity = (int)btPara[22];
paraInt.HearBeatSecond = BitConverter.ToUInt16(btPara, 23);
paraInt.IntervalMessageSecond = BitConverter.ToUInt16(btPara, 25);
paraInt.PIRContinueWorkingTime = BitConverter.ToUInt16(btPara, 27);
paraInt.PIRTriggerTimes = BitConverter.ToUInt16(btPara, 29);
//paraInt.Protocolversionnumber = BitConverter.ToUInt16(btPara, 30);
//paraInt.GatemagnetStatus = (int)btPara[32];
//paraInt.GatemagnetCapacity = (int)btPara[33];
//paraInt.PIR1Frequency = (int)btPara[34];
//paraInt.PIR2Frequency = (int)btPara[35];
//paraInt.PIR3Frequency = (int)btPara[36];
//paraInt.PIR4Frequency = (int)btPara[37];
}
// public int Protocolversionnumber { get; set; }
//public int GatemagnetStatus { get; set; }
//public int GatemagnetCapacity { get; set; }
//public int PIR1Frequency { get; set; }
//public int PIR2Frequency { get; set; }
//public int PIR3Frequency { get; set; }
//public int PIR4Frequency { get; set; }
return paraInt;
}
public static CatchElectricityPara AnalysisCatchElectrPara(byte[] btPara)
{
CatchElectricityPara parace = new CatchElectricityPara();
parace.action = (int)btPara[0];
return parace;
}
public static RequestServerPara AnalysisReqSvrPara(byte[] btPara)
{
RequestServerPara parars = new RequestServerPara();
parars.paratype = (int)btPara[0];
return parars;
}
public static ServerSetConfigResponsePara AnalysisRespOfSvrSetConfigPara(byte[] btPara)
{
ServerSetConfigResponsePara parasscr = new ServerSetConfigResponsePara();
parasscr.downparatype = (int)btPara[0];
parasscr.downresult = (int)btPara[1];
return parasscr;
}
public static DeviceInfoPara AnalysisDeviceInfoPara(byte[] btPara)
{
DeviceInfoPara paradv = new DeviceInfoPara();
if (btPara.Length > 39)
{
paradv.infotype = (int)btPara[0];
if (paradv.infotype == 1)//4G模块
{
paradv.md4gsoftverlow = (int)btPara[1];
paradv.md4gsoftverhigh = (int)btPara[2];
paradv.md4ghardver = (int)btPara[3];
paradv.md4gIMEI = System.Text.Encoding.UTF8.GetString(btPara.Skip(4).Take(15).ToArray());
paradv.md4gSIMICCID = System.Text.Encoding.UTF8.GetString(btPara.Skip(19).Take(20).ToArray());
paradv.Mdoapte = (int)btPara[39];
paradv.Dmdopoffe = (int)btPara[40];
paradv.IntervalMessageSecond = BitConverter.ToUInt16(btPara, 41);
paradv.Grsrpignal = (sbyte)btPara[43];
paradv.csqpignal = (int)btPara[44];
//
//
//
}
else if (paradv.infotype == 2)//BLE version
{
paradv.blesoftverlow = (int)btPara[1];
paradv.blesoftverhigh = (int)btPara[2];
paradv.blehardver = (int)btPara[3];
paradv.bleMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(4).Take(6).ToArray());
}
else if (paradv.infotype == 3)//RF version
{
paradv.pirsoftverlow = (int)btPara[1];
paradv.pirsoftverhigh = (int)btPara[2];
paradv.pirhardver = (int)btPara[3];
paradv.pirMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(4).Take(6).ToArray());
}
else if (paradv.infotype == 4)//RF MAC
{
paradv.lockMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(1).Take(6).ToArray());
paradv.pir1MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(7).Take(6).ToArray());
paradv.pir2MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(13).Take(6).ToArray());
paradv.pir3MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(19).Take(6).ToArray());
paradv.pir4MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(25).Take(6).ToArray());
paradv.GatemagnetMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(31).Take(6).ToArray());
paradv.pir5MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(37).Take(6).ToArray());
paradv.pir6MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(43).Take(6).ToArray());
paradv.pir7MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(49).Take(6).ToArray());
paradv.pir8MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(55).Take(6).ToArray());
}
else if (paradv.infotype == 5) //查询RF设备版本信息
{
paradv.Gatemagnetgsoftverlow = (int)btPara[1];
paradv.Gatemagnetgsoftverhigh = (int)btPara[2];
paradv.Gatemagnetghardver = (int)btPara[3];
paradv.pir1gsoftverlow = (int)btPara[4];
paradv.pir1gsoftverhigh = (int)btPara[5];
paradv.pir1ghardver = (int)btPara[6];
paradv.pir2gsoftverlow = (int)btPara[7];
paradv.pir2gsoftverhigh = (int)btPara[8];
paradv.pir2ghardver = (int)btPara[9];
paradv.pir3gsoftverlow = (int)btPara[10];
paradv.pir3gsoftverhigh = (int)btPara[11];
paradv.pir3ghardver = (int)btPara[12];
paradv.pir4gsoftverlow = (int)btPara[13];
paradv.pir4gsoftverhigh = (int)btPara[14];
paradv.pir4ghardver = (int)btPara[15];
paradv.pir5gsoftverlow = (int)btPara[16];
paradv.pir5gsoftverhigh = (int)btPara[17];
paradv.pir5ghardver = (int)btPara[18];
paradv.pir6gsoftverlow = (int)btPara[19];
paradv.pir6gsoftverhigh = (int)btPara[20];
paradv.pir6ghardver = (int)btPara[21];
paradv.pir7gsoftverlow = (int)btPara[22];
paradv.pir7gsoftverhigh = (int)btPara[23];
paradv.pir7ghardver = (int)btPara[24];
paradv.pir8gsoftverlow = (int)btPara[25];
paradv.pir8gsoftverhigh = (int)btPara[26];
paradv.pir8ghardver = (int)btPara[27];
}
else if (paradv.infotype == 6)//查询RF及门磁设备通讯成功率
{
paradv.GatemagnetPower = (int)btPara[1];
paradv.pir1Power = (int)btPara[2];
paradv.pir2Power = (int)btPara[3];
paradv.pir3Power = (int)btPara[4];
paradv.pir4Power = (int)btPara[5];
paradv.pir5Power = (int)btPara[6];
paradv.pir6Power = (int)btPara[7];
paradv.pir7Power = (int)btPara[8];
paradv.pir8Power = (int)btPara[9];
}
else if (paradv.infotype == 7) //超功率检测判定
{
paradv.maxelectricPower = (int)BitConverter.ToUInt32(btPara.Skip(1).Take(4).ToArray(), 0);
paradv.protectionTime = (int)BitConverter.ToUInt32(btPara.Skip(5).Take(4).ToArray(), 0);
}
}
else
{
paradv.infotype = (int)btPara[0];
if (paradv.infotype == 1)//4G模块
{
paradv.md4gsoftverlow = (int)btPara[1];
paradv.md4gsoftverhigh = (int)btPara[2];
paradv.md4ghardver = (int)btPara[3];
paradv.md4gIMEI = System.Text.Encoding.UTF8.GetString(btPara.Skip(4).Take(15).ToArray());
paradv.md4gSIMICCID = System.Text.Encoding.UTF8.GetString(btPara.Skip(19).Take(20).ToArray());
}
else if (paradv.infotype == 2)//BLE version
{
paradv.blesoftverlow = (int)btPara[1];
paradv.blesoftverhigh = (int)btPara[2];
paradv.blehardver = (int)btPara[3];
paradv.bleMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(4).Take(6).ToArray());
}
else if (paradv.infotype == 3)//RF version
{
paradv.pirsoftverlow = (int)btPara[1];
paradv.pirsoftverhigh = (int)btPara[2];
paradv.pirhardver = (int)btPara[3];
paradv.pirMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(4).Take(6).ToArray());
}
else if (paradv.infotype == 4)//RF MAC
{
paradv.lockMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(1).Take(6).ToArray());
paradv.pir1MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(7).Take(6).ToArray());
paradv.pir2MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(13).Take(6).ToArray());
paradv.pir3MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(19).Take(6).ToArray());
paradv.pir4MAC = GetHexStringFromByteArrayForMAC(btPara.Skip(25).Take(6).ToArray());
paradv.GatemagnetMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(31).Take(6).ToArray());
}
else if (paradv.infotype == 5) //查询RF设备版本信息
{
if (btPara.Length == 16)
{
paradv.Gatemagnetgsoftverlow = (int)btPara[1];
paradv.Gatemagnetgsoftverhigh = (int)btPara[2];
paradv.Gatemagnetghardver = (int)btPara[3];
paradv.pir1gsoftverlow = (int)btPara[4];
paradv.pir1gsoftverhigh = (int)btPara[5];
paradv.pir1ghardver = (int)btPara[6];
paradv.pir2gsoftverlow = (int)btPara[7];
paradv.pir2gsoftverhigh = (int)btPara[8];
paradv.pir2ghardver = (int)btPara[9];
paradv.pir3gsoftverlow = (int)btPara[10];
paradv.pir3gsoftverhigh = (int)btPara[11];
paradv.pir3ghardver = (int)btPara[12];
paradv.pir4gsoftverlow = (int)btPara[13];
paradv.pir4gsoftverhigh = (int)btPara[14];
paradv.pir4ghardver = (int)btPara[15];
}
else
{
paradv.Gatemagnetgsoftverlow = (int)btPara[1];
paradv.Gatemagnetgsoftverhigh = (int)btPara[2];
paradv.Gatemagnetghardver = (int)btPara[3];
paradv.pir1gsoftverlow = (int)btPara[4];
paradv.pir1gsoftverhigh = (int)btPara[5];
paradv.pir1ghardver = (int)btPara[6];
paradv.pir2gsoftverlow = (int)btPara[7];
paradv.pir2gsoftverhigh = (int)btPara[8];
paradv.pir2ghardver = (int)btPara[9];
paradv.pir3gsoftverlow = (int)btPara[10];
paradv.pir3gsoftverhigh = (int)btPara[11];
paradv.pir3ghardver = (int)btPara[12];
paradv.pir4gsoftverlow = (int)btPara[13];
paradv.pir4gsoftverhigh = (int)btPara[14];
paradv.pir4ghardver = (int)btPara[15];
paradv.pir5gsoftverlow = (int)btPara[16];
paradv.pir5gsoftverhigh = (int)btPara[17];
paradv.pir5ghardver = (int)btPara[18];
paradv.pir6gsoftverlow = (int)btPara[19];
paradv.pir6gsoftverhigh = (int)btPara[20];
paradv.pir6ghardver = (int)btPara[21];
paradv.pir7gsoftverlow = (int)btPara[22];
paradv.pir7gsoftverhigh = (int)btPara[23];
paradv.pir7ghardver = (int)btPara[24];
paradv.pir8gsoftverlow = (int)btPara[25];
paradv.pir8gsoftverhigh = (int)btPara[26];
paradv.pir8ghardver = (int)btPara[27];
}
}
else if (paradv.infotype == 6)//查询RF及门磁设备通讯成功率
{
paradv.GatemagnetPower = (int)btPara[1];
paradv.pir1Power = (int)btPara[2];
paradv.pir2Power = (int)btPara[3];
paradv.pir3Power = (int)btPara[4];
paradv.pir4Power = (int)btPara[5];
paradv.pir5Power = (int)btPara[6];
paradv.pir6Power = (int)btPara[7];
paradv.pir7Power = (int)btPara[8];
paradv.pir8Power = (int)btPara[9];
}
else if (paradv.infotype == 7) //超功率检测判定
{
paradv.maxelectricPower = (int)BitConverter.ToUInt32(btPara.Skip(1).Take(4).ToArray(), 0);
paradv.protectionTime = (int)BitConverter.ToUInt32(btPara.Skip(5).Take(4).ToArray(), 0);
}
}
return paradv;
}
public static DeviceStartPara AnalysisDeviceStartPara(byte[] btPara)
{
Dictionary<int, string> res = new Dictionary<int, string>();
res.Add(0, "上电开机");
res.Add(1, "充电或者AT指令下载完成后开机");
res.Add(2, "闹钟开机");
res.Add(3, "软件重启");
res.Add(4, "未知原因");
res.Add(5, "Reset键");
res.Add(6, "异常重启");
res.Add(7, "工具控制重启");
res.Add(8, "内部看门狗重启");
res.Add(9, "外部重启");
res.Add(10, "充电开机");
DeviceStartPara paradv = new DeviceStartPara();
int iRes = (int)btPara[0];
paradv.reason = res[iRes];
paradv.verLow = (int)btPara[1];
//GetHexStringFromByteArray(btPara.Skip(1).Take(1).ToArray());
paradv.verHigh = (int)btPara[2];
//GetHexStringFromByteArray(btPara.Skip(2).Take(1).ToArray());
paradv.verHardware = (int)btPara[3];
paradv.strHex = System.Text.Encoding.UTF8.GetString(btPara.Skip(4).ToArray());
//byte[] byteArray = System.Text.Encoding.Default.GetBytes(strHex);
//= System.Text.Encoding.UTF8.GetString(byteArray);
return paradv;
}
//// P1 ~P4：经度(浮点数)
////P5 ~P8：纬度(浮点数)
////P9 ~P12：当前服务小区的ECI（整数）
////P13 ~P16：当前服务小区的TAC(整数)
////P17 ~P20：当前服务小区的eNBID(整数)
//public class jizhanxx
//{
// public int infotype { get; set; }
// //经度
// public float longitude { get; set; }
// //纬度
// public float latitude { get; set; }
// public int ECI { get; set; }
// public int TAC { get; set; }
// public int eNBID { get; set; }
//}
public static jizhanxx Obtaindevicelocationinformation(byte[] btPara)
{
jizhanxx paradv = new jizhanxx();
paradv.infotype = (int)btPara[0];
if (paradv.infotype == 0)//获取经纬度及当前连接基站信息
{
byte[] b12 = btPara.Skip(1).Take(4).ToArray();
BitConverter.ToSingle(btPara.Skip(5).Take(4).ToArray(), 0);
// 1 2 3 4
paradv.longitude = BitConverter.ToSingle(b12, 0);
// 5 6 7 8
paradv.latitude = BitConverter.ToSingle(btPara.Skip(5).Take(4).ToArray(), 0);
//(float)BitConverter.ToUInt32(btPara, 5);
//9 10 11 12
paradv.ECI = (int)BitConverter.ToUInt32(btPara.Skip(9).Take(4).ToArray(), 0);
// 13 14 15 16
paradv.TAC = (int)BitConverter.ToUInt32(btPara.Skip(13).Take(4).ToArray(), 0);
// 17 18 19 20
paradv.eNBID = (int)BitConverter.ToUInt32(btPara.Skip(17).Take(4).ToArray(), 0);
}
else if (paradv.infotype == 1)//获取附件基站信息
{
byte[] b12 = btPara.Skip(1).ToArray();
paradv.jizhaninfo = System.Text.Encoding.UTF8.GetString(b12);
//paradv.blesoftverlow = (int)btPara[1];
//paradv.blesoftverhigh = (int)btPara[2];
//paradv.blehardver = (int)btPara[3];
//paradv.bleMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(4).Take(6).ToArray());
}
else if (paradv.infotype == 2)//更新位置信息
{
paradv.jizhaninfo = "更新位置信息中";
//paradv.pirsoftv
//paradv.pirsoftverhigh = (int)btPara[2];
//paradv.pirhardver = (int)btPara[3];
//paradv.pirMAC = GetHexStringFromByteArrayForMAC(btPara.Skip(4).Take(6).ToArray());
}
return paradv;
}
public static string jizha(jizhanxx jizhanxx)
{
string retStr = "";
if (jizhanxx.infotype == 0)
{
retStr += "经度：" + jizhanxx.longitude + ",";
retStr += "纬度：" + jizhanxx.latitude + ",";
retStr += "当前服务小区的ECI：" + jizhanxx.ECI + ",";
retStr += "当前服务小区的TAC：" + jizhanxx.TAC + ",";
retStr += "当前服务小区的eNBID：" + jizhanxx.eNBID + ",";
string didian = Obtainlocationinformations(jizhanxx);
retStr += "位置：" + didian + ",";
jizhanxx.didian = didian;
}
else if (jizhanxx.infotype == 1)
{
retStr += "基站信息：" + jizhanxx.jizhaninfo + ",";
}
else
{
retStr += "更新信息：" + jizhanxx.jizhaninfo + ",";
}
return retStr;
}
public static string GetHexStringFromByteArrayForMAC(byte[] byteArr)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < byteArr.Length; i++)
{
sb.Append(byteArr[i].ToString("X2") + ":");
}
return sb.ToString();
}
public static string GetHexStringFromByteArray(byte[] byteArr)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < byteArr.Length; i++)
{
sb.Append(byteArr[i].ToString("X2") + " ");
}
return sb.ToString();
}
/// <summary>
/// 转换时间戳为C#时间
/// </summary>
/// <param name="timeStamp">时间戳 单位：毫秒</param>
/// <returns>C#时间</returns>
public static DateTime ConvertTimeStampToDateTime(long timeStamp)
{
DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1)); // 当地时区
DateTime dt = startTime.AddSeconds(timeStamp);
return dt;
}
//GetAllCallinRecordBYName
private static DataSet GetAllCallinRecordBYName(string Name, int zhant)
{
DataSet ds = new DataSet();
string strSqlCmd = "";
if (!string.IsNullOrEmpty(Name) && zhant != -1)
{
strSqlCmd = "select * from deviceinformation where DeviceName LIKE " + "'%" + Name + "%'" + "and Online = " + zhant;
}
else if (!string.IsNullOrEmpty(Name) && zhant == -1)
{
strSqlCmd = "select * from deviceinformation where DeviceName LIKE " + "'%" + Name + "%'";
//strSqlCmd = "select deviceinformation.DeviceName,Online,districtinfo, `timestamp` from deviceinformation left JOIN devicestate on deviceinformation.DeviceName = devicestate.deviceName where deviceinformation.DeviceName LIKE " + "'%" + Name + "%'";
}
else if (string.IsNullOrEmpty(Name) && zhant != -1)
{
//strSqlCmd = "select deviceinformation.DeviceName,Online,districtinfo, `timestamp` from deviceinformation left JOIN devicestate on deviceinformation.DeviceName = devicestate.deviceName where deviceinformation.Online=" + zhant;
strSqlCmd = "select * from deviceinformation where Online=" + zhant;
}
else
{
//strSqlCmd = "select deviceinformation.DeviceName,Online,districtinfo, timestamp from deviceinformation left JOIN devicestate on deviceinformation.DeviceName = devicestate.deviceName";
strSqlCmd = "select * from deviceinformation";
}
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
private static DataSet GetAllCallinRecordBYTime(string productid, string devicename, int shijian)
{
DataSet ds = new DataSet();
DateTime time = new DateTime();
if (shijian == 1)
{
time = DateTime.Now.AddHours(-1);
}
else if (shijian == 2)
{
time = DateTime.Now.AddHours(-2);
}
else if (shijian == 3)
{
time = DateTime.Now.AddHours(-5);
}
else if (shijian == 4)
{
time = DateTime.Now.AddMinutes(-30);
}
else if (shijian == -1)
{
time = DateTime.Now.AddSeconds(-60);
}
DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
int timeStamp = Convert.ToInt32((time - dateStart).TotalSeconds);
string strSqlCmd = "SELECT timeappreceive, productid,devicename,topic,payload,seq,timestamp,timemills FROM uploginfo where timestamp >= '" + timeStamp + "' and devicename='" + devicename + "' ORDER BY timestamp DESC";
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
private static DataSet GetAllCallinRecordBYTime(string devicename)
{
DataSet ds = new DataSet();
long kstime;
long jstime;
DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
DateTime jieshu = DateTime.Now.AddDays(-7);
kstime = Convert.ToInt64((jieshu - dateStart).TotalSeconds);
//person = person.Where(x => x.timestamp >= kstime).OrderByDescending(x => x.timestamp).ToList();
string strSqlCmd = "SELECT id, timeappreceive, productid,devicename,topic,payload,seq,timestamp,timemills FROM uploginfo where devicename='" + devicename + "' and timestamp>=" + kstime + " ORDER BY timemills DESC";
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
private static DataSet GetAllCallinRecord()
{
DataSet ds = new DataSet();
//string strSqlCmd = "SELECT timeappreceive, productid,devicename,topic,payload,seq,timestamp,timemills FROM uploginfo ORDER BY timestamp DESC";
string strSqlCmd = "select DeviceName,Online,districtinfo,LastUpdateTime from deviceinformation where `Online`= 1 ";
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
public static void ClearAllCallinRecord()
{
DataSet ds = new DataSet();
//DELETE FROM table_name
//WHERE[condition];
string strSqlCmd = "DELETE FROM uploginfo";
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand(strSqlCmd))
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(cmd);
command.Fill(ds);
}
}
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
}
#endregion
public static void InsertCallin(JObject mqttmessage)
{
string strSqlCmd = "INSERT INTO uploginfo ( productid, devicename, topic, payload, seq, timestamp, timemills, timeappreceive) "
+ "VALUES('" + mqttmessage["productid"] + "','"
+ mqttmessage["devicename"] + "', '"
+ mqttmessage["topic"] + "', '"
+ mqttmessage["payload"] + "', '"
+ mqttmessage["seq"] + "', '"
+ mqttmessage["timestamp"] + "', '"
+ mqttmessage["timemills"] + "', '"
+ DateTime.Now.ToString() + "')";
try
{
//var UserlogList1 = dalHelperCustom.DataRowToModels(dataSetes.Tables[0]);
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
var strSql = strSqlCmd;
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
//Logger.LogDownSide("in exception; sql is: " + strSqlCmd);
//Logger.LogDownSide("in exception; exception is: " + ex.ToString());
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
}
public static DataSet QueryCm6TSP()
{
DataSet ds = new DataSet();
string strSqlCmd = "SELECT configValue FROM config where configName='cmd6tsp'";
try
{
SQLiteConnection conn = new SQLiteConnection(strSqliteConn);
conn.Open();
SQLiteCommand command = new SQLiteCommand(strSqlCmd, conn);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(ds);
conn.Close();
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
public static void UpdateCm6TSP(string strNewValue)
{
string strSqlCmd = "UPDATE config SET configValue = '" + strNewValue + "' WHERE configName='cmd6tsp';";
try
{
SQLiteConnection conn = new SQLiteConnection(strSqliteConn);
conn.Open();
SQLiteCommand command = new SQLiteCommand(strSqlCmd, conn);
int retRecAffected = command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
//Logger.LogDownSide("in exception; sql is: " + strSqlCmd);
//Logger.LogDownSide("in exception; exception is: " + ex.ToString());
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
}
/// <summary>
/// api:https://restapi.amap.com/v3/geocode/regeo?key=2ed117b3dff55516d969e48fb78bda04" + "&location=" + paradv.longitude + "," + paradv.latitude访问次数一天5000次
/// 如果超过则会报错
/// </summary>
/// <param name="paradv"></param>
/// <returns></returns>
public static string Obtainlocationinformations(jizhanxx paradv)
{
string Wzdsad = "";
try
{
string url = "https://restapi.amap.com/v3/geocode/regeo?key=2ed117b3dff55516d969e48fb78bda04" + "&location=" + paradv.longitude + "," + paradv.latitude;
var request = (HttpWebRequest)WebRequest.Create(url);
var response = (HttpWebResponse)request.GetResponse();
var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
//超过访问次数的报错位置
WZ shujiu = JsonConvert.DeserializeObject<WZ>(responseString);
Wzdsad = shujiu.regeocode.addressComponent.country
+ shujiu.regeocode.addressComponent.province
+ shujiu.regeocode.addressComponent.city
+ shujiu.regeocode.addressComponent.district
+ shujiu.regeocode.addressComponent.township
+ shujiu.regeocode.addressComponent.streetNumber.street;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return Wzdsad;
}
/// <summary>
/// 设备状态填写数据库
/// </summary>
/// <param name="mqttmessage"></param>
public static void InsertState(JObject mqttmessage)
{
try
{
string ip = "";
// 获取所有网络接口
NetworkInterface[] interfaces = NetworkInterface.GetAllNetworkInterfaces();
// 遍历每个网络接口
foreach (NetworkInterface networkInterface in interfaces)
{
// 获取IP地址
IPInterfaceProperties ipProperties = networkInterface.GetIPProperties();
UnicastIPAddressInformationCollection ipAddresses = ipProperties.UnicastAddresses;
// 遍历每个IP地址
foreach (UnicastIPAddressInformation ipAddress in ipAddresses)
{
// 判断是否为IPv4地址
if (ipAddress.Address.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork)
{
//过滤本地ip
if (ipAddress.Address.ToString() != "127.0.0.1")
{
//局域网ip
ip = ipAddress.Address.ToString();
}
}
}
}
deviceState dds = JsonConvert.DeserializeObject<deviceState>(mqttmessage.ToString());
//Logger.LogCalloutanaSide("InsertState:__________________" + dds.payload.@event);
string strSqlCmd = "insert into devicestate(devicename,eventName,productid,reason,timestamp,topic,seq,timemills,ctime) value(";
strSqlCmd += "'" + dds.devicename + "',";
strSqlCmd += "'" + dds.payload.@event + "',";
strSqlCmd += "'" + dds.productID + "',";
strSqlCmd += "'" + dds.payload.reason + "',";
strSqlCmd += "'" + dds.timestamp + "',";
strSqlCmd += "'" + dds.topic + "',";
strSqlCmd += "'" + dds.seq + "',";
strSqlCmd += "'" + dds.timemills + "',";
strSqlCmd += "'" + DateTime.Now + "')";
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
var strSql = strSqlCmd;
cmd.CommandText = strSql;
int retRecAffected = cmd.ExecuteNonQuery();
}
}
strSqlCmd = "select DeviceName from deviceinformation where DeviceName = " + "'" + dds.devicename + "'";
DataSet ds = new DataSet();
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
string sql = "";
string sqls = "";
if (dds.payload.@event == "EV_OFFLINE")
{
sql = "update deviceinformation set Online = " + 0 + " where DeviceName = '" + row["DeviceName"] + "'";
sqls = "insert into programeventlog(eventname,modifycontent,Online,ip,ctime) value(";
sqls += "'腾讯云推送单个设备状态，修改本地设备状态',";
sqls += "'" + row["DeviceName"] + "',";
sqls += 0 + ",";
sqls += "'" + ip + "',";
sqls += "'" + DateTime.Now + "')";
}
else
{
sql = "update deviceinformation set Online = " + 1 + " where DeviceName = '" + row["DeviceName"] + "'";
sqls = "insert into programeventlog(eventname,modifycontent,Online,ip,ctime) value(";
sqls += "'腾讯云推送单个设备状态，修改本地设备状态',";
sqls += "'" + row["DeviceName"] + "',";
sqls += 1 + ",";
sqls += "'" + ip + "',";
sqls += "'" + DateTime.Now + "')";
}
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
//var strSqlname = sql;
cmd.CommandText = sql;
int retRecAffected = cmd.ExecuteNonQuery();
}
}
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
cmd.CommandText = sqls;
int retRecAffected = cmd.ExecuteNonQuery();
}
}
}
}
}
catch (Exception ex)
{
Logger.LogCalloutanaSide("InsertState:__________________" + ex);
}
}
public static List<string> GetSelectStatus(string productid, string devicename, string times, string Statuss)
{
List<string> listRet = new List<string>();
string curline = "";
DataSet ds = new DataSet();
//if (string.IsNullOrEmpty(Name))
//{
// ds = GetAllCallinRecord();
//}
//else
//{
ds = GetAllSelectStatus(productid, devicename, times, Statuss);
//}
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
curline = "";
curline += "deviceName:" + row["deviceName"] + ",";
curline += "eventName:" + row["eventName"] + ",";
curline += "seq:" + row["seq"] + ",";
curline += "timemills:" + row["timemills"] + ",";
curline += "reason:" + row["reason"] + ",";
curline += "id:" + row["id"];
listRet.Add(curline);
}
}
return listRet;
}
private static DataSet GetAllSelectStatus(string productid, string devicename, string times, string Statuss)
{
DataSet ds = new DataSet();
string strSqlCmd = "";
if (string.IsNullOrEmpty(times) || string.IsNullOrEmpty(Statuss))
{
strSqlCmd = "Select * from devicestate where deviceName = '" + devicename + "' ORDER BY id DESC";
}
else if (times == "null" || Statuss == "null")
{
strSqlCmd = "Select * from devicestate where deviceName = '" + devicename + "' ORDER BY id DESC";
}
else
{
DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
DateTime stimes = Convert.ToDateTime(times);
DateTime etimes = Convert.ToDateTime(Statuss);
int kstime = Convert.ToInt32((stimes - dateStart).TotalSeconds);
int jstime = Convert.ToInt32((etimes - dateStart).TotalSeconds);
strSqlCmd = "Select * from devicestate where deviceName = '" + devicename + "' and timestamp >='" + kstime + "' and timestamp<='" + jstime + "' ORDER BY id DESC";
}
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
//SQLiteConnection conn = new SQLiteConnection(strSqliteConn);
//conn.Open();
//SQLiteCommand command = new SQLiteCommand(strSqlCmd, conn);
//SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
//adapter.Fill(ds);
//conn.Close();
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
//方法重载GetSelectStatus
public static List<string> GetSelectStatus(string productid, string devicename)
{
List<string> listRet = new List<string>();
string curline = "";
DataSet ds = new DataSet();
ds = GetAllSelectStatus(productid, devicename);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
curline = "";
curline += "eventName:" + row["eventName"] + ",";
curline += "shul:" + row["shul"];
listRet.Add(curline);
}
}
return listRet;
}
//方法重载GetAllSelectStatus
private static DataSet GetAllSelectStatus(string productid, string devicename)
{
DataSet ds = new DataSet();
string strSqlCmd = "select eventName, count(eventName) as shul from devicestate where deviceName = '" + devicename + "' GROUP BY eventName";
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
public static List<string> GetLOGCallin(string productid, string devicename)
{
List<string> listRet = new List<string>();
//DataSet ds = new DataSet();
string cachedata = RedisDBHelper.StringGet("mqttTopicMessage" + devicename, 1);
//using Newtonsoft.Json;
//// 将JSON字符串转换为对象
//string jsonString = "{\"Name\":\"John\",\"Age\":30}";
try
{
List<MqttTopicMessage> person = RedisDBHelper.ConvertObj<List<MqttTopicMessage>>(cachedata);
foreach (var item in person)
{
string curline = "";
curline += item.timeappreceive + " |";
//curline += "productid:" + row["productid"] + ",";
//curline += "devicename:" + row["devicename"] + ",";
//curline += "topic:" + row["topic"] + ",";
//curline += "payload:" + row["payload"] + ",";
curline += AnalysisByte(item.payload.ToString(), item.devicename.ToString());
//curline += "payload:" + AnalysisByte("pkwAf7vmZCwApwEEvQAAXEOuR0FAAACdQgAAAAAAAAAAAAAAAAAAAAAAAAA=") + ",";
//平台参数，暂时不需要
//curline += "seq:" + row["seq"] + ",";
//curline += "timestamp:" + row["timestamp"] + ",";
//curline += "timemills:" + row["timemills"];
listRet.Add(curline);
}
}
catch (Exception ex)
{
Logger.LogCalloutanaSide("Redis取值错误:" + ex);
}
string com = "设备启动上报服务器信息";
listRet = listRet.Where(x => x.Contains(com)).ToList();
return listRet;
}
internal static List<string> GetLOGCallin(string productid, string devicename, string stime, string etime, int cmdname)
{
List<string> listRet = new List<string>();
DataSet ds = new DataSet()
; //1. get all
ds = GetAllCallinRecordBYTime(devicename);
//2. filter
List<MqttTopicMessage> pairs = new List<MqttTopicMessage>();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable logs = ds.Tables[0];
EnumerableRowCollection<DataRow> query =
from log in logs.AsEnumerable()
where log.Field<string>("productid") == productid && log.Field<string>("devicename") == devicename
select log;
foreach (DataRow row in query)
{
//RedisValue[] mqttTopicMessage ={ row["id"].ToString(),row["payload"].ToString(), row["devicename"].ToString()};
//mqttTopicMessage ={
string ad = row.ToString();
// Redis缓存
MqttTopicMessage mqtt = new MqttTopicMessage();
//mqtt.id = int.Parse(row["id"].ToString());
mqtt.devicename = row["devicename"].ToString();
mqtt.productid = row["productid"].ToString();
mqtt.topic = row["topic"].ToString();
mqtt.payload = row["payload"].ToString();
mqtt.seq = long.Parse(row["seq"].ToString());
mqtt.timestamp = long.Parse(row["timestamp"].ToString());
mqtt.timemills = long.Parse(row["timemills"].ToString());
mqtt.timeappreceive = DateTime.Parse(row["timeappreceive"].ToString());
pairs.Add(mqtt);
}
}
//Redis缓存
try
{
RedisDBHelper.StringSet("mqttTopicMessage" + devicename, pairs, null, 1);
}
catch (Exception ex)
{
Logger.LogCalloutanaSide("Redis缓存错误:" + ex);
}
string cachedata = RedisDBHelper.StringGet("mqttTopicMessage" + devicename, 1);
//using Newtonsoft.Json;
try
{
List<MqttTopicMessage> person = RedisDBHelper.ConvertObj<List<MqttTopicMessage>>(cachedata);
long kstime;
long jstime;
DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
if (stime == "null" || etime == "null")
{
//DateTime jieshu = DateTime.Now.AddMinutes(-10);
//kstime = Convert.ToInt64((jieshu - dateStart).TotalSeconds);
person = person.Take(10).OrderByDescending(x => x.timestamp).ToList();
}
else
{
DateTime stimes = Convert.ToDateTime(stime);
DateTime etimes = Convert.ToDateTime(etime);
kstime = Convert.ToInt64((stimes - dateStart).TotalSeconds);
jstime = Convert.ToInt64((etimes - dateStart).TotalSeconds);
person = person.Where(x => x.timestamp >= kstime && x.timestamp <= jstime).OrderByDescending(x => x.timestamp).ToList();
//strSqlCmd = "SELECT id, timeappreceive, productid,devicename,topic,payload,seq,timestamp,timemills FROM uploginfo where timestamp >= '" + kstime + "' and timestamp <='" + jstime + "' and devicename='" + devicename + "' ORDER BY timestamp DESC";
}
foreach (var item in person)
{
string curline = "";
curline += item.timemills + "|";
curline += item.timeappreceive + "|";
//curline += "productid:" + row["productid"] + ",";
//curline += "devicename:" + row["devicename"] + ",";
//curline += "topic:" + row["topic"] + ",";
//curline += "payload:" + row["payload"] + ",";
curline += AnalysisByte(item.payload.ToString(), item.devicename.ToString());
//curline += "payload:" + AnalysisByte("pkwAf7vmZCwApwEEvQAAXEOuR0FAAACdQgAAAAAAAAAAAAAAAAAAAAAAAAA=") + ",";
//平台参数，暂时不需要
//curline += "seq:" + row["seq"] + ",";
//curline += "timestamp:" + row["timestamp"] + ",";
//curline += "timemills:" + row["timemills"];
listRet.Add(curline);
}
}
catch (Exception ex)
{
Logger.LogCalloutanaSide("Redis取值错误:" + ex);
}
//// 将对象转换为JSON字符串
//var person = new Person { Name = "John", Age = 30 };
//string jsonString = JsonConvert.SerializeObject(person);
if (cmdname != 0)
{
string com = "";
switch (cmdname)
{
case 1:
com = "设备上报门锁动作";
break;
case 2:
com = "设备上报PIR动作";
break;
case 3:
com = "设备定期上报";
break;
case 4:
com = "设备取电动作上报";
break;
case 5:
com = "设备获取云端服务器参数";
break;
case 6:
com = "云端服务器设置参数";
break;
case 7:
com = "服务器控制取电下发";
break;
case 8:
com = "服务器查询设备信息";
break;
case 9:
com = "设备启动上报服务器信息";
break;
case 10:
com = "获取设备位置信息";
break;
case 11:
com = "门磁动作上报";
break;
}
listRet = listRet.Where(x => x.Contains(com)).ToList();
}
return listRet;
}
public static List<string> GetAllpowerstatisticschart(string devname, string startT, string endT, string productid, int cmdname)
{
List<string> listRet = new List<string>();
#region Redis取值
//DataSet ds = new DataSet();
//1. get all
//string cachedata = RedisDBHelper.StringGet("mqttTopicMessage" + devname, 1);
//ds = GetAllpowerstatisticscharts(devname, startT, endT);
//try
//{
// List<MqttTopicMessage> person = RedisDBHelper.ConvertObj<List<MqttTopicMessage>>(cachedata);
// long kstime;
// long jstime;
// DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
// if (startT == "null" || endT == "null")
// {
// DateTime jieshu = DateTime.Now.AddDays(-5);
// kstime = Convert.ToInt64((jieshu - dateStart).TotalSeconds);
// person = person.Where(x => x.timestamp >= kstime).OrderByDescending(x => x.timestamp).ToList();
// }
// else
// {
// DateTime stimes = Convert.ToDateTime(startT);
// DateTime etimes = Convert.ToDateTime(endT);
// kstime = Convert.ToInt64((stimes - dateStart).TotalSeconds);
// jstime = Convert.ToInt64((etimes - dateStart).TotalSeconds);
// person = person.Where(x => x.timestamp >= kstime && x.timestamp <= jstime).OrderByDescending(x => x.timestamp).ToList();
// //strSqlCmd = "SELECT id, timeappreceive, productid,devicename,topic,payload,seq,timestamp,timemills FROM uploginfo where timestamp >= '" + kstime + "' and timestamp <='" + jstime + "' and devicename='" + devicename + "' ORDER BY timestamp DESC";
// }
// foreach (var item in person)
// {
// string curline = "";
// curline += item.timeappreceive + " |";
// //curline += "productid:" + row["productid"] + ",";
// //curline += "devicename:" + row["devicename"] + ",";
// //curline += "topic:" + row["topic"] + ",";
// //curline += "payload:" + row["payload"] + ",";
// curline += AnalysisByte(item.payload.ToString(), item.devicename.ToString());
// //curline += "payload:" + AnalysisByte("pkwAf7vmZCwApwEEvQAAXEOuR0FAAACdQgAAAAAAAAAAAAAAAAAAAAAAAAA=") + ",";
// //平台参数，暂时不需要
// //curline += "seq:" + row["seq"] + ",";
// //curline += "timestamp:" + row["timestamp"] + ",";
// //curline += "timemills:" + row["timemills"];
// listRet.Add(curline);
// }
//}
//catch (Exception ex)
//{
// Logger.LogCalloutanaSide("Redis取值错误:" + ex);
//}
//if (cmdname != 0)
//{
// string com = "";
// switch (cmdname)
// {
// case 1:
// com = "设备上报门锁动作";
// break;
// case 2:
// com = "设备上报PIR动作";
// break;
// case 3:
// com = "设备定期上报";
// break;
// case 4:
// com = "设备取电动作上报";
// break;
// case 5:
// com = "设备获取云端服务器参数";
// break;
// case 6:
// com = "云端服务器设置参数";
// break;
// case 7:
// com = "服务器控制取电下发";
// break;
// case 8:
// com = "服务器查询设备信息";
// break;
// case 9:
// com = "设备启动上报服务器信息";
// break;
// case 10:
// com = "获取设备位置信息";
// break;
// }
// listRet = listRet.Where(x => x.Contains(com)).ToList();
//}
//return listRet;
#endregion
#region sql语句取值
DataSet ds = new DataSet();
ds = GetAllpowerstatisticscharts(devname, startT, endT);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
string curline = "";
//curline += "devicename:" + row["devicename"] + ",";
curline += "creationtime&" + row["creationtime"] + ",";
curline += "lockElectricQuantity:" + row["lockElectricQuantity"] + ",";
curline += "PIR1ElectricQuantity:" + row["PIR1ElectricQuantity"] + ",";
curline += "PIR2ElectricQuantity:" + row["PIR2ElectricQuantity"] + ",";
curline += "PIR3ElectricQuantity:" + row["PIR3ElectricQuantity"] + ",";
curline += "PIR4ElectricQuantity:" + row["PIR4ElectricQuantity"] + ",";
curline += "GMElectricQuantity:" + row["GMElectricQuantity"] + ",";
//curline += "lockElectricQuantity:" + row["lockElectricQuantity"] + ",";
curline += "PIR5ElectricQuantity:" + row["PIR5ElectricQuantity"] + ",";
curline += "PIR6ElectricQuantity:" + row["PIR6ElectricQuantity"] + ",";
curline += "PIR7ElectricQuantity:" + row["PIR7ElectricQuantity"] + ",";
curline += "PIR8ElectricQuantity:" + row["PIR8ElectricQuantity"] + ",";
curline += "Power:" + row["Power"] + ",";
listRet.Add(curline);
}
}
return listRet;
#endregion
}
private static DataSet GetAllpowerstatisticscharts(string devname, string startT, string endT)
{
DataSet ds = new DataSet();
//int kstime;
//int jstime;
string strSqlCmd;
//DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
//if (startT == "null" || endT == "null")
//{
// DateTime jieshu = DateTime.Now.AddMinutes(-30);
// kstime = Convert.ToInt32((jieshu - dateStart).TotalSeconds);
// strSqlCmd = "SELECT timeappreceive, productid,devicename,topic,payload,seq,timestamp,timemills FROM uploginfo where timestamp >= '" + kstime + "' and devicename='" + devname + "' ORDER BY timestamp DESC";
//}
//else
//{
// DateTime stimes = Convert.ToDateTime(startT);
// DateTime etimes = Convert.ToDateTime(endT);
// kstime = Convert.ToInt32((stimes - dateStart).TotalSeconds);
// jstime = Convert.ToInt32((etimes - dateStart).TotalSeconds);
// strSqlCmd = "SELECT timeappreceive, productid,devicename,topic,payload,seq,timestamp,timemills FROM uploginfo where timestamp >= '" + kstime + "' and timestamp <='" + jstime + "' and devicename='" + devname + "' ORDER BY timestamp DESC";
//}
//DataSet ds = new DataSet();
if (startT == "null" || endT == "null")
{
DateTime jieshu = DateTime.Now.AddDays(-30);
strSqlCmd = "select * from deviceelectricquantity where devicename='" + devname + "' and creationtime >='" + jieshu + "'";
}
else
{
strSqlCmd = "select * from deviceelectricquantity where devicename='" + devname + "' and creationtime >='" + startT + "' and creationtime <= '" + endT + "'";
}
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
//SQLiteConnection conn = new SQLiteConnection(strSqliteConn);
//conn.Open();
//SQLiteCommand command = new SQLiteCommand(strSqlCmd, conn);
//SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
//adapter.Fill(ds);
//conn.Close();
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
public static List<string> jianzaogduo(string productid, string devicename, string shijian, int cmdname)
{
List<string> listRet = new List<string>();
DataSet ds = new DataSet()
; //1. get all
//ds = GetAllCallinRecordBYTimes(productid, devicename, shijian);
string cachedata = RedisDBHelper.StringGet("mqttTopicMessage" + devicename, 1);
try
{
List<MqttTopicMessage> person = RedisDBHelper.ConvertObj<List<MqttTopicMessage>>(cachedata);
int kstime;
DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
DateTime stimes = Convert.ToDateTime(shijian);
DateTime shijians = stimes.AddMinutes(-30);
kstime = Convert.ToInt32((shijians - dateStart).TotalSeconds);
person = person.Where(x => x.timestamp >= kstime).OrderByDescending(x => x.timestamp).ToList();
foreach (var item in person)
{
string curline = "";
curline += item.timemills + "|";
curline += item.timeappreceive + "|";
//curline += "productid:" + row["productid"] + ",";
//curline += "devicename:" + row["devicename"] + ",";
//curline += "topic:" + row["topic"] + ",";
//curline += "payload:" + row["payload"] + ",";
curline += AnalysisByte(item.payload.ToString(), item.devicename.ToString());
//curline += "payload:" + AnalysisByte("pkwAf7vmZCwApwEEvQAAXEOuR0FAAACdQgAAAAAAAAAAAAAAAAAAAAAAAAA=") + ",";
//平台参数，暂时不需要
//curline += "seq:" + row["seq"] + ",";
//curline += "timestamp:" + row["timestamp"] + ",";
//curline += "timemills:" + row["timemills"];
listRet.Add(curline);
}
if (cmdname != 0)
{
string com = "";
switch (cmdname)
{
case 1:
com = "设备上报门锁动作";
break;
case 2:
com = "设备上报PIR动作";
break;
case 3:
com = "设备定期上报";
break;
case 4:
com = "设备取电动作上报";
break;
case 5:
com = "设备获取云端服务器参数";
break;
case 6:
com = "云端服务器设置参数";
break;
case 7:
com = "服务器控制取电下发";
break;
case 8:
com = "服务器查询设备信息";
break;
case 9:
com = "设备启动上报服务器信息";
break;
case 10:
com = "获取设备位置信息";
break;
case 11:
com = "门磁动作上报";
break;
}
listRet = listRet.Where(x => x.Contains(com)).ToList();
}
}
catch (Exception ex)
{
Logger.LogCalloutanaSide("Redis取值错误:" + ex);
}
return listRet;
}
private static DataSet GetAllCallinRecordBYTimes(string productid, string devicename, string shijian)
{
DataSet ds = new DataSet();
int kstime;
string strSqlCmd;
DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
DateTime stimes = Convert.ToDateTime(shijian);
DateTime shijians = stimes.AddMinutes(-30);
kstime = Convert.ToInt32((shijians - dateStart).TotalSeconds);
strSqlCmd = "SELECT id, timeappreceive, productid,devicename,topic,payload,seq,timestamp,timemills FROM uploginfo where timestamp >= '" + kstime + "' and devicename='" + devicename + "' ORDER BY timestamp DESC";
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
public static List<string> GetSelectStatus(string devicename, string stime, string etime)
{
List<string> listRet = new List<string>();
string curline = "";
DataSet ds = new DataSet();
//if (string.IsNullOrEmpty(Name))
//{
// ds = GetAllCallinRecord();
//}
//else
//{
ds = GetAllSelectStatus(devicename, stime, etime);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
curline = "";
curline += "deviceName:" + row["deviceName"] + ",";
curline += "eventName:" + row["eventName"] + ",";
curline += "seq:" + row["seq"] + ",";
curline += "timemills:" + row["timemills"] + ",";
curline += "reason:" + row["reason"] + ",";
curline += "id:" + row["id"];
listRet.Add(curline);
}
}
return listRet;
}
private static DataSet GetAllSelectStatus(string devicename, string stime, string etime)
{
long kstime;
long jstime;
DataSet ds = new DataSet();
string strSqlCmd = "";
if (devicename == "null" && stime != "null" && etime != "null")
{
DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
DateTime stimes = Convert.ToDateTime(stime);
DateTime etimes = Convert.ToDateTime(etime);
kstime = Convert.ToInt64((stimes - dateStart).TotalSeconds);
jstime = Convert.ToInt64((etimes - dateStart).TotalSeconds);
strSqlCmd = "SELECT * FROM devicestate where timemills>= '" + kstime + "' and timemills<= '" + jstime + "' ORDER BY timemills desc";
}
else if (devicename != "null" && stime == "null" && etime == "null")
{
strSqlCmd = "SELECT * FROM devicestate where deviceName LIKE '%" + devicename + "%' ORDER BY timemills desc";
}
else if (devicename != "null" && stime != "null" && etime != "null")
{
DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
DateTime stimes = Convert.ToDateTime(stime);
DateTime etimes = Convert.ToDateTime(etime);
kstime = Convert.ToInt64((stimes - dateStart).TotalSeconds);
jstime = Convert.ToInt64((etimes - dateStart).TotalSeconds);
strSqlCmd = "SELECT * FROM devicestate where timemills>= '" + kstime + "' and timemills<= '" + jstime + "' and deviceName LIKE '%" + devicename + "%' ORDER BY timemills desc";
}
else
{
strSqlCmd = "SELECT * FROM devicestate ORDER BY timemills desc";
}
try
{
using (MySqlConnection connection = new MySqlConnection(strSqliteConn))
{
using (MySqlCommand cmd = new MySqlCommand())
{
connection.Open();
cmd.Connection = connection;
MySqlDataAdapter command = new MySqlDataAdapter(strSqlCmd, connection);
command.Fill(ds);
}
}
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("MQTTServerSideAPI", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss fff") + " " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
}
return ds;
}
public class deviceState
{
public device payload { get; set; }
public string timemills { get; set; }
public string seq { get; set; }
public string productID { get; set; }
public string timestamp { get; set; }
public string devicename { get; set; }
public string topic { get; set; }
}
public class device
{
public string deviceName { get; set; }
public string @event { get; set; }
public string productID { get; set; }
public string reason { get; set; }
public string timestamp { get; set; }
public string topic { get; set; }
}
public class WZ
{
public int status { get; set; }
public AA regeocode { get; set; }
public string info { get; set; }
public string infocode { get; set; }
}
public class AA
{
public BB addressComponent { get; set; }
public string formatted_address { get; set; }
}
public class BB
{
public string city { get; set; }
public string province { get; set; }
public string adcode { get; set; }
public string district { get; set; }
public string towncode { get; set; }
public string country { get; set; }
public string township { get; set; }
public string citycode { get; set; }
public DD streetNumber { get; set; }
}
public class DD
{
public string number { get; set; }
public string location { get; set; }
public string direction { get; set; }
public string distance { get; set; }
public string street { get; set; }
}
}
}
Reference in New Issue Copy Permalink