Connect to a SFTP location, import a file and create or update Releases

Connect to a SFTP location, import a file and create or update Releases.js

/*
This script will import a file from a remote server location, and import the file into Plutora using API's.
The script will perform a validation of values in the file against the relevant field values in Plutora and throw an exception if the values don't exist

IMPORTANT: In order to run the script on you subdomain:
1. The script is coded to look for a file called example.csv
2. The values in the file must match the values in your subdomain, this includes:
 - Impacted System
 - Risk Level
 - Type
 - Status
 - Portfolio Association
3. There must be a custom field added to Releases called 'Free Text Field 1'
4. You must enter valid values in 'params' where prompted with <<INSERT TEXT>>
*/

/********************************************************
*************STEP 1: Import npm's************************
********************************************************/

const https = require("https");
const http = require("http");
const xlsx = require("xlsx");
const fs = require("fs");
const ssh2 = require('ssh2');


/********************************************************
*******STEP 2: Define you API and SSH parameters*********
********************************************************/
const params = {
    auth: {
      server: {
        hostname: '<<INSERT OAUTH API>>',
      },
      client_id: "<<INSERT CLIENT_ID>>",
      client_secret: "<<INSERT CLIENT_SECRET>>",
      grant_type: "password",
      username: "<<INSERT USERNAME>>",
      password: "<<INSERT PASSWORD>>",
    },
    api: {
      server: {
        hostname: '<<INSERT API>>',
      }
    },
    ssh: {
      host: '<<INSERT HOST IP>>',
      port: 22,
      username: '<<USERNAME>>',
      password: '<<PASSWORD>>'
    }
  };

/********************************************************
****STEP 3: Create an API Request function********
********************************************************/
const makeRequest = function (options, payload = '') {
  return new Promise(function (resolve, reject) {
    let req = https.request(options, (res) => {

      let body = "";
      res.on("data", data => {
        body += data;
      });
      res.on("end", () => {
        if (res.statusCode >= 200 && res.statusCode < 300) {
          resolve(body);
        }
        else {
          reject(body);
        }
      });
    });
    console.log('Request has been made for ' + JSON.stringify(options));
    req.on('error', (e) => {
      console.error(e.message);
      reject(e);
    });

    req.write(payload);
    req.end();
    console.log('Request made...');
  });
};


/********************************************************
****STEP 4: Define all the API requests required ********
********************************************************/

//Get Plutora oauth token
const getAuthToken = function (authParams) {

  const postData = `client_id=${authParams.client_id}&client_secret=${authParams.client_secret}&grant_type=${authParams.grant_type}&username=${authParams.username}&password=${authParams.password}`

  const options = {
    hostname: authParams.server.hostname,
    path: '/oauth/token',
    method: 'POST',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded',
      'Content-Length': postData.length
    }
  };

  return makeRequest(options, postData).then((data) => {
    return JSON.parse(data);
  });
};

//Get the list of existing Release
const getReleases = function (apiParams) {
  const options = {
    hostname: apiParams.server.hostname,
    path: '/Releases',
    method: 'GET',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };

  return makeRequest(options).then((data) => {
    const releases = JSON.parse(data);
    return releases.reduce(function (map, obj) {
      map[obj.identifier] = obj;
      return map;
    }, {});
  });
};

//Get the list of valid values of all LookUpFields for the Release entity in Plutora
const getLookupFields = function (apiParams, type) {
  const options = {
    hostname: apiParams.server.hostname,
    path: '/lookupfields/' + type,
    method: 'GET',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };

  return makeRequest(options).then((data) => {
    const lookupFields = JSON.parse(data);
    return lookupFields.reduce(function (map, obj) {
      map[obj.value] = obj;
      return map;
    }, {});
  });
};

//Get the list of existing Additional Information field for the Release entity in Plutora
const getAdditionalInformations = function (apiParams, id) {

  const options = {
    hostname: apiParams.server.hostname,
    path: '/Releases/' + id + '/additionalInformation',
    method: 'GET',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };

  return makeRequest(options).then((data) => {
    const additionalInformations = JSON.parse(data);
    return additionalInformations.reduce(function (map, obj) {
      map[obj.name] = obj;
      return map;
    }, {});
  });
};

//Get the list of existing Systems
const getSystems = function (apiParams) {
  const options = {
    hostname: apiParams.server.hostname,
    path: '/systems',
    method: 'GET',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };

  return makeRequest(options).then((data) => {
    const systems = JSON.parse(data);
    return systems.reduce(function (map, obj) {
      map[obj.name] = obj;
      return map;
    }, {});
  });
};

//Get the list of existing Organization structure
const getOrganizations = function (apiParams) {
  const options = {
    hostname: apiParams.server.hostname,
    path: '/organizations',
    method: 'GET',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };

  return makeRequest(options).then((data) => {
    const organizations = JSON.parse(data);
    return organizations.reduce(function (map, obj) {
      map[obj.name] = obj;
      return map;
    }, {});
  });
};

