Exemplo n.º 1
0
 public bool equals(allSumStruct temp)
 {
     return(this.mb_brief == temp.mb_brief);
 }
Exemplo n.º 2
0
        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);
        }