Esempio n. 1
0
        private async void btPrint_Click(object sender, EventArgs e)
        {
            report = new Nwuram.Framework.ToExcelNew.ExcelUnLoad();
            blockers.SaveControlsEnabledState(this);
            blockers.SetControlsEnabled(this, false);
            progressBar1.Visible = true;

            var result = await Task <bool> .Factory.StartNew(() =>
            {
                DataTable dtReport = _proc.GetReportPayAgreement(dtpStart.Value.Date, dtpEnd.Value.Date);


                if (dtReport == null || dtReport.Rows.Count == 0)
                {
                    DoOnUIThread(() =>
                    {
                        MessageBox.Show("Нет данных для выгрузки", "Выгрузка отчёта", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        blockers.RestoreControlEnabledState(this);
                        progressBar1.Visible = false;
                    }, this);
                    return(false);
                }

                Logging.StartFirstLevel(79);
                Logging.Comment("Выгрузка отчёта по добавленным оплатам");
                Logging.StopFirstLevel();

                int indexRow     = 1;
                int maxColumns   = 9;
                bool isFirstLoad = false;

                List <int> numbers = new List <int>()
                {
                    1, 3, 2
                };
                //for (int i = 1; i <= 3; i++)
                foreach (int i in numbers)
                {
                    EnumerableRowCollection <DataRow> rowCollect = dtReport.AsEnumerable()
                                                                   .Where(r => r.Field <int>("id_PayType") == i)
                                                                   .OrderBy(r => r.Field <DateTime>("Date"));
                    if (rowCollect.Count() == 0)
                    {
                        continue;
                    }


                    if (!isFirstLoad)
                    {
                        isFirstLoad = true;
                        report.changeNameTab(rowCollect.First()["namePayType"].ToString());
                    }
                    else
                    {
                        report.GoToNextSheet(rowCollect.First()["namePayType"].ToString());
                    }

                    indexRow = 1;

                    #region "Head"
                    report.Merge(indexRow, 1, indexRow, maxColumns);
                    report.AddSingleValue($"{rowCollect.First()["namePayType"]}", indexRow, 1);
                    report.SetFontBold(indexRow, 1, indexRow, 1);
                    report.SetFontSize(indexRow, 1, indexRow, 1, 16);
                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1);
                    indexRow++;
                    indexRow++;


                    report.Merge(indexRow, 1, indexRow, maxColumns);
                    report.AddSingleValue("Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername, indexRow, 1);
                    indexRow++;

                    report.Merge(indexRow, 1, indexRow, maxColumns);
                    report.AddSingleValue("Дата выгрузки: " + DateTime.Now.ToString(), indexRow, 1);
                    indexRow++;
                    indexRow++;
                    #endregion

                    setWidthColumn(indexRow, 1, 21, report);
                    setWidthColumn(indexRow, 2, 21, report);
                    setWidthColumn(indexRow, 3, 20, report);
                    setWidthColumn(indexRow, 4, 30, report);

                    report.AddSingleValue("Арендодатель", indexRow, 1);
                    report.AddSingleValue("Арендатор", indexRow, 2);
                    report.AddSingleValue("Номер договора", indexRow, 3);
                    report.AddSingleValue("Местоположение по договору", indexRow, 4);
                    report.SetPrintRepeatHead(indexRow, indexRow);

                    #region "Обеспечительный платёж"
                    if (i == 1)
                    {
                        maxColumns = 8;
                        setWidthColumn(indexRow, 5, 14, report);
                        setWidthColumn(indexRow, 6, 17, report);
                        setWidthColumn(indexRow, 7, 20, report);

                        report.AddSingleValue("Дата оплаты", indexRow, 5);
                        report.AddSingleValue("Сумма оплаты", indexRow, 6);
                        report.AddSingleValue("Тип обеспечительного платежа", indexRow, 7);
                        report.AddSingleValue("Тип операции", indexRow, 8);


                        report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                        report.SetBorders(indexRow, 1, indexRow, maxColumns);
                        report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                        indexRow++;

                        EnumerableRowCollection <DataRow> Rows = rowCollect.Where(r => r.Field <bool>("isCash"));
                        if (Rows.Count() > 0)
                        {
                            report.Merge(indexRow, 1, indexRow, maxColumns);
                            report.AddSingleValue("Наличные", indexRow, 1);
                            report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                            report.SetBorders(indexRow, 1, indexRow, maxColumns);
                            report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                            indexRow++;

                            var gData = Rows.AsEnumerable().GroupBy(g => new { Date = g.Field <DateTime>("Date") }).Select(s => new { s.Key.Date });
                            foreach (var rData in gData)
                            {
                                report.Merge(indexRow, 1, indexRow, maxColumns);
                                report.AddSingleValue($"{rData.Date.ToShortDateString()}", indexRow, 1);
                                report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                                report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                indexRow++;

                                foreach (DataRow row in Rows.AsEnumerable().Where(r => r.Field <DateTime>("Date").Date == rData.Date))
                                {
                                    report.SetWrapText(indexRow, 1, indexRow, maxColumns);

                                    addDataToCell(row["nameTenant"], indexRow, 1, report);
                                    addDataToCell(row["nameLandLord"], indexRow, 2, report);
                                    addDataToCell(row["Agreement"], indexRow, 3, report);
                                    addDataToCell(row["namePlace"], indexRow, 4, report);

                                    addDataToCell(row["Date"], indexRow, 5, report);
                                    addDataToCell(row["Summa"], indexRow, 6, report);
                                    addDataToCell(row["nameSavePayment"], indexRow, 7, report);
                                    addDataToCell(row["nameToTenant"], indexRow, 8, report);


                                    report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                    indexRow++;
                                }
                            }
                        }

                        Rows = rowCollect.Where(r => !r.Field <bool>("isCash"));
                        if (Rows.Count() > 0)
                        {
                            report.Merge(indexRow, 1, indexRow, maxColumns);
                            report.AddSingleValue("Безналичный расчёт", indexRow, 1);
                            report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                            report.SetBorders(indexRow, 1, indexRow, maxColumns);
                            report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                            indexRow++;

                            var gData = Rows.AsEnumerable().GroupBy(g => new { Date = g.Field <DateTime>("Date") }).Select(s => new { s.Key.Date });
                            foreach (var rData in gData)
                            {
                                report.Merge(indexRow, 1, indexRow, maxColumns);
                                report.AddSingleValue($"{rData.Date.ToShortDateString()}", indexRow, 1);
                                report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                                report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                indexRow++;

                                foreach (DataRow row in Rows.AsEnumerable().Where(r => r.Field <DateTime>("Date").Date == rData.Date))
                                {
                                    report.SetWrapText(indexRow, 1, indexRow, maxColumns);

                                    addDataToCell(row["nameTenant"], indexRow, 1, report);
                                    addDataToCell(row["nameLandLord"], indexRow, 2, report);
                                    addDataToCell(row["Agreement"], indexRow, 3, report);
                                    addDataToCell(row["namePlace"], indexRow, 4, report);

                                    addDataToCell(row["Date"], indexRow, 5, report);
                                    addDataToCell(row["Summa"], indexRow, 6, report);
                                    addDataToCell(row["nameSavePayment"], indexRow, 7, report);
                                    addDataToCell(row["nameToTenant"], indexRow, 8, report);

                                    report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                    indexRow++;
                                }
                            }
                        }
                    }
                    #endregion

                    #region "Оплата аренды"
                    if (i == 2)
                    {
                        maxColumns = 8;
                        setWidthColumn(indexRow, 5, 14, report);
                        setWidthColumn(indexRow, 6, 17, report);
                        setWidthColumn(indexRow, 7, 10, report);
                        setWidthColumn(indexRow, 8, 17, report);

                        report.AddSingleValue("Дата оплаты", indexRow, 5);
                        report.AddSingleValue("Сумма оплаты", indexRow, 6);
                        report.AddSingleValue("Тип операции", indexRow, 7);
                        report.AddSingleValue("План в который попадает оплата", indexRow, 8);


                        report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                        report.SetBorders(indexRow, 1, indexRow, maxColumns);
                        report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                        indexRow++;

                        EnumerableRowCollection <DataRow> Rows = rowCollect.Where(r => r.Field <bool>("isCash"));
                        if (Rows.Count() > 0)
                        {
                            report.Merge(indexRow, 1, indexRow, maxColumns);
                            report.AddSingleValue("Наличные", indexRow, 1);
                            report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                            report.SetBorders(indexRow, 1, indexRow, maxColumns);
                            report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                            indexRow++;

                            var gData = Rows.AsEnumerable().GroupBy(g => new { Date = g.Field <DateTime>("Date") }).Select(s => new { s.Key.Date });
                            foreach (var rData in gData)
                            {
                                report.Merge(indexRow, 1, indexRow, maxColumns);
                                report.AddSingleValue($"{rData.Date.ToShortDateString()}", indexRow, 1);
                                report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                                report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                indexRow++;

                                foreach (DataRow row in Rows.AsEnumerable().Where(r => r.Field <DateTime>("Date").Date == rData.Date))
                                {
                                    report.SetWrapText(indexRow, 1, indexRow, maxColumns);

                                    addDataToCell(row["nameTenant"], indexRow, 1, report);
                                    addDataToCell(row["nameLandLord"], indexRow, 2, report);
                                    addDataToCell(row["Agreement"], indexRow, 3, report);
                                    addDataToCell(row["namePlace"], indexRow, 4, report);

                                    addDataToCell(row["Date"], indexRow, 5, report);
                                    addDataToCell(row["Summa"], indexRow, 6, report);
                                    addDataToCell(row["nameToTenant"], indexRow, 7, report);
                                    addDataToCell(row["PlaneDate"], indexRow, 8, report);

                                    report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                    indexRow++;
                                }
                            }
                        }

                        Rows = rowCollect.Where(r => !r.Field <bool>("isCash"));
                        if (Rows.Count() > 0)
                        {
                            report.Merge(indexRow, 1, indexRow, maxColumns);
                            report.AddSingleValue("Безналичный расчёт", indexRow, 1);
                            report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                            report.SetBorders(indexRow, 1, indexRow, maxColumns);
                            report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                            indexRow++;

                            var gData = Rows.AsEnumerable().GroupBy(g => new { Date = g.Field <DateTime>("Date") }).Select(s => new { s.Key.Date });
                            foreach (var rData in gData)
                            {
                                report.Merge(indexRow, 1, indexRow, maxColumns);
                                report.AddSingleValue($"{rData.Date.ToShortDateString()}", indexRow, 1);
                                report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                                report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                indexRow++;

                                foreach (DataRow row in Rows.AsEnumerable().Where(r => r.Field <DateTime>("Date").Date == rData.Date))
                                {
                                    report.SetWrapText(indexRow, 1, indexRow, maxColumns);

                                    addDataToCell(row["nameTenant"], indexRow, 1, report);
                                    addDataToCell(row["nameLandLord"], indexRow, 2, report);
                                    addDataToCell(row["Agreement"], indexRow, 3, report);
                                    addDataToCell(row["namePlace"], indexRow, 4, report);

                                    addDataToCell(row["Date"], indexRow, 5, report);
                                    addDataToCell(row["Summa"], indexRow, 6, report);
                                    addDataToCell(row["nameToTenant"], indexRow, 7, report);
                                    addDataToCell(row["PlaneDate"], indexRow, 8, report);

                                    report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                    indexRow++;
                                }
                            }
                        }
                    }
                    #endregion

                    #region "Дополнительная оплата"
                    if (i == 3)
                    {
                        maxColumns = 10;
                        setWidthColumn(indexRow, 5, 17, report);
                        setWidthColumn(indexRow, 6, 13, report);
                        setWidthColumn(indexRow, 7, 12, report);
                        setWidthColumn(indexRow, 8, 12, report);
                        setWidthColumn(indexRow, 9, 15, report);
                        setWidthColumn(indexRow, 10, 13, report);


                        report.AddSingleValue("Тип доп оплаты", indexRow, 5);
                        report.AddSingleValue("Дата выписки оплаты", indexRow, 6);
                        report.AddSingleValue("Дата оплаты", indexRow, 7);
                        report.AddSingleValue("Сумма оплаты", indexRow, 8);
                        report.AddSingleValue("Тип операции", indexRow, 9);
                        report.AddSingleValue("План в который попадает оплата", indexRow, 10);


                        report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                        report.SetBorders(indexRow, 1, indexRow, maxColumns);
                        report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                        indexRow++;

                        EnumerableRowCollection <DataRow> Rows = rowCollect.Where(r => r.Field <bool>("isCash"));
                        if (Rows.Count() > 0)
                        {
                            report.Merge(indexRow, 1, indexRow, maxColumns);
                            report.AddSingleValue("Наличные", indexRow, 1);
                            report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                            report.SetBorders(indexRow, 1, indexRow, maxColumns);
                            report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                            indexRow++;

                            var gData = Rows.AsEnumerable().GroupBy(g => new { Date = g.Field <DateTime>("Date") }).Select(s => new { s.Key.Date });
                            foreach (var rData in gData)
                            {
                                report.Merge(indexRow, 1, indexRow, maxColumns);
                                report.AddSingleValue($"{rData.Date.ToShortDateString()}", indexRow, 1);
                                report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                                report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                indexRow++;

                                foreach (DataRow row in Rows.AsEnumerable().Where(r => r.Field <DateTime>("Date").Date == rData.Date))
                                {
                                    report.SetWrapText(indexRow, 1, indexRow, maxColumns);

                                    addDataToCell(row["nameTenant"], indexRow, 1, report);
                                    addDataToCell(row["nameLandLord"], indexRow, 2, report);
                                    addDataToCell(row["Agreement"], indexRow, 3, report);
                                    addDataToCell(row["namePlace"], indexRow, 4, report);


                                    addDataToCell(row["nameAddPayment"], indexRow, 5, report);
                                    addDataToCell(row["DateFines"], indexRow, 6, report);

                                    addDataToCell(row["Date"], indexRow, 7, report);
                                    addDataToCell(row["Summa"], indexRow, 8, report);
                                    addDataToCell(row["nameToTenant"], indexRow, 9, report);
                                    addDataToCell(row["PlaneDate"], indexRow, 10, report);

                                    report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                    indexRow++;
                                }
                            }
                        }

                        Rows = rowCollect.Where(r => !r.Field <bool>("isCash"));
                        if (Rows.Count() > 0)
                        {
                            report.Merge(indexRow, 1, indexRow, maxColumns);
                            report.AddSingleValue("Безналичный расчёт", indexRow, 1);
                            report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                            report.SetBorders(indexRow, 1, indexRow, maxColumns);
                            report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                            indexRow++;

                            var gData = Rows.AsEnumerable().GroupBy(g => new { Date = g.Field <DateTime>("Date") }).Select(s => new { s.Key.Date });
                            foreach (var rData in gData)
                            {
                                report.Merge(indexRow, 1, indexRow, maxColumns);
                                report.AddSingleValue($"{rData.Date.ToShortDateString()}", indexRow, 1);
                                report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                                report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                indexRow++;

                                foreach (DataRow row in Rows.AsEnumerable().Where(r => r.Field <DateTime>("Date").Date == rData.Date))
                                {
                                    report.SetWrapText(indexRow, 1, indexRow, maxColumns);

                                    addDataToCell(row["nameTenant"], indexRow, 1, report);
                                    addDataToCell(row["nameLandLord"], indexRow, 2, report);
                                    addDataToCell(row["Agreement"], indexRow, 3, report);
                                    addDataToCell(row["namePlace"], indexRow, 4, report);

                                    addDataToCell(row["nameAddPayment"], indexRow, 5, report);
                                    addDataToCell(row["DateFines"], indexRow, 6, report);

                                    addDataToCell(row["Date"], indexRow, 7, report);
                                    addDataToCell(row["Summa"], indexRow, 8, report);
                                    addDataToCell(row["nameToTenant"], indexRow, 9, report);
                                    addDataToCell(row["PlaneDate"], indexRow, 10, report);

                                    report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                    indexRow++;
                                }
                            }
                        }
                    }
                    #endregion

                    report.SetPageSetup(1, 9999, true);
                }

                report.Show();


                DoOnUIThread(() =>
                {
                    blockers.RestoreControlEnabledState(this);
                    progressBar1.Visible = false;
                }, this);

                return(true);
            });
        }
        private void addTabExcel(int id_otdel, string nameDep)
        {
            int indexRow   = 1;
            int maxColumns = 0;


            DataTable dtReport = dtData.DefaultView.ToTable();

            if (id_otdel != 0)
            {
                dtReport.DefaultView.RowFilter = $"id_otdel = {id_otdel}";
            }

            if (dtReport.DefaultView.Count == 0)
            {
                return;
            }

            if (report == null)
            {
                report = new Nwuram.Framework.ToExcelNew.ExcelUnLoad();
                report.changeNameTab($"{nameDep}");
            }
            else
            {
                report.GoToNextSheet($"{nameDep}");
            }
            Config.DoOnUIThread(() =>
            {
                foreach (DataGridViewColumn col in dgvData.Columns)
                {
                    if (col.Visible)
                    {
                        maxColumns++;
                        if (col.Name.Equals(cDeps.Name))
                        {
                            setWidthColumn(indexRow, maxColumns, 18, report);
                        }
                        if (col.Name.Equals(cEan.Name))
                        {
                            setWidthColumn(indexRow, maxColumns, 18, report);
                        }
                        if (col.Name.Equals(cName.Name))
                        {
                            setWidthColumn(indexRow, maxColumns, 30, report);
                        }
                        if (col.Name.Equals(cPriceK21.Name))
                        {
                            setWidthColumn(indexRow, maxColumns, 15, report);
                        }
                        if (col.Name.Equals(cPriceX14.Name))
                        {
                            setWidthColumn(indexRow, maxColumns, 17, report);
                        }
                        if (col.Name.Equals(cDelta.Name))
                        {
                            setWidthColumn(indexRow, maxColumns, 17, report);
                        }
                    }
                }
            }, this);

            #region "Head"
            report.Merge(indexRow, 1, indexRow, maxColumns);
            report.AddSingleValue($"Отчёт по расхождениям продажных цен по товарам", indexRow, 1);
            report.SetFontBold(indexRow, 1, indexRow, 1);
            report.SetFontSize(indexRow, 1, indexRow, 1, 16);
            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1);
            indexRow++;
            indexRow++;

            Config.DoOnUIThread(() =>
            {
                if (chbPeriod.Checked)
                {
                    report.Merge(indexRow, 1, indexRow, maxColumns);
                    report.AddSingleValue($"Продажи с {dtpStart.Value.ToShortDateString()} по {dtpEnd.Value.ToShortDateString()}", indexRow, 1);
                    indexRow++;
                }

                report.Merge(indexRow, 1, indexRow, maxColumns);
                report.AddSingleValue($"Отдел: {cmbDeps.Text}", indexRow, 1);
                indexRow++;

                report.Merge(indexRow, 1, indexRow, maxColumns);
                report.AddSingleValue($"Т/У группа: {cmbGrp1.Text}", indexRow, 1);
                indexRow++;

                report.Merge(indexRow, 1, indexRow, maxColumns);
                report.AddSingleValue($"Инв. группа: {cmbGrp2.Text}", indexRow, 1);
                indexRow++;

                if (tbEan.Text.Trim().Length != 0 || tbName.Text.Trim().Length != 0)
                {
                    report.Merge(indexRow, 1, indexRow, maxColumns);
                    report.AddSingleValue($"Фильтр: {(tbEan.Text.Trim().Length != 0 ? $"EAN:{tbEan.Text.Trim()} | " : "")} {(tbName.Text.Trim().Length != 0 ? $"Наименование:{tbName.Text.Trim()}" : "")}", indexRow, 1);
                    indexRow++;
                }

                report.Merge(indexRow, 1, indexRow, maxColumns);
                report.AddSingleValue("Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername, indexRow, 1);
                indexRow++;

                report.Merge(indexRow, 1, indexRow, maxColumns);
                report.AddSingleValue("Дата выгрузки: " + DateTime.Now.ToString(), indexRow, 1);
            }, this);
            indexRow++;
            indexRow++;
            #endregion

            int indexCol = 0;

            Config.DoOnUIThread(() =>
            {
                foreach (DataGridViewColumn col in dgvData.Columns)
                {
                    if (col.Visible)
                    {
                        indexCol++;
                        report.AddSingleValue(col.HeaderText, indexRow, indexCol);
                    }
                }
            }, this);
            report.SetFontBold(indexRow, 1, indexRow, maxColumns);
            report.SetBorders(indexRow, 1, indexRow, maxColumns);
            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
            report.SetWrapText(indexRow, 1, indexRow, maxColumns);
            indexRow++;



            foreach (DataRowView row in dtReport.DefaultView)
            {
                indexCol = 1;
                report.SetWrapText(indexRow, indexCol, indexRow, maxColumns);
                foreach (DataGridViewColumn col in dgvData.Columns)
                {
                    if (col.Visible)
                    {
                        if (row[col.DataPropertyName] is DateTime)
                        {
                            report.AddSingleValue(((DateTime)row[col.DataPropertyName]).ToShortDateString(), indexRow, indexCol);
                        }
                        else
                        if (row[col.DataPropertyName] is decimal || row[col.DataPropertyName] is double)
                        {
                            report.AddSingleValueObject(row[col.DataPropertyName], indexRow, indexCol);
                            report.SetFormat(indexRow, indexCol, indexRow, indexCol, "0.00");
                        }
                        else
                        {
                            report.AddSingleValue(row[col.DataPropertyName].ToString(), indexRow, indexCol);
                        }

                        indexCol++;
                    }
                }

                report.SetBorders(indexRow, 1, indexRow, maxColumns);
                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                Config.DoOnUIThread(() =>
                {
                    if (new List <int>(new int[] { 1, 3 }).Contains((int)row["ntypetovar"]))
                    {
                        report.SetCellColor(indexRow, 1, indexRow, maxColumns, pReserv.BackColor);
                    }
                    else
                    if ((bool)row["isPromo"])
                    {
                        report.SetCellColor(indexRow, 1, indexRow, maxColumns, pPromo.BackColor);
                    }
                }, this);
                indexRow++;
            }
            indexRow++;

            report.SetCellColor(indexRow, 1, indexRow, 1, pPromo.BackColor);
            report.Merge(indexRow, 2, indexRow, maxColumns);
            report.AddSingleValue($"{chbDiscount.Text}", indexRow, 2);
            indexRow++;

            report.SetCellColor(indexRow, 1, indexRow, 1, pReserv.BackColor);
            report.Merge(indexRow, 2, indexRow, maxColumns);
            report.AddSingleValue($"{chbReserv.Text}", indexRow, 2);
        }
Esempio n. 3
0
        private void btPrint_Click(object sender, EventArgs e)
        {
            if (rbErrorRealiz.Checked)
            {
                Task <DataTable> task = Parameters.hConnect.GetReportMainKass(dtpStart.Value.Date, dtpEnd.Value.Date, 1);
                task.Wait();
                if (task.Result == null || task.Result.Rows.Count == 0)
                {
                    MessageBox.Show("Нет данных для отчёта!", "Выгрузка отчёта", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                Logging.StartFirstLevel(489);
                Logging.Comment($"Период с {dtpStart.Value.ToShortDateString()} по {dtpEnd.Value.ToShortDateString()}");
                Logging.Comment($"Отчет по ошибкам в реализации");
                Logging.StopFirstLevel();


                DataTable dtReport = task.Result.Copy();

                Nwuram.Framework.ToExcelNew.ExcelUnLoad report = new Nwuram.Framework.ToExcelNew.ExcelUnLoad();
                //Nwuram.Framework.ToExcel.HandmadeReport report = new Nwuram.Framework.ToExcel.HandmadeReport();
                int indexRow = 1;

                report.Merge(indexRow, 1, indexRow, 7);
                report.AddSingleValue("Отчет по ошибкам в реализации", indexRow, 1);
                report.SetFontBold(indexRow, 1, indexRow, 1);
                report.SetFontSize(indexRow, 1, indexRow, 1, 16);
                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1);
                indexRow++;
                indexRow++;


                report.Merge(indexRow, 1, indexRow, 7);
                report.AddSingleValue($"Период с {dtpStart.Value.ToShortDateString()} по {dtpEnd.Value.ToShortDateString()}", indexRow, 1);
                indexRow++;

                report.Merge(indexRow, 1, indexRow, 7);
                report.AddSingleValue($"Магазин: {(ConnectionSettings.GetServer().Contains("K21") ? "K21" : "X14")}", indexRow, 1);
                indexRow++;

                report.Merge(indexRow, 1, indexRow, 7);
                report.AddSingleValue("Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername, indexRow, 1);
                indexRow++;

                report.Merge(indexRow, 1, indexRow, 7);
                report.AddSingleValue("Дата выгрузки: " + DateTime.Now.ToString(), indexRow, 1);
                indexRow++;
                indexRow++;


                report.SetColumnWidth(1, 1, 1, 1, 26);
                report.SetColumnWidth(1, 2, 1, 2, 20);
                report.SetColumnWidth(1, 3, 1, 3, 28);
                report.SetColumnWidth(1, 4, 1, 4, 21);
                report.SetColumnWidth(1, 5, 1, 5, 22);
                report.SetColumnWidth(1, 6, 1, 6, 22);
                report.SetColumnWidth(1, 7, 1, 7, 22);

                report.AddSingleValue("Дата расхождения", indexRow, 1);
                report.AddSingleValue("Расхождение", indexRow, 2);
                report.AddSingleValue("Дата заявки на ремонт", indexRow, 3);
                report.AddSingleValue("Номер заявки", indexRow, 4);
                report.AddSingleValue("Комментарий к заявке", indexRow, 5);
                report.AddSingleValue("Дата подтверждения", indexRow, 6);
                report.AddSingleValue("Описание ошибки", indexRow, 7);

                report.SetFontBold(indexRow, 1, indexRow, 7);
                report.SetBorders(indexRow, 1, indexRow, 7);
                report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 7);
                indexRow++;

                foreach (DataRow row in dtReport.Rows)
                {
                    report.SetBorders(indexRow, 1, indexRow, 7);
                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 7);
                    report.SetCellAlignmentToJustify(indexRow, 1, indexRow, 7);
                    report.SetWrapText(indexRow, 1, indexRow, 7);
                    report.AddSingleValue($"{((DateTime)row["Data"]).ToShortDateString()}", indexRow, 1);
                    report.AddSingleValue($"{row["nameType"]}", indexRow, 2);
                    report.AddSingleValue($"{row["DateSubmission"]}", indexRow, 3);
                    report.AddSingleValue($"{row["Number"]}", indexRow, 4);
                    report.AddSingleValue($"{row["Comment"]}", indexRow, 5);
                    report.AddSingleValue($"{row["DateConfirm"]}", indexRow, 6);
                    report.AddSingleValue($"{row["Fault"]}", indexRow, 7);
                    indexRow++;
                }

                report.Show();
            }
            else if (rbNullDataValidate.Checked)
            {
                Task <DataTable> task = Parameters.hConnect.GetReportMainKass(dtpStart.Value.Date, dtpEnd.Value.Date, 2);
                task.Wait();
                if (task.Result == null || task.Result.Rows.Count == 0)
                {
                    MessageBox.Show("Нет данных для отчёта!", "Выгрузка отчёта", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                Logging.StartFirstLevel(489);
                Logging.Comment($"Период с {dtpStart.Value.ToShortDateString()} по {dtpEnd.Value.ToShortDateString()}");
                Logging.Comment($"Отчет об отсутствии сверки данных");
                Logging.StopFirstLevel();

                DataTable dtReport = task.Result.Copy();

                task = Parameters.hConnect.GetRealizForReportMainKass(dtpStart.Value.Date, dtpEnd.Value.Date);
                task.Wait();
                DataTable dtRealiz = new DataTable();
                if (task.Result != null && task.Result.Rows.Count != 0)
                {
                    dtRealiz = task.Result.Copy();
                }


                task = Parameters.hConnectVVO.GetRealizForReportMainKass(dtpStart.Value.Date, dtpEnd.Value.Date);
                task.Wait();
                DataTable dtRealizVVO = new DataTable();
                if (task.Result != null && task.Result.Rows.Count != 0)
                {
                    dtRealizVVO = task.Result.Copy();
                }

                Nwuram.Framework.ToExcelNew.ExcelUnLoad report = new Nwuram.Framework.ToExcelNew.ExcelUnLoad();
                bool isFirstTab = true;
                for (int i = 3; i >= 1; i--)
                {
                    EnumerableRowCollection <DataRow> rowCollect = dtReport.AsEnumerable().Where(r => r.Field <int>("type") == i);
                    if (rowCollect.Count() == 0)
                    {
                        continue;
                    }
                    if (!isFirstTab)
                    {
                        report.GoToNextSheet();
                    }


                    int indexRow = 1;

                    report.Merge(indexRow, 1, indexRow, 3);
                    report.AddSingleValue("Отчет об отсутствии сверки данных", indexRow, 1);
                    report.SetFontBold(indexRow, 1, indexRow, 1);
                    report.SetFontSize(indexRow, 1, indexRow, 1, 16);
                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1);
                    indexRow++;
                    indexRow++;


                    report.Merge(indexRow, 1, indexRow, 3);
                    report.AddSingleValue($"Период с {dtpStart.Value.ToShortDateString()} по {dtpEnd.Value.ToShortDateString()}", indexRow, 1);
                    indexRow++;

                    report.Merge(indexRow, 1, indexRow, 3);
                    report.AddSingleValue($"Магазин: {(ConnectionSettings.GetServer().Contains("K21") ? "K21" : "X14")}", indexRow, 1);
                    indexRow++;

                    report.Merge(indexRow, 1, indexRow, 3);
                    report.AddSingleValue("Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername, indexRow, 1);
                    indexRow++;

                    report.Merge(indexRow, 1, indexRow, 3);
                    report.AddSingleValue("Дата выгрузки: " + DateTime.Now.ToString(), indexRow, 1);
                    indexRow++;
                    indexRow++;


                    report.SetColumnWidth(1, 1, 1, 1, 26);
                    report.SetColumnWidth(1, 2, 1, 2, 20);
                    report.SetColumnWidth(1, 3, 1, 3, 28);

                    report.AddSingleValue("Причина", indexRow, 1);
                    report.AddSingleValue("Дата", indexRow, 2);
                    report.AddSingleValue("Отдел", indexRow, 3);

                    report.SetFontBold(indexRow, 1, indexRow, 3);
                    report.SetBorders(indexRow, 1, indexRow, 3);
                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 3);
                    indexRow++;

                    #region "Нет данных «Главная касса»"
                    if (i == 1)
                    {
                        //EnumerableRowCollection<DataRow> rowCollect = dtReport.AsEnumerable().Where(r => r.Field<int>("type") == 1);
                        if (rowCollect.Count() > 0)
                        {
                            if (isFirstTab)
                            {
                                isFirstTab = false; report.changeNameTab("Нет данных «Главная касса»");
                            }
                            else
                            {
                                report.changeNameTab("Нет данных «Главная касса»");
                            }

                            int headerStart = indexRow;

                            for (DateTime ii11 = dtpStart.Value; ii11 <= dtpEnd.Value; ii11 = ii11.AddDays(1))
                            {
                                int  midStart = indexRow;
                                bool showData = false;


                                if (rowCollect.AsEnumerable().Where(r => !r.Field <bool>("isVVO") && r.Field <DateTime>("Data").Date == ii11.Date).Count() == 0)
                                {
                                    showData = true;
                                    report.AddSingleValue("Все отделы, кроме ВВО", indexRow, 3);
                                    indexRow++;
                                }

                                if (rowCollect.AsEnumerable().Where(r => r.Field <bool>("isVVO") && r.Field <DateTime>("Data").Date == ii11.Date).Count() == 0)
                                {
                                    showData = true;
                                    report.AddSingleValue("Отдел ВВО", indexRow, 3);
                                    indexRow++;
                                }

                                if (showData)
                                {
                                    report.Merge(midStart, 2, indexRow - 1, 2);
                                    report.AddSingleValue($"{ii11.ToShortDateString()}", midStart, 2);
                                    report.SetCellAlignmentToCenter(midStart, 2, indexRow - 1, 2);
                                    report.SetCellAlignmentToJustify(midStart, 2, indexRow - 1, 2);
                                }
                            }

                            if (indexRow > headerStart)
                            {
                                report.Merge(headerStart, 1, indexRow - 1, 1);
                                report.SetBorders(headerStart, 1, indexRow - 1, 3);
                                report.SetCellAlignmentToCenter(headerStart, 1, indexRow - 1, 1);
                                report.SetCellAlignmentToJustify(headerStart, 1, indexRow - 1, 1);
                                report.SetWrapText(headerStart, 1, indexRow - 1, 1);
                                report.AddSingleValue($"Нет данных «Главная касса»", headerStart, 1);
                            }
                        }
                    }
                    #endregion

                    #region "Нет сверки с «Реал. SQL»"
                    if (i == 2)
                    {
                        //EnumerableRowCollection<DataRow> rowCollect = dtReport.AsEnumerable().Where(r => r.Field<int>("type") == 2);
                        if (rowCollect.Count() > 0)
                        {
                            if (isFirstTab)
                            {
                                isFirstTab = false; report.changeNameTab("Нет сверки с «Реал. SQL»");
                            }
                            else
                            {
                                report.changeNameTab("Нет сверки с «Реал. SQL»");
                            }
                            int headerStart = indexRow;

                            var groupData = rowCollect.AsEnumerable()
                                            .GroupBy(g => new { date = g.Field <DateTime>("Data") })
                                            .Select(s => new { s.Key.date });
                            foreach (var gDate in groupData)
                            {
                                int  midStart = indexRow;
                                bool showData = false;


                                foreach (DataRow row in rowCollect.AsEnumerable().Where(r => !r.Field <bool>("isVVO") && r.Field <DateTime>("Data").Date == gDate.date.Date))
                                {
                                    showData = true;
                                    report.AddSingleValue("Все отделы, кроме ВВО", indexRow, 3);
                                    indexRow++;
                                }

                                foreach (DataRow row in rowCollect.AsEnumerable().Where(r => r.Field <bool>("isVVO") && r.Field <DateTime>("Data").Date == gDate.date.Date))
                                {
                                    showData = true;
                                    report.AddSingleValue("Отдел ВВО", indexRow, 3);
                                    indexRow++;
                                }

                                if (showData)
                                {
                                    report.Merge(midStart, 2, indexRow - 1, 2);
                                    report.AddSingleValue($"{gDate.date.ToShortDateString()}", midStart, 2);
                                    report.SetCellAlignmentToCenter(midStart, 2, indexRow - 1, 2);
                                    report.SetCellAlignmentToJustify(midStart, 2, indexRow - 1, 2);
                                }
                            }

                            if (indexRow > headerStart)
                            {
                                report.Merge(headerStart, 1, indexRow - 1, 1);
                                report.AddSingleValue($"Нет сверки с «Реал. SQL»", headerStart, 1);
                                report.SetBorders(headerStart, 1, indexRow - 1, 3);
                                report.SetCellAlignmentToCenter(headerStart, 1, indexRow - 1, 1);
                                report.SetCellAlignmentToJustify(headerStart, 1, indexRow - 1, 1);
                            }
                        }
                    }
                    #endregion

                    #region "Данные «Реал. SQL» отличаются от сохраненных"
                    if (i == 3)
                    {
                        //EnumerableRowCollection<DataRow> rowCollect = dtReport.AsEnumerable().Where(r => r.Field<int>("type") == 3);
                        if (rowCollect.Count() > 0)
                        {
                            if (isFirstTab)
                            {
                                isFirstTab = false; report.changeNameTab("Данные «Реал. SQL» отличаются от сохраненных");
                            }
                            else
                            {
                                report.changeNameTab("Данные «Реал. SQL» отличаются от сохраненных");
                            }
                            int headerStart = indexRow;

                            var groupData = rowCollect.AsEnumerable()
                                            .GroupBy(g => new { date = g.Field <DateTime>("Data") })
                                            .Select(s => new { s.Key.date });
                            foreach (var gDate in groupData)
                            {
                                int  midStart = indexRow;
                                bool showData = false;

                                foreach (DataRow row in rowCollect.AsEnumerable().Where(r => !r.Field <bool>("isVVO") && r.Field <DateTime>("Data").Date == gDate.date.Date))
                                {
                                    if (dtRealiz.Rows.Count > 0)
                                    {
                                        if (dtRealiz.AsEnumerable().Where(r => r.Field <DateTime>("dreal").Date == gDate.date.Date && r.Field <decimal>("RealSql") == (decimal)row["MainKass"]).Count() == 0)
                                        {
                                            showData = true;
                                            report.AddSingleValue("Все отделы, кроме ВВО", indexRow, 3);
                                            indexRow++;
                                        }
                                    }
                                    else
                                    {
                                        showData = true;
                                        report.AddSingleValue("Все отделы, кроме ВВО", indexRow, 3);
                                        indexRow++;
                                    }
                                }

                                foreach (DataRow row in rowCollect.AsEnumerable().Where(r => r.Field <bool>("isVVO") && r.Field <DateTime>("Data").Date == gDate.date.Date))
                                {
                                    if (dtRealizVVO.Rows.Count > 0)
                                    {
                                        if (dtRealizVVO.AsEnumerable().Where(r => r.Field <DateTime>("dreal").Date == gDate.date.Date && r.Field <decimal>("RealSql") == (decimal)row["MainKass"]).Count() == 0)
                                        {
                                            showData = true;
                                            report.AddSingleValue("Отдел ВВО", indexRow, 3);
                                            indexRow++;
                                        }
                                    }
                                    else
                                    {
                                        showData = true;
                                        report.AddSingleValue("Отдел ВВО", indexRow, 3);
                                        indexRow++;
                                    }
                                }

                                if (showData)
                                {
                                    report.Merge(midStart, 2, indexRow - 1, 2);
                                    report.AddSingleValue($"{gDate.date.ToShortDateString()}", midStart, 2);
                                    report.SetCellAlignmentToCenter(midStart, 2, indexRow - 1, 2);
                                    report.SetCellAlignmentToJustify(midStart, 2, indexRow - 1, 2);
                                }
                            }

                            if (indexRow > headerStart)
                            {
                                report.Merge(headerStart, 1, indexRow - 1, 1);
                                report.SetBorders(headerStart, 1, indexRow - 1, 3);
                                report.SetCellAlignmentToCenter(headerStart, 1, indexRow - 1, 1);
                                report.SetCellAlignmentToJustify(headerStart, 1, indexRow - 1, 1);
                                report.SetWrapText(headerStart, 1, indexRow - 1, 1);
                                report.AddSingleValue($"Данные «Реал. SQL» отличаются от сохраненных", headerStart, 1);
                            }
                        }
                    }
                    #endregion
                }

                report.Show();
            }
        }
Esempio n. 4
0
        private async void btnPrint_Click(object sender, EventArgs e)
        {
            if (!chbCancel.Checked && !chbComplete.Checked && !chbЕmployesОrder.Checked)
            {
                return;
            }
            int id_Shop = (int)cmbShop.SelectedValue;
            var outer   = Task.Factory.StartNew(() =>    // внешняя задача
            {
                Config.DoOnUIThread(() =>
                {
                    blockers.SaveControlsEnabledState(this);
                    blockers.SetControlsEnabled(this, false);
                    //progressBar1.Visible = progressBar1.Enabled = true;
                    fLoad          = new Nwuram.Framework.UI.Forms.frmLoad();
                    fLoad.TopMost  = false;
                    fLoad.Owner    = this;
                    fLoad.TextWait = "Грузим отчётик!";
                    fLoad.Show();
                }, this);

                Nwuram.Framework.ToExcelNew.ExcelUnLoad report = new Nwuram.Framework.ToExcelNew.ExcelUnLoad();
                bool isShow = false;


                if (chbComplete.Checked)
                {
                    DataTable dtDataStatus3     = Config.connect.getReportData(dtpStart.Value, dtpEnd.Value, 3, id_Shop);
                    DataTable dtDataStatus3Body = (id_Shop == 1 ? Config.connect : Config.hCntSecond).getSumNotesOrderWithRCena(dtpStart.Value, dtpEnd.Value, 3, id_Shop);

                    if (dtDataStatus3 != null && dtDataStatus3.Rows.Count > 0)
                    {
                        isShow = true;
                        report.changeNameTab("Выполненные");

                        decimal sumOrder = 0, sumNote = 0, SummaDelivery = 0, sumPackage = 0, DeliveryCost = 0, valueOst = 0, valueDelta = 0;


                        int indexRow   = 1;
                        int maxColumns = 12;

                        setWidthColumn(indexRow, 1, 6, report);
                        setWidthColumn(indexRow, 2, 10, report);
                        setWidthColumn(indexRow, 3, 17, report);
                        setWidthColumn(indexRow, 4, 11, report);
                        setWidthColumn(indexRow, 5, 16, report);
                        setWidthColumn(indexRow, 6, 22, report);
                        setWidthColumn(indexRow, 7, 16, report);
                        setWidthColumn(indexRow, 8, 12, report);
                        setWidthColumn(indexRow, 9, 14, report);
                        setWidthColumn(indexRow, 10, 16, report);
                        setWidthColumn(indexRow, 11, 16, report);
                        setWidthColumn(indexRow, 12, 13, report);

                        #region "Head"
                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue($"Отчёт об выполненных заказах с {dtpStart.Value.ToShortDateString()} по {dtpEnd.Value.ToShortDateString()}", indexRow, 1);
                        report.SetFontBold(indexRow, 1, indexRow, 1);
                        report.SetFontSize(indexRow, 1, indexRow, 1, 16);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1);
                        indexRow++;
                        indexRow++;

                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue("Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername, indexRow, 1);
                        indexRow++;

                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue("Дата выгрузки: " + DateTime.Now.ToString(), indexRow, 1);
                        indexRow++;
                        indexRow++;
                        #endregion

                        report.AddSingleValue("№ п/п", indexRow, 1);
                        report.AddSingleValue("Номер заказа", indexRow, 2);
                        report.AddSingleValue("Сумма заказа", indexRow, 3);
                        report.AddSingleValue("Дата доставки", indexRow, 4);
                        report.AddSingleValue("Сумма чека", indexRow, 5);
                        report.AddSingleValue("№ чека", indexRow, 6);
                        report.AddSingleValue("Стоимость доставки", indexRow, 7);
                        report.AddSingleValue("Кол-во пакетов", indexRow, 8);
                        report.AddSingleValue("Стоимость пакетов", indexRow, 9);
                        report.AddSingleValue("Затраты на доставку", indexRow, 10);
                        report.AddSingleValue("Остаток по доставке", indexRow, 11);
                        report.AddSingleValue("∆ по чеку", indexRow, 12);


                        report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                        report.SetBorders(indexRow, 1, indexRow, maxColumns);
                        report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                        indexRow++;

                        int npp = 1;

                        var groupOrder = dtDataStatus3.AsEnumerable()
                                         .GroupBy(r => new { id_tOrders = r.Field <int>("id_tOrders") })
                                         .Select(g => new
                        {
                            g.Key.id_tOrders,
                            sumNote = g.Sum(r => r.Field <decimal>("sumNote"))
                        });

                        foreach (var g in groupOrder)
                        {
                            EnumerableRowCollection <DataRow> rowCollect = dtDataStatus3.AsEnumerable().Where(r => r.Field <int>("id_tOrders") == g.id_tOrders);
                            DataRow row  = rowCollect.First();
                            int startRow = indexRow;
                            report.SetWrapText(indexRow, 1, indexRow + rowCollect.Count() - 1, maxColumns);
                            report.Merge(indexRow, 1, indexRow + rowCollect.Count() - 1, 1);
                            report.Merge(indexRow, 2, indexRow + rowCollect.Count() - 1, 2);
                            report.Merge(indexRow, 3, indexRow + rowCollect.Count() - 1, 3);
                            report.Merge(indexRow, 4, indexRow + rowCollect.Count() - 1, 4);
                            report.Merge(indexRow, 5, indexRow + rowCollect.Count() - 1, 5);

                            report.Merge(indexRow, 7, indexRow + rowCollect.Count() - 1, 7);
                            report.Merge(indexRow, 8, indexRow + rowCollect.Count() - 1, 8);
                            report.Merge(indexRow, 9, indexRow + rowCollect.Count() - 1, 9);
                            report.Merge(indexRow, 10, indexRow + rowCollect.Count() - 1, 10);
                            report.Merge(indexRow, 11, indexRow + rowCollect.Count() - 1, 11);
                            report.Merge(indexRow, 12, indexRow + rowCollect.Count() - 1, 12);



                            report.AddSingleValue(npp.ToString(), indexRow, 1);
                            report.AddSingleValue(row["OrderNumber"].ToString(), indexRow, 2);
                            report.AddSingleValueObject(row["sumOrder"], indexRow, 3);
                            report.SetFormat(indexRow, 3, indexRow, 3, "0.00");
                            report.AddSingleValue(((DateTime)row["DeliveryDate"]).ToShortDateString(), indexRow, 4);

                            report.AddSingleValueObject(g.sumNote, indexRow, 5);
                            report.SetFormat(indexRow, 5, indexRow, 5, "0.00");

                            report.AddSingleValueObject(row["SummaDelivery"], indexRow, 7);
                            report.SetFormat(indexRow, 7, indexRow, 7, "0.00");

                            report.AddSingleValueObject(row["CountPackage"], indexRow, 8);
                            report.SetFormat(indexRow, 8, indexRow, 8, "0.00");

                            report.AddSingleValueObject(row["sumPackage"], indexRow, 9);
                            report.SetFormat(indexRow, 9, indexRow, 9, "0.00");

                            report.AddSingleValueObject(row["DeliveryCost"], indexRow, 10);
                            report.SetFormat(indexRow, 10, indexRow, 10, "0.00");


                            sumOrder      += (decimal)row["sumOrder"];
                            sumNote       += g.sumNote;
                            SummaDelivery += (decimal)row["SummaDelivery"];
                            sumPackage    += (decimal)row["sumPackage"];
                            DeliveryCost  += (decimal)row["DeliveryCost"];

                            decimal value = (decimal)row["SummaDelivery"] - (decimal)row["sumPackage"] - (decimal)row["DeliveryCost"];
                            valueOst     += value;
                            report.AddSingleValueObject(value, indexRow, 11);
                            report.SetFormat(indexRow, 11, indexRow, 11, "0.00");

                            if (dtDataStatus3Body != null)
                            {
                                decimal sum = dtDataStatus3Body.AsEnumerable().Where(r => r.Field <int>("id_tOrders") == (int)row["id_tOrders"]).Sum(r => r.Field <decimal>("resultSum"));
                                report.AddSingleValueObject(g.sumNote - sum, indexRow, 12);
                                valueDelta += g.sumNote - sum;
                            }
                            else
                            {
                                report.AddSingleValueObject(0, indexRow, 12);
                            }
                            report.SetFormat(indexRow, 12, indexRow, 12, "0.00");

                            foreach (DataRow rowSelect in rowCollect)
                            {
                                //report.AddSingleValueObject(rowSelect["sumNote"], indexRow, 5);
                                //report.SetFormat(indexRow, 5, indexRow, 5, "0.00");
                                report.AddSingleValue($"Касса:{rowSelect["KassNumber"]} Чек:{rowSelect["CheckNumber"]}", indexRow, 6);
                                indexRow++;
                            }



                            report.SetBorders(startRow, 1, indexRow - 1, maxColumns);
                            report.SetCellAlignmentToCenter(startRow, 1, indexRow - 1, maxColumns);
                            report.SetCellAlignmentToJustify(startRow, 1, indexRow - 1, maxColumns);
                            //indexRow++;
                            npp++;
                        }


                        report.SetFormat(indexRow, 2, indexRow, maxColumns, "0.00");

                        report.AddSingleValue($"Итого:", indexRow, 1);
                        report.AddSingleValueObject(sumOrder, indexRow, 3);
                        report.AddSingleValueObject(sumNote, indexRow, 5);
                        report.AddSingleValueObject(SummaDelivery, indexRow, 7);
                        report.AddSingleValueObject(sumPackage, indexRow, 9);
                        report.AddSingleValueObject(DeliveryCost, indexRow, 10);
                        report.AddSingleValueObject(valueOst, indexRow, 11);
                        report.AddSingleValueObject(valueDelta, indexRow, 12);

                        report.SetBorders(indexRow, 1, indexRow, maxColumns);
                        report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                    }
                }

                if (chbCancel.Checked)
                {
                    DataTable dtData = Config.connect.getReportData(dtpStart.Value, dtpEnd.Value, 4, id_Shop);
                    if (dtData != null && dtData.Rows.Count > 0)
                    {
                        if (!isShow)
                        {
                            isShow = true;
                            report.changeNameTab("Отменённые");
                        }
                        else
                        {
                            report.GoToNextSheet("Отменённые");
                        }

                        int indexRow     = 1;
                        int maxColumns   = 9;
                        decimal sumOrder = 0;

                        setWidthColumn(indexRow, 1, 9, report);
                        setWidthColumn(indexRow, 2, 20, report);
                        setWidthColumn(indexRow, 3, 20, report);
                        setWidthColumn(indexRow, 4, 16, report);
                        setWidthColumn(indexRow, 5, 20, report);
                        setWidthColumn(indexRow, 6, 18, report);
                        setWidthColumn(indexRow, 7, 16, report);
                        setWidthColumn(indexRow, 8, 16, report);
                        setWidthColumn(indexRow, 9, 40, report);

                        #region "Head"
                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue($"Отчёт об отменённых заказах с {dtpStart.Value.ToShortDateString()} по {dtpEnd.Value.ToShortDateString()}", indexRow, 1);
                        report.SetFontBold(indexRow, 1, indexRow, 1);
                        report.SetFontSize(indexRow, 1, indexRow, 1, 16);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1);
                        indexRow++;
                        indexRow++;

                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue("Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername, indexRow, 1);
                        indexRow++;

                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue("Дата выгрузки: " + DateTime.Now.ToString(), indexRow, 1);
                        indexRow++;
                        indexRow++;
                        #endregion

                        report.AddSingleValue("№ п/п", indexRow, 1);
                        report.AddSingleValue("ФИО", indexRow, 2);
                        report.AddSingleValue("Адрес", indexRow, 3);
                        report.AddSingleValue("Телефон", indexRow, 4);
                        report.AddSingleValue("e-mail", indexRow, 5);
                        report.AddSingleValue("Номер заказа", indexRow, 6);
                        report.AddSingleValue("Дата заказа", indexRow, 7);
                        report.AddSingleValue("Сумма заказа", indexRow, 8);
                        report.AddSingleValue("Комментарий об отмене заказа", indexRow, 9);

                        report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                        report.SetBorders(indexRow, 1, indexRow, maxColumns);
                        report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                        indexRow++;

                        int npp = 1;

                        foreach (DataRowView row in dtData.DefaultView)
                        {
                            report.SetWrapText(indexRow, 1, indexRow, maxColumns);

                            report.AddSingleValue(npp.ToString(), indexRow, 1);
                            report.AddSingleValue(row["FIO"].ToString(), indexRow, 2);
                            report.AddSingleValue(row["Address"].ToString(), indexRow, 3);
                            report.AddSingleValue(row["Phone"].ToString(), indexRow, 4);
                            report.AddSingleValue(row["Email"].ToString(), indexRow, 5);
                            report.AddSingleValue(row["OrderNumber"].ToString(), indexRow, 6);
                            report.AddSingleValue(((DateTime)row["DateOrder"]).ToShortDateString(), indexRow, 7);
                            report.AddSingleValueObject(row["sumOrder"], indexRow, 8);
                            report.SetFormat(indexRow, 4, indexRow, 4, "0.00");
                            report.AddSingleValue(row["Comment"].ToString(), indexRow, 9);

                            sumOrder += (decimal)row["sumOrder"];

                            report.SetBorders(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                            indexRow++;
                            npp++;
                        }

                        report.SetFormat(indexRow, 2, indexRow, maxColumns, "0.00");

                        report.AddSingleValue($"Итого:", indexRow, 1);
                        report.AddSingleValueObject(sumOrder, indexRow, 8);

                        report.SetBorders(indexRow, 1, indexRow, maxColumns);
                        report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                    }
                }

                if (chbЕmployesОrder.Checked)
                {
                    DataTable dtData = Config.connect.reportЕmployesОrder(dtpStart.Value, dtpEnd.Value, id_Shop);
                    if (dtData != null && dtData.Rows.Count > 0)
                    {
                        if (!isShow)
                        {
                            isShow = true;
                            report.changeNameTab("Отчет по сотрудникам");
                        }
                        else
                        {
                            report.GoToNextSheet("Отчет по сотрудникам");
                        }


                        int indexRow     = 1;
                        int maxColumns   = 8;
                        decimal sumOrder = 0;

                        setWidthColumn(indexRow, 1, 9, report);
                        setWidthColumn(indexRow, 2, 25, report);
                        setWidthColumn(indexRow, 3, 16, report);
                        setWidthColumn(indexRow, 4, 16, report);
                        setWidthColumn(indexRow, 5, 16, report);
                        setWidthColumn(indexRow, 6, 12, report);
                        setWidthColumn(indexRow, 7, 12, report);
                        setWidthColumn(indexRow, 8, 12, report);

                        #region "Head"
                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue($"Отчет по сотрудникам с {dtpStart.Value.ToShortDateString()} по {dtpEnd.Value.ToShortDateString()}", indexRow, 1);
                        report.SetFontBold(indexRow, 1, indexRow, 1);
                        report.SetFontSize(indexRow, 1, indexRow, 1, 16);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, 1);
                        indexRow++;
                        indexRow++;

                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue("Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername, indexRow, 1);
                        indexRow++;

                        report.Merge(indexRow, 1, indexRow, maxColumns);
                        report.AddSingleValue("Дата выгрузки: " + DateTime.Now.ToString(), indexRow, 1);
                        indexRow++;
                        indexRow++;
                        #endregion

                        report.AddSingleValue("№ п/п", indexRow, 1);
                        report.AddSingleValue("ФИО сотрудника", indexRow, 2);
                        report.AddSingleValue("Дата заказа", indexRow, 3);
                        report.AddSingleValue("Номер заказа", indexRow, 4);
                        report.AddSingleValue("Позиций в заказе", indexRow, 5);
                        report.AddSingleValue("Сборщик", indexRow, 6);
                        report.AddSingleValue("Пробитие", indexRow, 7);
                        report.AddSingleValue("Доставщик", indexRow, 8);

                        report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                        report.SetBorders(indexRow, 1, indexRow, maxColumns);
                        report.SetWrapText(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                        report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                        indexRow++;


                        var groupCountCollector = dtData.AsEnumerable()
                                                  .Where(r => r.Field <bool>("Collector"))
                                                  .GroupBy(g => new { OrderNumber = g.Field <int>("OrderNumber") })
                                                  .Select(s => new { s.Key.OrderNumber, countCollector = s.Count() });

                        var groupKadr = dtData.AsEnumerable().GroupBy(r => new { id_Kadr = r.Field <int>("id_Kadr"), FIO = r.Field <string>("FIO") }).Select(s => new { s.Key.id_Kadr, s.Key.FIO });


                        int npp          = 1;
                        Color cCompleted = Color.FromArgb(244, 255, 96);
                        foreach (var gKadr in groupKadr)
                        {
                            EnumerableRowCollection <DataRow> rowCollect = dtData.AsEnumerable().Where(r => r.Field <int>("id_Kadr") == gKadr.id_Kadr).OrderBy(r => r.Field <int>("OrderNumber"));
                            report.Merge(indexRow, 2, indexRow + rowCollect.Count() - 1, 2);
                            setValueToCell(indexRow, 2, gKadr.FIO, report);
                            var groupDate = rowCollect.AsEnumerable().GroupBy(g => new { DeliveryDate = g.Field <DateTime>("DeliveryDate") }).Select(s => new { s.Key.DeliveryDate });

                            decimal countCollector = 0;
                            int countKassCheck     = 0;
                            int countDelivery      = 0;

                            foreach (var gDate in groupDate)
                            {
                                EnumerableRowCollection <DataRow> rowCollectDate = rowCollect.Where(r => r.Field <DateTime>("DeliveryDate") == gDate.DeliveryDate);

                                report.Merge(indexRow, 3, indexRow + rowCollectDate.Count() - 1, 3);
                                setValueToCell(indexRow, 3, gDate.DeliveryDate, report);

                                foreach (DataRow row in rowCollectDate)
                                {
                                    report.SetWrapText(indexRow, 1, indexRow, maxColumns);

                                    setValueToCell(indexRow, 1, npp, report);
                                    setValueToCell(indexRow, 4, row["OrderNumber"], report);
                                    setValueToCell(indexRow, 5, row["countRow"], report);
                                    if ((bool)row["Collector"])
                                    {
                                        decimal valResult = Convert.ToDecimal((int)row["countRow"]) / Convert.ToDecimal(groupCountCollector.Where(r => r.OrderNumber == (int)row["OrderNumber"]).First().countCollector);
                                        countCollector   += valResult;
                                        setValueToCell(indexRow, 6, valResult, report);
                                    }
                                    if ((bool)row["KassCheck"])
                                    {
                                        setValueToCell(indexRow, 7, row["KassCheck"], report);
                                        countKassCheck++;
                                    }
                                    if ((bool)row["Delivery"])
                                    {
                                        setValueToCell(indexRow, 8, row["Delivery"], report);
                                        countDelivery++;
                                    }
                                    if (!(bool)row["isCompleted"])
                                    {
                                        report.SetCellColor(indexRow, 4, indexRow, maxColumns, cCompleted);
                                    }
                                    report.SetBorders(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                                    report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                                    indexRow++;
                                    npp++;
                                }
                            }


                            report.Merge(indexRow, 1, indexRow, 5);
                            setValueToCell(indexRow, 1, $"Итого:", report);
                            setValueToCell(indexRow, 6, countCollector, report);
                            setValueToCell(indexRow, 7, countKassCheck, report);
                            setValueToCell(indexRow, 8, countDelivery, report);

                            report.SetBorders(indexRow, 1, indexRow, maxColumns);
                            report.SetFontBold(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToCenter(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToJustify(indexRow, 1, indexRow, maxColumns);
                            report.SetCellAlignmentToRight(indexRow, 1, indexRow, 1);
                            //report.SetCellAlignmentToRight(indexRow, 6, indexRow, maxColumns);

                            indexRow++;
                        }
                        report.SetCellColor(indexRow + 1, 1, indexRow + 1, 1, cCompleted);
                        setValueToCell(indexRow + 1, 2, "-заказ не выполнен", report);
                    }
                }

                Config.DoOnUIThread(() =>
                {
                    blockers.RestoreControlEnabledState(this);
                    fLoad.Dispose();
                }, this);

                if (isShow)
                {
                    report.SetPageSetup(1, 9999, true);
                    report.Show();
                }
                else
                {
                    MessageBox.Show("Нет данных для отчёта", "Выгрузка отчёта", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

                #region логирование
                Logging.StartFirstLevel(79);
                Logging.Comment("Произведена выгрузка в Excel отчета по работе онлайн-магазина");
                Logging.Comment($"Период с: {dtpStart.Value.ToShortDateString()} по: {dtpEnd.Value.ToShortDateString()}");
                Logging.Comment($"Отчет о выполненых заказах: {(chbComplete.Checked ? "Да" : "Нет")}");
                Logging.Comment($"Отчет об отмененных заказах: {(chbCancel.Checked ? "Да" : "Нет")}");
                Logging.Comment("Завершение выгрузки отчета по работе онлайн магазина");
                Logging.StopFirstLevel();
                #endregion
            });
        }