science-ation/config/rollover.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-2009 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("../common.inc.php");
 require_once("../user.inc.php");
 require_once("../config_editor.inc.php");
 user_auth_required('committee', 'config');
 send_header("Year Rollover",
 		array('Committee Main' => 'committee_main.php',
			'SFIAB Configuration' => 'config/index.php')
            ,"rollover_fair_year"
			);
 ?>

 <script language="javascript" type="text/javascript">
 function confirmYearRollover()
 {
 	var currentyear=<?=$config['FAIRYEAR']?>;
	var nextyear=document.forms.rollover.nextfairyear.value;
	if(nextyear<currentyear)
		alert('You cannot roll backwards in years!');
	else if(nextyear==currentyear)
		alert('You cannot roll to the same year!');
	else
	{

		var okay=confirm('Are you sure you want to roll the FAIRYEAR from '+currentyear+' to '+nextyear+'? This can not be undone and should only be done if you are absolutely sure!');
		if(okay)
			return true; 
	}
	return false;
 }
 </script>
 <?

 function roll($currentfairyear, $newfairyear, $table, $where='', $replace=array())
 {
	global $pdo;
	/*  Field 	Type 			Null 	Key 	Default 	Extra
	 * id 		int(10) unsigned 	NO 	PRI 	NULL 	auto_increment
	 * sponsors_id 	int(10) unsigned 	NO 	MUL 	0 	 
	 * award_source_fairs_id int(10) unsigned YES  	   	NULL  	 
	*/

	/* Get field list for this table */
 	$q = $pdo->prepare("SHOW COLUMNS IN `$table`");
	$q->execute();
	show_pdo_errors_if_any($pdo);
	while(($c = $q->fetch(PDO::FETCH_ASSOC))) {
		$col[$c['Field']] = $c;
	}

	/* Record fields we care about */
	$fields = array();
	$keys = array_keys($col);
	foreach($keys as $k) {
		/* Skip id field */
		if($col[$k]['Extra'] == 'auto_increment') continue;
		/* Skip year field */
		if($k == 'year') continue;

		$fields[] = $k;
	} 

	if($where == '') $where='1';

	/* Get data */
	$q=$pdo->prepare("SELECT * FROM $table WHERE year='$currentfairyear' AND $where");
	$q->execute();
	show_pdo_errors_if_any($pdo);
	$names = '`'.join('`,`', $fields).'`';

	/* Process data */
	while($r=$q->fetch(PDO::FETCH_ASSOC)) {
		$vals = '';
		foreach($fields as $f) {
			if(array_key_exists($f, $replace))
				$vals .= ",".$pdo->quote($replace[$f]);
			else if($col[$f]['Null'] == 'YES' && $r[$f] == NULL)
				$vals .= ',NULL';
			else 
				$vals .= ",".$pdo->quote($r[$f]);
		}

		$stmt = $pdo->prepare("INSERT INTO `$table`(`year`,$names) VALUES ('$newfairyear'$vals)");
		$stmt->execute();
		show_pdo_errors_if_any($pdo);
	}
 }

 if(get_value_from_array($_POST, 'action') == "rollover" && get_value_from_array($_POST, 'nextfairyear'))
 {
 	$newfairyear=intval(get_value_from_array($_POST, 'nextfairyear'));
	$currentfairyear=intval($config['FAIRYEAR']);

	$cy = $currentfairyear;
	$ny = $newfairyear;

	if($newfairyear<$currentfairyear)
		echo error(i18n("You cannot roll backwards in years!"));
	else if($newfairyear==$currentfairyear)
		echo error(i18n("You cannot roll to the same year!"));
	else
	{
		//okay here we go! this is going to get to be a pretty big script me thinks!

		//first, lets do all of the configuration variables
		echo i18n("Rolling configuration variables")."<br />";
		config_update_variables($newfairyear, $currentfairyear);

		//now the dates
		echo i18n("Rolling dates")."<br />";
		$q=$pdo->prepare("SELECT DATE_ADD(date,INTERVAL 365 DAY) AS newdate,name,description FROM dates WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO dates (date,name,description,year) VALUES (
				'".$r->newdate."',
				'".$r->name."',
				'".$r->description."',
				'".$newfairyear."')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}
		

		//page text
		echo i18n("Rolling page texts")."<br />";
		$q=$pdo->prepare("SELECT * FROM pagetext WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO pagetext (textname,textdescription,text,lastupdate,year,lang) VALUES (
				'".$r->textname."',
				'".$r->textdescription."',
				'".$r->text."',
				'".$r->lastupdate."',
				'".$newfairyear."',
                '".$r->lang."')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		echo i18n("Rolling project categories")."<br />";
		//project categories
		$q=$pdo->prepare("SELECT * FROM projectcategories WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO projectcategories (id,category,category_shortform,mingrade,maxgrade,year) VALUES (
				'".$r->id."',
				'".$r->category."',
				'".$r->category_shortform."',
				'".$r->mingrade."',
				'".$r->maxgrade."',
				'".$newfairyear."')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		echo i18n("Rolling project divisions")."<br />";
		//project divisions
		$q=$pdo->prepare("SELECT * FROM projectdivisions WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO projectdivisions (id,division,division_shortform,cwsfdivisionid,year) VALUES (
				'".$r->id."',
				'".$r->division."',
				'".$r->division_shortform."',
				'".$r->cwsfdivisionid."',
				'".$newfairyear."')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		echo i18n("Rolling project category-division links")."<br />";
		//project categories divisions links
		$q=$pdo->prepare("SELECT * FROM projectcategoriesdivisions_link WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO projectcategoriesdivisions_link (projectdivisions_id,projectcategories_id,year) VALUES (
				'".$r->projectdivisions_id."',
				'".$r->projectcategories_id."',
				'".$newfairyear."')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		echo i18n("Rolling project sub-divisions")."<br />";
		//project subdivisions
		$q=$pdo->prepare("SELECT * FROM projectsubdivisions WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO projectsubdivisions (id,projectdivisions_id,subdivision,year) VALUES (
				'".$r->id."',
				'".$r->projectsubdivisions_id."',
				'".$r->subdivision."',
				'".$newfairyear."')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		echo i18n("Rolling safety questions")."<br />";
		//safety questions 
		$q=$pdo->prepare("SELECT * FROM safetyquestions WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO safetyquestions (question,type,required,ord,year) VALUES (
				'".$r->question."',
				'".$r->type."',
				'".$r->required."',
				'".$r->ord."',
				'".$newfairyear."')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		echo i18n("Rolling awards")."<br />";
		//awards


		$q=$pdo->prepare("SELECT * FROM award_awards WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			/* Roll the one award */
			roll($cy, $ny, 'award_awards', "id='{$r->id}'");
			$award_awards_id=$pdo->lastInsertId();

			roll($cy, $ny, 'award_awards_projectcategories', "award_awards_id='{$r->id}'",
								array('award_awards_id' => $award_awards_id));

			roll($cy, $ny, 'award_awards_projectdivisions', "award_awards_id='{$r->id}'",
								array('award_awards_id' => $award_awards_id));
			echo i18n("&nbsp; Rolling award prizes")."<br />";
			roll($cy, $ny, 'award_prizes', "award_awards_id='{$r->id}'",
								array('award_awards_id' => $award_awards_id));
		}

		echo i18n("Rolling award types")."<br />";
		//award types
		$q=$pdo->prepare("SELECT * FROM award_types WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO award_types (id,type,`order`,year) VALUES (
				'".$r->id."',
				'".$r->type."',
				'".$r->order."',
				'".$newfairyear."')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		echo i18n("Rolling schools")."<br />";
		//award types
		$q=$pdo->prepare("SELECT * FROM schools WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$puid = ($r->principal_uid == null) ? 'NULL' : ("'".intval($r->principal_uid)."'");
			$shuid = ($r->sciencehead_uid == null) ? 'NULL' : ("'".intval($r->sciencehead_uid)."'");

			$stmt = $pdo->prepare("INSERT INTO schools (school,schoollang,schoollevel,board,district,phone,fax,address,city,province_code,postalcode,principal_uid,schoolemail,sciencehead_uid,accesscode,lastlogin,junior,intermediate,senior,registration_password,projectlimit,projectlimitper,year) VALUES (
				".$pdo->quote($r->school).",
				".$pdo->quote($r->schoollang).",
				".$pdo->quote($r->schoollevel).",
				".$pdo->quote($r->board).",
				".$pdo->quote($r->district).",
				".$pdo->quote($r->phone).",
				".$pdo->quote($r->fax).",
				".$pdo->quote($r->address).",
				".$pdo->quote($r->city).",
				".$pdo->quote($r->province_code).",
				".$pdo->quote($r->postalcode).",$puid,
				".$pdo->quote($r->schoolemail).",$shuid,
				".$pdo->quote($r->accesscode).",
				NULL,
				".$pdo->quote($r->junior).",
				".$pdo->quote($r->intermediate).",
				".$pdo->quote($r->senior).",
				".$pdo->quote($r->registration_password).",
				".$pdo->quote($r->projectlimit).",
				".$pdo->quote($r->projectlimitper).",
				".$newfairyear.")");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		echo i18n("Rolling questions")."<br />";
		$q = $pdo->prepare("SELECT * FROM questions WHERE year='$currentfairyear'");
		$q->execute();
		show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_OBJ)) {
			$stmt = $pdo->prepare("INSERT INTO questions (id,year,section,db_heading,question,type,required,ord) VALUES (
				'',
				'$newfairyear',
				".$pdo->quote($r->section).",
				".$pdo->quote($r->db_heading).",
				".$pdo->quote($r->question).",
				".$pdo->quote($r->type).",
				".$pdo->quote($r->required).",
				".$pdo->quote($r->ord).")");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
		}

		//regfee items
		echo i18n("Rolling registration fee items")."<br />";
		roll($cy, $ny, 'regfee_items');

		//volunteer positions
		echo i18n('Rolling volunteer positions')."<br />";
		roll($cy, $ny, 'volunteer_positions');

		//timeslots and rounds
		echo i18n('Rolling judging timeslots and rounds')."<br />";
		$q=$pdo->prepare("SELECT * FROM judges_timeslots WHERE year='$currentfairyear' AND round_id='0'");
		$q->execute();
        show_pdo_errors_if_any($pdo);
		while($r=$q->fetch(PDO::FETCH_ASSOC)) {
			$d = $newfairyear - $currentfairyear;
			$stmt = $pdo->prepare("INSERT INTO judges_timeslots (`year`,`round_id`,`type`,`date`,`starttime`,`endtime`,`name`)
				VALUES ('$newfairyear','0','{$r['type']}',DATE_ADD('{$r['date']}', INTERVAL $d YEAR),
					'{$r['starttime']}','{$r['endtime']}','{$r['name']}')");
			$stmt->execute();
			show_pdo_errors_if_any($pdo);
			$round_id = $pdo->lastInsertId();
			$qq = $pdo->prepare("SELECT * FROM judges_timeslots WHERE round_id='{$r['id']}'");
			$qq->execute();
			show_pdo_errors_if_any($pdo);
			while($rr=$qq->fetch(PDO::FETCH_ASSOC)) {
				$stmt = $pdo->prepare("INSERT INTO judges_timeslots (`year`,`round_id`,`type`,`date`,`starttime`,`endtime`)
						VALUES ('$newfairyear','$round_id','timeslot',DATE_ADD('{$rr['date']}', INTERVAL $d YEAR),
							'{$rr['starttime']}','{$rr['endtime']}')");
				$stmt->execute();
				show_pdo_errors_if_any($pdo);
			}
		}

		echo "<br /><br />";
		$stmt = $pdo->prepare("UPDATE config SET val='$newfairyear' WHERE var='FAIRYEAR' AND year=0");
		$stmt->execute();
		show_pdo_errors_if_any($pdo);
		echo happy(i18n("Fair year has been rolled over from %1 to %2",array($currentfairyear,$newfairyear)));
		send_footer();
		exit;
	}
 }

 echo "<br />";
 echo "<a href=\"backuprestore.php\">".i18n("You should consider making a database backup before rolling over, just in case!")."</a><br />\n";
 echo "<br />";
 echo "<form name=\"rollover\" method=\"post\" action=\"rollover.php\" onsubmit=\"return confirmYearRollover()\">";
 echo "<input type=\"hidden\" name=\"action\" value=\"rollover\" />";
 echo i18n("Current Fair Year").": <b>".$config['FAIRYEAR']."</b><br />";
 $nextfairyear=$config['FAIRYEAR']+1;
 echo i18n("Next Fair Year").": <input size=\"8\" type=\"text\" name=\"nextfairyear\" value=\"$nextfairyear\" />";
 echo "<br />";
 echo "<input type=\"submit\" value=\"".i18n("Rollover Fair Year")."\" />";
 echo "</form>";

 send_footer();
?>