public async Task <long> ReadTotalAuditsForHourAsync(AuditQuery query, int auditArtifactTypeId, IList <int> actionChoiceIds, int userGroupById)
{
var pivotSettings = new PivotSettings()
{
ArtifactTypeID = auditArtifactTypeId,
GroupBy = new global::Relativity.Services.Field.FieldRef(userGroupById),
ObjectSetQuery = new global::Relativity.Services.Query()
{
Condition =
this.dataGridConditionBuilder.BuildActionTimeframeCondition(actionChoiceIds.ToArray(), query.StartTime,
query.EndTime)
}
};
using (var auditManagerProxy = this.auditLogObjectManagerFactory.GetManager())
{
try
{
var result = await
auditManagerProxy.ExecuteAsync(
query.WorkspaceId,
pivotSettings,
CancellationToken.None,
new NonCapturingProgress <string>());
if (!result.Success)
{
throw new Exception(
$"Read TotalAudits returned unsuccessful with message: {result.Message}");
}
try
{
if (result.Results.Columns.Contains(DataGridResourceConstants.DataGridGrandTotal))
{
return(result.Results.AsEnumerable().Sum(x => Convert.ToInt64(x[DataGridResourceConstants.DataGridGrandTotal])));
}
// Column doesn't exist, no values returned
await this.logger.LogVerboseAsync($"No audit totals returned... {result.Results.ToJson()} -- {result.Results.Columns[DataGridResourceConstants.DataGridGrandTotal]} -- WorkspaceId {query.WorkspaceId}, StartTime {query.StartTime}, EndTime {query.EndTime}", Names.LogCategory.DataGrid);
return(0);
}
catch (Exception e)
{
throw new Exception($"Failed to parse result.Results {result.Results.ToJson()} -- {result.Results.Columns[DataGridResourceConstants.DataGridGrandTotal]}", e);
}
}
catch (Exception e)
{
throw this.DataGridServiceException(e, query, $"Failed to read TotalAudits, PivotSettings: {pivotSettings.ToJson()}");
}
}
}
public static void CreateExcelPivot(Stream outputStream, DataTable data, PivotSettings settings)
{
using (var pckg = new ExcelPackage())
{
var book = pckg.Workbook;
var pivotSheet = book.Worksheets.Add("Pivot");
var dataSheet = book.Worksheets.Add("Data");
var dataRange = GenerateDataSheet(data, dataSheet);
GeneratePivotSheet(pivotSheet, dataRange, settings);
pckg.SaveAs(outputStream);
}
}
public async Task ExecuteAsync()
{
var apiClientHelper = new ApiClientHelper();
var auditLogObjectManager = apiClientHelper.GetKeplerServiceReference <IExternalAuditLogObjectManager>();
using (var proxy = auditLogObjectManager.Value)
{
var pivotSettings = new PivotSettings()
{
ObjectSetQuery =
new Query(
"(('Action' IN CHOICE [1057247])) AND (('Timestamp' >= 0001-01-01T00:00:00.00Z)) AND (('Timestamp' <= 2018-07-24T18:47:21.59Z))",
new List <Sort>()),
ArtifactTypeID = 1057150,
GroupBy = new FieldRef(1057179)
};
var result = await proxy.ExecuteAsync(1039923, pivotSettings, new CancellationToken(), new NonCapturingProgress <string>());
}
}
private static void GeneratePivotSheet(ExcelWorksheet pivotSheet, ExcelRangeBase dataRange, PivotSettings settings)
{
//var rowCount = data.Rows.Count;
//var columnCount = data.Columns.Count;
var pt = pivotSheet.PivotTables.Add(pivotSheet.Cells["A1"], dataRange, "PivotTable");
pt.MultipleFieldFilters = true;
pt.RowGrandTotals = true;
pt.ColumGrandTotals = true;
pt.Compact = true;
pt.CompactData = true;
pt.GridDropZones = false;
pt.Outline = false;
pt.OutlineData = false;
pt.ShowError = true;
pt.ErrorCaption = "[error]";
pt.ShowHeaders = true;
pt.UseAutoFormatting = true;
pt.ApplyWidthHeightFormats = true;
pt.ShowDrill = true;
pt.DataOnRows = false;
pt.FirstHeaderRow = 1; // first row has headers
pt.FirstDataCol = 1; // first col of data
pt.FirstDataRow = 2; // first row of data
pt.TableStyle = TableStyles.Medium6;
//pt.ColumGrandTotals = true;
//pt.RowGrandTotals = true;
//pt.GrandTotalCaption = "Genel Toplam";
// pt.RowHeaderCaption = "";
foreach (var column in settings.Columns)
{
var field = pt.Fields[column.Field];
var pivotField = pt.ColumnFields.Add(field);
if (column.PivotDataType == PivotDataType.Date)
{
//pivotField.AddDateGrouping(eDateGroupBy.Days);
}
}
foreach (var row in settings.Rows)
{
var field = pt.Fields[row.Field];
var pivotField = pt.RowFields.Add(field);
if (row.PivotDataType == PivotDataType.Date)
{
//pivotField.AddDateGrouping(eDateGroupBy.Days);
}
}
foreach (var measurement in settings.Measurements)
{
var field = pt.Fields[measurement.Field];
var pivotField = pt.DataFields.Add(field);
pivotField.Format = measurement.DisplayFormat;
pivotField.Function = DataFieldFunctions.Sum;
pivotField.Name = measurement.Caption;
}
}
