private string SearchSingleTeachCondition(SearchCaseISPRecord SearchStructure, int type) { string ConditionReturn = ""; string DateBase = "1900-01-01"; if (SearchStructure.txtstudentName != null) { ConditionReturn += " AND StudentName like (@StudentName) "; } if (SearchStructure.txtteacherName != null) { ConditionReturn += " AND TeacherName like (@TeacherName) "; } if (SearchStructure.txtConventionDatestart != null && SearchStructure.txtConventionDateend != null && SearchStructure.txtConventionDatestart != DateBase && SearchStructure.txtConventionDateend != DateBase) { ConditionReturn += " AND ( PlanDateStart BETWEEN (@ConventionDatestart) AND (@ConventionDaterend) or PlanDateEnd BETWEEN (@ConventionDatestart) AND (@ConventionDaterend) ) "; } 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 b.Unit =" + UserFile[2] + " "; } if (type == 0) { ConditionReturn += " AND b.CaseStatu2 =" + type + " "; } return ConditionReturn; }
public List<SingleClassShortTerm> SearchSingleTeach(int indexpage, SearchCaseISPRecord SearchStructure, int type) { List<SingleClassShortTerm> returnValue = new List<SingleClassShortTerm>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchCaseISPRecordCondition(SearchStructure, type); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = " SELECT * from (SELECT ROW_NUMBER() OVER (ORDER BY isnull( a.id,'') DESC) as RowNum,a.ID , PlanDateStart ,PlanDateEnd , b.studentName , c.TeacherName "; sql += " FROM SingleClassShortTerm a "; sql += " left join studentDatabase b on a.studentid = b.studentid "; sql += " left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid"; sql += " WHERE isnull(a.isDeleted,0) = 0 " + 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("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%"; cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtteacherName) + "%"; cmd.Parameters.Add("@ConventionDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDatestart); cmd.Parameters.Add("@ConventionDaterend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDateend); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SingleClassShortTerm addValue = new SingleClassShortTerm(); addValue.RowNum = dr["rownum"].ToString(); addValue.ID = dr["ID"].ToString(); addValue.studentName = dr["studentName"].ToString(); addValue.teacherName = dr["teacherName"].ToString(); addValue.PlanDateStart = DateTime.Parse(dr["PlanDateStart"].ToString()).ToString("yyyy-MM-dd"); addValue.PlanDateEnd = DateTime.Parse(dr["PlanDateEnd"].ToString()).ToString("yyyy-MM-dd"); returnValue.Add(addValue); } Sqlconn.Close(); } catch (Exception e) { //ShowCaseISPRecord addValue = new ShowCaseISPRecord(); //addValue.checkNo = "-1"; //addValue.errorMsg = e.Message.ToString(); //returnValue.Add(addValue); } } return returnValue; }
public string[] SearchSingleTeachCount(SearchCaseISPRecord SearchStructure, int type) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); string ConditionReturn = this.SearchSingleTeachCondition(SearchStructure, type); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT COUNT(*) AS QCOUNT FROM SingleClassShortTerm a left join studentDatabase b on a.studentid = b.id left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid WHERE isnull(a.isDeleted,0) = 0 " + ConditionReturn; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%"; cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtteacherName) + "%"; cmd.Parameters.Add("@ConventionDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDatestart); cmd.Parameters.Add("@ConventionDaterend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDateend); returnValue[0] = cmd.ExecuteScalar().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public List<ShowCaseISPRecord> SearchCaseISPRecord(int indexpage, SearchCaseISPRecord SearchStructure, int type) { List<ShowCaseISPRecord> returnValue = new List<ShowCaseISPRecord>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchCaseISPRecordCondition(SearchStructure, type); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.ConventionDate,'') DESC) as RowNum , a.ID ,a.ConventionName, convert(varchar, isnull(convert(date,a.ConventionDate,1),'1912'), 111) as ConventionDate, b.StudentName "; sql += " , TeacherName, ParticipantTeacheName,ParticipantSocialWorkerName,ParticipantAudiologistName,ParticipantHeadName "; sql += " , a.ParticipantParent, a.ParticipantProfessionals "; sql += " FROM CaseISPRecord a left join studentDatabase b on a.studentid = b.id "; sql += " left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid "; sql += " left join (select staffid as did , StaffName as ParticipantTeacheName from staffDatabase) d on a.ParticipantTeache = d.did "; sql += " left join (select staffid as eid , StaffName as ParticipantSocialWorkerName from staffDatabase) e on a.ParticipantSocialWorker = e.eid "; sql += " left join (select staffid as fid , StaffName as ParticipantAudiologistName from staffDatabase) f on a.ParticipantAudiologist = f.fid "; sql += " left join (select staffid as gid , StaffName as ParticipantHeadName from staffDatabase) g on a. ParticipantHead = g.gid "; sql += " WHERE isnull(a.isDeleted,0) = 0 " + 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("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%"; cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtteacherName) + "%"; cmd.Parameters.Add("@ConventionDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDatestart); cmd.Parameters.Add("@ConventionDaterend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtConventionDateend); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { ShowCaseISPRecord addValue = new ShowCaseISPRecord(); addValue.RowNum = dr["rownum"].ToString(); addValue.ID = dr["ID"].ToString(); addValue.ConventionName = dr["ConventionName"].ToString(); addValue.ConventionDate = Convert.ToDateTime(dr["ConventionDate"].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1); addValue.StudentName = dr["StudentName"].ToString(); addValue.TeacherName = dr["TeacherName"].ToString(); addValue.ParticipantTeacheName = dr["ParticipantTeacheName"].ToString(); addValue.ParticipantSocialWorkerName = dr["ParticipantSocialWorkerName"].ToString(); addValue.ParticipantAudiologistName = dr["ParticipantAudiologistName"].ToString(); addValue.ParticipantHeadName = dr["ParticipantHeadName"].ToString(); addValue.ParticipantParent = dr["ParticipantParent"].ToString(); addValue.ParticipantProfessionals = dr["ParticipantProfessionals"].ToString(); returnValue.Add(addValue); } Sqlconn.Close(); } catch (Exception e) { ShowCaseISPRecord addValue = new ShowCaseISPRecord(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message.ToString(); returnValue.Add(addValue); } } return returnValue; }
public string[] SearchSingleTeachCount(SearchCaseISPRecord SearchStructure) { AdministrationDataBase aDB = new AdministrationDataBase(); return aDB.SearchSingleTeachCount(SearchStructure, 1); }
public List<SingleClassShortTerm> SearchSingleTeach(int index, SearchCaseISPRecord SearchStructure) { AdministrationDataBase aDB = new AdministrationDataBase(); return aDB.SearchSingleTeach(index, SearchStructure, 0); }
public string[] SearchCaseISPRecordCount(SearchCaseISPRecord SearchStructure) { AdministrationDataBase aDB = new AdministrationDataBase(); //aDB.caseBTFunction(); //if (int.Parse(aDB._StaffhaveRoles[2]) == 1) //{ return aDB.SearchCaseISPRecordCount(SearchStructure, 1); //} //else //{ // return new string[2] { _noRole, _errorMsg }; //} }
public List<ShowCaseISPRecord> SearchCaseISPRecord(int index, SearchCaseISPRecord SearchStructure) { AdministrationDataBase aDB = new AdministrationDataBase(); return aDB.SearchCaseISPRecord(index, SearchStructure, 0); }