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 #======================================================================
27 #======================================================================
33 my ($self, $myconfig, $form) = @_;
36 my $dbh = $form->dbconnect($myconfig);
40 my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
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,
48 FROM oe o, $form->{vc} ct
49 WHERE o.$form->{vc}_id = ct.id|;
51 my $ordnumber = $form->like(lc $form->{ordnumber});
53 if ($form->{"$form->{vc}_id"}) {
54 $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
56 if ($form->{$form->{vc}}) {
57 my $name = $form->like(lc $form->{$form->{vc}});
58 $query .= " AND lower(name) LIKE '$name'";
61 unless ($form->{open} && $form->{closed}) {
62 $query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";
65 my $sortorder = join ', ', $form->sort_columns(qw(transdate ordnumber name));
66 $sortorder = $form->{sort} unless $sortorder;
68 $query .= " AND lower(ordnumber) LIKE '$ordnumber'" if $form->{ordnumber};
69 $query .= " AND transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
70 $query .= " AND transdate <= '$form->{transdateto}'" if $form->{transdateto};
71 $query .= " ORDER by $sortorder";
73 my $sth = $dbh->prepare($query);
74 $sth->execute || $form->dberror($query);
76 while (my $oe = $sth->fetchrow_hashref(NAME_lc)) {
77 $oe->{exchangerate} = 1 unless $oe->{exchangerate};
78 push @{ $form->{OE} }, $oe;
88 my ($self, $myconfig, $form) = @_;
90 # connect to database, turn off autocommit
91 my $dbh = $form->dbconnect_noauto($myconfig);
98 $query = qq|DELETE FROM orderitems
99 WHERE trans_id = $form->{id}|;
100 $dbh->do($query) || $form->dberror($query);
102 $query = qq|DELETE FROM shipto
103 WHERE trans_id = $form->{id}|;
104 $dbh->do($query) || $form->dberror($query);
108 $uid .= $form->{login};
110 $query = qq|INSERT INTO oe (ordnumber, employee_id)
111 VALUES ('$uid', (SELECT id FROM employee
112 WHERE login = '$form->{login}') )|;
113 $dbh->do($query) || $form->dberror($query);
115 $query = qq|SELECT id FROM oe
116 WHERE ordnumber = '$uid'|;
117 $sth = $dbh->prepare($query);
118 $sth->execute || $form->dberror($query);
120 ($form->{id}) = $sth->fetchrow_array;
124 map { $form->{$_} =~ s/'/''/g } qw(ordnumber shippingpoint notes message);
126 my ($amount, $linetotal, $discount, $project_id, $reqdate);
127 my ($taxrate, $taxamount, $fxsellprice);
129 my %taxaccounts = ();
130 my ($netamount, $tax) = (0, 0);
132 for my $i (1 .. $form->{rowcount}) {
134 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
136 if ($form->{"qty_$i"} != 0) {
138 map { $form->{"${_}_$i"} =~ s/'/''/g } qw(partnumber description unit);
140 # set values to 0 if nothing entered
141 $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
143 $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
144 $fxsellprice = $form->{"sellprice_$i"};
146 my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
148 my $decimalplaces = ($dec > 2) ? $dec : 2;
150 $discount = $form->round_amount($form->{"sellprice_$i"} * $form->{"discount_$i"}, $decimalplaces);
151 $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} - $discount, $decimalplaces);
153 $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
155 map { $taxrate += $form->{"${_}_rate"} } split / /, $form->{"taxaccounts_$i"};
157 if ($form->{taxincluded}) {
158 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
159 $taxbase = $linetotal - $taxamount;
160 # we are not keeping a natural price, do not round
161 $form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
163 $taxamount = $linetotal * $taxrate;
164 $taxbase = $linetotal;
167 if ($taxamount != 0) {
168 foreach my $item (split / /, $form->{"taxaccounts_$i"}) {
169 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
170 $taxbase{$item} += $taxbase;
174 $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"};
176 $project_id = 'NULL';
177 if ($form->{"project_id_$i"}) {
178 $project_id = $form->{"project_id_$i"};
180 $reqdate = ($form->{"reqdate_$i"}) ? qq|'$form->{"reqdate_$i"}'| : "NULL";
182 # save detail record in orderitems table
183 $query = qq|INSERT INTO orderitems
184 (trans_id, parts_id, description, qty, sellprice, discount,
185 unit, reqdate, project_id) VALUES (
186 $form->{id}, $form->{"id_$i"}, '$form->{"description_$i"}',
187 $form->{"qty_$i"}, $fxsellprice, $form->{"discount_$i"},
188 '$form->{"unit_$i"}', $reqdate, $project_id)|;
189 $dbh->do($query) || $form->dberror($query);
191 $form->{"sellprice_$i"} = $fxsellprice;
192 $form->{"discount_$i"} *= 100;
197 # set values which could be empty
198 map { $form->{$_} *= 1 } qw(vendor_id customer_id taxincluded closed);
200 $reqdate = ($form->{reqdate}) ? qq|'$form->{reqdate}'| : "NULL";
203 foreach my $item (sort keys %taxaccounts) {
204 $taxamount = $form->round_amount($taxaccounts{$item}, 2);
208 $amount = $form->round_amount($netamount + $tax, 2);
209 $netamount = $form->round_amount($netamount, 2);
211 if ($form->{currency} eq $form->{defaultcurrency}) {
212 $form->{exchangerate} = 1;
214 $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell');
217 $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate});
219 # fill in subject if there is none
220 $form->{subject} = qq|$form->{label} $form->{ordnumber}| unless $form->{subject};
221 # if there is a message stuff it into the notes
222 my $cc = "Cc: $form->{cc}\\r\n" if $form->{cc};
223 my $bcc = "Bcc: $form->{bcc}\\r\n" if $form->{bcc};
224 $form->{notes} .= qq|\r
228 $cc${bcc}Subject: $form->{subject}\r
230 Message: $form->{message}\r| if $form->{message};
233 $query = qq|UPDATE oe set
234 ordnumber = '$form->{ordnumber}',
235 transdate = '$form->{orddate}',
236 vendor_id = $form->{vendor_id},
237 customer_id = $form->{customer_id},
239 netamount = $netamount,
241 taxincluded = '$form->{taxincluded}',
242 shippingpoint = '$form->{shippingpoint}',
243 notes = '$form->{notes}',
244 curr = '$form->{currency}',
245 closed = '$form->{closed}'
246 WHERE id = $form->{id}|;
247 $dbh->do($query) || $form->dberror($query);
249 $form->{ordtotal} = $amount;
252 $form->{name} = $form->{$form->{vc}};
253 $form->{name} =~ s/--$form->{"$form->{vc}_id"}//;
254 $form->add_shipto($dbh, $form->{id});
256 if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
257 if ($form->{vc} eq 'customer') {
258 $form->update_exchangerate($dbh, $form->{currency}, $form->{orddate}, $form->{exchangerate}, 0);
260 if ($form->{vc} eq 'vendor') {
261 $form->update_exchangerate($dbh, $form->{currency}, $form->{orddate}, 0, $form->{exchangerate});
265 my $rc = $dbh->commit;
275 my ($self, $myconfig, $form) = @_;
277 # connect to database
278 my $dbh = $form->dbconnect_noauto($myconfig);
282 # can't use $form->delete_exchangerate
283 if ($form->{currency} ne $form->{defaultcurrency}) {
284 $query = qq|SELECT transdate FROM acc_trans
285 WHERE ar.id = trans_id
286 AND ar.curr = '$form->{currency}'
287 AND transdate = '$form->{orddate}'
288 UNION SELECT transdate FROM acc_trans
289 WHERE ap.id = trans_id
290 AND ap.curr = '$form->{currency}'
291 AND transdate = '$form->{orddate}'|;
292 my $sth = $dbh->prepare($query);
293 $sth->execute || $form->dberror($query);
295 my ($transdate) = $sth->fetchrow_array;
299 $query = qq|DELETE FROM exchangerate
300 WHERE curr = '$form->{currency}'
301 AND transdate = '$form->{orddate}'|;
302 $dbh->do($query) || $self->dberror($query);
308 $query = qq|DELETE FROM oe
309 WHERE id = $form->{id}|;
310 $dbh->do($query) || $form->dberror($query);
312 # delete individual entries
313 $query = qq|DELETE FROM orderitems
314 WHERE trans_id = $form->{id}|;
315 $dbh->do($query) || $form->dberror($query);
317 $query = qq|DELETE FROM shipto
318 WHERE trans_id = $form->{id}|;
319 $dbh->do($query) || $form->dberror($query);
321 my $rc = $dbh->commit;
331 my ($self, $myconfig, $form) = @_;
333 # connect to database
334 my $dbh = $form->dbconnect_noauto($myconfig);
339 # get default accounts and last order number
340 $query = qq|SELECT (SELECT c.accno FROM chart c
341 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
342 (SELECT c.accno FROM chart c
343 WHERE d.income_accno_id = c.id) AS income_accno,
344 (SELECT c.accno FROM chart c
345 WHERE d.expense_accno_id = c.id) AS expense_accno,
346 (SELECT c.accno FROM chart c
347 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
348 (SELECT c.accno FROM chart c
349 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
353 my $ordnumber = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber';
354 $query = qq|SELECT (SELECT c.accno FROM chart c
355 WHERE d.inventory_accno_id = c.id) AS inventory_accno,
356 (SELECT c.accno FROM chart c
357 WHERE d.income_accno_id = c.id) AS income_accno,
358 (SELECT c.accno FROM chart c
359 WHERE d.expense_accno_id = c.id) AS expense_accno,
360 (SELECT c.accno FROM chart c
361 WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
362 (SELECT c.accno FROM chart c
363 WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
364 $ordnumber AS ordnumber, d.curr AS currencies,
365 current_date AS orddate, current_date AS reqdate
368 my $sth = $dbh->prepare($query);
369 $sth->execute || $form->dberror($query);
371 my $ref = $sth->fetchrow_hashref(NAME_lc);
372 map { $form->{$_} = $ref->{$_} } keys %$ref;
375 ($form->{currency}) = split /:/, $form->{currencies};
380 $query = qq|SELECT o.ordnumber, o.transdate AS orddate, o.reqdate,
381 o.taxincluded, o.shippingpoint, o.notes, o.curr AS currency,
382 (SELECT name FROM employee e
383 WHERE e.id = o.employee_id) AS employee,
384 o.$form->{vc}_id, cv.name AS $form->{vc}, o.amount AS invtotal,
386 FROM oe o, $form->{vc} cv
387 WHERE o.$form->{vc}_id = cv.id
388 AND o.id = $form->{id}|;
389 $sth = $dbh->prepare($query);
390 $sth->execute || $form->dberror($query);
392 $ref = $sth->fetchrow_hashref(NAME_lc);
393 map { $form->{$_} = $ref->{$_} } keys %$ref;
397 $query = qq|SELECT * FROM shipto
398 WHERE trans_id = $form->{id}|;
399 $sth = $dbh->prepare($query);
400 $sth->execute || $form->dberror($query);
402 $ref = $sth->fetchrow_hashref(NAME_lc);
403 map { $form->{$_} = $ref->{$_} } keys %$ref;
406 my %oid = ( 'Pg' => 'oid',
410 # retrieve individual items
411 $query = qq|SELECT c1.accno AS inventory_accno,
412 c2.accno AS income_accno,
413 c3.accno AS expense_accno,
414 p.partnumber, p.assembly, o.description, o.qty,
415 o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin,
416 o.reqdate, o.project_id,
420 JOIN parts p ON (o.parts_id = p.id)
421 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
422 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
423 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
424 LEFT JOIN project pr ON (o.project_id = pr.id)
425 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
426 WHERE trans_id = $form->{id}
427 ORDER BY o.$oid{$myconfig->{dbdriver}}|;
428 $sth = $dbh->prepare($query);
429 $sth->execute || $form->dberror($query);
431 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
433 # get tax rates for part
434 $query = qq|SELECT c.accno
435 FROM chart c, partstax pt
436 WHERE pt.chart_id = c.id
437 AND pt.parts_id = $ref->{id}|;
438 my $pth = $dbh->prepare($query);
439 $pth->execute || $form->dberror($query);
441 $ref->{taxaccounts} = "";
444 while (my $ptref = $pth->fetchrow_hashref(NAME_lc)) {
445 $ref->{taxaccounts} .= "$ptref->{accno} ";
446 $taxrate += $form->{"$ptref->{accno}_rate"};
449 chop $ref->{taxaccounts};
451 push @{ $form->{order_details} }, $ref;
458 my $ordnumber = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber';
459 # up order number by 1
460 $form->{ordnumber}++;
462 # save the new number
463 $query = qq|UPDATE defaults
464 SET $ordnumber = '$form->{ordnumber}'|;
465 $dbh->do($query) || $form->dberror($query);
467 $form->get_employee($dbh);
470 $form->lastname_used($dbh, $myconfig, $form->{vc}) unless $form->{"$form->{vc}_id"};
474 $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{orddate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
476 my $rc = $dbh->commit;
486 my ($self, $myconfig, $form) = @_;
488 # connect to database
489 my $dbh = $form->dbconnect($myconfig);
496 my %oid = ( 'Pg' => 'oid',
497 'Oracle' => 'rowid' );
499 # sort items by partsgroup
500 for $i (1 .. $form->{rowcount}) {
502 if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
503 $form->format_string("partsgroup_$i");
504 $partsgroup = $form->{"partsgroup_$i"};
506 push @partsgroup, [ $i, $partsgroup ];
510 foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
513 if ($item->[1] ne $sameitem) {
514 push(@{ $form->{description} }, qq|$item->[1]|);
515 $sameitem = $item->[1];
517 map { push(@{ $form->{$_} }, "") } qw(runningnumber number bin qty unit reqdate sellprice listprice netprice discount linetotal);
520 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
522 if ($form->{"qty_$i"} != 0) {
524 # add number, description and qty to $form->{number}, ....
525 push(@{ $form->{runningnumber} }, $i);
526 push(@{ $form->{number} }, qq|$form->{"partnumber_$i"}|);
527 push(@{ $form->{description} }, qq|$form->{"description_$i"}|);
528 push(@{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"}));
529 push(@{ $form->{unit} }, qq|$form->{"unit_$i"}|);
530 push(@{ $form->{reqdate} }, qq|$form->{"reqdate_$i"}|);
532 push(@{ $form->{sellprice} }, $form->{"sellprice_$i"});
534 push(@{ $form->{listprice} }, $form->{"listprice_$i"});
536 my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
537 my ($dec) = ($sellprice =~ /\.(\d+)/);
539 my $decimalplaces = ($dec > 2) ? $dec : 2;
541 my $discount = $form->round_amount($sellprice * $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100, $decimalplaces);
543 # keep a netprice as well, (sellprice - discount)
544 $form->{"netprice_$i"} = $sellprice - $discount;
546 my $linetotal = $form->round_amount($form->{"qty_$i"} * $form->{"netprice_$i"}, 2);
548 push(@{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : " ");
550 $discount = ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : " ";
551 $linetotal = ($linetotal != 0) ? $linetotal : " ";
553 push(@{ $form->{discount} }, $discount);
555 $form->{ordtotal} += $linetotal;
557 push(@{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2));
559 my ($taxamount, $taxbase);
562 map { $taxrate += $form->{"${_}_rate"} } split / /, $form->{"taxaccounts_$i"};
564 if ($form->{taxincluded}) {
566 $taxamount = $linetotal * $taxrate / (1 + $taxrate);
567 $taxbase = $linetotal / (1 + $taxrate);
569 $taxamount = $linetotal * $taxrate;
570 $taxbase = $linetotal;
574 if ($taxamount != 0) {
575 foreach my $item (split / /, $form->{"taxaccounts_$i"}) {
576 $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
577 $taxbase{$item} += $taxbase;
581 if ($form->{"assembly_$i"}) {
584 # get parts and push them onto the stack
586 if ($form->{groupitems}) {
587 $sortorder = qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
589 $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
592 $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty,
595 JOIN parts p ON (a.parts_id = p.id)
596 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
598 AND a.id = '$form->{"id_$i"}'
600 $sth = $dbh->prepare($query);
601 $sth->execute || $form->dberror($query);
603 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
604 if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
605 map { push(@{ $form->{$_} }, "") } qw(runningnumber number unit bin qty sellprice listprice netprice discount linetotal);
606 $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
607 push(@{ $form->{description} }, $sameitem);
610 push(@{ $form->{number} }, qq|$ref->{partnumber}|);
611 push(@{ $form->{description} }, qq|$ref->{description}|);
612 push(@{ $form->{unit} }, qq|$ref->{unit}|);
613 push(@{ $form->{qty} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}));
615 map { push(@{ $form->{$_} }, "") } qw(runningnumber bin sellprice listprice netprice discount linetotal);
625 foreach $item (sort keys %taxaccounts) {
626 if ($form->round_amount($taxaccounts{$item}, 2) != 0) {
627 push(@{ $form->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
629 $taxamount = $form->round_amount($taxaccounts{$item}, 2);
632 push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
633 push(@{ $form->{taxdescription} }, $form->{"${item}_description"});
634 push(@{ $form->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
635 push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
640 $form->{subtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
641 $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax;
644 $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2);
647 map { $form->{$_} = $myconfig->{$_} } (qw(company address tel fax signature businessnumber));
648 $form->{username} = $myconfig->{name};
655 sub project_description {
656 my ($self, $dbh, $id) = @_;
658 my $query = qq|SELECT description
661 my $sth = $dbh->prepare($query);
662 $sth->execute || $form->dberror($query);
664 ($_) = $sth->fetchrow_array;