Example #1
0
        /// <summary>
        /// Returns the information of intransit carton.
        /// </summary>
        /// <param name="scan"></param>
        /// <returns></returns>

        public IntransitCarton GetIntransitCarton2(string scan)
        {
            const string QUERY = @"
                        with q1 AS
                         (    Select   SCI.VWH_ID AS VWH_ID,
                                 MS.UPC_CODE AS UPC_CODE,
                                 MS.STYLE AS STYLE_,
                                 MS.COLOR AS COLOR_,
                                 MS.DIMENSION AS DIMENSION_,
                                 MS.SKU_SIZE AS SKU_SIZE_,
                                 SCI.CARTON_ID AS CARTON_ID,
                                 SCI.IS_SHIPMENT_CLOSED,
                                 MS.SKU_ID AS SKU_ID,
                                 mss.spotcheck_percent AS spotcheck_percent,
                                 sci.received_date AS received_date,
                                 mss.spotcheck_flag as IsSpotCheck_Enabled
                            FROM <proxy />SRC_CARTON_INTRANSIT SCI
                            LEFT OUTER JOIN <proxy />MASTER_SKU MS
                              ON MS.STYLE = SCI.STYLE
                             AND MS.COLOR = SCI.COLOR
                             AND MS.DIMENSION = SCI.DIMENSION
                             AND MS.SKU_SIZE = SCI.SKU_SIZE
                        LEFT OUTER JOIN <proxy />MASTER_STYLE M
                              ON M.STYLE = MS.STYLE
                          LEFT OUTER JOIN <proxy />MASTER_SEWINGPLANT_STYLE MSS
                            ON (SCI.STYLE = MSS.STYLE OR MSS.STYLE = '.')
                           AND (SCI.SEWING_PLANT_CODE = MSS.SEWING_PLANT_CODE OR
                               MSS.SEWING_PLANT_CODE = '.')
                           AND (SCI.COLOR = MSS.COLOR OR
                               MSS.COLOR = '.')
                           WHERE SCI.CARTON_ID = :carton_id and sci.received_date IS NULL)
                        select * from q1
                 ";

            var binder = SqlBinder.Create(row => new IntransitCarton()
            {
                Sku = row.GetInteger("SKU_ID") == null ? null : new Sku
                {
                    SkuId     = row.GetInteger("SKU_ID").Value,
                    Style     = row.GetString("STYLE_"),
                    Color     = row.GetString("COLOR_"),
                    Dimension = row.GetString("DIMENSION_"),
                    SkuSize   = row.GetString("SKU_SIZE_"),
                },
                CartonId         = row.GetString("CARTON_ID"),
                VwhId            = row.GetString("VWH_ID"),
                UpcCode          = row.GetString("UPC_CODE"),
                SpotCheckPercent = row.GetDecimal("spotcheck_percent"),
                //ReceivedDate = row.GetDate("received_date"),
                IsShipmentClosed   = row.GetString("IS_SHIPMENT_CLOSED") == "Y",
                IsSpotCheckEnabled = row.GetString("isspotcheck_enabled") == "Y"
            }).Parameter("carton_id", scan)
                         .OutRefCursorParameter("result");

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #2
0
        /// <summary>
        /// Getting Building of passed area
        /// </summary>
        /// <returns></returns>
        public string GetBuildingofArea(string areaId)
        {
            const string QUERY =
                @"SELECT TIA.Warehouse_Location_Id FROM <proxy />TAB_INVENTORY_AREA TIA
          WHERE TIA.INVENTORY_STORAGE_AREA = :areaId
        ";
            var binder = SqlBinder.Create(row => row.GetString("Warehouse_Location_Id")).Parameter("areaId", areaId);

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #3
0
        /// <summary>
        /// Returns information about a specific customer.
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public string GetCustomerName(string customerId)
        {
            const string QUERY  = @"
                                    SELECT CUST.NAME AS NAME
                                     FROM <proxy />MASTER_CUSTOMER CUST
                                    WHERE CUST.CUSTOMER_ID = :SEARCH
                                    ";
            var          binder = SqlBinder.Create(row => row.GetString("NAME"));

            binder.Parameter("SEARCH", customerId);
            return(_db.ExecuteSingle(QUERY, binder));
        }
        internal Sku GetSku(int skuId)
        {
            if (skuId == 0)
            {
                throw new ArgumentNullException("skuId");
            }
            const string QUERY =
                             @"
                            SELECT  MS.UPC_CODE AS UPC_CODE,
                                    MS.SKU_ID AS SKU_ID,
                                    MS.STYLE AS STYLE,
                                    MS.COLOR AS COLOR,
                                    MS.DIMENSION AS DIMENSION,
                                    MS.SKU_SIZE AS SKU_SIZE
                                FROM <proxy />MASTER_SKU MS
                              WHERE MS.SKU_ID = :SKU_ID
                            ";

            var binder = SqlBinder.Create(row => new Sku
                {
                    SkuId = row.GetInteger("SKU_ID").Value,
                    Style = row.GetString("STYLE"),
                    Color = row.GetString("COLOR"),
                    Dimension = row.GetString("DIMENSION"),
                    SkuSize = row.GetString("SKU_SIZE"),
                    UpcCode = row.GetString("UPC_CODE")
                }).Parameter("SKU_ID", skuId);
            return _db.ExecuteSingle(QUERY, binder);

        }
Example #5
0
        /// <summary>
        /// Returns information about a specific customer.
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public Customer GetCustomer(string customerId)
        {
            const string QUERY  = @"
                                    SELECT CUST.CUSTOMER_ID AS CUSTOMER_ID,
                                           CUST.NAME AS NAME,
                                           CUST.INACTIVE_FLAG as INACTIVE_FLAG
                                     FROM <proxy />MASTER_CUSTOMER CUST
                                    WHERE CUST.CUSTOMER_ID = :SEARCH
                                    ";
            var          binder = SqlBinder.Create(row => new Customer
            {
                CustomerId = row.GetString("CUSTOMER_ID"),
                Name       = row.GetString("NAME"),
                IsActive   = row.GetString("INACTIVE_FLAG") != "Y"
            });

            binder.Parameter("SEARCH", customerId);
            return(_db.ExecuteSingle(QUERY, binder));
        }
        internal string GetBuildingDescription(string buildingId)
        {
            const string QUERY  = @"
                SELECT TWL.DESCRIPTION           AS DESCRIPTION
                  FROM <proxy />TAB_WAREHOUSE_LOCATION TWL
                 WHERE TWL.WAREHOUSE_LOCATION_ID = :WAREHOUSE_LOCATION_ID
                ";
            var          binder = SqlBinder.Create(row => row.GetString("DESCRIPTION"))
                                  .Parameter("WAREHOUSE_LOCATION_ID", buildingId);

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #7
0
        /// <summary>
        /// FOR CATRON LOCATION INFORMATION
        /// </summary>
        /// <param name="locationId"></param>
        /// <returns></returns>
        public CartonLocation GetCartonLocationInfo(string locationId)
        {
            Contract.Assert(_db != null);
            const string QUERY  = @"
                            Select Msl.Location_Id        As Location_Id,
                                Mskuass.Style             As Assigned_Style,
                                Mskuass.Color             As Assigned_Color,
                                Mskuass.Dimension         As Assigned_Dimension,
                                Mskuass.Sku_Size          As Assigned_Sku_Size,
                                Msl.Storage_Area          As Storage_Area,
                                Msl.Warehouse_Location_Id As Warehouse_Location_Id,
                                Msl.Assigned_Sku_Id       As Assigned_Sku_Id,
                                tia.short_name            as short_name,
                                Msl.Assigned_Max_Cartons  As Assigned_Max_Cartons
                      From <proxy />Master_Storage_Location Msl
                      Left Outer Join <proxy />Master_Sku Mskuass
                        On Mskuass.Sku_Id = Msl.Assigned_Sku_Id
                    left outer join <proxy />tab_inventory_area tia
                        on msl.storage_area = tia.inventory_storage_area
                     Where Msl.Location_Id = :Location_Id
                    ";
            var          binder = SqlBinder.Create(row => new CartonLocation
            {
                LocationId  = row.GetString("Location_Id"),
                WhId        = row.GetString("Warehouse_Location_Id"),
                Capacity    = row.GetInteger("Assigned_Max_Cartons"),
                Area        = row.GetString("Storage_Area"),
                ShortName   = row.GetString("short_name"),
                AssignedSku = new SkuBase
                {
                    Style     = row.GetString("Assigned_Style"),
                    Color     = row.GetString("Assigned_Color"),
                    Dimension = row.GetString("Assigned_Dimension"),
                    SkuSize   = row.GetString("Assigned_Sku_Size"),
                    SkuId     = row.GetInteger("Assigned_Sku_Id") ?? 0
                }
            }).Parameter("Location_Id", locationId);

            return(_db.ExecuteSingle(QUERY, binder));
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="upcCode"></param>
        /// <returns></returns>
        public Sku GetSku(string upcCode)
        {
            const string QUERY = @"
         SELECT MSKU.SKU_ID    AS SKU_ID,
                MSKU.STYLE     AS STYLE,
                MSKU.COLOR     AS COLOR,
                MSKU.DIMENSION AS DIMENSION,
                MSKU.SKU_SIZE  AS SKU_SIZE
           FROM <proxy />MASTER_SKU MSKU
          WHERE MSKU.UPC_CODE = :upc_code
            ";

            var binder = SqlBinder.Create(row => new Sku()
            {
                SkuId     = row.GetInteger("SKU_ID").Value,
                Style     = row.GetString("STYLE"),
                Color     = row.GetString("COLOR"),
                Dimension = row.GetString("DIMENSION"),
                SkuSize   = row.GetString("SKU_SIZE")
            }).Parameter("upc_code", upcCode);

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #9
0
        /// <summary>
        /// Returns sku info against scanned UPC
        /// </summary>
        /// <param name="upc"></param>
        /// <returns>
        /// </returns>
        public Sku GetSku(int skuId)
        {
            Contract.Assert(_db != null);
            const string QUERY_SKU_DETAIL = @"
              SELECT MSKU.STYLE                   AS STYLE,
                     MSKU.COLOR                   AS COLOR,
                     MSKU.DIMENSION               AS DIMENSION,
                     MSKU.SKU_SIZE                AS SKU_SIZE,
                     MSKU.UPC_CODE                AS UPC_CODE,
                     MSKU.SKU_ID                  AS SKU_ID,
                     MSKU.RETAIL_PRICE            AS RETAIL_PRICE,
                     MSKU.PIECES_PER_PACKAGE      AS PIECES_PER_PACKAGE,
                     MSKU.ADDITIONAL_RETAIL_PRICE AS ADDITIONAL_RETAIL_PRICE,
                     MS.DESCRIPTION               AS DESCRIPTION,
                     MSKU.STANDARD_CASE_QTY       AS STANDARD_CASE_QTY
                FROM <proxy />MASTER_SKU MSKU
                 LEFT OUTER JOIN <proxy />MASTER_STYLE MS
                     ON MSKU.STYLE = MS.STYLE  
               WHERE MSKU.sku_id = :sku_id
 ";
            var          binder           = SqlBinder.Create(row => new Sku
            {
                SkuId                 = row.GetInteger("SKU_ID").Value,
                Style                 = row.GetString("STYLE"),
                Color                 = row.GetString("COLOR"),
                Dimension             = row.GetString("DIMENSION"),
                SkuSize               = row.GetString("SKU_SIZE"),
                Upc                   = row.GetString("UPC_CODE"),
                RetailPrice           = row.GetDecimal("RETAIL_PRICE"),
                PiecesPerPackage      = row.GetInteger("PIECES_PER_PACKAGE"),
                AdditionalRetailPrice = row.GetString("ADDITIONAL_RETAIL_PRICE"),
                Description           = row.GetString("DESCRIPTION"),
                StandardCaseQty       = row.GetInteger("STANDARD_CASE_QTY")
            }).Parameter("sku_id", skuId);

            return(_db.ExecuteSingle(QUERY_SKU_DETAIL, binder));
        }
Example #10
0
        /// <summary>
        /// FOR SKU AREA  INFORMATION
        /// </summary>
        /// <param name="iaId"></param>
        /// <returns></returns>
        public SkuArea GetSkuAreaInfo(string iaId)
        {
            Contract.Assert(_db != null);
            const string QUERY  = @"
SELECT IA.IA_ID AS IA_ID,
       MAX(IA.SHORT_NAME) AS SHORT_NAME,
       MAX(IA.SHORT_DESCRIPTION) AS SHORT_DESCRIPTION,
       MAX(IA.DEFAULT_IA_LOCATION) AS DEFAULT_IA_LOCATION,
       MAX(IA.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
       MAX(IA.PICKING_AREA_FLAG) AS PICKING_AREA_FLAG,
       MAX(IA.SHIPPING_AREA_FLAG) AS SHIPPING_AREA_FLAG,
       MAX(IA.PULL_CARTON_LIMIT) AS PULL_CARTON_LIMIT,
       COUNT(DISTINCT IAL.LOCATION_ID) AS NUMBER_OF_LOCATIONS,
       COUNT(IAL.ASSIGNED_UPC_CODE) AS NUMBER_OF_ASSIGNED_LOCATIONS
  FROM <proxy />IA IA
  LEFT OUTER JOIN <proxy />IALOC IAL
    ON IA.IA_ID = IAL.IA_ID
 WHERE IA.IA_ID = :IA_ID
 GROUP BY IA.IA_ID
";
            var          binder = SqlBinder.Create(row => new SkuArea
            {
                IaId              = row.GetString("IA_ID"),
                Description       = row.GetString("SHORT_DESCRIPTION"),
                DefaultLocation   = row.GetString("DEFAULT_IA_LOCATION"),
                WhId              = row.GetString("WAREHOUSE_LOCATION_ID"),
                PickingAreaFlag   = row.GetString("PICKING_AREA_FLAG"),
                ShipingAreaFlag   = row.GetString("SHIPPING_AREA_FLAG"),
                PullCartonLimit   = row.GetInteger("PULL_CARTON_LIMIT"),
                NumberOfLocations = row.GetInteger("NUMBER_OF_LOCATIONS"),
                AssignedLocations = row.GetInteger("NUMBER_OF_ASSIGNED_LOCATIONS"),
                ShortName         = row.GetString("SHORT_NAME")
            }).Parameter("IA_ID", iaId);

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #11
0
        /// <summary>
        ///  Gets the
        /// </summary>
        /// <param name="appointmentNo"></param>
        /// <returns></returns>
        public Appointment GetAppointmentDetails(int appointmentNo)
        {
            const string QUERY  = @"
                                    SELECT COUNT(DISTINCT(CASE
                                                    WHEN BOX.TRUCK_LOAD_DATE IS NOT NULL THEN
                                                     BOX.PALLET_ID END))                        AS LOADED_PALLET_COUNT,
                                   COUNT(DISTINCT BOX.PALLET_ID)                                AS TOTAL_PALLET_COUNT,
                                   COUNT(DISTINCT(CASE
                                                    WHEN BOX.SUSPENSE_DATE IS NOT NULL THEN
                                                     BOX.PALLET_ID END))                        AS SUSPENCE_PALLET_COUNT,
                                   COUNT(DISTINCT(CASE
                                                    WHEN BOX.PALLET_ID IS NULL THEN
                                                     BOX.UCC128_ID END))                        AS UNPALLETIZE_BOX_COUNT,
                                   COUNT(DISTINCT(CASE
                                                    WHEN BOX.TRUCK_LOAD_DATE IS NOT NULL THEN
                                                     BOX.UCC128_ID END))                        AS LOADED_BOX_COUNT,
                                   COUNT(DISTINCT BOX.UCC128_ID)                                AS TOTAL_BOX_COUNT,
                                    MAX(MS.BUILDING_ID)                                         AS BUILDING_ID,
                                    MAX(MS.PICKUP_DOOR)                                         AS PICKUP_DOOR,
                                    MAX(MS.CARRIER_ID)                                          AS CARRIER_ID
                              FROM <proxy />BOX BOX
                              LEFT OUTER JOIN <proxy />PS PS
                                ON PS.PICKSLIP_ID = BOX.PICKSLIP_ID
                              LEFT OUTER JOIN <proxy />SHIP SHIP
                                ON SHIP.SHIPPING_ID = PS.SHIPPING_ID
                              LEFT OUTER JOIN <proxy />APPOINTMENT MS
                                ON MS.APPOINTMENT_ID = SHIP.APPOINTMENT_ID
                             WHERE MS.APPOINTMENT_NUMBER = :APPOINTMENT_NUMBER
                               AND PS.TRANSFER_DATE IS NULL
                               AND BOX.STOP_PROCESS_DATE IS NULL
                               GROUP BY MS.APPOINTMENT_ID";
            var          binder = SqlBinder.Create(row => new Appointment
            {
                LoadedBoxCount         = row.GetInteger("LOADED_BOX_COUNT") ?? 0,
                LoadedPalletCount      = row.GetInteger("LOADED_PALLET_COUNT") ?? 0,
                PalletsInSuspenseCount = row.GetInteger("SUSPENCE_PALLET_COUNT") ?? 0,
                TotalPalletCount       = row.GetInteger("TOTAL_PALLET_COUNT") ?? 0,
                TotalBoxCount          = row.GetInteger("TOTAL_BOX_COUNT") ?? 0,
                UnPalletizeBoxCount    = row.GetInteger("UNPALLETIZE_BOX_COUNT") ?? 0,
                BuildingId             = row.GetString("BUILDING_ID"),
                DoorId    = row.GetString("PICKUP_DOOR"),
                CarrierId = row.GetString("CARRIER_ID")
            })
                                  .Parameter("APPOINTMENT_NUMBER", appointmentNo);

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #12
0
        /// <summary>
        /// Used to validated the UPC code
        /// </summary>
        /// <param name="upcCode"></param>
        /// <returns></returns>
        public Sku GetSkuFromUpc(string upcCode)
        {
            const string QUERY = @"
            SELECT MS.UPC_CODE  AS UPC_CODE,
                   MS.STYLE     AS STYLE,
                   MS.COLOR     AS COLOR,
                   MS.DIMENSION AS DIMENSION,
                   MS.SKU_SIZE  AS SKU_SIZE,
                   MS.SKU_ID    AS SKU_ID
              FROM <proxy />MASTER_SKU MS
             WHERE MS.UPC_CODE = :UPCCODE
               AND MS.INACTIVE_FLAG IS NULL

            UNION ALL

            SELECT MS.UPC_CODE,
                   MS.STYLE,
                   MS.COLOR,
                   MS.DIMENSION,
                   MS.SKU_SIZE,
                   MS.SKU_ID
              FROM <proxy />MASTER_CUSTOMER_SKU MCS
             INNER JOIN <proxy />MASTER_SKU MS
                ON MS.STYLE = MCS.STYLE
               AND MS.COLOR = MCS.COLOR
               AND MS.DIMENSION = MCS.DIMENSION
               AND MS.SKU_SIZE = MCS.SKU_SIZE
             WHERE MS.INACTIVE_FLAG IS NULL
               AND NVL(MCS.SCANNED_BAR_CODE, MCS.CUSTOMER_SKU_ID) = :UPCCODE
        ";

            Contract.Assert(_db != null);
            var binder = SqlBinder.Create(row => new Sku
            {
                SkuId     = row.GetInteger("SKU_ID") ?? 0,
                Style     = row.GetString("STYLE"),
                Color     = row.GetString("COLOR"),
                Dimension = row.GetString("DIMENSION"),
                SkuSize   = row.GetString("SKU_SIZE"),
                UpcCode   = row.GetString("UPC_CODE"),
            }).Parameter("UPCCODE", upcCode);

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #13
0
        /// <summary>
        /// Retrieves area whihc is for bad pitch boxes. We are retreiving it from the iaconfig $BADPITCH.
        /// </summary>
        /// <returns>Returns the area where under pitched boxes are sent</returns>
        public string GetBadPitchArea()
        {
            const string QUERY = @"
           SELECT IA_ID AS IA_ID
           FROM <proxy />IACONFIG IC 
            WHERE IC.IACONFIG_ID = '$BADPITCH'
            ";

            Contract.Assert(_db != null);

            var binder = SqlBinder.Create(row => row.GetString("IA_ID"));

            ++_queryCount;
            var strBadPitchArea = _db.ExecuteSingle(QUERY, binder);

            return(strBadPitchArea);
        }
 public Customer GetCustomer(string customerId)
 {
     const string QUERY =
                 @"SELECT CUST.CUSTOMER_ID AS CUSTOMER_ID,
                 CUST.NAME AS CUSTOMER_NAME
                 FROM <proxy />CUST CUST
                 WHERE CUST.INACTIVE_FLAG IS NULL
                 <if c='$customerId'>
                     AND CUST.CUSTOMER_ID =:customerId
                 </if>
     ";
     Contract.Assert(_db != null);
     var binder = SqlBinder.Create(row => new Customer
     {
         CustomerId = row.GetString("CUSTOMER_ID"),
         CustomerName = row.GetString("CUSTOMER_NAME")
     })
     .Parameter("customerId", customerId);
     return _db.ExecuteSingle(QUERY, binder);
 }
Example #15
0
        /// <summary>
        /// This function is for checking whether the passed role is a priv.
        /// </summary>
        /// <param name="roleName"></param>
        private bool IsRolePriv(string roleName)
        {
            if (string.IsNullOrWhiteSpace(roleName))
            {
                throw new ArgumentNullException("roleName");
            }
            if (!_privTablesExist)
            {
                // This cannot be a privilege
                return(false);
            }

            const string strQuery = @"
                SELECT PRIV.PRIV_ID FROM <proxy />PRIV PRIV WHERE PRIV.PRIV_ID = :rolename 
            ";
            //var binder = new SqlBinder<string>("Retreving priv.");
            var binder = SqlBinder.Create(row => row.GetString(0));

            binder.Parameter("rolename", roleName.ToUpper());
            //binder.CreateMapper(strQuery);
            //binder.Query = strQuery;
            //binder.Factory = row => row.GetString();
            using (var db = new OracleDatastore(HttpContext.Current.Trace))
            {
                db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
                string strPriv = "";
                try
                {
                    strPriv = db.ExecuteSingle(strQuery, binder);
                }
                catch (OracleDataStoreException ex)
                {
                    if (ex.OracleErrorNumber == 942)
                    {
                        // Table PRIV does not exist. Stay silent
                        _privTablesExist = false;
                    }
                }
                return(!string.IsNullOrEmpty(strPriv));
            }
        }
Example #16
0
        /// <summary>
        /// Returns carton count on the passed pallet
        /// </summary>
        /// <param name="palletId"></param>
        /// <returns></returns>
        public Pallet GetPallet(string palletId)
        {
            const string QUERY = @"
            SELECT COUNT(CTN.CARTON_ID)             AS CARTON_COUNT,
                   MAX(CTN.CARTON_STORAGE_AREA)     AS MAX_CARTON_STORAGE_AREA,
                   SYS.STRAGG(UNIQUE(CTN.PRICE_SEASON_CODE) || ' ') AS PRICE_SEASON_CODE
                FROM <proxy />SRC_CARTON CTN
                WHERE CTN.PALLET_ID = :PALLET_ID
            ";

            var binder = SqlBinder.Create(row => new Pallet
            {
                CartonCount     = row.GetInteger("CARTON_COUNT") ?? 0,
                MaxCartonArea   = row.GetString("MAX_CARTON_STORAGE_AREA"),
                PriceSeasonCode = row.GetString("PRICE_SEASON_CODE")
            }).Parameter("PALLET_ID", palletId);

            var pallet = _db.ExecuteSingle(QUERY, binder);

            pallet.PalletId = palletId;
            return(pallet);
        }
Example #17
0
        /// <summary>
        /// Retrive info for passed location.
        /// </summary>
        /// <param name="locationId"></param>
        /// <returns></returns>
        public Location GetLocation(string locationId)
        {
            if (string.IsNullOrWhiteSpace(locationId))
            {
                throw new ArgumentNullException("locationId");
            }
            const string QUERY  = @"
                    Select msl.storage_area AS storage_area,
                           tia.short_name   AS SHORT_NAME,
                           msl.travel_sequence AS travel_sequence,
                        msl.assigned_max_cartons AS assigned_max_cartons,
                           NVL(TIA.WAREHOUSE_LOCATION_ID, MSL.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
                           TIA.STORES_WHAT AS STORES_WHAT,
                           MSL.unavailable_flag AS unavailable_flag,
                           (select count(*)
                              from  <proxy />src_carton ctn
                             where ctn.location_id = :location_id) AS count_cartons_at_location
                      from  <proxy />master_storage_location msl
                     INNER JOIN  <proxy />TAB_INVENTORY_AREA TIA
                        ON TIA.INVENTORY_STORAGE_AREA = MSL.STORAGE_AREA
                     where msl.location_id = :location_id
";
            var          binder = SqlBinder.Create(row => new Location
            {
                AreaId          = row.GetString("storage_area"),
                AreaShortName   = row.GetString("SHORT_NAME"),
                TravelSequence  = row.GetInteger("travel_sequence"),
                BuildingId      = row.GetString("WAREHOUSE_LOCATION_ID"),
                StoresWhat      = row.GetString("STORES_WHAT"),
                LocationId      = locationId,
                UnavailableFlag = row.GetString("unavailable_flag") == "Y",
                CountCartons    = row.GetInteger("count_cartons_at_location"),
                MaxCartons      = row.GetInteger("assigned_max_cartons")
            }).Parameter("location_id", locationId);

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #18
0
        /// <summary>
        /// This function is for checking whether the passed role exists or not.
        /// </summary>
        /// <param name="roleName"></param>
        /// <exception cref="NotImplementedException"></exception>
        /// <returns>boolean</returns>
        public override bool RoleExists(string roleName)
        {
            if (string.IsNullOrWhiteSpace(roleName))
            {
                throw new ArgumentNullException("roleName");
            }
            const string QUERY_PRIV = @"SELECT PRIV_ID FROM <proxy />PRIV WHERE PRIV_ID = :rolename";
            const string QUERY_ROLE = @"SELECT ROLE AS ROLE FROM DBA_ROLES  WHERE ROLE = :rolename";

            var strQuery = IsRolePriv(roleName) ? QUERY_PRIV : QUERY_ROLE;

            //var binder = new SqlBinder<string>("Retreving users.");
            var binder = SqlBinder.Create(row => row.GetString(0));

            binder.Parameter("rolename", roleName.ToUpper());
            //binder.CreateMapper(strQuery);
            //binder.Query = strQuery;
            //binder.Factory = row => row.GetString();
            using (var db = new OracleDatastore(HttpContext.Current.Trace))
            {
                db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
                return(!string.IsNullOrEmpty(db.ExecuteSingle(strQuery, binder)));
            }
        }
Example #19
0
        public Box GetBoxOfUcc(string uccId)
        {
            if (string.IsNullOrWhiteSpace(uccId))
            {
                throw new ArgumentNullException("uccId");
            }
            Contract.Assert(_db != null);
            const string QUERY_BOX_DETAIL = @"
                        SELECT BOX.UCC128_ID                 AS UCC128_ID,
                               BOX.PICKSLIP_ID          AS PICKSLIP_ID,
                               --PS.PO_ID                 AS PO_ID,
                               BOX.IA_ID                AS IA_ID,
                               IA.SHORT_NAME            AS SHORT_NAME,
                               IA.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
                               IA.SHORT_DESCRIPTION     AS IA_SHORT_DESCRIPTION,
                               BOX.PALLET_ID            AS PALLET_ID,
                               BOX.VWH_ID               AS VWH_ID,
                               BOX.PRO_NUMBER           AS PRO_NUMBER,
                               PS.CUSTOMER_ID           AS CUSTOMER_ID,
                               BOX.STOP_PROCESS_DATE    AS STOP_PROCESS_DATE,
                               BOX.STOP_PROCESS_REASON    AS STOP_PROCESS_REASON,
                               CUST.NAME                AS NAME,
                               --PS.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
                               BOX.PITCHING_END_DATE    AS PITCHING_END_DATE,
                               BOX.VERIFY_DATE          AS VERIFY_DATE,
                               BOX.QC_DATE              AS QC_DATE,
                               PS.IS_RFIDTAGS_REQUIRED  AS IS_RFIDTAGS_REQUIRED,
                               --SUM(NVL(BD.EXPECTED_PIECES, BD.CURRENT_PIECES       AS EXPECTED_PIECES,
                               --SUM(BD.CURRENT_PIECES        AS CURRENT_PIECES,
                               BOX.SUSPENSE_DATE        AS SUSPENSE_DATE,
                               BOX.REJECTION_CODE       AS REJECTION_CODE, 
                               --COUNT(DISTINCT BD.UPC_CODE   AS SKU_IN_BOX,
                               PS.BUCKET_ID            AS BUCKET_ID,
                               BOX.CARTON_ID            AS CARTON_ID,
                               BOX.LAST_CCL_PRINTED_BY  AS LAST_CCL_PRINTED_BY,
                               BOX.LAST_UCC_PRINTED_BY  AS LAST_UCC_PRINTED_BY,
                               BOX.LAST_UCC_PRINT_DATE  AS LAST_UCC_PRINTED_DATE,
                               BOX.LAST_CCL_PRINT_DATE  AS LAST_CCL_PRINT_DATE,
                               PS.CUSTOMER_DC_ID        AS CUSTOMER_DC_ID,
                               PS.CUSTOMER_STORE_ID     AS CUSTOMER_STORE_ID,
                               PS.SHIPPING_ADDRESS.ADDRESS_LINE_1      AS ADDRESS_LINE_1,
                               PS.SHIPPING_ADDRESS.ADDRESS_LINE_2      AS ADDRESS_LINE_2,
                               PS.SHIPPING_ADDRESS.ADDRESS_LINE_3      AS ADDRESS_LINE_3,
                               PS.SHIPPING_ADDRESS.ADDRESS_LINE_4      AS ADDRESS_LINE_4,
                               PS.SHIPPING_ADDRESS.CITY                AS CITY,
                               PS.SHIPPING_ADDRESS.STATE               AS STATE,
                               c.document_id as catalog_document_id
                         FROM <proxy />BOX BOX
                         INNER JOIN <proxy />PS PS
                            ON BOX.PICKSLIP_ID = PS.PICKSLIP_ID
                          LEFT OUTER JOIN <proxy />CUST CUST
                            ON CUST.CUSTOMER_ID = PS.CUSTOMER_ID        
                          left outer join  <proxy />CUSTDOC C 
                            on     PS.CUSTOMER_ID = c.customer_id       
                            AND C.DOCUMENT_ID = '$CL'      
                          LEFT OUTER JOIN <proxy />IA IA
                            ON BOX.IA_ID = IA.IA_ID
                        WHERE  box.ucc128_id = :UCC_ID
                        ";

            var binder = SqlBinder.Create(row => new Box
            {
                Ucc128Id           = row.GetString("UCC128_ID"),
                PalletId           = row.GetString("PALLET_ID"),
                CartonId           = row.GetString("CARTON_ID"),
                IaId               = row.GetString("IA_ID"),
                RfidTagsRequired   = row.GetString("IS_RFIDTAGS_REQUIRED"),
                QcDate             = row.GetDate("QC_DATE"),
                VerificationDate   = row.GetDate("VERIFY_DATE"),
                PitchingEndDate    = row.GetDate("PITCHING_END_DATE"),
                VwhId              = row.GetString("VWH_ID"),
                ProNo              = row.GetString("PRO_NUMBER"),
                LastCclPrintedBy   = row.GetString("LAST_CCL_PRINTED_BY"),
                LastUccPrintedBy   = row.GetString("LAST_UCC_PRINTED_BY"),
                LastCclPrintedDate = row.GetDate("LAST_CCL_PRINT_DATE"),
                LastUccPrintedDate = row.GetDate("LAST_UCC_PRINTED_DATE"),
                RejectionCode      = row.GetString("REJECTION_CODE"),
                SuspenseDate       = row.GetDate("SUSPENSE_DATE"),
                StopProcessDate    = row.GetDate("STOP_PROCESS_DATE"),
                StopProcessReason  = row.GetString("STOP_PROCESS_REASON"),
                PickslipId         = row.GetLong("PICKSLIP_ID").Value,
                BucketId           = row.GetInteger("BUCKET_ID") ?? 0,
                CustomerDC         = row.GetString("CUSTOMER_DC_ID"),
                CustomerStore      = row.GetString("CUSTOMER_STORE_ID"),
                CustomerId         = row.GetString("CUSTOMER_ID"),
                CustomerName       = row.GetString("NAME"),
                ShortName          = row.GetString("SHORT_NAME"),
                IaShortDescription = row.GetString("IA_SHORT_DESCRIPTION"),
                Building           = row.GetString("WAREHOUSE_LOCATION_ID"),
                ToAddress          = new[] {
                    row.GetString("ADDRESS_LINE_1"),
                    row.GetString("ADDRESS_LINE_2"),
                    row.GetString("ADDRESS_LINE_3"),
                    row.GetString("ADDRESS_LINE_4")
                },
                ToCity            = row.GetString("CITY"),
                ToState           = row.GetString("STATE"),
                CatalogDocumentId = row.GetString("catalog_document_id")
            }).Parameter("UCC_ID", uccId);

            return(_db.ExecuteSingle(QUERY_BOX_DETAIL, binder));
        }
Example #20
0
        /// <summary>
        /// Returns full information about the passed user
        /// </summary>
        /// <param name="username"></param>
        /// <param name="userIsOnline"></param>
        /// <returns></returns>
        /// <remarks>
        /// The returned info contains audit log of the user as well
        /// and also returned info contains session log of user.
        /// Users who own schema objects are not visible to this function since our goal is to manage application users only. We do not want to manage
        /// application owners.
        /// </remarks>
        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            if (string.IsNullOrEmpty(username))
            {
                throw new ArgumentNullException("username");
            }

            OracleMembershipUser user;

            using (var db = new OracleDatastore(HttpContext.Current.Trace))
            {
                //db.ConnectionString = _connectionString;
                //db.ProviderName = _providerName;
                db.CreateConnection(_connectionString, string.Empty);
                const string QUERY = @"
                                        SELECT U.USERNAME    AS USERNAME,
                                               U.LOCK_DATE   AS LOCK_DATE,
                                               U.CREATED     AS CREATION_DATE,
                                               U.USER_ID     AS USER_ID,
                                               U.EXPIRY_DATE AS EXPIRYDATE
                                          FROM DBA_USERS U
                                         WHERE U.USERNAME = :USERNAME
                                           AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)";
                //var binder = new SqlBinder<OracleMembershipUser>("Querying User properties");
                var binder = SqlBinder.Create(src => new OracleMembershipUser(
                                                  userName: src.GetString("USERNAME"),
                                                  providerUserKey: src.GetInteger("USER_ID").ToString(),
                                                  lastLockoutDate: src.GetDate("LOCK_DATE") ?? DateTime.MinValue,
                                                  createDate: src.GetDate("CREATION_DATE").Value,
                                                  passwordExpiryDate: src.GetDate("ExpiryDate") ?? DateTime.MinValue
                                                  ));
                binder.Parameter("username", username.ToUpper());
                //binder.CreateMapper(QUERY, config => config.CreateMap<OracleMembershipUser>()
                //                                         .ConstructUsing(src => new OracleMembershipUser
                //                                                                    (
                //                                                                    userName: src.GetValue<string>("USERNAME"),
                //                                                                    providerUserKey: src.GetValue<int>("USER_ID").ToString(),
                //                                                                    lastLockoutDate: src.GetValue<DateTime>("LOCK_DATE"),
                //                                                                    createDate: src.GetValue<DateTime>("CREATION_DATE"),
                //                                                                    passwordExpiryDate: src.GetValue<DateTime>("ExpiryDate")
                //                                                                    )).ForAllMembers(opt => opt.Ignore()));

                //binder.Query = QUERY;
                //binder.Factory = src => new OracleMembershipUser(
                //                                                                    userName: src.GetString("USERNAME"),
                //                                                                    providerUserKey: src.GetInteger("USER_ID").ToString(),
                //                                                                    lastLockoutDate: src.GetDate("LOCK_DATE").Value,
                //                                                                    createDate: src.GetDate("CREATION_DATE").Value,
                //                                                                    passwordExpiryDate: src.GetDate("ExpiryDate").Value
                //                                                                    );
                user = db.ExecuteSingle(QUERY, binder);

                if (user != null)
                {
                    user.AuditLog = DoGetUserAudit(username, db);
                    user.Sessions = DoGetUserSessions(username, db);
                }
            }

            return(user);
        }
        /// <summary>
        /// This function is for checking whether the passed role exists or not.
        /// </summary>
        /// <param name="roleName"></param>
        /// <exception cref="NotImplementedException"></exception>
        /// <returns>boolean</returns>
        public override bool RoleExists(string roleName)
        {
            if (string.IsNullOrWhiteSpace(roleName))
            {
                throw new ArgumentNullException("roleName");
            }
            const string QUERY_PRIV = @"SELECT PRIV_ID FROM <proxy />PRIV WHERE PRIV_ID = :rolename";
            const string QUERY_ROLE = @"SELECT ROLE AS ROLE FROM DBA_ROLES  WHERE ROLE = :rolename";

            var strQuery = IsRolePriv(roleName) ? QUERY_PRIV : QUERY_ROLE;

            //var binder = new SqlBinder<string>("Retreving users.");
            var binder = SqlBinder.Create(row => row.GetString(0));
            binder.Parameter("rolename", roleName.ToUpper());
            //binder.CreateMapper(strQuery);
            //binder.Query = strQuery;
            //binder.Factory = row => row.GetString();
            using (var db = new OracleDatastore(HttpContext.Current.Trace))
            {
                db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
                return !string.IsNullOrEmpty(db.ExecuteSingle(strQuery, binder));
            }
        }
        /// <summary>
        /// This function is for checking whether the passed role is a priv.
        /// </summary>
        /// <param name="roleName"></param>
        private bool IsRolePriv(string roleName)
        {
            if (string.IsNullOrWhiteSpace(roleName))
            {
                throw new ArgumentNullException("roleName");
            }
            if (!_privTablesExist)
            {
                // This cannot be a privilege
                return false;
            }

            const string strQuery = @"
                SELECT PRIV.PRIV_ID FROM <proxy />PRIV PRIV WHERE PRIV.PRIV_ID = :rolename
            ";
            //var binder = new SqlBinder<string>("Retreving priv.");
            var binder = SqlBinder.Create(row => row.GetString(0));
            binder.Parameter("rolename", roleName.ToUpper());
            //binder.CreateMapper(strQuery);
            //binder.Query = strQuery;
            //binder.Factory = row => row.GetString();
            using (var db = new OracleDatastore(HttpContext.Current.Trace))
            {
                db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
                string strPriv = "";
                try
                {
                    strPriv = db.ExecuteSingle(strQuery, binder);
                }
                catch (OracleDataStoreException ex)
                {
                    if (ex.OracleErrorNumber == 942)
                    {
                        // Table PRIV does not exist. Stay silent
                        _privTablesExist = false;
                    }
                }
                return !string.IsNullOrEmpty(strPriv);
            }
        }
        /// <summary>
        /// Function return the Pickslip info.
        /// </summary>
        /// <returns></returns>
        public Pickslip GetActivePickslip(long pickslipId)
        {
            Contract.Assert(_db != null);
            const string QUERY_PICKSLIP_INFO = @"
                SELECT PS.PICKSLIP_ID            AS PICKSLIP_ID,
                MC.CARRIER_ID || '.: ' || MC.DESCRIPTION AS CARRIER_ID,
                       CST.CUSTOMER_ID           AS CUSTOMER_ID,
                       CST.NAME                  AS NAME,
                       PS.PO_ID                  AS PO_ID,                      
                       PS.EXPORT_FLAG            AS EXPORT_FLAG,                       
                       PS.ITERATION              AS ITERATION,                       
                       PS.DATE_CREATED           AS DATE_CREATED,
                       PS.PICKSLIP_IMPORT_DATE   AS PICKSLIP_IMPORT_DATE,
                       PS.PICKSLIP_CANCEL_DATE   AS PICKSLIP_CANCEL_DATE,
                       PS.TRANSFER_DATE          AS TRANSFER_DATE,
                       PS.CUSTOMER_STORE_ID      AS CUSTOMER_STORE_ID,
                       PS.CUSTOMER_DEPARTMENT_ID AS CUSTOMER_DEPARTMENT_ID,                       
                       PS.ERP_ID AS ERP_ID,
                       TRIM(PS.CUSTOMER_DC_ID)   AS CUSTOMER_DC_ID,
                       SHIP.SHIPPING_ID          AS SHIPPING_ID,
                       SHIP.SHIP_DATE            AS SHIP_DATE,
                       SHIP.SHIPPER_NAME         AS SHIPPER_NAME,
                       SHIP.ONHOLD_FLAG         AS ONHOLD_FLAG,
                       PO.DC_CANCEL_DATE         AS DC_CANCEL_DATE,
                       PO.START_DATE             AS START_DATE,
                       PO.CANCEL_DATE            AS CANCEL_DATE,
                       ps.TOTAL_QUANTITY_ORDERED AS TOTAL_QUANTITY_ORDERED,
                       B.BUCKET_ID              AS BUCKET_ID,
                       B.CREATED_BY              AS BUCKET_CREATED_BY,
                       B.DATE_CREATED            AS BUCKET_DATE_CREATED,
                       PS.SHIPPING_ADDRESS.ADDRESS_LINE_1 AS SHIPPING_ADDRESS_LINE_1,
                       PS.SHIPPING_ADDRESS.ADDRESS_LINE_2 AS SHIPPING_ADDRESS_LINE_2,
                       PS.SHIPPING_ADDRESS.ADDRESS_LINE_3 AS SHIPPING_ADDRESS_LINE_3,
                       PS.SHIPPING_ADDRESS.ADDRESS_LINE_4 AS SHIPPING_ADDRESS_LINE_4,
                       PS.SHIPPING_ADDRESS.CITY           AS SHIPPING_ADDRESS_CITY,
                       PS.SHIPPING_ADDRESS.STATE           AS SHIPPING_ADDRESS_STATE,
                       PS.SHIPPING_ADDRESS.ZIP_CODE        AS SHIPPING_ADDRESS_ZIP_CODE,
                       PS.SHIPPING_ADDRESS.COUNTRY_CODE    AS SHIPPING_ADDRESS_COUNTRY_CODE,
                       ps.vendor_number                    AS vendor_number,
                       CST.ASN_FLAG                        AS ASN_FLAG  
                  FROM <proxy />PS PS
                  LEFT OUTER JOIN <proxy />CUST CST
                  ON CST.CUSTOMER_ID = PS.CUSTOMER_ID
                  LEFT OUTER JOIN  <proxy />MASTER_CARRIER MC
                  ON  MC.CARRIER_ID=PS.CARRIER_ID 
                  LEFT OUTER JOIN <proxy />PO PO
                  ON PO.PO_ID = PS.PO_ID
                  AND PO.CUSTOMER_ID = PS.CUSTOMER_ID
                  AND PO.ITERATION = PS.ITERATION
                  LEFT OUTER JOIN <proxy />SHIP SHIP
                  ON SHIP.SHIPPING_ID = PS.SHIPPING_ID
                  LEFT OUTER JOIN <proxy />BUCKET B 
                  ON PS.BUCKET_ID = B.BUCKET_ID 
                  WHERE PS.PICKSLIP_ID = :PICKSLIP_ID
            ";

            var binder = SqlBinder.Create(row => new Pickslip
            {
                PickslipId           = row.GetLong("PICKSLIP_ID").Value,
                CarrierId            = row.GetString("CARRIER_ID"),
                ShipDate             = row.GetDate("ship_date"),
                PickslipCancelDate   = row.GetDate("PICKSLIP_CANCEL_DATE"),
                CreateDate           = row.GetDate("DATE_CREATED"),
                ImportDate           = row.GetDate("PICKSLIP_IMPORT_DATE"),
                TransferDate         = row.GetDate("TRANSFER_DATE"),
                CustomerDC           = row.GetString("CUSTOMER_DC_ID"),
                CustomerStore        = row.GetString("CUSTOMER_STORE_ID"),
                ShippingId           = row.GetString("SHIPPING_ID"),
                TotalQuantityOrdered = row.GetInteger("TOTAL_QUANTITY_ORDERED"),
                ExportFlag           = !string.IsNullOrWhiteSpace(row.GetString("EXPORT_FLAG")) ? "Yes" : "No",
                AsnFlag              = !string.IsNullOrEmpty(row.GetString("ASN_FLAG")),
                BucketId             = row.GetInteger("BUCKET_ID"),
                BucketCreatedBy      = row.GetString("BUCKET_CREATED_BY"),
                BucketCreatedOn      = row.GetDate("BUCKET_DATE_CREATED"),
                ShipperName          = row.GetString("SHIPPER_NAME"),
                ShipmentOnHold       = row.GetString("ONHOLD_FLAG"),
                CustomerDepartmentId = row.GetString("CUSTOMER_DEPARTMENT_ID"),
                VendorNumber         = row.GetString("vendor_number"),
                PoId         = row.GetString("PO_ID"),
                Iteration    = row.GetInteger("ITERATION").Value,
                CancelDate   = row.GetDate("CANCEL_DATE"),
                StartDate    = row.GetDate("START_DATE"),
                DcCancelDate = row.GetDate("DC_CANCEL_DATE"),
                CustomerId   = row.GetString("CUSTOMER_ID"),
                CustomerName = row.GetString("NAME"),
                ShipAddress  = new[] {
                    row.GetString("SHIPPING_ADDRESS_LINE_1"),
                    row.GetString("SHIPPING_ADDRESS_LINE_2"),
                    row.GetString("SHIPPING_ADDRESS_LINE_3"),
                    row.GetString("SHIPPING_ADDRESS_LINE_4")
                },
                ShipCity    = row.GetString("SHIPPING_ADDRESS_CITY"),
                ShipCountry = row.GetString("SHIPPING_ADDRESS_COUNTRY_CODE"),
                ShipState   = row.GetString("SHIPPING_ADDRESS_STATE"),
                ShipZipCode = row.GetString("SHIPPING_ADDRESS_ZIP_CODE"),
                ErpId       = row.GetString("ERP_ID")
            }).Parameter("PICKSLIP_ID", pickslipId);


            return(_db.ExecuteSingle(QUERY_PICKSLIP_INFO, binder));
        }
        /// <summary>
        /// This function is for revoking the passed roles from the passed users.
        /// </summary>
        /// <param name="usernames"></param>
        /// <param name="roleNames"></param>
        /// <remarks>
        /// <para>
        /// Empty user names and roles are silently ignored. All user names and roles are converted to upper case before they are processed.
        /// </para>
        /// <para>
        /// The logged in user must have the rights to revoke roles. The logged in user must also have the delete rights on table upriv to delete user's priv.
        /// Follwing are the scripts.
        /// </para>
        /// <code>
        /// <![CDATA[
        /// grant GRANT ANY ROLE to <user-name>
        /// grant DELETE on URPIV to <user-name>
        /// ]]>
        /// </code>
        /// </remarks>
        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            if (usernames == null)
            {
                throw new ArgumentNullException("usernames");
            }
            if (roleNames == null)
            {
                throw new ArgumentNullException("roleNames");
            }

            if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
            {
                throw new ProviderException("You must be logged in with proper credentials to remove roles from users");
            }

            var joinUsersRoles = from user in usernames
                                 from role in roleNames
                                 where !string.IsNullOrEmpty(user) &&
                                 !string.IsNullOrEmpty(role)
                                 select new
                                 {
                                     Role = role.Trim().ToUpper(),
                                     User = user.Trim().ToUpper()
                                 };

            const string QUERY_PRIV = @"DELETE <proxy />UPRIV WHERE ORACLE_USER_NAME ='{0}' AND PRIV_ID ='{1}'";
            const string QUERY_ROLE = @"REVOKE {1} FROM {0}";

            using (var db = new OracleDatastore(HttpContext.Current.Trace))
            {
                db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
                foreach (var item in joinUsersRoles)
                {
                    var query = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
                    ClearRoleCache(item.User);
                    try
                    {
                        db.ExecuteNonQuery(query, null);
                    }
                    catch (OracleDataStoreException ex)
                    {
                        switch (ex.OracleErrorNumber)
                        {
                            case 1919:
                            case 942:
                            case 1031:
                                // 1919: Role does not exist
                                // 942 : UPRIV table does not exist. To us this means no rights to delete from UPRIV
                                // 1031: Rights to revoke the role are not avaliable.
                                throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to revoke this role", item.Role));

                            case 1951:
                                // Role not granted. Check whether the role has been granted inderectly.
                                const string QUERY_ROLE_PATH = @"
                                                                WITH Q1(GRANTED_ROLE,
                                                                PATH) AS
                                                                 (SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
                                                                    FROM DBA_ROLE_PRIVS P
                                                                   INNER JOIN DBA_USERS U
                                                                      ON P.GRANTEE = U.USERNAME
                                                                  UNION ALL
                                                                  SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
                                                                    FROM DBA_ROLE_PRIVS P
                                                                   INNER JOIN Q1
                                                                      ON Q1.GRANTED_ROLE = P.GRANTEE
                                                                    LEFT OUTER JOIN DBA_USERS U
                                                                      ON P.GRANTEE = U.USERNAME
                                                                   WHERE U.USERNAME IS NULL)
                                                                SELECT substr(path, instr(path, '/') + 1)
                                                                  FROM Q1 Q
                                                                 WHERE Q.PATH LIKE :username || '/%'
                                                                   and q.granted_role = :rolename
                                                                ";
                                // Execute this query as super user
                                db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
                                //var binder = new SqlBinder<string>("Get Role Path");
                                var binder = SqlBinder.Create(row => row.GetString(0));
                                //binder.CreateMapper(QUERY_ROLE_PATH);
                                //binder.Query = QUERY_ROLE_PATH;
                                //binder.Factory = row => row.GetString();
                                binder.Parameter("username", item.User);
                                binder.Parameter("rolename", item.Role);
                                var path = db.ExecuteSingle<string>(QUERY_ROLE_PATH, binder);
                                if (!string.IsNullOrEmpty(path))
                                {
                                    var roleToRevoke = path.Split('/').First();
                                    throw new ProviderException(
                                        string.Format(
                                            "Role {0} has indirectly granted to user {1} and cannot be revoked directly. {2}/{0}. To revoke {0} role revoke {3} role.",
                                            item.Role, item.User, path, roleToRevoke));
                                }
                                throw  new ProviderException(ex.Message);
                            case 1917:
                                throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));

                            default:
                                throw;
                        }
                    }
                }
            }
        }
Example #25
0
        /// <summary>
        /// Gets details of  passed carton
        /// </summary>
        /// <param name="cartonId">string</param>
        /// <returns>Retuns RestockCarton object</returns>
        public RestockCarton GetCartonDetails(string cartonId)
        {
            const string QUERY = @"
                            SELECT 0 AS CARTON_TYPE,
                                   SC.CARTON_ID AS CARTON_ID,
MAX(sc.pallet_id) AS pallet_id,
                                   MAX(SC.QUALITY_CODE) AS QUALITY_CODE,
                                   MAX(SC.VWH_ID) AS VWH_ID,
                                   MAX(MSKU.SKU_ID) AS SKU_ID,
                                   MAX(MSKU.STYLE) AS STYLE,
                                   MAX(MSKU.COLOR) AS COLOR,
                                   MAX(MSKU.DIMENSION) AS DIMENSION,
                                   MAX(MSKU.SKU_SIZE) AS SKU_SIZE,
                                   MAX(MSKU.UPC_CODE) AS UPC_CODE,
                                   MAX(MS.LABEL_ID) AS LABEL_ID,
                                   MAX(SCD.QUANTITY) AS QUANTITY,
                                   COUNT(DISTINCT SCD.SKU_ID) AS NUMBER_OF_SKU,
                                   MAX(SC.WORK_NEEDED_XML) AS WORK_NEEDED_XML,
                                   MAX(SC.CARTON_STORAGE_AREA) AS CARTON_STORAGE_AREA,
                                   MAX(MSKU.PIECES_PER_PACKAGE) AS PIECES_PER_PACKAGE,
                                   MAX(MSKU.RETAIL_PRICE) AS RETAIL_PRICE
                              FROM <proxy />SRC_CARTON SC
                              LEFT OUTER JOIN <proxy />SRC_CARTON_DETAIL SCD
                                ON SCD.CARTON_ID = SC.CARTON_ID
                              LEFT OUTER JOIN <proxy />MASTER_SKU MSKU
                                ON SCD.SKU_ID = MSKU.SKU_ID
                              LEFT OUTER JOIN <proxy />MASTER_STYLE MS
                                ON MS.STYLE = MSKU.STYLE
                             WHERE SC.CARTON_ID = :CARTON_ID
                             GROUP BY SC.CARTON_ID
                UNION ALL
                        SELECT 1,
                               SC.CARTON_ID AS CARTON_ID,
NULL,
                               SC.QUALITY_CODE AS QUALITY_CODE,
                               SC.VWH_ID AS VWH_ID,
                               SC.SKU_ID AS SKU_ID,
                               NULL AS STYLE_,
                               NULL AS COLOR_,
                               NULL AS DIMENSION_,
                               NULL AS SKU_SIZE_,
                               SC.UPC_CODE AS UPC_CODE_,
                               NULL AS LABEL_ID_,
                               SC.TOTAL_CARTON_QUANTITY AS QUANTITY,
                               1 AS NUMBER_OF_SKU,
                               NULL AS WORK_NEEDED_XML,
                               NULL AS CARTON_STORAGE_AREA,
                               NULL AS PIECES_PER_PACKAGE_,
                               NULL AS RETAIL_PRICE_
                          FROM <proxy />SRC_OPEN_CARTON SC
                         WHERE SC.CARTON_ID = :CARTON_ID
                  ORDER BY 1";

            var binder = SqlBinder.Create(row => new RestockCarton
            {
                CartonId          = row.GetString("CARTON_ID"),
                CartonType        = row.GetInteger("carton_type") ?? 0,
                QualityCode       = row.GetString("QUALITY_CODE"),
                VwhId             = row.GetString("VWH_ID"),
                PiecesInCarton    = row.GetInteger("QUANTITY") ?? 0,
                SkuCount          = row.GetInteger("NUMBER_OF_SKU"),
                CartonStorageArea = row.GetString("CARTON_STORAGE_AREA"),
                IsWorkNeeded      = !string.IsNullOrEmpty(row.GetString("WORK_NEEDED_XML")),
                SkuId             = row.GetInteger("SKU_ID"),
                Style             = row.GetString("STYLE"),
                Color             = row.GetString("COLOR"),
                Dimension         = row.GetString("DIMENSION"),
                SkuSize           = row.GetString("SKU_SIZE"),
                UpcCode           = row.GetString("UPC_CODE"),
                LabelId           = row.GetString("LABEL_ID"),
                PiecesPerPackage  = row.GetInteger("PIECES_PER_PACKAGE") ?? 0,
                RetailPrice       = row.GetDecimal("RETAIL_PRICE"),
                PalletId          = row.GetString("pallet_id")
            });

            binder.Parameter("CARTON_ID", cartonId);
            return(_db.ExecuteSingle(QUERY, binder));
        }
        /// <summary>
        /// The passed id can be a pallet id or a carton id. In the latter case, the cartonId is used to deduce the pallet.
        /// </summary>
        /// <returns></returns>
        public Pallet GetPallet(string palletId, string cartonId)
        {
            const string QUERY  = @"
SELECT SC.PALLET_ID                             AS PALLET_ID,
       COUNT(SC.CARTON_ID)                      AS CARTONS_COUNT,
       COUNT(DISTINCT SC.CARTON_STORAGE_AREA)   AS AREA_COUNT,
       COUNT(DISTINCT SC.LOCATION_ID)           AS LOCATION_COUNT,
       MIN(SC.LOCATION_ID)                      AS LOCATION_ID,
       MIN(SC.CARTON_STORAGE_AREA)              AS CARTON_STORAGE_AREA,
       MIN(SC.QUALITY_CODE)                     AS QUALITY_CODE,
       MIN(SC.VWH_ID)                           AS CTN_VWH_ID,
       COUNT(DISTINCT SC.VWH_ID)                AS CTN_VWH_COUNT,
       COUNT(DISTINCT CTNDET.SKU_ID)            AS SKU_COUNT,
       COUNT(DISTINCT SC.QUALITY_CODE)          AS QUALITY_COUNT,
       MIN(CTNDET.SKU_ID)                       AS SKU_ID,
       MIN(MSKU.STYLE)                          AS STYLE,
       MIN(MSKU.COLOR)                          AS COLOR,
       MIN(MSKU.DIMENSION)                      AS DIMENSION,
       MIN(MSKU.SKU_SIZE)                       AS SKU_SIZE,
       MIN(MSKU.UPC_CODE)                       AS UPC_CODE,
       MIN(TIA.SHORT_NAME)                      AS SHORT_NAME,
       SUM(CTNDET.QUANTITY)                     AS TOTAL_QUANTITY
  FROM <proxy />SRC_CARTON SC
 INNER JOIN <proxy />SRC_CARTON_DETAIL CTNDET
    ON SC.CARTON_ID = CTNDET.CARTON_ID
 INNER JOIN <proxy />MASTER_SKU MSKU
    ON MSKU.SKU_ID = CTNDET.SKU_ID
 INNER JOIN <proxy />TAB_INVENTORY_AREA TIA
    ON TIA.INVENTORY_STORAGE_AREA = SC.CARTON_STORAGE_AREA
WHERE 1 = 1
<if>
     AND SC.PALLET_ID = :PALLET_ID
</if>
<if>
    AND SC.PALLET_ID IN (select ctn.PALLET_ID FROM <proxy />SRC_CARTON ctn WHERE ctn.CARTON_ID = :CARTON_ID)
</if>
   GROUP BY SC.PALLET_ID
        ";
            var          binder = SqlBinder.Create(row => new Pallet
            {
                PalletId           = row.GetString("PALLET_ID"),
                CartonCount        = row.GetInteger("CARTONS_COUNT") ?? 0,
                AreaCount          = row.GetInteger("AREA_COUNT") ?? 0,
                LocationCount      = row.GetInteger("LOCATION_COUNT") ?? 0,
                CartonVwhId        = row.GetString("CTN_VWH_ID"),
                CartonVwhCount     = row.GetInteger("CTN_VWH_COUNT") ?? 0,
                LocationId         = row.GetString("LOCATION_ID"),
                SkuCount           = row.GetInteger("SKU_COUNT") ?? 0,
                CartonQuality      = row.GetString("QUALITY_CODE"),
                CartonQualityCount = row.GetInteger("QUALITY_COUNT") ?? 0,
                PalletArea         = new Area
                {
                    AreaId    = row.GetString("CARTON_STORAGE_AREA"),
                    ShortName = row.GetString("SHORT_NAME")
                },
                PalletSku = row.GetInteger("SKU_ID") == null ? null : new Sku
                {
                    SkuId     = row.GetInteger("SKU_ID").Value,
                    Style     = row.GetString("STYLE"),
                    Color     = row.GetString("COLOR"),
                    Dimension = row.GetString("DIMENSION"),
                    SkuSize   = row.GetString("SKU_SIZE"),
                    UpcCode   = row.GetString("UPC_CODE"),
                    Quantity  = row.GetInteger("TOTAL_QUANTITY") ?? 0
                }
            });

            binder.Parameter("PALLET_ID", palletId)
            .Parameter("CARTON_ID", cartonId);

            ++_queryCount;
            var pallet = _db.ExecuteSingle(QUERY, binder);

            if (pallet != null)
            {
                pallet.QueryTime = DateTime.Now;
            }
            return(pallet);
        }
Example #27
0
        public ActiveCarton GetActiveCarton(string cartonId)
        {
            if (string.IsNullOrWhiteSpace(cartonId))
            {
                throw new ArgumentNullException("cartonId");
            }
            const string QUERY = @"WITH Q1 AS
            (SELECT CTN.CARTON_ID             AS CARTON_ID,
                   CTN.CARTON_STORAGE_AREA   AS CARTON_STORAGE_AREA,
                   TIA.SHORT_NAME            AS SHORT_NAME,
                   TIA.description           AS description, 
                   CTN.LOCATION_ID           AS LOCATION_ID,
                   ctn.quality_code          AS quality_code,
                   TQC.DESCRIPTION           AS QUALITY_DESCRIPTION,
                   CTN.LAST_PULLED_DATE      As LAST_PULLED_DATE,
                   NVL(tia.warehouse_location_id, msl.warehouse_location_id) As warehouse_location_id,
                   ctn.pallet_id             As pallet_id,
                   ctn.price_season_code     As Price_Season_Code,
                   ctn.sewing_plant_code     As sewing_plant_code,
                   ctn.WORK_NEEDED_XML       as WORK_NEEDED_XML,
                   TSP.SEWING_PLANT_NAME     AS SEWING_PLANT_NAME,
                   ctn.vwh_id                As vwh_id,
                   ctn.damage_code           As damage_code,
                    ctn.suspense_date        AS suspense_date,
                   ctn.unmatch_comment_user  As unmatch_comment_user,
                   ctn.unmatch_reason        As unmatch_reason,
                   CTNDET.Quantity           As Quantity,
                   ctndet.sku_id             AS sku_id,
                   msku.style                AS style,
                   msku.color                AS color,
                   msku.dimension            AS dimension,
                   msku.sku_size             AS sku_size,
                   msku.upc_code             AS upc_code,
                   SS.SHIPMENT_ID            AS SHIPMENT_ID,
                   SS.SHIPMENT_DATE          AS SHIPMENT_DATE,
                   B.UCC128_ID               AS UCC128_ID
              FROM <proxy />SRC_CARTON CTN
              LEFT OUTER JOIN <proxy />SRC_CARTON_DETAIL CTNDET
                ON CTN.CARTON_ID = CTNDET.CARTON_ID
              LEFT OUTER JOIN <proxy />MASTER_SKU MSKU
                ON CTNDET.SKU_ID = MSKU.SKU_ID
              LEFT OUTER JOIN <proxy />TAB_SEWINGPLANT TSP
                ON TSP.SEWING_PLANT_CODE = ctn.SEWING_PLANT_CODE
              left outer join <proxy />tab_inventory_area tia
                on tia.inventory_storage_area = ctn.carton_storage_area
              left outer join <proxy />master_storage_location msl
                on msl.storage_area = ctn.carton_storage_area AND msl.location_id = ctn.location_id
                LEFT OUTER JOIN <proxy />src_carton_intransit SS
                    ON ctn.shipment_id = SS.SHIPMENT_ID
                    AND CTN.CARTON_ID = SS.CARTON_ID
                LEFT OUTER JOIN <proxy />BOX B ON
                     CTN.CARTON_ID = B.CARTON_ID
                LEFT OUTER JOIN <proxy />TAB_QUALITY_CODE TQC 
                     ON CTN.QUALITY_CODE = TQC.QUALITY_CODE
             WHERE CTN.CARTON_ID = :cartonId
            ),
            Q2 AS
             (SELECT MS.SKU_ID               AS SKU_ID,
                     I.IA_ID                 AS IA_ID,
                     I.VWH_ID                AS VWH_ID,
                     I.LOCATION_ID           AS LOCATION_ID,
                     I.RESTOCK_AISLE_ID      AS RESTOCK_AISLE,
                     I.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID,
                     MAX(IA.SHORT_DESCRIPTION)      AS SHORT_NAME
                FROM <proxy />IA IA
               INNER JOIN <proxy />IALOC I
                  ON IA.IA_ID = I.IA_ID
               INNER JOIN <proxy />MASTER_SKU MS
                  ON MS.UPC_CODE = I.ASSIGNED_UPC_CODE
               WHERE MS.SKU_ID IN (SELECT SKU_ID FROM Q1)
                 AND I.VWH_ID IN (SELECT VWH_ID FROM Q1)    
               GROUP BY MS.SKU_ID,
                        I.VWH_ID,
                        I.RESTOCK_AISLE_ID,
                        I.LOCATION_ID,
                        I.IA_ID,
                        I.WAREHOUSE_LOCATION_ID)
            SELECT Q1.CARTON_ID,
                   Q1.CARTON_STORAGE_AREA,
                   Q1.SHORT_NAME,
                   Q1.description, 
                   Q1.LOCATION_ID,
                   Q1.quality_code,
                   q1.QUALITY_DESCRIPTION,
                   Q1.LAST_PULLED_DATE,
                   Q1.warehouse_location_id,
                   Q1.pallet_id,
                   Q1.Price_Season_Code,
                   Q1.sewing_plant_code,
                   Q1.WORK_NEEDED_XML,
                   Q1.SEWING_PLANT_NAME,
                   Q1.vwh_id,
                   Q1.damage_code,
                   Q1.suspense_date,
                   Q1.unmatch_comment_user,
                   Q1.unmatch_reason,
                   Q1.Quantity,
                   Q1.style,
                   Q1.color,
                   Q1.dimension,
                   Q1.sku_size,
                   --Q1.upc_code,
                   Q1.sku_id,
                   Q1.SHIPMENT_ID,
                   Q1.SHIPMENT_DATE,
                   Q1.UCC128_ID,
                   Q2.LOCATION_ID AS ASSIGNED_LOCATION,
                   Q2.RESTOCK_AISLE AS ASSIGNED_RESTOCK_AISLE,
                   Q2.WAREHOUSE_LOCATION_ID AS ASSIGNED_WAREHOUSE_LOCATION,
                  -- Q2.IA_ID AS ASSIGNED_IAID,
                   Q2.SHORT_NAME AS ASSIGNED_IAID_SHORT_NAME
              FROM Q1
              LEFT OUTER JOIN Q2
                ON Q1.SKU_ID = Q2.SKU_ID
               AND Q1.VWH_ID = Q2.VWH_ID
                         ";

            var binder = SqlBinder.Create(row => new ActiveCarton
            {
                CartonId = row.GetString("CARTON_ID"),
                //sku_id = row.GetInteger("sku_id") ?? 0,
                Building        = row.GetString("warehouse_location_id"),
                PalletId        = row.GetString("pallet_id"),
                CartonAreaId    = row.GetString("CARTON_STORAGE_AREA"),
                AreaShortName   = row.GetString("SHORT_NAME"),
                AreaDescription = row.GetString("description"),
                //Upc = row.GetString("upc_code"),
                SkuId                 = row.GetInteger("sku_id"),
                SewingPlantName       = row.GetString("SEWING_PLANT_NAME"),
                QualityCode           = row.GetString("quality_code"),
                QualityDescription    = row.GetString("QUALITY_DESCRIPTION"),
                LastPulledDate        = row.GetDate("LAST_PULLED_DATE"),
                Style                 = row.GetString("style"),
                Color                 = row.GetString("color"),
                Dimension             = row.GetString("dimension"),
                SkuSize               = row.GetString("sku_size"),
                Pieces                = row.GetInteger("Quantity"),
                SewingPlantCode       = row.GetString("sewing_plant_code"),
                LocationId            = row.GetString("LOCATION_ID"),
                PriceSeasonCode       = row.GetString("Price_Season_Code"),
                SuspenseDate          = row.GetDate("suspense_date"),
                UnmatchReason         = row.GetString("unmatch_reason"),
                UnmatchComment        = row.GetString("unmatch_comment_user"),
                DamageCode            = row.GetString("damage_code"),
                VwhId                 = row.GetString("vwh_id"),
                ShipmentId            = row.GetString("SHIPMENT_ID"),
                ShipmentDate          = row.GetDate("SHIPMENT_DATE"),
                ReservedUccID         = row.GetString("UCC128_ID"),
                IsCartonMarkedForWork = !string.IsNullOrEmpty(row.GetString("WORK_NEEDED_XML")),
                BestRestockAisleId    = row.GetString("ASSIGNED_RESTOCK_AISLE"),
                BestRestockBuildingId = row.GetString("ASSIGNED_WAREHOUSE_LOCATION"),
                //AssignedIaId = row.GetString("ASSIGNED_IAID"),
                BestRestockAreaShortName = row.GetString("ASSIGNED_IAID_SHORT_NAME"),
                BestRestockLocationId    = row.GetString("ASSIGNED_LOCATION")
            }).Parameter("cartonId", cartonId);

            return(_db.ExecuteSingle(QUERY, binder));
        }
Example #28
0
        /// <summary>
        /// This function is for revoking the passed roles from the passed users.
        /// </summary>
        /// <param name="usernames"></param>
        /// <param name="roleNames"></param>
        /// <remarks>
        /// <para>
        /// Empty user names and roles are silently ignored. All user names and roles are converted to upper case before they are processed.
        /// </para>
        /// <para>
        /// The logged in user must have the rights to revoke roles. The logged in user must also have the delete rights on table upriv to delete user's priv.
        /// Follwing are the scripts.
        /// </para>
        /// <code>
        /// <![CDATA[
        /// grant GRANT ANY ROLE to <user-name>
        /// grant DELETE on URPIV to <user-name>
        /// ]]>
        /// </code>
        /// </remarks>
        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            if (usernames == null)
            {
                throw new ArgumentNullException("usernames");
            }
            if (roleNames == null)
            {
                throw new ArgumentNullException("roleNames");
            }

            if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
            {
                throw new ProviderException("You must be logged in with proper credentials to remove roles from users");
            }

            var joinUsersRoles = from user in usernames
                                 from role in roleNames
                                 where !string.IsNullOrEmpty(user) &&
                                 !string.IsNullOrEmpty(role)
                                 select new
            {
                Role = role.Trim().ToUpper(),
                User = user.Trim().ToUpper()
            };

            const string QUERY_PRIV = @"DELETE <proxy />UPRIV WHERE ORACLE_USER_NAME ='{0}' AND PRIV_ID ='{1}'";
            const string QUERY_ROLE = @"REVOKE {1} FROM {0}";

            using (var db = new OracleDatastore(HttpContext.Current.Trace))
            {
                db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
                foreach (var item in joinUsersRoles)
                {
                    var query = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
                    ClearRoleCache(item.User);
                    try
                    {
                        db.ExecuteNonQuery(query, null);
                    }
                    catch (OracleDataStoreException ex)
                    {
                        switch (ex.OracleErrorNumber)
                        {
                        case 1919:
                        case 942:
                        case 1031:
                            // 1919: Role does not exist
                            // 942 : UPRIV table does not exist. To us this means no rights to delete from UPRIV
                            // 1031: Rights to revoke the role are not avaliable.
                            throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to revoke this role", item.Role));

                        case 1951:
                            // Role not granted. Check whether the role has been granted inderectly.
                            const string QUERY_ROLE_PATH = @"
                                                                WITH Q1(GRANTED_ROLE,
                                                                PATH) AS
                                                                 (SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
                                                                    FROM DBA_ROLE_PRIVS P
                                                                   INNER JOIN DBA_USERS U
                                                                      ON P.GRANTEE = U.USERNAME
                                                                  UNION ALL
                                                                  SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
                                                                    FROM DBA_ROLE_PRIVS P
                                                                   INNER JOIN Q1
                                                                      ON Q1.GRANTED_ROLE = P.GRANTEE
                                                                    LEFT OUTER JOIN DBA_USERS U
                                                                      ON P.GRANTEE = U.USERNAME
                                                                   WHERE U.USERNAME IS NULL)
                                                                SELECT substr(path, instr(path, '/') + 1)
                                                                  FROM Q1 Q
                                                                 WHERE Q.PATH LIKE :username || '/%'
                                                                   and q.granted_role = :rolename
                                                                ";
                            // Execute this query as super user
                            db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
                            //var binder = new SqlBinder<string>("Get Role Path");
                            var binder = SqlBinder.Create(row => row.GetString(0));
                            //binder.CreateMapper(QUERY_ROLE_PATH);
                            //binder.Query = QUERY_ROLE_PATH;
                            //binder.Factory = row => row.GetString();
                            binder.Parameter("username", item.User);
                            binder.Parameter("rolename", item.Role);
                            var path = db.ExecuteSingle <string>(QUERY_ROLE_PATH, binder);
                            if (!string.IsNullOrEmpty(path))
                            {
                                var roleToRevoke = path.Split('/').First();
                                throw new ProviderException(
                                          string.Format(
                                              "Role {0} has indirectly granted to user {1} and cannot be revoked directly. {2}/{0}. To revoke {0} role revoke {3} role.",
                                              item.Role, item.User, path, roleToRevoke));
                            }
                            throw  new ProviderException(ex.Message);

                        case 1917:
                            throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));

                        default:
                            throw;
                        }
                    }
                }
            }
        }
Example #29
0
        /// <summary>
        /// Retrieves current pallet info, if pallet data is found then it will also populates carton location
        /// </summary>
        public Pallet RetrievePalletInfo(string palletId)
        {
            if (string.IsNullOrEmpty(palletId))
            {
                throw new ArgumentNullException("PalletId is null");
            }

            const string QUERY  = @"
                begin
                  :result := <proxy />pkg_boxpick.get_pallet_info(apallet_id => :apallet_id);
                end;
                ";
            var          binder = SqlBinder.Create(row => new Pallet
            {
                PalletId            = row.GetString("PALLET_ID"),
                TotalBoxCount       = row.GetInteger("BOX_COUNT") ?? 0,
                PickableBoxCount    = row.GetInteger("PICKABLE_BOX_COUNT") ?? 0,
                PickedBoxCount      = row.GetInteger("PICKED_BOX_COUNT") ?? 0,
                ReservedCartonCount = row.GetInteger("RESERVED_CARTON_COUNT") ?? 0,
                BuildingId          = row.GetString("WAREHOUSE_LOCATION_ID"),
                DestinationArea     = row.GetString("DESTINATION_AREA"),
                DestAreaShortName   = row.GetString("DEST_AREA_SHORT_NAME"),
                PickModeText        = row.GetString("PICK_MODE"),
                QueryTime           = row.GetDate("CARTONPICKSTARTDATE"),
                CartonSourceArea    = row.GetString("CARTON_SOURCE_AREA"),
                SourceAreaShortName = row.GetString("SOURCE_AREA_SHORT_NAME"),
                CountRequiredVAS    = row.GetInteger("REQUIRED_VAS_COUNT").Value,
                BoxToPick           = new Box
                {
                    UccId            = row.GetString("BOX_UCC128_ID"),
                    IaId             = row.GetString("BOX_IA_ID"),
                    VwhId            = row.GetString("BOX_VWHID"),
                    Pieces           = row.GetInteger("BOX_EXPECTED_PIECES") ?? 0,
                    QualityCode      = row.GetString("BOX_QUALITY_CODE"),
                    AssociatedCarton = new Carton
                    {
                        CartonId           = row.GetString("BOX_CARTON_CARTON_ID"),
                        LocationId         = row.GetString("BOX_CARTON_LOCATION_ID"),
                        VwhId              = row.GetString("BOX_CARTON_VWHID"),
                        QualityCode        = row.GetString("BOX_CARTON_QUALITYCODE"),
                        AssociatedPalletId = row.GetString("CARTON_PALLET"),
                        SkuInCarton        = new Sku
                        {
                            Color     = row.GetString("BOX_CARTON_COLOR"),
                            Dimension = row.GetString("BOX_CARTON_DIMENSION"),
                            SkuSize   = row.GetString("BOX_CARTON_SKU_SIZE"),
                            Style     = row.GetString("BOX_CARTON_STYLE")
                        }
                    },
                    SkuInBox = new Sku
                    {
                        Color     = row.GetString("BOX_COLOR"),
                        Dimension = row.GetString("BOX_DIMENSION"),
                        SkuId     = row.GetInteger("BOX_SKU_ID").Value,
                        SkuSize   = row.GetString("BOX_SKU_SIZE"),
                        Style     = row.GetString("BOX_STYLE")
                    }
                }
            }).Parameter("apallet_id", palletId).OutRefCursorParameter("result");

            var pallet = _db.ExecuteSingle(QUERY, binder);

            if (pallet != null)
            {
                pallet.CartonLocations = this.RetrieveCartonLocationsForPallet(palletId).ToArray();
            }
            return(pallet);
        }
        /// <summary>
        /// return the customer info against scanned customer id.
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public Customer GetCustomerInfo(string customerId)
        {
            Contract.Assert(_db != null);
            const string QUERY_CUSTOMER_DETAIL = @"
                            with PIVOT_CUST_SPLH(CUSTOMER_ID,
                            EDI753,
                            AMS,
                            SCO) AS
                             (SELECT *
                                FROM (SELECT CUSTOMER_ID, SPLH_ID, SPLH_VALUE FROM <proxy />CUSTSPLH) PIVOT(MAX(SPLH_VALUE) FOR SPLH_ID IN('$EDI753',
                                                                                                                                  '$AMS',
                                                                                                                                  '$SCO'))
                               where CUSTOMER_ID = :CUSTOMER_ID),
                            pivot_cust_doc(
                            NUMBER_OF_MPS,
                            MPS_SHORT_NAME,
                            NUMBER_OF_PSPB,
                            PSPB_SHORT_NAME,
                            NUMBER_OF_UCC,
                            UCC_SHORT_NAME,
                            NUMBER_OF_CCL,
                            CCL_SHORT_NAME,
                            NUMBER_OF_SHLBL,
                            SHLBL_SHORT_NAME
                            ) AS
                             (

                            SELECT *
                              FROM (SELECT d.DOCUMENT_ID,
                             d.short_description as short_description,
                                           case
                                             when cd.document_id is not null then
                                              cd.NUMBER_OF_COPIES
                                             else
                                              d.default_no_of_copies
                                           end as NUMBER_OF_COPIES
                                      FROM <proxy />doc d
                                      left outer join <proxy/>CUSTDOC cd
                                        on cd.document_id = d.document_id
                                       and cd.customer_id = :CUSTOMER_ID
                                       and cd.number_of_copies is not null) PIVOT(MAX(NUMBER_OF_COPIES),  MAX(short_description) as x FOR DOCUMENT_ID IN('$MPS',
                                                                                                                                                        '$PSPB',
                                                                                                                                                        '$UCC',
                                                                                                                                                        '$CCL',
                                                                                                                                                        '$SHLBL'))
                               ),
                CUST_VAS_CONFIGURATION AS
                    (SELECT MCV.CUSTOMER_ID AS CUSTOMER_ID,
                         MAX(TV.DESCRIPTION) AS DESCRIPTION,
                         MAX(LISTAGG(MCV.VAS_ID || ': ' || TV.DESCRIPTION, ', ') WITHIN
                        GROUP(ORDER BY MCV.VAS_ID)) OVER(PARTITION BY MCV.CUSTOMER_ID) AS CUST_VAS
                       FROM <proxy />MASTER_CUSTOMER_VAS MCV
                            INNER JOIN <proxy />TAB_VAS TV
                             ON TV.VAS_CODE = MCV.VAS_ID
                         GROUP BY MCV.CUSTOMER_ID)
                            SELECT CST.CUSTOMER_ID        AS CUSTOMER_ID,
                                   CST.NAME               AS NAME,
                                   CST.CATEGORY           AS CATEGORY,
                                   CTYPE.description      AS ACCOUNT_TYPE,
                                   CST.CARRIER_ID         AS CARRIER_ID,
                                   M.DESCRIPTION          AS CARRIER_DESCRIPTION,
                                   CST.ASN_FLAG           AS ASN_FLAG,
                                   CST.DEFAULT_PICK_MODE  AS DEFAULT_PICK_MODE,
                                   CST.MIN_PIECES_PER_BOX AS MIN_PIECES_PER_BOX,
                                   CST.MAX_PIECES_PER_BOX AS MAX_PIECES_PER_BOX,
                                   PCS.EDI753             AS EDI753,
                                   PCS.AMS                AS AMS,
                                   PCS.SCO                AS SCO,
                                   pcd.NUMBER_OF_MPS      as NUMBER_OF_MPS,
                            pcd.MPS_SHORT_NAME as MPS_SHORT_NAME,
                            pcd.PSPB_SHORT_NAME as PSPB_SHORT_NAME,
                            pcd.UCC_SHORT_NAME as UCC_SHORT_NAME,
                            pcd.CCL_SHORT_NAME as CCL_SHORT_NAME,
                            pcd.SHLBL_SHORT_NAME as SHLBL_SHORT_NAME,
                                   pcd.NUMBER_OF_PSPB     as NUMBER_OF_PSPB,
                                   pcd.NUMBER_OF_UCC      as NUMBER_OF_UCC,
                                   pcd.NUMBER_OF_CCL      as NUMBER_OF_CCL,
                                   pcd.NUMBER_OF_SHLBL    as NUMBER_OF_SHLBL,
                                    CVC.CUST_VAS           AS CUST_VAS              
                              FROM <proxy />CUST CST
                            LEFT OUTER JOIN CUST_VAS_CONFIGURATION CVC
                                ON CST.CUSTOMER_ID = CVC.CUSTOMER_ID
                             left outer join custtype CTYPE
                                ON CST.ACCOUNT_TYPE = CTYPE.customer_type
                             left outer JOIN PIVOT_CUST_SPLH PCS
                                ON CST.CUSTOMER_ID = PCS.CUSTOMER_ID
                             LEFT OUTER JOIN MASTER_CARRIER M
                                ON CST.CARRIER_ID = M.CARRIER_ID
                            cross join  pivot_cust_doc pcd
                             WHERE CST.CUSTOMER_ID = :CUSTOMER_ID
            ";
            var          binder = SqlBinder.Create(row => new Customer
            {
                CustomerId              = row.GetString("CUSTOMER_ID"),
                CustomerName            = row.GetString("NAME"),
                Category                = row.GetString("CATEGORY"),
                CustomerTypeDescription = row.GetString("ACCOUNT_TYPE"),
                CarrierId               = row.GetString("CARRIER_ID"),
                CarrierDescription      = row.GetString("CARRIER_DESCRIPTION"),
                DefaultPickMode         = row.GetString("DEFAULT_PICK_MODE"),
                MinPiecesPerBox         = row.GetInteger("MIN_PIECES_PER_BOX"),
                MaxPiecesPerBox         = row.GetInteger("MAX_PIECES_PER_BOX"),
                AmsFlag        = !string.IsNullOrEmpty(row.GetString("AMS")),
                EdiFlag        = !string.IsNullOrEmpty(row.GetString("EDI753")),
                ScoFlag        = !string.IsNullOrEmpty(row.GetString("SCO")),
                Asn_flag       = !string.IsNullOrEmpty(row.GetString("ASN_FLAG")),
                NumberOfMps    = row.GetInteger("NUMBER_OF_MPS"),
                MpsShortName   = row.GetString("MPS_SHORT_NAME"),
                NumberOfPspb   = row.GetInteger("NUMBER_OF_PSPB"),
                PspbShortName  = row.GetString("PSPB_SHORT_NAME"),
                NumberOfUcc    = row.GetInteger("NUMBER_OF_UCC"),
                UccShortName   = row.GetString("UCC_SHORT_NAME"),
                NumberOfCcl    = row.GetInteger("NUMBER_OF_CCL"),
                CclShortName   = row.GetString("CCL_SHORT_NAME"),
                NumberOfShlbl  = row.GetInteger("NUMBER_OF_SHLBL"),
                ShlblShortName = row.GetString("SHLBL_SHORT_NAME"),
                CustVas        = row.GetString("CUST_VAS")
            }).Parameter("CUSTOMER_ID", customerId);

            return(_db.ExecuteSingle(QUERY_CUSTOMER_DETAIL, binder));
        }
        /// <summary>
        /// Returns full information about the passed user
        /// </summary>
        /// <param name="username"></param>
        /// <param name="userIsOnline"></param>
        /// <returns></returns>
        /// <remarks>
        /// The returned info contains audit log of the user as well
        /// and also returned info contains session log of user.
        /// Users who own schema objects are not visible to this function since our goal is to manage application users only. We do not want to manage
        /// application owners.
        /// </remarks>
        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            if (string.IsNullOrEmpty(username))
            {
                throw new ArgumentNullException("username");
            }

            OracleMembershipUser user;
            using (var db = new OracleDatastore(HttpContext.Current.Trace))
            {
                //db.ConnectionString = _connectionString;
                //db.ProviderName = _providerName;
                db.CreateConnection(_connectionString, string.Empty);
                const string QUERY = @"
                                        SELECT U.USERNAME    AS USERNAME,
                                               U.LOCK_DATE   AS LOCK_DATE,
                                               U.CREATED     AS CREATION_DATE,
                                               U.USER_ID     AS USER_ID,
                                               U.EXPIRY_DATE AS EXPIRYDATE
                                          FROM DBA_USERS U
                                         WHERE U.USERNAME = :USERNAME
                                           AND U.USERNAME NOT IN (SELECT OWNER FROM DBA_OBJECTS)";
                //var binder = new SqlBinder<OracleMembershipUser>("Querying User properties");
                var binder = SqlBinder.Create(src => new OracleMembershipUser(
                                                                                    userName: src.GetString("USERNAME"),
                                                                                    providerUserKey: src.GetInteger("USER_ID").ToString(),
                                                                                    lastLockoutDate: src.GetDate("LOCK_DATE") ?? DateTime.MinValue,
                                                                                    createDate: src.GetDate("CREATION_DATE").Value,
                                                                                    passwordExpiryDate: src.GetDate("ExpiryDate") ?? DateTime.MinValue
                                                                                    ));
                binder.Parameter("username", username.ToUpper());
                //binder.CreateMapper(QUERY, config => config.CreateMap<OracleMembershipUser>()
                //                                         .ConstructUsing(src => new OracleMembershipUser
                //                                                                    (
                //                                                                    userName: src.GetValue<string>("USERNAME"),
                //                                                                    providerUserKey: src.GetValue<int>("USER_ID").ToString(),
                //                                                                    lastLockoutDate: src.GetValue<DateTime>("LOCK_DATE"),
                //                                                                    createDate: src.GetValue<DateTime>("CREATION_DATE"),
                //                                                                    passwordExpiryDate: src.GetValue<DateTime>("ExpiryDate")
                //                                                                    )).ForAllMembers(opt => opt.Ignore()));

                //binder.Query = QUERY;
                //binder.Factory = src => new OracleMembershipUser(
                //                                                                    userName: src.GetString("USERNAME"),
                //                                                                    providerUserKey: src.GetInteger("USER_ID").ToString(),
                //                                                                    lastLockoutDate: src.GetDate("LOCK_DATE").Value,
                //                                                                    createDate: src.GetDate("CREATION_DATE").Value,
                //                                                                    passwordExpiryDate: src.GetDate("ExpiryDate").Value
                //                                                                    );
                user = db.ExecuteSingle(QUERY, binder);

                if (user != null)
                {
                    user.AuditLog = DoGetUserAudit(username, db);
                    user.Sessions = DoGetUserSessions(username, db);
                }
            }

            return user;
        }
Example #32
0
        /// <summary>
        /// This function will provide information about intransit cartons.
        ///  <param name="shippingId"></param>
        /// </summary>
        public ParentShipment GetOutboundShipment(string shippingId, string mBolId)
        {
            Contract.Assert(_db != null);
            const string QUERY = @"
            SELECT MAX(S.SHIP_DATE)                                AS SHIP_DATE,    
               MAX(MC.CARRIER_ID)                                   AS CARRIER_ID,
               MAX(MC.DESCRIPTION)                                   AS CARRIER_NAME,
               MAX(S.SHIPPING_TYPE)                            AS SHIPPING_TYPE,
               MAX(S.ARRIVAL_DATE)                             AS ARRIVAL_DATE,
               MAX(C.CUSTOMER_ID)                              AS CUSTOMER_ID,
               MAX(C.NAME)                                     AS CUSTOMER_NAME,
               MAX(S.CUSTOMER_DC_ID)                           AS CUSTOMER_DC_ID,
               MAX(S.MBOL_ID)                                  AS MBOL_ID,
               MAX(S.ONHOLD_FLAG)                              AS ONHOLD_FLAG, 
               S.PARENT_SHIPPING_ID                            AS PARENT_SHIPPING_ID,
               MAX(S.SHIPPING_ADDRESS.ADDRESS_LINE_1)          AS ADDRESS_LINE_1,
               MAX(S.SHIPPING_ADDRESS.ADDRESS_LINE_2)          AS ADDRESS_LINE_2,
               MAX(S.SHIPPING_ADDRESS.ADDRESS_LINE_3)          AS ADDRESS_LINE_3,
               MAX(S.SHIPPING_ADDRESS.ADDRESS_LINE_4)          AS ADDRESS_LINE_4,
               MAX(S.SHIPPING_ADDRESS.CITY)                    AS CITY,
               MAX(S.SHIPPING_ADDRESS.STATE)                   AS STATE,
               MAX(S.SHIPPING_ADDRESS.COUNTRY_CODE)            AS COUNTRY_CODE,
               MAX(S.PREPAID_CODE)                             AS PREPAID_CODE,
               MAX(S.SHIPPING_ADDRESS.ZIP_CODE)                AS ZIP_CODE,
               MAX(WHL.ADDRESS_1)                              AS SHIP_FROM_ADDRESS_LINE_1,
               MAX(WHL.ADDRESS_2)                              AS SHIP_FROM_ADDRESS_LINE_2,
               MAX(WHL.ADDRESS_3)                              AS SHIP_FROM_ADDRESS_LINE_3,
               MAX(WHL.ADDRESS_4)                              AS SHIP_FROM_ADDRESS_LINE_4,
               MAX(WHL.CITY)                                   AS SHIP_FROM_CITY,
               MAX(WHL.STATE)                                  AS SHIP_FROM_STATE,
               MAX(WHL.ZIP_CODE)                               AS SHIP_FROM_ZIP_CODE,
               MAX(WHL.COUNTRY_CODE)                           AS SHIP_FROM_COUNTRY_CODE,
               MAX(WHL.COMPANY_NAME)                           AS SHIP_FROM_COMPANY_NAME,
              MAX(A.APPOINTMENT_NUMBER)                        AS APPOINTMENT_NUMBER,
              MAX(S.UPLOAD_DATE)                               AS UPLOAD_DATE
          FROM <proxy />SHIP S
          LEFT OUTER JOIN <proxy />CUST C
            ON S.CUSTOMER_ID = C.CUSTOMER_ID
          LEFT OUTER JOIN <proxy />MASTER_CARRIER MC
            ON S.CARRIER_ID = MC.CARRIER_ID
         LEFT OUTER JOIN <proxy />IA I
            ON S.DOOR_IA_ID = I.IA_ID
          LEFT OUTER JOIN <proxy />Tab_Warehouse_location WHL
            ON I.WAREHOUSE_LOCATION_ID = WHL.WAREHOUSE_LOCATION_ID
          LEFT OUTER JOIN <proxy />APPOINTMENT A
            ON S.APPOINTMENT_ID = A.APPOINTMENT_ID
         WHERE 1 = 1
    <if>
         AND S.SHIPPING_ID = :SHIPPING_ID
         OR S.PARENT_SHIPPING_ID = :SHIPPING_ID
    </if>
    <if>
         AND S.MBOL_ID = :MBOL_ID
    </if>
         GROUP BY S.PARENT_SHIPPING_ID            
            ";

            var binder = SqlBinder.Create(row => new ParentShipment
            {
                ToAddress = new[] {
                    row.GetString("ADDRESS_LINE_1"),
                    row.GetString("ADDRESS_LINE_2"),
                    row.GetString("ADDRESS_LINE_3"),
                    row.GetString("ADDRESS_LINE_4")
                },
                FromAddress = new[] {
                    row.GetString("SHIP_FROM_ADDRESS_LINE_1"),
                    row.GetString("SHIP_FROM_ADDRESS_LINE_2"),
                    row.GetString("SHIP_FROM_ADDRESS_LINE_3"),
                    row.GetString("SHIP_FROM_ADDRESS_LINE_4")
                },

                ZipCode      = row.GetString("ZIP_CODE"),
                City         = row.GetString("CITY"),
                State        = row.GetString("STATE"),
                Country      = row.GetString("COUNTRY_CODE"),
                ShippingDate = row.GetDate("SHIP_DATE"),
                //Carrier = row.GetString("CARRIER"),
                CarrierId         = row.GetString("CARRIER_ID"),
                CarrierName       = row.GetString("CARRIER_NAME"),
                ShippingType      = row.GetString("SHIPPING_TYPE"),
                ArrivalDate       = row.GetDate("ARRIVAL_DATE"),
                CustomerID        = row.GetString("CUSTOMER_ID"),
                CustomerName      = row.GetString("CUSTOMER_NAME"),
                CustomerDcId      = row.GetString("CUSTOMER_DC_ID"),
                MBolID            = row.GetString("MBOL_ID"),
                ParentShippingId  = row.GetString("PARENT_SHIPPING_ID"),
                FreightChargeTerm = row.GetString("PREPAID_CODE"),
                FromZipCode       = row.GetString("SHIP_FROM_ZIP_CODE"),
                FromCity          = row.GetString("SHIP_FROM_CITY"),
                FromState         = row.GetString("SHIP_FROM_STATE"),
                FromCountry       = row.GetString("SHIP_FROM_COUNTRY_CODE"),
                FromCompany       = row.GetString("SHIP_FROM_COMPANY_NAME"),
                OnHoldFlag        = !string.IsNullOrEmpty(row.GetString("ONHOLD_FLAG")),
                IsTransferred     = !string.IsNullOrEmpty(row.GetDate("UPLOAD_DATE").ToString()),
                AppointmentNumber = row.GetInteger("APPOINTMENT_NUMBER")
            }).Parameter("SHIPPING_ID", shippingId).Parameter("MBOL_ID", mBolId);


            return(_db.ExecuteSingle(QUERY, binder));
        }