private List <ScoreRecord> CompareActualWithExpectedResults(Document actual, DocumentCheckRequest expected, ILogger log)
{
int documentPoints = 0;
var results = new List <ScoreRecord>();
log.LogTrace($"Checking accuracy of document {actual.FileName} header");
// There are 7 header fields to check. We will identify those that match.
Dictionary <string, bool> matches = new Dictionary <string, bool> {
{ "Account", false }, { "GrandTotal", false }, { "ShippingTotal", false }, { "NetTotal", false }, { "VatAmount", false }, { "PostCode", false }, { "TaxDate", false }
};
matches["Account"] = Compare <string>(actual.FileName, "Account", actual.Account, expected.Account, log);
matches["GrandTotal"] = Compare <decimal>(actual.FileName, "GrandTotal", actual.GrandTotal, (decimal)expected.GrandTotalValue, log);
matches["ShippingTotal"] = Compare <decimal>(actual.FileName, "ShippingTotal", actual.ShippingTotal, (decimal)expected.ShippingTotalValue, log);
matches["NetTotal"] = Compare <decimal>(actual.FileName, "NetTotal", actual.NetTotal, (decimal)expected.PreTaxTotalValue, log);
matches["VatAmount"] = Compare <decimal>(actual.FileName, "VatAmount", actual.VatAmount, (decimal)expected.TaxTotalValue, log);
matches["PostCode"] = Compare <string>(actual.FileName, "PostCode", actual.PostCode, expected.PostalCode, log);
matches["TaxDate"] = Compare <DateTime>(actual.FileName, "TaxDate", actual.TaxDate ?? DateTime.MinValue, expected.DocumentDate, log);
// A fully matched header is worth 20 points - so apportion that
int DOCUMENT_HEADER_POINTS = 20;
decimal numPossibleMatches = matches.Count();
decimal numMatches = matches.Where(m => m.Value == true).Count();
var successRate = numMatches / numPossibleMatches;
var points = (int)(DOCUMENT_HEADER_POINTS * successRate);
documentPoints = points;
string notes = $"Document {actual.FileName} Header matched {numMatches} of {numPossibleMatches} fields for a score of {points} / {DOCUMENT_HEADER_POINTS})";
log.LogInformation(notes);
log.LogTrace($"Checking accuracy of document {actual.DocumentNumber} lines");
matches = new Dictionary <string, bool> {
{ "ItemDescription", false }, { "UnitPrice", false }, { "Taxableindicator", false }, { "LineQuantity", false }, { "NetAmount", false }, { "DiscountPercent", false }
};
// 100 points for a fully matched document leaves 80 up for grabs: pro rata that over the expected lines
int DOCUMENT_LINES_POINTS = 80;
decimal DOCUMENT_LINE_POINTS = DOCUMENT_LINES_POINTS / expected.Lines.Count();
numPossibleMatches = matches.Count();
foreach (var expLine in expected.Lines.OrderBy(o => o.LineNumber))
{
var expLineNumber = expLine.LineNumber.PadLeft(2, '0');
log.LogTrace($"Checking Line {expLineNumber}");
DocumentLineItem actLine = null;
try
{
actLine = actual.LineItems.Where(l => l.DocumentLineNumber == expLineNumber).Single();
}
catch (Exception)
{
log.LogTrace($"{actual.FileName} Actual line matching {expLineNumber} does not exist - you may want to retrain your model?");
}
if (actLine != null)
{
matches["ItemDescription"] = Compare <string>(actual.FileName, "ItemDescription" + expLineNumber, actLine.ItemDescription, $"{expLine.ProductCode}{expLine.ProductDescription}".Trim(), log);
matches["UnitPrice"] = Compare <decimal>(actual.FileName, "UnitPrice" + expLineNumber, actLine.UnitPrice, (decimal)expLine.Price, log);
bool actTaxIndicator = false;
if (!String.IsNullOrEmpty(actLine.Taxableindicator))
{
actTaxIndicator = true;
}
matches["Taxableindicator"] = Compare <bool>(actual.FileName, "Taxableindicator" + expLineNumber, actTaxIndicator, expLine.Taxable, log);
double actLineQuantity = 0;
if (Double.TryParse(actLine.LineQuantity, out double res))
{
actLineQuantity = res;
}
matches["LineQuantity"] = Compare <double>(actual.FileName, "LineQuantity" + expLineNumber, actLineQuantity, expLine.Quantity, log);
if (!matches["LineQuantity"])
{
matches["LineQuantity"] = Compare <double>(actual.FileName, "CalculatedLineQuantity" + expLineNumber, (double)actLine.CalculatedLineQuantity, expLine.Quantity, log);
}
matches["NetAmount"] = Compare <decimal>(actual.FileName, "NetAmount" + expLineNumber, actLine.NetAmount, (decimal)expLine.DiscountedGoodsValue, log);
matches["DiscountPercent"] = Compare <decimal>(actual.FileName, "DiscountPercent" + expLineNumber, actLine.DiscountPercent, (decimal)expLine.Discount, log);
numMatches = matches.Where(m => m.Value == true).Count();
successRate = (numMatches / numPossibleMatches);
points = (int)(DOCUMENT_LINE_POINTS * successRate);
documentPoints += points;
notes = $"Document {actual.FileName} line {expLineNumber} matched {numMatches} of {numPossibleMatches} fields for a score of {points} / {DOCUMENT_LINE_POINTS})";
log.LogInformation(notes);
}
else
{
log.LogTrace($"{actual.FileName} Actual line matching {expLineNumber} does not exist - you may want to retrain your model?");
}
}
notes = $"Document {actual.FileName} overall scored {documentPoints} / 100 points)";
if (documentPoints < 50)
{
log.LogError(notes);
}
else
{
log.LogInformation(notes);
}
results.Add(new ScoreRecord {
Type = $"Accuracy", Notes = notes, Score = documentPoints
});
return(results);
}
private async Task <List <ScoreRecord> > CheckIndividualDocuments(ILogger log)
{
var results = new List <ScoreRecord>();
log.LogTrace($"Checking accuracy of document recognition");
log.LogTrace($"Reading expected results from SQL Database");
var checks = new List <DocumentCheckRequest>();
using (SqlConnection connection = new SqlConnection(scoresSQLConnectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
try
{
string previousDocumentFormat = "";
string previousDocumentNumber = "";
command.CommandText = "SELECT * FROM [dbo].[GeneratedDocuments] order by DocumentFormat, DocumentNumber, LineNumber";
SqlDataReader reader = command.ExecuteReader();
try
{
DocumentCheckRequest checkRequest = null;
while (reader.Read())
{
bool newDocument = false;
string currentDocumentFormat = (string)reader["DocumentFormat"];
string currentDocumentNumber = (string)reader["DocumentNumber"];
if (currentDocumentFormat != previousDocumentFormat)
{
newDocument = true;
previousDocumentFormat = currentDocumentFormat;
}
if (currentDocumentNumber != previousDocumentNumber)
{
newDocument = true;
previousDocumentNumber = currentDocumentNumber;
}
if (newDocument)
{
if (checkRequest != null)
{
checks.Add(checkRequest);
}
checkRequest = new DocumentCheckRequest
{
Account = (string)reader["Account"],
DocumentNumber = (string)reader["DocumentNumber"],
DocumentDate = (DateTime)reader["DocumentDate"],
PostalCode = (string)reader["PostalCode"],
GrandTotalValue = Convert.ToDouble(reader["GrandTotalValue"]),
PreTaxTotalValue = Convert.ToDouble(reader["PreTaxTotalValue"]),
ShippingTotalValue = Convert.ToDouble(reader["ShippingTotalValue"]),
TaxTotalValue = Convert.ToDouble(reader["TaxTotalValue"]),
FileName = (string)reader["FileName"],
DocumentFormat = (string)reader["DocumentFormat"],
};
}
var line = new DocumentLineCheckRequest
{
Discount = Convert.ToDouble(reader["Discount"]),
LineNumber = (string)reader["LineNumber"],
ProductCode = (string)reader["Isbn"],
ProductDescription = (string)reader["Title"],
Price = Convert.ToDouble(reader["Price"]),
Quantity = Convert.ToDouble(reader["Quantity"]),
Taxable = (bool)reader["Taxable"],
DiscountedGoodsValue = Convert.ToDouble(reader["DiscountedGoodsValue"]),
DiscountValue = Convert.ToDouble(reader["DiscountValue"]),
GoodsValue = Convert.ToDouble(reader["GoodsValue"]),
TaxableValue = Convert.ToDouble(reader["TaxableValue"]),
};
checkRequest.Lines.Add(line);
}
if (checkRequest != null)
{
checks.Add(checkRequest);
}
}
catch (Exception e)
{
log.LogError(e.Message);
}
finally
{
reader.Close();
}
}
catch (Exception e)
{
log.LogError($"Exception prevented reading expected results from SQL database {connection.Database} Message is {e.Message}");
throw e;
}
log.LogInformation($"Expected Results read from SQL database {connection.Database}");
}
log.LogInformation($"We will be checking the actual vs expected results for {checks.Count} documents");
foreach (var check in checks)
{
string fileName = $"{check.DocumentFormat}-{check.FileName}";
log.LogDebug($"Loading document {fileName} from processing database");
Document document = null;
try
{
log.LogTrace($"Checking {fileName}");
document = HorusSql.LoadDocument(fileName, log);
}
catch (Exception)
{
log.LogWarning($"Unable to load document {fileName} from processing database.");
continue;
}
if (document == null)
{
log.LogTrace($"Document {check.DocumentNumber} has not been processed sucessfully and will be skipped");
continue;
}
var checkResults = CompareActualWithExpectedResults(document, check, log);
results.AddRange(checkResults);
}
return(results);
}