public string[] createStudentTrackedRecord(TeackedData 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 StudentTracked(TrackedID, TrackDate, Personnel, TrackItem, CreateFileBy, UpFileBy, UpFileDate ) " +
"VALUES (@TrackedID, @TrackDate, @Personnel, @TrackItem, @CreateFileBy, @UpFileBy, (getDate()) )";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@TrackedID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentTracked.offID);
cmd.Parameters.Add("@TrackDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.tDate);
cmd.Parameters.Add("@TrackItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.tContent);
cmd.Parameters.Add("@Personnel", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
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[] setStudentTrackedRecord(TeackedData 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 StudentTracked SET TrackItem=@TrackItem, UpFileBy=@UpFileBy, UpFileDate=(getDate()) " +
"WHERE ID=@ID AND isDeleted=0";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentTracked.tID);
cmd.Parameters.Add("@TrackItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.tContent);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] setStudentTrackedRecord(TeackedData StudentTracked)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[1]) == 1)
{
return SDB.setStudentTrackedRecord(StudentTracked);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}