path: root/sql/modules/Voucher.sql

CREATE OR REPLACE FUNCTION voucher_get_batch (in_batch_id integer) 
RETURNS batches AS 
$$
DECLARE
    batch_out batches%ROWTYPE;
BEGIN
    SELECT * INTO batch_out FROM batches b WHERE b.id = in_batch_id;
    RETURN batch_out;
END;
$$ language plpgsql;


CREATE TYPE batch_list AS (
id integer,
batch_number text,
description text,
entered date,
approved date,
amount numeric,
employee text,
manager text);

CREATE FUNCTION batch_search
(in_batch text, in_description text, in_batch_number text, in_date_from date,
    in_date_to date, in_date_include date, in_approved boolean) 
RETURNS SETOF batch_list
AS $$
DECLARE
    batch_out batch_list;
BEGIN
    FOR batch_out IN
    SELECT b.id, b.batch, b.batch_number, b.description,
                 b.entered, b.approved, b.amount,
                 e.name AS employee, m.name AS manager
    FROM batches b
    LEFT JOIN employees e ON (b.employee_id = e.id)
    LEFT JOIN employees m ON (b.managerid = m.id)
    WHERE supplied_and_equal(in_batch, b.batch)
        AND supplied_and_like(in_description, description)
        AND supplied_and_like(in_batch_number, batch_number)
        AND supplied_and_later(in_date_from, entered)
        AND supplied_and_earlier(in_date_to, entered)
        AND (coalesce(in_approved, 'f') = (approved IS NULL))

    LOOP
        RETURN NEXT batch_out;
    END LOOP;
END;
$$ language PLPGSQL;

CREATE FUNCTION batch_post (in_batch text, in_login varchar, in_entered date,
    in_batch_number text, in_description text, in_id integer) 
RETURNS integer AS
$$
BEGIN
    UPDATE batches
    SET batch_number = in_batch_number,
        description = in_description,
        entered = in_entered
    WHERE id = in_id;

    IF FOUND THEN 
        RETURN in_id;
    END IF;

    INSERT INTO batches (batch, employee_id, batch_number, description, 
        entered)
    VALUES (in_batch, (SELECT id FROM employees WHERE login = in_login),
        in_batch_number, description);

    RETURN currval('id');
END;
$$ LANGUAGE PLPGSQL;

CREATE TYPE voucher_list AS (
    id int,
    reference text,
    description text,
    batch_id int,
    transaction_id integer,
    amount numeric,
    transaction_date date,
    voucher_number text
);

CREATE OR REPLACE FUNCTION voucher_list_ap (in_batch_id integer) 
RETURNS SETOF voucher_list AS 
$$
DECLARE
    voucher_out voucher_list%ROWTYPE;
BEGIN
    FOR voucher_out IN SELECT v.id, a.invnumber AS reference, 
        c.name ||' -- ' || c.vendornumber AS description,
        v.batch_id, a.id AS transaction_id,
                a.amount, v.voucher_number
    FROM vouchers v
    JOIN ap a ON (a.id = v.trans_id)
    JOIN vendor c ON (c.id = a.vendor_id)
    WHERE v.br_id = in_batch_id

    LOOP
        RETURN NEXT voucher_out;
    END LOOP;
        
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION voucher_list_payment (in_batch_id integer) 
RETURNS SETOF voucher_list AS 
$$
DECLARE
    voucher_out voucher_list%ROWTYPE;
BEGIN
    FOR voucher_out IN SELECT v.id, c.vendornumber AS reference, 
        c.name AS description, in_batch_id AS batch_id,
        v.transaction_id AS transaction_id, sum(ac.amount) AS amount,
        v.voucher_number
    FROM acc_trans ac
    JOIN vouchers v ON (v.id = ac.vr_id AND v.transaction_id = ac.trans_id)
    JOIN chart ch ON (ch.id = ac.chart_id)
    JOIN ap a ON (a.id = ac.trans_id)
    JOIN vendor c ON (c.id = a.vendor_id)
    WHERE v.br_id = in_batch_id
        AND ch.link LIKE '%AP_paid%'
    GROUP BY v.id, c.name, c.vendornumber, v.voucher_number,
                a.vendor_id, v.transaction_id


    LOOP
        RETURN NEXT voucher_out;
    END LOOP;
        
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION voucher_list_payment_reversal (in_batch_id integer) 
RETURNS SETOF voucher_list AS 
$$
DECLARE
    voucher_out voucher_list%ROWTYPE;
