03-07-2012, 05:09 AM
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
StockSerialItems.diff
StockTransfers.diff
StockAdjustments.diff
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>';