public async Task<IHttpActionResult> PostByCurriculumAcademic(oCurriculum_academic data)
{
datacontext.curri_id = data.curri_id;
datacontext.year = data.aca_year;
object result = await datacontext.SelectWhereByCurriculumAcademic();
if (result.GetType().ToString().CompareTo("System.String") == 0)
return InternalServerError(new Exception(result.ToString()));
else if (((List<oNew_student_count>)result).Count != 0)
{
return Ok(((IEnumerable<oNew_student_count>)result).First());
}
else
{
datacontext.curri_id = data.curri_id;
datacontext.year = data.aca_year;
datacontext.num_admis_f = -1;
datacontext.num_admis_m = -1;
datacontext.num_childstaff_f = -1;
datacontext.num_childstaff_m = -1;
datacontext.num_direct_f = -1;
datacontext.num_direct_m = -1;
datacontext.num_goodstudy_f = -1;
datacontext.num_goodstudy_m = -1;
datacontext.num_others_f = -1;
datacontext.num_others_m = -1;
return Ok(datacontext);
}
}
public async Task<IHttpActionResult> PostByCurriculumAcademic(oCurriculum_academic data)
{
datacontext.curri_id = data.curri_id;
datacontext.year = data.aca_year;
object result = await datacontext.SelectWhereByCurriculumAcademic();
if (result.GetType().ToString().CompareTo("System.String") == 0)
return InternalServerError(new Exception(result.ToString()));
else if (((List<oStudent_count>)result).Count != 0)
{
return Ok(((IEnumerable<oStudent_count>)result).First());
}
else
{
datacontext.curri_id = data.curri_id;
datacontext.year = data.aca_year;
datacontext.ny1 = -1;
datacontext.ny2 = -1;
datacontext.ny3 = -1;
datacontext.ny4 = -1;
datacontext.ny5 = -1;
datacontext.ny6 = -1;
datacontext.ny7 = -1;
datacontext.ny8 = -1;
return Ok(datacontext);
}
}
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<IHttpActionResult> PostToQueryDownloadLinkByCurriculumAcademic(oCurriculum_academic data)
{
if (data.curri_id == null) return BadRequest();
datacontext.curri_id = data.curri_id;
datacontext.aca_year = data.aca_year;
object result = await datacontext.SelectFileDownloadLink();
if (result == null)
return Ok(datacontext.file_name);
else if (result.ToString().Contains("notfound"))
return Ok("");
else
return InternalServerError(new Exception(result.ToString()));
}
public async Task<IHttpActionResult> PostNewCurriculumAcademic(oCurriculum_academic data)
{
if(data == null)
{
return BadRequest("กรุณาเลือกหลักสูตรและระบุปีการศึกษาให้เป็นค่าที่ถูกต้อง");
}
object result = await data.Insert();
if (result == null)
return Ok();
else if (result.ToString().Contains("Duplicate"))
{
return BadRequest("มีหลักสูตร-ปีการศึกษานี้อยู่ในระบบแล้ว กรุณาระบุปีการศึกษาใหม่อีกครั้ง");
}
else
{
return BadRequest(result.ToString());
}
}
public async Task<IHttpActionResult> PostToQueryGallery(oCurriculum_academic data)
{
datacontext.curri_id = data.curri_id;
datacontext.aca_year = data.aca_year;
return Ok(await datacontext.SelectByCurriculumAcademic());
}
public async Task<IHttpActionResult> PostForGenAunSAR(oCurriculum_academic data)
{
if (data == null)
return BadRequest("กรุณาระบุหลักสูตรและปีการศึกษาที่ต้องการดาวน์โหลดร่างรายงาน");
datacontext.curri_id = data.curri_id;
datacontext.aca_year = data.aca_year;
object res = await datacontext.getSectionSaveDataForSAR();
if (res.GetType().ToString() != "System.String")
{
//Start to generate SAR doc
SAR reportobject = (SAR)res;
var strBody = new System.Text.StringBuilder("");
strBody.Append("<html " +
"xmlns:o=\"urn:schemas-microsoft-com:office:office\" " +
"xmlns:w=\"urn:schemas-microsoft-com:office:word\" " +
"xmlns=\"http://www.w3.org/TR/REC-html40\">" +
"<head><title></title>\n");
//strBody.Append("<meta name=ProgId content=Word.Document>");
strBody.Append(
"<!--[if gte mso 9]>\n" +
"<xml>\n" +
"<w:WordDocument>\n" +
"<w:View>Print</w:View>\n" +
"<w:Zoom>90</w:Zoom>\n" +
"<w:DoNotOptimizeForBrowser/>\n" +
"</w:WordDocument>\n" +
"</xml>\n" +
"<!--[endif]>\n\n"
);
strBody.Append("<style>" +
"<!-- /* Style Definitions */" +
"@page Section1" +
" {size: 21cm 29.7cm; " +
" margin:1.0in 1.25in 1.0in 1.25in ; " +
" mso-header-margin:.5in; " +
" mso-page-orientation: portrait; " +
" mso-footer-margin:.5in; mso-paper-source:0; " +
" mso-footer: f1; } " +
" div.Section1" +
" {page:Section1;}" +
"-->" +
"table{" +
"font-family:'Th Sarabun New';font-size:16pt; " +
"} \n" +
"h1 {\n" +
"font-size:36pt " +
"}\n" +
"h2 {\n" +
"font-size:24pt " +
"}\n" +
"h3 {\n" +
"font-size:21pt " +
"}\n" +
"h4 {\n" +
"font-size:18pt " +
"}\n" +
"h5 {\n" +
"font-size:16pt " +
"}\n" +
"h6 {\n " +
"font-size:14pt " +
"}\n" +
"p { margin:0 } \n " +
"table.evidence,table.selfevalres {" +
"border:1px solid black; " +
"border-collapse:collapse; " +
"} " +
"table.evidence th,table.evidence td, " +
"table.selfevalres th,table.selfevalres td { border:1px solid black; } " +
"ol.sar-ol li { margin:0 auto 0 auto } " +
"</style></head>");
strBody.Append("<body style=\"tab-interval:.5in;font-family:'Th Sarabun New';font-size:16pt\">" +
"<div class=Section1>");
//BODY SECTION => read SAR object to gather data
foreach (Indicator_with_section_save_list i in reportobject.indicator_section_save_list)
{
strBody.Append(string.Format("<b>AUN.{0} {1} </b><br>", i.indicator_num, i.indicator_name));
foreach (Section_save_with_sub_indicator_detail s in i.section_save_list)
{
strBody.Append(string.Format("<b>{0}.{1} {2}</b><br>", i.indicator_num, s.sub_indicator_num, s.sub_indicator_name));
strBody.Append(string.Format(s.detail + "<br><br>"));
}
strBody.Append("<b>รายการเอกสารหลักฐาน</b><br>");
if (i.evidence_list.Count == 0)
{
strBody.Append("--ไม่พบข้อมูล--<br><br clear=all style='mso-special-character:line-break;page-break-before:always'>");
}
else
{
strBody.Append("<table class=\"evidence\"><tr><th width=100>รหัสเอกสาร</th><th>รายการ</th></tr>");
foreach (Evidence_detail_for_SAR e in i.evidence_list)
{
strBody.Append(string.Format("<tr><td align=\"center\">{0}-{1}</td><td>{2}</td></tr>", e.indicator_num, e.evidence_real_code, e.evidence_name));
}
strBody.Append("</table><br><br clear=all style='mso-special-character:line-break;page-break-before:always'>");
}
}
strBody.Append("<b>วิเคราะห์จุดแข็งและจุดอ่อน</b><br><br>");
foreach (Indicator_with_section_save_list i in reportobject.indicator_section_save_list)
{
strBody.Append(string.Format("<b>AUN.{0} {1} </b><br>", i.indicator_num, i.indicator_name));
strBody.Append(string.Format("<b>จุดแข็ง</b><br>"));
string strtoinsert = "";
//INSERT STRENGTH
foreach (Section_save_with_sub_indicator_detail s in i.section_save_list)
{
if (s.strength != "--ไม่พบข้อมูล--")
strtoinsert += string.Format("<li>{0}</li>", s.strength);
}
if (strtoinsert != "")
{
strBody.Append(string.Format("<ol class=\"sar-ol\">{0}</ol>", strtoinsert));
}
else
{
strBody.Append("--ไม่พบข้อมูล--<br>");
}
strtoinsert = "";
strBody.Append(string.Format("<b>จุดอ่อน</b><br>"));
//INSERT WEAKNESS
foreach (Section_save_with_sub_indicator_detail s in i.section_save_list)
{
if (s.weakness != "--ไม่พบข้อมูล--")
strtoinsert += string.Format("<li>{0}</li>", s.weakness);
}
if (strtoinsert != "")
{
strBody.Append(string.Format("<ol class=\"sar-ol\">{0}</ol>", strtoinsert));
}
else
{
strBody.Append("--ไม่พบข้อมูล--<br>");
}
strtoinsert = "";
strBody.Append(string.Format("<b>จุดที่ควรพัฒนา</b><br>"));
//INSERT AREA OF IMPROVEMENT
foreach (Section_save_with_sub_indicator_detail s in i.section_save_list)
{
if (s.improve != "--ไม่พบข้อมูล--")
strtoinsert += string.Format("<li>{0}</li>", s.improve);
}
if (strtoinsert != "")
{
strBody.Append(string.Format("<ol class=\"sar-ol\">{0}</ol>", strtoinsert));
}
else
{
strBody.Append("--ไม่พบข้อมูล--<br>");
}
if (i != reportobject.indicator_section_save_list.Last())
strBody.Append("<br>");
else
strBody.Append("<br clear=all style='mso-special-character:line-break;page-break-before:always'>");
}
int overallscoresum = 0;
int overalldivisor = 0;
strBody.Append("<b>สรุปผลการประเมินตนเอง</b><br>");
strBody.Append("<table class=\"selfevalres\">");
foreach (Indicator_with_section_save_list i in reportobject.indicator_section_save_list)
{
//Header row for each indicator
strBody.Append(string.Format("<tr><th>{0}</th> <td><b>{1}</b></td> <th style=\"width:0.85cm\">1</th><th style=\"width:0.85cm\">2</th><th style=\"width:0.85cm\">3</th><th style=\"width:0.85cm\">4</th><th style=\"width:0.85cm\">5</th><th style=\"width:0.85cm\">6</th><th style=\"width:0.85cm\">7</th></tr>", i.indicator_num, i.indicator_name));
foreach (Section_save_with_sub_indicator_detail s in i.section_save_list)
{
Self_evaluation_tiny_detail target = reportobject.indicator_self_evaluation_list.First(t => t.indicator_num == i.indicator_num).self_evaluation_list.First(u => u.sub_indicator_num == s.sub_indicator_num);
strBody.Append(string.Format("<tr><td align=\"center\">{0}.{1}</td> <td>{2}</td>", i.indicator_num, s.sub_indicator_num, s.sub_indicator_name));
for (int score = 1; score <= 7; score++)
{
if (score == target.evaluation_score)
{
strBody.Append(string.Format("<td align=\"center\">✓</td>"));
}
else
{
strBody.Append(string.Format("<td></td>"));
}
}
strBody.Append("</tr>");
}
//Overall result for each indicator
strBody.Append(string.Format("<tr><td></td><td align=\"right\"><b>สรุปความคิดเห็นรวม</b></td>"));
Self_evaluation_tiny_detail overallforcurrindicator = reportobject.indicator_self_evaluation_list.First(t => t.indicator_num == i.indicator_num).self_evaluation_list.First(u => u.sub_indicator_num == 0);
//Add overall score for all indicator's sum if evaluation_score is not 0
if (overallforcurrindicator.evaluation_score != 0)
{
overallscoresum += overallforcurrindicator.evaluation_score;
overalldivisor++;
}
for (int overallscore = 1; overallscore <= 7; overallscore++)
{
if (overallscore == overallforcurrindicator.evaluation_score)
{
strBody.Append(string.Format("<td align=\"center\">✓</td>"));
}
else
{
strBody.Append(string.Format("<td></td>"));
}
}
strBody.Append("</tr>");
}
strBody.Append("<tr><td></td><td align=\"center\"><b>สรุปผลการพิจารณาโดยรวมทั้งหมด</b></td><td colspan=\"7\" align=\"center\">");
if (overalldivisor != 0)
{
strBody.Append(string.Format("{0:N1}", (overallscoresum * 1.0 / overalldivisor)));
}
else
{
strBody.Append("--ไม่พบข้อมูล--");
}
strBody.Append("</td></tr></table>");
//END BODY SECTION
strBody.Append("</div></body></html>");
/*Force this content to be downloaded as a Word document*/
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
writer.Write(strBody);
writer.Flush();
stream.Position = 0;
result.Content = new StreamContent(stream);
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/msword");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
result.Content.Headers.ContentDisposition.FileName = "AUN-QA SAR.doc";
//return result;
return ResponseMessage(result);
}
else
return InternalServerError(new Exception(res.ToString()));
}
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> 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<IHttpActionResult> PostByCurriculumAcademic(oCurriculum_academic data)
{
object result = await datacontext.SelectWhereOrderByWithKeepYearSource(string.Format("aca_year=(select max(j.aca_year) from indicator as j where j.aca_year <= {0})", data.aca_year), "indicator_num", null,data.aca_year);
return Ok(result);
}
public async Task<IHttpActionResult> PostForQueryOverallResult(oCurriculum_academic data)
{
return Ok(await datacontext.Select(data));
}
public async Task<IHttpActionResult> PostForQueryQuestionareSet(oCurriculum_academic data)
{
return Ok(await datacontext.SelectWithDetail(data));
}
public async Task<IHttpActionResult> PostForQueryCommittee(oCurriculum_academic data)
{
datacontext.curri_id = data.curri_id;
datacontext.aca_year = data.aca_year;
return Ok(await datacontext.SelectWithBriefDetail());
}