f_get_total_achievement_for_report_achievement_vs_target_201

--Adrian, Mar 12, 2018
--Untuk mendapatkan total summary dari atasan + bawahan

CREATE OR REPLACE FUNCTION f_get_total_achievement_for_report_achievement_vs_target(character varying, bigint, bigint, bigint, character varying, bigint, bigint)
  RETURNS void AS
$BODY$
DECLARE
	pSessionId				ALIAS FOR $1;
	pTenantId				ALIAS FOR $2;
	pSalesmanId				ALIAS FOR $3;
	pSalesmanLevel			ALIAS FOR $4;
	pGroupBrand				ALIAS FOR $5;
	pSalesmanHandleId		ALIAS FOR $6;
	pSalesmanHandleLevel	ALIAS FOR $7;

	vJoinSalesman		character varying;
	vFilterSalesman		character varying;

BEGIN

	vJoinSalesman	:= '';
	vFilterSalesman	:= '';

	IF(pSalesmanLevel = 1) THEN
		vJoinSalesman := ' AND C.bod_id = '||pSalesmanId||' ';
	ELSIF (pSalesmanLevel = 2) THEN
		vJoinSalesman := ' AND C.saso_id = '||pSalesmanId||' ';
	ELSIF (pSalesmanLevel = 3) THEN
		vJoinSalesman := ' AND C.sales_manager_id = '||pSalesmanId||' ';
	ELSIF (pSalesmanLevel = 4) THEN
		vJoinSalesman := ' AND C.sales_supervisor_id = '||pSalesmanId||' ';
	ELSIF (pSalesmanLevel = 5) THEN
		vJoinSalesman := ' AND C.salesman_id = '||pSalesmanId||' ';
	ELSIF (pSalesmanLevel = 6) THEN
		vJoinSalesman := ' AND C.salesman_junior_id = '||pSalesmanId||' ';
	END IF;

	IF(pSalesmanLevel + 1 = 1) THEN
		vFilterSalesman := ' AND C.bod_id = B.salesman_id ';
	ELSIF (pSalesmanLevel + 1 = 2) THEN
		vFilterSalesman := ' AND C.saso_id = B.salesman_id ';
	ELSIF (pSalesmanLevel + 1 = 3) THEN
		vFilterSalesman := ' AND C.sales_manager_id = B.salesman_id ';
	ELSIF (pSalesmanLevel + 1 = 4) THEN
		vFilterSalesman := ' AND C.sales_supervisor_id = B.salesman_id ';
	ELSIF (pSalesmanLevel + 1 = 5) THEN
		vFilterSalesman := ' AND C.salesman_id = B.salesman_id ';
	ELSIF (pSalesmanLevel + 1 = 6) THEN
		vFilterSalesman := ' AND C.salesman_junior_id = B.salesman_id ';
	END IF;

	EXECUTE '
		WITH tt_grouped_achievement_from_member AS(
			SELECT B.target_amount, B.year_to_date_accumulative_amount
			FROM tt_summary_for_report_achievement_and_target B
			INNER JOIN tt_salesman_customer_info_for_report_achievement_and_target C
				ON C.session_id = B.session_id AND
					C.tenant_id = B.tenant_id AND
					C.group_brand =  B.group_brand_parent_code '
					|| vJoinSalesman || '
			WHERE B.session_id = $1 AND
				B.tenant_id = $2 AND
				B.salesman_level = $6 AND
				B.group_brand_parent_code = $5 AND
				B.salesman_handle_id = $7 AND
				B.salesman_handle_level = $8 '
				|| vFilterSalesman || '
			GROUP BY B.salesman_id, B.salesman_level, B.target_amount,
				B.year_to_date_accumulative_amount, B.group_brand_parent_code
		), tt_grouped_achievement AS(
			SELECT SUM(B.target_amount) AS target_amount, SUM(B.year_to_date_accumulative_amount) AS year_to_date_accumulative_amount
			FROM tt_grouped_achievement_from_member B
		)
		UPDATE tt_summary_for_report_achievement_and_target Z
		SET target_amount = Z.target_amount + COALESCE(A.target_amount, 0),
			year_to_date_accumulative_amount = Z.year_to_date_accumulative_amount + COALESCE(A.year_to_date_accumulative_amount, 0)
		FROM tt_grouped_achievement A
		WHERE Z.session_id = $1 AND
			Z.tenant_id = $2 AND
			Z.salesman_id = $3 AND
			Z.salesman_level = $4 AND
			Z.group_brand_parent_code = $5 AND
			Z.salesman_handle_id = $7 AND
			Z.salesman_handle_level = $8 '
	USING pSessionId, pTenantId, pSalesmanId, pSalesmanLevel, pGroupBrand, pSalesmanLevel + 1, pSalesmanHandleId, pSalesmanHandleLevel;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 200;
/