path: root/sql/modules/Payment.sql

CREATE TYPE payment_vc_info AS (
    id int,
    name text,
    entity_class int,
    discount int
);


CREATE OR REPLACE FUNCTION payment_get_entity_accounts
(in_account_class int,
 in_vc_name text,
 in_vc_idn  int)
 returns SETOF payment_vc_info AS
 $$
 DECLARE out_entity payment_vc_info;
 

 BEGIN
    FOR out_entity IN
        SELECT ec.id, cp.legal_name as name, e.entity_class, ec.discount_account_id
        FROM entity e
        JOIN entity_credit_account ec ON (ec.entity_id = e.id)
        JOIN company cp ON (cp.entity_id = e.id)
        WHERE ec.entity_class = in_account_class
        AND (cp.legal_name ilike coalesce('%'||in_vc_name||'%','%%') OR cp.tax_id = in_vc_idn)
    LOOP
        RETURN NEXT out_entity;
    END LOOP;
 END;
 $$ LANGUAGE PLPGSQL;

-- payment_get_open_accounts and the option to get all accounts need to be
-- refactored and redesigned.  -- CT
CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int) 
returns SETOF entity AS
$$
DECLARE out_entity entity%ROWTYPE;
BEGIN
    FOR out_entity IN
        SELECT ec.id, cp.legal_name as name, e.entity_class, e.created 
        FROM entity e
        JOIN entity_credit_account ec ON (ec.entity_id = e.id)
        JOIN company cp ON (cp.entity_id = e.id)
            WHERE ec.entity_class = in_account_class
                   --  AND CASE WHEN in_account_class = 1 THEN
               --       e.id IN (SELECT entity_id FROM ap 
               --           WHERE amount <> paid
           --           GROUP BY entity_id)
           --          WHEN in_account_class = 2 THEN
           --       e.id IN (SELECT entity_id FROM ar
           --           WHERE amount <> paid
           --           GROUP BY entity_id)
           --     END
    LOOP
        RETURN NEXT out_entity;
    END LOOP;
END;
$$ LANGUAGE PLPGSQL;

COMMENT ON FUNCTION payment_get_open_accounts(int) IS
$$ This function takes a single argument (1 for vendor, 2 for customer as 
always) and returns all entities with open accounts of the appropriate type. $$;

CREATE OR REPLACE FUNCTION payment_get_all_accounts(in_account_class int) 
RETURNS SETOF entity AS
$$
DECLARE out_entity entity%ROWTYPE;
BEGIN
    FOR out_entity IN
        SELECT  ec.id, 
            e.name, e.entity_class, e.created 
        FROM entity e
        JOIN entity_credit_account ec ON (ec.entity_id = e.id)
                WHERE e.entity_class = in_account_class
    LOOP
        RETURN NEXT out_entity;
    END LOOP;
END;
$$ LANGUAGE PLPGSQL;

COMMENT ON FUNCTION payment_get_open_accounts(int) IS
$$ This function takes a single argument (1 for vendor, 2 for customer as 
always) and returns all entities with accounts of the appropriate type. $$;


CREATE TYPE payment_invoice AS (
    invoice_id int,
    invnumber text,
    invoice_date date,
    amount numeric,
    amount_fx numeric,
    discount numeric,
    discount_fx numeric,
    due numeric,
    due_fx numeric,
    exchangerate numeric
);

CREATE OR REPLACE FUNCTION payment_get_open_invoices
(in_account_class int,
 in_entity_credit_id int,
 in_curr char(3),
 in_datefrom date, 
 in_dateto date,
 in_amountfrom numeric,
 in_amountto   numeric,
 in_department_id int)
