public async Task <IHttpActionResult> PutToSaveAllPresident(JObject data)
{
Curriculums_presidents_detail cpdata = data["old_object"].ToObject <Curriculums_presidents_detail>();
oPresident_curriculum datacontext = new oPresident_curriculum();
datacontext.aca_year = Convert.ToInt32(data["aca_year"]);
object result = await datacontext.UpdatePresidentData(cpdata);
if (result == null)
{
return(Ok());
}
else
{
return(InternalServerError(new Exception(result.ToString())));
}
}
public async Task <object> UpdatePresidentData(Curriculums_presidents_detail data)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
string deletefrompresident = string.Format("delete from {0} where {1} = {2} ", FieldName.TABLE_NAME,
FieldName.ACA_YEAR, ParameterName.ACA_YEAR
);
string insertintopresidentwithdeletefromcommittee = "";
foreach (KeyValuePair <string, Curri_with_pres_and_cand> kv in data.all_presidents)
{
foreach (Personnel_brief_detail p in kv.Value.presidents)
{
//DELETE FROM COMMITTEE
insertintopresidentwithdeletefromcommittee += string.Format("delete from {0} where {1} = '{2}' and {3} = {4} and {5} = {6} ",
Committee.FieldName.TABLE_NAME, FieldName.CURRI_ID, kv.Key, FieldName.ACA_YEAR, ParameterName.ACA_YEAR,
FieldName.TEACHER_ID, p.user_id);
//INSERT INTO PRESIDENT
insertintopresidentwithdeletefromcommittee += string.Format("insert into {0} values ({1},'{2}',{3}) ", FieldName.TABLE_NAME, p.user_id, kv.Key, ParameterName.ACA_YEAR);
}
}
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year));
d.iCommand.CommandText = string.Format("BEGIN {0} {1} END", deletefrompresident, insertintopresidentwithdeletefromcommittee);
try
{
await d.iCommand.ExecuteNonQueryAsync();
return(null);
}
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();
}
}
public async Task <object> SelectAllCurriculumsAndAllPresidents()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
Curriculums_presidents_detail result = new Curriculums_presidents_detail();
string selcurriculumdata = string.Format("select {0}.{1},{2} " +
"from {0},{3} " +
"where {0}.{1} = {3}.{4} and {5} = {6} ",
Cu_curriculum.FieldName.TABLE_NAME, Cu_curriculum.FieldName.CURRI_ID, Cu_curriculum.FieldName.CURR_TNAME,
Curriculum_academic.FieldName.TABLE_NAME, Curriculum_academic.FieldName.CURRI_ID,
Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
string selallpresident = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " +
"where {6} = {7} and {8} = {9} ",
FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC,
Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.TEACHER_ID,
FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
string selallteacherwithcurri = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " +
"where {6} = 1 and {0}.{7} = {5}.{8} " +
"and {9} in (select {10} from {11} where {12} = {13}) ",
User_curriculum.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC,
Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID,
User_curriculum.FieldName.USER_ID, User_list.FieldName.USER_ID,
User_curriculum.FieldName.CURRI_ID, Curriculum_academic.FieldName.CURRI_ID, Curriculum_academic.FieldName.TABLE_NAME,
Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year));
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} END", selcurriculumdata, selallpresident, selallteacherwithcurri);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
if (data.Columns.Count == 2)
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_curri_id.Add(curri_id);
result.all_presidents[curri_id] = new Curri_with_pres_and_cand
{
curri_tname = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString()
};
}
}
else if (data.Columns.Count == 7)
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_presidents[curri_id].presidents.Add(new Personnel_brief_detail
{
tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(),
pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(),
user_id = item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString()
});
}
}
else
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_presidents[curri_id].candidates.Add(new Personnel_brief_detail
{
tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(),
pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(),
user_id = item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal].ToString()
});
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
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);
}