//Get the details of an existing Release by GUID
const getReleaseById = function (apiParams, id) {

  const options = {
    hostname: apiParams.server.hostname,
    path: '/Releases/' + id,
    method: 'GET',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };

  return makeRequest(options).then((data) => {
    return JSON.parse(data);
  });
};


//Create a new Release in Plutora
const createRelease = function (apiParams, release) {

  const postData = JSON.stringify(release);

  const options = {
    hostname: apiParams.server.hostname,
    path: '/Releases',
    method: 'POST',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Content-Length': postData.length,
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };

  return makeRequest(options, postData).then((data) => {

    return JSON.parse(data);
  });
};

//Update an existing Release in Plutora
const updateRelease = function (apiParams, release) {

  const postData = JSON.stringify(release);

  const options = {
    hostname: apiParams.server.hostname,
    path: '/Releases/' + release.id,
    method: 'PUT',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Content-Length': postData.length,
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };
  return makeRequest(options, postData).then((data) => {
    //update release returns no data? :\
    return data;
  });
};

//Update Additional Information of an existing Release in Plutora
const updateAdditionalInformation = function (apiParams, releaseId, fields){
  const postData = JSON.stringify(fields);

  const options = {
    hostname: apiParams.server.hostname,
    path: '/Releases/' + releaseId +'/additionalInformation',
    method: 'PUT',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Content-Length': postData.length,
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };
  return makeRequest(options, postData).then((data) => {
    //update release additional information returns no data? :\
    return data;
  });
};

//Associate a System to a Release in Plutora
const associateSystemToRelease = function (apiParams, releaseId, system) {
  const postData = JSON.stringify(system);

  const options = {
    hostname: apiParams.server.hostname,
    path: `/Releases/${releaseId}/Systems`,
    method: 'POST',
	rejectUnauthorized: false,
    headers: {
      'Content-Type': 'application/json',
      'Content-Length': postData.length,
      'Authorization': 'Bearer ' + apiParams.auth_token
    }
  };
  return makeRequest(options, postData).then((data) => {
    //update release returns no data? :\
    return data;
  });
};



/********************************************************
******STEP 5: Define functions to retrieve a file********
********************************************************/
const downloadFile = function (url, path, useSsl = true) {
  return new Promise((resolve, reject) => {
    const protocol = useSsl ? https : http;

    const file = fs.createWriteStream(path);
    const request = protocol.get(url, function (response) {
      const stream = response.pipe(file);

      stream.on('finish', function () {
        resolve();
      });
    });
  });
};

const downloadFileSsh = function (sshParams, externalPath, path) {
  return new Promise((resolve, reject) => {

    var conn = new ssh2.Client();
    conn.on('ready', function () {
      conn.sftp(function (err, sftp) {
        if (err) {
          conn.end();
          reject(err);
        }

        sftp.fastGet(externalPath, path, {concurrency: 64, chunkSize: 32768}, (err) => {
          conn.end();

          if (err) {
            reject(err);
          }
          resolve();
        });
      });
    }).connect(sshParams);
  });
};
//#endregion

/*#region Utils: This function re-organises the structure of the file so any Parent/Child release 
dependencies are created in the correct order of Parent first then child*/
function tsort(edges) {
  var nodes = {}, // hash: stringified id of the node => { id: id, afters: lisf of ids }
    sorted = [], // sorted list of IDs ( returned value )
    visited = {}; // hash: id of already visited node => true

  var Node = function (id) {
    this.id = id;
    this.afters = [];
  }

  // 1. build data structures
  edges.forEach(function (v) {
    var from = v[0], to = v[1];
    if (!nodes[from]) nodes[from] = new Node(from);
    if (!nodes[to]) nodes[to] = new Node(to);
    nodes[from].afters.push(to);
  });

  // 2. topological sort
  Object.keys(nodes).forEach(function visit(idstr, ancestors) {
    var node = nodes[idstr],
      id = node.id;

    // if already exists, do nothing
    if (visited[idstr]) return;

    if (!Array.isArray(ancestors)) ancestors = [];

    ancestors.push(id);

    visited[idstr] = true;

    node.afters.forEach(function (afterID) {
      if (ancestors.indexOf(afterID) >= 0)  // if already in ancestors, a closed chain exists.
        throw new Error('closed chain : ' + afterID + ' is in ' + id);

      visit(afterID.toString(), ancestors.map(function (v) {
        return v
      })); // recursive call
    });

    sorted.unshift(id);
  });

  return sorted;
}

const asyncForEach = async function (array, callback) {
  for (let index = 0; index < array.length; index++) {
    await callback(array[index], index, array)
  }
};
//#endregion

/********************************************************
********STEP 6: Define the main run function ************
********************************************************/

