private string SearchStudentTransConditionReturn(SearchTransRecord SearchStructure)
{
string ConditionReturn = "";
if (SearchStructure.txtstudentName != null)
{
ConditionReturn += " AND StudentDatabase.StudentName like (@StudentName) ";
}
if (SearchStructure.txttransYear != null)
{
ConditionReturn += " AND TurnYear=@TurnYear";
}
if (SearchStructure.txttransActivity != null)
{
ConditionReturn += " AND Events=@Events";
}
if (SearchStructure.txtmeeting != null)
{
ConditionReturn += " AND ParticipateMeeting=@ParticipateMeeting";
}
if (SearchStructure.txtschoolVisit != null)
{
ConditionReturn += " AND SchoolVisit=@SchoolVisit";
}
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 CaseTransitionService.Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public string[] SearchStudentTransCount(SearchTransRecord SearchStructure)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentTransConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM CaseTransitionService " +
"INNER JOIN StudentDatabase ON CaseTransitionService.StudentID=StudentDatabase.StudentID " +
"WHERE CaseTransitionService.isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@TurnYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txttransYear);
cmd.Parameters.Add("@Events", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txttransActivity);
cmd.Parameters.Add("@ParticipateMeeting", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtmeeting);
cmd.Parameters.Add("@SchoolVisit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtschoolVisit);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchStudentTransDataBaseCount(SearchTransRecord SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
return SDB.SearchStudentTransCount(SearchStructure);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<SearchTransRecordResult> SearchStudentTrans(int indexpage, SearchTransRecord SearchStructure)
{
List<SearchTransRecordResult> returnValue = new List<SearchTransRecordResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentTransConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CaseTransitionService.ID DESC) " +
"AS RowNum, .CaseTransitionService.* ,StudentDatabase.StudentName " +
"FROM CaseTransitionService " +
"INNER JOIN StudentDatabase ON CaseTransitionService.StudentID=StudentDatabase.StudentID " +
"WHERE CaseTransitionService.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("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@TurnYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txttransYear);
cmd.Parameters.Add("@Events", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txttransActivity);
cmd.Parameters.Add("@ParticipateMeeting", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtmeeting);
cmd.Parameters.Add("@SchoolVisit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtschoolVisit);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchTransRecordResult addValue = new SearchTransRecordResult();
addValue.ID = dr["ID"].ToString();
addValue.txtstudentName = dr["StudentName"].ToString();
addValue.txtstudentAge = dr["Age"].ToString();
addValue.txtstudentMonth = dr["AgeMonth"].ToString();
addValue.txttransStage = dr["TurnStage"].ToString();
addValue.txttransYear = dr["TurnYear"].ToString();
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchTransRecordResult addValue = new SearchTransRecordResult();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<SearchTransRecordResult> SearchStudentTransDataBase(int index, SearchTransRecord SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
return SDB.SearchStudentTrans(index, SearchStructure);
}
