118 lines
4.3 KiB
C#
118 lines
4.3 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading;
|
|
using System.Threading.Tasks;
|
|
using Microsoft.Data.SqlClient;
|
|
using Microsoft.Extensions.Configuration;
|
|
using Microsoft.Extensions.Logging;
|
|
using PartSource.Automation.Models.Configuration;
|
|
using PartSource.Automation.Models.Ftp;
|
|
using PartSource.Automation.Services;
|
|
using Ratermania.Automation.Interfaces;
|
|
|
|
namespace PartSource.Automation.Jobs
|
|
{
|
|
public class PartialInventoryUpdate : IAutomationJob
|
|
{
|
|
private readonly FtpService _ftpService;
|
|
private readonly ILogger<PartialInventoryUpdate> _logger;
|
|
private readonly string _connectionString;
|
|
|
|
public PartialInventoryUpdate(IConfiguration configuration, ILogger<PartialInventoryUpdate> logger)
|
|
{
|
|
FtpConfiguration ftpConfiguration = configuration.GetSection("FtpServers:AutomationConfiguration").Get<FtpConfiguration>();
|
|
_ftpService = new FtpService(ftpConfiguration);
|
|
|
|
_connectionString = _connectionString = configuration.GetConnectionString("PartSourceDatabase");
|
|
|
|
_logger = logger;
|
|
}
|
|
|
|
[System.Diagnostics.CodeAnalysis.SuppressMessage("Security", "CA2100:Review SQL queries for security vulnerabilities")]
|
|
public async Task Run(CancellationToken token, params string[] arguments)
|
|
{
|
|
FtpFileInfo lastUploadedFile = _ftpService.ListFilesExtended()
|
|
.Where(f => f.FileType == FtpFileType.File && f.Filename.IndexOf("Availability Partial") > -1)
|
|
.OrderByDescending(f => f.Modified)
|
|
.FirstOrDefault();
|
|
|
|
if (lastUploadedFile == null)
|
|
{
|
|
_logger.LogInformation($"No partial inventory file available.");
|
|
return;
|
|
}
|
|
|
|
_logger.LogInformation("Processing {filename}", lastUploadedFile.Filename);
|
|
|
|
string file = _ftpService.Download($"{lastUploadedFile.Filename}");
|
|
|
|
using SqlConnection connection = new SqlConnection(_connectionString);
|
|
connection.Open();
|
|
|
|
using StreamReader reader = new StreamReader(file);
|
|
string line = reader.ReadLine(); // Burn the header row
|
|
|
|
IDictionary<string, object> parameters = new Dictionary<string, object>();
|
|
string command = string.Empty;
|
|
int i = 0;
|
|
|
|
while (reader.Peek() > 0)
|
|
{
|
|
line = reader.ReadLine();
|
|
|
|
string[] columns = line.Split("|");
|
|
for (int j = 0; j < columns.Length; j++)
|
|
{
|
|
columns[j] = columns[j].Replace("\"", string.Empty);
|
|
}
|
|
|
|
if (int.TryParse(columns[0], out int store)
|
|
&& int.TryParse(columns[1], out int sku)
|
|
&& int.TryParse(columns[2], out int quantity)
|
|
&& DateTime.TryParse(columns[3], out DateTime updated))
|
|
{
|
|
command += $"UPDATE PartAvailability SET QTY = @qty_{i}, Updated = @updated_{i} WHERE SKU = @sku_{i} AND Store = @store_{i};";
|
|
|
|
parameters.Add($"qty_{i}", quantity);
|
|
parameters.Add($"store_{i}", store);
|
|
parameters.Add($"sku_{i}", sku);
|
|
parameters.Add($"updated_{i}", updated);
|
|
|
|
i++;
|
|
}
|
|
|
|
if (i == 250)
|
|
{
|
|
using SqlCommand nested = new SqlCommand(command, connection);
|
|
foreach (KeyValuePair<string, object> parameter in parameters)
|
|
{
|
|
nested.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
|
|
}
|
|
|
|
await nested.ExecuteNonQueryAsync(token);
|
|
|
|
parameters.Clear();
|
|
command = string.Empty;
|
|
i = 0;
|
|
}
|
|
}
|
|
|
|
using SqlCommand sqlCommand = new SqlCommand(command, connection);
|
|
foreach (KeyValuePair<string, object> parameter in parameters)
|
|
{
|
|
sqlCommand.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
|
|
}
|
|
|
|
await sqlCommand.ExecuteNonQueryAsync(token);
|
|
|
|
_ftpService.Delete(lastUploadedFile.Filename);
|
|
|
|
return;
|
|
}
|
|
}
|
|
}
|