| Line 19... |
Line 19... |
| 19 |
})
|
19 |
})
|
| 20 |
|
20 |
|
| 21 |
|
21 |
|
| 22 |
@NamedNativeQueries({
|
22 |
@NamedNativeQueries({
|
| 23 |
@NamedNativeQuery(name = "Upsell.UpsellingReport",
|
23 |
@NamedNativeQuery(name = "Upsell.UpsellingReport",
|
| 24 |
query = "SELECT uc.agentId, ucd.agent_id as agentEmail, uc.originalOrderId as orderId, uc.mobile as customerNumber, ucd.created_timestamp as callingDate," +
|
24 |
query = "SELECT uc.agentId, ucd.agent_id as agentEmail,u.name as partnerName, uc.originalOrderId as orderId, uc.mobile as customerNumber, ucd.created_timestamp as callingDate," +
|
| 25 |
" coalesce(rp.payment_status, 'Not Found') as paymentStatus, (CASE WHEN ac.create_timestamp IS NOT NULL THEN 'Created' ELSE 'Not Created' END) AS insuranceStatus, coalesce(ip.sale_amount,0) as soldAmount, coalesce(ip.invoice_number,'Not Found') as invoiceNumber, coalesce(ip.serial_number,'Not Found') as serialNumber" +
|
25 |
" coalesce(rp.payment_status, 'Not Found') as paymentStatus, (CASE WHEN ac.create_timestamp IS NOT NULL THEN 'Created' ELSE 'Not Created' END) AS insuranceStatus, coalesce(ip.sale_amount,0) as soldAmount, coalesce(ip.invoice_number,'Not Found') as invoiceNumber, coalesce(ip.serial_number,'Not Found') as serialNumber" +
|
| 26 |
" FROM fofo.upSaleCall uc" +
|
26 |
" FROM fofo.upSaleCall uc" +
|
| 27 |
" JOIN fofo.upsell_call_details ucd on uc.id = ucd.upsale_call_id" +
|
27 |
" JOIN fofo.upsell_call_details ucd on uc.id = ucd.upsale_call_id" +
|
| - |
|
28 |
" JOIN fofo.fofo_order fo on fo.id = uc.originalOrderId" +
|
| - |
|
29 |
" JOIN user.user u on u.id = fo.fofo_id" +
|
| 28 |
" LEFT JOIN fofo.upsellRazorpayPaymentStatus rp on rp.order_id = uc.originalOrderId" +
|
30 |
" LEFT JOIN fofo.upsellRazorpayPaymentStatus rp on rp.order_id = uc.originalOrderId" +
|
| 29 |
" LEFT JOIN fofo.upsaleAgentCollection ac on ac.order_id = uc.originalOrderId" +
|
31 |
" LEFT JOIN fofo.upsaleAgentCollection ac on ac.order_id = uc.originalOrderId" +
|
| 30 |
" LEFT JOIN dtr.insurance_policy ip on ip.id = ac.product_id" +
|
32 |
" LEFT JOIN dtr.insurance_policy ip on ip.device_invoice_number = fo.invoice_number" +
|
| 31 |
" WHERE uc.create_timestamp >= :startDate and uc.create_timestamp < :endDate",
|
33 |
" WHERE uc.create_timestamp >= :startDate and uc.create_timestamp < :endDate",
|
| 32 |
resultSetMapping = "UpsellingReport"),
|
34 |
resultSetMapping = "UpsellingReport"),
|
| 33 |
|
35 |
|
| 34 |
})
|
36 |
})
|
| 35 |
|
37 |
|
| Line 38... |
Line 40... |
| 38 |
@SqlResultSetMapping(name = "UpsellingReport",
|
40 |
@SqlResultSetMapping(name = "UpsellingReport",
|
| 39 |
classes = {@ConstructorResult(targetClass = UpsellingReportModel.class,
|
41 |
classes = {@ConstructorResult(targetClass = UpsellingReportModel.class,
|
| 40 |
columns = {
|
42 |
columns = {
|
| 41 |
@ColumnResult(name = "agentId", type = Integer.class),
|
43 |
@ColumnResult(name = "agentId", type = Integer.class),
|
| 42 |
@ColumnResult(name = "agentEmail", type = String.class),
|
44 |
@ColumnResult(name = "agentEmail", type = String.class),
|
| - |
|
45 |
@ColumnResult(name = "partnerName", type = String.class),
|
| 43 |
@ColumnResult(name = "orderId", type = Integer.class),
|
46 |
@ColumnResult(name = "orderId", type = Integer.class),
|
| 44 |
@ColumnResult(name = "customerNumber ", type = String.class),
|
47 |
@ColumnResult(name = "customerNumber ", type = String.class),
|
| 45 |
@ColumnResult(name = "callingDate ", type = String.class),
|
48 |
@ColumnResult(name = "callingDate ", type = String.class),
|
| 46 |
@ColumnResult(name = "paymentStatus ", type = String.class),
|
49 |
@ColumnResult(name = "paymentStatus ", type = String.class),
|
| 47 |
@ColumnResult(name = "insuranceStatus ", type = String.class),
|
50 |
@ColumnResult(name = "insuranceStatus ", type = String.class),
|