PharmCAS download for Salesforce

pharmcasImport.page

<apex:page controller="pharmcasExportController">

<link href="{!URLFOR($Resource.Bootstrap337, 'css/bootstrap-namespaced.min.css')}" rel="stylesheet" />
<style>
	@media all and (max-width: 840px) {
	    .card-container {
	        flex-direction: column;
	    }
	}
	.card-container {
		display:flex;
		justify-content: center;
	}
	.card {
		margin: 10px;
	}
	.card-detail {
		margin-top: auto;
	}
	.card-content-center{
		display: flex; 
		flex-direction: column; 
		align-items: center;
	}
	#advancedOptions .dropdown-menu {
		padding:10px;
	}
	#advancedOptions .dropdown-menu li {
		margin-left: inherit;
		margin: 10px;
	}
</style>

<div class="bootstrap-sf1">

	<div class="page-header context-lead">
		<h1>PharmCAS Import</h1>
	</div>

	<div class="container">

		<div class="card-container">
			<div class="card col-lg-5 col-xs-8">
				<div class="card-heading">Verified Applicants</div>
				<div class="card-detail">
					<div class="row">
						<div class="col-xs-12">
							<button id="btnExportApplicants" type="button" class="btn btn-default">Begin New Export</button>
							<div id="advancedOptions" class="btn-group">
							  <button type="button" class="btn btn-default dropdown-toggle" aria-haspopup="true" aria-expanded="false">
							    Advanced Options <span class="caret"></span>
							  </button>
							  <ul class="dropdown-menu">
							    <li>
							    	<button id="btnDownloadLastExport" type="button" class="btn btn-default" >Process Last Export</button>
						    	</li>
							    <li role="separator" class="divider"></li>
							    <li>
									<label for="inputExportDate">Application Filter Date</label>
									<input id="inputExportDate" type="date" class="form-control" value="{!lastExportDateString}" />
							    </li>
							  </ul>
							</div>
						</div>
					</div>
				</div>
			</div>

			<div class="card col-lg-5 col-xs-8">
				<div class="card-heading">In-Progress Applicants</div>
				<div class="card-detail form-group">
					<input type="file" id="fileInprogress" />
					<p class="help-block" id="fileInprogressError">&nbsp;</p>
					<button id="btnUploadInProgress" type="button" class="btn btn-default">Upload</button>
				</div>
			</div>
		</div>

		<pre id="output" style="display:none"></pre>

		<div id="importResults" style="display:none">
			<table class="table table-hover">
				<thead>
					<tr>
						<th>Action</th>
						<th>PharmCAS ID</th>
						<th>Application Last Changed Date</th>
						<th>Status</th>
						<th>Lead</th>
					</tr>
				</thead>
				<tbody>
				</tbody>
			</table>
		</div>
	</div>
</div>

<!-- Promise API polyfill for IE 11 -->
<script src="https://cdn.polyfill.io/v2/polyfill.min.js?features=Promise"></script>
<!-- CSV parsing lib -->
<script src="{!URLFOR($Resource.PharmcasExport, 'js/papaparse.min.js')}"/>
<!-- Date lib -->
<script src="{!URLFOR($Resource.PharmcasExport, 'js/moment.min.js')}"></script>
<!-- XLS parsing lib for in-progress applicants -->
<script src="{!URLFOR($Resource.PharmcasExport, 'js/xlsx.core.min.js')}"></script>
<script type="text/javascript">

