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