.
*/
if( isset($setup) ) {
#########################
### Create statistics ###
#########################
if( $setup['EventDateAppStart']!="" AND get_sql_date($setup['EventDateAppStart'])<=date("Y-m-d", strtotime("+2 days")) ) {
$out .= "\n\n=== Creating statistics ===\n";
$accepted = array();
$accepted['-'] = 0;
$accepted['yes'] = 0;
$accepted['pending'] = 0;
$accepted['confirmed'] = 0;
$accepted['arrived'] = 0;
$accepted['no'] = 0;
$accepted['cancel'] = 0;
$query = "SELECT `Accepted`, COUNT(`Accepted`) AS `amount` FROM `persons` GROUP BY `Accepted`";
if( @mysql_num_rows( $res=doquery($query) )>0 ) {
while( $row = mysql_fetch_array($res) ) {
$accepted[ $row['Accepted'] ] = $row['amount'];
}
}
if( ($accepted['-'] + $accepted['yes'] + $accepted['pending'] + $accepted['confirmed'] + $accepted['arrived'])>=50 ) {
// Require at least 50 applications to make statistics
$period = array();
$period[0]['name'] = "all";
$period[0]['query'] = "1";
$period[0]['sum'] = $accepted['-'] + $accepted['yes'] + $accepted['pending'] + $accepted['confirmed'] + $accepted['arrived'] + $accepted['no'] + $accepted['cancel'];
if( $accepted['yes'] + $accepted['pending'] + $accepted['confirmed'] + $accepted['arrived']>=50 AND $setup['ShowParticipantList'] ) {
$period[1]['name'] = "accepted";
$period[1]['query'] = "(`persons`.`Accepted`='yes' OR `persons`.`Accepted`='pending' OR `persons`.`Accepted`='confirmed')";
$period[1]['sum'] = $accepted['yes'] + $accepted['pending'] + $accepted['confirmed'];
if( $accepted['arrived']>=50 ) {
$period[2]['name'] = "arrived";
$period[2]['query'] = "(`persons`.`Accepted`='arrived')";
$period[2]['sum'] = $accepted['arrived'];
}
}
$sum = array_sum($accepted);
if( $setup['ShowParticipantList'] ) {
// Accepted status
$html = "| Status | Amount |
|---|
";
$k = array_keys($accepted);
for( $i=0; $i".$k[$i]." | ".$accepted[ $k[$i] ]." | ".number_format($accepted[ $k[$i] ]/array_sum($accepted)*100, 1)."% | ";
$html .= "
";
$chart = new Chart("Accepted status", $accepted, 10, $FULL_PATH."tmp/");
$img = $chart->pie(300);
$query = "INSERT INTO `statistics` (`selection`, `title`, `date`, `html`, `image`, `image_height`, `image_width`)
VALUES ('all', 'Accepted status', '".date("Y-m-d H:i:s")."', '".addslashes($html)."', '".addslashes($img)."', ".$chart->get_height("pie").", ".$chart->get_width("pie").")";
$query2 = "DELETE FROM `statistics` WHERE `selection`='all' AND `title`='Accepted status' AND `date`<'".date("Y-m-d H:i:s", strtotime("-1 minute"))."'";
if( doquery($query) AND doquery($query2) ) {
$out .= "Created 'accepted status'\n";
}else {
$out .= "Failed creating 'accepted status'\n";
}
}
/// All applications ///
// Applications per day
$query = "SELECT LEFT(`persons`.`Created`,10) AS `ApplicationDay`, COUNT( `persons`.`PersonID` ) AS `Number`
FROM `persons`
GROUP BY LEFT(`persons`.`Created`,10)
ORDER BY `ApplicationDay`";
$query2 = "SELECT MIN(LEFT(`persons`.`Created`,10)) AS `first`, MAX(LEFT(`persons`.`Created`,10)) AS `last` FROM `persons`";
if( @mysql_num_rows( $res=doquery($query) )>0 AND @mysql_num_rows( $res2=doquery($query2) )==1 ) {
$html_applperday = "| Date | Amount |
|---|
";
$html_appl = "| Date | Amount |
|---|
";
$data_applperday = array();
$data_appl = array();
$row2 = mysql_fetch_array($res2);
$weekend = array();
$deadline = array($setup['EventDateAppEnd']);
for( $i=strtotime($row2['first']); $i<=strtotime($row2['last']); $i+=24*3600 ) {
$data_applperday[ date("d.m.Y", $i) ] = 0;
$data_appl[ date("d.m.Y", $i) ] = 0;
if( date("w", $i)==0 OR date("w", $i)==6 ) $weekend[] = date("d.m.Y", $i);
}
$i = 0;
$count = 0;
while( $row = mysql_fetch_array($res) ) {
$i++;
$count += $row['Number'];
$data_applperday[ get_date($row['ApplicationDay']) ] = $row['Number'];
$data_appl[ get_date($row['ApplicationDay']) ] = $count;
$html_applperday .= "| ".get_date($row['ApplicationDay'])." | ".$row['Number']." | ".number_format($row['Number']/$sum*100, 1)."% |
";
$html_appl .= "| ".get_date($row['ApplicationDay'])." | ".$count." |
";
}
// Correct for days without applications
for( $i=strtotime($row2['first'])+24*3600; $i<=strtotime($row2['last']); $i+=24*3600 ) if( $data_appl[ date("d.m.Y", $i-24*3600) ]>$data_appl[ date("d.m.Y", $i) ] ) $data_appl[ date("d.m.Y", $i) ] = $data_appl[ date("d.m.Y", $i-24*3600) ];
$html_applperday .= "
";
$html_appl .= "
";
$chart = new Chart("Applications per day", $data_applperday, 10, $FULL_PATH."tmp/");
$img = $chart->bar(600, 500, -1, -1, -1, -1, $deadline, array(), $weekend, array());
$query = "INSERT INTO `statistics` (`selection`, `title`, `date`, `html`, `image`, `image_height`, `image_width`)
VALUES ('all', 'Applications per day', '".date("Y-m-d H:i:s")."', '".addslashes($html_applperday)."', '".addslashes($img)."', ".$chart->get_height("bar").", ".$chart->get_width("bar").")";
$query2 = "DELETE FROM `statistics` WHERE `selection`='all' AND `title`='Applications per day' AND `date`<'".date("Y-m-d H:i:s", strtotime("-1 minute"))."'";
if( doquery($query) AND doquery($query2) ) {
$out .= "Created 'applications per day'\n";
}else {
$out .= "Failed creating 'applications per day'\n";
}
$chart = new Chart("Applications", $data_appl, 10, $FULL_PATH."tmp/");
$img = $chart->line(600, 500, -1, -1, -1, -1, $deadline, array(), $weekend, array());
$query = "INSERT INTO `statistics` (`selection`, `title`, `date`, `html`, `image`, `image_height`, `image_width`)
VALUES ('all', 'Applications', '".date("Y-m-d H:i:s")."', '".addslashes($html_appl)."', '".addslashes($img)."', ".$chart->get_height("line").", ".$chart->get_width("line").")";
$query2 = "DELETE FROM `statistics` WHERE `selection`='all' AND `title`='Applications' AND `date`<'".date("Y-m-d H:i:s", strtotime("-1 minute"))."'";
if( doquery($query) AND doquery($query2) ) {
$out .= "Created 'applications'\n";
}else {
$out .= "Failed creating 'applications'\n";
}
}
// Quorum
for( $p=0; $p=get_sql_date($setup['EventDateStart'])?2:1) ) {
$query1 = "SELECT COUNT(`BodyCode`) AS `total` FROM `ab_bodies` WHERE `BodyStatus`='A'";
$query2 = "SELECT COUNT(`ab_bodies`.`BodyCode`) AS `count`
FROM `ab_bodies`, `persons`
WHERE `ab_bodies`.`BodyCode`=`persons`.`BodyCode` AND `ab_bodies`.`BodyStatus`='A' AND ".$period[$p]["query"]."
GROUP BY `ab_bodies`.`BodyCode`";
if( @mysql_num_rows( $res1=doquery($query1) )==1 AND @mysql_num_rows( $res2=doquery($query2) )>0 ) {
$row1 = mysql_fetch_array($res1);
$total = $row1['total'];
$present = mysql_num_rows($res2);
$html = "| Status | Amount |
|---|
";
$html .= "| Present | ".$present." | ".number_format($present/$total*100, 1)."% |
";
$html .= "| Not present | ".($total-$present)." | ".number_format(($total-$present)/$total*100, 1)."% |
";
$html .= "
";
$html .= "The quorum is the percentage of present bodies with voting rights (Antenna & AEGEE-WG). For a statutory meeting with decision power, this should be at least 50% + 1. Please note that the values shown here are calculated from the participants that applied. This chart only shows an estimate, the official number depends on the number of bodies that registers with AEGEE-Europe.
";
$data = array("Present" => $present, "Not present" => $total-$present);
$chart = new Chart("Quorum", $data, 10, $FULL_PATH."tmp/");
$img = $chart->pie(300);
$query = "INSERT INTO `statistics` (`selection`, `title`, `date`, `html`, `image`, `image_height`, `image_width`)
VALUES ('".$period[$p]['name']."', 'Quorum', '".date("Y-m-d H:i:s")."', '".addslashes($html)."', '".addslashes($img)."', ".$chart->get_height("pie").", ".$chart->get_width("pie").")";
$query2 = "DELETE FROM `statistics` WHERE `selection`='".$period[$p]['name']."' AND `title`='Quorum' AND `date`<'".date("Y-m-d H:i:s", strtotime("-1 minute"))."'";
if( doquery($query) AND doquery($query2) ) {
$out .= "Created 'quorum (".$period[$p]['name'].")'\n";
}else {
$out .= "Failed creating 'quorum (".$period[$p]['name'].")'\n";
}
}
}
// Rest goes in batch (limited amount of options)
for( $p=0; $p=get_sql_date($setup['EventDateStart'])?2:1) ) {
$stats = array();
// Meals
$stats['Meals'] = "SELECT `Meals` AS `key`, COUNT(`PersonID`) AS `amount` FROM `persons` WHERE ".$period[$p]["query"]." GROUP BY `key`";
##// Board confirmed
##$stats['Board confirmed'] = "SELECT `BoardConfirmed` AS `key`, COUNT(`PersonID`) AS `amount` FROM `persons` WHERE ".$period[$p]["query"]." GROUP BY `key`";
// Participant type
$stats['Participant type'] = "SELECT `ParticipantType` AS `key`, COUNT(`PersonID`) AS `amount` FROM `persons` WHERE ".$period[$p]["query"]." GROUP BY `key`";
// Sex
$stats['Gender'] = "SELECT `Sex` AS `key`, COUNT(`PersonID`) AS `amount` FROM `persons` WHERE ".$period[$p]["query"]." GROUP BY `key`";
##// Visa invitation needed
##$stats['Visa invitation needed'] = "SELECT `Visa` AS `key`, COUNT(`PersonID`) AS `amount` FROM `persons` WHERE ".$period[$p]["query"]." GROUP BY `key`";
// Type of member
$stats['Type of member'] = "SELECT `MemberType` AS `key`, COUNT(`PersonID`) AS `amount` FROM `persons` WHERE ".$period[$p]["query"]." GROUP BY `key`";
// Number of Agora visited
$stats['Number of Agora visited'] = "SELECT `AgoraVisited` AS `key`, COUNT(`PersonID`) AS `amount` FROM `persons` WHERE ".$period[$p]["query"]." GROUP BY `key`";
$stats_keys = array_keys($stats);
for( $s=0; $s0 ) {
$html = "| ".$stats_keys[$s]." | Amount |
|---|
";
$data = array();
while( $row = mysql_fetch_array($res) ) {
if( $stats_keys[$s]=="Number of Agora visited" AND $row['key']=="10" ) $row['key']="10+";
$html .= "| ".$row['key']." | ".$row['amount']." | ".number_format($row['amount']/$period[$p]['sum']*100, 1)."% |
";
$data[ $row['key'] ] = $row['amount'];
}
$html .= "
";
$chart = new Chart($stats_keys[$s], $data, 10, $FULL_PATH."tmp/");
$img = $chart->pie(300);
$query = "INSERT INTO `statistics` (`selection`, `title`, `date`, `html`, `image`, `image_height`, `image_width`)
VALUES ('".$period[$p]['name']."', '".$stats_keys[$s]."', '".date("Y-m-d H:i:s")."', '".addslashes($html)."', '".addslashes($img)."', ".$chart->get_height("pie").", ".$chart->get_width("pie").")";
$query2 = "DELETE FROM `statistics` WHERE `selection`='".$period[$p]['name']."' AND `title`='".$stats_keys[$s]."' AND `date`<'".date("Y-m-d H:i:s", strtotime("-1 minute"))."'";
if( doquery($query) AND doquery($query2) ) {
$out .= "Created '".$stats_keys[$s]." (".$period[$p]['name'].")'\n";
}else {
$out .= "Failed creating '".$stats_keys[$s]." (".$period[$p]['name'].")'\n";
}
}
}
}
// Batch (unlimited amount of options)
for( $p=0; $p=get_sql_date($setup['EventDateStart'])?2:1) ) {
$stats = array();
// Age
$stats['Year of birth'] = "SELECT LEFT(`DateBirth`, 4) AS `key`, COUNT(`PersonID`) AS `amount` FROM `persons` WHERE ".$period[$p]["query"]." GROUP BY `key` ORDER BY `amount` DESC";
// Body
$stats['Body'] = "SELECT `ab_bodies`.`BodyName` AS `key`, COUNT(`persons`.`PersonID`) AS `amount` FROM `persons`, `ab_bodies` WHERE `ab_bodies`.`BodyCode`=`persons`.`BodyCode` AND ".$period[$p]["query"]." GROUP BY `key` ORDER BY `amount` DESC";
// Country
$stats['Country'] = "SELECT `CountryCodes`.`Name` AS `key`, COUNT(`persons`.`PersonID`) AS `amount` FROM `persons`, `ab_bodies`, `CountryCodes` WHERE `CountryCodes`.`Postal`=`ab_bodies`.`CountryCode` AND `ab_bodies`.`BodyCode`=`persons`.`BodyCode` AND ".$period[$p]["query"]." GROUP BY `key` ORDER BY `amount` DESC";
$stats_keys = array_keys($stats);
for( $s=0; $s0 ) {
$html = "| ".$stats_keys[$s]." | Amount |
|---|
";
$data = array();
$other = 0;
while( $row = mysql_fetch_array($res) ) {
if( $stats_keys[$s]=="Country" ) $row['key'] = ucwords(strtolower($row['key']));
if( count($data)>10 ) {
$other += $row['amount'];
}else {
#$html .= "| ".$row['key']." | ".$row['amount']." | ".number_format($row['amount']/$period[$p]['sum']*100, 1)."% |
";
$data[ $row['key'] ] = $row['amount'];
}
}
ksort($data);
$data['(Other)'] = $other;
$k = array_keys($data);
for( $i=0; $i".$k[$i]." | ".$data[ $k[$i] ]." | ".number_format($data[ $k[$i] ]/$period[$p]['sum']*100, 1)."% | ";
$html .= "
";
if( $stats_keys[$s]=="Country" ) $html .= "Most European bodies (Commissions, Working Groups, Projects) are registered in Belgium. The country shown is the country of the body for which a person applied.
";
$chart = new Chart($stats_keys[$s], $data, 10, $FULL_PATH."tmp/");
$img = $chart->pie(300);
$query = "INSERT INTO `statistics` (`selection`, `title`, `date`, `html`, `image`, `image_height`, `image_width`)
VALUES ('".$period[$p]['name']."', '".$stats_keys[$s]."', '".date("Y-m-d H:i:s")."', '".addslashes($html)."', '".addslashes($img)."', ".$chart->get_height("pie").", ".$chart->get_width("pie").")";
$query2 = "DELETE FROM `statistics` WHERE `selection`='".$period[$p]['name']."' AND `title`='".$stats_keys[$s]."' AND `date`<'".date("Y-m-d H:i:s", strtotime("-1 minute"))."'";
if( doquery($query) AND doquery($query2) ) {
$out .= "Created '".$stats_keys[$s]." (".$period[$p]['name'].")'\n";
}else {
$out .= "Failed creating '".$stats_keys[$s]." (".$period[$p]['name'].")'\n";
}
}
}
}
}else {
$out .= "Too few applications to generate statistics.\n";
}
}
}
?>