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<object> SelectByIndicatorAndCurriculum(oIndicator inddata, string curri_id)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oEvidence> result = new List<oEvidence>();
d.iCommand.CommandText = string.Format("select * from {0} " +
"where {1} = {2} and {3} = '{4}' and {5} = {6} order by {7} ",
FieldName.TABLE_NAME,FieldName.INDICATOR_NUM,inddata.indicator_num,FieldName.CURRI_ID,
curri_id,FieldName.ACA_YEAR,inddata.aca_year,FieldName.EVIDENCE_REAL_CODE);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oEvidence
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
evidence_code = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVIDENCE_CODE].Ordinal]),
evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(),
file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(),
secret = Convert.ToChar(item.ItemArray[data.Columns[FieldName.SECRET].Ordinal]),
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]),
//DANGER NULLABLE ZONE
primary_evidence_num = item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]) : 0,
evidence_real_code = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVIDENCE_REAL_CODE].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal])
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return ex.Message;
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return result;
}
public async Task<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 <object> SelectByIndicatorWithKeepAcaYearSource(oIndicator inddata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <oSub_indicator> result = new List <oSub_indicator>();
d.iCommand.CommandText = string.Format("select * from {0} where {1} = (select max(j.{1}) from {0} as j where j.{1} <= {2}) and {3} = {4}",
FieldName.TABLE_NAME, FieldName.ACA_YEAR, inddata.aca_year, FieldName.INDICATOR_NUM, inddata.indicator_num);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oSub_indicator
{
aca_year = inddata.aca_year,
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]),
sub_indicator_name = item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
public async Task<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<object> SelectByIndicatorWithKeepAcaYearSource(oIndicator inddata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oSub_indicator> result = new List<oSub_indicator>();
d.iCommand.CommandText = string.Format("select * from {0} where {1} = (select max(j.{1}) from {0} as j where j.{1} <= {2}) and {3} = {4}",
FieldName.TABLE_NAME, FieldName.ACA_YEAR,inddata.aca_year,FieldName.INDICATOR_NUM,inddata.indicator_num);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new oSub_indicator
{
aca_year = inddata.aca_year,
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]),
sub_indicator_name = item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return ex.Message;
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return result;
}
public async Task<object> 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;
}
public async Task<object> SelectWithDetail(oIndicator inddata,string curri_id_param)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Primary_evidence_detail> result = new List<Primary_evidence_detail>();
//Retrieve already define primary evidence
d.iCommand.CommandText =
string.Format("select {0}.{1},{0}.{2},{3},{4},{5},{6},{7} " +
"from {0},{8} " +
"where {0}.{1} = {8}.{9} "+
"and {0}.{2} = '{10}' and {5} = {11} and {6} = {12}",
Primary_evidence_status.FieldName.TABLE_NAME, Primary_evidence_status.FieldName.PRIMARY_EVIDENCE_NUM,
Primary_evidence_status.FieldName.CURRI_ID, Primary_evidence_status.FieldName.TEACHER_ID,
Primary_evidence_status.FieldName.STATUS,FieldName.ACA_YEAR,FieldName.INDICATOR_NUM,
FieldName.EVIDENCE_NAME,FieldName.TABLE_NAME,FieldName.PRIMARY_EVIDENCE_NUM,curri_id_param,
inddata.aca_year,inddata.indicator_num
);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new Primary_evidence_detail
{
primary_evidence_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
status = Convert.ToChar(item.ItemArray[data.Columns[Primary_evidence_status.FieldName.STATUS].Ordinal]),
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[Primary_evidence_status.FieldName.TEACHER_ID].Ordinal])
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
//Retrieve primary evidence which define by admin but not assign any responsible teacher yet
res.Close();
d.iCommand.CommandText =
string.Format("select * from {0} where {1} = '0' and {2} = {3} and {4} = {5} " +
"and {0}.{6} not IN " +
"(select e.{7} from {8} as e where e.{9} = '{13}') and " +
"not exists(select * from {10} where {0}.{6} = {10}.{11} and {12} = '{13}')",
FieldName.TABLE_NAME,FieldName.CURRI_ID,FieldName.ACA_YEAR,inddata.aca_year,
FieldName.INDICATOR_NUM,inddata.indicator_num,FieldName.PRIMARY_EVIDENCE_NUM,
Exclusive_curriculum_evidence.FieldName.PRIMARY_EVIDENCE_NUM,
Exclusive_curriculum_evidence.FieldName.TABLE_NAME,
Exclusive_curriculum_evidence.FieldName.CURRI_ID,
Primary_evidence_status.FieldName.TABLE_NAME,
Primary_evidence_status.FieldName.PRIMARY_EVIDENCE_NUM,
Primary_evidence_status.FieldName.CURRI_ID,curri_id_param
);
res = d.iCommand.ExecuteReader();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new Primary_evidence_detail
{
primary_evidence_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(),
curri_id = curri_id_param,
status = '6',
teacher_id = 0
});
}
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;
}
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> SelectWithDetail(oIndicator inddata, string curri_id_param)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Primary_evidence_detail> result = new List <Primary_evidence_detail>();
//Retrieve already define primary evidence
d.iCommand.CommandText =
string.Format("select {0}.{1},{0}.{2},{3},{4},{5},{6},{7} " +
"from {0},{8} " +
"where {0}.{1} = {8}.{9} " +
"and {0}.{2} = '{10}' and {5} = {11} and {6} = {12}",
Primary_evidence_status.FieldName.TABLE_NAME, Primary_evidence_status.FieldName.PRIMARY_EVIDENCE_NUM,
Primary_evidence_status.FieldName.CURRI_ID, Primary_evidence_status.FieldName.TEACHER_ID,
Primary_evidence_status.FieldName.STATUS, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM,
FieldName.EVIDENCE_NAME, FieldName.TABLE_NAME, FieldName.PRIMARY_EVIDENCE_NUM, curri_id_param,
inddata.aca_year, inddata.indicator_num
);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new Primary_evidence_detail
{
primary_evidence_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
status = Convert.ToChar(item.ItemArray[data.Columns[Primary_evidence_status.FieldName.STATUS].Ordinal]),
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[Primary_evidence_status.FieldName.TEACHER_ID].Ordinal])
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
//Retrieve primary evidence which define by admin but not assign any responsible teacher yet
res.Close();
d.iCommand.CommandText =
string.Format("select * from {0} where {1} = '0' and {2} = {3} and {4} = {5} " +
"and {0}.{6} not IN " +
"(select e.{7} from {8} as e where e.{9} = '{13}') and " +
"not exists(select * from {10} where {0}.{6} = {10}.{11} and {12} = '{13}')",
FieldName.TABLE_NAME, FieldName.CURRI_ID, FieldName.ACA_YEAR, inddata.aca_year,
FieldName.INDICATOR_NUM, inddata.indicator_num, FieldName.PRIMARY_EVIDENCE_NUM,
Exclusive_curriculum_evidence.FieldName.PRIMARY_EVIDENCE_NUM,
Exclusive_curriculum_evidence.FieldName.TABLE_NAME,
Exclusive_curriculum_evidence.FieldName.CURRI_ID,
Primary_evidence_status.FieldName.TABLE_NAME,
Primary_evidence_status.FieldName.PRIMARY_EVIDENCE_NUM,
Primary_evidence_status.FieldName.CURRI_ID, curri_id_param
);
res = d.iCommand.ExecuteReader();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new Primary_evidence_detail
{
primary_evidence_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(),
curri_id = curri_id_param,
status = '6',
teacher_id = 0
});
}
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);
}
