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