/// <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)); }