RETURNS SETOF payment_invoice AS
$$
DECLARE payment_inv payment_invoice;
BEGIN
    FOR payment_inv IN
        SELECT a.id AS invoice_id, a.invnumber AS invnumber, 
               a.transdate AS invoice_date, a.amount AS amount, 
               a.amount/
               (CASE WHEN a.curr = (SELECT * from defaults_get_defaultcurrency())
                         THEN 1
                ELSE
                (CASE WHEN in_account_class =1
                      THEN ex.buy
                      ELSE ex.sell END)
                END) as amount_fx, 
               (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate))
                THEN 0
                ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100
                END) AS discount,
                (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate))
                THEN 0
                ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100
                END)/
                (CASE WHEN a.curr = (SELECT * from defaults_get_defaultcurrency())
                         THEN 1
                ELSE
                (CASE WHEN in_account_class =1
                      THEN ex.buy
                      ELSE ex.sell END)
                END) as discount_fx,                
                ac.due - (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate))
                THEN 0
                ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100
                END) AS due,
                (ac.due - (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate))
                THEN 0 
                ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100
                END))/
                (CASE WHEN a.curr = (SELECT * from defaults_get_defaultcurrency())
                         THEN 1
                 ELSE
                 (CASE WHEN in_account_class =1
                      THEN ex.buy
                      ELSE ex.sell END)
                 END) AS due_fx,
                (CASE WHEN a.curr = (SELECT * from defaults_get_defaultcurrency())
                 THEN 1
                 ELSE
                (CASE WHEN in_account_class =1
                 THEN ex.buy
                 ELSE ex.sell END)
                 END) AS exchangerate
                FROM  (SELECT id, invnumber, transdate, amount, entity_id,
                       1 as invoice_class, paid, curr, 
                       entity_credit_account, department_id
                  FROM ap
                         UNION
                 SELECT id, invnumber, transdate, amount, entity_id,
                       2 AS invoice_class, paid, curr,
                       entity_credit_account, department_id

                 FROM ar
                 ) a 
        JOIN (SELECT trans_id, chart_id, sum(CASE WHEN in_account_class = 1 THEN amount
                                          WHEN in_account_class = 2 
                                     THEN amount * -1
                                     END) as due
                FROM acc_trans 
                GROUP BY trans_id, chart_id) ac ON (ac.trans_id = a.id)
                JOIN chart ON (chart.id = ac.chart_id)
                LEFT JOIN exchangerate ex ON ( ex.transdate = a.transdate AND ex.curr = a.curr )         
                JOIN entity_credit_account c ON (c.id = a.entity_credit_account
                        OR (a.entity_credit_account IS NULL and a.entity_id = c.entity_id))
                WHERE ((chart.link = 'AP' AND in_account_class = 1)
                      OR (chart.link = 'AR' AND in_account_class = 2))
                        AND a.invoice_class = in_account_class
                AND c.entity_class = in_account_class
                AND c.id = in_entity_credit_id
                AND a.amount - a.paid <> 0
                AND a.curr = in_curr
                AND (a.transdate >= in_datefrom 
                     OR in_datefrom IS NULL)
                AND (a.transdate <= in_dateto
                     OR in_dateto IS NULL)
                AND (a.amount >= in_amountfrom 
                     OR in_amountfrom IS NULL)
                AND (a.amount <= in_amountto
                     OR in_amountto IS NULL)
                AND (a.department_id = in_department_id
                     OR in_department_id IS NULL)
                AND due <> 0          
                GROUP BY a.invnumber, a.transdate, a.amount, amount_fx, discount, discount_fx, ac.due, a.id, c.discount_terms, ex.buy, ex.sell, a.curr
    LOOP
        RETURN NEXT payment_inv;
    END LOOP;
END;
$$ LANGUAGE PLPGSQL;



COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3), date, date, numeric, numeric, int) IS
$$ This function takes three arguments:
Type: 1 for vendor, 2 for customer
Entity_id:  The entity_id of the customer or vendor
Currency:  3 characters for currency ('USD' for example).
Returns all open invoices for the entity in question. $$;

CREATE TYPE payment_contact_invoice AS (
    contact_id int,
    econtrol_code text,
    eca_description text,
    contact_name text,
    account_number text,
    total_due numeric,
    invoices text[],
        has_vouchers int
);

CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices
(in_account_class int, in_business_id int, in_currency char(3),
    in_date_from date, in_date_to date, in_batch_id int, 
    in_ar_ap_accno text, in_meta_number text)
RETURNS SETOF payment_contact_invoice AS
$$
DECLARE payment_item payment_contact_invoice;
BEGIN
    FOR payment_item IN
          SELECT c.id AS contact_id, e.control_code as econtrol_code, 
            c.description as eca_description, 
            e.name AS contact_name,
                 c.meta_number AS account_number,
                      sum (coalesce(p.due, 0) -
                      CASE WHEN c.discount_terms 
                                > extract('days' FROM age(a.transdate))
                           THEN 0
                           ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100
                      END) AS total_due,
                 compound_array(ARRAY[[
                      a.id::text, a.invnumber, a.transdate::text, 
                      a.amount::text, (a.amount - p.due)::text,
                      (CASE WHEN c.discount_terms 
                                > extract('days' FROM age(a.transdate))
                           THEN 0
                           ELSE (a.amount - coalesce((a.amount - p.due), 0)) * coalesce(c.discount, 0) / 100
                      END)::text, 
                      (coalesce(p.due, 0) -
                      (CASE WHEN c.discount_terms 
                                > extract('days' FROM age(a.transdate))
                           THEN 0
                           ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100
                      END))::text]]),
                              sum(case when a.batch_id = in_batch_id then 1
                          else 0 END),
                      bool_and(lock_record(a.id, (select max(session_id)                FROM "session" where users_id = (
                    select id from users WHERE username =
                    SESSION_USER))))
                           
            FROM entity e
            JOIN entity_credit_account c ON (e.id = c.entity_id)
            JOIN (SELECT ap.id, invnumber, transdate, amount, entity_id, 
                 paid, curr, 1 as invoice_class, 
                         entity_credit_account, on_hold, v.batch_id,
                 approved
                    FROM ap
               LEFT JOIN (select * from voucher where batch_class = 1) v 
                     ON (ap.id = v.trans_id)
               WHERE in_account_class = 1
                     AND (v.batch_class = 1 or v.batch_id IS NULL)
                   UNION
                  SELECT ar.id, invnumber, transdate, amount, entity_id,
                         paid, curr, 2 as invoice_class, 
                         entity_credit_account, on_hold, v.batch_id,
                 approved
                    FROM ar
               LEFT JOIN (select * from voucher where batch_class = 2) v 
                     ON (ar.id = v.trans_id)
               WHERE in_account_class = 2
                     AND (v.batch_class = 2 or v.batch_id IS NULL)
            ORDER BY transdate
                 ) a ON (a.entity_credit_account = c.id)
            JOIN transactions t ON (a.id = t.id)
            JOIN (SELECT trans_id, 
                         sum(CASE WHEN in_account_class = 1 THEN amount
                                  WHEN in_account_class = 2 
                                  THEN amount * -1
                             END) AS due 
                    FROM acc_trans 
                    JOIN chart ON (chart.id = acc_trans.chart_id)
                   WHERE ((chart.link = 'AP' AND in_account_class = 1)
                         OR (chart.link = 'AR' AND in_account_class = 2))
                GROUP BY trans_id) p ON (a.id = p.trans_id)
           WHERE a.batch_id = in_batch_id
                  OR (a.invoice_class = in_account_class
                     AND a.approved
             AND c.business_id = 
                coalesce(in_business_id, c.business_id)
                 AND ((a.transdate >= COALESCE(in_date_from, a.transdate)
                       AND a.transdate <= COALESCE(in_date_to, a.transdate)))
                 AND c.entity_class = in_account_class
                 AND a.curr = in_currency
                 AND a.entity_credit_account = c.id
             AND p.due <> 0
                 AND a.amount <> a.paid 
             AND NOT a.on_hold
             AND NOT (t.locked_by IS NOT NULL AND t.locked_by IN 
                (select "session_id" FROM "session"
                WHERE users_id IN 
                    (select id from users 
                    where username <> SESSION_USER)))
                 AND EXISTS (select trans_id FROM acc_trans
                              WHERE trans_id = a.id AND
                                    chart_id = (SELECT id frOM chart
                                                 WHERE accno
                                                       = in_ar_ap_accno)
                            ))
        GROUP BY c.id, e.name, c.meta_number, c.threshold, 
            e.control_code, c.description
          HAVING (in_meta_number IS NULL 
                OR in_meta_number = c.meta_number) AND 
            (sum(p.due) >= c.threshold
            OR sum(case when a.batch_id = in_batch_id then 1
                                  else 0 END) > 0)
        ORDER BY c.meta_number ASC
    LOOP
        RETURN NEXT payment_item;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION payment_get_all_contact_invoices
