summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/Pg-database.sql425
-rw-r--r--sql/modules/Payment.sql285
2 files changed, 261 insertions, 449 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 45cfe827..16615c3f 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -1,426 +1,4 @@
-begin;
-CREATE SEQUENCE id;
--- As of 1.3 there is no central db anymore. --CT
-
-CREATE TABLE chart (
- id serial PRIMARY KEY,
- accno text NOT NULL,
- description text,
- charttype char(1) DEFAULT 'A',
- category char(1),
- link text,
- gifi_accno text,
- contra bool DEFAULT 'f'
-);
---
--- pricegroup added here due to references
-CREATE TABLE pricegroup (
- id serial PRIMARY KEY,
- pricegroup text
-);
-
--- BEGIN new entity management
-CREATE TABLE entity_class (
- id serial primary key,
- class text check (class ~ '[[:alnum:]_]') NOT NULL,
- active boolean not null default TRUE);
-
-COMMENT ON TABLE entity_class IS $$ Defines the class type such as vendor, customer, contact, employee $$;
-COMMENT ON COLUMN entity_class.id IS $$ The first 7 values are reserved and permanent $$;
-
-CREATE index entity_class_idx ON entity_class(lower(class));
-
-CREATE TABLE entity (
- id serial UNIQUE,
- name text check (name ~ '[[:alnum:]_]'),
- entity_class integer references entity_class(id) not null ,
- created date not null default current_date,
- control_code text,
- PRIMARY KEY(control_code, entity_class));
-
-COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$;
-COMMENT ON COLUMN entity.name IS $$ This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com $$;
-
-
-ALTER TABLE entity ADD FOREIGN KEY (entity_class) REFERENCES entity_class(id);
-
-INSERT INTO entity_class (id,class) VALUES (1,'Vendor');
-INSERT INTO entity_class (id,class) VALUES (2,'Customer');
-INSERT INTO entity_class (id,class) VALUES (3,'Employee');
-INSERT INTO entity_class (id,class) VALUES (4,'Contact');
-INSERT INTO entity_class (id,class) VALUES (5,'Lead');
-INSERT INTO entity_class (id,class) VALUES (6,'Referral');
-
-SELECT setval('entity_class_id_seq',7);
-
-CREATE TABLE entity_class_to_entity (
- entity_class_id integer not null references entity_class(id) ON DELETE CASCADE,
- entity_id integer not null references entity(id) ON DELETE CASCADE,
- PRIMARY KEY(entity_class_id,entity_id)
- );
-
-COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
-
--- USERS stuff --
-CREATE TABLE users (
- id serial UNIQUE,
- username varchar(30) primary key,
- entity_id int not null references entity(id) on delete cascade
-);
-
-COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$;
-
--- Session tracking table
-
-
-CREATE TABLE session(
-session_id serial PRIMARY KEY,
-token VARCHAR(32) CHECK(length(token) = 32),
-last_used TIMESTAMP default now(),
-ttl int default 3600 not null,
-users_id INTEGER NOT NULL references users(id),
-transaction_id INTEGER NOT NULL
-);
-
-CREATE TABLE open_forms (
-id SERIAL PRIMARY KEY,
-session_id int REFERENCES session(session_id) ON DELETE CASCADE
-);
-
---
-CREATE TABLE transactions (
- id int PRIMARY KEY,
- table_name text,
- locked_by int references "session" (session_id) ON DELETE SET NULL,
- approved_by int references entity (id),
- approved_at timestamp
-);
-
-COMMENT on TABLE transactions IS
-$$ This table tracks basic transactions across AR, AP, and GL related tables.
-It provies a referential integrity enforcement mechanism for the financial data
-and also some common features such as discretionary (and pessimistic) locking
-for long batch workflows. $$;
-
-CREATE OR REPLACE FUNCTION lock_record (int, int) returns bool as
-$$
-declare
- locked int;
-begin
- SELECT locked_by into locked from transactions where id = $1;
- IF NOT FOUND THEN
- RETURN FALSE;
- ELSEIF locked is not null AND locked <> $2 THEN
- RETURN FALSE;
- END IF;
- UPDATE transactions set locked_by = $2 where id = $1;
- RETURN TRUE;
-end;
-$$ language plpgsql;
-
-COMMENT ON column transactions.locked_by IS
-$$ This should only be used in pessimistic locking measures as required by large
-batch work flows. $$;
-
--- LOCATION AND COUNTRY
-CREATE TABLE country (
- id serial PRIMARY KEY,
- name text check (name ~ '[[:alnum:]_]') NOT NULL,
- short_name text check (short_name ~ '[[:alnum:]_]') NOT NULL,
- itu text);
-
-COMMENT ON COLUMN country.itu IS $$ The ITU Telecommunication Standardization Sector code for calling internationally. For example, the US is 1, Great Britain is 44 $$;
-
-CREATE UNIQUE INDEX country_name_idx on country(lower(name));
-
-CREATE TABLE location_class (
- id serial UNIQUE,
- class text check (class ~ '[[:alnum:]_]') not null,
- authoritative boolean not null,
- PRIMARY KEY (class,authoritative));
-
-CREATE UNIQUE INDEX lower_class_unique ON location_class(lower(class));
-
-INSERT INTO location_class(id,class,authoritative) VALUES ('1','Billing',TRUE);
-INSERT INTO location_class(id,class,authoritative) VALUES ('2','Sales',TRUE);
-INSERT INTO location_class(id,class,authoritative) VALUES ('3','Shipping',TRUE);
-
-SELECT SETVAL('location_class_id_seq',4);
-
-CREATE TABLE location (
- id serial PRIMARY KEY,
- line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL,
- line_two text,
- line_three text,
- city text check (city ~ '[[:alnum:]_]') NOT NULL,
- state text check(state ~ '[[:alnum:]_]'),
- country_id integer not null REFERENCES country(id),
- mail_code text not null check (mail_code ~ '[[:alnum:]_]'),
- created date not null default now(),
- inactive_date timestamp default null,
- active boolean not null default TRUE
-);
-
-CREATE TABLE company (
- id serial UNIQUE,
- entity_id integer not null references entity(id),
- legal_name text check (legal_name ~ '[[:alnum:]_]'),
- tax_id text,
- created date default current_date not null,
- PRIMARY KEY (entity_id,legal_name));
-
-COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$;
-
-CREATE TABLE company_to_location (
- location_id integer references location(id) not null,
- location_class integer not null references location_class(id),
- company_id integer not null references company(id) ON DELETE CASCADE,
- PRIMARY KEY(location_id,company_id, location_class));
-
-COMMENT ON TABLE company_to_location IS
-$$ This table is used for locations generic to companies. For contract-bound
-addresses, use eca_to_location instead $$;
-
-CREATE TABLE salutation (
- id serial unique,
- salutation text primary key);
-
-INSERT INTO salutation (id,salutation) VALUES ('1','Dr.');
-INSERT INTO salutation (id,salutation) VALUES ('2','Miss.');
-INSERT INTO salutation (id,salutation) VALUES ('3','Mr.');
-INSERT INTO salutation (id,salutation) VALUES ('4','Mrs.');
-INSERT INTO salutation (id,salutation) VALUES ('5','Ms.');
-INSERT INTO salutation (id,salutation) VALUES ('6','Sir.');
-
-SELECT SETVAL('salutation_id_seq',7);
-
-CREATE TABLE person (
- id serial PRIMARY KEY,
- entity_id integer references entity(id) not null,
- salutation_id integer references salutation(id),
- first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL,
- middle_name text,
- last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL,
- created date not null default current_date
- );
-
-COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$;
-
-create table entity_employee (
-
- person_id integer references person(id) not null,
- entity_id integer references entity(id) not null unique,
- startdate date not null default current_date,
- enddate date,
- role varchar(20),
- ssn text,
- sales bool default 'f',
- manager_id integer references entity(id),
- employeenumber varchar(32),
- dob date,
- PRIMARY KEY (person_id, entity_id)
-);
-
-CREATE TABLE person_to_location (
- location_id integer not null references location(id),
- location_class integer not null references location_class(id),
- person_id integer not null references person(id) ON DELETE CASCADE,
- PRIMARY KEY (location_id,person_id));
-
-CREATE TABLE person_to_company (
- location_id integer references location(id) not null,
- person_id integer not null references person(id) ON DELETE CASCADE,
- company_id integer not null references company(id) ON DELETE CASCADE,
- PRIMARY KEY (location_id,person_id));
-
-CREATE TABLE entity_other_name (
- entity_id integer not null references entity(id) ON DELETE CASCADE,
- other_name text check (other_name ~ '[[:alnum:]_]'),
- PRIMARY KEY (other_name, entity_id));
-
-COMMENT ON TABLE entity_other_name IS $$ Similar to company_other_name, a person may be jd, Joshua Drake, linuxpoet... all are the same person. $$;
-
-CREATE TABLE person_to_entity (
- person_id integer not null references person(id) ON DELETE CASCADE,
- entity_id integer not null check (entity_id != person_id) references entity(id) ON DELETE CASCADE,
- related_how text,
- created date not null default current_date,
- PRIMARY KEY (person_id,entity_id));
-
-CREATE TABLE company_to_entity (
- company_id integer not null references company(id) ON DELETE CASCADE,
- entity_id integer check (company_id != entity_id) not null references entity(id) ON DELETE CASCADE,
- related_how text,
- created date not null default current_date,
- PRIMARY KEY (company_id,entity_id));
-
-CREATE TABLE contact_class (
- id serial UNIQUE,
- class text check (class ~ '[[:alnum:]_]') NOT NULL,
- PRIMARY KEY (class));
-
-CREATE UNIQUE INDEX contact_class_class_idx ON contact_class(lower(class));
-
-INSERT INTO contact_class (id,class) values (1,'Primary Phone');
-INSERT INTO contact_class (id,class) values (2,'Secondary Phone');
-INSERT INTO contact_class (id,class) values (3,'Cell Phone');
-INSERT INTO contact_class (id,class) values (4,'AIM');
-INSERT INTO contact_class (id,class) values (5,'Yahoo');
-INSERT INTO contact_class (id,class) values (6,'Gtalk');
-INSERT INTO contact_class (id,class) values (7,'MSN');
-INSERT INTO contact_class (id,class) values (8,'IRC');
-INSERT INTO contact_class (id,class) values (9,'Fax');
-INSERT INTO contact_class (id,class) values (10,'Generic Jabber');
-INSERT INTO contact_class (id,class) values (11,'Home Phone');
-INSERT INTO contact_class (id,class) values (12,'Email');
-
-SELECT SETVAL('contact_class_id_seq',12);
-
-CREATE TABLE person_to_contact (
- person_id integer not null references person(id) ON DELETE CASCADE,
- contact_class_id integer references contact_class(id) not null,
- contact text check(contact ~ '[[:alnum:]_]') not null,
- PRIMARY KEY (person_id,contact_class_id,contact));
-
-COMMENT ON TABLE person_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single individual $$;
-
-CREATE TABLE company_to_contact (
- company_id integer not null references company(id) ON DELETE CASCADE,
- contact_class_id integer references contact_class(id) not null,
- contact text check(contact ~ '[[:alnum:]_]') not null,
- description text,
- PRIMARY KEY (company_id, contact_class_id, contact));
-
-COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$;
-
-CREATE TABLE entity_bank_account (
- id serial not null,
- entity_id int not null references entity(id) ON DELETE CASCADE,
- bic varchar,
- iban varchar,
- UNIQUE (id),
- PRIMARY KEY (entity_id, bic, iban)
-);
-
-CREATE TABLE entity_credit_account (
- id serial not null unique,
- entity_id int not null references entity(id) ON DELETE CASCADE,
- entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
- discount numeric,
- description text,
- discount_terms int default 0,
- discount_account_id int references chart(id),
- taxincluded bool default 'f',
- creditlimit NUMERIC default 0,
- terms int2 default 0,
- meta_number varchar(32),
- cc text,
- bcc text,
- business_id int,
- language_code varchar(6),
- pricegroup_id int references pricegroup(id),
- curr char(3),
- startdate date DEFAULT CURRENT_DATE,
- enddate date,
- threshold numeric default 0,
- employee_id int references entity_employee(entity_id),
- primary_contact int references person(id),
- ar_ap_account_id int references chart(id),
- cash_account_id int references chart(id),
- bank_account int references entity_bank_account(id),
- PRIMARY KEY(entity_id, meta_number, entity_class)
-);
-
-CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u
-ON entity_credit_account(meta_number)
-WHERE entity_class = 2;
-
-COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
-$$This index is used to ensure that AR accounts are not reused.$$;
-
-CREATE TABLE eca_to_contact (
- credit_id integer not null references entity_credit_account(id)
- ON DELETE CASCADE,
- contact_class_id integer references contact_class(id) not null,
- contact text check(contact ~ '[[:alnum:]_]') not null,
- description text,
- PRIMARY KEY (credit_id, contact_class_id, contact));
-
-COMMENT ON TABLE eca_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single vendor or customer account. For generic
-contacts, use company_to_contact or person_to_contact instead.$$;
-
-CREATE TABLE eca_to_location (
- location_id integer references location(id) not null,
- location_class integer not null references location_class(id),
- credit_id integer not null references entity_credit_account(id)
- ON DELETE CASCADE,
- PRIMARY KEY(location_id,credit_id));
-
-CREATE UNIQUE INDEX eca_to_location_billing_u ON eca_to_location(credit_id)
- WHERE location_class = 1;
-
-COMMENT ON TABLE eca_to_location IS
-$$ This table is used for locations bound to contracts. For generic contact
-addresses, use company_to_location instead $$;
-
--- Begin rocking notes interface
--- Begin rocking notes interface
-CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'));
-INSERT INTO note_class(id,class) VALUES (1,'Entity');
-INSERT INTO note_class(id,class) VALUES (2,'Invoice');
-INSERT INTO note_class(id,class) VALUES (3,'Entity Credit Account');
-CREATE UNIQUE INDEX note_class_idx ON note_class(lower(class));
-
-CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id),
- note text not null, vector tsvector not null,
- created timestamp not null default now(),
- created_by text DEFAULT SESSION_USER,
- ref_key integer not null);
-
-CREATE TABLE entity_note(entity_id int references entity(id)) INHERITS (note);
-ALTER TABLE entity_note ADD CHECK (note_class = 1);
-ALTER TABLE entity_note ADD FOREIGN KEY (ref_key) REFERENCES entity(id) ON DELETE CASCADE;
-CREATE INDEX entity_note_id_idx ON entity_note(id);
-CREATE UNIQUE INDEX entity_note_class_idx ON note_class(lower(class));
-CREATE INDEX entity_note_vectors_idx ON entity_note USING gist(vector);
-CREATE TABLE invoice_note() INHERITS (note);
-CREATE INDEX invoice_note_id_idx ON invoice_note(id);
-CREATE UNIQUE INDEX invoice_note_class_idx ON note_class(lower(class));
-CREATE INDEX invoice_note_vectors_idx ON invoice_note USING gist(vector);
-
-CREATE TABLE eca_note()
- INHERITS (note);
-ALTER TABLE eca_note ADD CHECK (note_class = 3);
-ALTER TABLE eca_note ADD FOREIGN KEY (ref_key)
- REFERENCES entity_credit_account(id)
- ON DELETE CASCADE;
-
--- END entity
-
---
-CREATE TABLE makemodel (
- parts_id int PRIMARY KEY,
- make text,
- model text
-);
---
-CREATE TABLE gl (
- id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
- reference text,
- description text,
- transdate date DEFAULT current_date,
- person_id integer references person(id),
- notes text,
- approved bool default true,
- department_id int default 0
-);
---
-CREATE TABLE gifi (
- accno text PRIMARY KEY,
- description text
-);
---
-CREATE TABLE defaults (
+defaults (
setting_key text primary key,
value text
);
@@ -451,6 +29,7 @@ rcptnumber|1
paynumber|1
separate_duties|1
entity_control|A-00001
+batch_cc|B-11111
\.
COMMENT ON TABLE defaults IS $$
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 6e7ddc4d..175cac40 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -89,8 +89,12 @@ CREATE TYPE payment_invoice AS (
invnumber text,
invoice_date date,
amount numeric,
+ amount_fx numeric,
discount numeric,
- due numeric
+ discount_fx numeric,
+ due numeric,
+ due_fx numeric,
+ exchangerate numeric
);
CREATE OR REPLACE FUNCTION payment_get_open_invoices
@@ -109,10 +113,51 @@ 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, ac.due
+ 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
@@ -131,6 +176,7 @@ BEGIN
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)
@@ -151,7 +197,7 @@ BEGIN
AND (a.department_id = in_department_id
OR in_department_id IS NULL)
AND due <> 0
- GROUP BY a.invnumber, a.transdate, a.amount, discount, ac.due, a.id, c.discount_terms
+ 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;
@@ -506,7 +552,7 @@ CREATE TABLE payment (
payment_class integer NOT NULL,
payment_date date default current_date,
closed bool default FALSE,
- entity_id integer references entity(id),
+ entity_credit_id integer references entity_credit_account(id),
employee_id integer references entity_employee(entity_id),
currency char(3),
notes text,
@@ -540,11 +586,10 @@ COMMENT ON TABLE payment_links IS $$
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_id int,
+ in_entity_credit_id int,
in_curr char(3),
in_notes text,
in_department_id int,
@@ -553,6 +598,7 @@ CREATE OR REPLACE FUNCTION payment_post
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[],
@@ -570,21 +616,30 @@ 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;
--
- -- SECOND WE HAVE TO INSERT THE PAYMENT, USING THE GL INFORMATION
+ -- 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_id)
+ 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_id);
+ 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...
@@ -597,39 +652,85 @@ BEGIN
array_upper(in_cash_account_id, 1)
LOOP
INSERT INTO acc_trans (chart_id, amount,
- trans_id, transdate, approved, source)
+ trans_id, transdate, approved, source, memo)
VALUES (in_cash_account_id[out_count],
- CASE WHEN in_account_class = 1 THEN in_amount[out_count]
- ELSE in_amount[out_count]* - 1
+ 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_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
+ 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' );
- INSERT INTO acc_trans (chart_id, amount,
- trans_id, transdate, approved, source)
+ -- 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] * -1
- ELSE in_amount[out_count]
+ 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_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;
+ END LOOP;
--
-- WE NEED TO HANDLE THE OVERPAYMENTS NOW
--
@@ -637,6 +738,7 @@ BEGIN
-- 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)
@@ -658,13 +760,13 @@ BEGIN
array_upper(in_op_cash_account_id, 1)
LOOP
INSERT INTO acc_trans (chart_id, amount,
- trans_id, transdate, approved, source)
+ 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_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;
@@ -685,7 +787,7 @@ BEGIN
VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
END LOOP;
END IF;
- return 0;
+ return var_payment_id;
END;
$$ LANGUAGE PLPGSQL;
-- I HAVE TO MAKE A COMMENT ON THIS FUNCTION
@@ -752,8 +854,13 @@ 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
@@ -953,3 +1060,129 @@ BEGIN
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 :) $$;
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-25 09:05:25 +0000