【Publisher】Kendo DataSource using Sharperlight Query - UI:Grid

I would like to introduce providing data to the KENDO Datasource using Sharperlight Published Query. Sharperlight Published Query typically provides web report via the Sharperlight RESTful service. However, by differentiating the URL parameters, it can return data in JSON format. This time, we will utilize the data in JSON format.

# Creating a Published Report for the KENDO DataSource #
This will be a Published Query to supply data to the KENDO DataSource in JSON format. Launch the `Publisher` from the Sharperlight application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6f3290a2-d1c7-25f0-5ca9-cc53b6ffe754.png)

## General Items ##
Once the Publisher is launched, begin creating a new published query by clicking the `New` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c1355886-c0b9-32a7-bd56-f2f2d46907a5.png)
First, enter the values for `Code` and `Group`. This combination will form a unique ID.
Also, fill in the `Title` and `Report Title`. It's advisable to provide details like `Author` and `Version`, especially when multiple individuals or groups are involved in creating the published query.
Once you've completed this, try clicking the `Apply` button. You should see the following message.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a44269fe-0b38-1bea-c0eb-265a355a780a.png)
This is because there is a rule that **one Query per Published Query**. So, let's start creating the query by clicking the `Edit Query` button.

## Query Creation ##
A common `Query Builder` shared across the Sharperlight applications will be launched. This user interface is also used in the Sharperlight Excel Add-in.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bfa51153-72ae-e214-d318-b742ef7739fa.png)
The user interface is structured into three areas: **Filters**, **Selection**, and **Output**.
Since the purpose of the `Query Builder` is SQL statement design, the **Filters** area corresponds to the FROM and WHERE clauses. The **Output** area is for designing the SELECT clause. The **Selection** area displays fields and JOIN information belonging to the specified  table. You can choose fields from this area for filtering or output.
Now, let's start creating the query. Choose the **Product** (datamodel). This time, we will use **SAP Business One** datamodel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f5627475-1d45-e154-e624-cb09de7cd1d6.png)
Specify **OEC Computers AU** for the `Company`. Next, select the `Table`. Similarly, choose **A/R Sales Invoice** from the search window.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4b96cad6-47a9-8934-ef44-518de02b83aa.png)
The **Selection** area will display the list of fields and JOIN information for the **A/R Sales Invoice** table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5ce99c18-dcf9-e559-4bf2-0cb737195384.png)
Also, four mandatory filters will be automatically displayed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/593e7362-b38e-090b-49ef-dd1681b46ce5.png)
This is because when the **A/R Sales Invoice** table is selected, these four fields are designated as mandatory filters in the **SAP Business One** data model. This time, specify **N** (No) for the mandatory filter **Canceled**. Leave the other three mandatory filters as \<ALL>.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/38c60b99-474f-a299-c20d-f268821d94de.png)
<cr>
Next, define the output.
From the list of fields in the **Selection** area, double-click or drag & drop the following items to the **Output** area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2efc1299-e8a7-cc7c-3e7b-4a51ceb60e94.png)
Finally, edit the output names (unique names used internally) for the seven fields you have set as output items.
Select **Change Name** from the right-click menu and make the necessary edits. After editing, it should look like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/54054a95-55b8-607d-7ed8-33c98c4fea98.png)
That completes it for this time.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/996faf2b-ba1c-ec91-1a6a-7ae8f2c3bdc1.png)
You can try running the query by using the `Preview` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f1b82f2d-c4fa-a7c4-a41a-f8e8aa206951.png)
Click the `OK` button to save the query and return to the Published Query dialog. Then, click `OK` again to save the published query. The query is now ready.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1b5e6eb6-8512-0e88-a63a-88c9b4a3fd61.png)