(in_account_class int, in_business_id int, in_currency char(3),
    in_date_from date, in_date_to date, in_batch_id int, 
    in_ar_ap_accno text, in_meta_number text) IS
$$
This function takes the following arguments (all prefaced with in_ in the db):
account_class: 1 for vendor, 2 for customer
business_type: integer of business.id.
currency: char(3) of currency (for example 'USD')
date_from, date_to:  These dates are inclusive.
1;3B
batch_id:  For payment batches, where fees are concerned.
ar_ap_accno:  The AR/AP account number.

This then returns a set of contact information with a 2 dimensional array 
cnsisting of outstanding invoices.
$$;

CREATE OR REPLACE FUNCTION payment_bulk_queue
(in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
    in_ar_ap_accno text, in_cash_accno text, 
    in_payment_date date, in_account_class int)
returns int as
$$
BEGIN
    INSERT INTO payments_queue
    (transactions, batch_id, source, total, ar_ap_accno, cash_accno,
        payment_date, account_class)
    VALUES 
    (in_transactions, in_batch_id, in_source, in_total, in_ar_ap_accno,
        in_cash_accno, in_payment_date, in_account_class);

    RETURN array_upper(in_transactions, 1) - 
        array_lower(in_transactions, 1);
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION job__process_payment(in_job_id int)
RETURNS bool AS $$
DECLARE 
    queue_record RECORD;
    t_auth_name text;
    t_counter int;
BEGIN
    -- TODO:  Move the set session authorization into a utility function
    SELECT entered_by INTO t_auth_name FROM pending_job
    WHERE id = in_job_id;

    EXECUTE 'SET SESSION AUTHORIZATION ' || quote_ident(t_auth_name);

    t_counter := 0;
    
    FOR queue_record IN 
        SELECT * 
        FROM payments_queue WHERE job_id = in_job_id
    LOOP
        PERFORM payment_bulk_post
            (queue_record.transactions, queue_record.batch_id, 
                queue_record.source, queue_record.total, 
                queue_record.ar_ap_accno, 
                queue_record.cash_accno, 
                queue_record.payment_date, 
                queue_record.account_class);

        t_counter := t_counter + 1;
        RAISE NOTICE 'Processed record %, starting transaction %', 
            t_counter, queue_record.transactions[1][1];
    END LOOP;   
    DELETE FROM payments_queue WHERE job_id = in_job_id;

    UPDATE pending_job
    SET completed_at = timeofday()::timestamp,
        success = true
    WHERE id = in_job_id;
    RETURN TRUE;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION job__create(in_batch_class int, in_batch_id int)
RETURNS int AS
$$
BEGIN
    INSERT INTO pending_job (batch_class, batch_id)
    VALUES (coalesce(in_batch_class, 3), in_batch_id);

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

CREATE TYPE job__status AS (
    completed int, -- 1 for completed, 0 for no
    success int, -- 1 for success, 0 for no
    completed_at timestamp,
    error_condition text -- error if not successful
);

CREATE OR REPLACE FUNCTION job__status(in_job_id int) RETURNS job__status AS
$$
DECLARE out_row job__status;
BEGIN
    SELECT  (completed_at IS NULL)::INT, success::int, completed_at,
        error_condition
    INTO out_row 
    FROM pending_job
    WHERE id = in_job_id;

    RETURN out_row;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION payment_bulk_post
(in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
    in_ar_ap_accno text, in_cash_accno text, 
    in_payment_date date, in_account_class int)
RETURNS int AS
$$
DECLARE 
    out_count int;
    t_voucher_id int;
    t_trans_id int;
    t_amount numeric;
        t_ar_ap_id int;
    t_cash_id int;
