package IR;
use SL::AM;
+use SL::Common;
+use SL::DBUtils;
sub post_invoice {
$main::lxdebug->enter_sub();
$sth->finish;
}
- ($null, $form->{contact_id}) = split /--/, $form->{contact};
- $form->{contact_id} *= 1;
-
map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
if ($form->{storno}) {
$form->{"qty_$i"} *= -1;
}
+
+ if ($main::eur) {
+ $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"};
+ }
- if ($form->{"qty_$i"} != 0) {
+ if ($form->{"id_$i"}) {
# get item baseunit
$query = qq|SELECT p.unit
}
- $project_id = 'NULL';
- if ($form->{"projectnumber_$i"}) {
- $project_id = $form->{"projectnumber_$i"};
- }
$deliverydate =
($form->{"deliverydate_$i"})
? qq|'$form->{"deliverydate_$i"}'|
VALUES ($form->{id}, $form->{"id_$i"},
'$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
$form->{"sellprice_$i"}, $fxsellprice, $allocated,
- '$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"}')|;
$dbh->do($query) || $form->dberror($query);
}
}
+ $project_id = conv_i($form->{"globalproject_id"});
+
$form->{datepaid} = $form->{invdate};
# all amounts are in natural state, netamount includes the taxes
) != 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);
}
}
}
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
+ transdate, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
WHERE c.accno = '$form->{AP}'),
- $amount, '$form->{"datepaid_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ $amount, '$form->{"datepaid_$i"}', ?)|;
+ do_query($form, $dbh, $query, $project_id);
}
# record payment
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo)
+ source, memo, project_id)
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);
+ '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
+ do_query($form, $dbh, $query, $project_id);
$exchangerate = 0;
) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, cleared, fx_transaction)
+ transdate, cleared, fx_transaction, 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);
+ $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|;
+ do_query($form, $dbh, $query, $project_id);
}
}
}
ordnumber = '$form->{ordnumber}',
quonumber = '$form->{quonumber}',
transdate = '$form->{invdate}',
+ orddate = | . conv_dateq($form->{"orddate"}) . qq|,
+ quodate = | . conv_dateq($form->{"quodate"}) . qq|,
vendor_id = $form->{vendor_id},
amount = $amount,
netamount = $netamount,
curr = '$form->{currency}',
department_id = $form->{department_id},
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);
WHERE amount = 0|;
$dbh->do($query) || $form->dberror($query);
- if ($form->{webdav}) {
- &webdav_folder($myconfig, $form);
- }
+ Common::webdav_folder($form) if ($main::webdav);
my $rc = $dbh->commit;
$dbh->disconnect;
# retrieve invoice
$query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
- a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno,
+ a.orddate, a.quodate, a.globalproject_id,
+ a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
a.intnotes, a.curr AS currency
FROM ap a
WHERE a.id = $form->{id}|;
delete($ref->{id});
map { $form->{$_} = $ref->{$_} } keys %$ref;
$sth->finish;
- my $transdate = "current_date";
- if($form->{invdate}) {
- $transdate = "'$form->{invdate}'";
- }
+
+ my $transdate =
+ $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
if(!$form->{taxzone_id}) {
$form->{taxzone_id} = 0;
# get tax rates and description
$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 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|;
+ $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 v.name AS vendor, v.creditlimit, v.terms,
v.email, v.cc, v.bcc, v.language_id, v.payment_id,
v.street, v.zipcode, v.city, v.country, v.taxzone_id,
- $duedate + v.terms AS duedate
+ $duedate + v.terms AS duedate,
+ b.description AS business
FROM vendor v
+ LEFT JOIN business b ON b.id = v.business_id
WHERE v.id = $form->{vendor_id}|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
}
$sth->finish;
- $form->get_contacts($dbh, $form->{vendor_id});
-
- ($null, $form->{cp_id}) = split /--/, $form->{contact};
-
- # get contact if selected
- if ($form->{contact} ne "--" && $form->{contact} ne "") {
- $form->get_contact($dbh, $form->{cp_id});
- }
-
# get shipto if we do not convert an order or invoice
if (!$form->{shipto}) {
map { delete $form->{$_} }
my ($self, $myconfig, $form) = @_;
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
my $i = $form->{rowcount};
# don't include assemblies or obsolete parts
my $transdate = "";
if ($form->{type} eq "invoice") {
- $transdate = "'$form->{invdate}'";
- } elsif ($form->{type} eq "purchase_order") {
- $transdate = "'$form->{transdate}'";
- } elsif ($form->{type} eq "request_quotation") {
- $transdate = "'$form->{transdate}'";
- }
-
- if ($transdate eq "") {
- $transdate = "current_date";
+ $transdate =
+ $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,
+ my $query = qq|SELECT p.id, p.partnumber, p.description, p.lastcost AS 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,
$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 LEFT JOIN chart c ON (c.id=t.chart_id)
- WHERE t.taxkey in (SELECT taxkey_id from chart where accno = '$accno_id')
+ 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);
sub vendor_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;
$main::lxdebug->leave_sub();
}
-sub webdav_folder {
- $main::lxdebug->enter_sub();
-
- my ($myconfig, $form) = @_;
-
-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");
- }
-
- 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;
- }
- }
- }
-
- $main::lxdebug->leave_sub();
-}
-
sub post_payment {
$main::lxdebug->enter_sub();
$form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
"buy");
+ my $project_id = conv_i($form->{"globalproject_id"});
+
# record payments and offsetting AP
for my $i (1 .. $form->{paidaccounts}) {
$dbh->do($query) || $form->dberror($query);
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
+ transdate, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
WHERE c.accno = '$form->{AP}'),
- $amount, '$form->{"datepaid_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ $amount, '$form->{"datepaid_$i"}', ?)|;
+ do_query($form, $dbh, $query, $project_id);
$dbh->do($query) || $form->dberror($query);
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo)
+ source, memo, project_id)
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);
+ '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
+ do_query($form, $dbh, $query, $project_id);
# gain/loss
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)
+ transdate, cleared, fx_transaction, 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);
+ $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|;
+ do_query($form, $dbh, $query, $project_id);
}
}
}