summaryrefslogtreecommitdiff
path: root/LedgerSMB/CA.pm
blob: 5ea2859b587e0bd6985d707860b9b69095f8a69c (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://sourceforge.net/projects/ledger-smb/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2001
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # chart of accounts
  31. #
  32. #======================================================================
  33. package CA;
  34. sub all_accounts {
  35. my ($self, $myconfig, $form) = @_;
  36. my $amount = ();
  37. # connect to database
  38. my $dbh = $form->dbconnect($myconfig);
  39. my $query = qq|SELECT accno, SUM(acc_trans.amount) AS amount
  40. FROM chart, acc_trans
  41. WHERE chart.id = acc_trans.chart_id
  42. GROUP BY accno|;
  43. my $sth = $dbh->prepare($query);
  44. $sth->execute || $form->dberror($query);
  45. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  46. $amount{$ref->{accno}} = $ref->{amount}
  47. }
  48. $sth->finish;
  49. $query = qq|SELECT accno, description
  50. FROM gifi|;
  51. $sth = $dbh->prepare($query);
  52. $sth->execute || $form->dberror($query);
  53. my $gifi = ();
  54. while (my ($accno, $description) = $sth->fetchrow_array) {
  55. $gifi{$accno} = $description;
  56. }
  57. $sth->finish;
  58. $query = qq|SELECT c.id, c.accno, c.description, c.charttype,
  59. c.gifi_accno, c.category, c.link
  60. FROM chart c
  61. ORDER BY accno|;
  62. $sth = $dbh->prepare($query);
  63. $sth->execute || $form->dberror($query);
  64. while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
  65. $ca->{amount} = $amount{$ca->{accno}};
  66. $ca->{gifi_description} = $gifi{$ca->{gifi_accno}};
  67. if ($ca->{amount} < 0) {
  68. $ca->{debit} = $ca->{amount} * -1;
  69. } else {
  70. $ca->{credit} = $ca->{amount};
  71. }
  72. push @{ $form->{CA} }, $ca;
  73. }
  74. $sth->finish;
  75. $dbh->disconnect;
  76. }
  77. sub all_transactions {
  78. my ($self, $myconfig, $form) = @_;
  79. # connect to database
  80. my $dbh = $form->dbconnect($myconfig);
  81. # get chart_id
  82. my $query = qq|SELECT id
  83. FROM chart
  84. WHERE accno = '$form->{accno}'|;
  85. if ($form->{accounttype} eq 'gifi') {
  86. $query = qq|SELECT id
  87. FROM chart
  88. WHERE gifi_accno = '$form->{gifi_accno}'|;
  89. }
  90. my $sth = $dbh->prepare($query);
  91. $sth->execute || $form->dberror($query);
  92. my @id = ();
  93. while (my ($id) = $sth->fetchrow_array) {
  94. push @id, $id;
  95. }
  96. $sth->finish;
  97. my $fromdate_where;
  98. my $todate_where;
  99. ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  100. if ($form->{fromdate}) {
  101. $fromdate_where = qq| AND ac.transdate >= '$form->{fromdate}' |;
  102. }
  103. if ($form->{todate}) {
  104. $todate_where .= qq| AND ac.transdate <= '$form->{todate}' |;
  105. }
  106. my $false = ($myconfig->{dbdriver} =~ /Pg/) ? FALSE : q|'0'|;
  107. # Oracle workaround, use ordinal positions
  108. my %ordinal = ( transdate => 4,
  109. reference => 2,
  110. description => 3 );
  111. my @a = qw(transdate reference description);
  112. my $sortorder = $form->sort_order(\@a, \%ordinal);
  113. my $null;
  114. my $department_id;
  115. my $dpt_where;
  116. my $dpt_join;
  117. my $union;
  118. ($null, $department_id) = split /--/, $form->{department};
  119. if ($department_id) {
  120. $dpt_join = qq| JOIN department t ON (t.id = a.department_id) |;
  121. $dpt_where = qq| AND t.id = $department_id |;
  122. }
  123. my $project;
  124. my $project_id;
  125. if ($form->{projectnumber}) {
  126. ($null, $project_id) = split /--/, $form->{projectnumber};
  127. $project = qq| AND ac.project_id = $project_id |;
  128. }
  129. if ($form->{accno} || $form->{gifi_accno}) {
  130. # get category for account
  131. $query = qq|SELECT description, category, link, contra
  132. FROM chart
  133. WHERE accno = '$form->{accno}'|;
  134. if ($form->{accounttype} eq 'gifi') {
  135. $query = qq|SELECT description, category, link, contra
  136. FROM chart
  137. WHERE gifi_accno = '$form->{gifi_accno}'
  138. AND charttype = 'A'|;
  139. }
  140. ($form->{description}, $form->{category}, $form->{link}, $form->{contra}) = $dbh->selectrow_array($query);
  141. if ($form->{fromdate}) {
  142. if ($department_id) {
  143. # get beginning balance
  144. $query = "";
  145. $union = "";
  146. for (qw(ar ap gl)) {
  147. if ($form->{accounttype} eq 'gifi') {
  148. $query = qq| $union
  149. SELECT SUM(ac.amount)
  150. FROM acc_trans ac
  151. JOIN $_ a ON (a.id = ac.trans_id)
  152. JOIN chart c ON (ac.chart_id = c.id)
  153. WHERE c.gifi_accno = '$form->{gifi_accno}'
  154. AND ac.transdate < '$form->{fromdate}'
  155. AND a.department_id = $department_id
  156. $project |;
  157. } else {
  158. $query .= qq| $union
  159. SELECT SUM(ac.amount)
  160. FROM acc_trans ac
  161. JOIN $_ a ON (a.id = ac.trans_id)
  162. JOIN chart c ON (ac.chart_id = c.id)
  163. WHERE c.accno = '$form->{accno}'
  164. AND ac.transdate < '$form->{fromdate}'
  165. AND a.department_id = $department_id
  166. $project |;
  167. }
  168. $union = qq| UNION ALL |;
  169. }
  170. } else {
  171. if ($form->{accounttype} eq 'gifi') {
  172. $query = qq|SELECT SUM(ac.amount)
  173. FROM acc_trans ac
  174. JOIN chart c ON (ac.chart_id = c.id)
  175. WHERE c.gifi_accno = '$form->{gifi_accno}'
  176. AND ac.transdate < '$form->{fromdate}'
  177. $project |;
  178. } else {
  179. $query = qq|SELECT SUM(ac.amount)
  180. FROM acc_trans ac
  181. JOIN chart c ON (ac.chart_id = c.id)
  182. WHERE c.accno = '$form->{accno}'
  183. AND ac.transdate < '$form->{fromdate}'
  184. $project |;
  185. }
  186. }
  187. ($form->{balance}) = $dbh->selectrow_array($query);
  188. }
  189. }
  190. $query = "";
  191. $union = "";
  192. foreach my $id (@id) {
  193. # get all transactions
  194. $query .= qq|$union
  195. SELECT a.id, a.reference, a.description, ac.transdate,
  196. $false AS invoice, ac.amount, 'gl' as module, ac.cleared,
  197. ac.source, '' AS till, ac.chart_id
  198. FROM gl a
  199. JOIN acc_trans ac ON (ac.trans_id = a.id)
  200. $dpt_join
  201. WHERE ac.chart_id = $id
  202. $fromdate_where
  203. $todate_where
  204. $dpt_where
  205. $project
  206. UNION ALL
  207. SELECT a.id, a.invnumber, c.name, ac.transdate,
  208. a.invoice, ac.amount, 'ar' as module, ac.cleared,
  209. ac.source,
  210. a.till, ac.chart_id
  211. FROM ar a
  212. JOIN acc_trans ac ON (ac.trans_id = a.id)
  213. JOIN customer c ON (a.customer_id = c.id)
  214. $dpt_join
  215. WHERE ac.chart_id = $id
  216. $fromdate_where
  217. $todate_where
  218. $dpt_where
  219. $project
  220. UNION ALL
  221. SELECT a.id, a.invnumber, v.name, ac.transdate,
  222. a.invoice, ac.amount, 'ap' as module, ac.cleared,
  223. ac.source, a.till, ac.chart_id
  224. FROM ap a
  225. JOIN acc_trans ac ON (ac.trans_id = a.id)
  226. JOIN vendor v ON (a.vendor_id = v.id)
  227. $dpt_join
  228. WHERE ac.chart_id = $id
  229. $fromdate_where
  230. $todate_where
  231. $dpt_where
  232. $project |;
  233. $union = qq| UNION ALL |;
  234. }
  235. $query .= qq| ORDER BY $sortorder |;
  236. $sth = $dbh->prepare($query);
  237. $sth->execute || $form->dberror($query);
  238. $query = qq|SELECT c.id, c.accno
  239. FROM chart c
  240. JOIN acc_trans ac ON (ac.chart_id = c.id)
  241. WHERE ac.amount >= 0
  242. AND (c.link = 'AR' OR c.link = 'AP')
  243. AND ac.trans_id = ?|;
  244. my $dr = $dbh->prepare($query) || $form->dberror($query);
  245. $query = qq|SELECT c.id, c.accno
  246. FROM chart c
  247. JOIN acc_trans ac ON (ac.chart_id = c.id)
  248. WHERE ac.amount < 0
  249. AND (c.link = 'AR' OR c.link = 'AP')
  250. AND ac.trans_id = ?|;
  251. my $cr = $dbh->prepare($query) || $form->dberror($query);
  252. my $accno;
  253. my $chart_id;
  254. my %accno;
  255. while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
  256. # gl
  257. if ($ca->{module} eq "gl") {
  258. $ca->{module} = "gl";
  259. }
  260. # ap
  261. if ($ca->{module} eq "ap") {
  262. $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap';
  263. $ca->{module} = 'ps' if $ca->{till};
  264. }
  265. # ar
  266. if ($ca->{module} eq "ar") {
  267. $ca->{module} = ($ca->{invoice}) ? 'is' : 'ar';
  268. $ca->{module} = 'ps' if $ca->{till};
  269. }
  270. if ($ca->{amount}) {
  271. %accno = ();
  272. if ($ca->{amount} < 0) {
  273. $ca->{debit} = $ca->{amount} * -1;
  274. $ca->{credit} = 0;
  275. $dr->execute($ca->{id});
  276. $ca->{accno} = ();
  277. while (($chart_id, $accno) = $dr->fetchrow_array) {
  278. $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
  279. }
  280. $dr->finish;
  281. for (sort keys %accno) { push @{ $ca->{accno} }, "$_ " }
  282. } else {
  283. $ca->{credit} = $ca->{amount};
  284. $ca->{debit} = 0;
  285. $cr->execute($ca->{id});
  286. $ca->{accno} = ();
  287. while (($chart_id, $accno) = $cr->fetchrow_array) {
  288. $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
  289. }
  290. $cr->finish;
  291. for (keys %accno) { push @{ $ca->{accno} }, "$_ " }
  292. }
  293. push @{ $form->{CA} }, $ca;
  294. }
  295. }
  296. $sth->finish;
  297. $dbh->disconnect;
  298. }
  299. 1;