HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/99、未分類/Frm_DB_TRANS.vb

Imports System.Data.SqlClient
Imports System.Net
Imports Microsoft.Office.Interop
Public Class Frm_Import1
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim my_year111 As Integer
    Dim my_month111 As Integer
    'Dim my_year As Integer

    Dim sql As String
    Dim conn As New SqlConnection
    Private Sub FrmImport_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Me.MdiParent = FrmMDI


        If gUserName <> "matchy" Then


            Button17.Enabled = False
            Button18.Enabled = False
            Button19.Enabled = False
            Button20.Enabled = False
            Button21.Enabled = False
            Button22.Enabled = False
            Button23.Enabled = False
            Button26.Enabled = False
            Button27.Enabled = False





        End If






        ' my_year111 = Year(Today)



        my_month = Month(Today)
        If Month(Today) = 1 Then
            '  my_month = 12
            '  my_year111 = my_year - 1
        End If








        dgv1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText


        ini_update()





    End Sub
    Private Sub ini_update()



        conn.ConnectionString = connstring
        conn.Open()

        cmd.Connection = conn



        Dim dt As New DataTable


        sql = "Select top 1 date FROM rt_retan_record_d order by date desc"

        cmd.CommandText = sql

        TextBox21.Text = cmd.ExecuteScalar


        sql = "Select top 1 date FROM rt_chem_in_price order by date desc"

        cmd.CommandText = sql

        TextBox11.Text = cmd.ExecuteScalar


        sql = "Select top 1 date FROM rt_retan_record order by date desc"

        cmd.CommandText = sql

        TextBox1.Text = cmd.ExecuteScalar



        sql = "Select top 1 date FROM rt_pasting_daily order by date desc"

        cmd.CommandText = sql

        TextBox2.Text = cmd.ExecuteScalar

        sql = "Select top 1 date FROM RT_Vacumn_daily order by date desc"

        cmd.CommandText = sql

        TextBox3.Text = cmd.ExecuteScalar

        sql = "Select top 1 date4 FROM RT_hang_daily order by date4 desc"

        cmd.CommandText = sql

        TextBox4.Text = cmd.ExecuteScalar

        sql = "Select top 1 date1 FROM RT_Vibration_daily order by date1 desc"

        cmd.CommandText = sql

        TextBox5.Text = cmd.ExecuteScalar

        sql = "Select top 1 date1 FROM RT_Buff_daily order by date1 desc"

        cmd.CommandText = sql

        TextBox6.Text = cmd.ExecuteScalar

        sql = "Select top 1 date FROM RT_Burn_daily order by date desc"

        cmd.CommandText = sql

        TextBox8.Text = cmd.ExecuteScalar

        sql = "Select top 1 date FROM RT_Toggle_daily order by date desc"

        cmd.CommandText = sql

        TextBox7.Text = cmd.ExecuteScalar

        sql = "Select top 1 date FROM RT_Spray_daily order by date desc"

        cmd.CommandText = sql

        TextBox9.Text = cmd.ExecuteScalar



        sql = "Select top 1 date FROM RT_ship_detail order by date desc"

        cmd.CommandText = sql

        TextBox24.Text = cmd.ExecuteScalar



        sql = "Select top 1 date FROM RT_MEASURE_DAILY order by date desc"

        cmd.CommandText = sql

        TextBox10.Text = cmd.ExecuteScalar




        sql = "Select top 1 date FROM rt_sys_chem_out order by date desc"

        cmd.CommandText = sql

        TextBox15.Text = cmd.ExecuteScalar




        sql = "Select PERIOD AS 会计期间,ACCOUNTED AS 已结帐, DATE AS 结帐日 FROM RT_CHEM_ACCOUNTING"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        DGV_ACCOUNTING.DataSource = dt

        DGV_ACCOUNTING.Columns(0).Width = 95
        DGV_ACCOUNTING.Columns(1).Width = 55
        DGV_ACCOUNTING.Columns(2).Width = 95
        DGV_ACCOUNTING.AllowUserToAddRows = False

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click   '------化料领料

        Dim dt As New DataTable








        sql = "delete from rt_sys_chem_out where year(date)=" & Year(DTP1.Value) & " and month(date)=" & Month(DTP1.Value) & "" '----化料
        ' sql = "delete from rt_sys_chem_out "  '----化料
        cmd.CommandText = sql
            cmd.ExecuteNonQuery()


        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        Else
            Exit Sub
        End If


        Dim filepath As String = Me.Button1.Text

        Dim sourceFileName As String = file_name


        Dim address2 As String = "ftp://192.168.1.201:7777/" & filepath & "/" & OpenFileDialog1.SafeFileName

        Dim address3() As String

        address3 = Split(address2, "ftp://192.168.1.201:7777/")
        Dim add4 As String

        add4 = "d:\k3\" & address3(1)


        Dim userName As String = "admin"
        Dim password As String = "admin"
        Dim showUI As Boolean = True
        Dim connectionTimeout As Integer = 500


        Dim client As WebClient = New WebClient
        client.Credentials = New NetworkCredential(userName, password)
        client.Proxy = GlobalProxySelection.GetEmptyWebProxy()
        client.UploadFile(address2, sourceFileName)


        'If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

        '    file_name = OpenFileDialog1.FileName


        'End If




        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... [生产领料序时簿$]"



        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt



        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_sys_chem_out"
        my_sqlbulk.WriteToServer(dt)

        'MsgBox("导入完成!")


        Dim dt1 As New DataTable

        Dim card() As String
        Dim dr As SqlDataReader



        sql = "delete from rt_sys_chem_out where id=99999"
        cmd.CommandText = sql
        cmd.ExecuteScalar()


        sql = "SELECT card,avg(order_a) as wb_weight FROM RT_SYS_CHEM_OUT WHERE (LEN(CARD) > 13) group by card order by card"

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt1)


        For x As Integer = 0 To dt1.Rows.Count - 1

            card = Split(dt1.Rows(x).Item("card"), "+")

            Dim xx As Double = 0
            For y As Integer = 0 To UBound(card) - 1

                sql = " select wb_weight/2 as wb from rt_retan_record where right(card,10)='" & Microsoft.VisualBasic.Right(card(y), 10) & "' "
                cmd.CommandText = sql
                xx = cmd.ExecuteScalar

                If xx <> 0 Then
                    Dim dt2 As New DataTable
                    sql = "select * from rt_sys_chem_out where card='" & dt1.Rows(x).Item("card") & "' order by name"
                    cmd.CommandText = sql
                    da.SelectCommand = cmd
                    da.Fill(dt2)

                    For z = 0 To dt2.Rows.Count - 1

                        sql = "insert into rt_sys_chem_out (date,checked,dep,no,wh,code,name,type,unit,lot,weight,weight1,p,card,drum,order_a,id) " &
                           " values(N'" & dt2.Rows(z).Item("date") & "'," & "N'" & dt2.Rows(z).Item("checked") & "'," & "N'" & dt2.Rows(z).Item("dep") & "','" & dt2.Rows(z).Item("no") & "-1',N'" & dt2.Rows(z).Item("wh") & "',N'" & dt2.Rows(z).Item("code") & "',N'" & dt2.Rows(z).Item("name") & "',N'" & dt2.Rows(z).Item("type") & "','" & dt2.Rows(z).Item("unit") & "','" & dt2.Rows(z).Item("lot") & "','" & dt2.Rows(z).Item("weight") * xx / dt2.Rows(z).Item("order_A") & "','" & dt2.Rows(z).Item("weight1") & "',N'" & dt2.Rows(z).Item("p") & "',N'" & Microsoft.VisualBasic.Right(card(y), 10) & "',N'" & dt2.Rows(z).Item("drum") & "','" & xx & "','" & "99999" & "')"
                        cmd.CommandText = sql
                        cmd.ExecuteNonQuery()

                    Next

                End If
            Next

        Next

        MsgBox("")

    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_retan_record_d"  '-------染色
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['11$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt

        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_retan_record_d"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")



    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        Else
            Exit Sub

        End If

        sql = "delete from rt_pasting_daily" '-------贴板
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['13$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt

        Dim my_sqlbulk As New SqlBulkCopy(conn)
        my_sqlbulk.DestinationTableName = "dbo.rt_pasting_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")

    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_hang_daily"  '------吊干
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['16$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt

        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_hang_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")

    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_vibration_daily"    '-------打软
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['17$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt

        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_vibration_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")
    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_buff_daily"    '------磨皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['18$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt

        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_buff_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")
    End Sub

    Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_burn_daily"   '-----烧毛
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['19$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt


        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_burn_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")
    End Sub

    Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_spray_daily"    '------喷台
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['20$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt

        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_spray_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")
    End Sub

    Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_measure_daily"    '-------量皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['24-1$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt

        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_measure_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")
    End Sub

    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        Dim dt As New DataTable

        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_toggle_daily"   '-----挟皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['21$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt


        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_toggle_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")
    End Sub

    Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If

        sql = "delete from rt_vacumn_daily"     '------真空
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['14$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt

        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_vacumn_daily"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")
    End Sub

    Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click
        Dim dt As New DataTable

        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName
        Else
            Exit Sub

        End If

        Dim filepath As String = Me.Button12.Text

        Dim sourceFileName As String = file_name

        Dim address2 As String = "ftp://192.168.1.201:7777/" & filepath & "/" & OpenFileDialog1.SafeFileName

        Dim address3() As String

        address3 = Split(address2, "ftp://192.168.1.201:7777/")
        Dim add4 As String

        add4 = "d:\k3\" & address3(1)

        Dim userName As String = "admin"
        Dim password As String = "admin"
        Dim showUI As Boolean = True
        Dim connectionTimeout As Integer = 500


        Dim client As WebClient = New WebClient
        client.Credentials = New NetworkCredential(userName, password)
        client.Proxy = GlobalProxySelection.GetEmptyWebProxy()
        client.UploadFile(address2, sourceFileName)


        'My.Computer.Network.UploadFile(sourceFileName, address2, userName, password, showUI, connectionTimeout)
        'Dim request As FtpWebRequest = WebRequest.Create(address2)
        'request.Credentials = New NetworkCredential(userName, password)
        'request.Method = WebRequestMethods.Ftp.UploadFile

        'Using fileStream As Stream = File.OpenRead(sourceFileName),
        '      ftpStream As Stream = request.GetRequestStream()
        '    fileStream.CopyTo(ftpStream)
        'End Using



        sql = "delete from rt_chem_IN_PRICE where  year(date)=" & Year(DTP1.Value) & " and month(date)=" & Month(DTP1.Value) & ""

        cmd.CommandText = sql
            cmd.ExecuteNonQuery()




        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... [外购入库序时簿$]"


        'sql = "select * From OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;Database=" & add4 & "',[外购入库序时簿$])"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt




        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_chem_IN_PRICE"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")


    End Sub

    Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click
        Dim dt As New DataTable

        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName


        End If


        sql = "delete from rt_sys_chem_OUT_DB where month(date)=8"  '--------调拨出库
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... [仓库调拨序时簿$]"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt



        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_sys_chem_OUT_DB"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")

    End Sub

    Private Sub Button14_Click(sender As Object, e As EventArgs) Handles Button14.Click
        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName


        End If


        sql = "delete from rt_sys_chem_IN_s where month(date)=8"  '--------保税入库
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... [仓库调拨序时簿$]"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt



        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_sys_chem_IN_s"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")



    End Sub

    Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName


        End If


        sql = "delete from rt_sys_chem_IN_L where month(date)=7"  '--------老厂调拨入库
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... [仓库调拨序时簿$]"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt



        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_sys_chem_IN_L"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")



    End Sub

    Private Sub Button16_Click(sender As Object, e As EventArgs) Handles Button16.Click


        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName


        End If


        sql = "delete from rt_sys_chem_IN_S_IMPORT"  '--------保税进口入库
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... [外购入库序时簿$]"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt



        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_sys_chem_IN_S_IMPORT"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")
    End Sub

    Private Sub Button17_Click(sender As Object, e As EventArgs) Handles Button17.Click

        Dim dt As New DataTable
        sql = "Select * from rt_sys_chem_main"

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)

        Dim dr As SqlDataReader
        Dim p As Double


        For x As Integer = 0 To dt.Rows.Count - 1

            sql = "Select Top (1) price from rt_chem_in_price where name =N'" & dt.Rows(x).Item("name") & "' order by date desc"
            cmd.CommandText = sql
            If cmd.ExecuteScalar Is Nothing Then
                p = 0
            Else
                p = cmd.ExecuteScalar.ToString
            End If


            sql = "Update rt_sys_chem_main set price_LOCAL='" & p & "' where name =N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

        Next


        MsgBox("")


    End Sub

    Private Sub Button18_Click(sender As Object, e As EventArgs) Handles Button18.Click


        Dim dt As New DataTable
        sql = "Select * from rt_sys_chem_ALTER"

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)


        For x As Integer = 0 To dt.Rows.Count - 1

            sql = "Update rt_sys_chem_main set NAME_new= N'" & dt.Rows(x).Item("NEW") & "' where name ='" & dt.Rows(x).Item("OLD") & "'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

        Next


        MsgBox("")

    End Sub

    Private Sub Button19_Click(sender As Object, e As EventArgs) Handles Button19.Click


        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        OpenFileDialog1.Multiselect = True
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName


        End If

        Dim wenjian As String


        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet


        xlApp = CreateObject("Excel.Application")

        'xlBook = xlApp.Workbooks.Add
        'xlApp.Visible = True

        xlApp.DisplayAlerts = False
        ' xlBook.Activate()
        ' xlBook.Parent.Windows(1).Visible = True

        Dim cust As String
        Dim color As String
        Dim thick As String
        Dim po As String
        Dim sf As String
        Dim date1 As String
        Dim t_l As String
        Dim t_w As String
        Dim dry As String
        Dim wet As String
        Dim name As String
        Dim test_no As String





        For Each wenjian In OpenFileDialog1.FileNames
            xlBook = xlApp.Workbooks.Open(wenjian)
            xlBook.Activate()
            xlSheet = xlBook.Worksheets(1)


            Dim pp() As String

            pp = Split(wenjian, "\")


            Dim my_filename As String = pp(UBound(pp))


            test_no = xlSheet.Cells(5, 12).VALUE
            cust = xlSheet.Cells(8, 2).VALUE
            name = xlSheet.Cells(9, 2).VALUE
            thick = xlSheet.Cells(11, 2).VALUE
            color = xlSheet.Cells(12, 2).VALUE
            t_l = xlSheet.Cells(39, 5).VALUE
            t_w = xlSheet.Cells(40, 5).VALUE
            po = xlSheet.Cells(7, 13).VALUE
            sf = xlSheet.Cells(9, 13).VALUE
            date1 = xlSheet.Cells(11, 13).VALUE
            dry = xlSheet.Cells(62, 6).VALUE
            wet = xlSheet.Cells(63, 6).VALUE



            sql = "insert into rt_lab_test_RESULT (test_no,name,CUST,THICK,COLOR,T_L,T_W,PO,SF,DATE1,DRY,WET,filename) values(N'" & test_no & "'," & "N'" & name & "'," & "N'" & cust & "','" & thick & "',N'" & color & "','" & t_l & "','" & t_w & "',N'" & po & "','" & sf & "','" & date1 & "','" & dry & "','" & wet & "',N'" & my_filename & "')"
            cmd.CommandText = sql
            cmd.ExecuteScalar()

            xlBook.Close()
        Next



        MsgBox("")







    End Sub

    Private Sub Button20_Click(sender As Object, e As EventArgs) Handles Button20.Click


        Dim dt As New DataTable
        sql = "Select name,name_old from rt_sys_chem_main where name_new is not null"

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)


        For x As Integer = 0 To dt.Rows.Count - 1

            sql = "Update rt_retan_rcp set chem= N'" & dt.Rows(x).Item("name") & "' where chem =N'" & dt.Rows(x).Item("name_OLD") & "'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

        Next


        MsgBox("")



    End Sub

    Private Sub Button21_Click(sender As Object, e As EventArgs) Handles Button21.Click


        Dim dt As New DataTable
        sql = "Select name,name_old from rt_sys_chem_main where name_new is not null"

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)


        For x As Integer = 0 To dt.Rows.Count - 1

            sql = "Update rt_chem_stock_month1 set name= N'" & dt.Rows(x).Item("name") & "' where name =N'" & dt.Rows(x).Item("name_OLD") & "'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

        Next


        MsgBox("")


    End Sub

    Private Sub Button22_Click(sender As Object, e As EventArgs) Handles Button22.Click


        Dim dt As New DataTable
        sql = "Select name,name_old from rt_sys_chem_main where name_new is not null"

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)


        For x As Integer = 0 To dt.Rows.Count - 1

            sql = "Update rt_chem_container set name= N'" & dt.Rows(x).Item("name") & "' where name =N'" & dt.Rows(x).Item("name_OLD") & "'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

        Next


        MsgBox("")

    End Sub

    Private Sub Button23_Click(sender As Object, e As EventArgs) Handles Button23.Click '-----化料盘点入table




        sql = "delete from rt_chem_stock_month1"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()


        sql = "insert into rt_chem_stock_month1 select * from rt_chem_stock_month2 where year(date)=2020 and month(date)=6"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()











        sql = "update rt_chem_stock_month1 set stock1=0 WHERE STOCK1 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "update rt_chem_stock_month1 set stock2=0 WHERE STOCK2 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "update rt_chem_stock_month1 set stock3=0 WHERE STOCK3 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()


        sql = "update rt_chem_stock_month2 set stock1=0 WHERE STOCK1 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "update rt_chem_stock_month2 set stock2=0 WHERE STOCK2 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "update rt_chem_stock_month2 set stock3=0 WHERE STOCK3 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()



        Dim dt As New DataTable
        sql = "Select name,sum(stock1) as stock1,sum(stock2) as stock2,sum(stock3) as stock3 from rt_chem_stock_month1 group by name"

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        Dim dr As SqlDataReader

        sql = "update rt_sys_chem_main set stock1=0,stock2=0,stock3=0 "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()


        sql = "update rt_chem_stock_month1 set comment=0 "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        For x As Integer = 0 To dt.Rows.Count - 1  '-----写入main

            'sql = "update rt_sys_chem_main set stock1='" & dt.Rows(x).Item("stock1") & "',stock2='" & dt.Rows(x).Item("stock2") & "',stock3='" & dt.Rows(x).Item("stock3") & "' where name =N'" & dt.Rows(x).Item("name") & "'"
            'cmd.CommandText = sql
            'cmd.ExecuteNonQuery()

            Dim y As Integer = 0
            Dim z As String = ""


            sql = "select * from rt_sys_chem_main where name=N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            y = cmd.ExecuteNonQuery

            If y = 0 Then
                ListBox1.Items.Add(dt.Rows(x).Item("name"))
                TextBox20.Text = TextBox20.Text & vbCrLf & dt.Rows(x).Item("name").ToString

                sql = "update rt_chem_stock_month1 set comment=1 where name=N'" & dt.Rows(x).Item("name") & "'"
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()

            End If






        Next



        sql = "update rt_sys_chem_main set stock1=0 WHERE STOCK1 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "update rt_sys_chem_main set stock2=0 WHERE STOCK2 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "update rt_sys_chem_main set stock3=0 WHERE STOCK3 IS NULL "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()




        MsgBox("")


    End Sub

    Private Sub TextBox6_TextChanged(sender As Object, e As EventArgs) Handles TextBox6.TextChanged

    End Sub

    Private Sub Button24_Click(sender As Object, e As EventArgs) Handles Button24.Click


        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName
        Else
            Exit Sub


        End If



        Dim filepath As String = "在制品"

        Dim sourceFileName As String = file_name


        Dim address2 As String = "ftp://192.168.1.201:7777/" & filepath & "/" & OpenFileDialog1.SafeFileName

        Dim address3() As String

        address3 = Split(address2, "ftp://192.168.1.201:7777/")
        Dim add4 As String

        add4 = "d:\k3\" & address3(1)


        Dim userName As String = "admin"
        Dim password As String = "admin"
        Dim showUI As Boolean = True
        Dim connectionTimeout As Integer = 500


        Dim client As WebClient = New WebClient
        client.Credentials = New NetworkCredential(userName, password)
        client.Proxy = GlobalProxySelection.GetEmptyWebProxy()
        client.UploadFile(address2, sourceFileName)







        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['25$']"
        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt


        sql = "delete from rt_retan_record"  '-------染色
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_retan_record"
        my_sqlbulk.WriteToServer(dt)





        dt = New DataTable

        sql = "delete from rt_retan_record_d"  '-------染色
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['11$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt



        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_retan_record_d"
        my_sqlbulk.WriteToServer(dt)



        dt = New DataTable

        sql = "delete from rt_pasting_daily" '-------贴板
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['13$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        ' dgv1.DataSource = dt




        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_pasting_daily"
        my_sqlbulk.WriteToServer(dt)





        dt = New DataTable

        sql = "delete from rt_retan_record_s" '-------样品
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['10$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt

        my_sqlbulk.DestinationTableName = "dbo.rt_retan_record_S"
        my_sqlbulk.WriteToServer(dt)
















        dt = New DataTable
        sql = "delete from rt_vacumn_daily"     '------真空
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['14$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt


        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_vacumn_daily"
        my_sqlbulk.WriteToServer(dt)



        dt = New DataTable
        sql = "delete from rt_hang_daily"  '------吊干
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['16$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        ' dgv1.DataSource = dt




        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_hang_daily"
        my_sqlbulk.WriteToServer(dt)

        dt = New DataTable
        sql = "delete from rt_vibration_daily"    '-------打软
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['17$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt




        'Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_vibration_daily"
        my_sqlbulk.WriteToServer(dt)



        dt = New DataTable
        sql = "delete from rt_buff_daily"    '------磨皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['18$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt




        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_buff_daily"
        my_sqlbulk.WriteToServer(dt)

        dt = New DataTable
        sql = "delete from rt_toggle_daily"   '-----挟皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['21$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt




        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_toggle_daily"
        my_sqlbulk.WriteToServer(dt)

        dt = New DataTable
        sql = "delete from rt_burn_daily"   '-----烧毛
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['19$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt




        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_burn_daily"
        my_sqlbulk.WriteToServer(dt)




        dt = New DataTable
        sql = "delete from rt_spray_daily"    '------喷台
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['20$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt




        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_spray_daily"
        my_sqlbulk.WriteToServer(dt)





        dt = New DataTable
        sql = "delete from rt_pu_measure1"    '-------pu量皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['24-2$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt




        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_pu_measure1"
        my_sqlbulk.WriteToServer(dt)



        dt = New DataTable
        sql = "delete from rt_pu_measure2_TEMP"    '-------量皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['24-3$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt


        'Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_pu_measure2_TEMP"
        my_sqlbulk.WriteToServer(dt)




        sql = "INSERT into rt_pu_measure2(SERIAL,DATE,cust,color,pattern,sf,comment1,comment2) SELECT SERIAL,DATE,cust,color,pattern,sf,comment1,comment2 FROM RT_PU_MEASURE2_TEMP WHERE NOT EXISTS(SELECT SERIAL FROM RT_PU_MEASURE2 WHERE RT_PU_MEASURE2.SERIAL=RT_PU_MEASURE2_TEMP.SERIAL)"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()



        dt = New DataTable
        sql = "delete from rt_measure_daily"    '-------量皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['24-1$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt




        ' Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_measure_daily"
        my_sqlbulk.WriteToServer(dt)



        MsgBox("导入完成!")







    End Sub

    Private Sub Button26_Click(sender As Object, e As EventArgs) Handles Button26.Click


        Dim dt As New DataTable
        sql = "Select * from rt_sys_chem_main"

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)

        Dim dr As SqlDataReader
        Dim p As Double


        For x As Integer = 0 To dt.Rows.Count - 1

            sql = "Select Top (1) price from rt_chem_S_in_price where MATL_name =N'" & dt.Rows(x).Item("name") & "' order by date desc"
            cmd.CommandText = sql
            If cmd.ExecuteScalar Is Nothing Then
                p = 0
            Else
                p = cmd.ExecuteScalar.ToString
            End If


            sql = "Update rt_sys_chem_main set price_IMPORT='" & p & "' where name =N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

        Next


        MsgBox("")
    End Sub

    Private Sub Button27_Click(sender As Object, e As EventArgs) Handles Button27.Click

    End Sub

    Private Sub Button25_Click(sender As Object, e As EventArgs)

    End Sub

    Private Sub Button28_Click(sender As Object, e As EventArgs) Handles Button28.Click


        Dim dt As New DataTable

        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName
        Else
            Exit Sub


        End If



        Dim filepath As String = Me.Button28.Text




        Dim sourceFileName As String = file_name


        Dim address2 As String = "ftp://192.168.1.201:7777/" & filepath & "/" & OpenFileDialog1.SafeFileName

        Dim address3() As String

        address3 = Split(address2, "ftp://192.168.1.201:7777/")
        Dim add4 As String

        add4 = "d:\k3\" & address3(1)





        Dim userName As String = "admin"
        Dim password As String = "admin"
        Dim showUI As Boolean = True
        Dim connectionTimeout As Integer = 500


        Dim client As WebClient = New WebClient
        client.Credentials = New NetworkCredential(userName, password)
        client.Proxy = GlobalProxySelection.GetEmptyWebProxy()
        client.UploadFile(address2, sourceFileName)











        sql = "delete from RT_CHEM_S_IN_PRICE where year(date)=" & Year(DTP1.Value) & " and month(date)=" & Month(DTP1.Value) & ""  '----
        ' sql = "delete from rt_sys_chem_out "  '----化料
        cmd.CommandText = sql
            cmd.ExecuteNonQuery()





        'sql = "delete from RT_CHEM_S_IN_PRICE where month(date)=12"   '----收料通知单
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... [收料通知单序时簿$]"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt




        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.RT_CHEM_S_IN_PRICE"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")



    End Sub

    Private Sub Button29_Click(sender As Object, e As EventArgs) Handles Button29.Click




        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        OpenFileDialog1.Multiselect = True
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        Else
            Exit Sub
        End If


        Dim wenjian As String

        For Each wenjian In OpenFileDialog1.FileNames
            Dim dt As New DataTable

            Dim P() As String


            P = Split(wenjian, "\")



            Dim P1() As String

            P1 = Split(P(UBound(P)), ".")

            sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & wenjian & "')... [" & P1(0) & "$]"

            cmd.CommandText = sql

            da.SelectCommand = cmd
            da.Fill(dt)


            dt.Columns.Add("meter_no")


            For x As Integer = 0 To dt.Rows.Count - 1

                dt.Rows(x).Item("meter_no") = Microsoft.VisualBasic.Right(P1(0), 1)


            Next






            dgv1.DataSource = dt





            Dim my_sqlbulk As New SqlBulkCopy(conn)

            my_sqlbulk.DestinationTableName = "dbo.rt_WATER_METER"
            my_sqlbulk.WriteToServer(dt)
        Next




        MsgBox("导入完成!")
    End Sub

    Private Sub Button30_Click(sender As Object, e As EventArgs) Handles Button30.Click

        Dim dt As New DataTable

        sql = "select * from rt_order"
        cmd.CommandText = sql
        da.Fill(dt)

        Dim dr As SqlDataReader



        For x As Integer = 0 To dt.Rows.Count - 1
            Dim p() As String


            If dt.Rows(x).Item("type") = "11K0702575" Then
                Dim aa As String
                aa = ""
            End If

            p = Split(dt.Rows(x).Item("colour"), "TP")

            sql = "select * from rt_lab_test_result where po like '%" & dt.Rows(x).Item("Factory PO#") & "%' And Color Like '%" & p(0) & "%'"
            cmd.CommandText = sql

            dr = cmd.ExecuteReader


            If dr.Read Then

                dt.Rows(x).Item("test_no") = dr.Item("test_no")
                dt.Rows(x).Item("t_l") = dr.Item("t_l")
                dt.Rows(x).Item("t_w") = dr.Item("t_w")
                dt.Rows(x).Item("dry") = dr.Item("dry")
                dt.Rows(x).Item("wet") = dr.Item("wet")

            End If

            dr.Close()









        Next


        dgv1.DataSource = dt


        MsgBox("")





    End Sub

    Private Sub Button31_Click(sender As Object, e As EventArgs) Handles Button31.Click


        Dim dt As New DataTable

        sql = "select * from rt_lab_test_result"
        cmd.CommandText = sql
        da.Fill(dt)


        dt.Columns.Add("comment")


        Dim dr As SqlDataReader



        For x As Integer = 0 To dt.Rows.Count - 1
            Dim p() As String
            Dim c() As String





            p = Split(dt.Rows(x).Item("po"), "/")
            c = Split(dt.Rows(x).Item("color"), "TP")


            For y As Integer = 0 To UBound(p)

                sql = "select tannery from rt_order where [Factory PO#] like '%" & p(y) & "%' and colour like '%" & c(0) & "%'"
                cmd.CommandText = sql
                dt.Rows(x).Item("comment") = cmd.ExecuteScalar



            Next




            'sql = "select * from rt_lab_test_result where po like '%" & dt.Rows(x).Item("Factory PO#") & "%' And Color Like '%" & p(0) & "%'"
            'cmd.CommandText = sql

            'dr = cmd.ExecuteReader


            'If dr.Read Then

            '    dt.Rows(x).Item("test_no") = dr.Item("test_no")
            '    dt.Rows(x).Item("t_l") = dr.Item("t_l")
            '    dt.Rows(x).Item("t_w") = dr.Item("t_w")
            '    dt.Rows(x).Item("dry") = dr.Item("dry")
            '    dt.Rows(x).Item("wet") = dr.Item("wet")

            'End If

            'dr.Close()


        Next

        dgv1.DataSource = dt

        MsgBox("")



    End Sub

    Private Sub Button32_Click(sender As Object, e As EventArgs) Handles Button32.Click    '----PU中检


        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If


        sql = "delete from rt_pu_measure1"    '-------量皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['24-2$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt




        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_pu_measure1"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")


    End Sub

    Private Sub Button33_Click(sender As Object, e As EventArgs) Handles Button33.Click    '-----PU量尺

        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName

        End If


        sql = "delete from rt_pu_measure2_TEMP"    '-------量皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & file_name & "')... ['24-3$']"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt


        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.rt_pu_measure2_TEMP"
        my_sqlbulk.WriteToServer(dt)




        sql = "INSERT into rt_pu_measure2(SERIAL,DATE,cust,color,pattern,sf,comment1,comment2) SELECT SERIAL,DATE,cust,color,pattern,sf,comment1,comment2 FROM RT_PU_MEASURE2_TEMP WHERE NOT EXISTS(SELECT SERIAL FROM RT_PU_MEASURE2 WHERE RT_PU_MEASURE2.SERIAL=RT_PU_MEASURE2_TEMP.SERIAL)"
        cmd.CommandText = sql

        MsgBox("新增" & cmd.ExecuteNonQuery.ToString & " 笔资料")
























    End Sub

    Private Sub Button34_Click(sender As Object, e As EventArgs) Handles Button34.Click  '-----小毛


        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet

        Dim MTL(1000) As String
        Dim SERIAL(1000) As String
        Dim YEAR1(1000) As String
        Dim MONTH1(1000) As String
        Dim DATE1(1000) As String
        Dim COMMENT(1000) As String
        Dim QTY1(1000) As String
        Dim QTY2(1000) As String
        Dim QTY3(1000) As String
        Dim name(1000) As String
        Dim test_no(1000) As String
        Dim dt As New DataTable
        Dim file_name As String = ""
        Dim file_name1 As String = ""


        Dim a As Integer = 0
        Dim my_count As Integer = 0
        'OpenFileDialog1.Multiselect = True
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName
            file_name1 = OpenFileDialog1.SafeFileName

        End If



        xlApp = CreateObject("Excel.Application")


        xlApp.Visible = True



        'sql = "delete from rt_bg_io"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()


        xlBook = xlApp.Workbooks.Open(file_name)

        Dim AA As Integer = 10000
        Dim yy As Integer = 1
        For i = 1 To xlApp.Worksheets.Count

            xlSheet = xlApp.Workbooks(1).Worksheets(i)

            ListBox1.Items.Add(xlApp.Sheets(i).name)

            Dim year As String
            For y = 10 To xlApp.Workbooks(1).Worksheets(i).UsedRange.Rows.Count

                'If xlSheet.Cells(y, 2).value Like "*年" Then
                '    year = xlSheet.Cells(y, 2).value
                'End If

                'Dim p() As String
                'p = Split(year, "年")


                If IsNumeric(xlSheet.Cells(y, 10).value) Or IsNumeric(xlSheet.Cells(y, 11).value) Then
                    MTL(yy) = xlApp.Sheets(i).name
                    YEAR1(yy) = "20" & xlSheet.Cells(y, 1).VALUE.ToString

                    If Len(xlSheet.Cells(y, 2).value) = 1 Then
                        MONTH1(yy) = "0" & xlSheet.Cells(y, 2).value
                    Else
                        MONTH1(yy) = xlSheet.Cells(y, 2).value
                    End If

                    If Len(xlSheet.Cells(y, 3).value) = 1 Then
                        DATE1(yy) = "0" & xlSheet.Cells(y, 3).value
                    Else
                        DATE1(yy) = xlSheet.Cells(y, 3).value
                    End If



                    name(yy) = xlSheet.Cells(y, 6).value
                    QTY1(yy) = xlSheet.Cells(y, 11).value
                    QTY2(yy) = xlSheet.Cells(y, 12).value
                    QTY3(yy) = xlSheet.Cells(y, 13).value


                    yy = yy + 1
                End If

            Next y




        Next i




        For z As Integer = 1 To 1000




            If QTY1(z) = QTY2(z) Then
                Exit For
            End If

            ListBox1.Items.Add(file_name1 & "/" & (z) & "/" & YEAR1(z) & MONTH1(z) & DATE1(z) & "/" & name(z) & "/" & QTY1(z) & "/" & QTY2(z) & "/" & QTY3(z))


            sql = "insert into rt_bg_io (ID,xno,item,name,year,month,date1,m_in,m_out,bal,date) values(" & AA & ",N'" & file_name1 & "',N'" & MTL(z) & "',N'" & name(z) & "','" & YEAR1(z) & "','" & MONTH1(z) & "','" & DATE1(z) & "','" & QTY1(z) & "','" & QTY2(z) & "','" & QTY3(z) & "','" & YEAR1(z) & "/" & MONTH1(z) & "/" & DATE1(z) & "')"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()


            AA = AA + 1





        Next


        sql = " Update rt_bg_io set item_new=N'蓝湿牛皮' where item=N'蓝湿牛皮001'                              "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'合成油脂' where item=N'合成油脂006'                                  "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'合成油脂(矿)' where item=N'合成油脂005'                             "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'无机鞣料' where item=N'无机鞣料003'                                  "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'聚氨脂混合液' where item=N'聚氨酯012'                               "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'复鞣助剂' where item=N'复鞣助剂009'                                  "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'皮革整理剂' where item=N'皮革整理剂008'                             "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'表面活性剂' where item=N'表面活性剂004'                               "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'有机鞣料' where item=N'有机鞣料002'                                "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'PU树脂' where item=N'pu树脂014'                                      "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'促进剂' where item=N'促进剂013'                                    "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'蓝湿牛皮' where item=N'蓝湿牛皮001'                                  "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'牛二层皮' where item=N'成品总'                                     "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'制革染料' where item=N'制革染料010'                                  "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item=N'牛二层皮' where name like '%RTCP001%' or name like '%RTCP002%'           "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item=N'PU牛二层皮' where name like '%RTCP003%'                                "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'牛二层皮' where name like '%RTCP001%' or name like '%RTCP002%'       "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'PU牛二层皮' where name like '%RTCP003%'                            "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = " Update rt_bg_io set item=N'牛二层皮' where name like '%RTCP004%'                                "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item_new=N'牛二层皮' where name like '%RTCP004%'                            "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update rt_bg_io set item=N'牛二层皮' where name like '%RTCP005%'                                "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = " Update rt_bg_io set item_new=N'牛二层皮' where name like '%RTCP005%'                            "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        MsgBox("")









    End Sub

    Private Sub Button35_Click(sender As Object, e As EventArgs) Handles Button35.Click


        Dim dr As SqlDataReader
        sql = "Select * from rt_stock_nouse "
        cmd.CommandText = sql
        dr = cmd.ExecuteReader

        Dim conn1 As New SqlConnection

        conn1.ConnectionString = connstring

        Dim cmd1 As New SqlCommand
        cmd1.Connection = conn1

        If conn1.State = ConnectionState.Closed Then
            conn1.Open()
        End If


        While dr.Read
            Dim my_ans As String = ""

            sql = "update rt_month_tm Set [21]='" & dr("21") & "',[31]='" & dr("31") & "',[41]='" & dr("41") & "',[51]='" & dr("51") & "',[61]='" & dr("61") & "' where right(card,10)='" & Microsoft.VisualBasic.Right(dr("card"), 10) & "' and month(date)=12"
            cmd1.CommandText = sql
            my_ans = cmd1.ExecuteNonQuery()


            If my_ans = "" Then

                ListBox1.Items.Add(dr("card"))
            End If







        End While

        MsgBox("")





    End Sub

    Private Sub Button36_Click(sender As Object, e As EventArgs) Handles Button36.Click '---rt_bg_in_detail







        Dim dr As SqlDataReader
        sql = "select * from rt_bg_in_detail order by bg_date "
        cmd.CommandText = sql
        dr = cmd.ExecuteReader



        Dim conn1 As New SqlConnection

        conn1.ConnectionString = connstring

        Dim cmd1 As New SqlCommand
        cmd1.Connection = conn1

        If conn1.State = ConnectionState.Closed Then
            conn1.Open()
        End If


        Dim aa As Integer = 1


        While dr.Read

            'sql = "update rt_bg_in_detail  set id='" & aa & "' where matl_name=N'" & dr("matl_name").ToString & "' and xno='" & dr("xno").ToString & "' and date='" & dr("date").ToString & "' and bg_date='" & dr("bg_date").ToString & "' and name=N'" & dr("name").ToString & "' and qty='" & dr("qty") & "' and price='" & dr("price").ToString & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()


            sql = "update rt_bg_in_detail  set id='" & aa & "' where origin=N'" & dr("origin").ToString & "' and xno='" & dr("xno").ToString & "' and name=N'" & dr("name").ToString & "' and qty='" & dr("qty") & "' and price='" & dr("price").ToString & "' and date1='" & dr("date1").ToString & "' and date2='" & dr("date2").ToString & "' "
            cmd1.CommandText = sql
            cmd1.ExecuteNonQuery()



            'sql = "update rt_bg_in_detail  set id='" & aa & "' where matl_name=N'" & dr("matl_name").ToString & "' and xno='" & dr("xno").ToString & "' and qty='" & dr("qty") & "' and price='" & dr("price").ToString & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()



            aa = aa + 1

        End While
        dr.Close()



    End Sub

    Private Sub Button37_Click(sender As Object, e As EventArgs) Handles Button37.Click







        Dim dt1 As New DataTable
        Dim dr As SqlDataReader

        sql = "delete from rt_sys_chem_out where year(date)=2020 and month(date)=2 and weight=0"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()





        Dim conn1 As New SqlConnection

        conn1.ConnectionString = connstring

        Dim cmd1 As New SqlCommand
        cmd1.Connection = conn1

        If conn1.State = ConnectionState.Closed Then
            conn1.Open()
        End If
        sql = "select name from rt_sys_chem_main "
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt1)


        For x As Integer = 0 To dt1.Rows.Count - 1

            sql = "select weight from rt_sys_chem_out where year(date)=2020 and month(date)=2 and  name =N'" & dt1.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            dr = cmd.ExecuteReader

            If dr.Read Then

            Else


                sql = "insert into rt_sys_chem_out (name,date,weight) values (N'" & dt1.Rows(x).Item("name") & "','2020/02/28','0')"
                cmd1.CommandText = sql
                cmd1.ExecuteScalar()

            End If
            dr.Close()







        Next






        MsgBox("")





    End Sub

    Private Sub Button38_Click(sender As Object, e As EventArgs) Handles Button38.Click

        Dim dt1 As New DataTable
        Dim dr As SqlDataReader



        'sql = "drop table rt_pu_me"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()






        'sql = "select * into rt_pu_me from rt_pu_measure2"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()





        Dim conn1 As New SqlConnection

        conn1.ConnectionString = connstring

        Dim cmd1 As New SqlCommand
        cmd1.Connection = conn1

        If conn1.State = ConnectionState.Closed Then
            conn1.Open()
        End If


        sql = "select * from rt_pu_measure2"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt1)


        For x As Integer = 0 To dt1.Rows.Count - 1

            Dim p() As String
            Dim new_string As String = ""

            p = Split(dt1.Rows(x).Item("chem1").ToString, "  化白水")

            If UBound(p) < 1 Then
                p = Nothing
                p = Split(dt1.Rows(x).Item("chem1").ToString, " 化白水")


                If UBound(p) = 1 Then

                    For y As Integer = 0 To UBound(p)

                        new_string = new_string & p(y)

                    Next

                    sql = "update rt_pu_measure2 set chem1=N'" & new_string & "' where serial='" & dt1.Rows(x).Item("serial").ToString & "'"
                    cmd.CommandText = sql
                    cmd.ExecuteNonQuery()

                End If


            Else


                For y As Integer = 0 To UBound(p)

                    new_string = new_string & p(y)

                Next

                sql = "update rt_pu_measure2 set chem1=N'" & new_string & "' where serial='" & dt1.Rows(x).Item("serial").ToString & "'"
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()





            End If








        Next






        MsgBox("")







    End Sub

    Private Sub Button39_Click(sender As Object, e As EventArgs) Handles Button39.Click


        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        OpenFileDialog1.Multiselect = True
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName


        End If

        Dim wenjian As String


        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet


        xlApp = CreateObject("Excel.Application")

        'xlBook = xlApp.Workbooks.Add
        'xlApp.Visible = True

        xlApp.DisplayAlerts = False
        ' xlBook.Activate()
        ' xlBook.Parent.Windows(1).Visible = True

        Dim cust As String
        Dim color As String
        Dim thick As String
        Dim po As String
        Dim sf As String
        Dim date1 As String
        Dim t_l As String
        Dim t_w As String
        Dim dry As String
        Dim wet As String
        Dim name As String
        Dim test_no As String





        For Each wenjian In OpenFileDialog1.FileNames
            xlBook = xlApp.Workbooks.Open(wenjian,, True,, "0022")
            xlBook.Activate()



            For Each my_sheet In xlBook.Worksheets

                my_sheet.activate

                Dim my_date As String
                my_date = my_sheet.Cells(1, 2).VALUE & "/" & my_sheet.Cells(1, 4).VALUE & "/" & my_sheet.Cells(1, 5).VALUE
                For x As Integer = 5 To 30

                    If my_sheet.Cells(x, 2).VALUE <> "" Then

                        sql = "insert into rt_stock_out_fm_wu (date,cust,color,type,qty,p1,p2,no) values('" & my_date & "',N'" & my_sheet.Cells(x, 2).VALUE & "',N'" & my_sheet.Cells(x, 3).VALUE & "',N'" & my_sheet.Cells(x, 4).VALUE & "','" & my_sheet.Cells(x, 5).VALUE & "',N'" & my_sheet.Cells(x, 6).VALUE & "',N'" & my_sheet.Cells(x, 7).VALUE & "','" & my_sheet.Cells(x, 8).VALUE & "')"
                        cmd.CommandText = sql
                        cmd.ExecuteNonQuery()
                    Else
                        Exit For

                    End If





                Next







            Next












            'xlSheet = xlBook.Worksheets(1)


            'Dim pp() As String

            'pp = Split(wenjian, "\")


            'Dim my_filename As String = pp(UBound(pp))




            'sql = "insert into rt_lab_test_RESULT (test_no,name,CUST,THICK,COLOR,T_L,T_W,PO,SF,DATE1,DRY,WET,filename) values(N'" & test_no & "'," & "N'" & name & "'," & "N'" & cust & "','" & thick & "',N'" & color & "','" & t_l & "','" & t_w & "',N'" & po & "','" & sf & "','" & date1 & "','" & dry & "','" & wet & "',N'" & my_filename & "')"
            'cmd.CommandText = sql
            'cmd.ExecuteScalar()

            'xlBook.Close()
        Next



        MsgBox("")



    End Sub

    Private Sub Button40_Click(sender As Object, e As EventArgs) Handles Button40.Click



        Dim dt As New DataTable
        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        OpenFileDialog1.Multiselect = True
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName


        End If

        Dim wenjian As String


        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet


        xlApp = CreateObject("Excel.Application")

        'xlBook = xlApp.Workbooks.Add
        'xlApp.Visible = True

        xlApp.DisplayAlerts = False
        ' xlBook.Activate()
        ' xlBook.Parent.Windows(1).Visible = True

        Dim cust As String
        Dim color As String
        Dim thick As String
        Dim po As String
        Dim sf As String
        Dim date1 As String
        Dim t_l As String
        Dim t_w As String
        Dim dry As String
        Dim wet As String
        Dim name As String
        Dim test_no As String





        For Each wenjian In OpenFileDialog1.FileNames
            xlBook = xlApp.Workbooks.Open(wenjian,, True,, "0022")
            xlBook.Activate()



            For Each my_sheet In xlBook.Worksheets

                my_sheet.activate

                Dim my_date As String
                my_date = my_sheet.Cells(2, 10).VALUE & "/" & my_sheet.Cells(2, 11).VALUE & "/" & my_sheet.Cells(2, 12).VALUE
                For x As Integer = 4 To 30

                    If my_sheet.Cells(x, 3).VALUE <> "" Then

                        sql = "insert into rt_stock_out_PU_wu (date,cust,color,PATTERN,SPEC,qty,PRICE,TOTAL,p1,p2,no,CASH,COMMENT) values('" & my_date & "',N'" & my_sheet.Cells(x, 3).VALUE & "',N'" & my_sheet.Cells(x, 4).VALUE & "',N'" & my_sheet.Cells(x, 5).VALUE & "','" & my_sheet.Cells(x, 6).VALUE & "',N'" & my_sheet.Cells(x, 7).VALUE & "',N'" & my_sheet.Cells(x, 8).VALUE & "','" & my_sheet.Cells(x, 9).VALUE & "',N'" & my_sheet.Cells(x, 10).VALUE & "',N'" & my_sheet.Cells(x, 11).VALUE & "',N'" & my_sheet.Cells(x, 12).VALUE & "',N'" & my_sheet.Cells(x, 13).VALUE & "',N'" & my_sheet.Cells(x, 14).VALUE & "')"
                        cmd.CommandText = sql
                        cmd.ExecuteNonQuery()
                    Else
                        Exit For

                    End If

                Next


            Next




        Next



        MsgBox("")



    End Sub

    Private Sub Button41_Click(sender As Object, e As EventArgs) Handles Button41.Click





        Dim dr As SqlDataReader
        sql = "select * from rt_bg_in_detail order by bg_date "
        cmd.CommandText = sql
        dr = cmd.ExecuteReader



        Dim conn1 As New SqlConnection

        conn1.ConnectionString = connstring

        Dim cmd1 As New SqlCommand
        cmd1.Connection = conn1

        If conn1.State = ConnectionState.Closed Then
            conn1.Open()
        End If


        Dim aa As Integer = 1


        While dr.Read


            If Not (IsDBNull(dr("bg_date"))) Then

                Dim p() As String

                p = Split(dr("bg_date"), ".")

                If UBound(p) > 0 Then


                    If Len(p(1)) = 1 Then
                        p(1) = "0" + p(1)
                    End If
                    If Len(p(2)) = 1 Then
                        p(1) = "0" + p(1)
                    End If

                    sql = "update rt_bg_in_detail set date1='" & p(0) & p(1) & p(2) & "' where id='" & dr("id") & "'"
                    cmd1.CommandText = sql
                    cmd1.ExecuteNonQuery()
                Else
                    sql = "update rt_bg_in_detail set date1='" & dr("bg_date") & "' where id='" & dr("id") & "'"
                    cmd1.CommandText = sql
                    cmd1.ExecuteNonQuery()

                End If

            End If

            'sql = "update rt_bg_in_detail  set id='" & aa & "' where matl_name=N'" & dr("matl_name").ToString & "' and xno='" & dr("xno").ToString & "' and date='" & dr("date").ToString & "' and bg_date='" & dr("bg_date").ToString & "' and name=N'" & dr("name").ToString & "' and qty='" & dr("qty") & "' and price='" & dr("price").ToString & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()

            'sql = "update rt_bg_in_detail  set id='" & aa & "' where origin=N'" & dr("origin").ToString & "' and xno='" & dr("xno").ToString & "' and name=N'" & dr("name").ToString & "' and qty='" & dr("qty") & "' and price='" & dr("price").ToString & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()

            'sql = "update rt_bg_in_detail  set id='" & aa & "' where matl_name=N'" & dr("matl_name").ToString & "' and xno='" & dr("xno").ToString & "' and qty='" & dr("qty") & "' and price='" & dr("price").ToString & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()

            aa = aa + 1

        End While
        dr.Close()

        MsgBox("")


    End Sub

    Private Sub Button42_Click(sender As Object, e As EventArgs) Handles Button42.Click

        Dim dt As New DataTable

        Dim dr As SqlDataReader
        sql = "SELECT   NAME, CAST(SUM(WEIGHT) AS numeric(10, 1)) AS qty FROM      RT_SYS_CHEM_OUT WHERE  card='BRP2004250158' or card='BRP2004250157' or card='BRP2004250159' group by name"
        cmd.CommandText = sql
        da.SelectCommand = cmd

        da.Fill(dt)



        For x As Integer = 0 To dt.Rows.Count - 1

            sql = "update rt_chem_stock_month2 set stock1=stock1-" & dt.Rows(x).Item("qty") & " where name=N'" & dt.Rows(x).Item("name") & "' and date='2020/04/20'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

        Next

        MsgBox("")


        'Dim conn1 As New SqlConnection

        'conn1.ConnectionString = connstring

        'Dim cmd1 As New SqlCommand
        'cmd1.Connection = conn1

        'If conn1.State = ConnectionState.Closed Then
        '    conn1.Open()
        'End If


        'Dim aa As Integer = 1


        'While dr.Read











    End Sub

    Private Sub Button43_Click(sender As Object, e As EventArgs) Handles Button43.Click '-----成品出库






        Dim dt As New DataTable

        Dim file_name As String = ""

        Dim a As Integer = 0
        Dim my_count As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName
        Else
            Exit Sub


        End If



        Dim filepath As String = Me.Button43.Text




        Dim sourceFileName As String = file_name


        Dim address2 As String = "ftp://ruitaileather.tpddns.cn:7777/" & filepath & "/" & OpenFileDialog1.SafeFileName

        Dim address3() As String

        address3 = Split(address2, "ftp://ruitaileather.tpddns.cn:7777/")
        Dim add4 As String

        add4 = "d:\k3\" & address3(1)





        Dim userName As String = "admin"
        Dim password As String = "admin"
        Dim showUI As Boolean = True
        Dim connectionTimeout As Integer = 500


        Dim client As WebClient = New WebClient
        client.Credentials = New NetworkCredential(userName, password)
        client.Proxy = GlobalProxySelection.GetEmptyWebProxy()
        client.UploadFile(address2, sourceFileName)




        sql = "delete from RT_SHIP_DETAIL where year(date)=" & Year(DTP1.Value) & " and month(date)=" & Month(DTP1.Value) & ""  '----
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()



        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... [销售出库序时簿$]"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt




        Dim my_sqlbulk As New SqlBulkCopy(conn)

        my_sqlbulk.DestinationTableName = "dbo.RT_SHIP_DETAIL"
        my_sqlbulk.WriteToServer(dt)

        MsgBox("导入完成!")




    End Sub

    Private Sub Button44_Click(sender As Object, e As EventArgs) Handles Button44.Click




        '-----RT_BG_IO更新日期









        Dim conn1 As New SqlConnection

        conn1.ConnectionString = connstring

        Dim cmd1 As New SqlCommand
        cmd1.Connection = conn1

        If conn1.State = ConnectionState.Closed Then
            conn1.Open()
        End If





        sql = "update rt_bg_io set m_in=0 where m_in is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()

        sql = "update rt_bg_io set m_out=0 where m_out is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()



        Dim dr As SqlDataReader
        sql = "select * from rt_bg_io "
        cmd.CommandText = sql
        dr = cmd.ExecuteReader
        Dim aa As Integer = 1
        While dr.Read

            sql = "update rt_bg_IO set id='" & aa & "'  where item=N'" & dr("ITEM").ToString & "' and NAME=N'" & dr("NAME").ToString & "' and year='" & dr("year").ToString & "' and month='" & dr("month").ToString & "' and date1=N'" & dr("date1").ToString & "' and m_in='" & dr("m_in").ToString & "' and m_out='" & dr("m_out").ToString & "' and xno=N'" & dr("xno").ToString & "'"
            cmd1.CommandText = sql
            cmd1.ExecuteScalar()
            aa = aa + 1
        End While
        dr.Close()



        sql = "select * from rt_bg_io  "
        cmd.CommandText = sql
        dr = cmd.ExecuteReader
        While dr.Read




            Dim my_month As String
            Dim my_date As String


            If Len(dr("month")) = 1 Then
                my_month = "0" + dr("month")
            Else
                my_month = dr("month")
            End If


            If Len(dr("date1")) = 1 Then
                my_date = "0" + dr("date1")
            Else
                my_date = dr("date1")
            End If

            sql = "update rt_bg_io set date='" & dr("year") & "/" & my_month & "/" & my_date & "' where id='" & dr("id") & "'"
            cmd1.CommandText = sql
            cmd1.ExecuteNonQuery()




        End While
        dr.Close()

        MsgBox("")


    End Sub

    Private Sub Button45_Click(sender As Object, e As EventArgs) Handles Button45.Click  '----汇入小毛台帐(车间)


        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet

        Dim MTL(1000) As String
        Dim SERIAL(1000) As String
        Dim YEAR1(1000) As String
        Dim MONTH1(1000) As String
        Dim DATE1(1000) As String
        Dim COMMENT(1000) As String
        Dim QTY1(1000) As String
        Dim QTY2(1000) As String
        Dim QTY3(1000) As String
        Dim name(1000) As String
        Dim test_no(1000) As String
        Dim dt As New DataTable
        Dim file_name As String = ""
        Dim file_name1 As String = ""


        Dim a As Integer = 0
        Dim my_count As Integer = 0
        'OpenFileDialog1.Multiselect = True
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            file_name = OpenFileDialog1.FileName
            file_name1 = OpenFileDialog1.SafeFileName

        End If



        xlApp = CreateObject("Excel.Application")


        xlApp.Visible = True



        'sql = "delete from rt_bg_io"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()


        xlBook = xlApp.Workbooks.Open(file_name)


        Dim yy As Integer = 1
        For i = 1 To xlApp.Worksheets.Count

            xlSheet = xlApp.Workbooks(1).Worksheets(i)

            ListBox1.Items.Add(xlApp.Sheets(i).name)

            Dim year As String
            For y = 9 To xlApp.Workbooks(1).Worksheets(i).UsedRange.Rows.Count

                If xlSheet.Cells(y, 2).value Like "*年" Then
                    year = xlSheet.Cells(y, 2).value
                End If

                Dim p() As String
                p = Split(year, "年")


                If IsNumeric(xlSheet.Cells(y, 10).value) Or IsNumeric(xlSheet.Cells(y, 11).value) Then
                    MTL(yy) = xlApp.Sheets(i).name
                    YEAR1(yy) = "20" & p(0)

                    If Len(xlSheet.Cells(y, 2).value) = 1 Then
                        MONTH1(yy) = "0" & xlSheet.Cells(y, 2).value
                    Else
                        MONTH1(yy) = xlSheet.Cells(y, 2).value
                    End If

                    If Len(xlSheet.Cells(y, 3).value) = 1 Then
                        DATE1(yy) = "0" & xlSheet.Cells(y, 3).value
                    Else
                        DATE1(yy) = xlSheet.Cells(y, 3).value
                    End If



                    name(yy) = xlSheet.Cells(y, 5).value
                    QTY1(yy) = xlSheet.Cells(y, 10).value
                    QTY2(yy) = xlSheet.Cells(y, 11).value
                    QTY3(yy) = xlSheet.Cells(y, 12).value


                    yy = yy + 1
                End If

            Next y




        Next i




        For z As Integer = 1 To 1000




            If QTY1(z) = QTY2(z) Then
                Exit For
            End If

            ListBox1.Items.Add(file_name1 & "/" & (z) & "/" & YEAR1(z) & MONTH1(z) & DATE1(z) & "/" & name(z) & "/" & QTY1(z) & "/" & QTY2(z) & "/" & QTY3(z))


            sql = "insert into rt_bg_io_1 (xno,item,name,year,month,date1,m_in,m_out,bal) values(N'" & file_name1 & "',N'" & MTL(z) & "',N'" & name(z) & "','" & YEAR1(z) & "','" & MONTH1(z) & "','" & DATE1(z) & "','" & QTY1(z) & "','" & QTY2(z) & "','" & QTY3(z) & "')"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()








        Next






        MsgBox("")

    End Sub

    Private Sub Button46_Click(sender As Object, e As EventArgs) Handles Button46.Click





    End Sub

    Private Sub Button47_Click(sender As Object, e As EventArgs) Handles Button47.Click  '----BG_OUT 设ID






        Dim dr As SqlDataReader
        sql = "select * from rt_bg_OUT order by date1 "
        cmd.CommandText = sql
        dr = cmd.ExecuteReader



        Dim conn1 As New SqlConnection

        conn1.ConnectionString = connstring

        Dim cmd1 As New SqlCommand
        cmd1.Connection = conn1

        If conn1.State = ConnectionState.Closed Then
            conn1.Open()
        End If


        Dim aa As Integer = 1


        While dr.Read

            'sql = "update rt_bg_in_detail  set id='" & aa & "' where matl_name=N'" & dr("matl_name").ToString & "' and xno='" & dr("xno").ToString & "' and date='" & dr("date").ToString & "' and bg_date='" & dr("bg_date").ToString & "' and name=N'" & dr("name").ToString & "' and qty='" & dr("qty") & "' and price='" & dr("price").ToString & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()


            sql = "update rt_bg_OUT  set id='" & aa & "' where NO=N'" & dr("NO").ToString & "' and xno='" & dr("xno").ToString & "' and COMMENT=N'" & dr("COMMENT").ToString & "' and QTY=" & dr("qty") & " and TOTAL='" & dr("TOTAL") & "'"
            cmd1.CommandText = sql
            cmd1.ExecuteNonQuery()


            'sql = "update rt_bg_OUT  set id='" & aa & "' where NO=N'" & dr("NO") & "' and xno='" & dr("xno") & "' and qty='" & dr("qty") & "' and TOTAL='" & dr("TOTAL") & "' and FEE='" & dr("FEE") & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()



            'sql = "update rt_bg_in_detail  set id='" & aa & "' where matl_name=N'" & dr("matl_name").ToString & "' and xno='" & dr("xno").ToString & "' and qty='" & dr("qty") & "' and price='" & dr("price").ToString & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()



            aa = aa + 1

        End While
        dr.Close()
        MsgBox("")

    End Sub
End Class