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);
        }