Configuring Web Services

What is a Web Service?

A Web Service is a method of communication between two applications or electronic devices over web. You can either use pre-defined web services that come along with the SummitAI application or build Custom Services based on your organization's requirement.

Web Services are of two types:

1. Simple Object Access Protocol (SOAP)
2. Representational State Transfer (REST)

SOAP defines a standard communication protocol (set of rules) specification for XML-based message exchange. SOAP uses different transport protocols, such as HTTP and SMTP.

The standard protocol HTTP makes it easier for SOAP model to tunnel across firewalls and proxies without any modifications to the SOAP protocol. SOAP can sometimes be slower than middleware technologies like CORBA or ICE due to its verbose XML format.

REST describes a set of architectural principles by which data can be transmitted over a standardized interface (such as HTTP). REST does not contain an additional messaging layer and focuses on design rules for creating stateless services. A client can access the resource using the unique URL and a representation of the resource is returned. With each new resource representation, the client is said to transfer state.

While accessing RESTful resources with HTTP protocol, the URL of the resource serves as the resource identifier and GET, PUT, DELETE, POST and HEAD are the standard HTTP operations to be performed on that resource.

Provide the Service URL to create a Web Service.

Figure: Edit Service URL

Service URL Example: http://<webserviceurl>/REST/Summit_RESTWCF.svc

Replace <webserviceurl> with base URL.

Web Service Authentication

The default Token-based API authentication is implemented for Web Service from the Sierra HF01 Release onwards. If you upgraded to the Sierra HF01 and want the User ID and Password-based authentication for Web Service, you can enable it. 

Configuration

You can enable the User id and Password-based authentication by adding the following key in the SummitAI web.config file:

<add key="IsAPIKeyDisabled" value="true" />

To preview a Web Service:

1. Select Admin > Basic > Infrastructure > Web Services.

2. Select a URL from the drop-down list.
3. Select a service from the Custom Services list.
4. Fill in all the mandatory fields and then click SUBMIT. The Output Data is displayed.
   
   
   Figure: Web Services Preview page

Field Description

The following table describes the fields on the WEB SERVICES EXPLORER page:

To create Custom Services:

1. Click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
   
   
   Figure: Web Service Designer page
   
   

2. Type in the Web Service Name and specify the number of records to be displayed.

   Note:

   You can also create SQL Views, SQL Table, and SQL Procedure and modify the created fields.

    Create SQL View

   
   Figure: Create SQL View Page

    Create SQL Table

   
   Figure: Create SQL Table Page

   You can add indexes to improve the performance of the tables.

    Create SQL Procedure

   
   Figure: Create SQL Procedure Page

3. Click  icon to view, hide, or customize the Web Service properties. 
   

   
   Figure: Web Service Properties
   
   

   Field Description

   The following table describes the fields on the PAGE PROPERTIES section:

   Fields	Description
   Module Name	Select a Module for the Web Service.
   Active	If selected, the Web Service configuration becomes active.
   User Access	Type in the name of a user to whom you want to provide access to the Web Service.
   View Only	If selected, the user will have read-only access to the Web Service and cannot do any modifications to it.
   Report Description	Specify the description of the Web Service.

