summaryrefslogtreecommitdiff
path: root/LedgerSMB/CA.pm
blob: 264fcfe63beaf02fb0394c6f1dc41bcc37cb5293 (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://www.ledgersmb.org/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2001
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # 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->{dbh};
  39. my $query = qq|
  40. SELECT accno, SUM(acc_trans.amount) AS amount
  41. FROM chart, acc_trans
  42. WHERE chart.id = acc_trans.chart_id
  43. GROUP BY accno|;
  44. my $sth = $dbh->prepare($query);
  45. $sth->execute || $form->dberror($query);
  46. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  47. $amount{$ref->{accno}} = $ref->{amount}
  48. }
  49. $sth->finish;
  50. $query = qq|
  51. SELECT accno, description
  52. FROM gifi|;
  53. $sth = $dbh->prepare($query);
  54. $sth->execute || $form->dberror($query);
  55. my $gifi = ();
  56. while (my ($accno, $description) = $sth->fetchrow_array) {
  57. $gifi{$accno} = $description;
  58. }
  59. $sth->finish;
  60. $query = qq|
  61. SELECT c.id, c.accno, c.description, c.charttype,
  62. c.gifi_accno, c.category, c.link
  63. FROM chart c
  64. ORDER BY accno|;
  65. $sth = $dbh->prepare($query);
  66. $sth->execute || $form->dberror($query);
  67. while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
  68. $ca->{amount} = $amount{$ca->{accno}};
  69. $ca->{gifi_description} = $gifi{$ca->{gifi_accno}};
  70. if ($ca->{amount} < 0) {
  71. $ca->{debit} = $ca->{amount} * -1;
  72. } else {
  73. $ca->{credit} = $ca->{amount};
  74. }
  75. push @{ $form->{CA} }, $ca;
  76. }
  77. $sth->finish;
  78. $dbh->commit;
  79. }
  80. sub all_transactions {
  81. my ($self, $myconfig, $form) = @_;
  82. # connect to database
  83. my $dbh = $form->{dbh};
  84. # get chart_id
  85. my $query = qq|
  86. SELECT id
  87. FROM chart
  88. WHERE accno = ?|;
  89. my $accno = $form->{accno};
  90. if ($form->{accounttype} eq 'gifi') {
  91. $query = qq|
  92. SELECT id
  93. FROM chart
  94. WHERE gifi_accno = ?|;
  95. $accno = $form->{gifi_accno};
  96. }
  97. my $sth = $dbh->prepare($query);
  98. $sth->execute($accno) || $form->dberror($query);
  99. my @id = ();
  100. while (my ($id) = $sth->fetchrow_array) {
  101. push @id, $id;
  102. }
  103. $sth->finish;
  104. my $fromdate_where;
  105. my $todate_where;
  106. ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  107. my $fdate;
  108. if ($form->{fromdate}) {
  109. $fromdate_where = qq| AND ac.transdate >= ? |;
  110. $fdate = $form->{fromdate};
  111. }
  112. my $tdate;
  113. if ($form->{todate}) {
  114. $todate_where .= qq| AND ac.transdate <= ? |;
  115. $tdate = $form->{todate};
  116. }
  117. my $false = 'FALSE';
  118. # Oracle workaround, use ordinal positions
  119. my %ordinal = ( transdate => 4,
  120. reference => 2,
  121. description => 3 );
  122. my @a = qw(transdate reference description);
  123. my $sortorder = $form->sort_order(\@a, \%ordinal);
  124. my $null;
  125. my $department_id;
  126. my $dpt_where;
  127. my $dpt_join;
  128. my $union;
  129. ($null, $department_id) = split /--/, $form->{department};
  130. my $d_id;
  131. if ($department_id) {
  132. $dpt_join = qq| JOIN department t ON (t.id = a.department_id) |;
  133. $dpt_where = qq| AND t.id = ? |;
  134. $d_id = $department_id;
  135. }
  136. my $project;
  137. my $project_id;
  138. my $p_id;
  139. if ($form->{projectnumber}) {
  140. ($null, $project_id) = split /--/, $form->{projectnumber};
  141. $project = qq| AND ac.project_id = ? |;
  142. $p_id = $project_id;
  143. }
  144. @queryargs = ();
  145. if ($form->{accno} || $form->{gifi_accno}) {
  146. # get category for account
  147. $query = qq|
  148. SELECT description, category, link, contra
  149. FROM chart
  150. WHERE accno = ?|;
  151. $accno = $form->{accno};
  152. if ($form->{accounttype} eq 'gifi') {
  153. $query = qq|
  154. SELECT description, category, link, contra
  155. FROM chart
  156. WHERE gifi_accno = ?
  157. AND charttype = 'A'|;
  158. $accno = $form->{gifi_accno};
  159. }
  160. $sth = $dbh->prepare($query);
  161. $sth->execute($accno);
  162. ($form->{description}, $form->{category}, $form->{link},
  163. $form->{contra})
  164. = $sth->fetchrow_array;
  165. if ($form->{fromdate}) {
  166. if ($department_id) {
  167. # get beginning balance
  168. $query = "";
  169. $union = "";
  170. for (qw(ar ap gl)) {
  171. if ($form->{accounttype} eq 'gifi') {
  172. $query = qq|
  173. $union
  174. SELECT SUM(ac.amount)
  175. FROM acc_trans ac
  176. JOIN $_ a
  177. ON
  178. (a.id =
  179. ac.trans_id)
  180. JOIN chart c
  181. ON
  182. (ac.chart_id =
  183. c.id)
  184. WHERE c.gifi_accno = ?
  185. AND ac.transdate
  186. < ?
  187. AND
  188. a.department_id
  189. = ?
  190. $project |;
  191. push @queryargs,
  192. $form->{gifi_accno},
  193. $form->{fromdate},
  194. $form->{department_id};
  195. if ($p_id){
  196. push @queryargs, $p_id;
  197. }
  198. } else {
  199. $query .= qq|
  200. $union
  201. SELECT SUM(ac.amount)
  202. FROM acc_trans ac
  203. JOIN $_ a ON
  204. (a.id =
  205. ac.trans_id)
  206. JOIN chart c ON
  207. (ac.chart_id =
  208. c.id)
  209. WHERE c.accno = ?
  210. AND ac.transdate
  211. < ?
  212. AND
  213. a.department_id
  214. = ?
  215. $project |;
  216. push @queryargs, $form->{accno},
  217. $form->{fromdate},
  218. $department_id;
  219. if ($p_id){
  220. push @queryargs, $p_id;
  221. }
  222. }
  223. $union = qq| UNION ALL |;
  224. }
  225. } else {
  226. if ($form->{accounttype} eq 'gifi') {
  227. $query = qq|
  228. SELECT SUM(ac.amount)
  229. FROM acc_trans ac
  230. JOIN chart c ON
  231. (ac.chart_id = c.id)
  232. WHERE c.gifi_accno = ?
  233. AND ac.transdate < ?
  234. $project |;
  235. @queryargs = ($form->{gifi_accno},
  236. $form->{fromdate});
  237. if ($p_id){
  238. push @query_ags, $p_id;
  239. }
  240. } else {
  241. $query = qq|
  242. SELECT SUM(ac.amount)
  243. FROM acc_trans ac
  244. JOIN chart c
  245. ON (ac.chart_id = c.id)
  246. WHERE c.accno = ?
  247. AND ac.transdate < ?
  248. $project |;
  249. @queryargs = ($form->{accno},
  250. $form->{fromdate});
  251. if ($p_id){
  252. push @queryargs, $p_id;
  253. }
  254. }
  255. }
  256. $sth = $dbh->prepare($query);
  257. $sth->execute(@queryargs);
  258. ($form->{balance}) = $sth->fetchrow_array($query);
  259. $sth->finish;
  260. @queryargs = ();
  261. }
  262. }
  263. $query = "";
  264. $union = "";
  265. foreach my $id (@id) {
  266. # get all transactions
  267. $query .= qq|
  268. $union
  269. SELECT a.id, a.reference, a.description, ac.transdate,
  270. $false AS invoice, ac.amount, 'gl' as module,
  271. ac.cleared, ac.source, '' AS till, ac.chart_id
  272. FROM gl a
  273. JOIN acc_trans ac ON (ac.trans_id = a.id)
  274. $dpt_join
  275. WHERE ac.chart_id = ?
  276. $fromdate_where
  277. $todate_where
  278. $dpt_where
  279. $project|;
  280. if ($d_id){
  281. push @queryargs, $d_id;
  282. }
  283. push @queryargs, $id;
  284. if ($fdate){
  285. push @queryargs, $fdate;
  286. }
  287. if ($tdate){
  288. push @queryargs, $tdate;
  289. }
  290. if ($d_id){
  291. push @queryargs, $d_id;
  292. }
  293. if ($p_id){
  294. push @queryargs, $p_id;
  295. }
  296. $query .= qq|
  297. UNION ALL
  298. SELECT a.id, a.invnumber, c.name, ac.transdate,
  299. a.invoice, ac.amount, 'ar' as module, ac.cleared,
  300. ac.source, a.till, ac.chart_id
  301. FROM ar a
  302. JOIN acc_trans ac ON (ac.trans_id = a.id)
  303. JOIN customer c ON (a.customer_id = c.id)
  304. $dpt_join
  305. WHERE ac.chart_id = ?
  306. $fromdate_where
  307. $todate_where
  308. $dpt_where
  309. $project|;
  310. if ($d_id){
  311. push @queryargs, $d_id;
  312. }
  313. push @queryargs, $id;
  314. if ($fdate){
  315. push @queryargs, $fdate;
  316. }
  317. if ($tdate){
  318. push @queryargs, $tdate;
  319. }
  320. if ($d_id){
  321. push @queryargs, $d_id;
  322. }
  323. if ($p_id){
  324. push @queryargs, $p_id;
  325. }
  326. $query .= qq|
  327. UNION ALL
  328. SELECT a.id, a.invnumber, v.name, ac.transdate,
  329. a.invoice, ac.amount, 'ap' as module, ac.cleared,
  330. ac.source, a.till, ac.chart_id
  331. FROM ap a
  332. JOIN acc_trans ac ON (ac.trans_id = a.id)
  333. JOIN vendor v ON (a.vendor_id = v.id)
  334. $dpt_join
  335. WHERE ac.chart_id = ?
  336. $fromdate_where
  337. $todate_where
  338. $dpt_where
  339. $project |;
  340. if ($d_id){
  341. push @queryargs, $d_id;
  342. }
  343. push @queryargs, $id;
  344. if ($fdate){
  345. push @queryargs, $fdate;
  346. }
  347. if ($tdate){
  348. push @queryargs, $tdate;
  349. }
  350. if ($d_id){
  351. push @queryargs, $d_id;
  352. }
  353. if ($p_id){
  354. push @queryargs, $p_id;
  355. }
  356. $union = qq| UNION ALL |;
  357. }
  358. $query .= qq| ORDER BY $sortorder |;
  359. $sth = $dbh->prepare($query);
  360. $sth->execute(@queryargs) || $form->dberror($query);
  361. $query = qq|SELECT c.id, c.accno
  362. FROM chart c
  363. JOIN acc_trans ac ON (ac.chart_id = c.id)
  364. WHERE ac.amount >= 0
  365. AND (c.link = 'AR' OR c.link = 'AP')
  366. AND ac.trans_id = ?|;
  367. my $dr = $dbh->prepare($query) || $form->dberror($query);
  368. $query = qq|SELECT c.id, c.accno
  369. FROM chart c
  370. JOIN acc_trans ac ON (ac.chart_id = c.id)
  371. WHERE ac.amount < 0
  372. AND (c.link = 'AR' OR c.link = 'AP')
  373. AND ac.trans_id = ?|;
  374. my $cr = $dbh->prepare($query) || $form->dberror($query);
  375. my $accno;
  376. my $chart_id;
  377. my %accno;
  378. while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
  379. # gl
  380. if ($ca->{module} eq "gl") {
  381. $ca->{module} = "gl";
  382. }
  383. # ap
  384. if ($ca->{module} eq "ap") {
  385. $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap';
  386. $ca->{module} = 'ps' if $ca->{till};
  387. }
  388. # ar
  389. if ($ca->{module} eq "ar") {
  390. $ca->{module} = ($ca->{invoice}) ? 'is' : 'ar';
  391. $ca->{module} = 'ps' if $ca->{till};
  392. }
  393. if ($ca->{amount}) {
  394. %accno = ();
  395. if ($ca->{amount} < 0) {
  396. $ca->{debit} = $ca->{amount} * -1;
  397. $ca->{credit} = 0;
  398. $dr->execute($ca->{id});
  399. $ca->{accno} = ();
  400. while (($chart_id, $accno) = $dr->fetchrow_array) {
  401. $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
  402. }
  403. $dr->finish;
  404. for (sort keys %accno) { push @{ $ca->{accno} }, "$_ " }
  405. } else {
  406. $ca->{credit} = $ca->{amount};
  407. $ca->{debit} = 0;
  408. $cr->execute($ca->{id});
  409. $ca->{accno} = ();
  410. while (($chart_id, $accno) = $cr->fetchrow_array) {
  411. $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
  412. }
  413. $cr->finish;
  414. for (keys %accno) { push @{ $ca->{accno} }, "$_ " }
  415. }
  416. push @{ $form->{CA} }, $ca;
  417. }
  418. }
  419. $sth->finish;
  420. $dbh->commit;
  421. }
  422. 1;