1 employee for to report to 2 people

eayvl · Post by **eayvl** » Sun Nov 05, 2017 2:20 pm

Hi, is it possible for 1 employee to report to 2 people?

1.- ReportsTo
2.- Reports_2

Review this topic Advanced Security - Dynamic User Level Security, but I can't make it work.

Thnaks.

mobhar · Post by **mobhar** » Mon Nov 06, 2017 11:04 am

You may simply use "UserID_Loading" and/or "UserID_Loaded" server event for such case.

Please read "Server Events and Client Scritps" topic for more info and examples.

kirondedshem · Post by **kirondedshem** » Mon Nov 13, 2017 12:40 pm

I have never used employee_id functionality of phpmaker coz I was used to another approach from the start so if all fails you can give it a try.

I would create a table to hold my custom mapping say.
employee_managers
-id(primary key)
-manager_id(id for one employee foreign key).
-employees_managed [must be text field so it can hold comma separated values](comma separated values of employee ids) eg (1,2,3)

I would then be able to setup forms to add this setting employees_managed to checkbox controll to allow selecting of multiple employees.

RECOMMENDED HINT:You dont need to create another table for it, you can just add employees_managed column to your users table so that you can set it up as you enter a new user and still get the employee collection using CurrentUserInfo("employees_managed")

I would then use record_selecting of particular list pages of interest to filter out results basing on the user id collection.
function Recordset_Selecting(&$filter) {
//get the collection of employee ids
$employee_collection = CurrentUserInfo("employees_managed");
//IF used a table then query for it
$employee_collection = ew_ExecuteScalar("select employees_managed from employee_managers where manager_id = '".CurrentUserID()."'");
//filter table to show records owned by any of the employees
var_dump($employee_collection);
ew_AddFilter($filter, "employee_id in (".$employee_collection.")"); // Add your own filter expression
}

HINT:If you want fine tuned access eg if there are cases where you would want to track status of a particular employee being managed by a given employee, or be able to extend the funstionality of assigning an employee to some other things then you can use the table structure but only assign one employee at atime and repeat the process for each employee to be manged

eayvl · Post by **eayvl** » Tue Nov 14, 2017 2:27 pm

kirondedshem wrote:

I would create a table to hold my custom mapping say.
employee_managers
-id(primary key)
-manager_id(id for one employee foreign key).
-employees_managed [must be text field so it can hold comma separated values](comma
separated values of employee ids) eg (1,2,3)

Why the error:

bool(false)
Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') LIMIT 0, 10' at line 1

// Recordset Selecting event
function Recordset_Selecting(&$filter) {
// Enter your code here

//get the collection of employee ids
$employee_collection = CurrentUserInfo("employees_managed");
//IF used a table then query for it
$employee_collection = ew_ExecuteScalar("select employees_managed from employee_managers where manager_id = '".CurrentUserID()."'");
//filter table to show records owned by any of the employees
var_dump($employee_collection);
ew_AddFilter($filter, "employee_id in (".$employee_collection.")"); // Add your own filter expression
}

kirondedshem · Post by **kirondedshem** » Tue Nov 14, 2017 2:51 pm

You should not use both solutions, pick one.
ie either add a text field named employees_managed to your user table and set it checkbox
OR
create a separate table which impliments the same. NOT BOTH.

NOTE:for this to work the text field should be keeping comma separated values of the user or employee ids so link its autofill to the user accounts table setting value to your user id field, a user should be able to see a list of all availabel users and checks the users he wants to be managed

for example if you have just added the filed into your user table then this event should look like this:

function Recordset_Selecting(&$filter) {
// Enter your code here
//get the collection of employee ids
$employee_collection = CurrentUserInfo("employees_managed");
//filter table to show records owned by any of the employees
var_dump($employee_collection);
ew_AddFilter($filter, "employee_id in (".$employee_collection.")"); // Add your own filter expression
}

OR
If you have defined a table for implimenting this then this event should be like

