public string[] SearchTeacherRstimateDataBaseCount(SearchTeacherRstimate SearchTeacher) { StaffDataBase sDB = new StaffDataBase(); sDB.personnelFunction(); if (int.Parse(sDB._StaffhaveRoles[3]) == 1) { return sDB.SearchTeacherRstimateDataBaseCount(SearchTeacher); } else { return new string[2] { _noRole, _errorMsg }; } }
public List<SearchTeacherRstimateResult> SearchTeacherRstimateDataBase(int index, SearchTeacherRstimate SearchTeacher) { StaffDataBase sDB = new StaffDataBase(); return sDB.SearchTeacherRstimateDataBase(index, SearchTeacher); }
public string[] SearchTeacherRstimateDataBaseCount(SearchTeacherRstimate SearchTeacher) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); string[] ConditionReturn = this.SearchTeacherRstimateConditionReturn(SearchTeacher); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT Count(NewTeacherPerformanceRating.ID) " + "FROM NewTeacherPerformanceRating INNER JOIN StaffDatabase ON NewTeacherPerformanceRating.TeacherID=StaffDatabase.StaffID " + ConditionReturn[0] + "WHERE NewTeacherPerformanceRating.isDeleted=0 " + ConditionReturn[1]; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@txtTeacher", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchTeacher.txtTeacher) + "%"; cmd.Parameters.Add("@txtTeacherSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchTeacher.txtTeacherSex); cmd.Parameters.Add("@sassessmentStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchTeacher.txtassessmentStart); cmd.Parameters.Add("@sassessmentEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchTeacher.txtassessmentEnd); cmd.Parameters.Add("@txtTeacherUnit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchTeacher.txtTeacherUnit); returnValue[0] = cmd.ExecuteScalar().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] ="-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
private string[] SearchTeacherRstimateConditionReturn(SearchTeacherRstimate SearchConditionData) { string[] returnValue = new string[2]; string ConditionReturn = ""; string ConditionReturn1 = ""; string DataBase = "1900-01-01"; if (SearchConditionData.txtTeacher != null) { ConditionReturn += " AND StaffDatabase.StaffName like (@txtTeacher) "; } if (SearchConditionData.txtTeacherSex != null) { ConditionReturn += " AND StaffDatabase.sex = (@txtTeacherSex) "; } if (SearchConditionData.txtTeacherUnit != null) { ConditionReturn1 += " AND NewTeacherPerformanceRating.Unit =(@txtTeacherUnit) "; } if (SearchConditionData.txtassessmentStart != null && SearchConditionData.txtassessmentEnd != null && SearchConditionData.txtassessmentStart != DataBase && SearchConditionData.txtassessmentEnd != DataBase) { ConditionReturn1 += " AND NewTeacherPerformanceRating.RatingDate BETWEEN (@sassessmentStart) AND (@sassessmentEnd) "; } List<string> UserFile = this.getStaffDataName(HttpContext.Current.User.Identity.Name); this.personnelFunction(); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn1 += " AND NewTeacherPerformanceRating.Unit =" + UserFile[2] + " "; } returnValue[0] = ConditionReturn; returnValue[1] = ConditionReturn1; return returnValue; }
public List<SearchTeacherRstimateResult> SearchTeacherRstimateDataBase(int indexpage, SearchTeacherRstimate SearchTeacher) { List<SearchTeacherRstimateResult> returnValue = new List<SearchTeacherRstimateResult>(); DataBase Base = new DataBase(); string[] ConditionReturn = this.SearchTeacherRstimateConditionReturn(SearchTeacher); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY NewTeacherPerformanceRating.ID DESC) " + "AS RowNum, NewTeacherPerformanceRating.*,StaffDatabase.StaffName " + "FROM NewTeacherPerformanceRating INNER JOIN StaffDatabase ON NewTeacherPerformanceRating.TeacherID=StaffDatabase.StaffID " + ConditionReturn[0] + "WHERE NewTeacherPerformanceRating.isDeleted=0 " + ConditionReturn[1] + " ) " + "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("@txtTeacher", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchTeacher.txtTeacher) + "%"; cmd.Parameters.Add("@txtTeacherSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchTeacher.txtTeacherSex); cmd.Parameters.Add("@sassessmentStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchTeacher.txtassessmentStart); cmd.Parameters.Add("@sassessmentEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchTeacher.txtassessmentEnd); cmd.Parameters.Add("@txtTeacherUnit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchTeacher.txtTeacherUnit); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SearchTeacherRstimateResult addValue = new SearchTeacherRstimateResult(); addValue.Number = dr["RowNum"].ToString(); addValue.ID = dr["ID"].ToString(); addValue.TeacherName = dr["StaffName"].ToString(); addValue.Unit = dr["Unit"].ToString(); addValue.AssessmentDate = DateTime.Parse(dr["RatingDate"].ToString()).ToString("yyyy-MM-dd"); returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { SearchTeacherRstimateResult addValue = new SearchTeacherRstimateResult(); addValue.ID = "-1"; addValue.TeacherName = e.Message; returnValue.Add(addValue); } } return returnValue; }