HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/03、化料管理/FrmChem_Main.vb

Imports System.Data.SqlClient
Imports System.Reflection
Imports Microsoft.Office.Interop.Excel.XlUnderlineStyle
Imports Microsoft.Office.Interop.Excel.Constants
Imports Microsoft.Office.Interop.Excel.XlBordersIndex
Imports Microsoft.Office.Interop.Excel.XlLineStyle
Imports Microsoft.Office.Interop.Excel.XlBorderWeight
Imports Microsoft.Office.Interop.Excel.XlPrintLocation
Imports Microsoft.Office.Interop.Excel.XlPageOrientation
Imports Microsoft.Office.Interop.Excel.XlPaperSize
Imports Microsoft.Office.Interop.Excel.XlOrder
Imports Microsoft.Office.Interop.Excel.XlPrintErrors
Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop.Excel.XlThemeColor
Imports Microsoft.Office.Interop.Excel.XlThemeFont
Imports Microsoft.Office.Interop
Public Class FrmChem_Main
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Dim sql As String
    Dim period As Date
    Dim g_accounted As Boolean
    Dim g_period As Integer
    Private Sub FrmChemMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim conn As New SqlConnection
        Dim DT1 As New DataTable
        Dim DT2 As New DataTable
        Dim DT3 As New DataTable

        conn.ConnectionString = connstring
        conn.Open()

        cmd.Connection = conn

        dgv2.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        dgv_tech.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText



        sql = "select period from rt_chem_accounting where accounted=1 order by date desc"
        cmd.CommandText = sql
        period = cmd.ExecuteScalar


        sql = "select accounted from rt_chem_accounting  order by date desc"
        cmd.CommandText = sql
        g_accounted = cmd.ExecuteScalar



        If Month(Now) - Month(period) = 1 And g_accounted = False Then

            g_period = 1

        ElseIf Month(Now) - Month(period) = 1 And g_accounted = True Then

            g_period = 2


        End If


        dgv1.RowHeadersWidth = 20
        dgv2.RowHeadersWidth = 20
        dgv3.RowHeadersWidth = 20
        dgv4.RowHeadersWidth = 20
        dgv_tech.RowHeadersWidth = 20
        DGV_STOCK.RowHeadersWidth = 20

        'sql = "SELECT name AS 品名 from rt_sys_chem_main  order by name"


        'cmd.CommandText = sql

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

        'dgv1.DataSource = dt
        'dgv1.Columns(0).Width = 200



    End Sub

    Private Sub TextBox1_KeyUp(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyUp

        Dim dt As New DataTable


        If e.KeyCode <> Keys.Enter Then

        Else

            Dim my_dr As SqlDataReader


            sql = "select sys_name as 品名 from HT_chem_main WHERE sys_name LIKE N'%" & TextBox1.Text & "%' group by sys_name"
            cmd.CommandText = sql


            da.SelectCommand = cmd
            da.Fill(dt)

            dgv1.DataSource = dt
            dgv1.Columns(0).Width = 200

        End If


    End Sub



    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.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



        xlApp = CreateObject("Excel.Application")

        xlBook = xlApp.Workbooks.Add


        xlApp.DisplayAlerts = False
        xlBook.Activate()
        xlBook.Parent.Windows(1).Visible = True
        xlSheet = xlBook.Worksheets.Add
        xlSheet.Activate()
        xlApp.Visible = True


        Dim xx As Integer = 0
        Dim y As Integer = 0
        Dim P1 As Image
        Dim P2 As Image
        Dim P3 As Image
        Dim P4 As Image
        Dim P5 As Image
        Dim P6 As Image
        Dim P7 As Image
        Dim P8 As Image
        Dim P9 As Image


        Dim FILE_NAME As String
        Dim FILE_NAME1 As String


        Dim sPath As String

        sPath = "F:\化料\GHS\"

        Dim p(10) As Image


        p(1) = Image.FromFile("C:\GHS\1.jpg")  '有毒
        p(2) = Image.FromFile("C:\GHS\2.jpg") '危险
        p(3) = Image.FromFile("C:\GHS\3.jpg") '腐蚀
        p(4) = Image.FromFile("C:\GHS\4.jpg") '爆炸
        p(5) = Image.FromFile("C:\GHS\5.jpg") '健康
        p(6) = Image.FromFile("C:\GHS\6.jpg") '水生物
        p(7) = Image.FromFile("C:\GHS\7.jpg") '压力气体
        p(8) = Image.FromFile("C:\GHS\8.jpg") '氧化
        p(9) = Image.FromFile("C:\GHS\9.jpg") '易燃




        sql = "SELECT   RT_SYS_CHEM_MAIN.name AS 化料名称, RT_SYS_CHEM_MAIN.DEP AS 使用部门, 
                RT_SYS_CHEM_MAIN.CLASS1 AS 化工分类, RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类, 
                RT_CHEM_MSDS.SUPPLIER AS 供应商, RT_CHEM_MSDS.D1, RT_CHEM_MSDS.D2, RT_CHEM_MSDS.D3, 
                RT_CHEM_MSDS.D4, RT_CHEM_MSDS.D5, RT_CHEM_MSDS.D6, RT_CHEM_MSDS.D7, RT_CHEM_MSDS.D8, 
                RT_CHEM_MSDS.D9, RT_SYS_CHEM_MAIN.stock1, RT_SYS_CHEM_MAIN.stock2, RT_SYS_CHEM_MAIN.stock3, 
                RT_CHEM_MSDS.SUPPLIER
FROM      RT_SYS_CHEM_MAIN LEFT OUTER JOIN
                RT_CHEM_MSDS ON RT_SYS_CHEM_MAIN.name = RT_CHEM_MSDS.SYS_NAME
WHERE   (RT_SYS_CHEM_MAIN.stock1 + RT_SYS_CHEM_MAIN.stock2 + RT_SYS_CHEM_MAIN.stock3 <> 0)"





        sql = "SELECT  supplier AS 供应商, p AS 联络人, sys_name AS 化料名称, type1 AS 化工分类, dep AS 使用部门, d1, d2, d3, d4, d5, d6, d7, d8, d9
FROM     HT_Chem_Main"






        Dim DT As New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(DT)






        Dim z1 As Integer = 0

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


            Dim MY_CHEM As String

            MY_CHEM = DT.Rows(x).Item("化料名称").ToString
            MY_CHEM = MY_CHEM.Replace(" ", "_")
            ' MY_CHEM = MY_CHEM.Replace("-", "_")
            MY_CHEM = MY_CHEM.Replace("/", "-")


            ' xlSheet = xlBook.Worksheets.Add
            'xlSheet.Name = MY_CHEM
            'xlSheet.Activate()
            '    xlSheet.Hyperlinks.Add(Anchor:=xlSheet.Cells(1, 1), Address:="", SubAddress:="总表!A1", TextToDisplay:="总表")


            xlSheet.Cells(x * 16 + 1, 1) = DT.Rows(x).Item("化料名称")
            xlSheet.Cells(x * 16 + 1, 1).font.bold = True
            xlSheet.Cells(x * 16 + 1, 1).font.size = 24

            xlApp.Range(xlSheet.Cells(x * 16 + 1, 1), xlSheet.Cells(x * 16 + 1, 8)).Select()
            '  xlApp.Range("E2").Activate()
            xlApp.Selection.Merge
            xlApp.Selection.HorizontalAlignment = xlCenter
            With xlApp.Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With


            xlSheet.Cells(x * 16 + 7, 1) = "化  工  分  类 :"
            xlSheet.Cells(x * 16 + 7, 1).font.bold = True
            xlSheet.Cells(x * 16 + 7, 1).font.size = 12
            xlSheet.Cells(x * 16 + 7, 3) = DT.Rows(x).Item("化工分类")
            xlSheet.Cells(x * 16 + 7, 3).font.bold = True
            xlSheet.Cells(x * 16 + 7, 3).font.size = 12
            xlSheet.Cells(x * 16 + 4, 1) = "供   应   商   :"
            xlSheet.Cells(x * 16 + 4, 1).font.bold = True
            xlSheet.Cells(x * 16 + 4, 1).font.size = 12
            xlSheet.Cells(x * 16 + 4, 3) = DT.Rows(x).Item("供应商")
            xlSheet.Cells(x * 16 + 4, 3).font.bold = True
            xlSheet.Cells(x * 16 + 4, 3).font.size = 12
            xlSheet.Cells(x * 16 + 12, 1) = "验收日期／批号 :"
            xlSheet.Cells(x * 16 + 12, 1).font.bold = True
            xlSheet.Cells(x * 16 + 12, 1).font.size = 12

            xlSheet.Cells(x * 16 + 15, 1) = "有 效  期  限 :  二  年"
            xlSheet.Cells(x * 16 + 15, 1).font.bold = True
            xlSheet.Cells(x * 16 + 15, 1).font.size = 12



            xlApp.Range(xlSheet.Cells(x * 16 + 6, 5), xlSheet.Cells(x * 16 + 15, 8)).Select()
            '  xlApp.Range("E2").Activate()
            '
            ' xlApp.Selection.Merge
            ' xlApp.Selection.HorizontalAlignment = xlCenter
            With xlApp.Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With




            xlApp.Range(xlSheet.Cells(x * 16 + 4, 8), xlSheet.Cells(x * 16 + 5, 8)).Select()
            '  xlApp.Range("E2").Activate()
            '
            xlApp.Selection.Merge
            xlApp.Selection.FormulaR1C1 = "危险"
            xlApp.Selection.HorizontalAlignment = xlCenter

            '-----边框
            With xlApp.Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With xlApp.Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With



            '------字体
            With xlApp.Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorLight1
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            With xlApp.Selection.Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
            With xlApp.Selection.Font
                .Size = 18
                .ThemeColor = xlThemeColorDark1
                .ThemeFont = xlThemeFontMinor
            End With




            Dim zzz As Integer = 0
            For z As Integer = 1 To 9

                If DT.Rows(x).Item("d" & z) <> "" Then

                    System.Windows.Forms.Clipboard.SetDataObject(p(z), True)  '复制到剪贴板

                    If zzz < 4 Then

                        Dim range1 As Excel.Range = xlSheet.Range(xlApp.Cells(x * 16 + 6, zzz Mod 4 + 5), xlApp.Cells(x * 16 + 6, zzz Mod 4 + 5))  '粘贴图片的位置
                        xlSheet.Paste(range1, p(z))

                    Else

                        Dim range1 As Excel.Range = xlSheet.Range(xlApp.Cells(x * 16 + 11, zzz Mod 4 + 5), xlApp.Cells(x * 16 + 11, zzz Mod 4 + 5))  '粘贴图片的位置
                        xlSheet.Paste(range1, p(z))

                    End If



                    Dim pic1 As String
                    pic1 = "picture " & z1 + 1

                    xlApp.ActiveSheet.Shapes.Range(pic1).Select
                    xlApp.Selection.ShapeRange.ScaleWidth(0.025, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft)
                    xlApp.Selection.ShapeRange.Incrementtop(5)
                    xlApp.Selection.ShapeRange.Incrementleft(5)


                    zzz = zzz + 1
                    z1 = z1 + 1
                End If


            Next






            Dim r As String
            r = "A" & (x + 1) * 16 + 1

            xlApp.ActiveSheet.HPageBreaks.Add(xlApp.Range(r))



        Next

        ''xlApp.ActiveSheet.PageSetup.PrintArea = "$A$1:$E$660"

        'xlApp.ActiveSheet.PageSetup.PrintArea = "$a$1:$E$" & y * 13
        '' xlSheet.Columns("C:C").ColumnWidth = 10.75





        xlApp.ActiveWindow.View = Microsoft.Office.Interop.Excel.XlWindowView.xlPageBreakPreview






        'DT2.DefaultView.
        '= "项次 "

        MsgBox("输出完成")


    End Sub

    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.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 file_name As String

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

        '    FILE_NAME = OpenFileDialog1.FileName
        '    '  FILE_NAME1 = OpenFileDialog1.SafeFileName
        'Else
        '    Exit Sub

        'End If


        file_name = "c:\GHS\化工吊牌樣版1228.xlsx"


        xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        xlApp.DisplayAlerts = False

        xlBook = xlApp.Workbooks.Open(file_name,, True)

        xlBook.Activate()
        xlApp.Visible = True


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


        Dim xx As Integer = 0
        Dim y As Integer = 0
        'Dim P1 As Image
        'Dim P2 As Image
        'Dim P3 As Image
        'Dim P4 As Image
        'Dim P5 As Image
        'Dim P6 As Image
        'Dim P7 As Image
        'Dim P8 As Image
        'Dim P9 As Image





        '  Dim FILE_NAME As String
        ' Dim FILE_NAME1 As String


        Dim sPath As String

        sPath = "c:\GHS\"

        ' Dim p(10) As Image
        Dim p(10) As String



        'p(1) = Image.FromFile("f:\化料\GHS\1.jpg")
        'p(2) = Image.FromFile("f:\化料\GHS\2.jpg")
        'p(3) = Image.FromFile("f:\化料\GHS\3.jpg")
        'p(4) = Image.FromFile("f:\化料\GHS\4.jpg")
        'p(5) = Image.FromFile("f:\化料\GHS\5.jpg")
        'p(6) = Image.FromFile("f:\化料\GHS\6.jpg")
        'p(7) = Image.FromFile("f:\化料\GHS\7.jpg")
        'p(8) = Image.FromFile("f:\化料\GHS\8.jpg")
        'p(9) = Image.FromFile("f:\化料\GHS\9.jpg")

        p(1) = "c:\GHS\1.jpg"
        p(2) = "c:\GHS\2.jpg"
        p(3) = "c:\GHS\3.jpg"
        p(4) = "c:\GHS\4.jpg"
        p(5) = "c:\GHS\5.jpg"
        p(6) = "c:\GHS\6.jpg"
        p(7) = "c:\GHS\7.jpg"
        p(8) = "c:\GHS\8.jpg"
        p(9) = "c:\GHS\9.jpg"


        sql = "update rt_chem_msds set d1=0 where d1 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_chem_msds set d2=0 where d2 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_chem_msds set d3=0 where d3 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_chem_msds set d4=0 where d4 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_chem_msds set d5=0 where d5 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

        sql = "update rt_chem_msds set d6=0 where d6 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_chem_msds set d7=0 where d7 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_chem_msds set d8=0 where d8 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        sql = "update rt_chem_msds set d9=0 where d9 is null"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()










        sql = "SELECT   RT_SYS_CHEM_MAIN.name AS 化料名称, RT_SYS_CHEM_MAIN.DEP AS 使用部门, RT_SYS_CHEM_MAIN.CLASS1 AS 化工分类, RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类,                 RT_CHEM_MSDS.SUPPLIER AS 供应商, RT_CHEM_MSDS.D1, RT_CHEM_MSDS.D2, RT_CHEM_MSDS.D3,                 RT_CHEM_MSDS.D4, RT_CHEM_MSDS.D5, RT_CHEM_MSDS.D6, RT_CHEM_MSDS.D7, RT_CHEM_MSDS.D8,                 RT_CHEM_MSDS.D9, RT_SYS_CHEM_MAIN.stock1, RT_SYS_CHEM_MAIN.stock2, RT_SYS_CHEM_MAIN.stock3, RT_SYS_CHEM_MAIN.stock4, RT_CHEM_MSDS.SUPPLIER,                 RT_CHEM_MSDS.D1 + RT_CHEM_MSDS.D2 + RT_CHEM_MSDS.D3 + RT_CHEM_MSDS.D4 + RT_CHEM_MSDS.D5 + RT_CHEM_MSDS.D6                 + RT_CHEM_MSDS.D7 + RT_CHEM_MSDS.D8 + RT_CHEM_MSDS.D9 AS d, RT_CHEM_MSDS.contact,                 RT_CHEM_MSDS.phone FROM      RT_SYS_CHEM_MAIN LEFT OUTER JOIN     RT_CHEM_MSDS ON RT_SYS_CHEM_MAIN.name = RT_CHEM_MSDS.SYS_NAME WHERE (RT_SYS_CHEM_MAIN.stock1 + RT_SYS_CHEM_MAIN.stock2 + RT_SYS_CHEM_MAIN.stock3+RT_SYS_CHEM_MAIN.stock4 <> 0) and  (RT_CHEM_MSDS.D2 IS NOT NULL) and (RT_SYS_CHEM_MAIN.CLASS1 not like N'%染料%') and (RT_SYS_CHEM_MAIN.CLASS1 not like N'%涂料%') and (RT_SYS_CHEM_MAIN.DEP<>N'喷台')  ORDER BY d DESC"

        sql = "SELECT   RT_SYS_CHEM_MAIN.name AS 化料名称, RT_SYS_CHEM_MAIN.DEP AS 使用部门, RT_SYS_CHEM_MAIN.CLASS1 AS 化工分类, RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类,                 RT_CHEM_MSDS.SUPPLIER AS 供应商, RT_CHEM_MSDS.D1, RT_CHEM_MSDS.D2, RT_CHEM_MSDS.D3,                 RT_CHEM_MSDS.D4, RT_CHEM_MSDS.D5, RT_CHEM_MSDS.D6, RT_CHEM_MSDS.D7, RT_CHEM_MSDS.D8,                 RT_CHEM_MSDS.D9, RT_SYS_CHEM_MAIN.stock1, RT_SYS_CHEM_MAIN.stock2, RT_SYS_CHEM_MAIN.stock3, RT_SYS_CHEM_MAIN.stock4, RT_CHEM_MSDS.SUPPLIER,                 RT_CHEM_MSDS.D1 + RT_CHEM_MSDS.D2 + RT_CHEM_MSDS.D3 + RT_CHEM_MSDS.D4 + RT_CHEM_MSDS.D5 + RT_CHEM_MSDS.D6                 + RT_CHEM_MSDS.D7 + RT_CHEM_MSDS.D8 + RT_CHEM_MSDS.D9 AS d, RT_CHEM_MSDS.contact,                 RT_CHEM_MSDS.phone FROM      RT_SYS_CHEM_MAIN LEFT OUTER JOIN     RT_CHEM_MSDS ON RT_SYS_CHEM_MAIN.name = RT_CHEM_MSDS.SYS_NAME WHERE (RT_SYS_CHEM_MAIN.stock1 + RT_SYS_CHEM_MAIN.stock2 + RT_SYS_CHEM_MAIN.stock3+RT_SYS_CHEM_MAIN.stock4 <> 0) and  (RT_CHEM_MSDS.D2 IS NOT NULL) and (RT_SYS_CHEM_MAIN.CLASS1 not like N'%染料%')   ORDER BY d DESC"

        sql = "SELECT   RT_SYS_CHEM_MAIN.name AS 化料名称, RT_SYS_CHEM_MAIN.DEP AS 使用部门, RT_SYS_CHEM_MAIN.CLASS1 AS 化工分类, RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类,                 RT_CHEM_MSDS.SUPPLIER AS 供应商, RT_CHEM_MSDS.D1, RT_CHEM_MSDS.D2, RT_CHEM_MSDS.D3,                 RT_CHEM_MSDS.D4, RT_CHEM_MSDS.D5, RT_CHEM_MSDS.D6, RT_CHEM_MSDS.D7, RT_CHEM_MSDS.D8,                 RT_CHEM_MSDS.D9, RT_SYS_CHEM_MAIN.stock1, RT_SYS_CHEM_MAIN.stock2, RT_SYS_CHEM_MAIN.stock3, RT_SYS_CHEM_MAIN.stock4, RT_CHEM_MSDS.SUPPLIER,                 RT_CHEM_MSDS.D1 + RT_CHEM_MSDS.D2 + RT_CHEM_MSDS.D3 + RT_CHEM_MSDS.D4 + RT_CHEM_MSDS.D5 + RT_CHEM_MSDS.D6                 + RT_CHEM_MSDS.D7 + RT_CHEM_MSDS.D8 + RT_CHEM_MSDS.D9 AS d, RT_CHEM_MSDS.contact,                 RT_CHEM_MSDS.phone FROM      RT_SYS_CHEM_MAIN LEFT OUTER JOIN     RT_CHEM_MSDS ON RT_SYS_CHEM_MAIN.name = RT_CHEM_MSDS.SYS_NAME  where (RT_CHEM_MSDS.D2 IS NOT NULL)  ORDER BY d DESC"

        sql = "SELECT   RT_SYS_CHEM_MAIN.name AS 化料名称, RT_SYS_CHEM_MAIN.DEP AS 使用部门, RT_SYS_CHEM_MAIN.CLASS1 AS 化工分类, RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类,                 RT_CHEM_MSDS.SUPPLIER AS 供应商, RT_CHEM_MSDS.D1, RT_CHEM_MSDS.D2, RT_CHEM_MSDS.D3,                 RT_CHEM_MSDS.D4, RT_CHEM_MSDS.D5, RT_CHEM_MSDS.D6, RT_CHEM_MSDS.D7, RT_CHEM_MSDS.D8,                 RT_CHEM_MSDS.D9, RT_SYS_CHEM_MAIN.stock1, RT_SYS_CHEM_MAIN.stock2, RT_SYS_CHEM_MAIN.stock3, RT_SYS_CHEM_MAIN.stock4, RT_CHEM_MSDS.SUPPLIER,                 RT_CHEM_MSDS.D1 + RT_CHEM_MSDS.D2 + RT_CHEM_MSDS.D3 + RT_CHEM_MSDS.D4 + RT_CHEM_MSDS.D5 + RT_CHEM_MSDS.D6                 + RT_CHEM_MSDS.D7 + RT_CHEM_MSDS.D8 + RT_CHEM_MSDS.D9 AS d, RT_CHEM_MSDS.contact,                 RT_CHEM_MSDS.phone FROM      RT_SYS_CHEM_MAIN LEFT OUTER JOIN     RT_CHEM_MSDS ON RT_SYS_CHEM_MAIN.name = RT_CHEM_MSDS.SYS_NAME  where (RT_CHEM_MSDS.D2 IS NOT NULL) and (RT_SYS_CHEM_MAIN.name like '%8003%' or RT_SYS_CHEM_MAIN.name like '%zat%' or RT_SYS_CHEM_MAIN.name like '%CS%' or RT_SYS_CHEM_MAIN.name like '%602%' or RT_SYS_CHEM_MAIN.name like '%152%' or RT_SYS_CHEM_MAIN.name like '%ofa%' or RT_SYS_CHEM_MAIN.name like '%GSA%') ORDER BY d DESC"

        sql = "SELECT  supplier AS 供应商, p AS contact,d,tel as phone, sys_name AS 化料名称, type1 AS 化工分类, dep AS 使用部门, d1, d2, d3, d4, d5, d6, d7, d8, d9
FROM     HT_Chem_Main"


        Dim DT As New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(DT)


        xlSheet.Rows("1:19").Copy()

        For yy As Integer = 0 To DT.Rows.Count - 1

            xlApp.Range("a" & 1 + 19 * (yy + 1)).Select()
            xlSheet.Paste()

        Next yy


        Dim range1 As Excel.Range
        Dim pic1 As String

        Dim z1 As Integer = 0

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

            Dim MY_CHEM As String

            MY_CHEM = DT.Rows(x).Item("化料名称").ToString

            xlSheet.Cells(x * 19 + 3, 3) = DT.Rows(x).Item("化料名称")
            xlSheet.Cells(x * 19 + 5, 5) = DT.Rows(x).Item("化工分类")
            xlSheet.Cells(x * 19 + 6, 5) = DT.Rows(x).Item("供应商")
            xlSheet.Cells(x * 19 + 7, 5) = DT.Rows(x).Item("contact")
            xlSheet.Cells(x * 19 + 7, 8) = DT.Rows(x).Item("phone")

            Dim zzz As Integer = 0
            For z As Integer = 1 To 9

                If DT.Rows(x).Item("d" & z).ToString <> "" Then

                    Dim my_y As Integer = 0

                    If zzz Mod 3 = 0 Then
                        my_y = 7
                    ElseIf zzz Mod 3 = 1 Then
                        my_y = 15
                    Else

                        my_y = 23




                    End If
                    If DT.Rows(x).Item("d") < 4 Then     '--------3個圖標

                        range1 = xlSheet.Range(xlApp.Cells(x * 19 + 3, zzz * 3 + 15), xlApp.Cells(x * 19 + 3, zzz * 3 + 15))  '粘贴图片的位置
                        'lSheet.Paste(range1, p(z))

                        range1.Select()

                        xlApp.ActiveSheet.Pictures.Insert(p(z))

                        pic1 = "圖片 " & (z1 + 1) * 2

                        xlApp.ActiveSheet.Shapes.Range(pic1).Select
                        xlApp.Selection.ShapeRange.ScaleWidth(0.26, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft)
                        xlApp.Selection.ShapeRange.Incrementtop(45)
                        xlApp.Selection.ShapeRange.Incrementleft(35)
                        Select Case z
                            Case 1
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "急性毒性(经口/经皮/吸入)"
                            Case 2
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "皮肤刺激/过敏/眼刺激"
                            Case 3
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "皮肤腐蚀/眼损伤"
                            Case 4
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "爆炸物/过氧化物"
                            Case 5
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "吸入危险/呼吸过敏"
                            Case 6
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "危害水生环境"
                            Case 7
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "高压气体"
                            Case 8
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "氧化性气体/液体/固体"
                            Case 9
                                xlSheet.Cells(x * 19 + 8, 3 + zzz * 7) = "易燃气体/液体/固体"


                        End Select

                        zzz = zzz + 1
                        z1 = z1 + 1
                    Else                                    '---------6個圖標

                        If zzz < 3 Then

                            range1 = xlSheet.Range(xlApp.Cells(x * 19 + 3, zzz * 3 + 16), xlApp.Cells(x * 19 + 3, zzz * 3 + 16))  '粘贴图片的位置

                            range1.Select()

                            xlApp.ActiveSheet.Pictures.Insert(p(z))

                            ' xlSheet.Paste(range1, b)

                            pic1 = "圖片 " & (z1 + 1) * 2

                            xlApp.ActiveSheet.Shapes.Range(pic1).Select
                            xlApp.Selection.ShapeRange.ScaleWidth(0.23, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft)
                            xlApp.Selection.ShapeRange.Incrementtop(-5)
                            xlApp.Selection.ShapeRange.Incrementleft(5)

                            Select Case z
                                Case 1
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "急性毒性(经口/经皮/吸入)"
                                Case 2
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "皮肤刺激/过敏/眼刺激"
                                Case 3
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "皮肤腐蚀/眼损伤"
                                Case 4
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "爆炸物/过氧化物"
                                Case 5
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "吸入危险/呼吸过敏"
                                Case 6
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "危害水生环境"
                                Case 7
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "高压气体"
                                Case 8
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "氧化性气体/液体/固体"
                                Case 9
                                    xlSheet.Cells(x * 19 + 8, 3 + (zzz Mod 3) * 7) = "易燃气体/液体/固体"


                            End Select

                            zzz = zzz + 1
                            z1 = z1 + 1
                        Else

                            range1 = xlSheet.Range(xlApp.Cells(x * 19 + 5, (zzz Mod 3) * 3 + 16), xlApp.Cells(x * 19 + 5, (zzz Mod 3) * 3 + 16))  '粘贴图片的位置
                            'lSheet.Paste(range1, p(z))
                            range1.Select()

                            xlApp.ActiveSheet.Pictures.Insert(p(z))


                            pic1 = "picture " & (z1 + 1) * 2

                            xlApp.ActiveSheet.Shapes.Range(pic1).Select
                            xlApp.Selection.ShapeRange.ScaleWidth(0.23, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft)
                            xlApp.Selection.ShapeRange.Incrementtop(30)
                            xlApp.Selection.ShapeRange.Incrementleft(5)

                            Select Case z
                                Case 1
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "急性毒性(经口/经皮/吸入)"
                                Case 2
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "皮肤刺激/过敏/眼刺激"
                                Case 3
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "皮肤腐蚀/眼损伤"
                                Case 4
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "爆炸物/过氧化物"
                                Case 5
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "吸入危险/呼吸过敏"
                                Case 6
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "危害水生环境"
                                Case 7
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "高压气体"
                                Case 8
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "氧化性气体/液体/固体"
                                Case 9
                                    xlSheet.Cells(x * 19 + 9, 3 + (zzz Mod 3) * 7) = "易燃气体/液体/固体"


                            End Select

                            zzz = zzz + 1
                            z1 = z1 + 1

                        End If



                    End If



                    'If zzz < 4 Then

                    '    Dim range1 As Excel.Range = xlSheet.Range(xlApp.Cells(x * 19 + 3, zzz Mod 3 + 16), xlApp.Cells(x * 19 + 3, zzz Mod 3 + 16))  '粘贴图片的位置
                    '    xlSheet.Paste(range1, p(z))

                    'Else

                    '    Dim range1 As Excel.Range = xlSheet.Range(xlApp.Cells(x * 19 + 5, zzz Mod 3 + 16), xlApp.Cells(x * 19 + 5, zzz Mod 3 + 16))  '粘贴图片的位置
                    '    xlSheet.Paste(range1, p(z))

                    'End If





                End If


            Next






            Dim r As String
            r = "A" & (x + 1) * 19 + 1

            xlApp.ActiveSheet.HPageBreaks.Add(xlApp.Range(r))






        Next

        ''xlApp.ActiveSheet.PageSetup.PrintArea = "$A$1:$E$660"

        'xlApp.ActiveSheet.PageSetup.PrintArea = "$a$1:$E$" & y * 13
        '' xlSheet.Columns("C:C").ColumnWidth = 10.75





        xlApp.ActiveWindow.View = Microsoft.Office.Interop.Excel.XlWindowView.xlPageBreakPreview






        'DT2.DefaultView.Sort = "项次 "

        MsgBox("输出完成")




    End Sub

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






















    End Sub


    Private Sub dgv1_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgv1.CellMouseClick


        'Dim DT3 As New DataTable        '-------保税入库

        'sql = "SELECT  DATE AS 日期, QTY AS 數量, PRICE AS 單價  FROM  RT_CHEM_S_IN_PRICE WHERE   (matl_NAME = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "') and dep=N'保税仓-R' ORDER BY DATE DESC"

        'cmd.CommandText = sql

        'da.SelectCommand = cmd
        'da.Fill(DT3)
        'dgv4.DataSource = DT3
        'dgv4.Columns(0).Width = 100
        'dgv4.Columns(1).Width = 80


        'Dim DT4 As New DataTable  '----调拨出库

        'sql = "SELECT  DATE AS 日期, weight AS 數量 FROM RT_sys_CHEM_out_db WHERE   (NAME = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "') ORDER BY DATE DESC"

        'cmd.CommandText = sql

        'da.SelectCommand = cmd

        'da.Fill(DT4)
        'dgv5.DataSource = DT4

        'dgv5.Columns(0).Width = 120
        'dgv5.Columns(1).Width = 80


        'Dim dt_tech As New DataTable  '-----技术员领料
        'sql = "Select Year(Date) As 年, MONTH(Date) As 月, CAST(SUM(WEIGHT) As NUMERIC(10, 0)) As 用量kg,p as 技术员, COUNT(*) As 笔数, CAST(SUM(WEIGHT) / SUM(ORDER_A) * 100 As NUMERIC(10, 1)) As 百分比 From RT_SYS_CHEM_OUT Where (year(date)='2023' or year(date)='2022' or year(date)='2021' or year(date)='2024') and Name  = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "' Group By YEAR(Date), MONTH(Date), Name,p Order By 年 desc, 月 desc"
        'cmd.CommandText = sql

        'da.SelectCommand = cmd
        'da.Fill(dt_tech)
        'dgv_tech.DataSource = dt_tech
        'dgv_tech.Columns(0).Width = 80
        'dgv_tech.Columns(1).Width = 80

        'Dim DT_STOCK As New DataTable   '----month1库存

        'sql = "SELECT date as 日期,SUM(STOCK1) AS 库存1,SUM(STOCK2) AS 库存2, SUM(STOCK3) AS 库存3, SUM(STOCK1+STOCK2+STOCK3) AS 合计 FROM RT_CHEM_stock_month1 Where Name  = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "' GROUP BY NAME,date"

        'cmd.CommandText = sql

        'da.SelectCommand = cmd
        'da.Fill(DT_STOCK)
        'DGV_STOCK.DataSource = DT_STOCK
        'DGV_STOCK.Columns(0).Width = 120
        'DGV_STOCK.Columns(1).Width = 80

        'Dim DT_CONTAINER As New DataTable   '-------未到港
        'sql = "SELECT date as 日期,CONTRACT_NO AS 合约号, QTY AS 数量,PRICE AS 单价, ORIGINAL AS 产地,ETD AS 开船日,ETA AS 到港日 FROM RT_CHEM_CONTAINER Where Name  = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "' AND (RECEIVED='FALSE' OR RECEIVED IS NULL )"

        'cmd.CommandText = sql

        'da.SelectCommand = cmd
        'da.Fill(DT_CONTAINER)
        'DGV_CONTAINER.DataSource = DT_CONTAINER
        'DGV_CONTAINER.Columns(0).Width = 120
        'DGV_CONTAINER.Columns(1).Width = 80


        'Dim begin_stock As Double = 0
        'Dim used As Double = 0
        'Dim f_in As Double = 0
        'Dim s_in As Double = 0
        'Dim db_out As Double = 0
        'Dim total As Double = 0


        'Dim MY_MONTH As Integer = 0
        'Dim my_year As Integer = 0

        'MY_MONTH = Month(Today) - 1
        'my_year = Year(Now)
        'If Month(Today) = 1 Then
        '    MY_MONTH = 12
        '    my_year = Year(Now) - 1
        'End If




        'sql = "select accounted from rt_chem_accounting where year(period)='" & my_year & "' and month(period)='" & MY_MONTH & "'"
        'cmd.CommandText = sql
        'Dim accounted As Boolean = cmd.ExecuteScalar


        'If accounted = False Then  '------未結帳

        '    MY_MONTH = Month(Today)
        '    my_year = Year(Today)


        '    Dim my_month_begin As Integer = 0
        '    Dim my_year_begin As Integer = 0

        '    If Month(Today) = 1 Then
        '        my_month_begin = 12
        '        my_year_begin = Year(Now) - 1
        '    Else
        '        my_month_begin = Month(Today) - 1
        '        my_year_begin = Year(Now)

        '    End If




        '    '----month1期初库存

        '    sql = "SELECT SUM(STOCK1+STOCK2+STOCK3) AS 合计 FROM RT_CHEM_stock_month2 Where  year(date)='" & my_year_begin & "' and month(date)='" & my_month_begin & "' and Name  = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "' "
        '    cmd.CommandText = sql

        '    If IsDBNull(cmd.ExecuteScalar) Then

        '    Else
        '        begin_stock = cmd.ExecuteScalar
        '    End If

        '    '--------领料
        '    sql = "Select SUM(WEIGHT) From RT_SYS_CHEM_OUT Where year(date)='" & my_year & "' and month(date)='" & MY_MONTH & "' and Name = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "' "
        '    cmd.CommandText = sql
        '    If IsDBNull(cmd.ExecuteScalar) Then

        '    Else
        '        used = cmd.ExecuteScalar
        '    End If

        '    '----外购入库

        '    sql = "SELECT  sum(QTY) AS 數量 FROM RT_CHEM_IN_PRICE WHERE  year(date)='" & my_year & "' and month(date)='" & MY_MONTH & "' and (NAME LIKE N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%') and (dep is null or dep<>N'保税仓-R')"
        '    cmd.CommandText = sql
        '    If IsDBNull(cmd.ExecuteScalar) Then

        '    Else
        '        f_in = cmd.ExecuteScalar
        '    End If

        '    '-------保税入库

        '    sql = "SELECT  sum(QTY) AS 數量 FROM  RT_CHEM_s_IN_PRICE WHERE  year(date)='" & my_year & "' and month(date)='" & MY_MONTH & "' and (matl_NAME LIKE N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%') and dep=N'保税仓-R' "

        '    cmd.CommandText = sql
        '    If IsDBNull(cmd.ExecuteScalar) Then

        '    Else
        '        s_in = cmd.ExecuteScalar
        '    End If



        '    total = Math.Round((begin_stock + f_in + s_in - used), 0)


        '    TextBox2.Text = total


        'Else                              '------已结帐
        '    MY_MONTH = Month(Today)
        '    my_year = Year(Today)


        '    Dim my_month_begin As Integer = 0
        '    Dim my_year_begin As Integer = 0

        '    If Month(Today) = 1 Then
        '        my_month_begin = 12
        '        my_year_begin = Year(Now) - 1
        '    Else
        '        my_month_begin = MY_MONTH - 1
        '        my_year_begin = Year(Now)

        '    End If


        '    '----month1库存

        '    sql = "SELECT SUM(STOCK1+STOCK2+STOCK3) AS 合计 FROM RT_CHEM_stock_month2 Where  year(date)='" & my_year_begin & "' and month(date)='" & my_month_begin & "' and Name  = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "' "
        '    cmd.CommandText = sql

        '    If IsDBNull(cmd.ExecuteScalar) Then

        '    Else
        '        begin_stock = cmd.ExecuteScalar
        '    End If

        '    '--------领料
        '    sql = "Select SUM(WEIGHT) From RT_SYS_CHEM_OUT Where year(date)='" & my_year & "' and month(date)='" & MY_MONTH & "' and Name Like N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%' "
        '    cmd.CommandText = sql
        '    If IsDBNull(cmd.ExecuteScalar) Then

        '    Else
        '        used = cmd.ExecuteScalar
        '    End If

        '    '----外购入库

        '    sql = "SELECT  sum(QTY) AS 數量 FROM RT_CHEM_IN_PRICE WHERE  year(date)='" & my_year & "' and month(date)='" & MY_MONTH & "' and (NAME LIKE N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%') and (dep is null or dep<>N'保税仓-R')"
        '    cmd.CommandText = sql
        '    If IsDBNull(cmd.ExecuteScalar) Then

        '    Else
        '        f_in = cmd.ExecuteScalar
        '    End If

        '    '-------保税入库

        '    sql = "SELECT  sum(QTY) AS 數量 FROM  RT_CHEM_s_IN_PRICE WHERE  year(date)='" & my_year & "' and month(date)='" & MY_MONTH & "' and (matl_NAME LIKE N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%') and dep=N'保税仓-R' "

        '    cmd.CommandText = sql
        '    If IsDBNull(cmd.ExecuteScalar) Then

        '    Else
        '        s_in = cmd.ExecuteScalar
        '    End If



        '    total = Math.Round((begin_stock + f_in + s_in - used), 0)


        '    TextBox2.Text = total   '即時庫存

        'End If





        ''----调拨出库

        'sql = "SELECT  DATE AS 日期, weight AS 數量 FROM RT_sys_CHEM_out_db WHERE   (NAME LIKE N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%') ORDER BY DATE DESC"

        'cmd.CommandText = sql

        ''-------保税入库

        'sql = "SELECT  DATE AS 日期, QTY AS 數量, PRICE AS 單價  FROM  RT_CHEM_S_IN_PRICE WHERE   (MATL_NAME LIKE N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%') and dep=N'保税仓-R' ORDER BY DATE DESC"

        'cmd.CommandText = sql


        'sql = "select top(1) date from rt_sys_chem_out where Name Like N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%' order by date desc"
        'cmd.CommandText = sql

        'TextBox3.Text = cmd.ExecuteScalar

    End Sub

    Private Sub dgv1_KeyUp(sender As Object, e As KeyEventArgs) Handles dgv1.KeyUp

    End Sub

    Private Sub dgv1_SelectionChanged(sender As Object, e As EventArgs) Handles dgv1.SelectionChanged



        Dim s As String = dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value
        '  s = s.Remove(s.Length - 1, 1)

        sql = "Select supplier from ht_chem_main Where sys_Name = N'" & s & "' "
        cmd.CommandText = sql
        Dim supplier As String = cmd.ExecuteScalar.ToString
        TextBox5.Text = supplier

        sql = "Select mfg from ht_chem_main Where sys_Name = N'" & s & "' "
        cmd.CommandText = sql
        Dim mfg As String = cmd.ExecuteScalar.ToString
        TextBox4.Text = mfg

        sql = "Select type1 from ht_chem_main Where sys_Name = N'" & s & "' "
        cmd.CommandText = sql
        Dim type1 As String = cmd.ExecuteScalar.ToString
        TextBox7.Text = type1

        sql = "Select dep from ht_chem_main Where sys_Name = N'" & s & "' "
        cmd.CommandText = sql
        Dim dep As String = cmd.ExecuteScalar.ToString
        TextBox6.Text = dep


        TextBox3.Text = ""

        Dim DT As New DataTable     '--------领料
        sql = "Select Year(Date) As 年, MONTH(Date) As 月,  CAST(SUM(qty) As NUMERIC(10, 0)) As 用量,COUNT(*) As 笔数,name From ht_CHEMOUT Where Name = N'" & s & "' Group By YEAR(Date), MONTH(Date), Name Order By  年 desc, 月 desc"
        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(DT)
        dgv2.DataSource = DT

        dgv2.Columns(0).Width = 70
        dgv2.Columns(1).Width = 70
        dgv2.Columns(2).Width = 80
        dgv2.Columns(3).Width = 80
        '  dgv2.Columns(4).Width = 80




        Dim DT2 As New DataTable     '----外购入库

        'sql = "SELECT  DATE AS 日期, QTY AS 數量, PRICE AS 單價, CAST(PRICE / 7 AS NUMERIC(10, 2)) AS 折美金 FROM RT_CHEM_s_IN_PRICE WHERE   (matl_NAME LIKE N'%" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "%')  and dep<>N'保税仓-R' ORDER BY DATE DESC"
        sql = "SELECT  DATE AS 日期, QTY AS 數量, supplier as 供应商,cast(PRICE as numeric(10,2)) AS 單價, CAST(PRICE / 7 AS NUMERIC(10, 2)) AS 折美金 FROM ht_CHEM_IN where (NAME = N'" & dgv1.Rows(dgv1.CurrentRow.Index).Cells("品名").Value & "')  ORDER BY DATE DESC"

        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(DT2)
        dgv3.DataSource = DT2

        dgv3.Columns(0).Width = 95
        dgv3.Columns(1).Width = 65
        dgv3.Columns(2).Width = 100
        dgv3.Columns(3).Width = 60
        dgv3.Columns(4).Width = 60

        dgv3.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing

    End Sub
End Class