path: root/sql/modules/Drafts.sql

CREATE TYPE draft_search_result AS (
    id int,
    transdate date,
    reference text,
    description text,
    amount numeric
);

CREATE OR REPLACE FUNCTION draft__search(in_type text, in_with_accno text, 
in_from_date date, in_to_date date, in_amount_le numeric, in_amount_ge numeric)
returns setof draft_search_result AS
$$
DECLARE out_row RECORD;
BEGIN
    FOR out_row IN
        SELECT trans.id, trans.transdate, trans.reference, 
            trans.description, 
            sum(case when lower(in_type) = 'ap' AND chart.link = 'AP'
                 THEN line.amount
                 WHEN lower(in_type) = 'ar' AND chart.link = 'AR'
                 THEN line.amount * -1
                 WHEN lower(in_type) = 'gl' AND line.amount > 0
                 THEN line.amount
                 ELSE 0
                END) as amount
        FROM (
            SELECT id, transdate, reference, description, 
                approved from gl
            WHERE lower(in_type) = 'gl'
            UNION
            SELECT id, transdate, invnumber as reference, 
                description::text,
                approved from ap
            WHERE lower(in_type) = 'ap'
            UNION
            SELECT id, transdate, invnumber as reference,
                description, 
                approved from ar
            WHERE lower(in_type) = 'ar'
            ) trans
        JOIN acc_trans line ON (trans.id = line.trans_id)
        JOIN chart ON (line.chart_id = chart.id)
        WHERE (in_from_date IS NULL or trans.transdate >= in_from_date)
            AND (in_to_date IS NULL 
                or trans.transdate <= in_to_date)
            AND trans.approved IS FALSE
            AND trans.id NOT IN (select trans_id from voucher)
        GROUP BY trans.id, trans.transdate, trans.description, trans.reference
        HAVING (in_with_accno IS NULL or in_with_accno = 
            ANY(as_array(chart.accno)))
    LOOP
        RETURN NEXT out_row;
    END LOOP;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION draft_approve(in_id int) returns bool as
$$
declare 
    t_table text;
begin
    SELECT table_name into t_table FROM transactions where id = in_id;

        IF (t_table = 'ar') THEN
        UPDATE ar set approved = true where id = in_id;
    ELSIF (t_table = 'ap') THEN
        UPDATE ap set approved = true where id = in_id;
    ELSIF (t_table = 'gl') THEN
        UPDATE gl set approved = true where id = in_id;
    ELSE
        raise exception 'Invalid table % in draft_approve for transaction %', t_table, in_id;
    END IF;

    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;

    UPDATE transactions 
    SET approved_by = 
            (select entity_id FROM users 
            WHERE username = SESSION_USER), 
        approved_at = now() 
    WHERE id = in_id;

    RETURN TRUE;
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;

CREATE OR REPLACE FUNCTION draft_delete(in_id int) returns bool as
$$
declare 
    t_table text;
begin
        DELETE FROM acc_trans WHERE trans_id = in_id;
    SELECT lower(table_name) into t_table FROM transactions where id = in_id;

        IF t_table = 'ar' THEN
        DELETE FROM ar WHERE id = in_id AND approved IS FALSE;
    ELSIF t_table = 'ap' THEN
        DELETE FROM ap WHERE id = in_id AND approved IS FALSE;
    ELSIF t_table = 'gl' THEN
        DELETE FROM gl WHERE id = in_id AND approved IS FALSE;
    ELSE
        raise exception 'Invalid table % in draft_delete for transaction %', t_table, in_id;
    END IF;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Invalid transaction id %', in_id;
    END IF;
    RETURN TRUE;
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;