<?php


/************************************************************************/


/* Academic Team Learning & Assessment System                           */


/* ==========================================                           */


/* Author: Steven Donovan                                               */


/* Web Developer                                                        */


/* Boonshoft School of Medicine                                         */


/*                                                                     */


/*                                                                      */


/************************************************************************/


if(!defined('TL_ADMIN')) { die("Illegal Access Detected!!!"); }


global $prefix;


include("header.php");


TLAdminMenu();





//Get the academic year


$Get_Academic_Year = $db->sql_query("SELECT Academic_Year FROM ".$prefix."_tl_config");  


while ($info = $db->sql_fetchrow($Get_Academic_Year)) {


      $Academic_Year = $info['Academic_Year']; 


      }


      


// Update table and add Course_Year for students. Doing this so I don't have multiple JOINS just to get Course_Year.


$altertable = $db->sql_query("ALTER TABLE ".$prefix."_tl_session_grade_import ADD Course_Year TINYINT(4) NOT NULL AFTER Subtest_Percent");


if (!$altertable) {echo("<p>Error performing query: " . mysql_error() . "</p>");}





//Get the Course Year for students


$Get_Course_Year = $db->sql_query("SELECT Course_Year FROM ".$prefix."_tl_session a


JOIN ".$prefix."_tl_courses b ON a.Course_ID = b.Course_ID


WHERE a.Session_ID = '$Session_ID'");  


while ($info = $db->sql_fetchrow($Get_Course_Year)) {


      $Course_Year = $info['Course_Year']; 


      }


      


//Update table


$updateClass_Year = $db->sql_query("UPDATE ".$prefix."_tl_session_grade_import SET Course_Year ='$Course_Year'");


if (!$updateClass_Year) {echo("<p>Error performing query: " . mysql_error() . "</p>");}


      


      


$HasError=FALSE; 


$Session_ID = $_POST['Session_ID'];





//Check if IRAT already imported


checkSession($Session_ID);


if ($HasError=TRUE) {


  OpenTable();


echo"<tr><td>IRAT grades for this Session have already been imported.</td></tr>";


echo"<tr><td>You can edit individual IRAT grades <a href='".$admin_file.".php?op=TLViewIratGrades&Session_ID=$Session_ID'>Here!</a></td></tr>";


Closetable();


die();


} elseif {


//Validate UID and compare to Student_ID in the imported table.


checkUID();


OpenTable();


echo"<tr><td>There are errors in the IRAT imported table.  These need to be fixed before writing to the grades table.  Ensure all student UID's match for all current students already in the system.</td></tr>";


echo"<tr><td><input type=\"button\" value=\"Back\" onClick=\"history.go(-1)\"></td></tr>";


Closetable();


die(); 


} elseif {


//Check if all Students are assigned to a group.


checkGroup();


OpenTable();


echo"<tr><td>The following students do not appear to belong to a group.</td></tr>";


echo"<tr><td>Please ensure all students for this class year have been assigned to their TL group.</td></tr>";


echo"<tr><td><input type=\"button\" value=\"Back\" onClick=\"history.go(-1)\"></td></tr>";


Closetable();


OpenTable();


$checkGroup = $db->sql_query("SELECT b.*, a.Name_First, a.Name_Last FROM ".$prefix."_tl_session_grade_import a


LEFT JOIN ".$prefix."_tl_students b ON (b.UID = a.Student_ID)


JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)


WHERE c.SOMS_KEY IS NULL;");


echo "<table width='100%' border='1' cellspacing='0' cellpadding='2'>\n";


echo "<tr><td align='center' bgcolor='$bgcolor2' width='20%'><b>Student Name</b></td></tr>\n";





while ($row = $db->sql_fetchrow($checkGroup)) {


      $Name_First = $row['Name_First']; 


      $Name_Last = $row['Name_Last']; 


      }


 echo "<tr><td align=\"center\">$Name_First $Name_Last </td></tr>";


  echo "</table>\n";


Closetable();        


die(); 


} elseif {


//Check if any students are missing from import.


checkMissingStudents();


OpenTable();


echo "<form action='".$_server['php_self']."' method='post'>";


echo "<input type='hidden' name='op' value='InsertMissingIratGrade'>\n";


echo "<input type='hidden' name='Course_ID' value='$Course_ID'>\n";


echo"<tr><td align=\"center\">There are students in the main student table who have no IRAT grade record from this import.</td></tr>";


echo"<tr><td align=\"center\">It is possible they may have missed the exam, and/or have a valid excuse.</td></tr>";


echo"<tr><td align=\"center\">A zero will be recorded for the following student(s) for this IRAT grade.</td></tr>";


echo"<tr><td align=\"center\">You can edit the individual IRAT grade and place a check if this absence was excused.</td></tr>";


Closetable();


$studentQuery = $db->sql_query("SELECT DISTINCT a.* 


FROM ".$prefix."_tl_students AS a


JOIN ".$prefix."_tl_session_grade_import AS b


WHERE a.Class_Year = b.Course_Year AND a.UID NOT 


IN ( 


SELECT Student_ID


FROM ".$prefix."_tl_session_grade_import)");


echo "<table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\">"


  . " <tr>"


  . "  <td align=\"center\" bgcolor='$bgcolor2' width='10%'>SOM Key #</td>"


  . "  <td align=\"center\" bgcolor='$bgcolor2' width='20%'>First Name</td>"   


  . "  <td align=\"center\" bgcolor='$bgcolor2' width='20%'>Last Name </td>"    


  . "</tr>";      


while($row = $db->sql_fetchrow($studentQuery)) {


$SOMS_KEY = $row['SOMS_KEY'];


$Name_First = $row['Name_First'];


$Name_Last = $row['Name_Last'];


$Session_ID = $row['Session_ID'];


$UID = $row['UID'];





  //<!-- BEGIN Student Row -->"


   echo "<tr bgcolor='$bgcolor1'\">"


  . "  <td align=\"center\">$SOMS_KEY</td>" 


  . "  <td align=\"center\">$Name_First</td>"  


  . "  <td align=\"center\">$Name_Last</td>"   


  . "</tr>";


  }


  //-- END Student Row -->"  


echo"<td colspan=\"3\" align=\"center\"><input type=\"Submit\" name=\"InsertMissingIratGrade\" value=\"Continue\"</td>";  


echo"</form>";


echo"</table>";


die(); 


} else {


//No errors detected so import IRAT grades from _tl_session_grade_import into _tl_session_grades table.


$sql = ("INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Group_ID, IRAT_Grade)


(SELECT a.Session_ID, b.SOMS_KEY, a.Student_ID, c.Group_ID, a.Total_Percent FROM ".$prefix."_tl_session_grade_import a


JOIN ".$prefix."_tl_students b ON (a.Student_ID = b.UID)


JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)


JOIN ".$prefix."_tl_session d


WHERE a.Session_ID = d.Session_ID)");


$result = $db->sql_query($sql);


if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>")die();} 


//Get the academic year and update table


$Get_Academic_Year = $db->sql_query("UPDATE ".$prefix."_tl_session_grades SET Academic_Year = (SELECT Academic_Year FROM ".$prefix."_tl_config)");





//Drop Import table


$sql = "DROP TABLE IF EXISTS ".$prefix."_tl_session_grade_import";


$result = $db->sql_query($sql);   


if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>")die(); }  





//Redirect to header


header("Location: ".$admin_file.".php?op=TLViewIratGrades&Session_ID=$Session_ID");


} 








/*******Funtions*************/





function checkUID() {


//Check valid Students UID's and compare to the imported table


$checkUID = $db->sql_query("SELECT a.UID FROM ".$prefix."_tl_students a


LEFT JOIN ".$prefix."_tl_session_grade_import b ON a.UID = b.Student_ID


WHERE a.UID IS NULL");


$error_total = $db->sql_numrows($checkUID);


//We have unmatched UID's!


if ($error_total > 0) {


$HasError=TRUE;


}else{


$HasError=FALSE;


return();


}


}








function checkSession() {


$checkSession = $db->sql_query("SELECT * FROM ".$prefix."_tl_session_grades WHERE Session_ID = '$Session_ID'");


$Session_total = $db->sql_numrows($checkSession);


if ($Session_total > 0) {


$HasError=TRUE;


}else{


$HasError=FALSE;


return();


}


}








function checkGroup() {


//Check if all Students are assigned to a group.


$checkGroup = $db->sql_query("SELECT b.*, a.Name_First, a.Name_Last FROM ".$prefix."_tl_session_grade_import a


LEFT JOIN ".$prefix."_tl_students b ON (b.UID = a.Student_ID)


JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)


WHERE c.SOMS_KEY IS NULL;");


$group_total = $db->sql_numrows($checkGroup);


if ( $group_total > 0 ) {


$HasError=TRUE;


}else{


$HasError=FALSE;


return();


}


}





function checkMissingStudents(); {


//Check if any students are missing from import.  


$checkMissingStudents = $db->sql_query("SELECT DISTINCT a.* 


FROM ".$prefix."_tl_students AS a


JOIN ".$prefix."_tl_session_grade_import AS b


WHERE a.Class_Year = b.Course_Year AND a.UID NOT 


IN ( 


SELECT Student_ID


FROM ".$prefix."_tl_session_grade_import)");


$missingStudentTotal = $db->sql_numrows($checkMissingStudents);


if ( $missingStudentTotal > 0 ) {


$HasError=TRUE;


}else{


$HasError=FALSE;


return();


}


}





/*******END Funtions*************/


?>