Riferimenti: Esempio di script CCDS Plex UX

Verifica connessione
Importa struttura
Importare dati

Script di esempio di Plex UX

const ExternalMetadataDefinition = {
    Tables : [
        //note how constructions from the old Plex.DataSources object have migrated to the static definition.
        //this is due to losing a dynamic metadata builder with the UX DS API.

        {
            name : "7241", //this lands in the URL.  make sure it's the UX DS key
            displayName : "Item Usages Get UX",
            columns : [
                {name : "Usage_Date", displayName : "Usage_Date", type: "datetime"},
                {name : "Item_No", displayName : "Item_No"},
                {name : "Description", displayName : "Description"},
                {name : "Quantity", displayName : "Quantity", type: "float"},
                {name : "Location", displayName : "Location"},
                {name : "Cost", displayName : "Cost", type: "float"},
                {name : "Unit", displayName : "Unit"},
                {name : "Used_By_Name", displayName : "Used_By_Name"},
                {name : "Approved_By_Name", displayName : "Approved_By_Name"},
                {name : "Note", displayName : "Note"},
                {name : "Part_No", displayName : "Part_No"},
                {name : "Transaction_Type", displayName : "Transaction_Type"},
                {name : "Job_No", displayName : "Job_No"},
                {name : "Average_Cost", displayName : "Average_Cost", type : "float"},
                {name : "Item_Key", displayName : "Item_Key", type: "int"},
                {name : "Journal_Link", displayName : "Journal_Link", type : "int"},
                {name : "Postable", displayName : "Postable", type: "int"},
                {name : "Standard_Cost", displayName : "Standard_Cost", type: "float"},
                {name : "Item_Usage_Key", displayName : "Item_Usage_Key", type: "int"},
                {name : "Check_In", displayName : "Check_In", type: "int"},
                {name : "Checkout", displayName : "Checkout", type: "int"},
                {name : "Supplier_Code", displayName : "Supplier_Code"},
                {name : "Account_No", displayName : "Account_No"},
                {name : "Accounting_Job_No", displayName : "Accounting_Job_No"},
                {name : "Equipment_ID", displayName : "Equipment_ID"},
                {name : "Tool_Set_No", displayName : "Tool_Set_No"},
                {name : "Department_Code", displayName : "Department_Code"},
                {name : "Supplier_Cost", displayName : "Supplier_Cost", type: "float"},
                {name : "PO_No", displayName : "PO_No"}
            ],
            //this was originally two functions: getParameterNameList and getParameterValueList.
            getInputList : function(dataSource) {
                //`inputs` should result in an array of objects.
                let inputs = [{}];
                return inputs;
            }
        },

        {
            name : "2968", //this lands in the URL.  make sure it's the UX DS key
            displayName : "PO releases Due UX",
            columns : [
                {name : "PO_Key", displayName : "PO_Key", type: "int"},
                {name : "PO_No", displayName : "PO_No"},
                {name : "Supplier_No", displayName : "Supplier_No", type : "int"},
                {name : "PO_Ship_To", displayName : "PO_Ship_To"},
                {name : "PO_Ship_Via", displayName : "PO_Ship_Via"},
                {name : "Supplier_Code", displayName : "Supplier_Code"},
                {name : "Name", displayName : "Name"},
                {name : "Line_Item_No", displayName : "Line_Item_No", type : "int"},
                {name : "Description", displayName : "Description"},
                {name : "Part_Key", displayName : "Part_Key", type : "int"},
                {name : "Material_Key", displayName : "Material_Key", type : "int"},
                {name : "Item_Key", displayName : "Item_Key", type : "int"},
                {name : "Part_No", displayName : "Part_No"},
                {name : "Part_Name", displayName : "Part_Name"},
                {name : "Revision", displayName : "Revision"},
                {name : "Material_Code", displayName : "Material_Code"},
                {name : "Item_No", displayName : "Item_No"},
                {name : "Due_Date", displayName : "Due_Date", type: "datetime"},
                {name : "On_Order", displayName : "On_Order", type: "float"},
                {name : "Received", displayName : "Received", type: "float"},
                {name : "Standard_Pack", displayName : "Standard_Pack", type: "float"}
            ],
            //this was originally two functions: getParameterNameList and getParameterValueList.
            getInputList : function(dataSource) {
                //`inputs` should result in an array of objects.
                let inputs = [{}];
                return inputs;
            }
        }
    ]
};


/******************

Test Connection

******************/
function testConnection(context) {
    ai.log.logInfo('Plex exposes no "login" API, so the test is trivially true.', 'Import Structure to test access against applicable data sources');
    return true;
}


