Stock tracking

[Soujiro]

I use the following patches to improve the tracking of stock serial items in the system. I hope this patches can be useful to someone else. the patches are for version 4.07.2

StockMovements.diff

Code:

--- StockMovements.php    Fri Jan 16 00:26:28 1970
+++ StockMovements.php    Fri Jan 16 00:26:28 1970
@@ -60,6 +60,7 @@
$SQLBeforeDate = FormatDateForSQL($_POST['BeforeDate']);
$SQLAfterDate = FormatDateForSQL($_POST['AfterDate']);

+//this is to enable tracking of the serial items that belongs to certain move
$sql = "SELECT stockmoves.stockid,
                systypes.typename,
                stockmoves.type,
@@ -72,7 +73,8 @@
                stockmoves.price,
                stockmoves.discountpercent,
                stockmoves.newqoh,
-                stockmaster.decimalplaces
+                stockmaster.decimalplaces,
+                stockmoves.stkmoveno
        FROM stockmoves
        INNER JOIN systypes ON stockmoves.type=systypes.typeid
        INNER JOIN stockmaster ON stockmoves.stockid=stockmaster.stockid
@@ -170,7 +172,8 @@
                locale_number_format($myrow['discountpercent']*100,2),
                locale_number_format($myrow['newqoh'],$myrow['decimalplaces']));
    } else {
-
+        //this enable the link to see the serial items involved
+        $serial_link='<a target="_blank" href="'.$rootpath . '/StockSerialItems.php?&StockID=' . $StockID .'&MoveID='.$myrow['stkmoveno'].'">'._('Serial Numbers') . '</a>';
        printf('<td>%s</td>
                <td>%s</td>
                <td>%s</td>
@@ -181,6 +184,7 @@
                <td class="number">%s</td>
                <td class="number">%s%%</td>
                <td class="number">%s</td>
+                <td class="number">%s</td>
                </tr>',
                $myrow['typename'],
                $myrow['transno'],
@@ -191,7 +195,8 @@
                $myrow['reference'],
                locale_number_format($myrow['price'],$_SESSION['CompanyRecord']['decimalplaces']),
                locale_number_format($myrow['discountpercent']*100,2),
-                locale_number_format($myrow['newqoh'],$myrow['decimalplaces']));
+                locale_number_format($myrow['newqoh'],$myrow['decimalplaces']),
+                $serial_link);
    }
//end of page full new headings if
}

StockSerialItems.diff

Code:

--- StockSerialItems.php    Mon Mar 05 18:18:04 2012
+++ StockSerialItems.php    Tue Mar 06 16:00:00 2012
@@ -59,23 +59,35 @@
                        _('The SQL used to lookup the location was'));

$myrow = DB_fetch_row($result);
-
+//if we passed the id of the move we get the serials of that move
+if (isset($_GET['MoveID']))
+{
+    $MoveID = trim(strtoupper($_GET['MoveID']));
+    $sql="SELECT * FROM stockserialmoves
+        WHERE stockmoveno='". $MoveID."'
+        AND stockid='".$StockID."'";
+}
+else
+{
$sql = "SELECT serialno,
                quantity,
                expirationdate
-            FROM stockserialitems
-            WHERE loccode='" . $_GET['Location'] . "'
-            AND stockid = '" . $StockID . "'
-            AND quantity <>0";
-
+        FROM stockserialitems
+        WHERE loccode='" . $_GET['Location'] . "'
+        AND stockid = '" . $StockID . "'
+        AND quantity <>0";

+}
+    
$ErrMsg = _('The serial numbers/batches held cannot be retrieved because');
$LocStockResult = DB_query($sql, $db, $ErrMsg);

echo '<table class="selection">';

if ($Serialised==1){
-    echo '<tr><th colspan="5"><font color="navy" size="2">' . _('Serialised items in') . ' ';
+    //we check if we have a moveid to display serials numbers
+    if (isset($_GET['MoveID'])) echo '<tr><th colspan="11"><font color="navy" size="2">' . _('Serialised items in ') . _('Move').' '.$_GET['MoveID']. ' ';
+    else echo '<tr><th colspan="5"><font color="navy" size="2">' . _('Serialised items in ') . ' ';
} else {
    echo '<tr><th colspan="11"><font color="navy" size="2">' . _('Controlled items in') . ' ';
}
@@ -85,6 +97,7 @@
        <th colspan="11"><font color="navy" size="2">' . $StockID .'-'. $Description .'</b>  (' . _('In units of') . ' ' . $UOM . ')</font></th>
    </tr>';