BEGIN
    IF in_batch_id IS NULL THEN
        -- t_voucher_id := NULL;
        RAISE EXCEPTION 'Bulk Post Must be from Batch!';
    ELSE
        INSERT INTO voucher (batch_id, batch_class, trans_id)
        values (in_batch_id, 3, in_transactions[1][1]);

        t_voucher_id := currval('voucher_id_seq');
    END IF;

    CREATE TEMPORARY TABLE bulk_payments_in (id int, amount numeric);

    select id into t_ar_ap_id from chart where accno = in_ar_ap_accno;
    select id into t_cash_id from chart where accno = in_cash_accno;

    FOR out_count IN 
            array_lower(in_transactions, 1) ..
            array_upper(in_transactions, 1)
    LOOP
        EXECUTE $E$
            INSERT INTO bulk_payments_in(id, amount)
            VALUES ($E$ || quote_literal(in_transactions[out_count][1])
                || $E$, $E$ ||
                quote_literal(in_transactions[out_count][2])
                || $E$)$E$;
    END LOOP;
    EXECUTE $E$ 
        INSERT INTO acc_trans 
            (trans_id, chart_id, amount, approved, voucher_id, transdate, 
            source)
        SELECT id, 
        case when $E$ || quote_literal(in_account_class) || $E$ = 1
            THEN $E$ || t_cash_id || $E$
            WHEN $E$ || quote_literal(in_account_class) || $E$ = 2 
            THEN $E$ || t_ar_ap_id || $E$
            ELSE -1 END, 
        amount,
        CASE 
            WHEN $E$|| t_voucher_id || $E$ IS NULL THEN true
            ELSE false END,
        $E$ || t_voucher_id || $E$, $E$|| quote_literal(in_payment_date) 
        ||$E$ , $E$ ||COALESCE(quote_literal(in_source), 'NULL') ||$E$
        FROM bulk_payments_in $E$;

    EXECUTE $E$ 
        INSERT INTO acc_trans 
            (trans_id, chart_id, amount, approved, voucher_id, transdate, 
            source)
        SELECT id, 
        case when $E$ || quote_literal(in_account_class) || $E$ = 1 
            THEN $E$ || t_ar_ap_id || $E$
            WHEN $E$ || quote_literal(in_account_class) || $E$ = 2 
            THEN $E$ || t_cash_id || $E$
            ELSE -1 END, 
        amount * -1,
        CASE 
            WHEN $E$|| t_voucher_id || $E$ IS NULL THEN true
            ELSE false END,
        $E$ || t_voucher_id || $E$, $E$|| quote_literal(in_payment_date) 
        ||$E$ , $E$ ||COALESCE(quote_literal(in_source), 'null') ||$E$
        FROM bulk_payments_in $E$;

    EXECUTE $E$
        UPDATE ap 
        set paid = paid + (select amount from bulk_payments_in b 
            where b.id = ap.id)
        where id in (select id from bulk_payments_in) $E$;
    EXECUTE $E$ DROP TABLE bulk_payments_in $E$;
    perform unlock_all();
    return out_count;
END;
$$ language plpgsql;

COMMENT ON FUNCTION payment_bulk_post
(in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
        in_ar_ap_accno text, in_cash_accno text, 
        in_payment_date date, in_account_class int)
IS
$$ Note that in_transactions is a two-dimensional numeric array.  Of each 
sub-array, the first element is the (integer) transaction id, and the second
is the amount for that transaction.  $$;

--
-- WE NEED A PAYMENT TABLE 
--

CREATE TABLE payment (
  id serial primary key,
  reference text NOT NULL,
  gl_id     integer references gl(id),
  payment_class integer NOT NULL,
  payment_date date default current_date,
  closed bool default FALSE,
  entity_credit_id   integer references entity_credit_account(id),
  employee_id integer references entity_employee(entity_id),
  currency char(3),
  notes text,
  department_id integer default 0);
              
COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, et$$;
COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment order  $$; 
COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$;
COMMENT ON COLUMN payment.gl_id IS $$ A payment should always be linked to a GL movement $$;
CREATE  INDEX payment_id_idx ON payment(id);
                  
CREATE TABLE payment_links (
  payment_id integer references Payment(id),
  entry_id   integer references acc_trans(entry_id),
  type       integer);
COMMENT ON TABLE payment_links IS $$  
 An explanation to the type field.
 * A type 0 means the link is referencing an ar/ap  and was created
   using an overpayment movement after the receipt was created 
 * A type 1 means the link is referencing an ar/ap and  was made 
   on the payment creation, its not the product of an overpayment movement 
 * A type 2 means the link is not referencing an ar/ap and its the product
   of the overpayment logic 

 With this ideas in order we can do the following

 To get the payment amount we will sum the entries with type > 0.
 To get the linked amount we will sum the entries with type < 2.
 The overpayment account can be obtained from the entries with type = 2.

 This reasoning is hacky and i hope it can dissapear when we get to 1.4 - D.M.
$$;
 
CREATE OR REPLACE FUNCTION payment_post 
(in_datepaid              date,
 in_account_class         int,
 in_entity_credit_id                     int,
 in_curr                  char(3),
 in_notes                         text,
 in_department_id                 int,
 in_gl_description                text,
 in_cash_account_id               int[],
 in_amount                        numeric[],
 in_cash_approved                 bool[],
 in_source                        text[],
 in_memo                          text[], 
 in_transaction_id                int[],
 in_op_amount                     numeric[],
 in_op_cash_account_id            int[],
 in_op_source                     text[], 
 in_op_memo                       text[],
 in_op_account_id                 int[],                   
 in_approved                      bool)
