path: root/sql/modules/Reconciliaton.sql

CREATE TABLE reports (
    id bigserial primary key not null,
    report_id int NOT NULL,
    account text not null,
    scn text not null, -- SCN is the check #
    their_balance numeric,
    our_balance numeric,
    errorcode INT,
    user int references entity(id) not null, -- why ois this not an entity reference?
    corrections INT NOT NULL DEFAULT 0
    clear_time TIMESTAMP NOT NULL,
    insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
    ledger_id int REFERENCES acc_trans(entry_id),
    overlook boolean not null default 'f',
    approved boolean not null default 'f'
);

CREATE TABLE coa_to_account (
    chart_id int not null references chart(id),
    account text not null CHECK (account ~ '[0-9]{7}(xxx)')
);


CREATE TABLE report_corrections (
    id serial primary key not null,
    correction_id int not null default 1,
    entry references reports(id) not null,
    user references entity(id) not null, -- why is this not an entity reference?
    reason text not null,
    insert_time timestamptz not null default now()
);

-- to correct OUR wrong amount.
CREATE OR REPLACE FUNCTION reconciliation__correct_ledger (in_report_id INT, in_id int, in_new_amount NUMERIC, reason TEXT) returns INT AS $$

    DECLARE
        new_code INT;
        current_row RECORD;
        l_row acc_trans;
        in_user TEXT;
        full_reason TEXT;
    BEGIN
        select into in_user from current_user;
        
        select into current_row from reports where reports.id = in_report_id and reports.id = in_id;
        select into l_row from acc_trans where entry_id = current_row.lid;
        
        IF NOT FOUND THEN
            RAISE EXCEPTION 'No such id % in this report.', in_scn;
        END IF;
        
        IF user <> current_row.user THEN
        
            IF current_row.our_balance <> in_new_amount AND in_new_amount = current_row.their_balance THEN
                update reports pr
                set pr.corrections = reports.corrections + 1, 
                pr.new_balance = in_new_amount,
                error_code = 0
                where id = in_report_id and scn = in_scn;
                return 0;
                
                -- After that, it is required to update the general ledger.
                full_reason := "User % is filing a reconciliation correction on the general ledger, changing amount % to amount %. 
                Their reason given is: %", in_user, current_row.our_balance, in_new_amount, reason;
                perform select reconciliation__update_ledger(current_row.lid, in_new_amount, full_reason)
            ELSE IF current_row.our_balance = in_new_amount THEN
                -- This should be something, does it equal the original 
                -- balance? If so, there's no change.
                return current_row.error_code;
            END IF;
        END IF;
        
        return current_row.error_code;            
                    
    END;
$$ language 'plpgsql';

-- to correct an incorrect bank statement value.
CREATE OR REPLACE FUNCTION reconciliation__correct_bank_statement (in_report_id INT, in_id int, in_new_amount NUMERIC) returns INT AS $$

    DECLARE
        new_code INT;
        current_row RECORD;
        in_user TEXT;
    BEGIN
        select into in_user from current_user;
        select into current_row from reports where reports.id = in_id and reports.report_id = in_report_id;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'No such SCN % in this report.', in_scn;
        END IF;
        
        IF user <> current_row.user THEN
        
            IF current_row.their_balance <> in_new_amount AND in_new_amount = current_row.our_balance THEN
                update reports pr
                set pr.corrections = reports.corrections + 1, 
                pr.new_balance = in_new_amount,
                error_code = 0
                where id = in_report_id and scn = in_scn;
                return 0;
            
            ELSE IF current_row.their_balance = in_new_amount THEN
                -- This should be something, does it equal the original 
                -- balance? If so, there's no change.
                return current_row.error_code;         
            END IF;
        END IF;
        
        return current_row.error_code;            
                    
    END;
$$ language 'plpgsql';


CREATE OR REPLACE reconciliation__correct_passthrough ( in_report_id int, in_id int ) returns INT AS $$

    DECLARE
        in_user TEXT;
        pending_entry reports;
    BEGIN
        select into in_user from current_user;
        
        select into pending_entry from reports where report_id = in_report_id and id = in_id;
        
        IF NOT FOUND THEN
            -- Raise an exception.
            RAISE EXCEPTION "Cannot find entry.";
        ELSE IF pending_entry.errorcode <> 4 THEN 
            -- Only error codes of 4 may be "passed through" safely.
            RAISE EXCEPTION "Selected entry not permitted to be passed through.";
            
        ELSE
            -- Then we mark it passthroughable, and "approve" will overlook it.
            update reports set overlook = 't', errorcode = 0 where report_id = in_report_id and id = in_id;
            return 0;
        END IF;
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation__correct_bank_charge (in_report_id int, in_id int) returns INT AS $$

    DECLARE
        in_user TEXT;
        pending_entry reports;
    BEGIN
    
        IF NOT FOUND THEN
             -- Raise an exception.
             RAISE EXCEPTION "Cannot find entry with ID % in report %.", in_id, in_report_id;
         ELSE IF pending_entry.errorcode <> 2 THEN 
             -- Only error codes of 2 may be "passed through" safely.
             RAISE EXCEPTION "Attempt to retroactively add a non-bank-charge entry to the ledger.";
         
         ELSE
             -- Then we mark it passthroughable, and "approve" will overlook it.
             
             select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date, 'Bank charge');
             
             update reports set errorcode = 0 where report_id = in_report_id and id = in_id;
             return 0;
         END IF;
    END;

