package IR;
use SL::AM;
+use SL::ARAP;
use SL::Common;
use SL::DBUtils;
+use SL::DO;
use SL::MoreCommon;
use List::Util qw(min);
my $q_item_unit = qq|SELECT unit FROM parts WHERE id = ?|;
my $h_item_unit = prepare_query($form, $dbh, $q_item_unit);
+ $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
+ my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
+ my $price_factor;
+
for my $i (1 .. $form->{rowcount}) {
next unless $form->{"id_$i"};
map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
+ $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
+
if ($form->{"inventory_accno_$i"}) {
- $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
+ $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
if ($form->{taxincluded}) {
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
}
# add purchase to inventory, this one is without the tax!
- $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
- $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) * $form->{exchangerate};
+ $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
+ $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
$linetotal = $form->round_amount($linetotal, 2);
# this is the difference for the inventory
@values = ($form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
do_query($form, $dbh, $query, @values);
- $form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $baseqty, $form->{"id_$i"}) if !$form->{shipped};
-
# check if we sold the item already and
# make an entry for the expense and inventory
$query =
$sth->finish();
- } else {
+ } else { # if ($form->{"inventory_accno_id_$i"})
- $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
+ $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
if ($form->{taxincluded}) {
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
map { $form->{amount}{ $form->{id} }{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } @taxaccounts;
}
- $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
- $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) * $form->{exchangerate};
+ $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
+ $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
$linetotal = $form->round_amount($linetotal, 2);
# this is the difference for expense
$query =
qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
sellprice, fxsellprice, allocated, unit, deliverydate,
- project_id, serialnumber)
- VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
+ 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"}),
$form->{"description_$i"}, $form->{"qty_$i"} * -1,
$baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $allocated,
$form->{"unit_$i"}, conv_date($form->{deliverydate}),
- conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"});
+ 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);
}
$h_item_unit->finish();
- my $project_id = conv_i($form->{"globalproject_id"});
+ $project_id = conv_i($form->{"globalproject_id"});
$form->{datepaid} = $form->{invdate};
# add shipto
$form->{name} = $form->{vendor};
- $form->{name} =~ s/--$form->{vendor_id}//;
+ $form->{name} =~ s/--\Q$form->{vendor_id}\E//;
$form->add_shipto($dbh, $form->{id}, "AP");
# delete zero entries
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();
next unless $ref->{inventory_accno_id};
- # update onhand
- $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|, $ref->{qty});
-
# if $ref->{allocated} > 0 than we sold that many items
next if ($ref->{allocated} <= 0);
$main::lxdebug->enter_sub();
my ($self, $myconfig, $form) = @_;
-
+ my $query;
# connect to database
my $dbh = $form->dbconnect_noauto($myconfig);
&reverse_invoice($dbh, $form);
# delete zero entries
- my $query = qq|DELETE FROM acc_trans WHERE amount = 0|;
+ $query = qq|DELETE FROM acc_trans WHERE amount = 0|;
do_query($form, $dbh, $query);
# delete AP record
- my $query = qq|DELETE FROM ap WHERE id = ?|;
+ $query = qq|DELETE FROM ap WHERE id = ?|;
do_query($form, $dbh, $query, conv_i($form->{id}));
my $rc = $dbh->commit;
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
i.description, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
- p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin, pr.projectnumber, pg.partsgroup
+ i.price_factor_id, i.price_factor, i.marge_price_factor,
+ p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin, pr.projectnumber, pg.partsgroup
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
$ref->{taxaccounts} .= "$ptr->{accno} ";
- if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
+ if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
sub get_vendor {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form, $params) = @_;
+
+ $params = $form unless defined $params && ref $params eq "HASH";
# connect to database
my $dbh = $form->dbconnect($myconfig);
my $dateformat = $myconfig->{dateformat};
$dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
- my $vid = conv_i($form->{vendor_id});
+ my $vid = conv_i($params->{vendor_id});
+ my $vnr = conv_i($params->{vendornumber});
my $duedate =
- ($form->{invdate})
- ? "to_date(" . $dbh->quote($form->{invdate}) . ", '$dateformat')"
+ ($params->{invdate})
+ ? "to_date(" . $dbh->quote($params->{invdate}) . ", '$dateformat')"
: "current_date";
# get vendor
+ @values = ();
+ if ($vid) {
+ $where .= 'AND v.id = ?';
+ push @values, $vid;
+ }
+ if ($vnr) {
+ $where .= 'AND v.vendornumber = ?';
+ push @values, $vnr;
+ }
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.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,
FROM vendor v
LEFT JOIN business b ON (b.id = v.business_id)
LEFT JOIN payment_terms pt ON (v.payment_id = pt.id)
- WHERE v.id = ?|;
- $ref = selectfirst_hashref_query($form, $dbh, $query, $vid);
- map { $form->{$_} = $ref->{$_} } keys %$ref;
+ WHERE 1=1 $where|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
+ map { $params->{$_} = $ref->{$_} } keys %$ref;
- $form->{creditremaining} = $form->{creditlimit};
+ $params->{creditremaining} = $params->{creditlimit};
$query = qq|SELECT SUM(amount - paid) FROM ap WHERE vendor_id = ?|;
my ($unpaid_invoices) = selectfirst_array_query($form, $dbh, $query, $vid);
- $form->{creditremaining} -= $unpaid_invoices;
+ $params->{creditremaining} -= $unpaid_invoices;
$query = qq|SELECT o.amount,
(SELECT e.sell
my $sth = prepare_execute_query($form, $dbh, $query, $vid);
while (my ($amount, $exch) = $sth->fetchrow_array()) {
$exch = 1 unless $exch;
- $form->{creditremaining} -= $amount * $exch;
+ $params->{creditremaining} -= $amount * $exch;
}
$sth->finish();
# get shipto if we do not convert an order or invoice
- if (!$form->{shipto}) {
- delete @{$form}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)};
+ if (!$params->{shipto}) {
+ delete @{$params}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)};
$query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module= 'CT')|;
$ref = selectfirst_hashref_query($form, $dbh, $query, $vid);
- @{$form}{keys %$ref} = @{$ref}{keys %$ref};
- map { $form->{$_} = $ref->{$_} } keys %$ref;
+ @{$params}{keys %$ref} = @{$ref}{keys %$ref};
+ map { $params->{$_} = $ref->{$_} } keys %$ref;
}
- if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
+ if (!$params->{id} && $params->{type} !~ /_(order|quotation)/) {
# setup last accounts used
$query =
qq|SELECT c.id, c.accno, c.description, c.link, c.category
if ($ref->{category} eq 'E') {
$i++;
- if ($form->{initial_transdate}) {
+ if ($params->{initial_transdate}) {
my $tax_query = qq|SELECT tk.tax_id, t.rate FROM taxkeys tk
LEFT JOIN tax t ON (tk.tax_id = t.id)
WHERE (tk.chart_id = ?) AND (startdate <= ?)
ORDER BY tk.startdate DESC
LIMIT 1|;
- my ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $form->{initial_transdate});
- $form->{"taxchart_$i"} = "${tax_id}--${rate}";
+ my ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $params->{initial_transdate});
+ $params->{"taxchart_$i"} = "${tax_id}--${rate}";
}
- $form->{"AP_amount_$i"} = "$ref->{accno}--$tax_id";
+ $params->{"AP_amount_$i"} = "$ref->{accno}--$tax_id";
}
if ($ref->{category} eq 'L') {
- $form->{APselected} = $form->{AP_1} = $ref->{accno};
+ $params->{APselected} = $params->{AP_1} = $ref->{accno};
}
}
- $form->{rowcount} = $i if ($i && !$form->{type});
+ $params->{rowcount} = $i if ($i && !$params->{type});
}
$dbh->disconnect();
p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice,
p.unit, p.assembly, p.bin, p.onhand, p.formel,
p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
- p.inventory_accno_id,
+ p.inventory_accno_id, p.price_factor_id,
+
+ pfac.factor AS price_factor,
c1.accno AS inventory_accno,
c1.new_chart_id AS inventory_new_chart,
FROM buchungsgruppen
WHERE id = p.buchungsgruppen_id) = c3.id)
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+ LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
WHERE $where|;
my $sth = prepare_execute_query($form, $dbh, $query, @values);
$ref->{taxaccounts} .= "$ptr->{accno} ";
- if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
+ if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
$stw->finish();
chop $ref->{taxaccounts};
+ $ref->{onhand} *= 1;
+
push @{ $form->{item_list} }, $ref;
}
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 = ?";
map { $form->{$_} = $ref->{$_} } keys %$ref;
+ my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
+ 'module' => 'CT',
+ 'trans_id' => $form->{vendor_id});
+ map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
+
$dbh->disconnect();
$main::lxdebug->leave_sub();
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;
projects / kivitendo-erp.git / blobdiff