science-ation/admin/award_download.php

<?

/*
 * This file is part of the 'Science Fair In A Box' project
 * Science-ation Website: https://science-ation.ca/
 *
 * Copyright (C) 2007 James Grant <james@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');
user_auth_required('committee', 'admin');
require_once('curl.inc.php');
require_once('awards.inc.php');

switch (get_value_from_array($_GET, 'action')) {
	case 'check':
		$fairs_id = intval($_GET['fairs_id']);

		$q = $pdo->prepare("SELECT * FROM fairs WHERE id=?");
		$q->execute([$fairs_id]);
		$fair = $q->fetch(PDO::FETCH_ASSOC);
		if (!($fair['username'] && $fair['password'])) {
			echo error(i18n("Username and Password are not set for source '%1'.  Please set them in the SFIAB Configuration/External Award Sources editor first", array($r->name)));
			return;
		}

		echo i18n('Checking %1 for awards...', array($fair['name']));
		echo '<br />';

		if ($fair['type'] == 'ysc') {
			$req = array('awardrequest' => array(
				'username' => $fair['username'],
				'password' => $fair['password'],
				'year' => $config['FAIRYEAR'],
			));
		} else {
			$req['getawards'] = array('year' => $config['FAIRYEAR']);
		}

		$data = curl_query($fair, $req);

		if ($data['error'] != 0) {
			echo error("Server said: {$data['message']}<br />");
			send_footer();
			exit;
		}
		echo notice(i18n('Server said: Success'));
		//            echo "sending [".nl2br(htmlspecialchars($xmldata))."]";

		$keys = array_keys($data);
		if (!array_key_exists('awards', $data)) {
			echo error(i18n("Invalid XML response.  Expecting '%1' in '%2'", array('awards', join(',', array_keys($data)))));
			//				echo "response=".print_r($data);
			return;
		}

		// get a list of all the existing awards for this external source

		$aq = $pdo->prepare("SELECT * FROM award_awards WHERE award_source_fairs_id='$fairs_id' AND year='{$config['FAIRYEAR']}'");
		$aq->execute();
		$existingawards = array();
		while ($ar = $aq->fetch(PDO::FETCH_OBJ)) {
			$existingawards[$ar->id] = true;
		}

		echo '<i>';
		$awards = $data['awards'];
		$postback = $data['postback'];
		echo i18n('Postback URL: %1', array($postback)) . ' <br />';

		$numawards = is_array($awards) ? count($awards) : 0;
		echo i18n('Number of Awards: %1', array($numawards)) . ' <br />';

		if ($numawards == 0) {
			echo i18n('No awards to process') . '</i> <br />';
			return;
		}

		$divs = projectdivisions_load();
		$cats = projectcategories_load();

		foreach ($awards as $award) {
			$identifier = $award['identifier'];
			$year = $award['year'];
			echo i18n('Award Identifier: %1', array($identifier)) . ' &nbsp; ';
			echo i18n('Award Year: %1', array($year)) . '<br />';
			echo i18n('Award Name: %1', array($award['name_en'])) . '<br />';

			if ($year != $config['FAIRYEAR']) {
				echo error(i18n('Award is not for the current fair year... skipping'));
				echo '<br />';
				continue;
			}

			$tq = $pdo->prepare("SELECT * FROM award_awards WHERE 
					external_identifier='$identifier' AND 
					award_source_fairs_id='$fairs_id' AND 
					year='$year'");
			$tq->execute();
			if ($tq->rowCount() == 0) {
				/* Award doesn't exist, create it, then update it with the common code below */

				$q = $pdo->prepare("INSERT INTO award_awards (award_types_id,
						year, external_identifier,
						award_source_fairs_id) 
					VALUES (2,'{$year}',
					'" . $identifier . "',
					'$fairs_id')");
				$q->execute();
				$award_id = $pdo->lastInsertId();
				/* By default make all divs/cats eligible */
				foreach ($divs as $id => $d)
					$q = $pdo->prepare("INSERT INTO award_awards_projectdivisions(award_awards_id,projectdivisions_id,year) VALUES ('$award_id','$id','{$config['FAIRYEAR']}')");
				$q->execute();

				foreach ($cats as $id => $c)
					$q = $pdo->prepare("INSERT INTO award_awards_projectcategories(award_awards_id,projectcategories_id,year) VALUES ('$award_id','$id','{$config['FAIRYEAR']}')");
				$q->execute();
			} else {
				echo i18n('Award already exists, updating info') . '<br />';
				$awardrecord = $q->fetch(PDO::FETCH_OBJ);
				$award_id = $awardrecord->id;
			}

			// remove it from the existingawards list
			unset($existingawards[$award_id]);

			// check if the sponsor exists, if not, add them
			$sponsor_str = $award['sponsor'];

			$sponsorq = $pdo->prepare("SELECT * FROM sponsors WHERE organization='$sponsor_str'");
			$sponsorq->execute();
			if ($sponsorr = $sponsorq->fetch(PDO::FETCH_OBJ)) {
				$sponsor_id = $sponsorr->id;
			} else {
				$q = $pdo->prepare("INSERT INTO sponsors (organization,year,notes) 
				VALUES ('$sponsor_str','$year','" . "Imported from external source: $r->name" . "')");
				$q->execute();
				show_pdo_errors_if_any($pdo);
				$sponsor_id = $pdo->lastInsertId();
			}

			$self_nominate = ($award['self_nominate'] == 'yes') ? 'yes' : 'no';
			$schedule_judges = ($award['schedule_judges'] == 'yes') ? 'yes' : 'no';

			$q = $pdo->prepare("UPDATE award_awards SET
				sponsors_id='$sponsor_id',
				name='" . $award['name_en'] . "',
				criteria='" . $award['criteria_en'] . "',
				external_postback='" . $postback . "',
				external_register_winners='" . (($award['external_register_winners'] == 1) ? 1 : 0) . "',
				external_additional_materials='" . (($award['external_additional_materials'] == 1) ? 1 : 0) . "',
				self_nominate='$self_nominate',
				schedule_judges='$schedule_judges'
			WHERE 
				id='$award_id' 
				AND external_identifier='" . $identifier . "' 
				AND year='$year'
		");
			$q->execute();
			show_pdo_errors_if_any($pdo);

			// update the prizes
			$prizes = $award['prizes'];
			if (!is_array($prizes)) {
				continue;
			}

			echo i18n('Number of prizes: %1', array(count($prizes))) . '<br />';
			/* Get existing prizes */

			$pq = $pdo->prepare("SELECT * FROM award_prizes WHERE award_awards_id='$award_id'");
			$pq->execute();
			$existingprizes = array();
			while ($pr = $pq->fetch(PDO::FETCH_ASSOC))
				$existingprizes[$pr['prize']] = $pr;

			/* Iterate over the downloaded pizes */
			foreach ($prizes as $prize) {
				// if it doesn't exist, add it
				if (!array_key_exists($prize['prize_en'], $existingprizes)) {
					/*
					 * Add a base entry, then update it below, yes it's two sql queries,
					 * but it's much shorter code, and means changing things in only
					 * one spot
					 */
					echo '&nbsp;' . i18n('Adding prize %1', array($prize['prize_en'])) . '<br />';
					$p = stripslashes($prize['prize_en']);

					$q = $pdo->prepare("INSERT INTO award_prizes (award_awards_id,prize,year,external_identifier)
					VALUES ('$award_id','$p','$year','$p')");
					$q->execute();
					$prize_id = $pdo->lastInsertId();
				} else {
					$ep = $existingprizes[$prize['prize_en']];
					echo '&nbsp;' . i18n('Updating prize %1', array($ep['prize'])) . '<br />';
					$prize_id = $ep['id'];
					// remove it from the list
					unset($existingprizes[$ep['prize']]);
				}

				if (!array_key_exists('identifier', $prize))
					$prize['identifier'] = $prize['prize_en'];

				$q = $pdo->prepare("UPDATE award_prizes SET 
					cash='" . intval($prize['cash']) . "',
					scholarship='" . intval($prize['scholarship']) . "',
					value='" . intval($prize['value']) . "',
					prize='" . $prize['prize_en'] . "',
					number='" . intval($prize['number']) . "',
					`order`='" . intval($prize['ord']) . "',
					external_identifier='" . stripslashes($prize['identifier']) . "',
					trophystudentkeeper='" . intval($prize['trophystudentkeeper']) . "',
					trophystudentreturn='" . intval($prize['trophystudentreturn']) . "',
					trophyschoolkeeper='" . intval($prize['trophyschoolkeeper ']) . "',
					trophyschoolreturn='" . intval($prize['trophyschoolreturn']) . "'
				WHERE
					id='$prize_id'");

				$q->execute();

				show_pdo_errors_if_any($pdo);
				// FIXME: update the translations
			}

			/* Delete local entries that weren't downloaded */
			foreach ($existingprizes as $ep) {
				echo '&nbsp;' . i18n('Removing prize %1', array($ep['prize'])) . '<br />';
				award_prize_delete($ep['id']);
			}
		}
		echo '<br />';

		// remove any awards that are left in the $existingawards array, they must have been removed from the source
		foreach ($existingawards as $aid => $val) {
			echo i18n('Removing award id %1 that was removed from external source', array($aid)) . '<br />';
			award_delete($aid);
		}

		echo '</i>';
		exit;
}

send_header(
	'Download Awards',
	array(
		'Committee Main' => 'committee_main.php',
		'Administration' => 'admin/index.php',
		'Awards Main' => 'admin/awards.php'
	)
);

?>
<script type="text/javascript">
	function award_download(id) {
		if (id == -1) return false;
		$("#award_download_status").load("<?= $_SERVER['PHP_SELF'] ?>?action=check&fairs_id=" + id);
	}
</script>

<?

if (!function_exists('curl_init')) {
	echo error(i18n('CURL Support Missing'));
	echo notice(i18n('Your PHP installation does not support CURL.  You will need to have CURL support added by your system administrator before being able to access external award sources'));
	$links = false;
} else {
	$links = true;
}

?>
<table class="tableview">
	<thead>
		<tr>
			<th><?= i18n('Source Name') ?></th>
			<th><?= i18n('Source Location URL') ?></th>
			<th><?= i18n('Check') ?></th>
		</tr>
	</thead>
	<?

	$q = $pdo->prepare("SELECT * FROM fairs WHERE enable_awards='yes' ORDER BY name");
	$q->execute();
	while ($r = $q->fetch(PDO::FETCH_OBJ)) {
		echo '<tr>';
		echo "<td>{$r->name}</td>\n";
		echo "<td>{$r->url}</td>";
		echo '<td align="center">';
		if ($links)
			echo "<a href=\"#\" onclick=\"award_download({$r->id})\">" . i18n('check') . '</a>';
		else
			echo 'n/a';
		echo '</td>';
		echo '</tr>';
		//	$checkurl.="&check[]={$r->id}";
	}

	/*
 * if($links)
 * 	echo "<a href=\"award_download.php?action=check$checkurl\">".i18n("Check all sources")."</a>";
 */
	?>
</table>
<br />
<div id="award_download_status"></div>

<?
send_footer();
?>