From 025fd80e331af9a44552efd5be8ae35b4825e7f6 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Fri, 4 Jul 2008 23:04:16 +0000 Subject: Committing David Mora's single payment updates git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2185 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/AM.pm | 8 +- LedgerSMB/DBObject/Payment.pm | 21 +++ LedgerSMB/Template.pm | 23 ++- LedgerSMB/Template/LaTeX.pm | 6 +- UI/am-account-form.html | 20 ++- UI/payments/payment1.html | 19 ++- UI/payments/payment2.html | 51 +++++-- bin/am.pl | 4 +- bin/arapprn.pl | 8 +- scripts/payment.pl | 294 +++++++++++++++++++++++++++++------ sql/Pg-database.sql | 35 +---- sql/modules/Payment.sql | 345 ++++++++++++++++++++++++++++-------------- 12 files changed, 604 insertions(+), 230 deletions(-) diff --git a/LedgerSMB/AM.pm b/LedgerSMB/AM.pm index e1ff4043..d502bc20 100644 --- a/LedgerSMB/AM.pm +++ b/LedgerSMB/AM.pm @@ -159,10 +159,10 @@ sub save_account { $form->{link} = ""; foreach my $item ( $form->{AR}, $form->{AR_amount}, $form->{AR_tax}, $form->{AR_overpayment}, - $form->{AR_paid}, $form->{AP}, $form->{AP_amount}, $form->{AP_overpayment}, - $form->{AP_tax}, $form->{AP_paid}, $form->{IC}, - $form->{IC_income}, $form->{IC_sale}, $form->{IC_expense}, - $form->{IC_cogs}, $form->{IC_taxpart}, $form->{IC_taxservice} + $form->{AR_discount}, $form->{AR_paid}, $form->{AP}, $form->{AP_amount}, + $form->{AP_overpayment}, $form->{AP_discount}, $form->{AP_tax}, + $form->{AP_paid}, $form->{IC}, $form->{IC_income}, $form->{IC_sale}, + $form->{IC_expense}, $form->{IC_cogs}, $form->{IC_taxpart}, $form->{IC_taxservice} ) { $form->{link} .= "${item}:" if ($item); diff --git a/LedgerSMB/DBObject/Payment.pm b/LedgerSMB/DBObject/Payment.pm index 02f3aad5..6cc23f2e 100644 --- a/LedgerSMB/DBObject/Payment.pm +++ b/LedgerSMB/DBObject/Payment.pm @@ -105,6 +105,16 @@ sub get_open_accounts { return @{$self->{accounts}}; } + +sub get_entity_credit_account{ + my ($self) = @_; + @{$self->{entity_accounts}} = + $self->exec_method(funcname => 'payment_get_entity_accounts'); + return @{$self->{entity_accounts}}; + +} + + =over =item $payment->get_all_accounts() @@ -499,4 +509,15 @@ sub check_job { ($self->{job}) = $self->exec_method(funcname => 'job__status'); } +=item post_payment + +This method uses payment_post to store a payment (not a bulk payment) on the database. + +=cut + +sub post_payment { + my ($self) = @_; + $self->exec_method(funcname => 'payment_post'); + $self->{dbh}->commit(); +} 1; diff --git a/LedgerSMB/Template.pm b/LedgerSMB/Template.pm index c7386928..e87251fd 100755 --- a/LedgerSMB/Template.pm +++ b/LedgerSMB/Template.pm @@ -263,8 +263,8 @@ sub render { #return $format->can('postprocess')->($self); my $post = $format->can('postprocess')->($self); if (!$self->{'noauto'}) { - $self->output; # Clean up + $self->output; if ($self->{rendered}) { unlink($self->{rendered}) or throw Error::Simple 'Unable to delete output file'; @@ -276,15 +276,17 @@ sub render { sub output { my $self = shift; my %args = @_; + $self->{output_args} = \%args; my $method = $self->{method} || $args{method} || $args{media}; - if ('email' eq lc $method) { $self->_email_output; } elsif ('print' eq lc $method) { $self->_lpr_output; - } elsif (defined $self->{output}) { + } elsif (defined $self->{output} or $method = 'Screen') { $self->_http_output; exit; + } elsif (defined $method) { + $self->_lpr_output; } else { $self->_http_output_file; } @@ -376,15 +378,22 @@ sub _email_output { } sub _lpr_output { - my ($self) = shift; + my ($self, $in_args) = shift; my $args = $self->{output_args}; - if ($self->{format} != /(pdf|ps)/){ + if ($self->{format} ne 'LaTeX') { throw Error::Simple "Invalid Format"; } - my $lpr = $LedgerSMB::Sysconfig::printer{$args->{printer}}; + my $lpr = $LedgerSMB::Sysconfig::printer{$args->{media}}; open(LPR, '|-', $lpr); - print LPR $self->{output}; + + # Output is not defined here. In the future we should consider + # changing this to use the system command and hit the file as an arg. + # -- CT + open (FILE, '<', "$self->{rendered}"); + while (my $line = ){ + print LPR $line; + } close(LPR); } diff --git a/LedgerSMB/Template/LaTeX.pm b/LedgerSMB/Template/LaTeX.pm index 973cb724..ea04bb80 100755 --- a/LedgerSMB/Template/LaTeX.pm +++ b/LedgerSMB/Template/LaTeX.pm @@ -77,8 +77,10 @@ sub preprocess { $vars = $rawvars; } #XXX Fix escaping - $vars =~ s/([&\$\\_<>~^#\%\{\}])/\\$1/g; - $vars =~ s/"(.*)"/``$1''/gs; + if (defined $vars){ + $vars =~ s/([&\$\\_<>~^#\%\{\}])/\\$1/g; + $vars =~ s/"(.*)"/``$1''/gs; + } } else { for ( keys %{$rawvars} ) { $vars->{$_} = preprocess($rawvars->{$_}); diff --git a/UI/am-account-form.html b/UI/am-account-form.html index 64bffb8b..ec37f9b2 100644 --- a/UI/am-account-form.html +++ b/UI/am-account-form.html @@ -153,7 +153,13 @@ type => 'checkbox', label => text('Overpayment'), ${form.AR_overpayment} => form.AR_overpayment, - value => 'AR_overpayment'} ?> + value => 'AR_overpayment'} ?>
+ 'AR_discount', + type => 'checkbox', + label => text('Discount'), + ${form.AR_discount} => form.AR_discount, + value => 'AR_discount'} ?> @@ -180,9 +186,15 @@ type => 'checkbox', label => text('Overpayment'), ${form.AP_overpayment} => form.AP_overpayment, - value => 'AP_overpayment'} ?> + value => 'AP_overpayment'} ?>
+ 'AP_discount', + type => 'checkbox', + label => text('Discount'), + ${form.AP_overpayment} => form.AP_discount, + value => 'AP_discount'} ?> - + 'IC_sale', type => 'checkbox', @@ -202,7 +214,7 @@ ${form.IC_taxpart} => form.IC_taxpart, value => 'IC_taxpart'} ?> - + 'IC_income', type => 'checkbox', diff --git a/UI/payments/payment1.html b/UI/payments/payment1.html index 6d2d2216..1df90592 100644 --- a/UI/payments/payment1.html +++ b/UI/payments/payment1.html @@ -37,15 +37,22 @@ - - - - - + + + + - + + + + + + + + + diff --git a/UI/payments/payment2.html b/UI/payments/payment2.html index 335fc778..e2824349 100644 --- a/UI/payments/payment2.html +++ b/UI/payments/payment2.html @@ -19,10 +19,16 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif', - + + + - + +
+ + +
@@ -136,6 +142,7 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
+ @@ -150,6 +157,7 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif', + @@ -157,7 +165,7 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif', - - - - - - + @@ -214,12 +218,14 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
">
 @@ -202,11 +210,7 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
 
