| Line 40... |
Line 40... |
| 40 |
") sale1 ON sale1.catalog_item_id = os.catalog_id AND os.fofo_id = sale1.fofo_id" +
|
40 |
") sale1 ON sale1.catalog_item_id = os.catalog_id AND os.fofo_id = sale1.fofo_id" +
|
| 41 |
" JOIN catalog.catalog c ON c.id = cc.catalog_id" +
|
41 |
" JOIN catalog.catalog c ON c.id = cc.catalog_id" +
|
| 42 |
" WHERE cc.end_date IS NULL AND cc.status = 'HID'" +
|
42 |
" WHERE cc.end_date IS NULL AND cc.status = 'HID'" +
|
| 43 |
"GROUP BY cc.catalog_id",
|
43 |
"GROUP BY cc.catalog_id",
|
| 44 |
resultSetMapping = "HidAllocation"),
|
44 |
resultSetMapping = "HidAllocation"),
|
| 45 |
@NamedNativeQuery(name = "Stock.PartnerHidAllocationForCatalogs",
|
- |
|
| 46 |
query = "SELECT" +
|
- |
|
| 47 |
" COALESCE(os.fofo_id,0) as fofo_id," +
|
- |
|
| 48 |
" cc.catalog_id," +
|
- |
|
| 49 |
" c.model_number," +
|
- |
|
| 50 |
" c.brand," +
|
- |
|
| 51 |
" COALESCE(SUM(os.current_qty), 0) AS available_stock," +
|
- |
|
| 52 |
" COALESCE(sale1.last2daysSale, 0) AS last2DaysSoldQtySum," +
|
- |
|
| 53 |
" (CASE" +
|
- |
|
| 54 |
" WHEN ((COALESCE(sale1.last2daysSale, 0) * :allocationConst) = 0)" +
|
- |
|
| 55 |
" OR (COALESCE(SUM(os.current_qty), 0) = 0) THEN 2" +
|
- |
|
| 56 |
" ELSE ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst))" +
|
- |
|
| 57 |
" END) AS allocation" +
|
- |
|
| 58 |
" FROM catalog.catagoriesd_catalog cc" +
|
- |
|
| 59 |
" LEFT JOIN fofo.fofo_opening_stock os ON os.catalog_id = cc.catalog_id AND os.fofo_id = :fofoId" +
|
- |
|
| 60 |
" LEFT JOIN (" +
|
- |
|
| 61 |
" SELECT o.fofo_id, ci.catalog_item_id, SUM(oi.quantity) AS last2daysSale" +
|
- |
|
| 62 |
" FROM fofo.fofo_order o" +
|
- |
|
| 63 |
" JOIN fofo.fofo_order_item oi ON o.id = oi.order_id" +
|
- |
|
| 64 |
" JOIN catalog.item ci ON oi.item_id = ci.id" +
|
- |
|
| 65 |
" WHERE o.fofo_id = :fofoId" +
|
- |
|
| 66 |
" AND o.create_timestamp >= CURDATE() - INTERVAL 2 DAY" +
|
- |
|
| 67 |
" GROUP BY o.fofo_id, ci.catalog_item_id" +
|
- |
|
| 68 |
") sale1 ON sale1.catalog_item_id = os.catalog_id AND os.fofo_id = sale1.fofo_id" +
|
- |
|
| 69 |
" JOIN catalog.catalog c ON c.id = cc.catalog_id" +
|
- |
|
| 70 |
" WHERE cc.end_date IS NULL AND cc.status = 'HID'" +
|
- |
|
| 71 |
" AND cc.catalog_id IN (:catalogIds)" +
|
- |
|
| 72 |
"GROUP BY cc.catalog_id",
|
- |
|
| 73 |
resultSetMapping = "HidAllocation"),
|
- |
|
| 74 |
@NamedNativeQuery(
|
45 |
@NamedNativeQuery(
|
| 75 |
name = "Stock.PartnerHidFastMovingRunningstock",
|
46 |
name = "Stock.PartnerHidFastMovingRunningstock",
|
| 76 |
query = "SELECT " +
|
47 |
query = "SELECT " +
|
| 77 |
" fos.catalog_id, " +
|
48 |
" fos.catalog_id, " +
|
| 78 |
" c.brand, " +
|
49 |
" c.brand, " +
|