BEGIN
    FOR voucher_out IN 
    SELECT v.id, ac.source AS reference, 
        c.vendornumber || ' -- ' || c.name AS description,
                sum(ac.amount) * -1 AS amount, in_batch_id AS batch_id,
        v.transaction_id AS transaction_id, v.voucher_number
    FROM acc_trans ac
    JOIN vr v ON (v.id = ac.vr_id AND v.trans_id = ac.trans_id)
    JOIN chart ch ON (ch.id = ac.chart_id)
    JOIN ap a ON (a.id = ac.trans_id)
    JOIN vendor c ON (c.id = a.vendor_id)
    WHERE vr.br_id = in_batch_id
    AND c.link LIKE '%AP_paid%'
    GROUP BY v.id, c.name, c.vendornumber, v.voucher_number,
    a.vendor_id, ac.source

    LOOP
        RETURN NEXT voucher_out;
    END LOOP;
        
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION voucher_list_ap (in_batch_id integer) 
RETURNS SETOF voucher_list AS 
$$
DECLARE
    voucher_out voucher_list%ROWTYPE;
BEGIN
    FOR voucher_out IN 
    SELECT v.id, g.reference, g.description, in_batch_id AS batch_id,
                SUM(ac.amount) AS amount, g.id AS transaction_id, 
        v.vouchernumber
    FROM acc_trans ac
    JOIN gl g ON (g.id = ac.trans_id)
    JOIN vouchers v ON (v.trans_id = g.id)
    WHERE v.batch_id = in_batch_id
        AND ac.amount >= 0
    GROUP BY g.id, g.reference, g.description, v.id,
        v.voucher_number

    LOOP
        RETURN NEXT voucher_out;
    END LOOP;
        
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION batch_post (in_batch_id integer[], in_batch text, 
    in_control_amount NUMERIC)
RETURNS BOOL AS
$$
DECLARE
    control_amount NUMERIC;
    voucher voucher%ROWTYPE; 
    incriment NUMERIC;
BEGIN
--  CHECK CONTROL NUMBERS
    IF in_batch = 'gl' THEN
        SELECT sum(amount) INTO control_amount 
        FROM acc_trans
        WHERE trans_id IN (
                SELECT id FROM gl 
                WHERE coalesce(approved, false) != true)
            AND trans_id IN (
                SELECT transaction_id FROM voucher 
                WHERE batch_id = ANY (in_batch_id))
            AND coalesce(approved, false) != true
            AND amount > 0
        FOR UPDATE;

    ELSE IF in_batch like '%payment%' THEN

        SELECT sum(ac.amount) INTO control_amount 
        FROM acc_trans ac
        JOIN voucher v ON (v.transaction_id = ac.trans_id)
        WHERE v.batch_id = ANY (in_batch_id)
            AND ac.vr_id = v.id
            AND coalesce(approved, false) = false
        FOR UPDATE;

    ELSE
        SELECT sum(amount) INTO control_amount
        FROM acc_trans 
        WHERE trans_id IN
                (SELECT transaction_id FROM voucher 
                WHERE batch_id = ANY (in_batch_id))
            AND trans_id IN
                (SELECT trans_id FROM ap 
                WHERE coalesce(approved, false) = false)
            AND amount > 0
        FOR UPDATE;

    END IF;

    IF control_amount != in_control_amount THEN
        RETURN FALSE;
    END IF;

--  TODO: POST TRANSACTIONALLY

    IF in_batch like '%payment%' THEN
    ELSE
        UPDATE acc_trans 
        SET approved = true 
        WHERE trans_id IN 
            (SELECT transaction_id FROM voucher
            WHERE batch_id = ANY (in_batch_id));

        IF in_batch = 'gl' THEN

            UPDATE gl SET approved = true
            WHERE trans_id IN
                (SELECT transaction_id FROM voucher
                WHERE batch_id = ANY (in_batch_id));

        ELSE 
            UPDATE ap SET approved = true
            WHERE trans_id IN
                (SELECT transaction_id FROM voucher
                WHERE batch_id = ANY (in_batch_id));
        END IF;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE PLPGSQL;


CREATE OR REPLACE FUNCTION batch_create(
in_batch_number text, in_description text, in_batch_class text) RETURNS int AS
$$
BEGIN
    INSERT INTO 
        batch (batch_class_id, description, control_code, created_by)
    VALUES ((SELECT id FROM batch_class WHERE class = in_batch_class),
        in_description, in_batch_number, 
            (select entity_id FROM users WHERE username = session_user));

    return currval('batch_id_seq');
END;    
$$ LANGUAGE PLPGSQL;