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); }