4. Select the check box to select DATA SOURCE from the list. You can scroll down to see all the available Data Sources or type in the Data Source name in the search box to search a particular Data Source.

   Note:

   Create Relationship between the Data Sources if you want to use multiple Data Sources.

    Create Relationship

   To create relationship, select a Data Source. Fill in all the required details and click ADD RELATIONSHIP. A new Relationship is added.

   
   Figure: Relationship section
   
   

   Field Description

   The following table describes the fields under the RELATIONSHIP section:

   Fields	Description
   Join Type	Select a Join Type from the list.
   Data Source	If multiple Data Sources are selected then select a Data Source from the list to create a relationship with it.
   Table Alias	Select the alias name for the Data Source.
   Join Field	Select a field from the drop-down list for selected Data Source.
   Foreign Data Source	Select a Foreign Data Source from the drop-down list to create relationship with the selected Data Source.
   Join Field	Select a field foreign Data Source from the drop-down list.

    Add Fields to the Web Service

   To add fields to Web Services:

   1. Drag and drop the attributes from the list to FIELDS and FILTERS section to define the mandatory fields and Filter attributes. You can configure the properties of the attributes in the FIELDS and FILTERS section.
      
      
      Figure: Fields Section
      
      

      Note:

      • Click  icon to copy all the fields.
      • Click  icon to remove all the copied fields.
   2. Upon selecting attributes, PROPERTIES section is displayed (if not displayed, click icon) and configure Properties. Select an attribute on FIELDS/FILTERS column, FIELD/FILTERS PROPERTIES is displayed. If not, click the PROPERTIES icon.
      
      
      Figure: Page Properties for Fields

   Field Description

   The following table describes the fields on the PAGE PROPERTIES section:

   Fields	Description
   Source Name	Displays the Source Name of the attribute.
   Field Name	Displays the Field Name.
   Field Alias	If required, change the Field name.
   Sort	Select the sort order from the drop-down list.
   Function	Select the function of the attribute from the drop-down menu.
   Custom Expression	Specify the custom expression, such as SQL calculations.
   Mandatory	If selected, the corresponding field becomes mandatory.

5. Click GENERATE SQL to generate SQL Query. 

    See Screen Shot

   
   Figure: GENERATE SQL (when Edit Query check box is not selected)
   
   

   
   Figure: GENERATE SQL (when Edit Query check box is selected)
   
   

   Note:

   • The string values should be entered within double quotes. Example: ''Username''.
   • Data Manipulation Language (DML) is not allowed in the SQL query. Example: Delete, Truncate, Drop, Comment, Rename, Merge, Grant, Revoke, Commit, Savepoint, Rollback, Exec, and Execute.
   • If you want to pass any parameters, enter # before and after the respective Field name.

6. Click PREVIEW to view the Output of the ongoing Web Service. 

    See Screen Shot

   
   Figure: Output Preview pop-up page

7. Click SAVE. The Web Service is added to the WEB SERVICE PREVIEW list.

Creating Custom Tables in Web Services:

To Create a New Custom Table:

1. Select Admin > Basic > Infrastructure > Web Services. The WEB SERVICE EXPLORER page is displayed.
2. On the WEB SERVICE EXPLORER page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
3. On the WEB SERVICE DESIGNER page, select Create SQL Table from drop-down list.
4. Specify the SQL Table Name.
   
   
   Figure: WEB SERVICE DESIGNER page: Create SQL Table: SQL Table Name
   
5. Click icon in FIELDS section to view FIELD PROPERTIES.
   
   
   Figure: WEB SERVICE DESIGNER page: FIELD PROPERTIES

6. Type in Field Name (or Column Name) and select the Data Type value from drop-down list. For more information about the fields on the FIELD PROPERTIES, see Field Description.

7. Click ADD NEW FIELD to add a new field (or Column).
   

   Field	Description
   Field Name	Specify the name for the field (or column).
   Data Type	Select the required data type for the field (or column) from the drop-down list. Following are the available data types: nvarchar: Select the data type as nvarchar to store variable characters (letters and numbers) in the field (or column). Note: In the size parameter, specify the maximum column length in characters. int: Select the data type as int to store a whole number (not a fraction) in the field (or column). numeric: Select the data type as numeric to store numbers that have fixed precision and scale. decimal: Select the data type as decimal to store numbers that have fixed scale.   bit: Select the data type as bit to store a single bit with a value of 0,1, or NULL datetime: Select the data type as datatime to store date and time combination in the field (or column). text: Select the data type as text to store text or combinations of text and numbers in the field (or column).
   Auto Identity	Select this check box to define a field (or column) as auto increment field. Note: This field is displayed only for int, numeric, and decimal data types.
   Primary Key	Select this check box to define a field (or column) as Primary Key. Note: Primary keys should contain unique values and cannot have NULL values.

   
   

