stevewithington/muraImportUsersViaCSV.cfm

## muraImportUsersViaCSV.cfm
<cfscript>
	param name='form.csvUrl' default='#getPageContext().getRequest().getScheme()#://#cgi.server_name##getDirectoryFromPath(getPageContext().getRequest().getRequestURI())#users.csv';
	param name='form.group' default='Temp';
	param name='form.isSubmitted' default='false';
	param name='form.isTest' default='true';
	param name='form.siteid' default='default';

	$ = application.serviceFactory.getBean('$').init(form.siteid);

	if ( !$.currentUser().isSuperUser() && !$.currentUser().isInGroup('admin') ) {
		WriteOutput('<h3>You should not be here.</h3>');
		abort;
	};

	errors = [];

	rsSites = $.getBean('settingsManager').getList();
	//rsGroups = $.getBean('userManager').getUserGroups(form.siteid,1);
	group = $.getBean('user').loadBy(groupname=form.group);
</cfscript>
<cfif form.isSubmitted and IsValid('url', form.csvUrl)>
	<cftry>
		<cfhttp name="rsUsers" method="get" url="#form.csvUrl#" />
		<cfcatch>
			<cfset ArrayAppend(errors, 'The .CSV file either does not exist at the specified URL, or the file itself is not a valid .CSV file.')>
		</cfcatch>
	</cftry>
	<cfif not ArrayLen(errors)>
		<cfset arrColumns = ListToArray(rsUsers.columnList)>
	</cfif>
</cfif>

