summaryrefslogtreecommitdiff
path: root/LedgerSMB/CP.pm
blob: a0537f634e6b9b7f894e8db0309b8a3f13b1388b (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|
  98. SELECT count(*)
  99. FROM entity_credit_account ct
  100. JOIN $arap a USING (entity_id)
  101. WHERE a.amount != a.paid|;
  102. my ($count) = $dbh->selectrow_array($query);
  103. my $sth;
  104. my $ref;
  105. my $i = 0;
  106. my $where = qq|WHERE 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|
  113. SELECT DISTINCT ct.*, e.name, c.*, l.*
  114. FROM entity_credit_account ct
  115. JOIN $arap a USING (entity_id)
  116. JOIN company c USING (entity_id)
  117. JOIN entity e ON (e.id = a.entity_id)
  118. LEFT JOIN company_to_location c2l ON (c.id = c2l.company_id)
  119. LEFT JOIN location l ON (l.id = c2l.location_id)
  120. $where
  121. ORDER BY name|;
  122. $sth = $dbh->prepare($query);
  123. $sth->execute || $form->dberror($query);
  124. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  125. $i++;
  126. push @{ $form->{name_list} }, $ref;
  127. }
  128. $sth->finish;
  129. $form->all_departments( $myconfig, $dbh, $form->{vc} );
  130. # get language codes
  131. $query = qq|SELECT *
  132. FROM language
  133. ORDER BY 2|;
  134. $sth = $dbh->prepare($query);
  135. $sth->execute || $self->dberror($query);
  136. $form->{all_language} = ();
  137. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  138. push @{ $form->{all_language} }, $ref;
  139. }
  140. $sth->finish;
  141. # get currency for first name
  142. if ( @{ $form->{name_list} } ) {
  143. # Chris T: I don't like this but it seems safe injection-wise
  144. # Leaving it so we can change it when we go to a new system
  145. $query = qq|SELECT curr
  146. FROM $form->{vc}
  147. WHERE id = $form->{name_list}->[0]->{id}|;
  148. ( $form->{currency} ) = $dbh->selectrow_array($query);
  149. $form->{currency} ||= $form->{defaultcurrency};
  150. }
  151. $dbh->commit;
  152. $i;
  153. }
  154. sub get_openinvoices {
  155. my ( $self, $myconfig, $form ) = @_;
  156. my $null;
  157. my $department_id;
  158. # connect to database
  159. my $dbh = $form->{dbh};
  160. $vc_id = $dbh->quote( $form->{"entity_id"} );
  161. my $where = qq|WHERE a.entity__id = $vc_id
  162. AND a.amount != a.paid|;
  163. $curr = $dbh->quote( $form->{currency} );
  164. $where .= qq| AND a.curr = $curr| if $form->{currency};
  165. my $sortorder = "transdate, invnumber";
  166. my ($buysell);
  167. if ( $form->{vc} eq 'customer' ) {
  168. $buysell = "buy";
  169. }
  170. else {
  171. $buysell = "sell";
  172. }
  173. if ( $form->{payment} eq 'payments' ) {
  174. $where = qq|WHERE a.amount != a.paid|;
  175. $where .= qq| AND a.curr = $curr| if $form->{currency};
  176. if ( $form->{duedatefrom} ) {
  177. $where .= qq| AND a.duedate >=
  178. | . $dbh->quote( $form->{duedatefrom} );
  179. }
  180. if ( $form->{duedateto} ) {
  181. $where .=
  182. qq| AND a.duedate <= | . $dbh->quote( $form->{duedateto} );
  183. }
  184. $sortorder = "name, transdate";
  185. }
  186. ( $null, $department_id ) = split /--/, $form->{department};
  187. if ($department_id) {
  188. $where .= qq| AND a.department_id = $department_id|;
  189. }
  190. my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid,
  191. a.curr, c.name, a.entity_id, c.language_code
  192. FROM $form->{arap} a
  193. JOIN $form->{vc} c ON (c.entity_id = a.entity_id)
  194. $where
  195. ORDER BY $sortorder|;
  196. my $sth = $dbh->prepare($query);
  197. $sth->execute || $form->dberror($query);
  198. $query = qq|SELECT s.spoolfile
  199. FROM status s
  200. WHERE s.formname = '$form->{formname}'
  201. AND s.trans_id = ?|;
  202. my $vth = $dbh->prepare($query);
  203. my $spoolfile;
  204. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  205. # if this is a foreign currency transaction get exchangerate
  206. $ref->{exchangerate} =
  207. $form->get_exchangerate( $dbh, $ref->{curr}, $ref->{transdate},
  208. $buysell )
  209. if ( $form->{currency} ne $form->{defaultcurrency} );
  210. $vth->execute( $ref->{id} );
  211. $ref->{queue} = "";
  212. while ( ($spoolfile) = $vth->fetchrow_array ) {
  213. $ref->{queued} .= "$form->{formname} $spoolfile ";
  214. }
  215. $vth->finish;
  216. $ref->{queued} =~ s/ +$//g;
  217. push @{ $form->{PR} }, $ref;
  218. }
  219. $sth->finish;
  220. $dbh->commit;
  221. }
  222. sub post_payment {
  223. my ( $self, $myconfig, $form ) = @_;
  224. # connect to database, turn AutoCommit off
  225. my $dbh = $form->{dbh};
  226. my $sth;
  227. my ($paymentaccno) = split /--/, $form->{account};
  228. # if currency ne defaultcurrency update exchangerate
  229. if ( $form->{currency} ne $form->{defaultcurrency} ) {
  230. $form->{exchangerate} =
  231. $form->parse_amount( $myconfig, $form->{exchangerate} );
  232. if ( $form->{vc} eq 'customer' ) {
  233. $form->update_exchangerate( $dbh, $form->{currency},
  234. $form->{datepaid}, $form->{exchangerate}, 0 );
  235. }
  236. else {
  237. $form->update_exchangerate( $dbh, $form->{currency},
  238. $form->{datepaid}, 0, $form->{exchangerate} );
  239. }
  240. }
  241. else {
  242. $form->{exchangerate} = 1;
  243. }
  244. my $query = qq|
  245. SELECT (SELECT value FROM defaults
  246. WHERE setting_key='fxgain_accno_id'),
  247. (SELECT value FROM defaults
  248. WHERE setting_key='fxloss_accno_id')|;
  249. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  250. my ($buysell);
  251. if ( $form->{vc} eq 'customer' ) {
  252. $buysell = "buy";
  253. }
  254. else {
  255. $buysell = "sell";
  256. }
  257. my $ml;
  258. my $where;
  259. if ( $form->{ARAP} eq 'AR' ) {
  260. $ml = 1;
  261. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  262. }
  263. else {
  264. $ml = -1;
  265. $where =
  266. qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  267. }
  268. my $paymentamount = $form->parse_amount( $myconfig, $form->{amount} );
  269. # query to retrieve paid amount
  270. $query = qq|SELECT paid
  271. FROM $form->{arap}
  272. WHERE id = ?
  273. FOR UPDATE|;
  274. my $pth = $dbh->prepare($query) || $form->dberror($query);
  275. my %audittrail;
  276. # go through line by line
  277. for my $i ( 1 .. $form->{rowcount} ) {
  278. $form->{"paid_$i"} =
  279. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  280. $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} );
  281. if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) {
  282. $paymentamount -= $form->{"paid_$i"};
  283. # get exchangerate for original
  284. $query = qq|
  285. SELECT $buysell
  286. FROM exchangerate e
  287. JOIN $form->{arap} a
  288. ON (a.transdate = e.transdate)
  289. WHERE e.curr = ?
  290. AND a.id = ?|;
  291. my $sth = $dbh->prepare($query);
  292. $sth->execute( $form->{currency}, $form->{"id_$i"} );
  293. my ($exchangerate) = $sth->fetchrow_array();
  294. $exchangerate = 1 unless $exchangerate;
  295. $query = qq|
  296. SELECT c.id
  297. FROM chart c
  298. JOIN acc_trans a ON (a.chart_id = c.id)
  299. WHERE $where
  300. AND a.trans_id = ?|;
  301. my $sth = $dbh->prepare($query);
  302. $sth->execute( $form->{"id_$i"} );
  303. my ($id) = $sth->fetchrow_array;
  304. $amount =
  305. $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 );
  306. # add AR/AP
  307. $query = qq|
  308. INSERT INTO acc_trans
  309. (trans_id, chart_id, transdate,
  310. amount)
  311. VALUES (?, ?,
  312. ?,
  313. ?)|;
  314. $sth = $dbh->prepare($query);
  315. $sth->execute( $form->{"id_$i"}, $id, $form->{date_paid},
  316. $amount * $ml )
  317. || $form->dberror( $query, __file__, __line__ );
  318. # add payment
  319. $query = qq|
  320. INSERT INTO acc_trans
  321. (trans_id, chart_id, transdate,
  322. amount, source, memo)
  323. VALUES (?, (SELECT id
  324. FROM chart
  325. WHERE accno = ?),
  326. ?, ?, ?, ?)|;
  327. $sth = $dbh->prepare($query);
  328. $sth->execute( $form->{"id_$i"}, $paymentaccno, $form->{datepaid},
  329. $form->{"paid_$i"} * $ml * -1,
  330. $form->{source}, $form->{memo} )
  331. || $form->dberror( $query, 'CP.pm', 444 );
  332. # add exchangerate difference if currency ne defaultcurrency
  333. $amount =
  334. $form->round_amount(
  335. $form->{"paid_$i"} * ( $form->{exchangerate} - 1 ), 2 );
  336. if ($amount) {
  337. # exchangerate difference
  338. $query = qq|
  339. INSERT INTO acc_trans
  340. (trans_id, chart_id,
  341. transdate, amount, cleared,
  342. fx_transaction, source)
  343. VALUES (?, (SELECT id
  344. FROM chart
  345. WHERE accno = ?),
  346. ?, ?, '0', '1',
  347. ?)|;
  348. $sth = $dbh->prepare($query);
  349. $sth->execute(
  350. $form->{"id_$i"}, $paymentaccno, $form->{datepaid},
  351. $amount * $ml * -1, $form->{source}
  352. ) || $form->dberror( $query, 'CP.pm', 470 );
  353. # gain/loss
  354. $amount = (
  355. $form->round_amount(
  356. $form->{"paid_$i"} * $exchangerate, 2
  357. ) - $form->round_amount(
  358. $form->{"paid_$i"} * $form->{exchangerate}, 2
  359. )
  360. ) * $ml * -1;
  361. if ($amount) {
  362. my $accno_id =
  363. ( $amount > 0 )
  364. ? $fxgain_accno_id
  365. : $fxloss_accno_id;
  366. $query = qq|
  367. INSERT INTO acc_trans
  368. (trans_id,
  369. chart_id,
  370. transdate,
  371. amount, cleared,
  372. fx_transaction)
  373. VALUES (?, ?, ?, ?, '0', '1')|;
  374. $sth = $dbh->prepare($query);
  375. $sth->execute(
  376. $form->{"id_$i"}, $accno_id,
  377. $form->{datepaid}, $amount
  378. ) || $form->dberror( $query, 'CP.pm', 506 );
  379. }
  380. }
  381. $form->{"paid_$i"} =
  382. $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 );
  383. $pth->execute( $form->{"id_$i"} ) || $form->dberror($pth->statement);
  384. ($amount) = $pth->fetchrow_array;
  385. $pth->finish;
  386. $amount += $form->{"paid_$i"};
  387. # update AR/AP transaction
  388. $query = qq|
  389. UPDATE $form->{arap}
  390. SET paid = ?,
  391. datepaid = ?
  392. WHERE id = ?|;
  393. $sth = $dbh->prepare($query);
  394. $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} )
  395. || $form->dberror( $query, 'CP.pm', 530 );
  396. %audittrail = (
  397. tablename => $form->{arap},
  398. reference => $form->{source},
  399. formname => $form->{formname},
  400. action => 'posted',
  401. id => $form->{"id_$i"}
  402. );
  403. $form->audittrail( $dbh, "", \%audittrail );
  404. }
  405. }
  406. # record a AR/AP with a payment
  407. if ( $form->round_amount( $paymentamount, 2 ) ) {
  408. $form->{invnumber} = "";
  409. OP::overpayment( "", $myconfig, $form, $dbh, $paymentamount, $ml, 1 );
  410. }
  411. my $rc = $dbh->commit;
  412. $rc;
  413. }
  414. sub post_payments {
  415. my ( $self, $myconfig, $form ) = @_;
  416. # connect to database, turn AutoCommit off
  417. my $dbh = $form->{dbh};
  418. my $sth;
  419. my ($paymentaccno) = split /--/, $form->{account};
  420. # if currency ne defaultcurrency update exchangerate
  421. if ( $form->{currency} ne $form->{defaultcurrency} ) {
  422. $form->{exchangerate} =
  423. $form->parse_amount( $myconfig, $form->{exchangerate} );
  424. if ( $form->{vc} eq 'customer' ) {
  425. $form->update_exchangerate( $dbh, $form->{currency},
  426. $form->{datepaid}, $form->{exchangerate}, 0 );
  427. }
  428. else {
  429. $form->update_exchangerate( $dbh, $form->{currency},
  430. $form->{datepaid}, 0, $form->{exchangerate} );
  431. }
  432. }
  433. else {
  434. $form->{exchangerate} = 1;
  435. }
  436. my $query = qq|
  437. SELECT (SELECT value FROM defaults
  438. WHERE setting_key='fxgain_accno_id'),
  439. (SELECT value FROM defaults
  440. WHERE setting_key='fxloss_accno_id')|;
  441. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  442. my ($buysell);
  443. if ( $form->{vc} eq 'customer' ) {
  444. $buysell = "buy";
  445. }
  446. else {
  447. $buysell = "sell";
  448. }
  449. my $ml;
  450. my $where;
  451. if ( $form->{ARAP} eq 'AR' ) {
  452. $ml = 1;
  453. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  454. }
  455. else {
  456. $ml = -1;
  457. $where =
  458. qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  459. }
  460. # get AR/AP account
  461. $query = qq|SELECT c.accno
  462. FROM chart c
  463. JOIN acc_trans ac ON (ac.chart_id = c.id)
  464. WHERE trans_id = ?
  465. AND $where|;
  466. my $ath = $dbh->prepare($query) || $form->dberror($query);
  467. # query to retrieve paid amount
  468. $query = qq|SELECT paid
  469. FROM $form->{arap}
  470. WHERE id = ?
  471. FOR UPDATE|;
  472. my $pth = $dbh->prepare($query) || $form->dberror($query);
  473. my %audittrail;
  474. my $overpayment = 0;
  475. my $accno_id;
  476. # go through line by line
  477. for my $i ( 1 .. $form->{rowcount} ) {
  478. $ath->execute( $form->{"id_$i"} );
  479. ( $form->{ $form->{ARAP} } ) = $ath->fetchrow_array;
  480. $ath->finish;
  481. $form->{"paid_$i"} =
  482. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  483. $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} );
  484. if ( $form->{"$form->{vc}_id_$i"} ne $sameid ) {
  485. # record a AR/AP with a payment
  486. if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) {
  487. $form->{invnumber} = "";
  488. OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml,
  489. 1 );
  490. }
  491. $overpayment = 0;
  492. $form->{"$form->{vc}_id"} = $form->{"$form->{vc}_id_$i"};
  493. for (qw(source memo)) { $form->{$_} = $form->{"${_}_$i"} }
  494. }
  495. if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) {
  496. $overpayment += ( $form->{"paid_$i"} - $form->{"due_$i"} );
  497. # get exchangerate for original
  498. $query = qq|
  499. SELECT $buysell
  500. FROM exchangerate e
  501. JOIN $form->{arap} a
  502. ON (a.transdate = e.transdate)
  503. WHERE e.curr = ?
  504. AND a.id = ?|;
  505. $sth = $dbh->prepare($query);
  506. $sth->execute( $form->{currency}, $form->{"id_$i"} )
  507. || $form->dberror( $query, 'CP.pm', 671 );
  508. my ($exchangerate) = $sth->fetchrow_array;
  509. $exchangerate ||= 1;
  510. $query = qq|
  511. SELECT c.id
  512. FROM chart c
  513. JOIN acc_trans a ON (a.chart_id = c.id)
  514. WHERE $where
  515. AND a.trans_id = ?|;
  516. $sth = $dbh->prepare($query);
  517. $sth->execute( $form->{"id_$i"} );
  518. ($id) = $sth->fetchrow_array();
  519. $paid =
  520. ( $form->{"paid_$i"} > $form->{"due_$i"} )
  521. ? $form->{"due_$i"}
  522. : $form->{"paid_$i"};
  523. $amount = $form->round_amount( $paid * $exchangerate, 2 );
  524. # add AR/AP
  525. $query = qq|
  526. INSERT INTO acc_trans
  527. (trans_id, chart_id, transdate,
  528. amount)
  529. VALUES (?, ?, ?, ?)|;
  530. $sth = $dbh->prepare($query);
  531. $sth->execute( $form->{"id_$i"}, $id, $form->{datepaid},
  532. $amount * $ml )
  533. || $form->dberror( $query, 'CP.pm', 701 );
  534. $query = qq|SELECT id
  535. FROM chart
  536. WHERE accno = ?|;
  537. $sth = $dbh->prepare($query);
  538. $sth->execute($paymentaccno);
  539. ($accno_id) = $sth->fetchrow_array;
  540. # add payment
  541. $query = qq|
  542. INSERT INTO acc_trans
  543. (trans_id, chart_id, transdate,
  544. amount, source, memo)
  545. VALUES (?, ?, ?, ?, ?, ?)|;
  546. $sth = $dbh->prepare($query);
  547. $sth->execute(
  548. $form->{"id_$i"}, $accno_id, $form->{datepaid},
  549. $paid * $ml * -1, $form->{source}, $form->{memo}
  550. ) || $form->dberror( $query, 'CP.pm', 723 );
  551. # add exchangerate difference if currency ne defaultcurrency
  552. $amount =
  553. $form->round_amount(
  554. $paid * ( $form->{exchangerate} - 1 ) * $ml * -1, 2 );
  555. if ($amount) {
  556. # exchangerate difference
  557. $query = qq|
  558. INSERT INTO acc_trans
  559. (trans_id, chart_id,
  560. transdate,
  561. amount, source)
  562. VALUES (?, ?, ?, ?, ?)|;
  563. $sth = $dbh->prepare($query);
  564. $sth->execute(
  565. $form->{"id_$i"}, $accno_id, $form->{datepaid},
  566. $amount, $form->{source}
  567. ) || $form->dberror( $query, 'CP.pm', 748 );
  568. # gain/loss
  569. $amount =
  570. ( $form->round_amount( $paid * $exchangerate, 2 ) -
  571. $form->round_amount( $paid * $form->{exchangerate}, 2 ) )
  572. * $ml * -1;
  573. if ($amount) {
  574. $accno_id =
  575. ( $amount > 0 )
  576. ? $fxgain_accno_id
  577. : $fxloss_accno_id;
  578. $query = qq|
  579. INSERT INTO acc_trans
  580. (trans_id,
  581. chart_id,
  582. transdate,
  583. amount,
  584. fx_transaction)
  585. VALUES (?, ?, ?, ?, '1')|;
  586. $sth = $dbh->prepare($query);
  587. $sth->execute(
  588. $form->{"id_$i"}, $accno_id,
  589. $form->{datepaid}, $amount
  590. ) || $form->dberror( $query, 'CP.pm', 775 );
  591. }
  592. }
  593. $paid = $form->round_amount( $paid * $exchangerate, 2 );
  594. $pth->execute( $form->{"id_$i"} ) || $form->dberror($pth->statement);
  595. ($amount) = $pth->fetchrow_array;
  596. $pth->finish;
  597. $amount += $paid;
  598. # update AR/AP transaction
  599. $query = qq|
  600. UPDATE $form->{arap}
  601. SET paid = ?,
  602. datepaid = ?
  603. WHERE id = ?|;
  604. $sth = $dbh->prepare($query);
  605. $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} )
  606. || $form->dberror( $query, 'CP.pm', 796 );
  607. %audittrail = (
  608. tablename => $form->{arap},
  609. reference => $form->{source},
  610. formname => $form->{formname},
  611. action => 'posted',
  612. id => $form->{"id_$i"}
  613. );
  614. $form->audittrail( $dbh, "", \%audittrail );
  615. }
  616. $sameid = $form->{"$form->{vc}_id_$i"};
  617. }
  618. # record a AR/AP with a payment
  619. if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) {
  620. $form->{invnumber} = "";
  621. OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml, 1 );
  622. }
  623. my $rc = $dbh->commit;
  624. $rc;
  625. }
  626. 1;