Monthly Ticket Sales Report Excel

mail@pastecode.io avatar
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);
            }
        }