private void ObjectReport(ref int crow)
{
rep.AddSingleValue($"План - {Config.month[dtpDate.Value.Month]} {dtpDate.Value.Year}", crow, 1);
rep.SetFontBold(crow, 1, crow, 1);
crow++;
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.SetCellAlignmentToCenter(crow, 1, crow, 8);
rep.SetCellAlignmentToJustify(crow, 1, crow, 8);
rep.SetFontBold(crow, 1, crow, 8);
rep.SetWrapText(crow, 1, crow, 8);
crow++;
foreach (DataRow dr in dtMonthReport.Rows)
{
rep.AddSingleValue(dr["nameObj"].ToString(), crow, 1); // объект
rep.AddSingleValueObject(dr["sumPlan"], crow, 2); // план
rep.AddSingleValueObject(dr["currPayment"], crow, 3); // оплаты плана
if (decimal.Parse(dr["sumPlan"].ToString()) > 0)
{
rep.AddSingleValue((Math.Round(decimal.Parse(dr["currPayment"].ToString()) * 100 / decimal.Parse(dr["sumPlan"].ToString()), 1)).ToString("0.0") + "%", crow, 4); //суммарный % оплаты
}
rep.AddSingleValueObject(dr["debt"], crow, 5); //долг за предыдущий период
rep.AddSingleValueObject(dr["planWithDebt"], crow, 6); //план с учетом пред. долга
rep.AddSingleValueObject(dr["allPayments"], crow, 7); //оплаты всего
if (decimal.Parse(dr["sumPlan"].ToString()) > 0)
{
rep.AddSingleValue((Math.Round(decimal.Parse(dr["allPayments"].ToString()) * 100 / decimal.Parse(dr["planWithDebt"].ToString()), 1)).ToString("0.0") + "%", crow, 8); //суммарный % оплаты
}
crow++;
}
rep.SetBorders(2, 1, crow - 1, 8);
crow++;
}
private void AddHeaderSection(ref int crow)
{
int maxCol = 18;
rep.AddSingleValue("Объект", crow, 1);
rep.AddSingleValue("Секция", crow, 2);
rep.AddSingleValue("Арендатор", crow, 3);
rep.AddSingleValue("Номер договора", crow, 4);
rep.AddSingleValue("Сумма по договору", crow, 5);
rep.AddSingleValue($"План {monthPlan}", crow, 6);
rep.AddSingleValue($"Долг на {datePlan}", crow, 7);
rep.AddSingleValue($"Переплата на {datePlan}", crow, 8);
rep.AddSingleValue($"Итого долг на {datePlan}", crow, 9);
rep.AddSingleValue($"Итого переплата на {datePlan}", crow, 10);
rep.AddSingleValue("Сумма оплаты", crow, 11);
rep.Merge(crow, 11, crow + 1, 12);
rep.AddSingleValue("Долг тек.", crow, 13);
rep.AddSingleValue("Переплата тек.", crow, 15);
rep.AddSingleValue("Тек. % долга", crow, 14);
rep.AddSingleValue("Итого долг", crow, 16);
//rep.AddSingleValue("Итого % долга", crow, 17);
rep.AddSingleValue("Итого переплата", crow, 17);
rep.AddSingleValue("Арендодатель", crow, 18);
for (int i = 1; i <= 19; i++)
{
if (i == 11 || i == 12)
{
continue;
}
rep.Merge(crow, i, crow + 1, i);
}
rep.SetCellAlignmentToCenter(crow, 1, crow, maxCol);
rep.SetCellAlignmentToJustify(crow, 1, crow, maxCol);
rep.SetFontBold(crow, 1, crow + 1, maxCol);
rep.SetWrapText(crow, 1, crow + 1, maxCol);
crow += 2;
}
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 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();
}
/// <summary>
/// Таблица
/// </summary>
/// <param name="report">Отчет</param>
/// <param name="id">id Сметы</param>
/// <param name="date">Дата выдачи денежных средств</param>
/// <returns>Отчет</returns>
ExcelUnLoad addTable(ExcelUnLoad report, int id, DateTime date)
{
int row = 4;
DataTable dtTable = readSQL.getContentEstimate(id);
dtTable.Select("Status = 1");
dtTable.Columns.Remove("isSelect");
dtTable.Columns.Remove("id_ComponentsHardware");
dtTable.Columns.Remove("TypeComponentsHardware");
dtTable.Columns.Remove("Link");
dtTable.Columns.Remove("Description");
dtTable.Columns.Remove("isLink");
dtTable.Columns.Remove("id");
dtTable.Columns.Remove("Status");
dtTable.Columns.Remove("nameStatus");
dtTable.Columns.Remove("nameStatusConfirm");
dtTable.Columns.Remove("StatusConfirmation");
dtTable.Columns.Remove("Purchase");
dtTable.Columns.Remove("namePurchase");
dtTable.Columns.Remove("Delivery");
dtTable.Columns.Remove("id_component_link");
dtTable.Columns.Remove("sort");
dtTable.Columns.Remove("pos");
dtTable.Columns.Remove("nameTypeLinkView");
dtTable.Columns.Remove("StatusBuild");
dtTable.Columns.Remove("Comments");
DataColumn dc = new DataColumn();
dc.ColumnName = "dateEdit";
dc.DataType = typeof(String);
dc.DefaultValue = date.ToShortDateString();
dtTable.Columns.Add(dc);
dtTable.Columns["row"].SetOrdinal(0);
dtTable.Columns["nameType"].SetOrdinal(1);
dtTable.Columns["cName"].SetOrdinal(2);
dtTable.Columns["Count"].SetOrdinal(3);
dtTable.Columns["Price"].SetOrdinal(4);
dtTable.Columns["summa"].SetOrdinal(5);
dtTable.Columns["dateEdit"].SetOrdinal(6);
int count = dtTable.Rows.Count;
for (int i = row; i < row + count; i++)
{
report.Merge(i, 5, i, 6);
}
report.AddMultiValue(dtTable, row, 1);
report.SetBorders(row, 1, row + count - 1, 7);
report.SetCellAlignmentToCenter(row, 1, row + count - 1, 1);
report.SetCellAlignmentToRight(row, 4, row + count - 1, 7);
report.SetCellAlignmentToJustify(row, 3, row + count - 1, 3);
report.SetWrapText(row, 3, row + count - 1, 3);
amount = Convert.ToDecimal(dtTable.Compute("sum(summa)", "").ToString());
row += count;
posFooter = row;
return(report);
}
