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