summaryrefslogtreecommitdiff
path: root/LedgerSMB/CP.pm
blob: 6d31c5aacea0c67e48e784bc90bd73ef86e14e70 (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 entity_id = $form->{name_list}->[0]->{entity_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, e.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. JOIN entity e ON (a.entity_id = e.id)
  195. $where
  196. ORDER BY $sortorder|;
  197. my $sth = $dbh->prepare($query);
  198. $sth->execute || $form->dberror($query);
  199. $query = qq|SELECT s.spoolfile
  200. FROM status s
  201. WHERE s.formname = '$form->{formname}'
  202. AND s.trans_id = ?|;
  203. my $vth = $dbh->prepare($query);
  204. my $spoolfile;
  205. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  206. # if this is a foreign currency transaction get exchangerate
  207. $ref->{exchangerate} =
  208. $form->get_exchangerate( $dbh, $ref->{curr}, $ref->{transdate},
  209. $buysell )
  210. if ( $form->{currency} ne $form->{defaultcurrency} );
  211. $vth->execute( $ref->{id} );
  212. $ref->{queue} = "";
  213. while ( ($spoolfile) = $vth->fetchrow_array ) {
  214. $ref->{queued} .= "$form->{formname} $spoolfile ";
  215. }
  216. $vth->finish;
  217. $ref->{queued} =~ s/ +$//g;
  218. push @{ $form->{PR} }, $ref;
  219. }
  220. $sth->finish;
  221. $dbh->commit;
  222. }
  223. sub post_payment {
  224. my ( $self, $myconfig, $form ) = @_;
  225. # connect to database, turn AutoCommit off
  226. my $dbh = $form->{dbh};
  227. my $sth;
  228. my ($paymentaccno) = split /--/, $form->{account};
  229. # if currency ne defaultcurrency update exchangerate
  230. if ( $form->{currency} ne $form->{defaultcurrency} ) {
  231. $form->{exchangerate} =
  232. $form->parse_amount( $myconfig, $form->{exchangerate} );
  233. if ( $form->{vc} eq 'customer' ) {
  234. $form->update_exchangerate( $dbh, $form->{currency},
  235. $form->{datepaid}, $form->{exchangerate}, 0 );
  236. }
  237. else {
  238. $form->update_exchangerate( $dbh, $form->{currency},
  239. $form->{datepaid}, 0, $form->{exchangerate} );
  240. }
  241. }
  242. else {
  243. $form->{exchangerate} = 1;
  244. }
  245. my $query = qq|
  246. SELECT (SELECT value FROM defaults
  247. WHERE setting_key='fxgain_accno_id'),
  248. (SELECT value FROM defaults
  249. WHERE setting_key='fxloss_accno_id')|;
  250. my ( $fxgain_accno_id, $fxloss_accno_id ) = $dbh->selectrow_array($query);
  251. my ($buysell);
  252. if ( $form->{vc} eq 'customer' ) {
  253. $buysell = "buy";
  254. }
  255. else {
  256. $buysell = "sell";
  257. }
  258. my $ml;
  259. my $where;
  260. if ( $form->{ARAP} eq 'AR' ) {
  261. $ml = 1;
  262. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  263. }
  264. else {
  265. $ml = -1;
  266. $where =
  267. qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  268. }
  269. my $paymentamount = $form->parse_amount( $myconfig, $form->{amount} );
  270. # query to retrieve paid amount
  271. $query = qq|SELECT paid
  272. FROM $form->{arap}
  273. WHERE id = ?
  274. FOR UPDATE|;
  275. my $pth = $dbh->prepare($query) || $form->dberror($query);
  276. my %audittrail;
  277. # go through line by line
  278. for my $i ( 1 .. $form->{rowcount} ) {
  279. $form->{"paid_$i"} =
  280. $form->parse_amount( $myconfig, $form->{"paid_$i"} );
  281. $form->{"due_$i"} = $form->parse_amount( $myconfig, $form->{"due_$i"} );
  282. if ( $form->{"checked_$i"} && $form->{"paid_$i"} ) {
  283. $paymentamount -= $form->{"paid_$i"};
  284. # get exchangerate for original
  285. $query = qq|
  286. SELECT $buysell
  287. FROM exchangerate e
  288. JOIN $form->{arap} a
  289. ON (a.transdate = e.transdate)
  290. WHERE e.curr = ?
  291. AND a.id = ?|;
  292. my $sth = $dbh->prepare($query);
  293. $sth->execute( $form->{currency}, $form->{"id_$i"} );
  294. my ($exchangerate) = $sth->fetchrow_array();
  295. $exchangerate = 1 unless $exchangerate;
  296. $query = qq|
  297. SELECT c.id
  298. FROM chart c
  299. JOIN acc_trans a ON (a.chart_id = c.id)
  300. WHERE $where
  301. AND a.trans_id = ?|;
  302. my $sth = $dbh->prepare($query);
  303. $sth->execute( $form->{"id_$i"} );
  304. my ($id) = $sth->fetchrow_array;
  305. $amount =
  306. $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 );
  307. # add AR/AP
  308. $query = qq|
  309. INSERT INTO acc_trans
  310. (trans_id, chart_id, transdate,
  311. amount)
  312. VALUES (?, ?,
  313. ?,
  314. ?)|;
  315. $sth = $dbh->prepare($query);
  316. $sth->execute( $form->{"id_$i"}, $id, $form->{date_paid},
  317. $amount * $ml )
  318. || $form->dberror( $query, __file__, __line__ );
  319. # add payment
  320. $query = qq|
  321. INSERT INTO acc_trans
  322. (trans_id, chart_id, transdate,
  323. amount, source, memo)
  324. VALUES (?, (SELECT id
  325. FROM chart
  326. WHERE accno = ?),
  327. ?, ?, ?, ?)|;
  328. $sth = $dbh->prepare($query);
  329. $sth->execute( $form->{"id_$i"}, $paymentaccno, $form->{datepaid},
  330. $form->{"paid_$i"} * $ml * -1,
  331. $form->{source}, $form->{memo} )
  332. || $form->dberror( $query, 'CP.pm', 444 );
  333. # add exchangerate difference if currency ne defaultcurrency
  334. $amount =
  335. $form->round_amount(
  336. $form->{"paid_$i"} * ( $form->{exchangerate} - 1 ), 2 );
  337. if ($amount) {
  338. # exchangerate difference
  339. $query = qq|
  340. INSERT INTO acc_trans
  341. (trans_id, chart_id,
  342. transdate, amount, cleared,
  343. fx_transaction, source)
  344. VALUES (?, (SELECT id
  345. FROM chart
  346. WHERE accno = ?),
  347. ?, ?, '0', '1',
  348. ?)|;
  349. $sth = $dbh->prepare($query);
  350. $sth->execute(
  351. $form->{"id_$i"}, $paymentaccno, $form->{datepaid},
  352. $amount * $ml * -1, $form->{source}
  353. ) || $form->dberror( $query, 'CP.pm', 470 );
  354. # gain/loss
  355. $amount = (
  356. $form->round_amount(
  357. $form->{"paid_$i"} * $exchangerate, 2
  358. ) - $form->round_amount(
  359. $form->{"paid_$i"} * $form->{exchangerate}, 2
  360. )
  361. ) * $ml * -1;
  362. if ($amount) {
  363. my $accno_id =
  364. ( $amount > 0 )
  365. ? $fxgain_accno_id
  366. : $fxloss_accno_id;
  367. $query = qq|
  368. INSERT INTO acc_trans
  369. (trans_id,
  370. chart_id,
  371. transdate,
  372. amount, cleared,
  373. fx_transaction)
  374. VALUES (?, ?, ?, ?, '0', '1')|;
  375. $sth = $dbh->prepare($query);
  376. $sth->execute(
  377. $form->{"id_$i"}, $accno_id,
  378. $form->{datepaid}, $amount
  379. ) || $form->dberror( $query, 'CP.pm', 506 );
  380. }
  381. }
  382. $form->{"paid_$i"} =
  383. $form->round_amount( $form->{"paid_$i"} * $exchangerate, 2 );
  384. $pth->execute( $form->{"id_$i"} ) || $form->dberror($pth->statement);
  385. ($amount) = $pth->fetchrow_array;
  386. $pth->finish;
  387. $amount += $form->{"paid_$i"};
  388. # update AR/AP transaction
  389. $query = qq|
  390. UPDATE $form->{arap}
  391. SET paid = ?,
  392. datepaid = ?
  393. WHERE id = ?|;
  394. $sth = $dbh->prepare($query);
  395. $sth->execute( $amount, $form->{datepaid}, $form->{"id_$i"} )
  396. || $form->dberror( $query, 'CP.pm', 530 );
  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. %audittrail = (
  609. tablename => $form->{arap},
  610. reference => $form->{source},
  611. formname => $form->{formname},
  612. action => 'posted',
  613. id => $form->{"id_$i"}
  614. );
  615. $form->audittrail( $dbh, "", \%audittrail );
  616. }
  617. $sameid = $form->{"$form->{vc}_id_$i"};
  618. }
  619. # record a AR/AP with a payment
  620. if ( $overpayment > 0 && $form->{ $form->{ARAP} } ) {
  621. $form->{invnumber} = "";
  622. OP::overpayment( "", $myconfig, $form, $dbh, $overpayment, $ml, 1 );
  623. }
  624. my $rc = $dbh->commit;
  625. $rc;
  626. }
  627. 1;