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);
}