public ResultModel <bool> UploadNodeForStorage(Model_Waybill_Node node, int customerId, int?parentStorageId)
{
if (node != null)
{
LogServer.AddLogText("--------------------------------------------------------------", node.BaseId);
}
else
{
LogServer.AddLogText("实体为空", "XXX");
}
ResultModel <bool> result = new ResultModel <bool>();
try
{
LogServer.AddLogText(string.Format("上报第三方运单节点{0},customerId:{1},parentStorageId", node.BaseId, customerId, parentStorageId), node.BaseId);
result.Data = PDA_WaybillServer.UploadWaybill_Node(node, customerId, parentStorageId);
LogServer.AddLogText("退出节点逻辑", node.BaseId);
}
catch (Exception ex)
{
HandleExcepthin(result, ex);
LogServer.AddLogText("节点错误", node.BaseId);
}
finally
{
if (node != null)
{
LogServer.AddLogText("--------------------------------------------------------------", node.BaseId);
}
}
return(result);
}
//上报第三方运单
public ResultModel <int> UploadThirdOrderForCustomer(Model_ThirdPartOrder orderInfo, int customerId)
{
if (orderInfo != null)
{
LogServer.AddLogText("--------------------------------------------------------------", orderInfo.RelationId);
}
else
{
LogServer.AddLogText("实体为空", "XXX");
}
ResultModel <int> result = new ResultModel <int>();
try
{
LogServer.AddLogText(string.Format("上报第三方运单{0},customerId:{1}", orderInfo.RelationId, customerId), orderInfo.RelationId);
result.Data = PDA_HuadongTmsOrderServer.UploadThirdPartyOrder(orderInfo, customerId);
LogServer.AddLogText("退出运单逻辑", orderInfo.RelationId);
}
catch (Exception ex)
{
HandleExcepthin(result, ex);
LogServer.AddLogText("运单错误", orderInfo.RelationId);
}
finally {
if (orderInfo != null)
{
LogServer.AddLogText("--------------------------------------------------------------", orderInfo.RelationId);
}
}
return(result);
}
//上报签收图片统一入口，如果是自运单，则CustomerID传入0
public ResultModel <bool> UploadPostbackForCustomers(Model_Waybill_Postback_Pic postback, DateTime postbackTime, List <object> picList, int customerId)
{
if (postback != null)
{
LogServer.AddLogText("-------------------------------------------------------------------", postback.BaseId);
}
else
{
LogServer.AddLogText("实体类为空", "xxxx");
}
ResultModel <bool> result = new ResultModel <bool>();
try
{
LogServer.AddLogText(string.Format("上传图片{0},DateTime:{1},customerId:{2}", postback.BaseId, postbackTime, customerId), postback.BaseId);
result.Data = PDA_WaybillServer.UploadWaybill_Postbacks(postback, postbackTime, picList, customerId);
LogServer.AddLogText("退出保存图片逻辑", postback.BaseId);
}
catch (Exception ex)
{
HandleExcepthin(result, ex);
LogServer.AddLogText("图片错误", postback.BaseId);
}
finally
{
if (postback != null)
{
LogServer.AddLogText("--------------------------------------------------------------", postback.BaseId);
}
}
return(result);
}
/// <summary>
/// 检查订单是否存在
/// </summary>
/// <param name="relationId"></param>
/// <returns></returns>
private static Model_Waybill_Base CheckThirdPartyNumberExist(string relationId)
{
string sql = string.Format("select * from waybill_base where number in (select number from huadong_tmsorder_waybillbase where relationId = '{0}') and number<>'' and number is not null", relationId);
LogServer.AddLogText(sql, relationId);
return(_SqlHelp.ExecuteObject <Model_Waybill_Base>(sql));
}
/// <summary>
/// 添加冷链数据上报进度信息
/// </summary>
/// <param name="nodeInfo">节点信息</param>
/// <param name="parentStorageId"></param>
private static void SaveUploadDataProgress(Model_Waybill_Node nodeInfo, int parentStorageId, bool isFirstNode)
{
List <string> sqlList = new List <string>();
try
{
//向上查询 关联节点更新上一个记录的结束时间
string updateParent = string.Format("update uploadDataProgress set endNodeTime = '{0}' where relationId='{1}' and nodeTime<'{0}' and storageId = {2};", nodeInfo.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), nodeInfo.BaseId, parentStorageId);
if (nodeInfo.Arrived == Enum_Arrived.HaveArrived)
{
sqlList.Add(updateParent);
}
else
{
//插入新的记录
string insertSql = string.Format("INSERT INTO `uploadDataProgress` ( `relationId`, `storageId`, `storageName`, `nodeParentStorageId`, `nodeTime`, `endNodeTime`, `uploadProgress`, `lastHandleTime`, `handleFlag`) VALUES ( '{0}', '{1}', '{2}', '{3}', '{4}', {5}, '{4}', '{6}','{7}');", nodeInfo.BaseId, nodeInfo.StorageId, nodeInfo.StorageName, parentStorageId, nodeInfo.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), "【ENDNODETIME】", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), 0);
//向下查询 关联节点更新当前记录的结束时间
string selectSql = string.Format("select nodeTime from uploadDataProgress where relationId='{0}' and nodeTime>'{1}' and nodeParentStorageId = {2} order by nodeTime LIMIT 1", nodeInfo.BaseId, nodeInfo.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), nodeInfo.StorageId);
object objNodeTime = _SqlHelp.ExecuteScalar(selectSql);
string endNodeTime = "NULL";
if (objNodeTime != null)
{
endNodeTime = "'" + Convert.ToDateTime(objNodeTime).ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
insertSql = insertSql.Replace("【ENDNODETIME】", endNodeTime);
sqlList.Add(insertSql);
if (!isFirstNode)
{
sqlList.Add(updateParent);
}
}
_SqlHelp.ExecuteTranstration(sqlList);
}
catch (Exception ex)
{
throw ex;
}
finally {
foreach (string item in sqlList)
{
LogServer.AddLogText(string.Format("添加上报处理进度:{0}", item), nodeInfo.BaseId);
}
}
}
/// <summary>
/// 插入运单
/// </summary>
/// <param name="waybillList">运单集合</param>
/// <returns></returns>
public static bool UploadWaybill_Base(List <Model_Waybill_Base> waybillList)
{
if (waybillList.Count == 0)
{
return(false);
}
bool result = false;
try
{
List <Model_Waybill_Base> existList = GetExistWaybills(waybillList.Select(l => l.Number).Distinct().ToList());
StringBuilder sql = new StringBuilder("insert into waybill_base (number,senderId,senderOrg,senderPerson,senderTel,senderAddress,receiverId,receiverOrg,receiverPerson,receiverTel,receiverAddress,billingCount,stage,beginAt,company) values ");
int distinctCount = 0;//重复运单的数量
foreach (Model_Waybill_Base item in waybillList)
{
if (existList.Where(l => l.Number == item.Number).Count() > 0)
{
distinctCount++;
}
else
{
sql.AppendFormat("('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}'),", item.Number, item.SenderId, item.SenderOrg, item.SenderPerson, item.SenderTel, item.SenderAddress, item.ReceiverId == 0 ? "NULL" : item.ReceiverId.ToString(), item.ReceiverOrg, item.ReceiverPerson, item.ReceiverTel, item.ReceiverAddress, item.BillingCount, (int)item.Stage, item.BeginAt.ToString("yyyy-MM-dd HH:mm:ss"), 0);
}
}
if (distinctCount == waybillList.Count)
{
return(true);//如果全部重复，则返回True，通知PDA删除运单
}
sql.Length -= 1;
result = _SqlHelp.ExecuteNonQuery(sql.ToString()) == waybillList.Count - distinctCount;
//运单未上报，节点已存在，把节点信息添加到waybill_base表中并删除unnecessary_node表的节点信息.
GetOldNodeByWaybill(waybillList);
GetWaybillPictures(waybillList);
string cwMsg = "(上报运单)Number=" + waybillList.First().Number + ";Sender=" + waybillList.First().SenderOrg + ";Receiver=" + waybillList.First().ReceiverOrg + ";BeginAt=" + waybillList.First().BeginAt.ToString("yyyy-MM-dd HH:mm:ss");
Console.WriteLine(cwMsg);
LogServer.AddLogText(cwMsg, waybillList.First().Number);
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <summary>
/// 查询运单以前是否存在节点信息，没有超过一个月的节点信息保存到节点表中，超过时间的节点信息删除。
/// </summary>
/// <param name="waybillList"></param>
public static void GetOldNodeByWaybill(List <Model_Waybill_Base> waybillList)
{
string sql = string.Format("delete from unnecessary_node where inserttime < '{0}'", DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss"));
try
{
_SqlHelp.ExecuteNonQuery(sql);
List <Model_Waybill_Base> numberList = GetExistWaybills(waybillList.Select(l => l.Number).Distinct().ToList());
foreach (Model_Waybill_Base item in waybillList)
{
sql = string.Format("select * from unnecessary_node where baseId='{0}' and operateAt>'{1}';", item.Number, item.BeginAt.ToString("yyyy-MM-dd HH:mm:ss"));
List <Model_UnnecessaryNode> waybillnode = _SqlHelp.ExecuteObjects <Model_UnnecessaryNode>(sql);
if (waybillnode.Count != 0)
{
int baseId = numberList.Find(l => l.Number == item.Number).Id;
string number = numberList.Find(l => l.Number == item.Number).Number;
sql = string.Empty;
foreach (Model_UnnecessaryNode node in waybillnode)
{
sql += string.Format("insert into waybill_node (baseId,operateAt,storageId,storageName,content,arrived,parentStorageId,handleFlag,scanNumber,customerId,insertTime) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{10}','{8}','{9}') ;",
baseId, node.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), node.StorageId, node.StorageName, node.Content, (int)node.Arrived, node.ParentStorageId, (node.CustomerId == 0?-1:0), node.CustomerId, node.InsertTime.ToString("yyyy-MM-dd HH:mm:ss"), node.BaseId);
sql += string.Format("delete from unnecessary_node where id={0}; ", node.Id);
if (node.Arrived == Enum_Arrived.HaveArrived)
{
sql += string.Format("update waybill_base set stage=1 ,signinAt='{0}' where number='{1}';", node.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), number);
}
LogServer.AddLogText(string.Format("sql:{0}", sql), item.Number);
}
int result = _SqlHelp.ExecuteNonQuery(sql);
LogServer.AddLogText("结果:" + result, item.Number);
}
else
{
LogServer.AddLogText(string.Format("自运单临时节点信息不存在,unnecessary_node:{0},结果:{1}", sql, waybillnode.Count), item.Number);
}
}
}
catch
{
}
}
protected override DoWorkResult DoWork(Model_SmsReord sms)
{
try
{
string smsContent = _SmsContentModel.Replace("【运单编号】", sms.Number).Replace("【发货单位】", sms.SenderOrg).Replace("【收货单位】", sms.ReceiverOrg).Replace("【车牌号码】", sms.StorageName);
sms.SmsContent = smsContent;
sms.SendTime = DateTime.Now;
if (string.IsNullOrEmpty(sms.SmsReceiver) || sms.SmsReceiver.Length != 11)
{
sms.SendResult = "接收短信号码不正确";
}
else
{
sms.SendResult = SmsSendHelper.SendSms(sms.SmsReceiver, sms.SmsContent);
}
}
catch (Exception ex)
{
sms.SendResult = ex.Message;
//throw ex;
//return DoWorkResult.AbortCurrentThread;//有异常,可以终止当前线程.当然.也可以继续,
//return DoWorkResult.AbortAllThread; //特殊情况下 ,有异常终止所有的线程...
}
finally
{
try
{
SmsRecordServer.AddSendSmsRecord(sms);
//Console.WriteLine("发送短信："+sms.SmsContent+" 接收号码："+sms.SmsReceiver+" 发送结果："+sms.SendResult);
}
catch (Exception ex)
{
LogServer.AddLogText("保存发送记录出错：" + ex.Message, "SMS_" + DateTime.Now.ToString("yyyyMMdd"));
}
}
//return base.DoWork(pendingValue);
return(DoWorkResult.ContinueThread);
}
/// <summary>
/// 查询临时图片表是否存在图片信息,存在插入图片表中
/// </summary>
/// <param name="waybillList"></param>
public static void GetWaybillPictures(List <Model_Waybill_Base> waybillList)
{
string Exnumber = string.Empty;
string sql = string.Format("delete from temporarypictures where inserttime < '{0}'", DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss"));
try
{
_SqlHelp.ExecuteNonQuery(sql);
List <Model_Waybill_Base> numberList = GetExistWaybills(waybillList.Select(l => l.Number).Distinct().ToList());
foreach (Model_Waybill_Base item in waybillList)
{
Exnumber = item.Number;
sql = string.Format("select * from temporarypictures where baseId='{0}' and operateAt>'{1}';", item.Number, item.BeginAt.ToString("yyyy-MM-dd HH:mm:ss"));
List <Model_TemporaryPictures> waybillpictures = _SqlHelp.ExecuteObjects <Model_TemporaryPictures>(sql);
if (waybillpictures.Count != 0)
{
int baseId = numberList.Find(l => l.Number == item.Number).Id;
string number = numberList.Find(l => l.Number == item.Number).Number;
sql = string.Empty;
foreach (Model_TemporaryPictures pic in waybillpictures)
{
sql += string.Format("insert into waybill_postback_pic (baseId,picName) values({0},'{1}'); ", baseId, pic.PicName);
sql += string.Format("delete from temporarypictures where id={0}; ", pic.id);
sql += string.Format("update waybill_base set picPostbackAt='{0}' where number='{1}';", pic.operateAt, number);
}
int result = _SqlHelp.ExecuteNonQuery(sql);
LogServer.AddLogText(string.Format("sql:{0},结果:{1}", sql, result), item.Number);
}
else
{
LogServer.AddLogText(string.Format("临时图片表不存在图片信息,sql:{0},结果:{1}", sql, waybillpictures.Count), item.Number);
}
}
}
catch (Exception ex)
{
LogServer.AddLogText("GetWaybillPictures异常错误" + ex.Message, Exnumber);
}
}
/// <summary>
/// 查询临时图片表是否存在图片信息，存在保存到图片表中
/// </summary>
/// <param name="RelationId"></param>
public static void HuaDongFcCoPictures(string RelationId)
{
try
{
LogServer.AddLogText("开始删除临时图片表超过一个月的信息", RelationId);
//删除超过一个月的数据
string sql = string.Format("delete from temporarypictures where inserttime<'{0}' ;", DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss"));
_SqlHelp.ExecuteNonQuery(sql);
//查询关系表是否存在信息
sql = string.Format("select * from huadong_tmsorder_waybillbase where relationId='{0}';", RelationId);
Model_Huadong_Tmsorder_Waybillbase huadongbase = _SqlHelp.ExecuteObject <Model_Huadong_Tmsorder_Waybillbase>(sql);
if (huadongbase != null)
{
//查询运单信息是否存在
sql = string.Format("select * from waybill_base where number='{0}'; ", huadongbase.number);
Model_Waybill_Base waybbase = _SqlHelp.ExecuteObject <Model_Waybill_Base>(sql);
if (waybbase != null)
{
//查询临时图片表是否存在图片信息
sql = string.Format("select * from temporarypictures where baseId='{0}' and operateAt>='{1}';", RelationId, waybbase.BeginAt.ToString("yyyy-MM-dd HH:mm:ss"));
List <Model_TemporaryPictures> waybnode = _SqlHelp.ExecuteObjects <Model_TemporaryPictures>(sql);
if (waybnode.Count != 0)
{
sql = string.Empty;//清空sql语句
foreach (Model_TemporaryPictures item in waybnode)
{
string tempSql = string.Format("insert into waybill_postback_pic(baseId,picName) values({0},'{1}') ; ",
waybbase.Id, item.PicName);
LogServer.AddLogText("插入图片:" + tempSql, RelationId);
//查询到的信息保存到节点中
sql += tempSql;
//删除暂存表中的信息
tempSql = string.Format("delete from temporarypictures where id={0} ;", item.id);
LogServer.AddLogText("删除临时图片:" + tempSql, RelationId);
sql += tempSql;
tempSql = string.Format("update waybill_base set picPostbackAt='{0}' where number='{1}';", item.operateAt, waybbase.Number);
LogServer.AddLogText("更新运单图片时间:" + tempSql, RelationId);
sql += tempSql;
}
int result = _SqlHelp.ExecuteNonQuery(sql);
LogServer.AddLogText("临时图片信息保存到图片表，删除临时图片表中的信息，结果:" + result, RelationId);
}
else
{
LogServer.AddLogText(string.Format("图片表不存在图片信息,sql:{0},结果:{1}", sql, waybnode.Count), RelationId);
}
}
else
{
LogServer.AddLogText(string.Format("运单信息不存在,sql:{0},结果:{1}", sql, waybbase == null ? "没有信息" : "1"), RelationId);
}
}
else
{
LogServer.AddLogText(string.Format("关联信息不存在,sql:{0},结果:{1}", sql, huadongbase == null ? "没有信息" : "1"), RelationId);
}
}
catch (Exception ex)
{
LogServer.AddLogText("HuaDongFcCoTable异常：" + ex.Message, RelationId);
}
}
/// <summary>
/// 查询暂存表是否存在运单节点信息，存在保存到节点表中
/// </summary>
/// <param name="RelationId">运单号</param>
public static void HuaDongFcCoTable(string RelationId)
{
try
{
LogServer.AddLogText("开始删除临时表超过一个月的信息", RelationId);
//删除超过一个月的数据
string sql = string.Format("delete from unnecessary_node where inserttime<'{0}' ;", DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss"));
_SqlHelp.ExecuteNonQuery(sql);
//查询关系表是否存在信息
sql = string.Format("select * from huadong_tmsorder_waybillbase where relationId='{0}';", RelationId);
LogServer.AddLogText(string.Format("查询关系表是否存在信息{0}", sql), RelationId);
Model_Huadong_Tmsorder_Waybillbase huadongbase = _SqlHelp.ExecuteObject <Model_Huadong_Tmsorder_Waybillbase>(sql);
if (huadongbase != null)
{
//查询运单信息是否存在
sql = string.Format("select * from waybill_base where number='{0}'; ", huadongbase.number);
LogServer.AddLogText(string.Format("查询运单信息是否存在{0} ", sql), RelationId);
Model_Waybill_Base waybbase = _SqlHelp.ExecuteObject <Model_Waybill_Base>(sql);
if (waybbase != null)
{
//查询暂存表是否存在节点信息
sql = string.Format("select * from unnecessary_node where baseId='{0}' and operateAt>='{1}';", RelationId, waybbase.BeginAt.ToString("yyyy-MM-dd HH:mm:ss"));
LogServer.AddLogText(string.Format("查询暂存表是否存在节点信息{0}", sql), RelationId);
List <Model_UnnecessaryNode> waybnode = _SqlHelp.ExecuteObjects <Model_UnnecessaryNode>(sql);
if (waybnode.Count != 0)
{
sql = string.Empty;//清空sql语句
foreach (Model_UnnecessaryNode item in waybnode)
{
string tempSql = string.Format("insert into waybill_node (baseId,operateAt,storageId,storageName,content,arrived,parentStorageId,handleFlag,scanNumber,customerId,insertTime) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{10}','{8}','{9}') ; ",
waybbase.Id, item.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), item.StorageId, item.StorageName, item.Content, (int)item.Arrived, item.ParentStorageId, (item.CustomerId == 0 ? -1 : 0), item.CustomerId, item.InsertTime.ToString("yyyy-MM-dd HH:mm:ss"), item.BaseId);
LogServer.AddLogText("插入节点:" + tempSql, RelationId);
//查询到的信息保存到节点中
sql += tempSql;
//删除暂存表中的信息
tempSql = string.Format("delete from unnecessary_node where id={0} ;", item.Id);
LogServer.AddLogText("删除临时节点:" + tempSql, RelationId);
sql += tempSql;
if (item.Arrived == Enum_Arrived.HaveArrived)
{
tempSql = string.Format("update waybill_base set stage=1 ,signinAt='{0}' where number='{1}';", item.operateAt, waybbase.Number);
LogServer.AddLogText("更新运单运抵时间:" + tempSql, RelationId);
sql += tempSql;
}
}
int result = _SqlHelp.ExecuteNonQuery(sql);
LogServer.AddLogText("查询到的信息保存到节点中，删除暂存表中的信息", RelationId);
}
else
{
LogServer.AddLogText("暂存表不存在节点信息", RelationId);
}
}
else
{
LogServer.AddLogText("运单信息不存在", RelationId);
}
}
else
{
LogServer.AddLogText("关系表信息不存在", RelationId);
}
}
catch (Exception ex)
{
LogServer.AddLogText("HuaDongFcCoTable异常：" + ex.Message, RelationId);
}
}
/// <summary>
/// 创建第三方运单
/// </summary>
/// <param name="orderInfo"></param>
/// <returns>0:处理失败; 1:处理成功; 2:运单已存在</returns>
public static int UploadThirdPartyOrder(Model_ThirdPartOrder orderInfo, int customerId = 0)
{
int result = 0;
try
{
LogServer.AddLogText(string.Format("进入创建第三方运单逻辑{0},操作时间{1}", orderInfo.RelationId, orderInfo.OperateAt), orderInfo.RelationId);
if (orderInfo == null)
{
result = 1;
}
string sql = string.Empty;
Model_ThirdCustomer customerInfo = new Model_ThirdCustomer();
//检查是否已经创建过第三方运单的关联运单
Model_Waybill_Base IsExist = CheckThirdPartyNumberExist(orderInfo.RelationId);
LogServer.AddLogText(string.Format("CheckThirdPartyNumberExist:{0}", IsExist != null ? 1 : 0), orderInfo.RelationId);
if (IsExist == null)
{
#region 获取自动生成的运单号
//sql = "select concat('99', 1000000000 + Count(*) + 1) from waybill_base where company = 1";
//object obj = _SqlHelp.ExecuteScalar(sql);
string number = string.Empty;
string nHead = "981000000000";
if (customerId != 2)
{
nHead = "991000000000";
}
int jLen = orderInfo.RelationId.Length;
//if (orderInfo.RelationId.Length >= 12)
// jLen = 10;
//number = nHead.Substring(0, 12 - orderInfo.RelationId.Length) + orderInfo.RelationId;
if (orderInfo.RelationId.Length > 9)
{
string str1 = orderInfo.RelationId.ToString().Substring(orderInfo.RelationId.Length - 9);
number = nHead.Substring(0, 12 - str1.Length) + str1;
}
else
{
number = nHead.Substring(0, 12 - orderInfo.RelationId.Length) + orderInfo.RelationId;
}
LogServer.AddLogText("拼接后的运单号：" + number + "", orderInfo.RelationId);
//if (obj != null)
//{
// number = obj.ToString();
// if (obj.ToString() == "System.Byte[]")
// number = Encoding.Default.GetString(obj as byte[]);
//}
#endregion
#region 旧版通过配置文件获取第三方供应商名称，新版查询根据CustomerID查询
int Huadong_Id = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Huadong_Id"]);
string Huadong_Name = System.Configuration.ConfigurationManager.AppSettings["Huadong_Name"];
if (customerId != 0)
{
Huadong_Id = customerId;
customerInfo = PDA_CustomerServer.GetThirdCustomers(customerId).First();
Huadong_Name = customerInfo.CustomerName;
}
#endregion
List <string> sqlList = new List <string>();
//更新关系ID
sql = string.Format("update huadong_tms_order set relationId='{0}' where (SHIPDETAILID = '{0}' or LEGCODE = '{0}' )", orderInfo.RelationId);
sql += customerId == 0 ? string.Empty : (" and customerId = " + customerId);
int count = _SqlHelp.ExecuteNonQuery(sql);
LogServer.AddLogText(string.Format("更新第三方运单relationId：{0},结果：{1}", sql, count), orderInfo.RelationId);
if (count == 0)
{
LogServer.AddLogText(string.Format("第三方运单不存在时，先插入一条空信息的运单，待第三方运单同步回来时更新运单信息", orderInfo.RelationId, orderInfo.OperateAt, customerId), orderInfo.RelationId);
//第三方运单不存在时，先插入一条空信息的运单，待第三方运单同步回来时更新运单信息
sqlList.Add(string.Format("INSERT INTO `coldchain_logistics_db`.`huadong_tms_order` ( `relationId`, `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`, `senderOrg`, `senderPerson`, `senderTel`, `senderAddress`, `receiverOrg`, `receiverPerson`, `receiverTel`, `receiverAddress`, `SecretKey`, `CreateTime`, `customerId`) VALUES ( '{0}', '', '', '', '{0}', '', '{0}', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0,0, 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '未知', '{1}', '{2}');", orderInfo.RelationId, orderInfo.OperateAt, customerId));
}
LogServer.AddLogText("开始添加关联运单", orderInfo.RelationId);
//插入本系统自用的关联运单
sqlList.Add(string.Format("insert into huadong_tmsorder_waybillbase(relationId,number{2}) values('{0}', '{1}'{3})", orderInfo.RelationId, number, customerId != 0 ? ",customerId" : "", customerId != 0 ? "," + customerId : ""));
string updateReceiverIdSql = string.Empty;
if (customerInfo.LinkType == 2)
{
sql = string.Format("insert into waybill_base (number,senderId,senderOrg,senderPerson,senderTel,senderAddress,receiverId,receiverOrg,receiverPerson, receiverTel, receiverAddress, billingCount, stage, beginAt, signinAt, picPostbackAt, company) select '{4}' as number, {0} as senderId, '{1}' as senderOrg, senderPerson , senderTel , senderAddress ,null as receiverId, receiverOrg , receiverPerson , receiverTel , receiverAddress,(if(isnull(JFQUNTITY),0,JFQUNTITY)) as billingCount, 0 as stage, '{2}' as beginAt, null as signinAt, null as picPostbackAt, 1 as company from huadong_tms_order where (SHIPDETAILID = '{3}' or LEGCODE = '{3}') and customerId={5} limit 1", Huadong_Id, Huadong_Name, orderInfo.OperateAt, orderInfo.RelationId, number, customerId);
//宁波医药
updateReceiverIdSql = ("update waybill_base set receiverId = (select Id from customer where BindReceiverOrg=receiverOrg order by lastUpdateTime desc limit 1) where number = '" + number + "'");
}
else
{
sql = string.Format("insert into waybill_base (number,senderId,senderOrg,senderPerson,senderTel,senderAddress,receiverId,receiverOrg,receiverPerson, receiverTel, receiverAddress, billingCount, stage, beginAt, signinAt, picPostbackAt, company) select '{4}' as number, {0} as senderId, '{1}' as senderOrg, EXTCOL12 as senderPerson, '' as senderTel, EXTCOL11 as senderAddress,null as receiverId, CUSTOMERNAME as receiverOrg, RECEIVEMAN as receiverPerson, RECEIVEPHONE as receiverTel, RECEIVEADDR as receiverAddress,0 as billingCount, 0 as stage, '{2}' as beginAt, null as signinAt, null as picPostbackAt, 1 as company from huadong_tms_order where SHIPDETAILID = '{3}' or LEGCODE = '{3}' limit 1", Huadong_Id, Huadong_Name, orderInfo.OperateAt, orderInfo.RelationId, number);
//大华东
updateReceiverIdSql = ("update waybill_base set receiverId = (select Id from customer where fullName=receiverOrg order by lastUpdateTime desc limit 1) where number = '" + number + "'");
}
sqlList.Add(sql);
sqlList.Add(updateReceiverIdSql);
foreach (string item in sqlList)
{
LogServer.AddLogText(item, orderInfo.RelationId);
}
result = _SqlHelp.ExecuteTranstration(sqlList) ? 1 : 0;
LogServer.AddLogText(string.Format("执行结果：{0}", result), orderInfo.RelationId);
//回滚暂存表中的可用节点
if (result == 1)
{
HuaDongFcCoTable(orderInfo.RelationId);
HuaDongFcCoPictures(orderInfo.RelationId);
}
LogServer.AddLogText("创建第三方运单逻辑结束", orderInfo.RelationId);
}
else
{
//判断运单操作时间先后，操作时间早的更新后的
if (Convert.ToDateTime(orderInfo.OperateAt) < IsExist.BeginAt)
{
sql = string.Format("update waybill_base set beginAt='{0}' where number='{1}' ;", orderInfo.OperateAt, IsExist.Number);
int updatbeginAt = _SqlHelp.ExecuteNonQuery(sql);
LogServer.AddLogText(string.Format("sql:{0},结果:{1}", sql, updatbeginAt), orderInfo.RelationId);
result = 1;
}
else
{
result = 1;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static bool UploadWaybill_Node(Model_Waybill_Node node, int customerId, int?parentStorageId)
{
bool saveUploadDataProgress = false;//是否保存到冷链数据上报进度表
bool isFirstNode = false;
try
{
if (node != null)
{
LogServer.AddLogText(string.Format("进入创建运单节点逻辑{0},操作时间{1}", node.BaseId, node.operateAt), node.BaseId);
string cwMsg = "(上报节点)NodeNumber=" + node.BaseId + ";Content=" + node.Content + ";CustomerId=" + customerId + ";ParentStorageId=" + (parentStorageId == null ? "无" : parentStorageId.ToString());
Console.WriteLine(cwMsg);
LogServer.AddLogText(cwMsg, node.BaseId);
if (!string.IsNullOrEmpty(node.BaseId) && node.BaseId.Contains(@"\"))
{
node.BaseId = node.BaseId.Replace(@"\", "").Replace("/", "");
Console.WriteLine("替换运单号中的斜杠:" + node.BaseId);
LogServer.AddLogText("替换运单号中的斜杠:" + node.BaseId, node.BaseId);
}
}
string sql = string.Empty;
string number = node.BaseId;//PDA传过来的BaseId就是运单号
//Customer=0为自运单，否则为第三方运单
if (customerId != 0)
{
//第三方运单需要查询关系表获取转换后的运单号
sql = "select number from huadong_tmsorder_waybillbase where relationid='" + number + "' and customerId=" + customerId;
number = _SqlHelp.ExecuteScalar <string>(sql);
LogServer.AddLogText(string.Format("第三方运单需要查询关系表获取转换后的运单号{0},结果：{1}", sql, number), node.BaseId);
}
else
{
LogServer.AddLogText("自运单 :", node.BaseId);
}
Model_Waybill_Base waybillBase = null;//根据运单号获取到BaseId
sql = "select * from waybill_base where number = '" + number + "' order by id desc limit 1";
LogServer.AddLogText("查询运单是否存在:" + sql, node.BaseId);
waybillBase = _SqlHelp.ExecuteObject <Model_Waybill_Base>(sql);
if (waybillBase == null)
{
string remark = "运单[" + (string.IsNullOrEmpty(number) ? node.BaseId : number) + "]不存在";
remark += "传入的CustomerId=" + customerId;
sql = string.Format("insert into unnecessary_node(baseId,operateAt,storageId,storageName,content,arrived,remarks,inserttime,parentStorageId,customerId) values('{0}', '{1}', {2}, '{3}', '{4}', {5}, '{6}','{7}','{8}','{9}');",
node.BaseId, node.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), node.StorageId, node.StorageName, node.Content, (int)node.Arrived, remark, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), parentStorageId, customerId);
int insertResult = _SqlHelp.ExecuteNonQuery(sql);
saveUploadDataProgress = insertResult > 0;
LogServer.AddLogText(remark + ":" + sql + " 执行结果:" + insertResult, node.BaseId);
return(true);//运单不存在，将存入暂存表
}
else
{
LogServer.AddLogText("当前运单存在,开始添加节点", node.BaseId);
}
if (waybillBase.BeginAt == node.operateAt)
{
isFirstNode = true;
}
//查询该运单已有的所有节点
List <Model_Waybill_Node> base_NodeList = new List <Model_Waybill_Node>();
sql = "select id, convert(baseId, CHAR) as baseId ,operateAt,storageId,storageName,content,arrived from waybill_node where baseId = " + waybillBase.Id;
base_NodeList = _SqlHelp.ExecuteObjects <Model_Waybill_Node>(sql);
LogServer.AddLogText("查询该运单已有的所有节点:" + sql + ",查询到的节点条数：" + base_NodeList.Count + "", node.BaseId);
foreach (Model_Waybill_Node item in base_NodeList)
{
//存在内容一致，时间一致的节点时丢弃
if (item.operateAt == node.operateAt)
{
LogServer.AddLogText(string.Format("内容一致，时间一致的节点丢弃;节点ID[{0}] 节点时间[{1}] 节点内容[{2}]", item.Id, item.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), item.Content), node.BaseId);
return(true);
}
if (item.Arrived == Enum_Arrived.HaveArrived)
{
if (node.Arrived == Enum_Arrived.HaveArrived) //存在运抵节点时，并且当前上报的也是运抵节点则丢弃
{
LogServer.AddLogText("已存在运抵节点，当前也是运抵节点，弃之", node.BaseId);
return(true);
}
else if (node.operateAt >= item.operateAt) //存在运抵节点时，当前节点虽然不是运抵节点但是创建时间比运抵时间大的也丢弃
{
LogServer.AddLogText("已存在运抵节点，当前节点时间大于运抵节点时间，弃之", node.BaseId);
return(true);
}
}
}
List <string> sqlList = new List <string>();
sqlList.Add(string.Format("insert into waybill_node (baseId,operateAt,storageId,storageName,content,arrived,parentStorageId,handleFlag,scanNumber,CustomerId,insertTime) values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}','{6}','{7}','{8}','{9}','{10}');", waybillBase.Id, node.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), node.StorageId, node.StorageName.Replace("[默认]", ""), node.Content, (int)node.Arrived, (parentStorageId == null?-1:parentStorageId), (parentStorageId == null ? -1 : (customerId == 0 ? -1 : 0)), node.BaseId, customerId, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
if (node.Arrived == Enum_Arrived.HaveArrived)
{
sqlList.Add(string.Format("update waybill_base set stage=1 ,signinAt='{0}' where number='{1}';", node.operateAt.ToString("yyyy-MM-dd HH:mm:ss"), number));
//运抵时更新此单的上报优先级（第三方运单）
//if (customerId != 0)
// sqlList.Add(string.Format("update huadong_tmsorder_waybillbase set handleCount = null,handleLastTime=now() where number = '{0}';", number));
}
foreach (string item in sqlList)
{
LogServer.AddLogText(string.Format("添加节点:{0}", item), node.BaseId);
}
bool result = _SqlHelp.ExecuteTranstration(sqlList);
LogServer.AddLogText("添加结果：" + result.ToString(), node.BaseId);
saveUploadDataProgress = result;
return(result);
}
catch (Exception ex)
{
LogServer.AddLogText("添加节点失败:" + ex.Message, node.BaseId);
throw ex;
}
finally
{
try
{
if (customerId != 0 && parentStorageId != null && saveUploadDataProgress)
{
SaveUploadDataProgress(node, (int)parentStorageId, isFirstNode);
}
}
catch (Exception ex)
{
LogServer.AddLogText("添加冷链数据上报进度失败,但未影响该节点上报:" + ex.Message, node.BaseId);
}
}
}
///// <summary>
///// 查询运单以前是否存在节点信息，没有超过一个月的节点信息保存到节点表中，超过时间的节点信息删除。
///// </summary>
///// <param name="waybillList"></param>
///// <returns></returns>
//public static int GetNode(List<Model_Waybill_Base> waybillList)
//{
// int result = 0;
// try
// {
// //是否存在运单信息
// List<Model_Waybill_Base> numberList = GetExistWaybills(waybillList.Select(l => l.Number).Distinct().ToList());
// foreach (Model_Waybill_Base item in waybillList)
// {
// //查询unnecessary_node是否存在节点信息
// string sql = string.Format("select * from unnecessary_node where baseId='{0}' ;", item.Number);
// List<Model_Waybill_Node> waybillnode = _SqlHelp.ExecuteObjects<Model_Waybill_Node>(sql);
// if (waybillnode.Count != 0)
// {
// //获取id
// int baseId = numberList.Find(l => l.Number == item.Number).Id;
// foreach (Model_Waybill_Node node in waybillnode)
// {
// //节点信息超过一个月就删除
// if (node.operateAt.AddMonths(1) > item.BeginAt)
// {
// sql = string.Format("insert into waybill_node (baseId,operateAt,storageId,storageName,content,arrived) values('{0}','{1}','{2}','{3}','{4}','{5}') ;",
// baseId, node.operateAt, node.StorageId, node.StorageName, node.Content, (int)node.Arrived);
// result = _SqlHelp.ExecuteNonQuery(sql);
// //添加成功后删除节点
// if (result > 0)
// {
// sql = string.Format("delete from unnecessary_node where id={0} ", node.Id);
// _SqlHelp.ExecuteNonQuery(sql);
// }
// }
// else
// {
// sql = string.Format("delete from unnecessary_node where id={0} ", node.Id);
// _SqlHelp.ExecuteNonQuery(sql);
// }
// }
// }
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// return result;
//}
/// <summary>
/// 上传图片，插入签收拍照信息
/// </summary>
/// <param name="postback">签收拍照信息</param>
/// <param name="postbackTime">签收拍照时间</param>
/// <param name="picList">图片列表</param>
/// <returns></returns>
public static bool UploadWaybill_Postbacks(Model_Waybill_Postback_Pic postback, DateTime postbackTime, List <object> picList, int customerId = 0)
{
LogServer.AddLogText(string.Format("开始进入保存图片逻辑{0}，DateTime:{1}", postback.BaseId, postbackTime), postback.BaseId);
bool result = false;
int picIndex = 0;//成功上传的图片数量
if (picList.Count == 0)
{
LogServer.AddLogText(string.Format("图片列表{0}", picList.Count), postback.BaseId);
return(false);
}
if (string.IsNullOrEmpty(postback.PicName))
{
LogServer.AddLogText(string.Format("图片名称{0}", postback.PicName), postback.BaseId);
return(false);
}
string[] picNameArr = postback.PicName.Split('|');
if (picNameArr.Length != picList.Count)
{
LogServer.AddLogText(string.Format("picNameArr:{0},picList:{1}", picNameArr.Length, picList.Count), postback.BaseId);
return(false);
}
//配置文件中的路径
string filePath = string.Empty;
string tempPath = System.Configuration.ConfigurationManager.AppSettings["PostbackPath"];
if (string.IsNullOrEmpty(tempPath))
{
LogServer.AddLogText(string.Format("配置文件路径:{0}", tempPath), postback.BaseId);
return(false);
}
string timePath = DateTime.Now.ToString("yyyyMM");
filePath = tempPath + "\\" + timePath;
try
{
string sql = string.Empty;
string number = postback.BaseId;//PDA传过来的BaseId就是运单号
//Customer=0为自运单，否则为第三方运单
if (customerId != 0)
{
//第三方运单需要查询关系表获取转换后的运单号
sql = "select number from huadong_tmsorder_waybillbase where relationid='" + number + "' and customerId=" + customerId;
number = _SqlHelp.ExecuteScalar <string>(sql);
LogServer.AddLogText(string.Format("查询关联表，huadong_tmsorder_waybillbase:{0},结果:{1}", sql, number), postback.BaseId);
if (string.IsNullOrEmpty(number))
{
string remark = "运单[" + (string.IsNullOrEmpty(number) ? postback.BaseId : number) + "]不存在";
remark += "传入的CustomerId=" + customerId;
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
StringBuilder pic = new StringBuilder("insert into temporarypictures(baseId,operateAt,PicName,remarks,inserttime) values ");
for (int i = 0; i < picList.Count; i++)
{
string fileName = postback.BaseId + "_" + System.IO.Path.GetFileName(postback.PicName.Split('|')[i]);
MyTool.SaveImage(MyTool.GetGzipPicBytes(picList[i] as byte[]), filePath + "\\" + fileName);
pic.AppendFormat("('{0}', '{1}', '{2}', '{3}', '{4}'),", postback.BaseId, postbackTime.ToString("yyyy-MM-dd HH:mm:ss"), timePath + "/" + fileName, remark, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
//picIndex++;
}
pic.Length -= 1;
List <string> sqlpicList = new List <string>();
sqlpicList.Add(pic.ToString());
bool pictures = _SqlHelp.ExecuteTranstration(sqlpicList);
LogServer.AddLogText(string.Format("sql:{0},结果:{1}", sql, pictures), postback.BaseId);
return(true);
}
}
Model_Waybill_Base waybillBase = null;//根据运单号获取到BaseId
sql = "select * from waybill_base where number = '" + number + "' order by id desc limit 1";
waybillBase = _SqlHelp.ExecuteObject <Model_Waybill_Base>(sql);
if (waybillBase == null)
{
string remark = "运单[" + (string.IsNullOrEmpty(number) ? postback.BaseId : number) + "]不存在";
remark += "传入的CustomerId=" + customerId;
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
StringBuilder pic = new StringBuilder("insert into temporarypictures(baseId,operateAt,PicName,remarks,inserttime) values ");
for (int i = 0; i < picList.Count; i++)
{
string fileName = postback.BaseId + "_" + System.IO.Path.GetFileName(postback.PicName.Split('|')[i]);
MyTool.SaveImage(MyTool.GetGzipPicBytes(picList[i] as byte[]), filePath + "\\" + fileName);
pic.AppendFormat("('{0}', '{1}', '{2}', '{3}', '{4}'),", postback.BaseId, postbackTime.ToString("yyyy-MM-dd HH:mm:ss"), timePath + "/" + fileName, remark, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
//picIndex++;
}
pic.Length -= 1;
List <string> sqlpicList = new List <string>();
sqlpicList.Add(pic.ToString());
bool pictures = _SqlHelp.ExecuteTranstration(sqlpicList);
LogServer.AddLogText(string.Format("查询运单，waybill_base:{0},结果:{1}", sql, waybillBase == null ? "未查询到信息" : waybillBase.Number), postback.BaseId);
return(true);//运单还未上传，无法上传签收图片
}
//List<Model_Waybill_Base> numberList = GetExistWaybills(new List<string> { postback.BaseId }).Distinct().ToList();
//if (numberList.Count == 0)
// return true;//运单还未上传，无法上传签收图片
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
StringBuilder sb = new StringBuilder("insert into waybill_postback_pic (baseId,picName) values ");
for (int i = 0; i < picList.Count; i++)
{
string fileName = postback.BaseId + "_" + System.IO.Path.GetFileName(postback.PicName.Split('|')[i]);
MyTool.SaveImage(MyTool.GetGzipPicBytes(picList[i] as byte[]), filePath + "\\" + fileName);
sb.AppendFormat("('{0}','{1}'),", waybillBase.Id, timePath + "/" + fileName);
picIndex++;
}
sb.Length -= 1;
List <string> sqlList = new List <string>();
sqlList.Add(sb.ToString());
sqlList.Add(string.Format("update waybill_base set picPostbackAt='{0}' where number='{1}'", postbackTime.ToString("yyyy-MM-dd HH:mm:ss"), waybillBase.Number));
result = _SqlHelp.ExecuteTranstration(sqlList);
foreach (string item in sqlList)
{
LogServer.AddLogText(string.Format("sql:{0}", item), postback.BaseId);
}
LogServer.AddLogText(string.Format("保存结果：{0}", result), postback.BaseId);
}
catch (Exception ex)
{
LogServer.AddLogText("保存图片失败:" + ex.Message, postback.BaseId);
throw ex;
}
finally
{
if (result == false && picIndex > 0)
{
//失败时删除图片
for (int i = picIndex; i > 0; i--)
{
string fileName = postback.BaseId + "_" + System.IO.Path.GetFileName(postback.PicName.Split('|')[i - 1]);
System.IO.File.Delete(filePath + "\\" + fileName);
LogServer.AddLogText(string.Format("失败时删除图片，路径:{0}", filePath + "\\" + fileName), postback.BaseId);
}
}
}
return(result);
}