public async Task <IHttpActionResult> PostForLogin(JObject usrpwdata)
{
List <System.Net.Http.Headers.CookieHeaderValue> x = Request.Headers.GetCookies("mymy").ToList();
if (x.Count == 1)
{
//If login cookie exists:Return error to indicate that user already logged in
return(BadRequest("ท่านได้เข้าสู่ระบบอยู่แล้ว"));
}
UsernamePassword data = new UsernamePassword();
data.username = usrpwdata["username"].ToString();
data.password = usrpwdata["password"].ToString();
if (data.username == "" && data.password == "")
{
return(BadRequest("กรุณาใส่ชื่อผู้ใช้และรหัสผ่านที่ต้องการเข้าสู่ระบบ"));
}
else if (data.username == "")
{
return(BadRequest("กรุณาใส่ชื่อผู้ใช้งานที่ต้องการเข้าสู่ระบบ"));
}
else if (data.password == "")
{
return(BadRequest("กรุณาใส่รหัสผ่านที่ใช้ในการเข้าสู่ระบบ"));
}
oUsers context = new oUsers();
data.username = data.username.ToLower();
object result = await context.SelectUser(data.username);
//Check whether login is success?
if (result.GetType().ToString() != "System.String")
{
User_information_with_privilege_information u = (User_information_with_privilege_information)result;
string oldpassword = data.password;
data.password = u.information.GetPassword();
if (data.isMatchPassword(oldpassword))
{
return(Ok(u));
}
else
{
return(BadRequest("ชื่อผู้ใช้งานหรือรหัสผ่านไม่ถูกต้อง"));
}
}
else
{
return(BadRequest("ชื่อผู้ใช้งานหรือรหัสผ่านไม่ถูกต้อง"));
}
}
public async Task<object> UpdateUserData(User_information_with_privilege_information userdata)
{
DBConnector d = new DBConnector();
User_information_with_privilege_information result = new User_information_with_privilege_information();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
string temp80tablename = "#temp80";
string createtabletemp80 = string.Format("create table {0}(" +
"[row_num] int identity(1, 1) not null," +
"[file_name_pic_del] {1} null," +
"primary key([row_num])) " +
"alter table {0} " +
"alter column[file_name_pic_del] {1} collate database_default ",
temp80tablename, DBFieldDataType.FILE_NAME_TYPE);
string mainupdatecmd = "";
if(userdata.information.file_name_pic == null)
{
mainupdatecmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}', {5} = '{6}', {7} = '{8}'," +
"{9} = '{10}', {11} = '{12}' where {13} = {14} ",
User_list.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, userdata.information.t_prename,
Teacher.FieldName.T_NAME, userdata.information.t_name,
Teacher.FieldName.E_PRENAME, userdata.information.e_prename,
Teacher.FieldName.E_NAME, userdata.information.e_name,
Teacher.FieldName.TEL, userdata.information.tel,
Teacher.FieldName.ADDR, userdata.information.addr,
User_list.FieldName.USER_ID, userdata.user_id);
}
else
{
mainupdatecmd = string.Format("insert into {17} " +
"select * from " +
"(update {0} set {1} = '{2}', {3} = '{4}', {5} = '{6}', {7} = '{8}'," +
"{9} = '{10}', {11} = '{12}', {13} = '{14}' output deleted.{13} where {15} = {16}) as outputupdate ",
User_list.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, userdata.information.t_prename,
Teacher.FieldName.T_NAME, userdata.information.t_name,
Teacher.FieldName.E_PRENAME, userdata.information.e_prename,
Teacher.FieldName.E_NAME, userdata.information.e_name,
Teacher.FieldName.TEL, userdata.information.tel,
Teacher.FieldName.ADDR, userdata.information.addr,
Teacher.FieldName.FILE_NAME_PIC, userdata.information.file_name_pic,
User_list.FieldName.USER_ID, userdata.user_id,temp80tablename);
}
//email must UNIQUE
string emailupdatecmd = string.Format("if not exists (select * from {0} where {1} = '{2}' and {3} != {4}) " +
"BEGIN " +
"update {0} set {1} = '{2}' where {3} = {4} " +
"END ",
User_list.FieldName.TABLE_NAME, Teacher.FieldName.EMAIL, userdata.information.email, User_list.FieldName.USER_ID, userdata.user_id);
string updateteachertable = "";
string deletefromtechin = "";
string insertintotechin = "";
if (userdata.user_type == "อาจารย์") {
updateteachertable = string.Format("update {0} set {1} = '{2}' where {3} = {4} ",
Teacher.FieldName.TABLE_NAME, Teacher.FieldName.STATUS, userdata.information.status,
Teacher.FieldName.TEACHER_ID, userdata.user_id);
deletefromtechin = string.Format("delete from {0} where {1} = {2} ",
Technical_interested.FieldName.TABLE_NAME,Technical_interested.FieldName.TEACHER_ID,
userdata.user_id);
if (userdata.information.interest.Count != 0) {
insertintotechin = string.Format("insert into {0} values ",
Technical_interested.FieldName.TABLE_NAME);
int insertintotechinlength = insertintotechin.Length;
foreach (string topic in userdata.information.interest)
{
if (insertintotechin.Length <= insertintotechinlength)
insertintotechin += string.Format("({0},'{1}')", userdata.user_id, topic);
else
insertintotechin += string.Format(",({0},'{1}')", userdata.user_id, topic);
}
}
}
string deletefromeducationcmd = "";
if (userdata.user_type != "นักศึกษา")
{
deletefromeducationcmd = string.Format("delete from {0} where {1} = {2} ",
Educational_teacher_staff.FieldName.TABLE_NAME, Educational_teacher_staff.FieldName.PERSONNEL_ID,
userdata.user_id);
string excludecmd = "1=1 ";
foreach (Educational_teacher_staff e in userdata.information.education)
excludecmd += string.Format("and {0} != {1} ",Educational_teacher_staff.FieldName.EDUCATION_ID,e.education_id);
deletefromeducationcmd += string.Format("and ({0}) ", excludecmd);
}
string selectuserdatacmd = "";
if (userdata.user_type == "อาจารย์")
selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 0, "#temp99");
else if (userdata.user_type == "เจ้าหน้าที่")
selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 1, "#temp98");
else if (userdata.user_type == "นักศึกษา")
selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 2, "#temp97");
else if (userdata.user_type == "ศิษย์เก่า")
selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 3, "#temp96");
else if (userdata.user_type == "บริษัท")
selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 4, "#temp95");
else if (userdata.user_type == "ผู้ประเมินจากภายนอก")
selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 5, "#temp94");
else if (userdata.user_type == "ผู้ดูแลระบบ")
selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 6, "#temp93");
else
selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 7, "#temp92");
string selectfiletodelcmd = string.Format("select * from {0} ", temp80tablename);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} {7} {8} END ",
createtabletemp80, mainupdatecmd, emailupdatecmd, updateteachertable, deletefromtechin, insertintotechin,
deletefromeducationcmd, selectuserdatacmd, selectfiletodelcmd);
file_name_pic = null;
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable tabledata = new DataTable();
tabledata.Load(res);
foreach (DataRow item in tabledata.Rows)
{
if (tabledata.Columns.Contains(Teacher.FieldName.T_PRENAME))
{
//1 retrieve user_data from pre-defined select table command
string usrtype = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString();
if (usrtype == "อาจารย์")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]);
else if (usrtype == "เจ้าหน้าที่")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Staff.FieldName.STAFF_ID].Ordinal]);
else if (usrtype == "นักศึกษา" || usrtype == "ศิษย์เก่า")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Student.FieldName.USER_ID].Ordinal]);
else if (usrtype == "บริษัท")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Company.FieldName.COMPANY_ID].Ordinal]);
else if (usrtype == "ผู้ประเมินจากภายนอก")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Assessor.FieldName.ASSESSOR_ID].Ordinal]);
else if (usrtype == "ผู้ดูแลระบบ")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Admin.FieldName.ADMIN_ID].Ordinal]);
else
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[User_list.FieldName.USER_ID].Ordinal]);
result.username = item.ItemArray[tabledata.Columns[Teacher.FieldName.USERNAME].Ordinal].ToString();
result.user_type = usrtype;
//**********************************************
result.information.addr = item.ItemArray[tabledata.Columns[Teacher.FieldName.ADDR].Ordinal].ToString();
result.information.citizen_id = item.ItemArray[tabledata.Columns[Teacher.FieldName.CITIZEN_ID].Ordinal].ToString();
result.information.email = item.ItemArray[tabledata.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString();
result.information.tel = item.ItemArray[tabledata.Columns[Teacher.FieldName.TEL].Ordinal].ToString();
result.information.gender = item.ItemArray[tabledata.Columns[Teacher.FieldName.GENDER].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.GENDER].Ordinal]) : ' ';
result.information.file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[tabledata.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString());
result.information.timestamp = item.ItemArray[tabledata.Columns[Teacher.FieldName.TIMESTAMP].Ordinal].ToString();
result.information.e_name = item.ItemArray[tabledata.Columns[Teacher.FieldName.E_NAME].Ordinal].ToString();
result.information.e_prename = item.ItemArray[tabledata.Columns[Teacher.FieldName.E_PRENAME].Ordinal].ToString();
result.information.t_name = item.ItemArray[tabledata.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString();
result.information.t_prename = item.ItemArray[tabledata.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString();
result.information.SetPassword(item.ItemArray[tabledata.Columns[Teacher.FieldName.PASSWORD].Ordinal].ToString());
if (usrtype == "อาจารย์")
{
result.information.degree = item.ItemArray[tabledata.Columns[Teacher.FieldName.DEGREE].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.DEGREE].Ordinal]) : ' ';
result.information.position = item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal]) : ' ';
result.information.personnel_type = item.ItemArray[tabledata.Columns[Teacher.FieldName.PERSONNEL_TYPE].Ordinal].ToString();
result.information.person_id = item.ItemArray[tabledata.Columns[Teacher.FieldName.PERSON_ID].Ordinal].ToString();
result.information.room = item.ItemArray[tabledata.Columns[Teacher.FieldName.ROOM].Ordinal].ToString();
result.information.status = item.ItemArray[tabledata.Columns[Teacher.FieldName.STATUS].Ordinal].ToString();
result.information.alive = item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[tabledata.Columns[Teacher.FieldName.ALIVE].Ordinal]) : -1;
}
else if (usrtype == "เจ้าหน้าที่")
{
result.information.room = item.ItemArray[tabledata.Columns[Staff.FieldName.ROOM].Ordinal].ToString();
}
else if (usrtype == "บริษัท")
{
result.information.company_name = item.ItemArray[tabledata.Columns[Company.FieldName.COMPANY_NAME].Ordinal].ToString();
}
else if (usrtype == "ผู้ประเมินจากภายนอก")
{
}
else if (usrtype == "นักศึกษา")
{
}
else if (usrtype == "ศิษย์เก่า")
{
}
}
else if (tabledata.Columns.Contains(Educational_teacher_staff.FieldName.COLLEGE))
{
//2 retrieve education data(all user type except student)
result.information.education.Add(new Educational_teacher_staff
{
college = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.COLLEGE].Ordinal].ToString(),
degree = Convert.ToChar(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.DEGREE].Ordinal].ToString()),
grad_year = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal]) : 0,
major = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.MAJOR].Ordinal].ToString(),
personnel_id = result.user_id,
education_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.EDUCATION_ID].Ordinal]),
pre_major = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.PRE_MAJOR].Ordinal].ToString()
});
}
else if (tabledata.Columns.Contains("user_curri_id"))
{
//3 retrieve user_curri_id which user is in
result.curri_id_in.Add(item.ItemArray[tabledata.Columns["user_curri_id"].Ordinal].ToString());
}
else if (tabledata.Columns.Contains("pres_curri_id"))
{
//4 retrieve president_in (pres_curri_id, aca_year) when user is? (teacher only)
if (result.president_in == null)
result.president_in = new Dictionary<string, List<int>>();
string curri_id = item.ItemArray[tabledata.Columns["pres_curri_id"].Ordinal].ToString();
if (!result.president_in.ContainsKey(curri_id))
{
result.president_in.Add(curri_id, new List<int>());
}
result.president_in[curri_id].Add(Convert.ToInt32(item.ItemArray[tabledata.Columns[President_curriculum.FieldName.ACA_YEAR].Ordinal]));
}
else if (tabledata.Columns.Contains("comm_curri_id"))
{
//5 retrieve committee_in (comm_curri_id, aca_year) when user is? (teacher only)
if (result.committee_in == null)
result.committee_in = new Dictionary<string, List<int>>();
string curri_id = item.ItemArray[tabledata.Columns["comm_curri_id"].Ordinal].ToString();
if (!result.committee_in.ContainsKey(curri_id))
{
result.committee_in.Add(curri_id, new List<int>());
}
result.committee_in[curri_id].Add(Convert.ToInt32(item.ItemArray[tabledata.Columns[Committee.FieldName.ACA_YEAR].Ordinal]));
}
else if (tabledata.Columns.Contains(Technical_interested.FieldName.TOPIC_INTERESTED))
{
//6 retrieve topic_interested (teacher only)
result.information.interest.Add(item.ItemArray[tabledata.Columns[Technical_interested.FieldName.TOPIC_INTERESTED].Ordinal].ToString());
}
else if (tabledata.Columns.Contains("evid_curri_id"))
{
//7 retrieve not_send_primary (teacher only ? evid_curri_id,curr_tname, aca_year, evidence_name)
if (result.not_send_primary == null)
result.not_send_primary = new List<Evidence_brief_detail>();
result.not_send_primary.Add(new Evidence_brief_detail
{
curri_id = item.ItemArray[tabledata.Columns["evid_curri_id"].Ordinal].ToString(),
curr_tname = item.ItemArray[tabledata.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString(),
aca_year = Convert.ToInt32(item.ItemArray[tabledata.Columns[Primary_evidence.FieldName.ACA_YEAR].Ordinal]),
evidence_name = item.ItemArray[tabledata.Columns[Primary_evidence.FieldName.EVIDENCE_NAME].Ordinal].ToString()
});
}
else if (tabledata.Columns.Contains(Extra_privilege.FieldName.TITLE_CODE))
{
//8 retrieve privilege (use predefined select from temp table cmd)
string curri_id = item.ItemArray[tabledata.Columns[User_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
if (Convert.ToInt32(item.ItemArray[tabledata.Columns["privilege_type"].Ordinal]) == 1)
{
//Add normal privilege
if (!result.privilege.ContainsKey(curri_id))
{
result.privilege.Add(curri_id, new Dictionary<int, int>());
}
result.privilege[curri_id][Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_CODE].Ordinal])] = Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]);
}
else
{
//Add committee privilege
if (result.committee_privilege == null)
result.committee_privilege = new Dictionary<string, Dictionary<int, int>>();
if (!result.committee_privilege.ContainsKey(curri_id))
{
result.committee_privilege.Add(curri_id, new Dictionary<int, int>());
}
result.committee_privilege[curri_id][Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_CODE].Ordinal])] = Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]);
}
}
else if (tabledata.Columns.Contains("file_name_pic_del"))
{
//get file name pic to delete
file_name_pic = item.ItemArray[tabledata.Columns["file_name_pic_del"].Ordinal].ToString();
}
}
tabledata.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
break;
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return ex.Message;
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return result;
}
public async Task<object> selectUserData(int usrid)
{
DBConnector d = new DBConnector();
User_information_with_privilege_information result = new User_information_with_privilege_information();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
d.iCommand.CommandText = string.Format("if exists (select * from {0} where {1} = {2}) " +
"{3} " +
"else if exists (select * from {4} where {5} = {2}) " +
"{6} " +
"else if exists (select * from {7} where {8} = {2}) " +
"{9} " +
"else if exists (select * from {10} where {11} = {2}) " +
"{12} " +
"else if exists (select * from {13} where {14} = {2}) " +
"{15} " +
"else if exists (select * from {16} where {17} = {2}) " +
"{18} " +
"else if exists (select * from {19} where {20} = {2}) " +
"{21} " +
"else if exists (select * from {22} where {23} = {2}) " +
"{24} ",
Teacher.FieldName.TABLE_NAME, Teacher.FieldName.TEACHER_ID, usrid,
getSelectUserDataCommand(usrid.ToString(), 0,"#temp99"), Staff.FieldName.TABLE_NAME, Staff.FieldName.STAFF_ID,
getSelectUserDataCommand(usrid.ToString(), 1,"#temp98"), Student.FieldName.TABLE_NAME, Student.FieldName.USER_ID,
getSelectUserDataCommand(usrid.ToString(), 2,"#temp97"), Alumni.ExtraFieldName.TABLE_NAME, Alumni.FieldName.USER_ID,
getSelectUserDataCommand(usrid.ToString(), 3,"#temp96"), Company.FieldName.TABLE_NAME, Company.FieldName.COMPANY_ID,
getSelectUserDataCommand(usrid.ToString(), 4,"#temp95"), Assessor.FieldName.TABLE_NAME, Assessor.FieldName.ASSESSOR_ID,
getSelectUserDataCommand(usrid.ToString(), 5,"#temp94"), Admin.FieldName.TABLE_NAME, Admin.FieldName.ADMIN_ID,
getSelectUserDataCommand(usrid.ToString(), 6,"#temp93"), User_list.FieldName.TABLE_NAME,User_list.FieldName.USER_ID,
getSelectUserDataCommand(usrid.ToString(), 7, "#temp92"));
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable tabledata = new DataTable();
tabledata.Load(res);
foreach (DataRow item in tabledata.Rows)
{
if (tabledata.Columns.Contains(Teacher.FieldName.T_PRENAME))
{
//1 retrieve user_data from pre-defined select table command
string usrtype = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString();
if (usrtype == "อาจารย์")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]);
else if (usrtype == "เจ้าหน้าที่")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Staff.FieldName.STAFF_ID].Ordinal]);
else if (usrtype == "นักศึกษา" || usrtype == "ศิษย์เก่า")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Student.FieldName.USER_ID].Ordinal]);
else if (usrtype == "บริษัท")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Company.FieldName.COMPANY_ID].Ordinal]);
else if (usrtype == "ผู้ประเมินจากภายนอก")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Assessor.FieldName.ASSESSOR_ID].Ordinal]);
else if (usrtype == "ผู้ดูแลระบบ")
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Admin.FieldName.ADMIN_ID].Ordinal]);
else
result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[User_list.FieldName.USER_ID].Ordinal]);
result.username = item.ItemArray[tabledata.Columns[Teacher.FieldName.USERNAME].Ordinal].ToString();
result.user_type = usrtype;
//**********************************************
result.information.addr = item.ItemArray[tabledata.Columns[Teacher.FieldName.ADDR].Ordinal].ToString();
result.information.citizen_id = item.ItemArray[tabledata.Columns[Teacher.FieldName.CITIZEN_ID].Ordinal].ToString();
result.information.email = item.ItemArray[tabledata.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString();
result.information.tel = item.ItemArray[tabledata.Columns[Teacher.FieldName.TEL].Ordinal].ToString();
result.information.gender = item.ItemArray[tabledata.Columns[Teacher.FieldName.GENDER].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.GENDER].Ordinal]) : ' ';
result.information.file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[tabledata.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString());
result.information.timestamp = item.ItemArray[tabledata.Columns[Teacher.FieldName.TIMESTAMP].Ordinal].ToString();
result.information.e_name = item.ItemArray[tabledata.Columns[Teacher.FieldName.E_NAME].Ordinal].ToString();
result.information.e_prename = item.ItemArray[tabledata.Columns[Teacher.FieldName.E_PRENAME].Ordinal].ToString();
result.information.t_name = item.ItemArray[tabledata.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString();
result.information.t_prename = item.ItemArray[tabledata.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString();
result.information.SetPassword(item.ItemArray[tabledata.Columns[Teacher.FieldName.PASSWORD].Ordinal].ToString());
if (usrtype == "อาจารย์")
{
result.information.degree = item.ItemArray[tabledata.Columns[Teacher.FieldName.DEGREE].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.DEGREE].Ordinal]) : ' ';
result.information.position = item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal]) : ' ';
result.information.personnel_type = item.ItemArray[tabledata.Columns[Teacher.FieldName.PERSONNEL_TYPE].Ordinal].ToString();
result.information.person_id = item.ItemArray[tabledata.Columns[Teacher.FieldName.PERSON_ID].Ordinal].ToString();
result.information.room = item.ItemArray[tabledata.Columns[Teacher.FieldName.ROOM].Ordinal].ToString();
result.information.status = item.ItemArray[tabledata.Columns[Teacher.FieldName.STATUS].Ordinal].ToString();
result.information.alive = item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[tabledata.Columns[Teacher.FieldName.ALIVE].Ordinal]) : -1;
}
else if (usrtype == "เจ้าหน้าที่")
{
result.information.room = item.ItemArray[tabledata.Columns[Staff.FieldName.ROOM].Ordinal].ToString();
}
else if (usrtype == "บริษัท")
{
result.information.company_name = item.ItemArray[tabledata.Columns[Company.FieldName.COMPANY_NAME].Ordinal].ToString();
}
else if (usrtype == "ผู้ประเมินจากภายนอก")
{
}
else if (usrtype == "นักศึกษา")
{
}
else if (usrtype == "ศิษย์เก่า")
{
}
}
else if (tabledata.Columns.Contains(Educational_teacher_staff.FieldName.COLLEGE))
{
//2 retrieve education data(all user type except student)
result.information.education.Add(new Educational_teacher_staff
{
college = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.COLLEGE].Ordinal].ToString(),
degree = Convert.ToChar(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.DEGREE].Ordinal].ToString()),
grad_year = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal]) : 0,
major = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.MAJOR].Ordinal].ToString(),
personnel_id = result.user_id,
education_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.EDUCATION_ID].Ordinal]),
pre_major = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.PRE_MAJOR].Ordinal].ToString()
});
}
else if (tabledata.Columns.Contains("user_curri_id"))
{
//3 retrieve user_curri_id which user is in
result.curri_id_in.Add(item.ItemArray[tabledata.Columns["user_curri_id"].Ordinal].ToString());
}
else if (tabledata.Columns.Contains("pres_curri_id"))
{
//4 retrieve president_in (pres_curri_id, aca_year) when user is? (teacher only)
if (result.president_in == null)
result.president_in = new Dictionary<string, List<int>>();
string curri_id = item.ItemArray[tabledata.Columns["pres_curri_id"].Ordinal].ToString();
if (!result.president_in.ContainsKey(curri_id))
{
result.president_in.Add(curri_id, new List<int>());
}
result.president_in[curri_id].Add(Convert.ToInt32(item.ItemArray[tabledata.Columns[President_curriculum.FieldName.ACA_YEAR].Ordinal]));
}
else if (tabledata.Columns.Contains("comm_curri_id"))
{
//5 retrieve committee_in (comm_curri_id, aca_year) when user is? (teacher only)
if (result.committee_in == null)
result.committee_in = new Dictionary<string, List<int>>();
string curri_id = item.ItemArray[tabledata.Columns["comm_curri_id"].Ordinal].ToString();
if (!result.committee_in.ContainsKey(curri_id))
{
result.committee_in.Add(curri_id, new List<int>());
}
result.committee_in[curri_id].Add(Convert.ToInt32(item.ItemArray[tabledata.Columns[Committee.FieldName.ACA_YEAR].Ordinal]));
}
else if (tabledata.Columns.Contains(Technical_interested.FieldName.TOPIC_INTERESTED))
{
//6 retrieve topic_interested (teacher only)
result.information.interest.Add(item.ItemArray[tabledata.Columns[Technical_interested.FieldName.TOPIC_INTERESTED].Ordinal].ToString());
}
else if (tabledata.Columns.Contains("evid_curri_id"))
{
//7 retrieve not_send_primary (teacher only ? evid_curri_id,curr_tname, aca_year, evidence_name)
if (result.not_send_primary == null)
result.not_send_primary = new List<Evidence_brief_detail>();
result.not_send_primary.Add(new Evidence_brief_detail
{
curri_id = item.ItemArray[tabledata.Columns["evid_curri_id"].Ordinal].ToString(),
curr_tname = item.ItemArray[tabledata.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString(),
aca_year = Convert.ToInt32(item.ItemArray[tabledata.Columns[Primary_evidence.FieldName.ACA_YEAR].Ordinal]),
evidence_name = item.ItemArray[tabledata.Columns[Primary_evidence.FieldName.EVIDENCE_NAME].Ordinal].ToString()
});
}
else if (tabledata.Columns.Contains(Extra_privilege.FieldName.TITLE_CODE))
{
//8 retrieve privilege (use predefined select from temp table cmd)
string curri_id = item.ItemArray[tabledata.Columns[User_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
if (Convert.ToInt32(item.ItemArray[tabledata.Columns["privilege_type"].Ordinal]) == 1)
{
//Add normal privilege
if (!result.privilege.ContainsKey(curri_id))
{
result.privilege.Add(curri_id, new Dictionary<int, int>());
}
result.privilege[curri_id][Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_CODE].Ordinal])] = Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]);
}
else
{
//Add committee privilege
if (result.committee_privilege == null)
result.committee_privilege = new Dictionary<string, Dictionary<int, int>>();
if (!result.committee_privilege.ContainsKey(curri_id))
{
result.committee_privilege.Add(curri_id, new Dictionary<int, int>());
}
result.committee_privilege[curri_id][Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_CODE].Ordinal])] = Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]);
}
}
}
tabledata.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
break;
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return ex.Message;
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return result;
}
public async Task <IHttpActionResult> PutForUpdateUserData()
{
if (!Request.Content.IsMimeMultipartContent())
{
return(new System.Web.Http.Results.StatusCodeResult(HttpStatusCode.UnsupportedMediaType, Request));
}
string savepath = WebApiApplication.SERVERPATH + "myImages/profile_pic";
var result = new MultipartFormDataStreamProvider(savepath);
try
{
await Request.Content.ReadAsMultipartAsync(result);
oUsers datacontext = new oUsers();
//READ JSON DATA PART
JObject datareceive = JObject.Parse(result.FormData.GetValues(result.FormData.AllKeys[0])[0]);
User_information_with_privilege_information userdata = new User_information_with_privilege_information();
//Prerequisite
userdata.user_id = Convert.ToInt32(datareceive["user_id"]);
//username ignored
//citizen_id ignored
//gender ignored
//timestamp ignored
//teacher section => degree ignored
//teacher section => position ignored
//teacher section => personnel_type ignored
//teacher section => person_id ignored
//teacher,staff section => room ignored
//teacher section => alive ignored
userdata.user_type = datareceive["user_type"].ToString();
//list of update value
userdata.information.t_prename = datareceive["information"]["t_prename"].ToString();
userdata.information.t_name = datareceive["information"]["t_name"].ToString();
userdata.information.e_prename = datareceive["information"]["e_prename"].ToString();
userdata.information.e_name = datareceive["information"]["e_name"].ToString();
userdata.information.email = datareceive["information"]["email"].ToString();
userdata.information.tel = datareceive["information"]["tel"].ToString();
userdata.information.addr = datareceive["information"]["addr"].ToString();
if (userdata.user_type == "อาจารย์")
{
//teacher have status
userdata.information.status = datareceive["information"]["status"].ToString();
//teacher have interest
if (datareceive["information"]["interest"] != null)
{
JArray interestarr = (JArray)datareceive["information"]["interest"];
foreach (JValue value in interestarr)
{
userdata.information.interest.Add(value.ToString());
}
}
}
if (userdata.user_type != "นักศึกษา")
{
if (datareceive["information"]["education"] != null)
{
JArray educationarr = (JArray)datareceive["information"]["education"];
foreach (JObject eduitem in educationarr)
{
userdata.information.education.Add(new Models.Educational_teacher_staff {
education_id = Convert.ToInt32(eduitem["education_id"])
});
}
}
}
//filenamepic will add later
if (result.FileData.Count > 0)
{
MultipartFileData file = result.FileData[0];
FileInfo fileInfo = new FileInfo(file.LocalFileName);
if (!file.Headers.ContentType.ToString().Contains("image/"))
{
//Delete temp upload file
if (File.Exists(string.Format("{0}/{1}", savepath, fileInfo.Name)))
{
File.Delete(string.Format("{0}/{1}", savepath, fileInfo.Name));
}
return(BadRequest("ไฟล์รูปภาพที่ท่านอัพโหลดไมใช่ไฟล์รูปภาพที่ถูกต้อง"));
}
string newfilename = string.Format("{0}.{1}", fileInfo.Name.Substring(9), file.Headers.ContentDisposition.FileName.Split('.').LastOrDefault().Split('\"').FirstOrDefault());
userdata.information.file_name_pic = "myImages/profile_pic/" + newfilename;
File.Move(string.Format("{0}/{1}", savepath, fileInfo.Name), string.Format("{0}/{1}", savepath, newfilename));
}
else
{
//file_name_pic set to null => no change!
userdata.information.file_name_pic = null;
}
object resultfromdb = await datacontext.UpdateUserData(userdata);
if (resultfromdb.GetType().ToString() != "System.String")
{
//delete filename will inside file_name property of oUser object
string delpath = WebApiApplication.SERVERPATH;
if (datacontext.file_name_pic != null)
{
//Check whether file exists!
if (File.Exists(string.Format("{0}{1}", delpath, datacontext.file_name_pic)))
{
File.Delete(string.Format("{0}{1}", delpath, datacontext.file_name_pic));
}
}
return(Ok(resultfromdb));
}
else
{
return(InternalServerError(new Exception(resultfromdb.ToString())));
}
}
catch (Exception e)
{
return(InternalServerError(e));
}
}
