ibms-dome/Backend/Controllers/EmergencyContactController.cs

using Backend.Models;
using iTextSharp.text;
using iTextSharp.text.html.simpleparser;
using iTextSharp.text.pdf;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Repository.BackendRepository.Interface;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace Backend.Controllers
{
    public class EmergencyContactController : MybaseController<EmergencyContactController>
    {
        private readonly IBackendRepository backendRepository;
        public EmergencyContactController(IBackendRepository backendRepository)
        {
            this.backendRepository = backendRepository;
        }
        public IActionResult Index()
        {
            return View();
        }
        /// <summary>
        /// 根據所選組別列出人員清單
        /// </summary>
        /// <param name="selectgroupidlist">組別id</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ActionResult> EmergencyContactTable (List<int> selectgroupidlist)
        {
            List<EmergencyContactTable> Emergency_member_tables = new List<EmergencyContactTable>();
            ApiResult<List<EmergencyContactTable>> apiResult = new ApiResult<List<EmergencyContactTable>>();
            try
            {
                Emergency_member_tables = await backendRepository.GetAllAsync<EmergencyContactTable>($@"
                    select  v.system_key groupingName,va.system_key departmentName,em.* from emergency_member em left join variable v on em.grouping = v.id
                    left join (select * from variable vs where vs.system_type = 'department' and vs.deleted = 0) va on va.system_value = em.department
                    where em.grouping in @groupinglist and em.deleted = 0",new { groupinglist = selectgroupidlist });
                apiResult.Code = "0000";
                apiResult.Data = Emergency_member_tables;
            }
            catch (Exception exception)
            {
                apiResult.Code = "9999";
                apiResult.Msg = "系統內部錯誤，請聯絡管理者。";
                Logger.LogError("【" + controllerName + "/" + actionName + "】");
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            var result = Json(new
            {
                data = apiResult
            });
            return result;
        }


        public FileResult ExportPDF(string post)
        {
            var grouping = JsonConvert.DeserializeObject<export>(post);
            var stream = new MemoryStream();
            try
            {
                var Emergency_member_tables = backendRepository.GetAllAsync<EmergencyContactTable>($@"
                    select  v.system_key groupingName,va.system_key departmentName,* from emergency_member em left join variable v on em.grouping = v.id
                    left join (select * from variable vs where vs.system_type = 'department' and vs.deleted = 0) va on va.system_value = em.department
                    where em.grouping in @groupinglist and em.deleted = 0", new { groupinglist = grouping.groupidlist });
                using (var doc = new Document())
                {
                    using (var writer = PdfWriter.GetInstance(doc, stream))
                    {
                        writer.CloseStream = false;
                        BaseFont BaseF = BaseFont.CreateFont("C:\\Windows\\Fonts\\kaiu.ttf", BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
                        Font fontCh = new Font(BaseF, 14);
                        doc.Open();
                        PdfPTable table = new PdfPTable(new float[] { 1, 1, 1, 1, 1 ,1 });
                        table.TotalWidth = 480f;
                        table.LockedWidth = true;
                        PdfPCell header = new PdfPCell(new Phrase(grouping.disaster+"-聯絡清單", fontCh));
                        header.Colspan = 6;
                        table.AddCell(header);
                        table.AddCell(new Phrase("組別", fontCh));
                        table.AddCell(new Phrase("姓名", fontCh));
                        table.AddCell(new Phrase("部門", fontCh));
                        table.AddCell(new Phrase("電話", fontCh));
                        table.AddCell(new Phrase("LINE ID", fontCh));
                        table.AddCell(new Phrase("電子信箱", fontCh));

                        foreach(var group in Emergency_member_tables.Result)
                        {
                            table.AddCell(new Phrase(group.groupingName, fontCh));
                            table.AddCell(new Phrase(group.full_name, fontCh));
                            table.AddCell(new Phrase(group.departmentName, fontCh));
                            table.AddCell(new Phrase(group.phone, fontCh));
                            table.AddCell(new Phrase(group.lineid, fontCh));
                            table.AddCell(new Phrase(group.email, fontCh));
                        }
                        doc.Add(table);
                        doc.Close();
                    }
                }
                var bytes = stream.ToArray();
                stream.Position = 0;
            }
            catch (Exception exception)
            {
                Logger.LogError("【" + controllerName + "/" + actionName + "】");
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return File(stream, "application/pdf", grouping.disaster+"-聯絡清單.pdf");
        }

        public FileResult ExportExcel(string post)
        {
            var grouping = JsonConvert.DeserializeObject<export>(post);
            var workbook = new XSSFWorkbook();
            var ms = new NpoiMemoryStream
            {
                AllowClose = false
            };
            #region excel設定
            IFont font12 = workbook.CreateFont();
            font12.FontName = "新細明體";
            font12.FontHeightInPoints = 12;
            ICellStyle style12 = workbook.CreateCellStyle();
            style12.SetFont(font12);
            style12.Alignment = HorizontalAlignment.Center;
            style12.VerticalAlignment = VerticalAlignment.Center;
            IFont font12Times = workbook.CreateFont();
            font12Times.FontName = "Times New Roman";
            font12Times.FontHeightInPoints = 12;
            IFont font18 = workbook.CreateFont();
            font18.FontName = "新細明體";
            font18.FontHeightInPoints = 18;
            font18.IsBold = true;
            ICellStyle styleTitle18 = workbook.CreateCellStyle();
            styleTitle18.SetFont(font18);
            styleTitle18.Alignment = HorizontalAlignment.Center;
            styleTitle18.VerticalAlignment = VerticalAlignment.Center;
            ICellStyle styleLeft12 = workbook.CreateCellStyle();
            styleLeft12.SetFont(font12);
            styleLeft12.Alignment = HorizontalAlignment.Left;
            styleLeft12.VerticalAlignment = VerticalAlignment.Center;
            ICellStyle styleLine12 = workbook.CreateCellStyle();
            styleLine12.SetFont(font12);
            styleLine12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            styleLine12.VerticalAlignment = VerticalAlignment.Center;
            styleLine12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            styleLine12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleLine12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleLine12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            ICellStyle stylein12 = workbook.CreateCellStyle();
            stylein12.SetFont(font12Times);
            stylein12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
            stylein12.VerticalAlignment = VerticalAlignment.Center;
            stylein12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            stylein12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            stylein12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            stylein12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            stylein12.WrapText = true;
            #endregion
            try
            {
                var Emergency_member_tables = backendRepository.GetAllAsync<EmergencyContactTable>($@"
                    select  v.system_key groupingName,va.system_key departmentName,* from emergency_member em left join variable v on em.grouping = v.id
                    left join (select * from variable vs where vs.system_type = 'department' and vs.deleted = 0) va on va.system_value = em.department
                    where em.grouping in @groupinglist and em.deleted = 0", new { groupinglist = grouping.groupidlist }).Result;
                var sheet = workbook.CreateSheet(grouping.disaster+"-聯絡清單");
                int RowPosition = 0;
                IRow row = sheet.CreateRow(RowPosition);
                sheet.SetColumnWidth(0, 4 * 160 * 6);
                sheet.SetColumnWidth(1, 4 * 160 * 6);
                sheet.SetColumnWidth(2, 4 * 160 * 6);
                sheet.SetColumnWidth(3, 4 * 160 * 6);
                sheet.SetColumnWidth(4, 4 * 160 * 6);
                sheet.SetColumnWidth(5, 4 * 160 * 6);
                ICell cell = row.CreateCell(0);
                cell.SetCellValue("組別");
                cell.CellStyle = styleLine12;
                cell = row.CreateCell(1);
                cell.SetCellValue("姓名");
                cell.CellStyle = styleLine12;
                cell = row.CreateCell(2);
                cell.SetCellValue("部門");
                cell.CellStyle = styleLine12;
                cell = row.CreateCell(3);
                cell.SetCellValue("電話");
                cell.CellStyle = styleLine12;
                cell = row.CreateCell(4);
                cell.SetCellValue("LINE ID");
                cell.CellStyle = styleLine12;
                cell = row.CreateCell(5);
                cell.SetCellValue("電子信箱");
                cell.CellStyle = styleLine12;
                foreach (var group in Emergency_member_tables)
                {
                    RowPosition += 1;
                    row = sheet.CreateRow(RowPosition);
                    cell = row.CreateCell(0);
                    cell.SetCellValue(group.groupingName);
                    cell.CellStyle = style12;
                    cell = row.CreateCell(1);
                    cell.SetCellValue(group.full_name);
                    cell.CellStyle = style12;
                    cell = row.CreateCell(2);
                    cell.SetCellValue(group.departmentName);
                    cell.CellStyle = style12;
                    cell = row.CreateCell(3);
                    cell.SetCellValue(group.phone);
                    cell.CellStyle = style12;
                    cell = row.CreateCell(4);
                    cell.SetCellValue(group.lineid);
                    cell.CellStyle = style12;
                    cell = row.CreateCell(5);
                    cell.SetCellValue(group.email);
                    cell.CellStyle = style12;
                }
                workbook.Write(ms);
                ms.Flush();
                ms.Seek(0, SeekOrigin.Begin);
            }
            catch(Exception exception)
            {
                Logger.LogError("【" + controllerName + "/" + actionName + "】");
                Logger.LogError("【" + controllerName + "/" + actionName + "】" + exception.Message);
            }
            return File(ms, "application/vnd.ms-excel", grouping.disaster + "-聯絡清單.xlsx");
        }
    }
}