private IList <IntransitShipmentModel> GetShipmentModels(ShipmentListFilterModel filters, int maxRows)
{
ShipmentFilters statusFilter = ShipmentFilters.NoFilter;
if (filters.Status == ShipmentStatusType.Open)
{
// Showing open shipments.
statusFilter |= ShipmentFilters.OpenShipments;
filters.MinClosedDate = null;
filters.MaxClosedDate = null;
}
else
{
// Showing close shipments.
statusFilter |= ShipmentFilters.ClosedShipments;
}
if (filters.VariancesOnly)
{
statusFilter |= ShipmentFilters.VarianceOnlyShipments;
}
switch (filters.Source)
{
case null:
break;
case ShipmentSourceType.Vendor:
statusFilter |= ShipmentFilters.VendorShipments;
break;
case ShipmentSourceType.Transfer:
statusFilter |= ShipmentFilters.BuildingTransferShipments;
break;
default:
throw new NotImplementedException();
}
// int nMaxRowsToShow = int.MaxValue;
var shipmentList = _service.GetInboundShipmentSummary(filters.MinClosedDate, filters.MaxClosedDate, statusFilter, filters.SewingPlantCode, maxRows);
var list = from row in shipmentList
orderby(row.IsShipmentClosed?row.MaxUploadDate : row.ShipmentDate) descending
select new IntransitShipmentModel(row);
return(list.ToList());
}
/// <summary>
/// Shipments list
/// </summary>
/// <param name="maxCloseDate"></param>
/// <param name="minCloseDate"></param>
/// <param name="statusFilter"></param>
/// <remarks>
/// If both date minCloseDate and maxCloseDate are same,than we shows shipments of minCloseDate.
/// </remarks>
/// <returns></returns>
public IList <IntransitShipment> GetInboundShipmentSummary(DateTime?minCloseDate, DateTime?maxCloseDate, ShipmentFilters statusFilter, 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.GetInboundShipmentSummary(minCloseDate == null ? (DateTime?)null : minCloseDate.Value.Date,
maxCloseDate == null ? (DateTime?)null : maxCloseDate.Value.Date.AddDays(1).AddSeconds(-1),
statusFilter, sewingPlant, nRowsToShow));
}
/// <summary>
/// This function will provide all Shipments.
/// </summary>
/// <param name="maxCloseDate"></param>
/// <param name="minCloseDate"></param>
/// <param name="statusFilter">Pass the set of flags which control which filers will be applied</param>
/// <returns></returns>
public IList <IntransitShipment> GetInboundShipmentSummary(DateTime?minCloseDate, DateTime?maxCloseDate, ShipmentFilters statusFilter, string sewingPlant, int nRowsToShow)
{
Contract.Assert(_db != null);
var QUERY = @"
WITH EXPECTED_SHIPMENT AS
(SELECT S.ORIGINAL_SHIPMENT_ID AS SHIPMENT_ID,
MAX(S.IS_SHIPMENT_CLOSED) AS IS_SHIPMENT_CLOSED,
SUM(S.QUANTITY) AS EXPECTED_QUANTITY,
COUNT(S.CARTON_ID) AS EXPECTED_CTNS,
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,
COUNT(CASE
WHEN S.RECEIVED_DATE IS NOT NULL AND
S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID THEN
S.CARTON_ID
END) AS COUNT_OTHER_RECEIVED_CTN,
SUM(CASE
WHEN S.RECEIVED_DATE IS NOT NULL AND
S.ORIGINAL_SHIPMENT_ID != S.SHIPMENT_ID THEN
S.QUANTITY
END) AS COUNT_OTHER_RECEIVED_PIECES,
SUM(case
when s.received_date is null then
S.QUANTITY
end) AS NOT_RECEIVED_QTY,
COUNT(case
when s.received_date is null then
S.CARTON_ID
end) AS NOT_RECEIVED_CTNS,
MAX(S.SEWING_PLANT_CODE) AS SEWING_PLANT_CODE,
MAX(TS.SEWING_PLANT_NAME) AS SEWING_PLANT_NAME,
MAX(S.SHIPMENT_DATE) AS SHIPMENT_DATE,
MAX(S.INTRANSIT_TYPE) AS INTRANSIT_TYPE
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.ORIGINAL_SHIPMENT_ID),
ACTUAL_SHIPMENT AS
(SELECT S.SHIPMENT_ID,
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,
MAX(S.IS_SHIPMENT_CLOSED) AS IS_SHIPMENT_CLOSED,
SUM(case
when s.received_date is not null then
S.QUANTITY
end) AS RECEIVED_QTY,
COUNT(case
when s.received_date is not null then
S.CARTON_ID
end) AS RECEIVED_CTN,
COUNT(case
when s.received_date is not null and
s.original_shipment_id != s.shipment_id then
S.CARTON_ID
end) AS buddy_RECEIVED_CTN,
sum(case
when s.received_date is not null and
s.original_shipment_id != s.shipment_id then
S.QUANTITY
end) AS buddy_RECEIVED_pieces,
min(s.received_date) as min_received_date,
max(s.received_date) as max_received_date,
MAX(S.upload_date) as MAX_UPLOAD_DATE,
MIN(S.upload_date) as MIN_UPLOAD_DATE
FROM <proxy />SRC_CARTON_INTRANSIT S
WHERE S.ORIGINAL_SHIPMENT_ID IS NOT NULL
GROUP BY S.SHIPMENT_ID)
SELECT NVL(ES.SHIPMENT_ID,ASH.SHIPMENT_ID) as SHIPMENT_ID,
COUNT(*) OVER() AS TOTAL_SHIPMENTS,
ash.min_buddy_shipment_id,
ash.max_buddy_shipment_id,
es.MIN_OTHER_SHIPMENT_ID,
es.Max_OTHER_SHIPMENT_ID,
es.COUNT_OTHER_SHIPMENTS,
es.COUNT_OTHER_RECEIVED_CTN,
es.COUNT_OTHER_RECEIVED_PIECES,
ash.count_buddy_shipments,
ES.EXPECTED_QUANTITY,
ES.EXPECTED_CTNS,
ASH.RECEIVED_QTY,
ASH.RECEIVED_CTN,
es.NOT_RECEIVED_CTNS,
es.NOT_RECEIVED_QTY,
ash.buddy_RECEIVED_CTN,
ash.buddy_RECEIVED_pieces,
ES.SEWING_PLANT_CODE,
ES.SEWING_PLANT_NAME,
ash.IS_SHIPMENT_CLOSED,
ES.SHIPMENT_DATE,
ash.min_received_date,
ash.max_received_date,
ASH.MIN_UPLOAD_DATE,
ASH.MAX_UPLOAD_DATE,
ES.INTRANSIT_TYPE
FROM EXPECTED_SHIPMENT ES
full outer JOIN ACTUAL_SHIPMENT ASH
ON ES.SHIPMENT_ID = ASH.SHIPMENT_ID
where 1=1
<if>AND ASH.MIN_UPLOAD_DATE >= :MIN_UPLOAD_DATE </if>
<if>AND ASH.MIN_UPLOAD_DATE <= :MAX_UPLOAD_DATE</if>
<if>AND ES.SEWING_PLANT_CODE = :SEWING_PLANT_CODE</if>
<if c='$closed_filter'>and (ash.IS_SHIPMENT_CLOSED is not NULL)</if>
<if c='$open_filter'>and ash.IS_SHIPMENT_CLOSED is NULL</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 ES.EXPECTED_CTNS != ASH.RECEIVED_CTN) </if>
order by ASH.MAX_UPLOAD_DATE desc nulls last";
var binder = SqlBinder.Create(row => new IntransitShipment
{
ShipmentId = row.GetString("SHIPMENT_ID"),
TotalShipmentCount = row.GetInteger("TOTAL_SHIPMENTS"),
MinBuddyShipmentId = row.GetString("min_buddy_shipment_id"),
MaxBuddyShipmentId = row.GetString("max_buddy_shipment_id"),
MinOtherShipmentId = row.GetString("MIN_OTHER_SHIPMENT_ID"),
MaxOtherShipmentId = row.GetString("MAX_OTHER_SHIPMENT_ID"),
CountBuddyShipmentId = row.GetInteger("count_buddy_shipments"),
CountOtherShipmentId = row.GetInteger("COUNT_OTHER_SHIPMENTS"),
CountOtherReceivedCarton = row.GetInteger("COUNT_OTHER_RECEIVED_CTN"),
CountOtherReceivedPieces = row.GetInteger("COUNT_OTHER_RECEIVED_PIECES"),
SewingPlantCode = row.GetString("SEWING_PLANT_CODE"),
SewingPlantName = row.GetString("SEWING_PLANT_NAME"),
ShipmentDate = row.GetDate("SHIPMENT_DATE"),
MinUploadDate = row.GetDate("MIN_UPLOAD_DATE"),
MaxUploadDate = row.GetDate("MAX_UPLOAD_DATE"),
MinReceiveDate = row.GetDate("MIN_RECEIVED_DATE"),
MaxReceiveDate = row.GetDate("MAX_RECEIVED_DATE"),
ExpectedPieces = row.GetInteger("EXPECTED_QUANTITY"),
ExpectedCartonCount = row.GetInteger("EXPECTED_CTNS"),
ReceivedPieces = row.GetInteger("RECEIVED_QTY"),
ReceivedCartonCount = row.GetInteger("RECEIVED_CTN"),
UnReceivedCartonCount = row.GetInteger("NOT_RECEIVED_CTNS"),
UnReceivedPieces = row.GetInteger("NOT_RECEIVED_QTY"),
BuddyCartonCount = row.GetInteger("BUDDY_RECEIVED_CTN"),
BuddyReceivedPieces = row.GetInteger("BUDDY_RECEIVED_PIECES"),
IntransitType = row.GetString("INTRANSIT_TYPE"),
IsShipmentClosed = row.GetString("IS_SHIPMENT_CLOSED") == "Y" ? true : false
})
.Parameter("MIN_UPLOAD_DATE", minCloseDate)
.Parameter("MAX_UPLOAD_DATE", maxCloseDate)
.Parameter("SEWING_PLANT_CODE", sewingPlant);
binder.ParameterXPath("closed_filter", statusFilter.HasFlag(ShipmentFilters.ClosedShipments));
binder.ParameterXPath("open_filter", statusFilter.HasFlag(ShipmentFilters.OpenShipments));
binder.ParameterXPath("transfer_filter", statusFilter.HasFlag(ShipmentFilters.BuildingTransferShipments));
binder.ParameterXPath("vendor_filter", statusFilter.HasFlag(ShipmentFilters.VendorShipments));
binder.ParameterXPath("variance_filter", statusFilter.HasFlag(ShipmentFilters.VarianceOnlyShipments));
return(_db.ExecuteReader(QUERY, binder, nRowsToShow));
}
