query_get_customer_omzet

-- 1. prepare temp table
--   1. temp table utama
DROP TABLE IF EXISTS tr_data_omzet_toko_detail;
CREATE TABLE tr_data_omzet_toko_detail
(
  ou_id bigint,
  ou_code character varying(100),
  partner_id bigint,
  partner_code character varying(100),
  product_id bigint,
  product_code character varying(100),
  product_group_brand character varying(100),
  year_month character varying(6),
  qty numeric,
  gross_amount numeric,
  nett_amount numeric,
  tax_amount numeric
)
WITH (
  OIDS=FALSE
);

--   2. temp table sum per parent group brand
DROP TABLE IF EXISTS tr_data_omzet_toko_group_brand;
CREATE TABLE tr_data_omzet_toko_group_brand
(
  ou_id bigint,
  partner_id bigint,
  year_month character varying(6),
  group_brand_data character varying(5),
  gross_amount numeric,
  nett_amount numeric,
  tax_amount numeric
)
WITH (
  OIDS=FALSE
);


DELETE FROM tr_data_omzet_toko_detail;
DELETE FROM tr_data_omzet_toko_group_brand;

-- 2. get data trx (berdasarkan stock date)
--   1. POS (non nempil)
INSERT INTO tr_data_omzet_toko_detail(
    ou_id, ou_code, partner_id, partner_code,
    product_id, product_code, product_group_brand, year_month,
    qty, gross_amount, nett_amount, tax_amount
) SELECT B.ou_id, B.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
    G.product_id, G.product_code AS product_code, Q.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
    F.qty, F.qty * F.gross_sell_price AS omzet_bruto_amount, F.nett_amount_item AS nett_item_amount, F.tax_amount
FROM i_trx_pos A
    INNER JOIN t_ou B on A.ou_id = B.ou_id
    INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
    INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
    INNER JOIN m_product G on F.product_id = G.product_id
    LEFT JOIN m_product_custom_for_sasa P on G.product_id = P.product_id
    LEFT JOIN m_group_brand Q ON P.group_brand_id = Q.group_brand_id
    WHERE A.tenant_id = 10
        --AND A.ou_id = 15
        AND A.doc_date BETWEEN '20210501' AND '20210531';

--   2. POS Void (non nempil)
INSERT INTO tr_data_omzet_toko_detail(
    ou_id, ou_code, partner_id, partner_code,
    product_id, product_code, product_group_brand, year_month,
    qty, gross_amount, nett_amount, tax_amount
) SELECT B.ou_id, B.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
    G.product_id, G.product_code AS product_code, Q.group_brand_code, SUBSTRING(E.doc_date, 1, 6) AS year_month,
    (F.qty)*-1, (F.qty*-1)* F.gross_sell_price AS omzet_bruto_amount, F.nett_amount_item*(-1)AS nett_item_amount, F.tax_amount*(-1)
FROM i_trx_pos A
    INNER JOIN t_ou B on A.ou_id = B.ou_id
    INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
    INNER JOIN i_trx_log_voided_pos_custom E ON A.doc_no = E.doc_no AND A.ou_id = E.ou_id AND A.tenant_id = E.tenant_id
    INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
    INNER JOIN m_product G on F.product_id = G.product_id
    INNER JOIN m_product_custom_for_sasa P on G.product_id = P.product_id
    INNER JOIN m_group_brand Q ON P.group_brand_id = Q.group_brand_id
    WHERE A.status = 'V'
        AND A.tenant_id = 10
        AND E.doc_date BETWEEN '20210501' AND '20210531';

--   3. Penjualan (dari DO)
INSERT INTO tr_data_omzet_toko_detail(
    ou_id, ou_code, partner_id, partner_code,
    product_id, product_code, product_group_brand, year_month,
    qty, gross_amount, nett_amount, tax_amount
) SELECT B.ou_id, B.ou_code, Q.partner_id, COALESCE(f_get_partner_code(Q.partner_id), 'UMUM') AS partner_code,
    E.product_id, E.product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
    SUM(J.qty_dlv_so) AS qty,
    SUM((J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) + I.tax_amount) AS omzet_bruto_amount,
    SUM(J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) AS nett_item_amount,
    SUM(I.tax_amount) AS tax_amount
