/// <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() + "]");
}
}
