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