Monthly Ticket Sales Report Excel
unknown
csharp
2 years ago
16 kB
2
Indexable
public FileContentResult MonthlyTicketSalesReportExcel([FromBody] MonthlyOrderTicketReportSearchModel query)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var excel = new ExcelPackage())
{
string[] s = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ" };
string[] columName = { "№", "Дата", "Время", "Ид сеанса", "Наименование спектакля", "Заведение" };
int[] columWidth = { 5, 15, 15, 15, 35, 35 };
string[] payments = { "Перечисление", "Наличная", "Терминал" };
var reportModel = _reportService.MonthlySalesReport(query, true).Item1;
var orgName = _companyService.Find(x => x.Id == query.OrgId).Select(y => y.Name).FirstOrDefault();
var ws = excel.Workbook.Worksheets.Add("Сводный отчет о продаже билетов");
var k = 0;
for (int i = 0; i < 6; i++)
{
ws.Column(i+1).Width = columWidth[i];
ws.Cells[$"{s[k]}3:{s[k]}6"].Merge = true;
ws.Cells[$"{s[k]}3"].Value = $"{columName[k]}";
k++;
}
ws.Cells[$"{s[k]}3:{s[k+2]}5"].Merge = true;
ws.Cells[$"{s[k]}3"].Value = "Запланированные места к продаже";
ws.Column(k + 1).Width = 15;
ws.Cells[$"{s[k]}6"].Value = "Цена";
ws.Column(k + 2).Width = 10;
ws.Cells[$"{s[k+1]}6"].Value = "Кол-во";
ws.Column(k + 3).Width = 15;
ws.Cells[$"{s[k+2]}6"].Value = "Сумма";
k=k+3;
var acount = reportModel.AgentList.Count;
var _k = k + 5 + acount*2;
ws.Cells[$"{s[0]}2:{s[_k+5]}2"].Merge = true;
ws.Cells[$"{s[0]}2"].Value = $"Сводный отчет о продаже билетов {orgName} по спектаклям"; // за {DateTime.Now.ToString("MMMM yyyy").ToLower()} год
ws.Cells[$"{s[k]}3:{s[_k]}3"].Merge = true;
ws.Cells[$"{s[k]}3"].Value = "Реализовано билетов за месяц";
ws.Cells[$"{s[k]}4:{s[k+5]}4"].Merge = true;
ws.Cells[$"{s[k]}4"].Value = "Продано через кассу";
for (int i = 0; i < 3; i++)
{
ws.Cells[$"{s[k]}5:{s[k + 1]}5"].Merge = true;
ws.Cells[$"{s[k]}5"].Value = payments[i];
ws.Column(k + 1).Width = 10;
ws.Cells[$"{s[k]}6"].Value = "Кол-во";
ws.Column(k + 2).Width = 15;
ws.Cells[$"{s[k + 1]}6"].Value = "Сумма";
k = k + 2;
}
if (reportModel.AgentList.Count > 0)
{
_k = k + 1 + (acount - 1) * 2;
ws.Cells[$"{s[k]}4:{s[_k]}4"].Merge = true;
ws.Cells[$"{s[k]}4"].Value = "Продано через агенты";
foreach (var agent in reportModel.AgentList.OrderBy(x => x.AgentId))
{
ws.Cells[$"{s[k]}5:{s[k + 1]}5"].Merge = true;
ws.Cells[$"{s[k]}5"].Value = agent.AgentName;
ws.Column(k + 1).Width = 10;
ws.Cells[$"{s[k]}6"].Value = "Кол-во";
ws.Column(k + 2).Width = 15;
ws.Cells[$"{s[k + 1]}6"].Value = "Сумма";
k = k + 2;
}
}
ws.Cells[$"{s[k]}3:{s[k + 1]}5"].Merge = true;
ws.Cells[$"{s[k]}3"].Value = "Реализовано билетов";
ws.Column(k + 1).Width = 10;
ws.Cells[$"{s[k]}6"].Value = "Кол-во";
ws.Column(k + 2).Width = 15;
ws.Cells[$"{s[k+1]}6"].Value = "Сумма";
k = k + 2;
ws.Cells[$"{s[k]}3:{s[k + 1]}5"].Merge = true;
ws.Cells[$"{s[k]}3"].Value = "Непроданные места";
ws.Column(k + 1).Width = 10;
ws.Cells[$"{s[k]}6"].Value = "Кол-во";
ws.Column(k + 2).Width = 15;
ws.Cells[$"{s[k+1]}6"].Value = "Сумма";
k = k + 2;
ws.Cells[$"{s[k]}3:{s[k]}6"].Merge = true;
ws.Column(k + 1).Width = 20;
ws.Cells[$"{s[k]}3"].Value = "6 % коммиссионный сбор на ФОНД";
ws.Row(2).Height = 20;
var range = ws.Cells[$"A2:{s[k]}6"];
range.Style.WrapText = true;
range.Style.Font.Bold = true;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.SteelBlue);
range.Style.Font.Color.SetColor(Color.White);
int n = 6;
int m = 7;
int count = 0;
var grouplist = reportModel.ReportList.GroupBy(x => new { x.eventStartTime, x.sessionId, x.eventName, x.palaceOrgName }).ToList();
foreach (var group in grouplist)
{
var tarifList = reportModel.ReportList.Where(x => x.sessionId == group.Key.sessionId).ToList();
var tarifcount = tarifList.Count();
count++;
ws.Cells[$"A{m}"].Value = count;
ws.Cells[$"A{m}:A{m + tarifcount - 1}"].Merge = true;
ws.Cells[$"A{m}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[$"A{m}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[$"B{m}"].Value = $"{group.Key.eventStartTime.Value.ToString("dd.MM.yyy")}";
ws.Cells[$"B{m}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[$"B{m}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[$"B{m}:B{m + tarifcount - 1}"].Merge = true;
ws.Cells[$"C{m}"].Value = $"{group.Key.eventStartTime.Value.ToString("HH:mm")}";
ws.Cells[$"C{m}:C{m + tarifcount - 1}"].Merge = true;
ws.Cells[$"C{m}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[$"C{m}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[$"D{m}"].Value = $"{group.Key.sessionId}";
ws.Cells[$"D{m}:D{m + tarifcount - 1}"].Merge = true;
ws.Cells[$"D{m}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[$"D{m}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[$"E{m}"].Value = $"{group.Key.eventName}";
ws.Cells[$"E{m}:E{m + tarifcount - 1}"].Merge = true;
ws.Cells[$"E{m}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[$"E{m}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[$"F{m}"].Value = $"{group.Key.palaceOrgName}";
ws.Cells[$"F{m}:F{m + tarifcount - 1}"].Merge = true;
ws.Cells[$"F{m}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[$"F{m}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
m += tarifcount;
foreach (var item in tarifList)
{
n++;
ws.Cells[$"G{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"G{n}"].Value = $"{item.ticketPrice}";
ws.Cells[$"G{n}"].Style.Numberformat.Format = "#,##0.00";
ws.Cells[$"H{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"H{n}"].Value = $"{item.ticketPlanCount}";
ws.Cells[$"I{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"I{n}"].Value = $"{item.ticketPlanSum}";
ws.Cells[$"I{n}"].Style.Numberformat.Format = "#,##0.00";
ws.Cells[$"J{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"J{n}"].Value = $"{item.ticketBankCount}";
ws.Cells[$"K{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"K{n}"].Value = $"{item.ticketBankSum}";
ws.Cells[$"K{n}"].Style.Numberformat.Format = "#,##0.00";
ws.Cells[$"L{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"L{n}"].Value = item.ticketCashCount;
ws.Cells[$"M{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"M{n}"].Value = item.ticketCashSum;
ws.Cells[$"M{n}"].Style.Numberformat.Format = "#,##0.00";
ws.Cells[$"N{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"N{n}"].Value = item.ticketCardCount;
ws.Cells[$"O{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"O{n}"].Value = item.ticketCardSum;
ws.Cells[$"O{n}"].Style.Numberformat.Format = "#,##0.00";
k = 14;//O
foreach (var agentItem in reportModel.AgentList.OrderBy(x => x.AgentId))
{
var agent = item.agentTickets.FirstOrDefault(x => x.agentId == agentItem.AgentId);
var cell = s[k + 1];
if (agent != null)
{
ws.Cells[$"{s[k + 1]}{n}"].Value = agent.ticketCount;
ws.Cells[$"{s[k + 2]}{n}"].Value = agent.ticketSum;
ws.Cells[$"{s[k + 2]}{n}"].Style.Numberformat.Format = "#,##0.00";
}
k = k + 2;
}
k++;
ws.Cells[$"{s[k]}{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"{s[k]}{n}"].Value = $"{item.soldTicketsCount}";
ws.Cells[$"{s[k + 1]}{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"{s[k+1]}{n}"].Style.Numberformat.Format = "#,##0.00";
ws.Cells[$"{s[k + 1]}{n}"].Value = $"{item.soldTicketsSum}";
ws.Cells[$"{s[k + 2]}{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"{s[k + 2]}{n}"].Value = $"{item.unsoldTicketsCount}";
ws.Cells[$"{s[k + 3]}{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"{s[k+3]}{n}"].Style.Numberformat.Format = "#,##0.00";
ws.Cells[$"{s[k + 3]}{n}"].Value = $"{item.unsoldTicketsSum}";
ws.Cells[$"{s[k + 4]}{n}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[$"{s[k + 4]}{n}"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[k + 4]}{n}"].Style.Numberformat.Format = "#,##0.00";
ws.Cells[$"{s[k + 4]}{n}"].Value = $"{item.commission}";
}
ws.Cells[$"A{m}:F{m}"].Merge = true;
ws.Cells[$"A{m}:F{m}"].Value = "Итого";
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.WrapText = true;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Font.Bold = true;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
m++;
n++;
}
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Merge = true;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
m++;
ws.Cells[$"A{m}:F{m}"].Merge = true;
ws.Cells[$"A{m}:F{m}"].Value = "Общий итог";
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.WrapText = true;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Font.Bold = true;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Fill.BackgroundColor.SetColor(Color.SteelBlue);
ws.Cells[$"{s[0]}{m}:{s[_k + 5]}{m}"].Style.Font.Color.SetColor(Color.White);
string excelName = $"Сводный отчет о продаже билетов {orgName} по спектаклям.xlsx"; // за {DateTime.Now.ToString("MMMM yyyy").ToLower()} год
var stream = new MemoryStream();
excel.SaveAs(stream);
var file = stream.ToArray();
return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
}
}