| Line 19... |
Line 19... |
| 19 |
" cc.catalog_id," +
|
19 |
" cc.catalog_id," +
|
| 20 |
" c.model_number," +
|
20 |
" c.model_number," +
|
| 21 |
" c.brand," +
|
21 |
" c.brand," +
|
| 22 |
" COALESCE(SUM(os.current_qty), 0) AS available_stock," +
|
22 |
" COALESCE(SUM(os.current_qty), 0) AS available_stock," +
|
| 23 |
" COALESCE(sale1.last2daysSale, 0) AS last2DaysSoldQtySum," +
|
23 |
" COALESCE(sale1.last2daysSale, 0) AS last2DaysSoldQtySum," +
|
| 24 |
" COALESCE(sale2.last3rddaysale, 0) AS lastThirdDaySoldQty," +
|
- |
|
| 25 |
" (CASE" +
|
24 |
" (CASE" +
|
| 26 |
" WHEN ((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0) = 0)" +
|
25 |
" WHEN ((COALESCE(sale1.last2daysSale, 0) * :allocationConst) = 0)" +
|
| 27 |
" OR (COALESCE(SUM(os.current_qty), 0) = 0) THEN 2" +
|
26 |
" OR (COALESCE(SUM(os.current_qty), 0) = 0) THEN 2" +
|
| 28 |
" ELSE ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0))" +
|
27 |
" ELSE ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst))" +
|
| 29 |
" END) AS allocation ," +
|
28 |
" END) AS allocation" +
|
| 30 |
" (CASE" +
|
- |
|
| 31 |
" WHEN ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0)) > 0" +
|
- |
|
| 32 |
" THEN ROUND((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0))" +
|
- |
|
| 33 |
" - COALESCE(SUM(os.current_qty), 0)" +
|
- |
|
| 34 |
" WHEN ((COALESCE(sale1.last2daysSale, 0) * :allocationConst) + COALESCE(sale2.last3rddaysale, 0) = 0)" +
|
- |
|
| 35 |
" OR (COALESCE(SUM(os.current_qty), 0) = 0) THEN 2" +
|
- |
|
| 36 |
" ELSE 0" +
|
- |
|
| 37 |
" END) AS purchase_limit" +
|
- |
|
| 38 |
" FROM catalog.catagoriesd_catalog cc" +
|
29 |
" FROM catalog.catagoriesd_catalog cc" +
|
| 39 |
" LEFT JOIN fofo.fofo_opening_stock os ON os.catalog_id = cc.catalog_id AND os.fofo_id = :fofoId" +
|
30 |
" LEFT JOIN fofo.fofo_opening_stock os ON os.catalog_id = cc.catalog_id AND os.fofo_id = :fofoId" +
|
| 40 |
" LEFT JOIN (" +
|
31 |
" LEFT JOIN (" +
|
| 41 |
" SELECT o.fofo_id, ci.catalog_item_id, SUM(oi.quantity) AS last2daysSale" +
|
32 |
" SELECT o.fofo_id, ci.catalog_item_id, SUM(oi.quantity) AS last2daysSale" +
|
| 42 |
" FROM fofo.fofo_order o" +
|
33 |
" FROM fofo.fofo_order o" +
|
| 43 |
" JOIN fofo.fofo_order_item oi ON o.id = oi.order_id" +
|
34 |
" JOIN fofo.fofo_order_item oi ON o.id = oi.order_id" +
|
| 44 |
" JOIN catalog.item ci ON oi.item_id = ci.id" +
|
35 |
" JOIN catalog.item ci ON oi.item_id = ci.id" +
|
| 45 |
" WHERE DATE(o.create_timestamp) BETWEEN (CURDATE() - INTERVAL 2 DAY) AND CURDATE()" +
|
36 |
" WHERE DATE(o.create_timestamp) BETWEEN (CURDATE() - INTERVAL 2 DAY) AND CURDATE()" +
|
| 46 |
" GROUP BY o.fofo_id, ci.catalog_item_id" +
|
37 |
" GROUP BY o.fofo_id, ci.catalog_item_id" +
|
| 47 |
") sale1 ON sale1.catalog_item_id = os.catalog_id AND os.fofo_id = sale1.fofo_id" +
|
38 |
") sale1 ON sale1.catalog_item_id = os.catalog_id AND os.fofo_id = sale1.fofo_id" +
|
| 48 |
" LEFT JOIN (" +
|
- |
|
| 49 |
" SELECT o.fofo_id, ci.catalog_item_id, SUM(oi.quantity) AS last3rddaysale" +
|
- |
|
| 50 |
" FROM fofo.fofo_order o" +
|
- |
|
| 51 |
" JOIN fofo.fofo_order_item oi ON o.id = oi.order_id" +
|
- |
|
| 52 |
" JOIN catalog.item ci ON oi.item_id = ci.id" +
|
- |
|
| 53 |
" WHERE DATE(o.create_timestamp) = CURDATE() - INTERVAL 3 DAY" +
|
- |
|
| 54 |
" GROUP BY o.fofo_id, ci.catalog_item_id" +
|
- |
|
| 55 |
") sale2 ON sale2.catalog_item_id = os.catalog_id AND os.fofo_id = sale2.fofo_id" +
|
- |
|
| 56 |
" JOIN catalog.catalog c ON c.id = cc.catalog_id" +
|
39 |
" JOIN catalog.catalog c ON c.id = cc.catalog_id" +
|
| 57 |
" WHERE cc.end_date IS NULL AND cc.status = 'HID'" +
|
40 |
" WHERE cc.end_date IS NULL AND cc.status = 'HID'" +
|
| 58 |
"GROUP BY cc.catalog_id",
|
41 |
"GROUP BY cc.catalog_id",
|
| 59 |
resultSetMapping = "HidAllocation"),
|
42 |
resultSetMapping = "HidAllocation"),
|
| 60 |
|
43 |
|
| Line 69... |
Line 52... |
| 69 |
@ColumnResult(name = "catalog_id", type = Integer.class),
|
52 |
@ColumnResult(name = "catalog_id", type = Integer.class),
|
| 70 |
@ColumnResult(name = "model_number", type = String.class),
|
53 |
@ColumnResult(name = "model_number", type = String.class),
|
| 71 |
@ColumnResult(name = "brand ", type = String.class),
|
54 |
@ColumnResult(name = "brand ", type = String.class),
|
| 72 |
@ColumnResult(name = "available_stock ", type = Integer.class),
|
55 |
@ColumnResult(name = "available_stock ", type = Integer.class),
|
| 73 |
@ColumnResult(name = "last2DaysSoldQtySum ", type = Integer.class),
|
56 |
@ColumnResult(name = "last2DaysSoldQtySum ", type = Integer.class),
|
| 74 |
@ColumnResult(name = "lastThirdDaySoldQty ", type = Integer.class),
|
- |
|
| 75 |
@ColumnResult(name = "allocation ", type = Integer.class),
|
57 |
@ColumnResult(name = "allocation ", type = Integer.class)
|
| 76 |
@ColumnResult(name = "purchase_limit ", type = Integer.class),
|
- |
|
| 77 |
}
|
58 |
}
|
| 78 |
)}
|
59 |
)}
|
| 79 |
)
|
60 |
)
|
| 80 |
|
61 |
|
| 81 |
})
|
62 |
})
|