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