I have 2 SharePoint lists
- Assets list, with these fields:-
- ID
- MediumID
- WorkOrders list, with these fields:-
- ID
- WorkOrderStatus
- WorkOrderType
- TechnicianName
- AssetID
and i am getting the lists items using pnp core sdk inside the below .net core 8.0 console application, as follow:
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using PnP.Core.Auth;
using PnP.Core.Model.SharePoint;
using PnP.Core.QueryModel;
using PnP.Core.Services;
using PnP.Core.Services.Builder.Configuration;
using System;
using System.Collections.Generic;
using System.Runtime.CompilerServices;
using System.Security.Cryptography.X509Certificates;
namespace ConsoleApp4
{
internal class Program
{
static async Task Main(string[] args)
{
var tenantId = "***";
var clientId = "****8";
var certificatePath = @"****";
var certificatePassword = "*****";
// Initialize a new service collection
var serviceCollection = new ServiceCollection();
// Load the certificate
var certificate = new X509Certificate2(certificatePath, certificatePassword, X509KeyStorageFlags.Exportable);
// Configure logging
serviceCollection.AddLogging(builder =>
{
builder.AddConsole();
});
// Add and configure PnP Core SDK
serviceCollection.AddPnPCore(options =>
{
options.PnPContext.GraphFirst = true; // Set true if you prefer to use Graph over CSOM when possible
// options.HttpRequests.UserAgent = "ISV|Contoso|ProductX";
options.Sites.Add("SiteToWorkWith", new PnPCoreSiteOptions
{
SiteUrl = "https://*******.sharepoint.com/sites/S****-PPM",
AuthenticationProvider = new X509CertificateAuthenticationProvider(clientId, tenantId, certificate)
});
});
int i = 0;
// Build the service provider
var serviceProvider = serviceCollection.BuildServiceProvider();
// Use the service provider to get the IPnPContextFactory instance
var pnpContextFactory = serviceProvider.GetRequiredService<IPnPContextFactory>();
// Now you can use the IPnPContextFactory to get a PnPContext and perform operations
var context = await pnpContextFactory.CreateAsync("SiteToWorkWith");
// Assume the fields where not yet loaded, so loading them with the list
var workOrderList = context.Web.Lists.GetByTitle("Work Orders", p => p.Title,
p => p.Fields.QueryProperties(p => p.InternalName,
p => p.FieldTypeKind,
p => p.TypeAsString,
p => p.Title));
// Build a query that only returns the Title field for the first 20 items where the Title field starts with "Item1"
string viewXml = @"<View Scope='RecursiveAll'>
<ViewFields>
<FieldRef Name='Title' />
<FieldRef Name='Created' />
<FieldRef Name='FileLeafRef' />
<FieldRef Name='WorkOrderStatus' />
<FieldRef Name='WorkOrderType' />
<FieldRef Name='TechnicianName' />
<FieldRef Name='FileLeafRef' />
<FieldRef Name='AssetID' />
</ViewFields>
<Query>
<Eq>
<FieldRef Name='FSObjType' />
<Value Type='Integer'>0</Value>
</Eq>
</Query>
</View>";
// Load all the needed data using paged requests
bool paging = true;
string nextPage = null;
while (paging)
{
var output = await workOrderList.LoadListDataAsStreamAsync(new RenderListDataOptions()
{
ViewXml = viewXml,
RenderOptions = RenderListDataOptionsFlags.ListData,
Paging = nextPage ?? null,
}).ConfigureAwait(false);
if (output.ContainsKey("NextHref"))
{
nextPage = output["NextHref"].ToString().Substring(1);
}
else
{
paging = false;
}
}
// Iterate over the retrieved list items
foreach (var listItem in workOrderList.Items.AsRequested())
{
// Do something with the list item
Console.WriteLine(listItem["AssetID"]);
}
//var kpipertechreport = workOrderList.Items.AsRequested().GroupBy("TechnicianName");
var assetsList = context.Web.Lists.GetByTitle("Assets", p => p.Title,
p => p.Fields.QueryProperties(p => p.InternalName,
p => p.FieldTypeKind,
p => p.TypeAsString,
p => p.Title));
string viewXml2 = @"<View Scope='RecursiveAll'>
<ViewFields>
<FieldRef Name='Title' />
<FieldRef Name='Created' />
<FieldRef Name='FileLeafRef' />
<FieldRef Name='MediumID' />
</ViewFields>
<Query>
</Query>
</View>";
// Load all the needed data using paged requests
bool paging2 = true;
string nextPage2 = null;
while (paging2)
{
var output = await assetsList.LoadListDataAsStreamAsync(new RenderListDataOptions()
{
ViewXml = viewXml2,
RenderOptions = RenderListDataOptionsFlags.ListData,
Paging = nextPage ?? null,
}).ConfigureAwait(false);
if (output.ContainsKey("NextHref"))
{
nextPage2 = output["NextHref"].ToString().Substring(1);
}
else
{
paging2 = false;
}
}
var workOrderReport = workOrderList.Items.AsRequested()
// First, map each work order to its corresponding MediumID from the assets list
.Select(wo => new
{
TechnicianName = wo["TechnicianName"],
WorkOrderStatus = wo["WorkOrderStatus"],
Created = DateTime.Parse(wo["Created"].ToString()),
MediumID = (wo["AssetID"]!=null && assetsList.Items.AsRequested().FirstOrDefault(a => a.Id.ToString() == wo["AssetID"].ToString()) != null ? assetsList.Items.AsRequested().FirstOrDefault(a => a.Id.ToString() == wo["AssetID"].ToString())?["MediumID"]: string.Empty)
})
// Group by TechnicianName, then by Year, then by Month, then by MediumID
.GroupBy(wo => wo.TechnicianName)
.Select(groupByTechnician => new
{
Technician = groupByTechnician.Key,
YearlyReport = groupByTechnician
.GroupBy(wo => wo.Created.Year)
.Select(groupByYear => new
{
Year = groupByYear.Key,
MonthlyReport = groupByYear
.GroupBy(wo => wo.Created.Month)
.Select(groupByMonth => new
{
Month = groupByMonth.Key,
MediumReports = groupByMonth
.GroupBy(wo => wo.MediumID)
.Select(groupByMedium => new
{
MediumID = groupByMedium.Key,
TotalCount = groupByMedium.Count(),
DoneCount = groupByMedium.Count(wo => wo.WorkOrderStatus == "Done")
})
})
})
});
foreach (var kpireport in workOrderReport)
{
Console.WriteLine(kpireport.Technician.ToString());
}
// Ensure to dispose the context
context.Dispose();
}
}
}
now i want to build a report, which shows the number of work orders which have their status as Done and the overall total number of work order for each technician within each year and within each month and within each mediumID..but the workOrderReport will contain items count equals to the work orders count, so seems my code is not doing any grouping. can anyone advice why my code is failing?
Thanks