危ない、しかしパワフルな機能！拡張SQLをAPIから利用してみる。

こんにちは。
リーデックス小川です。

暑い日が続きますね。短時間の豪雨、雷雨も頻繁に起きているようです。最近はアプリでも雨雲の情報が見れますので、活用して突然の雨に備えたいですね。

 さて、今回は拡張をAPI経由で実行する方法を記事にしてみました（公式マニュアル）。拡張SQLはレコードの作成の前後や更新の前後など特定のタイミングの他に、APIから実行することができます。

 拡張SQLとは？

SQLで直接データを取得、更新できる機能です。拡張SQLの詳細については本記事では割愛いたしますので、詳しく知りたい方は下記をご覧ください。

Pleasanter ユーザーマニュアル - 開発者向け機能：拡張機能：拡張SQL

拡張SQLの注意点

すべてのテーブルの作成、更新、削除を行うことができるため、誤って使用するとプリザンターが利用できなくなったり、データが壊れたりする可能性がありますので十分注意してください。
また、APIキーやセッションによるログイン確認は行いますが、テーブルへの権限チェックなどは一切行っていないため、必要な権限のチェックはSQLで行ってください。

今回のサンプル

ログインユーザ（以下ユーザ）ごとに登録されているメールアドレスを取得する拡張SQLをAPIで呼び出すスクリプトを書いてみたいと思います。プリザンターで用意されているUserAPIではメールアドレスを取得できないため、ユーザIDを指定してMailAddressesテーブルから取得する必要があります。

具体的な仕様は、用意したテーブルに設定した担当者に紐づくメールアドレスを説明Aに表示する、とします。

テーブルを準備する

担当者と説明Aを設定したテーブルを用意ください。そのほかの項目は任意です。

拡張SQLを書いてみる

APIから実行する拡張SQLを書いていきます。拡張SQLはJSONで記載します。パラメータは下記通りです。

パラメータ名	値の例	説明
Description	例) 例	説明文です（任意項目）
Name	例) Sample	APIから実行させる際の名前を設定します。
Api	例) true	trueの場合APIからの実行を許可します。
CommandText	例) 下記参照	SQLを記載します。

今回のサンプルは下記となります。なお、CommandTextは外部ファイル化もできますので、SQLが長くなる場合はここを参考に設定してみてください。

{
    "Description": "Sample",
    "name": "user_mail",
    "Api": true,
    "CommandText": "select * from MailAddresses where OwnerId = @OwnerId"
}

ファイルを配置してIISを再起動

上記ファイルを「インストールしたディレクトリ\App_Data\Parameters\ExtendedSqls」に保存し、IISの再起動を行います。Windowsにデフォルトのままインストールした場合のディレクトリは「C:\inetpub\wwwroot\pleasanter\App_Data\Parameters\ExtendedSqls」となります。

最後はスクリプト

最後に拡張SQLを読み出すスクリプトを書きます。作成したテーブルの管理画面のスクリプトよりスクリプトをコピー、出力先を「編集」に設定してください。

スクリプトを簡単に説明すると、POSTするデータ用JSON（JSONdata）に先ほど設定した"Name"に"user_mail"と変数の"OwnerId"に担当者の値を"$p.getControl("Owner").val()"を設定します。

$(function(){
    let JSONdata = {
           "Name": "user_mail",
            "Params": {
                "OwnerId": $p.getControl("Owner").val()
            }
        };

    $.ajax({
        "type" : "post",
        "url" : "http://localhost/pleasanter/api/extended/sql",
        "data" : JSON.stringify(JSONdata),
        "contentType": "application/json",
        "scriptCharset":"utf-8",
        "dataType":"json",
        success : function(data) {
            let tmp="";
            $.each(data.Response.Data.Table, function(index, value) {
                tmp += value.MailAddress+"\n";
            })
            $p.set($p.getControl("説明A"),tmp);
        },
        error : function(data) {
            // Error
            alert("拡張SQLのAPIは失敗しました");
        }
    });
})

 なお、on_editor_loadに下記を設定すると各ボタンをクリックしたあとにも処理が実行されるため、担当者を変更→ボタンクリックで自動的に説明Aに設定されるようになります。

実行してみる

それでは実行してみましょう。

設定したテーブルで新規データを登録しあと、ブラウザの更新ボタンをクリックします。すると、説明Aにユーザに紐づくメールアドレスが表示されていると思います。表示されない場合は、担当に表示されたユーザにメールアドレスが登録されているかを確認してみてください。

まとめ

今回は、拡張SQLをAPIから利用してみました。最初にも書きましたが、データを壊したり、見えてはいけないデータを見せることができますので、取り扱いには十分お気をつけください。

弊社では、1時間単位でご相談頂けるサービス「気軽 ni プリザンター」をご提供させてただいています。Zoomなどのオンライン会議を利用し、全国どこでもご対応させていただくことが可能です。

また、スクリプトやスタイルを利用したカスタマイズについてご支援させていただいております。スクリプトについて相談したい、業務を改善のために実現したい機能がある、や、本記事や過去の記事のスクリプトを応用したい、などありましたら、お気軽にお問合せください。

それでは、今回はこの辺で。
最後までご覧いただき、ありがとうございました。