use Data::Dumper;
use SL::AM;
+use SL::DBUtils;
sub invoice_details {
$main::lxdebug->enter_sub();
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
- if ($form->{"qty_$i"} != 0) {
+ if ($form->{"id_$i"} != 0) {
# add number, description and qty to $form->{number},
if ($form->{"subtotal_$i"} && !$subtotal_header) {
($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
$form->{total} =
$form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
+
$form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
- $form->set_payment_options($myconfig, $form->{invdate});
$form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
+ $form->set_payment_options($myconfig, $form->{invdate});
$form->{username} = $myconfig->{name};
sub customer_details {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form, @wanted_vars) = @_;
# connect to database
my $dbh = $form->dbconnect($myconfig);
# remove id and taxincluded before copy back
delete @$ref{qw(id taxincluded)};
+
+ @wanted_vars = grep({ $_ } @wanted_vars);
+ if (scalar(@wanted_vars) > 0) {
+ my %h_wanted_vars;
+ map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
+ map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
+ }
+
map { $form->{$_} = $ref->{$_} } keys %$ref;
$sth->finish;
$form->{"qty_$i"} *= -1;
}
- if ($form->{"qty_$i"} != 0) {
+ if ($form->{"id_$i"}) {
# get item baseunit
$query = qq|SELECT p.unit
$baseqty * -1)
unless $form->{shipped};
- $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor);
+ $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
}
}
? qq|'$form->{"deliverydate_$i"}'|
: "NULL";
- # get pricegroup_id and save ist
- ($null, my $pricegroup_id) = split /--/, $form->{"sellprice_drag_$i"};
+ # get pricegroup_id and save it
+ ($null, my $pricegroup_id) = split /--/, $form->{"sellprice_pg_$i"};
$pricegroup_id *= 1;
my $subtotal = $form->{"subtotal_$i"} * 1;
# save AR record
$query = qq|UPDATE ar set
invnumber = '$form->{invnumber}',
- ordnumber = '$form->{ordnumber}',
- quonumber = '$form->{quonumber}',
- cusordnumber = '$form->{cusordnumber}',
+ ordnumber = '$form->{ordnumber}',
+ quonumber = '$form->{quonumber}',
+ cusordnumber = '$form->{cusordnumber}',
transdate = '$form->{invdate}',
+ orddate = | . conv_dateq($form->{orddate}) . qq|,
+ quodate = | . conv_dateq($form->{quodate}) . qq|,
customer_id = $form->{customer_id},
amount = $amount,
netamount = $netamount,
paid = $form->{paid},
- datepaid = $datepaid,
- duedate = $duedate,
+ datepaid = $datepaid,
+ duedate = $duedate,
deliverydate = $deliverydate,
- invoice = '1',
- shippingpoint = '$form->{shippingpoint}',
- shipvia = '$form->{shipvia}',
- terms = $form->{terms},
- notes = '$form->{notes}',
- intnotes = '$form->{intnotes}',
- taxincluded = '$form->{taxincluded}',
- curr = '$form->{currency}',
- department_id = $form->{department_id},
- payment_id = $form->{payment_id},
+ invoice = '1',
+ shippingpoint = '$form->{shippingpoint}',
+ shipvia = '$form->{shipvia}',
+ terms = $form->{terms},
+ notes = '$form->{notes}',
+ intnotes = '$form->{intnotes}',
+ taxincluded = '$form->{taxincluded}',
+ curr = '$form->{currency}',
+ department_id = $form->{department_id},
+ payment_id = $form->{payment_id},
type = '$form->{type}',
- language_id = $form->{language_id},
- taxzone_id = $form->{taxzone_id},
- shipto_id = $form->{shipto_id},
- delivery_customer_id = $form->{delivery_customer_id},
- delivery_vendor_id = $form->{delivery_vendor_id},
- employee_id = $form->{employee_id},
+ language_id = $form->{language_id},
+ taxzone_id = $form->{taxzone_id},
+ shipto_id = $form->{shipto_id},
+ delivery_customer_id = $form->{delivery_customer_id},
+ delivery_vendor_id = $form->{delivery_vendor_id},
+ employee_id = $form->{employee_id},
storno = '$form->{storno}',
cp_id = $form->{contact_id}
WHERE id = $form->{id}
sub cogs {
$main::lxdebug->enter_sub();
- my ($dbh, $form, $id, $totalqty, $basefactor) = @_;
-
+ my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
+ $form->{taxzone_id} *=1;
+ my $transdate = ($form->{invdate}) ? "'$form->{invdate}'" : "current_date";
my $query = qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
- (SELECT c.accno FROM chart c
- WHERE p.inventory_accno_id = c.id) AS inventory_accno,
- (SELECT c.accno FROM chart c
- WHERE p.expense_accno_id = c.id) AS expense_accno
+ 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
FROM invoice i, parts p
+ 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_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
+ LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
WHERE i.parts_id = p.id
AND i.parts_id = $id
AND (i.base_qty + i.allocated) < 0
# sellprice is the cost of the item
$linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
- if (!$eur) {
-
+ if (!$main::eur) {
+ $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
# add to expense
$form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
$form->{expense_inventory} .= " " . $ref->{expense_accno};
-
+ $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
# deduct inventory
$form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
$form->{expense_inventory} .= " " . $ref->{inventory_accno};
# retrieve invoice
$query = qq|SELECT a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
- a.transdate AS invdate, a.deliverydate, a.paid, a.storno,
+ a.orddate, a.quodate,
+ 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,
a.employee_id, e.name AS employee, a.payment_id, a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type
$sth->finish;
map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
- my $transdate = "current_date";
- if($form->{invdate}) {
- $transdate = "'$form->{invdate}'";
- }
+ my $transdate =
+ $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
+ $form->{invdate} ? $dbh->quote($form->{invdate}) :
+ "current_date";
- if(!$form->{taxzone_id}) {
+ if (!$form->{taxzone_id}) {
$form->{taxzone_id} = 0;
}
# retrieve individual items
i.project_id, pr.projectnumber, i.serialnumber,
p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, i.id AS invoice_pos,
pg.partsgroup, i.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=i.pricegroup_id) as pricegroup,
- i.ordnumber, i.transdate, i.cusordnumber, p.alu, p.formel, i.subtotal
+ i.ordnumber, i.transdate, i.cusordnumber, p.formel, i.subtotal
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
LEFT JOIN project pr ON (i.project_id = pr.id)
}
delete($ref->{"part_inventory_accno_id"});
- #set expense_accno=inventory_accno if they are different => bilanz
-
-
while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
my $query = qq| SELECT accno AS inventory_accno, new_chart_id AS inventory_new_chart, date($transdate) - valid_from AS inventory_valid FROM chart WHERE id = $ref->{inventory_new_chart}|;
my $stw = $dbh->prepare($query);
$stw->finish;
}
- $vendor_accno =
- ($ref->{expense_accno} != $ref->{inventory_accno})
- ? $ref->{inventory_accno}
- : $ref->{expense_accno};
-
# get tax rates and description
$accno_id =
- ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
- $query = 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.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
- ORDER BY accno|;
+ ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
+ $query = 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='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
+ ORDER BY c.accno|;
$stw = $dbh->prepare($query);
$stw->execute || $form->dberror($query);
$ref->{taxaccounts} = "";
$sth->finish;
# get shipping addresses
- $query = qq|SELECT s.id,s.shiptoname
+ $query = qq|SELECT s.shipto_id,s.shiptoname,s.shiptodepartment_1
FROM shipto s
WHERE s.trans_id = $form->{customer_id}|;
$sth = $dbh->prepare($query);
my ($self, $myconfig, $form) = @_;
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
my $i = $form->{rowcount};
my $where = "NOT p.obsolete = '1'";
$where .= " ORDER BY p.partnumber";
}
- my $transdate = "";
+ my $transdate;
if ($form->{type} eq "invoice") {
- $transdate = "'$form->{invdate}'";
- } elsif ($form->{type} eq "sales_order") {
- $transdate = "'$form->{transdate}'";
- } elsif ($form->{type} eq "sales_quotation") {
- $transdate = "'$form->{transdate}'";
- }
-
- if ($transdate eq "") {
- $transdate = "current_date";
+ $transdate =
+ $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
+ $form->{invdate} ? $dbh->quote($form->{invdate}) :
+ "current_date";
+ } else {
+ $transdate =
+ $form->{transdate} ? $dbh->quote($form->{transdate}) :
+ "current_date";
}
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
p.listprice, p.inventory_accno_id,
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,
p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
- pg.partsgroup, p.formel, p.alu, p.payment_id AS part_payment_id, adr.adr_description
+ pg.partsgroup, p.formel, p.payment_id AS part_payment_id
FROM parts p
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_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
- LEFT JOIN adr adr ON (adr.id = p.adr_id)
WHERE $where|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
}
}
-
- $vendor_accno =
- ($ref->{expense_accno} != $ref->{inventory_accno})
- ? $ref->{inventory_accno}
- : $ref->{expense_accno};
-
# get tax rates and description
$accno_id =
- ($form->{vc} eq "customer") ? $ref->{income_accno} : $vendor_accno;
+ ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
$query = 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.taxkey in (SELECT c2.taxkey_id from chart c2 where c2.accno = '$accno_id')
+ WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
ORDER BY c.accno|;
$stw = $dbh->prepare($query);
$stw->execute || $form->dberror($query);
my $dbh = $form->dbconnect($myconfig);
+ $form->{"PRICES"} = {};
+
my $i = 1;
my $id = 0;
my $dimension_units = AM->retrieve_units($myconfig, $form, "dimension");
my $service_units = AM->retrieve_units($myconfig, $form, "service");
my $all_units = AM->retrieve_units($myconfig, $form);
while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
+ $form->{"PRICES"}{$i} = [];
$id = $form->{"id_$i"};
}
($price, $selectedpricegroup_id) = split /--/,
- $form->{"sellprice_drag_$i"};
+ $form->{"sellprice_pg_$i"};
$pricegroup_old = $form->{"pricegroup_old_$i"};
$form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
and ($pkr->{price} == $form->{"sellprice_$i"})) {
# $pkr->{price} = $form->{"sellprice_$i"};
- } else {
+ } else {
$pkr->{price} = $form->{"sellprice_$i"};
}
}
projects / kivitendo-erp.git / blobdiff