Import a List of Users - Update

Tutorials and guides contributions

Moderator: amosbatto

itslenny
Posts: 34
Joined: Wed May 07, 2014 10:40 am

Import a List of Users - Update

Unread postby itslenny » Fri May 16, 2014 11:31 am

Please can you update the Wiki for importing a list of users which has numerous error and incompatabilities, with the code below,

http://wiki.processmaker.com/index.php/ ... t_of_Users

New Code

Code: Select all

<?php
 ini_set("soap.wsdl_cache_enabled", "0");
 ini_set('error_reporting', E_ALL);
 ini_set('display_errors', True);

 #Generic function to lookup information in a ProcessMaker database
 function queryDB($varToFind, $query, $fieldToReturn, $errorMsg)
 {
    if (empty($varToFind))
       return '';

    $result = mysql_query($query) or
       die("Error: Unable to query database.\n\tQuery:\n\t$query\n");
    $record = mysql_fetch_array($result, MYSQL_ASSOC);

    if (!$record)
       die("Error: $errorMsg\n");

    return $record["$fieldToReturn"];
 }

 # Check if CSV file of users was passed as a parameter to this script
 if ($argc < 2)
    exit("Error: Must specify a CSV file of users to import.");

 # Open the CSV file as an array for each line
 $aUsers = file($argv[1]);
 if ($aUsers === false or !is_array($aUsers) or count($aUsers) < 2)
    exit("Error: file \"{$argv[1]}\" does not exist or has no content.");

#Remove the first element of the array, which are the column headers:

$aUsersInFields = array();

