forked from science-ation/science-ation
363 lines
11 KiB
PHP
363 lines
11 KiB
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>
|
|
*
|
|
* 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.
|
|
*/
|
|
?>
|
|
<?
|
|
function getProjectsEligibleForAward($award_id)
|
|
{
|
|
global $config;
|
|
global $pdo;
|
|
|
|
$prjq = $pdo->prepare("SELECT
|
|
award_awards.id,
|
|
award_awards_projectcategories.projectcategories_id,
|
|
award_awards_projectdivisions.projectdivisions_id,
|
|
projects.projectnumber,
|
|
projects.title,
|
|
projects.id AS projects_id,
|
|
projects.fairs_id
|
|
FROM
|
|
award_awards,
|
|
award_awards_projectcategories,
|
|
award_awards_projectdivisions,
|
|
projects
|
|
WHERE
|
|
award_awards.id=?
|
|
AND award_awards.id=award_awards_projectcategories.award_awards_id
|
|
AND award_awards.id=award_awards_projectdivisions.award_awards_id
|
|
AND projects.projectcategories_id=award_awards_projectcategories.projectcategories_id
|
|
AND projects.projectdivisions_id=award_awards_projectdivisions.projectdivisions_id
|
|
AND projects.projectnumber is not null
|
|
AND projects.year=?'
|
|
ORDER BY
|
|
projectsort
|
|
");
|
|
$prjq->execute([$award_id, $config['FAIRYEAR']]);
|
|
$projects = array();
|
|
while ($prjr = $prjq->fetch(PDO::FETCH_OBJ)) {
|
|
$projects[$prjr->projectnumber] = array(
|
|
'id' => $prjr->projects_id,
|
|
'projectnumber' => $prjr->projectnumber,
|
|
'title' => $prjr->title,
|
|
'fairs_id' => $prjr->fairs_id
|
|
);
|
|
}
|
|
return $projects;
|
|
}
|
|
|
|
function getLanguagesOfProjectsEligibleForAward($award_id)
|
|
{
|
|
global $config, $pdo;
|
|
|
|
$prjq = $pdo->prepare("SELECT DISTINCT(projects.language) AS language
|
|
FROM
|
|
award_awards,
|
|
award_awards_projectcategories,
|
|
award_awards_projectdivisions,
|
|
projects
|
|
WHERE
|
|
award_awards.id=?
|
|
AND award_awards.id=award_awards_projectcategories.award_awards_id
|
|
AND award_awards.id=award_awards_projectdivisions.award_awards_id
|
|
AND projects.projectcategories_id=award_awards_projectcategories.projectcategories_id
|
|
AND projects.projectdivisions_id=award_awards_projectdivisions.projectdivisions_id
|
|
AND projects.projectnumber is not null
|
|
AND projects.year=?
|
|
ORDER BY
|
|
language
|
|
");
|
|
$prjq->execute([$award_id, $config['FAIRYEAR']]);
|
|
$languages = array();
|
|
while ($r = $prjq->fetch(PDO::FETCH_OBJ)) {
|
|
if ($r->language)
|
|
$languages[] = $r->language;
|
|
}
|
|
return $languages;
|
|
}
|
|
|
|
function getProjectsEligibleOrNominatedForAwards($awards_ids_array)
|
|
{
|
|
global $pdo;
|
|
$projects = array();
|
|
foreach ($awards_ids_array AS $award_id) {
|
|
$q = $pdo->prepare("SELECT award_types.type FROM award_awards, award_types WHERE award_awards.id=? AND award_awards.award_types_id=award_types.id");
|
|
$q->execute([$award_id]);
|
|
$r = $q->fetch(PDO::FETCH_OBJ);
|
|
|
|
$awardprojects = array();
|
|
|
|
// for special awards, we only want the ones that were nominated for it.
|
|
// for everything else, we weant all the eligible projects
|
|
if ($r->type == 'Special')
|
|
$awardprojects = getProjectsNominatedForSpecialAward($award_id);
|
|
else
|
|
$awardprojects = getProjectsEligibleForAward($award_id);
|
|
|
|
// $projects[$award_id]=$awardprojects;
|
|
|
|
// this will just overwrite ones that already exist, but still keep things in order because the main key is the projectnumber (i hope)
|
|
foreach ($awardprojects AS $proj)
|
|
$projects[$proj['projectnumber']] = $proj;
|
|
}
|
|
|
|
return $projects;
|
|
}
|
|
|
|
function getSpecialAwardsEligibleForProject($projectid)
|
|
{
|
|
global $config, $pdo;
|
|
|
|
$awardsq = $pdo->prepare("SELECT
|
|
award_awards.id,
|
|
award_awards.name,
|
|
award_awards.criteria,
|
|
award_awards_projectcategories.projectcategories_id,
|
|
award_awards_projectdivisions.projectdivisions_id,
|
|
projects.id AS projects_id,
|
|
award_awards.self_nominate
|
|
FROM
|
|
award_awards,
|
|
award_awards_projectcategories,
|
|
award_awards_projectdivisions,
|
|
award_types,
|
|
projects
|
|
WHERE
|
|
award_types.type='Special'
|
|
AND award_types.id=award_awards.award_types_id
|
|
AND award_awards.id=award_awards_projectcategories.award_awards_id
|
|
AND award_awards.id=award_awards_projectdivisions.award_awards_id
|
|
AND projects.projectcategories_id=award_awards_projectcategories.projectcategories_id
|
|
AND projects.projectdivisions_id=award_awards_projectdivisions.projectdivisions_id
|
|
AND award_awards.id is not null
|
|
AND projects.year=?
|
|
AND projects.id=?
|
|
AND award_types.year=?
|
|
AND award_awards.year=?
|
|
ORDER BY
|
|
award_awards.name
|
|
");
|
|
$awardsq->execute([$config['FAIRYEAR'], $config['FAIRYEAR'], $config['FAIRYEAR']]);
|
|
$awards = array();
|
|
show_pdo_errors_if_any($pdo);
|
|
while ($r = $awardsq->fetch(PDO::FETCH_OBJ)) {
|
|
$awards[$r->id] = array(
|
|
'id' => $r->id,
|
|
'criteria' => $r->criteria,
|
|
'name' => $r->name,
|
|
'self_nominate' => $r->self_nominate
|
|
);
|
|
}
|
|
return $awards;
|
|
}
|
|
|
|
function getSpecialAwardsNominatedForProject($projectid)
|
|
{
|
|
global $config, $pdo;
|
|
|
|
$awardsq = $pdo->prepare("SELECT
|
|
award_awards.id,
|
|
award_awards.name,
|
|
award_awards.criteria,
|
|
projects.id AS projects_id,
|
|
projects.fairs_id
|
|
FROM
|
|
award_awards,
|
|
project_specialawards_link,
|
|
projects
|
|
WHERE
|
|
project_specialawards_link.projects_id=?
|
|
AND project_specialawards_link.award_awards_id=award_awards.id
|
|
AND projects.year=?
|
|
AND projects.id=?
|
|
ORDER BY
|
|
award_awards.name
|
|
");
|
|
$awardsq->execute([$projectid, $config['FAIRYEAR'], $projectid]);
|
|
$awards = array();
|
|
show_pdo_errors_if_any($pdo);
|
|
while ($r = $awardsq->fetch(PDO::FETCH_OBJ)) {
|
|
$awards[$r->id] = array(
|
|
'id' => $r->id,
|
|
'criteria' => $r->criteria,
|
|
'name' => $r->name,
|
|
'fairs_id' => $r->fairs_id
|
|
);
|
|
}
|
|
return $awards;
|
|
}
|
|
|
|
function getNominatedForNoSpecialAwardsForProject($projectid)
|
|
{
|
|
global $config, $pdo;
|
|
$awardsq = $pdo->prepare("SELECT
|
|
projects.id AS projects_id
|
|
FROM
|
|
project_specialawards_link,
|
|
projects
|
|
WHERE
|
|
project_specialawards_link.projects_id=?
|
|
AND projects.year=?
|
|
AND projects.id=?
|
|
AND project_specialawards_link.award_awards_id IS NULL
|
|
");
|
|
$awardsq->execute([$projectid, $config['FAIRYEAR'], $projectid]);
|
|
if ($awardsq->rowCount() == 1)
|
|
return true;
|
|
return false;
|
|
}
|
|
|
|
function getProjectsNominatedForSpecialAward($award_id)
|
|
{
|
|
global $config, $pdo;
|
|
|
|
// if they dont use special award nominations, then we will instead get all of the projects that
|
|
// are eligible for the award, instead of nominated for it.
|
|
if ($config['specialawardnomination'] != 'none') {
|
|
$prjq = $pdo->prepare("SELECT
|
|
projects.projectnumber,
|
|
projects.title,
|
|
projects.language,
|
|
projects.id AS projects_id
|
|
FROM
|
|
project_specialawards_link,
|
|
projects
|
|
WHERE
|
|
project_specialawards_link.award_awards_id=?
|
|
AND project_specialawards_link.projects_id=projects.id
|
|
AND projects.projectnumber is not null
|
|
AND projects.year=?
|
|
ORDER BY
|
|
projectsort
|
|
");
|
|
$prjq->execute([$award_id, $config['FAIRYEAR']]);
|
|
$projects = array();
|
|
while ($prjr = $prjq->fetch(PDO::FETCH_OBJ)) {
|
|
$projects[$prjr->projectnumber] = array(
|
|
'id' => $prjr->projects_id,
|
|
'projectnumber' => $prjr->projectnumber,
|
|
'language' => $prjr->language,
|
|
'title' => $prjr->title
|
|
);
|
|
}
|
|
// return the projects that have self-nominated themselves for the award
|
|
return $projects;
|
|
} else {
|
|
// return the projects that are eligible for the award instead
|
|
return getProjectsEligibleForAward($award_id);
|
|
}
|
|
}
|
|
|
|
function getLanguagesOfProjectsNominatedForSpecialAward($award_id)
|
|
{
|
|
global $config, $pdo;
|
|
|
|
// if they dont use special award nominations, then we will instead get all of the projects that
|
|
// are eligible for the award, instead of nominated for it.
|
|
if ($config['specialawardnomination'] != 'none') {
|
|
$prjq = $pdo->prepare("SELECT DISTINCT(projects.language) AS language
|
|
FROM
|
|
project_specialawards_link,
|
|
projects
|
|
WHERE
|
|
project_specialawards_link.award_awards_id=?
|
|
AND project_specialawards_link.projects_id=projects.id
|
|
AND projects.projectnumber is not null
|
|
AND projects.year=?
|
|
ORDER BY language
|
|
");
|
|
$prjq->execute([$award_id, $config['FAIRYEAR']]);
|
|
$languages = array();
|
|
while ($r = $prjq->fetch(PDO::FETCH_OBJ)) {
|
|
// dont count "" as a language, if the project doesnt have a language specified too bad they're up shit creek without a paddle
|
|
if ($r->langauge) {
|
|
$languages[] = $r->language;
|
|
}
|
|
}
|
|
return $languages;
|
|
} else {
|
|
// return the projects that are eligible for the award instead
|
|
return getLanguagesOfProjectsEligibleForAward($award_id);
|
|
}
|
|
}
|
|
|
|
function getSpecialAwardsNominatedByRegistrationID($id)
|
|
{
|
|
global $config, $pdo;
|
|
|
|
$awardq = $pdo->prepare("SELECT
|
|
award_awards.id,
|
|
award_awards.name,
|
|
award_awards_projectcategories.projectcategories_id,
|
|
award_awards_projectdivisions.projectdivisions_id,
|
|
projects.id AS projects_id
|
|
FROM
|
|
award_awards,
|
|
award_awards_projectcategories,
|
|
award_awards_projectdivisions,
|
|
projects
|
|
WHERE
|
|
award_awards.id=?
|
|
AND award_awards.id=award_awards_projectcategories.award_awards_id
|
|
AND award_awards.id=award_awards_projectdivisions.award_awards_id
|
|
AND projects.projectcategories_id=award_awards_projectcategories.projectcategories_id
|
|
AND projects.projectdivisions_id=award_awards_projectdivisions.projectdivisions_id
|
|
AND projects.projectnumber is not null
|
|
AND projects.year=?
|
|
ORDER BY
|
|
projectsort
|
|
");
|
|
$awardq->execute([$award_id, $config['FAIRYEAR']]);
|
|
$projects = array();
|
|
while ($prjr = $awardq->fetch(PDO::FETCH_OBJ)) {
|
|
$projects[$prjr->projectnumber] = array(
|
|
'id' => $prjr->projects_id,
|
|
'projectnumber' => $prjr->projectnumber,
|
|
'title' => $prjr->title
|
|
);
|
|
}
|
|
return $projects;
|
|
}
|
|
|
|
function project_load($pid)
|
|
{
|
|
global $pdo;
|
|
/* Load this project */
|
|
$q = $pdo->prepare("SELECT * FROM projects WHERE id=?");
|
|
$q->execute([$pid]);
|
|
$proj = $q->fetch();
|
|
|
|
/* Load the students */
|
|
$q = $pdo->prepare("SELECT students.*,schools.school FROM students
|
|
LEFT JOIN schools ON schools.id=students.schools_id
|
|
WHERE registrations_id=? AND students.year=? ORDER BY students.id");
|
|
$q->execute([$proj['registrations_id'], $proj['year']]);
|
|
$proj['num_students'] = 0;
|
|
while ($s = $q->fetch(PDO::FETCH_OBJ)) {
|
|
$proj['num_students']++;
|
|
$proj['student'][] = $s;
|
|
}
|
|
return $proj;
|
|
}
|
|
|
|
?>
|