Subversion Repositories SmartDukaan

SELECT ai.serial_number,
       ai.activation_timestamp,
       cs.id                              as scheme_id,
       ii.id                              as inventory_item_id,
       ii.good_quantity + ii.bad_quantity as Qty,
       cs.amount,
       sio.status                         as SCHEME_PAYOUT_STATUS,
       sio.create_timestamp               as Entry_Date,
       sio.credit_timestamp                  CREDITED_ON,
       ai.checked                            Checked
FROM fofo.inventory_item ii
         join catalog.item i on i.id = ii.item_id
         left join fofo.activated_imei ai on ai.serial_number = ii.serial_number
         left join catalog.scheme cs
                   on (date(ai.activation_timestamp) between cs.start_date_time and cs.end_date_time and
                       cs.active_timestamp is not null)
         join fofo.scheme_item si on (si.scheme_id = cs.id and i.catalog_item_id = si.catalog_id)
         left join fofo.scheme_in_out sio on (sio.scheme_id = cs.id and sio.inventory_item_id = ii.id)
    and cs.type in ('SPECIAL_SUPPORT', 'ACTIVATION')
    and ai.serial_number in (861568067746911)

use warehouse


SELECT ci.id                         Item_Id,
       ci.brand,
       ci.model_name,
       ci.model_number,
       ci.color,
       sum(s.quantity)               Total_Qty,
       sum(l.unitPrice * s.quantity) Total_Value,
       s.type,
       v.id                          Vendor_ID,
       v.name                        Vendor_Name,
       sm.name                       Warehouse_State,
       iw.displayName                Warehouse_Name,
       s.scannedAt,
       i.created                     Grn_On,
       ai.activation_timestamp,
       ai.create_timestamp           Activation_added_on
FROM scanNew s
         left join scanNew s2
                   on (s.scannedAt < s2.scannedAt[ and date(s2.scannedAt) <= {criteria2}]and s.inventoryItemId=s2.inventoryItemId)
         join inventoryItem i on i.id = s.inventoryItemId
         join purchase p on p.id = i.purchaseId
         join lineitem l on (p.purchaseOrder_id = l.purchaseOrder_id and l.itemId = i.itemId)
         join inventory.warehouse iw on iw.id = i.currentWarehouseId
         join inventory.vendor v on v.id = iw.vendor_id
         join catalog.item ci on i.itemId = ci.id
         join inventory.warehouse bw on bw.id = iw.billingWarehouseId
         join inventory.statemaster sm on bw.state_id = sm.id
         left join fofo.activated_imei ai on ai.serial_number = i.serialNumber
where s.scannedAt
          > '2017-07-01' [
  and date (s.scannedAt) <= {criteria2}] [
  and ci.id in ({criteria1})] [
  and ci.brand={brand}] [
  and v.id = {vendorId}]
  and s.type in ('PURCHASE'
    , 'SALE_RET'
    , 'MARKED_GOOD'
    , 'WAREHOUSE_TRANSFER_IN')
  and s2.inventoryItemId is null
group by ci.id, iw.displayName



select inventoryi2_.fofo_id                       as col_0_0_,
       inventoryi2_.sgst_rate                     as col_1_0_,
       inventoryi2_.cgst_rate                     as col_2_0_,
       inventoryi2_.igst_rate                     as col_3_0_,
       cast(case
                when (schemeinou0_.credit_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59') and
                     (schemeinou0_.rolled_back_timestamp is null) or
                     schemeinou0_.rolled_back_timestamp not between '2022-09-01 00:00:00' and '2022-09-30 23:59:59'
                    then schemeinou0_.amount
                when (schemeinou0_.credit_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59') and
                     (schemeinou0_.rolled_back_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59') then 0
                when (schemeinou0_.credit_timestamp is not null) and
                     (schemeinou0_.rolled_back_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59')
                    then -schemeinou0_.amount
                else 0 end as decimal(19, 2))     as col_4_0_,
       inventoryi2_.serial_number                 as col_5_0_,
       scheme1_.name                              as col_6_0_,
       purchase3_.purchase_reference              as col_7_0_,
       case
           when schemeinou0_.rolled_back_timestamp is not null then schemeinou0_.rolled_back_timestamp
           else schemeinou0_.credit_timestamp end as col_8_0_
from fofo.scheme_in_out schemeinou0_
         inner join catalog.scheme scheme1_ on (scheme1_.id = schemeinou0_.scheme_id)
         inner join fofo.inventory_item inventoryi2_ on (schemeinou0_.inventory_item_id = inventoryi2_.id
    )
         inner join fofo.purchase purchase3_ on (purchase3_.id = inventoryi2_.purchase_id)
where schemeinou0_.create_timestamp >= '2022-09-16 00:00:00'
  and (schemeinou0_.credit_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59' or
       (schemeinou0_.credit_timestamp is not null) and
       (schemeinou0_.rolled_back_timestamp between '2022-09-01 00:00:00' and '2022-09-30 23:59:59'
           ))
  and serial_number = '353881480215900'


SELECT fs.id               Partner_Id,
       fs.code,
       ua.name,
       o.invoice_number,
       o.billing_timestamp,
       l.item_id,
       l.brand,
       l.model_name,
       l.model_number,
       l.color,
       l.unit_price,
       lii.serial_number,
       v.name              Vendor_Name,
       wi.created          Grn_On,
       ai.activation_timestamp,
       ai.create_timestamp activation_added_on
FROM fofo_store fs
         join user.user u on u.id = fs.id
         join user.address ua on u.default_address_id = ua.id
         join transaction.order o on o.customer_id = fs.id
         join transaction.lineitem l on l.order_id = o.id
         join transaction.line_item_imei lii on lii.line_item_id = l.id
         join inventory.warehouse w on w.id = o.fulfilmentWarehouseId
         join inventory.vendor v on v.id = w.vendor_id
         left join activated_imei ai on ai.serial_number = lii.serial_number
         join warehouse.inventoryItem wi on wi.serialNumber = lii.serial_number
         join warehouse.scanNew ws on (ws.orderId = o.id and ws.type = 'SALE' and wi.id = ws.inventoryItemId)
where o.created_timestamp > '2017-07-01'
  and date(o.billing_timestamp) < date_add('2022-10-16', interval 1 day)
  and (date(o.refund_timestamp) is null or o.refund_timestamp > date_add('2022-10-16', interval 1 day))
  and (o.partner_grn_timestamp is null or o.partner_grn_timestamp > date_add('2022-10-16', interval 1 day))
  and fs.code in ('HRSS255')
  and o.customer_email not in ('neeraj.gupta@smartdukaan.com', 'testpxps@gmail.com', 'sunny.yadav@smartdukaan.com')
  and fs.active = 1
  and fs.internal = 0;



select au.id Auth_Id,
       au.first_name,
       au.last_name,
       c.id  Category_Id,
       c.name,
       p.escalation_type,
       men.display_text,
       men.id
from auth.auth_user au
         left join cs.position p on p.auth_user_id = au.id
         left join cs.ticket_category c on c.id = p.category_id
         left join auth.menu_category mc on (mc.category_id = c.id and p.escalation_type = mc.escalation_type)
         left join auth.menu men on men.id = mc.menu_id
order by au.id, c.id;