unset($aUsers[0]);
$aUsers = array_values($aUsers);

 #Loop through the array and for each user break the line into its individual fields.
 #Make sure that there are 17 fields of information for each user; otherwise stop and correct CSV file.
 for ($cnt = 0, $len = count($aUsers); $cnt < $len; $cnt++)
 {
   $aUsersInFields[$cnt]= explode(',', $aUsers[$cnt]);
   $fieldCnt = count($aUsersInFields[$cnt]);
   if ($fieldCnt != 17)
      exit("Error in line " . $cnt + 2 . ": Should be 17 fields, but $fieldCnt fields found." .
         "\nCheck:\n{$aUsers[$cnt]}");
 }


 #Login to web services
 $client = new SoapClient('http://localhost/sysworkflow/en/classic/services/wsdl2');
 $pass = 'md5:' . md5('');
 $params = array(array('userid'=>'admin', 'password'=>$pass));
 $result = $client->__SoapCall('login', $params);

 if ($result->status_code == 0)
    $sessionId = $result->message;
 else
     exit("Unable to connect to ProcessMaker.\nError Number: $result->status_code\n" .
         "Error Message: $result->message\n");

 #loop through array of users and create new users with web services
 foreach ($aUsersInFields as $user)
 {
    $userId    = trim($user[0]);
    $firstname = trim($user[1]);
    $lastname  = trim($user[2]);
    $email     = trim($user[3]);
    $role      = trim($user[4]);
    $password  = trim($user[5]);
    $params = array(array('sessionId'=>$sessionId, 'userId' => $userId,
       'firstname'=>$firstname, 'lastname'=>$lastname, 'email'=>$email,
       'role'=>$role, 'password'=>$password));
    $result = $client->__SoapCall('createUser', $params);

    if ($result->status_code == 0)
       print "$result->message\nUser UID: $result->userUID";
    else
       exit("Unable to create user \"$firstname $lastname\" ($userId).\n" .
          "Error Number: $result->status_code\nError Message: $result->message\n");
 }

 #connect to the wf_workflow database used by ProcessMaker
 $conn = mysql_connect('localhost:3306', 'root', '') or
    die("Error connecting to mysql.\n");
 mysql_select_db('wf_workflow');

 #After all the users are created, loop through and look up unique IDs.
 #Then use PHP's mysql functions to insert the values in the USERS table.
 foreach($aUsersInFields as $user)
 {
    $userId         = trim($user[0]);
    $dueDate        = trim($user[6]);
    $countryName    = trim($user[7]);
    $cityName       = trim($user[8]);  #USERS.USR_CITY field is really the region or state
    $locationName   = trim($user[9]);
    $address        = trim($user[10]);
    $phone          = trim($user[11]);
    $zipCode        = trim($user[12]); #postal code
    $departmentName = trim($user[13]);
    $position       = trim($user[14]);
    $reportsToName  = trim($user[15]);
    $replacedByName = trim($user[16]);

    #find the unique ID for the department
    $departmentId = queryDB($departmentName, "SELECT CON_ID FROM CONTENT WHERE
       CON_CATEGORY='DEPO_TITLE' AND CON_VALUE='$departmentName'", 'CON_ID',
       "Unable to find department '$departmentName' in the CONTENT table.");

    #find the UID for the $reportsToName user in the USERS table
    $reportsToId = queryDB($reportsToName,
       "SELECT USR_UID FROM USERS WHERE USR_USERNAME='$reportsToName'", 'USR_UID',
       "Unable to find the Reports_To user '$reportsToName' in the USERS table.");

    #find the UID for the $replacedByName user in the USERS table
    $replacedById = queryDB($replacedByName,
       "SELECT USR_UID FROM USERS WHERE USR_USERNAME='$replacedByName'", 'USR_UID',
       "Unable to find the Replaced_By user '$replacedByName' in the USERS table.");

    #find the ID for the $countryName in the ISO_COUNTRY table
    $countryId = queryDB($countryName,
       "SELECT IC_UID FROM ISO_COUNTRY WHERE IC_NAME='$countryName'", 'IC_UID',
       "Unable to find the country '$countryName' in the ISO_COUNTRY table.");

    #find the ID for the $cityName in the ISO_SUBDIVISION table
    $cityId = queryDB($cityName, "SELECT IS_UID FROM ISO_SUBDIVISION
       WHERE IC_UID='$countryId' AND IS_NAME='$cityName'", 'IS_UID',
       "Unable to find the city '$cityName' in the ISO_SUBDIVISION table.");

    #find the ID for the $locationName in the ISO_LOCATION table
    $locationId = queryDB($locationName, "SELECT IL_UID FROM ISO_LOCATION
       WHERE IC_UID='$countryId' AND IS_UID='$cityId' AND IL_NAME='$locationName'",
       'IL_UID', "Unable to find the location '$locationName' in the ISO_LOCATION table.");

    #write the data to the user's profile in ProcessMaker
    $query = "UPDATE USERS SET USR_DUE_DATE='$dueDate', USR_COUNTRY='$countryId', USR_CITY='$cityId',
       USR_LOCATION='$locationId', USR_ADDRESS='$address', USR_PHONE='$phone', USR_ZIP_CODE='$zipCode',
       DEP_UID='$departmentId', USR_POSITION='$position', USR_REPORTS_TO='$reportsToId',
       USR_REPLACED_BY='$replacedById' WHERE USR_USERNAME='$userId'";
    mysql_query($query) or die("Error: UPDATE failed:\n\t$query\n");
 }

 print "Inserted " . count($aUsersInFields) . " new users in the USERS table.\n";
 ?>

User avatar
liliana
Posts: 2420
Joined: Wed Jun 01, 2011 11:24 am

Re: Import a List of Users - Update

Unread postby liliana » Fri May 16, 2014 4:45 pm

Hi itslenny,

Thanks for the update, In which version of ProcessMaker have you tested the code below?
Liliana Iriarte
Technical Writer

itslenny
Posts: 34
Joined: Wed May 07, 2014 10:40 am

Re: Import a List of Users - Update

Unread postby itslenny » Fri May 16, 2014 8:12 pm

2.5.2

User avatar
liliana
Posts: 2420
Joined: Wed Jun 01, 2011 11:24 am

Re: Import a List of Users - Update

Unread postby liliana » Mon May 19, 2014 10:26 am

Thanks, the code in the wiki was tested on earlier versions, it is probably that it changed in some version prior to 2.5, I'll test it and make the corresponding changes in the wiki.

Thanks again
Liliana Iriarte
Technical Writer


Return to “Documentation”

Who is online

Users browsing this forum: No registered users and 2 guests