| Line 62... |
Line 62... |
| 62 |
|
62 |
|
| 63 |
@NamedNativeQuery(name = "scheme.selectMissedActivationSale", query = "select cs.id as scheme_id, ai.serial_number, sr.order_id, ii.id as inventory_item_id, ai.activation_timestamp"
|
63 |
@NamedNativeQuery(name = "scheme.selectMissedActivationSale", query = "select cs.id as scheme_id, ai.serial_number, sr.order_id, ii.id as inventory_item_id, ai.activation_timestamp"
|
| 64 |
+ " from fofo.activated_imei ai "
|
64 |
+ " from fofo.activated_imei ai "
|
| 65 |
+ " join fofo.inventory_item ii on ai.serial_number = ii.serial_number "
|
65 |
+ " join fofo.inventory_item ii on ai.serial_number = ii.serial_number "
|
| 66 |
+ " join catalog.item i on i.id = ii.item_id "
|
66 |
+ " join catalog.item i on i.id = ii.item_id "
|
| 67 |
+ " join catalog.scheme cs on (date(ai.activation_timestamp) between cs.start_date_time and cs.end_date_time) "
|
67 |
+ " join catalog.scheme cs on ai.activation_timestamp >= cs.start_date_time and ai.activation_timestamp <= cs.end_date_time "
|
| 68 |
+ " join fofo.scheme_item si on (si.scheme_id = cs.id and i.catalog_item_id = si.catalog_id) "
|
68 |
+ " join fofo.scheme_item si on (si.scheme_id = cs.id and i.catalog_item_id = si.catalog_id) "
|
| 69 |
+ " join fofo.scan_record sr on (sr.inventory_item_id = ii.id)"
|
69 |
+ " join fofo.scan_record sr on (sr.inventory_item_id = ii.id)"
|
| 70 |
+ " join fofo.scheme_region sre on sre.scheme_id = cs.id"
|
70 |
+ " join fofo.scheme_region sre on sre.scheme_id = cs.id"
|
| 71 |
+ " join cs.partner_region pr on (pr.region_id = sre.region_id and (pr.fofo_id = ii.fofo_id or pr.fofo_id = 0))"
|
71 |
+ " join cs.partner_region pr on (pr.region_id = sre.region_id and (pr.fofo_id = ii.fofo_id or pr.fofo_id = 0))"
|
| 72 |
+ " left join fofo.scheme_in_out sio on (sio.scheme_id = cs.id and sio.inventory_item_id = ii.id) "
|
72 |
+ " left join fofo.scheme_in_out sio on (sio.scheme_id = cs.id and sio.inventory_item_id = ii.id) "
|
| 73 |
+ " where cs.active_timestamp is not null " + " and cs.type in ('SPECIAL_SUPPORT', 'ACTIVATION')"
|
73 |
+ " where cs.active_timestamp is not null " + " and cs.type ='SPECIAL_SUPPORT'"
|
| 74 |
+ " and sr.type = 'SALE'" + " and (sio.id is null or sio.status='REJECTED') "
|
74 |
+ " and sr.type = 'SALE'" + " and (sio.id is null or sio.status='REJECTED') "
|
| 75 |
+ " and ai.checked = false", resultSetMapping = "missedActivationSaleMapping") })
|
75 |
+ " and ai.checked = false", resultSetMapping = "missedActivationSaleMapping") })
|
| 76 |
|
76 |
|
| 77 |
@SqlResultSetMappings({ @SqlResultSetMapping(name = "missedActivationSaleMapping", classes = {
|
77 |
@SqlResultSetMappings({ @SqlResultSetMapping(name = "missedActivationSaleMapping", classes = {
|
| 78 |
@ConstructorResult(targetClass = SchemesImeisModel.class, columns = {
|
78 |
@ConstructorResult(targetClass = SchemesImeisModel.class, columns = {
|