summaryrefslogtreecommitdiff
path: root/LedgerSMB/CP.pm
blob: eea2454919e64fe9a93345aa4ddcd2152bb7d06c (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://sourceforge.net/projects/ledger-smb/
  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. sub new {
  38. my ($type, $countrycode) = @_;
  39. $self = {};
  40. if ($countrycode) {
  41. if (-f "locale/$countrycode/Num2text") {
  42. require "locale/$countrycode/Num2text";
  43. } else {
  44. use LedgerSMB::Num2text;
  45. }
  46. } else {
  47. use LedgerSMB::Num2text;
  48. }
  49. bless $self, $type;
  50. }
  51. sub paymentaccounts {
  52. my ($self, $myconfig, $form) = @_;
  53. # connect to database
  54. my $dbh = $form->dbconnect($myconfig);
  55. my $query = qq|SELECT accno, description, link
  56. FROM chart
  57. WHERE link LIKE '%$form->{ARAP}%'
  58. ORDER BY accno|;
  59. my $sth = $dbh->prepare($query);
  60. $sth->execute || $form->dberror($query);
  61. $form->{PR}{$form->{ARAP}} = ();
  62. $form->{PR}{"$form->{ARAP}_paid"} = ();
  63. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  64. foreach my $item (split /:/, $ref->{link}) {
  65. if ($item eq $form->{ARAP}) {
  66. push @{ $form->{PR}{$form->{ARAP}} }, $ref;
  67. }
  68. if ($item eq "$form->{ARAP}_paid") {
  69. push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
  70. }
  71. }
  72. }
  73. $sth->finish;
  74. # get currencies and closedto
  75. $query = qq|SELECT curr, closedto, current_date
  76. FROM defaults|;
  77. ($form->{currencies}, $form->{closedto}, $form->{datepaid}) = $dbh->selectrow_array($query);
  78. if ($form->{payment} eq 'payments') {
  79. # get language codes
  80. $query = qq|SELECT *
  81. FROM language
  82. ORDER BY 2|;
  83. $sth = $dbh->prepare($query);
  84. $sth->execute || $self->dberror($query);
  85. $form->{all_language} = ();
  86. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  87. push @{ $form->{all_language} }, $ref;
  88. }
  89. $sth->finish;
  90. $form->all_departments($myconfig, $dbh, $form->{vc});
  91. }
  92. $dbh->disconnect;
  93. }
  94. sub get_openvc {
  95. my ($self, $myconfig, $form) = @_;
  96. my $dbh = $form->dbconnect($myconfig);
  97. my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
  98. my $query = qq|SELECT count(*)
  99. FROM $form->{vc} ct, $arap a
  100. WHERE a.$form->{vc}_id = ct.id
  101. AND 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.$form->{vc}_id = ct.id
  107. AND a.amount != a.paid|;
  108. if ($form->{$form->{vc}}) {
  109. my $var = $form->like(lc $form->{$form->{vc}});
  110. $where .= " AND lower(name) LIKE '$var'";
  111. }
  112. # build selection list
  113. $query = qq|SELECT DISTINCT ct.*
  114. FROM $form->{vc} ct, $arap a
  115. $where
  116. ORDER BY name|;
  117. $sth = $dbh->prepare($query);
  118. $sth->execute || $form->dberror($query);
  119. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  120. $i++;
  121. push @{ $form->{name_list} }, $ref;
  122. }
  123. $sth->finish;
  124. $form->all_departments($myconfig, $dbh, $form->{vc});
  125. # get language codes
  126. $query = qq|SELECT *
  127. FROM language
  128. ORDER BY 2|;
  129. $sth = $dbh->prepare($query);
  130. $sth->execute || $self->dberror($query);
  131. $form->{all_language} = ();
  132. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  133. push @{ $form->{all_language} }, $ref;
  134. }
  135. $sth->finish;
  136. # get currency for first name
  137. if (@{ $form->{name_list} }) {
  138. $query = qq|SELECT curr
  139. FROM $form->{vc}
  140. WHERE id = $form->{name_list}->[0]->{id}|;
  141. ($form->{currency}) = $dbh->selectrow_array($query);
  142. $form->{currency} ||= $form->{defaultcurrency};
  143. }
  144. $dbh->disconnect;
  145. $i;
  146. }
  147. sub get_openinvoices {
  148. my ($self, $myconfig, $form) = @_;
  149. my $null;
  150. my $department_id;
  151. # connect to database
  152. my $dbh = $form->dbconnect($myconfig);
  153. my $where = qq|WHERE a.$form->{vc}_id = $form->{"$form->{vc}_id"}
  154. AND a.amount != a.paid|;
  155. $where .= qq| AND a.curr = '$form->{currency}'| if $form->{currency};
  156. my $sortorder = "transdate, invnumber";
  157. my ($buysell);
  158. if ($form->{vc} eq 'customer') {
  159. $buysell = "buy";
  160. } else {
  161. $buysell = "sell";
  162. }
  163. if ($form->{payment} eq 'payments') {
  164. $where = qq|WHERE a.amount != a.paid|;
  165. $where .= qq| AND a.curr = '$form->{currency}'| if $form->{currency};
  166. if ($form->{duedatefrom}) {
  167. $where .= qq| AND a.duedate >= '$form->{duedatefrom}'|;
  168. }
  169. if ($form->{duedateto}) {
  170. $where .= qq| AND a.duedate <= '$form->{duedateto}'|;
  171. }
  172. $sortorder = "name, transdate";
  173. }
  174. ($null, $department_id) = split /--/, $form->{department};
  175. if ($department_id) {
  176. $where .= qq| AND a.department_id = $department_id|;
  177. }
  178. my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid,
  179. a.curr, c.name, a.$form->{vc}_id, c.language_code
  180. FROM $form->{arap} a
  181. JOIN $form->{vc} c ON (c.id = a.$form->{vc}_id)
  182. $where
  183. ORDER BY $sortorder|;
  184. my $sth = $dbh->prepare($query);
  185. $sth->execute || $form->dberror($query);
  186. $query = qq|SELECT s.spoolfile
  187. FROM status s
  188. WHERE s.formname = '$form->{formname}'
  189. AND s.trans_id = ?|;
  190. my $vth = $dbh->prepare($query);
  191. my $spoolfile;
  192. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  193. # if this is a foreign currency transaction get exchangerate
  194. $ref->{exchangerate} = $form->get_exchangerate($dbh, $ref->{curr}, $ref->{transdate}, $buysell) if ($form->{currency} ne $form->{defaultcurrency});
  195. $vth->execute($ref->{id});
  196. $ref->{queue} = "";
  197. while (($spoolfile) = $vth->fetchrow_array) {
  198. $ref->{queued} .= "$form->{formname} $spoolfile ";
  199. }
  200. $vth->finish;
  201. $ref->{queued} =~ s/ +$//g;
  202. push @{ $form->{PR} }, $ref;
  203. }
  204. $sth->finish;
  205. $dbh->disconnect;
  206. }
  207. sub post_payment {
  208. my ($self, $myconfig, $form) = @_;
  209. # connect to database, turn AutoCommit off
  210. my $dbh = $form->dbconnect_noauto($myconfig);
  211. my $sth;
  212. my ($paymentaccno) = split /--/, $form->{account};
  213. # if currency ne defaultcurrency update exchangerate
  214. if ($form->{currency} ne $form->{defaultcurrency}) {
  215. $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});
  216. if ($form->{vc} eq 'customer') {
  217. $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
  218. } else {
  219. $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
  220. }
  221. } else {
  222. $form->{exchangerate} = 1;
  223. }
  224. my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
  225. FROM defaults|;
  226. my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
  227. my ($buysell);
  228. if ($form->{vc} eq 'customer') {
  229. $buysell = "buy";
  230. } else {
  231. $buysell = "sell";
  232. }
  233. my $ml;
  234. my $where;
  235. if ($form->{ARAP} eq 'AR') {
  236. $ml = 1;
  237. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  238. } else {
  239. $ml = -1;
  240. $where = qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  241. }
  242. my $paymentamount = $form->parse_amount($myconfig, $form->{amount});
  243. # query to retrieve paid amount
  244. $query = qq|SELECT paid
  245. FROM $form->{arap}
  246. WHERE id = ?
  247. FOR UPDATE|;
  248. my $pth = $dbh->prepare($query) || $form->dberror($query);
  249. my %audittrail;
  250. # go through line by line
  251. for my $i (1 .. $form->{rowcount}) {
  252. $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
  253. $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});
  254. if ($form->{"checked_$i"} && $form->{"paid_$i"}) {
  255. $paymentamount -= $form->{"paid_$i"};
  256. # get exchangerate for original
  257. $query = qq|SELECT $buysell
  258. FROM exchangerate e
  259. JOIN $form->{arap} a ON (a.transdate = e.transdate)
  260. WHERE e.curr = '$form->{currency}'
  261. AND a.id = $form->{"id_$i"}|;
  262. my ($exchangerate) = $dbh->selectrow_array($query);
  263. $exchangerate = 1 unless $exchangerate;
  264. $query = qq|SELECT c.id
  265. FROM chart c
  266. JOIN acc_trans a ON (a.chart_id = c.id)
  267. WHERE $where
  268. AND a.trans_id = $form->{"id_$i"}|;
  269. my ($id) = $dbh->selectrow_array($query);
  270. $amount = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
  271. # add AR/AP
  272. $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount)
  273. VALUES ($form->{"id_$i"}, $id, '$form->{datepaid}', $amount * $ml)|;
  274. $dbh->do($query) || $form->dberror($query);
  275. # add payment
  276. $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
  277. amount, source, memo)
  278. VALUES ($form->{"id_$i"}, (SELECT id
  279. FROM chart
  280. WHERE accno = '$paymentaccno'),
  281. '$form->{datepaid}', $form->{"paid_$i"} * $ml * -1, |
  282. .$dbh->quote($form->{source}).qq|, |
  283. .$dbh->quote($form->{memo}).qq|)|;
  284. $dbh->do($query) || $form->dberror($query);
  285. # add exchangerate difference if currency ne defaultcurrency
  286. $amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - 1), 2);
  287. if ($amount) {
  288. # exchangerate difference
  289. $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
  290. amount, cleared, fx_transaction, source)
  291. VALUES ($form->{"id_$i"}, (SELECT id
  292. FROM chart
  293. WHERE accno = '$paymentaccno'),
  294. '$form->{datepaid}', $amount * $ml * -1, '0', '1', |
  295. .$dbh->quote($form->{source}).qq|)|;
  296. $dbh->do($query) || $form->dberror($query);
  297. # gain/loss
  298. $amount = ($form->round_amount($form->{"paid_$i"} * $exchangerate,2) - $form->round_amount($form->{"paid_$i"} * $form->{exchangerate},2)) * $ml * -1;
  299. if ($amount) {
  300. my $accno_id = ($amount > 0) ? $fxgain_accno_id : $fxloss_accno_id;
  301. $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
  302. amount, cleared, fx_transaction)
  303. VALUES ($form->{"id_$i"}, $accno_id,
  304. '$form->{datepaid}', $amount, '0', '1')|;
  305. $dbh->do($query) || $form->dberror($query);
  306. }
  307. }
  308. $form->{"paid_$i"} = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);
  309. $pth->execute($form->{"id_$i"}) || $form->dberror;
  310. ($amount) = $pth->fetchrow_array;
  311. $pth->finish;
  312. $amount += $form->{"paid_$i"};
  313. # update AR/AP transaction
  314. $query = qq|UPDATE $form->{arap}
  315. SET paid = $amount,
  316. datepaid = '$form->{datepaid}'
  317. WHERE id = $form->{"id_$i"}|;
  318. $dbh->do($query) || $form->dberror($query);
  319. %audittrail = ( tablename => $form->{arap},
  320. reference => $form->{source},
  321. formname => $form->{formname},
  322. action => 'posted',
  323. id => $form->{"id_$i"} );
  324. $form->audittrail($dbh, "", \%audittrail);
  325. }
  326. }
  327. # record a AR/AP with a payment
  328. if ($form->round_amount($paymentamount, 2)) {
  329. $form->{invnumber} = "";
  330. OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml, 1);
  331. }
  332. my $rc = $dbh->commit;
  333. $dbh->disconnect;
  334. $rc;
  335. }
  336. sub post_payments {
  337. my ($self, $myconfig, $form) = @_;
  338. # connect to database, turn AutoCommit off
  339. my $dbh = $form->dbconnect_noauto($myconfig);
  340. my $sth;
  341. my ($paymentaccno) = split /--/, $form->{account};
  342. # if currency ne defaultcurrency update exchangerate
  343. if ($form->{currency} ne $form->{defaultcurrency}) {
  344. $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});
  345. if ($form->{vc} eq 'customer') {
  346. $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
  347. } else {
  348. $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
  349. }
  350. } else {
  351. $form->{exchangerate} = 1;
  352. }
  353. my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
  354. FROM defaults|;
  355. my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
  356. my ($buysell);
  357. if ($form->{vc} eq 'customer') {
  358. $buysell = "buy";
  359. } else {
  360. $buysell = "sell";
  361. }
  362. my $ml;
  363. my $where;
  364. if ($form->{ARAP} eq 'AR') {
  365. $ml = 1;
  366. $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;
  367. } else {
  368. $ml = -1;
  369. $where = qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;
  370. }
  371. # get AR/AP account
  372. $query = qq|SELECT c.accno
  373. FROM chart c
  374. JOIN acc_trans ac ON (ac.chart_id = c.id)
  375. WHERE trans_id = ?
  376. AND $where|;
  377. my $ath = $dbh->prepare($query) || $form->dberror($query);
  378. # query to retrieve paid amount
  379. $query = qq|SELECT paid
  380. FROM $form->{arap}
  381. WHERE id = ?
  382. FOR UPDATE|;
  383. my $pth = $dbh->prepare($query) || $form->dberror($query);
  384. my %audittrail;
  385. my $overpayment = 0;
  386. my $accno_id;
  387. # go through line by line
  388. for my $i (1 .. $form->{rowcount}) {
  389. $ath->execute($form->{"id_$i"});
  390. ($form->{$form->{ARAP}}) = $ath->fetchrow_array;
  391. $ath->finish;
  392. $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
  393. $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});
  394. if ($form->{"$form->{vc}_id_$i"} ne $sameid) {
  395. # record a AR/AP with a payment
  396. if ($overpayment > 0 && $form->{$form->{ARAP}}) {
  397. $form->{invnumber} = "";
  398. OP::overpayment("", $myconfig, $form, $dbh, $overpayment, $ml, 1);
  399. }
  400. $overpayment = 0;
  401. $form->{"$form->{vc}_id"} = $form->{"$form->{vc}_id_$i"};
  402. for (qw(source memo)) { $form->{$_} = $form->{"${_}_$i"} }
  403. }
  404. if ($form->{"checked_$i"} && $form->{"paid_$i"}) {
  405. $overpayment += ($form->{"paid_$i"} - $form->{"due_$i"});
  406. # get exchangerate for original
  407. $query = qq|SELECT $buysell
  408. FROM exchangerate e
  409. JOIN $form->{arap} a ON (a.transdate = e.transdate)
  410. WHERE e.curr = '$form->{currency}'
  411. AND a.id = $form->{"id_$i"}|;
  412. my ($exchangerate) = $dbh->selectrow_array($query);
  413. $exchangerate ||= 1;
  414. $query = qq|SELECT c.id
  415. FROM chart c
  416. JOIN acc_trans a ON (a.chart_id = c.id)
  417. WHERE $where
  418. AND a.trans_id = $form->{"id_$i"}|;
  419. my ($id) = $dbh->selectrow_array($query);
  420. $paid = ($form->{"paid_$i"} > $form->{"due_$i"}) ? $form->{"due_$i"} : $form->{"paid_$i"};
  421. $amount = $form->round_amount($paid * $exchangerate, 2);
  422. # add AR/AP
  423. $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount)
  424. VALUES ($form->{"id_$i"}, $id, '$form->{datepaid}',
  425. $amount * $ml)|;
  426. $dbh->do($query) || $form->dberror($query);
  427. $query = qq|SELECT id
  428. FROM chart
  429. WHERE accno = '$paymentaccno'|;
  430. ($accno_id) = $dbh->selectrow_array($query);
  431. # add payment
  432. $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
  433. amount, source, memo)
  434. VALUES ($form->{"id_$i"}, $accno_id, '$form->{datepaid}',
  435. $paid * $ml * -1, |
  436. .$dbh->quote($form->{source}).qq|, |
  437. .$dbh->quote($form->{memo}).qq|)|;
  438. $dbh->do($query) || $form->dberror($query);
  439. # add exchangerate difference if currency ne defaultcurrency
  440. $amount = $form->round_amount($paid * ($form->{exchangerate} - 1) * $ml * -1, 2);
  441. if ($amount) {
  442. # exchangerate difference
  443. $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
  444. amount, source)
  445. VALUES ($form->{"id_$i"}, $accno_id, '$form->{datepaid}',
  446. $amount, |
  447. .$dbh->quote($form->{source}).qq|)|;
  448. $dbh->do($query) || $form->dberror($query);
  449. # gain/loss
  450. $amount = ($form->round_amount($paid * $exchangerate,2) - $form->round_amount($paid * $form->{exchangerate},2)) * $ml * -1;
  451. if ($amount) {
  452. $accno_id = ($amount > 0) ? $fxgain_accno_id : $fxloss_accno_id;
  453. $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
  454. amount, fx_transaction)
  455. VALUES ($form->{"id_$i"}, $accno_id,
  456. '$form->{datepaid}', $amount, '1')|;
  457. $dbh->do($query) || $form->dberror($query);
  458. }
  459. }
  460. $paid = $form->round_amount($paid * $exchangerate, 2);
  461. $pth->execute($form->{"id_$i"}) || $form->dberror;
  462. ($amount) = $pth->fetchrow_array;
  463. $pth->finish;
  464. $amount += $paid;
  465. # update AR/AP transaction
  466. $query = qq|UPDATE $form->{arap}
  467. SET paid = $amount,
  468. datepaid = '$form->{datepaid}'
  469. WHERE id = $form->{"id_$i"}|;
  470. $dbh->do($query) || $form->dberror($query);
  471. %audittrail = ( tablename => $form->{arap},
  472. reference => $form->{source},
  473. formname => $form->{formname},
  474. action => 'posted',
  475. id => $form->{"id_$i"} );
  476. $form->audittrail($dbh, "", \%audittrail);
  477. }
  478. $sameid = $form->{"$form->{vc}_id_$i"};
  479. }
  480. # record a AR/AP with a payment
  481. if ($overpayment > 0 && $form->{$form->{ARAP}}) {
  482. $form->{invnumber} = "";
  483. OP::overpayment("", $myconfig, $form, $dbh, $overpayment, $ml, 1);
  484. }
  485. my $rc = $dbh->commit;
  486. $dbh->disconnect;
  487. $rc;
  488. }
  489. 1;