public async Task<object> SelectMaxAcademicYear()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oCurriculum_academic> result = new List<oCurriculum_academic>();
d.iCommand.CommandText = string.Format("select MAX({1})+1 from {0}", FieldName.TABLE_NAME,FieldName.ACA_YEAR);
try
{
object res = await d.iCommand.ExecuteScalarAsync();
if (res != null)
{
return res;
}
else
{
//Reserved for return error string
return null;
}
}
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();
}
}
public async Task<object> SelectWhere()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2} and {3} = {4} and {5} = {6} and {7} = {8}", FieldName.TABLE_NAME,
FieldName.INDICATOR_NUM,ParameterName.INDICATOR_NUM,FieldName.SUB_INDICATOR_NUM,ParameterName.SUB_INDICATOR_NUM,
FieldName.ACA_YEAR,ParameterName.ACA_YEAR,FieldName.CURRI_ID,ParameterName.CURRI_ID);
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.INDICATOR_NUM, indicator_num));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.SUB_INDICATOR_NUM, sub_indicator_num));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id));
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
string h, m;
DateTime timeofday = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.TIME].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture);
h = timeofday.Hour.ToString();
m = timeofday.Minute.ToString();
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString();
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]);
detail = item.ItemArray[data.Columns[FieldName.DETAIL].Ordinal].ToString();
strength = item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString() != "" ? item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString() : null;
weakness = item.ItemArray[data.Columns[FieldName.WEAKNESS].Ordinal].ToString() != "" ? item.ItemArray[data.Columns[FieldName.WEAKNESS].Ordinal].ToString() : null;
improve = item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString() != "" ? item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString() : null;
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]);
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]);
teacher_id = item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]) : 0;
date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3];
time = (timeofday.Hour > 9 ? "" : "0") + h + '.' + (timeofday.Minute > 9 ? "" : "0") + m;
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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 null;
}
public async Task<object> SelectWithDetailByCurriculum(string curri_id_data)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Research_detail> result = new List<Research_detail>();
curri_id = curri_id_data;
d.iCommand.CommandText = getSelectByCurriculumCommand();
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
int rid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]);
if (result.FirstOrDefault(r => r.research_id == rid) == null)
result.Add(new Research_detail
{
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(),
research_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]),
year_publish = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR_PUBLISH].Ordinal])
});
result.First(r => r.research_id == rid).researcher.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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> SelectWithBriefDetail()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Committee_with_detail> result = new List<Committee_with_detail>();
d.iCommand.CommandText = string.Format("select {0}.*,{1},{2},{3},{12} from {0},({4}) as {13} where {5} = '{6}' and {7} = {8} and {0}.{9} = {13}.{10} order by {11}",
FieldName.TABLE_NAME,Teacher.FieldName.T_PRENAME,Teacher.FieldName.T_NAME,Teacher.FieldName.FILE_NAME_PIC,
oTeacher.getSelectTeacherByJoinCommand(),FieldName.CURRI_ID,curri_id,FieldName.ACA_YEAR,aca_year,FieldName.TEACHER_ID,Teacher.FieldName.TEACHER_ID,FieldName.DATE_PROMOTED,Teacher.FieldName.EMAIL,
Teacher.FieldName.ALIAS_NAME);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new Committee_with_detail
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
date_promoted = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_PROMOTED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) +
item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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 object Select()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oNew_student_count> result = new List<oNew_student_count>();
d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME);
try
{
System.Data.Common.DbDataReader res = d.iCommand.ExecuteReader();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oNew_student_count
{
num_admis_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_ADMIS_F].Ordinal]),
num_admis_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_ADMIS_M].Ordinal]),
num_childstaff_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_CHILDSTAFF_F].Ordinal]),
num_childstaff_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_CHILDSTAFF_M].Ordinal]),
num_direct_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_DIRECT_F].Ordinal]),
num_direct_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_DIRECT_M].Ordinal]),
num_goodstudy_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_GOODSTUDY_F].Ordinal]),
num_goodstudy_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_GOODSTUDY_M].Ordinal]),
num_others_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_OTHERS_F].Ordinal]),
num_others_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_OTHERS_M].Ordinal]),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR].Ordinal])
});
}
res.Close();
data.Dispose();
}
else
{
//Reserved for return error string
}
}
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 object SelectWhere(string wherecond)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oEvidence> result = new List<oEvidence>();
d.iCommand.CommandText = string.Format("select * from {0} where {1}", FieldName.TABLE_NAME, wherecond);
try
{
System.Data.Common.DbDataReader res = d.iCommand.ExecuteReader();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oEvidence
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
evidence_code = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVIDENCE_CODE].Ordinal]),
evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(),
file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(),
secret = Convert.ToChar(item.ItemArray[data.Columns[FieldName.SECRET].Ordinal]),
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]),
//DANGER NULLABLE ZONE
primary_evidence_num = item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]) : 0,
evidence_real_code = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVIDENCE_REAL_CODE].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal])
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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> Select()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oCu_curriculum> result = new List<oCu_curriculum>();
d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oCu_curriculum
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
curr_tname = item.ItemArray[data.Columns[FieldName.CURR_TNAME].Ordinal].ToString(),
curr_ename = item.ItemArray[data.Columns[FieldName.CURR_ENAME].Ordinal].ToString(),
degree_e_bf = item.ItemArray[data.Columns[FieldName.DEGREE_E_BF].Ordinal].ToString(),
degree_e_full = item.ItemArray[data.Columns[FieldName.DEGREE_E_FULL].Ordinal].ToString(),
degree_t_bf = item.ItemArray[data.Columns[FieldName.DEGREE_T_BF].Ordinal].ToString(),
degree_t_full = item.ItemArray[data.Columns[FieldName.DEGREE_T_FULL].Ordinal].ToString(),
level = Convert.ToChar(item.ItemArray[data.Columns[FieldName.LEVEL].Ordinal]),
period = Convert.ToChar(item.ItemArray[data.Columns[FieldName.PERIOD].Ordinal]),
year = item.ItemArray[data.Columns[FieldName.YEAR].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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 object Select()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oStudent_status_other> result = new List<oStudent_status_other>();
d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME);
try
{
System.Data.Common.DbDataReader res = d.iCommand.ExecuteReader();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oStudent_status_other
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
grad_in_time = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GRAD_IN_TIME].Ordinal]),
grad_over_time = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GRAD_OVER_TIME].Ordinal]),
move_in = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MOVE_IN].Ordinal]),
quity1 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUITY1].Ordinal]),
quity2 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUITY2].Ordinal]),
quity3 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUITY3].Ordinal]),
quity4 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUITY4].Ordinal]),
year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR].Ordinal])
});
}
res.Close();
data.Dispose();
}
else
{
//Reserved for return error string
}
}
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> InsertOrUpdate(List<oSelf_evaluation> list)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
string insertcmd = string.Format("insert into {0} values ",FieldName.TABLE_NAME);
//Loop each item to gather data to construct sql cmd
string updatecmd = "";
foreach(oSelf_evaluation item in list)
{
insertcmd += string.Format("({0},{1},{2},{3},'{4}','{5}','{6}',{7})",
indicator_num, item.sub_indicator_num, item.teacher_id, item.evaluation_score > 0 ? "'" + item.evaluation_score.ToString() + "'" : "null", item.date, item.time, curri_id, aca_year);
if (item != list.Last()) insertcmd += ",";
updatecmd += string.Format("update {0} set {1} = {2},{3} = {4},{5} = '{6}',{7} = '{8}' where {9} = {10} and {11} = {12} and {13} = '{14}' and {15} = {16} ",
FieldName.TABLE_NAME, FieldName.TEACHER_ID, item.teacher_id, FieldName.EVALUATION_SCORE, item.evaluation_score > 0 ? "'" + item.evaluation_score.ToString() + "'" : "null", FieldName.DATE, item.date, FieldName.TIME, item.time,
FieldName.INDICATOR_NUM, item.indicator_num, FieldName.SUB_INDICATOR_NUM, item.sub_indicator_num, FieldName.CURRI_ID, item.curri_id, FieldName.ACA_YEAR, item.aca_year);
}
string selectcmd = string.Format("select * from {0} where {1} = {2} and {3} = '{4}' and {5} = {6}",
FieldName.TABLE_NAME, FieldName.INDICATOR_NUM, indicator_num, FieldName.CURRI_ID, curri_id,
FieldName.ACA_YEAR,aca_year);
d.iCommand.CommandText = string.Format("IF NOT EXISTS ({0}) " +
"BEGIN " +
"{1} " +
"END " +
"ELSE " +
"BEGIN " +
"{2} " +
"END", selectcmd, insertcmd, updatecmd);
try
{
await d.iCommand.ExecuteNonQueryAsync();
return null;
}
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();
}
}
public async Task<object> SelectExcludeUserType(int mode)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oUser_type> result = new List<oUser_type>();
if(mode == 0)
d.iCommand.CommandText = string.Format("select * from {0} where {1} != 'ผู้ดูแลระบบ'", FieldName.TABLE_NAME,FieldName.USER_TYPE_NAME);
else if(mode == 1)
d.iCommand.CommandText = string.Format("select * from {0} where {1} != 'ผู้ดูแลระบบ' and {1} != 'กรรมการหลักสูตร'", FieldName.TABLE_NAME, FieldName.USER_TYPE_NAME);
else
d.iCommand.CommandText = string.Format("select * from {0} ", FieldName.TABLE_NAME);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oUser_type
{
user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.USER_TYPE_ID].Ordinal]),
user_type = item.ItemArray[data.Columns[FieldName.USER_TYPE_NAME].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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> SelectByQuestionIdAsQuestionForm(int qid)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Questionare_question_answer> result = new List<Questionare_question_answer>();
d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2}", FieldName.TABLE_NAME,
FieldName.QUESTIONARE_SET_ID, qid);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new Questionare_question_answer
{
detail = item.ItemArray[data.Columns[FieldName.DETAIL].Ordinal].ToString(),
questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]),
questionare_question_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_QUESTION_ID].Ordinal])
});
}
data.Dispose();
}
else
{
//Reserved for return error string
res.Close();
return "แบบสอบถามนี้ถูกลบแล้ว";
}
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> SelectWhereOrderByWithKeepYearSource(string wherecond, string orderbycol, int? dir,int source_year)
{
string[] direction = { "ASC", "DESC" };
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oIndicator> result = new List<oIndicator>();
d.iCommand.CommandText = string.Format("select * from {0} where {1} order by {2} {3}",
FieldName.TABLE_NAME, wherecond, orderbycol, ((dir != null) ? direction[dir.Value] : ""));
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oIndicator
{
aca_year = source_year,
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
indicator_name_t = item.ItemArray[data.Columns[FieldName.INDICATOR_NAME_T].Ordinal].ToString(),
indicator_name_e = item.ItemArray[data.Columns[FieldName.INDICATOR_NAME_E].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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 object Select()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oAun_book> result = new List<oAun_book>();
d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME);
try
{
System.Data.Common.DbDataReader res = d.iCommand.ExecuteReader();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oAun_book
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(),
personnel_id = item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]) : 0,
date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3]
});
}
res.Close();
data.Dispose();
}
else
{
//Reserved for return error string
}
}
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> SelectTeacherIdAndTName(string curri_id)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Teacher_with_t_name> result = new List<Teacher_with_t_name>();
d.iCommand.CommandText = string.Format("select * from ({0}) as {6} where exists(select * from {1} where {6}.{2} = {1}.{3} and {4}='{5}')",
getSelectTeacherByJoinCommand(), User_curriculum.FieldName.TABLE_NAME,FieldName.TEACHER_ID,User_curriculum.FieldName.USER_ID,User_curriculum.FieldName.CURRI_ID,curri_id,
FieldName.ALIAS_NAME);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[FieldName.T_PRENAME].Ordinal].ToString()) +
item.ItemArray[data.Columns[FieldName.T_NAME].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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 object SelectWhere(string wherecond)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oSub_indicator> result = new List<oSub_indicator>();
d.iCommand.CommandText = string.Format("select * from {0} where {1}",FieldName.TABLE_NAME,wherecond);
try
{
System.Data.Common.DbDataReader res = d.iCommand.ExecuteReader();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oSub_indicator
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]),
sub_indicator_name = item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
}
res.Close();
data.Dispose();
}
else
{
//Reserved for return error string
}
}
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 object Select()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oCurriculum_academic> result = new List<oCurriculum_academic>();
d.iCommand.CommandText = string.Format("select * from {0}",FieldName.TABLE_NAME);
try
{
System.Data.Common.DbDataReader res = d.iCommand.ExecuteReader();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oCurriculum_academic
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal])
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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> Select()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oTitle> result = new List<oTitle>();
d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oTitle
{
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
title_code = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TITLE_CODE].Ordinal])
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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> InsertOrUpdate()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
string deletefromcommittee = string.Format("delete from {0} where {1} = {2} and {3} = {4} and {5} = {6} ",
Committee.FieldName.TABLE_NAME, FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.ACA_YEAR, ParameterName.ACA_YEAR,
FieldName.TEACHER_ID, ParameterName.TEACHER_ID);
d.iCommand.CommandText = deletefromcommittee + string.Format("IF NOT EXISTS (select * from {0} where {1} = {2} and {3} = {4}) " +
"BEGIN " +
"INSERT INTO {0} VALUES " +
"({5}, {2},{4}) " +
"END " +
"ELSE " +
"BEGIN " +
"UPDATE {0} SET {6} = {5} where {1} = {2} and {3} = {4} " +
"END",
FieldName.TABLE_NAME, FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.ACA_YEAR, ParameterName.ACA_YEAR, ParameterName.TEACHER_ID, FieldName.TEACHER_ID);
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.TEACHER_ID, teacher_id));
try
{
await d.iCommand.ExecuteNonQueryAsync();
return null;
}
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();
}
}
public async Task<object> InsertNewGalleryWithSelect(Gallery_detail gdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Gallery_detail> result = new List<Gallery_detail>();
string temp1tablename = "#temp1";
string temp2tablename = "#temp2";
string createtabletemp1 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NOT NULL," +
"PRIMARY KEY ([row_num])) ", temp1tablename, FieldName.GALLERY_ID);
string createtabletemp2 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] {3} NULL," +
"[{2}] VARCHAR(MAX) NULL," +
"PRIMARY KEY ([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} {3} COLLATE DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN {2} VARCHAR(MAX) COLLATE DATABASE_DEFAULT "
, temp2tablename, Picture.FieldName.FILE_NAME,Picture.FieldName.CAPTION,
DBFieldDataType.FILE_NAME_TYPE);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(insert into {1}({2},{3},{4},{5},{6}) output inserted.{7} values " +
"('{8}','{9}','{10}','{11}',{12})) " +
"as outputinsert ",
temp1tablename, FieldName.TABLE_NAME, FieldName.PERSONNEL_ID, FieldName.NAME, FieldName.DATE_CREATED,
FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.GALLERY_ID,
gdata.personnel_id, gdata.name, gdata.date_created, gdata.curri_id, gdata.aca_year);
string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null,null)", temp2tablename);
foreach (Picture p in gdata.pictures)
{
insertintotemp2 += string.Format(",('{0}','{1}')", p.file_name,p.caption);
}
string insertintopicture = string.Format(" INSERT INTO {0} " +
"select {1},{2},{3} from {4},{5} where {2} is not null ",
Picture.FieldName.TABLE_NAME, FieldName.GALLERY_ID, Picture.FieldName.FILE_NAME,
Picture.FieldName.CAPTION,
temp1tablename, temp2tablename);
curri_id = gdata.curri_id;
aca_year = gdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand(false);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2,
insertintotemp1, insertintotemp2,
insertintopicture, selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]);
if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null)
{
string real_t_prename;
if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์")
real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString());
else
real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString();
result.Add(new Gallery_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]),
t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]),
date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
pictures = new List<Picture>()
});
}
result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]),
file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(),
caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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> Delete(List<Gallery_detail> list)
{
DBConnector d = new DBConnector();
List<string> file_to_delete = new List<string>();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
string deleteprecmd = string.Format("DELETE FROM {0} WHERE {1} = '{2}' and {3} = {4} ",
FieldName.TABLE_NAME, FieldName.CURRI_ID, list.First().curri_id, FieldName.ACA_YEAR, list.First().aca_year);
string excludecond = "1=1 ";
foreach (Gallery_detail item in list)
{
excludecond += string.Format("and {0} != {1} ", FieldName.GALLERY_ID, item.gallery_id);
}
string deletefullcmd = string.Format("{0} and ({1})", deleteprecmd, excludecond);
string temp1tablename = "#temp1";
string createtabletemp1 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] {2} NOT NULL," +
"PRIMARY KEY([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT ",
temp1tablename, Picture.FieldName.FILE_NAME,DBFieldDataType.FILE_NAME_TYPE);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"SELECT {1} from {2} where ({3}) " +
"and {4} in " +
"(select {5} from {6} where {7} = '{8}' and {9} = {10}) ",
temp1tablename, Picture.FieldName.FILE_NAME, Picture.FieldName.TABLE_NAME,
excludecond, Picture.FieldName.GALLERY_ID, FieldName.GALLERY_ID,
FieldName.TABLE_NAME, FieldName.CURRI_ID, list.First().curri_id,
FieldName.ACA_YEAR, list.First().aca_year);
string selcmd = string.Format("select {0} from {1} ", Picture.FieldName.FILE_NAME, temp1tablename);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} END", createtabletemp1, insertintotemp1,
deletefullcmd,selcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
file_to_delete.Add(
item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString()
);
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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 file_to_delete;
}
public async Task<object> Delete(List<Research_detail> list)
{
DBConnector d = new DBConnector();
List<string> file_to_delete = new List<string>();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
string deleteprecmd = string.Format("DELETE FROM {0} OUTPUT DELETED.{3} WHERE {1} = '{2}'",
FieldName.TABLE_NAME, FieldName.CURRI_ID, list.First().curri_id, FieldName.FILE_NAME);
string excludecond = "1=1 ";
foreach (Research_detail item in list)
{
excludecond += string.Format("and {0} != {1} ", FieldName.RESEARCH_ID, item.research_id);
}
d.iCommand.CommandText = string.Format("{0} and ({1})", deleteprecmd, excludecond);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
file_to_delete.Add(
item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString()
);
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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 file_to_delete;
}
public async Task<object> UpdateResearchWithSelect(Research_detail rdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Research_detail> result = new List<Research_detail>();
string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME,
FieldName.RESEARCH_ID, rdata.research_id);
string temp1tablename = "#temp1";
string createtabletemp1 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] {2} NOT NULL," +
"PRIMARY KEY([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT ",
temp1tablename, FieldName.FILE_NAME,DBFieldDataType.FILE_NAME_TYPE);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(update {1} set {2} = '{3}', {4} = '{5}', {6} = {7} " +
"OUTPUT deleted.{4} where {8} = {9}) as outputupdate ",
temp1tablename, FieldName.TABLE_NAME, FieldName.NAME, rdata.name,
FieldName.FILE_NAME, rdata.file_name, FieldName.YEAR_PUBLISH, rdata.year_publish,
FieldName.RESEARCH_ID, rdata.research_id);
string deletefromresearchowner = string.Format("DELETE FROM {0} where {1} = {2} ",
Research_owner.FieldName.TABLE_NAME, Research_owner.FieldName.RESEARCH_ID, rdata.research_id);
string insertintoresearchowner = string.Format("INSERT INTO {0} values ",
Research_owner.FieldName.TABLE_NAME);
foreach (Teacher_with_t_name t in rdata.researcher)
{
insertintoresearchowner += string.Format("({0},{1})", rdata.research_id, t.teacher_id);
if (t != rdata.researcher.Last())
insertintoresearchowner += ",";
}
string selectcmd = string.Format("select temp1out.{6},r.*,{15}.{1},{15}.{2} from " +
"(select {3}.{4},{3}.{5},{3}.{6}," +
"{3}.{7}, {3}.{8},{9} from {3}, {10} where " +
"{5} = '{11}' and {3}.{4} = {10}.{12}) as r,(select {6} from {14}) as temp1out,({0}) as {15} where r.{9} = {15}.{13} order by {7} ",
oTeacher.getSelectTeacherByJoinCommand(), Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
FieldName.TABLE_NAME, FieldName.RESEARCH_ID, FieldName.CURRI_ID, FieldName.FILE_NAME,
FieldName.NAME, FieldName.YEAR_PUBLISH, Research_owner.FieldName.TEACHER_ID,
Research_owner.FieldName.TABLE_NAME, rdata.curri_id, Research_owner.FieldName.RESEARCH_ID,
Teacher.FieldName.TEACHER_ID,temp1tablename,Teacher.FieldName.ALIAS_NAME);
d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} END",ifexistscond, createtabletemp1,
insertintotemp1, deletefromresearchowner, insertintoresearchowner, selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
//get to-be delete file_name set in file_name property of main object for future use
file_name = data.Rows[0].ItemArray[0].ToString();
foreach (DataRow item in data.Rows)
{
int rid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]);
if (result.FirstOrDefault(r => r.research_id == rid) == null)
result.Add(new Research_detail
{
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
file_name = item.ItemArray[3].ToString(),
research_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]),
year_publish = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR_PUBLISH].Ordinal])
});
result.First(r => r.research_id == rid).researcher.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
res.Close();
return "ไม่พบข้อมูลงานวิจัยที่ต้องการแก้ไขในระบบ";
}
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> InsertNewResearchWithSelect(Research_detail rdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Research_detail> result = new List<Research_detail>();
string temp1tablename = "#temp1";
string temp2tablename = "#temp2";
string createtabletemp1 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NOT NULL," +
"PRIMARY KEY ([row_num])) ", temp1tablename, FieldName.RESEARCH_ID);
string createtabletemp2 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) "
, temp2tablename, Research_owner.FieldName.TEACHER_ID);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(insert into {1} output inserted.{2} values " +
"('{3}', '{4}', {5}, '{6}')) as outputinsert ",
temp1tablename,FieldName.TABLE_NAME,FieldName.RESEARCH_ID,
rdata.curri_id,rdata.name,rdata.year_publish,rdata.file_name);
string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename);
foreach(Teacher_with_t_name item in rdata.researcher)
insertintotemp2 += string.Format(",({0})", item.teacher_id);
string insertintoresowner = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Research_owner.FieldName.TABLE_NAME, FieldName.RESEARCH_ID, Research_owner.FieldName.TEACHER_ID,
temp1tablename, temp2tablename);
curri_id = rdata.curri_id;
string selectcmd = getSelectByCurriculumCommand();
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2,
insertintotemp1, insertintotemp2, insertintoresowner, selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
int rid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]);
if (result.FirstOrDefault(r => r.research_id == rid) == null)
result.Add(new Research_detail
{
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(),
research_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]),
year_publish = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR_PUBLISH].Ordinal])
});
result.First(r => r.research_id == rid).researcher.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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> 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<object> UpdateGalleryWithSelect(Gallery_detail gdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Gallery_detail> result = new List<Gallery_detail>();
Gallery_detail dummyfordeletepictures = new Gallery_detail();
string temp1tablename = "#temp1";
string updatepicturecmd = "";
string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME,
FieldName.GALLERY_ID, gdata.gallery_id);
string insertintopicturecmd = string.Format("insert into {0} values ", Picture.FieldName.TABLE_NAME);
string deletefrompicturecmd = string.Format("delete from {0} output deleted.{1} where {2} = {3} ",
Picture.FieldName.TABLE_NAME, Picture.FieldName.FILE_NAME, Picture.FieldName.GALLERY_ID,
gdata.gallery_id);
string excludecond = "1=1 ";
int len = insertintopicturecmd.Length;
string createtabletemp1 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] {2} NULL," +
"PRIMARY KEY ([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT "
, temp1tablename, Picture.FieldName.FILE_NAME,
DBFieldDataType.FILE_NAME_TYPE);
string updategallerycmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}' where {5} = {6} ",
FieldName.TABLE_NAME, FieldName.PERSONNEL_ID, gdata.personnel_id, FieldName.NAME, gdata.name,
FieldName.GALLERY_ID, gdata.gallery_id);
foreach (Picture p in gdata.pictures)
{
if (p.gallery_id != 0)
{
updatepicturecmd += string.Format("update {0} set {1} = '{2}' where {3} = {4} and {5} = '{6}' ",
Picture.FieldName.TABLE_NAME, Picture.FieldName.CAPTION, p.caption, Picture.FieldName.GALLERY_ID,
gdata.gallery_id, Picture.FieldName.FILE_NAME, p.file_name);
//Gen delete cond
excludecond += string.Format("and {0} != '{1}' ", Picture.FieldName.FILE_NAME, p.file_name);
}
else
{
if (insertintopicturecmd.Length <= len)
insertintopicturecmd += string.Format("({0},'{1}','{2}')", gdata.gallery_id, p.file_name, p.caption);
else
insertintopicturecmd += string.Format(",({0},'{1}','{2}')", gdata.gallery_id, p.file_name, p.caption);
}
}
if (insertintopicturecmd.Length <= len)
insertintopicturecmd = "";
deletefrompicturecmd += string.Format("and ({0}) ", excludecond);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"({1}) " +
"as outputdelete ",
temp1tablename, deletefrompicturecmd);
curri_id = gdata.curri_id;
aca_year = gdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand(true);
d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} {6} END",ifexistscond, createtabletemp1,updategallerycmd,updatepicturecmd,
insertintotemp1,insertintopicturecmd,selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]);
if (gallery_id != 0)
{
//If date is not null (means result is row with picture data)
if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null)
{
string real_t_prename;
if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์")
real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString());
else
real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString();
result.Add(new Gallery_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]),
t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]),
date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
pictures = new List<Picture>()
});
}
result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]),
file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(),
caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].Ordinal].ToString()
});
}
else
{
dummyfordeletepictures.pictures.Add(new Picture
{
file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString()
});
}
}
data.Dispose();
}
else
{
//Reserved for return error string
res.Close();
return "ไม่พบข้อมูลอัลบั้มรูปภาพที่ต้องการแก้ไขในระบบ";
}
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();
}
result.Add(dummyfordeletepictures);
return result;
}
public async Task<object> SelectByCurriculumAcademic()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Gallery_detail> result = new List<Gallery_detail>();
d.iCommand.CommandText = getSelectByCurriculumAcademicCommand(false);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]);
if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null)
{
string real_t_prename;
if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์")
real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString());
else
real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString();
result.Add(new Gallery_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]),
t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]),
date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
pictures = new List<Picture>()
});
}
result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]),
file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(),
caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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> InsertQuestionAnswer(Questionare_question_form qdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
string insertintoquestionareresobj = string.Format("insert into {0} values ",Questionare_result_obj.FieldName.TABLE_NAME);
foreach(Questionare_question_answer item in qdata.question_list)
{
insertintoquestionareresobj += string.Format("({0},{1})", item.questionare_question_id, item.answer);
if (item != qdata.question_list.Last())
insertintoquestionareresobj += ",";
}
string insertintoquestionareressub = string.Format("insert into {0} values ({1},'{2}')",
Questionare_result_sub.FieldName.TABLE_NAME, qdata.question_list.First().questionare_set_id,
qdata.suggestion);
string truecasecmd = string.Format("BEGIN {0} {1} END", insertintoquestionareresobj, insertintoquestionareressub);
d.iCommand.CommandText = string.Format("if exists (select * from {0} where {1} = {2}) {3} else return ",
Questionare_set.FieldName.TABLE_NAME, Questionare_set.FieldName.QUESTIONARE_SET_ID, qdata.question_list.First().questionare_set_id,
truecasecmd);
try
{
int rowaffacted = await d.iCommand.ExecuteNonQueryAsync();
if (rowaffacted > 0)
return null;
else
return "แบบสอบถามนี้ถูกลบแล้ว";
}
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();
}
}
public async Task<object> Insert(List<UsernamePassword> list, List<string> target_curri_id_list)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<string> result = new List<string>();
string temp5tablename = "#temp5";
string temp6tablename = "#temp6";
string temp7tablename = "#temp7";
string createtabletemp5 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] VARCHAR(60) NULL," +
"PRIMARY KEY ([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} VARCHAR(60) COLLATE DATABASE_DEFAULT ",
temp5tablename, FieldName.EMAIL);
string createtabletemp6 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) ",
temp6tablename, User_list.FieldName.USER_ID);
string createtabletemp7 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] {2} NULL," +
"PRIMARY KEY ([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT ",
temp7tablename, User_curriculum.FieldName.CURRI_ID,DBFieldDataType.CURRI_ID_TYPE);
string insertintotemp7 = string.Format("insert into {0} values ", temp7tablename);
int len = insertintotemp7.Length;
foreach (string curriitem in target_curri_id_list)
{
if (insertintotemp7.Length <= len)
insertintotemp7 += string.Format("('{0}')", curriitem);
else
insertintotemp7 += string.Format(",('{0}')", curriitem);
}
string insertintousercurri = "";
if (insertintotemp7.Length > len)
insertintousercurri = string.Format("insert into {0} " +
"select {1},{2} from {3},{4} ",
User_curriculum.FieldName.TABLE_NAME,
User_curriculum.FieldName.USER_ID,
User_curriculum.FieldName.CURRI_ID, temp6tablename, temp7tablename);
else
insertintotemp7 = "";
string insertcmd = "";
foreach (UsernamePassword item in list)
{
string ts = DateTime.Now.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[93];
insertcmd += string.Format(
"IF NOT EXISTS(select * from {0} where {1} = '{2}' or {3} = '{2}') " +
"begin " +
"insert into {4} " +
"select * from (insert into {0} ({5}, {1}, {6}, {3}, {7}, {15}) output inserted.{8} " +
"values ('{9}', '{2}', '{10}', '{2}', '{11}', '{2}')) as outputinsert " +
"insert into {12} ({13}) select {8} from {4} " +
insertintousercurri + " " +
"delete from {4} " +
"end " +
"else " +
"begin " +
"insert into {14} values ('{2}') " +
"end ",User_list.FieldName.TABLE_NAME, User_list.FieldName.USERNAME,item.username,
User_list.FieldName.EMAIL,temp6tablename,
User_list.FieldName.USER_TYPE_ID,FieldName.PASSWORD,FieldName.TIMESTAMP,
User_list.FieldName.USER_ID,
/*****9****/ 1,item.password,ts,
/****12****/ FieldName.TABLE_NAME,FieldName.TEACHER_ID,temp5tablename,
User_list.FieldName.T_NAME
);
}
string selectcmd = string.Format("select {1} from {0} ", temp5tablename, FieldName.EMAIL);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END ", createtabletemp5,createtabletemp6,createtabletemp7,
insertintotemp7,insertcmd,selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(
item.ItemArray[data.Columns[FieldName.EMAIL].Ordinal].ToString()
);
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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();
}
if (result.Count != 0)
return result;
else
return null;
}
public object UpdatePassword(string preferoldpassword,ref string newpassword,int user_id)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
d.iCommand.CommandText = string.Format("select {0} from {1} where {2} = {3}",
Teacher.FieldName.PASSWORD,User_list.FieldName.TABLE_NAME,User_list.FieldName.USER_ID,user_id);
try
{
object oldpass = d.iCommand.ExecuteScalar();
if (oldpass != null)
{
PasswordVerificationResult result = hasher.VerifyHashedPassword(oldpass.ToString(), preferoldpassword);
if(result == PasswordVerificationResult.Success || result == PasswordVerificationResult.SuccessRehashNeeded)
{
newpassword = hasher.HashPassword(newpassword);
d.iCommand.CommandText = string.Format("update {0} set {1} = '{2}' where {3} = {4}",
User_list.FieldName.TABLE_NAME, Teacher.FieldName.PASSWORD, newpassword, User_list.FieldName.USER_ID, user_id);
d.iCommand.ExecuteNonQuery();
return null;
}
else
{
return "รหัสผ่านเก่าไม่ถูกต้อง";
}
}
else
{
return "ไม่พบผู้ใช้งานนี้ในระบบ";
}
}
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();
}
}
