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
# Order entry module
26
#
27
#======================================================================
28
 
29
package OE;
30
 
31
 
32
sub transactions {
33
  my ($self, $myconfig, $form) = @_;
34
 
35
  # connect to database
36
  my $dbh = $form->dbconnect($myconfig);
37
 
38
  my $query;
39
 
40
  my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
41
 
42
  my $query = qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate,
43
                 o.amount, ct.name, o.netamount, o.$form->{vc}_id,
44
                 (SELECT $rate FROM exchangerate ex
45
                  WHERE ex.curr = o.curr
46
                  AND ex.transdate = o.transdate) AS exchangerate,
47
                 o.closed
48
                 FROM oe o, $form->{vc} ct
49
                 WHERE o.$form->{vc}_id = ct.id|;
114 finnarne 50
 
51
  my %ordinal = ( 'transdate' => 3,
52
                  'ordnumber' => 2,
53
                  'name' => 6
54
                );
2 finnarne 55
 
114 finnarne 56
  my $sortorder = join ',', $form->sort_columns(qw(transdate ordnumber name));
57
  map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal;
58
  $sortorder = $form->{sort} unless $sortorder;
59
 
60
 
2 finnarne 61
  if ($form->{"$form->{vc}_id"}) {
62
    $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
63
  } else {
64
    if ($form->{$form->{vc}}) {
65
      my $name = $form->like(lc $form->{$form->{vc}});
66
      $query .= " AND lower(name) LIKE '$name'";
67
    }
68
  }
114 finnarne 69
 
70
  if ($form->{ordnumber}) {
71
    my $ordnumber = $form->like(lc $form->{ordnumber});
72
    $query .= " AND lower(ordnumber) LIKE '$ordnumber'";
73
    $form->{open} = $form->{closed} = 1;
74
  }
75
 
2 finnarne 76
  unless ($form->{open} && $form->{closed}) {
77
    $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
78
  }
114 finnarne 79
 
2 finnarne 80
  $query .= " AND transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
81
  $query .= " AND transdate <= '$form->{transdateto}'" if $form->{transdateto};
82
  $query .= " ORDER by $sortorder";
83
 
84
  my $sth = $dbh->prepare($query);
85
  $sth->execute || $form->dberror($query);
86
 
87
  while (my $oe = $sth->fetchrow_hashref(NAME_lc)) {
88
    $oe->{exchangerate} = 1 unless $oe->{exchangerate};
89
    push @{ $form->{OE} }, $oe;
90
  }
91
 
92
  $sth->finish;
93
  $dbh->disconnect;
94
 
95
}
96
 
97
 
