// création de l'onglet AuditTrail
// cet onglet est utilisé pour stocker l'historique
Worksheet CreateAuditTrailSheet(SpreadSheetFile spreadSheet, string name)
{
var auditTrail = spreadSheet.FindSheet(name);
if (auditTrail == null)
{
auditTrail = spreadSheet.FindOrCreateSheet(name);
spreadSheet.SetCellValue(auditTrail, "A", 1, "Event ID");
spreadSheet.SetCellValue(auditTrail, "B", 1, "TimeStamp (UTC)");
spreadSheet.SetCellValue(auditTrail, "C", 1, "User Id");
spreadSheet.SetCellValue(auditTrail, "D", 1, "Action");
}
return(auditTrail);
}
// création de l'onglet "Summary" (tableau qui consolide le nombre d'évènements mois par mois)
void CreateSummarySheet(SpreadSheetFile spreadSheet, string name, int year)
{
name = name.Replace("{year}", year.ToString(), StringComparison.InvariantCultureIgnoreCase);
if (spreadSheet.FindSheet(name) == null)
{
var summary = spreadSheet.FindOrCreateSheet(name);
spreadSheet.SetCellValue(summary, "B", 2, "Automation UserId");
spreadSheet.SetCellValue(summary, "E", 2, "0000");
spreadSheet.SetCellValue(summary, "B", 3, "Administration UserId");
spreadSheet.SetCellValue(summary, "E", 3, "0000");
spreadSheet.SetCellValue(summary, "B", 4, "CAST UserId");
spreadSheet.SetCellValue(summary, "E", 4, "0000");
var events = new [] {
new EventInfo()
{
Category = "LOGIN", Label = "Login: Successfull connection"
},
new EventInfo()
{
Category = "LOGIN", Label = "Login: Incorrect password"
},
new EventInfo()
{
Category = "LOGIN", Label = "Switch Company Context"
},
new EventInfo()
{
Category = "LOGIN", Label = "Disconnect"
},
new EventInfo()
{
Category = "ADMIN", Label = "Create Domain"
},
new EventInfo()
{
Category = "ADMIN", Label = "Delete Domain"
},
new EventInfo()
{
Category = "ADMIN", Label = "Create or Update Custom Dashboard"
},
new EventInfo()
{
Category = "ADMIN", Label = "Update Computed Indicator"
},
new EventInfo()
{
Category = "ADMIN", Label = "Create or Update Tag"
},
new EventInfo()
{
Category = "APPLICATION", Label = "Create Application"
},
new EventInfo()
{
Category = "APPLICATION", Label = "Update Application"
},
new EventInfo()
{
Category = "APPLICATION", Label = "Add Tag to application"
},
new EventInfo()
{
Category = "APPLICATION", Label = "Remove Tag from application"
},
new EventInfo()
{
Category = "APPLICATION", Label = "Delete Application"
},
new EventInfo()
{
Category = "CAMPAIGN", Label = "Create Campaign"
},
new EventInfo()
{
Category = "CAMPAIGN", Label = "Update Campaign"
},
new EventInfo()
{
Category = "RESULT", Label = "Upload Files"
},
new EventInfo()
{
Category = "RESULT", Label = "Remove Result Files"
},
new EventInfo()
{
Category = "RESULT", Label = "Create Result"
},
new EventInfo()
{
Category = "RESULT", Label = "Update Result"
},
new EventInfo()
{
Category = "RESULT", Label = "Update Survey Answers"
},
new EventInfo()
{
Category = "USER", Label = "Create User"
},
new EventInfo()
{
Category = "USER", Label = "Update User"
},
new EventInfo()
{
Category = "USER", Label = "Update User Password"
},
};
var prevCat = string.Empty;
for (var i = 0; i < events.Length; i++)
{
var eventInfo = events[i];
if (eventInfo.Category != prevCat)
{
var col = spreadSheet.GetColumnName(7 + i);
spreadSheet.SetCellValue(summary, col, 5, eventInfo.Category);
prevCat = eventInfo.Category;
}
}
spreadSheet.SetCellValue(summary, "B", 6, "Année");
spreadSheet.SetCellValue(summary, "C", 6, "Mois");
spreadSheet.SetCellValue(summary, "D", 6, "Début");
spreadSheet.SetCellValue(summary, "E", 6, "Fin");
spreadSheet.SetCellValue(summary, "F", 6, "TOTAL");
for (var i = 0; i < events.Length; i++)
{
var eventInfo = events[i];
var col = spreadSheet.GetColumnName(7 + i);
spreadSheet.SetCellValue(summary, col, 6, eventInfo.Label);
}
for (uint month = 1; month <= 12; month++)
{
var row = 6 + month;
spreadSheet.SetCellValue(summary, "B", row, year);
spreadSheet.SetCellValue(summary, "C", row, month);
spreadSheet.SetCellFormula(summary, "D", row, $"DATE(B{row},C{row},1)");
spreadSheet.SetCellFormula(summary, "E", row, $"DATE(B{row},C{row}+1,1)");
spreadSheet.SetCellFormula(summary, "F", row, $"SUM(G{row}:AC{row})");
for (var i = 0; i < events.Length; i++)
{
var eventInfo = events[i];
var col = spreadSheet.GetColumnName(7 + i);
spreadSheet.SetCellFormula(summary, col, row, $@"COUNTIFS({SheetNames.AuditTrail}!$B:$B,"">=""&$D{row},{SheetNames.AuditTrail}!$B:$B,""<""&$E{row},{SheetNames.AuditTrail}!$D:$D,{col}$6,{SheetNames.AuditTrail}!$C:$C,""<>""&$E$2,{SheetNames.AuditTrail}!$C:$C,""<>""&$E$3,{SheetNames.AuditTrail}!$C:$C,""<>""&$E$4)");
}
}
spreadSheet.SetCellFormula(summary, "F", 19, $"SUM(F7:F18)");
for (var i = 0; i < events.Length; i++)
{
var eventInfo = events[i];
var col = spreadSheet.GetColumnName(7 + i);
spreadSheet.SetCellFormula(summary, col, 19, $"SUM({col}7:{col}18)");
}
}
}