Re: Still Cannot Log In

[..hidden..]

Hi,
this is probably relevant (?).  Just out of interest I ran Setup again, and it prompted to upgrade again (1.2 to 1.3) The postgres log now has this in it (somewhat truncated, it goes on to list a who ruck 'permission' errors. I guess it's not working too well!

 Cheers
Richard

2012-10-22 15:01:37 BST ERROR:  type "draft_search_result" already exists
2012-10-22 15:01:37 BST STATEMENT:  CREATE TYPE draft_search_result AS (
        id int,
        transdate date,
        reference text,
        description text,
        amount numeric
    );
2012-10-22 15:01:37 BST ERROR:  must be owner of function draft__search
2012-10-22 15:01:37 BST STATEMENT:  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,
                    (SELECT name FROM eca__get_entity(entity_credit_account)),
                    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 and charttype = 'A')
               LEFT JOIN voucher v ON (v.trans_id = trans.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 v.id IS NULL
            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)))
            ORDER BY trans.reference
        LOOP
            RETURN NEXT out_row;
        END LOOP;
    END;
    $$ language plpgsql;
2012-10-22 15:01:37 BST ERROR:  must be owner of function draft__search
2012-10-22 15:01:37 BST STATEMENT:  COMMENT ON 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)
    IS $$ Searches for drafts.  in_type may be any of 'ar', 'ap', or 'gl'.$$;
2012-10-22 15:01:37 BST ERROR:  must be owner of function draft_approve
2012-10-22 15:01:37 BST STATEMENT:  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;
2012-10-22 15:01:37 BST ERROR:  must be owner of function draft_approve
2012-10-22 15:01:37 BST STATEMENT:  COMMENT ON FUNCTION draft_approve(in_id int) IS
    $$ Posts draft to the books.  in_id is the id from the ar, ap, or gl table.$$;
2012-10-22 15:01:37 BST ERROR:  must be owner of function draft_delete
2012-10-22 15:01:37 BST STATEMENT:  CREATE OR REPLACE FUNCTION draft_delete(in_id int) returns bool as
    $$
    declare
        t_table text;
    begin
        DELETE FROM ac_tax_form
        WHERE entry_id IN
            (SELECT entry_id FROM acc_trans WHERE trans_id = in_id);
   
            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;
2012-10-22 15:01:37 BST ERROR:  must be owner of function draft_approve
2012-10-22 15:01:37 BST STATEMENT:  COMMENT ON FUNCTION draft_approve(in_id int) is
    $$ Deletes the draft from the book.  Only will delete unapproved transactions.
    Otherwise an exception is raised and the transaction terminated.$$;
2012-10-22 15:01:37 BST ERROR:  type "trial_balance_line" already exists
2012-10-22 15:01:37 BST STATEMENT:  CREATE TYPE trial_balance_line AS (
        chart_id int,
        accno text,
        description text,
        beginning_balance numeric,
        credits numeric,
        debits numeric,
        ending_balance numeric
    );
2012-10-22 15:01:37 BST ERROR:  must be owner of function report_trial_balance
2012-10-22 15:01:37 BST STATEMENT:  CREATE OR REPLACE FUNCTION report_trial_balance
    (in_datefrom date, in_dateto date, in_department_id int, in_project_id int,
    in_gifi bool)
    RETURNS setof trial_balance_line
    AS $$
    DECLARE out_row trial_balance_line;
    BEGIN
        IF in_department_id IS NULL THEN
            FOR out_row IN
                SELECT c.id, c.accno, c.description,
                    SUM(CASE WHEN ac.transdate < in_datefrom
                                  AND c.category IN ('I', 'L', 'Q')
                        THEN ac.amount
                        ELSE ac.amount * -1
                        END),
                        SUM(CASE WHEN ac.transdate >= in_date_from
                                  AND ac.amount > 0
                            THEN ac.amount
                            ELSE 0 END),
                        SUM(CASE WHEN ac.transdate >= in_date_from
                                  AND ac.amount < 0
                            THEN ac.amount
                            ELSE 0 END) * -1,
                    SUM(CASE WHEN ac.transdate >= in_date_from
                        AND c.charttype IN ('I')
                        THEN ac.amount
                        WHEN ac.transdate >= in_date_from
                                  AND c.category IN ('I', 'L', 'Q')
                        THEN ac.amount
                        ELSE ac.amount * -1
                        END)
                    FROM acc_trans ac
                    JOIN (select id, approved FROM ap
                        UNION ALL
                        select id, approved FROM gl
                        UNION ALL
                        select id, approved FROM ar) g
                        ON (g.id = ac.trans_id)
                    JOIN chart c ON (c.id = ac.chart_id)
                    WHERE ac.transdate <= in_date_to
                        AND ac.approved AND g.approved
                        AND (in_project_id IS NULL
                            OR in_project_id = ac.project_id)
                    GROUP BY c.id, c.accno, c.description
                    ORDER BY c.accno
                   
            LOOP
                RETURN NEXT out_row;
            END LOOP;
        ELSE
            FOR out_row IN
                SELECT 1
            LOOP
                RETURN NEXT out_row;
            END LOOP;
        END IF;
    END;
    $$ language plpgsql;
