private string SearchStudentActivityConditionReturn(SearchStudentActivity SearchStructure)
{
string ConditionReturn = "";
string DateBase = "1900-01-01";
if (SearchStructure.txteventName != null)
{
ConditionReturn += " AND actName like @actName ";
}
if (SearchStructure.txteventDatestart != null && SearchStructure.txteventDateend != null && SearchStructure.txteventDatestart != DateBase && SearchStructure.txteventDateend != DateBase)
{
ConditionReturn += " AND actDate BETWEEN (@sDateStart) AND (@sDateEnd) ";
}
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 Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public string[] SearchStudentActivityCount(SearchStudentActivity SearchStructure)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentActivityConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM ActivitiesRecords "+
"WHERE isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@actName", SqlDbType.NVarChar).Value = SearchStructure.txteventName + "%";
cmd.Parameters.Add("@sDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txteventDatestart);
cmd.Parameters.Add("@sDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txteventDateend);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string[] SearchStudentActivityDataBaseCount(SearchStudentActivity SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
return SDB.SearchStudentActivityCount(SearchStructure);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<SearchStudentActivityResult> SearchStudentActivity(int indexpage, SearchStudentActivity SearchStructure)
{
List<SearchStudentActivityResult> returnValue = new List<SearchStudentActivityResult>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchStudentActivityConditionReturn(SearchStructure);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY actDate DESC) " +
"AS RowNum, * , (SELECT COUNT(*) FROM ActivityParticipation WHERE actID=ActivitiesRecords.actID AND ActivityParticipation.isDeleted=0) AS pCOUNT " +
"FROM ActivitiesRecords "+
"WHERE ActivitiesRecords.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("@actName", SqlDbType.NVarChar).Value = SearchStructure.txteventName + "%";
cmd.Parameters.Add("@sDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txteventDatestart);
cmd.Parameters.Add("@sDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txteventDateend);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SearchStudentActivityResult addValue = new SearchStudentActivityResult();
addValue.ID = dr["actID"].ToString();
addValue.txteventName = dr["actName"].ToString();
addValue.txteventDate = DateTime.Parse(dr["actDate"].ToString()).ToString("yyyy-MM-dd");
addValue.txttotalNumber = dr["pCOUNT"].ToString();
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
SearchStudentActivityResult addValue = new SearchStudentActivityResult();
addValue.ID = "-1";
addValue.txteventName = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<SearchStudentActivityResult> SearchStudentActivityDataBase(int index, SearchStudentActivity SearchStructure)
{
CaseDataBase SDB = new CaseDataBase();
return SDB.SearchStudentActivity(index, SearchStructure);
}
