mes-client/UAS_MES_JH/FunctionCode/Special/Special_SmtLocation.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using UAS_MES_NEW.DataOperate;
using UAS_MES_NEW.Entity;
using UAS_MES_NEW.PublicForm;
using UAS_MES_NEW.PublicMethod;

namespace UAS_MES_NEW.Special
{
    public partial class Special_SmtLocation : Form
    {
        DataHelper dh;

        LogStringBuilder sql = new LogStringBuilder();
        DataTable dt = new DataTable();
        DataTable dbfind;
        ExcelHandler eh;
        DataTable dbbom;

        Thread thread;


        public Special_SmtLocation()
        {
            InitializeComponent();
        }

        private void Special_BoxSplit_Load(object sender, EventArgs e)
        {
             eh = new ExcelHandler();
            dh = SystemInf.dh;

        }

        private void Pr_code_DbChange(object sender, EventArgs e)
        {

            BaseUtil.SetFormValue(this.Controls, dbfind);
        }

        private void normalButton2_Click(object sender, EventArgs e)
        {
            DialogResult result = openFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
            }
        }

        //创建打印进程
        private void NewPrint()
        {
            try
            {
                BaseUtil.FillDgvWithDataTable(LabelDataGridView, dt);

                int number = 0;

                List<string> snlist = new List<string>();
                List<string> imei1list = new List<string>();
                List<string> imei2list = new List<string>();
                List<string> btlist = new List<string>();
                List<string> wifilist = new List<string>();

                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    if ( dt.Rows[i]["SN"].ToString().Trim().Length > 2 && dt.Rows[i]["IMEI1"].ToString().Trim().Length > 2 )
                    {
                        snlist.Add(dt.Rows[i]["SN"].ToString().Trim());
                        imei1list.Add(dt.Rows[i]["IMEI1"].ToString().Trim());
                        imei2list.Add(dt.Rows[i]["IMEI2"].ToString().Trim());
                        btlist.Add(dt.Rows[i]["BT"].ToString().Trim());
                        wifilist.Add(dt.Rows[i]["WIFI"].ToString().Trim());                
                        number++;
                    }
                  

                   
                    // dh.ExecuteSql("MERGE INTO SNIMEI a USING ( SELECT '" + dt.Rows[i]["SN"].ToString() + "' as SN FROM dual ) b ON (a.SN = b.SN) WHEN MATCHED THEN UPDATE SET a.IMEI1 = '" + dt.Rows[i]["IMEI1"].ToString() + "', a.IMEI2 = '" + dt.Rows[i]["IMEI2"].ToString() + "',a.BT = '" + dt.Rows[i]["BT"].ToString() + "', a.WIFI = '" + dt.Rows[i]["WIFI"].ToString() + "' WHEN NOT MATCHED THEN INSERT (a.SN, a.IMEI1, a.IMEI2,a.BT, a.WIFI) VALUES ('" + dt.Rows[i]["SN"].ToString() + "', '" + dt.Rows[i]["IMEI1"].ToString() + "', '" + dt.Rows[i]["IMEI2"].ToString() + "','" + dt.Rows[i]["BT"].ToString() + "','" + dt.Rows[i]["WIFI"].ToString() + "')", "insert");
                }

                sql.Clear();
                sql.Append("insert into SNIMEI_TEMP(ST_SN,ST_IMEI1,ST_IMEI2,ST_BT,ST_WIFI)");
                sql.Append("values(:sn,:imei1,:imei2,:bt,:wifi)");

                dh.BatchInsert(sql.GetString(), new string[] { "sn", "imei1", "imei2", "bt", "wifi" }, snlist.ToArray(), imei1list.ToArray(), imei2list.ToArray(), btlist.ToArray(), wifilist.ToArray());


                sql.Clear();
                sql.Append("merge  into SNIMEI using SNIMEI_TEMP  on (ST_SN = SN) when  MATCHED then UPDATE set IMEI1 = ST_IMEI1,IMEI2=ST_IMEI2,BT=ST_BT,WIFI=ST_WIFI,SDATE = SYSDATE when not MATCHED then insert  (SN, IMEI1, IMEI2,BT, WIFI,SDATE)");
                sql.Append(" values (ST_SN,ST_IMEI1,ST_IMEI2,ST_BT,ST_WIFI,SYSDATE)");
                dh.ExecuteSql(sql.GetString(), "insert");

                dh.ExecuteSql("delete SNIMEI_TEMP", "delete");
                MessageBox.Show("导入成功！记录：" + dt.Rows.Count + "行");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

          
        }

        private void normalButton3_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                MessageBox.Show("文件地址不可为空");
                return;
            }
            dt =  eh.ImportExcel(textBox1.Text, "IMEI");



            thread = new Thread(NewPrint);
            SetLoadingWindow stw = new SetLoadingWindow(thread, "请稍候...");
            BaseUtil.SetFormCenter(stw);
            stw.ShowDialog();

            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    MessageBox.Show(dt.Rows[i]["RefDes"].ToString()+":"+ dt.Rows[i]["Layer"].ToString());
            //}



        }
    }
}