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