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; } }