/// <summary>
/// 取得所有輔導相關老師
/// </summary>
/// <returns></returns>
public static Dictionary<int, DAO.CounselTeacherRecord> GetAllCounselTeacherDict()
{
Dictionary<int, DAO.CounselTeacherRecord> CounselTeacherRecordDict = new Dictionary<int, DAO.CounselTeacherRecord>();
QueryHelper hepler = new QueryHelper ();
string strSQL="select distinct teacher.id as id1,tag_teacher.id as id2,teacher.teacher_name,teacher.nickname,tag.name,tag.prefix from teacher inner join tag_teacher on teacher.id=tag_teacher.ref_teacher_id inner join tag on tag_teacher.ref_tag_id=tag.id where tag.prefix='輔導'";
DataTable dt =hepler.Select (strSQL);
foreach(DataRow dr in dt.Rows)
{
// TeachertagID
int eid = int.Parse(dr[1].ToString ());
if (!CounselTeacherRecordDict.ContainsKey(eid))
{
DAO.CounselTeacherRecord cRec = new DAO.CounselTeacherRecord();
if (dr[4].ToString () == "輔導老師")
cRec.counselTeacherType = DAO.CounselTeacherRecord.CounselTeacherType.輔導老師;
if (dr[4].ToString() == "認輔老師")
cRec.counselTeacherType = DAO.CounselTeacherRecord.CounselTeacherType.認輔老師;
if (dr[4].ToString() == "輔導主任")
cRec.counselTeacherType = DAO.CounselTeacherRecord.CounselTeacherType.輔導主任;
cRec.TeacherTag_ID = eid;
cRec.TeacherID = dr[0].ToString();
if(string.IsNullOrEmpty(dr[3].ToString ()))
cRec.TeacherFullName = dr[2].ToString ();
else
cRec.TeacherFullName=dr[2].ToString ()+"("+dr[3].ToString ()+")";
CounselTeacherRecordDict.Add(eid, cRec);
}
}
return CounselTeacherRecordDict;
}
/// <summary>
/// 透過輔導系統老師類別取得老師
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static List<DAO.CounselTeacherRecord> GetCounselTeacherByType(DAO.CounselTeacherRecord.CounselTeacherType type)
{
List<DAO.CounselTeacherRecord> CounselTeacherList = new List<DAO.CounselTeacherRecord>();
QueryHelper hepler = new QueryHelper();
string strSQL = "select distinct teacher.id as id1,tag_teacher.id as id2,teacher.teacher_name,teacher.nickname,tag.name,tag.prefix from teacher inner join tag_teacher on teacher.id=tag_teacher.ref_teacher_id inner join tag on tag_teacher.ref_tag_id=tag.id where tag.prefix='輔導' and tag.name='"+type.ToString ()+"';";
DataTable dt = hepler.Select(strSQL);
foreach (DataRow dr in dt.Rows)
{
DAO.CounselTeacherRecord counselTeacherRec = new DAO.CounselTeacherRecord ();
// 教師編號
counselTeacherRec.TeacherID = dr[0].ToString();
// TeacherTagID
counselTeacherRec.TeacherTag_ID=int.Parse(dr[1].ToString ());
// 教師名稱
if (string.IsNullOrEmpty(dr[3].ToString()))
counselTeacherRec.TeacherFullName = dr[2].ToString();
else
counselTeacherRec.TeacherFullName = dr[2].ToString() + "(" + dr[3].ToString() + ")";
// 類別
counselTeacherRec.counselTeacherType = type;
CounselTeacherList.Add(counselTeacherRec);
}
return CounselTeacherList.OrderBy(x=>x.TeacherFullName).ToList ();
}