=get_sql_date($setup['EventDateStart'])?2:1) ) {
$queryBuilder = new QueryBuilder($GLOBALS['DB'], "ab_bodies");
$queryBuilder->addField(new DBMethodCount("bodycode"), "total");
$queryBuilder->addWhereEquals("bodystatus", "A");
if( $GLOBALS['DB']->select($queryBuilder->toQuery()) == 1 ) {
$row = $GLOBALS['DB']->fetchAssoc();
$total = $row['total'];
$queryBuilder = new QueryBuilder($GLOBALS['DB'], "applications");
$queryBuilder->addField(new DBMethodCount("ab_bodies.bodycode"), "count");
$queryBuilder->addInnerJoin("ab_bodies", "bodycode", "applications.bodycode");
$queryBuilder->addWhereEquals("ab_bodies.bodystatus", "A");
if( $period[$p]['queryWhere'] != NULL ) {
$queryBuilder->addWhereIn("accepted", $period[$p]['queryWhere']);
}
$queryBuilder->addGroup("ab_bodies.bodycode");
if( $GLOBALS['DB']->select($queryBuilder->toQuery()) > 0 ) {
$present = $GLOBALS['DB']->numRows();
$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.
";
$json = array();
$json['generated'] = date("Y-m-d H:i:s");
$json['total'] = $total;
$json['data']['present'] = $present;
$json['data']['notPresent'] = $total - $present;
$data = array("Present" => $present, "Not present" => $total - $present);
$chart = new Chart("Quorum", $data, 10, FULL_PATH . "tmp/");
$data = array();
$data['selection'] = $period[$p]['name'];
$data['title'] = "Quorum";
$data['date'] = date("Y-m-d H:i:s");
$data['html'] = $html;
$data['json'] = json_encode($json);
$data['image'] = $chart->pie(300);
$data['image_height'] = $chart->get_height("pie");
$data['image_width'] = $chart->get_width("pie");
if( $GLOBALS['DB']->insert("statistics", $data) ) {
$this->addMessage("Created 'quorum (" . $period[$p]['name'] . ")'");
}else {
$this->addMessage("Failed creating 'quorum (" . $period[$p]['name'] . ")'");
}
}else {
$this->addMessage("Failed selecting present bodies for quorum");
}
}else {
$this->addMessage("Failed selecting total bodies for quorum");
}
}
}
private function createStatsLimitedOptions($period) {
global $setup;
global $Meals;
for( $p=0; $p < count($period); $p += (date("Y-m-d") >= get_sql_date($setup['EventDateStart']) ? 2 : 1) ) {
$stats = array();
$stats['Meals'] = $this->createQueryStats("meals", $period[$p]["queryWhere"]);
##$stats['Board confirmed'] = $this->createQueryStats("boardconfirmed", $period[$p]["queryWhere"]);
$stats['Participant type'] = $this->createQueryStats("participanttype", $period[$p]["queryWhere"]);
$stats['Gender'] = $this->createQueryStats("sex", $period[$p]["queryWhere"]);
##$stats['Visa invitation needed'] = $this->createQueryStats("visa", $period[$p]["queryWhere"]);
$stats['Type of member'] = $this->createQueryStats("membertype", $period[$p]["queryWhere"]);
$stats['Number of Agora visited'] = $this->createQueryStats("agoravisited", $period[$p]["queryWhere"]);
foreach( $stats as $name => $query ) {
if( $GLOBALS['DB']->select($query) > 0 ) {
$html = "| " . $name . " | Amount |
|---|
";
$data = array();
while( $row = $GLOBALS['DB']->fetchAssoc() ) {
if( $name == "Number of Agora visited" && $row['key'] == "10" ) $row['key'] = "10+";
if( $name == "Meals" ) $row['key'] = $Meals[ $row['key'] ];
$html .= "";
$html .= "| " . $row['key'] . " | ";
$html .= "" . $row['amount'] . " | ";
$html .= "" . number_format($row['amount'] / $period[$p]['sum'] * 100, 1) . "% | ";
$html .= "
";
$data[ $row['key'] ] = $row['amount'];
}
$html .= "
";
$json = array();
$json['generated'] = date("Y-m-d H:i:s");
$json['total'] = $period[$p]['sum'];
$json['data'] = $data;
$chart = new Chart($name, $data, 10, FULL_PATH . "tmp/");
$data = array();
$data['selection'] = $period[$p]['name'];
$data['title'] = $name;
$data['date'] = date("Y-m-d H:i:s");
$data['html'] = $html;
$data['json'] = json_encode($json);
$data['image'] = $chart->pie(300);
$data['image_height'] = $chart->get_height("pie");
$data['image_width'] = $chart->get_width("pie");
if( $GLOBALS['DB']->insert("statistics", $data) ) {
$this->addMessage("Created '" . $name . " (" . $period[$p]['name'] . ")'");
}else {
$this->addMessage("Failed creating '" . $name . " (" . $period[$p]['name'] . ")'");
}
}
}
}
}
private function createStatsUnlimitedOptions($period) {
global $setup;
for( $p=0; $p < count($period); $p += (date("Y-m-d") >= get_sql_date($setup['EventDateStart']) ? 2 : 1) ) {
$stats = array();
$stats['Year of birth'] = $this->createQueryStatsYearOfBirth($period[$p]["queryWhere"]);
$stats['Body'] = $this->createQueryStatsBody($period[$p]["queryWhere"]);
$stats['Country'] = $this->createQueryStatsCountry($period[$p]["queryWhere"]);
foreach( $stats as $name => $query ) {
if( $GLOBALS['DB']->select($query) > 0 ) {
$html = "| " . $name . " | Amount |
|---|
";
$data = array();
$jsonData = array();
$other = 0;
while( $row = $GLOBALS['DB']->fetchAssoc() ) {
if( $name == "Country" ) $row['key'] = ucwords(strtolower($row['key']));
if( count($data)>10 ) {
$other += $row['amount'];
}else {
$data[ $row['key'] ] = $row['amount'];
}
$jsonData[ $row['key'] ] = $row['amount'];
}
ksort($data);
ksort($jsonData);
$data['(Other)'] = $other;
$row = 0;
foreach( $data as $key => $value ) {
$html .= "";
$html .= "| " . $key . " | ";
$html .= "" . $value . " | ";
$html .= "" . number_format($value / $period[$p]['sum'] * 100, 1) . "% | ";
$html .= "
";
$row++;
}
$html .= "
";
if( $name == "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.
";
}
$json = array();
$json['generated'] = date("Y-m-d H:i:s");
$json['total'] = $period[$p]['sum'];
if( $name == "Body" || $name == "Country" ) {
$json['data'] = $jsonData;
}else {
$json['data'] = $data;
}
$chart = new Chart($name, $data, 10, FULL_PATH . "tmp/");
$data = array();
$data['selection'] = $period[$p]['name'];
$data['title'] = $name;
$data['date'] = date("Y-m-d H:i:s");
$data['html'] = $html;
$data['json'] = json_encode($json);
$data['image'] = $chart->pie(300);
$data['image_height'] = $chart->get_height("pie");
$data['image_width'] = $chart->get_width("pie");
if( $GLOBALS['DB']->insert("statistics", $data) ) {
$this->addMessage("Created '" . $name . " (" . $period[$p]['name'] . ")'");
}else {
$this->addMessage("Failed creating '" . $name . " (" . $period[$p]['name'] . ")'");
}
}
}
}
}
private function createQueryStats($field, $accepted) {
$queryBuilder = $this->createQueryStatsQueryBuilder($accepted);
$queryBuilder->addField($field, "key");
return $queryBuilder->toQuery();
}
private function createQueryStatsYearOfBirth($accepted) {
$queryBuilder = $this->createQueryStatsQueryBuilder($accepted);
$queryBuilder->addField(new DBMethodLeft("datebirth", 4), "key");
$queryBuilder->addOrderDesc("amount");
return $queryBuilder->toQuery();
}
private function createQueryStatsBody($accepted) {
$queryBuilder = $this->createQueryStatsQueryBuilder($accepted);
$queryBuilder->addField("ab_bodies.bodyname", "key");
$queryBuilder->addInnerJoin("ab_bodies", "bodycode", "applications.bodycode");
$queryBuilder->addOrderDesc("amount");
return $queryBuilder->toQuery();
}
private function createQueryStatsCountry($accepted) {
$queryBuilder = $this->createQueryStatsQueryBuilder($accepted);
$queryBuilder->addField("countrycodes.name", "key");
$queryBuilder->addInnerJoin("ab_bodies", "bodycode", "applications.bodycode");
$queryBuilder->addInnerJoin("countrycodes", "postal", "ab_bodies.countrycode");
$queryBuilder->addOrderDesc("amount");
return $queryBuilder->toQuery();
}
private function createQueryStatsQueryBuilder($accepted) {
$queryBuilder = new QueryBuilder($GLOBALS['DB'], "applications");
$queryBuilder->addField(new DBMethodCount("applications.id"), "amount");
if( $accepted != NULL ) {
$queryBuilder->addWhereIn("accepted", $accepted);
}
$queryBuilder->addGroup("key");
return $queryBuilder;
}
}
?>