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);
}