1#===================================================================== 2# SQL-Ledger ERP 3# Copyright (C) 2006 4# 5# Author: DWS Systems Inc. 6# Web: http://www.sql-ledger.com 7# 8#====================================================================== 9# 10# Check and receipt printing payment module backend routines 11# Number to text conversion routines are in 12# locale/{countrycode}/Num2text 13# 14#====================================================================== 15 16package CP; 17 18 19sub new { 20 my ($type, $countrycode) = @_; 21 22 my $self = {}; 23 24 if ($countrycode) { 25 if (-f "locale/$countrycode/Num2text") { 26 require "locale/$countrycode/Num2text"; 27 } else { 28 use SL::Num2text; 29 } 30 } else { 31 use SL::Num2text; 32 } 33 34 bless $self, $type; 35 36} 37 38 39sub paymentaccounts { 40 my ($self, $myconfig, $form, $dbh) = @_; 41 42 my $disconnect = ($dbh) ? 0 : 1; 43 44 # connect to database 45 $dbh = $form->dbconnect($myconfig) unless $dbh; 46 47 my $query = qq|SELECT c.accno, c.description, c.link, 48 l.description AS translation 49 FROM chart c 50 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 51 WHERE c.link LIKE '%$form->{ARAP}%' 52 AND c.closed = '0' 53 ORDER BY c.accno|; 54 my $sth = $dbh->prepare($query); 55 $sth->execute || $form->dberror($query); 56 57 $form->{PR}{$form->{ARAP}} = (); 58 $form->{PR}{"$form->{ARAP}_paid"} = (); 59 $form->{PR}{"$form->{ARAP}_discount"} = (); 60 61 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { 62 $ref->{description} = $ref->{translation} if $ref->{translation}; 63 foreach my $item (split /:/, $ref->{link}) { 64 if ($item eq $form->{ARAP}) { 65 push @{ $form->{PR}{$form->{ARAP}} }, $ref; 66 } 67 if ($item eq "$form->{ARAP}_paid") { 68 push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref; 69 } 70 if ($item eq "$form->{ARAP}_discount") { 71 push @{ $form->{PR}{"$form->{ARAP}_discount"} }, $ref; 72 } 73 } 74 } 75 $sth->finish; 76 77 # get currencies and closedto 78 $form->{datepaid} = $form->current_date($myconfig); 79 80 ($form->{employee}) = $form->get_employee($dbh); 81 82 my %defaults = $form->get_defaults($dbh, \@{['closedto', "$form->{type}\_%"]}); 83 for (keys %defaults) { $form->{$_} = $defaults{$_} } 84 85 $form->{currencies} = $form->get_currencies($myconfig, $dbh); 86 87 if ($form->{payment} eq 'payments') { 88 # get language codes 89 $form->all_languages($myconfig, $dbh); 90 91 $form->all_departments($myconfig, $dbh, $form->{vc}); 92 } 93 94 if ($form->{vc} eq 'vendor') { 95 # get business types 96 $query = qq|SELECT * 97 FROM business 98 ORDER BY rn|; 99 $sth = $dbh->prepare($query); 100 $sth->execute || $form->dberror($query); 101 102 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 103 push @{ $form->{all_business} }, $ref; 104 } 105 $sth->finish; 106 } 107 108 $query = qq|SELECT * 109 FROM paymentmethod 110 ORDER BY rn|; 111 $sth = $dbh->prepare($query); 112 $sth->execute || $form->dberror($query); 113 114 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 115 push @{ $form->{all_paymentmethod} }, $ref; 116 } 117 $sth->finish; 118 119 $form->get_peripherals($dbh); 120 121 $dbh->disconnect if $disconnect; 122 123} 124 125 126sub get_openvc { 127 my ($self, $myconfig, $form) = @_; 128 129 my $dbh = $form->dbconnect($myconfig); 130 131 $form->remove_locks($myconfig, $dbh, $form->{arap}); 132 $form->{redo} = 1; 133 $form->{locks_removed} = 1; 134 135 my $where = qq|a.amount != a.paid 136 AND a.approved = '1' 137 AND a.onhold = '0' 138 AND NOT a.id IN (SELECT id 139 FROM semaphore)|; 140 141 $form->{vc} =~ s/;//g; 142 my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap'; 143 144 my $sth; 145 my $ref; 146 my $i = 0; 147 my $var; 148 149 if ($form->{duedatefrom}) { 150 $where .= qq| 151 AND a.duedate >= '$form->{duedatefrom}'|; 152 } 153 if ($form->{duedateto}) { 154 $where .= qq| 155 AND a.duedate <= '$form->{duedateto}'|; 156 } 157 158 my $id; 159 my $description; 160 161 if (! $form->{all_vc}) { 162 ($id, $description) = split /--/, $form->{$form->{ARAP}}; 163 if ($id) { 164 $where .= qq| 165 AND c.accno = '$id'|; 166 } 167 if ($form->{vc} eq 'vendor') { 168 ($description, $id) = split /--/, $form->{business}; 169 if ($id) { 170 $where .= qq| 171 AND vc.business_id = $id|; 172 } 173 } 174 } 175 176 ($description, $id) = split /--/, $form->{paymentmethod}; 177 178 if ($id) { 179 $where .= qq| 180 AND a.paymentmethod_id = $id|; 181 } 182 183 if (! $form->{"select$form->{vc}"}) { 184 if ($form->{$form->{vc}}) { 185 $var = $form->like(lc $form->{$form->{vc}}); 186 $where .= qq| AND lower(vc.name) LIKE '$var'|; 187 } 188 if ($form->{"$form->{vc}number"}) { 189 $var = $form->like(lc $form->{"$form->{vc}number"}); 190 $where .= qq| AND lower(vc.$form->{vc}number) LIKE '$var'|; 191 } 192 } 193 194 my %defaults = $form->get_defaults($dbh, \@{['namesbynumber']}); 195 my $sortorder = "name"; 196 if ($defaults{namesbynumber}) { 197 $sortorder = "$form->{vc}number"; 198 } 199 200 # build selection list 201 $query = qq|SELECT vc.*, 202 ad.address1, ad.address2, ad.city, ad.state, ad.zipcode, 203 ad.country, a.amount, a.paid, 204 a.exchangerate, 205 l.description AS translation, 206 ch.accno AS $form->{ARAP}, 207 ch.description AS $form->{ARAP}_description, 208 pa.accno AS $form->{ARAP}_paid, 209 pa.description AS $form->{ARAP}_paid_description, 210 pm.description AS paymentmethod 211 FROM $form->{vc} vc 212 JOIN $arap a ON (a.$form->{vc}_id = vc.id) 213 JOIN acc_trans ac ON (a.id = ac.trans_id) 214 JOIN chart c ON (c.id = ac.chart_id) 215 JOIN address ad ON (ad.trans_id = vc.id) 216 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 217 LEFT JOIN chart ch ON (ch.id = vc.arap_accno_id) 218 LEFT JOIN chart pa ON (pa.id = vc.payment_accno_id) 219 LEFT JOIN paymentmethod pm ON (pm.id = vc.paymentmethod_id) 220 WHERE $where 221 ORDER BY vc.$sortorder|; 222 $sth = $dbh->prepare($query); 223 $sth->execute || $form->dberror($query); 224 225 my %due; 226 my @transactions = (); 227 228 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 229 230 $ref->{exchangerate} ||= 1; 231 $ref->{description} = $ref->{translation} if $ref->{translation}; 232 233 if ($form->{vc} eq 'vendor') { 234 $ref->{fxdue} = $form->round_amount(($ref->{amount} - $ref->{paid}) / $ref->{exchangerate}, $form->{precision}); 235 $due{$ref->{id}} += $ref->{fxdue}; 236 } 237 push @transactions, $ref; 238 } 239 $sth->finish; 240 241 my %vc; 242 243 @{ $form->{name_list} } = (); 244 245 foreach $ref (@transactions) { 246 247 next if $vc{$ref->{id}}; 248 if ($form->{vc} eq 'vendor') { 249 if ($ref->{threshold} > 0) { 250 next if $due{$ref->{id}} < $ref->{threshold}; 251 } 252 } 253 254 $i++; 255 $vc{$ref->{id}} = 1; 256 push @{ $form->{name_list} }, $ref; 257 258 } 259 260 $form->all_departments($myconfig, $dbh, $form->{vc}); 261 262 $form->all_languages($myconfig, $dbh); 263 264 $dbh->disconnect; 265 266 $i; 267 268} 269 270 271sub retrieve { 272 my ($self, $myconfig, $form) = @_; 273 274 my $id; 275 276 # connect to database 277 my $dbh = $form->dbconnect($myconfig); 278 279 $form->{id} *= 1; 280 281 my %defaults = $form->get_defaults($dbh, \@{['precision']}); 282 for (keys %defaults) { $form->{$_} = $defaults{$_} } 283 284 my $ml = 1; 285 286 if ($form->{vc} eq 'customer') { 287 $ml = -1; 288 } 289 290 my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.duedate, 291 ac.transdate AS datepaid, a.amount, 292 SUM(ac.amount) * $ml AS paid, ac.source, ac.memo, 293 a.$form->{vc}_id, 294 a.curr, a.discountterms, a.cashdiscount, a.netamount, 295 date '$form->{transdate}' <= a.transdate + a.discountterms AS calcdiscount, 296 ac.approved, 297 a.exchangerate, 298 (SELECT acc.amount * $ml 299 FROM acc_trans acc 300 JOIN chart c ON (c.id = acc.chart_id) 301 WHERE acc.trans_id = ac.trans_id 302 AND acc.fx_transaction = '0' 303 AND acc.vr_id = $form->{id} 304 AND c.link LIKE '%$form->{ARAP}_discount%') AS discount 305 FROM $form->{arap} a 306 JOIN acc_trans ac ON (ac.trans_id = a.id) 307 JOIN chart ch ON (ch.id = ac.chart_id) 308 WHERE ac.vr_id = $form->{id} 309 AND ac.fx_transaction = '0' 310 AND ch.link LIKE '%$form->{ARAP}_paid%' 311 GROUP BY a.id, a.invnumber, a.transdate, a.duedate, 312 a.amount, a.paid, a.discountterms, a.cashdiscount, a.netamount, 313 a.$form->{vc}_id, a.curr, ac.transdate, calcdiscount, 314 ac.approved, a.exchangerate, ac.trans_id, ac.source, ac.memo|; 315 316 my @sf = qw(transdate invnumber); 317 my %ordinal = $form->ordinal_order($dbh, $query); 318 $query .= qq| ORDER BY | .$form->sort_order(\@sf, \%ordinal); 319 320 my $sth = $dbh->prepare($query); 321 $sth->execute || $form->dberror($query); 322 323 $query = qq|SELECT c.accno, c.description, 324 l.description AS translation 325 FROM acc_trans ac 326 JOIN chart c ON (c.id = ac.chart_id) 327 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 328 WHERE ac.trans_id = ? 329 AND ac.fx_transaction = '0' 330 AND (c.link LIKE '$form->{ARAP}%' 331 OR c.link LIKE '%:$form->{ARAP}')|; 332 my $ath = $dbh->prepare($query); 333 334 $query = qq|SELECT c.accno, c.description, 335 l.description AS translation 336 FROM acc_trans ac 337 JOIN chart c ON (c.id = ac.chart_id) 338 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 339 WHERE ac.trans_id = ? 340 AND ac.transdate = ? 341 AND ac.fx_transaction = '0' 342 AND c.link LIKE '%$form->{ARAP}_paid%'|; 343 my $pth = $dbh->prepare($query); 344 345 $query = qq|SELECT c.accno, c.description, 346 l.description AS translation 347 FROM acc_trans ac 348 JOIN chart c ON (c.id = ac.chart_id) 349 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 350 WHERE ac.trans_id = ? 351 AND ac.fx_transaction = '0' 352 AND c.link LIKE '%$form->{ARAP}_discount%'|; 353 my $dth = $dbh->prepare($query); 354 355 my $accno; 356 my $description; 357 my $translation; 358 359 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 360 $form->{edit} .= "$ref->{id} "; 361 362 push @{ $form->{transactions} }, $ref; 363 364 $form->{"$form->{vc}_id"} = $ref->{"$form->{vc}_id"}; 365 $form->{source} ||= $ref->{source}; 366 $form->{memo} ||= $ref->{memo}; 367 $form->{approved} ||= $ref->{approved}; 368 $ref->{calcdiscount} = 0 unless $ref->{discountterms}; 369 370 # AR/AP account 371 if (!$form->{arap_accno}) { 372 $ath->execute($ref->{id}); 373 ($accno, $description, $translation) = $ath->fetchrow_array; 374 $ath->finish; 375 $description = $translation if $translation; 376 $form->{arap_accno} = "${accno}--$description"; 377 } 378 # payment 379 if (!$form->{payment_accno}) { 380 $pth->execute($ref->{id}, $ref->{datepaid}); 381 ($accno, $description, $translation) = $pth->fetchrow_array; 382 $pth->finish; 383 $description = $translation if $translation; 384 $form->{"$form->{ARAP}_paid"} = "${accno}--$description"; 385 $form->{payment_accno} = $form->{"$form->{ARAP}_paid"}; 386 } 387 # discount 388 if (!$form->{discount_accno}) { 389 if ($ref->{discount}) { 390 $dth->execute($ref->{id}); 391 ($accno, $description, $translation) = $dth->fetchrow_array; 392 $dth->finish; 393 $description = $translation if $translation; 394 $form->{"$form->{ARAP}_discount"} = "${accno}--$description"; 395 $form->{discount_accno} = $form->{"$form->{ARAP}_discount"}; 396 } 397 } 398 399 } 400 chop $form->{edit}; 401 $sth->finish; 402 403 $query = qq|SELECT br.description, vr.vouchernumber 404 FROM vr 405 JOIN br ON (br.id = vr.br_id) 406 WHERE vr.id = $form->{id}|; 407 ($form->{batchdescription}, $form->{vouchernumber}) = $dbh->selectrow_array($query); 408 409 $form->{voucherid} = $form->{id}; 410 $form->{id} *= 1; 411 AA->get_name($myconfig, $form, $dbh); 412 413 $form->{"old$form->{vc}"} = qq|$form->{$form->{vc}}--$form->{"$form->{vc}_id"}|; 414 415 &paymentaccounts("", $myconfig, $form, $dbh); 416 417 $form->all_departments($myconfig, $dbh, $form->{vc}); 418 419 $form->all_languages($myconfig, $dbh); 420 421 $dbh->disconnect; 422 423} 424 425 426sub get_openinvoices { 427 my ($self, $myconfig, $form) = @_; 428 429 my $id; 430 431 # connect to database 432 my $dbh = $form->dbconnect($myconfig); 433 434 $form->{vc} =~ s/;//g; 435 436 # remove locks 437 $form->remove_locks($myconfig, $dbh, $form->{arap}); 438 439 my $where = qq|WHERE a.$form->{vc}_id = $form->{"$form->{vc}_id"} 440 AND a.amount != a.paid 441 AND a.approved = '1' 442 AND a.onhold = '0' 443 AND NOT a.id IN (SELECT id 444 FROM semaphore)|; 445 446 my %defaults = $form->get_defaults($dbh, \@{[qw(namesbynumber cdt precision)]}); 447 448 for (keys %defaults) { $form->{$_} = $defaults{$_} } 449 450 my @sf = qw(transdate invnumber); 451 452 if ($form->{payment} eq 'payments') { 453 $where = qq|WHERE a.amount != a.paid 454 AND a.approved = '1' 455 AND a.onhold = '0' 456 AND NOT a.id IN (SELECT id 457 FROM semaphore)|; 458 @sf = qw(name transdate); 459 if ($defaults{namesbynumber}) { 460 @sf = ("$form->{vc}number", "transdate"); 461 } 462 } 463 464 $where .= qq| 465 AND a.curr = '$form->{currency}'| if $form->{currency}; 466 $where .= qq| 467 AND a.duedate >= '$form->{duedatefrom}'| if $form->{duedatefrom}; 468 $where .= qq| 469 AND a.duedate <= '$form->{duedateto}'| if $form->{duedateto}; 470 471 (undef, $id) = split /--/, $form->{department}; 472 $where .= qq| 473 AND a.department_id = $id| if $id; 474 475 (undef, $id) = split /--/, $form->{paymentmethod}; 476 $where .= qq| 477 AND a.paymentmethod_id = $id| if $id; 478 479 ($id) = split /--/, $form->{$form->{ARAP}}; 480 $where .= qq| 481 AND ch.accno = '$id'| if $id; 482 483 if ($form->{vc} eq 'vendor') { 484 (undef, $id) = split /--/, $form->{business}; 485 $where .= qq| 486 AND vc.business_id = $id| if $id; 487 488 } 489 490 my $datepaid = ($form->{datepaid}) ? "date '$form->{datepaid}'" : 'current_date'; 491 my $query = qq|SELECT DISTINCT a.id, a.invnumber, a.transdate, a.duedate, 492 a.description AS invdescription, 493 a.amount, a.paid, a.curr, vc.$form->{vc}number, vc.name, 494 vc.language_code, vc.threshold, vc.curr AS currency, 495 vc.payment_accno_id, 496 a.$form->{vc}_id, 497 a.discountterms, a.cashdiscount, a.netamount, 498 $datepaid <= a.transdate + a.discountterms AS calcdiscount, 499 a.exchangerate, ex.exchangerate AS vcexch, 500 a.taxincluded 501 FROM acc_trans ac 502 JOIN $form->{arap} a ON (a.id = ac.trans_id) 503 JOIN $form->{vc} vc ON (vc.id = a.$form->{vc}_id) 504 JOIN chart ch ON (ch.id = ac.chart_id) 505 LEFT JOIN exchangerate ex ON (ex.curr = vc.curr AND ex.transdate = a.transdate) 506 $where|; 507 508 my %ordinal = $form->ordinal_order($dbh, $query); 509 $query .= qq| ORDER BY | .$form->sort_order(\@sf, \%ordinal); 510 511 my $sth = $dbh->prepare($query); 512 $sth->execute || $form->dberror($query); 513 514 # discount 515 $query = qq|SELECT sum(ac.amount) 516 FROM acc_trans ac 517 JOIN chart c ON (c.id = ac.chart_id) 518 WHERE c.link LIKE '%$form->{ARAP}_discount%' 519 AND ac.approved = '1' 520 AND ac.trans_id = ?|; 521 my $tth = $dbh->prepare($query) || $form->dberror($query); 522 523 my %total; 524 my @transactions = (); 525 526 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 527 528 $ref->{calcdiscount} = 0 if ! $ref->{cashdiscount}; 529 if ($ref->{calcdiscount}) { 530 $tth->execute($ref->{id}); 531 if ($tth->fetchrow_array) { 532 $ref->{calcdiscount} = 0; 533 } 534 $tth->finish; 535 } 536 537 $ref->{exchangerate} ||= 1; 538 $ref->{vcexch} ||= 1; 539 540 # for threshold calculation 541 $ref->{fxdue} = $form->round_amount(($ref->{amount} - $ref->{paid}) / $ref->{vcexch}, $form->{precision}); 542 543 $total{$ref->{"$form->{vc}_id"}} += $ref->{fxdue}; 544 push @transactions, $ref; 545 } 546 547 $sth->finish; 548 549 $form->{PR} = (); 550 551 foreach $ref (@transactions) { 552 if ($form->{vc} eq 'vendor') { 553 if ($ref->{threshold} > 0) { 554 $total{$ref->{"$form->{vc}_id"}} = $form->round_amount($total{$ref->{"$form->{vc}_id"}}, $form->{precision}); 555 next if $total{$ref->{"$form->{vc}_id"}} < $ref->{threshold}; 556 } 557 } 558 $form->create_lock($myconfig, $dbh, $ref->{id}, $form->{arap}); 559 push @{ $form->{PR} }, $ref; 560 } 561 562 $dbh->disconnect; 563 564} 565 566 567 568sub post_payment { 569 my ($self, $myconfig, $form) = @_; 570 571 # connect to database, turn AutoCommit off 572 $dbh = $form->dbconnect_noauto($myconfig); 573 574 my ($paymentaccno) = split /--/, $form->{"$form->{ARAP}_paid"}; 575 my ($discountaccno) = split /--/, $form->{"$form->{ARAP}_discount"}; 576 577 # if currency ne defaultcurrency update exchangerate 578 if ($form->{currency} ne $form->{defaultcurrency}) { 579 $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate}); 580 581 $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}); 582 } else { 583 $form->{exchangerate} = 1; 584 } 585 586 $form->update_defaults($myconfig, qq|$form->{type}_$paymentaccno|, $dbh, $form->{source}); 587 588 my $query; 589 my $sth; 590 591 # tax accounts 592 $query = qq|SELECT DISTINCT c.accno, t.rate, t.validto 593 FROM chart c 594 JOIN acc_trans ac ON (ac.chart_id = c.id) 595 JOIN tax t ON (t.chart_id = c.id) 596 WHERE c.link LIKE '%$form->{ARAP}_tax%' 597 AND ac.trans_id = ? 598 AND (t.validto >= ? OR t.validto IS NULL) 599 ORDER BY t.validto DESC|; 600 my $tth = $dbh->prepare($query) || $form->dberror($query); 601 602 my %defaults = $form->get_defaults($dbh, \@{['fxgainloss_accno_id', 'cdt']}); 603 604 my $ml = ($form->{ARAP} eq 'AR') ? 1 : -1; 605 606 $form->{arap} = lc $form->{ARAP}; 607 608 my $where = qq| 609 c.link = '$form->{ARAP}' 610 |; 611 612 # AR/AP default account 613 $query = qq|SELECT c.id 614 FROM chart c 615 WHERE $where|; 616 my ($arapdefault) = $dbh->selectrow_array($query); 617 618 # AR/AP account 619 $query = qq|SELECT DISTINCT c.id 620 FROM chart c 621 JOIN acc_trans a ON (a.chart_id = c.id) 622 WHERE $where 623 AND a.trans_id = ?|; 624 my $ath = $dbh->prepare($query) || $form->dberror($query); 625 626 my $paymentamount = $form->parse_amount($myconfig, $form->{amount}); 627 628 # query to retrieve paid amount 629 $query = qq|SELECT amount, netamount, paid, transdate, taxincluded, 630 exchangerate 631 FROM $form->{arap} 632 WHERE id = ? 633 FOR UPDATE|; 634 my $pth = $dbh->prepare($query) || $form->dberror($query); 635 636 my %trans; 637 my $ref; 638 my $amount; 639 my $vth; 640 my $dth; 641 my $ith; 642 my $dith; 643 644 my %cdt; 645 my $diff; 646 my $accno; 647 my $rate; 648 649 # delete payments 650 $form->{voucherid} *= 1; 651 if ($form->{edit} && $form->{voucherid}) { 652 $query = qq|SELECT SUM(ac.amount) * $ml * -1 653 FROM acc_trans ac 654 JOIN chart c ON (c.id = ac.chart_id) 655 WHERE ac.trans_id = ? 656 AND ac.vr_id = $form->{voucherid} 657 AND c.link LIKE '%$form->{ARAP}_paid%' 658 AND NOT (ac.chart_id = $defaults{fxgainloss_accno_id})|; 659 $sth = $dbh->prepare($query) || $form->dberror($query); 660 661 # discount 662 $query = qq|SELECT sum(ac.amount) * $ml * -1 663 FROM acc_trans ac 664 JOIN chart c ON (c.id = ac.chart_id) 665 WHERE ac.trans_id = ? 666 AND ac.vr_id = $form->{voucherid} 667 AND c.link LIKE '%$form->{ARAP}_discount%'|; 668 $dth = $dbh->prepare($query) || $form->dberror($query); 669 670 foreach $id (split / /, $form->{edit}) { 671 672 $query = qq|SELECT id 673 FROM acc_trans 674 WHERE trans_id = $id 675 AND vr_id = $form->{voucherid}|; 676 $ith = $dbh->prepare($query) || $form->dberror($query); 677 $ith->execute; 678 679 while (($paymentid) = $ith->fetchrow_array) { 680 $query = qq|DELETE FROM payment 681 WHERE id = $paymentid 682 AND trans_id = $id|; 683 $dith = $dbh->prepare($query) || $form->dberror($query); 684 $dbh->do($query) || $form->dberror($query); 685 } 686 $ith->finish; 687 688 # payments 689 $sth->execute($id); 690 $ref = $sth->fetchrow_hashref(NAME_lc); 691 for (keys %$ref) { $trans{$id}{$_} = $ref->{$_} } 692 $sth->finish; 693 694 # discount 695 $dth->execute($id); 696 ($trans{$id}{discount}) = $dth->fetchrow_array; 697 $dth->finish; 698 699 # update arap 700 $form->update_balance($dbh, 701 $form->{arap}, 702 'paid', 703 qq|id = $id|, 704 ($trans{$id}{amount} + $trans{$id}{discount}) * -1); 705 706 # update batch 707 $form->update_balance($dbh, 708 'br', 709 'amount', 710 qq|id = $form->{batchid}|, 711 ($trans{$id}{amount} + $trans{$id}{discount}) * -1); 712 713 } 714 715 $query = qq|DELETE FROM acc_trans 716 WHERE vr_id = $form->{voucherid}|; 717 $dbh->do($query) || $form->dberror($query); 718 719 $query = qq|DELETE FROM vr 720 WHERE id = $form->{voucherid}|; 721 $dbh->do($query) || $form->dberror($query); 722 723 } 724 725 my %audittrail; 726 my $action = 'posted'; 727 my $approved = ($form->{pending}) ? '0' : '1'; 728 729 if (!$approved) { 730 $action = 'saved'; 731 } 732 733 my $voucherid = 'NULL'; 734 735 if ($form->{batch}) { 736 $voucherid = $form->{voucherid}; 737 if (! $form->{voucherid}) { 738 $query = qq|SELECT nextval('id')|; 739 ($voucherid) = $dbh->selectrow_array($query); 740 } 741 $form->{vouchernumber} = $form->update_defaults($myconfig, 'vouchernumber', $dbh) unless $form->{vouchernumber}; 742 } 743 744 $query = qq|INSERT INTO vr (br_id, trans_id, id, vouchernumber) 745 VALUES ($form->{batchid}, ?, $voucherid, | 746 .$dbh->quote($form->{vouchernumber}).qq|)|; 747 $vth = $dbh->prepare($query) || $form->dberror($query); 748 749 my $assignvoucherid; 750 my $arap; 751 my (undef, $paymentmethod_id) = split /--/, $form->{paymentmethod}; 752 $paymentmethod_id *= 1; 753 754 # go through line by line 755 for my $i (1 .. $form->{rowcount}) { 756 757 $assignvoucherid = 1; 758 $cdt = 0; 759 760 for (qw(paid discount)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } 761 762 if ($form->{"checked_$i"}) { 763 764 # paymentid 765 $query = qq|SELECT MAX(id) 766 FROM payment 767 WHERE trans_id = $form->{"id_$i"}|; 768 ($paymentid) = $dbh->selectrow_array($query); 769 $paymentid++; 770 771 # original paid 772 # lock for update 773 $pth->execute($form->{"id_$i"}) || $form->dberror; 774 $ref = $pth->fetchrow_hashref(NAME_lc); 775 for (keys %$ref) { $trans{$form->{"id_$i"}}{$_} = $ref->{$_} } 776 777 $paymentamount -= $form->{"paid_$i"}; 778 779 $ath->execute($form->{"id_$i"}) || $form->dberror; 780 ($arap) = $ath->fetchrow_array || $arapdefault; 781 $ath->finish; 782 783 $amount = $form->round_amount($form->{"paid_$i"} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}); 784 785 # add AR/AP 786 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, 787 amount, approved, vr_id) 788 VALUES ($form->{"id_$i"}, $arap, '$form->{datepaid}', 789 $amount * $ml, '$approved', 790 $voucherid)|; 791 $dbh->do($query) || $form->dberror($query); 792 793 # add payment 794 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, 795 amount, source, memo, approved, vr_id, id) 796 VALUES ($form->{"id_$i"}, 797 (SELECT id FROM chart 798 WHERE accno = '$paymentaccno'), 799 '$form->{datepaid}', $form->{"paid_$i"} * $ml * -1, | 800 .$dbh->quote($form->{source}).qq|, | 801 .$dbh->quote($form->{memo}).qq|, '$approved', 802 $voucherid, $paymentid)|; 803 $dbh->do($query) || $form->dberror($query); 804 805 $query = qq|INSERT INTO payment (id, trans_id, exchangerate, 806 paymentmethod_id) 807 VALUES ($paymentid, $form->{"id_$i"}, $form->{exchangerate}, 808 $paymentmethod_id)|; 809 $dbh->do($query) || $form->dberror($query); 810 811 # add exchangerate difference if currency ne defaultcurrency 812 $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - 1) * $ml * -1, $form->{precision}); 813 814 if ($amount) { 815 # exchangerate difference 816 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, 817 amount, fx_transaction, source, approved, vr_id) 818 VALUES ($form->{"id_$i"}, 819 (SELECT id FROM chart 820 WHERE accno = '$paymentaccno'), 821 '$form->{datepaid}', $amount, '1', | 822 .$dbh->quote($form->{source}).qq|, '$approved', 823 $voucherid)|; 824 $dbh->do($query) || $form->dberror($query); 825 } 826 827 # gain/loss 828 $amount = $form->round_amount(($form->round_amount($form->{"paid_$i"} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}) - $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, $form->{precision})) * $ml * -1, $form->{precision}); 829 if ($amount) { 830 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, 831 amount, fx_transaction, approved, vr_id) 832 VALUES ($form->{"id_$i"}, $defaults{fxgainloss_accno_id}, 833 '$form->{datepaid}', $amount, '1', '$approved', 834 $voucherid)|; 835 $dbh->do($query) || $form->dberror($query); 836 } 837 838 839 # deduct tax for cash discount 840 if ($form->{"discount_$i"}) { 841 842 %cdt = (); 843 $diff = 0; 844 845 if ($defaults{cdt} && !$trans{$form->{"id_$i"}}{taxincluded}) { 846 847 $tth->execute($form->{"id_$i"}, $trans{$form->{"id_$i"}}{transdate}) || $form->dberror; 848 849 my $totalrate = 0; 850 while (($accno, $rate) = $tth->fetchrow_array) { 851 $totalrate += $rate; 852 $cdt{$accno} = $rate; 853 } 854 $tth->finish; 855 856 $cdt = $form->round_amount($form->{"discount_$i"} * $totalrate, $form->{precision}); 857 858 for (keys %cdt) { 859 $accno = $_; 860 if ($totalrate) { 861 $amount = $cdt * $cdt{$_} / $totalrate; 862 $cdt{$_} = $form->round_amount($amount, $form->{precision}); 863 $diff += ($amount - $cdt{$_}); 864 } 865 } 866 $diff = $form->round_amount($diff, $form->{precision}); 867 if ($diff != 0) { 868 $cdt{$accno} -= $diff; 869 } 870 871 $cdt = $form->round_amount($cdt * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}); 872 873 } 874 875 $cdt{$discountaccno} = $form->{"discount_$i"}; 876 877 for (keys %cdt) { 878 # add AR/AP 879 $amount = $form->round_amount($cdt{$_} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}); 880 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, 881 amount, approved, vr_id, id) 882 VALUES ($form->{"id_$i"}, $arap, '$form->{datepaid}', 883 $amount * $ml, '$approved', 884 $voucherid, $form->{"id_$i"})|; 885 $dbh->do($query) || $form->dberror($query); 886 887 # add discount 888 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, 889 amount, source, memo, approved, vr_id, id) 890 VALUES ($form->{"id_$i"}, 891 (SELECT id FROM chart 892 WHERE accno = '$_'), 893 '$form->{datepaid}', $cdt{$_} * $ml * -1, | 894 .$dbh->quote($form->{source}).qq|, | 895 .$dbh->quote($form->{memo}).qq|, '$approved', 896 $voucherid, $form->{"id_$i"})|; 897 $dbh->do($query) || $form->dberror($query); 898 899 # add exchangerate difference if currency ne defaultcurrency 900 $amount = $form->round_amount($cdt{$_} * ($form->{exchangerate} - 1) * $ml * -1, $form->{precision}); 901 902 if ($amount) { 903 # exchangerate difference 904 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, 905 amount, fx_transaction, source, approved, vr_id, id) 906 VALUES ($form->{"id_$i"}, 907 (SELECT id FROM chart 908 WHERE accno = '$_'), 909 '$form->{datepaid}', $amount, '1', | 910 .$dbh->quote($form->{source}).qq|, '$approved', 911 $voucherid, $form->{"id_$i"})|; 912 $dbh->do($query) || $form->dberror($query); 913 914 # gain/loss 915 $amount = $form->round_amount(($form->round_amount($cdt{$_} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}) - $form->round_amount($cdt{$_} * $form->{exchangerate}, $form->{precision})) * $ml * -1, $form->{precision}); 916 917 if ($amount) { 918 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, 919 amount, fx_transaction, approved, vr_id, id) 920 VALUES ($form->{"id_$i"}, $defaults{fxgainloss_accno_id}, 921 '$form->{datepaid}', $amount, '1', '$approved', 922 $voucherid, $form->{"id_$i"})|; 923 $dbh->do($query) || $form->dberror($query); 924 } 925 } 926 } 927 } 928 929 930 $form->{"paid_$i"} = $form->round_amount($form->{"paid_$i"} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}); 931 $form->{"discount_$i"} = $form->round_amount($form->{"discount_$i"} * $trans{$form->{"id_$i"}}{exchangerate}, $form->{precision}); 932 933 # unlock arap 934 $pth->finish; 935 936 $amount = $form->round_amount($trans{$form->{"id_$i"}}{paid} + $form->{"paid_$i"} + $form->{"discount_$i"}, $form->{precision}); 937 938 # if discount taxable adjust ar/ap amount 939 if ($defaults{cdt} && !$trans{$form->{"id_$i"}}{taxincluded}) { 940 $trans{$form->{"id_$i"}}{amount} -= $cdt; 941 } 942 943 # update AR/AP transaction 944 $query = qq|UPDATE $form->{arap} set 945 amount = $trans{$form->{"id_$i"}}{amount}, 946 paid = $amount, 947 datepaid = '$form->{datepaid}', 948 bank_id = (SELECT id FROM chart WHERE accno = '$paymentaccno'), 949 paymentmethod_id = $paymentmethod_id 950 WHERE id = $form->{"id_$i"}|; 951 $dbh->do($query) || $form->dberror($query); 952 953 %audittrail = ( tablename => $form->{arap}, 954 reference => $form->{source}, 955 formname => $form->{formname}, 956 action => $action, 957 id => $form->{"id_$i"} ); 958 959 $form->audittrail($dbh, "", \%audittrail); 960 961 962 if ($form->{batch}) { 963 # add voucher 964 $vth->execute($form->{"id_$i"}); 965 $vth->finish; 966 967 # update batch 968 $form->update_balance($dbh, 969 'br', 970 'amount', 971 qq|id = $form->{batchid}|, 972 $amount); 973 } 974 } 975 } 976 977 # record a AR/AP with a payment 978 if ($form->round_amount($paymentamount, $form->{precision})) { 979 $form->{invnumber} = ""; 980 if ($assignvoucherid) { 981 for (qw(id number)) { delete $form->{"voucher$_"} } 982 } 983 OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml); 984 } 985 986 $form->remove_locks($myconfig, $dbh, $form->{arap}) if $form->{payment} eq 'payment'; 987 988 my $rc = $dbh->commit; 989 $dbh->disconnect; 990 991 $rc; 992 993} 994 995 996 997sub invoice_ids { 998 my ($self, $myconfig, $form) = @_; 999 1000 # connect to database 1001 my $dbh = $form->dbconnect($myconfig); 1002 1003 my $datepaid = ($form->{datepaid}) ? "date '$form->{datepaid}'" : 'current_date'; 1004 1005 $form->{vc} =~ s/;//g; 1006 $form->{arap} = ($form->{vc} eq 'customer') ? 'ar' : 'ap'; 1007 1008 my $query = qq|SELECT DISTINCT a.id, a.invnumber, a.transdate, a.duedate, 1009 a.description AS invdescription, 1010 a.amount, a.paid, vc.$form->{vc}number, vc.name, 1011 a.$form->{vc}_id, a.cashdiscount, a.netamount, 1012 $datepaid <= a.transdate + a.discountterms AS calcdiscount, 1013 a.exchangerate, 1014 (SELECT acc.amount 1015 FROM acc_trans acc 1016 JOIN chart c ON (c.id = acc.chart_id) 1017 WHERE acc.trans_id = ac.trans_id 1018 AND acc.fx_transaction = '0' 1019 AND c.link LIKE '%$form->{ARAP}_discount%') AS discount 1020 FROM acc_trans ac 1021 JOIN $form->{arap} a ON (a.id = ac.trans_id) 1022 JOIN $form->{vc} vc ON (vc.id = a.$form->{vc}_id) 1023 WHERE a.id = ?|; 1024 my $sth = $dbh->prepare($query) || $form->dberror($query); 1025 1026 my $ref; 1027 1028 for (1 .. $form->{rowcount}) { 1029 for $id (split / /, $form->{"id_$_"}) { 1030 $sth->execute($id) || $form->dberror; 1031 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1032 push @{ $form->{PR} }, $ref; 1033 } 1034 $sth->finish; 1035 } 1036 } 1037 1038 $dbh->disconnect; 1039 1040} 1041 1042 1043sub payment_register { 1044 my ($self, $myconfig, $form) = @_; 1045 1046 # connect to database 1047 my $dbh = $form->dbconnect($myconfig); 1048 1049 my %defaults = $form->get_defaults($dbh, \@{['precision']}); 1050 $form->{precision} = $defaults{precision}; 1051 1052 my $query = qq|SELECT c.id, c.accno, c.description, 1053 l.description AS translation 1054 FROM chart c 1055 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 1056 WHERE c.charttype = 'A' 1057 AND c.link LIKE '%$form->{ARAP}_paid%' 1058 AND c.closed = '0' 1059 ORDER BY c.accno|; 1060 my $sth = $dbh->prepare($query); 1061 $sth->execute || $form->dberror($query); 1062 1063 my @accno; 1064 my $ref; 1065 my $arap; 1066 my $ml = 1; 1067 1068 if ($form->{ARAP} eq 'AP') { 1069 $arap = 'ap'; 1070 } else { 1071 $arap = 'ar'; 1072 $ml = -1; 1073 } 1074 1075 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1076 $ref->{description} = $ref->{translation} if $ref->{translation}; 1077 push @accno, $ref; 1078 } 1079 $sth->finish; 1080 1081 $query = qq|SELECT acc.amount * $ml AS amount, acc.transdate AS datepaid, 1082 acc.source, acc.trans_id, 1083 vc.name, vc.id AS $form->{vc}_id 1084 FROM acc_trans acc 1085 JOIN $arap a ON (a.id = acc.trans_id) 1086 JOIN $form->{vc} vc ON (vc.id = a.$form->{vc}_id)|; 1087 1088 my $where = "WHERE acc.fx_transaction = '0' 1089 AND acc.chart_id = ?"; 1090 1091 unless ($form->{datepaidfrom} || $form->{datepaidto}) { 1092 ($form->{datepaidfrom}, $form->{datepaidto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; 1093 } 1094 if ($form->{datepaidfrom}) { 1095 $where .= " AND acc.transdate >= '$form->{datepaidfrom}'"; 1096 } 1097 if ($form->{datepaidto}) { 1098 $where .= " AND acc.transdate <= '$form->{datepaidto}'"; 1099 } 1100 if ($form->{"$form->{vc}_id"}) { 1101 $where .= qq| AND vc.id = $form->{"$form->{vc}_id"}|; 1102 } 1103 $query .= qq| $where ORDER BY acc.source|; 1104 1105 $sth = $dbh->prepare($query); 1106 1107 # check if void 1108 $query = qq|SELECT SUM(acc.amount) * $ml AS amount, vc.id 1109 FROM acc_trans acc 1110 JOIN $arap a ON (a.id = acc.trans_id) 1111 JOIN $form->{vc} vc ON (vc.id = a.$form->{vc}_id) 1112 WHERE acc.fx_transaction = '0' 1113 AND acc.source = ? 1114 AND acc.chart_id = ? 1115 GROUP BY vc.id|; 1116 my $ach = $dbh->prepare($query); 1117 1118 # printed 1119 my $formname = ($form->{ARAP} eq 'AP') ? 'check' : 'payment'; 1120 $query = qq|SELECT printed 1121 FROM status 1122 WHERE formname LIKE '$formname' 1123 AND printed 1124 AND trans_id = ?|; 1125 my $pth = $dbh->prepare($query); 1126 1127 my $accno; 1128 ($accno) = split /--/, $form->{accno}; 1129 if ($accno) { 1130 @accno = grep { $_->{accno} eq $accno } @accno; 1131 } 1132 1133 my $samesource; 1134 my %source; 1135 my %inv; 1136 my (undef, $option) = split /--/, $form->{option}; 1137 1138 for (@accno) { 1139 $sth->execute($_->{id}); 1140 1141 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1142 if ($form->{checknumberfrom}) { 1143 next if $ref->{source} < $form->{checknumberfrom}; 1144 } 1145 if ($form->{checknumberto}) { 1146 next if $ref->{source} > $form->{checknumberto}; 1147 } 1148 1149 if ($option eq 'all') { 1150 if ($ref->{amount}) { 1151 $ach->execute($ref->{source}, $_->{id}); 1152 ($amount) = $ach->fetchrow_array; 1153 $ach->finish; 1154 if ($form->round_amount($amount,10) == 0) { 1155 $ref->{amount} = 0; 1156 next if $inv{$ref->{trans_id}}; 1157 $inv{$ref->{trans_id}} = 1; 1158 $ref->{void} = 1; 1159 } 1160 } 1161 } elsif ($option eq 'void') { 1162 $ach->execute($ref->{source}, $_->{id}); 1163 ($amount) = $ach->fetchrow_array; 1164 $ach->finish; 1165 next if ($ref->{amount} > 0); 1166 $ref->{amount} = 0; 1167 next if $form->round_amount($amount,10); 1168 } elsif ($option eq 'exclude') { 1169 $ach->execute($ref->{source}, $_->{id}); 1170 ($amount) = $ach->fetchrow_array; 1171 $ach->finish; 1172 next if ($form->round_amount($amount,10) == 0); 1173 } elsif ($option eq 'printed') { 1174 $pth->execute($ref->{trans_id}); 1175 ($ok) = $pth->fetchrow_array; 1176 $pth->finish; 1177 next unless $ok; 1178 1179 $ach->execute($ref->{source}, $_->{id}); 1180 ($amount) = $ach->fetchrow_array; 1181 $ach->finish; 1182 next if ($form->round_amount($amount,10) == 0); 1183 1184 } elsif ($option eq 'notprinted') { 1185 $pth->execute($ref->{trans_id}); 1186 ($ok) = $pth->fetchrow_array; 1187 $pth->finish; 1188 next if $ok; 1189 1190 $ach->execute($ref->{source}, $_->{id}); 1191 ($amount) = $ach->fetchrow_array; 1192 $ach->finish; 1193 next if ($form->round_amount($amount,10) == 0); 1194 1195 } 1196 1197 $ref->{datetonum} = $form->datetonum($myconfig, $ref->{datepaid}); 1198 1199 $form->{"$_->{accno}"} = "$_->{accno}--$_->{description}"; 1200 1201 if ($ref->{source} && $ref->{source} eq $samesource) { 1202 $i = @{ $source{$_->{accno}} }; 1203 $source{$_->{accno}}[$i-1]->{amount} += $ref->{amount}; 1204 $source{$_->{accno}}[$i-1]->{trans_id} .= "\n$ref->{trans_id}"; 1205 } else { 1206 push @{ $source{$_->{accno}} }, $ref; 1207 } 1208 1209 $samesource = $ref->{source}; 1210 } 1211 $sth->finish; 1212 } 1213 1214 for (keys %source) { 1215 for $ref (sort { sortsource($a, $b, $form) } @{ $source{$_} }) { 1216 push @{ $form->{CHK}{$_} }, $ref; 1217 } 1218 } 1219 1220 $dbh->disconnect; 1221 1222} 1223 1224 1225sub sortsource { 1226 my ($a, $b, $form) = @_; 1227 1228 if ($form->{sort} eq 'datepaid') { 1229 if ($form->{direction} eq 'DESC') { 1230 return $b->{datetonum} <=> $a->{datetonum}; 1231 } 1232 return $a->{datetonum} <=> $b->{datetonum}; 1233 } 1234 1235 if ($form->{sort} eq 'source') { 1236 if ($form->{direction} eq 'DESC') { 1237 return $b->{source} <=> $a->{source}; 1238 } 1239 return $a->{source} <=> $b->{source}; 1240 } 1241 1242 if ($form->{direction} eq 'DESC') { 1243 return $b->{$form->{sort}} cmp $a->{$form->{sort}}; 1244 } 1245 return $a->{$form->{sort}} cmp $b->{$form->{sort}}; 1246 1247} 1248 1249 1250sub void_payments { 1251 my ($self, $myconfig, $form) = @_; 1252 1253 # connect to database 1254 my $dbh = $form->dbconnect_noauto($myconfig); 1255 1256 my $query = qq|SELECT SUM(acc.amount), MAX(acc.id), acc.project_id, 1257 acc.chart_id 1258 FROM acc_trans acc 1259 JOIN chart c ON (c.id = acc.chart_id) 1260 WHERE fx_transaction = '0' 1261 AND c.accno = ? 1262 AND acc.trans_id = ? 1263 AND acc.source = ? 1264 GROUP BY acc.project_id, acc.chart_id|; 1265 my $sth = $dbh->prepare($query) || $form->dberror($query); 1266 1267 $query = qq|INSERT INTO acc_trans 1268 (trans_id, chart_id, amount, source, project_id, id) 1269 VALUES (?, ?, ?, ?, ?, ?)|; 1270 my $ath = $dbh->prepare($query) || $form->dberror($query); 1271 1272 my $arap = lc $form->{ARAP}; 1273 1274 for (1 .. $form->{rowcount}) { 1275 if ($form->{"id_$_"}) { 1276 for my $trans_id (split /\n/, $form->{"id_$_"}) { 1277 $sth->execute($form->{"accno_$_"}, $trans_id, $form->{"source_$_"}); 1278 my ($amount, $id, $project_id, $chart_id) = $sth->fetchrow_array; 1279 $sth->finish; 1280 1281 $amount *= -1; 1282 $id++; 1283 $ath->execute($trans_id, $chart_id, $amount, $form->{"source_$_"}, $project_id, $id); 1284 $ath->finish; 1285 1286 $form->update_balance($dbh, 1287 $arap, 1288 "paid", 1289 qq|id = $trans_id|, 1290 $amount); 1291 } 1292 } 1293 } 1294 1295 my $rc = $dbh->commit; 1296 $dbh->disconnect; 1297 1298 $rc; 1299 1300} 1301 1302 1303sub create_selects { 1304 my ($self, $myconfig, $form) = @_; 1305 1306 my ($query, $sth); 1307 1308 my $dbh = $form->dbconnect($myconfig); 1309 1310 $form->get_peripherals($dbh); 1311 1312 $form->all_languages($myconfig, $dbh); 1313 1314 $query = qq|SELECT id FROM chart WHERE accno = '$form->{"accno_1"}'|; 1315 ($chart_id) = $dbh->selectrow_array($query); 1316 1317 my %defaults = $form->get_defaults($dbh, \@{["chknumber_${chart_id}"]}); 1318 $form->{source} = $defaults{"chknumber_${chart_id}"} + 1; 1319 1320 $dbh->disconnect; 1321 1322} 1323 1324 1325sub reissue_payment { 1326 my ($self, $myconfig, $form, $i) = @_; 1327 1328 my $dbh = $form->dbconnect_noauto($myconfig); 1329 1330 my %defaults = $form->get_defaults($dbh, \@{['precision']}); 1331 $form->{precision} = $defaults{precision}; 1332 1333 my $query; 1334 my $sth; 1335 my $tth; 1336 my $ath; 1337 my $zth; 1338 my $ref; 1339 my $trans_id; 1340 1341 my $rc = 0; 1342 my $arap = lc $form->{ARAP}; 1343 1344 # retrieve name, address 1345 $query = qq|SELECT vc.*, ad.*, current_date AS datepaid 1346 FROM $form->{vc} vc 1347 JOIN address ad ON (ad.trans_id = vc.id) 1348 WHERE vc.id = $form->{"$form->{vc}_id"}|; 1349 $sth = $dbh->prepare($query); 1350 $sth->execute || $form->dberror($query); 1351 $ref = $sth->fetchrow_hashref(NAME_lc); 1352 for (qw(name address1 address2 city state zipcode country datepaid)) { $form->{$_} = $ref->{$_} } 1353 $sth->finish; 1354 1355 # invoices 1356 $query = qq|SELECT a.*, a.transdate AS invdate 1357 FROM $arap a 1358 WHERE a.id = ?|; 1359 $sth = $dbh->prepare($query); 1360 1361 $query = qq|SELECT id 1362 FROM chart 1363 WHERE accno = '$form->{"accno_$i"}'|; 1364 my ($chart_id) = $dbh->selectrow_array($query); 1365 1366 # update acc_trans for each trans_id 1367 $query = qq|UPDATE acc_trans SET 1368 source = '$form->{source}', 1369 transdate = '$form->{datepaid}' 1370 WHERE source = '$form->{"source_$i"}' 1371 AND fx_transaction = '0' 1372 AND trans_id = ? 1373 AND chart_id = $chart_id|; 1374 $tth = $dbh->prepare($query); 1375 1376 $query = qq|UPDATE $arap 1377 SET datepaid = '$form->{datepaid}' 1378 WHERE id = ?|; 1379 $ath = $dbh->prepare($query); 1380 1381 # amount of check for each invoice 1382 $query = qq|SELECT SUM(amount) 1383 FROM acc_trans 1384 WHERE fx_transaction = '0' 1385 AND trans_id = ? 1386 AND source = '$form->{"source_$i"}' 1387 AND chart_id = $chart_id|; 1388 $zth = $dbh->prepare($query); 1389 1390 $form->{amount} = 0; 1391 for (qw(invnumber invdescription invdate due paid)) { @{ $form->{$_} } = () } 1392 1393 for $trans_id (split /\n/, $form->{"id_$i"}) { 1394 1395 $zth->execute($trans_id); 1396 $paid = 0; 1397 while ($amount = $zth->fetchrow_array) { 1398 $form->{amount} += $amount; 1399 $paid += $amount; 1400 } 1401 $zth->finish; 1402 1403 $sth->execute($trans_id); 1404 1405 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1406 $ref->{due} = $form->format_amount($myconfig, $ref->{amount}, $form->{precision}); 1407 $ref->{paid} = $form->format_amount($myconfig, $paid, $form->{precision}); 1408 for (qw(invnumber invdescription invdate due paid)) { push @{ $form->{$_} }, $ref->{$_} } 1409 } 1410 $sth->finish; 1411 1412 # update source, datepaid 1413 $tth->execute($trans_id); 1414 $tth->finish; 1415 1416 # update datepaid 1417 $ath->execute($trans_id); 1418 $ath->finish; 1419 } 1420 1421 $form->{"source_$i"} = $form->{source}; 1422 1423 my $chkno = ($form->{ARAP} eq 'AP') ? qq|chknumber_$form->{"accno_$i"}| : qq|receipt_$form->{"accno_$i"}|; 1424 # record last check number 1425 $form->update_defaults($myconfig, $chkno, $dbh, $form->{source}); 1426 1427 my $rc = $dbh->commit; 1428 $dbh->disconnect; 1429 1430 $rc; 1431 1432} 1433 1434 1435 14361; 1437 1438