diff options
| -rw-r--r-- | LedgerSMB/AA.pm | 57 | ||||
| -rw-r--r-- | LedgerSMB/Customer.pm | 33 | ||||
| -rw-r--r-- | LedgerSMB/IC.pm | 10 | ||||
| -rw-r--r-- | LedgerSMB/Vendor.pm | 33 | ||||
| -rw-r--r-- | bin/aa.pl | 2 | ||||
| -rw-r--r-- | scripts/customer.pl | 106 | ||||
| -rw-r--r-- | scripts/vendor.pl | 179 | ||||
| -rw-r--r-- | sql/Pg-database.sql | 32 | ||||
| -rw-r--r-- | sql/modules/Customer.sql | 212 | ||||
| -rw-r--r-- | sql/modules/Employee.sql | 218 | ||||
| -rw-r--r-- | sql/modules/Vendor.sql | 280 |
11 files changed, 1074 insertions, 88 deletions
diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm index b2df1567..dfcb0cc4 100644 --- a/LedgerSMB/AA.pm +++ b/LedgerSMB/AA.pm @@ -33,9 +33,31 @@ # #====================================================================== + + package AA; use LedgerSMB::Sysconfig; +=pod + +=head1 post_transaction() +Post transaction uses the following variables in the $form variable: + * dbh - the database connection handle + * currency - The current users' currency + * defaultcurrency - The "normal" currency + * department - Unknown + * department_id - ID for the department + * exchangerate - Conversion between currency and defaultcurrency + * invnumber - invoice number + * reverse - ? + * rowcount - Number of rows in the invoice + * taxaccounts - Apply taxes? + * taxincluded - ? + * transdate - Date of the transaction + * vc - Vendor or customer - determines transaction type + +=cut + sub post_transaction { my ( $self, $myconfig, $form ) = @_; @@ -278,18 +300,31 @@ sub post_transaction { my $uid = localtime; $uid .= "$$"; - + + # The query is done like this as the login name maps to the users table + # which maps to the user conf table, which links to an entity, to which + # a person is also attached. This is done in this fashion because we + # are using the current username as the "person" inserting the new + # AR/AP Transaction. + # ~A $query = qq| - INSERT INTO $table (invnumber) - VALUES ('$uid')|; - - $dbh->do($query) || $form->dberror($query); + INSERT INTO $table (invnumber, person_id) + VALUES (?, (select p.id from person p, entity e, users u + where u.username = ? + AND e.id = u.entity_id + AND p.entity_id = e.id ))|; + + # the second param is undef, as the DBI api expects a hashref of + # attributes to pass to $dbh->prepare. This is not used here. + # ~A + + $dbh->do($query,undef,$uid,$form->{login}) || $form->dberror($query); $query = qq| SELECT id FROM $table - WHERE invnumber = '$uid'|; + WHERE invnumber = ?|; - ( $form->{id} ) = $dbh->selectrow_array($query); + ( $form->{id} ) = $dbh->selectrow_array($query,undef,$uid); } # record last payment date in ar/ap table @@ -312,7 +347,6 @@ sub post_transaction { curr = ?, notes = ?, department_id = ?, - person_id = ?, ponumber = ? WHERE id = ? |; @@ -324,7 +358,7 @@ sub post_transaction { $form->{duedate}, $paid, $datepaid, $invnetamout, $form->{currency}, $form->{notes}, - $form->{department_id}, $form->{employee_id}, + $form->{department_id}, $form->{ponumber}, $form->{id} ); @@ -752,9 +786,9 @@ sub transactions { FROM $table a JOIN $form->{vc} vc USING (entity_id) LEFT JOIN employee e ON (a.person_id = e.entity_id) - LEFT JOIN employee m ON (e.managerid = m.id) + LEFT JOIN employee m ON (e.managerid = m.entity_id) JOIN entity ee ON (e.entity_id = ee.id) - JOIN entity me ON (m.entity_id = me.id) + JOIN entity me ON (m.entity_id = me.id) JOIN entity vce ON (vc.entity_id = vce.id) LEFT JOIN exchangerate ex ON (ex.curr = a.curr AND ex.transdate = a.transdate) @@ -789,6 +823,7 @@ sub transactions { my $where = "1 = 1"; if ( $form->{"$form->{vc}_id"} ) { + $form->{entity_id} = $form->{$form->{vc}."_id"}; $where .= qq| AND a.entity_id = $form->{entity_id}|; } else { diff --git a/LedgerSMB/Customer.pm b/LedgerSMB/Customer.pm new file mode 100644 index 00000000..ef25c4a1 --- /dev/null +++ b/LedgerSMB/Customer.pm @@ -0,0 +1,33 @@ +package LedgerSMB::DBObject::Customer; + +use base qw(LedgerSMB); +use LedgerSMB::DBObject; + +sub save_to_db { + + my $self = shift @_; + + my $id; + if ($self->{id} >= 1) { + $id = $self->{id}; + } + else { + $id = $self->next_customer_id(); + } + $id = $self->save($id, $self->{discount}, $self->{tax_included}, + $self->{creditlimit}, $self->{terms}, $self->{customernumber}, + $self->{cc}, $self->{bcc}, $self->{business_id}, $self->{language}, + $self->{pricegroup}, $self->{currency}, $self->{startdate}, + $self->{enddate} + ); + + # Undef in the created field causes the system to use now() as the current + # creation date. + $self->location_save( + $id, 1, $self->{line_one}, $self->{line_two}, $self->{line_three}, + $self->{city_province}, $self->{mailing_code}, $self->{country}, undef + + ); + return $id; +} +1;
\ No newline at end of file diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm index d3db12e8..c8d39f5f 100644 --- a/LedgerSMB/IC.pm +++ b/LedgerSMB/IC.pm @@ -1829,7 +1829,10 @@ sub create_links { my ($count) = $dbh->selectrow_array($query); if ( $count < $myconfig->{vclimit} ) { - $query = qq|SELECT id, name FROM vendor ORDER BY name|; + $query = qq|SELECT v.id, e.name + FROM vendor v + join entity e on e.id = v.entity_id + ORDER BY e.name|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -1845,7 +1848,10 @@ sub create_links { ($count) = $dbh->selectrow_array($query); if ( $count < $myconfig->{vclimit} ) { - $query = qq|SELECT id, name FROM customer ORDER BY name|; + $query = qq|SELECT c.id, e.name + FROM customer c + join entity e on e.id = c.entity_id + ORDER BY e.name|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); diff --git a/LedgerSMB/Vendor.pm b/LedgerSMB/Vendor.pm new file mode 100644 index 00000000..52aa4944 --- /dev/null +++ b/LedgerSMB/Vendor.pm @@ -0,0 +1,33 @@ +package LedgerSMB::DBObject::Vendor; + +use base qw(LedgerSMB); +use LedgerSMB::DBObject; + +sub save_to_db { + + my $self = shift @_; + + my $id; + if ($self->{id} >= 1) { + $id = $self->{id}; + } + else { + $id = $self->next_vendor_id(); + } + $id = $self->save($id, $self->{discount}, $self->{tax_included}, + $self->{creditlimit}, $self->{terms}, $self->{vendornumber}, + $self->{cc}, $self->{bcc}, $self->{business_id}, $self->{language}, + $self->{pricegroup}, $self->{currency}, $self->{startdate}, + $self->{enddate} + ); + + # Undef in the created field causes the system to use now() as the current + # creation date. + $self->location_save( + $id, 1, $self->{line_one}, $self->{line_two}, $self->{line_three}, + $self->{city_province}, $self->{mailing_code}, $self->{country}, undef + + ); + return $id; +} +1;
\ No newline at end of file @@ -1540,6 +1540,8 @@ qq|<input name="l_transdate" class=checkbox type=checkbox value=Y checked> | } sub transactions { + # it shouldn't be putting it into vendor_id or customer_id, but into + # entity_id, conforming to the new entity tables. if ( $form->{ $form->{vc} } ) { $form->{ $form->{vc} } = $form->unescape( $form->{ $form->{vc} } ); ( $form->{ $form->{vc} }, $form->{"$form->{vc}_id"} ) = diff --git a/scripts/customer.pl b/scripts/customer.pl new file mode 100644 index 00000000..8b476995 --- /dev/null +++ b/scripts/customer.pl @@ -0,0 +1,106 @@ + +=pod + +=head1 NAME + +LedgerSMB::Scripts::customer - LedgerSMB class defining the Controller +functions, template instantiation and rendering for customer editing and display. + +=head1 SYOPSIS + +This module is the UI controller for the customer DB access; it provides the +View interface, as well as defines the Save customer. +Save customer will update or create as needed. + + +=head1 METHODS + +=cut + +package LedgerSMB::Scripts::Customer; + +use LedgerSMB::Template; +use LedgerSMB::DBObject::Customer; + +=pod + +=over + +=item get($self, $request, $user) + +Requires form var: id + +Extracts a single customer from the database, using its company ID as the primary +point of uniqueness. Shows (appropriate to user privileges) and allows editing +of the customer informations. + +=back + +=cut + +sub get { + + my ($class, $request) = @_; + my $customer = LedgerSMB::DBObject::Customer->new(base => $request, copy => 'all'); + my $result = $customer->get($customer->{id}); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'customer.html', language => $user->{language}, + format => 'html'); + $template->render($results); + +} + +=pod + +=over + +=item search($self, $request, $user) + +Requires form var: search_pattern + +Directly calls the database function search, and returns a set of all customers +found that match the search parameters. Search parameters search over address +as well as customer/Company name. + +=back + +=cut + +sub search { + my ($class, $request) = @_; + my $customer = LedgerSMB::DBObject::Customer->new(base => $request, copy => 'all'); + my $results = $customer->search($customer->{search_pattern}); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'customer_search.html', language => $user->{language}, + format => 'html'); + $template->render($results); +} + +=pod + +=over + +=item save($self, $request, $user) + +Saves a customer to the database. The function will update or insert a new +customer as needed, and will generate a new Company ID for the customer if needed. + +=back + +=cut + +sub save { + + my ($class, $request) = @_; + my $customer = LedgerSMB::DBObject::Customer->new(base => $request, copy => 'all'); + my $result = $customer->save_to_db(); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'customer.html', language => $user->{language}, + format => 'html'); + $template->render($result); +} + +1;
\ No newline at end of file diff --git a/scripts/vendor.pl b/scripts/vendor.pl new file mode 100644 index 00000000..c8c21503 --- /dev/null +++ b/scripts/vendor.pl @@ -0,0 +1,179 @@ + +=pod + +=head1 NAME + +LedgerSMB::Scripts::Vendor - LedgerSMB class defining the Controller +functions, template instantiation and rendering for Vendor editing and display. + +=head1 SYOPSIS + +This module is the UI controller for the Vendor DB access; it provides the +View interface, as well as defines the Save Vendor. +Save vendor will update or create as needed. + + +=head1 METHODS + +=cut + +package LedgerSMB::Scripts::Vendor; + +use LedgerSMB::Template; +use LedgerSMB::DBObject::Vendor; + +=pod + +=over + +=item get($self, $request, $user) + +Requires form var: id + +Extracts a single Vendor from the database, using its company ID as the primary +point of uniqueness. Shows (appropriate to user privileges) and allows editing +of the vendor informations. + +=back + +=cut + +sub get { + + my ($class, $request) = @_; + my $vendor = LedgerSMB::DBObject::Vendor->new(base => $request, copy => 'all'); + my $result = $vendor->get($vendor->{id}); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'vendor.html', language => $user->{language}, + format => 'html'); + $template->render($results); + +} + +=pod + +=over + +=item search($self, $request, $user) + +Requires form var: search_pattern + +Directly calls the database function search, and returns a set of all vendors +found that match the search parameters. Search parameters search over address +as well as Vendor/Company name. + +=back + +=cut + +sub search { + my ($class, $request) = @_; + my $vendor = LedgerSMB::DBObject::Vendor->new(base => $request, copy => 'all'); + my $results = $vendor->search($vendor->{search_pattern}); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'vendor_search.html', language => $user->{language}, + format => 'html'); + $template->render($results); +} + +=pod + +=over + +=item save($self, $request, $user) + +Saves a Vendor to the database. The function will update or insert a new +vendor as needed, and will generate a new Company ID for the vendor if needed. + +=back + +=cut + +sub save { + + my ($class, $request) = @_; + my $vendor = LedgerSMB::DBObject::Vendor->new(base => $request, copy => 'all'); + my $result = $vendor->save_to_db(); + + my $template = LedgerSMB::Template->new( user => $user, + template => 'vendor.html', language => $user->{language}, + format => 'html'); + $template->render($result); +} + +=pod + +=over + +=item vendor_invoice($self, $request, $user) + +Added based on existing New Vendor screen. + +=back + +=cut + + +sub vendor_invoice { + + +} + +=pod + +=over + +=item purchase_order($self, $request, $user) + +Added based on existing New Vendor screen. + +=back + +=cut + +sub purchase_order { + + +} + +=pod + +=over + +=item rfq($self, $request, $user) + +Added based on existing New Vendor screen. + +=back + +=cut + +sub rfq { + + $self->save(@_); + my ($class, $request) = @_; + # saves a new vendor, then generates something. + +} + +=pod + +=over + +=item pricelist($self, $request, $user) + +Added based on existing New Vendor screen. + +=back + +=cut + +sub pricelist { + + + +} + +1; 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 |