+//a little fix
if ($Serialised == 1 and $Perishable==0){
    $tableheader = '<tr>
                        <th>' . _('Serial Number') . '</th>
@@ -92,6 +105,7 @@
                        <th>' . _('Serial Number') . '</th>
                        <th></th>
                        <th>' . _('Serial Number') . '</th>
+                        <th></th>
                    </tr>';
} else if ($Serialised == 1 and $Perishable==1){
    $tableheader = '<tr>
@@ -144,6 +158,8 @@
    }

    $TotalQuantity += $myrow['quantity'];
+    //to get the total when we pass a move id
+    if (isset($_GET['MoveID'])) $TotalQuantity +=1;

    if ($Serialised == 1 and $Perishable==0){
        echo '<td>'.$myrow['serialno'].'</td>';

StockTransfers.diff

Code:

--- StockTransfers.php    Mon Mar 05 18:18:04 2012
+++ StockTransfers.php    Tue Mar 06 15:58:14 2012
@@ -153,9 +153,13 @@
    $_SESSION['Transfer']->StockLocationTo = $_POST['StockLocationTo'];
    $_SESSION['Transfer']->TransferItem[0]->Quantity=filter_number_format($_POST['Quantity']);
    $_SESSION['Transfer']->TransferItem[0]->SerialItems=array();
+    //this set the comments in session
+    $_SESSION['Transfer']->Narrative=$_POST['Narrative'];
}
if ( isset($_POST['StockLocationTo']) ){
    $_SESSION['Transfer']->StockLocationTo = $_POST['StockLocationTo'];
+    //this set the comments in session
+    $_SESSION['Transfer']->Narrative=$_POST['Narrative'];
}

if ( isset($_POST['EnterTransfer']) ){
@@ -214,6 +218,24 @@
            include('includes/footer.inc');
            exit;
        }
+        //We get destination location name
+        $SQL="SELECT locationname
+            FROM locations
+            WHERE loccode= '" . $_SESSION['Transfer']->StockLocationTo . "'";
+
+        $ErrMsg =  _('We can not get destination because');
+        $DbgMsg =  _('The SQL that failed was');
+        $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
+        if (DB_num_rows($Result)==1){
+            $destino = DB_fetch_row($Result);
+            }
+        else {
+            $destino = $_SESSION['Transfer']->StockLocationTo;
+        }
+        if (isset($_SESSION['Transfer']->Narrative))
+        {
+          $destino[0]=$destino[0].', '.$_SESSION['Transfer']->Narrative;
+        }
        // Insert the stock movement for the stock going out of the from location
        $SQL = "INSERT INTO stockmoves (stockid,
                                        type,
@@ -231,7 +253,7 @@
                        '" . $_SESSION['Transfer']->StockLocationFrom . "',
                        '" . $SQLTransferDate . "',
                        '" . $PeriodNo . "',
-                        'To " . $_SESSION['Transfer']->StockLocationTo ."',
+                        'To " . $destino[0] ."',
                        '" . round(-$_SESSION['Transfer']->TransferItem[0]->Quantity,$_SESSION['Transfer']->TransferItem[0]->DecimalPlaces)  . "',
                        '" . ($QtyOnHandPrior - round($_SESSION['Transfer']->TransferItem[0]->Quantity,$_SESSION['Transfer']->TransferItem[0]->DecimalPlaces)) . "'
                        )";
@@ -329,8 +351,26 @@
            // There must actually be some error this should never happen
            $QtyOnHandPrior = 0;
        }
+        //We get location name of the origin
+        $SQL="SELECT locationname
+            FROM locations
+            WHERE loccode= '" . $_SESSION['Transfer']->StockLocationFrom . "'";

+        $ErrMsg =  _('Can not get location name because');
+        $DbgMsg =  _('The SQL that failed was');
+        $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
+        if (DB_num_rows($Result)==1){
+            $origen = DB_fetch_row($Result);
+            }
+        else {
+            $origen = $_SESSION['Transfer']->StockLocationFrom;
+        }
+        if (isset($_SESSION['Transfer']->Narrative))
+        {
+          $origen[0]=$origen[0].', '.$_SESSION['Transfer']->Narrative;
+        }
        // Insert the stock movement for the stock coming into the to location
+        //we also set the reference, this also need to modify stockmoves table, field reference to varchar(250)
        $SQL = "INSERT INTO stockmoves (stockid,
                        type,
                        transno,
@@ -346,7 +386,7 @@
                    '" . $_SESSION['Transfer']->StockLocationTo . "',
                    '" . $SQLTransferDate . "',
                    '" . $PeriodNo . "',
-                    '" . _('From') . " " . $_SESSION['Transfer']->StockLocationFrom . "',
+                    '" . _('From') . " " .  $origen[0] . "',
                    '" . $_SESSION['Transfer']->TransferItem[0]->Quantity . "',
                    '" . round($QtyOnHandPrior + $_SESSION['Transfer']->TransferItem[0]->Quantity,$_SESSION['Transfer']->TransferItem[0]->DecimalPlaces) . "')";

