/// <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);
}
