[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1454] trunk
- Subject: SF.net SVN: ledger-smb: [1454] trunk
- From: ..hidden..
- Date: Mon, 06 Aug 2007 13:28:36 -0700
Revision: 1454
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1454&view=rev
Author: aurynn_cmd
Date: 2007-08-06 13:28:31 -0700 (Mon, 06 Aug 2007)
Log Message:
-----------
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.
Modified Paths:
--------------
trunk/LedgerSMB/AA.pm
trunk/LedgerSMB/IC.pm
trunk/bin/aa.pl
trunk/sql/Pg-database.sql
trunk/sql/modules/Employee.sql
Added Paths:
-----------
trunk/LedgerSMB/Customer.pm
trunk/LedgerSMB/Vendor.pm
trunk/scripts/customer.pl
trunk/scripts/vendor.pl
trunk/sql/modules/Customer.sql
trunk/sql/modules/Vendor.sql
Modified: trunk/LedgerSMB/AA.pm
===================================================================
--- trunk/LedgerSMB/AA.pm 2007-08-03 18:59:21 UTC (rev 1453)
+++ trunk/LedgerSMB/AA.pm 2007-08-06 20:28:31 UTC (rev 1454)
@@ -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 @@
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')|;
+ 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 ))|;
- $dbh->do($query) || $form->dberror($query);
+ # 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 @@
curr = ?,
notes = ?,
department_id = ?,
- person_id = ?,
ponumber = ?
WHERE id = ?
|;
@@ -324,7 +358,7 @@
$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 @@
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 @@
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 {
Added: trunk/LedgerSMB/Customer.pm
===================================================================
--- trunk/LedgerSMB/Customer.pm (rev 0)
+++ trunk/LedgerSMB/Customer.pm 2007-08-06 20:28:31 UTC (rev 1454)
@@ -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
Modified: trunk/LedgerSMB/IC.pm
===================================================================
--- trunk/LedgerSMB/IC.pm 2007-08-03 18:59:21 UTC (rev 1453)
+++ trunk/LedgerSMB/IC.pm 2007-08-06 20:28:31 UTC (rev 1454)
@@ -1829,7 +1829,10 @@
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 @@
($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);
Added: trunk/LedgerSMB/Vendor.pm
===================================================================
--- trunk/LedgerSMB/Vendor.pm (rev 0)
+++ trunk/LedgerSMB/Vendor.pm 2007-08-06 20:28:31 UTC (rev 1454)
@@ -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
Modified: trunk/bin/aa.pl
===================================================================
--- trunk/bin/aa.pl 2007-08-03 18:59:21 UTC (rev 1453)
+++ trunk/bin/aa.pl 2007-08-06 20:28:31 UTC (rev 1454)
@@ -1540,6 +1540,8 @@
}
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"} ) =
Added: trunk/scripts/customer.pl
===================================================================
--- trunk/scripts/customer.pl (rev 0)
+++ trunk/scripts/customer.pl 2007-08-06 20:28:31 UTC (rev 1454)
@@ -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
Added: trunk/scripts/vendor.pl
===================================================================
--- trunk/scripts/vendor.pl (rev 0)
+++ trunk/scripts/vendor.pl 2007-08-06 20:28:31 UTC (rev 1454)
@@ -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;
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2007-08-03 18:59:21 UTC (rev 1453)
+++ trunk/sql/Pg-database.sql 2007-08-06 20:28:31 UTC (rev 1454)
@@ -188,7 +188,7 @@
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 @@
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 @@
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 @@
bic varchar,
iban varchar,
UNIQUE (id),
- PRIMARY KEY (bic, iban)
+ PRIMARY KEY (entity_id, bic, iban)
);
-CREATE TABLE entity_invoice_notes (
- id serial not null,
- entity_id int not null references entity(id),
- note text,
- unique (id)
-);
-
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 @@
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 @@
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. $$;
Added: trunk/sql/modules/Customer.sql
===================================================================
--- trunk/sql/modules/Customer.sql (rev 0)
+++ trunk/sql/modules/Customer.sql 2007-08-06 20:28:31 UTC (rev 1454)
@@ -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
Modified: trunk/sql/modules/Employee.sql
===================================================================
--- trunk/sql/modules/Employee.sql 2007-08-03 18:59:21 UTC (rev 1453)
+++ trunk/sql/modules/Employee.sql 2007-08-06 20:28:31 UTC (rev 1454)
@@ -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 @@
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 @@
(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 @@
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
Added: trunk/sql/modules/Vendor.sql
===================================================================
--- trunk/sql/modules/Vendor.sql (rev 0)
+++ trunk/sql/modules/Vendor.sql 2007-08-06 20:28:31 UTC (rev 1454)
@@ -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
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.