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