summaryrefslogtreecommitdiff
path: root/LedgerSMB/AA.pm
blob: dc6f627948485806ffa3729e2547d2b7b6fd521b (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://www.ledgersmb.org/
  5. # Copyright (C) 2006
  6. # This work contains copyrighted information from a number of sources all used
  7. # with permission.
  8. #
  9. # This file contains source code included with or based on SQL-Ledger which
  10. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  11. # under the GNU General Public License version 2 or, at your option, any later
  12. # version. For a full list including contact information of contributors,
  13. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  14. #
  15. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  16. # Copyright (C) 2006
  17. #
  18. # Author: DWS Systems Inc.
  19. # Web: http://www.sql-ledger.org
  20. #
  21. # Contributors:
  22. #
  23. #
  24. # See COPYRIGHT file for copyright information
  25. #======================================================================
  26. #
  27. # This file has undergone whitespace cleanup.
  28. #
  29. #======================================================================
  30. #
  31. # AR/AP backend routines
  32. # common routines
  33. #
  34. #======================================================================
  35. package AA;
  36. use LedgerSMB::Sysconfig;
  37. sub post_transaction {
  38. my ($self, $myconfig, $form) = @_;
  39. # connect to database
  40. my $dbh = $form->{dbh};
  41. my $query;
  42. my $sth;
  43. my $null;
  44. ($null, $form->{department_id}) = split(/--/, $form->{department});
  45. $form->{department_id} *= 1;
  46. my $ml = 1;
  47. my $table = 'ar';
  48. my $buysell = 'buy';
  49. my $ARAP = 'AR';
  50. my $invnumber = "sinumber";
  51. my $keepcleared;
  52. if ($form->{vc} eq 'vendor') {
  53. $table = 'ap';
  54. $buysell = 'sell';
  55. $ARAP = 'AP';
  56. $ml = -1;
  57. $invnumber = "vinumber";
  58. }
  59. if ($form->{currency} eq $form->{defaultcurrency}) {
  60. $form->{exchangerate} = 1;
  61. } else {
  62. $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, $buysell);
  63. $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate});
  64. }
  65. my @taxaccounts = split / /, $form->{taxaccounts};
  66. my $tax = 0;
  67. my $fxtax = 0;
  68. my $amount;
  69. my $diff;
  70. my %tax = ();
  71. my $accno;
  72. # add taxes
  73. foreach $accno (@taxaccounts) {
  74. $fxtax += $tax{fxamount}{$accno} = $form->parse_amount($myconfig, $form->{"tax_$accno"});
  75. $tax += $tax{fxamount}{$accno};
  76. push @{ $form->{acc_trans}{taxes} }, {
  77. accno => $accno,
  78. amount => $tax{fxamount}{$accno},
  79. project_id => undef,
  80. fx_transaction => 0 };
  81. $amount = $tax{fxamount}{$accno} * $form->{exchangerate};
  82. $tax{amount}{$accno} = $form->round_amount($amount - $diff, 2);
  83. $diff = $tax{amount}{$accno} - ($amount - $diff);
  84. $amount = $tax{amount}{$accno} - $tax{fxamount}{$accno};
  85. $tax += $amount;
  86. if ($form->{currency} ne $form->{defaultcurrency}) {
  87. push @{ $form->{acc_trans}{taxes} }, {
  88. accno => $accno,
  89. amount => $amount,
  90. project_id => undef,
  91. fx_transaction => 1 };
  92. }
  93. }
  94. my %amount = ();
  95. my $fxinvamount = 0;
  96. for (1 .. $form->{rowcount}) {
  97. $fxinvamount += $amount{fxamount}{$_} = $form->parse_amount($myconfig, $form->{"amount_$_"})
  98. }
  99. $form->{taxincluded} *= 1;
  100. my $i;
  101. my $project_id;
  102. my $cleared = 0;
  103. $diff = 0;
  104. # deduct tax from amounts if tax included
  105. for $i (1 .. $form->{rowcount}) {
  106. if ($amount{fxamount}{$i}) {
  107. if ($form->{taxincluded}) {
  108. $amount = ($fxinvamount) ? $fxtax * $amount{fxamount}{$i} / $fxinvamount : 0;
  109. $amount{fxamount}{$i} -= $amount;
  110. }
  111. # multiply by exchangerate
  112. $amount = $amount{fxamount}{$i} * $form->{exchangerate};
  113. $amount{amount}{$i} = $form->round_amount($amount - $diff, 2);
  114. $diff = $amount{amount}{$i} - ($amount - $diff);
  115. ($null, $project_id) = split /--/, $form->{"projectnumber_$i"};
  116. $project_id ||= undef;
  117. ($accno) = split /--/, $form->{"${ARAP}_amount_$i"};
  118. if ($keepcleared) {
  119. $cleared = ($form->{"cleared_$i"}) ? 1 : 0;
  120. }
  121. push @{ $form->{acc_trans}{lineitems} }, {
  122. accno => $accno,
  123. amount => $amount{fxamount}{$i},
  124. project_id => $project_id,
  125. description => $form->{"description_$i"},
  126. cleared => $cleared,
  127. fx_transaction => 0 };
  128. if ($form->{currency} ne $form->{defaultcurrency}) {
  129. $amount = $amount{amount}{$i} - $amount{fxamount}{$i};
  130. push @{ $form->{acc_trans}{lineitems} }, {
  131. accno => $accno,
  132. amount => $amount,
  133. project_id => $project_id,
  134. description => $form->{"description_$i"},
  135. cleared => $cleared,
  136. fx_transaction => 1 };
  137. }
  138. }
  139. }
  140. my $invnetamount = 0;
  141. for (@{ $form->{acc_trans}{lineitems} }) { $invnetamount += $_->{amount} }
  142. my $invamount = $invnetamount + $tax;
  143. # adjust paidaccounts if there is no date in the last row
  144. $form->{paidaccounts}--
  145. unless ($form->{"datepaid_$form->{paidaccounts}"});
  146. if ($form->{vc} ne "customer"){
  147. $form->{vc} = "vendor";
  148. }
  149. my $paid = 0;
  150. my $fxamount;
  151. $diff = 0;
  152. # add payments
  153. for $i (1 .. $form->{paidaccounts}) {
  154. $fxamount = $form->parse_amount($myconfig, $form->{"paid_$i"});
  155. if ($fxamount) {
  156. $paid += $fxamount;
  157. $paidamount = $fxamount * $form->{exchangerate};
  158. $amount = $form->round_amount($paidamount - $diff, 2);
  159. $diff = $amount - ($paidamount - $diff);
  160. $form->{datepaid} = $form->{"datepaid_$i"};
  161. $paid{fxamount}{$i} = $fxamount;
  162. $paid{amount}{$i} = $amount;
  163. }
  164. }
  165. $fxinvamount += $fxtax unless $form->{taxincluded};
  166. $fxinvamount = $form->round_amount($fxinvamount, 2);
  167. $invamount = $form->round_amount($invamount, 2);
  168. $paid = $form->round_amount($paid, 2);
  169. $paid = ($fxinvamount == $paid)
  170. ? $invamount
  171. : $form->round_amount($paid * $form->{exchangerate}, 2);
  172. $query = q|
  173. SELECT (SELECT value FROM defaults
  174. WHERE setting_key = 'fxgain_accno_id'),
  175. (SELECT value FROM defaults
  176. WHERE setting_key = 'fxloss_accno_id')|;
  177. my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
  178. ($null, $form->{employee_id}) = split /--/, $form->{employee};
  179. unless ($form->{employee_id}) {
  180. ($form->{employee}, $form->{employee_id}) =
  181. $form->get_employee($dbh);
  182. }
  183. # check if id really exists
  184. if ($form->{id}) {
  185. my $id = $dbh->quote($form->{id});
  186. $keepcleared = 1;
  187. $query = qq|
  188. SELECT id
  189. FROM $table
  190. WHERE id = $id|;
  191. if ($dbh->selectrow_array($query)) {
  192. # delete detail records
  193. $query = qq|
  194. DELETE FROM acc_trans
  195. WHERE trans_id = $id|;
  196. $dbh->do($query) || $form->dberror($query);
  197. }
  198. } else {
  199. my $uid = localtime;
  200. $uid .= "$$";
  201. $query = qq|
  202. INSERT INTO $table (invnumber)
  203. VALUES ('$uid')|;
  204. $dbh->do($query) || $form->dberror($query);
  205. $query = qq|
  206. SELECT id FROM $table
  207. WHERE invnumber = '$uid'|;
  208. ($form->{id}) = $dbh->selectrow_array($query);
  209. }
  210. # record last payment date in ar/ap table
  211. $form->{datepaid} = $form->{transdate} unless $form->{datepaid};
  212. my $datepaid = ($paid) ? qq|'$form->{datepaid}'| : 'NOW';
  213. $form->{invnumber} = $form->update_defaults($myconfig, $invnumber) unless $form->{invnumber};
  214. $query = qq|
  215. UPDATE $table
  216. SET invnumber = ?,
  217. ordnumber = ?,
  218. transdate = ?,
  219. $form->{vc}_id = ?,
  220. taxincluded = ?,
  221. amount = ?,
  222. duedate = ?,
  223. paid = ?,
  224. datepaid = ?,
  225. netamount = ?,
  226. curr = ?,
  227. notes = ?,
  228. department_id = ?,
  229. employee_id = ?,
  230. ponumber = ?
  231. WHERE id = ?
  232. |;
  233. my @queryargs = ($form->{invnumber}, $form->{ordnumber},
  234. $form->{transdate}, $form->{"$form->{vc}_id"},
  235. $form->{taxincluded}, $invamount, $form->{duedate}, $paid,
  236. $datepaid, $invnetamout, $form->{currency}, $form->{notes},
  237. $form->{department_id}, $form->{employee_id},
  238. $form->{ponumber}, $form->{id});
  239. $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
  240. @queries = $form->run_custom_queries($table, 'INSERT');
  241. # update exchangerate
  242. my $buy = $form->{exchangerate};
  243. my $sell = 0;
  244. if ($form->{vc} eq 'vendor') {
  245. $buy = 0;
  246. $sell = $form->{exchangerate};
  247. }
  248. if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
  249. $form->update_exchangerate(
  250. $dbh, $form->{currency}, $form->{transdate},
  251. $buy, $sell);
  252. }
  253. my $ref;
  254. # add individual transactions
  255. foreach $ref (@{ $form->{acc_trans}{lineitems} }) {
  256. # insert detail records in acc_trans
  257. if ($ref->{amount}) {
  258. $query = qq|
  259. INSERT INTO acc_trans
  260. (trans_id, chart_id, amount,
  261. transdate, project_id, memo,
  262. fx_transaction, cleared)
  263. VALUES (?, (SELECT id FROM chart
  264. WHERE accno = ?),
  265. ?, ?, ?, ?, ?, ?)|;
  266. @queryargs = ($form->{id}, $ref->{accno},
  267. $ref->{amount} * $ml, $form->{transdate},
  268. $ref->{project_id}, $ref->{description},
  269. $ref->{fx_transaction}, $ref->{cleared});
  270. $dbh->prepare($query)->execute(@queryargs)
  271. || $form->dberror($query);
  272. }
  273. }
  274. # save taxes
  275. foreach $ref (@{ $form->{acc_trans}{taxes} }) {
  276. if ($ref->{amount}) {
  277. $query = qq|
  278. INSERT INTO acc_trans
  279. (trans_id, chart_id, amount,
  280. transdate, fx_transaction)
  281. VALUES (?, (SELECT id FROM chart
  282. WHERE accno = ?),
  283. ?, ?, ?)|;
  284. @queryargs = ($form->{id}, $ref->{accno},
  285. $ref->{amount} * $ml, $form->{transdate},
  286. $ref->{fx_transaction});
  287. $dbh->prepare($query)->execute(@queryargs)
  288. || $form->dberror($query);
  289. }
  290. }
  291. my $arap;
  292. # record ar/ap
  293. if (($arap = $invamount)) {
  294. ($accno) = split /--/, $form->{$ARAP};
  295. $query = qq|
  296. INSERT INTO acc_trans
  297. (trans_id, chart_id, amount, transdate)
  298. VALUES (?, (SELECT id FROM chart
  299. WHERE accno = ?),
  300. ?, ?)|;
  301. @queryargs = ($form->{id}, $accno, $invamount * -1 * $ml,
  302. $form->{transdate});
  303. $dbh->prepare($query)->execute(@queryargs)
  304. || $form->dberror($query);
  305. }
  306. # if there is no amount force ar/ap
  307. if ($fxinvamount == 0) {
  308. $arap = 1;
  309. }
  310. my $exchangerate;
  311. # add paid transactions
  312. for $i (1 .. $form->{paidaccounts}) {
  313. if ($paid{fxamount}{$i}) {
  314. ($accno) = split(/--/, $form->{"${ARAP}_paid_$i"});
  315. $form->{"datepaid_$i"} = $form->{transdate} unless ($form->{"datepaid_$i"});
  316. $exchangerate = 0;
  317. if ($form->{currency} eq $form->{defaultcurrency}) {
  318. $form->{"exchangerate_$i"} = 1;
  319. } else {
  320. $exchangerate = $form->check_exchangerate(
  321. $myconfig, $form->{currency},
  322. $form->{"datepaid_$i"}, $buysell);
  323. $form->{"exchangerate_$i"} = ($exchangerate)
  324. ? $exchangerate
  325. : $form->parse_amount(
  326. $myconfig,
  327. $form->{"exchangerate_$i"});
  328. }
  329. # if there is no amount
  330. if ($fxinvamount == 0) {
  331. $form->{exchangerate} =
  332. $form->{"exchangerate_$i"};
  333. }
  334. # ar/ap amount
  335. if ($arap) {
  336. ($accno) = split /--/, $form->{$ARAP};
  337. # add ar/ap
  338. $query = qq|
  339. INSERT INTO acc_trans
  340. (trans_id, chart_id,
  341. amount,transdate)
  342. VALUES (?, (SELECT id FROM chart
  343. WHERE accno = ?),
  344. ?, ?)|;
  345. @queryargs = ($form->{id}, $accno,
  346. $paid{amount}{$i} * $ml,
  347. $form->{"datepaid_$i"});
  348. $dbh->prepare($query)->execute(@queryargs)
  349. || $form->dberror($query);
  350. }
  351. $arap = $paid{amount}{$i};
  352. # add payment
  353. if ($paid{fxamount}{$i}) {
  354. ($accno) = split /--/, $form->{"${ARAP}_paid_$i"};
  355. my $cleared = ($form->{"cleared_$i"}) ? 1 : 0;
  356. $amount = $paid{fxamount}{$i};
  357. $query = qq|
  358. INSERT INTO acc_trans
  359. (trans_id, chart_id, amount,
  360. transdate, source, memo,
  361. cleared)
  362. VALUES (?, (SELECT id FROM chart
  363. WHERE accno = ?),
  364. ?, ?, ?, ?, ?)|;
  365. @queryargs = ($form->{id}, $accno,
  366. $amount * -1 * $ml,
  367. $form->{"datepaid_$i"},
  368. $form->{"source_$i"},
  369. $form->{"memo_$i"},
  370. $cleared);
  371. $dbh->prepare($query)->execute(@queryargs)
  372. || $form->dberror($query);
  373. if ($form->{currency}
  374. ne $form->{defaultcurrency}) {
  375. # exchangerate gain/loss
  376. $amount = ($form->round_amount(
  377. $paid{fxamount}{$i}
  378. * $form->{exchangerate},2) -
  379. $form->round_amount(
  380. $paid{fxamount}{$i}
  381. * $form->{"exchangerate_$i"},
  382. 2)) * -1;
  383. if ($amount) {
  384. my $accno_id = (($amount * $ml) > 0) ? $fxgain_accno_id : $fxloss_accno_id;
  385. $query = qq|
  386. INSERT INTO acc_trans
  387. (trans_id,
  388. chart_id,
  389. amount,
  390. transdate,
  391. fx_transaction,
  392. cleared)
  393. VALUES (?, ?,
  394. ?,
  395. ?, '1', ?)|;
  396. @queryargs = ($form->{id},
  397. $accno_id,
  398. $amount * $ml,
  399. $form->{"datepaid_$i"},
  400. $cleared);
  401. $sth = $dbh->prepare($query);
  402. $sth->execute(@queryargs)
  403. ||
  404. $form->dberror($query);
  405. }
  406. # exchangerate difference
  407. $amount = $paid{amount}{$i} - $paid{fxamount}{$i} + $amount;
  408. $query = qq|
  409. INSERT INTO acc_trans
  410. (trans_id, chart_id,
  411. amount,
  412. transdate,
  413. fx_transaction,
  414. cleared, source)
  415. VALUES (?, (SELECT id
  416. FROM chart
  417. WHERE accno
  418. = ?),
  419. ?, ?,
  420. '1', ?, ?)|;
  421. @queryargs = ($form->{id}, $accno,
  422. $amount * -1 * $ml,
  423. $form->{"datepaid_$i"},
  424. $cleared, $form->{"source_$i"});
  425. $sth = $dbh->prepare($query) ;
  426. $sth->execute(@queryargs)
  427. || $form->dberror($query);
  428. }
  429. # update exchangerate record
  430. $buy = $form->{"exchangerate_$i"};
  431. $sell = 0;
  432. if ($form->{vc} eq 'vendor') {
  433. $buy = 0;
  434. $sell = $form->{"exchangerate_$i"};
  435. }
  436. if (($form->{currency} ne
  437. $form->{defaultcurrency}) && !$exchangerate) {
  438. $form->update_exchangerate(
  439. $dbh, $form->{currency},
  440. $form->{"datepaid_$i"}, $buy,
  441. $sell);
  442. }
  443. }
  444. }
  445. }
  446. # save printed and queued
  447. $form->save_status($dbh);
  448. my %audittrail = ( tablename => $table,
  449. reference => $form->{invnumber},
  450. formname => 'transaction',
  451. action => 'posted',
  452. id => $form->{id} );
  453. $form->audittrail($dbh, "", \%audittrail);
  454. $form->save_recurring($dbh, $myconfig);
  455. my $rc = $dbh->commit;
  456. $rc;
  457. }
  458. sub delete_transaction {
  459. my ($self, $myconfig, $form) = @_;
  460. # connect to database, turn AutoCommit off
  461. my $dbh = $form->{dbh};
  462. my $table = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
  463. my %audittrail = ( tablename => $table,
  464. reference => $form->{invnumber},
  465. formname => 'transaction',
  466. action => 'deleted',
  467. id => $form->{id} );
  468. $form->audittrail($dbh, "", \%audittrail);
  469. my $query = qq|DELETE FROM $table WHERE id = $form->{id}|;
  470. $dbh->do($query) || $form->dberror($query);
  471. $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
  472. $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query);
  473. # get spool files
  474. $query = qq|SELECT spoolfile
  475. FROM status
  476. WHERE trans_id = ?
  477. AND spoolfile IS NOT NULL|;
  478. my $sth = $dbh->prepare($query);
  479. $sth->execute($form->{id}) || $form->dberror($query);
  480. my $spoolfile;
  481. my @spoolfiles = ();
  482. while (($spoolfile) = $sth->fetchrow_array) {
  483. push @spoolfiles, $spoolfile;
  484. }
  485. $sth->finish;
  486. $query = qq|DELETE FROM status WHERE trans_id = ?|;
  487. $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query);
  488. # commit
  489. my $rc = $dbh->commit;
  490. if ($rc) {
  491. foreach $spoolfile (@spoolfiles) {
  492. unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;
  493. }
  494. }
  495. $rc;
  496. }
  497. sub transactions {
  498. my ($self, $myconfig, $form) = @_;
  499. # connect to database
  500. my $dbh = $form->{dbh};
  501. my $null;
  502. my $var;
  503. my $paid = "a.paid";
  504. my $ml = 1;
  505. my $ARAP = 'AR';
  506. my $table = 'ar';
  507. my $buysell = 'buy';
  508. my $acc_trans_join;
  509. my $acc_trans_flds;
  510. if ($form->{vc} eq 'vendor') {
  511. $ml = -1;
  512. $ARAP = 'AP';
  513. $table = 'ap';
  514. $buysell = 'sell';
  515. }
  516. ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  517. my @paidargs = ();
  518. if ($form->{outstanding}) {
  519. $paid = qq|
  520. SELECT SUM(ac.amount) * -1 * $ml
  521. FROM acc_trans ac
  522. JOIN chart c ON (c.id = ac.chart_id)
  523. WHERE ac.trans_id = a.id
  524. AND (c.link LIKE '%${ARAP}_paid%'
  525. OR c.link = '')|;
  526. if ($form->{transdateto}){
  527. $paid .= qq|
  528. AND ac.transdate <= ?|;
  529. push @paidargs, $form->{transdateto};
  530. }
  531. $form->{summary} = 1;
  532. }
  533. if (!$form->{summary}) {
  534. $acc_trans_flds = qq|
  535. , c.accno, ac.source,
  536. pr.projectnumber, ac.memo AS description,
  537. ac.amount AS linetotal,
  538. i.description AS linedescription|;
  539. $acc_trans_join = qq|
  540. JOIN acc_trans ac ON (a.id = ac.trans_id)
  541. JOIN chart c ON (c.id = ac.chart_id)
  542. LEFT JOIN project pr ON (pr.id = ac.project_id)
  543. LEFT JOIN invoice i ON (i.id = ac.invoice_id)|;
  544. }
  545. my $query = qq|
  546. SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
  547. a.duedate, a.netamount, a.amount, ($paid) AS paid,
  548. a.invoice, a.datepaid, a.terms, a.notes,
  549. a.shipvia, a.shippingpoint, e.name AS employee,
  550. vc.name,
  551. a.$form->{vc}_id, a.till, m.name AS manager, a.curr,
  552. ex.$buysell AS exchangerate,
  553. d.description AS department,
  554. a.ponumber $acc_trans_flds
  555. FROM $table a
  556. JOIN $form->{vc} vc ON (a.$form->{vc}_id = vc.id)
  557. LEFT JOIN employee e ON (a.employee_id = e.id)
  558. LEFT JOIN employee m ON (e.managerid = m.id)
  559. LEFT JOIN exchangerate ex ON (ex.curr = a.curr
  560. AND ex.transdate = a.transdate)
  561. LEFT JOIN department d ON (a.department_id = d.id)
  562. $acc_trans_join|;
  563. my %ordinal = ( id => 1,
  564. invnumber => 2,
  565. ordnumber => 3,
  566. transdate => 4,
  567. duedate => 5,
  568. datepaid => 10,
  569. shipvia => 13,
  570. shippingpoint => 14,
  571. employee => 15,
  572. name => 16,
  573. manager => 19,
  574. curr => 20,
  575. department => 22,
  576. ponumber => 23,
  577. accno => 24,
  578. source => 25,
  579. project => 26,
  580. description => 27);
  581. my @a = (transdate, invnumber, name);
  582. push @a, "employee" if $form->{l_employee};
  583. push @a, "manager" if $form->{l_manager};
  584. my $sortorder = $form->sort_order(\@a, \%ordinal);
  585. my $where = "1 = 1";
  586. if ($form->{"$form->{vc}_id"}) {
  587. $where .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
  588. } else {
  589. if ($form->{$form->{vc}}) {
  590. $var = $dbh->quote(
  591. $form->like(lc $form->{$form->{vc}}));
  592. $where .= " AND lower(vc.name) LIKE $var";
  593. }
  594. }
  595. for (qw(department employee)) {
  596. if ($form->{$_}) {
  597. ($null, $var) = split /--/, $form->{$_};
  598. $var = $dbh->quote($var);
  599. $where .= " AND a.${_}_id = $var";
  600. }
  601. }
  602. for (qw(invnumber ordnumber)) {
  603. if ($form->{$_}) {
  604. $var = $dbh->quote($form->like(lc $form->{$_}));
  605. $where .= " AND lower(a.$_) LIKE $var";
  606. $form->{open} = $form->{closed} = 0;
  607. }
  608. }
  609. if ($form->{partsid}){
  610. my $partsid = $dbh->quote($form->{partsid});
  611. $where .= " AND a.id IN (select trans_id FROM invoice
  612. WHERE parts_id = $partsid)";
  613. }
  614. for (qw(ponumber shipvia notes)) {
  615. if ($form->{$_}) {
  616. $var = $dbh->quote($form->like(lc $form->{$_}));
  617. $where .= " AND lower(a.$_) LIKE $var";
  618. }
  619. }
  620. if ($form->{description}) {
  621. if ($acc_trans_flds) {
  622. $var = $dbh->quote(
  623. $form->like(lc $form->{description})
  624. );
  625. $where .= " AND lower(ac.memo) LIKE $var
  626. OR lower(i.description) LIKE $var";
  627. } else {
  628. $where .= " AND a.id = 0";
  629. }
  630. }
  631. if ($form->{source}) {
  632. if ($acc_trans_flds) {
  633. $var = $dbh->quote($form->like(lc $form->{source}));
  634. $where .= " AND lower(ac.source) LIKE $var";
  635. } else {
  636. $where .= " AND a.id = 0";
  637. }
  638. }
  639. my $transdatefrom = $dbh->quote($form->{transdatefrom});
  640. $where .= " AND a.transdate >= $transdatefrom"
  641. if $form->{transdatefrom};
  642. my $transdateto = $dbh->quote($form->{transdateto});
  643. $where .= " AND a.transdate <= $transdateto" if $form->{transdateto};
  644. if ($form->{open} || $form->{closed}) {
  645. unless ($form->{open} && $form->{closed}) {
  646. $where .= " AND a.amount != a.paid" if ($form->{open});
  647. $where .= " AND a.amount = a.paid" if ($form->{closed});
  648. }
  649. }
  650. if ($form->{till} ne "") {
  651. $where .= " AND a.invoice = '1'
  652. AND a.till = $form->{till}";
  653. if ($myconfig->{role} eq 'user') {
  654. my $login = $dbh->quote($form->{login});
  655. $where .= " AND e.login = $login";
  656. }
  657. }
  658. if ($form->{$ARAP}) {
  659. my ($accno) = split /--/, $form->{$ARAP};
  660. $accno = $dbh->quote($accno);
  661. $where .= qq|
  662. AND a.id IN (SELECT ac.trans_id
  663. FROM acc_trans ac
  664. JOIN chart c ON (c.id = ac.chart_id)
  665. WHERE a.id = ac.trans_id
  666. AND c.accno = $accno)|;
  667. }
  668. if ($form->{description}) {
  669. $var = $dbh->quote($form->like(lc $form->{description}));
  670. $where .= qq|
  671. AND (a.id IN (SELECT DISTINCT trans_id
  672. FROM acc_trans
  673. WHERE lower(memo) LIKE '$var')
  674. OR a.id IN
  675. (SELECT DISTINCT trans_id
  676. FROM invoice
  677. WHERE lower(description)
  678. LIKE '$var'))|;
  679. }
  680. $query .= "WHERE $where
  681. ORDER BY $sortorder";
  682. my $sth = $dbh->prepare($query);
  683. $sth->execute(@paidargs) || $form->dberror($query);
  684. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  685. $ref->{exchangerate} = 1 unless $ref->{exchangerate};
  686. if ($ref->{linetotal} <= 0) {
  687. $ref->{debit} = $ref->{linetotal} * -1;
  688. $ref->{credit} = 0;
  689. } else {
  690. $ref->{debit} = 0;
  691. $ref->{credit} = $ref->{linetotal};
  692. }
  693. if ($ref->{invoice}) {
  694. $ref->{description} ||= $ref->{linedescription};
  695. }
  696. if ($form->{outstanding}) {
  697. next if $form->round_amount($ref->{amount}, 2)
  698. == $form->round_amount($ref->{paid}, 2);
  699. }
  700. push @{ $form->{transactions} }, $ref;
  701. }
  702. $sth->finish;
  703. $dbh->commit;
  704. }
  705. # this is used in IS, IR to retrieve the name
  706. sub get_name {
  707. my ($self, $myconfig, $form) = @_;
  708. # sanitize $form->{vc}
  709. if ($form->{vc} ne 'customer'){
  710. $form->{vc} = 'vendor';
  711. } else {
  712. $form->{vc} = 'customer';
  713. }
  714. # connect to database
  715. my $dbh = $form->{dbh};
  716. my $dateformat = $myconfig->{dateformat};
  717. if ($myconfig->{dateformat} !~ /^y/) {
  718. my @a = split /\W/, $form->{transdate};
  719. $dateformat .= "yy" if (length $a[2] > 2);
  720. }
  721. if ($form->{transdate} !~ /\W/) {
  722. $dateformat = 'yyyymmdd';
  723. }
  724. my $duedate;
  725. $dateformat = $dbh->quote($dateformat);
  726. my $tdate = $dbh->quote($form->{transdate});
  727. $duedate = ($form->{transdate})
  728. ? "to_date($tdate, $dateformat)
  729. + c.terms"
  730. : "current_date + c.terms";
  731. $form->{"$form->{vc}_id"} *= 1;
  732. # get customer/vendor
  733. my $query = qq|
  734. SELECT c.name AS $form->{vc}, c.discount, c.creditlimit,
  735. c.terms, c.email, c.cc, c.bcc, c.taxincluded,
  736. c.address1, c.address2, c.city, c.state,
  737. c.zipcode, c.country, c.curr AS currency,
  738. c.language_code, $duedate AS duedate,
  739. c.notes AS intnotes,
  740. b.discount AS tradediscount,
  741. b.description AS business,
  742. e.name AS employee, e.id AS employee_id
  743. FROM $form->{vc} c
  744. LEFT JOIN business b ON (b.id = c.business_id)
  745. LEFT JOIN employee e ON (e.id = c.employee_id)
  746. WHERE c.id = ?|;
  747. @queryargs = ($form->{"$form->{vc}_id"});
  748. my $sth = $dbh->prepare($query);
  749. $sth->execute(@queryargs) || $form->dberror($query);
  750. $ref = $sth->fetchrow_hashref(NAME_lc);
  751. if ($form->{id}) {
  752. for (qw(currency employee employee_id intnotes)) {
  753. delete $ref->{$_};
  754. }
  755. }
  756. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  757. $sth->finish;
  758. my $buysell = ($form->{vc} eq 'customer') ? "buy" : "sell";
  759. # if no currency use defaultcurrency
  760. $form->{currency} =
  761. ($form->{currency})
  762. ? $form->{currency}
  763. : $form->{defaultcurrency};
  764. $form->{exchangerate} = 0
  765. if $form->{currency} eq $form->{defaultcurrency};
  766. if ($form->{transdate} && ($form->{currency}
  767. ne $form->{defaultcurrency})) {
  768. $form->{exchangerate} = $form->get_exchangerate(
  769. $dbh, $form->{currency}, $form->{transdate}, $buysell);
  770. }
  771. $form->{forex} = $form->{exchangerate};
  772. # if no employee, default to login
  773. ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh)
  774. unless $form->{employee_id};
  775. my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
  776. my $ARAP = uc $arap;
  777. $form->{creditremaining} = $form->{creditlimit};
  778. $query = qq|
  779. SELECT SUM(amount - paid)
  780. FROM $arap
  781. WHERE $form->{vc}_id = ?|;
  782. $sth = $dbh->prepare($query);
  783. $sth->execute($form->{"$form->{vc}_id"})
  784. || $form->dberror($query);
  785. ($form->{creditremaining}) -= $sth->fetchrow_array;
  786. $sth->finish;
  787. if ($form->{vc} ne "customer"){
  788. $form->{vc} = 'vendor';
  789. }
  790. $query = qq|
  791. SELECT o.amount, (SELECT e.$buysell FROM exchangerate e
  792. WHERE e.curr = o.curr
  793. AND e.transdate = o.transdate)
  794. FROM oe o
  795. WHERE o.$form->{vc}_id = ?
  796. AND o.quotation = '0' AND o.closed = '0'|;
  797. $sth = $dbh->prepare($query);
  798. $sth->execute ($form->{"$form->{vc}_id"}) || $form->dberror($query);
  799. while (my ($amount, $exch) = $sth->fetchrow_array) {
  800. $exch = 1 unless $exch;
  801. $form->{creditremaining} -= $amount * $exch;
  802. }
  803. $sth->finish;
  804. # get shipto if we did not converted an order or invoice
  805. if (!$form->{shipto}) {
  806. for (
  807. qw(shiptoname shiptoaddress1 shiptoaddress2
  808. shiptocity shiptostate shiptozipcode
  809. shiptocountry shiptocontact shiptophone
  810. shiptofax shiptoemail)
  811. ) {
  812. delete $form->{$_}
  813. }
  814. ## needs fixing (SELECT *)
  815. $query = qq|
  816. SELECT *
  817. FROM shipto
  818. WHERE trans_id = $form->{"$form->{vc}_id"}|;
  819. $sth = $dbh->prepare($query);
  820. $sth->execute || $form->dberror($query);
  821. $ref = $sth->fetchrow_hashref(NAME_lc);
  822. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  823. $sth->finish;
  824. }
  825. # get taxes
  826. $query = qq|
  827. SELECT c.accno
  828. FROM chart c
  829. JOIN $form->{vc}tax ct ON (ct.chart_id = c.id)
  830. WHERE ct.$form->{vc}_id = ?|;
  831. $sth = $dbh->prepare($query);
  832. $sth->execute( $form->{"$form->{vc}_id"}) || $form->dberror($query);
  833. my %tax;
  834. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  835. $tax{$ref->{accno}} = 1;
  836. }
  837. $sth->finish;
  838. $transdate = $dbh->quote($form->{transdate});
  839. my $where = qq|AND (t.validto >= $transdate OR t.validto IS NULL)|
  840. if $form->{transdate};
  841. # get tax rates and description
  842. $query = qq|
  843. SELECT c.accno, c.description, t.rate, t.taxnumber
  844. FROM chart c
  845. JOIN tax t ON (c.id = t.chart_id)
  846. WHERE c.link LIKE '%${ARAP}_tax%'
  847. $where
  848. ORDER BY accno, validto|;
  849. $sth = $dbh->prepare($query);
  850. $sth->execute || $form->dberror($query);
  851. $form->{taxaccounts} = "";
  852. my %a = ();
  853. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  854. if ($tax{$ref->{accno}}) {
  855. if (not exists $a{$ref->{accno}}) {
  856. for (qw(rate description taxnumber)) {
  857. $form->{"$ref->{accno}_$_"} =
  858. $ref->{$_};
  859. }
  860. $form->{taxaccounts} .= "$ref->{accno} ";
  861. $a{$ref->{accno}} = 1;
  862. }
  863. }
  864. }
  865. $sth->finish;
  866. chop $form->{taxaccounts};
  867. # setup last accounts used for this customer/vendor
  868. if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
  869. $query = qq|
  870. SELECT c.accno, c.description, c.link, c.category,
  871. ac.project_id, p.projectnumber,
  872. a.department_id, d.description AS department
  873. FROM chart c
  874. JOIN acc_trans ac ON (ac.chart_id = c.id)
  875. JOIN $arap a ON (a.id = ac.trans_id)
  876. LEFT JOIN project p ON (ac.project_id = p.id)
  877. LEFT JOIN department d ON (d.id = a.department_id)
  878. WHERE a.$form->{vc}_id = ?
  879. AND a.id IN (SELECT max(id)
  880. FROM $arap
  881. WHERE $form->{vc}_id =
  882. ?)
  883. |;
  884. $sth = $dbh->prepare($query);
  885. $sth->execute($form->{"$form->{vc}_id"},
  886. $form->{"$form->{vc}_id"}) || $form->dberror($query);
  887. my $i = 0;
  888. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  889. $form->{department} = $ref->{department};
  890. $form->{department_id} = $ref->{department_id};
  891. if ($ref->{link} =~ /_amount/) {
  892. $i++;
  893. $form->{"$form->{ARAP}_amount_$i"} =
  894. "$ref->{accno}--$ref->{description}"
  895. if $ref->{accno};
  896. $form->{"projectnumber_$i"} =
  897. "$ref->{projectnumber}--" .
  898. "$ref->{project_id}"
  899. if $ref->{project_id};
  900. }
  901. if ($ref->{link} eq $form->{ARAP}) {
  902. $form->{$form->{ARAP}} =
  903. $form->{"$form->{ARAP}_1"} =
  904. "$ref->{accno}--".
  905. "$ref->{description}"
  906. if $ref->{accno};
  907. }
  908. }
  909. $sth->finish;
  910. $form->{rowcount} = $i if ($i && !$form->{type});
  911. }
  912. $dbh->commit;
  913. }
  914. 1;