public async Task<bool> MailTemplate3Async(ModelMenuC1 model, string rptBase64)
{
string mail_subject = "คณะกรรมการควบคุมความปลอดภัยทางชีวภาพ ขอความอนุเคราะห์จากท่านได้อ่านและพิจารณาการรับรองโครงการ ";
IList<ModelMail_Template3> email_to = await GetCommittee_MailTemplate3_Async(model);
if (email_to != null && email_to.Count > 0)
{
foreach (var item in email_to)
{
string mail_body = "<h3>เรียน " + item.fullname + "</h3>" + Environment.NewLine +
"<h3>" + mail_subject + "</h3>" + Environment.NewLine +
"</br>" + Environment.NewLine +
"<p>หมายเลขโครงการ " + model.projectnumber + "</p>" + Environment.NewLine +
"<p>" + model.projectnamethai + "</p>" + Environment.NewLine +
"<p>เพื่อขอความอนุเคราะห์จากท่านได้อ่านและพิจารณาการรับรองโครงการดังกล่าว ตามบันทึกแนบนี้ ซึ่งท่านสามารถล็อกอินเข้า “ระบบรับรองโครงการ” เพื่อดาวน์โหลดเอกสารที่เกี่ยวข้องได้ตั้งแต่บัดนี้เป็นต้นไป</p>" + Environment.NewLine +
"</br>" + Environment.NewLine +
"<h3>คณะกรรมการควบคุมความปลอดภัยทางชีวภาพ</h3>" + Environment.NewLine +
"<h3>มหาวิทยาลัยนเรศวร</h3>";
await _EmailHelper.SentGmail(item.email, "NUIBC : ขอความอนุเคราะห์อ่านโครงการ", mail_body, rptBase64);
}
return true;
}
else return false;
}
public async Task <IActionResult> UpdateDocMenuC1Edit([FromBody] ModelMenuC1 model)
{
IActionResult _result = BadRequest();
ModelResponseC1Message e = await _IDocMenuCService.UpdateDocMenuC1EditAsync(model);
if (e.Status == true)
{
_result = Ok(e);
try
{
await _IMailTemplateService.MailTemplate3Async(model, e.filebase64);
}
catch (Exception ex)
{
//Keep
}
}
else
{
_result = BadRequest();
}
return(_result);
}
public async Task <ModelResponseC1Message> UpdateDocMenuC1EditAsync(ModelMenuC1 model)
{
model.meetingdate = Convert.ToDateTime(model.meetingdate.Substring(0, 10)).ToString("yyyy-MM-dd");
var resp = await _IDocMenuC1Repository.UpdateDocMenuC1EditAsync(model);
return(resp);
}
public async Task <string> GetEmailUserAsync(ModelMenuC1 model)
{
if (model != null && model.boardcodearray != null && model.boardcodearray.Count > 0)
{
string register_id = string.Empty;
foreach (var item in model.boardcodearray)
{
register_id += Encoding.UTF8.GetString(Convert.FromBase64String(item.value)) + "','";
}
if (!string.IsNullOrEmpty(register_id))
{
register_id = register_id.Remove(register_id.Length - 3, 3).ToString();
}
string sql = "SELECT email FROM RegisterUser WHERE register_id IN('" + register_id + "')";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(sql, conn))
{
SqlDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
string email_list = "";
while (await reader.ReadAsync())
{
email_list += reader["email"].ToString() + ",";
}
if (!string.IsNullOrEmpty(email_list))
{
email_list = email_list.Remove(email_list.Length - 1, 1).ToString();
}
return(email_list);
}
}
conn.Close();
}
}
return(string.Empty);
}
public async Task <ModelResponseC1Message> UpdateDocMenuC1EditAsync(ModelMenuC1 model)
{
var cultureInfo = new CultureInfo("en-GB");
CultureInfo.DefaultThreadCurrentCulture = cultureInfo;
CultureInfo.DefaultThreadCurrentUICulture = cultureInfo;
ModelResponseC1Message resp = new ModelResponseC1Message();
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("sp_doc_menu_c1_edit", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
string assigner_code = Encoding.UTF8.GetString(Convert.FromBase64String(model.assignercode));
cmd.Parameters.Add("@doc_id", SqlDbType.Int).Value = model.docid;
cmd.Parameters.Add("@assigner_code", SqlDbType.VarChar, 50).Value = ParseDataHelper.ConvertDBNull(assigner_code);
cmd.Parameters.Add("@position_name", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.positionname);
cmd.Parameters.Add("@accept_type", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.accepttype);
cmd.Parameters.Add("@project_number", SqlDbType.VarChar, 20).Value = ParseDataHelper.ConvertDBNull(model.projectnumber);
cmd.Parameters.Add("@project_head_name", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.projectheadname);
cmd.Parameters.Add("@faculty_name", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.facultyname);
cmd.Parameters.Add("@project_name_thai", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.projectnamethai);
cmd.Parameters.Add("@project_name_eng", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.projectnameeng);
cmd.Parameters.Add("@board_code_array", SqlDbType.VarChar).Value = JsonConvert.SerializeObject(model.boardcodearray);
cmd.Parameters.Add("@speciallist_code_array", SqlDbType.VarChar).Value = JsonConvert.SerializeObject(model.speciallistcodearray);
cmd.Parameters.Add("@round_of_meeting", SqlDbType.Int).Value = model.roundofmeeting;
cmd.Parameters.Add("@year_of_meeting", SqlDbType.Int).Value = model.yearofmeeting;
cmd.Parameters.Add("@meeting_date", SqlDbType.DateTime).Value = Convert.ToDateTime(model.meetingdate);
cmd.Parameters.Add("@create_by", SqlDbType.VarChar, 50).Value = Encoding.UTF8.GetString(Convert.FromBase64String(model.createby));
SqlParameter rStatus = cmd.Parameters.Add("@rStatus", SqlDbType.Int);
rStatus.Direction = ParameterDirection.Output;
SqlParameter rMessage = cmd.Parameters.Add("@rMessage", SqlDbType.NVarChar, 500);
rMessage.Direction = ParameterDirection.Output;
await cmd.ExecuteNonQueryAsync();
if ((int)cmd.Parameters["@rStatus"].Value > 0)
{
resp.Status = true;
resp.EmailArray = await GetEmailUserAsync(model);
}
else
{
resp.Message = (string)cmd.Parameters["@rMessage"].Value;
}
}
conn.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(resp);
}
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);
}
public async Task <ModelResponseC1Message> AddDocMenuC1Async(ModelMenuC1 model)
{
var cultureInfo = new CultureInfo("en-GB");
CultureInfo.DefaultThreadCurrentCulture = cultureInfo;
CultureInfo.DefaultThreadCurrentUICulture = cultureInfo;
ModelResponseC1Message resp = new ModelResponseC1Message();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("sp_doc_menu_c1", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
string assigner_code = Encoding.UTF8.GetString(Convert.FromBase64String(model.assignercode));
cmd.Parameters.Add("@assigner_code", SqlDbType.VarChar, 50).Value = ParseDataHelper.ConvertDBNull(assigner_code);
cmd.Parameters.Add("@position_name", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.positionname);
cmd.Parameters.Add("@accept_type", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.accepttype);
cmd.Parameters.Add("@project_number", SqlDbType.VarChar, 20).Value = ParseDataHelper.ConvertDBNull(model.projectnumber);
cmd.Parameters.Add("@project_head_name", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.projectheadname);
cmd.Parameters.Add("@faculty_name", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.facultyname);
cmd.Parameters.Add("@project_name_thai", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.projectnamethai);
cmd.Parameters.Add("@project_name_eng", SqlDbType.VarChar, 200).Value = ParseDataHelper.ConvertDBNull(model.projectnameeng);
cmd.Parameters.Add("@board_code_array", SqlDbType.VarChar).Value = JsonConvert.SerializeObject(model.boardcodearray);
cmd.Parameters.Add("@speciallist_code_array", SqlDbType.VarChar).Value = JsonConvert.SerializeObject(model.speciallistcodearray);
cmd.Parameters.Add("@round_of_meeting", SqlDbType.Int).Value = model.roundofmeeting;
cmd.Parameters.Add("@year_of_meeting", SqlDbType.Int).Value = model.yearofmeeting;
cmd.Parameters.Add("@meeting_date", SqlDbType.DateTime).Value = Convert.ToDateTime(model.meetingdate);
cmd.Parameters.Add("@create_by", SqlDbType.VarChar, 50).Value = Encoding.UTF8.GetString(Convert.FromBase64String(model.createby));
int seq = 1;
StringBuilder list_committee_code_array = new StringBuilder();
StringBuilder list_committee_name_array = new StringBuilder();
if (model.boardcodearray != null && model.boardcodearray.Count > 0)
{
foreach (var item in model.boardcodearray)
{
list_committee_code_array.AppendLine(Encoding.UTF8.GetString(Convert.FromBase64String(item.value.Trim())) + ", ");
list_committee_name_array.AppendLine(seq.ToString() + ". " + item.label.Trim());
seq++;
}
}
cmd.Parameters.Add("@committee_code_array", SqlDbType.NVarChar).Value = list_committee_code_array.ToString();
cmd.Parameters.Add("@committee_name_array", SqlDbType.NVarChar).Value = list_committee_name_array.ToString();
SqlParameter rStatus = cmd.Parameters.Add("@rStatus", SqlDbType.Int);
rStatus.Direction = ParameterDirection.Output;
SqlParameter rMessage = cmd.Parameters.Add("@rMessage", SqlDbType.NVarChar, 500);
rMessage.Direction = ParameterDirection.Output;
SqlParameter rDocId = cmd.Parameters.Add("@rDocId", SqlDbType.Int);
rDocId.Direction = ParameterDirection.Output;
await cmd.ExecuteNonQueryAsync();
if ((int)cmd.Parameters["@rStatus"].Value > 0)
{
resp.Status = true;
resp.EmailArray = await GetEmailUserAsync(model);
model_rpt_11_file rpt = await _IDocMenuReportRepository.GetReportR11Async((int)cmd.Parameters["@rDocId"].Value);
resp.filename = rpt.filename;
resp.filebase64 = rpt.filebase64;
}
else
{
resp.Message = (string)cmd.Parameters["@rMessage"].Value;
}
}
conn.Close();
}
return(resp);
}
public async Task<IList<ModelMail_Template3>> GetCommittee_MailTemplate3_Async(ModelMenuC1 model)
{
string multi_user = "";
if (model.boardcodearray != null && model.boardcodearray.Count > 0)
{
foreach (var item in model.boardcodearray)
{
multi_user += Encoding.UTF8.GetString(Convert.FromBase64String(item.value)) + "','";
}
}
string sql = "SELECT email, (first_name + full_name) as full_name " +
"FROM [dbo].[RegisterUser] " +
"WHERE register_id IN ('" + multi_user + "')";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(sql, conn))
{
SqlDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
IList<ModelMail_Template3> e = new List<ModelMail_Template3>();
while (await reader.ReadAsync())
{
ModelMail_Template3 item = new ModelMail_Template3();
item.fullname = reader["full_name"].ToString();
item.email = reader["email"].ToString();
e.Add(item);
}
return e;
}
}
conn.Close();
}
return null;
}
public async Task <bool> MailTemplate3Async(ModelMenuC1 model, string rptBase64)
{
return(await _IMailTemplateRepository.MailTemplate3Async(model, rptBase64));
}