RETURNS INT AS
$$
DECLARE var_payment_id int;
DECLARE var_gl_id int;
DECLARE var_entry record;
DECLARE var_entry_id int[];
DECLARE out_count int;
DECLARE coa_id record;
DECLARE var_employee int;
DECLARE var_account_id int;
DECLARE default_currency char(3);
DECLARE current_exchangerate numeric;
DECLARE old_exchangerate numeric;
DECLARE tmp_amount numeric;
BEGIN
        
        SELECT * INTO default_currency  FROM defaults_get_defaultcurrency(); 
        SELECT * INTO current_exchangerate FROM currency_get_exchangerate(in_curr, in_datepaid, in_account_class);


        SELECT INTO var_employee entity_id FROM users WHERE username = SESSION_USER LIMIT 1;
        -- 
        -- WE HAVE TO INSERT THE PAYMENT, USING THE GL INFORMATION
        -- THE ID IS GENERATED BY payment_id_seq
        --
    INSERT INTO payment (reference, payment_class, payment_date,
                          employee_id, currency, notes, department_id, entity_credit_id) 
    VALUES ((CASE WHEN in_account_class = 1 THEN
                                    setting_increment('rcptnumber') -- I FOUND THIS ON sql/modules/Settings.sql 
                         ELSE                       -- and it is very usefull               
                            setting_increment('paynumber') 
                         END),
             in_account_class, in_datepaid, var_employee,
                 in_curr, in_notes, in_department_id, in_entity_credit_id);
        SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table
        -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT
        -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS... 
        --
        -- FIRST WE SHOULD INSERT THE CASH ACCOUNTS
        --
        -- WE SHOULD HAVE THE DATA STORED AS (ACCNO, AMOUNT), SO
    FOR out_count IN 
            array_lower(in_cash_account_id, 1) ..
            array_upper(in_cash_account_id, 1)
    LOOP
            INSERT INTO acc_trans (chart_id, amount,
                               trans_id, transdate, approved, source, memo)
        VALUES (in_cash_account_id[out_count], 
                CASE WHEN in_account_class = 1 THEN in_amount[out_count]*current_exchangerate  
                ELSE (in_amount[out_count]*current_exchangerate)* - 1
                END,
                in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), 
                in_source[out_count], in_memo[out_count]);
                INSERT INTO payment_links 
        VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1);
        
    END LOOP;
    -- NOW LETS HANDLE THE AR/AP ACCOUNTS
    -- WE RECEIVED THE TRANSACTIONS_ID AND WE CAN OBTAIN THE ACCOUNT FROM THERE
    FOR out_count IN
             array_lower(in_transaction_id, 1) ..
             array_upper(in_transaction_id, 1)
       LOOP
               SELECT INTO var_account_id chart_id FROM acc_trans as ac
            JOIN chart as c ON (c.id = ac.chart_id) 
                WHERE 
                trans_id = in_transaction_id[out_count] AND
                ( c.link = 'AP' OR c.link = 'AR' );
        -- We need to know the exchangerate of this transaction
        IF (current_exchangerate = 1 ) THEN 
           old_exchangerate := 1;
        ELSIF (in_account_class = 1) THEN
           SELECT buy INTO old_exchangerate 
           FROM exchangerate e
           JOIN ap a on (a.transdate = e.transdate )
           WHERE a.id = in_transaction_id[out_count];
        ELSE 
           SELECT sell INTO old_exchangerate 
           FROM exchangerate e
           JOIN ar a on (a.transdate = e.transdate )
           WHERE a.id = in_transaction_id[out_count];
        END IF;
        -- Now we post the AP/AR transaction
        INSERT INTO acc_trans (chart_id, amount,
                                trans_id, transdate, approved, source, memo)
        VALUES (var_account_id, 
                CASE WHEN in_account_class = 1 THEN 
                
                (in_amount[out_count]*old_exchangerate) * -1 
                ELSE in_amount[out_count]*old_exchangerate
                END,
                in_transaction_id[out_count], in_datepaid,  coalesce(in_approved, true), 
                in_source[out_count], in_memo[out_count]);
        -- Lets set the gain/loss, if tmp_amount equals zero then we dont need to post
        -- any transaction
        tmp_amount := in_amount[out_count]*current_exchangerate - in_amount[out_count]*old_exchangerate;
       IF (tmp_amount < 0) THEN
          IF (in_account_class  = 1) THEN
           INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source)
            VALUES (CAST((select value from defaults where setting_key like 'fxloss_accno_id') AS INT),
                    tmp_amount, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
                    in_source[out_count]);
           ELSE
            INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source)
            VALUES (CAST((select value from defaults where setting_key like 'fxgain_accno_id') AS INT),
                    tmp_amount, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
                    in_source[out_count]);
          END IF;
        ELSIF (tmp_amount > 0) THEN
          IF (in_account_class  = 1) THEN
            INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source)
            VALUES (CAST((select value from defaults where setting_key like 'fxgain_accno_id') AS INT),
                    tmp_amount, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
                    in_source[out_count]);
           ELSE
            INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source)
            VALUES (CAST((select value from defaults where setting_key like 'fxloss_accno_id') AS INT),
                    tmp_amount, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
                    in_source[out_count]);
          END IF; 
        END IF; 
        -- Now we set the links
         INSERT INTO payment_links 
        VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1);
      END LOOP;
