| Line 268... |
Line 268... |
| 268 |
" group by ii.itemId, ii.currentWarehouseId) as grouped " +
|
268 |
" group by ii.itemId, ii.currentWarehouseId) as grouped " +
|
| 269 |
"group by item_id, vendor_warehouse_id", resultSetMapping = "itemAvailabilityReservationMapping"),
|
269 |
"group by item_id, vendor_warehouse_id", resultSetMapping = "itemAvailabilityReservationMapping"),
|
| 270 |
|
270 |
|
| 271 |
@NamedNativeQuery(
|
271 |
@NamedNativeQuery(
|
| 272 |
name = "Order.selectTodayOrdersRBM",
|
272 |
name = "Order.selectTodayOrdersRBM",
|
| 273 |
query = "SELECT COALESCE(region_partner_map.auth_user_id, -1) AS auth_user_id," +
|
- |
|
| 274 |
" li.brand," +
|
273 |
query = "SELECT auth_user_id, brand, " +
|
| 275 |
" SUM(" +
|
- |
|
| 276 |
" CASE" +
|
274 |
" SUM(today_po_value) AS today_po_value, " +
|
| 277 |
" WHEN (o.created_timestamp BETWEEN :startDate AND :endDate and o.refund_timestamp is null)" +
|
- |
|
| 278 |
" AND o.refund_timestamp IS NULL" +
|
275 |
" SUM(today_billed_value) AS today_billed_value, " +
|
| 279 |
" THEN o.total_amount" +
|
276 |
" SUM(total_billed_value) AS total_billed_value, " +
|
| 280 |
" ELSE 0" +
|
277 |
" COUNT(DISTINCT customer_id) AS partner_count " +
|
| 281 |
" END" +
|
278 |
"FROM ( " +
|
| 282 |
" ) AS today_po_value," +
|
279 |
" SELECT COALESCE(region_partner_map.auth_user_id, -1) AS auth_user_id, " +
|
| 283 |
" SUM(" +
|
280 |
" li.brand, " +
|
| 284 |
" CASE" +
|
281 |
" o.customer_id, " +
|
| 285 |
" WHEN o.billing_timestamp IS NOT NULL" +
|
282 |
" CASE WHEN o.refund_timestamp IS NULL THEN o.total_amount ELSE 0 END AS today_po_value, " +
|
| 286 |
" AND (o.created_timestamp BETWEEN :startDate AND :endDate)" +
|
283 |
" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS today_billed_value, " +
|
| 287 |
" THEN o.total_amount" +
|
284 |
" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +
|
| 288 |
" ELSE 0" +
|
285 |
" FROM transaction.`order` o " +
|
| 289 |
" END" +
|
286 |
" JOIN transaction.lineitem li ON o.id = li.order_id " +
|
| 290 |
" ) AS today_billed_value," +
|
287 |
" JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +
|
| 291 |
" SUM(" +
|
288 |
" LEFT JOIN (SELECT p.auth_user_id, COALESCE(pr.fofo_id, ps.partner_id) AS final_retailer_id " +
|
| 292 |
" CASE" +
|
289 |
" FROM cs.partner_position ps " +
|
| 293 |
" WHEN o.billing_timestamp IS NOT NULL" +
|
290 |
" LEFT JOIN cs.partner_region pr ON ps.partner_id = 0 AND ps.region_id = pr.region_id " +
|
| 294 |
" THEN o.total_amount" +
|
291 |
" LEFT JOIN cs.position p ON p.id = ps.position_id " +
|
| - |
|
292 |
" WHERE p.category_id = :categoryId AND p.escalation_type = :escalationType) AS region_partner_map " +
|
| 295 |
" ELSE 0" +
|
293 |
" ON region_partner_map.final_retailer_id = fs.id " +
|
| 296 |
" END" +
|
294 |
" WHERE o.refund_timestamp IS NULL " +
|
| 297 |
" ) AS total_billed_value," +
|
295 |
" AND o.created_timestamp BETWEEN :startDate AND :endDate " +
|
| 298 |
" COUNT(DISTINCT o.customer_id) AS partner_count" +
|
296 |
" AND fs.internal = FALSE " +
|
| 299 |
" FROM transaction.`order` o" +
|
297 |
" AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +
|
| 300 |
" JOIN transaction.lineitem li" +
|
298 |
" UNION ALL " +
|
| 301 |
" ON o.id = li.order_id" +
|
299 |
" SELECT COALESCE(region_partner_map.auth_user_id, -1) AS auth_user_id, " +
|
| 302 |
" JOIN fofo.fofo_store fs" +
|
300 |
" li.brand, " +
|
| 303 |
" ON fs.id = o.customer_id" +
|
301 |
" o.customer_id, " +
|
| 304 |
" LEFT JOIN (SELECT ps.position_id," +
|
302 |
" 0 AS today_po_value, " +
|
| 305 |
" p.auth_user_id," +
|
303 |
" 0 AS today_billed_value, " +
|
| 306 |
" p.category_id," +
|
304 |
" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +
|
| 307 |
" p.escalation_type," +
|
305 |
" FROM transaction.`order` o " +
|
| 308 |
" COALESCE(pr.fofo_id, ps.partner_id) AS final_retailer_id," +
|
306 |
" JOIN transaction.lineitem li ON o.id = li.order_id " +
|
| 309 |
" ps.region_id" +
|
307 |
" JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +
|
| 310 |
" FROM cs.partner_position ps" +
|
308 |
" LEFT JOIN (SELECT p.auth_user_id, COALESCE(pr.fofo_id, ps.partner_id) AS final_retailer_id " +
|
| 311 |
" LEFT JOIN cs.partner_region pr" +
|
309 |
" FROM cs.partner_position ps " +
|
| 312 |
" ON ps.partner_id = 0 AND ps.region_id = pr.region_id" +
|
310 |
" LEFT JOIN cs.partner_region pr ON ps.partner_id = 0 AND ps.region_id = pr.region_id " +
|
| 313 |
" left join cs.position p on p.id = ps.position_id" +
|
311 |
" LEFT JOIN cs.position p ON p.id = ps.position_id " +
|
| 314 |
" where p.category_id = :categoryId" +
|
- |
|
| 315 |
" and p.escalation_type = :escalationType) AS region_partner_map" +
|
312 |
" WHERE p.category_id = :categoryId AND p.escalation_type = :escalationType) AS region_partner_map " +
|
| 316 |
" ON region_partner_map.final_retailer_id = fs.id" +
|
313 |
" ON region_partner_map.final_retailer_id = fs.id " +
|
| 317 |
" WHERE o.refund_timestamp IS NULL" +
|
314 |
" WHERE o.refund_timestamp IS NULL " +
|
| 318 |
" AND (" +
|
- |
|
| 319 |
" (o.created_timestamp BETWEEN :startDate AND :endDate)" +
|
315 |
" AND o.billing_timestamp BETWEEN :startDate AND :endDate " +
|
| 320 |
" OR (o.billing_timestamp BETWEEN :startDate AND :endDate)" +
|
316 |
" AND o.created_timestamp NOT BETWEEN :startDate AND :endDate " +
|
| 321 |
" )" +
|
- |
|
| 322 |
" AND fs.internal = FALSE" +
|
317 |
" AND fs.internal = FALSE " +
|
| 323 |
" AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
|
318 |
" AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +
|
| - |
|
319 |
") AS combined " +
|
| 324 |
"GROUP BY region_partner_map.auth_user_id, li.brand" +
|
320 |
"GROUP BY auth_user_id, brand " +
|
| 325 |
" ORDER BY region_partner_map.auth_user_id, li.brand",
|
321 |
"ORDER BY auth_user_id, brand",
|
| 326 |
resultSetMapping = "TodayPoByAuthUsers"
|
322 |
resultSetMapping = "TodayPoByAuthUsers"
|
| 327 |
),
|
323 |
),
|
| 328 |
|
324 |
|
| 329 |
@NamedNativeQuery(
|
325 |
@NamedNativeQuery(
|
| 330 |
name = "Order.selectTodayOrdersModelWise",
|
326 |
name = "Order.selectTodayOrdersModelWise",
|