diff options
| -rw-r--r-- | LedgerSMB/IS.pm | 63 |
1 files changed, 38 insertions, 25 deletions
diff --git a/LedgerSMB/IS.pm b/LedgerSMB/IS.pm index 4967f999..9682afd5 100644 --- a/LedgerSMB/IS.pm +++ b/LedgerSMB/IS.pm @@ -1058,33 +1058,45 @@ sub post_invoice { $project_id ); } else { - #search for entries on ap invoices, that have been allocated already for that product. - $query = qq| - SELECT i.id, i.qty, i.allocated, a.transdate, i.sellprice - FROM invoice i - JOIN parts p ON (p.id = i.parts_id) - JOIN ap a ON (a.id = i.trans_id) - WHERE i.parts_id = ? AND i.allocated > 0 - ORDER BY transdate,id DESC - |; - $sth = $dbh->prepare($query); - $sth->execute( $form->{"id_$i"}) || $form->dberror($query); + $query = qq| + SELECT i.id, i.qty, i.allocated, a.transdate + FROM invoice i + JOIN parts p ON (p.id = i.parts_id) + JOIN ar a ON (a.id = i.trans_id) + WHERE i.parts_id = ? AND (i.qty + i.allocated) > 0 AND i.sellprice = ? + ORDER BY transdate + |; + $sth = $dbh->prepare($query); + $sth->execute( + $form->{"id_$i"}, $form->{"sellprice_$i"} + ) || $form->dberror($query); my $totalqty = $form->{"qty_$i"}; my $total_inventory = 0; while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { - #deallocate the necessary entries - $form->db_parse_numeric(sth=>$sth, hashref => $ref); - my $qty = $ref->{allocated}; - if($totalqty + $qty > 0) { $qty = -$totalqty; } - $allocated+=$qty; - $form->update_balance( $dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1 ); - $total_inventory += $qty*$ref->{sellprice}; - last if ( ( $totalqty += $qty ) >= 0 ); - } - # increase the inventory account for that product - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id) VALUES (?, ?, ?, ?, ?)|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id}, $form->{"expense_accno_id_$i"}, $total_inventory, $form->{transdate}, $form->{"project_id_$i"}) || $form->dberror($query); + $form->db_parse_numeric(sth=>$sth, hashref => $ref); + my $qty = $ref->{qty} + $ref->{allocated}; + if ( ( $qty + $totalqty ) > 0 ) { $qty = -$totalqty; } + # update allocated for sold item + $form->update_balance( + $dbh, "invoice", "allocated", + qq|id = $ref->{id}|, $qty * -1 + ); + $allocated += $qty; + last if ( ( $totalqty += $qty ) >= 0 ); + } + + $query = qq| + INSERT INTO acc_trans + (trans_id, chart_id, amount, + transdate, project_id) + VALUES (?, ?, ?, ?, ?)|; + $sth = $dbh->prepare($query); + $sth->execute( + $form->{id}, + $form->{"expense_accno_id_$i"}, + $total_inventory, $form->{transdate}, + $form->{"project_id_$i"} + ) || $form->dberror($query); $total_inventory = (-1)*$total_inventory; $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id) VALUES (?, ?, ?, ?, ?)|; @@ -1560,8 +1572,9 @@ sub cogs { SELECT i.id, i.trans_id, i.qty, i.allocated, i.sellprice, i.fxsellprice, p.inventory_accno_id, p.expense_accno_id - FROM invoice i, parts p + FROM invoice i, parts p, ap a WHERE i.parts_id = p.id + AND i.trans_id = a.id AND i.parts_id = ? AND (i.qty + i.allocated) < 0 ORDER BY trans_id|; |