/// <summary>
/// Returns the information of intransit carton.
/// </summary>
/// <param name="scan"></param>
/// <returns></returns>
public IntransitCarton GetIntransitCarton2(string scan)
{
const string QUERY = @"
with q1 AS
( Select SCI.VWH_ID AS VWH_ID,
MS.UPC_CODE AS UPC_CODE,
MS.STYLE AS STYLE_,
MS.COLOR AS COLOR_,
MS.DIMENSION AS DIMENSION_,
MS.SKU_SIZE AS SKU_SIZE_,
SCI.CARTON_ID AS CARTON_ID,
SCI.IS_SHIPMENT_CLOSED,
MS.SKU_ID AS SKU_ID,
mss.spotcheck_percent AS spotcheck_percent,
sci.received_date AS received_date,
mss.spotcheck_flag as IsSpotCheck_Enabled
FROM <proxy />SRC_CARTON_INTRANSIT SCI
LEFT OUTER JOIN <proxy />MASTER_SKU MS
ON MS.STYLE = SCI.STYLE
AND MS.COLOR = SCI.COLOR
AND MS.DIMENSION = SCI.DIMENSION
AND MS.SKU_SIZE = SCI.SKU_SIZE
LEFT OUTER JOIN <proxy />MASTER_STYLE M
ON M.STYLE = MS.STYLE
LEFT OUTER JOIN <proxy />MASTER_SEWINGPLANT_STYLE MSS
ON (SCI.STYLE = MSS.STYLE OR MSS.STYLE = '.')
AND (SCI.SEWING_PLANT_CODE = MSS.SEWING_PLANT_CODE OR
MSS.SEWING_PLANT_CODE = '.')
AND (SCI.COLOR = MSS.COLOR OR
MSS.COLOR = '.')
WHERE SCI.CARTON_ID = :carton_id and sci.received_date IS NULL)
select * from q1
";
var binder = SqlBinder.Create(row => new IntransitCarton()
{
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_"),
},
CartonId = row.GetString("CARTON_ID"),
VwhId = row.GetString("VWH_ID"),
UpcCode = row.GetString("UPC_CODE"),
SpotCheckPercent = row.GetDecimal("spotcheck_percent"),
//ReceivedDate = row.GetDate("received_date"),
IsShipmentClosed = row.GetString("IS_SHIPMENT_CLOSED") == "Y",
IsSpotCheckEnabled = row.GetString("isspotcheck_enabled") == "Y"
}).Parameter("carton_id", scan)
.OutRefCursorParameter("result");
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Getting Building of passed area
/// </summary>
/// <returns></returns>
public string GetBuildingofArea(string areaId)
{
const string QUERY =
@"SELECT TIA.Warehouse_Location_Id FROM <proxy />TAB_INVENTORY_AREA TIA
WHERE TIA.INVENTORY_STORAGE_AREA = :areaId
";
var binder = SqlBinder.Create(row => row.GetString("Warehouse_Location_Id")).Parameter("areaId", areaId);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Returns information about a specific customer.
/// </summary>
/// <param name="customerId"></param>
/// <returns></returns>
public string GetCustomerName(string customerId)
{
const string QUERY = @"
SELECT CUST.NAME AS NAME
FROM <proxy />MASTER_CUSTOMER CUST
WHERE CUST.CUSTOMER_ID = :SEARCH
";
var binder = SqlBinder.Create(row => row.GetString("NAME"));
binder.Parameter("SEARCH", customerId);
return(_db.ExecuteSingle(QUERY, binder));
}
internal Sku GetSku(int skuId)
{
if (skuId == 0)
{
throw new ArgumentNullException("skuId");
}
const string QUERY =
@"
SELECT MS.UPC_CODE AS UPC_CODE,
MS.SKU_ID AS SKU_ID,
MS.STYLE AS STYLE,
MS.COLOR AS COLOR,
MS.DIMENSION AS DIMENSION,
MS.SKU_SIZE AS SKU_SIZE
FROM <proxy />MASTER_SKU MS
WHERE MS.SKU_ID = :SKU_ID
";
var binder = SqlBinder.Create(row => 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.ExecuteSingle(QUERY, binder);
}
/// <summary>
/// Returns information about a specific customer.
/// </summary>
/// <param name="customerId"></param>
/// <returns></returns>
public Customer GetCustomer(string customerId)
{
const string QUERY = @"
SELECT CUST.CUSTOMER_ID AS CUSTOMER_ID,
CUST.NAME AS NAME,
CUST.INACTIVE_FLAG as INACTIVE_FLAG
FROM <proxy />MASTER_CUSTOMER CUST
WHERE CUST.CUSTOMER_ID = :SEARCH
";
var binder = SqlBinder.Create(row => new Customer
{
CustomerId = row.GetString("CUSTOMER_ID"),
Name = row.GetString("NAME"),
IsActive = row.GetString("INACTIVE_FLAG") != "Y"
});
binder.Parameter("SEARCH", customerId);
return(_db.ExecuteSingle(QUERY, binder));
}
internal string GetBuildingDescription(string buildingId)
{
const string QUERY = @"
SELECT TWL.DESCRIPTION AS DESCRIPTION
FROM <proxy />TAB_WAREHOUSE_LOCATION TWL
WHERE TWL.WAREHOUSE_LOCATION_ID = :WAREHOUSE_LOCATION_ID
";
var binder = SqlBinder.Create(row => row.GetString("DESCRIPTION"))
.Parameter("WAREHOUSE_LOCATION_ID", buildingId);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// FOR CATRON LOCATION INFORMATION
/// </summary>
/// <param name="locationId"></param>
/// <returns></returns>
public CartonLocation GetCartonLocationInfo(string locationId)
{
Contract.Assert(_db != null);
const string QUERY = @"
Select Msl.Location_Id As Location_Id,
Mskuass.Style As Assigned_Style,
Mskuass.Color As Assigned_Color,
Mskuass.Dimension As Assigned_Dimension,
Mskuass.Sku_Size As Assigned_Sku_Size,
Msl.Storage_Area As Storage_Area,
Msl.Warehouse_Location_Id As Warehouse_Location_Id,
Msl.Assigned_Sku_Id As Assigned_Sku_Id,
tia.short_name as short_name,
Msl.Assigned_Max_Cartons As Assigned_Max_Cartons
From <proxy />Master_Storage_Location Msl
Left Outer Join <proxy />Master_Sku Mskuass
On Mskuass.Sku_Id = Msl.Assigned_Sku_Id
left outer join <proxy />tab_inventory_area tia
on msl.storage_area = tia.inventory_storage_area
Where Msl.Location_Id = :Location_Id
";
var binder = SqlBinder.Create(row => new CartonLocation
{
LocationId = row.GetString("Location_Id"),
WhId = row.GetString("Warehouse_Location_Id"),
Capacity = row.GetInteger("Assigned_Max_Cartons"),
Area = row.GetString("Storage_Area"),
ShortName = row.GetString("short_name"),
AssignedSku = new SkuBase
{
Style = row.GetString("Assigned_Style"),
Color = row.GetString("Assigned_Color"),
Dimension = row.GetString("Assigned_Dimension"),
SkuSize = row.GetString("Assigned_Sku_Size"),
SkuId = row.GetInteger("Assigned_Sku_Id") ?? 0
}
}).Parameter("Location_Id", locationId);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
///
/// </summary>
/// <param name="upcCode"></param>
/// <returns></returns>
public Sku GetSku(string upcCode)
{
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.UPC_CODE = :upc_code
";
var binder = SqlBinder.Create(row => 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")
}).Parameter("upc_code", upcCode);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Returns sku info against scanned UPC
/// </summary>
/// <param name="upc"></param>
/// <returns>
/// </returns>
public Sku GetSku(int skuId)
{
Contract.Assert(_db != null);
const string QUERY_SKU_DETAIL = @"
SELECT MSKU.STYLE AS STYLE,
MSKU.COLOR AS COLOR,
MSKU.DIMENSION AS DIMENSION,
MSKU.SKU_SIZE AS SKU_SIZE,
MSKU.UPC_CODE AS UPC_CODE,
MSKU.SKU_ID AS SKU_ID,
MSKU.RETAIL_PRICE AS RETAIL_PRICE,
MSKU.PIECES_PER_PACKAGE AS PIECES_PER_PACKAGE,
MSKU.ADDITIONAL_RETAIL_PRICE AS ADDITIONAL_RETAIL_PRICE,
MS.DESCRIPTION AS DESCRIPTION,
MSKU.STANDARD_CASE_QTY AS STANDARD_CASE_QTY
FROM <proxy />MASTER_SKU MSKU
LEFT OUTER JOIN <proxy />MASTER_STYLE MS
ON MSKU.STYLE = MS.STYLE
WHERE MSKU.sku_id = :sku_id
";
var binder = SqlBinder.Create(row => 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"),
Upc = row.GetString("UPC_CODE"),
RetailPrice = row.GetDecimal("RETAIL_PRICE"),
PiecesPerPackage = row.GetInteger("PIECES_PER_PACKAGE"),
AdditionalRetailPrice = row.GetString("ADDITIONAL_RETAIL_PRICE"),
Description = row.GetString("DESCRIPTION"),
StandardCaseQty = row.GetInteger("STANDARD_CASE_QTY")
}).Parameter("sku_id", skuId);
return(_db.ExecuteSingle(QUERY_SKU_DETAIL, binder));
}
/// <summary>
/// FOR SKU AREA INFORMATION
/// </summary>
/// <param name="iaId"></param>
/// <returns></returns>
public SkuArea GetSkuAreaInfo(string iaId)
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT IA.IA_ID AS IA_ID,
MAX(IA.SHORT_NAME) AS SHORT_NAME,
MAX(IA.SHORT_DESCRIPTION) AS SHORT_DESCRIPTION,
MAX(IA.DEFAULT_IA_LOCATION) AS DEFAULT_IA_LOCATION,
MAX(IA.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
MAX(IA.PICKING_AREA_FLAG) AS PICKING_AREA_FLAG,
MAX(IA.SHIPPING_AREA_FLAG) AS SHIPPING_AREA_FLAG,
MAX(IA.PULL_CARTON_LIMIT) AS PULL_CARTON_LIMIT,
COUNT(DISTINCT IAL.LOCATION_ID) AS NUMBER_OF_LOCATIONS,
COUNT(IAL.ASSIGNED_UPC_CODE) AS NUMBER_OF_ASSIGNED_LOCATIONS
FROM <proxy />IA IA
LEFT OUTER JOIN <proxy />IALOC IAL
ON IA.IA_ID = IAL.IA_ID
WHERE IA.IA_ID = :IA_ID
GROUP BY IA.IA_ID
";
var binder = SqlBinder.Create(row => new SkuArea
{
IaId = row.GetString("IA_ID"),
Description = row.GetString("SHORT_DESCRIPTION"),
DefaultLocation = row.GetString("DEFAULT_IA_LOCATION"),
WhId = row.GetString("WAREHOUSE_LOCATION_ID"),
PickingAreaFlag = row.GetString("PICKING_AREA_FLAG"),
ShipingAreaFlag = row.GetString("SHIPPING_AREA_FLAG"),
PullCartonLimit = row.GetInteger("PULL_CARTON_LIMIT"),
NumberOfLocations = row.GetInteger("NUMBER_OF_LOCATIONS"),
AssignedLocations = row.GetInteger("NUMBER_OF_ASSIGNED_LOCATIONS"),
ShortName = row.GetString("SHORT_NAME")
}).Parameter("IA_ID", iaId);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Gets the
/// </summary>
/// <param name="appointmentNo"></param>
/// <returns></returns>
public Appointment GetAppointmentDetails(int appointmentNo)
{
const string QUERY = @"
SELECT COUNT(DISTINCT(CASE
WHEN BOX.TRUCK_LOAD_DATE IS NOT NULL THEN
BOX.PALLET_ID END)) AS LOADED_PALLET_COUNT,
COUNT(DISTINCT BOX.PALLET_ID) AS TOTAL_PALLET_COUNT,
COUNT(DISTINCT(CASE
WHEN BOX.SUSPENSE_DATE IS NOT NULL THEN
BOX.PALLET_ID END)) AS SUSPENCE_PALLET_COUNT,
COUNT(DISTINCT(CASE
WHEN BOX.PALLET_ID IS NULL THEN
BOX.UCC128_ID END)) AS UNPALLETIZE_BOX_COUNT,
COUNT(DISTINCT(CASE
WHEN BOX.TRUCK_LOAD_DATE IS NOT NULL THEN
BOX.UCC128_ID END)) AS LOADED_BOX_COUNT,
COUNT(DISTINCT BOX.UCC128_ID) AS TOTAL_BOX_COUNT,
MAX(MS.BUILDING_ID) AS BUILDING_ID,
MAX(MS.PICKUP_DOOR) AS PICKUP_DOOR,
MAX(MS.CARRIER_ID) AS CARRIER_ID
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 PS.TRANSFER_DATE IS NULL
AND BOX.STOP_PROCESS_DATE IS NULL
GROUP BY MS.APPOINTMENT_ID";
var binder = SqlBinder.Create(row => new Appointment
{
LoadedBoxCount = row.GetInteger("LOADED_BOX_COUNT") ?? 0,
LoadedPalletCount = row.GetInteger("LOADED_PALLET_COUNT") ?? 0,
PalletsInSuspenseCount = row.GetInteger("SUSPENCE_PALLET_COUNT") ?? 0,
TotalPalletCount = row.GetInteger("TOTAL_PALLET_COUNT") ?? 0,
TotalBoxCount = row.GetInteger("TOTAL_BOX_COUNT") ?? 0,
UnPalletizeBoxCount = row.GetInteger("UNPALLETIZE_BOX_COUNT") ?? 0,
BuildingId = row.GetString("BUILDING_ID"),
DoorId = row.GetString("PICKUP_DOOR"),
CarrierId = row.GetString("CARRIER_ID")
})
.Parameter("APPOINTMENT_NUMBER", appointmentNo);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Used to validated the UPC code
/// </summary>
/// <param name="upcCode"></param>
/// <returns></returns>
public Sku GetSkuFromUpc(string upcCode)
{
const string QUERY = @"
SELECT MS.UPC_CODE AS UPC_CODE,
MS.STYLE AS STYLE,
MS.COLOR AS COLOR,
MS.DIMENSION AS DIMENSION,
MS.SKU_SIZE AS SKU_SIZE,
MS.SKU_ID AS SKU_ID
FROM <proxy />MASTER_SKU MS
WHERE MS.UPC_CODE = :UPCCODE
AND MS.INACTIVE_FLAG IS NULL
UNION ALL
SELECT MS.UPC_CODE,
MS.STYLE,
MS.COLOR,
MS.DIMENSION,
MS.SKU_SIZE,
MS.SKU_ID
FROM <proxy />MASTER_CUSTOMER_SKU MCS
INNER JOIN <proxy />MASTER_SKU MS
ON MS.STYLE = MCS.STYLE
AND MS.COLOR = MCS.COLOR
AND MS.DIMENSION = MCS.DIMENSION
AND MS.SKU_SIZE = MCS.SKU_SIZE
WHERE MS.INACTIVE_FLAG IS NULL
AND NVL(MCS.SCANNED_BAR_CODE, MCS.CUSTOMER_SKU_ID) = :UPCCODE
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create(row => new Sku
{
SkuId = row.GetInteger("SKU_ID") ?? 0,
Style = row.GetString("STYLE"),
Color = row.GetString("COLOR"),
Dimension = row.GetString("DIMENSION"),
SkuSize = row.GetString("SKU_SIZE"),
UpcCode = row.GetString("UPC_CODE"),
}).Parameter("UPCCODE", upcCode);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// Retrieves area whihc is for bad pitch boxes. We are retreiving it from the iaconfig $BADPITCH.
/// </summary>
/// <returns>Returns the area where under pitched boxes are sent</returns>
public string GetBadPitchArea()
{
const string QUERY = @"
SELECT IA_ID AS IA_ID
FROM <proxy />IACONFIG IC
WHERE IC.IACONFIG_ID = '$BADPITCH'
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create(row => row.GetString("IA_ID"));
++_queryCount;
var strBadPitchArea = _db.ExecuteSingle(QUERY, binder);
return(strBadPitchArea);
}
public Customer GetCustomer(string customerId)
{
const string QUERY =
@"SELECT CUST.CUSTOMER_ID AS CUSTOMER_ID,
CUST.NAME AS CUSTOMER_NAME
FROM <proxy />CUST CUST
WHERE CUST.INACTIVE_FLAG IS NULL
<if c='$customerId'>
AND CUST.CUSTOMER_ID =:customerId
</if>
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create(row => new Customer
{
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("CUSTOMER_NAME")
})
.Parameter("customerId", customerId);
return _db.ExecuteSingle(QUERY, binder);
}
/// <summary>
/// This function is for checking whether the passed role is a priv.
/// </summary>
/// <param name="roleName"></param>
private bool IsRolePriv(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
if (!_privTablesExist)
{
// This cannot be a privilege
return(false);
}
const string strQuery = @"
SELECT PRIV.PRIV_ID FROM <proxy />PRIV PRIV WHERE PRIV.PRIV_ID = :rolename
";
//var binder = new SqlBinder<string>("Retreving priv.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(strQuery);
//binder.Query = strQuery;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
string strPriv = "";
try
{
strPriv = db.ExecuteSingle(strQuery, binder);
}
catch (OracleDataStoreException ex)
{
if (ex.OracleErrorNumber == 942)
{
// Table PRIV does not exist. Stay silent
_privTablesExist = false;
}
}
return(!string.IsNullOrEmpty(strPriv));
}
}
/// <summary>
/// Returns carton count on the passed pallet
/// </summary>
/// <param name="palletId"></param>
/// <returns></returns>
public Pallet GetPallet(string palletId)
{
const string QUERY = @"
SELECT COUNT(CTN.CARTON_ID) AS CARTON_COUNT,
MAX(CTN.CARTON_STORAGE_AREA) AS MAX_CARTON_STORAGE_AREA,
SYS.STRAGG(UNIQUE(CTN.PRICE_SEASON_CODE) || ' ') AS PRICE_SEASON_CODE
FROM <proxy />SRC_CARTON CTN
WHERE CTN.PALLET_ID = :PALLET_ID
";
var binder = SqlBinder.Create(row => new Pallet
{
CartonCount = row.GetInteger("CARTON_COUNT") ?? 0,
MaxCartonArea = row.GetString("MAX_CARTON_STORAGE_AREA"),
PriceSeasonCode = row.GetString("PRICE_SEASON_CODE")
}).Parameter("PALLET_ID", palletId);
var pallet = _db.ExecuteSingle(QUERY, binder);
pallet.PalletId = palletId;
return(pallet);
}
/// <summary>
/// Retrive info for passed location.
/// </summary>
/// <param name="locationId"></param>
/// <returns></returns>
public Location GetLocation(string locationId)
{
if (string.IsNullOrWhiteSpace(locationId))
{
throw new ArgumentNullException("locationId");
}
const string QUERY = @"
Select msl.storage_area AS storage_area,
tia.short_name AS SHORT_NAME,
msl.travel_sequence AS travel_sequence,
msl.assigned_max_cartons AS assigned_max_cartons,
NVL(TIA.WAREHOUSE_LOCATION_ID, MSL.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
TIA.STORES_WHAT AS STORES_WHAT,
MSL.unavailable_flag AS unavailable_flag,
(select count(*)
from <proxy />src_carton ctn
where ctn.location_id = :location_id) AS count_cartons_at_location
from <proxy />master_storage_location msl
INNER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON TIA.INVENTORY_STORAGE_AREA = MSL.STORAGE_AREA
where msl.location_id = :location_id
";
var binder = SqlBinder.Create(row => new Location
{
AreaId = row.GetString("storage_area"),
AreaShortName = row.GetString("SHORT_NAME"),
TravelSequence = row.GetInteger("travel_sequence"),
BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"),
StoresWhat = row.GetString("STORES_WHAT"),
LocationId = locationId,
UnavailableFlag = row.GetString("unavailable_flag") == "Y",
CountCartons = row.GetInteger("count_cartons_at_location"),
MaxCartons = row.GetInteger("assigned_max_cartons")
}).Parameter("location_id", locationId);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// This function is for checking whether the passed role exists or not.
/// </summary>
/// <param name="roleName"></param>
/// <exception cref="NotImplementedException"></exception>
/// <returns>boolean</returns>
public override bool RoleExists(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
const string QUERY_PRIV = @"SELECT PRIV_ID FROM <proxy />PRIV WHERE PRIV_ID = :rolename";
const string QUERY_ROLE = @"SELECT ROLE AS ROLE FROM DBA_ROLES WHERE ROLE = :rolename";
var strQuery = IsRolePriv(roleName) ? QUERY_PRIV : QUERY_ROLE;
//var binder = new SqlBinder<string>("Retreving users.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(strQuery);
//binder.Query = strQuery;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
return(!string.IsNullOrEmpty(db.ExecuteSingle(strQuery, binder)));
}
}
public Box GetBoxOfUcc(string uccId)
{
if (string.IsNullOrWhiteSpace(uccId))
{
throw new ArgumentNullException("uccId");
}
Contract.Assert(_db != null);
const string QUERY_BOX_DETAIL = @"
SELECT BOX.UCC128_ID AS UCC128_ID,
BOX.PICKSLIP_ID AS PICKSLIP_ID,
--PS.PO_ID AS PO_ID,
BOX.IA_ID AS IA_ID,
IA.SHORT_NAME AS SHORT_NAME,
IA.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
IA.SHORT_DESCRIPTION AS IA_SHORT_DESCRIPTION,
BOX.PALLET_ID AS PALLET_ID,
BOX.VWH_ID AS VWH_ID,
BOX.PRO_NUMBER AS PRO_NUMBER,
PS.CUSTOMER_ID AS CUSTOMER_ID,
BOX.STOP_PROCESS_DATE AS STOP_PROCESS_DATE,
BOX.STOP_PROCESS_REASON AS STOP_PROCESS_REASON,
CUST.NAME AS NAME,
--PS.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
BOX.PITCHING_END_DATE AS PITCHING_END_DATE,
BOX.VERIFY_DATE AS VERIFY_DATE,
BOX.QC_DATE AS QC_DATE,
PS.IS_RFIDTAGS_REQUIRED AS IS_RFIDTAGS_REQUIRED,
--SUM(NVL(BD.EXPECTED_PIECES, BD.CURRENT_PIECES AS EXPECTED_PIECES,
--SUM(BD.CURRENT_PIECES AS CURRENT_PIECES,
BOX.SUSPENSE_DATE AS SUSPENSE_DATE,
BOX.REJECTION_CODE AS REJECTION_CODE,
--COUNT(DISTINCT BD.UPC_CODE AS SKU_IN_BOX,
PS.BUCKET_ID AS BUCKET_ID,
BOX.CARTON_ID AS CARTON_ID,
BOX.LAST_CCL_PRINTED_BY AS LAST_CCL_PRINTED_BY,
BOX.LAST_UCC_PRINTED_BY AS LAST_UCC_PRINTED_BY,
BOX.LAST_UCC_PRINT_DATE AS LAST_UCC_PRINTED_DATE,
BOX.LAST_CCL_PRINT_DATE AS LAST_CCL_PRINT_DATE,
PS.CUSTOMER_DC_ID AS CUSTOMER_DC_ID,
PS.CUSTOMER_STORE_ID AS CUSTOMER_STORE_ID,
PS.SHIPPING_ADDRESS.ADDRESS_LINE_1 AS ADDRESS_LINE_1,
PS.SHIPPING_ADDRESS.ADDRESS_LINE_2 AS ADDRESS_LINE_2,
PS.SHIPPING_ADDRESS.ADDRESS_LINE_3 AS ADDRESS_LINE_3,
PS.SHIPPING_ADDRESS.ADDRESS_LINE_4 AS ADDRESS_LINE_4,
PS.SHIPPING_ADDRESS.CITY AS CITY,
PS.SHIPPING_ADDRESS.STATE AS STATE,
c.document_id as catalog_document_id
FROM <proxy />BOX BOX
INNER JOIN <proxy />PS PS
ON BOX.PICKSLIP_ID = PS.PICKSLIP_ID
LEFT OUTER JOIN <proxy />CUST CUST
ON CUST.CUSTOMER_ID = PS.CUSTOMER_ID
left outer join <proxy />CUSTDOC C
on PS.CUSTOMER_ID = c.customer_id
AND C.DOCUMENT_ID = '$CL'
LEFT OUTER JOIN <proxy />IA IA
ON BOX.IA_ID = IA.IA_ID
WHERE box.ucc128_id = :UCC_ID
";
var binder = SqlBinder.Create(row => new Box
{
Ucc128Id = row.GetString("UCC128_ID"),
PalletId = row.GetString("PALLET_ID"),
CartonId = row.GetString("CARTON_ID"),
IaId = row.GetString("IA_ID"),
RfidTagsRequired = row.GetString("IS_RFIDTAGS_REQUIRED"),
QcDate = row.GetDate("QC_DATE"),
VerificationDate = row.GetDate("VERIFY_DATE"),
PitchingEndDate = row.GetDate("PITCHING_END_DATE"),
VwhId = row.GetString("VWH_ID"),
ProNo = row.GetString("PRO_NUMBER"),
LastCclPrintedBy = row.GetString("LAST_CCL_PRINTED_BY"),
LastUccPrintedBy = row.GetString("LAST_UCC_PRINTED_BY"),
LastCclPrintedDate = row.GetDate("LAST_CCL_PRINT_DATE"),
LastUccPrintedDate = row.GetDate("LAST_UCC_PRINTED_DATE"),
RejectionCode = row.GetString("REJECTION_CODE"),
SuspenseDate = row.GetDate("SUSPENSE_DATE"),
StopProcessDate = row.GetDate("STOP_PROCESS_DATE"),
StopProcessReason = row.GetString("STOP_PROCESS_REASON"),
PickslipId = row.GetLong("PICKSLIP_ID").Value,
BucketId = row.GetInteger("BUCKET_ID") ?? 0,
CustomerDC = row.GetString("CUSTOMER_DC_ID"),
CustomerStore = row.GetString("CUSTOMER_STORE_ID"),
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("NAME"),
ShortName = row.GetString("SHORT_NAME"),
IaShortDescription = row.GetString("IA_SHORT_DESCRIPTION"),
Building = row.GetString("WAREHOUSE_LOCATION_ID"),
ToAddress = new[] {
row.GetString("ADDRESS_LINE_1"),
row.GetString("ADDRESS_LINE_2"),
row.GetString("ADDRESS_LINE_3"),
row.GetString("ADDRESS_LINE_4")
},
ToCity = row.GetString("CITY"),
ToState = row.GetString("STATE"),
CatalogDocumentId = row.GetString("catalog_document_id")
}).Parameter("UCC_ID", uccId);
return(_db.ExecuteSingle(QUERY_BOX_DETAIL, binder));
}
/// <summary>
/// Returns full information about the passed user
/// </summary>
/// <param name="username"></param>
/// <param name="userIsOnline"></param>
/// <returns></returns>
/// <remarks>
/// The returned info contains audit log of the user as well
/// and also returned info contains session log of user.
/// Users who own schema objects are not visible to this function since our goal is to manage application users only. We do not want to manage
/// application owners.
/// </remarks>
public override MembershipUser GetUser(string username, bool userIsOnline)
{
if (string.IsNullOrEmpty(username))
{
throw new ArgumentNullException("username");
}
OracleMembershipUser user;
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
//db.ConnectionString = _connectionString;
//db.ProviderName = _providerName;
db.CreateConnection(_connectionString, string.Empty);
const string QUERY = @"
SELECT U.USERNAME AS USERNAME,
U.LOCK_DATE AS LOCK_DATE,
U.CREATED AS CREATION_DATE,
U.USER_ID AS USER_ID,
U.EXPIRY_DATE AS EXPIRYDATE
FROM DBA_USERS U
WHERE U.USERNAME = :USERNAME
AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)";
//var binder = new SqlBinder<OracleMembershipUser>("Querying User properties");
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("CREATION_DATE").Value,
passwordExpiryDate: src.GetDate("ExpiryDate") ?? DateTime.MinValue
));
binder.Parameter("username", username.ToUpper());
//binder.CreateMapper(QUERY, config => config.CreateMap<OracleMembershipUser>()
// .ConstructUsing(src => new OracleMembershipUser
// (
// userName: src.GetValue<string>("USERNAME"),
// providerUserKey: src.GetValue<int>("USER_ID").ToString(),
// lastLockoutDate: src.GetValue<DateTime>("LOCK_DATE"),
// createDate: src.GetValue<DateTime>("CREATION_DATE"),
// passwordExpiryDate: src.GetValue<DateTime>("ExpiryDate")
// )).ForAllMembers(opt => opt.Ignore()));
//binder.Query = QUERY;
//binder.Factory = src => new OracleMembershipUser(
// userName: src.GetString("USERNAME"),
// providerUserKey: src.GetInteger("USER_ID").ToString(),
// lastLockoutDate: src.GetDate("LOCK_DATE").Value,
// createDate: src.GetDate("CREATION_DATE").Value,
// passwordExpiryDate: src.GetDate("ExpiryDate").Value
// );
user = db.ExecuteSingle(QUERY, binder);
if (user != null)
{
user.AuditLog = DoGetUserAudit(username, db);
user.Sessions = DoGetUserSessions(username, db);
}
}
return(user);
}
/// <summary>
/// This function is for checking whether the passed role exists or not.
/// </summary>
/// <param name="roleName"></param>
/// <exception cref="NotImplementedException"></exception>
/// <returns>boolean</returns>
public override bool RoleExists(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
const string QUERY_PRIV = @"SELECT PRIV_ID FROM <proxy />PRIV WHERE PRIV_ID = :rolename";
const string QUERY_ROLE = @"SELECT ROLE AS ROLE FROM DBA_ROLES WHERE ROLE = :rolename";
var strQuery = IsRolePriv(roleName) ? QUERY_PRIV : QUERY_ROLE;
//var binder = new SqlBinder<string>("Retreving users.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(strQuery);
//binder.Query = strQuery;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
return !string.IsNullOrEmpty(db.ExecuteSingle(strQuery, binder));
}
}
/// <summary>
/// This function is for checking whether the passed role is a priv.
/// </summary>
/// <param name="roleName"></param>
private bool IsRolePriv(string roleName)
{
if (string.IsNullOrWhiteSpace(roleName))
{
throw new ArgumentNullException("roleName");
}
if (!_privTablesExist)
{
// This cannot be a privilege
return false;
}
const string strQuery = @"
SELECT PRIV.PRIV_ID FROM <proxy />PRIV PRIV WHERE PRIV.PRIV_ID = :rolename
";
//var binder = new SqlBinder<string>("Retreving priv.");
var binder = SqlBinder.Create(row => row.GetString(0));
binder.Parameter("rolename", roleName.ToUpper());
//binder.CreateMapper(strQuery);
//binder.Query = strQuery;
//binder.Factory = row => row.GetString();
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
string strPriv = "";
try
{
strPriv = db.ExecuteSingle(strQuery, binder);
}
catch (OracleDataStoreException ex)
{
if (ex.OracleErrorNumber == 942)
{
// Table PRIV does not exist. Stay silent
_privTablesExist = false;
}
}
return !string.IsNullOrEmpty(strPriv);
}
}
/// <summary>
/// Function return the Pickslip info.
/// </summary>
/// <returns></returns>
public Pickslip GetActivePickslip(long pickslipId)
{
Contract.Assert(_db != null);
const string QUERY_PICKSLIP_INFO = @"
SELECT PS.PICKSLIP_ID AS PICKSLIP_ID,
MC.CARRIER_ID || '.: ' || MC.DESCRIPTION AS CARRIER_ID,
CST.CUSTOMER_ID AS CUSTOMER_ID,
CST.NAME AS NAME,
PS.PO_ID AS PO_ID,
PS.EXPORT_FLAG AS EXPORT_FLAG,
PS.ITERATION AS ITERATION,
PS.DATE_CREATED AS DATE_CREATED,
PS.PICKSLIP_IMPORT_DATE AS PICKSLIP_IMPORT_DATE,
PS.PICKSLIP_CANCEL_DATE AS PICKSLIP_CANCEL_DATE,
PS.TRANSFER_DATE AS TRANSFER_DATE,
PS.CUSTOMER_STORE_ID AS CUSTOMER_STORE_ID,
PS.CUSTOMER_DEPARTMENT_ID AS CUSTOMER_DEPARTMENT_ID,
PS.ERP_ID AS ERP_ID,
TRIM(PS.CUSTOMER_DC_ID) AS CUSTOMER_DC_ID,
SHIP.SHIPPING_ID AS SHIPPING_ID,
SHIP.SHIP_DATE AS SHIP_DATE,
SHIP.SHIPPER_NAME AS SHIPPER_NAME,
SHIP.ONHOLD_FLAG AS ONHOLD_FLAG,
PO.DC_CANCEL_DATE AS DC_CANCEL_DATE,
PO.START_DATE AS START_DATE,
PO.CANCEL_DATE AS CANCEL_DATE,
ps.TOTAL_QUANTITY_ORDERED AS TOTAL_QUANTITY_ORDERED,
B.BUCKET_ID AS BUCKET_ID,
B.CREATED_BY AS BUCKET_CREATED_BY,
B.DATE_CREATED AS BUCKET_DATE_CREATED,
PS.SHIPPING_ADDRESS.ADDRESS_LINE_1 AS SHIPPING_ADDRESS_LINE_1,
PS.SHIPPING_ADDRESS.ADDRESS_LINE_2 AS SHIPPING_ADDRESS_LINE_2,
PS.SHIPPING_ADDRESS.ADDRESS_LINE_3 AS SHIPPING_ADDRESS_LINE_3,
PS.SHIPPING_ADDRESS.ADDRESS_LINE_4 AS SHIPPING_ADDRESS_LINE_4,
PS.SHIPPING_ADDRESS.CITY AS SHIPPING_ADDRESS_CITY,
PS.SHIPPING_ADDRESS.STATE AS SHIPPING_ADDRESS_STATE,
PS.SHIPPING_ADDRESS.ZIP_CODE AS SHIPPING_ADDRESS_ZIP_CODE,
PS.SHIPPING_ADDRESS.COUNTRY_CODE AS SHIPPING_ADDRESS_COUNTRY_CODE,
ps.vendor_number AS vendor_number,
CST.ASN_FLAG AS ASN_FLAG
FROM <proxy />PS PS
LEFT OUTER JOIN <proxy />CUST CST
ON CST.CUSTOMER_ID = PS.CUSTOMER_ID
LEFT OUTER JOIN <proxy />MASTER_CARRIER MC
ON MC.CARRIER_ID=PS.CARRIER_ID
LEFT OUTER JOIN <proxy />PO PO
ON PO.PO_ID = PS.PO_ID
AND PO.CUSTOMER_ID = PS.CUSTOMER_ID
AND PO.ITERATION = PS.ITERATION
LEFT OUTER JOIN <proxy />SHIP SHIP
ON SHIP.SHIPPING_ID = PS.SHIPPING_ID
LEFT OUTER JOIN <proxy />BUCKET B
ON PS.BUCKET_ID = B.BUCKET_ID
WHERE PS.PICKSLIP_ID = :PICKSLIP_ID
";
var binder = SqlBinder.Create(row => new Pickslip
{
PickslipId = row.GetLong("PICKSLIP_ID").Value,
CarrierId = row.GetString("CARRIER_ID"),
ShipDate = row.GetDate("ship_date"),
PickslipCancelDate = row.GetDate("PICKSLIP_CANCEL_DATE"),
CreateDate = row.GetDate("DATE_CREATED"),
ImportDate = row.GetDate("PICKSLIP_IMPORT_DATE"),
TransferDate = row.GetDate("TRANSFER_DATE"),
CustomerDC = row.GetString("CUSTOMER_DC_ID"),
CustomerStore = row.GetString("CUSTOMER_STORE_ID"),
ShippingId = row.GetString("SHIPPING_ID"),
TotalQuantityOrdered = row.GetInteger("TOTAL_QUANTITY_ORDERED"),
ExportFlag = !string.IsNullOrWhiteSpace(row.GetString("EXPORT_FLAG")) ? "Yes" : "No",
AsnFlag = !string.IsNullOrEmpty(row.GetString("ASN_FLAG")),
BucketId = row.GetInteger("BUCKET_ID"),
BucketCreatedBy = row.GetString("BUCKET_CREATED_BY"),
BucketCreatedOn = row.GetDate("BUCKET_DATE_CREATED"),
ShipperName = row.GetString("SHIPPER_NAME"),
ShipmentOnHold = row.GetString("ONHOLD_FLAG"),
CustomerDepartmentId = row.GetString("CUSTOMER_DEPARTMENT_ID"),
VendorNumber = row.GetString("vendor_number"),
PoId = row.GetString("PO_ID"),
Iteration = row.GetInteger("ITERATION").Value,
CancelDate = row.GetDate("CANCEL_DATE"),
StartDate = row.GetDate("START_DATE"),
DcCancelDate = row.GetDate("DC_CANCEL_DATE"),
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("NAME"),
ShipAddress = new[] {
row.GetString("SHIPPING_ADDRESS_LINE_1"),
row.GetString("SHIPPING_ADDRESS_LINE_2"),
row.GetString("SHIPPING_ADDRESS_LINE_3"),
row.GetString("SHIPPING_ADDRESS_LINE_4")
},
ShipCity = row.GetString("SHIPPING_ADDRESS_CITY"),
ShipCountry = row.GetString("SHIPPING_ADDRESS_COUNTRY_CODE"),
ShipState = row.GetString("SHIPPING_ADDRESS_STATE"),
ShipZipCode = row.GetString("SHIPPING_ADDRESS_ZIP_CODE"),
ErpId = row.GetString("ERP_ID")
}).Parameter("PICKSLIP_ID", pickslipId);
return(_db.ExecuteSingle(QUERY_PICKSLIP_INFO, binder));
}
/// <summary>
/// This function is for revoking the passed roles from the passed users.
/// </summary>
/// <param name="usernames"></param>
/// <param name="roleNames"></param>
/// <remarks>
/// <para>
/// Empty user names and roles are silently ignored. All user names and roles are converted to upper case before they are processed.
/// </para>
/// <para>
/// The logged in user must have the rights to revoke roles. The logged in user must also have the delete rights on table upriv to delete user's priv.
/// Follwing are the scripts.
/// </para>
/// <code>
/// <![CDATA[
/// grant GRANT ANY ROLE to <user-name>
/// grant DELETE on URPIV to <user-name>
/// ]]>
/// </code>
/// </remarks>
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials to remove roles from users");
}
var joinUsersRoles = from user in usernames
from role in roleNames
where !string.IsNullOrEmpty(user) &&
!string.IsNullOrEmpty(role)
select new
{
Role = role.Trim().ToUpper(),
User = user.Trim().ToUpper()
};
const string QUERY_PRIV = @"DELETE <proxy />UPRIV WHERE ORACLE_USER_NAME ='{0}' AND PRIV_ID ='{1}'";
const string QUERY_ROLE = @"REVOKE {1} FROM {0}";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
foreach (var item in joinUsersRoles)
{
var query = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
ClearRoleCache(item.User);
try
{
db.ExecuteNonQuery(query, null);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1919:
case 942:
case 1031:
// 1919: Role does not exist
// 942 : UPRIV table does not exist. To us this means no rights to delete from UPRIV
// 1031: Rights to revoke the role are not avaliable.
throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to revoke this role", item.Role));
case 1951:
// Role not granted. Check whether the role has been granted inderectly.
const string QUERY_ROLE_PATH = @"
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 substr(path, instr(path, '/') + 1)
FROM Q1 Q
WHERE Q.PATH LIKE :username || '/%'
and q.granted_role = :rolename
";
// Execute this query as super user
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
//var binder = new SqlBinder<string>("Get Role Path");
var binder = SqlBinder.Create(row => row.GetString(0));
//binder.CreateMapper(QUERY_ROLE_PATH);
//binder.Query = QUERY_ROLE_PATH;
//binder.Factory = row => row.GetString();
binder.Parameter("username", item.User);
binder.Parameter("rolename", item.Role);
var path = db.ExecuteSingle<string>(QUERY_ROLE_PATH, binder);
if (!string.IsNullOrEmpty(path))
{
var roleToRevoke = path.Split('/').First();
throw new ProviderException(
string.Format(
"Role {0} has indirectly granted to user {1} and cannot be revoked directly. {2}/{0}. To revoke {0} role revoke {3} role.",
item.Role, item.User, path, roleToRevoke));
}
throw new ProviderException(ex.Message);
case 1917:
throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));
default:
throw;
}
}
}
}
}
/// <summary>
/// Gets details of passed carton
/// </summary>
/// <param name="cartonId">string</param>
/// <returns>Retuns RestockCarton object</returns>
public RestockCarton GetCartonDetails(string cartonId)
{
const string QUERY = @"
SELECT 0 AS CARTON_TYPE,
SC.CARTON_ID AS CARTON_ID,
MAX(sc.pallet_id) AS pallet_id,
MAX(SC.QUALITY_CODE) AS QUALITY_CODE,
MAX(SC.VWH_ID) AS VWH_ID,
MAX(MSKU.SKU_ID) AS SKU_ID,
MAX(MSKU.STYLE) AS STYLE,
MAX(MSKU.COLOR) AS COLOR,
MAX(MSKU.DIMENSION) AS DIMENSION,
MAX(MSKU.SKU_SIZE) AS SKU_SIZE,
MAX(MSKU.UPC_CODE) AS UPC_CODE,
MAX(MS.LABEL_ID) AS LABEL_ID,
MAX(SCD.QUANTITY) AS QUANTITY,
COUNT(DISTINCT SCD.SKU_ID) AS NUMBER_OF_SKU,
MAX(SC.WORK_NEEDED_XML) AS WORK_NEEDED_XML,
MAX(SC.CARTON_STORAGE_AREA) AS CARTON_STORAGE_AREA,
MAX(MSKU.PIECES_PER_PACKAGE) AS PIECES_PER_PACKAGE,
MAX(MSKU.RETAIL_PRICE) AS RETAIL_PRICE
FROM <proxy />SRC_CARTON SC
LEFT OUTER JOIN <proxy />SRC_CARTON_DETAIL SCD
ON SCD.CARTON_ID = SC.CARTON_ID
LEFT OUTER JOIN <proxy />MASTER_SKU MSKU
ON SCD.SKU_ID = MSKU.SKU_ID
LEFT OUTER JOIN <proxy />MASTER_STYLE MS
ON MS.STYLE = MSKU.STYLE
WHERE SC.CARTON_ID = :CARTON_ID
GROUP BY SC.CARTON_ID
UNION ALL
SELECT 1,
SC.CARTON_ID AS CARTON_ID,
NULL,
SC.QUALITY_CODE AS QUALITY_CODE,
SC.VWH_ID AS VWH_ID,
SC.SKU_ID AS SKU_ID,
NULL AS STYLE_,
NULL AS COLOR_,
NULL AS DIMENSION_,
NULL AS SKU_SIZE_,
SC.UPC_CODE AS UPC_CODE_,
NULL AS LABEL_ID_,
SC.TOTAL_CARTON_QUANTITY AS QUANTITY,
1 AS NUMBER_OF_SKU,
NULL AS WORK_NEEDED_XML,
NULL AS CARTON_STORAGE_AREA,
NULL AS PIECES_PER_PACKAGE_,
NULL AS RETAIL_PRICE_
FROM <proxy />SRC_OPEN_CARTON SC
WHERE SC.CARTON_ID = :CARTON_ID
ORDER BY 1";
var binder = SqlBinder.Create(row => new RestockCarton
{
CartonId = row.GetString("CARTON_ID"),
CartonType = row.GetInteger("carton_type") ?? 0,
QualityCode = row.GetString("QUALITY_CODE"),
VwhId = row.GetString("VWH_ID"),
PiecesInCarton = row.GetInteger("QUANTITY") ?? 0,
SkuCount = row.GetInteger("NUMBER_OF_SKU"),
CartonStorageArea = row.GetString("CARTON_STORAGE_AREA"),
IsWorkNeeded = !string.IsNullOrEmpty(row.GetString("WORK_NEEDED_XML")),
SkuId = row.GetInteger("SKU_ID"),
Style = row.GetString("STYLE"),
Color = row.GetString("COLOR"),
Dimension = row.GetString("DIMENSION"),
SkuSize = row.GetString("SKU_SIZE"),
UpcCode = row.GetString("UPC_CODE"),
LabelId = row.GetString("LABEL_ID"),
PiecesPerPackage = row.GetInteger("PIECES_PER_PACKAGE") ?? 0,
RetailPrice = row.GetDecimal("RETAIL_PRICE"),
PalletId = row.GetString("pallet_id")
});
binder.Parameter("CARTON_ID", cartonId);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// The passed id can be a pallet id or a carton id. In the latter case, the cartonId is used to deduce the pallet.
/// </summary>
/// <returns></returns>
public Pallet GetPallet(string palletId, string cartonId)
{
const string QUERY = @"
SELECT SC.PALLET_ID AS PALLET_ID,
COUNT(SC.CARTON_ID) AS CARTONS_COUNT,
COUNT(DISTINCT SC.CARTON_STORAGE_AREA) AS AREA_COUNT,
COUNT(DISTINCT SC.LOCATION_ID) AS LOCATION_COUNT,
MIN(SC.LOCATION_ID) AS LOCATION_ID,
MIN(SC.CARTON_STORAGE_AREA) AS CARTON_STORAGE_AREA,
MIN(SC.QUALITY_CODE) AS QUALITY_CODE,
MIN(SC.VWH_ID) AS CTN_VWH_ID,
COUNT(DISTINCT SC.VWH_ID) AS CTN_VWH_COUNT,
COUNT(DISTINCT CTNDET.SKU_ID) AS SKU_COUNT,
COUNT(DISTINCT SC.QUALITY_CODE) AS QUALITY_COUNT,
MIN(CTNDET.SKU_ID) AS SKU_ID,
MIN(MSKU.STYLE) AS STYLE,
MIN(MSKU.COLOR) AS COLOR,
MIN(MSKU.DIMENSION) AS DIMENSION,
MIN(MSKU.SKU_SIZE) AS SKU_SIZE,
MIN(MSKU.UPC_CODE) AS UPC_CODE,
MIN(TIA.SHORT_NAME) AS SHORT_NAME,
SUM(CTNDET.QUANTITY) AS TOTAL_QUANTITY
FROM <proxy />SRC_CARTON SC
INNER JOIN <proxy />SRC_CARTON_DETAIL CTNDET
ON SC.CARTON_ID = CTNDET.CARTON_ID
INNER JOIN <proxy />MASTER_SKU MSKU
ON MSKU.SKU_ID = CTNDET.SKU_ID
INNER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON TIA.INVENTORY_STORAGE_AREA = SC.CARTON_STORAGE_AREA
WHERE 1 = 1
<if>
AND SC.PALLET_ID = :PALLET_ID
</if>
<if>
AND SC.PALLET_ID IN (select ctn.PALLET_ID FROM <proxy />SRC_CARTON ctn WHERE ctn.CARTON_ID = :CARTON_ID)
</if>
GROUP BY SC.PALLET_ID
";
var binder = SqlBinder.Create(row => new Pallet
{
PalletId = row.GetString("PALLET_ID"),
CartonCount = row.GetInteger("CARTONS_COUNT") ?? 0,
AreaCount = row.GetInteger("AREA_COUNT") ?? 0,
LocationCount = row.GetInteger("LOCATION_COUNT") ?? 0,
CartonVwhId = row.GetString("CTN_VWH_ID"),
CartonVwhCount = row.GetInteger("CTN_VWH_COUNT") ?? 0,
LocationId = row.GetString("LOCATION_ID"),
SkuCount = row.GetInteger("SKU_COUNT") ?? 0,
CartonQuality = row.GetString("QUALITY_CODE"),
CartonQualityCount = row.GetInteger("QUALITY_COUNT") ?? 0,
PalletArea = new Area
{
AreaId = row.GetString("CARTON_STORAGE_AREA"),
ShortName = row.GetString("SHORT_NAME")
},
PalletSku = 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"),
Quantity = row.GetInteger("TOTAL_QUANTITY") ?? 0
}
});
binder.Parameter("PALLET_ID", palletId)
.Parameter("CARTON_ID", cartonId);
++_queryCount;
var pallet = _db.ExecuteSingle(QUERY, binder);
if (pallet != null)
{
pallet.QueryTime = DateTime.Now;
}
return(pallet);
}
public ActiveCarton GetActiveCarton(string cartonId)
{
if (string.IsNullOrWhiteSpace(cartonId))
{
throw new ArgumentNullException("cartonId");
}
const string QUERY = @"WITH Q1 AS
(SELECT CTN.CARTON_ID AS CARTON_ID,
CTN.CARTON_STORAGE_AREA AS CARTON_STORAGE_AREA,
TIA.SHORT_NAME AS SHORT_NAME,
TIA.description AS description,
CTN.LOCATION_ID AS LOCATION_ID,
ctn.quality_code AS quality_code,
TQC.DESCRIPTION AS QUALITY_DESCRIPTION,
CTN.LAST_PULLED_DATE As LAST_PULLED_DATE,
NVL(tia.warehouse_location_id, msl.warehouse_location_id) As warehouse_location_id,
ctn.pallet_id As pallet_id,
ctn.price_season_code As Price_Season_Code,
ctn.sewing_plant_code As sewing_plant_code,
ctn.WORK_NEEDED_XML as WORK_NEEDED_XML,
TSP.SEWING_PLANT_NAME AS SEWING_PLANT_NAME,
ctn.vwh_id As vwh_id,
ctn.damage_code As damage_code,
ctn.suspense_date AS suspense_date,
ctn.unmatch_comment_user As unmatch_comment_user,
ctn.unmatch_reason As unmatch_reason,
CTNDET.Quantity As Quantity,
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,
SS.SHIPMENT_ID AS SHIPMENT_ID,
SS.SHIPMENT_DATE AS SHIPMENT_DATE,
B.UCC128_ID AS UCC128_ID
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 />TAB_SEWINGPLANT TSP
ON TSP.SEWING_PLANT_CODE = ctn.SEWING_PLANT_CODE
left outer join <proxy />tab_inventory_area tia
on tia.inventory_storage_area = ctn.carton_storage_area
left outer join <proxy />master_storage_location msl
on msl.storage_area = ctn.carton_storage_area AND msl.location_id = ctn.location_id
LEFT OUTER JOIN <proxy />src_carton_intransit SS
ON ctn.shipment_id = SS.SHIPMENT_ID
AND CTN.CARTON_ID = SS.CARTON_ID
LEFT OUTER JOIN <proxy />BOX B ON
CTN.CARTON_ID = B.CARTON_ID
LEFT OUTER JOIN <proxy />TAB_QUALITY_CODE TQC
ON CTN.QUALITY_CODE = TQC.QUALITY_CODE
WHERE CTN.CARTON_ID = :cartonId
),
Q2 AS
(SELECT MS.SKU_ID AS SKU_ID,
I.IA_ID AS IA_ID,
I.VWH_ID AS VWH_ID,
I.LOCATION_ID AS LOCATION_ID,
I.RESTOCK_AISLE_ID AS RESTOCK_AISLE,
I.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
MAX(IA.SHORT_DESCRIPTION) AS SHORT_NAME
FROM <proxy />IA IA
INNER JOIN <proxy />IALOC I
ON IA.IA_ID = I.IA_ID
INNER JOIN <proxy />MASTER_SKU MS
ON MS.UPC_CODE = I.ASSIGNED_UPC_CODE
WHERE MS.SKU_ID IN (SELECT SKU_ID FROM Q1)
AND I.VWH_ID IN (SELECT VWH_ID FROM Q1)
GROUP BY MS.SKU_ID,
I.VWH_ID,
I.RESTOCK_AISLE_ID,
I.LOCATION_ID,
I.IA_ID,
I.WAREHOUSE_LOCATION_ID)
SELECT Q1.CARTON_ID,
Q1.CARTON_STORAGE_AREA,
Q1.SHORT_NAME,
Q1.description,
Q1.LOCATION_ID,
Q1.quality_code,
q1.QUALITY_DESCRIPTION,
Q1.LAST_PULLED_DATE,
Q1.warehouse_location_id,
Q1.pallet_id,
Q1.Price_Season_Code,
Q1.sewing_plant_code,
Q1.WORK_NEEDED_XML,
Q1.SEWING_PLANT_NAME,
Q1.vwh_id,
Q1.damage_code,
Q1.suspense_date,
Q1.unmatch_comment_user,
Q1.unmatch_reason,
Q1.Quantity,
Q1.style,
Q1.color,
Q1.dimension,
Q1.sku_size,
--Q1.upc_code,
Q1.sku_id,
Q1.SHIPMENT_ID,
Q1.SHIPMENT_DATE,
Q1.UCC128_ID,
Q2.LOCATION_ID AS ASSIGNED_LOCATION,
Q2.RESTOCK_AISLE AS ASSIGNED_RESTOCK_AISLE,
Q2.WAREHOUSE_LOCATION_ID AS ASSIGNED_WAREHOUSE_LOCATION,
-- Q2.IA_ID AS ASSIGNED_IAID,
Q2.SHORT_NAME AS ASSIGNED_IAID_SHORT_NAME
FROM Q1
LEFT OUTER JOIN Q2
ON Q1.SKU_ID = Q2.SKU_ID
AND Q1.VWH_ID = Q2.VWH_ID
";
var binder = SqlBinder.Create(row => new ActiveCarton
{
CartonId = row.GetString("CARTON_ID"),
//sku_id = row.GetInteger("sku_id") ?? 0,
Building = row.GetString("warehouse_location_id"),
PalletId = row.GetString("pallet_id"),
CartonAreaId = row.GetString("CARTON_STORAGE_AREA"),
AreaShortName = row.GetString("SHORT_NAME"),
AreaDescription = row.GetString("description"),
//Upc = row.GetString("upc_code"),
SkuId = row.GetInteger("sku_id"),
SewingPlantName = row.GetString("SEWING_PLANT_NAME"),
QualityCode = row.GetString("quality_code"),
QualityDescription = row.GetString("QUALITY_DESCRIPTION"),
LastPulledDate = row.GetDate("LAST_PULLED_DATE"),
Style = row.GetString("style"),
Color = row.GetString("color"),
Dimension = row.GetString("dimension"),
SkuSize = row.GetString("sku_size"),
Pieces = row.GetInteger("Quantity"),
SewingPlantCode = row.GetString("sewing_plant_code"),
LocationId = row.GetString("LOCATION_ID"),
PriceSeasonCode = row.GetString("Price_Season_Code"),
SuspenseDate = row.GetDate("suspense_date"),
UnmatchReason = row.GetString("unmatch_reason"),
UnmatchComment = row.GetString("unmatch_comment_user"),
DamageCode = row.GetString("damage_code"),
VwhId = row.GetString("vwh_id"),
ShipmentId = row.GetString("SHIPMENT_ID"),
ShipmentDate = row.GetDate("SHIPMENT_DATE"),
ReservedUccID = row.GetString("UCC128_ID"),
IsCartonMarkedForWork = !string.IsNullOrEmpty(row.GetString("WORK_NEEDED_XML")),
BestRestockAisleId = row.GetString("ASSIGNED_RESTOCK_AISLE"),
BestRestockBuildingId = row.GetString("ASSIGNED_WAREHOUSE_LOCATION"),
//AssignedIaId = row.GetString("ASSIGNED_IAID"),
BestRestockAreaShortName = row.GetString("ASSIGNED_IAID_SHORT_NAME"),
BestRestockLocationId = row.GetString("ASSIGNED_LOCATION")
}).Parameter("cartonId", cartonId);
return(_db.ExecuteSingle(QUERY, binder));
}
/// <summary>
/// This function is for revoking the passed roles from the passed users.
/// </summary>
/// <param name="usernames"></param>
/// <param name="roleNames"></param>
/// <remarks>
/// <para>
/// Empty user names and roles are silently ignored. All user names and roles are converted to upper case before they are processed.
/// </para>
/// <para>
/// The logged in user must have the rights to revoke roles. The logged in user must also have the delete rights on table upriv to delete user's priv.
/// Follwing are the scripts.
/// </para>
/// <code>
/// <![CDATA[
/// grant GRANT ANY ROLE to <user-name>
/// grant DELETE on URPIV to <user-name>
/// ]]>
/// </code>
/// </remarks>
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials to remove roles from users");
}
var joinUsersRoles = from user in usernames
from role in roleNames
where !string.IsNullOrEmpty(user) &&
!string.IsNullOrEmpty(role)
select new
{
Role = role.Trim().ToUpper(),
User = user.Trim().ToUpper()
};
const string QUERY_PRIV = @"DELETE <proxy />UPRIV WHERE ORACLE_USER_NAME ='{0}' AND PRIV_ID ='{1}'";
const string QUERY_ROLE = @"REVOKE {1} FROM {0}";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
foreach (var item in joinUsersRoles)
{
var query = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
ClearRoleCache(item.User);
try
{
db.ExecuteNonQuery(query, null);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1919:
case 942:
case 1031:
// 1919: Role does not exist
// 942 : UPRIV table does not exist. To us this means no rights to delete from UPRIV
// 1031: Rights to revoke the role are not avaliable.
throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to revoke this role", item.Role));
case 1951:
// Role not granted. Check whether the role has been granted inderectly.
const string QUERY_ROLE_PATH = @"
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 substr(path, instr(path, '/') + 1)
FROM Q1 Q
WHERE Q.PATH LIKE :username || '/%'
and q.granted_role = :rolename
";
// Execute this query as super user
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
//var binder = new SqlBinder<string>("Get Role Path");
var binder = SqlBinder.Create(row => row.GetString(0));
//binder.CreateMapper(QUERY_ROLE_PATH);
//binder.Query = QUERY_ROLE_PATH;
//binder.Factory = row => row.GetString();
binder.Parameter("username", item.User);
binder.Parameter("rolename", item.Role);
var path = db.ExecuteSingle <string>(QUERY_ROLE_PATH, binder);
if (!string.IsNullOrEmpty(path))
{
var roleToRevoke = path.Split('/').First();
throw new ProviderException(
string.Format(
"Role {0} has indirectly granted to user {1} and cannot be revoked directly. {2}/{0}. To revoke {0} role revoke {3} role.",
item.Role, item.User, path, roleToRevoke));
}
throw new ProviderException(ex.Message);
case 1917:
throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));
default:
throw;
}
}
}
}
}
/// <summary>
/// Retrieves current pallet info, if pallet data is found then it will also populates carton location
/// </summary>
public Pallet RetrievePalletInfo(string palletId)
{
if (string.IsNullOrEmpty(palletId))
{
throw new ArgumentNullException("PalletId is null");
}
const string QUERY = @"
begin
:result := <proxy />pkg_boxpick.get_pallet_info(apallet_id => :apallet_id);
end;
";
var binder = SqlBinder.Create(row => new Pallet
{
PalletId = row.GetString("PALLET_ID"),
TotalBoxCount = row.GetInteger("BOX_COUNT") ?? 0,
PickableBoxCount = row.GetInteger("PICKABLE_BOX_COUNT") ?? 0,
PickedBoxCount = row.GetInteger("PICKED_BOX_COUNT") ?? 0,
ReservedCartonCount = row.GetInteger("RESERVED_CARTON_COUNT") ?? 0,
BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"),
DestinationArea = row.GetString("DESTINATION_AREA"),
DestAreaShortName = row.GetString("DEST_AREA_SHORT_NAME"),
PickModeText = row.GetString("PICK_MODE"),
QueryTime = row.GetDate("CARTONPICKSTARTDATE"),
CartonSourceArea = row.GetString("CARTON_SOURCE_AREA"),
SourceAreaShortName = row.GetString("SOURCE_AREA_SHORT_NAME"),
CountRequiredVAS = row.GetInteger("REQUIRED_VAS_COUNT").Value,
BoxToPick = new Box
{
UccId = row.GetString("BOX_UCC128_ID"),
IaId = row.GetString("BOX_IA_ID"),
VwhId = row.GetString("BOX_VWHID"),
Pieces = row.GetInteger("BOX_EXPECTED_PIECES") ?? 0,
QualityCode = row.GetString("BOX_QUALITY_CODE"),
AssociatedCarton = new Carton
{
CartonId = row.GetString("BOX_CARTON_CARTON_ID"),
LocationId = row.GetString("BOX_CARTON_LOCATION_ID"),
VwhId = row.GetString("BOX_CARTON_VWHID"),
QualityCode = row.GetString("BOX_CARTON_QUALITYCODE"),
AssociatedPalletId = row.GetString("CARTON_PALLET"),
SkuInCarton = new Sku
{
Color = row.GetString("BOX_CARTON_COLOR"),
Dimension = row.GetString("BOX_CARTON_DIMENSION"),
SkuSize = row.GetString("BOX_CARTON_SKU_SIZE"),
Style = row.GetString("BOX_CARTON_STYLE")
}
},
SkuInBox = new Sku
{
Color = row.GetString("BOX_COLOR"),
Dimension = row.GetString("BOX_DIMENSION"),
SkuId = row.GetInteger("BOX_SKU_ID").Value,
SkuSize = row.GetString("BOX_SKU_SIZE"),
Style = row.GetString("BOX_STYLE")
}
}
}).Parameter("apallet_id", palletId).OutRefCursorParameter("result");
var pallet = _db.ExecuteSingle(QUERY, binder);
if (pallet != null)
{
pallet.CartonLocations = this.RetrieveCartonLocationsForPallet(palletId).ToArray();
}
return(pallet);
}
/// <summary>
/// return the customer info against scanned customer id.
/// </summary>
/// <param name="customerId"></param>
/// <returns></returns>
public Customer GetCustomerInfo(string customerId)
{
Contract.Assert(_db != null);
const string QUERY_CUSTOMER_DETAIL = @"
with PIVOT_CUST_SPLH(CUSTOMER_ID,
EDI753,
AMS,
SCO) AS
(SELECT *
FROM (SELECT CUSTOMER_ID, SPLH_ID, SPLH_VALUE FROM <proxy />CUSTSPLH) PIVOT(MAX(SPLH_VALUE) FOR SPLH_ID IN('$EDI753',
'$AMS',
'$SCO'))
where CUSTOMER_ID = :CUSTOMER_ID),
pivot_cust_doc(
NUMBER_OF_MPS,
MPS_SHORT_NAME,
NUMBER_OF_PSPB,
PSPB_SHORT_NAME,
NUMBER_OF_UCC,
UCC_SHORT_NAME,
NUMBER_OF_CCL,
CCL_SHORT_NAME,
NUMBER_OF_SHLBL,
SHLBL_SHORT_NAME
) AS
(
SELECT *
FROM (SELECT d.DOCUMENT_ID,
d.short_description as short_description,
case
when cd.document_id is not null then
cd.NUMBER_OF_COPIES
else
d.default_no_of_copies
end as NUMBER_OF_COPIES
FROM <proxy />doc d
left outer join <proxy/>CUSTDOC cd
on cd.document_id = d.document_id
and cd.customer_id = :CUSTOMER_ID
and cd.number_of_copies is not null) PIVOT(MAX(NUMBER_OF_COPIES), MAX(short_description) as x FOR DOCUMENT_ID IN('$MPS',
'$PSPB',
'$UCC',
'$CCL',
'$SHLBL'))
),
CUST_VAS_CONFIGURATION AS
(SELECT MCV.CUSTOMER_ID AS CUSTOMER_ID,
MAX(TV.DESCRIPTION) AS DESCRIPTION,
MAX(LISTAGG(MCV.VAS_ID || ': ' || TV.DESCRIPTION, ', ') WITHIN
GROUP(ORDER BY MCV.VAS_ID)) OVER(PARTITION BY MCV.CUSTOMER_ID) AS CUST_VAS
FROM <proxy />MASTER_CUSTOMER_VAS MCV
INNER JOIN <proxy />TAB_VAS TV
ON TV.VAS_CODE = MCV.VAS_ID
GROUP BY MCV.CUSTOMER_ID)
SELECT CST.CUSTOMER_ID AS CUSTOMER_ID,
CST.NAME AS NAME,
CST.CATEGORY AS CATEGORY,
CTYPE.description AS ACCOUNT_TYPE,
CST.CARRIER_ID AS CARRIER_ID,
M.DESCRIPTION AS CARRIER_DESCRIPTION,
CST.ASN_FLAG AS ASN_FLAG,
CST.DEFAULT_PICK_MODE AS DEFAULT_PICK_MODE,
CST.MIN_PIECES_PER_BOX AS MIN_PIECES_PER_BOX,
CST.MAX_PIECES_PER_BOX AS MAX_PIECES_PER_BOX,
PCS.EDI753 AS EDI753,
PCS.AMS AS AMS,
PCS.SCO AS SCO,
pcd.NUMBER_OF_MPS as NUMBER_OF_MPS,
pcd.MPS_SHORT_NAME as MPS_SHORT_NAME,
pcd.PSPB_SHORT_NAME as PSPB_SHORT_NAME,
pcd.UCC_SHORT_NAME as UCC_SHORT_NAME,
pcd.CCL_SHORT_NAME as CCL_SHORT_NAME,
pcd.SHLBL_SHORT_NAME as SHLBL_SHORT_NAME,
pcd.NUMBER_OF_PSPB as NUMBER_OF_PSPB,
pcd.NUMBER_OF_UCC as NUMBER_OF_UCC,
pcd.NUMBER_OF_CCL as NUMBER_OF_CCL,
pcd.NUMBER_OF_SHLBL as NUMBER_OF_SHLBL,
CVC.CUST_VAS AS CUST_VAS
FROM <proxy />CUST CST
LEFT OUTER JOIN CUST_VAS_CONFIGURATION CVC
ON CST.CUSTOMER_ID = CVC.CUSTOMER_ID
left outer join custtype CTYPE
ON CST.ACCOUNT_TYPE = CTYPE.customer_type
left outer JOIN PIVOT_CUST_SPLH PCS
ON CST.CUSTOMER_ID = PCS.CUSTOMER_ID
LEFT OUTER JOIN MASTER_CARRIER M
ON CST.CARRIER_ID = M.CARRIER_ID
cross join pivot_cust_doc pcd
WHERE CST.CUSTOMER_ID = :CUSTOMER_ID
";
var binder = SqlBinder.Create(row => new Customer
{
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("NAME"),
Category = row.GetString("CATEGORY"),
CustomerTypeDescription = row.GetString("ACCOUNT_TYPE"),
CarrierId = row.GetString("CARRIER_ID"),
CarrierDescription = row.GetString("CARRIER_DESCRIPTION"),
DefaultPickMode = row.GetString("DEFAULT_PICK_MODE"),
MinPiecesPerBox = row.GetInteger("MIN_PIECES_PER_BOX"),
MaxPiecesPerBox = row.GetInteger("MAX_PIECES_PER_BOX"),
AmsFlag = !string.IsNullOrEmpty(row.GetString("AMS")),
EdiFlag = !string.IsNullOrEmpty(row.GetString("EDI753")),
ScoFlag = !string.IsNullOrEmpty(row.GetString("SCO")),
Asn_flag = !string.IsNullOrEmpty(row.GetString("ASN_FLAG")),
NumberOfMps = row.GetInteger("NUMBER_OF_MPS"),
MpsShortName = row.GetString("MPS_SHORT_NAME"),
NumberOfPspb = row.GetInteger("NUMBER_OF_PSPB"),
PspbShortName = row.GetString("PSPB_SHORT_NAME"),
NumberOfUcc = row.GetInteger("NUMBER_OF_UCC"),
UccShortName = row.GetString("UCC_SHORT_NAME"),
NumberOfCcl = row.GetInteger("NUMBER_OF_CCL"),
CclShortName = row.GetString("CCL_SHORT_NAME"),
NumberOfShlbl = row.GetInteger("NUMBER_OF_SHLBL"),
ShlblShortName = row.GetString("SHLBL_SHORT_NAME"),
CustVas = row.GetString("CUST_VAS")
}).Parameter("CUSTOMER_ID", customerId);
return(_db.ExecuteSingle(QUERY_CUSTOMER_DETAIL, binder));
}
/// <summary>
/// Returns full information about the passed user
/// </summary>
/// <param name="username"></param>
/// <param name="userIsOnline"></param>
/// <returns></returns>
/// <remarks>
/// The returned info contains audit log of the user as well
/// and also returned info contains session log of user.
/// Users who own schema objects are not visible to this function since our goal is to manage application users only. We do not want to manage
/// application owners.
/// </remarks>
public override MembershipUser GetUser(string username, bool userIsOnline)
{
if (string.IsNullOrEmpty(username))
{
throw new ArgumentNullException("username");
}
OracleMembershipUser user;
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
//db.ConnectionString = _connectionString;
//db.ProviderName = _providerName;
db.CreateConnection(_connectionString, string.Empty);
const string QUERY = @"
SELECT U.USERNAME AS USERNAME,
U.LOCK_DATE AS LOCK_DATE,
U.CREATED AS CREATION_DATE,
U.USER_ID AS USER_ID,
U.EXPIRY_DATE AS EXPIRYDATE
FROM DBA_USERS U
WHERE U.USERNAME = :USERNAME
AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)";
//var binder = new SqlBinder<OracleMembershipUser>("Querying User properties");
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("CREATION_DATE").Value,
passwordExpiryDate: src.GetDate("ExpiryDate") ?? DateTime.MinValue
));
binder.Parameter("username", username.ToUpper());
//binder.CreateMapper(QUERY, config => config.CreateMap<OracleMembershipUser>()
// .ConstructUsing(src => new OracleMembershipUser
// (
// userName: src.GetValue<string>("USERNAME"),
// providerUserKey: src.GetValue<int>("USER_ID").ToString(),
// lastLockoutDate: src.GetValue<DateTime>("LOCK_DATE"),
// createDate: src.GetValue<DateTime>("CREATION_DATE"),
// passwordExpiryDate: src.GetValue<DateTime>("ExpiryDate")
// )).ForAllMembers(opt => opt.Ignore()));
//binder.Query = QUERY;
//binder.Factory = src => new OracleMembershipUser(
// userName: src.GetString("USERNAME"),
// providerUserKey: src.GetInteger("USER_ID").ToString(),
// lastLockoutDate: src.GetDate("LOCK_DATE").Value,
// createDate: src.GetDate("CREATION_DATE").Value,
// passwordExpiryDate: src.GetDate("ExpiryDate").Value
// );
user = db.ExecuteSingle(QUERY, binder);
if (user != null)
{
user.AuditLog = DoGetUserAudit(username, db);
user.Sessions = DoGetUserSessions(username, db);
}
}
return user;
}
/// <summary>
/// This function will provide information about intransit cartons.
/// <param name="shippingId"></param>
/// </summary>
public ParentShipment GetOutboundShipment(string shippingId, string mBolId)
{
Contract.Assert(_db != null);
const string QUERY = @"
SELECT MAX(S.SHIP_DATE) AS SHIP_DATE,
MAX(MC.CARRIER_ID) AS CARRIER_ID,
MAX(MC.DESCRIPTION) AS CARRIER_NAME,
MAX(S.SHIPPING_TYPE) AS SHIPPING_TYPE,
MAX(S.ARRIVAL_DATE) AS ARRIVAL_DATE,
MAX(C.CUSTOMER_ID) AS CUSTOMER_ID,
MAX(C.NAME) AS CUSTOMER_NAME,
MAX(S.CUSTOMER_DC_ID) AS CUSTOMER_DC_ID,
MAX(S.MBOL_ID) AS MBOL_ID,
MAX(S.ONHOLD_FLAG) AS ONHOLD_FLAG,
S.PARENT_SHIPPING_ID AS PARENT_SHIPPING_ID,
MAX(S.SHIPPING_ADDRESS.ADDRESS_LINE_1) AS ADDRESS_LINE_1,
MAX(S.SHIPPING_ADDRESS.ADDRESS_LINE_2) AS ADDRESS_LINE_2,
MAX(S.SHIPPING_ADDRESS.ADDRESS_LINE_3) AS ADDRESS_LINE_3,
MAX(S.SHIPPING_ADDRESS.ADDRESS_LINE_4) AS ADDRESS_LINE_4,
MAX(S.SHIPPING_ADDRESS.CITY) AS CITY,
MAX(S.SHIPPING_ADDRESS.STATE) AS STATE,
MAX(S.SHIPPING_ADDRESS.COUNTRY_CODE) AS COUNTRY_CODE,
MAX(S.PREPAID_CODE) AS PREPAID_CODE,
MAX(S.SHIPPING_ADDRESS.ZIP_CODE) AS ZIP_CODE,
MAX(WHL.ADDRESS_1) AS SHIP_FROM_ADDRESS_LINE_1,
MAX(WHL.ADDRESS_2) AS SHIP_FROM_ADDRESS_LINE_2,
MAX(WHL.ADDRESS_3) AS SHIP_FROM_ADDRESS_LINE_3,
MAX(WHL.ADDRESS_4) AS SHIP_FROM_ADDRESS_LINE_4,
MAX(WHL.CITY) AS SHIP_FROM_CITY,
MAX(WHL.STATE) AS SHIP_FROM_STATE,
MAX(WHL.ZIP_CODE) AS SHIP_FROM_ZIP_CODE,
MAX(WHL.COUNTRY_CODE) AS SHIP_FROM_COUNTRY_CODE,
MAX(WHL.COMPANY_NAME) AS SHIP_FROM_COMPANY_NAME,
MAX(A.APPOINTMENT_NUMBER) AS APPOINTMENT_NUMBER,
MAX(S.UPLOAD_DATE) AS UPLOAD_DATE
FROM <proxy />SHIP S
LEFT OUTER JOIN <proxy />CUST C
ON S.CUSTOMER_ID = C.CUSTOMER_ID
LEFT OUTER JOIN <proxy />MASTER_CARRIER MC
ON S.CARRIER_ID = MC.CARRIER_ID
LEFT OUTER JOIN <proxy />IA I
ON S.DOOR_IA_ID = I.IA_ID
LEFT OUTER JOIN <proxy />Tab_Warehouse_location WHL
ON I.WAREHOUSE_LOCATION_ID = WHL.WAREHOUSE_LOCATION_ID
LEFT OUTER JOIN <proxy />APPOINTMENT A
ON S.APPOINTMENT_ID = A.APPOINTMENT_ID
WHERE 1 = 1
<if>
AND S.SHIPPING_ID = :SHIPPING_ID
OR S.PARENT_SHIPPING_ID = :SHIPPING_ID
</if>
<if>
AND S.MBOL_ID = :MBOL_ID
</if>
GROUP BY S.PARENT_SHIPPING_ID
";
var binder = SqlBinder.Create(row => new ParentShipment
{
ToAddress = new[] {
row.GetString("ADDRESS_LINE_1"),
row.GetString("ADDRESS_LINE_2"),
row.GetString("ADDRESS_LINE_3"),
row.GetString("ADDRESS_LINE_4")
},
FromAddress = new[] {
row.GetString("SHIP_FROM_ADDRESS_LINE_1"),
row.GetString("SHIP_FROM_ADDRESS_LINE_2"),
row.GetString("SHIP_FROM_ADDRESS_LINE_3"),
row.GetString("SHIP_FROM_ADDRESS_LINE_4")
},
ZipCode = row.GetString("ZIP_CODE"),
City = row.GetString("CITY"),
State = row.GetString("STATE"),
Country = row.GetString("COUNTRY_CODE"),
ShippingDate = row.GetDate("SHIP_DATE"),
//Carrier = row.GetString("CARRIER"),
CarrierId = row.GetString("CARRIER_ID"),
CarrierName = row.GetString("CARRIER_NAME"),
ShippingType = row.GetString("SHIPPING_TYPE"),
ArrivalDate = row.GetDate("ARRIVAL_DATE"),
CustomerID = row.GetString("CUSTOMER_ID"),
CustomerName = row.GetString("CUSTOMER_NAME"),
CustomerDcId = row.GetString("CUSTOMER_DC_ID"),
MBolID = row.GetString("MBOL_ID"),
ParentShippingId = row.GetString("PARENT_SHIPPING_ID"),
FreightChargeTerm = row.GetString("PREPAID_CODE"),
FromZipCode = row.GetString("SHIP_FROM_ZIP_CODE"),
FromCity = row.GetString("SHIP_FROM_CITY"),
FromState = row.GetString("SHIP_FROM_STATE"),
FromCountry = row.GetString("SHIP_FROM_COUNTRY_CODE"),
FromCompany = row.GetString("SHIP_FROM_COMPANY_NAME"),
OnHoldFlag = !string.IsNullOrEmpty(row.GetString("ONHOLD_FLAG")),
IsTransferred = !string.IsNullOrEmpty(row.GetDate("UPLOAD_DATE").ToString()),
AppointmentNumber = row.GetInteger("APPOINTMENT_NUMBER")
}).Parameter("SHIPPING_ID", shippingId).Parameter("MBOL_ID", mBolId);
return(_db.ExecuteSingle(QUERY, binder));
}