function Recordset_Selecting(&$filter) {
// Enter your code here

//IF used a table then query for it
$employee_collection = ew_ExecuteScalar("select employees_managed from employee_managers where manager_id = '".CurrentUserID()."'");
//filter table to show records owned by any of the employees
var_dump($employee_collection);
ew_AddFilter($filter, "employee_id in (".$employee_collection.")"); // Add your own filter expression
}

NOTE:ensure the filed names in this example match your table structure ie if your user id field is called "id" then rename employee_id to user_id etc
So which one are you using

So which approach are you using?

eayvl · Post by **eayvl** » Wed Nov 15, 2017 9:00 am

kirondedshem wrote:
You should not use both solutions, pick one.

Very grateful with your help.

I'm using the demo project for to better explain what I'm doing; Add a new table "employee_managers" with 3 fields.

id(primary key) type INT
manager_id type INT
employees_managed) type VARCHAR

In the table "employee_managers" Server events, cliente scripts paste the following code:

// Recordset Selecting event
function Recordset_Selecting(&$filter) {
// Enter your code here

//IF used a table then query for it
$employee_collection = ew_ExecuteScalar("select employees_managed from employee_managers where manager_id = '".CurrentUserID()."'");
//filter table to show records owned by any of the employees
var_dump($employee_collection);
ew_AddFilter($filter, "employee_id in (".$employee_collection.")"); // Add your own filter expression
}

it is right?

kirondedshem · Post by **kirondedshem** » Wed Nov 15, 2017 2:22 pm

it is right?
No it is not.

INTRO:The actual implementation is like 5 lines of code. BUT I am going to put alot of explaining around it so you can understand the logic behind. So read carefully as it also including setting some advanced security stuff. I am going to use the demo project for this. So make a backup of it somewhere if you want to. Lets do this.

Let me explain how it is working now to the best of my knowledge?
Using phpmaker's default employee reports to functionality. you will notice the following:

all employees(user accounts) are in the employees table.
each employee has a unique ID ie EmployeeID and each employee has a ReportsTo indicating which other employee he reports to. For example. employee with id = 1(nancy) report to employee with ID = 2(Fuller andrew).
So All tables that have records tied to and Employee for example orders which has a filed for EmployeeID indicating which employee has entered a specific order.
When an employee logs in eg nancy with ID 1 and they go to orders table, they will be able to see all orders with EmployeeID = 1(orders they entered), as if employee Fuller with ID = 2 logs in, he will see all orders with EMployeeId = 2(orders he entered) as well as orders of all people who report to him eg also nancy's orders.
This is how its working. now.

Problem with this is its a 1 to 1 relationship ie you can only specify that an employee reports to a specific employee, so for cases of an employee reporting to more that one employee it is abit hard to impliment with this, which is your concern.Although others pointed out a roundabout way to achieving this. I still feel its better to implement it yourself in the way you have specified it.

Let me explain what we are trying to do.
Unlike the above format. We want to be able to assign a given employee to report to anyone even more that one person. SO we reversing the order and instead we start from the person they report to and them select a list of employees who report to him. After this we want to be able to filter all tables having emplyeeID with our employeeID collections in employees_managed field of logged in employee.

NOTE:So am going to take the solution of adding the emplyees_managed field to the employees table as I see it will be less confusing for you. So

