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.IO; namespace AGVSystem { public partial class frmfind : Form { private ClsDBConn_sql db = new ClsDBConn_sql(); private System.Data.DataSet ds = new System.Data.DataSet(); public frmfind(string str ) { InitializeComponent(); this.Text = str; if (str != "异常报警日志") { label4.Visible = false; cmb_gz.Visible = false; } this.WindowState = FormWindowState.Maximized; dateTimePicker2.Value = DateTime.Now; dateTimePicker1.Value = DateTime.Now.AddHours(-1); } private void frmfind_Load(object sender, EventArgs e) { ds = db.connDt("select CONVERT( VARCHAR(10),id) +' . '+ line_name as info from Assembly_Line order by id"); if (ds.Tables.Count > 0) { if (ds.Tables[0].Rows.Count > 0) { cmb_linelist.Items.Add(""); foreach (DataRow dr in ds.Tables[0].Rows) { cmb_linelist.Items.Add(dr[0].ToString()); } } } ds.Tables.Clear(); } private void button2_Click(object sender, EventArgs e) { string strsql = ""; if (this.Text == "异常报警日志") { strsql = "SELECT Dt as '发生时间',Assembly_Line.line_name as '所属线体'" + " ,'* '+convert (nvarchar(10),agvinfo.id) +' / '+convert (nvarchar(10),AgvInfo.Internal_ID) as '设备编号' " + " ,Warn_Log.[Type] as '故障类型' " + " ,[Reset] as '故障恢复时间' " + " FROM [Warn_Log],AgvInfo,Assembly_Line " + " where Warn_Log.Agv_id = AgvInfo.ID and " + " AgvInfo.Assembly_Line = Assembly_Line.id and " + " Dt between '" + dateTimePicker1.Value + "' and '" + dateTimePicker2.Value + "' "; if (cmb_linelist.Text != "") strsql = strsql + " and Assembly_Line.id= " + cmb_linelist.Text.Split('.')[0]; if(cmb_agv.Text!="") strsql = strsql + " and AgvInfo.Internal_ID= " + cmb_agv.Text; if (cmb_gz.Text != "") strsql = strsql + " and warn_log.type like '%" + cmb_gz.Text + "%'"; strsql = strsql + " order by Assembly_Line.ID , dt"; } else if (this.Text == "地标读取日志") { strsql = "SELECT Dt as '读取时间',Assembly_Line.line_name as '所属线体'" + " ,'* '+convert (nvarchar(10),agvinfo.id) +' / '+convert (nvarchar(10),AgvInfo.Internal_ID) as '设备编号' " + " ,Location_Log.Loc_ID as '位置编号' , Location_Log.Value as 'plc发送值',[Loc_ID2] as 路径编号 ,[Value2] as plc路径值 " + " FROM [Location_Log],AgvInfo,Assembly_Line " + " where Location_Log.Agv_id = AgvInfo.ID and " + " AgvInfo.Assembly_Line = Assembly_Line.id and " + " Dt between '" + dateTimePicker1.Value + "' and '" + dateTimePicker2.Value + "' "; if (cmb_linelist.Text != "") strsql = strsql + " and Assembly_Line.id= " + cmb_linelist.Text.Split('.')[0]; if (cmb_agv.Text != "") strsql = strsql + " and AgvInfo.Internal_ID= " + cmb_agv.Text; strsql = strsql + " order by Assembly_Line.ID , dt"; } else if (this.Text == "AGV放行日志") { strsql = "SELECT Dt as '发生时间',Assembly_Line.line_name as '所属线体'" + " ,'* '+convert (nvarchar(10),agvinfo.id) +' / '+convert (nvarchar(10),AgvInfo.Internal_ID) as '设备编号' " + " ,go_Log.[Type] as '类型' ,go_log.Location as '位置编号' , go_log.plc_Value as 'plc发送值'" + " FROM [go_Log] left join AgvInfo on go_Log.Agv_id = AgvInfo.ID and Dt between '" + dateTimePicker1.Value + "' and '" + dateTimePicker2.Value + "' "; if (cmb_agv.Text != "") strsql = strsql + " and AgvInfo.Internal_ID= " + cmb_agv.Text; strsql = strsql + " right join Assembly_Line on AgvInfo.Assembly_Line = Assembly_Line.id "; if (cmb_linelist.Text != "") strsql = strsql + " and Assembly_Line.id= " + cmb_linelist.Text.Split('.')[0]; strsql = strsql +" left join landmarkinfo on landmarkinfo.loc_id = go_log.location" ; strsql = strsql + " order by Assembly_Line.ID , dt"; } else if (this.Text == "网络异常日志") { strsql = "SELECT Dt as '发生时间',Assembly_Line.line_name as '所属线体'" + " ,'* '+convert (nvarchar(10),agvinfo.id) +' / '+convert (nvarchar(10),AgvInfo.Internal_ID) as '设备编号' " + " ,net_Log.[Type] as '故障类型' " + " FROM [net_Log],AgvInfo,Assembly_Line " + " where net_Log.Agv_id = AgvInfo.ID and " + " AgvInfo.Assembly_Line = Assembly_Line.id and " + " Dt between '" + dateTimePicker1.Value + "' and '" + dateTimePicker2.Value + "' "; if (cmb_linelist.Text != "") strsql = strsql + " and Assembly_Line.id= " + cmb_linelist.Text.Split('.')[0]; if (cmb_agv.Text != "") strsql = strsql + " and AgvInfo.Internal_ID= " + cmb_agv.Text; strsql = strsql + " order by Assembly_Line.ID , dt"; } else if(this.Text=="充电记录") { strsql = "SELECT [Start_time] as 充电时间, '* '+convert (nvarchar(10),agvinfo.id) +' / '+convert (nvarchar(10),AgvInfo.Internal_ID) as '设备编号' " + ", Assembly_Line.Line_name as 所属线体 ,[Location_ID] as 充电位置,[Unit] as 充电时长 " + " FROM [Change_Log] , AgvInfo , Assembly_Line " + " where [Change_Log].Agv_ID = AgvInfo.ID and " + " AgvInfo.Assembly_Line = Assembly_Line.ID and "+ " [Start_time] between '" + dateTimePicker1.Value + "' and '" + dateTimePicker2.Value + "' "; if (cmb_linelist.Text != "") strsql = strsql + " and Assembly_Line.id= " + cmb_linelist.Text.Split('.')[0]; if (cmb_agv.Text != "") strsql = strsql + " and AgvInfo.Internal_ID= " + cmb_agv.Text; strsql = strsql + " order by Assembly_Line.ID , 充电时间"; } ds.Tables.Clear(); ds = db.connDt(strsql); dataGridView1.DataSource = ds.Tables[0]; dataGridView1.Columns[0].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss"; dataGridView1.Columns[0].Width = 200; dataGridView1.Columns[3].Width = 200; if (this.Text == "异常报警日志") { dataGridView1.Columns[4].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss"; dataGridView1.Columns[4].Width = 200; } } private void cmb_linelist_SelectedIndexChanged(object sender, EventArgs e) { if (cmb_linelist.Text != "") { ds = db.connDt("select Internal_ID from AgvInfo where [Assembly_Line] =" + cmb_linelist.Text.Split('.')[0] + " order by Internal_ID"); if (ds.Tables.Count > 0) { if (ds.Tables[0].Rows.Count > 0) { cmb_agv.Items.Clear(); cmb_agv.Items.Add(""); foreach (DataRow dr in ds.Tables[0].Rows) { cmb_agv.Items.Add(dr[0].ToString()); } } } } } private void button1_Click(object sender, EventArgs e) { if (dataGridView1.RowCount>0) SaveAs(); } private void SaveAs() //另存新档按钮 导出成Excel { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Execl files (*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = "Export Excel File To"; saveFileDialog.ShowDialog(); Stream myStream; try { myStream = saveFileDialog.OpenFile(); } catch { return; } //StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312")); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); string str = ""; try { //写标题 for (int i = 0; i < dataGridView1.ColumnCount; i++) { if (i > 0) { str += "\t"; } str += dataGridView1.Columns[i].HeaderText; } sw.WriteLine(str); //写内容 for (int j = 0; j < dataGridView1.Rows.Count; j++) { string tempStr = ""; for (int k = 0; k < dataGridView1.Columns.Count; k++) { if (k > 0) { tempStr += "\t"; } tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString(); } sw.WriteLine(tempStr); } sw.Close(); myStream.Close(); } catch (Exception e) { MessageBox.Show(e.ToString()); } finally { sw.Close(); myStream.Close(); } } private void dataGridView1_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e) { e.Row.HeaderCell.Value = string.Format("{0}", e.Row.Index + 1); } } }