public IEnumerable <PodAll> GetNikeExportPodAllByCondition(string Condition)
{
DbParam[] dbParams =
{
new DbParam("@Where", DbType.String, Condition, ParameterDirection.Input)
};
DataSet NikeExport = base.ExecuteDataSet("Proc_Nike_GetPodAllByCondition", dbParams);
IList <PodAll> podAllList = new List <PodAll>();
if (NikeExport != null && NikeExport.Tables[0] != null && NikeExport.Tables[0].Rows.Count > 0)
{
IEnumerable <Pod> podCollection = NikeExport.Tables[0].ConvertToEntityCollection <Pod>();
IEnumerable <PodTrack> podTrackCollection = Enumerable.Empty <PodTrack>();
if (NikeExport.Tables[1] != null && NikeExport.Tables[1].Rows.Count > 0)
{
podTrackCollection = NikeExport.Tables[1].ConvertToEntityCollection <PodTrack>();
}
podCollection.Each((i, p) =>
{
PodAll podAll = new PodAll();
podAll.Pod = p;
podAll.PodTracks = podTrackCollection.Where(t => t.PodID == p.ID);
podAllList.Add(podAll);
});
}
return(podAllList);
}
public IEnumerable <PodAll> GetPodAndPodReplyDocumentByCondition(IEnumerable <string> CustomerOrderNumbers, long?ShipperID, DateTime?StartActualArrivalDate, DateTime?EndActualArrivalDate, int MinPodState)
{
IList <PodAll> returnPodAlls = new List <PodAll>();
StringBuilder sb = new StringBuilder();
sb.Append("WITH Temp AS(SELECT a.ID AS PodID, a.SystemNumber,a.CustomerOrderNumber,a.ActualDeliveryDate,a.StartCityName, a.EndCityName,a.DateTime2,b.ID AS ReplyDocumentID, b.Replier,b.ReplyTime,b.Remark,b.AttachmentGroupID,b.Str1,b.Str2,b.DateTime1,b.Str3,c.DateTime1 AS TrackDate,c.Str1 AS Location, c.Str2 AS TrackState,c.Str3 AS CausesOFDelays,c.CreateTime AS TrackCreateTime,c.ID AS TrackID,c.Str4 as CsusesOfDelaysType FROM dbo.POD a LEFT JOIN dbo.PodReplyDocument b ON a.ID = b.PodID LEFT JOIN dbo.PodTrack c ON a.ID=c.PodID")
.Append(" Where a.ProjectID=1 and a.CustomerID=2 ").Append(" and a.PodStateID between 2 and 7 ");
//sb.Append("SELECT a.ID AS PodID, a.SystemNumber,a.CustomerOrderNumber,a.ActualDeliveryDate,a.StartCityName, a.EndCityName,a.DateTime2,b.ID AS ReplyDocumentID, b.Replier,b.ReplyTime,b.Remark,b.AttachmentGroupID,b.Str1,b.Str2,b.DateTime1,b.Str3 FROM dbo.POD a LEFT JOIN dbo.PodReplyDocument b ON a.ID = b.PodID")
// .Append(" Where a.ProjectID=1 and a.CustomerID=2 ").Append(" and a.PodStateID between 2 and 5 ");
if (CustomerOrderNumbers != null && CustomerOrderNumbers.Any())
{
sb.Append(" and (a.CustomerOrderNumber in (");
CustomerOrderNumbers.Each((i, p) =>
{
sb.Append("'").Append(p).Append("'").Append(",");
});
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
sb.Append(" or a.Str1 in (");
CustomerOrderNumbers.Each((i, p) =>
{
sb.Append("'").Append(p).Append("'").Append(",");
});
sb.Remove(sb.Length - 1, 1);
sb.Append(") )");
}
if (ShipperID.HasValue)
{
sb.Append(" and a.ShipperID=").Append(ShipperID.Value).Append(" ");
}
sb.Append(" and a.PodStateID > ").Append(MinPodState);
if (StartActualArrivalDate.HasValue)
{
sb.Append(" and a.ActualDeliveryDate >= '").Append(StartActualArrivalDate.Value.ToString("yyyy-MM-dd")).Append(" 00:00' ");
}
if (EndActualArrivalDate.HasValue)
{
sb.Append(" and a.ActualDeliveryDate <= '").Append(EndActualArrivalDate.Value.ToString("yyyy-MM-dd")).Append(" 23:59' ");
}
sb.Append(" ) SELECT * FROM Temp,(SELECT PodID,MAX(TrackCreateTime) AS timer1 FROM Temp GROUP BY PodID) AS tr WHERE Temp.PodID = tr.PodID AND (Temp.TrackCreateTime = tr.timer1 OR Temp.TrackCreateTime IS NULL) ORDER BY Temp.ActualDeliveryDate,Temp.CustomerOrderNumber");
DataTable dt;
try
{
dt = base.ExecuteDataTableBySqlString(sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
dt.Rows.Each((i, r) =>
{
PodAll podAll = new PodAll();
podAll.Pod = new Pod();
podAll.Pod.ID = r[0].ObjectToInt64();
podAll.Pod.SystemNumber = r[1].ToString();
podAll.Pod.CustomerOrderNumber = r[2].ToString();
podAll.Pod.ActualDeliveryDate = r[3].ObjectToNullableDateTime();
podAll.Pod.StartCityName = r[4].ToString();
podAll.Pod.EndCityName = r[5].ToString();
podAll.Pod.DateTime2 = r[6].ObjectToNullableDateTime();
if (r[7].ToString() != "")
{
podAll.PodReplyDocument = new PodReplyDocument();
podAll.PodReplyDocument.ID = r[7].ObjectToInt64();
podAll.PodReplyDocument.Replier = r[8].ToString();
podAll.PodReplyDocument.ReplyTime = r[9].ObjectToNullableDateTime();
podAll.PodReplyDocument.Remark = r[10].ToString();
podAll.PodReplyDocument.AttachmentGroupID = r[11].ToString();
podAll.PodReplyDocument.Str1 = r[12].ToString();
podAll.PodReplyDocument.Str2 = r[13].ToString();
podAll.PodReplyDocument.DateTime1 = r[14].ObjectToNullableDateTime();
podAll.PodReplyDocument.Str3 = r[15].ToString();
}
if (r[16].ToString() != "")
{
PodTrack podTrack = new PodTrack();
podTrack.DateTime1 = r[16].ObjectToNullableDateTime();
podTrack.Str1 = r[17].ToString();
podTrack.Str2 = r[18].ToString();
podTrack.Str3 = r[19].ToString();
podTrack.Str4 = r["CsusesOfDelaysType"].ToString();
podAll.PodTracks = new PodTrack[] { podTrack };
}
returnPodAlls.Add(podAll);
});
}
return(returnPodAlls);
}
catch
{
throw;
}
}
