From c26026c25ae610cf5332ed3c65eede1675225e73 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Sat, 17 Nov 2007 22:40:56 +0000 Subject: Commiting David Mora's payment code git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1871 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/DBObject/Payment.pm | 103 ++++++++++++++++- UI/payments/payment1.html | 124 +++++++++++++++++++++ UI/payments/payment2.html | 161 +++++++++++++++++++++++++++ scripts/payment.pl | 249 ++++++++++++++++++++++++++++++++++++++---- sql/Pg-database.sql | 5 +- sql/modules/Defaults.sql | 15 +++ sql/modules/Payment.sql | 65 ++++++++++- sql/modules/chart.sql | 23 ++++ 8 files changed, 710 insertions(+), 35 deletions(-) create mode 100644 UI/payments/payment1.html create mode 100644 UI/payments/payment2.html create mode 100644 sql/modules/Defaults.sql create mode 100644 sql/modules/chart.sql diff --git a/LedgerSMB/DBObject/Payment.pm b/LedgerSMB/DBObject/Payment.pm index f13a3d42..533a9cb8 100644 --- a/LedgerSMB/DBObject/Payment.pm +++ b/LedgerSMB/DBObject/Payment.pm @@ -32,7 +32,7 @@ our $VERSION = '0.1.0'; Inherited from LedgerSMB::DBObject. Please see that documnetation for details. -=item $oayment->get_open_accounts() +=item $payment->get_open_accounts() This function returns a list of open accounts depending on the $payment->{account_class} property. If this property is 1, it returns a list @@ -49,6 +49,17 @@ calculation. =cut +sub __validate__ { + my ($self) = shift @_; + #FIRST WE CHECK IF THE MAIN PROPERTY 'account_class' IS SET + if (!$self->{account_class}) { + $self->error("account_class must be set") + }; + #NOW WE SET THE CURRENT DATE + ($self->{current_date}) = $self->{dbh}->selectrow_array('select current_date'); +} + + sub get_open_accounts { my ($self) = @_; @{$self->{accounts}} = @@ -58,7 +69,7 @@ sub get_open_accounts { =over -=item $oayment->get_all_accounts() +=item $payment->get_all_accounts() This function returns a list of open or closed accounts depending on the $payment->{account_class} property. If this property is 1, it returns a list @@ -80,10 +91,10 @@ sub get_all_accounts { =over -=item $oayment->get_open_invoices() +=item $payment->get_open_invoices() This function returns a list of open invoices depending on the -$payment->{account_class}, $payment->{entity_id}, and $payment->{currency} +$payment->{account_class}, $payment->{entity_id}, and $payment->{curr} properties. Account classes follow the conventions above. This list is hence specific to a customer or vendor and currency as well. @@ -175,9 +186,8 @@ projects. The list is attached to $self->{projects} and returned. sub list_open_projects { my ($self) = @_; - my ($date) = $self->{dbh}->selectrow_array('select current_date'); @{$self->{projects}} = $self->call_procedure( - procname => 'project_list_open', args => [$date] + procname => 'project_list_open', args => [$self->{current_date}] ); return @{$self->{projects}}; } @@ -235,5 +245,86 @@ sub get_open_currencies { return @{$self->{openCurrencies}}; } +=item list_accounting + +This method lists all accounts that match the role specified in account_class property and +are availible to store the payment or receipts. +=back +=cut + +sub list_accounting { + my ($self) = @_; + @{$self->{pay_accounts}} = $self->exec_method( funcname => 'chart_list_cash'); + return @{$self->{pay_accounts}}; +} + +=item get_sources + +This method builds all the possible sources of money, +in the future it will look inside the DB. +=back + +=cut + +sub get_sources { + my ($self, $locale) = @_; + @{$self->{cash_sources}} = ($locale->text('cash'), + $locale->text('check'), + $locale->text('deposit'), + $locale->text('other')); + return @{$self->{cash_sources}}; +} + +=item get_exchange_rate(currency, date) + +This method gets the exchange rate for the specified currency and date + +=cut + +sub get_exchange_rate { + my ($self) = shift @_; + ($self->{currency}, $self->{date}) = @_; + ($self->{exchangerate}) = $self->exec_method(funcname => 'currency_get_exchangerate'); + return $self->{exchangerate}->{currency_get_exchangerate}; + +} + +=item get_default_currency + +This method gets the default currency +=back + +=cut + +sub get_default_currency { + my ($self) = shift @_; + ($self->{default_currency}) = $self->call_procedure(procname => 'defaults_get_defaultcurrency'); + return $self->{default_currency}->{defaults_get_defaultcurrency}; +} + +=item get_current_date + +This method returns the system's current date + +=cut + +sub get_current_date { + my ($self) = shift @_; + return $self->{current_date}; +} + +=item get_vc_info + +This method returns the contact informatino for a customer or vendor according to +$self->{account_class} + +=cut + +sub get_vc_info { + my ($self) = @_; + #@{$self->{vendor_customer_info}} = $self->call_procedure(procname => 'vendor_customer_info'); + #return @{$self->{vendor_customer_info}}; +} + 1; diff --git a/UI/payments/payment1.html b/UI/payments/payment1.html new file mode 100644 index 00000000..d064ad0e --- /dev/null +++ b/UI/payments/payment1.html @@ -0,0 +1,124 @@ + + + + <?lsmb titlebar ?> + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + + + + + + + + + + + + +
+ +
+ +
+ + + + + + + + + +
+ + + + +
>= + + <= + +
+ +
+ + + +
+ + diff --git a/UI/payments/payment2.html b/UI/payments/payment2.html new file mode 100644 index 00000000..502cb859 --- /dev/null +++ b/UI/payments/payment2.html @@ -0,0 +1,161 @@ + + + + <?lsmb titlebar ?> + + + + + + + + + +
+ + + + + + + +
+ + + + + +
+ + + + + + + + + + + + + +
+ + + + + + + +
+
+ 		+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
: + + +
: + + +
:
: + + + + + + +
+
+ + + + + + + + + + + + + + + + + + + + + + + +
+
">
"> +
+ + "> + + + + + "> + + + + + + +
">">
">">" type="checkbox" class="checkbox">
+
+ +
+
+ + + + +
+ + \ No newline at end of file diff --git a/scripts/payment.pl b/scripts/payment.pl index f36b0b91..ae476a9b 100644 --- a/scripts/payment.pl +++ b/scripts/payment.pl @@ -1,4 +1,3 @@ - =pod =head1 NAME @@ -66,14 +65,13 @@ TT2 system. (hopefully it will... ) sub payment { my ($request) = @_; my $locale = $request->{_locale}; - my $templateData; my $dbPayment = LedgerSMB::DBObject::Payment->new({'base' => $request}); # Lets get the project data... my @projectOptions; my @arrayOptions = $dbPayment->list_open_projects(); push @projectOptions, {}; #A blank field on the select box for my $ref (0 .. $#arrayOptions) { - push @projectOptions, { value => $arrayOptions[$ref]->{id}, + push @projectOptions, { value => $arrayOptions[$ref]->{id}."--".$arrayOptions[$ref]->{projectnumber}."--".$arrayOptions[$ref]->{description}, text => $arrayOptions[$ref]->{projectnumber}."--".$arrayOptions[$ref]->{description}}; } # Lets get the departments data... @@ -82,30 +80,32 @@ sub payment { @arrayOptions = $dbPayment->list_departments($role); push @departmentOptions, {}; # A blank field on the select box for my $ref (0 .. $#arrayOptions) { - push @departmentOptions, { value => $arrayOptions[$ref]->{id}, + push @departmentOptions, { value => $arrayOptions[$ref]->{id}."--".$arrayOptions[$ref]->{description}, text => $arrayOptions[$ref]->{description}}; } # Lets get the customer or vendor :) my @vcOptions; - $dbPayment->{account_class} = $request->{type} eq 'receipt' ? 2 : 1; @arrayOptions = $dbPayment->get_open_accounts(); for my $ref (0 .. $#arrayOptions) { - push @vcOptions, { value => $arrayOptions[$ref]->{id}, - text => $arrayOptions[$ref]->{description}}; + push @vcOptions, { value => $arrayOptions[$ref]->{id}.'--'.$arrayOptions[$ref]->{name}, + text => $arrayOptions[$ref]->{name}}; } # Lets get the open currencies (this uses the $dbPayment->{account_class} property) my @currOptions; @arrayOptions = $dbPayment->get_open_currencies(); for my $ref (0 .. $#arrayOptions) { - push @arrayOptions, { value => $arrayOptions[$ref]->{id}, - text => $arrayOptions[$ref]->{description}}; + push @currOptions, { value => $arrayOptions[$ref]->{payments_get_open_currencies}, + text => $arrayOptions[$ref]->{payments_get_open_currencies} }; } # Lets build filter by period my $date = LedgerSMB::DBObject::Date->new({base => $request}); $date->build_filter_by_period($locale); + # Lets set the data in a hash for the template system. :) my $select = { stylesheet => $request->{_user}->{stylesheet}, + login => { name => 'login', + value => $request->{_user}->{login} }, projects => { name => 'projects', options => \@projectOptions @@ -114,8 +114,8 @@ my $select = { name => 'department', options => \@departmentOptions }, - customer => { - name => 'customer', + vendor_customer => { + name => 'vendor-customer', options => \@vcOptions }, curr => { @@ -132,27 +132,24 @@ my $select = { }, interval_radios => $date->{radioOptions}, amountfrom => { - type => 'text', name => 'amountfrom', - size => '10', - maxlength => '10' - }, + }, amountto => { - type => 'text', name => 'amountto', - size => '10', - maxlength => '10' }, - sort => { - type => 'hidden', - value => 'sort_value' + accountclass => { + name => 'account_class', + value => $dbPayment->{account_class} + }, + type => { + name => 'type', + value => $request->{type} }, action => { name => 'action', - value => 'continue', + value => 'payment2', text => $locale->text("Continue"), }, - }; # Lets call upon the template system my $template; @@ -166,4 +163,210 @@ my $template; $template->render($select);# And finally, Lets print the screen :) } +=pod + +=item payment2 + +This method is used for the payment module, it is a consecuence of the payment sub, +and its used for all the mechanics of an invoices payment module. + +=back + +=cut + +sub payment2 { +my ($request) = @_; +my $locale = $request->{_locale}; +my $dbPayment = LedgerSMB::DBObject::Payment->new({'base' => $request}); + +my @array_options; +# LETS GET THE CUSTOMER/VENDOR INFORMATION + ($dbPayment->{entity_id}, my $vendor_customer_name) = split /--/ , $request->{'vendor-customer'}; + +my @array_options; +my $exchangerate; + +# LETS BUILD THE PROJECTS INFO +# I DONT KNOW IF I NEED ALL THIS, BUT AS IT IS AVAILABLE I'LL STORE IT FOR LATER USAGE. +my ($project_id, $project_number, $project_name) = split /--/ , $request->{projects} ; +my @project = { name => 'project', text => $project_number.' '.$project_name, value => $project_id }; +# LETS GET THE DEPARTMENT INFO +my ($department_id, $department_name) = split /--/, $request->{department}; +my @department = { name => 'department', text => $department_name, value => $department_id }; +# LETS GET ALL THE ACCOUNTS +my @account_options; +@array_options = $dbPayment->list_accounting(); +for my $ref (0 .. $#array_options) { + push @account_options, { value => $array_options[$ref]->{id}, + text => $array_options[$ref]->{description}}; +} +# LETS GET THE POSSIBLE SOURCES +my @sources_options; +@array_options = $dbPayment->get_sources(\%$locale); +for my $ref (0 .. $#array_options) { + push @sources_options, { value => $array_options[$ref], + text => $array_options[$ref]}; +} +# WE MUST PREPARE THE ENTITY INFORMATION + @array_options = $dbPayment->get_vc_info(); +# LETS BUILD THE CURRENCIES INFORMATION +# FIRST, WE NEED TO KNOW THE DEFAULT CURRENCY +my $default_currency = $dbPayment->get_default_currency(); +my @currency_options; +# LETS BUILD THE COLUMN HEADERS WE ALWAYS NEED +# THE OTHER HEADERS WILL BE BUILT IF THE RIGHT CONDITIONS ARE MET. +# ----------------------------------------------- +# SOME USERS WONT USE MULTIPLE CURRENCIES, AND WONT LIKE THE FACT CURRENCY BEING +# ON THE SCREEN ALL THE TIME, SO IF THEY ARE USING THE DEFAULT CURRENCY WE WONT PRINT IT +my $currency_text = $request->{curr} eq $default_currency ? '' : '('.$request->{curr}.')'; +my $default_currency_text = $currency_text ? '('.$default_currency.')' : ''; + +my @columnAS = ({text => $locale->text('Invoice')}, + {text => $locale->text('Date')}, + {text => $locale->text('Total').$default_currency_text}, + {text => $locale->text('Paid').$default_currency_text}, + {text => $locale->text('Amount Due').$default_currency_text}, + {text => $locale->text('To pay').$default_currency_text} + ); + +my @column_headers = ({text => $locale->text('Invoice')}, + {text => $locale->text('Date')}, + {text => $locale->text('Total').$default_currency_text}, + {text => $locale->text('Paid').$default_currency_text}, + {text => $locale->text('Amount Due').$default_currency_text}, + {text => $locale->text('To pay').$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[5,6,7] = @column_headers[6,7,5]; + # DOES THE CURRENCY IN USE HAS AN EXCHANGE RATE?, IF SO + # WE MUST SET THE VALUE, OTHERWISE THE UI WILL HANDLE IT + $exchangerate = $dbPayment->get_exchange_rate($request->{curr}, $dbPayment->{current_date}); + if ($exchangerate) { + @currency_options = { + name => 'date_curr', + value => "$exchangerate", #THERE IS A STRANGE BEHAVIOUR WITH THIS, + text => "$exchangerate" #IF I DONT USE THE DOUBLE QUOTES, IT WILL PRINT THE ADDRESS + #THERE MUST BE A REASON FOR THIS, I MUST RETURN TO IT LATER + }; + } else { + @currency_options = { + name => 'date_curr'}; + } + + } else { + # WE MUST SET EXCHANGERATE TO 1 FOR THE MATHS SINCE WE + # ARE USING THE DEFAULT CURRENCY + $exchangerate = 1; + } +# WE NEED TO QUERY THE DATABASE TO CHECK FOR OPEN INVOICES +# IF WE DONT HAVE ANY INVOICES MATCHING THE FILTER PARAMETERS, WE WILL WARN THE USER AND STOP +# THE PROCCESS. +my @invoice_data; +@array_options = $dbPayment->get_open_invoices(); +if (!$array_options[0]->{invoice_id}) { + $request->error($locale->text("Nothing to do")); +} +for my $ref (0 .. $#array_options) { + push @invoice_data, { invoice => { number => $array_options[$ref]->{invnumber}, + href => 'ar.pl?id='."$array_options[$ref]->{invoice_id}" + }, + invoice_date => "$array_options[$ref]->{invoice_date}", + amount => "$array_options[$ref]->{amount}", + due => "$array_options[$ref]->{due}", + paid => "$array_options[$ref]->{amount}" - "$array_options[$ref]->{due}", + exchange_rate => "$exchangerate", + due_fx => "$exchangerate"? "$array_options[$ref]->{due}"/"$exchangerate" : 'N/A', + topay => "$array_options[$ref]->{due}", + topay_fx => { name => "topay_fx_$ref", + value => "$exchangerate" ? "$array_options[$ref]->{due}"/"$exchangerate" : 'N/A' + } + + }; +} +# LETS BUILD THE SELECTION FOR THE UI +my $select = { + stylesheet => $request->{_user}->{stylesheet}, + header => { text => $request->{type} eq 'receipt' ? $locale->text('Receipt') : $locale->text('Payment') }, + project => @project, + department => @department, + account => { + name => 'account', + options => \@account_options}, + datepaid => { + name => 'datepaid', + value => $dbPayment->{current_date} + }, + source => { + name => 'source', + options => \@sources_options + }, + source_text => { + + name => 'source_text', + }, + + defaultcurrency => { + text => $default_currency + }, + curr => { + text => $request->{curr} + }, + column_headers => \@column_headers, + rows => \@invoice_data, + + vc => { name => $vendor_customer_name, + address => [ {text => 'Crra 83 #32 -1'}, + {text => '442 6464'}, + {text => 'Medellín'}, + {text => 'Colombia'}]}, + + post => { + accesskey => 'O', + title => 'POST ALT+O', + name => 'action', + value => 'post', + text => "POST" + }, + post_and_print => { + accesskey => 'R', + title => 'POST AND PRINT ALT+R', + name => 'action', + value => 'post_and_print', + text => "POST AND PRINT" + }, + format => { + name => 'FORMAT', + options => [ + {value => 1, text => "HTML" }, + {value => 2, text => "PDF" }, + {value => 3, text => "POSTSCRIPT" } + ], + }, + media => { + name => 'MEDIA', + options => [ + {value => 1, text => "Screen" }, + {value => 2, text => "PRINTER" }, + {value => 3, text => "EMAIL" } + ], + }, + date_curr => @currency_options # I HAVE TO PUT THIS LAST, BECAUSE IT CAN BE NULL + # THIS IS AN UGLY HACK THAT MUST BE FIXED. +}; +my $template = LedgerSMB::Template->new( + user => $request->{_user}, + locale => $request->{_locale}, + path => 'UI', + template => 'payment2', + format => 'HTML' ); +eval {$template->render($select) }; +if ($@) { $request->error("$@"); } +} + 1; diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 27c61a4e..ed32224d 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -1935,7 +1935,7 @@ CREATE TABLE menu_attribute ( -- Name: menu_attribute_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb -- -SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 536, true); +SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 550, true); -- @@ -2021,7 +2021,7 @@ COPY menu_attribute (node_id, attribute, value, id) FROM stdin; 34 action add 81 34 db vendor 82 35 menu 1 83 -36 module cp.pl 84 +36 module payment.pl 84 36 action payment 85 36 type receipt 86 37 module cp.pl 87 @@ -2462,6 +2462,7 @@ COPY menu_attribute (node_id, attribute, value, id) FROM stdin; 194 type credit_note 548 195 type credit_invoice 542 197 type debit_invoice 546 +36 account_class 1 550 \. diff --git a/sql/modules/Defaults.sql b/sql/modules/Defaults.sql new file mode 100644 index 00000000..62597faf --- /dev/null +++ b/sql/modules/Defaults.sql @@ -0,0 +1,15 @@ +-- Probably want to move this to the Settings module + +CREATE OR REPLACE FUNCTION defaults_get_defaultcurrency() +RETURNS SETOF char(3) AS +$$ +DECLARE defaultcurrency defaults.value%TYPE; + BEGIN + SELECT INTO defaultcurrency substr(value,1,3) + FROM defaults + WHERE setting_key = 'curr'; + RETURN NEXT defaultcurrency; + END; +$$ language plpgsql; +COMMENT ON FUNCTION defaults_get_defaultcurrency() IS +$$ This function return the default currency asigned by the program. $$; diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index f6332099..7f2d16e7 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -57,7 +57,7 @@ CREATE TYPE payment_invoice AS ( ); CREATE OR REPLACE FUNCTION payment_get_open_invoices -(in_account_class int, in_entity_id int, in_currency char(3)) +(in_account_class int, in_entity_id int, in_curr char(3)) RETURNS SETOF payment_invoice AS $$ DECLARE payment_inv payment_invoice; @@ -87,7 +87,7 @@ BEGIN JOIN entity_credit_account c USING (entity_id) WHERE a.invoice_class = in_account_class AND c.entity_class = in_account_class - AND a.curr = in_currency + AND a.curr = in_curr LOOP RETURN NEXT payment_inv; END LOOP; @@ -272,17 +272,20 @@ comment on function department_list(in_role char) is $$ This function returns all department that match the role provided as the argument.$$; -CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int)returns setof char(3) AS +CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int) +RETURNS SETOF char(3) AS $$ DECLARE resultrow record; BEGIN FOR resultrow IN SELECT curr FROM ar WHERE amount <> paid - AND in_account_class=2 + OR paid IS NULL + AND in_account_class=2 UNION SELECT curr FROM ap WHERE amount <> paid + OR paid IS NULL AND in_account_class=1 ORDER BY curr LOOP @@ -290,3 +293,57 @@ BEGIN END LOOP; END; $$ language plpgsql; + +CREATE OR REPLACE FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) +RETURNS NUMERIC AS +$$ +DECLARE + out_exrate exchangerate.buy%TYPE; + + BEGIN + IF in_account_class = 1 THEN + SELECT INTO out_exrate buy + FROM exchangerate + WHERE transdate = in_date AND curr = in_currency; + ELSE + SELECT INTO out_exrate sell + FROM exchangerate + WHERE transdate = in_date AND curr = in_currency; + END IF; + RETURN out_exrate; + END; +$$ language plpgsql; +COMMENT ON FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) IS +$$ This function return the exchange rate of a given currency, date and exchange rate class (buy or sell). $$; + + +CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int) +RETURNS SETOF entity AS +$$ +DECLARE + out_info entity%ROWTYPE; + + BEGIN + FOR out_info IN + + SELECT e.id, e.name FROM entity e + JOIN company c ON (e.id = c.entity_id) + WHERE e.id = in_entity_id + + --SELECT e.id, c.legal_name, l.line_one, l.city_province, cy.name FROM entity e + --JOIN company c ON (e.id = c.entity_id) + --JOIN company_to_location cl ON (c.id = cl.company_id) + --JOIN location l ON (l.id = cl.location_id) + --JOIN country cy ON (cy.id = l.country_id) + LOOP + return next out_info; + END LOOP; + + IF NOT FOUND THEN + RAISE EXCEPTION 'ID % not found!!!!!', in_entity_id; + END IF; + + END; +$$ language plpgsql; +COMMENT ON FUNCTION payment_get_vc_info(in_entity_id int) IS +$$ This function return vendor or customer info, its under construction $$; \ No newline at end of file diff --git a/sql/modules/chart.sql b/sql/modules/chart.sql new file mode 100644 index 00000000..11e2e38b --- /dev/null +++ b/sql/modules/chart.sql @@ -0,0 +1,23 @@ +CREATE OR REPLACE FUNCTION chart_list_cash(in_account_class int) +RETURNS SETOF chart AS +$$ +DECLARE resultrow record; + link_string text; +BEGIN + IF in_account_class = 1 THEN + link_string := '%AR_paid%'; + ELSE + link_string := '%AP_paid%'; + END IF; + + FOR resultrow IN + SELECT * FROM chart + WHERE link LIKE link_string + ORDER BY accno + LOOP + return next resultrow; + END LOOP; +END; +$$ language plpgsql; +COMMENT ON FUNCTION chart_list_cash(in_account_class int) IS +$$ This function returns the cash account acording with in_account_class which must be 1 or 2 $$; -- cgit v1.2.3