/// <summary> /// 插入从运管平台拉取的订单 /// </summary> /// <param name="orders"></param> /// <param name="senderCode"></param> public static void AddTMSOrders_Logink(List <M_TMSOrder> orders, string senderCode) { string dtNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); try { int customerId = GetCustomerIdBySenderCode(senderCode, 2); List <string> sqlList = new List <string>(); foreach (M_TMSOrder item in orders) { item.senderPerson = "配送中心"; sqlList.Add(string.Format("INSERT INTO `coldchain_logistics_db`.`huadong_tms_order` (`SHIPDETAILID`,`LEGCODE`, `CREDATE`, `senderOrg`, `senderPerson`, `senderTel`, `senderAddress`, `receiverOrg`, `receiverPerson`, `receiverTel`, `receiverAddress`, `SecretKey`, `CreateTime`, `customerId`,`JFQUNTITY`) VALUES ('{0}', '{0}','{12}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}','{13}');", item.orderNo, item.senderOrg, item.senderPerson, item.senderTel, item.senderAddress, item.receiverOrg, item.receiverPerson, item.receiverTel, item.receiverAddress, senderCode, dtNow, customerId, item.beginAt.ToString("yyyy-MM-dd HH:mm:ss"), item.billingCount)); } IEnumerable <string> tmsNumbers = orders.Select(l => l.orderNo); //查询关系表,查找是否存在已经扫描过的单,如果存在则更新订单表中的信息 string sql = string.Format("select relationId,number from huadong_tmsorder_waybillbase where relationId in ('{0}');", string.Join("','", tmsNumbers)); DataSet ds = DbHelperMySQL.Query(sql); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow row in ds.Tables[0].Rows) { string relationId = row["relationId"].ToString(); string number = row["number"].ToString(); M_TMSOrder order = orders.Find(l => l.orderNo == relationId); order.senderPerson = "配送中心"; sqlList.Add(string.Format("update waybill_base set senderPerson = '{0}',senderTel='{1}',senderAddress='{2}',receiverOrg='{3}',receiverPerson='{4}',receiverTel='{5}',receiverAddress='{6}' where number = '{7}';", order.senderPerson, order.senderTel, order.senderAddress, order.receiverOrg, order.receiverPerson, order.receiverTel, order.receiverAddress, number)); } } DbHelperMySQL.ExecuteSqlTran(sqlList); } catch (Exception ex) { throw new Exception("保存TMS运单出错:" + ex.Message); } }
/// <summary> /// 查询需要上报的节点数据 /// </summary> /// <param name="nodeId">当前上报数据的节点ID</param> /// <param name="startTime">数据开始时间</param> /// <param name="endTime">数据结束时间</param> /// <returns></returns> private static DataTable GetStorageData(int nodeId, DateTime startTime, DateTime endTime, int?storageId = null) { string sql = string.Empty; if (storageId == null) { sql = "select StorageId from waybill_Node where id = " + nodeId; object obj = DbHelperMySQL.GetSingle(sql); if (obj == null) { throw new Exception("获取节点冷藏载体失败:[NodeId]" + nodeId); } storageId = Convert.ToInt32(obj); } sql = string.Format("select * from aiinfo where storageId = '" + storageId + "' and actived=0 order by pointType"); StringBuilder dataSql = new StringBuilder("select * from (select t1.dataTime,CONCAT("); try { bool haveH = false; string notNullPoint = string.Empty; using (MySqlDataReader reader = DbHelperMySQL.ExecuteReader(sql)) { while (reader.Read()) { int pointType = Convert.ToInt32(reader["pointType"]); int pointId = Convert.ToInt32(reader["pointId"]); switch (pointType) { case 1: dataSql.AppendLine("max(case t1.pointId when '" + pointId + "' then ROUND(t1.data,1) end),'|',"); if (!notNullPoint.Contains("t is not null")) { notNullPoint += " t is not null"; } break; case 2: //if (haveH) // break; haveH = true; if (!dataSql.ToString().Contains(") as 't'")) { dataSql.Length -= 7; dataSql.Append(") as 't',CONCAT("); } dataSql.AppendLine("max(case t1.pointId when '" + pointId + "' then ROUND(t1.data,1) end),'|',"); //dataSql.AppendLine(") as 't',max(case t1.pointType when 2 then ROUND(t1.data,1) end) as 'h'"); if (!notNullPoint.Contains("h is not null")) { notNullPoint += " and h is not null"; } break; case 3: if (haveH) { if (!dataSql.ToString().Contains(") as 'h'")) { dataSql.Length -= 7; dataSql.Append(") as 'h'"); } } else if (!dataSql.ToString().Contains(") as 't'")) { dataSql.Length -= 7; dataSql.Append(") as 't'"); } dataSql.AppendLine(",max(case t1.pointType when 3 then ROUND(t1.data,4) end) as 'lo'"); if (!notNullPoint.Contains("lo is not null")) { notNullPoint += " and lo is not null"; } break; case 4: if (haveH) { if (!dataSql.ToString().Contains(") as 'h'")) { dataSql.Length -= 7; dataSql.Append(") as 'h'"); } } else if (!dataSql.ToString().Contains(") as 't'")) { dataSql.Length -= 7; dataSql.Append(") as 't'"); } dataSql.AppendLine(",max(case t1.pointType when 4 then ROUND(t1.data,4) end) as 'la'"); if (!notNullPoint.Contains("la is not null")) { notNullPoint += " and la is not null"; } break; } } } if (haveH && !dataSql.ToString().Contains(") as 'h'")) { dataSql.Length -= 7; dataSql.Append(") as 'h'"); } //startTime.ToString("yyyy-MM-dd HH:mm:ss") //endTime.ToString("yyyy-MM-dd HH:mm:ss") dataSql.AppendLine(string.Format(" from (select a.pointId,a.pointType, d.data, d.datatime from aiinfo a join history_data_{0} d on a.pointId = d.pointId where d.datatime > '{1}' and d.datatime<= '{2}') t1 GROUP BY t1.dataTime", storageId, startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"))); dataSql.AppendLine(") t2 where " + notNullPoint + " order by dataTime limit " + Utility._NodeDataUploadCount); DataSet ds = DbHelperMySQL.Query(dataSql.ToString()); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { return(null); } return(ds.Tables[0]); } catch (Exception ex) { throw new Exception(ex.Message + "[" + dataSql.ToString() + "]"); } }