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> SelectWithDetail(oCurriculum_academic curriacadata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Questionare_set_detail> result = new List<Questionare_set_detail>();
curri_id = curriacadata.curri_id;
aca_year = curriacadata.aca_year;
d.iCommand.CommandText = getSelectByCurriculumAcademicCommand();
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 qid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]);
if (result.FirstOrDefault(t => t.questionare_set_id == qid) == null)
{
result.Add(new Questionare_set_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
name = item.ItemArray[data.Columns[FieldName.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,
questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]),
date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
});
}
result.First(t => t.questionare_set_id == qid).target.Add(new User_type {
user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID].Ordinal]),
user_type = item.ItemArray[data.Columns[User_type.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> Update()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Educational_teacher_staff> result = new List<Educational_teacher_staff>();
string updatecmd = string.Format("update {0} set {1} = {2},{3} = {4},{5} = {6},{7} = {8},{9} = {10} where {11} = {12} ",
FieldName.TABLE_NAME, FieldName.DEGREE, ParameterName.DEGREE, FieldName.PRE_MAJOR, ParameterName.PRE_MAJOR, FieldName.MAJOR, ParameterName.MAJOR, FieldName.GRAD_YEAR, ParameterName.GRAD_YEAR, FieldName.COLLEGE, ParameterName.COLLEGE, FieldName.EDUCATION_ID, ParameterName.EDUCATION_ID);
string selectcmd = GetSelectEducationByPersonnelIdCommand();
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.PERSONNEL_ID, personnel_id));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.DEGREE, degree));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.PRE_MAJOR, pre_major));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.MAJOR, major));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.GRAD_YEAR, grad_year));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.COLLEGE, college));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.EDUCATION_ID, education_id));
d.iCommand.CommandText = string.Format("BEGIN {0} {1} END", updatecmd, 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(new Educational_teacher_staff
{
personnel_id = this.personnel_id,
college = item.ItemArray[data.Columns[FieldName.COLLEGE].Ordinal].ToString(),
degree = Convert.ToChar(item.ItemArray[data.Columns[FieldName.DEGREE].Ordinal]),
education_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EDUCATION_ID].Ordinal]),
grad_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GRAD_YEAR].Ordinal]),
major = item.ItemArray[data.Columns[FieldName.MAJOR].Ordinal].ToString(),
pre_major = item.ItemArray[data.Columns[FieldName.PRE_MAJOR].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
res.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
//Handle error from sql execution
if (ex.Number == 8152)
return "มีรายละเอียดของข้อมูลการศึกษาบางส่วนที่ต้องการบันทึกมีขนาดที่ยาวเกินกำหนด";
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> 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> SelectWhereByCurriculumAcademic()
{
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} where {1} = {2} and {3} = {4}", FieldName.TABLE_NAME,
FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.YEAR, ParameterName.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.YEAR, year));
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 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 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> SelectByIndicatorAndCurriculum(oIndicator inddata, string curri_id)
{
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} = {2} and {3} = '{4}' and {5} = {6} order by {7} ",
FieldName.TABLE_NAME,FieldName.INDICATOR_NUM,inddata.indicator_num,FieldName.CURRI_ID,
curri_id,FieldName.ACA_YEAR,inddata.aca_year,FieldName.EVIDENCE_REAL_CODE);
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 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> SelectWhereByCurriculumAcademic()
{
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} where {1} = {2} and {3} = {4}", FieldName.TABLE_NAME,
FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.YEAR, ParameterName.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.YEAR, year));
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 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])
});
}
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 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_count> result = new List<oStudent_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 oStudent_count
{
ny1 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY1].Ordinal]),
ny2 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY2].Ordinal]),
ny3 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY3].Ordinal]),
ny4 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY4].Ordinal]),
ny5 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY5].Ordinal]),
ny6 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY6].Ordinal]),
ny7 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY7].Ordinal]),
ny8 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY8].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 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> 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> SelectByCurriID()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2}",FieldName.TABLE_NAME,FieldName.CURRI_ID,ParameterName.CURRI_ID);
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)
{
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
{
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 null;
}
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 async Task<object> SelectWhere(string wherecond)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oPrimary_evidence> result = new List<oPrimary_evidence>();
d.iCommand.CommandText = string.Format("select * from {0} where {1}", FieldName.TABLE_NAME, wherecond);
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 oPrimary_evidence
{
primary_evidence_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].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> SelectByIndicatorWithKeepAcaYearSource(oIndicator inddata)
{
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} = (select max(j.{1}) from {0} as j where j.{1} <= {2}) and {3} = {4}",
FieldName.TABLE_NAME, FieldName.ACA_YEAR,inddata.aca_year,FieldName.INDICATOR_NUM,inddata.indicator_num);
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 oSub_indicator
{
aca_year = inddata.aca_year,
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()
});
}
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;
d.iCommand.CommandText = string.Format("if not exists(select * from {0} where {9} = {2} and {10} = {3} and {11} = {1} and {12} = {8}) " +
"insert into {0} values ({1},{2},{3},{4},{5},{17},{18},{19},{6},{7},{8}) " +
"else " +
"update {0} set {13}={7},{14}={6},{15}={4}, {16}={5},{20} = {17},{21} = {18},{22} = {19} where {9} = {2} and {10} = {3} and {11} = {1} and {12} = {8} ",
FieldName.TABLE_NAME, ParameterName.ACA_YEAR, ParameterName.INDICATOR_NUM, ParameterName.SUB_INDICATOR_NUM,
ParameterName.TEACHER_ID, ParameterName.DETAIL, ParameterName.DATE, ParameterName.TIME, ParameterName.CURRI_ID,
FieldName.INDICATOR_NUM,FieldName.SUB_INDICATOR_NUM,FieldName.ACA_YEAR,FieldName.CURRI_ID,
FieldName.TIME, FieldName.DATE,FieldName.TEACHER_ID, FieldName.DETAIL,
/*17*/ParameterName.STRENGTH, ParameterName.WEAKNESS, ParameterName.IMPROVE,
/*20*/FieldName.STRENGTH, FieldName.WEAKNESS, FieldName.IMPROVE
);
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year));
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.TEACHER_ID, teacher_id));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.DETAIL, detail));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.STRENGTH, strength));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.WEAKNESS, weakness));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.IMPROVE, improve));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.DATE, date));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.TIME, time));
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_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 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 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> 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> insertNewUserType(List<string> usrtypedata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
string insertintousrtypeanddefpriv = "";
foreach (string type in usrtypedata)
{
//check whether target user_type is already exists!
insertintousrtypeanddefpriv += string.Format("if not exists (select * from {0} where {1} = '{2}') ", FieldName.TABLE_NAME, FieldName.USER_TYPE_NAME, type);
insertintousrtypeanddefpriv += string.Format("BEGIN insert into {0} values ('{1}') ", FieldName.TABLE_NAME, type);
//insert default privilege as value 1 for every privilege to target user type
insertintousrtypeanddefpriv += string.Format("insert into {0} " +
"select * from " +
"(select {1} from {2} where {3} = '{4}') as targetusrtype, " +
"(select {5}, {6} from {7} where {6} = 1) as titleprivdefault END ",
Default_privilege_by_type.FieldName.TABLE_NAME, FieldName.USER_TYPE_ID,
FieldName.TABLE_NAME,
FieldName.USER_TYPE_NAME, type, Title_privilege.FieldName.TITLE_CODE,
Title_privilege.FieldName.TITLE_PRIVILEGE_CODE, Title_privilege.FieldName.TABLE_NAME);
}
d.iCommand.CommandText = insertintousrtypeanddefpriv;
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> 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> SelectFileDownloadLink()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
d.iCommand.CommandText = string.Format("select {0} from {1} where {2} = {3} and {4} = {5}",
FieldName.FILE_NAME,FieldName.TABLE_NAME,
FieldName.CURRI_ID,ParameterName.CURRI_ID,
FieldName.ACA_YEAR,ParameterName.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.ACA_YEAR, aca_year));
try
{
object result = await d.iCommand.ExecuteScalarAsync();
if (result != null)
{
file_name = result.ToString();
return null;
}
else
{
//Reserved for return error string
return "notfound";
}
}
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 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> 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;
}