public async Task <FileStreamResult> GetSaleExcel([FromQuery] string das1, string das2)
{
DateTime da1;
DateTime da2;
if (!DateTime.TryParse(das1, out da1))
{
return(null);
}
if (!DateTime.TryParse(das2, out da2))
{
return(null);
}
SaleReturnReportDto saleret = new SaleReturnReportDto();
saleret.sale = await _dapperContext.QueryAsync <SaleReportDto>("SELECT [Id],[Name],[Amount],[SaleCost], ([Amount] *[SaleCost]) as TotalSaleCost,[Client],[OptCost],[RegDt],[OrderNumber],[Comments],[IsBank] FROM[HistorySaleView] where RegDt between @da1 and @da2", new { da1, da2 });
saleret.returns = await _dapperContext.QueryAsync <ReturnReportDto>("SELECT [Id],[Name],[ReturnCost],[Amount],[RegDt],[CustomerName] FROM[ReturnView] where RegDt between @da1 and @da2", new { da1, da2 });
reportToFile = new SalesReport();
var excel = reportToFile.GetFile($"Продажа по дате: {da1.ToShortDateString()} - {da2.ToShortDateString()}", saleret);
return(new FileStreamResult(excel, new MediaTypeHeaderValue("text/plain"))
{
FileDownloadName = "Income.xlsx"
});
}
public async Task <ActionResult <SaleReturnReportDto> > GetSale([FromQuery] string das1, string das2)
{
DateTime da1;
DateTime da2;
if (!DateTime.TryParse(das1, out da1))
{
return(BadRequest());
}
if (!DateTime.TryParse(das2, out da2))
{
return(BadRequest());
}
SaleReturnReportDto saleret = new SaleReturnReportDto();
saleret.sale = await _dapperContext.QueryAsync <SaleReportDto>("SELECT [Id],[Name],[Amount],[SaleCost], ([Amount] *[SaleCost]) as TotalSaleCost,[Client],[OptCost],[RegDt],[OrderNumber],[Comments],[IsBank] FROM[HistorySaleView] where RegDt between @da1 and @da2", new { da1, da2 });
saleret.returns = await _dapperContext.QueryAsync <ReturnReportDto>("SELECT [Id],[Name],[ReturnCost],[Amount],[RegDt],[CustomerName] FROM[ReturnView] where RegDt between @da1 and @da2", new { da1, da2 });
return(saleret);
}
public MemoryStream GetFile(string title, object items)
{
using (ExcelPackage pck = new ExcelPackage())
{
SaleReturnReportDto itemlist = items as SaleReturnReportDto;
if (itemlist == null)
{
itemlist = new SaleReturnReportDto();
}
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Result");
SetType(typeof(SaleReportDto));
PrepareTitle(1, title, ws);
PrepareHead(2, ws);
LoadData("A3", itemlist.sale, ws);
ws.Cells["G3:G" + (itemlist.sale.Count + 2)].Style.Numberformat.Format = "dd-MM-yyyy";
ws.Column(7).Width = 13;
int iColCnt = ws.Dimension.End.Column;
int iRowCnt = ws.Dimension.End.Row + 2;
using (ExcelRange r = ws.Cells[iRowCnt - 1, 1, iRowCnt - 1, iColCnt])
{
r.Merge = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
double total = 0;
foreach (var item in itemlist.sale)
{
total += item.TotalSaleCost;
}
r.Value = $"Итого: {total.ToString("C0", new CultureInfo("kk-KZ"))}";
}
/// -------------------------------------------------------------------------------------------------------------- ///
SetType(typeof(ReturnReportDto));
PrepareTitle(iRowCnt + 2, title.Replace("Продажа", "Возврат"), ws);
PrepareHead(iRowCnt + 3, ws);
LoadData("A" + (iRowCnt + 4), itemlist.returns, ws);
ws.Cells["D" + (itemlist.sale.Count + 6) + ":D" + (itemlist.sale.Count + 6 + itemlist.returns.Count + 3)].Style.Numberformat.Format = "dd-MM-yyyy";
ws.Column(4).Width = 13;
iColCnt = ws.Dimension.End.Column;
iRowCnt = ws.Dimension.End.Row + 1;
using (ExcelRange r = ws.Cells[iRowCnt, 1, iRowCnt, 5])
{
r.Merge = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
double total = 0;
foreach (var item in itemlist.returns)
{
total += item.ReturnCost;
}
r.Value = $"Итого: {total.ToString("C0", new CultureInfo("kk-KZ"))}";
}
return(new MemoryStream(pck.GetAsByteArray())); //Get updated stream
}
}
