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> 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> SelectByCurriculumAcademic()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Minutes_detail> result = new List <Minutes_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)
{
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]);
date = item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString();
//If date is not null (means result is row with attendee data)
if (date != "")
{
//Is it exists?
if (result.FirstOrDefault(m => m.minutes_id == minutes_id) == null)
{
result.Add(new Minutes_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
teacher_id = item.ItemArray[data.Columns["t1_id"].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns["t1_id"].Ordinal]) : 0,
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t1_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t1_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(),
topic_name = item.ItemArray[data.Columns[FieldName.TOPIC_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]),
date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats(new System.Globalization.CultureInfo("fr-FR"))[0]
});
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
else
{
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
}
else
{
result.First(m => m.minutes_id == minutes_id).pictures.Add(new Minutes_pic
{
file_name = item.ItemArray[data.Columns[Minutes_pic.FieldName.FILE_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].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> UpdateMinutesWithSelect(Minutes_detail mdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Minutes_detail> result = new List <Minutes_detail>();
Minutes_detail dummyfordeleteminutes = new Minutes_detail();
string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME,
FieldName.MINUTES_ID, mdata.minutes_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_1;
//TOEDIT --UPDATE MINUTES TABLE (2 CASE WITH FILE_NAME UPDATES)
if (mdata.file_name != "")
{
insertintotemp1_1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(update {1} set {2} = {3}, {4} = '{5}', {6} = '{7}',{8} = '{9}' " +
"OUTPUT deleted.{8} where {10} = {11}) as outputupdate ",
temp1tablename, FieldName.TABLE_NAME, FieldName.TEACHER_ID, mdata.teacher_id,
FieldName.DATE, mdata.date, FieldName.TOPIC_NAME, mdata.topic_name,
FieldName.FILE_NAME, mdata.file_name, FieldName.MINUTES_ID, mdata.minutes_id);
}
else
{
insertintotemp1_1 = string.Format("update {0} set {1} = {2}, {3} = '{4}', {5} = '{6}' " +
"where {7} = {8} ",
FieldName.TABLE_NAME, FieldName.TEACHER_ID, mdata.teacher_id,
FieldName.DATE, mdata.date, FieldName.TOPIC_NAME, mdata.topic_name,
FieldName.MINUTES_ID, mdata.minutes_id);
}
//OK UPDATE ATTENDEE
string deletefromminutesattendee = string.Format("DELETE FROM {0} where {1} = {2} ",
Minutes_attendee.FieldName.TABLE_NAME, Minutes_attendee.FieldName.MINUTES_ID, mdata.minutes_id);
string insertintominutesattendee = string.Format("INSERT INTO {0} values ",
Minutes_attendee.FieldName.TABLE_NAME);
foreach (Teacher_with_t_name t in mdata.attendee)
{
insertintominutesattendee += string.Format("({0},{1})", mdata.minutes_id, t.teacher_id);
if (t != mdata.attendee.Last())
{
insertintominutesattendee += ",";
}
}
string insertintominutespiccmd = string.Format("insert into {0} values ", Minutes_pic.FieldName.TABLE_NAME);
string deletefromminutespicturecmd = string.Format("delete from {0} output deleted.{1} where {2} = {3} ",
Minutes_pic.FieldName.TABLE_NAME, Minutes_pic.FieldName.FILE_NAME, Minutes_pic.FieldName.MINUTES_ID,
mdata.minutes_id);
string excludecond = "1=1 ";
int len = insertintominutespiccmd.Length;
foreach (Minutes_pic m in mdata.pictures)
{
if (m.minutes_id != 0)
{
//Gen delete cond
excludecond += string.Format("and {0} != '{1}' ", Minutes_pic.FieldName.FILE_NAME, m.file_name);
}
else
{
if (insertintominutespiccmd.Length <= len)
{
insertintominutespiccmd += string.Format("({0},'{1}')", mdata.minutes_id, m.file_name);
}
else
{
insertintominutespiccmd += string.Format(",({0},'{1}')", mdata.minutes_id, m.file_name);
}
}
}
if (insertintominutespiccmd.Length <= len)
{
insertintominutespiccmd = "";
}
deletefromminutespicturecmd += string.Format("and ({0}) ", excludecond);
string insertintotemp1_2 = string.Format("INSERT INTO {0} " +
"select * from " +
"({1}) " +
"as outputdelete ",
temp1tablename, deletefromminutespicturecmd);
curri_id = mdata.curri_id;
aca_year = mdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand(true);
d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} {6} {7} END", ifexistscond, createtabletemp1,
insertintotemp1_1, deletefromminutesattendee, insertintominutesattendee,
insertintotemp1_2, insertintominutespiccmd, 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)
{
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]);
date = item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString();
//If date is not null (means result is row with attendee data)
if (date != "")
{
//Is it exists?
if (result.FirstOrDefault(m => m.minutes_id == minutes_id) == null)
{
result.Add(new Minutes_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
teacher_id = item.ItemArray[data.Columns["t1_id"].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns["t1_id"].Ordinal]) : 0,
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t1_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t1_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(),
topic_name = item.ItemArray[data.Columns[FieldName.TOPIC_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]),
date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats(new System.Globalization.CultureInfo("fr-FR"))[0]
});
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
else
{
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
}
else if (minutes_id != -1 && minutes_id != -2)
{
result.First(m => m.minutes_id == minutes_id).pictures.Add(new Minutes_pic
{
file_name = item.ItemArray[data.Columns[Minutes_pic.FieldName.FILE_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal])
});
}
//Add file_name to delete into dummy obj : minute_id == -1 || minutes_id == -2
else
{
dummyfordeleteminutes.pictures.Add(new Minutes_pic
{
file_name = item.ItemArray[data.Columns[Minutes_pic.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(dummyfordeleteminutes);
return(result);
}
public async Task <object> InsertNewMinutesWithSelect(Minutes_detail mdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Minutes_detail> result = new List <Minutes_detail>();
string temp1tablename = "#temp1";
string temp2tablename = "#temp2";
string temp3tablename = "#temp3";
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.MINUTES_ID);
string createtabletemp2 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) "
, temp2tablename, Minutes_attendee.FieldName.TEACHER_ID);
string createtabletemp3 = 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 "
, temp3tablename, Minutes_pic.FieldName.FILE_NAME,
DBFieldDataType.FILE_NAME_TYPE);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(insert into {1}({2},{3},{4},{5},{6},{7}) output inserted.{8} values " +
"('{9}','{10}',{11},'{12}','{13}','{14}')) " +
"as outputinsert ",
temp1tablename, FieldName.TABLE_NAME, FieldName.TEACHER_ID, FieldName.CURRI_ID, FieldName.ACA_YEAR,
FieldName.DATE, FieldName.TOPIC_NAME, FieldName.FILE_NAME, FieldName.MINUTES_ID,
mdata.teacher_id, mdata.curri_id, mdata.aca_year, mdata.date, mdata.topic_name,
mdata.file_name);
string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename);
foreach (Teacher_with_t_name t in mdata.attendee)
{
insertintotemp2 += string.Format(",({0})", t.teacher_id);
}
string insertintotemp3 = string.Format("INSERT INTO {0} VALUES (null)", temp3tablename);
foreach (Minutes_pic m in mdata.pictures)
{
insertintotemp3 += string.Format(",('{0}')", m.file_name);
}
string insertintominutesattendee = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Minutes_attendee.FieldName.TABLE_NAME, FieldName.MINUTES_ID, Minutes_attendee.FieldName.TEACHER_ID,
temp1tablename, temp2tablename);
string insertintominutespic = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Minutes_pic.FieldName.TABLE_NAME, FieldName.MINUTES_ID, Minutes_pic.FieldName.FILE_NAME,
temp1tablename, temp3tablename);
curri_id = mdata.curri_id;
aca_year = mdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand(false);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} {7} {8} END", createtabletemp1, createtabletemp2, createtabletemp3,
insertintotemp1, insertintotemp2, insertintotemp3, insertintominutesattendee,
insertintominutespic, 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)
{
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]);
date = item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString();
//If date is not null (means result is row with attendee data)
if (date != "")
{
//Is it exists?
if (result.FirstOrDefault(m => m.minutes_id == minutes_id) == null)
{
result.Add(new Minutes_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
teacher_id = item.ItemArray[data.Columns["t1_id"].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns["t1_id"].Ordinal]) : 0,
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t1_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t1_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(),
topic_name = item.ItemArray[data.Columns[FieldName.TOPIC_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]),
date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats(new System.Globalization.CultureInfo("fr-FR"))[0]
});
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
else
{
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
}
else
{
result.First(m => m.minutes_id == minutes_id).pictures.Add(new Minutes_pic
{
file_name = item.ItemArray[data.Columns[Minutes_pic.FieldName.FILE_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].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> 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> 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> SelectByIndicator()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
Others_evaluation_s_indic_name_list_with_file_name result = new Others_evaluation_s_indic_name_list_with_file_name();
string temp5tablename = "#temp5";
string createtabletemp5 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] VARCHAR(2000) NULL," +
"[{2}] INT NULL," +
"[{3}] INT NULL," +
"[{4}] INT NULL," +
"[{5}] {16} NULL," +
"[{6}] CHAR NULL," +
"[{7}] VARCHAR(MAX) NULL," +
"[{8}] VARCHAR(MAX) NULL," +
"[{9}] DATE NULL," +
"[{10}] TIME(0) NULL," +
"[{11}] {15} NULL," +
"[{12}] {14} NULL," +
"[{13}] INT NULL," +
"[{17}] VARCHAR(16) NULL," +
"[{18}] VARCHAR(60) NULL," +
"PRIMARY KEY([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{1}] VARCHAR(2000) collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{5}] {16} collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{6}] CHAR collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{7}] VARCHAR(MAX) collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{8}] VARCHAR(MAX) collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{11}] {15} collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{12}] {14} collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{17}] VARCHAR(16) collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{18}] VARCHAR(60) collate DATABASE_DEFAULT ",
temp5tablename, Sub_indicator.FieldName.SUB_INDICATOR_NAME,
FieldName.OTHERS_EVALUATION_ID, FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM,
FieldName.ASSESSOR_ID, FieldName.EVALUATION_SCORE, FieldName.STRENGTH, FieldName.IMPROVE,
FieldName.DATE, FieldName.TIME, Evidence.FieldName.FILE_NAME, FieldName.CURRI_ID,
FieldName.ACA_YEAR, DBFieldDataType.CURRI_ID_TYPE, DBFieldDataType.FILE_NAME_TYPE,
DBFieldDataType.USERNAME_TYPE, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME);
string insertintotemp5_1 = string.Format("insert into {13} " +
"select {2}, {0}.*,{17},{18} " +
"from {0}, {1}, {14} " +
"where {0}.{3} = {4} and " +
"{0}.{5} = {1}.{6} and " +
"{0}.{3} = {1}.{7} and " +
"{15} = {16} and " + //user_id = assessor_id
"{1}.{8} = " +
"(select max(s1.{8}) from {1} as s1 where s1.{8} <= {9}) and " +
"{0}.{10} = '{11}' and " +
"{0}.{12} = {9} ",
FieldName.TABLE_NAME, Sub_indicator.FieldName.TABLE_NAME,
Sub_indicator.FieldName.SUB_INDICATOR_NAME, FieldName.INDICATOR_NUM,
indicator_num, FieldName.SUB_INDICATOR_NUM,
Sub_indicator.FieldName.SUB_INDICATOR_NUM,
Sub_indicator.FieldName.INDICATOR_NUM,
Sub_indicator.FieldName.ACA_YEAR, aca_year,
FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, temp5tablename,
User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.ASSESSOR_ID,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME);
string insertintotemp5_2 = string.Format("insert into {12} " +
"select {1},0,{2},{3}," +
"'','0','','',null,null,'','{4}',{6},null,null " +
"from {0} where " +
"{2} = {13} and {5} = " +
"(select max(s1.{5}) from {0} as s1 where s1.{5} <= {6}) " +
"and not exists(select * from {7} " +
"where {8} = '{4}' and {9} = {6} and " +
"{0}.{2} = {7}.{10} " +
"and {0}.{3} = {7}.{11}) ",
Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.SUB_INDICATOR_NAME,
Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM,
curri_id, Sub_indicator.FieldName.ACA_YEAR, aca_year, FieldName.TABLE_NAME,
FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM,
FieldName.SUB_INDICATOR_NUM, temp5tablename, indicator_num);
string insertintotemp5_3truecase = string.Format("insert into {0} " +
"select {1}," +
"{2}.*," +
"{3}, {4} from {2}, {5}, {6} " +
"where {2}.{7} = {8} " +
"and {2}.{7} = {5}.{9} " +
"and {2}.{10} = 0 " +
"and {11} = {12} " +
"and {5}.{13} = " +
"(select max(s1.{13}) from {5} as s1 where s1.{13} <= {14}) " +
"and {2}.{15} = '{16}' and {2}.{17} = {14} ",
temp5tablename, Indicator.FieldName.INDICATOR_NAME_E, /*2 others*/ FieldName.TABLE_NAME,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
/*5 ind*/ Indicator.FieldName.TABLE_NAME,
/*6 usr*/ User_list.FieldName.TABLE_NAME,
FieldName.INDICATOR_NUM, indicator_num, Indicator.FieldName.INDICATOR_NUM,
FieldName.SUB_INDICATOR_NUM, User_list.FieldName.USER_ID,
FieldName.ASSESSOR_ID, Indicator.FieldName.ACA_YEAR, aca_year,
FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR
);
string insertintotemp5_3falsecase = string.Format("insert into {0} select " +
"{1}," +
"0,{2},0,'','0','','',null,null,'','{3}',{4},null,null " +
"from {5} " +
"where {6} = {2} " +
"and {5}.{7} = (select max({7}) from {5} where {7} <= {4}) ",
temp5tablename, Indicator.FieldName.INDICATOR_NAME_E, indicator_num, curri_id, aca_year,
Indicator.FieldName.TABLE_NAME, Indicator.FieldName.INDICATOR_NUM,
Indicator.FieldName.ACA_YEAR);
string insertintotemp5_3 = string.Format("if exists (select * from {0} where {1} = 0 and {2} = {3} and {4} = '{5}' and {6} = {7}) " +
" BEGIN " + insertintotemp5_3truecase + " END " +
" else " +
" BEGIN " + insertintotemp5_3falsecase + " END ",
FieldName.TABLE_NAME, FieldName.SUB_INDICATOR_NUM, FieldName.INDICATOR_NUM, indicator_num,
FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year);
string selectcmd = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.SUB_INDICATOR_NUM);
string selectselfscorecmd = string.Format("select {0},{1} " +
"from {2} " +
"where {3} = '{4}' and {5} = {6} and {7} = {8} ",
Self_evaluation.FieldName.SUB_INDICATOR_NUM, Self_evaluation.FieldName.EVALUATION_SCORE,
Self_evaluation.FieldName.TABLE_NAME, Self_evaluation.FieldName.CURRI_ID, curri_id,
Self_evaluation.FieldName.ACA_YEAR, aca_year, Self_evaluation.FieldName.INDICATOR_NUM, indicator_num);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp5, insertintotemp5_1,
insertintotemp5_2, insertintotemp5_3, selectcmd, selectselfscorecmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
if (data.Columns.Count > 2)
{
foreach (DataRow item in data.Rows)
{
if (Convert.ToInt32(item.ItemArray[data.Columns[FieldName.OTHERS_EVALUATION_ID].Ordinal]) != 0)
{
string h, m;
DateTime timeofday = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.TIME].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture);
h = timeofday.Hour.ToString();
m = timeofday.Minute.ToString();
result.evaluation_detail.Add(new Others_evaluation_sub_indicator_name
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
others_evaluation_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.OTHERS_EVALUATION_ID].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
assessor_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ASSESSOR_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(),
date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
time = (timeofday.Hour > 9 ? "" : "0") + h + '.' + (timeofday.Minute > 9 ? "" : "0") + m,
strength = item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString(),
improve = item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString(),
evaluation_score = item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal]) : 0,
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[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
if (item.ItemArray[data.Columns[Evidence.FieldName.FILE_NAME].Ordinal].ToString() != "")
{
result.file_name = item.ItemArray[data.Columns[Evidence.FieldName.FILE_NAME].Ordinal].ToString();
}
}
else
{
result.evaluation_detail.Add(new Others_evaluation_sub_indicator_name
{
curri_id = this.curri_id,
others_evaluation_id = 0,
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
assessor_id = 31,
date = "",
time = "",
strength = "",
improve = "",
evaluation_score = 0,
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[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
}
}
}
else
{
foreach (DataRow item in data.Rows)
{
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.SUB_INDICATOR_NUM].Ordinal]);
result.evaluation_detail.First(t => t.sub_indicator_num == sub_indicator_num).self_score =
Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal]);
}
}
data.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> SelectPersonnelWithCurriculum(string curri_id)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <User_curriculum_with_brief_detail> result = new List <User_curriculum_with_brief_detail>();
d.iCommand.CommandText = GetSelectWithCurriculumCommand(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 usrtype = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString();
if (usrtype == "อาจารย์")
{
result.Add(new User_curriculum_with_brief_detail
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[USER_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(),
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString(),
file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
type = usrtype
});
}
else
{
result.Add(new User_curriculum_with_brief_detail
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[USER_ID].Ordinal]),
t_name = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString() +
item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString(),
file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
type = usrtype
});
}
}
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> SelectPrimaryEvidenceWithTeacherDetail()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
Evidence_with_teacher_curri_indicator_detail result = new Evidence_with_teacher_curri_indicator_detail();
d.iCommand.CommandText = string.Format("select {0},{1},{2}.{3},{2}.{4},{5},{6},{7},{8} " +
"from {9},{2},{10},{11},{12} " +
"where {9}.{13} = '{14}' and {9}.{15} = {16} " +
"and {9}.{15} = {2}.{17} " +
"and {10}.{18} = {9}.{13} " +
"and {19} = {20} " +
"and {12}.{21} = {2}.{4} " +
"and {12}.{22} = (select max({12}.{22}) from {12} where {12}.{22} <= {2}.{3}) " +
"and {12}.{22} = {2}.{3} ",
FieldName.EVIDENCE_NAME, Cu_curriculum.FieldName.CURR_TNAME, /*2 primary_evidence*/ FieldName.TABLE_NAME,
FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, Indicator.FieldName.INDICATOR_NAME_T,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.EMAIL,
/*9*/ Primary_evidence_status.FieldName.TABLE_NAME, /*10*/ Cu_curriculum.FieldName.TABLE_NAME,
/*11*/ User_list.FieldName.TABLE_NAME, /*12*/ Indicator.FieldName.TABLE_NAME,
Primary_evidence_status.FieldName.CURRI_ID, curri_id, Primary_evidence_status.FieldName.PRIMARY_EVIDENCE_NUM,
primary_evidence_num, FieldName.PRIMARY_EVIDENCE_NUM, Cu_curriculum.FieldName.CURRI_ID,
Primary_evidence_status.FieldName.TEACHER_ID, User_list.FieldName.USER_ID,
/*21*/ Indicator.FieldName.INDICATOR_NUM, Indicator.FieldName.ACA_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.evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString();
result.aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]);
result.curr_tname = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString();
result.indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]);
result.indicator_name_t = item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NAME_T].Ordinal].ToString();
result.t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) +
item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString();
result.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> 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);
}
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> SelectNonCommitteeWithBriefDetail(List <string> user_list)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Committee_with_detail> result = new List <Committee_with_detail>();
string temp5tablename = "#temp5";
string createtabletemp5 = string.Format("create table {0}( " +
"[row_num] int identity(1, 1) not null," +
"[{1}] INT null," +
"[{2}] {5} null," +
"[{3}] VARCHAR(16) NULL," +
"[{4}] VARCHAR(60) NULL," +
"primary key([row_num])) " +
"alter table {0} " +
"alter column {2} {5} collate database_default " +
"alter table {0} " +
"alter column[{3}] VARCHAR(16) collate database_default " +
"alter table {0} " +
"alter column[{4}] VARCHAR(60) collate database_default ",
temp5tablename, FieldName.TEACHER_ID, FieldName.CURRI_ID,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
DBFieldDataType.CURRI_ID_TYPE);
string insertintotemp5_1 = string.Format("insert into {0} " +
"select {17}, {2}, {3}, {4} from ({5}) as {18}, {6} " +
"where {17} = {7} and {2} = '{8}' " +
"and not exists (select * from {9} where {10} = '{8}' and {11} = {12} and {9}.{13} = {18}.{17}) " +
"and not exists (select * from {14} where {15} = '{8}' and {16} = {12} and {14}.{1} = {18}.{17}) ",
temp5tablename, FieldName.TEACHER_ID, User_curriculum.FieldName.CURRI_ID, Teacher.FieldName.T_PRENAME,
Teacher.FieldName.T_NAME, /**5**/ oTeacher.getSelectTeacherByJoinCommand(), /**6**/ User_curriculum.FieldName.TABLE_NAME,
User_curriculum.FieldName.USER_ID, curri_id, President_curriculum.FieldName.TABLE_NAME,
President_curriculum.FieldName.CURRI_ID, President_curriculum.FieldName.ACA_YEAR,
/**12**/ this.aca_year, President_curriculum.FieldName.TEACHER_ID, FieldName.TABLE_NAME,
FieldName.CURRI_ID, FieldName.ACA_YEAR, Teacher.FieldName.TEACHER_ID, Teacher.FieldName.ALIAS_NAME);
string excludecond = "1=1 ";
foreach (string user_id in user_list)
{
excludecond += string.Format("and {2}.{0} != '{1}' ", FieldName.TEACHER_ID, user_id, FieldName.TABLE_NAME);
}
string insertintotemp5_2 = string.Format("insert into {0} " +
"select {1}.{2}, {3}, {4}, {5} " +
"from {1}, ({6}) as {12} " +
"where {3} = '{7}' and {8} = {9} and ({10}) " +
"and {1}.{2} = {12}.{11} ",
temp5tablename, FieldName.TABLE_NAME, FieldName.TEACHER_ID, FieldName.CURRI_ID,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
oTeacher.getSelectTeacherByJoinCommand(), curri_id,
FieldName.ACA_YEAR, aca_year, excludecond, Teacher.FieldName.TEACHER_ID,
Teacher.FieldName.ALIAS_NAME);
string selectcmd = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.TEACHER_ID);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} END ", createtabletemp5, insertintotemp5_1,
insertintotemp5_2, 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 Committee_with_detail
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
aca_year = this.aca_year,
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()
});
}
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> SelectAllCurriculumsAndAllPresidents()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
Curriculums_presidents_detail result = new Curriculums_presidents_detail();
string selcurriculumdata = string.Format("select {0}.{1},{2} " +
"from {0},{3} " +
"where {0}.{1} = {3}.{4} and {5} = {6} ",
Cu_curriculum.FieldName.TABLE_NAME, Cu_curriculum.FieldName.CURRI_ID, Cu_curriculum.FieldName.CURR_TNAME,
Curriculum_academic.FieldName.TABLE_NAME, Curriculum_academic.FieldName.CURRI_ID,
Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
string selallpresident = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " +
"where {6} = {7} and {8} = {9} ",
FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC,
Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.TEACHER_ID,
FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
string selallteacherwithcurri = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " +
"where {6} = 1 and {0}.{7} = {5}.{8} " +
"and {9} in (select {10} from {11} where {12} = {13}) ",
User_curriculum.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC,
Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID,
User_curriculum.FieldName.USER_ID, User_list.FieldName.USER_ID,
User_curriculum.FieldName.CURRI_ID, Curriculum_academic.FieldName.CURRI_ID, Curriculum_academic.FieldName.TABLE_NAME,
Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year));
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} END", selcurriculumdata, selallpresident, selallteacherwithcurri);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
if (data.Columns.Count == 2)
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_curri_id.Add(curri_id);
result.all_presidents[curri_id] = new Curri_with_pres_and_cand
{
curri_tname = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString()
};
}
}
else if (data.Columns.Count == 7)
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_presidents[curri_id].presidents.Add(new Personnel_brief_detail
{
tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(),
pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(),
user_id = item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString()
});
}
}
else
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_presidents[curri_id].candidates.Add(new Personnel_brief_detail
{
tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(),
pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(),
user_id = item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal].ToString()
});
}
}
data.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> SelectAllPendingPrimaryEvidence()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Personnel_with_pending_primary_evidence> result = new List <Personnel_with_pending_primary_evidence>();
d.iCommand.CommandText = string.Format("select {0},{1}.{2},{3}.{4},{1}.{5},{6},{7},{8},{9} " +
"from {3}, {1}, {10}, {11} " +
"where ({12} = '0' or {12} = '4') " +
"and {3}.{13} = {1}.{14} " +
"and {10}.{15} = {3}.{4} " +
"and {0} = {16} ",
Teacher.FieldName.TEACHER_ID, /*1*/ FieldName.TABLE_NAME, FieldName.EVIDENCE_NAME,
/*3*/ Primary_evidence_status.FieldName.TABLE_NAME, Primary_evidence_status.FieldName.CURRI_ID,
FieldName.ACA_YEAR, Cu_curriculum.FieldName.CURR_TNAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
Teacher.FieldName.EMAIL,
/*10*/ Cu_curriculum.FieldName.TABLE_NAME, /*11*/ User_list.FieldName.TABLE_NAME,
Primary_evidence_status.FieldName.STATUS, Primary_evidence_status.FieldName.PRIMARY_EVIDENCE_NUM,
FieldName.PRIMARY_EVIDENCE_NUM, Cu_curriculum.FieldName.CURRI_ID, User_list.FieldName.USER_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)
{
int uid = Convert.ToInt32(item.ItemArray[data.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]);
if (result.FirstOrDefault(t => t.teacher_id == uid) == null)
{
result.Add(new Personnel_with_pending_primary_evidence
{
email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(),
teacher_id = uid,
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) +
item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
pendinglist = new List <Evidence_brief_detail>()
});
}
result.First(t => t.teacher_id == uid).pendinglist.Add(new Evidence_brief_detail {
evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
curr_tname = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[Primary_evidence_status.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> Select(oCurriculum_academic curriacadata)
{
//System.Globalization.CultureInfo en = new System.Globalization.CultureInfo("en-US");
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Evaluation_overall_result> result = new List <Evaluation_overall_result>();
string temp1tablename = "#temp1";
string createtabletemp1 = string.Format("create table {0}(" +
"[row_num] int identity(1, 1) not null," +
"[assessor_type] int null," +
"[{1}] int null," +
"[{2}] int null," +
"[{3}] date null," +
"[{4}] TIME(0) null," +
"[caption1] varchar(2100) null," +
"[caption2] varchar(2100) null," +
"[{5}] float null," +
"primary key([row_num])) " +
"alter table {0} " +
"alter column[caption1] varchar(2100) collate database_default " +
"alter table {0} " +
"alter column[caption2] varchar(2100) collate database_default ",
temp1tablename, Indicator.FieldName.INDICATOR_NUM,
Sub_indicator.FieldName.SUB_INDICATOR_NUM,
Self_evaluation.FieldName.DATE, Self_evaluation.FieldName.TIME,
Self_evaluation.FieldName.EVALUATION_SCORE);
string insertintotemp1_1 = string.Format("insert into {0} " +
"select 0 as assess_data_type," +
"{1}.{2},{3},null,null,{4},{5},null from {1}, {6} " +
"where {1}.{7} = (select max({7}) from {1} where {7} <= {8}) " +
"and {1}.{7} = {6}.{9} " +
"and {1}.{2} = {6}.{10} order by {1}.{2} ",
temp1tablename, Indicator.FieldName.TABLE_NAME, Indicator.FieldName.INDICATOR_NUM,
Sub_indicator.FieldName.SUB_INDICATOR_NUM, Indicator.FieldName.INDICATOR_NAME_E,
Sub_indicator.FieldName.SUB_INDICATOR_NAME, Sub_indicator.FieldName.TABLE_NAME,
Indicator.FieldName.ACA_YEAR, curriacadata.aca_year, Sub_indicator.FieldName.ACA_YEAR,
Sub_indicator.FieldName.INDICATOR_NUM);
string insertintotemp1_2 = string.Format("insert into {0} " +
"select 1 as assessor_type," +
"{1}," +
"null as sub_indicator_num," +
"max({2}) as {2}," +
"max({14}) as {14}," +
"max({3})," +
"max({4})," +
"null " +
"from {6},{7} where {8} = '{9}' and {10} = {11} " +
"and {12} = {13} " +
"group by {1} ",
temp1tablename, Indicator.FieldName.INDICATOR_NUM,
Self_evaluation.FieldName.DATE,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
Self_evaluation.FieldName.EVALUATION_SCORE,
Self_evaluation.FieldName.TABLE_NAME /****6****/,
User_list.FieldName.TABLE_NAME,
Self_evaluation.FieldName.CURRI_ID, curriacadata.curri_id,
Self_evaluation.FieldName.ACA_YEAR, curriacadata.aca_year,
Self_evaluation.FieldName.TEACHER_ID,
User_list.FieldName.USER_ID, Self_evaluation.FieldName.TIME);
string insertintotemp1_3 = string.Format("insert into {0} " +
"select 2 as assessor_type," +
"{1}," +
"null as sub_indicator_num," +
"max({2}) as {2}," +
"max({14}) as {14}," +
"max({3})," +
"max({4})," +
"null " +
"from {6},{7} where {8} = '{9}' and {10} = {11} " +
"and {12} = {13} " +
"group by {1} ",
temp1tablename, Indicator.FieldName.INDICATOR_NUM,
Others_evaluation.FieldName.DATE,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
Others_evaluation.FieldName.EVALUATION_SCORE,
Others_evaluation.FieldName.TABLE_NAME /****6****/,
User_list.FieldName.TABLE_NAME,
Others_evaluation.FieldName.CURRI_ID, curriacadata.curri_id,
Others_evaluation.FieldName.ACA_YEAR, curriacadata.aca_year,
Others_evaluation.FieldName.ASSESSOR_ID,
User_list.FieldName.USER_ID, Others_evaluation.FieldName.TIME);
string insertintotemp1_4 = string.Format("insert into {0}(assessor_type,{1},{2},{3}) " +
"select 3 as assessor_type," +
"{1}," +
"{2}," +
"{3} " +
"from {4} where {5} = '{6}' and {7} = {8} ",
temp1tablename, Indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM,
Self_evaluation.FieldName.EVALUATION_SCORE,
Self_evaluation.FieldName.TABLE_NAME,
Self_evaluation.FieldName.CURRI_ID, curriacadata.curri_id,
Self_evaluation.FieldName.ACA_YEAR, curriacadata.aca_year);
string insertintotemp1_5 = string.Format("insert into {0}(assessor_type,{1},{2},{3}) " +
"select 4 as assessor_type," +
"{1}," +
"{2}," +
"{3} " +
"from {4} where {5} = '{6}' and {7} = {8} ",
temp1tablename, Indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM,
Others_evaluation.FieldName.EVALUATION_SCORE,
Others_evaluation.FieldName.TABLE_NAME,
Others_evaluation.FieldName.CURRI_ID, curriacadata.curri_id,
Others_evaluation.FieldName.ACA_YEAR, curriacadata.aca_year);
string selectcmd = string.Format("select * from {0} ", temp1tablename);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} END", createtabletemp1, insertintotemp1_1, insertintotemp1_2, insertintotemp1_3,
insertintotemp1_4, insertintotemp1_5, selectcmd);
try
{
//Retrieve self_evaluation data
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 assess_type = Convert.ToInt32(item.ItemArray[data.Columns["assessor_type"].Ordinal]);
//Type 0:Retrieve indicator name/sub indicator name
if (assess_type == 0)
{
int indnum = Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal]);
if (result.FirstOrDefault(t => t.indicator_num == indnum) == null)
{
result.Add(new Evaluation_overall_result
{
indicator_num = indnum,
indicator_name = item.ItemArray[data.Columns["caption1"].Ordinal].ToString(),
});
//Force add sub_indicator_num = 0 with sub_indicator_name as indicator_name
result.First(t => t.indicator_num == indnum).sub_indicator_result.Add(new Sub_indicator_result
{
sub_indicator_num = 0,
sub_indicator_name = item.ItemArray[data.Columns["caption1"].Ordinal].ToString()
});
}
result.First(t => t.indicator_num == indnum).sub_indicator_result.Add(new Sub_indicator_result
{
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]),
sub_indicator_name = item.ItemArray[data.Columns["caption2"].Ordinal].ToString()
});
}
//Type 1:Retrieve self assessment time and the name of assessor
else if (assess_type == 1)
{
Evaluation_overall_result target = result.First(t => t.indicator_num == Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal]));
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();
target.self_date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3];
target.self_name = NameManager.GatherPreName(item.ItemArray[data.Columns["caption1"].Ordinal].ToString()) + item.ItemArray[data.Columns["caption2"].Ordinal].ToString();
target.self_time = (timeofday.Hour > 9 ? "" : "0") + h + '.' + (timeofday.Minute > 9 ? "" : "0") + m;
}
//Type 2:Retrieve other assessment time and the name of assessor
else if (assess_type == 2)
{
Evaluation_overall_result target = result.First(t => t.indicator_num == Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal]));
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();
target.other_date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3];
target.other_name = NameManager.GatherPreName(item.ItemArray[data.Columns["caption1"].Ordinal].ToString()) + item.ItemArray[data.Columns["caption2"].Ordinal].ToString();
target.other_time = (timeofday.Hour > 9 ? "" : "0") + h + '.' + (timeofday.Minute > 9 ? "" : "0") + m;
}
//Type 3:Retrieve overall individual result of self evaluation
else if (assess_type == 3)
{
Sub_indicator_result target = result.First(t => t.indicator_num == Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal])).
sub_indicator_result.First(t => t.sub_indicator_num == Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]));
if (item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal].ToString() != "")
{
target.sub_indicator_self_result = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal]);
}
}
//Type 4:Retrieve overall individual result of other evaluation
else
{
Sub_indicator_result target = result.First(t => t.indicator_num == Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal])).
sub_indicator_result.First(t => t.sub_indicator_num == Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]));
if (item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal].ToString() != "")
{
target.sub_indicator_other_result = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal]);
}
}
}
data.Dispose();
//Get another data....
}
else
{
//Reserved for return error string
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> SelectPersonnelIdAndTName(string curri_id, int selectmode)
//selectmode 0 : only teacher and staff,1 for all
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Personnel_with_t_name> result = new List <Personnel_with_t_name>();
if (selectmode == 0)
{
d.iCommand.CommandText = string.Format("select {4}.{0},{1},{2},{3} from {4},{5},{9} " +
"where {6} = '{7}' " +
"and {4}.{0} = {5}.{8} " +
"and ({4}.{10} = 1 or {4}.{10} = 2) " + /*teacher = 1,staff = 2*/
"and {4}.{10} = {9}.{11}",
User_list.FieldName.USER_ID, User_list.FieldName.T_PRENAME,
Student.FieldName.T_NAME, User_type.FieldName.USER_TYPE_NAME,
User_list.FieldName.TABLE_NAME, User_curriculum.FieldName.TABLE_NAME,
Cu_curriculum.FieldName.CURRI_ID, curri_id, User_curriculum.FieldName.USER_ID,
User_type.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID,
User_type.FieldName.USER_TYPE_ID
);
}
else
{
if (curri_id != "999")
{
d.iCommand.CommandText = string.Format("select {4}.{0},{1},{2},{3} from {4},{5},{9} " +
"where {6} = '{7}' " +
"and {4}.{0} = {5}.{8} and {9}.{10} = {4}.{11} ",
User_list.FieldName.USER_ID, User_list.FieldName.T_PRENAME,
Student.FieldName.T_NAME, User_type.FieldName.USER_TYPE_NAME,
User_list.FieldName.TABLE_NAME, User_curriculum.FieldName.TABLE_NAME,
Cu_curriculum.FieldName.CURRI_ID, curri_id, User_curriculum.FieldName.USER_ID,
User_type.FieldName.TABLE_NAME, User_type.FieldName.USER_TYPE_ID,
User_list.FieldName.USER_TYPE_ID);
}
else
{
//curri_id = 999 => select the person who didn't in any curriculum
d.iCommand.CommandText = string.Format("select {0},{1},{2},{3} from {4},{5} " +
"where not exists(select * from {6} where {6}.{7} = {4}.{0}) and {4}.{8} != 7 and {4}.{8} = {5}.{9} ",
User_list.FieldName.USER_ID, User_list.FieldName.T_PRENAME,
Student.FieldName.T_NAME, User_type.FieldName.USER_TYPE_NAME,
User_list.FieldName.TABLE_NAME, User_type.FieldName.TABLE_NAME,
User_curriculum.FieldName.TABLE_NAME,
User_curriculum.FieldName.USER_ID, User_list.FieldName.USER_TYPE_ID,
User_type.FieldName.USER_TYPE_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)
{
if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์")
{
result.Add(new Personnel_with_t_name
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[USER_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()
});
}
else
{
result.Add(new Personnel_with_t_name
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[USER_ID].Ordinal]),
t_name = 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> InsertNewUserCurriculumWithSelect(List <User_curriculum> list)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <User_curriculum_with_brief_detail> result = new List <User_curriculum_with_brief_detail>();
string insertifnotexistscmd = "";
foreach (User_curriculum c in list)
{
insertifnotexistscmd += string.Format("if not exists(select * from {0} where {1} = '{2}' and {3} = {4}) " +
"BEGIN " +
"INSERT INTO {0} values({4}, '{2}') " +
"END ",
FieldName.TABLE_NAME, FieldName.CURRI_ID, c.curri_id, FieldName.USER_ID, c.user_id);
}
string selectcmd = ViewModels.Wrappers.oPersonnel.GetSelectWithCurriculumCommand(list.First().curri_id);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} END", insertifnotexistscmd, 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)
{
string usrtype = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString();
if (usrtype == "อาจารย์")
{
result.Add(new User_curriculum_with_brief_detail
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) +
item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
type = usrtype
});
}
else
{
result.Add(new User_curriculum_with_brief_detail
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal]),
t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() +
item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
type = usrtype
});
}
}
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);
}