--
-- WE NEED TO HANDLE THE OVERPAYMENTS NOW
--
       --
       -- FIRST WE HAVE TO MAKE THE GL TO HOLD THE OVERPAYMENT TRANSACTIONS
       -- THE ID IS GENERATED BY gl_id_seq
       --
       
  IF (array_upper(in_op_cash_account_id, 1) > 0) THEN
       INSERT INTO gl (reference, description, transdate,
                       person_id, notes, approved, department_id) 
              VALUES (setting_increment('glnumber'),
                  in_gl_description, in_datepaid, var_employee,
                  in_notes, in_approved, in_department_id);
       SELECT currval('id') INTO var_gl_id;   
--
-- WE NEED TO SET THE GL_ID FIELD ON PAYMENT'S TABLE
--
       UPDATE payment SET gl_id = var_gl_id 
       WHERE id = var_payment_id;
       -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS... 
       --
       -- FIRST WE SHOULD INSERT THE OVERPAYMENT CASH ACCOUNTS
       --
    FOR out_count IN 
            array_lower(in_op_cash_account_id, 1) ..
            array_upper(in_op_cash_account_id, 1)
    LOOP
            INSERT INTO acc_trans (chart_id, amount,
                               trans_id, transdate, approved, source, memo)
        VALUES (in_op_cash_account_id[out_count], 
                CASE WHEN in_account_class = 2 THEN in_op_amount[out_count]  
                ELSE in_op_amount[out_count] * - 1
                END,
                var_gl_id, in_datepaid, coalesce(in_approved, true), 
                in_op_source[out_count], in_op_memo[out_count]);
            INSERT INTO payment_links 
        VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
    END LOOP;
    -- NOW LETS HANDLE THE OVERPAYMENT ACCOUNTS
    FOR out_count IN
             array_lower(in_op_account_id, 1) ..
             array_upper(in_op_account_id, 1)
    LOOP
         INSERT INTO acc_trans (chart_id, amount,
                                trans_id, transdate, approved, source, memo)
        VALUES (in_op_account_id[out_count], 
                CASE WHEN in_account_class = 2 THEN in_op_amount[out_count] * -1 
                ELSE in_op_amount[out_count]
                END,
                var_gl_id, in_datepaid,  coalesce(in_approved, true), 
                in_op_source[out_count], in_op_memo[out_count]);
        INSERT INTO payment_links 
        VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
    END LOOP;           
 END IF;  
 return var_payment_id;
END;
$$ LANGUAGE PLPGSQL;
-- I HAVE TO MAKE A COMMENT ON THIS FUNCTION

-- Move this to the projects module when we start on that. CT
CREATE OR REPLACE FUNCTION project_list_open(in_date date) 
RETURNS SETOF project AS
$$
DECLARE out_project project%ROWTYPE;
BEGIN
    FOR out_project IN
        SELECT * from project
        WHERE startdate <= in_date AND enddate >= in_date
              AND completed = 0
    LOOP
        return next out_project;
    END LOOP;
END;
$$ language plpgsql;

comment on function project_list_open(in_date date) is
$$ This function returns all projects that were open as on the date provided as
the argument.$$;
-- Move this to the projects module when we start on that. CT


CREATE OR REPLACE FUNCTION department_list(in_role char)
RETURNS SETOF department AS
$$
DECLARE out_department department%ROWTYPE;
BEGIN
       FOR out_department IN
               SELECT * from department
               WHERE role = coalesce(in_role, role)
       LOOP
               return next out_department;
       END LOOP;
END;
$$ language plpgsql;
-- Move this into another module.

comment on function department_list(in_role char) is
$$ This function returns all department that match the role provided as
the argument.$$;

CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int)
RETURNS SETOF char(3) AS
$$
DECLARE resultrow record;
BEGIN
        FOR resultrow IN
          SELECT DISTINCT curr FROM ar
          UNION
          SELECT DISTINCT curr FROM ap 
          ORDER BY curr
          LOOP
         return next resultrow.curr;
        END LOOP;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) 
RETURNS NUMERIC AS
$$
DECLARE 
    out_exrate exchangerate.buy%TYPE;
    default_currency char(3);
    
    BEGIN 
        SELECT * INTO default_currency  FROM defaults_get_defaultcurrency();
        IF default_currency = in_currency THEN
           RETURN 1;
        END IF; 
        IF in_account_class = 1 THEN
          SELECT buy INTO out_exrate 
          FROM exchangerate
          WHERE transdate = in_date AND curr = in_currency;
        ELSE 
          SELECT sell INTO out_exrate 
          FROM exchangerate
          WHERE transdate = in_date AND curr = in_currency;   
        END IF;
        RETURN out_exrate;
    END;
$$ language plpgsql;                                                                  
COMMENT ON FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) IS
$$ This function return the exchange rate of a given currency, date and exchange rate class (buy or sell). $$;

--
--  payment_location_result has the same arch as location_result, except for one field 
--  This should be unified on the API when we get things working - David Mora
--
CREATE TYPE payment_location_result AS (
        id int,
        line_one text,
        line_two text,
        line_three text,
        city text,
        state text,
    mail_code text,
        country text,
        class text
);

