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