如何按C＃linq中每个类别行的末尾按类别和显示小计进行分组 [英] How to group by categoryid and dispaly subtotal at the end of rows of each category in C# linq
问题描述
Hy good people I am working on a datatable. I want to get the Subtotal for each category and Grand Total.
I want to add the subtotal row at the end of Rows of each. My problem is that I don't get the subtotal for the last category.
Any one who can help me please.
Required Output
+------------+------------+------------+-----------+----------+-------+-----------+
|CategoryID | Category | Orgname | Penalties | Interest | Admin |TotalAmount|
+------------+------------+------------+-----------+----------+-------+-----------+
| 1 | 1-Food | Spar | $10 | $0 | $15.3 | $20.3 |
+-------------------------+------------+-----------+----------+-------+-----------+
| 1 | 1-Food |Pick n’ Pay | $0 | $10 | $20 | $30 |
+------------+------------+------------+-----------+----------+-------+-----------+
| Subtotal | $10 | $10 | $30.3 | $50.3 |
+-------------------------+------------+-----------+----------+-------+-----------+
| 2 | 2-Auto | BMW | $0 | $30 | $55 | $85 |
| 2 | 2-Auto | Toyota | $9 | $0 | $14.5 | $23.5 |
| 2 | 2-Auto | Jaguar | $22.8 | $8.2 | $50 | $81 |
+-------------------------+------------+-----------+----------+-------+-----------+
| Subtotal | $31.8 | $38.2 | $119.5| $189.5 |
+-------------------------+------------+-----------+----------+-------+-----------+
| 3 | 3-Banking | Absa | $0 | $40 | $155 | $190 |
+-------------------------+------------+-----------+----------+-------+-----------+
| Subtotal | $0 | $40 | $155 | $190 |
+-------------------------+------------+-----------+----------+-------+-----------+
| Grand Total | $41.8 | $88.2 | $304.8| $429,8 |
+-------------------------+------------+-----------+----------+-------+-----------+
Current Output
+------------+------------+------------+-----------+----------+-------+-----------+
|CategoryID | Category | Orgname | Penalties | Interest | Admin |TotalAmount|
+------------+------------+------------+-----------+----------+-------+-----------+
| 1 | 1-Food | Spar | $10 | $0 | $15.3 | $20.3 |
+-------------------------+------------+-----------+----------+-------+-----------+
| 1 | 1-Food |Pick n’ Pay | $0 | $10 | $20 | $30 |
+------------+------------+------------+-----------+----------+-------+-----------+
| Subtotal | $10 | $10 | $30.3 | $50.3 |
+-------------------------+------------+-----------+----------+-------+-----------+
| 2 | 2-Auto | BMW | $0 | $30 | $55 | $85 |
| 2 | 2-Auto | Toyota | $9 | $0 | $14.5 | $23.5 |
| 2 | 2-Auto | Jaguar | $22.8 | $8.2 | $50 | $81 |
+-------------------------+------------+-----------+----------+-------+-----------+
| Subtotal | $31.8 | $38.2 | $119.5| $189.5 |
+-------------------------+------------+-----------+----------+-------+-----------+
| 3 | 3-Banking | Absa | $0 | $40 | $155 | $190 |
+-------------------------+------------+-----------+----------+-------+-----------+
| Grand Total | $41.8 | $88.2 | $304.8| $429,8 |
+-------------------------+------------+-----------+----------+-------+-----------+
我尝试过：
var query =（来自_entnsaction中的_transaction .Transactions
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
group new {_trans = _transaction，cd = _cd，}
by new {_transaction.CategoryID，_transaction.Refno，_cd.Orgname，_category.Description} into _group
orderby _group.Key.CategoryID
选择新的
{
CategoryID = _group.Key.CategoryID，
Category = _group.Key.CategoryID + - + _group.Key.Description，
Refno = _group.Key.Refno，
Orgname = _group.Key。 Orgname，
惩罚= _group.Sum（x => x._trans.Penalties），
兴趣= _group.Sum（x => x._trans.Interest），
Admin = _group.Sum（x => x._trans.Admin），
TotalAmount = _group.Sum（x => x._trans.TotalAmount），
}）;
DataTable dt = new DataTable（）;
DataSet ds = new DataSet（）;
ds.Tables.Add（query.CopyToDataTable（））; ds.Tables [0] .TableName =Table1;
dt = ds.Tables [0];
//获取小计
long _CategoryID = 0; double Admin = 0;双重利息= 0;
双倍惩罚= 0; double TotalAmount = 0; string Title = string.Empty;
for（int i = 0; i< = dt.Rows.Count - 1; i ++）
{
if（i> 0）
{
if（dt.Rows [i] [Category]。ToString（）。ToLower（）！= dt.Rows [i - 1] [Category]。ToString（）。ToLower（））
{
dt.Rows.InsertAt（dt.NewRow（） ，i）;
dt.Rows [i] [CategoryID] = _CategoryID;
_CategoryID = 0;
dt.Rows [ i] [Category] = Title;
Title = string.Empty;
dt.Rows [i] [Admin] = Admin;
Admin = 0;
dt.Rows [i] [Interest] =兴趣;
兴趣= 0;
dt.Rows [i] [Penalties] =处罚;
处罚= 0;
dt.Rows [i] [TotalAmount] = TotalAmount;
TotalAmount = 0;
i ++;
}
}
Title =Subtotal ;
_CategoryID = Convert.ToInt64（dt.Rows [i] [CategoryID]）;
Admin + = Convert.To双（dt.Rows [i] .IsNull（管理员）？ 0.0：dt.Rows [i] .Field< double>（Admin））;
兴趣+ = Convert.ToDouble（dt.Rows [i] .IsNull（兴趣）？0.0 ：dt.Rows [i] .Field< double>（Interest））;
惩罚+ = Convert.ToDouble（dt.Rows [i] .IsNull（Penalties）？0.0： dt.Rows [i] .Field< double>（Penalties））;
TotalAmount + = Convert.ToDouble（dt.Rows [i] .IsNull（TotalAmount）？0.0：dt .Rows [i] .Field< double>（TotalAmount））;
}
//总计
var subtotal = query .GroupBy（x => x.CategoryID）.Select（s => new
{
CategoryID = s.Key，
ISub = s.Sum（x => x.Interest），
ASub = s.Sum（x => x.Admin），
PSub = s。 Sum（x => x.Penalties），
TASub = s.Sum（x => x.TotalAmount）
}）;
var GrandTotal = subtotal.Select（s => new
{
GI = subtotal.Sum（x => x.ISub），
GA = subtotal.Sum（x => x.ASub），
GP = subto tal.Sum（x => x.PSub），
GTA = subtotal.Sum（x => x.TSub）
}）。Distinct（）;
//将总行添加到数据表中
foreach（在GrandTotal中变换a）
{
var dr = dt.NewRow（）;
dr [Category] =总计;
dr [兴趣] = a.GI;
dr [Admin] = a.GA;
dr [Penalties] = a.GP;
dr [TotalAmount] = a .GTA;
dt.Rows.Add（dr）;
}
//按摩我的查询
foreach（dt.Rows中的DataRow行）
{
foreach（DataColumn col in dt.Columns）
{
if（row [col.ColumnName] == DBNull.Value）
{
if（col.DataType == typeof（System.Double））row [col.ColumnName] = 0.0;
if（col.DataType == typeof（System.Int32） ））row [col.ColumnName] = 0;
if（col.DataType == typeof（System.String））row [col.ColumnName] = String.Empty;
}
}
}
What I have tried:
var query = (from _transaction in _entities.Transactions
join _cd in _entities.Organisations on _transaction.Refno equals _cd.Refno
join _category in _entities.Categorys on _transaction.CategoryID equals _category.CategoryID
group new{_trans = _transaction, cd = _cd,}
by new { _transaction.CategoryID, _transaction.Refno, _cd.Orgname, _category.Description } into _group
orderby _group.Key.CategoryID
select new
{
CategoryID = _group.Key.CategoryID,
Category = _group.Key.CategoryID + " - " + _group.Key.Description,
Refno = _group.Key.Refno,
Orgname = _group.Key.Orgname,
Penalties = _group.Sum(x => x._trans.Penalties),
Interest = _group.Sum(x => x._trans.Interest),
Admin = _group.Sum(x => x._trans.Admin),
TotalAmount = _group.Sum(x => x._trans.TotalAmount),
});
DataTable dt = new DataTable();
DataSet ds = new DataSet();
ds.Tables.Add(query.CopyToDataTable()); ds.Tables[0].TableName = "Table1";
dt = ds.Tables[0];
//Get Subtotal
long _CategoryID = 0; double Admin = 0; double Interest = 0;
double Penalties = 0; double TotalAmount = 0; string Title = string.Empty;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
if (i > 0)
{
if (dt.Rows[i]["Category"].ToString().ToLower() != dt.Rows[i - 1]["Category"].ToString().ToLower())
{
dt.Rows.InsertAt(dt.NewRow(), i);
dt.Rows[i]["CategoryID"] = _CategoryID;
_CategoryID = 0;
dt.Rows[i]["Category"] = Title;
Title = string.Empty;
dt.Rows[i]["Admin"] = Admin;
Admin = 0;
dt.Rows[i]["Interest"] = Interest;
Interest = 0;
dt.Rows[i]["Penalties"] = Penalties;
Penalties = 0;
dt.Rows[i]["TotalAmount"] = TotalAmount;
TotalAmount = 0;
i++;
}
}
Title = "Subtotal";
_CategoryID = Convert.ToInt64(dt.Rows[i]["CategoryID"]);
Admin += Convert.ToDouble(dt.Rows[i].IsNull("Admin") ? 0.0 : dt.Rows[i].Field<double>("Admin"));
Interest += Convert.ToDouble(dt.Rows[i].IsNull("Interest") ? 0.0 : dt.Rows[i].Field<double>("Interest"));
Penalties += Convert.ToDouble(dt.Rows[i].IsNull("Penalties") ? 0.0 : dt.Rows[i].Field<double>("Penalties"));
TotalAmount += Convert.ToDouble(dt.Rows[i].IsNull("TotalAmount") ? 0.0 : dt.Rows[i].Field<double>("TotalAmount"));
}
// Grand Total
var subtotal = query.GroupBy(x => x.CategoryID).Select(s => new
{
CategoryID = s.Key,
ISub = s.Sum(x => x.Interest),
ASub = s.Sum(x => x.Admin),
PSub = s.Sum(x => x.Penalties),
TASub = s.Sum(x => x.TotalAmount)
});
var GrandTotal = subtotal.Select(s => new
{
GI = subtotal.Sum(x => x.ISub),
GA = subtotal.Sum(x => x.ASub),
GP = subtotal.Sum(x => x.PSub),
GTA = subtotal.Sum(x => x.TSub)
}).Distinct();
//add grand total row to datatable
foreach (var a in GrandTotal)
{
var dr = dt.NewRow();
dr["Category"] = "Grand Total";
dr["Interest"] = a.GI;
dr["Admin"] = a.GA;
dr["Penalties"] = a.GP;
dr["TotalAmount"] = a.GTA;
dt.Rows.Add(dr);
}
//massaging my query
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
if (row[col.ColumnName] == DBNull.Value)
{
if (col.DataType == typeof(System.Double)) row[col.ColumnName] = 0.0;
if (col.DataType == typeof(System.Int32)) row[col.ColumnName] = 0;
if (col.DataType == typeof(System.String)) row[col.ColumnName] = String.Empty;
}
}
}
推荐答案
10 |
0 |
15.3 |
这篇关于如何按C＃linq中每个类别行的末尾按类别和显示小计进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
