public string[] CreatTeachServiceSupervisor(TeachServiceSupervisor sTemperatureData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; string strSql = ""; string strSqlPara = ""; foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": break; case "isDeleted": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "0 "; break; case "CreateFileDate": case "UpFileDate": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() "; break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name; break; } } DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); string sql = " insert into TeachServiceSupervisor ( " + strSql + " )values( " + strSqlPara + ") select @@identity as ID "; Sqlconn.Open(); SqlCommand cmd = new SqlCommand(sql, Sqlconn); //cmd.Parameters.Add("@AcademicYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData.AcademicYear.ToString()); //cmd.Parameters.Add("@VDID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sTemperatureData.ID.ToString()); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": break; case "CreateFileBy": case "UpFileBy": //cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData)); cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); break; case "ClassDate": if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, Convert.ToDateTime( fldInfo.GetValue(sTemperatureData).ToString()).AddYears(1911).ToShortDateString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, ""); } break; default: if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, ""); } break; } } SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { returnValue[0] = dr["ID"].ToString(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public List<TeachServiceSupervisor> SearchTeachServiceSupervisor(int indexpage, SearchStudent SearchStructure, int type) { List<TeachServiceSupervisor> returnValue = new List<TeachServiceSupervisor>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchVoiceDistanceCondition(SearchStructure, type); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.AcademicYear,'') DESC) as RowNum "; sql += " ,a.id, a.AcademicYear, convert(varchar, isnull(convert(date, a.ClassDate,1),'1912'), 111) as ClassDate,a.StudentAge,a.StudentMonth , b.StudentName , b.StudentBirthday "; sql += " FROM TeachServiceSupervisor a left join studentDatabase b on a.studentid = b.id "; sql += " WHERE 1=1 " + 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("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID); cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%"; cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex); 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("@AcademicYearstart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAcademicYearstart); cmd.Parameters.Add("@AcademicYearend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtAcademicYearend); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { TeachServiceSupervisor addValue = new TeachServiceSupervisor(); addValue.RowNum = dr["rownum"].ToString(); addValue.ID = dr["ID"].ToString(); addValue.ClassDate = dr["classDate"].ToString(); addValue.StudentName = dr["StudentName"].ToString(); addValue.AcademicYear = dr["AcademicYear"].ToString(); addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()); addValue.StudentAge = dr["studentAge"].ToString(); addValue.StudentMonth = dr["studentMonth"].ToString(); returnValue.Add(addValue); } Sqlconn.Close(); } catch (Exception e) { TeachServiceSupervisor addValue = new TeachServiceSupervisor(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message.ToString(); returnValue.Add(addValue); } } return returnValue; }
public List<AchievementAssessmentLoad> ShowTeachServiceSupervisor(int id) { List<AchievementAssessmentLoad> returnValue = new List<AchievementAssessmentLoad>(); string strSql = ""; TeachServiceSupervisor sTemperatureData = new TeachServiceSupervisor(); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": case "isDeleted": case "CreateFileDate": case "CreateFileBy": case "UpFileBy": case "UpFileDate": break; case "ClassDate": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "convert(varchar, isnull(convert(date, a." + fldInfo.Name + ",1),'1912'), 111) as " + fldInfo.Name; break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "a." + fldInfo.Name; break; } } DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = " SELECT " + strSql + " , b.StudentName ,c.TeacherName "; sql += " from TeachServiceSupervisor a "; sql += " left join (select id, studentName from 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 += " where 1=1 and a.ID = @ID "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", id); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { AchievementAssessmentLoad addValue = new AchievementAssessmentLoad(); addValue.IDname = "studentName"; addValue.ThisValue = dr["studentName"].ToString(); returnValue.Add(addValue); addValue.IDname = "teacherName"; addValue.ThisValue = dr["TeacherName"].ToString(); returnValue.Add(addValue); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": case "isDeleted": case "CreateFileDate": case "CreateFileBy": case "UpFileBy": case "UpFileDate": break; case "ClassDate": addValue.IDname = fldInfo.Name; addValue.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1); returnValue.Add(addValue); break; default: addValue.IDname = fldInfo.Name; addValue.ThisValue = dr[fldInfo.Name].ToString(); returnValue.Add(addValue); break; } } } Sqlconn.Close(); } catch (Exception e) { //AchievementAssessmentLoad addValue = new AchievementAssessmentLoad(); ////addValue.checkNo = "-1"; ////addValue.errorMsg = e.Message.ToString(); string ee = e.Message.ToString(); //returnValue.Add(addValue); } } return returnValue; }
public string[] UpdateTeachServiceSupervisor(TeachServiceSupervisor sTemperatureData) { AdministrationDataBase aDB = new AdministrationDataBase(); //aDB.caseBTFunction(); //if (int.Parse(aDB._StaffhaveRoles[2]) == 1) //{ return aDB.UpdateTeachServiceSupervisor(sTemperatureData); //} //else //{ // return null; //} }