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