| Line 355... |
Line 355... |
| 355 |
" AND fs.internal = FALSE" +
|
355 |
" AND fs.internal = FALSE" +
|
| 356 |
" AND fs.active = TRUE" +
|
356 |
" AND fs.active = TRUE" +
|
| 357 |
" AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
|
357 |
" AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
|
| 358 |
"GROUP BY o.customer_id, i.catalog_item_id",
|
358 |
"GROUP BY o.customer_id, i.catalog_item_id",
|
| 359 |
resultSetMapping = "TodayPoByCatalog"
|
359 |
resultSetMapping = "TodayPoByCatalog"
|
| 360 |
)
|
360 |
),
|
| 361 |
|
- |
|
| 362 |
|
361 |
|
| - |
|
362 |
@NamedNativeQuery(
|
| - |
|
363 |
name = "Order.selectTodayOrdersNative",
|
| - |
|
364 |
query = "SELECT " +
|
| - |
|
365 |
" COALESCE(pr.region_id, fs.warehouse_id) AS warehouse_id, " +
|
| - |
|
366 |
" combined.brand, " +
|
| - |
|
367 |
" SUM(combined.today_po_value) AS today_po_value, " +
|
| - |
|
368 |
" SUM(combined.today_billed_value) AS today_billed_value, " +
|
| - |
|
369 |
" SUM(combined.total_billed_value) AS total_billed_value " +
|
| - |
|
370 |
"FROM ( " +
|
| - |
|
371 |
" SELECT " +
|
| - |
|
372 |
" o.customer_id, " +
|
| - |
|
373 |
" li.brand, " +
|
| - |
|
374 |
" CASE WHEN o.refund_timestamp IS NULL THEN o.total_amount ELSE 0 END AS today_po_value, " +
|
| - |
|
375 |
" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS today_billed_value, " +
|
| - |
|
376 |
" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +
|
| - |
|
377 |
" FROM transaction.`order` o " +
|
| - |
|
378 |
" JOIN transaction.lineitem li ON o.id = li.order_id " +
|
| - |
|
379 |
" JOIN fofo.fofo_store fs2 ON fs2.id = o.customer_id " +
|
| - |
|
380 |
" WHERE o.refund_timestamp IS NULL " +
|
| - |
|
381 |
" AND o.created_timestamp BETWEEN :startDate AND :endDate " +
|
| - |
|
382 |
" AND fs2.internal = FALSE " +
|
| - |
|
383 |
" AND li.brand NOT IN ('Smartdukaan', 'Dummy') " +
|
| - |
|
384 |
" UNION ALL " +
|
| - |
|
385 |
" SELECT " +
|
| - |
|
386 |
" o.customer_id, " +
|
| - |
|
387 |
" li.brand, " +
|
| - |
|
388 |
" 0 AS today_po_value, " +
|
| - |
|
389 |
" 0 AS today_billed_value, " +
|
| - |
|
390 |
" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +
|
| - |
|
391 |
" FROM transaction.`order` o " +
|
| - |
|
392 |
" JOIN transaction.lineitem li ON o.id = li.order_id " +
|
| - |
|
393 |
" JOIN fofo.fofo_store fs2 ON fs2.id = o.customer_id " +
|
| - |
|
394 |
" WHERE o.refund_timestamp IS NULL " +
|
| - |
|
395 |
" AND o.billing_timestamp BETWEEN :startDate AND :endDate " +
|
| - |
|
396 |
" AND o.created_timestamp NOT BETWEEN :startDate AND :endDate " +
|
| - |
|
397 |
" AND fs2.internal = FALSE " +
|
| - |
|
398 |
" AND li.brand NOT IN ('Smartdukaan', 'Dummy') " +
|
| - |
|
399 |
") AS combined " +
|
| - |
|
400 |
"JOIN fofo.fofo_store fs ON fs.id = combined.customer_id " +
|
| - |
|
401 |
"LEFT JOIN cs.partner_region pr ON pr.fofo_id = combined.customer_id AND pr.region_id = 18 " +
|
| - |
|
402 |
"GROUP BY COALESCE(pr.region_id, fs.warehouse_id), combined.brand " +
|
| - |
|
403 |
"ORDER BY warehouse_id, combined.brand",
|
| - |
|
404 |
resultSetMapping = "TodayPoByRegion"
|
| - |
|
405 |
)
|
| 363 |
|
406 |
|
| 364 |
})
|
407 |
})
|
| 365 |
|
408 |
|
| 366 |
@SqlResultSetMappings({
|
409 |
@SqlResultSetMappings({
|
| 367 |
@SqlResultSetMapping(name = "itemAvailabilityReservationMapping", classes = {@ConstructorResult(targetClass = WarehouseItemQtyModel.class,
|
410 |
@SqlResultSetMapping(name = "itemAvailabilityReservationMapping", classes = {@ConstructorResult(targetClass = WarehouseItemQtyModel.class,
|
| Line 405... |
Line 448... |
| 405 |
@ColumnResult(name = "quantity", type = Long.class),
|
448 |
@ColumnResult(name = "quantity", type = Long.class),
|
| 406 |
@ColumnResult(name = "today_po_value", type = Long.class),
|
449 |
@ColumnResult(name = "today_po_value", type = Long.class),
|
| 407 |
@ColumnResult(name = "today_billed_value", type = Long.class)
|
450 |
@ColumnResult(name = "today_billed_value", type = Long.class)
|
| 408 |
}
|
451 |
}
|
| 409 |
)
|
452 |
)
|
| 410 |
)
|
453 |
),
|
| 411 |
|
454 |
|
| - |
|
455 |
@SqlResultSetMapping(name = "TodayPoByRegion",
|
| - |
|
456 |
classes = @ConstructorResult(
|
| - |
|
457 |
targetClass = BrandRegionPOModel.class,
|
| - |
|
458 |
columns = {
|
| - |
|
459 |
@ColumnResult(name = "warehouse_id", type = Integer.class),
|
| - |
|
460 |
@ColumnResult(name = "brand", type = String.class),
|
| - |
|
461 |
@ColumnResult(name = "today_po_value", type = Long.class),
|
| - |
|
462 |
@ColumnResult(name = "today_billed_value", type = Long.class),
|
| - |
|
463 |
@ColumnResult(name = "total_billed_value", type = Long.class)
|
| - |
|
464 |
}
|
| - |
|
465 |
)
|
| - |
|
466 |
)
|
| 412 |
|
467 |
|
| 413 |
})
|
468 |
})
|
| 414 |
|
469 |
|
| 415 |
|
470 |
|
| 416 |
public class Order implements Serializable {
|
471 |
public class Order implements Serializable {
|