CREATE TABLE tt_salesman_from_salesman_hierarchy
(
    session_id VARCHAR(50),
    tenant_id bigint,
    salesman_id bigint,
    group_brand VARCHAR(20),
    sales_child_id bigint,
    period_from VARCHAR(8),
    period_to VARCHAR(8),
    flg_get_child VARCHAR(1)
)
WITH (
  OIDS=FALSE
);

--Adrian, Mar 7, 2018

CREATE OR REPLACE FUNCTION f_get_salesman_from_salesman_hierarchy(character varying, bigint, bigint, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pSalesmanId         ALIAS FOR $3;
    pPeriod             ALIAS FOR $4;

BEGIN

    UPDATE tt_salesman_from_salesman_hierarchy SET flg_get_child = 'Y' WHERE sales_child_id = pSalesmanId;

    INSERT INTO tt_salesman_from_salesman_hierarchy
    (session_id, tenant_id,
    salesman_id, group_brand, sales_child_id, period_from, period_to,
    flg_get_child)
    SELECT pSessionId, A.tenant_id,
    A.sales_leader_id, A.group_brand, A.salesman_id, A.period_from, A.period_to,
    'N'
    FROM m_salesman_hierarchy A
    WHERE A.tenant_id = pTenantId AND
        A.sales_leader_id = pSalesmanId AND
        pPeriod BETWEEN A.period_from AND A.period_to;

    INSERT INTO tt_salesman_from_salesman_hierarchy
    (session_id, tenant_id,
    salesman_id, group_brand, sales_child_id, period_from, period_to,
    flg_get_child)
    SELECT pSessionId, A.tenant_id,
    A.salesman_id, A.group_brand, A.salesman_id, A.period_from, A.period_to,
    'Y'
    FROM m_salesman_hierarchy A
    WHERE A.tenant_id = pTenantId AND
        A.salesman_id = pSalesmanId AND
        pPeriod BETWEEN A.period_from AND A.period_to;

    UPDATE tt_salesman_from_salesman_hierarchy Z
    SET flg_get_child = 'Y'
    WHERE NOT EXISTS (
        SELECT 1
        FROM m_salesman_hierarchy A
        WHERE A.tenant_id = Z.tenant_id AND
            A.sales_leader_id = Z.sales_child_id AND
            A.group_brand = Z.group_brand AND
            pPeriod BETWEEN A.period_from AND A.period_to
    ) AND
    Z.flg_get_child = 'N';

    PERFORM f_get_salesman_from_salesman_hierarchy(pSessionId, pTenantId, A.sales_child_id, pPeriod)
    FROM tt_salesman_from_salesman_hierarchy A
    WHERE flg_get_child = 'N';

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


--Adrian, Mar 7, 2018

CREATE OR REPLACE FUNCTION f_get_customer_from_salesman_hierarchy(character varying, bigint, bigint, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefDetail          REFCURSOR := 'refDetail';
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pSalesmanId         ALIAS FOR $3;
    pPeriod             ALIAS FOR $4;

BEGIN

    DELETE FROM tt_salesman_from_salesman_hierarchy WHERE session_id = pSessionId;

    PERFORM f_get_salesman_from_salesman_hierarchy(pSessionId, pTenantId, pSalesmanId, pPeriod);

    Open pRefDetail FOR
        WITH grouped_salesman AS (
            SELECT A.tenant_id,
                A.sales_child_id, A.group_brand, A.period_from, A.period_to
            FROM tt_salesman_from_salesman_hierarchy A
            WHERE A.session_id = pSessionId AND
                A.tenant_id = pTenantId AND
                pPeriod BETWEEN A.period_from AND A.period_to
            GROUP BY session_id, A.tenant_id,
                A.sales_child_id, A.group_brand, A.period_from, A.period_to
        )
        SELECT B.customer_id AS id, f_get_partner_code(B.customer_id) AS code, f_get_partner_name(B.customer_id) AS name
        FROM grouped_salesman A
        INNER JOIN m_salesman_to_customer B
            ON A.tenant_id = B.tenant_id AND
                A.sales_child_id = B.salesman_id AND
                A.group_brand = B.group_brand
        WHERE A.tenant_id = pTenantId AND
            pPeriod BETWEEN B.period_from AND B.period_to;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_salesman_from_salesman_hierarchy WHERE session_id = pSessionId;

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