(function(){
	'use strict';

	/*
		This is a VisualForce page that will 
			1) download a csv from the WebAdMIT system
			2) convert the csv into JSON objects
			3) send these JSON objects to Salesforce for processing
			4) display a table of results when done

		Some noteworthy functions:
			exportApplicants - initializes the WebAdMIT export and checks its status until the export is available for download.
			handleDownload - downloads the csv export, converts it to JSON records, upserts them to salesforce and displays the results.
			upsertApps - upserts the JSON records to Salesforce (in batches of 200 each).
			displayImportResults - converts the upsert results into the #importResults table
	*/

	//register click handlers
	document.getElementById('btnExportApplicants').addEventListener('click', function(){
		exportApplicants()
			.then(handleExport)
			.catch(handleError);
	});
	document.getElementById('btnDownloadLastExport').addEventListener('click', function(){
		document.getElementById('advancedOptions').classList.remove('open');

		downloadLastExport({!fileId})
			.then(handleExport)
			.catch(handleError);
	});
	document.getElementById('btnUploadInProgress').addEventListener('click', handleFile, false);
	document.getElementById('inputExportDate').addEventListener('change', function(event){
		lastExportDate = moment(event.target.value);
		console.log(lastExportDate);
	});
	document.getElementById('advancedOptions').addEventListener('click', function(event) {
		if (!!event.target.getAttribute('aria-haspopup')) {
			var opened = this.classList.toggle('open');
			this.setAttribute('aria-expanded', opened);
		}
	});
	console.log('ready');

	var fileInprogressError = document.getElementById('fileInprogressError');
	var outputElem = document.getElementById('output');
	var importResultsTable = document.getElementById('importResults');
	var maxPollAttempts = 12;
	var waitTimeBetweenPolls = 60*1000;
	var pharmcasDateFormat = 'MMDDYYYY';

	function clearResults() {
		fileInprogressError.innerHTML = '&nbsp;';
		outputElem.innerText = '';
		importResults.querySelector('tbody').innerHTML = '';
		importResults.style.display = 'none';
	}

	function downloadLastExport(fileId) {
		clearResults();
		return checkExportStatus(fileId)
			.then(function(respJson) {
				if (respJson.status === 'Available') {
					return respJson.download_url;
				} else {
					Promise.reject(new Error('Report is not available. Status: ' + respJson.status));
				}
			});
	}

	function exportApplicants() {
		clearResults();
		logOutput('initializing export');
		return initiateExport()
			.then(function(res) {
				logOutput('initialized.');
				var attempts = 0;
				return checkExportStatusUntilDone(res.id, attempts)
					.then(function(respJson) {
						return respJson.download_url;
					});
			});

		function checkExportStatusUntilDone(fileId, attempts) {
			if (attempts >= maxPollAttempts) {
				Promise.reject(new Error('The export is taking too long... Quitting.'));
				return;
			}
			attempts++;
			logOutput('checking status.');
			return checkExportStatus(fileId)
				.then(function(respJson) {
					logOutput('status is ' + respJson.status);
					if (respJson.status === 'Available') {
						return respJson;
					} else {
						logOutput('scheduling next check in ' + Math.round(waitTimeBetweenPolls / 1000, 0) + ' seconds.' );
						return new Promise(function(resolve, reject){
							setTimeout(function() {
								resolve(checkExportStatusUntilDone(fileId, attempts));
							}, waitTimeBetweenPolls);
						
						});
					}
				});
		}
	}

	function handleExport(downloadUrl) {
		logOutput('downloading export from ' + formatHyperlink(downloadUrl, 'WebAdMIT'));

		return downloadExport(downloadUrl)
			.then(function(exportCsv) {
				logOutput('downloaded.');
				var records = parseCsv(exportCsv);

				logOutput(records.length + ' records found');
				if (records.length === 0) {
					return;
				}

				return upsertApps(records)
					.then(function(results){
						return handleResults(results, records);
					});
			});

		function parseCsv(csv) {
			var filteredRecords = [];
			Papa.parse(csv, {
				header: true,
				slipEmptyLines: true,
				step: function(results, parser){
					filteredRecords.push(results.data[0]);
				}
			});
			return filteredRecords;
		}		
	}

	function handleFile() {
		clearResults();
		
		var fileInput = document.getElementById('fileInprogress');
		if (fileInput.files.length === 0) {
			fileInprogressError.innerText = 'You must select a file before uploading';
			return;
		}
		var inProgressExcelFile = fileInput.files[0];

		// check type
		if (inProgressExcelFile.name !== 'In-Progress_report.xls') {
			fileInprogressError.innerText = 'You can only upload In-Progress_report.xls';
			return;
		}


		// load file
		var rABS = typeof FileReader !== "undefined" && typeof FileReader.prototype !== "undefined" && typeof FileReader.prototype.readAsBinaryString !== "undefined";
		var reader = new FileReader();
		reader.onload = function(e) {
			var data = e.target.result;
			var wb;
			if(rABS) {
				wb = XLSX.read(data, {type: 'binary'});
			} else {
				var arr = fixdata(data);
				wb = XLSX.read(btoa(arr), {type: 'base64'});
			}
			process_wb(wb);
		};
		if(rABS) {
			console.log('reading as binary string');
			reader.readAsBinaryString(inProgressExcelFile);
		}
		else {
			console.log('reading as array buffer');
			reader.readAsArrayBuffer(inProgressExcelFile);
		}

		function fixdata(data) {
			var o = "", l = 0, w = 10240;
			for(; l<data.byteLength/w; ++l) o+=String.fromCharCode.apply(null,new Uint8Array(data.slice(l*w,l*w+w)));
			o+=String.fromCharCode.apply(null, new Uint8Array(data.slice(l*w)));
			return o;
		}

		function process_wb(wb) {
			var jsonWorkbook = to_json(wb);
			var records = jsonWorkbook.Worksheet1;
			if (!records) {
				throw new Error('Cannot find Worksheet1');
			}
			return upsertApps(records)
					.then(handleResults)
					.catch(handleError);
		}

		function to_json(workbook) {
			var result = {};
			workbook.SheetNames.forEach(function(sheetName) {
				var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
				if(roa.length > 0){
					result[sheetName] = roa;
				}
			});
			return result;
		}
	}

	function handleResults(results, records) {
		var stats = results.reduce(function(counts, result) {
			counts.succeeded += (result.success ? 1 : 0);
			counts.created += (result.created ? 1 : 0);
			counts.failed += (result.success ? 0 : 1);
			return counts;
		}, {succeeded: 0, created: 0, failed: 0});

		logOutput(stats.succeeded + ' succeeded, ' + stats.failed + ' failed');
		logOutput(stats.created + ' created, ' + (stats.succeeded - stats.created) + ' updated');

		if (stats.failed > 0 ) {
			results.forEach(function(result, index) {
				if (!result.success) {
					logOutput(result.errors.map(function(error){ 
						var record = records[index];
						var message = 'CAS ID: ' + record.cas_id + ' - ' + error.status + ': ' + error.message;
						if (error.fields) {
							message += '[' + error.fields.join(', ') + ']';
						}
						return message;
					 }).join('; '));
				}
			});
		}

		return displayImportResults(results);

		function displayImportResults(results) {
			var resultsMap = results.reduce(function(newObj, result){ 
				if (result.success) {
					newObj[result.id] = result;
				}
				return newObj;
			}, {});
			var appIds = Object.keys(resultsMap);

			return fetchImportResults(appIds)
				.then(function(apps) {
					var tbody = importResultsTable.querySelector('tbody');
					apps.forEach(function(app) {
						var upsertResult = resultsMap[app.Id];
						var row = buildRowForApp(app, upsertResult);
						tbody.appendChild(row);
					});
					importResultsTable.style.display = 'block';
				});

			function buildRowForApp(app, upsertResult) {
				return (upsertResult.success === true) ? buildSuccessRow(app, upsertResult) : buildErrorRow(app, upsertResult);
			}

			function buildSuccessRow(app, upsertResult) {
				var row = document.createElement('tr');
				addCell(row, (upsertResult.created ? 'Created' : 'Updated') );
				addCell(row, '<a target="_blank" href="/' + app.Id + '">' + app.PharmCAS_ID__c + '</a>', true)
				addCell(row, moment.utc(app.Application_Date__c).format('MM/DD/YYYY'));
				addCell(row, app.Application_Status__c || '');
				if (app.Lead__c) {
					addCell(row, '<a target="_blank" href="/' + app.Lead__c + '">' + app.Lead__r.Name + '</a>', true);
				} else {
					addCell(row, '');
				}
				return row;
			}

			function buildErrorRow(app, upsertResult) {
				var row = document.createElement('tr');
				row.className = 'danger';
				addCell(row, 'Failed');
				var cell = document.createElement('td');
				cell.colSpan = 4;
				cell.innerText = upsertResult.errors.map(function(error) { 
					return error.statusCode + ': ' + error.message + ' [' + error.fields.join(',') + ']';  
				}).join('\n');
				row.appendChild(cell);
				return row;
			}

			function addCell(row, content, asHtml) {
				var cell = document.createElement('td');
				if (asHtml) {
					cell.innerHTML = content;
				} else {
					cell.innerText = content;
				}
				row.appendChild(cell);
			}
		}
	}


	/* Remote Actions */
	/*
	Schedule PharmCAS export - we will need the x-api-key
	POST https://api.webadmit.org/api/v1/user_identities/172736/exports/222183/export_files
	*/
	function initiateExport() {
		return invokeRemoteAction('{! $RemoteAction.pharmcasExportController.initiateExport }', [], {escape: false})
			.then(function(res){
				return res.export_files;
			});
	}


	/*
	Check on status of export 
	GET https://api.webadmit.org/api/v1/exports/222183/export_files/180718
	*/
	function checkExportStatus(fileId) {
		return invokeRemoteAction('{! $RemoteAction.pharmcasExportController.checkExportStatus }', [fileId], {escape: false})
			.then(function(res){
				return res.export_files;
			});
	}

	/*
	Download Export (from AWS)
	*/
	function downloadExport(downloadUrl) {
		return invokeRemoteAction('{! $RemoteAction.pharmcasExportController.downloadExport }', [downloadUrl], {escape: false});
	}

	function fetchImportResults(appIds) {
		return invokeRemoteAction('{! $RemoteAction.pharmcasExportController.fetchImportResults }', [appIds], {escape: false});
	}

	/*
	Upsert Applications
	*/
	var maxBatchSize = 200;
	function upsertApps(apps) {
		var batches = [];
		for (var idx = 0; idx < apps.length; idx += maxBatchSize) {
			batches.push(apps.slice(idx, idx+maxBatchSize));
		}

		logOutput('Beginning upload.')
		return upsertAppsRecursively(batches);

		function upsertAppsRecursively(batches) {
			var batch = batches.pop();

			return invokeRemoteAction('{! $RemoteAction.pharmcasExportController.upsertApps }', [batch], {escape: false, timeout: 120000})
				.then(function(results) {
					logOutput('uploaded ' + results.length + ' records');
					if (batches.length == 0) {
						return results;
					}

					return upsertAppsRecursively(batches).then(function(moreResults) {
						
						return results.concat(moreResults);
					});
					
				});
		}
		
	}


	/* helpers */
	function logOutput(text) {
		outputElem.style.display = 'block';
		outputElem.innerHTML += text+'\n';
	}

	function handleError(error) {
		console.error(error);
		logOutput('ERROR: ' + error.message);
	}

	function formatHyperlink(url, text) {
		return '<a href="' + url + '">'+text+'</a>';
	}

	function invokeRemoteAction(action, parameters, options) {
        return new Promise(function (resolve, reject) {
            var args = [action];
            if (parameters) {
            	args = args.concat(parameters);
            }
            args.push(function (result, event) {
                if (event.status) {
                    resolve(result);
                }
                else {
                    reject(event);
                }
            });
            if (options) {
            	args.push(options);
            }
            var vfrManager = window.Visualforce.remoting.Manager;
            vfrManager.invokeAction.apply(vfrManager, args);
        });
	}
})();

