private string SearchClassNameCondition(SearchClassName SearchStructure)
{
string ConditionReturn = "";
if (SearchStructure.txtstaffID != null)
{
ConditionReturn += " AND TeacherID=(@txtstaffID) ";
}
if (SearchStructure.txtstaffName != null)
{
ConditionReturn += " AND TeacherName like (@txtstaffName) ";
}
if (SearchStructure.txtClassID != null)
{
ConditionReturn += " AND ClassID=(@txtClassID) ";
}
if (SearchStructure.txtClassName != null)
{
ConditionReturn += " AND ClassName like (@txtClassName) ";
}
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 a.Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public List<SearchClassName> SearchClassNameDataBaseCase(int indexpage, SearchClassName SearchStructure)
{
List<SearchClassName> returnValue = new List<SearchClassName>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchClassNameCondition(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.ClassID,'') DESC) as RowNum , * ";
sql += " FROM classname a ";
sql += " left join (select staffid as bid , StaffName as TeacherName from staffDatabase ) b on a.Teacherid = b.bid ";
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("@txtstaffID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstaffID);
cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstaffName) + "%";
cmd.Parameters.Add("@txtClassID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtClassID);
cmd.Parameters.Add("@txtClassName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtClassName) + "%";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchClassName addValue = new SearchClassName();
addValue.txtClassID = dr["ClassID"].ToString();
addValue.txtClassName = dr["ClassIDName"].ToString();
addValue.txtstaffID = dr["Teacherid"].ToString();
addValue.txtstaffName = dr["teachername"].ToString();
//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)
{
string a = e.Message.ToString();
//SearchClassName addValue = new SearchClassName();
//addValue.checkNo = "-1";
//addValue.errorMsg = e.Message.ToString();
//returnValue.Add(addValue);
}
}
return returnValue;
}
public List<SearchClassName> SearchClassNameDataBaseCase(int index, SearchClassName SearchStaffCondition)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchClassNameDataBaseCase(index, SearchStaffCondition);
}
public string[] SearchClassNameCountCase(SearchClassName SearchStructure)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchClassNameCondition(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM ClassName a left join (select staffid as bid , StaffName as TeacherName from staffDatabase ) b on a.Teacherid = b.bid WHERE 1=1 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtstaffID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstaffID);
cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstaffName) + "%";
cmd.Parameters.Add("@txtClassID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtClassID);
cmd.Parameters.Add("@txtClassName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtClassName) + "%";
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchClassNameCountCase(SearchClassName SearchStaffCondition)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchClassNameCountCase(SearchStaffCondition);
}
