/// <summary>
/// To look for differences between 2 DataTables. Determine which rows are present in one DataTable
/// and not in the second
/// </summary>
/// <param name="r1">the first DataTable casted as AsEnumerable()</param>
/// <param name="r2">the second DataTable casted as AsEnumerable()</param>
/// <param name="dtRowsAdded">This DataTable contains the result</param>
/// <returns>Bool. If true differences found.</returns>
private static bool CompareRowsAdded(EnumerableRowCollection <DataRow> r1, EnumerableRowCollection <DataRow> r2, out DataTable dtRowsAdded)
{
bool hasRowsAdded = false;
var added = r1.Select(r => r.Field <string>("pk"))
.Except(r2.Select(r => r.Field <string>("pk")));
var va = (from row in r1.AsEnumerable()
join pk in added
on row.Field <string>("pk") equals pk
select row);
if (va.Count() > 0)
{
dtRowsAdded = va.CopyToDataTable();
hasRowsAdded = true;
}
else
{
dtRowsAdded = new DataTable();
}
return(hasRowsAdded);
}
public frmDesabasto(EnumerableRowCollection <DataRow> _tbl)
{
InitializeComponent();
Articulos = (from item in _tbl.AsEnumerable() select item.Field <string>("articulo")).ToList();
}
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 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 void getReport()
{
DoOnUIThread(delegate()
{
this.Enabled = false;
DataTable dtReport = proc.GetTenantReport((int)cbObjects.SelectedValue, (int)cbLandlord.SelectedValue, (int)cbActivties.SelectedValue);
if (dtReport.Rows.Count == 0)
{
MessageBox.Show("Нет данных для выгрузки", "Нет данных", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Enabled = true;
return;
}
else
{
Nwuram.Framework.ToExcelNew.ExcelUnLoad report = new Nwuram.Framework.ToExcelNew.ExcelUnLoad();
int indexRow = 1;
report.SetPageOrientationToLandscape();
///Заголовок
report.Merge(indexRow, 1, indexRow, 11);
report.AddSingleValue("ОТЧЕТ О ВИДАХ ДЕЯТЕЛЬНОСТИ АРЕНДАТОРОВ", indexRow, 1);
report.SetCellAlignmentToJustify(indexRow, 1, indexRow + 1, 11);
report.SetCellAlignmentToCenter(indexRow, 1, indexRow + 1, 11);
report.SetFontBold(indexRow, 1, indexRow, 11);
report.SetFontSize(indexRow, 1, indexRow, 11, 16);
indexRow++;
indexRow++;
///Параметры
report.AddSingleValue("Объект:", indexRow, 2);
report.SetFontBold(indexRow, 2, indexRow, 2);
report.AddSingleValue(cbObjects.Text.Trim(), indexRow, 3);
indexRow++;
report.AddSingleValue("Арендодатель:", indexRow, 2);
report.SetFontBold(indexRow, 2, indexRow, 2);
report.AddSingleValue(cbLandlord.Text.Trim(), indexRow, 3);
indexRow++;
report.AddSingleValue("Вид деятельности:", indexRow, 2);
report.SetFontBold(indexRow, 2, indexRow, 2);
report.AddSingleValue(cbActivties.Text.Trim(), indexRow, 3);
indexRow++;
indexRow++;
report.AddSingleValue("Выгрузил:", indexRow, 2);
report.SetFontBold(indexRow, 2, indexRow, 2);
report.AddSingleValue(Nwuram.Framework.Settings.User.UserSettings.User.FullUsername.Trim(), indexRow, 3);
indexRow++;
report.AddSingleValue("Дата:", indexRow, 2);
report.SetFontBold(indexRow, 2, indexRow, 2);
report.AddSingleValue(proc.GetDate().ToString(), indexRow, 3);
indexRow++;
indexRow++;
///Названия столбцов
report.Merge(indexRow, 1, indexRow + 1, 1);
report.AddSingleValue("Объект", indexRow, 1);
report.Merge(indexRow, 2, indexRow + 1, 2);
report.AddSingleValue("Арендодатель", indexRow, 2);
report.Merge(indexRow, 3, indexRow + 1, 3);
report.AddSingleValue("Вид деятельности", indexRow, 3);
report.Merge(indexRow, 4, indexRow + 1, 4);
report.AddSingleValue("№ п/п", indexRow, 4);
report.SetWrapText(indexRow, 4, indexRow + 1, 4);
report.Merge(indexRow, 5, indexRow + 1, 5);
report.AddSingleValue("Арендатор", indexRow, 5);
report.Merge(indexRow, 6, indexRow + 1, 6);
report.AddSingleValue("Вид договора", indexRow, 6);
report.Merge(indexRow, 7, indexRow, 9);
report.AddSingleValue("Местоположение", indexRow, 7);
report.AddSingleValue("Здание", indexRow + 1, 7);
report.AddSingleValue("Этаж", indexRow + 1, 8);
report.AddSingleValue("№ секции", indexRow + 1, 9);
report.Merge(indexRow, 10, indexRow + 1, 10);
report.AddSingleValue("Телефон", indexRow, 10);
report.Merge(indexRow, 11, indexRow + 1, 11);
report.AddSingleValue("Эл.почта", indexRow, 11);
report.SetCellAlignmentToJustify(indexRow, 1, indexRow + 1, 11);
report.SetCellAlignmentToCenter(indexRow, 1, indexRow + 1, 11);
report.SetBorders(indexRow, 1, indexRow + 1, 11);
report.SetFontBold(indexRow, 1, indexRow + 1, 11);
report.SetFontSize(indexRow, 1, indexRow + 1, 11, 14);
indexRow++;
indexRow++;
int ObjIndexStart = indexRow, ActIndexStart = indexRow, LandlordIndexStart = indexRow;
int NumPP = 1;///Номер строки
var dtObject = dtReport.AsEnumerable().Select(r => r.Field <int>("IdObject")).Distinct();;
foreach (var obj in dtObject)
{
ObjIndexStart = indexRow;
EnumerableRowCollection <DataRow> rcObj = dtReport.AsEnumerable().Where(r => r.Field <int>("IdObject") == obj);
report.AddSingleValue(rcObj.First()["Object"].ToString(), indexRow, 1);
var dtLandlord = rcObj.AsEnumerable().Select(r => r.Field <int>("IdLandlord")).Distinct();
foreach (var Landlord in dtLandlord)
{
LandlordIndexStart = indexRow;
EnumerableRowCollection <DataRow> rcLandlord = dtReport.AsEnumerable().Where(r => r.Field <int>("IdObject") == obj && r.Field <int>("IdLandlord") == Landlord);
report.AddSingleValue(rcLandlord.First()["Landlord"].ToString(), indexRow, 2);
var dtActivities = rcLandlord.AsEnumerable().Select(r => r.Field <int>("IdActivities")).Distinct();
foreach (var act in dtActivities)
{
ActIndexStart = indexRow;
EnumerableRowCollection <DataRow> rcAct = dtReport.AsEnumerable().Where(r => r.Field <int>("IdObject") == obj && r.Field <int>("IdLandlord") == Landlord && r.Field <int>("IdActivities") == act);
report.AddSingleValue(rcAct.First()["Activities"].ToString(), indexRow, 3);
foreach (var dr in rcAct)
{
report.AddSingleValue(NumPP.ToString(), indexRow, 4);
report.AddSingleValue(dr["Tenant"].ToString(), indexRow, 5);
report.AddSingleValue(dr["TypeContract"].ToString(), indexRow, 6);
report.AddSingleValue(dr["Building"].ToString(), indexRow, 7);
report.AddSingleValue(dr["Floor"].ToString(), indexRow, 8);
report.AddSingleValue(dr["Section"].ToString(), indexRow, 9);
report.AddSingleValue(dr["Work_phone"].ToString(), indexRow, 10);
report.AddSingleValue(dr["email"].ToString(), indexRow, 11);
report.SetCellAlignmentToLeft(indexRow, 4, indexRow, 11);
report.SetCellAlignmentToJustify(indexRow, 4, indexRow, 11);
report.SetBorders(indexRow, 4, indexRow, 11);
NumPP++;
indexRow++;
}
report.Merge(ActIndexStart, 3, indexRow - 1, 3);
report.SetCellAlignmentToLeft(ActIndexStart, 3, indexRow - 1, 3);
report.SetCellAlignmentToJustify(ActIndexStart, 3, indexRow - 1, 3);
report.SetBorders(ActIndexStart, 3, indexRow - 1, 3);
}
report.Merge(LandlordIndexStart, 2, indexRow - 1, 2);
report.SetCellAlignmentToLeft(LandlordIndexStart, 2, indexRow - 1, 2);
report.SetCellAlignmentToJustify(LandlordIndexStart, 2, indexRow - 1, 2);
report.SetBorders(LandlordIndexStart, 2, indexRow - 1, 2);
}
report.Merge(ObjIndexStart, 1, indexRow - 1, 1);
report.SetCellAlignmentToLeft(ObjIndexStart, 1, indexRow - 1, 1);
report.SetCellAlignmentToJustify(ObjIndexStart, 1, indexRow - 1, 1);
report.SetBorders(ObjIndexStart, 1, indexRow - 1, 1);
}
report.SetColumnAutoSize(1, 1, indexRow - 1, 11);
report.SetColumnWidth(10, 4, 11, 4, 5); ///Для № п/п
report.SetWrapText(12, 1, indexRow - 1, 11); ///Перенос текста для всех данных кроме телефон и почта
report.SetPageSetup(1, 1, true);
report.Show();
}
this.Enabled = true;
});
}
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
});
}
public object Convert(object[] values, Type targetType, object parameter, CultureInfo culture)
{
string result = null;
var workerId = System.Convert.ToInt32(values[0]);
var factoryId = System.Convert.ToInt64(values[1]);
StaffClass sc = null;
App.BaseClass.GetStaffClass(ref sc);
var workerProfessionsByWorkerId =
(sc.WorkerProfessionsDataTable.AsEnumerable().Where(
r => r.Field <Int64>("WorkerID") == workerId));
if (workerProfessionsByWorkerId.Count() > 1)
{
workerProfessionsByWorkerId =
(sc.WorkerProfessionsDataTable.AsEnumerable().Where(
r => r.Field <Int64>("WorkerID") == workerId &&
r.Field <Int64>("FactoryID") == factoryId));
}
if (!workerProfessionsByWorkerId.Any())
{
return(null);
}
if (parameter.ToString() == "ProfessionName")
{
var maxRate =
workerProfessionsByWorkerId.Max(m => m.Field <Decimal>("Rate"));
workerProfessionsByWorkerId =
workerProfessionsByWorkerId.Where(r => r.Field <Decimal>("Rate") == maxRate);
EnumerableRowCollection <DataRow> id = workerProfessionsByWorkerId;
var professionNamesByWorkerId =
(sc.ProfessionsDataTable.AsEnumerable().Where(
pidt => id.AsEnumerable().Any(
x => x.Field <Int64>("ProfessionID") == pidt.Field <Int64>("ProfessionID"))));
if (professionNamesByWorkerId.Count() != 0)
{
var profName = professionNamesByWorkerId.CopyToDataTable();
string professionName = profName.Select()[0]["ProfessionName"].ToString();
result = professionName;
}
}
if (parameter.ToString() == "Rate")
{
var workerProfessions = workerProfessionsByWorkerId.CopyToDataTable();
decimal rate =
workerProfessions.DefaultView.Cast <DataRowView>()
.Where(
dataRow =>
dataRow["Rate"] != DBNull.Value)
.Aggregate <DataRowView, decimal>(0,
(current, dataRow) => current + System.Convert.ToDecimal(dataRow["Rate"]));
result = rate.ToString(CultureInfo.InvariantCulture);
}
if (parameter.ToString() == "Category")
{
var maxRate =
workerProfessionsByWorkerId.Max(m => m.Field <Decimal>("Rate"));
workerProfessionsByWorkerId =
workerProfessionsByWorkerId.Where(r => r.Field <Decimal>("Rate") == maxRate);
if (!workerProfessionsByWorkerId.Any())
{
return(result);
}
var profName = workerProfessionsByWorkerId.CopyToDataTable();
string category = profName.Select()[0]["Category"].ToString();
result = category;
}
return(result);
}