/// <summary>
/// Adds to the log table
/// </summary>
/// <param name="source">The source.</param>
/// <param name="severity">The severity.</param>
/// <param name="text">The description.</param>
private void Add(string source, int severity, string text)
{
DataRow row = LogTable.NewRow();
SetRowValue(row, MandatoryLogField.Source, source, source);
SetRowValue(row, MandatoryLogField.Severity, severity, source);
SetRowValue(row, MandatoryLogField.Description, text, source);
var windowsIdentity = WindowsIdentity.GetCurrent();
SetRowValue(row, MandatoryLogField.User, windowsIdentity.Name, source);
SetRowValue(row, MandatoryLogField.LogDateTime, DateTime.UtcNow, source);
LogTable.Rows.Add(row);
}
/// <summary>
/// Reads the given CSV file.
/// </summary>
/// <param name="filePath">The CSV file path.</param>
/// <returns>A <see cref="LogTable"/>.</returns>
private static LogTable ReadCsvFile(string filePath)
{
var returnTable = new LogTable();
returnTable.BeginLoadData();
// AR csv report formatting
const char Delimiter = '|';
try
{
using (var readFile = new StreamReader(filePath))
{
// Skip headers
var line = readFile.ReadLine();
// Get lines
line = readFile.ReadLine();
while (line != null)
{
var newLine = line.Split(Delimiter);
var newRow = returnTable.NewRow();
// Parse account name, date, amount
newRow.AccountNum = newLine[0].Substring(0, 5).Replace("-", string.Empty).Trim();
newRow.Date = DateTime.Parse(newLine[1]);
newRow.Amount = Convert.ToDecimal(newLine[2]);
returnTable.Rows.Add(newRow);
line = readFile.ReadLine();
}
}
}
catch (Exception error)
{
Tools.DebugLog($"Error reading file {filePath}: {error.Message}");
}
returnTable.EndLoadData();
return(returnTable);
}
/// <summary>
/// Reads all WSRs in the given directory.
/// </summary>
/// <param name="folderPath">The folder path.</param>
/// <param name="progress">The progress reporter.</param>
/// <returns>A <see cref="DataTable"/>.</returns>
public static LogTable ReadWsrs(string folderPath, IProgress <int> progress = null)
{
// Set up import table
var returnTable = new LogTable();
var wsrs = Directory.GetFiles(folderPath).Where(f => Path.GetExtension(f) == Values.WsrExtension).ToList();
var excelApp = new Excel.Application
{
DisplayAlerts = false,
ScreenUpdating = false,
Visible = true
};
var workbooks = excelApp.Workbooks;
// Initialize progress reporting
var filesRead = 0;
var totalFiles = wsrs.Count;
progress?.Report(0);
foreach (var wsr in wsrs)
{
var thisWorkBook = workbooks.Open(wsr, ReadOnly: true);
try
{
// Get store name
var storeNum = Path.GetFileName(wsr)?.Split('-')[1].Trim();
var sheet = thisWorkBook.Sheets.OfType <Excel.Worksheet>().First().UsedRange;
const int DayCol = 4;
const int RowDate = 9;
const int RowAr = 45;
// Look for imbalances on each day
for (var d = 0; d < 7; d++)
{
// Check date
var salesDate = DateTime.Parse(sheet.Cells[RowDate, DayCol + (2 * d)].Value2.ToString());
if (salesDate < DateTime.Now.Date)
{
// Check for imbalance
var am = Convert.ToDecimal(sheet.Cells[RowAr, DayCol + (2 * d)].Value2);
var pm = Convert.ToDecimal(sheet.Cells[RowAr, DayCol + (2 * d) + 1].Value2);
if (am + pm != 0)
{
// Add row
var newRow = returnTable.NewRow();
newRow.Date = salesDate;
newRow.AccountNum = storeNum;
newRow.Amount = am + pm;
returnTable.Rows.Add(newRow);
}
}
}
// Report progress
filesRead++;
progress?.Report(100 * filesRead / totalFiles);
}
catch (Exception error)
{
Tools.DebugLog(error.Message);
}
finally
{
thisWorkBook.Close();
workbooks.Close();
}
}
excelApp.Quit();
return(returnTable);
}
/// <summary>
/// Reads the given Excel file.
/// </summary>
/// <param name="filePath">The Excel file path.</param>
/// <param name="progress">The progress reporter.</param>
/// <returns>A <see cref="LogTable"/>.</returns>
private static LogTable ReadXlFile(string filePath, IProgress <int> progress = null)
{
var returnTable = new LogTable();
var excelApp = new Excel.Application
{
DisplayAlerts = false,
ScreenUpdating = false,
Visible = true
};
var workbooks = excelApp.Workbooks;
returnTable.BeginLoadData();
var thisWorkBook = workbooks.Open(filePath, ReadOnly: true);
// Initialize progress reporting
var rowsRead = 0;
var totalRows = thisWorkBook.Sheets.OfType <Excel.Worksheet>().Sum(w => w.UsedRange.Rows.Count - 1); // Minus header row
progress?.Report(0);
try
{
foreach (var thisSheet in thisWorkBook.Sheets.OfType <Excel.Worksheet>())
{
var thisRange = thisSheet.UsedRange;
var thisDate = Convert.ToDateTime(thisSheet.Name, Values.AppCulture);
// Get column headers
var colDict = new Dictionary <int, int>();
for (var col = 1; col <= thisRange.Columns.Count; col++)
{
var headerName = Convert.ToString(thisRange.Cells[1, col].Value2);
var colName = returnTable.Columns.OfType <DataColumn>()
.First(c => c.Caption == headerName || c.ColumnName == headerName).ColumnName;
var colIndex = returnTable.Columns.IndexOf(colName);
if (colIndex > 0)
{
colDict.Add(col, colIndex);
continue;
}
Tools.DebugLog($"Error importing column '{colName}': column not found in target table.");
}
// Read each row below the column headers
for (var row = 2; row <= thisRange.Rows.Count; row++)
{
var newRow = returnTable.NewRow();
newRow.Date = thisDate;
for (var col = 1; col <= thisRange.Columns.Count; col++)
{
if (colDict.ContainsKey(col))
{
// Safe casts in case cell values are null
var cellValue = Convert.ToString(thisRange.Cells[row, col].Value2) ?? string.Empty;
if (returnTable.Columns[colDict[col]].DataType == typeof(bool))
{
newRow[colDict[col]] = cellValue.ToUpper() == Values.BoolExportString;
}
else
{
newRow[colDict[col]] = cellValue;
}
}
}
returnTable.Add(newRow);
// Report progress
rowsRead++;
progress?.Report(100 * rowsRead / totalRows);
}
}
}
catch (Exception error)
{
Tools.DebugLog(error.Message);
}
finally
{
thisWorkBook.Close();
workbooks.Close();
}
excelApp.Quit();
returnTable.EndLoadData();
return(returnTable);
}
