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