science-ation/remote.php

<?

/*
 * This file is part of the 'Science Fair In A Box' project
 * SFIAB Website: http://www.sfiab.ca
 *
 * Copyright (C) 2005 Sci-Tech Ontario Inc <info@scitechontario.org>
 * Copyright (C) 2005 James Grant <james@lightbox.org>
 * Copyright (C) 2009 David Grant <dave@lightbox.org>
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public
 * License as published by the Free Software Foundation, version 2.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *  General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; see the file COPYING.  If not, write to
 * the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
 * Boston, MA 02111-1307, USA.
 */
?>
<?
require_once ('common.inc.php');
require_once ('user.inc.php');

require_once ('fair_additional_materials.inc.php');

function handle_getstats(&$u, $fair, &$data, &$response)
{
	global $pdo;

	$year = $data['getstats']['year'];

	/* Send back the stats we'd like to collect */
	$response['statconfig'] = explode(',', $fair['gather_stats']);

	/* Send back the stats we currently have */
	$q = $pdo->prepare("SELECT * FROM fairs_stats WHERE fairs_id=?
				AND year=?");
	$q->execute([$u['fairs_id'],$year]);
	$response['stats'] = $q->fetch(PDO::FETCH_ASSOC);
	unset($response['stats']['id']);
	$response['error'] = 0;
}

function handle_stats(&$u, $fair, &$data, &$response)
{
	global $pdo;

	$stats = $data['stats'];
	foreach ($stats as $k => $v) {
		$stats[$k] = $stats[$k];
	}

	//	$str = join(',',$stats);
	$keys = '`fairs_id`,`' . join('`,`', array_keys($stats)) . '`';
	$vals = "'{$u['fairs_id']}','" . join("','", array_values($stats)) . "'";
	$stmt = $pdo->prepare("DELETE FROM fairs_stats WHERE fairs_id=?
		AND year=?");
	$stmt->execute([$u['fairs_id'],$stats['year']]);
	show_pdo_errors_if_any($pdo);
	$stmt = $pdo->prepare("INSERT INTO fairs_stats (`id`,$keys) VALUES ('',?)");
	$stmt->execute([$vals]);
	show_pdo_errors_if_any($pdo);

	$response['message'] = 'Stats saved';
	$response['error'] = 0;
}

function handle_getawards(&$u, $fair, &$data, &$response)
{
	global $pdo;

	$awards = array();
	$year = $data['getawards']['year'];

	$ids = array();
	/* Load a list of awards linked to the fair id */
	$q = $pdo->prepare("SELECT * FROM fairs_awards_link WHERE fairs_id=?");
	$q->execute([$fair['id']]);
	while ($r = $q->fetch(PDO::FETCH_ASSOC)) {
		$aaid = $r['award_awards_id'];
		if ($r['download_award'] == 'yes')
			$ids[] = $aaid;
		$ul[$aaid] = $r['upload_winners'];
	}

	/* Load the awards this fair is allowed to download */
	$where = "(id='" . join("' OR id='", $ids) . "')";
	$q = $pdo->prepare("SELECT * FROM award_awards WHERE $where AND year=?");
	$q->execute([$year]);

	while ($a = $q->fetch(PDO::FETCH_ASSOC)) {
		$award = array();
		$award['identifier'] = $a['external_identifier'];
		$award['external_additional_materials'] = $a['external_additional_materials'];
		$award['external_register_winners'] = $a['external_register_winners'];
		$award['year'] = $a['year'];
		$award['name_en'] = $a['name'];
		$award['criteria_en'] = $a['criteria'];
		$award['upload_winners'] = $ul[$a['id']];
		$award['self_nominate'] = $a['self_nominate'];
		$award['schedule_judges'] = $a['schedule_judges'];

		if ($a['sponsors_id']) {
			$sq = $pdo->prepare("SELECT * FROM sponsors WHERE id=?");
			$sq->execute([$a['sponsors_id']]);
			if ($sq->rowCount()) {
				$s = $sq->fetch(PDO::FETCH_ASSOC);
				$award['sponsor'] = $s['organization'];
			}
		}

		$award['prizes'] = array();
		$pq = $pdo->prepare("SELECT * FROM award_prizes WHERE award_awards_id=?");
		$pq->execute([$a['id']]);
		while ($p = $pq->fetch(PDO::FETCH_ASSOC)) {
			/* Map array keys -> local database field */
			$map = array('cash' => 'cash', 'scholarship' => 'scholarship',
				'value' => 'value', 'prize_en' => 'prize', 'number' => 'number',
				'ord' => 'order',
				'trophystudentkeeper' => 'trophystudentkeeper',
				'trophystudentreturn' => 'trophystudentreturn',
				'trophyschoolkeeper' => 'trophyschoolkeeper',
				'trophyschoolreturn' => 'trophyschoolreturn');
			$prize = array('identifier' => '');
			foreach ($map as $k => $field)
				$prize[$k] = $p[$field];

			$award['prizes'][] = $prize;
		}
		$awards[] = $award;
	}
	$response['awards'] = $awards;
	$response['postback'] = 'http://localhost';
}

function award_upload_update_school(&$mysql_query, &$school, $school_id = -1)
{
	global $pdo;
	/* transport name => mysql name */
	$school_fields = array(  // 'schoolname'=>'school',
		'schoollang' => 'schoollang',
		'schoollevel' => 'schoollevel',
		'board' => 'board',
		'district' => 'district',
		'phone' => 'phone',
		'fax' => 'fax',
		'address' => 'address',
		'city' => 'city',
		'province_code' => 'province_code',
		'postalcode' => 'postalcode',
		'schoolemail' => 'schoolemail'
	);
	/*				'principal'=>'principal',
					'sciencehead'=>'sciencehead',
					'scienceheademail'=>'scienceheademail',
					'scienceheadphone'=>'scienceheadphone');*/

	if ($school_id == -1) {
		$our_school = $mysql_query->fetch(PDO::FETCH_ASSOC);
		$sid = $our_school['id'];
	} else {
		$sid = $school_id;
		$our_school = array();
	}
	$set = '';
	foreach ($school_fields as $t => $m) {
		if ($our_school[$m] == $school[$t])
			continue;
		if ($set != '')
			$set .= ',';
		$set .= "`$m`='" . $school[$t] . "'";
	}
	$stmt = $pdo->prepare("UPDATE schools SET ? WHERE id=?");
	$stmt->execute([$set,$sid]);
	return $sid;
}

function award_upload_school(&$student, &$school, $year, &$response)
{
	global $pdo;

	$school_name = $school['schoolname'];
	$school_city = $school['city'];
	$school_phone = $school['phone'];
	$school_addr = $school['address'];
	$student_city = $student['city'];

	/* Find school by matching name, city, phone, year */
	$q = $pdo->prepare("SELECT * FROM schools WHERE school=? AND city=? AND phone=? AND year=?");
	$q->execute([$school_name,$school_city,$school_phone,$year]);
	if ($q->rowCount() == 1)
		return award_upload_update_school($q, $school);

	/* Find school by matching name, city, address, year */
	$q = $pdo->prepare("SELECT * FROM schools WHERE school=? AND city=? AND address=? AND year=?");
	$q->execute([$school_name,$school_city,$school_addr,$year]);
	if ($q->rowCount() == 1)
		return award_upload_update_school($q, $school);

	/* Find school by matching name, city, year */
	$q = $pdo->prepare("SELECT * FROM schools WHERE school=? AND city=? AND year=?");
	$q->execute([$school_name,$school_city,$year]);
	if ($q->rowCount() == 1)
		return award_upload_update_school($q, $school);

	/* Find school by matching name, student city, year */
	$q = $pdo->prepare("SELECT * FROM schools WHERE school=? AND city=? AND year=?");
	$q->execute([$school_name,$student_city,$year]);
	if ($q->rowCount() == 1)
		return award_upload_update_school($q, $school);

	$response['notice'][] = "      - Creating new school: $school_name";
	/* No? ok, make a new school */
	$stmt = $pdo->prepare("INSERT INTO schools(`school`,`year`) VALUES (?,?)");
	$stmt->execute([$school['schoolname'], $year]);
	$school_id = $pdo->lastInsertId();
	return award_upload_update_school($q, $school, $school_id);
}

function award_upload_assign(&$fair, &$award, &$prize, &$project, $year, &$response)
{
	global $pdo;

	$reg_email_needs_update = false;
	$new_reg = false;

	/*
	 * Copied from admin/award_upload.php, this is the
	 *  transport name => sql name mapping
	 */
	$student_fields = array('firstname' => 'firstname',
		'lastname' => 'lastname',
		'email' => 'email',
		'grade' => 'grade',
		'language' => 'lang',
		'birthdate' => 'dateofbirth',
		'address' => 'address',
		'city' => 'city',
		'province' => 'province',
		'postalcode' => 'postalcode',
		'phone' => 'phone',
		'teachername' => 'teachername',
		'teacheremail' => 'teacheremail');

	/* See if this project already exists */
	$pn = $project['projectnumber'];
	$q = $pdo->prepare("SELECT * FROM projects WHERE projectnumber=? AND fairs_id=? AND year=?");
	$q->execute([$pn,$fair['id'],$year]);
	show_pdo_errors_if_any($pdo);
	if ($q->rowCount() == 1) {
		$our_project = $q->fetch(PDO::FETCH_ASSOC);
		$registrations_id = $our_project['registrations_id'];
		$pid = $our_project['id'];
		$response['notice'][] = "   - Found existing project: {$project['title']}";
	} else {
		$response['notice'][] = "   - Creating new project: {$project['title']}";
		/* Create a registration */
		$regnum = 0;
		// now create the new registration record, and assign a random/unique registration number to then.
		do {
			// random number between
			// 100000 and 999999  (six digit integer)
			$regnum = rand(100000, 999999);
			$q = $pdo->prepare("SELECT * FROM registrations WHERE num=? AND year=?");
			$q->execute([$regnum,$year]);
			show_pdo_errors_if_any($pdo);
		} while ($q->rowCount() > 0);

		// actually insert it
		$stmt = $pdo->prepare('INSERT INTO registrations (num,email,start,status,schools_id,year) VALUES (
			?,?,NOW(),open,NULL,?)');
		$stmt->execute([$regnum,$regnum,$year]);
		$registrations_id = $pdo->lastInsertId();
		/* We'll fill in the email address later */

		/* Add the project */
		$stmt = $pdo->prepare("INSERT INTO projects (`registrations_id`,`projectnumber`,`year`,`fairs_id`)
				VALUES(?,
					?,
					?,?);");
		$stmt->execute([$registrations_id,$project['projectnumber'],$year,$fair['id']]);
		$pid = $pdo->lastInsertId();
		$reg_email_needs_update = true;
		$new_reg = true;
	}
	$q = $pdo->prepare("SELECT * FROM registrations WHERE id=?");
	$q->execute([$registrations_id]);
	$registration = $q->fetch(PDO::FETCH_ASSOC);

	/* Update the project in case anythign changed */
	$stmt = $pdo->prepare("UPDATE projects SET title=?,
					summary='" . $project['abstract'] . "',
					projectcategories_id=?,
					projectdivisions_id=?
				WHERE id=?");
	$stmt->execute([$project['title'],intval($project['projectcategories_id']),
	intval($project['projectdivisions_id']),$pid]);

	/* Record the winner */
	$stmt = $pdo->prepare("INSERT INTO winners(`awards_prizes_id`,`projects_id`,`year`,`fairs_id`)
			VALUES(?,?,?,?)");
	$stmt->execute([$prize['id'],$pid,$year,$fair['id']]);
	/* Delete the students attached to this project */
	$stmt = $pdo->prepare("DELETE FROM students WHERE registrations_id=?");
	$stmt->execute([$registrations_id]);

	/* Add new */
	foreach ($project['students'] as &$student) {
		$response['notice'][] = "      - Student {$student['firstname']} {$student['lastname']} saved";

		$schools_id = award_upload_school($student, $student['school'], $year, $response);

		$keys = ',`' . join('`,`', array_values($student_fields)) . '`';
		$values = '';
		foreach ($student_fields as $k => $v)
			$values .= ",'" . $student[$k] . "'";
		/* Note lack of comma before $keys, we added it above for both keys and values */
		$stmt = $pdo->prepare("INSERT INTO students (`registrations_id`,`fairs_id`, `schools_id`,`year` ?)
				VALUES(?,?,?,? ? )");
		$stmt->execute([$keys,$registrations_id,$fair['id'],$schools_id,$year,$values]);

		/* Update the registration email */
		if ($reg_email_needs_update) {
			$stmt = $pdo->prepare("UPDATE registrations SET email=?
					WHERE id=?");
			$stmt->execute([$student['email'],$registrations_id]);
			$reg_email_needs_update = false;
		}

		if ($award['external_register_winners'] == 1 && $new_reg == true) {
			/*
			 * This award is for students who are participating in this fair, we need
			 * to get their reg number to them if this is a new registration
			 */
			email_send('new_participant', $student['email'], array(),
				array('EMAIL' => $student['email'],
					'REGNUM' => $registration['num']));
			$response['notice'][] = "         - Sent welcome registration email to: {$student['firstname']} {$student['lastname']} &lt;{$student['email']}&gt;";
		}
	}
	if ($award['external_register_winners'] == 0) {
		/*
		 * It's not an external, so we don't need the student to login
		 * or antyhing, we probably want to include it in reports, so set
		 * it to complete
		 */
		$stmt = $pdo->prepare("UPDATE registrations SET status='complete' WHERE id=?");
		$stmt->execute([$registrations_id]);
	}
}

function handle_award_upload(&$u, &$fair, &$data, &$response)
{
	$response['notice'][] = 'Handle Award Upload deprecated , please upgrade your SFIAB';
	$response['error'] = 1;
}

function handle_awards_upload(&$u, &$fair, &$data, &$response)
{
	global $pdo;
	//	$response['debug'] = array_keys($data['awards_upload']);
	//	$response['error'] = 0;
	//	return;
	foreach ($data['awards_upload'] as $award_data) {
		$external_identifier = $award_data['external_identifier'];
		$year = intval($award_data['year']);

		/* Find the award */
		$eid = $external_identifier;

		$q = $pdo->prepare("SELECT * FROM award_awards WHERE external_identifier=? AND year=?");
		$q->execute([$eid,$year]);
		if ($q->rowCount() != 1) {
			$response['message'] = "Unknown award identifier '$eid' for year $year";
			$response['error'] = 1;
			return;
		}
		$award = $q->fetch(PDO::FETCH_ASSOC);
		$aaid = $award['id'];

		$response['notice'][] = "Found award: {$award['name']}";

		/*
		 * Load prizes, we fetched the right award by year, so we don't need to
		 * check the year as long as we query by aaid
		 */
		$prizes = array();
		$q = $pdo->prepare("SELECT * FROM award_prizes WHERE award_awards_id=?");
		$q->execute([$aaid]);
		while ($prize = $q->fetch(PDO::FETCH_ASSOC)) {
			$response['notice'][] = " - Prize: {$prize['prize']}";

			/* Clean out existing winners for this prize */
			$stmt = $pdo->prepare("DELETE FROM winners WHERE 
					award_prize_id=? 
					AND fairs_id=?");
			$stmt->execute([$prize['id'],$fair['id']]);

			/* Assign projects to this prize */
			$ul_p = &$award_data['prizes'][$prize['prize']];
			if (!is_array($ul_p['projects']))
				continue;

			foreach ($ul_p['projects'] as &$project) {
				award_upload_assign($fair, $award, $prize, $project, $year, $response);
			}
		}
	}
	$response['notice'][] = 'All awards and winners saved';
	$response['error'] = 0;
}

function handle_get_categories(&$u, &$fair, &$data, &$response)
{
	global $pdo;
	$year = intval($data['get_categories']['year']);
	$cat = array();
	$q = $pdo->prepare("SELECT * FROM projectcategories WHERE year=? ORDER BY id");
	$q->execute([$year]);
	while ($r = $q->fetch(PDO::FETCH_OBJ)) {
		$cat[$r->id] = array('id' => $r->id,
			'category' => $r->category,
			'mingrade' => $r->mingrade,
			'maxgrade' => $r->maxgrade);
	}
	$response['categories'] = $cat;
	$response['error'] = 0;
}

function handle_get_divisions(&$u, &$fair, &$data, &$response)
{
	global $pdo;
	$year = intval($data['get_divisions']['year']);
	$div = array();
	$q = $pdo->prepare("SELECT * FROM projectdivisions WHERE year=? ORDER BY id");
	$q->execute([$year]);
	while ($r = $q->fetch(PDO::FETCH_OBJ)) {
		$div[$r->id] = array('id' => $r->id,
			'division' => $r->division);
	}
	$response['divisions'] = $div;
	$response['error'] = 0;
}

function handle_award_additional_materials(&$u, &$fair, &$data, &$response)
{
	global $pdo;
	$year = intval($data['award_additional_materials']['year']);
	$external_identifier = $data['award_additional_materials']['identifier'];

	$eid = $external_identifier;
	$q = $pdo->prepare("SELECT * FROM award_awards WHERE external_identifier=? AND year=?");
	$q->execute([$eid,$year]);
	if ($q->rowCount() != 1) {
		$response['message'] = "Unknown award identifier '$eid'";
		$response['error'] = 1;
		return;
	}
	$award = $q->fetch(PDO::FETCH_ASSOC);

	$pdf = fair_additional_materials($fair, $award, $year);
	$response['award_additional_materials']['pdf']['header'] = $pdf['header'];
	$response['award_additional_materials']['pdf']['data64'] = base64_encode($pdf['data']);
	$response['error'] = 0;
}

// echo "post:";print_r($_POST);
// echo "json post: ".htmlspecialchars($_POST['json'])."<br>";
// echo "stripslashes(json post): ".stripslashes($_POST['json'])."<br>";
// echo "data:";print_r($data);
// echo "<br />";
// exit;

$username = $data['auth']['username'];
$password = $data['auth']['password'];

$response['query'] = $data;

// echo "Authenticating... ";
$u = user_load_by_email($username);
if ($u == false) {
	$response['error'] = 1;
	$response['message'] = 'Authentication Failed';
	echo json_encode($response);
	exit;
}
if (!is_array($u) || $u['password'] == '') {
	$response['error'] = 1;
	$response['message'] = 'Authentication Failed';
	echo json_encode($response);
	exit;
}

if (!password_verify($password, $u['password'])) {
	$response['error'] = 1;
	$response['message'] = 'Authentication Failed';
	echo json_encode($response);
	exit;
}

$q = $pdo->prepare("SELECT * FROM fairs WHERE id='?'");
$q->execute([$u['fairs_id']]);
$fair = $q->fetch(PDO::FETCH_ASSOC);

$response = array();
if (array_key_exists('getstats', $data))
	handle_getstats($u, $fair, $data, $response);
if (array_key_exists('stats', $data))
	handle_stats($u, $fair, $data, $response);
if (array_key_exists('getawards', $data))
	handle_getawards($u, $fair, $data, $response);
if (array_key_exists('awards_upload', $data))
	handle_awards_upload($u, $fair, $data, $response);
if (array_key_exists('award_upload', $data))
	handle_award_upload($u, $fair, $data, $response);
if (array_key_exists('get_categories', $data))
	handle_get_categories($u, $fair, $data, $response);
if (array_key_exists('get_divisions', $data))
	handle_get_divisions($u, $fair, $data, $response);
if (array_key_exists('award_additional_materials', $data))
	handle_award_additional_materials($u, $fair, $data, $response);

// $response['hi'] = 'hi';
echo urlencode(json_encode($response));
// echo "Success!<br />";

?>