@@ -535,7 +575,9 @@
}

echo '</select></td></tr>';
-
+//this enable comments about the transfer also need to modify stockmoves table, field reference to varchar(250)
+echo '<tr><td>'. _('Comments On Why').':</td>';
+echo '<td><textarea name="Narrative" cols=40 rows=4>' . $_SESSION['Transfer']->Narrative . '</textarea></td></tr>';
echo '<tr>
        <td>'._('Transfer Quantity').':</td>';

StockAdjustments.diff

Code:

--- StockAdjustments.php    Mon Mar 05 18:18:04 2012
+++ StockAdjustments.php    Tue Mar 06 12:32:37 2012
@@ -435,10 +434,10 @@
} else {
    $Narrative ='';
}
-
+//this enable long comments on why, also need to modify stockmoves table, field reference to varchar(250)
echo '<tr>
        <td>'. _('Comments On Why').':</td>
-        <td><input type="text" name="Narrative" size="32" maxlength="30" value="' . $Narrative . '" /></td>
+        <td><textarea name="Narrative"  cols="50" rows="5">'.$_SESSION['Adjustment']->Narrative.'</textarea></td>
    </tr>';

[phil]

Nice work - I will apply to svn...
Hopefully you'll contribute more of these gems, and if you could drop them to me at submissions@weberp.org that would be super.

Thanks very much

[Soujiro]

I hope to do a lot more contributions. Speaking of SVN wich branch is stable version on svn? I know that trunk is the latest version that add new features but last stable version? Thats to get the last bugfixes and work on those version in production with safety.
Also i want to know if you do a quality check (a bunch of test) before to make a release. This "quality checks" helps to improve the quality of code.

For example
Test Stockmoves
Adjust works fine with normal, serials, controlled, etc?
Transferes works fine?
dispatch works fine?
Etc

if all stock moves works fine then this is ok to go in release

[phil]

Exson maintains a stable branch with only bug fixes from the previous trunk release under what is actually called the "stable" branch in svn Pretty sure it is the ONLY branch in svn too!!

We test the changes in the trunk code of course and only those working on the trunk will be testing it. Then we release it periodically when there is something new and interesting to release.

Exson releases stable point versions thereafter when bugs emerge.

We do rely heavily on feedback as it is a community building this software.

[Exsonqu_Qu]

Hi, Soujiro,

It sounds great to do the Quality Assurance more tightly. I didn't test serialized items as I handling the StockAdjustments.php scripts. Sorry for any inconvenience caused.

A standard Quality Assurance procedure will be great. And I'd like to hear more great idea from you.

The best way maybe to open a topic in http://www.weberp.org website or a thread in the mailing list?

Thanks and best regards!

Exson

(03-07-2012 11:15 PM)Soujiro Wrote:  I hope to do a lot more contributions. Speaking of SVN wich branch is stable version on svn? I know that trunk is the latest version that add new features but last stable version? Thats to get the last bugfixes and work on those version in production with safety.
Also i want to know if you do a quality check (a bunch of test) before to make a release. This "quality checks" helps to improve the quality of code.

For example
Test Stockmoves
Adjust works fine with normal, serials, controlled, etc?
Transferes works fine?
dispatch works fine?
Etc

if all stock moves works fine then this is ok to go in release

[Soujiro]

Thanks for the info and i'm glad that you found my ideas interesting.
I'm currently working as system administrator in a small company and because of that i get involved in the implementation of the erp of the company. The sad history is that when that happened my accounting knowledge was almost 0 so the erp solution selected by the owner was not the best (propetary software and also crappy).
Now that i know what i'm doing or at least enought to at least understand the principles behind the curtains i'm trying to get weberp working according to our country law so i can throw away that crappy erp. So in the future expect to see me a lot in the forums (i 've never liked mailing lists, i think it's dificult to searching/tracking in they)
I'll prepare a diagram with a list of tests based on my experience, i hope it can be useful.

[Exsonqu_Qu]

Hi, Soujiro,

Thank you for your contribution.
And there are a nabble forum which is based the mailing lists. You can search the mailing history and reply it just like a forum. You can check it here: http://weberp-accounting.1478800.n4.nabble.com/

I've found that you open a new thread about the Quality Assurance topic of webERP. If more users and developers involved, more benefit will gain from this. So I'd like to transfer the topic to the mailing lists if you have no objection.

Thank you again and expect your diagram to throw new light to webERP and the whole community.

Best regards!

Exson

[Soujiro]

no problem at all