use Data::Dumper;
use SL::AM;
+use SL::Common;
+use SL::DBUtils;
sub invoice_details {
$main::lxdebug->enter_sub();
($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;
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];
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"});
- if ($form->{"qty_$i"} != 0) {
+ if ($form->{"id_$i"} != 0) {
# add number, description and qty to $form->{number},
if ($form->{"subtotal_$i"} && !$subtotal_header) {
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;
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);
}
$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;
$tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
- push(@{ $form->{taxdescription} }, $form->{"${item}_description"});
+ push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
push(@{ $form->{taxrate} },
$form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
$form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
}
}
-
- $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
+ if($form->{taxincluded}) {
+ $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
+ }
+ else {
+ $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
+ }
$yesdiscount = $form->{nodiscount_total} - $nodiscount;
$form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
$form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
($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);
}
# 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|;
# 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->get_employee($dbh);
}
- $form->{contact_id} = $form->{cp_id};
- $form->{contact_id} *= 1;
$form->{payment_id} *= 1;
$form->{language_id} *= 1;
$form->{taxzone_id} *= 1;
($form->{id}) = $sth->fetchrow_array;
$sth->finish;
+
+ if (!$form->{invnumber}) {
+ $form->{invnumber} =
+ $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
+ "cnnumber" : "invnumber", $dbh);
+ }
}
map { $form->{$_} =~ s/\'/\'\'/g }
$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);
}
}
- $project_id = 'NULL';
- if ($form->{"projectnumber_$i"}) {
- $project_id = $form->{"projectnumber_$i"};
- }
$deliverydate =
($form->{"deliverydate_$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;
'$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);
$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/);
) != 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;
}
}
) != 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);
}
}
}
if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
+ transdate, taxkey, 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);
+ WHERE c.accno = ?),
+ $amount, '$form->{"datepaid_$i"}',
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ do_query($form, $dbh, $query, $form->{AR}, $form->{AR}, $project_id);
}
# record payment
$form->{"paid_$i"} *= -1;
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo)
+ source, memo, taxkey, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
+ WHERE c.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"}',
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ do_query($form, $dbh, $query, $accno, $accno, $project_id);
# exchangerate difference
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, cleared, fx_transaction)
+ transdate, cleared, fx_transaction, taxkey, 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);
+ WHERE c.accno = ?),
+ $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1',
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ do_query($form, $dbh, $query, $accno, $accno, $project_id);
}
}
}
# 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},
+ salesman_id = | . conv_i($form->{salesman_id}, 'NULL') . qq|,
storno = '$form->{storno}',
- cp_id = $form->{contact_id}
+ globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
+ cp_id = | . conv_i($form->{"cp_id"}, 'NULL') . qq|
WHERE id = $form->{id}
|;
$dbh->do($query) || $form->dberror($query);
# save printed, emailed and queued
$form->save_status($dbh);
- if ($form->{webdav}) {
- &webdav_folder($myconfig, $form);
- }
+ Common::webdav_folder($form) if ($main::webdav);
my $rc = $dbh->commit;
$dbh->disconnect;
}
sub post_payment {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form, $locale) = @_;
+ $main::lxdebug->enter_sub() and my ($self, $myconfig, $form, $locale) = @_;
# connect to database, turn off autocommit
my $dbh = $form->dbconnect_noauto($myconfig);
# total payments, don't move we need it here
for my $i (1 .. $form->{paidaccounts}) {
- if ($form->{type} eq "credit_note") {
- $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
- } else {
- $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
- }
- $form->{paid} += $form->{"paid_$i"};
- $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
+ $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
+ $form->{"paid_$i"} *= -1 if ($form->{type} eq "credit_note");
+ $form->{"paid"} += $form->{"paid_$i"};
+ $form->{"datepaid"} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
}
- $form->{exchangerate} =
- $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
- "buy");
+ $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
# record payments and offsetting AR
for my $i (1 .. $form->{paidaccounts}) {
+ if ($form->{"paid_$i"}) {
- if ($form->{"paid_$i"} != 0) {
my ($accno) = split /--/, $form->{"AR_paid_$i"};
- $form->{"datepaid_$i"} = $form->{invdate}
- unless ($form->{"datepaid_$i"});
+ $form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"});
$form->{datepaid} = $form->{"datepaid_$i"};
$exchangerate = 0;
if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
$form->{"exchangerate_$i"} = 1;
} else {
- $exchangerate =
- $form->check_exchangerate($myconfig, $form->{currency},
- $form->{"datepaid_$i"}, 'buy');
-
- $form->{"exchangerate_$i"} =
- ($exchangerate)
- ? $exchangerate
- : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
+ $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
+ $form->{"exchangerate_$i"} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
}
# record AR
- $amount =
- $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
- 2);
-
-
- $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
- WHERE c.accno = '$form->{AR}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
- 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->round_amount($form->{"paid_$i"} * $form->{"exchangerate"}, 2);
+ $query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) AND amount = ? AND transdate = ?|;
+ do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, $form->{"datepaid_$i"});
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|;
+ do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, $form->{"datepaid_$i"}, conv_i($form->{"globalproject_id"}), $accno);
# record payment
$form->{"paid_$i"} *= -1;
- $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
- WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo)
- 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);
-
+ $query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) AND amount = ? AND transdate = ? AND source = ? AND memo = ?|;
+ do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"});
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey)
+ VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|;
+ do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"},
+ conv_i($form->{"globalproject_id"}), $accno);
# gain/loss
- $amount =
- $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
- $form->{"exchangerate_$i"};
- if ($amount > 0) {
- $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
- $amount;
- } else {
- $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
- $amount;
- }
+ $amount = $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} * $form->{"exchangerate_$i"};
+ $form->{fx}{ $form->{($amount > 0 ? 'fxgain_accno' : 'fxloss_accno')} }{ $form->{"datepaid_$i"} } += $amount;
$diff = 0;
# update exchange rate
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
- $form->update_exchangerate($dbh, $form->{currency},
- $form->{"datepaid_$i"},
- $form->{"exchangerate_$i"}, 0);
+ $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, $form->{"exchangerate_$i"}, 0);
}
+
}
}
# record exchange rate differences and gains/losses
foreach my $accno (keys %{ $form->{fx} }) {
foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
- if (
- ($form->{fx}{$accno}{$transdate} =
- $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
- ) != 0
- ) {
- $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
- 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)
- 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);
+
+ if ($form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2)) { # '=' is no typo, it's an assignment
+ $query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?)
+ AND amount = ? AND transdate = ? AND cleared = ? AND fx_transaction = ?|;
+ do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1);
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, project_id, taxkey)
+ VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|;
+ do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1, conv_i($form->{"globalproject_id"}), $accno);
}
+
}
}
- my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
# save AR record
- my $query = qq|UPDATE ar set
- paid = $form->{paid},
- datepaid = $datepaid
- WHERE id=$form->{id}|;
+ delete $form->{datepaid} unless $form->{paid};
- $dbh->do($query) || $form->dberror($query);
+ my $query = qq|UPDATE ar set paid = ?, datepaid = ? WHERE id = ?|;
+ do_query($form, $dbh, $query, $form->{paid}, $form->{datepaid}, $form->{id});
my $rc = $dbh->commit;
$dbh->disconnect;
- $main::lxdebug->leave_sub();
-
- return $rc;
+ $main::lxdebug->leave_sub() and return $rc;
}
sub process_assembly {
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.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,
- a.employee_id, e.name AS employee, a.payment_id, a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type
+ a.employee_id, e.name AS employee, a.salesman_id, a.payment_id, a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type
FROM ar a
LEFT JOIN employee e ON (e.id = a.employee_id)
WHERE a.id = $form->{id}|;
$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;
- if ($form->{webdav}) {
- &webdav_folder($myconfig, $form);
- }
+ Common::webdav_folder($form) if ($main::webdav);
}
my $rc = $dbh->commit;
my $query = qq|SELECT c.name AS customer, c.discount, c.creditlimit, c.terms,
c.email, c.cc, c.bcc, c.language_id, c.payment_id AS customer_payment_id,
c.street, c.zipcode, c.city, c.country,
- $duedate + c.terms AS duedate, c.notes AS intnotes,
- b.discount AS tradediscount, b.description AS business, c.klass as customer_klass, c.taxzone_id
+ $duedate + COALESCE(pt.terms_netto, 0) AS duedate, c.notes AS intnotes,
+ b.discount AS tradediscount, b.description AS business, c.klass as customer_klass, c.taxzone_id,
+ c.salesman_id
FROM customer c
LEFT JOIN business b ON (b.id = c.business_id)
- WHERE c.id = $form->{customer_id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
-
+ LEFT JOIN payment_terms pt ON c.payment_id = pt.id
+ WHERE c.id = ?|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{customer_id});
map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- my $query = qq|SELECT sum(a.amount-a.paid) AS dunning_amount FROM ar a WHERE a.paid < a.amount AND a.customer_id=$form->{customer_id} AND a.dunning_id IS NOT NULL|;
- my $sth = $dbh->prepare($query);
-
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
+ my $query = qq|SELECT sum(a.amount - a.paid) AS dunning_amount FROM ar a
+ WHERE a.paid < a.amount AND a.customer_id = ? AND a.dunning_config_id IS NOT NULL|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{customer_id});
map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- #print(STDERR "DUNNING AMOUTN $form->{dunning_amount}\n");
-
- my $query = qq|SELECT dnn.dunning_description AS max_dunning_level FROM dunning_config dnn WHERE id in (select dunning_id from ar WHERE paid < amount AND customer_id=$form->{customer_id} AND dunning_id IS NOT NULL) ORDER BY dunning_level DESC LIMIT 1|;
- my $sth = $dbh->prepare($query);
-
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
+ my $query = qq|SELECT dnn.dunning_description AS max_dunning_level FROM dunning_config dnn
+ WHERE id in (SELECT dunning_config_id from ar WHERE paid < amount AND customer_id = ? AND dunning_id IS NOT NULL)
+ ORDER BY dunning_level DESC LIMIT 1|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{customer_id});
map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
- #print(STDERR "LEVEL $form->{max_dunning_level}\n");
-
#check whether payment_terms are better than old payment_terms
if (($form->{payment_id} ne "") && ($form->{customer_payment_id} ne "")) {
- my $query = qq|select (select ranking from payment_terms WHERE id = $form->{payment_id}), (select ranking from payment_terms WHERE id = $form->{customer_payment_id})|;
+ my $query = qq|SELECT (SELECT ranking from payment_terms WHERE id = $form->{payment_id}), (SELECT ranking FROM payment_terms WHERE id = $form->{customer_payment_id})|;
my $stw = $dbh->prepare($query);
$stw->execute || $form->dberror($query);
($old_ranking, $new_ranking) = $stw->fetchrow_array;
}
$sth->finish;
- $form->get_contacts($dbh, $form->{customer_id});
- $form->{cp_id} *= 1;
-
- # get contact if selected
- if ($form->{cp_id}) {
- $form->get_contact($dbh, $form->{cp_id});
- }
-
# get shipto if we did not converted an order or invoice
if (!$form->{shipto}) {
map { delete $form->{$_} }
}
$sth->finish;
- # get shipping addresses
- $query = qq|SELECT s.id,s.shiptoname
- FROM shipto s
- WHERE s.trans_id = $form->{customer_id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $customertax = ();
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push(@{ $form->{SHIPTO} }, $ref);
- }
- $sth->finish;
-
# setup last accounts used for this customer
if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
- $query = qq|SELECT c.accno, c.description, c.link, c.category
+ $query = qq|SELECT c.id, c.accno, c.description, c.link, c.category
FROM chart c
JOIN acc_trans ac ON (ac.chart_id = c.id)
JOIN ar a ON (a.id = ac.trans_id)
if ($ref->{category} eq 'I') {
$i++;
$form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
+
+ if ($form->{initial_transdate}) {
+ my $tax_query =
+ qq|SELECT tk.tax_id, t.rate FROM taxkeys tk | .
+ qq|LEFT JOIN tax t ON tk.tax_id = t.id | .
+ qq|WHERE tk.chart_id = ? AND startdate <= ? | .
+ qq|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}";
+ }
}
if ($ref->{category} eq 'A') {
- $form->{ARselected} = $form->{AR_1} =
- "$ref->{accno}--$ref->{description}";
+ $form->{ARselected} = $form->{AR_1} = $ref->{accno};
}
}
$sth->finish;
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"};
}
}
$main::lxdebug->leave_sub();
}
-sub webdav_folder {
+sub has_storno {
$main::lxdebug->enter_sub();
- my ($myconfig, $form) = @_;
+ my ($self, $myconfig, $form, $table) = @_;
-SWITCH: {
- $path = "webdav/rechnungen/" . $form->{invnumber}, last SWITCH
- if ($form->{vc} eq "customer");
- $path = "webdav/einkaufsrechnungen/" . $form->{invnumber}, last SWITCH
- if ($form->{vc} eq "vendor");
- }
+ $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
- if (!-d $path) {
- mkdir($path, 0770) or die "can't make directory $!\n";
- } else {
- if ($form->{id}) {
- @files = <$path/*>;
- foreach $file (@files) {
- $file =~ /\/([^\/]*)$/;
- $fname = $1;
- $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
- $lxerp = $1;
- $link = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
- $form->{WEBDAV}{$fname} = $link;
- }
- }
- }
+ # make sure there's no funny stuff in $table
+ # ToDO: die when this happens and throw an error
+ $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
+
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $query = qq|SELECT storno FROM $table WHERE id = ?|;
+ my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
+
+ $dbh->disconnect();
$main::lxdebug->leave_sub();
+
+ return $result;
}
1;
projects / kivitendo-erp.git / blobdiff