summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/modules/Customer.sql75
-rw-r--r--sql/modules/Location.sql31
-rw-r--r--sql/modules/Settings.sql13
-rw-r--r--sql/modules/Vendor.sql8
4 files changed, 72 insertions, 55 deletions
diff --git a/sql/modules/Customer.sql b/sql/modules/Customer.sql
index 89867ded..6071bc4a 100644
--- a/sql/modules/Customer.sql
+++ b/sql/modules/Customer.sql
@@ -1,5 +1,17 @@
BEGIN;
+CREATE OR REPLACE FUNCTION entity_list_contact_class()
+RETURNS SETOF contact_class AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+ FOR out_row IN
+ SELECT * FROM contact_class ORDER BY id
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+END;
+$$ language plpgsql;
CREATE TYPE customer_search_return AS (
legal_name text,
@@ -28,25 +40,23 @@ CREATE TYPE customer_search_return AS (
-- $$ This structure will change greatly in 1.4.
-- If you want to reply on it heavily, be prepared for breakage later. $$;
-CREATE OR REPLACE FUNCTION customer_save (
- in_id int,
+CREATE OR REPLACE FUNCTION entity_credit_save (
+ in_id int, in_entity_class int,
in_discount numeric, in_taxincluded bool, in_creditlimit numeric,
in_discount_terms int,
- in_terms int, in_meta_number varchar(32), in_cc text, in_bcc text,
- in_business_id int, in_language varchar(6), in_pricegroup_id int,
+ in_terms int, in_meta_number varchar(32), in_business_id int,
+ in_language varchar(6), in_pricegroup_id int,
in_curr char, in_startdate date, in_enddate date,
-
- in_bic text, in_iban text,
-
in_notes text,
-
in_name text, in_tax_id TEXT,
in_threshold NUMERIC
) returns INT as $$
-- does not require entity_class, as entity_class is a known given to be 1
+
+ -- Maybe we should make this generic and pass through? -- CT
DECLARE
t_entity_class int;
@@ -55,20 +65,19 @@ CREATE OR REPLACE FUNCTION customer_save (
l_id int;
BEGIN
- t_entity_class := 1;
SELECT INTO v_row * FROM company WHERE id = in_id;
IF NOT FOUND THEN
-- do some inserts
- new_entity_id := nextval('entity_id_seq');
+ select nextval('entity_id_seq') into new_entity_id;
insert into entity (id, name, entity_class)
- VALUES (new_entity_id, in_name, t_entity_class);
+ VALUES (new_entity_id, in_name, in_entity_class);
- INSERT INTO company ( id, entity_id, legal_name, tax_id )
- VALUES ( in_id, new_entity_id, in_name, in_tax_id );
+ INSERT INTO company ( entity_id, legal_name, tax_id )
+ VALUES ( new_entity_id, in_name, in_tax_id );
INSERT INTO entity_credit_account (
entity_id,
@@ -78,27 +87,23 @@ CREATE OR REPLACE FUNCTION customer_save (
creditlimit,
terms,
meta_number,
- cc,
- bcc,
business_id,
language_code,
pricegroup_id,
curr,
startdate,
enddate,
- discountterms,
+ discount_terms,
threshold
)
VALUES (
new_entity_id,
- t_entity_class,
+ in_entity_class,
in_discount,
in_taxincluded,
in_creditlimit,
in_terms,
in_meta_number,
- in_cc,
- in_bcc,
in_business_id,
in_language,
in_pricegroup_id,
@@ -106,22 +111,13 @@ CREATE OR REPLACE FUNCTION customer_save (
in_startdate,
in_enddate,
in_discount_terms,
- in_threashold
+ in_threshold
);
- INSERT INTO entity_bank_account (
- entity_id,
- bic,
- iban
- )
- VALUES (
- new_entity_id,
- in_bic,
- in_iban
- );
-- entity note class
insert into entity_note (note_class, note, ref_key, vector) VALUES (
1, in_notes, new_entity_id, '');
-
+ return new_entity_id;
+
ELSIF FOUND THEN
update company set tax_id = in_tax_id where id = in_id;
@@ -131,8 +127,6 @@ CREATE OR REPLACE FUNCTION customer_save (
creditlimit = in_creditlimit,
terms = in_terms,
meta_number = in_meta_number,
- cc = in_cc,
- bcc = in_bcc,
business_id = in_business_id,
language_code = in_language,
pricegroup_id = in_pricegroup_id,
@@ -143,17 +137,13 @@ CREATE OR REPLACE FUNCTION customer_save (
discount_terms = in_discount_terms
where entity_id = v_row.entity_id;
- UPDATE entity_bank_account SET
- bic = in_bic,
- iban = in_iban
- WHERE entity_id = v_row.entity_id;
UPDATE entity_note SET
note = in_note
WHERE ref_key = v_row.entity_id;
+ return in_id;
END IF;
- return in_id;
END;
$$ language 'plpgsql';
@@ -161,15 +151,14 @@ $$ language 'plpgsql';
CREATE OR REPLACE FUNCTION customer_location_save (
in_company_id int,
in_location_class int, in_line_one text, in_line_two text,
- in_city_province TEXT, in_mail_code text, in_country_code int,
- in_created date
+ in_line_three text,
+ in_city TEXT, in_state text, in_mail_code text, in_country_code int
) returns int AS $$
BEGIN
return _entity_location_save(
in_company_id,
- in_location_class, in_line_one, in_line_two,
- in_city_province , in_mail_code, in_country_code,
- in_created);
+ in_location_class, in_line_one, in_line_two, in_line_three,
+ in_city, in_state, in_mail_code, in_country_code);
END;
$$ language 'plpgsql';
diff --git a/sql/modules/Location.sql b/sql/modules/Location.sql
index d4f8f9f5..51fb1d02 100644
--- a/sql/modules/Location.sql
+++ b/sql/modules/Location.sql
@@ -1,4 +1,31 @@
-- VERSION 1.3.0
+
+CREATE OR REPLACE FUNCTION location_list_class()
+RETURNS SETOF location_class AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+ FOR out_row IN
+ SELECT * FROM location_class ORDER BY id
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION location_list_country()
+RETURNS SETOF country AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+ FOR out_row IN
+ SELECT * FROM country ORDER BY id
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+END;
+$$ language plpgsql;
+
CREATE OR REPLACE FUNCTION location_save
(in_address1 text, in_address2 text, in_address3 text,
in_city text, in_state text, in_zipcode text, in_country text)
@@ -16,13 +43,13 @@ BEGIN
city = in_city AND
state = in_state AND
zipcode = in_zipcode AND
- country = in_country
+ country_id = in_country
LIMIT 1;
IF FOUND THEN
return location_row.id;
END IF;
INSERT INTO location
- (companyname, address1, address2, city, state, zipcode, country)
+ (companyname, address1, address2, city, state, zipcode, country_id)
VALUES
(in_companyname, in_address1, in_address2, in_city, in_state,
in_zipcode, in_country);
diff --git a/sql/modules/Settings.sql b/sql/modules/Settings.sql
index e86450c3..5b8d2b28 100644
--- a/sql/modules/Settings.sql
+++ b/sql/modules/Settings.sql
@@ -7,6 +7,7 @@ BEGIN
UPDATE defaults SET value = in_value WHERE setting_key = in_key;
RETURN;
END;
+$$ language plpgsql;
CREATE OR REPLACE FUNCTION setting_get (in_key varchar) RETURNS varchar AS
$$
@@ -23,7 +24,7 @@ RETURNS SETOF defaults AS
$$
DECLARE
account defaults%ROWTYPE;
-BEGIN;
+BEGIN
FOR account IN
SELECT * FROM defaults
WHERE setting_key like '%accno_id'
@@ -33,13 +34,13 @@ BEGIN;
END;
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION setting_incriment (in_key varchar) returns varchar
+CREATE OR REPLACE FUNCTION setting_increment (in_key varchar) returns varchar
AS
$$
DECLARE
base_value VARCHAR;
raw_value VARCHAR;
- incriment INTEGER;
+ increment INTEGER;
inc_length INTEGER;
new_value VARCHAR;
BEGIN
@@ -51,10 +52,10 @@ BEGIN
INTO base_value;
IF base_value like '0%' THEN
- incriment := base_value::integer + 1;
- SELECT char_length(incriment::text) INTO inc_length;
+ increment := base_value::integer + 1;
+ SELECT char_length(increment::text) INTO inc_length;
- SELECT overlay(base_value placing incriment::varchar
+ SELECT overlay(base_value placing increment::varchar
from (select char_length(base_value)
- inc_length + 1) for inc_length)
INTO new_value;
diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql
index e18f88a5..d2f6c3c3 100644
--- a/sql/modules/Vendor.sql
+++ b/sql/modules/Vendor.sql
@@ -171,9 +171,9 @@ $$ language 'plpgsql';
create or replace function _entity_location_save(
in_company_id int, in_location_id int,
- in_location_class int, in_line_one text, in_line_two text,
- in_city TEXT, in_state TEXT, in_mail_code text, in_country_code int,
- in_created date
+ in_location_class int, in_line_one text, in_line_two text,
+ in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
+ in_country_code int
) returns int AS $$
DECLARE
@@ -185,7 +185,7 @@ create or replace function _entity_location_save(
AND location_class = in_location_class
AND location_id = in_location_id;
- SELECT location_save(in_line_one, in_line_two, NULL, in_city,
+ SELECT location_save(in_line_one, in_line_two, in_line_three, in_city,
in_state, in_mail_code, in_mail_code, in_country_code)
INTO l_id;