Add a type text field called employees_managed to the employees table you can do so by running sql "ALTER TABLE employees ADD employees_managed text;", synchronize database in phpmaker.
2.Now we want to set this field to keep comma separated employee IDs, so go to filed settings of employees_managed and:
-set it to be checkbox
-set use lookup, table = employees, value = EmployeeID, display filed 1= Lastname, display filed 2 = FirstName
-Allow it on list page
Also disable check password strength on field settings of password filed such that it does not disturb you.Regenerate all files
NOW you should be able to edit any of these employees and be able to select multiple employees suing check boxes that you want to report to him/her.
**CHECKPOINT 1:Ensure You reach this checkpoint as its the backbone of what we want to implement.**
VERY IMPORTANT:Before we Implement our own filtering or records we need to
a. disable the default reports to functionality used by phpmaker.
-go to security->advanced security settings->click on UserID->Set the Parent User ID filed = disabled.
b. disable the default filtering of records according to logged in user used by phpmaker.
-go to security->advanced security settings->click on UserID->In the list of tables, set the UserID Field on orders table from EmployeeID to nothing(ie make it empty).
Regenerate all files
-NOW you will notice that when ANY user logs in they can see all orders irrespective or who entered them.
**CHECKPOINT 2:Ensure You reach this checkpoint as its also important.**

Now we can proceed to implement our own reports functionality as well as filtering records by logged in user.This is done for every table that need to be filtered but for now lets do it for the orders table.
LOGIC: what we want is when a user logs in we can show all orders he has entered as well as all orders of employees he manages.

Go to Recordset_Selecting event of the orders table and put the following.
// Recordset Selecting event
function Recordset_Selecting(&$filter) {

// Enter your code here
//get employeeID of logged in user

	$LoggedInEmployeeID = CurrentUserID();
	var_dump($LoggedInEmployeeID);

	//get comma separates lits of all employee's he manages
	$AllEmployeesManaged = CurrentUserInfo("employees_managed");
	var_dump($AllEmployeesManaged);
	$my_criteria = "";

	//incase this employee has no employees to manage
	if($AllEmployeesManaged == null)
	{
		$my_criteria = "(EmployeeID = '".$LoggedInEmployeeID."')";
	}

	//now if he has employees to manage
	else 
	{
		$my_criteria = "((EmployeeID = '".$LoggedInEmployeeID."') or (EmployeeID in (".$AllEmployeesManaged.")))";
	}

	//show only records he has entered or those employees he manages have entred 
	ew_AddFilter($filter, $my_criteria);
	var_dump($filter);

}

go in as ana dmin and set the employees managed for various employees
Now Depending on which employee is logged in he should be bale to see all orders entered by that employee as well as the employees they manages.
**CHECKPOINT 3:If you reach this checkpoint them it all okay.**

If its not working them tell me which checkpoints you have at least reached and ones you havent?

Now you just need to implement the same filtering for other tables that need it

eayvl · Post by **eayvl** » Thu Nov 16, 2017 9:22 am

kirondedshem wrote:
-go to security->advanced security settings->click on UserID->In the list of

tables, set the UserID Field on orders table from EmployeeID to nothing(ie make it
empty).

Everything is wonderful up to this point, clicking on the "ok" button, the following message appears: "The user ID field is deactivated".

I did the following:
-->go to security->advanced security settings->click in UserID->click in Parent User ID = disabled, Regenerate all files

I go to Recordset_Selecting event of the orders table and paste the code.

When I enter the page in "orders" shows the following error:
string(0) "" bool(false) string(17) "(EmployeeID = '')" string(0) "" bool(false) string(17) "(EmployeeID = '')"
Failed to execute SQL. Error: Unknown column 'employees_managed' in 'field list'

kirondedshem · Post by **kirondedshem** » Thu Nov 16, 2017 1:26 pm

clicking on the "ok" button, the following message appears: "The user ID field is deactivated".
when you go to USERID section fo advanced security you were supposed to
-set parent UserID field = disabled.
-set UserID field ON orders TABLE = nothing(make it empty), (THis is next to the orders table name in the list of tables you see when you click UserID section of advanced settings)
-YOU DID NOT HAVE TO TOUCH THE User ID field(the one at the very top before the parent UserID field) because that one manages the security of all suers logged in, leave it selected to EmployeeID as it was originally
SQL. Error: Unknown column 'employees_managed' in 'field list'
This means they cant find employees_managed field when you try to run CurrentUserInfo("employees_managed"); meaning You did not add the employees_managed filed to the employees table.
Indicate to me which of the checkpoints below you were able to reach successfully
-Does everything indicated before checkpoint 1 work as I explianed?
-Does everything indicated before checkpoint 2 work as I explianed?
-Does everything indicated before checkpoint 3 work as I explianed?