FROM sl_do A
    INNER JOIN t_ou B ON A.ou_id = B.ou_id
    INNER JOIN sl_do_item C ON A.do_id = C.do_id
    INNER JOIN m_product E ON C.product_id = E.product_id
    INNER JOIN sl_so_item H ON C.ref_id = H.so_item_id
    INNER JOIN sl_so_balance_invoice J ON J.ref_doc_type_id = A.doc_type_id
           AND J.ref_id = A.do_id
           AND J.ref_item_id = C.do_item_id
           AND J.do_receipt_item_id = -99
    INNER JOIN sl_so_balance_invoice_tax I ON I.ref_doc_type_id = A.doc_type_id
           AND I.ref_id = A.do_id
           AND I.ref_item_id = C.do_item_id
           AND I.do_receipt_item_id = -99
    INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
    INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
    INNER JOIN sl_so Q ON J.so_id = Q.so_id
    WHERE A.tenant_id = 10
    AND A.status_doc = 'R'
    AND A.doc_date BETWEEN '20210501' AND '20210531'
    AND A.doc_type_id = 311 -- docType DO
    GROUP BY B.ou_id, B.ou_code, A.doc_date, Q.partner_id, E.product_id, E.product_code, P.group_brand_code
    HAVING SUM(J.qty_dlv_so) <> 0;

--   4. Retur (RN, RRS, DOR)
INSERT INTO tr_data_omzet_toko_detail( -- RN
    ou_id, ou_code, partner_id, partner_code,
    product_id, product_code, product_group_brand, year_month,
    qty, gross_amount, nett_amount, tax_amount
) SELECT I.ou_id, I.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
    E.product_id, E.product_code AS product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
    SUM(C.qty_dlv_so)*(-1) AS qty,
    SUM((C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) + F.tax_amount)*-1,
    SUM(C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0)))*-1,
    SUM(F.tax_amount)*-1 AS tax_amount
FROM in_inventory A
    INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    INNER JOIN sl_so_balance_invoice C ON A.doc_type_id = C.ref_doc_type_id AND C.ref_id = A.inventory_id  AND B.ref_item_id = C.ref_item_id
    INNER JOIN m_product E ON B.product_id = E.product_id
    INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
    INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
    INNER JOIN sl_so Q ON C.so_id = Q.so_id
    INNER JOIN sl_so_balance_invoice_tax F ON A.inventory_id = F.ref_id AND A.doc_type_id = F.ref_doc_type_id AND B.ref_item_id = F.ref_item_id
    INNER JOIN sl_do_item G ON B.ref_item_id = G.do_item_id
    INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
    INNER JOIN t_ou I ON A.ou_from_id = I.ou_id
    WHERE A.tenant_id = 10
    AND A.status_doc = 'R'
    AND A.doc_date BETWEEN '20210501' AND '20210531'
    AND A.doc_type_id = 502 -- docType RN
    GROUP BY I.ou_id, I.ou_code, A.partner_id, E.product_id, E.product_code, P.group_brand_code, A.doc_date;

INSERT INTO tr_data_omzet_toko_detail( -- RN from RRS
    ou_id, ou_code, partner_id, partner_code,
    product_id, product_code, product_group_brand, year_month,
    qty, gross_amount, nett_amount, tax_amount
) SELECT X.ou_id, X.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
    D.product_id, D.product_code AS product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
    (G.qty_dlv_so)*-1,
    C.gross_sell_price * G.qty_dlv_so * (-1) AS omzet_bruto_amount,
    G.item_amount * (-1) AS nett_item_amount,
    ROUND(((C.tax_percentage / (100.0 + C.tax_percentage)) * C.gross_sell_price) * G.qty_dlv_so * (-1)) AS tax_amount
