| Line 7... |
Line 7... |
| 7 |
@Entity
|
7 |
@Entity
|
| 8 |
@NamedNativeQueries({
|
8 |
@NamedNativeQueries({
|
| 9 |
@NamedNativeQuery(name = "Aging.SoldAgingModel",
|
9 |
@NamedNativeQuery(name = "Aging.SoldAgingModel",
|
| 10 |
query = "SELECT" +
|
10 |
query = "SELECT" +
|
| 11 |
" a.Rbm_Name," +
|
11 |
" a.Rbm_Name," +
|
| 12 |
" SUM(CASE WHEN cc.status = 'EOL' THEN tl.selling_price ELSE 0 END) AS EOL_Billed," +
|
12 |
" SUM(CASE WHEN b.status = 'EOL' THEN b.selling_price ELSE 0 END) AS EOL_Billed," +
|
| 13 |
" SUM(CASE WHEN cc.status = 'SLOWMOVING' THEN tl.selling_price ELSE 0 END) AS Slowmoving_Billed," +
|
13 |
" SUM(CASE WHEN b.status = 'SLOWMOVING' THEN b.selling_price ELSE 0 END) AS Slowmoving_Billed," +
|
| 14 |
" SUM(CASE WHEN cc.status = 'FASTMOVING' THEN tl.selling_price ELSE 0 END) AS Fastmoving_Billed," +
|
14 |
" SUM(CASE WHEN b.status = 'FASTMOVING' THEN b.selling_price ELSE 0 END) AS Fastmoving_Billed," +
|
| 15 |
" SUM(CASE WHEN cc.status = 'HID' THEN tl.selling_price ELSE 0 END) AS HID_Billed," +
|
15 |
" SUM(CASE WHEN b.status = 'HID' THEN b.selling_price ELSE 0 END) AS HID_Billed," +
|
| 16 |
" SUM(CASE WHEN cc.status IS NULL THEN tl.selling_price ELSE 0 END) AS Other_Billed," +
|
16 |
" COALESCE(SUM(CASE WHEN b.status IS NULL THEN b.selling_price ELSE 0 END),0) AS Other_Billed," +
|
| 17 |
" SUM(tl.selling_price) AS Total_Amount" +
|
17 |
" COALESCE(SUM(b.selling_price),0) AS Total_Amount" +
|
| 18 |
" FROM (SELECT au.id AS auth_id," +
|
18 |
" FROM (SELECT au.id AS auth_id," +
|
| 19 |
" CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name," +
|
19 |
" CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name," +
|
| 20 |
" fs.id AS fofo_id" +
|
20 |
" fs.id AS fofo_id" +
|
| 21 |
" FROM auth.auth_user au" +
|
21 |
" FROM auth.auth_user au" +
|
| 22 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
22 |
" JOIN cs.position p ON p.auth_user_id = au.id" +
|
| Line 40... |
Line 40... |
| 40 |
" WHERE pp.partner_id = 0" +
|
40 |
" WHERE pp.partner_id = 0" +
|
| 41 |
" AND p.category_id = 18" +
|
41 |
" AND p.category_id = 18" +
|
| 42 |
" AND fs.active = 1" +
|
42 |
" AND fs.active = 1" +
|
| 43 |
" and fs.internal = false" +
|
43 |
" and fs.internal = false" +
|
| 44 |
" AND p.escalation_type = 'L1') a" +
|
44 |
" AND p.escalation_type = 'L1') a" +
|
| 45 |
" JOIN transaction.order o on (o.customer_id = a.fofo_id)" +
|
45 |
" LEFT JOIN (SELECT o.customer_id, tl.selling_price, cc.status from transaction.order o" +
|
| 46 |
" JOIN transaction.lineitem li ON li.order_id = o.id AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
|
46 |
" JOIN transaction.lineitem li ON li.order_id = o.id AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
|
| 47 |
" JOIN catalog.item ci ON ci.id = li.item_id" +
|
47 |
" JOIN catalog.item ci ON ci.id = li.item_id" +
|
| 48 |
" JOIN catalog.tag_listing tl ON tl.item_id = ci.id" +
|
48 |
" JOIN catalog.tag_listing tl ON tl.item_id = ci.id" +
|
| 49 |
" JOIN catalog.catagoriesd_catalog cc ON cc.catalog_id = ci.catalog_item_id" +
|
49 |
" LEFT JOIN catalog.catagoriesd_catalog cc ON cc.catalog_id = ci.catalog_item_id" +
|
| 50 |
" AND (cc.start_date <= o.created_timestamp AND (cc.end_date IS NULL OR cc.end_date > o.created_timestamp))" +
|
50 |
" AND (cc.start_date <= o.created_timestamp AND (cc.end_date IS NULL OR cc.end_date > o.created_timestamp))" +
|
| 51 |
" JOIN warehouse.scanNew s ON s.orderId = o.id" +
|
51 |
" JOIN warehouse.scanNew s ON s.orderId = o.id" +
|
| 52 |
" JOIN warehouse.inventoryItem ii ON ii.id = s.inventoryItemId" +
|
52 |
" JOIN warehouse.inventoryItem ii ON ii.id = s.inventoryItemId" +
|
| 53 |
" JOIN warehouse.inventoryItem ii2 ON ii.serialNumber = ii2.serialNumber AND ii2.lastScanType != 'PURCHASE_RETURN'" +
|
53 |
" JOIN warehouse.inventoryItem ii2 ON ii.serialNumber = ii2.serialNumber AND ii2.lastScanType != 'PURCHASE_RETURN'" +
|
| 54 |
" JOIN warehouse.purchase p ON p.id = ii2.purchaseId" +
|
54 |
" JOIN warehouse.purchase p ON p.id = ii2.purchaseId" +
|
| 55 |
" JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id" +
|
55 |
" JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id" +
|
| 56 |
" JOIN warehouse.invoice inv ON p.invoice_id = inv.id AND po.supplierId = inv.supplierId AND po.warehouseId = ii2.physicalWarehouseId" +
|
56 |
" JOIN warehouse.invoice inv ON p.invoice_id = inv.id AND po.supplierId = inv.supplierId AND po.warehouseId = ii2.physicalWarehouseId" +
|
| 57 |
" JOIN warehouse.supplier su ON su.id = inv.supplierId AND su.internal = 0" +
|
57 |
" JOIN warehouse.supplier su ON su.id = inv.supplierId AND su.internal = 0" +
|
| 58 |
"" +
|
58 |
"" +
|
| 59 |
" where ((o.created_timestamp >= :startDate AND o.created_timestamp < :endDate) AND (o.billing_timestamp >= :startDate AND o.billing_timestamp < :endDate) AND o.refund_timestamp IS NULL)" +
|
59 |
" where ((o.created_timestamp >= :startDate AND o.created_timestamp < :endDate) AND (o.billing_timestamp >= :startDate AND o.billing_timestamp < :endDate) AND o.refund_timestamp IS NULL)" +
|
| 60 |
" and DATEDIFF(NOW(), inv.invoiceDate) > 15" +
|
60 |
" and DATEDIFF(NOW(), inv.invoiceDate) > 15) b on b.customer_id = a.fofo_id" +
|
| 61 |
" GROUP BY a.auth_id, a.Rbm_Name",
|
61 |
" GROUP BY a.auth_id, a.Rbm_Name",
|
| 62 |
resultSetMapping = "SoldAgingModel"),
|
62 |
resultSetMapping = "SoldAgingModel"),
|
| 63 |
|
63 |
|
| 64 |
})
|
64 |
})
|
| 65 |
|
65 |
|