[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1697] branches/1.2/LedgerSMB/IS.pm
- Subject: SF.net SVN: ledger-smb: [1697] branches/1.2/LedgerSMB/IS.pm
- From: ..hidden..
- Date: Wed, 03 Oct 2007 23:02:32 -0700
Revision: 1697
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1697&view=rev
Author: einhverfr
Date: 2007-10-03 23:02:32 -0700 (Wed, 03 Oct 2007)
Log Message:
-----------
COGS fixes
Modified Paths:
--------------
branches/1.2/LedgerSMB/IS.pm
Modified: branches/1.2/LedgerSMB/IS.pm
===================================================================
--- branches/1.2/LedgerSMB/IS.pm 2007-10-04 01:50:45 UTC (rev 1696)
+++ branches/1.2/LedgerSMB/IS.pm 2007-10-04 06:02:32 UTC (rev 1697)
@@ -1053,102 +1053,13 @@
qq|id = $form->{"id_$i"}|,
$form->{"qty_$i"} * -1
) unless $form->{shipped};
-
- if($form->{"qty_$i"}>0) {
- $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"};
- while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $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}|, (-1)*$qty);
- $allocated += $qty;
- last if ( ( $totalqty += $qty ) <= 0 );
- }
- } else {
- my $total_inventory = 0;
- $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"};
- while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $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;
- my $linetotal = $qty*$ref->{sellprice};
- $query = qq|
- INSERT INTO acc_trans
- (trans_id, chart_id, amount,
- transdate, project_id, invoice_id)
- VALUES (?, ?, ?, ?, ?, ?)|;
- my $sth1 = $dbh->prepare($query);
- $sth1->execute(
- $form->{id}, $form->{"expense_accno_id_$i"},
- $linetotal, $form->{transdate},
- $form->{"project_id_$i"}, $ref->{id}
- ) || $form->dberror($query);
- $linetotal = (-1)*$linetotal;
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, invoice_id) VALUES (?, ?, ?, ?, ?, ?)|;
- $sth1 = $dbh->prepare($query);
- $sth1->execute(
- $form->{id}, $form->{"inventory_accno_id_$i"},
- $linetotal, $form->{transdate},
- $form->{"project_id_$i"}, $ref->{id}
- ) || $form->dberror($query);
- my $allocated1 = 0;
- my $totalqty1 = $qty;
- my $query_ap = qq|SELECT i.id, i.qty, i.allocated, a.transdate
- 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.qty + i.allocated) > 0 AND i.sellprice = ?
- ORDER BY transdate|;
- my $sth1 = $dbh->prepare($query_ap);
- $sth1->execute(
- $form->{"id_$i"}, $ref->{"sellprice"}
- ) || $form->dberror($query_ap);
- while ( my $ref1 = $sth1->fetchrow_hashref(NAME_lc) ) {
- $form->db_parse_numeric(sth=>$sth1, hashref => $ref1);
- my $qty = $ref1->{qty} + $ref1->{allocated};
- if ( ( $qty - $totalqty ) > 0 ) { $qty = $totalqty; }
- $form->update_balance(
- $dbh, "invoice", "allocated",
- qq|id = $ref1->{id}|, $qty );
- $allocated1 += $qty;
- last if ( ( $totalqty1 -= $qty ) <= 0 );
- }
- $form->update_balance( $dbh, "invoice", "allocated", qq|id = $ref->{id}|, $allocated1 * -1 );
- last if ( ( $totalqty += $qty ) >= 0 );
- }
-
- }
+ $allocated = cogs(
+ $dbh, $form,
+ $form->{"id_$i"}, $form->{"qty_$i"},
+ $project_id, $form->{"sellprice_$i"},
+ );
+
}
}
@@ -1599,66 +1510,147 @@
}
sub cogs {
- my ( $dbh2, $form, $id, $totalqty, $project_id ) = @_;
+ my ( $dbh2, $form, $id, $totalqty, $project_id, $sellprice) = @_;
my $dbh = $form->{dbh};
- my $query = qq|
+ my $query;
+ my $allocated = 0;
+ if ($totalqty == 0) {
+ return 0;
+ }
+ elsif ($totalqty > 0) {
+ # If the quantity is positive, we do a standard FIFO COGS calculation.
+ # In this case, we are going to order the queue by transdate and trans_id
+ # as this is the best way of doing this perpetually. We don't want out
+ # of order entry to screw with the books. Of course if someone wants to
+ # implement LIFO, this would be the place to do it. -- CT
+
+ my $query = qq|
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, 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|;
- my $sth = $dbh->prepare($query);
- $sth->execute($id) || $form->dberror($query);
+ p.expense_accno_id,
+ (i.qty * -1) - i.allocated AS available
+ FROM invoice i
+ JOIN parts p ON (i.parts_id = p.id)
+ JOIN ap a ON (i.trans_id = a.id)
+ WHERE i.parts_id = ? AND (i.qty + i.allocated) < 0
+ ORDER BY a.transdate, i.trans_id|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($id) || $form->dberror($query);
- my $allocated = 0;
- my $qty;
+ my $qty;
- while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- if ( ( $qty = ( ( $ref->{qty} * -1 ) - $ref->{allocated} ) ) >
- $totalqty )
- {
- $qty = $totalqty;
- }
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
+ if ( $ref->{available} >= $totalqty ) {
+ $qty = $totalqty;
+ }
+ else {
+ $qty = $ref->{available};
+ }
- $form->update_balance( $dbh, "invoice", "allocated",
- qq|id = $ref->{id}|, $qty );
+ $form->update_balance( $dbh, "invoice", "allocated",
+ qq|id = $ref->{id}|, $qty );
- # total expenses and inventory
- # sellprice is the cost of the item
- my $linetotal = $form->round_amount( $ref->{sellprice} * $qty, 2 );
+ # total expenses and inventory
+ # sellprice is the cost of the item
+ my $linetotal = $form->round_amount($ref->{sellprice} * $qty, 2);
- # add expense
- push @{ $form->{acc_trans}{lineitems} },
- {
- chart_id => $ref->{expense_accno_id},
- amount => $linetotal * -1,
- project_id => $project_id,
- invoice_id => $ref->{id}
- };
+ # add expense
+ push @{ $form->{acc_trans}{lineitems} },
+ {
+ chart_id => $ref->{expense_accno_id},
+ amount => $linetotal * -1,
+ project_id => $project_id,
+ invoice_id => $ref->{id}
+ };
- # deduct inventory
- push @{ $form->{acc_trans}{lineitems} },
- {
- chart_id => $ref->{inventory_accno_id},
- amount => $linetotal,
- project_id => $project_id,
- invoice_id => $ref->{id}
- };
+ # deduct inventory
+ push @{ $form->{acc_trans}{lineitems} },
+ {
+ chart_id => $ref->{inventory_accno_id},
+ amount => $linetotal,
+ project_id => $project_id,
+ invoice_id => $ref->{id}
+ };
- # add allocated
- $allocated += -$qty;
+ # subtract from allocated
+ $allocated -= $qty;
- last if ( ( $totalqty -= $qty ) <= 0 );
+ last if ( ( $totalqty -= $qty ) <= 0 );
+ }
+
+ $sth->finish;
}
+ else {
+ # In this case, the quantity is negative. So we are looking at a
+ # reversing entry for partial COGS. The two workflows supported here
+ # are those involved in voiding an invoice or returning some items on it.
+ # If there are unallocated items for the current invoice at the end, we
+ # will throw an error until we have an understanding of other workflows
+ # that need to be supported. -- CT
+ $query = qq|
+ SELECT i.id, i.qty, i.allocated, a.transdate
+ i.qty - i.allocated AS available,
+ p.expense_accno_id, p.inventory_accno_id
+ 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($id, $sellprice) || $form->dberror($query);
+ my $qty;
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ $form->db_parse_numeric(sth=>$sth, hashref => $ref);
+ if ($totalqty > $ref->{available}){
+ $qty = $ref->{available};
+ } else {
+ $qty = $totalqty;
+ }
+ # update allocated for sold item
+ $form->update_balance(
+ $dbh, "invoice", "allocated",
+ qq|id = $ref->{id}|, $qty * -1
+ );
+ $allocated += $qty;
+ my $linetotal = $qty*$ref->{sellprice};
+ $query = qq|
+ INSERT INTO acc_trans
+ (trans_id, chart_id, amount,
+ transdate, project_id, invoice_id)
+ VALUES (?, ?, ?, ?, ?, ?)|;
- $sth->finish;
+ my $sth1 = $dbh->prepare($query);
+ $sth1->execute(
+ $form->{id}, $ref->{"expense_accno_id"},
+ $linetotal, $form->{transdate},
+ $project_id, $ref->{id}
+ ) || $form->dberror($query);
- $allocated;
+ $query = qq|
+ INSERT INTO acc_trans
+ (trans_id, chart_id, amount, transdate,
+ project_id, invoice_id)
+ VALUES (?, ?, ?, ?, ?, ?)|;
+
+ $sth1 = $dbh->prepare($query);
+ $sth1->execute(
+ $form->{id}, $ref->{"inventory_accno_id"},
+ -$linetotal, $form->{transdate},
+ $project_id, $ref->{id}
+ ) || $form->dberror($query);
+
+ $totalqty -= $qty;
+ last if $totalqty == 0;
+ }
+ if ($totalqty < 0){
+ $form->error("Too many reversed items on an invoice");
+ }
+ }
+
+ return $allocated;
}
sub reverse_invoice {
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.