public ActionResult ViewStudent(SearchStudent searchStudent)
{
if (ModelState.IsValid)
{
CardDAO Card_DAO = new CardDAO();
if (Card_DAO.ISExsistCardDate(searchStudent.CardNumber))
{
RegestrationDAO Registrator = new RegestrationDAO();
if (Registrator.IsValidCardInDataBase(searchStudent.CardNumber))
{
StudentDAO Student_DAO = new StudentDAO();
return(View(Student_DAO.GetStudenInfo(searchStudent.CardNumber)));
}
else
{
ModelState.AddModelError("", "The Card Number is not sold yet,so there is no student registerd with this card number");
return(View("SearchStudent"));
}
}
else
{
ModelState.AddModelError("", "There is no card with this card number.Please give a valid card number");
return(View("SearchStudent"));
}
}
else
{
ModelState.AddModelError("", "The Card Number is invalid");
return(View("SearchStudent"));
}
}
private void searchtxt_TextChanged(object sender, EventArgs e)
{
var p = this.searchtxt.Text;
SearchStudent?.Invoke(this, new TextEventArgs(p));
}
public void OnPost()
{
// PERFORM SEARCH
if (string.IsNullOrWhiteSpace(SearchStudent))
{
// EXIT EARLY IF THERE IS NO SEARCH TERM PROVIDED
return;
}
SearchResults = _context.Educations
.Include(x => x.Profile)
.Where(x => x.UniversityName.ToLower().Contains(SearchStudent.ToLower()))
.ToList();
SearchCompleted = true;
}
public ActionResult AddCard(SearchStudent studentCardNumber)
{
if (ModelState.IsValid)
{
if (Session["StudentId"] != null)
{
CardDAO Card_DAO = new CardDAO();
if (Card_DAO.ISExsistCardDate(studentCardNumber.CardNumber))
{
RegestrationDAO Registrator = new RegestrationDAO();
if (Registrator.IsCardUnsoldAndUnRegisteredWhileRegitered(studentCardNumber.CardNumber))
{
int StudentId = (Int32)Session["StudentId"];
Registrator.UpdateCardsTableWithStudentId(studentCardNumber.CardNumber, StudentId);
ModelState.AddModelError("", "The New Card is added successfully .Please check student information by searching.");
return(View("SearchStudent"));
}
else
{
ModelState.AddModelError("", "The Card is already sold,Please give an unsold card number.");
return(View("SearchStudent"));
}
}
else
{
ModelState.AddModelError("", "There is no card with this card number.Please give a valid card number.");
return(View("SearchStudent"));
}
}
else
{
ModelState.AddModelError("", "Sorry Student information is not found.");
return(View("SearchStudent"));
}
}
else
{
ModelState.AddModelError("", "Sorry Student information is not found.");
return(View("SearchStudent"));
}
}
public ActionResult RefreshParticularCard(SearchStudent active_CardNumber)
{
if (ModelState.IsValid)
{
CardDAO Card_DAO = new CardDAO();
if (Card_DAO.ISExsistCardDate(active_CardNumber.CardNumber))
{
RegestrationDAO Registrator = new RegestrationDAO();
if (Registrator.IsValidCardInDataBase(active_CardNumber.CardNumber))
{
ActiveUserDAO ActiveUser_DAO = new ActiveUserDAO();
if (ActiveUser_DAO.IsActive(active_CardNumber.CardNumber))
{
ActiveUser_DAO.RefreshActiveUserListForParticularCard(active_CardNumber.CardNumber);
return(View());
}
else
{
ModelState.AddModelError("", "The Card is not active yet .So there is no reason for removing it from the active user list");
return(View("ShowParticularCard"));
}
}
else
{
ModelState.AddModelError("", "The Card Number is not sold yet,so there is no student registerd with this card number");
return(View("ShowParticularCard"));
}
}
else
{
ModelState.AddModelError("", "There is no card with this card number.Please give a valid card number");
return(View("ShowParticularCard"));
}
}
else
{
ModelState.AddModelError("", "Active user not found");
return(View("ShowParticularCard"));
}
}
// GET: Students
public async Task <IActionResult> Index(string studentIndex, string searchString)
{
IQueryable <Student> students = _context.Student.AsQueryable();
IQueryable <string> indexQuery = _context.Student.OrderBy(m => m.StudentId).Select(m => m.StudentId).Distinct();
if (!string.IsNullOrEmpty(searchString))
{
students = students.Where(s => (s.FirstName + " " + s.LastName).Contains(searchString));
}
if (!string.IsNullOrEmpty(studentIndex))
{
students = students.Where(x => x.StudentId == studentIndex);
}
var studentIndexVM = new SearchStudent
{
Index = new SelectList(await indexQuery.ToListAsync()),
Students = await students.ToListAsync()
};
return(View(studentIndexVM));
}
public ActionResult ViewCardUsage(SearchStudent searchStudent)
{
if (ModelState.IsValid)
{
CardDAO Card_DAO = new CardDAO();
if (Card_DAO.ISExsistCardDate(searchStudent.CardNumber))
{
RegestrationDAO Registrator = new RegestrationDAO();
if (Registrator.IsValidCardInDataBase(searchStudent.CardNumber))
{
ParticularCardUsageDAO CardUsage_DAO = new ParticularCardUsageDAO();
List <History> CardUsageList = CardUsage_DAO.ShowParticularCardHisroty(searchStudent.CardNumber).ToList();
GridView gridView = new GridView();
gridView.DataSource = CardUsageList;
gridView.DataBind();
Session["CardUsage"] = gridView;
return(View(CardUsageList));
}
else
{
ModelState.AddModelError("", "The Card with this card number is not sold yet.So there is no student registerd with this card number.");
return(View("ShowCardUsage"));
}
}
else
{
ModelState.AddModelError("", "There is no card with this card number.Please give a valid card number.");
return(View("ShowCardUsage"));
}
}
else
{
ModelState.AddModelError("", "The Card Number is invalid.");
return(View("ShowCardUsage"));
}
}
public static SearchStudent SearchStudent(int? count)
{
var rtValue = new SearchStudent();
rtValue.Id = "Id" + count.Extra();
rtValue.Pidm = "Pidm" + count.Extra();
rtValue.FirstName = "FirstName" + count.Extra();
rtValue.MI = "MI" + count.Extra();
rtValue.LastName = "LastName" + count.Extra();
rtValue.HoursEarned = 100m;
rtValue.Email = "Email" + count.Extra();
rtValue.MajorCode = "MajorCode" + count.Extra();
rtValue.CollegeCode = "CollegeCode" + count.Extra();
rtValue.LoginId = "LoginId" + count.Extra();
rtValue.Astd = "Astd" + count.Extra();
return rtValue;
}
private string SearchStudentTrackedConditionReturn(SearchStudent SearchStructure)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (SearchStructure.txtstudentID != null)
{
ConditionReturn += " AND StudentTrackedOffData.StudentID=(@StudentID) ";
}
if (SearchStructure.txtstudentName != null)
{
ConditionReturn += " AND StudentName like (@StudentName) ";
}
if (SearchStructure.txtstudentSex != null && SearchStructure.txtstudentSex != "0")
{
ConditionReturn += " AND StudentSex=(@StudentSex) ";
}
if (SearchStructure.txtbirthdaystart != null && SearchStructure.txtbirthdayend != null && SearchStructure.txtbirthdaystart != DateBase && SearchStructure.txtbirthdayend != DateBase)
{
ConditionReturn += " AND StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) ";
}
if (SearchStructure.txtendReasonDatestart != null && SearchStructure.txtendReasonDateend != null && SearchStructure.txtendReasonDatestart != DateBase && SearchStructure.txtendReasonDateend != DateBase)
{
ConditionReturn += " AND CompletedDate BETWEEN (@sCompletedDateStart) AND (@sCompletedDateEnd) ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND StudentTrackedOffData.Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public List<SearchAchievementAssessment> SearchCaseStudy(int index, SearchStudent SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
return SDB.SearchCaseStudy(index, SearchStructure, 0);
}
public string[] SearchStudentTrackedCount(SearchStudent SearchStructure)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentTrackedConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM StudentTrackedOffData INNER JOIN StudentDatabase ON StudentTrackedOffData.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
"WHERE StudentTrackedOffData.isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = SearchStructure.txtstudentName + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@sCompletedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDatestart);
cmd.Parameters.Add("@sCompletedDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDateend);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
private string SearchStudentConditionReturn(SearchStudent SearchStructure, int type)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (SearchStructure.txtstudentID != null)
{
ConditionReturn += " AND StudentID=(@StudentID) ";
}
if (SearchStructure.txtstudentName != null)
{
ConditionReturn += " AND StudentName like (@StudentName) ";
}
if (SearchStructure.txtstudentSex != null && SearchStructure.txtstudentSex != "0")
{
ConditionReturn += " AND StudentSex=(@StudentSex) ";
}
if (SearchStructure.txtbirthdaystart != null && SearchStructure.txtbirthdayend != null && SearchStructure.txtbirthdaystart != DateBase && SearchStructure.txtbirthdayend != DateBase)
{
ConditionReturn += " AND StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) ";
}
if (SearchStructure.txtcaseStatu != null && SearchStructure.txtcaseStatu != "0")
{
ConditionReturn += " AND CaseStatu =(@CaseStatu) ";
}
if (SearchStructure.txtjoindaystart != null && SearchStructure.txtjoindayend != null && SearchStructure.txtjoindaystart != DateBase && SearchStructure.txtjoindayend != DateBase)
{
ConditionReturn += " AND ClassDate BETWEEN (@sGuaranteeDateStart) AND (@sGuaranteeDateEnd) ";
}
if (SearchStructure.txtendReasonDatestart != null && SearchStructure.txtendReasonDateend != null && SearchStructure.txtendReasonDatestart != DateBase && SearchStructure.txtendReasonDateend != DateBase)
{
ConditionReturn += " AND CompletedDate BETWEEN (@sCompletedDateStart) AND (@sCompletedDateEnd) ";
}
if (SearchStructure.txtendReasonType != null && SearchStructure.txtendReasonType != "0")
{
ConditionReturn += " AND CompletedType =(@CompletedType) ";
}
if (SearchStructure.txtnomembershipType != null && SearchStructure.txtnomembershipType != "0")
{
ConditionReturn += " AND NomembershipType =(@NomembershipType) ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND Unit =" + UserFile[2] + " ";
}
if (type == 0)
{
ConditionReturn += " AND CaseStatu2 =" + type + " ";
}
return ConditionReturn;
}
public string[] SearchStudentTrackedDataBaseCount(SearchStudent SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
return SDB.SearchStudentTrackedCount(SearchStructure);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<SearchStudentTrackResult> SearchStudentTracked(int indexpage, SearchStudent SearchStructure)
{
List<SearchStudentTrackResult> returnValue = new List<SearchStudentTrackResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentTrackedConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY StudentTrackedOffData.ID DESC) " +
"AS RowNum, StudentDatabase.StudentName ,StudentDatabase.CompletedType, StudentDatabase.CompletedDate, StudentTrackedOffData.* " +
"FROM StudentTrackedOffData INNER JOIN StudentDatabase ON StudentTrackedOffData.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
"WHERE StudentTrackedOffData.isDeleted=0 " + ConditionReturn + " ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = SearchStructure.txtstudentName + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@sCompletedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDatestart);
cmd.Parameters.Add("@sCompletedDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDateend);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchStudentTrackResult addValue = new SearchStudentTrackResult();
addValue.ID = Int64.Parse(dr["ID"].ToString());
addValue.txtstudentID = dr["StudentID"].ToString();
addValue.txtstudentName = dr["StudentName"].ToString();
addValue.txtendReasonDateType = dr["CompletedType"].ToString();
addValue.txtendReasonDate = DateTime.Parse(dr["CompletedDate"].ToString()).ToString("yyyy-MM-dd");
addValue.txtemail = dr["StudentEmail"].ToString();
addValue.txtTel = dr["Phone"].ToString();
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchStudentTrackResult addValue = new SearchStudentTrackResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchVoiceDistanceCount(SearchStudent SearchStructure, int type)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchVoiceDistanceCondition(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM VoiceDistance a left join studentDatabase b on a.studentid = b.id WHERE 1=1 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@AcademicYearstart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAcademicYearstart);
cmd.Parameters.Add("@AcademicYearend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAcademicYearend);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchHearingLossPreschoolCount(SearchStudent SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
return SDB.SearchHearingLossPreschoolCount(SearchStructure, 1);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<SearchStudentResult> SearchStudentDataBase1(int index, SearchStudent SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
return SDB.SearchStudent(index, SearchStructure, 1);
}
public List<TeachServiceInspect> SearchTeachServiceInspect(int indexpage, SearchStudent SearchStructure, int type)
{
List<TeachServiceInspect> returnValue = new List<TeachServiceInspect>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchTeachServiceInspectCondition(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.AcademicYear,'') DESC) as RowNum ";
sql += " ,a.id, a.AcademicYear, convert(varchar, isnull(convert(date, a.InspectDate,1),'1912'), 111) as InspectDate,a.ClassType , b.StudentName , b.StudentBirthday , c.TeacherName ,d.ClassIDName ";
sql += " FROM TeachServiceInspect a left join studentDatabase b on a.studentid = b.id ";
sql += " left join (select staffid as cid , staffname as TeacherName from staffdatabase ) c on a.teacherid = c.cid ";
sql += " left join (select ClassIDName , ClassID as did from ClassName ) d on a.ClassNameID = d.did ";
sql += " WHERE 1=1 " + ConditionReturn + ") AS NewTable ";
sql += " where RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@InspectDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDatestart);
cmd.Parameters.Add("@InspectDateend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDateend);
cmd.Parameters.Add("@AcademicYearstart", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtAcademicYearstart);
cmd.Parameters.Add("@AcademicYearend", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtAcademicYearend);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TeachServiceInspect addValue = new TeachServiceInspect();
addValue.RowNum = dr["rownum"].ToString();
addValue.ID = dr["ID"].ToString();
addValue.ClassType = dr["ClassType"].ToString();
addValue.InspectDate = dr["InspectDate"].ToString();
addValue.StudentName = dr["StudentName"].ToString();
addValue.TeacherName = dr["TeacherName"].ToString();
addValue.AcademicYear = dr["AcademicYear"].ToString();
addValue.ClassIDName = dr["ClassIDName"].ToString();
//if (dr["ClassType"].ToString() == 1)
//{
// addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());
//}
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
TeachServiceInspect addValue = new TeachServiceInspect();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<VoiceDistance> SearchVoiceDistance(int index, SearchStudent SearchStructure)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchVoiceDistance(index, SearchStructure, 0);
}
public List<TeachServiceSupervisor> SearchTeachServiceSupervisor(int index, SearchStudent SearchStructure)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchTeachServiceSupervisor(index, SearchStructure, 0);
}
public List<TeachServiceInspect> SearchTeachServiceInspect(int index, SearchStudent SearchStructure)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchTeachServiceInspect(index, SearchStructure, 1);
}
public string[] SearchAchievementAssessmentCount(SearchStudent SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
return SDB.SearchAchievementAssessmentCount(SearchStructure, 1);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public string[] SearchStudentCount(SearchStudent SearchStructure,int type)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentConditionReturn(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM StudentDatabase WHERE isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value ="%"+ Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtcaseStatu);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@sGuaranteeDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtjoindaystart);
cmd.Parameters.Add("@sGuaranteeDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtjoindayend);
cmd.Parameters.Add("@sCompletedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDatestart);
cmd.Parameters.Add("@sCompletedDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDateend);
cmd.Parameters.Add("@CompletedType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtendReasonType);
cmd.Parameters.Add("@NomembershipType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtnomembershipType);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchVoiceDistanceCount(SearchStudent SearchStructure)
{
AdministrationDataBase aDB = new AdministrationDataBase();
//aDB.caseBTFunction();
//if (int.Parse(aDB._StaffhaveRoles[2]) == 1)
//{
return aDB.SearchVoiceDistanceCount(SearchStructure, 1);
//}
//else
//{
// return new string[2] { _noRole, _errorMsg };
//}
}
public List<SearchStudentTrackResult> SearchStudentTrackedDataBase(int index, SearchStudent SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
return SDB.SearchStudentTracked(index, SearchStructure);
}
public void SearchStudent()
{
try
{
SearchStudent SS = new SearchStudent();
if (ddlStType.SelectedValue == "1")
{
if (string.IsNullOrEmpty(txtSS.Text))
{
lblError.Visible = true;
lblSuccess.Visible = false;
lblError.Text = "Enter Student ID";
}
else
{
gvSearchResult.DataSource = null;
gvSearchResult.DataBind();
SS.sCode = Convert.ToInt64(txtSS.Text);
SS.Current_Session = Session["Current_Session"].ToString();
SS.Check = 4;
DataSet ds = dal.searchStudent(SS);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
txtname.Text = dr["StudentName"].ToString();
txtParentage.Text = dr["FathersName"].ToString();
txtRollNo.Text = dr["RollNo"].ToString();
txtClass.Text = dr["ClassName"].ToString();
txtSection.Text = dr["SectionName"].ToString();
txtUID.Text = dr["StudentCode"].ToString();
gvSearchResult.DataSource = ds;
gvSearchResult.DataBind();
txtSS.Text = "";
lblError.Visible = false;
lblSuccess.Visible = false;
}
else
{
gvSearchResult.DataSource = null;
gvSearchResult.DataBind();
lblError.Visible = true;
lblSuccess.Visible = false;
lblError.Text = "No Record Found";
}
}
}
else if (ddlStType.SelectedValue == "2")
{
if (string.IsNullOrEmpty(txtSS.Text))
{
lblError.Visible = true;
lblSuccess.Visible = false;
lblError.Text = "Enter Admissin No. ";
}
else
{
gvSearchResult.DataSource = null;
gvSearchResult.DataBind();
SS.AdminNo = Convert.ToInt64(txtSS.Text);
SS.Current_Session = Session["Current_Session"].ToString();
SS.Check = Convert.ToInt32(ddlStType.SelectedValue);
DataSet ds = dal.searchStudent(SS);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
txtname.Text = dr["StudentName"].ToString();
txtParentage.Text = dr["FathersName"].ToString();
txtRollNo.Text = dr["RollNo"].ToString();
txtClass.Text = dr["ClassName"].ToString();
txtSection.Text = dr["SectionName"].ToString();
txtUID.Text = dr["StudentCode"].ToString();
gvSearchResult.DataSource = ds;
gvSearchResult.DataBind();
txtSS.Text = "";
lblError.Visible = false;
lblSuccess.Visible = false;
}
else
{
gvSearchResult.DataSource = null;
gvSearchResult.DataBind();
lblError.Visible = true;
lblSuccess.Visible = false;
lblError.Text = "No Record Found";
}
}
}
else if (ddlStType.SelectedValue == "3")
{
if (string.IsNullOrEmpty(txtSS.Text))
{
lblError.Visible = true;
lblSuccess.Visible = false;
lblError.Text = "Enter Student Name";
}
else
{
gvSearchResult.DataSource = null;
gvSearchResult.DataBind();
SS.SName = txtSS.Text;
SS.Current_Session = Session["Current_Session"].ToString();
SS.Check = Convert.ToInt32(ddlStType.SelectedValue);
DataSet ds = dal.searchStudent(SS);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
txtname.Text = dr["StudentName"].ToString();
txtParentage.Text = dr["FathersName"].ToString();
txtRollNo.Text = dr["RollNo"].ToString();
txtClass.Text = dr["ClassName"].ToString();
txtSection.Text = dr["SectionName"].ToString();
txtUID.Text = dr["StudentCode"].ToString();
gvSearchResult.DataSource = ds;
gvSearchResult.DataBind();
txtSS.Text = "";
lblError.Visible = false;
lblSuccess.Visible = false;
}
else
{
gvSearchResult.DataSource = null;
gvSearchResult.DataBind();
lblError.Visible = true;
lblSuccess.Visible = false;
lblError.Text = "No Record Found";
}
}
}
else if (ddlStType.SelectedValue == "5")
{
if (ddlStType.SelectedValue == "-1")
{
lblError.Visible = true;
lblSuccess.Visible = false;
lblError.Text = "Enter Student Name";
}
else
{
gvSearchResult.DataSource = null;
gvSearchResult.DataBind();
SS.sCode = Convert.ToInt64(ddlStudent.SelectedValue);
SS.Current_Session = Session["Current_Session"].ToString();
SS.Check = 4;
DataSet ds = dal.searchStudent(SS);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
txtname.Text = dr["StudentName"].ToString();
txtParentage.Text = dr["FathersName"].ToString();
txtRollNo.Text = dr["RollNo"].ToString();
txtClass.Text = dr["ClassName"].ToString();
txtSection.Text = dr["SectionName"].ToString();
txtUID.Text = dr["StudentCode"].ToString();
gvSearchResult.DataSource = ds;
gvSearchResult.DataBind();
txtSS.Text = "";
lblError.Visible = false;
lblSuccess.Visible = false;
}
else
{
gvSearchResult.DataSource = null;
gvSearchResult.DataBind();
lblError.Visible = true;
lblSuccess.Visible = false;
lblError.Text = "No Record Found";
}
}
}
}
catch (Exception ex)
{
lblError.Visible = true;
lblError.Text = ex.ToString();
}
}
private void FindEditButton_Click(object sender, EventArgs e)
{
SearchStudent searchStudent = new SearchStudent();
searchStudent.Show();
}
public List<VoiceDistance> SearchVoiceDistance(int indexpage, SearchStudent SearchStructure, int type)
{
List<VoiceDistance> returnValue = new List<VoiceDistance>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchVoiceDistanceCondition(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.AcademicYear,'') DESC) as RowNum ";
sql += " ,a.id, a.AcademicYear,a.AcademicTerm ,a.StudentAge,a.StudentMonth , b.StudentName , b.StudentBirthday ";
sql += " FROM VoiceDistance a left join studentDatabase b on a.studentid = b.id ";
sql += " WHERE 1=1 " + ConditionReturn + ") AS NewTable ";
sql += " where RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@AcademicYearstart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAcademicYearstart);
cmd.Parameters.Add("@AcademicYearend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAcademicYearend);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
VoiceDistance addValue = new VoiceDistance();
addValue.RowNum = dr["rownum"].ToString();
addValue.ID = dr["ID"].ToString();
addValue.StudentName = dr["StudentName"].ToString();
addValue.AcademicYear = dr["AcademicYear"].ToString();
addValue.AcademicTerm = dr["AcademicTerm"].ToString();
addValue.StudentAge = dr["StudentAge"].ToString();
addValue.StudentMonth = dr["StudentMonth"].ToString();
addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
VoiceDistance addValue = new VoiceDistance();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<SearchAchievementAssessment> SearchCaseStudy(int indexpage, SearchStudent SearchStructure, int type)
{
List<SearchAchievementAssessment> returnValue = new List<SearchAchievementAssessment>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchAchievementAssessmentCondition(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.WriteDate,'') DESC) as RowNum , a.ID ,a.WriteDate , b.StudentName , b.StudentSex, b.StudentBirthday ";
// string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.WriteDate,'') DESC) as RowNum , a.ID , b.StudentName , CASE (b.StudentSex) WHEN '1' THEN '男' WHEN '2 THEN '女' END as StudentSex , b.StudentBirthday ";
sql += " FROM CaseStudy a left join studentDatabase b on a.studentid = b.id WHERE isnull(a.isDeleted,0) = 0 " + ConditionReturn + ") AS NewTable ";
sql += " where RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchAchievementAssessment addValue = new SearchAchievementAssessment();
addValue.RowNum = dr["rownum"].ToString();
addValue.ID = int.Parse(dr["ID"].ToString());
addValue.txtstudentName = dr["StudentName"].ToString();
addValue.txtstudentSex = int.Parse(dr["StudentSex"].ToString());
addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());
addValue.WriteDate = DateTime.Parse(dr["WriteDate"].ToString());
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchAchievementAssessment addValue = new SearchAchievementAssessment();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
private string SearchVoiceDistanceCondition(SearchStudent SearchStructure, int type)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (SearchStructure.txtstudentID != null)
{
ConditionReturn += " AND StudentID=(@StudentID) ";
}
if (SearchStructure.txtstudentName != null)
{
ConditionReturn += " AND StudentName like (@StudentName) ";
}
if (SearchStructure.txtstudentSex != null && SearchStructure.txtstudentSex != "0")
{
ConditionReturn += " AND StudentSex=(@StudentSex) ";
}
if (SearchStructure.txtbirthdaystart != null && SearchStructure.txtbirthdayend != null && SearchStructure.txtbirthdaystart != DateBase && SearchStructure.txtbirthdayend != DateBase)
{
ConditionReturn += " AND StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) ";
}
if (SearchStructure.txtAcademicYearstart != null && SearchStructure.txtAcademicYearend != null && SearchStructure.txtAcademicYearstart != DateBase && SearchStructure.txtAcademicYearend != DateBase)
{
ConditionReturn += " AND AcademicYear BETWEEN (@AcademicYearstart) AND (@AcademicYearend) "; // 教學管理使用 不知是否會與其他衝突
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
caseBTFunction();
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND b.Unit =" + UserFile[2] + " ";
}
if (type == 0)
{
ConditionReturn += " AND b.CaseStatu2 =" + type + " ";
}
return ConditionReturn;
}
public List<SearchStudentResult> SearchStudent(int indexpage, SearchStudent SearchStructure,int type)
{
List<SearchStudentResult> returnValue = new List<SearchStudentResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentConditionReturn(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY StudentDatabase.StudentID DESC) " +
"AS RowNum, StudentDatabase.* " +
"FROM StudentDatabase WHERE isDeleted=0 " + ConditionReturn + " ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtcaseStatu);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@sGuaranteeDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtjoindaystart);
cmd.Parameters.Add("@sGuaranteeDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtjoindayend);
cmd.Parameters.Add("@sCompletedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDatestart);
cmd.Parameters.Add("@sCompletedDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDateend);
cmd.Parameters.Add("@CompletedType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtendReasonType);
cmd.Parameters.Add("@NomembershipType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtnomembershipType);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchStudentResult addValue = new SearchStudentResult();
addValue.ID = int.Parse(dr["ID"].ToString());
addValue.txtstudentID = dr["StudentID"].ToString();
addValue.txtstudentName = dr["StudentName"].ToString();
addValue.txtstudentStatu = dr["CaseStatu"].ToString();
addValue.txtLegalRepresentativeRelation = dr["ContactRelation2"].ToString();
addValue.txtLegalRepresentative = dr["ContactName2"].ToString();
addValue.txtLegalRepresentativePhone = dr["ContactPhone2"].ToString();
addValue.txtLegalRepresentativeTel = dr["ContactTel_home2"].ToString();
addValue.txtstudentSex = int.Parse(dr["StudentSex"].ToString());
addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchStudentResult addValue = new SearchStudentResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
