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
$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
$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;
# get default accounts and last invoice number
- $query=
- qq|SELECT
- (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
- (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
- (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
- (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
- (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
- d.curr AS currencies
- $q_invdate
- FROM defaults d|;
+ $query = qq|SELECT
+ (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
+ (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
+ (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
+ (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
+ (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
+ d.curr AS currencies
+ $q_invdate
+ FROM defaults d|;
$ref = selectfirst_hashref_query($form, $dbh, $query);
map { $form->{$_} = $ref->{$_} } keys %$ref;
}
# retrieve invoice
- $query =
- qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,
- orddate, quodate, globalproject_id,
- ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,
- intnotes, curr AS currency
- FROM ap
- WHERE id = ?|;
+ $query = qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,
+ orddate, quodate, globalproject_id,
+ ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,
+ intnotes, curr AS currency
+ FROM ap
+ WHERE id = ?|;
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
map { $form->{$_} = $ref->{$_} } keys %$ref;
- $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
+ $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
# get shipto
$query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
# retrieve individual items
$query =
qq|SELECT
- c1.accno AS inventory_accno,
- c1.new_chart_id AS inventory_new_chart,
- date($transdate) - c1.valid_from AS inventory_valid,
+ c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
+ 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,
- 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.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.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,
+ 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)
- LEFT JOIN chart c1 ON
- ((SELECT inventory_accno_id
- FROM buchungsgruppen
- WHERE id = p.buchungsgruppen_id) = c1.id)
- LEFT JOIN chart c2 ON
- ((SELECT income_accno_id_${taxzone_id}
- FROM buchungsgruppen
- WHERE id = p.buchungsgruppen_id) = c2.id)
- LEFT JOIN chart c3 ON
- ((SELECT expense_accno_id_${taxzone_id}
- FROM buchungsgruppen
- WHERE id = p.buchungsgruppen_id) = c3.id)
+ LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
+ LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
+ LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
LEFT JOIN project pr ON (i.project_id = pr.id)
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- if (!$ref->{"part_inventory_accno_id"}) {
- map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
- }
+ map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
delete($ref->{"part_inventory_accno_id"});
foreach my $type (qw(inventory income expense)) {
while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
- my $query =
- qq|SELECT accno, new_chart_id, date($transdate) - valid_from
- FROM chart
- WHERE id = ?|;
- ($ref->{"${type}_accno"},
- $ref->{"${type}_new_chart"},
- $ref->{"${type}_valid"})
- = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
+ my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
+ @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
}
}
# get tax rates and description
my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
$query =
- qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
- FROM tax t
+ qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
LEFT JOIN chart c ON (c.id = t.chart_id)
WHERE t.id in
- (SELECT tk.tax_id
- FROM taxkeys tk
- WHERE tk.chart_id =
- (SELECT id
- FROM chart
- WHERE accno = ?)
+ (SELECT tk.tax_id FROM taxkeys tk
+ WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
AND (startdate <= $transdate)
ORDER BY startdate DESC
LIMIT 1)
$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,
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};
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();
projects / kivitendo-erp.git / blobdiff