summaryrefslogtreecommitdiff
path: root/LedgerSMB/GL.pm
blob: 8979d3cab68725da524f2c35a5778f47ae2d3735 (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) 2000
  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. # General ledger backend code
  31. #
  32. #======================================================================
  33. package GL;
  34. sub delete_transaction {
  35. my ( $self, $myconfig, $form ) = @_;
  36. # connect to database
  37. my $dbh = $form->{dbh};
  38. my %audittrail = (
  39. tablename => 'gl',
  40. reference => $form->{reference},
  41. formname => 'transaction',
  42. action => 'deleted',
  43. id => $form->{id}
  44. );
  45. $form->audittrail( $dbh, "", \%audittrail );
  46. my $id = $dbh->quote( $form->{id} );
  47. my $query = qq|DELETE FROM gl WHERE id = $id|;
  48. $dbh->do($query) || $form->dberror($query);
  49. $query = qq|DELETE FROM acc_trans WHERE trans_id = $id|;
  50. $dbh->do($query) || $form->dberror($query);
  51. # commit and redirect
  52. my $rc = $dbh->commit;
  53. $rc;
  54. }
  55. sub post_transaction {
  56. my ( $self, $myconfig, $form ) = @_;
  57. $form->{reference} = $form->update_defaults( $myconfig, 'glnumber', $dbh )
  58. unless $form->{reference};
  59. my $null;
  60. my $project_id;
  61. my $department_id;
  62. my $i;
  63. # connect to database, turn off AutoCommit
  64. my $dbh = $form->{dbh};
  65. my $query;
  66. my $sth;
  67. my $id = $dbh->quote( $form->{id} );
  68. if ( $form->{id} ) {
  69. $query = qq|SELECT id FROM gl WHERE id = $id|;
  70. ( $form->{id} ) = $dbh->selectrow_array($query);
  71. if ( $form->{id} ) {
  72. # delete individual transactions
  73. $query = qq|
  74. DELETE FROM acc_trans WHERE trans_id = $id|;
  75. $dbh->do($query) || $form->dberror($query);
  76. }
  77. }
  78. if ( !$form->{id} ) {
  79. my $uid = localtime;
  80. $uid .= "$$";
  81. $query = qq|
  82. INSERT INTO gl (reference)
  83. VALUES ('$uid')|;
  84. $sth = $dbh->prepare($query);
  85. $sth->execute() || $form->dberror($query);
  86. $query = qq|
  87. SELECT id
  88. FROM gl
  89. WHERE reference = '$uid'|;
  90. ( $form->{id} ) = $dbh->selectrow_array($query);
  91. }
  92. ( $null, $department_id ) = split /--/, $form->{department};
  93. $department_id *= 1;
  94. $form->{reference} ||= $form->{id};
  95. $query = qq|
  96. UPDATE gl
  97. SET reference = | . $dbh->quote( $form->{reference} ) . qq|,
  98. description = | . $dbh->quote( $form->{description} ) . qq|,
  99. notes = | . $dbh->quote( $form->{notes} ) . qq|,
  100. transdate = ?,
  101. department_id = ?
  102. WHERE id = ?|;
  103. if (defined $form->{approved}) {
  104. my $query = qq| UPDATE gl SET approved = ? WHERE id = ?|;
  105. $dbh->prepare($query)->execute($form->{approved}, $form->{id})
  106. || $form->dberror($query);
  107. if (!$form->{approved}){
  108. if (not defined $form->{batch_id}){
  109. $form->error($locale->text('Batch ID Missing'));
  110. }
  111. my $query = qq|
  112. INSERT INTO voucher (batch_id, trans_id, batch_class)
  113. VALUES (?, ?, (select id FROM batch_class
  114. WHERE class = ?))|;
  115. my $sth2 = $dbh->prepare($query);
  116. $sth2->execute($form->{batch_id}, $form->{id}, 'gl') ||
  117. $form->dberror($query);
  118. }
  119. }
  120. $sth = $dbh->prepare($query);
  121. print STDERR $query;
  122. $sth->execute( $form->{transdate}, $department_id, $form->{id} )
  123. || $form->dberror($query);
  124. my $amount = 0;
  125. my $posted = 0;
  126. my $debit;
  127. my $credit;
  128. # insert acc_trans transactions
  129. for $i ( 1 .. $form->{rowcount} ) {
  130. $debit = $form->parse_amount( $myconfig, $form->{"debit_$i"} );
  131. $credit = $form->parse_amount( $myconfig, $form->{"credit_$i"} );
  132. # extract accno
  133. ($accno) = split( /--/, $form->{"accno_$i"} );
  134. if ($credit) {
  135. $amount = $credit;
  136. $posted = 0;
  137. }
  138. if ($debit) {
  139. $amount = $debit * -1;
  140. $posted = 0;
  141. }
  142. # add the record
  143. if ( !$posted ) {
  144. ( $null, $project_id ) = split /--/, $form->{"projectnumber_$i"};
  145. $project_id ||= undef;
  146. $query = qq|
  147. INSERT INTO acc_trans
  148. (trans_id, chart_id, amount,
  149. transdate, source, project_id,
  150. fx_transaction, memo, cleared)
  151. VALUES (?, (SELECT id
  152. FROM chart
  153. WHERE accno = ?),
  154. ?, ?, ?, ?, ?, ?, ?)|;
  155. $sth = $dbh->prepare($query);
  156. $sth->execute(
  157. $form->{id}, $accno,
  158. $amount, $form->{transdate},
  159. $form->{"source_$i"}, $project_id,
  160. ($form->{"fx_transaction_$i"} || 0), $form->{"memo_$i"},
  161. ($form->{"cleared_$i"} || 0)
  162. ) || $form->dberror($query);
  163. $posted = 1;
  164. }
  165. }
  166. my %audittrail = (
  167. tablename => 'gl',
  168. reference => $form->{reference},
  169. formname => 'transaction',
  170. action => 'posted',
  171. id => $form->{id}
  172. );
  173. $form->audittrail( $dbh, "", \%audittrail );
  174. $form->save_recurring( $dbh, $myconfig );
  175. # commit and redirect
  176. my $rc = $dbh->commit;
  177. $rc;
  178. }
  179. sub all_transactions {
  180. my ( $self, $myconfig, $form ) = @_;
  181. # connect to database
  182. my $dbh = $form->{dbh};
  183. my $query;
  184. my $sth;
  185. my $var;
  186. my $null;
  187. my ( $glwhere, $arwhere, $apwhere ) = ( "1 = 1", "1 = 1", "1 = 1" );
  188. if ( $form->{reference} ne "" ) {
  189. $var = $dbh->quote( $form->like( lc $form->{reference} ) );
  190. $glwhere .= " AND lower(g.reference) LIKE $var";
  191. $arwhere .= " AND lower(a.invnumber) LIKE $var";
  192. $apwhere .= " AND lower(a.invnumber) LIKE $var";
  193. }
  194. if ( $form->{department} ne "" ) {
  195. ( $null, $var ) = split /--/, $form->{department};
  196. $var = $dbh->quote($var);
  197. $glwhere .= " AND g.department_id = $var";
  198. $arwhere .= " AND a.department_id = $var";
  199. $apwhere .= " AND a.department_id = $var";
  200. }
  201. if ( $form->{source} ne "" ) {
  202. $var = $dbh->quote( $form->like( lc $form->{source} ) );
  203. $glwhere .= " AND lower(ac.source) LIKE $var";
  204. $arwhere .= " AND lower(ac.source) LIKE $var";
  205. $apwhere .= " AND lower(ac.source) LIKE $var";
  206. }
  207. if ( $form->{memo} ne "" ) {
  208. $var = $dbh->quote( $form->like( lc $form->{memo} ) );
  209. $glwhere .= " AND lower(ac.memo) LIKE $var";
  210. $arwhere .= " AND lower(ac.memo) LIKE $var";
  211. $apwhere .= " AND lower(ac.memo) LIKE $var";
  212. }
  213. ( $form->{datefrom}, $form->{dateto} ) =
  214. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  215. if $form->{year} && $form->{month};
  216. if ( $form->{datefrom} ) {
  217. $glwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );
  218. $arwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );
  219. $apwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );
  220. }
  221. if ( $form->{dateto} ) {
  222. $glwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );
  223. $arwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );
  224. $apwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );
  225. }
  226. if ( $form->{amountfrom} ) {
  227. $glwhere .=
  228. " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );
  229. $arwhere .=
  230. " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );
  231. $apwhere .=
  232. " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );
  233. }
  234. if ( $form->{amountto} ) {
  235. $glwhere .=
  236. " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );
  237. $arwhere .=
  238. " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );
  239. $apwhere .=
  240. " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );
  241. }
  242. if ( $form->{description} ) {
  243. $var = $dbh->quote( $form->like( lc $form->{description} ) );
  244. $glwhere .= " AND lower(g.description) LIKE $var";
  245. $arwhere .= " AND (lower(ct.name) LIKE $var
  246. OR lower(ac.memo) LIKE $var
  247. OR a.id IN (SELECT DISTINCT trans_id
  248. FROM invoice
  249. WHERE lower(description) LIKE $var))";
  250. $apwhere .= " AND (lower(ct.name) LIKE $var
  251. OR lower(ac.memo) LIKE $var
  252. OR a.id IN (SELECT DISTINCT trans_id
  253. FROM invoice
  254. WHERE lower(description) LIKE $var))";
  255. }
  256. if ( $form->{notes} ) {
  257. $var = $dbh->quote( $form->like( lc $form->{notes} ) );
  258. $glwhere .= " AND lower(g.notes) LIKE $var";
  259. $arwhere .= " AND lower(a.notes) LIKE $var";
  260. $apwhere .= " AND lower(a.notes) LIKE $var";
  261. }
  262. if ( $form->{accno} ) {
  263. $var = $dbh->quote( $form->{accno} );
  264. $glwhere .= " AND c.accno = $var";
  265. $arwhere .= " AND c.accno = $var";
  266. $apwhere .= " AND c.accno = $var";
  267. }
  268. if ( $form->{gifi_accno} ) {
  269. $var = $dbh->quote( $form->{gifi_accno} );
  270. $glwhere .= " AND c.gifi_accno = $var";
  271. $arwhere .= " AND c.gifi_accno = $var";
  272. $apwhere .= " AND c.gifi_accno = $var";
  273. }
  274. if ( $form->{category} ne 'X' ) {
  275. $var = $dbh->quote( $form->{category} );
  276. $glwhere .= " AND c.category = $var";
  277. $arwhere .= " AND c.category = $var";
  278. $apwhere .= " AND c.category = $var";
  279. }
  280. if ( $form->{accno} ) {
  281. my $accno = $dbh->quote( $form->{accno} );
  282. # get category for account
  283. $query = qq|SELECT category, link, contra, description
  284. FROM chart
  285. WHERE accno = $accno|;
  286. (
  287. $form->{category}, $form->{link}, $form->{contra},
  288. $form->{account_description}
  289. ) = $dbh->selectrow_array($query);
  290. if ( $form->{datefrom} ) {
  291. $query = qq|
  292. SELECT SUM(ac.amount)
  293. FROM acc_trans ac
  294. JOIN chart c ON (ac.chart_id = c.id)
  295. WHERE c.accno = $accno
  296. AND ac.transdate < date | . $dbh->quote( $form->{datefrom} );
  297. ( $form->{balance} ) = $dbh->selectrow_array($query);
  298. }
  299. }
  300. if ( $form->{gifi_accno} ) {
  301. my $gifi = $dbh->quote( $form->{gifi_accno} );
  302. # get category for account
  303. $query = qq|SELECT c.category, c.link, c.contra, g.description
  304. FROM chart c
  305. LEFT JOIN gifi g ON (g.accno = c.gifi_accno)
  306. WHERE c.gifi_accno = $gifi|;
  307. (
  308. $form->{category}, $form->{link}, $form->{contra},
  309. $form->{gifi_account_description}
  310. ) = $dbh->selectrow_array($query);
  311. if ( $form->{datefrom} ) {
  312. $query = qq|
  313. SELECT SUM(ac.amount)
  314. FROM acc_trans ac
  315. JOIN chart c ON (ac.chart_id = c.id)
  316. WHERE c.gifi_accno = $gifi
  317. AND ac.transdate < date | . $dbh->quote( $form->{datefrom} );
  318. ( $form->{balance} ) = $dbh->selectrow_array($query);
  319. }
  320. }
  321. my $false = 'FALSE';
  322. my %ordinal = (
  323. id => 1,
  324. reference => 4,
  325. description => 5,
  326. transdate => 6,
  327. source => 7,
  328. accno => 9,
  329. department => 15,
  330. memo => 16
  331. );
  332. my @a = ( id, transdate, reference, source, description, accno );
  333. my $sortorder = $form->sort_order( \@a, \%ordinal );
  334. my $chart_id;
  335. if ($form->{chart_id}){
  336. $chart_id = $dbh->quote($form->{chart_id});
  337. } else {
  338. $chart_id = 'NULL';
  339. }
  340. my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,
  341. g.description, ac.transdate, ac.source,
  342. ac.amount, c.accno, c.gifi_accno, g.notes, c.link,
  343. '' AS till, ac.cleared, d.description AS department,
  344. ac.memo, c.description AS accname
  345. FROM gl AS g
  346. JOIN acc_trans ac ON (g.id = ac.trans_id)
  347. JOIN chart c ON (ac.chart_id = c.id)
  348. LEFT JOIN department d ON (d.id = g.department_id)
  349. WHERE $glwhere
  350. AND (ac.chart_id = $chart_id OR
  351. $chart_id IS NULL)
  352. UNION ALL
  353. SELECT a.id, 'ar' AS type, a.invoice, a.invnumber,
  354. e.name, ac.transdate, ac.source,
  355. ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
  356. a.till, ac.cleared, d.description AS department,
  357. ac.memo, c.description AS accname
  358. FROM ar a
  359. JOIN acc_trans ac ON (a.id = ac.trans_id)
  360. JOIN chart c ON (ac.chart_id = c.id)
  361. JOIN entity e ON (a.entity_id = e.id)
  362. LEFT JOIN department d ON (d.id = a.department_id)
  363. WHERE $arwhere
  364. AND (ac.chart_id = $chart_id OR
  365. $chart_id IS NULL)
  366. UNION ALL
  367. SELECT a.id, 'ap' AS type, a.invoice, a.invnumber,
  368. e.name, ac.transdate, ac.source,
  369. ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
  370. a.till, ac.cleared, d.description AS department,
  371. ac.memo, c.description AS accname
  372. FROM ap a
  373. JOIN acc_trans ac ON (a.id = ac.trans_id)
  374. JOIN chart c ON (ac.chart_id = c.id)
  375. JOIN entity e ON (a.entity_id = e.id)
  376. LEFT JOIN department d ON (d.id = a.department_id)
  377. WHERE $apwhere
  378. AND (ac.chart_id = $chart_id OR
  379. $chart_id IS NULL)
  380. ORDER BY $sortorder|;
  381. my $sth = $dbh->prepare($query);
  382. $sth->execute || $form->dberror($query);
  383. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  384. # gl
  385. if ( $ref->{type} eq "gl" ) {
  386. $ref->{module} = "gl";
  387. }
  388. # ap
  389. if ( $ref->{type} eq "ap" ) {
  390. if ( $ref->{invoice} ) {
  391. $ref->{module} = "ir";
  392. }
  393. else {
  394. $ref->{module} = "ap";
  395. }
  396. }
  397. # ar
  398. if ( $ref->{type} eq "ar" ) {
  399. if ( $ref->{invoice} ) {
  400. $ref->{module} = ( $ref->{till} ) ? "ps" : "is";
  401. }
  402. else {
  403. $ref->{module} = "ar";
  404. }
  405. }
  406. if ( $ref->{amount} < 0 ) {
  407. $ref->{debit} = $ref->{amount} * -1;
  408. $ref->{credit} = 0;
  409. }
  410. else {
  411. $ref->{credit} = $ref->{amount};
  412. $ref->{debit} = 0;
  413. }
  414. push @{ $form->{GL} }, $ref;
  415. }
  416. $sth->finish;
  417. $dbh->commit;
  418. }
  419. sub transaction {
  420. my ( $self, $myconfig, $form ) = @_;
  421. my ( $query, $sth, $ref );
  422. # connect to database
  423. my $dbh = $form->{dbh};
  424. if ( $form->{id} ) {
  425. $query = "SELECT setting_key, value
  426. FROM defaults
  427. WHERE setting_key IN
  428. ('closedto', 'revtrans')";
  429. $sth = $dbh->prepare($query);
  430. $sth->execute || $form->dberror($query);
  431. my $results = $sth->fetchall_hashref('setting_key');
  432. $form->{closedto} = $results->{'closedto'}->{'value'};
  433. $form->{revtrans} = $results->{'revtrans'}->{'value'};
  434. $sth->finish;
  435. $query = qq|SELECT g.*, d.description AS department
  436. FROM gl g
  437. LEFT JOIN department d ON (d.id = g.department_id)
  438. WHERE g.id = ?|;
  439. $sth = $dbh->prepare($query);
  440. $sth->execute( $form->{id} ) || $form->dberror($query);
  441. $ref = $sth->fetchrow_hashref(NAME_lc);
  442. for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
  443. $sth->finish;
  444. # retrieve individual rows
  445. $query = qq|SELECT ac.*, c.accno, c.description, p.projectnumber
  446. FROM acc_trans ac
  447. JOIN chart c ON (ac.chart_id = c.id)
  448. LEFT JOIN project p ON (p.id = ac.project_id)
  449. WHERE ac.trans_id = ?
  450. ORDER BY accno|;
  451. $sth = $dbh->prepare($query);
  452. $sth->execute( $form->{id} ) || $form->dberror($query);
  453. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  454. if ( $ref->{fx_transaction} ) {
  455. $form->{transfer} = 1;
  456. }
  457. push @{ $form->{GL} }, $ref;
  458. }
  459. # get recurring transaction
  460. $form->get_recurring($dbh);
  461. }
  462. else {
  463. $query = "SELECT current_date AS transdate, setting_key, value
  464. FROM defaults
  465. WHERE setting_key IN
  466. ('closedto', 'revtrans')";
  467. $sth = $dbh->prepare($query);
  468. $sth->execute || $form->dberror($query);
  469. my $results = $sth->fetchall_hashref('setting_key');
  470. $form->{closedto} = $results->{'closedto'}->{'value'};
  471. $form->{revtrans} = $results->{'revtrans'}->{'value'};
  472. if (!$form->{transdate}){
  473. $form->{transdate} = $results->{'revtrans'}->{'transdate'};
  474. }
  475. }
  476. $sth->finish;
  477. # get chart of accounts
  478. $query = qq|SELECT accno,description
  479. FROM chart
  480. WHERE charttype = 'A'
  481. ORDER BY accno|;
  482. $sth = $dbh->prepare($query);
  483. $sth->execute || $form->dberror($query);
  484. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  485. push @{ $form->{all_accno} }, $ref;
  486. }
  487. $sth->finish;
  488. # get departments
  489. $form->all_departments( $myconfig, $dbh );
  490. # get projects
  491. $form->all_projects( $myconfig, $dbh, $form->{transdate} );
  492. $dbh->commit;
  493. }
  494. 1;