/// <summary>
/// Returns all inventory areas for which there are cartons to pull
/// </summary>
/// <returns></returns>
public IEnumerable <Area> GetInventoryAreas()
{
const string QUERY = @"
SELECT T.Pick_Ia_Id AS Pick_Ia_Id,
MAX(I.SHORT_NAME) AS SHORT_NAME,
MAX(T.RESTOCK_AREA_ID) AS RESTOCK_AREA_ID,
MAX(T.Pull_Area_Id) AS Pull_Area_Id,
T.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
COUNT(Td.CARTON_ID) AS CARTON_COUNT
FROM <proxy/>REPLENISH_aisle T
LEFT OUTER JOIN <proxy/>IA I
ON I.IA_ID = T.PICK_IA_ID
inner join <proxy/>REPLENISH_aisle_carton td
on t.replenish_aisle_id = td.replenish_aisle_id
GROUP BY T.WAREHOUSE_LOCATION_ID, T.Pick_Ia_Id
ORDER BY T.WAREHOUSE_LOCATION_ID, T.Pick_Ia_Id
";
var binder = SqlBinder.Create(row => new Area
{
AreaId = row.GetString("Pick_Ia_Id"),
ShortName = row.GetString("SHORT_NAME"),
RestockAreaId = row.GetString("RESTOCK_AREA_ID"),
CartonAreaId = row.GetString("Pull_Area_Id"),
BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"),
PullableCartonCount = row.GetInteger("CARTON_COUNT") ?? 0
});
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Gets location suggestions for passed UPC ,VWH and Building.
/// If location passed it gets details of the passed location.
/// </summary>
/// <param name="upcCode">string</param>
/// <param name="vwhId">string</param>
/// <returns>List of locations if no location passed else returns passed locations detail</returns>
public IList <AssignedLocation> GetAssignedLocations(int skuId)
{
const string QUERY = @"
SELECT I.LOCATION_ID AS LOCATION_ID,
I.RESTOCK_AISLE_ID AS RESTOCK_AISLE_ID,
I.IA_ID AS IA_ID,
I.ASSIGNED_UPC_MAX_PIECES AS RAILCAPACITY,
I.VWH_ID as VWH_ID,
I.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
IC.NUMBER_OF_UNITS AS PIECES_AT_LOCATION
FROM <proxy />IALOC I
inner join <proxy />master_sku ms
on ms.upc_code = i.assigned_upc_code
LEFT OUTER JOIN <proxy />IALOC_CONTENT IC
ON I.LOCATION_ID = IC.LOCATION_ID
AND I.ASSIGNED_UPC_CODE = IC.IACONTENT_ID
WHERE ms.SKU_ID = :SKU_ID
AND I.CAN_ASSIGN_SKU = 1
";
var binder = SqlBinder.Create(row => new AssignedLocation
{
LocationId = row.GetString("LOCATION_ID"),
RestockAisleId = row.GetString("RESTOCK_AISLE_ID"),
PiecesAtLocation = row.GetInteger("PIECES_AT_LOCATION") ?? 0,
RailCapacity = row.GetInteger("RAILCAPACITY"),
IaId = row.GetString("IA_ID"),
BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"),
AssignedVwhId = row.GetString("VWH_ID")
});
binder.Parameter("SKU_ID", skuId);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// returns Sewing Plant list for Sewing plant drop down list box
/// </summary>
/// <returns></returns>
public IEnumerable <SewingPlant> GetSewingPlants()
{
const string QUERY = @"
SELECT SP.SEWING_PLANT_CODE AS SEWING_PLANT_CODE,
SP.SEWING_PLANT_NAME AS SEWING_PLANT_NAME
-- SPPARENT.SEWING_PLANT_NAME AS PARENT_SEWING_PLANT_NAME,
-- TC.NAME AS COUNTRY_NAME
FROM <proxy />TAB_SEWINGPLANT SP
-- LEFT OUTER JOIN <proxy />TAB_SEWINGPLANT SPPARENT
--ON SPPARENT.SEWING_PLANT_CODE = SP.PARENT_SEWING_PLANT
-- LEFT OUTER JOIN <proxy />TAB_COUNTRY TC ON
-- TC.COUNTRY_ID= SP.CO_OF_ORIGIN
ORDER BY
--SPPARENT.SEWING_PLANT_NAME,
SP.SEWING_PLANT_CODE,
SP.SEWING_PLANT_NAME
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create(row => new SewingPlant()
{
SewingPlantCode = row.GetString("SEWING_PLANT_CODE"),
PlantName = row.GetString("SEWING_PLANT_NAME")
//GroupingColumn = row.GetString("PARENT_SEWING_PLANT_NAME"),
//CountryName=row.GetString("COUNTRY_NAME")
});
//++_queryCount;
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// This Method is Used For Carton Pallet List.
/// </summary>
/// <param name="PalletId"></param>
/// <returns></returns>
public IList <PalletHeadLine> GetPalletList(int maxRows)
{
const string QUERY = @"SELECT SC.PALLET_ID,
COUNT(SC.CARTON_ID) AS TOTAL_CARTON,
MAX(SC.AREA_CHANGE_DATE) AS MAX_AREA_CHANGE_DATE,
MIN(SC.AREA_CHANGE_DATE) AS MIN_AREA_CHANGE_DATE,
COUNT(DISTINCT SC.CARTON_STORAGE_AREA) AS CARTON_AREA_COUNT,
MAX(TIA.SHORT_NAME) AS AREA_SHORT_NAME,
MAX(TIA.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID
FROM <proxy />SRC_CARTON SC
LEFT OUTER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON SC.CARTON_STORAGE_AREA = TIA.INVENTORY_STORAGE_AREA
WHERE SC.PALLET_ID IS NOT NULL
GROUP BY SC.PALLET_ID
ORDER BY MIN(SC.AREA_CHANGE_DATE) DESC NULLS LAST";
var binder = SqlBinder.Create(row => new PalletHeadLine
{
PalletId = row.GetString("PALLET_ID"),
TotalCarton = row.GetInteger("TOTAL_CARTON"),
MaxAreaChangeDate = row.GetDate("MAX_AREA_CHANGE_DATE"),
MinAreaChangeDate = row.GetDate("MIN_AREA_CHANGE_DATE"),
CartonAreaCount = row.GetInteger("CARTON_AREA_COUNT").Value,
AreaShortName = row.GetString("AREA_SHORT_NAME"),
WarehouseLocationId = row.GetString("WAREHOUSE_LOCATION_ID")
});
return(_db.ExecuteReader(QUERY, binder, maxRows));
}
/// <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));
}
/// <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>
/// 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);
}
/// <summary>
/// Retrieves a pallet suggestion for passed appointment.
/// </summary>
/// <param name="appointmentNo"></param>
/// <returns></returns>
public ICollection <Pallet> GetSuggestedPallet(int appointmentNo)
{
//Assuming the appointment will be unique
const string QUERY = @"
DECLARE
LPALLET <proxy />BOX.PALLET_ID%TYPE;
Lappointment_id <proxy />Appointment.appointment_id%type;
BEGIN
SELECT A.APPOINTMENT_ID
INTO LAPPOINTMENT_ID
FROM <proxy />APPOINTMENT A
WHERE A.APPOINTMENT_NUMBER = :APPOINTMENT_NUMBER
AND EXISTS (SELECT 1
FROM <proxy />SHIP T
WHERE T.APPOINTMENT_ID = A.APPOINTMENT_ID
AND T.TRANSFER_DATE IS NULL);
LPALLET := <proxy />PKG_APPOINTMENT.SUGGEST_PALLET_TO_LOAD_2(AAPPOINTMENT_ID => LAPPOINTMENT_ID);
IF LPALLET IS NOT NULL THEN
OPEN :REF_CURSOR FOR
SELECT BOX.PALLET_ID AS PALLET_ID,
MAX(BOX.IA_ID) AS IA_ID,
MAX(BOX.LOCATION_ID) AS LOCATION_ID,
COUNT(DISTINCT BOX.UCC128_ID) AS BOX_COUNT
FROM <proxy />BOX BOX
LEFT OUTER JOIN <proxy />PS PS
ON PS.PICKSLIP_ID = BOX.PICKSLIP_ID
LEFT OUTER JOIN <proxy />SHIP SHIP
ON SHIP.SHIPPING_ID = PS.SHIPPING_ID
LEFT OUTER JOIN <proxy />APPOINTMENT MS
ON MS.APPOINTMENT_ID = SHIP.APPOINTMENT_ID
WHERE MS.APPOINTMENT_NUMBER = :APPOINTMENT_NUMBER
AND BOX.PALLET_ID = LPALLET
AND PS.TRANSFER_DATE IS NULL
GROUP BY BOX.PALLET_ID
ORDER BY MAX(SHIP.SHIPPING_ID);
END IF;
END;
";
var binder = SqlBinder.Create(row => new Pallet
{
PalletId = row.GetString("PALLET_ID"),
IaId = row.GetString("IA_ID"),
LocationId = row.GetString("LOCATION_ID"),
BoxesCount = row.GetInteger("BOX_COUNT")
})
.Parameter("APPOINTMENT_NUMBER", appointmentNo)
.OutRefCursorParameter("REF_CURSOR");
return(_db.ExecuteReader(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));
}
/// <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>
/// 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>
/// Returns all matching users who do not own any schema objects. Thus all users returned can potentially be deleted.
/// </summary>
/// <param name="usernameToMatch">This can contain the wildcard character %</param>
/// <param name="pageIndex">Not used</param>
/// <param name="pageSize">Not used</param>
/// <param name="totalRecords">Not used</param>
/// <returns></returns>
public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
{
EnsureDefaultProfile();
const string QUERY = @"SELECT U.USERNAME, U.USER_ID, U.LOCK_DATE, U.CREATED, U.EXPIRY_DATE
FROM DBA_USERS U
WHERE UPPER(U.USERNAME) LIKE :TERM
AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)
AND u.profile IN <a pre='(' sep=',' post=')'>:profile</a>
ORDER BY U.USERNAME";
var binder = SqlBinder.Create(src => new OracleMembershipUser(
userName: src.GetString("USERNAME"),
providerUserKey: src.GetInteger("USER_ID").ToString(),
lastLockoutDate: src.GetDate("LOCK_DATE") ?? DateTime.MinValue,
createDate: src.GetDate("created") ?? DateTime.MinValue,
passwordExpiryDate: src.GetDate("expiry_date") ?? DateTime.MinValue
));
binder.Parameter("TERM", usernameToMatch.ToUpper());
binder.ParameterXmlArray("profile", _visibleProfiles);
var result = new MembershipUserCollection();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, string.Empty);
var usersList = db.ExecuteReader(QUERY, binder);
foreach (var user in usersList)
{
result.Add(user);
}
}
totalRecords = result.Count;
return(result);
}
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>
/// Contains SKU related functions
/// </summary>
public IList <Tuple <string, string> > GetBolPrinters()
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT NAME AS NAME, DESCRIPTION AS DESCRIPTION
FROM <proxy />TAB_PRINTER
WHERE UPPER(PRINTER_TYPE) IN
(SELECT UPPER(PRINTER_TYPE)
FROM <proxy />DOC
WHERE DOCUMENT_ID IN ('$BOL', '$MBOL'))
ORDER BY NAME ASC
";
var binder = SqlBinder.Create(row => Tuple.Create(row.GetString("NAME"), row.GetString("DESCRIPTION")));
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Results are sorted by Customer Name, ReceiveDate desc
/// </summary>
/// <param name="returnNumber"></param>
/// <param name="maxRows"></param>
/// <returns></returns>
public IList <ReturnReceiptDetail> GetReturnInfo(string returnNumber, int maxRows)
{
const string QUERY = @"
SELECT RET.RECEIPT_NUMBER AS RECEIPT_NUMBER,
RET.RECEIVED_DATE AS RECEIVED_DATE,
CUST.CUSTOMER_ID AS CUSTOMER_ID,
CUST.NAME AS CUSTOMER_NAME,
RET.CUSTOMER_STORE_ID AS CUSTOMER_STORE_ID,
TR.REASON_CODE AS REASON_CODE,
TR.DESCRIPTION AS REASON_CODE_DESCRIPTION,
RET.IS_COMPLETE_RETURN AS IS_COMPLETE_RETURN,
RET.ACTIVITY_ID AS ACTIVITY_ID,
RET.NO_OF_CARTONS AS NO_OF_CARTONS,
RET.EXPECTED_PIECES AS EXPECTED_PIECES,
COUNT(*) OVER() AS TOTAL_RECEIPT
FROM <proxy />DEM_RETURNS RET
LEFT OUTER JOIN <proxy />CUST CUST
ON RET.CUSTOMER_ID = CUST.CUSTOMER_ID
LEFT OUTER JOIN <proxy />TAB_REASON TR
ON RET.REASON_CODE = TR.REASON_CODE
WHERE RET.RETURNS_AUTHORIZATION_NUMBER = :RETURNS_AUTHORIZATION_NUMBER
ORDER BY CUST.NAME, RET.RECEIVED_DATE DESC
";
var binder = SqlBinder.Create(row => new ReturnReceiptDetail
{
ReceiptNumber = row.GetString("RECEIPT_NUMBER"),
ReceivedDate = row.GetDate("RECEIVED_DATE").Value,
ReasonCode = row.GetString("REASON_CODE"),
ReasonDescription = row.GetString("REASON_CODE_DESCRIPTION"),
IsCompleteReceipt = !string.IsNullOrWhiteSpace(row.GetString("IS_COMPLETE_RETURN")),
ActivityId = row.GetInteger("ACTIVITY_ID"),
TotalReceipts = row.GetInteger("TOTAL_RECEIPT").Value,
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("CUSTOMER_NAME"),
CustomerStoreId = row.GetString("CUSTOMER_STORE_ID"),
NoOfCartons = row.GetInteger("NO_OF_CARTONS"),
ExpectedPieces = row.GetInteger("EXPECTED_PIECES")
}).Parameter("RETURNS_AUTHORIZATION_NUMBER", returnNumber);
return(_db.ExecuteReader(QUERY, binder, maxRows));
}
public IList <Tuple <string, string> > 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 ROWNUM < 40 and SUBSTR(UPPER(CUST.CUSTOMER_ID), 1, 1) != '$'
ORDER BY CUST.CUSTOMER_ID
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create(row => Tuple.Create(row.GetString("CUSTOMER_ID"), row.GetString("CUSTOMER_NAME")))
.Parameter("TERM", term);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
///PROVIDE INFORMATION ABOUT PALLETS IN LOCATION
/// </summary>
/// <param name="locationId"></param>
/// <returns></returns>
// [Obsolete]
// public IList<CartonAreaInventory> GetCartonsOfLocationOnPallet(string locationId)
// {
// const string QUERY = @" SELECT COUNT(S.CARTON_ID) AS CARTON_COUNT,
// S.PALLET_ID AS PALLET_ID,
// SUM(SD.QUANTITY) AS PIECES,
// COUNT(DISTINCT SD.SKU_ID) AS SKU_COUNT
// FROM <proxy />SRC_CARTON S
// INNER JOIN <proxy />SRC_CARTON_DETAIL SD
// ON S.CARTON_ID = SD.CARTON_ID
// WHERE S.LOCATION_ID = :LOCATION_ID
// AND S.PALLET_ID IS NOT NULL
// GROUP BY S.PALLET_ID";
// var binder = SqlBinder.Create(row => new CartonAreaInventory
// {
// CartonCount = row.GetInteger("CARTON_COUNT"),
// PalletId = row.GetString("PALLET_ID"),
// SKUQuantity = row.GetInteger("PIECES"),
// DistinctSKUs = row.GetInteger("SKU_COUNT")
// }).Parameter("LOCATION_ID", locationId);
// return _db.ExecuteReader(QUERY, binder);
// }
/// <summary>
///PROVIDE INFORMATION ABOUT CARTONS NOT ON PALLETS IN LOCATION
/// </summary>
/// <param name="locationId"></param>
/// <returns></returns>
public IList <CartonAtLocation> GetCartonsAtLocation(string locationId)
{
const string QUERY = @"
SELECT S.CARTON_ID AS CARTON_ID,
MAX(S.PALLET_ID) AS PALLET_ID,
SUM(SD.QUANTITY) AS PIECES
FROM <proxy />SRC_CARTON S
INNER JOIN <proxy />SRC_CARTON_DETAIL SD
ON S.CARTON_ID = SD.CARTON_ID
WHERE S.LOCATION_ID = :LOCATION_ID
GROUP BY S.CARTON_ID
";
var binder = SqlBinder.Create(row => new CartonAtLocation
{
CartonId = row.GetString("CARTON_ID"),
SKUQuantity = row.GetInteger("PIECES"),
//DistinctSKUs = row.GetInteger("SKU_COUNT"),
PalletId = row.GetString("PALLET_ID")
}).Parameter("LOCATION_ID", locationId);
return(_db.ExecuteReader(QUERY, binder, 1000));
}
/// <summary>
/// Returns all possible pallets which can be picked
/// </summary>
/// <returns></returns>
public IEnumerable <Activity> GetPendingActivity()
{
var QUERY = @"
SELECT PS.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
BKT.SHIP_IA_ID AS SHIP_IA_ID,
BKT.PICK_MODE AS PICK_MODE,
COUNT(DISTINCT B.PALLET_ID) AS COUNT_PALLETS,
COUNT(DISTINCT B.UCC128_ID) AS PICKABLE_BOX_COUNT,
MAX(IA.SHORT_NAME) AS SHORT_NAME
FROM <proxy />BOX B
INNER JOIN <proxy />PS PS
ON PS.PICKSLIP_ID = B.PICKSLIP_ID
INNER JOIN <proxy />BUCKET BKT
ON PS.BUCKET_ID = BKT.BUCKET_ID
INNER JOIN <proxy />IA IA
ON IA.IA_ID = BKT.SHIP_IA_ID
WHERE B.STOP_PROCESS_DATE IS NULL
AND B.PALLET_ID IS NOT NULL
AND B.STOP_PROCESS_DATE IS NULL
AND B.IA_ID IS NULL
AND BKT.AVAILABLE_FOR_PITCHING = 'Y'
AND BKT.PICK_MODE IN ('PITCHING', 'ADRE', 'ADREPPWSS')
AND BKT.FREEZE IS NULL
AND BKT.PULL_TO_DOCK = 'Y'
GROUP BY PS.WAREHOUSE_LOCATION_ID, BKT.PICK_MODE, SHIP_IA_ID
ORDER BY PS.WAREHOUSE_LOCATION_ID, SHIP_IA_ID, BKT.PICK_MODE
";
var binder = SqlBinder.Create(row => new Activity
{
PickableBoxCount = row.GetInteger("PICKABLE_BOX_COUNT").Value,
PickModeText = row.GetString("PICK_MODE"),
BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"),
DestinationArea = row.GetString("SHIP_IA_ID"),
AreaShortName = row.GetString("SHORT_NAME"),
CountPallets = row.GetInteger("COUNT_PALLETS").Value
});
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));
}
/// <summary>
/// Following function will return all users that are assigned with the passed role.
/// </summary>
/// <param name="roleName"></param>
/// <returns>Array of roles</returns>
/// <remarks>
/// Users who own schema objects are not returned by this function.
/// </remarks>
public override string[] GetUsersInRole(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
/* Oracle 11gR2 hierarchical query
* Inspired by http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
*/
const string ROLE_USERS = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT DISTINCT NVL(SUBSTR(Q.PATH, 1, INSTR(Q.PATH, '/', 1 , 1) - 1), Q.PATH) AS USERS
FROM Q1 Q
WHERE Q.GRANTED_ROLE = :roleName
AND NVL(SUBSTR(Q.PATH, 1, INSTR(Q.PATH, '/', 1 , 1) - 1), Q.PATH) not in
(SELECT OWNER FROM DBA_OBJECTS)
ORDER BY USERS";
//var binder = new SqlBinder<string>("Retreving users.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(ROLE_USERS);
//binder.Query = ROLE_USERS;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
var roles = db.ExecuteReader(ROLE_USERS, binder);
var usersInRole = roles.ToArray();
return(usersInRole);
}
}
/// <summary>
/// Returns the highest priority bucket which has most boxes for expediting
/// </summary>
/// <returns>The best bucket id</returns>
public IList <BucketBase> GetExpeditableBuckets(int maxRows)
{
const string QUERY = @"
SELECT BUCKET.BUCKET_ID AS BUCKET_ID,
max(BUCKET.NAME) AS BUCKET_NAME,
max(BUCKET.BUCKET_COMMENT) AS BUCKET_COMMENT,
max(BUCKET.FREEZE) AS FREEZE,
max(BUCKET.PRIORITY) AS PRIORITY ,
max(BUCKET.PITCH_LIMIT) AS PITCH_LIMIT,
NULL AS QUICK_PITCH_FLAG_O, /* max(BUCKET.QUICK_PITCH_FLAG_O) Obsolete*/
max( BUCKET.CREATED_BY) AS CREATED_BY,
max( BUCKET.DATE_CREATED) AS DATE_CREATED,
MAX(BUCKET.PULL_TYPE) AS PULL_TYPE
FROM <proxy />BUCKET BUCKET
INNER JOIN <proxy />PS P
ON P.BUCKET_ID = BUCKET.BUCKET_ID
INNER JOIN <proxy />BOX B
ON P.PICKSLIP_ID = B.PICKSLIP_ID
WHERE B.IA_ID IS NULL
--AND B.STOP_PROCESS_DATE IS NULL
AND B.PALLET_ID IS NULL
AND BUCKET.PULL_TYPE = 'EXP'
AND P.TRANSFER_DATE IS NULL
AND BUCKET.FREEZE IS NULL and b.carton_id is not null
GROUP BY BUCKET.BUCKET_ID
ORDER BY MAX(BUCKET.PRIORITY) DESC,
COUNT(P.PICKSLIP_ID) DESC ";
var binder = SqlBinder.Create(row =>
new BucketBase
{
BucketId = row.GetInteger("BUCKET_ID") ?? 0,
BucketName = row.GetString("BUCKET_NAME"),
BucketComment = row.GetString("BUCKET_COMMENT"),
IsFrozen = row.GetString("FREEZE") == "Y",
CreatedBy = row.GetString("CREATED_BY"),
CreationDate = row.GetDate("DATE_CREATED").Value,
PriorityId = row.GetInteger("PRIORITY").Value,
RequireBoxExpediting = row.GetString("PULL_TYPE") == "EXP",
QuickPitch = row.GetString("QUICK_PITCH_FLAG_O") == "Y",
PitchLimit = row.GetInteger("PITCH_LIMIT")
}
);
return(_db.ExecuteReader(QUERY, binder, maxRows));
}
private static IList <RcUserApprovalStatus> GetApprovalStatus(string reportId, string version)
{
IList <RcUserApprovalStatus> approvers;
const string QUERY = @"
Select user_name,
<if c='$version'>
case when report_version =:version then approval_status end
</if>
<else>
NULL
</else>
as approval_status,
<if c='$version'>
case when report_version =:version then comments end
</if>
<else>
NULL
</else>
as comments,
approval_status_date
from dcmslive_user_report
where report_id = :report_id and is_approver = 'Y'
";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(ConfigurationManager.ConnectionStrings["dcmslive"].ConnectionString, "");
var binder = SqlBinder.Create(row => new RcUserApprovalStatus
{
UserId = row.GetString("user_name"),
DbStatus = row.GetString("approval_status"),
UserComment = row.GetString("comments"),
StatusDate = row.GetDate("approval_status_date")
});
binder.Parameter("report_id", reportId);
binder.Parameter("version", version);
approvers = db.ExecuteReader(QUERY, binder);
}
return(approvers);
}
/// <summary>
/// Gets all carton areas if you do not pass any flag.
/// If you pass a flag gets area according to passed flag.
/// </summary>
/// <param name="areaId"></param>
/// <param name="shortName"> </param>
/// <param name="buildingId"> </param>
/// <returns></returns>
public IEnumerable <CartonArea> GetCartonAreas(string areaId, string shortName, string buildingId)
{
const string QUERY =
@"
SELECT TIA.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA,
TIA.DESCRIPTION AS DESCRIPTION,
TIA.WAREHOUSE_LOCATION_ID AS BUILDING,
TWL.RECEIVING_PALLET_LIMIT AS RECEIVING_PALLET_LIMIT,
TIA.LOCATION_NUMBERING_FLAG AS LOCATION_NUMBERING_FLAG,
TIA.SHORT_NAME AS SHORT_NAME
FROM <proxy />TAB_INVENTORY_AREA TIA
LEFT OUTER JOIN <proxy />TAB_WAREHOUSE_LOCATION TWL
ON TWL.WAREHOUSE_LOCATION_ID = TIA.WAREHOUSE_LOCATION_ID
WHERE TIA.STORES_WHAT = 'CTN'
<if>
AND TIA.INVENTORY_STORAGE_AREA = :INVENTORY_STORAGE_AREA
</if>
<if>
AND TIA.SHORT_NAME = :SHORT_NAME
</if>
<if>
AND TIA.WAREHOUSE_LOCATION_ID = :WAREHOUSE_LOCATION_ID
</if>
ORDER BY TIA.INVENTORY_STORAGE_AREA
";
var binder = SqlBinder.Create(row => new CartonArea
{
AreaId = row.GetString("INVENTORY_STORAGE_AREA"),
Description = row.GetString("DESCRIPTION"),
Building = row.GetString("BUILDING"),
PalletLimit = row.GetInteger("RECEIVING_PALLET_LIMIT"),
ShortName = row.GetString("SHORT_NAME"),
IsNumberedLocationArea = row.GetString("LOCATION_NUMBERING_FLAG") == "Y"
}).Parameter("INVENTORY_STORAGE_AREA", areaId).Parameter("SHORT_NAME", shortName).Parameter("WAREHOUSE_LOCATION_ID", buildingId);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// Returns all information of the passed pallet or cartons.
/// </summary>
/// <param name="palletId"></param>
/// <param name="cartonList"></param>
/// <returns></returns>
internal IEnumerable <Carton> GetCartonsOfPallet(string palletId, IList <string> cartonList)
{
if (string.IsNullOrWhiteSpace(palletId) && cartonList == null)
{
throw new ArgumentNullException("palletId / cartonList");
}
const string QUERY = @"
SELECT S.CARTON_ID AS CARTON_ID,
S.WORK_NEEDED_XML AS WORK_NEEDED_XML
FROM <proxy />SRC_CARTON S
WHERE 1= 1
<if> AND S.PALLET_ID = :PALLET_ID </if>
<else><a pre='AND S.CARTON_ID IN (' sep=',' post=')'>:CARTON_ID_LIST</a></else>
";
var binder = SqlBinder.Create(row => new Carton()
{
CartonId = row.GetString("CARTON_ID"),
RemarkWorkNeeded = string.IsNullOrWhiteSpace(row.GetString("WORK_NEEDED_XML")) ? false : true
}).Parameter("PALLET_ID", palletId);
binder.ParameterXmlArray("CARTON_ID_LIST", cartonList);
return(_db.ExecuteReader(QUERY, binder));
}
/// <summary>
/// returns the inventory of the passed pickslip
/// </summary>
/// <param name="pickslipId"></param>
/// <returns></returns>
public IList <PickslipSku> GetPickslipSku(long pickslipId)
{
Contract.Assert(_db != null);
const string QUERY_PICKSLIP_INVENTORY = @"
SELECT
PSD.SKU_ID AS SKU_ID,
MSKU.STYLE AS STYLE,
MSKU.COLOR AS COLOR,
MSKU.DIMENSION AS DIMENSION,
MSKU.SKU_SIZE AS SKU_SIZE,
PS.VWH_ID AS VWH_ID,
PSD.PIECES_ORDERED AS PIECES_ORDERED,
PSD.QUALITY_CODE AS QUALITY_CODE,
(MSKU.RETAIL_PRICE * PSD.PIECES_ORDERED) AS RETAIL_PRICE,
PSD.MIN_PIECES_PER_BOX AS MIN_PIECES_PER_BOX,
PSD.MAX_PIECES_PER_BOX AS MAX_PIECES_PER_BOX,
PSD.PIECES_PER_PACKAGE AS PIECES_PER_PACKAGE
FROM <proxy />PSDET PSD
INNER JOIN <proxy />MASTER_SKU MSKU
ON MSKU.UPC_CODE = PSD.UPC_CODE
INNER JOIN <proxy />PS PS
ON PS.PICKSLIP_ID=PSD.PICKSLIP_ID
WHERE PSD.PICKSLIP_ID = :PICKSLIP_ID
ORDER BY STYLE, COLOR, DIMENSION, SKU_SIZE
";
var binder = SqlBinder.Create(row => new PickslipSku
{
Style = row.GetString("STYLE"),
Color = row.GetString("COLOR"),
Dimension = row.GetString("DIMENSION"),
SkuSize = row.GetString("SKU_SIZE"),
SkuId = row.GetInteger("SKU_ID").Value,
Pieces = row.GetInteger("PIECES_ORDERED"),
QualityCode = row.GetString("QUALITY_CODE"),
VwhId = row.GetString("VWH_ID"),
RetailPrice = row.GetDecimal("RETAIL_PRICE"),
PiecesPerPackage = row.GetInteger("PIECES_PER_PACKAGE").Value,
MaxPiecesPerBox = row.GetInteger("MAX_PIECES_PER_BOX"),
MinPiecesPerBox = row.GetInteger("MIN_PIECES_PER_BOX")
}).Parameter("PICKSLIP_ID", pickslipId);
return(_db.ExecuteReader(QUERY_PICKSLIP_INVENTORY, binder));
}
public IList <Epc> GetBoxEpc(string uccId)
{
Contract.Assert(_db != null);
const string QUERY_BOX_SKU_EPC = @"
SELECT bd.SKU_ID AS SKU_ID,
BDEPC.EPC AS EPC
FROM <proxy />BOXDET BD
LEFT OUTER JOIN <proxy />BOXDET_EPC BDEPC
ON BDEPC.BOXDET_ID = BD.BOXDET_ID
WHERE BD.UCC128_ID = :aucc_id
AND BDEPC.EPC IS NOT NULL
";
var binder = SqlBinder.Create(row => new Epc
{
EpcCode = row.GetString("EPC"),
SkuId = row.GetInteger("SKU_ID").Value
}).Parameter("aucc_id", uccId);
return(_db.ExecuteReader(QUERY_BOX_SKU_EPC, binder));
}
/// <summary>
/// This method return session log of user
/// </summary>
/// <param name="username"></param>
/// <param name="db"></param>
/// <returns>
/// The returned info contains session log of the user as well.
/// </returns>
private IEnumerable<OracleMembershipUserSession> DoGetUserSessions(string username, OracleDatastore db)
{
const string QUERY_SESSIONS = @"
SELECT S.SID,
S.SERIAL#,
S.PROGRAM,
S.STATUS,
S.OSUSER,
S.MACHINE,
S.MODULE,
S.ACTION,
S.CLIENT_INFO,
S.LOGON_TIME,
S.STATE
FROM GV$SESSION S
WHERE S.TYPE = 'USER'
AND S.USERNAME IS NOT NULL
AND S.USERNAME = :USERNAME
ORDER BY S.USERNAME DESC
";
//var binderSession = new SqlBinder<OracleMembershipUserSession>("Querying User properties");
var binderSession = SqlBinder.Create(row => new OracleMembershipUserSession
{
SessionId = row.GetInteger("SID").Value,
SerialNumber = row.GetInteger("SERIAL#").Value,
OsExecutableName = row.GetString("PROGRAM"),
IsActive = row.GetString("STATUS") == "ACTIVE",
OsUserName = row.GetString("OSUSER"),
MachineName = row.GetString("MACHINE"),
Module = row.GetString("MODULE"),
ActionName = row.GetString("ACTION"),
ClientInfo = row.GetString("CLIENT_INFO"),
LogonTime = row.GetDate("LOGON_TIME").Value,
State = row.GetString("STATE")
});
binderSession.Parameter("USERNAME", username.ToUpper());
//binderSession.Query = QUERY_SESSIONS;
//binderSession.Factory = row => new OracleMembershipUserSession
//{
// SessionId = row.GetInteger("SID").Value,
// SerialNumber = row.GetInteger("SERIAL#").Value,
// OsExecutableName = row.GetString("PROGRAM"),
// IsActive = row.GetString("STATUS") == "ACTIVE",
// OsUserName = row.GetString("OSUSER"),
// MachineName = row.GetString("MACHINE"),
// Module = row.GetString("MODULE"),
// ActionName = row.GetString("ACTION"),
// ClientInfo = row.GetString("CLIENT_INFO"),
// LogonTime = row.GetDate("LOGON_TIME").Value,
// State = row.GetString("STATE")
//};
//binderSession.CreateMapper(QUERY_SESSIONS, config => config.CreateMap<OracleMembershipUserSession>()
// .MapField("SID", p => p.SessionId)
// .MapField("SERIAL#", p => p.SerialNumber)
// .MapField("PROGRAM", p => p.OsExecutableName)
// .ForMember(dest => dest.IsActive, opt => opt.MapFrom(src => src.GetValue<string>("STATUS") == "ACTIVE"))
// .MapField("OSUSER", p => p.OsUserName)
// .MapField("MACHINE", p => p.MachineName)
// .MapField("MODULE", p => p.Module)
// .MapField("ACTION", p => p.ActionName)
// .MapField("CLIENT_INFO", p => p.ClientInfo)
// .MapField("LOGON_TIME", p => p.LogonTime)
// .MapField("STATE", p => p.State)
//);
return db.ExecuteReader(QUERY_SESSIONS, binderSession);
}
/// <summary>
/// This method return audit log of user
/// </summary>
/// <param name="username"></param>
/// <param name="db"></param>
/// <returns>
/// The returned info contains audit log of the user as well.
/// </returns>
private static IList<OracleMembershipUserAudit> DoGetUserAudit(string username, OracleDatastore db)
{
const string QUERY_AUDIT = @"
(SELECT T.ACTION_NAME AS ACTION_NAME,
NULL AS ROLE_NAME,
T.USERNAME AS USERNAME,
T.OS_USERNAME AS OS_USERNAME,
T.TERMINAL AS TERMINAL,
DECODE(T.RETURNCODE, 0, 'SUCCESS', 'FAILURE') AS RESULT,
T.TIMESTAMP AS TIMESTAMP
FROM DBA_AUDIT_TRAIL T
WHERE T.ACTION_NAME IN ('CREATE USER', 'DROP USER', 'ALTER USER')
AND T.OBJ_NAME = :USERNAME
UNION ALL
SELECT T.ACTION_NAME,
T.OBJ_NAME,
T.USERNAME,
T.OS_USERNAME,
T.TERMINAL,
DECODE(T.RETURNCODE, 0, 'SUCCESS', 'FAILURE') AS RESULT,
T.TIMESTAMP
FROM DBA_AUDIT_TRAIL T
WHERE T.ACTION_NAME IN ('GRANT ROLE', 'REVOKE ROLE')
AND T.GRANTEE = :USERNAME
UNION ALL
SELECT 'GRANT DCMS PRIVILEGE',
PRIV_ID,
NVL(MODIFIED_BY, CREATED_BY),
NULL,
NULL,
'SUCCESS',
NVL(DATE_MODIFIED, DATE_CREATED)
FROM UPRIV
WHERE UPRIV.ORACLE_USER_NAME = :USERNAME)
ORDER BY TIMESTAMP DESC";
//var binderAudit = new SqlBinder<OracleMembershipUserAudit>("Querying User properties");
var binderAudit = SqlBinder.Create(row => new OracleMembershipUserAudit
{
ActionName = row.GetString("ACTION_NAME"),
RoleName = row.GetString("ROLE_NAME"),
Result = row.GetString("RESULT"),
ByOsUserName = row.GetString("OS_USERNAME"),
TerminalName = row.GetString("TERMINAL"),
ActionTime = row.GetDate("TIMESTAMP").Value,
ByOracleUserName = row.GetString("USERNAME"),
});
binderAudit.Parameter("USERNAME", username.ToUpper());
//binderAudit.Query = QUERY_AUDIT;
//binderAudit.Factory = row => new OracleMembershipUserAudit
//{
// ActionName = row.GetString("ACTION_NAME"),
// RoleName = row.GetString("ROLE_NAME"),
// Result = row.GetString("RESULT"),
// ByOsUserName = row.GetString("OS_USERNAME"),
// TerminalName = row.GetString("TERMINAL"),
// ActionTime = row.GetDate("TIMESTAMP").Value,
// ByOracleUserName = row.GetString("USERNAME"),
//};
//binderAudit.CreateMapper(QUERY_AUDIT, config => config.CreateMap<OracleMembershipUserAudit>()
// .MapField("ACTION_NAME", p => p.ActionName)
// .MapField("ROLE_NAME", p => p.RoleName)
// .MapField("RESULT", p => p.Result)
// .MapField("OS_USERNAME", p => p.ByOsUserName)
// .MapField("TERMINAL", p => p.TerminalName)
// .MapField("TIMESTAMP", p => p.ActionTime)
// .MapField("USERNAME", p => p.ByOracleUserName)
//);
return db.ExecuteReader(QUERY_AUDIT, binderAudit);
}
/// <summary>
/// Returns all matching users who do not own any schema objects. Thus all users returned can potentially be deleted.
/// </summary>
/// <param name="usernameToMatch">This can contain the wildcard character %</param>
/// <param name="pageIndex">Not used</param>
/// <param name="pageSize">Not used</param>
/// <param name="totalRecords">Not used</param>
/// <returns></returns>
public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
{
EnsureDefaultProfile();
const string QUERY = @"SELECT U.USERNAME, U.USER_ID, U.LOCK_DATE, U.CREATED, U.EXPIRY_DATE
FROM DBA_USERS U
WHERE UPPER(U.USERNAME) LIKE :TERM
AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)
AND u.profile IN <a pre='(' sep=',' post=')'>:profile</a>
ORDER BY U.USERNAME";
var binder = SqlBinder.Create(src => new OracleMembershipUser(
userName: src.GetString("USERNAME"),
providerUserKey: src.GetInteger("USER_ID").ToString(),
lastLockoutDate: src.GetDate("LOCK_DATE") ?? DateTime.MinValue,
createDate: src.GetDate("created") ?? DateTime.MinValue,
passwordExpiryDate: src.GetDate("expiry_date") ?? DateTime.MinValue
));
binder.Parameter("TERM", usernameToMatch.ToUpper());
binder.ParameterXmlArray("profile", _visibleProfiles);
var result = new MembershipUserCollection();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, string.Empty);
var usersList = db.ExecuteReader(QUERY, binder);
foreach (var user in usersList)
{
result.Add(user);
}
}
totalRecords = result.Count;
return result;
}
/// <summary>
/// Following function will return all users that are assigned with the passed role.
/// </summary>
/// <param name="roleName"></param>
/// <returns>Array of roles</returns>
/// <remarks>
/// Users who own schema objects are not returned by this function.
/// </remarks>
public override string[] GetUsersInRole(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
/* Oracle 11gR2 hierarchical query
* Inspired by http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
*/
const string ROLE_USERS = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT DISTINCT NVL(SUBSTR(Q.PATH, 1, INSTR(Q.PATH, '/', 1 , 1) - 1), Q.PATH) AS USERS
FROM Q1 Q
WHERE Q.GRANTED_ROLE = :roleName
AND NVL(SUBSTR(Q.PATH, 1, INSTR(Q.PATH, '/', 1 , 1) - 1), Q.PATH) not in
(SELECT OWNER FROM DBA_OBJECTS)
ORDER BY USERS";
//var binder = new SqlBinder<string>("Retreving users.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(ROLE_USERS);
//binder.Query = ROLE_USERS;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
var roles = db.ExecuteReader(ROLE_USERS, binder);
var usersInRole = roles.ToArray();
return usersInRole;
}
}
/// <summary>
/// Gives the recent process being worked on.
/// </summary>
/// DB: Removed the restriction that data of last 7 days will only be shown.
/// Now we show recent 20 rows. If existing process id is passed we show that process.
/// <returns></returns>
public IList <ReceivingProcess> GetProcesses(int?processId)
{
const string QUERY = @"
SELECT MAX(SCP.PROCESS_ID) AS PROCESS_ID,
MAX(SCP.OPERATOR_NAME) AS OPERATOR_NAME,
MAX(SCP.PRO_NUMBER) AS PRO_NUMBER,
MAX(SCP.PRO_DATE) AS PRO_DATE,
MAX(SCP.PALLET_LIMIT) AS PALLET_LIMIT,
MAX(SCP.CARRIER_ID) AS CARRIER_ID,
MAX(SCP.PRICE_SEASON_CODE) AS PRICE_SEASON_CODE,
MAX(MC.DESCRIPTION) AS DESCRIPTION,
MAX(SCP.EXPECTED_CARTON) AS EXPECTED_CARTON,
MAX(SCP.PROCESS_START_DATE) AS START_DATE,
MAX(SCP.PROCESS_END_DATE) AS RECEIVING_END_DATE,
COUNT(DISTINCT SC.CARTON_ID) +
(SELECT COUNT(*)
FROM <proxy />SRC_OPEN_CARTON
WHERE INSHIPMENT_ID = SCP.PROCESS_ID) AS CARTON_COUNT,
COUNT(DISTINCT SC.PALLET_ID) AS PALLET_COUNT,
MAX(SCP.RECEIVING_AREA_ID) AS RECEIVING_AREA_ID,
MAX(SCP.SPOT_CHECK_AREA_ID) AS SPOT_CHECK_AREA_ID
FROM <proxy />SRC_CARTON_PROCESS SCP
LEFT OUTER JOIN <proxy />MASTER_CARRIER MC
ON SCP.CARRIER_ID = MC.CARRIER_ID
LEFT OUTER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON SCP.AREA_ID_TO_CHECK = TIA.INVENTORY_STORAGE_AREA
LEFT OUTER JOIN <proxy />SRC_CARTON SC
ON SC.INSHIPMENT_ID = SCP.PROCESS_ID
WHERE SCP.MODULE_CODE = :AMODULE_CODE
AND SCP.RECEIVING_AREA_ID IS NOT NULL
<if>
AND SCP.PROCESS_ID = :PROCESS_ID
</if>
GROUP BY SCP.PROCESS_ID
ORDER BY MAX(NVL(SCP.PROCESS_START_DATE, SCP.INSERT_DATE)) DESC NULLS LAST,
SCP.PROCESS_ID
FETCH FIRST 50 ROWS ONLY";
var binder = SqlBinder.Create(row => new ReceivingProcess
{
CarrierId = row.GetString("CARRIER_ID"),
CarrierName = row.GetString("DESCRIPTION"),
ProcessId = row.GetInteger("PROCESS_ID").Value,
OperatorName = row.GetString("OPERATOR_NAME"),
ProNumber = row.GetString("PRO_NUMBER"),
ProDate = row.GetDate("PRO_DATE"),
PalletLimit = row.GetInteger("PALLET_LIMIT"),
PalletCount = row.GetInteger("PALLET_COUNT").Value,
CartonCount = row.GetInteger("CARTON_COUNT").Value,
ExpectedCartons = row.GetInteger("EXPECTED_CARTON"),
PriceSeasonCode = row.GetString("PRICE_SEASON_CODE"),
ReceivingAreaId = row.GetString("RECEIVING_AREA_ID"),
SpotCheckAreaId = row.GetString("SPOT_CHECK_AREA_ID"),
StartDate = row.GetDate("start_date"),
ReceivingEndDate = row.GetDate("RECEIVING_END_DATE")
}).Parameter("AMODULE_CODE", MODULE_NAME)
.Parameter("PROCESS_ID", processId);
//++_queryCount;
return(_db.ExecuteReader(QUERY, binder));
}
internal IEnumerable <SkuRequirement> GetSkuRequirements(int?skuId)
{
const string QUERY = @"
SELECT MS.SKU_ID AS SKU_ID,
MS.STYLE AS STYLE,
MS.COLOR AS COLOR,
MS.DIMENSION AS DIMENSION,
MS.SKU_SIZE AS SKU_SIZE,
MS.UPC_CODE AS UPC_CODE,
L.VWH_ID AS VWH_ID,
NVL(I.WAREHOUSE_LOCATION_ID, L.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
L.IA_ID AS PICK_IA_ID,
I.SHORT_NAME AS SHORT_NAME,
L.RESTOCK_IA_ID AS RESTOCK_AREA_ID,
I.Default_Repreq_Ia_Id AS CARTON_AREA_ID,
L.LOCATION_ID AS LOCATION_ID,
L.RESTOCK_AISLE_ID AS RESTOCK_AISLE_ID,
C.NUMBER_OF_UNITS AS PIECES_AT_LOCATIONS,
L.ASSIGNED_UPC_MAX_PIECES AS PIECES_CAPACITY,
L.ASSIGNED_UPC_MAX_PIECES - NVL(C.NUMBER_OF_UNITS, 0) AS PIECES_REQUIRED,
L.LOCATION_TYPE AS LOCATION_TYPE,
L.FREEZE_FLAG AS FREEZE_FLAG
FROM <proxy/>MASTER_SKU MS
LEFT OUTER JOIN <proxy/>IALOC L
ON MS.UPC_CODE = L.ASSIGNED_UPC_CODE AND L.ASSIGNED_UPC_CODE IS NOT NULL
AND L.CAN_ASSIGN_SKU = 1
LEFT OUTER JOIN <proxy/>IALOC_CONTENT C
ON L.LOCATION_ID = C.LOCATION_ID
AND L.IA_ID = C.IA_ID
AND C.IACONTENT_ID = L.ASSIGNED_UPC_CODE
AND C.IACONTENT_TYPE_ID = 'SKU'
LEFT OUTER JOIN <proxy/>IA I
ON I.IA_ID = L.IA_ID
WHERE MS.SKU_ID = :SKU_ID
";
var binder = SqlBinder.Create(row => new SkuRequirement
{
RestockAisleId = row.GetString("RESTOCK_AISLE_ID"),
LocationId = row.GetString("LOCATION_ID"),
BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"),
PickAreaId = row.GetString("PICK_IA_ID"),
ShortName = row.GetString("SHORT_NAME"),
RestockAreaId = row.GetString("RESTOCK_AREA_ID"),
ReplenishAreaId = row.GetString("CARTON_AREA_ID"),
LocationType = row.GetString("LOCATION_TYPE"),
IsFrozen = row.GetString("FREEZE_FLAG") == "Y",
LocationCapacity = row.GetInteger("PIECES_CAPACITY"),
PiecesAtLocation = row.GetInteger("PIECES_AT_LOCATIONS") ?? 0,
PiecesRequiredAtLocation = row.GetInteger("PIECES_REQUIRED") ?? 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")
}
})
.Parameter("SKU_ID", skuId);
return(_db.ExecuteReader(QUERY, binder));
}
protected void viewHomePage_PreRender(object sender, EventArgs e)
{
IEnumerable <RcReport> listRc;
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
const string QUERY = @"
select t.report_id as report_id, t.user_name as user_name, t.report_version as report_version,
t.approval_status_date as approval_status_date,
<if c='$version'>
case when <a pre=""t.report_version IN ("" sep="","" post="")"">(:version)</a> then approval_status end
</if>
<else>
NULL
</else>
as approval_status,
t.comments as comments
from DCMSLIVE_USER_REPORT t
where <a pre=""t.report_id IN ("" sep="","" post="")"">(:report_id)</a>
";
db.CreateConnection(ConfigurationManager.ConnectionStrings["dcmslive"].ConnectionString, HttpContext.Current.User.Identity.Name);
var binder = SqlBinder.Create(row => new
{
ReportId = row.GetString("report_id"),
RcApprover = new RcUserApprovalStatus
{
DbStatus = row.GetString("approval_status"),
StatusDate = row.GetDate("approval_status_date"),
UserId = row.GetString("user_name"),
UserComment = row.GetString("comments")
},
VersionNumber = row.GetString("report_version"),
});
binder.ParameterXmlArray("report_id", SiteMap.Providers["Rc"].RootNode.ChildNodes.Cast <SiteMapNode>().Select(p => p.Key).ToArray());
binder.ParameterXmlArray("version", SiteMap.Providers["Rc"].RootNode.ChildNodes.Cast <SiteMapNode>().Select(p => p["version"]).ToArray());
var result = db.ExecuteReader(QUERY, binder);
// LinqQuery generates an entry for each RC report. Each RC report contains a list of approval statuses
listRc = from flatData in
(from SiteMapNode node in SiteMap.Providers["Rc"].RootNode.ChildNodes
join row in result on node.Key equals row.ReportId into outer
from row in outer.DefaultIfEmpty()
where node["Browsable"] != "false"
select new
{
Node = node,
Row = row
})
group flatData by flatData.Node into g
select new RcReport(g.Key, g.Where(p => p.Row != null).Select(p => p.Row.RcApprover));
}
repRc.DataSource = listRc;
repRc.DataBind();
panelRc.HeaderText = string.Format(panelRc.HeaderText, listRc.Count());
// This is a list of rc waiting for current user's approval
var query = (from rc in listRc
where rc.ListPending.Any(p => p.UserId == HttpContext.Current.User.Identity.Name)
select rc.ReportNumber).ToList();
if (query.Count > 0)
{
string reportno = query.Count == 1 ? "Report" : "Reports";
string reports = string.Join(",", query);
divMsg.InnerText = string.Format("{0} {1} waiting for your approval", reportno, reports);
}
}
/// <summary>
/// Returns all roles assigned to the passed user
/// </summary>
/// <param name="username"></param>
/// <returns>Array of roles</returns>
/// <remarks>
/// <para>
/// Roles assigned to roles are also properly handled
/// </para>
/// </remarks>
public override string[] GetRolesForUser(string username)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
RoleCache cached;
if (_userRoles.TryGetValue(username, out cached))
{
// Found the roles in the cache.
if (DateTime.Now - cached.TimeStamp > MAX_CACHE_DURATION)
{
// Cache is stale. Ignore it.
_userRoles.TryRemove(username, out cached);
}
else
{
// Thankfully query is avoided
return cached.Roles;
}
}
if (string.Compare(_connectionStringBuilder.ProxyUserId, username, true) == 0)
{
return new[] { "WEB_PROXYUSER" };
}
/*
* TODO: Use this new query which uses recursive subquery syntax instead of CONNECT BY. This syntax was introduced in 11gR2
* Inspired by http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
*/
const string QUERY_ALL_ROLES = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT DISTINCT Q.GRANTED_ROLE AS ROLES
FROM Q1 Q
WHERE (Q.PATH = :username OR Q.PATH LIKE :username || '/%')
ORDER BY ROLES
";
const string QUERY_PRIVILEGES = @"
SELECT T.PRIV_ID AS PRIVS
FROM <proxy />UPRIV T
WHERE T.ORACLE_USER_NAME = :username
ORDER BY PRIVS
";
cached = new RoleCache { TimeStamp = DateTime.Now };
//var binder = new SqlBinder<string>("Querying Roles and privileges");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("username", username.ToUpper());
//binder.Query = QUERY_ALL_ROLES;
//binder.Factory = row => row.GetString();
//binder.CreateMapper(QUERY_ALL_ROLES);
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
IEnumerable<string> roles = db.ExecuteReader(QUERY_ALL_ROLES, binder);
//binder.Query = QUERY_PRIVILEGES;
IEnumerable<string> privs;
try
{
privs = db.ExecuteReader(QUERY_PRIVILEGES, binder);
}
catch (OracleDataStoreException ex)
{
if (ex.OracleErrorNumber == 942)
{
// Table or view does not exist. Stay silent
privs = Enumerable.Empty<string>();
}
else
{
throw;
}
}
cached.Roles = roles.Concat(privs).ToArray();
_userRoles.TryAdd(username, cached);
return cached.Roles;
}
}
/// <summary>
/// Retrieve inventory at Area/Vwh level
/// </summary>
/// <param name="skuId"></param>
/// <param name="iaId"></param>
/// <returns></returns>
/// <remarks>
///
/// </remarks>
public IList <SkuInventoryItem> GetSkuInventoryByArea(int skuId)
{
Contract.Assert(_db != null);
const string QUERY_CARTON_PALLET_DETAIL = @"
WITH Q1 AS
(SELECT 2 AS AREA_TYPE,
MAX(CTN.CARTON_STORAGE_AREA) AS IA_ID,
MAX(TIA.SHORT_NAME) AS SHORT_NAME,
MAX(TIA.DESCRIPTION) AS DESCRIPTION,
MAX(TIA.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
SUM(CTNDET.QUANTITY) AS PIECES,
CTN.LOCATION_ID,
MAX(CTN.VWH_ID) AS VWH_ID
FROM <proxy />SRC_CARTON CTN
INNER JOIN <proxy />SRC_CARTON_DETAIL CTNDET
ON CTN.CARTON_ID = CTNDET.CARTON_ID
LEFT OUTER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON CTN.CARTON_STORAGE_AREA = TIA.INVENTORY_STORAGE_AREA
WHERE CTNDET.SKU_ID = :SKU_ID
GROUP BY CTN.LOCATION_ID
HAVING SUM(CTNDET.QUANTITY) != 0
UNION ALL
SELECT 1 AS AREA_TYPE,
MAX(I.IA_ID) AS IA_ID,
MAX(IA.SHORT_NAME) AS SHORT_NAME,
MAX(IA.DESCRIPTION) AS DESCRIPTION,
MAX(IA.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
SUM(IC.NUMBER_OF_UNITS) AS PIECES,
IC.LOCATION_ID,
MAX(I.VWH_ID) AS VWH_ID
FROM <proxy />IALOC I
INNER JOIN <proxy />IALOC_CONTENT IC
ON I.LOCATION_ID = IC.LOCATION_ID
INNER JOIN <proxy />MASTER_SKU MSKU
ON MSKU.UPC_CODE = IC.IACONTENT_ID
LEFT OUTER JOIN <proxy />IA
ON I.IA_ID = IA.IA_ID
WHERE I.LOCATION_TYPE = 'RAIL'
AND IC.IACONTENT_TYPE_ID = 'SKU'
AND MSKU.SKU_ID = :SKU_ID
GROUP BY IC.LOCATION_ID
HAVING SUM(IC.NUMBER_OF_UNITS) != 0)
SELECT MAX(Q1.AREA_TYPE) AS AREA_TYPE,
Q1.IA_ID AS IA_ID,
MAX(Q1.SHORT_NAME) AS SHORT_NAME,
MAX(Q1.DESCRIPTION) AS DESCRIPTION,
MAX(Q1.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
SUM(Q1.PIECES) AS PIECES,
MAX(Q1.LOCATION_ID) KEEP(DENSE_RANK FIRST ORDER BY case when q1.location_id is null then NULL ELSE Q1.PIECES END DESC NULLS LAST) AS BEST_LOC,
MAX(Q1.PIECES) KEEP(DENSE_RANK FIRST ORDER BY case when q1.location_id is null then NULL ELSE Q1.PIECES END DESC NULLS LAST) AS PCS_AT_BEST_LOC,
COUNT(UNIQUE Q1.LOCATION_ID) AS TOTAL_LOCATIONS,
Q1.VWH_ID AS VWH_ID
FROM Q1
GROUP BY Q1.IA_ID, Q1.VWH_ID
";
var binder = SqlBinder.Create(row => new SkuInventoryItem
{
IaId = row.GetString("IA_ID"),
ShortName = row.GetString("SHORT_NAME"),
AreaDescription = row.GetString("DESCRIPTION"),
Building = row.GetString("WAREHOUSE_LOCATION_ID"),
Pieces = row.GetInteger("PIECES"),
LocationId = row.GetString("BEST_LOC"),
PiecesAtLocation = row.GetInteger("PCS_AT_BEST_LOC").Value,
LocationCount = row.GetInteger("TOTAL_LOCATIONS").Value,
VwhId = row.GetString("VWH_ID"),
IsCartonArea = row.GetInteger("AREA_TYPE") == 2
}).Parameter("SKU_ID", skuId);
return(_db.ExecuteReader(QUERY_CARTON_PALLET_DETAIL, binder));
}