Subversion Repositories sql-ledger-old

Rev

Rev 114 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
2 finnarne 1
#=====================================================================
2
# SQL-Ledger Accounting
3
# Copyright (C) 2001
4
#
5
#  Author: Dieter Simader
6
#   Email: dsimader@sql-ledger.org
7
#     Web: http://www.sql-ledger.org
8
#
9
#  Contributors:
10
#
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.
15
#
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
#======================================================================
24
#
25
# General ledger backend code
26
#
27
#======================================================================
28
 
29
package GL;
30
 
31
 
32
sub delete_transaction {
33
  my ($self, $myconfig, $form) = @_;
34
 
35
  # connect to database
36
  my $dbh = $form->dbconnect_noauto($myconfig);
37
 
38
  my $query = qq|DELETE FROM gl WHERE id = $form->{id}|;
39
  $dbh->do($query) || $form->dberror($query);
40
 
41
  $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
42
  $dbh->do($query) || $form->dberror($query);
43
 
44
  # commit and redirect
45
  my $rc = $dbh->commit;
46
  $dbh->disconnect;
47
 
48
  $rc;
49
 
50
}
51
 
52
 
53
sub post_transaction {
54
  my ($self, $myconfig, $form) = @_;
55
 
114 finnarne 56
  my ($null, $project_id);
2 finnarne 57
  my $i;
58
 
59
  # connect to database, turn off AutoCommit
60
  my $dbh = $form->dbconnect_noauto($myconfig);
61
 
62
  # post the transaction
63
  # make up a unique handle and store in reference field
64
  # then retrieve the record based on the unique handle to get the id
65
  # replace the reference field with the actual variable
66
  # add records to acc_trans
67
 
68
  # if there is a $form->{id} replace the old transaction
69
  # delete all acc_trans entries and add the new ones
70
 
71
  # escape '
72
  map { $form->{$_} =~ s/'/''/g } qw(reference description);
73
 
74
 
75
  my ($query, $sth);
76
 
77
  if ($form->{id}) {
78
    # delete individual transactions
79
    $query = qq|DELETE FROM acc_trans
80
                WHERE trans_id = $form->{id}|;
81
    $dbh->do($query) || $form->dberror($query);
82
 
83
  } else {
84
    my $uid = time;
85
    $uid .= $form->{login};
86
 
87
    $query = qq|INSERT INTO gl (reference, employee_id)
88
                VALUES ('$uid', (SELECT id FROM employee
89
                                 WHERE login = '$form->{login}'))|;
90
    $dbh->do($query) || $form->dberror($query);
91
 
92
    $query = qq|SELECT id FROM gl
93
                WHERE reference = '$uid'|;
94
    $sth = $dbh->prepare($query);
95
    $sth->execute || $form->dberror($query);
96
 
97
    ($form->{id}) = $sth->fetchrow_array;
98
    $sth->finish;
99
 
100
  }
101
 
102
  $query = qq|UPDATE gl SET
103
              reference = '$form->{reference}',
104
              description = '$form->{description}',
105
              notes = '$form->{notes}',
106
              transdate = '$form->{transdate}'
107
              WHERE id = $form->{id}|;
108
 
109
  $dbh->do($query) || $form->dberror($query);
110
 
114 finnarne 111
  my $amount = 0;
112
  my $posted = 0;
2 finnarne 113
  # insert acc_trans transactions
114
  for $i (1 .. $form->{rowcount}) {
114 finnarne 115
 
116
    $form->{"debit_$i"} = $form->parse_amount($myconfig, $form->{"debit_$i"});
117
    $form->{"credit_$i"} = $form->parse_amount($myconfig, $form->{"credit_$i"});
118
 
2 finnarne 119
    # extract accno
120
    ($accno) = split(/--/, $form->{"accno_$i"});
114 finnarne 121
    $amount = 0;
2 finnarne 122
 
123
    if ($form->{"credit_$i"} != 0) {
124
      $amount = $form->{"credit_$i"};
114 finnarne 125
      $posted = 0;
2 finnarne 126
    }
127
    if ($form->{"debit_$i"} != 0) {
128
      $amount = $form->{"debit_$i"} * -1;
114 finnarne 129
      $posted = 0;
2 finnarne 130
    }
131
 
132
 
114 finnarne 133
    # add the record
134
    if (! $posted) {
135
      $project_id = 'NULL';
136
      if ($form->{"projectnumber_$i"}) {
137
 
138
        $form->{"$projectnumber_$i"} =~ s/'/''/g;
139
        $project_id = qq|(SELECT id
140
                          FROM project
141
                          WHERE projectnumber = '$form->{"projectnumber_$i"}')|;
142
      }
143
 
144
      $form->{"fx_transaction_$i"} *= 1;
2 finnarne 145
      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
114 finnarne 146
                  source, project_id, fx_transaction)
2 finnarne 147
                  VALUES
148
                  ($form->{id}, (SELECT id
114 finnarne 149
                                 FROM chart
2 finnarne 150
                                 WHERE accno = '$accno'),
151
                   $amount, '$form->{transdate}', '$form->{reference}',
114 finnarne 152
                  $project_id, '$form->{"fx_transaction_$i"}')|;
2 finnarne 153
 
154
      $dbh->do($query) || $form->dberror($query);
114 finnarne 155
 
156
      $posted = 1;
2 finnarne 157
    }
158
  }
159
 
160
  # commit and redirect
161
  my $rc = $dbh->commit;
162
  $dbh->disconnect;
163
 
164
  $rc;
165
 
166
}
167
 
