/// <summary>
/// FOR CATRON AREA INVENTORY INFORMATION
/// </summary>
/// <param name="cartonArea"></param>
/// <returns></returns>
public IList <CartonAreaInventory> GetCartonAreaInventory(string cartonArea)
{
Contract.Assert(_db != null);
const string QUERY_CARTON_AREA_INVENTORY_DETAIL = @"
SELECT COUNT(CTN.CARTON_ID) AS CARTON_COUNT,
COUNT(DISTINCT CTNDET.SKU_ID) AS DISTINCT_SKU,
MS.LABEL_ID AS LABEL_ID,
SUM(CTNDET.QUANTITY) AS QUANTITY
FROM <proxy />SRC_CARTON CTN
LEFT OUTER JOIN <proxy />SRC_CARTON_DETAIL CTNDET
ON CTN.CARTON_ID = CTNDET.CARTON_ID
LEFT OUTER JOIN <proxy />Master_Sku msku
ON ctndet.sku_id = msku.sku_id
LEFT OUTER JOIN <proxy />MASTER_STYLE MS
ON msku.style = MS.STYLE
WHERE CTN.CARTON_STORAGE_AREA = :CARTON_STORAGE_AREA
GROUP BY CTN.CARTON_STORAGE_AREA, MS.LABEL_ID
HAVING SUM(CTNDET.QUANTITY) > 0
ORDER BY SUM(CTNDET.QUANTITY) DESC, MS.LABEL_ID
";
var binder = SqlBinder.Create(row => new CartonAreaInventory
{
CartonCount = row.GetInteger("CARTON_COUNT"),
DistinctSKUs = row.GetInteger("DISTINCT_SKU"),
LabelId = row.GetString("LABEL_ID"),
SKUQuantity = row.GetInteger("QUANTITY")
}).Parameter("CARTON_STORAGE_AREA", cartonArea);
return(_db.ExecuteReader(QUERY_CARTON_AREA_INVENTORY_DETAIL, binder));
}
public IList <BucketBase> GetRecentCreatedBucket(int maxRows)
{
const string QUERY = @"SELECT BK.BUCKET_ID AS BUCKET_ID,
MIN(BK.NAME) AS BUCKET_NAME,
MIN(BK.DATE_CREATED) AS DATE_CREATED,
MIN(BK.CREATED_BY) AS CREATED_BY,
MIN(BK.date_modified) as date_modified
FROM BUCKET BK
INNER JOIN PS PS
ON BK.BUCKET_ID = PS.BUCKET_ID
GROUP BY BK.BUCKET_ID
ORDER BY MIN(BK.DATE_CREATED) DESC
NULLS LAST";
var binder = SqlBinder.Create(row => new BucketBase
{
BucketId = row.GetInteger("BUCKET_ID").Value,
CreatedBy = row.GetString("CREATED_BY"),
CreationDate = row.GetDate("DATE_CREATED"),
ModifyDate = row.GetDate("date_modified"),
BucketName = row.GetString("BUCKET_NAME")
});
return(_db.ExecuteReader(QUERY, binder, maxRows));
}
// /// <summary>
// /// count location for passed sku
// /// </summary>
// /// <param name="areaId"></param>
// /// <param name="skuId"></param>
// /// <returns></returns>
// public int GetAssignedLocationCount(string areaId, int skuId)
// {
// const string QUERY = @"
// SELECT COUNT(*) AS COUNT
// FROM <proxy/>MASTER_STORAGE_LOCATION MSL
// WHERE MSL.STORAGE_AREA = :areaId
// AND MSL.ASSIGNED_SKU_ID = :skuId
// ";
// var binder = SqlBinder.Create(row => row.GetInteger("COUNT").Value)
// .Parameter("areaId", areaId)
// .Parameter("skuId", skuId);
// return _db.ExecuteSingle(QUERY, binder);
// }
public IEnumerable <CartonArea> GetCartonAreas()
{
const string QUERY =
@"
SELECT T.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA,
T.WAREHOUSE_LOCATION_ID AS BUILDING_ID,
T.SHORT_NAME AS SHORT_NAME,
T.LOCATION_NUMBERING_FLAG AS IS_NUMBERED_AREA,
T.IS_RECEIVING_AREA AS IS_RECEIVING_AREA,
T.IS_spotcheck_area as IS_SPOTCHECK_AREA,
t.description AS description
FROM <proxy />TAB_INVENTORY_AREA T
WHERE T.STORES_WHAT = 'CTN'
";
var binder = SqlBinder.Create(row => new CartonArea()
{
BuildingId = row.GetString("BUILDING_ID"),
ShortName = row.GetString("SHORT_NAME"),
AreaId = row.GetString("INVENTORY_STORAGE_AREA"),
Description = row.GetString("description"),
IsNumberedArea = row.GetString("IS_NUMBERED_AREA") == "Y",
IsReceivingArea = row.GetString("IS_RECEIVING_AREA") == "Y",
IsSpotCheckArea = row.GetString("IS_SPOTCHECK_AREA") == "Y"
});
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// List of Sku returned.
/// </summary>
/// <returns></returns>
public IList <SkuHeadline> GetSkuList()
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT MAX(M.SKU_ID) AS SKU_ID,
MAX(M.STYLE) AS STYLE,
MAX(M.COLOR) AS COLOR,
MAX(M.DIMENSION) AS DIMENSION,
MAX(M.SKU_SIZE) AS SKU_SIZE,
M.UPC_CODE AS UPC_CODE,
MAX(PS.PICKSLIP_IMPORT_DATE) AS PICKSLIP_IMPORT_DATE
FROM <proxy/>PS PS
INNER JOIN <proxy/>PSDET PD
ON PS.PICKSLIP_ID = PD.PICKSLIP_ID
INNER JOIN <proxy/>MASTER_SKU M
ON M.UPC_CODE = PD.UPC_CODE
GROUP BY M.UPC_CODE
ORDER BY MAX(PS.PICKSLIP_IMPORT_DATE) DESC NULLS LAST
";
var binder = SqlBinder.Create(row => new SkuHeadline
{
SkuId = row.GetInteger("SKU_ID").Value,
Style = row.GetString("STYLE"),
Color = row.GetString("COLOR"),
Dimension = row.GetString("DIMENSION"),
SkuSize = row.GetString("SKU_SIZE"),
Upc = row.GetString("UPC_CODE"),
PickslipOrderDate = row.GetDate("PICKSLIP_IMPORT_DATE")
});
return(_db.ExecuteReader(QUERY, binder, 200));
}
/// <summary>
/// Removed passed pickslip from bucket
/// Sharad Sir and Shiva : Delete Bucket when last pickslip is removed.
/// </summary>
/// <param name="pickslips"></param>
/// <param name="bucketId"></param>
public void RemovePickslipFromBucket(IList <long> pickslips, int bucketId)
{
const string QUERY = @"
DECLARE
LPICKSLIP_COUNT NUMBER;
BEGIN
<proxy />PKG_BUCKET.DELETE_PS_CTNRESV(APICKSLIP_ID => :APICKSLIP_ID);
<proxy />PKG_DATA_EXCHANGE.REVERT_PICKSLIP(APICKSLIP_ID => :APICKSLIP_ID);
SELECT COUNT(P.PICKSLIP_ID)
INTO LPICKSLIP_COUNT
FROM <proxy />PS P
WHERE P.BUCKET_ID = :BUCKET_ID
AND P.TRANSFER_DATE IS NULL;
IF LPICKSLIP_COUNT = 0 THEN
DELETE FROM <proxy />BUCKET BKT WHERE BKT.BUCKET_ID = :BUCKET_ID;
END IF;
END;
";
var binder = SqlBinder.Create(pickslips.Count);
binder.Parameter("APICKSLIP_ID", pickslips)
.Parameter("BUCKET_ID", Enumerable.Repeat(bucketId, pickslips.Count));
_db.ExecuteDml(QUERY, binder);
}
public PickWave GetPickWave(int bucketId)
{
if (bucketId == 0)
{
throw new ArgumentNullException("bucketId");
}
const string QUERY = @"
SELECT COUNT(PS.PICKSLIP_ID) AS PICKSLIP_COUNT,
MAX(T.SHORT_NAME) AS PULL_AREA,
MAX(I.SHORT_NAME) AS PITCH_AREA,
B.BUCKET_ID AS BUCKET_ID
FROM <proxy />BUCKET B
LEFT OUTER JOIN <proxy />PS PS
ON PS.BUCKET_ID = B.BUCKET_ID
AND PS.TRANSFER_DATE IS NULL
LEFT OUTER JOIN <proxy />TAB_INVENTORY_AREA T
ON T.INVENTORY_STORAGE_AREA = B.PULL_CARTON_AREA
LEFT OUTER JOIN <proxy />IA I
ON I.IA_ID = B.PITCH_IA_ID
WHERE B.BUCKET_ID = :BUCKET_ID
GROUP BY B.BUCKET_ID";
var binder = SqlBinder.Create(row => new PickWave
{
BucketId = row.GetInteger("BUCKET_ID") ?? 0,
PullAreaShortName = row.GetString("PULL_AREA"),
PickslipCount = row.GetInteger("PICKSLIP_COUNT") ?? 0,
PitchAreaShortName = row.GetString("PITCH_AREA")
})
.Parameter("BUCKET_ID", bucketId);
return(_db.ExecuteSingle(QUERY, binder));
}
public IList <CustomerSkuLabel> GetPrivateLabelBarCodesOfSku(int skuId)
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT M.CUSTOMER_ID AS CUSTOMER_ID,
C.NAME AS NAME,
NVL(M.SCANNED_BAR_CODE, m.customer_sku_id) AS SCANNED_BAR_CODE
FROM <proxy />MASTER_CUSTOMER_SKU M
LEFT OUTER JOIN <proxy />CUST C
ON M.CUSTOMER_ID = C.CUSTOMER_ID
WHERE M.SKU_ID = :SKU_ID
AND M.INACTIVE_FLAG IS NULL
";
var binder = SqlBinder.Create(row => new CustomerSkuLabel
{
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("NAME"),
//InsertedBy = row.GetString("INSERTED_BY"),
//ModifiedBy = row.GetString("MODIFIED_BY"),
ScannedBarCode = row.GetString("SCANNED_BAR_CODE")
//InsetDate = row.GetDate("INSERT_DATE"),
//ModifiedDate = row.GetDate("MODIFIED_DATE")
}).Parameter("SKU_ID", skuId);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// This method prints the Carton Ticket
/// </summary>
/// <param name="cartonId"></param>
/// <param name="printerId"></param>
public void PrintCartonTicket(string scanText, string printerId)
{
const string QUERY = @"
DECLARE
TYPE CARTON_TABLE_T IS TABLE OF SRC_CARTON.CARTON_ID%TYPE;
CARTON_LIST CARTON_TABLE_T;
BEGIN
SELECT CARTON_ID BULK COLLECT
INTO CARTON_LIST
FROM <proxy />SRC_CARTON S
WHERE S.CARTON_ID = :scanText
OR S.PALLET_ID = :scanText;
IF CARTON_LIST.COUNT > 0 THEN
FOR I IN 1 .. CARTON_LIST.COUNT LOOP
<proxy />PKG_JF_SRC_2.PKG_JF_SRC_CTN_TKT(ACARTON_ID => CARTON_LIST(I),
APRINTER_NAME => :aprinter_name);
END LOOP;
END IF;
END;";
var binder = SqlBinder.Create().Parameter("scanText", scanText)
.Parameter("aprinter_name", printerId);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Get areas to transfer irregular and sample pieces
/// </summary>
/// <param name="pieceFlag"></param>
/// <returns></returns>
public IEnumerable <SkuArea> GetTransferAreas(PiecesRemoveFlag pieceFlag)
{
const string QUERY =
@"
SELECT TIA.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA,
TIA.DESCRIPTION AS DESCRIPTION,
TIA.SHORT_NAME AS SHORT_NAME
FROM <proxy />TAB_INVENTORY_AREA TIA
WHERE TIA.STORES_WHAT = 'SKU'
<if c='$samples'>
AND TIA.UNUSABLE_INVENTORY='Y'
</if>
<if c='$irregular'>
AND TIA.CONSOLIDATED_UPC_CODE IS NOT NULL
</if>
ORDER BY TIA.INVENTORY_STORAGE_AREA
";
var binder = SqlBinder.Create(row => new SkuArea()
{
AreaId = row.GetString("INVENTORY_STORAGE_AREA"),
Description = row.GetString("DESCRIPTION"),
ShortName = row.GetString("SHORT_NAME")
});
binder.ParameterXPath("samples", pieceFlag == PiecesRemoveFlag.Samples);
binder.ParameterXPath("irregular", pieceFlag == PiecesRemoveFlag.Irregular);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// This function returns audit of SKU assignment and unassignment on particular location.
/// </summary>
/// <param name="locationId"></param>
/// <returns></returns>
public IList <LocationAudit> GetLocAssignUnassignAudit(string locationId)
{
const string QUERY = @"
SELECT ACTION_PERFORMED AS ACTION_PERFORMED,
DATE_CREATED AS DATE_CREATED,
MODULE_CODE AS MODULE_CODE,
CREATED_BY AS CREATED_BY,
IACONTENT_ID AS IACONTENT_ID
FROM <proxy />LOCATION_AUDIT
WHERE ACTION_PERFORMED IN
('SKU_UNASSIGNED','SKU_ASSIGNED', 'SKUCHANGED')
AND LOCATION_ID = :LOCATION_ID
ORDER BY DATE_CREATED DESC
";
var binder = SqlBinder.Create(row => new LocationAudit
{
CreatedBy = row.GetString("CREATED_BY"),
ActionPerformed = row.GetString("ACTION_PERFORMED"),
DateCreated = row.GetDate("DATE_CREATED"),
ModuleCode = row.GetString("MODULE_CODE"),
UpcCode = row.GetString("IACONTENT_ID")
}).Parameter("LOCATION_ID", locationId);
return(_db.ExecuteReader(QUERY, binder, 500));
}
/// <summary>
/// This method updates the carton storage and pallet id of a carton
/// </summary>
/// <param name="carton"></param>
/// <param name="moveFlags"></param>
public void MoveCarton(Carton carton, CartonUpdateFlags moveFlags)
{
const string QUERY = @"
BEGIN
<if c='$carton_area'>
UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.CARTON_STORAGE_AREA = :carton_area ,SC.location_id = :location_id WHERE SC.CARTON_ID = :carton_id;
</if>
<if c='$pallet_id'>
UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.PALLET_ID = :pallet_id WHERE SC.CARTON_ID = :carton_id;
</if>
<if c='$removePallet'>
UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.PALLET_ID = NULL WHERE SC.CARTON_ID = :carton_id;
</if>
<if c='$updateLocation'>
UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.location_id = :location_id WHERE SC.CARTON_ID = :carton_id;
</if>
END;
";
var binder = SqlBinder.Create().Parameter("carton_id", carton.CartonId)
.Parameter("carton_area", moveFlags.HasFlag(CartonUpdateFlags.Area) ? carton.CartonArea.AreaId : null)
.Parameter("pallet_id", moveFlags.HasFlag(CartonUpdateFlags.Pallet) ? carton.PalletId : null)
.Parameter("location_id", moveFlags.HasFlag(CartonUpdateFlags.Location) ? carton.LocationId : null);
binder.ParameterXPath("removePallet", moveFlags.HasFlag(CartonUpdateFlags.RemovePallet));
binder.ParameterXPath("updateLocation", moveFlags.HasFlag(CartonUpdateFlags.Location));
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This function gets number of boxes per pallet on sku location
/// </summary>
/// <param name="locationId"></param>
/// <returns></returns>
public IList <SkuLocationPallet> GetPalletsOfSkuLocation(string locationId)
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT B.PALLET_ID AS PALLET_ID,
COUNT(DISTINCT B.UCC128_ID) AS TOTALBOXES,
C.CUSTOMER_ID AS CUSTOMER_ID,
MAX(C.NAME) AS CUSTOMER_NAME
FROM <proxy />BOX B
INNER JOIN <proxy />BOXDET BD
ON B.UCC128_ID = BD.UCC128_ID
LEFT OUTER JOIN <proxy />PS P
ON B.PICKSLIP_ID = P.PICKSLIP_ID
LEFT OUTER JOIN <proxy />CUST C
ON P.CUSTOMER_ID = C.CUSTOMER_ID
WHERE B.LOCATION_ID = :LOCATION_ID and b.stop_process_date is null
AND B.PALLET_ID IS NOT NULL
GROUP BY B.PALLET_ID,C.CUSTOMER_ID
ORDER BY B.PALLET_ID
";
var binder = SqlBinder.Create(row => new SkuLocationPallet
{
TotalBoxes = row.GetInteger("TOTALBOXES") ?? 0,
PalletId = row.GetString("PALLET_ID"),
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("CUSTOMER_NAME")
}).Parameter("LOCATION_ID", locationId);
return(_db.ExecuteReader(QUERY, binder));
}
public IList <ReturnReceiptHeadline> GetReturnList()
{
const string QUERY = @"SELECT
DR.RETURNS_AUTHORIZATION_NUMBER AS RETURNS_AUTHORIZATION_NUMBER,
COUNT(DR.RECEIPT_NUMBER) AS TOTAL_RECEIPT_NUMBER,
SUM(DR.NO_OF_CARTONS) AS TOTAL_CARTONS,
SUM(DR.EXPECTED_PIECES) AS TOTAL_EXPECTED_PIECES,
MIN(DR.RECEIVED_DATE) AS RECEIVED_DATE,
COUNT(UNIQUE DR.CUSTOMER_ID) AS CUSTOMER_COUNT,
MAX(MC.CUSTOMER_ID) AS CUSTOMER_ID,
MAX(MC.NAME) AS CUSTOMER_NAME
FROM DEM_RETURNS DR
LEFT OUTER JOIN MASTER_CUSTOMER MC
ON MC.CUSTOMER_ID = DR.CUSTOMER_ID
GROUP BY DR.RETURNS_AUTHORIZATION_NUMBER
ORDER BY MIN(DR.RECEIVED_DATE) DESC
";
var binder = SqlBinder.Create(row => new ReturnReceiptHeadline
{
ReturnNumber = row.GetString("RETURNS_AUTHORIZATION_NUMBER"),
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("CUSTOMER_NAME"),
TotalReceipts = row.GetInteger("TOTAL_RECEIPT_NUMBER").Value,
NoOfCartons = row.GetInteger("TOTAL_CARTONS"),
ExpectedPieces = row.GetInteger("TOTAL_EXPECTED_PIECES"),
ReceivedDate = row.GetDate("RECEIVED_DATE").Value,
CustomerCount = row.GetInteger("CUSTOMER_COUNT")
});
return(_db.ExecuteReader(QUERY, binder, 200));
}
/// <summary>
/// Function return the PO info.
/// </summary>
/// <param name="poId"></param>
/// <param name="customerId"></param>
/// <param name="iteration"></param>
/// <returns></returns>
public PurchaseOrder GetPo(string poId, string customerId, int iteration)
{
Contract.Assert(_db != null);
const string QUERY_PO_INFO = @"
SELECT PO.PO_ID AS PO_ID,
PO.CUSTOMER_ID AS CUSTOMER_ID,
PO.ITERATION AS ITERATION,
MAX(CUST.NAME) AS NAME,
MAX(PO.ORDER_DATE) AS ORDER_DATE,
MIN(PO.START_DATE) AS START_DATE,
MIN(PO.CANCEL_DATE) AS CANCEL_DATE,
MIN(PO.DC_CANCEL_DATE) AS DC_CANCEL_DATE,
COUNT(DISTINCT CASE
WHEN B.LAST_UCC_PRINT_DATE IS NOT NULL THEN
B.UCC128_ID
END) AS NUMBER_OF_UCC_PRINTED,
COUNT(DISTINCT CASE
WHEN B.LAST_CCL_PRINT_DATE IS NOT NULL THEN
B.UCC128_ID
END) AS NUMBER_OF_CCL_PRINTED,
COUNT(DISTINCT B.UCC128_ID) AS TOTAL_BOXES,
COUNT(UNIQUE PO.ITERATION) OVER() AS COUNT_ITERATIONS
FROM <proxy />PO PO
LEFT OUTER JOIN <proxy />PS PS
ON PS.PO_ID = PO.PO_ID
AND PS.CUSTOMER_ID = PO.CUSTOMER_ID
AND PS.ITERATION = PO.ITERATION
LEFT OUTER JOIN <proxy />BOX B
ON PS.PICKSLIP_ID = B.PICKSLIP_ID
INNER JOIN <proxy />CUST CUST
ON CUST.CUSTOMER_ID = PO.CUSTOMER_ID
WHERE PO.PO_ID = :PO_ID
AND PO.CUSTOMER_ID = :CUSTOMER_ID
--AND PO.ITERATION = :ITERATION
GROUP BY PO.CUSTOMER_ID, PO.PO_ID, PO.ITERATION
ORDER BY case when po.iteration = :ITERATION THEN :ITERATION END NULLS LAST
";
var binder = SqlBinder.Create(row => new PurchaseOrder
{
PoId = row.GetString("PO_ID"),
//PSCount = row.GetInteger("PS_COUNT"),
OrderDate = row.GetDate("ORDER_DATE"),
StartDate = row.GetDate("START_DATE"),
CancelDate = row.GetDate("CANCEL_DATE"),
DcCancelDate = row.GetDate("DC_CANCEL_DATE"),
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("NAME"),
Iteration = row.GetInteger("ITERATION") ?? 0,
CountOfCclPrinted = row.GetInteger("NUMBER_OF_CCL_PRINTED") ?? 0,
CountOfUccPrinted = row.GetInteger("NUMBER_OF_UCC_PRINTED") ?? 0,
TotalBoxes = row.GetInteger("TOTAL_BOXES") ?? 0,
CountIterations = row.GetInteger("COUNT_ITERATIONS") ?? 0
}).Parameter("PO_ID", poId)
.Parameter("CUSTOMER_ID", customerId)
.Parameter("ITERATION", iteration);
return(_db.ExecuteSingle(QUERY_PO_INFO, binder));
}
/// <summary>
/// Prints the pallet passed
/// We pass 'ACOPIES' parameter as null in 'PKG_BOXEXPEDITE.PRINT_PALLET' so that default documents are printed.
/// </summary>
/// <param name="palletId"></param>
/// <param name="printer"></param>
public void Print(string palletId, string printer)
{
if (string.IsNullOrEmpty(palletId))
{
throw new ArgumentNullException("Pallet is null");
}
if (string.IsNullOrEmpty(printer))
{
throw new ArgumentNullException("Printer is null");
}
const string QUERY = @"
BEGIN
<proxy />PKG_BOXEXPEDITE.PRINT_PALLET(APALLET_ID => :APALLET_ID,
APRINTER_NAME => :APRINTER_NAME,
ACOPIES => NULL);
END;
";
var binder = SqlBinder.Create()
.Parameter("APALLET_ID", palletId)
.Parameter("APRINTER_NAME", printer);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This method returns all value in SKuModel.
/// </summary>
/// <param name="style"></param>
/// <param name="color"></param>
/// <param name="dimension"></param>
/// <param name="skuSize"></param>
/// <returns>
/// Return style,color,dimension,SKuSize and SKuId.
/// </returns>
public Sku GetSku(string style, string color, string dimension, string skuSize)
{
const string QUERY =
@" SELECT MSKU.SKU_ID AS SKU_ID,
MSKU.STYLE AS STYLE,
MSKU.COLOR AS COLOR,
MSKU.DIMENSION AS DIMENSION,
MSKU.SKU_SIZE AS SKU_SIZE
FROM <proxy />MASTER_SKU MSKU
WHERE MSKU.STYLE = :STYLE
AND MSKU.COLOR = :COLOR
AND MSKU.DIMENSION = :DIMENSION
AND MSKU.SKU_SIZE = :SKU_SIZE";
var binder = SqlBinder.Create(row => new Sku
{
Style = row.GetString("STYLE"),
Color = row.GetString("COLOR"),
Dimension = row.GetString("DIMENSION"),
SkuSize = row.GetString("SKU_SIZE"),
SkuId = row.GetInteger("SKU_ID").Value
}).Parameter("STYLE", style)
.Parameter("COLOR", color)
.Parameter("DIMENSION", dimension)
.Parameter("SKU_SIZE", skuSize);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Create bucket
/// </summary>
/// <param name="bucket"></param>
/// <returns></returns>
public int CreateDefaultWave()
{
const string QUERY = @"
INSERT INTO <proxy />BUCKET BKT
(BKT.BUCKET_ID,
BKT.NAME,
BKT.PRIORITY,
BKT.FREEZE)
VALUES
(<proxy />BUCKET_SEQUENCE.NEXTVAL,
'Automatically Created',
'1',
'Y')
RETURNING BUCKET_ID INTO :BUCKET_ID
";
var binder = SqlBinder.Create();
//binder.Parameter("PITCH_TYPE", "BOX")
// .Parameter("NAME", bucket.BucketName)
// .Parameter("PITCH_IA_ID", bucket.PitchAreaId)
// .Parameter("PRIORITY", bucket.PriorityId)
// .Parameter("PULL_CARTON_AREA", bucket.PullAreaId)
// .Parameter("QUICK_PITCH_FLAG", bucket.QuickPitch ? "Y" : null)
// .Parameter("PULL_TO_DOCK", bucket.PullingBucket)
// ;
var bucketId = 0;
binder.OutParameter("BUCKET_ID", val => bucketId = val.Value);
_db.ExecuteDml(QUERY, binder);
return(bucketId);
}
/// <summary>
/// Returns all carton areas
/// 25-1-2012: Removing conversion area.Now conversion can be done in any area.
/// </summary>
/// <returns></returns>
public IEnumerable <CartonArea> GetCartonAreas(string areaId)
{
const string QUERY =
@"
SELECT TIA.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA,
TIA.DESCRIPTION AS DESCRIPTION,
TIA.SHORT_NAME AS SHORT_NAME,
TIA.STORES_WHAT AS STORES_WHAT,
TIA.LOCATION_NUMBERING_FLAG AS LOCATION_NUMBERING_FLAG,
TIA.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
TIA.UNUSABLE_INVENTORY AS UNUSABLE_INVENTORY
FROM <proxy />TAB_INVENTORY_AREA TIA
<if>
WHERE TIA.INVENTORY_STORAGE_AREA = :INVENTORY_STORAGE_AREA
</if>
ORDER BY TIA.INVENTORY_STORAGE_AREA
";
var binder = SqlBinder.Create(row => new CartonArea
{
AreaId = row.GetString("INVENTORY_STORAGE_AREA"),
Description = row.GetString("DESCRIPTION"),
ShortName = row.GetString("SHORT_NAME"),
BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"),
LocationNumberingFlag = row.GetString("LOCATION_NUMBERING_FLAG") == "Y",
UnusableInventory = row.GetString("UNUSABLE_INVENTORY") == "Y",
IsCartonArea = row.GetString("STORES_WHAT") == "CTN"
}).Parameter("INVENTORY_STORAGE_AREA", areaId);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// It will create a pallet for building and area specially for ADR pulling
/// </summary>
public int CreateADRPallet(string palletId, string buildingId, string area)
{
if (string.IsNullOrEmpty(palletId))
{
throw new ArgumentNullException("PalletId is null");
}
if (string.IsNullOrEmpty(buildingId))
{
throw new ArgumentNullException("Building is null");
}
const string QUERY = @"
begin
:result := <proxy />pkg_boxexpedite.create_adr_pallet(apallet_id => :apallet_id,
abuilding => :abuilding,
adestination_area => :adestination_area);
end;
";
int rowsAffected = 0;
var binder = SqlBinder.Create().Parameter("apallet_id", palletId)
.Parameter("abuilding", buildingId)
.Parameter("adestination_area", area)
.OutParameter("result", m => rowsAffected = m ?? 0);
_db.ExecuteNonQuery(QUERY, binder);
return(rowsAffected);
}
/// <summary>
/// This function returns a list of customer preferred SKU cases.
/// </summary>
/// <returns></returns>
public IEnumerable <CustomerSkuCase> GetCustomerSkuCaseList()
{
const string QUERY = @"
SELECT SCC.CUSTOMER_ID AS CUSTOMER_ID,
CUST.NAME AS NAME,
SCC.CASE_ID AS CASE_ID,
SCASE.SHORT_DESCRIPTION AS SHORT_DESCRIPTION,
SCASE.MAX_CONTENT_VOLUME AS MAX_CONTENT_VOLUME,
SCASE.OUTER_CUBE_VOLUME AS OUTER_CUBE_VOLUME,
SCASE.EMPTY_WT AS EMPTY_WT,
SCC.COMMENTS AS COMMENTS
FROM <proxy />CUSTSKUCASE_CONSTRAINTS SCC
INNER JOIN <proxy />SKUCASE SCASE
ON SCASE.CASE_ID = SCC.CASE_ID
LEFT OUTER JOIN <proxy />CUST CUST
ON SCC.CUSTOMER_ID = CUST.CUSTOMER_ID
WHERE SCASE.MAX_CONTENT_VOLUME IS NOT NULL
AND SCASE.MAX_CONTENT_VOLUME > 0.0
ORDER BY SCC.CUSTOMER_ID,SCASE.MAX_CONTENT_VOLUME DESC
";
var binder = SqlBinder.Create(row => new CustomerSkuCase
{
CaseId = row.GetString("CASE_ID"),
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("NAME"),
EmptyWeight = row.GetDecimal("EMPTY_WT"),
MaxContentVolume = row.GetDecimal("MAX_CONTENT_VOLUME"),
OuterCubeVolume = row.GetDecimal("OUTER_CUBE_VOLUME"),
Comment = row.GetString("COMMENTS"),
CaseDescription = row.GetString("SHORT_DESCRIPTION")
});
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Retrieve Scanned style information
/// </summary>
/// <param name="style"></param>
/// <returns></returns>
public Style GetStyleInfo(string style)
{
Contract.Assert(_db != null);
const string QUERY = @"
with q1 AS
(SELECT MS.STYLE AS STYLE,
MS.DESCRIPTION AS DESCRIPTION,
msc.color,
MS.LABEL_ID AS LABEL_ID,
MSC.COUNTRY_ID AS COUNTRY_ID,
tc.name as country_name
FROM <proxy/>MASTER_STYLE MS
LEFT OUTER JOIN <proxy/>MASTER_STYLE_COLOR MSC
ON MS.STYLE = MSC.STYLE
LEFT OUTER JOIN <proxy/>TAB_COUNTRY TC
ON MSC.COUNTRY_ID = TC.COUNTRY_ID
WHERE MS.STYLE = :style
)
select STYLE, DESCRIPTION, LABEL_ID, cast(country_id_xml as varchar2(4000)) as country_id_xml
from q1 pivot XML(count(distinct q1.color) as count_colors, max(q1.country_name) as country_name for country_id in(ANY))
";
var binder = SqlBinder.Create(row => new Style
{
StyleId = row.GetString("STYLE"),
Description = row.GetString("DESCRIPTION"),
LabelId = row.GetString("LABEL_ID"),
CountryOfOrigins = MapCountryXml(row.GetString("country_id_xml"))
}).Parameter("STYLE", style);
var result = _db.ExecuteSingle(QUERY, binder);
return(result);
}
/// <summary>
/// This function returns a list of SKU cases.
/// </summary>
/// <returns></returns>
public IEnumerable <SkuCase> GetSkuCaseList(string skuCaseId)
{
const string QUERY = @"
SELECT S.CASE_ID AS CASE_ID,
S.SHORT_DESCRIPTION AS SHORT_DESCRIPTION,
S.EMPTY_WT AS EMPTY_WT,
S.MAX_CONTENT_VOLUME AS MAX_CONTENT_VOLUME,
S.UNAVAILABILITY_FLAG AS UNAVAILABILITY_FLAG,
S.OUTER_CUBE_VOLUME AS OUTER_CUBE_VOLUME
FROM <proxy />SKUCASE S
WHERE 1 = 1
<if>AND UPPER(S.CASE_ID) = UPPER(:CASE_ID)</if>
ORDER BY S.UNAVAILABILITY_FLAG NULLS FIRST, S.MAX_CONTENT_VOLUME DESC
";
var binder = SqlBinder.Create(row => new SkuCase
{
CaseId = row.GetString("CASE_ID"),
Description = row.GetString("SHORT_DESCRIPTION"),
EmptyWeight = row.GetDecimal("EMPTY_WT"),
IsAvailable = string.IsNullOrEmpty(row.GetString("UNAVAILABILITY_FLAG")),
MaxContentVolume = row.GetDecimal("MAX_CONTENT_VOLUME"),
OuterCubeVolume = row.GetDecimal("OUTER_CUBE_VOLUME")
}).Parameter("CASE_ID", skuCaseId);
return(_db.ExecuteReader(QUERY, binder));
}
public int CreateBol(string customerId, ICollection <Tuple <string, int, string> > poList)
{
const string QUERY = @"
DECLARE
LEDI_ID EDI_753_754.EDI_ID%TYPE;
LBOL_COUNT NUMBER(3);
BEGIN
LEDI_ID := PKG_EDI_2.CREATE_EDI(ACUSTOMER_ID => :CUSTOMER_ID);
PKG_EDI_2.ADD_PO_TO_EDI(AEDI_ID => LEDI_ID,
APO_LIST => :PO_LIST,
AITERATION_LIST => :ITERATION_LIST,
ADC_LIST => :DC_LIST,
AATS_DATE => SYSDATE);
:LBOL_COUNT := PKG_EDI_2.CREATE_BOL_FOR_EDI(AEDI_ID => LEDI_ID);
END;";
int bolCount = 0;
var binder = SqlBinder.Create();
binder.ParameterAssociativeArray("PO_LIST", poList.Select(p => p.Item1).ToArray());
binder.ParameterAssociativeArray("ITERATION_LIST", poList.Select(p => (int?)p.Item2).ToArray());
binder.ParameterAssociativeArray("DC_LIST", poList.Select(p => p.Item3).ToArray());
binder.Parameter("CUSTOMER_ID", customerId);
binder.OutParameter("LBOL_COUNT", val => bolCount = val.Value);
_db.ExecuteNonQuery(QUERY, binder);
return(bolCount);
}
/// <summary>
/// For Customer autocomplete
/// </summary>
/// <param name="term"></param>
/// <returns></returns>
public IEnumerable<Customer> CustomerAutoComplete(string term)
{
const string QUERY =
@"
SELECT CUST.CUSTOMER_ID AS CUSTOMER_ID,
CUST.NAME AS CUSTOMER_NAME
FROM <proxy />CUST CUST
WHERE 1 = 1
<if c='$TERM'>
AND (UPPER(CUST.CUSTOMER_ID) LIKE '%' || UPPER(:TERM) ||'%'
OR UPPER(CUST.NAME) LIKE '%' || UPPER(:TERM) ||'%')
</if>
AND CUST.INACTIVE_FLAG IS NULL
AND ROWNUM < 40
ORDER BY CUST.CUSTOMER_ID
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create(row => new Customer
{
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("CUSTOMER_NAME")
}).Parameter("TERM", term);
return _db.ExecuteReader(QUERY, binder);
}
public IEnumerable <CartonLocation> RetrieveCartonLocationsForPallet(string palletId)
{
if (string.IsNullOrEmpty(palletId))
{
throw new ArgumentNullException("PalletId is null");
}
const string QUERY = @"
begin
:result := <proxy />pkg_boxpick.get_pallet_carton_locations(apallet_id => :apallet_id);
end;
";
var binder = SqlBinder.Create(row => new CartonLocation
{
CartonLocationId = row.GetString("carton_location_id"),
CartonStorageArea = row.GetString("carton_storage_area"),
CountCartonsToPick = row.GetInteger("count_cartons_to_pick").Value,
PiecesPerCarton = row.GetInteger("pieces_per_carton").Value,
SkuToPick = new Sku
{
Style = row.GetString("style"),
Color = row.GetString("color"),
Dimension = row.GetString("dimension"),
SkuSize = row.GetString("sku_size"),
SkuId = row.GetInteger("sku_id").Value
}
}).Parameter("apallet_id", palletId)
.OutRefCursorParameter("result");
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Queries the passed text against the database.
/// </summary>
/// <param name="scan"></param>
/// <returns></returns>
internal IList <SearchQueryResult> ParseScan(string scan)
{
long intParam = 0;
//var b = !scan.StartsWith("0") && int.TryParse(scan, out intParam);
var isNumericScan = !scan.StartsWith("0") && long.TryParse(scan, out intParam);
//var binder = new SqlBinder<ScanInfo>("Scan Type", true);
var binder = SqlBinder.Create(row => new SearchQueryResult
{
RoutingId = row.GetInteger(0) ?? 0,
Id = row.GetString(1),
ScanDescription = row.GetString(2),
PrimaryKey1 = row.GetString(3),
PrimaryKey2 = row.GetString(4),
});
binder.Parameter("search_text", scan);
binder.Parameter("int_value", isNumericScan ? intParam : (long?)null);
var queries = from routing in SearchRoutingList
let isNumericQuery = System.Globalization.CultureInfo.InvariantCulture.CompareInfo.IndexOf(routing.SearchAttr.Query, ":int_value", CompareOptions.IgnoreCase) >= 0
where isNumericScan || !isNumericQuery
select string.Format(routing.SearchAttr.Query, routing.SearchAttr.RoutingId);
var queryText = string.Join(" UNION ALL ", queries);
var results = _db.ExecuteReader(queryText, binder, 1000);
return(results); // query.ToArray();
}
/// <summary>
/// Update the info for the passed process
/// </summary>
/// <param name="info"></param>
public void UpdateProcess(ReceivingProcess info)
{
const string QUERY = @"
UPDATE <proxy />SRC_CARTON_PROCESS SCP
SET
SCP.PRO_NUMBER= :PRO_NUMBER,
SCP.EXPECTED_CARTON = :EXPECTED_CARTON,
SCP.PRO_DATE= :PRO_DATE,
SCP.PALLET_LIMIT = :PALLET_LIMIT,
SCP.CARRIER_ID= :CARRIER_ID,
SCP.PRICE_SEASON_CODE = :PRICE_SEASON_CODE,
SCP.RECEIVING_AREA_ID = :RECEIVING_AREA_ID,
SCP.SPOT_CHECK_AREA_ID = :SPOT_CHECK_AREA_ID
WHERE SCP.PROCESS_ID= :PROCESS_ID
";
var binder = SqlBinder.Create()
.Parameter("PRO_NUMBER", info.ProNumber)
.Parameter("PROCESS_ID", info.ProcessId)
.Parameter("EXPECTED_CARTON", info.ExpectedCartons)
.Parameter("PALLET_LIMIT", info.PalletLimit)
.Parameter("PRO_DATE", info.ProDate)
.Parameter("CARRIER_ID", info.CarrierId)
.Parameter("PRICE_SEASON_CODE", info.PriceSeasonCode)
.Parameter("RECEIVING_AREA_ID", info.ReceivingAreaId)
.Parameter("SPOT_CHECK_AREA_ID", info.SpotCheckAreaId);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This function returns pallets history from box productivity table
/// </summary>
/// <param name="palletId"></param>
/// <returns></returns>
public IList <BoxPalletHistory> GetBoxPalletHistory(string palletId)
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT B.OPERATION_START_DATE AS OPERATION_START_DATE,
B.OPERATOR AS OPERATOR,
B.OUTCOME AS OUTCOME,
B.MODULE_CODE AS MODULE_CODE,
B.OPERATION_CODE AS OPERATION_CODE
FROM <proxy />BOX_PRODUCTIVITY B
WHERE B.TO_PALLET = :PALLET_ID
OR B.FROM_PALLET = :PALLET_ID
order by B.OPERATION_START_DATE desc
";
var binder = SqlBinder.Create(row => new BoxPalletHistory
{
OperationStartDate = row.GetDate("OPERATION_START_DATE").Value,
Operator = row.GetString("OPERATOR"),
OutCome = row.GetString("OUTCOME"),
ModuleCode = row.GetString("MODULE_CODE"),
Operation = row.GetString("OPERATION_CODE")
}).Parameter("PALLET_ID", palletId);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// This method disables the VAS configuration for passed customer and VAS, on basis of all/selective non-validated orders .
/// </summary>
/// <param name="customerId"></param>
/// <param name="vasId"></param>
/// <param name="regExp"> </param>
/// <param name="currentOrdersOnly">
/// True: method will disable configuration from Current orders only.
/// False: method will disable configuration from All orders excluding Current orders.
/// Null: method will disable configuration from All orders.
/// </param>
internal void DisableVasConfiguration(string customerId, string vasId, string regExp, bool?currentOrdersOnly)
{
const string QUERY = @"
BEGIN
<if c= 'not($currentOrdersOnly) or $allOrders'>
UPDATE <proxy />MASTER_CUSTOMER_VAS MCV
SET MCV.INACTIVE_FLAG = 'Y'
WHERE MCV.CUSTOMER_ID = :CUSTOMER_ID
AND MCV.VAS_ID = :VAS_ID;
</if>
<if c= '$currentOrdersOnly or $allOrders'>
DELETE <proxy />PS_VAS T
WHERE T.PICKSLIP_ID IN
(SELECT DISTINCT(P.PICKSLIP_ID) AS PICKSLIP_ID
FROM <proxy />PS P
INNER JOIN <proxy />MASTER_CUSTOMER_VAS MCV
ON P.CUSTOMER_ID = MCV.CUSTOMER_ID
WHERE MCV.CUSTOMER_ID = :CUSTOMER_ID
AND MCV.VAS_ID = :VAS_ID
AND P.TRANSFER_DATE IS NULL
AND P.PICKSLIP_CANCEL_DATE IS NULL
AND REGEXP_LIKE(P.PO_ID || '@' || P.LABEL_ID, NVL(:REGEX_PATTERN,'.')));
</if>
END;
";
var binder = SqlBinder.Create()
.Parameter("CUSTOMER_ID", customerId)
.Parameter("VAS_ID", vasId)
.Parameter("REGEX_PATTERN", regExp);
binder.ParameterXPath("currentOrdersOnly", currentOrdersOnly != null && currentOrdersOnly.Value);
binder.ParameterXPath("allOrders", currentOrdersOnly == null);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Based on the passed string, returns the context in which picking must happen
/// </summary>
/// <param name="scan">Must be either building or area</param>
/// <returns></returns>
public PickContext GetPickContext(string scan)
{
if (string.IsNullOrEmpty(scan))
{
throw new ArgumentNullException("BuildingId is null");
}
const string QUERY = @"
SELECT TIA.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA,
TIA.WAREHOUSE_LOCATION_ID AS BUILDING_ID,
TIA.SHORT_NAME AS SHORT_NAME
FROM <proxy />TAB_INVENTORY_AREA TIA
WHERE TIA.SHORT_NAME = :SCAN
UNION ALL
SELECT NULL AS INVENTORY_STORAGE_AREA,
TBL.WAREHOUSE_LOCATION_ID AS BUILDING_ID,
NULL AS SHORT_NAME
FROM <proxy />TAB_WAREHOUSE_LOCATION TBL
WHERE TBL.WAREHOUSE_LOCATION_ID = :SCAN
ORDER BY INVENTORY_STORAGE_AREA NULLS LAST
";
var binder = SqlBinder.Create(row => new PickContext
{
BuildingId = row.GetString("BUILDING_ID"),
SourceArea = row.GetString("INVENTORY_STORAGE_AREA"),
SourceAreaShortName = row.GetString("SHORT_NAME")
}).Parameter("SCAN", scan);
return(_db.ExecuteSingle(QUERY, binder));
}
