public async Task <IList <ResultCommentNote> > GetResultNoteHome1Async(string project_number, string user_id) { ModelPermissionPage user_permission = await _IRegisterUserRepository.GetPermissionPageAsync(user_id, "M001"); string sql = "SELECT A.doc_id, ROW_NUMBER() OVER(PARTITION BY A.project_number ORDER BY A.doc_id ASC) as seq, A.doc_date, " + "A.assigner_code, (B.first_name + B.full_name) as full_name, A.comment_consider, C.name_thai, (D.name_thai + ' ' + D.name_thai_sub) as approval_name_thai " + "FROM Doc_MenuC2 A " + "LEFT OUTER JOIN RegisterUser B " + "ON A.assigner_code = B.register_id " + "LEFT OUTER JOIN MST_Safety C " + "ON A.safety_type = C.id " + "LEFT OUTER JOIN MST_ApprovalType D " + "ON A.approval_type = D.id " + "WHERE 1=1 " + (user_permission.groupcode == "G002" ? " AND assigner_code='" + user_permission.registerid + "' " : "") + "AND A.project_number='" + project_number + "' " + "ORDER BY A.doc_id ASC"; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand(sql, conn)) { SqlDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { IList <ResultCommentNote> e = new List <ResultCommentNote>(); while (await reader.ReadAsync()) { ResultCommentNote item = new ResultCommentNote(); item.docid = Convert.ToInt32(reader["doc_id"]); item.xseq = "ลำดับที่:"; item.xdate = " วันที่:"; item.xassignName = "ชื่อกรรมการ:"; item.xriskName = "ประเภทความเสี่ยง:"; item.xapprovalName = "ความเห็นการรับรอง:"; item.xcommentDetail = "ความเห็นประกอบการพิจารณา:"; item.commentDetail = reader["comment_consider"].ToString(); item.seq = Convert.ToInt32(reader["seq"]).ToString(); item.date = Convert.ToDateTime(reader["doc_date"]).ToString("dd/MM/yyyy"); item.assignName = reader["full_name"].ToString(); item.riskName = reader["name_thai"].ToString(); item.approvalName = reader["approval_name_thai"].ToString(); item.commentDetail = reader["comment_consider"].ToString(); e.Add(item); } return(e); } } conn.Close(); } return(null); }
public async Task <IActionResult> GetPermissionPage(string RegisterId, string PageCode) { ModelPermissionPage e = await _IRegisterUserService.GetPermissionPageAsync(RegisterId, PageCode); if (e != null) { return(Ok(e)); } return(BadRequest()); }
public async Task <ModelPermissionPage> GetPermissionPageAsync(string RegisterId, string PageCode) { string userid = Encoding.UTF8.GetString(Convert.FromBase64String(RegisterId)); string sql = "SELECT A.register_id, (A.first_name + A.full_name) as full_name, D.* " + "FROM [dbo].[RegisterUser] A " + "INNER JOIN[dbo].[SYS_UserRole] B ON A.character = B.code " + "INNER JOIN[dbo].[SYS_UserGroup] C ON B.usergroup = C.code " + "INNER JOIN[dbo].[SYS_Permission] D ON C.code = D.user_group_code " + "WHERE A.register_id='" + userid + "' AND D.menu_page_code = '" + PageCode + "' AND A.IsActive=1"; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand(sql, conn)) { SqlDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { ModelPermissionPage item = new ModelPermissionPage(); while (await reader.ReadAsync()) { item.registerid = reader["register_id"].ToString(); item.fullname = reader["full_name"].ToString(); item.groupcode = reader["user_group_code"].ToString(); item.pagecode = reader["menu_page_code"].ToString(); item.view = (bool)reader["pm_view"]; item.insert = (bool)reader["pm_insert"]; item.edit = (bool)reader["pm_update"]; item.print = (bool)reader["pm_print"]; item.alldata = (bool)reader["pm_all_data"]; } return(item); } } conn.Close(); } return(null); }
public async Task <IList <ModelMenuHome1ReportData> > GetAllReportDataHome1Async(ModelMenuHome1_InterfaceData search_data) { ModelPermissionPage user_permission = await _IRegisterUserRepository.GetPermissionPageAsync(search_data.userid, "M001"); string sql = "SELECT A.*, B.faculty, (B.first_name + ' ' + B.full_name) AS project_head_name " + "FROM Transaction_Document A " + "INNER JOIN RegisterUser B ON A.project_head = B.register_id " + "WHERE 1=1 "; if (user_permission != null && user_permission.alldata == false) { string userid = Encoding.UTF8.GetString(Convert.FromBase64String(search_data.userid)); sql += " AND (A.project_by='" + userid + "' OR A.committee_code_array LIKE '%" + userid + "%') "; } if (search_data != null) { if (!string.IsNullOrEmpty(search_data.year) && search_data.year.ToLower() != "all") { sql += " AND A.year ='" + search_data.year + "'"; } if (!string.IsNullOrEmpty(search_data.projecthead) && search_data.projecthead != "YWxs") { search_data.projecthead = Encoding.UTF8.GetString(Convert.FromBase64String(search_data.projecthead)); sql += " AND A.project_head ='" + search_data.projecthead + "'"; } if (!string.IsNullOrEmpty(search_data.accepttype) && search_data.accepttype.ToLower() != "all") { sql += " AND A.project_type ='" + search_data.accepttype + "'"; } if (!string.IsNullOrEmpty(search_data.faculty) && search_data.faculty.ToLower() != "all") { sql += " AND B.faculty ='" + search_data.faculty + "'"; } if (!string.IsNullOrEmpty(search_data.acronyms) && search_data.acronyms.ToLower() != "all") { sql += " AND A.acronyms ='" + search_data.acronyms + "'"; } if (!string.IsNullOrEmpty(search_data.risk) && search_data.risk.ToLower() != "all") { sql += " AND A.risk_type LIKE'%" + search_data.risk + "%'"; } } sql += " ORDER BY A.trans_id ASC "; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand(sql, conn)) { SqlDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { int row_count = 1; IList <ModelMenuHome1ReportData> e = new List <ModelMenuHome1ReportData>(); while (await reader.ReadAsync()) { ModelMenuHome1ReportData item = new ModelMenuHome1ReportData(); item.project_request_id = reader["project_request_id"].ToString(); item.project_name_thai = reader["project_name_thai"].ToString(); item.project_name_eng = reader["project_name_eng"].ToString(); item.project_number = reader["project_number"].ToString(); item.project_head_name = reader["project_head_name"].ToString(); item.acronyms = reader["acronyms"].ToString(); item.risk_type = reader["risk_type"].ToString(); item.delivery_online_date = reader["delivery_online_date"].ToString(); item.review_request_date = reader["review_request_date"].ToString(); item.result_doc_review = reader["result_doc_review"].ToString(); item.committee_assign_date = reader["committee_assign_date"].ToString(); item.committee_name_array = (user_permission.groupcode == "G002" ? user_permission.fullname : reader["committee_name_array"].ToString()); string comment_date = ""; if (user_permission.groupcode == "G002") { comment_date = await GetCommentDataAsync(reader["project_number"].ToString(), user_permission.groupcode, search_data.userid); } item.committee_comment_date = (user_permission.groupcode == "G002" ? comment_date : reader["committee_comment_date"].ToString()); item.meeting_date = reader["meeting_date"].ToString(); item.meeting_approval_date = reader["meeting_approval_date"].ToString(); string consider_result = reader["consider_result"].ToString() + (!string.IsNullOrEmpty(reader["consider_result"].ToString()) ? " (" + reader["safety_type"].ToString() + ")" : ""); item.consider_result = (reader["safety_type"].ToString() == "5" ? "-" : consider_result); item.alert_date = reader["alert_date"].ToString(); item.request_edit_meeting_date = reader["request_edit_meeting_date"].ToString(); ///// item.request_edit_date = reader["request_edit_date"].ToString(); item.report_status_date = reader["report_status_date"].ToString(); item.certificate_expire_date = reader["certificate_expire_date"].ToString(); item.request_renew_date = reader["request_renew_date"].ToString(); item.close_project_date = reader["close_project_date"].ToString(); ///// item.print_certificate_date = reader["print_certificate_date"].ToString(); ///// e.Add(item); row_count++; } return(e); } } conn.Close(); } return(null); }
private async Task <ModelMenuD1> GetMenuD1DataEditAsync(string ProjectNumber, string userid, ModelPermissionPage permission) { string user_id = Encoding.UTF8.GetString(Convert.FromBase64String(userid)); string sql = "SELECT TOP(1) A.*, B.name_thai as accept_result_name, " + "(CASE WHEN A.acceptCondition = 1 THEN 'แบบปีต่อปี' ELSE 'ไม่มีวันหมอายุ' END) as accept_condition_name " + "FROM Doc_MenuD1 A " + "LEFT OUTER JOIN MST_AcceptResult B ON A.acceptResult = B.id " + "WHERE project_number = '" + ProjectNumber + "' ORDER BY doc_id DESC"; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand(sql, conn)) { SqlDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { ModelMenuD1 e = new ModelMenuD1(); while (await reader.ReadAsync()) { e.docid = reader["doc_id"].ToString(); e.projectnumber = reader["project_number"].ToString(); e.projectheadname = reader["project_head_name"].ToString(); e.facultyname = reader["faculty_name"].ToString(); e.advisorsnamethai = reader["advisorsNameThai"].ToString(); e.acceptprojectno = reader["acceptProjectNo"].ToString(); e.projectnamethai = reader["project_name_thai"].ToString(); e.projectnameeng = reader["project_name_eng"].ToString(); e.accepttypenamethai = reader["accept_type_name"].ToString(); e.acceptresult = Convert.ToInt16(reader["acceptResult"]); e.acceptresultname = reader["accept_result_name"].ToString(); e.acceptcondition = Convert.ToInt16(reader["acceptCondition"]); e.acceptconditionname = reader["accept_condition_name"].ToString(); e.acceptdate = Convert.ToDateTime(reader["AcceptDate"]).ToString("dd/MM/yyyy"); e.createby = reader["create_by"].ToString(); } e.listRenewDate = new List <ModelMenuD1RenewTable>(); e.listRenewDate = await GetListRenewDateAsync(ProjectNumber); //Default Edit False e.editenable = false; if (permission.edit == true) { if (user_id == e.createby) { e.editenable = true; } } return(e); } } conn.Close(); } return(null); }
private async Task <ModelMenuA3> GetMenuA3DataEditAsync(string ProjectNumber, string userid, ModelPermissionPage permission) { string user_id = Encoding.UTF8.GetString(Convert.FromBase64String(userid)); string sql = "SELECT TOP(1)* FROM Doc_MenuA3 " + "WHERE project_number='" + ProjectNumber + "' ORDER BY doc_id DESC"; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand(sql, conn)) { SqlDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { ModelMenuA3 e = new ModelMenuA3(); while (await reader.ReadAsync()) { e.docid = reader["doc_id"].ToString(); e.projectnumber = reader["project_number"].ToString(); e.projectheadname = reader["project_head_name"].ToString(); e.facultyname = reader["faculty_name"].ToString(); e.projectnamethai = reader["project_name_thai"].ToString(); e.projectnameeng = reader["project_name_eng"].ToString(); e.accepttypenamethai = reader["accept_type_name"].ToString(); e.conclusiondate = Convert.ToDateTime(reader["conclusion_date"]).ToString("dd/MM/yyyy"); e.file1name = reader["file1name"].ToString(); e.createby = reader["create_by"].ToString(); //Default Edit False e.editenable = false; if (permission.edit == true) { if (user_id == reader["create_by"].ToString()) { e.editenable = true; } } } return(e); } } conn.Close(); } return(null); }
private async Task <ModelMenuC1> GetMenuC1DataEditAsync(string project_number, string RegisterId, ModelPermissionPage permission) { string sql = "SELECT TOP(1)* , (b.first_name + B.full_name) AS assigner_name, C.committee_comment_date " + "FROM Doc_MenuC1 A " + "LEFT OUTER JOIN RegisterUser B ON A.assigner_code = B.register_id " + "LEFT OUTER JOIN Transaction_Document C ON A.project_number = C.project_number " + "WHERE A.project_number='" + project_number + "' ORDER BY A.doc_id DESC"; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand(sql, conn)) { SqlDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { ModelMenuC1 e = new ModelMenuC1(); while (await reader.ReadAsync()) { string assigner_code = System.Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(reader["assigner_code"].ToString())); e.docid = reader["doc_id"].ToString(); e.assignercode = assigner_code; e.assignername = reader["assigner_name"].ToString(); e.positionname = reader["position_name"].ToString(); e.accepttype = reader["accept_type"].ToString(); e.projectnumber = reader["project_number"].ToString(); e.projectheadname = reader["project_head_name"].ToString(); e.facultyname = reader["faculty_name"].ToString(); e.projectnamethai = reader["project_name_thai"].ToString(); e.projectnameeng = reader["project_name_eng"].ToString(); e.roundofmeeting = reader["round_of_meeting"].ToString(); e.yearofmeeting = reader["year_of_meeting"].ToString(); e.meetingdate = Convert.ToDateTime(reader["meeting_date"]).ToString("dd/MM/yyyy"); e.createby = reader["create_by"].ToString(); //Default Edit False e.editenable = false; if (permission.edit == true) { if (string.IsNullOrEmpty(reader["committee_comment_date"].ToString())) { string user_id = Encoding.UTF8.GetString(Convert.FromBase64String(RegisterId)); if (user_id == reader["create_by"].ToString()) { e.editenable = true; } } } e.boardcodearray = JsonConvert.DeserializeObject <List <ModelSelectOption> >(reader["board_code_array"].ToString()); e.speciallistcodearray = JsonConvert.DeserializeObject <List <ModelSelectOption> >(reader["speciallist_code_array"].ToString()); } return(e); } } conn.Close(); } return(null); }
private async Task <ModelMenuC2> GetMenuC2DataEditAsync(int docid, string userid, ModelPermissionPage permission) { string user_id = Encoding.UTF8.GetString(Convert.FromBase64String(userid)); string sql = "SELECT TOP(1) A.*, (D.first_name + ' ' + D.full_name) AS assigner_name, B.name_thai AS safety_type_name, " + "(C.name_thai + ' ' + C.name_thai_sub) AS approval_type_name, E.meeting_date " + "FROM [dbo].[Doc_MenuC2] A " + "LEFT OUTER JOIN[dbo].[MST_Safety] B ON A.safety_type = B.id " + "LEFT OUTER JOIN[dbo].[MST_ApprovalType] C ON A.approval_type = C.id " + "LEFT OUTER JOIN[dbo].[RegisterUser] D ON A.assigner_code = D.register_id " + "LEFT OUTER JOIN Transaction_Document E ON A.project_number = E.project_number " + "WHERE A.doc_id='" + docid + "' " + (permission.alldata == true ? "" : " AND A.create_by = '" + user_id + "'") + "ORDER BY A.doc_id DESC"; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand(sql, conn)) { SqlDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { ModelMenuC2 e = new ModelMenuC2(); while (await reader.ReadAsync()) { string assigner_code = System.Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(reader["assigner_code"].ToString())); e.docid = reader["doc_id"].ToString(); e.assignercode = assigner_code; e.assignername = reader["assigner_name"].ToString(); e.positionname = reader["position_name"].ToString(); e.assignerseq = reader["assigner_seq"].ToString(); e.projectnumber = reader["project_number"].ToString(); e.projectheadname = reader["project_head_name"].ToString(); e.facultyname = reader["faculty_name"].ToString(); e.projectnamethai = reader["project_name_thai"].ToString(); e.projectnameeng = reader["project_name_eng"].ToString(); e.safetytype = reader["safety_type"].ToString(); e.safetytypename = reader["safety_type_name"].ToString(); e.approvaltype = reader["approval_type"].ToString(); e.approvaltypename = reader["approval_type_name"].ToString(); e.commentconsider = reader["comment_consider"].ToString(); e.roundofmeeting = reader["round_of_meeting"].ToString(); e.yearofmeeting = reader["year_of_meeting"].ToString(); e.createby = reader["create_by"].ToString(); //Default Edit False e.editenable = false; if (permission.edit == true) { if (string.IsNullOrEmpty(reader["meeting_date"].ToString())) { if (user_id == reader["create_by"].ToString()) { e.editenable = true; } } } } return(e); } } conn.Close(); } return(null); }