168
 
169
 
170
sub all_transactions {
171
  my ($self, $myconfig, $form) = @_;
172
 
173
  # connect to database
174
  my $dbh = $form->dbconnect($myconfig);
175
  my $query;
176
  my $sth;
177
 
178
  my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
179
 
180
  if ($form->{reference}) {
181
    my $source = $form->like(lc $form->{reference});
182
    $glwhere .= " AND lower(g.reference) LIKE '$source'";
183
    $arwhere .= " AND lower(a.invnumber) LIKE '$source'";
184
    $apwhere .= " AND lower(a.invnumber) LIKE '$source'";
185
  }
186
  if ($form->{source}) {
187
    my $source = $form->like(lc $form->{source});
188
    $glwhere .= " AND lower(ac.source) LIKE '$source'";
189
    $arwhere .= " AND lower(ac.source) LIKE '$source'";
190
    $apwhere .= " AND lower(ac.source) LIKE '$source'";
191
  }
192
  if ($form->{datefrom}) {
193
    $glwhere .= " AND ac.transdate >= '$form->{datefrom}'";
194
    $arwhere .= " AND ac.transdate >= '$form->{datefrom}'";
195
    $apwhere .= " AND ac.transdate >= '$form->{datefrom}'";
196
  }
197
  if ($form->{dateto}) {
198
    $glwhere .= " AND ac.transdate <= '$form->{dateto}'";
199
    $arwhere .= " AND ac.transdate <= '$form->{dateto}'";
200
    $apwhere .= " AND ac.transdate <= '$form->{dateto}'";
201
  }
114 finnarne 202
  if ($form->{amountfrom}) {
203
    $glwhere .= " AND abs(ac.amount) >= '$form->{amountfrom}'";
204
    $arwhere .= " AND abs(ac.amount) >= '$form->{amountfrom}'";
205
    $apwhere .= " AND abs(ac.amount) >= '$form->{amountfrom}'";
206
  }
207
  if ($form->{amountto}) {
208
    $glwhere .= " AND abs(ac.amount) <= '$form->{amountto}'";
209
    $arwhere .= " AND abs(ac.amount) <= '$form->{amountto}'";
210
    $apwhere .= " AND abs(ac.amount) <= '$form->{amountto}'";
211
  }
2 finnarne 212
  if ($form->{description}) {
213
    my $description = $form->like(lc $form->{description});
214
    $glwhere .= " AND lower(g.description) LIKE '$description'";
215
    $arwhere .= " AND lower(ct.name) LIKE '$description'";
216
    $apwhere .= " AND lower(ct.name) LIKE '$description'";
217
  }
218
  if ($form->{notes}) {
219
    my $notes = $form->like(lc $form->{notes});
220
    $glwhere .= " AND lower(g.notes) LIKE '$notes'";
221
    $arwhere .= " AND lower(a.notes) LIKE '$notes'";
222
    $apwhere .= " AND lower(a.notes) LIKE '$notes'";
223
  }
224
  if ($form->{accno}) {
225
    $glwhere .= " AND c.accno = '$form->{accno}'";
226
    $arwhere .= " AND c.accno = '$form->{accno}'";
227
    $apwhere .= " AND c.accno = '$form->{accno}'";
228
  }
229
  if ($form->{gifi_accno}) {
230
    $glwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
231
    $arwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
232
    $apwhere .= " AND c.gifi_accno = '$form->{gifi_accno}'";
233
  }
234
  if ($form->{category} ne 'X') {
235
    $glwhere .= " AND c.category = '$form->{category}'";
236
    $arwhere .= " AND c.category = '$form->{category}'";
237
    $apwhere .= " AND c.category = '$form->{category}'";
238
  }
239
 
240
  if ($form->{accno}) {
241
    # get category for account
114 finnarne 242
    $query = qq|SELECT category, link
2 finnarne 243
                FROM chart
244
                WHERE accno = '$form->{accno}'|;
245
    $sth = $dbh->prepare($query);
246
 
247
    $sth->execute || $form->dberror($query);
114 finnarne 248
    ($form->{ml}, $form->{link}) = $sth->fetchrow_array;
2 finnarne 249
    $sth->finish;
250
 
251
    if ($form->{datefrom}) {
252
      $query = qq|SELECT SUM(ac.amount)
253
                  FROM acc_trans ac, chart c
254
                  WHERE ac.chart_id = c.id
255
                  AND c.accno = '$form->{accno}'
256
                  AND ac.transdate < date '$form->{datefrom}'
257
                  |;
258
      $sth = $dbh->prepare($query);
259
      $sth->execute || $form->dberror($query);
260
 
261
      ($form->{balance}) = $sth->fetchrow_array;
262
      $sth->finish;
263
    }
264
  }
265
 
266
  if ($form->{gifi_accno}) {
267
    # get category for account
114 finnarne 268
    $query = qq|SELECT category, link
2 finnarne 269
                FROM chart
270
                WHERE gifi_accno = '$form->{gifi_accno}'|;
271
    $sth = $dbh->prepare($query);
272
 
273
    $sth->execute || $form->dberror($query);
114 finnarne 274
    ($form->{ml}, $form->{link}) = $sth->fetchrow_array;
2 finnarne 275
    $sth->finish;
276
 
277
    if ($form->{datefrom}) {
278
      $query = qq|SELECT SUM(ac.amount)
279
                  FROM acc_trans ac, chart c
280
                  WHERE ac.chart_id = c.id
281
                  AND c.gifi_accno = '$form->{gifi_accno}'
282
                  AND ac.transdate < date '$form->{datefrom}'
283
                  |;
284
      $sth = $dbh->prepare($query);
285
      $sth->execute || $form->dberror($query);
286
 
287
      ($form->{balance}) = $sth->fetchrow_array;
288
      $sth->finish;
289
    }
290
  }
291
 
114 finnarne 292
  my $false = ($myconfig->{dbdriver} =~ /Pg/) ? FALSE : q|'0'|;
2 finnarne 293
 
114 finnarne 294
  my %ordinal = ( id => 1,
295
                  transdate => 6,
2 finnarne 296
                  reference => 4,
297
                  source => 7,
114 finnarne 298
                  accno => 9,
2 finnarne 299
                  description => 5 );
114 finnarne 300
 
301
  my $sortorder = join ',', $form->sort_columns(qw(transdate reference source description accno id));
2 finnarne 302
  map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal;
303
 
304
  my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,
305
                 g.description, ac.transdate, ac.source,
114 finnarne 306
                 ac.amount, c.accno, c.gifi_accno, g.notes,
307
                 '' AS till, ac.cleared
2 finnarne 308
                 FROM gl g, acc_trans ac, chart c
309
                 WHERE $glwhere
310
                 AND ac.chart_id = c.id
311
                 AND g.id = ac.trans_id
312
        UNION ALL
313
                 SELECT a.id, 'ar' AS type, a.invoice, a.invnumber,
314
                 ct.name, ac.transdate, ac.source,
114 finnarne 315
                 ac.amount, c.accno, c.gifi_accno, a.notes,
316
                 a.till, ac.cleared
2 finnarne 317
                 FROM ar a, acc_trans ac, chart c, customer ct
318
                 WHERE $arwhere
319
                 AND ac.chart_id = c.id
320
                 AND a.customer_id = ct.id
321
                 AND a.id = ac.trans_id
322
        UNION ALL
323
                 SELECT a.id, 'ap' AS type, a.invoice, a.invnumber,
324
                 ct.name, ac.transdate, ac.source,
114 finnarne 325
                 ac.amount, c.accno, c.gifi_accno, a.notes,
326
                 a.till, ac.cleared
2 finnarne 327
                 FROM ap a, acc_trans ac, chart c, vendor ct
328
                 WHERE $apwhere
329
                 AND ac.chart_id = c.id
330
                 AND a.vendor_id = ct.id
331
                 AND a.id = ac.trans_id
332
                 ORDER BY $sortorder|;
333
  my $sth = $dbh->prepare($query);
334
  $sth->execute || $form->dberror($query);
335
 
336
  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
337
 
338
    # gl
339
    if ($ref->{type} eq "gl") {
340
      $ref->{module} = "gl";
341
    }
342
 
343
    # ap
344
    if ($ref->{type} eq "ap") {
345
      if ($ref->{invoice}) {
346
        $ref->{module} = "ir";
347
      } else {
348
        $ref->{module} = "ap";
349
      }
350
    }
351
 
352
    # ar
353
    if ($ref->{type} eq "ar") {
354
      if ($ref->{invoice}) {
114 finnarne 355
        $ref->{module} = ($ref->{till}) ? "ps" : "is";
2 finnarne 356
      } else {
357
        $ref->{module} = "ar";
358
      }
359
    }
360
 
361
    if ($ref->{amount} < 0) {
362
      $ref->{debit} = $ref->{amount} * -1;
363
      $ref->{credit} = 0;
364
    } else {
365
      $ref->{credit} = $ref->{amount};
366
      $ref->{debit} = 0;
367
    }
368
 
369
    push @{ $form->{GL} }, $ref;
370
 
371
  }
