326 lines
19 KiB
C#
326 lines
19 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel.DataAnnotations;
|
|
using System.Data;
|
|
using System.Globalization;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Threading.Tasks;
|
|
using ClosedXML.Excel;
|
|
using DocumentFormat.OpenXml.Office2010.ExcelAc;
|
|
using DocumentFormat.OpenXml.Spreadsheet;
|
|
using FastReport;
|
|
using FastReport.Export.Csv;
|
|
using FastReport.Export.Mht;
|
|
using FastReport.Export.OoXML;
|
|
using FastReport.Export.Pdf;
|
|
using Microsoft.AspNetCore.Mvc;
|
|
using Microsoft.VisualBasic;
|
|
using rmutr_report.Models;
|
|
using rmutr_report.Models.Hr;
|
|
using Swashbuckle.AspNetCore.Annotations;
|
|
|
|
namespace rmutr_report.Controllers
|
|
{
|
|
[SwaggerTag("สำหรับรายงานตัวชี้วัดและเป้าหมาย")]
|
|
public class KPI : Controller
|
|
{
|
|
readonly Setting _setting;
|
|
|
|
public KPI(Setting setting)
|
|
{
|
|
_setting = setting;
|
|
}
|
|
|
|
[HttpPost, Route("reports/kpi/{type}")]
|
|
[ApiExplorerSettings(GroupName = "reports")]
|
|
public IActionResult GetKpiReport([FromRoute] string type, [FromBody] kpi _kpi)
|
|
{
|
|
var workbook = new XLWorkbook();
|
|
var ws = workbook.Worksheets.Add("kpi");
|
|
ws.Cell("A2").Value = "ประเด็นยุทธศาสตร์/เป้าหมายหลัก/ตัวชี้วัด";
|
|
ws.Cell("A2").Style.Font.Bold = true;
|
|
ws.Cell("A2").Style.Alignment.WrapText = true;
|
|
ws.Range("A2:A3").Column(1).Merge();
|
|
ws.Range("B2:C3").Merge().Value = "ตัวชี้วัด";
|
|
ws.Range("B2:C3").Style.Font.Bold = true;
|
|
ws.Range("D3").Value = "หน่วย";
|
|
ws.Range("D3").Style.Font.Bold = true;
|
|
ws.Range("E3").Value = "ค่าเป้าหมาย";
|
|
ws.Range("E3").Style.Font.Bold = true;
|
|
ws.Range("F2:F3").Merge().Value = "ผู้รับผิดชอบกำกับดูแล";
|
|
ws.Range("G2:G3").Merge().Value = "ผู้รับผิดชอบในการปฎิบัติงาน";
|
|
ws.Range("H2:H3").Merge().Value = "ผู้รวบรวมและรายงานผลตัวชี้วัด";
|
|
ws.Range("F2:F3").Style.Font.Bold = true;
|
|
ws.Range("G2:G3").Style.Font.Bold = true;
|
|
ws.Range("H2:H3").Style.Font.Bold = true;
|
|
ws.Range("A2:A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Range("B2:C3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Range("B2:C3").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Range("D2:E2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Range("D3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Range("E3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Range("F2:F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Cell("F2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Range("G2:G3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Cell("G2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Range("H2:H3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Cell("H2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Range("A2:A3").Style.Fill.BackgroundColor = XLColor.SkyBlue;
|
|
ws.Range("B2:C3").Style.Fill.BackgroundColor = XLColor.SkyBlue;
|
|
ws.Range("D2:E3").Style.Fill.BackgroundColor = XLColor.SkyBlue;
|
|
ws.Range("D3").Style.Fill.BackgroundColor = XLColor.SkyBlue;
|
|
ws.Range("E3").Style.Fill.BackgroundColor = XLColor.SkyBlue;
|
|
ws.Range("F2:F3").Style.Fill.BackgroundColor = XLColor.SkyBlue;
|
|
ws.Range("G1:G2").Style.Fill.BackgroundColor = XLColor.SkyBlue;
|
|
ws.Range("H2:H3").Style.Fill.BackgroundColor = XLColor.SkyBlue;
|
|
ws.Range("A2:A3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Range("B2:C3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Range("B2:C3").Style.Border.TopBorder = XLBorderStyleValues.Thin;
|
|
ws.Range("D2:E2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Range("D3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Range("E3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Range("F2:F3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Range("G2:G3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Range("H2:H3").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Column(1).Width = 22;
|
|
ws.Column(2).Width = 5;
|
|
ws.Column(3).Width = 30;
|
|
ws.Column(4).Width = 10;
|
|
ws.Column(5).Width = 20;
|
|
ws.Column(6).Width = 40;
|
|
ws.Column(7).Width = 40;
|
|
ws.Column(8).Width = 40;
|
|
ws.Column(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Column(3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Column(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Column(5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Column(6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Column(7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Column(8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Column(2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Column(3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Column(4).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Column(5).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Column(6).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Column(7).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Column(8).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Column(1).Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Column(1).Style.Font.FontSize = 16;
|
|
ws.Column(2).Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Column(2).Style.Font.FontSize = 16;
|
|
ws.Column(3).Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Column(3).Style.Font.FontSize = 16;
|
|
ws.Column(4).Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Column(4).Style.Font.FontSize = 16;
|
|
ws.Column(5).Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Column(5).Style.Font.FontSize = 16;
|
|
ws.Column(6).Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Column(6).Style.Font.FontSize = 16;
|
|
ws.Column(7).Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Column(7).Style.Font.FontSize = 16;
|
|
ws.Column(8).Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Column(8).Style.Font.FontSize = 16;
|
|
int col = 1;
|
|
int row = 4;
|
|
int no = 1;
|
|
int rowno = 1;
|
|
// foreach (var kpis in _kpi)
|
|
// {
|
|
if (_kpi != null)
|
|
{
|
|
ws.Range("A1:H1").Merge().Value =
|
|
"ตัวชี้วัดและเป้าหมายตามแผนปฏิบัติราชการประจำปี พ.ศ. " + _kpi.academic_year +" มหาวิทยาลัยเทคโนโลยีราชมงคลรัตนโกสินทร์";
|
|
ws.Range("A1:H1").Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
|
|
ws.Range("A1:H1").Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
|
|
ws.Cell("A1").Style.Font.Bold = true;
|
|
ws.Cell("A1").Style.Font.FontName = "TH SarabunPSK";
|
|
ws.Cell("A1").Style.Font.FontSize = 16;
|
|
ws.Range("D2:E2").Merge().Value = "ค่าเป้าหมาย ปี " + _kpi.academic_year.Substring(2, 2);
|
|
ws.Range("D2:E2").Style.Font.Bold = true;
|
|
foreach (var header in _kpi.header_data)
|
|
{
|
|
if (header.header_1 != null || header.header_1 != "")
|
|
{
|
|
string kpiname = "ประเด็นยุทธ์ศาสตร์ที่ " + no + " :";
|
|
no++;
|
|
ws.Cell(row, 1).SetValue(kpiname).Style.Font.Bold = true;
|
|
ws.Cell(row, 2).Value = header.header_1;
|
|
ws.Cell(row, 1).Style.Fill.BackgroundColor = XLColor.Bisque;
|
|
ws.Cell(row, 1).Style.Border.OutsideBorder =
|
|
XLBorderStyleValues.None;
|
|
ws.Range(ws.Cell(row, 2), ws.Cell(row, 8)).Merge().Style.Fill.BackgroundColor = XLColor.Bisque;
|
|
ws.Range(ws.Cell(row, 3), ws.Cell(row, 8)).Style.Border.OutsideBorder =
|
|
XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
|
|
row++;
|
|
}
|
|
if (header.header_1 == null)
|
|
{
|
|
header.header_1 = null;
|
|
}
|
|
|
|
if (header.header_2!=null||header.header_2!="")
|
|
{
|
|
string target = "เป้าหมายหลัก :";
|
|
ws.Cell(row, 1).SetValue(target).Style.Font.Bold = true;
|
|
int g = target.Length;
|
|
g++;
|
|
ws.Cell(row, 2).Value = header.header_2;
|
|
ws.Cell(row, 1).Style.Fill.BackgroundColor = XLColor.LavenderBlush;
|
|
ws.Cell(row, 1).Style.Border.OutsideBorder =
|
|
XLBorderStyleValues.None;
|
|
ws.Range(ws.Cell(row, 2), ws.Cell(row, 8)).Merge().Style.Fill.BackgroundColor =
|
|
XLColor.LavenderBlush;
|
|
ws.Range(ws.Cell(row, 3), ws.Cell(row, 8)).Style.Border.OutsideBorder =
|
|
XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 2).Style.Border.TopBorder =
|
|
XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 1).Style.Border.TopBorder =
|
|
XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
|
|
row++;
|
|
}
|
|
if (header.header_2 == null)
|
|
{
|
|
header.header_2 = null;
|
|
}
|
|
|
|
// if (header.indicator_type_name_th!=null||header.indicator_type_name_th!="")
|
|
// {
|
|
// ws.Cell(row, 1).Value = "ตัวชี้วัดกลยุทธ์ : " + header.indicator_type_name_th;
|
|
// ws.Cell(row, 1).Style.Fill.BackgroundColor = XLColor.Beige;
|
|
// ws.Range(ws.Cell(row, 1), ws.Cell(row, 8)).Merge().Style.Border.OutsideBorder =
|
|
// XLBorderStyleValues.Thin;
|
|
// ws.Cell(row, 1).Style.Font.Bold = true;
|
|
// row++;
|
|
// }
|
|
// if (header.indicator_type_name_th == null)
|
|
// {
|
|
// header.indicator_type_name_th = null;
|
|
// }
|
|
//var data = _kpi.header_data.Select(d => d.data).ToList();
|
|
// foreach (var detail in header)
|
|
// {
|
|
|
|
foreach (var data in header.data)
|
|
{
|
|
|
|
if (data.indicator_type_name_th != null || data.indicator_type_name_th != "")
|
|
{
|
|
ws.Cell(row, 1).Value = "ตัวชี้วัดกลยุทธ์ : " + data.indicator_type_name_th;
|
|
ws.Cell(row, 1).Style.Fill.BackgroundColor = XLColor.Beige;
|
|
ws.Range(ws.Cell(row, 1), ws.Cell(row, 8)).Merge().Style.Border.OutsideBorder =
|
|
XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 1).Style.Font.Bold = true;
|
|
row++;
|
|
}
|
|
|
|
foreach (var data1 in data.data_detail)
|
|
{
|
|
ws.Cell(row, 2).Value = rowno;
|
|
ws.Cell(row, 3).Value = data1.name;
|
|
ws.Cell(row, 4).Value = data1.unit;
|
|
ws.Cell(row, 5).Value = data1.value;
|
|
ws.Cell(row, 6).Value = data1.responsible_person;
|
|
ws.Cell(row, 7).Value = data1.responsible_work;
|
|
ws.Cell(row, 8).Value = data1.collector;
|
|
ws.Cell(row, 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 1).Style.Alignment.WrapText = true;
|
|
ws.Cell(row, 2).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 2).Style.Alignment.WrapText = true;
|
|
ws.Cell(row, 3).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 3).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 3).Style.Border.RightBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 3).Style.Alignment.WrapText = true;
|
|
ws.Cell(row, 4).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 4).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 4).Style.Border.RightBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 4).Style.Alignment.WrapText = true;
|
|
ws.Cell(row, 5).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 5).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 5).Style.Border.RightBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 5).Style.Alignment.WrapText = true;
|
|
ws.Cell(row, 6).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 6).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 6).Style.Border.RightBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 6).Style.Alignment.WrapText = true;
|
|
ws.Cell(row, 7).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 7).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 7).Style.Border.RightBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 7).Style.Alignment.WrapText = true;
|
|
ws.Cell(row, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 8).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 8).Style.Border.RightBorder = XLBorderStyleValues.Thin;
|
|
ws.Cell(row, 8).Style.Alignment.WrapText = true;
|
|
row++;
|
|
rowno++;
|
|
|
|
// if (rowno > 1)
|
|
// {
|
|
// ws.Range(ws.Cell(row, 1), ws.Cell(row, 1)).Merge();
|
|
//
|
|
// }
|
|
}
|
|
}
|
|
//row++;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
//}
|
|
// }
|
|
|
|
using (var stream = new MemoryStream())
|
|
{
|
|
workbook.SaveAs(stream);
|
|
var content = stream.ToArray();
|
|
string date = DateTime.Now.ToString("yyyyMMddHHmmss");
|
|
return File(
|
|
content,
|
|
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
|
"kpi_" + date + ".xlsx");
|
|
}
|
|
}
|
|
[HttpPost, Route("reports/kpi_performance_expenditure_budget/{type}")]
|
|
[ApiExplorerSettings(GroupName = "reports")]
|
|
public IActionResult GetKpiBudgetReport([FromRoute] string type, [FromBody] kpi_performance_expenditure_budget kpiPerformance)
|
|
{
|
|
|
|
var kpiPerformanceExpenditure = new List<kpi_performance_expenditure_budget>() { kpiPerformance };
|
|
|
|
Report report = new Report();
|
|
report.Load(_setting.report_path + "kpi_performance_expenditure_budget.frx");
|
|
report.RegisterData(kpiPerformanceExpenditure, "kpi_performance_expenditure_budget");
|
|
report.Prepare();
|
|
|
|
MemoryStream stream = new MemoryStream();
|
|
switch (type)
|
|
{
|
|
case "pdf":
|
|
PDFExport pdf = new PDFExport();
|
|
report.Export(pdf, stream);
|
|
stream.Seek(0, SeekOrigin.Begin);
|
|
return File(stream, "application/pdf");
|
|
|
|
case "xls":
|
|
case "xlsx":
|
|
Excel2007Export excel = new Excel2007Export();
|
|
report.Export(excel, stream);
|
|
stream.Seek(0, SeekOrigin.Begin);
|
|
return File(
|
|
stream,
|
|
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
|
"kpi_performance_expenditure_budget_" + ".xlsx");
|
|
|
|
}
|
|
|
|
return Ok();
|
|
}
|
|
}
|
|
} |