public void UpdateStudentChangeHistory(String tg_id, int state) { sqlHelper = new SQLHelper(); String query = "UPDATE hsc set hsc_end_datetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' ,hsc_state=" + state + " FROM ntust.history_student_change hsc WHERE hsc_origin_tg_id = " + tg_id; sqlHelper.update(query); }
public void UpdateStudentApplyHistory(String tg_id, int state) { 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); }
public JObject UpdateStudentChangeApproval(String sc_id) { sqlHelper = new SQLHelper(); String query = "UPDATE sc set sc.sc_all_approval = 1 " + "FROM ntust.student_change sc " + "WHERE sc.sc_id = " + sc_id; JObject updateSCA = sqlHelper.update(query); return(updateSCA); }
public JObject UpdateApply(String tid, String tg_id, int accept) { sqlHelper = new SQLHelper(); JObject obj = new JObject(); String query = " update sa set sa_state=" + accept + ", sa_check_by_type=1, sa_check_by_st_id='" + tid + "'" + " FROM ntust.student_apply sa" + " WHERE sa_tg_id = '" + tg_id + "' AND sa_t_id = '" + tid + "'"; JObject updateStatus = sqlHelper.update(query); return(updateStatus); }
public void UpdateStudentChangeHistory(String tg_id, int state, String time) { sqlHelper = new SQLHelper(); JObject dataArray = new JObject { ["hsa_state"] = state, ["hsc_origin_tg_id"] = tg_id }; String query = @"UPDATE hsc set hsc_end_datetime='" + time + "' ,hsc_state=@hsa_state FROM ntust.history_student_change hsc WHERE hsc_origin_tg_id = @hsc_origin_tg_id"; sqlHelper.update(query); }
public JObject UpdateChange(String sc_id, String s_id, String t_id, String thesis_state, String allapprove, int accept) { sqlHelper = new SQLHelper(); String query; if (allapprove.Equals("0")) //更新原本老師的表(scota) { query = " UPDATE scota set scota_state = " + accept + ",scota_thesis_state=" + thesis_state + ", scota_check_by_type=1, scota_check_by_st_id='" + t_id + "' " + " FROM ntust.student_change_origin_teacher_approval scota" + " WHERE scota.scota_sc_id ='" + sc_id + "' AND scota.scota_t_id = '" + t_id + "'"; //檢查是否原本老師全部同意:如果是就更新allapproval=1 } else //更新 申請新老師的表(sc) { query = " UPDATE sc set sc.sc_state = " + accept + ", sc.sc_check_by_type = 1, sc_check_by_st_id = '" + t_id + "'" + " FROM ntust.student_change sc" + " WHERE sc.sc_id ='" + sc_id + "' AND sc.sc_t_id = '" + t_id + "'"; } JObject updateChange = sqlHelper.update(query); return(updateChange); }
//Request.Form["main"], Request.Form["sub"] public JObject studentChange(string main, JToken sub, string para_s_id = null) { //JObject insertData = new JObject(); JArray insertData = new JArray(); JArray insertData2 = new JArray(); SQLHelper sqlHelper = new SQLHelper(); string s_id_value = para_s_id == null ? s_id : para_s_id; int tgId = getNewTgId(); int oriTgId = getOriTgId(s_id_value); if (oriTgId == 0) { JObject callback = new JObject(); callback["status"] = false; callback["msg"] = "get ori tg id is fail"; return(callback); } /*insertData["sa_s_id"] = s_id; * insertData["sa_t_id"] = main; * insertData["sa_t_type"] = 1; * insertData["sa_tg_id"] = tgId; * insertData["sa_state"] = 0; * insertData["sa_create_by_type"] = 1; * insertData["sa_create_by_st_id"] = ""; * JObject returnValue = sqlHelper.insert("[ntust].[student_apply]", insertData);*/ JObject insertObj = new JObject(); JObject insertObj2 = new JObject(); insertObj["sc_s_id"] = s_id_value; insertObj["sc_t_id"] = main; insertObj["sc_t_type"] = 1; insertObj["sc_tg_id"] = tgId; insertObj["sc_state"] = 0; insertObj["sc_create_by_type"] = 1; insertObj["sc_create_by_st_id"] = ""; insertObj["sc_all_approval"] = 0; insertData.Add(insertObj); insertObj2["tg_id"] = tgId; insertObj2["t_id"] = main; insertObj2["t_type"] = 1; insertObj2["t_order"] = 1; insertData2.Add(insertObj2); foreach (var x in sub) { JObject tmp = (JObject)x; insertObj = new JObject(); insertObj["sc_s_id"] = s_id_value; insertObj["sc_t_id"] = tmp["t_id"]; insertObj["sc_t_type"] = tmp["t_type"]; insertObj["sc_tg_id"] = tgId; insertObj["sc_state"] = 0; insertObj["sc_create_by_type"] = 1; insertObj["sc_create_by_st_id"] = ""; insertObj["sc_all_approval"] = 0; insertData.Add(insertObj); insertObj2 = new JObject(); insertObj2["tg_id"] = tgId; insertObj2["t_id"] = tmp["t_id"]; insertObj2["t_type"] = tmp["t_type"]; insertObj2["t_order"] = 2; insertData2.Add(insertObj2); } JObject returnValue = sqlHelper.insertMulti("[ntust].[student_change]", insertData); if (!(bool)returnValue["status"]) { return(returnValue); } returnValue = sqlHelper.insertMulti("[ntust].[teacher_group]", insertData2); if (!(bool)returnValue["status"]) { return(returnValue); } //刪除同樣這origin teacher group JObject condi = new JObject(); condi["scota_tg_id"] = oriTgId.ToString(); returnValue = sqlHelper.delete("ntust.student_change_origin_teacher_approval", condi); if (!(bool)returnValue["status"]) { return(returnValue); } //新增新的student change String query = "INSERT INTO ntust.student_change_origin_teacher_approval(scota_tg_id, scota_t_id, scota_thesis_state, scota_state, scota_create_by_type)" + " SELECT tg.tg_id, tg.t_id, 0, 0, 1" + " FROM ntust.teacher_group AS tg WHERE tg.tg_id = " + oriTgId.ToString(); returnValue = sqlHelper.insert(query); if (!(bool)returnValue["status"]) { return(returnValue); } DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff"); //檢查有沒有未完成的異動 query = "UPDATE ntust.history_student_change set hsc_state=2, hsc_end_datetime='" + sqlFormattedDate + "' WHERE hsc_s_id = '" + s_id_value + "' AND hsc_state=0"; returnValue = sqlHelper.update(query); if (!(bool)returnValue["status"]) { return(returnValue); } insertObj = new JObject(); insertObj["hsc_s_id"] = s_id_value; insertObj["hsc_origin_tg_id"] = oriTgId; insertObj["hsc_tg_id"] = tgId; insertObj["hsc_create_datetime"] = sqlFormattedDate; insertObj["hsc_state"] = 0; returnValue = sqlHelper.insert("[ntust].[history_student_change]", insertObj); if (!(bool)returnValue["status"]) { return(returnValue); } //check student_apply_status exist condi = new JObject(); condi["sas_s_id"] = s_id_value; returnValue = sqlHelper.select("[ntust].[student_apply_status]", condi); if ((bool)returnValue["status"]) { query = "UPDATE ntust.student_apply_status set sas_tg_id=" + tgId + " ,sas_type=2 WHERE sas_s_id = '" + s_id_value + "'"; returnValue = sqlHelper.update(query); } else { JObject insertData3 = new JObject(); insertData3["sas_s_id"] = s_id_value; insertData3["sas_type"] = 2; insertData3["sas_tg_id"] = tgId; returnValue = sqlHelper.insert("[ntust].[student_apply_status]", insertData3); } //returnValue["sub"] = sub; //returnValue["insertData"] = insertData; /*if ((bool)returnValue["status"]) * { * return new HttpStatusCodeResult(200); * } * else * { * String msg = returnValue["msg"].ToString(); * switch (msg) { * case "a": * break; * } * return new HttpStatusCodeResult(404); * }*/ return(returnValue); }
//Request.Form["main"], Request.Form["sub"] public JObject studentApply(string main, JToken sub, string para_s_id = null) { //JObject insertData = new JObject(); JArray insertData = new JArray(); JArray insertData2 = new JArray(); SQLHelper sqlHelper = new SQLHelper(); string s_id_value = para_s_id == null ? s_id : para_s_id; int tgId = getNewTgId(); /*insertData["sa_s_id"] = s_id; * insertData["sa_t_id"] = main; * insertData["sa_t_type"] = 1; * insertData["sa_tg_id"] = tgId; * insertData["sa_state"] = 0; * insertData["sa_create_by_type"] = 1; * insertData["sa_create_by_st_id"] = ""; * JObject returnValue = sqlHelper.insert("[ntust].[student_apply]", insertData);*/ JObject insertObj = new JObject(); JObject insertObj2 = new JObject(); insertObj["sa_s_id"] = s_id_value; insertObj["sa_t_id"] = main; insertObj["sa_t_type"] = 1; insertObj["sa_tg_id"] = tgId; insertObj["sa_state"] = 0; insertObj["sa_create_by_type"] = 1; insertObj["sa_create_by_st_id"] = ""; insertData.Add(insertObj); insertObj2["tg_id"] = tgId; insertObj2["t_id"] = main; insertObj2["t_type"] = 1; insertObj2["t_order"] = 1; insertData2.Add(insertObj2); foreach (var x in sub) { JObject tmp = (JObject)x; insertObj = new JObject(); insertObj["sa_s_id"] = s_id_value; insertObj["sa_t_id"] = tmp["t_id"]; insertObj["sa_t_type"] = tmp["t_type"]; insertObj["sa_tg_id"] = tgId; insertObj["sa_state"] = 0; insertObj["sa_create_by_type"] = 1; insertObj["sa_create_by_st_id"] = ""; insertData.Add(insertObj); insertObj2 = new JObject(); insertObj2["tg_id"] = tgId; insertObj2["t_id"] = tmp["t_id"]; insertObj2["t_type"] = tmp["t_type"]; insertObj2["t_order"] = 2; insertData2.Add(insertObj2); } JObject returnValue = sqlHelper.insertMulti("[ntust].[student_apply]", insertData); if (!(bool)returnValue["status"]) { return(returnValue); } returnValue = sqlHelper.insertMulti("[ntust].[teacher_group]", insertData2); if (!(bool)returnValue["status"]) { return(returnValue); } DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff"); //檢查有沒有未完成的申請 String query = "UPDATE ntust.history_student_apply set hsa_state=2, hsa_end_datetime='" + sqlFormattedDate + "' WHERE hsa_s_id = '" + s_id_value + "' AND hsa_state=0"; returnValue = sqlHelper.update(query); if (!(bool)returnValue["status"]) { return(returnValue); } insertObj = new JObject(); insertObj["hsa_s_id"] = s_id_value; insertObj["hsa_tg_id"] = tgId; insertObj["hsa_create_datetime"] = sqlFormattedDate; insertObj["hsa_state"] = 0; returnValue = sqlHelper.insert("[ntust].[history_student_apply]", insertObj); if (!(bool)returnValue["status"]) { return(returnValue); } //check student_apply_status exist JObject condi = new JObject(); condi["sas_s_id"] = s_id_value; returnValue = sqlHelper.select("[ntust].[student_apply_status]", condi); if ((bool)returnValue["status"]) { query = "UPDATE ntust.student_apply_status set sas_tg_id=" + tgId + " ,sas_type=1 WHERE sas_s_id = '" + s_id_value + "'"; returnValue = sqlHelper.update(query); } else { JObject insertData3 = new JObject(); insertData3["sas_s_id"] = s_id_value; insertData3["sas_type"] = 1; insertData3["sas_tg_id"] = tgId; returnValue = sqlHelper.insert("[ntust].[student_apply_status]", insertData3); } //returnValue["sub"] = sub; //returnValue["insertData"] = insertData; /*if ((bool)returnValue["status"]) * { * return new HttpStatusCodeResult(200); * } * else * { * String msg = returnValue["msg"].ToString(); * switch (msg) { * case "a": * break; * } * return new HttpStatusCodeResult(404); * }*/ return(returnValue); }