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);
}
