<!-- 
/*
Copyright 2017 apHarmony

This file is part of jsHarmony.

jsHarmony is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

jsHarmony is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License
along with this package.  If not, see <http://www.gnu.org/licenses/>.
*/
-->
<script type="text/x-tutorial-info">
{
  "Title": "Grid Search",
  "Menu": ["Models","Grids"],
  "Code": ["/models/GridSearch_Cust_Listing.json"],
  "Demo": "jsHarmonyTutorials/GridSearch_Cust_Listing"
}
</script>

<h3>Grid Search Customizations</h3>

The following properties are available to manage search behavior:
<pre>
Model:
  "grid_require_search": 1
  "grid_expand_search": 1
  "noresultsmessage":  "No results found %%%FORSEARCHPHRASE%%%",
  "default_search": [{"Column":"...", "Value":"...", "Comparison":"...", "Join":"..."}],

Field:
  "sqlsearch":"cust_name||' '||cust_desc"
  "sqlsearchsound": "cust_id in (select cust_id from soundex where soundex_col='cust_name' and soundex_val=soundex(%%%FIELD%%%))"
  "disable_search": true
  "disable_search_all": true
  "enable_search": true
</pre>

<h4>model.grid_require_search</h4>
The "grid_require_search" parameter is used on grids with a lot of computed data, where the initial query may be slow.  This will prevent the initial grid results from showing when the form is first loaded.

<h4>model.grid_expand_search</h4>
The "grid_expand_search" parameter will automatically open the search when a grid is first loaded.  This is useful for grids with a lot of data that are frequently queried, such as customer listings.<br/>
<br/>
The "grid_expand_search" parameter is also helpful when integrating with barcode scanners, since the search field has focus when the page is first opened.

<h4>model.noresultsmessage</h4>
The message displayed in the grid when no results are returned for a query.  By default, this is:
<pre>
No results found %%%FORSEARCHPHRASE%%%
</pre>
The %%%FORSEARCHPHRASE%%% variable will automatically be replaced with the search phrase.

<h4>model.default_search</h4>
The "default_search" property can be used to set the initial default values for the search query.  Multiple search expressions are supported.
<pre>
{
  "default_search": [
    {"Column":"[i]field 1[/i]", "Value":"[i]value 1[/i]", "Comparison":"[i]comparison_type[/i]", "Join":"[i]join_type[/i]"},
    {"Column":"[i]field 2[/i]", "Value":"[i]value 2[/i]", "Comparison":"[i]comparison_type[/i]", "Join":"[i]join_type[/i]"},
  ],
}

[i]comparison_type[/i] :=
  &gt;
  &lt;
  &gt;=
  &lt;
  =
  &lt;&gt;
  contains
  notcontains
  beginswith
  endswith
  null       //Comparison type for "Blank"
  notnull    //Comparison type for "Not Blank"
  soundslike

[i]join_type[/i] :=
  and
  or
</pre>
For example:
<pre>
{
  "default_search": [
    {"Column":"cust_name", "Value":"Smith", "Comparison":"contains", "Join":"and"}
  ],
}
</pre>

<h4>field.sqlsearch</h4>
The field "sqlsearch" property can be used to customize the data that is searched.  This can be used to extend the search across additional fields.

<h4>field.sqlsearchsound</h4>
Soundex implementations are used to search by pronunciation instead of by a character string.  This is especially useful when searching for people's names, where one name can have multiple spellings, such as "Caitlyn", "Katelin",  "Kaitlin", and "Caitlin".<br/>
<br/>
The Soundex information is often implemented via database trigger, storing the phonetic representation in a "soundex" table:
<pre>
soundex :: Soundex Table
soundex_id        Soundex Internal ID
  field_name    Field name that is being encoded
  table_name    Table name of the field that is being encoded
  table_id      Unique key in the table of the field that is being encoded
  soundex_word      Original word
  soundex_val       Soundex value
</pre>
A trigger should populate the soundex table when the target field is updated (in this case, the customer name):
<pre>
delete from soundex where table_name='cust' and table_id=new.cust_id
insert into soundex(table_name, field_name, table_id, soundex_word, soundex_val)
         values('cust', 'cust_name', new.cust_id, new.cust_name, soundex(new.cust_name) )
</pre>
Then, the "sqlsearchsound" expression can be used in the field to search the soundex table by pronunciation:
<pre>
"sqlsearchsound": "cust_id in (select table_id from soundex where table_name='cust' and field_name='cust_name' and soundex_val=soundex(%%%FIELD%%%))"
</pre>
The %%%FIELD%%% variable will be replaced by the search query.<br/>
The %%%SOUNDEX%%% variable will be replaced by a Soundex of the search query

<h4>Removing a Field from the Search Drop-down</h4>
Setting a field's "disable_search" property to true will remove the field from the search drop-down.
<pre>
{
  "fields":[
    {"name":"cust_name","caption":"Customer","disable_search":true}
  ]
}
</pre>

<h4>Removing a Field from the "Any..." Column Search</h4>
The "disable_search_all" property will remove the field from the "Any..." Column search.  This is useful primary for performance reasons, such as in situations with data-intensive computed columns.  The "disable_search_all" property still enables users to search by the computed column individually, but removes the column from the "All Fields" search.
<pre>
{
  "fields":[
    {"name":"cust_name","caption":"Customer","disable_search_all":true}
  ]
}
</pre>

<h4>Enabling Search on a Hidden Field</h4>
By default, hidden fields have search disabled.  Search can be enabled on a hidden field with the "enable_search" property.  This is especially useful for searching for a hashed field, such as an SSN:
<pre>
{
  "fields":[
    {"name":"cust_ssnhash","caption": "SSN","type":"hash","length":20,"salt":"cust_ssnhash","enable_search":true}
  ]
}
</pre>


<h3>SQL Where Clauses</h3>
Additionally, where clauses can be hard-coded in the SQL expression using "model.sqlwhere" and "field.sqlwhere".

<h4>model.sqlwhere</h4>
A custom SQL where expression can be added to a grid using the model's "sqlwhere" property.  In the following example, the grid will only show Active customers:
<pre>
{
  "sqlwhere":"cust_sts='ACTIVE'"
}
</pre>

<h4>field.sqlwhere</h4>
If a field is in the querystring or in a binding, the grid will be filtered by that field.  By default, the sql "where" expression is:
<pre>
FIELD_NAME=@FIELD_NAME
</pre>
This sql "where" expression can be customized using the field's "sqlwhere" property.  For example, the Customer Name can be queried over multiple fields:
<pre>
{
  "fields":[
    {"name":"cust_name","caption":"Customer","sqlwhere":"(cust_name like '%'||@cust_name||'%' or cust_desc like '%'||@cust_name||'%')"}
  ]
}
</pre>
In the above example, the following URL querystrings will search both the customer name and description fields
<ul>
  <li>
    <b>Partial Customer Name Match:</b><br/>
    <a href="/jsHarmonyTutorials/GridSearch_Cust_Listing?cust_name=Industries" target="_blank">/GridBasic_Search?cust_name=Industries</a>
  </li>
  <li>
    <b>Partial Customer Description Match:</b><br/>
    <a href="/jsHarmonyTutorials/GridSearch_Cust_Listing?cust_name=Fixture" target="_blank">/GridBasic_Search?cust_name=Fixture</a>
  </li>
</ul>