On 28 Aug 2003 04:56:54 -0700,
ja****@liv.ac.u k (J J) wrote:
I would like some help on the following problem please if anyone can
help me. I have two datasets as follows:
* Carers
* Clients
Each client needs X amount of hours to be looked after and each carer
can afford Y amount of hours to give their services. So each Client is
allocated a value X which indicates how long they need treatment for
and each carer is allocated a Y value to indicate how much time they
can afford.
I want to sort this out so that a group of clients are allocated to a
carer. Obviously, the total amount of X hours must be less than or
equal to the Y value of a give carer
E.G.
Client 01: 2 Hours |
Client 02: 3 Hours | Carer 01: 10 Hours
Client 03: 4 Hours |
Client 04: 1 Hours |
Client 05: 2 Hours |
Client 06: 5 Hours | Carer 02: 12 hours
Client 07: 4 Hours | (One hour to spare but next client is has
X=2)
Client 08: 2 Hours |
Client 09: 2 Hours |
Client 10: 3 Hours | Carer 03: 18 Hours
Client 11: 4 Hours |
Client 12: 5 Hours |
The clients and carers will be sorted alpahabetically so we'll group
as many of the clients (that are ordered alphabetiaclly) to the 1st
carer (alphabetically and repeat this so that when the 'Nth' carer is
full, then we'll go to 'N+1' carer and start allocating them clients
from where we left of.
Is this possible to do via a MySQL database which stores the data and
PHP scripts to manipulate the sorting? If so, can anyone help?
It's pushing it a bit to do it in MySQL's level of SQL, so do part of it in
PHP.
Fetch your Carers into an array, with their name and hours, so you end up with
something like:
$carers = array(
array(name => 'Carer 01', hours => 10, remaining => 10),
array(name => 'Carer 02', hours => 12, remaining => 12),
array(name => 'Carer 03', hours => 18, remaining => 18),
);
Fetch your clients, and loop through them, allocating time to the 'current'
carer, or moving to the next.
Something like (just using arrays rather than fetching from DB, but it ought
to give the idea):
<pre>
<?php
$carers = array(
array('name' => 'Carer 01', 'hours' => 10, 'remaining' => 10),
array('name' => 'Carer 02', 'hours' => 12, 'remaining' => 12),
array('name' => 'Carer 03', 'hours' => 18, 'remaining' => 18),
);
$clients = array(
array('name' => 'Client 01', 'hours' => 2),
array('name' => 'Client 02', 'hours' => 3),
array('name' => 'Client 03', 'hours' => 4),
array('name' => 'Client 04', 'hours' => 1),
array('name' => 'Client 05', 'hours' => 2),
array('name' => 'Client 06', 'hours' => 5),
array('name' => 'Client 07', 'hours' => 4),
array('name' => 'Client 08', 'hours' => 2),
array('name' => 'Client 09', 'hours' => 2),
array('name' => 'Client 10', 'hours' => 3),
array('name' => 'Client 11', 'hours' => 4),
array('name' => 'Client 12', 'hours' => 5),
);
$carer_num = 0;
for ($i=0; $i<count($clien ts); $i++) {
$row = $clients[$i];
// If current carer doesn't have enough hours left, move to the next
while ($carer_num < count($carers)
&& $row['hours'] > $carers[$carer_num]['remaining'])
$carer_num++;
// No more carers
if ($carer_num == count($carers)) {
break;
}
// Allocate time
$allocated_hour s = min($carers[$carer_num]['remaining'],
$row['hours']);
$carers[$carer_num]['clients'][] = array('client' => $row['name'],
'hours' => $allocated_hour s);
$carers[$carer_num]['remaining'] -= $allocated_hour s;
}
print_r($carers );
?>
</pre>
Outputs:
Array
(
[0] => Array
(
[name] => Carer 01
[hours] => 10
[remaining] => 0
[clients] => Array
(
[0] => Array
(
[client] => Client 01
[hours] => 2
)
[1] => Array
(
[client] => Client 02
[hours] => 3
)
[2] => Array
(
[client] => Client 03
[hours] => 4
)
[3] => Array
(
[client] => Client 04
[hours] => 1
)
)
)
[1] => Array
(
[name] => Carer 02
[hours] => 12
[remaining] => 1
[clients] => Array
(
[0] => Array
(
[client] => Client 05
[hours] => 2
)
[1] => Array
(
[client] => Client 06
[hours] => 5
)
[2] => Array
(
[client] => Client 07
[hours] => 4
)
)
)
[2] => Array
(
[name] => Carer 03
[hours] => 18
[remaining] => 2
[clients] => Array
(
[0] => Array
(
[client] => Client 08
[hours] => 2
)
[1] => Array
(
[client] => Client 09
[hours] => 2
)
[2] => Array
(
[client] => Client 10
[hours] => 3
)
[3] => Array
(
[client] => Client 11
[hours] => 4
)
[4] => Array
(
[client] => Client 12
[hours] => 5
)
)
)
)
--
Andy Hassall (an**@andyh.co. uk) icq(5747695) (
http://www.andyh.co.uk)
Space: disk usage analysis tool (
http://www.andyhsoftware.co.uk/space)
