Esempio n. 1
0
        /// <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));
        }
Esempio n. 2
0
        /// <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));
        }
Esempio n. 3
0
        /// <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));
        }
Esempio n. 4
0
        /// <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));
        }
Esempio n. 5
0
        /// <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));
        }
Esempio n. 6
0
        /// <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 &lt; 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);
        }
Esempio n. 8
0
        /// <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));
        }
Esempio n. 10
0
        /// <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();
        }
Esempio n. 11
0
        /// <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));
        }
Esempio n. 12
0
        /// <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);
        }
Esempio n. 13
0
        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));
        }
Esempio n. 14
0
        /// <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));
        }
Esempio n. 15
0
        /// <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 &lt; 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));
        }
Esempio n. 17
0
        /// <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));
        }
Esempio n. 18
0
        /// <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));
        }
Esempio n. 19
0
        /// <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));
        }
Esempio n. 20
0
        /// <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);
            }
        }
Esempio n. 21
0
        /// <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));
        }
Esempio n. 22
0
        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);
        }
Esempio n. 23
0
        /// <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));
        }
Esempio n. 24
0
        /// <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));
        }
Esempio n. 26
0
        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;
        }
Esempio n. 30
0
 /// <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;
     }
 }
Esempio n. 31
0
        /// <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));
        }
Esempio n. 32
0
        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));
        }
Esempio n. 33
0
    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);
        }
    }
Esempio n. 34
0
        /// <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;
            }
        }
Esempio n. 35
0
        /// <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));
        }