public IActionResult GetContractor(int userId)
{
var contractor = _AppUserRepo.GetContractor(userId);
if (contractor == null)
{
contractor = new Contractor {
ContractorId = userId
};
}
return(PartialView("_ContractorModal", contractor));
}
public void GenerateInvoices(int contractorId, string name, FileInfo templateFile, int cycle, List <string> fileList)
{
int blankRow = 14;
var workEntries = _WorkRepo.GetWorkExtended(contractorId, cycle, true).Where(x => "SOW".Contains(x.BillType))
.GroupBy(g => new { g.ClientId, g.ProjectId });
using (var templatePackage = new ExcelPackage(templateFile))
{
var templateSheet = templatePackage.Workbook.Worksheets["Invoice"];
var contractor = _AppUserRepo.GetContractor(contractorId);
var file = new FileInfo($"C:\\TEMP\\{name}_Invoices.xlsx");
System.IO.File.Delete(file.FullName);
using (var package = new ExcelPackage(file))
{
foreach (var inv in workEntries)
{
var first = inv.First();
var workBook = package.Workbook;
ExcelWorksheet sheet = workBook.Worksheets.Add($"{first.Client} {first.Project}", templateSheet);
sheet.Cells[2, 6].Value = $"{DateTime.Today: M/d/yyyy}";
sheet.Cells[9, 3].Value = first.Client;
sheet.Cells[10, 3].Value = first.Project;
sheet.Cells[2, 2].Value = contractor.InvoiceName;
sheet.Cells[3, 2].Value = contractor.InvoiceAddress;
sheet.Cells[11, 3].Value = contractor.Rate;
int currentRow = blankRow + 1;
foreach (var entry in inv)
{
sheet.InsertRow(currentRow, 1);
sheet.Cells[blankRow, 1, blankRow, 10].Copy(sheet.Cells[currentRow, 1]);
sheet.Cells[currentRow, 2].Value = $"{entry.WorkDate: M/d}";
sheet.Cells[currentRow, 3].Value = $"{entry.WorkType} : {entry.Descr}";
sheet.Cells[currentRow, 4].Value = entry.Hours;
sheet.Cells[currentRow, 6].Formula = $"=D{currentRow} * C11";
currentRow++;
}
sheet.Cells[currentRow, 4].Formula = $"=SUM(D{blankRow} : D{currentRow - 1})";
sheet.Cells[currentRow, 6].Formula = $"=SUM(F{blankRow} : F{currentRow - 1})";
sheet.Calculate();
}
if (package.Workbook.Worksheets.Any())
{
package.Save();
fileList.Add(package.File.FullName);
}
}
}
}