using CommunityToolkit.Mvvm.ComponentModel;
|
using HMI.Base;
|
using HMI.Core;
|
using HMI.Core.Setting;
|
using HMI.Core.Util;
|
using HMI.UI.Util;
|
using HMI.Util;
|
using Microsoft.Win32;
|
using Panuon.WPF.UI;
|
using System.Collections.ObjectModel;
|
using System.ComponentModel;
|
using System.Data;
|
using System.IO;
|
using System.Windows.Controls;
|
using System.Windows.Controls.Primitives;
|
|
namespace Plugin.DataViewer
|
{
|
public class TestResult
|
{
|
[ExcelColumn("SN")]
|
public string sn { get; set; }
|
|
[ExcelColumn("穴位号")]
|
public string position { get; set; }
|
|
[ExcelColumn("型号")]
|
public string model { get; set; }
|
|
[ExcelColumn("测试结果")]
|
public string result { get; set; }
|
|
[ExcelColumn("测试时间")]
|
public string time { get; set; }
|
}
|
|
public class SerialData
|
{
|
[ExcelColumn("SN")]
|
public string sn { get; set; }
|
|
[ExcelColumn("穴位号")]
|
public string position { get; set; }
|
|
[ExcelColumn("型号")]
|
public string model { get; set; }
|
|
[ExcelColumn("测试项")]
|
public string step { get; set; }
|
|
[ExcelColumn("测试值")]
|
public string value { get; set; }
|
|
[ExcelColumn("下限")]
|
public string limitDown { get; set; }
|
|
[ExcelColumn("上限")]
|
public string limitUp { get; set; }
|
[ExcelColumn("结果")]
|
public string status { get; set; }
|
|
[ExcelColumn("单位")]
|
public string unit { get; set; }
|
|
[ExcelColumn("测试时间")]
|
public string time { get; set; }
|
}
|
|
public class SortParam
|
{
|
public string name { get; set; }
|
public ListSortDirection direction { get; set; }
|
public string sort { get; set; }
|
}
|
|
public class ResultData
|
{
|
public string sn { get; set; }
|
public string position { get; set; }
|
}
|
|
/// <summary>
|
/// DataViewerControl.xaml 的交互逻辑
|
/// </summary>
|
[ObservableObject]
|
public partial class DataViewerControl : SimpleMaskControl
|
{
|
[ObservableProperty]
|
private DataView resultView;
|
|
[ObservableProperty]
|
private ObservableCollection<ResultData> resultData;
|
|
[ObservableProperty]
|
private long currentPage = 1;
|
|
[ObservableProperty]
|
private long totalPage = 0;
|
|
private int pageSize = 30;
|
|
[ObservableProperty]
|
private long totalCount;
|
|
[ObservableProperty]
|
private long passCount;
|
|
[ObservableProperty]
|
private long failCount;
|
|
[ObservableProperty]
|
private long testItemPage = 1;
|
|
[ObservableProperty]
|
private long testItemTotalPage = 0;
|
|
[ObservableProperty]
|
private object selectedResult;
|
|
[ObservableProperty]
|
private string selectedStatus;
|
|
private SortParam sorter;
|
private SortParam itemSorter;
|
public DataViewerControl()
|
{
|
DataContext = this;
|
InitializeComponent();
|
Reset();
|
}
|
|
private string[] GetFiles(DateTime? startTime, DateTime? endTime)
|
{
|
string dir = ParamSetting.DataLogFolder;
|
string startName = $"Data_{startTime?.ToString("yyyyMMdd")}";
|
string endName = $"Data_{endTime?.ToString("yyyyMMdd")}";
|
var oldFiles = Directory.GetFiles(dir, "Data_*.db");
|
oldFiles = oldFiles.Where(file =>
|
{
|
var fileName = Path.GetFileNameWithoutExtension(file);
|
bool flag = true;
|
flag &= (string.Compare(fileName, startName) == 1 || fileName == startName);
|
flag &= (string.Compare(endName, fileName) == 1 || fileName == endName);
|
return flag;
|
}).ToArray();
|
return oldFiles;
|
}
|
|
private void Reset()
|
{
|
var today = DateTime.Now;
|
var startTime = today.AddDays(-7);
|
var end = today.AddDays(1);
|
|
snInput.Text = string.Empty;
|
positionInput.Text = string.Empty;
|
modelInput.Text = string.Empty;
|
resultInput.Text = string.Empty;
|
|
startTimeInput.SelectedDateTime = DateTime.Parse(startTime.ToString("yyyy-MM-dd 00:00:00"));
|
endTimeInput.SelectedDateTime = DateTime.Parse(end.ToString("yyyy-MM-dd 00:00:00"));
|
}
|
|
private void Button_Click(object sender, System.Windows.RoutedEventArgs e)
|
{
|
var btn = sender as Button;
|
if (btn.Name == "resetBtn")
|
{
|
Reset();
|
if (CurrentPage == 1)
|
{
|
SelectPage(1, this.pageSize);
|
}
|
else
|
{
|
CurrentPage = 1;
|
}
|
return;
|
}
|
if (btn.Name == "srhBtn")
|
{
|
if (CurrentPage == 1)
|
{
|
SelectPage(1, this.pageSize);
|
}
|
else
|
{
|
CurrentPage = 1;
|
}
|
return;
|
}
|
|
if (btn.Name == "srhItemBtn")
|
{
|
if (TestItemPage == 1)
|
{
|
RefreshTestItem(this.selectResultId, 1, this.pageSize);
|
}
|
else
|
{
|
TestItemPage = 1;
|
}
|
return;
|
}
|
|
if (btn.Name == "exportBtn")
|
{
|
TryExport();
|
}
|
|
if (btn.Name == "exportItemBtn")
|
{
|
TryExportItem();
|
}
|
}
|
|
private void TryExport()
|
{
|
SaveFileDialog sfd = new SaveFileDialog
|
{
|
Filter = "Excel 文件 (*.xls)|*.xls"
|
};
|
if (sfd.ShowDialog() != true)
|
{
|
return;
|
}
|
string filePath = sfd.FileName;
|
|
DateTime? startTime = startTimeInput.SelectedDateTime, endTime = endTimeInput.SelectedDateTime;
|
|
topMask.ShowMask(this, () =>
|
{
|
var table = SelectResult(startTime, endTime);
|
|
ExcelHelper.ExportExcel<TestResult>(filePath, table);
|
|
}, (ex) =>
|
{
|
log.Error("Export Result Failed.", ex);
|
Dispatcher.Invoke(() =>
|
{
|
MsgBoxHelper2.Error($"导出失败！{ex.Message}");
|
});
|
}, "导出中,请稍后...");
|
}
|
|
private void TryExportItem()
|
{
|
SaveFileDialog sfd = new SaveFileDialog
|
{
|
Filter = "Excel 文件 (*.xls)|*.xls"
|
};
|
if (sfd.ShowDialog() != true)
|
{
|
return;
|
}
|
string filePath = sfd.FileName;
|
|
DateTime? startTime = startTimeInput.SelectedDateTime, endTime = endTimeInput.SelectedDateTime;
|
|
topMask.ShowMask(this, () =>
|
{
|
var table = SelectItem(startTime, endTime);
|
|
ExcelHelper.ExportExcel<SerialData>(filePath, table);
|
|
}, (ex) =>
|
{
|
log.Error("Export Result Failed.", ex);
|
Dispatcher.Invoke(() =>
|
{
|
MsgBoxHelper2.Error($"导出失败！{ex.Message}");
|
});
|
}, "导出中,请稍后...");
|
}
|
|
private DataTable SelectResult(DateTime? startTime, DateTime? endTime)
|
{
|
var files = GetFiles(startTime, endTime);
|
if (files == null || files.Length <= 0)
|
{
|
return new DataTable();
|
}
|
using (var helper = new Sqlite3Helper(files[0]))
|
{
|
for (int i = 0; i < files.Length; i++)
|
{
|
var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
|
helper.Execute(attach);
|
}
|
|
for (int i = 0; i < files.Length; i++)
|
{
|
if (!helper.HasData($"SELECT 1 FROM pragma_table_info('testResult','db{i}') WHERE name = 'resultId';"))
|
{
|
helper.Execute($"ALTER TABLE db{i}.testResult ADD resultId TEXT;");
|
}
|
}
|
|
string sql = string.Join(" UNION ALL ", GetResultSqlsByFiles(files, startTime, endTime));
|
string sortName = "time", sortDirection = "DESC";
|
string baseSql = $"SELECT id,sn,model,station,position,result,time,resultId FROM ({sql}) as _temp";
|
|
|
|
sql = $"{baseSql} ORDER BY {sortName} {sortDirection}";
|
|
return helper.GetDataTable(sql);
|
|
//for (int i = 0; i < files.Length; i++)
|
//{
|
// helper.Execute($"DETACH DATABASE db{i}");
|
//}
|
}
|
}
|
|
private DataTable SelectItem(DateTime? startTime, DateTime? endTime)
|
{
|
if (string.IsNullOrEmpty(this.selectResultId))
|
{
|
return new DataTable();
|
}
|
var files = GetFiles(startTime, endTime);
|
if (files == null || files.Length <= 0)
|
{
|
return new DataTable();
|
}
|
using (var helper = new Sqlite3Helper(files[0]))
|
{
|
for (int i = 0; i < files.Length; i++)
|
{
|
var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
|
helper.Execute(attach);
|
}
|
|
for (int i = 0; i < files.Length; i++)
|
{
|
if (!helper.HasData($"SELECT 1 FROM pragma_table_info('serialData','db{i}') WHERE name = 'resultId';"))
|
{
|
helper.Execute($"ALTER TABLE db{i}.serialData ADD resultId TEXT;");
|
}
|
}
|
|
string sql = string.Join(" UNION ALL ", GetTestItemSqlsByFiles(this.selectResultId, files));
|
|
string sortName = "time", sortDirection = "DESC";
|
|
string baseSql = $"SELECT sn, position, model,step, limitDown, limitUp,unit, value,status,time FROM ({sql}) as _temp";
|
|
sql = $"{baseSql} ORDER BY {sortName} {sortDirection}";
|
|
return helper.GetDataTable(sql);
|
|
|
//for (int i = 0; i < files.Length; i++)
|
//{
|
// helper.Execute($"DETACH DATABASE db{i}");
|
//}
|
}
|
}
|
|
private void SelectPage(long page, long pageSize)
|
{
|
try
|
{
|
var files = GetFiles(startTimeInput.SelectedDateTime, endTimeInput.SelectedDateTime);
|
if (files == null || files.Length <= 0)
|
{
|
return;
|
}
|
using (var helper = new Sqlite3Helper(files[0]))
|
{
|
for (int i = 0; i < files.Length; i++)
|
{
|
var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
|
helper.Execute(attach);
|
}
|
|
for (int i = 0; i < files.Length; i++)
|
{
|
if (!helper.HasData($"SELECT 1 FROM pragma_table_info('testResult','db{i}') WHERE name = 'resultId';"))
|
{
|
helper.Execute($"ALTER TABLE db{i}.testResult ADD resultId TEXT;");
|
}
|
}
|
|
string sql = string.Join(" UNION ALL ", GetResultSqlsByFiles(files, startTimeInput.SelectedDateTime, endTimeInput.SelectedDateTime));
|
string sortName = "time", sortDirection = "DESC";
|
|
if (sorter != null)
|
{
|
sortName = sorter.name;
|
sortDirection = sorter.sort;
|
}
|
string countSql = $"SELECT COUNT(1) FROM ({sql}) as _temp";
|
long total = (long)helper.SelectOne(countSql);
|
|
string baseSql = $"SELECT id,sn,model,station,position,result,time,resultId FROM ({sql}) as _temp";
|
|
|
|
sql = $"{baseSql} ORDER BY {sortName} {sortDirection} LIMIT {pageSize} OFFSET {(page - 1) * pageSize}";
|
|
var dt = helper.GetDataTable(sql);
|
|
TotalPage = total / pageSize + (total % pageSize > 0 ? 1 : 0);
|
resultTable.ItemsSource = dt.DefaultView;
|
|
sql = $"SELECT result,COUNT(1) as num FROM ({baseSql}) GROUP BY result";
|
var nums = helper.GetDataTable(sql);
|
|
long all = 0, pass = 0, fail = 0;
|
foreach (DataRow row in nums.Rows)
|
{
|
if ("ok".Equals(row["result"].ToString(), StringComparison.OrdinalIgnoreCase)
|
|| "pass".Equals(row["result"].ToString(), StringComparison.OrdinalIgnoreCase))
|
{
|
pass += (long)row["num"];
|
all += (long)row["num"];
|
}
|
if ("ng".Equals(row["result"].ToString(), StringComparison.OrdinalIgnoreCase)
|
|| "fail".Equals(row["result"].ToString(), StringComparison.OrdinalIgnoreCase))
|
{
|
fail += (long)row["num"];
|
all += (long)row["num"];
|
}
|
}
|
|
TotalCount = all;
|
PassCount = pass;
|
FailCount = fail;
|
|
//for (int i = 0; i < files.Length; i++)
|
//{
|
// helper.Execute($"DETACH DATABASE db{i}");
|
//}
|
}
|
}
|
catch (Exception ex)
|
{
|
log.Error("Select TestResult Error.", ex);
|
|
}
|
}
|
|
private void RefreshTestItem(string resultId, long page, int pageSize)
|
{
|
try
|
{
|
if (string.IsNullOrEmpty(resultId))
|
{
|
testItemTable.ItemsSource = new DataTable().DefaultView;
|
return;
|
}
|
|
var files = GetFiles(startTimeInput.SelectedDateTime, endTimeInput.SelectedDateTime);
|
if (files == null || files.Length <= 0)
|
{
|
return;
|
}
|
|
string sql = string.Join(" UNION ALL ", GetTestItemSqlsByFiles(resultId, files));
|
string sortName = "time", sortDirection = "DESC";
|
if (itemSorter != null)
|
{
|
sortName = itemSorter.name;
|
sortDirection = itemSorter.sort;
|
}
|
|
mask2.ShowMask(null, () =>
|
{
|
using (var helper = new Sqlite3Helper(files[0]))
|
{
|
for (int i = 0; i < files.Length; i++)
|
{
|
var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
|
helper.Execute(attach);
|
}
|
|
for (int i = 0; i < files.Length; i++)
|
{
|
if (!helper.HasData($"SELECT 1 FROM pragma_table_info('serialData','db{i}') WHERE name = 'resultId';"))
|
{
|
helper.Execute($"ALTER TABLE db{i}.serialData ADD resultId TEXT;");
|
}
|
}
|
|
|
string countSql = $"SELECT COUNT(1) FROM ({sql}) as _temp";
|
long total = (long)helper.SelectOne(countSql);
|
|
string baseSql = $"SELECT sn, model,step, limitDown, limitUp,unit, value,status,time FROM ({sql}) as _temp";
|
|
sql = $"{baseSql} ORDER BY {sortName} {sortDirection} LIMIT {pageSize} OFFSET {(page - 1) * pageSize}";
|
|
var dt = helper.GetDataTable(sql);
|
|
|
//for (int i = 0; i < files.Length; i++)
|
//{
|
// helper.Execute($"DETACH DATABASE db{i}");
|
//}
|
|
Dispatcher.BeginInvoke(() =>
|
{
|
TestItemTotalPage = total / pageSize + (total % pageSize > 0 ? 1 : 0);
|
testItemTable.ItemsSource = dt.DefaultView;
|
}, System.Windows.Threading.DispatcherPriority.Normal);
|
}
|
}, (ex) =>
|
{
|
log.Error("Select TestItem Error.", ex);
|
},
|
"查询中...");
|
}
|
catch (Exception ex)
|
{
|
log.Error("Try Select TestItem Error.", ex);
|
}
|
}
|
|
public string[] GetResultSqlsByFiles(string[] files, DateTime? startTime, DateTime? endTime)
|
{
|
string sn = string.Empty, position = string.Empty, model = string.Empty, result = string.Empty;
|
Dispatcher.Invoke(() =>
|
{
|
sn = snInput.Text;
|
position = positionInput.Text;
|
model = modelInput.Text;
|
result = resultInput.Text;
|
});
|
|
string[] sqls = new string[files.Length];
|
List<string> querys = new List<string>();
|
if (!string.IsNullOrEmpty(sn))
|
{
|
querys.Add($"sn = '{sn}'");
|
}
|
|
if (!string.IsNullOrEmpty(position))
|
{
|
querys.Add($"position = '{position}'");
|
}
|
|
if (!string.IsNullOrEmpty(model))
|
{
|
querys.Add($"model = '{model}'");
|
}
|
|
if (!string.IsNullOrEmpty(result))
|
{
|
querys.Add($"result = '{result}'");
|
}
|
|
if (startTime != null)
|
{
|
querys.Add($"time >= '{startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}'");
|
}
|
|
if (endTime != null)
|
{
|
querys.Add($"time < '{endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}'");
|
}
|
|
string segment = "";
|
if (querys.Count > 0)
|
{
|
segment = $" WHERE {string.Join(" AND ", querys)}";
|
}
|
|
for (int i = 0; i < files.Length; i++)
|
{
|
sqls[i] = $"SELECT id,sn,model,station,position,result,time,resultId FROM db{i}.testResult {segment}";
|
}
|
return sqls;
|
}
|
|
private string[] GetTestItemSqlsByFiles(string resultId, string[] files)
|
{
|
List<string> querys = new List<string>() {
|
$"resultId = '{resultId}'"
|
};
|
string step = string.Empty, status = string.Empty;
|
Dispatcher.Invoke(() =>
|
{
|
step = stepInput.Text;
|
status = statusInput.Text;
|
});
|
|
|
if (!string.IsNullOrEmpty(step))
|
{
|
querys.Add($"step = '{step}'");
|
}
|
|
if (!string.IsNullOrEmpty(status))
|
{
|
querys.Add($"status = '{status}'");
|
}
|
|
string segment = string.Join(" AND ", querys); ;
|
|
string[] sqls = new string[files.Length];
|
for (int i = 0; i < files.Length; i++)
|
{
|
sqls[i] = $"SELECT sn, position, model,step, limitDown, limitUp,unit, value,status,time FROM db{i}.serialData WHERE {segment}";
|
}
|
return sqls;
|
}
|
|
public string[] GetSource(string[] files, string name, string tableName, string value)
|
{
|
try
|
{
|
if (files == null || files.Length <= 0)
|
{
|
return new string[0];
|
}
|
string[] GetModelSqls()
|
{
|
string[] sqls = new string[files.Length];
|
for (int i = 0; i < files.Length; i++)
|
{
|
sqls[i] = $"SELECT DISTINCT {name} FROM db{i}.{tableName} WHERE {name} LIKE '%{value}%'";
|
}
|
return sqls;
|
}
|
|
using (var helper = new Sqlite3Helper(files[0]))
|
{
|
for (int i = 0; i < files.Length; i++)
|
{
|
var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
|
helper.Execute(attach);
|
}
|
string sql = string.Join(" UNION ", GetModelSqls());
|
sql = $"SELECT DISTINCT {name} FROM ({sql}) LIMIT 10";
|
|
var dt = helper.GetDataTable(sql);
|
return dt.Select().Select(row => row[name].ToString()).ToArray();
|
}
|
return new string[0];
|
}
|
catch (Exception ex)
|
{
|
return new string[0];
|
}
|
}
|
|
|
private void SearchTextChanged(object sender, Panuon.WPF.SearchTextChangedRoutedEventArgs e)
|
{
|
SearchBox sb = sender as SearchBox;
|
string tag = sb.Tag.ToString(), value = sb.Text;
|
var files = GetFiles(startTimeInput.SelectedDateTime, endTimeInput.SelectedDateTime);
|
Task.Run(() =>
|
{
|
string[] items = new string[0];
|
if (tag == "step" || tag == "status")
|
{
|
items = GetSource(files, tag, "serialData", value);
|
}
|
else
|
{
|
items = GetSource(files, tag, "testResult", value);
|
}
|
Dispatcher.BeginInvoke(new Action(() =>
|
{
|
sb.ItemsSource = items;
|
}), System.Windows.Threading.DispatcherPriority.Normal);
|
});
|
}
|
|
private void Grid_MouseLeftButtonUp(object sender, System.Windows.Input.MouseButtonEventArgs e)
|
{
|
var border = sender as Border;
|
var header = border.Tag as DataGridColumnHeader;
|
var col = header.Column as ICustomSortColumn;
|
|
if (col == null) return;
|
|
col.UpdateOther(null);
|
|
var dataGrid = col.GetOwner();
|
|
if (col.IsAsc == null)
|
{
|
col.IsAsc = true;
|
|
if (dataGrid == resultTable)
|
{
|
sorter = new SortParam { name = header.Column.SortMemberPath, direction = ListSortDirection.Ascending, sort = "ASC" };
|
}
|
else {
|
itemSorter = new SortParam { name = header.Column.SortMemberPath, direction = ListSortDirection.Ascending, sort = "ASC" };
|
}
|
|
}
|
else if (col.IsAsc == true)
|
{
|
col.IsAsc = false;
|
if (dataGrid == resultTable)
|
{
|
sorter = new SortParam { name = header.Column.SortMemberPath, direction = ListSortDirection.Descending, sort = "DESC" };
|
}
|
else
|
{
|
itemSorter = new SortParam { name = header.Column.SortMemberPath, direction = ListSortDirection.Descending, sort = "DESC" };
|
}
|
}
|
else
|
{
|
col.IsAsc = null;
|
if (dataGrid == resultTable)
|
{
|
sorter = null;
|
}
|
else
|
{
|
itemSorter = null;
|
}
|
}
|
|
if (dataGrid == resultTable)
|
{
|
if (CurrentPage == 1)
|
{
|
SelectPage(1, this.pageSize);
|
}
|
else
|
{
|
CurrentPage = 1;
|
}
|
}
|
else
|
{
|
if (TestItemPage == 1)
|
{
|
RefreshTestItem(this.selectResultId, 1, this.pageSize);
|
}
|
else
|
{
|
TestItemPage = 1;
|
}
|
}
|
}
|
|
private void page_CurrentPageChanged(object sender, Panuon.WPF.SelectedValueChangedRoutedEventArgs<int> e)
|
{
|
if (sender == page)
|
{
|
SelectPage(e.NewValue, this.pageSize);
|
}
|
else
|
{
|
RefreshTestItem(this.selectResultId, e.NewValue, this.pageSize);
|
}
|
}
|
|
private string selectResultId;
|
|
partial void OnSelectedResultChanged(object value)
|
{
|
if (value != null)
|
{
|
return;
|
}
|
var selectedRow = value as DataRowView;
|
var resultId = selectedRow?["resultId"]?.ToString();
|
this.SelectedStatus = selectedRow?["result"]?.ToString();
|
this.selectResultId = resultId;
|
|
if (TestItemPage == 1)
|
{
|
RefreshTestItem(resultId, 1, this.pageSize);
|
}
|
else
|
{
|
TestItemPage = 1;
|
}
|
}
|
|
private void resultTable_MouseDoubleClick(object sender, System.Windows.Input.MouseButtonEventArgs e)
|
{
|
var selectedRow = this.SelectedResult as DataRowView;
|
var resultId = selectedRow?["resultId"]?.ToString();
|
this.SelectedStatus = selectedRow?["result"]?.ToString();
|
this.selectResultId = resultId;
|
|
if (TestItemPage == 1)
|
{
|
RefreshTestItem(resultId, 1, this.pageSize);
|
}
|
else
|
{
|
TestItemPage = 1;
|
}
|
}
|
}
|
}
|
