【Scheduler】Creating a task accessing file system

I'm planning to try using the Sharperlight Scheduler to perform actions such as copying files and exporting file lists to a file.

The folder structure, as shown in the diagram below will be used.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1d2b84f6-9f27-9f16-bacc-157571ba9b3d.png)
Each folder contains files like the ones shown below.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ade5da1b-973d-a840-33a7-9f7b3a30469f.png)
I'm planning to access these folders and files from the scheduler task.

# Folder Security #
Normally, Sharperlight is installed and operated on the application server. Allowing unrestricted access to the application server's folders poses a security risk. Therefore, Sharperlight provides the ability to register folders allowed for access using site setup. Let's proceed with the configuration.
Launch the **Site Setup** from the Sharperlight application menu.
Select `Product Settings` and `System` from the tree view.
The options for product settings will be displayed on the right side. In the bottom option, `Secure Folders`, specify the path of the folder you want to access in this case. If there are multiple folders, concatenate them with the pipe symbol (|).
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f6548930-6ae1-49cb-89d2-57edbd631f9b.png)
Close the Site Setup dialog by clicking the `Close` button.
Restart the Sharperlight service to apply the changes.

# Creating a Scheduler Task #
Launch the **Scheduler** from the Sharperlight application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/490741c0-4e78-77f1-2cba-e1456086cee1.png)
Initiate the task creation by clicking the `New` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/47e6ef9f-ec03-4559-36ed-ba72e6ae0620.png)
In the **General** tab, enter the `Code`, `Group` and `Description`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/66f6ebed-950e-11d3-dfb8-4a0355208bb4.png)
Navigate to the **Action** tab and define the process you want to execute here.

## Exporting a list of files to CSV ##
This time, I will check all the subfolders under C:\QiitaSampleTemp\ and create a CSV file listing all the files. At the same time, I will copy all the files to the C:\QiitaSampleCopy\ folder.
Add a new action by clicking the `New` icon.
Select **Query Combinations** from the list of available actions.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/aaab0fce-346a-3d58-cc0c-49c041a9d489.png)

Configure the settings for **Query Combinations**. This action functions like a for loop.
By defining the query here, you can pass a row one by one from the dataset returned by the query to the next action.
Let's begin creating the query by clicking the `Edit Query` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/655f0138-9f11-586a-2b9b-b82199f0100e.png)
The familiar Query Builder will open. Select **Summary Report** as the `Mode`, and choose **System** as the `Product`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e14a66a7-2e5c-c325-eb82-338e8f9f5a30.png)
Select **File List** for the `Table` in the query
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/20a9419f-63de-9bc7-6da9-961285a7e6bf.png)

`Folder` will be displayed as a mandatory filter. Choose the folder you want to access.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/513f25b3-e0d3-d980-fa16-cbfaeaf289a1.png)
To ensure access to sub folders, confirm that the `Sub Folders` is set to **Y** as another mandatory filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1a445e52-7596-ef72-3b1b-95c71047c72b.png)
Next, specify the following items in the output area from the selection list.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/871a8b1d-bf7d-6242-f40e-1448b24aef98.png)
Try the query by clicking the `Preview` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/15cb150c-fe77-a74f-d73f-04e9f32bd7f9.png)

The file list will be returned. Exclude the first row from the query as it is unnecessary. Return to the query definition by clicking the `Back to Query` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a9eac8ce-903a-b592-ece0-daf354027869.png)
Drag and drop **Size** from the selection list to the filter area. Set the condition to **Greater than 0**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d294ce10-213b-fbd9-4fe5-5456b4e48c8f.png)
Try the query again by clicking the `Preview` button. The unnecessary first row should now be excluded.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/04d9c144-56f0-d3e3-d368-bfb25ecae9ba.png)
Furthermore, in this query, keep only the following output items and remove the others.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/07ceddd3-9266-c433-5673-47181f3b8a76.png)
Executing the query will return a dataset similar to this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b244cb3b-1c68-9b0c-c6a6-fc3831df3f7f.png)

With this, the query is complete. Values will be passed one by one from this dataset to the next action as parameters. Click `OK` to save the query and return to the Action tab.
Add a new action by clicking the `New` icon.
The **Query Combinations** action, which was defined earlier, requires a corresponding **Query Combinations End** action to terminate the loop. Add this action first.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2f5700c0-b381-6e9f-2553-2bfa5fd190f0.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/88cd57b9-83ea-5b47-8a40-8d99155bc104.png)
Now, let's define the core action that processes each row of the dataset obtained by the **Query Combinations** action. Add a new action using the `New` icon. This time, use the **Query** action. This action will be defined between the **Query Combinations** and **Query Combinations End** actions.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8334a4e4-89a7-67a9-4009-6fded7f05ad9.png)
Define the query by clicking the `Edit Query` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7e4be6c5-f575-d084-98b2-76e078b893a4.png)
The query filters will be the same as before.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d327fb56-2153-b03e-6847-19dd80bfd298.png)
However, this time, we will pass the folder name obtained by the **Query Combinations** action to the `Folder` filter. Use the unique reference name displayed at the right end of the `Folder` filter, which is **@Folder**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/37915623-5c47-82b8-9582-327b6a0f09ff.png)
Before that, set the following items in the output area from the selection list.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/445ed068-c595-c5bd-ed69-519bbb1583d7.png)
Save the query by clicking the `OK` button.
In the `Query Parameters`, enter **Folder=**. This represents the reference name of the `Folder` filter in this query. It means passing a value to the `Folder` filter in this query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3918e26f-c138-7c4e-fd85-7d9e44e2cfc8.png)
The value to pass is the folder name obtained by the previous **Query Combinations** action, so use the reference button on the right to search and set it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7e04f659-03ef-beda-ecbb-b120b016b85d.png)
Complete the settings by clicking the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1a3c69f9-93f1-0232-564b-0ff556dcc410.png)
Next, write the results of this query to a CSV file. The settings for this are on the right side.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0cbe8155-5fc0-1fbb-26fe-1c637f32eded.png)
The settings will be as follows. The `Append` checkbox means appending data to the same file.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/59768b40-7cc6-1ef8-f1ec-f192963e78f9.png)

## File Copy ##
Add the **File Copy** action.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e5d3e9d0-fb28-9124-5976-6042573c5d04.png)
Set the source and destination of the file as follows. Utilize the return value of the previous **Query Combinations** action.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0f14d218-2ab5-aac8-cfd3-2c6d642105d7.png)

With this, the definition of the core actions are also completed.
Save the task by clicking the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/db0c5d0e-5699-7cff-8cb1-c2c7b446bad7.png)

# Validation of the Scheduler Task #
Select **Execute** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a11fd7be-7c2e-7fe6-ddca-7c73b6047fc7.png)
If no errors or issues are displayed, everything is working correctly.
Let's quickly locate the created file list, **FileList.csv**.
Great, it's there.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a3eea6a3-1697-770c-b3e8-c6e26aa210cb.png)

Let's also check the **C:QiitaSampleCopy** folder. You can see that the files have been copied.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/be2302a7-9374-bdc8-caf9-678f99f8bce3.png)


# Afterword #
This time, I used the Sharperlight scheduler to create a list of files and tried copying files. While this isn't a task typically done using scheduling, I wanted to convey that such usage is possible among the numerous features available. Thank you and goodbye.