Revision 9d0ebf28
Von Moritz Bunkus vor fast 18 Jahren hinzugefügt
| SL/OE.pm | ||
|---|---|---|
| 49 | 49 |
my $query; |
| 50 | 50 |
my $ordnumber = 'ordnumber'; |
| 51 | 51 |
my $quotation = '0'; |
| 52 |
my ($null, $department_id) = split /--/, $form->{department};
|
|
| 53 | 52 |
|
| 54 |
my $department = " AND o.department_id = $department_id" if $department_id; |
|
| 55 | 53 |
my @values; |
| 54 |
my $where; |
|
| 55 |
|
|
| 56 |
my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
|
|
| 57 |
|
|
| 58 |
if ($form->{type} =~ /_quotation$/) {
|
|
| 59 |
$quotation = '1'; |
|
| 60 |
$ordnumber = 'quonumber'; |
|
| 61 |
} |
|
| 62 |
|
|
| 63 |
my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
|
|
| 64 |
|
|
| 65 |
$query = |
|
| 66 |
qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | . |
|
| 67 |
qq| o.amount, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
|
|
| 68 |
qq| o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, | . |
|
| 69 |
qq| ex.$rate AS exchangerate, | . |
|
| 70 |
qq| pr.projectnumber AS globalprojectnumber, | . |
|
| 71 |
qq| e.name AS employee | . |
|
| 72 |
qq|FROM oe o | . |
|
| 73 |
qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
|
|
| 74 |
qq|LEFT JOIN employee e ON (o.employee_id = e.id) | . |
|
| 75 |
qq|LEFT JOIN exchangerate ex ON (ex.curr = o.curr | . |
|
| 76 |
qq| AND ex.transdate = o.transdate) | . |
|
| 77 |
qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | . |
|
| 78 |
qq|WHERE (o.quotation = ?) |; |
|
| 79 |
push(@values, $quotation); |
|
| 80 |
|
|
| 81 |
my ($null, $department_id) = split /--/, $form->{department};
|
|
| 82 |
if ($department_id) {
|
|
| 83 |
$query .= qq| AND o.department_id = ?|; |
|
| 84 |
push(@values, $department_id); |
|
| 85 |
} |
|
| 56 | 86 |
|
| 57 | 87 |
if ($form->{"project_id"}) {
|
| 58 |
$department .=
|
|
| 88 |
$query .=
|
|
| 59 | 89 |
qq|AND ((globalproject_id = ?) OR EXISTS | . |
| 60 | 90 |
qq| (SELECT * FROM orderitems oi | . |
| 61 | 91 |
qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|; |
| 62 | 92 |
push(@values, $form->{"project_id"}, $form->{"project_id"});
|
| 63 | 93 |
} |
| 64 | 94 |
|
| 65 |
my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
|
|
| 95 |
if ($form->{"${vc}_id"}) {
|
|
| 96 |
$query .= " AND o.${vc}_id = ?";
|
|
| 97 |
push(@values, $form->{"${vc}_id"});
|
|
| 66 | 98 |
|
| 67 |
if ($form->{type} =~ /_quotation$/) {
|
|
| 68 |
$quotation = '1';
|
|
| 69 |
$ordnumber = 'quonumber';
|
|
| 99 |
} elsif ($form->{$vc}) {
|
|
| 100 |
$query .= " AND ct.name ILIKE ?";
|
|
| 101 |
push(@values, '%' . $form->{$vc} . '%');
|
|
| 70 | 102 |
} |
| 71 | 103 |
|
| 72 |
my $number = $form->like(lc $form->{$ordnumber});
|
|
| 73 |
my $name = $form->like(lc $form->{ $form->{vc} });
|
|
| 74 |
|
|
| 75 |
my $query = qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, |
|
| 76 |
o.amount, ct.name, o.netamount, o.$form->{vc}_id,
|
|
| 77 |
o.globalproject_id, pr.projectnumber AS globalprojectnumber, |
|
| 78 |
ex.$rate AS exchangerate, |
|
| 79 |
o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, |
|
| 80 |
e.name AS employee |
|
| 81 |
FROM oe o |
|
| 82 |
JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
|
|
| 83 |
LEFT JOIN employee e ON (o.employee_id = e.id) |
|
| 84 |
LEFT JOIN exchangerate ex ON (ex.curr = o.curr |
|
| 85 |
AND ex.transdate = o.transdate) |
|
| 86 |
LEFT JOIN project pr ON o.globalproject_id = pr.id |
|
| 87 |
WHERE o.quotation = '$quotation' |
|
| 88 |
$department|; |
|
| 89 |
|
|
| 90 |
if ($form->{"$form->{vc}_id"}) {
|
|
| 91 |
$query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
|
|
| 92 |
} else {
|
|
| 93 |
if ($form->{ $form->{vc} }) {
|
|
| 94 |
$query .= " AND lower(ct.name) LIKE '$name'"; |
|
| 95 |
} |
|
| 96 |
} |
|
| 97 | 104 |
if (!$form->{open} && !$form->{closed}) {
|
| 98 | 105 |
$query .= " AND o.id = 0"; |
| 99 | 106 |
} elsif (!($form->{open} && $form->{closed})) {
|
| ... | ... | |
| 106 | 113 |
" AND o.delivered " : " AND NOT o.delivered"; |
| 107 | 114 |
} |
| 108 | 115 |
|
| 109 |
my $sortorder = join ', ', |
|
| 110 |
("o.id", $form->sort_columns(transdate, $ordnumber, name));
|
|
| 111 |
$sortorder = $form->{sort} if $form->{sort};
|
|
| 116 |
if ($form->{$ordnumber}) {
|
|
| 117 |
$query .= qq| AND $ordnumber ILIKE ?|; |
|
| 118 |
push(@values, '%' . $form->{$ordnumber} . '%');
|
|
| 119 |
} |
|
| 120 |
|
|
| 121 |
if($form->{transdatefrom}) {
|
|
| 122 |
$query .= qq| AND o.transdate >= ?|; |
|
| 123 |
push(@values, conv_date($form->{transdatefrom}));
|
|
| 124 |
} |
|
| 112 | 125 |
|
| 113 |
$query .= " AND lower($ordnumber) LIKE '$number'" if $form->{$ordnumber};
|
|
| 114 |
$query .= " AND o.transdate >= '$form->{transdatefrom}'"
|
|
| 115 |
if $form->{transdatefrom};
|
|
| 116 |
$query .= " AND o.transdate <= '$form->{transdateto}'"
|
|
| 117 |
if $form->{transdateto};
|
|
| 118 |
$query .= " ORDER by $sortorder"; |
|
| 126 |
if($form->{transdateto}) {
|
|
| 127 |
$query .= qq| AND o.transdate <= ?|; |
|
| 128 |
push(@values, conv_date($form->{transdateto}));
|
|
| 129 |
} |
|
| 130 |
|
|
| 131 |
my $sortorder = join(', ', ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
|
|
| 132 |
my %allowed_sort_columns = |
|
| 133 |
("transdate" => "o.transdate",
|
|
| 134 |
"reqdate" => "o.reqdate", |
|
| 135 |
"id" => "o.id", |
|
| 136 |
"ordnumber" => "o.ordnumber", |
|
| 137 |
"quonumber" => "o.quonumber", |
|
| 138 |
"name" => "ct.name", |
|
| 139 |
"employee" => "e.name", |
|
| 140 |
"shipvia" => "o.shipvia"); |
|
| 141 |
if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
|
|
| 142 |
$sortorder = $allowed_sort_columns{$form->{sort}};
|
|
| 143 |
} |
|
| 144 |
$query .= qq| ORDER by | . $sortorder; |
|
| 119 | 145 |
|
| 120 | 146 |
my $sth = $dbh->prepare($query); |
| 121 | 147 |
$sth->execute(@values) || |
| 122 | 148 |
$form->dberror($query . " (" . join(", ", @values) . ")");
|
| 123 | 149 |
|
| 124 | 150 |
my %id = (); |
| 151 |
$form->{OE} = [];
|
|
| 125 | 152 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 126 | 153 |
$ref->{exchangerate} = 1 unless $ref->{exchangerate};
|
| 127 | 154 |
push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
|
| ... | ... | |
| 142 | 169 |
# connect to database, turn off autocommit |
| 143 | 170 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 144 | 171 |
|
| 145 |
my ($query, $sth, $null); |
|
| 172 |
my ($query, @values, $sth, $null);
|
|
| 146 | 173 |
my $exchangerate = 0; |
| 147 | 174 |
|
| 148 |
my $service_units = AM->retrieve_units($myconfig,$form,"service"); |
|
| 149 |
my $part_units = AM->retrieve_units($myconfig,$form,"dimension"); |
|
| 150 |
$form->{service_units} =$service_units;
|
|
| 151 |
$form->{part_units} =$part_units;
|
|
| 175 |
my $all_units = AM->retrieve_units($myconfig, $form); |
|
| 176 |
$form->{all_units} = $all_units;
|
|
| 152 | 177 |
|
| 153 | 178 |
($null, $form->{employee_id}) = split /--/, $form->{employee};
|
| 154 | 179 |
unless ($form->{employee_id}) {
|
| 155 | 180 |
$form->get_employee($dbh); |
| 156 | 181 |
} |
| 157 | 182 |
|
| 158 |
$form->{payment_id} *= 1;
|
|
| 159 |
$form->{language_id} *= 1;
|
|
| 160 |
$form->{shipto_id} *= 1;
|
|
| 161 |
$form->{delivery_customer_id} *= 1;
|
|
| 162 |
$form->{delivery_vendor_id} *= 1;
|
|
| 163 |
|
|
| 164 | 183 |
my $ml = ($form->{type} eq 'sales_order') ? 1 : -1;
|
| 165 | 184 |
|
| 166 | 185 |
if ($form->{id}) {
|
| 167 | 186 |
|
| 168 | 187 |
&adj_onhand($dbh, $form, $ml) if $form->{type} =~ /_order$/;
|
| 169 | 188 |
|
| 170 |
$query = qq|DELETE FROM orderitems |
|
| 171 |
WHERE trans_id = $form->{id}|;
|
|
| 172 |
$dbh->do($query) || $form->dberror($query); |
|
| 189 |
$query = qq|DELETE FROM orderitems WHERE trans_id = ?|; |
|
| 190 |
do_query($form, $dbh, $query, $form->{id});
|
|
| 173 | 191 |
|
| 174 |
$query = qq|DELETE FROM shipto |
|
| 175 |
WHERE trans_id = $form->{id} AND module = 'OE'|;
|
|
| 176 |
$dbh->do($query) || $form->dberror($query);
|
|
| 192 |
$query = qq|DELETE FROM shipto | .
|
|
| 193 |
qq|WHERE trans_id = ? AND module = 'OE'|;
|
|
| 194 |
do_query($form, $dbh, $query, $form->{id});
|
|
| 177 | 195 |
|
| 178 | 196 |
} else {
|
| 179 | 197 |
|
| 180 |
my $uid = rand() . time; |
|
| 181 |
|
|
| 182 |
$uid .= $form->{login};
|
|
| 183 |
|
|
| 184 |
$uid = substr($uid, 2, 75); |
|
| 185 |
|
|
| 186 |
$query = qq|INSERT INTO oe (ordnumber, employee_id) |
|
| 187 |
VALUES ('$uid', $form->{employee_id})|;
|
|
| 188 |
$dbh->do($query) || $form->dberror($query); |
|
| 189 |
|
|
| 190 |
$query = qq|SELECT o.id FROM oe o |
|
| 191 |
WHERE o.ordnumber = '$uid'|; |
|
| 192 |
$sth = $dbh->prepare($query); |
|
| 193 |
$sth->execute || $form->dberror($query); |
|
| 198 |
$query = qq|SELECT nextval('id')|;
|
|
| 199 |
($form->{id}) = selectrow_query($form, $dbh, $query);
|
|
| 194 | 200 |
|
| 195 |
($form->{id}) = $sth->fetchrow_array;
|
|
| 196 |
$sth->finish;
|
|
| 201 |
$query = qq|INSERT INTO oe (id, ordnumber, employee_id) VALUES (?, '', ?)|;
|
|
| 202 |
do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
|
|
| 197 | 203 |
} |
| 198 | 204 |
|
| 199 |
map { $form->{$_} =~ s/\'/\'\'/g }
|
|
| 200 |
qw(ordnumber quonumber shippingpoint shipvia notes intnotes message); |
|
| 201 |
|
|
| 202 | 205 |
my $amount; |
| 203 | 206 |
my $linetotal; |
| 204 | 207 |
my $discount; |
| ... | ... | |
| 214 | 217 |
|
| 215 | 218 |
for my $i (1 .. $form->{rowcount}) {
|
| 216 | 219 |
|
| 217 |
map {
|
|
| 218 |
$form->{"${_}_$i"} =
|
|
| 219 |
$form->parse_amount($myconfig, $form->{"${_}_$i"})
|
|
| 220 |
} qw(qty ship); |
|
| 220 |
map({ $form->{"${_}_$i"} =
|
|
| 221 |
$form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(qty ship));
|
|
| 221 | 222 |
|
| 222 | 223 |
if ($form->{"id_$i"}) {
|
| 223 | 224 |
|
| 224 | 225 |
# get item baseunit |
| 225 |
$query = qq|SELECT p.unit |
|
| 226 |
FROM parts p |
|
| 227 |
WHERE p.id = $form->{"id_$i"}|;
|
|
| 228 |
$sth = $dbh->prepare($query); |
|
| 229 |
$sth->execute || $form->dberror($query); |
|
| 230 |
|
|
| 231 |
my ($item_unit) = $sth->fetchrow_array(); |
|
| 232 |
$sth->finish; |
|
| 233 |
|
|
| 234 |
if ($form->{"inventory_accno_$i"}) {
|
|
| 235 |
if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
|
|
| 236 |
$basefactor = $part_units->{$form->{"unit_$i"}}->{factor} / $part_units->{$item_unit}->{factor};
|
|
| 237 |
} else {
|
|
| 238 |
$basefactor = 1; |
|
| 239 |
} |
|
| 240 |
$baseqty = $form->{"qty_$i"} * $basefactor;
|
|
| 241 |
} else {
|
|
| 242 |
if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
|
|
| 243 |
$basefactor = $service_units->{$form->{"unit_$i"}}->{factor} / $service_units->{$item_unit}->{factor};
|
|
| 244 |
} else {
|
|
| 245 |
$basefactor = 1; |
|
| 246 |
} |
|
| 247 |
$baseqty = $form->{"qty_$i"} * $basefactor;
|
|
| 226 |
$query = qq|SELECT unit FROM parts WHERE id = ?|; |
|
| 227 |
my ($item_unit) = selectrow_query($form, $dbh, $query, $form->{"id_$i"});
|
|
| 228 |
|
|
| 229 |
my $basefactor = 1; |
|
| 230 |
if (defined($all_units->{$item_unit}->{factor}) &&
|
|
| 231 |
(($all_units->{$item_unit}->{factor} * 1) != 0)) {
|
|
| 232 |
$basefactor = $all_units->{$form->{"unit_$i"}}->{factor} /
|
|
| 233 |
$all_units->{$item_unit}->{factor};
|
|
| 248 | 234 |
} |
| 249 |
|
|
| 250 |
map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
|
|
| 251 |
qw(partnumber description unit); |
|
| 235 |
my $baseqty = $form->{"qty_$i"} * $basefactor;
|
|
| 252 | 236 |
|
| 253 | 237 |
# set values to 0 if nothing entered |
| 254 | 238 |
$form->{"discount_$i"} =
|
| ... | ... | |
| 259 | 243 |
$fxsellprice = $form->{"sellprice_$i"};
|
| 260 | 244 |
|
| 261 | 245 |
my ($dec) = ($form->{"sellprice_$i"} =~ /\.(\d+)/);
|
| 262 |
$dec = length $dec;
|
|
| 246 |
$dec = length($dec);
|
|
| 263 | 247 |
my $decimalplaces = ($dec > 2) ? $dec : 2; |
| 264 | 248 |
|
| 265 | 249 |
$discount = |
| ... | ... | |
| 275 | 259 |
$linetotal = |
| 276 | 260 |
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
| 277 | 261 |
|
| 278 |
@taxaccounts = split / /, $form->{"taxaccounts_$i"};
|
|
| 262 |
@taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
|
|
| 279 | 263 |
$taxrate = 0; |
| 280 | 264 |
$taxdiff = 0; |
| 281 | 265 |
|
| ... | ... | |
| 324 | 308 |
$netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"};
|
| 325 | 309 |
|
| 326 | 310 |
$reqdate = |
| 327 |
($form->{"reqdate_$i"}) ? qq|'$form->{"reqdate_$i"}'| : "NULL";
|
|
| 311 |
($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
|
|
| 328 | 312 |
|
| 329 | 313 |
# get pricegroup_id and save ist |
| 330 |
($null, my $pricegroup_id) = split /--/, $form->{"sellprice_pg_$i"};
|
|
| 314 |
($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
|
|
| 331 | 315 |
$pricegroup_id *= 1; |
| 332 | 316 |
$subtotal = $form->{"subtotal_$i"} * 1;
|
| 333 | 317 |
|
| 334 | 318 |
# save detail record in orderitems table |
| 319 |
@values = (); |
|
| 335 | 320 |
$query = qq|INSERT INTO orderitems (|; |
| 336 |
$query .= "id, " if $form->{"orderitems_id_$i"};
|
|
| 337 |
$query .= qq|trans_id, parts_id, description, longdescription, qty, base_qty, sellprice, discount, |
|
| 338 |
unit, reqdate, project_id, serialnumber, ship, pricegroup_id, |
|
| 339 |
ordnumber, transdate, cusordnumber, subtotal) |
|
| 340 |
VALUES (|; |
|
| 341 |
$query .= qq|$form->{"orderitems_id_$i"},|
|
|
| 342 |
if $form->{"orderitems_id_$i"};
|
|
| 343 |
$query .= qq|$form->{id}, $form->{"id_$i"},
|
|
| 344 |
'$form->{"description_$i"}', '$form->{"longdescription_$i"}', $form->{"qty_$i"}, $baseqty,
|
|
| 345 |
$fxsellprice, $form->{"discount_$i"},
|
|
| 346 |
'$form->{"unit_$i"}', $reqdate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
|
|
| 347 |
'$form->{"serialnumber_$i"}', $form->{"ship_$i"}, '$pricegroup_id',
|
|
| 348 |
'$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', '$subtotal')|;
|
|
| 349 |
$dbh->do($query) || $form->dberror($query); |
|
| 321 |
if ($form->{"orderitems_id_$i"}) {
|
|
| 322 |
$query .= "id, "; |
|
| 323 |
} |
|
| 324 |
$query .= qq|trans_id, parts_id, description, longdescription, qty, base_qty, | . |
|
| 325 |
qq|sellprice, discount, unit, reqdate, project_id, serialnumber, ship, | . |
|
| 326 |
qq|pricegroup_id, ordnumber, transdate, cusordnumber, subtotal) | . |
|
| 327 |
qq|VALUES (|; |
|
| 328 |
if($form->{"orderitems_id_$i"}) {
|
|
| 329 |
$query .= qq|?,|; |
|
| 330 |
push(@values, $form->{"orderitems_id_$i"});
|
|
| 331 |
} |
|
| 332 |
$query .= qq|?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; |
|
| 333 |
push(@values, |
|
| 334 |
conv_i($form->{id}), conv_i($form->{"id_$i"}),
|
|
| 335 |
$form->{"description_$i"}, $form->{"longdescription_$i"},
|
|
| 336 |
$form->{"qty_$i"}, $baseqty,
|
|
| 337 |
$fxsellprice, $form->{"discount_$i"},
|
|
| 338 |
$form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
|
|
| 339 |
$form->{"serialnumber_$i"}, $form->{"ship_$i"}, conv_i($pricegroup_id),
|
|
| 340 |
$form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
|
|
| 341 |
$form->{"cusordnumber_$i"}, $subtotal);
|
|
| 342 |
do_query($form, $dbh, $query, @values); |
|
| 350 | 343 |
|
| 351 | 344 |
$form->{"sellprice_$i"} = $fxsellprice;
|
| 352 | 345 |
$form->{"discount_$i"} *= 100;
|
| 353 | 346 |
} |
| 354 | 347 |
} |
| 355 | 348 |
|
| 356 |
# set values which could be empty |
|
| 357 |
map { $form->{$_} *= 1 }
|
|
| 358 |
qw(vendor_id customer_id taxincluded closed quotation); |
|
| 359 |
|
|
| 360 |
$reqdate = ($form->{reqdate}) ? qq|'$form->{reqdate}'| : "NULL";
|
|
| 349 |
$reqdate = ($form->{reqdate}) ? $form->{reqdate} : undef;
|
|
| 361 | 350 |
|
| 362 | 351 |
# add up the tax |
| 363 | 352 |
my $tax = 0; |
| ... | ... | |
| 409 | 398 |
Message: $form->{message}\r| if $form->{message};
|
| 410 | 399 |
|
| 411 | 400 |
($null, $form->{department_id}) = split(/--/, $form->{department});
|
| 412 |
$form->{department_id} *= 1;
|
|
| 413 |
$form->{payment_id} *= 1;
|
|
| 414 |
$form->{language_id} *= 1;
|
|
| 415 |
$form->{taxzone_id} *= 1;
|
|
| 416 |
$form->{proforma} *= 1;
|
|
| 417 |
|
|
| 418 |
|
|
| 419 | 401 |
|
| 420 | 402 |
# save OE record |
| 421 |
$query = qq|UPDATE oe set |
|
| 422 |
ordnumber = '$form->{ordnumber}',
|
|
| 423 |
quonumber = '$form->{quonumber}',
|
|
| 424 |
cusordnumber = '$form->{cusordnumber}',
|
|
| 425 |
transdate = '$form->{transdate}',
|
|
| 426 |
vendor_id = $form->{vendor_id},
|
|
| 427 |
customer_id = $form->{customer_id},
|
|
| 428 |
amount = $amount, |
|
| 429 |
netamount = $netamount, |
|
| 430 |
reqdate = $reqdate, |
|
| 431 |
taxincluded = '$form->{taxincluded}',
|
|
| 432 |
shippingpoint = '$form->{shippingpoint}',
|
|
| 433 |
shipvia = '$form->{shipvia}',
|
|
| 434 |
notes = '$form->{notes}',
|
|
| 435 |
intnotes = '$form->{intnotes}',
|
|
| 436 |
curr = '$form->{currency}',
|
|
| 437 |
closed = '$form->{closed}',
|
|
| 438 |
delivered = '| . ($form->{delivered} ? "t" : "f") . qq|',
|
|
| 439 |
proforma = '$form->{proforma}',
|
|
| 440 |
quotation = '$quotation', |
|
| 441 |
department_id = $form->{department_id},
|
|
| 442 |
language_id = $form->{language_id},
|
|
| 443 |
taxzone_id = $form->{taxzone_id},
|
|
| 444 |
shipto_id = $form->{shipto_id},
|
|
| 445 |
payment_id = $form->{payment_id},
|
|
| 446 |
delivery_vendor_id = $form->{delivery_vendor_id},
|
|
| 447 |
delivery_customer_id = $form->{delivery_customer_id},
|
|
| 448 |
globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
|
|
| 449 |
employee_id = $form->{employee_id},
|
|
| 450 |
salesman_id = | . conv_i($form->{salesman_id}, 'NULL') . qq|,
|
|
| 451 |
cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
|
|
| 452 |
WHERE id = $form->{id}|;
|
|
| 453 |
$dbh->do($query) || $form->dberror($query); |
|
| 403 |
$query = |
|
| 404 |
qq|UPDATE oe set | . |
|
| 405 |
qq|ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?, | . |
|
| 406 |
qq|customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?, | . |
|
| 407 |
qq|shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, curr = ?, closed = ?, | . |
|
| 408 |
qq|delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?, | . |
|
| 409 |
qq|taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?, | . |
|
| 410 |
qq|globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ? | . |
|
| 411 |
qq|WHERE id = ?|; |
|
| 412 |
|
|
| 413 |
@values = ($form->{ordnumber}, $form->{quonumber},
|
|
| 414 |
$form->{cusordnumber}, conv_date($form->{transdate}),
|
|
| 415 |
conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
|
|
| 416 |
$amount, $netamount, conv_date($reqdate), |
|
| 417 |
$form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
|
|
| 418 |
$form->{shipvia}, $form->{notes}, $form->{intnotes},
|
|
| 419 |
$form->{currency}, $form->{closed} ? 't' : 'f',
|
|
| 420 |
$form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
|
|
| 421 |
$quotation, conv_i($form->{department_id}),
|
|
| 422 |
conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
|
|
| 423 |
conv_i($form->{shipto_id}), conv_i($form->{payment_id}),
|
|
| 424 |
conv_i($form->{delivery_vendor_id}),
|
|
| 425 |
conv_i($form->{delivery_customer_id}),
|
|
| 426 |
conv_i($form->{globalproject_id}), conv_i($form->{employee_id}),
|
|
| 427 |
conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
|
|
| 428 |
conv_i($form->{id}));
|
|
| 429 |
do_query($form, $dbh, $query, @values); |
|
| 454 | 430 |
|
| 455 | 431 |
$form->{ordtotal} = $amount;
|
| 456 | 432 |
|
| ... | ... | |
| 508 | 484 |
(1 .. $form->{rowcount});
|
| 509 | 485 |
|
| 510 | 486 |
my $dbh = $form->dbconnect($myconfig); |
| 511 |
$query = qq|UPDATE oe SET |
|
| 512 |
closed = TRUE
|
|
| 513 |
WHERE ordnumber IN (|
|
|
| 487 |
$query = qq|UPDATE oe SET | .
|
|
| 488 |
qq|closed = TRUE | .
|
|
| 489 |
qq|WHERE ordnumber IN (|
|
|
| 514 | 490 |
. join(', ', map { $dbh->quote($_) } @ids) . qq|)|;
|
| 515 | 491 |
$dbh->do($query) || $form->dberror($query); |
| 516 | 492 |
$dbh->disconnect; |
| ... | ... | |
| 542 | 518 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 543 | 519 |
|
| 544 | 520 |
# delete spool files |
| 545 |
my $query = qq|SELECT s.spoolfile FROM status s |
|
| 546 |
WHERE s.trans_id = $form->{id}|;
|
|
| 521 |
my $query = qq|SELECT s.spoolfile FROM status s | . |
|
| 522 |
qq|WHERE s.trans_id = ?|; |
|
| 523 |
my @values = (conv_i($form->{id}));
|
|
| 547 | 524 |
$sth = $dbh->prepare($query); |
| 548 |
$sth->execute || $self->dberror($query); |
|
| 525 |
$sth->execute(@values) || $self->dberror($query);
|
|
| 549 | 526 |
|
| 550 | 527 |
my $spoolfile; |
| 551 | 528 |
my @spoolfiles = (); |
| ... | ... | |
| 555 | 532 |
} |
| 556 | 533 |
$sth->finish; |
| 557 | 534 |
|
| 558 |
$query = qq|SELECT o.parts_id, o.ship FROM orderitems o |
|
| 559 |
WHERE o.trans_id = $form->{id}|;
|
|
| 535 |
$query = qq|SELECT o.parts_id, o.ship FROM orderitems o | . |
|
| 536 |
qq|WHERE o.trans_id = ?|; |
|
| 537 |
@values = (conv_i($form->{id}));
|
|
| 560 | 538 |
$sth = $dbh->prepare($query); |
| 561 |
$sth->execute || $self->dberror($query); |
|
| 539 |
$sth->execute(@values) || $self->dberror($query);
|
|
| 562 | 540 |
|
| 563 | 541 |
while (my ($id, $ship) = $sth->fetchrow_array) {
|
| 564 | 542 |
$form->update_balance($dbh, "parts", "onhand", qq|id = $id|, $ship * -1); |
| 565 | 543 |
} |
| 566 | 544 |
$sth->finish; |
| 567 | 545 |
|
| 546 |
# delete-values |
|
| 547 |
@values = (conv_i($form->{id}));
|
|
| 548 |
|
|
| 568 | 549 |
# delete inventory |
| 569 |
$query = qq|DELETE FROM inventory |
|
| 570 |
WHERE oe_id = $form->{id}|;
|
|
| 571 |
$dbh->do($query) || $form->dberror($query);
|
|
| 550 |
$query = qq|DELETE FROM inventory | .
|
|
| 551 |
qq|WHERE oe_id = ?|;
|
|
| 552 |
do_query($form, $dbh, $query, @values);
|
|
| 572 | 553 |
|
| 573 | 554 |
# delete status entries |
| 574 |
$query = qq|DELETE FROM status |
|
| 575 |
WHERE trans_id = $form->{id}|;
|
|
| 576 |
$dbh->do($query) || $form->dberror($query);
|
|
| 555 |
$query = qq|DELETE FROM status | .
|
|
| 556 |
qq|WHERE trans_id = ?|;
|
|
| 557 |
do_query($form, $dbh, $query, @values);
|
|
| 577 | 558 |
|
| 578 | 559 |
# delete OE record |
| 579 |
$query = qq|DELETE FROM oe |
|
| 580 |
WHERE id = $form->{id}|;
|
|
| 581 |
$dbh->do($query) || $form->dberror($query);
|
|
| 560 |
$query = qq|DELETE FROM oe | .
|
|
| 561 |
qq|WHERE id = ?|;
|
|
| 562 |
do_query($form, $dbh, $query, @values);
|
|
| 582 | 563 |
|
| 583 | 564 |
# delete individual entries |
| 584 |
$query = qq|DELETE FROM orderitems |
|
| 585 |
WHERE trans_id = $form->{id}|;
|
|
| 586 |
$dbh->do($query) || $form->dberror($query);
|
|
| 565 |
$query = qq|DELETE FROM orderitems | .
|
|
| 566 |
qq|WHERE trans_id = ?|;
|
|
| 567 |
do_query($form, $dbh, $query, @values);
|
|
| 587 | 568 |
|
| 588 |
$query = qq|DELETE FROM shipto |
|
| 589 |
WHERE trans_id = $form->{id} AND module = 'OE'|;
|
|
| 590 |
$dbh->do($query) || $form->dberror($query);
|
|
| 569 |
$query = qq|DELETE FROM shipto | .
|
|
| 570 |
qq|WHERE trans_id = ? AND module = 'OE'|;
|
|
| 571 |
do_query($form, $dbh, $query, @values);
|
|
| 591 | 572 |
|
| 592 | 573 |
my $rc = $dbh->commit; |
| 593 | 574 |
$dbh->disconnect; |
| ... | ... | |
| 611 | 592 |
# connect to database |
| 612 | 593 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 613 | 594 |
|
| 614 |
my $query, @ids;
|
|
| 595 |
my ($query, @values, @ids);
|
|
| 615 | 596 |
|
| 616 | 597 |
# translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later |
| 617 | 598 |
map {
|
| 618 | 599 |
push @ids, $form->{"trans_id_$_"}
|
| 619 |
if ($form->{"id_$_"} and $form->{"trans_id_$_"})
|
|
| 600 |
if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"})
|
|
| 620 | 601 |
} (1 .. $form->{"rowcount"});
|
| 621 | 602 |
|
| 622 | 603 |
# if called in multi id mode, and still only got one id, switch back to single id |
| ... | ... | |
| 628 | 609 |
if ($form->{id}) {
|
| 629 | 610 |
|
| 630 | 611 |
# get default accounts and last order number |
| 631 |
$query = qq|SELECT (SELECT c.accno FROM chart c |
|
| 632 |
WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 633 |
(SELECT c.accno FROM chart c |
|
| 634 |
WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 635 |
(SELECT c.accno FROM chart c |
|
| 636 |
WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 637 |
(SELECT c.accno FROM chart c |
|
| 638 |
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 639 |
(SELECT c.accno FROM chart c |
|
| 640 |
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 641 |
d.curr AS currencies |
|
| 642 |
FROM defaults d|; |
|
| 612 |
$query = |
|
| 613 |
qq|SELECT (SELECT c.accno FROM chart c | . |
|
| 614 |
qq| WHERE d.inventory_accno_id = c.id) AS inventory_accno, | . |
|
| 615 |
qq| (SELECT c.accno FROM chart c | . |
|
| 616 |
qq| WHERE d.income_accno_id = c.id) AS income_accno, | . |
|
| 617 |
qq| (SELECT c.accno FROM chart c | . |
|
| 618 |
qq| WHERE d.expense_accno_id = c.id) AS expense_accno, | . |
|
| 619 |
qq| (SELECT c.accno FROM chart c | . |
|
| 620 |
qq| WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, | . |
|
| 621 |
qq| (SELECT c.accno FROM chart c | . |
|
| 622 |
qq| WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, | . |
|
| 623 |
qq|d.curr AS currencies | . |
|
| 624 |
qq|FROM defaults d|; |
|
| 643 | 625 |
} else {
|
| 644 |
$query = qq|SELECT (SELECT c.accno FROM chart c |
|
| 645 |
WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 646 |
(SELECT c.accno FROM chart c |
|
| 647 |
WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 648 |
(SELECT c.accno FROM chart c |
|
| 649 |
WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 650 |
(SELECT c.accno FROM chart c |
|
| 651 |
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 652 |
(SELECT c.accno FROM chart c |
|
| 653 |
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 654 |
d.curr AS currencies, |
|
| 655 |
current_date AS transdate, current_date AS reqdate |
|
| 656 |
FROM defaults d|; |
|
| 626 |
$query = |
|
| 627 |
qq|SELECT (SELECT c.accno FROM chart c | . |
|
| 628 |
qq| WHERE d.inventory_accno_id = c.id) AS inventory_accno, | . |
|
| 629 |
qq| (SELECT c.accno FROM chart c | . |
|
| 630 |
qq| WHERE d.income_accno_id = c.id) AS income_accno, | . |
|
| 631 |
qq| (SELECT c.accno FROM chart c | . |
|
| 632 |
qq| WHERE d.expense_accno_id = c.id) AS expense_accno, | . |
|
| 633 |
qq| (SELECT c.accno FROM chart c | . |
|
| 634 |
qq| WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, | . |
|
| 635 |
qq| (SELECT c.accno FROM chart c | . |
|
| 636 |
qq| WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, | . |
|
| 637 |
qq|d.curr AS currencies, | . |
|
| 638 |
qq|current_date AS transdate, current_date AS reqdate | . |
|
| 639 |
qq|FROM defaults d|; |
|
| 657 | 640 |
} |
| 658 | 641 |
my $sth = $dbh->prepare($query); |
| 659 | 642 |
$sth->execute || $form->dberror($query); |
| ... | ... | |
| 662 | 645 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| 663 | 646 |
$sth->finish; |
| 664 | 647 |
|
| 665 |
($form->{currency}) = split /:/, $form->{currencies};
|
|
| 648 |
($form->{currency}) = split(/:/, $form->{currencies});
|
|
| 666 | 649 |
|
| 667 | 650 |
# set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure |
| 668 | 651 |
# we come from invoices, feel free. |
| ... | ... | |
| 670 | 653 |
if ( $form->{deliverydate}
|
| 671 | 654 |
and $form->{callback} =~ /action=ar_transactions/);
|
| 672 | 655 |
|
| 656 |
my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
|
|
| 657 |
|
|
| 673 | 658 |
if ($form->{id} or @ids) {
|
| 674 | 659 |
|
| 675 | 660 |
# retrieve order for single id |
| 676 | 661 |
# NOTE: this query is intended to fetch all information only ONCE. |
| 677 | 662 |
# so if any of these infos is important (or even different) for any item, |
| 678 | 663 |
# it will be killed out and then has to be fetched from the item scope query further down |
| 679 |
$query = qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate, |
|
| 680 |
o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes, |
|
| 681 |
o.curr AS currency, e.name AS employee, o.employee_id, o.salesman_id, |
|
| 682 |
o.$form->{vc}_id, cv.name AS $form->{vc}, o.amount AS invtotal,
|
|
| 683 |
o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber, |
|
| 684 |
d.description AS department, o.payment_id, o.language_id, o.taxzone_id, |
|
| 685 |
o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id, |
|
| 686 |
o.globalproject_id, |
|
| 687 |
o.delivered |
|
| 688 |
FROM oe o |
|
| 689 |
JOIN $form->{vc} cv ON (o.$form->{vc}_id = cv.id)
|
|
| 690 |
LEFT JOIN employee e ON (o.employee_id = e.id) |
|
| 691 |
LEFT JOIN department d ON (o.department_id = d.id) |
|
| 692 |
| |
|
| 693 |
. ($form->{id}
|
|
| 694 |
? qq|WHERE o.id = $form->{id}|
|
|
| 695 |
: qq|WHERE o.id IN (| . join(', ', @ids) . qq|)|);
|
|
| 696 |
|
|
| 697 |
#$main::lxdebug->message(0, $query); |
|
| 698 |
|
|
| 699 |
$sth = $dbh->prepare($query); |
|
| 700 |
$sth->execute || $form->dberror($query); |
|
| 664 |
$query = |
|
| 665 |
qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate, | . |
|
| 666 |
qq| o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes, | . |
|
| 667 |
qq| o.curr AS currency, e.name AS employee, o.employee_id, o.salesman_id, | . |
|
| 668 |
qq| o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal, | .
|
|
| 669 |
qq| o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber, | . |
|
| 670 |
qq| d.description AS department, o.payment_id, o.language_id, o.taxzone_id, | . |
|
| 671 |
qq| o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id, | . |
|
| 672 |
qq| o.globalproject_id, o.delivered | . |
|
| 673 |
qq|FROM oe o | . |
|
| 674 |
qq|JOIN ${vc} cv ON (o.${vc}_id = cv.id) | .
|
|
| 675 |
qq|LEFT JOIN employee e ON (o.employee_id = e.id) | . |
|
| 676 |
qq|LEFT JOIN department d ON (o.department_id = d.id) | . |
|
| 677 |
($form->{id} ? qq|WHERE o.id = ?| :
|
|
| 678 |
qq|WHERE o.id IN (| . join(', ', map("? ", @ids)) . qq|)|);
|
|
| 679 |
@values = $form->{id} ? ($form->{id}) : @ids;
|
|
| 680 |
$sth = prepare_execute_query($form, $dbh, $query, @values); |
|
| 701 | 681 |
|
| 702 | 682 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
| 703 | 683 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| ... | ... | |
| 717 | 697 |
$sth->finish; |
| 718 | 698 |
|
| 719 | 699 |
if ($form->{delivery_customer_id}) {
|
| 720 |
$query = qq|SELECT name FROM customer WHERE id=$form->{delivery_customer_id}|;
|
|
| 721 |
$sth = $dbh->prepare($query); |
|
| 722 |
$sth->execute || $form->dberror($query); |
|
| 723 |
($form->{delivery_customer_string}) = $sth->fetchrow_array();
|
|
| 724 |
$sth->finish; |
|
| 700 |
$query = qq|SELECT name FROM customer WHERE id = ?|; |
|
| 701 |
($form->{delivery_customer_string}) =
|
|
| 702 |
selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
|
|
| 725 | 703 |
} |
| 726 | 704 |
|
| 727 | 705 |
if ($form->{delivery_vendor_id}) {
|
| 728 |
$query = qq|SELECT name FROM customer WHERE id=$form->{delivery_vendor_id}|;
|
|
| 729 |
$sth = $dbh->prepare($query); |
|
| 730 |
$sth->execute || $form->dberror($query); |
|
| 731 |
($form->{delivery_vendor_string}) = $sth->fetchrow_array();
|
|
| 732 |
$sth->finish; |
|
| 706 |
$query = qq|SELECT name FROM customer WHERE id = ?|; |
|
| 707 |
($form->{delivery_vendor_string}) =
|
|
| 708 |
selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
|
|
| 733 | 709 |
} |
| 734 | 710 |
|
| 735 | 711 |
# shipto and pinted/mailed/queued status makes only sense for single id retrieve |
| 736 | 712 |
if (!@ids) {
|
| 737 |
$query = qq|SELECT s.* FROM shipto s |
|
| 738 |
WHERE s.trans_id = $form->{id} AND s.module = 'OE'|;
|
|
| 739 |
$sth = $dbh->prepare($query); |
|
| 740 |
$sth->execute || $form->dberror($query); |
|
| 713 |
$query = qq|SELECT s.* FROM shipto s | . |
|
| 714 |
qq|WHERE s.trans_id = ? AND s.module = 'OE'|; |
|
| 715 |
$sth = prepare_execute_query($form, $dbh, $query, $form->{id});
|
|
| 741 | 716 |
|
| 742 | 717 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
| 743 | 718 |
delete($ref->{id});
|
| ... | ... | |
| 745 | 720 |
$sth->finish; |
| 746 | 721 |
|
| 747 | 722 |
# get printed, emailed and queued |
| 748 |
$query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname |
|
| 749 |
FROM status s |
|
| 750 |
WHERE s.trans_id = $form->{id}|;
|
|
| 751 |
$sth = $dbh->prepare($query); |
|
| 752 |
$sth->execute || $form->dberror($query); |
|
| 723 |
$query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname | . |
|
| 724 |
qq|FROM status s | . |
|
| 725 |
qq|WHERE s.trans_id = ?|; |
|
| 726 |
$sth = prepare_execute_query($form, $dbh, $query, $form->{id});
|
|
| 753 | 727 |
|
| 754 | 728 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 755 | 729 |
$form->{printed} .= "$ref->{formname} " if $ref->{printed};
|
| ... | ... | |
| 767 | 741 |
my $transdate = |
| 768 | 742 |
$form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
|
| 769 | 743 |
|
| 770 |
if(!$form->{taxzone_id}) {
|
|
| 771 |
$form->{taxzone_id} = 0;
|
|
| 772 |
} |
|
| 744 |
$form->{taxzone_id} = 0 unless ($form->{taxzone_id});
|
|
| 745 |
|
|
| 773 | 746 |
# retrieve individual items |
| 774 | 747 |
# this query looks up all information about the items |
| 775 | 748 |
# stuff different from the whole will not be overwritten, but saved with a suffix. |
| 776 |
$query = qq|SELECT o.id AS orderitems_id, |
|
| 777 |
c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid, |
|
| 778 |
c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid, |
|
| 779 |
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid, |
|
| 780 |
oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe, |
|
| 781 |
p.partnumber, p.assembly, o.description, o.qty, |
|
| 782 |
o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, |
|
| 783 |
o.reqdate, o.project_id, o.serialnumber, o.ship, |
|
| 784 |
o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription, |
|
| 785 |
pr.projectnumber, p.formel, |
|
| 786 |
pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup |
|
| 787 |
FROM orderitems o |
|
| 788 |
JOIN parts p ON (o.parts_id = p.id) |
|
| 789 |
JOIN oe ON (o.trans_id = oe.id) |
|
| 790 |
LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id) |
|
| 791 |
LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
|
|
| 792 |
LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
|
|
| 793 |
LEFT JOIN project pr ON (o.project_id = pr.id) |
|
| 794 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 795 |
| |
|
| 796 |
. ($form->{id}
|
|
| 797 |
? qq|WHERE o.trans_id = $form->{id}|
|
|
| 798 |
: qq|WHERE o.trans_id IN (| . join(", ", @ids) . qq|)|)
|
|
| 799 |
. qq| |
|
| 800 |
ORDER BY o.$oid{$myconfig->{dbdriver}}|;
|
|
| 801 |
|
|
| 802 |
$sth = $dbh->prepare($query); |
|
| 803 |
$sth->execute || $form->dberror($query); |
|
| 749 |
$query = |
|
| 750 |
qq|SELECT o.id AS orderitems_id, | . |
|
| 751 |
qq| c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid, | . |
|
| 752 |
qq| c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid, | . |
|
| 753 |
qq| c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid, | . |
|
| 754 |
qq| oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe, | . |
|
| 755 |
qq| p.partnumber, p.assembly, o.description, o.qty, | . |
|
| 756 |
qq| o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, | . |
|
| 757 |
qq| o.reqdate, o.project_id, o.serialnumber, o.ship, | . |
|
| 758 |
qq| o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription, | . |
|
| 759 |
qq| pr.projectnumber, p.formel, | . |
|
| 760 |
qq| pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup | . |
|
| 761 |
qq|FROM orderitems o | . |
|
| 762 |
qq|JOIN parts p ON (o.parts_id = p.id) | . |
|
| 763 |
qq|JOIN oe ON (o.trans_id = oe.id) | . |
|
| 764 |
qq|LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id) | . |
|
| 765 |
qq|LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id) | .
|
|
| 766 |
qq|LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id) | .
|
|
| 767 |
qq|LEFT JOIN project pr ON (o.project_id = pr.id) | . |
|
| 768 |
qq|LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | . |
|
| 769 |
($form->{id} ? qq|WHERE o.trans_id = ?| :
|
|
| 770 |
qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
|
|
| 771 |
qq|ORDER BY o.$oid{$myconfig->{dbdriver}}|;
|
|
| 772 |
|
|
| 773 |
@ids = $form->{id} ? ($form->{id}) : @ids;
|
|
| 774 |
$sth = prepare_execute_query($form, $dbh, $query, @values); |
|
| 804 | 775 |
|
| 805 | 776 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 806 | 777 |
if (!$ref->{"part_inventory_accno_id"}) {
|
| ... | ... | |
| 818 | 789 |
|
| 819 | 790 |
|
| 820 | 791 |
|
| 821 |
while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
|
|
| 822 |
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}|;
|
|
| 823 |
my $stw = $dbh->prepare($query); |
|
| 824 |
$stw->execute || $form->dberror($query); |
|
| 825 |
($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
|
|
| 826 |
$stw->finish; |
|
| 827 |
} |
|
| 792 |
while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >= 0)) {
|
|
| 793 |
my $query = |
|
| 794 |
qq|SELECT accno AS inventory_accno, | . |
|
| 795 |
qq| new_chart_id AS inventory_new_chart, | . |
|
| 796 |
qq| date($transdate) - valid_from AS inventory_valid | . |
|
| 797 |
qq|FROM chart WHERE id = $ref->{inventory_new_chart}|;
|
|
| 798 |
($ref->{inventory_accno}, $ref->{inventory_new_chart},
|
|
| 799 |
$ref->{inventory_valid}) = selectrow_query($form, $dbh, $query);
|
|
| 800 |
} |
|
| 828 | 801 |
|
| 829 |
while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
|
|
| 830 |
my $query = qq| SELECT accno AS income_accno, new_chart_id AS income_new_chart, date($transdate) - valid_from AS income_valid FROM chart WHERE id = $ref->{income_new_chart}|;
|
|
| 831 |
my $stw = $dbh->prepare($query); |
|
| 832 |
$stw->execute || $form->dberror($query); |
|
| 833 |
($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
|
|
| 834 |
$stw->finish; |
|
| 835 |
} |
|
| 802 |
while ($ref->{income_new_chart} && ($ref->{income_valid} >= 0)) {
|
|
| 803 |
my $query = |
|
| 804 |
qq|SELECT accno AS income_accno, | . |
|
| 805 |
qq| new_chart_id AS income_new_chart, | . |
|
| 806 |
qq| date($transdate) - valid_from AS income_valid | . |
|
| 807 |
qq|FROM chart WHERE id = $ref->{income_new_chart}|;
|
|
| 808 |
($ref->{income_accno}, $ref->{income_new_chart},
|
|
| 809 |
$ref->{income_valid}) = selectrow_query($form, $dbh, $query);
|
|
| 810 |
} |
|
| 836 | 811 |
|
| 837 |
while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
|
|
| 838 |
my $query = qq| SELECT accno AS expense_accno, new_chart_id AS expense_new_chart, date($transdate) - valid_from AS expense_valid FROM chart WHERE id = $ref->{expense_new_chart}|;
|
|
| 839 |
my $stw = $dbh->prepare($query); |
|
| 840 |
$stw->execute || $form->dberror($query); |
|
| 841 |
($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
|
|
| 842 |
$stw->finish; |
|
| 843 |
} |
|
| 812 |
while ($ref->{expense_new_chart} && ($ref->{expense_valid} >= 0)) {
|
|
| 813 |
my $query = |
|
| 814 |
qq|SELECT accno AS expense_accno, | . |
|
| 815 |
qq| new_chart_id AS expense_new_chart, | . |
|
| 816 |
qq| date($transdate) - valid_from AS expense_valid | . |
|
| 817 |
qq|FROM chart WHERE id = $ref->{expense_new_chart}|;
|
|
| 818 |
($ref->{expense_accno}, $ref->{expense_new_chart},
|
|
| 819 |
$ref->{expense_valid}) = selectrow_query($form, $dbh, $query);
|
|
| 820 |
} |
|
| 844 | 821 |
|
| 845 | 822 |
# delete orderitems_id in collective orders, so that they get cloned no matter what |
| 846 | 823 |
delete $ref->{orderitems_id} if (@ids);
|
| ... | ... | |
| 848 | 825 |
# get tax rates and description |
| 849 | 826 |
$accno_id = |
| 850 | 827 |
($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
|
| 851 |
$query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber |
|
| 852 |
FROM tax t LEFT JOIN chart c on (c.id=t.chart_id) |
|
| 853 |
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) |
|
| 854 |
ORDER BY c.accno|; |
|
| 855 |
$stw = $dbh->prepare($query); |
|
| 856 |
$stw->execute || $form->dberror($query); |
|
| 828 |
$query = |
|
| 829 |
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | . |
|
| 830 |
qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | . |
|
| 831 |
qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | . |
|
| 832 |
qq| WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | . |
|
| 833 |
qq| AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | . |
|
| 834 |
qq|ORDER BY c.accno|; |
|
| 835 |
$stw = prepare_execute_query($form, $dbh, $query, $accno_id); |
|
| 857 | 836 |
$ref->{taxaccounts} = "";
|
| 858 | 837 |
my $i = 0; |
| 859 | 838 |
while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
|
| 860 |
|
|
| 861 |
# if ($customertax{$ref->{accno}}) {
|
|
| 862 | 839 |
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
|
| 863 | 840 |
$i++; |
| 864 | 841 |
$ptr->{accno} = $i;
|
| ... | ... | |
| 894 | 871 |
Common::webdav_folder($form) if ($main::webdav); |
| 895 | 872 |
|
| 896 | 873 |
# get tax zones |
| 897 |
$query = qq|SELECT id, description |
|
| 898 |
FROM tax_zones|; |
|
| 899 |
$sth = $dbh->prepare($query); |
|
| 900 |
$sth->execute || $form->dberror($query); |
|
| 901 |
|
|
| 902 |
|
|
| 903 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 904 |
push @{ $form->{TAXZONE} }, $ref;
|
|
| 905 |
} |
|
| 906 |
$sth->finish; |
|
| 907 |
|
|
| 874 |
$query = qq|SELECT id, description FROM tax_zones|; |
|
| 875 |
$form->{TAXZONE} = selectall_hashref_query($form, $dbh, $query);
|
|
| 908 | 876 |
|
| 909 | 877 |
my $rc = $dbh->commit; |
| 910 | 878 |
$dbh->disconnect; |
| ... | ... | |
| 922 | 890 |
# connect to database |
| 923 | 891 |
my $dbh = $form->dbconnect($myconfig); |
| 924 | 892 |
my $query; |
| 893 |
my @values = (); |
|
| 925 | 894 |
my $sth; |
| 926 | 895 |
my $nodiscount; |
| 927 | 896 |
my $yesdiscount; |
| ... | ... | |
| 954 | 923 |
|
| 955 | 924 |
if (@project_ids) {
|
| 956 | 925 |
$query = "SELECT id, projectnumber FROM project WHERE id IN (" .
|
| 957 |
join(", ", map({ "?" } @project_ids)) . ")";
|
|
| 958 |
$sth = $dbh->prepare($query); |
|
| 959 |
$sth->execute(@project_ids) || |
|
| 960 |
$form->dberror($query . " (" . join(", ", @project_ids) . ")");
|
|
| 926 |
join(", ", map("?", @project_ids)) . ")";
|
|
| 927 |
$sth = prepare_execute_query($form, $dbh, $query, @project_ids); |
|
| 961 | 928 |
while (my $ref = $sth->fetchrow_hashref()) {
|
| 962 | 929 |
$projectnumbers{$ref->{id}} = $ref->{projectnumber};
|
| 963 | 930 |
} |
| ... | ... | |
| 1093 | 1060 |
my ($taxamount, $taxbase); |
| 1094 | 1061 |
my $taxrate = 0; |
| 1095 | 1062 |
|
| 1096 |
map { $taxrate += $form->{"${_}_rate"} } split / /,
|
|
| 1097 |
$form->{"taxaccounts_$i"};
|
|
| 1063 |
map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
|
|
| 1098 | 1064 |
|
| 1099 | 1065 |
if ($form->{taxincluded}) {
|
| 1100 | 1066 |
|
| ... | ... | |
| 1129 | 1095 |
$sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
|
| 1130 | 1096 |
} |
| 1131 | 1097 |
|
| 1132 |
$query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, |
|
| 1133 |
pg.partsgroup
|
|
| 1134 |
FROM assembly a
|
|
| 1135 |
JOIN parts p ON (a.parts_id = p.id)
|
|
| 1136 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
|
| 1137 |
WHERE a.bom = '1'
|
|
| 1138 |
AND a.id = '$form->{"id_$i"}'
|
|
| 1139 |
$sortorder|;
|
|
| 1098 |
$query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | .
|
|
| 1099 |
qq|pg.partsgroup | .
|
|
| 1100 |
qq|FROM assembly a | .
|
|
| 1101 |
qq| JOIN parts p ON (a.parts_id = p.id) | .
|
|
| 1102 |
qq| LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | .
|
|
| 1103 |
qq| WHERE a.bom = '1' | .
|
|
| 1104 |
qq| AND a.id = ? | . $sortorder;
|
|
| 1105 |
@values = ($form->{"id_$i"});
|
|
| 1140 | 1106 |
$sth = $dbh->prepare($query); |
| 1141 |
$sth->execute || $form->dberror($query); |
|
| 1107 |
$sth->execute(@values) || $form->dberror($query);
|
|
| 1142 | 1108 |
|
| 1143 | 1109 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 1144 | 1110 |
if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
|
| ... | ... | |
| 1208 | 1174 |
|
| 1209 | 1175 |
my ($self, $dbh, $id) = @_; |
| 1210 | 1176 |
|
| 1211 |
my $query = qq|SELECT p.description |
|
| 1212 |
FROM project p |
|
| 1213 |
WHERE p.id = $id|; |
|
| 1214 |
my $sth = $dbh->prepare($query); |
|
| 1215 |
$sth->execute || $form->dberror($query); |
|
| 1216 |
|
|
| 1217 |
($_) = $sth->fetchrow_array; |
|
| 1218 |
|
|
| 1219 |
$sth->finish; |
|
| 1177 |
my $query = qq|SELECT description FROM project WHERE id = ?|; |
|
| 1178 |
my ($value) = selectrow_query($form, $dbh, $query, $id); |
|
| 1220 | 1179 |
|
| 1221 | 1180 |
$main::lxdebug->leave_sub(); |
| 1222 | 1181 |
|
| 1223 |
return $_;
|
|
| 1182 |
return $value;
|
|
| 1224 | 1183 |
} |
| 1225 | 1184 |
|
| 1226 | 1185 |
sub adj_onhand {
|
| ... | ... | |
| 1228 | 1187 |
|
| 1229 | 1188 |
my ($dbh, $form, $ml) = @_; |
| 1230 | 1189 |
|
| 1231 |
my $service_units = $form->{service_units};
|
|
| 1232 |
my $part_units = $form->{part_units};
|
|
| 1190 |
my $all_units = $form->{all_units};
|
|
| 1233 | 1191 |
|
| 1234 |
my $query = qq|SELECT oi.parts_id, oi.ship, oi.unit, p.inventory_accno_id, p.assembly |
|
| 1235 |
FROM orderitems oi |
|
| 1236 |
JOIN parts p ON (p.id = oi.parts_id) |
|
| 1237 |
WHERE oi.trans_id = $form->{id}|;
|
|
| 1192 |
my $query = |
|
| 1193 |
qq|SELECT oi.parts_id, oi.ship, oi.unit, p.inventory_accno_id, p.assembly | . |
|
| 1194 |
qq| FROM orderitems oi | . |
|
| 1195 |
qq| JOIN parts p ON (p.id = oi.parts_id) | . |
|
| 1196 |
qq| WHERE oi.trans_id = ?|; |
|
| 1197 |
my @values = ($form->{id});
|
|
| 1238 | 1198 |
my $sth = $dbh->prepare($query); |
| 1239 |
$sth->execute || $form->dberror($query); |
|
| 1199 |
$sth->execute(@values) || $form->dberror($query);
|
|
| 1240 | 1200 |
|
| 1241 |
$query = qq|SELECT sum(p.inventory_accno_id) |
|
| 1242 |
FROM parts p |
|
| 1243 |
JOIN assembly a ON (a.parts_id = p.id) |
|
| 1244 |
WHERE a.id = ?|; |
|
| 1201 |
$query = |
|
| 1202 |
qq|SELECT sum(p.inventory_accno_id) | . |
|
| 1203 |
qq|FROM parts p | . |
|
| 1204 |
qq|JOIN assembly a ON (a.parts_id = p.id) | . |
|
| 1205 |
qq|WHERE a.id = ?|; |
|
| 1245 | 1206 |
my $ath = $dbh->prepare($query) || $form->dberror($query); |
| 1246 | 1207 |
|
| 1247 | 1208 |
my $ispa; |
| 1248 | 1209 |
|
| 1249 | 1210 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 1250 |
#print(STDERR "Bin in Schleife $ref->{inventory_accno_id}\n");
|
|
| 1251 |
|
|
| 1252 | 1211 |
if ($ref->{inventory_accno_id} || $ref->{assembly}) {
|
| 1253 | 1212 |
|
| 1254 | 1213 |
# do not update if assembly consists of all services |
| ... | ... | |
| 1263 | 1222 |
} |
| 1264 | 1223 |
|
| 1265 | 1224 |
# get item baseunit |
| 1266 |
$query = qq|SELECT p.unit |
|
| 1267 |
FROM parts p |
|
| 1268 |
WHERE p.id = $ref->{parts_id}|;
|
|
| 1269 |
my $stw = $dbh->prepare($query); |
|
| 1270 |
$stw->execute || $form->dberror($query); |
|
| 1271 |
|
|
| 1272 |
my ($item_unit) = $stw->fetchrow_array(); |
|
| 1273 |
$stw->finish; |
|
| 1274 |
|
|
| 1275 |
if ($ref->{inventory_accno_id}) {
|
|
| 1276 |
if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
|
|
| 1277 |
$basefactor = $part_units->{$ref->{unit}}->{factor} / $part_units->{$item_unit}->{factor};
|
|
| 1278 |
} else {
|
|
| 1279 |
$basefactor = 1; |
|
| 1280 |
} |
|
| 1281 |
$baseqty = $ref->{ship} * $basefactor;
|
|
| 1282 |
} else {
|
|
| 1283 |
if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
|
|
| 1284 |
$basefactor = $service_units->{$ref->{unit}}->{factor} / $part_units->{$item_unit}->{factor};
|
|
| 1285 |
} else {
|
|
| 1286 |
$basefactor = 1; |
|
| 1287 |
} |
|
| 1288 |
$baseqty = $ref->{ship} * $basefactor;
|
|
| 1289 |
} |
|
| 1290 |
#print(STDERR "$baseqty Basismenge\n"); |
|
| 1225 |
$query = qq|SELECT unit FROM parts WHERE id = ?|; |
|
| 1226 |
my ($item_unit) = selectrow_query($form, $dbh, $query, $ref->{parts_id});
|
|
| 1227 |
|
|
| 1228 |
my $basefactor = 1; |
|
| 1229 |
if (defined($all_units->{$item_unit}->{factor}) &&
|
|
| 1230 |
(($all_units->{$item_unit}->{factor} * 1) != 0)) {
|
|
| 1231 |
$basefactor = $all_units->{$ref->{unit}}->{factor} /
|
|
| 1232 |
$all_units->{$item_unit}->{factor};
|
|
| 1233 |
} |
|
| 1234 |
my $baseqty = $ref->{ship} * $basefactor;
|
|
| 1291 | 1235 |
|
| 1292 | 1236 |
# adjust onhand in parts table |
| 1293 | 1237 |
$form->update_balance($dbh, "parts", "onhand", |
| ... | ... | |
| 1302 | 1246 |
} |
| 1303 | 1247 |
|
| 1304 | 1248 |
1; |
| 1305 |
|
|
Auch abrufbar als: Unified diff
OE.pm: Keine Daten aus $form direkt in SQL-Anfragen einsetzen und statt dessen parametrisierte Anfragen benutzen. Außerdem einen SQL-Fehler behoben, wenn mehrere Auftrage zu einem zusammengefasst werden. Behebt Bug #609.