# Creating HTML Using KENDO GRID and Datasource #
The HTML code you are about to create is designed with the assumption that it will later embed another Sharperlight published query and make it accessible through the Sharperlight RESTful service. Therefore, it includes specific Sharperlight tags. I will guide you through the key points, including those related to Sharperlight-specific tags. The finalized HTML and JavaScript code looks like this.
```
<!DOCTYPE html>
<html>
    <head>
        <title>Order</title>
        <meta charset="utf-8"/>
        <base href="{*Url.Root}"/>
        <link rel="shortcut icon" type="image/x-ico" href="favicon.ico"/>
        <link rel="icon" type="image/x-ico" href="favicon.ico"/>
        
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.default.min.css" />
	    <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.common.min.css" />
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.default.mobile.min.css" />
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.silver.min.css"/>
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.silver.mobile.min.css" />
        
        <script type="text/javascript" src="{*Url.Root}Resources/jquery/jquery.min.js"></script>
                
        <script type="text/javascript" src="{*Url.Root}Resources/kendo/js/kendo.all.min.js"></script>
        <script type="text/javascript" src="{*Url.Root}Resources/kendo/js/kendo.timezones.min.js"></script>
    
    </head>
    <body>
        <style type="text/css">
            body {
                height: 1500px;
                font-family: Tahoma;
                font-size: 11px;
            }          
 </style>
        
        <div id="grid" style="width: 1500px"></div>
        
        
        <script type="text/javascript">
            var gURL_Read = "{*Row.DatasourceURL.text}";
            var gKGrid = null;
            
            function _Feed_Data_To_Grid(dsURL){
                try{
                    var dsItems = _Datasource_Create(dsURL);
                    if (dsItems != null){				
                        dsItems.read().then(function (){
                            gKGrid.setDataSource(dsItems);
                            gKGrid.refresh();      
                        });
                    }else{
                        console.log("_Feed_Data_To_Grid(): " + "No Dataset populated");
                    }
                }catch (err){
                    console.log("_Feed_Data_To_Grid(): " + err.message);
                }
            };

            

            function _Datasource_Create(dsURL){
                try{
                    var ds = new kendo.data.DataSource({
                        serverFiltering: false,
                        transport: {
                            read: {
                                datatype: "jsonp",
                                url: dsURL
                            }
                        },
                        schema: {
                            model: {
                                id: "DocId",
                                fields: {
                                    DocId: { type: "number" },
                                    DocDate: { type: "datetime" },
                                    DocNumber: { type: "string" },
                                    CusCode: { type: "string" },
                                    CusName: { type: "string" },
                                    Quantity: { type: "number" },
                                    RowTotal: { type: "number" }
                                }
                            }
                        }
                    });
                    return ds;
                }catch (err){
                    console.log("_Datasource_Create(): " + err.message);
                    return null;
                }
            };
            
            
            // Start from here ==================================================
            $(document).ready(function() {
            
                gKGrid = $("#grid").kendoGrid({
                    columnMenu: {
                        filterable: false
                    },
                    height: 580,
                    editable: "incell",
                    pageable: false,
                    sortable: true,
                    navigatable: true,
                    resizable: true,
                    reorderable: true,
                    groupable: false,
                    filterable: false,
                    columns: [
                        {
                            field: "DocDate",
                            title: "Document Date",
                            width: 80
                        },{
                            field: "DocNumber",
                            title: "Document Number",
                            width: 100
                        },{
                            field: "CusCode",
                            title: "Customer Code",
                            width: 80
                        },{
                            field: "CusName",
                            title: "Customer Name",
                            width: 80
                        },{
                            field: "Quantity",
                            title: "Quantity",
                            width: 80
                        },{
                            field: "RowTotal",
                            title: "Row Total",
                            width: 80
                        }]
                }).data("kendoGrid");
            
                console.log(gURL_Read);
                _Feed_Data_To_Grid(gURL_Read);

            });
        
        </script>
    </body>
</html>
```
Certainly, let's go through the key points of the HTML and JavaScript code.
## \<head> ##
1. The `{*Url.Root}` tag: Replaced at runtime with the base URL of the Sharperlight RESTful service
2. Links to the JavaScript and CSS files packaged with Sharperlight

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7999af5a-637e-d336-df1c-f5e659cc70ac.png)
## \<body> ##
In the initial HTML code section, a div is prepared to create a KENDO Grid control.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/41d545db-21e8-c379-fa52-cb6a9d6334dd.png)
In the script section, there are three components as follows:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/12857f4d-7bb3-9bd8-1dde-25db70d2f205.png)
1. Preparing the URL to retrieve a datasource through the Sharperlight RESTful service and the variable for the KENDO Grid control. The URL has been defined within the query definition of the published query where this HTML code is intended to be embedded. `{*Row.DatasourceURL.text}` is the tag used to reference it
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1d6ea9bc-1f89-f1f5-3901-5488cdb05ece.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e8015dad-bee8-6a1d-791e-6b732235c1df.png)
2. There are two functions. _Feed_Data_To_Grid() is a function that supplies data to the KENDO Grid, and _Datasource_Create() is a function that creates a KENDO Datasource. _Datasource_Create() function is called from _Feed_Data_To_Grid() function.
3. The familiar $(document).ready function where everything begins. Here, the definition of the KENDO Grid control and the call to the _Feed_Data_To_Grid() function are initiated.

