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