summaryrefslogtreecommitdiff
path: root/LedgerSMB/RP.pm
blob: 22af9bb1ec697b5ca82f78dedaa5e258026d2055 (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://www.ledgersmb.org/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2001
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # backend code for reports
  31. #
  32. #======================================================================
  33. package RP;
  34. sub inventory_activity {
  35. my ( $self, $myconfig, $form ) = @_;
  36. ( $form->{fromdate}, $form->{todate} ) =
  37. $form->from_to( $form->{fromyear}, $form->{frommonth}, $form->{interval} )
  38. if $form->{fromyear} && $form->{frommonth};
  39. my $dbh = $form->{dbh};
  40. unless ( $form->{sort_col} ) {
  41. $form->{sort_col} = 'partnumber';
  42. }
  43. my $where = '';
  44. if ( $form->{fromdate} ) {
  45. $where .=
  46. "AND coalesce(ar.transdate, ap.transdate) >= "
  47. . $dbh->quote( $form->{fromdate} );
  48. }
  49. if ( $form->{todate} ) {
  50. $where .=
  51. "AND coalesce(ar.transdate, ap.transdate) < "
  52. . $dbh->quote( $form->{todate} ) . " ";
  53. }
  54. if ( $form->{partnumber} ) {
  55. $where .=
  56. qq| AND p.partnumber ILIKE |
  57. . $dbh->quote( '%' . "$form->{partnumber}%" );
  58. }
  59. if ( $form->{description} ) {
  60. $where .=
  61. q| AND p.description ILIKE |
  62. . $dbh->quote( '%' . "$form->{description}%" );
  63. }
  64. $where =~ s/^\s?AND/WHERE/;
  65. my $query = qq|
  66. SELECT min(p.description) AS description,
  67. min(p.partnumber) AS partnumber, sum(
  68. CASE WHEN i.qty > 0 THEN i.qty ELSE 0 END) AS sold,
  69. sum (CASE WHEN i.qty > 0
  70. THEN i.sellprice * i.qty
  71. ELSE 0 END) AS revenue,
  72. sum(CASE WHEN i.qty < 0 THEN i.qty * -1 ELSE 0 END)
  73. AS received, sum(CASE WHEN i.qty < 0
  74. THEN i.sellprice * i.qty * -1
  75. ELSE 0 END) as expenses,
  76. min(p.id) as id
  77. FROM invoice i
  78. JOIN parts p ON (i.parts_id = p.id)
  79. LEFT JOIN ar ON (ar.id = i.trans_id)
  80. LEFT JOIN ap ON (ap.id = i.trans_id)
  81. $where
  82. GROUP BY i.parts_id
  83. ORDER BY $form->{sort_col}|;
  84. my $sth = $dbh->prepare($query) || $form->dberror($query);
  85. $sth->execute() || $form->dberror($query);
  86. @cols = qw(description sold revenue partnumber received expense);
  87. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  88. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  89. $ref->{net_income} = $ref->{revenue} - $ref->{expense};
  90. map { $ref->{$_} =~ s/^\s*// } @cols;
  91. map { $ref->{$_} =~ s/\s*$// } @cols;
  92. push @{ $form->{TB} }, $ref;
  93. }
  94. $sth->finish;
  95. $dbh->commit;
  96. }
  97. sub yearend_statement {
  98. my ( $self, $myconfig, $form ) = @_;
  99. my $dbh = $form->{dbh};
  100. # if todate < existing yearends, delete GL and yearends
  101. my $query = qq|SELECT trans_id FROM yearend WHERE transdate >= ?|;
  102. my $sth = $dbh->prepare($query);
  103. $sth->execute( $form->{todate} ) || $form->dberror($query);
  104. my @trans_id = ();
  105. my $id;
  106. while ( ($id) = $sth->fetchrow_array ) {
  107. push @trans_id, $id;
  108. }
  109. $sth->finish;
  110. $query = qq|DELETE FROM gl WHERE id = ?|;
  111. $sth = $dbh->prepare($query) || $form->dberror($query);
  112. $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
  113. my $ath = $dbh->prepare($query) || $form->dberror($query);
  114. foreach $id (@trans_id) {
  115. $sth->execute($id);
  116. $ath->execute($id);
  117. $sth->finish;
  118. $ath->finish;
  119. }
  120. my $last_period = 0;
  121. my @categories = qw(I E);
  122. my $category;
  123. $form->{decimalplaces} *= 1;
  124. &get_accounts( $dbh, 0, $form->{fromdate}, $form->{todate}, $form,
  125. \@categories );
  126. $dbh->commit;
  127. # now we got $form->{I}{accno}{ }
  128. # and $form->{E}{accno}{ }
  129. my %account = (
  130. 'I' => {
  131. 'label' => 'income',
  132. 'labels' => 'income',
  133. 'ml' => 1
  134. },
  135. 'E' => {
  136. 'label' => 'expense',
  137. 'labels' => 'expenses',
  138. 'ml' => -1
  139. }
  140. );
  141. foreach $category (@categories) {
  142. foreach $key ( sort keys %{ $form->{$category} } ) {
  143. if ( $form->{$category}{$key}{charttype} eq 'A' ) {
  144. $form->{"total_$account{$category}{labels}_this_period"} +=
  145. $form->{$category}{$key}{this} * $account{$category}{ml};
  146. }
  147. }
  148. }
  149. # totals for income and expenses
  150. $form->{total_income_this_period} =
  151. $form->round_amount( $form->{total_income_this_period},
  152. $form->{decimalplaces} );
  153. $form->{total_expenses_this_period} =
  154. $form->round_amount( $form->{total_expenses_this_period},
  155. $form->{decimalplaces} );
  156. # total for income/loss
  157. $form->{total_this_period} =
  158. $form->{total_income_this_period} - $form->{total_expenses_this_period};
  159. }
  160. sub income_statement {
  161. my ( $self, $myconfig, $form ) = @_;
  162. my $dbh = $form->{dbh};
  163. my $last_period = 0;
  164. my @categories = qw(I E);
  165. my $category;
  166. $form->{decimalplaces} *= 1;
  167. if ( !( $form->{fromdate} || $form->{todate} ) ) {
  168. if ( $form->{fromyear} && $form->{frommonth} ) {
  169. ( $form->{fromdate}, $form->{todate} ) =
  170. $form->from_to( $form->{fromyear}, $form->{frommonth},
  171. $form->{interval} );
  172. }
  173. }
  174. &get_accounts( $dbh, $last_period, $form->{fromdate}, $form->{todate},
  175. $form, \@categories, 1 );
  176. if ( !( $form->{comparefromdate} || $form->{comparetodate} ) ) {
  177. if ( $form->{compareyear} && $form->{comparemonth} ) {
  178. ( $form->{comparefromdate}, $form->{comparetodate} ) =
  179. $form->from_to( $form->{compareyear}, $form->{comparemonth},
  180. $form->{interval} );
  181. }
  182. }
  183. # if there are any compare dates
  184. if ( $form->{comparefromdate} || $form->{comparetodate} ) {
  185. $last_period = 1;
  186. &get_accounts(
  187. $dbh, $last_period,
  188. $form->{comparefromdate},
  189. $form->{comparetodate},
  190. $form, \@categories, 1
  191. );
  192. }
  193. $dbh->commit;
  194. # now we got $form->{I}{accno}{ }
  195. # and $form->{E}{accno}{ }
  196. my %account = (
  197. 'I' => {
  198. 'label' => 'income',
  199. 'labels' => 'income',
  200. 'ml' => 1
  201. },
  202. 'E' => {
  203. 'label' => 'expense',
  204. 'labels' => 'expenses',
  205. 'ml' => -1
  206. }
  207. );
  208. my $str;
  209. foreach $category (@categories) {
  210. foreach $key ( sort keys %{ $form->{$category} } ) {
  211. # push description onto array
  212. ## $str = ( $form->{l_heading} ) ? $form->{padding} : "";
  213. $str = "";
  214. if ( $form->{$category}{$key}{charttype} eq "A" ) {
  215. $str .=
  216. ( $form->{l_accno} )
  217. ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
  218. : "$form->{$category}{$key}{description}";
  219. $str = {account => $form->{$category}{$key}{accno}, text => $str };
  220. $str->{gifi_account} = 1 if $form->{accounttype} eq 'gifi';
  221. }
  222. if ( $form->{$category}{$key}{charttype} eq "H" ) {
  223. if ( $account{$category}{subtotal}
  224. && $form->{l_subtotal} )
  225. {
  226. $dash = "- ";
  227. push(
  228. @{ $form->{"$account{$category}{label}_account"} }, {
  229. text => "$account{$category}{subdescription}",
  230. subtotal => 1,
  231. },
  232. );
  233. push(
  234. @{
  235. $form->{"$account{$category}{labels}_this_period"}
  236. },
  237. $form->format_amount(
  238. $myconfig,
  239. $account{$category}{subthis} *
  240. $account{$category}{ml},
  241. $form->{decimalplaces},
  242. $dash
  243. )
  244. );
  245. if ($last_period) {
  246. # Chris T: Giving up on
  247. # Formatting this one :-(
  248. push(
  249. @{
  250. $form->{
  251. "$account{$category}{labels}_last_period"}
  252. },
  253. $form->format_amount(
  254. $myconfig,
  255. $account{$category}{sublast} *
  256. $account{$category}{ml},
  257. $form->{decimalplaces},
  258. $dash
  259. )
  260. );
  261. }
  262. }
  263. $str = {
  264. text => "$form->{$category}{$key}{description}",
  265. heading => 1,
  266. };
  267. $account{$category}{subthis} = $form->{$category}{$key}{this};
  268. $account{$category}{sublast} = $form->{$category}{$key}{last};
  269. $account{$category}{subdescription} =
  270. $form->{$category}{$key}{description};
  271. $account{$category}{subtotal} = 1;
  272. $form->{$category}{$key}{this} = 0;
  273. $form->{$category}{$key}{last} = 0;
  274. next unless $form->{l_heading};
  275. $dash = " ";
  276. }
  277. push( @{ $form->{"$account{$category}{label}_account"} }, $str );
  278. if ( $form->{$category}{$key}{charttype} eq 'A' ) {
  279. $form->{"total_$account{$category}{labels}_this_period"} +=
  280. $form->{$category}{$key}{this} * $account{$category}{ml};
  281. $dash = "- ";
  282. }
  283. push(
  284. @{ $form->{"$account{$category}{labels}_this_period"} },
  285. $form->format_amount(
  286. $myconfig,
  287. $form->{$category}{$key}{this} * $account{$category}{ml},
  288. $form->{decimalplaces}, $dash
  289. )
  290. );
  291. # add amount or - for last period
  292. if ($last_period) {
  293. $form->{"total_$account{$category}{labels}_last_period"} +=
  294. $form->{$category}{$key}{last} * $account{$category}{ml};
  295. push(
  296. @{ $form->{"$account{$category}{labels}_last_period"} },
  297. $form->format_amount(
  298. $myconfig,
  299. $form->{$category}{$key}{last} *
  300. $account{$category}{ml},
  301. $form->{decimalplaces},
  302. $dash
  303. )
  304. );
  305. }
  306. }
  307. ## $str = ( $form->{l_heading} ) ? $form->{padding} : "";
  308. $str = "";
  309. if ( $account{$category}{subtotal} && $form->{l_subtotal} ) {
  310. push(
  311. @{ $form->{"$account{$category}{label}_account"} }, {
  312. text => "$account{$category}{subdescription}",
  313. subtotal => 1,
  314. },
  315. );
  316. push(
  317. @{ $form->{"$account{$category}{labels}_this_period"} },
  318. $form->format_amount(
  319. $myconfig,
  320. $account{$category}{subthis} * $account{$category}{ml},
  321. $form->{decimalplaces}, $dash
  322. )
  323. );
  324. if ($last_period) {
  325. push(
  326. @{ $form->{"$account{$category}{labels}_last_period"} },
  327. $form->format_amount(
  328. $myconfig,
  329. $account{$category}{sublast} * $account{$category}{ml},
  330. $form->{decimalplaces},
  331. $dash
  332. )
  333. );
  334. }
  335. }
  336. }
  337. # totals for income and expenses
  338. $form->{total_income_this_period} =
  339. $form->round_amount( $form->{total_income_this_period},
  340. $form->{decimalplaces} );
  341. $form->{total_expenses_this_period} =
  342. $form->round_amount( $form->{total_expenses_this_period},
  343. $form->{decimalplaces} );
  344. # total for income/loss
  345. $form->{total_this_period} =
  346. $form->{total_income_this_period} - $form->{total_expenses_this_period};
  347. if ($last_period) {
  348. # total for income/loss
  349. $form->{total_last_period} = $form->format_amount(
  350. $myconfig,
  351. $form->{total_income_last_period} -
  352. $form->{total_expenses_last_period},
  353. $form->{decimalplaces},
  354. "- "
  355. );
  356. # totals for income and expenses for last_period
  357. $form->{total_income_last_period} = $form->format_amount(
  358. $myconfig,
  359. $form->{total_income_last_period},
  360. $form->{decimalplaces}, "- "
  361. );
  362. $form->{total_expenses_last_period} = $form->format_amount(
  363. $myconfig,
  364. $form->{total_expenses_last_period},
  365. $form->{decimalplaces}, "- "
  366. );
  367. }
  368. $form->{total_income_this_period} = $form->format_amount(
  369. $myconfig,
  370. $form->{total_income_this_period},
  371. $form->{decimalplaces}, "- "
  372. );
  373. $form->{total_expenses_this_period} = $form->format_amount(
  374. $myconfig,
  375. $form->{total_expenses_this_period},
  376. $form->{decimalplaces}, "- "
  377. );
  378. $form->{total_this_period} = $form->format_amount(
  379. $myconfig,
  380. $form->{total_this_period},
  381. $form->{decimalplaces}, "- "
  382. );
  383. }
  384. sub balance_sheet {
  385. my ( $self, $myconfig, $form ) = @_;
  386. my $dbh = $form->{dbh};
  387. my $last_period = 0;
  388. my @categories = qw(A L Q);
  389. my $null;
  390. if ( $form->{asofdate} ) {
  391. if ( $form->{asofyear} && $form->{asofmonth} ) {
  392. if ( $form->{asofdate} !~ /\W/ ) {
  393. $form->{asofdate} =
  394. "$form->{asofyear}$form->{asofmonth}$form->{asofdate}";
  395. }
  396. }
  397. }
  398. else {
  399. if ( $form->{asofyear} && $form->{asofmonth} ) {
  400. ( $null, $form->{asofdate} ) =
  401. $form->from_to( $form->{asofyear}, $form->{asofmonth} );
  402. }
  403. }
  404. # if there are any dates construct a where
  405. if ( $form->{asofdate} ) {
  406. $form->{this_period} = "$form->{asofdate}";
  407. $form->{period} = "$form->{asofdate}";
  408. }
  409. $form->{decimalplaces} *= 1;
  410. &get_accounts( $dbh, $last_period, "", $form->{asofdate}, $form,
  411. \@categories, 1 );
  412. if ( $form->{compareasofdate} ) {
  413. if ( $form->{compareasofyear} && $form->{compareasofmonth} ) {
  414. if ( $form->{compareasofdate} !~ /\W/ ) {
  415. $form->{compareasofdate} =
  416. "$form->{compareasofyear}$form->{compareasofmonth}$form->{compareasofdate}";
  417. }
  418. }
  419. }
  420. else {
  421. if ( $form->{compareasofyear} && $form->{compareasofmonth} ) {
  422. ( $null, $form->{compareasofdate} ) =
  423. $form->from_to( $form->{compareasofyear},
  424. $form->{compareasofmonth} );
  425. }
  426. }
  427. # if there are any compare dates
  428. if ( $form->{compareasofdate} ) {
  429. $last_period = 1;
  430. &get_accounts( $dbh, $last_period, "", $form->{compareasofdate},
  431. $form, \@categories, 1 );
  432. $form->{last_period} = "$form->{compareasofdate}";
  433. }
  434. $dbh->commit;
  435. # now we got $form->{A}{accno}{ } assets
  436. # and $form->{L}{accno}{ } liabilities
  437. # and $form->{Q}{accno}{ } equity
  438. # build asset accounts
  439. my $str;
  440. my $key;
  441. my %account = (
  442. 'A' => {
  443. 'label' => 'asset',
  444. 'labels' => 'assets',
  445. 'ml' => -1
  446. },
  447. 'L' => {
  448. 'label' => 'liability',
  449. 'labels' => 'liabilities',
  450. 'ml' => 1
  451. },
  452. 'Q' => {
  453. 'label' => 'equity',
  454. 'labels' => 'equity',
  455. 'ml' => 1
  456. }
  457. );
  458. foreach $category (@categories) {
  459. foreach $key ( sort keys %{ $form->{$category} } ) {
  460. ## $str = ( $form->{l_heading} ) ? $form->{padding} : "";
  461. $str = "";
  462. if ( $form->{$category}{$key}{charttype} eq "A" ) {
  463. $str .=
  464. ( $form->{l_accno} )
  465. ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
  466. : "$form->{$category}{$key}{description}";
  467. $str = {account => $form->{$category}{$key}{accno}, text => $str};
  468. $str->{gifi_account} = 1 if $form->{accounttype} eq 'gifi';
  469. }
  470. elsif ( $form->{$category}{$key}{charttype} eq "H" ) {
  471. if ( $account{$category}{subtotal}
  472. && $form->{l_subtotal} )
  473. {
  474. $dash = "- ";
  475. push(
  476. @{ $form->{"$account{$category}{label}_account"} },
  477. {
  478. text => "$account{$category}{subdescription}",
  479. subtotal => 1
  480. },
  481. );
  482. push(
  483. @{ $form->{"$account{$category}{label}_this_period"} },
  484. $form->format_amount(
  485. $myconfig,
  486. $account{$category}{subthis} *
  487. $account{$category}{ml},
  488. $form->{decimalplaces},
  489. $dash
  490. )
  491. );
  492. if ($last_period) {
  493. push(
  494. @{
  495. $form->{
  496. "$account{$category}{label}_last_period"}
  497. },
  498. $form->format_amount(
  499. $myconfig,
  500. $account{$category}{sublast} *
  501. $account{$category}{ml},
  502. $form->{decimalplaces},
  503. $dash
  504. )
  505. );
  506. }
  507. }
  508. $str = {
  509. text => "$form->{$category}{$key}{description}",
  510. heading => 1
  511. };
  512. $account{$category}{subthis} = $form->{$category}{$key}{this};
  513. $account{$category}{sublast} = $form->{$category}{$key}{last};
  514. $account{$category}{subdescription} =
  515. $form->{$category}{$key}{description};
  516. $account{$category}{subtotal} = 1;
  517. $form->{$category}{$key}{this} = 0;
  518. $form->{$category}{$key}{last} = 0;
  519. next unless $form->{l_heading};
  520. $dash = " ";
  521. }
  522. # push description onto array
  523. push( @{ $form->{"$account{$category}{label}_account"} }, $str );
  524. if ( $form->{$category}{$key}{charttype} eq 'A' ) {
  525. $form->{"total_$account{$category}{labels}_this_period"} +=
  526. $form->{$category}{$key}{this} * $account{$category}{ml};
  527. $dash = "- ";
  528. }
  529. push(
  530. @{ $form->{"$account{$category}{label}_this_period"} },
  531. $form->format_amount(
  532. $myconfig,
  533. $form->{$category}{$key}{this} * $account{$category}{ml},
  534. $form->{decimalplaces}, $dash
  535. )
  536. );
  537. if ($last_period) {
  538. $form->{"total_$account{$category}{labels}_last_period"} +=
  539. $form->{$category}{$key}{last} * $account{$category}{ml};
  540. push(
  541. @{ $form->{"$account{$category}{label}_last_period"} },
  542. $form->format_amount(
  543. $myconfig,
  544. $form->{$category}{$key}{last} *
  545. $account{$category}{ml},
  546. $form->{decimalplaces},
  547. $dash
  548. )
  549. );
  550. }
  551. }
  552. #$str = ( $form->{l_heading} ) ? $form->{padding} : "";
  553. $str = "";
  554. if ( $account{$category}{subtotal} && $form->{l_subtotal} ) {
  555. push(
  556. @{ $form->{"$account{$category}{label}_account"} }, {
  557. text => "$account{$category}{subdescription}",
  558. subtotal => 1,
  559. },
  560. );
  561. push(
  562. @{ $form->{"$account{$category}{label}_this_period"} },
  563. $form->format_amount(
  564. $myconfig,
  565. $account{$category}{subthis} * $account{$category}{ml},
  566. $form->{decimalplaces}, $dash
  567. )
  568. );
  569. if ($last_period) {
  570. push(
  571. @{ $form->{"$account{$category}{label}_last_period"} },
  572. $form->format_amount(
  573. $myconfig,
  574. $account{$category}{sublast} * $account{$category}{ml},
  575. $form->{decimalplaces},
  576. $dash
  577. )
  578. );
  579. }
  580. }
  581. }
  582. # totals for assets, liabilities
  583. $form->{total_assets_this_period} =
  584. $form->round_amount( $form->{total_assets_this_period},
  585. $form->{decimalplaces} );
  586. $form->{total_liabilities_this_period} =
  587. $form->round_amount( $form->{total_liabilities_this_period},
  588. $form->{decimalplaces} );
  589. $form->{total_equity_this_period} =
  590. $form->round_amount( $form->{total_equity_this_period},
  591. $form->{decimalplaces} );
  592. # calculate earnings
  593. $form->{earnings_this_period} =
  594. $form->{total_assets_this_period} -
  595. $form->{total_liabilities_this_period} -
  596. $form->{total_equity_this_period};
  597. push(
  598. @{ $form->{equity_this_period} },
  599. $form->format_amount(
  600. $myconfig, $form->{earnings_this_period},
  601. $form->{decimalplaces}, "- "
  602. )
  603. );
  604. $form->{total_equity_this_period} =
  605. $form->round_amount(
  606. $form->{total_equity_this_period} + $form->{earnings_this_period},
  607. $form->{decimalplaces} );
  608. # add liability + equity
  609. $form->{total_this_period} = $form->format_amount(
  610. $myconfig,
  611. $form->{total_liabilities_this_period} +
  612. $form->{total_equity_this_period},
  613. $form->{decimalplaces},
  614. "- "
  615. );
  616. if ($last_period) {
  617. # totals for assets, liabilities
  618. $form->{total_assets_last_period} =
  619. $form->round_amount( $form->{total_assets_last_period},
  620. $form->{decimalplaces} );
  621. $form->{total_liabilities_last_period} =
  622. $form->round_amount( $form->{total_liabilities_last_period},
  623. $form->{decimalplaces} );
  624. $form->{total_equity_last_period} =
  625. $form->round_amount( $form->{total_equity_last_period},
  626. $form->{decimalplaces} );
  627. # calculate retained earnings
  628. $form->{earnings_last_period} =
  629. $form->{total_assets_last_period} -
  630. $form->{total_liabilities_last_period} -
  631. $form->{total_equity_last_period};
  632. push(
  633. @{ $form->{equity_last_period} },
  634. $form->format_amount(
  635. $myconfig, $form->{earnings_last_period},
  636. $form->{decimalplaces}, "- "
  637. )
  638. );
  639. $form->{total_equity_last_period} =
  640. $form->round_amount(
  641. $form->{total_equity_last_period} + $form->{earnings_last_period},
  642. $form->{decimalplaces} );
  643. # add liability + equity
  644. $form->{total_last_period} = $form->format_amount(
  645. $myconfig,
  646. $form->{total_liabilities_last_period} +
  647. $form->{total_equity_last_period},
  648. $form->{decimalplaces},
  649. "- "
  650. );
  651. }
  652. $form->{total_liabilities_last_period} = $form->format_amount(
  653. $myconfig,
  654. $form->{total_liabilities_last_period},
  655. $form->{decimalplaces}, "- "
  656. ) if ( $form->{total_liabilities_last_period} );
  657. $form->{total_equity_last_period} = $form->format_amount(
  658. $myconfig,
  659. $form->{total_equity_last_period},
  660. $form->{decimalplaces}, "- "
  661. ) if ( $form->{total_equity_last_period} );
  662. $form->{total_assets_last_period} = $form->format_amount(
  663. $myconfig,
  664. $form->{total_assets_last_period},
  665. $form->{decimalplaces}, "- "
  666. ) if ( $form->{total_assets_last_period} );
  667. $form->{total_assets_this_period} = $form->format_amount(
  668. $myconfig,
  669. $form->{total_assets_this_period},
  670. $form->{decimalplaces}, "- "
  671. );
  672. $form->{total_liabilities_this_period} = $form->format_amount(
  673. $myconfig,
  674. $form->{total_liabilities_this_period},
  675. $form->{decimalplaces}, "- "
  676. );
  677. $form->{total_equity_this_period} = $form->format_amount(
  678. $myconfig,
  679. $form->{total_equity_this_period},
  680. $form->{decimalplaces}, "- "
  681. );
  682. }
  683. sub get_accounts {
  684. my ( $dbh, $last_period, $fromdate, $todate, $form, $categories,
  685. $excludeyearend )
  686. = @_;
  687. my $department_id;
  688. my $project_id;
  689. ( $null, $department_id ) = split /--/, $form->{department};
  690. ( $null, $project_id ) = split /--/, $form->{projectnumber};
  691. my $query;
  692. my $dpt_where;
  693. my $dpt_join;
  694. my $project;
  695. my $where = "1 = 1";
  696. my $glwhere = "";
  697. my $subwhere = "";
  698. my $yearendwhere = "1 = 1";
  699. my $item;
  700. my $category = "AND (";
  701. foreach $item ( @{$categories} ) {
  702. $category .= qq|c.category = | . $dbh->quote($item) . qq| OR |;
  703. }
  704. $category =~ s/OR $/\)/;
  705. # get headings
  706. $query = qq|
  707. SELECT accno, description, category
  708. FROM chart c
  709. WHERE c.charttype = 'H' $category
  710. ORDER BY c.accno|;
  711. if ( $form->{accounttype} eq 'gifi' ) {
  712. $query = qq|
  713. SELECT g.accno, g.description, c.category
  714. FROM gifi g
  715. JOIN chart c ON (c.gifi_accno = g.accno)
  716. WHERE c.charttype = 'H' $category
  717. ORDER BY g.accno|;
  718. }
  719. $sth = $dbh->prepare($query);
  720. $sth->execute || $form->dberror($query);
  721. my @headingaccounts = ();
  722. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  723. $form->{ $ref->{category} }{ $ref->{accno} }{description} =
  724. "$ref->{description}";
  725. $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "H";
  726. $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
  727. push @headingaccounts, $ref->{accno};
  728. }
  729. $sth->finish;
  730. if ( $form->{method} eq 'cash' && !$todate ) {
  731. ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
  732. }
  733. if ($fromdate) {
  734. if ( $form->{method} eq 'cash' ) {
  735. $subwhere .= " AND transdate >= " . $dbh->quote($fromdate);
  736. $glwhere = " AND ac.transdate >= " . $dbh->quote($fromdate);
  737. }
  738. else {
  739. $where .= " AND ac.transdate >= " . $dbh->quote($fromdate);
  740. }
  741. }
  742. if ($todate) {
  743. $where .= " AND ac.transdate <= " . $dbh->quote($todate);
  744. $subwhere .= " AND transdate <= " . $dbh->quote($todate);
  745. $yearendwhere = "ac.transdate < " . $dbh->quote($todate);
  746. }
  747. if ($excludeyearend) {
  748. $ywhere = "
  749. AND ac.trans_id NOT IN (SELECT trans_id FROM yearend)";
  750. if ($todate) {
  751. $ywhere = "
  752. AND ac.trans_id NOT IN
  753. (SELECT trans_id FROM yearend
  754. WHERE transdate <= " . $dbh->quote($todate) . ")";
  755. }
  756. if ($fromdate) {
  757. $ywhere = "
  758. AND ac.trans_id NOT IN
  759. (SELECT trans_id FROM yearend
  760. WHERE transdate >= " . $dbh->quote($fromdate) . ")";
  761. if ($todate) {
  762. $ywhere = "
  763. AND ac.trans_id NOT IN
  764. (SELECT trans_id FROM yearend
  765. WHERE transdate >= "
  766. . $dbh->quote($fromdate) . "
  767. AND transdate <= " . $dbh->quote($todate) . ")";
  768. }
  769. }
  770. }
  771. if ($department_id) {
  772. $dpt_join = qq|
  773. JOIN department t ON (a.department_id = t.id)|;
  774. $dpt_where = qq|
  775. AND t.id = $department_id|;
  776. }
  777. if ($project_id) {
  778. $project = qq|
  779. AND ac.project_id = $project_id|;
  780. }
  781. if (!defined $form->{approved}){
  782. $approved = 'true';
  783. } elsif ($form->{approved} eq 'all') {
  784. $approved = 'NULL';
  785. } else {
  786. $approved = $dbh->quote($form->{approved});
  787. }
  788. if ( $form->{accounttype} eq 'gifi' ) {
  789. if ( $form->{method} eq 'cash' ) {
  790. $query = qq|
  791. SELECT g.accno, sum(ac.amount) AS amount,
  792. g.description, c.category
  793. FROM acc_trans ac
  794. JOIN chart c ON (c.id = ac.chart_id)
  795. JOIN ar a ON (a.id = ac.trans_id)
  796. JOIN gifi g ON (g.accno = c.gifi_accno)
  797. $dpt_join
  798. WHERE $where $ywhere $dpt_where $category
  799. AND ac.trans_id IN (
  800. SELECT trans_id
  801. FROM acc_trans
  802. JOIN chart ON (chart_id = id)
  803. WHERE link LIKE '%AR_paid%'
  804. $subwhere)
  805. $project
  806. GROUP BY g.accno, g.description, c.category
  807. UNION ALL
  808. SELECT '' AS accno, SUM(ac.amount) AS amount,
  809. '' AS description, c.category
  810. FROM acc_trans ac
  811. JOIN chart c ON (c.id = ac.chart_id)
  812. JOIN ar a ON (a.id = ac.trans_id)
  813. $dpt_join
  814. WHERE $where $ywhere $dpt_where $category
  815. AND c.gifi_accno = '' AND
  816. ac.trans_id IN
  817. (SELECT trans_id FROM acc_trans
  818. JOIN chart ON (chart_id = id)
  819. WHERE link LIKE '%AR_paid%'
  820. $subwhere) $project
  821. GROUP BY c.category
  822. UNION ALL
  823. SELECT g.accno, sum(ac.amount) AS amount,
  824. g.description, c.category
  825. FROM acc_trans ac
  826. JOIN chart c ON (c.id = ac.chart_id)
  827. JOIN ap a ON (a.id = ac.trans_id)
  828. JOIN gifi g ON (g.accno = c.gifi_accno)
  829. $dpt_join
  830. WHERE $where $ywhere $dpt_where $category
  831. AND ac.trans_id IN
  832. (SELECT trans_id FROM acc_trans
  833. JOIN chart ON (chart_id = id)
  834. WHERE link LIKE '%AP_paid%'
  835. $subwhere) $project
  836. GROUP BY g.accno, g.description, c.category
  837. UNION ALL
  838. SELECT '' AS accno, SUM(ac.amount) AS amount,
  839. '' AS description, c.category
  840. FROM acc_trans ac
  841. JOIN chart c ON (c.id = ac.chart_id)
  842. JOIN ap a ON (a.id = ac.trans_id)
  843. $dpt_join
  844. WHERE $where $ywhere $dpt_where $category
  845. AND c.gifi_accno = ''
  846. AND ac.trans_id IN
  847. (SELECT trans_id FROM acc_trans
  848. JOIN chart ON (chart_id = id)
  849. WHERE link LIKE '%AP_paid%' $subwhere)
  850. $project
  851. GROUP BY c.category
  852. UNION ALL
  853. SELECT g.accno, sum(ac.amount) AS amount,
  854. g.description, c.category
  855. FROM acc_trans ac
  856. JOIN chart c ON (c.id = ac.chart_id)
  857. JOIN gifi g ON (g.accno = c.gifi_accno)
  858. JOIN gl a ON (a.id = ac.trans_id)
  859. $dpt_join
  860. WHERE $where $ywhere $glwhere $dpt_where
  861. $category AND NOT
  862. (c.link = 'AR' OR c.link = 'AP')
  863. $project
  864. GROUP BY g.accno, g.description, c.category
  865. UNION ALL
  866. SELECT '' AS accno, SUM(ac.amount) AS amount,
  867. '' AS description, c.category
  868. FROM acc_trans ac
  869. JOIN chart c ON (c.id = ac.chart_id)
  870. JOIN gl a ON (a.id = ac.trans_id)
  871. $dpt_join
  872. WHERE $where $ywhere $glwhere $dpt_where
  873. $category AND c.gifi_accno = ''
  874. AND NOT
  875. (c.link = 'AR' OR c.link = 'AP')
  876. $project
  877. GROUP BY c.category|;
  878. if ($excludeyearend) {
  879. $query .= qq|
  880. UNION ALL
  881. SELECT g.accno,
  882. sum(ac.amount) AS amount,
  883. g.description, c.category
  884. FROM yearend y
  885. JOIN gl a ON (a.id = y.trans_id)
  886. JOIN acc_trans ac
  887. ON (ac.trans_id = y.trans_id)
  888. JOIN chart c
  889. ON (c.id = ac.chart_id)
  890. JOIN gifi g
  891. ON (g.accno = c.gifi_accno)
  892. $dpt_join
  893. WHERE $yearendwhere
  894. AND c.category = 'Q'
  895. $dpt_where $project
  896. GROUP BY g.accno, g.description,
  897. c.category|;
  898. }
  899. }
  900. else {
  901. if ($department_id) {
  902. $dpt_join = qq|
  903. JOIN dpt_trans t
  904. ON (t.trans_id = ac.trans_id)|;
  905. $dpt_where = qq|
  906. AND t.department_id = | . $dbh->quote($department_id);
  907. }
  908. $query = qq|
  909. SELECT g.accno, SUM(ac.amount) AS amount,
  910. g.description, c.category
  911. FROM acc_trans ac
  912. JOIN chart c ON (c.id = ac.chart_id)
  913. JOIN gifi g ON (c.gifi_accno = g.accno)
  914. JOIN (SELECT id, approved FROM gl UNION
  915. SELECT id, approved FROM ar UNION
  916. SELECT id, approved FROM ap) gl
  917. ON (ac.trans_id = gl.id)
  918. $dpt_join
  919. WHERE $where $ywhere $dpt_where $category
  920. AND gl.approved AND ac.approved
  921. $project
  922. GROUP BY g.accno, g.description, c.category
  923. UNION ALL
  924. SELECT '' AS accno, SUM(ac.amount) AS amount,
  925. '' AS description, c.category
  926. FROM acc_trans ac
  927. JOIN chart c ON (c.id = ac.chart_id)
  928. $dpt_join
  929. WHERE $where $ywhere $dpt_where $category
  930. AND c.gifi_accno = '' $project
  931. GROUP BY c.category|;
  932. if ($excludeyearend) {
  933. $query .= qq|
  934. UNION ALL
  935. SELECT g.accno,
  936. sum(ac.amount)
  937. AS amount,
  938. g.description,
  939. c.category
  940. FROM yearend y
  941. JOIN gl a
  942. ON (a.id = y.trans_id)
  943. JOIN acc_trans ac
  944. ON (ac.trans_id =
  945. y.trans_id)
  946. JOIN chart c
  947. ON
  948. (c.id = ac.chart_id)
  949. JOIN gifi g
  950. ON (g.accno =
  951. c.gifi_accno)
  952. $dpt_join
  953. WHERE $yearendwhere
  954. AND c.category = 'Q'
  955. $dpt_where $project
  956. GROUP BY g.accno,
  957. g.description,
  958. c.category|;
  959. }
  960. }
  961. }
  962. else { # standard account
  963. if ( $form->{method} eq 'cash' ) {
  964. $query = qq|
  965. SELECT c.accno, sum(ac.amount) AS amount,
  966. c.description, c.category
  967. FROM acc_trans ac
  968. JOIN chart c ON (c.id = ac.chart_id)
  969. JOIN ar a ON (a.id = ac.trans_id) $dpt_join
  970. WHERE $where $ywhere $dpt_where $category
  971. AND ac.trans_id IN (
  972. SELECT trans_id FROM acc_trans
  973. JOIN chart ON (chart_id = id)
  974. WHERE link LIKE '%AR_paid%' $subwhere)
  975. $project
  976. GROUP BY c.accno, c.description, c.category
  977. UNION ALL
  978. SELECT c.accno, sum(ac.amount) AS amount,
  979. c.description, c.category
  980. FROM acc_trans ac
  981. JOIN chart c ON (c.id = ac.chart_id)
  982. JOIN ap a ON (a.id = ac.trans_id) $dpt_join
  983. WHERE $where $ywhere $dpt_where $category
  984. AND ac.trans_id IN (
  985. SELECT trans_id FROM acc_trans
  986. JOIN chart ON (chart_id = id)
  987. WHERE link LIKE '%AP_paid%' $subwhere)
  988. $project
  989. GROUP BY c.accno, c.description, c.category
  990. UNION ALL
  991. SELECT c.accno, sum(ac.amount) AS amount,
  992. c.description, c.category
  993. FROM acc_trans ac
  994. JOIN chart c ON (c.id = ac.chart_id)
  995. JOIN gl a ON (a.id = ac.trans_id) $dpt_join
  996. WHERE $where $ywhere $glwhere $dpt_where $category
  997. AND NOT (c.link = 'AR' OR c.link = 'AP')
  998. $project
  999. GROUP BY c.accno, c.description, c.category|;
  1000. if ($excludeyearend) {
  1001. # this is for the yearend
  1002. $query .= qq|
  1003. UNION ALL
  1004. SELECT c.accno,
  1005. sum(ac.amount) AS amount,
  1006. c.description, c.category
  1007. FROM yearend y
  1008. JOIN gl a ON (a.id = y.trans_id)
  1009. JOIN acc_trans ac
  1010. ON (ac.trans_id = y.trans_id)
  1011. JOIN chart c
  1012. ON (c.id = ac.chart_id)
  1013. $dpt_join
  1014. WHERE $yearendwhere AND
  1015. c.category = 'Q' $dpt_where
  1016. $project
  1017. GROUP BY c.accno, c.description,
  1018. c.category|;
  1019. }
  1020. }
  1021. else {
  1022. if ($department_id) {
  1023. $dpt_join = qq|
  1024. JOIN dpt_trans t
  1025. ON (t.trans_id = ac.trans_id)|;
  1026. $dpt_where =
  1027. qq| AND t.department_id = | . $dbh->quote($department_id);
  1028. }
  1029. $query = qq|
  1030. SELECT c.accno, sum(ac.amount) AS amount,
  1031. c.description, c.category
  1032. FROM acc_trans ac
  1033. JOIN (SELECT id, approved FROM ar
  1034. UNION
  1035. SELECT id, approved FROM ap
  1036. UNION
  1037. SELECT id, approved FROM gl
  1038. ) g ON (ac.trans_id = g.id)
  1039. JOIN chart c ON (c.id = ac.chart_id)
  1040. $dpt_join
  1041. WHERE $where $ywhere $dpt_where $category
  1042. $project
  1043. AND ($approved IS NULL OR
  1044. $approved =
  1045. (ac.approved AND g.approved))
  1046. GROUP BY c.accno, c.description, c.category|;
  1047. if ($excludeyearend) {
  1048. $query .= qq|
  1049. UNION ALL
  1050. SELECT c.accno,
  1051. sum(ac.amount) AS amount,
  1052. c.description, c.category
  1053. FROM yearend y
  1054. JOIN gl a ON (a.id = y.trans_id)
  1055. JOIN acc_trans ac
  1056. ON (ac.trans_id = y.trans_id)
  1057. JOIN chart c
  1058. ON (c.id = ac.chart_id)
  1059. $dpt_join
  1060. WHERE $yearendwhere AND
  1061. c.category = 'Q' $dpt_where
  1062. $project
  1063. GROUP BY c.accno, c.description,
  1064. c.category|;
  1065. }
  1066. }
  1067. }
  1068. my @accno;
  1069. my $accno;
  1070. my $ref;
  1071. my $sth = $dbh->prepare($query);
  1072. $sth->execute || $form->dberror($query);
  1073. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1074. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1075. # get last heading account
  1076. @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
  1077. $accno = pop @accno;
  1078. if ( $accno && ( $accno ne $ref->{accno} ) ) {
  1079. if ($last_period) {
  1080. $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
  1081. }
  1082. else {
  1083. $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
  1084. }
  1085. }
  1086. $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
  1087. $form->{ $ref->{category} }{ $ref->{accno} }{description} =
  1088. $ref->{description};
  1089. $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
  1090. if ($last_period) {
  1091. $form->{ $ref->{category} }{ $ref->{accno} }{last} +=
  1092. $ref->{amount};
  1093. }
  1094. else {
  1095. $form->{ $ref->{category} }{ $ref->{accno} }{this} +=
  1096. $ref->{amount};
  1097. }
  1098. }
  1099. $sth->finish;
  1100. # remove accounts with zero balance
  1101. foreach $category ( @{$categories} ) {
  1102. foreach $accno ( keys %{ $form->{$category} } ) {
  1103. $form->{$category}{$accno}{last} =
  1104. $form->round_amount( $form->{$category}{$accno}{last},
  1105. $form->{decimalplaces} );
  1106. $form->{$category}{$accno}{this} =
  1107. $form->round_amount( $form->{$category}{$accno}{this},
  1108. $form->{decimalplaces} );
  1109. delete $form->{$category}{$accno}
  1110. if ( $form->{$category}{$accno}{this} == 0
  1111. && $form->{$category}{$accno}{last} == 0 );
  1112. }
  1113. }
  1114. }
  1115. sub trial_balance {
  1116. my ( $self, $myconfig, $form ) = @_;
  1117. my $dbh = $form->{dbh};
  1118. my $approved = ($form->{approved})? 'TRUE' : 'FALSE';
  1119. my ( $query, $sth, $ref );
  1120. my %balance = ();
  1121. my %trb = ();
  1122. my $null;
  1123. my $department_id;
  1124. my $project_id;
  1125. my @headingaccounts = ();
  1126. my $dpt_where;
  1127. my $dpt_join;
  1128. my $project;
  1129. my $where = "1 = 1";
  1130. my $invwhere = $where;
  1131. ( $null, $department_id ) = split /--/, $form->{department};
  1132. ( $null, $project_id ) = split /--/, $form->{projectnumber};
  1133. if ($department_id) {
  1134. $dpt_join = qq|
  1135. JOIN dpt_trans t ON (ac.trans_id = t.trans_id)|;
  1136. $dpt_where = qq|
  1137. AND t.department_id = | . $dbh->quote($department_id);
  1138. }
  1139. if ($project_id) {
  1140. $project = qq|
  1141. AND ac.project_id = | . $dbh->quote($project_id);
  1142. }
  1143. ( $form->{fromdate}, $form->{todate} ) =
  1144. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  1145. if $form->{year} && $form->{month};
  1146. # get beginning balances
  1147. if ( $form->{fromdate} ) {
  1148. if ( $form->{accounttype} eq 'gifi' ) {
  1149. $query = qq|
  1150. SELECT g.accno, c.category,
  1151. SUM(ac.amount) AS amount,
  1152. g.description, c.contra
  1153. FROM acc_trans ac
  1154. JOIN chart c ON (ac.chart_id = c.id)
  1155. JOIN gifi g ON (c.gifi_accno = g.accno)
  1156. $dpt_join
  1157. JOIN (SELECT id, approved FROM gl UNION
  1158. SELECT id, approved FROM ar UNION
  1159. SELECT id, approved FROM ap) gl
  1160. ON (ac.trans_id = gl.id)
  1161. WHERE ac.transdate < '$form->{fromdate}'
  1162. $dpt_where $project
  1163. AND ($approved OR ac.approved)
  1164. AND ($approved OR gl.approved)
  1165. GROUP BY g.accno, c.category, g.description,
  1166. c.contra|;
  1167. }
  1168. else {
  1169. $query = qq|
  1170. SELECT c.accno, c.category,
  1171. SUM(ac.amount) AS amount,
  1172. c.description, c.contra
  1173. FROM acc_trans ac
  1174. JOIN chart c ON (ac.chart_id = c.id)
  1175. $dpt_join
  1176. JOIN (SELECT id, approved FROM gl UNION
  1177. SELECT id, approved FROM ar UNION
  1178. SELECT id, approved FROM ap) gl
  1179. ON (ac.trans_id = gl.id)
  1180. WHERE ac.transdate < '$form->{fromdate}'
  1181. $dpt_where $project
  1182. AND ($approved OR ac.approved)
  1183. AND ($approved OR gl.approved)
  1184. GROUP BY c.accno, c.category, c.description,
  1185. c.contra|;
  1186. }
  1187. $sth = $dbh->prepare($query);
  1188. $sth->execute || $form->dberror($query);
  1189. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1190. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1191. $ref->{amount} = $form->round_amount( $ref->{amount}, 2 );
  1192. $balance{ $ref->{accno} } = $ref->{amount};
  1193. if ( $form->{all_accounts} ) {
  1194. $trb{ $ref->{accno} }{description} = $ref->{description};
  1195. $trb{ $ref->{accno} }{charttype} = 'A';
  1196. $trb{ $ref->{accno} }{category} = $ref->{category};
  1197. $trb{ $ref->{accno} }{contra} = $ref->{contra};
  1198. }
  1199. }
  1200. $sth->finish;
  1201. }
  1202. # get headings
  1203. $query = qq|
  1204. SELECT c.accno, c.description, c.category FROM chart c
  1205. WHERE c.charttype = 'H'
  1206. ORDER by c.accno|;
  1207. if ( $form->{accounttype} eq 'gifi' ) {
  1208. $query = qq|
  1209. SELECT g.accno, g.description, c.category, c.contra
  1210. FROM gifi g
  1211. JOIN chart c ON (c.gifi_accno = g.accno)
  1212. WHERE c.charttype = 'H'
  1213. ORDER BY g.accno|;
  1214. }
  1215. $sth = $dbh->prepare($query);
  1216. $sth->execute || $form->dberror($query);
  1217. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1218. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1219. $trb{ $ref->{accno} }{description} = $ref->{description};
  1220. $trb{ $ref->{accno} }{charttype} = 'H';
  1221. $trb{ $ref->{accno} }{category} = $ref->{category};
  1222. $trb{ $ref->{accno} }{contra} = $ref->{contra};
  1223. push @headingaccounts, $ref->{accno};
  1224. }
  1225. $sth->finish;
  1226. if ( $form->{fromdate} || $form->{todate} ) {
  1227. if ( $form->{fromdate} ) {
  1228. $where .=
  1229. " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} );
  1230. $invwhere .=
  1231. " AND a.transdate >= " . $dbh->quote( $form->{fromdate} );
  1232. }
  1233. if ( $form->{todate} ) {
  1234. $where .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} );
  1235. $invwhere .=
  1236. " AND a.transdate <= " . $dbh->quote( $form->{todate} );
  1237. }
  1238. }
  1239. if ( $form->{accounttype} eq 'gifi' ) {
  1240. $query = qq|
  1241. SELECT g.accno, g.description, c.category,
  1242. SUM(ac.amount) AS amount, c.contra
  1243. FROM acc_trans ac
  1244. JOIN chart c ON (c.id = ac.chart_id)
  1245. JOIN gifi g ON (c.gifi_accno = g.accno)
  1246. $dpt_join
  1247. JOIN (SELECT id, approved FROM gl UNION
  1248. SELECT id, approved FROM ar UNION
  1249. SELECT id, approved FROM ap) gl
  1250. ON (ac.trans_id = gl.id)
  1251. WHERE $where $dpt_where $project
  1252. AND ($approved OR ac.approved)
  1253. AND ($approved OR gl.approved)
  1254. GROUP BY g.accno, g.description, c.category, c.contra
  1255. ORDER BY accno|;
  1256. }
  1257. else {
  1258. $query = qq|
  1259. SELECT c.accno, c.description, c.category,
  1260. SUM(ac.amount) AS amount, c.contra
  1261. FROM acc_trans ac
  1262. JOIN chart c ON (c.id = ac.chart_id)
  1263. $dpt_join
  1264. JOIN (SELECT id, approved FROM gl UNION
  1265. SELECT id, approved FROM ar UNION
  1266. SELECT id, approved FROM ap) gl
  1267. ON (ac.trans_id = gl.id)
  1268. WHERE $where $dpt_where $project
  1269. AND ($approved OR ac.approved)
  1270. AND ($approved OR gl.approved)
  1271. GROUP BY c.accno, c.description, c.category, c.contra
  1272. ORDER BY accno|;
  1273. }
  1274. $sth = $dbh->prepare($query);
  1275. $sth->execute || $form->dberror($query);
  1276. # prepare query for each account
  1277. $query = qq|
  1278. SELECT (SELECT SUM(ac.amount) * -1 FROM acc_trans ac
  1279. JOIN chart c ON (c.id = ac.chart_id)
  1280. $dpt_join
  1281. JOIN (SELECT id, approved FROM gl UNION
  1282. SELECT id, approved FROM ar UNION
  1283. SELECT id, approved FROM ap) gl
  1284. ON (ac.trans_id = gl.id)
  1285. WHERE $where $dpt_where $project AND ac.amount < 0
  1286. AND ($approved OR ac.approved)
  1287. AND ($approved OR gl.approved)
  1288. AND c.accno = ?) AS debit,
  1289. (SELECT SUM(ac.amount) FROM acc_trans ac
  1290. JOIN chart c ON (c.id = ac.chart_id)
  1291. $dpt_join
  1292. JOIN (SELECT id, approved FROM gl UNION
  1293. SELECT id, approved FROM ar UNION
  1294. SELECT id, approved FROM ap) gl
  1295. ON (ac.trans_id = gl.id)
  1296. WHERE $where $dpt_where $project AND ac.amount > 0
  1297. AND ($approved OR ac.approved)
  1298. AND ($approved OR gl.approved)
  1299. AND c.accno = ?) AS credit |;
  1300. if ( $form->{accounttype} eq 'gifi' ) {
  1301. $query = qq|
  1302. SELECT (SELECT SUM(ac.amount) * -1
  1303. FROM acc_trans ac
  1304. JOIN chart c ON (c.id = ac.chart_id)
  1305. $dpt_join
  1306. WHERE $where $dpt_where $project AND ac.amount < 0
  1307. AND ($approved OR ac.approved)
  1308. AND c.gifi_accno = ?) AS debit,
  1309. (SELECT SUM(ac.amount)
  1310. FROM acc_trans ac
  1311. JOIN chart c ON (c.id = ac.chart_id)
  1312. $dpt_join
  1313. WHERE $where $dpt_where $project AND ac.amount > 0
  1314. AND ($approved OR ac.approved)
  1315. AND c.gifi_accno = ?) AS credit|;
  1316. }
  1317. $drcr = $dbh->prepare($query);
  1318. # calculate debit and credit for the period
  1319. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1320. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1321. $trb{ $ref->{accno} }{description} = $ref->{description};
  1322. $trb{ $ref->{accno} }{charttype} = 'A';
  1323. $trb{ $ref->{accno} }{category} = $ref->{category};
  1324. $trb{ $ref->{accno} }{contra} = $ref->{contra};
  1325. $trb{ $ref->{accno} }{amount} += $ref->{amount};
  1326. }
  1327. $sth->finish;
  1328. my ( $debit, $credit );
  1329. foreach my $accno ( sort keys %trb ) {
  1330. $ref = ();
  1331. $ref->{accno} = $accno;
  1332. for (qw(description category contra charttype amount)) {
  1333. $ref->{$_} = $trb{$accno}{$_};
  1334. }
  1335. $ref->{balance} = $balance{ $ref->{accno} };
  1336. if ( $trb{$accno}{charttype} eq 'A' ) {
  1337. if ($project_id) {
  1338. if ( $ref->{amount} < 0 ) {
  1339. $ref->{debit} = $ref->{amount} * -1;
  1340. }
  1341. else {
  1342. $ref->{credit} = $ref->{amount};
  1343. }
  1344. next if $form->round_amount( $ref->{amount}, 2 ) == 0;
  1345. }
  1346. else {
  1347. # get DR/CR
  1348. $drcr->execute( $ref->{accno}, $ref->{accno} )
  1349. || $form->dberror($query);
  1350. ( $debit, $credit ) = ( 0, 0 );
  1351. while ( my @drcrlist = $drcr->fetchrow_array ) {
  1352. $form->db_parse_numeric(sth=>$drcr, arrayref=>\@drcrlist);
  1353. ($debit, $credit) = @drcrlist;
  1354. $ref->{debit} += $debit;
  1355. $ref->{credit} += $credit;
  1356. }
  1357. $drcr->finish;
  1358. }
  1359. $ref->{debit} = $form->round_amount( $ref->{debit}, 2 );
  1360. $ref->{credit} = $form->round_amount( $ref->{credit}, 2 );
  1361. if ( !$form->{all_accounts} ) {
  1362. next
  1363. if $form->round_amount( $ref->{debit} + $ref->{credit}, 2 ) ==
  1364. 0;
  1365. }
  1366. }
  1367. # add subtotal
  1368. @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
  1369. $accno = pop @accno;
  1370. if ($accno) {
  1371. $trb{$accno}{debit} += $ref->{debit};
  1372. $trb{$accno}{credit} += $ref->{credit};
  1373. }
  1374. push @{ $form->{TB} }, $ref;
  1375. }
  1376. $dbh->commit;
  1377. # debits and credits for headings
  1378. foreach $accno (@headingaccounts) {
  1379. foreach $ref ( @{ $form->{TB} } ) {
  1380. if ( $accno eq $ref->{accno} ) {
  1381. $ref->{debit} = $trb{$accno}{debit};
  1382. $ref->{credit} = $trb{$accno}{credit};
  1383. }
  1384. }
  1385. }
  1386. }
  1387. sub aging {
  1388. my ( $self, $myconfig, $form ) = @_;
  1389. my $ref;
  1390. my $department_id;
  1391. my $null;
  1392. my $dbh = $form->{dbh};
  1393. my $invoice = ( $form->{arap} eq 'ar' ) ? 'is' : 'ir';
  1394. my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
  1395. ( $form->{currencies} ) = $dbh->selectrow_array($query);
  1396. ( $null, $form->{todate} ) = $form->from_to( $form->{year}, $form->{month} )
  1397. if $form->{year} && $form->{month};
  1398. if ( !$form->{todate} ) {
  1399. $query = qq|SELECT current_date|;
  1400. ( $form->{todate} ) = $dbh->selectrow_array($query);
  1401. }
  1402. my $transdate = ( $form->{overdue} ) ? "duedate" : "transdate";
  1403. if ( $form->{department} ) {
  1404. ( $null, $department_id ) = split /--/, $form->{department};
  1405. }
  1406. my $buysell = ( $form->{arap} eq 'ar' ) ? 'buy' : 'sell';
  1407. my $todate = $dbh->quote( $form->{todate} );
  1408. my %interval = (
  1409. 'c0' => "(date $todate - interval '0 days')",
  1410. 'c30' => "(date $todate - interval '30 days')",
  1411. 'c60' => "(date $todate - interval '60 days')",
  1412. 'c90' => "(date $todate - interval '90 days')"
  1413. );
  1414. # for each company that has some stuff outstanding
  1415. $form->{currencies} ||= ":";
  1416. $where = qq|a.paid != a.amount|;
  1417. if ( $form->{"$form->{ct}_id"} ) {
  1418. $where .= qq| AND c.entity_id = | . $dbh->quote( $form->{"$form->{ct}_id"} );
  1419. }
  1420. if ($department_id) {
  1421. $where .= qq| AND a.department_id = | . $dbh->quote($department_id);
  1422. }
  1423. $query = "";
  1424. my $union = "";
  1425. $query .= qq|
  1426. SELECT c.entity_id AS ctid,
  1427. c.meta_number as $form->{ct}number, e.legal_name as name,
  1428. '' as address1, '' as address2, '' as city,
  1429. '' as state,
  1430. '' as zipcode,
  1431. '' as country, '' as contact, '' as email,
  1432. '' as $form->{ct}phone,
  1433. '' as $form->{ct}fax,
  1434. '' as $form->{ct}taxnumber,
  1435. a.invnumber, a.transdate, a.till, a.ordnumber,
  1436. a.ponumber, a.notes, c.language_code,
  1437. CASE WHEN
  1438. EXTRACT(days FROM age(a.transdate)/30)
  1439. = 0
  1440. THEN (a.amount - a.paid) ELSE 0 END
  1441. as c0,
  1442. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  1443. = 1
  1444. THEN (a.amount - a.paid) ELSE 0 END
  1445. as c30,
  1446. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  1447. = 2
  1448. THEN (a.amount - a.paid) ELSE 0 END
  1449. as c60,
  1450. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  1451. > 2
  1452. THEN (a.amount - a.paid) ELSE 0 END
  1453. as c90,
  1454. a.duedate, a.invoice, a.id, a.curr,
  1455. (SELECT $buysell FROM exchangerate e
  1456. WHERE a.curr = e.curr
  1457. AND e.transdate = a.transdate)
  1458. AS exchangerate
  1459. FROM $form->{arap} a
  1460. JOIN entity_credit_account c USING (entity_id)
  1461. JOIN company e USING (entity_id)
  1462. WHERE $where|;
  1463. $query .= qq| ORDER BY ctid, curr, $transdate, invnumber|;
  1464. $sth = $dbh->prepare($query) || $form->dberror($query);
  1465. $sth->execute();
  1466. while ( $ref = $sth->fetchrow_hashref('NAME_lc') ) {
  1467. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1468. $ref->{module} =
  1469. ( $ref->{invoice} )
  1470. ? $invoice
  1471. : $form->{arap};
  1472. $ref->{module} = 'ps' if $ref->{till};
  1473. $ref->{exchangerate} = 1
  1474. unless $ref->{exchangerate};
  1475. push @{ $form->{AG} }, $ref;
  1476. }
  1477. $sth->finish;
  1478. # get language
  1479. my $query = qq|SELECT code, description FROM language ORDER BY 2|;
  1480. $sth = $dbh->prepare($query);
  1481. $sth->execute || $form->dberror($query);
  1482. while ( $ref = $sth->fetchrow_hashref('NAME_lc') ) {
  1483. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1484. push @{ $form->{all_language} }, $ref;
  1485. }
  1486. $sth->finish;
  1487. $dbh->commit;
  1488. }
  1489. sub get_customer {
  1490. my ( $self, $myconfig, $form ) = @_;
  1491. my $dbh = $form->{dbh};
  1492. my $query = qq|
  1493. SELECT name, email, cc, bcc FROM $form->{ct} ct
  1494. WHERE ct.id = ?|;
  1495. $sth = $dbh->prepare($query);
  1496. $sth->execute( $form->{"$form->{ct}_id"} );
  1497. ( $form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc} ) =
  1498. $sth->fetchrow_array();
  1499. $dbh->commit;
  1500. }
  1501. sub get_taxaccounts {
  1502. my ( $self, $myconfig, $form ) = @_;
  1503. my $dbh = $form->{dbh};
  1504. my $ARAP = uc $form->{db};
  1505. # get tax accounts
  1506. my $query = qq|
  1507. SELECT DISTINCT c.accno, c.description
  1508. FROM chart c
  1509. JOIN tax t ON (c.id = t.chart_id)
  1510. WHERE c.link LIKE '%${ARAP}_tax%'
  1511. ORDER BY c.accno|;
  1512. my $sth = $dbh->prepare($query);
  1513. $sth->execute || $form->dberror;
  1514. my $ref = ();
  1515. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1516. push @{ $form->{taxaccounts} }, $ref;
  1517. }
  1518. $sth->finish;
  1519. # get gifi tax accounts
  1520. my $query = qq|
  1521. SELECT DISTINCT g.accno, g.description
  1522. FROM gifi g
  1523. JOIN chart c ON (c.gifi_accno= g.accno)
  1524. JOIN tax t ON (c.id = t.chart_id)
  1525. WHERE c.link LIKE '%${ARAP}_tax%'
  1526. ORDER BY accno|;
  1527. my $sth = $dbh->prepare($query);
  1528. $sth->execute || $form->dberror;
  1529. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1530. push @{ $form->{gifi_taxaccounts} }, $ref;
  1531. }
  1532. $sth->finish;
  1533. $dbh->commit;
  1534. }
  1535. sub tax_report {
  1536. my ( $self, $myconfig, $form ) = @_;
  1537. my $dbh = $form->{dbh};
  1538. my ( $null, $department_id ) = split /--/, $form->{department};
  1539. # build WHERE
  1540. my $where = "1 = 1";
  1541. my $cashwhere = "";
  1542. if ($department_id) {
  1543. $where .= qq|AND a.department_id = | . $dbh->quote($department_id);
  1544. }
  1545. my $query;
  1546. my $sth;
  1547. my $accno;
  1548. if ( $form->{accno} ) {
  1549. if ( $form->{accno} =~ /^gifi_/ ) {
  1550. ( $null, $accno ) = split /_/, $form->{accno};
  1551. $accno = $dbh->quote($accno);
  1552. $accno = qq| AND ch.gifi_accno = $accno|;
  1553. }
  1554. else {
  1555. $accno = $dbh->quote( $form->{accno} );
  1556. $accno = qq| AND ch.accno = $accno|;
  1557. }
  1558. }
  1559. my $table;
  1560. my $ARAP;
  1561. if ( $form->{db} eq 'ar' ) {
  1562. $table = "customer";
  1563. $ARAP = "AR";
  1564. }
  1565. if ( $form->{db} eq 'ap' ) {
  1566. $table = "vendor";
  1567. $ARAP = "AP";
  1568. }
  1569. my $transdate = "a.transdate";
  1570. ( $form->{fromdate}, $form->{todate} ) =
  1571. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  1572. if $form->{year} && $form->{month};
  1573. # if there are any dates construct a where
  1574. if ( $form->{fromdate} || $form->{todate} ) {
  1575. if ( $form->{fromdate} ) {
  1576. $where .= " AND $transdate >= '$form->{fromdate}'";
  1577. }
  1578. if ( $form->{todate} ) {
  1579. $where .= " AND $transdate <= '$form->{todate}'";
  1580. }
  1581. }
  1582. if ( $form->{method} eq 'cash' ) {
  1583. $transdate = "a.datepaid";
  1584. my $todate = $form->{todate};
  1585. if ( !$todate ) {
  1586. ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
  1587. }
  1588. $cashwhere = qq|
  1589. AND ac.trans_id IN (
  1590. SELECT trans_id
  1591. FROM acc_trans
  1592. JOIN chart ON (chart_id = chart.id)
  1593. WHERE link LIKE '%${ARAP}_paid%'
  1594. AND $transdate <= | . $dbh->quote($todate) . qq|
  1595. AND a.paid = a.amount)|;
  1596. }
  1597. my $ml = ( $form->{db} eq 'ar' ) ? 1 : -1;
  1598. my %ordinal = ( 'transdate' => 3, 'invnumber' => 4, 'name' => 5 );
  1599. my @a = qw(transdate invnumber name);
  1600. my $sortorder = $form->sort_order( \@a, \%ordinal );
  1601. if ( $form->{summary} ) {
  1602. $query = qq|
  1603. SELECT a.id, a.invoice, $transdate AS transdate,
  1604. a.invnumber, c.legal_name AS name, a.netamount,
  1605. ac.amount * $ml AS tax, a.till
  1606. FROM acc_trans ac
  1607. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1608. JOIN chart ch ON (ch.id = ac.chart_id)
  1609. JOIN $table n ON (n.entity_id = a.entity_id)
  1610. JOIN company c ON (c.entity_id = n.entity_id)
  1611. WHERE $where $accno $cashwhere |;
  1612. if ( $form->{fromdate} ) {
  1613. # include open transactions from previous period
  1614. if ($cashwhere) {
  1615. $query .= qq|
  1616. UNION
  1617. SELECT a.id, a.invoice,
  1618. $transdate AS transdate, a.invnumber,
  1619. c.legal_name AS name, a.netamount, ac.
  1620. amount * $ml AS tax, a.till
  1621. FROM acc_trans ac
  1622. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1623. JOIN chart ch ON (ch.id = ac.chart_id)
  1624. JOIN $table n ON (n.entity_id = a.entity_id)
  1625. JOIN company c ON (c.entity_id = n.entity_id)
  1626. WHERE a.datepaid >= '$form->{fromdate}'
  1627. $accno $cashwhere|;
  1628. }
  1629. }
  1630. }
  1631. else {
  1632. $query = qq|
  1633. SELECT a.id, '0' AS invoice, $transdate AS transdate,
  1634. a.invnumber, c.legal_name AS name, a.netamount,
  1635. ac.amount * $ml AS tax, a.notes AS description,
  1636. a.till
  1637. FROM acc_trans ac
  1638. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1639. JOIN chart ch ON (ch.id = ac.chart_id)
  1640. JOIN $table n ON (n.entity_id = a.entity_id)
  1641. JOIN company c ON (c.entity_id = a.entity_id)
  1642. WHERE $where $accno AND a.invoice = '0' $cashwhere
  1643. UNION
  1644. SELECT a.id, '1' AS invoice, $transdate AS transdate,
  1645. a.invnumber, c.legal_name AS name,
  1646. i.sellprice * i.qty * $ml AS netamount,
  1647. i.sellprice * i.qty * $ml *
  1648. (SELECT tx.rate FROM tax tx
  1649. WHERE tx.chart_id = ch.id
  1650. AND (tx.validto > $transdate
  1651. OR tx.validto IS NULL)
  1652. ORDER BY validto LIMIT 1)
  1653. AS tax, i.description, a.till
  1654. FROM acc_trans ac
  1655. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1656. JOIN chart ch ON (ch.id = ac.chart_id)
  1657. JOIN $table n ON (n.entity_id = a.entity_id)
  1658. JOIN company c ON (c.entity_id = n.entity_id)
  1659. JOIN ${table}tax t
  1660. ON (t.${table}_id = n.id AND t.chart_id = ch.id)
  1661. JOIN invoice i ON (i.trans_id = a.id)
  1662. JOIN partstax pt
  1663. ON (pt.parts_id = i.parts_id
  1664. AND pt.chart_id = ch.id)
  1665. WHERE $where $accno AND a.invoice = '1' $cashwhere|;
  1666. if ( $form->{fromdate} ) {
  1667. if ($cashwhere) {
  1668. $query .= qq|
  1669. UNION
  1670. SELECT a.id, '0' AS invoice,
  1671. $transdate AS transdate,
  1672. a.invnumber, c.legal_name AS name,
  1673. a.netamount,
  1674. ac.amount * $ml AS tax,
  1675. a.notes AS description, a.till
  1676. FROM acc_trans ac
  1677. JOIN $form->{db} a
  1678. ON (a.id = ac.trans_id)
  1679. JOIN chart ch ON (ch.id = ac.chart_id)
  1680. JOIN $table n
  1681. ON (n.entity_id = a.entity_id)
  1682. JOIN company c
  1683. ON (c.entity_id = n.entity_id)
  1684. WHERE a.datepaid >= '$form->{fromdate}'
  1685. $accno AND a.invoice = '0'
  1686. $cashwhere
  1687. UNION
  1688. SELECT a.id, '1' AS invoice,
  1689. $transdate AS transdate,
  1690. a.invnumber,
  1691. c.legal_name AS name,
  1692. i.sellprice * i.qty * $ml
  1693. AS netamount, i.sellprice
  1694. * i.qty * $ml *
  1695. (SELECT tx.rate FROM tax tx
  1696. WHERE tx.chart_id = ch.id
  1697. AND
  1698. (tx.validto > $transdate
  1699. OR tx.validto IS NULL)
  1700. ORDER BY validto LIMIT 1)
  1701. AS tax, i.description, a.till
  1702. FROM acc_trans ac
  1703. JOIN $form->{db} a
  1704. ON (a.id = ac.trans_id)
  1705. JOIN chart ch ON (ch.id = ac.chart_id)
  1706. JOIN $table n ON
  1707. (n.entity_id = a.entity_id)
  1708. JOIN company c ON
  1709. (c.entity_id = n.entity_id)
  1710. JOIN ${table}tax t
  1711. ON (t.${table}_id = n.id
  1712. AND t.chart_id = ch.id)
  1713. JOIN invoice i ON (i.trans_id = a.id)
  1714. JOIN partstax pt
  1715. ON (pt.parts_id = i.parts_id
  1716. AND pt.chart_id = ch.id)
  1717. WHERE a.datepaid >= '$form->{fromdate}'
  1718. $accno AND a.invoice = '1'
  1719. $cashwhere|;
  1720. }
  1721. }
  1722. }
  1723. if ( $form->{report} =~ /nontaxable/ ) {
  1724. if ( $form->{summary} ) {
  1725. # only gather up non-taxable transactions
  1726. $query = qq|
  1727. SELECT DISTINCT a.id, a.invoice,
  1728. $transdate AS transdate, a.invnumber,
  1729. c.legal_name AS name, a.netamount, a.till
  1730. FROM acc_trans ac
  1731. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1732. JOIN $table n ON (n.entity_id = a.entity_id)
  1733. JOIN company c ON (c.entity_id = n.entity_id)
  1734. WHERE $where AND a.netamount = a.amount
  1735. $cashwhere|;
  1736. if ( $form->{fromdate} ) {
  1737. if ($cashwhere) {
  1738. $query .= qq|
  1739. UNION
  1740. SELECT DISTINCT a.id, a.invoice,
  1741. $transdate AS transdate,
  1742. a.invnumber,
  1743. c.legal_name AS name,
  1744. a.netamount, a.till
  1745. FROM acc_trans ac
  1746. JOIN $form->{db} a
  1747. ON (a.id = ac.trans_id)
  1748. JOIN $table n
  1749. ON (n.entity_id = a.entity_id)
  1750. JOIN company c
  1751. ON (c.entity_id = n.entity_id)
  1752. WHERE a.datepaid
  1753. >= '$form->{fromdate}'
  1754. AND
  1755. a.netamount = a.amount
  1756. $cashwhere|;
  1757. }
  1758. }
  1759. }
  1760. else {
  1761. # gather up details for non-taxable transactions
  1762. $query = qq|
  1763. SELECT a.id, '0' AS invoice,
  1764. $transdate AS transdate, a.invnumber,
  1765. c.legal_name AS name, a.netamount,
  1766. a.notes AS description, a.till
  1767. FROM acc_trans ac
  1768. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1769. JOIN $table n ON (n.entity_id = a.entity_id)
  1770. JOIN company c ON (c.entity_id = n.entity_id)
  1771. WHERE $where AND a.invoice = '0'
  1772. AND a.netamount = a.amount $cashwhere
  1773. GROUP BY a.id, $transdate, a.invnumber, name,
  1774. a.netamount, a.notes, a.till
  1775. UNION
  1776. SELECT a.id, '1' AS invoice,
  1777. $transdate AS transdate, a.invnumber,
  1778. c.legal_name AS name,
  1779. sum(ac.sellprice * ac.qty)
  1780. * $ml AS netamount, ac.description,
  1781. a.till
  1782. FROM invoice ac
  1783. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1784. JOIN $table n ON (n.entity_id = a.entity_id)
  1785. JOIN company c ON (c.entity_id = n.entity_id)
  1786. WHERE $where AND a.invoice = '1' AND
  1787. (a.entity_id NOT IN
  1788. (SELECT ${table}_id FROM ${table}tax t
  1789. (${table}_id)
  1790. ) OR ac.parts_id NOT IN
  1791. (SELECT parts_id FROM partstax p
  1792. (parts_id))) $cashwhere
  1793. GROUP BY a.id, a.invnumber, $transdate, name,
  1794. ac.description, a.till|;
  1795. if ( $form->{fromdate} ) {
  1796. if ($cashwhere) {
  1797. $query .= qq|
  1798. UNION
  1799. SELECT a.id, '0' AS invoice,
  1800. $transdate AS transdate,
  1801. a.invnumber,
  1802. c.legal_name AS name,
  1803. a.netamount,
  1804. a.notes AS description,
  1805. a.till
  1806. FROM acc_trans ac
  1807. JOIN $form->{db} a
  1808. ON (a.id = ac.trans_id)
  1809. JOIN $table n
  1810. ON (n.entity_id = a.entity_id)
  1811. JOIN company c
  1812. ON (c.entity_id = n.entity_id)
  1813. WHERE a.datepaid
  1814. >= '$form->{fromdate}'
  1815. AND a.invoice = '0'
  1816. AND a.netamount
  1817. = a.amount $cashwhere
  1818. GROUP BY a.id, $transdate,
  1819. a.invnumber, name,
  1820. a.netamount, a.notes,
  1821. a.till
  1822. UNION
  1823. SELECT a.id, '1' AS invoice,
  1824. $transdate AS transdate,
  1825. a.invnumber,
  1826. c.legal_name AS name,
  1827. sum(ac.sellprice
  1828. * ac.qty) * $ml
  1829. AS netamount,
  1830. ac.description, a.till
  1831. FROM invoice ac
  1832. JOIN $form->{db} a
  1833. ON (a.id = ac.trans_id)
  1834. JOIN $table n
  1835. ON (n.entity_id = a.entity_id)
  1836. JOIN company c
  1837. ON (c.entity_id = n.entity_id)
  1838. WHERE a.datepaid
  1839. >= '$form->{fromdate}'
  1840. AND a.invoice = '1' AND
  1841. (a.entity_id NOT IN
  1842. (SELECT ${table}_id
  1843. FROM ${table}tax t
  1844. (${table}_id)) OR
  1845. ac.parts_id NOT IN
  1846. (SELECT parts_id
  1847. FROM partstax p
  1848. (parts_id)))
  1849. $cashwhere
  1850. GROUP BY a.id, a.invnumber,
  1851. $transdate, name,
  1852. ac.description, a.till|;
  1853. }
  1854. }
  1855. }
  1856. }
  1857. $query .= qq| ORDER by $sortorder|;
  1858. $sth = $dbh->prepare($query);
  1859. $sth->execute || $form->dberror($query);
  1860. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1861. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1862. $ref->{tax} = $form->round_amount( $ref->{tax}, 2 );
  1863. if ( $form->{report} =~ /nontaxable/ ) {
  1864. push @{ $form->{TR} }, $ref if $ref->{netamount};
  1865. }
  1866. else {
  1867. push @{ $form->{TR} }, $ref if $ref->{tax};
  1868. }
  1869. }
  1870. $sth->finish;
  1871. $dbh->commit;
  1872. }
  1873. sub paymentaccounts {
  1874. my ( $self, $myconfig, $form ) = @_;
  1875. my $dbh = $form->{dbh};
  1876. my $ARAP = uc $form->{db};
  1877. # get A(R|P)_paid accounts
  1878. my $query = qq|
  1879. SELECT accno, description FROM chart
  1880. WHERE link LIKE '%${ARAP}_paid%'
  1881. ORDER BY accno|;
  1882. my $sth = $dbh->prepare($query);
  1883. $sth->execute || $form->dberror($query);
  1884. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1885. push @{ $form->{PR} }, $ref;
  1886. }
  1887. $sth->finish;
  1888. $form->all_years( $myconfig, $dbh );
  1889. $dbh->{dbh};
  1890. }
  1891. sub payments {
  1892. my ( $self, $myconfig, $form ) = @_;
  1893. my $dbh = $form->{dbh};
  1894. my $ml = 1;
  1895. if ( $form->{db} eq 'ar' ) {
  1896. $table = 'customer';
  1897. $account_class = 2;
  1898. $ml = -1;
  1899. }
  1900. if ( $form->{db} eq 'ap' ) {
  1901. $table = 'vendor';
  1902. $account_class = 1;
  1903. }
  1904. my $query;
  1905. my $sth;
  1906. my $dpt_join;
  1907. my $where;
  1908. my $var;
  1909. if ( $form->{department_id} ) {
  1910. $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id)|;
  1911. $where =
  1912. qq| AND t.department_id = | . $dbh->quote( $form->{department_id} );
  1913. }
  1914. ( $form->{fromdate}, $form->{todate} ) =
  1915. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  1916. if $form->{year} && $form->{month};
  1917. if ( $form->{fromdate} ) {
  1918. $where .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} );
  1919. }
  1920. if ($form->{meta_number} ) {
  1921. $where .= " AND c.meta_number = " . $dbh->quote($form->{meta_number});
  1922. }
  1923. if ( $form->{todate} ) {
  1924. $where .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} );
  1925. }
  1926. if ( !$form->{fx_transaction} ) {
  1927. $where .= " AND ac.fx_transaction = '0'";
  1928. }
  1929. if ( $form->{description} ne "" ) {
  1930. $var = $dbh->quote( $form->like( lc $form->{description} ) );
  1931. $where .= " AND lower(ce.name) LIKE $var";
  1932. }
  1933. if ( $form->{source} ne "" ) {
  1934. $var = $dbh->quote( $form->like( lc $form->{source} ) );
  1935. $where .= " AND lower(ac.source) LIKE $var";
  1936. }
  1937. if ( $form->{memo} ne "" ) {
  1938. $var = $dbh->quote( $form->like( lc $form->{memo} ) );
  1939. $where .= " AND lower(ac.memo) LIKE $var";
  1940. }
  1941. my %ordinal = (
  1942. 'name' => 1,
  1943. 'transdate' => 2,
  1944. 'source' => 4,
  1945. 'employee' => 6,
  1946. 'till' => 7
  1947. );
  1948. my @a = qw(name transdate employee);
  1949. my $sortorder = $form->sort_order( \@a, \%ordinal );
  1950. my $glwhere = $where;
  1951. $glwhere =~ s/\(c.name\)/\(g.description\)/;
  1952. # cycle through each id
  1953. foreach my $accno ( split( / /, $form->{paymentaccounts} ) ) {
  1954. $query = qq|
  1955. SELECT id, accno, description
  1956. FROM chart
  1957. WHERE accno = ?|;
  1958. $sth = $dbh->prepare($query);
  1959. $sth->execute($accno) || $form->dberror($query);
  1960. my $ref = $sth->fetchrow_hashref(NAME_lc);
  1961. push @{ $form->{PR} }, $ref;
  1962. $sth->finish;
  1963. $query = qq|
  1964. SELECT ce.name, ac.transdate,
  1965. sum(ac.amount) * $ml AS paid, ac.source,
  1966. ac.memo, ee.name AS employee, a.till, a.curr,
  1967. c.meta_number
  1968. FROM acc_trans ac
  1969. JOIN $form->{db} a ON (ac.trans_id = a.id)
  1970. JOIN entity_credit_account c ON
  1971. (c.id = a.entity_credit_account)
  1972. JOIN entity ce ON (ce.id = c.entity_id)
  1973. LEFT JOIN entity_employee e ON
  1974. (a.person_id = e.entity_id)
  1975. LEFT JOIN entity ee ON (e.entity_id = ee.id)
  1976. $dpt_join
  1977. WHERE ac.chart_id = $ref->{id}
  1978. AND ac.approved AND a.approved
  1979. $where|;
  1980. if ( $form->{till} ne "" ) {
  1981. $query .= " AND a.invoice = '1' AND NOT a.till IS NULL";
  1982. if ( $myconfig->{role} eq 'user' ) {
  1983. $query .= " AND e.login = '$form->{login}'";
  1984. }
  1985. }
  1986. $query .= qq|
  1987. GROUP BY ce.name, ac.transdate, ac.source, ac.memo,
  1988. ee.name, a.till, a.curr, c.meta_number|;
  1989. if ( $form->{till} eq "" && !$form->{meta_number}) {
  1990. $query .= qq|
  1991. UNION
  1992. SELECT g.description, ac.transdate,
  1993. sum(ac.amount) * $ml AS paid, ac.source,
  1994. ac.memo, ee.name AS employee, '' AS till,
  1995. '' AS curr, '' AS meta_number
  1996. FROM acc_trans ac
  1997. JOIN gl g ON (g.id = ac.trans_id)
  1998. LEFT
  1999. JOIN entity_employee e ON
  2000. (g.person_id = e.entity_id)
  2001. JOIN entity ee ON (e.entity_id = ee.id)
  2002. $dpt_join
  2003. WHERE ac.chart_id = $ref->{id} $glwhere
  2004. AND ac.approved AND g.approved
  2005. AND (ac.amount * $ml) > 0
  2006. GROUP BY g.description, ac.transdate,
  2007. ac.source, ac.memo, ee.name|;
  2008. }
  2009. $query .= qq| ORDER BY $sortorder|;
  2010. $sth = $dbh->prepare($query);
  2011. $sth->execute || $form->dberror($query);
  2012. while ( my $pr = $sth->fetchrow_hashref(NAME_lc) ) {
  2013. $form->db_parse_numeric(sth=>$sth, hashref=>$pr);
  2014. push @{ $form->{ $ref->{id} } }, $pr;
  2015. }
  2016. $sth->finish;
  2017. }
  2018. $dbh->commit;
  2019. }
  2020. 1;