Addition of Vendor and Customer-specific save, get, and search functions in Pgsql stored procedures.

Cleanup and modification of Employee.sql, to fit the new Entity framework, as well as the modifications to support trigram searching.
Addition of customer.pl and vendor.pl scripts, to support new perl framework code, as well as .pm modules for the same.

Some cleanup of Pg-database.sql.
addition of note_class = 1 check on entity_note, removal of check id = 1.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1454 4979c152-3d1c-0410-bac9-87ea11338e46

4 files changed, 667 insertions, 75 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index b0d63331..8011a9b8 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -188,7 +188,7 @@ CREATE TABLE location (
   created date not null,
   inactive_date timestamp default null,
   active boolean not null default TRUE
-  );  
+);
 CREATE INDEX location_unique_class_idx ON location (id,location_class);
   
 CREATE TABLE company (
@@ -313,7 +313,7 @@ CREATE TABLE note (id serial primary key, note_class integer not null references
                    ref_key integer not null);
 
 CREATE TABLE entity_note() INHERITS (note);
-ALTER TABLE entity_note ADD CHECK (id = 1);
+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));
@@ -448,7 +448,7 @@ CREATE TABLE entity_credit_account (
   bcc text,
   business_id int,
   language_code varchar(6),
-  pricegroup_id int,
+  pricegroup_id int references pricegroup(id),
   curr char(3),
   startdate date DEFAULT CURRENT_DATE,
   enddate date,
@@ -462,18 +462,13 @@ CREATE TABLE entity_bank_account (
     bic varchar,
     iban varchar,
     UNIQUE (id),
-    PRIMARY KEY (bic, iban)
-);
-
-CREATE TABLE entity_invoice_notes (
-    id serial not null,
-    entity_id int not null references entity(id),
-    note text,
-    unique (id)    
+    PRIMARY KEY (entity_id, bic, iban)
 );
 
 CREATE VIEW customer AS 
-    SELECT emd.entity_id, 
+    SELECT 
+        c.id,
+        emd.entity_id, 
         emd.entity_class, 
         emd.discount,
         emd.taxincluded,
@@ -490,15 +485,17 @@ CREATE VIEW customer AS
         emd.enddate,
         eba.bic, 
         eba.iban, 
-        ein.note as 
-        invoice_notes 
+        ein.note as invoice_notes 
     FROM entity_credit_account emd 
     join entity_bank_account eba on emd.entity_id = eba.entity_id
-    join entity_invoice_notes ein on ein.entity_id = emd.entity_id
+    join entity_note ein on ein.ref_key = emd.entity_id
+    join company c on c.entity_id = emd.entity_id
     where emd.entity_class = 2;
     
 CREATE VIEW vendor AS 
-    SELECT emd.entity_id, 
+    SELECT 
+        c.id, 
+        emd.entity_id, 
         emd.entity_class, 
         emd.discount,
         emd.taxincluded,
@@ -519,7 +516,8 @@ CREATE VIEW vendor AS
         invoice_notes 
     FROM entity_credit_account emd 
     join entity_bank_account eba on emd.entity_id = eba.entity_id
-    join entity_invoice_notes ein on ein.entity_id = emd.entity_id
+    join entity_note ein on ein.ref_key = emd.entity_id
+    join company c on c.entity_id = emd.entity_id
     where emd.entity_class = 1;
 
 COMMENT ON TABLE entity_credit_account IS $$ This is a metadata table for ALL entities in LSMB; it deprecates the use of customer and vendor specific tables (which were nearly identical and largely redundant), and replaces it with a single point of metadata. $$;
diff --git a/sql/modules/Customer.sql b/sql/modules/Customer.sql
new file mode 100644
index 00000000..f59c1ae1
--- /dev/null
+++ b/sql/modules/Customer.sql
@@ -0,0 +1,212 @@
+BEGIN;
+
+CREATE TYPE customer_search_return AS (
+        legal_name text,
+        id int,
+        entity_id int,
+        entity_class int,
+        discount numeric,
+        taxincluded bool,
+        creditlimit numeric,
+        terms int2,
+        customernumber int,
+        cc text,
+        bcc text,
+        business_id int,
+        language_code text,
+        pricegroup_id int,
+        curr char,
+        startdate date,
+        enddate date,
+        bic varchar, 
+        iban varchar, 
+        note text
+);
+
+CREATE OR REPLACE FUNCTION customer_save (
+    in_id int,
+    
+    in_discount numeric, in_taxincluded bool, in_creditlimit numeric, 
+    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_curr char, in_startdate date, in_enddate date, 
+    
+    in_bic text, in_iban text, 
+    
+    in_notes text, 
+    
+    in_name text, in_tax_id TEXT
+    
+) returns INT as $$
+    
+    -- does not require entity_class, as entity_class is a known given to be 1
+    
+    DECLARE
+        t_entity_class int;
+        new_entity_id int;
+        v_row company;
+        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');
+            
+            insert into entity (id, name, entity_class) 
+                VALUES (new_entity_id, in_name, t_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 entity_credit_account (
+                entity_id,
+                entity_class,
+                discount, 
+                taxincluded,
+                creditlimit,
+                terms,
+                meta_number,
+                cc,
+                bcc,
+                business_id,
+                language_code,
+                pricegroup_id,
+                curr,
+                startdate,
+                enddate
+            )
+            VALUES (
+                new_entity_id,
+                t_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,
+                in_curr,
+                in_startdate,
+                in_enddate
+            );
+            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, '');
+             
+        ELSIF FOUND THEN
+        
+            update company set tax_id = in_tax_id where id = in_id;
+            update entity_credit_account SET
+                discount = in_discount,
+                taxincluded = in_taxincluded,
+                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,
+                curr = in_curr,
+                startdate = in_startdate,
+                enddate = in_enddate                
+            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;
+        
+        END IF;
+        return in_id;
+    END;
+    
+$$ 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
+) 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);
+    END;
+
+$$ language 'plpgsql';
+
+
+CREATE OR REPLACE FUNCTION customer_search(in_pattern TEXT) returns setof customer_search_return as $$
+    
+    -- searches customer name, account number, street address, city, state,
+    -- other location-based stuff
+    
+    declare
+        v_row customer_search_return;
+        query text;
+    begin
+            
+        for v_row in select c.legal_name, v.* from customer v
+                    join company c on c.entity_id = v.entity_id 
+                    join entity e on e.id = v.entity_id 
+                    join company_to_location ctl on c.id = ctl.company_id
+                    join location l on l.id = ctl.location_id
+                    where l.line_one % in_pattern
+                    OR l.line_two % in_pattern
+                    OR l.line_three % in_pattern
+                    OR l.city_province % in_pattern
+                    OR c.legal_name % in_pattern
+                    OR e.name % in_pattern
+        LOOP
+        
+            RETURN NEXT v_row;
+        
+        END LOOP;
+        
+        RETURN;
+    
+    end;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION customer_retrieve(INT) returns setof customer as $$
+    
+    
+    select v.* from customer v 
+    join company c on c.entity_id = v.entity_id
+    where v.id = $1;
+    
+$$ language 'sql';
+COMMIT;
+
+CREATE OR REPLACE FUNCTION customer_next_customer_id() returns int as $$
+    
+    select nextval('company_id_seq');
+    
+$$ language 'sql';ƒ
\ No newline at end of file
diff --git a/sql/modules/Employee.sql b/sql/modules/Employee.sql
index c407e244..a934a3e5 100644
--- a/sql/modules/Employee.sql
+++ b/sql/modules/Employee.sql
@@ -1,66 +1,157 @@
 -- VERSION 1.3.0
