public bool equals(allSumStruct temp)
{
return(this.mb_brief == temp.mb_brief);
}
private void exportxmlbutton_Click(object sender, EventArgs e)
{
exportxmlbutton.Enabled = false;
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<nb_aio_mblistSheet5_6> repairList = new List<nb_aio_mblistSheet5_6>();
List <nb_aio_mblistSheet5_6> repairListtarget = new List <nb_aio_mblistSheet5_6>();
List <nb_aio_mblistSheet5_6> repairListtargetAIO = new List <nb_aio_mblistSheet5_6>();
List <nb_aio_mblistSheet5_6> repairListtargetNB = new List <nb_aio_mblistSheet5_6>();
// List<allReturnOrderStruct> allReturnOrderList = new List<allReturnOrderStruct>();
List <allSumStruct> allSumStructListAIO = new List <allSumStruct>();
List <allSumStruct> allSumStructListNB = new List <allSumStruct>();
List <fruListSheet4> fruList = new List <fruListSheet4>();
debitnotsSheet3 debitnots = new debitnotsSheet3();
try
{
SqlConnection mConn = new SqlConnection(Constlist.ConStr);
mConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = mConn;
cmd.CommandType = CommandType.Text;
//查询fru的记录
cmd.CommandText = "SELECT receive_date,orderno,customermaterialno,machine_type,name,peijian_no,make_date,gurantee,vendor_material_no,mpn1,custom_fault from fruDeliveredTable where receive_date between '" + startTime + "' and '" + endTime + "' and vendor='COMPAL'";
SqlDataReader querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
fruListSheet4 temp = new fruListSheet4();
temp.receive_date = querySdr[0].ToString();
temp.orderno = querySdr[1].ToString();
temp.customermaterialno = querySdr[2].ToString();
temp.machine_type = querySdr[3].ToString();
temp.name = querySdr[4].ToString();
temp.peijian_no = querySdr[5].ToString();
temp.make_date = querySdr[6].ToString();
temp.gurantee = querySdr[7].ToString();
temp.vendor_material_no = querySdr[8].ToString();
temp.mpn1 = querySdr[9].ToString();
temp.custom_fault = querySdr[10].ToString();
fruList.Add(temp);
}
querySdr.Close();
foreach (fruListSheet4 temp in fruList)
{
//查询fru是否计费
cmd.CommandText = "SELECT eol from frubomtable where custom_material_no='" + temp.customermaterialno + "'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
temp.eol = querySdr[0].ToString();
break;
}
querySdr.Close();
}
///维修记录
cmd.CommandText = "SELECT track_serial_no,COUNT(*) from repair_record_table where repair_date between '" + startTime + "' and '" + endTime + "' and vendor='COMPAL' group by track_serial_no";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
nb_aio_mblistSheet5_6 temp = new nb_aio_mblistSheet5_6();
temp.track_serial_no = querySdr[0].ToString();
temp.repair_Num = querySdr[1].ToString();
repairListtarget.Add(temp);
}
querySdr.Close();
foreach (nb_aio_mblistSheet5_6 repairRecord in repairListtarget)
{
cmd.CommandText = "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 + "'";
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 eol from MBMaterialCompare where custommaterialNo ='" + repairRecord.custommaterialNo + "'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
repairRecord.eol = querySdr[0].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 <allSmtRecort>();
while (querySdr.Read())
{
allSmtRecort sub = new allSmtRecort();
sub.smtMpn = querySdr[0].ToString();
sub.smtplace = querySdr[1].ToString();
repairRecord.smtRecords.Add(sub);
}
querySdr.Close();
cmd.CommandText = "select top 1 tester from test1table where track_serial_no ='" + repairRecord.track_serial_no + "'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
repairRecord.tester = querySdr[0].ToString();
}
querySdr.Close();
cmd.CommandText = "select top 1 tester from testalltable where track_serial_no ='" + repairRecord.track_serial_no + "'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
repairRecord.tester = querySdr[0].ToString();
}
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 = "修复良品";
}
}
int frucount = 0;
foreach (nb_aio_mblistSheet5_6 repairRecord in repairListtarget)
{
if (repairRecord.product == "AIO" && repairRecord.eol.Trim() == "Free")
{
repairListtargetAIO.Add(repairRecord);
}
else if (repairRecord.product == "LBG" && repairRecord.eol.Trim() == "Free")
{
repairListtargetNB.Add(repairRecord);
}
//统计信息
allSumStruct temp1 = new allSumStruct();
temp1.mb_brief = repairRecord.mb_brief;
if (repairRecord.product == "AIO")
{
if (allSumStructListAIO.Count == 0)
{
if (temp1.mb_brief != null && temp1.mb_brief.Trim() != "")
{
allSumStructListAIO.Add(temp1);
}
}
else
{
bool exist = false;
foreach (allSumStruct oldrecord in allSumStructListAIO)
{
if (oldrecord.equals(temp1))
{
oldrecord.incrementNum();
exist = true;
break;
}
}
if (exist == false)
{
if (temp1.mb_brief != null && temp1.mb_brief.Trim() != "")
{
allSumStructListAIO.Add(temp1);
}
}
}
}
else if (repairRecord.product == "LBG")
{
if (allSumStructListNB.Count == 0)
{
if (temp1.mb_brief != null && temp1.mb_brief.Trim() != "")
{
allSumStructListNB.Add(temp1);
}
}
else
{
bool exist = false;
foreach (allSumStruct oldrecord in allSumStructListNB)
{
if (oldrecord.equals(temp1))
{
oldrecord.incrementNum();
exist = true;
break;
}
}
if (exist == false)
{
if (temp1.mb_brief != null && temp1.mb_brief.Trim() != "")
{
allSumStructListNB.Add(temp1);
}
}
}
}
}
foreach (fruListSheet4 temp in fruList)
{
if (temp.eol.Trim() == "Free")
{
frucount++;
}
}
//修改debitnots中部分数据，todo
debitnots.dateG5 = endTime;
debitnots.invF6 = "Inv#HWB" + endTime;
debitnots.contentD14 = startTime;
debitnots.contentE14 = endTime;
debitnots.contentD16 = repairListtargetAIO.Count + "";
debitnots.contentD17 = repairListtargetNB.Count + "";
debitnots.contentD20 = frucount + "";
debitnots.contentF16 = "$" + (Int16.Parse(debitnots.contentD16) * Double.Parse(debitnots.contentE16.Replace("$", "").Trim()));
debitnots.contentF17 = "$" + (Int16.Parse(debitnots.contentD17) * Double.Parse(debitnots.contentE17.Replace("$", "").Trim()));
debitnots.contentF20 = "$" + (Int16.Parse(debitnots.contentD20) * Double.Parse(debitnots.contentE20.Replace("$", "").Trim()));
debitnots.contentF22 = "$" + (Double.Parse(debitnots.contentF16.Replace("$", "").Trim()) +
Double.Parse(debitnots.contentF17.Replace("$", "").Trim()) +
Double.Parse(debitnots.contentF20.Replace("$", "").Trim()));
//debitnots.contentF24?如何计算
//debitnots.contentF25?如何计算
debitnots.contentF27 = "$" + (Double.Parse(debitnots.contentF22.Replace("$", "").Trim()) +
Double.Parse(debitnots.contentF24.Replace("$", "").Trim()) +
Double.Parse(debitnots.contentF25.Replace("$", "").Trim()));//结果计算如何 ？
debitnots.contentE31 = "韩宝军" + endTime;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
generateExcelToCheck(debitnots, allSumStructListAIO, allSumStructListNB, fruList, repairListtargetAIO, repairListtargetNB, startTime, endTime);
}