$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation__correct_unaccounted_charge (in_report_id int, in_id int, reason TEXT) RETURNS INT AS $$

    DECLARE
        in_user TEXT;
        pending_entry reports;
        note TEXT;
    BEGIN
    
        IF NOT FOUND THEN
             -- Raise an exception.
             RAISE EXCEPTION "Cannot find entry with ID % in report %.", in_id, in_report_id;
         ELSE IF pending_entry.errorcode <> 3 THEN 
             -- Only error codes of 3 may be "passed through" safely.
             RAISE EXCEPTION "Not an unaccounted charge; cannot be retroactively added to the ledger.";
         
         ELSE
             -- Then we mark it passthroughable, and "approve" will overlook it.
             
             note := 'Retroactive addition of an unaccounted entry, of value %. 
             Being added by user % with the following explanation: %', pending_entry.their_balance, in_user, in_reason;
             
             select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date,note);
             
             update reports set errorcode = 0 where report_id = in_report_id and id = in_id;
             return in_id;
         END IF;
    END;
$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation__report_approve (in_report_id INT) returns INT as $$
    
    -- Does some basic checks before allowing the approval to go through; 
    -- moves the approval to "reports", I guess, or some other "final" table.
    --
    -- Pending may just be a single flag in the database to mark that it is
    -- not finalized. Will need to discuss with Chris.
    
    DECLARE
        current_row RECORD;
        completed reports;
        total_errors INT;
        in_user TEXT;
    BEGIN
        
        select into in_user current_user;
        select into current_row distinct on user * from reports where report_id = in_report_id;
        IF NOT FOUND THEN
            RAISE EXCEPTION "Fatal Error: Pending report % not found", in_report_id;
        END IF;
        
        IF current_row.user = in_user THEN
            RAISE EXCEPTION "Fatal Error: User % cannot self-approve report!", in_user;
        END IF;
        
        SELECT INTO total_errors count(*) from reports where report_id = in_report_id and error_code <> 0;
        
        IF total_errors <> 0 THEN
            RAISE EXCEPTION "Fatal Error: Cannot approve while % uncorrected errors remain.", total_errors;
        END IF;
        
        -- so far, so good. Different user, and no errors remain. Therefore, we can move it to completed reports.
        --
        -- User may not be necessary - I would think it better to use the 
        -- in_user, to note who approved the report, than the user who
        -- filed it. This may require clunkier syntax..
        
        -- 
        
        update reports set approved = 't', clear_time = now() where report_id = in_report_id;
        
        return 1;        
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation__new_report_id () returns INT as $$

    SELECT nextval('pending_report_report_id_seq');

$$ language 'sql';

create or replace function reconciliation__add_entry(
    in_report_id INT, 
    in_scn INT, 
    in_amount numeric, 
    in_account INT, 
    in_user TEXT, 
    in_date TIMESTAMP
) RETURNS INT AS $$
    
    DELCARE
        la RECORD;
        errorcode INT;
        our_value NUMERIC;
        lid INT;
    BEGIN
    
        SELECT INTO la FROM acc_trans gl 
        JOIN chart c on gl.chart_id = c.id
        JOIN ap ON gl.trans_id = ap.id
        JOIN coa_to_account cta on cta.chart_id = gl.chart_id
        WHERE gl.source ~ in_scn -- does it look like it?
        and cta.account = in_account 
        and gl.amount = in_amount
        AND gl.transdate = in_date;
        
        lid := NULL;
        IF NOT FOUND THEN
            -- they have it, we don't. This is Bad, and implies either a bank
            -- charge or an unaccounted cheque.
            
            if in_scn <> '' and in_scn IS NOT NULL THEN
            
                -- It's a bank charge. Approval action will probably be 
                -- adding it as an entry to the general ledger.
                errorcode := 2; 
                our_value := 0;
            ELSE
                -- Okay, now this is bad.
                -- They have a cheque/sourced charge that we don't. 
                -- REsolution action is going to be
                errorcode := 3;
                our_value := 0;
            END IF;
            
        ELSE if la.amount <> in_amount THEN
        
            errorcode := 1;
            our_value := la.amount;
            lid := la.entry_id;
            
        ELSE
            -- it reconciles. No problem.
            
            errorcode := 0;
            our_value := la.amount;
            lid := la.entry_id;
            
        END IF;
        
        INSERT INTO reports (
                report_id,
                scn,
                their_balance,
                our_balance,
                error_code, 
                user,
                clear_time,
                ledger_id
            ) 
            VALUES (
                in_report_id,
                in_scn,
                in_amount,
                gl.amount,
                errorcode,
                in_user,
                in_date,
                lid
            );
            
        -- success, basically. This could very likely be collapsed to
        -- do the compare check here, instead of in the Perl app. Save us a DB
        -- call.
        return 1; 
        
    END;    
