public string[] CreateTeachServiceInspect(TeachServiceInspect sTemperatureData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
string strSql = "";
string strSqlPara = "";
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
case "RowNum":
case "TeacherName":
case "StudentName":
case "txtstudentbirthday":
case "checkNo":
case "errorMsg":
case "ClassIDName":
case "DirectorName":
break;
case "isDeleted":
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name;
strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "0 ";
break;
case "CreateFileDate":
case "UpFileDate":
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name;
strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() ";
break;
default:
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name;
strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name;
break;
}
}
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
string sql = " insert into TeachServiceInspect ( " + strSql + " )values( " + strSqlPara + ") select @@identity as ID ";
Sqlconn.Open();
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
//cmd.Parameters.Add("@AcademicYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData.AcademicYear.ToString());
//cmd.Parameters.Add("@VDID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sTemperatureData.ID.ToString());
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
case "RowNum":
case "TeacherName":
case "StudentName":
case "txtstudentbirthday":
case "checkNo":
case "errorMsg":
case "ClassIDName":
case "DirectorName":
break;
case "CreateFileBy":
case "UpFileBy":
//cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData));
cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
break;
case "ClassDate":
if (fldInfo.GetValue(sTemperatureData) != null)
{
cmd.Parameters.Add("@" + fldInfo.Name, Convert.ToDateTime(fldInfo.GetValue(sTemperatureData).ToString()).AddYears(1911).ToShortDateString());
}
else
{
cmd.Parameters.Add("@" + fldInfo.Name, "");
}
break;
default:
if (fldInfo.GetValue(sTemperatureData) != null)
{
cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString());
}
else
{
cmd.Parameters.Add("@" + fldInfo.Name, "");
}
break;
}
}
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
returnValue[0] = dr["ID"].ToString();
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<TeachServiceInspect> SearchTeachServiceInspect(int indexpage, SearchStudent SearchStructure, int type)
{
List<TeachServiceInspect> returnValue = new List<TeachServiceInspect>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchTeachServiceInspectCondition(SearchStructure, type);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.AcademicYear,'') DESC) as RowNum ";
sql += " ,a.id, a.AcademicYear, convert(varchar, isnull(convert(date, a.InspectDate,1),'1912'), 111) as InspectDate,a.ClassType , b.StudentName , b.StudentBirthday , c.TeacherName ,d.ClassIDName ";
sql += " FROM TeachServiceInspect a left join studentDatabase b on a.studentid = b.id ";
sql += " left join (select staffid as cid , staffname as TeacherName from staffdatabase ) c on a.teacherid = c.cid ";
sql += " left join (select ClassIDName , ClassID as did from ClassName ) d on a.ClassNameID = d.did ";
sql += " WHERE 1=1 " + ConditionReturn + ") AS NewTable ";
sql += " where RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID);
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%";
cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex);
cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart);
cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend);
cmd.Parameters.Add("@InspectDatestart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDatestart);
cmd.Parameters.Add("@InspectDateend", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDateend);
cmd.Parameters.Add("@AcademicYearstart", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtAcademicYearstart);
cmd.Parameters.Add("@AcademicYearend", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtAcademicYearend);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TeachServiceInspect addValue = new TeachServiceInspect();
addValue.RowNum = dr["rownum"].ToString();
addValue.ID = dr["ID"].ToString();
addValue.ClassType = dr["ClassType"].ToString();
addValue.InspectDate = dr["InspectDate"].ToString();
addValue.StudentName = dr["StudentName"].ToString();
addValue.TeacherName = dr["TeacherName"].ToString();
addValue.AcademicYear = dr["AcademicYear"].ToString();
addValue.ClassIDName = dr["ClassIDName"].ToString();
//if (dr["ClassType"].ToString() == 1)
//{
// addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());
//}
returnValue.Add(addValue);
}
Sqlconn.Close();
}
catch (Exception e)
{
TeachServiceInspect addValue = new TeachServiceInspect();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<AchievementAssessmentLoad> ShowTeachServiceInspect(int id)
{
List<AchievementAssessmentLoad> returnValue = new List<AchievementAssessmentLoad>();
string strSql = "";
TeachServiceInspect sTemperatureData = new TeachServiceInspect();
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
case "RowNum":
case "TeacherName":
case "StudentName":
case "txtstudentbirthday":
case "checkNo":
case "errorMsg":
case "isDeleted":
case "CreateFileDate":
case "CreateFileBy":
case "UpFileBy":
case "UpFileDate":
case "ClassIDName":
case "DirectorName":
break;
case "ClassDate":
case "InspectDate":
case "Date":
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "convert(varchar, isnull(convert(date, a." + fldInfo.Name + ",1),'1912'), 111) as " + fldInfo.Name;
break;
default:
strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "a." + fldInfo.Name;
break;
}
}
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = " SELECT " + strSql + " , b.StudentName ,c.TeacherName ,d.ClassIDName ,e.DirectorName ";
sql += " from TeachServiceInspect a ";
sql += " left join (select id, studentName from studentDatabase) b on a.studentid = b.id ";
sql += " left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid ";
sql += " left join (select ClassIDName , ClassID as did from ClassName ) d on a.ClassNameID = d.did ";
sql += " left join ( select staffid as eid , StaffName as DirectorName from staffDatabase ) e on a.Director = e.eid "; //DirectorName
sql += " where 1=1 and a.ID = @ID ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", id);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
AchievementAssessmentLoad addValue = new AchievementAssessmentLoad();
addValue.IDname = "ClassType";
addValue.ThisValue = dr["ClassType"].ToString();
returnValue.Add(addValue);
addValue.IDname = "studentName";
addValue.ThisValue = dr["studentName"].ToString();
returnValue.Add(addValue);
addValue.IDname = "teacherName";
addValue.ThisValue = dr["TeacherName"].ToString();
returnValue.Add(addValue);
addValue.IDname = "DirectorName";
addValue.ThisValue = dr["DirectorName"].ToString();
returnValue.Add(addValue);
addValue.IDname = "ClassName";
addValue.ThisValue = dr["ClassIDName"].ToString();
returnValue.Add(addValue);
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
case "RowNum":
case "TeacherName":
case "StudentName":
case "txtstudentbirthday":
case "checkNo":
case "errorMsg":
case "isDeleted":
case "CreateFileDate":
case "CreateFileBy":
case "UpFileBy":
case "UpFileDate":
case "ClassType":
case "ClassIDName":
case "DirectorName":
break;
case "InspectDate":
case "Date":
case "ClassDate":
addValue.IDname = fldInfo.Name;
addValue.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).AddDays(-1).ToShortDateString().Remove(0, 1);
returnValue.Add(addValue);
break;
default:
addValue.IDname = fldInfo.Name;
addValue.ThisValue = dr[fldInfo.Name].ToString();
returnValue.Add(addValue);
break;
}
}
}
Sqlconn.Close();
}
catch (Exception e)
{
//AchievementAssessmentLoad addValue = new AchievementAssessmentLoad();
////addValue.checkNo = "-1";
////addValue.errorMsg = e.Message.ToString();
string ee = e.Message.ToString();
//returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] UpdateTeachServiceInspect(TeachServiceInspect sTemperatureData)
{
AdministrationDataBase aDB = new AdministrationDataBase();
//aDB.caseBTFunction();
//if (int.Parse(aDB._StaffhaveRoles[2]) == 1)
//{
return aDB.UpdateTeachServiceInspect(sTemperatureData);
//}
//else
//{
// return null;
//}
}
