Collabofloc コラボフローの申請フォームで SQL Server のマスタデータを参照する：CData API Server連携

CDataAPIServerAutocomplete.js

(function () {
    'use strict';

    // オートコンプリートの設定フィールド
    const AutocompleteSetting =
    {
        // 商品名：オートコンプリートの対象フィールド
        InputName: 'fid12', 

        // 明細行数
        ListRowNumber : 15,

        // オートコンプリートの検索フィールド名
        ApiListupFiledColumn : 'Name',

        // 対象APIのKey
        ApiListupKeyColumn : 'ProductID',

        // 以下でAPI名とフィールド名の関連付け
        Mappings: [
            {
                // 型番
                PartsName: 'fid13',
                APIName: 'ProductNumber'
            },
            {
                // 標準単価
                PartsName: 'fid15', 
                APIName: 'StandardCost'
            },
            {
                // 仕入単価
                PartsName: 'fid16', 
                APIName: 'StandardCost'
            },
            {
                // 御提供単価
                PartsName: 'fid17', 
                APIName: 'ListPrice'
            }
        ]
    };

    const CDataAPIServerSetting = {
        // API Server URL
        ApiServerUrl : 'http://XXXXXX.ap-northeast-1.compute.amazonaws.com:8387',

        // API Server Resource Name
        ApiServerResourceName : 'Products',

        // API Server Key
        Headers : { Authorization: 'Basic XXXXXXXX' },

        // General Properties
        ParseType : 'json',

        get BaseUrl() {
            return CDataAPIServerSetting.ApiServerUrl + '/api.rsc/' + CDataAPIServerSetting.ApiServerResourceName
        }
    }

    let results = [];
    let records = [];

    // Set autocomplete processing for target input field
    collaboflow.events.on('request.input.show', function (data) {
        for (let index = 1; index < AutocompleteSetting.ListRowNumber; index++) {
            $('#' + AutocompleteSetting.InputName + '_' + index).autocomplete({
                source: AutocompleteDelegete,
                autoFocus: true,
                delay: 500,
                minLength: 2
            });
        }
    });

    // This function get details from API Server, Then set values at each input fields based on mappings object.
    collaboflow.events.on('request.input.' + AutocompleteSetting.InputName + '.change', function (eventData) {
        debugger;

        let tartgetParts = eventData.parts.tbl_1.value[eventData.row_index - 1]; 

        let keyId = tartgetParts[AutocompleteSetting.InputName].value.split(':')[1];
        let record = records.find(x => x[AutocompleteSetting.ApiListupKeyColumn] == keyId);

        if (!record)
            return;

        AutocompleteSetting.Mappings.forEach(x => tartgetParts[x.PartsName].value = '');
        AutocompleteSetting.Mappings.forEach(x => tartgetParts[x.PartsName].value = record[x.APIName]);
    });

    function AutocompleteDelegete(req, res) {
        let topParam = '&$top=10'
        let queryParam = '$filter=contains(' + AutocompleteSetting.ApiListupFiledColumn + ',\'' + encodeURIComponent(req.term) + '\')';
        collaboflow.proxy.get(
            CDataAPIServerSetting.BaseUrl + '?' + 
            queryParam + 
            topParam, 
            CDataAPIServerSetting.Headers, 
            CDataAPIServerSetting.ParseType).then(function (response) {
            results = [];
            records = [];

            if (response.body.value.length == 0) {
                results.push('No Results')
                res(results);
                return;
            }

            records = response.body.value;
            records.forEach(x => results.push(x[AutocompleteSetting.ApiListupFiledColumn] + ':' + x[AutocompleteSetting.ApiListupKeyColumn]));
            res(results);
        }).catch(function (error) {
            alert(error);
        });
    }
})();