public async Task <int> InsertMilkoScanFileData(MilkoscanFile milkoscanFile)
{
return(await WithConnection(async conn =>
{
var p = new DynamicParameters();
p.Add("szFileName", milkoscanFile.FileName);
p.Add("szFileBody", String.Empty);
p.Add("tFileCreated", milkoscanFile.FileCreated);
p.Add("tFileModified", milkoscanFile.FileModified);
p.Add("bHasWrongStructure", milkoscanFile.HasWrongStructure);
p.Add("bIsDuplicate", false);
p.Add("nKey", dbType: DbType.Int32, direction: ParameterDirection.Output);
await conn.QueryAsync <int>("sp_MS_MilkoScanDataInsert",
p,
commandType: CommandType.StoredProcedure,
commandTimeout: conn.ConnectionTimeout
);
return p.Get <int>("nKey");
}));
}
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
string filePath = _serviceSettings.FilePath;
string archivePath = _serviceSettings.ArchivePath;
string duplicatePath = _serviceSettings.DuplicatePath;
string trashPath = _serviceSettings.TrashPath;
string logPath = _serviceSettings.LogPath;
#if DEBUG
_logger.LogInformation("Service settings:"
+ $"\r\n filePath - {filePath}"
+ $"\r\n archivePath - {archivePath}"
+ $"\r\n duplicatePath - {duplicatePath}"
+ $"\r\n trashPath - {trashPath}"
+ $"\r\n logPath - {logPath}"
, filePath, archivePath, logPath);
#endif
_workerLogger.WriteLog("Service"
, $"started by {Environment.UserName} with interval - {_serviceSettings.FileReadingInterval}ms"
, 0);
try
{
while (!stoppingToken.IsCancellationRequested)
{
int milkoScanDataId = 0;
try
{
// Read CSV file
MilkoscanFile milkoscanFile = _fileReader.ReadFile();
if (milkoscanFile != null)
{
#region Log file header
_workerLogger.WriteLog($"({milkoscanFile.FileName}) file observed"
, $"start reading {DateTime.Now.ToString(new CultureInfo("es-ES"))}"
, 1);
_workerLogger.WriteLog($"File path"
, $"{_serviceSettings.FilePath}"
, 3);
_workerLogger.WriteLog($"File created"
, $"{milkoscanFile.FileCreated}"
, 3);
_workerLogger.WriteLog($"File modified"
, $"{milkoscanFile.FileModified}"
, 3);
#endregion
// Insert row to MilkoscanData
try
{
milkoScanDataId = await _repository.InsertMilkoScanFileData(milkoscanFile);
_workerLogger.WriteLog($"New row added to table tbl_MS_MilkoScanData"
, $"id = {milkoScanDataId}"
, 2);
}
catch (Exception e)
{
_workerLogger.WriteLog($"ERROR - Insert to DB"
, $"{e.Message}"
, 0);
_logger.LogError("Error Insert data to DB");
_logger.LogError(e.Message);
}
// Wrong structure
if (milkoscanFile.HasWrongStructure ?? true)
{
_workerLogger.WriteLog($"File has wrong structure"
, $"will be moved to trash folder"
, 2);
var result = _fileHandler.Trash(milkoscanFile.FilePath);
if (result != null)
{
_workerLogger.WriteLog($"File moved to trash folder"
, $"path = {result}"
, 2);
}
else
{
_workerLogger.WriteLog($"ERROR File moving failed"
, $"path: {_serviceSettings.TrashPath}"
, 0);
}
}
else
{
MilkoscanDataHandler milkoscanDataHandler = new MilkoscanDataHandler();
List <MilkoscanSample> samples =
milkoscanDataHandler.HandleData(milkoscanFile.MilkoScanFileData);
if (samples.Any())
{
// get last sample date time
var relevantSampleDate = await _repository.GetMilkoscanRelevantSampleDate();
var relevantSamples = samples.Where(
sample => sample.AnalysisTime > relevantSampleDate
).ToList();
var lastSamplesNumber = await _repository.GetMilkoscanLastSamples();
var filteredSamples = relevantSamples.Where(sample =>
!lastSamplesNumber.Contains(sample.Parameters.SampleNumber)
).ToList();
_workerLogger.WriteLog($"New samples"
, $"Count = {filteredSamples.Count()}"
, 2);
if (filteredSamples.Any())
{
foreach (var sample in filteredSamples)
{
// Write Milkoscan sample to DB
try
{
var milkoScanDataSampleId =
await _repository.InsertMilkoScanDataSample(milkoScanDataId,
sample);
_workerLogger.WriteLog(
$"New row added to table tbl_MS_MilkoScanDataSample"
, $"id = {milkoScanDataSampleId}"
, 2);
_workerLogger.WriteLog($"Sample"
, $"data = {sample.SampleRow}"
, 3);
}
catch (Exception e)
{
_workerLogger.WriteLog($"ERROR - Insert to DB"
, $"{e.Message}"
, 0);
_logger.LogError("Error Insert data to DB");
}
}
// Archive file
var result = _fileHandler.Archive(milkoscanFile.FilePath);
if (result != null)
{
_workerLogger.WriteLog($"File archived"
, $"path = {result}"
, 2);
}
else
{
_workerLogger.WriteLog($"ERROR File archiving failed"
, $"archive path: {_serviceSettings.ArchivePath}"
, 0);
}
}
else
{
// Duplicate file
var affectedRows =
await _repository.UpdateMilkoScanFileData(milkoScanDataId, true);
var result = _fileHandler.Duplicate(milkoscanFile.FilePath);
if (result != null)
{
_workerLogger.WriteLog($"File duplicated"
, $"path = {result}"
, 2);
}
else
{
_workerLogger.WriteLog($"ERROR File duplicating failed"
, $"duplicate path: {_serviceSettings.ArchivePath}"
, 0);
}
}
}
else
{
_workerLogger.WriteLog($"No samples in file"
, $"Count = 0"
, 2);
// Duplicate file
var affectedRows = await _repository.UpdateMilkoScanFileData(milkoScanDataId, true);
var result = _fileHandler.Duplicate(milkoscanFile.FilePath);
if (result != null)
{
_workerLogger.WriteLog($"File duplicated"
, $"path = {result}"
, 2);
}
else
{
_workerLogger.WriteLog($"ERROR File archiving failed"
, $"archive path: {_serviceSettings.ArchivePath}"
, 0);
}
}
}
}
}
catch (Exception e)
{
_logger.LogWarning(e.Message);
}
await Task.Delay(_serviceSettings.FileReadingInterval, stoppingToken);
}
}
catch (Exception e)
{
_logger.LogCritical(e.Message);
}
}
public MilkoscanFile ReadFile()
{
try
{
string filePath = _serviceSettings.FilePath;
if (!File.Exists(filePath))
{
if (Directory.Exists(filePath))
{
var files = Directory.GetFiles(filePath);
foreach (var file in files)
{
if (Path.GetExtension(file).ToLower() == ".csv")
{
filePath = file;
break;
}
}
}
}
if (!File.Exists(filePath))
{
return(null);
}
_reader = new StreamReader(filePath);
MilkoscanFile milkoscanFile = new MilkoscanFile();
MilkoscanFileData milkoscanFileData = new MilkoscanFileData();
int linePointer = 0;
while (!_reader.EndOfStream)
{
linePointer++;
var line = _reader.ReadLine();
var values = line?.Split(";");
if (linePointer == 1)
{
milkoscanFileData.Key = values;
}
else
{
milkoscanFileData.Samples.Add(values);
}
}
milkoscanFile.FileName = Path.GetFileName(filePath);
milkoscanFile.FileCreated = File.GetCreationTime(filePath);
milkoscanFile.FileModified = File.GetLastWriteTime(filePath);
milkoscanFile.FilePath = filePath;
milkoscanFile.ReadingTime = DateTime.Now;
milkoscanFile.HasWrongStructure = milkoscanFileData?.Key?.Length != 30;
milkoscanFile.MilkoScanFileData = milkoscanFileData;
_reader.Close();
return(milkoscanFile);
}
catch (Exception)
{
return(null);
}
}
