[mohamad]

Dear Experts,
How to get personal information like Email, Expiration Date,status, replaced by from an external database (SQL server)?
Also, If it is possible, I want to get groups, departments and process group from the mentioned database.
As far as I know, we can connect to database from each process, and it is no global for entire ProcessMaker. I'm not sure about this. For example, when I connect to SQL server in Process A, I do not have this database in process B. Is it possible connect to external database for entire PM?
I would be very grateful if you could support me.
Looking forward to your comments
Kind Regards

[amosbatto]

If you want to import users from an external database table, then export the table as a CSV file. Then, you can use this script to import the users into ProcessMaker.

You can create similar scripts to import groups and departments into ProcessMaker.

[mohamad]

Thanks for your kind reply,
It sounds this is static solution. I mean, if users in external database would be edited or removed, Can PM understand this change or not? In every time, we have to export the table? or your solution is dynamic?
Kind Regards

[amosbatto]

As far as I know, we can connect to database from each process, and it is no global for entire ProcessMaker. I'm not sure about this. For example, when I connect to SQL server in Process A, I do not have this database in process B. Is it possible connect to external database for entire PM?

You have to create a new Database Connection for each process. There is no way to have a database connection that applies to all processes.

PS: You could create a script to copy the existing record for a database connection and duplicate if for each process in the DB_SOURCE table, but the work to do that would be much more than manually creating your database connections.

[mohamad]

Thank you very much for your consideration and time.

[mohamad]

Dear Amosbatto,
If I connect PM to another database(SQL) in one process, and I try to get some information like groups, departments and personal information, Can PM add these information to PM database? I mean, when I get a group or a department, the mentioned group and department will be appeared in Admin/users/groups or departments?
If the answer yes, how can I do? are there points in wiki or pm users websites?
Actually, If we can get all of departments, groups and personal information in one process, we can have all of them for all of processes.
Would you please help me with answering the question?
Kind Regards

[amosbatto]

My advice is that you add your users to LDAP or Active Directory. Then, you can import them that way. Advanced LDAP in the Enterprise Edition can import users, groups and departments from LDAP/Active Directory and keep the accounts up-to-date with synchronization.

Database Connections only work for 1 process, so you need to recreate the database connection in every process.

Once you use PMFCreateUser() in a trigger, the createUser() web service or POST /user REST endpoint to create a new user, that user is available in all processes. It is the same for creating groups and departments.

You can write an external script that use web services or REST to get the list of users/groups/departments from ProcessMaker, and check that list with the records in the database. Then, update the users/groups/departments in ProcessMaker if they have changed in the database. You can run your script periodically as a cron job in Linux or a Scheduled Task in Windows.

[mohamad]

Thanks a lot,
I want to get users in a grid from an external database. After that, I am going to use PMFCreateUser() to make users in PM, too
In this grid, I got first_name, last_name, username, email, expiration_date( this is date type in grid. I'm not sure about that) and password from external database according to the image below.
I used this query for first_name and last_name (both of them are suggest property):
SELECT DISTINCT first_name, first_name FROM pm_users
SELECT DISTINCT first_name,last_name FROM pm_users
for other ids of the gird (username, email, expiration_date, password), I want to be appeared them automatically. I tried to understand it, but I could not find
I used this query:
SELECT username FROM pm_users WHERE first_name = first_name AND last_name = last_name;
Actually, in dynaform, users have to enter first_name and last_name, after that other fields must be appeared on the basis of aforementioned query.
For example, if users enter Cristiano (first_name) and Ronaldo (last_name), the other fields of database should be emerged, automatically.
The variable of grid has been called personal_information, and its ids are the same label. How can I write this query? for example:
SELECT username FROM pm_users WHERE first_name = @@first_name AND last_name = @@last_name;

second,I was wondering if you could help me to write this trigger :
All of grid data will be added as users and personal information.
I know how to create just one user, but I do not know about all of grid data.
maybe, that would be like this, however I know that a loop must be written for that (foreach)

$var = PMFCreateUser(@@first_name, @@last_name, @@username,@@email, @@expiration_date, @@password);
if ($var == 0)
@@text = 'not created';
else
@@text = 'created';
Would you please help me to write this trigger?
Thanks in advance
Kind Regards

[amosbatto]

Your trigger code would be something like this:

Code: Select all

if (!empty(@=usersGrid)) {
   for ($i = 1; count(@=usersGrid); $i++) {
      //search in the database to see if the user already exists:
      $username= addslashes(@=usersGrid[$i]['username']);
      $sql = "SELECT * FROM USERS WHERE USR_USERNAME='$username'";
      $aResult = executeQuery($sql);
      
      //if the username already exists, then update the user's account:
      if (count($aResult) > 0) {
         $ret = PMFUpdateUser($aResult[1]['USR_UID'], $username, @=usersGrid[$i]['first_name'], 
            @=usersGrid[$i]['last_name'], @=usersGrid[$i]['email'], @=usersGrid[$i]['expiration_date'], 
            $aResult[1]['USR_STATUS'], $aResult[1]['USR_ROLE'], @=usersGrid[$i]['password']);
         
         @=usersGrid[$i]['result'] = $ret == 1 ? "Updated" : "Update Error: ".@@__ERROR__;
      }
      else { //create a new user account:
         $ret = PMFCreateUser(@=usersGrid[$i]['first_name'], @=usersGrid[$i]['last_name'], 
             $username, @=usersGrid[$i]['email'], @=usersGrid[$i]['expiration_date'], @=usersGrid[$i]['password']);
             
         @=usersGrid[$i]['result'] = $ret == 1 ? "Created" : "Create Error: ".@@__ERROR__;
      }
   }
}

Where @=usersGrid is the variable associated with the grid, which has the following IDs in its fields:
"first_name", "last_name", "username", "email", "expiration_date", "password".

Then create a copy of your Dynaform to display the result after the trigger and add a text box inside the grid with the ID "result" which will display "Updated", "Created" or an error message.

[mohamad]

Dear Amos, thank you
There are some errors in this solution. I explain whatever I did:
1) I got the information of my grid (@=database_name) from external database. (Everything is OK)
2) I wrote this trigger on the basis of your trigger

