Validating Logic

Donovan · Client Joined: Oct 07, 2003 Posts: 691 Location: Ohio

I have reworked a rather large script to try and implement some logic.

The script had become bloated and hard to follow.

I want to test the following:

Check if already imported
Validate UID and compare to Student_ID in the imported table.
Check if all Students are assigned to a group.
Check if any students are missing from import.

Each part of the script works but I don't think the logic flows. I need all these things to "pass" before I write to the database.

I was thinking about something like this:

Code:

<?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'];

//"
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*************/
?>

sting · Posted: Wed Apr 23, 2008 9:17 am

It's been a while, but did you find what you were looking for to use in this instance?

-sting

Donovan · Client Joined: Oct 07, 2003 Posts: 691 Location: Ohio

sting wrote:

It's been a while, but did you find what you were looking for to use in this instance?

-sting

I still have to go back and address this. I had the database and the website on the same server but now they are on different servers. The import broke and I can no longer use LOAD DATA INFILE.

I had the import going into _tl_session_grade_import

then do a bunch of error checking then into _tl_session_grades

sting · Posted: Thu Apr 24, 2008 1:11 pm

I used to work in higher ed so some of the familiar variables tweaked my interest... lol.

-sting