protected UserAccount(string username)
{
if (username == null)
{
throw new AccessViolationException(ErrorStringNoLogin);
}
LoginRole = (LoginRole)(int)(decimal)DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"Login_Role_ID", LoginTable, "Username = :username"),
new CommandParameter(":username", username)).Rows[0][0];
LoginID = (int)(decimal)DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"ID", LoginTable, "Username = :username"),
new CommandParameter(":username", username)).Rows[0][0];
ID = (int)(decimal)DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"ID", LoginRole.ToString(), "Login_ID = :LoginID"),
new CommandParameter(":LoginID", LoginID)).Rows[0][0];
Name = (string)DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"Name", LoginRole.ToString(), "ID = :ID"),
new CommandParameter(":ID", ID)).Rows[0][0];
}
/// <summary>
/// Get headmaster name if assigned, otherwise "N/A"
/// </summary>
/// <returns>headmaster name if assigned, otherwise "N/A"</returns>
public string GetHeadmasterName()
{
DataTable dataTable = DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"Headmaster_ID", LoginRole.ToString(), "ID = :ID"),
new CommandParameter(":ID", ID));
if (!dataTable.Rows[0].IsNull(0))
{
int headmasterID = (int)(decimal)dataTable.Rows[0][0];
return((string)DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"Name", "Teacher", "ID = :headmasterID"),
new CommandParameter(":headmasterID", headmasterID)).Rows[0][0]);
}
return(EmptyField);
}
/// <summary>
/// Get exam result info of an exam ID or all exams, a specific or all courses,
/// upcoming exams or all exams, a specific or all classes, in current school or all schools
/// </summary>
/// <param name="examID">0 for all exams, or exam ID of the selected exam</param>
/// <param name="courseName">empty string for all exams, or course name of the selected exams</param>
/// <param name="inCurrentClass">true for current class, or false for all or the selected class</param>
/// <param name="className">(if 'inCurrentClass' false) empty string for all classes, or name of the selected class</param>
/// <param name="inCurrentSchool">true for current school, or false for all schools</param>
/// <returns>
/// data of 'DataTable' type containing exam ID, course name, course code, class name, school name, date, time,
/// total marks, obtained marks
/// </returns>
public DataTable GetExamResultInfo(int examID = 0, string courseName = "", bool inCurrentClass = true,
string className = "", bool inCurrentSchool = true)
{
string selectClause = "Exam_ID AS \"Exam ID\"";
string whereClause = "Student_ID = :ID";
List <CommandParameter> commandParameters = new List <CommandParameter>();
commandParameters.Add(new CommandParameter(":ID", ID));
if (examID != 0)
{
whereClause += " AND Exam_ID = :examID";
commandParameters.Add(new CommandParameter(":examID", examID));
}
if (courseName != "")
{
whereClause += " AND Course_Name = :courseName";
commandParameters.Add(new CommandParameter(":courseName", courseName));
}
else
{
selectClause += ", Course_Name AS \"Course Name\"";
}
selectClause += ", Course_Code AS \"Course Code\"";
whereClause += " AND to_char(Date_Time, 'yyyy/MM/dd') <= to_char(sysdate, 'yyyy/MM/dd')";
if (inCurrentClass)
{
DataTable dataTableClassID = DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"Class_ID", LoginRole.ToString(), "ID = :ID"),
new CommandParameter(":ID", ID));
int classID = 0;
if (!dataTableClassID.Rows[0].IsNull(0))
{
classID = (int)(decimal)dataTableClassID.Rows[0][0];
}
whereClause += " AND Class_ID = :classID";
commandParameters.Add(new CommandParameter(":classID", classID));
}
else if (className != "")
{
whereClause += " AND Class_Name = :className";
commandParameters.Add(new CommandParameter(":className", className));
}
else
{
selectClause += ", Class_Name AS \"Class Name\"";
}
if (inCurrentSchool)
{
DataTable dataTableSchoolID = DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"School_ID", LoginRole.ToString(), "ID = :ID"),
new CommandParameter(":ID", ID));
int schoolID = 0;
if (!dataTableSchoolID.Rows[0].IsNull(0))
{
schoolID = (int)(decimal)dataTableSchoolID.Rows[0][0];
}
whereClause += " AND School_ID = :schoolID";
commandParameters.Add(new CommandParameter(":schoolID", schoolID));
}
else
{
selectClause += ", School_Name AS \"School Name\"";
}
selectClause += ", (to_char(Date_Time, :date_format)) AS \"Date\", " +
"(to_char(Date_Time, :time_format)) AS \"Time\", " +
"Total_Marks AS \"Total Marks\", Obtained_Marks AS \"Obtained Marks\"";
commandParameters.Insert(0, new CommandParameter(":date_format", OutputDateFormat));
commandParameters.Insert(1, new CommandParameter(":time_format", OutputTimeFormat));
DataTable dataTable = DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
selectClause, ExamResultTableName, whereClause,
orderByClause: "Course_Code, (to_char(Date_Time, 'yyyy/MM/dd'))"),
commandParameters.ToArray());
return(dataTable);
}
/// <summary>
/// Get enrolled course info of a specific or all classes, in current school or all schools
/// </summary>
/// <param name="courseName">empty string for all courses, or name of the selected course</param>
/// <param name="inCurrentClass">true for current class, or false for all or the selected class</param>
/// <param name="className">(if 'inCurrentClass' false) empty string for all classes, or name of the selected class</param>
/// <param name="inCurrentSchool">true for current school, or false for all schools</param>
/// <returns>data of 'DataTable' type containing course name, course code, class name, school name, enrolled date</returns>
public DataTable GetEnrolledCourseInfo(string courseName = "", bool inCurrentClass = true, string className = "",
bool inCurrentSchool = true)
{
string selectClause = "";
string whereClause = "Student_ID = :ID";
List <CommandParameter> commandParameters = new List <CommandParameter>();
commandParameters.Add(new CommandParameter(":ID", ID));
if (courseName != "")
{
whereClause += " AND Course_Name = :courseName";
commandParameters.Add(new CommandParameter(":courseName", courseName));
}
else
{
selectClause = "Course_Name AS \"Course Name\", ";
}
selectClause += "Course_Code AS \"Course Code\"";
if (inCurrentClass)
{
DataTable dataTableClassID = DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"Class_ID", LoginRole.ToString(), "ID = :ID"),
new CommandParameter(":ID", ID));
int classID = 0;
if (!dataTableClassID.Rows[0].IsNull(0))
{
classID = (int)(decimal)dataTableClassID.Rows[0][0];
}
whereClause += " AND Class_ID = :classID";
commandParameters.Add(new CommandParameter(":classID", classID));
}
else if (className != "")
{
whereClause += " AND Class_Name = :className";
commandParameters.Add(new CommandParameter(":className", className));
}
else
{
selectClause += ", Class_Name AS \"Class Name\"";
}
if (inCurrentSchool)
{
DataTable dataTableSchoolID = DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
"School_ID", LoginRole.ToString(), "ID = :ID"),
new CommandParameter(":ID", ID));
int schoolID = 0;
if (!dataTableSchoolID.Rows[0].IsNull(0))
{
schoolID = (int)(decimal)dataTableSchoolID.Rows[0][0];
}
whereClause += " AND School_ID = :schoolID";
commandParameters.Add(new CommandParameter(":schoolID", schoolID));
}
else
{
selectClause += ", School_Name AS \"School Name\"";
}
selectClause += ", (to_char(Enroll_Date, :date_format)) AS \"Enroll Date\"";
commandParameters.Insert(0, new CommandParameter(":date_format", OutputDateFormat));
DataTable dataTable = DataAccessLayer.SelectCommand(DataAccessLayer.SelectCommandString(
selectClause, EnrollCourseTableName, whereClause,
orderByClause: "Course_Code, (to_char(Enroll_Date, 'yyyy/MM/dd'))"),
commandParameters.ToArray());
return(dataTable);
}