X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIR.pm;h=78970e3d29c5fb3acfdfeddf820ae1c11a19bd20;hb=1c603341fc02e3a5a7b5126cd7df6478d2e34700;hp=30f49fd7c6f7bb47da723aff694a6fbbaf7a85b0;hpb=83914eeb2e95cdf587565952eef54be59dd58693;p=kivitendo-erp.git diff --git a/SL/IR.pm b/SL/IR.pm index 30f49fd7c..78970e3d2 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -35,8 +35,12 @@ package IR; use SL::AM; +use SL::ARAP; use SL::Common; +use SL::CVar; use SL::DBUtils; +use SL::DO; +use SL::GenericTranslations; use SL::MoreCommon; use List::Util qw(min); @@ -49,6 +53,9 @@ sub post_invoice { my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); $form->{defaultcurrency} = $form->get_default_currency($myconfig); + my $ic_cvar_configs = CVar->get_configs(module => 'IC', + dbh => $dbh); + my ($query, $sth, @values, $project_id); my ($allocated, $taxrate, $taxamount, $taxdiff, $item); my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno); @@ -124,7 +131,27 @@ sub post_invoice { map { $taxrate += $form->{"${_}_rate"} } @taxaccounts; $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1; - + ##################################################################### + # das ist aus IS.pm kopiert. schlimm. jb 7.10.2009 + # ich würde mir wünschen, dass diese vier stellen zusammengefasst werden + # ... vier stellen = (einkauf + verkauf) * (maske + backend) + # ansonsten stolpert man immer wieder viermal statt einmal heftig + # und auch das undo discount formatting ist nicht besonders wartungsfreundlich + + # keep entered selling price + my $fxsellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"}); + + # keine ahnung wofür das in IS.pm gemacht wird: + # my ($dec) = ($fxsellprice =~ /\.(\d+)/); + # $dec = length $dec; + # my $decimalplaces = ($dec > 2) ? $dec : 2; + + # undo discount formatting + $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100; + # deduct discount + $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"}); + + ###################################################################### if ($form->{"inventory_accno_$i"}) { $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2); @@ -287,18 +314,29 @@ sub post_invoice { next if $payments_only; # save detail record in invoice table + my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|); + $query = - qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty, - sellprice, fxsellprice, allocated, unit, deliverydate, + qq|INSERT INTO invoice (id, trans_id, parts_id, description, qty, base_qty, + sellprice, fxsellprice, discount, allocated, unit, deliverydate, project_id, serialnumber, price_factor_id, price_factor, marge_price_factor) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?)|; - @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?)|; + @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}), $form->{"description_$i"}, $form->{"qty_$i"} * -1, - $baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $allocated, + $baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, $form->{"unit_$i"}, conv_date($form->{deliverydate}), conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"}, conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"})); do_query($form, $dbh, $query, @values); + + CVar->save_custom_variables(module => 'IC', + sub_module => 'invoice', + trans_id => $invoice_id, + configs => $ic_cvar_configs, + variables => $form, + name_prefix => 'ic_', + name_postfix => "_$i", + dbh => $dbh); } $h_item_unit->finish(); @@ -342,7 +380,7 @@ sub post_invoice { $expensediff += $paiddiff; ######## this only applies to tax included - + $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff if $lastinventoryaccno; $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff if $lastexpenseaccno; @@ -363,11 +401,11 @@ sub post_invoice { $form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax; - + $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2) if $form->{paid} != 0; # update exchangerate - + $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate}) if ($form->{currency} ne $defaultcurrency) && !$exchangerate; @@ -396,7 +434,7 @@ sub post_invoice { } # force AP entry if 0 - + $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid} if $form->{amount}{$form->{id}}{$form->{AP}} == 0; # record payments and offsetting AP @@ -498,7 +536,7 @@ sub post_invoice { netamount = ?, paid = ?, duedate = ?, datepaid = ?, invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?, intnotes = ?, curr = ?, storno_id = ?, storno = ?, - cp_id = ?, employee_id = ?, department_id = ?, + cp_id = ?, employee_id = ?, department_id = ?, globalproject_id = ? WHERE id = ?|; @values = ( @@ -507,7 +545,7 @@ sub post_invoice { $netamount, $form->{paid}, conv_date($form->{duedate}), $form->{paid} ? conv_date($form->{datepaid}) : undef, '1', $taxzone_id, $form->{notes}, $form->{taxincluded} ? 't' : 'f', $form->{intnotes}, $form->{currency}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f', - conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), + conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), conv_i($form->{globalproject_id}), conv_i($form->{id}) ); @@ -521,7 +559,7 @@ sub post_invoice { do_query($form, $dbh, $query, conv_i($form->{storno_id})); $query = qq!UPDATE ap SET intnotes = ? || intnotes WHERE id = ?!; - do_query($form, $dbh, $query, 'Rechnung storniert am $form->{invdate} ', conv_i($form->{storno_id})); + do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{storno_id})); $query = qq|UPDATE ap SET paid = amount WHERE id = ?|; do_query($form, $dbh, $query, conv_i($form->{id})); @@ -538,8 +576,36 @@ sub post_invoice { Common::webdav_folder($form) if ($main::webdav); - my $rc = 1; + # Link this record to the records it was created from. + RecordLinks->create_links('dbh' => $dbh, + 'mode' => 'ids', + 'from_table' => 'oe', + 'from_ids' => $form->{convert_from_oe_ids}, + 'to_table' => 'ap', + 'to_id' => $form->{id}, + ); + delete $form->{convert_from_oe_ids}; + + my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids}; + if (scalar @convert_from_do_ids) { + DO->close_orders('dbh' => $dbh, + 'ids' => \@convert_from_do_ids); + + RecordLinks->create_links('dbh' => $dbh, + 'mode' => 'ids', + 'from_table' => 'delivery_orders', + 'from_ids' => \@convert_from_do_ids, + 'to_table' => 'ap', + 'to_id' => $form->{id}, + ); + } + delete $form->{convert_from_do_ids}; + ARAP->close_orders_if_billed('dbh' => $dbh, + 'arap_id' => $form->{id}, + 'table' => 'ap',); + + my $rc = 1; if (!$provided_dbh) { $rc = $dbh->commit(); $dbh->disconnect(); @@ -729,8 +795,9 @@ sub retrieve_invoice { c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid, c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid, + i.id AS invoice_id, i.description, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber, - i.price_factor_id, i.price_factor, i.marge_price_factor, + i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount, p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin, pr.projectnumber, pg.partsgroup FROM invoice i @@ -747,6 +814,15 @@ sub retrieve_invoice { $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + # Retrieve custom variables. + my $cvars = CVar->get_custom_variables(dbh => $dbh, + module => 'IC', + sub_module => 'invoice', + trans_id => $ref->{invoice_id}, + ); + map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars }; + delete $ref->{invoice_id}; + map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"}; delete($ref->{"part_inventory_accno_id"}); @@ -825,7 +901,8 @@ sub get_vendor { : "current_date"; # get vendor - @values = (); + my @values = (); + my $where = ''; if ($vid) { $where .= 'AND v.id = ?'; push @values, $vid; @@ -836,7 +913,8 @@ sub get_vendor { } my $query = qq|SELECT - v.name AS vendor, v.creditlimit, v.terms, v.notes AS intnotes, + v.id AS vendor_id, v.name AS vendor, v.discount as vendor_discount, + v.creditlimit, v.terms, v.notes AS intnotes, v.email, v.cc, v.bcc, v.language_id, v.payment_id, v.street, v.zipcode, v.city, v.country, v.taxzone_id, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, @@ -940,6 +1018,11 @@ sub retrieve_item { $where .= " AND lower(${table_column}) LIKE lower(?)"; push @values, '%' . $form->{"${field}_${i}"} . '%'; } + #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung + if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) { + $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|; + push @values, $form->{"partnumber_$i"}; + } if ($form->{"description_$i"}) { $where .= " ORDER BY p.description"; @@ -1059,6 +1142,16 @@ sub retrieve_item { } $sth->finish(); + + foreach my $item (@{ $form->{item_list} }) { + my $custom_variables = CVar->get_custom_variables(module => 'IC', + trans_id => $item->{id}, + dbh => $dbh, + ); + + map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables }; + } + $dbh->disconnect(); $main::lxdebug->leave_sub(); @@ -1075,7 +1168,7 @@ sub vendor_details { my @values; # get contact id, set it if nessessary - $form->{cp_id} = (split /--/, $form->{contact})[1]; + $form->{cp_id} *= 1; my $contact = ""; if ($form->{cp_id}) { $contact = "AND cp.cp_id = ?"; @@ -1110,6 +1203,10 @@ sub vendor_details { 'trans_id' => $form->{vendor_id}); map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables }; + $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh, + 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'), + 'allow_fallback' => 1); + $dbh->disconnect(); $main::lxdebug->leave_sub(); @@ -1151,34 +1248,34 @@ sub _delete_payments { my ($self, $form, $dbh) = @_; - my @delete_oids; + my @delete_acc_trans_ids; # Delete old payment entries from acc_trans. my $query = - qq|SELECT oid + qq|SELECT acc_trans_id FROM acc_trans WHERE (trans_id = ?) AND fx_transaction UNION - SELECT at.oid + SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND (c.link LIKE '%AP_paid%')|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); $query = - qq|SELECT at.oid + qq|SELECT at.acc_trans_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id OFFSET 1|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); - if (@delete_oids) { - $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|; + if (@delete_acc_trans_ids) { + $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|; do_query($form, $dbh, $query); } @@ -1235,7 +1332,7 @@ sub post_payment { LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id LIMIT 1|; ($form->{AP}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); @@ -1253,4 +1350,42 @@ sub post_payment { return $rc; } +sub get_duedate { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + if (!$params{vendor_id} || !$params{invdate}) { + $main::lxdebug->leave_sub(); + return $params{default}; + } + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $query = qq|SELECT ?::date + pt.terms_netto + FROM vendor v + LEFT JOIN payment_terms pt ON (pt.id = v.payment_id) + WHERE v.id = ?|; + + my ($sth, $duedate); + + if (($sth = $dbh->prepare($query)) && $sth->execute($params{invdate}, conv_i($params{vendor_id}))) { + ($duedate) = $sth->fetchrow_array(); + $sth->finish(); + } else { + $dbh->rollback(); + } + + $duedate ||= $params{default}; + + $main::lxdebug->leave_sub(); + + return $duedate; +} + + 1;