public Stream GenerateReport(FilterStock T)
{
var data = vacationRepository.getFilteredVacation(T).Where(o => o.Sold == false).ToList();
var fileName = DateTime.Now.Ticks + ".xlsx";
FileInfo file = new FileInfo(fileName);
FileInfo templateFile = new FileInfo(@"Templates/ReportingTemplate.xlsx");
ExcelPackage package = new ExcelPackage(file, templateFile);
ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
worksheet.Cells[1, 1].Value = string.Format("Holiday Booker Stock");
worksheet.Cells[2, 1].Value = "Resort";
worksheet.Cells[2, 2].Value = "Unit Size";
worksheet.Cells[2, 3].Value = "Region";
worksheet.Cells[2, 4].Value = "Area";
worksheet.Cells[2, 5].Value = "Arrival";
worksheet.Cells[2, 6].Value = "Nights";
worksheet.Cells[2, 7].Value = "Buying Price";
worksheet.Cells[2, 8].Value = "Price";
worksheet.Cells[2, 9].Value = "Profit";
worksheet.Cells[2, 10].Value = "Provider";
worksheet.Cells[2, 11].Value = "Admin Fee";
int row = 3;
int?regionId;
int?areaId;
foreach (var d in data)
{
worksheet.Cells[row, 1].Value = d.Resort;
worksheet.Cells[row, 2].Value = d.UnitSize;
try
{
regionId = dc.Resorts.Where(o => o.Id == d.ResortId).FirstOrDefault().RegionId;
areaId = dc.Regions.Where(o => o.Id == regionId).FirstOrDefault().AreaId;
if (regionId != null)
{
worksheet.Cells[row, 3].Value = dc.Areas.Where(o => o.Id == areaId).First().Description;
worksheet.Cells[row, 4].Value = dc.Regions.Where(o => o.Id == regionId).First().Description;
}
}
catch
{
}
worksheet.Cells[row, 5].Value = Convert.ToDateTime(d.Arrival).ToString("dd MMM yyy");
worksheet.Cells[row, 6].Value = d.Nights;
worksheet.Cells[row, 7].Value = d.BuyingPrice;
worksheet.Cells[row, 8].Value = d.Price2Pay;
worksheet.Cells[row, 9].Value = d.Price2Pay - d.BuyingPrice;
worksheet.Cells[row, 10].Value = d.Provider;
worksheet.Cells[row, 11].Value = d.AdminFee;
row++;
}
//Add values
var dataBytes = package.GetAsByteArray();
Stream dataStream = new MemoryStream(dataBytes);
dataStream.Seek(0, SeekOrigin.Begin);
return(dataStream);
}
public IActionResult ExportToExcel64(int SupplierId, int ResortId, string TheDate)
{
using (MemoryStream ms = new MemoryStream())
{
FilterStock temp = new FilterStock();
if (TheDate == "null")
{
temp.TheDate = null;
}
else
{
temp.TheDate = Convert.ToDateTime(TheDate);
}
temp.ResortId = ResortId;
temp.SupplierId = SupplierId;
var stream = exportRepository.GenerateReport(temp);
stream.CopyTo(ms);
var file = File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
file.FileDownloadName = $"Report-{DateTime.Now.ToShortDateString()}.xlsx";
Byte[] bytes = file.FileContents;
string result = Convert.ToBase64String(bytes);
return(Ok(new ExportBase64(result)));
}
}
public async Task <List <StockItem> > Get([FromQuery] string term, [FromBody] object param)
{
FilterStock filterStock = new FilterStock()
{
Name = term, param = param
};
return(await _stocksRepository.GetStockByNameFilter(filterStock));
}
public IActionResult ExportToExcel([FromBody] FilterStock T)
{
using (MemoryStream ms = new MemoryStream())
{
var stream = exportRepository.GenerateReport(T);
stream.CopyTo(ms);
var file = File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
file.FileDownloadName = $"Report-{DateTime.Now.ToShortDateString()}.xlsx";
return(file);
}
}
public IActionResult ExportToExcel64([FromBody] FilterStock T)
{
using (MemoryStream ms = new MemoryStream())
{
var stream = exportRepository.GenerateReport(T);
stream.CopyTo(ms);
var file = File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
file.FileDownloadName = $"Report-{DateTime.Now.ToShortDateString()}.xlsx";
Byte[] bytes = file.FileContents;
string result = Convert.ToBase64String(bytes);
return(Ok(new ExportBase64(result)));
}
}
public List <VacationForDisplayDto> getFilteredVacation(FilterStock T)
{
var vacationData = dc.Vacations.OrderByDescending(o => o.Id).ToList();
if (T.ResortId != 0)
{
vacationData = vacationData.Where(o => o.ResortId == T.ResortId).ToList();
}
if (T.SupplierId != 0)
{
vacationData = vacationData.Where(o => o.SupplierId == T.SupplierId).ToList();
}
if (T.TheDate != null)
{
var tempDate = (DateTime)T.TheDate;
tempDate = tempDate.Date.AddDays(1);
vacationData = vacationData.Where(o => o.Arrival >= tempDate).ToList();
vacationData = vacationData.OrderBy(o => o.Arrival).ToList();
}
List <VacationForDisplayDto> result = new List <VacationForDisplayDto>();
foreach (var entity in vacationData)
{
result.Add(new VacationForDisplayDto()
{
Id = entity.Id,
Provider = dc.Suppliers.Where(o => o.Id == entity.SupplierId).FirstOrDefault().Description,
ProviderId = entity.SupplierId,
Resort = dc.Resorts.Where(o => o.Id == entity.ResortId).FirstOrDefault().Description,
ResortId = entity.ResortId,
UnitSize = dc.UnitSizes.Where(o => o.Id == entity.UnitSizeId).FirstOrDefault().Description,
UnitSizeId = entity.UnitSizeId,
Arrival = entity.Arrival.ToString(),
Nights = entity.Nights,
BuyingPrice = entity.BuyingPrice,
Price2Pay = entity.Price2Pay,
AdminFee = entity.AdminFee,
Sold = entity.Sold,
Hold = entity.Hold
});
}
return(result);
}
public IActionResult getFilteredVacation([FromBody] FilterStock T)
{
return(Ok(vacationRepository.getFilteredVacation(T)));
}
