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"));
}
public IActionResult Edit(int id, Transaction request)
{
var transaction = transactionDac.Get(id);
transaction.IssueDate = request.IssueDate;
transaction.DuplicatePaymentType = request.DuplicatePaymentType;
transaction.DuplicatePaymentNumber = request.DuplicatePaymentNumber;
transaction.DuplicatePaymentYear = request.DuplicatePaymentYear;
transaction.Title = request.Title;
transaction.Remark = request.Remark;
transaction.ProductType = request.ProductType;
if (transaction.Amount < 0)
{
if (request.PartnerId.HasValue)
{
var partner = partnerDac.Get(request.PartnerId.Value);
transaction.VatInclude = partner.PartnerType switch
{
PartnerType.Shop => VatHelper.GetShopVatFromFullAmount(request.Amount),
PartnerType.Person => VatHelper.GetPersonVatFromFullAmount(request.Amount),
_ => 0,
};
}
transaction.Amount = -Math.Abs(request.Amount);
if (request.BudgetId == 0)
{
request.DuplicatePaymentType = null;
request.DuplicatePaymentNumber = null;
request.DuplicatePaymentYear = null;
request.PaymentType = null;
request.PartnerId = null;
}
}
transactionDac.Update(transaction);
CalculateBringForword(request.IssueDate, request.BudgetId);
CalculateBringForword(request.IssueDate, 0);
return(RedirectToAction(nameof(Index)));
}
public IActionResult CreatePayment(Transaction request, string trackVat, int DPYCount)
{
if (request.PartnerId.HasValue)
{
var partner = partnerDac.Get(request.PartnerId.Value);
request.VatInclude = partner.PartnerType switch
{
PartnerType.Shop => VatHelper.GetShopVatFromFullAmount(request.Amount),
PartnerType.Person => VatHelper.GetPersonVatFromFullAmount(request.Amount),
_ => 0,
};
var lastDuplicatePaymentNumber = transactionDac.GetLastDuplicatePaymentNumber(CurrentSchoolConfig.SchoolYear);
var nextNumber = int.Parse(lastDuplicatePaymentNumber?.DuplicatePaymentNumber ?? "1") + (lastDuplicatePaymentNumber?.DuplicatePaymentCount ?? 0);
request.DuplicatePaymentType = partner.IsInternal ? "บค." : "บจ.";
request.DuplicatePaymentNumber = nextNumber.ToString();
request.DuplicatePaymentCount = DPYCount < 1 ? 1 : DPYCount;
request.DuplicatePaymentYear = CurrentSchoolConfig.SchoolYear;
}
request.Amount = -Math.Abs(request.Amount);
request.SchoolId = CurrentSchoolData.sc_id;
if (request.BudgetId == 0)
{
request.DuplicatePaymentType = null;
request.DuplicatePaymentNumber = null;
request.DuplicatePaymentCount = null;
request.DuplicatePaymentYear = null;
request.PaymentType = null;
request.PartnerId = null;
}
transactionDac.InsertPayment(request);
CalculateBringForword(request.IssueDate, request.BudgetId);
CalculateBringForword(request.IssueDate, 0);
return(RedirectToAction(nameof(Index)));
}