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