[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2253] trunk
- Subject: SF.net SVN: ledger-smb:[2253] trunk
- From: ..hidden..
- Date: Tue, 12 Aug 2008 19:47:04 +0000
Revision: 2253
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2253&view=rev
Author: einhverfr
Date: 2008-08-12 19:47:03 +0000 (Tue, 12 Aug 2008)
Log Message:
-----------
* Correcting discount display discrepancies in multiple payment detail screen
* Merging entity_credit_account mapped functions to SVN main
* Adding schema entries for entity_credit_account mappings
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Company.pm
trunk/UI/payments/payments_detail.html
trunk/scripts/payment.pl
trunk/sql/Pg-database.sql
trunk/sql/modules/Company.sql
trunk/sql/modules/Payment.sql
trunk/sql/modules/Settings.sql
Modified: trunk/LedgerSMB/DBObject/Company.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Company.pm 2008-08-12 16:40:54 UTC (rev 2252)
+++ trunk/LedgerSMB/DBObject/Company.pm 2008-08-12 19:47:03 UTC (rev 2253)
@@ -88,9 +88,17 @@
sub save_location {
my $self = shift @_;
+
$self->{country_id} = $self->{country_code};
- $self->exec_method(funcname => 'company__location_save');
+ if($self->{credit_id}){
+ $self->exec_method(funcname => 'eca__location_save');
+ } else {
+ my ($ref) = $self->exec_method(funcname => 'company__location_save');
+ my @vals = values %$ref;
+ $self->{location_id} = $vals[0];
+ }
+
$self->{dbh}->commit;
}
@@ -154,13 +162,15 @@
@{$self->{contact_class_list}} =
$self->exec_method(funcname => 'entity_list_contact_class');
- @{$self->{credit_list}} =
- $self->exec_method(funcname => 'entity__list_credit');
}
sub save_contact {
my ($self) = @_;
- $self->exec_method(funcname => 'company__save_contact');
+ if ($self->{credit_id}){
+ $self->exec_method(funcname => 'eca__save_contact');
+ } else {
+ $self->exec_method(funcname => 'company__save_contact');
+ }
$self->{dbh}->commit;
}
@@ -172,7 +182,11 @@
sub save_notes {
my $self = shift @_;
- $self->exec_method(funcname => 'entity__save_notes');
+ if ($self->{credit_id} && $self->{note_class} eq '3'){
+ $self->exec_method(funcname => 'eca__save_notes');
+ } else {
+ $self->exec_method(funcname => 'entity__save_notes');
+ }
$self->{dbh}->commit;
}
@@ -198,19 +212,65 @@
$self->merge($ref);
$self->{threshold} = $self->format_amount(amount => $self->{threshold});
+ @{$self->{credit_list}} =
+ $self->exec_method(funcname => 'entity__list_credit');
+
+ for (@{$self->{credit_list}}){
+ print STDERR "credit_id: $_->{credit_id}\n";
+ if (($_->{credit_id} eq $self->{credit_id})
+ or ($_->{meta_number} eq $self->{meta_number})){
+ $self->merge($_);
+ last;
+ }
+ }
$self->{name} = $self->{legal_name};
+ if ($self->{credit_id} and $self->{meta_number}){
+ $self->get_credit_id;
+ }
- @{$self->{locations}} = $self->exec_method(
+ if ($self->{credit_id}){
+ @{$self->{locations}} = $self->exec_method(
+ funcname => 'eca__list_locations');
+ @{$self->{contacts}} = $self->exec_method(
+ funcname => 'eca__list_contacts');
+ @{$self->{notes}} = $self->exec_method(
+ funcname => 'eca__list_notes');
+
+ }
+ else {
+ @{$self->{locations}} = $self->exec_method(
funcname => 'company__list_locations');
-
- @{$self->{contacts}} = $self->exec_method(
+ @{$self->{contacts}} = $self->exec_method(
funcname => 'company__list_contacts');
+ @{$self->{notes}} = $self->exec_method(
+ funcname => 'company__list_notes');
+ }
+
+ if ($self->{location_id}){
+ for (@{$self->{locations}}){
+ if ($_->{id} = $self->{location_id}){
+ my $old_id = $self->{id};
+ $self->merge($_);
+ $self->{id} = $old_id;
+ last;
+ }
+ }
+ }
+
+ if ($self->{contact_id}){
+ for (@{$self->{contacts}}){
+ if ($_->{id} = $self->{contact_id}){
+ my $old_id = $self->{id};
+ $self->merge($_);
+ $self->{id} = $old_id;
+ last;
+ }
+ }
+ }
+
@{$self->{bank_account}} = $self->exec_method(
funcname => 'company__list_bank_account');
-
- @{$self->{notes}} = $self->exec_method(
- funcname => 'company__list_notes');
};
1;
Modified: trunk/UI/payments/payments_detail.html
===================================================================
--- trunk/UI/payments/payments_detail.html 2008-08-12 16:40:54 UTC (rev 2252)
+++ trunk/UI/payments/payments_detail.html 2008-08-12 19:47:03 UTC (rev 2253)
@@ -198,7 +198,8 @@
ELSE ; 'name_has_no_vouchers' ;
END
?>"><?lsmb r.contact_name ?></span></td>
- <td class="invoice"><?lsmb r.total_due ?> <?lsmb currency ?></td>
+ <td class="invoice"><?lsmb INCLUDE format_money number=r.total_due ?>
+ <?lsmb currency ?></td>
<td class="payment" class="details_select">
<?lsmb INCLUDE input element_data = {
name = "paid_$r.contact_id"
@@ -293,12 +294,10 @@
value = icount
} ?>
<?lsmb IF !r.unselected;
- IF (${"paid_$r.contact_id"} == 'all');
- grand_total = grand_total + r.total_due;
- ELSIF (${"paid_$r.contact_id"} == 'some');
+ IF (${"paid_$r.contact_id"} == 'some');
grand_total = grand_total + contact_total;
ELSE;
- contact_total = 'error';
+ grand_total = grand_total + r.total_due;
END; # IF (paid...)
END # IF !r.unselected) ?>
<tr class="subtotal">
Modified: trunk/scripts/payment.pl
===================================================================
--- trunk/scripts/payment.pl 2008-08-12 16:40:54 UTC (rev 2252)
+++ trunk/scripts/payment.pl 2008-08-12 19:47:03 UTC (rev 2253)
@@ -307,7 +307,6 @@
}
for my $inv (1 .. $inv_count){
- print STDERR "Invoice $inv of " .$payment->{"invoice_count_$id"} . "\n";
my $invhash = {};
my $inv_id = $payment->{"invoice_${id}_$inv"};
for (qw(invnumber invdate)){
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2008-08-12 16:40:54 UTC (rev 2252)
+++ trunk/sql/Pg-database.sql 2008-08-12 19:47:03 UTC (rev 2253)
@@ -1,11 +1,7 @@
begin;
CREATE SEQUENCE id;
--- Central DB structure
--- This is the central database stuff which is used across all datasets
--- in the ledger-smb.conf it is called 'ledgersmb' by default, but obviously
--- can be named anything.
+-- As of 1.3 there is no central db anymore. --CT
-
-- BEGIN new entity management
CREATE TABLE entity_class (
id serial primary key,
@@ -22,7 +18,8 @@
name text check (name ~ '[[:alnum:]_]'),
entity_class integer references entity_class(id) not null ,
created date not null default current_date,
- PRIMARY KEY(name,entity_class));
+ control_code text,
+ PRIMARY KEY(control_code, entity_class));
COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$;
COMMENT ON COLUMN entity.name IS $$ This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com $$;
@@ -47,6 +44,40 @@
COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
+CREATE TABLE entity_credit_account (
+ id serial not null unique,
+ entity_id int not null references entity(id) ON DELETE CASCADE,
+ entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
+ discount numeric,
+ discount_terms int default 0,
+ discount_account_id int references chart(id),
+ taxincluded bool default 'f',
+ creditlimit NUMERIC default 0,
+ terms int2 default 0,
+ meta_number varchar(32),
+ cc text,
+ bcc text,
+ business_id int,
+ language_code varchar(6),
+ pricegroup_id int references pricegroup(id),
+ curr char(3),
+ startdate date DEFAULT CURRENT_DATE,
+ enddate date,
+ threshold numeric default 0,
+ employee_id int references entity_employee(entity_id),
+ primary_contact int references person(id),
+ ar_ap_account_id int references chart(id),
+ cash_account_id int references chart(id),
+ PRIMARY KEY(entity_id, meta_number, entity_class)
+);
+
+CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u
+ON entity_credit_account(meta_number)
+WHERE entity_class = 2;
+
+COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
+$$This index is used to ensure that AR accounts are not reused.$$;
+
-- USERS stuff --
CREATE TABLE users (
id serial UNIQUE,
@@ -163,6 +194,24 @@
company_id integer not null references company(id) ON DELETE CASCADE,
PRIMARY KEY(location_id,company_id));
+COMMENT ON TABLE company_to_location IS
+$$ This table is used for locations generic to companies. For contract-bound
+addresses, use eca_to_location instead $$;
+
+CREATE TABLE eca_to_location (
+ location_id integer references location(id) not null,
+ location_class integer not null references location_class(id),
+ credit_id integer not null references entity_credit_account(id)
+ ON DELETE CASCADE,
+ PRIMARY KEY(location_id,credit_id));
+
+CREATE UNIQUE INDEX eca_to_location_billing_u ON eca_to_location(credit_id)
+ WHERE location_class = 1;
+
+COMMENT ON TABLE eca_to_location IS
+$$ This table is used for locations bound to contracts. For generic contact
+addresses, use company_to_location instead $$;
+
CREATE TABLE salutation (
id serial unique,
salutation text primary key);
@@ -275,10 +324,23 @@
COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$;
+CREATE TABLE eca_to_contact (
+ credit_id integer not null references entity_credit_account(id)
+ ON DELETE CASCADE,
+ contact_class_id integer references contact_class(id) not null,
+ contact text check(contact ~ '[[:alnum:]_]') not null,
+ description text,
+ PRIMARY KEY (credit_id, contact_class_id, contact));
+
+COMMENT ON TABLE eca_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single vendor or customer account. For generic
+contacts, use company_to_contact or person_to_contact instead.$$;
+
-- Begin rocking notes interface
+-- Begin rocking notes interface
CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'));
INSERT INTO note_class(id,class) VALUES (1,'Entity');
INSERT INTO note_class(id,class) VALUES (2,'Invoice');
+INSERT INTO note_class(id,class) VALUES (3,'Entity Credit Account');
CREATE UNIQUE INDEX note_class_idx ON note_class(lower(class));
CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id),
@@ -297,6 +359,13 @@
CREATE UNIQUE INDEX invoice_note_class_idx ON note_class(lower(class));
CREATE INDEX invoice_note_vectors_idx ON invoice_note USING gist(vector);
+CREATE TABLE eca_note()
+ INHERITS (note);
+ALTER TABLE eca_note ADD CHECK (note_class = 3);
+ALTER TABLE eca_note ADD FOREIGN KEY (ref_key)
+ REFERENCES entity_credit_account(id)
+ ON DELETE CASCADE;
+
-- END entity
--
@@ -362,6 +431,7 @@
rcptnumber|1
paynumber|1
separate_duties|1
+entity_control|A-00001
\.
COMMENT ON TABLE defaults IS $$
@@ -466,40 +536,6 @@
pricegroup text
);
-CREATE TABLE entity_credit_account (
- id serial not null unique,
- entity_id int not null references entity(id) ON DELETE CASCADE,
- entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
- discount numeric,
- discount_terms int default 0,
- discount_account_id int references chart(id),
- taxincluded bool default 'f',
- creditlimit NUMERIC default 0,
- terms int2 default 0,
- meta_number varchar(32),
- cc text,
- bcc text,
- business_id int,
- language_code varchar(6),
- pricegroup_id int references pricegroup(id),
- curr char(3),
- startdate date DEFAULT CURRENT_DATE,
- enddate date,
- threshold numeric default 0,
- employee_id int references entity_employee(entity_id),
- primary_contact int references person(id),
- ar_ap_account_id int references chart(id),
- cash_account_id int references chart(id),
- PRIMARY KEY(entity_id, meta_number, entity_class)
-);
-
-CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u
-ON entity_credit_account(meta_number)
-WHERE entity_class = 2;
-
-COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
-$$This index is used to ensure that AR accounts are not reused.$$;
-
-- THe following credit accounts are used for inventory adjustments.
INSERT INTO entity (name, entity_class) values ('Inventory Entity', 1);
@@ -2507,7 +2543,7 @@
198 module vouchers.pl 553
199 module vouchers.pl 559
199 action create_batch 560
-199 batch_type payable 561
+199 batch_type ap 561
201 module vouchers.pl 562
201 action create_batch 563
203 module vouchers.pl 565
Modified: trunk/sql/modules/Company.sql
===================================================================
--- trunk/sql/modules/Company.sql 2008-08-12 16:40:54 UTC (rev 2252)
+++ trunk/sql/modules/Company.sql 2008-08-12 19:47:03 UTC (rev 2253)
@@ -623,4 +623,104 @@
$$ language 'plpgsql';
+create or replace function eca__location_save(
+ in_credit_id int, in_location_id int,
+ in_location_class int, in_line_one text, in_line_two text,
+ in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
+ in_country_code int
+) returns int AS $$
+
+ DECLARE
+ l_row location;
+ l_id INT;
+ BEGIN
+
+ DELETE FROM eca_to_location
+ WHERE credit_id = in_credit_id
+ AND location_class = in_location_class
+ AND location_id = in_location_id;
+
+ SELECT location_save(in_line_one, in_line_two, in_line_three, in_city,
+ in_state, in_mail_code, in_country_code)
+ INTO l_id;
+
+ INSERT INTO eca_to_location
+ (credit_id, location_class, location_id)
+ VALUES (in_credit_id, in_location_class, l_id);
+
+ RETURN l_id;
+ END;
+
+$$ language 'plpgsql';
+
+
+CREATE OR REPLACE FUNCTION company_get_billing_info (in_id int)
+returns company_billing_info as
+$$
+DECLARE out_var company_billing_info;
+ t_id INT;
+BEGIN
+ select c.legal_name, c.tax_id, a.line_one, a.line_two, a.line_three,
+ a.city, a.state, a.mail_code, cc.name
+ into out_var
+ FROM company c
+ JOIN entity_credit_account eca ON (eca.entity_id = c.entity_id)
+ JOIN eca_to_location cl ON (eca.id = cl.credit_id)
+ JOIN location a ON (a.id = cl.location_id)
+ JOIN country cc ON (cc.id = a.country_id)
+ WHERE eca.id = in_id AND location_class = 1;
+
+ RETURN out_var;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION eca__list_locations(in_entity_id int)
+RETURNS SETOF location_result AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+ FOR out_row IN
+ SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
+ l.state, l.mail_code, c.name, lc.class
+ FROM location l
+ JOIN eca_to_location ctl ON (ctl.location_id = l.id)
+ JOIN location_class lc ON (ctl.location_class = lc.id)
+ JOIN country c ON (c.id = l.country_id)
+ WHERE ctl.credit_id = in_credit_id
+ ORDER BY lc.id, l.id, c.name
+ LOOP
+ RETURN NEXT out_row;
+ END LOOP;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION eca__list_contacts(in_credit_id int)
+RETURNS SETOF contact_list AS $$
+DECLARE out_row contact_list;
+BEGIN
+ FOR out_row IN
+ SELECT cl.class, c.description, c.contact
+ FROM eca_to_contact c
+ JOIN contact_class cl ON (c.contact_class_id = cl.id)
+ WHERE credit_id = in_credit_id
+ LOOP
+ return next out_row;
+ END LOOP;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION eca__save_contact
+(in_credit_id int, in_contact_class int, in_description text, in_contact text)
+RETURNS INT AS
+$$
+DECLARE out_id int;
+BEGIN
+ INSERT INTO eca_to_contact(credit_id, contact_class_id,
+ description, contact)
+ VALUES (in_credit_id, in_contact_class, in_description, in_contact);
+
+ RETURN 1;
+END;
+$$ LANGUAGE PLPGSQL;
+
-- COMMIT;
Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql 2008-08-12 16:40:54 UTC (rev 2252)
+++ trunk/sql/modules/Payment.sql 2008-08-12 19:47:03 UTC (rev 2253)
@@ -187,7 +187,12 @@
FOR payment_item IN
SELECT c.id AS contact_id, e.name AS contact_name,
c.meta_number AS account_number,
- sum(p.due) AS total_due,
+ sum (coalesce(p.due, 0) -
+ CASE WHEN c.discount_terms
+ > extract('days' FROM age(a.transdate))
+ THEN 0
+ ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100
+ END) AS total_due,
compound_array(ARRAY[[
a.id::text, a.invnumber, a.transdate::text,
a.amount::text, (a.amount - p.due)::text,
Modified: trunk/sql/modules/Settings.sql
===================================================================
--- trunk/sql/modules/Settings.sql 2008-08-12 16:40:54 UTC (rev 2252)
+++ trunk/sql/modules/Settings.sql 2008-08-12 19:47:03 UTC (rev 2253)
@@ -68,3 +68,7 @@
return new_value;
END;
$$ LANGUAGE PLPGSQL;
+
+-- Table schema defaults
+
+ALTER TABLE entity ADD control_code TEXT default setting_increment('entity_control');
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.