+BEGIN;
 CREATE OR REPLACE FUNCTION employee_save
-(in_id integer, in_location_id integer, in_employeenumber varchar(32), 
-	in_name varchar(64), in_address1 varchar(32), in_address2 varchar(32),
+(in_id integer, in_employeenumber varchar(32), 
+	in_salutation int, in_first_name varchar(64), in_last_name varchar(64),
+	in_address1 varchar(32), in_address2 varchar(32),
 	in_city varchar(32), in_state varchar(32), in_zipcode varchar(10),
-	in_country varchar(32), in_workphone varchar(20), 
+	in_country int, in_workphone varchar(20), 
 	in_homephone varchar(20), in_startdate date, in_enddate date, 
 	in_notes text, in_role varchar(20), in_sales boolean, in_email text, 
 	in_ssn varchar(20), in_dob date, in_iban varchar(34), 
-	in_bic varchar(11), in_managerid integer) returns int
-AS
-$$
+	in_bic varchar(11), in_managerid integer) 
+returns int AS $$ 
+DECLARE
+    e_id int;
+    e entity;
+    loc location;
+    l_id int;
+    per person;
+    p_id int;
 BEGIN
-	UPDATE employees
-	SET location_id = in_location_id,
-		employeenumber = in_employeenumber,
-		name = in_name,
-		address1 = in_address1,
-		address2 = in_address2,
-		city = in_city,
-		state = in_state,
-		zipcode = in_zipcode,
-		country = in_country,
-		workphone = in_workphone,
-		homephone = in_homephone,
-		startdate = in_startdate,
-		enddate = in_enddate,
-		notes = in_notes,
-		role = in_role,
-		sales = in_sales,
-		email = in_email,
-		ssn = in_ssn,
-		dob=in_dob,
-		iban = in_iban, 
-		bic = in_bic, 
-		manager_id = in_managerid
-	WHERE id = in_id;
 
