| 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,u.name as partnerName,u.mobile_number as partnerMobile, fs.code, uc.originalOrderId as orderId, uc.mobile as customerNumber, " +
|
24 |
query = "SELECT uc.agentId, ucd.agent_id as agentEmail,u.name as partnerName,ua.phone as partnerMobile, ua.state, uc.originalOrderId as orderId, uc.mobile as customerNumber, " +
|
| 25 |
"ucd.created_timestamp as callingDate, coalesce(ucd.disposition,'-') as disposition,coalesce(ucd.remark,'-') as remark," +
|
25 |
"ucd.created_timestamp as callingDate, coalesce(ucd.disposition,'-') as disposition,coalesce(ucd.remark,'-') as remark," +
|
| 26 |
" coalesce(rp.payment_status, 'Not Found') as paymentStatus, " +
|
26 |
" coalesce(rp.payment_status, 'Not Found') as paymentStatus, " +
|
| 27 |
"(CASE WHEN ac.create_timestamp IS NOT NULL THEN 'Created' ELSE 'Not Created' END) AS insuranceStatus, " +
|
27 |
"(CASE WHEN ac.create_timestamp IS NOT NULL THEN 'Created' ELSE 'Not Created' END) AS insuranceStatus, " +
|
| 28 |
"coalesce(ip.sale_amount,0) as soldAmount, coalesce(ip.invoice_number,'Not Found') as invoiceNumber, coalesce(ip.serial_number,'Not Found') as serialNumber" +
|
28 |
"coalesce(ip.sale_amount,0) as soldAmount, coalesce(ip.invoice_number,'Not Found') as invoiceNumber, coalesce(ip.serial_number,'Not Found') as serialNumber" +
|
| 29 |
" FROM fofo.upSaleCall uc" +
|
29 |
" FROM fofo.upSaleCall uc" +
|
| 30 |
" JOIN fofo.upsell_call_details ucd on uc.id = ucd.upsale_call_id" +
|
30 |
" JOIN fofo.upsell_call_details ucd on uc.id = ucd.upsale_call_id" +
|
| 31 |
" JOIN fofo.fofo_order fo on fo.id = uc.originalOrderId" +
|
31 |
" JOIN fofo.fofo_order fo on fo.id = uc.originalOrderId" +
|
| 32 |
" JOIN user.user u on u.id = fo.fofo_id" +
|
32 |
" JOIN user.user u on u.id = fo.fofo_id" +
|
| 33 |
" JOIN fofo.fofo_store fs on u.id = fs.id" +
|
33 |
" JOIN user.address ua on ua.id = u.default_address_id" +
|
| 34 |
" LEFT JOIN fofo.upsellRazorpayPaymentStatus rp on rp.order_id = uc.originalOrderId" +
|
34 |
" LEFT JOIN fofo.upsellRazorpayPaymentStatus rp on rp.order_id = uc.originalOrderId" +
|
| 35 |
" LEFT JOIN fofo.upsaleAgentCollection ac on ac.order_id = uc.originalOrderId" +
|
35 |
" LEFT JOIN fofo.upsaleAgentCollection ac on ac.order_id = uc.originalOrderId" +
|
| 36 |
" LEFT JOIN dtr.insurance_policy ip on ip.device_invoice_number = fo.invoice_number" +
|
36 |
" LEFT JOIN dtr.insurance_policy ip on ip.device_invoice_number = fo.invoice_number" +
|
| 37 |
" WHERE uc.create_timestamp >= :startDate and uc.create_timestamp < :endDate",
|
37 |
" WHERE uc.create_timestamp >= :startDate and uc.create_timestamp < :endDate",
|
| 38 |
resultSetMapping = "UpsellingReport"),
|
38 |
resultSetMapping = "UpsellingReport"),
|
| Line 46... |
Line 46... |
| 46 |
columns = {
|
46 |
columns = {
|
| 47 |
@ColumnResult(name = "agentId", type = Integer.class),
|
47 |
@ColumnResult(name = "agentId", type = Integer.class),
|
| 48 |
@ColumnResult(name = "agentEmail", type = String.class),
|
48 |
@ColumnResult(name = "agentEmail", type = String.class),
|
| 49 |
@ColumnResult(name = "partnerName", type = String.class),
|
49 |
@ColumnResult(name = "partnerName", type = String.class),
|
| 50 |
@ColumnResult(name = "partnerMobile", type = String.class),
|
50 |
@ColumnResult(name = "partnerMobile", type = String.class),
|
| 51 |
@ColumnResult(name = "code", type = String.class),
|
51 |
@ColumnResult(name = "state", type = String.class),
|
| 52 |
@ColumnResult(name = "orderId", type = Integer.class),
|
52 |
@ColumnResult(name = "orderId", type = Integer.class),
|
| 53 |
@ColumnResult(name = "customerNumber ", type = String.class),
|
53 |
@ColumnResult(name = "customerNumber ", type = String.class),
|
| 54 |
@ColumnResult(name = "callingDate ", type = String.class),
|
54 |
@ColumnResult(name = "callingDate ", type = String.class),
|
| 55 |
@ColumnResult(name = "disposition ", type = String.class),
|
55 |
@ColumnResult(name = "disposition ", type = String.class),
|
| 56 |
@ColumnResult(name = "remark ", type = String.class),
|
56 |
@ColumnResult(name = "remark ", type = String.class),
|