public int GetCurrentStudentNum(string tid, string sid) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["tid"] = tid }; String queryString = "SELECT p.p_s_Id FROM ntust.pair as p, " + "ntust.teacher_group as tg WHERE p.p_end_date IS NULL " + "AND tg.tg_id=p.p_tg_id AND tg.t_id=@tid"; JObject student = sqlHelper.query(queryString, dataArray); int count = 0; foreach (JObject i in student["data"]) { //System.Diagnostics.Debug.Print(); String p_sid = (String)i["p_s_Id"]; //略過外籍生與在職生 if (p_sid[6] == '8' || p_sid[6] == '9') { continue; } if (p_sid.Substring(0, 3).Equals(sid.Substring(0, 3))) { count++; } //System.Diagnostics.Debug.Print((String)p_sid.Substring(1, 4)); } return(count); }
public JObject GetChangeHistory() { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["t_id"] = t_id }; String queryString = "SELECT DISTINCT " + "hsc.hsc_s_id, s.s_name, hsc.hsc_create_datetime, hsc.hsc_end_datetime, hsc.hsc_state, " + "STUFF((SELECT ', ' + new_t.t_name FROM ntust.teacher_group as new_tg join ntust.teacher as new_t on new_t.t_id = new_tg.t_id " + "WHERE hsc.hsc_tg_id = new_tg.tg_id FOR XML PATH('')),1,1,'') AS new_teacher, " + "STUFF((SELECT ', ' + org_t.t_name FROM ntust.teacher_group as org_tg join ntust.teacher as org_t on org_t.t_id = org_tg.t_id " + "WHERE hsc.hsc_origin_tg_id = org_tg.tg_id FOR XML PATH('')),1,1,'') AS org_teacher " + "FROM ntust.history_student_change as hsc " + "JOIN ntust.teacher_group as tg on(tg.tg_id = hsc.hsc_tg_id) " + "JOIN ntust.student as s on hsc.hsc_s_id = s.s_id " + "join ntust.teacher_group as org_tg on(org_tg.tg_id = hsc.hsc_origin_tg_id AND hsc.hsc_end_datetime IS NOT NULL) " + "WHERE(tg.t_id = @t_id) OR(org_tg.t_id = @t_id)"; //String queryString = @"SELECT DISTINCT p.p_s_id, s.s_name, hsc.hsc_create_datetime, hsc.hsc_end_datetime, hsc.hsc_state, t.t_name AS new_teacher," + // " STUFF((SELECT ', ' + org_t.t_name FROM ntust.teacher_group as org_tg"+ // " join ntust.teacher as org_t on org_t.t_id = org_tg.t_id"+ // " WHERE hsc.hsc_origin_tg_id = org_tg.tg_id"+ // " FOR XML PATH('')),1,1,'') AS org_teacher"+ // " FROM ntust.history_student_change as hsc"+ // " JOIN ntust.teacher_group as tg on(tg.tg_id = hsc.hsc_tg_id)"+ // " JOIN ntust.pair as p on p.p_tg_id = tg.tg_id"+ // " JOIN ntust.student as s on p.p_s_id = s.s_id"+ // " JOIN ntust.teacher as t on t.t_id = tg.t_id"+ // " join ntust.teacher_group as org_tg on(org_tg.tg_id = hsc.hsc_origin_tg_id AND hsc.hsc_end_datetime IS NOT NULL)"+ // "WHERE(tg.t_id = @t_id) OR(org_tg.t_id = @t_id)"; JObject changeHistory = sqlHelper.query(queryString, dataArray); return(changeHistory); }
public void UpdateOrgPair(string org_tg_id, String time) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["p_tg_id"] = org_tg_id }; String queryStr = @"UPDATE p set p_end_date='" + time + "' FROM ntust.pair p WHERE p.p_tg_id = @p_tg_id"; sqlHelper.query(queryStr, dataArray); }
public void UpdateStudentChangeHistory(String tg_id, int state, String time) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["hsc_origin_tg_id"] = tg_id, ["hsc_state"] = state }; String queryString = @"UPDATE hsc set hsc_end_datetime='" + time + "' ,hsc_state=@hsc_state FROM ntust.history_student_change hsc WHERE hsc_origin_tg_id = @hsc_origin_tg_id"; sqlHelper.query(queryString, dataArray); }
public JObject AddChangePair(String new_tg_id, String s_id, String time) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["new_tg_id"] = new_tg_id, ["s_id"] = s_id }; String queryString = @"INSERT into ntust.pair (p_tg_id, p_s_id ,p_pair_date) VALUES(@new_tg_id, @s_id,'" + time + "')"; return(sqlHelper.query(queryString, dataArray)); }
public void UpdateStudentApplyHistory(String tg_id, int state) { sqlHelper = new SQLHelper(); String time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); JObject dataArray = new JObject { ["hsa_state"] = state, ["hsa_tg_id"] = tg_id }; String query = @"UPDATE hsa set hsa_end_datetime='" + time + "' ,hsa_state=@hsa_state FROM ntust.history_student_apply hsa WHERE hsa_tg_id =@hsa_tg_id "; sqlHelper.query(query, dataArray); }
public JObject UpdateStudentChangeApproval(String tg_id) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["sc_tg_id"] = tg_id }; String queryString = @"UPDATE sc set sc.sc_all_approval = 1 " + "FROM ntust.student_change sc " + "WHERE sc.sc_tg_id = @sc_tg_id"; return(sqlHelper.query(queryString, dataArray)); }
public JObject UpdateApply(String tg_id, String t_id, String adminId, int accept) { sqlHelper = new SQLHelper(); String queryString = @"UPDATE sa set sa_state=@state, sa_check_by_type=1, sa_check_by_st_id=@sa_check_by_st_id FROM ntust.student_apply sa WHERE sa_tg_id = @sa_tg_id AND sa_t_id = @sa_t_id"; JObject dataArray = new JObject(); dataArray["state"] = accept; dataArray["sa_check_by_st_id"] = adminId; dataArray["sa_tg_id"] = tg_id; dataArray["sa_t_id"] = t_id; JObject updateStatus = sqlHelper.query(queryString, dataArray); return(updateStatus); }
public int GetMaxStudentNum(string tid, string sid) { sqlHelper = new SQLHelper(); string semester = sid.Substring(1, 3); JObject dataArray = new JObject { ["t_id"] = tid, ["semester"] = semester }; String queryString = @"SELECT ms.ms_max_student_num FROM ntust.max_student as ms WHERE ms.ms_t_id=@t_id AND ms.ms_semester =@semester"; JObject res = sqlHelper.query(queryString, dataArray); return(Convert.ToInt32(res["data"][0]["ms_max_student_num"])); }
public void UpdateStudentApplyHistory(String tg_id, int state, String time) { sqlHelper = new SQLHelper(); //String query = "UPDATE hsa set hsa_end_datetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' ,hsa_state=" + state + " FROM ntust.history_student_apply hsa WHERE hsa_tg_id = " + tg_id; //sqlHelper.update(query); String queryString = "UPDATE hsa set hsa_end_datetime='" + time + "' ,hsa_state=" + state + " FROM ntust.history_student_apply hsa WHERE hsa_tg_id = " + tg_id; JObject dataArray = new JObject { ["hsa_state"] = state, ["hsa_tg_id"] = tg_id }; sqlHelper.query(queryString, dataArray); }
public JObject UpdateChange(String sc_id, String org_tg_id, String s_id, String t_id, String adminId, String thesis_state, String allapprove, int accept) { sqlHelper = new SQLHelper(); JObject dataArray; String queryString; if (sc_id != "null") //new teacher { dataArray = new JObject { ["sc_state"] = accept, ["scota_check_by_st_id"] = adminId, ["sc_id"] = sc_id, ["sc_t_id"] = t_id }; queryString = " UPDATE sc set sc.sc_state = @sc_state , sc.sc_check_by_type = 2, sc_check_by_st_id = @scota_check_by_st_id" + " FROM ntust.student_change sc" + " WHERE sc.sc_id =@sc_id AND sc.sc_t_id = @sc_t_id"; return(sqlHelper.query(queryString, dataArray)); } else //original teacher { dataArray = new JObject { ["scota_state"] = accept, ["scota_thesis_state"] = thesis_state, ["scota_check_by_st_id"] = adminId, ["scota_tg_id"] = org_tg_id, ["scota_t_id"] = t_id }; queryString = " UPDATE scota set scota_state = @scota_state,scota_thesis_state=@scota_thesis_state" + ", scota_check_by_type=2, scota_check_by_st_id=@scota_check_by_st_id" + " FROM ntust.student_change_origin_teacher_approval scota" + " WHERE scota.scota_tg_id = @scota_tg_id AND scota.scota_t_id = @scota_t_id"; return(sqlHelper.query(queryString, dataArray)); } }
public JObject UpdateStudentApplyStatus(String s_id, int state) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["sas_type"] = state, ["sas_s_id"] = s_id }; String queryString = "UPDATE sas set sas_type=@sas_type FROM ntust.student_apply_status AS sas WHERE sas_s_id=@sas_s_id"; //String query = "UPDATE sas set sas_type=" + state + " FROM ntust.student_apply_status AS sas WHERE sas_s_id='" + s_id + "'"; //JObject applyStatus = sqlHelper.select(query); JObject applyStatus = sqlHelper.query(queryString, dataArray); return(applyStatus); }
public int CheckNewChange(String tg_id) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["sc_tg_id"] = tg_id }; //query for apply which not agree String queryString = @"SELECT COUNT(*) AS count " + "FROM ntust.student_change sc " + "WHERE sc.sc_tg_id = " + tg_id + " AND sc.sc_state!=1"; JObject updateStatus = sqlHelper.query(queryString, dataArray); return(Convert.ToInt32(updateStatus["data"][0]["count"])); }
public int CheckOrgChange(String org_tg_id) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["scota_tg_id"] = org_tg_id }; //query for apply which not agree String queryString = @"SELECT COUNT(*) AS count " + "FROM ntust.student_change_origin_teacher_approval scota " + "WHERE scota.scota_tg_id = @scota_tg_id AND scota.scota_state!=1"; JObject updateStatus = sqlHelper.query(queryString, dataArray); return(Convert.ToInt32(updateStatus["data"][0]["count"])); }
public void UpdateOrgPair(string org_tg_id, String time) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["org_tg_id"] = org_tg_id }; String queryString = @"UPDATE p set p_end_date='" + time + "' FROM ntust.pair p WHERE p.p_tg_id = @org_tg_id"; sqlHelper.query(queryString, dataArray); //JObject data = new JObject(); //data.Add("p_s_id", s_id); //String table = "ntust.pair"; //sqlHelper.delete(table, data); }
public bool IsAllOrgTeacherApprove(String org_tg_id) { sqlHelper = new SQLHelper(); //query for apply which not agree JObject dataArray = new JObject { ["scota_tg_id"] = org_tg_id }; String query = @"SELECT COUNT(*) AS count " + "FROM ntust.student_change_origin_teacher_approval scota " + "WHERE scota.scota_tg_id = @scota_tg_id AND scota.scota_state!=1"; int notApprrovalCount = Convert.ToInt32(sqlHelper.query(query, dataArray)["data"][0]["count"]); return(notApprrovalCount == 0 ? true : false); }
internal JObject UpdateMaxStudent(string tid, int max_student_num, string semester) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["max_student_num"] = max_student_num, ["tid"] = tid, ["semester"] = semester }; String queryString = "UPDATE ms set ms.ms_max_student_num=@max_student_num " + "FROM ntust.max_student as ms WHERE ms.ms_t_id=@tid AND ms.ms_semester=@semester"; JObject updateStatus = sqlHelper.query(queryString, dataArray); return(updateStatus); }
public int CheckAllApply(String tg_id) { //找出所有未同意的老師申請的數量 sqlHelper = new SQLHelper(); JObject obj = new JObject(); JObject dataArray = new JObject { ["sa_tg_id"] = tg_id }; String queryString = " SELECT COUNT(*) AS count FROM " + "ntust.student_apply sa " + "WHERE sa.sa_tg_id =@sa_tg_id AND sa.sa_state != 1 "; JObject updateStatus = sqlHelper.query(queryString, dataArray); return(Convert.ToInt32(updateStatus["data"][0]["count"])); }
public JObject AddChangePair(String new_tg_id, String s_id, String time) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["new_tg_id"] = new_tg_id, ["s_id"] = s_id }; //String queryString = @"INSERT into ntust.pair (p_tg_id, p_s_id ,p_pair_date) " + // "SELECT sc.sc_tg_id, @s_id,'"+now+"' " + // "from ntust.student_change sc " + // "WHERE sc.sc_id = @sc_id "; String queryString = @"INSERT into ntust.pair (p_tg_id, p_s_id ,p_pair_date) VALUES(@new_tg_id, @s_id,'" + time + "')"; return(sqlHelper.query(queryString, dataArray)); }
public bool IsAllTeacherApprove(String sc_tg_id, String scota_tg_id) { JObject dataArray = new JObject { ["sc_tg_id"] = sc_tg_id, ["scota_tg_id"] = scota_tg_id }; sqlHelper = new SQLHelper(); String queryString = @"SELECT SUM(t.count) as 'count' FROM (SELECT COUNT(*) AS count " + "FROM ntust.student_change_origin_teacher_approval scota " + "WHERE scota.scota_tg_id = @scota_tg_id AND scota.scota_state!=1 UNION SELECT COUNT(*) AS count " + "FROM ntust.student_change sc WHERE sc.sc_tg_id = @sc_tg_id AND sc.sc_state!=1 ) as t"; JArray res = (JArray)sqlHelper.query(queryString, dataArray).GetValue("data"); return(Convert.ToInt32(res[0]["count"]) == 0?true:false); }
public int CheckAllApply(String tg_id) { sqlHelper = new SQLHelper(); //找出所有未同意的老師申請的數量 JObject dataArray = new JObject { ["sa_tg_id"] = tg_id }; //query for apply which not agree String queryString = @" SELECT COUNT(*) AS count FROM " + "ntust.student_apply sa " + "WHERE sa.sa_tg_id = @sa_tg_id AND sa.sa_state != 1 "; JObject updateStatus = sqlHelper.query(queryString, dataArray); JArray array = (JArray)updateStatus.GetValue("data"); return(Convert.ToInt32(updateStatus["data"][0]["count"])); }
public JObject GetStudent() { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["t_id"] = t_id }; String queryString = @"SELECT s.s_id, s.s_name, s.s_state,STUFF(( " + "SELECT ','+sub_t.t_name FROM ntust.teacher as sub_t join ntust.teacher_group as sub_tg on sub_t.t_id=sub_tg.t_id " + "WHERE sub_tg.tg_id=tg.tg_id FOR XML PATH('')),1 ,1 ,'' ) AS whole_teacher " + "FROM ntust.teacher t " + "left join ntust.teacher_group tg on t.t_id=tg.t_id " + "join ntust.pair p on p.p_tg_id=tg.tg_id and p.p_end_date IS NULL " + "join ntust.student s on s.s_id = p.p_s_id " + "left join ntust.teacher t2 on t2.t_id=tg.t_id WHERE t.t_id=@t_id ;"; return(sqlHelper.query(queryString, dataArray)); }
public JObject GetApplyHistory() { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["t_id"] = t_id }; String queryString = "SELECT s.s_id,s.s_name,hsa.hsa_create_datetime, hsa.hsa_end_datetime, hsa.hsa_state, " + "STUFF ((SELECT ',' + new_t.t_name " + "FROM ntust.teacher_group as new_tg " + "join ntust.teacher as new_t on new_t.t_id = new_tg.t_id " + "WHERE hsa.hsa_tg_id = new_tg.tg_id " + "FOR XML PATH('')),1,1,'') AS Apply_teacher " + "from ntust.history_student_apply hsa " + "JOIN ntust.teacher_group as tg on tg.tg_id = hsa.hsa_tg_id " + "JOIN ntust.student as s on s.s_id = hsa.hsa_s_id " + "WHERE tg.t_id = @t_id AND hsa.hsa_end_datetime IS NOT NULL"; JObject applyHistory = sqlHelper.query(queryString, dataArray); return(applyHistory); }
public JArray getChangeHistoryByID(String s_id) { sqlHelper = new SQLHelper(); String queryString = @"SELECT DISTINCT hsc.hsc_s_id, s.s_name, hsc.hsc_create_datetime, hsc.hsc_end_datetime, hsc.hsc_state, STUFF((SELECT ', ' + new_t.t_name FROM ntust.teacher_group as new_tg join ntust.teacher as new_t on new_t.t_id = new_tg.t_id WHERE hsc.hsc_tg_id = new_tg.tg_id FOR XML PATH('')),1,1,'') AS new_teacher, STUFF((SELECT ', ' + org_t.t_name FROM ntust.teacher_group as org_tg join ntust.teacher as org_t on org_t.t_id = org_tg.t_id WHERE hsc.hsc_origin_tg_id = org_tg.tg_id FOR XML PATH('')),1,1,'') AS org_teacher FROM ntust.history_student_change as hsc JOIN ntust.teacher_group as tg on(tg.tg_id = hsc.hsc_tg_id) JOIN ntust.student as s on hsc.hsc_s_id = s.s_id join ntust.teacher_group as org_tg on(org_tg.tg_id = hsc.hsc_origin_tg_id AND hsc.hsc_end_datetime IS NOT NULL) WHERE s.s_id=@s_id"; JObject dataArray = new JObject(); dataArray["s_id"] = s_id; JObject result = sqlHelper.query(queryString, dataArray); return((JArray)result["data"]); }
public int NewTeacher(string sid, string tid) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["sid"] = sid }; String query = @"SELECT tg.t_id FROM ntust.pair as p, ntust.teacher_group as tg WHERE p.p_tg_id = tg.tg_id AND p.p_s_id = @sid AND p_end_date IS NULL"; JObject result = sqlHelper.query(query, dataArray); foreach (JObject i in result["data"]) { string id = (String)i["t_id"]; if (id == tid) { return(0); } } return(1); }
public JObject GetApply() { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["t_id"] = t_id }; String queryString = @"SELECT sa.sa_s_id, sa.sa_tg_id, s.s_name,hsa.hsa_create_datetime, 0 AS allapprove, " + "STUFF((SELECT ', ' + t.t_name " + "FROM ntust.student_apply as sa_all " + "join ntust.teacher as t on t.t_id = sa_all.sa_t_id " + "WHERE sa_all.sa_tg_id = sa.sa_tg_id " + "FOR XML PATH('')),1,1,''" + ") AS all_teacher " + "from ntust.student_apply as sa " + "join [ntust].[teacher_group] as tg on tg.tg_id = sa.sa_tg_id " + "join ntust.student as s on s.s_id = sa.sa_s_id " + "join ntust.history_student_apply as hsa on hsa.hsa_s_id = sa.sa_s_id AND hsa.hsa_tg_id = sa.sa_tg_id AND hsa.hsa_end_datetime IS NULL " + "WHERE sa.sa_state = 0 AND sa.sa_t_id = @t_id " + "UNION " + "SELECT sa.sa_s_id, sa.sa_tg_id, s.s_name,hsa.hsa_create_datetime,1 AS allapprove, " + "STUFF((SELECT ', ' + t.t_name " + "FROM ntust.student_apply as sa_all " + "join ntust.teacher as t on t.t_id = sa_all.sa_t_id " + "WHERE sa_all.sa_tg_id = sa.sa_tg_id " + "FOR XML PATH('')),1,1,''" + ") AS all_teacher " + "from ntust.student_apply as sa " + "join [ntust].[teacher_group] as tg on tg.tg_id = sa.sa_tg_id " + "join ntust.student_apply as sa_notapprove on sa_notapprove.sa_tg_id = tg.tg_id and sa_notapprove.sa_state = 0 and sa_notapprove.sa_t_id != @t_id " + "join ntust.student as s on s.s_id = sa.sa_s_id " + "join ntust.history_student_apply as hsa on hsa.hsa_s_id = sa.sa_s_id AND hsa.hsa_tg_id = sa.sa_tg_id AND hsa.hsa_end_datetime IS NULL " + "WHERE(sa.sa_state = 1 OR sa.sa_state = 2) AND sa.sa_t_id = @t_id"; return(sqlHelper.query(queryString, dataArray)); }
public JObject UpdateChange(String org_tg_id, String tg_id, String s_id, String t_id, String thesis_state, String allapprove, int accept) { sqlHelper = new SQLHelper(); String queryString; JObject dataArray; if (allapprove.Equals("0")) //更新原本老師的表(scota) { dataArray = new JObject { ["scota_state"] = accept, ["scota_thesis_state"] = thesis_state, ["org_tg_id"] = org_tg_id, ["scota_t_id"] = t_id }; queryString = " UPDATE scota set scota_state = @scota_state,scota_thesis_state=@scota_thesis_state, scota_check_by_type=1 " + " FROM ntust.student_change_origin_teacher_approval scota" + " WHERE scota.scota_tg_id =@org_tg_id AND scota.scota_t_id = '" + t_id + "'"; //檢查是否原本老師全部同意:如果是就更新allapproval=1 } else //更新 申請新老師的表(sc) { dataArray = new JObject { ["sc_state"] = accept, ["sc_t_id"] = t_id, ["tg_id"] = tg_id }; queryString = "UPDATE sc set sc.sc_state = @sc_state , sc.sc_check_by_type = 1 " + "FROM ntust.student_change sc " + "WHERE sc.sc_t_id=@sc_t_id AND sc.sc_tg_id=@tg_id"; } JObject updateChange = sqlHelper.query(queryString, dataArray); return(updateChange); }
public JToken getStudentList() { JToken semesterList = getStudentSemester(); JArray returnJT = new JArray(); JObject condi = new JObject(); JObject returnValue = new JObject(); JObject studentJO; //JObject returnValue2 = new JObject(); /*student_state_event sse_id * sse_event * sse_datetime*/ string st_department = (string)st_info["st_department"]; string s_id; condi["t_department"] = st_department; returnValue = sqlHelper.select("[ntust].[teacher]", condi , select: "t_id, t_name tname"); if (!(bool)returnValue["status"]) { return(returnJT); } returnJT = (JArray)returnValue["data"]; foreach (JObject jt in returnJT) { foreach (string semester in semesterList) { jt[semester] = new JArray(); } jt["phd"] = new JArray(); jt["pt_m"] = new JArray(); jt["history"] = new JArray(); condi = new JObject(); condi["t.t_id"] = jt["t_id"]; returnValue = sqlHelper.select("[ntust].[teacher] t" + " LEFT JOIN ntust.teacher_group tg on tg.t_id=t.t_id" + " JOIN [ntust].[pair] p on tg.tg_id=p.p_tg_id AND p.p_end_date IS NULL" + " JOIN [ntust].[student] s on s.s_id=p.p_s_id" + " LEFT JOIN [ntust].[student_apply_status] sas on sas.sas_s_id=s.s_id" + " LEFT JOIN (SELECT max(sse_event) now_status, sse_s_id FROM [ntust].[student_state_event] GROUP BY sse_s_id) sse on sse.sse_s_id=s.s_id", condi , select: "(Case when sse.now_status IS NULL then '1' else sse.now_status End) as now_status , s.s_id, (Case when sas.sas_type IS NULL then '0' else sas.sas_type End) as sas_type"); if ((bool)returnValue["status"]) { foreach (JObject jo in returnValue["data"]) { studentJO = new JObject(); s_id = (string)jo["s_id"]; studentJO["sid"] = s_id; studentJO["otherTeacher"] = getOtherTeacher(s_id, (string)jt["t_id"]); studentJO["status"] = jo["now_status"]; studentJO["apply_status"] = jo["sas_type"]; if (s_id.Substring(0, 1) == "D") { ((JArray)jt["phd"]).Add(studentJO); continue; } if (s_id.Substring(6, 1) == "9") { ((JArray)jt["pt_m"]).Add(studentJO); continue; } ((JArray)jt[s_id.Substring(0, 4)]).Add(studentJO); } } returnValue = sqlHelper.select("[ntust].[teacher] t" + " JOIN ntust.teacher_group tg on tg.t_id=t.t_id" + " JOIN (SELECT p.p_tg_id, p.p_s_id, p.p_end_date,'out' as status FROM [ntust].[pair] p" + " JOIN [ntust].[pair] p2 on p.p_s_id=p2.p_s_id AND p2.p_pair_date=p.p_end_date WHERE p.p_end_date IS NOT NULL" +//out " UNION SELECT p2.p_tg_id, p2.p_s_id, p.p_end_date,'in' as status FROM [ntust].[pair] p" + " JOIN [ntust].[pair] p2 on p.p_s_id=p2.p_s_id AND p2.p_pair_date=p.p_end_date WHERE p.p_end_date IS NOT NULL) out on tg.tg_id=out.p_tg_id" + " JOIN [ntust].[student] s on s.s_id=out.p_s_id", condi , select: "s.s_name , s.s_id, out.p_end_date as datetime, out.status as in_or_out"); jt["history"] = (JToken)returnValue["data"]; //Get max student by teacher JObject dataArray = new JObject { ["tid"] = jt["t_id"] }; String queryString = "SELECT * from ntust.max_student as ms WHERE ms.ms_t_id=@tid"; returnValue = sqlHelper.query(queryString, dataArray); jt["max_student"] = (JToken)returnValue["data"]; System.Diagnostics.Debug.Print("========================================="); } ////////////////// JObject noTeacherStudent = new JObject(); foreach (string semester in semesterList) { noTeacherStudent[semester] = new JArray(); } noTeacherStudent["phd"] = new JArray(); noTeacherStudent["pt_m"] = new JArray(); noTeacherStudent["history"] = new JArray(); condi = new JObject(); condi["s.s_department"] = st_department; condi["p.p_id"] = "null"; returnValue = sqlHelper.select("[ntust].[student] s" + " LEFT JOIN [ntust].[pair] p on s.s_id=p.p_s_id" + " LEFT JOIN [ntust].[student_apply_status] sas on sas.sas_s_id=s.s_id" + " LEFT JOIN (SELECT max(sse_event) now_status, sse_s_id FROM [ntust].[student_state_event] GROUP BY sse_s_id) sse on sse.sse_s_id=s.s_id", condi , select: "(Case when sse.now_status IS NULL then '1' else sse.now_status End) as now_status, s.s_id, (Case when sas.sas_type IS NULL then '0' else sas.sas_type End) as sas_type"); if ((bool)returnValue["status"]) { foreach (JObject jo in returnValue["data"]) { studentJO = new JObject(); s_id = (string)jo["s_id"]; studentJO["sid"] = s_id; studentJO["status"] = jo["now_status"]; if (s_id.Substring(0, 1) == "D") { ((JArray)noTeacherStudent["phd"]).Add(studentJO); continue; } if (s_id.Substring(6, 1) == "9") { ((JArray)noTeacherStudent["pt_m"]).Add(studentJO); continue; } ((JArray)noTeacherStudent[s_id.Substring(0, 4)]).Add(studentJO); } } noTeacherStudent["tname"] = "無人認養"; noTeacherStudent["max_student"] = ""; returnJT.Add(noTeacherStudent); return(returnJT); }
public JObject GetChange() { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["t_id"] = t_id }; String queryString = "SELECT DISTINCT @t_id AS t_id,sc_t_id, STUFF((SELECT ',' + org_t.t_id " + "FROM ntust.teacher_group as org_tg join ntust.teacher as org_t " + "on org_t.t_id = org_tg.t_id " + "WHERE hsc.hsc_origin_tg_id = org_tg.tg_id FOR XML PATH('')), 1, 1, '') " + "AS org_t_id " + ", STUFF ((SELECT ',' + new_t.t_id " + "FROM ntust.teacher_group as new_tg join ntust.teacher as new_t " + "on new_t.t_id = new_tg.t_id " + "WHERE sc.sc_tg_id = new_tg.tg_id FOR XML PATH('')), 1, 1, '') AS new_t_id," + "sc.sc_s_id, s.s_name, hsc.hsc_create_datetime" + ", sc.sc_tg_id, hsc.hsc_origin_tg_id," + "STUFF((SELECT ',' + new_t.t_name FROM ntust.teacher_group as new_tg join ntust.teacher as new_t " + "on new_t.t_id = new_tg.t_id WHERE sc.sc_tg_id = new_tg.tg_id FOR XML PATH('')), 1, 1, '') " + "AS new_teacher," + "STUFF ((SELECT ',' + org_t.t_name FROM ntust.teacher_group as org_tg join ntust.teacher as org_t " + "on org_t.t_id = org_tg.t_id " + "WHERE hsc.hsc_origin_tg_id = org_tg.tg_id FOR XML PATH('')), 1, 1, '') AS org_teacher," + "sc.sc_all_approval, scota.scota_state, sc.sc_state, p.p_id " + "FROM ntust.student_change as sc " + "join ntust.teacher_group as tg on sc.sc_tg_id = tg.tg_id " + "join ntust.student as s on s.s_id = sc.sc_s_id " + "join ntust.history_student_change " + "as hsc on hsc.hsc_s_id = s.s_id AND hsc.hsc_end_datetime IS NULL AND hsc.hsc_tg_id = sc.sc_tg_id " + "join ntust.teacher_group as org_tg on org_tg.tg_id = hsc.hsc_origin_tg_id " + "left join ntust.student_change_origin_teacher_approval " + "as scota on scota.scota_tg_id = org_tg.tg_id AND scota.scota_t_id = @t_id " + "left join ntust.pair as p on p.p_tg_id = sc.sc_tg_id " + "WHERE((org_tg.t_id = @t_id AND sc.sc_state = 0 AND hsc.hsc_end_datetime IS NULL) " + "OR(sc.sc_t_id = @t_id AND sc.sc_all_approval = 1 AND hsc.hsc_end_datetime IS NULL)) AND p.p_tg_id is NULL"; //String queryString = "SELECT DISTINCT sc.sc_id, scota.scota_t_id,sc.sc_t_id, sc.sc_s_id, s.s_name, hsc.hsc_create_datetime, sc.sc_tg_id, hsc.hsc_origin_tg_id, " + // "sc.sc_tg_id, hsc.hsc_origin_tg_id, " + // "STUFF ((SELECT ', ' + new_t.t_name " + // "FROM ntust.teacher_group as new_tg join ntust.teacher as new_t " + // "on new_t.t_id = new_tg.t_id " + // "WHERE sc.sc_tg_id = new_tg.tg_id FOR XML PATH('')), 1, 1, '') AS new_teacher, " + // "STUFF " + // "((SELECT ', ' + org_t.t_name " + // "FROM ntust.teacher_group as org_tg " + // "join ntust.teacher as org_t on org_t.t_id = org_tg.t_id " + // "WHERE hsc.hsc_origin_tg_id = org_tg.tg_id " + // "FOR XML PATH('')), 1, 1, '') AS org_teacher, sc.sc_all_approval, scota.scota_state, sc.sc_state, p.p_id " + // "FROM ntust.student_change as sc " + // "join ntust.teacher_group as tg on sc.sc_tg_id = tg.tg_id " + // "join ntust.teacher as t on t.t_id = sc.sc_t_id " + // "join ntust.student as s on s.s_id = sc.sc_s_id " + // "join ntust.history_student_change as hsc on hsc.hsc_s_id = s.s_id AND hsc.hsc_end_datetime IS NULL AND hsc.hsc_tg_id = sc.sc_tg_id " + // "join ntust.teacher_group as org_tg on org_tg.tg_id = hsc.hsc_origin_tg_id " + // "left join ntust.student_change_origin_teacher_approval as scota on scota.scota_tg_id = org_tg.tg_id AND scota.scota_t_id = @t_id " + // "left join ntust.pair as p on p.p_tg_id = sc.sc_tg_id " + // "WHERE((org_tg.t_id = @t_id AND sc.sc_state = 0 AND hsc.hsc_end_datetime IS NULL) OR(t.t_id = @t_id AND sc.sc_all_approval = 1 AND hsc.hsc_end_datetime IS NULL)) AND p.p_tg_id is NULL"; return(sqlHelper.query(queryString, dataArray)); }