diff options
| -rw-r--r-- | LedgerSMB/DBObject/Company.pm | 82 | ||||
| -rw-r--r-- | UI/payments/payments_detail.html | 9 | ||||
| -rw-r--r-- | scripts/payment.pl | 1 | ||||
| -rw-r--r-- | sql/Pg-database.sql | 118 | ||||
| -rw-r--r-- | sql/modules/Company.sql | 100 | ||||
| -rw-r--r-- | sql/modules/Payment.sql | 7 | ||||
| -rw-r--r-- | sql/modules/Settings.sql | 4 |
7 files changed, 262 insertions, 59 deletions
diff --git a/LedgerSMB/DBObject/Company.pm b/LedgerSMB/DBObject/Company.pm index 23a56cec..b2eed72c 100644 --- a/LedgerSMB/DBObject/Company.pm +++ b/LedgerSMB/DBObject/Company.pm @@ -88,8 +88,16 @@ This method saves an address for a company. 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 @@ sub get_metadata { @{$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_bank_account { 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 @@ sub get { $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; diff --git a/UI/payments/payments_detail.html b/UI/payments/payments_detail.html index 206d0d59..6fabf8ca 100644 --- a/UI/payments/payments_detail.html +++ b/UI/payments/payments_detail.html @@ -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"> diff --git a/scripts/payment.pl b/scripts/payment.pl index 295ad60e..6dfbed05 100644 --- a/scripts/payment.pl +++ b/scripts/payment.pl @@ -307,7 +307,6 @@ sub print { } 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)){ diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index e1d8251e..5fd33f58 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -1,10 +1,6 @@ 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 ( @@ -22,7 +18,8 @@ CREATE TABLE entity ( 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 @@ CREATE TABLE entity_class_to_entity ( 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 @@ CREATE TABLE company_to_location ( 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 @@ CREATE TABLE company_to_contact ( 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 INDEX invoice_note_id_idx ON invoice_note(id); 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 @@ poll_frequency|1 rcptnumber|1 paynumber|1 separate_duties|1 +entity_control|A-00001 \. COMMENT ON TABLE defaults IS $$ @@ -466,40 +536,6 @@ CREATE TABLE pricegroup ( 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 @@ COPY menu_attribute (node_id, attribute, value, id) FROM stdin; 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 diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql index ce9723a0..c4118853 100644 --- a/sql/modules/Company.sql +++ b/sql/modules/Company.sql @@ -623,4 +623,104 @@ create or replace function _entity_location_save( $$ 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; diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 2a7e6e56..bee243f8 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -187,7 +187,12 @@ BEGIN 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, diff --git a/sql/modules/Settings.sql b/sql/modules/Settings.sql index eed89581..9c2abb59 100644 --- a/sql/modules/Settings.sql +++ b/sql/modules/Settings.sql @@ -68,3 +68,7 @@ BEGIN return new_value; END; $$ LANGUAGE PLPGSQL; + +-- Table schema defaults + +ALTER TABLE entity ADD control_code TEXT default setting_increment('entity_control'); |