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