TO BE ON a SAFE SIDE, get a fresh setup of the demo project and restore its dtabase to its original format and go through this again, you can re download them if you have to.

eayvl · Post by **eayvl** » Fri Nov 17, 2017 8:34 am

Hi,

I followed all the steps correctly, added to the employees in the "emplyees_managed" field, example ( 5,6 )

only this error appears:
string(1) "1" string(3) "5,6" string(73) "(EmployeeID IN (1)) AND (((EmployeeID = '1') or (EmployeeID in (5,6))))"

Thank, thank.

kirondedshem · Post by **kirondedshem** » Fri Nov 17, 2017 1:41 pm

Question 1: Does the actuall filtering work as expected, have you tested and its exactly like it should be?
If so then let move to your "error"
only this error appears:
string(1) "1" string(3) "5,6" string(73) "(EmployeeID IN (1)) AND (((EmployeeID = '1') or (EmployeeID in (5,6))))"
It seems your php knowledge is lacking a bit
Thats not an error. One normally user var_dump("value") to print out some values in some time in time on the screen. I had added var_dumps in the record_Selecting events to show you what the values and filters look like so ou can better undertsnad what its doing.

To remove them just comment out OR remove all line starting with var_dump. for example the record selecting event can look something like this.

when I comment out the var_dumps the function will looks like

// Recordset Selecting event
function Recordset_Selecting(&$filter) {

// Enter your code here
//get employeeID of logged in user

$LoggedInEmployeeID = CurrentUserID();
//var_dump($LoggedInEmployeeID);

//get comma separates lits of all employee's he manages
$AllEmployeesManaged = CurrentUserInfo("employees_managed");
//var_dump($AllEmployeesManaged);
$my_criteria = "";

//incase this employee has no employees to manage
if($AllEmployeesManaged == null)
{
$my_criteria = "(EmployeeID = '".$LoggedInEmployeeID."')";
}

//now if he has employees to manage
else
{
$my_criteria = "((EmployeeID = '".$LoggedInEmployeeID."') or (EmployeeID in (".$AllEmployeesManaged.")))";
}

//show only records he has entered or those employees he manages have entred
ew_AddFilter($filter, $my_criteria);
//var_dump($filter);
}

OR when I remove all var_dumps, it looks like this.

// Recordset Selecting event
function Recordset_Selecting(&$filter) {

// Enter your code here
//get employeeID of logged in user

$LoggedInEmployeeID = CurrentUserID();

//get comma separates lits of all employee's he manages
$AllEmployeesManaged = CurrentUserInfo("employees_managed");
$my_criteria = "";

//incase this employee has no employees to manage
if($AllEmployeesManaged == null)
{
$my_criteria = "(EmployeeID = '".$LoggedInEmployeeID."')";
}

//now if he has employees to manage
else
{
$my_criteria = "((EmployeeID = '".$LoggedInEmployeeID."') or (EmployeeID in (".$AllEmployeesManaged.")))";
}

//show only records he has entered or those employees he manages have entred
ew_AddFilter($filter, $my_criteria);
}

Just use any of the above, again NOT BOTH.

HINT:I also advise you to learn some php, coz you will need it for where you are heading

eayvl · Post by **eayvl** » Sat Nov 18, 2017 1:00 pm

Does not show information of the associated employees, the ID 1 is associated with id (5,6) in employees_managed.

kirondedshem · Post by **kirondedshem** » Sat Nov 18, 2017 3:44 pm

If you remeber when you were seeing these lines
string(1) "1" string(3) "5,6" string(73) "(EmployeeID IN (1)) AND (((EmployeeID = '1') or (EmployeeID in (5,6))))"
This means you didt not disable the UserID field from automatically filtering records on the orders table in advanced settings.

