6. Make it data-driven with PHP

Owned by Kris Jin (Unlicensed)
Last updated: May 11, 2013Version comment
7 min read

Description 

This example takes the previous sample and makes it data driven and adds a way for the user to define new DataSource types. It will also be extended to define a new DataSource and change the user interface to allow the user to switch between the two DataSources.

Prerequisites

As this sample builds on the previous one, in order to be able to run it, please esnure you have the latest build of SmartClient (at least version 8.3p). This can be downloaded from here.

Add the method that gets primary key from the DataSource

In the previous articles, in many places the primary key is used (to get the record from the database). However,  to make it generic, this cannot be hard-coded. It is possible to retrieve it from the DataSource definition. To do this, add a method to the DataSource which will search in the loaded fields and return the one which has a primaryKey attribute set to true:

DateSource.php
private function getPrimaryKey()
{
	$primaryKey = '';
	$ds = $this->data_source;
	$fields = $ds['fields'];
	
	foreach($fields as $field)
	{
		if(array_key_exists("primaryKey", $field))
		{
			$primaryKey = $field['name'];
			return $primaryKey;
		}                       
	}
}

Change the Add / Remove / Update methods 

Use the new getPrimaryKey() method to make the logic work with any DataSource.

private function add($request)
{            
	$ds = $this->data_source;
	// get the table name from data source
	$tbl_name = $ds['ID'];
	$new_data = $request->data;
	$insert_values = array();
	// make the query            
	$columns = '';
	$values = '';
	foreach($new_data as $key => $value)
	{            
	   $columns .=  $key.',';
	   $values .=  "?,";   
	   array_push($insert_values, $value);             
	   
	}         
	// remove the last comma
	$columns = substr($columns, 0, strlen($columns) - 1);
	$values = substr($values, 0, strlen($values) - 1);
	// result insert query
	$insert_query = 'INSERT INTO '.$tbl_name.' ('.$columns.') VALUES ('.$values.')';
	
	// Run the insert query
	R::exec($insert_query, $insert_values);
	//DSResponse
	$response = new DSResponse();      
	$response->setData($new_data);            
	$response->setStatus(0);  
	return $response;
}
private function update($request)
{
	$old_data = $request->oldValues;
	$update_data = $request->data;
	$ds = $this->data_source;
	
	// update all fields which have changed. they are defined in the data property
	if ( count($request->getDataKeys()) != 0 )
	{
		 foreach($request->data as $key => $value)
		 {
			$old_data[$key] = $value;
		 }
	}     
	// get Primary key
	$primary_key = $this->getPrimaryKey();
	// get the id  by primary key
	$values = array();
	
	
	// get the table name from data source
	$tbl_name = $ds['ID'];            
	$set_value = '';
	// make the update query
	foreach($old_data as $key => $value)
	{          
		if($key != $primary_key)  
		{
			$set_value .= "$key = ?,"; 
			array_push($values, $value);                  
		}   
	}
	array_push($values, $old_data[$primary_key]);
	
	// remove the last comma
	$set_value = substr($set_value, 0, strlen($set_value) - 1);
	// result insert query
	$update_query = 'UPDATE '.$tbl_name.' SET '.$set_value.' WHERE '.$primary_key.'= ?';
	R::exec($update_query, $values);
	
	// build the criteria
	//DSResponse
	$response = new DSResponse();      
	$response->setData($old_data);            
	$response->setStatus(0);  
	return $response;
}
private function remove($request)
{
	$ds = $this->data_source;            
	// get the table name from data source
	$tbl_name = $ds['ID'];            
	$new_data = $request->data;
	// get Primary key
	$primary_key = $this->getPrimaryKey();
	// get the id  by primary key
	$value = $new_data[$primary_key];
	// make the delete query
	$delete_query = "DELETE FROM $tbl_name WHERE $primary_key = ?";
	// Run the insert query
	R::exec($delete_query, array($value));
										 
	//DSResponse
	$response = new DSResponse();      
	$response->setData($new_data);            
	$response->setStatus(0);  
	return $response;
}

 

Adding a new DataSource

To test this new code,  define a new DataSource instance for employee and later allow the user to switch between them. Firslyt, the definition of the DataSource itself:

