database into groups

[poe]

 

if I have a database of people in a company by name, department, level like so:

chris  |  accounting  |  manager

joe  |  accounting  |  manager

jimmy  |  accounting  |  manager

adam  |  accounting  |  manager

tina  |  accounting  |  manager

james  |  accounting  |  manager

mark  |  accounting  |  manager

mike  |  accounting  |  sr

jay  |  accounting  |  sr

ben  |  accounting  |  sr

chris  |  accounting  |  sr

dave  |  accounting  |  sr

jan  |  accounting  |  sr

kim  |  accounting  |  sr

sue  |  accounting  |  sr

bev  |  accounting  |  jr

frank  |  accounting  |  jr

nick  |  accounting  |  jr

tammy  |  accounting  |  jr

trevor  |  accounting  |  jr

 

I want to put these people into 'focus groups' of either 6 or 7,but keeping departments together

if there is an extra body left over I will bring in subs to fillthe seats

but I will need to know how many subs I willneed for each level

 

there are 3 different focus group combinations:

 

comboA = 2 manager, 3 sr, 2 jr = 7 people

comboB = 1 manager, 2 sr, 3 jr = 6 people

comboC = 2 manager, 2 sr, 3 jr = 7 people

 

 

based on my sample data below via excel, this is how I determined my grops to look like.:

 

1.comboA (2,3,2)

2.comboB (1,2,3)

3.comboC (2,2,3)

4.comboA (2,3,2)

5 -> no need. everyone has a spot

 

 

I will then loop through this again for the next department and so on…

 

now the question is how do I do this in mysql and php

 

 

chris

accounting

manager

a

[ignace]

SELECT count(*) FROM people GROUP BY department

[poe]

i am actually looking to assign individual people to the groups,

 

 

 

first i want to pick a random Focus Group Configuration

( in my case i start with comboA '2mgr, 3sr, 2jr' )

 

then pick out people from the overall group... by title according to spcaes available specified by ComboA

 

mgr - adam, tina

sr - jan, kim, ben

jr - bev, frank

 

now, check to see if there are any people left.

if so, randomly pick another focus group (i picked comboB '1mgr, 2sr, 3jr' )

 

mgr - jimmy

sr - chris dave

jr - nick tammy trevor

 

now, check if there are any people left

if so, randomly pick another focus group (i picked comboC '2mgr, 2sr, 3jr' )

 

mgr - adam, tina

sr - jan, kim, ben

jr - SUB, SUB, SUB

(there were no more JR left, but i still need to fill the seats so i used 3 SUBS)

 

now, check if there are any people left

if so, randomly pick another focus group (i picked comboA '2mgr, 3sr, 2jr' )

 

mgr - james, mark

sr - sue, SUB, SUB

jr - SUB, SUB

(there was only 1 sr available, so i also used 2SUBS to fill the other sr seats

also, there are still no JR people available, so i use 2 SUBS)

 

now, check if there are any people left

looks like there are no more people left.

END

 

 

so i guess ht e final array will look a bit like:

Array(
[0] => Array(
[manager] => Array(
[0] => chris
[1] => joe
)
[sr] => Array(
[0] => mike
[1] => jay
[2] => ben
)
[jr] => Array(
[0] => bev
[1] => frank
)
)


JUMP FORWARD TO MY THIRD GROUP....

[2] => Array(
[manager] => Array(
[0] => adam
[1] => tina
)
[sr] => Array(
[0] => jan
[1] => kim
[2] => ben
)
[jr] => Array(
[0] => SUB
[1] => SUB
[2] => SUB
)
)

...I HAVE RUN OUT OF JR, SO I MUST USE SUBS 

AND FINALLY THE 4th GROUP....       	

[3] => Array(
[manager] => Array(
[0] => james
[1] => mark
)
[sr] => Array(
[0] => sue
[1] => SUB
[2] => SUB
)
[jr] => Array(
[0] => SUB
[1] => SUB
)
)
...THERRE ARE STILL NO JR LEFT SO I SUBBED 2 MORE, PLUS I HAVE NOW RUN OUT OF SR SO I SUBBED 2 ALSO 

 

 

[Barand]

<?php
$staff = "chris  |  accounting  |   manager
joe  |  accounting  |   manager
jimmy  |  accounting  |   manager
adam  |  accounting  |   manager
tina  |  accounting  |   manager
james  |  accounting  |   manager
mark  |  accounting  |   manager
mike  |  accounting  |   sr
jay  |  accounting  |   sr
ben  |  accounting  |   sr
chris  |  accounting  |   sr
dave  |  accounting  |   sr
jan  |  accounting  |   sr
kim  |  accounting  |   sr
sue  |  accounting  |   sr
bev  |  accounting  |   jr
frank  |  accounting  |   jr
nick  |  accounting  |   jr
tammy  |  accounting  |   jr
trevor  |  accounting  |   jr";

$combos = array (
                array (2,3,2),
                array (1,2,3),
                array (2,2,3)
            );

$manager = $sr = $jr = $groups = array();
$subs = array(0,0,0);
$staff = str_replace(' ', '', $staff);
$arr = explode("\n", $staff) ;

foreach ($arr as $person)
{
    list($name, $dept, $rank) = explode('|', $person);
    switch (trim($rank)) {
       case 'manager':
         $manager[]=$name . '(mgr)';
         break;
       case 'sr':
         $sr[]=$name . '(sr)';
         break;
       case 'jr':
         $jr[]=$name . '(jr)';
         break;
    }
}

while (count($manager) + count($sr) + count($jr))
{
    $combo = $combos[array_rand($combos)];
    $k = count($manager);
    $m = array_splice($manager, 0, $combo[0]);
    if ($k < $combo[0]) 
    {
         $subs[0] += $combo[0] - $k; 
         $m[] = sprintf('MGR SUBS (%d)', $combo[0] - $k); 
    }
        
    $k = count($sr);
    $s = array_splice($sr, 0, $combo[1]);
    if ($k < $combo[1]) 
    {
        $subs[1] += $combo[1] - $k;
        $s[] = sprintf('SR SUBS (%d)', $combo[1] - $k);
    }
    
    $k = count($jr);
    $j = array_splice($jr, 0, $combo[2]);
    if ($k < $combo[2]) 
    {
         $subs[2] += $combo[2] - $k;
         $j[] = sprintf('JR SUBS (%d)', $combo[2] - $k);
    }
        
    $groups[] = array_merge($m, $s, $j);
}

echo '<pre> GROUPS ', print_r($groups, true), '</pre>';
echo '<pre> SUBS ', print_r($subs, true), '</pre>';
?>

gives (example) -->
GROUPS Array
(
    [0] => Array
        (
            [0] => chris(mgr)
            [1] => joe(mgr)
            [2] => mike(sr)
            [3] => jay(sr)
            [4] => ben(sr)
            [5] => bev(jr)
            [6] => frank(jr)
        )

    [1] => Array
        (
            [0] => jimmy(mgr)
            [1] => chris(sr)
            [2] => dave(sr)
            [3] => nick(jr)
            [4] => tammy(jr)
            [5] => trevor(jr)
        )

    [2] => Array
        (
            [0] => adam(mgr)
            [1] => tina(mgr)
            [2] => jan(sr)
            [3] => kim(sr)
            [4] => sue(sr)
            [5] => JR SUBS (2)
        )

    [3] => Array
        (
            [0] => james(mgr)
            [1] => mark(mgr)
            [2] => SR SUBS (2)
            [3] => JR SUBS (3)
        )

)

SUBS Array
(
    [0] => 0
    [1] => 2
    [2] => 5
)