From df073d6e09c0f87fb2e88cc215ace843a5851d4a Mon Sep 17 00:00:00 2001 From: einhverfr Date: Thu, 12 Apr 2007 20:46:20 +0000 Subject: Formatting with Perltidy git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/branches/1.2@1068 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/AA.pm | 1592 +++++---- LedgerSMB/AM.pm | 1938 +++++----- LedgerSMB/BP.pm | 516 +-- LedgerSMB/CA.pm | 596 ++-- LedgerSMB/CP.pm | 936 +++-- LedgerSMB/CT.pm | 1417 ++++---- LedgerSMB/CreditCard.pm | 11 +- LedgerSMB/CreditCard/Config.pm | 4 +- LedgerSMB/CreditCard/TrustCommerce.pm | 96 +- LedgerSMB/CreditCard/TrustCommerce/Config.pm | 9 +- LedgerSMB/Form.pm | 4407 ++++++++++++----------- LedgerSMB/GL.pm | 728 ++-- LedgerSMB/HR.pm | 316 +- LedgerSMB/IC.pm | 2654 +++++++------- LedgerSMB/IR.pm | 1981 +++++------ LedgerSMB/IS.pm | 3326 +++++++++--------- LedgerSMB/Inifile.pm | 95 +- LedgerSMB/JC.pm | 827 ++--- LedgerSMB/Locale.pm | 192 +- LedgerSMB/Log.pm | 50 +- LedgerSMB/Mailer.pm | 152 +- LedgerSMB/Menu.pm | 149 +- LedgerSMB/Num2text.pm | 1904 +++++----- LedgerSMB/OE.pm | 4268 +++++++++++----------- LedgerSMB/OP.pm | 136 +- LedgerSMB/PE.pm | 2118 ++++++----- LedgerSMB/PriceMatrix.pm | 254 +- LedgerSMB/RC.pm | 589 ++-- LedgerSMB/RP.pm | 2930 +++++++-------- LedgerSMB/Session.pm | 14 +- LedgerSMB/Session/DB.pm | 375 +- LedgerSMB/Sysconfig.pm | 96 +- LedgerSMB/Tax.pm | 137 +- LedgerSMB/Taxes/Simple.pm | 48 +- LedgerSMB/User.pm | 1293 +++---- admin.pl | 47 +- bin/aa.pl | 2742 +++++++++------ bin/admin.pl | 1327 +++---- bin/am.pl | 3863 +++++++++++--------- bin/ap.pl | 9 +- bin/ar.pl | 9 +- bin/arap.pl | 1160 +++--- bin/arapprn.pl | 1071 +++--- bin/bp.pl | 724 ++-- bin/ca.pl | 638 ++-- bin/cp.pl | 2046 ++++++----- bin/ct.pl | 3613 ++++++++++--------- bin/gl.pl | 1661 +++++---- bin/hr.pl | 1520 ++++---- bin/ic.pl | 4884 +++++++++++++++----------- bin/io.pl | 2489 +++++++------ bin/ir.pl | 1448 ++++---- bin/is.pl | 1507 ++++---- bin/jc.pl | 2774 ++++++++------- bin/login.pl | 354 +- bin/menu.pl | 373 +- bin/oe.pl | 4620 +++++++++++++----------- bin/pe.pl | 3145 +++++++++-------- bin/pos.pl | 1433 ++++---- bin/ps.pl | 21 +- bin/pw.pl | 41 +- bin/rc.pl | 985 +++--- bin/rp.pl | 3290 +++++++++-------- common.pl | 133 +- custom.pl | 2 +- doc/release_notes | 6 - drivers/pd3000.pl | 40 +- import_members.pl | 334 +- login.pl | 23 +- menu.pl | 151 +- net-setup.pl | 1082 +++--- pos.conf.pl | 65 +- sql/Pg-database.sql | 2 +- upgrade-templates.pl | 27 +- utils/cli/ledgersmb_cli.pl | 132 +- utils/devel/find-use | 36 +- utils/notify_short/config.pl | 8 +- utils/notify_short/listener.pl | 46 +- utils/pos/directnet.pl | 17 +- 79 files changed, 46020 insertions(+), 40032 deletions(-) diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm index dc6f6279..be1ac8eb 100755 --- a/LedgerSMB/AA.pm +++ b/LedgerSMB/AA.pm @@ -1,5 +1,5 @@ #===================================================================== -# LedgerSMB +# LedgerSMB # Small Medium Business Accounting software # http://www.ledgersmb.org/ # Copyright (C) 2006 @@ -20,7 +20,7 @@ # # Contributors: # -# +# # See COPYRIGHT file for copyright information #====================================================================== # @@ -38,237 +38,258 @@ use LedgerSMB::Sysconfig; sub post_transaction { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->{dbh}; - - my $query; - my $sth; - - my $null; - ($null, $form->{department_id}) = split(/--/, $form->{department}); - $form->{department_id} *= 1; - - my $ml = 1; - my $table = 'ar'; - my $buysell = 'buy'; - my $ARAP = 'AR'; - my $invnumber = "sinumber"; - my $keepcleared; - - if ($form->{vc} eq 'vendor') { - $table = 'ap'; - $buysell = 'sell'; - $ARAP = 'AP'; - $ml = -1; - $invnumber = "vinumber"; - } - - if ($form->{currency} eq $form->{defaultcurrency}) { - $form->{exchangerate} = 1; - } else { - $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, $buysell); - - $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate}); - } - - my @taxaccounts = split / /, $form->{taxaccounts}; - my $tax = 0; - my $fxtax = 0; - my $amount; - my $diff; - - my %tax = (); - my $accno; - - # add taxes - foreach $accno (@taxaccounts) { - $fxtax += $tax{fxamount}{$accno} = $form->parse_amount($myconfig, $form->{"tax_$accno"}); - $tax += $tax{fxamount}{$accno}; - - push @{ $form->{acc_trans}{taxes} }, { - accno => $accno, - amount => $tax{fxamount}{$accno}, - project_id => undef, - fx_transaction => 0 }; - - $amount = $tax{fxamount}{$accno} * $form->{exchangerate}; - $tax{amount}{$accno} = $form->round_amount($amount - $diff, 2); - $diff = $tax{amount}{$accno} - ($amount - $diff); - $amount = $tax{amount}{$accno} - $tax{fxamount}{$accno}; - $tax += $amount; - - if ($form->{currency} ne $form->{defaultcurrency}) { - push @{ $form->{acc_trans}{taxes} }, { - accno => $accno, - amount => $amount, - project_id => undef, - fx_transaction => 1 }; - } - - } - - my %amount = (); - my $fxinvamount = 0; - for (1 .. $form->{rowcount}) { - $fxinvamount += $amount{fxamount}{$_} = $form->parse_amount($myconfig, $form->{"amount_$_"}) - } - - $form->{taxincluded} *= 1; - - my $i; - my $project_id; - my $cleared = 0; - - $diff = 0; - # deduct tax from amounts if tax included - for $i (1 .. $form->{rowcount}) { - - if ($amount{fxamount}{$i}) { - - if ($form->{taxincluded}) { - $amount = ($fxinvamount) ? $fxtax * $amount{fxamount}{$i} / $fxinvamount : 0; - $amount{fxamount}{$i} -= $amount; - } - - # multiply by exchangerate - $amount = $amount{fxamount}{$i} * $form->{exchangerate}; - $amount{amount}{$i} = $form->round_amount($amount - $diff, 2); - $diff = $amount{amount}{$i} - ($amount - $diff); - - ($null, $project_id) = split /--/, $form->{"projectnumber_$i"}; - $project_id ||= undef; - ($accno) = split /--/, $form->{"${ARAP}_amount_$i"}; - - if ($keepcleared) { - $cleared = ($form->{"cleared_$i"}) ? 1 : 0; - } - - push @{ $form->{acc_trans}{lineitems} }, { - accno => $accno, - amount => $amount{fxamount}{$i}, - project_id => $project_id, - description => $form->{"description_$i"}, - cleared => $cleared, - fx_transaction => 0 }; - - if ($form->{currency} ne $form->{defaultcurrency}) { - $amount = $amount{amount}{$i} - $amount{fxamount}{$i}; - push @{ $form->{acc_trans}{lineitems} }, { - accno => $accno, - amount => $amount, - project_id => $project_id, - description => $form->{"description_$i"}, - cleared => $cleared, - fx_transaction => 1 }; - } - } - } - - - my $invnetamount = 0; - for (@{ $form->{acc_trans}{lineitems} }) { $invnetamount += $_->{amount} } - my $invamount = $invnetamount + $tax; - - # adjust paidaccounts if there is no date in the last row - $form->{paidaccounts}-- - unless ($form->{"datepaid_$form->{paidaccounts}"}); - - if ($form->{vc} ne "customer"){ - $form->{vc} = "vendor"; - } - - my $paid = 0; - my $fxamount; - - $diff = 0; - # add payments - for $i (1 .. $form->{paidaccounts}) { - $fxamount = $form->parse_amount($myconfig, $form->{"paid_$i"}); - - if ($fxamount) { - $paid += $fxamount; - - $paidamount = $fxamount * $form->{exchangerate}; - - $amount = $form->round_amount($paidamount - $diff, 2); - $diff = $amount - ($paidamount - $diff); - - $form->{datepaid} = $form->{"datepaid_$i"}; - - $paid{fxamount}{$i} = $fxamount; - $paid{amount}{$i} = $amount; - } - } - - $fxinvamount += $fxtax unless $form->{taxincluded}; - $fxinvamount = $form->round_amount($fxinvamount, 2); - $invamount = $form->round_amount($invamount, 2); - $paid = $form->round_amount($paid, 2); - - $paid = ($fxinvamount == $paid) - ? $invamount - : $form->round_amount($paid * $form->{exchangerate}, 2); - - - $query = q| + my ( $self, $myconfig, $form ) = @_; + + # connect to database + my $dbh = $form->{dbh}; + + my $query; + my $sth; + + my $null; + ( $null, $form->{department_id} ) = split( /--/, $form->{department} ); + $form->{department_id} *= 1; + + my $ml = 1; + my $table = 'ar'; + my $buysell = 'buy'; + my $ARAP = 'AR'; + my $invnumber = "sinumber"; + my $keepcleared; + + if ( $form->{vc} eq 'vendor' ) { + $table = 'ap'; + $buysell = 'sell'; + $ARAP = 'AP'; + $ml = -1; + $invnumber = "vinumber"; + } + + if ( $form->{currency} eq $form->{defaultcurrency} ) { + $form->{exchangerate} = 1; + } + else { + $exchangerate = + $form->check_exchangerate( $myconfig, $form->{currency}, + $form->{transdate}, $buysell ); + + $form->{exchangerate} = + ($exchangerate) + ? $exchangerate + : $form->parse_amount( $myconfig, $form->{exchangerate} ); + } + + my @taxaccounts = split / /, $form->{taxaccounts}; + my $tax = 0; + my $fxtax = 0; + my $amount; + my $diff; + + my %tax = (); + my $accno; + + # add taxes + foreach $accno (@taxaccounts) { + $fxtax += $tax{fxamount}{$accno} = + $form->parse_amount( $myconfig, $form->{"tax_$accno"} ); + $tax += $tax{fxamount}{$accno}; + + push @{ $form->{acc_trans}{taxes} }, + { + accno => $accno, + amount => $tax{fxamount}{$accno}, + project_id => undef, + fx_transaction => 0 + }; + + $amount = $tax{fxamount}{$accno} * $form->{exchangerate}; + $tax{amount}{$accno} = $form->round_amount( $amount - $diff, 2 ); + $diff = $tax{amount}{$accno} - ( $amount - $diff ); + $amount = $tax{amount}{$accno} - $tax{fxamount}{$accno}; + $tax += $amount; + + if ( $form->{currency} ne $form->{defaultcurrency} ) { + push @{ $form->{acc_trans}{taxes} }, + { + accno => $accno, + amount => $amount, + project_id => undef, + fx_transaction => 1 + }; + } + + } + + my %amount = (); + my $fxinvamount = 0; + for ( 1 .. $form->{rowcount} ) { + $fxinvamount += $amount{fxamount}{$_} = + $form->parse_amount( $myconfig, $form->{"amount_$_"} ); + } + + $form->{taxincluded} *= 1; + + my $i; + my $project_id; + my $cleared = 0; + + $diff = 0; + + # deduct tax from amounts if tax included + for $i ( 1 .. $form->{rowcount} ) { + + if ( $amount{fxamount}{$i} ) { + + if ( $form->{taxincluded} ) { + $amount = + ($fxinvamount) + ? $fxtax * $amount{fxamount}{$i} / $fxinvamount + : 0; + $amount{fxamount}{$i} -= $amount; + } + + # multiply by exchangerate + $amount = $amount{fxamount}{$i} * $form->{exchangerate}; + $amount{amount}{$i} = $form->round_amount( $amount - $diff, 2 ); + $diff = $amount{amount}{$i} - ( $amount - $diff ); + + ( $null, $project_id ) = split /--/, $form->{"projectnumber_$i"}; + $project_id ||= undef; + ($accno) = split /--/, $form->{"${ARAP}_amount_$i"}; + + if ($keepcleared) { + $cleared = ( $form->{"cleared_$i"} ) ? 1 : 0; + } + + push @{ $form->{acc_trans}{lineitems} }, + { + accno => $accno, + amount => $amount{fxamount}{$i}, + project_id => $project_id, + description => $form->{"description_$i"}, + cleared => $cleared, + fx_transaction => 0 + }; + + if ( $form->{currency} ne $form->{defaultcurrency} ) { + $amount = $amount{amount}{$i} - $amount{fxamount}{$i}; + push @{ $form->{acc_trans}{lineitems} }, + { + accno => $accno, + amount => $amount, + project_id => $project_id, + description => $form->{"description_$i"}, + cleared => $cleared, + fx_transaction => 1 + }; + } + } + } + + my $invnetamount = 0; + for ( @{ $form->{acc_trans}{lineitems} } ) { $invnetamount += $_->{amount} } + my $invamount = $invnetamount + $tax; + + # adjust paidaccounts if there is no date in the last row + $form->{paidaccounts}-- + unless ( $form->{"datepaid_$form->{paidaccounts}"} ); + + if ( $form->{vc} ne "customer" ) { + $form->{vc} = "vendor"; + } + + my $paid = 0; + my $fxamount; + + $diff = 0; + + # add payments + for $i ( 1 .. $form->{paidaccounts} ) { + $fxamount = $form->parse_amount( $myconfig, $form->{"paid_$i"} ); + + if ($fxamount) { + $paid += $fxamount; + + $paidamount = $fxamount * $form->{exchangerate}; + + $amount = $form->round_amount( $paidamount - $diff, 2 ); + $diff = $amount - ( $paidamount - $diff ); + + $form->{datepaid} = $form->{"datepaid_$i"}; + + $paid{fxamount}{$i} = $fxamount; + $paid{amount}{$i} = $amount; + } + } + + $fxinvamount += $fxtax unless $form->{taxincluded}; + $fxinvamount = $form->round_amount( $fxinvamount, 2 ); + $invamount = $form->round_amount( $invamount, 2 ); + $paid = $form->round_amount( $paid, 2 ); + + $paid = + ( $fxinvamount == $paid ) + ? $invamount + : $form->round_amount( $paid * $form->{exchangerate}, 2 ); + + $query = q| SELECT (SELECT value FROM defaults WHERE setting_key = 'fxgain_accno_id'), (SELECT value FROM defaults WHERE setting_key = 'fxloss_accno_id')|; - my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query); + my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query); - ($null, $form->{employee_id}) = split /--/, $form->{employee}; - unless ($form->{employee_id}) { - ($form->{employee}, $form->{employee_id}) = - $form->get_employee($dbh); - } + ( $null, $form->{employee_id} ) = split /--/, $form->{employee}; + unless ( $form->{employee_id} ) { + ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh); + } - # check if id really exists - if ($form->{id}) { - my $id = $dbh->quote($form->{id}); - $keepcleared = 1; - $query = qq| + # check if id really exists + if ( $form->{id} ) { + my $id = $dbh->quote( $form->{id} ); + $keepcleared = 1; + $query = qq| SELECT id FROM $table WHERE id = $id|; - if ($dbh->selectrow_array($query)) { - # delete detail records - $query = qq| + if ( $dbh->selectrow_array($query) ) { + + # delete detail records + $query = qq| DELETE FROM acc_trans WHERE trans_id = $id|; - $dbh->do($query) || $form->dberror($query); - } - } else { + $dbh->do($query) || $form->dberror($query); + } + } + else { - my $uid = localtime; - $uid .= "$$"; + my $uid = localtime; + $uid .= "$$"; - $query = qq| + $query = qq| INSERT INTO $table (invnumber) VALUES ('$uid')|; - $dbh->do($query) || $form->dberror($query); + $dbh->do($query) || $form->dberror($query); - $query = qq| + $query = qq| SELECT id FROM $table WHERE invnumber = '$uid'|; - ($form->{id}) = $dbh->selectrow_array($query); - } - + ( $form->{id} ) = $dbh->selectrow_array($query); + } - # record last payment date in ar/ap table - $form->{datepaid} = $form->{transdate} unless $form->{datepaid}; - my $datepaid = ($paid) ? qq|'$form->{datepaid}'| : 'NOW'; + # record last payment date in ar/ap table + $form->{datepaid} = $form->{transdate} unless $form->{datepaid}; + my $datepaid = ($paid) ? qq|'$form->{datepaid}'| : 'NOW'; - $form->{invnumber} = $form->update_defaults($myconfig, $invnumber) unless $form->{invnumber}; + $form->{invnumber} = $form->update_defaults( $myconfig, $invnumber ) + unless $form->{invnumber}; - $query = qq| + $query = qq| UPDATE $table SET invnumber = ?, ordnumber = ?, @@ -288,38 +309,42 @@ sub post_transaction { WHERE id = ? |; - my @queryargs = ($form->{invnumber}, $form->{ordnumber}, - $form->{transdate}, $form->{"$form->{vc}_id"}, - $form->{taxincluded}, $invamount, $form->{duedate}, $paid, - $datepaid, $invnetamout, $form->{currency}, $form->{notes}, - $form->{department_id}, $form->{employee_id}, - $form->{ponumber}, $form->{id}); - - $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query); - - @queries = $form->run_custom_queries($table, 'INSERT'); - # update exchangerate - my $buy = $form->{exchangerate}; - my $sell = 0; - if ($form->{vc} eq 'vendor') { - $buy = 0; - $sell = $form->{exchangerate}; - } - - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { - $form->update_exchangerate( - $dbh, $form->{currency}, $form->{transdate}, - $buy, $sell); - } - - my $ref; - - # add individual transactions - foreach $ref (@{ $form->{acc_trans}{lineitems} }) { - - # insert detail records in acc_trans - if ($ref->{amount}) { - $query = qq| + my @queryargs = ( + $form->{invnumber}, $form->{ordnumber}, + $form->{transdate}, $form->{"$form->{vc}_id"}, + $form->{taxincluded}, $invamount, + $form->{duedate}, $paid, + $datepaid, $invnetamout, + $form->{currency}, $form->{notes}, + $form->{department_id}, $form->{employee_id}, + $form->{ponumber}, $form->{id} + ); + + $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query); + + @queries = $form->run_custom_queries( $table, 'INSERT' ); + + # update exchangerate + my $buy = $form->{exchangerate}; + my $sell = 0; + if ( $form->{vc} eq 'vendor' ) { + $buy = 0; + $sell = $form->{exchangerate}; + } + + if ( ( $form->{currency} ne $form->{defaultcurrency} ) && !$exchangerate ) { + $form->update_exchangerate( $dbh, $form->{currency}, $form->{transdate}, + $buy, $sell ); + } + + my $ref; + + # add individual transactions + foreach $ref ( @{ $form->{acc_trans}{lineitems} } ) { + + # insert detail records in acc_trans + if ( $ref->{amount} ) { + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, memo, @@ -328,19 +353,21 @@ sub post_transaction { WHERE accno = ?), ?, ?, ?, ?, ?, ?)|; - @queryargs = ($form->{id}, $ref->{accno}, - $ref->{amount} * $ml, $form->{transdate}, - $ref->{project_id}, $ref->{description}, - $ref->{fx_transaction}, $ref->{cleared}); - $dbh->prepare($query)->execute(@queryargs) - || $form->dberror($query); - } - } - - # save taxes - foreach $ref (@{ $form->{acc_trans}{taxes} }) { - if ($ref->{amount}) { - $query = qq| + @queryargs = ( + $form->{id}, $ref->{accno}, + $ref->{amount} * $ml, $form->{transdate}, + $ref->{project_id}, $ref->{description}, + $ref->{fx_transaction}, $ref->{cleared} + ); + $dbh->prepare($query)->execute(@queryargs) + || $form->dberror($query); + } + } + + # save taxes + foreach $ref ( @{ $form->{acc_trans}{taxes} } ) { + if ( $ref->{amount} ) { + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction) @@ -348,78 +375,78 @@ sub post_transaction { WHERE accno = ?), ?, ?, ?)|; - @queryargs = ($form->{id}, $ref->{accno}, - $ref->{amount} * $ml, $form->{transdate}, - $ref->{fx_transaction}); - $dbh->prepare($query)->execute(@queryargs) - || $form->dberror($query); - } - } - + @queryargs = ( + $form->{id}, $ref->{accno}, $ref->{amount} * $ml, + $form->{transdate}, $ref->{fx_transaction} + ); + $dbh->prepare($query)->execute(@queryargs) + || $form->dberror($query); + } + } - my $arap; + my $arap; - # record ar/ap - if (($arap = $invamount)) { - ($accno) = split /--/, $form->{$ARAP}; + # record ar/ap + if ( ( $arap = $invamount ) ) { + ($accno) = split /--/, $form->{$ARAP}; - $query = qq| + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?)|; - @queryargs = ($form->{id}, $accno, $invamount * -1 * $ml, - $form->{transdate}); + @queryargs = + ( $form->{id}, $accno, $invamount * -1 * $ml, $form->{transdate} ); - $dbh->prepare($query)->execute(@queryargs) - || $form->dberror($query); - } + $dbh->prepare($query)->execute(@queryargs) + || $form->dberror($query); + } - # if there is no amount force ar/ap - if ($fxinvamount == 0) { - $arap = 1; - } + # if there is no amount force ar/ap + if ( $fxinvamount == 0 ) { + $arap = 1; + } + my $exchangerate; - my $exchangerate; + # add paid transactions + for $i ( 1 .. $form->{paidaccounts} ) { - # add paid transactions - for $i (1 .. $form->{paidaccounts}) { + if ( $paid{fxamount}{$i} ) { - if ($paid{fxamount}{$i}) { + ($accno) = split( /--/, $form->{"${ARAP}_paid_$i"} ); + $form->{"datepaid_$i"} = $form->{transdate} + unless ( $form->{"datepaid_$i"} ); - ($accno) = split(/--/, $form->{"${ARAP}_paid_$i"}); - $form->{"datepaid_$i"} = $form->{transdate} unless ($form->{"datepaid_$i"}); + $exchangerate = 0; - $exchangerate = 0; + if ( $form->{currency} eq $form->{defaultcurrency} ) { + $form->{"exchangerate_$i"} = 1; + } + else { + $exchangerate = + $form->check_exchangerate( $myconfig, $form->{currency}, + $form->{"datepaid_$i"}, $buysell ); - if ($form->{currency} eq $form->{defaultcurrency}) { - $form->{"exchangerate_$i"} = 1; - } else { - $exchangerate = $form->check_exchangerate( - $myconfig, $form->{currency}, - $form->{"datepaid_$i"}, $buysell); + $form->{"exchangerate_$i"} = + ($exchangerate) + ? $exchangerate + : $form->parse_amount( $myconfig, + $form->{"exchangerate_$i"} ); + } - $form->{"exchangerate_$i"} = ($exchangerate) - ? $exchangerate - : $form->parse_amount( - $myconfig, - $form->{"exchangerate_$i"}); - } + # if there is no amount + if ( $fxinvamount == 0 ) { + $form->{exchangerate} = $form->{"exchangerate_$i"}; + } - # if there is no amount - if ($fxinvamount == 0) { - $form->{exchangerate} = - $form->{"exchangerate_$i"}; - } + # ar/ap amount + if ($arap) { + ($accno) = split /--/, $form->{$ARAP}; - # ar/ap amount - if ($arap) { - ($accno) = split /--/, $form->{$ARAP}; - - # add ar/ap - $query = qq| + # add ar/ap + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, amount,transdate) @@ -427,25 +454,26 @@ sub post_transaction { WHERE accno = ?), ?, ?)|; - @queryargs = ($form->{id}, $accno, - $paid{amount}{$i} * $ml, - $form->{"datepaid_$i"}); - $dbh->prepare($query)->execute(@queryargs) - || $form->dberror($query); - } - - $arap = $paid{amount}{$i}; + @queryargs = ( + $form->{id}, $accno, + $paid{amount}{$i} * $ml, + $form->{"datepaid_$i"} + ); + $dbh->prepare($query)->execute(@queryargs) + || $form->dberror($query); + } + $arap = $paid{amount}{$i}; - # add payment - if ($paid{fxamount}{$i}) { + # add payment + if ( $paid{fxamount}{$i} ) { - ($accno) = split /--/, $form->{"${ARAP}_paid_$i"}; + ($accno) = split /--/, $form->{"${ARAP}_paid_$i"}; - my $cleared = ($form->{"cleared_$i"}) ? 1 : 0; + my $cleared = ( $form->{"cleared_$i"} ) ? 1 : 0; - $amount = $paid{fxamount}{$i}; - $query = qq| + $amount = $paid{fxamount}{$i}; + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, @@ -454,32 +482,34 @@ sub post_transaction { WHERE accno = ?), ?, ?, ?, ?, ?)|; - @queryargs = ($form->{id}, $accno, - $amount * -1 * $ml, - $form->{"datepaid_$i"}, - $form->{"source_$i"}, - $form->{"memo_$i"}, - $cleared); - $dbh->prepare($query)->execute(@queryargs) - || $form->dberror($query); - - if ($form->{currency} - ne $form->{defaultcurrency}) { - - # exchangerate gain/loss - $amount = ($form->round_amount( - $paid{fxamount}{$i} - * $form->{exchangerate},2) - - $form->round_amount( - $paid{fxamount}{$i} - * $form->{"exchangerate_$i"}, - 2)) * -1; - - if ($amount) { - - my $accno_id = (($amount * $ml) > 0) ? $fxgain_accno_id : $fxloss_accno_id; - - $query = qq| + @queryargs = ( + $form->{id}, $accno, + $amount * -1 * $ml, $form->{"datepaid_$i"}, + $form->{"source_$i"}, $form->{"memo_$i"}, + $cleared + ); + $dbh->prepare($query)->execute(@queryargs) + || $form->dberror($query); + + if ( $form->{currency} ne $form->{defaultcurrency} ) { + + # exchangerate gain/loss + $amount = ( + $form->round_amount( + $paid{fxamount}{$i} * $form->{exchangerate}, 2 ) - + $form->round_amount( + $paid{fxamount}{$i} * $form->{"exchangerate_$i"}, 2 + ) + ) * -1; + + if ($amount) { + + my $accno_id = + ( ( $amount * $ml ) > 0 ) + ? $fxgain_accno_id + : $fxloss_accno_id; + + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, @@ -491,21 +521,20 @@ sub post_transaction { ?, ?, '1', ?)|; - @queryargs = ($form->{id}, - $accno_id, - $amount * $ml, - $form->{"datepaid_$i"}, - $cleared); - $sth = $dbh->prepare($query); - $sth->execute(@queryargs) - || - $form->dberror($query); - } - - # exchangerate difference - $amount = $paid{amount}{$i} - $paid{fxamount}{$i} + $amount; - - $query = qq| + @queryargs = ( + $form->{id}, $accno_id, + $amount * $ml, + $form->{"datepaid_$i"}, $cleared + ); + $sth = $dbh->prepare($query); + $sth->execute(@queryargs) + || $form->dberror($query); + } + + # exchangerate difference + $amount = $paid{amount}{$i} - $paid{fxamount}{$i} + $amount; + + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, amount, @@ -519,171 +548,175 @@ sub post_transaction { ?, ?, '1', ?, ?)|; - @queryargs = ($form->{id}, $accno, - $amount * -1 * $ml, - $form->{"datepaid_$i"}, - $cleared, $form->{"source_$i"}); - $sth = $dbh->prepare($query) ; - $sth->execute(@queryargs) - || $form->dberror($query); + @queryargs = ( + $form->{id}, $accno, + $amount * -1 * $ml, + $form->{"datepaid_$i"}, + $cleared, $form->{"source_$i"} + ); + $sth = $dbh->prepare($query); + $sth->execute(@queryargs) + || $form->dberror($query); - } + } - # update exchangerate record - $buy = $form->{"exchangerate_$i"}; - $sell = 0; + # update exchangerate record + $buy = $form->{"exchangerate_$i"}; + $sell = 0; - if ($form->{vc} eq 'vendor') { - $buy = 0; - $sell = $form->{"exchangerate_$i"}; - } + if ( $form->{vc} eq 'vendor' ) { + $buy = 0; + $sell = $form->{"exchangerate_$i"}; + } - if (($form->{currency} ne - $form->{defaultcurrency}) && !$exchangerate) { + if ( ( $form->{currency} ne $form->{defaultcurrency} ) + && !$exchangerate ) + { - $form->update_exchangerate( - $dbh, $form->{currency}, - $form->{"datepaid_$i"}, $buy, - $sell); - } - } - } - } + $form->update_exchangerate( $dbh, $form->{currency}, + $form->{"datepaid_$i"}, + $buy, $sell ); + } + } + } + } - # save printed and queued - $form->save_status($dbh); + # save printed and queued + $form->save_status($dbh); - my %audittrail = ( tablename => $table, - reference => $form->{invnumber}, - formname => 'transaction', - action => 'posted', - id => $form->{id} ); + my %audittrail = ( + tablename => $table, + reference => $form->{invnumber}, + formname => 'transaction', + action => 'posted', + id => $form->{id} + ); - $form->audittrail($dbh, "", \%audittrail); + $form->audittrail( $dbh, "", \%audittrail ); - $form->save_recurring($dbh, $myconfig); + $form->save_recurring( $dbh, $myconfig ); - my $rc = $dbh->commit; + my $rc = $dbh->commit; - $rc; + $rc; } - sub delete_transaction { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database, turn AutoCommit off - my $dbh = $form->{dbh}; + # connect to database, turn AutoCommit off + my $dbh = $form->{dbh}; - my $table = ($form->{vc} eq 'customer') ? 'ar' : 'ap'; + my $table = ( $form->{vc} eq 'customer' ) ? 'ar' : 'ap'; - my %audittrail = ( tablename => $table, - reference => $form->{invnumber}, - formname => 'transaction', - action => 'deleted', - id => $form->{id} ); + my %audittrail = ( + tablename => $table, + reference => $form->{invnumber}, + formname => 'transaction', + action => 'deleted', + id => $form->{id} + ); - $form->audittrail($dbh, "", \%audittrail); + $form->audittrail( $dbh, "", \%audittrail ); - my $query = qq|DELETE FROM $table WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + my $query = qq|DELETE FROM $table WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); - $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; - $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query); + $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; + $dbh->prepare($query)->execute( $form->{id} ) || $form->dberror($query); - # get spool files - $query = qq|SELECT spoolfile + # get spool files + $query = qq|SELECT spoolfile FROM status WHERE trans_id = ? AND spoolfile IS NOT NULL|; - my $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - - my $spoolfile; - my @spoolfiles = (); + my $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - while (($spoolfile) = $sth->fetchrow_array) { - push @spoolfiles, $spoolfile; - } - - $sth->finish; + my $spoolfile; + my @spoolfiles = (); - $query = qq|DELETE FROM status WHERE trans_id = ?|; - $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query); + while ( ($spoolfile) = $sth->fetchrow_array ) { + push @spoolfiles, $spoolfile; + } - # commit - my $rc = $dbh->commit; + $sth->finish; - if ($rc) { - foreach $spoolfile (@spoolfiles) { - unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile; - } - } + $query = qq|DELETE FROM status WHERE trans_id = ?|; + $dbh->prepare($query)->execute( $form->{id} ) || $form->dberror($query); - $rc; -} + # commit + my $rc = $dbh->commit; + if ($rc) { + foreach $spoolfile (@spoolfiles) { + unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile; + } + } + $rc; +} sub transactions { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->{dbh}; - my $null; - my $var; - my $paid = "a.paid"; - my $ml = 1; - my $ARAP = 'AR'; - my $table = 'ar'; - my $buysell = 'buy'; - my $acc_trans_join; - my $acc_trans_flds; - - if ($form->{vc} eq 'vendor') { - $ml = -1; - $ARAP = 'AP'; - $table = 'ap'; - $buysell = 'sell'; - } - - ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; - - my @paidargs = (); - if ($form->{outstanding}) { - $paid = qq| + my ( $self, $myconfig, $form ) = @_; + + # connect to database + my $dbh = $form->{dbh}; + my $null; + my $var; + my $paid = "a.paid"; + my $ml = 1; + my $ARAP = 'AR'; + my $table = 'ar'; + my $buysell = 'buy'; + my $acc_trans_join; + my $acc_trans_flds; + + if ( $form->{vc} eq 'vendor' ) { + $ml = -1; + $ARAP = 'AP'; + $table = 'ap'; + $buysell = 'sell'; + } + + ( $form->{transdatefrom}, $form->{transdateto} ) = + $form->from_to( $form->{year}, $form->{month}, $form->{interval} ) + if $form->{year} && $form->{month}; + + my @paidargs = (); + if ( $form->{outstanding} ) { + $paid = qq| SELECT SUM(ac.amount) * -1 * $ml FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) WHERE ac.trans_id = a.id AND (c.link LIKE '%${ARAP}_paid%' OR c.link = '')|; - if ($form->{transdateto}){ - $paid .= qq| + if ( $form->{transdateto} ) { + $paid .= qq| AND ac.transdate <= ?|; - push @paidargs, $form->{transdateto}; - } - $form->{summary} = 1; - } - + push @paidargs, $form->{transdateto}; + } + $form->{summary} = 1; + } - if (!$form->{summary}) { - $acc_trans_flds = qq| + if ( !$form->{summary} ) { + $acc_trans_flds = qq| , c.accno, ac.source, pr.projectnumber, ac.memo AS description, ac.amount AS linetotal, i.description AS linedescription|; - $acc_trans_join = qq| + $acc_trans_join = qq| JOIN acc_trans ac ON (a.id = ac.trans_id) JOIN chart c ON (c.id = ac.chart_id) LEFT JOIN project pr ON (pr.id = ac.project_id) LEFT JOIN invoice i ON (i.id = ac.invoice_id)|; - } + } - my $query = qq| + my $query = qq| SELECT a.id, a.invnumber, a.ordnumber, a.transdate, a.duedate, a.netamount, a.amount, ($paid) AS paid, a.invoice, a.datepaid, a.terms, a.notes, @@ -702,129 +735,130 @@ sub transactions { LEFT JOIN department d ON (a.department_id = d.id) $acc_trans_join|; - my %ordinal = ( id => 1, - invnumber => 2, - ordnumber => 3, - transdate => 4, - duedate => 5, - datepaid => 10, - shipvia => 13, - shippingpoint => 14, - employee => 15, - name => 16, - manager => 19, - curr => 20, - department => 22, - ponumber => 23, - accno => 24, - source => 25, - project => 26, - description => 27); - - - my @a = (transdate, invnumber, name); - push @a, "employee" if $form->{l_employee}; - push @a, "manager" if $form->{l_manager}; - my $sortorder = $form->sort_order(\@a, \%ordinal); - - my $where = "1 = 1"; - if ($form->{"$form->{vc}_id"}) { - $where .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|; - } else { - if ($form->{$form->{vc}}) { - $var = $dbh->quote( - $form->like(lc $form->{$form->{vc}})); - $where .= " AND lower(vc.name) LIKE $var"; - } - } - - for (qw(department employee)) { - if ($form->{$_}) { - ($null, $var) = split /--/, $form->{$_}; - $var = $dbh->quote($var); - $where .= " AND a.${_}_id = $var"; - } - } - - for (qw(invnumber ordnumber)) { - if ($form->{$_}) { - $var = $dbh->quote($form->like(lc $form->{$_})); - $where .= " AND lower(a.$_) LIKE $var"; - $form->{open} = $form->{closed} = 0; - } - } - if ($form->{partsid}){ - my $partsid = $dbh->quote($form->{partsid}); - $where .= " AND a.id IN (select trans_id FROM invoice + my %ordinal = ( + id => 1, + invnumber => 2, + ordnumber => 3, + transdate => 4, + duedate => 5, + datepaid => 10, + shipvia => 13, + shippingpoint => 14, + employee => 15, + name => 16, + manager => 19, + curr => 20, + department => 22, + ponumber => 23, + accno => 24, + source => 25, + project => 26, + description => 27 + ); + + my @a = ( transdate, invnumber, name ); + push @a, "employee" if $form->{l_employee}; + push @a, "manager" if $form->{l_manager}; + my $sortorder = $form->sort_order( \@a, \%ordinal ); + + my $where = "1 = 1"; + if ( $form->{"$form->{vc}_id"} ) { + $where .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|; + } + else { + if ( $form->{ $form->{vc} } ) { + $var = $dbh->quote( $form->like( lc $form->{ $form->{vc} } ) ); + $where .= " AND lower(vc.name) LIKE $var"; + } + } + + for (qw(department employee)) { + if ( $form->{$_} ) { + ( $null, $var ) = split /--/, $form->{$_}; + $var = $dbh->quote($var); + $where .= " AND a.${_}_id = $var"; + } + } + + for (qw(invnumber ordnumber)) { + if ( $form->{$_} ) { + $var = $dbh->quote( $form->like( lc $form->{$_} ) ); + $where .= " AND lower(a.$_) LIKE $var"; + $form->{open} = $form->{closed} = 0; + } + } + if ( $form->{partsid} ) { + my $partsid = $dbh->quote( $form->{partsid} ); + $where .= " AND a.id IN (select trans_id FROM invoice WHERE parts_id = $partsid)"; - } - - for (qw(ponumber shipvia notes)) { - if ($form->{$_}) { - $var = $dbh->quote($form->like(lc $form->{$_})); - $where .= " AND lower(a.$_) LIKE $var"; - } - } - - if ($form->{description}) { - if ($acc_trans_flds) { - $var = $dbh->quote( - $form->like(lc $form->{description}) - ); - $where .= " AND lower(ac.memo) LIKE $var + } + + for (qw(ponumber shipvia notes)) { + if ( $form->{$_} ) { + $var = $dbh->quote( $form->like( lc $form->{$_} ) ); + $where .= " AND lower(a.$_) LIKE $var"; + } + } + + if ( $form->{description} ) { + if ($acc_trans_flds) { + $var = $dbh->quote( $form->like( lc $form->{description} ) ); + $where .= " AND lower(ac.memo) LIKE $var OR lower(i.description) LIKE $var"; - } else { - $where .= " AND a.id = 0"; - } - } - - if ($form->{source}) { - if ($acc_trans_flds) { - $var = $dbh->quote($form->like(lc $form->{source})); - $where .= " AND lower(ac.source) LIKE $var"; - } else { - $where .= " AND a.id = 0"; - } - } - - my $transdatefrom = $dbh->quote($form->{transdatefrom}); - $where .= " AND a.transdate >= $transdatefrom" - if $form->{transdatefrom}; - - my $transdateto = $dbh->quote($form->{transdateto}); - $where .= " AND a.transdate <= $transdateto" if $form->{transdateto}; - - if ($form->{open} || $form->{closed}) { - unless ($form->{open} && $form->{closed}) { - $where .= " AND a.amount != a.paid" if ($form->{open}); - $where .= " AND a.amount = a.paid" if ($form->{closed}); - } - } - - if ($form->{till} ne "") { - $where .= " AND a.invoice = '1' + } + else { + $where .= " AND a.id = 0"; + } + } + + if ( $form->{source} ) { + if ($acc_trans_flds) { + $var = $dbh->quote( $form->like( lc $form->{source} ) ); + $where .= " AND lower(ac.source) LIKE $var"; + } + else { + $where .= " AND a.id = 0"; + } + } + + my $transdatefrom = $dbh->quote( $form->{transdatefrom} ); + $where .= " AND a.transdate >= $transdatefrom" + if $form->{transdatefrom}; + + my $transdateto = $dbh->quote( $form->{transdateto} ); + $where .= " AND a.transdate <= $transdateto" if $form->{transdateto}; + + if ( $form->{open} || $form->{closed} ) { + unless ( $form->{open} && $form->{closed} ) { + $where .= " AND a.amount != a.paid" if ( $form->{open} ); + $where .= " AND a.amount = a.paid" if ( $form->{closed} ); + } + } + + if ( $form->{till} ne "" ) { + $where .= " AND a.invoice = '1' AND a.till = $form->{till}"; - if ($myconfig->{role} eq 'user') { - my $login = $dbh->quote($form->{login}); - $where .= " AND e.login = $login"; - } - } + if ( $myconfig->{role} eq 'user' ) { + my $login = $dbh->quote( $form->{login} ); + $where .= " AND e.login = $login"; + } + } - if ($form->{$ARAP}) { - my ($accno) = split /--/, $form->{$ARAP}; - $accno = $dbh->quote($accno); - $where .= qq| + if ( $form->{$ARAP} ) { + my ($accno) = split /--/, $form->{$ARAP}; + $accno = $dbh->quote($accno); + $where .= qq| AND a.id IN (SELECT ac.trans_id FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) WHERE a.id = ac.trans_id AND c.accno = $accno)|; - } + } - if ($form->{description}) { - $var = $dbh->quote($form->like(lc $form->{description})); - $where .= qq| + if ( $form->{description} ) { + $var = $dbh->quote( $form->like( lc $form->{description} ) ); + $where .= qq| AND (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE lower(memo) LIKE '$var') @@ -833,78 +867,82 @@ sub transactions { FROM invoice WHERE lower(description) LIKE '$var'))|; - } + } - $query .= "WHERE $where + $query .= "WHERE $where ORDER BY $sortorder"; - my $sth = $dbh->prepare($query); - $sth->execute(@paidargs) || $form->dberror($query); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $ref->{exchangerate} = 1 unless $ref->{exchangerate}; - - if ($ref->{linetotal} <= 0) { - $ref->{debit} = $ref->{linetotal} * -1; - $ref->{credit} = 0; - } else { - $ref->{debit} = 0; - $ref->{credit} = $ref->{linetotal}; - } - - if ($ref->{invoice}) { - $ref->{description} ||= $ref->{linedescription}; - } - - if ($form->{outstanding}) { - next if $form->round_amount($ref->{amount}, 2) - == $form->round_amount($ref->{paid}, 2); - } - - push @{ $form->{transactions} }, $ref; - } - - $sth->finish; - $dbh->commit; + my $sth = $dbh->prepare($query); + $sth->execute(@paidargs) || $form->dberror($query); + + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $ref->{exchangerate} = 1 unless $ref->{exchangerate}; + + if ( $ref->{linetotal} <= 0 ) { + $ref->{debit} = $ref->{linetotal} * -1; + $ref->{credit} = 0; + } + else { + $ref->{debit} = 0; + $ref->{credit} = $ref->{linetotal}; + } + + if ( $ref->{invoice} ) { + $ref->{description} ||= $ref->{linedescription}; + } + + if ( $form->{outstanding} ) { + next + if $form->round_amount( $ref->{amount}, 2 ) == + $form->round_amount( $ref->{paid}, 2 ); + } + + push @{ $form->{transactions} }, $ref; + } + + $sth->finish; + $dbh->commit; } - # this is used in IS, IR to retrieve the name sub get_name { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # sanitize $form->{vc} - if ($form->{vc} ne 'customer'){ - $form->{vc} = 'vendor'; - } else { - $form->{vc} = 'customer'; - } - # connect to database - my $dbh = $form->{dbh}; + # sanitize $form->{vc} + if ( $form->{vc} ne 'customer' ) { + $form->{vc} = 'vendor'; + } + else { + $form->{vc} = 'customer'; + } - my $dateformat = $myconfig->{dateformat}; + # connect to database + my $dbh = $form->{dbh}; - if ($myconfig->{dateformat} !~ /^y/) { - my @a = split /\W/, $form->{transdate}; - $dateformat .= "yy" if (length $a[2] > 2); - } + my $dateformat = $myconfig->{dateformat}; - if ($form->{transdate} !~ /\W/) { - $dateformat = 'yyyymmdd'; - } + if ( $myconfig->{dateformat} !~ /^y/ ) { + my @a = split /\W/, $form->{transdate}; + $dateformat .= "yy" if ( length $a[2] > 2 ); + } - my $duedate; + if ( $form->{transdate} !~ /\W/ ) { + $dateformat = 'yyyymmdd'; + } - $dateformat = $dbh->quote($dateformat); - my $tdate = $dbh->quote($form->{transdate}); - $duedate = ($form->{transdate}) - ? "to_date($tdate, $dateformat) - + c.terms" - : "current_date + c.terms"; + my $duedate; - $form->{"$form->{vc}_id"} *= 1; - # get customer/vendor - my $query = qq| + $dateformat = $dbh->quote($dateformat); + my $tdate = $dbh->quote( $form->{transdate} ); + $duedate = ( $form->{transdate} ) + ? "to_date($tdate, $dateformat) + + c.terms" + : "current_date + c.terms"; + + $form->{"$form->{vc}_id"} *= 1; + + # get customer/vendor + my $query = qq| SELECT c.name AS $form->{vc}, c.discount, c.creditlimit, c.terms, c.email, c.cc, c.bcc, c.taxincluded, c.address1, c.address2, c.city, c.state, @@ -919,65 +957,67 @@ sub get_name { LEFT JOIN employee e ON (e.id = c.employee_id) WHERE c.id = ?|; - @queryargs = ($form->{"$form->{vc}_id"}); - my $sth = $dbh->prepare($query); + @queryargs = ( $form->{"$form->{vc}_id"} ); + my $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror($query); + $sth->execute(@queryargs) || $form->dberror($query); - $ref = $sth->fetchrow_hashref(NAME_lc); + $ref = $sth->fetchrow_hashref(NAME_lc); - if ($form->{id}) { - for (qw(currency employee employee_id intnotes)) { - delete $ref->{$_}; - } - } + if ( $form->{id} ) { + for (qw(currency employee employee_id intnotes)) { + delete $ref->{$_}; + } + } - for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } + $sth->finish; - my $buysell = ($form->{vc} eq 'customer') ? "buy" : "sell"; + my $buysell = ( $form->{vc} eq 'customer' ) ? "buy" : "sell"; - # if no currency use defaultcurrency - $form->{currency} = - ($form->{currency}) - ? $form->{currency} - : $form->{defaultcurrency}; - $form->{exchangerate} = 0 - if $form->{currency} eq $form->{defaultcurrency}; + # if no currency use defaultcurrency + $form->{currency} = + ( $form->{currency} ) + ? $form->{currency} + : $form->{defaultcurrency}; + $form->{exchangerate} = 0 + if $form->{currency} eq $form->{defaultcurrency}; - if ($form->{transdate} && ($form->{currency} - ne $form->{defaultcurrency})) { - $form->{exchangerate} = $form->get_exchangerate( - $dbh, $form->{currency}, $form->{transdate}, $buysell); - } + if ( $form->{transdate} + && ( $form->{currency} ne $form->{defaultcurrency} ) ) + { + $form->{exchangerate} = + $form->get_exchangerate( $dbh, $form->{currency}, $form->{transdate}, + $buysell ); + } - $form->{forex} = $form->{exchangerate}; + $form->{forex} = $form->{exchangerate}; - # if no employee, default to login - ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh) - unless $form->{employee_id}; + # if no employee, default to login + ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh) + unless $form->{employee_id}; - my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap'; - my $ARAP = uc $arap; + my $arap = ( $form->{vc} eq 'customer' ) ? 'ar' : 'ap'; + my $ARAP = uc $arap; - $form->{creditremaining} = $form->{creditlimit}; - $query = qq| + $form->{creditremaining} = $form->{creditlimit}; + $query = qq| SELECT SUM(amount - paid) FROM $arap WHERE $form->{vc}_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{"$form->{vc}_id"}) - || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute( $form->{"$form->{vc}_id"} ) + || $form->dberror($query); - ($form->{creditremaining}) -= $sth->fetchrow_array; + ( $form->{creditremaining} ) -= $sth->fetchrow_array; - $sth->finish; - if ($form->{vc} ne "customer"){ - $form->{vc} = 'vendor'; - } + $sth->finish; + if ( $form->{vc} ne "customer" ) { + $form->{vc} = 'vendor'; + } - $query = qq| + $query = qq| SELECT o.amount, (SELECT e.$buysell FROM exchangerate e WHERE e.curr = o.curr AND e.transdate = o.transdate) @@ -985,66 +1025,66 @@ sub get_name { WHERE o.$form->{vc}_id = ? AND o.quotation = '0' AND o.closed = '0'|; - $sth = $dbh->prepare($query); - $sth->execute ($form->{"$form->{vc}_id"}) || $form->dberror($query); - - while (my ($amount, $exch) = $sth->fetchrow_array) { - $exch = 1 unless $exch; - $form->{creditremaining} -= $amount * $exch; - } + $sth = $dbh->prepare($query); + $sth->execute( $form->{"$form->{vc}_id"} ) || $form->dberror($query); - $sth->finish; + while ( my ( $amount, $exch ) = $sth->fetchrow_array ) { + $exch = 1 unless $exch; + $form->{creditremaining} -= $amount * $exch; + } + $sth->finish; - # get shipto if we did not converted an order or invoice - if (!$form->{shipto}) { + # get shipto if we did not converted an order or invoice + if ( !$form->{shipto} ) { - for ( - qw(shiptoname shiptoaddress1 shiptoaddress2 - shiptocity shiptostate shiptozipcode - shiptocountry shiptocontact shiptophone - shiptofax shiptoemail) - ) { - delete $form->{$_} - } + for ( + qw(shiptoname shiptoaddress1 shiptoaddress2 + shiptocity shiptostate shiptozipcode + shiptocountry shiptocontact shiptophone + shiptofax shiptoemail) + ) + { + delete $form->{$_}; + } - ## needs fixing (SELECT *) - $query = qq| + ## needs fixing (SELECT *) + $query = qq| SELECT * FROM shipto WHERE trans_id = $form->{"$form->{vc}_id"}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; - } + $ref = $sth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } + $sth->finish; + } - # get taxes - $query = qq| + # get taxes + $query = qq| SELECT c.accno FROM chart c JOIN $form->{vc}tax ct ON (ct.chart_id = c.id) WHERE ct.$form->{vc}_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute( $form->{"$form->{vc}_id"}) || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute( $form->{"$form->{vc}_id"} ) || $form->dberror($query); - my %tax; + my %tax; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $tax{$ref->{accno}} = 1; - } + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $tax{ $ref->{accno} } = 1; + } - $sth->finish; - $transdate = $dbh->quote($form->{transdate}); - my $where = qq|AND (t.validto >= $transdate OR t.validto IS NULL)| - if $form->{transdate}; + $sth->finish; + $transdate = $dbh->quote( $form->{transdate} ); + my $where = qq|AND (t.validto >= $transdate OR t.validto IS NULL)| + if $form->{transdate}; - # get tax rates and description - $query = qq| + # get tax rates and description + $query = qq| SELECT c.accno, c.description, t.rate, t.taxnumber FROM chart c JOIN tax t ON (c.id = t.chart_id) @@ -1052,33 +1092,32 @@ sub get_name { $where ORDER BY accno, validto|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - $form->{taxaccounts} = ""; - my %a = (); + $form->{taxaccounts} = ""; + my %a = (); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { - if ($tax{$ref->{accno}}) { - if (not exists $a{$ref->{accno}}) { - for (qw(rate description taxnumber)) { - $form->{"$ref->{accno}_$_"} = - $ref->{$_}; - } - $form->{taxaccounts} .= "$ref->{accno} "; - $a{$ref->{accno}} = 1; - } - } - } + if ( $tax{ $ref->{accno} } ) { + if ( not exists $a{ $ref->{accno} } ) { + for (qw(rate description taxnumber)) { + $form->{"$ref->{accno}_$_"} = $ref->{$_}; + } + $form->{taxaccounts} .= "$ref->{accno} "; + $a{ $ref->{accno} } = 1; + } + } + } - $sth->finish; - chop $form->{taxaccounts}; + $sth->finish; + chop $form->{taxaccounts}; - # setup last accounts used for this customer/vendor - if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) { + # setup last accounts used for this customer/vendor + if ( !$form->{id} && $form->{type} !~ /_(order|quotation)/ ) { - $query = qq| + $query = qq| SELECT c.accno, c.description, c.link, c.category, ac.project_id, p.projectnumber, a.department_id, d.description AS department @@ -1094,41 +1133,38 @@ sub get_name { ?) |; - $sth = $dbh->prepare($query); - $sth->execute($form->{"$form->{vc}_id"}, - $form->{"$form->{vc}_id"}) || $form->dberror($query); - - my $i = 0; - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{department} = $ref->{department}; - $form->{department_id} = $ref->{department_id}; - - if ($ref->{link} =~ /_amount/) { - $i++; - $form->{"$form->{ARAP}_amount_$i"} = - "$ref->{accno}--$ref->{description}" - if $ref->{accno}; - $form->{"projectnumber_$i"} = - "$ref->{projectnumber}--" . - "$ref->{project_id}" - if $ref->{project_id}; - } - - if ($ref->{link} eq $form->{ARAP}) { - $form->{$form->{ARAP}} = - $form->{"$form->{ARAP}_1"} = - "$ref->{accno}--". - "$ref->{description}" - if $ref->{accno}; - } - } - - $sth->finish; - $form->{rowcount} = $i if ($i && !$form->{type}); - } - - $dbh->commit; + $sth = $dbh->prepare($query); + $sth->execute( $form->{"$form->{vc}_id"}, $form->{"$form->{vc}_id"} ) + || $form->dberror($query); + + my $i = 0; + + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $form->{department} = $ref->{department}; + $form->{department_id} = $ref->{department_id}; + + if ( $ref->{link} =~ /_amount/ ) { + $i++; + $form->{"$form->{ARAP}_amount_$i"} = + "$ref->{accno}--$ref->{description}" + if $ref->{accno}; + $form->{"projectnumber_$i"} = + "$ref->{projectnumber}--" . "$ref->{project_id}" + if $ref->{project_id}; + } + + if ( $ref->{link} eq $form->{ARAP} ) { + $form->{ $form->{ARAP} } = $form->{"$form->{ARAP}_1"} = + "$ref->{accno}--" . "$ref->{description}" + if $ref->{accno}; + } + } + + $sth->finish; + $form->{rowcount} = $i if ( $i && !$form->{type} ); + } + + $dbh->commit; } 1; diff --git a/LedgerSMB/AM.pm b/LedgerSMB/AM.pm index c395a0a8..6c307e07 100755 --- a/LedgerSMB/AM.pm +++ b/LedgerSMB/AM.pm @@ -1,8 +1,8 @@ #===================================================================== -# LedgerSMB +# LedgerSMB # Small Medium Business Accounting software # http://www.ledgersmb.org/ -# +# # Copyright (C) 2006 # This work contains copyrighted information from a number of sources all used # with permission. @@ -40,25 +40,25 @@ use LedgerSMB::Sysconfig; sub get_account { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - my $dbh = $form->{dbh}; + my $dbh = $form->{dbh}; - my $query = qq| + my $query = qq| SELECT accno, description, charttype, gifi_accno, category, link, contra FROM chart WHERE id = ?|; - my $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; + my $ref = $sth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } + $sth->finish; - # get default accounts - $query = qq| + # get default accounts + $query = qq| SELECT (SELECT value FROM defaults WHERE setting_key = 'inventory_accno_id') AS inventory_accno_id, @@ -75,77 +75,71 @@ sub get_account { WHERE setting_key = 'fxloss_accno_id') AS fxloss_accno_id|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; + $ref = $sth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } + $sth->finish; - # check if we have any transactions - $query = qq| + # check if we have any transactions + $query = qq| SELECT trans_id FROM acc_trans WHERE chart_id = ? LIMIT 1|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}); - ($form->{orphaned}) = $sth->fetchrow_array(); - $form->{orphaned} = !$form->{orphaned}; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ); + ( $form->{orphaned} ) = $sth->fetchrow_array(); + $form->{orphaned} = !$form->{orphaned}; - $dbh->commit; + $dbh->commit; } - sub save_account { - my ($self, $myconfig, $form) = @_; - - # connect to database, turn off AutoCommit - my $dbh = $form->{dbh}; - - $form->{link} = ""; - foreach my $item ($form->{AR}, - $form->{AR_amount}, - $form->{AR_tax}, - $form->{AR_paid}, - $form->{AP}, - $form->{AP_amount}, - $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); - } - - chop $form->{link}; - - # strip blanks from accno - for (qw(accno gifi_accno)) { $form->{$_} =~ s/( |')//g } - - foreach my $item (qw(accno gifi_accno description)) { - $form->{$item} =~ s/-(-+)/-/g; - $form->{$item} =~ s/ ( )+/ /g; - } - - my $query; - my $sth; - - $form->{contra} *= 1; - - my @queryargs; - @queryargs = ($form->{accno}, $form->{description}, - $form->{charttype}, $form->{gifi_accno}, - $form->{category}, $form->{"link"}, - $form->{contra}); - # if we have an id then replace the old record - if ($form->{id}) { - $query = qq| + my ( $self, $myconfig, $form ) = @_; + + # connect to database, turn off AutoCommit + my $dbh = $form->{dbh}; + + $form->{link} = ""; + foreach my $item ( + $form->{AR}, $form->{AR_amount}, $form->{AR_tax}, + $form->{AR_paid}, $form->{AP}, $form->{AP_amount}, + $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); + } + + chop $form->{link}; + + # strip blanks from accno + for (qw(accno gifi_accno)) { $form->{$_} =~ s/( |')//g } + + foreach my $item (qw(accno gifi_accno description)) { + $form->{$item} =~ s/-(-+)/-/g; + $form->{$item} =~ s/ ( )+/ /g; + } + + my $query; + my $sth; + + $form->{contra} *= 1; + + my @queryargs; + @queryargs = ( + $form->{accno}, $form->{description}, $form->{charttype}, + $form->{gifi_accno}, $form->{category}, $form->{"link"}, + $form->{contra} + ); + + # if we have an id then replace the old record + if ( $form->{id} ) { + $query = qq| UPDATE chart SET accno = ?, description = ?, charttype = ?, @@ -154,102 +148,104 @@ sub save_account { link = ?, contra = ? WHERE id = ?|; - push @queryargs, $form->{id}; - } else { - $query = qq| + push @queryargs, $form->{id}; + } + else { + $query = qq| INSERT INTO chart (accno, description, charttype, gifi_accno, category, link, contra) VALUES (?, ?, ?, ?, ?, ?, ?)|; - } + } + + $sth = $dbh->prepare($query); + $sth->execute(@queryargs) || $form->dberror($query); + $sth->finish; - $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror($query); - $sth->finish; + $chart_id = $dbh->quote( $form->{id} ); - $chart_id = $dbh->quote($form->{id}); + if ( !$form->{id} ) { - if (! $form->{id}) { - # get id from chart - $query = qq| + # get id from chart + $query = qq| SELECT id FROM chart WHERE accno = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{accno}); - ($chart_id) = $sth->fetchrow_array(); - $sth->finish; - } + $sth = $dbh->prepare($query); + $sth->execute( $form->{accno} ); + ($chart_id) = $sth->fetchrow_array(); + $sth->finish; + } - if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{AR_tax} || $form->{AP_tax}) { + if ( $form->{IC_taxpart} + || $form->{IC_taxservice} + || $form->{AR_tax} + || $form->{AP_tax} ) + { - # add account if it doesn't exist in tax - $query = qq|SELECT chart_id + # add account if it doesn't exist in tax + $query = qq|SELECT chart_id FROM tax WHERE chart_id = $chart_id|; - my ($tax_id) = $dbh->selectrow_array($query); + my ($tax_id) = $dbh->selectrow_array($query); - # add tax if it doesn't exist - unless ($tax_id) { - $query = qq|INSERT INTO tax (chart_id, rate) + # add tax if it doesn't exist + unless ($tax_id) { + $query = qq|INSERT INTO tax (chart_id, rate) VALUES ($chart_id, 0)|; - $dbh->do($query) || $form->dberror($query); - } + $dbh->do($query) || $form->dberror($query); + } - } else { + } + else { - # remove tax - if ($form->{id}) { - $query = qq|DELETE FROM tax + # remove tax + if ( $form->{id} ) { + $query = qq|DELETE FROM tax WHERE chart_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - } - } + $dbh->do($query) || $form->dberror($query); + } + } - # commit - my $rc = $dbh->commit; + # commit + my $rc = $dbh->commit; - $rc; + $rc; } - - sub delete_account { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database, turn off AutoCommit - my $dbh = $form->{dbh}; - my $sth; - my $query = qq| + # connect to database, turn off AutoCommit + my $dbh = $form->{dbh}; + my $sth; + my $query = qq| SELECT count(*) FROM acc_trans WHERE chart_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}); - my ($rowcount) = $sth->fetchrow_array(); - - if ($rowcount) { - $form->error( - "Cannot delete accounts with associated transactions!" - ); - } - - - # delete chart of account record - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ); + my ($rowcount) = $sth->fetchrow_array(); + + if ($rowcount) { + $form->error( "Cannot delete accounts with associated transactions!" ); + } + + # delete chart of account record + $query = qq| DELETE FROM chart WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - # set inventory_accno_id, income_accno_id, expense_accno_id to defaults - $query = qq| + # set inventory_accno_id, income_accno_id, expense_accno_id to defaults + $query = qq| UPDATE parts SET inventory_accno_id = (SELECT value::int FROM defaults @@ -257,724 +253,711 @@ sub delete_account { 'inventory_accno_id') WHERE inventory_accno_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - for (qw(income_accno_id expense_accno_id)){ - $query = qq| + for (qw(income_accno_id expense_accno_id)) { + $query = qq| UPDATE parts SET $_ = (SELECT value::int FROM defaults WHERE setting_key = '$_') WHERE $_ = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - $sth->finish; - } + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + $sth->finish; + } - foreach my $table (qw(partstax customertax vendortax tax)) { - $query = qq| + foreach my $table (qw(partstax customertax vendortax tax)) { + $query = qq| DELETE FROM $table WHERE chart_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - $sth->finish; - } + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + $sth->finish; + } - # commit and redirect - my $rc = $dbh->commit; + # commit and redirect + my $rc = $dbh->commit; - $rc; + $rc; } - sub gifi_accounts { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - my $query = qq| + my $query = qq| SELECT accno, description FROM gifi ORDER BY accno|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{ALL} }, $ref; + } - $sth->finish; - $dbh->commit; + $sth->finish; + $dbh->commit; } - - sub get_gifi { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; - my $sth; + # connect to database + my $dbh = $form->{dbh}; + my $sth; - my $query = qq| + my $query = qq| SELECT accno, description FROM gifi WHERE accno = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{accno}) || $form->dberror($query); - ($form->{accno}, $form->{description}) = $sth->fetchrow_array(); + $sth = $dbh->prepare($query); + $sth->execute( $form->{accno} ) || $form->dberror($query); + ( $form->{accno}, $form->{description} ) = $sth->fetchrow_array(); - $sth->finish; + $sth->finish; - # check for transactions - $query = qq| + # check for transactions + $query = qq| SELECT count(*) FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) JOIN gifi g ON (c.gifi_accno = g.accno) WHERE g.accno = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{accno}) || $form->dberror($query); - ($numrows) = $sth->fetchrow_array; - if (($numrows * 1) == 0){ - $form->{orphaned} = 1; - } else { - $form->{orphaned} = 0; - } + $sth = $dbh->prepare($query); + $sth->execute( $form->{accno} ) || $form->dberror($query); + ($numrows) = $sth->fetchrow_array; + if ( ( $numrows * 1 ) == 0 ) { + $form->{orphaned} = 1; + } + else { + $form->{orphaned} = 0; + } - $dbh->commit; + $dbh->commit; } - sub save_gifi { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; + + my $dbh = $form->{dbh}; - my $dbh = $form->{dbh}; + $form->{accno} =~ s/( |')//g; - $form->{accno} =~ s/( |')//g; + foreach my $item (qw(accno description)) { + $form->{$item} =~ s/-(-+)/-/g; + $form->{$item} =~ s/ ( )+/ /g; + } - foreach my $item (qw(accno description)) { - $form->{$item} =~ s/-(-+)/-/g; - $form->{$item} =~ s/ ( )+/ /g; - } + my @queryargs = ( $form->{accno}, $form->{description} ); - my @queryargs = ($form->{accno}, $form->{description}); - # id is the old account number! - if ($form->{id}) { - $query = qq| + # id is the old account number! + if ( $form->{id} ) { + $query = qq| UPDATE gifi SET accno = ?, description = ? WHERE accno = ?|; - push @queryargs, $form->{id}; + push @queryargs, $form->{id}; - } else { - $query = qq| + } + else { + $query = qq| INSERT INTO gifi (accno, description) VALUES (?, ?)|; - } + } - $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror; - $sth->finish; - $dbh->commit; + $sth = $dbh->prepare($query); + $sth->execute(@queryargs) || $form->dberror; + $sth->finish; + $dbh->commit; } - sub delete_gifi { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - # id is the old account number! - $query = qq| + # id is the old account number! + $query = qq| DELETE FROM gifi WHERE accno = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - $sth->finish; - $dbh->commit; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + $sth->finish; + $dbh->commit; } - sub warehouses { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $form->sort_order(); - my $query = qq| + $form->sort_order(); + my $query = qq| SELECT id, description FROM warehouse ORDER BY description $form->{direction}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{ALL} }, $ref; + } - $sth->finish; - $dbh->commit; + $sth->finish; + $dbh->commit; } - sub get_warehouse { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; - my $sth; + # connect to database + my $dbh = $form->{dbh}; + my $sth; - my $query = qq| + my $query = qq| SELECT description FROM warehouse WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}) || $form->dberror($query); - ($form->{description}) = $sth->fetchrow_array; - $sth->finish; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + ( $form->{description} ) = $sth->fetchrow_array; + $sth->finish; - # see if it is in use - $query = qq| + # see if it is in use + $query = qq| SELECT count(*) FROM inventory WHERE warehouse_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}); + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ); - ($form->{orphaned}) = $sth->fetchrow_array; - if (($form->{orphaned} * 1) == 0){ - $form->{orphaned} = 1; - } else { - $form->{orphaned} = 0; - } + ( $form->{orphaned} ) = $sth->fetchrow_array; + if ( ( $form->{orphaned} * 1 ) == 0 ) { + $form->{orphaned} = 1; + } + else { + $form->{orphaned} = 0; + } - $dbh->commit; + $dbh->commit; } - sub save_warehouse { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - my $sth; - my @queryargs = ($form->{description}); + my $sth; + my @queryargs = ( $form->{description} ); - $form->{description} =~ s/-(-)+/-/g; - $form->{description} =~ s/ ( )+/ /g; + $form->{description} =~ s/-(-)+/-/g; + $form->{description} =~ s/ ( )+/ /g; - - if ($form->{id}) { - $query = qq| + if ( $form->{id} ) { + $query = qq| UPDATE warehouse SET description = ? WHERE id = ?|; - push @queryargs, $form->{id}; - } else { - $query = qq| + push @queryargs, $form->{id}; + } + else { + $query = qq| INSERT INTO warehouse (description) VALUES (?)|; - } + } - $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror($query); - $sth->finish; - $dbh->commit; + $sth = $dbh->prepare($query); + $sth->execute(@queryargs) || $form->dberror($query); + $sth->finish; + $dbh->commit; } - sub delete_warehouse { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $query = qq| + $query = qq| DELETE FROM warehouse WHERE id = ?|; - $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query); - $dbh->commit; + $dbh->prepare($query)->execute( $form->{id} ) || $form->dberror($query); + $dbh->commit; } - - sub departments { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $form->sort_order(); - my $query = qq|SELECT id, description, role + $form->sort_order(); + my $query = qq|SELECT id, description, role FROM department ORDER BY description $form->{direction}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{ALL} }, $ref; + } - $sth->finish; - $dbh->commit; + $sth->finish; + $dbh->commit; } - - sub get_department { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; - my $sth; + # connect to database + my $dbh = $form->{dbh}; + my $sth; - my $query = qq| + my $query = qq| SELECT description, role FROM department WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}); - ($form->{description}, $form->{role}) = $sth->fetchrow_array; - $sth->finish; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ); + ( $form->{description}, $form->{role} ) = $sth->fetchrow_array; + $sth->finish; - for (keys %$ref) { $form->{$_} = $ref->{$_} } + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } - # see if it is in use - $query = qq| + # see if it is in use + $query = qq| SELECT count(*) FROM dpt_trans WHERE department_id = ? |; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}); - ($form->{orphaned}) = $sth->fetchrow_array; - if (($form->{orphaned} * 1) == 0){ - $form->{orphaned} = 1; - } else { - $form->{orphaned} = 0; - } - - $dbh->commit; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ); + ( $form->{orphaned} ) = $sth->fetchrow_array; + if ( ( $form->{orphaned} * 1 ) == 0 ) { + $form->{orphaned} = 1; + } + else { + $form->{orphaned} = 0; + } + + $dbh->commit; } - sub save_department { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $form->{description} =~ s/-(-)+/-/g; - $form->{description} =~ s/ ( )+/ /g; - my $sth; - my @queryargs = ($form->{description}, $form->{role}); - if ($form->{id}) { - $query = qq| + $form->{description} =~ s/-(-)+/-/g; + $form->{description} =~ s/ ( )+/ /g; + my $sth; + my @queryargs = ( $form->{description}, $form->{role} ); + if ( $form->{id} ) { + $query = qq| UPDATE department SET description = ?, role = ? WHERE id = ?|; - push @queryargs, $form->{id}; + push @queryargs, $form->{id}; - } else { - $query = qq| + } + else { + $query = qq| INSERT INTO department (description, role) VALUES (?, ?)|; - } + } - $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror($query); - $dbh->commit; + $sth = $dbh->prepare($query); + $sth->execute(@queryargs) || $form->dberror($query); + $dbh->commit; } - sub delete_department { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $query = qq| + $query = qq| DELETE FROM department WHERE id = ?|; - $dbh->prepare($query)->execute($form->{id}); - $dbh->commit; + $dbh->prepare($query)->execute( $form->{id} ); + $dbh->commit; } - sub business { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $form->sort_order(); - my $query = qq| + $form->sort_order(); + my $query = qq| SELECT id, description, discount FROM business ORDER BY description $form->{direction}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{ALL} }, $ref; + } - $sth->finish; - $dbh->commit; + $sth->finish; + $dbh->commit; } - sub get_business { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - my $query = qq| + my $query = qq| SELECT description, discount FROM business WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}); - ($form->{description}, $form->{discount}) = $sth->fetchrow_array(); - $dbh->commit; + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ); + ( $form->{description}, $form->{discount} ) = $sth->fetchrow_array(); + $dbh->commit; } - sub save_business { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $form->{description} =~ s/-(-)+/-/g; - $form->{description} =~ s/ ( )+/ /g; - $form->{discount} /= 100; + $form->{description} =~ s/-(-)+/-/g; + $form->{description} =~ s/ ( )+/ /g; + $form->{discount} /= 100; - my $sth; - my @queryargs = ($form->{description}, $form->{discount}); + my $sth; + my @queryargs = ( $form->{description}, $form->{discount} ); - if ($form->{id}) { - $query = qq| + if ( $form->{id} ) { + $query = qq| UPDATE business SET description = ?, discount = ? WHERE id = ?|; - push @queryargs, $form->{id}; + push @queryargs, $form->{id}; - } else { - $query = qq|INSERT INTO business (description, discount) + } + else { + $query = qq|INSERT INTO business (description, discount) VALUES (?, ?)|; - } + } - $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query); - $dbh->commit; + $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query); + $dbh->commit; } - sub delete_business { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $query = qq| + $query = qq| DELETE FROM business WHERE id = ?|; - $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query); - $dbh->commit; + $dbh->prepare($query)->execute( $form->{id} ) || $form->dberror($query); + $dbh->commit; } - sub sic { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $form->{sort} = "code" unless $form->{sort}; - my @a = qw(code description); + $form->{sort} = "code" unless $form->{sort}; + my @a = qw(code description); - my %ordinal = ( code => 1, - description => 3 ); + my %ordinal = ( + code => 1, + description => 3 + ); - my $sortorder = $form->sort_order(\@a, \%ordinal); + my $sortorder = $form->sort_order( \@a, \%ordinal ); - my $query = qq|SELECT code, sictype, description + my $query = qq|SELECT code, sictype, description FROM sic ORDER BY $sortorder|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{ALL} }, $ref; + } - $sth->finish; - $dbh->commit; + $sth->finish; + $dbh->commit; } - sub get_sic { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - my $query = qq| + my $query = qq| SELECT code, sictype, description FROM sic - WHERE code = |.$dbh->quote($form->{code}); + WHERE code = | . $dbh->quote( $form->{code} ); - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } + my $ref = $sth->fetchrow_hashref(NAME_lc); + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } - $sth->finish; - $dbh->commit; + $sth->finish; + $dbh->commit; } - sub save_sic { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - foreach my $item (qw(code description)) { - $form->{$item} =~ s/-(-)+/-/g; - } - my $sth; - @queryargs = ($form->{code}, $form->{sictype}, $form->{description}); - # if there is an id - if ($form->{id}) { - $query = qq| + foreach my $item (qw(code description)) { + $form->{$item} =~ s/-(-)+/-/g; + } + my $sth; + @queryargs = ( $form->{code}, $form->{sictype}, $form->{description} ); + + # if there is an id + if ( $form->{id} ) { + $query = qq| UPDATE sic SET code = ?, sictype = ?, description = ? WHERE code = ?)|; - push @queryargs, $form->{id}; + push @queryargs, $form->{id}; - } else { - $query = qq| + } + else { + $query = qq| INSERT INTO sic (code, sictype, description) VALUES (?, ?, ?)|; - } + } - $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query); - $dbh->commit; + $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query); + $dbh->commit; } - sub delete_sic { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $query = qq| + $query = qq| DELETE FROM sic WHERE code = ?|; - $dbh->prepare($query)->execute($form->{code}); - $dbh->commit; + $dbh->prepare($query)->execute( $form->{code} ); + $dbh->commit; } - sub language { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $form->{sort} = "code" unless $form->{sort}; - my @a = qw(code description); + $form->{sort} = "code" unless $form->{sort}; + my @a = qw(code description); - my %ordinal = ( code => 1, - description => 2 ); + my %ordinal = ( + code => 1, + description => 2 + ); - my $sortorder = $form->sort_order(\@a, \%ordinal); + my $sortorder = $form->sort_order( \@a, \%ordinal ); - my $query = qq| + my $query = qq| SELECT code, description FROM language ORDER BY $sortorder|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{ALL} }, $ref; + } - $sth->finish; - $dbh->commit; + $sth->finish; + $dbh->commit; } - sub get_language { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - ## needs fixing (SELECT *...) - my $query = qq| + ## needs fixing (SELECT *...) + my $query = qq| SELECT * FROM language WHERE code = ?|; - my $sth = $dbh->prepare($query); - $sth->execute($form->{code}) || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute( $form->{code} ) || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); + my $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } - $sth->finish; - $dbh->commit; + $sth->finish; + $dbh->commit; } - sub save_language { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; + + # connect to database + my $dbh = $form->{dbh}; - # connect to database - my $dbh = $form->{dbh}; + $form->{code} =~ s/ //g; - $form->{code} =~ s/ //g; + foreach my $item (qw(code description)) { + $form->{$item} =~ s/-(-)+/-/g; + $form->{$item} =~ s/ ( )+/-/g; + } + my $sth; + my @queryargs = ( $form->{code}, $form->{description} ); - foreach my $item (qw(code description)) { - $form->{$item} =~ s/-(-)+/-/g; - $form->{$item} =~ s/ ( )+/-/g; - } - my $sth; - my @queryargs = ($form->{code}, $form->{description}); - # if there is an id - if ($form->{id}) { - $query = qq| + # if there is an id + if ( $form->{id} ) { + $query = qq| UPDATE language SET code = ?, description = ? WHERE code = ?|; - push @queryargs, $form->{id}; + push @queryargs, $form->{id}; - } else { - $query = qq| + } + else { + $query = qq| INSERT INTO language (code, description) VALUES (?, ?)|; - } + } - $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query); - $dbh->commit; + $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query); + $dbh->commit; } - sub delete_language { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $query = qq| + $query = qq| DELETE FROM language - WHERE code = |.$dbh->quote($form->{code}); + WHERE code = | . $dbh->quote( $form->{code} ); - $dbh->do($query) || $form->dberror($query); - $dbh->{dbh}; + $dbh->do($query) || $form->dberror($query); + $dbh->{dbh}; } - sub recurring_transactions { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - my $dbh = $form->{dbh}; + my $dbh = $form->{dbh}; - my $query = qq|SELECT value FROM defaults where setting_key = 'curr'|; + my $query = qq|SELECT value FROM defaults where setting_key = 'curr'|; - my ($defaultcurrency) = $dbh->selectrow_array($query); - $defaultcurrency = $dbh->quote($defaultcurrency =~ s/:.*//g); + my ($defaultcurrency) = $dbh->selectrow_array($query); + $defaultcurrency = $dbh->quote( $defaultcurrency =~ s/:.*//g ); - $form->{sort} ||= "nextdate"; - my @a = ($form->{sort}); - my $sortorder = $form->sort_order(\@a); + $form->{sort} ||= "nextdate"; + my @a = ( $form->{sort} ); + my $sortorder = $form->sort_order( \@a ); - $query = qq| + $query = qq| SELECT 'ar' AS module, 'ar' AS transaction, a.invoice, n.name AS description, a.amount, s.*, se.formname AS recurringemail, @@ -1069,77 +1052,90 @@ sub recurring_transactions { ORDER BY $sortorder|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $id; - my $transaction; - my %e = (); - my %p = (); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + my $id; + my $transaction; + my %e = (); + my %p = (); - $ref->{exchangerate} ||= 1; + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { - if ($ref->{id} != $id) { + $ref->{exchangerate} ||= 1; - if (%e) { - $form->{transactions}{$transaction}->[$i]->{recurringemail} = ""; - for (keys %e) { - $form->{transactions}{$transaction}->[$i]->{recurringemail} .= "${_}:"; - } - chop $form->{transactions}{$transaction}->[$i]->{recurringemail}; - } + if ( $ref->{id} != $id ) { - if (%p) { - $form->{transactions}{$transaction}->[$i]->{recurringprint} = ""; - for (keys %p) { $form->{transactions}{$transaction}->[$i]->{recurringprint} .= "${_}:" } - chop $form->{transactions}{$transaction}->[$i]->{recurringprint}; - } + if (%e) { + $form->{transactions}{$transaction}->[$i]->{recurringemail} = + ""; + for ( keys %e ) { + $form->{transactions}{$transaction}->[$i] + ->{recurringemail} .= "${_}:"; + } + chop $form->{transactions}{$transaction}->[$i] + ->{recurringemail}; + } - %e = (); - %p = (); + if (%p) { + $form->{transactions}{$transaction}->[$i]->{recurringprint} = + ""; + for ( keys %p ) { + $form->{transactions}{$transaction}->[$i] + ->{recurringprint} .= "${_}:"; + } + chop $form->{transactions}{$transaction}->[$i] + ->{recurringprint}; + } - push @{ $form->{transactions}{$ref->{transaction}} }, $ref; + %e = (); + %p = (); - $id = $ref->{id}; - $i = $#{ $form->{transactions}{$ref->{transaction}} }; + push @{ $form->{transactions}{ $ref->{transaction} } }, $ref; - } + $id = $ref->{id}; + $i = $#{ $form->{transactions}{ $ref->{transaction} } }; - $transaction = $ref->{transaction}; + } - $e{$ref->{recurringemail}} = 1 if $ref->{recurringemail}; - $p{$ref->{recurringprint}} = 1 if $ref->{recurringprint}; + $transaction = $ref->{transaction}; - } + $e{ $ref->{recurringemail} } = 1 if $ref->{recurringemail}; + $p{ $ref->{recurringprint} } = 1 if $ref->{recurringprint}; - $sth->finish; + } - # this is for the last row - if (%e) { - $form->{transactions}{$transaction}->[$i]->{recurringemail} = ""; - for (keys %e) { $form->{transactions}{$transaction}->[$i]->{recurringemail} .= "${_}:" } - chop $form->{transactions}{$transaction}->[$i]->{recurringemail}; - } + $sth->finish; - if (%p) { - $form->{transactions}{$transaction}->[$i]->{recurringprint} = ""; - for (keys %p) { $form->{transactions}{$transaction}->[$i]->{recurringprint} .= "${_}:" } - chop $form->{transactions}{$transaction}->[$i]->{recurringprint}; - } + # this is for the last row + if (%e) { + $form->{transactions}{$transaction}->[$i]->{recurringemail} = ""; + for ( keys %e ) { + $form->{transactions}{$transaction}->[$i]->{recurringemail} .= + "${_}:"; + } + chop $form->{transactions}{$transaction}->[$i]->{recurringemail}; + } + if (%p) { + $form->{transactions}{$transaction}->[$i]->{recurringprint} = ""; + for ( keys %p ) { + $form->{transactions}{$transaction}->[$i]->{recurringprint} .= + "${_}:"; + } + chop $form->{transactions}{$transaction}->[$i]->{recurringprint}; + } - $dbh->commit; + $dbh->commit; } sub recurring_details { - my ($self, $myconfig, $form, $id) = @_; + my ( $self, $myconfig, $form, $id ) = @_; - my $dbh = $form->{dbh}; - my $query = qq| + my $dbh = $form->{dbh}; + my $query = qq| SELECT s.*, ar.id AS arid, ar.invoice AS arinvoice, ap.id AS apid, ap.invoice AS apinvoice, ar.duedate - ar.transdate AS overdue, @@ -1152,370 +1148,382 @@ sub recurring_details { LEFT JOIN oe ON (oe.id = s.id) WHERE s.id = ?|; - my $sth = $dbh->prepare($query); - $sth->execute($id) || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute($id) || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); - $form->{vc} = "customer" if $ref->{customer_id}; - $form->{vc} = "vendor" if $ref->{vendor_id}; - for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; + my $ref = $sth->fetchrow_hashref(NAME_lc); + $form->{vc} = "customer" if $ref->{customer_id}; + $form->{vc} = "vendor" if $ref->{vendor_id}; + for ( keys %$ref ) { $form->{$_} = $ref->{$_} } + $sth->finish; - $form->{invoice} = ($form->{arid} && $form->{arinvoice}); - $form->{invoice} = ($form->{apid} && $form->{apinvoice}) unless $form->{invoice}; + $form->{invoice} = ( $form->{arid} && $form->{arinvoice} ); + $form->{invoice} = ( $form->{apid} && $form->{apinvoice} ) + unless $form->{invoice}; - $query = qq| + $query = qq| SELECT * FROM recurringemail WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($id) || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute($id) || $form->dberror($query); - $form->{recurringemail} = ""; + $form->{recurringemail} = ""; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{recurringemail} .= "$ref->{formname}:$ref->{format}:"; - $form->{message} = $ref->{message}; - } + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $form->{recurringemail} .= "$ref->{formname}:$ref->{format}:"; + $form->{message} = $ref->{message}; + } - $sth->finish; + $sth->finish; - $query = qq| + $query = qq| SELECT * FROM recurringprint WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($id) || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute($id) || $form->dberror($query); - $form->{recurringprint} = ""; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{recurringprint} .= - "$ref->{formname}:$ref->{format}:$ref->{printer}:"; - } + $form->{recurringprint} = ""; + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $form->{recurringprint} .= + "$ref->{formname}:$ref->{format}:$ref->{printer}:"; + } - $sth->finish; + $sth->finish; - chop $form->{recurringemail}; - chop $form->{recurringprint}; + chop $form->{recurringemail}; + chop $form->{recurringprint}; - for (qw(arinvoice apinvoice)) { delete $form->{$_} } + for (qw(arinvoice apinvoice)) { delete $form->{$_} } - $dbh->commit; + $dbh->commit; } - sub update_recurring { - my ($self, $myconfig, $form, $id) = @_; + my ( $self, $myconfig, $form, $id ) = @_; - my $dbh = $form->{dbh}; + my $dbh = $form->{dbh}; - $id = $dbh->quote($id); - my $query = qq| + $id = $dbh->quote($id); + my $query = qq| SELECT nextdate, repeat, unit FROM recurring WHERE id = $id|; - my ($nextdate, $repeat, $unit) = $dbh->selectrow_array($query); + my ( $nextdate, $repeat, $unit ) = $dbh->selectrow_array($query); + + $nextdate = $dbh->quote($nextdate); + my $interval = $dbh->quote("$repeat $unit"); - $nextdate = $dbh->quote($nextdate); - my $interval = $dbh->quote("$repeat $unit"); - # check if it is the last date - $query = qq| + # check if it is the last date + $query = qq| SELECT (date $nextdate + interval $interval) > enddate FROM recurring WHERE id = $id|; - my ($last_repeat) = $dbh->selectrow_array($query); - if ($last_repeat) { - $advance{$myconfig->{dbdriver}} = "NULL"; - } + my ($last_repeat) = $dbh->selectrow_array($query); + if ($last_repeat) { + $advance{ $myconfig->{dbdriver} } = "NULL"; + } - $query = qq| + $query = qq| UPDATE recurring SET nextdate = (date $nextdate + interval $interval) WHERE id = $id|; - $dbh->do($query) || $form->dberror($query); + $dbh->do($query) || $form->dberror($query); - $dbh->commit; + $dbh->commit; } - sub check_template_name { - my ($self, $myconfig, $form) = @_; - - my @allowedsuff = qw(css tex txt html xml); - if ($form->{file} =~ /^(.:)*?\/|:|\.\.\/|^\//){ - $form->error("Directory transversal not allowed."); - } - if ($form->{file} =~ /^${LedgerSMB::Sysconfig::backuppath}\//){ - $form->error("Not allowed to access ${LedgerSMB::Sysconfig::backuppath}/ with this method"); - } - my $whitelisted = 0; - for (@allowedsuff){ - if ($form->{file} =~ /$_$/){ - $whitelisted = 1; - } - } - if (!$whitelisted){ - $form->error("Error: File is of type that is not allowed."); - } - - if ($form->{file} !~ /^$myconfig->{templates}\//){ - $form->error("Not in a whitelisted directory: $form->{file}") unless $form->{file} =~ /^css\//; - } + my ( $self, $myconfig, $form ) = @_; + + my @allowedsuff = qw(css tex txt html xml); + if ( $form->{file} =~ /^(.:)*?\/|:|\.\.\/|^\// ) { + $form->error("Directory transversal not allowed."); + } + if ( $form->{file} =~ /^${LedgerSMB::Sysconfig::backuppath}\// ) { + $form->error( +"Not allowed to access ${LedgerSMB::Sysconfig::backuppath}/ with this method" + ); + } + my $whitelisted = 0; + for (@allowedsuff) { + if ( $form->{file} =~ /$_$/ ) { + $whitelisted = 1; + } + } + if ( !$whitelisted ) { + $form->error("Error: File is of type that is not allowed."); + } + + if ( $form->{file} !~ /^$myconfig->{templates}\// ) { + $form->error("Not in a whitelisted directory: $form->{file}") + unless $form->{file} =~ /^css\//; + } } - sub load_template { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - $self->check_template_name(\%$myconfig, \%$form); - open(TEMPLATE, '<', "$form->{file}") or $form->error("$form->{file} : $!"); + $self->check_template_name( \%$myconfig, \%$form ); + open( TEMPLATE, '<', "$form->{file}" ) + or $form->error("$form->{file} : $!"); - while (