372
 
373
  $sth->finish;
374
 
375
  if ($form->{accno}) {
376
    $query = qq|SELECT description FROM chart WHERE accno = '$form->{accno}'|;
377
    $sth = $dbh->prepare($query);
378
    $sth->execute || $form->dberror($query);
379
 
380
    ($form->{account_description}) = $sth->fetchrow_array;
381
    $sth->finish;
382
  }
383
  if ($form->{gifi_accno}) {
384
    $query = qq|SELECT description FROM gifi WHERE accno = '$form->{gifi_accno}'|;
385
    $sth = $dbh->prepare($query);
386
    $sth->execute || $form->dberror($query);
387
 
388
    ($form->{gifi_account_description}) = $sth->fetchrow_array;
389
    $sth->finish;
390
  }
391
 
392
  $dbh->disconnect;
393
 
394
}
395
 
396
 
397
sub transaction {
398
  my ($self, $myconfig, $form) = @_;
114 finnarne 399
 
400
  my ($query, $sth, $ref);
2 finnarne 401
 
402
  # connect to database
403
  my $dbh = $form->dbconnect($myconfig);
404
 
405
  if ($form->{id}) {
406
    $query = "SELECT closedto, revtrans
407
              FROM defaults";
408
    $sth = $dbh->prepare($query);
409
    $sth->execute || $form->dberror($query);
410
 
411
    ($form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
412
    $sth->finish;
413
 
414
    $query = "SELECT reference, description, notes, transdate
415
              FROM gl
416
              WHERE id = $form->{id}";
417
    $sth = $dbh->prepare($query);
418
    $sth->execute || $form->dberror($query);
419
 
420
    ($form->{reference}, $form->{description}, $form->{notes}, $form->{transdate}) = $sth->fetchrow_array;
421
    $sth->finish;
114 finnarne 422
 
2 finnarne 423
    # retrieve individual rows
114 finnarne 424
    $query = qq|SELECT c.accno, c.description, ac.amount, ac.project_id,
425
                p.projectnumber, ac.fx_transaction
426
                FROM acc_trans ac
427
                JOIN chart c ON (ac.chart_id = c.id)
428
                LEFT JOIN project p ON (ac.project_id = p.id)
429
                WHERE ac.trans_id = $form->{id}
430
                ORDER BY accno|;
2 finnarne 431
    $sth = $dbh->prepare($query);
432
    $sth->execute || $form->dberror($query);
114 finnarne 433
 
434
    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
435
      if ($ref->{fx_transaction}) {
436
        $form->{transfer} = 1;
437
      }
2 finnarne 438
      push @{ $form->{GL} }, $ref;
439
    }
440
  } else {
441
    $query = "SELECT current_date AS transdate, closedto, revtrans
442
              FROM defaults";
443
    $sth = $dbh->prepare($query);
444
    $sth->execute || $form->dberror($query);
445
 
446
    ($form->{transdate}, $form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
447
  }
448
 
449
  $sth->finish;
450
 
114 finnarne 451
  my $paid;
452
  if ($form->{transfer}) {
453
    $paid = "AND link LIKE '%_paid%'
454
             AND NOT (category = 'I'
455
                  OR category = 'E')";
456
  }
457
 
2 finnarne 458
  # get chart of accounts
459
  $query = qq|SELECT accno,description
460
              FROM chart
461
              WHERE charttype = 'A'
114 finnarne 462
              $paid
2 finnarne 463
              ORDER by accno|;
464
  $sth = $dbh->prepare($query);
465
  $sth->execute || $form->dberror($query);
114 finnarne 466
 
467
  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
468
    push @{ $form->{all_accno} }, $ref;
2 finnarne 469
  }
470
  $sth->finish;
114 finnarne 471
 
472
  # get projects
473
  $form->get_project($dbh);
2 finnarne 474
 
475
  $dbh->disconnect;
476
 
477
}
478
 
479
 
480
1;
481