summaryrefslogtreecommitdiff
path: root/LedgerSMB/RP.pm
blob: 40bc3d08ce7faa4d6a963a31d252a91ceebf74d1 (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.duedate, ap.duedate) >= "
  47. . $dbh->quote( $form->{fromdate} );
  48. }
  49. if ( $form->{todate} ) {
  50. $where .=
  51. "AND coalesce(ar.duedate, ap.duedate) < "
  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. $dpt_join
  915. WHERE $where $ywhere $dpt_where $category
  916. $project
  917. GROUP BY g.accno, g.description, c.category
  918. UNION ALL
  919. SELECT '' AS accno, SUM(ac.amount) AS amount,
  920. '' AS description, c.category
  921. FROM acc_trans ac
  922. JOIN chart c ON (c.id = ac.chart_id)
  923. $dpt_join
  924. WHERE $where $ywhere $dpt_where $category
  925. AND c.gifi_accno = '' $project
  926. GROUP BY c.category|;
  927. if ($excludeyearend) {
  928. $query .= qq|
  929. UNION ALL
  930. SELECT g.accno,
  931. sum(ac.amount)
  932. AS amount,
  933. g.description,
  934. c.category
  935. FROM yearend y
  936. JOIN gl a
  937. ON (a.id = y.trans_id)
  938. JOIN acc_trans ac
  939. ON (ac.trans_id =
  940. y.trans_id)
  941. JOIN chart c
  942. ON
  943. (c.id = ac.chart_id)
  944. JOIN gifi g
  945. ON (g.accno =
  946. c.gifi_accno)
  947. $dpt_join
  948. WHERE $yearendwhere
  949. AND c.category = 'Q'
  950. $dpt_where $project
  951. GROUP BY g.accno,
  952. g.description,
  953. c.category|;
  954. }
  955. }
  956. }
  957. else { # standard account
  958. if ( $form->{method} eq 'cash' ) {
  959. $query = qq|
  960. SELECT c.accno, sum(ac.amount) AS amount,
  961. c.description, c.category
  962. FROM acc_trans ac
  963. JOIN chart c ON (c.id = ac.chart_id)
  964. JOIN ar a ON (a.id = ac.trans_id) $dpt_join
  965. WHERE $where $ywhere $dpt_where $category
  966. AND ac.trans_id IN (
  967. SELECT trans_id FROM acc_trans
  968. JOIN chart ON (chart_id = id)
  969. WHERE link LIKE '%AR_paid%' $subwhere)
  970. $project
  971. GROUP BY c.accno, c.description, c.category
  972. UNION ALL
  973. SELECT c.accno, sum(ac.amount) AS amount,
  974. c.description, c.category
  975. FROM acc_trans ac
  976. JOIN chart c ON (c.id = ac.chart_id)
  977. JOIN ap a ON (a.id = ac.trans_id) $dpt_join
  978. WHERE $where $ywhere $dpt_where $category
  979. AND ac.trans_id IN (
  980. SELECT trans_id FROM acc_trans
  981. JOIN chart ON (chart_id = id)
  982. WHERE link LIKE '%AP_paid%' $subwhere)
  983. $project
  984. GROUP BY c.accno, c.description, c.category
  985. UNION ALL
  986. SELECT c.accno, sum(ac.amount) AS amount,
  987. c.description, c.category
  988. FROM acc_trans ac
  989. JOIN chart c ON (c.id = ac.chart_id)
  990. JOIN gl a ON (a.id = ac.trans_id) $dpt_join
  991. WHERE $where $ywhere $glwhere $dpt_where $category
  992. AND NOT (c.link = 'AR' OR c.link = 'AP')
  993. $project
  994. GROUP BY c.accno, c.description, c.category|;
  995. if ($excludeyearend) {
  996. # this is for the yearend
  997. $query .= qq|
  998. UNION ALL
  999. SELECT c.accno,
  1000. sum(ac.amount) AS amount,
  1001. c.description, c.category
  1002. FROM yearend y
  1003. JOIN gl a ON (a.id = y.trans_id)
  1004. JOIN acc_trans ac
  1005. ON (ac.trans_id = y.trans_id)
  1006. JOIN chart c
  1007. ON (c.id = ac.chart_id)
  1008. $dpt_join
  1009. WHERE $yearendwhere AND
  1010. c.category = 'Q' $dpt_where
  1011. $project
  1012. GROUP BY c.accno, c.description,
  1013. c.category|;
  1014. }
  1015. }
  1016. else {
  1017. if ($department_id) {
  1018. $dpt_join = qq|
  1019. JOIN dpt_trans t
  1020. ON (t.trans_id = ac.trans_id)|;
  1021. $dpt_where =
  1022. qq| AND t.department_id = | . $dbh->quote($department_id);
  1023. }
  1024. $query = qq|
  1025. SELECT c.accno, sum(ac.amount) AS amount,
  1026. c.description, c.category
  1027. FROM acc_trans ac
  1028. JOIN (SELECT id, approved FROM ar
  1029. UNION
  1030. SELECT id, approved FROM ap
  1031. UNION
  1032. SELECT id, approved FROM gl
  1033. ) g ON (ac.trans_id = g.id)
  1034. JOIN chart c ON (c.id = ac.chart_id)
  1035. $dpt_join
  1036. WHERE $where $ywhere $dpt_where $category
  1037. $project
  1038. AND ($approved IS NULL OR
  1039. $approved =
  1040. (ac.approved AND g.approved))
  1041. GROUP BY c.accno, c.description, c.category|;
  1042. if ($excludeyearend) {
  1043. $query .= qq|
  1044. UNION ALL
  1045. SELECT c.accno,
  1046. sum(ac.amount) AS amount,
  1047. c.description, c.category
  1048. FROM yearend y
  1049. JOIN gl a ON (a.id = y.trans_id)
  1050. JOIN acc_trans ac
  1051. ON (ac.trans_id = y.trans_id)
  1052. JOIN chart c
  1053. ON (c.id = ac.chart_id)
  1054. $dpt_join
  1055. WHERE $yearendwhere AND
  1056. c.category = 'Q' $dpt_where
  1057. $project
  1058. GROUP BY c.accno, c.description,
  1059. c.category|;
  1060. }
  1061. }
  1062. }
  1063. my @accno;
  1064. my $accno;
  1065. my $ref;
  1066. my $sth = $dbh->prepare($query);
  1067. $sth->execute || $form->dberror($query);
  1068. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1069. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1070. # get last heading account
  1071. @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
  1072. $accno = pop @accno;
  1073. if ( $accno && ( $accno ne $ref->{accno} ) ) {
  1074. if ($last_period) {
  1075. $form->{ $ref->{category} }{$accno}{last} += $ref->{amount};
  1076. }
  1077. else {
  1078. $form->{ $ref->{category} }{$accno}{this} += $ref->{amount};
  1079. }
  1080. }
  1081. $form->{ $ref->{category} }{ $ref->{accno} }{accno} = $ref->{accno};
  1082. $form->{ $ref->{category} }{ $ref->{accno} }{description} =
  1083. $ref->{description};
  1084. $form->{ $ref->{category} }{ $ref->{accno} }{charttype} = "A";
  1085. if ($last_period) {
  1086. $form->{ $ref->{category} }{ $ref->{accno} }{last} +=
  1087. $ref->{amount};
  1088. }
  1089. else {
  1090. $form->{ $ref->{category} }{ $ref->{accno} }{this} +=
  1091. $ref->{amount};
  1092. }
  1093. }
  1094. $sth->finish;
  1095. # remove accounts with zero balance
  1096. foreach $category ( @{$categories} ) {
  1097. foreach $accno ( keys %{ $form->{$category} } ) {
  1098. $form->{$category}{$accno}{last} =
  1099. $form->round_amount( $form->{$category}{$accno}{last},
  1100. $form->{decimalplaces} );
  1101. $form->{$category}{$accno}{this} =
  1102. $form->round_amount( $form->{$category}{$accno}{this},
  1103. $form->{decimalplaces} );
  1104. delete $form->{$category}{$accno}
  1105. if ( $form->{$category}{$accno}{this} == 0
  1106. && $form->{$category}{$accno}{last} == 0 );
  1107. }
  1108. }
  1109. }
  1110. sub trial_balance {
  1111. my ( $self, $myconfig, $form ) = @_;
  1112. my $dbh = $form->{dbh};
  1113. my $approved = ($form->{approved})? 'TRUE' : 'FALSE';
  1114. my ( $query, $sth, $ref );
  1115. my %balance = ();
  1116. my %trb = ();
  1117. my $null;
  1118. my $department_id;
  1119. my $project_id;
  1120. my @headingaccounts = ();
  1121. my $dpt_where;
  1122. my $dpt_join;
  1123. my $project;
  1124. my $where = "1 = 1";
  1125. my $invwhere = $where;
  1126. ( $null, $department_id ) = split /--/, $form->{department};
  1127. ( $null, $project_id ) = split /--/, $form->{projectnumber};
  1128. if ($department_id) {
  1129. $dpt_join = qq|
  1130. JOIN dpt_trans t ON (ac.trans_id = t.trans_id)|;
  1131. $dpt_where = qq|
  1132. AND t.department_id = | . $dbh->quote($department_id);
  1133. }
  1134. if ($project_id) {
  1135. $project = qq|
  1136. AND ac.project_id = | . $dbh->quote($project_id);
  1137. }
  1138. ( $form->{fromdate}, $form->{todate} ) =
  1139. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  1140. if $form->{year} && $form->{month};
  1141. # get beginning balances
  1142. if ( $form->{fromdate} ) {
  1143. if ( $form->{accounttype} eq 'gifi' ) {
  1144. $query = qq|
  1145. SELECT g.accno, c.category,
  1146. SUM(ac.amount) AS amount,
  1147. g.description, c.contra
  1148. FROM acc_trans ac
  1149. JOIN chart c ON (ac.chart_id = c.id)
  1150. JOIN gifi g ON (c.gifi_accno = g.accno)
  1151. $dpt_join
  1152. WHERE ac.transdate < '$form->{fromdate}'
  1153. $dpt_where $project
  1154. AND ($approved OR ac.approved)
  1155. GROUP BY g.accno, c.category, g.description,
  1156. c.contra|;
  1157. }
  1158. else {
  1159. $query = qq|
  1160. SELECT c.accno, c.category,
  1161. SUM(ac.amount) AS amount,
  1162. c.description, c.contra
  1163. FROM acc_trans ac
  1164. JOIN chart c ON (ac.chart_id = c.id)
  1165. $dpt_join
  1166. WHERE ac.transdate < '$form->{fromdate}'
  1167. $dpt_where $project
  1168. AND ($approved OR ac.approved)
  1169. GROUP BY c.accno, c.category, c.description,
  1170. c.contra|;
  1171. }
  1172. $sth = $dbh->prepare($query);
  1173. $sth->execute || $form->dberror($query);
  1174. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1175. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1176. $ref->{amount} = $form->round_amount( $ref->{amount}, 2 );
  1177. $balance{ $ref->{accno} } = $ref->{amount};
  1178. if ( $form->{all_accounts} ) {
  1179. $trb{ $ref->{accno} }{description} = $ref->{description};
  1180. $trb{ $ref->{accno} }{charttype} = 'A';
  1181. $trb{ $ref->{accno} }{category} = $ref->{category};
  1182. $trb{ $ref->{accno} }{contra} = $ref->{contra};
  1183. }
  1184. }
  1185. $sth->finish;
  1186. }
  1187. # get headings
  1188. $query = qq|
  1189. SELECT c.accno, c.description, c.category FROM chart c
  1190. WHERE c.charttype = 'H'
  1191. ORDER by c.accno|;
  1192. if ( $form->{accounttype} eq 'gifi' ) {
  1193. $query = qq|
  1194. SELECT g.accno, g.description, c.category, c.contra
  1195. FROM gifi g
  1196. JOIN chart c ON (c.gifi_accno = g.accno)
  1197. WHERE c.charttype = 'H'
  1198. ORDER BY g.accno|;
  1199. }
  1200. $sth = $dbh->prepare($query);
  1201. $sth->execute || $form->dberror($query);
  1202. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1203. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1204. $trb{ $ref->{accno} }{description} = $ref->{description};
  1205. $trb{ $ref->{accno} }{charttype} = 'H';
  1206. $trb{ $ref->{accno} }{category} = $ref->{category};
  1207. $trb{ $ref->{accno} }{contra} = $ref->{contra};
  1208. push @headingaccounts, $ref->{accno};
  1209. }
  1210. $sth->finish;
  1211. if ( $form->{fromdate} || $form->{todate} ) {
  1212. if ( $form->{fromdate} ) {
  1213. $where .=
  1214. " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} );
  1215. $invwhere .=
  1216. " AND a.transdate >= " . $dbh->quote( $form->{fromdate} );
  1217. }
  1218. if ( $form->{todate} ) {
  1219. $where .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} );
  1220. $invwhere .=
  1221. " AND a.transdate <= " . $dbh->quote( $form->{todate} );
  1222. }
  1223. }
  1224. if ( $form->{accounttype} eq 'gifi' ) {
  1225. $query = qq|
  1226. SELECT g.accno, g.description, c.category,
  1227. SUM(ac.amount) AS amount, c.contra
  1228. FROM acc_trans ac
  1229. JOIN chart c ON (c.id = ac.chart_id)
  1230. JOIN gifi g ON (c.gifi_accno = g.accno)
  1231. $dpt_join
  1232. WHERE $where $dpt_where $project
  1233. AND ($approved OR ac.approved)
  1234. GROUP BY g.accno, g.description, c.category, c.contra
  1235. ORDER BY accno|;
  1236. }
  1237. else {
  1238. $query = qq|
  1239. SELECT c.accno, c.description, c.category,
  1240. SUM(ac.amount) AS amount, c.contra
  1241. FROM acc_trans ac
  1242. JOIN chart c ON (c.id = ac.chart_id)
  1243. $dpt_join
  1244. WHERE $where $dpt_where $project
  1245. AND ($approved OR ac.approved)
  1246. GROUP BY c.accno, c.description, c.category, c.contra
  1247. ORDER BY accno|;
  1248. }
  1249. $sth = $dbh->prepare($query);
  1250. $sth->execute || $form->dberror($query);
  1251. # prepare query for each account
  1252. $query = qq|
  1253. SELECT (SELECT SUM(ac.amount) * -1 FROM acc_trans ac
  1254. JOIN chart c ON (c.id = ac.chart_id)
  1255. $dpt_join
  1256. WHERE $where $dpt_where $project AND ac.amount < 0
  1257. AND ($approved OR ac.approved)
  1258. AND c.accno = ?) AS debit,
  1259. (SELECT SUM(ac.amount) FROM acc_trans ac
  1260. JOIN chart c ON (c.id = ac.chart_id)
  1261. $dpt_join
  1262. WHERE $where $dpt_where $project AND ac.amount > 0
  1263. AND ($approved OR ac.approved)
  1264. AND c.accno = ?) AS credit |;
  1265. if ( $form->{accounttype} eq 'gifi' ) {
  1266. $query = qq|
  1267. SELECT (SELECT SUM(ac.amount) * -1
  1268. FROM acc_trans ac
  1269. JOIN chart c ON (c.id = ac.chart_id)
  1270. $dpt_join
  1271. WHERE $where $dpt_where $project AND ac.amount < 0
  1272. AND ($approved OR ac.approved)
  1273. AND c.gifi_accno = ?) AS debit,
  1274. (SELECT SUM(ac.amount)
  1275. FROM acc_trans ac
  1276. JOIN chart c ON (c.id = ac.chart_id)
  1277. $dpt_join
  1278. WHERE $where $dpt_where $project AND ac.amount > 0
  1279. AND ($approved OR ac.approved)
  1280. AND c.gifi_accno = ?) AS credit|;
  1281. }
  1282. $drcr = $dbh->prepare($query);
  1283. # calculate debit and credit for the period
  1284. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1285. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1286. $trb{ $ref->{accno} }{description} = $ref->{description};
  1287. $trb{ $ref->{accno} }{charttype} = 'A';
  1288. $trb{ $ref->{accno} }{category} = $ref->{category};
  1289. $trb{ $ref->{accno} }{contra} = $ref->{contra};
  1290. $trb{ $ref->{accno} }{amount} += $ref->{amount};
  1291. }
  1292. $sth->finish;
  1293. my ( $debit, $credit );
  1294. foreach my $accno ( sort keys %trb ) {
  1295. $ref = ();
  1296. $ref->{accno} = $accno;
  1297. for (qw(description category contra charttype amount)) {
  1298. $ref->{$_} = $trb{$accno}{$_};
  1299. }
  1300. $ref->{balance} = $balance{ $ref->{accno} };
  1301. if ( $trb{$accno}{charttype} eq 'A' ) {
  1302. if ($project_id) {
  1303. if ( $ref->{amount} < 0 ) {
  1304. $ref->{debit} = $ref->{amount} * -1;
  1305. }
  1306. else {
  1307. $ref->{credit} = $ref->{amount};
  1308. }
  1309. next if $form->round_amount( $ref->{amount}, 2 ) == 0;
  1310. }
  1311. else {
  1312. # get DR/CR
  1313. $drcr->execute( $ref->{accno}, $ref->{accno} )
  1314. || $form->dberror($query);
  1315. ( $debit, $credit ) = ( 0, 0 );
  1316. while ( my @drcrlist = $drcr->fetchrow_array ) {
  1317. $form->db_parse_numeric(sth=>$drcr, arrayref=>\@drcrlist);
  1318. ($debit, $credit) = @drcrlist;
  1319. $ref->{debit} += $debit;
  1320. $ref->{credit} += $credit;
  1321. }
  1322. $drcr->finish;
  1323. }
  1324. $ref->{debit} = $form->round_amount( $ref->{debit}, 2 );
  1325. $ref->{credit} = $form->round_amount( $ref->{credit}, 2 );
  1326. if ( !$form->{all_accounts} ) {
  1327. next
  1328. if $form->round_amount( $ref->{debit} + $ref->{credit}, 2 ) ==
  1329. 0;
  1330. }
  1331. }
  1332. # add subtotal
  1333. @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
  1334. $accno = pop @accno;
  1335. if ($accno) {
  1336. $trb{$accno}{debit} += $ref->{debit};
  1337. $trb{$accno}{credit} += $ref->{credit};
  1338. }
  1339. push @{ $form->{TB} }, $ref;
  1340. }
  1341. $dbh->commit;
  1342. # debits and credits for headings
  1343. foreach $accno (@headingaccounts) {
  1344. foreach $ref ( @{ $form->{TB} } ) {
  1345. if ( $accno eq $ref->{accno} ) {
  1346. $ref->{debit} = $trb{$accno}{debit};
  1347. $ref->{credit} = $trb{$accno}{credit};
  1348. }
  1349. }
  1350. }
  1351. }
  1352. sub aging {
  1353. my ( $self, $myconfig, $form ) = @_;
  1354. my $ref;
  1355. my $department_id;
  1356. my $null;
  1357. my $dbh = $form->{dbh};
  1358. my $invoice = ( $form->{arap} eq 'ar' ) ? 'is' : 'ir';
  1359. my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
  1360. ( $form->{currencies} ) = $dbh->selectrow_array($query);
  1361. ( $null, $form->{todate} ) = $form->from_to( $form->{year}, $form->{month} )
  1362. if $form->{year} && $form->{month};
  1363. if ( !$form->{todate} ) {
  1364. $query = qq|SELECT current_date|;
  1365. ( $form->{todate} ) = $dbh->selectrow_array($query);
  1366. }
  1367. my $transdate = ( $form->{overdue} ) ? "duedate" : "transdate";
  1368. if ( $form->{department} ) {
  1369. ( $null, $department_id ) = split /--/, $form->{department};
  1370. }
  1371. my $buysell = ( $form->{arap} eq 'ar' ) ? 'buy' : 'sell';
  1372. my $todate = $dbh->quote( $form->{todate} );
  1373. my %interval = (
  1374. 'c0' => "(date $todate - interval '0 days')",
  1375. 'c30' => "(date $todate - interval '30 days')",
  1376. 'c60' => "(date $todate - interval '60 days')",
  1377. 'c90' => "(date $todate - interval '90 days')"
  1378. );
  1379. # for each company that has some stuff outstanding
  1380. $form->{currencies} ||= ":";
  1381. $where = qq|a.paid != a.amount|;
  1382. if ( $form->{"$form->{ct}_id"} ) {
  1383. $where .= qq| AND c.entity_id = | . $dbh->quote( $form->{"$form->{ct}_id"} );
  1384. }
  1385. if ($department_id) {
  1386. $where .= qq| AND a.department_id = | . $dbh->quote($department_id);
  1387. }
  1388. $query = "";
  1389. my $union = "";
  1390. $query .= qq|
  1391. SELECT c.entity_id AS ctid,
  1392. c.meta_number as $form->{ct}number, e.legal_name as name,
  1393. '' as address1, '' as address2, '' as city,
  1394. '' as state,
  1395. '' as zipcode,
  1396. '' as country, '' as contact, '' as email,
  1397. '' as $form->{ct}phone,
  1398. '' as $form->{ct}fax,
  1399. '' as $form->{ct}taxnumber,
  1400. a.invnumber, a.transdate, a.till, a.ordnumber,
  1401. a.ponumber, a.notes, c.language_code,
  1402. CASE WHEN
  1403. EXTRACT(days FROM age(a.transdate)/30)
  1404. = 0
  1405. THEN (a.amount - a.paid) ELSE 0 END
  1406. as c0,
  1407. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  1408. = 1
  1409. THEN (a.amount - a.paid) ELSE 0 END
  1410. as c30,
  1411. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  1412. = 2
  1413. THEN (a.amount - a.paid) ELSE 0 END
  1414. as c60,
  1415. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  1416. > 2
  1417. THEN (a.amount - a.paid) ELSE 0 END
  1418. as c90,
  1419. a.duedate, a.invoice, a.id, a.curr,
  1420. (SELECT $buysell FROM exchangerate e
  1421. WHERE a.curr = e.curr
  1422. AND e.transdate = a.transdate)
  1423. AS exchangerate
  1424. FROM $form->{arap} a
  1425. JOIN entity_credit_account c USING (entity_id)
  1426. JOIN company e USING (entity_id)
  1427. WHERE $where|;
  1428. $query .= qq| ORDER BY ctid, curr, $transdate, invnumber|;
  1429. $sth = $dbh->prepare($query) || $form->dberror($query);
  1430. $sth->execute();
  1431. while ( $ref = $sth->fetchrow_hashref('NAME_lc') ) {
  1432. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1433. $ref->{module} =
  1434. ( $ref->{invoice} )
  1435. ? $invoice
  1436. : $form->{arap};
  1437. $ref->{module} = 'ps' if $ref->{till};
  1438. $ref->{exchangerate} = 1
  1439. unless $ref->{exchangerate};
  1440. push @{ $form->{AG} }, $ref;
  1441. }
  1442. $sth->finish;
  1443. # get language
  1444. my $query = qq|SELECT code, description FROM language ORDER BY 2|;
  1445. $sth = $dbh->prepare($query);
  1446. $sth->execute || $form->dberror($query);
  1447. while ( $ref = $sth->fetchrow_hashref('NAME_lc') ) {
  1448. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1449. push @{ $form->{all_language} }, $ref;
  1450. }
  1451. $sth->finish;
  1452. $dbh->commit;
  1453. }
  1454. sub get_customer {
  1455. my ( $self, $myconfig, $form ) = @_;
  1456. my $dbh = $form->{dbh};
  1457. my $query = qq|
  1458. SELECT name, email, cc, bcc FROM $form->{ct} ct
  1459. WHERE ct.id = ?|;
  1460. $sth = $dbh->prepare($query);
  1461. $sth->execute( $form->{"$form->{ct}_id"} );
  1462. ( $form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc} ) =
  1463. $sth->fetchrow_array();
  1464. $dbh->commit;
  1465. }
  1466. sub get_taxaccounts {
  1467. my ( $self, $myconfig, $form ) = @_;
  1468. my $dbh = $form->{dbh};
  1469. my $ARAP = uc $form->{db};
  1470. # get tax accounts
  1471. my $query = qq|
  1472. SELECT DISTINCT c.accno, c.description
  1473. FROM chart c
  1474. JOIN tax t ON (c.id = t.chart_id)
  1475. WHERE c.link LIKE '%${ARAP}_tax%'
  1476. ORDER BY c.accno|;
  1477. my $sth = $dbh->prepare($query);
  1478. $sth->execute || $form->dberror;
  1479. my $ref = ();
  1480. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1481. push @{ $form->{taxaccounts} }, $ref;
  1482. }
  1483. $sth->finish;
  1484. # get gifi tax accounts
  1485. my $query = qq|
  1486. SELECT DISTINCT g.accno, g.description
  1487. FROM gifi g
  1488. JOIN chart c ON (c.gifi_accno= g.accno)
  1489. JOIN tax t ON (c.id = t.chart_id)
  1490. WHERE c.link LIKE '%${ARAP}_tax%'
  1491. ORDER BY accno|;
  1492. my $sth = $dbh->prepare($query);
  1493. $sth->execute || $form->dberror;
  1494. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1495. push @{ $form->{gifi_taxaccounts} }, $ref;
  1496. }
  1497. $sth->finish;
  1498. $dbh->commit;
  1499. }
  1500. sub tax_report {
  1501. my ( $self, $myconfig, $form ) = @_;
  1502. my $dbh = $form->{dbh};
  1503. my ( $null, $department_id ) = split /--/, $form->{department};
  1504. # build WHERE
  1505. my $where = "1 = 1";
  1506. my $cashwhere = "";
  1507. if ($department_id) {
  1508. $where .= qq|AND a.department_id = | . $dbh->quote($department_id);
  1509. }
  1510. my $query;
  1511. my $sth;
  1512. my $accno;
  1513. if ( $form->{accno} ) {
  1514. if ( $form->{accno} =~ /^gifi_/ ) {
  1515. ( $null, $accno ) = split /_/, $form->{accno};
  1516. $accno = $dbh->quote($accno);
  1517. $accno = qq| AND ch.gifi_accno = $accno|;
  1518. }
  1519. else {
  1520. $accno = $dbh->quote( $form->{accno} );
  1521. $accno = qq| AND ch.accno = $accno|;
  1522. }
  1523. }
  1524. my $table;
  1525. my $ARAP;
  1526. if ( $form->{db} eq 'ar' ) {
  1527. $table = "customer";
  1528. $ARAP = "AR";
  1529. }
  1530. if ( $form->{db} eq 'ap' ) {
  1531. $table = "vendor";
  1532. $ARAP = "AP";
  1533. }
  1534. my $transdate = "a.transdate";
  1535. ( $form->{fromdate}, $form->{todate} ) =
  1536. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  1537. if $form->{year} && $form->{month};
  1538. # if there are any dates construct a where
  1539. if ( $form->{fromdate} || $form->{todate} ) {
  1540. if ( $form->{fromdate} ) {
  1541. $where .= " AND $transdate >= '$form->{fromdate}'";
  1542. }
  1543. if ( $form->{todate} ) {
  1544. $where .= " AND $transdate <= '$form->{todate}'";
  1545. }
  1546. }
  1547. if ( $form->{method} eq 'cash' ) {
  1548. $transdate = "a.datepaid";
  1549. my $todate = $form->{todate};
  1550. if ( !$todate ) {
  1551. ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
  1552. }
  1553. $cashwhere = qq|
  1554. AND ac.trans_id IN (
  1555. SELECT trans_id
  1556. FROM acc_trans
  1557. JOIN chart ON (chart_id = chart.id)
  1558. WHERE link LIKE '%${ARAP}_paid%'
  1559. AND $transdate <= | . $dbh->quote($todate) . qq|
  1560. AND a.paid = a.amount)|;
  1561. }
  1562. my $ml = ( $form->{db} eq 'ar' ) ? 1 : -1;
  1563. my %ordinal = ( 'transdate' => 3, 'invnumber' => 4, 'name' => 5 );
  1564. my @a = qw(transdate invnumber name);
  1565. my $sortorder = $form->sort_order( \@a, \%ordinal );
  1566. if ( $form->{summary} ) {
  1567. $query = qq|
  1568. SELECT a.id, a.invoice, $transdate AS transdate,
  1569. a.invnumber, c.legal_name AS name, a.netamount,
  1570. ac.amount * $ml AS tax, a.till
  1571. FROM acc_trans ac
  1572. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1573. JOIN chart ch ON (ch.id = ac.chart_id)
  1574. JOIN $table n ON (n.entity_id = a.entity_id)
  1575. JOIN company c ON (c.entity_id = n.entity_id)
  1576. WHERE $where $accno $cashwhere |;
  1577. if ( $form->{fromdate} ) {
  1578. # include open transactions from previous period
  1579. if ($cashwhere) {
  1580. $query .= qq|
  1581. UNION
  1582. SELECT a.id, a.invoice,
  1583. $transdate AS transdate, a.invnumber,
  1584. c.legal_name AS name, a.netamount, ac.
  1585. amount * $ml AS tax, a.till
  1586. FROM acc_trans ac
  1587. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1588. JOIN chart ch ON (ch.id = ac.chart_id)
  1589. JOIN $table n ON (n.entity_id = a.entity_id)
  1590. JOIN company c ON (c.entity_id = n.entity_id)
  1591. WHERE a.datepaid >= '$form->{fromdate}'
  1592. $accno $cashwhere|;
  1593. }
  1594. }
  1595. }
  1596. else {
  1597. $query = qq|
  1598. SELECT a.id, '0' AS invoice, $transdate AS transdate,
  1599. a.invnumber, c.legal_name AS name, a.netamount,
  1600. ac.amount * $ml AS tax, a.notes AS description,
  1601. a.till
  1602. FROM acc_trans ac
  1603. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1604. JOIN chart ch ON (ch.id = ac.chart_id)
  1605. JOIN $table n ON (n.entity_id = a.entity_id)
  1606. JOIN company c ON (c.entity_id = a.entity_id)
  1607. WHERE $where $accno AND a.invoice = '0' $cashwhere
  1608. UNION
  1609. SELECT a.id, '1' AS invoice, $transdate AS transdate,
  1610. a.invnumber, c.legal_name AS name,
  1611. i.sellprice * i.qty * $ml AS netamount,
  1612. i.sellprice * i.qty * $ml *
  1613. (SELECT tx.rate FROM tax tx
  1614. WHERE tx.chart_id = ch.id
  1615. AND (tx.validto > $transdate
  1616. OR tx.validto IS NULL)
  1617. ORDER BY validto LIMIT 1)
  1618. AS tax, i.description, a.till
  1619. FROM acc_trans ac
  1620. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1621. JOIN chart ch ON (ch.id = ac.chart_id)
  1622. JOIN $table n ON (n.entity_id = a.entity_id)
  1623. JOIN company c ON (c.entity_id = n.entity_id)
  1624. JOIN ${table}tax t
  1625. ON (t.${table}_id = n.id AND t.chart_id = ch.id)
  1626. JOIN invoice i ON (i.trans_id = a.id)
  1627. JOIN partstax pt
  1628. ON (pt.parts_id = i.parts_id
  1629. AND pt.chart_id = ch.id)
  1630. WHERE $where $accno AND a.invoice = '1' $cashwhere|;
  1631. if ( $form->{fromdate} ) {
  1632. if ($cashwhere) {
  1633. $query .= qq|
  1634. UNION
  1635. SELECT a.id, '0' AS invoice,
  1636. $transdate AS transdate,
  1637. a.invnumber, c.legal_name AS name,
  1638. a.netamount,
  1639. ac.amount * $ml AS tax,
  1640. a.notes AS description, a.till
  1641. FROM acc_trans ac
  1642. JOIN $form->{db} a
  1643. ON (a.id = ac.trans_id)
  1644. JOIN chart ch ON (ch.id = ac.chart_id)
  1645. JOIN $table n
  1646. ON (n.entity_id = a.entity_id)
  1647. JOIN company c
  1648. ON (c.entity_id = n.entity_id)
  1649. WHERE a.datepaid >= '$form->{fromdate}'
  1650. $accno AND a.invoice = '0'
  1651. $cashwhere
  1652. UNION
  1653. SELECT a.id, '1' AS invoice,
  1654. $transdate AS transdate,
  1655. a.invnumber,
  1656. c.legal_name AS name,
  1657. i.sellprice * i.qty * $ml
  1658. AS netamount, i.sellprice
  1659. * i.qty * $ml *
  1660. (SELECT tx.rate FROM tax tx
  1661. WHERE tx.chart_id = ch.id
  1662. AND
  1663. (tx.validto > $transdate
  1664. OR tx.validto IS NULL)
  1665. ORDER BY validto LIMIT 1)
  1666. AS tax, i.description, a.till
  1667. FROM acc_trans ac
  1668. JOIN $form->{db} a
  1669. ON (a.id = ac.trans_id)
  1670. JOIN chart ch ON (ch.id = ac.chart_id)
  1671. JOIN $table n ON
  1672. (n.entity_id = a.entity_id)
  1673. JOIN company c ON
  1674. (c.entity_id = n.entity_id)
  1675. JOIN ${table}tax t
  1676. ON (t.${table}_id = n.id
  1677. AND t.chart_id = ch.id)
  1678. JOIN invoice i ON (i.trans_id = a.id)
  1679. JOIN partstax pt
  1680. ON (pt.parts_id = i.parts_id
  1681. AND pt.chart_id = ch.id)
  1682. WHERE a.datepaid >= '$form->{fromdate}'
  1683. $accno AND a.invoice = '1'
  1684. $cashwhere|;
  1685. }
  1686. }
  1687. }
  1688. if ( $form->{report} =~ /nontaxable/ ) {
  1689. if ( $form->{summary} ) {
  1690. # only gather up non-taxable transactions
  1691. $query = qq|
  1692. SELECT DISTINCT a.id, a.invoice,
  1693. $transdate AS transdate, a.invnumber,
  1694. c.legal_name AS name, a.netamount, a.till
  1695. FROM acc_trans ac
  1696. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1697. JOIN $table n ON (n.entity_id = a.entity_id)
  1698. JOIN company c ON (c.entity_id = n.entity_id)
  1699. WHERE $where AND a.netamount = a.amount
  1700. $cashwhere|;
  1701. if ( $form->{fromdate} ) {
  1702. if ($cashwhere) {
  1703. $query .= qq|
  1704. UNION
  1705. SELECT DISTINCT a.id, a.invoice,
  1706. $transdate AS transdate,
  1707. a.invnumber,
  1708. c.legal_name AS name,
  1709. a.netamount, a.till
  1710. FROM acc_trans ac
  1711. JOIN $form->{db} a
  1712. ON (a.id = ac.trans_id)
  1713. JOIN $table n
  1714. ON (n.entity_id = a.entity_id)
  1715. JOIN company c
  1716. ON (c.entity_id = n.entity_id)
  1717. WHERE a.datepaid
  1718. >= '$form->{fromdate}'
  1719. AND
  1720. a.netamount = a.amount
  1721. $cashwhere|;
  1722. }
  1723. }
  1724. }
  1725. else {
  1726. # gather up details for non-taxable transactions
  1727. $query = qq|
  1728. SELECT a.id, '0' AS invoice,
  1729. $transdate AS transdate, a.invnumber,
  1730. c.legal_name AS name, a.netamount,
  1731. a.notes AS description, a.till
  1732. FROM acc_trans ac
  1733. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1734. JOIN $table n ON (n.entity_id = a.entity_id)
  1735. JOIN company c ON (c.entity_id = n.entity_id)
  1736. WHERE $where AND a.invoice = '0'
  1737. AND a.netamount = a.amount $cashwhere
  1738. GROUP BY a.id, $transdate, a.invnumber, name,
  1739. a.netamount, a.notes, a.till
  1740. UNION
  1741. SELECT a.id, '1' AS invoice,
  1742. $transdate AS transdate, a.invnumber,
  1743. c.legal_name AS name,
  1744. sum(ac.sellprice * ac.qty)
  1745. * $ml AS netamount, ac.description,
  1746. a.till
  1747. FROM invoice ac
  1748. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1749. JOIN $table n ON (n.entity_id = a.entity_id)
  1750. JOIN company c ON (c.entity_id = n.entity_id)
  1751. WHERE $where AND a.invoice = '1' AND
  1752. (a.entity_id NOT IN
  1753. (SELECT ${table}_id FROM ${table}tax t
  1754. (${table}_id)
  1755. ) OR ac.parts_id NOT IN
  1756. (SELECT parts_id FROM partstax p
  1757. (parts_id))) $cashwhere
  1758. GROUP BY a.id, a.invnumber, $transdate, name,
  1759. ac.description, a.till|;
  1760. if ( $form->{fromdate} ) {
  1761. if ($cashwhere) {
  1762. $query .= qq|
  1763. UNION
  1764. SELECT a.id, '0' AS invoice,
  1765. $transdate AS transdate,
  1766. a.invnumber,
  1767. c.legal_name AS name,
  1768. a.netamount,
  1769. a.notes AS description,
  1770. a.till
  1771. FROM acc_trans ac
  1772. JOIN $form->{db} a
  1773. ON (a.id = ac.trans_id)
  1774. JOIN $table n
  1775. ON (n.entity_id = a.entity_id)
  1776. JOIN company c
  1777. ON (c.entity_id = n.entity_id)
  1778. WHERE a.datepaid
  1779. >= '$form->{fromdate}'
  1780. AND a.invoice = '0'
  1781. AND a.netamount
  1782. = a.amount $cashwhere
  1783. GROUP BY a.id, $transdate,
  1784. a.invnumber, name,
  1785. a.netamount, a.notes,
  1786. a.till
  1787. UNION
  1788. SELECT a.id, '1' AS invoice,
  1789. $transdate AS transdate,
  1790. a.invnumber,
  1791. c.legal_name AS name,
  1792. sum(ac.sellprice
  1793. * ac.qty) * $ml
  1794. AS netamount,
  1795. ac.description, a.till
  1796. FROM invoice ac
  1797. JOIN $form->{db} a
  1798. ON (a.id = ac.trans_id)
  1799. JOIN $table n
  1800. ON (n.entity_id = a.entity_id)
  1801. JOIN company c
  1802. ON (c.entity_id = n.entity_id)
  1803. WHERE a.datepaid
  1804. >= '$form->{fromdate}'
  1805. AND a.invoice = '1' AND
  1806. (a.entity_id NOT IN
  1807. (SELECT ${table}_id
  1808. FROM ${table}tax t
  1809. (${table}_id)) OR
  1810. ac.parts_id NOT IN
  1811. (SELECT parts_id
  1812. FROM partstax p
  1813. (parts_id)))
  1814. $cashwhere
  1815. GROUP BY a.id, a.invnumber,
  1816. $transdate, name,
  1817. ac.description, a.till|;
  1818. }
  1819. }
  1820. }
  1821. }
  1822. $query .= qq| ORDER by $sortorder|;
  1823. $sth = $dbh->prepare($query);
  1824. $sth->execute || $form->dberror($query);
  1825. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1826. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1827. $ref->{tax} = $form->round_amount( $ref->{tax}, 2 );
  1828. if ( $form->{report} =~ /nontaxable/ ) {
  1829. push @{ $form->{TR} }, $ref if $ref->{netamount};
  1830. }
  1831. else {
  1832. push @{ $form->{TR} }, $ref if $ref->{tax};
  1833. }
  1834. }
  1835. $sth->finish;
  1836. $dbh->commit;
  1837. }
  1838. sub paymentaccounts {
  1839. my ( $self, $myconfig, $form ) = @_;
  1840. my $dbh = $form->{dbh};
  1841. my $ARAP = uc $form->{db};
  1842. # get A(R|P)_paid accounts
  1843. my $query = qq|
  1844. SELECT accno, description FROM chart
  1845. WHERE link LIKE '%${ARAP}_paid%'
  1846. ORDER BY accno|;
  1847. my $sth = $dbh->prepare($query);
  1848. $sth->execute || $form->dberror($query);
  1849. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1850. push @{ $form->{PR} }, $ref;
  1851. }
  1852. $sth->finish;
  1853. $form->all_years( $myconfig, $dbh );
  1854. $dbh->{dbh};
  1855. }
  1856. sub payments {
  1857. my ( $self, $myconfig, $form ) = @_;
  1858. my $dbh = $form->{dbh};
  1859. my $ml = 1;
  1860. if ( $form->{db} eq 'ar' ) {
  1861. $table = 'customer';
  1862. $account_class = 2;
  1863. $ml = -1;
  1864. }
  1865. if ( $form->{db} eq 'ap' ) {
  1866. $table = 'vendor';
  1867. $account_class = 1;
  1868. }
  1869. my $query;
  1870. my $sth;
  1871. my $dpt_join;
  1872. my $where;
  1873. my $var;
  1874. if ( $form->{department_id} ) {
  1875. $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id)|;
  1876. $where =
  1877. qq| AND t.department_id = | . $dbh->quote( $form->{department_id} );
  1878. }
  1879. ( $form->{fromdate}, $form->{todate} ) =
  1880. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  1881. if $form->{year} && $form->{month};
  1882. if ( $form->{fromdate} ) {
  1883. $where .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} );
  1884. }
  1885. if ($form->{meta_number} ) {
  1886. $where .= " AND c.meta_number = " . $dbh->quote($form->{meta_number});
  1887. }
  1888. if ( $form->{todate} ) {
  1889. $where .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} );
  1890. }
  1891. if ( !$form->{fx_transaction} ) {
  1892. $where .= " AND ac.fx_transaction = '0'";
  1893. }
  1894. if ( $form->{description} ne "" ) {
  1895. $var = $dbh->quote( $form->like( lc $form->{description} ) );
  1896. $where .= " AND lower(ce.name) LIKE $var";
  1897. }
  1898. if ( $form->{source} ne "" ) {
  1899. $var = $dbh->quote( $form->like( lc $form->{source} ) );
  1900. $where .= " AND lower(ac.source) LIKE $var";
  1901. }
  1902. if ( $form->{memo} ne "" ) {
  1903. $var = $dbh->quote( $form->like( lc $form->{memo} ) );
  1904. $where .= " AND lower(ac.memo) LIKE $var";
  1905. }
  1906. my %ordinal = (
  1907. 'name' => 1,
  1908. 'transdate' => 2,
  1909. 'source' => 4,
  1910. 'employee' => 6,
  1911. 'till' => 7
  1912. );
  1913. my @a = qw(name transdate employee);
  1914. my $sortorder = $form->sort_order( \@a, \%ordinal );
  1915. my $glwhere = $where;
  1916. $glwhere =~ s/\(c.name\)/\(g.description\)/;
  1917. # cycle through each id
  1918. foreach my $accno ( split( / /, $form->{paymentaccounts} ) ) {
  1919. $query = qq|
  1920. SELECT id, accno, description
  1921. FROM chart
  1922. WHERE accno = ?|;
  1923. $sth = $dbh->prepare($query);
  1924. $sth->execute($accno) || $form->dberror($query);
  1925. my $ref = $sth->fetchrow_hashref(NAME_lc);
  1926. push @{ $form->{PR} }, $ref;
  1927. $sth->finish;
  1928. $query = qq|
  1929. SELECT ce.name, ac.transdate,
  1930. sum(ac.amount) * $ml AS paid, ac.source,
  1931. ac.memo, ee.name AS employee, a.till, a.curr,
  1932. c.meta_number
  1933. FROM acc_trans ac
  1934. JOIN $form->{db} a ON (ac.trans_id = a.id)
  1935. JOIN entity_credit_account c ON
  1936. (c.id = a.entity_credit_account)
  1937. JOIN entity ce ON (ce.id = c.entity_id)
  1938. LEFT JOIN entity_employee e ON
  1939. (a.person_id = e.entity_id)
  1940. LEFT JOIN entity ee ON (e.entity_id = ee.id)
  1941. $dpt_join
  1942. WHERE ac.chart_id = $ref->{id} $where|;
  1943. if ( $form->{till} ne "" ) {
  1944. $query .= " AND a.invoice = '1' AND NOT a.till IS NULL";
  1945. if ( $myconfig->{role} eq 'user' ) {
  1946. $query .= " AND e.login = '$form->{login}'";
  1947. }
  1948. }
  1949. $query .= qq|
  1950. GROUP BY ce.name, ac.transdate, ac.source, ac.memo,
  1951. ee.name, a.till, a.curr, c.meta_number|;
  1952. if ( $form->{till} eq "" && !$form->{meta_number}) {
  1953. $query .= qq|
  1954. UNION
  1955. SELECT g.description, ac.transdate,
  1956. sum(ac.amount) * $ml AS paid, ac.source,
  1957. ac.memo, ee.name AS employee, '' AS till,
  1958. '' AS curr, '' AS meta_number
  1959. FROM acc_trans ac
  1960. JOIN gl g ON (g.id = ac.trans_id)
  1961. LEFT
  1962. JOIN entity_employee e ON
  1963. (g.person_id = e.entity_id)
  1964. JOIN entity ee ON (e.entity_id = ee.id)
  1965. $dpt_join
  1966. WHERE ac.chart_id = $ref->{id} $glwhere
  1967. AND (ac.amount * $ml) > 0
  1968. GROUP BY g.description, ac.transdate,
  1969. ac.source, ac.memo, ee.name|;
  1970. }
  1971. $query .= qq| ORDER BY $sortorder|;
  1972. $sth = $dbh->prepare($query);
  1973. $sth->execute || $form->dberror($query);
  1974. while ( my $pr = $sth->fetchrow_hashref(NAME_lc) ) {
  1975. $form->db_parse_numeric(sth=>$sth, hashref=>$pr);
  1976. push @{ $form->{ $ref->{id} } }, $pr;
  1977. }
  1978. $sth->finish;
  1979. }
  1980. $dbh->commit;
  1981. }
  1982. 1;