[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SF.net SVN: ledger-smb:[4652] trunk/LedgerSMB



Revision: 4652
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4652&view=rev
Author:   einhverfr
Date:     2012-04-16 10:02:14 +0000 (Mon, 16 Apr 2012)
Log Message:
-----------
Hooking in IR and IS modules to new COGS stored procedures

Modified Paths:
--------------
    trunk/LedgerSMB/IR.pm
    trunk/LedgerSMB/IS.pm

Modified: trunk/LedgerSMB/IR.pm
===================================================================
--- trunk/LedgerSMB/IR.pm	2012-04-16 08:35:31 UTC (rev 4651)
+++ trunk/LedgerSMB/IR.pm	2012-04-16 10:02:14 UTC (rev 4652)
@@ -443,109 +443,14 @@
                     qq|id = $form->{"id_$i"}|,
                     $form->{"qty_$i"} )
                   unless $form->{shipped};
+                my $cogs_sth = $dbh->prepare(
+                      'SELECT * FROM cogs__add_for_ap(?, ?, ?)'
+                );
+                $cogs_sth->execute($form->{"id_$i"}, 
+                                   $form->{"qty_$i"},
+                                   $form->{"sellprice_$i"});
+                $cogs_sth->finish;
 
-                # check if we sold the item
-                $query = qq|
-					  SELECT i.id, i.qty, i.allocated, 
-					         i.trans_id, 
-					         p.inventory_accno_id, 
-					         p.expense_accno_id, 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
-					ORDER BY transdate|;
-                $sth = $dbh->prepare($query);
-                $sth->execute( $form->{"id_$i"} )
-                  || $form->dberror($query);
-
-                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;
-                    }
-
-                    $linetotal =
-                      $form->round_amount( $form->{"sellprice_$i"} * $qty, 2 );
-
-                    if ($linetotal) {
-                        $query = qq|
-				INSERT INTO acc_trans 
-				            (trans_id, 
-				            chart_id, 
-				            amount, 
-				            invoice_id,
-				            transdate) 
-				     VALUES (?, ?, ?, 
-				            ?, (SELECT CASE WHEN ? <= value::date
-				                            THEN value::date +
-				                               '1 day'::interval
-				                            ELSE ?
-				                        END AS value 
-				                  FROM defaults
-				                  WHERE setting_key = 'closedto'
-				))|;
-
-                        my $sth = $dbh->prepare($query);
-                        $sth->execute(
-                            $ref->{trans_id},   $ref->{inventory_accno_id},
-                            $linetotal, 
-                            $invoice_id,
-                            $ref->{transdate}, $ref->{transdate},
-                        ) || $form->dberror($query);
-
-                        for my $cls(@{$form->{bu_class}}){
-                            if ($form->{"b_unit_$cls->{id}_$i"}){
-                             $b_unit_sth_ac->execute($cls->{id}, $form->{"b_unit_$cls->{id}_$i"});
-                            }
-                        }
-                        # add expense
-                        $query = qq|
-				INSERT INTO acc_trans 
-				            (trans_id, chart_id, amount, 
-				            invoice_id,
-				            transdate) 
-				     VALUES (?, ?, ?, 
-				            ?, (SELECT CASE WHEN ? <= value::date
-				                            THEN value::date +
-				                               '1 day'::interval
-				                            ELSE ?
-				                        END AS value 
-				                  FROM defaults
-				                  WHERE setting_key = 'closedto'
-				))|;
-                        $sth = $dbh->prepare($query);
-                        $sth->execute(
-                            $ref->{trans_id},   $ref->{expense_accno_id},
-                            $linetotal * -1,    
-                            $invoice_id,
-                            $ref->{transdate}, $ref->{transdate},
-                        ) || $form->dberror($query);
-                    }
-
-                    for my $cls(@{$form->{bu_class}}){
-                        if ($form->{"b_unit_$cls->{id}_$i"}){
-                         $b_unit_sth_ac->execute($cls->{id}, $form->{"b_unit_$cls->{id}_$i"});
-                        }
-                    }
-                    # update allocated for sold item
-                    $form->update_balance( $dbh, "invoice", "allocated",
-                        qq|id = $ref->{id}|,
-                        $qty * -1 );
-                    $form->update_balance( $dbh, "invoice", "allocated",
-				qq|id =$invoice_id|,$qty);
-
-                    $allocated += $qty;
-
-                    last if ( ( $totalqty -= $qty ) <= 0 );
-                }
-
-                $sth->finish;
-
             }
             else {
 

Modified: trunk/LedgerSMB/IS.pm
===================================================================
--- trunk/LedgerSMB/IS.pm	2012-04-16 08:35:31 UTC (rev 4651)
+++ trunk/LedgerSMB/IS.pm	2012-04-16 10:02:14 UTC (rev 4652)
@@ -1719,84 +1719,40 @@
 }
 
 sub cogs {
-    # This is nearly entirely rewritten since 1.2.8 based in part on the works
-    # of Victor Sterpu and Dieter Simader (see CONTRIBUTORS for more 
-    # information).  However, there are a number of areas where I have 
-    # substantially rewritten the logic.  This function is heavily annotated 
-    # largely because COGS/invoices are still scheduled to be re-engineered in
-    # 1.4 so it is a good idea to have records of opinions in the code.-- CT
     my ( $dbh2, $form, $id, $totalqty, $project_id, $sellprice) = @_;
+    # $id is parts id.
     my $dbh   = $form->{dbh};
-    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.precision, i.fxsellprice, p.inventory_accno_id, 
-		          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);
+    # Parts info
+    my $part_sth = $dbh->prepare('SELECT * FROM parts WHERE id = ?');
+    $part_sth->execute($id);
+    my ($part_ref) = $part_sth->fetchrow_hashref('NAME_lc')
 
-        my $qty;
+    # Getting cogs
+    my $cogs_sth = $dbh->prepare('SELECT * FROM cogs__add_for_ar(?, ?)');
+    $cogs_sth->execute($id, $totalqty);
+    my ($cogs) = $cogs_sth->fetchrow_array();
 
-        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};
-            }
+    # Setting up for the main transaction.
 
-            $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);
-
-            # add expense
-            push @{ $form->{acc_trans}{lineitems} },
+    if ($totalqty > 0){
+        push @{ $form->{acc_trans}{lineitems} },
               {
-                chart_id   => $ref->{expense_accno_id},
-                amount     => $linetotal * -1,
+                chart_id   => $parts_ref->{expense_accno_id},
+                amount     => $cogs * -1,
                 project_id => $project_id,
-                invoice_id => $ref->{id}
+                invoice_id => $parts_ref->{id}
               };
 
-            # deduct inventory
-            push @{ $form->{acc_trans}{lineitems} },
+        push @{ $form->{acc_trans}{lineitems} },
               {
-                chart_id   => $ref->{inventory_accno_id},
-                amount     => $linetotal,
+                chart_id   => $parts_ref->{inventory_accno_id},
+                amount     => $cogs,
                 project_id => $project_id,
-                invoice_id => $ref->{id}
+                invoice_id => $parts_ref->{id}
               };
 
-            # subtract from allocated
-            $allocated -= $qty;
-
-            last if ( ( $totalqty -= $qty ) <= 0 );
-        }
-
-        $sth->finish;
-    }
-    else {
+    } 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.

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.