public string[] setStudentTrackedDataBase(CreateStudentTracked StudentTracked) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()); using (Sqlconn) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "UPDATE StudentTrackedOffData SET StudentEmail=@StudentEmail, " + "Phone=@Phone, DisabilityCategory1=@DisabilityCategory1, DisabilityGrade1=@DisabilityGrade1, " + "DisabilityCategory2=@DisabilityCategory2, DisabilityGrade2=@DisabilityGrade2, DisabilityCategory3=@DisabilityCategory3, " + "DisabilityGrade3=@DisabilityGrade3, ElementarySchool=@ElementarySchool,"+ " ElementarySchoolName=@ElementarySchoolName, JuniorHighSchool=@JuniorHighSchool, " + "JuniorHighSchoolName=@JuniorHighSchoolName, HighSchool=@HighSchool, HighSchoolName=@HighSchoolName, University=@University, " + "UniversityName=@UniversityName, JobName=@JobName, OtherConditions=@OtherConditions, OtherConditionsName=@OtherConditionsName, " + "ElementarySY=@ElementarySY ,ElementarySM=@ElementarySM ,ElementaryEY=@ElementaryEY ,ElementaryEM=@ElementaryEM , " + "JuniorHighSY=@JuniorHighSY ,JuniorHighSM=@JuniorHighSM ,JuniorHighEY=@JuniorHighEY ,JuniorHighEM=@JuniorHighEM , " + "HighSY=@HighSY ,HighSM=@HighSM ,HighEY=@HighEY ,HighEM=@HighEM , " + "UniversitySY=@UniversitySY ,UniversitySM=@UniversitySM ,UniversityEY=@UniversityEY ,UniversityEM=@UniversityEM , " + "JobSY=@JobSY ,JobSM=@JobSM ,JobEY=@JobEY ,JobEM=@JobEM , " + "OtherSY=@OtherSY ,OtherSM=@OtherSM ,OtherEY=@OtherEY ,OtherEM=@OtherEM , remark=@remark, " + "UpFileBy=@UpFileBy, UpFileDate=(getDate()) " + "WHERE ID=@ID;"; sql += "UPDATE StudentDatabase set AddressZip1=@AddressZip1" + ",AddressCity1=@AddressCity1" + ",AddressOther1=@AddressOther1" + ",AddressZip2=@AddressZip2" + ",AddressCity2=@AddressCity2" + ",AddressOther2=@AddressOther2" + ",ContactRelation1=@ContactRelation1" + ",ContactName1=@ContactName1" + ",ContactTel_company1=@ContactTel_company1" + ",ContactTel_home1=@ContactTel_home1" + ",ContactPhone1=@ContactPhone1" + ",ContactFax1=@ContactFax1" + ",ContactRelation2=@ContactRelation2" + ",ContactName2=@ContactName2" + ",ContactTel_company2=@ContactTel_company2" + ",ContactTel_home2=@ContactTel_home2" + ",ContactPhone2=@ContactPhone2" + ",ContactFax2=@ContactFax2" + ",ContactRelation3=@ContactRelation3" + ",ContactName3=@ContactName3" + ",ContactTel_company3=@ContactTel_company3" + ",ContactTel_home3=@ContactTel_home3" + ",ContactPhone3=@ContactPhone3" + ",ContactFax3=@ContactFax3" + ",ContactRelation4=@ContactRelation4" + ",ContactName4=@ContactName4" + ",ContactTel_company4=@ContactTel_company4" + ",ContactTel_home4=@ContactTel_home4" + ",ContactPhone4=@ContactPhone4" + ",ContactFax4=@ContactFax4" + ",StudentEmail=@StudentEmail" + " WHERE StudentID=@StudentID;"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentTracked.ID); cmd.Parameters.Add("@StudentEmail", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.email); cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.Tel); cmd.Parameters.Add("@DisabilityCategory1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualCategory1); cmd.Parameters.Add("@DisabilityGrade1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualGrade1); cmd.Parameters.Add("@DisabilityCategory2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualCategory2); cmd.Parameters.Add("@DisabilityGrade2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualGrade2); cmd.Parameters.Add("@DisabilityCategory3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualCategory3); cmd.Parameters.Add("@DisabilityGrade3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualGrade3); cmd.Parameters.Add("@ElementarySchool", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.esType); cmd.Parameters.Add("@ElementarySchoolName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.esName); cmd.Parameters.Add("@JuniorHighSchool", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.jsType); cmd.Parameters.Add("@JuniorHighSchoolName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.jsName); cmd.Parameters.Add("@HighSchool", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.hsType); cmd.Parameters.Add("@HighSchoolName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.hsName); cmd.Parameters.Add("@University", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.uType); cmd.Parameters.Add("@UniversityName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.uName); cmd.Parameters.Add("@JobName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.jobUnit); cmd.Parameters.Add("@OtherConditions", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.otherType); cmd.Parameters.Add("@OtherConditionsName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.otherName); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@ElementarySY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ElementarySY); cmd.Parameters.Add("@ElementarySM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ElementarySM); cmd.Parameters.Add("@ElementaryEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ElementaryEY); cmd.Parameters.Add("@ElementaryEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ElementaryEM); cmd.Parameters.Add("@JuniorHighSY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JuniorHighSY); cmd.Parameters.Add("@JuniorHighSM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JuniorHighSM); cmd.Parameters.Add("@JuniorHighEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JuniorHighEY); cmd.Parameters.Add("@JuniorHighEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JuniorHighEM); cmd.Parameters.Add("@HighSY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.HighSY); cmd.Parameters.Add("@HighSM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.HighSM); cmd.Parameters.Add("@HighEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.HighEY); cmd.Parameters.Add("@HighEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.HighEM); cmd.Parameters.Add("@UniversitySY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.UniversitySY); cmd.Parameters.Add("@UniversitySM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.UniversitySM); cmd.Parameters.Add("@UniversityEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.UniversityEY); cmd.Parameters.Add("@UniversityEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.UniversityEM); cmd.Parameters.Add("@JobSY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JobSY); cmd.Parameters.Add("@JobSM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JobSM); cmd.Parameters.Add("@JobEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JobEY); cmd.Parameters.Add("@JobEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JobEM); cmd.Parameters.Add("@OtherSY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.OtherSY); cmd.Parameters.Add("@OtherSM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.OtherSM); cmd.Parameters.Add("@OtherEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.OtherEY); cmd.Parameters.Add("@OtherEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.OtherEM); cmd.Parameters.Add("@remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.remark); cmd.Parameters.Add("@AddressZip1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.censusAddressZip); cmd.Parameters.Add("@AddressCity1", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.censusAddressCity); cmd.Parameters.Add("@AddressOther1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.censusAddress); cmd.Parameters.Add("@AddressZip2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.addressZip); cmd.Parameters.Add("@AddressCity2", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.addressCity); cmd.Parameters.Add("@AddressOther2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.address); cmd.Parameters.Add("@ContactRelation1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPRelation1); cmd.Parameters.Add("@ContactName1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPName1); cmd.Parameters.Add("@ContactTel_company1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPTel1); cmd.Parameters.Add("@ContactTel_home1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPHPhone1); cmd.Parameters.Add("@ContactPhone1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPPhone1); cmd.Parameters.Add("@ContactFax1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPFax1); cmd.Parameters.Add("@ContactRelation2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPRelation2); cmd.Parameters.Add("@ContactName2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPName2); cmd.Parameters.Add("@ContactTel_company2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPTel2); cmd.Parameters.Add("@ContactTel_home2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPHPhone2); cmd.Parameters.Add("@ContactPhone2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPPhone2); cmd.Parameters.Add("@ContactFax2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPFax2); cmd.Parameters.Add("@ContactRelation3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPRelation3); cmd.Parameters.Add("@ContactName3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPName3); cmd.Parameters.Add("@ContactTel_company3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPTel3); cmd.Parameters.Add("@ContactTel_home3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPHPhone3); cmd.Parameters.Add("@ContactPhone3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPPhone3); cmd.Parameters.Add("@ContactFax3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPFax3); cmd.Parameters.Add("@ContactRelation4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPRelation4); cmd.Parameters.Add("@ContactName4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPName4); cmd.Parameters.Add("@ContactTel_company4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPTel4); cmd.Parameters.Add("@ContactTel_home4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPHPhone4); cmd.Parameters.Add("@ContactPhone4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPPhone4); cmd.Parameters.Add("@ContactFax4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.fPFax4); // cmd.Parameters.Add("@StudentEmail", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.email); cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.studentID); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message; } } return returnValue; }
public string[] createStudentTrackedDataBase(CreateStudentTracked StudentTracked) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()); using (Sqlconn) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "INSERT INTO StudentTrackedOffData(Unit, StudentID, StudentEmail, StudentIdentity, " + " Phone, DisabilityCategory1, DisabilityGrade1, DisabilityCategory2, DisabilityGrade2, DisabilityCategory3, DisabilityGrade3, " + //"HearingAids_R, AidsBrand_R, AidsModel_R, AidsOptionalTime_R, AidsOptionalLocation_R, EEarHospital_R, EEarOpen_R, HearingAids_L, AidsBrand_L, "+ //"AidsModel_L, AidsOptionalTime_L, AidsOptionalLocation_L, EEarHospital_L, EEarOpen_L, "ElementarySchool, ElementarySchoolName, JuniorHighSchool, " + "JuniorHighSchoolName, HighSchool, HighSchoolName, University, UniversityName, JobName, OtherConditions, OtherConditionsName, CreateFileBy,"+ " UpFileBy, UpFileDate,ElementarySY,ElementarySM,ElementaryEY,ElementaryEM ,JuniorHighSY,JuniorHighSM,JuniorHighEY,JuniorHighEM,HighSY "+ ",HighSM,HighEY,HighEM ,UniversitySY,UniversitySM,UniversityEY,UniversityEM,JobSY,JobSM,JobEY,JobEM,OtherSY,OtherSM,OtherEY,OtherEM,remark ) " + "VALUES (@Unit, @StudentID, @StudentEmail ,@StudentIdentity, @Phone, " + "@DisabilityCategory1, @DisabilityGrade1, @DisabilityCategory2, @DisabilityGrade2, @DisabilityCategory3, @DisabilityGrade3, "+ //"@AidsBrand_R, @AidsModel_R, @AidsOptionalTime_R, @AidsOptionalLocation_R, @EEarHospital_R, @EEarOpen_R, @HearingAids_L, @AidsBrand_L, "+ // "@AidsModel_L, @AidsOptionalTime_L, @AidsOptionalLocation_L, @EEarHospital_L, @EEarOpen_L, "@ElementarySchool, @ElementarySchoolName, " + "@JuniorHighSchool, @JuniorHighSchoolName, @HighSchool, @HighSchoolName, @University, @UniversityName, @JobName, @OtherConditions, "+ "@OtherConditionsName, @CreateFileBy, @UpFileBy, (getDate()),@ElementarySY,@ElementarySM,@ElementaryEY,@ElementaryEM ,@JuniorHighSY,@JuniorHighSM,@JuniorHighEY,@JuniorHighEM,@HighSY " + ",@HighSM,@HighEY,@HighEM ,@UniversitySY,@UniversitySM,@UniversityEY,@UniversityEM,@JobSY,@JobSM,@JobEY,@JobEM,@OtherSY,@OtherSM,@OtherEY,@OtherEM,@remark ) "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]); cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.studentID); cmd.Parameters.Add("@StudentIdentity", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.StudentIdentity); cmd.Parameters.Add("@StudentEmail", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.email); cmd.Parameters.Add("@AddressZip2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.addressZip); cmd.Parameters.Add("@AddressCity2", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.addressCity); cmd.Parameters.Add("@AddressOther2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.address); cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.Tel); cmd.Parameters.Add("@DisabilityCategory1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualCategory1); cmd.Parameters.Add("@DisabilityGrade1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualGrade1); cmd.Parameters.Add("@DisabilityCategory2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualCategory2); cmd.Parameters.Add("@DisabilityGrade2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualGrade2); cmd.Parameters.Add("@DisabilityCategory3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualCategory3); cmd.Parameters.Add("@DisabilityGrade3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualGrade3); cmd.Parameters.Add("@HearingAids_R", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.assistmanageR); cmd.Parameters.Add("@AidsBrand_R", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.brandR); cmd.Parameters.Add("@AidsModel_R", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.modelR); cmd.Parameters.Add("@AidsOptionalTime_R", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.buyingtimeR); cmd.Parameters.Add("@AidsOptionalLocation_R", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.buyingPlaceR); cmd.Parameters.Add("@EEarHospital_R", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.insertHospitalR); cmd.Parameters.Add("@EEarOpen_R", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.openHzDateR); cmd.Parameters.Add("@HearingAids_L", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.assistmanageL); cmd.Parameters.Add("@AidsBrand_L", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.brandL); cmd.Parameters.Add("@AidsModel_L", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.modelL); cmd.Parameters.Add("@AidsOptionalTime_L", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.buyingtimeL); cmd.Parameters.Add("@AidsOptionalLocation_L", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.buyingPlaceL); cmd.Parameters.Add("@EEarHospital_L", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.insertHospitalL); cmd.Parameters.Add("@EEarOpen_L", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.openHzDateL); cmd.Parameters.Add("@ElementarySchool", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.esType); cmd.Parameters.Add("@ElementarySchoolName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.esName); cmd.Parameters.Add("@JuniorHighSchool", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.jsType); cmd.Parameters.Add("@JuniorHighSchoolName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.jsName); cmd.Parameters.Add("@HighSchool", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.hsType); cmd.Parameters.Add("@HighSchoolName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.hsName); cmd.Parameters.Add("@University", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.uType); cmd.Parameters.Add("@UniversityName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.uName); cmd.Parameters.Add("@JobName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.jobUnit); cmd.Parameters.Add("@OtherConditions", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.otherType); cmd.Parameters.Add("@OtherConditionsName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.otherName); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@ElementarySY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ElementarySY); cmd.Parameters.Add("@ElementarySM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ElementarySM); cmd.Parameters.Add("@ElementaryEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ElementaryEY); cmd.Parameters.Add("@ElementaryEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ElementaryEM); cmd.Parameters.Add("@JuniorHighSY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JuniorHighSY); cmd.Parameters.Add("@JuniorHighSM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JuniorHighSM); cmd.Parameters.Add("@JuniorHighEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JuniorHighEY); cmd.Parameters.Add("@JuniorHighEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JuniorHighEM); cmd.Parameters.Add("@HighSY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.HighSY); cmd.Parameters.Add("@HighSM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.HighSM); cmd.Parameters.Add("@HighEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.HighEY); cmd.Parameters.Add("@HighEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.HighEM); cmd.Parameters.Add("@UniversitySY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.UniversitySY); cmd.Parameters.Add("@UniversitySM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.UniversitySM); cmd.Parameters.Add("@UniversityEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.UniversityEY); cmd.Parameters.Add("@UniversityEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.UniversityEM); cmd.Parameters.Add("@JobSY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JobSY); cmd.Parameters.Add("@JobSM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JobSM); cmd.Parameters.Add("@JobEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JobEY); cmd.Parameters.Add("@JobEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.JobEM); cmd.Parameters.Add("@OtherSY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.OtherSY); cmd.Parameters.Add("@OtherSM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.OtherSM); cmd.Parameters.Add("@OtherEY", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.OtherEY); cmd.Parameters.Add("@OtherEM", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.OtherEM); cmd.Parameters.Add("@remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.remark); returnValue[0] = cmd.ExecuteNonQuery().ToString(); if (returnValue[0] != "0") { sql = "select IDENT_CURRENT('StudentTrackedOffData') AS cID"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { returnValue[1] = dr["cID"].ToString(); } dr.Close(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message; } } return returnValue; }
public string[] setStudentBaseData(CreateStudent StudentData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = ""; DataBase Base = new DataBase(); int CaseStatu = this.CaseStatusFunction(StudentData); string upPhoto = ""; if (StudentData.studentPhoto != null) { upPhoto += " StudentAvatar=@StudentAvatar, "; } if (StudentData.studentManualImg != null) { upPhoto += " DisabilityProve=@DisabilityProve, "; } StudentData1 oldStudentDataBase1=this.getStudentData1(StudentData.ID); StudentHearingInformation oldStudentDataBase5 = this.getStudentHearingInfo(StudentData.ID); #region 離會生&轉通知 if (oldStudentDataBase1.endReasonDate == "1900-01-01" && oldStudentDataBase1.endReasonDate != Chk.CheckStringFunction(StudentData.endReasonDate) && Chk.CheckStringFunction(StudentData.endReasonDate).Length > 0) { /*已結案轉入離會生*/ CreateStudentTracked StudentTracked = new CreateStudentTracked(); StudentTracked.sUnit = StudentData.Unit; StudentTracked.studentID = StudentData.studentID; StudentTracked.StudentIdentity = StudentData.studentTWID; StudentTracked.email = StudentData.email; StudentTracked.addressZip = StudentData.addressZip; StudentTracked.addressCity = StudentData.addressCity; StudentTracked.address = StudentData.address; StudentTracked.Tel = StudentData.fPHPhone2; StudentTracked.manualCategory1 = StudentData.manualCategory1; StudentTracked.manualGrade1 = StudentData.manualGrade1; StudentTracked.manualCategory2 = StudentData.manualCategory2; StudentTracked.manualGrade2 = StudentData.manualGrade2; StudentTracked.manualCategory3 = StudentData.manualCategory3; StudentTracked.manualGrade3 = StudentData.manualGrade3; StudentTracked.assistmanageR = oldStudentDataBase5.assistmanageR; StudentTracked.brandR = oldStudentDataBase5.brandR; StudentTracked.modelR = oldStudentDataBase5.modelR; StudentTracked.buyingtimeR = oldStudentDataBase5.buyingtimeR; StudentTracked.buyingPlaceR = oldStudentDataBase5.buyingPlaceR; StudentTracked.insertHospitalR = oldStudentDataBase5.insertHospitalR; StudentTracked.openHzDateR = oldStudentDataBase5.openHzDateR; StudentTracked.assistmanageL = oldStudentDataBase5.assistmanageL; StudentTracked.brandL = oldStudentDataBase5.brandL; StudentTracked.modelL = oldStudentDataBase5.modelL; StudentTracked.buyingtimeL = oldStudentDataBase5.buyingtimeL; StudentTracked.buyingPlaceL = oldStudentDataBase5.buyingPlaceL; StudentTracked.insertHospitalL = oldStudentDataBase5.insertHospitalL; StudentTracked.openHzDateL = oldStudentDataBase5.openHzDateL; this.createStudentTrackedDataBase(StudentTracked); OtherDataBase oDB = new OtherDataBase(); StaffDataBase sDB = new StaffDataBase(); List<int> item = new List<int>(); //</option><option value="20">主任/教師</option><option value="21">主任/聽力師</option><option value="22">主任/社工</option></select> item.Add(11);//主任 item.Add(20);//主任/教師 item.Add(21);//主任/聽力師 item.Add(22);//主任/社工 item.Add(17);//社工管理長 item.Add(18);//社工 int[] days = { 90, 180, 365 }; List<StaffDataList> SDL = sDB.getAllStaffDataList(item); foreach (StaffDataList atom in SDL) { if (atom.sUnit == StudentData.Unit) { for (int i = 0; i < 3; i++) { CreateRemind Remind = new CreateRemind(); Remind.executionDate = Chk.CheckStringtoDateFunction(StudentData.endReasonDate).AddDays(days[i] - 5).ToShortDateString(); Remind.recipientID = atom.sID; Remind.executionContent = StudentData.studentName + " 離會第 " + days[i].ToString() + " 天追蹤個案近況"; //cmd.Parameters.Add("@Executor", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RemindSystemData.recipientID); //cmd.Parameters.Add("@RemindContent", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(RemindSystemData.executionContent); //cmd.Parameters.Add("@RemindDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.executionDate); //cmd.Parameters.Add("@CompleteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.fulfillmentDate); oDB.CreateRemindSystem(Remind); } } } item.Clear(); item.Add(3);//行政組長 SDL = sDB.getAllStaffDataListRemindlist(item); foreach (StaffDataList atom in SDL) { CreateRemind Remind = new CreateRemind(); Remind.executionDate = Chk.CheckStringtoDateFunction(StudentData.endReasonDate).AddDays(360).ToShortDateString(); Remind.recipientID = atom.sID; Remind.executionContent = StudentData.studentName + " 離會第 365 天"; //cmd.Parameters.Add("@Executor", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RemindSystemData.recipientID); //cmd.Parameters.Add("@RemindContent", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(RemindSystemData.executionContent); //cmd.Parameters.Add("@RemindDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.executionDate); //cmd.Parameters.Add("@CompleteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.fulfillmentDate); oDB.CreateRemindSystem(Remind); } item.Clear(); item.Add(19);//中心行政 SDL = sDB.getAllStaffDataList(item); foreach (StaffDataList atom in SDL) { if (atom.sUnit == StudentData.Unit) { CreateRemind Remind = new CreateRemind(); Remind.executionDate = Chk.CheckStringtoDateFunction(StudentData.endReasonDate).AddDays(25).ToShortDateString(); Remind.recipientID = atom.sID; Remind.executionContent = StudentData.studentName + " 結案30天要完成退保證金:" + Chk.CheckStringtoDateFunction(StudentData.endReasonDate).AddDays(30).ToShortDateString() ; //cmd.Parameters.Add("@Executor", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(RemindSystemData.recipientID); //cmd.Parameters.Add("@RemindContent", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(RemindSystemData.executionContent); //cmd.Parameters.Add("@RemindDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.executionDate); //cmd.Parameters.Add("@CompleteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(RemindSystemData.fulfillmentDate); oDB.CreateRemindSystem(Remind); } } } #endregion #region 轉換成通知系統 //轉通知系統記錄) //評估日期 assessDate if (oldStudentDataBase1.assessDate == "1900-01-01" && oldStudentDataBase1.assessDate != Chk.CheckStringFunction(StudentData.assessDate) && Chk.CheckStringFunction(StudentData.assessDate).Length > 0) { List<int> item = new List<int>(); item.Add(17);//社工管理長 item.Add(18);//社工 item.Add(19);//中心行政 OtherDataBase oDB = new OtherDataBase(); StaffDataBase sDB = new StaffDataBase(); List<StaffDataList> SDL = sDB.getAllStaffDataList(item); foreach (StaffDataList atom in SDL) { if (atom.sUnit == StudentData.Unit) { CreateRemind Remind = new CreateRemind(); Remind.executionDate = Chk.CheckStringtoDateFunction(StudentData.assessDate).AddDays(-5).ToShortDateString(); Remind.recipientID = atom.sID; Remind.executionContent = StudentData.studentName + " 排定評估日期收咨詢費: " + Chk.CheckStringtoDateFunction(StudentData.assessDate).ToShortDateString() + " "; oDB.CreateRemindSystem(Remind); } } } //首次上課 firstClassDate if (oldStudentDataBase1.firstClassDate == "1900-01-01" && oldStudentDataBase1.firstClassDate != Chk.CheckStringFunction(StudentData.firstClassDate) && Chk.CheckStringFunction(StudentData.firstClassDate).Length > 0) { List<int> item = new List<int>(); item.Add(11);//主任 item.Add(20);//主任/教師 item.Add(21);//主任/聽力師 item.Add(22);//主任/社工 item.Add(15);//聽力師管理長 item.Add(16);//聽力師 item.Add(17);//社工管理長 item.Add(18);//社工 OtherDataBase oDB = new OtherDataBase(); StaffDataBase sDB = new StaffDataBase(); List<StaffDataList> SDL = sDB.getAllStaffDataList(item); foreach (StaffDataList atom in SDL) { if (atom.sUnit == StudentData.Unit) { CreateRemind Remind = new CreateRemind(); Remind.executionDate = Chk.CheckStringtoDateFunction(StudentData.firstClassDate).AddDays(25).ToShortDateString(); Remind.recipientID = atom.sID; Remind.executionContent = StudentData.studentName + " 首次上課30天內要完家訪和 ISP,首次上課日期 : " + Chk.CheckStringtoDateFunction(StudentData.firstClassDate).ToShortDateString() + " "; oDB.CreateRemindSystem(Remind); } } } #endregion SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()); using (Sqlconn) { try { Sqlconn.Open(); StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); string sql = "UPDATE StudentDatabase SET Unit=@Unit,EvaluationDate=@EvaluationDate, ConsultDate=@ConsultDate, Updated=(getdate()), CaseStatu=@CaseStatu, " + "StudentName=@StudentName, StudentIdentity=@StudentIdentity, AddressZip1=@AddressZip1, " + "AddressCity1=@AddressCity1, AddressOther1=@AddressOther1, AddressZip2=@AddressZip2, AddressCity2=@AddressCity2, AddressOther2=@AddressOther2, " + "StudentBirthday=@StudentBirthday, StudentSex=@StudentSex, ClassDate=@ClassDate, GuaranteeDate= @GuaranteeDate, BackGuaranteeDate=@BackGuaranteeDate, CompletedDate=@CompletedDate, " + "CompletedType=@CompletedType, CompletedReason=@CompletedReason, ShortEndDateSince=@ShortEndDateSince, ShortEndDateUntil=@ShortEndDateUntil, " + "NomembershipType=@NomembershipType, NomembershipReason=@NomembershipReason, CaregiversDaytime=@CaregiversDaytime, " + "CaregiversDaytimeText=@CaregiversDaytimeText, CaregiversNight=@CaregiversNight, CaregiversNightText=@CaregiversNightText, ContactRelation1=@ContactRelation1, " + "ContactName1=@ContactName1, ContactTel_company1=@ContactTel_company1, ContactTel_home1=@ContactTel_home1, ContactPhone1=@ContactPhone1, " + "ContactFax1=@ContactFax1, ContactRelation2=@ContactRelation2, ContactName2=@ContactName2, ContactTel_company2=@ContactTel_company2, " + "ContactTel_home2=@ContactTel_home2, ContactPhone2=@ContactPhone2, ContactFax2=@ContactFax2, ContactRelation3=@ContactRelation3, ContactName3=@ContactName3, " + "ContactTel_company3=@ContactTel_company3, ContactTel_home3=@ContactTel_home3, ContactPhone3=@ContactPhone3, ContactFax3=@ContactFax3, " + "ContactRelation4=@ContactRelation4, ContactName4=@ContactName4, ContactTel_company4=@ContactTel_company4, ContactTel_home4=@ContactTel_home4, " + "ContactPhone4=@ContactPhone4, ContactFax4=@ContactFax4, StudentEmail=@StudentEmail, ReferralSourceType=@ReferralSourceType, ReferralSource=@ReferralSource, " + "PhysicalAndMentalDisabilityHandbook=@PhysicalAndMentalDisabilityHandbook, DisabilityCategory1= @DisabilityCategory1, DisabilityGrade1=@DisabilityGrade1, " + "DisabilityCategory2=@DisabilityCategory2, DisabilityGrade2=@DisabilityGrade2, DisabilityCategory3=@DisabilityCategory3, DisabilityGrade3=@DisabilityGrade3, " + "NoDisabilityHandbook=@NoDisabilityHandbook, ApplyDisabilityHandbook=@ApplyDisabilityHandbook, Notify=@Notify, " + "Notify_Unit=@Notify_Unit, Notify_Member=@Notify_Member, Notify_Tel=@Notify_Tel, Notify_Date=@Notify_Date, Notify_City=@Notify_City, EconomyState=@EconomyState, " + "EconomyLow=@EconomyLow ," + upPhoto + " UpFileBy=@UpFileBy, UpFileDate=(getDate()) " + "WHERE StudentID=@StudentID AND ID=@ID"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = StudentData.ID; cmd.Parameters.Add("@Unit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentData.Unit); cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.studentID); cmd.Parameters.Add("@EvaluationDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.assessDate); cmd.Parameters.Add("@ConsultDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.consultationDate); cmd.Parameters.Add("@Updated", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.upDate); cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = Chk.CheckIntFunction(CaseStatu); //cmd.Parameters.Add("@WriteName", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentData.fillInName); cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.studentName); cmd.Parameters.Add("@StudentIdentity", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.studentTWID); cmd.Parameters.Add("@AddressZip1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.censusAddressZip); cmd.Parameters.Add("@AddressCity1", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.censusAddressCity); cmd.Parameters.Add("@AddressOther1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.censusAddress); cmd.Parameters.Add("@AddressZip2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.addressZip); cmd.Parameters.Add("@AddressCity2", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.addressCity); cmd.Parameters.Add("@AddressOther2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.address); cmd.Parameters.Add("@StudentBirthday", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.studentbirthday); cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.studentSex); cmd.Parameters.Add("@ClassDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.firstClassDate); cmd.Parameters.Add("@GuaranteeDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.guaranteeDate); cmd.Parameters.Add("@BackGuaranteeDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.BackGuaranteeDate); cmd.Parameters.Add("@CompletedDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.endReasonDate); cmd.Parameters.Add("@CompletedType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.endReasonType); cmd.Parameters.Add("@CompletedReason", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.endReason); cmd.Parameters.Add("@ShortEndDateSince", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.sendDateSince); cmd.Parameters.Add("@ShortEndDateUntil", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.sendDateUntil); cmd.Parameters.Add("@NomembershipType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.nomembershipType); cmd.Parameters.Add("@NomembershipReason", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.nomembershipReason); cmd.Parameters.Add("@StudentAvatar", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.studentPhoto); cmd.Parameters.Add("@CaregiversDaytime", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.wCare); cmd.Parameters.Add("@CaregiversDaytimeText", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.wCareName); cmd.Parameters.Add("@CaregiversNight", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.bCare); cmd.Parameters.Add("@CaregiversNightText", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.bCareName); cmd.Parameters.Add("@ContactRelation1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPRelation1); cmd.Parameters.Add("@ContactName1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPName1); cmd.Parameters.Add("@ContactTel_company1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPTel1); cmd.Parameters.Add("@ContactPhone1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPPhone1); cmd.Parameters.Add("@ContactTel_home1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPHPhone1); cmd.Parameters.Add("@ContactFax1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPFax1); cmd.Parameters.Add("@ContactRelation2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPRelation2); cmd.Parameters.Add("@ContactName2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPName2); cmd.Parameters.Add("@ContactTel_company2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPTel2); cmd.Parameters.Add("@ContactPhone2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPPhone2); cmd.Parameters.Add("@ContactTel_home2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPHPhone2); cmd.Parameters.Add("@ContactFax2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPFax2); cmd.Parameters.Add("@ContactRelation3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPRelation3); cmd.Parameters.Add("@ContactName3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPName3); cmd.Parameters.Add("@ContactTel_company3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPTel3); cmd.Parameters.Add("@ContactPhone3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPPhone3); cmd.Parameters.Add("@ContactTel_home3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPHPhone3); cmd.Parameters.Add("@ContactFax3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPFax3); cmd.Parameters.Add("@ContactRelation4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPRelation4); cmd.Parameters.Add("@ContactName4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPName4); cmd.Parameters.Add("@ContactTel_company4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPTel4); cmd.Parameters.Add("@ContactPhone4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPPhone4); cmd.Parameters.Add("@ContactTel_home4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPHPhone4); cmd.Parameters.Add("@ContactFax4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.fPFax4); cmd.Parameters.Add("@StudentEmail", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.email); cmd.Parameters.Add("@ReferralSourceType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.sourceType); cmd.Parameters.Add("@ReferralSource", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.sourceName); cmd.Parameters.Add("@PhysicalAndMentalDisabilityHandbook", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.manualWhether); cmd.Parameters.Add("@DisabilityCategory1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.manualCategory1); cmd.Parameters.Add("@DisabilityGrade1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.manualGrade1); cmd.Parameters.Add("@DisabilityCategory2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.manualCategory2); cmd.Parameters.Add("@DisabilityGrade2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.manualGrade2); cmd.Parameters.Add("@DisabilityCategory3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.manualCategory3); cmd.Parameters.Add("@DisabilityGrade3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.manualGrade3); cmd.Parameters.Add("@NoDisabilityHandbook", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.manualNo); cmd.Parameters.Add("@ApplyDisabilityHandbook", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.manualUnit); cmd.Parameters.Add("@DisabilityProve", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.studentManualImg); cmd.Parameters.Add("@Notify", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.notificationWhether); cmd.Parameters.Add("@Notify_Unit", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.notificationUnit); cmd.Parameters.Add("@Notify_Member", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.notificationManage); cmd.Parameters.Add("@Notify_Tel", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentData.notificationTel); cmd.Parameters.Add("@Notify_Date", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentData.notificationDate); cmd.Parameters.Add("@Notify_City", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.notificationCity); cmd.Parameters.Add("@EconomyState", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.economy); cmd.Parameters.Add("@EconomyLow", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentData.economyLow); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { string error = e.Message; returnValue[0] = "-1"; returnValue[1] = e.Message; } } return returnValue; }
public CreateStudentTracked getStudentTrackedDataBase(Int64 tID) { CreateStudentTracked returnValue = new CreateStudentTracked(); returnValue.Teack = new List<TeackedData>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT StudentTrackedOffData.*, StudentDatabase.* " + "FROM StudentTrackedOffData INNER JOIN StudentDatabase ON StudentTrackedOffData.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " + "WHERE StudentTrackedOffData.isDeleted=0 AND StudentTrackedOffData.ID=@ID"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = tID; SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { returnValue.ID = dr["ID"].ToString(); returnValue.sUnit = dr["Unit"].ToString(); returnValue.studentID = dr["StudentID"].ToString(); returnValue.studentName = dr["StudentName"].ToString(); returnValue.studentSex = dr["StudentSex"].ToString(); returnValue.studentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()).ToString("yyyy-MM-dd"); returnValue.endReasonDate = DateTime.Parse(dr["CompletedDate"].ToString()).ToString("yyyy-MM-dd"); returnValue.firstClassDate = DateTime.Parse(dr["ClassDate"].ToString()).ToString("yyyy-MM-dd"); returnValue.email = dr["StudentEmail"].ToString(); returnValue.censusAddressZip = dr["AddressZip1"].ToString(); returnValue.censusAddressCity = dr["AddressCity1"].ToString(); returnValue.censusAddress = dr["AddressOther1"].ToString(); returnValue.addressZip = dr["AddressZip2"].ToString(); returnValue.addressCity = dr["AddressCity2"].ToString(); returnValue.address = dr["AddressOther2"].ToString(); returnValue.Tel = dr["Phone"].ToString(); returnValue.manualCategory1 = dr["DisabilityCategory1"].ToString(); returnValue.manualGrade1 = dr["DisabilityGrade1"].ToString(); returnValue.manualCategory2 = dr["DisabilityCategory2"].ToString(); returnValue.manualGrade2 = dr["DisabilityGrade2"].ToString(); returnValue.manualCategory3 = dr["DisabilityCategory3"].ToString(); returnValue.manualGrade3 = dr["DisabilityGrade3"].ToString(); returnValue.assistmanageR = dr["HearingAids_R"].ToString(); returnValue.brandR = dr["AidsBrand_R"].ToString(); returnValue.modelR = dr["AidsModel_R"].ToString(); returnValue.buyingPlaceR = dr["AidsOptionalLocation_R"].ToString(); returnValue.buyingtimeR = DateTime.Parse(dr["AidsOptionalTime_R"].ToString()).ToString("yyyy-MM-dd"); returnValue.insertHospitalR = dr["EEarHospital_R"].ToString(); returnValue.openHzDateR = DateTime.Parse(dr["EEarOpen_R"].ToString()).ToString("yyyy-MM-dd"); returnValue.assistmanageL = dr["HearingAids_L"].ToString(); returnValue.brandL = dr["AidsBrand_L"].ToString(); returnValue.modelL = dr["AidsModel_L"].ToString(); returnValue.buyingtimeL = DateTime.Parse(dr["AidsOptionalTime_L"].ToString()).ToString("yyyy-MM-dd"); returnValue.buyingPlaceL = dr["AidsOptionalLocation_L"].ToString(); returnValue.insertHospitalL = dr["EEarHospital_L"].ToString(); returnValue.openHzDateL = DateTime.Parse(dr["EEarOpen_L"].ToString()).ToString("yyyy-MM-dd"); returnValue.esType = dr["ElementarySchool"].ToString(); returnValue.esName = dr["ElementarySchoolName"].ToString(); returnValue.jsType = dr["JuniorHighSchool"].ToString(); returnValue.jsName = dr["JuniorHighSchoolName"].ToString(); returnValue.hsType = dr["HighSchool"].ToString(); returnValue.hsName = dr["HighSchoolName"].ToString(); returnValue.uType = dr["University"].ToString(); returnValue.uName = dr["UniversityName"].ToString(); returnValue.jobUnit = dr["JobName"].ToString(); returnValue.otherType = dr["OtherConditions"].ToString(); returnValue.otherName = dr["OtherConditionsName"].ToString(); returnValue.fPRelation1 = dr["ContactRelation1"].ToString(); returnValue.fPName1 = dr["ContactName1"].ToString(); returnValue.fPTel1 = dr["ContactTel_company1"].ToString(); returnValue.fPPhone1 = dr["ContactPhone1"].ToString(); returnValue.fPHPhone1 = dr["ContactTel_home1"].ToString(); returnValue.fPFax1 = dr["ContactFax1"].ToString(); returnValue.fPRelation2 = dr["ContactRelation2"].ToString(); returnValue.fPName2 = dr["ContactName2"].ToString(); returnValue.fPTel2 = dr["ContactTel_company2"].ToString(); returnValue.fPPhone2 = dr["ContactPhone2"].ToString(); returnValue.fPHPhone2 = dr["ContactTel_home2"].ToString(); returnValue.fPFax2 = dr["ContactFax2"].ToString(); returnValue.fPRelation3 = dr["ContactRelation3"].ToString(); returnValue.fPName3 = dr["ContactName3"].ToString(); returnValue.fPTel3 = dr["ContactTel_company3"].ToString(); returnValue.fPPhone3 = dr["ContactPhone3"].ToString(); returnValue.fPHPhone3 = dr["ContactTel_home3"].ToString(); returnValue.fPFax3 = dr["ContactFax3"].ToString(); returnValue.fPRelation4 = dr["ContactRelation4"].ToString(); returnValue.fPName4 = dr["ContactName4"].ToString(); returnValue.fPTel4 = dr["ContactTel_company4"].ToString(); returnValue.fPPhone4 = dr["ContactPhone4"].ToString(); returnValue.fPHPhone4 = dr["ContactTel_home4"].ToString(); returnValue.fPFax4 = dr["ContactFax4"].ToString(); returnValue.ElementarySY = dr["ElementarySY"].ToString(); returnValue.ElementarySM = dr["ElementarySM"].ToString(); returnValue.ElementaryEY = dr["ElementaryEY"].ToString(); returnValue.ElementaryEM = dr["ElementaryEM"].ToString(); returnValue.JuniorHighSY = dr["JuniorHighSY"].ToString(); returnValue.JuniorHighSM = dr["JuniorHighSM"].ToString(); returnValue.JuniorHighEY = dr["JuniorHighEY"].ToString(); returnValue.JuniorHighEM = dr["JuniorHighEM"].ToString(); returnValue.HighSY = dr["HighSY"].ToString(); returnValue.HighSM = dr["HighSM"].ToString(); returnValue.HighEY = dr["HighEY"].ToString(); returnValue.HighEM = dr["HighEM"].ToString(); returnValue.UniversitySY = dr["UniversitySY"].ToString(); returnValue.UniversitySM = dr["UniversitySM"].ToString(); returnValue.UniversityEY = dr["UniversityEY"].ToString(); returnValue.UniversityEM = dr["UniversityEM"].ToString(); returnValue.JobSY = dr["JobSY"].ToString(); returnValue.JobSM = dr["JobSM"].ToString(); returnValue.JobEY = dr["JobEY"].ToString(); returnValue.JobEM = dr["JobEM"].ToString(); returnValue.OtherSY = dr["OtherSY"].ToString(); returnValue.OtherSM = dr["OtherSM"].ToString(); returnValue.OtherEY = dr["OtherEY"].ToString(); returnValue.OtherEM = dr["OtherEM"].ToString(); returnValue.remark = dr["remark"].ToString(); returnValue.Teack = new List<TeackedData>(); } dr.Close(); sql = "SELECT StudentTracked.*,StaffDatabase.StaffName FROM StudentTracked INNER JOIN StaffDatabase ON StudentTracked.Personnel=StaffDatabase.StaffID "+ "WHERE StudentTracked.isDeleted=0 AND TrackedID=@TrackedID order by StudentTracked.TrackDate desc"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@TrackedID", SqlDbType.BigInt).Value = returnValue.ID; dr = cmd.ExecuteReader(); while (dr.Read()) { TeackedData addValue = new TeackedData(); addValue.tID = dr["ID"].ToString(); addValue.tDate = DateTime.Parse(dr["TrackDate"].ToString()).ToString("yyyy-MM-dd"); addValue.tStaffName = dr["StaffName"].ToString(); addValue.tStaff = dr["Personnel"].ToString(); addValue.tContent = dr["TrackItem"].ToString(); returnValue.Teack.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { returnValue.checkNo = "-1"; returnValue.errorMsg = e.Message.ToString(); } } return returnValue; }
public string[] setStudentTrackedDataBase(CreateStudentTracked StudentTracked) { CaseDataBase SDB = new CaseDataBase(); if (int.Parse(SDB._StaffhaveRoles[1]) == 1) { return SDB.setStudentTrackedDataBase(StudentTracked); } else { return new string[2] { _noRole, _errorMsg }; } }
public CreateStudentTracked getStudentTrackedDataBase(Int64 tID) { CaseDataBase SDB = new CaseDataBase(); CreateStudentTracked returnValue = new CreateStudentTracked(); if (int.Parse(SDB._StaffhaveRoles[3]) == 1) { returnValue=SDB.getStudentTrackedDataBase(tID); } else { returnValue.checkNo = _noRole; returnValue.errorMsg = _errorMsg; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); if (returnValue.sUnit != UserFile[2] && int.Parse(SDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { returnValue.checkNo = _getcheckNo; returnValue.errorMsg = _errorMsg; } return returnValue; }
public CreateStudentTracked getStudentDataBaseForTrack(string stuID) { CaseDataBase SDB = new CaseDataBase(); StudentResult stuData = this.getStudentDataBase(stuID); CreateStudentTracked returnValue = new CreateStudentTracked(); returnValue.studentID = stuData.StudentData.studentID; returnValue.studentName = stuData.StudentData.studentName; returnValue.studentSex = stuData.StudentData.studentSex; returnValue.studentbirthday = stuData.StudentData.studentbirthday; returnValue.email = stuData.StudentData.email; returnValue.address = stuData.StudentData.address; returnValue.addressCity = stuData.StudentData.addressCity; returnValue.addressZip = stuData.StudentData.addressZip; returnValue.manualCategory1 = stuData.StudentData.manualCategory1; returnValue.manualCategory2 = stuData.StudentData.manualCategory2; returnValue.manualCategory3 = stuData.StudentData.manualCategory3; returnValue.manualGrade1 = stuData.StudentData.manualGrade1; returnValue.manualGrade2 = stuData.StudentData.manualGrade2; returnValue.manualGrade3 = stuData.StudentData.manualGrade3; returnValue.assistmanageL = stuData.HearingData.assistmanageL; returnValue.assistmanageR = stuData.HearingData.assistmanageR; returnValue.brandL = stuData.HearingData.brandL; returnValue.brandR = stuData.HearingData.brandR; returnValue.Tel = stuData.StudentData.fPPhone2; returnValue.checkNo = stuData.checkNo; returnValue.errorMsg = stuData.errorMsg; return returnValue; }