public void ExportToExcel(IEnumerable <IEnumerable <object> > reports)
{
var packageModel = new ExcelPackageModel {
SaveFilePath = string.Format("{0}.xlsx", Guid.NewGuid())
};
var excelPackage = _excelPackageWriter.Create(packageModel);
var sheetId = 0;
foreach (var records in reports)
{
var excelWorksheet = _excelWorkSheetWriter.Create(excelPackage, new ExcelWorkSheetModel {
Title = string.Format("Report{0}", sheetId)
});
_reflectionDataWriter.Write(excelWorksheet, DataWriterModel.Default, records);
sheetId++;
}
_excelPackageWriter.Save(excelPackage, packageModel);
}
public async Task Import(string fileName)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var file = new FileInfo(fileName);
var excelRows = new List <ExcelRowModel>();
using (var excelFile = new ExcelPackage(file))
{
ExcelWorksheet worksheet = excelFile.Workbook.Worksheets[0];
for (int i = 2; i <= worksheet.Dimension.End.Row; i++)
{
if (worksheet.Cells[$"A{i}"].Value == null)
{
continue;
}
var excelRow = new ExcelRowModel()
{
RowNumber = i,
HackneyID = worksheet.Cells[$"A{i}"].Value?.ToString(),
ServiceTypeGroup = worksheet.Cells[$"G{i}"].Value?.ToString(),
ServiceType = worksheet.Cells[$"H{i}"].Value?.ToString(),
ElementType = worksheet.Cells[$"I{i}"].Value?.ToString(),
CostPer = worksheet.Cells[$"L{i}"].Value?.ToString(),
Quantity = worksheet.Cells[$"N{i}"].Value?.ToString(),
UnitOfMeasure = worksheet.Cells[$"N{i}"].Value?.ToString(),
StartDateOA = worksheet.Cells[$"W{i}"].Value?.ToString(),
EndDateOA = worksheet.Cells[$"X{i}"].Value?.ToString(),
BudgetCode = worksheet.Cells[$"Y{i}"].Value?.ToString(),
SupplierID = worksheet.Cells[$"AB{i}"].Value?.ToString(),
SupplierSite = worksheet.Cells[$"AC{i}"].Value?.ToString()
};
excelRows.Add(excelRow);
}
}
var serviceUserPackages = excelRows.GroupBy(x => x.HackneyID, x => x).ToList();
List <string> logs = new List <string>();
foreach (var serviceUserPackage in serviceUserPackages)
{
bool hasError = false;
Guid serviceUserID = await CreateOrSkipUser(serviceUserPackage.Key);
if (serviceUserID == Guid.Empty)
{
logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tService user {serviceUserPackage.Key} not found");
hasError = true;
}
var primarySupportReasonID = GetPrimarySupportReasonID(serviceUserPackage.FirstOrDefault().BudgetCode.Replace("-", "").Substring(0, 5));
if (primarySupportReasonID == 0)
{
logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tPrimary support reason {serviceUserPackage.FirstOrDefault().BudgetCode.Substring(0, 5)} not found");
hasError = true;
}
if (!int.TryParse(serviceUserPackage.FirstOrDefault().SupplierID, out int supplierId))
{
logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tSupplierID {serviceUserPackage.FirstOrDefault().SupplierID} must be number.");
hasError = true;
}
var supplierID = GetSupplierID(supplierId, serviceUserPackage.FirstOrDefault().SupplierSite);
if (supplierID == 0)
{
logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tService user {serviceUserPackage.Key}\tSupplierID: {supplierId}\tSupplierSite: {serviceUserPackage.FirstOrDefault().SupplierSite} not found.");
hasError = true;
}
if (hasError)
{
continue;
}
var carePackage = new CarePackage()
{
Id = Guid.NewGuid(),
PackageType = ExcelPackageModel.GetPackageType(serviceUserPackage.FirstOrDefault().ServiceType),
Status = PackageStatus.Approved,
PackageScheduling = PackageScheduling.Temporary,
DateCreated = DateTimeOffset.UtcNow,
CreatorId = _applicationID,
Settings = new CarePackageSettings()
{
Id = Guid.NewGuid(),
CreatorId = _applicationID,
DateCreated = DateTimeOffset.UtcNow,
},
SupplierId = supplierID,
ServiceUserId = serviceUserID,
PrimarySupportReasonId = primarySupportReasonID,
};
foreach (var package in serviceUserPackage)
{
var excelPackageModel = new ExcelPackageModel(package.ElementType);
if (excelPackageModel.SubPackageType == ExcelPackageModel.ExcelPackageType.Detail)
{
var corePackage = new CarePackageDetail()
{
CarePackageId = carePackage.Id,
CreatorId = _applicationID,
Cost = Math.Abs(package.Cost),
DateCreated = DateTimeOffset.UtcNow,
Id = Guid.NewGuid(),
Type = excelPackageModel.PackageDetailType,
UnitOfMeasure = package.UnitOfMeasure,
StartDate = package.StartDate.UtcDateTime,
CostPeriod = excelPackageModel.CostPeriod,
EndDate = package.EndDate?.UtcDateTime,
Subjective = package.Subjective
};
carePackage.Details.Add(corePackage);
}
else if (excelPackageModel.SubPackageType == ExcelPackageModel.ExcelPackageType.Reclaim)
{
var reclaim = new CarePackageReclaim()
{
CarePackageId = carePackage.Id,
CreatorId = _applicationID,
Cost = excelPackageModel.ReclaimType == ReclaimType.Fnc ? package.Cost : Math.Abs(package.Cost),
Description = package.ElementType,
DateCreated = DateTimeOffset.UtcNow,
Id = Guid.NewGuid(),
StartDate = package.StartDate.UtcDateTime,
EndDate = package.EndDate?.UtcDateTime,
ClaimCollector = excelPackageModel.ClaimCollector,
SubType = excelPackageModel.ReclaimSubType,
Type = excelPackageModel.ReclaimType,
Status = ReclaimStatus.Active,
Subjective = package.Subjective
};
carePackage.Reclaims.Add(reclaim);
}
else
{
logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tService user {serviceUserPackage.Key}\tPackage Type {package.ElementType} is not valid.");
}
}
SyncFncClaimCollector(carePackage.Reclaims);
_databaseContext.CarePackages.Add(carePackage);
}
File.WriteAllLines($"{fileName}_logs.txt", logs);
_databaseContext.SaveChanges();
}
