public List<AchievementAssessmentLoad> ShowTeachServiceSupervisor(int id)
{
List<AchievementAssessmentLoad> returnValue = new List<AchievementAssessmentLoad>();
string strSql = "";
TeachServiceSupervisor sTemperatureData = new TeachServiceSupervisor();
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":
break;
case "ClassDate":
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 ";
sql += " from TeachServiceSupervisor 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 += " 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 = "studentName";
addValue.ThisValue = dr["studentName"].ToString();
returnValue.Add(addValue);
addValue.IDname = "teacherName";
addValue.ThisValue = dr["TeacherName"].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":
break;
case "ClassDate":
addValue.IDname = fldInfo.Name;
addValue.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).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 List<AchievementAssessmentLoad> ShowCaseIspRecord(int ID)
{
string strSql = "";
List<AchievementAssessmentLoad> returnvalue = new List<AchievementAssessmentLoad>();
AchievementAssessmentLoad temp = new AchievementAssessmentLoad();
CaseISPRecord sTemperatureData = new CaseISPRecord();
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
break;
case "ConventionDate":
case "PlanExecutionFrameDate":
case "PlanExecutionOverDate":
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
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
//string sql = "select b.studentname ,convert(varchar, isnull(convert(date, b.studentbirthday ,1),'1912'), 23) as studentbirthday , " + strSql + " from CaseStudy a left join studentDatabase b on a.studentid = b.id where a.ID=@ID and isnull(a.isDeleted,0) = 0 ";
string sql = "select b.StudentName , c.TeacherName, ParticipantTeacheName,ParticipantSocialWorkerName,ParticipantAudiologistName,ParticipantHeadName , ";
sql += strSql;
sql += " FROM CaseISPRecord a 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 staffid as did , StaffName as ParticipantTeacheName from staffDatabase) d on a.ParticipantTeache = d.did ";
sql += " left join (select staffid as eid , StaffName as ParticipantSocialWorkerName from staffDatabase) e on a.ParticipantSocialWorker = e.eid ";
sql += " left join (select staffid as fid , StaffName as ParticipantAudiologistName from staffDatabase) f on a.ParticipantAudiologist = f.fid ";
sql += " left join (select staffid as gid , StaffName as ParticipantHeadName from staffDatabase) g on a. ParticipantHead = g.gid ";
sql += " WHERE isnull(a.isDeleted,0) = 0 and a.ID = @ID ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
temp.IDname = "studentName";
temp.ThisValue = dr["studentName"].ToString();
returnvalue.Add(temp);
temp.IDname = "ParticipantTeacheName";
temp.ThisValue = dr["ParticipantTeacheName"].ToString();
returnvalue.Add(temp);
temp.IDname = "teacherName";
temp.ThisValue = dr["TeacherName"].ToString();
returnvalue.Add(temp);
temp.IDname = "ParticipantSocialWorkerName";
temp.ThisValue = dr["ParticipantSocialWorkerName"].ToString();
returnvalue.Add(temp);
temp.IDname = "ParticipantAudiologistName";
temp.ThisValue = dr["ParticipantAudiologistName"].ToString();
returnvalue.Add(temp);
temp.IDname = "ParticipantHeadName";
temp.ThisValue = dr["ParticipantHeadName"].ToString();
returnvalue.Add(temp);
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
break;
case "ConventionDate":
case "PlanExecutionFrameDate":
case "PlanExecutionOverDate":
temp.IDname = fldInfo.Name;
temp.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1);
returnvalue.Add(temp);
break;
default:
string namee = fldInfo.Name;
string namee2 = dr[fldInfo.Name].ToString();
temp.IDname = fldInfo.Name;
temp.ThisValue = dr[fldInfo.Name].ToString();
returnvalue.Add(temp);
break;
}
}
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
string error = e.Message;
}
}
return returnvalue;
}
public List<AchievementAssessmentLoad> ShowCaseStudy(int ID)
{
string strSql = "";
List<AchievementAssessmentLoad> returnvalue = new List<AchievementAssessmentLoad>();
AchievementAssessmentLoad temp = new AchievementAssessmentLoad();
CaseStudy sTemperatureData = new CaseStudy();
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
case "WriteNameName":
case "WriteName1Name":
case "RecordedByName":
break;
case "WriteDate":
case "RecordedDateTime":
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
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "select b.studentname ,convert(varchar, isnull(convert(date, b.studentbirthday ,1),'1912'), 23) as studentbirthday , c.WriteNameName , d.WriteName1Name,e.RecordedByName , " + strSql + " from CaseStudy a ";
sql += " left join ( select staffid as cid , StaffName as WriteNameName from staffDatabase ) c on a.WriteName = c.cid ";
sql += " left join ( select staffid as did , StaffName as WriteName1Name from staffDatabase ) d on a.WriteName1 = d.did ";
sql += " left join ( select staffid as eid , StaffName as RecordedByName from staffDatabase ) e on a.RecordedBy = e.eid ";
sql += " left join studentDatabase b on a.studentid = b.id where a.ID=@ID and isnull(a.isDeleted,0) = 0 ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
temp.IDname = "studentName";
temp.ThisValue = dr["studentName"].ToString();
returnvalue.Add(temp);
temp.IDname = "WriteNameName";
temp.ThisValue = dr["WriteNameName"].ToString();
returnvalue.Add(temp);
temp.IDname = "WriteName1Name";
temp.ThisValue = dr["WriteName1Name"].ToString();
returnvalue.Add(temp);
temp.IDname = "RecordedByName";
temp.ThisValue = dr["RecordedByName"].ToString();
returnvalue.Add(temp);
temp.IDname = "studentbirthday";
temp.ThisValue = dr["studentbirthday"].ToString();
returnvalue.Add(temp);
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
case "WriteNameName":
case "WriteName1Name":
case "RecordedByName":
break;
case "WriteDate":
case "RecordedDateTime":
temp.IDname = fldInfo.Name;
temp.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1);
returnvalue.Add(temp);
break;
default:
string namee = fldInfo.Name;
string namee2 = dr[fldInfo.Name].ToString();
temp.IDname = fldInfo.Name;
temp.ThisValue = dr[fldInfo.Name].ToString();
returnvalue.Add(temp);
break;
}
}
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
string error = e.Message;
}
}
return returnvalue;
}
public List<AchievementAssessmentLoad> ShowAchievementAssessment(int ID)
{
string strSql = "";
List<AchievementAssessmentLoad> returnvalue = new List<AchievementAssessmentLoad>();
AchievementAssessmentLoad temp = new AchievementAssessmentLoad();
AchievementAssessment sTemperatureData = new AchievementAssessment();
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
break;
case "Intelligence_Date":
case "AuditorySkills_Date":
case "Vocabulary_Date":
case "Vocabulary1_Date":
case "Language1_Date":
case "Language2_Date":
case "Language3_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
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "select b.studentname , " + strSql + " from AchievementAssessment a left join studentDatabase b on a.studentid = b.id where a.ID=@ID and isnull(a.isDeleted,0) = 0 ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
temp.IDname = "studentName";
temp.ThisValue = dr["studentName"].ToString();
returnvalue.Add(temp);
foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields())
{
switch (fldInfo.Name)
{
case "ID":
case "Unit":
break;
case "Intelligence_Date":
case "AuditorySkills_Date":
case "Vocabulary_Date":
case "Vocabulary1_Date":
case "Language1_Date":
case "Language2_Date":
case "Language3_Date":
temp.IDname = fldInfo.Name;
temp.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1);
returnvalue.Add(temp);
break;
default:
string namee = fldInfo.Name;
string namee2 = dr[fldInfo.Name].ToString();
temp.IDname = fldInfo.Name;
temp.ThisValue = dr[fldInfo.Name].ToString();
returnvalue.Add(temp);
break;
}
}
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
string error = e.Message;
}
}
return returnvalue;
}
