<?php
  require('global-php.php');
  $verbindung = pg_connect("dbname=$database user=$database_user");
  if (!$verbindung) {
    print($lang_database_failure[$language]);
    exit;
  }
  $PHP_AUTH_USER = $_SERVER["PHP_AUTH_USER"];
  $PHP_AUTH_PW = $_SERVER["PHP_AUTH_PW"];
  $ergebnis = db_exec($verbindung,
			"SELECT id FROM staff_authorization "
  				. "WHERE "
				. login_query_clauses(
					$PHP_AUTH_USER,
					$PHP_AUTH_PW)
				. ";");
  if (pg_numrows($ergebnis) < 1) {
    header('WWW-Authenticate: Basic realm="Kursverwaltung"');
    header("HTTP/1.0 401 Unauthorized");
    echo $lang_authentication_failure[$language];
    exit;
  }
  $authorization = pg_result($ergebnis, 0, 0);
  $course_period = option_value($course_period_option);
  $ergebnis = db_exec($verbindung,
                      "SELECT count(*) FROM valid_registration "
			. "WHERE course_period=$course_period;");
  $persons = pg_result($ergebnis,0,0);
  $ergebnis = db_exec($verbindung,
                      "SELECT category.name"
		      	. "||', '"
			. "||course.name"
			. "||', '"
			. "||$course_date_readable.description,count(*) "
			. "FROM course,$course_date_readable,category,"
			. "registration_course,valid_registration,"
			. ($course_date_translated ? "language," : "")
			. "authorized_course "
			. "WHERE valid_registration.course_period="
			. "$course_period "
			. "AND registration_course.registration="
			. "valid_registration.registration "
			. "AND registration_course.course="
			. "$course_date_readable.id "
			. "AND $course_date_readable.course=course.id "
			. "AND category=category.id "
			. "AND $course_date_readable.id="
			. "authorized_course.course "
			. "AND authorized_course.account=$authorization "
			. ($course_date_translated
				? ("AND code='$language' "
					. "AND language=language.id ")
				: "")
			. "GROUP BY course.name,"
			. "$course_date_readable.description,position,"
			. "category.name "
			. "ORDER BY category.name,position;");
  $courses = array();
  for ($lt = 0; $lt < pg_numrows($ergebnis); $lt++) {
    array_push($courses,array(
		'name' => pg_result($ergebnis,$lt,0),
		'registrations' => pg_result($ergebnis,$lt,1)));
  }
  $ergebnis = db_exec($verbindung,
                      "SELECT category.name"
		      	. "||', '"
			. "||course.name"
			. "||', '"
			. "||$course_date_readable.description "
			. "FROM course,$course_date_readable,category,"
			. ($course_date_translated ? "language," : "")
			. "authorized_course "
			. "WHERE $course_date_readable.course=course.id "
			. "AND authorized_course.account=$authorization "
			. "AND category=category.id "
			. "AND $course_date_readable.id="
			. "authorized_course.course "
			. "AND course.course_period=$course_period "
			. "AND NOT EXISTS (SELECT true "
			. "FROM valid_registration,registration_course "
			. "WHERE registration_course.registration="
			. "valid_registration.registration "
			. "AND registration_course.course="
			. "$course_date_readable.id) "
			. ($course_date_translated
				? ("AND code='$language' "
					. "AND language=language.id ")
				: "")
			. "GROUP BY course.name,"
			. "$course_date_readable.description,position,"
			. "category.name "
			. "ORDER BY category.name,position;");
  $empty_courses = array();
  for ($lt = 0; $lt < pg_numrows($ergebnis); $lt++) {
    array_push($empty_courses,array(
		'name' => pg_result($ergebnis,$lt,0)));
  }
  require('local-criteria.php');
  $presented = array(
  	'persons' => $persons,
  	'courses' => $courses,
  	'empty_courses' => $empty_courses);
  load_template_overridable('statistics');
?>