CREATE OR REPLACE FUNCTION r_report_group_brand_qty_hpp(
    character varying,
    bigint,
    bigint,
    bigint,
    character varying,
    character varying,
    character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
	pRefHeader						REFCURSOR := 'refHeader';
	pRefColumn						REFCURSOR := 'refColumn';
	pRefDetail						REFCURSOR := 'refDetail';
	pSessionId						ALIAS FOR $1;
	pTenantId						ALIAS FOR $2;
	pOuId							ALIAS FOR $3;
	pWarehouseId					ALIAS FOR $4;
	pStockDate						ALIAS FOR $5;
	pDatetime						ALIAS FOR $6;
	pGroupBrandCodeOrName			ALIAS FOR $7;

	vDocTypeDeliveryOrder			bigint;
	vDocTypeDeliveryOrderReceipt	bigint;
	vDocTypePosShop					bigint;
	vDocTypePosShopInShop			bigint;
	vDocTypePosVoidShop				bigint;
	vDocTypePosVoidShopInShop		bigint;
	vFilterOu						text;
	vFilterWarehouse				text;
	vFilterGroupBrandCodeOrName		text;
	vFilterGroupBrandExcFJS		text;

	vEmptyId						bigint;
	vEmptyString					text;
	vMonthRange						bigint;
	vOuCodeName						text;
	vWarehouseCodeName				text;
	vCtgrCode					text:='ALL';
	vSubCtgrCode					text:='ALL';

	vWarehouse 						RECORD;
	vColumName						text[];
	vColumNameForCoalesce			text := '';
	vColumNameWithType				text := '';

	vLatestInvLedgerClosingDate	character varying := '';
	vNextYearMonth			character varying := '';
	vStartDateBalanceStock		character varying := '';
	vLedgerCodeInv			character varying := 'INV';

BEGIN
	vDocTypeDeliveryOrder			:= 311;
	vDocTypeDeliveryOrderReceipt	:= 526;
	vDocTypePosShop					:= 401;
	vDocTypePosShopInShop			:= 403;
	vDocTypePosVoidShop				:= 405;
	vDocTypePosVoidShopInShop		:= 406;
	vFilterOu						:= '';
	vFilterWarehouse				:= '';
	vFilterGroupBrandCodeOrName		:= '';
	vEmptyId						:= -99;
	vEmptyString					:= '';
	vOuCodeName						:= 'ALL';
	vWarehouseCodeName				:= 'ALL';


	DELETE FROM tt_filter_warehouse WHERE session_id = pSessionId;
	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId;
--
--	SELECT (DATE_PART('year', to_date(pYearMonthTo, 'YYYYMM')) - DATE_PART('year', to_date(pYearMonthFrom, 'YYYYMM'))) * 12 +
--	(DATE_PART('month', to_date(pYearMonthTo, 'YYYYMM')) - DATE_PART('month', to_date(pYearMonthFrom, 'YYYYMM'))) + 1 INTO vMonthRange;
--
--	RAISE NOTICE '% vMonthRange', vMonthRange;

	/* get last closing ledger date */
	SELECT MAX(date_year_month)
	FROM m_admin_process_ledger
	WHERE tenant_id = pTenantId
	AND ledger_code = vLedgerCodeInv
	AND date_year_month < SUBSTRING(pStockDate,1,6)
	AND status_ledger = '1' INTO vLatestInvLedgerClosingDate;
	RAISE NOTICE 'vLatestInvLedgerClosingDate : %',vLatestInvLedgerClosingDate;

	SELECT TO_CHAR(TO_DATE(vLatestInvLedgerClosingDate,'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
	vStartDateBalanceStock = vNextYearMonth || '01';
	RAISE NOTICE 'get vStartDateBalanceStock : %',vStartDateBalanceStock;

	raise notice 'vNextYearMonth %', vNextYearMonth;
	raise notice 'vStartDateBalanceStock %', vStartDateBalanceStock;

	-- filter ou id
	IF(pOuId <> vEmptyId) THEN
--		vFilterOu := ' AND A.ou_id = '|| pOuId || '';

		SELECT ou_code||' - '||ou_name INTO vOuCodeName
		FROM t_ou
		WHERE ou_id = pOuId;
	END IF;

	-- filter warehouse id
	IF(pWarehouseId <> vEmptyId) THEN
		vFilterWarehouse := ' AND A.warehouse_id = '|| pWarehouseId || '';

		SELECT warehouse_code||' - '||warehouse_name INTO vWarehouseCodeName
		FROM m_warehouse
		WHERE warehouse_id = pWarehouseId;
	END IF;

	-- filter product code/name
	IF(pGroupBrandCodeOrName <> vEmptyString) THEN
		vFilterGroupBrandCodeOrName := ' AND (G.group_brand_code ILIKE ''%'|| pGroupBrandCodeOrName || '%'' OR G.group_brand_name ILIKE ''%' || pGroupBrandCodeOrName || '%'')';
	END IF;

	RAISE NOTICE '% vFilterOu', vFilterOu;
	RAISE NOTICE '% vFilterWarehouse', vFilterWarehouse;


	/**
	 * bagian query:
	 * 1. mendapatkan list data warehouse
	 * 2. looping warehouse untuk penamaan kolom dan data dalam array -> hanya untuk digunakan dalam crosstab
	 * 3. mendapatkan sumber data
	 * 3.1 mendapatkan data qty dari begining balance tgl 1 -> identifier 1
	 * 3.2 mendapatkan data qty dari tanggal 1 sampai stock date -> identifier 2
	 * 3.3 mengabungkan data qty menjadi 1 identifier -> identifier 3
	 * 3.4 mendapatkan data total qty per group brand produk -> identifier 4
	 * 3.5 mendapatkan data hpp dari monthly cogs -> identifier 5
	 * 3.6 mendapatkan data produk yg belum ada hpp dari montlhy cogs -> identifier 11
	 * 3.7 mendapatkan data rg id terakhir per produk untuk produk yg belum ada hpp dari montlhy cogs ->identifier 12
	 * 3.8 mendapatkan data hpp produk yg belum ada hpp dari monthly cogs berdasarkan data rg id terakhir -> identifier 5
	 * 3.9 menghitung data qty * hpp per produk -> identifier 6
	 * 3.10 menghitung data hpp per group brand -> identifier 7
	 * 4. open pRefHeader -> data untuk header pada template seperti ou dari mana, filter apa saja, dkk
	 * 5. open pRefColumn -> data untuk dynamic gudang digunakan saat looping ke kanan gudang pada template
	 * 6. oper pRefDetail -> data crosstab untuk isi qty dan amount(hpp) data pada template
	 */

	-- 1. mendapatkan list data warehouse
	EXECUTE'
		INSERT INTO tt_filter_warehouse(
			  session_id, warehouse_id, warehouse_code, warehouse_name
		)
		SELECT $2, B.warehouse_id, B.warehouse_code, B.warehouse_name
		FROM m_warehouse_ou A
		JOIN m_warehouse B ON A.warehouse_id = B.warehouse_id
		WHERE B.tenant_id = $1	'||
		vFilterWarehouse ||'
	'USING pTenantId, pSessionId;

	-- LOOPING UNTUK COLUMN
	-- looping warehouse untuk penamaan kolom dan data dalam array -> hanya untuk digunakan dalam crosstab
	FOR vWarehouse IN
		SELECT warehouse_code
		FROM tt_filter_warehouse
		WHERE session_id = pSessionId
		ORDER BY warehouse_code
	LOOP
		vColumName := array_append(vColumName,CONCAT(vWarehouse.warehouse_code,'_qty')); -- mencatat nama column average sales saja (dalam bentuk array)
		vColumName := array_append(vColumName,CONCAT(vWarehouse.warehouse_code,'_qty_amount')); -- mencatat nama column average sales saja (dalam bentuk array)
		vColumNameWithType := CONCAT(vColumNameWithType,',"',vWarehouse.warehouse_code,'_qty','" numeric'); -- mencatat nama column beserta tipe data nya
		vColumNameWithType := CONCAT(vColumNameWithType,',"',vWarehouse.warehouse_code,'_qty_amount','" numeric'); -- mencatat nama column beserta tipe data nya
		vColumNameForCoalesce := CONCAT(vColumNameForCoalesce,', COALESCE("',vWarehouse.warehouse_code,'_qty','", 0) AS "',vWarehouse.warehouse_code,'_qty"');
		vColumNameForCoalesce := CONCAT(vColumNameForCoalesce,', COALESCE("',vWarehouse.warehouse_code,'_qty_amount','", 0) AS "',vWarehouse.warehouse_code,'_qty_amount"');

	END LOOP;

	raise notice 'vColumName %', vColumName;
	raise notice 'vColumNameWithType %', vColumNameWithType;
	raise notice 'vColumNameForCoalesce %', vColumNameForCoalesce;

	-- 3. mendapatkan sumber data
	raise notice '--==0==--';
	-- 3.1 mendapatkan data qty dari begining balance tgl 1 -> identifier 1
	EXECUTE'
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		product_id, product_code, product_name,
		group_brand_id, group_brand_code, group_brand_name,
		code, qty_or_amount, identifier
	)
	SELECT $3,
		D.product_id, D.product_code, D.product_name,
		G.group_brand_id, G.group_brand_code, G.group_brand_name,
		BB.warehouse_code||''_qty'' AS code,
		SUM(A.qty) AS qty, 1
	FROM in_summary_monthly_qty A
	INNER JOIN m_warehouse_ou B ON A.warehouse_id=B.warehouse_id
	INNER JOIN m_warehouse BB ON B.warehouse_id = BB.warehouse_id
	INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
	INNER JOIN m_product D ON D.product_id = A.product_id
	INNER JOIN m_uom E ON D.base_uom_id = E.uom_id
	INNER JOIN m_product_custom_for_sasa F ON D.product_id = F.product_id
	INNER JOIN m_group_brand G ON F.group_brand_id = G.group_brand_id
	WHERE A.tenant_id = $1'||
	vFilterWarehouse ||
	vFilterGroupBrandCodeOrName ||'
	AND A.date_year_month = $2
	GROUP BY BB.warehouse_code, D.product_id, D.product_code, D.product_name,
		G.group_brand_id, G.group_brand_code, G.group_brand_name'
	USING pTenantId, vNextYearMonth, pSessionId;
	raise notice '--==1==--';

	-- 3.2 mendapatkan data qty dari tanggal 1 sampai stock date -> identifier 2
	EXECUTE '
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		product_id, product_code, product_name,
		group_brand_id, group_brand_code, group_brand_name,
		code, qty_or_amount, identifier
	)
	SELECT $1,
		D.product_id, D.product_code, D.product_name,
		G.group_brand_id, G.group_brand_code, G.group_brand_name,
		C.warehouse_code||''_qty'' AS code,
		SUM(AA.qty) AS qty, 2
	FROM in_log_product_balance_stock AA
	INNER JOIN m_warehouse_ou A ON A.warehouse_id = AA.warehouse_id
	INNER JOIN t_ou B ON B.ou_id = A.ou_id
	INNER JOIN m_warehouse C ON C.warehouse_id = A.warehouse_id
	INNER JOIN m_product D ON D.product_id = AA.product_id
	INNER JOIN m_uom E ON D.base_uom_id = E.uom_id
	INNER JOIN m_product_custom_for_sasa F ON D.product_id = F.product_id
	INNER JOIN m_group_brand G ON F.group_brand_id = G.group_brand_id
	WHERE AA.tenant_id = $2
	AND AA.doc_date BETWEEN $3 AND $4 '||
	vFilterWarehouse ||
	vFilterGroupBrandCodeOrName ||'
	GROUP BY C.warehouse_code, D.product_id, D.product_code, D.product_name,
		G.group_brand_id, G.group_brand_code, G.group_brand_name'
	USING pSessionId, pTenantId, vStartDateBalanceStock, pStockDate;
	raise notice '--==2==--';

	-- 3.3 mengabungkan data qty menjadi 1 identifier -> identifier 3
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		product_id, product_code, product_name,
		group_brand_id, group_brand_code, group_brand_name,
		code, qty_or_amount, identifier
	)
	SELECT pSessionId,
		A.product_id, A.product_code, A.product_name,
		A.group_brand_id, A.group_brand_code, A.group_brand_name,
		A.code As code,	SUM(A.qty_or_amount), 3
	FROM tt_product_for_group_brand_qty_hpp A
	WHERE A.session_id = pSessionId
	AND A.identifier IN ( 1, 2 ) -- beginning balance stock, stock from beginning balance to date
	GROUP BY A.product_id, A.product_code, A.product_name,
		A.group_brand_id, A.group_brand_code, A.group_brand_name, A.code;
	raise notice '--==3==--';

	-- hapus redundant data
	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId AND identifier = 1;
	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId AND identifier = 2;

	-- 3.4 mendapatkan data total qty per group brand produk -> identifier 4
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		group_brand_id, group_brand_code, group_brand_name,
		code, qty_or_amount, identifier
	)
	SELECT pSessionId,
		A.group_brand_id, A.group_brand_code, A.group_brand_name,
		A.code As code,	SUM(A.qty_or_amount), 4
	FROM tt_product_for_group_brand_qty_hpp A
	WHERE A.session_id = pSessionId
	AND A.identifier = 3 -- from total stock per product
	GROUP BY A.group_brand_id, A.group_brand_code, A.group_brand_name, A.code;

	-- 3.5 mendapatkan data hpp dari monthly cogs -> identifier 5
	EXECUTE'
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		product_id, product_code, product_name,
		group_brand_id, group_brand_code, group_brand_name,
		code, qty_or_amount, identifier
	)
	SELECT $4,
		D.product_id, D.product_code, D.product_name,
		G.group_brand_id, G.group_brand_code, G.group_brand_name,
		''hpp_average'' AS code, A.avg_price AS hpp_avg_price, 5
	FROM in_summary_monthly_cogs A
	INNER JOIN m_product D ON A.product_id = D.product_id
	INNER JOIN m_uom E ON D.base_uom_id = E.uom_id
	INNER JOIN m_product_custom_for_sasa F ON D.product_id = F.product_id
	INNER JOIN m_group_brand G ON F.group_brand_id = G.group_brand_id
	WHERE A.tenant_id = $1'||
	vFilterGroupBrandCodeOrName ||'
	AND A.date_year_month = $3 '
	USING pTenantId, pOuId, vLatestInvLedgerClosingDate, pSessionId;
	raise notice '--==5.1==--';

	-- 3.6 mendapatkan data produk yg belum ada hpp dari montlhy cogs -> identifier 11
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		product_id, product_code, product_name,
		group_brand_id, group_brand_code, group_brand_name,
		code, identifier
	)
	SELECT pSessionId,
			A.product_id, A.product_code, A.product_name,
			A.group_brand_id, A.group_brand_code, A.group_brand_name,
			'product_wo_hpp', 11
	FROM (
		SELECT A.product_id, A.product_code, A.product_name,
				A.group_brand_id, A.group_brand_code, A.group_brand_name
		FROM tt_product_for_group_brand_qty_hpp A
		WHERE A.session_id = pSessionId
		AND	A.identifier = 3 -- total qty per product
		GROUP BY A.product_id, A.product_code, A.product_name,
				A.group_brand_id, A.group_brand_code, A.group_brand_name
	) A
	WHERE NOT EXISTS (
		SELECT 1
		FROM (
			SELECT product_id
			FROM tt_product_for_group_brand_qty_hpp C
			WHERE session_id = pSessionId
			AND identifier = 5  -- data hpp
			GROUP BY product_id
		) AA
		WHERE A.product_id = AA.product_id
	);
	raise notice '--==5.2==--';

	-- 3.7 mendapatkan data rg id terakhir per produk untuk produk yg belum ada hpp dari montlhy cogs ->identifier 12
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		product_id, product_code, product_name,
		group_brand_id, group_brand_code, group_brand_name,
		code, identifier, rg_id
	)
	SELECT pSessionId,
			A.product_id, A.product_code, A.product_name,
			A.group_brand_id, a.group_brand_code, A.group_brand_name,
			'last_rg_data', 12,
			MAX(C.receive_goods_id) AS id_penerimaan_terakhir
	FROM tt_product_for_group_brand_qty_hpp A
	JOIN pu_receive_goods_item B ON A.product_id = B.product_id
	JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id AND C.status_doc = 'R'
	WHERE A.session_id = pSessionId
	AND A.identifier = 11
	GROUP BY A.product_id, A.product_code, A.product_name,
					A.group_brand_id, a.group_brand_code, A.group_brand_name;
	raise notice '--==5.3==--';

	-- 3.8 mendapatkan data hpp produk yg belum ada hpp dari monthly cogs berdasarkan data rg id terakhir -> identifier 5
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		product_id, product_code, product_name,
		group_brand_id, group_brand_code, group_brand_name,
		code, qty_or_amount, identifier
	)
	SELECT pSessionId,
			A.product_id, A.product_code, A.product_name,
			A.group_brand_id, a.group_brand_code, A.group_brand_name,
			'hpp_average', C.price_po, 5
	FROM tt_product_for_group_brand_qty_hpp A
	JOIN pu_receive_goods_item B ON A.product_id = B.product_id
	JOIN pu_po_balance_invoice C ON C.ref_id = B.receive_goods_id
								AND C.ref_item_id = B.receive_goods_item_id
								AND C.ref_doc_type_id = 111 -- RG
								AND A.rg_id = C.ref_id
	WHERE A.session_id = pSessionId
	AND A.identifier = 12; -- HARUS DARI id_penerimaan terakhir
	raise notice '--==5.4==--';

	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId AND identifier = 11;
	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId AND identifier = 12;

	-- 3.9 menghitung data qty * hpp per produk -> identifier 6
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		product_id, product_code, product_name,
		group_brand_id, group_brand_code, group_brand_name,
		code, qty_or_amount, identifier
	)
	SELECT pSessionId,
		A.product_id, A.product_code, A.product_name,
		A.group_brand_id, A.group_brand_code, A.group_brand_name,
		A.code||'_amount' As code, A.qty_or_amount * COALESCE(B.qty_or_amount,0), 6
	FROM tt_product_for_group_brand_qty_hpp A
	LEFT JOIN tt_product_for_group_brand_qty_hpp B ON A.session_id = B.session_id AND A.product_id = B.product_id AND B.identifier = 5 -- data hpp satuan per product
	WHERE A.session_id = pSessionId
	AND A.identifier = 3; -- data total qty per product
	raise notice '--==6==--';

	-- hapus redundant data
	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId AND identifier = 3; -- data qty per product
	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId AND identifier = 5; -- data hpp satuan per product

	-- 3.10 menghitung data hpp per group brand -> identifier 7
	INSERT INTO tt_product_for_group_brand_qty_hpp(
		session_id,
		group_brand_id, group_brand_code, group_brand_name,
		code, qty_or_amount, identifier
	)
	SELECT pSessionId,
		A.group_brand_id, A.group_brand_code, A.group_brand_name,
		A.code as code,	SUM(A.qty_or_amount), 7
	FROM tt_product_for_group_brand_qty_hpp A
	WHERE A.session_id = pSessionId
	AND A.identifier = 6 -- data hpp amount per product
	GROUP BY A.group_brand_id, A.group_brand_code, A.group_brand_name, A.code;
	raise notice '--==7==--';

	-- hapus redundant data
	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId AND identifier = 6; -- data hpp amount per product;
	-- DONE insert sumber data --

	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId AND group_brand_code = 'FJS'; -- hapus data group brand FJS agar tidak tampil (jasa)


	-- 4. open pRefHeader -> data untuk header pada template seperti ou dari mana, filter apa saja, dkk
	OPEN pRefHeader FOR
		SELECT CASE WHEN pOuId = vEmptyId THEN -99 ELSE pOuId END AS ou_id, vOuCodeName AS ou_code_name,
			CASE WHEN pWarehouseId = vEmptyId THEN -99 ELSE pWarehouseId END AS warehouse_id, vWarehouseCodeName AS warehouse_code_name,
			pDatetime AS datetime,
			pStockDate AS stock_date;
	RETURN NEXT pRefHeader;

	-- 5. open pRefColumn -> data untuk dynamic gudang digunakan saat looping ke kanan gudang pada template
	OPEN pRefColumn FOR
		SELECT warehouse_id, 'qty' AS header, warehouse_code, warehouse_name, warehouse_code||'_qty' AS code
		FROM tt_filter_warehouse A
		WHERE A.session_id = pSessionId
		UNION
		SELECT warehouse_id, 'amount' AS header, warehouse_code, warehouse_name, warehouse_code||'_qty_amount' AS code
		FROM tt_filter_warehouse A
		WHERE A.session_id = pSessionId
		ORDER BY warehouse_code, code;
	RETURN NEXT pRefColumn;

	-- 6. oper pRefDetail -> data crosstab untuk isi qty dan amount(hpp) data pada template
	OPEN pRefDetail FOR
		EXECUTE'
		SELECT group_brand_code, group_brand_name '||
		vColumNameForCoalesce ||
		'
		FROM crosstab(
			''
			SELECT group_brand_code, group_brand_name, code, qty_or_amount
			FROM tt_product_for_group_brand_qty_hpp
			WHERE session_id = '''''|| pSessionId ||'''''
			ORDER BY group_brand_code, group_brand_name, code desc
			'',$$SELECT unnest('''||vColumName::text||'''::text[])$$
		) AS A ("group_brand_code" character varying, "group_brand_name" character varying
			'||vColumNameWithType||
		' )' ;
	RETURN NEXT pRefDetail;

	DELETE FROM tt_filter_warehouse WHERE session_id = pSessionId;
	DELETE FROM tt_product_for_group_brand_qty_hpp WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /