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