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));
}
public TeacherHelper()
{
sqlHelper = new SQLHelper();
}