Monthly Ticket Sales Report Excel
unknown
csharp
a year ago
16 kB
2
Indexable
Never
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); } }