</script>
	
</apex:page>

PharmcasImportController.cls

/*
PharmCAS Import Controller - Used by VF Page pharmcasImport
	This controller mostly offers some RemoteAction methods to our Visualforce page

	Most of the Remote Actions act as proxies for web service calls to the WebAdMIT system in order to work around CORS restrictions.
	e.g. initiateExport, checkExportStatus, and downloadExport

	upsertApps is the main action on this controller. It will receive a list of WebAdMIT records and process them in Salesforce.
	For more info on this refer to the Technical Documentation at https://docs.google.com/document/d/edit
*/

public with sharing class pharmcasExportController {

	// TODO: refactor to make these configurable
	public static String userId { get { return '12345'; } }
	public static String exportId { get { return '23456'; } }
	public static String fileId {get { return '34567'; }}
                      
	public static Id pharmacyRecordTypeId { get { return ApplicationsExt.pharmacyRecordTypeId; }}

	@TestVisible
	private static PharmCasImportService.I appService {
		get {
			if (appService == null) {
				appService = new PharmCasImportService();
			}
			return appService;
		}
		set;
	}

	private static ApplicationsData appSelector {
		get {
			if (appSelector == null) {
				appSelector = new ApplicationsData();
			}
			return appSelector;
		}
		set;
	}

	
	/* Remote Actions */
	// this action will generate a new export for download
	@RemoteAction
	public static Map<String, Object> initiateExport() {
		HttpRequest req = buildPharmcasRequest('POST', '/api/v1/user_identities/' + userId + '/exports/' + exportId + '/export_files');
		Http httpService = new Http();
		HttpResponse resp = httpService.send(req);
		String respBody = resp.getBody();
		return (Map<String, Object>)JSON.deserializeUntyped(respBody);
	}

	// this action will check the status of the last export initiated
	@RemoteAction
	public static Map<String, Object> checkExportStatus(Integer paramFileId) {
		String statusUrl = '/api/v1/exports/' + exportId + '/export_files/' + paramFileId;
		HttpRequest req = buildPharmcasRequest('GET', statusUrl);
		Http httpService = new Http();
		HttpResponse resp = httpService.send(req);
		String respBody = resp.getBody();
		return (Map<String, Object>)JSON.deserializeUntyped(respBody);
	}

  // this action will download the exported csv from aws
	@RemoteAction
	public static String downloadExport(String downloadUri) {
		Url downloadUrl = new Url(downloadUri);

		if (downloadUrl.getHost() != 'webadmit-production.s3.amazonaws.com') {
			throw new PharmCASException('Invalid URL: ' + downloadUri);
		}
		HttpRequest req = new HttpRequest();
		req.setMethod('GET');
		req.setEndpoint(downloadUri);
		Http httpService = new Http();
		HttpResponse resp = httpService.send(req);
		if (resp.getStatusCode() == 200) {
			return resp.getBody();
		} else {
			throw new PharmCASException('Error downloading export from PharmCAS. \nError: ' + resp.getBody() + ' \nURL: ' + downloadUri);
		}
	}

  	// this action will take an array of json records and process them
  	// it will return the upsert results
	@RemoteAction
	public static Database.UpsertResult[] upsertApps(List<Map<String, Object>> records) {
		appService
			.init(records)
			.enrichWithApplicationInfo();

		appService
			.matchLeads()
			.updateMatchedLeads()
			.createLeads();

		Application__c[] appsToUpsert = appService.getApps();

		Database.UpsertResult[] results = appSelector.UpsertSobjects(appsToUpsert);

		return results;
	}

  	// this action will fetch a list of applications for display to the end user
	@RemoteAction
	public static Application__c[] fetchImportResults(Id[] appIds) {
		return appSelector.selectById(new Set<Id>(appIds));
	}


	/* private static methods */
	private static HttpRequest buildPharmcasRequest(String method, String endpoint) {
		HttpRequest req = new HttpRequest();
		req.setMethod(method);
		req.setEndpoint('callout:pharmcas' + endpoint);
		req.setHeader('x-api-key', '{!$Credential.Password}');
		return req;
	}

	/* helper classes */
	public class PharmCASException extends Exception {}
}

Hi Ghub,

Thanks for your code. I have a similar scenario... I need to integrate the Webadmit to Salesforce... to import the data to Leads.

I'm just wondering if you have any recommendations before I start with this.

I'm experienced programmer but this is my first salesforce project.

Thanks,
Pedro Garcia
pgarcia@ccnm.edu