private string SearchStudentServiceConditionReturn(SearchStudentService SearchStructure) { string ConditionReturn = ""; string DateBase = "1900-01-01"; if (SearchStructure.txtstudentID != null) { ConditionReturn += " AND CaseServiceRecord.StudentID=(@StudentID) "; } if (SearchStructure.txtstudentName != null) { ConditionReturn += " AND StudentDatabase.StudentName like (@StudentName) "; } if (SearchStructure.txtstudentSex != null && SearchStructure.txtstudentSex != "0") { ConditionReturn += " AND StudentDatabase.StudentSex=(@StudentSex) "; } if (SearchStructure.txtbirthdaystart != null && SearchStructure.txtbirthdayend != null && SearchStructure.txtbirthdaystart != DateBase && SearchStructure.txtbirthdayend != DateBase) { ConditionReturn += " AND StudentDatabase.StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) "; } if (SearchStructure.txtcaseStatu != null && SearchStructure.txtcaseStatu != "0") { ConditionReturn += " AND StudentDatabase.CaseStatu =(@CaseStatu) "; } if (SearchStructure.txtendviewstart != null && SearchStructure.txtendviewend != null && SearchStructure.txtendviewstart != DateBase && SearchStructure.txtendviewend != DateBase) { ConditionReturn += " AND CaseServiceRecord.ServiceDate BETWEEN (@sViewDateStart) AND (@sViewDateEnd) "; } 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 CaseServiceRecord.Unit =" + UserFile[2] + " "; } return ConditionReturn; }
public string[] SearchStudentServiceCount(SearchStudentService SearchStructure) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); string ConditionReturn = this.SearchStudentServiceConditionReturn(SearchStructure); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT COUNT(*) AS QCOUNT FROM CaseServiceRecord INNER JOIN StudentDatabase ON CaseServiceRecord.StudentID=StudentDatabase.StudentID " + "WHERE CaseServiceRecord.isDeleted=0 " + ConditionReturn; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID); cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = SearchStructure.txtstudentName + "%"; cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex); cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtcaseStatu); cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart); cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend); cmd.Parameters.Add("@sViewDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendviewstart); cmd.Parameters.Add("@sViewDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendviewend); returnValue[0] = cmd.ExecuteScalar().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] SearchStudentServiceDataBaseCount(SearchStudentService SearchStructure) { CaseDataBase SDB = new CaseDataBase(); if (int.Parse(SDB._StaffhaveRoles[3]) == 1) { return SDB.SearchStudentServiceCount(SearchStructure); } else { return new string[2] { _noRole, _errorMsg }; } }
public List<SearchStudentServiceResult> SearchStudentService(int indexpage, SearchStudentService SearchStructure) { List<SearchStudentServiceResult> returnValue = new List<SearchStudentServiceResult>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchStudentServiceConditionReturn(SearchStructure); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY StudentDatabase.ID DESC,ServiceDate DESC) " + "AS RowNum, StudentDatabase.StudentName ,StudentDatabase.CaseStatu, CaseServiceRecord.* " + "FROM CaseServiceRecord INNER JOIN StudentDatabase ON CaseServiceRecord.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " + "WHERE CaseServiceRecord.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("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID); cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = SearchStructure.txtstudentName + "%"; cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex); cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtcaseStatu); cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart); cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend); cmd.Parameters.Add("@sViewDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendviewstart); cmd.Parameters.Add("@sViewDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendviewend); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SearchStudentServiceResult addValue = new SearchStudentServiceResult(); addValue.ID = Int64.Parse(dr["ID"].ToString()); addValue.txtstudentID = dr["StudentID"].ToString(); addValue.txtstudentName = dr["StudentName"].ToString(); addValue.txtstudentStatu = dr["CaseStatu"].ToString(); addValue.txtviewData = DateTime.Parse(dr["ServiceDate"].ToString()).ToString("yyyy-MM-dd"); addValue.txtviewPeople = dr["Objects"].ToString(); addValue.txtviewStyle = dr["Method"].ToString(); addValue.txtviewTitle = dr["Theme"].ToString(); returnValue.Add(addValue); } Sqlconn.Close(); } catch (Exception e) { SearchStudentServiceResult addValue = new SearchStudentServiceResult(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message; returnValue.Add(addValue); } } return returnValue; }
public List<SearchStudentServiceResult> SearchStudentServiceDataBase(int index, SearchStudentService SearchStructure) { CaseDataBase SDB = new CaseDataBase(); return SDB.SearchStudentService(index, SearchStructure); }