如果另一列的值与使用 C# 和 CAML 匹配,则 SharePoint 列表中的列值总和
本文关键字:CAML 匹配 SharePoint 列表 一列 如果 | 更新日期: 2023-09-27 18:31:10
>我正在尝试在sharepoint列表中获取经理的值,当经理值相同时,我正在尝试将所有金额相加,然后输出经理姓名和总和。
以下是共享点列表中的条目
Manager Amount
1 1000
2 2000
3 3000
4 500
1 1500
2 2500
然后我应该发送一封应该有此输出的电子邮件,并只返回前 3 个最高金额:
Manager Amount
2 4500
3 3000
1 2500
这是我的 CAML 查询
camlQuery.ViewXml = " <Query><Where><And><IsNotNull><FieldRef Name='Manager' /></IsNotNull><IsNotNull><FieldRef Name='Amount' /></IsNotNull></And><OrderBy><FieldRef Name='Amount' Ascending='False' /></OrderBy></Where></Query> ";
这是我的代码
double iSum = 0;
foreach (ListItem oListItem in collListItem)
{
FieldUserValue man = (FieldUserValue)oListItem["Manager"];
if(oListItem["Amount"].ToString() == null)
continue;
iSum += Convert.ToDouble(oListItem["Amount"].ToString());
Console.WriteLine("'nManager Name: " + man.LookupValue + " Amount: " + iSum.ToString());
message += "<tr>"
+ " <td class='pdetails'> " + man.LookupValue + "</td> "
+ " <td class='pdetails'> " + iSum.ToString() + "</td></tr> ";
}
请帮助我修复我的 caml 查询和 foreach 循环以及如何获得预期的输出,即每个经理的金额总和。请和谢谢。
我手边没有 SharePoint,所以列表访问代码可能不太正确,但其余代码应该做你想要的。
ManagerSummary summary = new ManagerSummary();
foreach (ListItem oListItem in collListItem)
{
FieldUserValue managerValue = (FieldUserValue)oListItem["Manager"];
string managerId = managerValue.LookupId;
string managerName = managerValue.LookupValue;
double amount = Convert.ToDouble(oListItem["Amount"].ToString());
summary.AddValue(managerId, managerName, amount);
}
StringBuilder output = new StringBuilder();
output.AppendLine("<tr><th>Manager Name</th><th>Total</th></tr>");
foreach (var manager in summary.TopManagers(3))
{
output.AppendFormat("<tr><td>{0}</td><td>{1}</td></tr>", manager.Name, manager.Total);
output.AppendLine();
}
Console.WriteLine(output.ToString());
class ManagerSummary
{
Dictionary<string, Manager> _managers = new Dictionary<string, Manager>();
public void AddValue(string managerId, string managerName, double amount)
{
if (_managers.ContainsKey(managerId))
{
_managers[managerId].Total += amount;
}
else
{
_managers[managerId] = new Manager { Id = managerId, Name = managerName, Total = amount };
}
}
public List<Manager> TopManagers(int count)
{
var managers = _managers.Values.ToList();
return managers.OrderByDescending(x => x.Total).Take(count).ToList();
}
}
class Manager
{
public string Id { get; set; }
public string Name { get; set; }
public double Total { get; set; }
public override string ToString()
{
return string.Format("{0,-20}{1,-10}", Name, Total);
}
}
您可以使用
LINQ GroupBy
为您处理管理器的分组,以及组中所有组件的总和。
var query = collListItem.OfType<ListItem>()
.Select(item => new
{
Manager = (FieldUserValue)item["Manger"],
Amount = item["Amount"] as double?,
})
.GroupBy(item => item.Manager.LookupValue)
.Select(group => new
{
Manager = group.Key,
Amount = group.Sum(item => item.Amount),
})
.Select(group => string.Format(
@"<tr><td class='pdetails'>{0}</td>
<td class='pdetails'>{1}</td></tr>", group.Manager, group.Amount));
string message = string.Concat(query);