Unless you can specify to me exaclty what checkpoints you were able to reach and which ones which of the desired fucntionalities as indicated on those checkpoints you were able to achieve I cant know what your doing wrong.

But basically these are the important parts. This cant even take 5 minutes even if am doing it from scratch. So you are clearly not following the instructions yet I feel I made them as detailed as possible.

adding employees_managed into employees table.
disabling automatic filtering of records using UserId field on orders table in advance settings.
adding code into record_selecting event to do the filtering.

So Try to figure out what you failed to set or what you did not undertand when going through it so we can start with that, otherwise I can help you directly If possible but since I cant post a video tutorial here for you,I can suggest you maybe get teamviewer or something like that

eayvl · Post by **eayvl** » Wed Jun 27, 2018 2:10 am

Excellent solution of kirondedshem,

I only have one question, when i login as admin i can not see any record, what changes would I have to make?

// Enter your code here
//get employeeID of logged in user

$LoggedInEmployeeID = CurrentUserID();
//var_dump($LoggedInEmployeeID);

//get comma separates lits of all employee's he manages
$AllEmployeesManaged = CurrentUserInfo("employees_managed");
//var_dump($AllEmployeesManaged);
$my_criteria = "";

//incase this employee has no employees to manage
if($AllEmployeesManaged == null)
{
		$my_criteria = "(EmployeeID = '".$LoggedInEmployeeID."')";
}

//now if he has employees to manage
else
{
		$my_criteria = "((EmployeeID = '".$LoggedInEmployeeID."') or (EmployeeID in (".$AllEmployeesManaged.")))";
}

//show only records he has entered or those employees he manages have entred
ew_AddFilter($filter, $my_criteria);
//var_dump($filter);

}

arbei · Post by **arbei** » Wed Jun 27, 2018 9:39 am

You do need to add the filter if the administrator is logged in.

Use isAdmin() as the condition to control add filter or not.

For example:
if (!IsAdmin()) {
// Add your filter.
}

mobhar · Post by **mobhar** » Thu Jun 28, 2018 10:20 am

Simply change this code:
//show only records he has entered or those employees he manages have entred
ew_AddFilter($filter, $my_criteria);

to:
if (!IsAdmin())
ew_AddFilter($filter, $my_criteria); //show only records he has entered or those employees he manages have entred

eayvl · Post by **eayvl** » Sat Oct 27, 2018 8:29 am

Hi, i have a need for that, i turn to the subject.

I like this code because the following employees id(5,8,10,11,12,14,17) can see the information of the employee id(20).

The code worked well; but I have this situation.

Example:
I have 20 employees, EMPLOYEE 1 can see the information of the employees id(4,7,9,11,13) But this same EMPLOYEE 1 can not see the information of the employees who report to him id(4,7,9,11,13),

// Recordset Selecting event
function Recordset_Selecting(&$filter) {

// Enter your code here
//get employeeID of logged in user

$LoggedInEmployeeID = CurrentUserID();
//var_dump($LoggedInEmployeeID);

//get comma separates lits of all employee's he manages
$AllEmployeesManaged = CurrentUserInfo("employees_managed");
//var_dump($AllEmployeesManaged);
$my_criteria = "";


//in case of that is the admin 
if (IsAdmin()) {
	// Add your filter.
}
else{
		//in case this employee has no employees to manage
		if($AllEmployeesManaged == null){
				$my_criteria = "(EmployeeID = '".$LoggedInEmployeeID."')";
		}

		//now if he has employees to manage
		else{
	
			$my_criteria = "((EmployeeID = '".$LoggedInEmployeeID."') or (EmployeeID in (".$AllEmployeesManaged.")))";

		}

		//show only records he has entered or those employees he manages have entred
		ew_AddFilter($filter, $my_criteria);

}

Thanks.