[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1373] trunk
- Subject: SF.net SVN: ledger-smb: [1373] trunk
- From: ..hidden..
- Date: Tue, 10 Jul 2007 14:16:16 -0700
Revision: 1373
http://svn.sourceforge.net/ledger-smb/?rev=1373&view=rev
Author: aurynn_cmd
Date: 2007-07-10 14:16:16 -0700 (Tue, 10 Jul 2007)
Log Message:
-----------
Fixes Add Vendor, to correctly work with the new entity_credit_account table, in place of the customer/vendor tables.
Removes the Vendor and Customer tables, replacing them with entity_credit_account (previously entity_metadata) and Vendor and Customer as a view, mimicking the original Vendor and Customer tables.
Modified Paths:
--------------
trunk/LedgerSMB/CT.pm
trunk/LedgerSMB/Form.pm
trunk/sql/Pg-database.sql
Modified: trunk/LedgerSMB/CT.pm
===================================================================
--- trunk/LedgerSMB/CT.pm 2007-07-10 21:14:55 UTC (rev 1372)
+++ trunk/LedgerSMB/CT.pm 2007-07-10 21:16:16 UTC (rev 1373)
@@ -209,7 +209,8 @@
$form->{vc} = 'vendor';
$form->{entity_class} = 1;
}
-
+
+ # this should really all be replaced by an upsert.
if ( $form->{id} ) {
$query = qq|
DELETE FROM $form->{vc}tax
@@ -231,7 +232,7 @@
if ( $sth->fetchrow_array ) {
$sth->finish;
$query = qq|
- UPDATE $form->{vc}
+ UPDATE enti
SET discount = ?
taxincluded = ?
creditlimit = ?
@@ -265,7 +266,7 @@
if (!$updated){
# Creating Entity
($form->{entity_id}) = $dbh->selectrow_array("SELECT nextval('entity_id_seq')");
- $query = qq|INSERT INTO entity (id, name, entity_class) VALUES (?, ?)|;
+ $query = qq|INSERT INTO entity (id, name, entity_class) VALUES (?, ?,?)|;
$sth = $dbh->prepare($query);
$sth->execute($form->{entity_id}, $form->{name}, $form->{entity_class});
$sth->finish;
@@ -276,9 +277,9 @@
$query = qq|
INSERT INTO location
(id, line_one, line_two, city_province, mail_code,
- country_id, location_class,created)
+ country_id, location_class, created)
VALUES
- (?, ?, ?, ?,
+ (?, ?, ?, ?, ?,
(SELECT id FROM country
WHERE short_name = ? OR name = ?),
1, current_date)
@@ -291,43 +292,60 @@
#Creating company
- # Removed entity_class_id ~Aurynn
- # removed primary_location_id ~Aurynn
+ # Removed entity_class_id,
+ # removed primary_location_id,
+ # added sic_code ~Aurynn
$query = qq|
INSERT INTO company
- (entity_id, legal_name, tax_id)
+ (entity_id, legal_name, tax_id, sic_code)
VALUES
- (?, ?, ?)
+ (?, ?, ?, ?)
|;
$sth = $dbh->prepare($query) || $form->dberror($query);
$sth->execute($form->{entity_id}, # $form->{entity_class}, # removed entity_class_id ~Aurynn
$form->{name},
# $form->{location_id}, # removed by ~aurynn
- $form->{taxnumber});
- #Creating customer record
+ $form->{taxnumber},
+ $form->{sic_code});
+ # Creating entity_metadata record, replacing customer and vendor.
+
$query = qq|
- INSERT INTO customer
- (entity_id, discount, taxincluded, creditlimit, terms,
- customernumber, cc, bcc, business_id, sic_code,
- language_code, pricegroup_id, curr, startdate,
- enddate, invoice_notes, bic, iban)
-
- VALUES (?, ?, ?, ?, ?,
- ?, ?, ?, ?, ?,
- ?, ?, ?, ?,
- ?, ?, ?, ?)|;
+ 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 (?, ?, ?, ?, ?,
+ ?, ?, ?, ?,
+ ?, ?, ?, ?,
+ ?, ?)|;
+
$sth = $dbh->prepare($query);
$sth->execute(
- $form->{entity_id}, $form->{discount}, $form->{taxincluded},
- $form->{creditlimit},
+ $form->{entity_id}, $form->{entity_class}, $form->{discount},
+ $form->{taxincluded}, $form->{creditlimit},
$form->{terms}, $form->{"$form->{vc}number"}, $form->{cc},
- $form->{bcc}, $form->{business_id}, $form->{sic_code},
+ $form->{bcc}, $form->{business_id},
$form->{language_code}, $form->{pricegroup_id}, $form->{curr},
$form->{startdate} || undef, $form->{enddate} || undef,
- $form->{invoice_notes},
- $form->{bic}, $form->{iban}
) || $form->dberror($query);
+
+ $query = qq|
+ INSERT INTO entity_bank_account (entity_id, bic, iban)
+ VALUES (?,?,?)
+ |;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{entity_id}, $form->{bic}, $form->{iban}) ||
+ $form->dberror($query);
+ $query = qq|
+ insert into entity_invoice_notes (entity_id, note)
+ values (?, ?)
+ |;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{entity_id}, $form->{notes}) ||
+ $form->dberror($query);
+
}
}
Modified: trunk/LedgerSMB/Form.pm
===================================================================
--- trunk/LedgerSMB/Form.pm 2007-07-10 21:14:55 UTC (rev 1372)
+++ trunk/LedgerSMB/Form.pm 2007-07-10 21:16:16 UTC (rev 1373)
@@ -1197,9 +1197,11 @@
@queryargs = ( $transdate, $transdate );
}
-
+
+ # Company name is stored in $self->{vendor} or $self->{customer}
my $name = $self->like( lc $self->{$table} );
-
+
+ # Vendor and Customer are now views into entity_credit_account.
my $query = qq|
SELECT * FROM $table t
JOIN entity e ON t.entity_id = e.id
@@ -1207,23 +1209,20 @@
$where
ORDER BY e.name|;
- unshift( @queryargs, $name, $name );
+ unshift( @queryargs, $name, $name, $table );
my $sth = $self->{dbh}->prepare($query);
$sth->execute(@queryargs) || $self->dberror($query);
my $i = 0;
@{ $self->{name_list} } = ();
-
while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
push( @{ $self->{name_list} }, $ref );
$i++;
}
-
$sth->finish;
- $i;
-
+ return $i;
}
sub all_vc {
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2007-07-10 21:14:55 UTC (rev 1372)
+++ trunk/sql/Pg-database.sql 2007-07-10 21:16:16 UTC (rev 1373)
@@ -324,21 +324,18 @@
--
--
-CREATE TABLE customer (
- id serial PRIMARY KEY,
- entity_id int references entity(id),
+
+CREATE TABLE entity_credit_account (
+ entity_id int not null references entity(id),
+ entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
discount numeric,
taxincluded bool default 'f',
creditlimit NUMERIC default 0,
terms int2 default 0,
- customernumber varchar(32),
+ meta_number varchar(32),
cc text,
bcc text,
business_id int,
- invoice_notes text,
- sic_code varchar(6),
- iban varchar(34),
- bic varchar(11),
language_code varchar(6),
pricegroup_id int,
curr char(3),
@@ -346,10 +343,79 @@
enddate date
);
-COMMENT ON TABLE customer IS $$ This is now a metadata table that holds information specific to customers. Source info is not part of the entity management $$;
-COMMENT ON COLUMN customer.entity_id IS $$ This is the relationship between entities and customers $$;
+CREATE TABLE entity_bank_account (
+ id serial not null,
+ entity_id int references entity(id) not null,
+ 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)
+);
+CREATE VIEW customer AS
+ SELECT emd.entity_id,
+ emd.entity_class,
+ emd.discount,
+ emd.taxincluded,
+ emd.creditlimit,
+ emd.terms,
+ emd.meta_number as customernumber,
+ emd.cc,
+ emd.bcc,
+ emd.business_id,
+ emd.language_code,
+ emd.pricegroup_id,
+ emd.curr,
+ emd.startdate,
+ emd.enddate,
+ eba.bic,
+ eba.iban,
+ 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
+ where emd.entity_class = 2;
+
+CREATE VIEW vendor AS
+ SELECT emd.entity_id,
+ emd.entity_class,
+ emd.discount,
+ emd.taxincluded,
+ emd.creditlimit,
+ emd.terms,
+ emd.meta_number as vendornumber,
+ emd.cc,
+ emd.bcc,
+ emd.business_id,
+ emd.language_code,
+ emd.pricegroup_id,
+ emd.curr,
+ emd.startdate,
+ emd.enddate,
+ eba.bic,
+ eba.iban,
+ 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
+ where emd.entity_class = 1;
+
+COMMENT ON TABLE entity_metadata 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. $$;
+
+COMMENT ON COLUMN entity_metadata.entity_id IS $$ This is the relationship between entities and their metadata. $$;
+COMMENT ON COLUMN entity_metadata.entity_class IS $$ A reference to entity_class, requiring that entity_metadata only apply to vendors and customers, using the entity_class table as the Point Of Truth. $$;
+
+ALTER TABLE company ADD COLUMN sic_code varchar;
+
--
--
CREATE TABLE parts (
@@ -634,31 +700,7 @@
-- SHIPTO really needs to be pushed into entities too
--
-CREATE TABLE vendor (
- entity_id int references entity(id) not null PRIMARY KEY,
- entity_class_id int references entity_class(id) not null check (entity_class_id = 1),
- terms int2 default 0,
- taxincluded bool default 'f',
- vendornumber varchar(32),
- cc text,
- bcc text,
- gifi_accno varchar(30),
- business_id int,
- taxnumber varchar(32),
- sic_code varchar(6),
- discount numeric,
- creditlimit numeric default 0,
- iban varchar(34),
- bic varchar(11),
- language_code varchar(6),
- pricegroup_id int,
- curr char(3),
- startdate date,
- enddate date
-);
-COMMENT ON TABLE vendor IS $$ Now a meta data table $$;
-
--
CREATE TABLE project (
id serial PRIMARY KEY,
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.