FROM  in_inventory  A
    INNER JOIN sl_so_balance_invoice G ON a.tenant_id = G.tenant_id
        AND G.ou_id = A.ou_from_id
        AND G.ref_doc_type_id = 502
        AND G.ref_id = A.inventory_id
    INNER JOIN sl_so_balance_invoice_ext_for_return_note B ON G.so_balance_invoice_id = B.so_balance_invoice_id
        AND B.flg_from_rrs = 'Y'
    INNER JOIN sl_request_return_sales_item C ON a.tenant_id = C.tenant_id
        AND B.request_return_sales_item_id = C.request_return_sales_item_id
    INNER JOIN m_product D ON A.tenant_id = D.tenant_id
        AND C.product_id = D.product_id
    INNER JOIN t_ou X ON X.ou_id = A.ou_from_id
    INNER JOIN m_product_custom_for_sasa O ON D.product_id = O.product_id
    INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
    LEFT OUTER JOIN sl_invoice H ON H.invoice_id = G.invoice_id AND G.flg_invoice = 'Y'
WHERE A.tenant_id = 10
AND A.status_doc = 'R'
AND A.doc_date BETWEEN '20210501' AND '20210531'
AND A.ref_doc_type_id= 381 -- RRS
AND A.doc_type_id = 502; -- RN

INSERT INTO tr_data_omzet_toko_detail( -- DO Receipt
    ou_id, ou_code, partner_id, partner_code,
    product_id, product_code, product_group_brand, year_month,
    qty, gross_amount, nett_amount, tax_amount
) SELECT I.ou_id, I.ou_code, Q.partner_id, COALESCE(f_get_partner_code(Q.partner_id), 'UMUM') AS partner_code,
E.product_id, E.product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
SUM(C.qty_dlv_so) AS qty,
SUM((C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) + F.tax_amount) AS omzet_bruto_amount,
SUM(C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) AS nett_item_amount,
SUM(F.tax_amount) AS tax_amount
FROM in_do_receipt A
    INNER JOIN in_do_receipt_item B ON A.do_receipt_id = B.do_receipt_id
    INNER JOIN sl_do_item G ON B.ref_id = G.do_item_id AND G.do_id = A.ref_id
    INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
    INNER JOIN sl_so_balance_invoice C ON B.ref_doc_type_id = C.ref_doc_type_id
                    AND C.ref_id = A.ref_id
                    AND B.ref_id = C.ref_item_id
                    AND C.do_receipt_item_id = B.do_receipt_item_id
    INNER JOIN m_product E ON B.product_id = E.product_id
    INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
    INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
    INNER JOIN sl_so Q ON C.so_id = Q.so_id
    INNER JOIN sl_so_balance_invoice_tax F ON B.ref_doc_type_id = F.ref_doc_type_id
                    AND F.ref_id = A.ref_id
                    AND B.ref_id = F.ref_item_id
                    AND F.do_receipt_item_id = B.do_receipt_item_id
    INNER JOIN t_ou I ON A.ou_id = I.ou_id
    WHERE A.tenant_id = 10
    AND A.status_doc = 'R'
    AND A.doc_date BETWEEN '20210501' AND '20210531'
    AND A.doc_type_id = 526 -- docType DOR
    GROUP BY I.ou_id, I.ou_code, A.doc_date, Q.partner_id,E.product_id, E.product_code, P.group_brand_code;


--3. kelompokkan berdasarkan parent group brand, masukkan ke tr_data_omzet_toko_group_brand
-- get data product with group_brand_code 2 huruf
INSERT INTO tr_data_omzet_toko_group_brand(
        ou_id, partner_id, year_month, group_brand_data,
        gross_amount, nett_amount, tax_amount
)
SELECT A.ou_id, A.partner_id, A.year_month,
        CASE WHEN LEFT(group_brand_code, 2) = 'FB' THEN 'FB'
            WHEN LEFT(group_brand_code, 2) = 'FE' THEN 'FE'
            WHEN LEFT(group_brand_code, 2) = 'FL' THEN 'FL'
            WHEN LEFT(group_brand_code, 2) = 'FX' THEN 'FX'
        END AS group_brand_data,
        SUM(A.gross_amount), SUM(A.nett_amount), SUM(A.tax_amount)
    FROM tr_data_omzet_toko_detail A
    INNER JOIN m_group_brand B ON A.product_group_brand = B.group_brand_code
    WHERE LEFT(group_brand_code, 2) IN ('FB', 'FE', 'FL', 'FX')
    GROUP BY A.ou_id, A.partner_id, A.year_month, group_brand_data
    ORDER BY ou_id, partner_id, year_month, group_brand_data;