98
sub save_order {
99
  my ($self, $myconfig, $form) = @_;
100
 
101
  # connect to database, turn off autocommit
102
  my $dbh = $form->dbconnect_noauto($myconfig);
103
 
104
  my ($query, $sth);
105
  my $exchangerate = 0;
106
 
107
  if ($form->{id}) {
108
 
109
    $query = qq|DELETE FROM orderitems
110
                WHERE trans_id = $form->{id}|;
111
    $dbh->do($query) || $form->dberror($query);
112
 
113
    $query = qq|DELETE FROM shipto
114
                WHERE trans_id = $form->{id}|;
115
    $dbh->do($query) || $form->dberror($query);
116
 
117
  } else {
118
    my $uid = time;
119
    $uid .= $form->{login};
120
 
121
    $query = qq|INSERT INTO oe (ordnumber, employee_id)
122
                VALUES ('$uid', (SELECT id FROM employee
123
                                 WHERE login = '$form->{login}') )|;
124
    $dbh->do($query) || $form->dberror($query);
125
 
126
    $query = qq|SELECT id FROM oe
127
                WHERE ordnumber = '$uid'|;
128
    $sth = $dbh->prepare($query);
129
    $sth->execute || $form->dberror($query);
130
 
131
    ($form->{id}) = $sth->fetchrow_array;
132
    $sth->finish;
133
  }
134
 
135
  map { $form->{$_} =~ s/'/''/g } qw(ordnumber shippingpoint notes message);
136
 
137
  my ($amount, $linetotal, $discount, $project_id, $reqdate);
138
  my ($taxrate, $taxamount, $fxsellprice);
139
  my %taxbase = ();
140
  my %taxaccounts = ();
141
  my ($netamount, $tax) = (0, 0);
142
 
143
  for my $i (1 .. $form->{rowcount}) {
144
 
145
    $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
146
 
147
    if ($form->{"qty_$i"} != 0) {
148
 
114 finnarne 149
      map { $form->{"${_}_$i"} =~ s/'/''/g } qw(partnumber description unit projectnumber);
2 finnarne 150
 
151
      # set values to 0 if nothing entered
152
      $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
153
 
154
      $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
155
      $fxsellprice = $form->{"sellprice_$i"};
156
 
157
      my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
158
      $dec = length $dec;
159
      my $decimalplaces = ($dec > 2) ? $dec : 2;
160
 
161
      $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
162
      $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
163
 
164
      $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
165
      $taxrate = 0;
166
      map { $taxrate += $form->{"${_}_rate"} } split / /, $form->{"taxaccounts_$i"};
167
 
168
      if ($form->{taxincluded}) {
169
        $taxamount = $linetotal * $taxrate / (1 + $taxrate);
170
        $taxbase = $linetotal - $taxamount;
171
        # we are not keeping a natural price, do not round
172
        $form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
173
      } else {
174
        $taxamount = $linetotal * $taxrate;
175
        $taxbase = $linetotal;
176
      }
177
 
178
      if ($taxamount != 0) {
179
        foreach my $item (split / /, $form->{"taxaccounts_$i"}) {
180
          $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
181
          $taxbase{$item} += $taxbase;
182
        }
183
      }
184
 
185
      $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"};
186
 
187
      $project_id = 'NULL';
114 finnarne 188
      if ($form->{"projectnumber_$i"}) {
189
        $project_id = qq|(SELECT id
190
                          FROM project
191
                          WHERE projectnumber = '$form->{"projectnumber_$i"}')|;
2 finnarne 192
      }
193
      $reqdate = ($form->{"reqdate_$i"}) ? qq|'$form->{"reqdate_$i"}'| : "NULL";
194
 
195
      # save detail record in orderitems table
196
      $query = qq|INSERT INTO orderitems
197
                 (trans_id, parts_id, description, qty, sellprice, discount,
198
                  unit, reqdate, project_id) VALUES (
199
                  $form->{id}, $form->{"id_$i"}, '$form->{"description_$i"}',
200
                  $form->{"qty_$i"}, $fxsellprice, $form->{"discount_$i"},
201
                  '$form->{"unit_$i"}', $reqdate, $project_id)|;
202
      $dbh->do($query) || $form->dberror($query);
203
 
204
      $form->{"sellprice_$i"} = $fxsellprice;
205
      $form->{"discount_$i"} *= 100;
206
    }
207
  }
208
 
209
 
210
  # set values which could be empty
211
  map { $form->{$_} *= 1 } qw(vendor_id customer_id taxincluded closed);
212
 
213
  $reqdate = ($form->{reqdate}) ? qq|'$form->{reqdate}'| : "NULL";
214
 
215
  # add up the tax
216
  foreach my $item (sort keys %taxaccounts) {
217
    $taxamount = $form->round_amount($taxaccounts{$item}, 2);
218
    $tax += $taxamount;
219
  }
220
 
221
  $amount = $form->round_amount($netamount + $tax, 2);
222
  $netamount = $form->round_amount($netamount, 2);
223
 
224
  if ($form->{currency} eq $form->{defaultcurrency}) {
225
    $form->{exchangerate} = 1;
226
  } else {
227
    $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
228
  }
229
 
230
  $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate});
231
 
232
  # fill in subject if there is none
233
  $form->{subject} = qq|$form->{label} $form->{ordnumber}| unless $form->{subject};
234
  # if there is a message stuff it into the notes
235
  my $cc = "Cc: $form->{cc}\\r\n" if $form->{cc};
236
  my $bcc = "Bcc: $form->{bcc}\\r\n" if $form->{bcc};
237
  $form->{notes} .= qq|\r
238
\r
239
[email]\r
240
To: $form->{email}\r
241
$cc${bcc}Subject: $form->{subject}\r
242
\r
243
Message: $form->{message}\r| if $form->{message};
244
 
245
  # save OE record
246
  $query = qq|UPDATE oe set
247
              ordnumber = '$form->{ordnumber}',
248
              transdate = '$form->{orddate}',
249
              vendor_id = $form->{vendor_id},
250
              customer_id = $form->{customer_id},
251
              amount = $amount,
252
              netamount = $netamount,
253
              reqdate = $reqdate,
254
              taxincluded = '$form->{taxincluded}',
255
              shippingpoint = '$form->{shippingpoint}',
256
              notes = '$form->{notes}',
257
              curr = '$form->{currency}',
258
              closed = '$form->{closed}'
259
              WHERE id = $form->{id}|;
260
  $dbh->do($query) || $form->dberror($query);
261
 
262
  $form->{ordtotal} = $amount;
263
 
264
  # add shipto
265
  $form->{name} = $form->{$form->{vc}};
266
  $form->{name} =~ s/--$form->{"$form->{vc}_id"}//;
267
  $form->add_shipto($dbh, $form->{id});
268
 
269
  if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
270
    if ($form->{vc} eq 'customer') {
271
      $form->update_exchangerate($dbh, $form->{currency}, $form->{orddate}, $form->{exchangerate}, 0);
272
    }
273
    if ($form->{vc} eq 'vendor') {
274
      $form->update_exchangerate($dbh, $form->{currency}, $form->{orddate}, 0, $form->{exchangerate});
275
    }
276
  }
