summaryrefslogtreecommitdiff
path: root/LedgerSMB/CA.pm
blob: 389550ae595b798628b0f7861b92377acb2e8487 (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. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  48. $amount{ $ref->{accno} } = $ref->{amount};
  49. }
  50. $sth->finish;
  51. $query = qq|
  52. SELECT accno, description
  53. FROM gifi|;
  54. $sth = $dbh->prepare($query);
  55. $sth->execute || $form->dberror($query);
  56. my $gifi = ();
  57. while ( my ( $accno, $description ) = $sth->fetchrow_array ) {
  58. $gifi{$accno} = $description;
  59. }
  60. $sth->finish;
  61. $query = qq|
  62. SELECT c.id, c.accno, c.description, c.charttype,
  63. c.gifi_accno, c.category, c.link
  64. FROM chart c
  65. ORDER BY accno|;
  66. $sth = $dbh->prepare($query);
  67. $sth->execute || $form->dberror($query);
  68. while ( my $ca = $sth->fetchrow_hashref(NAME_lc) ) {
  69. $ca->{amount} = $amount{ $ca->{accno} };
  70. $ca->{gifi_description} = $gifi{ $ca->{gifi_accno} };
  71. if ( $ca->{amount} < 0 ) {
  72. $ca->{debit} = $ca->{amount} * -1;
  73. }
  74. else {
  75. $ca->{credit} = $ca->{amount};
  76. }
  77. push @{ $form->{CA} }, $ca;
  78. }
  79. $sth->finish;
  80. $dbh->commit;
  81. }
  82. sub all_transactions {
  83. my ( $self, $myconfig, $form ) = @_;
  84. # connect to database
  85. my $dbh = $form->{dbh};
  86. # get chart_id
  87. my $query = qq|
  88. SELECT id
  89. FROM chart
  90. WHERE accno = ?|;
  91. my $accno = $form->{accno};
  92. if ( $form->{accounttype} eq 'gifi' ) {
  93. $query = qq|
  94. SELECT id
  95. FROM chart
  96. WHERE gifi_accno = ?|;
  97. $accno = $form->{gifi_accno};
  98. }
  99. my $sth = $dbh->prepare($query);
  100. $sth->execute($accno) || $form->dberror($query);
  101. my @id = ();
  102. while ( my ($id) = $sth->fetchrow_array ) {
  103. push @id, $id;
  104. }
  105. $sth->finish;
  106. my $fromdate_where;
  107. my $todate_where;
  108. ( $form->{fromdate}, $form->{todate} ) =
  109. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  110. if !$form->{fromdate} && !$form->{todate};
  111. my $fdate;
  112. if ( $form->{fromdate} ) {
  113. $fromdate_where = qq| AND ac.transdate >= ? |;
  114. $fdate = $form->{fromdate};
  115. }
  116. my $tdate;
  117. if ( $form->{todate} ) {
  118. $todate_where .= qq| AND ac.transdate <= ? |;
  119. $tdate = $form->{todate};
  120. }
  121. my $false = 'FALSE';
  122. # Oracle workaround, use ordinal positions
  123. my %ordinal = (
  124. transdate => 4,
  125. reference => 2,
  126. description => 3
  127. );
  128. my @a = qw(transdate reference description);
  129. my $sortorder = $form->sort_order( \@a, \%ordinal );
  130. my $null;
  131. my $department_id;
  132. my $dpt_where;
  133. my $dpt_join;
  134. my $union;
  135. ( $null, $department_id ) = split /--/, $form->{department};
  136. my $d_id;
  137. if ($department_id) {
  138. $dpt_join = qq| JOIN department t ON (t.id = a.department_id) |;
  139. $dpt_where = qq| AND t.id = ? |;
  140. $d_id = $department_id;
  141. }
  142. my $project;
  143. my $project_id;
  144. my $p_id;
  145. if ( $form->{projectnumber} ) {
  146. ( $null, $project_id ) = split /--/, $form->{projectnumber};
  147. $project = qq| AND ac.project_id = ? |;
  148. $p_id = $project_id;
  149. }
  150. @queryargs = ();
  151. if ( $form->{accno} || $form->{gifi_accno} ) {
  152. # get category for account
  153. $query = qq|
  154. SELECT description, category, link, contra
  155. FROM chart
  156. WHERE accno = ?|;
  157. $accno = $form->{accno};
  158. if ( $form->{accounttype} eq 'gifi' ) {
  159. $query = qq|
  160. SELECT description, category, link, contra
  161. FROM chart
  162. WHERE gifi_accno = ?
  163. AND charttype = 'A'|;
  164. $accno = $form->{gifi_accno};
  165. }
  166. $sth = $dbh->prepare($query);
  167. $sth->execute($accno);
  168. (
  169. $form->{description}, $form->{category},
  170. $form->{link}, $form->{contra}
  171. ) = $sth->fetchrow_array;
  172. if ( $form->{fromdate} ) {
  173. if ($department_id) {
  174. # get beginning balance
  175. $query = "";
  176. $union = "";
  177. for (qw(ar ap gl)) {
  178. if ( $form->{accounttype} eq 'gifi' ) {
  179. $query = qq|
  180. $union
  181. SELECT SUM(ac.amount)
  182. FROM acc_trans ac
  183. JOIN $_ a
  184. ON
  185. (a.id =
  186. ac.trans_id)
  187. JOIN chart c
  188. ON
  189. (ac.chart_id =
  190. c.id)
  191. WHERE c.gifi_accno = ?
  192. AND ac.transdate
  193. < ?
  194. AND
  195. a.department_id
  196. = ?
  197. $project |;
  198. push @queryargs, $form->{gifi_accno}, $form->{fromdate},
  199. $form->{department_id};
  200. if ($p_id) {
  201. push @queryargs, $p_id;
  202. }
  203. }
  204. else {
  205. $query .= qq|
  206. $union
  207. SELECT SUM(ac.amount)
  208. FROM acc_trans ac
  209. JOIN $_ a ON
  210. (a.id =
  211. ac.trans_id)
  212. JOIN chart c ON
  213. (ac.chart_id =
  214. c.id)
  215. WHERE c.accno = ?
  216. AND ac.transdate
  217. < ?
  218. AND
  219. a.department_id
  220. = ?
  221. $project |;
  222. push @queryargs, $form->{accno}, $form->{fromdate},
  223. $department_id;
  224. if ($p_id) {
  225. push @queryargs, $p_id;
  226. }
  227. }
  228. $union = qq| UNION ALL |;
  229. }
  230. }
  231. else {
  232. if ( $form->{accounttype} eq 'gifi' ) {
  233. $query = qq|
  234. SELECT SUM(ac.amount)
  235. FROM acc_trans ac
  236. JOIN chart c ON
  237. (ac.chart_id = c.id)
  238. WHERE c.gifi_accno = ?
  239. AND ac.transdate < ?
  240. $project |;
  241. @queryargs = ( $form->{gifi_accno}, $form->{fromdate} );
  242. if ($p_id) {
  243. push @query_ags, $p_id;
  244. }
  245. }
  246. else {
  247. $query = qq|
  248. SELECT SUM(ac.amount)
  249. FROM acc_trans ac
  250. JOIN chart c
  251. ON (ac.chart_id = c.id)
  252. WHERE c.accno = ?
  253. AND ac.transdate < ?
  254. $project |;
  255. @queryargs = ( $form->{accno}, $form->{fromdate} );
  256. if ($p_id) {
  257. push @queryargs, $p_id;
  258. }
  259. }
  260. }
  261. $sth = $dbh->prepare($query);
  262. $sth->execute(@queryargs);
  263. my @balance = $sth->fetchrow_array;
  264. $form->db_parse_numeric(sth=>$sth, arrayref=>\@balance);
  265. ( $form->{balance} ) = @balance;
  266. $sth->finish;
  267. @queryargs = ();
  268. }
  269. }
  270. $query = "";
  271. $union = "";
  272. foreach my $id (@id) {
  273. # get all transactions
  274. $query .= qq|
  275. $union
  276. SELECT a.id, a.reference, a.description, ac.transdate,
  277. $false AS invoice, ac.amount, 'gl' as module,
  278. ac.cleared, ac.source, '' AS till, ac.chart_id
  279. FROM gl a
  280. JOIN acc_trans ac ON (ac.trans_id = a.id)
  281. $dpt_join
  282. WHERE ac.chart_id = ?
  283. $fromdate_where
  284. $todate_where
  285. $dpt_where
  286. $project|;
  287. if ($d_id) {
  288. push @queryargs, $d_id;
  289. }
  290. push @queryargs, $id;
  291. if ($fdate) {
  292. push @queryargs, $fdate;
  293. }
  294. if ($tdate) {
  295. push @queryargs, $tdate;
  296. }
  297. if ($d_id) {
  298. push @queryargs, $d_id;
  299. }
  300. if ($p_id) {
  301. push @queryargs, $p_id;
  302. }
  303. $query .= qq|
  304. UNION ALL
  305. SELECT a.id, a.invnumber, e.name, ac.transdate,
  306. a.invoice, ac.amount, 'ar' as module, ac.cleared,
  307. ac.source, a.till, ac.chart_id
  308. FROM ar a
  309. JOIN acc_trans ac ON (ac.trans_id = a.id)
  310. JOIN entity e ON (a.entity_id = e.id)
  311. $dpt_join
  312. WHERE ac.chart_id = ?
  313. $fromdate_where
  314. $todate_where
  315. $dpt_where
  316. $project|;
  317. if ($d_id) {
  318. push @queryargs, $d_id;
  319. }
  320. push @queryargs, $id;
  321. if ($fdate) {
  322. push @queryargs, $fdate;
  323. }
  324. if ($tdate) {
  325. push @queryargs, $tdate;
  326. }
  327. if ($d_id) {
  328. push @queryargs, $d_id;
  329. }
  330. if ($p_id) {
  331. push @queryargs, $p_id;
  332. }
  333. $query .= qq|
  334. UNION ALL
  335. SELECT a.id, a.invnumber, e.name, ac.transdate,
  336. a.invoice, ac.amount, 'ap' as module, ac.cleared,
  337. ac.source, a.till, ac.chart_id
  338. FROM ap a
  339. JOIN acc_trans ac ON (ac.trans_id = a.id)
  340. JOIN entity e ON (a.entity_id = e.id)
  341. $dpt_join
  342. WHERE ac.chart_id = ?
  343. $fromdate_where
  344. $todate_where
  345. $dpt_where
  346. $project |;
  347. if ($d_id) {
  348. push @queryargs, $d_id;
  349. }
  350. push @queryargs, $id;
  351. if ($fdate) {
  352. push @queryargs, $fdate;
  353. }
  354. if ($tdate) {
  355. push @queryargs, $tdate;
  356. }
  357. if ($d_id) {
  358. push @queryargs, $d_id;
  359. }
  360. if ($p_id) {
  361. push @queryargs, $p_id;
  362. }
  363. $union = qq| UNION ALL |;
  364. }
  365. $query .= qq| ORDER BY $sortorder |;
  366. $sth = $dbh->prepare($query);
  367. $sth->execute(@queryargs) || $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 $dr = $dbh->prepare($query) || $form->dberror($query);
  375. $query = qq|SELECT c.id, c.accno
  376. FROM chart c
  377. JOIN acc_trans ac ON (ac.chart_id = c.id)
  378. WHERE ac.amount < 0
  379. AND (c.link = 'AR' OR c.link = 'AP')
  380. AND ac.trans_id = ?|;
  381. my $cr = $dbh->prepare($query) || $form->dberror($query);
  382. my $accno;
  383. my $chart_id;
  384. my %accno;
  385. while ( my $ca = $sth->fetchrow_hashref(NAME_lc) ) {
  386. $form->db_parse_numeric(sth=>$sth, hashref=>$ca);
  387. # gl
  388. if ( $ca->{module} eq "gl" ) {
  389. $ca->{module} = "gl";
  390. }
  391. # ap
  392. if ( $ca->{module} eq "ap" ) {
  393. $ca->{module} = ( $ca->{invoice} ) ? 'ir' : 'ap';
  394. $ca->{module} = 'ps' if $ca->{till};
  395. }
  396. # ar
  397. if ( $ca->{module} eq "ar" ) {
  398. $ca->{module} = ( $ca->{invoice} ) ? 'is' : 'ar';
  399. $ca->{module} = 'ps' if $ca->{till};
  400. }
  401. if ( $ca->{amount} ) {
  402. %accno = ();
  403. if ( $ca->{amount} < 0 ) {
  404. $ca->{debit} = $ca->{amount} * -1;
  405. $ca->{credit} = 0;
  406. $dr->execute( $ca->{id} );
  407. $ca->{accno} = ();
  408. while ( my @dr_results = $dr->fetchrow_array ) {
  409. $form->db_parse_numeric(sth=>$dr, arrayref=>\@dr_results);
  410. ($chart_id, $accno) = @dr_results;
  411. $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
  412. }
  413. $dr->finish;
  414. for ( sort keys %accno ) { push @{ $ca->{accno} }, "$_ " }
  415. }
  416. else {
  417. $ca->{credit} = $ca->{amount};
  418. $ca->{debit} = 0;
  419. $cr->execute( $ca->{id} );
  420. $ca->{accno} = ();
  421. while ( my @cr_results = $cr->fetchrow_array ) {
  422. $form->db_parse_numeric(sth=>$cr, arrayref=>\@cr_results);
  423. ($chart_id, $accno) = @cr_results;
  424. $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
  425. }
  426. $cr->finish;
  427. for ( keys %accno ) { push @{ $ca->{accno} }, "$_ " }
  428. }
  429. push @{ $form->{CA} }, $ca;
  430. }
  431. }
  432. $sth->finish;
  433. $dbh->commit;
  434. }
  435. 1;