/******************

Import Structure

******************/
function importStructure(context) {

    //structure flow was completely revamped for static definition processes.
    //it behaves much more like file cabinet solutions now.
    const addColumn = function(table, column, order) {
        let recordColumn = table.addColumn(column.name);
        recordColumn.setDisplayName(column.displayName);
        switch (column.type) {
            case "text":
                recordColumn.setTextColumnType(column.length);
                break;
            case "int":
                recordColumn.setIntegerColumnType();
                break;
            case "float":
                recordColumn.setFloatColumnType();
                break;
            case "boolean":
                recordColumn.setBooleanColumnType();
                break;
            case "datetime":
                recordColumn.setDateTimeColumnType();
                break;
            default:
                recordColumn.setTextColumnType(200);
                break;
        }
        recordColumn.setDisplayOrder(order);
        recordColumn.setIncludeByDefault(column.includeByDefault || false);
        recordColumn.setIncludeInKey(column.includeInKey || false);
        recordColumn.setMandatoryForImports(column.mandatoryForImport || false);
        recordColumn.setRemoteColumnType(column.remoteColumnType || "String");
    };

    const builder = context.getStructureBuilder();

    for (let i in ExternalMetadataDefinition.Tables) {
        let externalTable = ExternalMetadataDefinition.Tables[i];
        let table = builder.addTable(externalTable.name);
        table.setDisplayName(externalTable.displayName);

        for (let j = 0; j < externalTable.columns.length; j++) {
            addColumn(table, externalTable.columns[j], j + 1);
        }
    }
}


/******************

Import Data

******************/
function importData(context) {
    //made addRows a function to "hide" some of the nested loops for easier reading.
    const addRows = function(rowset, rows) {
        for (let y = 0; y < rows.length; y++) {
            let row = rows[y];
            let cells = [];
            for (let x = 0; x < rowset.getColumns().length; x++) {
                let column = rowset.getColumns()[x];
                let columnIndex = column.getDisplayOrder()-1;

                cells.push(row[columnIndex]);
            }
            rowset.addRow(cells);
        }
    };

    const getAuthHeader = function(dataSource, pcn) {
        let username = dataSource.getSetting(pcn + ' Username').getValue();
        let password = dataSource.getSetting(pcn + ' Password').getValue();
        let auth = {
            'Authorization': 'Basic ' + ai.util.encodeBase64(username + ':' + password, '')
        };
        return auth;
    };

    //regular initializations; nothing special.
    const dataSource = context.getDataSource();
    let rowset = context.getRowset(); //var as rowset intrinsically gets modified one line down.  everything else is constant for a script flow.
    rowset.setSmartParsingEnabled(true);
    const rowsetId = rowset.getTableId();

    //replacing the old, static URL with one that dynamically updates based on the UX DS Key (when it's captured as the rowset name)
    const url = 'https://cloud.plex.com/api/datasources/' + rowsetId + '/execute';

    //this process still needs some metadata configuration for support.  we get it from the ExternalMetadataDefiniton object here.
    const internalScriptIndex = getInternalScriptIndex(rowsetId);

    //build the array of PCNs to loop over by user input.
    const pcnList = dataSource.getSetting("Plex PCNs").getValue().split(',');

    for (let i = 0; i < pcnList.length; i++) {

        let pcn = pcnList[i];
        let headers = getAuthHeader(dataSource, pcn);
        let inputList = ExternalMetadataDefinition.Tables[i].getInputList(dataSource);

        for (let j = 0; j < inputList.length; j++) {

            let bodyObj = {};
            bodyObj.inputs = inputList[j];

            let body = JSON.stringify(bodyObj);

            let response = ai.https.request(url, 'POST', body, headers);
            let rows = JSON.parse(response.getBody()).tables[0].rows;

            //consider adding a variable to assess rowLimitExceeded.
            //JSON.parse(response.getBody()).tables[0].rowLimitExceeded
            //i won't say it should throw the script, but that's dealer's choice.

            addRows(rowset, rows);
        }
    }
}

//used to get the metadata definition object's index associated with a specific Plex key.
function getInternalScriptIndex(dataSourceKey) {
    var scriptIndex;
    for (var i = 0; i < ExternalMetadataDefinition.Tables.length; i++) {
        if (ExternalMetadataDefinition.Tables[i].name == dataSourceKey) {
            scriptIndex = i;
            break;
        }
    }
    return scriptIndex;
}

//given Plex structures, getMonthCount and convertToYYYYMM likely still have merit.
function getMonthCount(date1, date2) {
    var date1Year = date1.getFullYear();
    var date2Year = date2.getFullYear();
    var date1Month = date1.getMonth();
    var date2Month = date2.getMonth();

    return (date2Month + 12 * date2Year) - (date1Month + 12 * date1Year);
}

function convertToYYYYMM(date) {
    var period = '';
    if (date.getMonth() + 1 < 10) {
        period += date.getFullYear() + '0' + (date.getMonth() + 1);
    } else {
        period += date.getFullYear() + '' + (date.getMonth() + 1);
    }
    return period;
}