1.第一个Chart控件
1）先来熟悉一下chart,在前端做一个图表可能会用到chart.js，在C#中可以用自带的控件chart，感觉挺方便的。
2）创建一个项目，windows窗体应用程序。在工具箱的【数据】找到【 Chart】控件，并拖到窗体
3)右键chart【属性】，在VS右侧属性【布局】下面找到【Dock】属性设置为Fill,自己再调整一下大小
4）这里的操作是当加载窗体的时候显示chart,所以有个窗体load事件。
5）双击后直接进入代码，当在代码中写Series时会出现红色波浪线，提示缺少相关命名空间之类的，点击【Series】就可以看到所需要的，添加就ok了
using System;using System.Collections.Generic;using System.ComponentModel;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); } }}
1）右击项目名，【添加】一个windows窗体。然后的话步骤和前面一样，这里就不多说了
using System;
using System.Collections.Generic;
using System.ComponentModel;
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();
}
private 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);
}
}
}
（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上的点赋值
Strength.Points.AddXY("A","90");Strength.Points.AddXY("B","88");Strength.Points.AddXY("C","60");
（2）ChartArea(就是我们看到的区域)
以3D形式展示
chart1.ChartAreas[0].Area3DStyle.Enable3D = true;
设置坐标轴标题
chart1.ChartAreas[0].AxisY.Title="属性";chart1.ChartAreas[0].AxisY.TitleForeColor= System.Drawing.Color.Crimson;chart1.ChartAreas[0].AxisY.TextOrientation= TextOrientation.Horizontal;
设置网格间隔（这里设成0.5，看得更直观一点）
chart1.ChartAreas[0].AxisX.MajorGrid.Interval=0.5;
1
using Daisy.Common.McsClient;using System;using System.Collections.Generic;using System.ComponentModel;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.SystemNavigatorClickedEventArgse) { 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;} } }
查询设备在某个时间范围内的使用频率（可按日或周或月），点击一条记录可以看到对应的曲线。该报表的设备包括有记录已经维护基础数据的设备，也包括有记录还没有维护基础数据的设备
using Daisy.Common.McsClient;using System;using System.Collections.Generic;using System.ComponentModel;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.SystemNavigatorClickedEventArgse) { 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.DataGridViewTextBoxExColumnColumn = new Mes.ControlsEx.DataGridViewTextBoxExColumn(this.components);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);} } }}
usingMes.ControlsEx;usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;namespace BIZDeviceUseAnalysis{publicpartialclassQueryForm : Mes.ControlsEx.ExtendForm.QueryForm {publicintKind =0;publicstringcolsql =string.Empty;publicQueryForm() { InitializeComponent(); }privatevoidQueryForm_Load(objectsender, EventArgs e) { }privatevoidcbByDay_CheckedChanged(objectsender, EventArgs e) { CheckChangeEvent(sender); }privatevoidcbByWeek_CheckedChanged(objectsender, EventArgs e) { CheckChangeEvent(sender); }privatevoidcbByMonth_CheckedChanged(objectsender, EventArgs e) { CheckChangeEvent(sender); }privatevoidCheckChangeEvent(objectsender) {try{if((senderasCheckBoxEx).Checked ==true) {foreach(CheckBoxEx chkin(senderasCheckBoxEx).Parent.Controls) {if(chk != sender) { chk.Checked =false; } } } }catch(Exception ex) { MessageBox.Show(ex.Message); } }privatevoidQueryForm_OnQuery(objectsender, EventArgs e) {stringstartTimeStr =string.Empty;stringendTimeStr =string.Empty;stringcondition =string.Empty;stringmac =string.Empty;//MacstringtxtMac =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); } }//查询日期从stringtxtDailyWorkFrom =this.tbDateFrom.Text.Trim();if(txtDailyWorkFrom ==string.Empty) { MessageBox.Show(Mes.Core.Utility.StrUtil.Translate("查询日期从不能为空"));return; }else{ startTimeStr = txtDailyWorkFrom; }//查询日期到stringtxtDailyWorkTo =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(); }privatevoidQueryForm_OnCancelQuery(objectsender, EventArgs e) {this.tbMac.Text =this.tbDateFrom.Text =this.tbDateTo.Text =string.Empty; } }}
using System;using System.Collections.Generic;using System.ComponentModel;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.SystemNavigatorClickedEventArgse) { 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.SystemNavigatorClickedEventArgse) { 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;} } }}
using System;using System.Collections.Generic;using System.ComponentModel;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;} }}
using 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(SELECTmac,SUM(counting) total, to_char(bmr.creation_date,'yyyy-mm-dd'), device_code,device_nameFROMbiz_mac_record bmr, (SELECT1AScountingFROMdual),biz_device_info bdiWHEREbmr.creation_date >= to_date('" + startTime + @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ANDbmr.mac=bdi.mac_address"+mac+@""+condition+@") , noBaseInfoAS(SELECTDISTINCTmac,SUM(counting) total, to_char(bmr.creation_date,'yyyy-mm-dd'),''device_code,''device_nameFROMbiz_mac_record bmr, (SELECT1AScountingFROMdual),biz_device_info bdiWHEREbmr.creation_date >= to_date('" + startTime + @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ANDbmr.macNOTIN(SELECTmac_addressFROMbiz_device_info)" + mac + @"" + condition + @")SELECT*FROMbaseInfoRecordUNIONALLSELECT*FROMnoBaseInfo";
return sql;
}
public static string GetColumnByDay(string startTime, string endTime)
{
string sql = @"SELECTDISTINCTto_char(bmr.creation_date,'yyyy-mm-dd')FROMbiz_mac_record bmrWHEREbmr.creation_date >= to_date('" + startTime+ @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ORDERBYto_char(bmr.creation_date,'yyyy-mm-dd')";
return sql;
}
//按周
public static string GetMainSqlByWeek(string startTime, string endTime, string mac, string condition)
{
string sql = @"WITHbaseInfoRecordAS(SELECTmac,SUM(counting) total, to_char(trunc(bmr.creation_date),'yyyy-iw'), device_code,device_nameFROMbiz_mac_record bmr, (SELECT1AScountingFROMdual),biz_device_info bdiWHEREbmr.creation_date >= to_date('" + startTime + @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ANDbmr.mac=bdi.mac_address" + mac + @"" + condition + @") , noBaseInfoAS(SELECTDISTINCTmac,SUM(counting) total, to_char(trunc(bmr.creation_date),'yyyy-iw'),''device_code,''device_nameFROMbiz_mac_record bmr, (SELECT1AScountingFROMdual),biz_device_info bdiWHEREbmr.creation_date >= to_date('" + startTime + @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ANDbmr.macNOTIN(SELECTmac_addressFROMbiz_device_info)" + mac + @"" + condition + @")SELECT*FROMbaseInfoRecordUNIONALLSELECT*FROMnoBaseInfo";
return sql;
}
public static string GetColumnByWeek(string startTime, string endTime)
{
string sql = @"SELECTDISTINCTto_char(bmr.creation_date,'yyyy-iw')FROMbiz_mac_record bmrWHEREbmr.creation_date >= to_date('" + startTime + @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ORDERBYto_char(bmr.creation_date,'yyyy-iw')";
return sql;
}
//按月
public static string GetMainSqlByMonth(string startTime, string endTime, string mac, string condition)
{
string sql = @"WITHbaseInfoRecordAS(SELECTmac,SUM(counting) total, to_char(trunc(bmr.creation_date),'yyyy-mm'), device_code,device_nameFROMbiz_mac_record bmr, (SELECT1AScountingFROMdual),biz_device_info bdiWHEREbmr.creation_date >= to_date('" + startTime + @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ANDbmr.mac=bdi.mac_address" + mac + @"" + condition + @") , noBaseInfoAS(SELECTDISTINCTmac,SUM(counting) total, to_char(trunc(bmr.creation_date),'yyyy-mm'),''device_code,''device_nameFROMbiz_mac_record bmr, (SELECT1AScountingFROMdual),biz_device_info bdiWHEREbmr.creation_date >= to_date('" + startTime + @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ANDbmr.macNOTIN(SELECTmac_addressFROMbiz_device_info)" + mac + @"" + condition + @")SELECT*FROMbaseInfoRecordUNIONALLSELECT*FROMnoBaseInfo";
return sql;
}
public static string GetColumnByMonth(string startTime, string endTime)
{
string sql = @"SELECTDISTINCTto_char(bmr.creation_date,'yyyy-mm')FROMbiz_mac_record bmrWHEREbmr.creation_date >= to_date('" + startTime + @"','yyyy-mm-dd')ANDbmr.creation_date <= to_date('" + endTime + @"','yyyy-mm-dd')ORDERBYto_char(bmr.creation_date,'yyyy-mm')";
return sql;
}
//设备基础信息
public static string GetDeviceInfo()
{
string sql = @"select*frombiz_device_infowhere1=1";
return sql;
}
}
}
按日
按周
网友评论