using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.IO; namespace M423project { public partial class FormDetectionResult : Form { private ConfigStruct opcConfig; public FormDetectionResult(ConfigStruct _opcConfig) { InitializeComponent(); opcConfig = _opcConfig; gridPlate.AutoGenerateColumns = false; } private void btnFind_Click(object sender, EventArgs e) { StringBuilder sb = new StringBuilder(); System.Data.DataTable dt; switch (tcResult.SelectedIndex) { case 0: sb.Append(string.Format( " SELECT DetectID = AutoID " + " ,DetectTime " + " ,ProductNo " + " ,PlateID " + " ,Height = ProductHeight" //+ " ,CellHeight = ProductCellHeight" + " ,HeightResult = ProductHeightStatus" + " ,Length = ProductLength " + " ,Width = ProductWidth " //+ " ,CellWidth = ProductCellWidth " + " ,SizeResult = ProductSizeStatus " + " ,ProductResult = ProductStatus" + " ,ImageFileName,case when ISNULL(ProductType,0)=0 then N'产品' when ProductType=1 then N'标准块' end ProductTypeTxt" + " FROM ProductDetectionDetail " + " WHERE DetectTime Between '{0}' AND '{1}' ", dtpBegin.Value.ToString("yyyy-MM-dd HH:mm:ss"), dtpEnd.Value.ToString("yyyy-MM-dd HH:mm:ss"))); if (!cbIncludeInvalidProductNo.Checked) { sb.Append(string.Format(" AND ProductNo <> 'N/A' ")); } if (tbProductNo.Text.Trim() != string.Empty) { sb.Append(string.Format(" AND ProductNo LIKE '%{0}%'", tbProductNo.Text)); } dt = CommonUtil.mainForm.DetectData.Query(sb.ToString()); dgvResultDetail.DataSource = dt; break; case 1: sb.Append(string.Format( " SELECT ProductNo " + " ,HeightResult = ProductHeightStatus" + " ,SizeResult = ProductSizeStatus " + " ,DetectTimes = COUNT(ProductNo) " + " FROM ProductDetectionDetail " + " WHERE ProductNo <> 'N/A' AND ProductSizeStatus <> 'UNKNOWN' AND ProductHeightStatus <> 'UNKNOWN' " + " AND DetectTime Between '{0}' AND '{1}' ", dtpBegin.Value.ToString("yyyy-MM-dd HH:mm:ss"), dtpEnd.Value.ToString("yyyy-MM-dd HH:mm:ss"))); if (tbProductNo.Text.Trim() != string.Empty) { sb.Append(string.Format(" AND ProductNo LIKE '%{0}%'", tbProductNo.Text)); } if (!cbIncludeInvalidProductNo.Checked) { sb.Append(string.Format(" AND ProductNo <> 'N/A' ")); } sb.Append(" GROUP BY ProductNo, ProductHeightStatus, ProductSizeStatus "); dt = CommonUtil.mainForm.DetectData.Query(sb.ToString()); dgvTotal.DataSource = dt; break; case 2: sb.AppendFormat(@"select *,TotalQty-ProductOK ProductNG from (select T1.PlateID,ISNULL(T2.TotalQty,0) TotalQty,ISNULL(T3.ProductNoNG,0) ProductNoNG, ISNULL(T4.ProductHeightNG,0) ProductHeightNG,ISNULL(T5.ProductLengthNG,0) ProductLengthNG, ISNULL(T6.ProductWidthNG,0) ProductWidthNG,ISNULL(T7.ProductOK,0) ProductOK from (select 1 PlateID union select 2 union select 3 union select 4 union select 5) T1 left join (select PlateID,isnull(convert(decimal(10,2),COUNT(AutoID)),0) TotalQty from ProductDetectionDetail where isnull(ProductType,0)=0 and PlateID is not null and DetectTime Between '{0}' and '{1}' group by PlateID) T2 on T1.PlateID=T2.PlateID left join (select PlateID,isnull(convert(decimal(10,2),COUNT(AutoID)),0) ProductNoNG from ProductDetectionDetail where isnull(ProductType,0)=0 and PlateID is not null and DetectTime Between '{0}' and '{1}' and ProductNoStatus='NG' group by PlateID) T3 on T1.PlateID=T3.PlateID left join (select PlateID,isnull(convert(decimal(10,2),COUNT(AutoID)),0) ProductHeightNG from ProductDetectionDetail where isnull(ProductType,0)=0 and PlateID is not null and DetectTime Between '{0}' and '{1}' and ProductHeightStatus='NG' group by PlateID) T4 on T1.PlateID=T4.PlateID left join (select PlateID,isnull(convert(decimal(10,2),COUNT(AutoID)),0) ProductLengthNG from ProductDetectionDetail where isnull(ProductType,0)=0 and PlateID is not null and DetectTime Between '{0}' and '{1}' and ProductLengthStatus='NG' group by PlateID) T5 on T1.PlateID=T5.PlateID left join (select PlateID,isnull(convert(decimal(10,2),COUNT(AutoID)),0) ProductWidthNG from ProductDetectionDetail where isnull(ProductType,0)=0 and PlateID is not null and DetectTime Between '{0}' and '{1}' and ProductWidthStatus='NG' group by PlateID) T6 on T1.PlateID=T6.PlateID left join (select PlateID,isnull(convert(decimal(10,2),COUNT(AutoID)),0) ProductOK from ProductDetectionDetail where isnull(ProductType,0)=0 and PlateID is not null and DetectTime Between '{0}' and '{1}' and ProductStatus='OK' group by PlateID) T7 on T1.PlateID=T7.PlateID ) T order by PlateID", dtpBegin.Value.ToString("yyyy-MM-dd HH:mm:ss"), dtpEnd.Value.ToString("yyyy-MM-dd HH:mm:ss")); dt = CommonUtil.mainForm.DetectData.Query(sb.ToString()); gridPlate.DataSource = dt; break; } } private void FormDetectionResult_Load(object sender, EventArgs e) { dtpBegin.Value = DateTime.Now.AddHours(-3); dtpEnd.Value = DateTime.Now; } private void btnExport_Click(object sender, EventArgs e) { switch (tcResult.SelectedIndex) { case 0: DataGridViewToExcel(dgvResultDetail); break; case 1: DataGridViewToExcel(dgvTotal); break; case 2: DataGridViewToExcel(gridPlate); break; } } private void DataGridViewToExcel(DataGridView dgv) { SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "Execl files (*.csv)|*.csv"; dlg.FilterIndex = 0; dlg.RestoreDirectory = true; dlg.CreatePrompt = true; dlg.Title = "导出到Excel文件"; if (dlg.ShowDialog() == DialogResult.OK) { Stream myStream; myStream = dlg.OpenFile(); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.UTF8); string columnTitle = ""; try { for (int i = 0; i < dgv.ColumnCount; i++) { if (i > 0) { columnTitle += ","; } columnTitle += dgv.Columns[i].HeaderText; } sw.WriteLine(columnTitle); for (int j = 0; j < dgv.Rows.Count; j++) { string columnValue = ""; for (int k = 0; k < dgv.Columns.Count; k++) { if (k > 0) { columnValue += ","; } if (dgv.Rows[j].Cells[k].Value == null) columnValue += ""; else columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim(); } sw.WriteLine(columnValue); } sw.Close(); myStream.Close(); } catch (Exception e) { MessageBox.Show(e.ToString()); } finally { sw.Close(); myStream.Close(); } } } private void dgvResultDetail_CellPainting(object sender, DataGridViewCellPaintingEventArgs e) { /* //if ( e.RowIndex >= 0) { Color backColor = e.CellStyle.BackColor; if (e.Value != null) { if (dgvResultDetail.Columns["Height"].Index == e.ColumnIndex) { double val; try { val = (double)e.Value; } catch (Exception) { val = 0.0; } backColor = val >= opcConfig.batteryBMULimit.Min && val <= opcConfig.batteryBMULimit.Max ? backColor : Color.Red; } if (dgvResultDetail.Columns["Length"].Index == e.ColumnIndex) { double val; try { val = (double)e.Value; } catch (Exception) { val = 0.0; } backColor = val >= opcConfig.batteryLengthLimit.Min && val <= opcConfig.batteryLengthLimit.Max ? backColor : Color.Red; } if (dgvResultDetail.Columns["Width"].Index == e.ColumnIndex) { double val; try { val = (double)e.Value; } catch (Exception) { val = 0.0; } backColor = val >= opcConfig.batteryWidthLimit.Min && val <= opcConfig.batteryWidthLimit.Max ? backColor : Color.Red; } } Rectangle newRect = new Rectangle(e.CellBounds.X + 1, e.CellBounds.Y + 1, e.CellBounds.Width - 1, e.CellBounds.Height - 1); e.CellStyle.BackColor = backColor; using (Brush backColorBrush = new SolidBrush(dgvResultDetail.BackgroundColor)) { e.Graphics.FillRectangle(backColorBrush, e.CellBounds); if (e.Value != null) { e.Graphics.DrawString(e.Value.ToString(), e.CellStyle.Font, backColorBrush, e.CellBounds.X + 2, e.CellBounds.Y + 2, StringFormat.GenericDefault); } e.Handled = true; } } */ } private void dgvResultDetail_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e) { /* if (e.RowIndex >= 0) { Color backColor = dgvResultDetail.BackgroundColor; if (dgvResultDetail.Columns["Height"].Index == e.ColumnIndex) { double val = (double)e.Value; backColor = val >= opcConfig.batteryBMULimit.Min && val <= opcConfig.batteryBMULimit.Max ? backColor : Color.Red; } if (dgvResultDetail.Columns["Length"].Index == e.ColumnIndex) { double val = (double)e.Value; backColor = val >= opcConfig.batteryLengthLimit.Min && val <= opcConfig.batteryLengthLimit.Max ? backColor : Color.Red; } if (dgvResultDetail.Columns["Width"].Index == e.ColumnIndex) { double val = (double)e.Value; backColor = val >= opcConfig.batteryWidthLimit.Min && val <= opcConfig.batteryWidthLimit.Max ? backColor : Color.Red; } e.CellStyle.BackColor = backColor; } * */ } } }