public ActionResult WithdrawReportFile(string ids, DateTime?date)
{
if (date == null)
{
date = DateTime.UtcNow;
}
var transactions = transactionDac.Get(ids).OrderBy(x => x.IssueDate);
var FileName = Path.Combine(Directory.GetCurrentDirectory(), "ReportSrc/withdrawreport.xlsx");
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load(FileName, LoadOptions.XlsxDefault);
var worksheet = workbook.Worksheets[0];
worksheet.Cells[3, Col.A].SetValue($"ส่วนราชการ {CurrentSchoolData.sc_name}");
worksheet.Cells[4, Col.B].SetValue($"บง.01/2563");
worksheet.Cells[4, Col.F].SetValue(date.Value.ToString("d MMMM yyyy", CultureInfo.GetCultureInfo("th-TH")));
worksheet.Cells[6, Col.B].SetValue($"ผู้อำนวยการ{CurrentSchoolData.sc_name}");
var issuer = identityService.GetUser();
worksheet.Cells[8, Col.B].SetValue($"ด้วยข้าพเจ้า {issuer.Name} ตำแหน่ง {issuer.Position}");
worksheet.Cells[9, Col.A].SetValue($"{CurrentSchoolData.sc_name} ทำหน้าที่ หัวหน้างานการเงิน ขออนุมัติเบิก-จ่าย");
var amount = (double)Math.Abs(transactions.Sum(t => t.Amount));
worksheet.Cells[12, Col.A].SetValue(amount);
worksheet.Cells[12, Col.C].SetValue($"({VatHelper.ThaiBaht(amount.ToString())})");
worksheet.Cells[17, Col.D].SetValue($"({issuer.Name})");
//TODO: school director
worksheet.Cells[24, Col.E].SetValue("นายสุขสันต์ สอนนวล");
worksheet.Cells[25, Col.E].SetValue($"ตำแหน่ง ผู้อำนวยการ{CurrentSchoolData.sc_name}");
var tableRowIndex = 11;
var currentRowIndex = tableRowIndex;
foreach (var item in transactions)
{
worksheet.Rows.InsertEmpty(currentRowIndex);
var range = worksheet.Cells.GetSubrange($"A{currentRowIndex}", $"J{currentRowIndex}");
range.Merged = false;
range = worksheet.Cells.GetSubrange($"B{currentRowIndex}", $"J{currentRowIndex}");
range.Merged = true;
worksheet.Rows[currentRowIndex].Style.Font = worksheet.Cells[0, 0].Style.Font;
worksheet.Cells[currentRowIndex, Col.B].SetValue($"- {item.Title}");
worksheet.Cells[currentRowIndex, Col.B].Style.WrapText = true;
worksheet.Rows[currentRowIndex].AutoFit();
currentRowIndex++;
}
var contentStream = new MemoryStream();
worksheet.PrintOptions.PaperType = PaperType.A4;
workbook.Save(contentStream, SaveOptions.PdfDefault);
return(File(contentStream, "application/pdf", $"WithdrawReport-{date:yyyyMMdd}.pdf"));
}
public ActionResult VatCertificateFile(int id)
{
var transaction = transactionDac.GetWithPartner(id);
var FileName = Path.Combine(Directory.GetCurrentDirectory(), "ReportSrc/vatcertificate.xlsx");
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load(FileName, LoadOptions.XlsxDefault);
var worksheet = workbook.Worksheets[0];
worksheet.Cells[2, Col.R].SetValue(1);
worksheet.Cells[3, Col.R].SetValue(transaction.Id.ToString());
worksheet.Cells[5, Col.D].SetValue(CurrentSchoolData.sc_name);
worksheet.Cells[5, Col.R].SetValue(CurrentSchoolData.VatId);
worksheet.Cells[7, Col.D].SetValue(CurrentSchoolData.Address);
worksheet.Cells[11, Col.D].SetValue(transaction.Partner.Name);
worksheet.Cells[13, Col.D].SetValue(transaction.Partner.Address);
if (transaction.Partner.PartnerType == PartnerType.Person)
{
worksheet.Cells[10, Col.R].SetValue(transaction.Partner.VatNumber);
worksheet.Cells[11, Col.R].SetValue(string.Empty);
worksheet.Cells[17, Col.Q].SetValue("☑ (4) ภ.ง.ด.3");
}
else
{
worksheet.Cells[10, 17].SetValue(string.Empty);
worksheet.Cells[11, 17].SetValue(transaction.Partner.VatNumber);
worksheet.Cells[17, Col.Q].SetValue("☑ (7) ภ.ง.ด.53");
}
worksheet.Cells[42, Col.G].SetValue(transaction.ProductType);
worksheet.Cells[42, Col.M].SetValue(transaction.IssueDate.ToString("d MMM yyyy", CultureInfo.CreateSpecificCulture("th-TH")));
worksheet.Cells[42, Col.O].SetValue((double)(Math.Abs(transaction.Amount) - transaction.VatInclude.Value));
worksheet.Cells[42, Col.Q].SetValue((double)transaction.VatInclude.Value);
worksheet.Cells[48, Col.O].SetValue((double)(Math.Abs(transaction.Amount) - transaction.VatInclude.Value));
worksheet.Cells[48, Col.Q].SetValue((double)transaction.VatInclude.Value);
worksheet.Cells[50, Col.I].SetValue(VatHelper.ThaiBaht(transaction.VatInclude.ToString()));
worksheet.Cells[58, Col.K].SetValue(transaction.IssueDate.ToString("d MMMM yyyy", CultureInfo.CreateSpecificCulture("th-TH")));
worksheet.Cells[62, Col.M].SetValue(transaction.IssueDate.ToString("d MMMM yyyy", CultureInfo.CreateSpecificCulture("th-TH")));
worksheet.Cells[64, Col.F].SetValue(transaction.Partner.Name);
worksheet.Cells[66, Col.G].SetValue(VatHelper.ThaiBaht((Math.Abs(transaction.Amount) - transaction.VatInclude).ToString()));
worksheet.Cells[67, Col.M].SetValue((double)(Math.Abs(transaction.Amount) - transaction.VatInclude.Value));
var contentStream = new MemoryStream();
worksheet.PrintOptions.PaperType = PaperType.A4;
workbook.Save(contentStream, SaveOptions.PdfDefault);
return(File(contentStream, "application/pdf", $"VatCertificate-{id}.pdf"));
}
