public static XLWorkbook getStatistic(List <Manager> lm, DateTime firstDate, string Company, bool Anvaitis = false, bool ParkStroy = false, bool Belfan = false, bool forDays = false) { XLWorkbook wbAnalytic = new XLWorkbook(); var page = wbAnalytic.AddWorksheet("Еженедельная сводка"); var CaptionTable = page.Cell("A1"); //CaptionTable.Value = "Касание с компанией за период с " + firstDate.ToString("dd.MM") + " по " + DateTime.Now.AddDays(-2).ToString("dd.MM"); var ManagerCell = page.Cell("A2"); var BadPointCell = ManagerCell.CellRight(); //var BadCommentCell = BadPointCell.CellRight(); //var GoodCorrectionCell = Belfan ? BadPointCell.CellRight() : BadCommentCell.CellRight(); //var WorseCallCell = GoodCorrectionCell.CellRight(); //var BestCallCell = WorseCallCell.CellRight(); //var qtyCell = BestCallCell.CellRight(); var qtyCell = BadPointCell.CellRight(); var qtyPreLastCell = qtyCell.CellRight(); var AVGCell = forDays ? qtyCell.CellRight() : qtyPreLastCell.CellRight(); var AVGpreviousCell = AVGCell.CellRight(); var rngCaption = page.Range(ManagerCell, forDays ? AVGCell : AVGpreviousCell); rngCaption.Style.Font.Bold = true; ManagerCell.Value = "Менеджер"; BadPointCell.Value = "Систематически невыполняемые пункты"; // BadCommentCell.Value = "Коррекции, отклоняющиеся от нормы"; //WorseCallCell.Value = "Худший звонок"; //BestCallCell.Value = "Лучший звонок"; //GoodCorrectionCell.Value = "Положительные коррекции"; int dayShift = DateTime.Today.DayOfWeek - DayOfWeek.Thursday; bool mondayReport = Regex.Match(Company, "Атекс|Шарплейс|Sharplace|Аверс|Бенза|Деловой|Парк", RegexOptions.IgnoreCase).Success; if (mondayReport) { dayShift = DateTime.Today.DayOfWeek - DayOfWeek.Monday; } if (dayShift < 0) { dayShift += 7; } dayShift += 7; if (DateTime.Today.DayOfWeek == DayOfWeek.Sunday && mondayReport) { dayShift = 6; } if (DateTime.Today.DayOfWeek == DayOfWeek.Monday && mondayReport) { dayShift = 7; } DateTime startlastWeek = DateTime.Today.AddDays(-dayShift); if (!forDays) { qtyCell.Value = "Всего звонков с " + startlastWeek.ToString("dd.MM") + " по " + startlastWeek.AddDays(6).ToString("dd.MM"); AVGCell.Value = "Средний % c " + startlastWeek.ToString("dd.MM") + " по " + startlastWeek.AddDays(6).ToString("dd.MM"); AVGpreviousCell.Value = "Средний % c " + startlastWeek.AddDays(-7).ToString("dd.MM") + " по " + startlastWeek.AddDays(-1).ToString("dd.MM"); qtyPreLastCell.Value = "Всего звонков с " + startlastWeek.AddDays(-7).ToString("dd.MM") + " по " + startlastWeek.AddDays(-1).ToString("dd.MM"); } else { qtyCell.Value = "Всего звонков " + firstDate.ToString("dd.MM"); AVGCell.Value = "Средний % " + firstDate.ToString("dd.MM"); } ManagerCell.WorksheetColumn().Width = 15; BadPointCell.WorksheetColumn().Width = 60; //BadCommentCell.WorksheetColumn().Width = 30; qtyCell.WorksheetColumn().Width = 10; AVGCell.WorksheetColumn().Width = 10; //WorseCallCell.WorksheetColumn().Width = 15; //BestCallCell.WorksheetColumn().Width = 15; // GoodCorrectionCell.WorksheetColumn().Width = 30; AVGpreviousCell.WorksheetColumn().Width = 12; qtyPreLastCell.WorksheetColumn().Width = 12; var LastDate = firstDate.AddDays(1);//firstDate.AddDays(7); ; var firstDateFact = DateTime.Today; int curRowForPoints = 3; int curRowForBadCorrections = 3; int curRowForGoodCorrections = 3; int firstRow = 2; foreach (var m in lm) { firstRow = Math.Max(Math.Max(Math.Max(curRowForPoints, curRowForBadCorrections), curRowForGoodCorrections), firstRow + 1); curRowForPoints = firstRow; curRowForBadCorrections = firstRow; curRowForGoodCorrections = firstRow; if (m.getLastDate() > LastDate && !forDays) { LastDate = m.getLastDate(); } int qty; double AVGPerCent; if (!forDays) { qty = m.getCountOfCalls(startlastWeek, startlastWeek.AddDays(6)); AVGPerCent = m.getAVGPersent(startlastWeek, startlastWeek.AddDays(6)); } else { qty = m.getCountOfCalls(firstDate, firstDate); AVGPerCent = m.getAVGPersent(firstDate, firstDate); } List <string> BadPoints = m.getBadPoints(startlastWeek, startlastWeek.AddDays(6)); List <Call> BadComments = m.getBadComments(startlastWeek, startlastWeek.AddDays(6)); List <Call> goodComments = m.getgoodComments(startlastWeek, startlastWeek.AddDays(6)); var processedcalls = m.GetCalls().Where(c => c.dateOfCall >= startlastWeek); if (processedcalls.Count() > 0 && processedcalls.Min(c => c.dateOfCall) < firstDateFact) { firstDateFact = m.GetCalls().Where(c => c.dateOfCall >= startlastWeek).Min(c => c.dateOfCall); } //var cls = m.GetCalls().Where(c=> c.getAVGPersent() > 1); ManagerCell = page.Cell(firstRow, ManagerCell.Address.ColumnNumber); ManagerCell.Value = m.Name; ManagerCell.Style.Font.Bold = true; foreach (var p in BadPoints) { BadPointCell = page.Cell(curRowForPoints, BadPointCell.Address.ColumnNumber); BadPointCell.Value = p; curRowForPoints++; } //GoodCorrectionCell = page.Cell(firstRow, GoodCorrectionCell.Address.ColumnNumber); //GoodCorrectionCell.Value = goodComments; //WorseCallCell = WorseCallCell.CellBelow(); //WorseCallCell.Value = m.getWorseCall(firstDate); qtyCell = page.Cell(firstRow, qtyCell.Address.ColumnNumber); qtyCell.Value = qty; qtyCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); qtyPreLastCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); AVGCell = page.Cell(firstRow, AVGCell.Address.ColumnNumber); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); AVGCell.Value = AVGPerCent == -1 ? "" : String.Format("{0:0.####}", AVGPerCent); AVGCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); if (!forDays) { qtyPreLastCell = page.Cell(firstRow, qtyPreLastCell.Address.ColumnNumber); var qtyPrev = m.getCountOfCalls(startlastWeek.AddDays(-7), startlastWeek.AddDays(-1)); qtyPreLastCell.Value = qtyPrev; AVGpreviousCell = page.Cell(firstRow, AVGpreviousCell.Address.ColumnNumber); var prevAVG = m.getAVGPersent(startlastWeek.AddDays(-7), startlastWeek.AddDays(-1)); AVGpreviousCell.Value = prevAVG == -1 ? "" : String.Format("{0:0.####}", prevAVG); AVGpreviousCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); if (AVGPerCent < prevAVG && prevAVG != -1 && AVGPerCent != -1) { AVGCell.Style.Fill.BackgroundColor = XLColor.Red; } else { if (AVGPerCent > prevAVG && prevAVG != -1 && AVGPerCent != -1) { AVGCell.Style.Fill.BackgroundColor = XLColor.BrightGreen; } } if (qty < qtyPrev || (qty < 50 && ParkStroy)) { qtyCell.Style.Fill.BackgroundColor = XLColor.Red; } else { if (qty > qtyPrev) { qtyCell.Style.Fill.BackgroundColor = XLColor.BrightGreen; } } } m.getInformationPerDay(firstDate, LastDate); int lastRow = Math.Max(Math.Max(Math.Max(curRowForPoints, curRowForBadCorrections), curRowForGoodCorrections) - 1, firstRow); page.Range(ManagerCell, page.Cell(lastRow, ManagerCell.Address.ColumnNumber)).Merge(); page.Range(qtyCell, page.Cell(lastRow, qtyCell.Address.ColumnNumber)).Merge(); page.Range(AVGCell, page.Cell(lastRow, AVGCell.Address.ColumnNumber)).Merge(); if (!forDays) { page.Range(qtyPreLastCell, page.Cell(lastRow, qtyPreLastCell.Address.ColumnNumber)).Merge(); page.Range(AVGpreviousCell, page.Cell(lastRow, AVGpreviousCell.Address.ColumnNumber)).Merge(); } } CaptionTable.Value = "Касание с компанией за период с " + startlastWeek.ToString("dd.MM") + " по " + startlastWeek.AddDays(6).ToString("dd.MM"); var Rng = page.RangeUsed(); var Caption = page.Range(1, 1, 1, Rng.LastColumn().ColumnNumber()); Caption.Style.Font.Bold = true; Caption.Merge(); Rng.Style.Border.InsideBorder = XLBorderStyleValues.Thin; Rng.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;; Rng.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; Rng.Style.Alignment.WrapText = true; return(wbAnalytic); }
public static XLWorkbook FillAnalyticOfPoint(Manager m, Manager PreLastMonthManager, bool Belfan = false, bool RNR = false) { XLWorkbook wb = new XLWorkbook(m.FilePath); foreach (var stage in m.getStages()) { var dictPoints = stage.getStatisticOfPoints(Belfan, RNR); var page = wb.Worksheet(stage.name); var table = page.RangeUsed(); int curCol = table.LastColumn().ColumnNumber() + 2; const int numColPoint = 4; IXLCell CellStartCaption = page.Cell(2, curCol); if (dictPoints.Count > 0) { double widthCellCaption = 13.3; CellStartCaption.Value = "Количество некорректных пунктов"; CellStartCaption.WorksheetColumn().Width = widthCellCaption; CellStartCaption.CellRight().Value = "Количество прохождений пункта"; CellStartCaption.CellRight().WorksheetColumn().Width = widthCellCaption; CellStartCaption.CellRight().CellRight().Value = "Количество корректных пунктов"; CellStartCaption.CellRight().CellRight().WorksheetColumn().Width = widthCellCaption; CellStartCaption.CellRight().CellRight().CellRight().Value = "% Выполнения"; CellStartCaption.CellRight().CellRight().CellRight().WorksheetColumn().Width = widthCellCaption; var lastCellCaption = CellStartCaption.CellRight().CellRight().CellRight(); Dictionary <string, KeyValuePair <int, int> > dictPointsPreLastMonth = new Dictionary <string, KeyValuePair <int, int> >(); var prelastMonthCell = CellStartCaption.CellRight().CellRight().CellRight().CellRight(); var howChangeCell = prelastMonthCell.CellRight(); var totalCompareLastMonthsCell = howChangeCell.CellRight().CellBelow(); var totalCompareRange = page.Range(totalCompareLastMonthsCell, totalCompareLastMonthsCell.CellRight().CellRight()); var QtyWorseCell = totalCompareLastMonthsCell.CellBelow(); var QtyBetterCell = QtyWorseCell.CellRight(); var QtyNoChangeCell = QtyBetterCell.CellRight(); var totalMonthCell = QtyWorseCell.CellBelow().CellBelow().CellBelow(); var totalAVGLastMonthCell = totalMonthCell.CellBelow(); var totalAVGPreLastMonthCell = totalAVGLastMonthCell.CellBelow(); if (PreLastMonthManager != null && PreLastMonthManager.Name == m.Name && PreLastMonthManager.getStages().Exists(s => s.name.Trim() == stage.name.Trim()) && PreLastMonthManager.getStages().Where(s => s.name.Trim() == stage.name.Trim()).First().calls.Count > 0) { prelastMonthCell.Value = PreLastMonthManager.month; var preLastMonthCurStage = PreLastMonthManager.getStages().Where(s => s.name == stage.name).First(); dictPointsPreLastMonth = preLastMonthCurStage.getStatisticOfPoints(Belfan, RNR); howChangeCell.Value = "Как изменилось по сравнению с прошлым месяцем"; howChangeCell.WorksheetColumn().Width = 18; lastCellCaption = howChangeCell; totalCompareLastMonthsCell.Value = "Итоги сравнения с прошлым месяцем"; totalCompareRange.Merge(); QtyWorseCell.Value = "Всего Ухудшилось"; QtyBetterCell.Value = "Всего Улучшилось"; QtyNoChangeCell.Value = "Без изменения"; QtyWorseCell = QtyWorseCell.CellBelow(); QtyBetterCell = QtyBetterCell.CellBelow(); QtyNoChangeCell = QtyNoChangeCell.CellBelow(); totalCompareRange = page.Range(totalCompareLastMonthsCell, QtyNoChangeCell); Range(ref totalCompareRange); totalMonthCell.Value = "Месяц"; totalMonthCell.CellRight().Value = "Средний процент выполнения пунктов"; totalAVGLastMonthCell.Value = m.month; totalAVGPreLastMonthCell.Value = PreLastMonthManager.month; totalAVGLastMonthCell = totalAVGLastMonthCell.CellRight(); totalAVGPreLastMonthCell = totalAVGPreLastMonthCell.CellRight(); var totalrng = page.Range(totalMonthCell, totalAVGPreLastMonthCell); Range(ref totalrng); page.Columns(QtyWorseCell.Address.ColumnNumber, QtyNoChangeCell.Address.ColumnNumber).AdjustToContents(); } var Caption = page.Range(CellStartCaption, lastCellCaption); Caption.Style.Alignment.WrapText = true; //if (page.Cell(3, 4).IsMerged()) //{ Caption = page.Range(CellStartCaption.CellLeft(), lastCellCaption.CellBelow()); MergeRange(ref Caption); //} Caption.Style.Alignment.WrapText = true; Caption.Style.Font.Bold = true; Regex rComment = new Regex(@"КОРРЕКЦИИ"); int corrRow = 5; Match Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper()); while (!Mcomment.Success) { corrRow++; Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper()); } var CellPoint = CellStartCaption.CellBelow(); IXLCell lastCell = CellPoint; double totalSumLast = 0; double totalSumPreLast = 0; int totalQtyPointsLast = 0; int totalQtyPointsPreLast = 0; int qtyNoChange = 0; int qtyBetter = 0; int qtyWorse = 0; while (CellPoint.Address.RowNumber < corrRow - 4) { var CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint); if (dictPoints.ContainsKey(CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString(): "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : ""))) { int qtyRed = dictPoints[CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")].Key; int qtyAll = dictPoints[CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")].Value; CellPoint.CellLeft().Style.Fill.BackgroundColor = XLColor.Red; CellPoint.Value = qtyRed; CellPoint.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); CellPoint.CellRight().Value = qtyAll; CellPoint.CellRight().Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); CellPoint.CellRight().CellRight().Value = qtyAll - qtyRed; CellPoint.CellRight().CellRight().Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); double AVGLast = (double)(qtyAll - qtyRed) / qtyAll; CellPoint.CellRight().CellRight().CellRight().Value = AVGLast; CellPoint.CellRight().CellRight().CellRight().Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); if (AVGLast < 1) { CellPoint.CellRight().CellRight().CellRight().Style.Fill.BackgroundColor = XLColor.Red; } totalSumLast += AVGLast; totalQtyPointsLast++; lastCell = CellPoint.CellRight().CellRight().CellRight(); if (PreLastMonthManager != null && PreLastMonthManager.Name == m.Name && PreLastMonthManager.getStages().Exists(s => s.name == stage.name) && PreLastMonthManager.getStages().Where(s => s.name == stage.name).First().calls.Count > 0) { howChangeCell = page.Cell(CellPoint.Address.RowNumber, howChangeCell.Address.ColumnNumber); lastCell = howChangeCell; if (dictPointsPreLastMonth.ContainsKey(CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")) && PreLastMonthManager.getStages().Where(s => s.name == stage.name).First().calls.Count > 0) { prelastMonthCell = page.Cell(CellPoint.Address.RowNumber, prelastMonthCell.Address.ColumnNumber); int qtyAllPreLast = dictPointsPreLastMonth[CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")].Value; int qtyRedPreLast = dictPointsPreLastMonth[CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")].Key; double AvgPreLast = (double)(qtyAllPreLast - qtyRedPreLast) / qtyAllPreLast; prelastMonthCell.Value = AvgPreLast; prelastMonthCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); totalSumPreLast += AvgPreLast; totalQtyPointsPreLast++; if (AvgPreLast < 1) { prelastMonthCell.Style.Fill.BackgroundColor = XLColor.Red; } if (AVGLast < AvgPreLast) { howChangeCell.Value = "Ухудшилось"; howChangeCell.Style.Fill.BackgroundColor = XLColor.Red; qtyWorse++; } if (AVGLast > AvgPreLast) { howChangeCell.Value = "Улучшилось"; howChangeCell.Style.Fill.BackgroundColor = XLColor.BrightGreen; qtyBetter++; } if (AVGLast == AvgPreLast) { howChangeCell.Value = "Не изменилось"; qtyNoChange++; } } else { if (PreLastMonthManager.getStages().Where(s => s.name == stage.name).First().calls.Count > 0) { howChangeCell.Value = "Критерий изменился"; } } } } CellPoint = CellPoint.CellBelow(); } if (PreLastMonthManager != null && PreLastMonthManager.Name == m.Name && PreLastMonthManager.getStages().Exists(s => s.name == stage.name) && PreLastMonthManager.getStages().Where(s => s.name == stage.name).First().calls.Count > 0) { QtyNoChangeCell.Value = qtyNoChange; QtyNoChangeCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); QtyWorseCell.Value = qtyWorse; QtyWorseCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); QtyBetterCell.Value = qtyBetter; QtyBetterCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); totalAVGLastMonthCell.Value = totalSumLast / totalQtyPointsLast; totalAVGLastMonthCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); totalAVGPreLastMonthCell.Value = totalSumPreLast / totalQtyPointsPreLast; totalAVGPreLastMonthCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); } else { var CellAVGLastMonth = CellPoint.CellRight().CellRight().CellRight().CellBelow(); CellAVGLastMonth.Value = totalSumLast / totalQtyPointsLast; CellAVGLastMonth.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); CellAVGLastMonth.CellLeft().Value = "Средний %"; var rngavg = page.Range(CellAVGLastMonth.CellLeft(), CellAVGLastMonth); Range(ref rngavg); } var rngTable = page.Range(CellStartCaption.CellLeft(), lastCell); //rngTable.Style.Border.RightBorder = XLBorderStyleValues.Thin; rngTable.Style.Border.InsideBorder = XLBorderStyleValues.Thin; rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;; rngTable.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; } } return(wb); }