<!-- 
/*
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">
{
  "ID": "grid_basic",
  "Title": "Basic Grid",
  "Menu": ["Models","Grids"],
  "Code": [
    "/models/GridBasic_Cust_Listing.json",
    "/models/GridBasic_ReadOnlyColumn.json",
    "/models/GridBasic_ReadOnlyGrid.json",
    "/models/GridBasic_Controls.json",
    "/models/GridBasic_Controls_Links.json",
    "/models/GridBasic_HTML.json",
    "/models/GridBasic_Sort.json",
    "/models/GridBasic_Rowcount.json",
    "/models/GridBasic_ExtendedProperties.json",
    "/models/GridBasic_Static.json"
  ],
  "Demo": [
    { "url": "jsHarmonyTutorials/GridBasic_Cust_Listing", "title": "Basic Grid" },
    { "url": "jsHarmonyTutorials/GridBasic_ReadOnlyColumn", "title": "Read-Only Column" },
    { "url": "jsHarmonyTutorials/GridBasic_ReadOnlyGrid", "title": "Read-Only Grid" },
    { "url": "jsHarmonyTutorials/GridBasic_Controls", "title": "Grid Controls" },
    { "url": "jsHarmonyTutorials/GridBasic_Controls_Links", "title": "Grid Links" },
    { "url": "jsHarmonyTutorials/GridBasic_HTML", "title": "Grid HTML Controls" },
    { "url": "jsHarmonyTutorials/GridBasic_Rowcount", "title": "Grid Rowcount" },
    { "url": "jsHarmonyTutorials/GridBasic_Static", "title": "Static Grid" }
  ]
}
</script>
<h3>Introduction</h3>
The "grid" layout is used to view or edit multiple records.  Grids can be either read-only or editable.

<%-getScreenshot('jsHarmonyTutorials/GridBasic_Cust_Listing?popup=1','Basic Grid')%>

Syntax for a basic grid is as follows:
<pre>
"MODELID": {
  "layout":"grid",              //Required - define the model as a grid
  "table":"TABLE_OR_VIEW",      //Database table or view used as a data source
  "caption":["SINGULAR_CAPTION","PLURAL_CAPTION"], //Caption defining the data being edited
  "fields":[                                       //List of database fields
    {"name":"FIELD_NAME", "caption":"CAPTION"}
  ]
}
</pre>
For example:
<pre>
"Cust_Listing": {
  "table":"cust",
  "layout":"grid",
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_name","caption":"Customer"}
  ]
}
</pre>

A grid's datasource is a database table or view.  The database table or view is defined by the "table".  Custom SQL can be used for more advanced scenarios, such as grids that display data from multiple tables.<br/>
<br/>
The model caption described the singular and plural names of the data items.  If the caption is not defined, it will default to ["Item","Items"].<br/>
<br/>
Each grid field's "name" should match the database column name.  The field caption will be displayed as the column heading.<br/>

<h3>Data Types and Primary Key</h3>
Each grid field needs a datatype.  Grids with insert / update / delete statements also require a primary key.  Read-only grids can operation without a primary key, however the "nokey" attribute should be set to true so that it does not generate warnings that the primary key is missing.  In most scenarios, the primary key and data types can be automatically recognized based on the database schema, if Config.system_settings.automatic_schema is enabled.<br/>
<br/>
Data types can alternatively be manually defined as follows:
<pre>
"fields": [
  {"name":"cust_id","type":"bigint","key":1,"caption": "ID"},
  {"name":"cust_name","type":"varchar","length":"50","caption":"Customer"}
]
</pre>
Additional information, including a list of all data types, is available in the <a href="/tutorials/field_datatypes">Field Data Types</a> tutorial.

<h3>Actions and Read-only Fields and Grids</h3>
Actions define whether the data can be "B" (Browsed), "I" (Inserted), "U" (Updated), or "D" (Deleted).  Actions can be defined at the field or model level.<br/>
<br/>
An individual field can be made read-only by setting the field action to "B":
<pre>
"fields":[
  {"name":"cust_id","caption": "ID"},
  {"name":"cust_name","caption":"Customer","action":"B"}
]
</pre>
<%-getScreenshot('jsHarmonyTutorials/GridBasic_ReadOnlyColumn?popup=1','Read-only Column')%>

An entire grid can be made read-only by setting the model action to "B":
<pre>
"Cust_Listing": {
  "table":"cust",
  "layout":"grid",
  "action":"B",
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_name","caption":"Customer"}
  ]
}
</pre>
<%-getScreenshot('jsHarmonyTutorials/GridBasic_ReadOnlyGrid?popup=1','Read-only Grid')%>


Additional information on actions and roles can be found in the <a href="/tutorials/model_actions">Actions / Roles</a> tutorial.

<h3>Grid Fields / Controls</h3>
The "fields" property defines the fields that are pulled from the database.  The database SQL statements will be automatically generated based on the fields.  Hidden columns, such as those used only for searching or linking to other forms, also need to be defined in the fields section.<br/>
<br/>
Fields controls can be modified using the "control" property:
<pre>
{
  "fields":[
    {"name":"cust_id","caption": "ID","control":"textbox_VS"},
    {"name":"cust_name","caption":"Customer","control":"label"},
    {"name":"cust_sts","caption":"Status","control":"dropdown_M"}
  ]
}
</pre>
<%-getScreenshot('jsHarmonyTutorials/GridBasic_Controls?popup=1','Grid Controls')%>

Labels can also link to other models and popup forms:
<pre>
{
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_name","caption":"Customer","control":"label","link":"update:Cust"}
  ]
}
</pre>
<%-getScreenshot('jsHarmonyTutorials/GridBasic_Controls_Links?popup=1','Grid Controls - Links')%>

Controls can be customized in a variety of ways.  See the <a href="/tutorials/fields_standard_controls">Fields</a> tutorials for more information.

<h3>Unbound / HTML Fields</h3>
Unbound fields can be used to display custom links, images, and interactive UX elements in grid rows.  Any field without a "type" property will be unbound.  Unbound HTML fields should have the "control" set to "html", and "value" set to the HTML / EJS code:

<pre>
{
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_name","control": "hidden"},
    {"name":"cust_name_disp","html":"<b>Customer: </b><#=datarow.cust_name#>","caption":"Customer"}
  ]
}
</pre>

The following EJS variables are available for evaluating row HTML:
<pre>
{
  datarow,  //The current data row, ex:  { cust_id: 1, cust_name: "ACME Industries" }
  rowid,    //The # of the current row being rendered
  xejs,     //EJS extension functions (XExt.xejs)
  jsh,      //The jsHarmony client object
  instance  //The name of the jsHarmony client instance
}
</pre>

<%-getScreenshot('jsHarmonyTutorials/GridBasic_HTML?popup=1','Grid Controls - HTML')%>

Any database columns used in the HTML expression, that are not displayed on the grid, should be added as "hidden" columns, so that the data can be loaded into the datarow object.

<h3>Sort</h3>
Clicking on the column header will sort the grid by that column.  Back-button history will also be added for the sort action.<br/>
<br/>
The following properties are available to manage sort behavior:
<pre>
Model:
  //Model's Default Sort Order
  "sort": [ { "item_name": "asc" }, { "cust_name": "desc" } ]
  //Model's Default Sort Order - Alternate Syntax
  "sort": [ "^item_name", "vcust_name" ]
Field:
  "sqlsort": "ifnull(cust_name,cust_id)"
  "disable_sort": true
</pre>

<h4>model.sort</h4>
The model's default sort order is defined using the "sort" property - an array of field names and their sort direction.  There are two syntax options available for the sort array:
<ul>
  <li>
    Standard Syntax:<br/>
    Ascending: { "FIELD_NAME", "asc" }<br/>
    Descending: { "FIELD_NAME", "desc" }
  </li>
  <li>Short Syntax:<br/>
    Ascending: "^FIELD_NAME"<br/>
    Descending: "vFIELD_NAME"
  </li>
</ul>

<h4>field.sqlsort</h4>

Each field can have a custom SQL sort expression using the "sqlsort" property:
<pre>
{
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_name","caption":"Customer","sqlsort":"ifnull(cust_name,cust_id)"}
  ]
}
</pre>

<h4>Disable Sort by Field</h4>
Setting "disable_sort" to true on the field will prevent the column from being sorted.

<h4>Advanced Sorting<h4/>
Advanced sorting, such as always keeping one of the rows on top, can be implemented by overriding the model.sqlselect expression.  Additional information can be found in the <a href="/tutorials/model_database_sql">Model Database / SQL</a> tutorial.

<h3>Searching</h3>
Searching is automatically added to each grid.  Search the grid by clicking on the "Search" icon.
<%-getScreenshot('jsHarmonyTutorials/GridBasic_Cust_Listing?popup=1','Grid Searching', { onload:function(){ return new Promise(function(resolve){
  jshInstance.$('.xsearchbutton').click();
  window.setTimeout(resolve, 1000);
}); }, beforeScreenshot:function(jsh, page, cb){
  page.click('.xsearch_column').then(cb).catch(function (err) { jsh.Log.error(err); });
} })%>

<%-getScreenshot('jsHarmonyTutorials/GridBasic_Cust_Listing?popup=1','Grid Searching - ID', { onload:function(){ return new Promise(function(resolve){
  jshInstance.$('.xsearchbutton').click();
  window.setTimeout(function(){
    jshInstance.$root('.xsearch_column').val('cust_id').change();
    return resolve(); 
  }, 1000);
}); }, beforeScreenshot:function(jsh, page, cb){
  page.click('.xsearch_comparison').then(cb).catch(function (err) { jsh.Log.error(err); });
} })%>

<%-getScreenshot('jsHarmonyTutorials/GridBasic_Cust_Listing?popup=1','Grid Searching - Customer', { onload:function(){ return new Promise(function(resolve){
  jshInstance.$('.xsearchbutton').click();
  window.setTimeout(function(){
    jshInstance.$root('.xsearch_column').eq(0).val('cust_id').change();
    jshInstance.$root('.xsearch_add').click();
    jshInstance.$root('.xsearch_column').eq(0).val('cust_name').change();
    return resolve(); 
  }, 1000);
}); }, beforeScreenshot:function(jsh, page, cb){
  page.click('.xsearch_expression_0 .xsearch_comparison').then(cb).catch(function (err) { jsh.Log.error(err); });
} })%>

Each search column has a comparison type based on the datatype.  For instance, strings have a "contains" comparison type, while numbers have a "greater than" comparison type.<br/>
<br/>
Search expressions can be "and" expressions, where only the rows that match all of the criteria are returned.  Alternatively, clicking on the "and" label will change the search expression to an "or" expression.  "Or" expressions return all rows that match any of the search parameters.<br/>
<br/>
Search queries will update the URL, and can be bookmarked by the browser.

<h3>Paging</h3>
When the user scrolls to the bottom of the screen, the next "page" of data is automatically loaded and added to the bottom of the grid.  The number of rows per "page" can be configured per-model via the "rowlimit" property:
<pre>
{
  "rowlimit": 50
}
</pre>
The Config variable "default_rowlimit" in app.config.js sets the default value of the rowlimit parameter.<br/>
<br/>
If necessary, automatic paging can be disabled via the "disableautoload" property:
<pre>
{
  "disableautoload": true
}
</pre>
With automatic paging disabled, the user will need to manully click "Load More" to load the next page of data.  This can be useful in situations where multiple grid are stacked on the same screen.

<h3>Number of Rows</h3>
The total number of rows returned for the current search can be displayed at the top of the grid by using the "grid_rowcount" property.
<pre>
{
  "grid_rowcount": true
}
</pre>
<%-getScreenshot('jsHarmonyTutorials/GridBasic_Rowcount?popup=1','Grid Rowcount')%>

<h3>Static Grids (Fast Performance)</h3>
In applications that require rendering thousands of rows, the standard grid may need to be optimized for performance.  There are two options:
<ul>
  <li>Use a custom grid template (for fastest performance)</li>
  <li>Use a static grid (disabling any data binding or dynamic evaluation logic)</li>
</ul>
Custom grid templates are covered in the <a href="/tutorials/grids_custom_templates">Custom Grid Templates</a> tutorials.<br/>
<br/>
Static grids disable any data binding logic, and can be used when all of the following conditions are met:
<ul>
  <li>The only model action is "B" (browse)</li>
  <li>No formatting is required client-side</li>
  <li>No dynamic EJS tags are used</li>
  <li>All controls are labels</li>
</ul>
Static grids are defined using the "grid_static" property:
<pre>
{ 
  "GridBasic_Static": {
    "table":"jsharmony_number",
    "layout":"grid",
    "caption":["Number","Numbers"],
    "grid_static": true,
    "rowlimit": 1000,
    "fields":[
      {"name":"number_val","caption": "Number", "key": true}
    ]
  }
}
</pre>
<%-getScreenshot('jsHarmonyTutorials/GridBasic_Static?popup=1','Static Grid',{height: 400})%>
