KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Datagrid server side processing not working #3236

Open Ed Kavi opened this topic on on Jan 31 - 5 comments
Ed Kavi commented on Jan 31

When I enable server-side processing, only the table header shows. In the console, I get the following error:

Uncaught SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

This error appears only when server-side processing is enabled,

What could be the issue?

Sebastian Morales commented on Feb 1

Would you pls post your report setup and view file content, especially DataTables::create, for us to check them for you?

Ed Kavi commented on Feb 1 
public function setup()
    {
        $query_params = array();
        if($this->params["R_officeId"]!=array())
        {
            $query_params[":R_officeId"] = $this->params["R_officeId"];
        }
        if($this->params["R_groupId"]!=array())
        {
            $query_params[":R_groupId"] = $this->params["R_groupId"];
        }
        if($this->params["R_loanOfficerId"]!=array())
        {
            $query_params[":R_loanOfficerId"] = $this->params["R_loanOfficerId"];
        }
		if($this->params["R_genderId"]!=array())
        {
            $query_params[":R_genderId"] = $this->params["R_genderId"];
        }
		///////////////////
				
        $this->src('clients')->query("select
				concat(repeat('..',
				   ((LENGTH(ounder.`hierarchy`) - LENGTH(REPLACE(ounder.`hierarchy`, '.', '')) - 1))), ounder.`name`) as 'Office/Branch',
				 c.account_no as 'Client Account No.',
				ifnull(mg.display_name, '-') as 'Group', 
				c.display_name as 'Name',
				c.mobile_no as 'Phone', 
				DATE_FORMAT(c.date_of_birth, '%d %b %Y') as 'Date Of Birth', 
				ms.display_name as 'Loan Officer',  
				r.enum_message_property as 'Status', 
				DATE_FORMAT(c.activation_date, '%d %b %Y') as 'Activation', c.external_id as 'External Id'
				from m_office o
				join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%')
				and ounder.hierarchy like concat('.', '%')
				join m_client c on c.office_id = ounder.id
				left join r_enum_value r on r.enum_name = 'status_enum' and r.enum_id = c.status_enum
				LEFT JOIN m_group_client mgc ON c.id = mgc.client_id
				LEFT JOIN m_group mg ON mgc.group_id = mg.id 
				LEFT JOIN m_staff ms ON c.staff_id = ms.id 
				where 1=1
            ".(($this->params["R_officeId"]!=array())? "AND o.id = (:R_officeId)": "")." 
            ".(($this->params["R_groupId"]!=array())?" AND (ifnull(mgc.group_id, -10) = (:R_groupId) OR (:R_groupId) = '-1')" : "")." 
            ".(($this->params["R_loanOfficerId"]!=array())?" AND (ifnull(c.staff_id, -10) = (:R_loanOfficerId) OR (:R_loanOfficerId) = '-1')":"")."    
			".(($this->params["R_genderId"]!=array())?" AND (ifnull(c.gender_cv_id, -10) = (:R_genderId) OR (:R_genderId) = '-1')":"")."    
				order by ounder.hierarchy, c.account_no")->params($query_params) 
          ->pipe($this->dataStore('clients_list'));
    }

DataTables::create(array(
    "dataStore"=>$this->dataStore('clients_list'),
        "columns"=>array(
            "Office/Branch"=>array(
                "label"=>"Office/Branch"
            ),
            "Group"=>array(
                "label"=>"Group",
            ),
			"Name"=>array(
                "label"=>"Name",
            ),
			"Phone"=>array(
                "label"=>"Phone",
            ),
			"Date Of Birth"=>array(
                "label"=>"Date Of Birth",
            ), 
			"Loan Officer"=>array(
                "label"=>"Loan Officer",
            ),
			"Status"=>array(
                "label"=>"Status",
            ),
			"Activation"=>array(
                "label"=>"Activation",
            ),
			"External Id"=>array(
                "label"=>"External Id",
            ),
        ),
	"plugins" => ["Buttons"],
	"options"=>array(
		"colReorder"=>true, 
		"dom" => 'Blfrtip',
		"order" => [], 
		"buttons" => [
			[
				"extend" => "colvis", 
				"text" => 'Show/Hide Column '
			],
			[
                "extend" => 'excelHtml5',
				"className" => 'btn btn-primary', 
				"title" => 'Client List', 
                "exportOptions" => [
                    "columns" => ':visible'
                ] 
            ],
            [
                "extend" => 'pdfHtml5', 
				"className" => 'btn btn-primary', 
				"title" => 'Client List', 
				"orientation" => "landscape", 
				"pageSize" => "A4",
                "exportOptions" => [
                    "columns" => ':visible'
                ]
            ],
		],
		//"paging" => true,
		"fixedHeader" => true,
		"searching"=>true,
	),
	
	"serverSide"=>true,
	"processing" => true,
	//"fastRender" => true,
	"themeBase"=>"bs4",
    "cssClass"=>array(
			"table" => "table table-condensed table-striped table-bordered", 
    ), 
	"cssStyle"=>array(
        "td"=>"padding: 3px; color: #000; font-size:12px",
		"th"=>"padding: 5px; color: #000; font-size:14px",

    )
));
Sebastian Morales commented on Feb 2

For DataTables' "serverSide" => true property to work the "dataSource" property must be a function that returns data instead of a datastore like normal mode. Here's an example:

    DataTables::create(array(
        'name' => 'DataTable0',
        'dataSource' => function() {
            return $this->src("automaker")
            ->query("select * from customer_product_dollarsales");
        }, 
        "serverSide"=>true,
        "method"=>'post', 
        ...
    ));

If in your query in the "datasource" function you need to access report parameters, you can pass parameters to a "scope" property and variable like this:

    DataTables::create(array(
        'name' => 'DataTable0',
        'dataSource' => function($scope) { // access "scope" value via $scope variable 
            $query = ...;
            $sqlParams = ...;
            return $this->src(...)->query(...)->params(...);
        }, 
        "scope" => $this->params, // pass params to DataTable's "scope" property
        "serverSide"=>true,
        "method"=>'post', 
        ...
    ));

Pls try this and let us know if there's any issue.

Ed Kavi commented on Feb 2

Thanks for the help Sebastian.

I was able to get the table to show the data using server-side processing, based on your guidelines.

However, when I try to do a search, or order based on some columns by clicking the table header, I get the following error in the console:

Uncaught SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

I also noticed some custom CSS styles defined in "cssStyle" property of the table don't work. Some work, but others like padding do not appear to have effect. What could be the isssue?

This is code:

DataTables::create(array(

'name' => 'DataTable0',
'dataSource' => function($scope) { // access "scope" value via $scope variable 

	$query = "select 
			...
                            ...
purpose_cv_id, -10) = :R_loanPurposeId or '-1' = :R_loanPurposeId)":"")." 
			order by ...  2 , l.id, lo.id";
			
	$sqlParams = array(
				":R_officeId" => $scope["R_officeId"], 
				 ...
				":R_loanPurposeId" => $scope["R_loanPurposeId"] 
			);

	return $this->src('appdb')->query($query)->params($sqlParams);
}, 	
"scope" => $this->params, 
"serverSide"=>true, 
"processing" =>true, 
"method"=>'post', 	
"searchOnEnter" => true,
"searchMode" => "or", 

"columns"=>array(
	"Branch Office"=>array(
		"label"=>"Office/Branch"
	),
	...

	"Interest Calculated in Period"=>array(
		"label"=>"Interest Calculated in Period",
	),
            ...

	"Written Off"=>array(
		"label"=>"Written Off Date",
	),
),
	
"plugins" => ["Buttons"],
"options"=>array(
	"colReorder"=>true, 
	"dom" => 'Blfrtip', 
	"order" => [], 
	"stateSave" => true,
	"buttons" => [			
		[
			"extend" => "colvis", 
			"text" => 'Show/Hide Column '
		],
		[
            "extend" => 'excelHtml5',
			"className" => 'btn btn-primary', 
			"title" => 'Client Loans', 
            "exportOptions" => [
                "columns" => ':visible'
            ] 
        ],
        [
            "extend" => 'pdfHtml5', 
			"className" => 'btn btn-primary', 
			"title" => 'Client Loans', 
			"orientation" => "landscape", 
			"pageSize" => "A4",
            "exportOptions" => [
                "columns" => ':visible'
            ]
        ],
	],
	"columnDefs" => [
        array(
            "targets" => 3,
            "visible" => false
        ), 

  ...

		array(
            "targets" => 27,
            "visible" => false
        ),
    ],
	"fixedHeader" => true,
	"searching"=>true,
),
//"fastRender" => true,
"themeBase"=>"bs4",
"cssClass"=>array(
		"table" => "table table-condensed table-striped table-bordered", 
		
), 
"cssStyle"=>array(
    "td"=>"padding: 3px; color: #000; font-size:12px",
"th"=>"padding: 5px; color: #000; font-size:14px",
)

));

Sebastian Morales commented on Feb 5

It's good that it works on the first load. To test sorting and searching problem, pls try a test: use a simple select query without any parameter in the "dataSource" function to see if it's ok or not?

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
DataGrid

Designed and built with by KoolPHP Inc.

Code licensed MIT.