public string[] setNewTeacherDataBase(CreateNewTeacher NewTeacherData)
{
StaffDataBase sDB = new StaffDataBase();
sDB.personnelFunction();
if (int.Parse(sDB._StaffhaveRoles[1]) == 1)
{
return sDB.setNewTeacherDataBase(NewTeacherData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public CreateNewTeacher getNewTeacherDataBase(string tID)
{
StaffDataBase sDB = new StaffDataBase();
CreateNewTeacher returnValue = new CreateNewTeacher();
sDB.personnelFunction();
if (int.Parse(sDB._StaffhaveRoles[3]) == 1)
{
returnValue=sDB.getNewTeacherDataBase(tID);
}
else
{
returnValue.checkNo = _noRole;
returnValue.errorMsg = _errorMsg;
}
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (returnValue.Unit != UserFile[2] && int.Parse(sDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
returnValue.checkNo = _getcheckNo;
returnValue.errorMsg = _errorMsg;
}
return returnValue;
}
public string[] setNewTeacherDataBase(CreateNewTeacher NewTeacherData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
List<string> CreateFileName = this.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "UPDATE NewTeacherPerformanceRating SET RatingDate = @RatingDate, CaseReport = @CaseReport, BasicTheory = @BasicTheory, "+
"TeachingPractice = @TeachingPractice, Result = @Result, TeachingContentAdvantage = @TeachingContentAdvantage, " +
"TeachingContentShortcoming = @TeachingContentShortcoming, TeachingAidAdvantage = @TeachingAidAdvantage, " +
"TeachingAidShortcoming = @TeachingAidShortcoming, TeachingLangAdvantage = @TeachingLangAdvantage, " +
"TeachingLangShortcoming = @TeachingLangShortcoming, TeachingSkillAdvantage = @TeachingSkillAdvantage, " +
"TeachingSkillShortcoming = @TeachingSkillShortcoming, InteractionAdvantage = @InteractionAdvantage, " +
"InteractionShortcoming = @InteractionShortcoming, ParentAdvisoryAdvantage = @ParentAdvisoryAdvantage, " +
"ParentAdvisoryShortcoming = @ParentAdvisoryShortcoming, OverallPerformanceAdvantage = @OverallPerformanceAdvantage, " +
"OverallPerformanceShortcoming = @OverallPerformanceShortcoming, Director = @Director, Supervisor = @Supervisor, " +
"Trainer = @Trainer, Collation = @Collation, UpFileBy=@UpFileBy, UpFileDate=(getDate()) "+
"WHERE ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(NewTeacherData.ID);
cmd.Parameters.Add("@RatingDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(NewTeacherData.evaluationDate);
cmd.Parameters.Add("@CaseReport", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(NewTeacherData.reportPer);
cmd.Parameters.Add("@BasicTheory", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(NewTeacherData.basicPer);
cmd.Parameters.Add("@TeachingPractice", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(NewTeacherData.teachPer);
cmd.Parameters.Add("@Result", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.rated);
cmd.Parameters.Add("@TeachingContentAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.contentMerit);
cmd.Parameters.Add("@TeachingContentShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.contentDefect);
cmd.Parameters.Add("@TeachingAidAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.useMerit);
cmd.Parameters.Add("@TeachingAidShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.useDefect);
cmd.Parameters.Add("@TeachingLangAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.langMerit);
cmd.Parameters.Add("@TeachingLangShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.langDefect);
cmd.Parameters.Add("@TeachingSkillAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.skillMerit);
cmd.Parameters.Add("@TeachingSkillShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.skillDefect);
cmd.Parameters.Add("@InteractionAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.ExchangeMerit);
cmd.Parameters.Add("@InteractionShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.ExchangeDefect);
cmd.Parameters.Add("@ParentAdvisoryAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.AdvisoryMerit);
cmd.Parameters.Add("@ParentAdvisoryShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.AdvisoryDefect);
cmd.Parameters.Add("@OverallPerformanceAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.OverallMerit);
cmd.Parameters.Add("@OverallPerformanceShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.OverallDefect);
cmd.Parameters.Add("@Director", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(NewTeacherData.director);
cmd.Parameters.Add("@Supervisor", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.prison);
cmd.Parameters.Add("@Trainer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.trainPerson);
cmd.Parameters.Add("@Collation", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.file);
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.ToString();
}
}
return returnValue;
}
public CreateNewTeacher getNewTeacherDataBase(string tID)
{
CreateNewTeacher returnValue = new CreateNewTeacher();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "SELECT nT.*,a.StaffName AS teacherName, a.AppointmentDate, b.StaffName AS trainPersonName,c.StaffName AS fileName1 FROM NewTeacherPerformanceRating AS nT " +
"INNER JOIN StaffDatabase AS a ON nT.TeacherID=a.StaffID " +
"LEFT JOIN StaffDatabase AS b ON nT.Trainer=b.StaffID " +
"LEFT JOIN StaffDatabase AS c ON nT.Collation=c.StaffID " +
"WHERE nT.ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(tID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.ID = dr["ID"].ToString();
returnValue.Unit = dr["Unit"].ToString();
returnValue.officeDate = DateTime.Parse(dr["AppointmentDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.teacher = dr["TeacherID"].ToString();
returnValue.teacherName = dr["teacherName"].ToString();
returnValue.evaluationDate = DateTime.Parse(dr["RatingDate"].ToString()).ToString("yyyy-MM-dd");;
returnValue.reportPer = dr["CaseReport"].ToString();
returnValue.basicPer = dr["BasicTheory"].ToString();
returnValue.teachPer = dr["TeachingPractice"].ToString();
returnValue.rated = dr["Result"].ToString();
returnValue.contentMerit = dr["TeachingContentAdvantage"].ToString();
returnValue.contentDefect = dr["TeachingContentShortcoming"].ToString();
returnValue.useMerit = dr["TeachingAidAdvantage"].ToString();
returnValue.useDefect = dr["TeachingAidShortcoming"].ToString();
returnValue.langMerit = dr["TeachingLangAdvantage"].ToString();
returnValue.langDefect = dr["TeachingLangShortcoming"].ToString();
returnValue.skillMerit = dr["TeachingSkillAdvantage"].ToString();
returnValue.skillDefect = dr["TeachingSkillShortcoming"].ToString();
returnValue.ExchangeMerit = dr["InteractionAdvantage"].ToString();
returnValue.ExchangeDefect = dr["InteractionShortcoming"].ToString();
returnValue.AdvisoryMerit = dr["ParentAdvisoryAdvantage"].ToString();
returnValue.AdvisoryDefect = dr["ParentAdvisoryShortcoming"].ToString();
returnValue.OverallMerit = dr["OverallPerformanceAdvantage"].ToString();
returnValue.OverallDefect = dr["OverallPerformanceShortcoming"].ToString();
returnValue.director = dr["Director"].ToString();
returnValue.prison = dr["Supervisor"].ToString();
returnValue.trainPerson = dr["Trainer"].ToString();
returnValue.trainPersonName = dr["trainPersonName"].ToString();
returnValue.file = dr["Collation"].ToString();
returnValue.fileName = dr["fileName1"].ToString();
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message.ToString();
}
}
return returnValue;
}
public string[] createNewTeacherDataBase(CreateNewTeacher NewTeacherData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
List<string> CreateFileName = this.getStaffDataName(HttpContext.Current.User.Identity.Name);
List<string> StaffFileName = this.getStaffDataName(NewTeacherData.teacher);
Sqlconn.Open();
string sql = "INSERT INTO NewTeacherPerformanceRating (Unit,TeacherID, RatingDate, CaseReport, BasicTheory, TeachingPractice, Result, TeachingContentAdvantage, " +
"TeachingContentShortcoming, TeachingAidAdvantage, TeachingAidShortcoming, TeachingLangAdvantage, TeachingLangShortcoming, TeachingSkillAdvantage, " +
"TeachingSkillShortcoming, InteractionAdvantage, InteractionShortcoming, ParentAdvisoryAdvantage, ParentAdvisoryShortcoming, OverallPerformanceAdvantage, " +
"OverallPerformanceShortcoming, Director, Supervisor, Trainer, Collation, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ) " +
"VALUES (@Unit, @TeacherID, @RatingDate, @CaseReport, @BasicTheory, @TeachingPractice, @Result, @TeachingContentAdvantage, @TeachingContentShortcoming, " +
"@TeachingAidAdvantage, @TeachingAidShortcoming, @TeachingLangAdvantage, @TeachingLangShortcoming, @TeachingSkillAdvantage, @TeachingSkillShortcoming, " +
"@InteractionAdvantage, @InteractionShortcoming, @ParentAdvisoryAdvantage, @ParentAdvisoryShortcoming, @OverallPerformanceAdvantage, " +
"@OverallPerformanceShortcoming, @Director, @Supervisor, @Trainer, @Collation, @CreateFileBy, getDate(), @UpFileBy, getDate())";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffFileName[2]);
cmd.Parameters.Add("@TeacherID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(NewTeacherData.teacher);
cmd.Parameters.Add("@RatingDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(NewTeacherData.evaluationDate);
cmd.Parameters.Add("@CaseReport", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(NewTeacherData.reportPer);
cmd.Parameters.Add("@BasicTheory", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(NewTeacherData.basicPer);
cmd.Parameters.Add("@TeachingPractice", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(NewTeacherData.teachPer);
cmd.Parameters.Add("@Result", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.rated);
cmd.Parameters.Add("@TeachingContentAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.contentMerit);
cmd.Parameters.Add("@TeachingContentShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.contentDefect);
cmd.Parameters.Add("@TeachingAidAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.useMerit);
cmd.Parameters.Add("@TeachingAidShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.useDefect);
cmd.Parameters.Add("@TeachingLangAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.langMerit);
cmd.Parameters.Add("@TeachingLangShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.langDefect);
cmd.Parameters.Add("@TeachingSkillAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.skillMerit);
cmd.Parameters.Add("@TeachingSkillShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.skillDefect);
cmd.Parameters.Add("@InteractionAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.ExchangeMerit);
cmd.Parameters.Add("@InteractionShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.ExchangeDefect);
cmd.Parameters.Add("@ParentAdvisoryAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.AdvisoryMerit);
cmd.Parameters.Add("@ParentAdvisoryShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.AdvisoryDefect);
cmd.Parameters.Add("@OverallPerformanceAdvantage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.OverallMerit);
cmd.Parameters.Add("@OverallPerformanceShortcoming", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.OverallDefect);
cmd.Parameters.Add("@Director", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(NewTeacherData.director);
cmd.Parameters.Add("@Supervisor", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.prison);
cmd.Parameters.Add("@Trainer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.trainPerson);
cmd.Parameters.Add("@Collation", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(NewTeacherData.file);
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();
if (returnValue[0] != "0")
{
sql = "select IDENT_CURRENT('NewTeacherPerformanceRating') 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.ToString();
}
}
return returnValue;
}