8. Click GENERATE SQL to generate the SQL Query.
   
   
   Figure: GENERATE SQL (sample screenshot)
   
   
9. Click SAVE to successfully create the SQL Table. Click YES if you want to redirect to the Web Service Explorer page.
   
   
   
   

Creating Table via Excel in Web Services

You will be able to create a new table in webservices using excel. You can also view or modify the created table. You can view the table along with the records unlike creating table with SQL where you can view only SQL queries in the Web Service Explorer. You can also import and export the records in the excel. 

Creating New Table using Excel

The following section provides information on how to create a table using Excel.

1. Select Admin > Basic > Infrastructure > Web Services. The WEB SERVICE EXPLORER page is displayed.
   
   
   Figure: WEB SERVICE EXPLORER page
   
   
2. On the WEB SERVICE EXPLORER page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed. 
   
   
   Figure: WEB SERVICE DESIGNER page
   
   
3. On the WEB SERVICE DESIGNER page, select Create SQL Table from the drop-down list.
   
   
   Figure: WEB SERVICE DESIGNER page: Create SQL Table
   
   
4. Specify the SQL Table Name.
   
   
   Figure: WEB SERVICE DESIGNER page: Create SQL Table: SQL Table Name
   
   
5. Click GENERATE TABLE to create a table using excel import.
   
   
   Figure: WEB SERVICE DESIGNER page
   
   The MASTER IMPORT page is displayed with the following three tabs:
   
   1. UPLOAD EXCEL
   2. MAP COLUMNS
   3. UPLOAD MASTER
   
   
   Figure: MASTER IMPORT page
   
   
6. Specify the required fields on GENERAL tab.
   
   
   Figure: GENERAL
   
   
7. Click on the download icon and it will open your file browser window to select the required Excel file from the location.
   
   
   Figure: Browser Window
   
   
8. Click SUBMIT to import the selected excel sheet as source.
   
   
   Figure: GENERAL
   
   
9. The uploaded excel file will display all of the available sheets inside it and you can choose the required sheet for mapping which needs to be uploaded if the uploaded excel sheet has more than one sheets. Select the required record and click on Select hyperlink. The MAPPING page under the MAP COLUMNS tab is displayed:
   
   
   Figure: MAPPING
   

   Field Description

   The following table describes the fields on the MAPPING page:

   Fields	Description
   Column Name	Displays all the columns name mentioned in the uploaded excel sheet. For Example in the above screenshot, LeaveHistory Excel displays the columns such as Leave Type, FromDate, ToDate,NoofDays, Status, Remarks and ApprovalStatus.
   Data Type	Data Type defines which type of value can be provided for the respective column. Respective column input value should be in same data format. For Example: If Data Type for column name NoofDays is defined as an Integer, then the input value in this column should be an integer only. Select the required data type for the field (or column) from the drop-down list. Following are the available data types: Number - Select the data type as Number to store numbers that have fixed precision and scale. Integer - Select the data type as Integer to store a whole number (not a fraction) in the field (or column). String - Select the data type as String to store variable characters (letters and numbers) in the field (or column). Bool - Select the data type as Bool to store a single bit with a value of 0,1, or NULL. Date - Select the data type as Date to store a date in the field (or column). Note: Format of Date must be DD:MM:YYYY Date Time - Select the data type as Date Time to store date and time combination in the field (or column). Time - Select the data type as Time to store a date in the field (or column). Note: Format of time must be HH:MM:SS By default, it displays the data type related to the column name. You may modify it as per the requirement. For Example: If the Column Name is mentioned as Priority then by default, the Data Type will be displayed as Number.
   Primary Key	Select this check box to define a field (or column) as Primary Key. Note: Primary keys should contain unique values and cannot have NULL values. If Primary Key has duplicate values it will be highlighted during validation checks.
   Mandatory	Select the Mandatory checkbox to indicate if the associated column is the mandatory column. Note: If Mandatory Key has null value or left empty then it will be highlighted during validation checks.
   Autogenerate?	Autogenerate? field is displayed only if Data Type is Number or Integer. If Autogenerate? checkbox is selected it indicates that the value for the column will be populated automatically as the records are entered and in the series of the existing series. Figure: Autogenerate? For example: If a new table is created and 5 records are being uploaded. The auto-generate column will get automatically populated from 1 to 5. If there is an already existing table with 10 records and 5 more records have to be added. Then the auto-generate column will automatically take the subsequent series i.e. 1 to 10 serial no. already exists. 5 new records will take 11 to 15.
   PREVIOUS	Click PREVIOUS button to go back to the previous screen.
   NEXT	Click NEXT button to proceed further.
   CANCEL	Click CANCEL button to cancel the immediate action. It will redirects you to Web Service Designer page.