const run = async function (args) {

  return new Promise(async (resolve, reject) => {

    const filePath = '/example.csv';

    const sshFilePath = 'example.csv';
    console.log(`downloading file via ssh on host: ${params.ssh.host}`);
    await downloadFileSsh(params.ssh, sshFilePath, filePath);

    console.log(`parsing file ${filePath}.`);
    const result = xlsx.readFile(filePath);

    let projects = xlsx.utils.sheet_to_json(result.Sheets[result.SheetNames[0]]);
    console.log(`${Object.keys(projects).length} projects found.`);

    //Sorting projects based on topology
    console.log('establishing project topology.');
    const edges = projects.map((project) => [project["ID"], project["Role Dependency Type"] ? project["Role Dependency Type"] : ""]);
    const ordered = tsort(edges).reverse();
    projects.sort(function (a, b) {
      return ordered.indexOf(a["ID"]) - ordered.indexOf(b["ID"]);
    });

    console.log(`retrieving auth token.`);
    const authToken = await getAuthToken(params.auth);
    const authApiParams = {...params.api, ...{auth_token: authToken.access_token}};

    console.log(`loading Release risk levels.`);
    const releaseRiskLevels = await getLookupFields(authApiParams, 'ReleaseRiskLevel');

    console.log(`loading systems.`);
    const systems = await getSystems(authApiParams);

    console.log(`loading releases.`);
    const existingReleases = await getReleases(authApiParams);

    console.log(`loading Release status options`);
    const releaseStatuses = await getLookupFields(authApiParams, 'ReleaseStatusType');

    console.log(`loading Release type options`);
    const releaseTypes = await getLookupFields(authApiParams, 'ReleaseType');

    console.log(`loading Organizations`);
    const organizationNodes = await getOrganizations(authApiParams);

    console.log('loading System Role Dependency Types');
    const systemRoleDependencyTypes = await getLookupFields(authApiParams, 'SystemRoleDependencyType');

    const incomingAdditionalInformationFields = ['Free Text'];

    console.log(`started processing loaded projects.`);
    await asyncForEach(projects, async (project) => {
      try {
        console.log(`processing project (${project["ID"]}).`);

        let parentRelease = existingReleases[project['Parent Release']];
        let riskLevel = releaseRiskLevels[project['Risk Level']];
        let organization = organizationNodes[project['Portfolio Association']];
        let releaseStatus = releaseStatuses[project['Status']];
        let releaseType = releaseTypes[project['Type']];

        if (parentRelease == null && project['Parent Release']) {
          console.warn(`project (${project["ID"]}) references a parent project ${project['Parent Release']} that doesn't exist, skipping.`);
          return;
        }

        let release = {
          "identifier": project["ID"],
          "name": project["Name"],
          "summary": project["Release Description"],
          "releaseRiskLevelId": riskLevel ? riskLevel.id : null,
          "parentReleaseId": parentRelease ? parentRelease.id : null,
          "implementationDate": project["Go Live Date"],
          "organizationId": organization ? organization.id : null,
          "releaseStatusTypeId": releaseStatus ? releaseStatus.id : null,
          "releaseTypeId": releaseType ? releaseType.id : null,
          "displayColor": "#888",
          "plutoraReleaseType": parentRelease ? "Integrated" : "Enterprise",
          "releaseProjectType": "IsProject",
          "additionalInformation": []
        };

        let currentRelease;
        if (existingReleases[release.identifier]) {
          console.log(`project (${project["ID"]}) exists.`);

          console.log(`retrieving release with id (${existingReleases[release.identifier].id}).`);
          let existingRelease = await getReleaseById(authApiParams, existingReleases[release.identifier].id);

          console.log(`merging project data with release data`);
          currentRelease = Object.assign({}, existingRelease, release/*, {additionalInformation: mergedAdditionalInformation}*/);

          console.log(`updating release with identifier (${project["ID"]}).`);
          await updateRelease(authApiParams, currentRelease);
        }
        else {
          console.log(`project (${project["ID"]}) doesn't exist, creating.`);
          currentRelease = await createRelease(authApiParams, release);
          //add to existing releases incase we need to reference as a parent release.
          existingReleases[currentRelease.identifier] = currentRelease;
        }

        //Handle additional information
        console.log("Setting additional information");
        let newAdditionalInformations=[];
        let additionalInformations = await getAdditionalInformations(authApiParams, currentRelease.id);
        incomingAdditionalInformationFields.forEach(field => {
          if (!additionalInformations[field] || !project[field]) return;
          newAdditionalInformations.push({
            "id": additionalInformations[field].id,
            "text": project[field]
          });
        });

        if (newAdditionalInformations.length > 0) await updateAdditionalInformation(authApiParams, currentRelease.id, newAdditionalInformations);

        let associatedSystem = systems[project['Impacted System']];
        if (associatedSystem) {
          console.log(`associating system ${associatedSystem.name} to project (${project["ID"]})`);
          await associateSystemToRelease(authApiParams, currentRelease.id, {
            systemId: associatedSystem.id,
            systemRoleType: "Impact",
            systemRoleDependencyTypeId: systemRoleDependencyTypes[Object.keys(systemRoleDependencyTypes)[0]].id
          });
        }
      }
      catch (ex) {
        console.error(ex);
      }
    });
    console.log(`finished processing loaded projects`);

    resolve();
  });
};


module.exports = {
  run: run
};

example.csv