internal IList <IntransitShipmentSkuSummary> GetInboundShipmentSkuDetail(ShipmentSkuFilters statusFilter, DateTime?minCloseDate, DateTime?maxCloseDate, string sewingPlant, int nRowsToShow) { // If both min and max are specified and min > max, then swap the dates if (minCloseDate.HasValue && maxCloseDate.HasValue && minCloseDate > maxCloseDate) { var temp = minCloseDate; minCloseDate = maxCloseDate; maxCloseDate = temp; } return(_repos.GetInboundShipmentSkuDetail(statusFilter, minCloseDate == null ? (DateTime?)null : minCloseDate.Value.Date, maxCloseDate == null ? (DateTime?)null : maxCloseDate.Value.Date.AddDays(1).AddSeconds(-1), sewingPlant, nRowsToShow)); }
private IList <ShipmentSkuGroup> GetInboundShipmentDetailModels(ShipmentSkuFilterModel filters, int maxRows) { ShipmentSkuFilters statusFilter = ShipmentSkuFilters.NoFilter; if (filters.SkuStatus == ShipmentSkuStatusType.Varience) { statusFilter |= ShipmentSkuFilters.VarianceSku; } switch (filters.SkuSource) { case null: break; case ShipmentSkuSourceType.Vendor: statusFilter |= ShipmentSkuFilters.VendorShipments; break; case ShipmentSkuSourceType.Transfer: statusFilter |= ShipmentSkuFilters.BuildingTransferShipments; break; default: throw new NotImplementedException(); } var shipmentList = _service.GetInboundShipmentSkuDetail(statusFilter, filters.MaxClosedDate, filters.MinClosedDate, filters.SewingPlantCode, maxRows); var list = from shipment in shipmentList group shipment by new { InstransitType = string.IsNullOrWhiteSpace(shipment.IntransitType) || shipment.IntransitType == "IT" ? "" : shipment.IntransitType == "ZEL" || shipment.IntransitType == "TR" ? "ZEL" : "Unknown" } into g select new ShipmentSkuGroup { InstransitType = g.Key.InstransitType, Shipments = (from p in g orderby p.UploadDate descending, p.ShipmentId, p.Style, p.Color, p.Dimension, p.SkuSize select new ShipmentDetailSkuModel(p)).ToList() }; return(list.ToList()); }
/// <summary> /// Brings list of shipments per SKU. /// </summary> /// <returns></returns> public IList <IntransitShipmentSkuSummary> GetInboundShipmentSkuDetail(ShipmentSkuFilters statusFilter, DateTime?minCloseDate, DateTime?maxCloseDate, string sewingPlant, int nRowsToShow) { Contract.Assert(_db != null); const string QUERY = @" WITH CLOSED_SHIPMENTS AS (SELECT S.SHIPMENT_ID, S.STYLE, S.COLOR, S.DIMENSION, S.SKU_SIZE, S.VWH_ID AS VWH_ID, max(s.is_shipment_closed) AS IS_SHIPMENT_CLOSED, MAX(s.sewing_plant_code) AS sewing_plant_code, MAX(TS.SEWING_PLANT_NAME) AS SEWING_PLANT_NAME, MAX(S.UPLOAD_DATE) AS MAX_UPLOAD_DATE, MIN(S.UPLOAD_DATE) AS MIN_UPLOAD_DATE, MIN(CASE WHEN S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID AND S.RECEIVED_DATE IS NOT NULL THEN S.ORIGINAL_SHIPMENT_ID END) AS MIN_BUDDY_SHIPMENT_ID, MAX(CASE WHEN S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID AND S.RECEIVED_DATE IS NOT NULL THEN S.ORIGINAL_SHIPMENT_ID END) AS MAX_BUDDY_SHIPMENT_ID, COUNT(UNIQUE CASE WHEN S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID AND S.RECEIVED_DATE IS NOT NULL THEN S.ORIGINAL_SHIPMENT_ID END) AS COUNT_BUDDY_SHIPMENTS, SUM(CASE WHEN S.RECEIVED_DATE IS NOT NULL AND S.ORIGINAL_SHIPMENT_ID = S.SHIPMENT_ID THEN S.QUANTITY END) AS RECEIVED_QUANTITY_MINE, COUNT(CASE WHEN S.RECEIVED_DATE IS NOT NULL AND S.ORIGINAL_SHIPMENT_ID = S.SHIPMENT_ID THEN S.CARTON_ID END) AS RECEIVED_CTN_MINE, SUM(CASE WHEN S.RECEIVED_DATE IS NOT NULL AND S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID THEN S.QUANTITY END) AS RECEIVED_QTY_OF_BUDDIES, COUNT(CASE WHEN S.RECEIVED_DATE IS NOT NULL AND S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID THEN S.CARTON_ID END) AS RECEIVED_CTN_OF_BUDDIES, MAX(S.SHIPMENT_DATE) AS SHIPMENT_DATE FROM <proxy />SRC_CARTON_INTRANSIT S LEFT OUTER JOIN <proxy />TAB_SEWINGPLANT TS ON TS.SEWING_PLANT_CODE = S.SEWING_PLANT_CODE WHERE S.ORIGINAL_SHIPMENT_ID IS NOT NULL GROUP BY S.SHIPMENT_ID, S.STYLE, S.COLOR, S.DIMENSION, S.SKU_SIZE, S.VWH_ID), EXPECTED_SHIPMENTS AS (SELECT S.ORIGINAL_SHIPMENT_ID AS SHIPMENT_ID, S.STYLE, S.COLOR, S.DIMENSION, S.SKU_SIZE, S.VWH_ID AS VWH_ID, max(S.Is_Shipment_Closed) AS Is_Shipment_Closed, MAX(S.INTRANSIT_TYPE) AS INTRANSIT_TYPE, MIN(CASE WHEN S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID AND S.RECEIVED_DATE IS NOT NULL THEN S.SHIPMENT_ID END) AS MIN_OTHER_SHIPMENT_ID, MAX(CASE WHEN S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID AND S.RECEIVED_DATE IS NOT NULL THEN S.SHIPMENT_ID END) AS MAX_OTHER_SHIPMENT_ID, COUNT(UNIQUE CASE WHEN S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID AND S.RECEIVED_DATE IS NOT NULL THEN S.SHIPMENT_ID END) AS COUNT_OTHER_SHIPMENTS, SUM(S.QUANTITY) AS EXPECTED_QUANTITY, COUNT(S.CARTON_ID) AS EXPECTED_CTNS, COUNT(CASE WHEN S.RECEIVED_DATE IS NOT NULL AND S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID THEN S.CARTON_ID END) AS RECEIVED_CTN_BY_BUDDIES, SUM(CASE WHEN S.RECEIVED_DATE IS NOT NULL AND S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID THEN S.QUANTITY END) AS RECEIVED_QTY_BY_BUDDIES FROM <proxy /> SRC_CARTON_INTRANSIT S WHERE S.ORIGINAL_SHIPMENT_ID IS NOT NULL GROUP BY S.ORIGINAL_SHIPMENT_ID, S.STYLE, S.COLOR, S.DIMENSION, S.SKU_SIZE, S.VWH_ID) SELECT NVL(ASH.SHIPMENT_ID, ES.SHIPMENT_ID) AS SHIPMENT_ID, ES.MIN_OTHER_SHIPMENT_ID AS MIN_OTHER_SHIPMENT_ID, ES.MAX_OTHER_SHIPMENT_ID AS MAX_OTHER_SHIPMENT_ID, --COUNT(UNIQUE NVL(ASH.SHIPMENT_ID, ES.SHIPMENT_ID)) OVER() AS total_shipment_count, ES.COUNT_OTHER_SHIPMENTS, ASH.MIN_BUDDY_SHIPMENT_ID AS MIN_BUDDY_SHIPMENT_ID, ASH.MAX_BUDDY_SHIPMENT_ID AS MAX_BUDDY_SHIPMENT_ID, ASH.COUNT_BUDDY_SHIPMENTS, ash.sewing_plant_code, ash.SEWING_PLANT_NAME, NVL(ASH.STYLE, ES.STYLE) AS STYLE, NVL(ASH.COLOR, ES.COLOR) AS COLOR, NVL(ASH.DIMENSION, ES.DIMENSION) AS DIMENSION, NVL(ASH.SKU_SIZE, ES.SKU_SIZE) AS SKU_SIZE, NVL(ASH.VWH_ID, ES.VWH_ID) AS VWH_ID, ES.EXPECTED_QUANTITY AS EXPECTED_QUANTITY, ES.EXPECTED_CTNS AS EXPECTED_CTNS, ASH.RECEIVED_QUANTITY_MINE AS RECEIVED_QUANTITY_MINE, ASH.RECEIVED_CTN_MINE, ES.RECEIVED_CTN_BY_BUDDIES, ASH.RECEIVED_CTN_OF_BUDDIES, ES.RECEIVED_QTY_BY_BUDDIES, ASH.RECEIVED_QTY_OF_BUDDIES, ASH.SHIPMENT_DATE AS SHIPMENT_DATE, ASH.MAX_UPLOAD_DATE AS MAX_UPLOAD_DATE, ES.INTRANSIT_TYPE AS INTRANSIT_TYPE FROM CLOSED_SHIPMENTS ASH FULL OUTER JOIN EXPECTED_SHIPMENTS ES ON ES.SHIPMENT_ID = ASH.SHIPMENT_ID AND ES.STYLE = ASH.STYLE AND ES.COLOR = ASH.COLOR AND ES.DIMENSION = ASH.DIMENSION AND ES.SKU_SIZE = ASH.SKU_SIZE AND ES.VWH_ID = ASH.VWH_ID where (ash.IS_SHIPMENT_CLOSED IS NOT NULL) <if>AND ASH.MIN_UPLOAD_DATE >= :MIN_UPLOAD_DATE </if> <if>AND ASH.MIN_UPLOAD_DATE <= :MAX_UPLOAD_DATE</if> <if>AND ASH.SEWING_PLANT_CODE = :SEWING_PLANT_CODE</if> <if c='$transfer_filter'> and ES.intransit_type in ('TR','ZEL')</if> <if c='$vendor_filter'> and (ES.intransit_type is null or ES.intransit_type = 'IT')</if> <if c= '$variance_filter'> and (ash.count_buddy_shipments > 0 or count_other_shipments > 0 or (es.EXPECTED_CTNS - ash.RECEIVED_CTN_MINE > 0 )) </if> order by ASH.max_upload_date desc nulls last"; var binder = SqlBinder.Create(row => new IntransitShipmentSkuSummary { ShipmentId = row.GetString("SHIPMENT_ID"), MinOtherShipmentId = row.GetString("min_other_shipment_id"), MaxOtherShipmentId = row.GetString("max_other_shipment_id"), // TotalShipmentCount = row.GetInteger("total_shipment_count"), CountOtherShipments = row.GetInteger("count_other_shipments"), MinBuddyShipmentId = row.GetString("min_buddy_shipment_id"), MaxBuddyShipmentId = row.GetString("max_buddy_shipment_id"), CountBuddyShipments = row.GetInteger("count_buddy_shipments"), ShipmentDate = row.GetDate("SHIPMENT_DATE"), SewingPlantCode = row.GetString("sewing_plant_code"), SewingPlantName = row.GetString("sewing_plant_name"), UploadDate = row.GetDate("MAX_UPLOAD_DATE"), Style = row.GetString("STYLE"), Color = row.GetString("COLOR"), Dimension = row.GetString("DIMENSION"), SkuSize = row.GetString("SKU_SIZE"), VwhId = row.GetString("VWH_ID"), ExpectedPieces = row.GetInteger("EXPECTED_QUANTITY"), ExpectedCartonCount = row.GetInteger("EXPECTED_CTNS"), ReceivedPiecesMine = row.GetInteger("RECEIVED_QUANTITY_MINE"), ReceivedCartonsMine = row.GetInteger("RECEIVED_CTN_MINE"), IntransitType = row.GetString("INTRANSIT_TYPE"), ReceivedCtnByBuddies = row.GetInteger("RECEIVED_CTN_BY_BUDDIES"), ReceivedCtnOfBuddies = row.GetInteger("RECEIVED_CTN_OF_BUDDIES"), ReceivedPiecesByBuddies = row.GetInteger("RECEIVED_QTY_BY_BUDDIES"), ReceivedPiecesOfBuddies = row.GetInteger("RECEIVED_QTY_OF_BUDDIES") }) .Parameter("MIN_UPLOAD_DATE", minCloseDate) .Parameter("MAX_UPLOAD_DATE", maxCloseDate) .Parameter("SEWING_PLANT_CODE", sewingPlant); binder.ParameterXPath("transfer_filter", statusFilter.HasFlag(ShipmentSkuFilters.BuildingTransferShipments)); binder.ParameterXPath("vendor_filter", statusFilter.HasFlag(ShipmentSkuFilters.VendorShipments)); binder.ParameterXPath("variance_filter", statusFilter.HasFlag(ShipmentSkuFilters.VarianceSku)); return(_db.ExecuteReader(QUERY, binder, nRowsToShow)); }