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