summaryrefslogtreecommitdiff
path: root/LedgerSMB/CP.pm
blob: 6dd6df4363ebe845b7dad32f0efbbb8ccc072f14 (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://www.ledgersmb.org/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2003
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #
  25. #======================================================================
  26. #
  27. # This file has undergone whitespace cleanup.
  28. #
  29. #======================================================================
  30. #
  31. # Check and receipt printing payment module backend routines
  32. # Number to text conversion routines are in
  33. # locale/{countrycode}/Num2text
  34. #
  35. #======================================================================
  36. package CP;
  37. use LedgerSMB::Sysconfig;
  38. sub new {
  39. my ( $type, $countrycode ) = @_;
  40. $self = {};
  41. use LedgerSMB::Num2text;
  42. use LedgerSMB::Locale;
  43. $self->{'locale'} = LedgerSMB::Locale->get_handle($countrycode);
  44. bless $self, $type;
  45. }
  46. sub paymentaccounts {
  47. my ( $self, $myconfig, $form ) = @_;
  48. my $dbh = $form->{dbh};
  49. my $query = qq|SELECT accno, description, link
  50. FROM chart
  51. WHERE link LIKE ?
  52. ORDER BY accno|;
  53. my $sth = $dbh->prepare($query);
  54. $sth->execute("%$form->{ARAP}%") || $form->dberror($query);
  55. $form->{PR}{ $form->{ARAP} } = ();
  56. $form->{PR}{"$form->{ARAP}_paid"} = ();
  57. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  58. foreach my $item ( split /:/, $ref->{link} ) {
  59. if ( $item eq $form->{ARAP} ) {
  60. push @{ $form->{PR}{ $form->{ARAP} } }, $ref;
  61. }
  62. if ( $item eq "$form->{ARAP}_paid" ) {
  63. push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
  64. }
  65. }
  66. }
  67. $sth->finish;
  68. # get currencies and closedto
  69. $query = qq|
  70. SELECT value, (SELECT value FROM defaults
  71. WHERE setting_key = 'closedto'),
  72. current_date
  73. FROM defaults
  74. WHERE setting_key = 'curr'|;
  75. ( $form->{currencies}, $form->{closedto}, $form->{datepaid} ) =
  76. $dbh->selectrow_array($query);
  77. if ( $form->{payment} eq 'payments' ) {
  78. # get language codes
  79. $query = qq|SELECT *
  80. FROM language
  81. ORDER BY 2|;
  82. $sth = $dbh->prepare($query);
  83. $sth->execute || $self->dberror($query);
  84. $form->{all_language} = ();
  85. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  86. push @{ $form->{all_language} }, $ref;
  87. }
  88. $sth->finish;
  89. $form->all_departments( $myconfig, $dbh, $form->{vc} );
  90. }
  91. $dbh->commit;
  92. }
  93. sub get_openvc {
  94. my ( $self, $myconfig, $form ) = @_;
  95. my $dbh = $form->{dbh};
  96. my $arap = ( $form->{vc} eq 'customer' ) ? 'ar' : 'ap';
  97. my $query = qq|SELECT count(*)
  98. FROM $form->{vc} ct, $arap a
  99. WHERE a.$form->{vc}_id = ct.id
  100. AND a.amount != a.paid|;
  101. my ($count) = $dbh->selectrow_array($query);
  102. my $sth;
  103. my $ref;
  104. my $i = 0;
  105. my $where = qq|WHERE a.$form->{vc}_id = ct.id
  106. AND a.amount != a.paid|;
  107. if ( $form->{ $form->{vc} } ) {
  108. my $var = $dbh->quote( $form->like( lc $form->{ $form->{vc} } ) );
  109. $where .= " AND lower(name) LIKE $var";
  110. }
  111. # build selection list
  112. $query = qq|SELECT DISTINCT ct.*
  113. FROM $form->{vc} ct, $arap a
  114. $where
  115. ORDER BY name|;
  116. $sth = $dbh->prepare($query);
  117. $sth->execute || $form->dberror($query);
  118. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  119. $i++;
  120. push @{ $form->{name_list} }, $ref;
  121. }
  122. $sth->finish;
  123. $form->all_departments( $myconfig, $dbh, $form->{vc} );
  124. # get language codes
  125. $query = qq|SELECT *
  126. FROM language
  127. ORDER BY 2|;
  128. $sth = $dbh->prepare($query);
  129. $sth->execute || $self->dberror($query);
  130. $form->{all_language} = ();
  131. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  132. push @{ $form->{all_language} }, $ref;
  133. }
  134. $sth->finish;
  135. # get currency for first name
  136. if ( @{ $form->{name_list} } ) {
  137. # Chris T: I don't like this but it seems safe injection-wise
  138. # Leaving it so we can change it when we go to a new system
  139. $query = qq|SELECT curr
  140. FROM $form->{vc}
  141. WHERE id = $form->{name_list}->[0]->{id}|;
  142. ( $form->{currency} ) = $dbh->selectrow_array($query);
  143. $form->{currency} ||= $form->{defaultcurrency};
  144. }
  145. $dbh->commit;
  146. $i;
  147. }
  148. sub get_openinvoices {
  149. my ( $self, $myconfig, $form ) = @_;
  150. my $null;
  151. my $department_id;
  152. # connect to database
  153. my $dbh = $form->{dbh};
  154. $vc_id = $dbh->quote( $form->{"$form->{vc}_id"} );
  155. my $where = qq|WHERE a.$form->{vc}_id = $vc_id
  156. AND a.amount != a.paid|;
  157. $curr = $dbh->quote( $form->{currency} );
  158. $where .= qq| AND a.curr = $curr| if $form->{currency};
  159. my $sortorder = "transdate, invnumber";
  160. my ($buysell);
  161. if ( $form->{vc} eq 'customer' ) {
  162. $buysell = "buy";
  163. }
  164. else {
  165. $buysell = "sell";
  166. }
  167. if ( $form->{payment} eq 'payments' ) {
  168. $where = qq|WHERE a.amount != a.paid|;
  169. $where .= qq| AND a.curr = $curr| if $form->{currency};
  170. if ( $form->{duedatefrom} ) {
  171. $where .= qq| AND a.duedate >=
  172. | . $dbh->quote( $form->{duedatefrom} );
  173. }
  174. if ( $form->{duedateto} ) {
  175. $where .=
  176. qq| AND a.duedate <= | . $dbh->quote( $form->{duedateto} );
  177. }
  178. $sortorder = "name, transdate";
  179. }
  180. ( $null, $department_id ) = split /--/, $form->{department};
  181. if ($department_id) {
  182. $where .= qq| AND a.department_id = $department_id|;
  183. }
  184. my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid,
  185. a.curr, c.name, a.$form->{vc}_id, c.language_code
  186. FROM $form->{arap} a
  187. JOIN $form->{vc} c ON (c.id = a.$form->{vc}_id)
  188. $where
  189. ORDER BY $sortorder|;
  190. my $sth = $dbh->prepare($query);
  191. $sth->execute || $form->dberror($query);
  192. $query = qq|SELECT s.spoolfile
  193. FROM status s
  194. WHERE s.formname = '$form->{formname}'
  195. AND s.trans_id = ?|;
  196. my $vth = $dbh->prepare($query);
  197. my $spoolfile;
  198. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  199. # if this is a foreign currency transaction get exchangerate
  200. $ref->{exchangerate} =
  201. $form->get_exchangerate( $dbh, $ref->{curr}, $ref->{transdate},
  202. $buysell )
  203. if ( $form->{currency} ne $form->{defaultcurrency} );
  204. $vth->execute( $ref->{id} );
  205. $ref->{queue} = "";
  206. while ( ($spoolfile) = $vth->fetchrow_array ) {
  207. $ref->{queued} .= "$form->{formname} $spoolfile ";
  208. }
  209. $vth->finish;
  210. $ref->{queued} =~ s/ +$//g;
  211. push @{ $form->{PR} }, $ref;
  212. }
  213. $sth->finish;
  214. $dbh->commit;
  215. }
  216. sub post_payment {
  217. my ( $self, $myconfig, $form ) = @_;
  218. # connect to database, turn AutoCommit off
  219. my $dbh = $form->{dbh};
  220. my $sth;
  221. my ($paymentaccno) = split /--/, $form->{account};
  222. # if currency ne defaultcurrency update exchangerate
  223. if ( $form->{currency} ne $form->{defaultcurrency} ) {
  224. $form->{exchangerate} =
  225. $form->parse_amount( $myconfig, $form->{exchangerate} );
  226. if ( $form->{vc} eq 'customer' ) {
  227. $form->update_exchangerate( $dbh, $form->{currency},
  228. $form->{datepaid}, $form->{exchangerate}, 0 );
  229. }
  230. else {
  231. $form->update_exchangerate( $dbh, $form->{currency},
  232. $form->{datepaid}, 0, $form->{exchangerate} );
  233. }
  234. }
  235. else {
  236. $form->{exchangerate} = 1;
  237. }
  238. my $query = qq|
  239. SELECT (SELECT value FROM defaults
  240. WHERE setting_key='fxgain_accno_id'),
  241. (SELECT value FROM defaults
  242. WHERE setting_key='fxloss_accno_id')|;
  243. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  244. my ($buysell);
  245. if ( $form->{vc} eq 'customer' ) {
  246. $buysell = "buy";
  247. }
  248. else {
  249. $buysell = "sell";
  250. }
  251. my $ml;
  252. my $where;
  253. if ( $form->{ARAP} eq 'AR' ) {
  254. $ml = 1;
  255. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  256. }
  257. else {
  258. $ml = -1;
  259. $where =
  260. qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  261. }
  262. my $paymentamount = $form->parse_amount( $myconfig, $form->{amount} );
  263. # query to retrieve paid amount
  264. $query = qq|SELECT paid
  265. FROM $form->{arap}
  266. WHERE id = ?
  267. FOR UPDATE|;
  268. my $pth = $dbh->prepare($query) || $form->dberror($query);
  269. my %audittrail;
  270. # go through line by line
  271. for my $i ( 1 .. $form->{rowcount} ) {
  272. $form->{"paid_$i"} =
  273. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  274. $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} );
  275. if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) {
  276. $paymentamount -= $form->{"paid_$i"};
  277. # get exchangerate for original
  278. $query = qq|
  279. SELECT $buysell
  280. FROM exchangerate e
  281. JOIN $form->{arap} a
  282. ON (a.transdate = e.transdate)
  283. WHERE e.curr = ?
  284. AND a.id = ?|;
  285. my $sth = $dbh->prepare($query);
  286. $sth->execute( $form->{currency}, $form->{"id_$i"} );
  287. my ($exchangerate) = $sth->fetchrow_array();
  288. $exchangerate = 1 unless $exchangerate;
  289. $query = qq|
  290. SELECT c.id
  291. FROM chart c
  292. JOIN acc_trans a ON (a.chart_id = c.id)
  293. WHERE $where
  294. AND a.trans_id = ?|;
  295. my $sth = $dbh->prepare($query);
  296. $sth->execute( $form->{"id_$i"} );
  297. my ($id) = $sth->fetchrow_array;
  298. $amount =
  299. $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 );
  300. # add AR/AP
  301. $query = qq|
  302. INSERT INTO acc_trans
  303. (trans_id, chart_id, transdate,
  304. amount)
  305. VALUES (?, ?,
  306. ?,
  307. ?)|;
  308. $sth = $dbh->prepare($query);
  309. $sth->execute( $form->{"id_$i"}, $id, $form->{date_paid},
  310. $amount * $ml )
  311. || $form->dberror( $query, __file__, __line__ );
  312. # add payment
  313. $query = qq|
  314. INSERT INTO acc_trans
  315. (trans_id, chart_id, transdate,
  316. amount, source, memo)
  317. VALUES (?, (SELECT id
  318. FROM chart
  319. WHERE accno = ?),
  320. ?, ?, ?, ?)|;
  321. $sth = $dbh->prepare($query);
  322. $sth->execute( $form->{"id_$i"}, $paymentaccno, $form->{datepaid},
  323. $form->{"paid_$i"} * $ml * -1,
  324. $form->{source}, $form->{memo} )
  325. || $form->dberror( $query, 'CP.pm', 444 );
  326. # add exchangerate difference if currency ne defaultcurrency
  327. $amount =
  328. $form->round_amount(
  329. $form->{"paid_$i"} * ( $form->{exchangerate} - 1 ), 2 );
  330. if ($amount) {
  331. # exchangerate difference
  332. $query = qq|
  333. INSERT INTO acc_trans
  334. (trans_id, chart_id,
  335. transdate, amount, cleared,
  336. fx_transaction, source)
  337. VALUES (?, (SELECT id
  338. FROM chart
  339. WHERE accno = ?),
  340. ?, ?, '0', '1',
  341. ?)|;
  342. $sth = $dbh->prepare($query);
  343. $sth->execute(
  344. $form->{"id_$i"}, $paymentaccno, $form->{datepaid},
  345. $amount * $ml * -1, $form->{source}
  346. ) || $form->dberror( $query, 'CP.pm', 470 );
  347. # gain/loss
  348. $amount = (
  349. $form->round_amount(
  350. $form->{"paid_$i"} * $exchangerate, 2
  351. ) - $form->round_amount(
  352. $form->{"paid_$i"} * $form->{exchangerate}, 2
  353. )
  354. ) * $ml * -1;
  355. if ($amount) {
  356. my $accno_id =
  357. ( $amount > 0 )
  358. ? $fxgain_accno_id
  359. : $fxloss_accno_id;
  360. $query = qq|
  361. INSERT INTO acc_trans
  362. (trans_id,
  363. chart_id,
  364. transdate,
  365. amount, cleared,
  366. fx_transaction)
  367. VALUES (?, ?, ?, ?, '0', '1')|;
  368. $sth = $dbh->prepare($query);
  369. $sth->execute(
  370. $form->{"id_$i"}, $accno_id,
  371. $form->{datepaid}, $amount
  372. ) || $form->dberror( $query, 'CP.pm', 506 );
  373. }
  374. }
  375. $form->{"paid_$i"} =
  376. $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 );
  377. $pth->execute( $form->{"id_$i"} ) || $form->dberror($pth->statement);
  378. ($amount) = $pth->fetchrow_array;
  379. $pth->finish;
  380. $amount += $form->{"paid_$i"};
  381. # update AR/AP transaction
  382. $query = qq|
  383. UPDATE $form->{arap}
  384. SET paid = ?,
  385. datepaid = ?
  386. WHERE id = ?|;
  387. $sth = $dbh->prepare($query);
  388. $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} )
  389. || $form->dberror( $query, 'CP.pm', 530 );
  390. %audittrail = (
  391. tablename => $form->{arap},
  392. reference => $form->{source},
  393. formname => $form->{formname},
  394. action => 'posted',
  395. id => $form->{"id_$i"}
  396. );
  397. $form->audittrail( $dbh, "", \%audittrail );
  398. }
  399. }
  400. # record a AR/AP with a payment
  401. if ( $form->round_amount( $paymentamount, 2 ) ) {
  402. $form->{invnumber} = "";
  403. OP::overpayment( "", $myconfig, $form, $dbh, $paymentamount, $ml, 1 );
  404. }
  405. my $rc = $dbh->commit;
  406. $rc;
  407. }
  408. sub post_payments {
  409. my ( $self, $myconfig, $form ) = @_;
  410. # connect to database, turn AutoCommit off
  411. my $dbh = $form->{dbh};
  412. my $sth;
  413. my ($paymentaccno) = split /--/, $form->{account};
  414. # if currency ne defaultcurrency update exchangerate
  415. if ( $form->{currency} ne $form->{defaultcurrency} ) {
  416. $form->{exchangerate} =
  417. $form->parse_amount( $myconfig, $form->{exchangerate} );
  418. if ( $form->{vc} eq 'customer' ) {
  419. $form->update_exchangerate( $dbh, $form->{currency},
  420. $form->{datepaid}, $form->{exchangerate}, 0 );
  421. }
  422. else {
  423. $form->update_exchangerate( $dbh, $form->{currency},
  424. $form->{datepaid}, 0, $form->{exchangerate} );
  425. }
  426. }
  427. else {
  428. $form->{exchangerate} = 1;
  429. }
  430. my $query = qq|
  431. SELECT (SELECT value FROM defaults
  432. WHERE setting_key='fxgain_accno_id'),
  433. (SELECT value FROM defaults
  434. WHERE setting_key='fxloss_accno_id')|;
  435. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  436. my ($buysell);
  437. if ( $form->{vc} eq 'customer' ) {
  438. $buysell = "buy";
  439. }
  440. else {
  441. $buysell = "sell";
  442. }
  443. my $ml;
  444. my $where;
  445. if ( $form->{ARAP} eq 'AR' ) {
  446. $ml = 1;
  447. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  448. }
  449. else {
  450. $ml = -1;
  451. $where =
  452. qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  453. }
  454. # get AR/AP account
  455. $query = qq|SELECT c.accno
  456. FROM chart c
  457. JOIN acc_trans ac ON (ac.chart_id = c.id)
  458. WHERE trans_id = ?
  459. AND $where|;
  460. my $ath = $dbh->prepare($query) || $form->dberror($query);
  461. # query to retrieve paid amount
  462. $query = qq|SELECT paid
  463. FROM $form->{arap}
  464. WHERE id = ?
  465. FOR UPDATE|;
  466. my $pth = $dbh->prepare($query) || $form->dberror($query);
  467. my %audittrail;
  468. my $overpayment = 0;
  469. my $accno_id;
  470. # go through line by line
  471. for my $i ( 1 .. $form->{rowcount} ) {
  472. $ath->execute( $form->{"id_$i"} );
  473. ( $form->{ $form->{ARAP} } ) = $ath->fetchrow_array;
  474. $ath->finish;
  475. $form->{"paid_$i"} =
  476. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  477. $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} );
  478. if ( $form->{"$form->{vc}_id_$i"} ne $sameid ) {
  479. # record a AR/AP with a payment
  480. if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) {
  481. $form->{invnumber} = "";
  482. OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml,
  483. 1 );
  484. }
  485. $overpayment = 0;
  486. $form->{"$form->{vc}_id"} = $form->{"$form->{vc}_id_$i"};
  487. for (qw(source memo)) { $form->{$_} = $form->{"${_}_$i"} }
  488. }
  489. if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) {
  490. $overpayment += ( $form->{"paid_$i"} - $form->{"due_$i"} );
  491. # get exchangerate for original
  492. $query = qq|
  493. SELECT $buysell
  494. FROM exchangerate e
  495. JOIN $form->{arap} a
  496. ON (a.transdate = e.transdate)
  497. WHERE e.curr = ?
  498. AND a.id = ?|;
  499. $sth = $dbh->prepare($query);
  500. $sth->execute( $form->{currency}, $form->{"id_$i"} )
  501. || $form->dberror( $query, 'CP.pm', 671 );
  502. my ($exchangerate) = $sth->fetchrow_array;
  503. $exchangerate ||= 1;
  504. $query = qq|
  505. SELECT c.id
  506. FROM chart c
  507. JOIN acc_trans a ON (a.chart_id = c.id)
  508. WHERE $where
  509. AND a.trans_id = ?|;
  510. $sth = $dbh->prepare($query);
  511. $sth->execute( $form->{"id_$i"} );
  512. ($id) = $sth->fetchrow_array();
  513. $paid =
  514. ( $form->{"paid_$i"} > $form->{"due_$i"} )
  515. ? $form->{"due_$i"}
  516. : $form->{"paid_$i"};
  517. $amount = $form->round_amount( $paid * $exchangerate, 2 );
  518. # add AR/AP
  519. $query = qq|
  520. INSERT INTO acc_trans
  521. (trans_id, chart_id, transdate,
  522. amount)
  523. VALUES (?, ?, ?, ?)|;
  524. $sth = $dbh->prepare($query);
  525. $sth->execute( $form->{"id_$i"}, $id, $form->{datepaid},
  526. $amount * $ml )
  527. || $form->dberror( $query, 'CP.pm', 701 );
  528. $query = qq|SELECT id
  529. FROM chart
  530. WHERE accno = ?|;
  531. $sth = $dbh->prepare($query);
  532. $sth->execute($paymentaccno);
  533. ($accno_id) = $sth->fetchrow_array;
  534. # add payment
  535. $query = qq|
  536. INSERT INTO acc_trans
  537. (trans_id, chart_id, transdate,
  538. amount, source, memo)
  539. VALUES (?, ?, ?, ?, ?, ?)|;
  540. $sth = $dbh->prepare($query);
  541. $sth->execute(
  542. $form->{"id_$i"}, $accno_id, $form->{datepaid},
  543. $paid * $ml * -1, $form->{source}, $form->{memo}
  544. ) || $form->dberror( $query, 'CP.pm', 723 );
  545. # add exchangerate difference if currency ne defaultcurrency
  546. $amount =
  547. $form->round_amount(
  548. $paid * ( $form->{exchangerate} - 1 ) * $ml * -1, 2 );
  549. if ($amount) {
  550. # exchangerate difference
  551. $query = qq|
  552. INSERT INTO acc_trans
  553. (trans_id, chart_id,
  554. transdate,
  555. amount, source)
  556. VALUES (?, ?, ?, ?, ?)|;
  557. $sth = $dbh->prepare($query);
  558. $sth->execute(
  559. $form->{"id_$i"}, $accno_id, $form->{datepaid},
  560. $amount, $form->{source}
  561. ) || $form->dberror( $query, 'CP.pm', 748 );
  562. # gain/loss
  563. $amount =
  564. ( $form->round_amount( $paid * $exchangerate, 2 ) -
  565. $form->round_amount( $paid * $form->{exchangerate}, 2 ) )
  566. * $ml * -1;
  567. if ($amount) {
  568. $accno_id =
  569. ( $amount > 0 )
  570. ? $fxgain_accno_id
  571. : $fxloss_accno_id;
  572. $query = qq|
  573. INSERT INTO acc_trans
  574. (trans_id,
  575. chart_id,
  576. transdate,
  577. amount,
  578. fx_transaction)
  579. VALUES (?, ?, ?, ?, '1')|;
  580. $sth = $dbh->prepare($query);
  581. $sth->execute(
  582. $form->{"id_$i"}, $accno_id,
  583. $form->{datepaid}, $amount
  584. ) || $form->dberror( $query, 'CP.pm', 775 );
  585. }
  586. }
  587. $paid = $form->round_amount( $paid * $exchangerate, 2 );
  588. $pth->execute( $form->{"id_$i"} ) || $form->dberror($pth->statement);
  589. ($amount) = $pth->fetchrow_array;
  590. $pth->finish;
  591. $amount += $paid;
  592. # update AR/AP transaction
  593. $query = qq|
  594. UPDATE $form->{arap}
  595. SET paid = ?,
  596. datepaid = ?
  597. WHERE id = ?|;
  598. $sth = $dbh->prepare($query);
  599. $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} )
  600. || $form->dberror( $query, 'CP.pm', 796 );
  601. %audittrail = (
  602. tablename => $form->{arap},
  603. reference => $form->{source},
  604. formname => $form->{formname},
  605. action => 'posted',
  606. id => $form->{"id_$i"}
  607. );
  608. $form->audittrail( $dbh, "", \%audittrail );
  609. }
  610. $sameid = $form->{"$form->{vc}_id_$i"};
  611. }
  612. # record a AR/AP with a payment
  613. if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) {
  614. $form->{invnumber} = "";
  615. OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml, 1 );
  616. }
  617. my $rc = $dbh->commit;
  618. $rc;
  619. }
  620. 1;