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