X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/8e2065873b0e00dde437383bb22c57be508e331f..4e27e828de91b6f1252f3353f06edea72f13a0c1:/SL/IS.pm diff --git a/SL/IS.pm b/SL/IS.pm index b59561656..b1df09770 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -58,6 +58,35 @@ sub invoice_details { ($form->{terms}) = $sth->fetchrow_array; $sth->finish; + my (@project_ids, %projectnumbers); + + push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"}); + + # sort items by partsgroup + for $i (1 .. $form->{rowcount}) { + $partsgroup = ""; + if ($form->{"partsgroup_$i"} && $form->{groupitems}) { + $partsgroup = $form->{"partsgroup_$i"}; + } + push @partsgroup, [$i, $partsgroup]; + push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"}); + } + + if (@project_ids) { + $query = "SELECT id, projectnumber FROM project WHERE id IN (" . + join(", ", map({ "?" } @project_ids)) . ")"; + $sth = $dbh->prepare($query); + $sth->execute(@project_ids) || + $form->dberror($query . " (" . join(", ", @project_ids) . ")"); + while (my $ref = $sth->fetchrow_hashref()) { + $projectnumbers{$ref->{id}} = $ref->{projectnumber}; + } + $sth->finish(); + } + + $form->{"globalprojectnumber"} = + $projectnumbers{$form->{"globalproject_id"}}; + my $tax = 0; my $item; my $i; @@ -91,6 +120,15 @@ sub invoice_details { my $subtotal_header = 0; my $subposition = 0; + my @arrays = + qw(runningnumber number description longdescription qty ship unit bin + deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil + partnotes serialnumber reqdate sellprice listprice netprice + discount p_discount discount_sub nodiscount_sub + linetotal nodiscount_linetotal tax_rate projectnumber); + + my @tax_arrays = + qw(taxbase tax taxdescription taxrate taxnumber); foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { $i = $item->[0]; @@ -99,8 +137,7 @@ sub invoice_details { push(@{ $form->{description} }, qq|$item->[1]|); $sameitem = $item->[1]; - map { push(@{ $form->{$_} }, "") } - qw(runningnumber number serialnumber bin partnotes qty unit deliverydate sellprice listprice netprice discount linetotal); + map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); } $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}); @@ -230,8 +267,8 @@ sub invoice_details { push(@{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2)); + push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}); - @taxaccounts = split / /, $form->{"taxaccounts_$i"}; $taxrate = 0; $taxdiff = 0; @@ -302,8 +339,7 @@ sub invoice_details { while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) { - map { push(@{ $form->{$_} }, "") } - qw(runningnumber number serialnumber unit qty bin sellprice listprice netprice discount linetotal nodiscount_linetotal); + map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--"; push(@{ $form->{description} }, $sameitem); } @@ -314,8 +350,7 @@ sub invoice_details { $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"} ) . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|); - map { push(@{ $form->{$_} }, "") } - qw(number unit qty runningnumber serialnumber bin sellprice listprice netprice discount linetotal nodiscount_linetotal); + map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); } $sth->finish; @@ -408,7 +443,7 @@ sub customer_details { } # get rest for the customer - my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes + my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes, ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail FROM customer ct LEFT JOIN contacts cp on ct.id = cp.cp_cv_id WHERE ct.id = $form->{customer_id} $contact order by cp.cp_id limit 1|; @@ -685,10 +720,6 @@ sub post_invoice { } } - $project_id = 'NULL'; - if ($form->{"projectnumber_$i"}) { - $project_id = $form->{"projectnumber_$i"}; - } $deliverydate = ($form->{"deliverydate_$i"}) ? qq|'$form->{"deliverydate_$i"}'| @@ -708,7 +739,7 @@ sub post_invoice { '$form->{"description_$i"}', '$form->{"longdescription_$i"}', $form->{"qty_$i"}, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, 'f', - '$form->{"unit_$i"}', $deliverydate, (SELECT id from project where projectnumber = '$project_id'), + '$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|, '$form->{"serialnumber_$i"}', '$pricegroup_id', '$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', $baseqty, '$subtotal')|; $dbh->do($query) || $form->dberror($query); @@ -801,6 +832,8 @@ sub post_invoice { $form->{exchangerate}, 0); } + $project_id = conv_i($form->{"globalproject_id"}); + foreach my $trans_id (keys %{ $form->{amount} }) { foreach my $accno (keys %{ $form->{amount}{$trans_id} }) { next unless ($form->{expense_inventory} =~ /$accno/); @@ -810,12 +843,12 @@ sub post_invoice { ) != 0 ) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, taxkey) + transdate, taxkey, project_id) VALUES ($trans_id, (SELECT c.id FROM chart c WHERE c.accno = '$accno'), $form->{amount}{$trans_id}{$accno}, '$form->{invdate}', - (SELECT taxkey_id FROM chart WHERE accno = '$accno'))|; - $dbh->do($query) || $form->dberror($query); + (SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|; + do_query($form, $dbh, $query, $project_id); $form->{amount}{$trans_id}{$accno} = 0; } } @@ -827,12 +860,12 @@ sub post_invoice { ) != 0 ) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, taxkey) + transdate, taxkey, project_id) VALUES ($trans_id, (SELECT id FROM chart WHERE accno = '$accno'), $form->{amount}{$trans_id}{$accno}, '$form->{invdate}', - (SELECT taxkey_id FROM chart WHERE accno = '$accno'))|; - $dbh->do($query) || $form->dberror($query); + (SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|; + do_query($form, $dbh, $query, $project_id); } } } @@ -881,23 +914,23 @@ sub post_invoice { if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate) + transdate, project_id) VALUES ($form->{id}, (SELECT c.id FROM chart c WHERE c.accno = '$form->{AR}'), - $amount, '$form->{"datepaid_$i"}')|; - $dbh->do($query) || $form->dberror($query); + $amount, '$form->{"datepaid_$i"}', ?)|; + do_query($form, $dbh, $query, $project_id); } # record payment $form->{"paid_$i"} *= -1; $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, memo) + source, memo, project_id) VALUES ($form->{id}, (SELECT c.id FROM chart c WHERE c.accno = '$accno'), $form->{"paid_$i"}, '$form->{"datepaid_$i"}', - '$form->{"source_$i"}', '$form->{"memo_$i"}')|; - $dbh->do($query) || $form->dberror($query); + '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|; + do_query($form, $dbh, $query, $project_id); # exchangerate difference $form->{fx}{$accno}{ $form->{"datepaid_$i"} } += @@ -937,12 +970,12 @@ sub post_invoice { ) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, cleared, fx_transaction) + transdate, cleared, fx_transaction, project_id) VALUES ($form->{id}, (SELECT c.id FROM chart c WHERE c.accno = '$accno'), - $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|; - $dbh->do($query) || $form->dberror($query); + $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|; + do_query($form, $dbh, $query, $project_id); } } } @@ -1009,6 +1042,7 @@ Message: $form->{message}\r| if $form->{message}; delivery_vendor_id = $form->{delivery_vendor_id}, employee_id = $form->{employee_id}, storno = '$form->{storno}', + globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|, cp_id = | . conv_i($form->{"cp_id"}, 'NULL') . qq| WHERE id = $form->{id} |; @@ -1074,6 +1108,8 @@ sub post_payment { $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); + my $project_id = conv_i($form->{"globalproject_id"}); + # record payments and offsetting AR for my $i (1 .. $form->{paidaccounts}) { @@ -1108,11 +1144,11 @@ sub post_payment { $dbh->do($query) || $form->dberror($query); $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate) + transdate, project_id) VALUES ($form->{id}, (SELECT c.id FROM chart c WHERE c.accno = '$form->{AR}'), - $amount, '$form->{"datepaid_$i"}')|; - $dbh->do($query) || $form->dberror($query); + $amount, '$form->{"datepaid_$i"}', ?)|; + do_query($form, $dbh, $query, $project_id); # record payment @@ -1123,12 +1159,12 @@ sub post_payment { $dbh->do($query) || $form->dberror($query); $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, memo) + source, memo, project_id) VALUES ($form->{id}, (SELECT c.id FROM chart c WHERE c.accno = '$accno'), $form->{"paid_$i"}, '$form->{"datepaid_$i"}', - '$form->{"source_$i"}', '$form->{"memo_$i"}')|; - $dbh->do($query) || $form->dberror($query); + '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|; + do_query($form, $dbh, $query, $project_id); # gain/loss @@ -1166,12 +1202,12 @@ sub post_payment { WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|; $dbh->do($query) || $form->dberror($query); $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, cleared, fx_transaction) + transdate, cleared, fx_transaction, project_id) VALUES ($form->{id}, (SELECT c.id FROM chart c WHERE c.accno = '$accno'), - $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|; - $dbh->do($query) || $form->dberror($query); + $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|; + do_query($form, $dbh, $query, $project_id); } } } @@ -1484,7 +1520,7 @@ sub retrieve_invoice { # retrieve invoice $query = qq|SELECT a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber, - a.orddate, a.quodate, + a.orddate, a.quodate, a.globalproject_id, a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate, a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id, a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,