private void button1_Click(object sender, EventArgs e) { DateTime time1 = Convert.ToDateTime(this.dateTimePickerstart.Value.Date.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)); DateTime time2 = Convert.ToDateTime(this.dateTimePickerend.Value.Date.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)); if (DateTime.Compare(time1, time2) > 0) //判断日期大小 { MessageBox.Show("开始日期大于结束"); return; } if (this.vendorComboBox.Text.Trim() == "" || this.productComboBox.Text.Trim() == "") { MessageBox.Show("请选择厂商客户别"); return; } string startTime = this.dateTimePickerstart.Value.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo); string endTime = this.dateTimePickerend.Value.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo); List <HefeiDataStruct> receiveOrderList = new List <HefeiDataStruct>(); List <HefeiDataStruct> receiveOrdersource = new List <HefeiDataStruct>();//只记录没要还货记录的数据 try { SqlConnection mConn = new SqlConnection(Constlist.ConStr); mConn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = mConn; cmd.CommandType = CommandType.Text; //记录收货但是没有还货的记录 cmd.CommandText = "select A.track_serial_no,A.custom_order,A.custom_serial_no,A.mpn,A.dpk_status, " + //5列 " A.custommaterialNo,A.custom_machine_type,A.vendor,A.mb_brief,A.mb_make_date,A.order_receive_date," + //6列 "A.guarantee, A.custom_fault, A.warranty_period" + //3 " from DeliveredTable as A where A.order_receive_date between '" + startTime + "' and '" + endTime + "' and A.vendor ='" + this.vendorComboBox.Text.Trim() + "' and A.product='" + this.productComboBox.Text.Trim() + "'"; SqlDataReader querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { HefeiDataStruct temp = new HefeiDataStruct(); temp.tracker_no_receive = querySdr[0].ToString(); temp.REPAIR_CENTER = "HWB"; temp.RMA_NO = querySdr[1].ToString(); temp.PRODUCT = "MB"; temp.PRODUCT_SN = querySdr[2].ToString(); temp.SHIPPING_SN = ""; temp.LCFC_PN = querySdr[3].ToString(); temp.OS_VERSION = querySdr[4].ToString(); temp.LNV_FRU_PN = querySdr[5].ToString(); temp.LNV_MODEL_NAME = querySdr[6].ToString(); temp.LNV_SERIES = ""; //temp.PRODUCT = querySdr[7].ToString(); temp.LCFC_MODEL_NAME = querySdr[8].ToString(); temp.PRODUCT_DAY_CODE = querySdr[9].ToString(); temp.RETURN_AREA = "CHINA"; temp.SERVICE_REQUESTER = "LENOVO"; temp.SHIP_BACK_DATE = querySdr[10].ToString(); //收货日期 temp.SVC_RECEIVE_DATE = querySdr[10].ToString(); //同一天 temp.SERVICE_TYPE = (querySdr[11].ToString() == "保内" ? "IW" : "OOW"); temp.INCOMING_INSPECTION = ""; temp.LINE_INPUT_DATE = querySdr[10].ToString(); temp.REPAIR_START_DATE = querySdr[10].ToString(); temp.PACKING_DATE = "";//还货日期 temp.DELIVERY_DATE = ""; temp.CLOSE_DATE = ""; temp.NORMAL_SYMPTOM = querySdr[12].ToString(); temp.tracker_no_return = querySdr[0].ToString();//还没有还货的使用收货跟踪条码替代 temp.Q_A_Result = ""; temp.DELIVERY = "Y"; temp.TAT_TARGET = "7"; temp.warranty_period = querySdr[13].ToString(); temp.repairDetailList = new List <repairDetail>(); receiveOrdersource.Add(temp); } querySdr.Close(); //记录还货的记录 cmd.CommandText = "select A.track_serial_no,A.custom_order,A.custom_serial_no,B.custom_serial_no,A.mpn,A.dpk_status, " + //6列 " A.custommaterialNo,A.custom_machine_type,A.vendor,A.mb_brief,A.mb_make_date,A.order_receive_date," + //6列 "A.guarantee, B._status,B.return_date,A.custom_fault,B.track_serial_no, A.warranty_period" + //5 " from DeliveredTable as A, returnStore as B where A.receiveOrderIndex = B.returnOrderIndex and B.return_date between '" + startTime + "' and '" + endTime + "' and B.vendor ='" + this.vendorComboBox.Text.Trim() + "' and B.product='" + this.productComboBox.Text.Trim() + "'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { HefeiDataStruct temp = new HefeiDataStruct(); temp.tracker_no_receive = querySdr[0].ToString(); temp.REPAIR_CENTER = "HWB"; temp.RMA_NO = querySdr[1].ToString(); temp.PRODUCT = "MB"; temp.PRODUCT_SN = querySdr[2].ToString(); temp.SHIPPING_SN = querySdr[3].ToString(); temp.LCFC_PN = querySdr[4].ToString(); temp.OS_VERSION = querySdr[5].ToString(); temp.LNV_FRU_PN = querySdr[6].ToString(); temp.LNV_MODEL_NAME = querySdr[7].ToString(); temp.LNV_SERIES = ""; //temp.PRODUCT = querySdr[8].ToString(); temp.LCFC_MODEL_NAME = querySdr[9].ToString(); temp.PRODUCT_DAY_CODE = querySdr[10].ToString(); temp.RETURN_AREA = "CHINA"; temp.SERVICE_REQUESTER = "LENOVO"; temp.SHIP_BACK_DATE = querySdr[11].ToString(); //收货日期 temp.SVC_RECEIVE_DATE = querySdr[11].ToString(); //同一天 temp.SERVICE_TYPE = (querySdr[12].ToString() == "保内" ? "IW" : "OOW"); temp.INCOMING_INSPECTION = (querySdr[13].ToString() == "良品" ? "IW" : "CID"); if (temp.INCOMING_INSPECTION == "CID") { temp.SHIPPING_SN = temp.SHIPPING_SN + "," + temp.PRODUCT_SN; } temp.LINE_INPUT_DATE = querySdr[11].ToString(); temp.REPAIR_START_DATE = querySdr[11].ToString(); temp.PACKING_DATE = querySdr[14].ToString();//还货日期 temp.DELIVERY_DATE = querySdr[14].ToString(); temp.CLOSE_DATE = querySdr[14].ToString(); temp.NORMAL_SYMPTOM = querySdr[15].ToString(); temp.tracker_no_return = querySdr[16].ToString(); temp.Q_A_Result = ""; temp.DELIVERY = "Y"; temp.TAT_TARGET = "7"; temp.warranty_period = querySdr[17].ToString(); temp.repairDetailList = new List <repairDetail>(); foreach (HefeiDataStruct temp1 in receiveOrdersource) { if (temp1.tracker_no_receive.Trim() == temp.tracker_no_receive.Trim()) { receiveOrdersource.Remove(temp1); Console.WriteLine(temp1.tracker_no_receive); break; } } receiveOrderList.Add(temp); } querySdr.Close(); foreach (HefeiDataStruct temp in receiveOrdersource) { receiveOrderList.Add(temp); } foreach (HefeiDataStruct temp in receiveOrderList) { DateTime mb_makedate = Convert.ToDateTime(temp.PRODUCT_DAY_CODE); DateTime mb_reciveDate = Convert.ToDateTime(temp.SVC_RECEIVE_DATE); DateTime waranty_date = mb_makedate.AddMonths(Int16.Parse(temp.warranty_period.Replace('M', ' ').Trim())); DateTime subOneDay = mb_reciveDate.AddDays(-1); temp.WARRANTY_PERIOD = waranty_date.ToString("yyyy-MM-dd"); temp.REQUEST_DATE = subOneDay.ToString("yyyy-MM-dd"); temp.APPROVAL_DATE = subOneDay.ToString("yyyy-MM-dd"); if (temp.SHIP_BACK_DATE != "" && temp.PACKING_DATE != "") { DateTime dt1 = Convert.ToDateTime(temp.SHIP_BACK_DATE); DateTime dt2 = Convert.ToDateTime(temp.PACKING_DATE); TimeSpan ts = dt2.Subtract(dt1); int overdays = ts.Days; if (overdays > 7) { temp.TAT_HIT = "N"; } else { temp.TAT_HIT = "Y"; } temp.TAT = overdays + ""; } cmd.CommandText = "select top 1 test_date from test1table where track_serial_no='" + temp.tracker_no_receive + "' order by Id desc"; querySdr = cmd.ExecuteReader(); temp.FINAL_TEST_DATE = null; while (querySdr.Read()) { temp.FINAL_TEST_DATE = querySdr[0].ToString(); temp.WH_TAKEIN_DATE = querySdr[0].ToString(); } querySdr.Close(); if (temp.FINAL_TEST_DATE == null) { cmd.CommandText = "select top 1 test_date from testalltable where track_serial_no='" + temp.tracker_no_receive + "' order by Id desc"; querySdr = cmd.ExecuteReader(); temp.FINAL_TEST_DATE = null; while (querySdr.Read()) { temp.FINAL_TEST_DATE = querySdr[0].ToString(); temp.WH_TAKEIN_DATE = querySdr[0].ToString(); } querySdr.Close(); } //维修记录,首先查询BGA然后查询小材料 cmd.CommandText = "select BGA_place, bgatype,BGAPN from bga_repair_record_table where track_serial_no ='" + temp.tracker_no_receive + "' and newSn !='' order by Id desc"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { repairDetail repair = new repairDetail(); repair.REPAIR1_LOCATION = querySdr[0].ToString(); repair.REPAIR1_ACTION = "CHANGE"; repair.REPAIR1_PARTS = querySdr[1].ToString(); repair.REPAIR1_USED_MATERIAL_CODE = querySdr[2].ToString(); repair.REPAIR1_USED_MATERIAL_QTY = "1"; repair.REPAIR1_RESULT = "REPAIR GOOD"; repair.REPAIR1_RESPONSIBILITY = "NORMAL"; temp.repairDetailList.Add(repair); } querySdr.Close(); cmd.CommandText = "select stock_place,material_mpn,_action,thisNumber from fru_smt_used_record where track_serial_no ='" + temp.tracker_no_receive + "'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { repairDetail repair = new repairDetail(); repair.REPAIR1_LOCATION = querySdr[0].ToString(); repair.REPAIR1_USED_MATERIAL_CODE = querySdr[1].ToString(); if (querySdr[2].ToString().Trim() != "") { repair.REPAIR1_PARTS = ""; repair.REPAIR1_USED_MATERIAL_QTY = ""; repair.REPAIR1_RESULT = ""; repair.REPAIR1_RESPONSIBILITY = ""; } string action = querySdr[3].ToString().Trim(); if (action.Contains("清洁") || action.Contains("加焊")) { repair.REPAIR1_ACTION = "MAINTAIN"; } else if (action.Contains("BIOS") || action.Contains("软体")) { repair.REPAIR1_ACTION = "UPDATE"; } else if (action.Contains("更换")) { repair.REPAIR1_ACTION = "CHANGE"; } temp.repairDetailList.Add(repair); } querySdr.Close(); if (temp.INCOMING_INSPECTION == "CID") { temp.REPAIR_RESULT = "NO REPAIR"; temp.REPAIR_RESPONSIBILITY = "CUSTOMER"; temp.RMA_STATUS = "COMPLETE"; } else if (temp.repairDetailList.Count == 0) { temp.REPAIR_RESULT = "NDF"; temp.REPAIR_RESPONSIBILITY = ""; temp.RMA_STATUS = "CLOSE"; } else { temp.REPAIR_RESULT = "REPAIR GOOD"; temp.REPAIR_RESPONSIBILITY = "NORMAL"; temp.RMA_STATUS = "COMPLETE"; } //如果不够5个数据,则补齐 while (temp.repairDetailList.Count < 5) { repairDetail repair = new repairDetail(); temp.repairDetailList.Add(repair); } //差一个RRR_90 TODO //规则:根据收货序号,查询其收货日期,然后查这个收货日期之前的还货记录,查最近的一次还货记录,如果在90天内则为1,否则为0 if (temp.LINE_INPUT_DATE != "") { string receiveDateEnd = temp.LINE_INPUT_DATE; string lastReturnDateStart = Convert.ToDateTime(receiveDateEnd).AddDays(-90).ToString("yyyy-MM-dd"); cmd.CommandText = "select top 1 return_date from returnStore where return_date between '" + lastReturnDateStart + "' and '" + receiveDateEnd + "' and custom_serial_no='" + temp.PRODUCT_SN + "'"; querySdr = cmd.ExecuteReader(); if (querySdr.HasRows) { temp.RRR_90 = "1"; } else { temp.RRR_90 = "0"; } querySdr.Close(); } } mConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } generateExcelToCheck(receiveOrderList, startTime, endTime); }
private void button1_Click(object sender, EventArgs e) { DateTime time1 = Convert.ToDateTime(this.dateTimePickerstart.Value.Date.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)); DateTime time2 = Convert.ToDateTime(this.dateTimePickerend.Value.Date.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)); if (DateTime.Compare(time1, time2) > 0) //判断日期大小 { MessageBox.Show("开始日期大于结束"); return; } if (this.vendorComboBox.Text.Trim() == "" || this.productComboBox.Text.Trim() == "") { MessageBox.Show("请选择厂商客户别"); return; } string startTime = this.dateTimePickerstart.Value.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo); string endTime = this.dateTimePickerend.Value.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo); List <CompalMonthlyDataStruct> receiveOrderList = new List <CompalMonthlyDataStruct>(); try { SqlConnection mConn = new SqlConnection(Constlist.ConStr); mConn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = mConn; cmd.CommandType = CommandType.Text; //记录还货的记录 cmd.CommandText = "select A.track_serial_no,A.custom_order,A.mb_brief,A.custommaterialNo,A.custom_serial_no,B.custom_serial_no,A.custom_fault, " + " B.return_date,B._status from DeliveredTable as A, returnStore as B where A.receiveOrderIndex = B.returnOrderIndex and B.return_date between '" + startTime + "' and '" + endTime + "' and B.vendor ='" + this.vendorComboBox.Text.Trim() + "' and B.product='" + this.productComboBox.Text.Trim() + "'"; SqlDataReader querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { CompalMonthlyDataStruct temp = new CompalMonthlyDataStruct(); temp.tracker_no_receive = querySdr[0].ToString(); temp.TrackNo = temp.tracker_no_receive; temp.RMACaseID = querySdr[1].ToString(); temp.Region = ""; temp.TPR = ""; temp.Model = querySdr[2].ToString(); temp.P_N = querySdr[3].ToString(); temp.PPID_IN = querySdr[4].ToString(); temp.PPID_OUT = querySdr[5].ToString(); temp.FAILURE_DESCRIPTIONS_Customer = querySdr[6].ToString(); temp.ERROR_CODE = ""; temp.FAILURE_DESCRIPTIONS_Compal = ""; temp.PART = ""; temp.ShippingDate = querySdr[7].ToString(); temp.repairDetailList = new List <repairDetail>(); temp.INCOMING_INSPECTION = (querySdr[8].ToString() == "良品" ? "IW" : "CID"); receiveOrderList.Add(temp); } querySdr.Close(); foreach (CompalMonthlyDataStruct temp in receiveOrderList) { DateTime shipdate = Convert.ToDateTime(temp.ShippingDate); DateTime subOneDay = shipdate.AddDays(-1); temp.ReceivedDate = subOneDay.ToString("yyyy-MM-dd"); cmd.CommandText = " select COUNT(*) from DeliveredTable where custom_serial_no ='" + temp.PPID_IN + "'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { temp.Return_times = querySdr[0].ToString(); } querySdr.Close(); //维修记录,首先查询BGA然后查询小材料 cmd.CommandText = "select BGA_place, bgatype,BGAPN,bga_repair_date from bga_repair_record_table where track_serial_no ='" + temp.tracker_no_receive + "' and newSn !='' order by Id desc"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { repairDetail repair = new repairDetail(); repair.REPAIR1_LOCATION = querySdr[0].ToString(); repair.REPAIR1_ACTION = "CHANGE"; repair.REPAIR1_PARTS = querySdr[1].ToString(); repair.REPAIR1_USED_MATERIAL_CODE = querySdr[2].ToString(); repair.REPAIR1_USED_MATERIAL_QTY = "1"; repair.REPAIR1_RESULT = "REPAIR GOOD"; repair.REPAIR1_RESPONSIBILITY = "NORMAL"; temp.RepairMonth = querySdr[3].ToString(); temp.repairDetailList.Add(repair); } querySdr.Close(); cmd.CommandText = "select stock_place,material_mpn,_action,thisNumber,input_date from fru_smt_used_record where track_serial_no ='" + temp.tracker_no_receive + "'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { repairDetail repair = new repairDetail(); repair.REPAIR1_LOCATION = querySdr[0].ToString(); repair.REPAIR1_USED_MATERIAL_CODE = querySdr[1].ToString(); if (querySdr[2].ToString().Trim() != "") { repair.REPAIR1_PARTS = ""; repair.REPAIR1_USED_MATERIAL_QTY = ""; repair.REPAIR1_RESULT = ""; repair.REPAIR1_RESPONSIBILITY = ""; } string action = querySdr[3].ToString().Trim(); if (action.Contains("清洁") || action.Contains("加焊")) { repair.REPAIR1_ACTION = "MAINTAIN"; } else if (action.Contains("BIOS") || action.Contains("软体")) { repair.REPAIR1_ACTION = "UPDATE"; } else if (action.Contains("更换")) { repair.REPAIR1_ACTION = "CHANGE"; } temp.RepairMonth = querySdr[4].ToString(); temp.repairDetailList.Add(repair); } querySdr.Close(); if (temp.INCOMING_INSPECTION == "CID") { temp.CATEGORY = "CID"; } else if (temp.repairDetailList.Count == 0) { temp.CATEGORY = "NTF"; } else { temp.CATEGORY = "ROK"; } foreach (repairDetail repairtemp in temp.repairDetailList) { temp.LOCATION += repairtemp.REPAIR1_LOCATION + ","; } } mConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } generateExcelToCheck(receiveOrderList, startTime, endTime); }