public async Task <ModelMenuB1Data> GetProjectNumberWithDataAsync(string project_number)
{
string sql = "SELECT A.project_id, A.accept_type, A.initial_result, A.acronyms, " +
"C.full_name AS project_head_name, A.project_key_number, " +
"B.project_head, B.faculty_name, B.project_name_thai, B.project_name_eng, " +
"D.name_thai AS position_name_thai, E.name_thai AS accepttype_name_thai, " +
"F.name_thai AS initial_name_thai, A.file_download_name, " +
"A.round_of_meeting, A.year_of_meeting, A.meeting_date as meeting_set_date, " +
"G.meeting_date as conclusion_date " +
"FROM Doc_MenuB1 A " +
"INNER JOIN Doc_MenuA1 B ON A.project_id = B.doc_id " +
"INNER JOIN RegisterUser C ON B.project_head = C.register_id " +
"INNER JOIN MST_Position D ON C.position = D.id " +
"LEFT JOIN MST_AcceptType E ON A.accept_type = E.id " +
"LEFT JOIN MST_InitialResult F ON A.initial_result = F.id " +
"INNER JOIN Doc_MenuC3 G ON A.project_key_number = G.agenda_3_project_number " +
"WHERE A.IsClosed='0' AND A.project_key_number='" + project_number + "'";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(sql, conn))
{
SqlDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
ModelMenuB1Data e = new ModelMenuB1Data();
while (await reader.ReadAsync())
{
e.accepttype = reader["accept_type"].ToString();
e.accepttypenamethai = reader["accepttype_name_thai"].ToString();
e.projecthead = reader["project_head"].ToString();
e.projectheadname = reader["project_head_name"].ToString();
e.projectid = reader["project_id"].ToString();
e.projectnamethai = reader["project_name_thai"].ToString();
e.projectnameeng = reader["project_name_eng"].ToString();
e.positionnamethai = "หัวหน้าโครงการ";// reader["position_name_thai"].ToString();
e.facultyname = reader["faculty_name"].ToString();
e.acronyms = reader["acronyms"].ToString();
e.initialresult = reader["initial_result"].ToString();
e.initialnamethai = reader["initial_name_thai"].ToString();
e.filedownloadname = reader["file_download_name"].ToString();
e.projectkeynumber = reader["project_key_number"].ToString();
e.roundofmeeting = Convert.ToInt32(reader["round_of_meeting"]);
e.yearofmeeting = Convert.ToInt32(reader["year_of_meeting"]);
e.meetingsetdate = Convert.ToDateTime(reader["meeting_set_date"]).ToString("dd/MM/yyyy");
e.conclusiondate = Convert.ToDateTime(reader["conclusion_date"]).ToString("dd/MM/yyyy");
}
return(e);
}
}
conn.Close();
}
return(null);
}
public async Task <IActionResult> GetProjectNumberWithData(string ProjectNumber)
{
ModelMenuB1Data e = await _IDocMenuBService.GetProjectNumberWithDataAsync(ProjectNumber);
if (e != null)
{
return(Ok(e));
}
else
{
return(BadRequest());
}
}
