Třídová akce

Kód:

10

Tělo:

#ORA
Select
  *
From
(
  Select
#MS
Select
  TOP (:MaxRowsCount)   
#IB
Select
  First :MaxRowsCount
#ALL
  /* Stejné StoreCard_ID, QUnit, UnitRate, StoreBatch_ID - stupen 0, jinak stupen 10 */
  Case (
    Select
      count(*)
    From
      LogStoreContentsForStrat_VIEW LSC
    Where
      LSC.Parent_ID = SP.ID and
      (LSC.QuantityAwaited + LSC.Quantity) > 0 and
      LSC.StoreCard_ID = :StoreCard_ID and
      (
        (
          LSC.StoreBatch_ID is null and
          :SBatch_ID_NULL = 'A'
        ) or
        (LSC.StoreBatch_ID =  :StoreBatch_ID)
      ) and
      LSC.QUnit = :QUnit and
      (
        ((LSC.UnitRate - :UnitRate) < 0.0005) and
        ((:UnitRate - LSC.UnitRate) < 0.0005)
      )
  ) when 0 then 10 else 0 end
  +
  /* Stejné StoreCard_ID, QUnit, UnitRate, ale jiné StoreBatch_ID - zvyseni stupne o 100 */
  Case (
    Select
      count(*)
    From
      LogStoreContentsForStrat_VIEW LSC
    Where
      LSC.Parent_ID = SP.ID and
      (LSC.QuantityAwaited + LSC.Quantity) > 0 and
      LSC.StoreCard_ID = :StoreCard_ID and
      (
        (
         (LSC.StoreBatch_ID <> :StoreBatch_ID) and
         (LSC.StoreBatch_ID is not null) and
         (:SBatch_ID_NULL = 'N')
        ) or
        (
          LSC.StoreBatch_ID is null and
          :SBatch_ID_NULL = 'N'
        ) or
        (
          LSC.StoreBatch_ID is not null and
          :SBatch_ID_NULL = 'A'
        )
      ) and
      LSC.QUnit = :QUnit and
      (
        ((LSC.UnitRate - :UnitRate) < 0.0005) and
        ((:UnitRate - LSC.UnitRate) < 0.0005)
      )
  ) when 0 then 0 else 100 end
  +
  /* Stejné StoreCard - jiná Quantity nebo UnitRate - pridame 1000 */
  Case (
    Select
      count(*)
    From
      LogStoreContentsForStrat_VIEW LSC
    Where
      LSC.Parent_ID = SP.ID and
      (LSC.QuantityAwaited + LSC.Quantity) > 0 and
      LSC.StoreCard_ID = :StoreCard_ID and
      (
        LSC.QUnit <> :QUnit or
        (
          ((LSC.UnitRate - :UnitRate) > 0.0005) or
          ((:UnitRate - LSC.UnitRate) > 0.0005)
        )
      )
  ) when 0 then 0 else 1000 end
  +
  /* Jiné StoreCard - pridame 10 000 */
  Case (
    Select
      count(*)
    From
      LogStoreContentsForStrat_VIEW LSC
    Where
      LSC.Parent_ID = SP.ID and
      (LSC.QuantityAwaited + LSC.Quantity) > 0 and
      LSC.StoreCard_ID <> :StoreCard_ID
  ) when 0 then 0 else 10000 end
  +
  /* Prázdná pozice - zatím má 100 010, ted bude vice o 1 000 000 */
  Case (
    Select
      count(*)
    From
      LogStoreContentsForStrat_VIEW LSC
    Where
      LSC.Parent_ID = SP.ID and
      (LSC.QuantityAwaited + LSC.Quantity) > 0
  ) when 0 then 1000000 else 0 end
  +
  /* Pouze jiné StoreCard - zatim ma 110 010, ted bude mit vice o 10 000 000 */
  Case (
    Select
      count(*)
    From
      LogStoreContentsForStrat_VIEW LSC
    Where
      LSC.Parent_ID = SP.ID and
      (LSC.QuantityAwaited + LSC.Quantity) > 0 and
      LSC.StoreCard_ID <> :StoreCard_ID and
      not exists
      (
        Select
          LSC.ID
        From
          LogStoreContentsForStrat_VIEW LSC
        Where
          LSC.Parent_ID = SP.ID and
          LSC.StoreCard_ID = :StoreCard_ID and
          (LSC.QuantityAwaited + LSC.Quantity) > 0
      )
  ) when 0 then 0 else 10000000 end Score,
  SP.ID,
  SP.Code,
  SP.Accessibility,
  SP.BasicFreeSpace,
  SP.BasicFreeWeight,
  /* Zda je pozice preferovaná */
  Case (
    Select
      Count(P2.ID)
    From
      LogStorePreferences2 P2
      Left Join Firms F On P2.Firm_ID = F.ID
    Where
      (
        (P2.Parent_ID = PP.ID) or
        (P2.Parent_ID = PC.ID)
      ) and
      (P2.Strictly = 0) and
      (
        P2.StoreCard_ID = :StoreCard_ID or
        P2.StoreCardCategory_ID = :StoreCardCategory_ID or
        P2.StoreMenuItem_ID = :StoreMenuItem_ID or
        P2.Firm_ID = :Firm_ID or
        F.Firm_ID = :Firm_ID 
      )
  ) when 0 then 0 else 1 end Preferred  
From
  LogStorePositionsForStrat_VIEW SP
  LEFT JOIN LogStorePreferences PP ON SP.ID = PP.StorePosition_id
  LEFT JOIN LogStorePreferences PC ON SP.Code like PC.StorePositionAddress || '%'
Where
  ((SP.PositionType = 0) OR (:IsReservedForDoc = 'A')) and
  SP.Hidden = 'N' and
  SP.Frozen = 'N' and
  ((SP.Accessibility < :AccessibilityLimit) or (:IsAccessibilityLimitFilter = 'N')) and
  SP.Store_ID = :Store_ID and
  SP.BasicFreeWeight >= :UnitBasicWeight and
  SP.BasicFreeSpace >= :UnitBasicCapacity and
  (
    (Case When (Case When SP.Width > SP.Height then SP.Width Else SP.Height end) > SP.Depth then (Case When SP.Width > SP.Height then SP.Width Else SP.Height end) else SP.Depth end) *  
    Cast((Case SP.SizeUnit When 0 then 100 When 1 then 10 When 2 then 1 else 1 end) as Float)
  ) >= :UnitBasicMaxSize and
  (
    (:IsReservedForDoc = 'A' and SP.ReservedForDoc_ID = :ReservedForDocID and SP.ReservedForDocType = :ReservedForDocType) or
    (:IsReservedForDoc = 'N' and SP.ReservedForDoc_ID is null)
  )  
  {PREFSTRICTLY}
Order by
  1, 7 desc, SP.Accessibility
#ORA
)
where
  RowNum <= :MaxRowsCount

Generated by ABRA Software a.s. 27.10.2021 16:35:19