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