Thread: [WebERP-developers] Script changes to enhance inventory description search regardless of keyword or

Accounting & Best Practice Business Administration System

Brought to you by: daintree, exsonqu, rchacon, tim_schofield, turbopt

web-erp-developers

[WebERP-developers] Script changes to enhance inventory description search regardless of keyword order

From: Debi C. <deb...@ya...> - 2011-11-21 15:44:12

----------------------------------------------------------------------
Problem:
----------------------------------------------------------------------
We sell industrial hoses and fittings with an inventory of over 9,000 parts.
We need to search for items without having to know the exact order keywords
might show up in the description.For example, the inventory search
SS HEX SCREW 
would not return results for an item with the description
5/16 HEX SL PS SCREW 9/16 SS BA HSG

----------------------------------------------------------------------
Solution:
----------------------------------------------------------------------
Change all inventory description search scripts so that order of keywords
doesn't matter by changing the query from

"LIKE %SS%HEX%SCREW%"
to 
"LIKE %SS% AND LIKE %HEX% AND LIKE %SCREW%"

----------------------------------------------------------------------
Scripts to be changed:
----------------------------------------------------------------------
  BOMInquiry.php
  BOMs.php
  ContractBOM.php
  CounterSales.php
  MRPDemands.php
  MRPReport.php
  PO_Items.php
  PO_SelectOSPurchOrder.php
  PO_SelectPurchOrder.php
  SelectCompletedOrder.php
  SelectCreditItems.php
  SelectOrderItems.php
  SelectProduct.php
  SelectSalesOrder.php
  SelectWorkOrder.php
  Shipt_Select.php
  SupplierTenders.php
  WorkOrderEntry.php
  WorkOrderIssue.php

----------------------------------------------------------------------
Script modification:
----------------------------------------------------------------------
CHANGE where the script builds the search string. (Take care to locate
the line that is
 building the inventory search string, not another type of
search string, like customer name.)

   $SearchString = '%' . str_replace(' ', '%', $_POST['Keywords']) . '%';

TO building the new SQL search phrase instead

   $SearchWords = explode(" ", $_POST['Keywords']);
   $SearchWordsCount = substr_count($_POST['Keywords'], ' ');
   $SearchStringPhrase = "";
   for ($i = 0; $i <= $SearchWordsCount; $i++) {
       $SearchStringPhrase .= " AND stockmaster.description LIKE '%" . $SearchWords[$i] . "%'";
   }

CHANGE where the script builds the SQL statement (sometimes there are multiple instances of this line)

   AND stockmaster.description " . LIKE . " '$SearchString'

TO the SQL search phrase

   " . $SearchStringPhrase . "

OR sometimes the line might be


   WHERE stockmaster.description " . LIKE . " '$SearchString'


in that case, change it to

   WHERE 1 " . $SearchStringPhrase . "

I hope this change will be considered for incorporating into the next release. 

The ability to do a literal search might be a nice addition, too. 

Debi
Odessa, Texas, USA