using System; using System.IO; using System.Linq; using ClosedXML.Excel; using Microsoft.AspNetCore.Mvc; using rmutr_report.Models; using Swashbuckle.AspNetCore.Annotations; namespace rmutr_report.Controllers { [SwaggerTag("สำหรับรายงาน ง.6")] public class CalculationExcessTuitionFee : Controller { readonly Setting _setting; public CalculationExcessTuitionFee(Setting setting) { this._setting = setting; } [HttpPost, Route("reports/calculation_excess_tuition_fee/{type}")] [ApiExplorerSettings(GroupName = "reports")] public IActionResult GetCalReport([FromRoute] string type, [FromBody] calculation_excess_tuition_fee _calculation_excess_tuition_fee) { { var workbook = new XLWorkbook(); var ws = workbook.Worksheets.Add("แบบ ง.6"); ws.Range("A1:M1").Merge().Value = "แบบฟอร์มการคำนวณค่าสอนเกินภาระงาน"; ws.Range("A1:M1").Style.Alignment.WrapText = true; ws.Range("A1:M1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range("A1:M1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("A1").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("A1").Style.Font.FontSize = 25; ws.Range("A1:N1").Style.Font.Bold = true; ws.Cell("N1").Value = "แบบ ง.6"; ws.Cell("N1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("N1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("N1").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("N1").Style.Font.FontSize = 14; ws.Cell("N1").Style.Font.Bold = true; ws.Range("A2:D2").Merge().Value = "หน่วยงาน "+ _calculation_excess_tuition_fee.agency; ws.Cell("A2").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("A2").Style.Font.FontSize = 14; ws.Cell("A2").Style.Font.Bold = true; ws.Cell("A2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("A2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("E2:G2").Merge().Value = "พื้นที่ "+ _calculation_excess_tuition_fee.area; ws.Cell("E2").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("E2").Style.Font.FontSize = 14; ws.Cell("E2").Style.Font.Bold = true; ws.Cell("E2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("E2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("A3:G3").Merge().Value = "ผลผลิต "+ _calculation_excess_tuition_fee.product; ws.Cell("A3").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("A3").Style.Font.FontSize = 14; ws.Cell("A3").Style.Font.Bold = true; ws.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("A3").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("A5:A6").Merge().Value = "ลำดับที่"; ws.Cell("A5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("A5").Style.Font.FontSize = 14; ws.Cell("A5").Style.Font.Bold = true; ws.Cell("A5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("A5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("A5:A6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("B5:C6").Merge().Value = "ชื่อ - สกุล (1)"; ws.Cell("B5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("B5").Style.Font.FontSize = 14; ws.Cell("B5").Style.Font.Bold = true; ws.Cell("B5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("B5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("B5:C6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("D5:D6").Merge().Value = "ตำแหน่ง (2)"; ws.Cell("D5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("D5").Style.Font.FontSize = 14; ws.Cell("D5").Style.Font.Bold = true; ws.Cell("D5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("D5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("D5:D6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("E5:E6").Merge().Value = "ประเภทบุคลากร (3)"; ws.Cell("E5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("E5").Style.Font.FontSize = 14; ws.Cell("E5").Style.Font.Bold = true; ws.Cell("E5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("E5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("E5:E6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("F5:H5").Merge().Value = "จำนวนชั่วโมงที่สอน / 1 ภาคการศึกษา "+_calculation_excess_tuition_fee.academic_semester_name_th1+ " / "+" "+_calculation_excess_tuition_fee.academic_year_name_th1+")"; ws.Cell("F5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("F5").Style.Font.FontSize = 14; ws.Cell("F5").Style.Font.Bold = true; ws.Cell("F5").Style.Alignment.WrapText = true; ws.Cell("F5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("F5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("F5:H5").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("F6").Value = "จำนวนชั่วโมงที่สอนตามภาระงานแต่ละประเภท (ตามเกณฑ์) (4)"; ws.Cell("F6").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("F6").Style.Font.FontSize = 14; ws.Cell("F6").Style.Font.Bold = true; ws.Cell("F6").Style.Alignment.WrapText = true; ws.Cell("F6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("F6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("F6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("G6").Value = "จำนวนชั่วโมงที่สอนจริงตามภาระงานแต่ละประเภท (5)"; ws.Cell("G6").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("G6").Style.Font.FontSize = 14; ws.Cell("G6").Style.Font.Bold = true; ws.Cell("G6").Style.Alignment.WrapText = true; ws.Cell("G6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("G6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("G6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("H6").Value = "ผลต่าง ระหว่าง (5) - (4) = (6)"; ws.Cell("H6").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("H6").Style.Font.FontSize = 14; ws.Cell("H6").Style.Font.Bold = true; ws.Cell("H6").Style.Alignment.WrapText = true; ws.Cell("H6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("H6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("H6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("I5:I6").Merge().Value = "อัตราค่าสอน(400) x ( 6 ) = (7)"; ws.Cell("I5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("I5").Style.Font.FontSize = 14; ws.Cell("I5").Style.Font.Bold = true; ws.Cell("I5").Style.Alignment.WrapText = true; ws.Cell("I5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("I5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("I5").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("J5:L5").Merge().Value = "จำนวนชั่วโมงที่สอน / 1 ภาคการศึกษา "+_calculation_excess_tuition_fee.academic_semester_name_th2+ " / "+" "+_calculation_excess_tuition_fee.academic_year_name_th2+")"; ws.Cell("J5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("J5").Style.Font.FontSize = 14; ws.Cell("J5").Style.Font.Bold = true; ws.Cell("J5").Style.Alignment.WrapText = true; ws.Cell("J5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("J5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("J5:L5").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("J6").Value = "จำนวนชั่วโมงที่สอนตามภาระงานแต่ละประเภท (ตามเกณฑ์) (8)"; ws.Cell("J6").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("J6").Style.Font.FontSize = 14; ws.Cell("J6").Style.Font.Bold = true; ws.Cell("J6").Style.Alignment.WrapText = true; ws.Cell("J6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("J6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("J6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("K6").Value = "จำนวนชั่วโมงที่สอนจริงตามภาระงานแต่ละประเภท (9)"; ws.Cell("K6").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("K6").Style.Font.FontSize = 14; ws.Cell("K6").Style.Font.Bold = true; ws.Cell("K6").Style.Alignment.WrapText = true; ws.Cell("K6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("K6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("K6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cell("L6").Value = "ผลต่าง ระหว่าง (9) - (8) = (10)"; ws.Cell("L6").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("L6").Style.Font.FontSize = 14; ws.Cell("L6").Style.Font.Bold = true; ws.Cell("L6").Style.Alignment.WrapText = true; ws.Cell("L6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("L6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Cell("L6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("M5:M6").Merge().Value = "อัตราค่าสอน(400) x (10) = (11)"; ws.Cell("M5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("M5").Style.Font.FontSize = 14; ws.Cell("M5").Style.Font.Bold = true; ws.Cell("M5").Style.Alignment.WrapText = true; ws.Cell("M5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("M5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("M5:M6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range("N5:N6").Merge().Value = "รวมงบประมาณ (7) + (11) = (12)"; ws.Cell("N5").Style.Font.FontName = "TH SarabunPSK"; ws.Cell("N5").Style.Font.FontSize = 14; ws.Cell("N5").Style.Font.Bold = true; ws.Cell("N5").Style.Alignment.WrapText = true; ws.Cell("N5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("N5").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range("N5:N6").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Column(1).Width = 10; ws.Column(2).Width = 20; ws.Column(3).Width = 20; ws.Column(4).Width = 30; ws.Column(5).Width = 30; ws.Column(6).Width = 30; ws.Column(7).Width = 30; ws.Column(8).Width = 30; ws.Column(9).Width = 30; ws.Column(10).Width = 30; ws.Column(11).Width = 30; ws.Column(12).Width = 30; ws.Column(13).Width = 30; ws.Column(14).Width = 30; int rowno = 1; int row = 7; //ws.Cell(row, 3).SetDataType(XLDataType.Number); if (_calculation_excess_tuition_fee != null) { foreach (var expenses in _calculation_excess_tuition_fee.data) { decimal? sum1 = expenses.teaching_hours_workload1 - expenses.teaching_hours_workload_pertype1; decimal? sum2 = (expenses.teaching_hours_workload1 - expenses.teaching_hours_workload_pertype1) * 400; decimal? sum3 = (expenses.teaching_hours_workload2 - expenses.teaching_hours_workload_pertype2); decimal? sum4 = (expenses.teaching_hours_workload2 - expenses.teaching_hours_workload_pertype2) * 400; //_calculation_excess_tuition_fee.total_1 = sum1; var sum5 = _calculation_excess_tuition_fee.data.Where(g => g.teaching_hours_workload_pertype1 == g.teaching_hours_workload_pertype1).Sum(a => a.teaching_hours_workload_pertype1); var sum6 = _calculation_excess_tuition_fee.data .Where(g => g.teaching_hours_workload1 == g.teaching_hours_workload1).Sum(a => a.teaching_hours_workload1); var sum7 = _calculation_excess_tuition_fee.data.Where(g => g.teaching_hours_workload_pertype2 == g.teaching_hours_workload_pertype2).Sum(a => a.teaching_hours_workload_pertype2); var sum8 = _calculation_excess_tuition_fee.data .Where(g => g.teaching_hours_workload2 == g.teaching_hours_workload2).Sum(a => a.teaching_hours_workload2); var teach_rate1 = _calculation_excess_tuition_fee.data .Where(g => g.teach_rate_1 == g.teach_rate_1).Sum(a => a.teach_rate_1); var diff_teach1 = _calculation_excess_tuition_fee.data .Where(g => g.diff_teach1 == g.diff_teach1).Sum(a => a.diff_teach1); var teach_rate2 = _calculation_excess_tuition_fee.data .Where(g => g.teach_rate_2 == g.teach_rate_2).Sum(a => a.teach_rate_2); var diff_teach2 = _calculation_excess_tuition_fee.data .Where(g => g.diff_teach2 == g.diff_teach2).Sum(a => a.diff_teach2); var sum9 = diff_teach1; var sum10 = sum9*400; var sum11 = diff_teach2; var sum12 = sum11*400; var sum13 = teach_rate1+teach_rate2; if ( expenses.name !="รวม") { ws.Cell(row, 1).Value = rowno; rowno++; ws.Range(ws.Cell(row, 2), ws.Cell(row, 3)).Merge().Value = expenses.name; //ws.Cell(row, 3).Value = null; ws.Cell(row, 4).Value = expenses.position; ws.Cell(row, 5).Value = expenses.pertype; ws.Cell(row, 6).Value = expenses.teaching_hours_workload_pertype1; ws.Cell(row, 7).Value = expenses.teaching_hours_workload1; ws.Cell(row, 8).Value = expenses.diff_teach1; ws.Cell(row, 9).Value = expenses.teach_rate_1; ws.Cell(row, 10).Value = expenses.teaching_hours_workload_pertype2; ws.Cell(row, 11).Value = expenses.teaching_hours_workload2; ws.Cell(row, 12).Value = expenses.diff_teach2; ws.Cell(row, 13).Value = expenses.teach_rate_2; ws.Cell(row, 14).Value = expenses.teach_rate_1 + expenses.teach_rate_2; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Alignment.WrapText = true; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Cell(row, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Range(ws.Cell(row, 4), ws.Cell(row, 14)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //ws.Cell(row, 3).Style.NumberFormat.NumberFormatId = 2; //ws.Cell(row, 3).DataType = XLDataType.Number; //ws.Cell(row,3).SetDataType(XLDataType.Number); ws.Range(ws.Cell(row, 6), ws.Cell(row, 14)).Style.NumberFormat.SetFormat("#,#"); row++; } if ( expenses.name=="รวม") { ws.Range(ws.Cell(row, 2), ws.Cell(row, 3)).Merge().Value = expenses.name; //ws.Cell(row, 3).Value = null; ws.Cell(row, 4).Value = null; ws.Cell(row, 5).Value = null; ws.Cell(row, 6).Value = sum5; ws.Cell(row, 7).Value = sum6; ws.Cell(row, 8).Value = sum9; ws.Cell(row, 9).Value = sum10; ws.Cell(row, 10).Value = sum7; ws.Cell(row, 11).Value = sum8; ws.Cell(row, 12).Value = sum11; ws.Cell(row, 13).Value = sum12; ws.Cell(row, 14).Value = sum13; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Font.FontName = "TH SarabunPSK"; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Font.FontSize = 14; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Alignment.WrapText = true; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(ws.Cell(row, 1), ws.Cell(row, 14)).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell(row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(row, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range(ws.Cell(row, 4), ws.Cell(row, 14)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //ws.Cell(row, 3).Style.NumberFormat.NumberFormatId = 2; //ws.Cell(row, 3).DataType = XLDataType.Number; //ws.Cell(row,3).SetDataType(XLDataType.Number); ws.Range(ws.Cell(row, 6), ws.Cell(row, 14)).Style.NumberFormat.SetFormat("#,#"); 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", "calculation_excess_tuition_" + date + ".xlsx"); } } } } }