public JToken getDepartmentList()
{
JArray returnJA = new JArray();
JObject returnValue = sqlHelper.select("[ntust].[teacher]", new JObject(), "GROUP BY t_department", "t_department");
if ((bool)returnValue["status"])
{
return(returnValue["data"]);
}
else
{
return(returnJA);
}
}
public JObject GetApply(String tid)
{
sqlHelper = new SQLHelper();
JObject obj = new JObject();
obj["sa.sa_state"] = 0;
obj["sa.sa_t_id"] = tid;
String select = " sa.sa_s_id, s.s_name, hsa.hsa_create_datetime, sa.sa_tg_id";
//return sqlHelper.select("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"
// , obj, select: select);
String queryStr = "SELECT sa.sa_s_id, sa.sa_tg_id, s.s_name,hsa.hsa_create_datetime, 0 AS allapprove " +
"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 " +
"WHERE sa.sa_state = 0 AND sa.sa_t_id = '" + tid + "' " +
"UNION " +
"SELECT sa.sa_s_id, sa.sa_tg_id, s.s_name,hsa.hsa_create_datetime,1 AS allapprove " +
"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 != '" + tid + "' " +
"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 " +
"WHERE(sa.sa_state = 1 OR sa.sa_state = 2) AND sa.sa_t_id = '" + tid + "'";
return(sqlHelper.select(queryStr));
}
public JObject UpdateStudentApplyStatus(String s_id, int state)
{
sqlHelper = new SQLHelper();
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);
return(applyStatus);
}
public int CheckNewChange(String sc_id)
{
sqlHelper = new SQLHelper();
//query for apply which not agree
String query = "SELECT COUNT(*) AS count " +
"FROM ntust.student_change_origin_teacher_approval scota " +
"WHERE scota.scota_sc_id = " + sc_id + " AND scota.scota_state!=1";
JObject updateStatus = sqlHelper.select(query);
return(Convert.ToInt32(updateStatus["data"][0]["count"]));
}
public JObject GetStudent(String tid)
{
sqlHelper = new SQLHelper();
JObject teacher = new JObject();
teacher["t.t_id"] = tid;
String 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 ";
return(sqlHelper.select("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 " +
" join ntust.student s on s.s_id = p.p_s_id " +
" left join ntust.teacher t2 on t2.t_id=tg.t_id", teacher, select: select));
}
public int CheckAllApply(String tg_id)
{
sqlHelper = new SQLHelper();
//找出所有未同意的老師申請的數量
JObject obj = new JObject();
//query for apply which not agree
String query = " SELECT COUNT(*) AS count FROM " +
"ntust.student_apply sa " +
"WHERE sa.sa_tg_id = " + tg_id + " AND sa.sa_state != 1 ";
JObject updateStatus = sqlHelper.select(query);
JArray array = (JArray)updateStatus.GetValue("data");
System.Diagnostics.Debug.Print(array.ToString());
return(Convert.ToInt32(updateStatus["data"][0]["count"]));
}
public JObject GetApplyHistory(String tid)
{
sqlHelper = new SQLHelper();
String query = "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 = '" + tid + "' AND hsa.hsa_end_datetime IS NOT NULL";
JObject applyHistory = sqlHelper.select(query);
return(applyHistory);
}
public JObject GetChangeHistory(String tid)
{
sqlHelper = new SQLHelper();
String query = "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 = '" + tid + "') OR(org_tg.t_id = '" + tid + "')";
JObject changeHistory = sqlHelper.select(query);
return(changeHistory);
}
public int getNewTgId()
{
SQLHelper sqlHelper = new SQLHelper();
JObject returnValue = sqlHelper.select("[ntust].[teacher_group]", new JObject(), "ORDER BY tg_id DESC ", "top 1 tg_id as max");
if ((bool)returnValue["status"])
{
JToken data = returnValue["data"];
JObject dataJO = (JObject)data[0];
System.Diagnostics.Debug.Print(data.ToString());
System.Diagnostics.Debug.Print("MAX=" + dataJO["max"].ToString());
return((int)dataJO["max"] + 1);
//return dataJO.ToString();
}
else
{
return(1);
}
}
public JObject getAdminInfo(string userId)
{
JObject condi = new JObject();
condi["st_u_id"] = userId;
JObject returnValue = sqlHelper.select("[ntust].[staff]", condi);
if ((bool)returnValue["status"])
{
foreach (JToken jt in returnValue["data"])
{
st_info = (JObject)jt;
st_id = (string)st_info["st_id"];
}
return(st_info);
}
else
{
//String msg = returnValue["msg"].ToString();
return(st_info);
}
}
public JObject GetChange(String tid)
{
sqlHelper = new SQLHelper();
JObject obj = new JObject();
string t_id = tid;
String select = " DISTINCT sc.sc_id, sc.sc_t_id, sc.sc_s_id, s.s_name, sc.sc_id, hsc.hsc_create_datetime,hsc.hsc_origin_tg_id, 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, sc.sc_all_approval, scota.scota_state, sc.sc_state, p.p_id, scota.scota_t_id ";
String where = " ((org_tg.t_id = '" + t_id + "' AND sc.sc_state=0) OR (t.t_id = '" + t_id + "' AND sc.sc_all_approval=1 AND sc.sc_state=0)) AND p.p_tg_id is NULL";
return(sqlHelper.select("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" +
" left join ntust.student_change_origin_teacher_approval as scota on scota.scota_sc_id = sc.sc_id AND scota.scota_t_id ='" + t_id + "' " +
" join ntust.teacher_group as org_tg on org_tg.tg_id = hsc.hsc_origin_tg_id" +
" left join ntust.pair as p on p.p_tg_id = sc.sc_tg_id"
, obj, select: select, where : where));
}
public int getOriTgId(string para_s_id = null)
{
SQLHelper sqlHelper = new SQLHelper();
JObject condi = new JObject();
condi["p_s_id"] = para_s_id == null ? s_id : para_s_id;
JObject returnValue = sqlHelper.select("[ntust].[pair]", condi, "ORDER BY p_tg_id DESC ", "top 1 p_tg_id as max");
if ((bool)returnValue["status"])
{
JToken data = returnValue["data"];
JObject dataJO = (JObject)data[0];
System.Diagnostics.Debug.Print(data.ToString());
System.Diagnostics.Debug.Print("MAX=" + dataJO["max"].ToString());
return((int)dataJO["max"]);
//return dataJO.ToString();
}
else
{
return(0);
}
}
//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);
}
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);
}
