methodological Questions for user levels

[onlyican]

Hi All.

 

I have a methodological question (not How to code it)

 

I have an internal application.

 

The application allows users to record Hours and Jobs

 

I want to allow certain users to view basic users hours / jobs

 

I have a user Structure

 

BOSS MAN

Area Manager

Team Leader

Basic User

 

So a basic user can view their OWN hours

 

A Team Leader can view  the users assigned to their own

 

A Area manager can view the users assigned to a Team Leader

 

A BOSS MAN can view all users assigned to an Area Manager (which is assigned to a Team Leader)

 

 

Best methods of DB query methods?

 

Excuse typos, FING touchpad moving my cursor every second.

 

[Jessica]

Rather than complaining about your touchpad, disable it.

 

Read up on ACL.

[onlyican]

A: Without touchpad, then no mouse as wireless mouse broke and i am away so using laptop not desktop

B:ACL is what I am building from scratch, this is a custom script.

 

options:

 

A: MESSY!!!! Have a link table for each  user2user (positive, allows team leaders / boss to view users assigned to someone NOT below them without seeing them all) NEGATIVE, Multiple Rows for user. so user X to TM, User X to Area manager, user X to BOSS)

 

B: Load concern One single look up table. Users assigned to Team, Then if logged in as area manager, read all TMs and get users assigbned to TEAM user, so and managers (THE HEAVY BIT), load all Area Managers, to load ALL team leaders to then load ALL users;

C: YOUR OPTIONS

[Barand]

Id create a table of staff, each one storing the id of their boss

$db = new mysqli(HOST, USERNAME, PASSWORD, 'test');

/*   data ***************************************************/
$sql = "CREATE TABLE employee (
empid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
empname varchar(50),
managerid INT
)";

$db->query($sql);

$sql = "INSERT INTO employee (empid,empname,managerid) VALUES
(1, 'Boss', 0),
(2, 'Area Mgr 1',  1),
(3, 'Area Mgr 2',  1),
(4, 'Team Ldr 1A', 2),
(5, 'Team Ldr 1B', 2),
(6, 'Team Ldr 2A', 3),
(7, 'Team Ldr 2B', 3),
(8, 'Emp 1A1',     4),
(9, 'Emp 1A2',     4),
(10, 'Emp 1B1',    5),
(11, 'Emp 1B2',    5),
(12, 'Emp 2A1',    6),
(13, 'Emp 2A2',    6),
(14, 'Emp 2B1',    7),
(15, 'Emp 2B2',     7)";

$db->query($sql);

You can then do recursive search to find all employees in their part of the hierarchy

$userid = 3;                                // current user
$subords = array($userid);
findSubordinates($userid, $subords, $db);   // search for rubordinates

//
// recursive search
//
function findSubordinates ($id, &$canview, $db)
{
    $sql = "SELECT empid
            FROM employee
            WHERE managerid = $id";
    $res = $db->query($sql);
    while (list($emp) = $res->fetch_row()) {
        $canview[] = $emp;
        findSubordinates($emp, $canview, $db);
    }
}

Thenyou can retrieve the data for all employees in the list

$emplist = join(',', $subords);

$sql = "SELECT emp.empid
    , emp.empname
    , timesheet.date_workd
    , timesheet.hrs
    FROM employee as emp 
    INNER JOIN timesheet ON emp.empid = timesheet.empid
    WHERE emp.empid IN ($emplist)
    ORDER BY emp.empid, timesheet.date_workd";
    
$res = $db->query($sql);
echo '<pre>';
while ($row = $res->fetch_row()) {
    vprintf("%2d %-15s %-12s %3d\n", $row);
}
echo '</pre>';

Giving, for Area Mgr 2 :

 3 Area Mgr 2      2013-05-24     3
 3 Area Mgr 2      2013-05-25     7
 6 Team Ldr 2A     2013-05-24     3
 6 Team Ldr 2A     2013-05-25     7
 7 Team Ldr 2B     2013-05-24     3
 7 Team Ldr 2B     2013-05-25     7
12 Emp 2A1         2013-05-24     3
12 Emp 2A1         2013-05-25     7
13 Emp 2A2         2013-05-24     3
13 Emp 2A2         2013-05-25     7
14 Emp 2B1         2013-05-24     3
14 Emp 2B1         2013-05-25     7
15 Emp 2B2         2013-05-24     3
15 Emp 2B2         2013-05-25     7

Edited May 25, 2013 by ignace
Added missing /