summaryrefslogtreecommitdiff
path: root/LedgerSMB/GL.pm
blob: c6fe103716f3aa0d9f0bb93fb337798c93bdcef5 (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 = '$form->{transdate}',
  102. department_id = $department_id
  103. WHERE id = $form->{id}|;
  104. $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. $dbh->disconnect;
  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}) = $form->from_to(
  197. $form->{year}, $form->{month}, $form->{interval})
  198. if $form->{year} && $form->{month};
  199. if ($form->{datefrom}) {
  200. $glwhere .= " AND ac.transdate >= ".
  201. $dbh->quote($form->{datefrom});
  202. $arwhere .= " AND ac.transdate >= ".
  203. $dbh->quote($form->{datefrom});
  204. $apwhere .= " AND ac.transdate >= ".
  205. $dbh->quote($form->{datefrom});
  206. }
  207. if ($form->{dateto}) {
  208. $glwhere .= " AND ac.transdate <= ".
  209. $dbh->quote($form->{dateto});
  210. $arwhere .= " AND ac.transdate <= ".
  211. $dbh->quote($form->{dateto});
  212. $apwhere .= " AND ac.transdate <= ".
  213. $dbh->quote($form->{dateto});
  214. }
  215. if ($form->{amountfrom}) {
  216. $glwhere .= " AND abs(ac.amount) >= ".
  217. $dbh->quote($form->{amountfrom});
  218. $arwhere .= " AND abs(ac.amount) >= ".
  219. $dbh->quote($form->{amountfrom});
  220. $apwhere .= " AND abs(ac.amount) >= ".
  221. $dbh->quote($form->{amountfrom});
  222. }
  223. if ($form->{amountto}) {
  224. $glwhere .= " AND abs(ac.amount) <= ".
  225. $dbh->quote($form->{amountto});
  226. $arwhere .= " AND abs(ac.amount) <= ".
  227. $dbh->quote($form->{amountto});
  228. $apwhere .= " AND abs(ac.amount) <= ".
  229. $dbh->quote($form->{amountto});
  230. }
  231. if ($form->{description}) {
  232. $var = $dbh->quote($form->like(lc $form->{description}));
  233. $glwhere .= " AND lower(g.description) LIKE $var";
  234. $arwhere .= " AND (lower(ct.name) LIKE $var
  235. OR lower(ac.memo) LIKE $var
  236. OR a.id IN (SELECT DISTINCT trans_id
  237. FROM invoice
  238. WHERE lower(description) LIKE 'var))";
  239. $apwhere .= " AND (lower(ct.name) LIKE $var
  240. OR lower(ac.memo) LIKE $var
  241. OR a.id IN (SELECT DISTINCT trans_id
  242. FROM invoice
  243. WHERE lower(description) LIKE $var))";
  244. }
  245. if ($form->{notes}) {
  246. $var = $dbh->quote($form->like(lc $form->{notes}));
  247. $glwhere .= " AND lower(g.notes) LIKE $var";
  248. $arwhere .= " AND lower(a.notes) LIKE $var";
  249. $apwhere .= " AND lower(a.notes) LIKE $var";
  250. }
  251. if ($form->{accno}) {
  252. $var = $dbh->quote($form->{accno});
  253. $glwhere .= " AND c.accno = $var";
  254. $arwhere .= " AND c.accno = $var";
  255. $apwhere .= " AND c.accno = $var";
  256. }
  257. if ($form->{gifi_accno}) {
  258. $var = $dbh->quote($form->{gifiaccno});
  259. $glwhere .= " AND c.gifi_accno = $var";
  260. $arwhere .= " AND c.gifi_accno = $var";
  261. $apwhere .= " AND c.gifi_accno = $var";
  262. }
  263. if ($form->{category} ne 'X') {
  264. $var = $dbh->quote($form->{gifiaccno});
  265. $glwhere .= " AND c.category = $var";
  266. $arwhere .= " AND c.category = $var";
  267. $apwhere .= " AND c.category = $var";
  268. }
  269. if ($form->{accno}) {
  270. my $accno = $dbh->quote($form->{accno});
  271. # get category for account
  272. $query = qq|SELECT category, link, contra, description
  273. FROM chart
  274. WHERE accno = $accno|;
  275. ($form->{category}, $form->{link}, $form->{contra},
  276. $form->{account_description}) = $dbh->selectrow_array($query);
  277. if ($form->{datefrom}) {
  278. $query = qq|
  279. SELECT SUM(ac.amount)
  280. FROM acc_trans ac
  281. JOIN chart c ON (ac.chart_id = c.id)
  282. WHERE c.accno = $accno
  283. AND ac.transdate < date |.
  284. $dbh->quote($form->{datefrom});
  285. ($form->{balance}) = $dbh->selectrow_array($query);
  286. }
  287. }
  288. if ($form->{gifi_accno}) {
  289. my $gifi = $dbh->quote($form->{gifi_accno});
  290. # get category for account
  291. $query = qq|SELECT c.category, c.link, c.contra, g.description
  292. FROM chart c
  293. LEFT JOIN gifi g ON (g.accno = c.gifi_accno)
  294. WHERE c.gifi_accno = $gifi|;
  295. ($form->{category}, $form->{link}, $form->{contra},
  296. $form->{gifi_account_description}) = $dbh->selectrow_array(
  297. $query);
  298. if ($form->{datefrom}) {
  299. $query = qq|
  300. SELECT SUM(ac.amount)
  301. FROM acc_trans ac
  302. JOIN chart c ON (ac.chart_id = c.id)
  303. WHERE c.gifi_accno = $gifi
  304. AND ac.transdate < date |.
  305. $dbh->quote($form->{datefrom});
  306. ($form->{balance}) = $dbh->selectrow_array($query);
  307. }
  308. }
  309. my $false = 'FALSE';
  310. my %ordinal = (
  311. id => 1,
  312. reference => 4,
  313. description => 5,
  314. transdate => 6,
  315. source => 7,
  316. accno => 9,
  317. department => 15,
  318. memo => 16 );
  319. my @a = (id, transdate, reference, source, description, accno);
  320. my $sortorder = $form->sort_order(\@a, \%ordinal);
  321. my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,
  322. g.description, ac.transdate, ac.source,
  323. ac.amount, c.accno, c.gifi_accno, g.notes, c.link,
  324. '' AS till, ac.cleared, d.description AS department,
  325. ac.memo
  326. FROM gl AS g
  327. JOIN acc_trans ac ON (g.id = ac.trans_id)
  328. JOIN chart c ON (ac.chart_id = c.id)
  329. LEFT JOIN department d ON (d.id = g.department_id)
  330. WHERE $glwhere
  331. UNION ALL
  332. SELECT a.id, 'ar' AS type, a.invoice, a.invnumber,
  333. ct.name, ac.transdate, ac.source,
  334. ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
  335. a.till, ac.cleared, d.description AS department,
  336. ac.memo
  337. FROM ar a
  338. JOIN acc_trans ac ON (a.id = ac.trans_id)
  339. JOIN chart c ON (ac.chart_id = c.id)
  340. JOIN customer ct ON (a.customer_id = ct.id)
  341. LEFT JOIN department d ON (d.id = a.department_id)
  342. WHERE $arwhere
  343. UNION ALL
  344. SELECT a.id, 'ap' AS type, a.invoice, a.invnumber,
  345. ct.name, ac.transdate, ac.source,
  346. ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
  347. a.till, ac.cleared, d.description AS department,
  348. ac.memo
  349. FROM ap a
  350. JOIN acc_trans ac ON (a.id = ac.trans_id)
  351. JOIN chart c ON (ac.chart_id = c.id)
  352. JOIN vendor ct ON (a.vendor_id = ct.id)
  353. LEFT JOIN department d ON (d.id = a.department_id)
  354. WHERE $apwhere
  355. ORDER BY $sortorder|;
  356. my $sth = $dbh->prepare($query);
  357. $sth->execute || $form->dberror($query);
  358. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  359. # gl
  360. if ($ref->{type} eq "gl") {
  361. $ref->{module} = "gl";
  362. }
  363. # ap
  364. if ($ref->{type} eq "ap") {
  365. if ($ref->{invoice}) {
  366. $ref->{module} = "ir";
  367. } else {
  368. $ref->{module} = "ap";
  369. }
  370. }
  371. # ar
  372. if ($ref->{type} eq "ar") {
  373. if ($ref->{invoice}) {
  374. $ref->{module} = ($ref->{till}) ? "ps" : "is";
  375. } else {
  376. $ref->{module} = "ar";
  377. }
  378. }
  379. if ($ref->{amount} < 0) {
  380. $ref->{debit} = $ref->{amount} * -1;
  381. $ref->{credit} = 0;
  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 closedto, revtrans
  398. FROM defaults";
  399. $sth = $dbh->prepare($query);
  400. $sth->execute || $form->dberror($query);
  401. ($form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
  402. $sth->finish;
  403. $query = qq|SELECT g.*, d.description AS department
  404. FROM gl g
  405. LEFT JOIN department d ON (d.id = g.department_id)
  406. WHERE g.id = ?|;
  407. $sth = $dbh->prepare($query);
  408. $sth->execute($form->{id}) || $form->dberror($query);
  409. $ref = $sth->fetchrow_hashref(NAME_lc);
  410. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  411. $sth->finish;
  412. # retrieve individual rows
  413. $query = qq|SELECT ac.*, c.accno, c.description, p.projectnumber
  414. FROM acc_trans ac
  415. JOIN chart c ON (ac.chart_id = c.id)
  416. LEFT JOIN project p ON (p.id = ac.project_id)
  417. WHERE ac.trans_id = ?
  418. ORDER BY accno|;
  419. $sth = $dbh->prepare($query);
  420. $sth->execute($form->{id}) || $form->dberror($query);
  421. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  422. if ($ref->{fx_transaction}) {
  423. $form->{transfer} = 1;
  424. }
  425. push @{ $form->{GL} }, $ref;
  426. }
  427. # get recurring transaction
  428. $form->get_recurring($dbh);
  429. } else {
  430. $query = "SELECT current_date AS transdate, closedto, revtrans
  431. FROM defaults";
  432. $sth = $dbh->prepare($query);
  433. $sth->execute || $form->dberror($query);
  434. ($form->{transdate}, $form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
  435. }
  436. $sth->finish;
  437. # get chart of accounts
  438. $query = qq|SELECT accno,description
  439. FROM chart
  440. WHERE charttype = 'A'
  441. ORDER BY accno|;
  442. $sth = $dbh->prepare($query);
  443. $sth->execute || $form->dberror($query);
  444. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  445. push @{ $form->{all_accno} }, $ref;
  446. }
  447. $sth->finish;
  448. # get departments
  449. $form->all_departments($myconfig, $dbh);
  450. # get projects
  451. $form->all_projects($myconfig, $dbh, $form->{transdate});
  452. $dbh->commit;
  453. }
  454. 1;