Code: Select all

if (!empty(@=database_name)) {
   for ($i = 1; count(@=database_name); $i++) {
      //search in the database to see if the user already exists:
      $username= addslashes(@=database_name[$i]['username']);
      $sql = "SELECT * FROM USERS WHERE USR_USERNAME='$username'";
      $aResult = executeQuery($sql);
      
      //if the username already exists, then update the user's account:
      if (count($aResult) > 0) {
         $ret = PMFUpdateUser($aResult[1]['USR_UID'], $username, @=database_name[$i]['first_name'], 
            @=database_name[$i]['last_name'], @=database_name[$i]['email'], @=database_name[$i]['expiration_date'], 
            $aResult[1]['USR_STATUS'], $aResult[1]['USR_ROLE'], @=database_name[$i]['password']);
         
         @=database_name[$i]['result'] = $ret == 1 ? "Updated" : "Update Error: ".@@__ERROR__;
      }
      else { //create a new user account:
         $ret = PMFCreateUser(@=database_name[$i]['first_name'], @=database_name[$i]['last_name'], 
             $username, @=database_name[$i]['email'], @=database_name[$i]['expiration_date'], @=udatabase_name[$i]['password']);
             
         @=database_name[$i]['result'] = $ret == 1 ? "Created" : "Create Error: ".@@__ERROR__;
      }
   }
}

3) I exported the Dynaform, then I imported it to new Dynaform, and added a textbox to grid(@=database_name). its name is result.
4) I assigned the trigger after the first Dynaform, and before the second Dyanform.
5) When I ran the process, after submitting the first Dyanform, noticeable time passed, and then this error message appeared.
Whoops, looks like something went wrong.
My sql table and the first Dyanform are attached with this post.
I would be appreciated if you support me with this .
Kind Regards

[amosbatto]

Your Dynaform won't work, because you are letting the user select records from any user, so you will mix records in the same row of your grid from different users. You should only have one suggest field that users can select from, and then make all the rest of the fields be dependent fields which are only viewable.

I have fixed your Dynaform:

sql_import-revised.json

(4.12 KiB) Downloaded 255 times

It is better to use a dropdown so users can only select from existing options, but if you want to use a select field, then you need to use this trigger code:

Code: Select all

