コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }