private string GetSDSQL(string sCollege, string sDegree, string sMajor, int Reason, int Period, string sNo, int iAdvisor, int CancelTerm, InitializeModule.EnumOptions iOption)
{
string sSQL = "";
try
{
string sCondition = GetCondition(sCollege, sDegree, sMajor, Reason, Period, sNo, iAdvisor, CancelTerm, iOption);
sSQL += "SELECT lngSerial, Period, No, Name,Section,sngGrade, ENG, Score, Phone1, Phone2,Status, LTR, CGPA, byteShift AS iPeriod,Advisor,AcceptedTerm,isAccWanted,MESL,RMD";
sSQL += " FROM Web_Student_Data";
sSQL += sCondition;
//sSQL += " AND No='AM08090170'";
sSQL += " ORDER BY Name";
////Selected Creteria REF in Specific Major
//sSQL += "SELECT WD.lngSerial, WD.Period, WD.No, WD.Name, WD.ENG, WD.Score, WD.Phone1, WD.Phone2, WD.Status, WD.LTR, WD.CGPA, WD.byteShift AS iPeriod, WD.Advisor, WD.AcceptedTerm, WD.isAccWanted, WD.MESL, WD.RMD";
//sSQL += " FROM Reg_Applications AS A2 INNER JOIN Reg_Applications AS A1 ON A2.lngStudentNumber = A1.sReference INNER JOIN Web_Student_Data AS WD ON A1.lngStudentNumber = WD.No";
//sSQL += " WHERE (A2.strCollege = N'1') AND (A2.strDegree = N'1') AND (A2.strSpecialization = N'18') AND (A1.strCollege = N'1') AND (A1.strDegree = N'3') AND ";
//sSQL += " (A1.strSpecialization = N'6') AND (A1.byteCancelReason IS NULL)";
//sSQL += " ORDER BY Name";
////Selected Creteria No REF
//sSQL += "SELECT WD.lngSerial, WD.Period, WD.No, WD.Name, WD.ENG, WD.Score, WD.Phone1, WD.Phone2, WD.Status, WD.LTR, WD.CGPA, WD.byteShift AS iPeriod, WD.Advisor,WD.AcceptedTerm, WD.isAccWanted, WD.MESL, WD.RMD";
//sSQL += " FROM Reg_Applications AS A1 INNER JOIN Web_Student_Data AS WD ON A1.lngStudentNumber = WD.No";
//sSQL += " WHERE (A1.strCollege = N'1') AND (A1.strDegree = N'3') AND (A1.strSpecialization = N'5') AND (A1.byteCancelReason IS NULL) AND (A1.sReference IS NULL)";
//sSQL += " ORDER BY Name";
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex.Message);
}
finally
{
}
return(sSQL);
}
public string GetCondition(string sCollege, string sDegree, string sMajor, int Reason, int Period, string sNo, int iAdvisor, int CancelTerm, InitializeModule.EnumOptions iOption)
{
string sCondition = "";
try
{
sCondition = " WHERE strCollege='" + sCollege + "'";
sCondition += " AND strDegree='" + sDegree + "'";
sCondition += " AND strSpecialization='" + sMajor + "'";
if (iAdvisor > 0)
{
sCondition += " AND intAdvisor=" + iAdvisor;
}
if (sNo != "0")
{
sCondition += " AND sStudentNumber='" + sNo + "'";
}
else
{
if (Reason > 0)
{
// (byteCancelReason is null OR
sCondition += " AND byteCancelReason=" + Reason;// + "";
if (CancelTerm > 0)
{
sCondition += " AND CancelTerm ";
string sOption = LibraryMOD.GetOption(iOption);
sCondition += sOption + CancelTerm;
}
}
else
{
switch (Reason)
{
case 0: //No Reason
sCondition += " AND byteCancelReason is null";
break;
case -1: //Not Graduated
sCondition += " AND (byteCancelReason <>3 or byteCancelReason is null)";
break;
}
}
if (Period > 0)
{
sCondition += " AND byteShift=" + Period;
}
}
//if (CGPA > 0)
//{
// sCondition += " AND CGPA ";
// string sOperator = LibraryMOD.GetOperator(iOperator);
//}
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex.Message);
}
finally
{
}
return(sCondition);
}
private string GetExpectedMirrorSQL(string sCollege, string sDegree, string sMajor, int Reason, int Period, string sNo, int CancelTerm, InitializeModule.EnumOptions iOption)
{
string sSQL = "";
try
{
string sCondition = GetCondition(sCollege, sDegree, sMajor, Reason, Period, sNo, 0, CancelTerm, iOption);
sSQL += "SELECT lngSerial,sStudentNumber,sCourse,sGrade,bPassIt,0 AS Regterm,byteOrder,CONVERT (int, 1, 102) as Priority";
sSQL += " FROM Web_Max_Grades";
sSQL += sCondition;
// sSQL += " AND (sStudentNumber = 'AF07090070')";
//sSQL += " union all";
//sSQL += " SELECT lngSerial,sStudentNumber,sCourse,sGrade,bPassIt,byteOrder,CONVERT (int, 2, 102) as Priority";
//sSQL += " FROM Web_Max_Ellective";
//sSQL += sCondition;
//sSQL += " AND (sStudentNumber = 'AF07090070')";
sSQL += " union all";
sSQL += " SELECT lngSerial,sStudentNumber,sCourse,sGrade";
sSQL += " ,CONVERT (bit, 1, 102)";
sSQL += " ,Regterm,byteOrder,CONVERT (int, 3, 102) as Priority";
sSQL += " FROM Web_Registered_No_Grades";
sSQL += sCondition;
//sSQL += " AND (sStudentNumber = 'AF07090070')";
sSQL += " ORDER BY lngSerial,byteOrder,Priority";
//sSQL += " SELECT lngSerial,sStudentNumber,sCourse,sGrade,bPassIt,byteOrder";
//sSQL += " FROM Web_Mirror_Data_Min";
//sSQL += sCondition;
////sSQL += " AND (sStudentNumber = 'am08020034')";
//sSQL += " ORDER BY lngSerial,byteOrder";
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex.Message);
}
finally
{
}
return(sSQL);
}
private string GetProgramMirrorSQL(string sCollege, string sDegree, string sMajor, int Reason, int Period, string sNo, int iAdvisor, int CancelTerm, InitializeModule.EnumOptions iOption, bool isRegisteredPassed)
{
string sSQL = "";
try
{
string sCondition = GetCondition(sCollege, sDegree, sMajor, Reason, Period, sNo, iAdvisor, CancelTerm, iOption);
sSQL += "SELECT lngSerial,sStudentNumber,sCourse,sGrade,bPassIt,0 AS Regterm,byteOrder,CONVERT (int, 1, 102) as Priority";
sSQL += " FROM Web_Max_Grades";
sSQL += sCondition;
//sSQL += " AND (sStudentNumber = 'AM08090101')";
//sSQL += " union all";
//sSQL += " SELECT lngSerial,sStudentNumber,sCourse,sGrade,bPassIt,byteOrder,CONVERT (int, 2, 102) as Priority";
//sSQL += " FROM Web_Max_Ellective";
//sSQL += sCondition;
//sSQL += " AND (sStudentNumber = 'AM08090101')";
sSQL += " union all";
sSQL += " SELECT lngSerial,sStudentNumber,sCourse,sGrade";
if (isRegisteredPassed)
{
sSQL += " ,CONVERT (bit, 1, 102)";
}
else
{
sSQL += " ,CONVERT (bit, 0, 102)";
}
sSQL += " ,Regterm,byteOrder,CONVERT (int, 3, 102) as Priority";
sSQL += " FROM Web_Registered_No_Grades";
sSQL += sCondition;
//sSQL += " AND RegTerm < " + LibraryMOD.GetRegTerm();
//sSQL += " AND (sStudentNumber = 'AM08090101')";
sSQL += " ORDER BY lngSerial,byteOrder,Priority";
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex.Message);
}
finally
{
}
return(sSQL);
}
private string GetMirrorSQL(MirrorsType itype, string sCollege, string sDegree, string sMajor, int Reason, int Period, string sNo, int iAdvisor, int CancelTerm, InitializeModule.EnumOptions iOption, bool isRegisteredPassed)
{
string sSQL = "";
try
{
switch (itype)
{
case MirrorsType.ProgramMirror:
sSQL = GetProgramMirrorSQL(sCollege, sDegree, sMajor, Reason, Period, sNo, iAdvisor, CancelTerm, iOption, isRegisteredPassed);
break;
case MirrorsType.ExpectedMirror:
sSQL = GetExpectedMirrorSQL(sCollege, sDegree, sMajor, Reason, Period, sNo, CancelTerm, iOption);
break;
}
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex.Message);
}
finally
{
}
return(sSQL);
}
public List <MirrorCLS> GetMirrorWithSD(string sCollege, string sDegree, string sMajor, int Reason, int Period, string sNo, int iAdvisor, int CancelTerm, InitializeModule.EnumOptions iOption, MirrorsType iType, InitializeModule.EnumCampus Campus, out int iHours, bool iRegisteredPassed)
{
List <MirrorCLS> Result = new List <MirrorCLS>();
List <Plans> MyPlans = new List <Plans>();
Plans myPlan = new Plans();
PlansDAL myPlansDAL = new PlansDAL();
Connection_StringCLS myConnection_String = new Connection_StringCLS(Campus);
SqlConnection Conn = new SqlConnection(myConnection_String.Conn_string);
Conn.Open();
iHours = 0;
try
{
MyPlans = myPlansDAL.GetPlans(sCollege, sDegree, sMajor, false, Campus);
myPlan = MyPlans[0];
int iPlan = myPlan.Courses.Count;
Result = GetStudentData(sCollege, sDegree, sMajor, Reason, Period, sNo, iAdvisor, CancelTerm, myPlan, iOption, Conn, Campus);
iHours = myPlan.StudyHours;
if (Result.Count > 0)
{
//string sSQL = "select * from GetGrades('" + sCollege + "','" + sDegree + "','" + sMajor + "'," + Reason + "," + Period + ",'" + sNo + "')";
//sSQL += " union all";
//sSQL += " select * from GetRegistered('" + sCollege + "','" + sDegree + "','" + sMajor + "'," + Reason + "," + Period + ",'" + sNo + "')";
//sSQL += " union all";
//sSQL += " select * from GetEllective('" + sCollege + "','" + sDegree + "','" + sMajor + "'," + Reason + "," + Period + ",'" + sNo + "')";
//sSQL += " order by lngSerial,byteOrder";
string sSQL = GetMirrorSQL(iType, sCollege, sDegree, sMajor, Reason, Period, sNo, iAdvisor, CancelTerm, iOption, iRegisteredPassed);
SqlCommand Cmd = new SqlCommand(sSQL, Conn);
SqlDataReader Rd = Cmd.ExecuteReader();
int iIndex = 0;
int iCrsIndex = 0;
string sSno = "";
int iCourseregTerm = 0;
int iRegTerm = LibraryMOD.GetRegTerm();
while (Rd.Read())
{
if (sSno != Rd["sStudentNumber"].ToString())
{
sSno = Rd["sStudentNumber"].ToString();
iIndex = Result.FindIndex(delegate(MirrorCLS M) { return(M.StudentNumber == sSno); });
if (iIndex >= 0)
{
//Result[iIndex].Mirror = new StudentCourses.StudentMirror[iPlan];
Result[iIndex].Passed = 0;
Result[iIndex].Hours = iHours;
for (int i = 0; i < iPlan; i++)
{
Result[iIndex].Mirror[i].iClass = myPlan.Courses[i].iClass;
}
}
}
if (iIndex >= 0)
{
iCrsIndex = int.Parse(Rd["byteOrder"].ToString()) - 1;
//Result[iIndex].Mirror[iCrsIndex].sCourse = Rd["sCourse"].ToString
//Student has Fainance Problem
Result[iIndex].Mirror[iCrsIndex].sGrade = Rd["sGrade"].ToString();
iCourseregTerm = int.Parse(Rd["RegTerm"].ToString());
if (Rd["bPassIt"].Equals(DBNull.Value))
{
Result[iIndex].Mirror[iCrsIndex].isPassed = false;
}
else
{
if (iCourseregTerm >= iRegTerm)
{
Result[iIndex].Mirror[iCrsIndex].isPassed = false;
}
else
{
Result[iIndex].Mirror[iCrsIndex].isPassed = bool.Parse(Rd["bPassIt"].ToString());
}
}
//if (Result[iIndex].IsAccWanted && Result[iIndex].Mirror[iCrsIndex].isPassed)
//{
// Result[iIndex].Mirror[iCrsIndex].sGrade = "*";
//}
}
}
Rd.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex.Message);
}
finally
{
MyPlans.Clear();
Conn.Close();
Conn.Dispose();
}
return(Result);
}
private List <MirrorCLS> GetStudentData(string sCollege, string sDegree, string sMajor, int Reason, int Period, string sNo, int iAdvisor, int CancelTerm, Plans Plan, InitializeModule.EnumOptions iOption, SqlConnection Conn, InitializeModule.EnumCampus Campus)
{
List <MirrorCLS> Result = new List <MirrorCLS>();
try
{
string sSQL = GetSDSQL(sCollege, sDegree, sMajor, Reason, Period, sNo, iAdvisor, CancelTerm, iOption);
//List<Plans> Plan = new List<Plans>();
//PlansDAL myPlansDAL = new PlansDAL();
//Plan = myPlansDAL.GetPlans(sCollege, sDegree, sMajor, false, Campus);
//int iPlan = Plan[0].Courses.Count;
SqlCommand Cmd = new SqlCommand(sSQL, Conn);
SqlDataReader Rd = Cmd.ExecuteReader();
MirrorCLS myMirror;
int iSerial = 0;
while (Rd.Read())
{
iSerial += 1;
myMirror = new MirrorCLS();
myMirror.Serial = iSerial;
myMirror.StudentNumber = Rd["No"].ToString();
myMirror.Name = Rd["Name"].ToString();
myMirror.IPeriod = int.Parse(Rd["iPeriod"].ToString());
myMirror.Period = Rd["Period"].ToString();
myMirror.ENG = Rd["ENG"].ToString();
myMirror.Advisor = Rd["Advisor"].ToString();
myMirror.SSection = Rd["Section"].ToString();
if (!Rd["sngGrade"].Equals(DBNull.Value))
{
myMirror.SGPA = decimal.Parse(Rd["sngGrade"].ToString());
}
if (!Rd["Score"].Equals(DBNull.Value))
{
myMirror.Score = decimal.Parse(Rd["Score"].ToString());
}
if (!Rd["CGPA"].Equals(DBNull.Value))
{
myMirror.CGPA = decimal.Parse(Rd["CGPA"].ToString());
}
else
{
myMirror.CGPA = 101;
}
if (!Rd["LTR"].Equals(DBNull.Value))
{
myMirror.LTR = int.Parse(Rd["LTR"].ToString());
}
else
{
myMirror.LTR = 0;
}
myMirror.Phone1 = Rd["Phone1"].ToString();
myMirror.Phone2 = Rd["Phone2"].ToString();
myMirror.Status = Rd["Status"].ToString();
myMirror.IsENGRequired = (int.Parse(Rd["AcceptedTerm"].ToString()) >= 20062);
myMirror.IsAccWanted = bool.Parse(Rd["isAccWanted"].ToString());
if (!Rd["MESL"].Equals(DBNull.Value))
{
myMirror.MaxESL = int.Parse(Rd["MESL"].ToString());
}
else
{
myMirror.MaxESL = 0;
}
if (!Rd["RMD"].Equals(DBNull.Value))
{
myMirror.Remind = int.Parse(Rd["RMD"].ToString());
}
myMirror.Mirror = new StudentCourses.StudentMirror[Plan.Courses.Count];
for (int i = 0; i < Plan.Courses.Count; i++)
{
myMirror.Mirror[i].sCourse = Plan.Courses[i].sCourse;
myMirror.Mirror[i].iCredit = Plan.Courses[i].iCredit;
myMirror.Mirror[i].sGrade = ".";
myMirror.Mirror[i].isPassed = false;
myMirror.Mirror[i].RegTerm = 0;
}
Result.Add(myMirror);
}
//Add the last
Rd.Close();
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex.Message);
}
finally
{
}
return(Result);
}
