| 31860 |
tejbeer |
1 |
SELECT ai.serial_number,
|
|
|
2 |
ai.activation_timestamp,
|
|
|
3 |
cs.id as scheme_id,
|
|
|
4 |
ii.id as inventory_item_id,
|
|
|
5 |
ii.good_quantity + ii.bad_quantity as Qty,
|
|
|
6 |
cs.amount,
|
|
|
7 |
sio.status as SCHEME_PAYOUT_STATUS,
|
|
|
8 |
sio.create_timestamp as Entry_Date,
|
|
|
9 |
sio.credit_timestamp CREDITED_ON,
|
|
|
10 |
ai.checked Checked
|
|
|
11 |
FROM fofo.inventory_item ii
|
|
|
12 |
join catalog.item i on i.id = ii.item_id
|
|
|
13 |
left join fofo.activated_imei ai on ai.serial_number = ii.serial_number
|
|
|
14 |
left join catalog.scheme cs
|
|
|
15 |
on (date(ai.activation_timestamp) between cs.start_date_time and cs.end_date_time and
|
|
|
16 |
cs.active_timestamp is not null)
|
|
|
17 |
join fofo.scheme_item si on (si.scheme_id = cs.id and i.catalog_item_id = si.catalog_id)
|
|
|
18 |
left join fofo.scheme_in_out sio on (sio.scheme_id = cs.id and sio.inventory_item_id = ii.id)
|
|
|
19 |
and cs.type in ('SPECIAL_SUPPORT', 'ACTIVATION')
|
|
|
20 |
and ai.serial_number in (861568067746911)
|
|
|
21 |
|
|
|
22 |
use warehouse
|
|
|
23 |
|
|
|
24 |
|
|
|
25 |
SELECT ci.id Item_Id,
|
|
|
26 |
ci.brand,
|
|
|
27 |
ci.model_name,
|
|
|
28 |
ci.model_number,
|
|
|
29 |
ci.color,
|
|
|
30 |
sum(s.quantity) Total_Qty,
|
|
|
31 |
sum(l.unitPrice * s.quantity) Total_Value,
|
|
|
32 |
s.type,
|
|
|
33 |
v.id Vendor_ID,
|
|
|
34 |
v.name Vendor_Name,
|
|
|
35 |
sm.name Warehouse_State,
|
|
|
36 |
iw.displayName Warehouse_Name,
|
|
|
37 |
s.scannedAt,
|
|
|
38 |
i.created Grn_On,
|
|
|
39 |
ai.activation_timestamp,
|
|
|
40 |
ai.create_timestamp Activation_added_on
|
|
|
41 |
FROM scanNew s
|
|
|
42 |
left join scanNew s2
|
|
|
43 |
on (s.scannedAt < s2.scannedAt[ and date(s2.scannedAt) <= {criteria2}]and s.inventoryItemId=s2.inventoryItemId)
|
|
|
44 |
join inventoryItem i on i.id = s.inventoryItemId
|
|
|
45 |
join purchase p on p.id = i.purchaseId
|
|
|
46 |
join lineitem l on (p.purchaseOrder_id = l.purchaseOrder_id and l.itemId = i.itemId)
|
|
|
47 |
join inventory.warehouse iw on iw.id = i.currentWarehouseId
|
|
|
48 |
join inventory.vendor v on v.id = iw.vendor_id
|
|
|
49 |
join catalog.item ci on i.itemId = ci.id
|
|
|
50 |
join inventory.warehouse bw on bw.id = iw.billingWarehouseId
|
|
|
51 |
join inventory.statemaster sm on bw.state_id = sm.id
|
|
|
52 |
left join fofo.activated_imei ai on ai.serial_number = i.serialNumber
|
|
|
53 |
where s.scannedAt
|
|
|
54 |
> '2017-07-01' [
|
|
|
55 |
and date (s.scannedAt) <= {criteria2}] [
|
|
|
56 |
and ci.id in ({criteria1})] [
|
|
|
57 |
and ci.brand={brand}] [
|
|
|
58 |
and v.id = {vendorId}]
|
|
|
59 |
and s.type in ('PURCHASE'
|
|
|
60 |
, 'SALE_RET'
|
|
|
61 |
, 'MARKED_GOOD'
|
|
|
62 |
, 'WAREHOUSE_TRANSFER_IN')
|
|
|
63 |
and s2.inventoryItemId is null
|
|
|
64 |
group by ci.id, iw.displayName
|
|
|
65 |
|
|
|
66 |
|
|
|
67 |
|
|
|
68 |
select inventoryi2_.fofo_id as col_0_0_,
|
|
|
69 |
inventoryi2_.sgst_rate as col_1_0_,
|
|
|
70 |
inventoryi2_.cgst_rate as col_2_0_,
|
|
|
71 |
inventoryi2_.igst_rate as col_3_0_,
|
|
|
72 |
cast(case
|
|
|
73 |
when (schemeinou0_.credit_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59') and
|
|
|
74 |
(schemeinou0_.rolled_back_timestamp is null) or
|
|
|
75 |
schemeinou0_.rolled_back_timestamp not between '2022-09-01 00:00:00' and '2022-09-30 23:59:59'
|
|
|
76 |
then schemeinou0_.amount
|
|
|
77 |
when (schemeinou0_.credit_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59') and
|
|
|
78 |
(schemeinou0_.rolled_back_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59') then 0
|
|
|
79 |
when (schemeinou0_.credit_timestamp is not null) and
|
|
|
80 |
(schemeinou0_.rolled_back_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59')
|
|
|
81 |
then -schemeinou0_.amount
|
|
|
82 |
else 0 end as decimal(19, 2)) as col_4_0_,
|
|
|
83 |
inventoryi2_.serial_number as col_5_0_,
|
|
|
84 |
scheme1_.name as col_6_0_,
|
|
|
85 |
purchase3_.purchase_reference as col_7_0_,
|
|
|
86 |
case
|
|
|
87 |
when schemeinou0_.rolled_back_timestamp is not null then schemeinou0_.rolled_back_timestamp
|
|
|
88 |
else schemeinou0_.credit_timestamp end as col_8_0_
|
|
|
89 |
from fofo.scheme_in_out schemeinou0_
|
|
|
90 |
inner join catalog.scheme scheme1_ on (scheme1_.id = schemeinou0_.scheme_id)
|
|
|
91 |
inner join fofo.inventory_item inventoryi2_ on (schemeinou0_.inventory_item_id = inventoryi2_.id
|
|
|
92 |
)
|
|
|
93 |
inner join fofo.purchase purchase3_ on (purchase3_.id = inventoryi2_.purchase_id)
|
|
|
94 |
where schemeinou0_.create_timestamp >= '2022-09-16 00:00:00'
|
|
|
95 |
and (schemeinou0_.credit_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59' or
|
|
|
96 |
(schemeinou0_.credit_timestamp is not null) and
|
|
|
97 |
(schemeinou0_.rolled_back_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59'
|
|
|
98 |
))
|
|
|
99 |
and serial_number = '353881480215900'
|
|
|
100 |
|
|
|
101 |
|
|
|
102 |
SELECT fs.id Partner_Id,
|
|
|
103 |
fs.code,
|
|
|
104 |
ua.name,
|
|
|
105 |
o.invoice_number,
|
|
|
106 |
o.billing_timestamp,
|
|
|
107 |
l.item_id,
|
|
|
108 |
l.brand,
|
|
|
109 |
l.model_name,
|
|
|
110 |
l.model_number,
|
|
|
111 |
l.color,
|
|
|
112 |
l.unit_price,
|
|
|
113 |
lii.serial_number,
|
|
|
114 |
v.name Vendor_Name,
|
|
|
115 |
wi.created Grn_On,
|
|
|
116 |
ai.activation_timestamp,
|
|
|
117 |
ai.create_timestamp activation_added_on
|
|
|
118 |
FROM fofo_store fs
|
|
|
119 |
join user.user u on u.id = fs.id
|
|
|
120 |
join user.address ua on u.default_address_id = ua.id
|
|
|
121 |
join transaction.order o on o.customer_id = fs.id
|
|
|
122 |
join transaction.lineitem l on l.order_id = o.id
|
|
|
123 |
join transaction.line_item_imei lii on lii.line_item_id = l.id
|
|
|
124 |
join inventory.warehouse w on w.id = o.fulfilmentWarehouseId
|
|
|
125 |
join inventory.vendor v on v.id = w.vendor_id
|
|
|
126 |
left join activated_imei ai on ai.serial_number = lii.serial_number
|
|
|
127 |
join warehouse.inventoryItem wi on wi.serialNumber = lii.serial_number
|
|
|
128 |
join warehouse.scanNew ws on (ws.orderId = o.id and ws.type = 'SALE' and wi.id = ws.inventoryItemId)
|
|
|
129 |
where o.created_timestamp > '2017-07-01'
|
|
|
130 |
and date(o.billing_timestamp) < date_add('2022-10-16', interval 1 day)
|
|
|
131 |
and (date(o.refund_timestamp) is null or o.refund_timestamp > date_add('2022-10-16', interval 1 day))
|
|
|
132 |
and (o.partner_grn_timestamp is null or o.partner_grn_timestamp > date_add('2022-10-16', interval 1 day))
|
|
|
133 |
and fs.code in ('HRSS255')
|
|
|
134 |
and o.customer_email not in ('neeraj.gupta@smartdukaan.com', 'testpxps@gmail.com', 'sunny.yadav@smartdukaan.com')
|
|
|
135 |
and fs.active = 1
|
|
|
136 |
and fs.internal = 0;
|
|
|
137 |
|
|
|
138 |
|
|
|
139 |
|
|
|
140 |
select au.id Auth_Id,
|
|
|
141 |
au.first_name,
|
|
|
142 |
au.last_name,
|
|
|
143 |
c.id Category_Id,
|
|
|
144 |
c.name,
|
|
|
145 |
p.escalation_type,
|
|
|
146 |
men.display_text,
|
|
|
147 |
men.id
|
|
|
148 |
from auth.auth_user au
|
|
|
149 |
left join cs.position p on p.auth_user_id = au.id
|
|
|
150 |
left join cs.ticket_category c on c.id = p.category_id
|
|
|
151 |
left join auth.menu_category mc on (mc.category_id = c.id and p.escalation_type = mc.escalation_type)
|
|
|
152 |
left join auth.menu men on men.id = mc.menu_id
|
|
|
153 |
order by au.id, c.id;
|
|
|
154 |
|