1 #=====================================================================
2 # SQL-Ledger Accounting
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
11 # This program is free software; you can redistribute it and/or modify
12 # it under the terms of the GNU General Public License as published by
13 # the Free Software Foundation; either version 2 of the License, or
14 # (at your option) any later version.
16 # This program is distributed in the hope that it will be useful,
17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 #======================================================================
25 # General ledger backend code
27 #======================================================================
32 sub delete_transaction {
33 my ($self, $myconfig, $form) = @_;
36 my $dbh = $form->dbconnect_noauto($myconfig);
38 my %audittrail = ( tablename => 'gl',
39 reference => $form->{reference},
40 formname => 'transaction',
44 $form->audittrail($dbh, "", \%audittrail);
46 my $query = qq|DELETE FROM gl WHERE id = $form->{id}|;
47 $dbh->do($query) || $form->dberror($query);
49 $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
50 $dbh->do($query) || $form->dberror($query);
53 my $rc = $dbh->commit;
61 sub post_transaction {
62 my ($self, $myconfig, $form) = @_;
69 # connect to database, turn off AutoCommit
70 my $dbh = $form->dbconnect_noauto($myconfig);
72 # post the transaction
73 # make up a unique handle and store in reference field
74 # then retrieve the record based on the unique handle to get the id
75 # replace the reference field with the actual variable
76 # add records to acc_trans
78 # if there is a $form->{id} replace the old transaction
79 # delete all acc_trans entries and add the new ones
85 # delete individual transactions
86 $query = qq|DELETE FROM acc_trans
87 WHERE trans_id = $form->{id}|;
88 $dbh->do($query) || $form->dberror($query);
92 $uid .= $form->{login};
94 $query = qq|INSERT INTO gl (reference, employee_id)
95 VALUES ('$uid', (SELECT id FROM employee
96 WHERE login = '$form->{login}'))|;
97 $dbh->do($query) || $form->dberror($query);
99 $query = qq|SELECT id FROM gl
100 WHERE reference = '$uid'|;
101 $sth = $dbh->prepare($query);
102 $sth->execute || $form->dberror($query);
104 ($form->{id}) = $sth->fetchrow_array;
108 ($null, $department_id) = split /--/, $form->{department};
111 $query = qq|UPDATE gl SET
112 reference = |.$dbh->quote($form->{reference}).qq|,
113 description = |.$dbh->quote($form->{description}).qq|,
114 notes = |.$dbh->quote($form->{notes}).qq|,
115 transdate = '$form->{transdate}',
116 department_id = $department_id
117 WHERE id = $form->{id}|;
119 $dbh->do($query) || $form->dberror($query);
124 # insert acc_trans transactions
125 for $i (1 .. $form->{rowcount}) {
127 $form->{"debit_$i"} = $form->parse_amount($myconfig, $form->{"debit_$i"});
128 $form->{"credit_$i"} = $form->parse_amount($myconfig, $form->{"credit_$i"});
131 ($accno) = split(/--/, $form->{"accno_$i"});
134 if ($form->{"credit_$i"} != 0) {
135 $amount = $form->{"credit_$i"};
138 if ($form->{"debit_$i"} != 0) {
139 $amount = $form->{"debit_$i"} * -1;
147 ($null, $project_id) = split /--/, $form->{"projectnumber_$i"};
149 $form->{"fx_transaction_$i"} *= 1;
151 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
152 source, project_id, fx_transaction)
154 ($form->{id}, (SELECT id
156 WHERE accno = '$accno'),
157 $amount, '$form->{transdate}', |
158 .$dbh->quote($form->{reference}).qq|,
159 $project_id, '$form->{"fx_transaction_$i"}')|;
161 $dbh->do($query) || $form->dberror($query);
168 my %audittrail = ( tablename => 'gl',
169 reference => $form->{reference},
170 formname => 'transaction',
174 $form->audittrail($dbh, "", \%audittrail);
176 # commit and redirect
177 my $rc = $dbh->commit;
186 sub all_transactions {
187 my ($self, $myconfig, $form) = @_;
189 # connect to database
190 my $dbh = $form->dbconnect($myconfig);
196 my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
198 if ($form->{reference}) {
199 $var = $form->like(lc $form->{reference});
200 $glwhere .= " AND lower(g.reference) LIKE '$var'";
201 $arwhere .= " AND lower(a.invnumber) LIKE '$var'";
202 $apwhere .= " AND lower(a.invnumber) LIKE '$var'";
204 if ($form->{department}) {
205 ($null, $var) = split /--/, $form->{department};
206 $glwhere .= " AND g.department_id = $var";
207 $arwhere .= " AND a.department_id = $var";
208 $apwhere .= " AND a.department_id = $var";
211 if ($form->{source}) {
212 $var = $form->like(lc $form->{source});
213 $glwhere .= " AND lower(ac.source) LIKE '$var'";
214 $arwhere .= " AND lower(ac.source) LIKE '$var'";
215 $apwhere .= " AND lower(ac.source) LIKE '$var'";
218 ($form->{datefrom}, $form->{dateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
220 if ($form->{datefrom}) {
221 $glwhere .= " AND ac.transdate >= '$form->{datefrom}'";
222 $arwhere .= " AND ac.transdate >= '$form->{datefrom}'";
223 $apwhere .= " AND ac.transdate >= '$form->{datefrom}'";
225 if ($form->{dateto}) {
226 $glwhere .= " AND ac.transdate <= '$form->{dateto}'";
227 $arwhere .= " AND ac.transdate <= '$form->{dateto}'";
228 $apwhere .= " AND ac.transdate <= '$form->{dateto}'";
230 if ($form->{amountfrom}) {
231 $glwhere .= " AND abs(ac.amount) >= $form->{amountfrom}";
232 $arwhere .= " AND abs(ac.amount) >= $form->{amountfrom}";
233 $apwhere .= " AND abs(ac.amount) >= $form->{amountfrom}";
235 if ($form->{amountto}) {
236 $glwhere .= " AND abs(ac.amount) <= $form->{amountto}";
237 $arwhere .= " AND abs(ac.amount) <= $form->{amountto}";
238 $apwhere .= " AND abs(ac.amount) <= $form->{amountto}";
240 if ($form->{description}) {
241 $var = $form->like(lc $form->{description});
242 $glwhere .= " AND lower(g.description) LIKE '$var'";
243 $arwhere .= " AND lower(ct.name) LIKE '$var'";
244 $apwhere .= " AND lower(ct.name) LIKE '$var'";
246 if ($form->{notes}) {
247 $var = $form->like(lc $form->{notes});
248 $glwhere .= " AND lower(g.notes) LIKE '$var'";
249 $arwhere .= " AND lower(a.notes) LIKE '$var'";
250 $apwhere .= " AND lower(a.notes) LIKE '$var'";
252 if ($form->{accno}) {
253 $glwhere .= " AND c.accno = '$form->{accno}'";
254 $arwhere .= " AND c.accno = '$form->{accno}'";
255 $apwhere .= " AND c.accno = '$form->{accno}'";
257 if ($form->{gifi_accno}) {
258 $glwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
259 $arwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
260 $apwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
262 if ($form->{category} ne 'X') {
263 $glwhere .= " AND c.category = '$form->{category}'";
264 $arwhere .= " AND c.category = '$form->{category}'";
265 $apwhere .= " AND c.category = '$form->{category}'";
268 if ($form->{accno}) {
269 # get category for account
270 $query = qq|SELECT category, link
272 WHERE accno = '$form->{accno}'|;
273 ($form->{ml}, $form->{link}) = $dbh->selectrow_array($query);
275 if ($form->{datefrom}) {
276 $query = qq|SELECT SUM(ac.amount)
278 JOIN chart c ON (ac.chart_id = c.id)
279 WHERE c.accno = '$form->{accno}'
280 AND ac.transdate < date '$form->{datefrom}'
282 ($form->{balance}) = $dbh->selectrow_array($query);
286 if ($form->{gifi_accno}) {
287 # get category for account
288 $query = qq|SELECT category, link
290 WHERE gifi_accno = '$form->{gifi_accno}'|;
291 ($form->{ml}, $form->{link}) = $dbh->selectrow_array($query);
293 if ($form->{datefrom}) {
294 $query = qq|SELECT SUM(ac.amount)
296 JOIN chart c ON (ac.chart_id = c.id)
297 WHERE c.gifi_accno = '$form->{gifi_accno}'
298 AND ac.transdate < date '$form->{datefrom}'
300 ($form->{balance}) = $dbh->selectrow_array($query);
304 my $false = ($myconfig->{dbdriver} =~ /Pg/) ? FALSE : q|'0'|;
306 my %ordinal = ( id => 1,
313 my @a = (id, transdate, reference, source, description, accno);
314 my $sortorder = $form->sort_order(\@a, \%ordinal);
316 my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,
317 g.description, ac.transdate, ac.source,
318 ac.amount, c.accno, c.gifi_accno, g.notes, c.link,
319 '' AS till, ac.cleared
320 FROM gl g, acc_trans ac, chart c
322 AND ac.chart_id = c.id
323 AND g.id = ac.trans_id
325 SELECT a.id, 'ar' AS type, a.invoice, a.invnumber,
326 ct.name, ac.transdate, ac.source,
327 ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
329 FROM ar a, acc_trans ac, chart c, customer ct
331 AND ac.chart_id = c.id
332 AND a.customer_id = ct.id
333 AND a.id = ac.trans_id
335 SELECT a.id, 'ap' AS type, a.invoice, a.invnumber,
336 ct.name, ac.transdate, ac.source,
337 ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
339 FROM ap a, acc_trans ac, chart c, vendor ct
341 AND ac.chart_id = c.id
342 AND a.vendor_id = ct.id
343 AND a.id = ac.trans_id
344 ORDER BY $sortorder|;
345 my $sth = $dbh->prepare($query);
346 $sth->execute || $form->dberror($query);
349 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
352 if ($ref->{type} eq "gl") {
353 $ref->{module} = "gl";
357 if ($ref->{type} eq "ap") {
358 if ($ref->{invoice}) {
359 $ref->{module} = "ir";
361 $ref->{module} = "ap";
366 if ($ref->{type} eq "ar") {
367 if ($ref->{invoice}) {
368 $ref->{module} = ($ref->{till}) ? "ps" : "is";
370 $ref->{module} = "ar";
374 if ($ref->{amount} < 0) {
375 $ref->{debit} = $ref->{amount} * -1;
378 $ref->{credit} = $ref->{amount};
382 push @{ $form->{GL} }, $ref;
389 if ($form->{accno}) {
390 $query = qq|SELECT description FROM chart WHERE accno = '$form->{accno}'|;
391 $sth = $dbh->prepare($query);
392 $sth->execute || $form->dberror($query);
394 ($form->{account_description}) = $sth->fetchrow_array;
397 if ($form->{gifi_accno}) {
398 $query = qq|SELECT description FROM gifi WHERE accno = '$form->{gifi_accno}'|;
399 $sth = $dbh->prepare($query);
400 $sth->execute || $form->dberror($query);
402 ($form->{gifi_account_description}) = $sth->fetchrow_array;
412 my ($self, $myconfig, $form) = @_;
414 my ($query, $sth, $ref);
416 # connect to database
417 my $dbh = $form->dbconnect($myconfig);
420 $query = "SELECT closedto, revtrans
422 $sth = $dbh->prepare($query);
423 $sth->execute || $form->dberror($query);
425 ($form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
428 $query = qq|SELECT g.*,
429 d.description AS department
431 LEFT JOIN department d ON (d.id = g.department_id)
432 WHERE g.id = $form->{id}|;
433 $sth = $dbh->prepare($query);
434 $sth->execute || $form->dberror($query);
436 $ref = $sth->fetchrow_hashref(NAME_lc);
437 map { $form->{$_} = $ref->{$_} } keys %$ref;
440 # retrieve individual rows
441 $query = qq|SELECT c.accno, c.description, ac.amount, ac.project_id,
442 p.projectnumber, ac.fx_transaction
444 JOIN chart c ON (ac.chart_id = c.id)
445 LEFT JOIN project p ON (p.id = ac.project_id)
446 WHERE ac.trans_id = $form->{id}
448 $sth = $dbh->prepare($query);
449 $sth->execute || $form->dberror($query);
451 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
452 if ($ref->{fx_transaction}) {
453 $form->{transfer} = 1;
455 push @{ $form->{GL} }, $ref;
458 $query = "SELECT current_date AS transdate, closedto, revtrans
460 $sth = $dbh->prepare($query);
461 $sth->execute || $form->dberror($query);
463 ($form->{transdate}, $form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
469 if ($form->{transfer}) {
470 $paid = "AND link LIKE '%_paid%'
471 AND NOT (category = 'I'
476 SELECT accno,description
478 WHERE id IN (SELECT fxgain_accno_id FROM defaults)
479 OR id IN (SELECT fxloss_accno_id FROM defaults)";
482 # get chart of accounts
483 $query = qq|SELECT accno,description
485 WHERE charttype = 'A'
488 $sth = $dbh->prepare($query);
489 $sth->execute || $form->dberror($query);
491 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
492 push @{ $form->{all_accno} }, $ref;
499 ORDER BY projectnumber|;
500 $sth = $dbh->prepare($query);
501 $sth->execute || $form->dberror($query);
503 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
504 push @{ $form->{all_projects} }, $ref;