public async Task <IHttpActionResult> PutForUpdateIndicatorSubIndicator(List <oIndicator_sub_indicator_list> list) { if (list.Count == 0) { return(BadRequest("กรุณาระบุปีการศึกษาที่ตัวบ่งชี้ที่สร้างจะเริ่มมีผลให้เป็นค่าที่เหมาะสม")); } object result; if (list.Count == 1 && list.First().indicator_name_t == null) { oIndicator indicatorcontext = new oIndicator(); result = await indicatorcontext.Delete(string.Format("aca_year = {0}", list.First().aca_year)); } else { result = await datacontext.UpdateEntireList(list); } if (result == null) { return(Ok()); } else { return(BadRequest(result.ToString())); } }
public async Task <IHttpActionResult> PostToQueryPrimaryEvidenceDetailByPresidentCurri(JObject data) { oIndicator inddata = new oIndicator { aca_year = Convert.ToInt32(data["aca_year"]), indicator_num = Convert.ToInt32(data["indicator_num"]) }; return(Ok(await datacontext.SelectWithDetail(inddata, data["curri_id"].ToString()))); }
public async Task <IHttpActionResult> PostByIndicatorAndCurriculumWithGetName(JObject obj) { oIndicator data = new oIndicator { aca_year = Convert.ToInt32(obj["aca_year"]), indicator_num = Convert.ToInt32(obj["indicator_num"]) }; return(Ok(await datacontext.SelectByIndicatorAndCurriculumWithTName(data, obj["curri_id"].ToString()))); }
public async Task <IHttpActionResult> PostToQuerySelfEvaluationData(JObject obj) { oSelf_evaluation_sub_indicator_name datacontext = new oSelf_evaluation_sub_indicator_name(); oIndicator data = new oIndicator { aca_year = Convert.ToInt32(obj["aca_year"]), indicator_num = Convert.ToInt32(obj["indicator_num"]) }; object result = await datacontext.SelectByIndicatorAndCurriculum(data, obj["curri_id"].ToString()); return(Ok(result)); }
public async Task <IHttpActionResult> PostToQueryPrimaryEvidenceDetailByIndicator(oIndicator data) { return(Ok(await datacontext.SelectWhere(string.Format("indicator_num = '{0}' and aca_year = '{1}' and curri_id = '0'", data.indicator_num, data.aca_year)))); }
public async Task <object> SelectByIndicatorAndCurriculum(oIndicator inddata, string curri_id) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <oSelf_evaluation_sub_indicator_name> result = new List <oSelf_evaluation_sub_indicator_name>(); string temp90tablename = "#temp90"; string createtabletemp90 = string.Format("CREATE TABLE {0} (" + "[row_num] INT IDENTITY(1, 1) not null," + "[{1}] INT NULL," + "[{2}] INT NULL," + "[{3}] INT NULL," + "[{4}] CHAR NULL," + "[{5}] DATE NULL," + "[{6}] TIME(0) NULL," + "[{7}] {10} NULL," + "[{8}] INT NULL," + "[{9}] VARCHAR(2000) NULL," + "PRIMARY KEY([row_num])) " + "alter table {0} " + "alter column [{4}] CHAR collate database_default " + "alter table {0} " + "alter column [{7}] {10} collate database_default " + "alter table {0} " + "alter column [{9}] VARCHAR(2000) collate database_default ", temp90tablename, FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, FieldName.TEACHER_ID, FieldName.EVALUATION_SCORE, FieldName.DATE, FieldName.TIME, FieldName.CURRI_ID, FieldName.ACA_YEAR, Sub_indicator.FieldName.SUB_INDICATOR_NAME, DBFieldDataType.CURRI_ID_TYPE); string insertintotemp90_1 = string.Format("insert into {0} " + "select {1}.*, {2} " + "from {1}, {3} " + "where {4} = '{5}' and {1}.{6} = {7} " + "and {1}.{8} = {9} " + "and {3}.{10} = {1}.{8} " + "and {3}.{11} = {1}.{12} " + "and {3}.{13} = (select max({13}) from {3} where {13} <= {7}) ", temp90tablename, FieldName.TABLE_NAME, Sub_indicator.FieldName.SUB_INDICATOR_NAME, Sub_indicator.FieldName.TABLE_NAME, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, inddata.aca_year, FieldName.INDICATOR_NUM, inddata.indicator_num, Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.ACA_YEAR); string insertintotemp90_2 = string.Format("insert into {0} " + "select {1},{2},1 as teacher_id,0 as evalscore,null,null,'{3}',{4},{5} " + "from {6} " + "where {1} = {7} and {8} = (select max({8}) from {6} where {8} <= {4}) " + "and not exists (select * from {9} where " + "{10} = '{3}' and {9}.{11} = {4} " + "and {9}.{12} = {7} " + "and {9}.{13} = {6}.{2}) ", temp90tablename, Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM, curri_id, inddata.aca_year, Sub_indicator.FieldName.SUB_INDICATOR_NAME, Sub_indicator.FieldName.TABLE_NAME, inddata.indicator_num, Sub_indicator.FieldName.ACA_YEAR, FieldName.TABLE_NAME, FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM); string insertintotemp90_3truecase = string.Format("insert into {0} " + "select {1}.*,{2} " + "from {1},{3} " + "where {4} = 0 and {1}.{5} = {6} " + "and {7} = '{8}' and {1}.{9} = {10} " + "and {3}.{11} = {1}.{5} " + "and {3}.{12} = " + "(select max({12}) from {3} where {12} <= {10}) ", temp90tablename, FieldName.TABLE_NAME, Indicator.FieldName.INDICATOR_NAME_E, Indicator.FieldName.TABLE_NAME, FieldName.SUB_INDICATOR_NUM, FieldName.INDICATOR_NUM, inddata.indicator_num, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, inddata.aca_year, Indicator.FieldName.INDICATOR_NUM, Indicator.FieldName.ACA_YEAR); string insertintotemp90_3falsecase = string.Format("insert into {0} " + "select {1}, 0, 1, 0, NULL, NULL, '{2}', {3}, {4} " + "from {5} " + "where {6} = {1} " + "and {5}.{7} = (select max({7}) from {5} where {7} <= {3}) ", temp90tablename, inddata.indicator_num, curri_id, inddata.aca_year, Indicator.FieldName.INDICATOR_NAME_E, Indicator.FieldName.TABLE_NAME, Indicator.FieldName.INDICATOR_NUM, Indicator.FieldName.ACA_YEAR ); string insertintotemp90_3 = string.Format("if exists (select * from {0} where {1} = 0 and {2} = {3} and {4} = '{5}' and {6} = {7}) " + " BEGIN " + insertintotemp90_3truecase + " END " + " else " + " BEGIN " + insertintotemp90_3falsecase + " END ", FieldName.TABLE_NAME, FieldName.SUB_INDICATOR_NUM, FieldName.INDICATOR_NUM, inddata.indicator_num, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, inddata.aca_year); string selectcmd = string.Format("select * from {0} order by {1} ", temp90tablename, FieldName.SUB_INDICATOR_NUM); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} END", createtabletemp90, insertintotemp90_1, insertintotemp90_2, insertintotemp90_3, 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 h = "", m = "", readdate; readdate = item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(); if (readdate != "") { 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.Add(new oSelf_evaluation_sub_indicator_name { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), 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]), evaluation_score = item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal]) : 0, teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString()), sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_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 }); } else { result.Add(new oSelf_evaluation_sub_indicator_name { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), 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]), evaluation_score = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal]), teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString()), sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString() }); } } data.Dispose(); } else { } 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); }
//Retrieve sub_indicator by indicator data public async Task <IHttpActionResult> PostByIndicator(oIndicator data) { object result = await datacontext.SelectByIndicatorWithKeepAcaYearSource(data); return(Ok(result)); }