public JObject AddChangePair(String sc_id, String s_id)
{
sqlHelper = new SQLHelper();
String query = "INSERT into ntust.pair (p_tg_id, p_s_id ,p_pair_date) " +
"SELECT sc.sc_tg_id, '" + s_id + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' " +
"from ntust.student_change sc " +
"WHERE sc.sc_id = " + sc_id;
JObject res = sqlHelper.insert(query);
return(res);
}
public JObject AddApplyPair(String tg_id, String s_id)
{
sqlHelper = new SQLHelper();
//String query = "INSERT INTO ntust.pair (p_tg_id, p_s_id, p_pair_date) VALUES ("+ tg_id +", '"+ s_id +"', '')";
String table = "ntust.pair";
JObject obj = new JObject();
obj.Add("p_tg_id", tg_id);
obj.Add("p_s_id", s_id);
obj.Add("p_pair_date", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
JObject res = sqlHelper.insert(table, obj);
return(res);
}
//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);
}
//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);
}
public JObject addNewExtraTeacher(String t_name, String t_email, String t_phone, String t_telephone, String t_service_units)
{
sqlHelper = new SQLHelper();
JObject callback = new JObject();
JObject condi = new JObject();
condi["t_email"] = t_email;
JObject returnValue = sqlHelper.select("[ntust].[extra_teacher]", condi);
if ((bool)returnValue["status"])
{
callback["status"] = false;
callback["msg"] = "email is exist.";
return(callback);
}
condi = new JObject();
condi["t_phone"] = t_phone;
returnValue = sqlHelper.select("[ntust].[extra_teacher]", condi);
if ((bool)returnValue["status"])
{
callback["status"] = false;
callback["msg"] = "phone is exist.";
return(callback);
}
condi = new JObject();
condi["t_telephone"] = t_telephone;
returnValue = sqlHelper.select("[ntust].[extra_teacher]", condi);
if ((bool)returnValue["status"])
{
callback["status"] = false;
callback["msg"] = "telephone is exist.";
return(callback);
}
String t_id;
while (true)
{
t_id = RandomString(8);
condi = new JObject();
condi["t_id"] = t_id;
returnValue = sqlHelper.select("[ntust].[extra_teacher]", condi);
if (!(bool)returnValue["status"])
{
break;
}
}
String table = "ntust.extra_teacher";
condi = new JObject();
condi["t_id"] = t_id;
condi["t_name"] = t_name;
condi["t_email"] = t_email;
condi["t_phone"] = t_phone;
condi["t_telephone"] = t_telephone;
condi["t_service_units"] = t_service_units;
condi["t_create_by"] = 2;
condi["t_create_by_id"] = st_id;
condi["t_create_datetime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
JObject res = sqlHelper.insert(table, condi);
return(res);
}