Skip to content

Instantly share code, notes, and snippets.

@mimron

mimron/eod_new

Last active July 17, 2023 03:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mimron/ecfec34a4553dd679ec9a6f17146c520 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mimron/ecfec34a4553dd679ec9a6f17146c520 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
eod_new
SELECT
store_code AS ISCSITCD,
sales_date AS ISCSALDAT,
product_id ISCPLUNUM,
type AS ISCSALTYPE,
case when type = 'R' then -1 * sum(total_rtcnt - total_voided_rtcnt) else SUM((total_cnt - total_voided_cnt) + (total_pcnt - total_voided_pcnt)) end AS ISCSALQTY,
ROUND(SUM((total_amt - total_voided_amt) + (total_pamt - total_voided_pamt) - (total_rfamt - total_voided_rfamt) - (total_rtamt - total_voided_rtamt))) AS ISCSALAMT,
case when vat_code = '1' then ROUND(SUM((total_amt - total_voided_amt - (total_amt_vat - total_voided_amt_vat)) + (total_pamt - total_voided_pamt - (total_pamt_vat - total_voided_pamt_vat)) - (total_rfamt - total_voided_rfamt - (total_rfamt_vat - total_voided_rfamt_vat)) - (total_rtamt - total_voided_rtamt - (total_rtamt_vat - total_voided_rtamt_vat)))) else 0 end AS ISCVATSALAMT,
ROUND(SUM((total_pamt_disc - total_voided_pamt_disc))) AS ISCSALDISC,
ROUND(SUM ( total_pamt_disc - total_voided_pamt_disc - (total_pamt_disc_vat - total_voided_pamt_disc_v))) AS ISCVATSALDISC,
ROUND(SUM(total_pamt_memb_disc - total_voided_pamt_memb_disc)) AS ISCMCCDISC,
case when vat_code = '1' then ROUND(SUM(total_pamt_memb_disc - total_voided_pamt_memb_disc - (total_pamt_memb_disc_vat - total_voided_pamt_memb_disc_v))) else 0 end AS ISCVATMCCDISC,
NOW() AS ISCCREDAT, ISCPAYMENTTYP
FROM
(
SELECT
x.store_code,
sales_date,
pos_txn_id,
product_id,
type,
is_cmc, iscPaymentTyp,
vat_code,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'N' AND discount_amount = 0 THEN x.quantity ELSE 0 END) AS total_cnt,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'Y' AND discount_amount = 0 THEN x.quantity ELSE 0 END) AS total_voided_cnt,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'N' AND discount_amount = 0 THEN x.price_subtotal ELSE 0 END) AS total_amt,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'N' AND discount_amount = 0 AND vat_code = '1' THEN x.price_subtotal / 1.1 ELSE 0 END) AS total_amt_vat,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'Y' AND discount_amount = 0 THEN x.price_subtotal ELSE 0 END) AS total_voided_amt,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'Y' AND discount_amount = 0 AND vat_code = '1' THEN x.price_subtotal / 1.1 ELSE 0 END) AS total_voided_amt_vat,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'N' AND discount_amount != 0 THEN x.quantity ELSE 0 END) AS total_pcnt,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'Y' AND discount_amount != 0 THEN x.quantity ELSE 0 END) AS total_voided_pcnt,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'N' AND discount_amount != 0 THEN x.price_subtotal - discount_amount ELSE 0 END) AS total_pamt,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'N' AND discount_amount != 0 AND vat_code= '1' THEN (x.price_subtotal - discount_amount) / 1.1 ELSE 0 END) AS total_pamt_vat,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'Y' AND discount_amount != 0 THEN x.price_subtotal - discount_amount ELSE 0 END) AS total_voided_pamt,
SUM(CASE WHEN ttype = 'SALE' AND tx_item_is_voided = 'Y' AND discount_amount != 0 AND vat_code= '1' THEN (x.price_subtotal - discount_amount) / 1.1 ELSE 0 END) AS total_voided_pamt_vat,
SUM(CASE WHEN ttype = 'REFUND' AND tx_item_is_voided = 'N' THEN x.price_subtotal ELSE 0 END) AS total_rfamt,
SUM(CASE WHEN ttype = 'REFUND' AND tx_item_is_voided = 'N' AND vat_code= '1' THEN x.price_subtotal / 1.1 ELSE 0 END) AS total_rfamt_vat,
SUM(CASE WHEN ttype = 'REFUND' AND tx_item_is_voided = 'Y' THEN x.price_subtotal ELSE 0 END) AS total_voided_rfamt,
SUM(CASE WHEN ttype = 'REFUND' AND tx_item_is_voided = 'Y' AND vat_code = '1' THEN x.price_subtotal / 1.1 ELSE 0 END) AS total_voided_rfamt_vat,
SUM(CASE WHEN ttype = 'RETURN' AND tx_item_is_voided = 'N' THEN x.price_subtotal - discount_amount ELSE 0 END) AS total_rtamt,
SUM(CASE WHEN ttype = 'RETURN' AND tx_item_is_voided = 'N' AND vat_code = '1' THEN (x.price_subtotal - discount_amount) / 1.1 ELSE 0 END) AS total_rtamt_vat,
SUM(CASE WHEN ttype = 'RETURN' AND tx_item_is_voided = 'N' THEN x.quantity ELSE 0 END) AS total_rtcnt,
SUM(CASE WHEN ttype = 'RETURN' AND tx_item_is_voided = 'Y' THEN x.quantity ELSE 0 END) AS total_voided_rtcnt,
SUM(CASE WHEN ttype = 'RETURN' AND tx_item_is_voided = 'Y' THEN x.price_subtotal - discount_amount ELSE 0 END) AS total_voided_rtamt,
SUM(CASE WHEN ttype = 'RETURN' AND tx_item_is_voided = 'Y' AND vat_code = '1' THEN (x.price_subtotal - discount_amount) / 1.1 ELSE 0 END) AS total_voided_rtamt_vat,
SUM(CASE WHEN ttype in ('SALE', 'RETURN') AND tx_item_is_voided = 'Y' AND member_discount_amount != 0 THEN member_discount_amount ELSE 0 END) AS total_voided_pamt_memb_disc,
SUM(CASE WHEN ttype in ('SALE', 'RETURN') AND tx_item_is_voided = 'Y' AND member_discount_amount != 0 AND vat_code = '1' THEN member_discount_amount / 1.1 ELSE 0 END) AS total_voided_pamt_memb_disc_v,
SUM(CASE WHEN ttype in ('SALE', 'RETURN') AND tx_item_is_voided = 'N' AND member_discount_amount != 0 THEN member_discount_amount ELSE 0 END) AS total_pamt_memb_disc,
SUM(CASE WHEN ttype in ('SALE', 'RETURN') AND tx_item_is_voided = 'N' AND member_discount_amount != 0 AND vat_code = '1' THEN member_discount_amount / 1.1 ELSE 0 END) AS total_pamt_memb_disc_vat,
SUM(CASE WHEN ttype in ('SALE', 'RETURN') AND tx_item_is_voided = 'Y' AND discount_amount != 0 THEN discount_amount - member_discount_amount ELSE 0 END) AS total_voided_pamt_disc,
SUM(CASE WHEN ttype in ('SALE', 'RETURN') AND tx_item_is_voided = 'Y' AND discount_amount != 0 AND vat_code = '1' THEN (discount_amount - member_discount_amount) / 1.1 ELSE 0 END) AS total_voided_pamt_disc_v,
SUM(CASE WHEN ttype in ('SALE', 'RETURN') AND tx_item_is_voided = 'N' AND discount_amount != 0 THEN discount_amount - member_discount_amount ELSE 0 END) AS total_pamt_disc,
SUM(CASE WHEN ttype in ('SALE', 'RETURN') AND tx_item_is_voided = 'N' AND discount_amount != 0 AND vat_code = '1' THEN ( discount_amount - member_discount_amount ) / 1.1 ELSE 0 END) AS total_pamt_disc_vat
FROM
(
SELECT
s.code AS ISCSITCD,
ptx.sales_date AS ISCSALDAT,
pt.pos_txn_id,
ptx.status AS status,
discount_amount + (case
when ptx.member_disc_reversal = 0 then member_discount_amount
else 0
end)+ second_layer_discount_amount + crm_member_discount_amount AS discount_amount,
disc_btn_amount + member_discount_amount as member_discount_amount,
member_discount_amount as member_discount_amount_original,
disc_btn_amount as disc_btn_amount_original,
(case
when member_discount_amount > 0 then 'Y'
when disc_btn_amount > 0 then 'Y'
when member_discount_amount = 0 then 'N'
else 'N'
end) AS is_cmc,
ptx.TYPE AS ttype,
is_voided AS tx_item_is_voided,
coalesce(pt.sku,pr.sku) AS product_id,
pr.is_tax_inclusive,
s.code AS store_code,
quantity,
price_subtotal + non_member_markup as price_subtotal,
ptx.sales_date,
pr.department_code,
pr.vat_code,
ptx.create_date_aud,
(case when ptx.type in ('RETURN') then 'R' when ppi.type is NULL then (case when (discount_amount + second_layer_discount_amount + crm_member_discount_amount) = 0 then 'N' else '1' end) else ppi.type end) as type,
(case
when member_discount_amount > 0 then (select distinct substr(card_num, 1, 6) card_num from electronic_fund_transfer where transaction_id = ptx.id limit 1)
when disc_btn_amount > 0 then '-'
when member_discount_amount = 0 then null
else null
end) AS iscPaymentTyp
FROM pos_tx_item pt
LEFT JOIN product pr ON pr.plu_id = pt.product_id
LEFT JOIN pos_transaction ptx ON ptx.ID = pt.pos_txn_id
LEFT JOIN STORE s ON ptx.store_id = s.store_id
left join pos_promotion_item ppi on pt.pos_txn_id=ppi.pos_txn_id and pt.product_id=ppi.product_id
WHERE
ptx.status = 'COMPLETED' AND
ptx.sales_date = '20211025' AND
pr.sku IS NOT NULL AND
pt.CATEGORY_ID NOT IN ('DEPTSTORE')
) as x
GROUP BY x.store_code, sales_date, pos_txn_id, product_id,type, is_cmc, iscPaymentTyp, vat_code
) as c
GROUP BY
store_code,
sales_date,
product_id,
type,
is_cmc, iscPaymentTyp,
vat_code
ORDER BY store_code, sales_date, product_id,type;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment