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