Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SelectProduct.php Search by Supplier Part Number
05-18-2021, 11:45 PM, (This post was last modified: 05-19-2021, 10:32 AM by TurboPT.)
#1
Question  SelectProduct.php Search by Supplier Part Number
I have been working on modifying SelectProduct.php to be able to search by supplier part number and just can't to get it to work, in the code if I remove lines 625 to 683 then the search for vendor code will work, but the the stockid search will not I am sure I am missing somthing to do with elseif any help would be greatly appricated.
I do understand that this would list multiple stock codes of the same but that is not a issue for us.

PHP Code:
<?php
    
/* $Id: SelectProduct.php 7188 2015-03-05 01:57:24Z vvs2012 $*/
    /* Selection of items. All item maintenance, transactions and inquiries start with this script. */
    
    
$PricesSecurity 12;//don't show pricing info unless security token 12 available to user
    
$SuppliersSecurity 9//don't show supplier purchasing info unless security token 9 available to user
    
    
include ('includes/session.inc');
    
$Title _('Search Inventory Items');
    
/* webERP manual links before header.inc */
    
$ViewTopic'Inventory';
    
$BookMark 'SelectingInventory';
    
    include (
'includes/header.inc');
    include (
'includes/SQL_CommonFunctions.inc');
    
    if (isset(
$_GET['StockID'])) {
        
//The page is called with a StockID
        
$_GET['StockID'] = trim(mb_strtoupper($_GET['StockID']));
        
$_POST['Select'] = trim(mb_strtoupper($_GET['StockID']));
    }
    echo 
'<p class="page_title_text"><img src="' $RootPath '/css/' $Theme '/images/inventory.png" title="' _('Inventory Items') . '" alt="" />' ' ' _('Inventory Items') . '</p>';
    if (isset(
$_GET['NewSearch']) or isset($_POST['Next']) or isset($_POST['Previous']) or isset($_POST['Go'])) {
        unset(
$StockID);
        unset(
$_SESSION['SelectedStockItem']);
        unset(
$_POST['Select']);
    }
    if (!isset(
$_POST['PageOffset'])) {
        
$_POST['PageOffset'] = 1;
        } else {
        if (
$_POST['PageOffset'] == 0) {
            
$_POST['PageOffset'] = 1;
        }
    }
    if (isset(
$_POST['StockCode'])) {
        
$_POST['StockCode'] = trim(mb_strtoupper($_POST['StockCode']));
    }
    
    
// Always show the search facilities
    
$SQL "SELECT categoryid,
    categorydescription
    FROM stockcategory
    ORDER BY categorydescription"
;
    
$result1 DB_query($SQL);
    if (
DB_num_rows($result1) == 0) {
        echo 
'<p class="bad">' _('Problem Report') . ':<br />' _('There are no stock categories currently defined please use the link below to set them up') . '</p>';
        echo 
'<br /><a href="' $RootPath '/StockCategories.php">' _('Define Stock Categories') . '</a>';
        exit;
    }
    
// end of showing search facilities
    /* displays item options if there is one and only one selected */
    