277
 
278
  my $rc = $dbh->commit;
279
  $dbh->disconnect;
280
 
281
  $rc;
282
 
283
}
284
 
285
 
286
 
287
sub delete_order {
288
  my ($self, $myconfig, $form) = @_;
289
 
290
  # connect to database
291
  my $dbh = $form->dbconnect_noauto($myconfig);
292
 
293
  my $query;
294
 
295
  # can't use $form->delete_exchangerate
296
  if ($form->{currency} ne $form->{defaultcurrency}) {
297
       $query = qq|SELECT transdate FROM acc_trans
298
                   WHERE ar.id = trans_id
299
                   AND ar.curr = '$form->{currency}'
300
                   AND transdate = '$form->{orddate}'
301
           UNION SELECT transdate FROM acc_trans
302
                   WHERE ap.id = trans_id
303
                   AND ap.curr = '$form->{currency}'
304
                   AND transdate = '$form->{orddate}'|;
305
    my $sth = $dbh->prepare($query);
306
    $sth->execute || $form->dberror($query);
307
 
308
    my ($transdate) = $sth->fetchrow_array;
309
    $sth->finish;
310
 
311
    if (!$transdate) {
312
      $query = qq|DELETE FROM exchangerate
313
                  WHERE curr = '$form->{currency}'
314
                  AND transdate = '$form->{orddate}'|;
315
      $dbh->do($query) || $self->dberror($query);
316
    }
317
  }
318
 
319
 
320
  # delete OE record
321
  $query = qq|DELETE FROM oe
322
              WHERE id = $form->{id}|;
323
  $dbh->do($query) || $form->dberror($query);
324
 
325
  # delete individual entries
326
  $query = qq|DELETE FROM orderitems
327
              WHERE trans_id = $form->{id}|;
328
  $dbh->do($query) || $form->dberror($query);
329
 
330
  $query = qq|DELETE FROM shipto
331
              WHERE trans_id = $form->{id}|;
332
  $dbh->do($query) || $form->dberror($query);
333
 
334
  my $rc = $dbh->commit;
335
  $dbh->disconnect;
336
 
337
  $rc;
338
 
339
}
340
 
341
 
342
 