<cfoutput>
<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="utf-8">
		<cfheader name="expires" value="#dateformat(now(), 'ddd, dd mmm yyyy')# #timeformat(now(), 'HH:mm:ss tt')#">
		<cfheader name="pragma" value="no-cache">
		<cfheader name="cache-control" value="no-cache, no-store">
		<meta http-equiv="pragma" content="no-cache">
		<meta http-equiv="Expires" content="#dateformat(now(), 'ddd, dd mmm yyyy')# #timeformat(now(), 'HH:mm:ss tt')#">
		<title>Import Users Into Mura CMS Via CSV</title>
		<style type="text/css">
			.wrap {
				clear:both;
				display:block;
				padding:1em;
				margin:1em;
				border:1px dashed grey;
				font-family:Arial, Helvetica, sans-serif;
				font-size:0.8em;
			}
			.wrap label, .wrap input {
				clear:both;
				display:block;
			}
			.wrap label {
				padding:1em 0 0 0;
			}
			.error {
				border: 2px solid red;
				padding: 1em;
				color: red;
			}
		</style>
	</head>
	<body>
		<div class="wrap">

			<h2>Import Users Into Mura CMS Via CSV</h2>

			<cfif form.isSubmitted and IsDefined('rsUsers')>

				<cfset tickStart = getTickCount()>

				<cfscript>
					if ( Len(form.group) && group.getIsNew() ) {
						WriteOutput('<h3>Group (#HTMLEditFormat(form.group)#) does not exist.</h3>');
					};
					invalidUsers = 0;
				</cfscript>

				<cfloop query="#rsUsers#">
					<cfscript>
						// loading by USERNAME. be sure to edit this to match your field names
						user = $.getBean('user').loadBy(username = rsUsers['Email'][currentrow]);

						// needed to set up Public user (Site Member)
						user.setValue('Type', 2); // 1=Group, 2=User
						user.setValue('isPublic', 1); // 0=Private/System, 1=Public/Member
						user.setValue('InActive', 0);

						// Make sure the following fields exist at a minimum: FName, LName, Email
						if ( !ArrayFindNoCase(arrColumns, 'FName') ) {
							ArrayAppend(errors, 'COLUMN: FName is required and was not found in the CSV file.');
						};
						if ( !ArrayFindNoCase(arrColumns, 'LName') ) {
							ArrayAppend(errors, 'COLUMN: LName is required and was not found in the CSV file.');
						};
						if ( !ArrayFindNoCase(arrColumns, 'Email') ) {
							ArrayAppend(errors, 'COLUMN: Email is required and was not found in the CSV file.');
						} else if ( !IsValid('email', rsUsers['Email'][currentRow]) ) {
							ArrayAppend(errors, 'Invalid email address: #rsUsers['Email'][currentRow]#. This user was not be saved.');
							invalidUsers++;
						};

						// PARSE .CSV COLUMNS
						if ( !ArrayLen(errors) ) {

							// If a Username field doesn't exist, use the Email address
							if ( !ArrayFindNoCase(arrColumns, 'Username') ) {
								user.setValue('username', rsUsers['Email'][currentrow]);
							};

							// If a columnName exists in Mura, it will be populated (e.g., any extended attributes)
							for ( columnName in arrColumns ) {
								if ( columnName != 'Groups' ) {
									user.setValue(columnName, rsUsers[columnName][currentRow]);
								};
							};
						};

						// POPULATE GROUPS
						if ( !ArrayLen(errors) ) {
							if ( ArrayFindNoCase(arrColumns, 'Groups') ) {
								arrGroups = ListToArray(rsUsers['Groups'][currentRow]);
								for ( groupName in arrGroups ) {
									group = $.getBean('user').loadBy(groupname=groupName);
									if ( !group.getIsNew() ) {
										user.setGroupID(groupid=group.getUserID(), append=true);
									};
								};
							} else if ( !group.getIsNew() ) {
								// use the Group field form the FORM
								user.setGroupID(groupid=group.getUserID(), append=true);
							};
						};

						// SAVE THE USER

						if ( !form.istest && !ArrayLen(errors) ) {
							user.save();
							if ( !StructIsEmpty(user.getErrors()) ) {
								ArrayAppend(errors, user.getErrors());
							};
							//WriteDump(user.getAllValues());
						};
					</cfscript>
				</cfloop>

				<cfif form.isTest>
					<h3>Test Results <a href="#CGI.script_name##CGI.query_string#">Return to form &gt;</a></h3>
					<cfdump var="#rsUsers#" label="rsUsers">
				<cfelse>
					<h3>Completed! <a href="#CGI.script_name##CGI.query_string#">Return to form &gt;</a></h3>
					<h4>#rsUsers.recordcount-invalidUsers# Users Imported</h4>
				</cfif>

				<cfset tickEnd = getTickCount()>
				<p><em>Processed in #tickEnd-tickStart# milliseconds</em></p>

			<cfelse>

				<form name="frmUser" method="post">
					<label for="csvurl">CSV URL:</label>
					<input type="text" name="csvurl" id="csvurl" value="#form.csvurl#" size="80" />

					<label for="siteid">Site ID:</label>
					<select name="siteid">
						<cfloop query="rsSites">
							<option value="#siteid#"<cfif form.siteid eq siteid> selected="selected"</cfif>>#HTMLEditFormat(site)#</option>
						</cfloop>
					</select>

					<label for="group">Group <small>(do NOT use if you have a 'Groups' field in your .CSV file)</small>:</label>
					<input type="text" name="group" id="group" value="#form.group#" size="80" />

					<label for="istest">Test?</label>
					<select name="istest">
						<option value="true"<cfif form.istest> selected="selected"</cfif>>Yes</option>
						<option value="false"<cfif !form.istest> selected="selected"</cfif>>No</option>
					</select>

					<input type="hidden" name="isSubmitted" value="true" />
					<p><input type="submit" value="Submit" /></p>
				</form>

			</cfif>

			<!--- ERROR OUTPUT --->
			<cfif ArrayLen(errors)>
				<div class="alert error">
					<h4>Error<cfif ArrayLen(errors) gt 1>s</cfif></h4>
					<ul>
						<cfloop array="#errors#" index="error">
							<li>
								<cfif IsSimpleValue(error)>
									#error#
								<cfelse>
									<cfdump var="#error#" />
								</cfif>
							</li>
						</cfloop>
					</ul>
				</div>
			</cfif>

		</div>
	</body>
</html>
</cfoutput>

