public async Task<IHttpActionResult> PutForUpdateSubIndicator(oIndicator_sub_indicator_list data)
{
object result;
if (data.sub_indicator_list.Count == 0)
{
oSub_indicator sub_indicatorcontext = new oSub_indicator();
result = await sub_indicatorcontext.Delete(string.Format("aca_year = {0} and indicator_num = {1}", data.aca_year,data.indicator_num));
}
else
result = await datacontext.UpdateOnlySubIndicatorList(data.sub_indicator_list);
if (result == null)
return Ok();
else
return InternalServerError(new Exception(result.ToString()));
}
public async Task<object> SelectByAcademicYear(int year)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<oIndicator_sub_indicator_list> result = new List<oIndicator_sub_indicator_list>();
string temp1tablename = "#temp1";
string createtabletemp1 = string.Format("create table {0} (" +
"[{1}] INT NOT NULL," +
"[{2}] INT NOT NULL," +
"[{3}] INT NOT NULL," +
"[{4}] VARCHAR(2000) NULL," +
"PRIMARY KEY([{1}],[{2}],[{3}])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {4} VARCHAR(2000) COLLATE DATABASE_DEFAULT ",
temp1tablename, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM,
Sub_indicator.FieldName.SUB_INDICATOR_NUM,
Sub_indicator.FieldName.SUB_INDICATOR_NAME);
string insertintotemp1 = string.Format("insert into {0} " +
"select {1}, {2}, -1, null from {3} where not exists " +
"(select * from {4} where {3}.{1} = {4}.{5} and {3}.{2} = {4}.{6}) " +
"insert into {0} " +
"select * from {4} where {5} = {7} ",
temp1tablename, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, FieldName.TABLE_NAME,
Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.ACA_YEAR,
Sub_indicator.FieldName.INDICATOR_NUM,year);
string selectcmd = string.Format
("select {0}.{1},{0}.{2},{3},{4},{5},{6} " +
"from {0},{7} " +
"where {0}.{1} = {8} and {0}.{1} = {7}.{9} and {0}.{2}={7}.{10} " +
"order by {7}.{2},{7}.{5}",
FieldName.TABLE_NAME, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, FieldName.INDICATOR_NAME_T, FieldName.INDICATOR_NAME_E,
Sub_indicator.FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NAME,
temp1tablename, year, Sub_indicator.FieldName.ACA_YEAR, Sub_indicator.FieldName.INDICATOR_NUM);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} END",createtabletemp1,insertintotemp1,selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
oIndicator_sub_indicator_list curr = null;
indicator_num = -1;
foreach (DataRow item in data.Rows)
{
if(indicator_num != Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]))
{
curr = new oIndicator_sub_indicator_list
{
aca_year = aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
indicator_name_t = item.ItemArray[data.Columns[FieldName.INDICATOR_NAME_T].Ordinal].ToString(),
indicator_name_e = item.ItemArray[data.Columns[FieldName.INDICATOR_NAME_E].Ordinal].ToString(),
sub_indicator_list = new List<Sub_indicator>()
};
result.Add(curr);
indicator_num = curr.indicator_num;
}
if(Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]) != -1)
curr.sub_indicator_list.Add(new Sub_indicator
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.INDICATOR_NUM].Ordinal]),
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]),
sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
}
res.Close();
data.Dispose();
}
else
{
//Reserved for return error string
}
}
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> SelectByAcademicYear(int year)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <oIndicator_sub_indicator_list> result = new List <oIndicator_sub_indicator_list>();
string temp1tablename = "#temp1";
string createtabletemp1 = string.Format("create table {0} (" +
"[{1}] INT NOT NULL," +
"[{2}] INT NOT NULL," +
"[{3}] INT NOT NULL," +
"[{4}] VARCHAR(2000) NULL," +
"PRIMARY KEY([{1}],[{2}],[{3}])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {4} VARCHAR(2000) COLLATE DATABASE_DEFAULT ",
temp1tablename, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM,
Sub_indicator.FieldName.SUB_INDICATOR_NUM,
Sub_indicator.FieldName.SUB_INDICATOR_NAME);
string insertintotemp1 = string.Format("insert into {0} " +
"select {1}, {2}, -1, null from {3} where not exists " +
"(select * from {4} where {3}.{1} = {4}.{5} and {3}.{2} = {4}.{6}) " +
"insert into {0} " +
"select * from {4} where {5} = {7} ",
temp1tablename, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, FieldName.TABLE_NAME,
Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.ACA_YEAR,
Sub_indicator.FieldName.INDICATOR_NUM, year);
string selectcmd = string.Format
("select {0}.{1},{0}.{2},{3},{4},{5},{6} " +
"from {0},{7} " +
"where {0}.{1} = {8} and {0}.{1} = {7}.{9} and {0}.{2}={7}.{10} " +
"order by {7}.{2},{7}.{5}",
FieldName.TABLE_NAME, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, FieldName.INDICATOR_NAME_T, FieldName.INDICATOR_NAME_E,
Sub_indicator.FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NAME,
temp1tablename, year, Sub_indicator.FieldName.ACA_YEAR, Sub_indicator.FieldName.INDICATOR_NUM);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} END", createtabletemp1, insertintotemp1, selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
oIndicator_sub_indicator_list curr = null;
indicator_num = -1;
foreach (DataRow item in data.Rows)
{
if (indicator_num != Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]))
{
curr = new oIndicator_sub_indicator_list
{
aca_year = aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
indicator_name_t = item.ItemArray[data.Columns[FieldName.INDICATOR_NAME_T].Ordinal].ToString(),
indicator_name_e = item.ItemArray[data.Columns[FieldName.INDICATOR_NAME_E].Ordinal].ToString(),
sub_indicator_list = new List <Sub_indicator>()
};
result.Add(curr);
indicator_num = curr.indicator_num;
}
if (Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]) != -1)
{
curr.sub_indicator_list.Add(new Sub_indicator
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.ACA_YEAR].Ordinal]),
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.INDICATOR_NUM].Ordinal]),
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]),
sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
}
}
res.Close();
data.Dispose();
}
else
{
//Reserved for return error string
}
}
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);
}
