//This function is for carton printing
public void PrintCarton(string cartonId, string printerId)
{
Contract.Assert(_db != null);
const string QUERY = @"begin
<proxy />pkg_jf_src_2.pkg_jf_src_ctn_tkt(acarton_id => :acarton_id,
aprinter_name => :aprinter_name);
end;
";
var binder = SqlBinder.Create()
.Parameter("acarton_id", cartonId)
.Parameter("aprinter_name", printerId);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Get the list of PriceSeasonCode.
/// </summary>
/// <returns></returns>
public IEnumerable <CodeDescriptionModel> GetPriceSeasonCode()
{
const string QUERY =
@"SELECT TPS.PRICE_SEASON_CODE, TPS.DESCRIPTION
FROM <proxy />TAB_PRICE_SEASON TPS
ORDER BY TPS.PRICE_SEASON_CODE";
var binder = SqlBinder.Create(row => new CodeDescriptionModel()
{
Code = row.GetString("PRICE_SEASON_CODE"),
Description = row.GetString("DESCRIPTION")
});
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Remove pallet from box.
/// </summary>
/// <param name="ucc128Id"></param>
public void RemovePalletFromBox(string ucc128Id)
{
Contract.Assert(_db != null);
const string QUERY = @"
UPDATE <proxy />BOX B
SET B.PALLET_ID = NULL, B.LOCATION_ID = NULL
WHERE B.UCC128_ID = :UCC128_ID
";
var binder = SqlBinder.Create().Parameter("UCC128_ID", ucc128Id);
_db.ExecuteNonQuery(QUERY, binder);
++_queryCount;
}
/// <summary>
/// Puts all boxes of the pallet in suspense.
/// </summary>
/// <param namparam name="palletId">
/// </param>
public void PutBoxOfPalletInSuspence(string palletId)
{
Contract.Assert(_db != null);
const string QUERY = @"
UPDATE <proxy />BOX B
SET B.SUSPENSE_DATE = SYSDATE
WHERE B.PALLET_ID = :PALLET_ID
";
var binder = SqlBinder.Create().Parameter("PALLET_ID", palletId);
_db.ExecuteNonQuery(QUERY, binder);
++_queryCount;
}
/// <summary>
/// This method is use for find ctnresvId
/// </summary>
/// <param name="reqId"></param>
/// <returns></returns>
// public string GetCtnRevId(string reqId)
// {
// const string QUERY =
// @" select c.ctn_resv_id
// from <proxy />ctnresv c where c.ctn_resv_id=:dcms4_req_id";
// var binder = SqlBinder.Create(row => row.GetString("ctn_resv_id")).Parameter("dcms4_req_id", reqId);
// return _db.ExecuteSingle(QUERY, binder);
// return reqId;
// }
/// <summary>
/// Creates a new request and returns the request id.
/// 25-1-2012:Insert IS_CONVERSION_REQUEST colomn value in table when request is for conversion.
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public void CreateCartonRequest(PullRequest model)
{
//TODO: remove hardwirings of Module Code
const string QUERY = @"
declare
Lresv_rec <proxy />pkg_ctnresv.resv_rec_type;
begin
Lresv_rec.ctn_resv_id := :resv_id;
Lresv_rec.source_area := :source_area;
Lresv_rec.destination_area := :destination_area;
Lresv_rec.pieces_constraint := :pieces_constraint;
Lresv_rec.vwh_id := :source_vwh_id;
Lresv_rec.conversion_vwh_id := :conversion_vwh_id;
Lresv_rec.priority := :priority;
Lresv_rec.quality_code := :quality_code;
Lresv_rec.target_quality := :target_quality;
Lresv_rec.module_code := 'REQ2';
Lresv_rec.warehouse_location_id := :warehouse_location_id;
Lresv_rec.price_season_code := :price_season_code;
Lresv_rec.sewing_plant_code := :sewing_plant_code;
Lresv_rec.receive_date := :receive_date;
Lresv_rec.is_conversion_request := :is_conversion_request;
Lresv_rec.remarks := :remarks;
:ctn_resv_id := <proxy />pkg_ctnresv.create_resv_id(aresv_rec => Lresv_rec);
end;";
var binder = SqlBinder.Create()
.Parameter("source_area", model.SourceAreaId)
.Parameter("destination_area", model.DestinationArea)
.Parameter("pieces_constraint", model.AllowOverPulling)
.Parameter("source_vwh_id", model.SourceVwhId)
.Parameter("conversion_vwh_id", model.TargetVwhId)
.Parameter("priority", model.Priority)
.Parameter("quality_code", model.SourceQuality)
.Parameter("target_quality", model.TargetQuality)
.Parameter("warehouse_location_id", model.BuildingId)
//.Parameter("packaging_preference", model.PackagingPreferance)
.Parameter("price_season_code", model.PriceSeasonCode)
.Parameter("sewing_plant_code", model.SewingPlantCode)
.Parameter("remarks", model.Remarks)
.Parameter("receive_date", model.CartonReceivedDate)
.Parameter("resv_id", model.CtnResvId)
.Parameter("is_conversion_request", model.IsConversionRequest ? "Y" : "")
.OutParameter("ctn_resv_id", val => model.CtnResvId = val)
;
_db.ExecuteNonQuery(QUERY, binder);
return;
}
/// <summary>
/// This function is for retrieving the count of the boxes which are related to the past criteria waiting to be
/// placed on a pallet.
/// If you need verified boxes only then pass needVerifiedBoxes = true
/// </summary>
/// <param name="customerId"></param>
/// <param name="poId"></param>
/// <param name="customerDcId"></param>
/// <param name="bucketId"></param>
/// <param name="needVerifiedBoxes"></param>
/// <param name="isVasUi"> </param>
/// <returns>This function will return the remaining box count of the set criteria boxes</returns>
public int GetQualifyingBoxCount(string customerId, string poId, string customerDcId, int?bucketId, bool needVerifiedBoxes, bool isVasUi)
{
if (string.IsNullOrEmpty(customerId))
{
throw new ArgumentNullException("customerId");
}
const string QUERY = @"
SELECT COUNT(DISTINCT BOX.UCC128_ID) as QUALIFYING_BOXCOUNT
FROM <proxy />BOX BOX
INNER JOIN <proxy />PS PS
ON BOX.PICKSLIP_ID = PS.PICKSLIP_ID
<if c='$isVasUi'>
INNER JOIN <proxy />PS_VAS PSVAS
ON PSVAS.PICKSLIP_ID = PS.PICKSLIP_ID
</if>
WHERE PS.CUSTOMER_ID = :customer_id
<if c='$isVasUi'>
AND BOX.PALLET_ID IS NULL
AND BOX.VERIFY_DATE IS NULL
</if>
<else>
AND BOX.SCAN_TO_PALLET_DATE IS NULL
</else>
AND BOX.STOP_PROCESS_DATE IS NULL
AND PS.TRANSFER_DATE IS NULL
<if>
AND PS.PO_ID = :po_id
</if>
<if>
AND PS.CUSTOMER_DC_ID = :customer_dc_id
</if>
<if>
AND PS.BUCKET_ID = :bucket_id
</if>
<if c='$needVerifiedBoxes'>
AND BOX.VERIFY_DATE IS NOT NULL
</if>
";
var binder = SqlBinder.Create(row => row.GetInteger("QUALIFYING_BOXCOUNT").Value)
.Parameter("customer_id", customerId)
.Parameter("po_id", poId)
.Parameter("customer_dc_id", customerDcId)
.Parameter("bucket_id", bucketId);
binder.ParameterXPath("needVerifiedBoxes", needVerifiedBoxes);
binder.ParameterXPath("isVasUi", isVasUi);
return(_db.ExecuteSingle(QUERY, binder));
}
public Carton GetCarton(string cartonId)
{
const string QUERY = @"
SELECT CTN.CARTON_ID AS CARTON_ID,
CTNDET.SKU_ID AS SKU_ID,
MSKU.STYLE AS STYLE_,
MSKU.COLOR AS COLOR_,
MSKU.DIMENSION AS DIMENSION_,
MSKU.SKU_SIZE AS SKU_SIZE_,
MSKU.UPC_CODE AS UPC_CODE_,
CTN.QUALITY_CODE AS QUALITY_CODE,
CTNDET.QUANTITY AS QUANTITY,
CTN.VWH_ID AS VWH_ID,
CTN.CARTON_STORAGE_AREA AS CARTON_STORAGE_AREA,
TIA.SHORT_NAME AS SHORT_NAME
FROM <proxy />SRC_CARTON CTN
LEFT OUTER JOIN <proxy />SRC_CARTON_DETAIL CTNDET
ON CTNDET.CARTON_ID = CTN.CARTON_ID
LEFT OUTER JOIN <proxy />MASTER_SKU MSKU
ON MSKU.SKU_ID = CTNDET.SKU_ID
LEFT OUTER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON TIA.INVENTORY_STORAGE_AREA = CTN.CARTON_STORAGE_AREA
WHERE CTN.CARTON_ID = :CARTON_ID
";
var binder = SqlBinder.Create(row => new Carton
{
CartonId = row.GetString("CARTON_ID"),
QualityCode = row.GetString("QUALITY_CODE"),
Pieces = row.GetInteger("QUANTITY") ?? 0,
VwhId = row.GetString("VWH_ID"),
Sku = row.GetInteger("SKU_ID") == null ? null : new Sku
{
SkuId = row.GetInteger("SKU_ID").Value,
Style = row.GetString("STYLE_"),
Color = row.GetString("COLOR_"),
Dimension = row.GetString("DIMENSION_"),
SkuSize = row.GetString("SKU_SIZE_"),
UpcCode = row.GetString("UPC_CODE_")
},
Area = new Area
{
AreaId = row.GetString("CARTON_STORAGE_AREA"),
ShortName = row.GetString("SHORT_NAME")
}
})
.Parameter("CARTON_ID", cartonId);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Gets cartons of passed process which are received but not on any pallet.
/// </summary>
/// <param name="processId"></param>
/// <returns>CartonList</returns>
public IList <ReceivedCarton> GetUnpalletizedCartons(int processId)
{
const string QUERY = @"
SELECT
CTN.PALLET_ID AS PALLET_ID,
CTN.CARTON_ID AS CARTON_ID,
CTN.INSERT_DATE AS INSERT_DATE,
CTN.CARTON_STORAGE_AREA AS CARTON_STORAGE_AREA,
CTN.VWH_ID AS VWH_ID,
ctn.inshipment_id AS inshipment_id,
CTNDET.SKU_ID AS SKU_ID,
MSKU.STYLE AS STYLE_,
MSKU.COLOR AS COLOR_,
MSKU.DIMENSION AS DIMENSION_,
MSKU.SKU_SIZE AS SKU_SIZE_,
MSKU.RETAIL_PRICE AS SKU_PRICE
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 M
ON M.STYLE = MSKU.STYLE
WHERE CTN.PALLET_ID IS NULL AND ctn.inshipment_id = :inshipment_id
order by CTN.insert_date desc
FETCH FIRST 500 ROWS ONLY
";
var binder = SqlBinder.Create(row => new ReceivedCarton()
{
CartonId = row.GetString("CARTON_ID"),
ReceivedDate = row.GetDate("INSERT_DATE"),
PalletId = row.GetString("PALLET_ID"),
DestinationArea = row.GetString("CARTON_STORAGE_AREA"),
VwhId = row.GetString("VWH_ID"),
InShipmentId = row.GetInteger("inshipment_id"),
Sku = row.GetInteger("SKU_ID") == null ? null : new Sku
{
SkuId = row.GetInteger("SKU_ID").Value,
Style = row.GetString("STYLE_"),
Color = row.GetString("COLOR_"),
Dimension = row.GetString("DIMENSION_"),
SkuSize = row.GetString("SKU_SIZE_"),
SkuPrice = row.GetDecimal("SKU_PRICE")
}
}).Parameter("INSHIPMENT_ID", processId);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Removes the all reserved cartons to pull against passed pallet.
/// So that those can be assigned to others.
/// </summary>
/// <param name="pullerName"></param>
/// <param name="palletId"></param>
internal int DiscardPalletSuggestion(string pullerName, string palletId)
{
const string QUERY = @"
DELETE <proxy />TEMP_PULL_CARTON
WHERE PALLET_ID = :PALLET_ID
AND OPERATOR_NAME = :OPERATOR_NAME
";
var binder = SqlBinder.Create();
binder.Parameter("PALLET_ID", palletId)
.Parameter("OPERATOR_NAME", pullerName);
return(_db.ExecuteDml(QUERY, binder));
}
/// <summary>
/// To get Style list for Style Auto Complete text box
/// </summary>
/// <param name="searchText">
/// Search term is passed to populate the list
/// </param>
/// <returns></returns>
public IList <Tuple <string, string> > GetStyles(string searchId, string searchDescription)
{
const string QUERY = @"
SELECT MS.STYLE AS STYLE, MS.DESCRIPTION AS DESCRIPTION
FROM <PROXY /> MASTER_STYLE MS
WHERE 1 = 1
AND (UPPER(MS.STYLE) LIKE '%' || UPPER(:id) || '%' OR
UPPER(MS.DESCRIPTION) LIKE '%' || UPPER(:description) || '%')
AND ROWNUM < 40";
var binder = SqlBinder.Create(row => Tuple.Create(row.GetString("STYLE"), row.GetString("DESCRIPTION")))
.Parameter("id", searchId)
.Parameter("description", searchDescription);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Item1 is name. Item2 is description
/// </summary>
/// <returns></returns>
public static IList <Tuple <string, string> > GetPrinters(OracleDatastore db, PrinterType printerType)
{
Contract.Assert(db != null);
const string QUERY = @"
SELECT NAME AS NAME, DESCRIPTION AS DESCRIPTION
FROM <proxy />TAB_PRINTER
WHERE PRINTER_TYPE = :PRINTER_TYPE
ORDER BY NAME ASC
";
var binder = SqlBinder.Create(row => Tuple.Create(row.GetString("NAME"), row.GetString("DESCRIPTION")))
.Parameter("PRINTER_TYPE", printerType == PrinterType.LabelPrinter ? "ZEBRA" : "LASER");
return(db.ExecuteReader(QUERY, binder));
}
internal int RestockCartons(string areaId)
{
var rowCount = 0;
const string QUERY = @"
begin
:result := <proxy />pkg_receiving_lite.restock_cartons(arestockarea_id => :arestockarea_id);
end;
";
var binder = SqlBinder.Create()
.Parameter("arestockarea_id", areaId)
.OutParameter("result", val => rowCount = val ?? 0);
_db.ExecuteNonQuery(QUERY, binder);
return(rowCount);
}
/// <summary>
/// Place the passed carton on passed pallet.
/// </summary>
/// <param name="mergeOnPallet">Pallet Id on which the carton will be put</param>
/// <param name="cartonId">Carton to be palletized</param>
public void PalletizeCarton(string mergeOnPallet, string cartonId)
{
const string QUERY = @"
UPDATE <proxy />SRC_CARTON SC
SET SC.PALLET_ID = :MERGE_ON_PALLET
WHERE SC.CARTON_ID = :CARTON_ID
";
var binder = new SqlBinder("PalletizeCarton")
.Parameter("MERGE_ON_PALLET", mergeOnPallet)
.Parameter("CARTON_ID", cartonId);
++_queryCount;
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Close passed shipment.
/// </summary>
/// <param name="shipmentId"></param>
public void CloseShipment(string shipmentId, long?poId)
{
const string QUERY = @"
begin
-- Call the procedure
<proxy />pkg_rec_2.close_shipment(ashipment_id => :ashipment_id,
apo_id => :apo_id);
end;
";
var binder = SqlBinder.Create()
.Parameter("ashipment_id", shipmentId)
.Parameter("apo_id", poId);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This function adds customer's sku case preference.
/// </summary>
/// <param name="customerId"></param>
/// <param name="skuCaseId"></param>
/// <param name="comments"></param>
public void AddCustSKuCasePreference(string customerId, string skuCaseId, string comments)
{
const string QUERY = @"
INSERT INTO <proxy />CUSTSKUCASE_CONSTRAINTS
(CUSTOMER_ID, CASE_ID, DATE_CREATED, CREATED_BY,COMMENTS)
VALUES
(:CUSTOMER_ID, :CASE_ID, SYSDATE, :CREATED_BY, :COMMENTS)
";
var binder = SqlBinder.Create().Parameter("CUSTOMER_ID", customerId)
.Parameter("CASE_ID", skuCaseId)
.Parameter("CREATED_BY", HttpContext.Current.User.Identity.Name)
.Parameter("COMMENTS", comments);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Removes the VAS configuration for the passed customer and VAS Id
/// </summary>
/// <param name="customerId"></param>
/// <param name="vasId"></param>
/// <returns></returns>
public bool RemoveVasConfiguration(string customerId, string vasId)
{
const string QUERY = @"
DELETE FROM <proxy />MASTER_CUSTOMER_VAS MCV
WHERE MCV.CUSTOMER_ID = :CUSTOMER_ID
AND MCV.VAS_ID = :VAS_ID
";
var binder = SqlBinder.Create()
.Parameter("CUSTOMER_ID", customerId)
.Parameter("VAS_ID", vasId);
var rowCount = _db.ExecuteDml(QUERY, binder);
return(rowCount > 0);
}
public void PrintUCC(string ucc128Id, string printerId)
{
var count = 0;
const string QUERY = @"
BEGIN
:count := <proxy />PKG_PRINT_UCC.WRITE_UCC_TO_FILE(AUCC128_ID => :ucc128Id, APRINTER_NAME => :printerId );
END;
";
var binder = SqlBinder.Create()
.Parameter("ucc128Id", ucc128Id)
.Parameter("printerId", printerId)
.OutParameter("count", val => count = val ?? 0);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Returns the list of Area.
/// </summary>
/// <param name="buildingId">It is required to show all areas in a building</param>
/// <param name="areaId">It is required to show the info of particular area</param>
/// <param name="shortName"></param>
/// <param name="locationId">this is an optional parameter, it will be passed location id was scanned instead of area</param>
/// <returns></returns>
/// <remarks>
/// Sharad 21 Dec 2011: Areas with null building are returned for any building
/// </remarks>
public IEnumerable <Area> GetCartonAreas(string areaId, string buildingId, string shortName, string locationId)
{
const string QUERY = @"
SELECT TIA.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA,
TIA.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
TIA.SHORT_NAME AS SHORT_NAME,
TIA.DESCRIPTION AS DESCRIPTION,
TIA.REPLENISHMENT_AREA_ID AS REPLENISHMENT_AREA_ID,
TIA2.SHORT_NAME AS REPLENISHMENT_SHORT_NAME,
TIA.LOCATION_NUMBERING_FLAG AS LOCATION_NUMBERING_FLAG,
SYSDATE AS QUERY_TIME
FROM <proxy />TAB_INVENTORY_AREA TIA
LEFT OUTER JOIN <proxy />TAB_INVENTORY_AREA TIA2
ON TIA.REPLENISHMENT_AREA_ID = TIA2.INVENTORY_STORAGE_AREA
<if c='$LOCATION_ID'>
INNER JOIN <proxy />MASTER_STORAGE_LOCATION MSL
ON MSL.STORAGE_AREA = TIA.INVENTORY_STORAGE_AREA
</if>
WHERE TIA.STORES_WHAT = 'CTN'
<if c='not($AREA_ID) and not($SHORT_NAME) and not($LOCATION_ID)'>
AND TIA.IS_PALLET_REQUIRED IS NOT NULL
AND tia.location_numbering_flag is not null
</if>
<if>AND MSL.LOCATION_ID = :LOCATION_ID</if>
<if>AND (TIA.WAREHOUSE_LOCATION_ID = :WAREHOUSE_LOCATION_ID OR TIA.WAREHOUSE_LOCATION_ID IS NULL)</if>
<if>AND TIA.INVENTORY_STORAGE_AREA = :AREA_ID</if>
<if>AND TIA.SHORT_NAME = :SHORT_NAME</if>
";
var binder = SqlBinder.Create(row => new Area
{
AreaId = row.GetString("INVENTORY_STORAGE_AREA"),
BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"),
ShortName = row.GetString("SHORT_NAME"),
Description = row.GetString("DESCRIPTION"),
ReplenishAreaId = row.GetString("REPLENISHMENT_AREA_ID"),
ReplenishAreaShortName = row.GetString("REPLENISHMENT_SHORT_NAME"),
QueryTime = row.GetDate("QUERY_TIME").Value,
IsNumbered = row.GetString("LOCATION_NUMBERING_FLAG") == "Y"
});
binder.Parameter("WAREHOUSE_LOCATION_ID", buildingId)
.Parameter("AREA_ID", areaId)
.Parameter("SHORT_NAME", shortName)
.Parameter("LOCATION_ID", locationId);
++_queryCount;
return(_db.ExecuteReader(QUERY, binder));
}
internal bool AcceptCloseShipmentCtn(string cartonId, int processId)
{
int rowCount = 0;
const string QUERY = @"
begin
:result := <proxy />pkg_rec_2.accept_close_shipment_carton(acarton_id => :acarton_id,
aprocess_id => :aprocess_id);
end;
";
var binder = SqlBinder.Create()
.Parameter("acarton_id", cartonId)
.Parameter("aprocess_id", processId).OutParameter("result", val => rowCount = val ?? 0);
_db.ExecuteNonQuery(QUERY, binder);
return(rowCount > 0);
}
internal IList <VirtualWarehouse> GetVirtualWarehouses()
{
const string QUERY = @"
SELECT vwh_id AS vwh_id, description AS description FROM <proxy />tab_virtual_warehouse ORDER BY vwh_id
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create(row => new VirtualWarehouse
{
VwhId = row.GetString("vwh_id"),
Description = row.GetString("description")
});
var result = _db.ExecuteReader(QUERY, binder);
return(result);
}
/// <summary>
/// This function is for validating the location.
/// </summary>
/// <param name="locationId"></param>
/// <returns>True if the location exists else will return false</returns>
public bool IsLocationValid(string locationId)
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT COUNT(I.LOCATION_ID) AS Get_LOCATION_ID
FROM <proxy />IALOC I
WHERE I.LOCATION_ID = :locationid
";
var binder = SqlBinder.Create(row => row.GetInteger("Get_LOCATION_ID").Value)
.Parameter("locationid", locationId);
++_queryCount;
return(_db.ExecuteSingle(QUERY, binder) > 0);
}
/// <summary>
/// Returns the number of rows deleted
/// </summary>
/// <param name="style"></param>
/// <param name="color"></param>
/// <param name="sewingPlantId"></param>
/// <returns></returns>
public int DeleteSpotCheckSetting(string style, string color, string sewingPlantId)
{
const string QUERY = @"
DELETE FROM <proxy />MASTER_SEWINGPLANT_STYLE MS
WHERE MS.STYLE = :STYLE
AND MS.COLOR = :COLOR
AND MS.SEWING_PLANT_CODE = :SEWING_PLANT_CODE
";
var binder = SqlBinder.Create()
.Parameter("STYLE", string.IsNullOrEmpty(style) ? "." : style)
.Parameter("COLOR", string.IsNullOrEmpty(color) ? "." : color)
.Parameter("SEWING_PLANT_CODE", string.IsNullOrEmpty(sewingPlantId) ? "." : sewingPlantId);
//++_queryCount;
return(_db.ExecuteDml(QUERY, binder));
}
public int PrintCatalog(string ucc128Id, string printerId)
{
var count = 0;
const string QUERY = @"
BEGIN
:count := <proxy />FNC_PRINT_CATALOG_LABEL(AUCC128_ID => :ucc128Id, APRINTER_NAME => :printerId);
END;
";
var binder = SqlBinder.Create()
.Parameter("ucc128Id", ucc128Id)
.Parameter("printerId", printerId)
.OutParameter("count", val => count = val ?? 0);
_db.ExecuteNonQuery(QUERY, binder);
return(count);
}
/// <summary>
/// Getting the list of price season codes
/// </summary>
/// <returns></returns>
public IEnumerable <CodeDescriptionModel> GetPriceSeasonCodes()
{
const string QUERY =
@"
SELECT Price_Season_code AS Price_Season_code,
DESCRIPTION AS DESCRIPTION
FROM <proxy />TAB_Price_Season ORDER BY Price_Season_code
";
var binder = SqlBinder.Create(row => new CodeDescriptionModel
{
Code = row.GetString("Price_Season_code"),
Description = row.GetString("DESCRIPTION"),
});
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Return count of non-suspense boxes on the passed pallet.
/// </summary>
/// <param name="palletId"></param>
/// <returns></returns>
public int GetValidBoxesCount(string palletId)
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT COUNT(UCC128_ID) AS UCC128_ID
FROM <proxy />BOX B
WHERE PALLET_ID =:PALLET_ID
AND B.SUSPENSE_DATE IS NULL
AND B.STOP_PROCESS_DATE IS NULL
";
var binder = SqlBinder.Create(row => row.GetInteger("UCC128_ID").Value).Parameter("PALLET_ID", palletId);
++_queryCount;
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Getting list of Building.
/// </summary>
/// <returns></returns>
public IEnumerable <CodeDescriptionModel> GetBuildingList()
{
const string QUERY = @"
SELECT TWL.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
TWL.DESCRIPTION AS DESCRIPTION
FROM <proxy />TAB_WAREHOUSE_LOCATION TWL
ORDER BY WAREHOUSE_LOCATION_ID
";
var binder = SqlBinder.Create(row => new CodeDescriptionModel
{
Code = row.GetString("WAREHOUSE_LOCATION_ID"),
Description = row.GetString("DESCRIPTION"),
});
return(_db.ExecuteReader(QUERY, binder));
}
public bool MarkVasComplete(string ucc128Id)
{
const string QUERY = @"
INSERT INTO <proxy />BOX_VAS BP
(BOX_PROCESS_CODE, UCC128_ID, PROCESS_STATUS)
VALUES
('$CATALOG', :UCC128_ID, 'COMPLETED')
";
var binder = SqlBinder.Create()
.Parameter("UCC128_ID", ucc128Id);
var rowCount = _db.ExecuteDml(QUERY, binder);
++_queryCount;
return(rowCount > 0);
}
/// <summary>
/// Reprints labels for the boxes of <paramref name="batchId"/> as well try to pitch all the box.
/// </summary>
/// <param name="batchId"></param>
/// <param name="printer"></param>
internal void ReprintBatch(string batchId, string printer)
{
const string QUERY = @"
DECLARE
LCount BINARY_INTEGER;
BEGIN
LCount := <proxy />pkg_bucket_lite.PRINT_BATCH(ABATCH_ID => :PALLET_ID, APRINTER_NAME => :PRINTER_NAME);
LCount := <proxy />pkg_bucket_lite.PITCH_BATCH(ABATCH_ID => :PALLET_ID);
END;
";
var binder = SqlBinder.Create()
.Parameter("PALLET_ID", batchId)
.Parameter("PRINTER_NAME", printer);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Returns the list of VAS configuration with their description.
/// </summary>
/// <returns></returns>
public IEnumerable <CodeDescription> GetVasList()
{
const string QUERY = @"
SELECT TV.VAS_CODE AS VAS_CODE,
TV.DESCRIPTION AS DESCRIPTION
FROM <proxy />TAB_VAS TV
";
var binder = SqlBinder.Create(row => new CodeDescription
{
Code = row.GetString("VAS_CODE"),
Description = row.GetString("DESCRIPTION")
});
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// To get Color list for Color Auto Complete text box
/// </summary>
public IList <Tuple <string, string> > GetColors(string searchId, string searchDescription)
{
const string QUERY = @"
SELECT MC.COLOR_ID AS COLOR, MC.COLOR_DESCRIPTION AS DESCRIPTION
FROM <proxy />MASTER_COLOR MC
WHERE 1=1
AND (UPPER(MC.Color_Id) LIKE '%' || UPPER(:ID) || '%' OR
UPPER(MC.Color_Description) LIKE '%' || UPPER(:DESCRIPTION) || '%')
AND ROWNUM <40
";
var binder = SqlBinder.Create(row => Tuple.Create(row.GetString("COLOR"), row.GetString("DESCRIPTION")))
.Parameter("ID", searchId)
.Parameter("DESCRIPTION", searchDescription);
return(_db.ExecuteReader(QUERY, binder));
}