343
sub retrieve_order {
344
  my ($self, $myconfig, $form) = @_;
345
 
346
  # connect to database
347
  my $dbh = $form->dbconnect_noauto($myconfig);
348
 
349
  my $query;
350
 
351
  if ($form->{id}) {
352
    # get default accounts and last order number
353
    $query = qq|SELECT (SELECT c.accno FROM chart c
354
                        WHERE d.inventory_accno_id = c.id) AS inventory_accno,
355
                       (SELECT c.accno FROM chart c
356
                        WHERE d.income_accno_id = c.id) AS income_accno,
357
                       (SELECT c.accno FROM chart c
358
                        WHERE d.expense_accno_id = c.id) AS expense_accno,
359
                       (SELECT c.accno FROM chart c
360
                        WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
361
                       (SELECT c.accno FROM chart c
362
                        WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
363
                d.curr AS currencies
364
                FROM defaults d|;
365
  } else {
366
    my $ordnumber = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber';
367
    $query = qq|SELECT (SELECT c.accno FROM chart c
368
                        WHERE d.inventory_accno_id = c.id) AS inventory_accno,
369
                       (SELECT c.accno FROM chart c
370
                        WHERE d.income_accno_id = c.id) AS income_accno,
371
                       (SELECT c.accno FROM chart c
372
                        WHERE d.expense_accno_id = c.id) AS expense_accno,
373
                       (SELECT c.accno FROM chart c
374
                        WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
375
                       (SELECT c.accno FROM chart c
376
                        WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
377
                $ordnumber AS ordnumber, d.curr AS currencies,
378
                current_date AS orddate, current_date AS reqdate
379
                FROM defaults d|;
380
  }
381
  my $sth = $dbh->prepare($query);
382
  $sth->execute || $form->dberror($query);
383
 
384
  my $ref = $sth->fetchrow_hashref(NAME_lc);
385
  map { $form->{$_} = $ref->{$_} } keys %$ref;
386
  $sth->finish;
387
 
388
  ($form->{currency}) = split /:/, $form->{currencies};
389
 
390
  if ($form->{id}) {
391
 
392
    # retrieve order
393
    $query = qq|SELECT o.ordnumber, o.transdate AS orddate, o.reqdate,
394
                o.taxincluded, o.shippingpoint, o.notes, o.curr AS currency,
395
                (SELECT name FROM employee e
396
                 WHERE e.id = o.employee_id) AS employee,
397
                o.$form->{vc}_id, cv.name AS $form->{vc}, o.amount AS invtotal,
398
                o.closed, o.reqdate
399
                FROM oe o, $form->{vc} cv
400
                WHERE o.$form->{vc}_id = cv.id
401
                AND o.id = $form->{id}|;
402
    $sth = $dbh->prepare($query);
403
    $sth->execute || $form->dberror($query);
404
 
405
    $ref = $sth->fetchrow_hashref(NAME_lc);
406
    map { $form->{$_} = $ref->{$_} } keys %$ref;
407
    $sth->finish;
408
 
409
 
410
    $query = qq|SELECT * FROM shipto
411
                WHERE trans_id = $form->{id}|;
412
    $sth = $dbh->prepare($query);
413
    $sth->execute || $form->dberror($query);
414
 
415
    $ref = $sth->fetchrow_hashref(NAME_lc);
416
    map { $form->{$_} = $ref->{$_} } keys %$ref;
417
    $sth->finish;
418
 
114 finnarne 419
    my %oid = ( 'Pg'            => 'o.oid',
420
                'PgPP'          => 'o.oid',
421
                'Oracle'        => 'o.rowid',
422
                'DB2'           => '1'
423
              );
2 finnarne 424
 
425
    # retrieve individual items
114 finnarne 426
    $query = qq|SELECT c1.accno AS inventory_accno,
427
                       c2.accno AS income_accno,
428
                       c3.accno AS expense_accno,
429
                p.partnumber, p.assembly, o.description, o.qty,
2 finnarne 430
                o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin,
114 finnarne 431
                o.reqdate, o.project_id,
432
                pr.projectnumber,
433
                pg.partsgroup
434
                FROM orderitems o
435
                JOIN parts p ON (o.parts_id = p.id)
436
                LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
437
                LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
438
                LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
439
                LEFT JOIN project pr ON (o.project_id = pr.id)
440
                LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
441
                WHERE trans_id = $form->{id}
442
                ORDER BY $oid{$myconfig->{dbdriver}}|;
2 finnarne 443
    $sth = $dbh->prepare($query);
444
    $sth->execute || $form->dberror($query);
445
 
446
    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
447
 
448
      # get tax rates for part
449
      $query = qq|SELECT c.accno
114 finnarne 450
                  FROM chart c, partstax pt
451
                  WHERE pt.chart_id = c.id
2 finnarne 452
                  AND pt.parts_id = $ref->{id}|;
453
      my $pth = $dbh->prepare($query);
454
      $pth->execute || $form->dberror($query);
455
 
456
      $ref->{taxaccounts} = "";
457
      my $taxrate = 0;
458
 
459
      while (my $ptref = $pth->fetchrow_hashref(NAME_lc)) {
460
        $ref->{taxaccounts} .= "$ptref->{accno} ";
461
        $taxrate += $form->{"$ptref->{accno}_rate"};
462
      }
463
      $pth->finish;
464
      chop $ref->{taxaccounts};
465
 
466
      push @{ $form->{order_details} }, $ref;
467
 
468
    }
469
    $sth->finish;
470
 
471
  } else {
472
 
473
    my $ordnumber = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber';
474
    # up order number by 1
475
    $form->{ordnumber}++;
476
 
477
    # save the new number
478
    $query = qq|UPDATE defaults
479
                SET $ordnumber = '$form->{ordnumber}'|;
480
    $dbh->do($query) || $form->dberror($query);
481
 
482
    $form->get_employee($dbh);
483
 
484
    # get last name used
485
    $form->lastname_used($dbh, $myconfig, $form->{vc}) unless $form->{"$form->{vc}_id"};
486
 
487
  }
488
 
489
  $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{orddate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
490
 
114 finnarne 491
  $form->get_project($dbh);
492
 
2 finnarne 493
  my $rc = $dbh->commit;
494
  $dbh->disconnect;
495
 
496
  $rc;
497
 
498
}
499
 
500
 
501
 
502
sub order_details {
503
  my ($self, $myconfig, $form) = @_;
504
 
505
  # connect to database
506
  my $dbh = $form->dbconnect($myconfig);
507
 
508
  my $tax = 0;
509
  my $item;
510
  my $i;
114 finnarne 511
  my @partsgroup = ();
512
  my $partsgroup;
513
  my %oid = ( 'Pg'      => 'a.oid',
514
              'PgPP'    => 'a.oid',
515
              'Oracle'  => 'a.rowid',
516
              'DB2'     => '1'
517
            );
518
 
519
  # sort items by partsgroup
2 finnarne 520
  for $i (1 .. $form->{rowcount}) {
114 finnarne 521
    $partsgroup = "";
522
    if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
523
      $form->format_string("partsgroup_$i");
524
      $partsgroup = $form->{"partsgroup_$i"};
2 finnarne 525
    }
114 finnarne 526
    push @partsgroup, [ $i, $partsgroup ];
2 finnarne 527
  }
528
 
529
  my $sameitem = "";
114 finnarne 530
  foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
2 finnarne 531
    $i = $item->[0];
532
 
114 finnarne 533
    if ($item->[1] ne $sameitem) {
534
      push(@{ $form->{description} }, qq|$item->[1]|);
535
      $sameitem = $item->[1];
2 finnarne 536
 
114 finnarne 537
      map { push(@{ $form->{$_} }, "") } qw(runningnumber number bin qty unit reqdate projectnumber sellprice listprice netprice discount linetotal);
2 finnarne 538
    }
539
 
540
    $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
541
 
542
    if ($form->{"qty_$i"} != 0) {
543
 
544
      # add number, description and qty to $form->{number}, ....
545
      push(@{ $form->{runningnumber} }, $i);
546
      push(@{ $form->{number} }, qq|$form->{"partnumber_$i"}|);
547
      push(@{ $form->{description} }, qq|$form->{"description_$i"}|);
548
      push(@{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"}));
549
      push(@{ $form->{unit} }, qq|$form->{"unit_$i"}|);
550
      push(@{ $form->{reqdate} }, qq|$form->{"reqdate_$i"}|);
114 finnarne 551
      push(@{ $form->{projectnumber} }, qq|$form->{"projectnumber_$i"}|);
2 finnarne 552
 
553
      push(@{ $form->{sellprice} }, $form->{"sellprice_$i"});
554
 
114 finnarne 555
      push(@{ $form->{listprice} }, $form->{"listprice_$i"});
556
 
2 finnarne 557
      my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
558
      my ($dec) = ($sellprice =~ /\.(\d+)/);
559
      $dec = length $dec;
560
      my $decimalplaces = ($dec > 2) ? $dec : 2;
114 finnarne 561
 
2 finnarne 562
      my $discount = $form->round_amount($sellprice * $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100, $decimalplaces);
563
 
564
      # keep a netprice as well, (sellprice - discount)
565
      $form->{"netprice_$i"} = $sellprice - $discount;
566
 
567
      my $linetotal = $form->round_amount($form->{"qty_$i"} * $form->{"netprice_$i"}, 2);
568
 
569
      push(@{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : " ");
570
 
571
      $discount = ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : " ";
572
      $linetotal = ($linetotal != 0) ? $linetotal : " ";
573
 
574
      push(@{ $form->{discount} }, $discount);
575
 
576
      $form->{ordtotal} += $linetotal;
577
 
578
      push(@{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2));
579
 
580
      my ($taxamount, $taxbase);
581
      my $taxrate = 0;
582
 
583
      map { $taxrate += $form->{"${_}_rate"} } split / /, $form->{"taxaccounts_$i"};
584
 
585
      if ($form->{taxincluded}) {
586
        # calculate tax
587
        $taxamount = $linetotal * $taxrate / (1 + $taxrate);
588
        $taxbase = $linetotal / (1 + $taxrate);
589
      } else {
590
        $taxamount = $linetotal * $taxrate;
591
        $taxbase = $linetotal;
592
      }
593
 
594
 
595
      if ($taxamount != 0) {
596
        foreach my $item (split / /, $form->{"taxaccounts_$i"}) {
597
          $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
598
          $taxbase{$item} += $taxbase;
599
        }
600
      }
601
 
114 finnarne 602
      if ($form->{"assembly_$i"}) {
603
        $sameitem = "";
604
 
2 finnarne 605
        # get parts and push them onto the stack
114 finnarne 606
        my $sortorder = "";
607
        if ($form->{groupitems}) {
608
          $sortorder = qq|ORDER BY pg.partsgroup, $oid{$myconfig->{dbdriver}}|;
609
        } else {
610
          $sortorder = qq|ORDER BY $oid{$myconfig->{dbdriver}}|;
611
        }
612
 
613
        $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty,
614
                    pg.partsgroup
615
                    FROM assembly a
616
                    JOIN parts p ON (a.parts_id = p.id)
617
                    LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
618
                    WHERE a.bom = '1'
619
                    AND a.id = '$form->{"id_$i"}'
620
                    $sortorder|;
2 finnarne 621
        $sth = $dbh->prepare($query);
622
        $sth->execute || $form->dberror($query);
623
 
624
        while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
114 finnarne 625
          if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
626
            map { push(@{ $form->{$_} }, "") } qw(runningnumber number unit bin qty reqdate projectnumber sellprice listprice netprice discount linetotal);
627
            $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
628
            push(@{ $form->{description} }, $sameitem);
629
          }
630
 
2 finnarne 631
          push(@{ $form->{number} }, qq|$ref->{partnumber}|);
632
          push(@{ $form->{description} }, qq|$ref->{description}|);
633
          push(@{ $form->{unit} }, qq|$ref->{unit}|);
114 finnarne 634
          push(@{ $form->{qty} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}));
2 finnarne 635
 
114 finnarne 636
          map { push(@{ $form->{$_} }, "") } qw(runningnumber bin reqdate projectnumber sellprice listprice netprice discount linetotal);
2 finnarne 637
 
638
        }
639
        $sth->finish;
640
      }
641
 
642
    }
643
  }
644
 
645
 
646
  foreach $item (sort keys %taxaccounts) {
647
    if ($form->round_amount($taxaccounts{$item}, 2) != 0) {
648
      push(@{ $form->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
649
 
650
      $taxamount = $form->round_amount($taxaccounts{$item}, 2);
651
      $tax += $taxamount;
652
 
653
      push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
654
      push(@{ $form->{taxdescription} }, $form->{"${item}_description"});
655
      push(@{ $form->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
656
      push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
657
    }
658
  }
659
 
660
 
661
  $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
662
  $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
663
 
664
  # format amounts
665
  $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
666
 
667
  # myconfig variables
668
  map { $form->{$_} = $myconfig->{$_} } (qw(company address tel fax signature businessnumber));
669
  $form->{username} = $myconfig->{name};
670
 
671
  $dbh->disconnect;
672
 
673
}
674
 
675
 
676
1;
677