private void exportxmlbutton_Click(object sender, EventArgs e) { DateTime time1 = Convert.ToDateTime(this.dateTimePickerstart.Value.Date.ToString("yyyy-MM-dd")); DateTime time2 = Convert.ToDateTime(this.dateTimePickerend.Value.Date.ToString("yyyy-MM-dd")); if (DateTime.Compare(time1, time2) > 0) //判断日期大小 { MessageBox.Show("开始日期大于结束"); return; } string startTime = this.dateTimePickerstart.Value.ToString("yyyy-MM-dd"); string endTime = this.dateTimePickerend.Value.ToString("yyyy-MM-dd"); List <RepairRecordStruct> receiveOrderList = new List <RepairRecordStruct>(); List <string> tracknolist = new List <string>();//用来判别是否有重复项问题 List <RepairRecordStruct> receiveOrderListtarget = new List <RepairRecordStruct>(); try { SqlConnection mConn = new SqlConnection(Constlist.ConStr); mConn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = mConn; cmd.CommandType = CommandType.Text; string subsql = ""; if (this.vendorComboBox.Text.Trim() != "") { subsql += " and vendor='" + this.vendorComboBox.Text.Trim() + "'"; } if (this.productComboBox.Text.Trim() != "") { subsql += " and product='" + this.productComboBox.Text.Trim() + "'"; } string sql = ""; SqlDataReader querySdr = null; if (isTest) { sql = "SELECT A.track_serial_no from testalltable as A inner join repair_record_table as B on A.track_serial_no =B.track_serial_no " + "where A.test_date between '" + startTime + "' and '" + endTime + "' "; if (subsql != "") { sql += subsql; } cmd.CommandText = sql; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { RepairRecordStruct temp = new RepairRecordStruct(); temp.track_serial_no = querySdr[0].ToString().ToUpper(); //temp.repair_Num = querySdr[1].ToString(); if (tracknolist.Contains(temp.track_serial_no) == false) { tracknolist.Add(temp.track_serial_no); receiveOrderList.Add(temp); } } querySdr.Close(); sql = "SELECT A.track_serial_no from test2table as A inner join repair_record_table as B on A.track_serial_no =B.track_serial_no " + "where A.test_date between '" + startTime + "' and '" + endTime + "' "; if (subsql != "") { sql += subsql; } cmd.CommandText = sql; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { RepairRecordStruct temp = new RepairRecordStruct(); temp.track_serial_no = querySdr[0].ToString().ToUpper(); //temp.repair_Num = querySdr[1].ToString(); if (tracknolist.Contains(temp.track_serial_no) == false) { tracknolist.Add(temp.track_serial_no); receiveOrderList.Add(temp); } } querySdr.Close(); foreach (RepairRecordStruct repairRecord in receiveOrderList) { RepairRecordStruct temp = repairRecord; sql = "SELECT track_serial_no,COUNT(*) from repair_record_table where track_serial_no='" + repairRecord.track_serial_no + "' group by track_serial_no"; cmd.CommandText = sql; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { temp.repair_Num = querySdr[1].ToString(); } querySdr.Close(); } } else//此时走外观逻辑 { // sql = "SELECT track_serial_no,COUNT(*) from outlookcheck where test_date between '" + startTime + "' and '" + endTime + "' group by track_serial_no"; sql = "SELECT A.track_serial_no from outlookcheck as A inner join repair_record_table as B on A.track_serial_no =B.track_serial_no " + "where A.test_date between '" + startTime + "' and '" + endTime + "' "; if (subsql != "") { sql += subsql; } cmd.CommandText = sql; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { RepairRecordStruct temp = new RepairRecordStruct(); temp.track_serial_no = querySdr[0].ToString().ToUpper(); //temp.repair_Num = querySdr[1].ToString(); if (tracknolist.Contains(temp.track_serial_no) == false) { tracknolist.Add(temp.track_serial_no); receiveOrderList.Add(temp); } } querySdr.Close(); foreach (RepairRecordStruct repairRecord in receiveOrderList) { RepairRecordStruct temp = repairRecord; sql = "SELECT track_serial_no,COUNT(*) from repair_record_table where track_serial_no='" + repairRecord.track_serial_no + "' group by track_serial_no"; cmd.CommandText = sql; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { temp.repair_Num = querySdr[1].ToString(); } querySdr.Close(); } } foreach (RepairRecordStruct repairRecord in receiveOrderList) { receiveOrderListtarget.Add(repairRecord); } foreach (RepairRecordStruct repairRecord in receiveOrderListtarget) { string sqltest = "select vendor,product,source_brief,custom_order,order_receive_date,custommaterialNo," + "custom_serial_no,mb_describe,mb_brief,vendor_serail_no,mpn,mb_make_date,custom_fault,lenovo_maintenance_no from DeliveredTable where track_serial_no ='" + repairRecord.track_serial_no + "' "; cmd.CommandText = sqltest; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { repairRecord.vendor = querySdr[0].ToString(); repairRecord.product = querySdr[1].ToString(); repairRecord.source = querySdr[2].ToString(); repairRecord.order_no = querySdr[3].ToString(); repairRecord.receivedate = querySdr[4].ToString(); repairRecord.custommaterialNo = querySdr[5].ToString(); repairRecord.custom_serial_no = querySdr[6].ToString(); repairRecord.mb_describe = querySdr[7].ToString(); repairRecord.mb_brief = querySdr[8].ToString(); repairRecord.vendor_serail_no = querySdr[9].ToString(); repairRecord.mpn = querySdr[10].ToString(); repairRecord.mb_make_date = querySdr[11].ToString(); repairRecord.custom_fault = querySdr[12].ToString(); repairRecord.lenovo_maintenance_no = querySdr[13].ToString(); break; } querySdr.Close(); cmd.CommandText = "select top 1 Id,short_cut,fault_type,repairer,repair_date,repair_result,software_update from repair_record_table where track_serial_no ='" + repairRecord.track_serial_no + "' order by id desc"; querySdr = cmd.ExecuteReader(); repairRecord.fault_describeList = new List <string>(); repairRecord.mbfaList = new List <string>(); while (querySdr.Read()) { repairRecord.shortcut = querySdr[1].ToString(); // repairRecord.fault_type = querySdr[2].ToString(); repairRecord.repairer = querySdr[3].ToString(); repairRecord.repair_date = querySdr[4].ToString(); repairRecord.repair_result = querySdr[5].ToString(); repairRecord.software_update = querySdr[6].ToString(); } querySdr.Close(); cmd.CommandText = "select top 3 repair_result, fault_describe,mbfa1,fault_type from repair_record_table where track_serial_no ='" + repairRecord.track_serial_no + "' order by id desc"; querySdr = cmd.ExecuteReader(); repairRecord.fault_describeList = new List <string>(); repairRecord.mbfaList = new List <string>(); while (querySdr.Read()) { if (Int16.Parse(repairRecord.repair_Num) > 1) { if (querySdr[0].ToString() == "NTF待测") { continue; } else { repairRecord.repair_result = querySdr[0].ToString(); repairRecord.fault_describeList.Add(querySdr[1].ToString()); repairRecord.mbfaList.Add(querySdr[2].ToString()); repairRecord.fault_type = querySdr[3].ToString(); } } else { repairRecord.repair_result = querySdr[0].ToString(); repairRecord.fault_describeList.Add(querySdr[1].ToString()); repairRecord.mbfaList.Add(querySdr[2].ToString()); repairRecord.fault_type = querySdr[3].ToString(); } } querySdr.Close(); cmd.CommandText = "select bgatype,BGAPN,BGA_place from bga_repair_record_table where track_serial_no ='" + repairRecord.track_serial_no + "' and bga_repair_result='更换OK待测量' "; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { string type = querySdr[0].ToString(); switch (type) { case "CPU": repairRecord.cpu = querySdr[1].ToString(); repairRecord.cpu_place = querySdr[2].ToString(); repairRecord.fault_type = "功能不良"; //针对只换BGA的,但是小材料信息没有的情况 break; case "PCH": repairRecord.pch = querySdr[1].ToString(); repairRecord.pch_place = querySdr[2].ToString(); repairRecord.fault_type = "功能不良"; //针对只换BGA的,但是小材料信息没有的情况 break; case "VGA": repairRecord.vga = querySdr[1].ToString(); repairRecord.vga_place = querySdr[2].ToString(); repairRecord.fault_type = "功能不良"; //针对只换BGA的,但是小材料信息没有的情况 break; } } querySdr.Close(); cmd.CommandText = "select material_mpn,stock_place from fru_smt_used_record where track_serial_no ='" + repairRecord.track_serial_no + "'"; querySdr = cmd.ExecuteReader(); repairRecord.smtRecords = new List <SmtRecort>(); while (querySdr.Read()) { SmtRecort sub = new SmtRecort(); sub.smtMpn = querySdr[0].ToString(); sub.smtplace = querySdr[1].ToString(); repairRecord.smtRecords.Add(sub); } querySdr.Close(); cmd.CommandText = "select top 1 tester,test_date from test2table where track_serial_no ='" + repairRecord.track_serial_no + "' order by test_date desc"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { repairRecord.tester = querySdr[0].ToString(); repairRecord.test_date = querySdr[1].ToString(); } querySdr.Close(); cmd.CommandText = "select top 1 tester,test_date from testalltable where track_serial_no ='" + repairRecord.track_serial_no + "' order by test_date desc"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { repairRecord.tester = querySdr[0].ToString(); repairRecord.test_date = querySdr[1].ToString(); } querySdr.Close(); cmd.CommandText = "select top 1 custom_serial_no from cidRecord where track_serial_no ='" + repairRecord.track_serial_no + "'"; querySdr = cmd.ExecuteReader(); if (querySdr.HasRows) { repairRecord.is_cid = "是"; } querySdr.Close(); //修改最终结果repairRecord.repair_result if (repairRecord.pch == "" && repairRecord.pch_place == "" && repairRecord.vga == "" && repairRecord.vga_place == "" && repairRecord.cpu == "" && repairRecord.cpu_place == "" && repairRecord.smtRecords.Count == 0) { repairRecord.repair_result = "NTF测试OK"; } else { repairRecord.repair_result = "修复良品"; } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } generateExcelToCheck(receiveOrderListtarget, startTime, endTime); }
private void exportxmlbutton_Click(object sender, EventArgs e) { DateTime time1 = Convert.ToDateTime(this.dateTimePickerstart.Value.Date.ToString("yyyy/MM/dd")); DateTime time2 = Convert.ToDateTime(this.dateTimePickerend.Value.Date.ToString("yyyy/MM/dd")); if (DateTime.Compare(time1, time2) > 0) //判断日期大小 { MessageBox.Show("开始日期大于结束"); return; } string startTime = this.dateTimePickerstart.Value.ToString("yyyy/MM/dd"); string endTime = this.dateTimePickerend.Value.ToString("yyyy/MM/dd"); List <RepairRecordStruct> receiveOrderList = new List <RepairRecordStruct>(); try { SqlConnection mConn = new SqlConnection(Constlist.ConStr); mConn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = mConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT track_serial_no,COUNT(*) from repair_record_table where repair_date between '" + startTime + "' and '" + endTime + "' group by track_serial_no"; SqlDataReader querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { RepairRecordStruct temp = new RepairRecordStruct(); temp.track_serial_no = querySdr[0].ToString(); temp.repair_Num = querySdr[1].ToString(); receiveOrderList.Add(temp); } querySdr.Close(); foreach (RepairRecordStruct repairRecord in receiveOrderList) { cmd.CommandText = "select vendor,product,receivedate,mb_describe,mb_brief,custom_serial_no,vendor_serail_no,mpn,repairer from repair_record_table where track_serial_no ='" + repairRecord.track_serial_no + "'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { repairRecord.vendor = querySdr[0].ToString(); repairRecord.product = querySdr[1].ToString(); repairRecord.receivedate = querySdr[2].ToString(); repairRecord.mb_describe = querySdr[3].ToString(); repairRecord.mb_brief = querySdr[4].ToString(); repairRecord.custom_serial_no = querySdr[5].ToString(); repairRecord.vendor_serail_no = querySdr[6].ToString(); repairRecord.mpn = querySdr[7].ToString(); repairRecord.repairer = querySdr[8].ToString(); break; } querySdr.Close(); cmd.CommandText = "select _action,repair_result,repair_date,fault_describe from repair_record_table where track_serial_no ='" + repairRecord.track_serial_no + "'"; querySdr = cmd.ExecuteReader(); repairRecord.subRecords = new List <SubRepairRecord>(); while (querySdr.Read()) { SubRepairRecord sub = new SubRepairRecord(); sub._action = querySdr[0].ToString(); sub.repair_result = querySdr[1].ToString(); sub.repair_date = querySdr[2].ToString(); sub.fault_describe = querySdr[3].ToString(); repairRecord.subRecords.Add(sub); } querySdr.Close(); cmd.CommandText = "select BGAPN,bgatype,bga_brief,BGA_place from bga_repair_record_table where track_serial_no ='" + repairRecord.track_serial_no + "' and newSn !=''"; querySdr = cmd.ExecuteReader(); repairRecord.bgaRecords = new List <BgaRecord>(); while (querySdr.Read()) { BgaRecord sub = new BgaRecord(); sub.bgampn = querySdr[0].ToString(); sub.bgatype = querySdr[1].ToString(); sub.bgabrief = querySdr[2].ToString(); sub.bga_place = querySdr[3].ToString(); repairRecord.bgaRecords.Add(sub); } querySdr.Close(); cmd.CommandText = "select material_mpn,thisNumber,stock_place from fru_smt_used_record where track_serial_no ='" + repairRecord.track_serial_no + "'"; querySdr = cmd.ExecuteReader(); repairRecord.smtRecords = new List <SmtRecort>(); while (querySdr.Read()) { SmtRecort sub = new SmtRecort(); sub.smtMpn = querySdr[0].ToString(); sub.smtNum = querySdr[1].ToString(); sub.smtplace = querySdr[2].ToString(); repairRecord.smtRecords.Add(sub); } querySdr.Close(); } // foreach (RepairRecordStruct repairRecord in receiveOrderList) { foreach (BgaRecord bgarecord in repairRecord.bgaRecords) { cmd.CommandText = "select mbfa1,short_cut from bga_wait_record_table where track_serial_no ='" + repairRecord.track_serial_no + "' and bgatype='" + bgarecord.bgatype + "' and _status='BGA不良'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { bgarecord.bgambfa1 = querySdr[0].ToString(); bgarecord.bgashort_cut = querySdr[1].ToString(); break; } querySdr.Close(); } } mConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } generateExcelToCheck(receiveOrderList, startTime, endTime); }