public static DegreeAuditData degreeAudit(String studentID, String majorID)
{
//SQL Statements for major reqs
String getMajorInfoString = @"SELECT c.course_id,CONCAT(d.department_short_name,c.course_num)AS course_number,c.course_name,c.course_credits
FROM major_requirements mr
INNER JOIN course c ON c.course_id = mr.course_id
INNER JOIN major m ON m.major_id = mr.major_id
INNER JOIN department d ON d.department_id = m.department_id
WHERE m.major_id = " + majorID;
//SQL Statements for electives
String getMajorElectivesString = @"SELECT c.course_id, CONCAT(d.department_short_name, c.course_num) AS 'course_num' ,c.course_name, c.course_credits
FROM [HarborViewUniversity].[dbo].[major_elective] m
JOIN course c ON c.course_id = m.course_id
JOIN department d ON c.department_id = d.department_id
WHERE m.major_id = " + majorID;
//SQL to get classes already taken
String getClassesTakenString = "SELECT DISTINCT s.course_id,sh.grade FROM student_semester_history sh INNER JOIN section s ON s.section_id = sh.section_id INNER JOIN course c ON c.course_id = s.course_id inner join department d on d.department_id = c.department_id INNER join major m on m.department_id = d.department_id where sh.student_id = " + studentID;
//SQL to get out of major classes taken
String getOutOfMajorClassesTaken = "SELECT [course_id], [course_number], [course_name], [prereqs], [course_credits], [grade] FROM out_of_major_reqs_view WHERE student_id = " + studentID + "AND course_id NOT IN(select course_id from major_requirements where major_id = " + majorID + ") AND course_id NOT IN(select course_id from major_elective where major_id = " + majorID + ")";
//SQL to get classes currently being taken
String getClassesInProgressString = "SELECT s.course_id FROM enrollment e INNER JOIN section s ON s.section_id = e.section_id WHERE s.semster = 'spring' AND s.[year] = '2019' AND e.student_id = " + studentID;
//SQL to get prereqs for major courses
String getPrereqsString = "SELECT [course_id] ,[prereq_course_id] ,[prereq_course_name] FROM [HarborViewUniversity].[dbo].[prereq_view] WHERE [major_id] = " + majorID;
//SQL to get requirements for minor
String getMinorReqsString = "SELECT c.course_id,CONCAT(d.department_short_name,c.course_num)AS course_number,c.course_name,c.course_credits FROM minor_requirements mr INNER JOIN course c ON c.course_id = mr.course_id INNER JOIN minor m ON m.minor_id = mr.minor_id INNER JOIN department d ON d.department_id = m.department_id WHERE m.minor_id = (SELECT [minor_id] FROM [student_minor_list] WHERE [student_id] = " + studentID + ")";
//SQL to get prereqs for minor courses
String getMinorPrereqsString = "SELECT[course_id],[prereq_course_id] ,[prereq_course_name] FROM[HarborViewUniversity].[dbo].[prereq_view_minors] WHERE[minor_id] = (SELECT[minor_id] FROM [student_minor_list] WHERE[student_id] = " + studentID + ")";
//Connection String
String cString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnect"].ConnectionString;
//List of major reqs
List <DegreeAuditMajorReqs> majorReqs = new List <DegreeAuditMajorReqs>();
//List of courses taken
Hashtable coursesTaken = new Hashtable();
//Prerequisites for major courses
DataTable majorPrereqTable = new DataTable();
//Prerequisites for minor courses
DataTable minorPrereqTable = new DataTable();
//List of courses currently being taken
List <string> inProgress = new List <string>();
//Lists for major electives
List <DegreeAuditElectives> majorElectives = new List <DegreeAuditElectives>();
//List of out of major classes taken
List <DegreeAuditOutOfMajorReqs> outOfMajorReqs = new List <DegreeAuditOutOfMajorReqs>();
//List of Minor Requirements
List <DegreeAuditMajorReqs> minorReqs = new List <DegreeAuditMajorReqs>();
//Create connect
using (SqlConnection connection = new SqlConnection(cString))
{
//Major Requirements
//Get list of major requirements
SqlCommand command = new SqlCommand(getMajorInfoString, connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
majorReqs.Add(new DegreeAuditMajorReqs(reader.GetInt32(0).ToString(), reader.GetString(1), reader.GetString(2), reader.GetByte(3)));
}
}
//Get classes taken by student
SqlCommand command2 = new SqlCommand(getClassesTakenString, connection);
using (var reader = command2.ExecuteReader())
{
while (reader.Read())
{
coursesTaken.Add(reader.GetInt32(0).ToString(), reader.GetString(1));
}
}
//Get prereqs for major reqs
SqlCommand cmd = new SqlCommand(getPrereqsString, connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(majorPrereqTable);
da.Dispose();
//Major Electives
//get list of major electives
SqlCommand command3 = new SqlCommand(getMajorElectivesString, connection);
using (var reader = command3.ExecuteReader())
{
while (reader.Read())
{
majorElectives.Add(new DegreeAuditElectives(reader.GetInt32(0).ToString(), reader.GetString(1), reader.GetString(2), reader.GetByte(3)));
}
}
//Get classes currently being taken
SqlCommand command4 = new SqlCommand(getClassesInProgressString, connection);
using (var reader = command4.ExecuteReader())
{
while (reader.Read())
{
inProgress.Add(reader.GetInt32(0).ToString());
}
}
//Out of Major Requirements
SqlCommand command5 = new SqlCommand(getOutOfMajorClassesTaken, connection);
using (var reader = command5.ExecuteReader())
{
while (reader.Read())
{
//[course_id], [course_number], [course_name], [prereqs], [course_credits], [grade]
string courseId;
string courseNumber;
string courseName;
string prereqs;
int courseCredits;
string grade;
try { courseId = reader.GetInt32(0).ToString(); } catch { courseId = ""; }
try { courseNumber = reader.GetString(1); } catch { courseNumber = ""; }
try { courseName = reader.GetString(2); } catch { courseName = ""; }
try { prereqs = reader.GetString(3); } catch { prereqs = ""; }
try { courseCredits = reader.GetByte(4); } catch { courseCredits = 0; }
try { grade = reader.GetString(5); } catch { grade = ""; }
outOfMajorReqs.Add(new DegreeAuditOutOfMajorReqs(courseId, courseNumber, courseName, prereqs, courseCredits, grade));
}
}
//Minor Requirements
//Get list of minor requirements
SqlCommand command6 = new SqlCommand(getMinorReqsString, connection);
using (var reader = command6.ExecuteReader())
{
while (reader.Read())
{
minorReqs.Add(new DegreeAuditMajorReqs(reader.GetInt32(0).ToString(), reader.GetString(1), reader.GetString(2), reader.GetByte(3)));
}
}
//Get prereqs for minor reqs
SqlCommand cmd2 = new SqlCommand(getMinorPrereqsString, connection);
SqlDataAdapter da2 = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da2.Fill(minorPrereqTable);
da2.Dispose();
connection.Close();
}
//Major reqs data processing
foreach (var mr in majorReqs)
{
//if course is in progress, mark as in progress
if (inProgress.Contains(mr.courseID))
{
mr.courseStatus = "☐";
}
//if taken course is passed, mark as complete
if (coursesTaken.Contains(mr.courseID))
{
if (GradeList.isPassing((string)coursesTaken[mr.courseID]))
{
mr.courseStatus = "☑";
}
}
//foreach buildingid
//listofroomsforbuildingx.add( datarow dr prereqTable.Select(where buildingid = currentbuilingid))
foreach (DataRow dr in majorPrereqTable.Select("course_id = '" + mr.courseID + "'"))
{
if (coursesTaken.Contains(dr[1] + ""))
{
mr.prereqsTaken += " " + dr[2] + ",";
mr.grade = (string)coursesTaken[mr.courseID];
}
else
{
mr.prereqsToTake += " " + dr[2] + ",";
}
}
if (coursesTaken.Contains(mr.courseID + ""))
{
mr.grade = (string)coursesTaken[mr.courseID];
}
}
//major electives data processing
foreach (var el in majorElectives)
{
//if course is in progress, mark as in progreess
if (inProgress.Contains(el.courseID))
{
el.courseStatus = "☐";
}
//if taken course is passed, mark as complete
if (coursesTaken.Contains(el.courseID))
{
if (GradeList.isPassing((string)coursesTaken[el.courseID]))
{
el.courseStatus = "☑";
el.grade = (string)coursesTaken[el.courseID];
}
}
//sort and add prereqs
foreach (DataRow dr in majorPrereqTable.Select("course_id = '" + el.courseID + "'"))
{
if (coursesTaken.Contains(dr[1] + ""))
{
el.prereqsTaken += " " + dr[2] + ",";
}
else
{
el.prereqsToTake += " " + dr[2] + ",";
}
}
if (coursesTaken.Contains(el.courseID))
{
el.grade = (string)coursesTaken[el.courseID];
}
}
//Out of Major Requirements data processing
foreach (var oom in outOfMajorReqs)
{
if (oom.grade.Equals(""))
{
oom.courseStatus = "☐";
}
else
{
if (GradeList.isPassing(oom.grade))
{
oom.courseStatus = "☑";
}
else
{
oom.courseStatus = "☒";
}
}
}
foreach (var min in minorReqs)
{
//if course is in progress, mark as in progreess
if (inProgress.Contains(min.courseID))
{
min.courseStatus = "☐";
}
//if taken course is passed, mark as complete
if (coursesTaken.Contains(min.courseID))
{
if (GradeList.isPassing((string)coursesTaken[min.courseID]))
{
min.courseStatus = "☑";
min.grade = (string)coursesTaken[min.courseID];
}
}
//sort and add prereqs
foreach (DataRow dr in minorPrereqTable.Select("course_id = '" + min.courseID + "'"))
{
if (coursesTaken.Contains(dr[1] + ""))
{
min.prereqsTaken += " " + dr[2] + ",";
}
else
{
min.prereqsToTake += " " + dr[2] + ",";
}
}
if (coursesTaken.Contains(min.courseID))
{
min.grade = (string)coursesTaken[min.courseID];
}
}
return(new DegreeAuditData(majorReqs, majorElectives, outOfMajorReqs, minorReqs));
}