## users.csv

          
            Email
            FName
            LName
            Age
            Groups

            
              john.doe@email.com
              John
              Doe
              42
              Temp,Test

            
              jane.doe@email.com
              Jane
              Doe
              38
              Temp

            
              steve.smith@email.com
              Steve
              Smith
              29
              Test
	<cfscript>
	param name='form.csvUrl' default='#getPageContext().getRequest().getScheme()#://#cgi.server_name##getDirectoryFromPath(getPageContext().getRequest().getRequestURI())#users.csv';
	param name='form.group' default='Temp';
	param name='form.isSubmitted' default='false';
	param name='form.isTest' default='true';
	param name='form.siteid' default='default';

	$ = application.serviceFactory.getBean('$').init(form.siteid);

	if ( !$.currentUser().isSuperUser() && !$.currentUser().isInGroup('admin') ) {
	WriteOutput('<h3>You should not be here.</h3>');
	abort;
	};

	errors = [];

	rsSites = $.getBean('settingsManager').getList();
	//rsGroups = $.getBean('userManager').getUserGroups(form.siteid,1);
	group = $.getBean('user').loadBy(groupname=form.group);
	</cfscript>
	<cfif form.isSubmitted and IsValid('url', form.csvUrl)>
	<cftry>
	<cfhttp name="rsUsers" method="get" url="#form.csvUrl#" />
	<cfcatch>
	<cfset ArrayAppend(errors, 'The .CSV file either does not exist at the specified URL, or the file itself is not a valid .CSV file.')>
	</cfcatch>
	</cftry>
	<cfif not ArrayLen(errors)>
	<cfset arrColumns = ListToArray(rsUsers.columnList)>
	</cfif>
	</cfif>

	<cfoutput>
	<!DOCTYPE html>
	<html lang="en">
	<head>
	<meta charset="utf-8">
	<cfheader name="expires" value="#dateformat(now(), 'ddd, dd mmm yyyy')# #timeformat(now(), 'HH:mm:ss tt')#">
	<cfheader name="pragma" value="no-cache">
	<cfheader name="cache-control" value="no-cache, no-store">
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="Expires" content="#dateformat(now(), 'ddd, dd mmm yyyy')# #timeformat(now(), 'HH:mm:ss tt')#">
	<title>Import Users Into Mura CMS Via CSV</title>
	<style type="text/css">
	.wrap {
	clear:both;
	display:block;
	padding:1em;
	margin:1em;
	border:1px dashed grey;
	font-family:Arial, Helvetica, sans-serif;
	font-size:0.8em;
	}
	.wrap label, .wrap input {
	clear:both;
	display:block;
	}
	.wrap label {
	padding:1em 0 0 0;
	}
	.error {
	border: 2px solid red;
	padding: 1em;
	color: red;
	}
	</style>
	</head>
	<body>
	<div class="wrap">

	<h2>Import Users Into Mura CMS Via CSV</h2>

	<cfif form.isSubmitted and IsDefined('rsUsers')>

	<cfset tickStart = getTickCount()>

	<cfscript>
	if ( Len(form.group) && group.getIsNew() ) {
	WriteOutput('<h3>Group (#HTMLEditFormat(form.group)#) does not exist.</h3>');
	};
	invalidUsers = 0;
	</cfscript>

	<cfloop query="#rsUsers#">
	<cfscript>
	// loading by USERNAME. be sure to edit this to match your field names
	user = $.getBean('user').loadBy(username = rsUsers['Email'][currentrow]);

	// needed to set up Public user (Site Member)
	user.setValue('Type', 2); // 1=Group, 2=User
	user.setValue('isPublic', 1); // 0=Private/System, 1=Public/Member
	user.setValue('InActive', 0);

	// Make sure the following fields exist at a minimum: FName, LName, Email
	if ( !ArrayFindNoCase(arrColumns, 'FName') ) {
	ArrayAppend(errors, 'COLUMN: FName is required and was not found in the CSV file.');
	};
	if ( !ArrayFindNoCase(arrColumns, 'LName') ) {
	ArrayAppend(errors, 'COLUMN: LName is required and was not found in the CSV file.');
	};
	if ( !ArrayFindNoCase(arrColumns, 'Email') ) {
	ArrayAppend(errors, 'COLUMN: Email is required and was not found in the CSV file.');
	} else if ( !IsValid('email', rsUsers['Email'][currentRow]) ) {
	ArrayAppend(errors, 'Invalid email address: #rsUsers['Email'][currentRow]#. This user was not be saved.');
	invalidUsers++;
	};

	// PARSE .CSV COLUMNS
	if ( !ArrayLen(errors) ) {

	// If a Username field doesn't exist, use the Email address
	if ( !ArrayFindNoCase(arrColumns, 'Username') ) {
	user.setValue('username', rsUsers['Email'][currentrow]);
	};

	// If a columnName exists in Mura, it will be populated (e.g., any extended attributes)
	for ( columnName in arrColumns ) {
	if ( columnName != 'Groups' ) {
	user.setValue(columnName, rsUsers[columnName][currentRow]);
	};
	};
	};

	// POPULATE GROUPS
	if ( !ArrayLen(errors) ) {
	if ( ArrayFindNoCase(arrColumns, 'Groups') ) {
	arrGroups = ListToArray(rsUsers['Groups'][currentRow]);
	for ( groupName in arrGroups ) {
	group = $.getBean('user').loadBy(groupname=groupName);
	if ( !group.getIsNew() ) {
	user.setGroupID(groupid=group.getUserID(), append=true);
	};
	};
	} else if ( !group.getIsNew() ) {
	// use the Group field form the FORM
	user.setGroupID(groupid=group.getUserID(), append=true);
	};
	};

	// SAVE THE USER

	if ( !form.istest && !ArrayLen(errors) ) {
	user.save();
	if ( !StructIsEmpty(user.getErrors()) ) {
	ArrayAppend(errors, user.getErrors());
	};
	//WriteDump(user.getAllValues());
	};
	</cfscript>
	</cfloop>

	<cfif form.isTest>
	<h3>Test Results <a href="#CGI.script_name##CGI.query_string#">Return to form ></a></h3>
	<cfdump var="#rsUsers#" label="rsUsers">
	<cfelse>
	<h3>Completed! <a href="#CGI.script_name##CGI.query_string#">Return to form ></a></h3>
	<h4>#rsUsers.recordcount-invalidUsers# Users Imported</h4>
	</cfif>

	<cfset tickEnd = getTickCount()>
	<p><em>Processed in #tickEnd-tickStart# milliseconds</em></p>

	<cfelse>

	<form name="frmUser" method="post">
	<label for="csvurl">CSV URL:</label>
	<input type="text" name="csvurl" id="csvurl" value="#form.csvurl#" size="80" />

	<label for="siteid">Site ID:</label>
	<select name="siteid">
	<cfloop query="rsSites">
	<option value="#siteid#"<cfif form.siteid eq siteid> selected="selected"</cfif>>#HTMLEditFormat(site)#</option>
	</cfloop>
	</select>

	<label for="group">Group <small>(do NOT use if you have a 'Groups' field in your .CSV file)</small>:</label>
	<input type="text" name="group" id="group" value="#form.group#" size="80" />

	<label for="istest">Test?</label>
	<select name="istest">
	<option value="true"<cfif form.istest> selected="selected"</cfif>>Yes</option>
	<option value="false"<cfif !form.istest> selected="selected"</cfif>>No</option>
	</select>

	<input type="hidden" name="isSubmitted" value="true" />
	<p><input type="submit" value="Submit" /></p>
	</form>

	</cfif>

	<!--- ERROR OUTPUT --->
	<cfif ArrayLen(errors)>
	<div class="alert error">
	<h4>Error<cfif ArrayLen(errors) gt 1>s</cfif></h4>
	<ul>
	<cfloop array="#errors#" index="error">
	<li>
	<cfif IsSimpleValue(error)>
	#error#
	<cfelse>
	<cfdump var="#error#" />
	</cfif>
	</li>
	</cfloop>
	</ul>
	</div>
	</cfif>

	</div>
	</body>
	</html>
	</cfoutput>
Email	FName	LName	Age	Groups
john.doe@email.com	John	Doe	42	Temp,Test
jane.doe@email.com	Jane	Doe	38	Temp
steve.smith@email.com	Steve	Smith	29	Test