HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/98、系統維護/Frm_0Import1.vb

Imports System.Data.SqlClient
Imports System.Net
Imports System.Runtime.InteropServices
Public Class Frm_0Import1
    Dim cmd As New SqlCommand : Dim da As New SqlDataAdapter : Dim conn As New SqlConnection
    Dim my_year111, my_month111 As Integer
    Dim sql As String
    Private Shared pbCount As Integer = 0 '進度條計數用
    Private Shared timeH As Integer = 0 '小時計數用
    Private Shared timeM As Integer = 0 '分鐘計數用
    Private Shared timeS As Integer = 0 '秒計數用
    Private Shared alarmCounter As Integer = 0 '總秒數計數用
    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 xlRange As Microsoft.Office.Interop.Excel.Range
    Dim PTCache As Microsoft.Office.Interop.Excel.PivotCache
    Dim PT As Microsoft.Office.Interop.Excel.PivotTable
    Private Sub FrmImport_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    End Sub
    Private Sub 导入染色工艺_bt_Click(sender As Object, e As EventArgs) Handles 导入染色工艺_bt.Click
        Application.DoEvents()
        Dim dt As New DataTable : Dim file_name As String = "" : Dim a As Integer = 0 : Dim my_count As Integer = 0 : Dim 導入數量 As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then : file_name = OpenFileDialog1.FileName : End If
        Dim xlApp As Microsoft.Office.Interop.Excel.Application : Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim file_name1 As String = OpenFileDialog1.SafeFileName
        xlApp = CreateObject("Excel.Application") : xlApp.Visible = True : Me.TopMost = True : xlApp.DisplayAlerts = False
        xlBook = xlApp.Workbooks.Open(file_name, UpdateLinks:=False, ReadOnly:=True) : xlBook.Activate()
        For i = 1 To xlApp.Worksheets.Count : ProgressBar1.Maximum = xlApp.Worksheets.Count
            Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet : xlSheet = xlApp.Workbooks(1).Worksheets(i) : xlSheet.Activate()
            Dim 流程卡號與片數, 檔案分頁, 日期, 品名, 開削, 重量, 選級, 化料, 百分比, 時間, 備注 As String
            If xlSheet.Cells(1, 6).value Is Nothing Then : 流程卡號與片數 = xlSheet.Cells(1, 4).value
            Else : 流程卡號與片數 = xlSheet.Cells(1, 4).value & "  " & xlSheet.Cells(1, 6).value : End If
            日期 = xlSheet.Cells(2, 4).value : 品名 = xlSheet.Cells(1, 10).value : 開削 = xlSheet.Cells(2, 10).value
            重量 = xlSheet.Cells(2, 14).value : 選級 = xlSheet.Cells(3, 4).value : 檔案分頁 = file_name1 & "_" & xlSheet.Name
            If 流程卡號與片數 <> "" And 品名 <> "" And 重量 <> "" And 日期 <> "" Then
                SQL_染色處方已導入確認(流程卡號與片數, 品名, 開削, 重量, 選級)
                If dr.Read Then : Else
                    Dim 項次 As Integer = 1 : 導入數量 += 1
                    For j = 6 To 100
                        If IsDBNull(xlSheet.Cells(j, 3).value) Then Exit For
                        化料 = xlSheet.Cells(j, 6).value : 百分比 = xlSheet.Cells(j, 3).value : 時間 = xlSheet.Cells(j, 9).value : 備注 = xlSheet.Cells(j, 10).value
                        SQL_染色處方導入(項次, 檔案分頁, 流程卡號與片數, 品名, 日期, 開削, 重量, 選級, 化料, 百分比, 時間, 備注) : 項次 += 1
                    Next j
                End If
            End If : ProgressBar1.Value += 1
        Next
        '--------------------- Excel 關閉處理 ---------------------
        xlBook.Close(False) : xlApp.Quit()
        MsgBox("导入完成! 共導入" & 導入數量 & " 染色處方!") : ProgressBar1.Value = 0
    End Sub
    Private Sub 导入毛皮工艺_tb_Click(sender As Object, e As EventArgs) Handles 导入毛皮工艺_tb.Click
        Application.DoEvents() : Dim file_name As String = "" : Dim 導入數量 As Integer = 0
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then : file_name = OpenFileDialog1.FileName : End If
        Dim xlApp As Microsoft.Office.Interop.Excel.Application : Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim file_name1 As String = OpenFileDialog1.SafeFileName : xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True : xlApp.DisplayAlerts = False : xlBook = xlApp.Workbooks.Open(file_name,, True) : xlBook.Activate()
        Dim summarySheet = xlBook.Worksheets.Add : summarySheet.Name = "总表"
        Dim rowIndex As Integer = 1 : Dim columnIndex As Integer = 1      ' 从A列开始
        For i As Integer = 1 To xlBook.Worksheets.Count : ProgressBar1.Maximum = xlApp.Worksheets.Count
            xlSheet = CType(xlBook.Worksheets(i), Microsoft.Office.Interop.Excel.Worksheet)
            If xlSheet.Name <> "总表" Then
                If rowIndex Mod 20 = 0 Then                                                     ' 每20行换行
                    rowIndex = 1                                                                ' 重置rowIndex为1
                    columnIndex += 2                                                            ' 跳到下一个列
                End If : 導入數量 += 1
                xlSheet.Hyperlinks.Add(xlSheet.Cells(2, 7), "#" & summarySheet.Name & "!A1",
                                       Type.Missing, "返回总表", "返回总表")                    ' 在总表中插入超链接，显示工作表名称
                summarySheet.Cells(rowIndex, columnIndex).Formula = "=HYPERLINK(""#'" & xlSheet.Name & "'!A1"", """ & xlSheet.Name & """)"
                rowIndex += 1                                                                   ' 增加总表的行数
            End If : ProgressBar1.Value += 1
        Next : MsgBox("导入完成! 共導入" & 導入數量 & " 毛皮處方!") : ProgressBar1.Value = 0
    End Sub






















    '------------------------以下未整理-----------------------------------------------------------------------------------------------------------------------------------------------------
    Private Sub FrmImport_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
        'my_month = Month(Today) : InitializeTimer() : ini_update() : Timer1.Enabled = True
        'dgv2.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        'conn.ConnectionString = connstring : If conn.State = ConnectionState.Closed Then : conn.Open() : End If : cmd.Connection = conn
        'dgv1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        'sql = "SELECT  YEAR(date) AS 年, MONTH(date) AS 月 FROM  RT_CHEM_STOCK_MONTH2 GROUP BY YEAR(date), MONTH(date) ORDER BY 年 DESC, 月 DESC"
        'cmd.CommandText = sql : Dim dt As New DataTable : da.SelectCommand = cmd : da.Fill(dt) : dgv2.DataSource = dt
        'dgv2.SelectionMode = DataGridViewSelectionMode.FullRowSelect
    End Sub
    Public Sub InitializeTimer()
        Timer1.Interval = 1000 : Timer1.Enabled = True : 暫停_bt.PerformClick()
    End Sub
    Private Sub 暫停_bt_Click(sender As Object, e As EventArgs) Handles 暫停_bt.Click
        If 暫停_bt.Text = "Stop" Then : 開始_bt.Text = "START" : Timer1.Enabled = False
        Else : 開始_bt.Text = "STOP" : Timer1.Enabled = True : End If
    End Sub
    Private Sub ini_update()
        Exit Sub     '-------------------------------------------------------原本備注掉-------------------------------------------------------
        '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).FillWeight = 95 : DGV_ACCOUNTING.Columns(1).FillWeight = 55 : DGV_ACCOUNTING.Columns(2).FillWeight = 95
        'DGV_ACCOUNTING.AllowUserToAddRows = False
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles 開始_bt.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) & "" '----化料
        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.開始_bt.Text
        Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/k3/领料单/" & OpenFileDialog1.SafeFileName
        Dim address3() As String = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String = "d:\k3\" & address3(1)
        add4 = "d:\k3\领料单\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c" : Dim password As String = "Lafayette11"
        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 & "')... [生产领料序时簿$]"
        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)

        Dim dt1 As New DataTable
        sql = "update rt_sys_chem_out set year=year(date)" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "update rt_sys_chem_out set month=month(date)" : cmd.CommandText = sql : cmd.ExecuteNonQuery() : dt = New DataTable
        sql = "select name from rt_sys_chem_main" : cmd.CommandText = sql : da.SelectCommand = cmd : da.Fill(dt)
        MsgBox("完成!")
        Exit Sub     '-------------------------------------------------------原本備注掉-------------------------------------------------------
        '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
    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
        Dim filepath As String = "k3/在制品" : Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim address3() As String : address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String : add4 = "d:\k3\在制品\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c" : Dim password As String = "Lafayette11"
        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_shaving_out" : 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 : dt.Rows(dt.Rows.Count - 1).Delete()
        Dim my_sqlbulk As New SqlBulkCopy(conn) : my_sqlbulk.DestinationTableName = "dbo.rt_shaving_out" : 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 : End If
        For Each worksheets In xlBook.Worksheets : If worksheets.name = "24-3" Then : xlSheet = worksheets : End If : Next
        If xlSheet.Cells(6334, 3).value = "2024年05月30日-31日" Then : xlSheet.Rows("1:6334").delete : xlBook.Save() : End If
        xlBook.Close() : xlApp.Quit()
        Dim filepath As String = "k3/在制品" : Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim address3() As String : address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String : add4 = "d:\k3\在制品\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c" : Dim password As String = "Lafayette11"
        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_k3_sale_reject" : 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 : dt.Rows(dt.Rows.Count - 1).Delete()
        Dim my_sqlbulk As New SqlBulkCopy(conn) : my_sqlbulk.DestinationTableName = "dbo.rt_k3_sale_reject" : my_sqlbulk.WriteToServer(dt)
        MsgBox("导入完成!")
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim file_name As String = "" : Dim year As String = "" : Dim month As String = "" : Dim sql As String = ""
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then : file_name = OpenFileDialog1.FileName : End If
        Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        xlApp.Visible = False : xlApp.DisplayAlerts = False
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(file_name) : xlBook.Activate()
        year = OpenFileDialog1.SafeFileName.Substring(0, 4)                           ' 获取文件名中的年份和月份
        month = OpenFileDialog1.SafeFileName.Substring(4, 2)
        Dim cmd As New SqlCommand()                                                   ' 连接数据库并准备执行 SQL 命令
        cmd.Connection = conn                                                         ' 假设 conn 是你的 SqlConnection 对象
        Dim file_name1 As String = OpenFileDialog1.SafeFileName                       ' 遍历 Excel 中的所有工作表
        For i = 1 To xlApp.Worksheets.Count
            Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = xlApp.Workbooks(1).Worksheets(i) : xlSheet.Activate()
            Dim no As String = xlSheet.Cells(1, 4).value : Dim rcp_no As String = file_name1 & "_" & xlSheet.Name
            Dim retan_date As String = xlSheet.Cells(2, 4).value : Dim name As String = xlSheet.Cells(1, 10).value
            Dim spec As String = xlSheet.Cells(2, 10).value : Dim weight As String = xlSheet.Cells(2, 14).value
            Dim selection As String = xlSheet.Cells(3, 4).value
            sql = "SELECT COUNT(*) FROM ht_retanrcp WHERE rcp_no = @rcp_no"            ' 检查数据库中是否已存在相同的 rcp_no
            cmd.CommandText = sql : cmd.Parameters.Clear() : cmd.Parameters.AddWithValue("@rcp_no", rcp_no)
            Dim count As Integer = Convert.ToInt32(cmd.ExecuteScalar())
            If count = 0 Then : Dim aa As Integer = 1                                  ' 如果不存在该 rcp_no，则插入新的记录
                For j = 7 To 100                                                       ' 检查当前行是否有数据
                    If xlSheet.Cells(j, 6).value Is Nothing Then Exit For
                    Dim chem As String = xlSheet.Cells(j, 6).value : Dim percents As String = xlSheet.Cells(j, 3).value
                    Dim time As String = xlSheet.Cells(j, 9).value : Dim comment As String = xlSheet.Cells(j, 10).value
                    ' 插入数据到数据库（使用参数化查询）
                    sql = "INSERT INTO ht_retanrcp (item, rcp_no, no, name, date, spec, weight, selection, chem, percents, time, comment) 
                        VALUES (@item, @rcp_no, @no, @name, @retan_date, @spec, @weight, @selection, @chem, @percents, @time, @comment)"
                    cmd.CommandText = sql : cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@item", aa) : cmd.Parameters.AddWithValue("@rcp_no", rcp_no)
                    cmd.Parameters.AddWithValue("@no", no) : cmd.Parameters.AddWithValue("@name", name)
                    cmd.Parameters.AddWithValue("@retan_date", retan_date) : cmd.Parameters.AddWithValue("@spec", spec)
                    cmd.Parameters.AddWithValue("@weight", weight) : cmd.Parameters.AddWithValue("@selection", selection)
                    cmd.Parameters.AddWithValue("@chem", chem) : cmd.Parameters.AddWithValue("@percents", percents)
                    cmd.Parameters.AddWithValue("@time", If(time = "", DBNull.Value, time))
                    cmd.Parameters.AddWithValue("@comment", If(comment = "", DBNull.Value, comment))
                    cmd.ExecuteNonQuery() : aa += 1                                                                   ' 执行插入操作
                Next j
            End If
        Next
        xlBook.Close(False) : xlApp.Quit()                                                                            ' 关闭 Excel 应用
        Marshal.ReleaseComObject(xlSheet) : Marshal.ReleaseComObject(xlBook) : Marshal.ReleaseComObject(xlApp)        ' 释放资源
        MessageBox.Show("数据已成功导入！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information)
    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 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 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://" & my_ip & ":7788/k3/外购入库单/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\" & address3(1)
        add4 = "d:\k3\外购入库单\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        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)
        sql = "insert into rt_chem_in (date,qty,price,name,comment)
select date,qty,price,name,comment from rt_chem_in_price where (comment not like N'%征%' or comment is null)"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "insert into rt_chem_in (date,qty,price_usd,name)
select date,qty,price,matl_name from rt_chem_s_in_price"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        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
        Dim a As Integer = 0
        Dim b As Integer = 0
        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
                b = b + 1
            Else
                p = cmd.ExecuteScalar.ToString
                a = a + 1
            End If
            Dim xx As String
            Dim year1 As String
            Dim month1 As String
            Dim date1 As String
            sql = "Select top(1) date 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
            Else
                year1 = Year(cmd.ExecuteScalar)
                month1 = Month(cmd.ExecuteScalar)
                ' date1 = Day(cmd.ExecuteScalar)
                If Len(month1) = 1 Then
                    month1 = "0" & month1
                End If
                xx = year1 + month1 + "S"
                a = a + 1
            End If
            sql = "Update rt_sys_chem_main set usd='" & p & "', lot_usd='" & xx & "' where name =N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
        Next
        MsgBox(a)
        MsgBox(b)
    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") : xlApp.DisplayAlerts = False
        Dim cust, color, thick, po, sf, date1, t_l, t_w, dry, wet, name, test_no, t_a03, t_b01_1, t_b01_2, t_b09_1, t_b09_2, t_e01_1, t_e01_2, t_e02_1, t_e02_2, t_e06,
            card_no, A03, B01_1_1, B01_1_2, B01_2_1, B01_2_2, B09_1, B09_2, E01_GRAIN, E01_BACK, E02_DRY, E02_WET, T_E03, E03, E20, E06, T_E20, T_E11_1, T_E11_2, T_E10,
            E11_1, E11_2, E10 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
            t_a03 = xlSheet.Cells(38, 5).VALUE : t_b01_1 = xlSheet.Cells(41, 6).VALUE : t_b01_2 = xlSheet.Cells(42, 6).VALUE : t_b09_1 = xlSheet.Cells(43, 6).VALUE
            t_b09_2 = xlSheet.Cells(44, 6).VALUE : t_e01_1 = xlSheet.Cells(60, 5).VALUE : t_e01_2 = xlSheet.Cells(61, 5).VALUE : t_e02_1 = xlSheet.Cells(62, 6).VALUE
            t_e02_2 = xlSheet.Cells(63, 6).VALUE : t_e06 = xlSheet.Cells(72, 5).VALUE : card_no = xlSheet.Cells(76, 13).VALUE
            If xlSheet.Cells(38, 13).VALUE Is Nothing Then : A03 = "" : Else : A03 = xlSheet.Cells(38, 13).VALUE.ToString : End If
            If xlSheet.Cells(39, 13).VALUE Is Nothing Then : B01_1_1 = "" : Else : B01_1_1 = xlSheet.Cells(39, 13).VALUE.ToString : End If
            If xlSheet.Cells(40, 13).VALUE Is Nothing Then : B01_1_2 = "" : Else : B01_1_2 = xlSheet.Cells(40, 13).VALUE.ToString : End If
            If xlSheet.Cells(41, 13).VALUE Is Nothing Then : B01_2_1 = "" : Else : B01_2_1 = xlSheet.Cells(41, 13).VALUE.ToString : End If
            If xlSheet.Cells(42, 13).VALUE Is Nothing Then : B01_2_2 = "" : Else : B01_2_2 = xlSheet.Cells(42, 13).VALUE.ToString : End If
            If xlSheet.Cells(43, 13).VALUE Is Nothing Then : B09_1 = "" : Else : B09_1 = xlSheet.Cells(43, 13).VALUE.ToString : End If
            If xlSheet.Cells(44, 13).VALUE Is Nothing Then : B09_2 = "" : Else : B09_2 = xlSheet.Cells(44, 13).VALUE.ToString : End If
            If xlSheet.Cells(60, 13).VALUE Is Nothing Then : E01_GRAIN = "" : Else : E01_GRAIN = xlSheet.Cells(60, 13).VALUE.ToString : End If
            If xlSheet.Cells(61, 13).VALUE Is Nothing Then : E01_BACK = "" : Else : E01_BACK = xlSheet.Cells(61, 13).VALUE.ToString : End If
            If xlSheet.Cells(62, 13).VALUE Is Nothing Then : E02_DRY = "" : Else : E02_DRY = xlSheet.Cells(62, 13).VALUE.ToString : End If
            If xlSheet.Cells(63, 13).VALUE Is Nothing Then : E02_WET = "" : Else : E02_WET = xlSheet.Cells(63, 13).VALUE.ToString : End If
            If xlSheet.Cells(64, 6).VALUE Is Nothing Then : T_E03 = "" : Else : T_E03 = xlSheet.Cells(64, 6).VALUE.ToString : End If
            If xlSheet.Cells(64, 13).VALUE Is Nothing Then : E03 = "" : Else : E03 = xlSheet.Cells(64, 13).VALUE.ToString : End If
            If xlSheet.Cells(71, 13).VALUE Is Nothing Then : E20 = "" : Else : E20 = xlSheet.Cells(71, 13).VALUE.ToString : End If
            If xlSheet.Cells(72, 13).VALUE Is Nothing Then : E06 = "" : Else : E06 = xlSheet.Cells(72, 13).VALUE.ToString : End If
            If xlSheet.Cells(71, 5).VALUE Is Nothing Then : T_E20 = "" : Else : T_E20 = xlSheet.Cells(71, 5).VALUE.ToString : End If
            If xlSheet.Cells(68, 5).VALUE Is Nothing Then : T_E11_1 = "" : Else : T_E11_1 = xlSheet.Cells(68, 5).VALUE.ToString : End If
            If xlSheet.Cells(69, 5).VALUE Is Nothing Then : T_E11_2 = "" : Else : T_E11_2 = xlSheet.Cells(69, 5).VALUE.ToString : End If
            If xlSheet.Cells(70, 5).VALUE Is Nothing Then : T_E20 = "" : Else : T_E20 = xlSheet.Cells(70, 5).VALUE.ToString : End If
            If xlSheet.Cells(68, 13).VALUE Is Nothing Then : E11_1 = "" : Else : E11_1 = xlSheet.Cells(68, 13).VALUE.ToString : End If
            If xlSheet.Cells(69, 13).VALUE Is Nothing Then : E11_2 = "" : Else : E11_2 = xlSheet.Cells(69, 13).VALUE.ToString : End If
            If xlSheet.Cells(70, 13).VALUE Is Nothing Then : E10 = "" : Else : E10 = xlSheet.Cells(70, 13).VALUE.ToString : End If
            sql = "insert RT_lab_test_result_FM_phy(test_no, name, CUST, THICK, COLOR, T_L, T_W, PO,SF, DATE1, DRY, WET, filename, t_a03, t_b01_1, t_b01_2, t_b09_1, t_b09_2, t_e01_1,
                                                    t_e01_2, t_e02_1, t_e02_2, t_e06, card_no, A03, B01_1_1, B01_1_2, B01_2_1, B01_2_2, B09_1, B09_2, E01_GRAIN, E01_BACK, E02_DRY,
                                                    E02_WET, T_E03, E03, E20, E06, T_E20, T_E11_1, T_E11_2, T_E10,E11_1, E11_2, E10)
                    values(N'" & test_no & "'," & "N'" & name & "'," & "N'" & cust & "','" & thick & "',N'" & color & "','" & t_l & "','" & t_w & "',N'" & po & "','" & sf & "',
                            '" & date1 & "','" & dry & "','" & wet & "',N'" & my_filename & "','" & t_a03 & "','" & t_b01_1 & "','" & t_b01_2 & "','" & t_b09_1 & "','" & t_b09_2 & "',
                            '" & t_e01_1 & "','" & t_e01_2 & "','" & t_e02_1 & "','" & t_e02_2 & "','" & t_e06 & "','" & card_no & "','" & A03 & "','" & B01_1_1 & "','" & B01_1_2 & "',
                            '" & B01_2_1 & "','" & B01_2_2 & "','" & B09_1 & "','" & B09_2 & "','" & E01_GRAIN & "','" & E01_BACK & "','" & E02_DRY & "','" & E02_WET & "','" & T_E03 & "',
                            '" & E03 & "','" & E20 & "','" & E06 & "','" & T_E20 & "','" & T_E11_1 & "','" & T_E11_2 & "','" & T_E10 & "','" & E11_1 & "','" & E11_2 & "','" & E10 & "')"
            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)=2021 and month(date)=2"
        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 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
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Open(file_name)
        xlApp.Visible = True
        'xlApp.DisplayAlerts = False
        xlBook.Activate()
        xlBook.Parent.Windows(1).Visible = True
        For Each worksheets In xlBook.Worksheets
            If worksheets.name = "24-3" Then
                xlSheet = worksheets
            End If
        Next
        If xlSheet.Cells(6334, 3).value = "2024年05月30日-31日" Then
            xlSheet.Rows("1:6334").delete
            xlBook.Save()
        End If
        xlBook.Close()
        xlApp.Quit()
        Dim filepath As String = "k3/在制品"
        Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\在制品\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        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_retan_record_d1"  '-------中小鼓染色
        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
        ' Dim my_sqlbulk As New SqlBulkCopy(conn)
        my_sqlbulk.DestinationTableName = "dbo.rt_retan_record_d1"
        my_sqlbulk.WriteToServer(dt)
        Dim mystring As String = ""
        Dim c As Integer = dt.Rows.Count \ 1000
        Dim d As Integer = dt.Rows.Count Mod 1000
        'For xx As Integer = 0 To c
        '    mystring = ""
        '    For x As Integer = xx * 1000 To (xx + 1) * 1000 - 1
        '        If x = dt.Rows.Count - 1 Then
        '            mystring = mystring & "('" & dt.Rows(x).Item(2) & "'," & "'" & dt.Rows(x).Item(3) & "'," & "N'" & dt.Rows(x).Item(5) & "'," & "N'" & dt.Rows(x).Item(6) & "'," & "'" & dt.Rows(x).Item(7) & "')"
        '            sql = "insert into rt_retan_record_d (date,card,cust,color,weight) values " & mystring
        '            cmd.CommandText = sql
        '            cmd.ExecuteNonQuery()
        '            Exit For
        '        End If
        '        If x = (xx + 1) * 1000 - 1 Then
        '            mystring = mystring & "('" & dt.Rows(x).Item(2) & "'," & "'" & dt.Rows(x).Item(3) & "'," & "'" & dt.Rows(x).Item(5) & "'," & "'" & dt.Rows(x).Item(6) & "'," & "'" & dt.Rows(x).Item(7) & "')"
        '        Else
        '            mystring = mystring & "('" & dt.Rows(x).Item(2) & "'," & "'" & dt.Rows(x).Item(3) & "'," & "'" & dt.Rows(x).Item(5) & "'," & "'" & dt.Rows(x).Item(6) & "'," & "'" & dt.Rows(x).Item(7) & "'),"
        '        End If
        '    Next
        '    sql = "insert into rt_retan_record_d (date,card,cust,color,weight) values " & mystring
        '    cmd.CommandText = sql
        '    cmd.ExecuteNonQuery()
        'Next
        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=" & add4 & "')... ['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=" & add4 & "')... ['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_spray_daily"    '------喷台
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['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=" & add4 & "')... ['24-2$']"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt
        'Exit Sub
        my_sqlbulk.DestinationTableName = "dbo.rt_pu_measure1"
        my_sqlbulk.WriteToServer(dt)
        dt = New DataTable
        sql = "delete from rt_pu_measure2_TEMP"    '-------pu量皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['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)
        dt = New DataTable
        sql = "delete from rt_measure_daily_sample"    '-------样品量皮
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['28$']"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        ' dgv1.DataSource = dt
        'Exit Sub
        my_sqlbulk.DestinationTableName = "dbo.rt_measure_daily_sample"
        my_sqlbulk.WriteToServer(dt)
        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=" & add4 & "')... ['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)
        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=" & add4 & "')... ['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_clean_daily"   '-----扫灰
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['18-1$']"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt
        my_sqlbulk.DestinationTableName = "dbo.rt_clean_daily"
        my_sqlbulk.WriteToServer(dt)
        dt = New DataTable
        sql = "delete from rt_mill_spray_daily"   '-----喷台摔鼓
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['20-1$']"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt
        my_sqlbulk.DestinationTableName = "dbo.rt_mill_spray_daily"
        my_sqlbulk.WriteToServer(dt)
        dt = New DataTable
        sql = "delete from rt_fill_daily"   '-----填充
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['15$']"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        'dgv1.DataSource = dt
        ' Dim my_sqlbulk As New SqlBulkCopy(conn)
        my_sqlbulk.DestinationTableName = "dbo.rt_fill_daily"
        my_sqlbulk.WriteToServer(dt)
        '--------------------------------------------------------------------------------------调整
        sql = "INSERT INTO RT_RETAN_RECORD_D
                   (DATE, CARD, CUST, COLOR, WEIGHT, dd, Dep, source,type)
SELECT  date, card, cust, color, weight, tech, N'染色' AS Expr1, source,commentB
FROM      rt_retan_record_d1"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "UPDATE  RT_RETAN_RECORD_D SET type = N'打底' WHERE   (type IS NULL)"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update RT_RETAN_RECORD set card1=SUBSTRING(card,4,10) +'F' where SUBSTRING(card,3,1)='F'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update RT_RETAN_RECORD set card1=SUBSTRING(card,4,10) +'P' where SUBSTRING(card,3,1)='P'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "UPDATE  RT_RETAN_RECORD_D SET   COMa = '' WHERE   (COMa IS NULL)"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_retan_record_d set w1=0 where w1 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_retan_record_d set w2=0 where w2 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_retan_record_d set w3=0 where w3 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_retan_record_d set w4=0 where w4 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        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()
        sql = "delete from rt_dep_daily"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update RT_RETAN_RECORD set dep=N'下鼓'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = " Update RT_RETAN_RECORD_d set dep=N'染色'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Update Rt_pasting_daily set dep=N'贴板'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Update Rt_hang_daily set dep=N'吊干' "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Update Rt_toggle_daily set dep=N'绷板'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Update Rt_vacumn_daily set dep=N'真空'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Update Rt_vibration_daily set dep=N'打软'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Update Rt_buff_daily set dep=N'磨皮'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Update Rt_spray_daily set dep=N'喷台'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "Update Rt_measure_daily set dep=N'量尺' "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily (Date, card, weight, dep, tech, source) Select Date,card,wb_weight,dep,tech,source from RT_RETAN_RECORD"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily (Date, card, weight, dep) Select Date,card,weight,dep from RT_RETAN_RECORD_d"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        ''sql = "insert into rt_dep_daily (Date, card, weight, dep) Select Date,card,weight,dep from rt_wb_selection_out"
        ''cmd.CommandText = sql
        ''cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily (Date, card, weight, dep) Select  date5,card,weight,dep from Rt_pasting_daily"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily (Date, card, weight, dep) Select  date4,card,weight1,dep from Rt_hang_daily "
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily (Date, card, weight, dep) Select  Date,card,weight,dep from Rt_toggle_daily"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily (Date, card, weight, dep) Select  Date,card,weight,dep from Rt_vacumn_daily"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily (Date, card, weight, dep) Select  date5,card,weight1,dep from Rt_vibration_daily"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily(Date, card, weight, dep) Select  date4,card,weight,dep from Rt_buff_daily"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily(Date, card, weight, dep) Select  Date,card,weight,dep from Rt_spray_daily"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "insert into rt_dep_daily(Date, card, weight, dep, sf,measure_comment) Select  Date,card,weight,dep,sf,commentB from Rt_measure_daily"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set finished= 'True' where measure_comment like N'%完%' "
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set contamination= 'True' where measure_comment like N'%污染%' "
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set thick= 'True' where measure_comment like N'%厚%' "
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set suck= 'True' where measure_comment like N'%烂%' "
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set vein= 'True' where measure_comment like N'%筋%' "
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set finished= 'False' where finished is null "
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set contamination= 'False' where contamination is null"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set thick= 'False' where thick is null"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set suck= 'False' where suck is null"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        'sql = "update Rt_dep_daily set vein= 'False' where vein is null"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        '  Button53.PerformClick()
        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_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
            Dim xx As String
            Dim year1 As String
            Dim month1 As String
            Dim date1 As String
            sql = "Select top(1) date 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
            Else
                year1 = Year(cmd.ExecuteScalar)
                month1 = Month(cmd.ExecuteScalar)
                ' date1 = Day(cmd.ExecuteScalar)
                If Len(month1) = 1 Then
                    month1 = "0" & month1
                End If
                xx = year1 & month1 & "F"
            End If
            sql = "Update rt_sys_chem_main set RMB='" & p & "',lot_rmb='" & xx & "' where name =N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
        Next
        MsgBox("完成!")
    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://" & my_ip & ":7788/K3/收料通知单/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\" & address3(1)
        add4 = "d:\k3\收料通知单\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        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
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Open(file_name)
        xlApp.Visible = True
        'xlApp.DisplayAlerts = False
        xlBook.Activate()
        xlBook.Parent.Windows(1).Visible = True
        For Each worksheets In xlBook.Worksheets
            If worksheets.name = "24-3" Then
                xlSheet = worksheets
            End If
        Next
        xlSheet.Rows("1:2000").delete
        xlBook.Save()
        xlBook.Close()
        xlApp.Quit()
        Dim filepath As String = "k3/在制品"
        Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\在制品\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        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)
        Dim dt22 As 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=" & add4 & "')... ['24-3$']"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt22)
        dgv1.DataSource = dt22
        Dim my_sqlbulk As New SqlBulkCopy(conn)
        my_sqlbulk.DestinationTableName = "dbo.rt_pu_measure2_TEMP"
        my_sqlbulk.WriteToServer(dt22)
        '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(10000) As String
        Dim SERIAL(1000) As String
        Dim YEAR1(10000) As String
        Dim YEAR2(10000) As String
        Dim MONTH1(10000) As String
        Dim DATE1(10000) As String
        Dim COMMENT(10000) As String
        Dim QTY1(10000) As String
        Dim QTY2(10000) As String
        Dim QTY3(10000) As String
        Dim name(10000) As String
        Dim test_no(10000) 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)
        sql = "delete from rt_bg_io where xno like '%A0008%'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        'sql = "delete from rt_bg_io where xno like '%A0005%'"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()
        Dim AA As Integer = 10000
        Dim yy As Integer = 1
        For i = 1 To xlApp.Worksheets.Count
            xlSheet = xlApp.Workbooks(1).Worksheets(i)
            If xlApp.Sheets(i).name Like "*金*" Or xlApp.Sheets(i).name Like "*边角料*" Or xlApp.Sheets(i).name Like "*sheet2*" Or xlApp.Sheets(i).name Like "*一般贸易*" Or xlApp.Sheets(i).name Like "*成品退货*" Or xlApp.Sheets(i).name Like "*汇总*" Or xlApp.Sheets(i).name Like "*牛二层皮1*" Or xlApp.Sheets(i).name Like "*牛二层皮2*" Or xlApp.Sheets(i).name Like "*PU牛二层皮*" Or xlApp.Sheets(i).name Like "*牛二层皮3*" Or xlApp.Sheets(i).name Like "*牛二层皮4*" Then GoTo skip
            Dim year As String
            ListBox1.Items.Add(xlApp.Sheets(i).name)
            ' yy = 1
            For y = 7 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, 5).value) Or IsNumeric(xlSheet.Cells(y, 6).value)) And Not (IsDBNull(xlSheet.Cells(y, 1).value)) Then
                    MTL(yy) = xlApp.Sheets(i).name
                    YEAR1(yy) = xlSheet.Cells(y, 1).VALUE
                    If IsDBNull(xlSheet.Cells(y, 2).VALUE) Then
                        YEAR2(yy) = ""
                    Else
                        YEAR2(yy) = xlSheet.Cells(y, 2).VALUE
                    End If
                    '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
                    '----0008
                    name(yy) = xlSheet.Cells(y, 3).value
                    QTY1(yy) = xlSheet.Cells(y, 5).value
                    QTY2(yy) = xlSheet.Cells(y, 6).value
                    QTY3(yy) = xlSheet.Cells(y, 7).value
                    ''----0005
                    '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
skip:
        Next i
        For z As Integer = 1 To 10000
            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))
            '----0008
            sql = "insert into rt_bg_io (ID,xno,item,name,year,month,m_in,m_out,bal,date,date2) values(" & AA & ",N'" & file_name1 & "',N'" & MTL(z) & "',N'" & name(z) & "','" & YEAR1(z) & "','" & MONTH1(z) & "','" & QTY1(z) & "','" & QTY2(z) & "','" & QTY3(z) & "','" & YEAR1(z) & "','" & YEAR2(z) & "')"
            ''----0005
            '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) & "','" & "20" & YEAR1(z) & "/" & MONTH1(z) & "/" & DATE1(z) & "')"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            AA = AA + 1
        Next
        sql = "UPDATE rt_bg_iosetitem_new = N'蓝湿牛皮' WHERE item = N'蓝湿牛皮001'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'钛白粉' WHERE item = N'钛白粉011'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'合成油脂' WHERE item = N'合成油脂006'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'合成油脂(矿)' WHERE item = N'合成油脂（矿）005'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'合成油脂(矿)' WHERE item = N'合成油脂005'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'无机鞣料' WHERE item = N'无机鞣料003'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'聚氨酯混合液' WHERE item = N'聚氨酯混合液012'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'聚氨酯混合液' WHERE item = N'聚氨酯012'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'复鞣助剂' WHERE item = N'复鞣助剂009'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'皮革整理剂' WHERE item = N'皮革整理剂008'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'表面活性剂' WHERE item = N'表面活性剂004'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'有机鞣料' WHERE item = N'有机鞣料002'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'PU树脂' WHERE item = N'pu树脂14'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'PU树脂' WHERE item = N'pu树脂014'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'皮革促进剂' WHERE item = N'皮革促进剂013'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'蓝湿牛皮' WHERE item = N'蓝湿牛皮001'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'牛二层皮' WHERE item = N'成品总'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'制革染料' WHERE item = N'制革染料010'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem = N'牛二层皮' WHERE name LIKE '%RTCP001%' OR name LIKE '%RTCP002%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem = N'牛二层皮1' WHERE name LIKE '%RTCP001%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem = N'牛二层皮2' WHERE name LIKE '%RTCP002%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem = N'PU牛二层皮' WHERE name LIKE '%RTCP003%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'牛二层皮' WHERE name LIKE '%RTCP001%' OR name LIKE '%RTCP002%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'PU牛二层皮' WHERE name LIKE '%RTCP003%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem = N'牛二层皮3' WHERE name LIKE '%RTCP004%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'牛二层皮' WHERE name LIKE '%RTCP004%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem = N'牛二层皮4' WHERE name LIKE '%RTCP005%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'牛二层皮' WHERE name LIKE '%RTCP005%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'牛二层皮' WHERE item = N'牛二层皮'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetitem_new = N'加脂剂' WHERE item = N'加脂剂007'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetxno = 'B372719A0005' WHERE xno LIKE '%A0005%'" : cmd.CommandText = sql : cmd.ExecuteNonQuery()
        sql = "UPDATE rt_bg_iosetxno = 'B372719A0008' WHERE xno LIKE '%A0008%'" : 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
        conn.ConnectionString = connstring
        conn.Open()
        cmd.Connection = conn
        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 & "'"
            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 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://" & my_ip & ":7788/K3/销售/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\销售\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        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 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
    Private Sub Button48_Click(sender As Object, e As EventArgs) Handles Button48.Click
        Dim dr As SqlDataReader
        sql = "select date, CONTRACT_NO, name, qty, price, coin, etd, eta, received, COMMENT, SUPPLIER, ORIGINAL, serial, item, r_date, Xno, Receive_date, BG_date, deposit, balance from RT_chem_container ORDER BY 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_chem_container set SERIAL='" & aa & "' WHERE DATE='" & dr("DATE") & "' AND CONTRACT_NO='" & dr("CONTRACT_NO") & "' AND NAME=N'" & dr("NAME") & "' AND QTY='" & dr("QTY") & "' AND PRICE='" & dr("PRICE") & "' AND SUPPLIER=N'" & dr("SUPPLIER") & "' AND ORIGINAL=N'" & dr("ORIGINAL") & "'"
            cmd1.CommandText = sql
            cmd1.ExecuteNonQuery()
            aa = aa + 1
        End While
        dr.Close()
        MsgBox("完成!")
    End Sub
    Private Sub Button49_Click(sender As Object, e As EventArgs) Handles Button49.Click
        Dim dr As SqlDataReader
        sql = "update rt_bg_in_detail set id=null "
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "select * from rt_bg_in_detail 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 xno='" & dr("xno").ToString & "' and name=N'" & dr("name").ToString & "' and qty=N'" & dr("qty").ToString & "' and price='" & dr("price") & "' and date1='" & dr("date1").ToString & "'"
            cmd1.CommandText = sql
            cmd1.ExecuteNonQuery()
            aa = aa + 1
        End While
        dr.Close()
        MsgBox("完成!")
    End Sub
    Private Sub Button50_Click(sender As Object, e As EventArgs) Handles Button50.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
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet
        Dim file_name1 As String = OpenFileDialog1.SafeFileName
        xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        xlApp.DisplayAlerts = False
        xlBook = xlApp.Workbooks.Open(file_name,, True)
        xlBook.Activate()
        Dim year As String = OpenFileDialog1.SafeFileName.Substring(0, 4)
        Dim month As String = OpenFileDialog1.SafeFileName.Substring(4, 2)
        sql = "select * from ht_retanrcp where year(date)='" & year & "' and month(date)='" & month & "' ORDER BY date DESC, rcp_no DESC"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        ' 删除现有数据（如果有）
        sql = "delete from ht_retanrcp where rcp_no like N'" & file_name1 & "%'"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        ' 循环处理每一个工作表
        For i = 1 To xlApp.Worksheets.Count
            Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
            xlSheet = xlApp.Workbooks(1).Worksheets(i)
            xlSheet.Activate()
            Dim no, rcp_no, retan_date, name, spec, weight, selection, chem, percents, time, comment As String
            If xlSheet.Cells(1, 6).value Is Nothing Then
                no = xlSheet.Cells(1, 4).value
            Else
                no = xlSheet.Cells(1, 4).value & "  " & xlSheet.Cells(1, 6).value
            End If
            retan_date = xlSheet.Cells(2, 4).value
            name = xlSheet.Cells(1, 10).value
            spec = xlSheet.Cells(2, 10).value
            weight = xlSheet.Cells(2, 14).value
            selection = xlSheet.Cells(3, 4).value
            rcp_no = file_name1 & "_" & xlSheet.Name
            Dim aa As Integer = 1
            For j = 6 To 100
                If IsDBNull(xlSheet.Cells(j, 3).value) Then Exit For
                chem = xlSheet.Cells(j, 6).value
                percents = xlSheet.Cells(j, 3).value
                time = xlSheet.Cells(j, 9).value
                comment = xlSheet.Cells(j, 10).value
                ' 查询数据库，看记录是否已经存在
                sql = "SELECT COUNT(*) FROM ht_retanrcp WHERE rcp_no = N'" & rcp_no & "' AND no = N'" & no & "'"
                cmd.CommandText = sql
                Dim recordCount As Integer = Convert.ToInt32(cmd.ExecuteScalar())
                ' 如果记录不存在，则插入新数据
                If recordCount = 0 Then
                    sql = "INSERT INTO ht_retanrcp (item, rcp_no, no, name, date, spec, weight, selection, chem, percents, time, comment) " &
                  "VALUES (" & aa & ", N'" & rcp_no & "', N'" & no & "', N'" & name & "', '" & retan_date & "', '" & spec & "', '" & weight & "', N'" & selection & "', N'" & chem & "', '" & percents & "', N'" & time & "', N'" & comment & "')"
                    cmd.CommandText = sql
                    cmd.ExecuteScalar()
                    aa += 1
                End If
            Next j
        Next
        xlBook.Save()
        xlBook.Close()
        Marshal.ReleaseComObject(xlBook)
    End Sub
    Private Sub Button51_Click(sender As Object, e As EventArgs) Handles Button51.Click
        Dim dr As SqlDataReader
        Dim dt As New DataTable
        sql = "select name from rt_chem_stock_month2"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "select m_id from rt_sys_chem where name=N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            If IsDBNull(cmd.ExecuteScalar) Then
            Else
                Dim my_id As String
                my_id = cmd.ExecuteScalar
                sql = "update rt_chem_stock_month2 set code='" & my_id & "' where name=N'" & dt.Rows(x).Item("name") & "'"
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()
            End If
        Next
        MsgBox("完成!")
    End Sub
    Private Sub Button52_Click(sender As Object, e As EventArgs) Handles Button52.Click
        Dim dr As SqlDataReader
        Dim dt As New DataTable
        sql = "select * from table_1"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        Dim a(5) As String
        Dim b(5) As String
        Dim c(5) As String
        Dim d(5) As String
        Dim mystart As String
        Dim myend As String
        Dim mystring As String
        Dim zz As TimeSpan
        For xx As Integer = 4 To 34
            For x As Integer = 0 To dt.Rows.Count - 1
                mystring = dt.Rows(x).Item(xx).ToString
                If mystring <> "" Then
                    a = Split(dt.Rows(x).Item(xx), "换行")
                    If IsDBNull(a(0)) Then
                        sql = "xxxx"
                    Else
                        c = Split(a(0), "空格")
                        b = Split(a(UBound(a)), "空格")
                        If b(0) = "" Or b(0) = " " Then
                            sql = "xxxx"
                            d = Split(a(UBound(a) - 1), "空格")
                            Try
                                '  zz = DateTime.Parse(d(0)) - DateTime.Parse(c(0))
                            Catch
                                ListBox1.Items.Add(dt.Rows(x).Item(0) & "--" & xx & "日--" & dt.Rows(x).Item(xx))
                            Finally
                                'sql = "insert into table_2 (name,group1,dep,[" & xx - 3 & "]) values (N'" & dt.Rows(x).Item("name") & "',N'" & dt.Rows(x).Item("group1") & "',N'" & dt.Rows(x).Item("dep") & "'," & zz.TotalMinutes / 60 & ")"
                            End Try
                        Else
                            d(0) = b(0)
                            Try
                                '    zz = DateTime.Parse(d(0)) - DateTime.Parse(c(0))
                            Catch ex As Exception
                                ListBox1.Items.Add(dt.Rows(x).Item(0) & "--" & xx & "日--" & "--" & dt.Rows(x).Item(xx))
                            Finally
                                ' sql = "insert into table_2 (name,group1,dep,[" & xx - 3 & "]) values (N'" & dt.Rows(x).Item("name") & "',N'" & dt.Rows(x).Item("group1") & "',N'" & dt.Rows(x).Item("dep") & "'," & zz.TotalMinutes / 60 & ")"
                            End Try
                        End If
                        sql = "update table_2 set [" & xx - 3 & "]=" & zz.TotalMinutes / 60 & " where name=N'" & dt.Rows(x).Item("name") & "'"
                        cmd.CommandText = sql
                        cmd.ExecuteScalar()
                    End If
                End If
            Next
        Next
        MsgBox("完成")
    End Sub
    Public Sub Button53_Click(sender As Object, e As EventArgs) Handles Button53.Click   '''----更新retanrecord_完不良污染
        Dim StartTime As System.DateTime
        Dim EndTime As System.DateTime
        Dim ts As System.TimeSpan
        Dim dr As SqlDataReader
        Dim dt As New DataTable
        StartTime = System.DateTime.Now
        sql = "select card,commentB from rt_measure_daily where commentB like N'%完%'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "update rt_retan_record set finished='True' where card='" & dt.Rows(x).Item("card") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        sql = "select card,commentB from rt_measure_daily where commentB like N'%完%'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "update rt_retan_record set finished='True' where card='" & dt.Rows(x).Item("card") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        dt = New DataTable
        sql = "select card,commentB from rt_measure_daily where commentB like N'%不良%'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "update rt_retan_record set bad='True' where card='" & dt.Rows(x).Item("card") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        dt = New DataTable
        sql = "select card,commentB from rt_measure_daily where commentB like N'%烂%'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "update rt_retan_record set suck='True' where card='" & dt.Rows(x).Item("card") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        dt = New DataTable
        sql = "select card,commentB from rt_measure_daily where commentB like N'%污染%'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "update rt_retan_record set Contamination='True' where card='" & dt.Rows(x).Item("card") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        dt = New DataTable
        sql = "select card,commentB from rt_measure_daily where commentB like N'%筋%'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "update rt_retan_record set vein='True' where card='" & dt.Rows(x).Item("card") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        dt = New DataTable
        sql = "select card,commentB from rt_measure_daily where commentB like N'%厚%'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "update rt_retan_record set thick='True' where card='" & dt.Rows(x).Item("card") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        sql = "update rt_retan_record set finished='False' where finished is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_retan_record set vein='False' where vein is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_retan_record set bad='False' where bad is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_retan_record set suck='False' where suck is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_retan_record set contamination='False' where contamination is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_retan_record set thick='False' where thick is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_retan_record set phy_test='False' where phy_test is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
                sql = "update rt_retan_record set che_test='False' where che_test is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_retan_record set measure='False' where measure is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_retan_record set measure_percent=null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "UPDATE  RT_RETAN_RECORD
SET           measure_percent = p.Expr1
FROM      (SELECT  card, SUM(sf) AS Expr1
                   FROM       Rt_measure_daily
                   GROUP BY card) AS p INNER JOIN
                   RT_RETAN_RECORD ON p.card = RT_RETAN_RECORD.card
WHERE   (p.Expr1 IS NOT NULL)"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update RT_RETAN_RECORD set measure='True' where measure_percent is not null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        EndTime = System.DateTime.Now
        ts = EndTime.Subtract(StartTime)
        MsgBox("ok")
    End Sub
    Private Sub Button54_Click(sender As Object, e As EventArgs) Handles Button54.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
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Open(file_name)
        xlApp.Visible = True
        'xlApp.DisplayAlerts = False
        xlBook.Activate()
        xlBook.Parent.Windows(1).Visible = True
        If (xlApp.Cells(1, 1).value.ToString = "已修改") Then
            '  MsgBox("已修改过了，不用再修改")
        Else
            xlBook.ActiveSheet.rows("1:11").delete
            xlApp.Cells(1, 1) = "已修改"
            xlBook.Save()
        End If
        xlBook.Close()
        xlApp.Quit()
        Dim filepath As String = "k3/在制品"
        Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\在制品\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        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_inprocess_crust"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['2023年03月 $']"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        ' dgv1.DataSource = dt
        Dim my_sqlbulk As New SqlBulkCopy(conn)
        my_sqlbulk.DestinationTableName = "dbo.rt_inprocess_crust"
        my_sqlbulk.WriteToServer(dt)
        MsgBox("导入完成")
    End Sub
    Private Sub Button55_Click(sender As Object, e As EventArgs) Handles Button55.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 = "k3/蓝皮"
        Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\蓝皮\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        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_wetbluein"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... ['2022年蓝皮到货明细 $']"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt
        Dim my_sqlbulk As New SqlBulkCopy(conn)
        my_sqlbulk.DestinationTableName = "dbo.rt_wetbluein"
        my_sqlbulk.WriteToServer(dt)
        sql = "update rt_wetbluein set supplier='MW' where supplier='M W'"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_wetbluein set supplier=N'佳旺' where supplier like N'%佳%'"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_wetbluein set supplier=N'永泰' where supplier like N'%永%'"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        MsgBox("导入完成")
    End Sub
    Private Sub Button56_Click(sender As Object, e As EventArgs) Handles Button56.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
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Open(file_name)
        xlApp.Visible = True
        'xlApp.DisplayAlerts = False
        xlBook.Activate()
        xlBook.Parent.Windows(1).Visible = True
        For Each worksheets In xlBook.Worksheets
            If worksheets.name = "蓝皮" Then
                xlSheet = worksheets
            End If
        Next
        If (xlSheet.Cells(1, 1).value Is Nothing) Then
            xlSheet.Rows("1:2").delete
            xlSheet.Cells(1, 1) = "已修改"
            xlBook.Save()
        Else
        End If
        xlBook.Close()
        xlApp.Quit()
        Dim filepath As String = "k3/蓝皮"
        Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\蓝皮\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        Dim showUI As Boolean = True
        Dim connectionTimeout As Integer = 500
        add4 = "d:\k3\蓝皮\" & OpenFileDialog1.SafeFileName
        sourceFileName = file_name
        address2 = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim client1 As WebClient = New WebClient
        client1.Credentials = New NetworkCredential(userName, password)
        client1.Proxy = GlobalProxySelection.GetEmptyWebProxy()
        client1.UploadFile(address2, sourceFileName)
        sql = "delete from rt_wetblue"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "Select * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=" & add4 & "')... [蓝皮$]"
        cmd.CommandText = sql
        dt = New DataTable
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt
        Dim my_sqlbulk As New SqlBulkCopy(conn)
        my_sqlbulk.DestinationTableName = "dbo.rt_wetblue"
        my_sqlbulk.WriteToServer(dt)
        sql = "delete from rt_wetblue where CONTRACT_NO IS NULL"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set type=N'单边身皮' where source2 like N'%单%'                                                                                           "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set type=N'身皮' where source2 like N'%身皮%' and source2 not like N'%单边%'                                                                  "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set type=N'去头' where source2 like N'%去头%'                                                                                               "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set type=N'牛头' where source2 like N'%牛头%'                                                                                                 "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set type=N'修边' where source2 like N'%修边%'                                                                                               "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set type=N'普通二层' where type is null                                                                                                       "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'阿根廷',countrycode='AR',flag='https://www.worldometers.info/img/flags/ar-flag.gif' where source2 like N'%阿根廷%'          "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'乌拉圭',countrycode='UY',flag='https://www.worldometers.info/img/flags/uy-flag.gif' where source2 like N'%乌拉圭%'            "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'巴西',countrycode='BR',flag='https://www.worldometers.info/img/flags/br-flag.gif' where source2 like N'%巴西%'              "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'日本',countrycode='JP',flag='https://www.worldometers.info/img/flags/ja-flag.gif' where source2 like N'%日本%'                "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'泰国',countrycode='TH',flag='https://www.worldometers.info/img/flags/th-flag.gif' where source2 like N'%泰国%'              "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'孟加拉',countrycode='BD',flag='https://www.worldometers.info/img/flags/bg-flag.gif' where source2 like N'%孟加拉%'            "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'南非',countrycode='ZA',flag='https://www.worldometers.info/img/flags/sf-flag.gif' where source2 like N'%南非%'              "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'智利',countrycode='CL',flag='https://www.worldometers.info/img/flags/ci-flag.gif' where source2 like N'%智利%'                "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'越南',countrycode='VN',flag='https://www.worldometers.info/img/flags/vm-flag.gif' where source2 like N'%越南%'              "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'意大利',countrycode='IT',flag='https://www.worldometers.info/img/flags/it-flag.gif' where source2 like N'%意大利%'            "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'新西兰',countrycode='NZ',flag='https://www.worldometers.info/img/flags/nz-flag.gif' where source2 like N'%新西兰%'          "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'澳洲',countrycode='AU',flag='https://www.worldometers.info/img/flags/as-flag.gif' where source2 like N'%澳%'                  "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'德国',countrycode='DE',flag='https://www.worldometers.info/img/flags/gm-flag.gif' where source2 like N'%德%'                "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'墨西哥',countrycode='MX',flag='https://www.worldometers.info/img/flags/mx-flag.gif' where source2 like N'%墨%'                "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'欧洲',countrycode='AR',flag='https://www.worldometers.info/img/flags/fi-flag.gif' where source2 like N'%欧洲%'              "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'俄罗斯',countrycode='RU',flag='https://www.worldometers.info/img/flags/rs-flag.gif' where source2 like N'%俄%'                "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'西班牙',countrycode='ES',flag='https://www.worldometers.info/img/flags/sp-flag.gif' where source2 like N'%西班牙%'          "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'美国',countrycode='US',flag='https://www.worldometers.info/img/flags/us-flag.gif' where source2 like N'%美国%'                "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "  Update RT_WETBLUE set country=N'台湾',countrycode='CN',flag='https://www.worldometers.info/img/flags/ch-flag.gif' where source2 like N'%台湾%'              "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        MsgBox("完成!")
    End Sub
    Private Sub Button57_Click(sender As Object, e As EventArgs) Handles Button57.Click
        Dim dt As New DataTable
        cmd.Connection = conn
        sql = "select filename from rt_lab_test_result_fm_phy where test_no=''"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dt.Columns.Add("comment")
        Dim dr As SqlDataReader
        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
        Dim W300b As String
        Dim W316A As String
        Dim W316B As String
        Dim W326A As String
        Dim W326B As String
        Dim W327A As String
        Dim W327B As String
        Dim W340F As String
        Dim W341D As String
        Dim W343j_1 As String
        Dim pct As String
        Dim uv As String
        Dim ph As String
        Dim t_W300b As String
        Dim t_W316A As String
        Dim t_W316B As String
        Dim t_W326A As String
        Dim t_W326B As String
        Dim t_W327A As String
        Dim t_W327B As String
        Dim t_W340F As String
        Dim t_W341D As String
        Dim t_W343j_1 As String
        Dim t_pct As String
        Dim t_uv As String
        Dim t_ph As String
        Dim cardno As String
        Dim test_no As String
        Dim color As String
        Dim name As String
        xlApp = CreateObject("Excel.Application")
        'xlBook = xlApp.Workbooks.Add
        'xlApp.Visible = True
        xlApp.DisplayAlerts = False
        For x As Integer = 0 To dt.Rows.Count - 1
            xlBook = xlApp.Workbooks.Open("D:\K3\测试报告\2022年实验室反毛出货报表\" & dt.Rows(x).Item("filename"))
            xlBook.Activate()
            xlSheet = xlBook.Worksheets(1)
            W300b = xlSheet.Cells(7, 6).VALUE
            W316A = xlSheet.Cells(8, 6).VALUE
            W316B = xlSheet.Cells(9, 6).VALUE
            W326A = xlSheet.Cells(10, 6).VALUE
            W326B = xlSheet.Cells(11, 6).VALUE
            W327A = xlSheet.Cells(12, 6).VALUE
            W327B = xlSheet.Cells(13, 6).VALUE
            W340F = xlSheet.Cells(20, 6).VALUE
            W341D = xlSheet.Cells(21, 6).VALUE
            W343j_1 = xlSheet.Cells(22, 6).VALUE
            pct = xlSheet.Cells(28, 6).VALUE
            uv = xlSheet.Cells(29, 6).VALUE
            ph = xlSheet.Cells(30, 6).VALUE
            t_W300b = xlSheet.Cells(7, 5).VALUE
            t_W316A = xlSheet.Cells(8, 5).VALUE
            t_W316B = xlSheet.Cells(9, 5).VALUE
            t_W326A = xlSheet.Cells(10, 5).VALUE
            t_W326B = xlSheet.Cells(11, 5).VALUE
            t_W327A = xlSheet.Cells(12, 5).VALUE
            t_W327B = xlSheet.Cells(13, 5).VALUE
            t_W340F = xlSheet.Cells(20, 5).VALUE
            t_W341D = xlSheet.Cells(21, 5).VALUE
            t_W343j_1 = xlSheet.Cells(22, 5).VALUE
            t_pct = xlSheet.Cells(28, 5).VALUE
            t_uv = xlSheet.Cells(29, 5).VALUE
            t_ph = xlSheet.Cells(30, 5).VALUE
            cardno = Microsoft.VisualBasic.Right(xlSheet.Cells(47, 1).VALUE, 11)
            color = xlSheet.Cells(44, 2).VALUE
            name = xlSheet.Cells(39, 2).VALUE
            test_no = Microsoft.VisualBasic.Right(xlSheet.Cells(4, 2).VALUE, 10)
            sql = "insert RT_lab_test_result_FM_phy(cardno,test_no,name,CUST,COLOR,a03,filename,
b09_1,b09_2,b01_1_1,b01_1_2,b01_2_1,
b01_2_2,E02_DRY,c05,e06,E01_GRAIN,
e03,e20,
t_b09_1,t_b09_2,t_b01_1_1,t_b01_1_2,t_b01_2_1,
t_b01_2_2,t_E02_1,t_c05,t_e06,t_E01_1,
t_e03,t_e20)
values(N'" & cardno & "','" & test_no & "'," & "N'" & name & "'," & "N'PUMA',N'" & color & "',N'" & W300b & "',N'" & dt.Rows(x).Item("filename") & "','" &
           W316A & "','" & W316B & "','" & W326A & "','" & W326B & "','" & W327A & "','" &
           W327B & "','" & W340F & "','" & W341D & "','" & W343j_1 & "','" & pct & "','" &
           uv & "','" & ph & "','" &
           t_W316A & "','" & t_W316B & "','" & t_W326A & "','" & t_W326B & "','" & t_W327A & "','" &
           t_W327B & "','" & t_W340F & "','" & t_W341D & "','" & t_W343j_1 & "','" & t_pct & "','" &
           t_uv & "','" & t_ph & "')"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        MsgBox("完成!")
    End Sub
    Private Sub Button58_Click(sender As Object, e As EventArgs) Handles Button58.Click
        Dim dt As New DataTable
        ListBox1.Visible = True
        Dim mydate As Date
        sql = "update rt_sys_chem_main set beginstock=null,buy=null,used=null,lastused=null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "select date,name,sum(stock1) as stock from rt_chem_stock_month1 group by name,date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            mydate = dt.Rows(x).Item("date")
            sql = "update rt_sys_chem_main set beginstock='" & dt.Rows(x).Item("stock") & "' where name=N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            If cmd.ExecuteNonQuery <> 1 Then
                ListBox1.Items.Add(dt.Rows(x).Item("name"))
            End If
            sql = "UPDATE  RT_sys_chem_main
                SET           buy = p.Expr1
                FROM      (SELECT  name, SUM(qty) AS Expr1
                   FROM       Rt_chem_in where rt_chem_in.date>'" & dt.Rows(x).Item("date") & "'
                   GROUP BY name) AS p INNER JOIN
                   RT_sys_chem_main ON p.name = RT_sys_chem_main.name
                WHERE   rt_sys_chem_main.name=N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
            sql = "UPDATE  RT_sys_chem_main  SET used = p.Expr1 FROM      (SELECT  name, SUM(weight) AS Expr1  FROM Rt_sys_chem_out where rt_sys_chem_out.date>'" & dt.Rows(x).Item("date") & "'  GROUP BY name) AS p 
INNER JOIN   RT_sys_chem_main ON p.name = RT_sys_chem_main.name  WHERE   rt_sys_chem_main.name=N'" & dt.Rows(x).Item("name") & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        sql = "update rt_sys_chem_main set buy=0 where buy is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_sys_chem_main set beginstock=0 where beginstock is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_sys_chem_main set used=0 where used is null"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        sql = "update rt_sys_chem_main set balance= beginstock+buy-used "
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        dt = New DataTable
        sql = "select name from rt_sys_chem_main"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "select date from rt_sys_chem_out where name=N'" & dt.Rows(x).Item("name") & "' order by date desc"
            cmd.CommandText = sql
            If IsDate(cmd.ExecuteScalar) Then
                sql = "update rt_sys_chem_main set lastused='" & cmd.ExecuteScalar & "' where name='" & dt.Rows(x).Item("name") & "'"
                cmd.CommandText = sql
                cmd.ExecuteScalar()
            End If
        Next
        MsgBox("完成!")
    End Sub
    Private Sub Button59_Click(sender As Object, e As EventArgs) Handles Button59.Click '更新皮胚进销存
        sql = "SELECT  p.color, p.size, p.boh, SUM(s.beginsf) AS beginsf, SUM(q.insf) AS insf, SUM(r.outsf) AS outsf
FROM      (SELECT  color, size, boh
                   FROM       RT_PU_crust_class
                   GROUP BY color, size, boh) AS p LEFT OUTER JOIN
                       (SELECT  color, size, boh, SUM(qty) AS insf
                        FROM       rt_PUin
                        WHERE    (date > '2022/10/31')
                        GROUP BY color, size, boh) AS q ON p.size = q.size AND p.boh = q.boh AND p.color = q.color LEFT OUTER JOIN
                       (SELECT  color, size, boh, SUM(qty) AS outsf
                        FROM       RT_PUout
                        WHERE    (date > '2022/10/31') 
                        GROUP BY color, size, boh) AS r ON p.size = r.size AND p.boh = r.boh AND p.color = r.color LEFT OUTER JOIN
                       (SELECT  color, size, boh, SUM(sf) AS beginsf
                        FROM       RT_PU_crust_stock
                        WHERE    (date = '2022/10/31')
                        GROUP BY color, size, boh) AS s ON p.size = s.size AND p.boh = s.boh AND p.color = s.color
WHERE   (NOT (s.beginsf IS NULL)) OR
                   (NOT (q.insf IS NULL)) OR
                   (NOT (r.outsf IS NULL))
GROUP BY p.color, p.size, p.boh, q.color, q.size, q.boh, r.color, r.size, r.boh, s.color, s.size, s.boh
ORDER BY p.color, p.size, p.boh"
        cmd.CommandText = sql
        Dim dt As New DataTable
        da.SelectCommand = cmd
        da.Fill(dt)
        sql = "delete from rt_pu_crust_realtimestock"
        cmd.CommandText = sql
        cmd.ExecuteScalar()
        For x As Integer = 0 To dt.Rows.Count - 1
            sql = "insert into rt_pu_crust_realtimestock (date,color,size,boh,beginsf,insf,outsf) values ('" & Today & "',N'" & dt.Rows(x).Item("color") & "','" & dt.Rows(x).Item("size") & "',N'" & dt.Rows(x).Item("boh") & "','" &
                dt.Rows(x).Item("beginsf") & "','" & dt.Rows(x).Item("insf") & "','" & dt.Rows(x).Item("outsf") & "')"
            cmd.CommandText = sql
            cmd.ExecuteScalar()
        Next
        MsgBox("完成!")
    End Sub
    Private Sub Timer1_Timer()
        Static t10s As Date
        Static t30s As Date
        Static t60 As Date
        Static tday As Date
        If Now >= t10s Then
            t10s = DateAdd("s", 10, Now)
            Debug.Print(Now)
        End If
        'If Now >= t30s Then
        '    t30s = DateAdd("s", 30, Now)
        '    Print("30秒:" & Now)
        'End If
        'If Now >= t60 Then
        '    t60 = DateAdd("n", 60, Now)
        '    Print("60分:" & Now)
        'End If
        'If Now >= tday Then
        '    tday = DateAdd("d", 1, Now)
        '    Print("一天:" & Now)
        'End If
    End Sub
    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        Static t10s As Date
        Static t30s As Date
        Static t60 As Date
        Static tday As Date
        If Now >= t10s Then
            t10s = DateAdd("s", 10, Now)
            Debug.Print(Now)
            Label2.Text = Now
        End If
        pbCount += 1
        Label1.Text = Now
        If ProgressBar1.Value < 100 Then
            Me.ProgressBar1.Value = pbCount
        Else
            Me.ProgressBar1.Value = 0
            pbCount = 1
        End If
        'If Now >= t30s Then
        '    t30s = DateAdd("s", 30, Now)
        '    Print("30秒:" & Now)
        'End If
        'If Now >= t60 Then
        '    t60 = DateAdd("n", 60, Now)
        '    Print("60分:" & Now)
        'End If
        'If Now >= tday Then
        '    tday = DateAdd("d", 1, Now)
        '    Print("一天:" & Now)
        'End If
    End Sub
    Public Sub OpenBatConn()
        Dim MyConn As Double
        MyConn = Shell("d:\xxx.bat", vbHide)
    End Sub
    Private Sub Button61_Click(sender As Object, e As EventArgs) Handles Button61.Click
        OpenBatConn()
    End Sub
    Private Sub Button62_Click(sender As Object, e As EventArgs) Handles Button62.Click
        Dim dt As New DataTable
        sql = "select trim(name) as name,sum(stock1) as qty from RT_CHEM_STOCK_MONTH2 where date='2023/1/31'  group by name having sum(stock1)>10"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dt.Columns.Add("out")
        'dt.Columns.Add("buy12f")
        'dt.Columns.Add("buy12s")
        dt.Columns.Add("lastbuy")
        dt.Columns.Add("lastbuy1")
        Dim my_chemout As Double
        For x As Integer = 0 To dt.Rows.Count - 1  '12月出
            my_chemout = 0
            sql = "select sum(weight) as out from RT_sys_chem_out where name=N'" & dt.Rows(x).Item("name") & "' and date between '2022/12/1' and '2022/12/31'"
            cmd.CommandText = sql
            If IsDBNull(cmd.ExecuteScalar) Then
                my_chemout = 0
            Else
                my_chemout = cmd.ExecuteScalar
            End If
            dt.Rows(x).Item("out") = my_chemout
        Next
        'For x As Integer = 0 To dt.Rows.Count - 1  '12月入 非保
        '    Dim my_date As Date
        '    sql = "select top(1) date from RT_CHEM_IN_PRICE where name=N'" & dt.Rows(x).Item("name") & "' and qty>0  and date between '2023/12/1' and '2023/12/31' order by date desc"
        '    cmd.CommandText = sql
        '    If cmd.ExecuteScalar Is Nothing Then
        '    Else
        '        my_date = cmd.ExecuteScalar
        '        dt.Rows(x).Item("buy12f") = my_date
        '    End If
        'Next
        'For x As Integer = 0 To dt.Rows.Count - 1 '12月入 保
        '    Dim my_date1 As Date
        '    sql = "select top(1) date from RT_CHEM_S_IN_PRICE where matl_name=N'" & dt.Rows(x).Item("name") & "' and qty>0 and date between '2023/12/1' and '2023/12/31' order by date desc"
        '    cmd.CommandText = sql
        '    If cmd.ExecuteScalar Is Nothing Then
        '    Else
        '        my_date1 = cmd.ExecuteScalar
        '        dt.Rows(x).Item("buy12s") = my_date1
        '    End If
        'Next
        Dim my_chem As String
        For x As Integer = 0 To dt.Rows.Count - 1
            my_chem = ""
            sql = "select top(1) * from RT_CHEM_IN_PRICE where name=N'" & dt.Rows(x).Item("name") & "' and qty>0  and date <'2023/1/1' order by date desc"
            cmd.CommandText = sql
            If cmd.ExecuteScalar Is Nothing Then
            Else
                my_chem = cmd.ExecuteScalar
                dt.Rows(x).Item("lastbuy") = my_chem
            End If
        Next
        Dim my_chem1 As String
        For x As Integer = 0 To dt.Rows.Count - 1
            my_chem = ""
            sql = "select top(1) * from RT_CHEM_S_IN_PRICE where matl_name=N'" & dt.Rows(x).Item("name") & "' and qty>0  and date <'2023/1/1' order by date desc"
            cmd.CommandText = sql
            If cmd.ExecuteScalar Is Nothing Then
            Else
                my_chem1 = cmd.ExecuteScalar
                dt.Rows(x).Item("lastbuy1") = my_chem1
            End If
        Next
        dgv1.DataSource = dt
    End Sub
    Private Sub Button46_Click_1(sender As Object, e As EventArgs) Handles Button46.Click
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Add
        xlApp.Visible = True
        xlBook.Activate()
        xlBook.Parent.Windows(1).Visible = True
        xlSheet = xlBook.Worksheets.Add
        xlSheet.Name = "化工"
        xlSheet.Activate()
        sql = "select name as 化工品名,stock1+stock2+stock3 as 数量 from RT_CHEM_STOCK_MONTH2 where year(date)='" & dgv2.Rows(dgv2.CurrentCell.RowIndex).Cells("年").Value & "' and month(date)='" & dgv2.Rows(dgv2.CurrentCell.RowIndex).Cells("月").Value & "'"
        cmd.CommandText = sql
        Dim dt As New DataTable
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv2.DataSource = dt
        dgv2.SelectAll()
        Me.dgv2.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        Clipboard.SetDataObject(dgv2.GetClipboardContent().GetData(DataFormats.Text.ToString))
        System.Threading.Thread.Sleep(200)
        xlApp.Cells(2, 2).Select()
        If IsDBNull(dgv2.GetClipboardContent().GetData(DataFormats.Text.ToString)) Then
        Else
            xlSheet.PasteSpecial()
            Clipboard.SetDataObject(DBNull.Value)
        End If
    End Sub
    Private Sub Button63_Click(sender As Object, e As EventArgs) Handles Button63.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
        Dim filepath As String = "k3/在制品"
        Dim sourceFileName As String = file_name
        Dim address2 As String = "ftp://" & my_ip & ":7788/" & filepath & "/" & OpenFileDialog1.SafeFileName
        Dim address3() As String
        address3 = Split(address2, "ftp://" & my_ip & ":7788/")
        Dim add4 As String
        add4 = "d:\k3\在制品\" & OpenFileDialog1.SafeFileName
        Dim userName As String = "matchy3c"
        Dim password As String = "Lafayette11"
        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_finish_stock"  '-------
        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
        dt.Columns.Add("filename")
        For x As Integer = 0 To dt.Rows.Count - 1
            dt.Rows(x).Item("filename") = file_name
        Next
        Dim my_sqlbulk As New SqlBulkCopy(conn)
        my_sqlbulk.DestinationTableName = "dbo.rt_finish_stock"
        my_sqlbulk.WriteToServer(dt)
        MsgBox("导入完成!")
    End Sub
End Class