Working with third-party REST API

In the current world situation, all countries track new and existing cases of COVID-19. Some countries provide an API to access the latest data. Here is an API provided by Hong Kong’s department of health. Today we will learn how to display this data in our own application. Applies to version 10.4 of PHPRunner, ASPRunner.NET and ASPRunnerPro.

Creating the REST View

1. Lets see how we can display data for March 2020. We added a filter for ‘As of date’ field to show data that contains ’03/2020′ in this field (they use UK date format). Click ‘Get result’ and we will see both API query string and the data it returns.

2. Now, lets take a look at the URL:

https://api.data.gov.hk/v2/filter?q=%7B%22resource%22%3A%22http%3A%2F%2Fwww.chp.gov.hk%2Ffiles%2Fmisc%2Flatest_situation_of_reported_cases_wuhan_eng.csv%22%2C%22section%22%3A1%2C%22format%22%3A%22json%22%2C%22filters%22%3A%5B%5B1%2C%22ct%22%2C%5B%2203%2F2020%22%5D%5D%5D%7D

We can paste it to the web browser and get the same results in JSON fomat. Now it is the time to create a REST connection. The https://api.data.gov.hk/v2/ part of the URL is the main connection URL.

3. When setting up the REST View we use the rest of our URL. Now we can run the request, add fields and build the project.

4. And this is how it looks in the web browser. I just shortened field labels to make it look better.

Creating the REST Chart

1. Now proceed to ‘Datasource tables’ screen, right click on our REST View and choose ‘Add chart’. By default it uses the same REST API call and has access to the same dataset.

2. Now we proceed to the chart setup. It is fairly straightforward, just select a few fields to show on the chart.

3. There is one more thing before we are ready to test it in the web browser. Our chart will make more sense if data is sorted by the date. Since this REST API doesn’t support sorting we will have to do this manually.

Proceed to the REST Chart ‘Source’ page, switch to ‘PHP mode’. Most of the code is already generated for us, we just need to insert the following snippet:

if( !$command->order ) {
$command->order[] = array("column" => "As of date", "dir"=>"ASC" );
}

Here is how the complete code going to look:

$method = "GET";
$url = "filter?q=%7B%22resource%22%3A%22http%3A%2F%2Fwww.chp.gov.hk%2Ffiles%2Fmisc%2Flatest_situation_of_reported_cases_wuhan_eng.csv%22%2C%22section%22%3A1%2C%22format%22%3A%22json%22%2C%22filters%22%3A%5B%5B1%2C%22ct%22%2C%5B%2203%2F2020%22%5D%5D%5D%7D";
$url = RunnerContext::PrepareRest( $url );

//	do the API request
$response = $dataSource->getConnection()->requestJson( $url, $method );
if( !$response ) {
	//	something went wrong
	$dataSource->setError( $dataSource->getConnection()->lastError() );
	return false;
}

//	convert API result into recordset
$rs = $dataSource->resultFromJson( $response, true );

// apply search and order parameters
if( !$command->order ) {
$command->order[] = array("column" => "As of date", "dir"=>"ASC" );
}
$rs = $dataSource->filterResult( $rs, $command->filter );
$dataSource->reorderResult( $command, $rs );

//	apply pagination
$rs->seekRecord( $command->startRecord );
return $rs;

4. And this how it looks in the web browser.

Enjoy!