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