/// <summary>
/// 上报成功后更新处理标记为1
/// </summary>
/// <param name="nodeId"></param>
/// <returns></returns>
public static bool UpdateNodeHandleFlag(int nodeId)
{
string sql = "update waybill_node set handleflag = 1 where id = " + nodeId;
return(DbHelperMySQL.ExecuteSql(sql) >= 1);
}
public static bool AddTMSOrders(XML orders)
{
Utility.AddLogText("开始进入TMS运单保存逻辑");
string dtNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
List <string> sqlList = new List <string>();
int result = 0;
try
{
int customerId = GetCustomerIdBySenderCode("", 1);
foreach (var order in orders.CONTENTLIST)
{
string sqlOrder = string.Format("INSERT INTO `coldchain_logistics_db`.`huadong_tms_order` (`code`, `SRCEXPNO`, `ROADID`, `SHIPDETAILID`, `TOTALID`, `LEGCODE`, `SHIPMENTCODE`, `CONSIGNORCODE`, `CONSIGNORNAME`, `DEPTNO`, `DEPTNAME`, `CUSTOMERCODE`, `CUSTOMERNAME`, `AREAHOUSE`, `SALESMAN`, `TRANSMODEID`, `ERPTRANSMODENAME`, `OPERATIONTYPE`, `DEMANDARRIVETIME`, `TRANSPORTTYPE`, `ORDERINSTANCY`, `TRANSPORTCATEGORY`, `ROUTENO`, `TRANSDEADLINE`, `FROMGTRANSID`, `FROMGTRANSNAME`, `TOGTRANSID`, `TOGTRANSNAME`, `RECEIVEADDR`, `RECEIVEMAN`, `RECEIVEPHONE`, `CREDATE`, `INOUTFLAG`, `WMSROUTEWAVENO`, `PRINTTYPE`, `TOTALQUNTITY`, `WHOLEQUNTITY`, `PARTQUNTITY`, `JFQUNTITY`, `DESCRIPTION`, `EXTCOL0`, `EXTCOL1`, `EXTCOL2`, `EXTCOL3`, `EXTCOL4`, `EXTCOL5`, `EXTCOL6`, `EXTCOL7`, `EXTCOL8`, `EXTCOL9`, `EXTCOL10`, `EXTCOL11`, `EXTCOL12`, `EXTCOL13`, `EXTCOL14`, `EXTCOL15`, `EXTCOL16`, `EXTCOL17`, `EXTCOL18`, `EXTCOL19`,`SecretKey`,`CreateTime`,`customerId`) VALUES ('{0}', '{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}', '{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}', '{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}', '{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}', '{41}','{42}','{43}','{44}','{45}','{46}','{47}','{48}','{49}','{50}', '{51}','{52}','{53}','{54}','{55}','{56}','{57}','{58}','{59}','{60}','{61}','{62}');", order.HEADER.CODE, order.HEADER.SRCEXPNO, order.HEADER.ROADID, order.HEADER.SHIPDETAILID, order.HEADER.TOTALID, order.HEADER.LEGCODE, order.HEADER.SHIPMENTCODE, order.HEADER.CONSIGNORCODE, order.HEADER.CONSIGNORNAME, order.HEADER.DEPTNO, order.HEADER.DEPTNAME, order.HEADER.CUSTOMERCODE, order.HEADER.CUSTOMERNAME, order.HEADER.AREAHOUSE, order.HEADER.SALESMAN, order.HEADER.TRANSMODEID, order.HEADER.ERPTRANSMODENAME, order.HEADER.OPERATIONTYPE, order.HEADER.DEMANDARRIVETIME, order.HEADER.TRANSPORTTYPE, order.HEADER.ORDERINSTANCY, order.HEADER.TRANSPORTCATEGORY, order.HEADER.ROUTENO, order.HEADER.TRANSDEADLINE, order.HEADER.FROMGTRANSID, order.HEADER.FROMGTRANSNAME, order.HEADER.TOGTRANSID, order.HEADER.TOGTRANSNAME, order.HEADER.RECEIVEADDR, order.HEADER.RECEIVEMAN, order.HEADER.RECEIVEPHONE, order.HEADER.CREDATE, order.HEADER.INOUTFLAG, order.HEADER.WMSROUTEWAVENO, order.HEADER.PRINTTYPE, order.HEADER.TOTALQUNTITY, order.HEADER.WHOLEQUNTITY, order.HEADER.PARTQUNTITY, order.HEADER.JFQUNTITY, order.HEADER.DESCRIPTION, order.HEADER.EXTCOL0, order.HEADER.EXTCOL1, order.HEADER.EXTCOL2, order.HEADER.EXTCOL3, order.HEADER.EXTCOL4, order.HEADER.EXTCOL5, order.HEADER.EXTCOL6, order.HEADER.EXTCOL7, order.HEADER.EXTCOL8, order.HEADER.EXTCOL9, order.HEADER.EXTCOL10, order.HEADER.EXTCOL11, "供应链", order.HEADER.EXTCOL13, order.HEADER.EXTCOL14, order.HEADER.EXTCOL15, order.HEADER.EXTCOL16, order.HEADER.EXTCOL17, order.HEADER.EXTCOL18, order.HEADER.EXTCOL19, Utility._SecretKey, dtNow, customerId);
Utility.AddLogText(string.Format("添加第三方运单：{0}", sqlOrder));
sqlList.Add(sqlOrder);
foreach (var detail in order.BODY.DETAILLIST)
{
string sqlDetail = string.Format("INSERT INTO `coldchain_logistics_db`.`huadong_tms_orderDetail` (`TMSORDERDTLID`, `BMSORDERDTLID`, `SALESNO`, `BACODE`, `GOODSOWNID`, `GOODSCODE`, `GOODSNAME`, `GOODSTYPE`, `DRUGTYPE`, `LOTNO`, `PRODDATE`, `APPROVENO`, `TRADEPACK`, `PACKAGEUNIT`, `PACKAGEQTY`, `QUNTITY`, `FACTQUNTITY`, `WHOLEQUNTITY`, `SINGLEQUNTITY`, `WEIGHT`, `VOLUME`, `VALIDDATE`, `PINBACK`, `WORKREQUIRE`, `EXTCOL0`) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}');", detail.TMSORDERDTLID, detail.BMSORDERDTLID, detail.SALESNO, detail.BACODE, detail.GOODSOWNID, detail.GOODSCODE, detail.GOODSNAME, detail.GOODSTYPE, detail.DRUGTYPE, detail.LOTNO, detail.PRODDATE, detail.APPROVENO, detail.TRADEPACK, detail.PACKAGEUNIT, detail.PACKAGEQTY, detail.QUNTITY, detail.FACTQUNTITY, detail.WHOLEQUNTITY, detail.SINGLEQUNTITY, detail.WEIGHT, detail.VOLUME, detail.VALIDDATE, detail.PINBACK, detail.WORKREQUIRE, detail.EXTCOL0);
sqlList.Add(sqlDetail);
}
}
#region xxx
//List<string> tmsNumbers = orders.CONTENTLIST.Select(l => l.HEADER.SHIPDETAILID).ToList();
//tmsNumbers.AddRange(orders.CONTENTLIST.Select(l => l.HEADER.LEGCODE));
//tmsNumbers = tmsNumbers.Distinct().ToList();
////查询关系表，查找是否存在已经扫描过的单，如果存在则更新订单表中的信息
//string sql = string.Format("select relationId,number from huadong_tmsorder_waybillbase where relationId in ('{0}');", string.Join("','", tmsNumbers));
//DataSet ds = DbHelperMySQL.Query(sql);
//Utility.AddLogText(string.Format("查询关系表:{0},执行结果:{1}",sql,ds.Tables.Count));
//if (ds != null && ds.Tables.Count > 0)
//{
// Utility.AddLogText("开始更新waybill_base表中的信息");
// foreach (DataRow row in ds.Tables[0].Rows)
// {
// string relationId = row["relationId"].ToString();
// string number = row["number"].ToString();
// Utility.AddLogText(string.Format("更新waybill_base表,{0},{1}", relationId, number));
// CONTENT order = orders.CONTENTLIST.Find(l => l.HEADER.LEGCODE == relationId || l.HEADER.SHIPDETAILID == relationId);
// string huadongbase=string.Format("update waybill_base set senderPerson = '{0}',senderTel='{1}',senderAddress='{2}',receiverOrg='{3}',receiverPerson='{4}',receiverTel='{5}',receiverAddress='{6}' where number = '{7}';", order.HEADER.EXTCOL12, "", order.HEADER.EXTCOL11, order.HEADER.CUSTOMERNAME, order.HEADER.RECEIVEMAN, order.HEADER.RECEIVEPHONE, order.HEADER.RECEIVEADDR, number);
// sqlList.Add(huadongbase);
// Utility.AddLogText(huadongbase);
// //string hudaongkey =string.Format("update huadong_tms_order set SecretKey = '{0}' where relationId = '{1}' and customerId={2} ;", Utility._SecretKey, relationId, customerId);
// string hudaongkey = string.Format("update huadong_tms_order set `code`='{0}', `SRCEXPNO`='{1}', `ROADID`='{2}', `TOTALID`='{3}', `SHIPMENTCODE`= '{4}', `CONSIGNORCODE`= '{5}', " +
// "`CONSIGNORNAME`= '{6}', `DEPTNO`= '{7}', `DEPTNAME`= '{8}', `CUSTOMERCODE`= '{9}', `CUSTOMERNAME`= '{10}', `AREAHOUSE`= '{11}'," +
// " `SALESMAN`= '{12}', `TRANSMODEID`= '{13}',`ERPTRANSMODENAME`= '{14}', `OPERATIONTYPE`= '{15}', `DEMANDARRIVETIME`= '{16}', `TRANSPORTTYPE`= '{17}',"+
// " `ORDERINSTANCY`= '{18}', `TRANSPORTCATEGORY`= '{19}',`ROUTENO`= '{20}', `TRANSDEADLINE`= '{21}', `FROMGTRANSID`= '{22}', `FROMGTRANSNAME`= '{23}',"+
// " `TOGTRANSID`= '{24}', `TOGTRANSNAME`= '{25}', `RECEIVEADDR`= '{26}',`RECEIVEMAN`= '{27}', `RECEIVEPHONE`= '{28}', `CREDATE`= '{29}',"+
// " `INOUTFLAG`= '{30}',`WMSROUTEWAVENO`= '{31}', `PRINTTYPE`= '{32}', `TOTALQUNTITY`= '{33}', `WHOLEQUNTITY`= '{34}',`PARTQUNTITY`= '{35}', "+
// "`JFQUNTITY`= '{36}', `DESCRIPTION`= '{37}', `EXTCOL0`= '{38}', `EXTCOL1`= '{39}', `EXTCOL2`= '{40}', `EXTCOL3`= '{41}', "+
// "`EXTCOL4`= '{42}', `EXTCOL5`= '{43}', `EXTCOL6`= '{44}', `EXTCOL7`= '{45}', `EXTCOL8`= '{46}', `EXTCOL9`= '{47}',"+
// " `EXTCOL10`= '{48}', `EXTCOL11`= '{49}', `EXTCOL12`= '{50}', `EXTCOL13`= '{51}',`EXTCOL14`= '{52}', `EXTCOL15`= '{53}',"+
// " `EXTCOL16`= '{54}', `EXTCOL17`= '{55}', `EXTCOL18`= '{56}', `EXTCOL19`= '{57}',`SecretKey`= '{58}' where relationId = '{59}' and customerId = {60} ",
// order.HEADER.CODE, order.HEADER.SRCEXPNO, order.HEADER.ROADID, order.HEADER.TOTALID, order.HEADER.SHIPMENTCODE, order.HEADER.CONSIGNORCODE,
// order.HEADER.CONSIGNORNAME, order.HEADER.DEPTNO, order.HEADER.DEPTNAME, order.HEADER.CUSTOMERCODE, order.HEADER.CUSTOMERNAME, order.HEADER.AREAHOUSE,
// order.HEADER.SALESMAN, order.HEADER.TRANSMODEID, order.HEADER.ERPTRANSMODENAME, order.HEADER.OPERATIONTYPE, order.HEADER.DEMANDARRIVETIME, order.HEADER.TRANSPORTTYPE,
// order.HEADER.ORDERINSTANCY, order.HEADER.TRANSPORTCATEGORY, order.HEADER.ROUTENO, order.HEADER.TRANSDEADLINE, order.HEADER.FROMGTRANSID, order.HEADER.FROMGTRANSNAME,
// order.HEADER.TOGTRANSID, order.HEADER.TOGTRANSNAME, order.HEADER.RECEIVEADDR, order.HEADER.RECEIVEMAN, order.HEADER.RECEIVEPHONE, order.HEADER.CREDATE,
// order.HEADER.INOUTFLAG, order.HEADER.WMSROUTEWAVENO, order.HEADER.PRINTTYPE, order.HEADER.TOTALQUNTITY, order.HEADER.WHOLEQUNTITY, order.HEADER.PARTQUNTITY,
// order.HEADER.JFQUNTITY, order.HEADER.DESCRIPTION, order.HEADER.EXTCOL0, order.HEADER.EXTCOL1, order.HEADER.EXTCOL2, order.HEADER.EXTCOL3,
// order.HEADER.EXTCOL4, order.HEADER.EXTCOL5, order.HEADER.EXTCOL6, order.HEADER.EXTCOL7, order.HEADER.EXTCOL8, order.HEADER.EXTCOL9,
// order.HEADER.EXTCOL10, order.HEADER.EXTCOL11, order.HEADER.EXTCOL12, order.HEADER.EXTCOL13, order.HEADER.EXTCOL14, order.HEADER.EXTCOL15,
// order.HEADER.EXTCOL16, order.HEADER.EXTCOL17, order.HEADER.EXTCOL18, order.HEADER.EXTCOL19, Utility._SecretKey, relationId, customerId);
// sqlList.Add(hudaongkey);
// Utility.AddLogText(hudaongkey);
// }
//}
#endregion
result = DbHelperMySQL.ExecuteSqlTran(sqlList);
Utility.AddLogText("保存TMS运单结果：" + result);
return(result > 0);
}
catch (Exception ex)
{
Utility.AddLogText(string.Format("AddTMSOrders,保存TMS运单出错：{0}", ex.Message));
throw new Exception("保存TMS运单出错：" + ex.Message);
}
finally
{
HandleScanLaterOrder();
}
}
/// <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() + "]");
}
}