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