10. Click NEXT. The following popup is displayed:
    
    
    Figure: Popup page 
    
    

11. The popup page is displayed with two options:
     
    1. CONTINUE WITH THIS DATA - Click this option if you want to continue with the already existing data from the excel.
    2. DOWNLOAD MASTER - Click this option if you want to download the excel template having the defined columns with the configured data types, and add the records in the excel template.
    
    
12. If you click CONTINUE WITH THIS DATA then the PREVIEW page under the UPLOAD MASTER tab is displayed:
    
    
    Figure: PREVIEW
    

    The PREVIEW page displays all of the records which are imported via excel. There will be a few validation checks before you do bulk upload. 
    
    The following table describes a few validation checks while bulk uploading. 

    Symbol	Meaning
    	If the data in the field does not match the data type mapped against that field, then it displays the data mismatch. For Example: If you have defined Data Type for a column name NoofDays as an integer but the input value is Five (text string) then it's a data mismatch.
    	Mandatory columns can not be left blank. For Example: If you have defined Data Type for a column name Employee ID as mandatory then this column can not be left blank.
    	In Excel data set if a single column which is mentioned as Primary Key has same values then that record is duplicate.  If a column is selected as Primary Key, then that column will become a unique identifier. This column can not have duplicate entries and will be identified during validation checks.  For Example:  If you have defined Data Type for a column name Employee ID as primary then this column can not have duplicate entries.

    
    Field Description

    The following table describes the fields on the PREVIEW page:

    Fields	Description
    Discard	Select the Discard checkbox to indicate that the selected record will not be included while upload.
    Column	Displays all columns names which are imported via excel.
    PREVIOUS	Click PREVIOUS button to go back to the previous screen.
    BULK UPLOAD	Click BULK UPLOAD button to upload all the records displayed on PREVIEW tab.
    CANCEL	Click CANCEL button to cancel the immediate action. It will redirects you to Web Service Designer page.

13. Click BULK UPLOAD. The following popup is displayed with total count of records, count of uploaded records and count of failed records.
    
    
    Figure: BULK UPLOAD popup 
    
    Download failed records - Click Download failed records to download the excel with failed records which were failed to upload. You can make corrections to the records and upload again. 
    OK & Proceed - Click OK & Proceed button will direct to the Table List Screen with the table having the records which are uploaded. A sample screenshot is as follows:
    
    
    
    

Various Methods to Import Data into Custom Table

The Administrators can use the following methods to import data into the Custom Table:

• Import the data form source systems (such as: AD, SAP or HR) via API
• Backend Import: You can import a file (.csv,.txt, etc.) into your database using SQL Server Management Studio.
• SQL Query or Stored Procedure

Importing Custom Data into Custom Table Using Stored Procedure:

To Import Data into Custom Table Using Stored Procedure:

1. On the WEB SERVICE EXPLORE page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
2. On the WEB SERVICE DESIGNER page, select Create SQL Procedure from drop-down list.
3. Specify the SQL Stored Procedure Name.
4. Select Edit Query check box.

5. Type in the stored procedure to insert new records in a table.
   

6. Click SAVE to insert values into table.
   
   

ACTIONS

This section explains all the icons displayed on the ACTIONS panel of the WEB SERVICE EXPLORER page.

