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