### _Feed_Data_To_Grid function ###
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fb095d13-3c22-12b0-d2dd-a5f003ed63b6.png)
### _Datasource_Create function ###
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a9a2af84-97f3-afe9-7b16-5f91c7241d2c.png)
### $(document).ready function ###
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ce7e6b1c-93e1-f24c-47b1-6f27d918c251.png)
That's it. Save this HTML code as "xxxxxxxxx.html".
# Creating a Published Query for hosting the HTML code #
Launching `Publisher` from the Sharperlight application menu.
Starting the creation process by clicking the "New" button.

## General ##
**Code** and **Group** entries are required at a minimum. You may choose your preferences for **Titles**, etc.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2c6cdc2a-b406-5515-1420-559416667dea.png)

## Embedding HTML code ##
Use these three options on the `Options` tab.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/525dca8b-bfaa-1508-3cbb-7bb52f11344b.png)
1. In the context of `Resource` options, embed the HTML code (file) created earlier as a resource. Click the `Edit` button to configure the resource settings.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/729545f4-6c61-b680-6b65-7157f94b2e5a.png)
Begin embedding with the `New` icon. A new item will be added to the top grid.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/14fa8231-a1d3-3d4a-d41f-99aa54842d52.png)
Specify the HTML file path here by using the browse icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8783f698-99a5-cc5d-8b65-64f58150ff00.png)
Finally, copy the **Resource Code Tag** by clicking the `Copy` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8126f70c-0282-a0eb-e013-e48bab85b134.png)
Confirm synchronization again with the `Sync` button, then save by clicking the `Close` button.
2. In the `Custom HTML` option, actually use the resource. Turn on the `Enable` option and click the `Edit` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2fe1ea52-0ca8-a7ca-b1e3-2a5e0e98cae2.png)
The Custom HTML editor will open. Since there is already sample code, delete it all. Paste the **Resource Code Tag** that you copied to the clipboard earlier.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d7fdb120-89ac-c1d8-1020-d814208e6f1b.png)
Embedding is now complete. Save by clicking the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a89fa07f-652f-4d71-9c76-71738c76d338.png)
Prompt display in the browser is not necessary, so turn off the prompts.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3e1b778c-97dd-a71a-cc2b-a5183793c93b.png)

## Definition of a query ##
This public query is simply a container for the previous HTML code, so a query is not necessary. However, as mentioned earlier, there is a rule of **One Query per Published Query**. Therefore, we will define a query.

Firstly, it is preferable for the query to return as few records as possible. Therefore, we will define a query that returns only one record. It would be suitable to use the following products **System** and specify the **User Account** table for this query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7d0b5c4d-7480-ab84-bb28-31054984adcb.png)
In the output area, specify constants or other values you want to pass to the embedded HTML code. Here, you will use Sharperlight Expression to specify the URL for the datasource. This corresponds to the part mentioned at the beginning of the **\<body>** description.

Choose **Add Expression** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eb89135d-061a-f791-8cdd-0b9340e97cc8.png)
Search for the syntax using the keyword **REST** and transfer the found Service URL syntax to the top text editor.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b0d5d68e-3ca0-08c0-973c-bb84ef999b9a.png)
Further edit the syntax as follows:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3c8e7a1a-01c6-30c1-6c9f-84e333d397fb.png)
Save by clicking the `OK` button. Edit the **Description** and **Name** as follows. Particularly, the **Name** is important. This name is referenced in the HTML code as `{*Row.DatasourceURL.text}`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cb46343f-b83c-8ade-36b2-682093eba5be.png)

That's it. Save by clicking the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f9ebb486-369c-80e3-e916-f943e1c58be0.png)

Save the published query as well by clicking the `OK` button. Now, select the saved published  query, and choose "Show Report" from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/402ab5aa-dd07-e2b9-8bae-26a6e95f194e.png)

If the Sharperlight service is running, the published query will be displayed in your browser.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2f58514f-8f67-ea9f-bc72-6173705215f3.png)

You can start the Sharperlight service either from the Sharperlight application menu or from the Windows Services dialog.