static void Main(string[] args) { AppSettings.Initialize(); Console.WriteLine($"Excel path = {AppSettings.SurveyResultFilePath}"); Console.WriteLine($"Template path = {AppSettings.ReportTemplatePath}"); Console.WriteLine($"Scoring config = {AppSettings.ScoringRulesConfigPath}"); Console.WriteLine($"Output directory = {AppSettings.OutputDirectory}"); //Verify.TestSerialization(); Console.WriteLine("Press `Enter` to continue..."); Console.ReadLine(); string scoringXml = File.ReadAllText(AppSettings.ScoringRulesConfigPath); ScoringConfig config = XmlHelper.DeserializeData <ScoringConfig>(scoringXml); var outputDir = FileHelper.CreateDirectoryWithTimestamp(AppSettings.OutputDirectory, null); //here is the actual application logic var scores = new SurveyExcelReader().ReadFromExcelFile(AppSettings.SurveyResultFilePath, config); var excelSummary = SurveySummaryGenerator.CreateExcel(scores); FileHelper.CreateFileWithTimestampAndWrite(excelSummary, outputDir, AppSettings.OutputFilePrefix, "xlsx"); //var html = SurveyReportGenerator.Create(scores[0], AppSettings.ReportTemplatePath); SurveyReportGenerator.Process(scores, AppSettings.ReportTemplatePath, outputDir); Console.WriteLine("Scores calculated & reports generated. See output directory."); Console.WriteLine("Press `Enter` to exit..."); Console.ReadLine(); }
//TODO: overall RED-GREEN rules are hard coded as 70% and 85% - should be configurable internal static (decimal RedScore, decimal GreenScore) GetAggregateRules(ScoringConfig scoringConfig) { var maxPossibleScore = scoringConfig.Questions .Aggregate(0.0m, (current, next) => current + (next.SingleAnswerOnly ? next.Options.Max(op => op.Score) : next.Options.Sum(op => op.Score))); var redScore = maxPossibleScore * (scoringConfig.AggregateYellowPercentage / 100m); var greenScore = maxPossibleScore * (scoringConfig.AggregateGreenPercentage / 100m); return(redScore, greenScore); }
private static void PopulateMetaColumnNumbers(ScoringConfig ruleConfig, ExcelWorksheet worksheet, int totalColumns) { for (int col = 1; col <= totalColumns; col++) { var colHeader = worksheet.GetCellValue(1, col); var metaToUpdate = ruleConfig.Metadata .FirstOrDefault(m => m.Query.Trim().ToLower() == colHeader.Trim().ToLower()); if (metaToUpdate != null) { metaToUpdate.ColumnNumber = col; } } }
public List <ScoreCard> ReadFromExcelFile(string excelFilePath, ScoringConfig scoringConfig) { CheckFileExists(excelFilePath); var allScores = new List <ScoreCard>(); var excel = new FileInfo(excelFilePath); using (ExcelPackage xlPackage = new ExcelPackage(excel)) { CheckExcelValid(xlPackage); var worksheet = xlPackage.Workbook.Worksheets.First(); var totalRows = worksheet.Dimension.End.Row; var totalColumns = worksheet.Dimension.End.Column; PopulateMetaColumnNumbers(scoringConfig, worksheet, totalColumns); PopulateColumnNumbers(scoringConfig, worksheet, totalColumns); var aggregateRules = ScoreCalculator.GetAggregateRules(scoringConfig); for (int rowNum = 2; rowNum <= totalRows; rowNum++) { var scorecard = GetScorecardWithMetadata(worksheet, scoringConfig, rowNum); foreach (var rule in scoringConfig.Questions) { var response = worksheet.GetCellValue(rowNum, rule.ColumnNumber.Value); var result = ScoreCalculator.GetScore(rule, response); scorecard.ScoreItems.Add(new CategoryScore { Query = rule.Query, ReportTag = rule.ReportTag, Response = response, Score = result.Score, StateColor = result.StateColor }); } var aggregateResult = ScoreCalculator.GetAggregate(scorecard, aggregateRules.RedScore, aggregateRules.GreenScore); scorecard.Aggregate = aggregateResult.Sum; scorecard.ResultColor = aggregateResult.Color; allScores.Add(scorecard); } } return(allScores); }
private static ScoreCard GetScorecardWithMetadata(ExcelWorksheet worksheet, ScoringConfig scoringConfig, int rowNum) { var scorecard = new ScoreCard() { ScoreItems = new List <CategoryScore>(), Metadata = new List <MetaResponse>() }; foreach (var rule in scoringConfig.Metadata) { scorecard.Metadata.Add(new MetaResponse { Query = rule.Query, ReportTag = rule.ReportTag, Response = worksheet.GetCellValue(rowNum, rule.ColumnNumber.Value) }); } return(scorecard); }
internal static void TestSerialization() { //test var test = new ScoringConfig { Questions = new Question[] { new Question { Options = new Option[] { new Option { Score = 10, Value = "First option" }, new Option { Score = 20, Value = "Second option" } }, Query = "Select an option", ReportTag = "opt1", ScoringRule = new ScoringRule { GreenScore = 10, RedScore = null }, SingleAnswerOnly = true }, new Question { Options = new Option[] { new Option { Score = 100, Value = "Good option" }, new Option { Score = 20, Value = "Bad option" } }, Query = "Select another option", ReportTag = "opt2", ScoringRule = new ScoringRule { GreenScore = 100, RedScore = 20 }, SingleAnswerOnly = true } }, Metadata = new Metadata[] { new Metadata { Query = "Project name", ReportTag = "project" }, new Metadata { Query = "Project BU", ReportTag = "bu" } } }; var xml = XmlHelper.SerializeData(test); }