private void printToExcel_Click(object sender, EventArgs e)
{
Logging.StartFirstLevel(79);
Logging.Comment("Выгрузка отчета по оборудованию");
Logging.Comment($"Местоположение:{cbLocation.Text}");
Logging.Comment($"Ответственный:{cbResponsibles.Text}");
Logging.Comment($"Статус:{cbStatus.Text}");
Logging.Comment($"Комплектующие или Оборудование:{(rbHrdWare.Checked? rbHrdWare.Text:rbComponents.Text)}");
Logging.Comment($"Показать данные за N дней:{(chbDays.Checked ? nudDays.Value.ToString() : "Показать данные за все время")}");
Logging.Comment($"Поиск по Инв.№:{tbNumber.Text}");
Logging.Comment($"поиск по EAN:{tbEAN.Text}");
Logging.Comment($"поиск по наименованию:{tbName.Text}");
Logging.Comment($"поиск по Номеру СЗ:{tbNumberSZ.Text}");
Logging.StopFirstLevel();
var report = new ExcelUnLoad();
report.AddSingleValue("Инв. №", 1, 1);
report.AddSingleValue("EAN", 1, 2);
report.AddSingleValue("Наименование", 1, 3);
report.AddSingleValue("Объект", 1, 4);
report.AddSingleValue("Оборудование/Комплектующие", 1, 5);
report.AddSingleValue("Местоположение", 1, 6);
report.AddSingleValue("Ответственный", 1, 7);
report.AddSingleValue("Статус", 1, 8);
report.SetBorders(1, 1, 1, 8);
report.SetFontBold(1, 1, 1, 8);
var rowCount = 2;
foreach (DataGridViewRow row in dgvData.Rows)
{
var rowCountStart = rowCount;
report.AddSingleValue(row.Cells["cNum"].Value.ToString(), rowCount, 1);
report.AddSingleValue(row.Cells["cEAN"].Value.ToString(), rowCount, 2);
report.AddSingleValue(row.Cells["cName"].Value.ToString(), rowCount, 3);
report.AddSingleValue(row.Cells["LocationComment"].Value.ToString(), rowCount, 4);
report.AddSingleValue(row.Cells["cHardWare"].Value.ToString(), rowCount, 5);
report.AddSingleValue(row.Cells["cLocation"].Value.ToString(), rowCount, 6);
report.AddSingleValue(row.Cells["cResponsible"].Value.ToString(), rowCount, 7);
report.AddSingleValue(row.Cells["cStatus"].Value.ToString(), rowCount, 8);
rowCount++;
for (var i = 1; i < 8; i++)
{
report.Merge(rowCountStart, i, rowCount - 1, i);
report.SetBorders(rowCountStart, 1, rowCount - 1, 8);
}
}
report.SetColumnAutoSize(1, 1, rowCount, 8);
report.SetColumnWidth(1, 1, rowCount, 1, 8);
report.SetWrapText(1, 1, rowCount, 8);
report.Show();
}
public estimateReport(DataRow current)
{
ExcelUnLoad report = new ExcelUnLoad();
report = addHeader(report, current);
report = addHeaderColumn(report);
report = addTable(report, Convert.ToInt32(current["id"].ToString()));
report = addFooter(report, current);
report.Show();
}
public balanceReport(int id, DateTime date)
{
ExcelUnLoad report = new ExcelUnLoad();
report = addHeader(report);
report = addHeaderColumn(report);
report = addTable(report, id, date);
report = addFooter(report);
report.Show();
}
private void btnExcel_Click(object sender, EventArgs e)
{
DateTime datePlan = new DateTime(dtpDate.Value.Year, dtpDate.Value.Month, 1);
Task <DataTable> task = Config.hCntMain.getReport(datePlan);
task.Wait();
dtMonthReport = task.Result;
if (dtMonthReport == null || dtMonthReport.Rows.Count == 0)
{
MessageBox.Show("Нет данных для отчета", "Выгрузка отчета", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// план с учетом предыдущего долга
if (!dtMonthReport.Columns.Contains("planWithDebt"))
{
dtMonthReport.Columns.Add("planWithDebt", typeof(decimal));
}
foreach (DataRow dr in dtMonthReport.Rows)
{
dr["planWithDebt"] = (decimal)dr["sumPlan"] + (decimal)dr["debt"];
}
rep = new ExcelUnLoad();
int crow = 1;
rep.SetColumnWidth(2, 2, 2, 2, 12);
rep.SetColumnWidth(3, 3, 3, 3, 12);
rep.SetColumnWidth(4, 4, 4, 4, 12);
rep.SetColumnWidth(5, 5, 5, 5, 12);
rep.SetColumnWidth(6, 6, 6, 6, 12);
rep.SetColumnWidth(7, 7, 7, 7, 12);
rep.SetColumnWidth(8, 8, 8, 8, 12);
ObjectReport(ref crow);
task = Config.hCntMain.getAllPayments(datePlan);
task.Wait();
PaymentsReport(ref crow, task.Result);
rep.SetPageSetup(1, 9999, false);
rep.Show();
}
public void showReport()
{
ExcelUnLoad rep = new ExcelUnLoad();
int crow = 1;
rep.AddSingleValue("Стоимости договоров по месяцам", crow, 1);
crow += 2;
foreach (var a in listPay)
{
DataTable dtData = a.Value;
int col = 2;
rep.AddSingleValue($"Договор: {a.Key}", crow, 1);
foreach (DataRow dr in dtData.Rows)
{
rep.AddSingleValue(dr["date"].ToString(), crow, col);
rep.AddSingleValue(dr["price"].ToString(), crow + 1, col);
col++;
}
crow += 3;
}
rep.GoToNextSheet("С аннуляциями");
crow = 1;
rep.AddSingleValue("Договора с аннуляциями", crow, 1);
crow += 2;
foreach (var a in listLeave)
{
rep.AddSingleValue($"Договор id: {a.Key}", crow, 1);
int col = 2;
foreach (DataRow dr in a.Value.Rows)
{
rep.AddSingleValue(dr["date"].ToString(), crow, col);
rep.AddSingleValue(dr["price"].ToString(), crow + 1, col);
col++;
}
crow += 3;
}
rep.Show();
}
private void btnReport_Click(object sender, EventArgs e)
{
if (dtData == null || dtData.Rows.Count == 0)
{
return;
}
Logging.StartFirstLevel(79);
Logging.Comment($"Объект ID:{cmbObject.SelectedValue}; Наименование:{cmbObject.Text}");
Logging.Comment($"Тип договора ID:{cmbTypeContract.SelectedValue}; Наименование:{cmbTypeContract.Text}");
Logging.Comment($"Договор:{tbAgreements.Text}");
Logging.Comment($"Арендатор:{tbLandLord.Text}");
Logging.Comment($"Подтвержденные скидки: {(chbIsAccept.Checked ? "Да" : "Нет")}");
Logging.Comment($"Отклоненные скидки: {(chbNotActive.Checked ? "Да" : "Нет")}");
Logging.Comment($"Дата начала:{dtpStart.Value.ToShortDateString()}");
Logging.Comment($"Дата окончания:{dtpEnd.Value.ToShortDateString()}");
Logging.Comment($"Отклоненные скидки: {(chbUnlimitedDiscount.Checked ? "Да" : "Нет")}");
Logging.StopFirstLevel();
ExcelUnLoad rep = new ExcelUnLoad("Скидки");
int row = 1;
rep.AddSingleValue($"Отчет по скидкам с {dtpStart.Value.ToShortDateString()}" + (!chbIsAccept.Checked ? $" по {dtpEnd.Value.ToShortDateString()}" : ""), row, 1);
rep.SetFontBold(row, 1, row, 1);
row++;
rep.AddSingleValue($"Дата выгрузки: {DateTime.Now.ToShortDateString()} {DateTime.Now.ToShortTimeString()}", row, 1);
row++;
rep.AddSingleValue($"Выгрузил: {UserSettings.User.FullUsername}", row, 1);
row += 2;
int startTable = row;
#region шапка
rep.AddSingleValue("Объект", row, 1); //nameObjectLease
rep.AddSingleValue("Арендатор", row, 2); //nameLandLord
rep.AddSingleValue("№ договора", row, 3); //Agreement
rep.AddSingleValue("Тип договора", row, 4); //TypeContract
rep.AddSingleValue("Дата начала", row, 5); //DateStart
rep.AddSingleValue("Дата окончания", row, 6); //DateEnd
rep.AddSingleValue("Тип скидки", row, 7); //nameTypeDiscount
rep.AddSingleValue("Скидка", row, 8); //Discount
rep.SetFontBold(row, 1, row, 8);
rep.SetWrapText(row, 1, row, 8);
rep.SetCellAlignmentToCenter(row, 1, row, 8);
rep.SetCellAlignmentToJustify(row, 1, row, 8);
#endregion
#region колонки
rep.SetColumnWidth(1, 1, 1, 1, 8);
rep.SetColumnWidth(2, 2, 2, 2, 23);
rep.SetColumnWidth(3, 3, 3, 3, 15);
rep.SetColumnWidth(4, 4, 4, 4, 20);
rep.SetColumnWidth(5, 5, 5, 5, 12);
rep.SetColumnWidth(6, 6, 6, 6, 12);
rep.SetColumnWidth(7, 7, 7, 7, 25);
rep.SetColumnWidth(8, 8, 8, 8, 15);
rep.SetPageOrientationToLandscape();
#endregion
row++;
rep.AddSingleValue("Неподтвержденные скидки", row, 1);
rep.Merge(row, 1, row, 8);
rep.SetCellAlignmentToCenter(row, 1, row, 1);
rep.SetFontBold(row, 1, row, 1);
row++;
addTable(ref rep, ref row, 1);
rep.AddSingleValue("Подтвержденные скидки", row, 1);
rep.Merge(row, 1, row, 8);
rep.SetCellAlignmentToCenter(row, 1, row, 1);
rep.SetFontBold(row, 1, row, 1);
row++;
addTable(ref rep, ref row, 2);
rep.AddSingleValue("Отклоненные скидки", row, 1);
rep.Merge(row, 1, row, 8);
rep.SetCellAlignmentToCenter(row, 1, row, 1);
rep.SetFontBold(row, 1, row, 1);
row++;
addTable(ref rep, ref row, 3);
rep.SetBorders(startTable, 1, row - 1, 8);
rep.Show();
}
private void btnReport_Click(object sender, EventArgs e)
{
if (dtData.DefaultView.Count == 0)
{
return;
}
ExcelUnLoad rep = new ExcelUnLoad();
int crow = 1;
int maxCol = 4;
rep.SetColumnWidth(1, 1, 1, 1, 10);
rep.SetColumnWidth(2, 2, 2, 2, 15);
rep.SetColumnWidth(3, 3, 3, 3, 40);
rep.SetColumnWidth(4, 4, 4, 4, 20);
rep.AddSingleValue("Отчет о загрузке картинок", crow, 1);
rep.Merge(crow, 1, crow, maxCol);
rep.SetCellAlignmentToCenter(crow, 1, crow, maxCol);
rep.SetCellAlignmentToJustify(crow, 1, crow, maxCol);
rep.SetFontBold(crow, 1, crow, 1);
crow += 2;
rep.AddFilter("EAN", tbEan, ref crow);
rep.AddFilter("Наименование", tbName, ref crow);
rep.AddFilter("Артикул", tbIdTovar, ref crow);
rep.AddFilter("Выбран вариант", chbIsNotFount, ref crow);
rep.AddFilter("Выбран вариант", chbIsUpdated, ref crow);
rep.AddFilter("Выбран вариант", chbNoImage, ref crow);
crow++;
int startRow = crow;
int col = 1;
foreach (DataGridViewColumn dc in dgvData.Columns)
{
rep.AddSingleValue(dc.HeaderText, crow, col);
rep.SetFontBold(crow, col, crow, col);
rep.SetCellAlignmentToCenter(crow, col, crow, col);
rep.SetCellAlignmentToJustify(crow, col, crow, col);
rep.SetWrapText(crow, col, crow, col);
col++;
}
foreach (DataRowView row in dtData.DefaultView)
{
crow++;
col = 1;
foreach (DataGridViewColumn dc in dgvData.Columns)
{
rep.AddSingleValue(row[dc.DataPropertyName].ToString(), crow, col);
rep.SetWrapText(crow, col, crow, col);
Color color = Color.White;
if (row["result"].ToString() == "Обновлено")
{
color = panel1.BackColor;
}
else if (row["result"].ToString() == "Картинка не найдена")
{
color = panel2.BackColor;
}
else if (row["result"].ToString() == "Товар не найден")
{
color = panel3.BackColor;
}
rep.SetCellColor(crow, col, crow, col, color);
col++;
}
}
rep.SetBorders(startRow, 1, crow, maxCol);
rep.Show();
}
private void btnPrint_Click(object sender, EventArgs e)
{
Logging.StartFirstLevel(79);
Logging.Comment("Выгрузка отчета о занятости секций");
Logging.Comment($"Объект ID:{cmbObject.SelectedValue}; Наименование:{cmbObject.Text}");
Logging.Comment($"Здание ID:{cmbBuilding.SelectedValue}; Наименование:{cmbBuilding.Text}");
Logging.Comment($"Этаж ID:{cmbFloor.SelectedValue}; Наименование:{cmbFloor.Text}");
Logging.Comment($"Поиск по секции:{tbSection.Text}");
Logging.Comment($"{chbBusy.Text}:{(chbBusy.Checked?"Да":"Нет")}");
Logging.Comment($"{chbClearing.Text}:{(chbClearing.Checked ? "Да" : "Нет")}");
Logging.Comment($"{chbFree.Text}:{(chbFree.Checked ? "Да" : "Нет")}");
Logging.StopFirstLevel();
DataTable dtReport = dtData.DefaultView.ToTable();
ExcelUnLoad rep = new ExcelUnLoad();
rep.SetPageOrientationToLandscape();
int maxColumns = 8;
int cRow = 1;
#region колонки
rep.SetColumnWidth(1, 1, 1, 1, 10);
rep.SetColumnWidth(2, 2, 2, 2, 18);
rep.SetColumnWidth(3, 3, 3, 3, 12);
rep.SetColumnWidth(4, 4, 4, 4, 20);
rep.SetColumnWidth(5, 5, 5, 5, 12);
rep.SetColumnWidth(6, 6, 6, 6, 12);
rep.SetColumnWidth(7, 7, 7, 7, 12);
rep.SetColumnWidth(8, 8, 8, 8, 15);
#endregion
#region Шапка
rep.AddSingleValue("Отчет по секциям", 1, 1);
rep.Merge(cRow, 1, cRow, maxColumns);
rep.SetCellAlignmentToCenter(1, 1, 1, 1);
cRow++;
rep.AddSingleValue("Объект", cRow, 1);
rep.AddSingleValue(cmbObject.Text, cRow, 2);
rep.AddSingleValue("Дата выгрузки", cRow, 3);
rep.AddSingleValue(DateTime.Now.ToString(), cRow, 4);
cRow++;
rep.AddSingleValue("Здание", cRow, 1);
rep.AddSingleValue(cmbBuilding.Text, cRow, 2);
rep.AddSingleValue("Выгрузил", cRow, 3);
rep.AddSingleValue(UserSettings.User.FullUsername, cRow, 4);
cRow++;
rep.AddSingleValue("Этаж", cRow, 1);
rep.AddSingleValue(cmbFloor.Text, cRow, 2);
cRow += 2;
#endregion
#region Шапка таблицы
int startRow = cRow;
rep.AddSingleValue("Объект", cRow, 1);
rep.AddSingleValue("Здание", cRow, 2);
rep.AddSingleValue("Этаж", cRow, 3);
rep.AddSingleValue("Секция", cRow, 4);
rep.AddSingleValue("Начало аренды", cRow, 5);
rep.AddSingleValue("Конец аренды", cRow, 6);
rep.AddSingleValue("Номер договора", cRow, 7);
rep.AddSingleValue("Арендатор", cRow, 8);
rep.SetFontBold(cRow, 1, cRow, maxColumns);
rep.SetCellAlignmentToCenter(cRow, 1, cRow, maxColumns);
rep.SetWrapText(cRow, 1, cRow, maxColumns);
cRow++;
#endregion
#region таблица
foreach (DataRow dr in dtReport.Rows)
{
rep.AddSingleValue(dr["nameObject"].ToString(), cRow, 1);
rep.AddSingleValue(dr["nameBuilding"].ToString(), cRow, 2);
rep.AddSingleValue(dr["nameFloor"].ToString(), cRow, 3);
rep.AddSingleValue(dr["nameSection"].ToString(), cRow, 4);
rep.AddSingleValue(dr["StartDate"].ToString().Length > 0 ?
dr["StartDate"].ToString().Substring(0, 10) : dr["StartDate"].ToString(), cRow, 5);
rep.AddSingleValue(dr["EndDate"].ToString().Length > 0 ? dr["EndDate"].ToString().Substring(0, 10) : dr["EndDate"].ToString(), cRow, 6);
rep.AddSingleValue(dr["numDoc"].ToString(), cRow, 7);
rep.AddSingleValue(dr["nameArenda"].ToString(), cRow, 8);
if (dr["typeSection"].ToString() == "1")
{
rep.SetCellColor(cRow, 1, cRow, maxColumns, panel2.BackColor);
}
if (dr["typeSection"].ToString() == "2")
{
rep.SetCellColor(cRow, 1, cRow, maxColumns, panel1.BackColor);
}
cRow++;
}
cRow--;
rep.SetWrapText(startRow, 1, cRow, maxColumns);
rep.SetBorders(startRow, 1, cRow, maxColumns);
cRow += 2;
rep.SetCellColor(cRow, 1, cRow, 1, panel1.BackColor);
rep.AddSingleValue("-Занятые секции", cRow, 2);
cRow++;
rep.SetCellColor(cRow, 1, cRow, 1, panel2.BackColor);
rep.AddSingleValue("-Освобождающиеся секции", cRow, 2);
#endregion
rep.Show();
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
ExcelUnLoad rep = new ExcelUnLoad("Отчет по секциям");
rep.AddSingleValue("Справочник секций", 1, 1);
rep.Merge(1, 1, 1, 9);
rep.SetBordersToBottom(1, 1, 9, true);
rep.SetBordersToBottom(1, 2, 9, true);
rep.SetBordersToBottom(1, 3, 9, true);
rep.SetBordersToBottom(1, 4, 9, true);
rep.SetBordersToBottom(1, 5, 9, true);
rep.SetBordersToBottom(1, 6, 9, true);
rep.SetBordersToBottom(1, 7, 9, true);
rep.SetBordersToBottom(1, 8, 9, true);
rep.SetBordersToBottom(1, 9, 9, true);
// rep.SetBorders(1, 1, 1, 9);
rep.SetFontBold(1, 1, 1, 1);
rep.SetFontSize(1, 1, 1, 1, 16);
rep.SetCellAlignmentToCenter(1, 1, 1, 1);
int crow = 3;
rep.AddSingleValue($"Объект: {obj}", crow, 1);
crow++;
rep.AddSingleValue($"Здание: {build}", crow, 1);
crow++;
rep.AddSingleValue($"Этаж: {floor}", crow, 1);
crow += 2;
rep.AddSingleValue($"Выгрузил: {Nwuram.Framework.Settings.User.UserSettings.User.FullUsername}", crow, 1);
crow++;
rep.AddSingleValue($"Дата выгрузки: {DateTime.Now}", crow, 1);
crow += 2;
int startRow = crow;
#region Шапка
rep.AddSingleValue("Секция", crow, 1);
rep.AddSingleValue("Объект", crow, 2);
rep.AddSingleValue("Здание", crow, 3);
rep.AddSingleValue("Этаж", crow, 4);
rep.AddSingleValue("Кол-во телефонных линий", crow, 5);
rep.AddSingleValue("Кол-во светильников", crow, 6);
rep.AddSingleValue("Номер телефона", crow, 7);
rep.AddSingleValue("Оборудование и кол-во", crow, 8);
rep.AddSingleValue("Приборы/части системы", crow, 9);
#endregion
#region ширина столбцов
rep.SetColumnWidth(1, 1, 1, 1, 9);
rep.SetColumnWidth(1, 2, 1, 2, 9);
rep.SetColumnWidth(1, 3, 1, 3, 17);
rep.SetColumnWidth(1, 4, 1, 4, 9);
rep.SetColumnWidth(1, 5, 1, 5, 13);
rep.SetColumnWidth(1, 6, 1, 6, 14);
rep.SetColumnWidth(1, 7, 1, 7, 10);
rep.SetColumnWidth(1, 8, 1, 8, 20);
rep.SetColumnWidth(1, 9, 1, 9, 20);
#endregion
rep.SetFontBold(crow, 1, crow, 9);
rep.SetCellAlignmentToCenter(crow, 1, crow, 9);
rep.SetCellAlignmentToJustify(crow, 1, crow, 9);
rep.SetWrapText(crow, 1, crow, 9);
crow++;
if (tSec.Rows.Count > 0)
{
for (int i = 0; i < view.Count; i++)
{
string A, I, sEq = "";
string sDevices = "";
A = "A" + (i + 11);
I = "I" + (i + 11);
int isActive = 0;
if (checEq.Checked == true)
{
isActive = 1;
}
else
{
isActive = 0;
}
foreach (DataRow dr in _proc.GetEqVsSec(tSec.DefaultView[i]["id"].ToString(), isActive).Rows)
{
if (sEq.Length > 0)
{
sEq += "\n";
}
sEq += dr["cName"] + " " + dr["Quantity"] + " шт.";
}
DataTable dtDevices = _proc.GetSectionDevices(Convert.ToInt32(tSec.DefaultView[i]["id"]));
foreach (DataRow dr in dtDevices.Rows)
{
if (sDevices.Length > 0)
{
sDevices += "\n";
}
sDevices += dr["cname"].ToString() + " " + dr["quantity"].ToString() + " " + dr["unit"].ToString();
}
rep.AddSingleValue(tSec.DefaultView[i]["Sec"].ToString(), crow, 1);
rep.AddSingleValue(tSec.DefaultView[i]["Obj"].ToString(), crow, 2);
rep.AddSingleValue(tSec.DefaultView[i]["Build"].ToString(), crow, 3);
rep.AddSingleValue(tSec.DefaultView[i]["Floo"].ToString(), crow, 4);
rep.AddSingleValue(tSec.DefaultView[i]["Telephone_lines"].ToString(), crow, 5);
rep.AddSingleValue(tSec.DefaultView[i]["Lamps"].ToString(), crow, 6);
rep.AddSingleValue(tSec.DefaultView[i]["Phone_number"].ToString(), crow, 7);
rep.AddSingleValue(sEq, crow, 8);
rep.AddSingleValue(sDevices, crow, 9);
rep.SetWrapText(crow, 1, crow, 9);
rep.SetCellAlignmentToTop(crow, 1, crow, 9);
crow++;
}
}
rep.SetBorders(startRow, 1, crow - 1, 9);
rep.SetPageOrientationToLandscape();
rep.Show();
string logEvent = "Выгрузка справочника секций в Excel";
string BuildName = "";
cbZdan.Invoke((MethodInvoker) delegate
{
BuildName = cbZdan.Text;
});
string FloorName = "";
cbZloor.Invoke((MethodInvoker) delegate
{
FloorName = cbZloor.Text;
});
Logging.StartFirstLevel(763);
Logging.Comment(logEvent);
Logging.Comment("Имя выгруженного excel файла \"" + file.Name + "\"");
Logging.Comment("Выгрузка произведена для здания: \"" + BuildName + "\" и этажа: \"" + FloorName + "\". Выгружено " + view.Count.ToString() + " строк.");
Logging.Comment("Завершение операции \"" + logEvent + "\"");
Logging.StopFirstLevel();
}
public void createReportTenant()
{
if (dtReport == null)
{
return;
}
monthPlan = listMonth[DateTime.Parse(dtReport.Rows[0]["monthX"].ToString()).Month];
DateTime dayX = DateTime.Parse(dtReport.Rows[0]["dayX"].ToString());
datePlan = $"{dayX.Day}.{dayX.Month}";
rep = new ExcelUnLoad();
int crow = 1;
int startRow = 1;
AddHeaderTenant(ref crow);
var tenants = dtReport.AsEnumerable().GroupBy(g => g.Field <string>("new_tenant_Name")).Select(s => s.Key);
int indexTenant = 0;
foreach (string tenant in tenants)
{
rep.AddSingleValue(tenant, crow, 1);
int startTenant = crow;
// таблица по арендатору
EnumerableRowCollection <DataRow> rTenant = dtReport.AsEnumerable().Where(r => r.Field <string>("new_tenant_Name") == tenant);
// объекты
var objects = rTenant.GroupBy(g => g.Field <string>("objectName")).Select(s => s.Key);
foreach (string obj in objects)
{
rep.AddSingleValue(obj, crow, 2);
int startObject = crow;
// таблица по объекту
EnumerableRowCollection <DataRow> rObject = rTenant.Where(r => r.Field <string>("objectName") == obj);
// секции
var sections = rObject.GroupBy(g => g.Field <string>("nameSection")).Select(s => s.Key);
foreach (var section in sections)
{
rep.AddSingleValue(section, crow, 3);
int startSection = crow;
//договоры
EnumerableRowCollection <DataRow> rAgrements = rObject.Where(r => r.Field <string>("nameSection") == section);
foreach (DataRow dr in rAgrements)
{
rep.AddSingleValue(dr["Agreement"].ToString(), crow, 4);
rep.AddSingleValue(DateTime.Parse(dr["Start_Date"].ToString()).ToShortDateString(), crow, 5);
rep.AddSingleValue(DateTime.Parse(dr["End_Date"].ToString()).ToShortDateString(), crow, 6);
rep.AddSingleValueObject(dr["Total_Sum"], crow, 7);
rep.AddSingleValueObject(dr["SummaPlanView"], crow, 8);
decimal before25day = (dr["payments"] == DBNull.Value ? 0 : (decimal)dr["payments"]);
if (before25day < 0)
{
rep.AddSingleValueObject((-1 * before25day), crow, 9);
}
if (before25day > 0)
{
rep.AddSingleValueObject(before25day, crow, 10);
}
decimal pay = (dr["paymentsNew"] == DBNull.Value ? 0 : (decimal)dr["paymentsNew"]);
if (pay != 0)
{
rep.AddSingleValueObject(pay, crow, 13);
}
/* decimal curDebt = before25day + pay;
* if (curDebt < 0)
* rep.AddSingleValue((-curDebt).ToString("0.00"), crow, 13);
* if (curDebt > 0)
* rep.AddSingleValue(curDebt.ToString("0.00"), crow, 14);
*
*
* decimal currDown = (decimal)dr["percDebt"];
* if (currDown != 0 && curDebt < 0)
* {
* rep.AddSingleValue($"{((curDebt / -currDown) * 100).ToString("0.00")}%", crow, 15);
* }
*/
rep.AddSingleValue(dr["landLord_name"].ToString(), crow, 21);
crow++;
}
rep.Merge(startSection, 3, crow - 1, 3);
rep.SetCellAlignmentToJustify(startSection, 3, crow - 1, 3);
}
//итого долг на 25 число
EnumerableRowCollection <DataRow> rCol = rObject
.Where(r => r.Field <object>("payments") != null);
// тут будет у нас или в одну или в другую считаться
decimal rowSumm = rCol.Sum(s => s.Field <decimal>("payments"));
// decimal rowSumm = rCol
// .Where(r => r.Field<decimal>("payments") < 0)
// .Sum(s => s.Field<decimal>("payments"));
if (rowSumm < 0)
{
rep.AddSingleValueObject((-rowSumm), startObject, 11);
}
//переплата на 25 число
// rowSumm = rCol
// .Where(r => r.Field<decimal>("payments") > 0)
// .Sum(s => s.Field<decimal>("payments"));
if (rowSumm > 0)
{
rep.AddSingleValueObject(rowSumm, startObject, 12);
}
//сумма оплаты
rCol = rObject
.Where(r => r.Field <object>("paymentsNew") != null);
rowSumm = rCol
.Sum(s => s.Field <decimal>("paymentsNew"));
if (rowSumm != 0)
{
rep.AddSingleValueObject(rowSumm, startObject, 14);
}
//итого долг - считается сумма текущего по секции - если в -, то долг, если в +, то переплата... для - считается переплата
//это как в предыдущем, только по другому идет сортировочка
/*rowSumm = rTenant.Sum(s => s.Field<decimal>("payments") + s.Field<decimal>("paymentsNew"));
* if (rowSumm < 0)
* rep.AddSingleValue((-rowSumm).ToString("0.00"), startObject, 16);
* if (rowSumm > 0)
* rep.AddSingleValue(rowSumm.ToString("0.00"), startObject, 18);
* // итого % долга - при минусовом rowSumm, делим вот на то число
* decimal total_sum = rCol.Where(r => r.Field<decimal>("percDebt") > 0).Sum(s => s.Field<decimal>("percDebt"));
* if (rowSumm < 0 && total_sum != 0)
* rep.AddSingleValue((-rowSumm * 100 / total_sum).ToString("0.00") + "%", startObject, 17);*/
// итого переплата
decimal currentSum = rObject.Sum(s => (s.Field <decimal>("payments") + s.Field <decimal>("paymentsNew")));
if (currentSum < 0)
{
rep.AddSingleValueObject((-currentSum), startObject, 15);
}
if (currentSum > 0)
{
rep.AddSingleValueObject(currentSum, startObject, 16);
}
decimal currDown = rObject.Sum(s => s.Field <decimal>("percDebt"));
if (currDown != 0 && currentSum < 0)
{
rep.AddSingleValue($"{((currentSum / -currDown) * 100).ToString("0.00")}%", startObject, 17);
}
rep.Merge(startObject, 2, crow - 1, 2);
rep.Merge(startObject, 11, crow - 1, 11);
rep.Merge(startObject, 12, crow - 1, 12);
rep.Merge(startObject, 14, crow - 1, 14);
rep.Merge(startObject, 15, crow - 1, 15);
rep.Merge(startObject, 16, crow - 1, 16);
rep.Merge(startObject, 17, crow - 1, 17);
rep.SetCellAlignmentToJustify(startObject, 2, crow - 1, 2);
rep.SetCellAlignmentToJustify(startObject, 11, crow - 1, 11);
rep.SetCellAlignmentToJustify(startObject, 12, crow - 1, 12);
rep.SetCellAlignmentToJustify(startObject, 14, crow - 1, 14);
rep.SetCellAlignmentToJustify(startObject, 15, crow - 1, 15);
rep.SetCellAlignmentToJustify(startObject, 16, crow - 1, 16);
rep.SetCellAlignmentToJustify(startObject, 17, crow - 1, 17);
}
decimal summ = rTenant.Sum(s => s.Field <decimal>("payments") + s.Field <decimal>("paymentsNew"));
if (summ < 0)
{
rep.AddSingleValueObject((-summ), startTenant, 18);
}
if (summ > 0)
{
rep.AddSingleValueObject(summ, startTenant, 20);
}
// итого % долга - при минусовом rowSumm, делим вот на то число
decimal sum_total = rTenant.Where(r => r.Field <decimal>("percDebt") > 0).Sum(s => s.Field <decimal>("percDebt"));
if (summ < 0 && sum_total != 0)
{
rep.AddSingleValue((-summ * 100 / sum_total).ToString("0.00") + "%", startTenant, 19);
}
rep.Merge(startTenant, 18, crow - 1, 18);
rep.Merge(startTenant, 19, crow - 1, 19);
rep.Merge(startTenant, 20, crow - 1, 20);
rep.SetCellAlignmentToJustify(startTenant, 18, crow - 1, 18);
rep.SetCellAlignmentToJustify(startTenant, 19, crow - 1, 19);
rep.SetCellAlignmentToJustify(startTenant, 20, crow - 1, 20);
rep.Merge(startTenant, 1, crow - 1, 1);
rep.SetCellAlignmentToJustify(startTenant, 1, crow - 1, 1);
//подстветка каждого второго арендатора
//цвет до со 1 по 21 ячейки
if (indexTenant % 2 == 1)
{
rep.SetCellColor(startTenant, 1, crow - 1, 21, Color.LightGray);
}
indexTenant++;
}
rep.SetPageOrientationToLandscape();
rep.SetPageSetup(1, 9999, true);
rep.SetBorders(1, 1, crow - 1, 21);
rep.SetPrintRepeatHead(1, 2);
rep.SetBottomMargin(0);
rep.SetFooterMargin(0);
rep.SetHeaderMargin(0);
rep.SetLeftMargin(0);
rep.SetRightMargin(0);
rep.SetTopMargin(0);
rep.Show();
}
public void createReportSection()
{
/*Task<DataTable> task = Config.hCntMain.getDataReport();
* task.Wait();
* dtReport = task.Result;*/
if (dtReport == null)
{
return;
}
monthPlan = listMonth[DateTime.Parse(dtReport.Rows[0]["monthX"].ToString()).Month];
DateTime dayX = DateTime.Parse(dtReport.Rows[0]["dayX"].ToString());
datePlan = $"{dayX.Day}.{dayX.Month}";
rep = new ExcelUnLoad();
int crow = 1;
int startRow = 1;
AddHeaderSection(ref crow);
var objects = dtReport.AsEnumerable().GroupBy(g => g.Field <string>("objectName")).Select(s => s.Key);
//индекс секции для подкраски строчки
int indexSection = 0;
foreach (string obj in objects)
{
rep.AddSingleValue(obj, crow, 1);
int startObject = crow;
//дататейбл по объекту
EnumerableRowCollection <DataRow> rObject = dtReport.AsEnumerable().Where(r => r.Field <string>("objectName") == obj);
//секциии
var sections = rObject.GroupBy(g => g.Field <string>("nameSection")).Select(s => s.Key);
foreach (string sect in sections)
{
rep.AddSingleValue(sect, crow, 2);
int startSection = crow;
//дататейбл по обхекту и секции
EnumerableRowCollection <DataRow> rSection = rObject.Where(r => r.Field <string>("nameSection") == sect);
//арендаторы
var tenants = rSection.GroupBy(g => new { id_tenant = g.Field <int>("tenant_id"), name = g.Field <string>("tenant_Name") }).Select(r => new{ r.Key.name, r.Key.id_tenant });
foreach (var tenant in tenants)
{
rep.AddSingleValue(tenant.name, crow, 3);
int startTenant = crow;
//договоры
EnumerableRowCollection <DataRow> rAgrements = rSection.Where(r => r.Field <int>("tenant_id") == tenant.id_tenant);
foreach (DataRow dr in rAgrements)
{
rep.AddSingleValueObject(dr["Agreement"], crow, 4);
rep.AddSingleValueObject(dr["Total_Sum"], crow, 5);
rep.AddSingleValueObject(dr["SummaPlanView"], crow, 6);
decimal before25day = (dr["payments"] == DBNull.Value ? 0 : (decimal)dr["payments"]);
if (before25day < 0)
{
rep.AddSingleValueObject((-1 * before25day), crow, 7);
}
if (before25day > 0)
{
rep.AddSingleValueObject(before25day, crow, 8);
}
decimal pay = (dr["paymentsNew"] == DBNull.Value ? 0 : (decimal)dr["paymentsNew"]);
if (pay > 0)
{
rep.AddSingleValueObject(pay, crow, 11);
}
decimal curDebt = before25day + pay;
if (curDebt < 0)
{
rep.AddSingleValueObject((-curDebt), crow, 13);
}
if (curDebt > 0)
{
rep.AddSingleValueObject(curDebt, crow, 15);
}
decimal currDown = (decimal)dr["percDebt"];
if (currDown != 0 && curDebt < 0)
{
rep.AddSingleValue($"{((curDebt / -currDown) * 100).ToString("0.00")}%", crow, 14);
}
rep.AddSingleValue(dr["landLord_name"].ToString(), crow, 18);
crow++;
}
rep.Merge(startTenant, 3, crow - 1, 3);
rep.SetCellAlignmentToJustify(startTenant, 3, crow - 1, 3);
}
//итого долг на 25 число
EnumerableRowCollection <DataRow> rCol = rSection
.Where(r => r.Field <object>("payments") != null);
// переписано, теперь тупо все суммируется и если >0 то переплата, если <0 то долг
decimal rowSumm = rCol.Sum(s => s.Field <decimal>("payments"));
if (rowSumm > 0)
{
rep.AddSingleValueObject(rowSumm, startSection, 10);
}
if (rowSumm < 0)
{
rep.AddSingleValueObject(-rowSumm, startSection, 9);
}
/*decimal rowSumm = rCol
* .Where(r => r.Field<decimal>("payments") < 0)
* .Sum(s => s.Field<decimal>("payments"));
* if (rowSumm!=0)
* rep.AddSingleValueObject(-rowSumm, startSection, 9);
* //переплата на 25 число
* rowSumm = rCol
* .Where(r=> r.Field<decimal>("payments") > 0)
* .Sum(s => s.Field<decimal>("payments"));
* if (rowSumm!=0)
* rep.AddSingleValueObject(rowSumm, startSection, 10);
*/
//сумма оплаты
rCol = rSection
.Where(r => r.Field <object>("paymentsNew") != null);
rowSumm = rCol
.Sum(s => s.Field <decimal>("paymentsNew"));
if (rowSumm != 0)
{
rep.AddSingleValueObject(rowSumm, startSection, 12);
}
//итого долг - считается сумма текущего по секции - если в -, то долг, если в +, то переплата... для - считается переплата
rowSumm = rSection.Sum(s => s.Field <decimal>("payments") + s.Field <decimal>("paymentsNew"));
if (rowSumm < 0)
{
rep.AddSingleValueObject((-rowSumm), startSection, 16);
}
if (rowSumm > 0)
{
rep.AddSingleValueObject(rowSumm, startSection, 17);
}
// итого % долга - при минусовом rowSumm, делим вот на то число
// закомментить проценты!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//decimal total_sum = rCol.Where(r=>r.Field<decimal>("percDebt")>0).Sum(s => s.Field<decimal>("percDebt"));
//if (rowSumm < 0 && total_sum!=0)
// rep.AddSingleValue((-rowSumm * 100 / total_sum).ToString("0.00") + "%", startSection, 17);
////////////////////////////////////////////////////////////////////////
// итого переплата
rep.Merge(startSection, 2, crow - 1, 2);
rep.Merge(startSection, 9, crow - 1, 9);
rep.Merge(startSection, 10, crow - 1, 10);
rep.Merge(startSection, 12, crow - 1, 12);
rep.Merge(startSection, 16, crow - 1, 16);
//rep.Merge(startSection, 17, crow - 1, 17);
rep.Merge(startSection, 17, crow - 1, 17);
rep.SetCellAlignmentToJustify(startSection, 2, crow - 1, 2);
rep.SetCellAlignmentToJustify(startSection, 9, crow - 1, 9);
rep.SetCellAlignmentToJustify(startSection, 10, crow - 1, 10);
rep.SetCellAlignmentToJustify(startSection, 12, crow - 1, 12);
rep.SetCellAlignmentToJustify(startSection, 16, crow - 1, 16);
//rep.SetCellAlignmentToJustify(startSection, 17, crow - 1, 17);
rep.SetCellAlignmentToJustify(startSection, 17, crow - 1, 17);
//цвет до со 2 до 18 ячейки
if (indexSection % 2 == 1)
{
rep.SetCellColor(startSection, 2, crow - 1, 18, Color.LightGray);
}
indexSection++;
}
rep.Merge(startObject, 1, crow - 1, 1);
rep.SetCellAlignmentToJustify(startObject, 1, crow - 1, 1);
}
rep.SetPageOrientationToLandscape();
rep.SetPageSetup(1, 9999, true);
rep.SetBorders(1, 1, crow - 1, 18);
rep.SetPrintRepeatHead(1, 2);
rep.SetBottomMargin(0);
rep.SetFooterMargin(0);
rep.SetHeaderMargin(0);
rep.SetLeftMargin(0);
rep.SetRightMargin(0);
rep.SetTopMargin(0);
rep.Show();
}