summaryrefslogtreecommitdiff
path: root/LedgerSMB/RP.pm
blob: a9790855700f62645bef3ab4309ad99b809b4007 (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, e.legal_name 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. JOIN company e USING (entity_id)
  1402. WHERE $where|;
  1403. $query .= qq| ORDER BY ctid, curr, $transdate, invnumber|;
  1404. $sth = $dbh->prepare($query) || $form->dberror($query);
  1405. $sth->execute();
  1406. while ( $ref = $sth->fetchrow_hashref('NAME_lc') ) {
  1407. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1408. $ref->{module} =
  1409. ( $ref->{invoice} )
  1410. ? $invoice
  1411. : $form->{arap};
  1412. $ref->{module} = 'ps' if $ref->{till};
  1413. $ref->{exchangerate} = 1
  1414. unless $ref->{exchangerate};
  1415. push @{ $form->{AG} }, $ref;
  1416. }
  1417. $sth->finish;
  1418. # get language
  1419. my $query = qq|SELECT code, description FROM language ORDER BY 2|;
  1420. $sth = $dbh->prepare($query);
  1421. $sth->execute || $form->dberror($query);
  1422. while ( $ref = $sth->fetchrow_hashref('NAME_lc') ) {
  1423. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1424. push @{ $form->{all_language} }, $ref;
  1425. }
  1426. $sth->finish;
  1427. $dbh->commit;
  1428. }
  1429. sub get_customer {
  1430. my ( $self, $myconfig, $form ) = @_;
  1431. my $dbh = $form->{dbh};
  1432. my $query = qq|
  1433. SELECT name, email, cc, bcc FROM $form->{ct} ct
  1434. WHERE ct.id = ?|;
  1435. $sth = $dbh->prepare($query);
  1436. $sth->execute( $form->{"$form->{ct}_id"} );
  1437. ( $form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc} ) =
  1438. $sth->fetchrow_array();
  1439. $dbh->commit;
  1440. }
  1441. sub get_taxaccounts {
  1442. my ( $self, $myconfig, $form ) = @_;
  1443. my $dbh = $form->{dbh};
  1444. my $ARAP = uc $form->{db};
  1445. # get tax accounts
  1446. my $query = qq|
  1447. SELECT DISTINCT c.accno, c.description
  1448. FROM chart c
  1449. JOIN tax t ON (c.id = t.chart_id)
  1450. WHERE c.link LIKE '%${ARAP}_tax%'
  1451. ORDER BY c.accno|;
  1452. my $sth = $dbh->prepare($query);
  1453. $sth->execute || $form->dberror;
  1454. my $ref = ();
  1455. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1456. push @{ $form->{taxaccounts} }, $ref;
  1457. }
  1458. $sth->finish;
  1459. # get gifi tax accounts
  1460. my $query = qq|
  1461. SELECT DISTINCT g.accno, g.description
  1462. FROM gifi g
  1463. JOIN chart c ON (c.gifi_accno= g.accno)
  1464. JOIN tax t ON (c.id = t.chart_id)
  1465. WHERE c.link LIKE '%${ARAP}_tax%'
  1466. ORDER BY accno|;
  1467. my $sth = $dbh->prepare($query);
  1468. $sth->execute || $form->dberror;
  1469. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1470. push @{ $form->{gifi_taxaccounts} }, $ref;
  1471. }
  1472. $sth->finish;
  1473. $dbh->commit;
  1474. }
  1475. sub tax_report {
  1476. my ( $self, $myconfig, $form ) = @_;
  1477. my $dbh = $form->{dbh};
  1478. my ( $null, $department_id ) = split /--/, $form->{department};
  1479. # build WHERE
  1480. my $where = "1 = 1";
  1481. my $cashwhere = "";
  1482. if ($department_id) {
  1483. $where .= qq|AND a.department_id = | . $dbh->quote($department_id);
  1484. }
  1485. my $query;
  1486. my $sth;
  1487. my $accno;
  1488. if ( $form->{accno} ) {
  1489. if ( $form->{accno} =~ /^gifi_/ ) {
  1490. ( $null, $accno ) = split /_/, $form->{accno};
  1491. $accno = $dbh->quote($accno);
  1492. $accno = qq| AND ch.gifi_accno = $accno|;
  1493. }
  1494. else {
  1495. $accno = $dbh->quote( $form->{accno} );
  1496. $accno = qq| AND ch.accno = $accno|;
  1497. }
  1498. }
  1499. my $table;
  1500. my $ARAP;
  1501. if ( $form->{db} eq 'ar' ) {
  1502. $table = "customer";
  1503. $ARAP = "AR";
  1504. }
  1505. if ( $form->{db} eq 'ap' ) {
  1506. $table = "vendor";
  1507. $ARAP = "AP";
  1508. }
  1509. my $transdate = "a.transdate";
  1510. ( $form->{fromdate}, $form->{todate} ) =
  1511. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  1512. if $form->{year} && $form->{month};
  1513. # if there are any dates construct a where
  1514. if ( $form->{fromdate} || $form->{todate} ) {
  1515. if ( $form->{fromdate} ) {
  1516. $where .= " AND $transdate >= '$form->{fromdate}'";
  1517. }
  1518. if ( $form->{todate} ) {
  1519. $where .= " AND $transdate <= '$form->{todate}'";
  1520. }
  1521. }
  1522. if ( $form->{method} eq 'cash' ) {
  1523. $transdate = "a.datepaid";
  1524. my $todate = $form->{todate};
  1525. if ( !$todate ) {
  1526. ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
  1527. }
  1528. $cashwhere = qq|
  1529. AND ac.trans_id IN (
  1530. SELECT trans_id
  1531. FROM acc_trans
  1532. JOIN chart ON (chart_id = chart.id)
  1533. WHERE link LIKE '%${ARAP}_paid%'
  1534. AND $transdate <= | . $dbh->quote($todate) . qq|
  1535. AND a.paid = a.amount)|;
  1536. }
  1537. my $ml = ( $form->{db} eq 'ar' ) ? 1 : -1;
  1538. my %ordinal = ( 'transdate' => 3, 'invnumber' => 4, 'name' => 5 );
  1539. my @a = qw(transdate invnumber name);
  1540. my $sortorder = $form->sort_order( \@a, \%ordinal );
  1541. if ( $form->{summary} ) {
  1542. $query = qq|
  1543. SELECT a.id, a.invoice, $transdate AS transdate,
  1544. a.invnumber, c.legal_name AS name, a.netamount,
  1545. ac.amount * $ml AS tax, a.till
  1546. FROM acc_trans ac
  1547. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1548. JOIN chart ch ON (ch.id = ac.chart_id)
  1549. JOIN $table n ON (n.entity_id = a.entity_id)
  1550. JOIN company c ON (c.entity_id = n.entity_id)
  1551. WHERE $where $accno $cashwhere |;
  1552. if ( $form->{fromdate} ) {
  1553. # include open transactions from previous period
  1554. if ($cashwhere) {
  1555. $query .= qq|
  1556. UNION
  1557. SELECT a.id, a.invoice,
  1558. $transdate AS transdate, a.invnumber,
  1559. c.legal_name AS name, a.netamount, ac.
  1560. amount * $ml AS tax, a.till
  1561. FROM acc_trans ac
  1562. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1563. JOIN chart ch ON (ch.id = ac.chart_id)
  1564. JOIN $table n ON (n.entity_id = a.entity_id)
  1565. JOIN company c ON (c.entity_id = n.entity_id)
  1566. WHERE a.datepaid >= '$form->{fromdate}'
  1567. $accno $cashwhere|;
  1568. }
  1569. }
  1570. }
  1571. else {
  1572. $query = qq|
  1573. SELECT a.id, '0' AS invoice, $transdate AS transdate,
  1574. a.invnumber, c.legal_name AS name, a.netamount,
  1575. ac.amount * $ml AS tax, a.notes AS description,
  1576. a.till
  1577. FROM acc_trans ac
  1578. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1579. JOIN chart ch ON (ch.id = ac.chart_id)
  1580. JOIN $table n ON (n.entity_id = a.entity_id)
  1581. JOIN company c ON (c.entity_id = a.entity_id)
  1582. WHERE $where $accno AND a.invoice = '0' $cashwhere
  1583. UNION
  1584. SELECT a.id, '1' AS invoice, $transdate AS transdate,
  1585. a.invnumber, c.legal_name AS name,
  1586. i.sellprice * i.qty * $ml AS netamount,
  1587. i.sellprice * i.qty * $ml *
  1588. (SELECT tx.rate FROM tax tx
  1589. WHERE tx.chart_id = ch.id
  1590. AND (tx.validto > $transdate
  1591. OR tx.validto IS NULL)
  1592. ORDER BY validto LIMIT 1)
  1593. AS tax, i.description, a.till
  1594. FROM acc_trans ac
  1595. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1596. JOIN chart ch ON (ch.id = ac.chart_id)
  1597. JOIN $table n ON (n.entity_id = a.entity_id)
  1598. JOIN company c ON (c.entity_id = n.entity_id)
  1599. JOIN ${table}tax t
  1600. ON (t.${table}_id = n.id AND t.chart_id = ch.id)
  1601. JOIN invoice i ON (i.trans_id = a.id)
  1602. JOIN partstax pt
  1603. ON (pt.parts_id = i.parts_id
  1604. AND pt.chart_id = ch.id)
  1605. WHERE $where $accno AND a.invoice = '1' $cashwhere|;
  1606. if ( $form->{fromdate} ) {
  1607. if ($cashwhere) {
  1608. $query .= qq|
  1609. UNION
  1610. SELECT a.id, '0' AS invoice,
  1611. $transdate AS transdate,
  1612. a.invnumber, c.legal_name AS name,
  1613. a.netamount,
  1614. ac.amount * $ml AS tax,
  1615. a.notes AS description, a.till
  1616. FROM acc_trans ac
  1617. JOIN $form->{db} a
  1618. ON (a.id = ac.trans_id)
  1619. JOIN chart ch ON (ch.id = ac.chart_id)
  1620. JOIN $table n
  1621. ON (n.entity_id = a.entity_id)
  1622. JOIN company c
  1623. ON (c.entity_id = n.entity_id)
  1624. WHERE a.datepaid >= '$form->{fromdate}'
  1625. $accno AND a.invoice = '0'
  1626. $cashwhere
  1627. UNION
  1628. SELECT a.id, '1' AS invoice,
  1629. $transdate AS transdate,
  1630. a.invnumber,
  1631. c.legal_name AS name,
  1632. i.sellprice * i.qty * $ml
  1633. AS netamount, i.sellprice
  1634. * i.qty * $ml *
  1635. (SELECT tx.rate FROM tax tx
  1636. WHERE tx.chart_id = ch.id
  1637. AND
  1638. (tx.validto > $transdate
  1639. OR tx.validto IS NULL)
  1640. ORDER BY validto LIMIT 1)
  1641. AS tax, i.description, a.till
  1642. FROM acc_trans ac
  1643. JOIN $form->{db} a
  1644. ON (a.id = ac.trans_id)
  1645. JOIN chart ch ON (ch.id = ac.chart_id)
  1646. JOIN $table n ON
  1647. (n.entity_id = a.entity_id)
  1648. JOIN company c ON
  1649. (c.entity_id = n.entity_id)
  1650. JOIN ${table}tax t
  1651. ON (t.${table}_id = n.id
  1652. AND t.chart_id = ch.id)
  1653. JOIN invoice i ON (i.trans_id = a.id)
  1654. JOIN partstax pt
  1655. ON (pt.parts_id = i.parts_id
  1656. AND pt.chart_id = ch.id)
  1657. WHERE a.datepaid >= '$form->{fromdate}'
  1658. $accno AND a.invoice = '1'
  1659. $cashwhere|;
  1660. }
  1661. }
  1662. }
  1663. if ( $form->{report} =~ /nontaxable/ ) {
  1664. if ( $form->{summary} ) {
  1665. # only gather up non-taxable transactions
  1666. $query = qq|
  1667. SELECT DISTINCT a.id, a.invoice,
  1668. $transdate AS transdate, a.invnumber,
  1669. c.legal_name AS name, a.netamount, a.till
  1670. FROM acc_trans ac
  1671. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1672. JOIN $table n ON (n.entity_id = a.entity_id)
  1673. JOIN company c ON (c.entity_id = n.entity_id)
  1674. WHERE $where AND a.netamount = a.amount
  1675. $cashwhere|;
  1676. if ( $form->{fromdate} ) {
  1677. if ($cashwhere) {
  1678. $query .= qq|
  1679. UNION
  1680. SELECT DISTINCT a.id, a.invoice,
  1681. $transdate AS transdate,
  1682. a.invnumber,
  1683. c.legal_name AS name,
  1684. a.netamount, a.till
  1685. FROM acc_trans ac
  1686. JOIN $form->{db} a
  1687. ON (a.id = ac.trans_id)
  1688. JOIN $table n
  1689. ON (n.entity_id = a.entity_id)
  1690. JOIN company c
  1691. ON (c.entity_id = n.entity_id)
  1692. WHERE a.datepaid
  1693. >= '$form->{fromdate}'
  1694. AND
  1695. a.netamount = a.amount
  1696. $cashwhere|;
  1697. }
  1698. }
  1699. }
  1700. else {
  1701. # gather up details for non-taxable transactions
  1702. $query = qq|
  1703. SELECT a.id, '0' AS invoice,
  1704. $transdate AS transdate, a.invnumber,
  1705. c.legal_name AS name, a.netamount,
  1706. a.notes AS description, a.till
  1707. FROM acc_trans ac
  1708. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1709. JOIN $table n ON (n.entity_id = a.entity_id)
  1710. JOIN company c ON (c.entity_id = n.entity_id)
  1711. WHERE $where AND a.invoice = '0'
  1712. AND a.netamount = a.amount $cashwhere
  1713. GROUP BY a.id, $transdate, a.invnumber, name,
  1714. a.netamount, a.notes, a.till
  1715. UNION
  1716. SELECT a.id, '1' AS invoice,
  1717. $transdate AS transdate, a.invnumber,
  1718. c.legal_name AS name,
  1719. sum(ac.sellprice * ac.qty)
  1720. * $ml AS netamount, ac.description,
  1721. a.till
  1722. FROM invoice ac
  1723. JOIN $form->{db} a ON (a.id = ac.trans_id)
  1724. JOIN $table n ON (n.entity_id = a.entity_id)
  1725. JOIN company c ON (c.entity_id = n.entity_id)
  1726. WHERE $where AND a.invoice = '1' AND
  1727. (a.entity_id NOT IN
  1728. (SELECT ${table}_id FROM ${table}tax t
  1729. (${table}_id)
  1730. ) OR ac.parts_id NOT IN
  1731. (SELECT parts_id FROM partstax p
  1732. (parts_id))) $cashwhere
  1733. GROUP BY a.id, a.invnumber, $transdate, name,
  1734. ac.description, a.till|;
  1735. if ( $form->{fromdate} ) {
  1736. if ($cashwhere) {
  1737. $query .= qq|
  1738. UNION
  1739. SELECT a.id, '0' AS invoice,
  1740. $transdate AS transdate,
  1741. a.invnumber,
  1742. c.legal_name AS name,
  1743. a.netamount,
  1744. a.notes AS description,
  1745. a.till
  1746. FROM acc_trans ac
  1747. JOIN $form->{db} a
  1748. ON (a.id = ac.trans_id)
  1749. JOIN $table n
  1750. ON (n.entity_id = a.entity_id)
  1751. JOIN company c
  1752. ON (c.entity_id = n.entity_id)
  1753. WHERE a.datepaid
  1754. >= '$form->{fromdate}'
  1755. AND a.invoice = '0'
  1756. AND a.netamount
  1757. = a.amount $cashwhere
  1758. GROUP BY a.id, $transdate,
  1759. a.invnumber, name,
  1760. a.netamount, a.notes,
  1761. a.till
  1762. UNION
  1763. SELECT a.id, '1' AS invoice,
  1764. $transdate AS transdate,
  1765. a.invnumber,
  1766. c.legal_name AS name,
  1767. sum(ac.sellprice
  1768. * ac.qty) * $ml
  1769. AS netamount,
  1770. ac.description, a.till
  1771. FROM invoice 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 = '1' AND
  1781. (a.entity_id NOT IN
  1782. (SELECT ${table}_id
  1783. FROM ${table}tax t
  1784. (${table}_id)) OR
  1785. ac.parts_id NOT IN
  1786. (SELECT parts_id
  1787. FROM partstax p
  1788. (parts_id)))
  1789. $cashwhere
  1790. GROUP BY a.id, a.invnumber,
  1791. $transdate, name,
  1792. ac.description, a.till|;
  1793. }
  1794. }
  1795. }
  1796. }
  1797. $query .= qq| ORDER by $sortorder|;
  1798. $sth = $dbh->prepare($query);
  1799. $sth->execute || $form->dberror($query);
  1800. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1801. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1802. $ref->{tax} = $form->round_amount( $ref->{tax}, 2 );
  1803. if ( $form->{report} =~ /nontaxable/ ) {
  1804. push @{ $form->{TR} }, $ref if $ref->{netamount};
  1805. }
  1806. else {
  1807. push @{ $form->{TR} }, $ref if $ref->{tax};
  1808. }
  1809. }
  1810. $sth->finish;
  1811. $dbh->commit;
  1812. }
  1813. sub paymentaccounts {
  1814. my ( $self, $myconfig, $form ) = @_;
  1815. my $dbh = $form->{dbh};
  1816. my $ARAP = uc $form->{db};
  1817. # get A(R|P)_paid accounts
  1818. my $query = qq|
  1819. SELECT accno, description FROM chart
  1820. WHERE link LIKE '%${ARAP}_paid%'
  1821. ORDER BY accno|;
  1822. my $sth = $dbh->prepare($query);
  1823. $sth->execute || $form->dberror($query);
  1824. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1825. push @{ $form->{PR} }, $ref;
  1826. }
  1827. $sth->finish;
  1828. $form->all_years( $myconfig, $dbh );
  1829. $dbh->{dbh};
  1830. }
  1831. sub payments {
  1832. my ( $self, $myconfig, $form ) = @_;
  1833. my $dbh = $form->{dbh};
  1834. my $ml = 1;
  1835. if ( $form->{db} eq 'ar' ) {
  1836. $table = 'customer';
  1837. $ml = -1;
  1838. }
  1839. if ( $form->{db} eq 'ap' ) {
  1840. $table = 'vendor';
  1841. }
  1842. my $query;
  1843. my $sth;
  1844. my $dpt_join;
  1845. my $where;
  1846. my $var;
  1847. if ( $form->{department_id} ) {
  1848. $dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id)|;
  1849. $where =
  1850. qq| AND t.department_id = | . $dbh->quote( $form->{department_id} );
  1851. }
  1852. ( $form->{fromdate}, $form->{todate} ) =
  1853. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  1854. if $form->{year} && $form->{month};
  1855. if ( $form->{fromdate} ) {
  1856. $where .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} );
  1857. }
  1858. if ( $form->{todate} ) {
  1859. $where .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} );
  1860. }
  1861. if ( !$form->{fx_transaction} ) {
  1862. $where .= " AND ac.fx_transaction = '0'";
  1863. }
  1864. if ( $form->{description} ne "" ) {
  1865. $var = $dbh->quote( $form->like( lc $form->{description} ) );
  1866. $where .= " AND lower(c.name) LIKE $var";
  1867. }
  1868. if ( $form->{source} ne "" ) {
  1869. $var = $dbh->quote( $form->like( lc $form->{source} ) );
  1870. $where .= " AND lower(ac.source) LIKE $var";
  1871. }
  1872. if ( $form->{memo} ne "" ) {
  1873. $var = $dbh->quote( $form->like( lc $form->{memo} ) );
  1874. $where .= " AND lower(ac.memo) LIKE $var";
  1875. }
  1876. my %ordinal = (
  1877. 'name' => 1,
  1878. 'transdate' => 2,
  1879. 'source' => 4,
  1880. 'employee' => 6,
  1881. 'till' => 7
  1882. );
  1883. my @a = qw(name transdate employee);
  1884. my $sortorder = $form->sort_order( \@a, \%ordinal );
  1885. my $glwhere = $where;
  1886. $glwhere =~ s/\(c.name\)/\(g.description\)/;
  1887. # cycle through each id
  1888. foreach my $accno ( split( / /, $form->{paymentaccounts} ) ) {
  1889. $query = qq|
  1890. SELECT id, accno, description
  1891. FROM chart
  1892. WHERE accno = ?|;
  1893. $sth = $dbh->prepare($query);
  1894. $sth->execute($accno) || $form->dberror($query);
  1895. my $ref = $sth->fetchrow_hashref(NAME_lc);
  1896. push @{ $form->{PR} }, $ref;
  1897. $sth->finish;
  1898. $query = qq|
  1899. SELECT c.name, ac.transdate,
  1900. sum(ac.amount) * $ml AS paid, ac.source,
  1901. ac.memo, e.name AS employee, a.till, a.curr
  1902. FROM acc_trans ac
  1903. JOIN $form->{db} a ON (ac.trans_id = a.id)
  1904. JOIN $table c ON (c.id = a.${table}_id)
  1905. LEFT JOIN employee e ON (a.employee_id = e.id)
  1906. $dpt_join
  1907. WHERE ac.chart_id = $ref->{id} $where|;
  1908. if ( $form->{till} ne "" ) {
  1909. $query .= " AND a.invoice = '1' AND NOT a.till IS NULL";
  1910. if ( $myconfig->{role} eq 'user' ) {
  1911. $query .= " AND e.login = '$form->{login}'";
  1912. }
  1913. }
  1914. $query .= qq|
  1915. GROUP BY c.name, ac.transdate, ac.source, ac.memo,
  1916. e.name, a.till, a.curr|;
  1917. if ( $form->{till} eq "" ) {
  1918. $query .= qq|
  1919. UNION
  1920. SELECT g.description, ac.transdate,
  1921. sum(ac.amount) * $ml AS paid, ac.source,
  1922. ac.memo, e.name AS employee, '' AS till,
  1923. '' AS curr
  1924. FROM acc_trans ac
  1925. JOIN gl g ON (g.id = ac.trans_id)
  1926. LEFT
  1927. JOIN employee e ON (g.employee_id = e.id)
  1928. $dpt_join
  1929. WHERE ac.chart_id = $ref->{id} $glwhere
  1930. AND (ac.amount * $ml) > 0
  1931. GROUP BY g.description, ac.transdate,
  1932. ac.source, ac.memo, e.name|;
  1933. }
  1934. $query .= qq| ORDER BY $sortorder|;
  1935. $sth = $dbh->prepare($query);
  1936. $sth->execute || $form->dberror($query);
  1937. while ( my $pr = $sth->fetchrow_hashref(NAME_lc) ) {
  1938. $form->db_parse_numeric(sth=>$sth, hashref=>$pr);
  1939. push @{ $form->{ $ref->{id} } }, $pr;
  1940. }
  1941. $sth->finish;
  1942. }
  1943. $dbh->commit;
  1944. }
  1945. 1;