public async Task <IActionResult> GetPaymentsReportAsync(PaymentsReportInputModel input)
{
var file = await QueriesService
.PaymentsReport(input)
.ConfigureAwait(false);
if (String.IsNullOrEmpty(file))
{
return(NotFound("No data found for the query"));
}
var memory = new MemoryStream();
using (var stream1 = new FileStream(file, FileMode.Open))
{
await stream1
.CopyToAsync(memory)
.ConfigureAwait(false);
}
memory.Position = 0;
var headertype = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
return(File(memory, headertype, Path.GetFileName(file)));
}
public async Task <string> PaymentsReport(PaymentsReportInputModel input)
{
var lineeReport = new List <DettaglioPagamentoModel>();
lineeReport = await DbContext.DettaglioPagamento
.Where(dp => (input.Emitted ?
dp.DatiPagamento.BodyModel.CessionarioCommittenteId == input.CliforId :
dp.DatiPagamento.BodyModel.CedentePrestatoreId == input.CliforId) &&
dp.DatiPagamento.BodyModel.Data >= input.Begin &&
dp.DatiPagamento.BodyModel.Data <= input.End)
.Include(l => l.DatiPagamento)
.ThenInclude(dp => dp.BodyModel)
.ThenInclude(b => b.CedentePrestatore)
.ThenInclude(cf => cf.ContiBancari)
.ToListAsync()
.ConfigureAwait(false);
if (lineeReport.Count == 0)
{
return(null);
}
lineeReport = lineeReport
.OrderBy(lr => lr.DataScadenzaPagamento)
.ThenBy(lr => lr.Id)
.ToList();
var paymentsReportModel = new List <PaymentsReportModel>();
foreach (var linea in lineeReport)
{
paymentsReportModel.Add(Mapper
.Map <DettaglioPagamentoModel, PaymentsReportModel>(linea));
}
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Report");
int writingRow = ReportWriter.WriteReportHeader(ws, input);
var reportData = new Tuple <int, List <PaymentsReportModel>, PaymentsReportInputModel>
(
writingRow,
paymentsReportModel,
input
);
ReportWriter.WriteReportLines(ws, reportData);
string filePath = ReportsPath + "pagamenti_" +
DateTime.Now.ToString("yy-MM-dd_hhmmss") + "_" +
input.Clifor.Replace(' ', '_') + "_" + ".xlsx";
wb.SaveAs(filePath);
return(filePath);
}
public int WriteReportHeader(IXLWorksheet ws, PaymentsReportInputModel input)
{
int writingRow = 1;
ws.Cell(writingRow, 1).RichText
.AddText("Fornitore:")
.SetBold();
ws.Cell(writingRow++, 2).RichText
.AddText(input.Clifor);
ws.Cell(writingRow, 1).RichText
.AddText("Dal:")
.SetBold();
ws.Cell(writingRow++, 2).RichText
.AddText(input.Begin.ToString("dd/MM/yyyy"));
ws.Cell(writingRow, 1).RichText
.AddText("Al:")
.SetBold();
ws.Cell(writingRow++, 2).RichText
.AddText(input.End.ToString("dd/MM/yyyy"));
writingRow++;
ws.Cell(writingRow, 1).RichText
.AddText("Numero")
.SetBold();
ws.Cell(writingRow, 2).RichText
.AddText("Del")
.SetBold();
ws.Cell(writingRow, 3).RichText
.AddText("Modalita")
.SetBold();
ws.Cell(writingRow, 4).RichText
.AddText("Banca")
.SetBold();
ws.Cell(writingRow, 5).RichText
.AddText("IBAN")
.SetBold();
ws.Cell(writingRow, 6).RichText
.AddText("TRN")
.SetBold();
ws.Cell(writingRow, 7).RichText
.AddText("Scadenza")
.SetBold();
ws.Cell(writingRow, 8).RichText
.AddText("Pagamento")
.SetBold();
ws.Cell(writingRow, 9).RichText
.AddText("Importo")
.SetBold();
ws.Range("B" + writingRow + ":H" + writingRow).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell("I" + writingRow).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range("A" + writingRow + ":I" + writingRow).Style.Border.BottomBorder = XLBorderStyleValues.Thick;
return(++writingRow);
}
public Task BindModelAsync(ModelBindingContext bindingContext)
{
string clifor = bindingContext.ValueProvider.GetValue("clifor").FirstValue;
int cliforId = Convert.ToInt32(bindingContext.ValueProvider.GetValue("cliforId").FirstValue);
DateTime begin = Convert.ToDateTime(bindingContext.ValueProvider.GetValue("begin").FirstValue);
DateTime end = Convert.ToDateTime(bindingContext.ValueProvider.GetValue("end").FirstValue);
bool emitted = Convert.ToBoolean(bindingContext.ValueProvider.GetValue("emitted").FirstValue);
var input = new PaymentsReportInputModel(clifor, cliforId, begin, end, emitted);
bindingContext.Result = ModelBindingResult.Success(input);
return(Task.CompletedTask);
}
public void WriteReportLines(IXLWorksheet ws, Tuple <int, List <PaymentsReportModel>, PaymentsReportInputModel> reportData)
{
int writingRow = reportData.Item1;
List <PaymentsReportModel> paymentsReportModel = reportData.Item2;
PaymentsReportInputModel input = reportData.Item3;
foreach (var p in paymentsReportModel)
{
var tabellaPagamento = new ModalitaPagamento().List;
var paymentMethod = tabellaPagamento
.Where(x => x.Codice == p.ModalitaPagamento)
.Select(x => x.Nome)
.FirstOrDefault();
ws.Cell(writingRow, 1).SetValue(p.NumeroFattura);
ws.Cell(writingRow, 2).SetValue(p.DataFattura);
ws.Cell(writingRow, 3).SetValue(paymentMethod);
ws.Cell(writingRow, 4).SetValue(p.IstitutoFinanziario);
ws.Cell(writingRow, 5).SetValue(p.IBAN);
ws.Cell(writingRow, 6).SetValue(p.TRNCode);
if (p.DataScadenzaPagamento != DateTime.MinValue)
{
ws.Cell(writingRow, 7).SetValue(p.DataScadenzaPagamento.ToString("dd/MM/yyyy"));
}
if (p.PaymentDate != DateTime.MinValue)
{
ws.Cell(writingRow, 8).SetValue(p.PaymentDate.ToString("dd/MM/yyyy"));
}
ws.Cell(writingRow, 9).SetValue(p.ImportoPagamento);
ws.Range("A" + writingRow + ":I" + writingRow).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
writingRow++;
}
writingRow++;
var due = paymentsReportModel
.Select(x => x.ImportoPagamento)
.Sum();
var paid = paymentsReportModel
.Where(x => x.PaymentDate != DateTime.MinValue)
.Select(x => x.ImportoPagamento)
.Sum();
var difference = paymentsReportModel
.Where(x => x.PaymentDate == DateTime.MinValue)
.Select(x => x.ImportoPagamento)
.Sum();
ws.Cell(writingRow, 1).RichText
.AddText("Totale fatture:")
.SetBold();
ws.Cell(writingRow, 2).SetValue(due);
ws.Cell(writingRow + 1, 1).RichText
.AddText("Totale pagato:")
.SetBold();
ws.Cell(writingRow + 1, 2).SetValue(paid);
ws.Cell(writingRow + 2, 1).RichText
.AddText("Residuo:")
.SetBold();
ws.Cell(writingRow + 2, 2).SetValue(difference);
var colA = ws.Column("A");
colA.AdjustToContents();
var colBD = ws.Columns("B:D");
colBD.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colBD.AdjustToContents();
var colE = ws.Column("E");
colE.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colE.AdjustToContents();
var colF = ws.Column("F");
colF.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colF.AdjustToContents();
var colG = ws.Column("G");
colG.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colG.AdjustToContents();
var colH = ws.Column("H");
colH.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colH.AdjustToContents();
var colI = ws.Column("I");
colI.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
colI.AdjustToContents();
var rangeValues = ws.Range("B1:B4");
rangeValues.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
}
