/// <summary> /// 插入JT808数据到DB /// </summary> /// <param name="aJT808_PackageData"></param> /// <returns></returns> public int INSERT2(JT808_PackageData aJT808_PackageData) { string sql = ""; //sql = string.Format("INSERT INTO `locationdb`.`t_gps_jt808` (`message_id`, `vehicle_sim`, `alc`, `bst`, `lon`, `lat`, `hgt`, `spd`, `agl`, `gtm`, `mlg`, `oil`, `spd2`, `est`, `io`, `ad1`, `yte`, `gnss`, `db_time`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', {18});", sql = string.Format("INSERT INTO `gpsdb`.`t_gps` ( `device_id`, `alarm_status`, `vehicle_status`, `longitude`, `latitude`, `height`, `speed`, `direction`, `time`, `mile`, `oil`, `speed2`, `signal_status`, `io_status`, `analog`, `wifi`, `satellite_num`, `create_time`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}',{17});", //GetNewGuid(), aJT808_PackageData.msgHeader.terminalPhone, aJT808_PackageData.locationInfo.alc, aJT808_PackageData.locationInfo.bst, aJT808_PackageData.locationInfo.lon.ToString(), aJT808_PackageData.locationInfo.lat.ToString(), aJT808_PackageData.locationInfo.hgt.ToString(), aJT808_PackageData.locationInfo.spd.ToString(), aJT808_PackageData.locationInfo.agl.ToString(), aJT808_PackageData.locationInfo.gtm.ToString(), aJT808_PackageData.locationInfo.mlg.ToString(), aJT808_PackageData.locationInfo.oil.ToString(), aJT808_PackageData.locationInfo.spd2.ToString(), aJT808_PackageData.locationInfo.est, aJT808_PackageData.locationInfo.io, aJT808_PackageData.locationInfo.ad1.ToString(), aJT808_PackageData.locationInfo.yte.ToString(), aJT808_PackageData.locationInfo.gnss.ToString(), "NOW()" ); return(helper_lb.AddDelUpdate(sql)); }
/// <summary> /// 这里解析的到的数据是会把头和尾部都给去掉的 包括0x7E /// </summary> /// <param name="readBuffer"></param> /// <param name="offset"></param> /// <param name="length"></param> /// <returns></returns> protected override HLProtocolRequestInfo ProcessMatchedRequest(byte[] readBuffer, int offset, int length) { //var aJT808_PackageData = new JT808_PackageData(); string all = ""; for (int i = 0; i < length; i++) { all = all + readBuffer[offset + i].ToString("X2") + " "; } //转义还原 byte[] message = ExplainUtils.DoEscape4Receive(readBuffer, offset, offset + length); string all2 = ""; for (int i = 0; i < message.Length; i++) { all2 = all2 + message[i].ToString("X2") + " "; } //解析消息头 JT808_PackageData aJT808_PackageData = decoder.Bytes2PackageData(message); aJT808_PackageData.all = all; aJT808_PackageData.all2 = all2; if (aJT808_PackageData.errorlog == null) { //解析消息体 ProcessPackageData(ref aJT808_PackageData); } return(new HLProtocolRequestInfo(aJT808_PackageData)); }
/// <summary> /// 二进制数据 转成 内容包数据 /// </summary> /// <param name="data"></param> /// <returns></returns> public JT808_PackageData Bytes2PackageData(byte[] data) { JT808_PackageData ret = new JT808_PackageData(); // 1. 16byte 或 12byte 消息头 JT808_PackageData.MsgHeader msgHeader = this.ParseMsgHeaderFromBytes(data); ret.msgHeader = msgHeader; int msgBodyByteStartIndex = 12 + 1; // 2. 消息体 // 有子包信息,消息体起始字节后移四个字节:消息包总数(word(16))+包序号(word(16)) if (msgHeader.hasSubPackage) { msgBodyByteStartIndex = 16 + 1; } byte[] tmp = new byte[msgHeader.msgBodyLength]; Buffer.BlockCopy(data, msgBodyByteStartIndex, tmp, 0, tmp.Length); ret.setMsgBodyBytes(tmp); // 3. 去掉分隔符之后,最后一位就是校验码 int checkSumInPkg = data[data.Length - 1 - 1]; int calculatedCheckSum = ExplainUtils.getCheckSum4JT808(data, 0 + 1, data.Length - 1 - 1); ret.checkSum = (checkSumInPkg); if (checkSumInPkg != calculatedCheckSum) { ret.errorlog = ret.errorlog + "检验码不一致;";// string.Format("检验码不一致,msgid:{},pkg:{},calculated:{}", msgHeader.getMsgId(), checkSumInPkg, calculatedCheckSum); } return(ret); }
public void query_access_sensor(JT808_PackageData packageData) { DataSet accessSensorSet = locationDB.GET_t_bm_access_sensor(); foreach (DataRow mDr in accessSensorSet.Tables[0].Rows) { int sensor_port = int.Parse(mDr["sensor_port"].ToString());// int(6) DEFAULT NULL COMMENT '接入点端口', Console.WriteLine("ip=====" + sensor_port); } }
private void ProcessPackageData(ref JT808_PackageData packageData) { //消息ID int msgId = packageData.msgHeader.msgId; //消息体属性 int msgBodyProps = packageData.msgHeader.msgBodyPropsField; //终端手机号 string terminalPhone = packageData.msgHeader.terminalPhone; //消息流水号 int flowId = packageData.msgHeader.flowId; //1. 终端注册 ==> 终端注册应答 if (msgId == ExplainUtils.msg_id_terminal_register) { //客户端消息应答 byte[] sendMsg = ExplainUtils.rtnTerminalRespMsg(0014, terminalPhone, flowId); //设置回复信息 packageData.setMsgRespBytes(sendMsg); } //2. 终端鉴权 ==> 平台通用应答 else if (msgId == ExplainUtils.msg_id_terminal_authentication) { //客户端消息应答 byte[] sendMsg = ExplainUtils.rtnServerCommonRespMsg(0005, terminalPhone, flowId, msgId); //设置回复信息 packageData.setMsgRespBytes(sendMsg); } //3. 终端心跳-消息体为空 ==> 平台通用应答 else if (msgId == ExplainUtils.msg_id_terminal_heart_beat) { //客户端消息应答 byte[] sendMsg = ExplainUtils.rtnServerCommonRespMsg(0005, terminalPhone, flowId, msgId); //设置回复信息 packageData.setMsgRespBytes(sendMsg); } //4. 位置信息汇报 ==> 平台通用应答 else if (msgId == ExplainUtils.msg_id_terminal_location_info_upload) { packageData.locationInfo = (decoder.ToLocationInfoMsg(packageData.getMsgBodyBytes())); //客户端消息应答 byte[] sendMsg = ExplainUtils.rtnServerCommonRespMsg(0005, terminalPhone, flowId, msgId); //设置回复信息 packageData.setMsgRespBytes(sendMsg); } // 其他情况 else { packageData.errorlog = ">>>>>[未知消息类型-0x{" + msgId + "}],phone=" + terminalPhone + ",flowid={" + flowId + "}"; } }
/// <summary> /// 插入JT808数据到DB /// </summary> /// <param name="aJT808_PackageData"></param> /// <returns></returns> public string GetSql_INSERT_JT808_PackageData(JT808_PackageData aJT808_PackageData) { string sql = ""; sql = string.Format("INSERT INTO t_gps_jt808 VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}');", // GetNewGuid(), aJT808_PackageData.msgHeader.terminalPhone, aJT808_PackageData.locationInfo.alc, aJT808_PackageData.locationInfo.bst, aJT808_PackageData.locationInfo.lon.ToString(), aJT808_PackageData.locationInfo.lat.ToString(), aJT808_PackageData.locationInfo.hgt.ToString(), aJT808_PackageData.locationInfo.spd.ToString(), aJT808_PackageData.locationInfo.agl.ToString(), aJT808_PackageData.locationInfo.gtm.ToString(), aJT808_PackageData.locationInfo.mlg.ToString(), aJT808_PackageData.locationInfo.oil.ToString(), aJT808_PackageData.locationInfo.spd2.ToString(), aJT808_PackageData.locationInfo.est, aJT808_PackageData.locationInfo.io, aJT808_PackageData.locationInfo.ad1.ToString(), aJT808_PackageData.locationInfo.yte.ToString(), aJT808_PackageData.locationInfo.gnss.ToString(), "NOW()" ); //sqlList.Add(sql); //if (sqlList.Count == 2000) //{ // helper_lb.ExecuteSqlTran(sqlList); // sqlList.RemoveRange(0, 2000); //} //else if (sqlList.Count < 1000) //{ //} return(sql); //return helper_lb.AddDelUpdate(sql); }
private static bool ProcessMessage(byte[] body) { try { MsgDecoder msgDecoder = new MsgDecoder(); JT808_PackageData packageData = new JT808_PackageData(); string content = ""; content = BitConverter.ToString(body).Replace("-", " "); Console.WriteLine("Received: {0}", content); logger.Info("Received: " + content); //终端设备号 消息体前6个byte String deviceCode = body[0].ToString("X2") + body[1].ToString("X2") + body[2].ToString("X2") + body[3].ToString("X2") + body[4].ToString("X2") + body[5].ToString("X2"); packageData.locationInfo = (msgDecoder.ToLocationInfoMsg(body)); string alc = packageData.locationInfo.alc; //报警标志 string bst = packageData.locationInfo.bst; //状态 double lon = packageData.locationInfo.lon; //经度 double lat = packageData.locationInfo.lat; //纬度 double hgt = packageData.locationInfo.hgt; //高度 double spd = packageData.locationInfo.spd; //速度 double agl = packageData.locationInfo.agl; //方向 DateTime gtm = packageData.locationInfo.gtm; //时间 double mlg = packageData.locationInfo.mlg; //里程 double oil = packageData.locationInfo.oil; //油量 double spd2 = packageData.locationInfo.spd2; //记录仪速度 int est = 0; //信号状态 if (packageData.locationInfo.est != "") { est = int.Parse(packageData.locationInfo.est); } int io = 0;//IO状态位 if (packageData.locationInfo.io != "") { io = int.Parse(packageData.locationInfo.io); } int ad1 = 0;//模拟量 if (packageData.locationInfo.ad1 != "") { ad1 = int.Parse(packageData.locationInfo.ad1); } int yte = packageData.locationInfo.yte; //信号强度 int gnss = packageData.locationInfo.gnss; //定位卫星数 string connectionStr = //从配置文件读取连接字符串 WebConfigurationManager.ConnectionStrings["connStr1"].ConnectionString; //string connectionStr = "data source='" + datasource + "';user id='" + uname + "';password='******';charset=utf8"; DBConnectionSingletion.ConnectionString = connectionStr; MySqlConnection conn = pool.BorrowDBConnection();//从连接池借一个连接对象 String sql = "SELECT * FROM gps_main.t_gps_main where device_code = '" + deviceCode + "'"; DataSet result = null; try { result = DBGuid.Select(conn, sql, "t_gps_main"); } catch (Exception e) { logger.Error(e.Message); } //将连接对象还给连接池 pool.ReturnDBConnection(conn); TBOperation operation = new TBOperation(); int latterSeq = operation.SelectLatterSeqNo(conn, "gps"); //已存在的最大轨迹序列号 int latestSeq = TbUtil.CreateLatestSeqNo(latterSeq, "gps"); //创建新的轨迹序列号 //int latterAlarmSeq = operation.SelectLatterSeqNo(conn, "alarm");//已存在的最大报警序列号 //int latestAlarmSeq = TbUtil.CreateLatestSeqNo(latterAlarmSeq, "alarm");//创建新的报警序列号 if (result != null && result.Tables.Count > 0 && result.Tables[0].Rows.Count > 0) {//主表已保存该设备信息 //更新轨迹快照表sql DataRow deviceRow = result.Tables[0].Rows[0]; string vendor = deviceRow["vendor_code"].ToString(); string updateSnapSql = string.Format("UPDATE gps_main.t_gps_snapshot " + "set alarm_status={1},vehicle_status={2},lat={3},lon={4},height={5},speed={6}," + "direction={7},time={8},mile={9},oil={10},speed2={11},signal_status={12},bst={13},io_status={14},analog={15},wifi={16},satellite_num={17},create_time={18},vendor_code='{19}'" + " where device_code='{0}';", deviceCode, alc, bst, lat, lon, hgt, spd, agl, TbUtil.GetGMTInMS(gtm), mlg, oil, spd2, est, bst, io, ad1, yte, gnss, "FROM_UNIXTIME(NOW())", vendor); Console.WriteLine("<<Info>>update gps_main.t_gps_snapshot {0}", deviceCode); logger.Info("update gps_main.t_gps_snapshot " + deviceCode); if (conn != null && !conn.State.Equals("open")) { conn = pool.BorrowDBConnection();//从连接池借一个连接对象 } DBGuid.Update(conn, updateSnapSql); pool.ReturnDBConnection(conn);//将连接对象还给连接池 //轨迹表新增一条轨迹信息 string dbName = TbUtil.GetDbName("gps", latestSeq, "gps"); string tbName = TbUtil.GetTableName(packageData.locationInfo.gtm, "t_gps", latestSeq, "gps"); string tbPath = dbName + "." + tbName; string insertGpsSql = string.Format("INSERT INTO " + tbPath + " (device_code,alarm_status,vehicle_status,lat,lon,height,speed," + "direction,time,mile,oil,speed2,signal_status,bst,io_status,analog,wifi,satellite_num,create_time,vendor_code)" + "VALUES ('{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},'{15}',{16},{17},{18},'{19}');", deviceCode, alc, bst, lat, lon, hgt, spd, agl, TbUtil.GetGMTInMS(gtm), mlg, oil, spd2, est, bst, io, ad1, yte, gnss, "FROM_UNIXTIME(NOW())", vendor); Console.WriteLine("<<Info>>insert {0} {1}", tbPath, deviceCode); logger.Info("insert " + tbPath + " " + deviceCode); if (conn != null && !conn.State.Equals("open")) { conn = pool.BorrowDBConnection();//从连接池借一个连接对象 } DBGuid.Insert(conn, insertGpsSql); pool.ReturnDBConnection(conn);//将连接对象还给连接池 //报警表新增一条报警信息 //先判断是否有报警 //string alarmStatus = packageData.locationInfo.alc; //if (alarmStatus.Equals("0000"))//有报警则新增 //{ // string alarmDb = TbUtil.GetDbName("gps_alarm", latestAlarmSeq, "alarm"); // string alarmTb = TbUtil.GetTableName(gtm, "t_gps_alarm", latestAlarmSeq, "alarm"); // string alarmTbPath = alarmDb + "." + alarmTb; // string insertalarmsql = string.Format("insert into " + alarmTbPath + // " (device_code,alarm_status,vehicle_status,lat,lon,height,speed," + // "direction,time,mile,oil,speed2,signal_status,bst,io_status,analog,wifi,satellite_num,create_time,vendor_code,alarm_handle)" + // "values ('{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},'{15}',{16},{17},{18},'{19}',{20});", // deviceCode, alc, bst, lat, lon, hgt, spd, agl, TbUtil.GetGMTInMS(gtm), mlg, oil, spd2, est, bst, io, ad1, yte, gnss, "from_unixtime(now())", vendor, 0); //} } else {//主表不存在该设备信息 //插入main表sql //for test //随机生成一个车牌号 Random R = new Random(); string[] strArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V","W", "X", "Y", "Z" }; int ron = R.Next(1000, 9999); int ran = R.Next(0, 25); String plateNo = "浙" + strArr[ran] + strArr[ran] + ron; //随机生成一个运营商代码 String vendor = ""; for (int i = 0; i < 6; i++) { vendor += strArr[ran]; } vendor = vendor + ron; String remark = "test" + DateTime.Now.ToString(); //主表新增一条设备信息 String insertMainSql = string.Format("INSERT INTO gps_main.t_gps_main (device_code,plate_no,vendor_code,trail_seq_no,alarm_seq_no,status,remark)" + "VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}');", deviceCode, plateNo, vendor, latestSeq, 0, 10, remark); Console.WriteLine("<<Info>>insert gps_main.t_gps_main {0}", deviceCode); logger.Info("insert gps_main.t_gps_main " + deviceCode); if (conn != null && !conn.State.Equals("open")) { conn = pool.BorrowDBConnection();//从连接池借一个连接对象 } DBGuid.Insert(conn, insertMainSql); pool.ReturnDBConnection(conn);//将连接对象还给连接池 //插入轨迹快照表sql string insertSnapSql = string.Format("INSERT INTO " + "gps_main.t_gps_snapshot" + " (device_code,alarm_status,vehicle_status,lat,lon,height,speed," + "direction,time,mile,oil,speed2,signal_status,bst,io_status,analog,wifi,satellite_num,create_time,vendor_code)" + "VALUES ('{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},'{15}',{16},{17},{18},'{19}');", deviceCode, alc, bst, lat, lon, hgt, spd, agl, TbUtil.GetGMTInMS(gtm), mlg, oil, spd2, est, bst, io, ad1, yte, gnss, "FROM_UNIXTIME(NOW())", vendor); if (conn != null && !conn.State.Equals("open")) { conn = pool.BorrowDBConnection();//从连接池借一个连接对象 } Console.WriteLine("<<Info>>insert gps_main.t_gps_snapshot {0}", deviceCode); logger.Info("insert gps_main.t_gps_snapshot " + deviceCode); DBGuid.Insert(conn, insertSnapSql); pool.ReturnDBConnection(conn);//将连接对象还给连接池 //轨迹表新增一条轨迹信息 string dbName = TbUtil.GetDbName("gps", latestSeq, "gps"); string tbName = TbUtil.GetTableName(packageData.locationInfo.gtm, "t_gps", latestSeq, "gps"); string tbPath = dbName + "." + tbName; string insertGpsSql = string.Format("INSERT INTO " + tbPath + " (device_code,alarm_status,vehicle_status,lat,lon,height,speed," + "direction,time,mile,oil,speed2,signal_status,bst,io_status,analog,wifi,satellite_num,create_time,vendor_code)" + "VALUES ('{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},'{15}',{16},{17},{18},'{19}');", deviceCode, alc, bst, lat, lon, hgt, spd, agl, TbUtil.GetGMTInMS(gtm), mlg, oil, spd2, est, bst, io, ad1, yte, gnss, "FROM_UNIXTIME(NOW())", vendor); if (conn != null && !conn.State.Equals("open")) { conn = pool.BorrowDBConnection();//从连接池借一个连接对象 } Console.WriteLine("<<Info>>insert {0} {1}", tbPath, deviceCode); logger.Info("insert " + tbPath + " " + deviceCode); DBGuid.Insert(conn, insertGpsSql); pool.ReturnDBConnection(conn);//将连接对象还给连接池 //报警表新增一条报警信息 //string alarmDb = TbUtil.GetDbName("gps_alarm", latestAlarmSeq, "alarm"); //string alarmTb = TbUtil.GetTableName(gtm, "t_gps_alarm", latestAlarmSeq, "alarm"); //string alarmTbPath = alarmDb + "." + alarmTb; //string insertalarmsql = string.Format("insert into " + alarmTbPath + // " (device_code,alarm_status,vehicle_status,lat,lon,height,speed," + // "direction,time,mile,oil,speed2,signal_status,bst,io_status,analog,wifi,satellite_num,create_time,vendor_code,alarm_handle)" + // "values ('{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},'{15}',{16},{17},{18},'{19}',{20});", // devicecode, alc, bst, lat, lon, hgt, spd, agl, operation.getgmtinms(gtm), mlg, oil, spd2, est, bst, io, ad1, yte, gnss, "from_unixtime(now())", vendor, 0); } } catch (Exception e) { logger.Error(e.Message); return(false); } return(true); }
public void inster_JT808data(JT808_PackageData packageData) { locationDB.INSERT2(packageData); }
public void inster_JT808alarmData(JT808_PackageData packageData) { locationDB.insertAlarm(packageData); }