I'm trying to figure out how to populate table with data coming from DataTable using QuestPDF.
I'm currently using JSON as a data source. The JSON is formatted like this:
{
"Brother MFC (60)": {
"AssetId": 60,
"AssetName": "Brother MFC",
"AssetTag": "PRN-001",
"AssetTypeId": 19,
"AssetTypeName": "Office Equipment",
"Parameters": {
"Color (12)": "12"
}
},
"Office Table (63)": {
"AssetId": 63,
"AssetName": "Office Table",
"AssetTag": "TAB-002",
"AssetTypeId": 19,
"AssetTypeName": "Office Equipment",
"Parameters": {
"Color (12)": "2",
"Height (13)": "300"
}
},
"Office Desk (64)": {
"AssetId": 64,
"AssetName": "Office Desk",
"AssetTag": "DESK-03",
"AssetTypeId": 9,
"AssetTypeName": "New Office Equipment",
"Parameters": {
"Color (12)": "4",
"Height (13)": "400"
}
}}
I have variable "Parameters" that can vary depending on the asset, and I want to display them in separate columns. If there is a matching column name, I need to show the value corresponding to that parameter. Parameters are stored in database as records and not column names. Therefore, they are completely dynamic (for example we can have multiple parameters like "color," "width," "type of plastic," etc.)
To accomplish this, I'm converting a JSON file into a DataTable. Although the debugger correctly shows the number of columns, I'm having difficulty displaying "Parameters" using QuestPDF. Even though I have included the necessary columns, the library seems to be skipping over "Parameters." Columns related to "Assets" show up without any issues.
I believe that some of the data necessary for QuestPDF is not accessible while the application is running, but I am unsure of how to address this problem. I need to use Dictionaries to return Parameters, so I need to use dynamic model and dictionary for that.
Expected sample output:
| AssetName | ... | Color (12) | Height (13) |
|---|---|---|---|
| Brother MFC | ... | 12 | |
| Office Table | ... | 2 | 300 |
| Office Desk | ... | 4 | 400 |
Below is my code for converting JSON into DataTable:
public class ReportsController : Controller
{
private readonly ApplicationDbContext _context;
public ReportsController(ApplicationDbContext context)
{
_context = context;
}
public static class DynamicModel
{
public static dynamic CreateModel()
{
return new ExpandoObject();
}
public static void SetProperty(dynamic model, string propertyName, object propertyValue)
{
var expando = (IDictionary<string, object>)model;
expando[propertyName] = propertyValue;
}
public static object GetProperty(dynamic model, string propertyName)
{
var expando = (IDictionary<string, object>)model;
return expando.ContainsKey(propertyName) ? expando[propertyName] : null;
}
}
public class AssetParam
{
public int AssetId { get; set; }
public string AssetName { get; set; }
public string AssetTag { get; set; }
public int AssetTypeId { get; set; }
public string AssetTypeName { get; set; }
public Dictionary<string, string> Parameters { get; set; }
}
public static DataTable JsonToDataTable(string jsonString)
{
var assets = JsonConvert.DeserializeObject<Dictionary<string, AssetParam>>(jsonString);
var dataTable = new DataTable();
// Add columns for non-parameter properties
dataTable.Columns.Add("AssetId", typeof(int));
dataTable.Columns.Add("AssetName", typeof(string));
dataTable.Columns.Add("AssetTag", typeof(string));
dataTable.Columns.Add("AssetTypeId", typeof(int));
dataTable.Columns.Add("AssetTypeName", typeof(string));
// Loop over each asset and add its parameters to the DataTable as columns
foreach (var asset in assets.Values)
{
foreach (var parameter in asset.Parameters)
{
if (!dataTable.Columns.Contains(parameter.Key))
{
dataTable.Columns.Add(parameter.Key, typeof(string));
}
}
}
// Loop over each asset and add a new row to the DataTable
foreach (var asset in assets.Values)
{
var row = dataTable.NewRow();
// Add non-parameter properties to the row
row["AssetId"] = asset.AssetId;
row["AssetName"] = asset.AssetName;
row["AssetTag"] = asset.AssetTag;
row["AssetTypeId"] = asset.AssetTypeId;
row["AssetTypeName"] = asset.AssetTypeName;
// Add parameter values to the row
foreach (var parameter in asset.Parameters)
{
row[parameter.Key] = parameter.Value;
}
dataTable.Rows.Add(row);
}
return dataTable;
}
[HttpGet]
public IActionResult GetParameterAssetsReport(int[] selectedParameterIDs, int[] selectedAssetIDs)
{
try
{
var getSelectedAssets = _context.Assets.Include(s => s.AssetTypes)
.Where(i => selectedAssetIDs.Contains(i.Id))
.ToList();
var getSelectedParameterValues = _context.AssetParamInt.Include(s => s.Parameter)
.Where(i => selectedParameterIDs.Contains(i.ParameterID))
.ToList();
dynamic myModel = DynamicModel.CreateModel();
var assetsDistinct = _context.AssetParamInt
.Include(i => i.Assets)
.Where(i.Parameter.ParameterType.isAsset)
.Select(s => new
{
AssetId = s.AssetID,
AssetName = s.Assets.AssetName,
AssetTypeName = s.Assets.AssetTypes.AssetTypeName,
AssetTypeNameId = s.Assets.AssetTypes.Id,
}).Distinct().ToList();
// get asset types
var assetTypesDistinct1 = assetsDistinct.Select(s => new
{
AssetTypeNameId = s.AssetTypeNameId,
AssetTypeName = s.AssetTypeName
}
).Distinct();
foreach (var asset in getSelectedAssets)
{
dynamic record = new ExpandoObject();
record.AssetId = asset.Id;
record.AssetName = asset.AssetName;
record.AssetTag = asset.AssetTag;
record.AssetTypeId = asset.AssetTypes.Id;
record.AssetTypeName = asset.AssetTypes.AssetTypeName;
var headers = new Dictionary<string, string>();
var selectedAssetParameters = getSelectedParameterValues.Where(s => s.AssetID == asset.Id).ToList();
foreach (var parameters in selectedAssetParameters)
{
if (!headers.ContainsKey(parameters.Parameter.ParameterName + " (" + parameters.ParameterID + ")"))
{
headers.Add(parameters.Parameter.ParameterName + " (" + parameters.ParameterID + ")", parameters.ParameterValue);
}
}
record.Parameters = headers;
DynamicModel.SetProperty(myModel, asset.AssetName + " (" + asset.Id + ")", record);
}
string json = JsonConvert.SerializeObject(myModel, new ExpandoObjectConverter());
// store in DataTable
DataTable dtAssetsParams = JsonToDataTable(json);
DateTime now = DateTime.Now;
DateTime utc = DateTime.UtcNow;
TimeZoneInfo localZone = TimeZoneInfo.Local;
var ms = new MemoryStream();
Document.Create(container =>
{
foreach (var assetType in assetTypesDistinct1)
{
container.Page(page =>
{
page.Size(PageSizes.Letter.Landscape());
page.Margin(1, Unit.Centimetre);
page.DefaultTextStyle(x => x.FontSize(8).FontFamily("Arial"));
page.Header()
.Text("Asset Parameter Report")
.SemiBold().FontSize(14).FontColor(Colors.Blue.Medium);
page.Content()
.Column(x =>
{
x.Spacing(10);
x.Item().Text("Asset Type: " + assetType.AssetTypeName).SemiBold().FontSize(12);
x.Item().Table(table =>
{
table.ColumnsDefinition(columns =>
{
// loop through all columns
foreach (DataColumn column in dtAssetsParams.Columns)
{
columns.RelativeColumn();
}
});
table.Header(header =>
{
foreach (DataColumn column in dtAssetsParams.Columns)
{
var columnName = column.ColumnName;
header.Cell().Element(CellStyle).AlignCenter().Text(columnName);
}
static IContainer CellStyle(IContainer container)
{
return container.DefaultTextStyle(x => x.SemiBold()).BorderBottom(1).BorderColor(Colors.Black);
}
});
/* Cells go here (removed for now) -----
table.Cell().Element(CellStyle).Text("cell data");
---------------------------------------- */
static IContainer CellStyle(IContainer container)
{
return container.Border(1).BorderColor(Colors.Grey.Lighten3).Padding(3);
}
});
});
page.Footer()
.AlignCenter()
.Text(x =>
{
x.Span("Page ");
x.CurrentPageNumber();
x.Line("");
x.Span("Generated on " + $"{now:f} " + localZone.DisplayName).FontSize(8).FontColor(Colors.Grey.Medium);
});
});
}
}).GeneratePdf(ms);
ms.Position = 0;
return File(ms, "application/pdf");
}
catch (Exception e)
{
return BadRequest();
}
}
}
Please, let me know if you need more details.
Tested:
The code was working fine, but there was a problem with an AJAX call. I was calling the function twice, but because the second call was right after the first, it was difficult to debug. The debugger was showing me the correct values, but the second call was returning an empty file without passing any data.