--
--  payment_get_vc_info has the same arch as company__list_locations, except for the filtering capabilities 
--  This should be unified on the API when we get things working - David Mora
--
CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_credit_id int, in_location_class_id int)
RETURNS SETOF payment_location_result AS
$$
DECLARE out_row payment_location_result;
    BEGIN
        FOR out_row IN
                SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
                       l.state, l.mail_code, c.name, lc.class
                FROM location l
                JOIN company_to_location ctl ON (ctl.location_id = l.id)
                JOIN company cp ON (ctl.company_id = cp.id)
                JOIN location_class lc ON (ctl.location_class = lc.id)
                JOIN country c ON (c.id = l.country_id)
                JOIN entity_credit_account ec ON (ec.entity_id = cp.entity_id)
                WHERE ec.id = in_entity_credit_id AND
                      lc.id = in_location_class_id
                ORDER BY lc.id, l.id, c.name
                LOOP
                    RETURN NEXT out_row;
        END LOOP;
    END;
$$ LANGUAGE PLPGSQL;

COMMENT ON FUNCTION payment_get_vc_info(in_entity_id int, in_location_class_id int) IS
$$ This function returns vendor or customer info $$;

CREATE TYPE payment_record AS (
    amount numeric,
    meta_number text,
        credit_id int,
    company_paid text,
    accounts text[],
        source text,
        date_paid date
);

CREATE OR REPLACE FUNCTION payment__search 
(in_source text, in_date_from date, in_date_to date, in_credit_id int, 
    in_cash_accno text, in_account_class int)
RETURNS SETOF payment_record AS
$$
DECLARE 
    out_row payment_record;
BEGIN
    FOR out_row IN 
        select sum(CASE WHEN c.entity_class = 1 then a.amount
                ELSE a.amount * -1 END), c.meta_number, 
            c.id, co.legal_name,
            compound_array(ARRAY[ARRAY[ch.id::text, ch.accno, 
                ch.description]]), a.source, a.transdate
        FROM entity_credit_account c
        JOIN ( select entity_credit_account, id
            FROM ar WHERE in_account_class = 2
            UNION
            SELECT entity_credit_account, id
            FROM ap WHERE in_account_class = 1
            ) arap ON (arap.entity_credit_account = c.id)
        JOIN acc_trans a ON (arap.id = a.trans_id)
        JOIN chart ch ON (ch.id = a.chart_id)
        JOIN company co ON (c.entity_id = co.entity_id)
        WHERE (ch.accno = in_cash_accno)
            AND (c.id = in_credit_id OR in_credit_id IS NULL)
            AND (a.transdate >= in_date_from 
                OR in_date_from IS NULL)
            AND (a.transdate <= in_date_to OR in_date_to IS NULL)
            AND (source = in_source OR in_source IS NULL)
        GROUP BY c.meta_number, c.id, co.legal_name, a.transdate, 
            a.source
        ORDER BY a.transdate, c.meta_number, a.source
    LOOP
        RETURN NEXT out_row;
    END LOOP;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION payment__reverse
(in_source text, in_date_paid date, in_credit_id int, in_cash_accno text, 
    in_date_reversed date, in_account_class int, in_batch_id int)
RETURNS INT 
AS $$
DECLARE
    pay_row record;
        t_voucher_id int;
        t_voucher_inserted bool;
BEGIN
        IF in_batch_id IS NOT NULL THEN
        t_voucher_id := nextval('voucher_id_seq');
        t_voucher_inserted := FALSE;
    END IF;
    FOR pay_row IN 
        SELECT a.*, c.ar_ap_account_id
        FROM acc_trans a
        JOIN (select id, entity_credit_account 
            FROM ar WHERE in_account_class = 2
            UNION
            SELECT id, entity_credit_account
            FROM ap WHERE in_account_class = 1
        ) arap ON (a.trans_id = arap.id)
        JOIN entity_credit_account c 
            ON (arap.entity_credit_account = c.id)
        JOIN chart ch ON (a.chart_id = ch.id)
        WHERE coalesce(source, '') = coalesce(in_source, '')
            AND transdate = in_date_paid
            AND in_credit_id = c.id
            AND in_cash_accno = ch.accno
    LOOP
        IF in_batch_id IS NOT NULL 
            AND t_voucher_inserted IS NOT TRUE
        THEN
            INSERT INTO voucher 
            (id, trans_id, batch_id, batch_class)
            VALUES
            (t_voucher_id, pay_row.trans_id, in_batch_id,
                CASE WHEN in_account_class = 1 THEN 4
                     WHEN in_account_class = 2 THEN 7
                END);

            t_voucher_inserted := TRUE;
        END IF;

        INSERT INTO acc_trans
        (trans_id, chart_id, amount, transdate, source, memo, approved,
            voucher_id) 
        VALUES 
        (pay_row.trans_id, pay_row.chart_id, pay_row.amount * -1, 
            in_date_reversed, in_source, 'Reversing ' || 
            COALESCE(in_source, ''), 
            case when in_batch_id is not null then false 
            else true end, t_voucher_id);
        INSERT INTO acc_trans
        (trans_id, chart_id, amount, transdate, source, memo, approved,
            voucher_id) 
        VALUES 
        (pay_row.trans_id, pay_row.ar_ap_account_id, pay_row.amount,
            in_date_reversed, in_source, 'Reversing ' ||
            COALESCE(in_source, ''), 
            case when in_batch_id is not null then false 
            else true end, t_voucher_id);
        IF in_account_class = 1 THEN
            UPDATE ap SET paid = amount - 
                (SELECT sum(a.amount) 
                FROM acc_trans a
                JOIN chart c ON (a.chart_id = c.id)
                WHERE c.link = 'AP'
                    AND trans_id = pay_row.trans_id
                ) 
            WHERE id = pay_row.trans_id;
        ELSIF in_account_class = 2 THEN
            update ar SET paid = amount - 
                (SELECT sum(a.amount) 
                FROM acc_trans a
                JOIN chart c ON (a.chart_id = c.id)
                WHERE c.link = 'AR'
                    AND trans_id = pay_row.trans_id
                ) * -1
            WHERE id = pay_row.trans_id;
        ELSE
            RAISE EXCEPTION 'Unknown account class for payments %',
                in_account_class;
        END IF;
    END LOOP;
    RETURN 1;