$$ language 'plpgsql';

create or replace function reconciliation__pending_transactions (in_month DATE) RETURNS setof acc_trans as $$
    
    DECLARE
        gl_row acc_trans;
    BEGIN
        FOR gl_row IN
            select gl.* from acc_trans gl, reports pr 
            where gl.cleared = 'f' 
            and date_trunc('month',gl.transdate) <= date_trunc('month', in_month)
            and gl.entry_id <> pr.ledger_id -- there's no entries in the reports for this
        LOOP
            RETURN NEXT gl_row;
        END LOOP;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION reconciliation__report (in_report_id INT) RETURNS setof reports as $$

    DECLARE
        row reports;
    BEGIN    
        FOR row IN select * from reports where report_id = in_report_id LOOP
        
            RETURN NEXT row;
        
        END LOOP;    
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation__get_total (in_report_id INT) returns setof reports AS $$

    DECLARE
        row reports;
    BEGIN
    
        SELECT INTO row FROM reports 
        WHERE ledger_id IS NULL 
        and report_id = in_report_id 
        AND scn = -1;
        
        IF NOT FOUND THEN -- I think this is a fairly major error condition
            RAISE EXCEPTION "No Bank Total found.";
        ELSE
            return row;
        END IF;
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation__corrections (in_report_id INT, in_id INT) returns setof report_corrections AS $$

    DECLARE
        corr report_corrections;
    BEGIN
    
        SELECT INTO corr FROM report_corrections WHERE report_id = in_report_id AND id = in_id LIMIT 1;
        IF NOT FOUND THEN
            RAISE EXCEPTION "No corrections for selected entry.";
        ELSE
            FOR corr IN select * from report_corrections WHERE report_id = in_report_id AND id = in_id LOOP
                RETURN NEXT corr;
            END LOOP;
        END IF;
    END;

$$ language 'plplsql';

CREATE OR REPLACE FUNCTION reconciliation__single_entry (in_report_id INT, in_id INT) returns setof reports AS $$

    DECLARE
        row reports;
    BEGIN
    
        SELECT INTO row FROM reports WHERE report_id = in_report_id and id = in_id LIMIT 1; 
        -- if there's more than one, that's a Bad Thing
        
        IF NOT FOUND THEN
            RAISE EXCEPTION "Could not find selected report entry";
        ELSE
            RETURN row;
        END IF;
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation__search (
    in_date_begin DATE, 
    in_date_end DATE, 
    in_account TEXT,
    in_status TEXT
) RETURNS setof reports AS $$

    DECLARE
        row reports;
        statement text;
        where_stmt text;
        v_status BOOLEAN;
        v_accum NUMERIC;
    BEGIN
        
        if in_status = "pending" then
            v_status = 'ft'::bool;
        ELSIF in_status = "approved" THEN
        
            v_status = 't'::bool;
        END IF;
        
        IF in_date_begin IS NOT NULL
            or in_date_end IS NOT NULL
            or in_account IS NOT NULL
            or v_status IS NOT NULL
        THEN
            statement = "select pr.* from reports pr ";
            statement = statement + "join acc_trans at on pr.ledger_id = at.entry_id ";
            
            IF in_account IS NOT NULL THEN
                
                statement = statement + "join chart c on at.chart_id = c.id ";
                where_stmt = "c.accno =~ " + quote_literal(in_account) + " AND ";
            END IF;
            
            IF in_date_begin IS NOT NULL THEN
                where_stmt = where_stmt + "insert_time >= " + quote_literal(in_date_begin) + " AND ";
            END IF;
            
            IF in_date_end IS NOT NULL THEN
                where_stmt = where_stmt + "insert_time <= " + quote_literal(in_date_end) + " AND ";
            END IF;
            
            IF in_status IS NOT NULL THEN
                
                if v_status == 't'::bool THEN
                    where_stmt = where_stmt + " approved = 't'::bool AND ";
                ELSIF v_status == 'f'::bool THEN
                    where_stmt = where_stmt + " approved = 'f'::bool AND ";
                END IF;
            
            END IF;
            
            FOR row in EXECUTE statement LOOP
                RETURN NEXT row;
            END LOOP;
        ELSE
        
            FOR row IN SELECT * FROM reports LOOP
                RETURN NEXT row;
            END LOOP;
        
        END IF;
    END;
$$ language 'plpgsql';

create type recon_accounts as (
    name text,
    id int
);

create or replace function reconciliation__get_accounts () returns setof recon_accounts as $$
    SELECT 
        coa.accno || ' ' || coa.description as name,
        coa.id as id
    FROM chart coa, coa_to_account cta
    WHERE cta.chart_id = coa.id;
$$ language sql;