-- get data product with group_brand_code 3 huruf
INSERT INTO tr_data_omzet_toko_group_brand(
        ou_id, partner_id, year_month, group_brand_data,
        gross_amount, nett_amount, tax_amount
)
SELECT A.ou_id, A.partner_id, A.year_month,
        CASE WHEN group_brand_code = 'FAB' THEN 'FAB'
             WHEN group_brand_code = 'FA2' THEN 'FA2'
             WHEN group_brand_code = 'FA4' THEN 'FA4'
        END AS group_brand_data,
        SUM(A.gross_amount), SUM(A.nett_amount), SUM(A.tax_amount)
    FROM tr_data_omzet_toko_detail A
    INNER JOIN m_group_brand B ON A.product_group_brand = B.group_brand_code
    WHERE group_brand_code IN ('FAB', 'FA2', 'FA4')
    GROUP BY A.ou_id, A.partner_id, A.year_month, group_brand_data
    ORDER BY ou_id, partner_id, year_month, group_brand_data;


-- select terakhir
WITH summary_table AS (
    SELECT ou_id, partner_id, year_month,
        CASE WHEN group_brand_data = 'FB' THEN SUM(gross_amount) ELSE 0 END AS omzet_fb,
        CASE WHEN group_brand_data = 'FE' THEN SUM(gross_amount) ELSE 0 END AS omzet_fe,
        CASE WHEN group_brand_data = 'FL' THEN SUM(gross_amount) ELSE 0 END AS omzet_fl,
        CASE WHEN group_brand_data = 'FX' THEN SUM(gross_amount) ELSE 0 END AS omzet_fx,
        CASE WHEN group_brand_data = 'FAB' THEN SUM(gross_amount) ELSE 0 END AS omzet_fab,
        CASE WHEN group_brand_data IN ('FA2', 'FA4') THEN SUM(gross_amount) ELSE 0 END AS omzet_fa
    FROM tr_data_omzet_toko_group_brand
    --WHERE partner_id = 37406
    GROUP BY ou_id, partner_id, year_month, group_brand_data
    ORDER BY ou_id, partner_id, year_month, group_brand_data
) SELECT B.ou_code, C.partner_code, C.partner_name,
    D.address1, D.address2, D.state_or_province, D.city, D.address3 AS kecamatan, C.price_level,
    COALESCE(E.cp_name, '') AS cp_name, COALESCE(E.phone1, '') AS phone_no, COALESCE(E.cp_identity_no, '') AS ktp_no,
    F.amount_limit AS plafon,
    SUM(omzet_fb) AS omzet_fb, SUM(omzet_fe) AS omzet_fe, SUM(omzet_fl) AS omzet_fl,
    SUM(omzet_fx) AS omzet_fx, SUM(omzet_fab) AS omzet_fab, SUM(omzet_fa) AS omzet_fa
FROM summary_table A
INNER JOIN t_ou B ON A.ou_id = B.ou_id
INNER JOIN m_partner C ON A.partner_id = C.partner_id
INNER JOIN m_partner_address D ON D.partner_id = C.partner_id AND flg_official = 'Y'
LEFT JOIN m_partner_cp E ON A.partner_id = E.partner_id AND E.active = 'Y' AND E.cp_job = 'PIC'
INNER JOIN m_partner_type F ON A.partner_id = F.partner_id
GROUP BY B.ou_code, C.partner_code, C.partner_name, D.address1, D.address2, D.state_or_province, D.city, D.address3, C.price_level,
    E.cp_name, E.phone1, E.cp_identity_no,
    F.amount_limit
ORDER BY B.ou_code, C.partner_code;