private static int GetRelationCount(List <string> senderCodeList)
{
int result = 0;
string sql = "select count(*) from huadong_tmsorder_waybillbase where (currentUploadNodeId is null or currentUploadNodeId=-1 or currentUploadNodeId<>0) and relationId in (select relationId from huadong_tms_order where 【where】 and relationId is not null)";
string where = "SecretKey ='" + Utility._SecretKey + "'";
if (senderCodeList != null)//如果不为空，则表示查询运管平台同步过来的订单
{
where = string.Format("SecretKey in ('{0}')", string.Join("','", senderCodeList));
}
sql = sql.Replace("【where】", where);
try
{
result = Convert.ToInt32(DbHelperMySQL.GetSingle(sql));
}
catch
{
}
return(result);
}
/// <summary>
/// 根据关系ID获取TMS运单的派车单号
/// </summary>
/// <param name="relationId">关系ID</param>
/// <returns></returns>
public static string GetShipmentCodeByTMSOrder(string relationId)
{
string result = string.Empty;
string sql = string.Format("select ShipmentCode from huadong_tms_order where relationId = '{0}' limit 1", relationId);
Utility.AddLogText(sql);
try
{
object obj = DbHelperMySQL.GetSingle(sql);
if (obj != null)
{
result = obj.ToString();
}
Utility.AddLogText("根据关系ID获取TMS运单的派车单号:" + result);
}
catch (Exception ex)
{
Utility.AddLogText("获取TMS派车单号失败：" + ex.Message);
throw new Exception("获取TMS派车单号失败：" + ex.Message);
}
return(result);
}
public static int GetCustomerIdBySenderCode(string senderCode, int linkType)
{
int customerId = 0;
try
{
string sql = "select customerId from customer_transport where interchangeCode = " + senderCode + "";
if (linkType != 2)
{
sql = "select customerId from customer_transport where linkType=" + linkType;
}
object obj = DbHelperMySQL.GetSingle(sql);
if (obj != null)
{
int.TryParse(obj.ToString(), out customerId);
}
}
catch (Exception ex)
{
throw new Exception("查询[SenderCode=" + senderCode + "]的上游客户ID出错:" + ex.Message);
}
return(customerId);
}
/// <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() + "]");
}
}
