#!/usr/bin/php
<?php

include("dbcon.inc");
//get a list of practices
$practices=mysql_query("SELECT id, max( denominator ) as listsize, SHA
FROM `surgery` , achievement, Targets
WHERE practiceid = id
AND achievement.area = Targets.areaid
AND TYPE =1
AND SHA NOT IN ('SCO', 'WAL', 'NIR')
GROUP BY id");

$national['Q9B_4']=120022;
$national['Q9B_5']=3941;
$national['Q9B_3']=66482;
$national['Q9B_1']=17611;

while ($practice=mysql_fetch_array($practices))
{
$practiceid=$practice['id'];
$results=mysql_query("select question,value from survey where question in ('q9b_1','q9b_3','q9b_4','q9b_5') and practice='$practiceid'");
//form an array of the results which we can mangle later
$survey=null;
while ($result=mysql_fetch_array($results))
{
$survey[$result['question']]=$result['value'];

}
//some practices did not take part. We will use the national data. Worth noting though.
if ($survey['Q9B_4']==null) {$survey=$national; $nat=1;} else $nat=0;

//we need to normalise the array. Basically make all of the result a ratio
$unhappy=array_sum($survey);
if ($unhappy==0) {$survey=$national; $nat=1; $unhappy=array_sum($survey);} //well what do you do if no-one is unhappy?

$want['Sat']=$survey['Q9B_4']/$unhappy;
$want['Sun']=$survey['Q9B_5']/$unhappy;
$want['Eve']=$survey['Q9B_3']/$unhappy;
$want['Mor']=$survey['Q3B_1']/$unhappy;
$minutesrequired=round(($practice['listsize']-1)/1000)*30+30;
$minutesestimate=(floor($minutesrequired/90)+1)*90;//a rough guess of how many minutes will be supplied
$minutessupplied=0;
$sessions['Mor']=0;$sessions['Eve']=0;$sessions['Sat']=0;$sessions['Sun']=0;//reset the sessions. We need to set them explicitly to make the SQL work
while($minutessupplied<$minutesrequired)
{
asort($want);
end($want);
$time=key($want);//so we have the area with the most want
if ($time=='Mor') $supply=60; else $supply=90;
$sessions[$time]++;//increment the appropriate session.
$minutessupplied+=$supply;
$want[$time]-=($supply/$minutesestimate); //deducting a proportion from the wants. This may end up negative
}

//echo $nat.','.$practiceid.','.$sessions['Sat'].','.$sessions['Sun'].','.$sessions['Eve'].','.$sessions['Mor']."\n";
$query="insert into extended values( '$practiceid' , ".$practice['listsize']." ,$nat, ".$sessions['Sat'].", ".$sessions['Sun'].",". $sessions['Eve'].",". $sessions['Mor'].")";
$err=mysql_query($query);
echo $query;

}
?>