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