Revision e840d786
Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt
| SL/IR.pm | ||
|---|---|---|
| 46 | 46 |
# connect to database, turn off autocommit |
| 47 | 47 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 48 | 48 |
|
| 49 |
my ($query, $sth, $null, $project_id); |
|
| 49 |
my ($query, $sth, @values, $project_id); |
|
| 50 |
my ($allocated, $taxrate, $taxamount, $taxdiff, $item); |
|
| 51 |
my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno); |
|
| 52 |
my ($netamount, $invoicediff, $expensediff) = (0, 0, 0); |
|
| 50 | 53 |
my $exchangerate = 0; |
| 51 |
my $allocated; |
|
| 52 |
my $taxrate; |
|
| 53 |
my $taxamount; |
|
| 54 |
my $taxdiff; |
|
| 55 |
my $item; |
|
| 56 | 54 |
|
| 57 | 55 |
my $all_units = AM->retrieve_units($myconfig, $form); |
| 58 | 56 |
|
| ... | ... | |
| 61 | 59 |
&reverse_invoice($dbh, $form); |
| 62 | 60 |
|
| 63 | 61 |
} else {
|
| 64 |
my $uid = rand() . time; |
|
| 65 |
|
|
| 66 |
$uid .= $form->{login};
|
|
| 67 |
|
|
| 68 |
$uid = substr($uid, 2, 75); |
|
| 69 |
|
|
| 70 |
$query = qq|INSERT INTO ap (invnumber, employee_id) |
|
| 71 |
VALUES ('$uid', '$form->{employee_id}')|;
|
|
| 72 |
$dbh->do($query) || $form->dberror($query); |
|
| 73 |
|
|
| 74 |
$query = qq|SELECT a.id FROM ap a |
|
| 75 |
WHERE a.invnumber = '$uid'|; |
|
| 76 |
$sth = $dbh->prepare($query); |
|
| 77 |
$sth->execute || $form->dberror($query); |
|
| 62 |
($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);
|
|
| 78 | 63 |
|
| 79 |
($form->{id}) = $sth->fetchrow_array;
|
|
| 80 |
$sth->finish; |
|
| 64 |
do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber) VALUES (?, '')|, $form->{id});
|
|
| 81 | 65 |
} |
| 82 | 66 |
|
| 83 |
map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
|
|
| 84 |
|
|
| 85 |
my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno); |
|
| 86 |
my ($netamount, $invoicediff, $expensediff) = (0, 0, 0); |
|
| 87 |
|
|
| 88 | 67 |
if ($form->{currency} eq $form->{defaultcurrency}) {
|
| 89 | 68 |
$form->{exchangerate} = 1;
|
| 90 | 69 |
} else {
|
| ... | ... | |
| 100 | 79 |
|
| 101 | 80 |
$form->{exchangerate} = 1 unless ($form->{exchangerate} * 1);
|
| 102 | 81 |
|
| 82 |
my %item_units; |
|
| 83 |
my $q_item_unit = qq|SELECT unit FROM parts WHERE id = ?|; |
|
| 84 |
my $h_item_unit = prepare_query($form, $dbh, $q_item_unit); |
|
| 85 |
|
|
| 103 | 86 |
for my $i (1 .. $form->{rowcount}) {
|
| 87 |
next unless $form->{"id_$i"};
|
|
| 88 |
|
|
| 104 | 89 |
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
|
| 105 |
|
|
| 90 |
|
|
| 106 | 91 |
if ($form->{storno}) {
|
| 107 | 92 |
$form->{"qty_$i"} *= -1;
|
| 108 | 93 |
} |
| ... | ... | |
| 110 | 95 |
if ($main::eur) {
|
| 111 | 96 |
$form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"};
|
| 112 | 97 |
} |
| 113 |
|
|
| 114 |
if ($form->{"id_$i"}) {
|
|
| 115 |
|
|
| 116 |
# get item baseunit |
|
| 117 |
$query = qq|SELECT p.unit |
|
| 118 |
FROM parts p |
|
| 119 |
WHERE p.id = $form->{"id_$i"}|;
|
|
| 120 |
$sth = $dbh->prepare($query); |
|
| 121 |
$sth->execute || $form->dberror($query); |
|
| 122 |
|
|
| 123 |
my ($item_unit) = $sth->fetchrow_array(); |
|
| 124 |
$sth->finish; |
|
| 125 |
|
|
| 126 |
if (defined($all_units->{$item_unit}->{factor})
|
|
| 127 |
&& ($all_units->{$item_unit}->{factor} ne '')
|
|
| 128 |
&& ($all_units->{$item_unit}->{factor} * 1 != 0)) {
|
|
| 129 |
$basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
|
|
| 130 |
} else {
|
|
| 131 |
$basefactor = 1; |
|
| 132 |
} |
|
| 133 |
$baseqty = $form->{"qty_$i"} * $basefactor;
|
|
| 134 | 98 |
|
| 135 |
map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
|
|
| 136 |
qw(partnumber description unit); |
|
| 99 |
# get item baseunit |
|
| 100 |
if (!$item_units{$form->{"id_$i"}}) {
|
|
| 101 |
do_statement($form, $h_item_unit, $q_item_unit, $form->{"id_$i"});
|
|
| 102 |
($item_units{$form->{"id_$i"}}) = $h_item_unit->fetchrow_array();
|
|
| 103 |
} |
|
| 137 | 104 |
|
| 138 |
@taxaccounts = split / /, $form->{"taxaccounts_$i"};
|
|
| 139 |
$taxdiff = 0; |
|
| 140 |
$allocated = 0; |
|
| 141 |
$taxrate = 0; |
|
| 105 |
my $item_unit = $item_units{$form->{"id_$i"}};
|
|
| 142 | 106 |
|
| 143 |
$form->{"sellprice_$i"} =
|
|
| 144 |
$form->parse_amount($myconfig, $form->{"sellprice_$i"});
|
|
| 145 |
my $fxsellprice = $form->{"sellprice_$i"};
|
|
| 107 |
if (defined($all_units->{$item_unit}->{factor})
|
|
| 108 |
&& ($all_units->{$item_unit}->{factor} ne '')
|
|
| 109 |
&& ($all_units->{$item_unit}->{factor} * 1 != 0)) {
|
|
| 110 |
$basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
|
|
| 111 |
} else {
|
|
| 112 |
$basefactor = 1; |
|
| 113 |
} |
|
| 114 |
$baseqty = $form->{"qty_$i"} * $basefactor;
|
|
| 146 | 115 |
|
| 147 |
my ($dec) = ($fxsellprice =~ /\.(\d+)/); |
|
| 148 |
$dec = length $dec; |
|
| 149 |
my $decimalplaces = ($dec > 2) ? $dec : 2; |
|
| 116 |
@taxaccounts = split / /, $form->{"taxaccounts_$i"};
|
|
| 117 |
$taxdiff = 0; |
|
| 118 |
$allocated = 0; |
|
| 119 |
$taxrate = 0; |
|
| 150 | 120 |
|
| 151 |
map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
|
|
| 121 |
$form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
|
|
| 122 |
my $fxsellprice = $form->{"sellprice_$i"};
|
|
| 152 | 123 |
|
| 153 |
if ($form->{"inventory_accno_$i"}) {
|
|
| 124 |
my ($dec) = ($fxsellprice =~ /\.(\d+)/); |
|
| 125 |
$dec = length $dec; |
|
| 126 |
my $decimalplaces = ($dec > 2) ? $dec : 2; |
|
| 154 | 127 |
|
| 155 |
$linetotal = |
|
| 156 |
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
|
| 128 |
map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
|
|
| 157 | 129 |
|
| 158 |
if ($form->{taxincluded}) {
|
|
| 159 |
$taxamount = $linetotal * ($taxrate / (1 + $taxrate)); |
|
| 160 |
$form->{"sellprice_$i"} =
|
|
| 161 |
$form->{"sellprice_$i"} * (1 / (1 + $taxrate));
|
|
| 162 |
} else {
|
|
| 163 |
$taxamount = $linetotal * $taxrate; |
|
| 164 |
} |
|
| 130 |
if ($form->{"inventory_accno_$i"}) {
|
|
| 165 | 131 |
|
| 166 |
$netamount += $linetotal;
|
|
| 167 |
|
|
| 168 |
if ($form->round_amount($taxrate, 7) == 0) {
|
|
| 169 |
if ($form->{taxincluded}) {
|
|
| 170 |
foreach $item (@taxaccounts) {
|
|
| 171 |
$taxamount =
|
|
| 172 |
$form->round_amount($linetotal * $form->{"${item}_rate"} /
|
|
| 173 |
(1 + abs($form->{"${item}_rate"})),
|
|
| 174 |
2); |
|
| 175 |
$taxdiff += $taxamount;
|
|
| 176 |
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
|
| 177 |
}
|
|
| 178 |
$form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
|
|
| 179 |
} else {
|
|
| 180 |
map {
|
|
| 181 |
$form->{amount}{ $form->{id} }{$_} -=
|
|
| 182 |
$linetotal * $form->{"${_}_rate"}
|
|
| 183 |
} @taxaccounts;
|
|
| 132 |
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
|
| 133 |
|
|
| 134 |
if ($form->{taxincluded}) {
|
|
| 135 |
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
|
|
| 136 |
$form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
|
|
| 137 |
} else {
|
|
| 138 |
$taxamount = $linetotal * $taxrate;
|
|
| 139 |
}
|
|
| 140 |
|
|
| 141 |
$netamount += $linetotal;
|
|
| 142 |
|
|
| 143 |
if ($form->round_amount($taxrate, 7) == 0) {
|
|
| 144 |
if ($form->{taxincluded}) {
|
|
| 145 |
foreach $item (@taxaccounts) {
|
|
| 146 |
$taxamount =
|
|
| 147 |
$form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
|
|
| 148 |
$taxdiff += $taxamount;
|
|
| 149 |
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
|
| 184 | 150 |
} |
| 151 |
$form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
|
|
| 152 |
|
|
| 185 | 153 |
} else {
|
| 186 |
map {
|
|
| 187 |
$form->{amount}{ $form->{id} }{$_} -=
|
|
| 188 |
$taxamount * $form->{"${_}_rate"} / $taxrate
|
|
| 189 |
} @taxaccounts; |
|
| 154 |
map { $form->{amount}{ $form->{id} }{$_} -= $linetotal * $form->{"${_}_rate"} } @taxaccounts;
|
|
| 190 | 155 |
} |
| 191 | 156 |
|
| 192 |
# add purchase to inventory, this one is without the tax! |
|
| 193 |
$amount = |
|
| 194 |
$form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
|
|
| 195 |
$linetotal = |
|
| 196 |
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
|
|
| 197 |
$form->{exchangerate};
|
|
| 198 |
$linetotal = $form->round_amount($linetotal, 2); |
|
| 199 |
|
|
| 200 |
# this is the difference for the inventory |
|
| 201 |
$invoicediff += ($amount - $linetotal); |
|
| 202 |
|
|
| 203 |
$form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
|
|
| 204 |
$linetotal; |
|
| 205 |
|
|
| 206 |
# adjust and round sellprice |
|
| 207 |
$form->{"sellprice_$i"} =
|
|
| 208 |
$form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
|
|
| 209 |
$decimalplaces); |
|
| 210 |
|
|
| 211 |
# update parts table |
|
| 212 |
$query = qq|UPDATE parts SET |
|
| 213 |
lastcost = $form->{"sellprice_$i"}
|
|
| 214 |
WHERE id = $form->{"id_$i"}|;
|
|
| 215 |
|
|
| 216 |
$dbh->do($query) || $form->dberror($query); |
|
| 217 |
|
|
| 218 |
$form->update_balance($dbh, "parts", "onhand", qq|id = ?|, |
|
| 219 |
$baseqty, $form->{"id_$i"})
|
|
| 220 |
unless $form->{shipped};
|
|
| 221 |
|
|
| 222 |
# check if we sold the item already and |
|
| 223 |
# make an entry for the expense and inventory |
|
| 224 |
$query = qq|SELECT i.id, i.qty, i.allocated, i.trans_id, |
|
| 225 |
p.inventory_accno_id, p.expense_accno_id, a.transdate |
|
| 226 |
FROM invoice i, ar a, parts p |
|
| 227 |
WHERE i.parts_id = p.id |
|
| 228 |
AND i.parts_id = $form->{"id_$i"}
|
|
| 229 |
AND (i.base_qty + i.allocated) > 0 |
|
| 230 |
AND i.trans_id = a.id |
|
| 231 |
ORDER BY transdate|; |
|
| 232 |
$sth = $dbh->prepare($query); |
|
| 233 |
$sth->execute || $form->dberror($query); |
|
| 234 |
|
|
| 235 |
my $totalqty = $base_qty; |
|
| 236 |
|
|
| 237 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 238 |
|
|
| 239 |
my $qty = $ref->{base_qty} + $ref->{allocated};
|
|
| 240 |
|
|
| 241 |
if (($qty - $totalqty) > 0) {
|
|
| 242 |
$qty = $totalqty; |
|
| 243 |
} |
|
| 157 |
} else {
|
|
| 158 |
map { $form->{amount}{ $form->{id} }{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } @taxaccounts;
|
|
| 159 |
} |
|
| 244 | 160 |
|
| 245 |
$linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
|
|
| 246 |
|
|
| 247 |
if ($ref->{allocated} < 0) {
|
|
| 248 |
|
|
| 249 |
# we have an entry for it already, adjust amount |
|
| 250 |
$form->update_balance( |
|
| 251 |
$dbh, |
|
| 252 |
"acc_trans", |
|
| 253 |
"amount", |
|
| 254 |
qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$ref->{transdate}'|,
|
|
| 255 |
$linetotal); |
|
| 256 |
|
|
| 257 |
$form->update_balance( |
|
| 258 |
$dbh, |
|
| 259 |
"acc_trans", |
|
| 260 |
"amount", |
|
| 261 |
qq|trans_id = $ref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$ref->{transdate}'|,
|
|
| 262 |
$linetotal * -1); |
|
| 263 |
|
|
| 264 |
} else {
|
|
| 265 |
|
|
| 266 |
# add entry for inventory, this one is for the sold item |
|
| 267 |
if ($linetotal != 0) {
|
|
| 268 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 269 |
transdate, taxkey) |
|
| 270 |
VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
|
|
| 271 |
$linetotal, '$ref->{transdate}', (SELECT taxkey_id FROM chart WHERE id = $ref->{inventory_accno_id}))|;
|
|
| 272 |
$dbh->do($query) || $form->dberror($query); |
|
| 273 |
|
|
| 274 |
# add expense |
|
| 275 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 276 |
transdate, taxkey) |
|
| 277 |
VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
|
|
| 278 |
| . ($linetotal * -1) . qq|, '$ref->{transdate}',
|
|
| 279 |
(SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
|
|
| 280 |
$dbh->do($query) || $form->dberror($query); |
|
| 281 |
} |
|
| 282 |
} |
|
| 161 |
# add purchase to inventory, this one is without the tax! |
|
| 162 |
$amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
|
|
| 163 |
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) * $form->{exchangerate};
|
|
| 164 |
$linetotal = $form->round_amount($linetotal, 2); |
|
| 283 | 165 |
|
| 284 |
# update allocated for sold item |
|
| 285 |
$form->update_balance($dbh, "invoice", "allocated", |
|
| 286 |
qq|id = $ref->{id}|,
|
|
| 287 |
$qty * -1); |
|
| 166 |
# this is the difference for the inventory |
|
| 167 |
$invoicediff += ($amount - $linetotal); |
|
| 288 | 168 |
|
| 289 |
$allocated += $qty;
|
|
| 169 |
$form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -= $linetotal;
|
|
| 290 | 170 |
|
| 291 |
last if (($totalqty -= $qty) <= 0); |
|
| 292 |
} |
|
| 171 |
# adjust and round sellprice |
|
| 172 |
$form->{"sellprice_$i"} =
|
|
| 173 |
$form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
|
|
| 293 | 174 |
|
| 294 |
$sth->finish; |
|
| 175 |
# update parts table |
|
| 176 |
$query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|; |
|
| 177 |
@values = ($form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
|
|
| 178 |
do_query($form, $dbh, $query, @values); |
|
| 295 | 179 |
|
| 296 |
$lastinventoryaccno = $form->{"inventory_accno_$i"};
|
|
| 180 |
if (!$form->{shipped}) {
|
|
| 181 |
$form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $baseqty, $form->{"id_$i"})
|
|
| 182 |
} |
|
| 297 | 183 |
|
| 298 |
} else {
|
|
| 184 |
# check if we sold the item already and |
|
| 185 |
# make an entry for the expense and inventory |
|
| 186 |
$query = |
|
| 187 |
qq|SELECT i.id, i.qty, i.allocated, i.trans_id, |
|
| 188 |
p.inventory_accno_id, p.expense_accno_id, a.transdate |
|
| 189 |
FROM invoice i, ar a, parts p |
|
| 190 |
WHERE (i.parts_id = p.id) |
|
| 191 |
AND (i.parts_id = ?) |
|
| 192 |
AND ((i.base_qty + i.allocated) > 0) |
|
| 193 |
AND (i.trans_id = a.id) |
|
| 194 |
ORDER BY transdate|; |
|
| 195 |
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
|
|
| 299 | 196 |
|
| 300 |
$linetotal = |
|
| 301 |
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
|
| 197 |
my $totalqty = $base_qty; |
|
| 302 | 198 |
|
| 303 |
if ($form->{taxincluded}) {
|
|
| 304 |
$taxamount = $linetotal * ($taxrate / (1 + $taxrate)); |
|
| 199 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 305 | 200 |
|
| 306 |
$form->{"sellprice_$i"} =
|
|
| 307 |
$form->{"sellprice_$i"} * (1 / (1 + $taxrate));
|
|
| 308 |
} else {
|
|
| 309 |
$taxamount = $linetotal * $taxrate;
|
|
| 201 |
my $qty = $ref->{base_qty} + $ref->{allocated};
|
|
| 202 |
|
|
| 203 |
if (($qty - $totalqty) > 0) {
|
|
| 204 |
$qty = $totalqty;
|
|
| 310 | 205 |
} |
| 311 | 206 |
|
| 312 |
$netamount += $linetotal; |
|
| 313 |
|
|
| 314 |
if ($form->round_amount($taxrate, 7) == 0) {
|
|
| 315 |
if ($form->{taxincluded}) {
|
|
| 316 |
foreach $item (@taxaccounts) {
|
|
| 317 |
$taxamount = |
|
| 318 |
$linetotal * $form->{"${item}_rate"} /
|
|
| 319 |
(1 + abs($form->{"${item}_rate"}));
|
|
| 320 |
$totaltax += $taxamount; |
|
| 321 |
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
|
| 322 |
} |
|
| 323 |
} else {
|
|
| 324 |
map {
|
|
| 325 |
$form->{amount}{ $form->{id} }{$_} -=
|
|
| 326 |
$linetotal * $form->{"${_}_rate"}
|
|
| 327 |
} @taxaccounts; |
|
| 328 |
} |
|
| 329 |
} else {
|
|
| 330 |
map {
|
|
| 331 |
$form->{amount}{ $form->{id} }{$_} -=
|
|
| 332 |
$taxamount * $form->{"${_}_rate"} / $taxrate
|
|
| 333 |
} @taxaccounts; |
|
| 207 |
$linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
|
|
| 208 |
|
|
| 209 |
if ($ref->{allocated} < 0) {
|
|
| 210 |
|
|
| 211 |
# we have an entry for it already, adjust amount |
|
| 212 |
$form->update_balance($dbh, "acc_trans", "amount", |
|
| 213 |
qq| (trans_id = $ref->{trans_id})
|
|
| 214 |
AND (chart_id = $ref->{inventory_accno_id})
|
|
| 215 |
AND (transdate = '$ref->{transdate}')|,
|
|
| 216 |
$linetotal); |
|
| 217 |
|
|
| 218 |
$form->update_balance($dbh, "acc_trans", "amount", |
|
| 219 |
qq| (trans_id = $ref->{trans_id})
|
|
| 220 |
AND (chart_id = $ref->{expense_accno_id})
|
|
| 221 |
AND (transdate = '$ref->{transdate}')|,
|
|
| 222 |
$linetotal * -1); |
|
| 223 |
|
|
| 224 |
} elsif ($linetotal != 0) {
|
|
| 225 |
# add entry for inventory, this one is for the sold item |
|
| 226 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) |
|
| 227 |
VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?))|; |
|
| 228 |
@values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal,
|
|
| 229 |
$ref->{transdate}, $ref->{inventory_accno_id});
|
|
| 230 |
do_query($form, $dbh, $query, @values); |
|
| 231 |
|
|
| 232 |
# add expense |
|
| 233 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) |
|
| 234 |
VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?))|; |
|
| 235 |
@values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1),
|
|
| 236 |
$ref->{transdate}, $ref->{expense_accno_id});
|
|
| 237 |
do_query($form, $dbh, $query, @values); |
|
| 334 | 238 |
} |
| 335 | 239 |
|
| 336 |
$amount = |
|
| 337 |
$form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
|
|
| 338 |
$linetotal = |
|
| 339 |
$form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
|
|
| 340 |
$form->{exchangerate};
|
|
| 341 |
$linetotal = $form->round_amount($linetotal, 2); |
|
| 240 |
# update allocated for sold item |
|
| 241 |
$form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1);
|
|
| 242 |
|
|
| 243 |
$allocated += $qty; |
|
| 342 | 244 |
|
| 343 |
# this is the difference for expense
|
|
| 344 |
$expensediff += ($amount - $linetotal);
|
|
| 245 |
last if (($totalqty -= $qty) <= 0);
|
|
| 246 |
}
|
|
| 345 | 247 |
|
| 346 |
# add amount to expense |
|
| 347 |
$form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
|
|
| 348 |
$linetotal; |
|
| 248 |
$sth->finish(); |
|
| 349 | 249 |
|
| 350 |
$lastexpenseaccno = $form->{"expense_accno_$i"};
|
|
| 250 |
$lastinventoryaccno = $form->{"inventory_accno_$i"};
|
|
| 351 | 251 |
|
| 352 |
# adjust and round sellprice |
|
| 353 |
$form->{"sellprice_$i"} =
|
|
| 354 |
$form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
|
|
| 355 |
$decimalplaces); |
|
| 252 |
} else {
|
|
| 356 | 253 |
|
| 357 |
# update lastcost |
|
| 358 |
$query = qq|UPDATE parts SET |
|
| 359 |
lastcost = $form->{"sellprice_$i"}
|
|
| 360 |
WHERE id = $form->{"id_$i"}|;
|
|
| 254 |
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
|
|
| 361 | 255 |
|
| 362 |
$dbh->do($query) || $form->dberror($query); |
|
| 256 |
if ($form->{taxincluded}) {
|
|
| 257 |
$taxamount = $linetotal * ($taxrate / (1 + $taxrate)); |
|
| 258 |
$form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
|
|
| 363 | 259 |
|
| 260 |
} else {
|
|
| 261 |
$taxamount = $linetotal * $taxrate; |
|
| 364 | 262 |
} |
| 365 | 263 |
|
| 366 |
$deliverydate = |
|
| 367 |
($form->{"deliverydate_$i"})
|
|
| 368 |
? qq|'$form->{"deliverydate_$i"}'|
|
|
| 369 |
: "NULL"; |
|
| 370 |
|
|
| 371 |
# save detail record in invoice table |
|
| 372 |
$query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty, |
|
| 373 |
sellprice, fxsellprice, allocated, unit, deliverydate, |
|
| 374 |
project_id, serialnumber) |
|
| 375 |
VALUES ($form->{id}, $form->{"id_$i"},
|
|
| 376 |
'$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
|
|
| 377 |
$form->{"sellprice_$i"}, $fxsellprice, $allocated,
|
|
| 378 |
'$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
|
|
| 379 |
'$form->{"serialnumber_$i"}')|;
|
|
| 380 |
$dbh->do($query) || $form->dberror($query); |
|
| 264 |
$netamount += $linetotal; |
|
| 265 |
|
|
| 266 |
if ($form->round_amount($taxrate, 7) == 0) {
|
|
| 267 |
if ($form->{taxincluded}) {
|
|
| 268 |
foreach $item (@taxaccounts) {
|
|
| 269 |
$taxamount = |
|
| 270 |
$linetotal * $form->{"${item}_rate"}
|
|
| 271 |
/ (1 + abs($form->{"${item}_rate"}));
|
|
| 272 |
$totaltax += $taxamount; |
|
| 273 |
$form->{amount}{ $form->{id} }{$item} -= $taxamount;
|
|
| 274 |
} |
|
| 275 |
|
|
| 276 |
} else {
|
|
| 277 |
map { $form->{amount}{ $form->{id} }{$_} -= $linetotal * $form->{"${_}_rate"} } @taxaccounts;
|
|
| 278 |
} |
|
| 279 |
|
|
| 280 |
} else {
|
|
| 281 |
map { $form->{amount}{ $form->{id} }{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } @taxaccounts;
|
|
| 282 |
} |
|
| 283 |
|
|
| 284 |
$amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
|
|
| 285 |
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) * $form->{exchangerate};
|
|
| 286 |
$linetotal = $form->round_amount($linetotal, 2); |
|
| 287 |
|
|
| 288 |
# this is the difference for expense |
|
| 289 |
$expensediff += ($amount - $linetotal); |
|
| 290 |
|
|
| 291 |
# add amount to expense |
|
| 292 |
$form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -= $linetotal;
|
|
| 293 |
|
|
| 294 |
$lastexpenseaccno = $form->{"expense_accno_$i"};
|
|
| 295 |
|
|
| 296 |
# adjust and round sellprice |
|
| 297 |
$form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
|
|
| 298 |
|
|
| 299 |
# update lastcost |
|
| 300 |
$query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|; |
|
| 301 |
do_query($form, $dbh, $query, $form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
|
|
| 381 | 302 |
} |
| 303 |
|
|
| 304 |
# save detail record in invoice table |
|
| 305 |
$query = |
|
| 306 |
qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty, |
|
| 307 |
sellprice, fxsellprice, allocated, unit, deliverydate, |
|
| 308 |
project_id, serialnumber) |
|
| 309 |
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; |
|
| 310 |
@values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
|
|
| 311 |
$form->{"description_$i"}, $form->{"qty_$i"} * -1,
|
|
| 312 |
$baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $allocated,
|
|
| 313 |
$form->{"unit_$i"}, conv_date($form->{deliverydate}),
|
|
| 314 |
conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"});
|
|
| 315 |
do_query($form, $dbh, $query, @values); |
|
| 382 | 316 |
} |
| 383 | 317 |
|
| 384 |
$project_id = conv_i($form->{"globalproject_id"});
|
|
| 318 |
$h_item_unit->finish(); |
|
| 319 |
|
|
| 320 |
my $project_id = conv_i($form->{"globalproject_id"});
|
|
| 385 | 321 |
|
| 386 | 322 |
$form->{datepaid} = $form->{invdate};
|
| 387 | 323 |
|
| ... | ... | |
| 391 | 327 |
|
| 392 | 328 |
# total payments |
| 393 | 329 |
for my $i (1 .. $form->{paidaccounts}) {
|
| 394 |
$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
|
|
| 395 |
$form->{paid} += $form->{"paid_$i"};
|
|
| 396 |
$form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
|
|
| 330 |
$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
|
|
| 331 |
$form->{paid} += $form->{"paid_$i"};
|
|
| 332 |
$form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
|
|
| 397 | 333 |
} |
| 398 | 334 |
|
| 399 | 335 |
my ($tax, $paiddiff) = (0, 0); |
| ... | ... | |
| 408 | 344 |
$netamount = $amount; |
| 409 | 345 |
|
| 410 | 346 |
foreach $item (split / /, $form->{taxaccounts}) {
|
| 411 |
$amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
|
|
| 347 |
$amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
|
|
| 412 | 348 |
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
|
| 413 |
$amount = $form->{amount}{ $form->{id} }{$item} * -1;
|
|
| 414 |
$tax += $amount; |
|
| 349 |
|
|
| 350 |
$amount = $form->{amount}{ $form->{id} }{$item} * -1;
|
|
| 351 |
$tax += $amount; |
|
| 415 | 352 |
$netamount -= $amount; |
| 416 | 353 |
} |
| 417 | 354 |
|
| ... | ... | |
| 430 | 367 |
$amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
|
| 431 | 368 |
$paiddiff = $amount - $netamount * $form->{exchangerate};
|
| 432 | 369 |
$netamount = $amount; |
| 370 |
|
|
| 433 | 371 |
foreach my $item (split / /, $form->{taxaccounts}) {
|
| 434 |
$form->{amount}{ $form->{id} }{$item} =
|
|
| 435 |
$form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
|
|
| 436 |
$amount = |
|
| 437 |
$form->round_amount( |
|
| 438 |
$form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
|
|
| 439 |
2); |
|
| 440 |
$paiddiff += |
|
| 441 |
$amount - $form->{amount}{ $form->{id} }{$item} *
|
|
| 442 |
$form->{exchangerate} * -1;
|
|
| 443 |
$form->{amount}{ $form->{id} }{$item} =
|
|
| 444 |
$form->round_amount($amount * -1, 2); |
|
| 445 |
$amount = $form->{amount}{ $form->{id} }{$item} * -1;
|
|
| 446 |
$tax += $amount; |
|
| 372 |
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
|
|
| 373 |
$amount = $form->round_amount( $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1, 2);
|
|
| 374 |
$paiddiff += $amount - $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1;
|
|
| 375 |
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount * -1, 2);
|
|
| 376 |
$amount = $form->{amount}{ $form->{id} }{$item} * -1;
|
|
| 377 |
$tax += $amount; |
|
| 447 | 378 |
} |
| 448 | 379 |
} |
| 449 | 380 |
|
| 450 | 381 |
$form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
|
| 451 | 382 |
|
| 452 | 383 |
if ($form->{paid} != 0) {
|
| 453 |
$form->{paid} =
|
|
| 454 |
$form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
|
|
| 455 |
2); |
|
| 384 |
$form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2);
|
|
| 456 | 385 |
} |
| 457 | 386 |
|
| 458 | 387 |
# update exchangerate |
| 459 | 388 |
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
| 460 |
$form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
|
|
| 461 |
$form->{exchangerate});
|
|
| 389 |
$form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate});
|
|
| 462 | 390 |
} |
| 463 | 391 |
|
| 464 | 392 |
# record acc_trans transactions |
| 465 | 393 |
foreach my $trans_id (keys %{ $form->{amount} }) {
|
| 466 | 394 |
foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
|
| 467 |
if ( |
|
| 468 |
($form->{amount}{$trans_id}{$accno} =
|
|
| 469 |
$form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
|
|
| 470 |
) != 0 |
|
| 471 |
) {
|
|
| 472 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 473 |
transdate, taxkey, project_id) |
|
| 474 |
VALUES ($trans_id, (SELECT c.id FROM chart c |
|
| 475 |
WHERE c.accno = '$accno'), |
|
| 476 |
$form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
|
|
| 477 |
(SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|; |
|
| 478 |
do_query($form, $dbh, $query, $project_id); |
|
| 479 |
} |
|
| 395 |
$form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
|
|
| 396 |
next unless $form->{amount}{$trans_id}{$accno};
|
|
| 397 |
|
|
| 398 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) |
|
| 399 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
|
| 400 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 401 |
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
|
|
| 402 |
conv_date($form->{invdate}), $accno, $project_id);
|
|
| 403 |
do_query($form, $dbh, $query, @values); |
|
| 480 | 404 |
} |
| 481 | 405 |
} |
| 482 | 406 |
|
| 483 | 407 |
# deduct payment differences from paiddiff |
| 484 | 408 |
for my $i (1 .. $form->{paidaccounts}) {
|
| 485 | 409 |
if ($form->{"paid_$i"} != 0) {
|
| 486 |
$amount = |
|
| 487 |
$form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
|
|
| 410 |
$amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
|
|
| 488 | 411 |
$paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
|
| 489 | 412 |
} |
| 490 | 413 |
} |
| 491 | 414 |
|
| 492 | 415 |
# force AP entry if 0 |
| 493 |
$form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
|
|
| 494 |
if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0);
|
|
| 416 |
if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 0) {
|
|
| 417 |
$form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid};
|
|
| 418 |
} |
|
| 495 | 419 |
|
| 496 | 420 |
# record payments and offsetting AP |
| 497 | 421 |
for my $i (1 .. $form->{paidaccounts}) {
|
| 422 |
next if ($form->{"paid_$i"} == 0);
|
|
| 423 |
|
|
| 424 |
my ($accno) = split /--/, $form->{"AP_paid_$i"};
|
|
| 425 |
$form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"});
|
|
| 426 |
$form->{datepaid} = $form->{"datepaid_$i"};
|
|
| 427 |
|
|
| 428 |
$amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2) * -1;
|
|
| 429 |
|
|
| 430 |
# record AP |
|
| 431 |
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
|
|
| 432 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) |
|
| 433 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
|
| 434 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 435 |
@values = (conv_i($form->{id}), $form->{AP}, $amount,
|
|
| 436 |
$form->{"datepaid_$i"}, $form->{AP}, $project_id);
|
|
| 437 |
do_query($form, $dbh, $query, @values); |
|
| 438 |
} |
|
| 498 | 439 |
|
| 499 |
if ($form->{"paid_$i"} != 0) {
|
|
| 500 |
my ($accno) = split /--/, $form->{"AP_paid_$i"};
|
|
| 501 |
$form->{"datepaid_$i"} = $form->{invdate}
|
|
| 502 |
unless ($form->{"datepaid_$i"});
|
|
| 503 |
$form->{datepaid} = $form->{"datepaid_$i"};
|
|
| 504 |
|
|
| 505 |
$amount = ( |
|
| 506 |
$form->round_amount( |
|
| 507 |
$form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
|
|
| 508 |
) |
|
| 509 |
) * -1; |
|
| 510 |
|
|
| 511 |
# record AP |
|
| 512 |
|
|
| 513 |
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
|
|
| 514 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 515 |
transdate, taxkey, project_id) |
|
| 516 |
VALUES ($form->{id}, (SELECT c.id FROM chart c
|
|
| 517 |
WHERE c.accno = ?), |
|
| 518 |
$amount, '$form->{"datepaid_$i"}',
|
|
| 519 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 520 |
do_query($form, $dbh, $query, $form->{AP}, $form->{AP}, $project_id);
|
|
| 521 |
} |
|
| 522 |
|
|
| 523 |
# record payment |
|
| 440 |
# record payment |
|
| 441 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id) |
|
| 442 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, |
|
| 443 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 444 |
@values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
|
|
| 445 |
$form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
|
|
| 446 |
do_query($form, $dbh, $query, @values); |
|
| 447 |
|
|
| 448 |
$exchangerate = 0; |
|
| 449 |
|
|
| 450 |
if ($form->{currency} eq $form->{defaultcurrency}) {
|
|
| 451 |
$form->{"exchangerate_$i"} = 1;
|
|
| 452 |
} else {
|
|
| 453 |
$exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'sell');
|
|
| 454 |
|
|
| 455 |
$form->{"exchangerate_$i"} =
|
|
| 456 |
($exchangerate) |
|
| 457 |
? $exchangerate |
|
| 458 |
: $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
|
|
| 459 |
} |
|
| 524 | 460 |
|
| 525 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 526 |
source, memo, taxkey, project_id) |
|
| 527 |
VALUES ($form->{id}, (SELECT c.id FROM chart c
|
|
| 528 |
WHERE c.accno = ?), |
|
| 529 |
$form->{"paid_$i"}, '$form->{"datepaid_$i"}',
|
|
| 530 |
'$form->{"source_$i"}', '$form->{"memo_$i"}',
|
|
| 531 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 532 |
do_query($form, $dbh, $query, $accno, $accno, $project_id); |
|
| 461 |
# exchangerate difference |
|
| 462 |
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
|
|
| 463 |
$form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
|
|
| 533 | 464 |
|
| 534 |
$exchangerate = 0; |
|
| 465 |
# gain/loss |
|
| 466 |
$amount = |
|
| 467 |
($form->{"paid_$i"} * $form->{exchangerate}) -
|
|
| 468 |
($form->{"paid_$i"} * $form->{"exchangerate_$i"});
|
|
| 469 |
if ($amount > 0) {
|
|
| 470 |
$form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
|
|
| 535 | 471 |
|
| 536 |
if ($form->{currency} eq $form->{defaultcurrency}) {
|
|
| 537 |
$form->{"exchangerate_$i"} = 1;
|
|
| 538 |
} else {
|
|
| 539 |
$exchangerate = |
|
| 540 |
$form->check_exchangerate($myconfig, $form->{currency},
|
|
| 541 |
$form->{"datepaid_$i"}, 'sell');
|
|
| 542 |
|
|
| 543 |
$form->{"exchangerate_$i"} =
|
|
| 544 |
($exchangerate) |
|
| 545 |
? $exchangerate |
|
| 546 |
: $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
|
|
| 547 |
} |
|
| 548 |
|
|
| 549 |
# exchangerate difference |
|
| 550 |
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
|
|
| 551 |
$form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
|
|
| 552 |
|
|
| 553 |
# gain/loss |
|
| 554 |
$amount = |
|
| 555 |
($form->{"paid_$i"} * $form->{exchangerate}) -
|
|
| 556 |
($form->{"paid_$i"} * $form->{"exchangerate_$i"});
|
|
| 557 |
if ($amount > 0) {
|
|
| 558 |
$form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
|
|
| 559 |
$amount; |
|
| 560 |
} else {
|
|
| 561 |
$form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
|
|
| 562 |
$amount; |
|
| 563 |
} |
|
| 472 |
} else {
|
|
| 473 |
$form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
|
|
| 474 |
} |
|
| 564 | 475 |
|
| 565 |
$paiddiff = 0;
|
|
| 476 |
$paiddiff = 0; |
|
| 566 | 477 |
|
| 567 |
# update exchange rate |
|
| 568 |
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
|
| 569 |
$form->update_exchangerate($dbh, $form->{currency},
|
|
| 570 |
$form->{"datepaid_$i"},
|
|
| 571 |
0, $form->{"exchangerate_$i"});
|
|
| 572 |
} |
|
| 478 |
# update exchange rate |
|
| 479 |
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
|
| 480 |
$form->update_exchangerate($dbh, $form->{currency},
|
|
| 481 |
$form->{"datepaid_$i"},
|
|
| 482 |
0, $form->{"exchangerate_$i"});
|
|
| 573 | 483 |
} |
| 574 | 484 |
} |
| 575 | 485 |
|
| 576 | 486 |
# record exchange rate differences and gains/losses |
| 577 | 487 |
foreach my $accno (keys %{ $form->{fx} }) {
|
| 578 | 488 |
foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
|
| 579 |
if ( |
|
| 580 |
($form->{fx}{$accno}{$transdate} =
|
|
| 581 |
$form->round_amount($form->{fx}{$accno}{$transdate}, 2)
|
|
| 582 |
) != 0 |
|
| 583 |
) {
|
|
| 584 |
|
|
| 585 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 586 |
transdate, cleared, fx_transaction, taxkey, project_id) |
|
| 587 |
VALUES ($form->{id}, (SELECT c.id FROM chart c
|
|
| 588 |
WHERE c.accno = '$accno'), |
|
| 589 |
$form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', 0, ?)|;
|
|
| 590 |
do_query($form, $dbh, $query, $project_id); |
|
| 591 |
} |
|
| 489 |
$form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
|
|
| 490 |
next if ($form->{fx}{$accno}{$transdate} == 0);
|
|
| 491 |
|
|
| 492 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id) |
|
| 493 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?)|; |
|
| 494 |
@values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id);
|
|
| 495 |
do_query($form, $dbh, $query, @values); |
|
| 592 | 496 |
} |
| 593 | 497 |
} |
| 594 | 498 |
|
| 595 | 499 |
$amount = $netamount + $tax; |
| 596 | 500 |
|
| 597 | 501 |
# set values which could be empty |
| 598 |
$form->{taxincluded} *= 1;
|
|
| 599 |
my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
|
|
| 600 |
my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
|
|
| 601 |
|
|
| 602 |
($null, $form->{department_id}) = split(/--/, $form->{department});
|
|
| 603 |
$form->{department_id} *= 1;
|
|
| 604 |
$form->{payment_id} *= 1;
|
|
| 605 |
$form->{language_id} *= 1;
|
|
| 606 |
$form->{taxzone_id} *= 1;
|
|
| 607 |
$form->{storno} *= 1;
|
|
| 502 |
my $taxzone_id = $form->{taxzone_id} * 1;
|
|
| 503 |
$form->{department_id} = (split /--/, $form->{department})[1];
|
|
| 504 |
$form->{invnumber} = $form->{id} unless $form->{invnumber};
|
|
| 608 | 505 |
|
| 609 |
|
|
| 610 |
$form->{invnumber} = $form->{id} unless $form->{invnumber};
|
|
| 506 |
$taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id)); |
|
| 611 | 507 |
|
| 612 | 508 |
# save AP record |
| 613 |
$query = qq|UPDATE ap set |
|
| 614 |
invnumber = '$form->{invnumber}',
|
|
| 615 |
ordnumber = '$form->{ordnumber}',
|
|
| 616 |
quonumber = '$form->{quonumber}',
|
|
| 617 |
transdate = '$form->{invdate}',
|
|
| 618 |
orddate = | . conv_dateq($form->{"orddate"}) . qq|,
|
|
| 619 |
quodate = | . conv_dateq($form->{"quodate"}) . qq|,
|
|
| 620 |
vendor_id = $form->{vendor_id},
|
|
| 621 |
amount = $amount, |
|
| 622 |
netamount = $netamount, |
|
| 623 |
paid = $form->{paid},
|
|
| 624 |
datepaid = $datepaid, |
|
| 625 |
duedate = $duedate, |
|
| 626 |
invoice = '1', |
|
| 627 |
taxzone_id = '$form->{taxzone_id}',
|
|
| 628 |
taxincluded = '$form->{taxincluded}',
|
|
| 629 |
notes = '$form->{notes}',
|
|
| 630 |
intnotes = '$form->{intnotes}',
|
|
| 631 |
curr = '$form->{currency}',
|
|
| 632 |
department_id = $form->{department_id},
|
|
| 633 |
storno = '$form->{storno}',
|
|
| 634 |
globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
|
|
| 635 |
cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
|
|
| 636 |
WHERE id = $form->{id}|;
|
|
| 637 |
$dbh->do($query) || $form->dberror($query); |
|
| 509 |
$query = qq|UPDATE ap SET |
|
| 510 |
invnumber = ?, |
|
| 511 |
ordnumber = ?, |
|
| 512 |
quonumber = ?, |
|
| 513 |
transdate = ?, |
|
| 514 |
orddate = ?, |
|
| 515 |
quodate = ?, |
|
| 516 |
vendor_id = ?, |
|
| 517 |
amount = ?, |
|
| 518 |
netamount = ?, |
|
| 519 |
paid = ?, |
|
| 520 |
datepaid = ?, |
|
| 521 |
duedate = ?, |
|
| 522 |
invoice = '1', |
|
| 523 |
taxzone_id = ?, |
|
| 524 |
taxincluded = ?, |
|
| 525 |
notes = ?, |
|
| 526 |
intnotes = ?, |
|
| 527 |
curr = ?, |
|
| 528 |
department_id = ?, |
|
| 529 |
storno = ?, |
|
| 530 |
globalproject_id = ?, |
|
| 531 |
cp_id = ?, |
|
| 532 |
employee_id = ? |
|
| 533 |
WHERE id = ?|; |
|
| 534 |
@values = ($form->{invnumber}, $form->{ordnumber}, $form->{quonumber},
|
|
| 535 |
conv_date($form->{invdate}), conv_date($form->{orddate}), conv_date($form->{quodate}),
|
|
| 536 |
conv_i($form->{vendor_id}), $amount, $netamount, $form->{paid},
|
|
| 537 |
$form->{paid} ? conv_date($form->{datepaid}) : undef,
|
|
| 538 |
conv_date($form->{duedate}), $taxzone_id,
|
|
| 539 |
$form->{taxincluded} ? 't' : 'f',
|
|
| 540 |
$form->{notes}, $form->{intnotes}, $form->{currency}, conv_i($form->{department_id}),
|
|
| 541 |
$form->{storno} ? 't' : 'f',
|
|
| 542 |
conv_i($form->{globalproject_id}), conv_i($form->{cp_id}),
|
|
| 543 |
conv_i($form->{employee_id}),
|
|
| 544 |
conv_i($form->{id}));
|
|
| 545 |
do_query($form, $dbh, $query, @values); |
|
| 638 | 546 |
|
| 639 | 547 |
if ($form->{storno}) {
|
| 640 |
$query = qq| UPDATE ap SET paid = paid+amount WHERE id = $form->{storno_id}|;
|
|
| 641 |
$dbh->do($query) || $form->dberror($query); |
|
| 642 |
$query = qq| UPDATE ap SET storno = '$form->{storno}' WHERE id = $form->{storno_id}|;
|
|
| 643 |
$dbh->do($query) || $form->dberror($query); |
|
| 644 |
$query = qq? UPDATE ap SET intnotes = 'Rechnung storniert am $form->{invdate} ' || intnotes WHERE id = $form->{storno_id}?;
|
|
| 645 |
$dbh->do($query) || $form->dberror($query); |
|
| 646 |
|
|
| 647 |
$query = qq| UPDATE ap SET paid = amount WHERE id = $form->{id}|;
|
|
| 648 |
$dbh->do($query) || $form->dberror($query); |
|
| 548 |
$query = qq|UPDATE ap SET paid = paid + amount WHERE id = ?|; |
|
| 549 |
do_query($form, $dbh, $query, conv_i($form->{storno_id}));
|
|
| 550 |
|
|
| 551 |
$query = qq|UPDATE ap SET storno = 't' WHERE id = ?|; |
|
| 552 |
do_query($form, $dbh, $query, conv_i($form->{storno_id}));
|
|
| 553 |
|
|
| 554 |
$query = qq!UPDATE ap SET intnotes = ? || intnotes WHERE id = ?!; |
|
| 555 |
do_query($form, $dbh, $query, 'Rechnung storniert am $form->{invdate} ', conv_i($form->{storno_id}));
|
|
| 556 |
|
|
| 557 |
$query = qq|UPDATE ap SET paid = amount WHERE id = ?|; |
|
| 558 |
do_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 649 | 559 |
} |
| 650 | 560 |
|
| 651 | 561 |
|
| ... | ... | |
| 655 | 565 |
$form->add_shipto($dbh, $form->{id}, "AP");
|
| 656 | 566 |
|
| 657 | 567 |
# delete zero entries |
| 658 |
$query = qq|DELETE FROM acc_trans |
|
| 659 |
WHERE amount = 0|; |
|
| 660 |
$dbh->do($query) || $form->dberror($query); |
|
| 568 |
do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE amount = 0|); |
|
| 661 | 569 |
|
| 662 | 570 |
Common::webdav_folder($form) if ($main::webdav); |
| 663 | 571 |
|
| ... | ... | |
| 675 | 583 |
my ($dbh, $form) = @_; |
| 676 | 584 |
|
| 677 | 585 |
# reverse inventory items |
| 678 |
my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id, |
|
| 679 |
i.qty, i.allocated, i.sellprice |
|
| 680 |
FROM invoice i, parts p |
|
| 681 |
WHERE i.parts_id = p.id |
|
| 682 |
AND i.trans_id = $form->{id}|;
|
|
| 683 |
my $sth = $dbh->prepare($query); |
|
| 684 |
$sth->execute || $form->dberror($query); |
|
| 586 |
my $query = |
|
| 587 |
qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id, i.qty, i.allocated, i.sellprice |
|
| 588 |
FROM invoice i, parts p |
|
| 589 |
WHERE (i.parts_id = p.id) |
|
| 590 |
AND (i.trans_id = ?)|; |
|
| 591 |
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 685 | 592 |
|
| 686 | 593 |
my $netamount = 0; |
| 687 | 594 |
|
| 688 | 595 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 689 | 596 |
$netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
|
| 690 | 597 |
|
| 691 |
if ($ref->{inventory_accno_id}) {
|
|
| 692 |
|
|
| 693 |
# update onhand |
|
| 694 |
$form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|,
|
|
| 695 |
$ref->{qty});
|
|
| 696 |
|
|
| 697 |
# if $ref->{allocated} > 0 than we sold that many items
|
|
| 698 |
if ($ref->{allocated} > 0) {
|
|
| 699 |
|
|
| 700 |
# get references for sold items |
|
| 701 |
$query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate |
|
| 702 |
FROM invoice i, ar a |
|
| 703 |
WHERE i.parts_id = $ref->{parts_id}
|
|
| 704 |
AND i.allocated < 0 |
|
| 705 |
AND i.trans_id = a.id |
|
| 706 |
ORDER BY transdate DESC|; |
|
| 707 |
my $sth = $dbh->prepare($query); |
|
| 708 |
$sth->execute || $form->dberror($query); |
|
| 709 |
|
|
| 710 |
while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 711 |
my $qty = $ref->{allocated};
|
|
| 712 |
if (($ref->{allocated} + $pthref->{allocated}) > 0) {
|
|
| 713 |
$qty = $pthref->{allocated} * -1;
|
|
| 714 |
} |
|
| 598 |
next unless $ref->{inventory_accno_id};
|
|
| 715 | 599 |
|
| 716 |
my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
|
|
| 600 |
# update onhand |
|
| 601 |
$form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|, $ref->{qty});
|
|
| 717 | 602 |
|
| 718 |
#adjust allocated |
|
| 719 |
$form->update_balance($dbh, "invoice", "allocated", |
|
| 720 |
qq|id = $pthref->{id}|, $qty);
|
|
| 603 |
# if $ref->{allocated} > 0 than we sold that many items
|
|
| 604 |
next if ($ref->{allocated} <= 0);
|
|
| 721 | 605 |
|
| 722 |
$form->update_balance( |
|
| 723 |
$dbh, |
|
| 724 |
"acc_trans", |
|
| 725 |
"amount", |
|
| 726 |
qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
|
|
| 727 |
$amount); |
|
| 606 |
# get references for sold items |
|
| 607 |
$query = |
|
| 608 |
qq|SELECT i.id, i.trans_id, i.allocated, a.transdate |
|
| 609 |
FROM invoice i, ar a |
|
| 610 |
WHERE (i.parts_id = ?) |
|
| 611 |
AND (i.allocated < 0) |
|
| 612 |
AND (i.trans_id = a.id) |
|
| 613 |
ORDER BY transdate DESC|; |
|
| 614 |
my $sth2 = prepare_execute_query($form, $dbh, $query, $ref->{parts_id});
|
|
| 728 | 615 |
|
| 729 |
$form->update_balance( |
|
| 730 |
$dbh, |
|
| 731 |
"acc_trans", |
|
| 732 |
"amount", |
|
| 733 |
qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
|
|
| 734 |
$amount * -1); |
|
| 735 |
|
|
| 736 |
last if (($ref->{allocated} -= $qty) <= 0);
|
|
| 616 |
while (my $pthref = $sth2->fetchrow_hashref(NAME_lc)) {
|
|
| 617 |
my $qty = $ref->{allocated};
|
|
| 618 |
if (($ref->{allocated} + $pthref->{allocated}) > 0) {
|
|
| 619 |
$qty = $pthref->{allocated} * -1;
|
|
| 737 | 620 |
} |
| 738 |
$sth->finish; |
|
| 621 |
|
|
| 622 |
my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
|
|
| 623 |
|
|
| 624 |
#adjust allocated |
|
| 625 |
$form->update_balance($dbh, "invoice", "allocated", qq|id = $pthref->{id}|, $qty);
|
|
| 626 |
|
|
| 627 |
$form->update_balance($dbh, "acc_trans", "amount", |
|
| 628 |
qq| (trans_id = $pthref->{trans_id})
|
|
| 629 |
AND (chart_id = $ref->{expense_accno_id})
|
|
| 630 |
AND (transdate = '$pthref->{transdate}')|,
|
|
| 631 |
$amount); |
|
| 632 |
|
|
| 633 |
$form->update_balance($dbh, "acc_trans", "amount", |
|
| 634 |
qq| (trans_id = $pthref->{trans_id})
|
|
| 635 |
AND (chart_id = $ref->{inventory_accno_id})
|
|
| 636 |
AND (transdate = '$pthref->{transdate}')|,
|
|
| 637 |
$amount * -1); |
|
| 638 |
|
|
| 639 |
last if (($ref->{allocated} -= $qty) <= 0);
|
|
| 739 | 640 |
} |
| 740 |
}
|
|
| 641 |
$sth2->finish();
|
|
| 741 | 642 |
} |
| 742 |
$sth->finish; |
|
| 643 |
$sth->finish(); |
|
| 644 |
|
|
| 645 |
my $id = conv_i($form->{id});
|
|
| 743 | 646 |
|
| 744 | 647 |
# delete acc_trans |
| 745 |
$query = qq|DELETE FROM acc_trans |
|
| 746 |
WHERE trans_id = $form->{id}|;
|
|
| 747 |
$dbh->do($query) || $form->dberror($query); |
|
| 648 |
$query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; |
|
| 649 |
do_query($form, $dbh, $query, $id); |
|
| 748 | 650 |
|
| 749 | 651 |
# delete invoice entries |
| 750 |
$query = qq|DELETE FROM invoice |
|
| 751 |
WHERE trans_id = $form->{id}|;
|
|
| 752 |
$dbh->do($query) || $form->dberror($query); |
|
| 652 |
$query = qq|DELETE FROM invoice WHERE trans_id = ?|; |
|
| 653 |
do_query($form, $dbh, $query, $id); |
|
| 753 | 654 |
|
| 754 |
$query = qq|DELETE FROM shipto |
|
| 755 |
WHERE trans_id = $form->{id} AND module = 'AP'|;
|
|
| 756 |
$dbh->do($query) || $form->dberror($query); |
|
| 655 |
$query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|; |
|
| 656 |
do_query($form, $dbh, $query, $id); |
|
| 757 | 657 |
|
| 758 | 658 |
$main::lxdebug->leave_sub(); |
| 759 | 659 |
} |
| ... | ... | |
| 769 | 669 |
&reverse_invoice($dbh, $form); |
| 770 | 670 |
|
| 771 | 671 |
# delete zero entries |
| 772 |
my $query = qq|DELETE FROM acc_trans |
|
| 773 |
WHERE amount = 0|; |
|
| 774 |
$dbh->do($query) || $form->dberror($query); |
|
| 672 |
my $query = qq|DELETE FROM acc_trans WHERE amount = 0|; |
|
| 673 |
do_query($form, $dbh, $query); |
|
| 775 | 674 |
|
| 776 | 675 |
# delete AP record |
| 777 |
my $query = qq|DELETE FROM ap |
|
| 778 |
WHERE id = $form->{id}|;
|
|
| 779 |
$dbh->do($query) || $form->dberror($query); |
|
| 676 |
my $query = qq|DELETE FROM ap WHERE id = $form->{id}|;
|
|
| 677 |
do_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 780 | 678 |
|
| 781 | 679 |
my $rc = $dbh->commit; |
| 782 | 680 |
$dbh->disconnect; |
| ... | ... | |
| 792 | 690 |
my ($self, $myconfig, $form) = @_; |
| 793 | 691 |
|
| 794 | 692 |
# connect to database |
| 795 |
my $dbh = $form->dbconnect_noauto($myconfig);
|
|
| 693 |
my $dbh = $form->dbconnect($myconfig); |
|
| 796 | 694 |
|
| 797 |
my $query;
|
|
| 695 |
my ($query, $sth, $ref, $q_invdate);
|
|
| 798 | 696 |
|
| 799 |
if ($form->{id}) {
|
|
| 697 |
$q_invdate = ", current_date AS invdate" unless $form->{id};
|
|
| 800 | 698 |
|
| 801 |
# get default accounts and last invoice number |
|
| 802 |
$query = qq|SELECT (SELECT c.accno FROM chart c |
|
| 803 |
WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 804 |
(SELECT c.accno FROM chart c |
|
| 805 |
WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 806 |
(SELECT c.accno FROM chart c |
|
| 807 |
WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 808 |
(SELECT c.accno FROM chart c |
|
| 809 |
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 810 |
(SELECT c.accno FROM chart c |
|
| 811 |
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 812 |
d.curr AS currencies |
|
| 813 |
FROM defaults d|; |
|
| 814 |
} else {
|
|
| 815 |
$query = qq|SELECT (SELECT c.accno FROM chart c |
|
| 816 |
WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 817 |
(SELECT c.accno FROM chart c |
|
| 818 |
WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 819 |
(SELECT c.accno FROM chart c |
|
| 820 |
WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 821 |
(SELECT c.accno FROM chart c |
|
| 822 |
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 823 |
(SELECT c.accno FROM chart c |
|
| 824 |
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 825 |
d.curr AS currencies, |
|
| 826 |
current_date AS invdate |
|
| 827 |
FROM defaults d|; |
|
| 828 |
} |
|
| 829 |
my $sth = $dbh->prepare($query); |
|
| 830 |
$sth->execute || $form->dberror($query); |
|
| 699 |
# get default accounts and last invoice number |
|
| 831 | 700 |
|
| 832 |
my $ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 701 |
$query= |
|
| 702 |
qq|SELECT |
|
| 703 |
(SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 704 |
(SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 705 |
(SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 706 |
(SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 707 |
(SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 708 |
d.curr AS currencies |
|
| 709 |
$q_invdate |
|
| 710 |
FROM defaults d|; |
|
| 711 |
$ref = selectfirst_hashref_query($form, $dbh, $query); |
|
| 833 | 712 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| 834 |
$sth->finish; |
|
| 835 | 713 |
|
| 836 |
if ($form->{id}) {
|
|
| 714 |
if (!$form->{id}) {
|
|
| 715 |
$dbh->disconnect(); |
|
| 716 |
$main::lxdebug->leave_sub(); |
|
| 837 | 717 |
|
| 838 |
# retrieve invoice |
|
| 839 |
$query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate, |
|
| 840 |
a.orddate, a.quodate, a.globalproject_id, |
|
| 841 |
a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate, |
|
| 842 |
a.intnotes, a.curr AS currency |
|
| 843 |
FROM ap a |
|
| 844 |
WHERE a.id = $form->{id}|;
|
|
| 845 |
$sth = $dbh->prepare($query); |
|
| 846 |
$sth->execute || $form->dberror($query); |
|
| 847 |
|
|
| 848 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 849 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
|
| 850 |
$sth->finish; |
|
| 718 |
return; |
|
| 719 |
} |
|
| 851 | 720 |
|
| 852 |
$form->{exchangerate} =
|
|
| 853 |
$form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
|
|
| 854 |
"sell"); |
|
| 721 |
# retrieve invoice |
|
| 722 |
$query = |
|
| 723 |
qq|SELECT cp_id, invnumber, transdate AS invdate, duedate, |
|
| 724 |
orddate, quodate, globalproject_id, |
|
| 725 |
ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate, |
|
| 726 |
intnotes, curr AS currency |
|
| 727 |
FROM ap |
|
| 728 |
WHERE id = ?|; |
|
| 729 |
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 730 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
|
| 855 | 731 |
|
| 856 |
# get shipto |
|
| 857 |
$query = qq|SELECT s.* FROM shipto s |
|
| 858 |
WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
|
|
| 859 |
$sth = $dbh->prepare($query); |
|
| 860 |
$sth->execute || $form->dberror($query); |
|
| 732 |
$form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
|
|
| 861 | 733 |
|
| 862 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 863 |
delete($ref->{id});
|
|
| 864 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
|
| 865 |
$sth->finish; |
|
| 734 |
# get shipto |
|
| 735 |
$query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|; |
|
| 736 |
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 737 |
delete $ref->{id};
|
|
| 738 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
|
| 866 | 739 |
|
| 867 |
my $transdate = |
|
| 868 |
$form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
|
|
| 740 |
my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
|
|
| 741 |
my $taxzone_id = $form->{taxzone_id} * 1;
|
|
| 742 |
|
|
| 743 |
$taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id)); |
|
| 744 |
|
|
| 745 |
# retrieve individual items |
|
| 746 |
$query = |
|
| 747 |
qq|SELECT |
|
| 748 |
c1.accno AS inventory_accno, |
|
| 749 |
c1.new_chart_id AS inventory_new_chart, |
|
| 750 |
date($transdate) - c1.valid_from AS inventory_valid, |
|
| 751 |
|
|
| 752 |
c2.accno AS income_accno, |
|
| 753 |
c2.new_chart_id AS income_new_chart, |
|
| 754 |
date($transdate) - c2.valid_from AS income_valid, |
|
| 755 |
|
|
| 756 |
c3.accno AS expense_accno, |
|
| 757 |
c3.new_chart_id AS expense_new_chart, |
|
| 758 |
date($transdate) - c3.valid_from AS expense_valid, |
|
| 759 |
|
|
| 760 |
i.description, i.qty, i.fxsellprice AS sellprice, |
|
| 761 |
i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber, |
|
| 762 |
|
|
| 763 |
p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin, |
|
| 764 |
pr.projectnumber, |
|
| 765 |
pg.partsgroup |
|
| 766 |
|
|
| 767 |
FROM invoice i |
|
| 768 |
JOIN parts p ON (i.parts_id = p.id) |
|
| 769 |
LEFT JOIN chart c1 ON |
|
| 770 |
((SELECT inventory_accno_id |
|
| 771 |
FROM buchungsgruppen |
|
| 772 |
WHERE id = p.buchungsgruppen_id) = c1.id) |
|
| 773 |
LEFT JOIN chart c2 ON |
|
| 774 |
((SELECT income_accno_id_${taxzone_id}
|
|
| 775 |
FROM buchungsgruppen |
|
| 776 |
WHERE id = p.buchungsgruppen_id) = c2.id) |
|
| 777 |
LEFT JOIN chart c3 ON |
|
| 778 |
((SELECT expense_accno_id_${taxzone_id}
|
|
| 779 |
FROM buchungsgruppen |
|
| 780 |
WHERE id = p.buchungsgruppen_id) = c3.id) |
|
| 781 |
LEFT JOIN project pr ON (i.project_id = pr.id) |
|
| 782 |
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) |
|
| 783 |
|
|
| 784 |
WHERE i.trans_id = ? |
|
| 785 |
|
|
| 786 |
ORDER BY i.id|; |
|
| 787 |
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 869 | 788 |
|
| 870 |
if(!$form->{taxzone_id}) {
|
|
| 871 |
$form->{taxzone_id} = 0;
|
|
| 789 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 790 |
if (!$ref->{"part_inventory_accno_id"}) {
|
|
| 791 |
map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
|
|
| 872 | 792 |
} |
| 873 |
# retrieve individual items |
|
| 874 |
$query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid, |
|
| 875 |
c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid, |
|
| 876 |
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid, |
|
| 877 |
p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id, |
|
| 878 |
i.parts_id AS id, i.unit, p.bin, i.deliverydate, |
|
| 879 |
pr.projectnumber, |
|
| 880 |
i.project_id, i.serialnumber, |
|
| 881 |
pg.partsgroup |
|
| 882 |
FROM invoice i |
|
| 883 |
JOIN parts p ON (i.parts_id = p.id) |
|
| 884 |
LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id) |
|
| 885 |
LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
|
|
| 886 |
LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
|
|
| 887 |
LEFT JOIN project pr ON (i.project_id = pr.id) |
|
| 888 |
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) |
|
| 889 |
WHERE i.trans_id = $form->{id}
|
|
| 890 |
ORDER BY i.id|; |
|
| 891 |
$sth = $dbh->prepare($query); |
|
| 892 |
$sth->execute || $form->dberror($query); |
|
| 893 |
|
|
| 894 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 895 |
if (!$ref->{"part_inventory_accno_id"}) {
|
|
| 896 |
map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
|
|
| 897 |
} |
|
| 898 |
delete($ref->{"part_inventory_accno_id"});
|
|
| 899 |
|
|
| 900 |
while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
|
|
| 901 |
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}|;
|
|
| 902 |
my $stw = $dbh->prepare($query); |
|
| 903 |
$stw->execute || $form->dberror($query); |
|
| 904 |
($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
|
|
| 905 |
$stw->finish; |
|
| 793 |
delete($ref->{"part_inventory_accno_id"});
|
|
| 794 |
|
|
| 795 |
foreach my $type (qw(inventory income expense)) {
|
|
| 796 |
while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
|
|
| 797 |
my $query = |
|
| 798 |
qq|SELECT accno, new_chart_id, date($transdate) - valid_from |
|
| 799 |
FROM chart |
|
| 800 |
WHERE id = ?|; |
|
| 801 |
($ref->{"${type}_accno"},
|
|
| 802 |
$ref->{"${type}_new_chart"},
|
|
| 803 |
$ref->{"${type}_valid"})
|
|
| 804 |
= selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
|
|
| 906 | 805 |
} |
| 907 |
|
|
| 908 |
while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
|
|
| 909 |
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}|;
|
|
| 910 |
my $stw = $dbh->prepare($query); |
|
| 911 |
$stw->execute || $form->dberror($query); |
|
| 912 |
($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
|
|
| 913 |
$stw->finish; |
|
| 914 |
} |
|
| 915 |
|
|
| 916 |
while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
|
|
| 917 |
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}|;
|
|
| 918 |
my $stw = $dbh->prepare($query); |
|
| 919 |
$stw->execute || $form->dberror($query); |
|
| 920 |
($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
|
|
| 921 |
$stw->finish; |
|
| 806 |
} |
|
| 807 |
|
|
| 808 |
# get tax rates and description |
|
| 809 |
my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
|
|
| 810 |
$query = |
|
| 811 |
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber |
|
| 812 |
FROM tax t |
|
| 813 |
LEFT JOIN chart c ON (c.id = t.chart_id) |
|
| 814 |
WHERE t.id in |
|
| 815 |
(SELECT tk.tax_id |
|
| 816 |
FROM taxkeys tk |
|
| 817 |
WHERE tk.chart_id = |
|
| 818 |
(SELECT id |
|
| 819 |
FROM chart |
|
| 820 |
WHERE accno = ?) |
|
| 821 |
AND (startdate <= $transdate) |
|
| 822 |
ORDER BY startdate DESC |
|
| 823 |
LIMIT 1) |
|
| 824 |
ORDER BY c.accno|; |
|
| 825 |
my $stw = prepare_execute_query($form, $dbh, $query, $accno_id); |
|
| 826 |
$ref->{taxaccounts} = "";
|
|
| 827 |
|
|
| 828 |
my $i = 0; |
|
| 829 |
while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
|
|
| 830 |
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
|
|
| 831 |
$i++; |
|
| 832 |
$ptr->{accno} = $i;
|
|
| 922 | 833 |
} |
| 923 | 834 |
|
| 924 |
# get tax rates and description |
|
| 925 |
$accno_id = |
|
| 926 |
($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
|
|
| 927 |
$query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber |
|
| 928 |
FROM tax t LEFT JOIN chart c on (c.id=t.chart_id) |
|
| 929 |
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) |
|
| 930 |
ORDER BY c.accno|; |
|
| 931 |
$stw = $dbh->prepare($query); |
|
| 932 |
$stw->execute || $form->dberror($query); |
|
| 933 |
$ref->{taxaccounts} = "";
|
|
| 934 |
my $i = 0; |
|
| 935 |
while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
|
|
| 936 |
|
|
| 937 |
# if ($customertax{$ref->{accno}}) {
|
|
| 938 |
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
|
|
| 939 |
$i++; |
|
| 940 |
$ptr->{accno} = $i;
|
|
| 941 |
} |
|
| 942 |
$ref->{taxaccounts} .= "$ptr->{accno} ";
|
|
| 943 |
if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
|
|
| 944 |
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
|
|
| 945 |
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
|
|
| 946 |
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
|
|
| 947 |
$form->{taxaccounts} .= "$ptr->{accno} ";
|
|
| 948 |
} |
|
| 835 |
$ref->{taxaccounts} .= "$ptr->{accno} ";
|
|
| 949 | 836 |
|
| 837 |
if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
|
|
| 838 |
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
|
|
| 839 |
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
|
|
| 840 |
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
|
|
| 841 |
$form->{taxaccounts} .= "$ptr->{accno} ";
|
|
| 950 | 842 |
} |
| 951 | 843 |
|
| 952 |
chop $ref->{taxaccounts};
|
|
| 953 |
push @{ $form->{invoice_details} }, $ref;
|
|
| 954 |
$stw->finish; |
|
| 955 | 844 |
} |
| 956 |
$sth->finish; |
|
| 957 | 845 |
|
| 958 |
Common::webdav_folder($form) if ($main::webdav); |
|
| 846 |
chop $ref->{taxaccounts};
|
|
| 847 |
push @{ $form->{invoice_details} }, $ref;
|
|
| 848 |
$stw->finish(); |
|
| 959 | 849 |
} |
| 850 |
$sth->finish(); |
|
| 960 | 851 |
|
| 961 |
my $rc = $dbh->commit; |
|
| 962 |
$dbh->disconnect; |
|
| 852 |
Common::webdav_folder($form) if ($main::webdav); |
|
| 963 | 853 |
|
| 964 |
$main::lxdebug->leave_sub();
|
|
| 854 |
$dbh->disconnect();
|
|
| 965 | 855 |
|
| 966 |
return $rc;
|
|
| 856 |
$main::lxdebug->leave_sub();
|
|
| 967 | 857 |
} |
| 968 | 858 |
|
| 969 | 859 |
sub get_vendor {
|
| ... | ... | |
| 977 | 867 |
my $dateformat = $myconfig->{dateformat};
|
| 978 | 868 |
$dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
|
| 979 | 869 |
|
| 870 |
my $vid = conv_i($form->{vendor_id});
|
|
| 871 |
|
|
| 980 | 872 |
my $duedate = |
| 981 | 873 |
($form->{invdate})
|
| 982 |
? "to_date('$form->{invdate}', '$dateformat')"
|
|
| 874 |
? "to_date(" . $dbh->quote($form->{invdate}) . ", '$dateformat')"
|
|
| 983 | 875 |
: "current_date"; |
| 984 | 876 |
|
| 985 |
$form->{vendor_id} *= 1;
|
|
| 986 |
|
|
| 987 | 877 |
# get vendor |
| 988 |
my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms, v.notes AS intnotes, |
|
| 989 |
v.email, v.cc, v.bcc, v.language_id, v.payment_id, |
|
| 990 |
v.street, v.zipcode, v.city, v.country, v.taxzone_id, |
|
| 991 |
$duedate + COALESCE(pt.terms_netto, 0) AS duedate, |
|
| 992 |
b.description AS business |
|
| 993 |
FROM vendor v |
|
| 994 |
LEFT JOIN business b ON b.id = v.business_id |
|
| 995 |
LEFT JOIN payment_terms pt ON v.payment_id = pt.id |
|
| 996 |
WHERE v.id = ?|; |
|
| 997 |
$ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id});
|
|
| 878 |
my $query = |
|
| 879 |
qq|SELECT |
|
| 880 |
v.name AS vendor, v.creditlimit, v.terms, v.notes AS intnotes, |
|
| 881 |
v.email, v.cc, v.bcc, v.language_id, v.payment_id, |
|
| 882 |
v.street, v.zipcode, v.city, v.country, v.taxzone_id, |
|
| 883 |
$duedate + COALESCE(pt.terms_netto, 0) AS duedate, |
|
| 884 |
b.description AS business |
|
| 885 |
FROM vendor v |
|
| 886 |
LEFT JOIN business b ON (b.id = v.business_id) |
|
| 887 |
LEFT JOIN payment_terms pt ON (v.payment_id = pt.id) |
|
| 888 |
WHERE v.id = ?|; |
|
| 889 |
$ref = selectfirst_hashref_query($form, $dbh, $query, $vid); |
|
| 998 | 890 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| 999 | 891 |
|
| 1000 | 892 |
$form->{creditremaining} = $form->{creditlimit};
|
| 1001 |
$query = qq|SELECT SUM(a.amount - a.paid) FROM ap a WHERE a.vendor_id = ?|; |
|
| 1002 |
my ($unpaid_invoices) = selectfirst_array_query($form, $dbh, $query, $form->{vendor_id});
|
|
| 893 |
|
|
| 894 |
$query = qq|SELECT SUM(amount - paid) FROM ap WHERE vendor_id = ?|; |
|
| 895 |
my ($unpaid_invoices) = selectfirst_array_query($form, $dbh, $query, $vid); |
|
| 1003 | 896 |
$form->{creditremaining} -= $unpaid_invoices;
|
| 1004 | 897 |
|
| 1005 | 898 |
$query = qq|SELECT o.amount, |
| 1006 |
(SELECT e.sell FROM exchangerate e |
|
| 1007 |
WHERE e.curr = o.curr AND e.transdate = o.transdate) AS exch |
|
| 1008 |
FROM oe o WHERE o.vendor_id = ? |
|
| 1009 |
AND o.quotation = '0' AND o.closed = '0'|; |
|
| 1010 |
my $sth = prepare_execute_query($form, $dbh, $query, $form->{vendor_id});
|
|
| 899 |
(SELECT e.sell |
|
| 900 |
FROM exchangerate e |
|
| 901 |
WHERE (e.curr = o.curr) |
|
| 902 |
AND (e.transdate = o.transdate)) AS exch |
|
| 903 |
FROM oe o |
|
| 904 |
WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|; |
|
| 905 |
my $sth = prepare_execute_query($form, $dbh, $query, $vid); |
|
| 1011 | 906 |
while (my ($amount, $exch) = $sth->fetchrow_array()) {
|
| 1012 | 907 |
$exch = 1 unless $exch; |
| 1013 | 908 |
$form->{creditremaining} -= $amount * $exch;
|
| ... | ... | |
| 1016 | 911 |
|
| 1017 | 912 |
# get shipto if we do not convert an order or invoice |
| 1018 | 913 |
if (!$form->{shipto}) {
|
| 1019 |
map { delete $form->{$_} }
|
|
| 1020 |
qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail); |
|
| 914 |
delete @{$form}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)};
|
|
| 1021 | 915 |
|
| 1022 |
$query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module= 'CT'|; |
|
| 1023 |
$ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id});
|
|
| 916 |
$query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module= 'CT')|; |
|
| 917 |
$ref = selectfirst_hashref_query($form, $dbh, $query, $vid); |
|
| 918 |
@{$form}{keys %$ref} = @{$ref}{keys %$ref};
|
|
| 1024 | 919 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| 1025 | 920 |
} |
| 1026 | 921 |
|
| 1027 |
# get taxes for vendor |
|
| 1028 |
$query = qq|SELECT c.accno FROM chart c |
|
| 1029 |
JOIN vendortax v ON (v.chart_id = c.id) |
|
| 1030 |
WHERE v.vendor_id = ?|; |
|
| 1031 |
my $vendortax = (); |
|
| 1032 |
$ref = selectall_hashref_query($form, $dbh, $query, $form->{vendor_id});
|
|
| 1033 |
map { $vendortax{ $_->{accno} } = 1 } @$ref;
|
|
Auch abrufbar als: Unified diff
Umstellung der IR.pm auf die Verwendung von parametrisierten und richtig gequoteten Queries zur Vermeidung von SQL injections. Zusätzlich Kosmetik wie Leerzeichen am Zeilenende oder Umformatierungen anderer Codeteile.