using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CSVLoadAndStatisticsTest
{
public partial class Form1 : Form
{
// TODO: Change this to your CSV root folder.
// Expected structure: root\YYYY\MMDD\hhmmssff_Audit.csv
private const string RootFolder = @"D:\csvroot";
private CancellationTokenSource _cts;
public Form1()
{
InitializeComponent();
}
// --------------------------
// UI Events
// --------------------------
private async void btnStart_Click(object sender, EventArgs e)
{
btnStart.Enabled = false;
btnCancel.Enabled = true;
if (_cts != null)
{
_cts.Cancel();
_cts.Dispose();
}
_cts = new CancellationTokenSource();
DateTime from = dtpFrom.Value;
DateTime to = dtpTo.Value;
try
{
// Run aggregation on a background thread, return only results.
AggregateResult result = await Task.Run(() => ProcessRange(from, to, _cts.Token));
// Apply results on the UI thread in one shot.
ApplyResultToUi(result);
}
catch (OperationCanceledException)
{
lblStatus.Text = "Canceled.";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error");
}
finally
{
btnStart.Enabled = true;
btnCancel.Enabled = false;
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
if (_cts != null) _cts.Cancel();
}
private void btnExportDetailsCsv_Click(object sender, EventArgs e)
{
// Export detail rows currently displayed (last aggregation result).
// This sample stores the last result in Tag. You can refactor as needed.
AggregateResult r = this.Tag as AggregateResult;
if (r == null || r.Details == null || r.Details.Count == 0)
{
MessageBox.Show("No detail data to export.", "Info");
return;
}
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*";
sfd.FileName = "FixesDetails.csv";
if (sfd.ShowDialog(this) != DialogResult.OK) return;
try
{
ExportDetailsToCsv(r.Details, sfd.FileName);
MessageBox.Show("Export completed.", "Info");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error");
}
}
}
// --------------------------
// Core Aggregation (No UI access)
// --------------------------
private AggregateResult ProcessRange(DateTime from, DateTime to, CancellationToken ct)
{
AggregateResult result = new AggregateResult();
foreach (string path in EnumerateTargetAuditCsvFiles(from, to, ct))
{
ct.ThrowIfCancellationRequested();
result.FilesRead++;
DateTime fileTime;
if (!TryParseTimeFromAuditFilePath(path, out fileTime))
{
// Skip if file time cannot be determined from path.
continue;
}
bool firstLine = true;
int lineNo = 0;
foreach (string line in File.ReadLines(path))
{
ct.ThrowIfCancellationRequested();
lineNo++;
if (string.IsNullOrWhiteSpace(line))
continue;
// Spec: 1st line is a datetime text. We skip it for row processing.
if (firstLine)
{
firstLine = false;
continue;
}
result.RowsRead++;
// NOTE: If columns may include commas inside quotes, use a real CSV parser.
string[] cols = line.Split(',');
if (cols.Length < 2)
continue;
// Expected:
// cols[0] = WorkNo (int)
// cols[1] = LP or Fix
// cols[2] = Fix reason (only for NG) [optional but recommended]
// cols[3..] = measurement values (optional)
int workNo = 0;
int.TryParse(cols[0].Trim(), out workNo);
string judge = cols[1].Trim();
if (judge.Equals("LP", StringComparison.OrdinalIgnoreCase))
{
result.OkCount++;
// Collect OK measurement statistics (all numeric columns from index 3).
AddMeasurementsToStats(cols, startIndex: 3, statsMap: result.OkStatsByIndex);
}
else if (judge.Equals("Fix", StringComparison.OrdinalIgnoreCase))
{
result.FixesCount++;
string reason = (cols.Length >= 3 ? cols[2].Trim() : "Unknown");
if (string.IsNullOrWhiteSpace(reason)) reason = "Unknown";
int cnt;
if (!result.FixesByReason.TryGetValue(reason, out cnt)) cnt = 0;
result.FixesByReason[reason] = cnt + 1;
// Collect Fix measurement statistics too (optional).
AddMeasurementsToStats(cols, startIndex: 3, statsMap: result.FixesStatsByIndex);
// Store detail row for later drill-down / export.
FixRecord rec = new FixRecord();
rec.Time = fileTime;
rec.WorkNo = workNo;
rec.Judge = "Fix";
rec.Reason = reason;
rec.FilePath = path;
rec.LineNo = lineNo;
rec.RawColumns = cols;
result.Details.Add(rec);
}
else
{
// Unknown judge string => skip or treat as needed.
}
}
}
return result;
}
private static void AddMeasurementsToStats(string[] cols, int startIndex, Dictionary<int, StatisticsAccumulator> statsMap)
{
for (int i = startIndex; i < cols.Length; i++)
{
double v;
string s = cols[i].Trim();
// If your numeric format is stable (e.g., dot decimal), you can force InvariantCulture:
// double.TryParse(s, NumberStyles.Float, CultureInfo.InvariantCulture, out v)
if (!double.TryParse(s, NumberStyles.Float, CultureInfo.InvariantCulture, out v) &&
!double.TryParse(s, NumberStyles.Float, CultureInfo.CurrentCulture, out v))
{
continue;
}
StatisticsAccumulator acc;
if (!statsMap.TryGetValue(i, out acc))
{
acc = new StatisticsAccumulator();
statsMap[i] = acc;
}
acc.Add(v);
}
}
// --------------------------
// File Enumeration & Time Parsing (Audit naming)
// --------------------------
private IEnumerable<string> EnumerateTargetAuditCsvFiles(DateTime from, DateTime to, CancellationToken ct)
{
DateTime dayFrom = from.Date;
DateTime dayTo = to.Date;
foreach (DateTime day in EachDay(dayFrom, dayTo))
{
ct.ThrowIfCancellationRequested();
string dayFolder = BuildDayFolder(day);
if (!Directory.Exists(dayFolder))
continue;
// Rename: Inspection -> Audit
foreach (string file in Directory.EnumerateFiles(dayFolder, "*_Audit.csv", SearchOption.TopDirectoryOnly))
{
ct.ThrowIfCancellationRequested();
DateTime fileTime;
if (!TryParseTimeFromAuditFilePath(file, out fileTime))
continue;
if (fileTime >= from && fileTime <= to)
yield return file;
}
}
}
private static IEnumerable<DateTime> EachDay(DateTime from, DateTime to)
{
for (DateTime d = from.Date; d <= to.Date; d = d.AddDays(1))
yield return d;
}
private static string BuildDayFolder(DateTime day)
{
// root\YYYY\MMDD
return Path.Combine(
RootFolder,
day.Year.ToString("0000"),
day.ToString("MMdd")
);
}
private static bool TryParseTimeFromAuditFilePath(string filePath, out DateTime dt)
{
dt = DateTime.MinValue;
try
{
// Expected: root\YYYY\MMDD\hhmmssff_Audit.csv
string fileName = Path.GetFileName(filePath);
if (string.IsNullOrEmpty(fileName)) return false;
int underscore = fileName.IndexOf('_');
if (underscore <= 0) return false;
string timePart = fileName.Substring(0, underscore); // "hhmmssff"
if (timePart.Length < 6) return false;
string folderMMDD = new DirectoryInfo(Path.GetDirectoryName(filePath)).Name; // "MMDD"
string folderYYYY = new DirectoryInfo(Path.GetDirectoryName(Path.GetDirectoryName(filePath))).Name; // "YYYY"
int year, month, day;
if (!int.TryParse(folderYYYY, out year)) return false;
if (folderMMDD == null || folderMMDD.Length != 4) return false;
if (!int.TryParse(folderMMDD.Substring(0, 2), out month)) return false;
if (!int.TryParse(folderMMDD.Substring(2, 2), out day)) return false;
int hh, mm, ss, ff = 0;
if (!int.TryParse(timePart.Substring(0, 2), out hh)) return false;
if (!int.TryParse(timePart.Substring(2, 2), out mm)) return false;
if (!int.TryParse(timePart.Substring(4, 2), out ss)) return false;
if (timePart.Length >= 8)
{
int tmp;
if (int.TryParse(timePart.Substring(6, 2), out tmp))
ff = tmp; // 1/100 sec
}
int ms = ff * 10;
dt = new DateTime(year, month, day, hh, mm, ss, ms);
return true;
}
catch
{
return false;
}
}
// --------------------------
// UI Apply (Single shot)
// --------------------------
private void ApplyResultToUi(AggregateResult r)
{
// Keep last result for export button usage.
this.Tag = r;
lblStatus.Text = string.Format("Files={0}, Rows={1}", r.FilesRead, r.RowsRead);
lblOk.Text = r.OkCount.ToString();
lblFixes.Text = r.FixesCount.ToString(); // Rename: NGWork -> Fixes
// Summary by reason (Fixes)
dgvFixesSummary.Rows.Clear();
foreach (var kv in r.FixesByReason.OrderByDescending(x => x.Value))
{
dgvFixesSummary.Rows.Add(kv.Key, kv.Value);
}
// Detail rows (Fix records)
dgvFixesDetails.Rows.Clear();
foreach (FixRecord rec in r.Details)
{
// Show main fields + optional raw columns join (or pick specific measurement columns).
string raw = (rec.RawColumns == null) ? "" : string.Join(",", rec.RawColumns);
dgvFixesDetails.Rows.Add(
rec.Time.ToString("yyyy-MM-dd HH:mm:ss.fff"),
rec.WorkNo,
rec.Reason,
rec.FilePath,
rec.LineNo,
raw
);
}
// Optional: show one example statistics summary for OK measurement columns
// (You can bind to another grid if you want.)
if (r.OkStatsByIndex.Count > 0)
{
int firstIndex = r.OkStatsByIndex.Keys.OrderBy(x => x).First();
StatisticsAccumulator acc = r.OkStatsByIndex[firstIndex];
if (acc.HasData)
{
lblOkStats.Text = string.Format(
"OK Stats (col {0}): avg={1:F3}, min={2:F3}, max={3:F3}, n={4}",
firstIndex, acc.Average, acc.Min, acc.Max, acc.Count);
}
else
{
lblOkStats.Text = "OK Stats: no numeric data.";
}
}
else
{
lblOkStats.Text = "OK Stats: no numeric columns.";
}
}
// --------------------------
// Export
// --------------------------
private static void ExportDetailsToCsv(List<FixRecord> details, string outputPath)
{
// Write UTF-8 with BOM to be Excel-friendly (optional).
using (var sw = new StreamWriter(outputPath, false, System.Text.Encoding.UTF8))
{
// Header
sw.WriteLine("Time,WorkNo,Reason,FilePath,LineNo,RawColumns");
foreach (FixRecord rec in details)
{
string raw = (rec.RawColumns == null) ? "" : string.Join("|", rec.RawColumns.Select(EscapeCsv));
sw.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",
EscapeCsv(rec.Time.ToString("yyyy-MM-dd HH:mm:ss.fff")),
rec.WorkNo,
EscapeCsv(rec.Reason),
EscapeCsv(rec.FilePath),
rec.LineNo,
EscapeCsv(raw)
));
}
}
}
private static string EscapeCsv(string s)
{
if (s == null) return "";
if (s.Contains("\"")) s = s.Replace("\"", "\"\"");
if (s.Contains(",") || s.Contains("\n") || s.Contains("\r"))
return "\"" + s + "\"";
return s;
}
}
// --------------------------
// Result DTOs
// --------------------------
public sealed class AggregateResult
{
public int FilesRead { get; set; }
public int RowsRead { get; set; }
public int OkCount { get; set; }
// Rename: NGWork -> Fixes
public int FixesCount { get; set; }
// Summary counts by reason
public Dictionary<string, int> FixesByReason { get; set; }
// Detail rows (Fixes)
public List<FixRecord> Details { get; set; }
// Measurement stats (OK and Fixes) by column index
public Dictionary<int, StatisticsAccumulator> OkStatsByIndex { get; set; }
public Dictionary<int, StatisticsAccumulator> FixesStatsByIndex { get; set; }
public AggregateResult()
{
FixesByReason = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
Details = new List<FixRecord>();
OkStatsByIndex = new Dictionary<int, StatisticsAccumulator>();
FixesStatsByIndex = new Dictionary<int, StatisticsAccumulator>();
}
}
// Rename: NGWorkRecord -> FixRecord
public sealed class FixRecord
{
public DateTime Time { get; set; }
public int WorkNo { get; set; }
public string Judge { get; set; } // "NG"
public string Reason { get; set; }
public string FilePath { get; set; }
public int LineNo { get; set; }
public string[] RawColumns { get; set; }
}
public sealed class StatisticsAccumulator
{
public double Min { get; private set; }
public double Max { get; private set; }
public double Sum { get; private set; }
public int Count { get; private set; }
public StatisticsAccumulator()
{
Min = double.PositiveInfinity;
Max = double.NegativeInfinity;
Sum = 0;
Count = 0;
}
public void Add(double value)
{
if (value < Min) Min = value;
if (value > Max) Max = value;
Sum += value;
Count++;
}
public double Average
{
get { return Count == 0 ? 0 : (Sum / Count); }
}
public bool HasData
{
get { return Count > 0; }
}
}
}