summaryrefslogtreecommitdiff
path: root/LedgerSMB/CP.pm
blob: abcf2fdda732fc144dbd82977fcf678b82cb7fc9 (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. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  200. # if this is a foreign currency transaction get exchangerate
  201. $ref->{exchangerate} =
  202. $form->get_exchangerate( $dbh, $ref->{curr}, $ref->{transdate},
  203. $buysell )
  204. if ( $form->{currency} ne $form->{defaultcurrency} );
  205. $vth->execute( $ref->{id} );
  206. $ref->{queue} = "";
  207. while ( ($spoolfile) = $vth->fetchrow_array ) {
  208. $ref->{queued} .= "$form->{formname} $spoolfile ";
  209. }
  210. $vth->finish;
  211. $ref->{queued} =~ s/ +$//g;
  212. push @{ $form->{PR} }, $ref;
  213. }
  214. $sth->finish;
  215. $dbh->commit;
  216. }
  217. sub post_payment {
  218. my ( $self, $myconfig, $form ) = @_;
  219. # connect to database, turn AutoCommit off
  220. my $dbh = $form->{dbh};
  221. my $sth;
  222. my ($paymentaccno) = split /--/, $form->{account};
  223. # if currency ne defaultcurrency update exchangerate
  224. if ( $form->{currency} ne $form->{defaultcurrency} ) {
  225. $form->{exchangerate} =
  226. $form->parse_amount( $myconfig, $form->{exchangerate} );
  227. if ( $form->{vc} eq 'customer' ) {
  228. $form->update_exchangerate( $dbh, $form->{currency},
  229. $form->{datepaid}, $form->{exchangerate}, 0 );
  230. }
  231. else {
  232. $form->update_exchangerate( $dbh, $form->{currency},
  233. $form->{datepaid}, 0, $form->{exchangerate} );
  234. }
  235. }
  236. else {
  237. $form->{exchangerate} = 1;
  238. }
  239. my $query = qq|
  240. SELECT (SELECT value FROM defaults
  241. WHERE setting_key='fxgain_accno_id'),
  242. (SELECT value FROM defaults
  243. WHERE setting_key='fxloss_accno_id')|;
  244. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  245. my ($buysell);
  246. if ( $form->{vc} eq 'customer' ) {
  247. $buysell = "buy";
  248. }
  249. else {
  250. $buysell = "sell";
  251. }
  252. my $ml;
  253. my $where;
  254. if ( $form->{ARAP} eq 'AR' ) {
  255. $ml = 1;
  256. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  257. }
  258. else {
  259. $ml = -1;
  260. $where =
  261. qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  262. }
  263. my $paymentamount = $form->parse_amount( $myconfig, $form->{amount} );
  264. # query to retrieve paid amount
  265. $query = qq|SELECT paid
  266. FROM $form->{arap}
  267. WHERE id = ?
  268. FOR UPDATE|;
  269. my $pth = $dbh->prepare($query) || $form->dberror($query);
  270. my %audittrail;
  271. # go through line by line
  272. for my $i ( 1 .. $form->{rowcount} ) {
  273. $form->{"paid_$i"} =
  274. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  275. $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} );
  276. if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) {
  277. $paymentamount -= $form->{"paid_$i"};
  278. # get exchangerate for original
  279. $query = qq|
  280. SELECT $buysell
  281. FROM exchangerate e
  282. JOIN $form->{arap} a
  283. ON (a.transdate = e.transdate)
  284. WHERE e.curr = ?
  285. AND a.id = ?|;
  286. my $sth = $dbh->prepare($query);
  287. $sth->execute( $form->{currency}, $form->{"id_$i"} );
  288. my @exchange = $sth->fetchrow_array();
  289. $form->db_parse_numeric(sth=>$sth, arrayref=>\@exchange);
  290. my $exchangerate = shift @exchange;
  291. $exchangerate = 1 unless $exchangerate;
  292. $query = qq|
  293. SELECT c.id
  294. FROM chart c
  295. JOIN acc_trans a ON (a.chart_id = c.id)
  296. WHERE $where
  297. AND a.trans_id = ?|;
  298. my $sth = $dbh->prepare($query);
  299. $sth->execute( $form->{"id_$i"} );
  300. my ($id) = $sth->fetchrow_array;
  301. $amount =
  302. $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 );
  303. # add AR/AP
  304. $query = qq|
  305. INSERT INTO acc_trans
  306. (trans_id, chart_id, transdate,
  307. amount)
  308. VALUES (?, ?,
  309. ?,
  310. ?)|;
  311. $sth = $dbh->prepare($query);
  312. $sth->execute( $form->{"id_$i"}, $id, $form->{datepaid},
  313. $amount * $ml )
  314. || $form->dberror( $query, __file__, __line__ );
  315. # add payment
  316. $query = qq|
  317. INSERT INTO acc_trans
  318. (trans_id, chart_id, transdate,
  319. amount, source, memo)
  320. VALUES (?, (SELECT id
  321. FROM chart
  322. WHERE accno = ?),
  323. ?, ?, ?, ?)|;
  324. $sth = $dbh->prepare($query);
  325. $sth->execute( $form->{"id_$i"}, $paymentaccno, $form->{datepaid},
  326. $form->{"paid_$i"} * $ml * -1,
  327. $form->{source}, $form->{memo} )
  328. || $form->dberror( $query, 'CP.pm', 444 );
  329. # add exchangerate difference if currency ne defaultcurrency
  330. $amount =
  331. $form->round_amount(
  332. $form->{"paid_$i"} * ( $form->{exchangerate} - 1 ), 2 );
  333. if ($amount) {
  334. # exchangerate difference
  335. $query = qq|
  336. INSERT INTO acc_trans
  337. (trans_id, chart_id,
  338. transdate, amount, cleared,
  339. fx_transaction, source)
  340. VALUES (?, (SELECT id
  341. FROM chart
  342. WHERE accno = ?),
  343. ?, ?, '0', '1',
  344. ?)|;
  345. $sth = $dbh->prepare($query);
  346. $sth->execute(
  347. $form->{"id_$i"}, $paymentaccno, $form->{datepaid},
  348. $amount * $ml * -1, $form->{source}
  349. ) || $form->dberror( $query, 'CP.pm', 470 );
  350. # gain/loss
  351. $amount = (
  352. $form->round_amount(
  353. $form->{"paid_$i"} * $exchangerate, 2
  354. ) - $form->round_amount(
  355. $form->{"paid_$i"} * $form->{exchangerate}, 2
  356. )
  357. ) * $ml * -1;
  358. if ($amount) {
  359. my $accno_id =
  360. ( $amount > 0 )
  361. ? $fxgain_accno_id
  362. : $fxloss_accno_id;
  363. $query = qq|
  364. INSERT INTO acc_trans
  365. (trans_id,
  366. chart_id,
  367. transdate,
  368. amount, cleared,
  369. fx_transaction)
  370. VALUES (?, ?, ?, ?, '0', '1')|;
  371. $sth = $dbh->prepare($query);
  372. $sth->execute(
  373. $form->{"id_$i"}, $accno_id,
  374. $form->{datepaid}, $amount
  375. ) || $form->dberror( $query, 'CP.pm', 506 );
  376. }
  377. }
  378. $form->{"paid_$i"} =
  379. $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 );
  380. $pth->execute( $form->{"id_$i"} ) || $form->dberror($pth->statement);
  381. ($amount) = $pth->fetchrow_array;
  382. $pth->finish;
  383. $amount += $form->{"paid_$i"};
  384. # update AR/AP transaction
  385. $query = qq|
  386. UPDATE $form->{arap}
  387. SET paid = ?,
  388. datepaid = ?
  389. WHERE id = ?|;
  390. $sth = $dbh->prepare($query);
  391. $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} )
  392. || $form->dberror( $query, 'CP.pm', 530 );
  393. if ($amount->is_nan) {
  394. $dbh->rollback;
  395. return;
  396. }
  397. %audittrail = (
  398. tablename => $form->{arap},
  399. reference => $form->{source},
  400. formname => $form->{formname},
  401. action => 'posted',
  402. id => $form->{"id_$i"}
  403. );
  404. $form->audittrail( $dbh, "", \%audittrail );
  405. }
  406. }
  407. # record a AR/AP with a payment
  408. if ( $form->round_amount( $paymentamount, 2 ) ) {
  409. $form->{invnumber} = "";
  410. OP::overpayment( "", $myconfig, $form, $dbh, $paymentamount, $ml, 1 );
  411. }
  412. my $rc = $dbh->commit;
  413. $rc;
  414. }
  415. sub post_payments {
  416. my ( $self, $myconfig, $form ) = @_;
  417. # connect to database, turn AutoCommit off
  418. my $dbh = $form->{dbh};
  419. my $sth;
  420. my ($paymentaccno) = split /--/, $form->{account};
  421. # if currency ne defaultcurrency update exchangerate
  422. if ( $form->{currency} ne $form->{defaultcurrency} ) {
  423. $form->{exchangerate} =
  424. $form->parse_amount( $myconfig, $form->{exchangerate} );
  425. if ( $form->{vc} eq 'customer' ) {
  426. $form->update_exchangerate( $dbh, $form->{currency},
  427. $form->{datepaid}, $form->{exchangerate}, 0 );
  428. }
  429. else {
  430. $form->update_exchangerate( $dbh, $form->{currency},
  431. $form->{datepaid}, 0, $form->{exchangerate} );
  432. }
  433. }
  434. else {
  435. $form->{exchangerate} = 1;
  436. }
  437. my $query = qq|
  438. SELECT (SELECT value FROM defaults
  439. WHERE setting_key='fxgain_accno_id'),
  440. (SELECT value FROM defaults
  441. WHERE setting_key='fxloss_accno_id')|;
  442. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  443. my ($buysell);
  444. if ( $form->{vc} eq 'customer' ) {
  445. $buysell = "buy";
  446. }
  447. else {
  448. $buysell = "sell";
  449. }
  450. my $ml;
  451. my $where;
  452. if ( $form->{ARAP} eq 'AR' ) {
  453. $ml = 1;
  454. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  455. }
  456. else {
  457. $ml = -1;
  458. $where =
  459. qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  460. }
  461. # get AR/AP account
  462. $query = qq|SELECT c.accno
  463. FROM chart c
  464. JOIN acc_trans ac ON (ac.chart_id = c.id)
  465. WHERE trans_id = ?
  466. AND $where|;
  467. my $ath = $dbh->prepare($query) || $form->dberror($query);
  468. # query to retrieve paid amount
  469. $query = qq|SELECT paid
  470. FROM $form->{arap}
  471. WHERE id = ?
  472. FOR UPDATE|;
  473. my $pth = $dbh->prepare($query) || $form->dberror($query);
  474. my %audittrail;
  475. my $overpayment = 0;
  476. my $accno_id;
  477. # go through line by line
  478. for my $i ( 1 .. $form->{rowcount} ) {
  479. $ath->execute( $form->{"id_$i"} );
  480. ( $form->{ $form->{ARAP} } ) = $ath->fetchrow_array;
  481. $ath->finish;
  482. $form->{"paid_$i"} =
  483. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  484. $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} );
  485. if ( $form->{"$form->{vc}_id_$i"} ne $sameid ) {
  486. # record a AR/AP with a payment
  487. if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) {
  488. $form->{invnumber} = "";
  489. OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml,
  490. 1 );
  491. }
  492. $overpayment = 0;
  493. $form->{"$form->{vc}_id"} = $form->{"$form->{vc}_id_$i"};
  494. for (qw(source memo)) { $form->{$_} = $form->{"${_}_$i"} }
  495. }
  496. if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) {
  497. $overpayment += ( $form->{"paid_$i"} - $form->{"due_$i"} );
  498. # get exchangerate for original
  499. $query = qq|
  500. SELECT $buysell
  501. FROM exchangerate e
  502. JOIN $form->{arap} a
  503. ON (a.transdate = e.transdate)
  504. WHERE e.curr = ?
  505. AND a.id = ?|;
  506. $sth = $dbh->prepare($query);
  507. $sth->execute( $form->{currency}, $form->{"id_$i"} )
  508. || $form->dberror( $query, 'CP.pm', 671 );
  509. my ($exchangerate) = $sth->fetchrow_array;
  510. $exchangerate ||= 1;
  511. $query = qq|
  512. SELECT c.id
  513. FROM chart c
  514. JOIN acc_trans a ON (a.chart_id = c.id)
  515. WHERE $where
  516. AND a.trans_id = ?|;
  517. $sth = $dbh->prepare($query);
  518. $sth->execute( $form->{"id_$i"} );
  519. ($id) = $sth->fetchrow_array();
  520. $paid =
  521. ( $form->{"paid_$i"} > $form->{"due_$i"} )
  522. ? $form->{"due_$i"}
  523. : $form->{"paid_$i"};
  524. $amount = $form->round_amount( $paid * $exchangerate, 2 );
  525. # add AR/AP
  526. $query = qq|
  527. INSERT INTO acc_trans
  528. (trans_id, chart_id, transdate,
  529. amount)
  530. VALUES (?, ?, ?, ?)|;
  531. $sth = $dbh->prepare($query);
  532. $sth->execute( $form->{"id_$i"}, $id, $form->{datepaid},
  533. $amount * $ml )
  534. || $form->dberror( $query, 'CP.pm', 701 );
  535. $query = qq|SELECT id
  536. FROM chart
  537. WHERE accno = ?|;
  538. $sth = $dbh->prepare($query);
  539. $sth->execute($paymentaccno);
  540. ($accno_id) = $sth->fetchrow_array;
  541. # add payment
  542. $query = qq|
  543. INSERT INTO acc_trans
  544. (trans_id, chart_id, transdate,
  545. amount, source, memo)
  546. VALUES (?, ?, ?, ?, ?, ?)|;
  547. $sth = $dbh->prepare($query);
  548. $sth->execute(
  549. $form->{"id_$i"}, $accno_id, $form->{datepaid},
  550. $paid * $ml * -1, $form->{source}, $form->{memo}
  551. ) || $form->dberror( $query, 'CP.pm', 723 );
  552. # add exchangerate difference if currency ne defaultcurrency
  553. $amount =
  554. $form->round_amount(
  555. $paid * ( $form->{exchangerate} - 1 ) * $ml * -1, 2 );
  556. if ($amount) {
  557. # exchangerate difference
  558. $query = qq|
  559. INSERT INTO acc_trans
  560. (trans_id, chart_id,
  561. transdate,
  562. amount, source)
  563. VALUES (?, ?, ?, ?, ?)|;
  564. $sth = $dbh->prepare($query);
  565. $sth->execute(
  566. $form->{"id_$i"}, $accno_id, $form->{datepaid},
  567. $amount, $form->{source}
  568. ) || $form->dberror( $query, 'CP.pm', 748 );
  569. # gain/loss
  570. $amount =
  571. ( $form->round_amount( $paid * $exchangerate, 2 ) -
  572. $form->round_amount( $paid * $form->{exchangerate}, 2 ) )
  573. * $ml * -1;
  574. if ($amount) {
  575. $accno_id =
  576. ( $amount > 0 )
  577. ? $fxgain_accno_id
  578. : $fxloss_accno_id;
  579. $query = qq|
  580. INSERT INTO acc_trans
  581. (trans_id,
  582. chart_id,
  583. transdate,
  584. amount,
  585. fx_transaction)
  586. VALUES (?, ?, ?, ?, '1')|;
  587. $sth = $dbh->prepare($query);
  588. $sth->execute(
  589. $form->{"id_$i"}, $accno_id,
  590. $form->{datepaid}, $amount
  591. ) || $form->dberror( $query, 'CP.pm', 775 );
  592. }
  593. }
  594. $paid = $form->round_amount( $paid * $exchangerate, 2 );
  595. $pth->execute( $form->{"id_$i"} ) || $form->dberror($pth->statement);
  596. ($amount) = $pth->fetchrow_array;
  597. $pth->finish;
  598. $amount += $paid;
  599. # update AR/AP transaction
  600. $query = qq|
  601. UPDATE $form->{arap}
  602. SET paid = ?,
  603. datepaid = ?
  604. WHERE id = ?|;
  605. $sth = $dbh->prepare($query);
  606. $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} )
  607. || $form->dberror( $query, 'CP.pm', 796 );
  608. if ($amount->is_nan) {
  609. $dbh->rollback;
  610. return;
  611. }
  612. %audittrail = (
  613. tablename => $form->{arap},
  614. reference => $form->{source},
  615. formname => $form->{formname},
  616. action => 'posted',
  617. id => $form->{"id_$i"}
  618. );
  619. $form->audittrail( $dbh, "", \%audittrail );
  620. }
  621. $sameid = $form->{"$form->{vc}_id_$i"};
  622. }
  623. # record a AR/AP with a payment
  624. if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) {
  625. $form->{invnumber} = "";
  626. OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml, 1 );
  627. }
  628. my $rc = $dbh->commit;
  629. $rc;
  630. }
  631. 1;