/// <summary>
/// 查询运单信息是否存在第三方信息
/// </summary>
/// <param name="huadong"></param>
/// <returns></returns>
public static List <Model_Waybill_Base> QueryWaybill(Model_Huadong_Tms_Order huadong)
{
List <Model_Waybill_Base> list = null;
//Model_Huadong_Tmsorder_Waybillbase huadongtmsorder = new Model_Huadong_Tmsorder_Waybillbase();
string distinctSql = string.Format("select * from huadong_tmsorder_waybillbase where relationId in('{0}')", huadong.RelationId);
Model_Huadong_Tmsorder_Waybillbase huadongtmsorder = _SqlHelp.ExecuteObject <Model_Huadong_Tmsorder_Waybillbase>(distinctSql);
if (huadongtmsorder != null)
{
string sql = "select id,number,senderId,senderOrg,senderPerson,senderTel,senderAddress,receiverId,receiverOrg," +
"receiverPerson,receiverTel,receiverAddress,billingCount,stage,beginAt,signinAt,picPostbackAt from waybill_base " +
" where number = ?number";
MySqlParameter[] para = new MySqlParameter[1];
para[0] = new MySqlParameter("number", huadongtmsorder.number);
list = _SqlHelp.ExecuteObjects <Model_Waybill_Base>(sql, para);
}
return(list);
}
/// <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="nodeList"></param>
/// <returns></returns>
public static bool GethuadongTmsOrderNode(List <Model_Waybill_Node> nodeList)
{
if (nodeList.Count == 0)
{
return(false);
}
bool result = false;
try
{
//查询关联表是否存在信息
List <Model_Huadong_Tmsorder_Waybillbase> numberList = GetHuadongWaybillbase(nodeList.Select(l => l.BaseId).Distinct().ToList());
if (numberList.Count == 0)
{
try
{
//关联表不存在的信息保存在暂存表中
string remarks = "运单不存在";
string sql = string.Format("insert into unnecessary_node(baseId,operateAt,storageId,storageName,content,arrived,remarks,inserttime) values('{0}','{1}',{2},'{3}','{4}',{5},'{6}','{7}') ;",
nodeList[0].BaseId, nodeList[0].operateAt, nodeList[0].StorageId, nodeList[0].StorageName, nodeList[0].Content, (int)nodeList[0].Arrived, remarks, DateTime.Now);
_SqlHelp.ExecuteNonQuery(sql);
}
catch (Exception)
{
}
return(true);
}
int receiveContinue = 0;
//如果有多条数据，取第一条
Model_Huadong_Tmsorder_Waybillbase item = numberList[0];
List <Model_Waybill_Base> baseId = GetExistWaybills(item.number);
if (baseId.Count == 0)
{
return(true);
}
foreach (Model_Waybill_Base waybase in baseId)
{
//判断运单状态是否签收
if (waybase.Stage == Enum_WaybillStage.Received)
{
receiveContinue++;
continue;
}
foreach (Model_Waybill_Node node in nodeList)
{
if (GetOperateAt(waybase.Id, node.operateAt.ToString("yyyy-MM-dd HH:mm:ss")) > 0)
{
receiveContinue++;
continue;
}
int no = AddNode(node, waybase);
if (no > 0)
{
result = true;
}
else
{
result = false;
}
}
}
if (receiveContinue == nodeList.Count)
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