2012-10-22 15:01:37 BST ERROR:  must be owner of function report_trial_balance
2012-10-22 15:01:37 BST STATEMENT:  COMMENT ON FUNCTION report_trial_balance
    (in_datefrom date, in_dateto date, in_department_id int, in_project_id int,
    in_gifi bool) IS
    $$ This is a simple routine to generate trial balances for the full
    company, for a project, or for a department.$$;
2012-10-22 15:01:37 BST ERROR:  must be owner of function chart_list_all
2012-10-22 15:01:37 BST STATEMENT:  CREATE OR REPLACE FUNCTION chart_list_all()
    RETURNS SETOF chart AS
    $$
    DECLARE out_row chart%ROWTYPE;
    BEGIN
        FOR out_row IN
            SELECT * FROM chart ORDER BY accno
        LOOP
            RETURN next out_row;
        END LOOP;
    END;
    $$ LANGUAGE PLPGSQL;
2012-10-22 15:01:37 BST ERROR:  must be owner of function chart_list_all
2012-10-22 15:01:37 BST STATEMENT:  COMMENT ON FUNCTION chart_list_all() IS
    $$ Generates a list of chart view entries.$$;
2012-10-22 15:01:37 BST ERROR:  must be owner of function chart_get_ar_ap
2012-10-22 15:01:37 BST STATEMENT:  CREATE OR REPLACE FUNCTION chart_get_ar_ap(in_account_class int)
    RETURNS SETOF chart AS
    $$
    DECLARE out_row chart%ROWTYPE;
    BEGIN
        IF in_account_class NOT IN (1, 2) THEN
            RAISE EXCEPTION 'Bad Account Type';
        END IF;
           FOR out_row IN
                   SELECT * FROM chart
                   WHERE link = CASE WHEN in_account_class = 1 THEN 'AP'
                                   WHEN in_account_class = 2 THEN 'AR'
                                   END
                   ORDER BY accno
           LOOP
                   RETURN NEXT out_row;
           END LOOP;
    END;
    $$ LANGUAGE PLPGSQL;
2012-10-22 15:01:37 BST ERROR:  must be owner of function chart_get_ar_ap
2012-10-22 15:01:37 BST STATEMENT:  COMMENT ON FUNCTION chart_get_ar_ap(in_account_class int) IS
    $$ This function returns the cash account acording with in_account_class which
    must be 1 or 2.
   
    If in_account_class is 1 then it returns a list of AP accounts, and if
    in_account_class is 2, then a list of AR accounts.$$;
2012-10-22 15:01:37 BST ERROR:  must be owner of function chart_list_search
2012-10-22 15:01:37 BST STATEMENT:  CREATE OR REPLACE FUNCTION chart_list_search(in_search text, in_link_desc text)
    RETURNS SETOF account AS
    $$
    DECLARE out_row account%ROWTYPE;
    BEGIN
        FOR out_row IN
            SELECT * FROM account
                     WHERE (accno ~* ('^'||in_search)
                           OR description ~* ('^'||in_search))
                           AND (in_link_desc IS NULL
                               or id in
                              (select account_id from account_link
                                where description = in_link_desc))
                  ORDER BY accno
        LOOP
            RETURN next out_row;
        END LOOP;
    END;$$
    LANGUAGE 'plpgsql';
2012-10-22 15:01:37 BST ERROR:  must be owner of function chart_list_search
2012-10-22 15:01:37 BST STATEMENT:  COMMENT ON FUNCTION chart_list_search(in_search text, in_link_desc text) IS
    $$ This returns a list of account entries where the description or account
    number begins with in_search.