<!-- 
/*
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": "Field SQL Expressions",
  "Menu": ["Models","Fields / Controls"],
  "Code": [
    "/models/FieldCustomSQL_Grid.json",
    "/models/FieldCustomSQL_Form.json"
  ],
  "Demo": [
    { "url": "jsHarmonyTutorials/FieldCustomSQL_Grid", "title": "Custom SQL Field Expressions - Grid" },
    { "url": "jsHarmonyTutorials/FieldCustomSQL_Form?cust_id=1&action=update", "title": "Custom SQL Field Expressions - Form" }
  ]
}
</script>

<h3>Field SQL Expressions</h3>
Although a model's database SQL behavior can be modified using the <a href="/tutorials/model_database_sql">Model Custom SQL Expressions</a>, it can be useful to change the SQL behavior for an individual field.<br/>
<br/>
By default, the generated SQL statements will use the field value as the column value in select / insert / update / delete statements.  This behavior can be overridden by using the following sql properties:

<h4>field.sqlselect</h4>
The "sqlselect" property can be used to change the column expression in the "select" statement.  For example, sqlselect can be used to select only the "year" part of a date field:
<pre>
{
  "name": "cust_start_dt",
  "sqlselect": "(cast(strftime('%Y', cust_start_dt) as integer))"
}
Resulting SQL Expression: select (cast(strftime('%Y', cust_start_dt) as integer)) as "cust_start_dt" from cust
</pre>

<h4>field.sqlinsert</h4>
The "sqlinsert" property can be used to change the column value in the "insert" statement.  For example, sqlinsert can be used to set the primary key on insert:
<pre>
{
  "name": "customer_id",
  "sqlinsert": "(select max(customer_id)+1 from cust)"
}
Resulting SQL Expression: insert into cust(customer_id) values ((select ifnull(max(cust_id)+1,1) from cust))
</pre>

<h4>field.sqlupdate</h4>
The "sqlupdate" property can be used to change the column value in the "sqlupdate" statement.  For example, sqlupdate can be used to apply a "last modified" date:
<pre>
{
  "name": "cust_update_dt",
  "sqlupdate": "(date('now', 'localtime'))"
}
Resulting SQL Expression: update cust set cust_update_dt=(date('now', 'localtime'))
</pre>

<h4>field.sqlsort</h4>
The "sqlsort" property is used in grids, as the sort expression.  For example, sqlsort can be used to sort a List of Values field by a sequence field:
<pre>
{
  "name": "cust_sts",
  "sqlupdate": "(select code_seq from code_cust_sts where code_cust_sts.code_val=cust.cust_sts)"
}
Resulting SQL Expression: select cust_id,cust_sts from cust order by (select code_seq from code_cust_sts where code_cust_sts.code_val=cust.cust_sts) asc
</pre>

<h4>field.sqlwhere</h4>
The "sqlwhere" property is used in grids, when a filter is set in the URL / querystring.  For instance, if the value for "cust_start_dt" is passed in the URL:<br/>
<a href="/jsHarmonyTutorials/FieldCustomSQL_Grid?cust_start_dt=2018" target="_blank">/jsHarmonyTutorials/FieldCustomSQL_Grid?cust_start_dt=2018</a><br/>
Then, the SQL where expression could be modified to show any results with "cust_start_dt" greater than or equal to "2018", instead of just results equal to "2018".
<pre>
{
  "name": "cust_start_dt",
  "sqlwhere": "(cust_start_dt >= (date(@cust_start_dt||'-01-01')))"
}
Resulting SQL Expression: select cust_id,cust_sts from cust where (cust_start_dt >= (date(@cust_start_dt||'-01-01')))
</pre>
The field.sqlwhere expression will only be added if the parameter is in the querystring or bindings.

<h4>field.sqlsearch</h4>
The "sqlsearch" property is used in grids, when the "search" tool is used to search for data.  For example, sqlsearch can be used to search across multiple fields:
<pre>
{
  "name": "cust_name",
  "sqlsearch":"(cust_name||' '||cust_desc)"
}
Resulting SQL Expression: select cust_id,cust_name from cust where (upper((cust_name||' '||cust_desc)) like upper(@search_0_cust_name))
</pre>
In this example, searching the "cust_name" field will search across both "cust_name" and "cust_desc".

<h4>field.sqlsearchsound</h4>
The "sqlsearchsound" property is used in grids, when the "search" tool is used to search the column using the "Sounds Like" comparison.  The Soundex of the search value can be used as a parameter via the %%%SOUNDEX%%% expression:
<pre>
{
  "name": "cust_name",
  "sqlsearchsound": "cust_id in (select table_id from soundex where table_name='cust' and field_name='cust_name' and soundex_val=%%%SOUNDEX%%%)"
}
Resulting SQL Expression: select cust_id,cust_name from cust where (cust_id in (select table_id from soundex where table_name='cust' and field_name='cust_name' and soundex_val=@search_0_cust_name_soundex))
</pre>
In this example, the "soundex" table contains the Soundex index of all data in the "cust" table.  The soundex table should be updated via trigger on any insert / update into the "cust" table.<br/>
<br/>
The sqlsearchsound property supports the following variables:
<ul>
  <li>%%%FIELD%%% - Will be replaced by the search query</li>
  <li>%%%SOUNDEX%%% - Will be replaced by a Soundex of the search query</li>
</ul>

<h4>Example</h4>
Form with fields using sqlselect, sqlinsert, and sqlupdate:
<ul>
  <li>cust_id - Automatic primary key</li>
  <li>cust_start_dt - Year-only dates</li>
  <li>cust_update_dt - Timestamp</li>
</ul>
<%-getScreenshot('jsHarmonyTutorials/FieldCustomSQL_Form?cust_id=1&action=update&popup=1','Custom SQL Field Expressions - Form')%>
<pre>
{ 
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "popup":[900,400],
  "fields":[
    {
      "name":"cust_id",
      "caption": "ID",
      "sqlinsert":"(select max(cust_id)+2 from cust)",
      "actions":"BI",
      "readonly":true
    },
    {"name":"cust_name","caption":"Name"},
    {
      "name":"cust_start_dt",
      "caption":"Start Year",
      "type":"int",
      "sqlselect":"(cast(strftime('%Y', cust_start_dt) as integer))",
      "sqlinsert":"(date(@cust_start_dt||'-01-01'))",
      "sqlupdate":"(date(@cust_start_dt||'-01-01'))"
    },
    {
      "name":"cust_update_dt",
      "control":"hidden",
      "sqlinsert":"(date('now', 'localtime'))",
      "sqlupdate":"(date('now', 'localtime'))"
    }
  ]
}
</pre>
Grid with fields using sqlselect, sqlinsert, sqlupdate, sqlsort, sqlwhere, sqlsearch, and sqlsearchsound:
<ul>
  <li>cust_name - Compound Search via sqlsearch, and Soundex via sqlsearchsound</li>
  <li>cust_sts - sqlsort via a sort column in another table</li>
  <li>cust_start_dt - Year-only dates, and custom sqlwhere expression</li>
</ul>
<%-getScreenshot('jsHarmonyTutorials/FieldCustomSQL_Grid?popup=1','Custom SQL Field Expressions - Grid')%>
<pre>
{
  "table":"cust",
  "layout":"grid",
  "caption":["Customer","Customers"],
  "buttons":[{"link":"insert:FieldCustomSQL_Form"}],
  "fields":[
    {"name":"cust_id","caption": "ID","link":"update:FieldCustomSQL_Form"},
    {"name":"cust_name","caption":"Customer",
     "sqlsearch":"(cust_name||' '||cust_desc)",
     "sqlsearchsound": "cust_id in (select table_id from soundex where table_name='cust' and field_name='cust_name' and soundex_val=%%%SOUNDEX%%%)"
    },
    {"name":"cust_sts","caption":"Status",
     "sqlsort":"(select code_seq from code_cust_sts where code_cust_sts.code_val=cust.cust_sts)"
    },
    {"name":"cust_start_dt","caption":"Start Year",
      "type":"int",
      "sqlselect":"(cast(strftime('%Y', cust_start_dt) as integer))", //Extract the year from the date field
      "sqlinsert":"(date(@cust_start_dt||'-01-01'))", //Convert the input year ("integer") to a date YYYY-01-01
      "sqlupdate":"(date(@cust_start_dt||'-01-01'))", //Convert the input year ("integer") to a date YYYY-01-01
      "sqlwhere":"cust_start_dt >= (date(@cust_start_dt||'-01-01'))" //If cust_start_dt is in querystring, display any start date >= the value
     }
  ]
}
</pre>