if (!empty(@=database_name)) {
   for ($i = 1; count(@=database_name); $i++) {
      if (empty(@=database_name[$i]['username'])) {
          continue;
      }
      //search in the database to see if the user already exists:
      $username= addslashes(@=database_name[$i]['username']);
      $sql = "SELECT * FROM USERS WHERE USR_USERNAME='$username'";
      $aResult = executeQuery($sql);
      
      //if the username already exists, then update the user's account:
      if (count($aResult) > 0) {
         $ret = PMFUpdateUser($aResult[1]['USR_UID'], $username, @=database_name[$i]['first_name'], 
            @=database_name[$i]['last_name'], @=database_name[$i]['email'], @=database_name[$i]['expiration_date'], 
            $aResult[1]['USR_STATUS'], $aResult[1]['USR_ROLE'], @=database_name[$i]['password']);
         
         @=database_name[$i]['result'] = $ret == 1 ? "Updated" : "Update Error: ".@@__ERROR__;
      }
      else { //create a new user account:
         $ret = PMFCreateUser(@=database_name[$i]['first_name'], @=database_name[$i]['last_name'], 
             $username, @=database_name[$i]['email'], @=database_name[$i]['expiration_date'], @=udatabase_name[$i]['password']);
             
         @=database_name[$i]['result'] = $ret == 1 ? "Created" : "Create Error: ".@@__ERROR__;
      }
   }
}

[mohamad]

Thanks for your kind reply, Unfortunately, when user selects specified user from dropdown or suggest field, there are some errors and some irrelevant data would be appeared in the first dynaform, Or some information of them are not correct,
however when I added result to second dynaform (after trigger), the fields were written properly(according to the image below.), There are 2 problems
1) Users who are entered are not created in PM.
2) When trigger is running, the speed is really low! (for 4 users, it takes more than 2 minutes).
I would be very grateful, if you could help me
Kind Regards

[amosbatto]

You need to post the entire error message.
Run the case case in Debug Mode and check if there are any errors. If that doesn't give you enough information, then set debug=1 in your env.ini file.

When running the case, your logged-in user needs to be a user such as "admin" who has the PM_USERS permission in his role.

Mohammed, From your questions, it is obvious that you need to hire a programmer to help you.

[mohamad]

Thanks for your kind reply,
I can see just this error in Debug Mode:
Maximum execution time of 120 seconds exceeded
Also, when I set debug=1 in my env.ini file, nothing is appeared.
To solve my problem, I set max_execution_time =700 in my php.ini, and then restart Apache, but this error is appeared:
Maximum execution time of 700 seconds exceeded.
Anyway, if users are created in this time, this is not reasonable time.
Is there alternative way?
Thanks in advance;

[amosbatto]

I see that the order of parameters in PMFCreateUser() (which I copied from your original code) are wrong.

It should be:

Code: Select all

$ret = PMFCreateUser($username, @=udatabase_name[$i]['password'], @=database_name[$i]['first_name'],
    @=database_name[$i]['last_name'], @=database_name[$i]['email'], 'PROCESSMAKER_OPERATOR',  
    @=database_name[$i]['expiration_date'], 'ACTIVE');

This is the first thing that you should have checked. If it still doesn't work, then check each parameter's value.
You need to DEBUG the code.

[mohamad]

Thanks for your support, I set order of parameters according to wiki:

Code: Select all

int PMFCreateUser(string username, string password, string firstname, string lastname, string email,
   string role, string dueDate = null, string $status = null)

After that, I run process with Admin, but again I saw the same error.
Also, I checked each parameter's value. It sounds everything is OK.

Code: Select all