END;
$$ LANGUAGE PLPGSQL;


CREATE OR REPLACE FUNCTION payments_set_exchangerate(in_account_class int,
 in_exchangerate numeric, in_curr char(3), in_datepaid date )
RETURNS INT
AS $$
DECLARE current_exrate  exchangerate%ROWTYPE;
BEGIN
select  * INTO current_exrate
        FROM  exchangerate 
        WHERE transdate = in_date;
IF current_exrate.transdate = in_date THEN
   IF in_account_class = 1 THEN 
      UPDATE exchangerate set buy = in_exchangerate  where transdate = in_date;
   ELSE
      UPDATE exchangerate set sell = in_exchangerate where transdate = in_date;
   END IF;
   RETURN 0; 
ELSE
    IF in_account_class = 1 THEN
     INSERT INTO exchangerate (curr, transdate, buy) values (in_currency, in_date, in_exchangerate);
  ELSE   
     INSERT INTO exchangerate (curr, transdate, sell) values (in_currency, in_date, in_exchangerate);
  END IF;                                       
RETURN 0;
END IF;
END;
$$ language plpgsql;


CREATE TYPE payment_header_item AS (
payment_id int,
payment_reference int,
payment_date date,
legal_name text,
amount numeric,
employee_first_name text,
employee_last_name  text,
currency char(3),
notes text
);
-- I NEED TO PLACE THE COMPANY TELEPHONE AND ALL THAT STUFF
CREATE OR REPLACE FUNCTION payment_gather_header_info(in_account_class int, in_payment_id int)
 RETURNS SETOF payment_header_item AS
 $$
 DECLARE out_payment payment_header_item;
 BEGIN
 FOR out_payment IN 
   SELECT p.id as payment_id, p.reference as payment_reference, p.payment_date,  
          c.legal_name as legal_name, am.amount as amount, em.first_name, em.last_name, p.currency, p.notes
   FROM payment p
   JOIN employee em ON (em.entity_id = p.employee_id)
   JOIN company c ON   (c.entity_id  = p.entity_id)
   JOIN (  SELECT sum(a.amount) as amount
        FROM acc_trans a
        JOIN chart c ON (a.chart_id = c.id)
        JOIN payment_links pl ON (pl.entry_id=a.entry_id)
        WHERE 
        (   ((c.link like '%AP_paid%' OR c.link like '%AP_discount%') AND in_account_class = 1)
         OR ((c.link like '%AR_paid%' OR c.link like '%AR_discount%') AND in_account_class = 2))
                 AND pl.payment_id = in_payment_id ) am ON (1=1)
   WHERE p.id = in_payment_id
 LOOP
     RETURN NEXT out_payment;
 END LOOP;

 END;
 $$ language plpgsql;
                            

COMMENT ON FUNCTION payment_gather_header_info(int,int) IS
$$ This function finds a payment based on the id and retrieves the record, 
it is usefull for printing payments :) $$;

CREATE TYPE payment_line_item AS (
  payment_id int,
  entry_id int,
  link_type int,
  trans_id int,
  invoice_number int,
  chart_id int,
  chart_accno int,
  chart_description text,
  chart_link text,
  amount int,
  trans_date date,  
  source text,
  cleared bool,
  fx_transaction bool,
  project_id int,
  memo text,
  invoice_id int,
  approved bool,
  cleared_on date,
  reconciled_on date
);
   
CREATE OR REPLACE FUNCTION payment_gather_line_info(in_account_class int, in_payment_id int)
 RETURNS SETOF payment_line_item AS
 $$
 DECLARE out_payment_line payment_line_item;
 BEGIN
   FOR out_payment_line IN 
     SELECT pl.payment_id, ac.entry_id, pl.type as link_type, ac.trans_id, a.invnumber as invoice_number,
     ac.chart_id, ch.accno as chart_accno, ch.description as chart_description, ch.link as chart_link,
     ac.amount,  ac.transdate as trans_date, ac.source, ac.cleared_on, ac.fx_transaction, ac.project_id,
     ac.memo, ac.invoice_id, ac.approved, ac.cleared_on, ac.reconciled_on
     FROM acc_trans ac
     JOIN payment_links pl ON (pl.entry_id = ac.entry_id )
     JOIN chart         ch ON (ch.id = ac.chart_id)
     LEFT JOIN (SELECT id,invnumber
                 FROM ar WHERE in_account_class = 2
                 UNION
                 SELECT id,invnumber
                 FROM ap WHERE in_account_class = 1
                ) a ON (ac.trans_id = a.id)
     WHERE pl.payment_id = in_payment_id
   LOOP
      RETURN NEXT out_payment_line;
   END LOOP;  
 END;
 $$ language plpgsql;

COMMENT ON FUNCTION payment_gather_line_info(int,int) IS
$$ This function finds a payment based on the id and retrieves all the line records, 
it is usefull for printing payments and build reports :) $$;