- + + + @@ -234,11 +240,22 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif', -- + + + + - + - +
X
+ -- + + + + + @@ -260,6 +277,13 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif', + +
 
 
@@ -293,13 +318,13 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif', diff --git a/bin/am.pl b/bin/am.pl index b30b4fe2..f92e380e 100644 --- a/bin/am.pl +++ b/bin/am.pl @@ -195,7 +195,7 @@ sub save_account { ) if length $a > 2; for ( - qw(AR_amount AR_tax AR_paid AR_overpayment AP_amount AP_tax AP_paid AP_overpayment IC_taxpart IC_taxservice IC_sale IC_cogs IC_income IC_expense) + qw(AR_amount AR_tax AR_paid AR_overpayment AR_discount AP_amount AP_tax AP_paid AP_overpayment AP_discount IC_taxpart IC_taxservice IC_sale IC_cogs IC_income IC_expense) ) { $form->error( @@ -208,7 +208,7 @@ sub save_account { foreach $item ( "AR", "AP" ) { $i = 0; - for ( "${item}_amount", "${item}_paid", "${item}_tax", "${item}_overpayment" ) { + for ( "${item}_amount", "${item}_paid", "${item}_tax", "${item}_overpayment", "${item}_discount" ) { $i++ if $form->{$_}; } $form->error( diff --git a/bin/arapprn.pl b/bin/arapprn.pl index 3b733004..2cc37f46 100644 --- a/bin/arapprn.pl +++ b/bin/arapprn.pl @@ -289,10 +289,13 @@ sub print_check { my $template = LedgerSMB::Template->new( user => \%myconfig, template => $form->{'formname'}, - format => uc $form->{'format'} ); + format => uc $form->{'format'}, + no_auto_output => 1, + output_args => $form, + ); try { $template->render($form); - $template->output(%{$form}); + $template->output($form); } catch Error::Simple with { my $E = shift; @@ -557,6 +560,7 @@ sub print_transaction { my $template = LedgerSMB::Template->new( user => \%myconfig, template => $form->{'formname'}, + no_auto_output => 1, format => uc $form->{format} ); try { $template->render($form); diff --git a/scripts/payment.pl b/scripts/payment.pl index d50eb15b..ed83b9c8 100644 --- a/scripts/payment.pl +++ b/scripts/payment.pl @@ -274,7 +274,7 @@ sub display_payments { =item payment This method is used to set the filter screen and prints it, using the -TT2 system. (hopefully it will... ) +TT2 system. =back @@ -284,7 +284,6 @@ sub payment { my ($request) = @_; my $locale = $request->{_locale}; my $dbPayment = LedgerSMB::DBObject::Payment->new({'base' => $request}); - # Lets get the project data... my @projectOptions; my @arrayOptions = $dbPayment->list_open_projects(); @@ -303,15 +302,7 @@ sub payment { push @departmentOptions, { value => $arrayOptions[$ref]->{id}."--".$arrayOptions[$ref]->{description}, text => $arrayOptions[$ref]->{description}}; } - -# Lets get the customer or vendor :) - my @vcOptions; - @arrayOptions = $dbPayment->get_open_accounts(); - for my $ref (0 .. $#arrayOptions) { - 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) +# Lets get the currencies (this uses the $dbPayment->{account_class} property) my @currOptions; @arrayOptions = $dbPayment->get_open_currencies(); for my $ref (0 .. $#arrayOptions) { @@ -321,7 +312,6 @@ sub payment { # 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}, @@ -335,10 +325,6 @@ my $select = { name => 'department', options => \@departmentOptions }, - vendor_customer => { - name => 'vendor-customer', - options => \@vcOptions - }, curr => { name => 'curr', options => \@currOptions @@ -368,20 +354,89 @@ my $select = { }, action => { name => 'action', - value => 'payment2', + value => 'payment1_5', text => $locale->text("Continue"), - }, + } }; -# Lets call upon the template system -my $template; - $template = LedgerSMB::Template->new( - user => $request->{_user}, - locale => $request->{_locale}, - path => 'UI/payments', - template => 'payment1', - format => 'HTML', ); -$template->render($select);# And finally, Lets print the screen :) + my $template; + $template = LedgerSMB::Template->new( + user => $request->{_user}, + locale => $request->{_locale}, + path => 'UI/payments', + template => 'payment1', + format => 'HTML' ); + $template->render($select);# And finally, Lets print the screen :) +} + + +=pod + +=item payment1_5 + +This method is called between payment and payment2, it will search the database +for entity_credit_accounts that match the parameter, if only one is found it will +run unnoticed by the user, if more than one is found it will ask the user to pick +one to handle the payment against + +=back + +=cut + +sub payment1_5 { +my ($request) = @_; +my $locale = $request->{_locale}; +my $dbPayment = LedgerSMB::DBObject::Payment->new({'base' => $request}); +my @array_options = $dbPayment->get_entity_credit_account(); + if ($#array_options == -1) { + &payment($request); + } elsif ($#array_options == 0) { + $request->{'vendor-customer'} = $array_options[0]->{id}.'--'.$array_options[0]->{name}; + &payment2($request); + } else { + # Lets call upon the template system + + my @company_options; + for my $ref (0 .. $#array_options) { + push @company_options, { id => $array_options[$ref]->{id}, + name => $array_options[$ref]->{name}}; + } + my $select = { + companies => \@company_options, + stylesheet => $request->{_user}->{stylesheet}, + login => { name => 'login', + value => $request->{_user}->{login}}, + department => { name => 'department', + value => $request->{department}}, + currency => { name => 'curr', + value => $request->{curr}}, + datefrom => { name => 'datefrom', + value => $request->{datefrom}}, + dateto => { name => 'dateto', + value => $request->{dateto}}, + amountfrom => { name => 'amountfrom', + value => $request->{datefrom}}, + amountto => { name => 'amountto', + value => $request->{dateto}}, + accountclass => { name => 'account_class', + value => $dbPayment->{account_class}}, + type => { name => 'type', + value => $request->{type}}, + action => { name => 'action', + value => 'payment2', + text => $locale->text("Continue")} + }; + my $template; + $template = LedgerSMB::Template->new( + user => $request->{_user}, + locale => $request->{_locale}, + path => 'UI/payments', + template => 'payment1_5', + format => 'HTML' ); + eval {$template->render($select) }; + if ($@) { $request->error("$@"); } # PRINT ERRORS ON THE UI + } + } =pod @@ -409,14 +464,12 @@ my @array_options; my @currency_options; my $exchangerate; # LETS GET THE CUSTOMER/VENDOR INFORMATION -# TODO TODO TODO TODO TODO TODO TODO -($Payment->{entity_id}, $Payment->{company_name}) = split /--/ , $request->{'vendor-customer'}; +($Payment->{entity_credit_id}, $Payment->{company_name}) = split /--/ , $request->{'vendor-customer'}; + # WE NEED TO RETRIEVE A BILLING LOCATION, THIS IS HARDCODED FOR NOW... Should we change it? $Payment->{location_class_id} = '1'; -#$request->error($Payment->{entity_id}); my @vc_options; @vc_options = $Payment->get_vc_info(); -# TODO TODO TODO TODO TODO TODO TODO # 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. if ($request->{projects}) { @@ -450,6 +503,7 @@ 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('Discount').$default_currency_text}, {text => $locale->text('Amount Due').$default_currency_text}, {text => $locale->text('To pay').$default_currency_text} ); @@ -461,7 +515,7 @@ my @column_headers = ({text => $locale->text('Invoice')}, {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]; + @column_headers[6,7,8] = @column_headers[7,8,6]; # 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} ? @@ -500,10 +554,12 @@ my @topay_state; # WE WILL USE THIS TO HELP UI TO DETERMINE WHAT IS VISIBLE for my $ref (0 .. $#array_options) { if ( !$request->{"checkbox_$array_options[$ref]->{invoice_id}"}) { +# SHOULD I APPLY DISCCOUNTS? + # LETS SET THE EXCHANGERATE VALUES my $due_fx; my $topay_fx_value; if ("$exchangerate") { - $topay_fx_value = $due_fx = "$array_options[$ref]->{due}"/"$exchangerate"; + $topay_fx_value = $due_fx = "$array_options[$ref]->{due}"/"$exchangerate" - "$array_options[$ref]->{discount}"/"$exchangerate"; } else { $topay_fx_value = $due_fx = "N/A"; } @@ -513,11 +569,12 @@ for my $ref (0 .. $#array_options) { }, invoice_date => "$array_options[$ref]->{invoice_date}", amount => "$array_options[$ref]->{amount}", - due => "$array_options[$ref]->{due}", + due => "$array_options[$ref]->{due}" - "$array_options[$ref]->{discount}", paid => "$array_options[$ref]->{amount}" - "$array_options[$ref]->{due}", + discount => "$array_options[$ref]->{discount}", exchange_rate => "$exchangerate", due_fx => $due_fx, # This was set at the begining of the for statement - topay => "$array_options[$ref]->{due}", + 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}"}, @@ -555,24 +612,46 @@ for (my $i=1 ; $i <= $request->{overpayment_qty}; $i++) { if ( $request->{"overpayment_topay_$i"} ) { # Now we split the account selected options my ($id, $accno, $description) = split(/--/, $request->{"overpayment_account_$i"}); + my ($cashid, $cashaccno, $cashdescription ) = split(/--/, $request->{"overpayment_cash_account_$i"}); push @overpayment, {amount => $request->{"overpayment_topay_$i"}, source1 => $request->{"overpayment_source1_$i"}, source2 => $request->{"overpayment_source2_$i"}, + memo => $request->{"overpayment_memo_$i"}, account => { id => $id, accno => $accno, description => $description - } + }, + cashaccount => { id => $cashid, + accno => $cashaccno, + description => $cashdescription + } }; } else { $i = $request->{overpayment_qty} + 1; } } +} +# We need to set the availible media and format from printing +my @media_options; +push @media_options, {value => 1, text => "Screen"}; +if ($#{LedgerSMB::Sysconfig::printer}) { + for (keys %{LedgerSMB::Sysconfig::printer}) { + push @media_options, {value => 1, text => $_}; + } } +#$request->error("@media_options"); +my @format_options; +push @format_options, {value => 1, text => "HTML"}; +if (${LedgerSMB::Sysconfig::latex}) { + push @format_options, {value => 2, text => "PDF" }, {value => 3, text => "POSTSCRIPT" }; +} # 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') }, + type => { name => 'type', + value => $request->{type} }, login => { name => 'login', value => $request->{_user}->{login} }, accountclass => { @@ -615,19 +694,11 @@ my $select = { }, format => { name => 'FORMAT', - options => [ - {value => 1, text => "HTML" }, - {value => 2, text => "PDF" }, - {value => 3, text => "POSTSCRIPT" } - ], - }, - media => { + options => \@format_options + }, + media => { name => 'MEDIA', - options => [ - {value => 1, text => "Screen" }, - {value => 2, text => "PRINTER" }, - {value => 3, text => "EMAIL" } - ], + options => \@media_options }, exrate => @currency_options, selectedcheckboxes => @selected_checkboxes ? \@selected_checkboxes : '', @@ -646,5 +717,132 @@ eval {$template->render($select) }; } +=pod + +=item post_payment + +This method is used for the payment module (not the bulk payment), +and its used for all the mechanics of storing a payment. + +=back + +=cut + +sub post_payment { +my ($request) = @_; +my $locale = $request->{_locale}; +my $Payment = LedgerSMB::DBObject::Payment->new({'base' => $request}); +# LETS GET THE CUSTOMER/VENDOR INFORMATION +($Payment->{entity_id}, $Payment->{company_name}) = split /--/ , $request->{'vendor-customer'}; +# LETS GET THE DEPARTMENT INFO +# WE HAVE TO SET $dbPayment->{department_id} in order to process +if ($request->{department}) { + $request->{department} =~ /^(\d+)--*/; + $Payment->{department_id} = $1; +} +# +# We want to set a gl_description, +# since we are using two tables there is no need to use doubled information, +# we could specify this gl is the result of a payment movement... +# +$Payment->{gl_description} = $locale->text('This gl movement, is the result of a payment transaction'); +# +# Im not sure what this is for... gotta comment this later +$Payment->{approved} = 'true'; +# +# We have to setup a lot of things before we can process the payment +# they are related to payment_post sql function, so if you have any doubts +# look there. +#------------------------------------------------------------------------- +# +# Variable definition +# +# We use the prefix op to refer to the overpayment variables. +my $overpayment; # This variable might be fuzzy, we are using it to handle invalid data + # i.e. a user set an overpayment qty inside an invoice. +my @array_options; +my @amount; +my @cash_account_id; +my @source; +my @transaction_id; +my @op_amount; +my @op_cash_account_id; +my @op_source; +my @op_memo; +my @op_account_id; +# +# We need the invoices in order to process the income data, this is done this way +# since the data we have isn't indexed in any way. +# + +@array_options = $Payment->get_open_invoices(); +for my $ref (0 .. $#array_options) { + if ( !$request->{"checkbox_$array_options[$ref]->{invoice_id}"}) { + # + # 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. + # + # Maybe i should move this to another sub, so i can call it from payment2 as well :). D.M. + if ($array_options[$ref]->{amount} < $request->{"topay_$array_options[$ref]->{invoice_id}"} ) { + # THERE IS AN OVERPAYMENT!, we should store it and see if we can use it on the UI + $overpayment = $overpayment + $request->{"topay_$array_options[$ref]->{invoice_id}"} - $array_options[$ref]->{amount}; + $request->{"topay_$array_options[$ref]->{invoice_id}"} = $request->{"topay_$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 @transaction_id, $array_options[$ref]->{invoice_id}; + } +} +# +# Now we need the overpayment information. +# +# We will use the prefix op to indicate it is an overpayment information. +# +# note: I love the for's C-like syntax. + +for (my $i=1 ; $i <= $request->{overpayment_qty}; $i++) { + if (!$request->{"overpayment_checkbox_$i"}) { # Is overpayment marked as deleted ? + if ( $request->{"overpayment_topay_$i"} ) { # Is this overpayment an used field? + # Now we split the account selected options, using the namespace the if statement + # provides for us. + $request->{"overpayment_account_$i"} =~ /^(\d+)--*/; + my $id = $1; + $request->{"overpayment_cash_account_$i"} =~ /^(\d+)--*/; + my $cashid = $1; + push @op_amount, $request->{"overpayment_topay_$i"}; + push @op_cash_account_id, $cashid; + push @op_source, $request->{"overpayment_source1_$i"}.' '.$request->{"overpayment_source2_$i"}; + push @op_memo, $request->{"overpayment_memo_$i"}; + push @op_account_id, $id; + } + } +} + +# Finally we store all the data inside the LedgerSMB::DBObject::Payment object. + $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->{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... + $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', + format => 'HTML' ); + eval {$template->render($select) }; + if ($@) { $request->error("$@"); } # PRINT ERRORS ON THE UI + +} + eval { do "scripts/custom/payment.pl"}; 1; diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index ca907d50..ef6c4de2 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -359,7 +359,12 @@ glnumber|1 projectnumber|1 queue_payments|0 poll_frequency|1 +<<<<<<< .mine +rcptnumber|1 +paynumber|1 +======= separate_duties|1 +>>>>>>> .r2141 \. COMMENT ON TABLE defaults IS $$ @@ -469,6 +474,7 @@ CREATE TABLE entity_credit_account ( 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, @@ -738,35 +744,6 @@ CREATE TABLE ap ( ); COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$; - --- Payment stuff by David Mora - -CREATE TABLE payment ( - id serial primary key, - reference text NOT NULL, - payment_class integer NOT NULL, - amount numeric NOT NULL, - payment_date date default current_date, - closed bool default FALSE, - person_id integer references person(id), - currency char(3), - notes text, - department_id integer default 0); - -COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, etc... $$; -COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment orders, it will be differentiate by payment_class $$; -COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$; -CREATE INDEX payment_id_idx ON Payment(id); - -CREATE TABLE payment_links ( - payment_id integer references Payment(id), - transaction_id integer NOT NULL, - amount numeric); -COMMENT ON TABLE payment_links IS $$ This table will link payment to ar/ap transactions $$; -COMMENT ON COLUMN payment_links.transaction_id IS $$ This column lacks some data integrity controls that must be implemented in the future $$; - - - -- CREATE TABLE taxmodule ( taxmodule_id serial PRIMARY KEY, diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 2ab6cf69..87cca5ca 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -1,3 +1,24 @@ +CREATE OR REPLACE FUNCTION payment_get_entity_accounts +(in_account_class int, + in_vc_name text, + in_vc_idn int) + returns SETOF entity AS + $$ + DECLARE out_entity entity%ROWTYPE; + BEGIN + FOR out_entity IN + SELECT ec.id, cp.legal_name as name, e.entity_class, e.created + FROM entity e + JOIN entity_credit_account ec ON (ec.entity_id = e.id) + JOIN company cp ON (cp.entity_id = e.id) + WHERE ec.entity_class = in_account_class + AND (cp.legal_name ilike coalesce('%'||in_vc_name||'%','%%') OR cp.tax_id = in_vc_idn) + LOOP + RETURN NEXT out_entity; + END LOOP; + END; + $$ LANGUAGE PLPGSQL; + -- payment_get_open_accounts and the option to get all accounts need to be -- refactored and redesigned. -- CT CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int) @@ -11,15 +32,15 @@ BEGIN JOIN entity_credit_account ec ON (ec.entity_id = e.id) JOIN company cp ON (cp.entity_id = e.id) WHERE ec.entity_class = in_account_class - AND CASE WHEN in_account_class = 1 THEN - e.id IN (SELECT entity_id FROM ap - WHERE amount <> paid - GROUP BY entity_id) - WHEN in_account_class = 2 THEN - e.id IN (SELECT entity_id FROM ar - WHERE amount <> paid - GROUP BY entity_id) - END + -- AND CASE WHEN in_account_class = 1 THEN + -- e.id IN (SELECT entity_id FROM ap + -- WHERE amount <> paid + -- GROUP BY entity_id) + -- WHEN in_account_class = 2 THEN + -- e.id IN (SELECT entity_id FROM ar + -- WHERE amount <> paid + -- GROUP BY entity_id) + -- END LOOP RETURN NEXT out_entity; END LOOP; @@ -74,55 +95,58 @@ RETURNS SETOF payment_invoice AS $$ DECLARE payment_inv payment_invoice; BEGIN - FOR payment_inv IN - SELECT a.id AS invoice_id, a.invnumber, - a.transdate AS invoice_date, a.amount, - CASE WHEN discount_terms - > extract('days' FROM age(a.transdate)) - THEN 0 - ELSE (a.amount - a.paid) * c.discount / 100 - END AS discount, - a.amount - a.paid - - CASE WHEN discount_terms - > extract('days' FROM age(a.transdate)) - THEN 0 - ELSE (a.amount - a.paid) * c.discount / 100 - END - AS due - FROM (SELECT id, invnumber, transdate, amount, entity_id, + FOR payment_inv IN + SELECT a.id AS invoice_id, a.invnumber AS invnumber, + a.transdate AS invoice_date, a.amount AS amount, + (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 + FROM (SELECT id, invnumber, transdate, amount, entity_id, 1 as invoice_class, paid, curr, entity_credit_account, department_id FROM ap UNION - SELECT id, invnumber, transdate, amount, entity_id, + SELECT id, invnumber, transdate, amount, entity_id, 2 AS invoice_class, paid, curr, entity_credit_account, department_id - FROM ar - ) a - 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 a.invoice_class = in_account_class - AND c.entity_class = in_account_class - AND a.amount - a.paid <> 0 - AND a.curr = in_curr - AND (a.transdate >= in_datefrom - OR in_datefrom IS NULL) - AND (a.transdate <= in_dateto - OR in_dateto IS NULL) - AND (a.amount >= in_amountfrom - OR in_amountfrom IS NULL) - AND (a.amount <= in_amountto - OR in_amountto IS NULL) - AND (a.department_id = in_department_id - OR in_department_id IS NULL) - + FROM ar + ) a + JOIN (SELECT trans_id, chart_id, sum(CASE WHEN in_account_class = 1 THEN amount + WHEN in_account_class = 2 + THEN amount * -1 + END) as due + FROM acc_trans + GROUP BY trans_id, chart_id) ac ON (ac.trans_id = a.id) + JOIN chart ON (chart.id = ac.chart_id) + 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) + OR (chart.link = 'AR' AND in_account_class = 2)) + AND a.invoice_class = in_account_class + AND c.entity_class = in_account_class + AND c.id = in_entity_credit_id + AND a.amount - a.paid <> 0 + AND a.curr = in_curr + AND (a.transdate >= in_datefrom + OR in_datefrom IS NULL) + AND (a.transdate <= in_dateto + OR in_dateto IS NULL) + AND (a.amount >= in_amountfrom + OR in_amountfrom IS NULL) + AND (a.amount <= in_amountto + OR in_amountto IS NULL) + AND (a.department_id = in_department_id + OR in_department_id IS NULL) + GROUP BY a.invnumber, a.transdate, a.amount, discount, ac.due, a.id, c.discount_terms LOOP RETURN NEXT payment_inv; END LOOP; END; $$ LANGUAGE PLPGSQL; + + COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3), date, date, numeric, numeric, int) IS $$ This function takes three arguments: Type: 1 for vendor, 2 for customer @@ -426,101 +450,201 @@ sub-array, the first element is the (integer) transaction id, and the second is the amount for that transaction. If the total of the amounts do not add up to in_total, then an error is generated. $$; +-- +-- WE NEED A PAYMENT TABLE +-- +CREATE TABLE payment ( + id serial primary key, + reference text NOT NULL, + gl_id integer references gl(id), + payment_class integer NOT NULL, + payment_date date default current_date, + closed bool default FALSE, + entity_id integer references entity(id), + employee_id integer references entity_employee(entity_id), + currency char(3), + notes text, + department_id integer default 0); + +COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, et$$; +COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment order $$; +COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$; +COMMENT ON COLUMN payment.gl_id IS $$ A payment should always be linked to a GL movement $$; +CREATE INDEX payment_id_idx ON payment(id); + +CREATE TABLE payment_links ( + payment_id integer references Payment(id), + entry_id integer references acc_trans(entry_id), + type integer); +COMMENT ON TABLE payment_links IS $$ + An explanation to the type field. + * A type 0 means the link is referencing an ar/ap and was created + using an overpayment movement after the receipt was created + * A type 1 means the link is referencing an ar/ap and was made + on the payment creation, its not the product of an overpayment movement + * A type 2 means the link is not referencing an ar/ap and its the product + of the overpayment logic + + With this ideas in order we can do the following + + To get the payment amount we will sum the entries with type > 0. + To get the linked amount we will sum the entries with type < 2. + The overpayment account can be obtained from the entries with type = 2. + + 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_payment_date date, - in_account_class int, - in_person_id int, - in_currency char(3), - in_notes text, - in_department int, - in_gl_description text, - in_cash_accno int[], - in_cash_amount int[], - in_cash_approved bool[], - in_cash_source text[], - in_accno int[], - in_amount int[], - in_approved bool[], - in_source text[], - in_transaction_id int[], - in_type int[], - in_approved bool) +(in_datepaid date, + in_account_class int, + in_entity_id int, + in_curr char(3), + in_notes text, + in_department_id int, + in_gl_description text, + in_cash_account_id int[], + in_amount numeric[], + in_cash_approved bool[], + in_source text[], + in_transaction_id int[], + in_op_amount numeric[], + in_op_cash_account_id int[], + in_op_source text[], + in_op_memo text[], + in_op_account_id int[], + in_approved bool) RETURNS INT AS $$ DECLARE var_payment_id int; DECLARE var_gl_id int; +DECLARE var_entry record; DECLARE var_entry_id int[]; DECLARE out_count int; - +DECLARE coa_id record; +DECLARE var_employee int; +DECLARE var_account_id int; BEGIN - -- FIRST WE HAVE TO INSERT THE PAYMENT + 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 -- THE ID IS GENERATED BY payment_id_seq -- INSERT INTO payment (reference, payment_class, payment_date, - person_id, currency, notes, department_id) + employee_id, currency, notes, department_id, entity_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_payment_date, in_person_id, - in_currency, in_notes, in_department); + in_account_class, in_datepaid, var_employee, + in_curr, in_notes, in_department_id, in_entity_id); SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table - -- SECOND WE HAVE TO MAKE THE GL TO HOLD THE TRANSACTIONS - -- THE ID IS GENERATED BY gl_id_seq - -- - INSERT INTO gl (reference, description, transdate, - person_id, notes, approved, department_id) - VALUES (setting_increment('glnumber'), - in_gl_description, in_payment_date, in_person_id, - in_notes, in_department, coalesce(in_approved, true)); - SELECT currval('id') INTO var_gl_id; -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT + -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS... -- -- FIRST WE SHOULD INSERT THE CASH ACCOUNTS -- -- WE SHOULD HAVE THE DATA STORED AS (ACCNO, AMOUNT), SO FOR out_count IN - array_lower(in_cash_accno, 1) .. - array_upper(in_cash_accno, 1) + array_lower(in_cash_account_id, 1) .. + array_upper(in_cash_account_id, 1) LOOP INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source) - VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno[out_count]), - CASE WHEN in_account_class = 2 THEN in_cash_amount[out_count] * -1 - ELSE in_cash_amount[out_count] + VALUES (in_cash_account_id[out_count], + CASE WHEN in_account_class = 2 THEN in_amount[out_count] + ELSE in_amount[out_count]* - 1 END, - var_gl_id, in_payment_date, coalesce(in_cash_approved[1], true), - in_cash_source[out_count]); - --SELECT currval('acc_trans_entry_id_seq') INTO var_entry_id[out_count];--WE'LL NEED THIS FOR THE PAYMENT_LINK + in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), + in_source[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/OVERPAYMENT ACCOUNT - -- - FOR var_count IN - array_lower(in_accno, 1) .. - array_upper(in_accno, 1) + -- 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 - INSERT INTO acc_trans (chart_id, amount, - trans_id, transdate, approved, source) - VALUES ((SELECT id FROM chart WHERE accno = in_accno[out_count]), + 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) + VALUES (var_account_id, CASE WHEN in_account_class = 2 THEN in_amount[out_count] * -1 ELSE in_amount[out_count] END, - var_gl_id, in_payment_date, coalesce(in_approved[1], true), + in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), in_source[out_count]); - -- - -- WE WILL INSERT THE LINK INTO PAYMENT_LINKS NOW - -- - INSERT INTO payment_links - VALUES (var_payment_id, currval(acc_trans_entry_id_seq), - in_transaction_id[out_count], in_type[var_count]); + INSERT INTO payment_links + VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1); + END LOOP; - return 0; +-- +-- WE NEED TO HANDLE THE OVERPAYMENTS NOW +-- + -- + -- 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) + VALUES (setting_increment('glnumber'), + in_gl_description, in_datepaid, var_employee, + in_notes, in_approved, in_department_id); + SELECT currval('id') INTO var_gl_id; +-- +-- WE NEED TO SET THE GL_ID FIELD ON PAYMENT'S TABLE +-- + UPDATE payment SET gl_id = var_gl_id + WHERE id = var_payment_id; + -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS... + -- + -- FIRST WE SHOULD INSERT THE OVERPAYMENT CASH ACCOUNTS + -- + FOR out_count IN + array_lower(in_op_cash_account_id, 1) .. + array_upper(in_op_cash_account_id, 1) + LOOP + INSERT INTO acc_trans (chart_id, amount, + trans_id, transdate, approved, source) + 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]); + INSERT INTO payment_links + VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2); + END LOOP; + -- NOW LETS HANDLE THE OVERPAYMENT ACCOUNTS + FOR out_count IN + array_lower(in_op_account_id, 1) .. + array_upper(in_op_account_id, 1) + LOOP + INSERT INTO acc_trans (chart_id, amount, + trans_id, transdate, approved, source, memo) + VALUES (in_op_account_id[out_count], + CASE WHEN in_account_class = 2 THEN in_op_amount[out_count] * -1 + ELSE in_op_amount[out_count] + END, + var_gl_id, in_datepaid, coalesce(in_approved, true), + 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; + END IF; + return 0; END; $$ LANGUAGE PLPGSQL; -- I HAVE TO MAKE A COMMENT ON THIS FUNCTION + -- Move this to the projects module when we start on that. CT CREATE OR REPLACE FUNCTION project_list_open(in_date date) RETURNS SETOF project AS @@ -568,15 +692,9 @@ $$ DECLARE resultrow record; BEGIN FOR resultrow IN - SELECT curr AS curr FROM ar - WHERE amount <> paid - OR paid IS NULL - AND in_account_class=2 + SELECT DISTINCT curr FROM ar UNION - SELECT curr FROM ap - WHERE amount <> paid - OR paid IS NULL - AND in_account_class=1 + SELECT DISTINCT curr FROM ap ORDER BY curr LOOP return next resultrow.curr; @@ -627,7 +745,7 @@ CREATE TYPE payment_location_result AS ( -- payment_get_vc_info has the same arch as company__list_locations, except for the filtering capabilities -- This should be unified on the API when we get things working - David Mora -- -CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int, in_location_class_id int) +CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_credit_id int, in_location_class_id int) RETURNS SETOF payment_location_result AS $$ DECLARE out_row RECORD; @@ -640,7 +758,8 @@ DECLARE out_row RECORD; JOIN company cp ON (ctl.company_id = cp.id) JOIN location_class lc ON (ctl.location_class = lc.id) JOIN country c ON (c.id = l.country_id) - WHERE cp.entity_id = in_entity_id AND + JOIN entity_credit_account ec ON (ec.entity_id = cp.entity_id) + WHERE ec.id = in_entity_credit_id AND lc.id = in_location_class_id ORDER BY lc.id, l.id, c.name LOOP -- cgit v1.2.3