diff options
Diffstat (limited to 'LedgerSMB/CP.pm')
-rwxr-xr-x | LedgerSMB/CP.pm | 936 |
1 files changed, 463 insertions, 473 deletions
diff --git a/LedgerSMB/CP.pm b/LedgerSMB/CP.pm index b5aa6258..7d20ccc3 100755 --- a/LedgerSMB/CP.pm +++ b/LedgerSMB/CP.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. @@ -37,354 +37,354 @@ package CP; use LedgerSMB::Sysconfig; - - sub new { - my ($type, $countrycode) = @_; + my ( $type, $countrycode ) = @_; - $self = {}; + $self = {}; - use LedgerSMB::Num2text; - use LedgerSMB::Locale; - $self->{'locale'} = LedgerSMB::Locale->get_handle($countrycode); + use LedgerSMB::Num2text; + use LedgerSMB::Locale; + $self->{'locale'} = LedgerSMB::Locale->get_handle($countrycode); - bless $self, $type; + bless $self, $type; } - sub paymentaccounts { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - my $dbh = $form->{dbh}; + my $dbh = $form->{dbh}; - my $query = qq|SELECT accno, description, link + my $query = qq|SELECT accno, description, link FROM chart WHERE link LIKE ? ORDER BY accno|; - my $sth = $dbh->prepare($query); - $sth->execute("%$form->{ARAP}%") || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute("%$form->{ARAP}%") || $form->dberror($query); - $form->{PR}{$form->{ARAP}} = (); - $form->{PR}{"$form->{ARAP}_paid"} = (); + $form->{PR}{ $form->{ARAP} } = (); + $form->{PR}{"$form->{ARAP}_paid"} = (); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { - foreach my $item (split /:/, $ref->{link}) { + foreach my $item ( split /:/, $ref->{link} ) { - if ($item eq $form->{ARAP}) { - push @{ $form->{PR}{$form->{ARAP}} }, $ref; - } + if ( $item eq $form->{ARAP} ) { + push @{ $form->{PR}{ $form->{ARAP} } }, $ref; + } - if ($item eq "$form->{ARAP}_paid") { - push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref; - } - } - } + if ( $item eq "$form->{ARAP}_paid" ) { + push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref; + } + } + } - $sth->finish; + $sth->finish; - # get currencies and closedto - $query = qq| + # get currencies and closedto + $query = qq| SELECT value, (SELECT value FROM defaults WHERE setting_key = 'closedto'), current_date FROM defaults WHERE setting_key = 'curr'|; - ($form->{currencies}, $form->{closedto}, $form->{datepaid}) = $dbh->selectrow_array($query); + ( $form->{currencies}, $form->{closedto}, $form->{datepaid} ) = + $dbh->selectrow_array($query); - if ($form->{payment} eq 'payments') { - # get language codes - $query = qq|SELECT * + if ( $form->{payment} eq 'payments' ) { + + # get language codes + $query = qq|SELECT * FROM language ORDER BY 2|; - $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $self->dberror($query); - $form->{all_language} = (); + $form->{all_language} = (); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_language} }, $ref; - } + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{all_language} }, $ref; + } - $sth->finish; + $sth->finish; - $form->all_departments($myconfig, $dbh, $form->{vc}); - } + $form->all_departments( $myconfig, $dbh, $form->{vc} ); + } - $dbh->commit; + $dbh->commit; } - sub get_openvc { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - my $dbh = $form->{dbh}; + my $dbh = $form->{dbh}; - my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap'; - my $query = qq|SELECT count(*) + my $arap = ( $form->{vc} eq 'customer' ) ? 'ar' : 'ap'; + my $query = qq|SELECT count(*) FROM $form->{vc} ct, $arap a WHERE a.$form->{vc}_id = ct.id AND a.amount != a.paid|; - my ($count) = $dbh->selectrow_array($query); + my ($count) = $dbh->selectrow_array($query); - my $sth; - my $ref; - my $i = 0; + my $sth; + my $ref; + my $i = 0; - my $where = qq|WHERE a.$form->{vc}_id = ct.id + my $where = qq|WHERE a.$form->{vc}_id = ct.id AND a.amount != a.paid|; - if ($form->{$form->{vc}}) { - my $var = $dbh->quote($form->like(lc $form->{$form->{vc}})); - $where .= " AND lower(name) LIKE $var"; - } + if ( $form->{ $form->{vc} } ) { + my $var = $dbh->quote( $form->like( lc $form->{ $form->{vc} } ) ); + $where .= " AND lower(name) LIKE $var"; + } - # build selection list - $query = qq|SELECT DISTINCT ct.* + # build selection list + $query = qq|SELECT DISTINCT ct.* FROM $form->{vc} ct, $arap a $where ORDER BY name|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $i++; - push @{ $form->{name_list} }, $ref; - } + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $i++; + push @{ $form->{name_list} }, $ref; + } - $sth->finish; + $sth->finish; - $form->all_departments($myconfig, $dbh, $form->{vc}); + $form->all_departments( $myconfig, $dbh, $form->{vc} ); - # get language codes - $query = qq|SELECT * + # get language codes + $query = qq|SELECT * FROM language ORDER BY 2|; - $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $self->dberror($query); - $form->{all_language} = (); + $form->{all_language} = (); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_language} }, $ref; - } + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{all_language} }, $ref; + } - $sth->finish; + $sth->finish; - # get currency for first name - if (@{ $form->{name_list} }) { + # get currency for first name + if ( @{ $form->{name_list} } ) { - # Chris T: I don't like this but it seems safe injection-wise - # Leaving it so we can change it when we go to a new system - $query = qq|SELECT curr + # Chris T: I don't like this but it seems safe injection-wise + # Leaving it so we can change it when we go to a new system + $query = qq|SELECT curr FROM $form->{vc} WHERE id = $form->{name_list}->[0]->{id}|; - ($form->{currency}) = $dbh->selectrow_array($query); - $form->{currency} ||= $form->{defaultcurrency}; - } + ( $form->{currency} ) = $dbh->selectrow_array($query); + $form->{currency} ||= $form->{defaultcurrency}; + } - $dbh->commit; + $dbh->commit; - $i; + $i; } - sub get_openinvoices { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - my $null; - my $department_id; + my $null; + my $department_id; - # connect to database - my $dbh = $form->{dbh}; + # connect to database + my $dbh = $form->{dbh}; - $vc_id = $dbh->quote($form->{"$form->{vc}_id"}); - my $where = qq|WHERE a.$form->{vc}_id = $vc_id + $vc_id = $dbh->quote( $form->{"$form->{vc}_id"} ); + my $where = qq|WHERE a.$form->{vc}_id = $vc_id AND a.amount != a.paid|; - $curr = $dbh->quote($form->{currency}); - $where .= qq| AND a.curr = $curr| if $form->{currency}; + $curr = $dbh->quote( $form->{currency} ); + $where .= qq| AND a.curr = $curr| if $form->{currency}; - my $sortorder = "transdate, invnumber"; + my $sortorder = "transdate, invnumber"; - my ($buysell); + my ($buysell); - if ($form->{vc} eq 'customer') { - $buysell = "buy"; - } else { - $buysell = "sell"; - } + if ( $form->{vc} eq 'customer' ) { + $buysell = "buy"; + } + else { + $buysell = "sell"; + } - if ($form->{payment} eq 'payments') { + if ( $form->{payment} eq 'payments' ) { - $where = qq|WHERE a.amount != a.paid|; - $where .= qq| AND a.curr = $curr| if $form->{currency}; + $where = qq|WHERE a.amount != a.paid|; + $where .= qq| AND a.curr = $curr| if $form->{currency}; - if ($form->{duedatefrom}) { - $where .= qq| AND a.duedate >= - |.$dbh->quote($form->{duedatefrom}); - } + if ( $form->{duedatefrom} ) { + $where .= qq| AND a.duedate >= + | . $dbh->quote( $form->{duedatefrom} ); + } - if ($form->{duedateto}) { - $where .= qq| AND a.duedate <= |. - $dbh->quote($form->{duedateto}); - } + if ( $form->{duedateto} ) { + $where .= + qq| AND a.duedate <= | . $dbh->quote( $form->{duedateto} ); + } - $sortorder = "name, transdate"; - } + $sortorder = "name, transdate"; + } + ( $null, $department_id ) = split /--/, $form->{department}; - ($null, $department_id) = split /--/, $form->{department}; + if ($department_id) { + $where .= qq| AND a.department_id = $department_id|; + } - if ($department_id) { - $where .= qq| AND a.department_id = $department_id|; - } - - my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, + my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, a.curr, c.name, a.$form->{vc}_id, c.language_code FROM $form->{arap} a JOIN $form->{vc} c ON (c.id = a.$form->{vc}_id) $where ORDER BY $sortorder|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - $query = qq|SELECT s.spoolfile + $query = qq|SELECT s.spoolfile FROM status s WHERE s.formname = '$form->{formname}' AND s.trans_id = ?|; - my $vth = $dbh->prepare($query); - - my $spoolfile; + my $vth = $dbh->prepare($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + my $spoolfile; - # if this is a foreign currency transaction get exchangerate - $ref->{exchangerate} = - $form->get_exchangerate($dbh, - $ref->{curr}, - $ref->{transdate}, - $buysell) - if ($form->{currency} - ne $form->{defaultcurrency}); + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { - $vth->execute($ref->{id}); - $ref->{queue} = ""; - - while (($spoolfile) = $vth->fetchrow_array) { - $ref->{queued} .= "$form->{formname} $spoolfile "; - } + # if this is a foreign currency transaction get exchangerate + $ref->{exchangerate} = + $form->get_exchangerate( $dbh, $ref->{curr}, $ref->{transdate}, + $buysell ) + if ( $form->{currency} ne $form->{defaultcurrency} ); - $vth->finish; - $ref->{queued} =~ s/ +$//g; + $vth->execute( $ref->{id} ); + $ref->{queue} = ""; - push @{ $form->{PR} }, $ref; - } + while ( ($spoolfile) = $vth->fetchrow_array ) { + $ref->{queued} .= "$form->{formname} $spoolfile "; + } - $sth->finish; - $dbh->commit; + $vth->finish; + $ref->{queued} =~ s/ +$//g; -} + push @{ $form->{PR} }, $ref; + } + $sth->finish; + $dbh->commit; +} sub post_payment { - 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 $sth; + my $sth; - my ($paymentaccno) = split /--/, $form->{account}; + my ($paymentaccno) = split /--/, $form->{account}; - # if currency ne defaultcurrency update exchangerate - if ($form->{currency} ne $form->{defaultcurrency}) { + # if currency ne defaultcurrency update exchangerate + if ( $form->{currency} ne $form->{defaultcurrency} ) { - $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate}); + $form->{exchangerate} = + $form->parse_amount( $myconfig, $form->{exchangerate} ); - if ($form->{vc} eq 'customer') { - $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0); - } else { - $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate}); - } + if ( $form->{vc} eq 'customer' ) { + $form->update_exchangerate( $dbh, $form->{currency}, + $form->{datepaid}, $form->{exchangerate}, 0 ); + } + else { + $form->update_exchangerate( $dbh, $form->{currency}, + $form->{datepaid}, 0, $form->{exchangerate} ); + } - } else { - $form->{exchangerate} = 1; - } + } + else { + $form->{exchangerate} = 1; + } - my $query = qq| + my $query = qq| 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); - my ($buysell); + my ($buysell); - if ($form->{vc} eq 'customer') { - $buysell = "buy"; - } else { - $buysell = "sell"; - } + if ( $form->{vc} eq 'customer' ) { + $buysell = "buy"; + } + else { + $buysell = "sell"; + } - my $ml; - my $where; + my $ml; + my $where; - if ($form->{ARAP} eq 'AR') { + if ( $form->{ARAP} eq 'AR' ) { - $ml = 1; - $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |; + $ml = 1; + $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |; - } else { + } + else { - $ml = -1; - $where = qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |; + $ml = -1; + $where = + qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |; - } + } - my $paymentamount = $form->parse_amount($myconfig, $form->{amount}); + my $paymentamount = $form->parse_amount( $myconfig, $form->{amount} ); - # query to retrieve paid amount - $query = qq|SELECT paid + # query to retrieve paid amount + $query = qq|SELECT paid FROM $form->{arap} WHERE id = ? FOR UPDATE|; - my $pth = $dbh->prepare($query) || $form->dberror($query); + my $pth = $dbh->prepare($query) || $form->dberror($query); - my %audittrail; + my %audittrail; - # go through line by line - for my $i (1 .. $form->{rowcount}) { + # go through line by line + for my $i ( 1 .. $form->{rowcount} ) { - $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}); - $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"}); + $form->{"paid_$i"} = + $form->parse_amount( $myconfig, $form->{"paid_$i"} ); + $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} ); - if ($form->{"checked_$i"} && $form->{"paid_$i"}) { + if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) { - $paymentamount -= $form->{"paid_$i"}; + $paymentamount -= $form->{"paid_$i"}; - # get exchangerate for original - $query = qq| + # get exchangerate for original + $query = qq| SELECT $buysell FROM exchangerate e JOIN $form->{arap} a @@ -392,42 +392,41 @@ sub post_payment { WHERE e.curr = ? AND a.id = ?|; - my $sth = $dbh->prepare($query); - $sth->execute($form->{currency}, $form->{"id_$i"}); - my ($exchangerate) = $sth->fetchrow_array(); + my $sth = $dbh->prepare($query); + $sth->execute( $form->{currency}, $form->{"id_$i"} ); + my ($exchangerate) = $sth->fetchrow_array(); - $exchangerate = 1 unless $exchangerate; + $exchangerate = 1 unless $exchangerate; - $query = qq| + $query = qq| SELECT c.id FROM chart c JOIN acc_trans a ON (a.chart_id = c.id) WHERE $where AND a.trans_id = ?|; - my $sth = $dbh->prepare($query); - $sth->execute($form->{"id_$i"}); - my ($id) = $sth->fetchrow_array; + my $sth = $dbh->prepare($query); + $sth->execute( $form->{"id_$i"} ); + my ($id) = $sth->fetchrow_array; - $amount = - $form->round_amount( - $form->{"paid_$i"} * $exchangerate, 2); + $amount = + $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 ); - # add AR/AP - $query = qq| + # add AR/AP + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, transdate, amount) VALUES (?, ?, ?, ?)|; - $sth = $dbh->prepare($query); - $sth->execute($form->{"id_$i"}, $id, - $form->{datepaid}, $amount * $ml) - || $form->dberror($query, __file__, __line__); + $sth = $dbh->prepare($query); + $sth->execute( $form->{"id_$i"}, $id, $form->{datepaid}, + $amount * $ml ) + || $form->dberror( $query, __file__, __line__ ); - # add payment - $query = qq| + # add payment + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, memo) @@ -435,24 +434,21 @@ sub post_payment { FROM chart WHERE accno = ?), ?, ?, ?, ?)|; - $sth = $dbh->prepare($query); - $sth->execute( - $form->{"id_$i"}, $paymentaccno, - $form->{datepaid}, - $form->{"paid_$i"} * $ml * -1, - $form->{source}, $form->{memo}) - || $form->dberror( - $query, 'CP.pm', 444); - - # add exchangerate difference if currency ne defaultcurrency - $amount = $form->round_amount( - $form->{"paid_$i"} * - ($form->{exchangerate} - 1), - 2); - - if ($amount) { - # exchangerate difference - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( $form->{"id_$i"}, $paymentaccno, $form->{datepaid}, + $form->{"paid_$i"} * $ml * -1, + $form->{source}, $form->{memo} ) + || $form->dberror( $query, 'CP.pm', 444 ); + + # add exchangerate difference if currency ne defaultcurrency + $amount = + $form->round_amount( + $form->{"paid_$i"} * ( $form->{exchangerate} - 1 ), 2 ); + + if ($amount) { + + # exchangerate difference + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, cleared, @@ -462,34 +458,29 @@ sub post_payment { WHERE accno = ?), ?, ?, '0', '1', ?)|; - $sth = $dbh->prepare($query); - $sth->execute( - $form->{"id_$i"}, $paymentaccno, - $form->{datepaid}, $amount * $ml * -1, - $form->{source}) - || $form->dberror( - $query, 'CP.pm', 470); - - # gain/loss - $amount = - ($form->round_amount( - $form->{"paid_$i"} * - $exchangerate, - 2) - - $form->round_amount( - $form->{"paid_$i"} * - $form->{exchangerate}, - 2)) - * $ml * -1; - - if ($amount) { - - my $accno_id = - ($amount > 0) - ? $fxgain_accno_id - : $fxloss_accno_id; - - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( + $form->{"id_$i"}, $paymentaccno, $form->{datepaid}, + $amount * $ml * -1, $form->{source} + ) || $form->dberror( $query, 'CP.pm', 470 ); + + # gain/loss + $amount = ( + $form->round_amount( + $form->{"paid_$i"} * $exchangerate, 2 + ) - $form->round_amount( + $form->{"paid_$i"} * $form->{exchangerate}, 2 + ) + ) * $ml * -1; + + if ($amount) { + + my $accno_id = + ( $amount > 0 ) + ? $fxgain_accno_id + : $fxloss_accno_id; + + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, @@ -497,172 +488,176 @@ sub post_payment { amount, cleared, fx_transaction) VALUES (?, ?, ?, ?, '0', '1')|; - $sth = $dbh->prepare($query); - $sth->execute( - $form->{"id_$i"}, $accno_id, - $form->{datepaid}, $amount) - || $form->dberror( - $query, - 'CP.pm', - 506); - } - } - - $form->{"paid_$i"} = - $form->round_amount( - $form->{"paid_$i"} * $exchangerate, 2); - - $pth->execute($form->{"id_$i"}) || $form->dberror; - ($amount) = $pth->fetchrow_array; - $pth->finish; - - $amount += $form->{"paid_$i"}; - - # update AR/AP transaction - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( + $form->{"id_$i"}, $accno_id, + $form->{datepaid}, $amount + ) || $form->dberror( $query, 'CP.pm', 506 ); + } + } + + $form->{"paid_$i"} = + $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 ); + + $pth->execute( $form->{"id_$i"} ) || $form->dberror; + ($amount) = $pth->fetchrow_array; + $pth->finish; + + $amount += $form->{"paid_$i"}; + + # update AR/AP transaction + $query = qq| UPDATE $form->{arap} SET paid = ?, datepaid = ? WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute( - $amount, $form->{datepaid}, $form->{"id_$i"}) - || $form->dberror($query, 'CP.pm', - 530); - - %audittrail = ( - tablename => $form->{arap}, - reference => $form->{source}, - formname => $form->{formname}, - action => 'posted', - id => $form->{"id_$i"} ); + $sth = $dbh->prepare($query); + $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} ) + || $form->dberror( $query, 'CP.pm', 530 ); - $form->audittrail($dbh, "", \%audittrail); + %audittrail = ( + tablename => $form->{arap}, + reference => $form->{source}, + formname => $form->{formname}, + action => 'posted', + id => $form->{"id_$i"} + ); - } - } + $form->audittrail( $dbh, "", \%audittrail ); + } + } - # record a AR/AP with a payment - if ($form->round_amount($paymentamount, 2)) { - $form->{invnumber} = ""; - OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml, 1); - } + # record a AR/AP with a payment + if ( $form->round_amount( $paymentamount, 2 ) ) { + $form->{invnumber} = ""; + OP::overpayment( "", $myconfig, $form, $dbh, $paymentamount, $ml, 1 ); + } - my $rc = $dbh->commit; + my $rc = $dbh->commit; - $rc; + $rc; } - sub post_payments { - 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 $sth; + my $sth; - my ($paymentaccno) = split /--/, $form->{account}; + my ($paymentaccno) = split /--/, $form->{account}; - # if currency ne defaultcurrency update exchangerate - if ($form->{currency} ne $form->{defaultcurrency}) { - $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate}); + # if currency ne defaultcurrency update exchangerate + if ( $form->{currency} ne $form->{defaultcurrency} ) { + $form->{exchangerate} = + $form->parse_amount( $myconfig, $form->{exchangerate} ); - if ($form->{vc} eq 'customer') { - $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0); - } else { - $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate}); - } + if ( $form->{vc} eq 'customer' ) { + $form->update_exchangerate( $dbh, $form->{currency}, + $form->{datepaid}, $form->{exchangerate}, 0 ); + } + else { + $form->update_exchangerate( $dbh, $form->{currency}, + $form->{datepaid}, 0, $form->{exchangerate} ); + } - } else { - $form->{exchangerate} = 1; - } + } + else { + $form->{exchangerate} = 1; + } - my $query = qq| + my $query = qq| 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); - my ($buysell); + my ($buysell); - if ($form->{vc} eq 'customer') { - $buysell = "buy"; - } else { - $buysell = "sell"; - } + if ( $form->{vc} eq 'customer' ) { + $buysell = "buy"; + } + else { + $buysell = "sell"; + } - my $ml; - my $where; + my $ml; + my $where; - if ($form->{ARAP} eq 'AR') { + if ( $form->{ARAP} eq 'AR' ) { - $ml = 1; - $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |; + $ml = 1; + $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |; - } else { + } + else { - $ml = -1; - $where = qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |; + $ml = -1; + $where = + qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |; - } + } - # get AR/AP account - $query = qq|SELECT c.accno + # get AR/AP account + $query = qq|SELECT c.accno FROM chart c JOIN acc_trans ac ON (ac.chart_id = c.id) WHERE trans_id = ? AND $where|; - my $ath = $dbh->prepare($query) || $form->dberror($query); + my $ath = $dbh->prepare($query) || $form->dberror($query); - # query to retrieve paid amount - $query = qq|SELECT paid + # query to retrieve paid amount + $query = qq|SELECT paid FROM $form->{arap} WHERE id = ? FOR UPDATE|; - my $pth = $dbh->prepare($query) || $form->dberror($query); + my $pth = $dbh->prepare($query) || $form->dberror($query); + + my %audittrail; - my %audittrail; + my $overpayment = 0; + my $accno_id; - my $overpayment = 0; - my $accno_id; + # go through line by line + for my $i ( 1 .. $form->{rowcount} ) { - # go through line by line - for my $i (1 .. $form->{rowcount}) { + $ath->execute( $form->{"id_$i"} ); + ( $form->{ $form->{ARAP} } ) = $ath->fetchrow_array; + $ath->finish; - $ath->execute($form->{"id_$i"}); - ($form->{$form->{ARAP}}) = $ath->fetchrow_array; - $ath->finish; + $form->{"paid_$i"} = + $form->parse_amount( $myconfig, $form->{"paid_$i"} ); + $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} ); - $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}); - $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"}); + if ( $form->{"$form->{vc}_id_$i"} ne $sameid ) { - if ($form->{"$form->{vc}_id_$i"} ne $sameid) { - # record a AR/AP with a payment - if ($overpayment > 0 && $form->{$form->{ARAP}}) { - $form->{invnumber} = ""; - OP::overpayment("", $myconfig, $form, $dbh, $overpayment, $ml, 1); - } + # record a AR/AP with a payment + if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) { + $form->{invnumber} = ""; + OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml, + 1 ); + } - $overpayment = 0; - $form->{"$form->{vc}_id"} = $form->{"$form->{vc}_id_$i"}; - for (qw(source memo)) { $form->{$_} = $form->{"${_}_$i"} } - } + $overpayment = 0; + $form->{"$form->{vc}_id"} = $form->{"$form->{vc}_id_$i"}; + for (qw(source memo)) { $form->{$_} = $form->{"${_}_$i"} } + } - if ($form->{"checked_$i"} && $form->{"paid_$i"}) { + if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) { - $overpayment += ($form->{"paid_$i"} - $form->{"due_$i"}); + $overpayment += ( $form->{"paid_$i"} - $form->{"due_$i"} ); - # get exchangerate for original - $query = qq| + # get exchangerate for original + $query = qq| SELECT $buysell FROM exchangerate e JOIN $form->{arap} a @@ -670,99 +665,97 @@ sub post_payments { WHERE e.curr = ? AND a.id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{currency}, $form->{"id_$i"}) - || $form->dberror($query, 'CP.pm', 671); - my ($exchangerate) = $sth->fetchrow_array; + $sth = $dbh->prepare($query); + $sth->execute( $form->{currency}, $form->{"id_$i"} ) + || $form->dberror( $query, 'CP.pm', 671 ); + my ($exchangerate) = $sth->fetchrow_array; - $exchangerate ||= 1; + $exchangerate ||= 1; - $query = qq| + $query = qq| SELECT c.id FROM chart c JOIN acc_trans a ON (a.chart_id = c.id) WHERE $where AND a.trans_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{"id_$i"}); - ($id) = $sth->fetchrow_array(); + $sth = $dbh->prepare($query); + $sth->execute( $form->{"id_$i"} ); + ($id) = $sth->fetchrow_array(); - $paid = ($form->{"paid_$i"} > $form->{"due_$i"}) ? $form->{"due_$i"} : $form->{"paid_$i"}; - $amount = $form->round_amount($paid * $exchangerate, 2); + $paid = + ( $form->{"paid_$i"} > $form->{"due_$i"} ) + ? $form->{"due_$i"} + : $form->{"paid_$i"}; + $amount = $form->round_amount( $paid * $exchangerate, 2 ); - # add AR/AP - $query = qq| + # add AR/AP + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, transdate, amount) VALUES (?, ?, ?, ?)|; - $sth = $dbh->prepare($query); - $sth->execute( - $form->{"id_$i"}, $id, $form->{datepaid}, - $amount * $ml) - || $form->dberror($query, 'CP.pm', - 701); + $sth = $dbh->prepare($query); + $sth->execute( $form->{"id_$i"}, $id, $form->{datepaid}, + $amount * $ml ) + || $form->dberror( $query, 'CP.pm', 701 ); - $query = qq|SELECT id + $query = qq|SELECT id FROM chart WHERE accno = ?|; - $sth = $dbh->prepare($query); - $sth->execute($paymentaccno); - ($accno_id) = $sth->fetchrow_array; + $sth = $dbh->prepare($query); + $sth->execute($paymentaccno); + ($accno_id) = $sth->fetchrow_array; - # add payment - $query = qq| + # add payment + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, memo) VALUES (?, ?, ?, ?, ?, ?)|; - $sth = $dbh->prepare($query); - $sth->execute( - $form->{"id_$i"}, $accno_id, $form->{datepaid}, - $paid * $ml * -1, $form->{source}, - $form->{memo}) - || $form->dberror($query, 'CP.pm', - 723); - - # add exchangerate difference if currency ne defaultcurrency - $amount = - $form->round_amount( - $paid * ($form->{exchangerate} - 1) - * $ml * -1, - 2); - - if ($amount) { - # exchangerate difference - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( + $form->{"id_$i"}, $accno_id, $form->{datepaid}, + $paid * $ml * -1, $form->{source}, $form->{memo} + ) || $form->dberror( $query, 'CP.pm', 723 ); + + # add exchangerate difference if currency ne defaultcurrency + $amount = + $form->round_amount( + $paid * ( $form->{exchangerate} - 1 ) * $ml * -1, 2 ); + + if ($amount) { + + # exchangerate difference + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source) VALUES (?, ?, ?, ?, ?)|; - - $sth = $dbh->prepare($query); - $sth->execute( - $form->{"id_$i"}, $accno_id, - $form->{datepaid}, $amount, - $form->{source}) - || $form->dberror( - $query, 'CP.pm', 748); - - # gain/loss - $amount = ($form->round_amount($paid * $exchangerate,2) - $form->round_amount($paid * $form->{exchangerate},2)) * $ml * -1; - - if ($amount) { - $accno_id = - ($amount > 0) - ? $fxgain_accno_id - : $fxloss_accno_id; - - $query = qq| + $sth = $dbh->prepare($query); + $sth->execute( + $form->{"id_$i"}, $accno_id, $form->{datepaid}, + $amount, $form->{source} + ) || $form->dberror( $query, 'CP.pm', 748 ); + + # gain/loss + $amount = + ( $form->round_amount( $paid * $exchangerate, 2 ) - + $form->round_amount( $paid * $form->{exchangerate}, 2 ) ) + * $ml * -1; + + if ($amount) { + $accno_id = + ( $amount > 0 ) + ? $fxgain_accno_id + : $fxloss_accno_id; + + $query = qq| INSERT INTO acc_trans (trans_id, chart_id, @@ -771,63 +764,60 @@ sub post_payments { fx_transaction) VALUES (?, ?, ?, ?, '1')|; - $sth = $dbh->prepare($query); - $sth->execute( - $form->{"id_$i"}, $accno_id, - $form->{datepaid}, $amount) - || $form->dberror( - $query, - 'CP.pm', 775); - } - } + $sth = $dbh->prepare($query); + $sth->execute( + $form->{"id_$i"}, $accno_id, + $form->{datepaid}, $amount + ) || $form->dberror( $query, 'CP.pm', 775 ); + } + } - $paid = $form->round_amount($paid * $exchangerate, 2); + $paid = $form->round_amount( $paid * $exchangerate, 2 ); - $pth->execute($form->{"id_$i"}) || $form->dberror; - ($amount) = $pth->fetchrow_array; - $pth->finish; + $pth->execute( $form->{"id_$i"} ) || $form->dberror; + ($amount) = $pth->fetchrow_array; + $pth->finish; - $amount += $paid; + $amount += $paid; - # update AR/AP transaction - $query = qq| + # update AR/AP transaction + $query = qq| UPDATE $form->{arap} SET paid = ?, datepaid = ? WHERE id = ?|; - $sth = $dbh->prepare($query); - $sth->execute( - $amount, $form->{datepaid}, $form->{"id_$i"}) - || $form->dberror($query, 'CP.pm', - 796); + $sth = $dbh->prepare($query); + $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} ) + || $form->dberror( $query, 'CP.pm', 796 ); - %audittrail = ( tablename => $form->{arap}, - reference => $form->{source}, - formname => $form->{formname}, - action => 'posted', - id => $form->{"id_$i"} ); + %audittrail = ( + tablename => $form->{arap}, + reference => $form->{source}, + formname => $form->{formname}, + action => 'posted', + id => $form->{"id_$i"} + ); - $form->audittrail($dbh, "", \%audittrail); + $form->audittrail( $dbh, "", \%audittrail ); - } + } - $sameid = $form->{"$form->{vc}_id_$i"}; + $sameid = $form->{"$form->{vc}_id_$i"}; - } + } - # record a AR/AP with a payment - if ($overpayment > 0 && $form->{$form->{ARAP}}) { - $form->{invnumber} = ""; - OP::overpayment("", $myconfig, $form, $dbh, $overpayment, $ml, 1); - } + # record a AR/AP with a payment + if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) { + $form->{invnumber} = ""; + OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml, 1 ); + } - my $rc = $dbh->commit; + my $rc = $dbh->commit; - $rc; + $rc; } - 1; |