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));
}
