叕叕叕到周五了，时间总是走的如此之快，不免伤感(- -)。。。(伤感个毛线呀，再伤感就走了)12月就要结束了，赶紧来一篇充实一下生活。最近在项目中，做了个图表程序，使用到了Chart,今天在这里做一个整理总结。
1.第一个Chart控件
1)先来熟悉一下chart,在前端做一个图表可能会用到chart.js，在C#中可以用自带的控件chart，感觉挺方便的。
2)创建一个项目，windows窗体应用程序。在工具箱的【数据】找到【 Chart】控件，并拖到窗体
3)右键chart【属性】，在VS右侧属性【布局】下面找到【Dock】属性设置为Fill,自己再调整一下大小
4)这里的操作是当加载窗体的时候显示chart,所以有个窗体load事件。
5)双击后直接进入代码，当在代码中写Series时会出现红色波浪线，提示缺少相关命名空间之类的，点击【Series】就可以看到所需要的，添加就ok了
6)代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40using System;
using System.Collections.Generic;
using ponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;
namespace MyChart
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//清除默认的series
chart1.Series.Clear();
//new 一个叫做【Strength】的系列
Series Strength = new Series("力量");
//设置chart的类型，这里为柱状图
Strength.ChartType = SeriesChartType.Column;
//给系列上的点进行赋值，分别对应横坐标和纵坐标的值
Strength.Points.AddXY("A", "90");
Strength.Points.AddXY("B","88");
Strength.Points.AddXY("C", "60");
Strength.Points.AddXY("D", "93");
Strength.Points.AddXY("E", "79");
Strength.Points.AddXY("F", "85");
//把series添加到chart上
chart1.Series.Add(Strength);
}
}
}
7)效果图
2.两个Series
1)右击项目名，【添加】一个windows窗体。然后的话步骤和前面一样，这里就不多说了
2)简单粗暴上代码
using System; using System.Collections.Generic; using ponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Windows.Forms.DataVisualization.Charting;
namespace MyChart { public partial class Form2 : Form { public Form2() { InitializeComponent(); }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49private void Form2_Load(object sender, EventArgs e)
{
chart1.Series.Clear();
Series Strength = new Series("力量");
Series Speed= new Series("速度");
Strength.ChartType = SeriesChartType.Column;
Strength.IsValueShownAsLabel = true;
Strength.Color = System.Drawing.Color.Cyan;
Speed.ChartType = SeriesChartType.Spline;
Speed.IsValueShownAsLabel = true;
chart1.ChartAreas[0].AxisX.MajorGrid.Interval =0.5;
chart1.ChartAreas[0].AxisX.MajorGrid.Enabled =true;
//chart1.ChartAreas[0].Area3DStyle.Enable3D = true;
chart1.ChartAreas[0].AxisX.IsMarginVisible = true;
chart1.ChartAreas[0].AxisX.Title = "英雄";
chart1.ChartAreas[0].AxisX.TitleForeColor = System.Drawing.Color.Crimson;
chart1.ChartAreas[0].AxisY.Title = "属性";
chart1.ChartAreas[0].AxisY.TitleForeColor = System.Drawing.Color.Crimson;
chart1.ChartAreas[0].AxisY.TextOrientation = TextOrientation.Horizontal;
Strength.LegendText = "力气";
Strength.Points.AddXY("A", "90");
Strength.Points.AddXY("B", "88");
Strength.Points.AddXY("C", "60");
Strength.Points.AddXY("D", "93");
Strength.Points.AddXY("E", "79");
Strength.Points.AddXY("F", "85");
Speed.Points.AddXY("A", "120");
Speed.Points.AddXY("B", "133");
Speed.Points.AddXY("C", "100");
Speed.Points.AddXY("D", "98");
Speed.Points.AddXY("E", "126");
Speed.Points.AddXY("F", "89");
//把series添加到chart上
chart1.Series.Add(Speed);
chart1.Series.Add(Strength);
}
}
}
3)效果
4)熟悉常用属性和方法
(1)Series对象
Series Strength = new Series("力量"); Series Speed= new Series("速度");
设置series类型
Strength.ChartType = SeriesChartType.Column; Speed.ChartType = SeriesChartType.Spline;
是否把值当做标签展示(默认false)
Speed.IsValueShownAsLabel = true;
设置series颜色
Strength.Color = System.Drawing.Color.Cyan;
给series上的点赋值
1
2
3Strength.Points.AddXY("A", "90");
Strength.Points.AddXY("B", "88");
Strength.Points.AddXY("C", "60");
(2)ChartArea(就是我们看到的区域)
以3D形式展示
chart1.ChartAreas[0].Area3DStyle.Enable3D = true;
设置坐标轴标题
1
2
3chart1.ChartAreas[0].AxisY.Title = "属性";
chart1.ChartAreas[0].AxisY.TitleForeColor = System.Drawing.Color.Crimson;
chart1.ChartAreas[0].AxisY.TextOrientation = TextOrientation.Horizontal;
设置网格间隔(这里设成0.5，看得更直观一点)
1chart1.ChartAreas[0].AxisX.MajorGrid.Interval =0.5;
3.库存波动
1)主代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75using mon.McsClient;
using System;
using System.Collections.Generic;
using ponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;
namespace BIZWhOnhandQuery
{
public partial class MainForm : Mes.ControlsEx.ExtendForm.BaseForm
{
public string QuerySql01 = string.Empty;
public MainForm()
{
InitializeComponent();
}
private void navigatorEx1_OnQuery(object sender, Mes.Core.ApplicationObject.SystemNavigatorClickedEventArgs e)
{
try
{
QueryForm qf = new QueryForm();
qf.StartPosition = FormStartPosition.CenterScreen;
qf.ShowDialog();
if (qf.DialogResult == System.Windows.Forms.DialogResult.OK)
{
QuerySql01 = qf.QuerySql01;
this.chart1.Series.Clear();//先将series清除
//设置X/Y样式
chart1.ChartAreas[0].AxisY.Title = Mes.Core.Utility.StrUtil.Translate("数量");
chart1.ChartAreas[0].AxisX.Title = Mes.Core.Utility.StrUtil.Translate("日期");
chart1.ChartAreas[0].AxisX.LabelStyle.Angle = 0;
chart1.ChartAreas[0].AxisX.IntervalAutoMode = IntervalAutoMode.VariableCount;
chart1.ChartAreas[0].AxisY.IntervalAutoMode = IntervalAutoMode.VariableCount;
// chart1.ChartAreas[0].AxisX.Enabled = AxisEnabled.False;
// chart1.ChartAreas[0].AxisY.Enabled = AxisEnabled.False;
chart1.Titles[0].Text = "";
//设置char样式
this.chart1.Series.Add(Mes.Core.Utility.StrUtil.Translate("数量"));
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].MarkerColor = Color.Black;//设置标志
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].MarkerSize = 1;
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].MarkerStyle = MarkerStyle.Square;
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].IsValueShownAsLabel = false;//是否显示值
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].ChartType = SeriesChartType.Spline;//设置显示样式
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].BorderWidth = 1;
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].Color = Color.Blue;
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].ToolTip = Mes.Core.Utility.StrUtil.Translate("原材料数量");
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].YValueType = ChartValueType.Double;
McsDataReader reader = (McsDataReader)Mes.Core.Service.DatabaseAccessService.execute(qf.QuerySql01, Mes.Core.Service.ReturnType.RESULTSET);
if (reader.rowNumber > 0)
{
while (reader.Read())
{
chart1.Series[Mes.Core.Utility.StrUtil.Translate("数量")].Points.AddXY(reader.getString(1), reader.getString(0));
}
}
chart1.ChartAreas[0].AxisY.Minimum = 0;
chart1.ChartAreas[0].Axes[1].LabelStyle.Format = "N0";
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
}
2)效果图
4.设备使用分析
查询设备在某个时间范围内的使用频率(可按日或周或月)，点击一条记录可以看到对应的曲线。该报表的设备包括有记录已经维护基础数据的设备，也包括有记录还没有维护基础数据的设备
1)功能代码结构
2)MainForm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324using mon.McsClient;
using System;
using System.Collections.Generic;
using ponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace BIZDeviceUseAnalysis
{
public partial class MainForm : Mes.ControlsEx.ExtendForm.BaseForm
{
QueryForm form = null;
public MainForm()
{
InitializeComponent();
}
private void navigatorEx1_OnQuery(object sender, Mes.Core.ApplicationObject.SystemNavigatorClickedEventArgs e)
{
form = new QueryForm();
form.ShowDialog();
if (form.DialogResult == System.Windows.Forms.DialogResult.OK)
{
GetData();
}
}
void GetData()
{
try
{
AddColumns(); //获取列名
AddDataInGridView();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
void AddColumns()
{
try
{
List columnList = new List();
McsDataReader reader = (McsDataReader)Mes.Core.Service.DatabaseAccessService.execute(form.colsql, Mes.Core.Service.ReturnType.RESULTSET);
//McsDataReader reader01 = (McsDataReader)Mes.Core.Service.DatabaseAccessService.execute(form.QuerySql, Mes.Core.Service.ReturnType.RESULTSET);
while (reader.Read())
{
string calendar = reader.getString(0);
if (form.Kind == 1)
{
calendar = DateTime.Parse(calendar).ToString("yyyy-MM-dd");
}
//if (form.Kind == 2)
//{
// calendar = DateTime.Parse(calendar).ToString("yyyy-iw");
//}
//if (form.Kind == 3)
//{
// calendar = DateTime.Parse(calendar).ToString("yyyy-MM");
//}
columnList.Add(calendar);
}
if (columnList.Count == 0)
return;
int count = this.dataGridViewEx1.Columns.Count;
for (int j = count - 1; j > 2; j--)
{
this.dataGridViewEx1.Columns.RemoveAt(j);
}
for (int i = 0; i < columnList.Count; i++)
{
Mes.ControlsEx.DataGridViewTextBoxExColumn Column = new Mes.ControlsEx.DataGridViewTextBoxExColumn(ponents);
Column.DataType = Mes.Core.ApplicationObject.DataGridViewColumnDataType.NONE;
Column.DefaultCellStyle.Alignment = DataGridViewContentAlignment.NotSet;
Column.SortMode = DataGridViewColumnSortMode.Automatic;
Column.HeaderText = Mes.Core.Utility.StrUtil.ValueToString(columnList[i]);
//Column.HeaderText = DateTime.Parse(Mes.Core.Utility.StrUtil.ValueToString(columnList[i])).ToString("yyyy-MM-dd");
Column.ToolTipText = Mes.Core.Utility.StrUtil.ValueToString(columnList[i]);
Column.IgnoreValueChanged = false;
Column.IndexOrder = 0;
Column.IsShowTimePick = false;
Column.LovParameter = null;
Column.MustBeInput = false;
Column.MustBeInputErrorMsg = "";
Column.Name = "Col" + Mes.Core.Utility.StrUtil.ValueToString(columnList[i]);
Column.Tag = columnList[i];
Column.PopType = Mes.Core.ApplicationObject.DataGridViewColumnPopType.NONE;
Column.RangeEndValue = "";
Column.RangeStartValue = "";
Column.ReadOnly = true;
Column.RegString = "";
Column.ValidationErrorMsg = "";
Column.ValidationType = Mes.Core.ApplicationObject.DataGridViewColumnValidationType.NONE;
Column.Width = 150;
Column.Resizable = DataGridViewTriState.False;
Column.SortMode = DataGridViewColumnSortMode.NotSortable;
this.dataGridViewEx1.Columns.Add(Column);
}
}
catch (Exception ex)
{
throw ex;
}
}
void AddDataInGridView()
{
try
{
this.dataGridViewEx1.Rows.Clear();
GetDataBySQL(form.QuerySql, 1);
this.statusStripBarEx1.ShowMessage(Mes.Core.Utility.StrUtil.Translate("共查询到" + dataGridViewEx1.RowCount + "条数据"));
//FillChart();//填充图表
}
catch (Exception ex)
{
throw ex;
}
}
void GetDataBySQL(string sql, int type)
{
try
{
int rowIndex = -1;
McsDataReader reader = (McsDataReader)Mes.Core.Service.DatabaseAccessService.execute(sql, Mes.Core.Service.ReturnType.RESULTSET);
while (reader.Read())
{
string calendar = reader.getString(2);
if (form.Kind == 1)
{
// calendar = DateTime.Parse(calendar).ToShortDateString();
calendar = DateTime.Parse(calendar).ToString("yyyy-MM-dd");
}
string mac= reader.getString(0);
string deviceCode = reader.getString(3);
string deviceName = reader.getString(4);
string value=reader.getString(1);
bool IsExist = false;
for (int i = 0; i < this.dataGridViewEx1.Rows.Count; i++)
{
string rowValue = Mes.Core.Utility.StrUtil.ValueToString(this.dataGridViewEx1.Rows[i].Cells[0].Value);
if (rowValue == mac)
{
rowIndex = i;
IsExist = true;
break;
}
}
if (IsExist)
{
FindCellForValue(rowIndex, value, calendar,deviceCode,deviceName);
}
else
{
string text = string.Empty;
if (type == 1)
text = mac;
// MessageBox.Show("mac:" + mac);
rowIndex = this.dataGridViewEx1.Rows.Add(text);
this.dataGridViewEx1.Rows[rowIndex].Cells[1].Value = deviceCode;
this.dataGridViewEx1.Rows[rowIndex].Cells[2].Value = deviceName;
FindCellForValue(rowIndex, value, calendar, deviceCode, deviceName);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
void FindCellForValue(int rowIndex, string Value, string ScanItem,string deviceCode,string deviceName)
{
for (int i = 3; i < this.dataGridViewEx1.Columns.Count; i++)
{
if (ScanItem == this.dataGridViewEx1.Columns[i].HeaderText)
{
this.dataGridViewEx1.Rows[rowIndex].Cells[i].Value =Value;
}
}
}
void FillChart(int rowIndex)
{
int FixColumnIndex = 1;
try
{
chart1.Series.Clear();
//chart1.ChartAreas[0].Axes[1].Maximum = 1.3;
chart1.ChartAreas[0].Axes[1].Minimum = 0;
//设置网格线
chart1.ChartAreas[0].AxisX.MajorGrid.LineColor = Color.Black;
chart1.ChartAreas[0].AxisX.MajorGrid.Interval = 0;//网格间隔
chart1.ChartAreas[0].AxisX.MinorGrid.Interval = 0;
chart1.ChartAreas[0].AxisY.MajorGrid.LineColor = Color.Black;
chart1.ChartAreas[0].AxisY.MajorGrid.Interval = 0;
chart1.ChartAreas[0].AxisY.MinorGrid.Interval = 0;
chart1.ChartAreas[0].AxisY.Title = Mes.Core.Utility.StrUtil.Translate("使用次数");
chart1.ChartAreas[0].AxisX.Title = Mes.Core.Utility.StrUtil.Translate("使用日期");
//折线图
//for (int i = 0; i < this.datagridviewex1.rowcount; i++)
//{
int[] yValues1 = new int[this.dataGridViewEx1.Columns.Count - FixColumnIndex];
string[] xValues1 = new string[this.dataGridViewEx1.Columns.Count - FixColumnIndex];
//int[]yValues1=new int[0];
//string[] xValues1=new string[0] ;
chart1.Series.Add(Mes.Core.Utility.StrUtil.ValueToString(this.dataGridViewEx1.Rows[rowIndex].Cells[0].Value));
//设置图片类型
chart1.Series[0].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Spline;
//设置默认轴
chart1.Series[0].IsVisibleInLegend = true;
//设置图例显示
chart1.Series[0].IsValueShownAsLabel = true;
//设置轴显示
//chart1.Series[i].Label = "#VAL{P}";
//设置线条粗细
chart1.Series[0].BorderWidth = 3;
int count = 0;
for (int j = 3; j < this.dataGridViewEx1.ColumnCount; j++)
{
int _value = 0;
string value = Mes.Core.Utility.StrUtil.ValueToString(this.dataGridViewEx1.Rows[rowIndex].Cells[j].Value);
string text = Mes.Core.Utility.StrUtil.ValueToString(this.dataGridViewEx1.Columns[j].HeaderText);
xValues1[count] = text;
//柱状图数据添加
if (value!= string.Empty)
{
_value =Convert.ToInt32(value);
yValues1[count] = _value;
}
else
{
_value = 0;
yValues1[count] = _value;
}
chart1.Series[0].Points.DataBindXY(xValues1,yValues1);
count += 1;
// }
}
}
catch (Exception ex)
{
throw ex;
}
}
private void navigatorEx1_Load(object sender, EventArgs e)
{
this.navigatorEx1.AddButton(Mes.Core.Utility.StrUtil.Translate("设备基础资料维护"), MAINTAIN_DEVICE);
}
//设备基础资料维护窗口
private void MAINTAIN_DEVICE(object sender, EventArgs e)
{
BaseInfoForm bi = new BaseInfoForm();
bi.ShowDialog();
}
private void dataGridViewEx1_CellClick(object sender, DataGridViewCellEventArgs e)
{
int currentIndex = e.RowIndex;
if (e.RowIndex < 0)
return;
if (this.dataGridViewEx1.Rows.Count > 0 && e.RowIndex < this.dataGridViewEx1.Rows.Count) {
FillChart(currentIndex);
}
}
}
}
3)QueryForm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183using Mes.ControlsEx;
using System;
using System.Collections.Generic;
using ponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace BIZDeviceUseAnalysis
{
public partial class QueryForm : Mes.ControlsEx.ExtendForm.QueryForm
{
public int Kind = 0;
public string colsql = string.Empty;
public QueryForm()
{
InitializeComponent();
}
private void QueryForm_Load(object sender, EventArgs e)
{
}
private void cbByDay_CheckedChanged(object sender, EventArgs e)
{
CheckChangeEvent(sender);
}
private void cbByWeek_CheckedChanged(object sender, EventArgs e)
{
CheckChangeEvent(sender);
}
private void cbByMonth_CheckedChanged(object sender, EventArgs e)
{
CheckChangeEvent(sender);
}
private void CheckChangeEvent(object sender)
{
try
{
if ((sender as CheckBoxEx).Checked == true)
{
foreach (CheckBoxEx chk in (sender as CheckBoxEx).Parent.Controls)
{
if (chk != sender)
{
chk.Checked = false;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void QueryForm_OnQuery(object sender, EventArgs e)
{
string startTimeStr = string.Empty;
string endTimeStr = string.Empty;
string condition = string.Empty;
string mac = string.Empty;
//Mac
string txtMac = this.tbMac.Text.Trim();
List macList = this.tbMac.MultirowValue;
if (macList != null & macList.Count > 0)
{
mac = " AND mac in (" + Mes.Core.Utility.StrUtil.BuildPara(macList) + ") ";
}
else
{
if (txtMac != string.Empty)
{
mac = " AND mac " + Mes.Core.Utility.StrUtil.ProcInput(txtMac, false);
}
}
//查询日期从
string txtDailyWorkFrom = this.tbDateFrom.Text.Trim();
if (txtDailyWorkFrom == string.Empty)
{
MessageBox.Show(Mes.Core.Utility.StrUtil.Translate("查询日期从不能为空"));
return;
}
else
{
startTimeStr = txtDailyWorkFrom;
}
//查询日期到
string txtDailyWorkTo = this.tbDateTo.Text.Trim();
if (txtDailyWorkTo == string.Empty)
{
MessageBox.Show(Mes.Core.Utility.StrUtil.Translate("查询日期到不能为空"));
return;
}
else
{
endTimeStr = txtDailyWorkTo;
}
TimeSpan ts;
try
{
DateTime startTime = DateTime.ParseExact(txtDailyWorkFrom, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture);
DateTime endTime = DateTime.ParseExact(txtDailyWorkTo, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture);
ts = endTime - startTime;
}
catch
{
MessageBox.Show(Mes.Core.Utility.StrUtil.Translate("时间格式不正确"));
return;
}
//日报
if (this.cbByDay.Checked)
{
if (ts.TotalDays > 30)
{
MessageBox.Show(Mes.Core.Utility.StrUtil.Translate("查询日期从到查询日期到不能超过一个月"));
return;
}
Kind = 1;
condition += "GROUP BY mac, to_char(bmr.creation_date, 'yyyy-mm-dd'), device_code,device_name";
condition += " ORDER BY mac, to_char(bmr.creation_date, 'yyyy-mm-dd') ,device_code,device_name ";
this.QuerySql = Sql.Core.GetMainSqlByDay(startTimeStr, endTimeStr,mac,condition);
colsql = Sql.Core.GetColumnByDay(startTimeStr, endTimeStr);
}
//周报
if (this.cbByWeek.Checked)
{
if (ts.TotalDays > 13 * 7)
{
MessageBox.Show(Mes.Core.Utility.StrUtil.Translate("查询日期从到查询日期到不能超过13周"));
return;
}
Kind = 2;
condition += "GROUP BY mac, to_char(trunc(bmr.creation_date),'yyyy-iw'), device_code,device_name";
condition += " ORDER BY mac, to_char(trunc(bmr.creation_date),'yyyy-iw') ,device_code,device_name ";
this.QuerySql = Sql.Core.GetMainSqlByWeek(startTimeStr, endTimeStr,mac, condition);
colsql = Sql.Core.GetColumnByWeek(startTimeStr, endTimeStr);
}
//月报
if (this.cbByMonth.Checked)
{
if (ts.TotalDays > 365 * 2)
{
MessageBox.Show(Mes.Core.Utility.StrUtil.Translate("查询日期从到查询日期到不能超过2年"));
return;
}
Kind = 3;
condition += "GROUP BY mac, to_char(trunc(bmr.creation_date),'yyyy-mm'), device_code,device_name";
condition += " ORDER BY mac, to_char(trunc(bmr.creation_date),'yyyy-mm') ,device_code,device_name ";
this.QuerySql = Sql.Core.GetMainSqlByMonth(startTimeStr, endTimeStr, mac,condition);
colsql = Sql.Core.GetColumnByMonth(startTimeStr, endTimeStr);
}
this.DialogResult = System.Windows.Forms.DialogResult.OK;
this.Close();
}
private void QueryForm_OnCancelQuery(object sender, EventArgs e)
{
this.tbMac.Text = this.tbDateFrom.Text = this.tbDateTo.Text = string.Empty;
}
}
}
5)BaseInfoForm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88using System;
using System.Collections.Generic;
using ponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace BIZDeviceUseAnalysis
{
public partial class BaseInfoForm : Mes.ControlsEx.ExtendForm.BaseForm
{
public BaseInfoForm()
{
InitializeComponent();
}
private void navigatorEx1_OnQuery(object sender, Mes.Core.ApplicationObject.SystemNavigatorClickedEventArgs e)
{
BaseInfoQueryForm bio = new BaseInfoQueryForm();
bio.StartPosition = FormStartPosition.CenterScreen;
bio.ShowDialog();
if (bio.DialogResult == System.Windows.Forms.DialogResult.OK) {
this.navigatorEx1.QuerySql = bio.QuerySql;
}
}
private void navigatorEx1_OnSave(object sender, Mes.Core.ApplicationObject.SystemNavigatorClickedEventArgs e)
{
if (this.dataGridViewEx1.AddedRows.Count > 0)//新增行大于0
{
this.navigatorEx1.InsertSqlList = this.SaveOrUpdate(this.dataGridViewEx1.AddedRows, true);//返回值为True
}
if (this.dataGridViewEx1.ChangedRows.Count > 0)//修改行大于0
{
this.navigatorEx1.UpdateSqlList = this.SaveOrUpdate(this.dataGridViewEx1.ChangedRows, false);//返回值为False
}
}
private List SaveOrUpdate(List list, bool flag)
{
List InsertOrUpdatelist = new List();
for (int i = 0; i < list.Count; i++)
{
int row_index = Convert.ToInt32(list[i]);
List lis = new List();
if (flag)//如果返回值为True
{
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDeviceCode.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDeviceType.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDeviceName.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColMacAddress.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColFloor.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColAdminDepartment.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColEnable.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDescription.Name].Value));
string template = Mes.Core.Utility.StrUtil.BuildPara(lis);
string sql = "{? = call biz_device_pck.insert_rows_for_ui(" + template + ")}";
InsertOrUpdatelist.Add(sql);
}
else
{
// lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[0].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDeviceID.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDeviceCode.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDeviceType.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDeviceName.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColMacAddress.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColFloor.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColAdminDepartment.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColEnable.Name].Value));
lis.Add(Mes.Core.Utility.StrUtil.ValueToString(dataGridViewEx1.Rows[row_index].Cells[this.ColDescription.Name].Value));
string template = Mes.Core.Utility.StrUtil.BuildPara(lis);
string sql = "{? = call biz_device_pck.update_rows_for_ui(" + template + ")}";
InsertOrUpdatelist.Add(sql);
}
}
return InsertOrUpdatelist;
}
}
}
}
6)BaseInfoQueryForm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92using System;
using System.Collections.Generic;
using ponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace BIZDeviceUseAnalysis
{
public partial class BaseInfoQueryForm : Mes.ControlsEx.ExtendForm.QueryForm
{
public BaseInfoQueryForm()
{
InitializeComponent();
}
private void BaseInfoQueryForm_OnQuery(object sender, EventArgs e)
{
this.QuerySql = Sql.Core.GetDeviceInfo();
// 设备编码
string txtDeviceCode = this.tbDeviceCode.Text.Trim();
List deviceCodeList = this.tbDeviceCode.MultirowValue;
if (deviceCodeList != null & deviceCodeList.Count > 0)
{
this.QuerySql += " AND device_code in (" + Mes.Core.Utility.StrUtil.BuildPara(deviceCodeList) + ") ";
}
else
{
if (txtDeviceCode != string.Empty)
{
this.QuerySql += " AND device_code " + Mes.Core.Utility.StrUtil.ProcInput(txtDeviceCode, false);
}
}
//设备名称
string txtDeviceName = this.tbDeviceName.Text.Trim();
List deviceNameList = this.tbDeviceName.MultirowValue;
if (deviceNameList != null & deviceNameList.Count > 0)
{
this.QuerySql += " AND device_name in (" + Mes.Core.Utility.StrUtil.BuildPara(deviceNameList) + ") ";
}
else
{
if (txtDeviceName != string.Empty)
{
this.QuerySql += " AND device_name " + Mes.Core.Utility.StrUtil.ProcInput(txtDeviceName, false);
}
}
//mac地址
string txtMac = this.tbMacAddress.Text.Trim();
List macList = this.tbMacAddress.MultirowValue;
if (macList != null & macList.Count > 0)
{
this.QuerySql += " AND mac_address in (" + Mes.Core.Utility.StrUtil.BuildPara(macList) + ") ";
}
else
{
if (txtMac != string.Empty)
{
this.QuerySql += " AND mac_address " + Mes.Core.Utility.StrUtil.ProcInput(txtMac, false);
}
}
//部门
string txtDepartment = this.tbDepartment.Text.Trim();
List departmentList = this.tbDepartment.MultirowValue;
if (departmentList != null & departmentList.Count > 0)
{
this.QuerySql += " AND admin_department in (" + Mes.Core.Utility.StrUtil.BuildPara(departmentList) + ") ";
}
else
{
if (txtDepartment != string.Empty)
{
this.QuerySql += " AND admin_department " + Mes.Core.Utility.StrUtil.ProcInput(txtDepartment, false);
}
}
this.QuerySql += " order by device_code,device_name";
this.DialogResult = System.Windows.Forms.DialogResult.OK;
this.Close();
}
private void BaseInfoQueryForm_OnCancelQuery(object sender, EventArgs e)
{
this.tbDeviceCode.Text = this.tbDeviceName.Text =
this.tbMacAddress.Text = this.tbDepartment.Text = string.Empty;
}
}
}
7)SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133using Mes.ControlsEx;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BIZDeviceUseAnalysis.Sql
{
class Core
{
//按天
public static string GetMainSqlByDay(string startTime, string endTime,string mac,string condition)
{
string sql = @"
WITH baseInfoRecord AS(SELECT mac, SUM(counting) total, to_char(bmr.creation_date, 'yyyy-mm-dd'), device_code,device_name FROM biz_mac_record bmr, (SELECT 1 AS counting FROM dual),biz_device_info bdi WHERE bmr.creation_date >= to_date('" + startTime + @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') AND bmr.mac=bdi.mac_address "+mac+@" "+condition+@" ) , noBaseInfo AS(SELECT DISTINCT mac, SUM(counting) total, to_char(bmr.creation_date, 'yyyy-mm-dd'),''device_code,''device_name FROM biz_mac_record bmr, (SELECT 1 AS counting FROM dual),biz_device_info bdi WHERE bmr.creation_date >= to_date('" + startTime + @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') AND bmr.mac NOT IN (SELECT mac_address FROM biz_device_info) " + mac + @" " + condition + @" ) SELECT * FROM baseInfoRecord UNION ALL SELECT * FROM noBaseInfo"; return sql; } public static string GetColumnByDay(string startTime, string endTime) { string sql = @"SELECT DISTINCT to_char(bmr.creation_date,'yyyy-mm-dd') FROM biz_mac_record bmr WHERE bmr.creation_date >= to_date('" + startTime+ @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') ORDER BY to_char(bmr.creation_date,'yyyy-mm-dd')"; return sql; } //按周 public static string GetMainSqlByWeek(string startTime, string endTime, string mac, string condition) { string sql = @" WITH baseInfoRecord AS(SELECT mac, SUM(counting) total, to_char(trunc(bmr.creation_date),'yyyy-iw'), device_code,device_name FROM biz_mac_record bmr, (SELECT 1 AS counting FROM dual),biz_device_info bdi WHERE bmr.creation_date >= to_date('" + startTime + @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') AND bmr.mac=bdi.mac_address " + mac + @" " + condition + @" ) , noBaseInfo AS(SELECT DISTINCT mac, SUM(counting) total, to_char(trunc(bmr.creation_date),'yyyy-iw'),''device_code,''device_name FROM biz_mac_record bmr, (SELECT 1 AS counting FROM dual),biz_device_info bdi WHERE bmr.creation_date >= to_date('" + startTime + @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') AND bmr.mac NOT IN (SELECT mac_address FROM biz_device_info) " + mac + @" " + condition + @" ) SELECT * FROM baseInfoRecord UNION ALL SELECT * FROM noBaseInfo"; return sql; } public static string GetColumnByWeek(string startTime, string endTime) { string sql = @"SELECT DISTINCT to_char(bmr.creation_date,'yyyy-iw') FROM biz_mac_record bmr WHERE bmr.creation_date >= to_date('" + startTime + @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') ORDER BY to_char(bmr.creation_date,'yyyy-iw')"; return sql; } //按月 public static string GetMainSqlByMonth(string startTime, string endTime, string mac, string condition) { string sql = @" WITH baseInfoRecord AS(SELECT mac, SUM(counting) total, to_char(trunc(bmr.creation_date),'yyyy-mm'), device_code,device_name FROM biz_mac_record bmr, (SELECT 1 AS counting FROM dual),biz_device_info bdi WHERE bmr.creation_date >= to_date('" + startTime + @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') AND bmr.mac=bdi.mac_address " + mac + @" " + condition + @" ) , noBaseInfo AS(SELECT DISTINCT mac, SUM(counting) total, to_char(trunc(bmr.creation_date),'yyyy-mm'),''device_code,''device_name FROM biz_mac_record bmr, (SELECT 1 AS counting FROM dual),biz_device_info bdi WHERE bmr.creation_date >= to_date('" + startTime + @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') AND bmr.mac NOT IN (SELECT mac_address FROM biz_device_info) " + mac + @" " + condition + @" ) SELECT * FROM baseInfoRecord UNION ALL SELECT * FROM noBaseInfo"; return sql; } public static string GetColumnByMonth(string startTime, string endTime) { string sql = @"SELECT DISTINCT to_char(bmr.creation_date,'yyyy-mm') FROM biz_mac_record bmr WHERE bmr.creation_date >= to_date('" + startTime + @"', 'yyyy-mm-dd') AND bmr.creation_date <= to_date('" + endTime + @"', 'yyyy-mm-dd') ORDER BY to_char(bmr.creation_date,'yyyy-mm')"; return sql; } //设备基础信息 public static string GetDeviceInfo() { string sql = @"select * from biz_device_info where 1=1"; return sql; } } }
8)运行效果
按日
按周
