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