--SCRIPT PERBAIKAN DATA CURRENT

DROP TABLE zzz_rekap_mutasi_selish_balance;
DROP TABLE zzz_balance_to_update;
DROP TABLE zzz_balance_to_update_after_calc;

CREATE TABLE zzz_rekap_mutasi_selish_balance AS
WITH mutasi AS
(
	SELECT doc_id,doc_type_id,doc_no,amount,partner_id
	FROM sl_log_partner_credit_limit_usage A
	WHERE doc_id <>-99
	AND doc_type_id <>-99
	AND doc_date >= '20160101'
	GROUP BY doc_id,doc_no,amount,partner_id,doc_type_id
),
balance_invoice_mlm AS
(
	SELECT so_id,ref_doc_no AS doc_no,ref_doc_type_id AS doc_type_id,partner_id,SUM(item_amount_member_price) AS total_amount,
	SUM(
		commission_amount
	) AS total_commission,
	SUM(
		item_amount_member_price -
		commission_amount
	) AS total_amount_minus_commission
	FROM sl_so_mlm_balance_invoice
	WHERE ref_doc_date >= '20160101'
	GROUP BY so_id,ref_doc_type_id,ref_doc_no,partner_id
),
additional_cost AS(
	SELECT B.so_mlm_id,SUM(add_amount)AS add_amount,SUM(tax_amount) AS tax_amount
	FROM mutasi  A
	JOIN sl_do_mlm C ON A.doc_id = C.do_mlm_id AND A.doc_type_id = C.doc_type_id
	JOIN sl_so_mlm_cost B ON C.ref_id = B.so_mlm_id
	GROUP BY B.so_mlm_id
),
calc AS(
SELECT A.doc_no,A.partner_id,A.total_amount_minus_commission,B.amount AS amount_mutasi,(A.total_amount_minus_commission-B.amount) AS selisih,COALESCE(C.add_amount,0) AS additional_amount,(A.total_amount_minus_commission-B.amount+COALESCE(C.add_amount,0)) AS balance_minus_mutasi
FROM balance_invoice_mlm A
INNER JOIN mutasi B ON A.partner_id= B.partner_id
AND A.doc_no = B.doc_no
AND A.doc_type_id = B.doc_type_id
JOIN in_inventory X ON X.inventory_id =B.doc_id AND B.doc_type_id = X.doc_type_id
LEFT JOIN additional_cost C ON C.so_mlm_id = X.ref_id
WHERE (A.total_amount_minus_commission-B.amount+COALESCE(C.add_amount,0))<>0
)
SELECT *
FROM calc
--WHERE partner_id =3763
ORDER BY balance_minus_mutasi ASC;


CREATE TABLE zzz_balance_to_update AS
WITH raw AS(
SELECT ROW_NUMBER() OVER(PARTITION BY so_id ORDER BY so_balance_invoice_id),*
FROM sl_so_mlm_balance_invoice A
WHERE ref_doc_no IN(
	SELECT doc_no FROM zzz_rekap_mutasi_selish_balance
)
)
SELECT *
FROM raw
WHERE row_number = 1;

SELECT * FROM zzz_balance_to_update

CREATE TABLE zzz_balance_to_update_after_calc AS
SELECT A.doc_no,B.so_balance_invoice_id,A.balance_minus_mutasi,B.commission_amount,(B.commission_amount+A.balance_minus_mutasi) AS commission_amount_after_adj
FROM zzz_rekap_mutasi_selish_balance A
JOIN zzz_balance_to_update B ON A.partner_id =B.partner_id AND A.doc_no =B.ref_doc_no;

--BEGIN;
UPDATE sl_so_mlm_balance_invoice A
SET commission_amount = X.commission_amount_after_adj
FROM zzz_balance_to_update_after_calc X
WHERE X.so_balance_invoice_id = A.so_balance_invoice_id;


--ROLLBACK;