-	IF FOUND THEN
-		return in_id;
+    select * into e from entity where id = in_id and entity_class = 3;
+    
+    if found then
+        
+        select l.* into loc from location l 
+        left join person_to_location ptl on ptl.location_id = l.id
+        left join person p on p.id = ptl.person_id
+        where p.entity_id = in_id;
+        
+        select * into per from person p where p.entity_id = in_id;
+        
+        update location 
+        set
+            line_one = in_address1,
+        	line_two = in_address2,
+        	city_province = in_city,
+        	mail_code = in_zipcode,
+        	country_id = in_country
+        where id = loc.id;
+	
+    	UPDATE employee
+    	SET 
+    		employeenumber = in_employeenumber,
+    		startdate = in_startdate,
+    		enddate = in_enddate,
+    		role = in_role,
+    		sales = in_sales,
+    		ssn = in_ssn,
+    		dob = in_dob, 
+    		managerid = in_managerid
+    	WHERE entity_id = in_id;
+    	
+    	update entity_note
+    	set 
+    	    note = in_note
+    	where entity_id = in_id;
+    	
+    	UPDATE entity_bank_account 
+    	SET
+    	    bic = in_bic,
+    	    iban = in_iban
+    	WHERE entity_id = in_id;
+    	
+    	UPDATE person
+        SET
+            salutation_id = in_salutation,
+            first_name = in_first_name,
+            last_name = in_last_name
+        WHERE entity_id = in_id;
+        
+        UPDATE person_to_contact
+        set
+            contact = in_homephone
+        WHERE person_id = per.id
+          AND contact_class_id = 11;
+          
+        UPDATE person_to_contact
+        set
+          contact = in_workphone
+        WHERE person_id = per.id
+          AND contact_class_id = 1;
+          
+        UPDATE person_to_contact
+        set
+        contact = in_email
+        WHERE person_id = per.id
+        AND contact_class_id = 12;  
+        
+        return in_id;
+        
+	ELSIF NOT FOUND THEN	
+	    -- first, create a new entity
+    	-- Then, create an employee.
+	
+    	e_id := in_id; -- expect nextval entity_id to have been called.
+    	INSERT INTO entity (id, entity_class, name) VALUES (e_id, 3, in_first_name||' '||in_last_name);
+    	    
+    	INSERT INTO entity_bank_account (entity_id, iban, bic)
+    	VALUES (e_id, in_iban, in_bic);
+    	
+    	p_id := nextval('person_id_seq');
+    	insert into person (id, salutation_id, first_name, last_name, entity_id)
+    	VALUES
+    	(p_id, in_salutation, in_first_name, in_last_name, e_id);
+	    
+	    if in_notes is not null then
+	        insert into entity_note (note_class, note, ref_key, vector)
+	        values (1, in_notes, e_id, '');
+	    END IF;
+	    
+	    insert into person_to_contact (person_id, contact_class_id, contact)
+	    VALUES (p_id, 1, in_workphone); -- work phone #
+	    insert into person_to_contact (person_id, contact_class_id, contact)
+	    VALUES (p_id, 11, in_homephone); -- Home phone #
+	    insert into person_to_contact (person_id, contact_class_id, contact)
+	    VALUES (p_id, 12, in_email); -- email address.
+	    
+    	INSERT INTO employee
+    	(employeenumber, startdate, enddate, 
+    	    role, sales, ssn,
+    		dob, managerid, entity_id, entity_class_id)
+    	VALUES
+    	(in_employeenumber, in_startdate, in_enddate,
+    	    in_role, in_sales, in_ssn, 
+    	    in_dob,	in_managerid, e_id, 3);
+    		
+    	l_id := nextval('location_id_seq');
+    	insert into location (id, location_class, line_one, line_two, city_province, country_id, mail_code)
+    	VALUES (
+    	    l_id,
+    	    1,
+    	    in_address1,
+    	    in_address2,
+    	    in_city,
+    	    in_country, 
+    	    in_zipcode    	    
+    	);
+    	insert into person_to_location (person_id, location_id)
+    	VALUES (p_id, l_id);
+	
+    	return e_id;
 	END IF;
