/// <summary> /// 主页面告警及饼状图模块 /// </summary> public HomeTableView() { InitializeComponent(); var DayTime = DateTime.Today.Date.ToShortDateString(); var HourTime = DateTime.Now.Hour.ToString(); string hoursql = $"SELECT FaultID,Content FROM FaultData WHERE Day = '{DayTime}' AND Time = {HourTime}"; DataTable HourData = SQLiteHelp.ExecuteQuery(hoursql); if (HourData.Rows.Count > 0) { // 默认显示小时数据 UserPieCharts = new BreakDownPieChart("Hour", "Bottom"); } else { UserPieCharts = new NoData(); } FontColor = MainWindow.WindowFontColor; BgColor = MainWindow.WindowBgColor; // 告警信息数据显示 this.DataGrid.DataContext = new WarningList(); this.DataContext = this; }
/// <summary> /// 添加月柱状图数据 /// </summary> public void AddBarCharts() { string devlidtsql = "SELECT ID FROM Equipment"; DataTable devdata = SQLiteHelp.ExecuteQuery(devlidtsql); //获取当前设备编号列表 var DevNumberList = SQLiteHelp.NumberList(devdata, "ID"); // 添加数据 for (int i = 0; i < DevNumberList.Count; i++) { BrushConverter brushconverter = new BrushConverter(); Brush color = (Brush)brushconverter.ConvertFromString(MainWindow.ColorList[i % MainWindow.ColorList.Length]); color.Opacity = 0.6; UserBarCharts.Add(new StackedColumnSeries { Values = new ChartValues <double>(), DataLabels = true, Title = MainWindow.DeviceList[i], Fill = color, }); UserBarCharts[i].Values = AddBarChartValue(i); OnPropertyChanged(nameof(UserBarCharts)); } }
/// <summary> /// 添加折线图数据 /// </summary> public void AddLineCharts() { string devlidtsql = "SELECT ID FROM Equipment"; DataTable devdata = SQLiteHelp.ExecuteQuery(devlidtsql); //获取当前设备编号列表 var DevNumberList = SQLiteHelp.NumberList(devdata, "ID"); for (int i = 0; i < DevNumberList.Count; i++) { BrushConverter brushconverter = new BrushConverter(); Brush color = (Brush)brushconverter.ConvertFromString(MainWindow.ColorList[i % MainWindow.ColorList.Length]); color.Opacity = 0.1; UserLineCharts.Add(new GLineSeries { Values = new GearedValues <double>(), DataLabels = false, Title = MainWindow.DeviceList[i], LineSmoothness = 0.3, StrokeThickness = 1, Stroke = (Brush)brushconverter.ConvertFromString(MainWindow.ColorList[i % MainWindow.ColorList.Length]), Fill = color, }); UserLineCharts[i].Values = AddChartValue(i); OnPropertyChanged(nameof(UserLineCharts)); } }
/// <summary> /// 添加故障排名前十的列表 /// </summary> public void AddFaultData(string Date) { DataTable dt = new DataTable("Fault"); dt.Columns.Add("ID", typeof(int)); dt.Columns.Add("Number", typeof(int)); var DayTime = DateTime.Today.ToShortDateString(); string Allsql = $"SELECT ID, Number FROM DetailedFault WHERE Day = '{DayTime}'"; DataTable AllTable = SQLiteHelp.ExecuteQuery(Allsql); if (AllTable.Rows.Count > 0) { for (int i = 0; i < MainWindow.FaultMessageList.Length; i++) { string sql = $"SELECT ID, sum(Number) FROM DetailedFault WHERE Day = '{DayTime}' AND ID = {i}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); dt.Rows.Add( data.Rows[0][0], data.Rows[0][1] ); } // 排序表格 DataView dv = dt.DefaultView; dv.Sort = "Number DESC"; dt = dv.ToTable(); // 取表格前几位 DataTable NewTable = DtSelectTop(10, dt); // 获取故障总数 string ratiosql = $"SELECT sum(Number) FROM DetailedFault WHERE Day = '{DayTime}'"; DataTable ToyalData = SQLiteHelp.ExecuteQuery(ratiosql); int TotalValue = 0; if (ToyalData.Rows[0][0].ToString() != "") { TotalValue = int.Parse(ToyalData.Rows[0][0].ToString()); } // 添加表格体数据 for (int i = 0; i < NewTable.Rows.Count; i++) { FaultList.Add(new FaultFormat() { FaultName = MainWindow.FaultMessageList[int.Parse(NewTable.Rows[i][0].ToString())], FaultNumber = NewTable.Rows[i][1].ToString(), Ratio = (double.Parse(NewTable.Rows[i][1].ToString()) / TotalValue).ToString("P2") }); } } }
/// <summary> /// 获取数据库值 添加初始值 /// </summary> private void GetText() { string sql = "SELECT * FROM StandardData"; DataTable data = SQLiteHelp.ExecuteQuery(sql); // 添加页面显示数据 if (data.Rows.Count > 0) { CapacityText = int.Parse(data.Rows[data.Rows.Count - 1][1].ToString()); DayPlanText = int.Parse(data.Rows[data.Rows.Count - 1][2].ToString()); MonthPlanText = int.Parse(data.Rows[data.Rows.Count - 1][3].ToString()); Capacity.Text = $"{CapacityText}"; DayPlan.Text = $"{DayPlanText}"; MonthPlan.Text = $"{MonthPlanText}"; } }
/// <summary> /// 添加设备初始信息 /// </summary> private void AddSource() { string sql = "SELECT * FROM Equipment"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { for (int i = 0; i < data.Rows.Count; i++) { DevNameList.Add(new DeviceName() { ID = data.Rows[i][0].ToString(), Name = data.Rows[i][1].ToString() }); } } }
/// <summary> /// 月点击 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Month_Checked(object sender, RoutedEventArgs e) { //按钮颜色 HourBackcolor = Color; DayBackcolor = Color; MonthBackcolor = Click; string monthsql = $"SELECT 1 FROM FaultData"; DataTable MonthData = SQLiteHelp.ExecuteQuery(monthsql); if (MonthData.Rows.Count > 0) { UserPieCharts = new BreakDownPieChart("Month", "Bottom"); } else { UserPieCharts = new NoData(); } }
/// <summary> /// 获取数据库中的IP地址 /// </summary> private void GetIP() { string sql = "SELECT * FROM IPData"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { int ip1 = int.Parse(data.Rows[data.Rows.Count - 1][0].ToString()); int ip2 = int.Parse(data.Rows[data.Rows.Count - 1][1].ToString()); int ip3 = int.Parse(data.Rows[data.Rows.Count - 1][2].ToString()); int ip4 = int.Parse(data.Rows[data.Rows.Count - 1][3].ToString()); int port = int.Parse(data.Rows[data.Rows.Count - 1][4].ToString()); IP1.Text = $"{ip1}"; IP2.Text = $"{ip2}"; IP3.Text = $"{ip3}"; IP4.Text = $"{ip4}"; Port.Text = $"{port}"; IP = $"{ip1}.{ip2}.{ip3}.{ip4}:{port}"; } }
/// <summary> /// 天点击 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Day_Checked(object sender, RoutedEventArgs e) { //按钮颜色 HourBackcolor = Color; DayBackcolor = Click; MonthBackcolor = Color; var DayTime = DateTime.Today.Date.ToShortDateString(); string daysql = $"SELECT FaultID,Content FROM FaultData WHERE Day = '{DayTime}'"; DataTable DayData = SQLiteHelp.ExecuteQuery(daysql); if (DayData.Rows.Count > 0) { // 默认显示小时数据 UserPieCharts = new BreakDownPieChart("Day", "Bottom"); } else { UserPieCharts = new NoData(); } }
/// <summary> /// 添加表格数据 /// </summary> /// <param name="Date">时间日期</param> public void AddTableData(string Date) { string sql = $"SELECT Time,Produce,GoodProduct FROM DayDeviceData WHERE Day = '{Date}' AND DevID = {0}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); // 判断是否存在数据 if (data.Rows.Count > 0) { for (int i = 0; i < data.Rows.Count; i++) { if (int.Parse(data.Rows[i][1].ToString()) > 0) { AddGoodList.Add(new GoodDataFormat() { Time = data.Rows[i][0].ToString(), Produce = data.Rows[i][1].ToString(), Good = data.Rows[i][2].ToString() }); } } } }
///// <summary> ///// 获取鼠标选中的行 ///// </summary> ///// <param name="dg">表格体</param> ///// <param name="rowIndex"></param> ///// <param name="columnIndex"></param> ///// <returns></returns> //private bool GetCellXY(DataGrid dg, ref int rowIndex, ref int columnIndex) //{ // var cells = dg.SelectedCells; // if (cells.Any()) // { // rowIndex = dg.Items.IndexOf(cells.First().Item); // columnIndex = cells.First().Column.DisplayIndex; // string devlidtsql = "SELECT DevID FROM DayDeviceData"; // DataTable devdata = SQLiteHelp.ExecuteQuery(devlidtsql); // //获取当前设备编号列表 // var DevNumberList = SQLiteHelp.NumberList(devdata, "DevID"); // foreach (var id in DevNumberList) // { // if (int.Parse(DevNameList[rowIndex].ID) == id) // { // MessageBox.Show("无法删除设备,该设备已有运行数据,只支持修改设备名称"); // return false; // } // } // return true; // } // return false; //} ///// <summary> ///// 获取选中的值 ///// </summary> ///// <param name="dg"></param> ///// <returns></returns> //private string GetSelectedCellsValue(DataGrid dg) //{ // var cells = dg.SelectedCells; // StringBuilder sb = new StringBuilder(); // if (cells.Any()) // { // foreach (var cell in cells) // { // sb.Append((cell.Column.GetCellContent(cell.Item) as TextBlock).Text); // sb.Append(" "); // } // } // return sb.ToString(); //} ///// <summary> ///// 删除选中的行 ///// </summary> ///// <param name="sender"></param> ///// <param name="e"></param> //private void DeleteRow(object sender, RoutedEventArgs e) //{ // int rowIndex = 0; // int columnIndex = 0; // if (GetCellXY(DataGrid, ref rowIndex, ref columnIndex)) // { // DevNameList.RemoveAt(rowIndex); // } //} /// <summary> /// 删除末行 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void DeleteRow(object sender, RoutedEventArgs e) { if (DevNameList.Count > 0) { string devlidtsql = "SELECT DevID FROM DayDeviceData"; DataTable devdata = SQLiteHelp.ExecuteQuery(devlidtsql); //获取当前设备编号列表 var DevNumberList = SQLiteHelp.NumberList(devdata, "DevID"); foreach (var id in DevNumberList) { // 如果数据库中存在要删除设备的数据,则无法删除 if (int.Parse(DevNameList[DevNameList.Count - 1].ID) == id) { Message mes = new Message(2, "无法删除设备,该设备已有运行数据,只支持修改设备名称"); mes.ShowDialog(); return; } } DevNameList.RemoveAt(DevNameList.Count - 1); } }
/// <summary> /// 获取ModBus地址 /// </summary> public void GetModBusData() { string sql = "SELECT * FROM IPData"; DataTable data = SQLiteHelp.ExecuteQuery(sql); // 判断是否有IP地址数据 if (data.Rows.Count > 0) { try { RunGetModBus(); } catch (Exception) { Message mes = new Message(2, "读取ModBus失败"); mes.ShowDialog(); } } else { Message mes = new Message(2, "获取ModBus地址失败"); mes.ShowDialog(); } }
/// <summary> /// 获取对应设备ID的图表数据 /// </summary> /// <param name="ID">设备ID</param> /// <returns></returns> public ChartValues <BarStatistics> AddBarChartValue(int ID) { ChartValues <BarStatistics> BarChartValue = new ChartValues <BarStatistics>(); // 获取当前天数 var nowday = DateTime.Now.Day.ToString(); // 获取当前年份 var nowyear = DateTime.Now.Year.ToString(); // 获取当前月份 var nowmonth = DateTime.Now.Month.ToString(); if (UserBarCharts[0].Values.Count > 20) { AxisXMax = UserBarCharts[0].Values.Count - 1; AxisXMin = UserBarCharts[0].Values.Count - 20; } else { AxisXMax = 20; AxisXMin = 0; } for (int i = 1; i < int.Parse(nowday) + 1; i++) { // 生成当前天数之前的日期 如果有日期没有数据,则添加图表数据为0 string nowtime = nowyear + "/" + nowmonth + "/" + i; // 查询数据库 string sql = $"SELECT DevID,Produce FROM MonthDeviceData WHERE Day = '{nowtime}' AND DevID = {ID}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { if (data.Rows[0][1].ToString() == "") { BarChartValue.Add(new BarStatistics { Num = i - 1, Value = 0 }); } else { BarChartValue.Add(new BarStatistics { Num = i - 1, Value = double.Parse(data.Rows[0][1].ToString()) }); } } else { BarChartValue.Add(new BarStatistics { Num = i - 1, Value = 0 }); } } //timerNotice = new System.Timers.Timer(); ////间隔触发函数 //timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => //{ //}); ////触发间隔 //timerNotice.Interval = MainWindow.Time; //timerNotice.Start(); return(BarChartValue); }
/// <summary> /// 设置页面展示数据 /// </summary> private void SetChartValue() { var DayTime = DateTime.Today.ToShortDateString(); // 添加饼状图数据 string insertsql = $"SELECT TimeName,Hour,Minute,Second FROM TimeData WHERE Day = '{DayTime}'"; //执行查询操作输出DataTable DataTable Insertdata = SQLiteHelp.ExecuteQuery(insertsql); if (Insertdata.Rows.Count > 0) { // 上电时间 时/分/秒 int StartHour = int.Parse(Insertdata.Rows[0][1].ToString()); int StartMin = int.Parse(Insertdata.Rows[0][2].ToString()); int StartS = int.Parse(Insertdata.Rows[0][3].ToString()); // 运行时间 时/分/秒 int Hour = int.Parse(Insertdata.Rows[1][1].ToString()); int Min = int.Parse(Insertdata.Rows[1][2].ToString()); int Second = int.Parse(Insertdata.Rows[1][3].ToString()); // 停机时间 时/分/秒 int StopHour = int.Parse(Insertdata.Rows[2][1].ToString()); int StopMin = int.Parse(Insertdata.Rows[2][2].ToString()); int StopSecond = int.Parse(Insertdata.Rows[2][3].ToString()); // 故障时间 时/分/秒 int FaultHour = int.Parse(Insertdata.Rows[3][1].ToString()); int FaultMin = int.Parse(Insertdata.Rows[3][2].ToString()); int FaultSecond = int.Parse(Insertdata.Rows[3][3].ToString()); // 添加饼状图数据 for (int i = 0; i < UserPieChart.Count; i++) { switch (i) { case 0: // 添加运行时间数据 int Time = Hour * 60 * 60 + Min * 60 + Second; if (Insertdata.Rows.Count > 0) { UserPieChart[i].Values = new ChartValues <double> { (double)Time }; } else { UserPieChart[i].Values = new ChartValues <double> { 0 }; } break; case 1: // 添加停机时间数据 int StopTime = StopHour * 60 * 60 + StopMin * 60 + StopSecond; if (Insertdata.Rows.Count > 0) { UserPieChart[i].Values = new ChartValues <double> { (double)StopTime }; } else { UserPieChart[i].Values = new ChartValues <double> { 0 }; } break; case 2: // 添加故障时间数据 int FaultTime = FaultHour * 60 * 60 + FaultMin * 60 + FaultSecond; if (Insertdata.Rows.Count > 0) { UserPieChart[i].Values = new ChartValues <double> { (double)FaultTime }; } else { UserPieChart[i].Values = new ChartValues <double> { 0 }; } break; default: break; } } // 添加故障率信息 double FTime = FaultHour * 60 * 60 + FaultMin * 60 + FaultSecond; double StartTime = StartHour * 60 * 60 + StartMin * 60 + StartS; FaultValue = double.Parse((FTime / StartTime).ToString("N2")) * 100; // 添加页面下方上电信息 TimeText = $"总上电时间:{StartHour}时{StartMin}分{StartS}秒"; } // 添加当日计划生产数据 PlanNumber = $"当日计划生产数:{MainWindow.DayPlan}"; //添加生产数据 string sql = $"SELECT DevID,Produce,GoodProduct FROM MonthDeviceData WHERE Day = '{DayTime}' AND DevID = {0}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); // 判断是否有数据 if (data.Rows.Count > 0) { double Produce = double.Parse(data.Rows[0][1].ToString()); double GoodProduct = double.Parse(data.Rows[0][2].ToString()); ProduceNumber = $"{Produce}"; string prodata = (Produce / MainWindow.DayPlan).ToString("N2"); string gooddata = "0"; if (Produce > 0) { gooddata = (GoodProduct / Produce).ToString("N2"); } // 添加生产进度信息 ProduceValue = double.Parse(prodata) * 100; // 添加良率信息 GoodValue = double.Parse(gooddata) * 100; } else { // 添加生产进度信息 ProduceValue = 0; // 添加良率信息 GoodValue = 0; } // 定时刷新页面 ChangeChartValue(); }
/// <summary> /// 定时器刷新页面 /// </summary> public void ChangeChartValue() { var DayTime = DateTime.Today.ToShortDateString(); timerNotice = new System.Timers.Timer(); // 每隔一段时间触发函数 timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => { // 添加当日计划生产数据 PlanNumber = $"当日计划生产数:{MainWindow.DayPlan}"; // 添加饼状图数据 string insertsql = $"SELECT TimeName,Hour,Minute,Second FROM TimeData WHERE Day = '{DayTime}'"; //执行查询操作输出DataTable DataTable Insertdata = SQLiteHelp.ExecuteQuery(insertsql); string sql = $"SELECT DevID,Produce,GoodProduct FROM MonthDeviceData WHERE Day = '{DayTime}' AND DevID = {0}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { double Produce = double.Parse(data.Rows[0][1].ToString()); double GoodProduct = double.Parse(data.Rows[0][2].ToString()); ProduceNumber = $"{Produce}"; string prodata = (Produce / MainWindow.DayPlan).ToString("N2"); string gooddata = "0"; if (Produce > 0) { gooddata = (GoodProduct / Produce).ToString("N2"); } // 添加生产进度信息 ProduceValue = double.Parse(prodata) * 100; // 添加良率信息 GoodValue = double.Parse(gooddata) * 100; } else { // 添加生产进度信息 ProduceValue = 0; // 添加良率信息 GoodValue = 0; } // 判断是否有数据 if (Insertdata.Rows.Count > 0) { // 上电时间 时/分/秒 int StartHour = int.Parse(Insertdata.Rows[0][1].ToString()); int StartMin = int.Parse(Insertdata.Rows[0][2].ToString()); int StartS = int.Parse(Insertdata.Rows[0][3].ToString()); // 运行时间 时/分/秒 int Hour = int.Parse(Insertdata.Rows[1][1].ToString()); int Min = int.Parse(Insertdata.Rows[1][2].ToString()); int Second = int.Parse(Insertdata.Rows[1][3].ToString()); // 停机时间 时/分/秒 int StopHour = int.Parse(Insertdata.Rows[2][1].ToString()); int StopMin = int.Parse(Insertdata.Rows[2][2].ToString()); int StopSecond = int.Parse(Insertdata.Rows[2][3].ToString()); // 故障时间 时/分/秒 int FaultHour = int.Parse(Insertdata.Rows[3][1].ToString()); int FaultMin = int.Parse(Insertdata.Rows[3][2].ToString()); int FaultSecond = int.Parse(Insertdata.Rows[3][3].ToString()); // 故障时间(秒) double FTime = FaultHour * 60 * 60 + FaultMin * 60 + FaultSecond; // 上电时间(秒) double StartTime = StartHour * 60 * 60 + StartMin * 60 + StartS; // 运行时间(秒) double RunTime = Hour * 60 * 60 + Min * 60 + Second; // 停机时间(秒) double StopTime = StopHour * 60 * 60 + StopMin * 60 + StopSecond; //添加故障率数据 FaultValue = double.Parse((FTime / StartTime).ToString("N2")) * 100; //添加上电信息数据 TimeText = $"总上电时间:{StartHour}时{StartMin}分{StartS}秒"; // 添加饼状图数据 for (int i = 0; i < UserPieChart.Count; i++) { switch (i) { case 0: // 改变运行时长数据 UserPieChart[i].Values[0] = RunTime; break; case 1: // 改变停机时长数据 UserPieChart[i].Values[0] = StopTime; break; case 2: //改变停机时长数据 UserPieChart[i].Values[0] = FTime; break; default: break; } } } else { // 添加无数据时的默认数据 FaultValue = 0; TimeText = $"总上电时间:暂无数据"; } }); //触发间隔 timerNotice.Interval = MainWindow.Time; timerNotice.Start(); }
/// <summary> /// 添加故障排名前十的列表 /// </summary> public void AddFaultData(string Date) { DataTable dt = new DataTable("Fault"); dt.Columns.Add("ID", typeof(int)); dt.Columns.Add("Number", typeof(int)); dt.Columns.Add("Duration", typeof(int)); string allsql = $"SELECT * FROM DetailedFault WHERE Day = '{Date}'"; DataTable Alldata = SQLiteHelp.ExecuteQuery(allsql); // 判断是否有数据 if (Alldata.Rows.Count > 0) { var DayTime = DateTime.Today.ToShortDateString(); for (int i = 0; i < MainWindow.FaultMessageList.Length; i++) { string sql = $"SELECT ID, sum(Number),sum(Duration) FROM DetailedFault WHERE Day = '{DayTime}' AND ID = {i}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); dt.Rows.Add( data.Rows[0][0], data.Rows[0][1], data.Rows[0][2] ); } // 排序表格 DataView dv = dt.DefaultView; dv.Sort = "Number DESC"; dt = dv.ToTable(); // 取表格前几位 DataTable NewTable = DtSelectTop(10, dt); // 获取故障总数 string ratiosql = $"SELECT sum(Number) FROM DetailedFault WHERE Day = '{DayTime}'"; DataTable ToyalData = SQLiteHelp.ExecuteQuery(ratiosql); int TotalValue = 0; if (ToyalData.Rows[0][0].ToString() != "") { TotalValue = int.Parse(ToyalData.Rows[0][0].ToString()); } // 添加表格体数据 for (int i = 0; i < NewTable.Rows.Count; i++) { // 格式化时间信息 double FaultTime = double.Parse(NewTable.Rows[i][2].ToString()); string TimeFormate = $"{Math.Floor(FaultTime / 3600)}时{Math.Floor((FaultTime - (Math.Floor(FaultTime / 3600) * 3600)) / 60)}分"; // 添加表格信息 FaultList.Add(new FaultFormat() { FaultName = MainWindow.FaultMessageList[int.Parse(NewTable.Rows[i][0].ToString())], FaultNumber = NewTable.Rows[i][1].ToString(), Ratio = (double.Parse(NewTable.Rows[i][1].ToString()) / TotalValue).ToString("P2"), Time = TimeFormate, }); } } // 循环刷新 timerNotice = new System.Timers.Timer(); timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, eea) => { ChangeTableData(); }); timerNotice.Interval = MainWindow.Time * 10; timerNotice.Start(); }
/// <summary> /// 添加设备图表折线图数据 /// </summary> public void AddChartValue(int num) { //初始化长度值 DevLength.Add(num, 0); //添加初始化设备数据 ChartDataList.Add(new DayChartData()); var Daytime = DateTime.Today.ToShortDateString(); string Sql = $"SELECT Time,Produce FROM DayDeviceData WHERE Day = '{Daytime}' AND DevID = {num}"; //获取数据库数据 DataTable data = SQLiteHelp.ExecuteQuery(Sql); // 存储长度值 DevLength[num] = data.Rows.Count; if (data.Rows.Count > 0) { // 遍历读取的数据 foreach (DataRow dr in data.Rows) { int Time = int.Parse(dr["Time"].ToString()); double Produce = double.Parse(dr["Produce"].ToString()); ChartDataList[num].LineChartData.Add(new LineStatistics { Num = Time, Value = double.Parse((Produce / MainWindow.HourPlan).ToString("N3")) }); } } if (ChartDataList[0].LineChartData.Count > 20) { AxisXMax = ChartDataList[0].LineChartData.Count; AxisXMin = ChartDataList[0].LineChartData.Count - 20; } else { AxisXMax = 20; AxisXMin = 0; } // 添加设备折线图数据 UserLineCharts[num].Values = ChartDataList[num].LineChartData; timerNotice = new System.Timers.Timer(); // 每隔一段时间触发函数 timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => { var NowDaytime = DateTime.Today.ToShortDateString(); string sql = $"SELECT Time,Produce FROM DayDeviceData WHERE Day = '{NowDaytime}' AND DevID = {num}"; DataTable RunData = SQLiteHelp.ExecuteQuery(sql); // 如果运行时长超过一天,则清空图表 if (NowDaytime != Daytime) { ChartDataList[num].LineChartData.Clear(); DevLength[num] = 0; } else { if (RunData.Rows.Count > DevLength[num]) { ChartDataList[num].LineChartData.Add(new LineStatistics { Num = int.Parse(RunData.Rows[RunData.Rows.Count - 1][0].ToString()), Value = double.Parse((double.Parse(RunData.Rows[RunData.Rows.Count - 1][1].ToString()) / MainWindow.HourPlan).ToString("N3")) }); DevLength[num] = RunData.Rows.Count; } else { if (RunData.Rows.Count - 1 >= 0) { ChartDataList[num].LineChartData[RunData.Rows.Count - 1].Value = double.Parse((double.Parse(RunData.Rows[RunData.Rows.Count - 1][1].ToString()) / MainWindow.HourPlan).ToString("N3")); } } } // 判断是否点击了显示所有按钮,改变X轴显示的大小 if (IsShowAll) { AxisXMax = 24; AxisXMin = 0; } else { if (ChartDataList[0].LineChartData.Count > 20) { AxisXMax = ChartDataList[0].LineChartData.Count; AxisXMin = ChartDataList[0].LineChartData.Count; } else { AxisXMax = 20; AxisXMin = 0; } } }); //触发间隔 timerNotice.Interval = MainWindow.Time; //timerNotice.Start(); }
/// <summary> /// 刷新饼状图数据 /// </summary> /// <param name="Type"></param> public void ChangePieData(string Type) { //图表数据 var DayTime = DateTime.Today.Date.ToShortDateString(); var HourTime = DateTime.Now.Hour.ToString(); switch (Type) { case "Hour": string sql = $"SELECT FaultID,Content FROM FaultData WHERE Day = '{DayTime}' AND Time = {HourTime}"; //执行查询操作输出DataTable DataTable data = SQLiteHelp.ExecuteQuery(sql); //排序DataTable DataView dv = data.DefaultView; dv.Sort = "FaultID Asc"; DataTable hourdata = dv.ToTable(); for (int i = 0; i < UserPieChart.Count; i++) { //当当天存在数据时 if (hourdata.Rows.Count > 0) { try { // 修改数据 UserPieChart[i].Values[0] = double.Parse(hourdata.Rows[i][1].ToString()); } catch (Exception ex) { Console.WriteLine(ex); } } } break; case "Day": //图表数据 for (int i = 0; i < UserPieChart.Count; i++) { //对相同的故障类型累加 string daysql = $"SELECT FaultID,sum(Content) FROM FaultData WHERE Day = '{DayTime}' AND FaultID = {i}"; //查询数据库数据 DataTable daydata = SQLiteHelp.ExecuteQuery(daysql); // 判断是否有累加数据 if (daydata.Rows[0][1].ToString() != "") { try { //添加饼状图数据 UserPieChart[i].Values[0] = double.Parse(daydata.Rows[0][1].ToString()); } catch (Exception ex) { Console.WriteLine(ex); } } } break; case "Month": // 获取当前天数 var nowday = DateTime.Now.Day.ToString(); // 获取当前年份 var nowyear = DateTime.Now.Year.ToString(); // 获取当前月份 var nowmonth = DateTime.Now.Month.ToString(); // 添加按月统计各故障饼状图数据 for (int i = 0; i < UserPieChart.Count; i++) { double sum = 0; for (int j = 1; j < int.Parse(nowday) + 1; j++) { // 生成当前天数之前的日期 string nowtime = nowyear + "/" + nowmonth + "/" + j; string monthsql = $"SELECT Day,sum(Content) FROM FaultData WHERE Day = '{nowtime}' AND FaultID = {i}"; DataTable monthdata = SQLiteHelp.ExecuteQuery(monthsql); // 判断当月是否有数据 if (monthdata.Rows.Count > 0) { // 判断当月是否有数据累加 if (monthdata.Rows[0][1].ToString() == "") { sum += 0; } else { sum += int.Parse(monthdata.Rows[0][1].ToString()); } } else { sum += 0; } } try { // 改变数据 UserPieChart[i].Values[0] = sum; } catch (Exception ex) { Console.WriteLine(ex); } } break; default: break; } }
/// <summary> /// 改变表格数据函数 /// </summary> /// <param name="Type">天统计/月统计</param> public void ChangeTableData(string Type) { string Day = DateTime.Today.ToShortDateString(); // 只刷新天数据表格 if (Type == "Day" && DayDate == Day) { timerNotice = new System.Timers.Timer(); // 每隔一段时间触发函数 timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => { string sql = $"SELECT Time,Produce,Reach,TimeEfficiency,GoodProduct FROM DayDeviceData WHERE Day = '{DayDate}' AND DevID = {0}"; DataTable Rundata = SQLiteHelp.ExecuteQuery(sql); // 判断有数据才刷新页面 if (Rundata.Rows.Count > 0) { double Produce = double.Parse(Rundata.Rows[Rundata.Rows.Count - 1][1].ToString()); string TimeEfficiency = Rundata.Rows[Rundata.Rows.Count - 1][3].ToString(); string Reach = Rundata.Rows[Rundata.Rows.Count - 1][2].ToString(); double GoodProduct = double.Parse(Rundata.Rows[Rundata.Rows.Count - 1][4].ToString()); double GoodValue = 0; if (GoodProduct == 0 && Produce == 0) { GoodValue = 0; } else { GoodValue = GoodProduct / Produce; } // 当运行数据大于表格数据长度时,添加一个表格 if (Rundata.Rows.Count > TableLength) { ThreadPool.QueueUserWorkItem(delegate { System.Threading.SynchronizationContext.SetSynchronizationContext(new System.Windows.Threading.DispatcherSynchronizationContext(System.Windows.Application.Current.Dispatcher)); System.Threading.SynchronizationContext.Current.Post(p1 => { DevNameList.Clear(); for (int i = 0; i < Rundata.Rows.Count; i++) { double Efficiency = double.Parse(Rundata.Rows[i][2].ToString()); double GoodProduce = double.Parse(Rundata.Rows[i][4].ToString()); double NowProduce = double.Parse(Rundata.Rows[i][1].ToString()); double value = 0; if (GoodProduce == 0 && NowProduce == 0) { value = 0; } else { value = GoodProduce / NowProduce; } if (NowProduce != 0) { //添加表格信息 DevNameList.Add(new TableData() { Time = $"{Rundata.Rows[i][0]}:00", Efficiency = (Efficiency / 100).ToString("p0"), Good = (value).ToString("p0"), //Good = 1.ToString("p0"), TimeEfficiency = $"{Rundata.Rows[i][3]}%" }); } } TableLength = Rundata.Rows.Count; }, null); }); } else { if (Produce > 0) { int Hour = int.Parse(DateTime.Now.Hour.ToString()); DevNameList[DevNameList.Count - 1].Time = $"{Hour}:00"; // 修改最后一个表格数据 DevNameList[DevNameList.Count - 1].Efficiency = (double.Parse(Reach) / 100).ToString("p0"); DevNameList[DevNameList.Count - 1].Good = (GoodValue).ToString("p0"); DevNameList[DevNameList.Count - 1].TimeEfficiency = $"{TimeEfficiency}%"; } } } string allsql = $"SELECT Produce,TimeEfficiency,GoodProduct FROM MonthDeviceData WHERE Day = '{DayDate}' AND DevID = {0}"; DataTable Alldata = SQLiteHelp.ExecuteQuery(allsql); if (Alldata.Rows.Count > 0) { double Produce = double.Parse(Alldata.Rows[0][0].ToString()); double GoodProduct = double.Parse(Alldata.Rows[0][2].ToString()); double TimeEfficiency = double.Parse(Alldata.Rows[0][1].ToString()); double GoodValue = 0; if (Produce == 0 && GoodProduct == 0) { GoodValue = 0; } else { GoodValue = GoodProduct / Produce; } SummaryList[0].Title = "汇总"; SummaryList[0].Efficiency = (Produce / MainWindow.DayPlan).ToString("p0"); SummaryList[0].Good = GoodValue.ToString("p0"); SummaryList[0].TimeEfficiency = $"{TimeEfficiency}%"; } }); //触发间隔 timerNotice.Interval = MainWindow.Time; timerNotice.Start(); } }
/// <summary> /// 刷新图表数据 /// </summary> /// <param name="i">需要展示的图表数据类型</param> public void ChangeChartData(int i) { switch (i) { case 0: // 获取图表显示的折线图数据长度 int ProduceLength = ChartDataList[i].LineChartData.Count; ChartLength.Add(i, ProduceLength); break; case 1: // 获取图表显示的折线图数据长度 int GoodLength = ChartDataList[i].LineChartData.Count; ChartLength.Add(i, GoodLength); break; case 2: // 获取图表显示的折线图数据长度 int TimeLength = ChartDataList[i].LineChartData.Count; ChartLength.Add(i, TimeLength); break; default: break; } timerNotice = new System.Timers.Timer(); // 每隔一段时间触发函数 timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => { string sql = $"SELECT Time,Produce,Reach,TimeEfficiency,GoodProduct FROM DayDeviceData WHERE Day = '{Daytime}' AND DevID = {0}"; DataTable RunData = SQLiteHelp.ExecuteQuery(sql); int Hour = int.Parse(DateTime.Now.Hour.ToString()); // 如果查询有数据则运行循环函数 if (RunData.Rows.Count > 0) { double Reach = double.Parse(RunData.Rows[RunData.Rows.Count - 1][2].ToString()); double Produce = double.Parse(RunData.Rows[RunData.Rows.Count - 1][1].ToString()); double TimeEfficiency = double.Parse(RunData.Rows[RunData.Rows.Count - 1][3].ToString()); double GoodProduct = double.Parse(RunData.Rows[RunData.Rows.Count - 1][4].ToString()); // 如果图表数据长度与数据库数据长度不符,则清空图表 if (RunData.Rows.Count != ChartDataList[i].LineChartData.Count) { ChartDataList[i].LineChartData.Clear(); switch (i) { case 0: for (int num = 0; num < RunData.Rows.Count; num++) { // 添加达成率图表数据 ChartDataList[i].LineChartData.Add(new LineStatistics { Num = num, Value = double.Parse((double.Parse(RunData.Rows[num][2].ToString()) / 100).ToString()) }); } break; case 1: for (int num = 0; num < RunData.Rows.Count; num++) { // 添加良率图表数据 if (double.Parse(RunData.Rows[num][4].ToString()) == 0 && double.Parse(RunData.Rows[num][1].ToString()) == 0) { // 如果生产数和良品数都为0,则将良率设为0 ChartDataList[i].LineChartData.Add(new LineStatistics { Num = num, Value = 0.0 }); } else { // 添加良率数据 ChartDataList[i].LineChartData.Add(new LineStatistics { Num = num, Value = double.Parse((double.Parse(RunData.Rows[num][4].ToString()) / double.Parse(RunData.Rows[num][1].ToString())).ToString("N2")) }); } } break; case 2: for (int num = 0; num < RunData.Rows.Count; num++) { // 添加时间稼动率图表数据 ChartDataList[i].LineChartData.Add(new LineStatistics { Num = num, Value = double.Parse((double.Parse(RunData.Rows[num][3].ToString()) / 100).ToString()) }); } break; default: break; } } else { // 修改最后一个节点数据 switch (i) { case 0: ChartDataList[i].LineChartData[ChartDataList[i].LineChartData.Count - 1].Value = double.Parse((Reach / 100).ToString()); break; case 1: if (GoodProduct == 0 && Produce == 0) { ChartDataList[i].LineChartData[ChartDataList[i].LineChartData.Count - 1].Value = 0; } else { ChartDataList[i].LineChartData[ChartDataList[i].LineChartData.Count - 1].Value = double.Parse((GoodProduct / Produce).ToString("N2")); } break; case 2: ChartDataList[i].LineChartData[ChartDataList[i].LineChartData.Count - 1].Value = double.Parse((TimeEfficiency / 100).ToString()); break; default: break; } } } }); //触发间隔 timerNotice.Interval = MainWindow.Time; timerNotice.Start(); }
/// <summary> /// 获取故障数据 /// </summary> /// <param name="NowDay">今天</param> /// <param name="Hour">当前小时</param> /// <param name="yesterday">昨天</param> public static void GetFault(string NowDay, int Hour, string yesterday) { for (int i = 0; i < MainWindow.FaultList.Length; i++) { // 获取前一天8点到24点的数据和 string yesterdaySql = $"SELECT sum(Content) FROM FaultData WHERE Day = '{yesterday}' AND FaultID = {i} AND Time > {8}"; DataTable YesterDay = SQLiteHelp.ExecuteQuery(yesterdaySql); // 有数据 if (YesterDay.Rows[0][0].ToString() != "") { switch (i) { case 0: YesterDayAFault = int.Parse(YesterDay.Rows[0][0].ToString()); break; case 1: YesterDayBFault = int.Parse(YesterDay.Rows[0][0].ToString()); break; case 2: YesterDayCFault = int.Parse(YesterDay.Rows[0][0].ToString()); break; case 3: YesterDayDFault = int.Parse(YesterDay.Rows[0][0].ToString()); break; case 4: YesterDayEFault = int.Parse(YesterDay.Rows[0][0].ToString()); break; default: break; } } else { switch (i) { case 0: YesterDayAFault = 0; break; case 1: YesterDayBFault = 0; break; case 2: YesterDayCFault = 0; break; case 3: YesterDayDFault = 0; break; case 4: YesterDayEFault = 0; break; default: break; } } // 获取当前小时之前的数据 string BeforeSql = $"SELECT sum(Content) FROM FaultData WHERE Day = '{NowDay}' AND FaultID = {i} AND Time < {Hour}"; DataTable BeforeData = SQLiteHelp.ExecuteQuery(BeforeSql); // 判断是否存在数据 if (BeforeData.Rows[0][0].ToString() != "") { switch (i) { case 0: AFaultSum = int.Parse(BeforeData.Rows[0][0].ToString()); break; case 1: BFaultSum = int.Parse(BeforeData.Rows[0][0].ToString()); break; case 2: CFaultSum = int.Parse(BeforeData.Rows[0][0].ToString()); break; case 3: DFaultSum = int.Parse(BeforeData.Rows[0][0].ToString()); break; case 4: EFaultSum = int.Parse(BeforeData.Rows[0][0].ToString()); break; default: break; } } else { switch (i) { case 0: AFaultSum = 0; break; case 1: BFaultSum = 0; break; case 2: CFaultSum = 0; break; case 3: DFaultSum = 0; break; case 4: EFaultSum = 0; break; default: break; } } // 获取当前天当前小时前一个小时到当天8点的数据 string TodaySql = $"SELECT sum(Content) FROM FaultData WHERE Day = '{NowDay}' AND FaultID = {i} AND Time >= {8} AND Time <{Hour}"; DataTable ToDayData = SQLiteHelp.ExecuteQuery(TodaySql); if (ToDayData.Rows[0][0].ToString() != "") { switch (i) { case 0: ToDayAFaultData = int.Parse(ToDayData.Rows[0][0].ToString()); break; case 1: ToDayBFaultData = int.Parse(ToDayData.Rows[0][0].ToString()); break; case 2: ToDayCFaultData = int.Parse(ToDayData.Rows[0][0].ToString()); break; case 3: ToDayDFaultData = int.Parse(ToDayData.Rows[0][0].ToString()); break; case 4: ToDayEFaultData = int.Parse(ToDayData.Rows[0][0].ToString()); break; default: break; } } else { switch (i) { case 0: ToDayAFaultData = 0; break; case 1: ToDayBFaultData = 0; break; case 2: ToDayCFaultData = 0; break; case 3: ToDayDFaultData = 0; break; case 4: ToDayEFaultData = 0; break; default: break; } } // 获取当天的生产的总数据 string Todaysumsql = $"SELECT sum(Content) FROM FaultData WHERE Day = '{NowDay}' AND FaultID = {i}"; DataTable TodaySumData = SQLiteHelp.ExecuteQuery(Todaysumsql); if (TodaySumData.Rows[0][0].ToString() != "") { switch (i) { case 0: ToDayAFault = int.Parse(TodaySumData.Rows[0][0].ToString()); break; case 1: ToDayBFault = int.Parse(TodaySumData.Rows[0][0].ToString()); break; case 2: ToDayCFault = int.Parse(TodaySumData.Rows[0][0].ToString()); break; case 3: ToDayDFault = int.Parse(TodaySumData.Rows[0][0].ToString()); break; case 4: ToDayEFault = int.Parse(TodaySumData.Rows[0][0].ToString()); break; default: break; } } else { switch (i) { case 0: ToDayAFault = 0; break; case 1: ToDayBFault = 0; break; case 2: ToDayCFault = 0; break; case 3: ToDayDFault = 0; break; case 4: ToDayEFault = 0; break; default: break; } } } }
/// <summary> /// 添加设备图表折线图数据 /// </summary> public GearedValues <LineStatistics> AddChartValue(int num) { // 定义折线图数据结构 GearedValues <LineStatistics> LineChartData = new GearedValues <LineStatistics>(); // 获取当前天数 var nowday = DateTime.Now.Day.ToString(); // 获取当前年份 var nowyear = DateTime.Now.Year.ToString(); // 获取当前月份 var nowmonth = DateTime.Now.Month.ToString(); if (UserLineCharts[0].Values.Count > 20) { AxisXMax = UserLineCharts[0].Values.Count - 1; AxisXMin = UserLineCharts[0].Values.Count - 20; } else { AxisXMax = 20; AxisXMin = 0; } for (int i = 1; i < int.Parse(nowday) + 1; i++) { // 生成当前天数之前的日期 如果有日期没有数据,则添加图表数据为0 string nowtime = nowyear + "/" + nowmonth + "/" + i; // 查询数据库 string sql = $"SELECT DevID,Produce FROM MonthDeviceData WHERE Day = '{nowtime}' AND DevID = {num}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { if (data.Rows[0][1].ToString() == "") { LineChartData.Add(new LineStatistics { Num = i - 1, Value = 0 }); } else { LineChartData.Add(new LineStatistics { Num = i - 1, Value = double.Parse((double.Parse(data.Rows[0][1].ToString()) / MainWindow.DayPlan).ToString("N3")) }); } } else { LineChartData.Add(new LineStatistics { Num = i - 1, Value = 0 }); } } timerNotice = new System.Timers.Timer(); //间隔触发函数 timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => { }); //触发间隔 timerNotice.Interval = MainWindow.Time; timerNotice.Start(); return(LineChartData); }
/// <summary> /// 改变表格函数 /// </summary> public void ChangeTableData() { var DayTime = DateTime.Today.ToShortDateString(); // 添加表格列,排序用 DataTable dt = new DataTable("Fault"); dt.Columns.Add("ID", typeof(int)); dt.Columns.Add("Number", typeof(int)); dt.Columns.Add("Duration", typeof(int)); string allsql = $"SELECT * FROM DetailedFault WHERE Day = '{DayTime}'"; DataTable Alldata = SQLiteHelp.ExecuteQuery(allsql); // 判断是否有数据 if (Alldata.Rows.Count > 0) { for (int i = 0; i < MainWindow.FaultMessageList.Length; i++) { string sql = $"SELECT ID, sum(Number),sum(Duration) FROM DetailedFault WHERE Day = '{DayTime}' AND ID = {i}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); dt.Rows.Add( data.Rows[0][0], data.Rows[0][1], data.Rows[0][2] ); } // 排序表格 DataView dv = dt.DefaultView; dv.Sort = "Number DESC"; dt = dv.ToTable(); // 取表格前几位 DataTable NewTable = DtSelectTop(10, dt); // 获取故障总数 string ratiosql = $"SELECT sum(Number) FROM DetailedFault WHERE Day = '{DayTime}'"; DataTable ToyalData = SQLiteHelp.ExecuteQuery(ratiosql); int TotalValue = 0; if (ToyalData.Rows[0][0].ToString() != "") { TotalValue = int.Parse(ToyalData.Rows[0][0].ToString()); } // 添加表格体数据 for (int i = 0; i < NewTable.Rows.Count; i++) { // 格式化时间信息 double FaultTime = double.Parse(NewTable.Rows[i][2].ToString()); string TimeFormate = $"{Math.Floor(FaultTime / 3600)}时{Math.Floor((FaultTime - (Math.Floor(FaultTime / 3600) * 3600)) / 60)}分"; // 修改表格信息 FaultList[i].FaultName = MainWindow.FaultMessageList[int.Parse(NewTable.Rows[i][0].ToString())]; FaultList[i].FaultNumber = NewTable.Rows[i][1].ToString(); FaultList[i].Ratio = (double.Parse(NewTable.Rows[i][1].ToString()) / TotalValue).ToString("P2"); FaultList[i].Time = TimeFormate; } OnPropertyChanged(nameof(FaultList)); } }
/// <summary> /// 添加设备信息数据 /// </summary> public void InitProductionList() { // 获取时间 var DayTime = DateTime.Today.Date.ToShortDateString(); var Hour = DateTime.Now.Hour.ToString(); // 初始化设备信息 EquipmentList = new ObservableCollection <PList>(); // 获取当前设备编号列表 string devlidtsql = "SELECT ID FROM Equipment"; DataTable devdata = SQLiteHelp.ExecuteQuery(devlidtsql); var DevNumberList = SQLiteHelp.NumberList(devdata, "ID"); // 生成所有数据 for (int i = 0; i < DevNumberList.Count; i++) { //初始化数据 DevProduceList.Add(0); //创建查询数据库语句 string Sql = "SELECT DevID,sum(Produce),sum(Electric),sum(Gas),sum(GoodProduct) " + "FROM DayDeviceData " + $" WHERE DevID = {i} AND Day = '{DayTime}'"; DataTable data = SQLiteHelp.ExecuteQuery(Sql); string timesql = $"SELECT TimeEfficiency FROM DayDeviceData WHERE DevID = {i} AND Day = '{DayTime}' AND Time = {Hour}"; DataTable timedata = SQLiteHelp.ExecuteQuery(timesql); string timeefficiency = "0"; // 判断当前时间是否有稼动率数据 if (timedata.Rows.Count > 0) { timeefficiency = timedata.Rows[0][0].ToString(); } // 判断是否有数据 if (data.Rows[0][1].ToString() != "") { // 添加初始化页面显示设备详细数据 (当天数据) EquipmentList.Add(new PList() { EquTitle = MainWindow.DeviceList[i], ProductionSum = data.Rows[0][1].ToString(), Reach = (double.Parse(data.Rows[0][1].ToString()) / MainWindow.DayPlan).ToString("p2"), Efficiency = (double.Parse(data.Rows[0][1].ToString()) / MainWindow.DayPlan).ToString("p2"), TimeEfficiency = $"{timeefficiency}%", Electricity = data.Rows[0][2].ToString(), Gas = data.Rows[0][3].ToString(), //GoodProduce = (double.Parse(data.Rows[0][4].ToString()) / double.Parse(data.Rows[0][1].ToString())).ToString("p2"), //GoodSum = data.Rows[0][4].ToString() GoodProduce = (1).ToString("p2"), GoodSum = data.Rows[0][1].ToString() }); DevProduceList[i] = int.Parse(data.Rows[0][1].ToString()); } else { // 添加初始化页面显示设备详细数据 (当天数据) EquipmentList.Add(new PList() { EquTitle = MainWindow.DeviceList[i], ProductionSum = 0.ToString(), Reach = (0).ToString("p2"), Efficiency = (0).ToString("p2"), TimeEfficiency = (0).ToString("p0"), Electricity = 0.ToString(), Gas = 0.ToString(), GoodProduce = (1).ToString("p2"), GoodSum = 0.ToString() }); DevProduceList[i] = 0; } } timerNotice = new System.Timers.Timer(); //间隔触发函数 timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => { string sql = "SELECT DevID FROM DayDeviceData"; DataTable data = SQLiteHelp.ExecuteQuery(sql); //获取当前设备编号列表 var NowNumberList = SQLiteHelp.NumberList(data, "DevID"); var NowDayTime = DateTime.Today.Date.ToShortDateString(); var NowHour = DateTime.Now.Hour.ToString(); for (int i = 0; i < NowNumberList.Count; i++) { //创建查询数据库语句 string runsql = "SELECT DevID,sum(Produce),sum(Electric),sum(Gas),sum(GoodProduct) " + "FROM DayDeviceData " + $" WHERE DevID = {i} AND Day = '{NowDayTime}'"; DataTable rundata = SQLiteHelp.ExecuteQuery(runsql); string timesql = $"SELECT TimeEfficiency FROM DayDeviceData WHERE DevID = {i} AND Day = '{NowDayTime}' AND Time = {NowHour}"; DataTable timedata = SQLiteHelp.ExecuteQuery(timesql); string timeefficiency = "0"; // 判断当前时间是否有稼动率数据 if (timedata.Rows.Count > 0) { timeefficiency = timedata.Rows[0][0].ToString(); } // 判断当前时间是否有数据,和上一次读取的数据是否相同 if (rundata.Rows[0][1].ToString() != "" && int.Parse(rundata.Rows[0][1].ToString()) != DevProduceList[i]) { EquipmentList[i].ProductionSum = rundata.Rows[0][1].ToString(); EquipmentList[i].Reach = (double.Parse(rundata.Rows[0][1].ToString()) / MainWindow.DayPlan).ToString("p2"); EquipmentList[i].Efficiency = (double.Parse(rundata.Rows[0][1].ToString()) / MainWindow.DayPlan).ToString("p2"); EquipmentList[i].TimeEfficiency = $"{timeefficiency}%"; EquipmentList[i].Electricity = rundata.Rows[0][2].ToString(); EquipmentList[i].Gas = rundata.Rows[0][3].ToString(); //EquipmentList[i].GoodProduce = (double.Parse(rundata.Rows[0][4].ToString()) / double.Parse(rundata.Rows[0][1].ToString())).ToString("p2"); //EquipmentList[i].GoodSum = rundata.Rows[0][4].ToString(); EquipmentList[i].GoodProduce = (1).ToString("p2"); EquipmentList[i].GoodSum = rundata.Rows[0][1].ToString(); } } }); //触发间隔 timerNotice.Interval = MainWindow.Time; timerNotice.Start(); }
/// <summary> /// 添加折线图数据 /// </summary> /// <param name="i">需要展示的折线图类型</param> public void AddChartValue(int i) { // 添加初始化数据格式 ChartDataList.Add(new DayChartData()); switch (i) { case 0: // 添加达成率图表信息 string Sql = $"SELECT Time,Reach FROM DayDeviceData WHERE Day = '{Daytime}' AND DevID = {0}"; //获取数据库数据 DataTable data = SQLiteHelp.ExecuteQuery(Sql); if (data.Rows.Count > 0) { // 遍历读取的数据 foreach (DataRow dr in data.Rows) { int Time = int.Parse(dr["Time"].ToString()); // 小时 double Reach = double.Parse(dr["Reach"].ToString()); // 生产总数 // 添加折线图数据 ChartDataList[0].LineChartData.Add(new LineStatistics { Num = Time, Value = double.Parse((Reach / 100).ToString("N2")) }); } } UserLineCharts[0].Values = ChartDataList[0].LineChartData; break; case 1: // 添加良率图表信息 string goodSql = $"SELECT Time,Produce,GoodProduct FROM DayDeviceData WHERE Day = '{Daytime}' AND DevID = {0}"; //获取数据库数据 DataTable Gooddata = SQLiteHelp.ExecuteQuery(goodSql); if (Gooddata.Rows.Count > 0) { // 遍历读取的数据 foreach (DataRow dr in Gooddata.Rows) { int Time = int.Parse(dr["Time"].ToString()); // 小时 double Produce = double.Parse(dr["Produce"].ToString()); // 生产总数 double GoodProduct = double.Parse(dr["GoodProduct"].ToString()); // 良品数 double Good; // 良率 if (Produce == 0 && GoodProduct == 0) { // 如果当天产量和良品数都为0,则将良率设为0 Good = 0; } else { Good = GoodProduct / Produce; } // 添加折线图数据 ChartDataList[1].LineChartData.Add(new LineStatistics { Num = Time, Value = double.Parse(Good.ToString("N2")) }); } } UserLineCharts[1].Values = ChartDataList[1].LineChartData; break; case 2: // 添加时间稼动率信息 string timeSql = $"SELECT Time,TimeEfficiency FROM DayDeviceData WHERE Day = '{Daytime}' AND DevID = {0}"; //获取数据库数据 DataTable Timedata = SQLiteHelp.ExecuteQuery(timeSql); if (Timedata.Rows.Count > 0) { // 遍历读取的数据 foreach (DataRow dr in Timedata.Rows) { int Time = int.Parse(dr["Time"].ToString()); // 小时 double TimeEfficiency = double.Parse(dr["TimeEfficiency"].ToString()); // 时间稼动率 // 添加折线图数据 ChartDataList[i].LineChartData.Add(new LineStatistics { Num = Time, Value = double.Parse((TimeEfficiency / 100).ToString()) }); } } UserLineCharts[i].Values = ChartDataList[i].LineChartData; break; default: break; } ChangeChartData(i); }
/// <summary> /// 添加对应ID图表数据 /// </summary> /// <param name="ID">设备编号</param> public void AddBarChartValue(int ID) { //初始化长度值 DevLength.Add(ID, 0); //添加初始化设备数据 ChartDataList.Add(new DayChartData()); string Sql = $"SELECT Time,Produce FROM DayDeviceData WHERE Day = '{Daytime}' AND DevID = {ID}"; //获取数据库数据 DataTable data = SQLiteHelp.ExecuteQuery(Sql); // 存储长度值 DevLength[ID] = data.Rows.Count; // 遍历读取的数据 foreach (DataRow dr in data.Rows) { int Time = int.Parse(dr["Time"].ToString()); double Produce = double.Parse(dr["Produce"].ToString()); ChartDataList[ID].LineChartData.Add(new BarStatistics { Num = Time, Value = (double)Produce }); } // 当数据图表长度大于20时,修改X轴 if (ChartDataList[0].LineChartData.Count > 20) { AxisXMax = ChartDataList[0].LineChartData.Count + 1; AxisXMin = ChartDataList[0].LineChartData.Count - 20; } else { AxisXMax = 21; AxisXMin = 0; } // 添加设备折线图数据 UserBarCharts[ID].Values = ChartDataList[ID].LineChartData; timerNotice = new System.Timers.Timer(); // 每隔一段时间触发函数 timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => { string sql = $"SELECT Time,Produce FROM DayDeviceData WHERE Day = '{Daytime}' AND DevID = {ID}"; DataTable RunData = SQLiteHelp.ExecuteQuery(sql); // 如果运行时长超过一天,则清空图表 if (Daytime == "") { ChartDataList[ID].LineChartData.Clear(); DevLength[ID] = 0; } else { // 如果查询出的数据长度大于图表数据长度,则添加一个节点 if (RunData.Rows.Count > DevLength[ID]) { ChartDataList[ID].LineChartData.Add(new BarStatistics { Num = int.Parse(RunData.Rows[RunData.Rows.Count - 1][0].ToString()), Value = double.Parse(RunData.Rows[RunData.Rows.Count - 1][1].ToString()) }); DevLength[ID] = RunData.Rows.Count; } else { // 修改图表数据最后一个节点数据 if (RunData.Rows.Count - 1 >= 0) { ChartDataList[ID].LineChartData[RunData.Rows.Count - 1].Value = double.Parse(RunData.Rows[RunData.Rows.Count - 1][1].ToString()); } } } // 判断是否点击了显示所有按钮,改变X轴显示的大小 if (IsShowAll) { AxisXMax = 24; AxisXMin = 0; } else { if (ChartDataList[0].LineChartData.Count > 20) { AxisXMax = ChartDataList[0].LineChartData.Count + 1; AxisXMin = ChartDataList[0].LineChartData.Count - 20; } else { AxisXMax = 21; AxisXMin = 0; } } }); //触发间隔 timerNotice.Interval = MainWindow.Time; timerNotice.Start(); }
/// <summary> /// 添加设备图表折线图数据 /// </summary> public GearedValues <LineStatistics> AddChartValue(int num) { // 定义折线图数据结构 GearedValues <LineStatistics> LineChartData = new GearedValues <LineStatistics>(); // 获取当前天数 var nowday = DateTime.Now.Day.ToString(); // 获取当前年份 var nowyear = DateTime.Now.Year.ToString(); // 获取当前月份 var nowmonth = DateTime.Now.Month.ToString(); if (UserLineCharts[0].Values.Count > 20) { AxisXMax = UserLineCharts[0].Values.Count - 1; AxisXMin = UserLineCharts[0].Values.Count - 20; } else { AxisXMax = 20; AxisXMin = 0; } switch (num) { case 0: //添加月达成率图表 for (int i = 1; i < int.Parse(nowday) + 1; i++) { // 生成当前天数之前的日期 如果有日期没有数据,则添加图表数据为0 string nowtime = nowyear + "/" + nowmonth + "/" + i; // 查询数据库 string sql = $"SELECT DevID,Produce FROM MonthDeviceData WHERE Day = '{nowtime}' AND DevID = {num}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { if (data.Rows[0][1].ToString() == "") { LineChartData.Add(new LineStatistics { Num = i - 1, Value = 0 }); } else { LineChartData.Add(new LineStatistics { Num = i - 1, Value = double.Parse((double.Parse(data.Rows[0][1].ToString()) / MainWindow.DayPlan).ToString("N3")) }); } } else { LineChartData.Add(new LineStatistics { Num = i - 1, Value = 0 }); } } break; case 1: // 添加月良率图表 for (int i = 1; i < int.Parse(nowday) + 1; i++) { // 生成当前天数之前的日期 如果有日期没有数据,则添加图表数据为0 string nowtime = nowyear + "/" + nowmonth + "/" + i; // 查询数据库 string sql = $"SELECT DevID,Produce,GoodProduct FROM MonthDeviceData WHERE Day = '{nowtime}' AND DevID = {0}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { if (data.Rows[0][1].ToString() == "") { LineChartData.Add(new LineStatistics { Num = i - 1, Value = 0 }); } else { double good = double.Parse(data.Rows[0][2].ToString()) / double.Parse(data.Rows[0][1].ToString()); LineChartData.Add(new LineStatistics { Num = i - 1, Value = double.Parse(good.ToString("N2")) }); } } else { LineChartData.Add(new LineStatistics { Num = i - 1, Value = 0 }); } } break; case 2: // 添加月时间稼动率图表 for (int i = 1; i < int.Parse(nowday) + 1; i++) { // 生成当前天数之前的日期 如果有日期没有数据,则添加图表数据为0 string nowtime = nowyear + "/" + nowmonth + "/" + i; // 查询数据库 string sql = $"SELECT DevID,TimeEfficiency FROM MonthDeviceData WHERE Day = '{nowtime}' AND DevID = {0}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { if (data.Rows[0][1].ToString() == "") { LineChartData.Add(new LineStatistics { Num = i - 1, Value = 0 }); } else { double time = double.Parse(data.Rows[0][1].ToString()); LineChartData.Add(new LineStatistics { Num = i - 1, Value = double.Parse((time / 100).ToString("N2")) }); } } else { LineChartData.Add(new LineStatistics { Num = i - 1, Value = 0 }); } } break; default: break; } return(LineChartData); }
/// <summary> /// 添加设备告警信息 /// </summary> private void Init() { UserWarningList = new ObservableCollection <UserModel>(); //添加设备告警信息 var Daytime = DateTime.Today.Date.ToShortDateString(); //创建查询数据库语句 string sql = $"SELECT * FROM WarningData WHERE Day = '{Daytime}'"; //执行查询操作 DataTable dt = SQLiteHelp.ExecuteQuery(sql); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { string time = dr["Time"].ToString(); //添加设备日志信息 UserWarningList.Add(new UserModel() { Time = $"{time}:00", Device = MainWindow.DeviceList[int.Parse(dr["DevID"].ToString())], Content = $"{MainWindow.WarningTypeList[int.Parse(dr["Content"].ToString())]},完成{double.Parse(dr["ReachRate"].ToString()).ToString("p0")}" }); } } WarningLength = dt.Rows.Count; // 记录初始告警列表长度 timerNotice = new System.Timers.Timer(); //间隔触发函数 timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, e) => { //创建查询数据库语句 string runsql = $"SELECT * FROM WarningData WHERE Day = '{Daytime}'"; //执行查询操作 DataTable rundt = SQLiteHelp.ExecuteQuery(sql); if (rundt.Rows.Count == 0) { WarningLength = 0; } else if (rundt.Rows.Count > WarningLength) { for (int i = 0; i < rundt.Rows.Count - WarningLength; i++) { // 获取数据库中新增加的数据信息 var runtime = rundt.Rows[rundt.Rows.Count - i - 1][1].ToString(); //时间 var rundevname = rundt.Rows[rundt.Rows.Count - i - 1][2].ToString(); //设备名 var runcontent = rundt.Rows[rundt.Rows.Count - i - 1][3].ToString(); //告警内容 var runreachrate = rundt.Rows[rundt.Rows.Count - i - 1][4].ToString(); //完成度 ThreadPool.QueueUserWorkItem(delegate { System.Threading.SynchronizationContext.SetSynchronizationContext(new System.Windows.Threading.DispatcherSynchronizationContext(System.Windows.Application.Current.Dispatcher)); System.Threading.SynchronizationContext.Current.Post(p1 => { //添加设备日志信息 UserWarningList.Add(new UserModel() { Time = $"{runtime}:00", Device = MainWindow.DeviceList[int.Parse(rundevname)], //Content = MainWindow.WarningTypeList[int.Parse(runcontent)] Content = $"{MainWindow.WarningTypeList[int.Parse(runcontent)]},完成{double.Parse(runreachrate).ToString("p0")}" }); }, null); }); } WarningLength = rundt.Rows.Count; } }); //触发间隔 timerNotice.Interval = MainWindow.Time; timerNotice.Start(); }
/// <summary> /// 添加表格数据 /// </summary> /// <param name="Type">天统计/月统计</param> public void AddDataTableData(string Type) { var Daytime = DateTime.Today.ToShortDateString(); // 获取当前天数 var nowday = DateTime.Now.Day.ToString(); // 获取当前年份 var nowyear = DateTime.Now.Year.ToString(); // 获取当前月份 var nowmonth = DateTime.Now.Month.ToString(); switch (Type) { case "Day": string sql = $"SELECT Time,Produce,Reach,TimeEfficiency,GoodProduct FROM DayDeviceData WHERE Day = '{DayDate}' AND DevID = {0}"; DataTable data = SQLiteHelp.ExecuteQuery(sql); if (data.Rows.Count > 0) { TableLength = data.Rows.Count; for (var i = 0; i < data.Rows.Count; i++) { double Produce = double.Parse(data.Rows[i][1].ToString()); double GoodProduct = double.Parse(data.Rows[i][4].ToString()); double GoodValue = 0; if (Produce == 0 && GoodProduct == 0) { GoodValue = 0; } else { GoodValue = GoodProduct / Produce; } if (Produce != 0) { DevNameList.Add(new TableData() { Time = $"{data.Rows[i][0]}:00", Efficiency = (double.Parse(data.Rows[i][2].ToString()) / 100).ToString("p0"), Good = (GoodValue).ToString("p0"), //Good = 1.ToString("p0"), TimeEfficiency = $"{data.Rows[i][3].ToString()}%" }); } } } break; case "Month": for (int i = 1; i < int.Parse(nowday) + 1; i++) { // 生成当前天数之前的日期 如果有日期没有数据,则添加图表数据为0 string nowtime = nowyear + "/" + nowmonth + "/" + i; // 查询数据库 string monthsql = $"SELECT Produce,TimeEfficiency,GoodProduct FROM MonthDeviceData WHERE Day = '{nowtime}' AND DevID = {0}"; DataTable Monthdata = SQLiteHelp.ExecuteQuery(monthsql); if (Monthdata.Rows.Count > 0) { double Produce = double.Parse(Monthdata.Rows[0][0].ToString()); double GoodProduct = double.Parse(Monthdata.Rows[0][2].ToString()); double GoodValue = 0; if (Produce == 0 && GoodProduct == 0) { GoodValue = 0; } else { GoodValue = GoodProduct / Produce; } if (Monthdata.Rows[0][1].ToString() != "") { DevNameList.Add(new TableData() { Time = $"{nowmonth}月{i}日", Efficiency = (double.Parse(Monthdata.Rows[0][0].ToString()) / MainWindow.DayPlan).ToString("p0"), Good = GoodValue.ToString("p0"), TimeEfficiency = $"{Monthdata.Rows[0][1].ToString()}%" }); } } //else //{ // DevNameList.Add(new TableData() // { // Time = $"{nowmonth}月{i}日", // Efficiency = 0.ToString("p0"), // //Good = data.Rows[i][3].ToString(), // Good = 0.ToString("p0"), // TimeEfficiency = $"{0}%" // }); //} } break; default: break; } string allsql = $"SELECT Produce,TimeEfficiency,GoodProduct FROM MonthDeviceData WHERE Day = '{Daytime}' AND DevID = {0}"; DataTable Alldata = SQLiteHelp.ExecuteQuery(allsql); if (Alldata.Rows.Count > 0) { double Produce = double.Parse(Alldata.Rows[0][0].ToString()); double GoodProduct = double.Parse(Alldata.Rows[0][2].ToString()); double TimeEfficiency = double.Parse(Alldata.Rows[0][1].ToString()); double GoodValue = 0; if (Produce == 0 && GoodProduct == 0) { GoodValue = 0; } else { GoodValue = GoodProduct / Produce; } SummaryList.Add(new Summary() { Title = "汇总", Efficiency = (Produce / MainWindow.DayPlan).ToString("p0"), Good = GoodValue.ToString("p0"), TimeEfficiency = $"{TimeEfficiency}%" }); } else { SummaryList.Add(new Summary() { Title = "汇总", Efficiency = (0).ToString("p0"), Good = 0.ToString("p0"), TimeEfficiency = "0%" }); } // 定时刷新表格页面 ChangeTableData(Type); }
/// <summary> /// 获取图表显示数据 /// </summary> /// <param name="Type"></param> public void GetPieChartData(string Type, string Position) { // 图表标签显示位置 switch (Position) { case "Bottom": UserLegendLocation = "Bottom"; break; case "Right": UserLegendLocation = "Right"; break; default: UserLegendLocation = "Bottom"; break; } //时间 var DayTime = DateTime.Today.Date.ToShortDateString(); var HourTime = DateTime.Now.Hour.ToString(); // 添加图表数据 switch (Type) { case "Hour": string sql = $"SELECT FaultID,Content FROM FaultData WHERE Day = '{DayTime}' AND Time = {HourTime}"; //执行查询操作输出DataTable DataTable data = SQLiteHelp.ExecuteQuery(sql); //排序DataTable DataView dv = data.DefaultView; dv.Sort = "FaultID Asc"; DataTable hourdata = dv.ToTable(); // 添加饼状图数据 for (int i = 0; i < UserPieChart.Count; i++) { //当当天存在数据时 if (hourdata.Rows.Count > 0) { UserPieChart[i].Values = new ChartValues <double> { double.Parse(hourdata.Rows[i][1].ToString()) }; } else { UserPieChart[i].Values = new ChartValues <double>() { 0 }; } } // 循环函数(刷新数据) timerNotice = new System.Timers.Timer(); timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, eea) => { // 刷新图表数据 ChangePieData("Hour"); }); timerNotice.Interval = MainWindow.Time; timerNotice.Start(); break; case "Day": //图表数据 for (int i = 0; i < UserPieChart.Count; i++) { //对相同的故障类型累加 string daysql = $"SELECT FaultID,sum(Content) FROM FaultData WHERE Day = '{DayTime}' AND FaultID = {i}"; //查询数据库数据 DataTable daydata = SQLiteHelp.ExecuteQuery(daysql); if (daydata.Rows[0][1].ToString() != "") { //添加饼状图数据 UserPieChart[i].Values = new ChartValues <double> { double.Parse(daydata.Rows[0][1].ToString()) }; } else { //添加饼状图数据 UserPieChart[i].Values = new ChartValues <double> { 0 }; } } // 循环刷新 timerNotice = new System.Timers.Timer(); timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, eea) => { // 刷新图表数据 ChangePieData("Day"); }); timerNotice.Interval = MainWindow.Time; timerNotice.Start(); break; case "Month": // 获取当前天数 var nowday = DateTime.Now.Day.ToString(); // 获取当前年份 var nowyear = DateTime.Now.Year.ToString(); // 获取当前月份 var nowmonth = DateTime.Now.Month.ToString(); // 添加按月统计各故障饼状图数据 for (int i = 0; i < UserPieChart.Count; i++) { int sum = 0; // 循环当前的天数,累加得到当月数据 for (int j = 1; j < int.Parse(nowday) + 1; j++) { // 生成当前天数之前的日期 string nowtime = nowyear + "/" + nowmonth + "/" + j; string monthsql = $"SELECT Day,sum(Content) FROM FaultData WHERE Day = '{nowtime}' AND FaultID = {i}"; DataTable monthdata = SQLiteHelp.ExecuteQuery(monthsql); // 判断是否有数据 if (monthdata.Rows.Count > 0) { // 判断是否有数据累加 (无数据但有累加值时,读出的DataTable长度为1) if (monthdata.Rows[0][1].ToString() == "") { sum += 0; } else { sum += int.Parse(monthdata.Rows[0][1].ToString()); } } else { sum += 0; } } UserPieChart[i].Values = new ChartValues <double> { sum }; } // 循环刷新 timerNotice = new System.Timers.Timer(); timerNotice.Elapsed += new System.Timers.ElapsedEventHandler((o, eea) => { // 刷新图表数据 ChangePieData("Month"); }); timerNotice.Interval = MainWindow.Time; timerNotice.Start(); break; default: break; } }