TABLE LIST

Click TABLE LIST to display the list of all tables which are created using Create SQL Table option on Web Service Designer page.


Figure: TABLE list page

Field Description

The following table describes the fields on the TABLE page:

To View the Table

Perform the following steps to view the table:

1. Navigate to Admin > Web Services. The WEB SERVICE EXPLORER page is displayed:
   
   
   Figure: Table List
   
   
2. Click Table List under ACTIONS panel. The TABLE list page is displayed:
   
   
   Figure: TABLE list page 
   
   
3. Click Download Excel hyperlink under the View Data column. The following popup is displayed:
   
   
   Figure: Download Excel Popup
   
   
4. Click DOWNLOAD EXCEL.
   
   
   Figure: Download Excel
   
   The excel file is downloaded at the bottom-left of the page.  

To Modify the Table

Perform the following steps to edit the table:

1. Navigate to  Admin > Web Services. The WEB SERVICE EXPLORER page is displayed:
   
   
   Figure: WEB SERVICE EXPLORER
   
   
2. Click Table List under ACTIONS panel. The TABLE list page is displayed:
   
   
   Figure: TABLE list page
   
   
3. Click Edit hyperlink under the Modify Data column. The following popup is displayed:
   
   
   Figure: Popup Page
   
   
4. Click on the one of the following options:
   

   Override the table records - Select this option to replace all the table records with new records when updated excel is uploaded. By this, records can be deleted or edited, or new records can also be added.

   Add new records -Select this option to add new records to the already existing records. The new records will be appended at the end of the table.
   
   The MASTER IMPORT page is displayed:
   
   
   Figure: MASTER IMPORT
   

   Field Description

   The following table describes the fields on the MASTER IMPORT page:

   Fields	Description
   Table Name	Displays the name of the table whose records are getting modified.
   Mode	Displays the mode based on the selection in the above popup.  Override - Displays the Mode as Override if OVERRIDE THE TABLE RECORDS is selected in the popup. A sample screenshot is as follows: Add new record - Displays the Mode as Add new record if ADD NEW RECORDS is selected in the popup. A sample screenshot is as follows:
   Source	Download the excel which needs to be modified.
   Download Excel	Click Download Excel to download to excel sheet with all previous records.
   DOWNLOAD MASTER	Click DOWNLOAD MASTER to download excel template without any records. It will only display the column names with appropriate validations associated with columns.

5. Click on Download icon to browse the excel file which needs to be uploaded. A sample screenshot is as follows:
   

   Note:

   Only the Excel file can be uploaded and data types and column names can not be changed while editing the table.

   
   Figure: Download excel file
   
   

6. Click SUBMIT. You can view the imported sheets under GENERAL tab. A sample screenshot is as follows:
   
   
   Figure: Imported Excel Sheets
   
   

7. Click Select hyperlink and it moves to the MAPPING tab. A sample screenshot is as follows:
   
   
   Figure: Import - Mapping
   
   The following table describes the fields on the MAPPING tab.
   

   Fields	Description
   Destination	Displays the name of the destination columns
   Data Type	Displays the data type of the Destination column.
   Source	Select the required column from the drop-down list. Available options are as per the imported excel.

8. Click Next. The PREVIEW tab is displayed.
   
   i. Add New Records:  The records will be imported as per the Primary Key. If Primary Key matches, then as per the Primary Key, the new records gets replaced with the previous records. If new Primary Keys are fetched, then the new records will be entered at the end of already existing records.
   
   ii. Override the Table Records: The imported records override the already existing records i.e. all the existing records will be deleted and only new imported records are available in the table.
   
   
   Figure: Import - PREVIEW

You can discard the records and do the validation checks before Bulk Upload.

FILTERS

This section explains all the icons displayed on the FILTERS section. FILTERS provide easy and simplified search criteria to search for a table based on Table Name or Table ID from a list of tables. Click on FILTERS, the following popup page is displayed.

Figure: FILTERS popup page

Field Description

The following table describes the fields on the FILTER popup page: