Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderator: amosbatto

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
#824491
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
#824494
mohamad wrote: Fri May 17, 2019 12:37 pm 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.
#824526
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
#824540
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.
#824571
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
Attachments
personal information.png
personal information.png (16.55 KiB) Viewed 1634 times
#824573
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: "[email protected]@__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: "[email protected]@__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.
#824581
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: "[email protected]@__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: "[email protected]@__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
Attachments
error.png
error.png (5.18 KiB) Viewed 1624 times
(1.48 KiB) Downloaded 19 times
(3.94 KiB) Downloaded 20 times
#824593
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:
(4.12 KiB) Downloaded 21 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: "[email protected]@__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: "[email protected]@__ERROR__;
      }
   }
}

#824598
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
Attachments
second_form.png
second_form.png (18.05 KiB) Viewed 1598 times
#824618
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.
#824630
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;
#824632
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.
#824649
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: "[email protected]@__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: "[email protected]@__ERROR__;
      }
   }
}

Again, the files are attached with this post
(4.05 KiB) Downloaded 16 times
.
Would you please check it if you have time ?
Thanks in advance
Regards
#824650
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: "[email protected]@__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: "[email protected]@__ERROR__;
      }
   }
}
Also, the values that you posted don't include the email address. Are including that in your grid?
#824662
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,
#824665
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.
#824737
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
#824744
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);
    }
}

Genial!!! gracias. :D i need to translate an[…]

Are you sure that you only have one case open at[…]

Deleting the processes.

Are you sure that you are using version 1.245 f[…]

Changing the login page

In your pmos.conf, try changing from: Redir[…]