<!-- 
/*
Copyright 2020 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": "Reports",
  "ID": "reference_reports",
  "Menu": ["Language Reference"]
}
</script>

<h3 class="tutorials_intro">Reports</h3>

<h3>Enabling Reports</h3>
In order to run reports, the "jsharmony-report" module must be installed using NPM, and the report extension should be loaded in the app.config.js file:
<pre>
jsh.Extensions.report = require('jsharmony-report');
</pre>

<h3>Report Model Definition</h3>

<pre>
model := {
  //Same top-level and field properties as [html]<i><a href="/tutorials/reference_forms">Forms</a></i>[/html]
  "layout": "report",
  "title": "Report Title",
  "actions": "B",
  "roles": { "C_ADMIN": "B" },
  "db": "default", //Optional property - use an alternate database connection
  "menu":"reports",

  //Reports have the following additional properties
  "format": "pdf", //pdf or xlsx
  "description": "Report Description",
  "template": "canonical_report.ejs", //Report source HTML/EJS template
  "pagesettings":  { //Any custom pagesettings
    "format": "Letter", //Letter,Legal,Tabloid,Ledger,A0,A1,A2,A3,A4,A5,A6,A7
    "landscape": false,
    "printBrackground": true,
    "margin": {
      "top": "1cm",
      "bottom": "1cm",
      "left": "1cm",
      "right": "1cm",
    },
    "scale":1, //0.1 to 2
  },
  "fonts": [
    {
      "font-family": "Material Icons",
      "font-style": "normal",
      "font-weight": 400,
      "src": "jsharmony/public/fonts/Material-Icons-Regular.woff2",
      "format": "woff2",
      "local": ["Material Icons","Material-Icons-Regular"], // (Optional "local" font source - Might cause invalid font on some versions of Chromium)
      "css": ".material-icons { font-family: 'Material Icons'; font-weight: normal; font-style: normal; font-size: 24px; display: inline-block; line-height: 1; text-transform: none; letter-spacing: normal; word-wrap: normal; white-space: nowrap; direction: ltr; -webkit-font-smoothing: antialiased; text-rendering: optimizeLegibility; -moz-osx-font-smoothing: grayscale; font-feature-settings: 'liga'; }"
    }
  ],
  "fields": [
    {"name":"cust_id","type":"bigint","datalock":{"cust_id":"cust_id"},"nl":true,
        "control":"dropdown_L","actions": "B",
        "lov": { "sql": "select cust_id as code_val,cust_name as code_txt from cust where 1=1 %%%DATALOCKS%%% order by cust_name", "datalock":{"cust_id":"cust_id"}, "blank": "(All)" },
        "default": "js:XExt.UndefinedBlank(jsh._GET['cust_id'])", //Set a default value for the field if in query string
        "caption":"Company Name"},
    {"name": "MDate","type": "date","actions": "B","nl": true,
      "control": "date_mmddyy","sample": "01/28/11","validate": [ "Required" ],
      "default": "js:moment().format()",
      "caption": "Start Date"
    },
    {"control":"html","value":"Sample Comment.","nl": true},
    //Button with onclick event to Run the Report in the browser
    {"control":"button","value":"Run Report","controlstyle": "padding:3px 8px;", "onclick": "jsh.XPage.RunReport();","nl": true},
    //Button with onclick event to Test the Job Queue - show a listing of what will be sent to the Queue
    {"control":"button","value":"Test Job Queue","controlstyle": "padding:3px 8px;", "onclick": "jsh.XPage.TestQueue();"},
    //Button with onclick event to Start the Job Queue
    {"control":"button","value":"Email to Clients","controlstyle": "padding:3px 8px;", "onclick": "jsh.XPage.RunQueue();"}
  ],
  //Code to disable an element if its value has been set in the query string
  "onload": "if(xmodel.controller.form.Data.cust_id){ jsh.XPage.Disable(jsh.$root('.cust_id.xelemreports_REPORTNAME')); }",
  //onload,oninit,oninsert
  //For onrender / onrendered:
  //  If model.format=pdf, report = { page, content { header, body, footer } , data, params }
  //  If model.format=xlsx, report = { workbook, data, params }
  "onrender": "_this.onrender(report, callback, require, jsh, modelid)", //Server-side function called after data is available, before rendering
                                                                           //  Return cancelRender=true in callback(err, cancelRender) to override rendering
  "onrendered": "_this.onrendered(report, callback, require, jsh, modelid)", //Server-side function called after rendering complete, before generating PDF or returning file to the user
  //Page header can contain styles and images, and refer to the page number.  Any image used in the header must be also included in the report body.  Set body image style to "display:none" if necessary.
  "ongetfilename": "return callback('filename.pdf');", //Server-side function to set the report filename
  //Parameters: callback, model, Q, P, req, dbdata
  //Return callback(new_filename)   Return callback(null) to use default filename
  "pageheader" : "<h3 style=\"font-size:20px;font-family:'Roboto';\"><center>&lt;%=data.GPP[0].gpp_name%&gt;<img width='200' style='position:fixed;right:-0px;top:-12px;' src='file:&lt;%='/'+'/'+'/'%&gt;&lt;%=jsh.Config.datadir%&gt;../public/images/logo.png' align='right' /></center></h3>",
  //Page footer is similar to the page header.  Refer to page numbers with &lt;%-pageNum%&gt; of &lt;%-numPages%&gt; max pages.
  "pagefooter" : "<p align='center' style=\"font-family:'Roboto';\">Page Footer &lt;%-pageNum%&gt; / &lt;%-numPages%&gt;</p>",
  //EJS content for the body of the report
  "reportbody": "",
  //Data used by the report
  "reportdata" : {
    //Simple non-joined header data.  Datalock ignore is turned on since we are accessing global data
    "GPP": { "sql": "select (select gpp_val from gpp where gpp_attrib='NAME') as gpp_name", "nodatalock": ["cust_id"] },
    //Nested data example with multiple children.  Data is merged up the tree.
    //Use &lt;pre&gt;&lt;%=JSON.stringify(data,null,2)%&gt;&lt;/pre&gt; in the Report body to debug and print out the full resulting data structure
    //All data is processed in memory several times
    //Try to limit the amount of data returned, do not use * (select all) if possible.
    "C":{
      "sql":"select * from cust where cust_id=isnull(@cust_id,cust_id) and cust_startdt<=@mdate %%%DATALOCKS%%%",
      "children":{
        "CA":{
          "bindings":{"cust_id":"cust_id"},
          "sql":"select * from cust_addr where cust_id in (select cust_id from cust where cust_id=isnull(@cust_id,cust_id) and cust_startdt<=@mdate %%%DATALOCKS%%%)"
        },
        "NTYPE":{
          "bindings":{"cust_id":"cust_id"},
          "sql":"select distinct note.cust_id,note_type from note inner join cust on cust.cust_id=note.cust_id where note.cust_id=isnull(@cust_id,note.cust_id) and cust_startdt<=@mdate %%%DATALOCKS%%%",
          "children":{
            "N":{
              "bindings":{"cust_id":"cust_id","note_type": "note_type"},
              "sql":"select note.cust_id,note_type,note_euser,format(note_etstmp,'MM/dd/yyyy') as note_etstmp,note_body from note inner join cust on cust.cust_id=note.cust_id where note.cust_id=isnull(@cust_id,note.cust_id) and cust_startdt<=@mdate %%%DATALOCKS%%%"
            }
          }
        }
      }
    }
  },
  //Use the batch queue to run the report once for each row of parameter, and then merge the result into a PDF
  "batch": {
    "sql": "select cust_id from cust where cust_id=ifnull(@cust_id,cust_id) %%%DATALOCKS%%% order by cust_name"
  },
  //SQL used by the Job Queue to define how the elements will be queued.
  "jobqueue": {
    "sql": "select 'MAIN' as rqst_source, 'C' as doc_scope,cust_id as doc_scope_id,'DOCUMENT' as doc_ctgr,'Sample Report' as doc_desc, cust_email as email_to,null as email_cc,null as email_bcc,'SMPLRPT' as email_txt_attrib,'filename:123.pdf' as email_attach, 'C' as note_scope,cust_id as note_scope_id,'S' as note_type,'Report created and sent' as note_body, cust_id from cust where cust_id=isnull(@cust_id,cust_id) and cust_startdt<=@mdate %%%DATALOCKS%%%"
  }
}
</pre>