BACKUP REPORT MONTHLY OMZET BY CUSTOMER

CREATE OR REPLACE FUNCTION r_monthly_omzet_by_customer(character varying, bigint, character varying, bigint, bigint, character varying, character varying, bigint, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
	pRefHeader				REFCURSOR := 'refHeader';
	pRefDetail				REFCURSOR := 'refDetail';
	pSessionId				ALIAS FOR $1;
	pTenantId				ALIAS FOR $2;
	pYearMonthDate				ALIAS FOR $3;
	pUserId					ALIAS FOR $4;
	pPartnerId				ALIAS FOR $5;
	pTglAwal				ALIAS FOR $6;
	pTglAkhir				ALIAS FOR $7;
	pOuId					ALIAS FOR $8;
	pDatetime				ALIAS FOR $9;

	vEmptyValue				character varying(1);
	vAwalTahun				character varying(6);
	vAkhirRekap				character varying(6);
	vAllId					bigint;
	vEmptyId				bigint;
	vRoundingMode			character varying(5);
	vValutaBuku				character varying(5);
BEGIN

	vEmptyValue := '';
	vAllId := -99;
	vEmptyId := -99;
	vAwalTahun := SUBSTRING(pYearMonthDate, 1, 4) || '01';
	vValutaBuku := 'IDR';

	SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingMode;

	SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vValutaBuku;

	IF SUBSTRING(pYearMonthDate, 5, 2) = '01' THEN
		vAkhirRekap := pYearMonthDate;
	ELSE
		vAkhirRekap := TO_CHAR(to_date(pYearMonthDate, 'YYYYMM') - interval '1 month', 'YYYYMM');
	END IF;

	DELETE FROM tr_sales_gross_profit WHERE session_id = pSessionId;
	DELETE FROM tr_current_sales_gross_profit WHERE session_id = pSessionId;
	DELETE FROM tr_rekap_sales_gross_profit WHERE session_id = pSessionId;
	DELETE FROM tr_sales_gross_profit_for_output WHERE session_id = pSessionId;

	/*
	 * ambil semua so balance invoice di main business unit yang tidak di do receipt
	 * jika transaksi itu ada item assembly ambil berdasarkan core product id nya
	 * ambil juga harga beli terakhir dari item itu sesuai dengan tahun bulan
	 * v341, kolom nett_amount_item dan nett_sell_price sudah tidak nett lagi karena sudah ditambahkan tax
	 */
	INSERT INTO tr_sales_gross_profit(
		   session_id, tenant_id, ou_id, year_month_date, partner_id, partner_code, partner_name,
		   doc_type_id, doc_no, doc_date,
		   product_id, curr_code, qty, nett_sell_price,
		   nett_amount_item,
		   monthly_price_curr_code, monthly_price_amount
	)
	SELECT pSessionId, A.tenant_id, E.ou_bu_id, SUBSTRING(A.ref_doc_date, 1, 6), A.partner_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id),
		   A.ref_doc_type_id, A.ref_doc_no, A.ref_doc_date,
		   B.product_id, A.curr_code, A.qty_dlv_so, (A.price_so + COALESCE(D.tax_price, 0)),
		   SUM(A.item_amount + f_get_so_balance_invoice_tax_amount_by_unique(A.tenant_id, A.ou_id, A.partner_id, A.ref_doc_type_id, A.ref_id, A.ref_item_id, A.do_receipt_item_id)),
		   '', 0
	FROM sl_so_balance_invoice A
	INNER JOIN sl_do_item B ON A.ref_item_id = B.do_item_id
	INNER JOIN sl_do C ON A.ref_doc_type_id = C.doc_type_id AND C.do_id = A.ref_id AND C.do_id = B.do_id
	INNER JOIN sl_so_item D ON B.ref_id = D.so_item_id
	INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
	WHERE A.tenant_id = pTenantId
	AND SUBSTRING(A.ref_doc_date, 1, 6) BETWEEN vAwalTahun AND pYearMonthDate
	AND E.ou_bu_id = pOuId
	GROUP BY A.tenant_id, E.ou_bu_id, A.ref_doc_date, A.partner_id, A.ref_doc_type_id, A.ref_doc_no, B.product_id,
	A.curr_code, A.qty_dlv_so, A.price_so, D.tax_price;

	ANALYZE tr_sales_gross_profit;

	/* v329 data yang diambil dari pu_monthly_price_product diubah
	 * menjadi ambil dari table
	 * tt_out_latest_purchasing_price_by_date menggunakan
	 * pu_get_latest_purchasing_price_by_date yang sebelumnya datanya dimasukkan ke
	 * tt_in_latest_purchasing_price_by_date */

	INSERT INTO tt_in_latest_purchasing_price_by_date(session_id, tenant_id, ou_bu_id, doc_date, product_id)
		SELECT pSessionId, pTenantId, B.ou_bu_id, A.doc_date, A.product_id
	FROM tr_sales_gross_profit A
		INNER JOIN m_ou_structure B ON A.ou_id = B.ou_id
	WHERE A.tenant_id = pTenantId
		AND A.session_id = pSessionId
	GROUP BY B.ou_bu_id, A.doc_date, A.product_id;

	PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, pYearMonthDate, pOuId, pDatetime, pUserId);

	/*sebelum v329
	 UPDATE tr_sales_gross_profit
	--SET monthly_price_curr_code = A.curr_code, monthly_price_amount = ROUND(A.amount / A.qty)
	SET monthly_price_curr_code = COALESCE(A.curr_code, tr_sales_gross_profit.curr_code), monthly_price_amount = COALESCE(f_get_gross_price_from_nett_amount(A.amount, A.qty, A.flg_tax_amount, A.tax_percentage, vRoundingMode, f_get_digit_decimal_doc_curr(-99, A.curr_code)), 0)
	FROM pu_monthly_price_product A
	WHERE tr_sales_gross_profit.session_id = pSessionId
	AND tr_sales_gross_profit.product_id = A.product_id
	AND tr_sales_gross_profit.year_month_date = A.year_month_date
	AND tr_sales_gross_profit.ou_id = A.ou_id;*/

	ANALYZE tt_out_latest_purchasing_price_by_date;

	UPDATE tr_sales_gross_profit B
	--SET monthly_price_curr_code = A.curr_code, monthly_price_amount = ROUND(A.amount / A.qty)
	SET monthly_price_curr_code = COALESCE(A.gl_purch_curr_code, B.curr_code), monthly_price_amount = COALESCE(A.gl_purch_gross_price, 0)
	FROM tt_out_latest_purchasing_price_by_date A
	INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_bu_id
	WHERE B.session_id = A.session_id
	AND B.tenant_id = A.tenant_id
	AND B.product_id = A.product_id
	AND B.doc_date = A.doc_date
	AND C.ou_id = B.ou_id;

	ANALYZE tr_sales_gross_profit;

	/*
	 * update nilai harga beli terakhir menggunakan valuta penjualan (gunakan kurs komersial)

	UPDATE tr_sales_gross_profit SET monthly_price_amount = monthly_price_amount * f_commercial_rate(tenant_id, doc_date, monthly_price_curr_code, vValutaBuku), monthly_price_curr_code = vValutaBuku
	WHERE monthly_price_curr_code <> vValutaBuku
	AND session_id = pSessionId AND monthly_price_curr_code <> '';	 */

	UPDATE tr_sales_gross_profit SET nett_amount_item = nett_amount_item * f_commercial_rate(tenant_id, doc_date, curr_code, vValutaBuku), curr_code = vValutaBuku
	WHERE curr_code <> vValutaBuku
	AND session_id = pSessionId AND curr_code <> '';

	/*
	 * pindahkan data tahun bulan yang diminta (sudah direkap per partner)
	 */
	INSERT INTO tr_current_sales_gross_profit (
		session_id, tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code,
		current_sales_amount, current_monthly_price_amount
	)
	SELECT pSessionId, tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code,
		   SUM(nett_amount_item) AS current_sales_amount, SUM(monthly_price_amount * qty) AS current_monthly_price_amount
	FROM tr_sales_gross_profit
	WHERE session_id = pSessionId
	AND year_month_date = pYearMonthDate
	AND doc_date BETWEEN pTglAwal AND pTglAkhir
	GROUP BY tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code;

	ANALYZE tr_current_sales_gross_profit;

	/*
	 * pindahkan data sebelum tahun bulan yang diminta (sudah direkap per partner)
	 */
	INSERT INTO tr_rekap_sales_gross_profit (
		session_id, tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code,
		rekap_sales_amount, rekap_monthly_price_amount
	)
	SELECT pSessionId, tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code,
		   SUM(nett_amount_item) AS rekap_sales_amount, SUM(monthly_price_amount * qty) AS rekap_monthly_price_amount
	FROM tr_sales_gross_profit
	WHERE session_id = pSessionId
	AND year_month_date <> pYearMonthDate
	GROUP BY tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code;

	ANALYZE tr_current_sales_gross_profit;

	ANALYZE tr_rekap_sales_gross_profit;

	ANALYZE tr_current_sales_gross_profit;

	INSERT INTO tr_sales_gross_profit_for_output
		(session_id, tenant_id, ou_id, partner_id, partner_code, partner_name,
		 curr_code,
		 current_sales_amount,
		 rekap_sales_amount)
	SELECT pSessionId, A.tenant_id, ou_id, A.partner_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id),
	       A.curr_code,
	       A.current_sales_amount,
	       0
	FROM tr_current_sales_gross_profit A
	WHERE A.session_id = pSessionId;

	ANALYZE tr_rekap_sales_gross_profit;

	UPDATE tr_sales_gross_profit_for_output
	SET rekap_sales_amount = A.rekap_sales_amount
	FROM tr_rekap_sales_gross_profit A
	WHERE A.session_id = pSessionId AND tr_sales_gross_profit_for_output.partner_id = A.partner_id;

	ANALYZE tr_rekap_sales_gross_profit;

	INSERT INTO tr_sales_gross_profit_for_output
		(session_id, tenant_id, ou_id, partner_id, partner_code, partner_name,
		 curr_code,
		 current_sales_amount,
		 rekap_sales_amount)
	SELECT pSessionId, A.tenant_id, A.ou_id, A.partner_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id),
	       A.curr_code,
	       0,
	       A.rekap_sales_amount
	FROM tr_rekap_sales_gross_profit A
	WHERE A.session_id = pSessionId
	AND A.partner_id NOT IN ( SELECT B.partner_id FROM tr_sales_gross_profit_for_output B WHERE B.session_id = pSessionId );

	ANALYZE tr_sales_gross_profit_for_output;

	/*
	 * return header
	 */
	Open pRefHeader FOR
	SELECT A.fullname AS username, B.ou_name AS ou_name, pYearMonthDate AS current_month_year, vAwalTahun AS start_month_year, vAkhirRekap AS end_month_year,
	vValutaBuku AS valuta_buku, pDatetime AS datetime
	FROM t_user A
	INNER JOIN t_ou B ON A.tenant_id = B.tenant_id
	WHERE user_id = pUserId
	AND ou_id = pOuId;

	RETURN NEXT pRefHeader;

	IF (pPartnerId <> vEmptyId) THEN
		Open pRefDetail FOR
		SELECT A.partner_id AS partner_id, A.partner_code AS partner_code, A.partner_name AS partner_name,
			   A.curr_code AS curr_code,
			   A.current_sales_amount AS current_sales,
			   A.rekap_sales_amount AS before_sales
		FROM tr_sales_gross_profit_for_output A
		WHERE A.session_id = pSessionId
		AND A.partner_id = pPartnerId
		ORDER BY curr_code, partner_name;
	ELSE
		Open pRefDetail FOR
		SELECT A.partner_id AS partner_id, A.partner_code AS partner_code, A.partner_name AS partner_name,
			   A.curr_code AS curr_code,
			   A.current_sales_amount AS current_sales,
			   A.rekap_sales_amount AS before_sales
		FROM tr_sales_gross_profit_for_output A
		WHERE A.session_id = pSessionId
		ORDER BY curr_code, partner_name;
	END IF;

	RETURN NEXT pRefDetail;

	DELETE FROM tr_sales_gross_profit WHERE session_id = pSessionId;
 	DELETE FROM tr_current_sales_gross_profit WHERE session_id = pSessionId;
 	DELETE FROM tr_rekap_sales_gross_profit WHERE session_id = pSessionId;
 	DELETE FROM tr_sales_gross_profit_for_output WHERE session_id = pSessionId;
 	DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
 	DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /