private string SearchCPTDataConditionReturn(SearchTeachCaseItem SearchData)
{
string ConditionReturn = "";
if (SearchData.txtClassName != null)
{
ConditionReturn += " AND ClassName.ClassIDName LIKE (@ClassIDName) ";
}
if ( SearchData.txtTeacherName != null)
{
ConditionReturn += " AND StaffDatabase.StaffName LIKE(@TeacherName) ";
}
if ( SearchData.txtCourseName != null)
{
ConditionReturn += " AND CourseName.CourseIDName LIKE (@CourseIDName) ";
}
if (SearchData.txtSPeriod != null && SearchData.txtEPeriod != null && SearchData.txtSPeriod != "1900-01-01" && SearchData.txtEPeriod != "1900-01-01")
{
ConditionReturn += " AND StartPeriod >= @StartPeriod AND EndPeriod <= @EndPeriod ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserData = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (UserData[2] != "0" && UserData[2] != null)
{
ConditionReturn += " AND CoursePlanTemplate.Unit =(@Unit) ";
}
return ConditionReturn;
}
public string[] SearchCPTDataBaseCount(SearchTeachCaseItem SearchData)
{
string[] returnValue = new string[2];
DataBase Base = new DataBase();
StaffDataBase sDB = new StaffDataBase();
List<string> UserData = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
string ConditionReturn = this.SearchCPTDataConditionReturn(SearchData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT Count(CoursePlanTemplate.CPTID) AS sCount " +
"FROM CoursePlanTemplate " +
"INNER JOIN StaffDatabase ON StaffDatabase.isDeleted=0 AND CoursePlanTemplate.TeacherID=StaffDatabase.StaffID " +
"INNER JOIN ClassName ON ClassName.isDeleted=0 AND CoursePlanTemplate.ClassID=ClassName.ClassID " +
"INNER JOIN CourseName ON CourseName.isDeleted=0 AND CoursePlanTemplate.ClassID=CourseName.CourseID " +
"WHERE CoursePlanTemplate.isDeleted=0" + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ClassIDName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchData.txtClassName) + "%";
cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchData.txtTeacherName) + "%";
cmd.Parameters.Add("@CourseIDName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchData.txtCourseName) + "%";
cmd.Parameters.Add("@StartPeriod", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchData.txtSPeriod);
cmd.Parameters.Add("@EndPeriod", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchData.txtEPeriod);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(UserData[2]);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0]="-1";
returnValue[1]=e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchCPTDataBaseCount(SearchTeachCaseItem SearchData)
{
TeachDataBase tDB = new TeachDataBase();
return tDB.SearchCPTDataBaseCount(SearchData);
}
public List<CPTDataList> SearchCPTDataBase(int indexpage, SearchTeachCaseItem SearchData)
{
List<CPTDataList> returnValue = new List<CPTDataList>();
DataBase Base = new DataBase();
StaffDataBase sDB = new StaffDataBase();
List<string> UserData = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
string ConditionReturn = this.SearchCPTDataConditionReturn(SearchData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CoursePlanTemplate.CPTID ASC) AS RowNum, CoursePlanTemplate.CPTID," +
"ClassName.ClassIDName AS ClassIDName," +
"StaffDatabase.StaffName AS TeacherName,"+
"CourseName.CourseIDName AS CourseIDName," +
"CoursePlanTemplate.StartPeriod, CoursePlanTemplate.EndPeriod, CoursePlanTemplate.Unit " +
"FROM CoursePlanTemplate " +
"INNER JOIN ClassName ON ClassName.isDeleted=0 AND CoursePlanTemplate.ClassID=ClassName.ClassID " +
"INNER JOIN StaffDatabase ON StaffDatabase.isDeleted=0 AND CoursePlanTemplate.TeacherID=StaffDatabase.StaffID " +
"INNER JOIN CourseName ON CourseName.isDeleted=0 AND CoursePlanTemplate.CourseNameID=CourseName.CourseID " +
ConditionReturn +" ) "+
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + this.PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@ClassIDName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchData.txtClassName) + "%";
cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchData.txtTeacherName) + "%";
cmd.Parameters.Add("@CourseIDName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchData.txtCourseName) + "%";
cmd.Parameters.Add("@StartPeriod", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchData.txtSPeriod);
cmd.Parameters.Add("@EndPeriod", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchData.txtEPeriod);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(UserData[2]);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CPTDataList addValue = new CPTDataList();
addValue.sID = int.Parse(dr["RowNum"].ToString());
addValue.sCPTID = int.Parse(dr["CPTID"].ToString());
addValue.sClassName = dr["ClassIDName"].ToString();
addValue.sTeacherName = dr["TeacherName"].ToString();
addValue.sCourseName = dr["CourseIDName"].ToString();
addValue.sStartPeriod = DateTime.Parse(dr["StartPeriod"].ToString());
addValue.sEndPeriod = DateTime.Parse(dr["EndPeriod"].ToString());
addValue.sUnit = int.Parse(dr["Unit"].ToString());
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CPTDataList addValue = new CPTDataList();
addValue.sCPTID = -1;
addValue.sClassName = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<CPTDataList> SearchCPTDataBase(int index, SearchTeachCaseItem SearchData)
{
TeachDataBase tDB = new TeachDataBase();
return tDB.SearchCPTDataBase(index, SearchData);
}