if (!isset($_POST['Search']) AND (isset($_POST['Select']) OR isset($_SESSION['SelectedStockItem']))) {
        if (isset(
$_POST['Select'])) {
            
$_SESSION['SelectedStockItem'] = $_POST['Select'];
            
$StockID $_POST['Select'];
            unset(
$_POST['Select']);
            } else {
            
$StockID $_SESSION['SelectedStockItem'];
        }
        
$result DB_query("SELECT stockmaster.description,
        stockmaster.longdescription,
        purchdata.suppliers_partno,
        stockmaster.mbflag,
        stockcategory.stocktype,
        stockmaster.units,
        stockmaster.decimalplaces,
        stockmaster.controlled,
        stockmaster.serialised,
        stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS cost,
        stockmaster.discontinued,
        stockmaster.eoq,
        stockmaster.volume,
        stockmaster.grossweight,
        stockcategory.categorydescription,
        stockmaster.categoryid
        FROM stockmaster INNER JOIN stockcategory
        ON stockmaster.categoryid=stockcategory.categoryid
        LEFT JOIN purchdata
        ON purchdata.stockid=stockmaster.stockid
        WHERE stockmaster.stockid='" 
$StockID "'");
        
$myrow DB_fetch_array($result);
        
$Its_A_Kitset_Assembly_Or_Dummy false;
        
$Its_A_Dummy false;
        
$Its_A_Kitset false;
        
$Its_A_Labour_Item false;
        if (
$myrow['discontinued']==1){
            
$ItemStatus '<p class="bad">' ._('Obsolete') . '</p>';
            } else {
            
$ItemStatus '';
        }
        echo 
'<table width="90%">
        <tr>
        <th colspan="3"><img src="' 
$RootPath '/css/' $Theme '/images/inventory.png" title="' _('Inventory') . '" alt="" /><b title="' $myrow['longdescription'] . '">' ' ' $StockID ' - ' $myrow['description'] . '</b> ' $ItemStatus '</th>
        </tr>'
;
        
        
        echo 
'<tr>
        <td style="width:40%" valign="top">
        <table>'
//nested table
        
echo '<tr><th class="number">' _('Category') . ':</th> <td colspan="6" class="select">' $myrow['categorydescription'] , '</td></tr>';
        echo 
'<tr><th class="number">' _('Item Type') . ':</th>
        <td colspan="2" class="select">'
;
        switch (
$myrow['mbflag']) {
            case 
'A':
            echo 
_('Assembly Item');
            
$Its_A_Kitset_Assembly_Or_Dummy True;
            break;
            case 
'G':
            echo _('Phantom Assembly Item');
            $Its_A_Kitset_Assembly_Or_Dummy True;
            $Its_A_Kitset True;
            break;
            case 
'K':
            echo 
_('Kitset Item');
            
$Its_A_Kitset_Assembly_Or_Dummy True;
            
$Its_A_Kitset True;
            break;
            case 
'D':
            echo 
_('Service/Labour Item');
            
$Its_A_Kitset_Assembly_Or_Dummy True;
            
$Its_A_Dummy True;
            if (
$myrow['stocktype'] == 'L') {
                
$Its_A_Labour_Item True;
            }
            break;
            case 
'B':
            echo 
_('Purchased Item');
            break;
            default:
            echo 
_('Manufactured Item');
            break;
        }
        echo 
'</td><th class="number">' _('Control Level') . ':</th><td class="select">';
        if (
$myrow['serialised'] == 1) {
            echo 
_('serialised');
            } elseif (
$myrow['controlled'] == 1) {
            echo 
_('Batchs/Lots');
            } else {
            echo 
_('N/A');
        }
        echo 
'</td><th class="number">' _('Units') . ':</th>
        <td class="select">' 
$myrow['units'] . '</td></tr>';
        echo 
'<tr><th class="number">' _('Volume') . ':</th>
        <td class="select" colspan="2">' 
locale_number_format($myrow['volume'], 3) . '</td>
        <th class="number">' 
_('Weight') . ':</th>
        <td class="select">' 
locale_number_format($myrow['grossweight'], 3) . '</td>
        <th class="number">' 
_('EOQ') . ':</th>
        <td class="select">' 
locale_number_format($myrow['eoq'], $myrow['decimalplaces']) . '</td></tr>';
        if (
in_array($PricesSecurity$_SESSION['AllowedPageSecurityTokens']) OR !isset($PricesSecurity)) {
            
$PriceResult DB_query("SELECT typeabbrev,
            price
            FROM prices
            WHERE currabrev ='" 
$_SESSION['CompanyRecord']['currencydefault'] . "'
            AND typeabbrev = '" 
$_SESSION['DefaultPriceList'] . "'
            AND debtorno=''
            AND branchcode=''
            AND startdate <= '"
Date('Y-m-d') ."' AND ( enddate >= '" Date('Y-m-d') . "' OR enddate = '0000-00-00')
            AND stockid='" 
$StockID "'");
            if (
$myrow['mbflag'] == 'K' OR $myrow['mbflag'] == 'A' OR $myrow['mbflag'] == 'G') {
                
$CostResult DB_query("SELECT SUM(bom.quantity * (stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost)) AS cost
                FROM bom INNER JOIN stockmaster
                ON bom.component=stockmaster.stockid
                WHERE bom.parent='" 
$StockID "'
                AND bom.effectiveafter <= '" 
date('Y-m-d') . "'
                AND bom.effectiveto > '" 
date('Y-m-d') . "'");
                
$CostRow DB_fetch_row($CostResult);
                
$Cost $CostRow[0];
                } else {
                
$Cost $myrow['cost'];
            }
            echo 
'<tr>
            <th class="number">' 
_('Price') . ':</th>';
            if (
DB_num_rows($PriceResult) == 0) {
                echo 
'<td class="select" colspan="6">' _('No Default Price Set in Home Currency') . '</td>
                </tr>'
;
                
$Price 0;
                } else {
                
$PriceRow DB_fetch_row($PriceResult);
                
$Price $PriceRow[1];
                echo 
'<td class="select">' $PriceRow[0] . '</td>
                <td class="select">' 
locale_number_format($Price$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
                <th class="number">' 
_('Cost') . ':</th>
                <td class="select">' 
locale_number_format($Cost$_SESSION['StandardCostDecimalPlaces']) . '</td>
                <th class="number">' 
_('Gross Profit') . ':</th>
                <td class="select">'
;
                if (
$Price 0) {
                    echo 
locale_number_format(($Price $Cost) * 100 $Price1) . '%';
                    } else {
                    echo 
_('N/A');
                }
                echo 
'</td>
                </tr>'
;
            }
        } 
//end of if PricesSecuirty allows viewing of prices
        
echo '</table>'//end of first nested table
        // Item Category Property mod: display the item properties
        
echo '<table>';
        
        
$sql "SELECT stkcatpropid,
        label,
        controltype,
        defaultvalue
        FROM stockcatproperties
        WHERE categoryid ='" 
$myrow['categoryid'] . "'
        AND reqatsalesorder =0
        ORDER BY stkcatpropid"
;
        
$PropertiesResult DB_query($sql);
        
$PropertyCounter 0;
        
$PropertyWidth = array();
        while (
$PropertyRow DB_fetch_array($PropertiesResult)) {
            
$PropValResult DB_query("SELECT value
            FROM stockitemproperties
            WHERE stockid='" 
$StockID "'
            AND stkcatpropid ='" 
$PropertyRow['stkcatpropid']."'");
            
$PropValRow DB_fetch_row($PropValResult);
            if (
DB_num_rows($PropValResult)==0){
                
$PropertyValue _('Not Set');
                } else {
                
$PropertyValue $PropValRow[0];
            }
            echo 
'<tr>
            <th align="right">' 
$PropertyRow['label'] . ':</th>';
            switch (
$PropertyRow['controltype']) {
                case 
0:
                case 
1:
                echo 
'<td class="select" style="width:60px">' $PropertyValue;
                break;
                case 
2//checkbox
                
echo '<td class="select" style="width:60px">';
                if (
$PropertyValue == _('Not Set')){
                    echo 
_('Not Set');
                    } elseif (
$PropertyValue == 1){
                    echo 
_('Yes');
                    } else {
                    echo 
_('No');
                }
                break;
            } 
//end switch
            
echo '</td></tr>';
            
$PropertyCounter++;
        } 
//end loop round properties for the item category
        
echo '</table></td>'//end of Item Category Property mod
        
echo '<td style="width:15%; vertical-align:top">
        <table>'
//nested table to show QOH/orders
        
$QOH 0;
        switch (
$myrow['mbflag']) {
            case 
'A':
            case 
'D':
            case 
'K':
            
$QOH _('N/A');
            
$QOO _('N/A');
            break;
            case 
'M':
            case 
'B':
            
$QOHResult DB_query("SELECT sum(quantity)
            FROM locstock
            WHERE stockid = '" 
$StockID "'");
            
$QOHRow DB_fetch_row($QOHResult);
            
$QOH locale_number_format($QOHRow[0], $myrow['decimalplaces']);
            
            
// Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
            
$QOO GetQuantityOnOrderDueToPurchaseOrders($StockID'');
            
// Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
            
$QOO += GetQuantityOnOrderDueToWorkOrders($StockID'');
            
            
$QOO locale_number_format($QOO$myrow['decimalplaces']);
            break;
        }
        
$Demand 0;
        
$DemResult DB_query("SELECT SUM(salesorderdetails.quantity-salesorderdetails.qtyinvoiced) AS dem
        FROM salesorderdetails INNER JOIN salesorders
        ON salesorders.orderno = salesorderdetails.orderno
        INNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" 
.  $_SESSION['UserID'] . "' AND locationusers.canview=1
        WHERE salesorderdetails.completed=0
        AND salesorders.quotation=0
        AND salesorderdetails.stkcode='" 
$StockID "'");
        
$DemRow DB_fetch_row($DemResult);
        
$Demand $DemRow[0];
        
$DemAsComponentResult DB_query("SELECT  SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
        FROM salesorderdetails INNER JOIN salesorders
        ON salesorders.orderno = salesorderdetails.orderno
        INNER JOIN bom ON salesorderdetails.stkcode=bom.parent
        INNER JOIN stockmaster ON stockmaster.stockid=bom.parent
        INNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" 
.  $_SESSION['UserID'] . "' AND locationusers.canview=1
        WHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
        AND bom.component='" 
$StockID "'
        AND stockmaster.mbflag='A'
        AND salesorders.quotation=0"
);
        
$DemAsComponentRow DB_fetch_row($DemAsComponentResult);
        
$Demand+= $DemAsComponentRow[0];
        
//Also the demand for the item as a component of works orders
        
$sql "SELECT SUM(qtypu*(woitems.qtyreqd - woitems.qtyrecd)) AS woqtydemo
        FROM woitems INNER JOIN worequirements
        ON woitems.stockid=worequirements.parentstockid
        INNER JOIN workorders
        ON woitems.wo=workorders.wo
        AND woitems.wo=worequirements.wo
        INNER JOIN locationusers ON locationusers.loccode=workorders.loccode AND locationusers.userid='" 
.  $_SESSION['UserID'] . "' AND locationusers.canview=1
        WHERE  worequirements.stockid='" 
$StockID "'
        AND workorders.closed=0"
;
        
$ErrMsg _('The workorder component demand for this product cannot be retrieved because');
        
$DemandResult DB_query($sql$ErrMsg);
        if (
DB_num_rows($DemandResult) == 1) {
            
$DemandRow DB_fetch_row($DemandResult);
            
$Demand+= $DemandRow[0];
        }
        echo 
'<tr>
        <th class="number" style="width:15%">' 
_('Quantity On Hand') . ':</th>
        <td style="width:17%" class="select">' 
$QOH '</td>
        </tr>'
;
        echo 
'<tr>
        <th class="number" style="width:15%">' 
_('Quantity Demand') . ':</th>
        <td style="width:17%" class="select">' 
locale_number_format($Demand$myrow['decimalplaces']) . '</td>
        </tr>'
;
        echo 
'<tr>
        <th class="number" style="width:15%">' 
_('Quantity On Order') . ':</th>
        <td style="width:17%" class="select">' 
$QOO '</td>
        </tr>
        </table>'
//end of nested table
        
echo '</td>'//end cell of master table
        
        
if (($myrow['mbflag'] == 'B' OR ($myrow['mbflag'] == 'M'))
        AND (
in_array($SuppliersSecurity$_SESSION['AllowedPageSecurityTokens']))){
            
            echo 
'<td style="width:50%" valign="top"><table>
            <tr><th style="width:50%">' 
_('Supplier') . '</th>
            <th style="width:15%">' 
_('Cost') . '</th>
            <th style="width:5%">' 
_('Curr') . '</th>
            <th style="width:15%">' 
_('Eff Date') . '</th>
            <th style="width:10%">' 
_('Lead Time') . '</th>
            <th style="width:10%">' 
_('Min Order Qty') . '</th>
            <th style="width:5%">' 
_('Prefer') . '</th></tr>';
            
$SuppResult DB_query("SELECT suppliers.suppname,
            suppliers.currcode,
            suppliers.supplierid,
            purchdata.suppliers_partno,
            purchdata.price,
            purchdata.effectivefrom,
            purchdata.leadtime,
            purchdata.conversionfactor,
            purchdata.minorderqty,
            purchdata.preferred,
            currencies.decimalplaces
            FROM purchdata INNER JOIN suppliers
            ON purchdata.supplierno=suppliers.supplierid
            INNER JOIN currencies
            ON suppliers.currcode=currencies.currabrev
            WHERE purchdata.stockid = '" 
$StockID "'
            ORDER BY purchdata.preferred DESC, purchdata.effectivefrom DESC"
);
            while (
$SuppRow DB_fetch_array($SuppResult)) {
                echo 
'<tr><td class="select">' $SuppRow['suppname'] . '</td>
                <td class="select">' 
locale_number_format($SuppRow['price'] / $SuppRow['conversionfactor'], $SuppRow['decimalplaces']) . '</td>
                <td class="select">' 
$SuppRow['currcode'] . '</td>
                <td class="select">' 
ConvertSQLDate($SuppRow['effectivefrom']) . '</td>
                <td class="select">' 
$SuppRow['leadtime'] . '</td>
                <td class="select">' 
$SuppRow['minorderqty'] . '</td>';
                
                if (
$SuppRow['preferred']==1) { //then this is the preferred supplier
                    
echo '<td class="select">' _('Yes') . '</td>';
                    } else {
                    echo 
'<td class="select">' _('No') . '</td>';
                }
                echo 
'<td class="select"><a href="' $RootPath '/PO_Header.php?NewOrder=Yes&amp;SelectedSupplier=' .
                
$SuppRow['supplierid'] . '&amp;StockID=' $StockID '&amp;Quantity='.$SuppRow['minorderqty'].'&amp;LeadTime='.$SuppRow['leadtime'] . '">' _('Order') . ' </a></td>';
                echo 
'</tr>';
            }
            echo 
'</table>';
            
DB_data_seek($result0);
        }
        echo 
'</td></tr></table><br />'// end first item details table
        
echo '<table width="90%"><tr>
        <th style="width:33%">' 
_('Item Inquiries') . '</th>
        <th style="width:33%">' 
_('Item Transactions') . '</th>
        <th style="width:33%">' 
_('Item Maintenance') . '</th>
        </tr>'
;
        echo 
'<tr><td valign="top" class="select">';
        
/*Stock Inquiry Options */
        
echo '<a href="' $RootPath '/StockMovements.php?StockID=' $StockID '">' _('Show Stock Movements') . '</a><br />';
        if (
$Its_A_Kitset_Assembly_Or_Dummy == False) {
            echo 
'<a href="' $RootPath '/StockStatus.php?StockID=' $StockID '">' _('Show Stock Status') . '</a><br />';
            echo 
'<a href="' $RootPath '/StockUsage.php?StockID=' $StockID '">' _('Show Stock Usage') . '</a><br />';
        }
        echo 
'<a href="' $RootPath '/SelectSalesOrder.php?SelectedStockItem=' $StockID '">' _('Search Outstanding Sales Orders') . '</a><br />';
        echo 
'<a href="' $RootPath '/SelectCompletedOrder.php?SelectedStockItem=' $StockID '">' _('Search Completed Sales Orders') . '</a><br />';
        if (
$Its_A_Kitset_Assembly_Or_Dummy == False) {
            echo 
'<a href="' $RootPath '/PO_SelectOSPurchOrder.php?SelectedStockItem=' $StockID '">' _('Search Outstanding Purchase Orders') . '</a><br />';
            echo 
'<a href="' $RootPath '/PO_SelectPurchOrder.php?SelectedStockItem=' $StockID '">' _('Search All Purchase Orders') . '</a><br />';
            echo 
'<a href="' $RootPath '/' $_SESSION['part_pics_dir'] . '/' $StockID '.jpg">' _('Show Part Picture (if available)') . '</a><br />';
        }
        if (
$Its_A_Dummy == False) {
            echo 
'<a href="' $RootPath '/BOMInquiry.php?StockID=' $StockID '">' _('View Costed Bill Of Material') . '</a><br />';
            echo 
'<a href="' $RootPath '/WhereUsedInquiry.php?StockID=' $StockID '">' _('Where This Item Is Used') . '</a><br />';
        }
        if (
$Its_A_Labour_Item == True) {
            echo 
'<a href="' $RootPath '/WhereUsedInquiry.php?StockID=' $StockID '">' _('Where This Labour Item Is Used') . '</a><br />';
        }
        
wikiLink('Product'$StockID);
        echo 
'</td><td valign="top" class="select">';
        
/* Stock Transactions */
        
if ($Its_A_Kitset_Assembly_Or_Dummy == false) {
            echo 
'<a href="' $RootPath '/StockAdjustments.php?StockID=' $StockID '">' _('Quantity Adjustments') . '</a><br />';
            echo 
'<a href="' $RootPath '/StockTransfers.php?StockID=' $StockID '&amp;NewTransfer=true">' _('Location Transfers') . '</a><br />';
            
//show the item image if it has been uploaded
            
if (function_exists('imagecreatefromjpeg')){
                if (
$_SESSION['ShowStockidOnImages'] == '0'){
                    
$StockImgLink '<img src="GetStockImage.php?automake=1&amp;textcolor=FFFFFF&amp;bgcolor=CCCCCC'.
                    
'&amp;StockID='.urlencode($StockID).
                    
'&amp;text='.
                    
'&amp;width=100'.
                    
'&amp;height=100'.
                    
'" alt="" />';
                    } else {
                    
$StockImgLink '<img src="GetStockImage.php?automake=1&amp;textcolor=FFFFFF&amp;bgcolor=CCCCCC'.
                    
'&amp;StockID='.urlencode($StockID).
                    
'&amp;text='$StockID .
                    
'&amp;width=100'.
                    
'&amp;height=100'.
                    
'" alt="" />';
                }
                } else {
                if( isset(
$StockID) AND file_exists($_SESSION['part_pics_dir'] . '/' .$StockID.'.jpg') ) {
                    
$StockImgLink '<img src="' $_SESSION['part_pics_dir'] . '/' $StockID '.jpg" height="100" width="100" />';
                    } else {
                    
$StockImgLink _('No Image');
                }
            }
            
            echo 
'<div class="centre">' $StockImgLink '</div>';
            
            
            if ((
$myrow['mbflag'] == 'B')
            AND (
in_array($SuppliersSecurity$_SESSION['AllowedPageSecurityTokens']))
            AND 
$myrow['discontinued']==0){
                echo 
'<br />';
                
$SuppResult DB_query("SELECT suppliers.suppname,
                suppliers.supplierid,
                purchdata.preferred,
                purchdata.minorderqty,
                purchdata.leadtime
                FROM purchdata INNER JOIN suppliers
                ON purchdata.supplierno=suppliers.supplierid
                WHERE purchdata.stockid='" 
$StockID "'
                ORDER BY purchdata.effectivefrom DESC"
);
                
$LastSupplierShown "";
                while (
$SuppRow DB_fetch_array($SuppResult)) {
                    if (
$LastSupplierShown != $SuppRow['supplierid']){
                        if ((
$myrow['eoq'] < $SuppRow['minorderqty'])) {
                            
$EOQ $SuppRow['minorderqty'];
                            } else {
                            
$EOQ $myrow['eoq'];
                        }
                        echo 
'<a href="' $RootPath '/PO_Header.php?NewOrder=Yes' '&amp;SelectedSupplier=' $SuppRow['supplierid'] . '&amp;StockID=' $StockID '&amp;Quantity='.$EOQ.'&amp;LeadTime='.$SuppRow['leadtime'].'">' .  _('Purchase this Item from') . ' ' $SuppRow['suppname'] . '</a>
                        <br />'
;
                        
$LastSupplierShown $SuppRow['supplierid'];
                    }
                    
/**/
                
/* end of while */
            
/* end of $myrow['mbflag'] == 'B' */
        
/* end of ($Its_A_Kitset_Assembly_Or_Dummy == False) */
        
echo '</td><td valign="top" class="select">';
        
/* Stock Maintenance Options */
        
echo '<a href="' $RootPath '/Stocks.php?">' _('Insert New Item') . '</a><br />';
        echo 
'<a href="' $RootPath '/Stocks.php?StockID=' $StockID '">' _('Modify Item Details') . '</a><br />';
        if (
$Its_A_Kitset_Assembly_Or_Dummy == False) {
            echo 
'<a href="' $RootPath '/StockReorderLevel.php?StockID=' $StockID '">' _('Maintain Reorder Levels') . '</a><br />';
            echo 
'<a href="' $RootPath '/StockCostUpdate.php?StockID=' $StockID '">' _('Maintain Standard Cost') . '</a><br />';
            echo 
'<a href="' $RootPath '/PurchData.php?StockID=' $StockID '">' _('Maintain Purchasing Data') . '</a><br />';
            echo 
'<a href="' $RootPath '/CustItem.php?StockID=' $StockID '">' _('Maintain Customer Item Data') . '</a><br />';
        }
        if (
$Its_A_Labour_Item == True) {
            echo 
'<a href="' $RootPath '/StockCostUpdate.php?StockID=' $StockID '">' _('Maintain Standard Cost') . '</a><br />';
        }
        if (!
$Its_A_Kitset) {
            echo 
'<a href="' $RootPath '/Prices.php?Item=' $StockID '">' _('Maintain Pricing') . '</a><br />';
            if (isset(
$_SESSION['CustomerID'])
            AND 
$_SESSION['CustomerID'] != ''
            
AND mb_strlen($_SESSION['CustomerID']) > 0) {
                echo 
'<a href="' $RootPath '/Prices_Customer.php?Item=' $StockID '">' _('Special Prices for customer') . ' - ' $_SESSION['CustomerID'] . '</a><br />';
            }
            echo 
'<a href="' $RootPath '/DiscountCategories.php?StockID=' $StockID '">' _('Maintain Discount Category') . '</a><br />';
            echo 
'<a href="' $RootPath '/StockClone.php?OldStockID=' $StockID '">' _('Clone This Item') . '</a><br />';
            echo 
'<a href="' $RootPath '/RelatedItemsUpdate.php?Item=' $StockID '">' _('Maintain Related Items') . '</a><br />';
            echo 
'<a href="' $RootPath '/PriceMatrix.php?StockID=' $StockID '">' _('Maintain Price Matrix') . '</a><br />';
        }
        echo 
'</td></tr></table>';
        } else {
        
// options (links) to pages. This requires stock id also to be passed.
        
echo '<table width="90%" cellpadding="4">';
        echo 
'<tr>
        <th style="width:33%">' 
_('Item Inquiries') . '</th>
        <th style="width:33%">' 
_('Item Transactions') . '</th>
        <th style="width:33%">' 
_('Item Maintenance') . '</th>
        </tr>'
;
        echo 
'<tr><td class="select">';
        
/*Stock Inquiry Options */
        
echo '</td><td class="select">';
        
/* Stock Transactions */
        
echo '</td><td class="select">';
        
/*Stock Maintenance Options */
        
echo '<a href="' $RootPath '/Stocks.php?">' _('Insert New Item') . '</a><br />';
        echo 
'</td></tr></table>';
    } 
// end displaying item options if there is one and only one record
    
echo '<form action="' htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">';
    echo 
'<div>';
    echo 
'<input type="hidden" name="FormID" value="' $_SESSION['FormID'] . '" />';
    
    echo 
'<p class="page_title_text"><img src="' $RootPath '/css/' $Theme '/images/magnifier.png" title="' _('Search') . '" alt="" />' ' ' _('Search for Inventory Items'). '</p>';
    echo 
'<table class="selection"><tr>';
    echo 
'<td>' _('In Stock Category') . ':';
    echo 
'<select name="StockCat">';
    if (!isset(
$_POST['StockCat'])) {
        
$_POST['StockCat'] ='';
    }
    
    if (
$_POST['StockCat'] == 'All') {
        echo 
'<option selected="selected" value="All">' _('All') . '</option>';
        } else {
        echo 
'<option value="All">' _('All') . '</option>';
    }
    while (
$myrow1 DB_fetch_array($result1)) {
        if (
$myrow1['categoryid'] == $_POST['StockCat']) {
            echo 
'<option selected="selected" value="' $myrow1['categoryid'] . '">' $myrow1['categorydescription'] . '</option>';
            } else {
            echo 
'<option value="' $myrow1['categoryid'] . '">' $myrow1['categorydescription'] . '</option>';
        }
    }
    echo 
'</select></td>';
    echo 
'<td>' _('Enter partial') . '<b> ' _('Description') . '</b>:</td><td>';
    if (isset(
$_POST['Keywords'])) {
        echo 
'<input type="text" autofocus="autofocus" name="Keywords" value="' $_POST['Keywords'] . '" title="' _('Enter text that you wish to search for in the item description') . '" size="20" maxlength="25" />';
        } else {
        echo 
'<input type="text" autofocus="autofocus" name="Keywords" title="' _('Enter text that you wish to search for in the item description') . '" size="20" maxlength="25" />';
    }
    echo 
'</td></tr><tr><td></td>';
    echo 
'<td><b>' _('OR') . ' ' '</b>' _('Enter partial') . ' <b>' _('Stock Code') . '</b>:</td>';
    echo 
'<td>';
    if (isset(
$_POST['StockCode'])) {
        echo 
'<input type="text" name="StockCode" value="' $_POST['StockCode'] . '" title="' _('Enter text that you wish to search for in the item code') . '" size="15" maxlength="18" />';
        } else {
        echo 
'<input type="text" name="StockCode" title="' _('Enter text that you wish to search for in the item code') . '" size="15" maxlength="18" />';
    }
    echo 
'</td></tr><tr><td></td>';
    echo 
'<td><b>' _('OR') . ' ' '</b>' _('Enter partial') . ' <b>' _('Vendor Code') . '</b>:</td>';
    echo 
'<td>';
    if (isset(
$_POST['SupplierPartno'])) {
        echo 
'<input type="text" name="SupplierPartno" value="' $_POST['SupplierPartno'] . '" title="' _('Enter text that you wish to search for in the item code') . '" size="15" maxlength="18" />';
        } else {
        echo 
'<input type="text" name="SupplierPartno" title="' _('Enter text that you wish to search for in the Vendor code') . '" size="15" maxlength="18" />';
    }
    echo 
'</td></tr></table><br />';
    echo 
'<div class="centre"><input type="submit" name="Search" value="' _('Search Now') . '" /></div><br />';
    echo 
'</div>
    </form>'
;
    
// query for list of record(s)
    
if(isset($_POST['Go']) OR isset($_POST['Next']) OR isset($_POST['Previous'])) {
        
$_POST['Search']='Search';
    }
    if (isset(
$_POST['Search']) OR isset($_POST['Go']) OR isset($_POST['Next']) OR isset($_POST['Previous'])) {
        if (!isset(
$_POST['Go']) AND !isset($_POST['Next']) AND !isset($_POST['Previous'])) {
            
// if Search then set to first page
            
$_POST['PageOffset'] = 1;
        }
        if (
$_POST['Keywords'] AND $_POST['StockCode'] AND $_POST['SupplierPartno']) {
            
prnMsg (_('Stock description keywords have been used in preference to the Stock code extract entered'), 'info');
        }
        if (
$_POST['Keywords']) {
            
//insert wildcard characters in spaces
            
$_POST['Keywords'] = mb_strtoupper($_POST['Keywords']);
            
$SearchString '%' str_replace(' ''%'$_POST['Keywords']) . '%';
            if (
$_POST['StockCat'] == 'All') {
                
$SQL "SELECT stockmaster.stockid,
                stockmaster.description,
                stockmaster.longdescription,
                SUM(locstock.quantity) AS qoh,
                purchdata.suppliers_partno,
                purchdata.supplierno,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                FROM stockmaster INNER JOIN locstock
                ON stockmaster.stockid=locstock.stockid
                LEFT JOIN purchdata
                ON stockmaster.stockid=purchdata.stockid
                WHERE stockmaster.stockid=locstock.stockid
                AND stockmaster.description " 
LIKE " '$SearchString'
                AND stockmaster.stockid NOT LIKE '*%'
                GROUP BY stockmaster.stockid,
                purchdata.supplierno,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                ORDER BY stockmaster.discontinued, stockmaster.stockid"
;
                } else {
                
$SQL "SELECT stockmaster.stockid,
                stockmaster.description,
                stockmaster.longdescription,
                SUM(locstock.quantity) AS qoh,
                purchdata.suppliers_partno,
                purchdata.supplierno,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                FROM stockmaster INNER JOIN locstock
                ON stockmaster.stockid=locstock.stockid
                LEFT JOIN purchdata
                ON stockmaster.stockid=purchdata.stockid
                WHERE description " 
LIKE " '$SearchString'
                AND categoryid='" 
$_POST['StockCat'] . "'
                AND stockmaster.stockid NOT LIKE '*%'
                GROUP BY stockmaster.stockid,
                purchdata.supplierno,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                ORDER BY stockmaster.discontinued, stockmaster.stockid"
;
            }
            
// If Coment out From here------------------------------------------------
            
} elseif (isset($_POST['StockCode'])) {
            
$_POST['StockCode'] = mb_strtoupper($_POST['StockCode']);
            if (
$_POST['StockCat'] == 'All') {
                
$SQL "SELECT stockmaster.stockid,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.mbflag,
                stockmaster.discontinued,
                SUM(locstock.quantity) AS qoh,
                purchdata.suppliers_partno,
                purchdata.supplierno,
                stockmaster.units,
                stockmaster.decimalplaces
                FROM stockmaster
                INNER JOIN stockcategory
                ON stockmaster.categoryid=stockcategory.categoryid
                INNER JOIN locstock ON stockmaster.stockid=locstock.stockid
                LEFT JOIN purchdata
                ON stockmaster.stockid=purchdata.stockid
                WHERE stockmaster.stockid " 
LIKE " '%" $_POST['StockCode'] . "%'
                AND stockmaster.stockid NOT LIKE '*%'
                GROUP BY stockmaster.stockid,
                purchdata.supplierno,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                ORDER BY stockmaster.discontinued, stockmaster.stockid"
;
                } else {
                
$SQL "SELECT stockmaster.stockid,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.mbflag,
                stockmaster.discontinued,
                sum(locstock.quantity) as qoh,
                purchdata.suppliers_partno,
                purchdata.supplierno,
                stockmaster.units,
                stockmaster.decimalplaces
                FROM stockmaster INNER JOIN locstock
                ON stockmaster.stockid=locstock.stockid
                LEFT JOIN purchdata
                ON stockmaster.stockid=purchdata.stockid
                WHERE stockmaster.stockid " 
LIKE " '%" $_POST['StockCode'] . "%'
                AND categoryid='" 
$_POST['StockCat'] . "'
                GROUP BY stockmaster.stockid,
                purchdata.supplierno,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                ORDER BY stockmaster.discontinued, stockmaster.stockid"
;
            }
            
// to here supplier part number search works---------------------------------------------------
            // Added Search For Supplier Part Number
            
} elseif (isset($_POST['SupplierPartno'])) {
            
$_POST['SupplierPartno'] = mb_strtoupper($_POST['SupplierPartno']);
            if (
$_POST['StockCat'] == 'All') {
                
$SQL "SELECT stockmaster.stockid,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.mbflag,
                stockmaster.discontinued,
                SUM(locstock.quantity) AS qoh,
                purchdata.suppliers_partno,
                purchdata.supplierno,
                stockmaster.units,
                stockmaster.decimalplaces
                FROM stockmaster
                INNER JOIN stockcategory
                ON stockmaster.categoryid=stockcategory.categoryid
                INNER JOIN locstock ON stockmaster.stockid=locstock.stockid
                LEFT JOIN purchdata
                ON stockmaster.stockid=purchdata.stockid
                WHERE purchdata.suppliers_partno " 
LIKE " '%" $_POST['SupplierPartno'] . "%'
                AND stockmaster.stockid NOT LIKE '*%'
                GROUP BY stockmaster.stockid,
                purchdata.supplierno,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                ORDER BY stockmaster.discontinued, stockmaster.stockid"
;
                } else {
                
$SQL "SELECT stockmaster.stockid,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.mbflag,
                stockmaster.discontinued,
                sum(locstock.quantity) as qoh,
                purchdata.suppliers_partno,
                purchdata.supplierno,
                stockmaster.units,
                stockmaster.decimalplaces
                FROM stockmaster INNER JOIN locstock
                ON stockmaster.stockid=locstock.stockid
                LEFT JOIN purchdata
                ON stockmaster.stockid=purchdata.stockid
                WHERE purchdata.suppliers_partno " 
LIKE " '%" $_POST['SupplierPartno'] . "%'
                AND categoryid='" 
$_POST['StockCat'] . "'
                GROUP BY stockmaster.stockid,
                purchdata.supplierno,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                ORDER BY stockmaster.discontinued, stockmaster.stockid"
;
            }
            
// End Search For Supplier Part Number
            
            
} elseif (!isset($_POST['StockCode']) AND !isset($_POST['Keywords']) AND !isset($_POST['SupplierPartno'])) {
            if (
$_POST['StockCat'] == 'All') {
                
$SQL "SELECT stockmaster.stockid,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.mbflag,
                stockmaster.discontinued,
                SUM(locstock.quantity) AS qoh,
                purchdata.suppliers_partno,
                purchdata.supplierno,
                stockmaster.units,
                stockmaster.decimalplaces
                FROM stockmaster INNER JOIN locstock
                ON stockmaster.stockid=locstock.stockid
                LEFT JOIN purchdata
                ON stockmaster.stockid=purchdata.stockid
                WHERE stockmaster.stockid=locstock.stockid
                GROUP BY stockmaster.stockid,
                purchdata.supplierno,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                ORDER BY stockmaster.discontinued, stockmaster.stockid"
;
                } else {
                
$SQL "SELECT stockmaster.stockid,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.mbflag,
                stockmaster.discontinued,
                SUM(locstock.quantity) AS qoh,
                purchdata.suppliers_partno,
                purchdata.supplierno,
                stockmaster.units,
                stockmaster.decimalplaces
                FROM stockmaster INNER JOIN locstock
                ON stockmaster.stockid=locstock.stockid
                LEFT JOIN purchdata
                ON stockmaster.stockid=purchdata.stockid
                WHERE categoryid='" 
$_POST['StockCat'] . "'
                GROUP BY stockmaster.stockid,
                purchdata.supplierno,
                stockmaster.description,
                stockmaster.longdescription,
                stockmaster.units,
                stockmaster.mbflag,
                stockmaster.discontinued,
                stockmaster.decimalplaces
                ORDER BY stockmaster.discontinued, stockmaster.stockid"
;
            }
        }
        
$ErrMsg _('No stock items were returned by the SQL because');
        
$DbgMsg _('The SQL that returned an error was');
        
$SearchResult DB_query($SQL$ErrMsg$DbgMsg);
        if (
DB_num_rows($SearchResult) == 0) {
            
prnMsg(_('No stock items were returned by this search please re-enter alternative criteria to try again'), 'info');
        }
        unset(
$_POST['Search']);
    }
    
/* end query for list of records */
    /* display list if there is more than one record */
    
if (isset($SearchResult) AND !isset($_POST['Select'])) {
        echo 
'<form action="' htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">';
        echo 
'<div>';
        echo 
'<input type="hidden" name="FormID" value="' $_SESSION['FormID'] . '" />';
        
$ListCount DB_num_rows($SearchResult);
        if (
$ListCount 0) {
            
// If the user hit the search button and there is more than one item to show
            
$ListPageMax ceil($ListCount $_SESSION['DisplayRecordsMax']);
            if (isset(
$_POST['Next'])) {
                if (
$_POST['PageOffset'] < $ListPageMax) {
                    
$_POST['PageOffset'] = $_POST['PageOffset'] + 1;
                }
            }
            if (isset(
$_POST['Previous'])) {
                if (
$_POST['PageOffset'] > 1) {
                    
$_POST['PageOffset'] = $_POST['PageOffset'] - 1;
                }
            }
            if (
$_POST['PageOffset'] > $ListPageMax) {
                
$_POST['PageOffset'] = $ListPageMax;
            }
            if (
$ListPageMax 1) {
                echo 
'<div class="centre"><br />&nbsp;&nbsp;' $_POST['PageOffset'] . ' ' _('of') . ' ' $ListPageMax ' ' _('pages') . '. ' _('Go to Page') . ': ';
                echo 
'<select name="PageOffset">';
                
$ListPage 1;
                while (
$ListPage <= $ListPageMax) {
                    if (
$ListPage == $_POST['PageOffset']) {
                        echo 
'<option value="' $ListPage '" selected="selected">' $ListPage '</option>';
                        } else {
                        echo 
'<option value="' $ListPage '">' $ListPage '</option>';
                    }
                    
$ListPage++;
                }
                echo 
'</select>
                <input type="submit" name="Go" value="' 
_('Go') . '" />
                <input type="submit" name="Previous" value="' 
_('Previous') . '" />
                <input type="submit" name="Next" value="' 
_('Next') . '" />
                <input type="hidden" name="Keywords" value="'
.$_POST['Keywords'].'" />
                <input type="hidden" name="StockCat" value="'
.$_POST['StockCat'].'" />
                <input type="hidden" name="StockCode" value="'
.$_POST['StockCode'].'" />
                <input type="hidden" name="SupplierPartno" value="'
.$_POST['SupplierPartno'].'" />
                
                <br />
                </div>'
;
            }
            echo 
'<table id="ItemSearchTable" class="selection">';
            
$TableHeader '<tr>
            <th>' 
_('Stock Status') . '</th>
            <th class="ascending">' 
_('Code') . '</th>
            <th class="ascending">' 
_('Vendor Code') . '</th>
            <th class="ascending">' 
_('Vendor') . '</th>
            <th class="ascending">' 
_('Description') . '</th>
            <th>' 
_('Total Qty On Hand') . '</th>
            <th>' 
_('Units') . '</th>
            </tr>'
;
            echo 
$TableHeader;
            
$j 1;
            
$k 0//row counter to determine background colour
            
$RowIndex 0;
            if (
DB_num_rows($SearchResult) <> 0) {
                
DB_data_seek($SearchResult, ($_POST['PageOffset'] - 1) * $_SESSION['DisplayRecordsMax']);
            }
            while ((
$myrow DB_fetch_array($SearchResult)) AND ($RowIndex <> $_SESSION['DisplayRecordsMax'])) {
                if (
$k == 1) {
                    echo 
'<tr class="EvenTableRows">';
                    
$k 0;
                    } else {
                    echo 
'<tr class="OddTableRows">';
                    
$k++;
                }
                if (
$myrow['mbflag'] == 'D') {
                    
$qoh _('N/A');
                    } else {
                    
$qoh locale_number_format($myrow['qoh'], $myrow['decimalplaces']);
                }
                if (
$myrow['discontinued']==1){
                    
$ItemStatus '<p class="bad">' _('Obsolete') . '</p>';
                    } else {
                    
$ItemStatus ='';
                }
                
                echo 
'<td>' $ItemStatus '</td>
                <td><input type="submit" name="Select" value="' 
$myrow['stockid'] . '" /></td>
                <td>' 
$myrow['suppliers_partno']  '</td>
                <td><a target="_blank" href="' 
$RootPath '/SelectSupplier.php?SupplierID=' $myrow['supplierno'].'">' $myrow['supplierno'] . '</a></td>
                <td title="'
$myrow['longdescription'] . '">' $myrow['description'] . '</td>
                <td class="number">' 
$qoh '</td>
                <td>' 
$myrow['units'] . '</td>
                <td><a target="_blank" href="' 
$RootPath '/StockStatus.php?StockID=' $myrow['stockid'].'">' _('View') . '</a></td>
                </tr>'
;
                
/*
                    $j++;
                    
                    if ($j == 20 AND ($RowIndex + 1 != $_SESSION['DisplayRecordsMax'])) {
                    $j = 1;
                    echo $TableHeader;
                    }
                */
                
$RowIndex $RowIndex 1;
                
//end of page full new headings if
            
}
            
//end of while loop
            
echo '</table>
            </div>
            </form>
            <br />'
;
        }
    }
    
/* end display list if there is more than one record */
    
    
include ('includes/footer.inc');
?>
Reply


Messages In This Thread
SelectProduct.php Search by Supplier Part Number - by daveparrish - 05-18-2021, 11:45 PM

Forum Jump:


Users browsing this thread: 1 Guest(s)