public ActionResult Create(StudentModel Model)
{
try
{
Query = @"INSERT INTO UPS_Students ( STD_RollNo, STD_Password, STD_FirstName, STD_LastName, STD_CNIC, STD_Address, STD_SCLSchoolCode, STD_PRGPCode, STD_PRGSCode, STD_CrntSemester, STD_PhoneNo, STD_Email, STD_Gender, STD_CCCityCode, STD_CCCntryCode)
VALUES ('" + Model.STD_RollNo + "','" + Model.STD_Password + "','" + Model.STD_FirstName + "','" + Model.STD_LastName + "','" + Model.STD_CNIC + "','" + Model.STD_Address + "','" + Model.STD_SCLSchoolCode + "','" + Model.STD_PRGPCode + "','" + Model.STD_PRGSCode + "','" + Model.STD_CrntSemester + "','" + Model.STD_PhoneNo + "','" + Model.STD_Email + "','" + Model.STD_Gender + "','" + Model.STD_CCCityCode + "','" + Model.STD_CCCntryCode + "')";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["AlertType"] = "success";
ViewData["AlertHeading"] = "Info";
ViewData["AlertData"] = "Data successfully Saved.";
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
ViewData["AlertData"] = ex.Message.ToString();
return(View());
}
}
public ActionResult Create(TeacherModel Model)
{
try
{
Query = @"INSERT INTO UPS_Teachers ( TCHR_ID, TCHR_Password, TCHR_FirstName, TCHR_LastName, TCHR_CNIC, TCHR_Address, TCHR_SCLSchoolCode, TCHR_PRGPCode, TCHR_PRGSCode, TCHR_Post, TCHR_PhoneNo, TCHR_Email, TCHR_Gender, TCHR_CCCityCode, TCHR_CCCntryCode)
VALUES ('" + Model.TCHR_ID + "','" + Model.TCHR_Password + "','" + Model.TCHR_FirstName + "','" + Model.TCHR_LastName + "','" + Model.TCHR_CNIC + "','" + Model.TCHR_Address + "','" + Model.TCHR_SCLSchoolCode + "','" + Model.TCHR_PRGPCode + "','" + Model.TCHR_PRGSCode + "','" + Model.TCHR_Post + "','" + Model.TCHR_PhoneNo + "','" + Model.TCHR_Email + "','" + Model.TCHR_Gender + "','" + Model.TCHR_CCCityCode + "','" + Model.TCHR_CCCntryCode + "'";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["AlertType"] = "success";
ViewData["AlertHeading"] = "Info";
ViewData["AlertData"] = "Data successfully Saved.";
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
ViewData["AlertData"] = ex.Message.ToString();
return(View());
}
}
public ActionResult TCHRAttendance(FormCollection Collection, FilterModel Model)
{
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
Query = @"SELECT STD_RollNo FROM UPS_Students WHERE (STD_PRGPCode = '" + Model.PRG_PCode + "') AND (STD_PRGSCode = '" + Model.PRG_SCode + "') AND (STD_SCLSchoolCode = '" + Model.PRG_SCLSchoolCode + "') ORDER BY STD_RollNo";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
for (int i = 0; i < FormDataTable.Rows.Count; i++)
{
Query = @"INSERT INTO UPS_STDAttendance ( SA_STDRollNo, SA_SubjectName, SA_SCLSchoolCode, SA_PRGPCode, SA_PRGSCode, SA_Date, SA_StartTime, SA_EndTime, SA_Status)
VALUES ('" + FormDataTable.Rows[i]["STD_RollNo"].ToString() + "','" + Model.Subject + "','" + Model.PRG_SCLSchoolCode + "','" + Model.PRG_PCode + "','" + Model.PRG_SCode + "','" + Model.ClassDate.ToString() + "','" + Model.StartTime + "', '" + Model.EndTime + "','" + ((Collection[FormDataTable.Rows[i]["STD_RollNo"].ToString() + "Chk"]) == "on" ? "Y" : "N") + "')";
ReturnValue = DBQueries.DB_ExecuteNonQuery(Query);
}
if (ReturnValue == "Y")
{
ViewData["AlertType"] = "Success";
ViewData["AlertHeading"] = "Info";
ViewData["AlertData"] = "Marks Sheet Saved.";
}
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(RedirectToAction("TCHRAttendance"));
}
public ActionResult TCHRMarkSheet(FormCollection Collection, FilterModel Model)
{
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
Query = @"SELECT STD_RollNo FROM UPS_Students WHERE (STD_PRGPCode = '" + Model.PRG_PCode + "') AND (STD_PRGSCode = '" + Model.PRG_SCode + "') AND (STD_SCLSchoolCode = '" + Model.PRG_SCLSchoolCode + "') ORDER BY STD_RollNo";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
for (int i = 0; i < FormDataTable.Rows.Count; i++)
{
Query = @"INSERT INTO UPS_STDMarks ( SM_STDRollNo, SM_ExamName, SM_SubjectName, STD_SCLSchoolCode, SM_PRGPCode, SM_PRGSCode, SM_Date, SM_TotalMarks, SM_ObtainedMarks)
VALUES ('" + FormDataTable.Rows[i]["STD_RollNo"].ToString() + "','" + Model.ExamType + "','" + Model.Subject + "','" + Model.PRG_SCLSchoolCode + "','" + Model.PRG_PCode + "','" + Model.PRG_SCode + "','" + Model.ClassDate.ToString() + "','" + Model.TotalMarks + "','" + Collection[FormDataTable.Rows[i]["STD_RollNo"].ToString() + "OM"] + "')";
ReturnValue = DBQueries.DB_ExecuteNonQuery(Query);
}
if (ReturnValue == "Y")
{
ViewData["AlertType"] = "Success";
ViewData["AlertHeading"] = "Info";
ViewData["AlertData"] = "Marks Sheet Saved.";
}
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(RedirectToAction("TCHRMarkSheet"));
}
public ActionResult StudentDashboard(string id)
{
DataTable Attendance = new DataTable();
DataTable Marks = new DataTable();
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
Query = @"SELECT STD_RollNo, STD_FirstName, STD_LastName,
(SELECT DISTINCT PRG_ProgramName FROM UPS_Programs WHERE (PRG_PCode = STD_PRGPCode)) AS PRG_ProgramName,
(SELECT DISTINCT PRG_SectionName FROM UPS_Programs WHERE (PRG_PCode = STD_PRGPCode) AND (PRG_SCode = STD_PRGSCode)) AS PRG_SectionName, STD_CrntSemester,
(SELECT DISTINCT SCL_SchoolName FROM UPS_Schools WHERE (SCL_SchoolCode = STD_SCLSchoolCode)) AS SCL_SchoolName,
(SELECT DISTINCT SCL_SchoolAbb FROM UPS_Schools WHERE (SCL_SchoolCode = STD_SCLSchoolCode)) AS SCL_SchoolAbb, STD_PKID
FROM UPS_Students WHERE STD_RollNo = '" + id + "' ORDER BY STD_RollNo";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["StudentData"] = FormDataTable;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
Query = @"SELECT SA_SubjectName, SA_Date, SA_StartTime, SA_EndTime, SA_Status FROM UPS_STDAttendance
WHERE (SA_STDRollNo = '" + id + "') ORDER BY SA_Date";
ReturnValue = DBQueries.DBFilDTable(ref Attendance, Query);
if (ReturnValue == "Y")
{
ViewData["Attendance"] = Attendance;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
Query = @"SELECT SM_ExamName, SM_SubjectName, SM_Date, SM_TotalMarks, SM_ObtainedMarks FROM UPS_STDMarks WHERE (SM_STDRollNo = '" + id + "') ORDER BY SM_Date";
ReturnValue = DBQueries.DBFilDTable(ref Marks, Query);
if (ReturnValue == "Y")
{
ViewData["Marks"] = Marks;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(View());
}
public JsonResult ProgramList(string id)
{
Query = @"SELECT DISTINCT PRG_PCode, PRG_ProgramName FROM UPS_Programs WHERE (PRG_SCLSchoolCode = '" + id + "')";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ReturnValue = JsonConvert.SerializeObject(FormDataTable);
return(Json(ReturnValue, JsonRequestBehavior.AllowGet));
}
return(Json("Data Not Found", JsonRequestBehavior.AllowGet));
}
public JsonResult CountryList(string id)
{
Query = @"SELECT CC_CityCode, CC_CityName FROM UPS_CityCountry WHERE (CC_CntryCode = '" + id + "')";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ReturnValue = JsonConvert.SerializeObject(FormDataTable);
return(Json(ReturnValue, JsonRequestBehavior.AllowGet));
}
return(Json("Data Not Found", JsonRequestBehavior.AllowGet));
}
public ActionResult Create()
{
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
Query = @"SELECT CC_CntryCode, CC_CntryName FROM UPS_CityCountry ORDER BY CC_CntryName";
ReturnValue = DBQueries.DB_ExecuteNonQuery(Query);
if (ReturnValue == "Y")
{
List <SelectListItem> cbCountries = new List <SelectListItem>();
foreach (DataRow dtRow in FormDataTable.Rows)
{
cbCountries.Add(new SelectListItem {
Value = dtRow["CC_CntryCode"].ToString(), Text = dtRow["CC_CntryName"].ToString()
});
}
ViewData["cbCountries"] = cbCountries;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
Query = @"SELECT SCL_SchoolCode, SCL_SchoolName FROM UPS_Schools ORDER BY SCL_SchoolName";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
List <SelectListItem> cbSchools = new List <SelectListItem>();
foreach (DataRow dtRow in FormDataTable.Rows)
{
cbSchools.Add(new SelectListItem {
Value = dtRow["SCL_SchoolCode"].ToString(), Text = dtRow["SCL_SchoolName"].ToString()
});
}
ViewData["cbSchools"] = cbSchools;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(View());
}
public ActionResult AdminLogin(LoginModel LM)
{
if (HttpContext.Request.Cookies["Login"] != null)
{
HttpContext.Response.Cookies.Remove("Login");
}
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
LM.txtUserID = LM.txtUserID.ToLower();
if (ViewData.ModelState.IsValid)
{
Query = "SELECT ADMIN_ID, ADMIN_Password, ADMIN_FirstName, ADMIN_LastName FROM UPS_Admin WHERE (ADMIN_ID = '" + LM.txtUserID + "') AND (ADMIN_Password = '******')";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
var Login = new Dictionary <string, string>()
{
{ "ID", FormDataTable.Rows[0]["ADMIN_ID"].ToString().ToUpper() },
{ "FirstName", FormDataTable.Rows[0]["ADMIN_FirstName"].ToString() },
{ "LastName", FormDataTable.Rows[0]["ADMIN_LastName"].ToString() },
{ "Role", "Admin" }
};
var json = JsonConvert.SerializeObject(Login);
HttpCookie LoginCookie = new HttpCookie("Login", json);
LoginCookie.Expires = DateTime.Now.AddDays(1);
HttpContext.Response.Cookies.Add(LoginCookie);
return(Redirect(Url.Action("Dashboard", "Home")));
}
else
{
ViewData["AlertData"] = ReturnValue;
}
}
else
{
ViewData["AlertData"] = "Email or Password is incorrect.";
}
return(View());
}
public ActionResult Delete(FormCollection collection)
{
Query = @"DELETE FROM UPS_Teachers WHERE (TCHR_PKID = '" + collection["PKID"] + "')";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["AlertType"] = "success";
ViewData["AlertHeading"] = "Info";
ViewData["AlertData"] = "Data successfully Deleted.";
}
else
{
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
ViewData["AlertData"] = ReturnValue;
}
return(RedirectToAction("Index"));
}
public JsonResult STDList(string schoolid, string programid, string id)
{
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
Query = @"SELECT STD_RollNo, STD_FirstName, STD_LastName,
(SELECT DISTINCT PRG_ProgramName FROM UPS_Programs WHERE (PRG_PCode = STD_PRGPCode)) AS PRG_ProgramName,
(SELECT DISTINCT PRG_SectionName FROM UPS_Programs WHERE (PRG_PCode = STD_PRGPCode) AND (PRG_SCode = STD_PRGSCode)) AS PRG_SectionName, STD_CrntSemester,
(SELECT DISTINCT SCL_SchoolName FROM UPS_Schools WHERE (SCL_SchoolCode = STD_SCLSchoolCode)) AS SCL_SchoolName,
(SELECT DISTINCT SCL_SchoolAbb FROM UPS_Schools WHERE (SCL_SchoolCode = STD_SCLSchoolCode)) AS SCL_SchoolAbb, STD_PKID
FROM UPS_Students WHERE (STD_PRGPCode = '" + programid + "') AND (STD_PRGSCode = '" + id + "') AND (STD_SCLSchoolCode = '" + schoolid + "') ORDER BY STD_RollNo";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ReturnValue = JsonConvert.SerializeObject(FormDataTable);
return(Json(ReturnValue, JsonRequestBehavior.AllowGet));
}
return(Json("Data Not Found", JsonRequestBehavior.AllowGet));
}
public ActionResult Index()
{
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
Query = @"SELECT STD_RollNo, STD_FirstName, STD_LastName,
(SELECT DISTINCT PRG_ProgramName FROM UPS_Programs WHERE (PRG_PCode = STD_PRGPCode)) AS PRG_ProgramName,
(SELECT DISTINCT PRG_SectionName FROM UPS_Programs WHERE (PRG_PCode = STD_PRGPCode) AND (PRG_SCode = STD_PRGSCode)) AS PRG_SectionName, STD_CrntSemester,
(SELECT DISTINCT SCL_SchoolName FROM UPS_Schools WHERE (SCL_SchoolCode = STD_SCLSchoolCode)) AS SCL_SchoolName,
(SELECT DISTINCT SCL_SchoolAbb FROM UPS_Schools WHERE (SCL_SchoolCode = STD_SCLSchoolCode)) AS SCL_SchoolAbb, STD_PKID
FROM UPS_Students ORDER BY STD_RollNo";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["Students"] = FormDataTable;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(View());
}
public ActionResult Index()
{
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
Query = @"SELECT TCHR_ID, TCHR_FirstName, TCHR_LastName,
(SELECT DISTINCT PRG_ProgramName FROM UPS_Programs WHERE (PRG_PCode = TCHR_PRGPCode)) AS PRG_ProgramName,
(SELECT DISTINCT PRG_SectionName FROM UPS_Programs WHERE (PRG_PCode = TCHR_PRGPCode) AND (PRG_SCode = TCHR_PRGSCode)) AS PRG_SectionName, TCHR_Post,
(SELECT DISTINCT SCL_SchoolName FROM UPS_Schools WHERE (SCL_SchoolCode = TCHR_SCLSchoolCode)) AS TCHR_SchoolName,
(SELECT DISTINCT SCL_SchoolAbb FROM UPS_Schools WHERE (SCL_SchoolCode = TCHR_SCLSchoolCode)) AS TCHR_SchoolAbb, TCHR_PKID
FROM UPS_Teachers ORDER BY TCHR_ID";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["Teachers"] = FormDataTable;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(View());
}
public ActionResult Edit(int id)
{
StudentModel SM = new StudentModel();
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
Query = @"SELECT CC_CntryCode, CC_CntryName FROM UPS_CityCountry ORDER BY CC_CntryName";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
List <SelectListItem> cbCountries = new List <SelectListItem>();
foreach (DataRow dtRow in FormDataTable.Rows)
{
cbCountries.Add(new SelectListItem {
Value = dtRow["CC_CntryCode"].ToString(), Text = dtRow["CC_CntryName"].ToString()
});
}
ViewData["cbCountries"] = cbCountries;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
Query = @"SELECT SCL_SchoolCode, SCL_SchoolName FROM UPS_Schools ORDER BY SCL_SchoolName";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
List <SelectListItem> cbSchools = new List <SelectListItem>();
foreach (DataRow dtRow in FormDataTable.Rows)
{
cbSchools.Add(new SelectListItem {
Value = dtRow["SCL_SchoolCode"].ToString(), Text = dtRow["SCL_SchoolName"].ToString()
});
}
ViewData["cbSchools"] = cbSchools;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
Query = @"SELECT * FROM UPS_Students WHERE (STD_PKID = '" + id + "')";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
SM.STD_Address = FormDataTable.Rows[0]["STD_Address"].ToString();
SM.STD_CCCityCode = FormDataTable.Rows[0]["STD_CCCityCode"].ToString();
SM.STD_CCCntryCode = FormDataTable.Rows[0]["STD_CCCntryCode"].ToString();
SM.STD_CNIC = FormDataTable.Rows[0]["STD_CNIC"].ToString();
SM.STD_CrntSemester = FormDataTable.Rows[0]["STD_CrntSemester"].ToString();
SM.STD_Email = FormDataTable.Rows[0]["STD_Email"].ToString();
SM.STD_FirstName = FormDataTable.Rows[0]["STD_FirstName"].ToString();
SM.STD_Gender = FormDataTable.Rows[0]["STD_Gender"].ToString();
SM.STD_LastName = FormDataTable.Rows[0]["STD_LastName"].ToString();
SM.STD_Password = FormDataTable.Rows[0]["STD_Password"].ToString();
SM.STD_PhoneNo = FormDataTable.Rows[0]["STD_PhoneNo"].ToString();
SM.STD_PRGPCode = FormDataTable.Rows[0]["STD_PRGPCode"].ToString();
SM.STD_PRGSCode = FormDataTable.Rows[0]["STD_PRGSCode"].ToString();
SM.STD_RollNo = FormDataTable.Rows[0]["STD_RollNo"].ToString();
SM.STD_SCLSchoolCode = FormDataTable.Rows[0]["STD_SCLSchoolCode"].ToString();
SM.PKID = FormDataTable.Rows[0]["STD_PKID"].ToString();
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(View(SM));
}
public ActionResult Dashboard()
{
ViewData["AlertData"] = "";
ViewData["AlertType"] = "danger";
ViewData["AlertHeading"] = "Error";
//Schools
Query = "SELECT * FROM UPS_Admin";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["SchoolsCount"] = FormDataTable.Rows.Count;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
//Programs
Query = "SELECT * FROM UPS_Programs";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["ProgramsCount"] = FormDataTable.Rows.Count;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
//Teachers
Query = "SELECT * FROM UPS_Teachers";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["TeachersCount"] = FormDataTable.Rows.Count;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
//Students
Query = "SELECT * FROM UPS_Students";
ReturnValue = DBQueries.DBFilDTable(ref FormDataTable, Query);
if (ReturnValue == "Y")
{
ViewData["StudentsCount"] = FormDataTable.Rows.Count;
}
else
{
ViewData["AlertData"] = ReturnValue;
}
return(View());
}