summaryrefslogtreecommitdiff
path: root/LedgerSMB/RP.pm
blob: fa036bc889e2c6df67b020f294dafe0f6f7a9deb (plain) 
    

  1. #=====================================================================
    2. 

  2. # LedgerSMB 
    3. 

  3. # Small Medium Business Accounting software
    4. 

  4. # http://www.ledgersmb.org/
    5. 

  5. # 
    6. 

  6. # Copyright (C) 2006
    7. 

  7. # This work contains copyrighted information from a number of sources all used
    8. 

  8. # with permission.
    9. 

  9. #
    10. 

  10. # This file contains source code included with or based on SQL-Ledger which
    11. 

  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
    12. 

  12. # under the GNU General Public License version 2 or, at your option, any later
    13. 

  13. # version.  For a full list including contact information of contributors,
    14. 

  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
    15. 

  15. #
    16. 

  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
    17. 

  17. # Copyright (C) 2001
    18. 

  18. #
    19. 

  19. #  Author: DWS Systems Inc.
    20. 

  20. #     Web: http://www.sql-ledger.org
    21. 

  21. #
    22. 

  22. #  Contributors:
    23. 

  23. #
    24. 

  24. #======================================================================
    25. 

  25. #
    26. 

  26. # This file has NOT undergone whitespace cleanup.
    27. 

  27. #
    28. 

  28. #======================================================================
    29. 

  29. #
    30. 

  30. # backend code for reports
    31. 

  31. #
    32. 

  32. #======================================================================
    33. 

  33. 

  34. package RP;
    35. 

  35. 

  36. sub inventory_activity {
    37. 

  37.   my ($self, $myconfig, $form) = @_;
    38. 

  38.   ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{fromyear}, $form->{frommonth}, $form->{interval}) if $form->{fromyear} && $form->{frommonth};
    39. 

  39. 

  40. 

  41.   unless ($form->{sort_col}){
    42. 

  42.     $form->{sort_col} = 'partnumber';
    43. 

  43.   }
    44. 

  44. 

  45.   my $dbh = $form->dbconnect($myconfig) || $form->dberror();
    46. 

  46. 

  47.   my $where = '';
    48. 

  48.   if ($form->{fromdate}){
    49. 

  49.      $where .= "AND coalesce(ar.duedate, ap.duedate) >= ".$dbh->quote($form->{fromdate});
    50. 

  50.   }
    51. 

  51.   if ($form->{todate}){
    52. 

  52.      $where .= "AND coalesce(ar.duedate, ap.duedate) < ".$dbh->quote($form->{todate}). " ";
    53. 

  53.   }
    54. 

  54.   if ($form->{partnumber}){
    55. 

  55.     $where .= qq|AND p.partnumber ILIKE '%|.$form->{partnumber}.qq|%' |; 
    56. 

  56.   }
    57. 

  57.   if ($form->{description}){
    58. 

  58.     $where .= q|AND p.description ILIKE '%|.$form->{description}.q|%' |;
    59. 

  59.   }
    60. 

  60.   $where =~ s/^AND/WHERE/;
    61. 

  61. 

  62.   my $query = qq|
    63. 

  63.     SELECT min(p.description) AS description, 
    64. 

  64.         min(p.partnumber) AS partnumber, sum(
    65. 

  65.             CASE WHEN i.qty > 0 THEN i.qty ELSE 0 END
    66. 

  66.         ) AS sold, sum (
    67. 

  67.             CASE WHEN i.qty > 0 THEN i.sellprice * i.qty ELSE 0 END
    68. 

  68.         ) AS revenue, sum(
    69. 

  69.             CASE WHEN i.qty < 0 THEN i.qty * -1 ELSE 0 END
    70. 

  70.         ) AS received, sum(
    71. 

  71.             CASE WHEN i.qty < 0 THEN i.sellprice * i.qty * -1
    72. 

  72.             ELSE 0 END
    73. 

  73.         ) as expenses, min(p.id) as id
    74. 

  74.     FROM invoice i
    75. 

  75.     INNER JOIN parts p ON (i.parts_id = p.id)
    76. 

  76.     LEFT JOIN ar ON (ar.id = i.trans_id)
    77. 

  77.     LEFT JOIN ap ON (ap.id = i.trans_id)
    78. 

  78.         $where
    79. 

  79.     GROUP BY i.parts_id
    80. 

  80.     ORDER BY $form->{sort_col}
    81. 

  81.   |;
    82. 

  82.   my $sth = $dbh->prepare($query) || $form->dberror($query);
    83. 

  83.   $sth->execute() || $form->dberror($query);
    84. 

  84.   @cols = qw(description sold revenue partnumber received expense);
    85. 

  85.   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    86. 

  86.     $ref->{net_income} = $ref->{revenue} - $ref->{expense}; 
    87. 

  87.     map {$ref->{$_} =~ s/^\s*//} @cols;
    88. 

  88.     map {$ref->{$_} =~ s/\s*$//} @cols;
    89. 

  89.     push @{$form->{TB}}, $ref;
    90. 

  90.   }
    91. 

  91.   $sth->finish;
    92. 

  92.   $dbh->disconnect;
    93. 

  93.     
    94. 

  94. }
    95. 

  95. 

  96. 

  97. 

  98. sub yearend_statement {
    99. 

  99.   my ($self, $myconfig, $form) = @_;
    100. 

  100. 

  101.   # connect to database
    102. 

  102.   my $dbh = $form->dbconnect($myconfig);
    103. 

  103. 

  104.   # if todate < existing yearends, delete GL and yearends
    105. 

  105.   my $query = qq|SELECT trans_id FROM yearend
    106. 

  106.                  WHERE transdate >= '$form->{todate}'|;
    107. 

  107.   my $sth = $dbh->prepare($query);
    108. 

  108.   $sth->execute || $form->dberror($query);
    109. 

  109.   
    110. 

  110.   my @trans_id = ();
    111. 

  111.   my $id;
    112. 

  112.   while (($id) = $sth->fetchrow_array) {
    113. 

  113.     push @trans_id, $id;
    114. 

  114.   }
    115. 

  115.   $sth->finish;
    116. 

  116. 

  117.   $query = qq|DELETE FROM gl
    118. 

  118.               WHERE id = ?|;
    119. 

  119.   $sth = $dbh->prepare($query) || $form->dberror($query);
    120. 

  120. 

  121.   $query = qq|DELETE FROM acc_trans
    122. 

  122.               WHERE trans_id = ?|;
    123. 

  123.   my $ath = $dbh->prepare($query) || $form->dberror($query);
    124. 

  124.           
    125. 

  125.   foreach $id (@trans_id) {
    126. 

  126.     $sth->execute($id);
    127. 

  127.     $ath->execute($id);
    128. 

  128. 

  129.     $sth->finish;
    130. 

  130.     $ath->finish;
    131. 

  131.   }
    132. 

  132.   
    133. 

  133.   
    134. 

  134.   my $last_period = 0;
    135. 

  135.   my @categories = qw(I E);
    136. 

  136.   my $category;
    137. 

  137. 

  138.   $form->{decimalplaces} *= 1;
    139. 

  139. 

  140.   &get_accounts($dbh, 0, $form->{fromdate}, $form->{todate}, $form, \@categories);
    141. 

  141.   
    142. 

  142.   # disconnect
    143. 

  143.   $dbh->disconnect;
    144. 

  144. 

  145. 

  146.   # now we got $form->{I}{accno}{ }
    147. 

  147.   # and $form->{E}{accno}{  }
    148. 

  148.   
    149. 

  149.   my %account = ( 'I' => { 'label' => 'income',
    150. 

  150.                            'labels' => 'income',
    151. 

  151.                'ml' => 1 },
    152. 

  152.           'E' => { 'label' => 'expense',
    153. 

  153.                    'labels' => 'expenses',
    154. 

  154.                'ml' => -1 }
    155. 

  155.         );
    156. 

  156.   
    157. 

  157.   foreach $category (@categories) {
    158. 

  158.     foreach $key (sort keys %{ $form->{$category} }) {
    159. 

  159.       if ($form->{$category}{$key}{charttype} eq 'A') {
    160. 

  160.     $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
    161. 

  161.       }
    162. 

  162.     }
    163. 

  163.   }
    164. 

  164. 

  165. 

  166.   # totals for income and expenses
    167. 

  167.   $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
    168. 

  168.   $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
    169. 

  169. 

  170.   # total for income/loss
    171. 

  171.   $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
    172. 

  172.   
    173. 

  173. }
    174. 

  174. 

  175. 

  176. sub income_statement {
    177. 

  177.   my ($self, $myconfig, $form) = @_;
    178. 

  178. 

  179.   # connect to database
    180. 

  180.   my $dbh = $form->dbconnect($myconfig);
    181. 

  181. 

  182.   my $last_period = 0;
    183. 

  183.   my @categories = qw(I E);
    184. 

  184.   my $category;
    185. 

  185. 

  186.   $form->{decimalplaces} *= 1;
    187. 

  187. 

  188.   if (! ($form->{fromdate} || $form->{todate})) {
    189. 

  189.     if ($form->{fromyear} && $form->{frommonth}) {
    190. 

  190.       ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{fromyear}, $form->{frommonth}, $form->{interval});
    191. 

  191.     }
    192. 

  192.   }
    193. 

  193.   
    194. 

  194.   &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, \@categories, 1);
    195. 

  195.   
    196. 

  196.   if (! ($form->{comparefromdate} || $form->{comparetodate})) {
    197. 

  197.     if ($form->{compareyear} && $form->{comparemonth}) {
    198. 

  198.       ($form->{comparefromdate}, $form->{comparetodate}) = $form->from_to($form->{compareyear}, $form->{comparemonth}, $form->{interval});
    199. 

  199.     }
    200. 

  200.   }
    201. 

  201. 

  202.   # if there are any compare dates
    203. 

  203.   if ($form->{comparefromdate} || $form->{comparetodate}) {
    204. 

  204.     $last_period = 1;
    205. 

  205. 

  206.     &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, \@categories, 1);
    207. 

  207.   }  
    208. 

  208. 

  209.   
    210. 

  210.   # disconnect
    211. 

  211.   $dbh->disconnect;
    212. 

  212. 

  213. 

  214.   # now we got $form->{I}{accno}{ }
    215. 

  215.   # and $form->{E}{accno}{  }
    216. 

  216.   
    217. 

  217.   my %account = ( 'I' => { 'label' => 'income',
    218. 

  218.                            'labels' => 'income',
    219. 

  219.                'ml' => 1 },
    220. 

  220.           'E' => { 'label' => 'expense',
    221. 

  221.                    'labels' => 'expenses',
    222. 

  222.                'ml' => -1 }
    223. 

  223.         );
    224. 

  224.   
    225. 

  225.   my $str;
    226. 

  226.   
    227. 

  227.   foreach $category (@categories) {
    228. 

  228.     
    229. 

  229.     foreach $key (sort keys %{ $form->{$category} }) {
    230. 

  230.       # push description onto array
    231. 

  231.       
    232. 

  232.       $str = ($form->{l_heading}) ? $form->{padding} : "";
    233. 

  233.       
    234. 

  234.       if ($form->{$category}{$key}{charttype} eq "A") {
    235. 

  235.     $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
    236. 

  236.       }
    237. 

  237.       if ($form->{$category}{$key}{charttype} eq "H") {
    238. 

  238.     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
    239. 

  239.       $dash = "- ";
    240. 

  240.       push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
    241. 

  241.       push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    242. 

  242.       
    243. 

  243.       if ($last_period) {
    244. 

  244.         push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    245. 

  245.       }
    246. 

  246.       
    247. 

  247.     }
    248. 

  248.     
    249. 

  249.     $str = "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
    250. 

  250. 

  251.     $account{$category}{subthis} = $form->{$category}{$key}{this};
    252. 

  252.     $account{$category}{sublast} = $form->{$category}{$key}{last};
    253. 

  253.     $account{$category}{subdescription} = $form->{$category}{$key}{description};
    254. 

  254.     $account{$category}{subtotal} = 1;
    255. 

  255. 

  256.     $form->{$category}{$key}{this} = 0;
    257. 

  257.     $form->{$category}{$key}{last} = 0;
    258. 

  258. 

  259.     next unless $form->{l_heading};
    260. 

  260. 

  261.     $dash = " ";
    262. 

  262.       }
    263. 

  263.       
    264. 

  264.       push(@{$form->{"$account{$category}{label}_account"}}, $str);
    265. 

  265.       
    266. 

  266.       if ($form->{$category}{$key}{charttype} eq 'A') {
    267. 

  267.     $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
    268. 

  268.     $dash = "- ";
    269. 

  269.       }
    270. 

  270.       
    271. 

  271.       push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    272. 

  272.       
    273. 

  273.       # add amount or - for last period
    274. 

  274.       if ($last_period) {
    275. 

  275.     $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
    276. 

  276. 

  277.     push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig,$form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    278. 

  278.       }
    279. 

  279.     }
    280. 

  280. 

  281.     $str = ($form->{l_heading}) ? $form->{padding} : "";
    282. 

  282.     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
    283. 

  283.       push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
    284. 

  284.       push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    285. 

  285. 

  286.       if ($last_period) {
    287. 

  287.     push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    288. 

  288.       }
    289. 

  289.     }
    290. 

  290.       
    291. 

  291.   }
    292. 

  292. 

  293. 

  294.   # totals for income and expenses
    295. 

  295.   $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
    296. 

  296.   $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
    297. 

  297. 

  298.   # total for income/loss
    299. 

  299.   $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
    300. 

  300.   
    301. 

  301.   if ($last_period) {
    302. 

  302.     # total for income/loss
    303. 

  303.     $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period} - $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
    304. 

  304.     
    305. 

  305.     # totals for income and expenses for last_period
    306. 

  306.     $form->{total_income_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period}, $form->{decimalplaces}, "- ");
    307. 

  307.     $form->{total_expenses_last_period} = $form->format_amount($myconfig, $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
    308. 

  308. 

  309.   }
    310. 

  310. 

  311. 

  312.   $form->{total_income_this_period} = $form->format_amount($myconfig,$form->{total_income_this_period}, $form->{decimalplaces}, "- ");
    313. 

  313.   $form->{total_expenses_this_period} = $form->format_amount($myconfig,$form->{total_expenses_this_period}, $form->{decimalplaces}, "- ");
    314. 

  314.   $form->{total_this_period} = $form->format_amount($myconfig,$form->{total_this_period}, $form->{decimalplaces}, "- ");
    315. 

  315. 

  316. }
    317. 

  317. 

  318. 

  319. sub balance_sheet {
    320. 

  320.   my ($self, $myconfig, $form) = @_;
    321. 

  321.   
    322. 

  322.   # connect to database
    323. 

  323.   my $dbh = $form->dbconnect($myconfig);
    324. 

  324. 

  325.   my $last_period = 0;
    326. 

  326.   my @categories = qw(A L Q);
    327. 

  327. 

  328.   my $null;
    329. 

  329.   
    330. 

  330.   if ($form->{asofdate}) {
    331. 

  331.     if ($form->{asofyear} && $form->{asofmonth}) {
    332. 

  332.       if ($form->{asofdate} !~ /\W/) {
    333. 

  333.     $form->{asofdate} = "$form->{asofyear}$form->{asofmonth}$form->{asofdate}";
    334. 

  334.       }
    335. 

  335.     }
    336. 

  336.   } else {
    337. 

  337.     if ($form->{asofyear} && $form->{asofmonth}) {
    338. 

  338.       ($null, $form->{asofdate}) = $form->from_to($form->{asofyear}, $form->{asofmonth});
    339. 

  339.     }
    340. 

  340.   }
    341. 

  341.   
    342. 

  342.   # if there are any dates construct a where
    343. 

  343.   if ($form->{asofdate}) {
    344. 

  344.     
    345. 

  345.     $form->{this_period} = "$form->{asofdate}";
    346. 

  346.     $form->{period} = "$form->{asofdate}";
    347. 

  347.     
    348. 

  348.   }
    349. 

  349. 

  350.   $form->{decimalplaces} *= 1;
    351. 

  351. 

  352.   &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories, 1);
    353. 

  353.   
    354. 

  354.   if ($form->{compareasofdate}) {
    355. 

  355.     if ($form->{compareasofyear} && $form->{compareasofmonth}) {
    356. 

  356.       if ($form->{compareasofdate} !~ /\W/) {
    357. 

  357.     $form->{compareasofdate} = "$form->{compareasofyear}$form->{compareasofmonth}$form->{compareasofdate}";
    358. 

  358.       }
    359. 

  359.     }
    360. 

  360.   } else {
    361. 

  361.     if ($form->{compareasofyear} && $form->{compareasofmonth}) {
    362. 

  362.       ($null, $form->{compareasofdate}) = $form->from_to($form->{compareasofyear}, $form->{compareasofmonth});
    363. 

  363.     }
    364. 

  364.   }
    365. 

  365.   
    366. 

  366.   # if there are any compare dates
    367. 

  367.   if ($form->{compareasofdate}) {
    368. 

  368. 

  369.     $last_period = 1;
    370. 

  370.     &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories, 1);
    371. 

  371.   
    372. 

  372.     $form->{last_period} = "$form->{compareasofdate}";
    373. 

  373. 

  374.   }  
    375. 

  375. 

  376.   
    377. 

  377.   # disconnect
    378. 

  378.   $dbh->disconnect;
    379. 

  379. 

  380. 

  381.   # now we got $form->{A}{accno}{ }    assets
    382. 

  382.   # and $form->{L}{accno}{ }           liabilities
    383. 

  383.   # and $form->{Q}{accno}{ }           equity
    384. 

  384.   # build asset accounts
    385. 

  385.   
    386. 

  386.   my $str;
    387. 

  387.   my $key;
    388. 

  388.   
    389. 

  389.   my %account  = ( 'A' => { 'label' => 'asset',
    390. 

  390.                             'labels' => 'assets',
    391. 

  391.                 'ml' => -1 },
    392. 

  392.            'L' => { 'label' => 'liability',
    393. 

  393.                     'labels' => 'liabilities',
    394. 

  394.                 'ml' => 1 },
    395. 

  395.            'Q' => { 'label' => 'equity',
    396. 

  396.                     'labels' => 'equity',
    397. 

  397.                 'ml' => 1 }
    398. 

  398.         );      
    399. 

  399. 

  400. 

  401.    foreach $category (@categories) {                
    402. 

  402. 

  403.     foreach $key (sort keys %{ $form->{$category} }) {
    404. 

  404. 

  405.       $str = ($form->{l_heading}) ? $form->{padding} : "";
    406. 

  406. 

  407.       if ($form->{$category}{$key}{charttype} eq "A") {
    408. 

  408.     $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
    409. 

  409.       }
    410. 

  410.       if ($form->{$category}{$key}{charttype} eq "H") {
    411. 

  411.     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
    412. 

  412.       $dash = "- ";
    413. 

  413.       push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
    414. 

  414.       push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    415. 

  415.       
    416. 

  416.       if ($last_period) {
    417. 

  417.         push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    418. 

  418.       }
    419. 

  419.     }
    420. 

  420. 

  421.     $str = "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
    422. 

  422.     
    423. 

  423.     $account{$category}{subthis} = $form->{$category}{$key}{this};
    424. 

  424.     $account{$category}{sublast} = $form->{$category}{$key}{last};
    425. 

  425.     $account{$category}{subdescription} = $form->{$category}{$key}{description};
    426. 

  426.     $account{$category}{subtotal} = 1;
    427. 

  427.     
    428. 

  428.     $form->{$category}{$key}{this} = 0;
    429. 

  429.     $form->{$category}{$key}{last} = 0;
    430. 

  430. 

  431.     next unless $form->{l_heading};
    432. 

  432. 

  433.     $dash = " ";
    434. 

  434.       }
    435. 

  435.       
    436. 

  436.       # push description onto array
    437. 

  437.       push(@{$form->{"$account{$category}{label}_account"}}, $str);
    438. 

  438.       
    439. 

  439.       if ($form->{$category}{$key}{charttype} eq 'A') {
    440. 

  440.     $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
    441. 

  441.     $dash = "- ";
    442. 

  442.       }
    443. 

  443. 

  444.       push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    445. 

  445.       
    446. 

  446.       if ($last_period) {
    447. 

  447.     $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
    448. 

  448. 

  449.     push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    450. 

  450.       }
    451. 

  451.     }
    452. 

  452. 

  453.     $str = ($form->{l_heading}) ? $form->{padding} : "";
    454. 

  454.     if ($account{$category}{subtotal} && $form->{l_subtotal}) {
    455. 

  455.       push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
    456. 

  456.       push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    457. 

  457.       
    458. 

  458.       if ($last_period) {
    459. 

  459.     push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
    460. 

  460.       }
    461. 

  461.     }
    462. 

  462. 

  463.   }
    464. 

  464. 

  465.   
    466. 

  466.   # totals for assets, liabilities
    467. 

  467.   $form->{total_assets_this_period} = $form->round_amount($form->{total_assets_this_period}, $form->{decimalplaces});
    468. 

  468.   $form->{total_liabilities_this_period} = $form->round_amount($form->{total_liabilities_this_period}, $form->{decimalplaces});
    469. 

  469.   $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period}, $form->{decimalplaces});
    470. 

  470. 

  471.   # calculate earnings
    472. 

  472.   $form->{earnings_this_period} = $form->{total_assets_this_period} - $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
    473. 

  473. 

  474.   push(@{$form->{equity_this_period}}, $form->format_amount($myconfig, $form->{earnings_this_period}, $form->{decimalplaces}, "- "));
    475. 

  475.   
    476. 

  476.   $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period} + $form->{earnings_this_period}, $form->{decimalplaces});
    477. 

  477.   
    478. 

  478.   # add liability + equity
    479. 

  479.   $form->{total_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period} + $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
    480. 

  480. 

  481. 

  482.   if ($last_period) {
    483. 

  483.     # totals for assets, liabilities
    484. 

  484.     $form->{total_assets_last_period} = $form->round_amount($form->{total_assets_last_period}, $form->{decimalplaces});
    485. 

  485.     $form->{total_liabilities_last_period} = $form->round_amount($form->{total_liabilities_last_period}, $form->{decimalplaces});
    486. 

  486.     $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period}, $form->{decimalplaces});
    487. 

  487. 

  488.     # calculate retained earnings
    489. 

  489.     $form->{earnings_last_period} = $form->{total_assets_last_period} - $form->{total_liabilities_last_period} - $form->{total_equity_last_period};
    490. 

  490. 

  491.     push(@{$form->{equity_last_period}}, $form->format_amount($myconfig,$form->{earnings_last_period}, $form->{decimalplaces}, "- "));
    492. 

  492.     
    493. 

  493.     $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period} + $form->{earnings_last_period}, $form->{decimalplaces});
    494. 

  494. 

  495.     # add liability + equity
    496. 

  496.     $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period} + $form->{total_equity_last_period}, $form->{decimalplaces}, "- ");
    497. 

  497. 

  498.   }
    499. 

  499. 

  500.   
    501. 

  501.   $form->{total_liabilities_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_liabilities_last_period});
    502. 

  502.   
    503. 

  503.   $form->{total_equity_last_period} = $form->format_amount($myconfig, $form->{total_equity_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_equity_last_period});
    504. 

  504.   
    505. 

  505.   $form->{total_assets_last_period} = $form->format_amount($myconfig, $form->{total_assets_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_assets_last_period});
    506. 

  506.   
    507. 

  507.   $form->{total_assets_this_period} = $form->format_amount($myconfig, $form->{total_assets_this_period}, $form->{decimalplaces}, "- ");
    508. 

  508.   
    509. 

  509.   $form->{total_liabilities_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period}, $form->{decimalplaces}, "- ");
    510. 

  510.   
    511. 

  511.   $form->{total_equity_this_period} = $form->format_amount($myconfig, $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
    512. 

  512. 

  513. }
    514. 

  514. 

  515. 

  516. sub get_accounts {
    517. 

  517.   my ($dbh, $last_period, $fromdate, $todate, $form, $categories, $excludeyearend) = @_;
    518. 

  518. 

  519.   my $department_id;
    520. 

  520.   my $project_id;
    521. 

  521.   
    522. 

  522.   ($null, $department_id) = split /--/, $form->{department};
    523. 

  523.   ($null, $project_id) = split /--/, $form->{projectnumber};
    524. 

  524. 

  525.   my $query;
    526. 

  526.   my $dpt_where;
    527. 

  527.   my $dpt_join;
    528. 

  528.   my $project;
    529. 

  529.   my $where = "1 = 1";
    530. 

  530.   my $glwhere = "";
    531. 

  531.   my $subwhere = "";
    532. 

  532.   my $yearendwhere = "1 = 1";
    533. 

  533.   my $item;
    534. 

  534.  
    535. 

  535.   my $category = "AND (";
    536. 

  536.   foreach $item (@{ $categories }) {
    537. 

  537.     $category .= qq|c.category = '$item' OR |;
    538. 

  538.   }
    539. 

  539.   $category =~ s/OR $/\)/;
    540. 

  540. 

  541. 

  542.   # get headings
    543. 

  543.   $query = qq|SELECT accno, description, category
    544. 

  544.           FROM chart c
    545. 

  545.           WHERE c.charttype = 'H'
    546. 

  546.           $category
    547. 

  547.           ORDER by c.accno|;
    548. 

  548. 

  549.   if ($form->{accounttype} eq 'gifi')
    550. 

  550.   {
    551. 

  551.     $query = qq|SELECT g.accno, g.description, c.category
    552. 

  552.         FROM gifi g
    553. 

  553.         JOIN chart c ON (c.gifi_accno = g.accno)
    554. 

  554.         WHERE c.charttype = 'H'
    555. 

  555.         $category
    556. 

  556.         ORDER BY g.accno|;
    557. 

  557.   }
    558. 

  558. 

  559.   $sth = $dbh->prepare($query);
    560. 

  560.   $sth->execute || $form->dberror($query);
    561. 

  561.   
    562. 

  562.   my @headingaccounts = ();
    563. 

  563.   while ($ref = $sth->fetchrow_hashref(NAME_lc))
    564. 

  564.   {
    565. 

  565.     $form->{$ref->{category}}{$ref->{accno}}{description} = "$ref->{description}";
    566. 

  566.     $form->{$ref->{category}}{$ref->{accno}}{charttype} = "H";
    567. 

  567.     $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
    568. 

  568.     
    569. 

  569.     push @headingaccounts, $ref->{accno};
    570. 

  570.   }
    571. 

  571. 

  572.   $sth->finish;
    573. 

  573. 

  574.   if ($form->{method} eq 'cash' && !$todate) {
    575. 

  575.     ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
    576. 

  576.   }
    577. 

  577. 

  578.   if ($fromdate) {
    579. 

  579.     if ($form->{method} eq 'cash') {
    580. 

  580.       $subwhere .= " AND transdate >= '$fromdate'";
    581. 

  581.       $glwhere = " AND ac.transdate >= '$fromdate'";
    582. 

  582.     } else {
    583. 

  583.       $where .= " AND ac.transdate >= '$fromdate'";
    584. 

  584.     }
    585. 

  585.   }
    586. 

  586. 

  587.   if ($todate) {
    588. 

  588.     $where .= " AND ac.transdate <= '$todate'";
    589. 

  589.     $subwhere .= " AND transdate <= '$todate'";
    590. 

  590.     $yearendwhere = "ac.transdate < '$todate'";
    591. 

  591.   }
    592. 

  592. 

  593.   if ($excludeyearend) {
    594. 

  594.     $ywhere = " AND ac.trans_id NOT IN
    595. 

  595.                (SELECT trans_id FROM yearend)";
    596. 

  596.            
    597. 

  597.    if ($todate) {
    598. 

  598.       $ywhere = " AND ac.trans_id NOT IN
    599. 

  599.          (SELECT trans_id FROM yearend
    600. 

  600.           WHERE transdate <= '$todate')";
    601. 

  601.     }
    602. 

  602.        
    603. 

  603.     if ($fromdate) {
    604. 

  604.       $ywhere = " AND ac.trans_id NOT IN
    605. 

  605.          (SELECT trans_id FROM yearend
    606. 

  606.           WHERE transdate >= '$fromdate')";
    607. 

  607.       if ($todate) {
    608. 

  608.     $ywhere = " AND ac.trans_id NOT IN
    609. 

  609.            (SELECT trans_id FROM yearend
    610. 

  610.             WHERE transdate >= '$fromdate'
    611. 

  611.             AND transdate <= '$todate')";
    612. 

  612.       }
    613. 

  613.     }
    614. 

  614.   }
    615. 

  615. 

  616.   if ($department_id) {
    617. 

  617.     $dpt_join = qq|
    618. 

  618.                JOIN department t ON (a.department_id = t.id)
    619. 

  619.           |;
    620. 

  620.     $dpt_where = qq|
    621. 

  621.                AND t.id = $department_id
    622. 

  622.                |;
    623. 

  623.   }
    624. 

  624. 

  625.   if ($project_id) {
    626. 

  626.     $project = qq|
    627. 

  627.                  AND ac.project_id = $project_id
    628. 

  628.          |;
    629. 

  629.   }
    630. 

  630. 

  631. 

  632.   if ($form->{accounttype} eq 'gifi') {
    633. 

  633.     
    634. 

  634.     if ($form->{method} eq 'cash') {
    635. 

  635. 

  636.     $query = qq|
    637. 

  637.     
    638. 

  638.              SELECT g.accno, sum(ac.amount) AS amount,
    639. 

  639.          g.description, c.category
    640. 

  640.          FROM acc_trans ac
    641. 

  641.              JOIN chart c ON (c.id = ac.chart_id)
    642. 

  642.              JOIN ar a ON (a.id = ac.trans_id)
    643. 

  643.              JOIN gifi g ON (g.accno = c.gifi_accno)
    644. 

  644.              $dpt_join
    645. 

  645.          WHERE $where
    646. 

  646.          $ywhere
    647. 

  647.          $dpt_where
    648. 

  648.          $category
    649. 

  649.          AND ac.trans_id IN
    650. 

  650.            (
    651. 

  651.              SELECT trans_id
    652. 

  652.              FROM acc_trans
    653. 

  653.              JOIN chart ON (chart_id = id)
    654. 

  654.              WHERE link LIKE '%AR_paid%'
    655. 

  655.              $subwhere
    656. 

  656.            )
    657. 

  657.          $project
    658. 

  658.          GROUP BY g.accno, g.description, c.category
    659. 

  659.          
    660. 

  660.        UNION ALL
    661. 

  661.        
    662. 

  662.          SELECT '' AS accno, SUM(ac.amount) AS amount,
    663. 

  663.          '' AS description, c.category
    664. 

  664.          FROM acc_trans ac
    665. 

  665.              JOIN chart c ON (c.id = ac.chart_id)
    666. 

  666.              JOIN ar a ON (a.id = ac.trans_id)
    667. 

  667.              $dpt_join
    668. 

  668.          WHERE $where
    669. 

  669.          $ywhere
    670. 

  670.          $dpt_where
    671. 

  671.          $category
    672. 

  672.          AND c.gifi_accno = ''
    673. 

  673.          AND ac.trans_id IN
    674. 

  674.            (
    675. 

  675.              SELECT trans_id
    676. 

  676.              FROM acc_trans
    677. 

  677.              JOIN chart ON (chart_id = id)
    678. 

  678.              WHERE link LIKE '%AR_paid%'
    679. 

  679.              $subwhere
    680. 

  680.            )
    681. 

  681.          $project
    682. 

  682.          GROUP BY c.category
    683. 

  683. 

  684.        UNION ALL
    685. 

  685. 

  686.                  SELECT g.accno, sum(ac.amount) AS amount,
    687. 

  687.          g.description, c.category
    688. 

  688.          FROM acc_trans ac
    689. 

  689.              JOIN chart c ON (c.id = ac.chart_id)
    690. 

  690.              JOIN ap a ON (a.id = ac.trans_id)
    691. 

  691.              JOIN gifi g ON (g.accno = c.gifi_accno)
    692. 

  692.              $dpt_join
    693. 

  693.          WHERE $where
    694. 

  694.          $ywhere
    695. 

  695.          $dpt_where
    696. 

  696.          $category
    697. 

  697.          AND ac.trans_id IN
    698. 

  698.            (
    699. 

  699.              SELECT trans_id
    700. 

  700.              FROM acc_trans
    701. 

  701.              JOIN chart ON (chart_id = id)
    702. 

  702.              WHERE link LIKE '%AP_paid%'
    703. 

  703.              $subwhere
    704. 

  704.            )
    705. 

  705.          $project
    706. 

  706.          GROUP BY g.accno, g.description, c.category
    707. 

  707.          
    708. 

  708.        UNION ALL
    709. 

  709.        
    710. 

  710.          SELECT '' AS accno, SUM(ac.amount) AS amount,
    711. 

  711.          '' AS description, c.category
    712. 

  712.          FROM acc_trans ac
    713. 

  713.              JOIN chart c ON (c.id = ac.chart_id)
    714. 

  714.              JOIN ap a ON (a.id = ac.trans_id)
    715. 

  715.              $dpt_join
    716. 

  716.          WHERE $where
    717. 

  717.          $ywhere
    718. 

  718.          $dpt_where
    719. 

  719.          $category
    720. 

  720.          AND c.gifi_accno = ''
    721. 

  721.          AND ac.trans_id IN
    722. 

  722.            (
    723. 

  723.              SELECT trans_id
    724. 

  724.              FROM acc_trans
    725. 

  725.              JOIN chart ON (chart_id = id)
    726. 

  726.              WHERE link LIKE '%AP_paid%'
    727. 

  727.              $subwhere
    728. 

  728.            )
    729. 

  729.          $project
    730. 

  730.          GROUP BY c.category
    731. 

  731. 

  732.        UNION ALL
    733. 

  733. 

  734. -- add gl
    735. 

  735.     
    736. 

  736.              SELECT g.accno, sum(ac.amount) AS amount,
    737. 

  737.          g.description, c.category
    738. 

  738.          FROM acc_trans ac
    739. 

  739.              JOIN chart c ON (c.id = ac.chart_id)
    740. 

  740.              JOIN gifi g ON (g.accno = c.gifi_accno)
    741. 

  741.              JOIN gl a ON (a.id = ac.trans_id)
    742. 

  742.              $dpt_join
    743. 

  743.          WHERE $where
    744. 

  744.          $ywhere
    745. 

  745.          $glwhere
    746. 

  746.          $dpt_where
    747. 

  747.          $category
    748. 

  748.          AND NOT (c.link = 'AR' OR c.link = 'AP')
    749. 

  749.          $project
    750. 

  750.          GROUP BY g.accno, g.description, c.category
    751. 

  751.          
    752. 

  752.        UNION ALL
    753. 

  753.        
    754. 

  754.          SELECT '' AS accno, SUM(ac.amount) AS amount,
    755. 

  755.          '' AS description, c.category
    756. 

  756.          FROM acc_trans ac
    757. 

  757.              JOIN chart c ON (c.id = ac.chart_id)
    758. 

  758.              JOIN gl a ON (a.id = ac.trans_id)
    759. 

  759.              $dpt_join
    760. 

  760.          WHERE $where
    761. 

  761.          $ywhere
    762. 

  762.          $glwhere
    763. 

  763.          $dpt_where
    764. 

  764.          $category
    765. 

  765.          AND c.gifi_accno = ''
    766. 

  766.          AND NOT (c.link = 'AR' OR c.link = 'AP')
    767. 

  767.          $project
    768. 

  768.          GROUP BY c.category
    769. 

  769.          |;
    770. 

  770. 

  771.       if ($excludeyearend) {
    772. 

  772. 

  773.          # this is for the yearend
    774. 

  774. 

  775.      $query .= qq|
    776. 

  776. 

  777.        UNION ALL
    778. 

  778.        
    779. 

  779.              SELECT g.accno, sum(ac.amount) AS amount,
    780. 

  780.          g.description, c.category
    781. 

  781.          FROM yearend y
    782. 

  782.          JOIN gl a ON (a.id = y.trans_id)
    783. 

  783.          JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
    784. 

  784.          JOIN chart c ON (c.id = ac.chart_id)
    785. 

  785.          JOIN gifi g ON (g.accno = c.gifi_accno)
    786. 

  786.              $dpt_join
    787. 

  787.          WHERE $yearendwhere
    788. 

  788.          AND c.category = 'Q'
    789. 

  789.          $dpt_where
    790. 

  790.          $project
    791. 

  791.          GROUP BY g.accno, g.description, c.category
    792. 

  792.          |;
    793. 

  793.       }
    794. 

  794. 

  795.     } else {
    796. 

  796. 

  797.       if ($department_id) {
    798. 

  798.     $dpt_join = qq|
    799. 

  799.           JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
    800. 

  800.           |;
    801. 

  801.     $dpt_where = qq|
    802. 

  802.                AND t.department_id = $department_id
    803. 

  803.           |;
    804. 

  804.       }
    805. 

  805. 

  806.       $query = qq|
    807. 

  807.       
    808. 

  808.           SELECT g.accno, SUM(ac.amount) AS amount,
    809. 

  809.           g.description, c.category
    810. 

  810.           FROM acc_trans ac
    811. 

  811.           JOIN chart c ON (c.id = ac.chart_id)
    812. 

  812.           JOIN gifi g ON (c.gifi_accno = g.accno)
    813. 

  813.           $dpt_join
    814. 

  814.           WHERE $where
    815. 

  815.           $ywhere
    816. 

  816.           $dpt_where
    817. 

  817.           $category
    818. 

  818.           $project
    819. 

  819.           GROUP BY g.accno, g.description, c.category
    820. 

  820.           
    821. 

  821.        UNION ALL
    822. 

  822.        
    823. 

  823.           SELECT '' AS accno, SUM(ac.amount) AS amount,
    824. 

  824.           '' AS description, c.category
    825. 

  825.           FROM acc_trans ac
    826. 

  826.           JOIN chart c ON (c.id = ac.chart_id)
    827. 

  827.           $dpt_join
    828. 

  828.           WHERE $where
    829. 

  829.           $ywhere
    830. 

  830.           $dpt_where
    831. 

  831.           $category
    832. 

  832.           AND c.gifi_accno = ''
    833. 

  833.           $project
    834. 

  834.           GROUP BY c.category
    835. 

  835.           |;
    836. 

  836. 

  837.     if ($excludeyearend) {
    838. 

  838. 

  839.       # this is for the yearend
    840. 

  840. 

  841.       $query .= qq|
    842. 

  842. 

  843.        UNION ALL
    844. 

  844.        
    845. 

  845.              SELECT g.accno, sum(ac.amount) AS amount,
    846. 

  846.          g.description, c.category
    847. 

  847.          FROM yearend y
    848. 

  848.          JOIN gl a ON (a.id = y.trans_id)
    849. 

  849.          JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
    850. 

  850.          JOIN chart c ON (c.id = ac.chart_id)
    851. 

  851.          JOIN gifi g ON (g.accno = c.gifi_accno)
    852. 

  852.              $dpt_join
    853. 

  853.          WHERE $yearendwhere
    854. 

  854.          AND c.category = 'Q'
    855. 

  855.          $dpt_where
    856. 

  856.          $project
    857. 

  857.          GROUP BY g.accno, g.description, c.category
    858. 

  858.           |;
    859. 

  859.     }
    860. 

  860.     }
    861. 

  861.     
    862. 

  862.   } else {    # standard account
    863. 

  863. 

  864.     if ($form->{method} eq 'cash') {
    865. 

  865. 

  866.       $query = qq|
    867. 

  867.     
    868. 

  868.              SELECT c.accno, sum(ac.amount) AS amount,
    869. 

  869.          c.description, c.category
    870. 

  870.          FROM acc_trans ac
    871. 

  871.          JOIN chart c ON (c.id = ac.chart_id)
    872. 

  872.          JOIN ar a ON (a.id = ac.trans_id)
    873. 

  873.          $dpt_join
    874. 

  874.          WHERE $where
    875. 

  875.              $ywhere
    876. 

  876.          $dpt_where
    877. 

  877.          $category
    878. 

  878.          AND ac.trans_id IN
    879. 

  879.            (
    880. 

  880.              SELECT trans_id
    881. 

  881.              FROM acc_trans
    882. 

  882.              JOIN chart ON (chart_id = id)
    883. 

  883.              WHERE link LIKE '%AR_paid%'
    884. 

  884.              $subwhere
    885. 

  885.            )
    886. 

  886.              
    887. 

  887.          $project
    888. 

  888.          GROUP BY c.accno, c.description, c.category
    889. 

  889.          
    890. 

  890.     UNION ALL
    891. 

  891.     
    892. 

  892.              SELECT c.accno, sum(ac.amount) AS amount,
    893. 

  893.          c.description, c.category
    894. 

  894.          FROM acc_trans ac
    895. 

  895.          JOIN chart c ON (c.id = ac.chart_id)
    896. 

  896.          JOIN ap a ON (a.id = ac.trans_id)
    897. 

  897.          $dpt_join
    898. 

  898.          WHERE $where
    899. 

  899.              $ywhere
    900. 

  900.          $dpt_where
    901. 

  901.          $category
    902. 

  902.          AND ac.trans_id IN
    903. 

  903.            (
    904. 

  904.              SELECT trans_id
    905. 

  905.              FROM acc_trans
    906. 

  906.              JOIN chart ON (chart_id = id)
    907. 

  907.              WHERE link LIKE '%AP_paid%'
    908. 

  908.              $subwhere
    909. 

  909.            )
    910. 

  910.              
    911. 

  911.          $project
    912. 

  912.          GROUP BY c.accno, c.description, c.category
    913. 

  913.          
    914. 

  914.         UNION ALL
    915. 

  915. 

  916.          SELECT c.accno, sum(ac.amount) AS amount,
    917. 

  917.          c.description, c.category
    918. 

  918.          FROM acc_trans ac
    919. 

  919.          JOIN chart c ON (c.id = ac.chart_id)
    920. 

  920.          JOIN gl a ON (a.id = ac.trans_id)
    921. 

  921.          $dpt_join
    922. 

  922.          WHERE $where
    923. 

  923.              $ywhere
    924. 

  924.          $glwhere
    925. 

  925.          $dpt_where
    926. 

  926.          $category
    927. 

  927.          AND NOT (c.link = 'AR' OR c.link = 'AP')
    928. 

  928.          $project
    929. 

  929.          GROUP BY c.accno, c.description, c.category
    930. 

  930.          |;
    931. 

  931. 

  932.       if ($excludeyearend) {
    933. 

  933. 

  934.         # this is for the yearend
    935. 

  935.     
    936. 

  936.     $query .= qq|
    937. 

  937. 

  938.        UNION ALL
    939. 

  939.        
    940. 

  940.              SELECT c.accno, sum(ac.amount) AS amount,
    941. 

  941.          c.description, c.category
    942. 

  942.          FROM yearend y
    943. 

  943.          JOIN gl a ON (a.id = y.trans_id)
    944. 

  944.          JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
    945. 

  945.          JOIN chart c ON (c.id = ac.chart_id)
    946. 

  946.              $dpt_join
    947. 

  947.          WHERE $yearendwhere
    948. 

  948.          AND c.category = 'Q'
    949. 

  949.          $dpt_where
    950. 

  950.          $project
    951. 

  951.          GROUP BY c.accno, c.description, c.category
    952. 

  952.          |;
    953. 

  953.       }
    954. 

  954. 

  955.     } else {
    956. 

  956.      
    957. 

  957.       if ($department_id) {
    958. 

  958.     $dpt_join = qq|
    959. 

  959.           JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
    960. 

  960.           |;
    961. 

  961.     $dpt_where = qq|
    962. 

  962.                AND t.department_id = $department_id
    963. 

  963.           |;
    964. 

  964.       }
    965. 

  965. 

  966.     
    967. 

  967.       $query = qq|
    968. 

  968.       
    969. 

  969.          SELECT c.accno, sum(ac.amount) AS amount,
    970. 

  970.          c.description, c.category
    971. 

  971.          FROM acc_trans ac
    972. 

  972.          JOIN chart c ON (c.id = ac.chart_id)
    973. 

  973.          $dpt_join
    974. 

  974.          WHERE $where
    975. 

  975.              $ywhere
    976. 

  976.          $dpt_where
    977. 

  977.          $category
    978. 

  978.          $project
    979. 

  979.          GROUP BY c.accno, c.description, c.category
    980. 

  980.          |;
    981. 

  981. 

  982.       if ($excludeyearend) {
    983. 

  983. 

  984.         # this is for the yearend
    985. 

  985.     
    986. 

  986.     $query .= qq|
    987. 

  987. 

  988.        UNION ALL
    989. 

  989.        
    990. 

  990.              SELECT c.accno, sum(ac.amount) AS amount,
    991. 

  991.          c.description, c.category
    992. 

  992.          FROM yearend y
    993. 

  993.          JOIN gl a ON (a.id = y.trans_id)
    994. 

  994.          JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
    995. 

  995.          JOIN chart c ON (c.id = ac.chart_id)
    996. 

  996.              $dpt_join
    997. 

  997.          WHERE $yearendwhere
    998. 

  998.          AND c.category = 'Q'
    999. 

  999.          $dpt_where
    1000. 

  1000.          $project
    1001. 

  1001.          GROUP BY c.accno, c.description, c.category
    1002. 

  1002.          |;
    1003. 

  1003.       }
    1004. 

  1004.     }
    1005. 

  1005.   }
    1006. 

  1006. 

  1007.   my @accno;
    1008. 

  1008.   my $accno;
    1009. 

  1009.   my $ref;
    1010. 

  1010.   
    1011. 

  1011.   my $sth = $dbh->prepare($query);
    1012. 

  1012.   $sth->execute || $form->dberror($query);
    1013. 

  1013. 

  1014.   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    1015. 

  1015. 

  1016.     # get last heading account
    1017. 

  1017.     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
    1018. 

  1018.     $accno = pop @accno;
    1019. 

  1019.     if ($accno && ($accno ne $ref->{accno}) ) {
    1020. 

  1020.       if ($last_period)
    1021. 

  1021.       {
    1022. 

  1022.     $form->{$ref->{category}}{$accno}{last} += $ref->{amount};
    1023. 

  1023.       } else {
    1024. 

  1024.     $form->{$ref->{category}}{$accno}{this} += $ref->{amount};
    1025. 

  1025.       }
    1026. 

  1026.     }
    1027. 

  1027.     
    1028. 

  1028.     $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
    1029. 

  1029.     $form->{$ref->{category}}{$ref->{accno}}{description} = $ref->{description};
    1030. 

  1030.     $form->{$ref->{category}}{$ref->{accno}}{charttype} = "A";
    1031. 

  1031.     
    1032. 

  1032.     if ($last_period) {
    1033. 

  1033.       $form->{$ref->{category}}{$ref->{accno}}{last} += $ref->{amount};
    1034. 

  1034.     } else {
    1035. 

  1035.       $form->{$ref->{category}}{$ref->{accno}}{this} += $ref->{amount};
    1036. 

  1036.     }
    1037. 

  1037.   }
    1038. 

  1038.   $sth->finish;
    1039. 

  1039. 

  1040.   
    1041. 

  1041.   # remove accounts with zero balance
    1042. 

  1042.   foreach $category (@{ $categories }) {
    1043. 

  1043.     foreach $accno (keys %{ $form->{$category} }) {
    1044. 

  1044.       $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $form->{decimalplaces});
    1045. 

  1045.       $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $form->{decimalplaces});
    1046. 

  1046. 

  1047.       delete $form->{$category}{$accno} if ($form->{$category}{$accno}{this} == 0 && $form->{$category}{$accno}{last} == 0);
    1048. 

  1048.     }
    1049. 

  1049.   }
    1050. 

  1050. 

  1051. }
    1052. 

  1052. 

  1053. 

  1054. 

  1055. sub trial_balance {
    1056. 

  1056.   my ($self, $myconfig, $form) = @_;
    1057. 

  1057. 

  1058.   my $dbh = $form->dbconnect($myconfig);
    1059. 

  1059. 

  1060.   my ($query, $sth, $ref);
    1061. 

  1061.   my %balance = ();
    1062. 

  1062.   my %trb = ();
    1063. 

  1063.   my $null;
    1064. 

  1064.   my $department_id;
    1065. 

  1065.   my $project_id;
    1066. 

  1066.   my @headingaccounts = ();
    1067. 

  1067.   my $dpt_where;
    1068. 

  1068.   my $dpt_join;
    1069. 

  1069.   my $project;
    1070. 

  1070. 

  1071.   my $where = "1 = 1";
    1072. 

  1072.   my $invwhere = $where;
    1073. 

  1073.   
    1074. 

  1074.   ($null, $department_id) = split /--/, $form->{department};
    1075. 

  1075.   ($null, $project_id) = split /--/, $form->{projectnumber};
    1076. 

  1076. 

  1077.   if ($department_id) {
    1078. 

  1078.     $dpt_join = qq|
    1079. 

  1079.                 JOIN dpt_trans t ON (ac.trans_id = t.trans_id)
    1080. 

  1080.           |;
    1081. 

  1081.     $dpt_where = qq|
    1082. 

  1082.                 AND t.department_id = $department_id
    1083. 

  1083.         |;
    1084. 

  1084.   }
    1085. 

  1085.   
    1086. 

  1086.   
    1087. 

  1087.   if ($project_id) {
    1088. 

  1088.     $project = qq|
    1089. 

  1089.                 AND ac.project_id = $project_id
    1090. 

  1090.         |;
    1091. 

  1091.   }
    1092. 

  1092.   
    1093. 

  1093.   ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; 
    1094. 

  1094.    
    1095. 

  1095.   # get beginning balances
    1096. 

  1096.   if ($form->{fromdate}) {
    1097. 

  1097. 

  1098.     if ($form->{accounttype} eq 'gifi') {
    1099. 

  1099.       
    1100. 

  1100.       $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
    1101. 

  1101.                   g.description, c.contra
    1102. 

  1102.           FROM acc_trans ac
    1103. 

  1103.           JOIN chart c ON (ac.chart_id = c.id)
    1104. 

  1104.           JOIN gifi g ON (c.gifi_accno = g.accno)
    1105. 

  1105.           $dpt_join
    1106. 

  1106.           WHERE ac.transdate < '$form->{fromdate}'
    1107. 

  1107.           $dpt_where
    1108. 

  1108.           $project
    1109. 

  1109.           GROUP BY g.accno, c.category, g.description, c.contra
    1110. 

  1110.           |;
    1111. 

  1111.    
    1112. 

  1112.     } else {
    1113. 

  1113.       
    1114. 

  1114.       $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
    1115. 

  1115.                   c.description, c.contra
    1116. 

  1116.           FROM acc_trans ac
    1117. 

  1117.           JOIN chart c ON (ac.chart_id = c.id)
    1118. 

  1118.           $dpt_join
    1119. 

  1119.           WHERE ac.transdate < '$form->{fromdate}'
    1120. 

  1120.           $dpt_where
    1121. 

  1121.           $project
    1122. 

  1122.           GROUP BY c.accno, c.category, c.description, c.contra
    1123. 

  1123.           |;
    1124. 

  1124.           
    1125. 

  1125.     }
    1126. 

  1126. 

  1127.     $sth = $dbh->prepare($query);
    1128. 

  1128.     $sth->execute || $form->dberror($query);
    1129. 

  1129. 

  1130.     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    1131. 

  1131.       $ref->{amount} = $form->round_amount($ref->{amount}, 2);
    1132. 

  1132.       $balance{$ref->{accno}} = $ref->{amount};
    1133. 

  1133. 

  1134.       if ($form->{all_accounts}) {
    1135. 

  1135.     $trb{$ref->{accno}}{description} = $ref->{description};
    1136. 

  1136.     $trb{$ref->{accno}}{charttype} = 'A';
    1137. 

  1137.     $trb{$ref->{accno}}{category} = $ref->{category};
    1138. 

  1138.     $trb{$ref->{accno}}{contra} = $ref->{contra};
    1139. 

  1139.       }
    1140. 

  1140. 

  1141.     }
    1142. 

  1142.     $sth->finish;
    1143. 

  1143. 

  1144.   }
    1145. 

  1145.   
    1146. 

  1146. 

  1147.   # get headings
    1148. 

  1148.   $query = qq|SELECT c.accno, c.description, c.category
    1149. 

  1149.           FROM chart c
    1150. 

  1150.           WHERE c.charttype = 'H'
    1151. 

  1151.           ORDER by c.accno|;
    1152. 

  1152. 

  1153.   if ($form->{accounttype} eq 'gifi')
    1154. 

  1154.   {
    1155. 

  1155.     $query = qq|SELECT g.accno, g.description, c.category, c.contra
    1156. 

  1156.         FROM gifi g
    1157. 

  1157.         JOIN chart c ON (c.gifi_accno = g.accno)
    1158. 

  1158.         WHERE c.charttype = 'H'
    1159. 

  1159.         ORDER BY g.accno|;
    1160. 

  1160.   }
    1161. 

  1161. 

  1162.   $sth = $dbh->prepare($query);
    1163. 

  1163.   $sth->execute || $form->dberror($query);
    1164. 

  1164.   
    1165. 

  1165.   while ($ref = $sth->fetchrow_hashref(NAME_lc))
    1166. 

  1166.   {
    1167. 

  1167.     $trb{$ref->{accno}}{description} = $ref->{description};
    1168. 

  1168.     $trb{$ref->{accno}}{charttype} = 'H';
    1169. 

  1169.     $trb{$ref->{accno}}{category} = $ref->{category};
    1170. 

  1170.     $trb{$ref->{accno}}{contra} = $ref->{contra};
    1171. 

  1171.    
    1172. 

  1172.     push @headingaccounts, $ref->{accno};
    1173. 

  1173.   }
    1174. 

  1174. 

  1175.   $sth->finish;
    1176. 

  1176. 

  1177. 

  1178.   if ($form->{fromdate} || $form->{todate}) {
    1179. 

  1179.     if ($form->{fromdate}) {
    1180. 

  1180.       $where .= " AND ac.transdate >= '$form->{fromdate}'";
    1181. 

  1181.       $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
    1182. 

  1182.     }
    1183. 

  1183.     if ($form->{todate}) {
    1184. 

  1184.       $where .= " AND ac.transdate <= '$form->{todate}'";
    1185. 

  1185.       $invwhere .= " AND a.transdate <= '$form->{todate}'";
    1186. 

  1186.     }
    1187. 

  1187.   }
    1188. 

  1188. 

  1189. 

  1190.   if ($form->{accounttype} eq 'gifi') {
    1191. 

  1191. 

  1192.     $query = qq|SELECT g.accno, g.description, c.category,
    1193. 

  1193.                 SUM(ac.amount) AS amount, c.contra
    1194. 

  1194.         FROM acc_trans ac
    1195. 

  1195.         JOIN chart c ON (c.id = ac.chart_id)
    1196. 

  1196.         JOIN gifi g ON (c.gifi_accno = g.accno)
    1197. 

  1197.         $dpt_join
    1198. 

  1198.         WHERE $where
    1199. 

  1199.         $dpt_where
    1200. 

  1200.         $project
    1201. 

  1201.         GROUP BY g.accno, g.description, c.category, c.contra
    1202. 

  1202.         ORDER BY accno|;
    1203. 

  1203.     
    1204. 

  1204.   } else {
    1205. 

  1205. 

  1206.     $query = qq|SELECT c.accno, c.description, c.category,
    1207. 

  1207.                 SUM(ac.amount) AS amount, c.contra
    1208. 

  1208.         FROM acc_trans ac
    1209. 

  1209.         JOIN chart c ON (c.id = ac.chart_id)
    1210. 

  1210.         $dpt_join
    1211. 

  1211.         WHERE $where
    1212. 

  1212.         $dpt_where
    1213. 

  1213.         $project
    1214. 

  1214.         GROUP BY c.accno, c.description, c.category, c.contra
    1215. 

  1215.                 ORDER BY accno|;
    1216. 

  1216. 

  1217.   }
    1218. 

  1218. 

  1219.   $sth = $dbh->prepare($query);
    1220. 

  1220.   $sth->execute || $form->dberror($query);
    1221. 

  1221. 

  1222.   # prepare query for each account
    1223. 

  1223.   $query = qq|SELECT (SELECT SUM(ac.amount) * -1
    1224. 

  1224.           FROM acc_trans ac
    1225. 

  1225.           JOIN chart c ON (c.id = ac.chart_id)
    1226. 

  1226.           $dpt_join
    1227. 

  1227.           WHERE $where
    1228. 

  1228.           $dpt_where
    1229. 

  1229.           $project
    1230. 

  1230.           AND ac.amount < 0
    1231. 

  1231.           AND c.accno = ?) AS debit,
    1232. 

  1232.           
    1233. 

  1233.          (SELECT SUM(ac.amount)
    1234. 

  1234.           FROM acc_trans ac
    1235. 

  1235.           JOIN chart c ON (c.id = ac.chart_id)
    1236. 

  1236.           $dpt_join
    1237. 

  1237.           WHERE $where
    1238. 

  1238.           $dpt_where
    1239. 

  1239.           $project
    1240. 

  1240.           AND ac.amount > 0
    1241. 

  1241.           AND c.accno = ?) AS credit
    1242. 

  1242.           |;
    1243. 

  1243. 

  1244.   if ($form->{accounttype} eq 'gifi') {
    1245. 

  1245. 

  1246.     $query = qq|SELECT (SELECT SUM(ac.amount) * -1
    1247. 

  1247.         FROM acc_trans ac
    1248. 

  1248.         JOIN chart c ON (c.id = ac.chart_id)
    1249. 

  1249.         $dpt_join
    1250. 

  1250.         WHERE $where
    1251. 

  1251.         $dpt_where
    1252. 

  1252.         $project
    1253. 

  1253.         AND ac.amount < 0
    1254. 

  1254.         AND c.gifi_accno = ?) AS debit,
    1255. 

  1255.         
    1256. 

  1256.            (SELECT SUM(ac.amount)
    1257. 

  1257.         FROM acc_trans ac
    1258. 

  1258.         JOIN chart c ON (c.id = ac.chart_id)
    1259. 

  1259.         $dpt_join
    1260. 

  1260.         WHERE $where
    1261. 

  1261.         $dpt_where
    1262. 

  1262.         $project
    1263. 

  1263.         AND ac.amount > 0
    1264. 

  1264.         AND c.gifi_accno = ?) AS credit|;
    1265. 

  1265.   
    1266. 

  1266.   }
    1267. 

  1267.   
    1268. 

  1268.   $drcr = $dbh->prepare($query);
    1269. 

  1269. 

  1270.   # calculate debit and credit for the period
    1271. 

  1271.   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    1272. 

  1272.     $trb{$ref->{accno}}{description} = $ref->{description};
    1273. 

  1273.     $trb{$ref->{accno}}{charttype} = 'A';
    1274. 

  1274.     $trb{$ref->{accno}}{category} = $ref->{category};
    1275. 

  1275.     $trb{$ref->{accno}}{contra} = $ref->{contra};
    1276. 

  1276.     $trb{$ref->{accno}}{amount} += $ref->{amount};
    1277. 

  1277.   }
    1278. 

  1278.   $sth->finish;
    1279. 

  1279. 

  1280.   my ($debit, $credit);
    1281. 

  1281.   
    1282. 

  1282.   foreach my $accno (sort keys %trb) {
    1283. 

  1283.     $ref = ();
    1284. 

  1284.     
    1285. 

  1285.     $ref->{accno} = $accno;
    1286. 

  1286.     for (qw(description category contra charttype amount)) { $ref->{$_} = $trb{$accno}{$_} }
    1287. 

  1287.     
    1288. 

  1288.     $ref->{balance} = $balance{$ref->{accno}};
    1289. 

  1289. 

  1290.     if ($trb{$accno}{charttype} eq 'A') {
    1291. 

  1291.       if ($project_id) {
    1292. 

  1292. 

  1293.         if ($ref->{amount} < 0) {
    1294. 

  1294.       $ref->{debit} = $ref->{amount} * -1;
    1295. 

  1295.     } else {
    1296. 

  1296.       $ref->{credit} = $ref->{amount};
    1297. 

  1297.     }
    1298. 

  1298.     next if $form->round_amount($ref->{amount}, 2) == 0;
    1299. 

  1299. 

  1300.       } else {
    1301. 

  1301.     
    1302. 

  1302.     # get DR/CR
    1303. 

  1303.     $drcr->execute($ref->{accno}, $ref->{accno});
    1304. 

  1304.     
    1305. 

  1305.     ($debit, $credit) = (0,0);
    1306. 

  1306.     while (($debit, $credit) = $drcr->fetchrow_array) {
    1307. 

  1307.       $ref->{debit} += $debit;
    1308. 

  1308.       $ref->{credit} += $credit;
    1309. 

  1309.     }
    1310. 

  1310.     $drcr->finish;
    1311. 

  1311. 

  1312.       }
    1313. 

  1313. 

  1314.       $ref->{debit} = $form->round_amount($ref->{debit}, 2);
    1315. 

  1315.       $ref->{credit} = $form->round_amount($ref->{credit}, 2);
    1316. 

  1316.     
    1317. 

  1317.       if (!$form->{all_accounts}) {
    1318. 

  1318.     next if $form->round_amount($ref->{debit} + $ref->{credit}, 2) == 0;
    1319. 

  1319.       }
    1320. 

  1320.     }
    1321. 

  1321. 

  1322.     # add subtotal
    1323. 

  1323.     @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
    1324. 

  1324.     $accno = pop @accno;
    1325. 

  1325.     if ($accno) {
    1326. 

  1326.       $trb{$accno}{debit} += $ref->{debit};
    1327. 

  1327.       $trb{$accno}{credit} += $ref->{credit};
    1328. 

  1328.     }
    1329. 

  1329. 

  1330.     push @{ $form->{TB} }, $ref;
    1331. 

  1331.     
    1332. 

  1332.   }
    1333. 

  1333. 

  1334.   $dbh->disconnect;
    1335. 

  1335. 

  1336.   # debits and credits for headings
    1337. 

  1337.   foreach $accno (@headingaccounts) {
    1338. 

  1338.     foreach $ref (@{ $form->{TB} }) {
    1339. 

  1339.       if ($accno eq $ref->{accno}) {
    1340. 

  1340.         $ref->{debit} = $trb{$accno}{debit};
    1341. 

  1341.         $ref->{credit} = $trb{$accno}{credit};
    1342. 

  1342.       }
    1343. 

  1343.     }
    1344. 

  1344.   }
    1345. 

  1345. 

  1346. }
    1347. 

  1347. 

  1348. 

  1349. sub aging {
    1350. 

  1350.   my ($self, $myconfig, $form) = @_;
    1351. 

  1351. 

  1352.   # connect to database
    1353. 

  1353.   my $dbh = $form->dbconnect($myconfig);
    1354. 

  1354.   my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
    1355. 

  1355. 

  1356.   my $query = qq|SELECT value FROM defaults WHERE settings_key = 'curr'|;
    1357. 

  1357.   ($form->{currencies}) = $dbh->selectrow_array($query);
    1358. 

  1358.   
    1359. 

  1359.   ($null, $form->{todate}) = $form->from_to($form->{year}, $form->{month}) if $form->{year} && $form->{month};
    1360. 

  1360.   
    1361. 

  1361.   if (! $form->{todate}) {
    1362. 

  1362.     $query = qq|SELECT current_date|;
    1363. 

  1363.     ($form->{todate}) = $dbh->selectrow_array($query);
    1364. 

  1364.   }
    1365. 

  1365.     
    1366. 

  1366.   my $where = "1 = 1";
    1367. 

  1367.   my $name;
    1368. 

  1368.   my $null;
    1369. 

  1369.   my $ref;
    1370. 

  1370.   my $transdate = ($form->{overdue}) ? "duedate" : "transdate";
    1371. 

  1371. 

  1372.   if ($form->{"$form->{ct}_id"}) {
    1373. 

  1373.     $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
    1374. 

  1374.   } else {
    1375. 

  1375.     if ($form->{$form->{ct}} ne "") {
    1376. 

  1376.       $name = $form->like(lc $form->{$form->{ct}});
    1377. 

  1377.       $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{$form->{ct}};
    1378. 

  1378.     }
    1379. 

  1379.   }
    1380. 

  1380. 

  1381.   if ($form->{department}) {
    1382. 

  1382.     ($null, $department_id) = split /--/, $form->{department};
    1383. 

  1383.     $where .= qq| AND a.department_id = $department_id|;
    1384. 

  1384.   }
    1385. 

  1385.   
    1386. 

  1386.   # select outstanding vendors or customers, depends on $ct
    1387. 

  1387.   $query = qq|SELECT DISTINCT ct.id, ct.name, ct.language_code
    1388. 

  1388.               FROM $form->{ct} ct
    1389. 

  1389.           JOIN $form->{arap} a ON (a.$form->{ct}_id = ct.id)
    1390. 

  1390.           WHERE $where
    1391. 

  1391.               AND a.paid != a.amount
    1392. 

  1392.               AND (a.$transdate <= '$form->{todate}')
    1393. 

  1393.               ORDER BY ct.name|;
    1394. 

  1394.   my $sth = $dbh->prepare($query);
    1395. 

  1395.   $sth->execute || $form->dberror;
    1396. 

  1396.   
    1397. 

  1397.   my @ot = ();
    1398. 

  1398.   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    1399. 

  1399.     push @ot, $ref;
    1400. 

  1400.   }
    1401. 

  1401.   $sth->finish;
    1402. 

  1402. 

  1403.   my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
    1404. 

  1404. 

  1405.   my %interval = ( 'Pg' => {
    1406. 

  1406.                         'c0' => "(date '$form->{todate}' - interval '0 days')",
    1407. 

  1407.             'c30' => "(date '$form->{todate}' - interval '30 days')",
    1408. 

  1408.             'c60' => "(date '$form->{todate}' - interval '60 days')",
    1409. 

  1409.             'c90' => "(date '$form->{todate}' - interval '90 days')" },
    1410. 

  1410.           'DB2' => {
    1411. 

  1411.                 'c0' => "(date ('$form->{todate}') - 0 days)",
    1412. 

  1412.             'c30' => "(date ('$form->{todate}') - 30 days)",
    1413. 

  1413.             'c60' => "(date ('$form->{todate}') - 60 days)",
    1414. 

  1414.             'c90' => "(date ('$form->{todate}') - 90 days)" }
    1415. 

  1415.         );
    1416. 

  1416. 

  1417.   $interval{Oracle} = $interval{PgPP} = $interval{Pg};
    1418. 

  1418.   
    1419. 

  1419.             
    1420. 

  1420.   # for each company that has some stuff outstanding
    1421. 

  1421.   $form->{currencies} ||= ":";
    1422. 

  1422.   
    1423. 

  1423.       
    1424. 

  1424.        $where = qq|
    1425. 

  1425.         a.paid != a.amount
    1426. 

  1426.         AND c.id = ?
    1427. 

  1427.         AND a.curr = ?|;
    1428. 

  1428.         
    1429. 

  1429.       if ($department_id) {
    1430. 

  1430.     $where .= qq| AND a.department_id = $department_id|;
    1431. 

  1431.       }
    1432. 

  1432.   
    1433. 

  1433.       $query = "";
    1434. 

  1434.       my $union = "";
    1435. 

  1435. 

  1436.       if ($form->{c0}) {
    1437. 

  1437.     $query .= qq|
    1438. 

  1438.       SELECT c.id AS ctid, c.$form->{ct}number, c.name,
    1439. 

  1439.       c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
    1440. 

  1440.       c.contact, c.email,
    1441. 

  1441.       c.phone as $form->{ct}phone, c.fax as $form->{ct}fax,
    1442. 

  1442.       c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber,
    1443. 

  1443.       a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, a.notes,
    1444. 

  1444.       (a.amount - a.paid) as c0, 0.00 as c30, 0.00 as c60, 0.00 as c90,
    1445. 

  1445.       a.duedate, a.invoice, a.id, a.curr,
    1446. 

  1446.         (SELECT $buysell FROM exchangerate e
    1447. 

  1447.          WHERE a.curr = e.curr
    1448. 

  1448.          AND e.transdate = a.transdate) AS exchangerate
    1449. 

  1449.     FROM $form->{arap} a
    1450. 

  1450.     JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
    1451. 

  1451.       WHERE $where
    1452. 

  1452.       AND (
    1453. 

  1453.           a.$transdate <= $interval{$myconfig->{dbdriver}}{c0}
    1454. 

  1454.           AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c30}
    1455. 

  1455.           )
    1456. 

  1456. |;
    1457. 

  1457. 

  1458.         $union = qq|
    1459. 

  1459.       UNION
    1460. 

  1460. |;
    1461. 

  1461. 

  1462.       }
    1463. 

  1463.       
    1464. 

  1464.       if ($form->{c30}) {
    1465. 

  1465. 

  1466.     $query .= qq|
    1467. 

  1467. 

  1468.       $union
    1469. 

  1469. 

  1470.       SELECT c.id AS ctid, c.$form->{ct}number, c.name,
    1471. 

  1471.       c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
    1472. 

  1472.       c.contact, c.email,
    1473. 

  1473.       c.phone as $form->{ct}phone, c.fax as $form->{ct}fax,
    1474. 

  1474.       c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber,
    1475. 

  1475.       a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, a.notes,
    1476. 

  1476.       0.00 as c0, (a.amount - a.paid) as c30, 0.00 as c60, 0.00 as c90,
    1477. 

  1477.       a.duedate, a.invoice, a.id, a.curr,
    1478. 

  1478.         (SELECT $buysell FROM exchangerate e
    1479. 

  1479.          WHERE a.curr = e.curr
    1480. 

  1480.          AND e.transdate = a.transdate) AS exchangerate
    1481. 

  1481.     FROM $form->{arap} a
    1482. 

  1482.     JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
    1483. 

  1483.       WHERE $where
    1484. 

  1484.       AND (
    1485. 

  1485.           a.$transdate < $interval{$myconfig->{dbdriver}}{c30}
    1486. 

  1486.           AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c60}
    1487. 

  1487.           )
    1488. 

  1488. |;
    1489. 

  1489. 

  1490.         $union = qq|
    1491. 

  1491.       UNION
    1492. 

  1492. |;
    1493. 

  1493. 

  1494.       }
    1495. 

  1495. 

  1496.       if ($form->{c60}) {
    1497. 

  1497. 

  1498.     $query .= qq|
    1499. 

  1499. 

  1500.       $union
    1501. 

  1501.     
    1502. 

  1502.       SELECT c.id AS ctid, c.$form->{ct}number, c.name,
    1503. 

  1503.       c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
    1504. 

  1504.       c.contact, c.email,
    1505. 

  1505.       c.phone as $form->{ct}phone, c.fax as $form->{ct}fax,
    1506. 

  1506.       c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber,
    1507. 

  1507.       a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, a.notes,
    1508. 

  1508.       0.00 as c0, 0.00 as c30, (a.amount - a.paid) as c60, 0.00 as c90,
    1509. 

  1509.       a.duedate, a.invoice, a.id, a.curr,
    1510. 

  1510.         (SELECT $buysell FROM exchangerate e
    1511. 

  1511.          WHERE a.curr = e.curr
    1512. 

  1512.          AND e.transdate = a.transdate) AS exchangerate
    1513. 

  1513.       FROM $form->{arap} a
    1514. 

  1514.       JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
    1515. 

  1515.       WHERE $where
    1516. 

  1516.       AND (
    1517. 

  1517.           a.$transdate < $interval{$myconfig->{dbdriver}}{c60}
    1518. 

  1518.           AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c90}
    1519. 

  1519.           )
    1520. 

  1520. |;
    1521. 

  1521. 

  1522.         $union = qq|
    1523. 

  1523.       UNION
    1524. 

  1524. |;
    1525. 

  1525. 

  1526.       }
    1527. 

  1527. 

  1528.       if ($form->{c90}) {
    1529. 

  1529. 

  1530.     $query .= qq|
    1531. 

  1531. 

  1532.       $union
    1533. 

  1533. 

  1534.       SELECT c.id AS ctid, c.$form->{ct}number, c.name,
    1535. 

  1535.       c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
    1536. 

  1536.       c.contact, c.email,
    1537. 

  1537.       c.phone as $form->{ct}phone, c.fax as $form->{ct}fax,
    1538. 

  1538.       c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber,
    1539. 

  1539.       a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, a.notes,
    1540. 

  1540.       0.00 as c0, 0.00 as c30, 0.00 as c60, (a.amount - a.paid) as c90,
    1541. 

  1541.       a.duedate, a.invoice, a.id, a.curr,
    1542. 

  1542.         (SELECT $buysell FROM exchangerate e
    1543. 

  1543.          WHERE a.curr = e.curr
    1544. 

  1544.          AND e.transdate = a.transdate) AS exchangerate
    1545. 

  1545.       FROM $form->{arap} a
    1546. 

  1546.       JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
    1547. 

  1547.       WHERE $where
    1548. 

  1548.       AND a.$transdate < $interval{$myconfig->{dbdriver}}{c90}
    1549. 

  1549. |;
    1550. 

  1550.       }
    1551. 

  1551. 

  1552.       $query .= qq|
    1553. 

  1553. 

  1554.       ORDER BY ctid, $transdate, invnumber|;
    1555. 

  1555. 

  1556.       $sth = $dbh->prepare($query) || $form->dberror($query);
    1557. 

  1557. 

  1558.   my @var = ();
    1559. 

  1559.   
    1560. 

  1560.   if ($form->{c0} + $form->{c30} + $form->{c60} + $form->{c90}) {
    1561. 

  1561.     foreach $curr (split /:/, $form->{currencies}) {
    1562. 

  1562.     
    1563. 

  1563.       foreach $item (@ot) {
    1564. 

  1564.     
    1565. 

  1565.     @var = ();
    1566. 

  1566.     for (qw(c0 c30 c60 c90)) { push @var, ($item->{id}, $curr) if $form->{$_} }
    1567. 

  1567.     
    1568. 

  1568.     $sth->execute(@var);
    1569. 

  1569. 

  1570.     while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    1571. 

  1571.       $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
    1572. 

  1572.       $ref->{module} = 'ps' if $ref->{till};
    1573. 

  1573.       $ref->{exchangerate} = 1 unless $ref->{exchangerate};
    1574. 

  1574.       $ref->{language_code} = $item->{language_code};
    1575. 

  1575.       push @{ $form->{AG} }, $ref;
    1576. 

  1576.     }
    1577. 

  1577.     $sth->finish;
    1578. 

  1578. 

  1579.       }
    1580. 

  1580.     }
    1581. 

  1581.   }
    1582. 

  1582. 

  1583.   # get language
    1584. 

  1584.   my $query = qq|SELECT *
    1585. 

  1585.                  FROM language
    1586. 

  1586.          ORDER BY 2|;
    1587. 

  1587.   $sth = $dbh->prepare($query);
    1588. 

  1588.   $sth->execute || $form->dberror($query);
    1589. 

  1589. 

  1590.   while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 
    1591. 

  1591.     push @{ $form->{all_language} }, $ref;
    1592. 

  1592.   }
    1593. 

  1593.   $sth->finish;
    1594. 

  1594. 

  1595.   # disconnect
    1596. 

  1596.   $dbh->disconnect;
    1597. 

  1597. 

  1598. }
    1599. 

  1599. 

  1600. 

  1601. sub get_customer {
    1602. 

  1602.   my ($self, $myconfig, $form) = @_;
    1603. 

  1603. 

  1604.   # connect to database
    1605. 

  1605.   my $dbh = $form->dbconnect($myconfig);
    1606. 

  1606. 

  1607.   my $query = qq|SELECT name, email, cc, bcc
    1608. 

  1608.                  FROM $form->{ct} ct
    1609. 

  1609.          WHERE ct.id = $form->{"$form->{ct}_id"}|;
    1610. 

  1610.   ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $dbh->selectrow_array($query);
    1611. 

  1611.   
    1612. 

  1612.   $dbh->disconnect;
    1613. 

  1613. 

  1614. }
    1615. 

  1615. 

  1616. 

  1617. sub get_taxaccounts {
    1618. 

  1618.   my ($self, $myconfig, $form) = @_;
    1619. 

  1619. 

  1620.   # connect to database
    1621. 

  1621.   my $dbh = $form->dbconnect($myconfig);
    1622. 

  1622.   my $ARAP = uc $form->{db};
    1623. 

  1623.   
    1624. 

  1624.   # get tax accounts
    1625. 

  1625.   my $query = qq|SELECT DISTINCT c.accno, c.description
    1626. 

  1626.                  FROM chart c
    1627. 

  1627.          JOIN tax t ON (c.id = t.chart_id)
    1628. 

  1628.          WHERE c.link LIKE '%${ARAP}_tax%'
    1629. 

  1629.                  ORDER BY c.accno|;
    1630. 

  1630.   my $sth = $dbh->prepare($query);
    1631. 

  1631.   $sth->execute || $form->dberror;
    1632. 

  1632. 

  1633.   my $ref = ();
    1634. 

  1634.   while ($ref = $sth->fetchrow_hashref(NAME_lc) ) {
    1635. 

  1635.     push @{ $form->{taxaccounts} }, $ref;
    1636. 

  1636.   }
    1637. 

  1637.   $sth->finish;
    1638. 

  1638. 

  1639.   # get gifi tax accounts
    1640. 

  1640.   my $query = qq|SELECT DISTINCT g.accno, g.description
    1641. 

  1641.                  FROM gifi g
    1642. 

  1642.          JOIN chart c ON (c.gifi_accno= g.accno)
    1643. 

  1643.          JOIN tax t ON (c.id = t.chart_id)
    1644. 

  1644.          WHERE c.link LIKE '%${ARAP}_tax%'
    1645. 

  1645.                  ORDER BY accno|;
    1646. 

  1646.   my $sth = $dbh->prepare($query);
    1647. 

  1647.   $sth->execute || $form->dberror;
    1648. 

  1648. 

  1649.   while ($ref = $sth->fetchrow_hashref(NAME_lc) ) {
    1650. 

  1650.     push @{ $form->{gifi_taxaccounts} }, $ref;
    1651. 

  1651.   }
    1652. 

  1652.   $sth->finish;
    1653. 

  1653. 

  1654.   $dbh->disconnect;
    1655. 

  1655. 

  1656. }
    1657. 

  1657. 

  1658. 

  1659. 

  1660. sub tax_report {
    1661. 

  1661.   my ($self, $myconfig, $form) = @_;
    1662. 

  1662. 

  1663.   # connect to database
    1664. 

  1664.   my $dbh = $form->dbconnect($myconfig);
    1665. 

  1665. 

  1666.   my ($null, $department_id) = split /--/, $form->{department};
    1667. 

  1667.   
    1668. 

  1668.   # build WHERE
    1669. 

  1669.   my $where = "1 = 1";
    1670. 

  1670.   my $cashwhere = "";
    1671. 

  1671. 

  1672.   if ($department_id) {
    1673. 

  1673.     $where .= qq|
    1674. 

  1674.                  AND a.department_id = $department_id
    1675. 

  1675.         |;
    1676. 

  1676.   }
    1677. 

  1677.   
    1678. 

  1678.   my $query;
    1679. 

  1679.   my $sth;
    1680. 

  1680.   my $accno;
    1681. 

  1681.   
    1682. 

  1682.   if ($form->{accno}) {
    1683. 

  1683.     if ($form->{accno} =~ /^gifi_/) {
    1684. 

  1684.       ($null, $accno) = split /_/, $form->{accno};
    1685. 

  1685.       $accno = qq| AND ch.gifi_accno = '$accno'|;
    1686. 

  1686.     } else {
    1687. 

  1687.       $accno = $form->{accno};
    1688. 

  1688.       $accno = qq| AND ch.accno = '$accno'|;
    1689. 

  1689.     }
    1690. 

  1690.   }
    1691. 

  1691. 

  1692.   my $table;
    1693. 

  1693.   my $ARAP;
    1694. 

  1694.   
    1695. 

  1695.   if ($form->{db} eq 'ar') {
    1696. 

  1696.     $table = "customer";
    1697. 

  1697.     $ARAP = "AR";
    1698. 

  1698.   }
    1699. 

  1699.   if ($form->{db} eq 'ap') {
    1700. 

  1700.     $table = "vendor";
    1701. 

  1701.     $ARAP = "AP";
    1702. 

  1702.   }
    1703. 

  1703. 

  1704.   my $transdate = "a.transdate";
    1705. 

  1705. 

  1706.   ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
    1707. 

  1707.   
    1708. 

  1708.   # if there are any dates construct a where
    1709. 

  1709.   if ($form->{fromdate} || $form->{todate}) {
    1710. 

  1710.     if ($form->{fromdate}) {
    1711. 

  1711.       $where .= " AND $transdate >= '$form->{fromdate}'";
    1712. 

  1712.     }
    1713. 

  1713.     if ($form->{todate}) {
    1714. 

  1714.       $where .= " AND $transdate <= '$form->{todate}'";
    1715. 

  1715.     }
    1716. 

  1716.   }
    1717. 

  1717. 

  1718. 

  1719.   if ($form->{method} eq 'cash') {
    1720. 

  1720.     $transdate = "a.datepaid";
    1721. 

  1721. 

  1722.     my $todate = $form->{todate};
    1723. 

  1723.     if (! $todate) {
    1724. 

  1724.       ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
    1725. 

  1725.     }
    1726. 

  1726.     
    1727. 

  1727.     $cashwhere = qq|
    1728. 

  1728.          AND ac.trans_id IN
    1729. 

  1729.            (
    1730. 

  1730.              SELECT trans_id
    1731. 

  1731.              FROM acc_trans
    1732. 

  1732.              JOIN chart ON (chart_id = chart.id)
    1733. 

  1733.              WHERE link LIKE '%${ARAP}_paid%'
    1734. 

  1734.              AND $transdate <= '$todate'
    1735. 

  1735.              AND a.paid = a.amount
    1736. 

  1736.            )
    1737. 

  1737.           |;
    1738. 

  1738. 

  1739.   }
    1740. 

  1740. 

  1741.     
    1742. 

  1742.   my $ml = ($form->{db} eq 'ar') ? 1 : -1;
    1743. 

  1743.   
    1744. 

  1744.   my %ordinal = ( 'transdate' => 3,
    1745. 

  1745.                   'invnumber' => 4,
    1746. 

  1746.           'name' => 5
    1747. 

  1747.         );
    1748. 

  1748.   
    1749. 

  1749.   my @a = qw(transdate invnumber name);
    1750. 

  1750.   my $sortorder = $form->sort_order(\@a, \%ordinal);
    1751. 

  1751. 

  1752.   if ($form->{summary}) {
    1753. 

  1753.     
    1754. 

  1754.     $query = qq|SELECT a.id, a.invoice, $transdate AS transdate,
    1755. 

  1755.         a.invnumber, n.name, a.netamount,
    1756. 

  1756.         ac.amount * $ml AS tax,
    1757. 

  1757.         a.till
    1758. 

  1758.         FROM acc_trans ac
    1759. 

  1759.           JOIN $form->{db} a ON (a.id = ac.trans_id)
    1760. 

  1760.           JOIN chart ch ON (ch.id = ac.chart_id)
    1761. 

  1761.           JOIN $table n ON (n.id = a.${table}_id)
    1762. 

  1762.         WHERE $where
    1763. 

  1763.         $accno
    1764. 

  1764.         $cashwhere
    1765. 

  1765.         |;
    1766. 

  1766. 

  1767.       if ($form->{fromdate}) {
    1768. 

  1768.     # include open transactions from previous period
    1769. 

  1769.     if ($cashwhere) {
    1770. 

  1770.       $query .= qq|
    1771. 

  1771.               UNION
    1772. 

  1772.           
    1773. 

  1773.                 SELECT a.id, a.invoice, $transdate AS transdate,
    1774. 

  1774.         a.invnumber, n.name, a.netamount,
    1775. 

  1775.         ac.amount * $ml AS tax,
    1776. 

  1776.         a.till
    1777. 

  1777.         FROM acc_trans ac
    1778. 

  1778.           JOIN $form->{db} a ON (a.id = ac.trans_id)
    1779. 

  1779.           JOIN chart ch ON (ch.id = ac.chart_id)
    1780. 

  1780.           JOIN $table n ON (n.id = a.${table}_id)
    1781. 

  1781.         WHERE a.datepaid >= '$form->{fromdate}'
    1782. 

  1782.         $accno
    1783. 

  1783.         $cashwhere
    1784. 

  1784.         |;
    1785. 

  1785.     }
    1786. 

  1786.       }
    1787. 

  1787.  
    1788. 

  1788.         
    1789. 

  1789.     } else {
    1790. 

  1790.       
    1791. 

  1791.      $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
    1792. 

  1792.         a.invnumber, n.name, a.netamount,
    1793. 

  1793.         ac.amount * $ml AS tax,
    1794. 

  1794.         a.notes AS description, a.till
    1795. 

  1795.         FROM acc_trans ac
    1796. 

  1796.           JOIN $form->{db} a ON (a.id = ac.trans_id)
    1797. 

  1797.           JOIN chart ch ON (ch.id = ac.chart_id)
    1798. 

  1798.           JOIN $table n ON (n.id = a.${table}_id)
    1799. 

  1799.         WHERE $where
    1800. 

  1800.         $accno
    1801. 

  1801.         AND a.invoice = '0'
    1802. 

  1802.         $cashwhere
    1803. 

  1803.         
    1804. 

  1804.           UNION
    1805. 

  1805.           
    1806. 

  1806.         SELECT a.id, '1' AS invoice, $transdate AS transdate,
    1807. 

  1807.         a.invnumber, n.name,
    1808. 

  1808.         i.sellprice * i.qty * $ml AS netamount,
    1809. 

  1809.         i.sellprice * i.qty * $ml *
    1810. 

  1810.         (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,
    1811. 

  1811.         i.description, a.till
    1812. 

  1812.         FROM acc_trans ac
    1813. 

  1813.           JOIN $form->{db} a ON (a.id = ac.trans_id)
    1814. 

  1814.           JOIN chart ch ON (ch.id = ac.chart_id)
    1815. 

  1815.           JOIN $table n ON (n.id = a.${table}_id)
    1816. 

  1816.           JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id)
    1817. 

  1817.           JOIN invoice i ON (i.trans_id = a.id)
    1818. 

  1818.           JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id)
    1819. 

  1819.         WHERE $where
    1820. 

  1820.         $accno
    1821. 

  1821.         AND a.invoice = '1'
    1822. 

  1822.         $cashwhere
    1823. 

  1823.         |;
    1824. 

  1824. 

  1825.       if ($form->{fromdate}) {
    1826. 

  1826.     if ($cashwhere) {
    1827. 

  1827.      $query .= qq|
    1828. 

  1828.           UNION
    1829. 

  1829.           
    1830. 

  1830.             SELECT a.id, '0' AS invoice, $transdate AS transdate,
    1831. 

  1831.         a.invnumber, n.name, a.netamount,
    1832. 

  1832.         ac.amount * $ml AS tax,
    1833. 

  1833.         a.notes AS description, a.till
    1834. 

  1834.         FROM acc_trans ac
    1835. 

  1835.           JOIN $form->{db} a ON (a.id = ac.trans_id)
    1836. 

  1836.           JOIN chart ch ON (ch.id = ac.chart_id)
    1837. 

  1837.           JOIN $table n ON (n.id = a.${table}_id)
    1838. 

  1838.         WHERE a.datepaid >= '$form->{fromdate}'
    1839. 

  1839.         $accno
    1840. 

  1840.         AND a.invoice = '0'
    1841. 

  1841.         $cashwhere
    1842. 

  1842.         
    1843. 

  1843.           UNION
    1844. 

  1844.           
    1845. 

  1845.         SELECT a.id, '1' AS invoice, $transdate AS transdate,
    1846. 

  1846.         a.invnumber, n.name,
    1847. 

  1847.         i.sellprice * i.qty * $ml AS netamount,
    1848. 

  1848.         i.sellprice * i.qty * $ml *
    1849. 

  1849.         (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,
    1850. 

  1850.         i.description, a.till
    1851. 

  1851.         FROM acc_trans ac
    1852. 

  1852.           JOIN $form->{db} a ON (a.id = ac.trans_id)
    1853. 

  1853.           JOIN chart ch ON (ch.id = ac.chart_id)
    1854. 

  1854.           JOIN $table n ON (n.id = a.${table}_id)
    1855. 

  1855.           JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id)
    1856. 

  1856.           JOIN invoice i ON (i.trans_id = a.id)
    1857. 

  1857.           JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id)
    1858. 

  1858.         WHERE a.datepaid >= '$form->{fromdate}'
    1859. 

  1859.         $accno
    1860. 

  1860.         AND a.invoice = '1'
    1861. 

  1861.         $cashwhere
    1862. 

  1862.         |;
    1863. 

  1863.     }
    1864. 

  1864.       }
    1865. 

  1865.     }
    1866. 

  1866. 

  1867. 

  1868.   if ($form->{report} =~ /nontaxable/) {
    1869. 

  1869.     
    1870. 

  1870.     if ($form->{summary}) {
    1871. 

  1871.       # only gather up non-taxable transactions
    1872. 

  1872.       $query = qq|SELECT DISTINCT a.id, a.invoice, $transdate AS transdate,
    1873. 

  1873.           a.invnumber, n.name, a.netamount, a.till
    1874. 

  1874.           FROM acc_trans ac
    1875. 

  1875.         JOIN $form->{db} a ON (a.id = ac.trans_id)
    1876. 

  1876.         JOIN $table n ON (n.id = a.${table}_id)
    1877. 

  1877.           WHERE $where
    1878. 

  1878.           AND a.netamount = a.amount
    1879. 

  1879.           $cashwhere
    1880. 

  1880.           |;
    1881. 

  1881. 

  1882.       if ($form->{fromdate}) {
    1883. 

  1883.     if ($cashwhere) {
    1884. 

  1884.       $query .= qq|
    1885. 

  1885.                 UNION
    1886. 

  1886.         
    1887. 

  1887.                   SELECT DISTINCT a.id, a.invoice, $transdate AS transdate,
    1888. 

  1888.           a.invnumber, n.name, a.netamount, a.till
    1889. 

  1889.           FROM acc_trans ac
    1890. 

  1890.         JOIN $form->{db} a ON (a.id = ac.trans_id)
    1891. 

  1891.         JOIN $table n ON (n.id = a.${table}_id)
    1892. 

  1892.         WHERE a.datepaid >= '$form->{fromdate}'
    1893. 

  1893.           AND a.netamount = a.amount
    1894. 

  1894.           $cashwhere
    1895. 

  1895.           |;
    1896. 

  1896.     }
    1897. 

  1897.       }
    1898. 

  1898.           
    1899. 

  1899.     } else {
    1900. 

  1900. 

  1901.       # gather up details for non-taxable transactions
    1902. 

  1902.       $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
    1903. 

  1903.           a.invnumber, n.name, a.netamount,
    1904. 

  1904.           a.notes AS description, a.till
    1905. 

  1905.           FROM acc_trans ac
    1906. 

  1906.         JOIN $form->{db} a ON (a.id = ac.trans_id)
    1907. 

  1907.         JOIN $table n ON (n.id = a.${table}_id)
    1908. 

  1908.           WHERE $where
    1909. 

  1909.           AND a.invoice = '0'
    1910. 

  1910.           AND a.netamount = a.amount
    1911. 

  1911.           $cashwhere
    1912. 

  1912.         GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
    1913. 

  1913.         a.notes, a.till
    1914. 

  1914.         
    1915. 

  1915.         UNION
    1916. 

  1916.         
    1917. 

  1917.           SELECT a.id, '1' AS invoice, $transdate AS transdate,
    1918. 

  1918.           a.invnumber, n.name,
    1919. 

  1919.           sum(ac.sellprice * ac.qty) * $ml AS netamount,
    1920. 

  1920.           ac.description, a.till
    1921. 

  1921.           FROM invoice ac
    1922. 

  1922.         JOIN $form->{db} a ON (a.id = ac.trans_id)
    1923. 

  1923.         JOIN $table n ON (n.id = a.${table}_id)
    1924. 

  1924.           WHERE $where
    1925. 

  1925.           AND a.invoice = '1'
    1926. 

  1926.           AND (
    1927. 

  1927.             a.${table}_id NOT IN (
    1928. 

  1928.               SELECT ${table}_id FROM ${table}tax t (${table}_id)
    1929. 

  1929.                      ) OR
    1930. 

  1930.             ac.parts_id NOT IN (
    1931. 

  1931.               SELECT parts_id FROM partstax p (parts_id)
    1932. 

  1932.                       )
    1933. 

  1933.               )
    1934. 

  1934.           $cashwhere
    1935. 

  1935.           GROUP BY a.id, a.invnumber, $transdate, n.name,
    1936. 

  1936.           ac.description, a.till
    1937. 

  1937.           |;
    1938. 

  1938. 

  1939.       if ($form->{fromdate}) {
    1940. 

  1940.     if ($cashwhere) {
    1941. 

  1941.       $query .= qq|
    1942. 

  1942.                 UNION
    1943. 

  1943.         
    1944. 

  1944.                   SELECT a.id, '0' AS invoice, $transdate AS transdate,
    1945. 

  1945.           a.invnumber, n.name, a.netamount,
    1946. 

  1946.           a.notes AS description, a.till
    1947. 

  1947.           FROM acc_trans ac
    1948. 

  1948.         JOIN $form->{db} a ON (a.id = ac.trans_id)
    1949. 

  1949.         JOIN $table n ON (n.id = a.${table}_id)
    1950. 

  1950.           WHERE a.datepaid >= '$form->{fromdate}'
    1951. 

  1951.           AND a.invoice = '0'
    1952. 

  1952.           AND a.netamount = a.amount
    1953. 

  1953.           $cashwhere
    1954. 

  1954.         GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
    1955. 

  1955.         a.notes, a.till
    1956. 

  1956.         
    1957. 

  1957.         UNION
    1958. 

  1958.         
    1959. 

  1959.           SELECT a.id, '1' AS invoice, $transdate AS transdate,
    1960. 

  1960.           a.invnumber, n.name,
    1961. 

  1961.           sum(ac.sellprice * ac.qty) * $ml AS netamount,
    1962. 

  1962.           ac.description, a.till
    1963. 

  1963.           FROM invoice ac
    1964. 

  1964.         JOIN $form->{db} a ON (a.id = ac.trans_id)
    1965. 

  1965.         JOIN $table n ON (n.id = a.${table}_id)
    1966. 

  1966.           WHERE a.datepaid >= '$form->{fromdate}'
    1967. 

  1967.           AND a.invoice = '1'
    1968. 

  1968.           AND (
    1969. 

  1969.             a.${table}_id NOT IN (
    1970. 

  1970.               SELECT ${table}_id FROM ${table}tax t (${table}_id)
    1971. 

  1971.                      ) OR
    1972. 

  1972.             ac.parts_id NOT IN (
    1973. 

  1973.               SELECT parts_id FROM partstax p (parts_id)
    1974. 

  1974.                       )
    1975. 

  1975.               )
    1976. 

  1976.           $cashwhere
    1977. 

  1977.           GROUP BY a.id, a.invnumber, $transdate, n.name,
    1978. 

  1978.           ac.description, a.till
    1979. 

  1979.           |;
    1980. 

  1980.     }
    1981. 

  1981.       }
    1982. 

  1982. 

  1983.     }
    1984. 

  1984.   }
    1985. 

  1985. 

  1986.   
    1987. 

  1987.   $query .= qq|
    1988. 

  1988.           ORDER by $sortorder|;
    1989. 

  1989. 

  1990.   $sth = $dbh->prepare($query);
    1991. 

  1991.   $sth->execute || $form->dberror($query);
    1992. 

  1992. 

  1993.   while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    1994. 

  1994.     $ref->{tax} = $form->round_amount($ref->{tax}, 2);
    1995. 

  1995.     if ($form->{report} =~ /nontaxable/) {
    1996. 

  1996.       push @{ $form->{TR} }, $ref if $ref->{netamount};
    1997. 

  1997.     } else {
    1998. 

  1998.       push @{ $form->{TR} }, $ref if $ref->{tax};
    1999. 

  1999.     }
    2000. 

  2000.   }
    2001. 

  2001. 

  2002.   $sth->finish;
    2003. 

  2003.   $dbh->disconnect;
    2004. 

  2004. 

  2005. }
    2006. 

  2006. 

  2007. 

  2008. sub paymentaccounts {
    2009. 

  2009.   my ($self, $myconfig, $form) = @_;
    2010. 

  2010.  
    2011. 

  2011.   # connect to database, turn AutoCommit off
    2012. 

  2012.   my $dbh = $form->dbconnect_noauto($myconfig);
    2013. 

  2013. 

  2014.   my $ARAP = uc $form->{db};
    2015. 

  2015.   
    2016. 

  2016.   # get A(R|P)_paid accounts
    2017. 

  2017.   my $query = qq|SELECT accno, description
    2018. 

  2018.                  FROM chart
    2019. 

  2019.                  WHERE link LIKE '%${ARAP}_paid%'
    2020. 

  2020.          ORDER BY accno|;
    2021. 

  2021.   my $sth = $dbh->prepare($query);
    2022. 

  2022.   $sth->execute || $form->dberror($query);
    2023. 

  2023.  
    2024. 

  2024.   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    2025. 

  2025.     push @{ $form->{PR} }, $ref;
    2026. 

  2026.   }
    2027. 

  2027.   $sth->finish;
    2028. 

  2028. 

  2029.   $form->all_years($myconfig, $dbh);
    2030. 

  2030.   
    2031. 

  2031.   $dbh->disconnect;
    2032. 

  2032. 

  2033. }
    2034. 

  2034. 

  2035.  
    2036. 

  2036. sub payments {
    2037. 

  2037.   my ($self, $myconfig, $form) = @_;
    2038. 

  2038. 

  2039.   # connect to database, turn AutoCommit off
    2040. 

  2040.   my $dbh = $form->dbconnect_noauto($myconfig);
    2041. 

  2041. 

  2042.   my $ml = 1;
    2043. 

  2043.   if ($form->{db} eq 'ar') {
    2044. 

  2044.     $table = 'customer';
    2045. 

  2045.     $ml = -1;
    2046. 

  2046.   }
    2047. 

  2047.   if ($form->{db} eq 'ap') {
    2048. 

  2048.     $table = 'vendor';
    2049. 

  2049.   }
    2050. 

  2050.      
    2051. 

  2051. 

  2052.   my $query;
    2053. 

  2053.   my $sth;
    2054. 

  2054.   my $dpt_join;
    2055. 

  2055.   my $where;
    2056. 

  2056.   my $var;
    2057. 

  2057. 

  2058.   if ($form->{department_id}) {
    2059. 

  2059.     $dpt_join = qq|
    2060. 

  2060.              JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
    2061. 

  2061.          |;
    2062. 

  2062. 

  2063.     $where = qq|
    2064. 

  2064.          AND t.department_id = $form->{department_id}
    2065. 

  2065.         |;
    2066. 

  2066.   }
    2067. 

  2067. 

  2068.   ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
    2069. 

  2069.   
    2070. 

  2070.   if ($form->{fromdate}) {
    2071. 

  2071.     $where .= " AND ac.transdate >= '$form->{fromdate}'";
    2072. 

  2072.   }
    2073. 

  2073.   if ($form->{todate}) {
    2074. 

  2074.     $where .= " AND ac.transdate <= '$form->{todate}'";
    2075. 

  2075.   }
    2076. 

  2076.   if (!$form->{fx_transaction}) {
    2077. 

  2077.     $where .= " AND ac.fx_transaction = '0'";
    2078. 

  2078.   }
    2079. 

  2079.   
    2080. 

  2080.   if ($form->{description} ne "") {
    2081. 

  2081.     $var = $form->like(lc $form->{description});
    2082. 

  2082.     $where .= " AND lower(c.name) LIKE '$var'";
    2083. 

  2083.   }
    2084. 

  2084.   if ($form->{source} ne "") {
    2085. 

  2085.     $var = $form->like(lc $form->{source});
    2086. 

  2086.     $where .= " AND lower(ac.source) LIKE '$var'";
    2087. 

  2087.   }
    2088. 

  2088.   if ($form->{memo} ne "") {
    2089. 

  2089.     $var = $form->like(lc $form->{memo});
    2090. 

  2090.     $where .= " AND lower(ac.memo) LIKE '$var'";
    2091. 

  2091.   }
    2092. 

  2092.  
    2093. 

  2093.   my %ordinal = ( 'name' => 1,
    2094. 

  2094.           'transdate' => 2,
    2095. 

  2095.           'source' => 4,
    2096. 

  2096.           'employee' => 6,
    2097. 

  2097.           'till' => 7
    2098. 

  2098.         );
    2099. 

  2099. 

  2100.   my @a = qw(name transdate employee);
    2101. 

  2101.   my $sortorder = $form->sort_order(\@a, \%ordinal);
    2102. 

  2102.   
    2103. 

  2103.   my $glwhere = $where;
    2104. 

  2104.   $glwhere =~ s/\(c.name\)/\(g.description\)/;
    2105. 

  2105. 

  2106.   # cycle through each id
    2107. 

  2107.   foreach my $accno (split(/ /, $form->{paymentaccounts})) {
    2108. 

  2108. 

  2109.     $query = qq|SELECT id, accno, description
    2110. 

  2110.                 FROM chart
    2111. 

  2111.         WHERE accno = '$accno'|;
    2112. 

  2112.     $sth = $dbh->prepare($query);
    2113. 

  2113.     $sth->execute || $form->dberror($query);
    2114. 

  2114. 

  2115.     my $ref = $sth->fetchrow_hashref(NAME_lc);
    2116. 

  2116.     push @{ $form->{PR} }, $ref;
    2117. 

  2117.     $sth->finish;
    2118. 

  2118. 

  2119.     $query = qq|SELECT c.name, ac.transdate, sum(ac.amount) * $ml AS paid,
    2120. 

  2120.                 ac.source, ac.memo, e.name AS employee, a.till, a.curr
    2121. 

  2121.         FROM acc_trans ac
    2122. 

  2122.             JOIN $form->{db} a ON (ac.trans_id = a.id)
    2123. 

  2123.             JOIN $table c ON (c.id = a.${table}_id)
    2124. 

  2124.         LEFT JOIN employee e ON (a.employee_id = e.id)
    2125. 

  2125.             $dpt_join
    2126. 

  2126.         WHERE ac.chart_id = $ref->{id}
    2127. 

  2127.         $where|;
    2128. 

  2128. 

  2129.     if ($form->{till} ne "") {
    2130. 

  2130.       $query .= " AND a.invoice = '1' 
    2131. 

  2131.                   AND NOT a.till IS NULL";
    2132. 

  2132.       
    2133. 

  2133.       if ($myconfig->{role} eq 'user') {
    2134. 

  2134.     $query .= " AND e.login = '$form->{login}'";
    2135. 

  2135.       }
    2136. 

  2136.     }
    2137. 

  2137. 

  2138.     $query .= qq|
    2139. 

  2139.                 GROUP BY c.name, ac.transdate, ac.source, ac.memo,
    2140. 

  2140.         e.name, a.till, a.curr
    2141. 

  2141.         |;
    2142. 

  2142.         
    2143. 

  2143.     if ($form->{till} eq "") {
    2144. 

  2144. # don't need gl for a till
    2145. 

  2145.       
    2146. 

  2146.       $query .= qq|
    2147. 

  2147.     UNION
    2148. 

  2148.         SELECT g.description, ac.transdate, sum(ac.amount) * $ml AS paid, ac.source,
    2149. 

  2149.         ac.memo, e.name AS employee, '' AS till, '' AS curr
    2150. 

  2150.         FROM acc_trans ac
    2151. 

  2151.             JOIN gl g ON (g.id = ac.trans_id)
    2152. 

  2152.         LEFT JOIN employee e ON (g.employee_id = e.id)
    2153. 

  2153.             $dpt_join
    2154. 

  2154.         WHERE ac.chart_id = $ref->{id}
    2155. 

  2155.         $glwhere
    2156. 

  2156.         AND (ac.amount * $ml) > 0
    2157. 

  2157.     GROUP BY g.description, ac.transdate, ac.source, ac.memo, e.name
    2158. 

  2158.         |;
    2159. 

  2159. 

  2160.     }
    2161. 

  2161. 

  2162.     $query .= qq|
    2163. 

  2163.                 ORDER BY $sortorder|;
    2164. 

  2164. 

  2165.     $sth = $dbh->prepare($query);
    2166. 

  2166.     $sth->execute || $form->dberror($query);
    2167. 

  2167. 

  2168.     while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
    2169. 

  2169.       push @{ $form->{$ref->{id}} }, $pr;
    2170. 

  2170.     }
    2171. 

  2171.     $sth->finish;
    2172. 

  2172. 

  2173.   }
    2174. 

  2174.   
    2175. 

  2175.   $dbh->disconnect;
    2176. 

  2176.   
    2177. 

  2177. }
    2178. 

  2178. 

  2179. 

  2180. 1;
    2181. 

  2181. 

  2182.
generated by cgit v1.2.3 (git 2.46.0) at 2024-12-04 10:33:09 +0000