employees.js
isc.RestDataSource.create({
    "ID": "employees",
    "fields": [
        {"name": "Name", "title": "Name", "type": "text", "length": "128" },
        {"name": "EmployeeId", "title": "Employee ID", "type": "integer", "primaryKey": "true", "required": "true" },
        {"name": "ReportsTo", "title": "Manager", "type": "integer", "required": "true", "foreignKey": "employees.EmployeeId", "rootValue": "1", "detail": "true" },
        { name": "Job", "title": "Title", "type": "text", "length": "128" },
        { "name": "Email", "title": "Email", "type": "text", "length": "128" },
        { "name": "EmployeeType", "title": "Employee Type","type": "text", "length": "40" },
        { "name": "EmployeeStatus", "title": "Status", "type": "text", "length": "40" },
        { "name": "Salary", "title": "Salary", "type": "float" },
        { "name": "OrgUnit", "title": "Org Unit", "type":"text", "length":"128" },
        { "name": "Gender", "title": "Gender", "type":"text", "length":"7",
            "valueMap": ["male", "female"]
        },
        { "name": "MaritalStatus", "title": "Marital Status", "type": "text", "length": "10",
            "valueMap": ["married", "single"]
        }
    ],
    "dataFormat": "json",    
    "operationBindings": [
            { "operationtype": "fetch", "dataProtocol": "postMessage", "dataURL": "process.php" },
            { "operationtype": "add", "dataProtocol": "postMessage", "dataURL": "process.php" },
            { "operationtype": "update", "dataProtocol": "postMessage", "dataURL": "process.php" },
            { "operationtype": "remove", "dataProtocol": "postMessage", "dataURL": "process.php" }
        ]
});

 

Create the database table for this DataSource. For this, open the Database Explorer, select the connection to the database and right click 'Tables'. In the popup menu select 'Add new table'. Using the table editor, enter the fields for the table as follows:

Column name

Data Type

Allow Nulls

Additional

EmployeeId

int

No

Identity and Primary Key

Name

varchar(128)

No

 

ReportsTo

int

No

 

Job

varchar(128)

Yes

 

Email

varchar(128)

Yes

 

EmployeeType

varchar(40)

Yes

 

EmployeeStatus

varchar(40)

Yes

 

Salary

float

Yes

 

OrgUnit

varchar(128)

Yes

 

Gender

varchar(7)

Yes

 

MaritalStatus

varchar(10)

Yes

 

Save the table with the name "employee", then open the table data and add a couple of sample rows, so the table has data.

Finally,  load this newly defined DataSource into the browser. For this edit the index.php and add the following code:

 

index.php
<SCRIPT SRC="ds/employees.js"></SCRIPT>

 

UI changes

On the user interface, a change is required to allow users to switch the current DataSource. Add a form with a drop-down with the DataSources to switch and place it in front of the grids below the ListGrid. This requires putting the form in the HStack layout used for the buttons: 

 

ui.js
isc.HStack.create({
    "membersMargin": 10,
    "ID": "gridButtons",
    "members": [
        isc.DynamicForm.create({
            "values": { dataSource: "Change DataSource" },
            "items": [
                { "name": "dataSource", show"title": false, "editortype": "select",
                    "valueMap": ["supplyItem", "employees"],
                    "change": function (form, item, value, oldValue) {
                        if (!this.valueMap.contains(value)) return false;
                        else {
                            supplyItemGrid.setDataSource(value);
                            advancedFilter.setDataSource(value);
                            supplyItemGrid.filterData(advancedFilter.getCriteria());
                        }
                    }
                }
            ]
        }),
        isc.IButton.create({
            "top": 250,
            "title": "Edit New",
            "click": "supplyItemGrid.startEditingNew()"
        }),
 
        isc.IButton.create({
            "top": 250,
            "left": 100,
            "title": "Save all",
            "click": "supplyItemGrid.saveAllEdits()"
        }),
    ]
});

 

Notice that when the user changes the DataSource,  the selected DataSource is set, both for the supplyItemGrid and for the advancedFilter FilterBuilder. Then filterData() is called on the grid to refresh the content.

This example now shows a data-driven DataSource that allows users to add/remove/update two DataSources with two different entity DataSource, and also apply various filter criteria built with the Filter Builder.

The complete code for this sample project can be downloaded from here.