Array ( [1] => Array ( [enter_user] => A.FErgosen [enter_user_label] => Alex Alex (A.FErgosen) [first_name] => Alex [first_name_label] => Alex [last_name] => Fergosen [last_name_label] => Fergosen [username] => A.FErgosen [username_label] => A.FErgosen [expiration_date] => 2020-10-25 [expiration_date_label] => 2020-10-25 [password] => 12345 [password_label] => 12345 [result] => Create Error: 

I see this error: Fatal error in trigger (Maximum execution time of 200 seconds exceeded).
Another point that needs to be considered is that, I tested creating User for 1 user, and it was correct. (without grid)
Here is my trigger codes

Code: Select all

if (!empty(@=database_name)) {
   for ($i = 1; count(@=database_name); $i++) {
     if (empty(@=database_name[$i]['username'])) {
          continue;
     }
      //search in the database to see if the user already exists:
      $username= addslashes(@=database_name[$i]['username']);
      $sql = "SELECT * FROM USERS WHERE USR_USERNAME='$username'";
      $aResult = executeQuery($sql);
      
      //if the username already exists, then update the user's account:
      if (count($aResult) > 0) {
         $ret = PMFUpdateUser($aResult[1]['USR_UID'], $username, @=database_name[$i]['first_name'], 
            @=database_name[$i]['last_name'], @=database_name[$i]['email'], @=database_name[$i]['expiration_date'], 
            $aResult[1]['USR_STATUS'], $aResult[1]['USR_ROLE'], @=database_name[$i]['password']);
         
         @=database_name[$i]['result'] = $ret == 1 ? "Updated" : "Update Error: ".@@__ERROR__;
      }
      else { //create a new user account:
         $ret = PMFCreateUser($username, @=database_name[$i]['password'], @=database_name[$i]['first_name'],
    @=database_name[$i]['last_name'], @=database_name[$i]['email'], 'PROCESSMAKER_OPERATOR' 
   );
             
         @=database_name[$i]['result'] = $ret == 1 ? "Created" : "Create Error: ".@@__ERROR__;
      }
   }
}

Again, the files are attached with this post

database (4).json

(4.05 KiB) Downloaded 232 times

.
Would you please check it if you have time ?
Thanks in advance
Regards

[amosbatto]

Oh, the for loop isn't correct. It goes into an infinite loop.

Try it this way:

Code: Select all

if (!empty(@=database_name)) {
   for ($i = 1; $i <= count(@=database_name); $i++) {
     if (empty(@=database_name[$i]['username'])) {
          continue;
     }
      //search in the database to see if the user already exists:
      $username= addslashes(@=database_name[$i]['username']);
      $sql = "SELECT * FROM USERS WHERE USR_USERNAME='$username'";
      $aResult = executeQuery($sql);
      
      //if the username already exists, then update the user's account:
      if (count($aResult) > 0) {
         $ret = PMFUpdateUser($aResult[1]['USR_UID'], $username, @=database_name[$i]['first_name'], 
            @=database_name[$i]['last_name'], @=database_name[$i]['email'], @=database_name[$i]['expiration_date'], 
            $aResult[1]['USR_STATUS'], $aResult[1]['USR_ROLE'], @=database_name[$i]['password']);
         
         @=database_name[$i]['result'] = $ret == 1 ? "Updated" : "Update Error: ".@@__ERROR__;
      }
      else { //create a new user account:
         $ret = PMFCreateUser($username, @=database_name[$i]['password'], @=database_name[$i]['first_name'],
             @=database_name[$i]['last_name'], @=database_name[$i]['email'], 'PROCESSMAKER_OPERATOR',   
             @=database_name[$i]['expiration_date']);
             
         @=database_name[$i]['result'] = $ret == 1 ? "Created" : "Create Error: ".@@__ERROR__;
      }
   }
}

Also, the values that you posted don't include the email address. Are including that in your grid?

[mohamad]

Dear Amos, Thanks a lot.
Also, I searched the wiki to find some functions which can create group and department, but I could not find them. I am going to get groups and departments from external database, too. What is your suggestion functions?
Again, I'm deeply grateful for your excellent support, and words can not describe your kindness.
Best Regards,

[amosbatto]

You can use the createGroup() and createDepartment() web services.

If you want to use REST (which is more complicated), there are endpoints for that.
You can also use the Groupwf::create() and Department::create() functions, but you will have to read the source code to learn how to use them.

[mohamad]

Dear Amos,
I prefer to work with REST. I have an external database, and I have to get the groups from the external database. As you mentioned that, I have to use REST endpoints in order to create groups in PM.
Fortunately, I managed to do this activity, however there is a little problem. (I can get the group_title, but group_status is always ACTIVE in PM)
My trigger is attached:

Code: Select all

#obtain the current access token for the logged-in user
$userId = @@USER_LOGGED;
$query = "SELECT ACCESS_TOKEN FROM OAUTH_ACCESS_TOKENS WHERE USER_ID='$userId' ORDER BY EXPIRES DESC";
$result = executeQuery($query);
$accessToken = $result[1]['ACCESS_TOKEN'];

#first look up the UID of the database connection
$proc = @@PROCESS;
$result2 = executeQuery("SELECT DBS_UID FROM DB_SOURCE WHERE PRO_UID='$proc' " .
   "AND DBS_DATABASE_NAME='ecommerce' AND DBS_SERVER='localhost'");
   
   if (!is_array($result2) or count($result2) == 0)
   die("Error: Unable to look up UID for database 'ecommerce'!");
$db = $result2[1]['DBS_UID'];
$query2 = "SELECT group_title, group_status FROM pm_groups";

if (executeQuery($query2, $db) == 0)
   die("Unable to select record from pm_groups table");

//now call a REST endpoint using the access token
$apiServer = "http://localhost:67"; //set to your ProcessMaker address
$result3 = executeQuery($query2, $db);

	if (!empty($result3)) {
   for ($i = 1; $i <= count($result3); $i++) {
     if (empty($result3[$i]['group_title'])) {
          continue;
     }


	//create a new group:
	$postParams = array(
   'grp_title'   => $result3[$i]['group_title'],
   'grp_status'  => $result3[$i]['group_status']
);
	
   
   
$ch = curl_init($apiServer . "/api/1.0/workflow/group");
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Authorization: Bearer $accessToken"));
curl_setopt($ch, CURLOPT_HEADER, false);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postParams);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$oGroup = json_decode(curl_exec($ch));
	}
	}
if (!isset($oGroup)) {
   print "Error accessing $apiServer: \n" . curl_error($ch);
}
elseif (isset($oGroup->error)) {
   print "Error in $apiServer: \nCode: {$oGroup->error->code}\nMessage: {$oGroup->error->message}\n";
}
else {
   print "Group '{$oGroup->grp_title}' created with UID: {$oGroup->grp_uid}\n";
}
curl_close($ch);

I do not know why the groups are always ACTIVE in PM? ( in External database, some of groups are INACTIVE)
I was wondering if you could help me to solve that.
Kind Regards

[amosbatto]

Yes, this is a bug. It has been fixed in recent versions of PM, but you can patch it yourself.

Edit your workflow/engine/classes/model/Groupwf.php file and change lines 101-111 from:

Code: Select all

            if (!empty($aData['GRP_STATUS'])) {
                $this->setGrpStatus($data['GRP_STATUS']);
            } else {
                $this->setGrpStatus('ACTIVE');
            }

            if (!empty($aData['GRP_LDAP_DN'])) {
                $this->setGrpLdapDn($data['GRP_LDAP_DN']);
            } else {
                $this->setGrpLdapDn('');
            }

To:

Code: Select all

            if (!empty($data['GRP_STATUS'])) {
                $this->setGrpStatus($data['GRP_STATUS']);
            } else {
                $this->setGrpStatus('ACTIVE');
            }

            if (!empty($data['GRP_LDAP_DN'])) {
                $this->setGrpLdapDn($data['GRP_LDAP_DN']);
            } else {
                $this->setGrpLdapDn('');
            }

You will get an error with your trigger code if your table doesn't have any records or there is an error querying the table. Use this code instead:

Code: Select all

#obtain the current access token for the logged-in user
$userId = @@USER_LOGGED;
$query = "SELECT ACCESS_TOKEN FROM OAUTH_ACCESS_TOKENS WHERE USER_ID='$userId' ORDER BY EXPIRES DESC";
$result = executeQuery($query);
$accessToken = $result[1]['ACCESS_TOKEN'];

#first look up the UID of the database connection
$proc = @@PROCESS;
$result2 = executeQuery("SELECT DBS_UID FROM DB_SOURCE WHERE PRO_UID='$proc' " .
   "AND DBS_DATABASE_NAME='ecommerce' AND DBS_SERVER='localhost'");

if (!is_array($result2) or count($result2) == 0)
   die("Error: Unable to look up UID for database 'ecommerce'!");

$db = $result2[1]['DBS_UID'];
$query2 = "SELECT group_title, group_status FROM pm_groups";

if (executeQuery($query2, $db) == 0)
   die("Unable to select record from pm_groups table");

//now call a REST endpoint using the access token
$apiServer = "http://localhost:67"; //set to your ProcessMaker address
$result3 = executeQuery($query2, $db);
@@output = '';

if (!empty($result3)) {
    for ($i = 1; $i <= count($result3); $i++) {
        if (empty($result3[$i]['group_title'])) {
            continue;
        }

        //create a new group:
        $postParams = array(
            'grp_title'   => $result3[$i]['group_title'],
            'grp_status'  => $result3[$i]['group_status']
        );
       
        $ch = curl_init($apiServer . "/api/1.0/workflow/group");
        curl_setopt($ch, CURLOPT_HTTPHEADER, array("Authorization: Bearer $accessToken"));
        curl_setopt($ch, CURLOPT_TIMEOUT, 30);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $postParams);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        $oGroup = json_decode(curl_exec($ch));

        if (!isset($oGroup)) {
           throw new Exception("Error accessing $apiServer:\n" . curl_error($ch));
        }
        elseif (isset($oGroup->error)) {
           throw new Exception("Error in $apiServer: \nCode: {$oGroup->error->code}\nMessage: {$oGroup->error->message}");
        }
        else {
           @@output .= "Group '{$oGroup->grp_title}' created with UID: {$oGroup->grp_uid}\n";
        }
        
        curl_close($ch);
    }
}