Plugin development – Creating simple input

The following sections will describe a simple example of how to make a plugin that handles input from the user and store the result to the database. The point if this exercise is to demonstrate the different mechanisms involved when creating custom UI (InFront plugin) that communicates with the database.

Prerequisites

InFront is an HTML5 web application built using AngularJS and an InFront plugin is conceptually an AngularJS directive. Developing InFront plugins will therefore require some knowledge about both web technologies in general and about AngularJS and on how Angular directives work. We depend on Angular 1.X and will use techniques from AngularJS in this example.

Creating a simple input plugin

Let’s get started and write a plugin that takes values from a simple input form and stores them to the database.  In this example we will be will be creating and displaying a list of names of persons. We should see all the persons already registered in the list and be able to add more persons to the list. For the sake of simplicity, we have omitted styling so we can keep the focus on the core concepts needed also when creating more complex plugins. So this one is stripped of “eyecandy”.

The pieces of the puzzle

The parts we need are the following:

  • A custom table (“Persons”) to store a person’s first name and last name.
  • An SQL Report with a related stored procedure to read from the “Persons” table (“reader”).
  • A stored procedure to handle storing new persons to the “Persons” table (“writer”).
  • The plugin with a user interface showing the form to register a person and the list over persons.

Creating the InFront SQL Report.

  1. Start the InFront Designer
  2. Create an SQL Report – this will be the report we need to add the custom plugin to. This is done by setting the reports “Plugin” property to the plugin id. Create 2 columns in the report.

Creating the table in the database

  1. Microsoft SQL Management Studio and connect to the InFront meta database.
  2. Create the Persons table with the following script:
    CREATE TABLE [dbo].[Persons](
    	[firstname] [nvarchar](200) NULL,
    	[lastname] [nvarchar](200) NULL
    ) ON [PRIMARY]
    

Creating the “reader” stored procedure

  1. Create the stored procedure that will read the content of the “Persons” table with the following script:
    CREATE PROCEDURE [dbo].[sp_getPersons]
    	@filters xml,
    	@user nvarchar(50) = null,
    	@language nvarchar(5) = null
    AS
    BEGIN	
    SET NOCOUNT ON;
    
    	SELECT * FROM Persons
    	ORDER BY firstname	
    END
    
  2. Set the “Stored Procedure Name” property on the SQL report to the name of this stored procedure – sp_getPersons.

Creating the “writer” stored procedure

Create the stored procedure that will handle writing content to the “Persons” table with the following script:

ALTER PROCEDURE [dbo].[sp_putPerson]
	@args xml,
	@user nvarchar(50) = null,
	@language nvarchar(5) = null
AS
BEGIN
	SET NOCOUNT ON;

INSERT INTO [dbo].[Persons]
SELECT     T.v.value(N'./firstName[1]', N'nvarchar(200)') AS firstName,
           T.v.value(N'./lastName[1]', N'nvarchar(200)') AS lastName
FROM       @args.nodes(N'/root') AS T(v)

END

Notice that the select part of the procedure dissects the args xml to get hold of the values. In general, the args argument can be “anything”. So code that dissects that argument will be custom and different every time.

Creating the Plugin

The plugin code itself consists of a few lines of HTML that makes out an input form and a list. This code can be put into the HTML part in the plugin screen after creating a new plugin and giving it a name.

  1. Open the plugins user interface by accessing Tools – Plugins in the designer
  2. Enter a plugin name and press the “pluss” icon.
  3. In the HTML section of the plugin user interface we need the following code.
    <div>
      <H3>Input skjema</H3>
      <form ng-submit="clickSubmit()">
         <label> Fornavn:</label><input type="text" ng-model="firstName"><br>
        <label> Etternavn:</label><input type="text" ng-model="lastName"><br>
        <button type="submit">Lagre</button>  
      </form><br><br>
      
      <div>
      <H3>Navneliste:</H3>
        <div ng-repeat="row in data.rows">
          <span>{{row.cells[0].v}} {{row.cells[1].v}}</span>
        </div>
      </div>
    </div>
    
  4. In addition, we need the JavaScript code that handles the logic and the data. The following code can be put in the JavaScript portion of the plugin UI:
    function link(scope, element, attrs) {
      // Init
      var ps = pluginservice.init(scope);
     
      // Button click handling
      scope.clickSubmit = function () {
        // Collect the data from the UI
        var obj = {
          firstName: scope.firstName,
          lastName: scope.lastName
        };
    
        // Get the stored procedure name from the plugin properties
        var spName = ps.getPluginProperties().spName;
        // Lets run a stored procedure to push our arguments to the dB.
        ps.runStoredProcedure(spName, obj).success(function (data) {
        // data is the return object from the database. In this case we dont use it at all we just want to reload.
          ps.reloadReport();
          
        }).error(function (err) {
          // Handle an error situation.
          console.log(err);
        });
      }
    
      function loadData() {
        scope.data = ps.getReportData();
        
        // Instantiate the firstName and LastName model properties.
        scope.firstName = '';
        scope.lastName = '';
      }
    
      loadData();
    
      scope.$on('reload', function () {
        loadData();
      });
    }
    
  5. Save the plugin and enter the name you gave it into the Plugin property of the SQL Report we made

The Plugin logic

Initially when the plugin is loaded the only code that runs is the initialization code for the pluginservice, the loadData() function, and some code that registers a listener to  the ‘reload’ event in the application.

The loadData function gets hold of the report data and puts it on a data property on the scope making it available to bind towards from the HTML code.

The last part of the plugin code is the clickSubmit() function. This function handles the logic that needs to happen when the Submit button is pressed.

Appendix
The InFront PluginService, see : http://blogs.profitbase.com/infront/?p=63
Angular documentation, see : https://angularjs.org/

Leave a Reply