-	INSERT INTO employees
-	(location_id, employeenumber, name, address1, address2, 
-		city, state, zipcode, country, workphone, homephone,
-		startdate, enddate, notes, role, sales, email, ssn,
-		dob, iban, bic, managerid)
-	VALUES
-	(in_location_id, in_employeenumber, in_name, in_address1,
-		in_address2, in_city, in_state, in_zipcode, in_country,
-		in_workphone, in_homephone, in_startdate, in_enddate,
-		in_notes, in_role, in_sales, in_email, in_ssn, in_dob,
-		in_iban, in_bic, in_managerid);
-	SELECT currval('employee_id_seq') INTO employee_id;
-	return employee_id;
 END;
-$$ LANGUAGE PLPGSQL;
+$$ LANGUAGE 'plpgsql';
 
+-- why is this like this?
 CREATE OR REPLACE FUNCTION employee_get
 (in_id integer)
-returns employees as
+returns employee as
 $$
 DECLARE
-	emp employees%ROWTYPE;
+	emp employee%ROWTYPE;
 BEGIN
 	SELECT * INTO emp FROM employees WHERE id = in_id;
 	RETURN emp;
@@ -69,15 +160,15 @@ $$ language plpgsql;
 
 CREATE OR REPLACE FUNCTION employee_list_managers
 (in_id integer)
-RETURNS SETOF employees as
+RETURNS SETOF employee as
 $$
 DECLARE
-	emp employees%ROWTYPE;
+	emp employee%ROWTYPE;
 BEGIN
 	FOR emp IN 
-		SELECT * FROM employees 
+		SELECT * FROM employee
 		WHERE sales = '1' AND role='manager'
-			AND id <> coalesce(in_id, -1)
+			AND entity_id <> coalesce(in_id, -1)
 		ORDER BY name
 	LOOP
 		RETURN NEXT emp;
@@ -89,15 +180,25 @@ CREATE OR REPLACE FUNCTION employee_delete
 (in_id integer) returns void as
 $$
 BEGIN
-	DELETE FROM employees WHERE id = in_id;
+	DELETE FROM employee WHERE entity_id = in_id;
 	RETURN;
 END;
 $$ language plpgsql;
 
 -- as long as we need the datatype, might as well get some other use out of it!
+--
+-- % type is pg_trgm comparison.
+
+CREATE INDEX name_idx ON employee USING gist(name gist_trgm_ops);
+CREATE INDEX notes_idx ON entity_note USING gist(note gist_trgm_ops);
+
 CREATE OR REPLACE VIEW employee_search AS
-SELECT e.*, m.name AS manager 
-FROM employees e LEFT JOIN employees m ON (e.managerid = m.id);
+SELECT e.*, em.name AS manager, emn.note, en.name as name
+FROM employee e 
+LEFT JOIN entity en on (e.entity_id = en.id)
+LEFT JOIN employee m ON (e.managerid = m.entity_id)
+LEFT JOIN entity em on (em.id = m.entity_id)
+LEFT JOIN entity_note emn on (emn.ref_key = em.id);
 
 CREATE OR REPLACE FUNCTION employee_search
 (in_startdatefrom date, in_startdateto date, in_name varchar, in_notes text,
@@ -118,12 +219,13 @@ BEGIN
 				coalesce(in_enddateto, 'infinity'::timestamp)
 			AND coalesce(enddate, 'infinity'::timestamp) >= 
 				coalesce(in_enddatefrom, '-infinity'::timestamp)
-			AND lower(name) LIKE '%' || lower(in_name) || '%'
-			AND lower(notes) LIKE '%' || lower(in_notes) || '%'
+			AND (name % in_name
+			    OR note % in_notes)
 			AND (sales = 't' OR coalesce(in_sales, 'f') = 'f')
 	LOOP
 		RETURN NEXT emp;
 	END LOOP;
+	return;
 END;
 $$ language plpgsql;
-
+COMMIT;
\ No newline at end of file
diff --git a/sql/modules/Vendor.sql b/sql/modules/Vendor.sql
new file mode 100644
index 00000000..2026fffa
--- /dev/null
+++ b/sql/modules/Vendor.sql
@@ -0,0 +1,280 @@
+BEGIN;
+
+CREATE TYPE vendor_search_return AS (
+        legal_name text,
+        id int,
+        entity_id int,
+        entity_class int,
+        discount numeric,
+        taxincluded bool,
+        creditlimit numeric,
+        terms int2,
+        vendornumber int,
+        cc text,
+        bcc text,
+        business_id int,
+        language_code text,
+        pricegroup_id int,
+        curr char,
+        startdate date,
+        enddate date,
+        bic varchar, 
+        iban varchar, 
+        note text
+);
+
+CREATE OR REPLACE FUNCTION vendor_save (
+    in_id int,
+    
+    in_discount numeric, in_taxincluded bool, in_creditlimit numeric, 
+    in_terms int, in_vendornumber varchar(32), in_cc text, in_bcc text, 
+    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
+    
+) returns INT as $$
+    
+    -- does not require entity_class, as entity_class is a known given to be 1
+    
+    DECLARE
+        t_entity_class int;
+        new_entity_id int;
+        v_row company;
+        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');
+            
+            insert into entity (id, name, entity_class) 
+                VALUES (new_entity_id, in_name, t_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 entity_credit_account (
+                entity_id,
+                entity_class,
+                discount, 
+                taxincluded,
+                creditlimit,
+                terms,
+                cc,
+                bcc,
+                business_id,
+                language_code,
+                pricegroup_id,
+                curr,
+                startdate,
+                enddate,
+                meta_number
+            )
+            VALUES (
+                new_entity_id,
+                t_entity_class,
+                in_discount, 
+                in_taxincluded,
+                in_creditlimit,
+                in_terms,
+                in_cc,
+                in_bcc,
+                in_business_id,
+                in_language,
+                in_pricegroup_id,
+                in_curr,
+                in_startdate,
+                in_enddate,
+                in_vendornumber
+            );
+            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, '');
+             
+        ELSIF FOUND THEN
+        
+            update company set tax_id = in_tax_id where id = in_id;
+            update entity_credit_account SET
+                discount = in_discount,
+                taxincluded = in_taxincluded,
+                creditlimit = in_creditlimit,
+                terms = in_terms,
+                cc = in_cc,
+                bcc = in_bcc,
+                business_id = in_business_id,
+                language_code = in_language,
+                pricegroup_id = in_pricegroup_id,
+                curr = in_curr,
+                startdate = in_startdate,
+                enddate = in_enddate,
+                meta_number = in_vendornumber
+            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;
+        
+        END IF;
+        return in_id;
+    END;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION vendor_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
+) 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);
+    END;
+
+$$ language 'plpgsql';
+
+
+create or replace function _entity_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
+) returns int AS $$
+
+    DECLARE
+        l_row location;
+        l_id INT;
+    BEGIN
+    
+        SELECT l.* INTO l_row FROM location l 
+        JOIN company_to_location ctl ON ctl.location_id = l.id
+        JOIN company c on ctl.company_id = c.id
+        where c.id = in_company_id;
+        
+        IF NOT FOUND THEN
+        
+            l_id := nextval('location_id_seq');
+            
+            INSERT INTO location (id, location_class, line_one, line_two, 
+                 city_province, country_id, mail_code, created)
+            VALUES (
+                l_id,
+                in_location_class,
+                in_line_one,
+                in_line_two,
+                in_city_province,
+                in_country_code,
+                in_mail_code,
+                in_created
+            );
+            
+            INSERT INTO company_to_location (location_id, company_id)
+            VALUES (l_id, in_company_id);
+        
+        ELSIF FOUND THEN
+        
+            l_id := l.id;
+            update location SET
+                location_class = in_location_class,
+                line_one = in_line_one,
+                line_two = in_line_two,
+                city_province = in_city_province,
+                country_id = in_country_code,
+                mail_code = in_mail_code
+            WHERE id = l_id;        
+        
+        END IF;
+        return l_id;
+    END;
+
+$$ language 'plpgsql';
+
+CREATE INDEX company_name_gist__idx ON company USING gist(legal_name gist_trgm_ops);
+CREATE INDEX location_address_one_gist__idx ON location USING gist(line_one gist_trgm_ops);
+CREATE INDEX location_address_two_gist__idx ON location USING gist(line_two gist_trgm_ops);
+CREATE INDEX location_address_three_gist__idx ON location USING gist(line_three gist_trgm_ops);
+    
+CREATE INDEX location_city_prov_gist_idx ON location USING gist(city_province gist_trgm_ops);
+CREATE INDEX entity_name_gist_idx ON entity USING gist(name gist_trgm_ops);
+
+CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT, 
+    in_city_prov TEXT) 
+    RETURNS SETOF vendor_search_return AS $$
+    
+    -- searches vendor name, account number, street address, city, state,
+    -- other location-based stuff
+    
+    declare
+        v_row vendor_search_return;
+        query text;
+    begin
+            
+        for v_row in select c.legal_name, v.* from vendor v
+                    join company c on c.entity_id = v.entity_id 
+                    join entity e on e.id = v.entity_id 
+                    join company_to_location ctl on c.id = ctl.company_id
+                    join location l on l.id = ctl.location_id
+                    where (
+                        l.line_one % in_address
+                        OR l.line_two % in_address
+                        OR l.line_three % in_address
+                    )
+                    OR l.city_province % in_city_prov
+                    OR (
+                        c.legal_name % in_name
+                        OR e.name % in_name
+                    )
+        LOOP
+        
+            RETURN NEXT v_row;
+        
+        END LOOP;
+        
+        RETURN;
+    
+    end;
+    
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION vendor_retrieve(INT) returns setof vendor as $$
+    
+    select v.* from vendor v 
+    join company c on c.entity_id = v.entity_id
+    where v.id = $1;
+    
+$$ language 'sql';
+COMMIT;
+
+CREATE OR REPLACE FUNCTION vendor_next_vendor_id() returns int as $$
+    
+    select nextval('company_id_seq');
+    
+$$ language 'sql';
\ No newline at end of file