From 343c6184f7ac98ae28f4bd457a0b2c17d12530ce Mon Sep 17 00:00:00 2001 From: einhverfr Date: Fri, 21 Nov 2008 18:23:51 +0000 Subject: Committing David Mora's single payment interface enhancements git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2410 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/CP.pm | 1 + LedgerSMB/DBObject/Payment.pm | 42 ++++- LedgerSMB/Num2text.pm | 2 +- UI/payments/payment2.html | 11 +- scripts/payment.pl | 160 ++++++++++++---- sql/Pg-database.sql | 425 +----------------------------------------- sql/modules/Payment.sql | 285 +++++++++++++++++++++++++--- t/43-dbtest.t | 2 +- templates/demo/check_base.tex | 15 +- 9 files changed, 437 insertions(+), 506 deletions(-) diff --git a/LedgerSMB/CP.pm b/LedgerSMB/CP.pm index b901354b..2f3d5307 100644 --- a/LedgerSMB/CP.pm +++ b/LedgerSMB/CP.pm @@ -37,6 +37,7 @@ package CP; use LedgerSMB::Sysconfig; + sub new { my ( $type, $countrycode ) = @_; diff --git a/LedgerSMB/DBObject/Payment.pm b/LedgerSMB/DBObject/Payment.pm index b148165e..65526609 100644 --- a/LedgerSMB/DBObject/Payment.pm +++ b/LedgerSMB/DBObject/Payment.pm @@ -19,6 +19,7 @@ included COPYRIGHT and LICENSE files for more information. =cut package LedgerSMB::DBObject::Payment; +use LedgerSMB::Num2text; use base qw(LedgerSMB::DBObject); use strict; use Math::BigFloat lib => 'GMP'; @@ -547,7 +548,46 @@ This method uses payment_post to store a payment (not a bulk payment) on the dat sub post_payment { my ($self) = @_; - $self->exec_method(funcname => 'payment_post'); + # We have to check if it was a fx_payment + $self->{currency} = $self->{curr}; + + + if ("$self->{currency}" ne $self->get_default_currency()) { + # First we have to check for an exchangerate on this date + my $db_exchangerate = $self->get_exchange_rate($self->{curr},$self->{datepaid}); + if (!$db_exchangerate) { + # We have to set the exchangerate + + + $self->call_procedure(procname => 'payments_set_exchangerate', args => ["$self->{account_class}", "$self->{exchangerate}" ,"$self->{curr}", "$self->{datepaid}"]); + + + + } + elsif ($db_exchangerate != $self->{exchangerate} ) + { + # Something went wrong + $self->error("Exchange rate inconsistency with database, please try again") + } + } + my @TMParray = $self->exec_method(funcname => 'payment_post'); $self->{dbh}->commit(); + $self->{payment_id} = $TMParray[0]->{payment_post}; + return $self->{payment_id}; +} + +=item gather_printable_info + +This method retrieves all the payment related info needed to build a +document and print it. IT IS NECESSARY TO ALREADY HAVE payment_id on $self + +=cut + + +sub gather_printable_info { +my ($self) = @_; +@{$self->{header_info}} = $self->exec_method(funcname => 'payment_gather_header_info'); +@{$self->{line_info}} = $self->exec_method(funcname => 'payment_gather_line_info'); } + 1; diff --git a/LedgerSMB/Num2text.pm b/LedgerSMB/Num2text.pm index 57accc3b..aceb4840 100644 --- a/LedgerSMB/Num2text.pm +++ b/LedgerSMB/Num2text.pm @@ -121,7 +121,7 @@ sub num2text { sub num2text_en { my ( $self, $amount ) = @_; - + return $self->{numbername}{0} unless $amount; my @textnumber = (); diff --git a/UI/payments/payment2.html b/UI/payments/payment2.html index 49abaa18..c1c99a8e 100644 --- a/UI/payments/payment2.html +++ b/UI/payments/payment2.html @@ -151,25 +151,24 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif', - + - " id="" type="checkbox" class="checkbox" checked > - - + +
">
- + - diff --git a/scripts/payment.pl b/scripts/payment.pl index a081c5e8..8007a175 100644 --- a/scripts/payment.pl +++ b/scripts/payment.pl @@ -640,9 +640,6 @@ if ($request->{department}) { my @account_options = $Payment->list_accounting(); # LETS GET THE POSSIBLE SOURCES my @sources_options = $Payment->get_sources(\%$locale); -# WE MUST PREPARE THE ENTITY INFORMATION -#@array_options = $Payment->get_vc_info();# IS THIS WORKING? - # LETS BUILD THE CURRENCIES INFORMATION # FIRST, WE NEED TO KNOW THE DEFAULT CURRENCY my $default_currency = $Payment->get_default_currency(); @@ -659,18 +656,17 @@ my @column_headers = ({text => $locale->text('Invoice')}, {text => $locale->text('Paid').$default_currency_text}, {text => $locale->text('Discount').$default_currency_text}, {text => $locale->text('Apply Disc')}, - {text => $locale->text('Amount Due').$default_currency_text}, - {text => $locale->text('To pay').$default_currency_text} + {text => $locale->text('Memo')}, + {text => $locale->text('Amount Due').$default_currency_text} ); # WE NEED TO KNOW IF WE ARE USING A CURRENCY THAT NEEDS AN EXCHANGERATE - if ($default_currency ne $request->{curr} ) { # FIRST WE PUSH THE OTHER COLUMN HEADERS WE NEED push @column_headers, {text => $locale->text('Exchange Rate')}, {text => $locale->text('Amount Due').$currency_text}, {text => $locale->text('To pay').$currency_text}; # WE SET THEM IN THE RIGHT ORDER FOR THE TABLE INSIDE THE UI - @column_headers[6,7,8] = @column_headers[7,8,6]; + @column_headers[7,8] = @column_headers[8,7]; # DOES THE CURRENCY IN USE HAS AN EXCHANGE RATE?, IF SO # WE MUST SET THE VALUE, OTHERWISE THE UI WILL HANDLE IT $exchangerate = $request->{exrate} ? @@ -711,47 +707,61 @@ for my $ref (0 .. $#array_options) { if ( !$request->{"checkbox_$array_options[$ref]->{invoice_id}"}) { # SHOULD I APPLY DISCCOUNTS? $request->{"optional_discount_$array_options[$ref]->{invoice_id}"} = $request->{first_load}? "on": $request->{"optional_discount_$array_options[$ref]->{invoice_id}"}; - + # LETS SET THE EXCHANGERATE VALUES - my $due_fx; my $topay_fx_value; + my $due_fx = $request->{"optional_discount_$array_options[$ref]->{invoice_id}"} ? $request->round_amount($array_options[$ref]->{due_fx}) : $request->round_amount($array_options[$ref]->{due_fx}) + $array_options[$ref]->{discount_fx} ; + my $topay_fx_value; if ("$exchangerate") { - $topay_fx_value = $due_fx = $request->round_amount("$array_options[$ref]->{due}"/"$exchangerate"); - if ($request->{"optional_discount_$array_options[$ref]->{invoice_id}"}) { - $topay_fx_value = $due_fx = $request->round_amount($due_fx - "$array_options[$ref]->{discount}"/"$exchangerate"); + $topay_fx_value = $due_fx; + if (!$request->{"optional_discount_$array_options[$ref]->{invoice_id}"}) { + $topay_fx_value = $due_fx = $due_fx + $request->round_amount($array_options[$ref]->{discount}/$array_options[$ref]->{exchangerate}); } } else { - $topay_fx_value = $due_fx = "N/A"; + $topay_fx_value = "N/A"; } + + # We need to check for unhandled overpayment, see the post function for details # First we will see if the discount should apply? +=i dont think this is working my $temporary_discount = 0; if (($request->{"optional_discount_$array_options[$ref]->{invoice_id}"})&&($due_fx <= $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} + $request->round_amount($array_options[$ref]->{discount}/"$exchangerate"))) { - $temporary_discount = $request->round_amount("$array_options[$ref]->{discount}"/"$exchangerate"); + $temporary_discount = $request->round_amount("$array_options[$ref]->{discount}"/$array_options[$ref]->{exchangerate}); } +=cut # We need to compute the unhandled_overpayment, notice that all the values inside the if already have -# the exchangerate applied +# the exchangerate applied + if ( $due_fx < $request->{"topay_fx_$array_options[$ref]->{invoice_id}"}) { # We need to store all the overpayments so we can use it on the screen $unhandled_overpayment = $request->round_amount($unhandled_overpayment + $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} - $due_fx ); $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} = "$due_fx"; - } + } +#Now its time to build the link to the invoice :) + +my $uri = $Payment->{account_class} == 1 ? 'ap' : 'ar'; +$uri .= '.pl?action=edit&id='.$array_options[$ref]->{invoice_id}.'&path=bin/mozilla&login='.$request->{login}; + push @invoice_data, { invoice => { number => $array_options[$ref]->{invnumber}, id => $array_options[$ref]->{invoice_id}, - href => 'ar.pl?id='."$array_options[$ref]->{invoice_id}" + href => $uri }, invoice_date => "$array_options[$ref]->{invoice_date}", amount => "$array_options[$ref]->{amount}", - due => $request->{"optional_discount_$array_options[$ref]->{invoice_id}"}? "$array_options[$ref]->{due}" - "$array_options[$ref]->{discount}": "$array_options[$ref]->{due}", - paid => "$array_options[$ref]->{amount}" - "$array_options[$ref]->{due}", + due => $request->{"optional_discount_$array_options[$ref]->{invoice_id}"}? "$array_options[$ref]->{due}" : "$array_options[$ref]->{due}" + "$array_options[$ref]->{discount}", + paid => "$array_options[$ref]->{amount}" - "$array_options[$ref]->{due}"-"$array_options[$ref]->{discount}", discount => $request->{"optional_discount_$array_options[$ref]->{invoice_id}"} ? "$array_options[$ref]->{discount}" : 0 , optional_discount => $request->{"optional_discount_$array_options[$ref]->{invoice_id}"}, - exchange_rate => "$exchangerate", + exchange_rate => "$array_options[$ref]->{exchangerate}", due_fx => "$due_fx", # This was set at the begining of the for statement topay => "$array_options[$ref]->{due}" - "$array_options[$ref]->{discount}", source_text => $request->{"source_text_$array_options[$ref]->{invoice_id}"}, optional => $request->{"optional_pay_$array_options[$ref]->{invoice_id}"}, selected_account => $request->{"account_$array_options[$ref]->{invoice_id}"}, selected_source => $request->{"source_$array_options[$ref]->{invoice_id}"}, + memo => { name => "memo_invoice_$array_options[$ref]->{invoice_id}", + value => $request->{"memo_invoice_$array_options[$ref]->{invoice_id}"} + },#END HASH topay_fx => { name => "topay_fx_$array_options[$ref]->{invoice_id}", value => $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} ? $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} eq 'N/A' ? @@ -829,7 +839,7 @@ my $select = { type => { name => 'type', value => $request->{type} }, login => { name => 'login', - value => $request->{_user}->{login} }, + value => $request->{login} }, accountclass => { name => 'account_class', value => $Payment->{account_class} @@ -892,8 +902,6 @@ eval {$template->render($select) }; if ($@) { $request->error("$@"); } # PRINT ERRORS ON THE UI } - - =pod =item post_payment @@ -909,6 +917,9 @@ sub post_payment { my ($request) = @_; my $locale = $request->{_locale}; my $Payment = LedgerSMB::DBObject::Payment->new({'base' => $request}); + +if (!$request->{exrate}) { + $Payment->error($locale->text('Exchange rate hasn\'t been defined').'!');} # LETS GET THE CUSTOMER/VENDOR INFORMATION ($Payment->{entity_credit_id}, $Payment->{company_name}) = split /--/ , $request->{'vendor-customer'}; # LETS GET THE DEPARTMENT INFO @@ -941,6 +952,7 @@ my @array_options; my @amount; my @discount; my @cash_account_id; +my @memo; my @source; my @transaction_id; my @op_amount; @@ -963,32 +975,34 @@ for my $ref (0 .. $#array_options) { # we will assume that a discount should apply only # if this is the last payment of an invoice my $temporary_discount = 0; - if (($request->{"optional_discount_$array_options[$ref]->{invoice_id}"})&&("$array_options[$ref]->{due}"/"$request->{exrate}" <= $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} + $array_options[$ref]->{discount})) { - $temporary_discount = $array_options[$ref]->{discount}; + if (($request->{"optional_discount_$array_options[$ref]->{invoice_id}"})&&("$array_options[$ref]->{due_fx}" <= $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} + $array_options[$ref]->{discount_fx})) { + $temporary_discount = $array_options[$ref]->{discount_fx}; } # # The prefix cash is to set the movements of the cash accounts, # same names are used for ap/ar accounts w/o the cash prefix. # - if ( "$array_options[$ref]->{due}"/"$request->{exrate}" < $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} + $temporary_discount ) { + if ( "$array_options[$ref]->{due_fx}" < $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} ) { # We need to store all the overpayments so we can use it on a new payment2 screen $unhandled_overpayment = $request->round_amount($unhandled_overpayment + $request->{"topay_fx_$array_options[$ref]->{invoice_id}"} + $temporary_discount - $array_options[$ref]->{amount}) ; - + } if ($request->{"optional_discount_$array_options[$ref]->{invoice_id}"}) { - push @amount, $array_options[$ref]->{discount}; + push @amount, $array_options[$ref]->{discount_fx}; push @cash_account_id, $discount_account_id; push @source, $locale->text('Applied discount'); push @transaction_id, $array_options[$ref]->{invoice_id}; } push @amount, $request->{"topay_fx_$array_options[$ref]->{invoice_id}"}; # We'll use this for both cash and ap/ar accounts push @cash_account_id, $request->{"optional_pay_$array_options[$ref]->{invoice_id}"} ? $request->{"account_$array_options[$ref]->{invoice_id}"} : $request->{account}; - push @source, $request->{"source1_$array_options[$ref]->{invoice_id}"}.' '.$request->{"source2_$array_options[$ref]->{invoice_id}"}; # We'll use this for both source and ap/ar accounts + push @source, $request->{"optional_pay_$array_options[$ref]"} ? + $request->{"source_$array_options[$ref]->{invoice_id}"}.' '.$request->{"source_text_$array_options[$ref]->{invoice_id}"} + : $request->{source}.' '.$request->{source_value}; # We'll use this for both source and ap/ar accounts + push @memo, $request->{"memo_invoice_$array_options[$ref]->{invoice_id}"}; push @transaction_id, $array_options[$ref]->{invoice_id}; } } # Check if there is an unhandled overpayment and run payment2 as needed - if ($unhandled_overpayment) { &payment2($request); return 0; @@ -1020,27 +1034,91 @@ for (my $i=1 ; $i <= $request->{overpayment_qty}; $i++) { $Payment->{cash_account_id} = $Payment->_db_array_scalars(@cash_account_id); $Payment->{amount} = $Payment->_db_array_scalars(@amount); $Payment->{source} = $Payment->_db_array_scalars(@source); + $Payment->{memo} = $Payment->_db_array_scalars(@memo); $Payment->{transaction_id} = $Payment->_db_array_scalars(@transaction_id); $Payment->{op_amount} = $Payment->_db_array_scalars(@op_amount); $Payment->{op_cash_account_id} = $Payment->_db_array_scalars(@op_cash_account_id); $Payment->{op_source} = $Payment->_db_array_scalars(@op_source); $Payment->{op_memo} = $Payment->_db_array_scalars(@op_memo); $Payment->{op_account_id} = $Payment->_db_array_scalars(@op_account_id); -# Ok, hoping for the best... +# Ok, passing the control to postgresql and hoping for the best... $Payment->post_payment(); -# We've gotta print anything, in the near future this will redirect to a new payment. - my $select = {}; - my $template = LedgerSMB::Template->new( - user => $request->{_user}, - locale => $request->{_locale}, - path => 'UI/payments', - template => 'payment2', + if ($request->{continue_to_calling_sub}){ return $Payment->{payment_id} ;} + else { + # Our work here is done, ask for more payments. + &payment($request); + } +} + +=pod + +=item print_payment + +This sub will print the payment on the selected media, it needs to +receive the $Payment object with all this information. + +=back + +=cut + +sub print_payment { + my ($Payment) = @_; + my $locale = $Payment->{_locale}; + $Payment->gather_printable_info(); + my $header = @{$Payment->{header_info}}[0]; + my @rows = @{$Payment->{line_info}}; + ############################################################################### + # FIRST CODE SECTION + # + # THE FOLLOWING LINES OF CODE ADD SOME EXTRA PROCESSING TO THE DATA THAT + # WILL BE AVAILIBLE ON THE UI, + # PLEASE FEEL FREE TO ADD EXTRA LINES IF YOU NEED IT (AND KNOW WHAT YOU ARE DOING). + ############################################################################### + # First we need to solve some ugly behaviour in the template system + $header->{amount} = abs("$header->{amount}"); + # The next code will enable number to text conversion + $Payment->init(); + $header->{amount2text} = $Payment->num2text($header->{amount}); + + + ############################################################################ +# $Payment->{format_amount} = sub {return $Payment->format_amount(@_); }; + + # IF YOU NEED MORE INFORMATION ON THE HEADER AND ROWS ITEMS CHECK SQL FUNCTIONS + # payment_gather_header_info AND payment_gather_line_info + my $select = { + header => $header, + rows => \@rows + }; + my $template = LedgerSMB::Template->new( + user => $Payment->{_user}, + locale => $Payment->{_locale}, + path => "templates/test/", + template => 'printPayment', format => 'HTML' ); - eval {$template->render($select) }; - if ($@) { $request->error("$@"); } # PRINT ERRORS ON THE UI - + eval {$template->render($select) }; + if ($@) { $Payment->error("$@"); } # PRINT ERRORS ON THE UI } +=pod + +=item post_and_print_payment + +This is simply a shortcut between post_payment and print_payment methods, please refer +to these functions + +=back + +=cut + +sub post_and_print_payment { +my ($request) = @_; +$request->{continue_to_calling_sub} = 1; +$request->{payment_id} = &post_payment($request); +my $locale = $request->{_locale}; +my $Payment = LedgerSMB::DBObject::Payment->new({'base' => $request}); +&print_payment($Payment); +} eval { do "scripts/custom/payment.pl"}; 1; diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 45cfe827..16615c3f 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -1,426 +1,4 @@ -begin; -CREATE SEQUENCE id; --- As of 1.3 there is no central db anymore. --CT - -CREATE TABLE chart ( - id serial PRIMARY KEY, - accno text NOT NULL, - description text, - charttype char(1) DEFAULT 'A', - category char(1), - link text, - gifi_accno text, - contra bool DEFAULT 'f' -); --- --- pricegroup added here due to references -CREATE TABLE pricegroup ( - id serial PRIMARY KEY, - pricegroup text -); - --- BEGIN new entity management -CREATE TABLE entity_class ( - id serial primary key, - class text check (class ~ '[[:alnum:]_]') NOT NULL, - active boolean not null default TRUE); - -COMMENT ON TABLE entity_class IS $$ Defines the class type such as vendor, customer, contact, employee $$; -COMMENT ON COLUMN entity_class.id IS $$ The first 7 values are reserved and permanent $$; - -CREATE index entity_class_idx ON entity_class(lower(class)); - -CREATE TABLE entity ( - id serial UNIQUE, - name text check (name ~ '[[:alnum:]_]'), - entity_class integer references entity_class(id) not null , - created date not null default current_date, - 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 $$; - - -ALTER TABLE entity ADD FOREIGN KEY (entity_class) REFERENCES entity_class(id); - -INSERT INTO entity_class (id,class) VALUES (1,'Vendor'); -INSERT INTO entity_class (id,class) VALUES (2,'Customer'); -INSERT INTO entity_class (id,class) VALUES (3,'Employee'); -INSERT INTO entity_class (id,class) VALUES (4,'Contact'); -INSERT INTO entity_class (id,class) VALUES (5,'Lead'); -INSERT INTO entity_class (id,class) VALUES (6,'Referral'); - -SELECT setval('entity_class_id_seq',7); - -CREATE TABLE entity_class_to_entity ( - entity_class_id integer not null references entity_class(id) ON DELETE CASCADE, - entity_id integer not null references entity(id) ON DELETE CASCADE, - PRIMARY KEY(entity_class_id,entity_id) - ); - -COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$; - --- USERS stuff -- -CREATE TABLE users ( - id serial UNIQUE, - username varchar(30) primary key, - entity_id int not null references entity(id) on delete cascade -); - -COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$; - --- Session tracking table - - -CREATE TABLE session( -session_id serial PRIMARY KEY, -token VARCHAR(32) CHECK(length(token) = 32), -last_used TIMESTAMP default now(), -ttl int default 3600 not null, -users_id INTEGER NOT NULL references users(id), -transaction_id INTEGER NOT NULL -); - -CREATE TABLE open_forms ( -id SERIAL PRIMARY KEY, -session_id int REFERENCES session(session_id) ON DELETE CASCADE -); - --- -CREATE TABLE transactions ( - id int PRIMARY KEY, - table_name text, - locked_by int references "session" (session_id) ON DELETE SET NULL, - approved_by int references entity (id), - approved_at timestamp -); - -COMMENT on TABLE transactions IS -$$ This table tracks basic transactions across AR, AP, and GL related tables. -It provies a referential integrity enforcement mechanism for the financial data -and also some common features such as discretionary (and pessimistic) locking -for long batch workflows. $$; - -CREATE OR REPLACE FUNCTION lock_record (int, int) returns bool as -$$ -declare - locked int; -begin - SELECT locked_by into locked from transactions where id = $1; - IF NOT FOUND THEN - RETURN FALSE; - ELSEIF locked is not null AND locked <> $2 THEN - RETURN FALSE; - END IF; - UPDATE transactions set locked_by = $2 where id = $1; - RETURN TRUE; -end; -$$ language plpgsql; - -COMMENT ON column transactions.locked_by IS -$$ This should only be used in pessimistic locking measures as required by large -batch work flows. $$; - --- LOCATION AND COUNTRY -CREATE TABLE country ( - id serial PRIMARY KEY, - name text check (name ~ '[[:alnum:]_]') NOT NULL, - short_name text check (short_name ~ '[[:alnum:]_]') NOT NULL, - itu text); - -COMMENT ON COLUMN country.itu IS $$ The ITU Telecommunication Standardization Sector code for calling internationally. For example, the US is 1, Great Britain is 44 $$; - -CREATE UNIQUE INDEX country_name_idx on country(lower(name)); - -CREATE TABLE location_class ( - id serial UNIQUE, - class text check (class ~ '[[:alnum:]_]') not null, - authoritative boolean not null, - PRIMARY KEY (class,authoritative)); - -CREATE UNIQUE INDEX lower_class_unique ON location_class(lower(class)); - -INSERT INTO location_class(id,class,authoritative) VALUES ('1','Billing',TRUE); -INSERT INTO location_class(id,class,authoritative) VALUES ('2','Sales',TRUE); -INSERT INTO location_class(id,class,authoritative) VALUES ('3','Shipping',TRUE); - -SELECT SETVAL('location_class_id_seq',4); - -CREATE TABLE location ( - id serial PRIMARY KEY, - line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL, - line_two text, - line_three text, - city text check (city ~ '[[:alnum:]_]') NOT NULL, - state text check(state ~ '[[:alnum:]_]'), - country_id integer not null REFERENCES country(id), - mail_code text not null check (mail_code ~ '[[:alnum:]_]'), - created date not null default now(), - inactive_date timestamp default null, - active boolean not null default TRUE -); - -CREATE TABLE company ( - id serial UNIQUE, - entity_id integer not null references entity(id), - legal_name text check (legal_name ~ '[[:alnum:]_]'), - tax_id text, - created date default current_date not null, - PRIMARY KEY (entity_id,legal_name)); - -COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$; - -CREATE TABLE company_to_location ( - location_id integer references location(id) not null, - location_class integer not null references location_class(id), - company_id integer not null references company(id) ON DELETE CASCADE, - PRIMARY KEY(location_id,company_id, location_class)); - -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 salutation ( - id serial unique, - salutation text primary key); - -INSERT INTO salutation (id,salutation) VALUES ('1','Dr.'); -INSERT INTO salutation (id,salutation) VALUES ('2','Miss.'); -INSERT INTO salutation (id,salutation) VALUES ('3','Mr.'); -INSERT INTO salutation (id,salutation) VALUES ('4','Mrs.'); -INSERT INTO salutation (id,salutation) VALUES ('5','Ms.'); -INSERT INTO salutation (id,salutation) VALUES ('6','Sir.'); - -SELECT SETVAL('salutation_id_seq',7); - -CREATE TABLE person ( - id serial PRIMARY KEY, - entity_id integer references entity(id) not null, - salutation_id integer references salutation(id), - first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL, - middle_name text, - last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL, - created date not null default current_date - ); - -COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$; - -create table entity_employee ( - - person_id integer references person(id) not null, - entity_id integer references entity(id) not null unique, - startdate date not null default current_date, - enddate date, - role varchar(20), - ssn text, - sales bool default 'f', - manager_id integer references entity(id), - employeenumber varchar(32), - dob date, - PRIMARY KEY (person_id, entity_id) -); - -CREATE TABLE person_to_location ( - location_id integer not null references location(id), - location_class integer not null references location_class(id), - person_id integer not null references person(id) ON DELETE CASCADE, - PRIMARY KEY (location_id,person_id)); - -CREATE TABLE person_to_company ( - location_id integer references location(id) not null, - person_id integer not null references person(id) ON DELETE CASCADE, - company_id integer not null references company(id) ON DELETE CASCADE, - PRIMARY KEY (location_id,person_id)); - -CREATE TABLE entity_other_name ( - entity_id integer not null references entity(id) ON DELETE CASCADE, - other_name text check (other_name ~ '[[:alnum:]_]'), - PRIMARY KEY (other_name, entity_id)); - -COMMENT ON TABLE entity_other_name IS $$ Similar to company_other_name, a person may be jd, Joshua Drake, linuxpoet... all are the same person. $$; - -CREATE TABLE person_to_entity ( - person_id integer not null references person(id) ON DELETE CASCADE, - entity_id integer not null check (entity_id != person_id) references entity(id) ON DELETE CASCADE, - related_how text, - created date not null default current_date, - PRIMARY KEY (person_id,entity_id)); - -CREATE TABLE company_to_entity ( - company_id integer not null references company(id) ON DELETE CASCADE, - entity_id integer check (company_id != entity_id) not null references entity(id) ON DELETE CASCADE, - related_how text, - created date not null default current_date, - PRIMARY KEY (company_id,entity_id)); - -CREATE TABLE contact_class ( - id serial UNIQUE, - class text check (class ~ '[[:alnum:]_]') NOT NULL, - PRIMARY KEY (class)); - -CREATE UNIQUE INDEX contact_class_class_idx ON contact_class(lower(class)); - -INSERT INTO contact_class (id,class) values (1,'Primary Phone'); -INSERT INTO contact_class (id,class) values (2,'Secondary Phone'); -INSERT INTO contact_class (id,class) values (3,'Cell Phone'); -INSERT INTO contact_class (id,class) values (4,'AIM'); -INSERT INTO contact_class (id,class) values (5,'Yahoo'); -INSERT INTO contact_class (id,class) values (6,'Gtalk'); -INSERT INTO contact_class (id,class) values (7,'MSN'); -INSERT INTO contact_class (id,class) values (8,'IRC'); -INSERT INTO contact_class (id,class) values (9,'Fax'); -INSERT INTO contact_class (id,class) values (10,'Generic Jabber'); -INSERT INTO contact_class (id,class) values (11,'Home Phone'); -INSERT INTO contact_class (id,class) values (12,'Email'); - -SELECT SETVAL('contact_class_id_seq',12); - -CREATE TABLE person_to_contact ( - person_id integer not null references person(id) ON DELETE CASCADE, - contact_class_id integer references contact_class(id) not null, - contact text check(contact ~ '[[:alnum:]_]') not null, - PRIMARY KEY (person_id,contact_class_id,contact)); - -COMMENT ON TABLE person_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single individual $$; - -CREATE TABLE company_to_contact ( - company_id integer not null references company(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 (company_id, contact_class_id, contact)); - -COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$; - -CREATE TABLE entity_bank_account ( - id serial not null, - entity_id int not null references entity(id) ON DELETE CASCADE, - bic varchar, - iban varchar, - UNIQUE (id), - PRIMARY KEY (entity_id, bic, iban) -); - -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, - description text, - 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), - bank_account int references entity_bank_account(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.$$; - -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.$$; - -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 $$; - --- 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), - note text not null, vector tsvector not null, - created timestamp not null default now(), - created_by text DEFAULT SESSION_USER, - ref_key integer not null); - -CREATE TABLE entity_note(entity_id int references entity(id)) INHERITS (note); -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)); -CREATE INDEX entity_note_vectors_idx ON entity_note USING gist(vector); -CREATE TABLE invoice_note() INHERITS (note); -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 - --- -CREATE TABLE makemodel ( - parts_id int PRIMARY KEY, - make text, - model text -); --- -CREATE TABLE gl ( - id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id), - reference text, - description text, - transdate date DEFAULT current_date, - person_id integer references person(id), - notes text, - approved bool default true, - department_id int default 0 -); --- -CREATE TABLE gifi ( - accno text PRIMARY KEY, - description text -); --- -CREATE TABLE defaults ( +defaults ( setting_key text primary key, value text ); @@ -451,6 +29,7 @@ rcptnumber|1 paynumber|1 separate_duties|1 entity_control|A-00001 +batch_cc|B-11111 \. COMMENT ON TABLE defaults IS $$ diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 6e7ddc4d..175cac40 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -89,8 +89,12 @@ CREATE TYPE payment_invoice AS ( invnumber text, invoice_date date, amount numeric, + amount_fx numeric, discount numeric, - due numeric + discount_fx numeric, + due numeric, + due_fx numeric, + exchangerate numeric ); CREATE OR REPLACE FUNCTION payment_get_open_invoices @@ -109,10 +113,51 @@ BEGIN FOR payment_inv IN SELECT a.id AS invoice_id, a.invnumber AS invnumber, a.transdate AS invoice_date, a.amount AS amount, + a.amount/ + (CASE WHEN a.curr = (SELECT * from defaults_get_defaultcurrency()) + THEN 1 + ELSE + (CASE WHEN in_account_class =1 + THEN ex.buy + ELSE ex.sell END) + END) as amount_fx, (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate)) THEN 0 ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100 - END) AS discount, ac.due + END) AS discount, + (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate)) + THEN 0 + ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100 + END)/ + (CASE WHEN a.curr = (SELECT * from defaults_get_defaultcurrency()) + THEN 1 + ELSE + (CASE WHEN in_account_class =1 + THEN ex.buy + ELSE ex.sell END) + END) as discount_fx, + ac.due - (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate)) + THEN 0 + ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100 + END) AS due, + (ac.due - (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate)) + THEN 0 + ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100 + END))/ + (CASE WHEN a.curr = (SELECT * from defaults_get_defaultcurrency()) + THEN 1 + ELSE + (CASE WHEN in_account_class =1 + THEN ex.buy + ELSE ex.sell END) + END) AS due_fx, + (CASE WHEN a.curr = (SELECT * from defaults_get_defaultcurrency()) + THEN 1 + ELSE + (CASE WHEN in_account_class =1 + THEN ex.buy + ELSE ex.sell END) + END) AS exchangerate FROM (SELECT id, invnumber, transdate, amount, entity_id, 1 as invoice_class, paid, curr, entity_credit_account, department_id @@ -131,6 +176,7 @@ BEGIN FROM acc_trans GROUP BY trans_id, chart_id) ac ON (ac.trans_id = a.id) JOIN chart ON (chart.id = ac.chart_id) + LEFT JOIN exchangerate ex ON ( ex.transdate = a.transdate AND ex.curr = a.curr ) JOIN entity_credit_account c ON (c.id = a.entity_credit_account OR (a.entity_credit_account IS NULL and a.entity_id = c.entity_id)) WHERE ((chart.link = 'AP' AND in_account_class = 1) @@ -151,7 +197,7 @@ BEGIN AND (a.department_id = in_department_id OR in_department_id IS NULL) AND due <> 0 - GROUP BY a.invnumber, a.transdate, a.amount, discount, ac.due, a.id, c.discount_terms + GROUP BY a.invnumber, a.transdate, a.amount, amount_fx, discount, discount_fx, ac.due, a.id, c.discount_terms, ex.buy, ex.sell, a.curr LOOP RETURN NEXT payment_inv; END LOOP; @@ -506,7 +552,7 @@ CREATE TABLE payment ( payment_class integer NOT NULL, payment_date date default current_date, closed bool default FALSE, - entity_id integer references entity(id), + entity_credit_id integer references entity_credit_account(id), employee_id integer references entity_employee(entity_id), currency char(3), notes text, @@ -540,11 +586,10 @@ COMMENT ON TABLE payment_links IS $$ This reasoning is hacky and i hope it can dissapear when we get to 1.4 - D.M. $$; - CREATE OR REPLACE FUNCTION payment_post (in_datepaid date, in_account_class int, - in_entity_id int, + in_entity_credit_id int, in_curr char(3), in_notes text, in_department_id int, @@ -553,6 +598,7 @@ CREATE OR REPLACE FUNCTION payment_post in_amount numeric[], in_cash_approved bool[], in_source text[], + in_memo text[], in_transaction_id int[], in_op_amount numeric[], in_op_cash_account_id int[], @@ -570,21 +616,30 @@ DECLARE out_count int; DECLARE coa_id record; DECLARE var_employee int; DECLARE var_account_id int; +DECLARE default_currency char(3); +DECLARE current_exchangerate numeric; +DECLARE old_exchangerate numeric; +DECLARE tmp_amount numeric; BEGIN + + SELECT * INTO default_currency FROM defaults_get_defaultcurrency(); + SELECT * INTO current_exchangerate FROM currency_get_exchangerate(in_curr, in_datepaid, in_account_class); + + SELECT INTO var_employee entity_id FROM users WHERE username = SESSION_USER LIMIT 1; -- - -- SECOND WE HAVE TO INSERT THE PAYMENT, USING THE GL INFORMATION + -- WE HAVE TO INSERT THE PAYMENT, USING THE GL INFORMATION -- THE ID IS GENERATED BY payment_id_seq -- INSERT INTO payment (reference, payment_class, payment_date, - employee_id, currency, notes, department_id, entity_id) + employee_id, currency, notes, department_id, entity_credit_id) VALUES ((CASE WHEN in_account_class = 1 THEN setting_increment('rcptnumber') -- I FOUND THIS ON sql/modules/Settings.sql ELSE -- and it is very usefull setting_increment('paynumber') END), in_account_class, in_datepaid, var_employee, - in_curr, in_notes, in_department_id, in_entity_id); + in_curr, in_notes, in_department_id, in_entity_credit_id); SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS... @@ -597,39 +652,85 @@ BEGIN array_upper(in_cash_account_id, 1) LOOP INSERT INTO acc_trans (chart_id, amount, - trans_id, transdate, approved, source) + trans_id, transdate, approved, source, memo) VALUES (in_cash_account_id[out_count], - CASE WHEN in_account_class = 1 THEN in_amount[out_count] - ELSE in_amount[out_count]* - 1 + CASE WHEN in_account_class = 1 THEN in_amount[out_count]*current_exchangerate + ELSE (in_amount[out_count]*current_exchangerate)* - 1 END, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), - in_source[out_count]); + in_source[out_count], in_memo[out_count]); INSERT INTO payment_links VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1); + END LOOP; -- NOW LETS HANDLE THE AR/AP ACCOUNTS -- WE RECEIVED THE TRANSACTIONS_ID AND WE CAN OBTAIN THE ACCOUNT FROM THERE FOR out_count IN array_lower(in_transaction_id, 1) .. array_upper(in_transaction_id, 1) - LOOP - SELECT INTO var_account_id chart_id FROM acc_trans as ac + LOOP + SELECT INTO var_account_id chart_id FROM acc_trans as ac JOIN chart as c ON (c.id = ac.chart_id) WHERE trans_id = in_transaction_id[out_count] AND ( c.link = 'AP' OR c.link = 'AR' ); - INSERT INTO acc_trans (chart_id, amount, - trans_id, transdate, approved, source) + -- We need to know the exchangerate of this transaction + IF (current_exchangerate = 1 ) THEN + old_exchangerate := 1; + ELSIF (in_account_class = 1) THEN + SELECT buy INTO old_exchangerate + FROM exchangerate e + JOIN ap a on (a.transdate = e.transdate ) + WHERE a.id = in_transaction_id[out_count]; + ELSE + SELECT sell INTO old_exchangerate + FROM exchangerate e + JOIN ar a on (a.transdate = e.transdate ) + WHERE a.id = in_transaction_id[out_count]; + END IF; + -- Now we post the AP/AR transaction + INSERT INTO acc_trans (chart_id, amount, + trans_id, transdate, approved, source, memo) VALUES (var_account_id, - CASE WHEN in_account_class = 1 THEN in_amount[out_count] * -1 - ELSE in_amount[out_count] + CASE WHEN in_account_class = 1 THEN + + (in_amount[out_count]*old_exchangerate) * -1 + ELSE in_amount[out_count]*old_exchangerate END, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), - in_source[out_count]); + in_source[out_count], in_memo[out_count]); + -- Lets set the gain/loss, if tmp_amount equals zero then we dont need to post + -- any transaction + tmp_amount := in_amount[out_count]*current_exchangerate - in_amount[out_count]*old_exchangerate; + IF (tmp_amount < 0) THEN + IF (in_account_class = 1) THEN + INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source) + VALUES (CAST((select value from defaults where setting_key like 'fxloss_accno_id') AS INT), + tmp_amount, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), + in_source[out_count]); + ELSE + INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source) + VALUES (CAST((select value from defaults where setting_key like 'fxgain_accno_id') AS INT), + tmp_amount, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), + in_source[out_count]); + END IF; + ELSIF (tmp_amount > 0) THEN + IF (in_account_class = 1) THEN + INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source) + VALUES (CAST((select value from defaults where setting_key like 'fxgain_accno_id') AS INT), + tmp_amount, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), + in_source[out_count]); + ELSE + INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source) + VALUES (CAST((select value from defaults where setting_key like 'fxloss_accno_id') AS INT), + tmp_amount, in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), + in_source[out_count]); + END IF; + END IF; + -- Now we set the links INSERT INTO payment_links VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1); - - END LOOP; + END LOOP; -- -- WE NEED TO HANDLE THE OVERPAYMENTS NOW -- @@ -637,6 +738,7 @@ BEGIN -- FIRST WE HAVE TO MAKE THE GL TO HOLD THE OVERPAYMENT TRANSACTIONS -- THE ID IS GENERATED BY gl_id_seq -- + IF (array_upper(in_op_cash_account_id, 1) > 0) THEN INSERT INTO gl (reference, description, transdate, person_id, notes, approved, department_id) @@ -658,13 +760,13 @@ BEGIN array_upper(in_op_cash_account_id, 1) LOOP INSERT INTO acc_trans (chart_id, amount, - trans_id, transdate, approved, source) + trans_id, transdate, approved, source, memo) VALUES (in_op_cash_account_id[out_count], CASE WHEN in_account_class = 2 THEN in_op_amount[out_count] ELSE in_op_amount[out_count] * - 1 END, var_gl_id, in_datepaid, coalesce(in_approved, true), - in_op_source[out_count]); + in_op_source[out_count], in_op_memo[out_count]); INSERT INTO payment_links VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2); END LOOP; @@ -685,7 +787,7 @@ BEGIN VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2); END LOOP; END IF; - return 0; + return var_payment_id; END; $$ LANGUAGE PLPGSQL; -- I HAVE TO MAKE A COMMENT ON THIS FUNCTION @@ -752,8 +854,13 @@ RETURNS NUMERIC AS $$ DECLARE out_exrate exchangerate.buy%TYPE; - + default_currency char(3); + BEGIN + SELECT * INTO default_currency FROM defaults_get_defaultcurrency(); + IF default_currency = in_currency THEN + RETURN 1; + END IF; IF in_account_class = 1 THEN SELECT buy INTO out_exrate FROM exchangerate @@ -953,3 +1060,129 @@ BEGIN RETURN 1; END; $$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION payments_set_exchangerate(in_account_class int, + in_exchangerate numeric, in_curr char(3), in_datepaid date ) +RETURNS INT +AS $$ +DECLARE current_exrate exchangerate%ROWTYPE; +BEGIN +select * INTO current_exrate + FROM exchangerate + WHERE transdate = in_date; +IF current_exrate.transdate = in_date THEN + IF in_account_class = 1 THEN + UPDATE exchangerate set buy = in_exchangerate where transdate = in_date; + ELSE + UPDATE exchangerate set sell = in_exchangerate where transdate = in_date; + END IF; + RETURN 0; +ELSE + IF in_account_class = 1 THEN + INSERT INTO exchangerate (curr, transdate, buy) values (in_currency, in_date, in_exchangerate); + ELSE + INSERT INTO exchangerate (curr, transdate, sell) values (in_currency, in_date, in_exchangerate); + END IF; +RETURN 0; +END IF; +END; +$$ language plpgsql; + + +CREATE TYPE payment_header_item AS ( +payment_id int, +payment_reference int, +payment_date date, +legal_name text, +amount numeric, +employee_first_name text, +employee_last_name text, +currency char(3), +notes text +); +-- I NEED TO PLACE THE COMPANY TELEPHONE AND ALL THAT STUFF +CREATE OR REPLACE FUNCTION payment_gather_header_info(in_account_class int, in_payment_id int) + RETURNS SETOF payment_header_item AS + $$ + DECLARE out_payment payment_header_item; + BEGIN + FOR out_payment IN + SELECT p.id as payment_id, p.reference as payment_reference, p.payment_date, + c.legal_name as legal_name, am.amount as amount, em.first_name, em.last_name, p.currency, p.notes + FROM payment p + JOIN employee em ON (em.entity_id = p.employee_id) + JOIN company c ON (c.entity_id = p.entity_id) + JOIN ( SELECT sum(a.amount) as amount + FROM acc_trans a + JOIN chart c ON (a.chart_id = c.id) + JOIN payment_links pl ON (pl.entry_id=a.entry_id) + WHERE + ( ((c.link like '%AP_paid%' OR c.link like '%AP_discount%') AND in_account_class = 1) + OR ((c.link like '%AR_paid%' OR c.link like '%AR_discount%') AND in_account_class = 2)) + AND pl.payment_id = in_payment_id ) am ON (1=1) + WHERE p.id = in_payment_id + LOOP + RETURN NEXT out_payment; + END LOOP; + + END; + $$ language plpgsql; + + +COMMENT ON FUNCTION payment_gather_header_info(int,int) IS +$$ This function finds a payment based on the id and retrieves the record, +it is usefull for printing payments :) $$; + +CREATE TYPE payment_line_item AS ( + payment_id int, + entry_id int, + link_type int, + trans_id int, + invoice_number int, + chart_id int, + chart_accno int, + chart_description text, + chart_link text, + amount int, + trans_date date, + source text, + cleared bool, + fx_transaction bool, + project_id int, + memo text, + invoice_id int, + approved bool, + cleared_on date, + reconciled_on date +); + +CREATE OR REPLACE FUNCTION payment_gather_line_info(in_account_class int, in_payment_id int) + RETURNS SETOF payment_line_item AS + $$ + DECLARE out_payment_line payment_line_item; + BEGIN + FOR out_payment_line IN + SELECT pl.payment_id, ac.entry_id, pl.type as link_type, ac.trans_id, a.invnumber as invoice_number, + ac.chart_id, ch.accno as chart_accno, ch.description as chart_description, ch.link as chart_link, + ac.amount, ac.transdate as trans_date, ac.source, ac.cleared_on, ac.fx_transaction, ac.project_id, + ac.memo, ac.invoice_id, ac.approved, ac.cleared_on, ac.reconciled_on + FROM acc_trans ac + JOIN payment_links pl ON (pl.entry_id = ac.entry_id ) + JOIN chart ch ON (ch.id = ac.chart_id) + LEFT JOIN (SELECT id,invnumber + FROM ar WHERE in_account_class = 2 + UNION + SELECT id,invnumber + FROM ap WHERE in_account_class = 1 + ) a ON (ac.trans_id = a.id) + WHERE pl.payment_id = in_payment_id + LOOP + RETURN NEXT out_payment_line; + END LOOP; + END; + $$ language plpgsql; + +COMMENT ON FUNCTION payment_gather_line_info(int,int) IS +$$ This function finds a payment based on the id and retrieves all the line records, +it is usefull for printing payments and build reports :) $$; diff --git a/t/43-dbtest.t b/t/43-dbtest.t index 9cc9fd15..bb286d26 100644 --- a/t/43-dbtest.t +++ b/t/43-dbtest.t @@ -5,7 +5,7 @@ if (!defined $ENV{LSMB_TEST_DB}){ plan skip_all => 'Skipping all. Told not to test db.'; } else { - plan tests => 50; + plan tests => 51; if (defined $ENV{LSMB_NEW_DB}){ $ENV{PGDATABASE} = $ENV{LSMB_NEW_DB}; } diff --git a/templates/demo/check_base.tex b/templates/demo/check_base.tex index a93b87c7..6069eb6d 100644 --- a/templates/demo/check_base.tex +++ b/templates/demo/check_base.tex @@ -1,5 +1,6 @@ \usepackage{textpos} +\usepackage[top=1.5in, bottom=.25in, left=.25in, right=.25in]{geometry} \setlength{\TPHorizModule}{1in} \setlength{\TPVertModule}{1in} @@ -12,19 +13,19 @@ % %\vspace*{0.6cm} -\begin{textblock}{2}(6.75, 0.75) +\begin{textblock}{2}[0,1](6.75, 0.00) \end{textblock} -\begin{textblock}{5.5}(0.75, 1.12) +\begin{textblock}{5.5}[0,1](0.75, 0.5) \end{textblock} %\hfill \makebox[2cm]{\hfill} -\begin{textblock}{2}(6.6, 1.15) - +\begin{textblock}{2}[0,1](6.6, 0.5) + \end{textblock} -\begin{textblock}{7.25}(0, 1.5) +\begin{textblock}{7.25}[0,1](0, 0.75) \dotfill /100 \makebox[0.5cm]{\hfill} \end{textblock} \vspace{0.5cm} @@ -60,7 +61,7 @@ % -\begin{textblock}{7}(0, 3.75) +\begin{textblock}{7}(0, 4) \vspace{0.5cm} \noindent Master Code: \\ Date: \\ @@ -68,7 +69,7 @@ Description: \\ Total Amount: \\ \end{textblock} -\begin{textblock}{7}(0, 6.75) +\begin{textblock}{7}(0, 7) \vspace{0.5cm} \noindent Master Code: \\ Date: \\ -- cgit v1.2.3