public void DataSetLinq40A()
{
EnumerableRowCollection <DataRow> numbers = testDS.Tables["Numbers"].AsEnumerable();
#region Linq with Lambda - Make Sure to try yourself before looking at the code
var numberGroups =
numbers
.GroupBy(n => n.Field <int>("number") % 5)
.Select(g => new { Remainder = g.Key, Numbers = g });
Console.WriteLine();
Console.WriteLine("***********************************");
Console.WriteLine("Linq with Lambda.");
foreach (var g in numberGroups)
{
Console.WriteLine("Numbers with a remainder of {0} when divided by 5:", g.Remainder);
foreach (var n in g.Numbers)
{
Console.WriteLine(n.Field <int>("number"));
}
}
#endregion
}
void genNode(SettleNode pnode, EnumerableRowCollection <DataRow> rows, int colidx)
{
int colcount = DataSource.Table.Columns.Count;
int idx = 0;
foreach (string sortname in rows.GroupBy(p => p.Field <string>(colidx)).OrderBy(p => p.Key).Select(p => p.Key))
{
SettleNode node = pnode.addNode(sortname);
if (colidx == 0)
{
node.mbrush = MediaHelper.getSolidBrush((EMaterailColor)idx, EMaterialColorDeep.浅色).Clone(); idx++;
}
else
{
node.mbrush = pnode.mbrush.Clone();
}
node.splitDir = colidx % 2 != 0 ? SettleNode.ESplitDir.z方向 : SettleNode.ESplitDir.x方向;
if (colcount - 3 == colidx)
{
node.energy = rows.Where(p => p.Field <string>(colidx) == sortname).Sum(p => double.Parse(p[colcount - 2].ToString()));
node.fee = rows.Where(p => p.Field <string>(colidx) == sortname).Sum(p => double.Parse(p[colcount - 2].ToString()) * double.Parse(p[colcount - 1].ToString()));
}
else
{
EnumerableRowCollection <DataRow> subrows = rows.Where(p => p.Field <string>(colidx) == sortname);
genNode(node, subrows, colidx + 1);
}
}
}
public string Json(
Context context,
SiteSettings ss,
Export export)
{
var data = new JsonExport();
var idColumn = Rds.IdColumn(ss.ReferenceType);
if (export.Header == true)
{
data.Header = new List <JsonExportColumn>();
export.Columns
.Where(o => o.Column.CanRead(
context: context,
ss: ss,
mine: null))
.ForEach(exportColumn =>
{
if (!data.Header.Any(o => o.SiteId == exportColumn.Column.SiteId))
{
data.Header.Add(new JsonExportColumn(
siteId: exportColumn.Column.SiteId,
siteTitle: exportColumn.SiteTitle));
}
data.Header.FirstOrDefault(o => o.SiteId == exportColumn.Column.SiteId)
?.Columns.AddIfNotConainsKey(
exportColumn.Column.Name,
exportColumn.GetLabelText());
});
}
data.Body = new List <IExportModel>();
DataRows
.GroupBy(o => o.Long(idColumn))
.Select(o => o.ToList())
.ForEach(dataRows =>
{
data.Body.Add(JsonStacks(
context: context,
ss: ss,
idColumn: idColumn,
dataRows: dataRows));
});
return(data.ToJson(formatting: Formatting.Indented));
}
private void PaymentsReport(ref int crow, DataTable dtResult)
{
rep.AddSingleValue($"Оплаты за {Config.month[dtpDate.Value.Month]}", 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.SetWrapText(crow, 1, crow, 6);
rep.SetFontBold(crow, 1, crow, 6);
rep.SetCellAlignmentToCenter(crow, 1, crow, 6);
rep.SetCellAlignmentToJustify(crow, 1, crow, 6);
crow++;
//даты
var dates = dtResult.AsEnumerable().GroupBy(r => r.Field <DateTime>("Date")).Select(s => s.Key);
foreach (var date in dates)
{
rep.AddSingleValue(date.ToShortDateString(), crow, 1);
int startDate = crow;
//таблица по датам
EnumerableRowCollection <DataRow> rDate = dtResult.AsEnumerable().Where(r => r.Field <DateTime>("Date") == date);
// объекты
var objects = rDate.GroupBy(r => r.Field <string>("nameObj")).Select(s => s.Key);
foreach (var obj in objects)
{
rep.AddSingleValue(obj, crow, 2);
int startObject = crow;
decimal plan = (decimal)dtMonthReport.AsEnumerable().Where(r => r.Field <string>("nameObj") == obj).First()["planWithDebt"];
//оплаты по объекту
EnumerableRowCollection <DataRow> rObj = rDate.Where(r => r.Field <string>("nameObj") == obj);
foreach (DataRow dr in rObj)
{
rep.AddSingleValueObject(dr["sumPay"], crow, 3);
rep.AddSingleValueObject(dr["payToPlan"], crow, 4);
rep.AddSingleValue(Math.Round((decimal)dr["sumPay"] * 100 / plan, 1) + "%", crow, 5);
rep.AddSingleValue(dr["cashType"].ToString(), crow, 6);
crow++;
}
rep.Merge(startObject, 2, crow - 1, 2);
rep.SetCellAlignmentToJustify(startObject, 2, startObject, 2);
}
rep.Merge(startDate, 1, crow - 1, 1);
rep.SetCellAlignmentToJustify(startDate, 1, startDate, 1);
}
rep.SetBorders(startRow, 1, crow - 1, 6);
}
private void btSave_Click(object sender, EventArgs e)
{
if (dtData == null || dtData.Rows.Count == 0)
{
MessageBox.Show("Выберите ДЗ", "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return;
}
EnumerableRowCollection <DataRow> rowCollect = dtData.AsEnumerable().Where(r => r.Field <bool>("isSelect"));
rowCollectToSend = rowCollect;
if (rowCollect.Count() == 0)
{
MessageBox.Show("Выберите ДЗ", "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return;
}
var groupBonus = rowCollect.GroupBy(r => new { FIOBonus = r.Field <string>("FIOBonus") })
.Select(s => new
{
s.Key.FIOBonus,
SumBonus = s.Sum(r => r.Field <decimal>("SumBonus"))
});
dicListMemorandum.Clear();
sumDZ = 0;
foreach (DataRow row in rowCollect)
{
string str = $"{row["FIOBonus"]} - {row["SumBonus"]}";
int _id = (int)row["id"];
dicListMemorandum.Add(_id, str);
sumDZ += (decimal)row["SumBonus"];
}
textDZ = "";
int npp = 1;
foreach (var v in groupBonus)
{
textDZ += $"{npp}.{v.FIOBonus} - {v.SumBonus}" + Environment.NewLine;
npp++;
}
this.DialogResult = DialogResult.OK;
}
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();
}
public static DataTable Aggregate(EnumerableRowCollection <DataRow> resu, Criteria currentCriteria)
{
IEnumerable <string> columnsToGroupBy = currentCriteria.agKey.Split(',');
IEnumerable <string> sumsToSelect = currentCriteria.agSum.Split(',');
var keys = currentCriteria.agKey.Split(',');
var groupList = resu.GroupBy(x => new NTuple <object>(from column in columnsToGroupBy select x[column])); //.Select(val => new { nK=val.FirstOrDefault().Field<string>(keys[0])+"~"+ val.FirstOrDefault().Field<string>(keys[1]), total=val.Sum(c=>Convert.ToDecimal(c.Field<string>(sumsToSelect.FirstOrDefault()))).ToString()});//new NTuple<object>(from sum in sumsToSelect select val[sum])
if (resu.Count() == 0)
{
return(new DataTable());
}
DataTable aggregatedTable = resu.FirstOrDefault().Table.Clone();
aggregatedTable.Columns.Add("AggregatedKey", typeof(string));
/*foreach (DataColumn col in resu.ElementAtOrDefault(0).Table.Columns)
* {
* table.Columns.Add(col.ColumnName, col.DataType);
* }*/
//DataRow toAdd = aggregatedTable.NewRow();
//toAdd.Table.Columns.Add("AggregatedKey", typeof(string));
//toAdd["AggregatedKey"] = aggregatedKey;
foreach (var group in groupList)
{
string aggregatedKey = "";
DataRow toAdd = group.FirstOrDefault();
if (!(toAdd.Table.Columns.Contains("AggregatedKey")))
{
toAdd.Table.Columns.Add("AggregatedKey", typeof(string));
}
for (int i = 1; i < group.Count(); i++)
{ //each row in the group (except first)
for (int j = 0; j < group.ElementAt(i).Table.Columns.Count; j++) //each column in row
{
var colName = group.ElementAt(i).Table.Columns[j].ColumnName;
if (sumsToSelect.Contains(colName))
{
toAdd[colName] = Convert.ToDouble(toAdd[colName].ToString()) + Convert.ToDouble(group.ElementAt(i)[colName].ToString());
}
else if (toAdd[colName].ToString() != group.ElementAt(i)[colName].ToString())
{
toAdd[colName] = null;//group.ElementAt(i)[colName].ToString();
}
}
}
for (int i = 0; i < columnsToGroupBy.Count(); i++)
{
if (i == 0)
{
aggregatedKey = toAdd[columnsToGroupBy.ElementAt(i)].ToString();
}
else
{
aggregatedKey += "~" + toAdd[columnsToGroupBy.ElementAt(i)].ToString();
}
}
toAdd["AggregatedKey"] = aggregatedKey;
aggregatedTable.Rows.Add(toAdd.ItemArray);
}
return(aggregatedTable);
}