Third Light Developer Exchange

steve

Third Light Staff

Registered: 2013-06-06

Posts: 105

Importing user accounts from a spreadsheet

This example is designed to be run from the CLI and allows a list of users accounts to be created through the API based on input taken from a .csv file.

Example .csv content

Username    First Name   Surname   Email                 Group
simon       Simon        Jones     sjones@company.com    admin
bob         Robert       Miller    rmiller@company.com   Marketing
bill        William      Smith     wsmith@company.com    Accounts
alice       Alice        Walker    awalker@company.com

Example usage:

php UsersFromSpreadsheet.php -h "yoursite.com" -u "admin" -p "yourpassword" -f "sample_user_spreadsheet.csv"

This script makes use of the PHP client

<?php

// Display request and response data
// Choices are "DisplayRequestAndResponseJSONTxt", "DisplayRequestAndResponseArrayTxt" or ""
$arrExtraParams = array("DEBUG"=> "DisplayRequestAndResponseJSONTxt");

chdir(__DIR__);
require_once("displaysupport.php");
require_once("../imsapiclient.php");

try{
	
	$curDir = getcwd();

	$arrOpts = getopt("u:p:k:f:h:");

	$arrColumns = array("username" => null, "description" => null, "firstname" => null, "surname" => null, "email" => null, "emailaddress" => null, "group" => null, "preset" => null);

	// Validate Inputs
	if(empty($arrOpts["h"]))
	{
		echo "Specify IMS host using -h \n";
		exit(1);
	}

	if(empty($arrOpts["k"]) && (empty($arrOpts["u"]) || empty($arrOpts["p"])))
	{
		echo "Specify username/password with -u -p\n";
		echo "Alternatively provide API key with -k\n";
		exit(1);
	}

	if(empty($arrOpts["f"]))
	{
		echo "Specify filename of CSV with -f\n";
		exit(2);
	}

	if(!file_exists($arrOpts["f"]))
	{
		echo "Specified CSV not found\n";
		exit(3);
	}

	$rFile = @fopen($arrOpts["f"], "r");
	if(!$rFile)
	{
		echo "Failed to open file\n";
		exit(3);
	}

	$arrHeader = fgetcsv($rFile);

	if(!$arrHeader)
	{
		echo "File does not parse as CSV\n";
		exit(4);
	}

	foreach($arrHeader as $nIndex => $strValue)
	{
		$strSafeVal = mb_strtolower(preg_replace("/[^a-zA-Z0-9]/", "", $strValue));
		if(array_key_exists($strSafeVal, $arrColumns))
		{
			$arrColumns[$strSafeVal] = $nIndex;
		}
	}

	$nUserCol = $nEmailCol = $nGroupCol = $descCallback = null;

	if(!isset($arrColumns["username"]))
	{
		echo "No username column found\n";
		exit(5);
	}
	$nUserCol = $arrColumns["username"];

	if(!isset($arrColumns["email"]) && !isset($arrColumns["emailaddress"]))
	{
		echo "No e-mail address column found\n";
		exit(6);
	}
	elseif(isset($arrColumns["email"]))
	{
		$nEmailCol = $arrColumns["email"];
	}
	else
	{
		$nEmailCol = $arrColumns["emailaddress"];
	}

	if(isset($arrColumns["group"]))
	{
		$nGroupCol = $arrColumns["group"];
	}
	else
	{
		$nGroupCol = $arrColumns["preset"];
	}

	if(isseT($arrColumns["description"]))
	{
		$descCallback = function($arr) use ($arrColumns) {
			return $arr[$arrColumns["description"]];
		};
	}
	elseif(isset($arrColumns["firstname"]))
	{
		if(isset($arrColumns["surname"]))
		{
			$descCallback = function($arr) use ($arrColumns) {
				return $arr[$arrColumns["firstname"]]." ".$arr[$arrColumns["surname"]];
			};
		}
		else
		{
			$descCallback = function($arr) use ($arrColumns) {
				return $arr[$arrColumns["firstname"]];
			};
		}
	}
	elseif(isset($arrColumns["surname"]))
	{
		$descCallback = function($arr) use ($arrColumns) {
			return $arr[$arrColumns["surname"]];
		};
	}
	else
	{
		echo "No description column found\n";
		exit(7);
	}


	$arrUserInfo = array();

	while($arr = fgetcsv($rFile))
	{
		$thisUser = array(
			"username" => $arr[$nUserCol],
			"email" => $arr[$nEmailCol],
			"group" => "auto",
			"description" => "");
		if($nGroupCol && !empty($arr[$nGroupCol]))
		{
			$thisUser["group"] = mb_strtolower($arr[$nGroupCol]);
		}
		$thisUser["description"] = $descCallback($arr);

		$arrUserInfo[] = $thisUser;
	}

	fclose($rFile);


	$strApiKey = isset($arrOpts["k"]) ? $arrOpts["k"] : null;
	$objAPI = new IMSApiClient($arrOpts["h"], $strApiKey, $arrExtraParams);

	if(!$strApiKey)
	{
		$objAPI->Login(array("username"=>$arrOpts["u"], "password" => $arrOpts["p"]));
	}

	$arrPresetInfo = $objAPI->Users_GetAvailableUserPresets(array("includeGroups" => true));
	$arrPresetMappings = array();
	foreach($arrPresetInfo["presets"] as $thisPreset)
	{
		$arrPresetMappings[$thisPreset["id"]] = $thisPreset["id"];
	}
	foreach($arrPresetInfo["groups"] as $thisPreset)
	{
		$arrPresetMappings[mb_strtolower($thisPreset["description"])] = $thisPreset["id"];
	}

	foreach($arrUserInfo as $thisUser)
	{
		$thisUser["preset"] = "auto";
		if(array_key_exists($thisUser["group"], $arrPresetMappings))
		{
			$thisUser["preset"] = $arrPresetMappings[$thisUser["group"]];
		}
		unset($thisUser["group"]);
		$objAPI->Users_CreateUser($thisUser);
	}
}
catch(IMSApiClientException $e)
{
	echo "Error: ".get_class($e)."\n".$e->getMessage()."\n";
	if ($arrExtraParams["DEBUG"] =="") echo "Enable debug to see API request/response\n";
}