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