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