【Scheduler】Using PayPal Rest API (Transaction:Analysis)

In the [previous article](https://qiita.com/Miki_Yokohata/items/8afde60ce05713f715c1), I sent a Get request to PayPal Rest API, obtained **Transaction** information, and saved it in a file.
This time, I would like to read and understand the data in JSON format while analyzing the file.
And again, use the [Sharperlight](https://sharperlight.com/) scheduler task to do so.
The reason why we use Sharperlight's scheduler task is that the ultimate goal is to automate the flow of retrieving data from the Rest API and synchronizing the data to our database. The data synchronized in this way can be visualized using Sharperlight's powerful reporting function, etc., and used for business purposes.

In creating the analysis task this time, We would like to reduce as much as possible the change work on our side that would otherwise occur due to changes in the schema and the data structure on the PayPal Rest API side, so we would use various methods to achive it, but this time this is a briefe article, so I will omit that area.

## Checking Data Structure ##
If I open the obtained JSON data file, I can see such a structure.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bad8f90b-90d3-f304-74e2-dec817735978.png)
In this partial image, you can see that two records are displayed under `transaction_details`, and each record has seven `_info` groups. This time, I will only retrieve the information in the `transaction_info` group of each record.

What kind of data structure does the `transaction_info` group have? It looks like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/69da001a-9388-a465-fa1d-a1a1e86b264c.png)
I can also see items with child information. Each child's information was like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1d93b97f-be96-0a52-39d8-558c901075cc.png)
They don't seem to have grandchildren.
The idea is to store this `transaction_info` information for each record in the `[Transaction_Info]` table in the database. There are items with child information in the `transaction_info` information of each record, and I also want to flatten them.
i.e. do something like this;
> [paypal_account_id]
> [transaction_id]
> [transaction_event_code]
> [transaction_initiation_date]
> [transaction_updated_date]
> [**transaction_amount**_currency_code]
> [**transaction_amount**_value]
> [transaction_status]
> [**ending_balance**_currency_code]
> [**ending_balance**_value]
> [**available_balance**_currency_code]
> [**available_balance**_value]
> [protection_eligibility]
## Loading data ##
I will create a process to read data in the Sharperlight **scheduler** task. I could write everything in JavaScript, but this time I'll save some time.
Start **Scheduler** from the [Sharperlight](https://sharperlight.com/) application menu.
### General tab ###
In the `General` tab, fill in **Code**, **Group**, and **Description** like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f9a783db-6449-73da-a4bc-7332b2cffb12.png)
### Actions tab ###
Go to the `Actions` tab and add an action with `New` icon. The first action to add is the **Query Combinations** action. This action allows me to iterate over each record returned by the defined query. I can do processing like a For loop statement.
Start building a query with the `Edit Query` button.
In the **Filters** area, specify **System** for `Products`, and **Web page tables, CSV, JSON, XML or OData** for `Tables`.
For the `URL Link` filter, specify a JSON data file path. (Since only one file was specified this time, specify its name directly)
Given a file, the engine will analyze the file and display selectable items in the **Selection** area.
Specify the root item of the data structure, **transaction_details**, as the `Start Position` filter.
Finally, specify **transaction_info**, which is the target information for this time, in the **Outputs** area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/77ccd4e4-554b-7e43-f900-b5b5f75a4d91.png)
Check the output value with the `preview` button. I can see that only the **transaction_info** information of each record is output. I can see that the Transaction data obtained from the PayPal Rest API this time has 16 records.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b426791f-cd9d-0ab9-a4f1-ed3a01560a45.png)

Then it goes through these 16 records one by one. JavaScript is used for that process. Use the output name {%trans_info} to refer to each output record.

Add a **JavaScript** action using `New` icon.
Write the code like this in the script field at the bottom. The very first row receives one row of data returned by the query combination. Then convert it to an object and process the columns one by one. Along the way, I will also process child objects. The processed result is displayed on the console each time for debugging.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e672dc1b-d3dd-30c3-3b23-6441f7327446.png)
```
// Get a record
var output = lib_task.QueryCombinationValueGet('trans_info');
var rec = JSON.parse(output);

// Get columns for the record
var cols = Object.keys(rec);
for (var c in cols){
       if (typeof rec[cols[c]] !== 'object'){
              console.log(cols[c] + " / " + rec[cols[c]]);
              
       }else{       // Processing child Information
              var childRec = rec[cols[c]];
              var childRecCols = Object.keys(childRec);
              for (var cc in childRecCols){
                     console.log(cols[c] + ': ' + childRecCols[cc] + " / " + childRec[childRecCols[cc]]);
              }
       }
}
```
When executed with the `Test` button, I was able to get the column names and their values like this. I was able to flatten the child info too:ok_hand:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6c9ba9e5-9194-ed7f-8e75-0f9b903b079a.png)
Finally add a **Query Combinations End** action to close the loop.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4dd4be88-0b3c-29b4-97ac-264457e9e8fc.png)

After that, I will write this information to the database using the Writeback function of Sharperlight. In this article, I acquired and analyzed transaction data for a limited period of time as an example, but in reality, I will develop such processing to synchronize all data and use that data to create various reports with the powerful reporting functions of Sharperlight.
This process is automated with a scheduler task that incorporates such as delta update function, etc., and only the latest data created after the previous data is acquired and added to the database.

Up to here for this time.

Thank you.