/// <summary>
/// 获取指定节点的关联的冷藏载体信息
/// </summary>
/// <param name="nodeId">指定节点ID</param>
/// <returns></returns>
private static void GetStorageByNodeId(int nodeId, ref DETAIL d)
{
try
{
string sql = "select n.*,s.StorageType,s.Driver,s.DriverTel from waybill_Node n left join coldStorage s on n.storageId=s.id where n.id='" + nodeId + "'";
string storageName = string.Empty;
int storageType = 0;
using (MySqlDataReader reader = DbHelperMySQL.ExecuteReader(sql))
{
if (reader.Read())
{
storageName = reader["storageName"].ToString();
storageType = Convert.ToInt32(reader["storageType"]);
}
}
if (storageType == 1)
{
d.WAREHOUSECODE = storageName;
}
else if (storageType == 2)
{
d.LICENSENO = storageName;
}
}
catch (Exception ex)
{
throw new Exception("获取节点时间失败:" + ex.Message);
}
}
/// <summary>
/// 获取指定NodeId的下一个节点信息
/// </summary>
/// <param name="nodeId">节点ID</param>
/// <param name="number">内部订单号</param>
/// <returns></returns>
public static NodeInfo GetNextUploadNodeInfo(int?nodeId, string number)
{
NodeInfo n = null;
int baseId = 0;
string sql = string.Format("select Id from waybill_base where number = '{0}' order by id desc limit 1", number);
try
{
baseId = Convert.ToInt32(DbHelperMySQL.GetSingle(sql));
}
catch (Exception e)
{
throw new Exception("获取内部订单Id失败：" + e.Message);
}
try
{
int realNodeId = nodeId == null ? -1 : (int)nodeId;
string where = ">(SELECT operateat from waybill_node where id='" + realNodeId + "')";
if (realNodeId == -1)
{
where = string.Format("=(select beginAt from waybill_base where number = '{0}' order by id desc limit 1)", number);
}
sql = string.Format("select n.*,s.StorageType,s.Driver,s.DriverTel from waybill_Node n left join coldStorage s on n.storageId=s.id where n.baseid='{0}' and n.operateat{1} order by arrived ,operateat limit 1", baseId, where);
using (MySqlDataReader reader = DbHelperMySQL.ExecuteReader(sql))
{
if (reader.Read())
{
n = new NodeInfo();
n.Id = Convert.ToInt32(reader["Id"]);
n.BaseId = Convert.ToInt32(reader["BaseId"]);
n.OperateAt = Convert.ToDateTime(reader["OperateAt"]).ToString("yyyy-MM-dd HH:mm:ss"); //reader["OperateAt"].ToString();
n.StorageId = Convert.ToInt32(reader["StorageId"]);
n.StorageName = reader["StorageName"].ToString();
n.Content = reader["Content"].ToString();
n.Arrived = Convert.ToInt32(reader["Arrived"]);
n.StorageType = Convert.ToInt32(reader["StorageType"]);
n.Driver = reader["Driver"].ToString();
n.DriverTel = reader["DriverTel"].ToString();
n.CustomerId = Convert.ToInt32(reader["StorageType"]);
}
}
}
catch (Exception ex)
{
throw new Exception("获取节点信息失败：" + ex.Message);
}
return(n);
}
public static RelationModel GetNextWaitUploadDataOrderRelation(List <string> senderCodeList)//, ref List<long> _ignoreTempRelationList)
{
RelationModel result = null;
string sql = "select * from huadong_tmsorder_waybillbase where (currentUploadDataNodeId<>0 or currentUploadDataNodeId is null) and relationId in (select relationId from huadong_tms_order where 【where】 and relationId is not null) 【ignore】 limit 1";
//if (_ignoreTempRelationList.Count > 0)
// sql = sql.Replace("【ignore】", string.Format("and id not in({0})", string.Join(",", _ignoreTempRelationList)));
//else
// sql = sql.Replace("【ignore】", "");
//排除集合改为使用权重列和id排序
sql = sql.Replace("【ignore】", " order by handleTHCount,id desc ");
string where = "SecretKey ='" + Utility._SecretKey + "'";
if (senderCodeList != null)//如果不为空，则表示查询运管平台同步过来的订单
{
where = string.Format("SecretKey in ('{0}')", string.Join("','", senderCodeList));
}
sql = sql.Replace("【where】", where);
try
{
using (MySqlDataReader reader = DbHelperMySQL.ExecuteReader(sql))
{
if (reader.Read())
{
result = new RelationModel();
result.Id = Convert.ToInt32(reader["Id"]);
result.RelationId = reader["RelationId"].ToString();
result.Number = reader["Number"].ToString();
result.CurrentUploadNodeId = reader["CurrentUploadNodeId"] is DBNull ? -1 : Convert.ToInt32(reader["CurrentUploadNodeId"]);
result.CurrentUploadDataNodeId = reader["CurrentUploadDataNodeId"] is DBNull ? -1 : Convert.ToInt32(reader["CurrentUploadDataNodeId"]);
result.CurrentUploadDataTime = reader["CurrentUploadDataTime"] is DBNull ? string.Empty : Convert.ToDateTime(reader["CurrentUploadDataTime"]).ToString("yyyy-MM-dd HH:mm:ss");
result.CustomerId = reader["CustomerId"] is DBNull ? 0 : Convert.ToInt32(reader["CustomerId"]);
}
}
}
catch (Exception ex)
{
throw new Exception("获取运单关系失败：" + ex.Message);
}
//int count = _ignoreTempRelationList.Count;
//if (result == null && _ignoreTempRelationList.Count >= GetRelationCount(senderCodeList))
//{
// _ignoreTempRelationList.Clear();
// throw new Exception("已检测完所有上报信息,共[" + count + "]条,将重新开始");
//}
return(result);
}
/// <summary>
/// 获取一条冷链数据上报进度
/// </summary>
/// <param name="senderCodeList"></param>
/// <returns></returns>
private static UploadDataProgress GetNextWaitUploadDataProgress(List <string> senderCodeList)
{
UploadDataProgress progress = null;
string sql = "select * from (select p.*,o.ShipmentCode from uploadDataProgress p left join huadong_tms_order o on p.relationId = o.SHIPDETAILID or p.relationId=o.LEGCODE where p.handleFlag = -1 and endnodeTime is not null and 【SecretKey】 order by p.nodetime limit 1)a left JOIN coldStorage b on a.StorageId = b.id";
string secretkeySql = "SecretKey ='" + Utility._SecretKey + "' and SHIPMENTCODE<>''";
if (senderCodeList != null)
{
secretkeySql = string.Format("SecretKey in ('{0}')", string.Join("','", senderCodeList));
}
sql = sql.Replace("【SecretKey】", secretkeySql);
using (MySqlDataReader reader = DbHelperMySQL.ExecuteReader(sql))
{
if (reader.Read())
{
progress = new UploadDataProgress();
progress.Id = Convert.ToInt32(reader["Id"]);
progress.relationId = reader["relationId"].ToString();
progress.storageId = Convert.ToInt32(reader["storageId"]);
progress.storageName = reader["storageName"].ToString();
progress.nodeTime = Convert.ToDateTime(reader["nodeTime"]);
progress.endNodeTime = Convert.ToDateTime(reader["endNodeTime"]);
progress.uploadProgress = Convert.ToDateTime(reader["uploadProgress"]);
progress.shipmentCode = reader["shipmentCode"].ToString();
progress.storageType = Convert.ToInt32(reader["storageType"]);
}
}
if (progress == null)
{
int count = 0;
try
{
//本轮检测完毕，重置标记，开始下一轮检测
sql = "update uploadDataProgress p inner join (select relationId from huadong_tms_order where 【SecretKey】) as o set handleflag = -1 where handleflag=0 and p.relationId in(o.relationId)";
sql = sql.Replace("【SecretKey】", secretkeySql);
count = DbHelperMySQL.ExecuteSql(sql);
Console.WriteLine("共" + count + "条处理进度已处理完一轮,已重置进度标记成功.");
}
catch (Exception ex)
{
Console.WriteLine("共" + count + "条处理进度已处理完一轮,重置进度标记时失败：" + ex.Message);
}
}
return(progress);
}
/// <summary>
/// 获取带上报的节点 运管平台固定拿一条，大华东拿配置的条数
/// </summary>
/// <param name="senderCodeList"></param>
/// <returns></returns>
private static NodeInfo GetWaitUploadNode(List <string> senderCodeList)
{
NodeInfo n = null;
string sql = "select * from ( select n.*,o.ShipmentCode from waybill_node n left join huadong_tms_order o on n.scanNumber = o.SHIPDETAILID or n.scanNumber=o.LEGCODE where n.handleFlag = 0 and n.scanNumber is not null and 【SecretKey】 order by n.id desc limit 1) a left JOIN coldStorage b on a.StorageId = b.id";
if (senderCodeList == null)
{
sql = sql.Replace("【SecretKey】", "SecretKey ='" + Utility._SecretKey + "' and SHIPMENTCODE<>''");
}
else
{
sql = sql.Replace("【SecretKey】", string.Format("SecretKey in ('{0}')", string.Join("','", senderCodeList)));
}
using (MySqlDataReader reader = DbHelperMySQL.ExecuteReader(sql))
{
if (reader.Read())
{
n = new NodeInfo();
n.Id = Convert.ToInt32(reader["Id"]);
n.BaseId = Convert.ToInt32(reader["BaseId"]);
n.OperateAt = Convert.ToDateTime(reader["OperateAt"]).ToString("yyyy-MM-dd HH:mm:ss"); //reader["OperateAt"].ToString();
n.StorageId = Convert.ToInt32(reader["StorageId"]);
n.StorageName = reader["StorageName"].ToString().Replace("[默认]", string.Empty);
n.Content = reader["Content"].ToString();
n.Arrived = Convert.ToInt32(reader["Arrived"]);
n.StorageType = Convert.ToInt32(reader["StorageType"]);
n.Driver = reader["Driver"].ToString();
n.DriverTel = reader["DriverTel"].ToString();
n.ScanNumber = reader["ScanNumber"].ToString();
n.ParentStorageId = Convert.ToInt32(reader["ParentStorageId"]);
n.ShipmentCode = reader["ShipmentCode"].ToString();
n.CustomerId = Convert.ToInt32(reader["CustomerId"]);
}
}
return(n);
}
/// <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() + "]");
}
}