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