HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/02、皮料管理/Frm_Retan.vb

Imports System.Data.SqlClient
Imports System.Windows.Forms.VisualStyles.VisualStyleElement

Public Class Frm_Retan
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Dim sql As String
    Dim conn As New SqlConnection
    Dim PTCache As Microsoft.Office.Interop.Excel.PivotCache
    Dim PT As Microsoft.Office.Interop.Excel.PivotTable

    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click

        Dim sql1 As String
        Dim sql2 As String
        Dim sql3 As String
        Dim sql4 As String

        sql1 = "SELECT  CARD as 卡号, card2 as 卡号1, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源,  DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本  FROM      (SELECT  TOP (100) PERCENT p.CARD, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10)                                       + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) 
                                      = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, 
                                      AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL 
                                      THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL 
                                      THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL 
                                      THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL 
                                      THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, 
                                      rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL 
                                      THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL 
                                      THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, 
                                      CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) 
                                      = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) 
                                      = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) 
                                      = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, 
                                      CASE WHEN rt_shaving_out.card1 IS NULL AND 
                                      p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND 
                                      p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, 
                                      CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'
                                       WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR
                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%') 
                                      THEN N'巴西 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层'
                                       WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN
                                       p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source
                                       LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%'
                                       THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN
                                       N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'
                                       WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN
                                       p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'
                                       END AS 皮源1, 
                                      CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source
                                       LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR
                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%') 
                                      THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source
                                       LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN
                                       N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE
                                       N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ'
                                       WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%'
                                       THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source
                                       LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, 
                                      rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) 
                                      / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, 
                                      RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, 
                                      RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL 
                                      THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem
                   FROM       RT_SYS_CHEM_OUT INNER JOIN
                                      rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN
                                          (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, 
                                                              W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, 
                                                              gg, hh, Dep
                                           FROM       RT_RETAN_RECORD_D
                                           WHERE "

        sql2 = "(date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "')"

        sql3 = ") AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN
                                      rt_wetblue_name RIGHT OUTER JOIN
                                      rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON 
                                      p.CARD = rt_shaving_out.card1
                   GROUP BY p.type, p.CARD, p.CARD1, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, 
                                      rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, 
                                      rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, 
                                      RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT
                   ORDER BY p.CARD) AS s
GROUP BY CARD, card2, 类别, type, 下鼓重, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 
                   备注, 皮源1, 代码"


        '--------------全部下鼓

        sql = sql1 + sql2 + sql3 + " order by date "
        cmd.CommandText = sql
        dt = New DataTable
        da.SelectCommand = cmd
        da.Fill(dt)

        dgv2.DataSource = dt

        Label3.Text = dt.Compute("sum(下鼓重)", "")
        '  Label6.Text = dt.Compute("sum(出库重)", "")


        'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 & ") as p"
        'cmd.CommandText = sql

        'Label3.Text = cmd.ExecuteScalar.ToString



        Dim dt1 As New DataTable
        Dim dt2 As New DataTable
        Dim dt3 As New DataTable
        Dim dt4 As New DataTable
        Dim dt5 As New DataTable
        Dim dt6 As New DataTable


        '------蓝皮下鼓
        Dim dv As New DataView(dt)
        dv.RowFilter = ("出库重 <>0 and 类别1='打底'")
        dt1 = dv.ToTable()
        DataGridView3.DataSource = dt1
        TextBox1.Text = dt1.Compute("sum(出库重)", "")
        '------蓝皮下鼓(无出库)
        dv.RowFilter = ("出库重 =0 and 类别<>'其他' and 类别1='打底' and 皮源 not like '*干*' and 皮源 not like '*brf*' ")
        dt2 = dv.ToTable()
        DataGridView5.DataSource = dt2
        TextBox2.Text = dt2.Compute("sum(下鼓重)", "")
        '------套染
        dv.RowFilter = ("(类别1='套染' or 皮源 like '*C级*') and (皮源 not like '*brf*')")
        dt3 = dv.ToTable()
        DataGridView1.DataSource = dt3
        TextBox3.Text = dt3.Compute("sum(下鼓重)", "")
        '------回鼓
        dv.RowFilter = ("类别1='回鼓' and 皮源 not like '*干*'")
        dt4 = dv.ToTable()
        DataGridView4.DataSource = dt4
        TextBox5.Text = dt4.Compute("sum(出库重)", "")

        '------改色
        dv.RowFilter = ("(皮源 like '*干*' or 皮源 like '*brf*') and (皮源 not like '*C级*') ")
        dt5 = dv.ToTable()
        DataGridView2.DataSource = dt5
        TextBox4.Text = dt5.Compute("sum(下鼓重)", "")

        '------改色
        dv.RowFilter = ("类别='其他'")
        dt6 = dv.ToTable()
        DataGridView6.DataSource = dt6
        TextBox6.Text = dt6.Compute("sum(下鼓重)", "")




        TextBox7.Text = Val(TextBox1.Text) + Val(TextBox2.Text) + Val(TextBox3.Text) + Val(TextBox4.Text) + Val(TextBox5.Text) + Val(TextBox6.Text)



        DataGridView1.Columns("卡号1").Visible = False
        DataGridView1.Columns(0).Width = 150
        DataGridView1.Columns(1).Width = 70
        DataGridView1.Columns(2).Width = 66
        DataGridView1.Columns(3).Width = 73
        DataGridView1.Columns("技术员").Width = 80
        DataGridView1.Columns("代码").Width = 65
        DataGridView1.Columns("单价").Width = 65
        DataGridView1.Columns("厚度").Width = 65
        DataGridView1.Columns("日期").Width = 90
        DataGridView1.Columns("选级").Width = 65

        DataGridView2.Columns("卡号1").Visible = False
        DataGridView2.Columns(0).Width = 150
        DataGridView2.Columns(1).Width = 70
        DataGridView2.Columns(2).Width = 66
        DataGridView2.Columns(3).Width = 73
        DataGridView2.Columns("技术员").Width = 80
        DataGridView2.Columns("代码").Width = 65
        DataGridView2.Columns("单价").Width = 65
        DataGridView2.Columns("厚度").Width = 65
        DataGridView2.Columns("日期").Width = 90
        DataGridView2.Columns("选级").Width = 65
        DataGridView3.Columns("卡号1").Visible = False
        DataGridView3.Columns(0).Width = 150
        DataGridView3.Columns(1).Width = 70
        DataGridView3.Columns(2).Width = 66
        DataGridView3.Columns(3).Width = 73
        DataGridView3.Columns("技术员").Width = 80
        DataGridView3.Columns("代码").Width = 65
        DataGridView3.Columns("单价").Width = 65
        DataGridView3.Columns("厚度").Width = 65
        DataGridView3.Columns("日期").Width = 90
        DataGridView3.Columns("选级").Width = 65
        DataGridView4.Columns("卡号1").Visible = False
        DataGridView4.Columns(0).Width = 150
        DataGridView4.Columns(1).Width = 70
        DataGridView4.Columns(2).Width = 66
        DataGridView4.Columns(3).Width = 73
        DataGridView4.Columns("技术员").Width = 80
        DataGridView4.Columns("代码").Width = 65
        DataGridView4.Columns("单价").Width = 65
        DataGridView4.Columns("厚度").Width = 65
        DataGridView4.Columns("日期").Width = 90
        DataGridView4.Columns("选级").Width = 65
        DataGridView5.Columns("卡号1").Visible = False
        DataGridView5.Columns(0).Width = 150
        DataGridView5.Columns(1).Width = 70
        DataGridView5.Columns(2).Width = 66
        DataGridView5.Columns(3).Width = 73
        DataGridView5.Columns("技术员").Width = 80
        DataGridView5.Columns("代码").Width = 65
        DataGridView5.Columns("单价").Width = 65
        DataGridView5.Columns("厚度").Width = 65
        DataGridView5.Columns("日期").Width = 90
        DataGridView5.Columns("选级").Width = 65
        DataGridView6.Columns("卡号1").Visible = False
        DataGridView6.Columns(0).Width = 150
        DataGridView6.Columns(1).Width = 70
        DataGridView6.Columns(2).Width = 66
        DataGridView6.Columns(3).Width = 73
        DataGridView6.Columns("技术员").Width = 80
        DataGridView6.Columns("代码").Width = 65
        DataGridView6.Columns("单价").Width = 65
        DataGridView6.Columns("厚度").Width = 65
        DataGridView6.Columns("日期").Width = 90
        DataGridView6.Columns("选级").Width = 65






        'sql = sql1 + sql2 + sql3 + " having AVG(挑级仓出库重) is not null and type=N'打底' order by date"
        'cmd.CommandText = sql
        'dt = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt)





        'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having AVG(挑级仓出库重) is not null and  type=N'打底' " & ") as p"
        'cmd.CommandText = sql

        'Label6.Text = cmd.ExecuteScalar.ToString
        'TextBox1.Text = cmd.ExecuteScalar.ToString

        '------蓝皮下鼓(无)

        'sql = sql1 + sql2 + sql3 + " having AVG(挑级仓出库重) is null and 类别<>N'其他' and type=N'打底' order by date"
        'cmd.CommandText = sql
        'dt = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt)

        'DataGridView5.DataSource = dt



        'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having AVG(挑级仓出库重) is null and 类别<>N'其他' and type=N'打底' " & ") as p"
        'cmd.CommandText = sql

        'Label16.Text = cmd.ExecuteScalar.ToString
        'TextBox2.Text = cmd.ExecuteScalar.ToString


        ''------套染

        'sql = sql1 + sql2 + sql3 + " having type=N'套染' order by date"
        'cmd.CommandText = sql
        'dt = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt)

        'DataGridView1.DataSource = dt



        'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having type=N'套染' " & ") as p"
        'cmd.CommandText = sql

        'Label16.Text = cmd.ExecuteScalar.ToString
        'TextBox3.Text = cmd.ExecuteScalar.ToString



        ''------回鼓

        'sql = sql1 + sql2 + sql3 + " having type=N'回鼓' order by date"
        'cmd.CommandText = sql
        'dt = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt)

        'DataGridView4.DataSource = dt



        'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having type=N'回鼓' " & ") as p"
        'cmd.CommandText = sql

        'Label16.Text = cmd.ExecuteScalar.ToString
        'TextBox5.Text = cmd.ExecuteScalar.ToString


        ''------合鼓

        'sql = sql1 + sql2 + sql3 + " having 类别=N'其他' order by date"
        'cmd.CommandText = sql
        'dt = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt)

        'DataGridView6.DataSource = dt



        'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having 类别=N'其他' " & ") as p"
        'cmd.CommandText = sql


        'TextBox6.Text = cmd.ExecuteScalar.ToString







    End Sub

    Private Sub Frm_Retan_Load(sender As Object, e As EventArgs) Handles MyBase.Load




        'connstring = "Data Source=127.0.0.1;Persist Security Info=True;User ID=sa;Password=Lafayette11;Initial Catalog=maindb_hy2014"
        conn.ConnectionString = connstring
        conn.Open()





        dgv2.RowHeadersWidth = 15
        dgv2.ColumnHeadersHeight = 20


        dgv2.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        'DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        'DataGridView2.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        DataGridView3.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        ' DataGridView4.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        ' DataGridView5.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        '  DataGridView6.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        ' DataGridView7.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        '  DataGridView8.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText


        cmd.Connection = conn
        sql = "SELECT  TOP (1) date FROM     RT_RETAN_RECORD ORDER BY date DESC"
        cmd.CommandText = sql

        Label11.Text = cmd.ExecuteScalar


    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click


        Dim aa As String
        Dim bb As String
        Dim my_card As String = ""
        Dim my_date As Date

        aa = TextBox8.Text
        bb = aa


        If aa = "" Then
            MsgBox("请输入起始单号")
            Exit Sub
        End If



        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 xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet




        Dim file_name As String = ""
        Dim file_name1 As String = ""
        Dim my_weight As Double = 0
        Dim a As Integer = 0
        Dim my_count As Integer = 0
        Dim my_x As Integer = 0



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


        xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\下鼓染色入库.xls",, True)

        xlBook.Activate()

        Dim dt2 As New DataTable


        For i = 1 To xlApp.Worksheets.Count

            If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
                xlSheet = xlApp.Workbooks(1).Worksheets(i)
                xlSheet.Activate()
                Exit For
            End If


        Next


        Dim my_no As String = "CPRK0"
        Dim yy As Integer = 1
        Dim x As Integer = 0


        'sql = ""   ''----下鼓


        'Dim dt_out As DataTable

        'cmd.CommandText = sql

        'dt_out = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt_out)

        Dim my_total_no As Integer = 0

        Dim dt_retan As New DataTable

        sql = "SELECT  date FROM       RT_RETAN_RECORD_D
                                           WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_retan)




        For x = 0 To dt_retan.Rows.Count - 1          '-----------loop





            xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 3) = "'郑笔成"
            xlSheet.Cells(x + 2, 4) = "'" & my_no & bb
            xlSheet.Cells(x + 2, 5) = "'zhang"
            xlSheet.Cells(x + 2, 6) = "'1"
            xlSheet.Cells(x + 2, 7) = "'2"
            xlSheet.Cells(x + 2, 8) = "'165701"

            'If x = 1 Or x = 2 Then

            '    xlSheet.Cells(x + 2, 9) = "'607-R"
            '    xlSheet.Cells(x + 2, 10) = "'挑选等级-R"


            'Else
            xlSheet.Cells(x + 2, 9) = "'621-R"
            xlSheet.Cells(x + 2, 10) = "'染色-R"



            'End If


            xlSheet.Cells(x + 2, 11) = "'103"
            xlSheet.Cells(x + 2, 12) = "'曾添梅"
            xlSheet.Cells(x + 2, 13) = "'103"
            xlSheet.Cells(x + 2, 14) = "'曾添梅"
            xlSheet.Cells(x + 2, 15) = "'85"




            For i = 1 To xlApp.Worksheets.Count

                If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then
                    xlSheet2 = xlApp.Workbooks(1).Worksheets(i)
                    xlSheet2.Activate()
                    Exit For
                End If

            Next






            sql = "SELECT top(100) percent CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源,  DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end  AS 出库重, SUM(chem) AS 化工成本 FROM      (SELECT  TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10)                                       + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1)                                       = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL                                       THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL                                       THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL                                       THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL                                       THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1,                                       rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL                                       THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL                                       THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE,                                       CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd,                                       CASE WHEN rt_shaving_out.card1 IS NULL AND                                       p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND                                       p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR  p.source LIKE N'%bx%' or p.source LIKE N'%南美%') and p.source not like '%美国%'     THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%'                                       THEN N'美国 二层'WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源1,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source                                       LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%')                                       THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source                                       LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN                                       N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE                                       N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ'                                       WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%'                                       THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source                                       LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码,   rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight)  / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice,  RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL                                       THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem                   FROM       RT_SYS_CHEM_OUT INNER JOIN     rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN                                          (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2,                                                               W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff,                                                               gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as 鼓号                                           FROM       RT_RETAN_RECORD_D                                           WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN                                      rt_wetblue_name RIGHT OUTER JOIN     rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON    p.CARD = rt_shaving_out.card1                   GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code,                                       rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source,                                       rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT                   ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd,   备注, 皮源1, 代码"









            Dim sql1 As String

            Dim my_type As String

            Dim serial As Integer = 1
            For xx As Integer = 1 To 6

                Select Case xx
                    Case 1
                        sql1 = sql + " having (avg(挑级仓出库重)) is not null and type =N'打底' and source not like N'%干%' "
                        my_type = "--蓝皮下鼓--"


                        sql1 = "SELECT  zz.卡号, zz.卡号1, zz.鼓号, zz.类别, zz.类别1, zz.下鼓重, zz.单价, zz.code, zz.选级, zz.出库卡, zz.客户, zz.颜色, zz.皮源, zz.日期, zz.厚度, zz.技术员, zz.皮源1, zz.代码, zz.出库重, zz.化工成本, rt_TM240630.weight FROM     (" + sql1 + ") AS zz LEFT OUTER JOIN  (select date,card,weight,commentd from rt_TM240630   AS rt_TM240630_1 where date='2024/8/31') as rt_tm240630 ON zz.卡号 = rt_tm240630.card WHERE   (rt_tm240630.weight IS NULL)"



                    Case 2
                        sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%C级%') and (type<>N'回鼓')  order by date"
                        my_type = "--改色--"
                    Case 3
                        sql1 = sql + " having (type=N'套染' or source like N'%C级%') and (source not like N'%brf%')  order by date"
                        my_type = "--套染--"
                    Case 4
                        sql1 = sql + " having type='回鼓'  order by date"
                        my_type = "--回鼓--"
                    Case 5
                        sql1 = sql + " having  (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date "
                        my_type = "--蓝皮下鼓无对应--"
                    Case 6
                        sql1 = "SELECT  zz.卡号, zz.卡号1, zz.鼓号, zz.类别, zz.类别1, zz.下鼓重, zz.单价, zz.code, zz.选级, zz.出库卡, zz.客户, zz.颜色, zz.皮源, zz.日期, zz.厚度, zz.技术员, zz.皮源1, zz.代码, zz.出库重, zz.化工成本, rt_TM240630.weight FROM     (" + sql1 + ") AS zz LEFT OUTER JOIN  (select date,card,weight,commentd from rt_TM240630   AS rt_TM240630_1 where date='2024/9/30') as rt_tm240630 ON zz.卡号 = rt_tm240630.card WHERE  (rt_tm240630.commentD LIKE N'%鼓中%')"
                        my_type = "--蓝皮下鼓(鼓中)--"
                End Select



                dt2 = New DataTable
                cmd.CommandText = sql1
                da.SelectCommand = cmd
                da.Fill(dt2)








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

                    xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial  '行号
                    xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb






                    '   xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价")

                    If my_type = "--回鼓--" Then

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                            '         xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                        Else
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                            '         xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                        End If

                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If


                    ElseIf my_type = "--改色--" Then

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If


                    ElseIf my_type = "--套染--" Then

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                            '       xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                            xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        Else
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                            '    xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")
                            xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        End If

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If



                    ElseIf my_type = "--蓝皮下鼓--" Then
                        xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价")

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                        xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")
                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If

                    ElseIf my_type = "--蓝皮下鼓无对应--" Then

                        xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价")

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If

                    End If




                    xlSheet2.Cells(yy + 1 + y, 18) = dt2.Rows(y).Item("鼓号") & my_type   '备注
                    ' xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期")


                    Select Case xx
                        Case 1
                            xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01"
                            xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓"

                            xlSheet.Cells(x + 2, 9) = "'607-R"
                            xlSheet.Cells(x + 2, 10) = "'挑选等级-R"

                        Case 2
                            xlSheet2.Cells(yy + 1 + y, 25) = "'313"
                            xlSheet2.Cells(yy + 1 + y, 26) = "涂饰在制品" '皮胚改色

                            xlSheet.Cells(x + 2, 9) = "'621-R"
                            xlSheet.Cells(x + 2, 10) = "'染色-R"
                            'xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01"
                            'xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓"
                        Case 3
                            xlSheet2.Cells(yy + 1 + y, 25) = "'313"
                            xlSheet2.Cells(yy + 1 + y, 26) = "涂饰在制品" '套染

                            xlSheet.Cells(x + 2, 9) = "'621-R"
                            xlSheet.Cells(x + 2, 10) = "'染色-R"
                        Case 4
                            xlSheet2.Cells(yy + 1 + y, 25) = "'313"
                            xlSheet2.Cells(yy + 1 + y, 26) = "涂饰在制品" '回鼓

                            xlSheet.Cells(x + 2, 9) = "'621-R"
                            xlSheet.Cells(x + 2, 10) = "'染色-R"
                        Case 5

                            xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01"
                            xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓"

                            xlSheet.Cells(x + 2, 9) = "'607-R"
                            xlSheet.Cells(x + 2, 10) = "'挑选等级-R"

                            'Case 6
                            '    xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.08"
                            '    xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/合鼓中和"

                    End Select




                    xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别")
                    xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("客户")
                    xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员")
                    xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源")
                    xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("颜色")
                    xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("厚度")
                    xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("卡号")
                    xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级")
                    xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本")

                    xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("出库卡")


                    my_total_no = yy + 1 + y


                    serial = serial + 1

                Next

                yy = yy + dt2.Rows.Count


                aa = aa + 1
                my_x = my_x + 1

            Next
            bb = bb + 1
        Next
        xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("O2:O" & x + 1).FillDown()
        'xlSheet2.Range("P2:P" & x + 1).FillDown()



        xlSheet2.Range("G:M").EntireColumn.Hidden = True
        xlSheet2.Range("T:X").EntireColumn.Hidden = True
        xlSheet2.Range("AB:AK").EntireColumn.Hidden = True



        PTCache = xlBook.PivotCaches.Add _
        (SourceType:=Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase,
         SourceData:=xlSheet2.Range("a1:au" & xlSheet2.UsedRange.Rows.Count).CurrentRegion.Address)

        PT = PTCache.CreatePivotTable _
        (TableDestination:=xlSheet2.Cells(2, 51),
         TableName:="PivotTable1")

        With PT
            '.PivotFields("批号").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField
            .PivotFields("批号").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
            .PivotFields("实收数量").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
        End With

        xlSheet2.PivotTables("PivotTable1").TableStyle2 = "PivotStyleMedium9"


        '----------------
        PT = PTCache.CreatePivotTable(TableDestination:=xlSheet2.Cells(2, 54),
         TableName:="PivotTable2")

        With PT
            '.PivotFields("批号").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField
            .PivotFields("备注").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
            .PivotFields("实收数量").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
        End With

        xlSheet2.PivotTables("PivotTable2").TableStyle2 = "PivotStyleMedium9"



        '----------------
        PT = PTCache.CreatePivotTable(TableDestination:=xlSheet2.Cells(2, 57),
         TableName:="PivotTable3")

        With PT
            '.PivotFields("批号").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField
            .PivotFields("收货仓库_FName").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
            .PivotFields("实收数量").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
        End With

        xlSheet2.PivotTables("PivotTable3").TableStyle2 = "PivotStyleMedium9"



        MsgBox("")



    End Sub

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


        Dim aa As String
        Dim bb As String
        Dim my_card As String = ""
        Dim my_date As Date

        aa = TextBox9.Text
        bb = aa


        If aa = "" Then
            MsgBox("请输入起始单号")
            Exit Sub
        End If



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

        Dim xlSheet_1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlSheet2_2 As Microsoft.Office.Interop.Excel.Worksheet


        Dim file_name As String = ""
        Dim file_name1 As String = ""
        Dim my_weight As Double = 0
        Dim a As Integer = 0
        Dim my_count As Integer = 0
        Dim my_x As Integer = 0



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

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




        xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\水场出库.xls",, True)

        xlBook.Activate()

        Dim dt2 As New DataTable

        For i = 1 To xlApp.Worksheets.Count

            If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
                xlSheet = xlApp.Workbooks(1).Worksheets(i)
                xlSheet.Activate()
                Exit For
            End If

        Next


        Dim my_no As String = "SCLL0"
        Dim yy As Integer = 1
        Dim x As Integer = 0


        Dim my_total_no As Integer = 0

        Dim dt_retan As New DataTable

        sql = "SELECT  date FROM       RT_RETAN_RECORD_D
                                           WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_retan)


        Dim final_no As Integer

        For x = 0 To dt_retan.Rows.Count - 1          '-----------loop





            xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 3) = "'曾添梅"
            xlSheet.Cells(x + 2, 4) = "'" & my_no & bb
            xlSheet.Cells(x + 2, 5) = "'zhang"
            xlSheet.Cells(x + 2, 6) = "'1"
            xlSheet.Cells(x + 2, 7) = "'2"
            xlSheet.Cells(x + 2, 8) = "'165701"
            xlSheet.Cells(x + 2, 9) = "'622-R"
            xlSheet.Cells(x + 2, 10) = "'煎板、真空、挤水-R"
            xlSheet.Cells(x + 2, 12) = "'103"
            xlSheet.Cells(x + 2, 13) = "'曾添梅"
            xlSheet.Cells(x + 2, 14) = "'103"
            xlSheet.Cells(x + 2, 15) = "'曾添梅"
            ' xlSheet.Cells(x + 2, 15) = "'85"




            For i = 1 To xlApp.Worksheets.Count

                If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then
                    xlSheet2 = xlApp.Workbooks(1).Worksheets(i)
                    xlSheet2.Activate()
                    Exit For
                End If

            Next





            sql = "SELECT  CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源,  DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end  AS 出库重, SUM(chem) AS 化工成本 FROM      (SELECT  TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10)                                       + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1)                                       = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL                                       THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL                                       THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL   THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL  THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1,                                       rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL                                       THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL                                       THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE,                                       CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd,                                       CASE WHEN rt_shaving_out.card1 IS NULL AND                                       p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND                                       p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%')     and p.source not like '%美国%'                                  THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%'                                       THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源1,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source                                       LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%')                                       THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source                                       LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN                                       N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE                                       N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ'                                       WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%'                                       THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source                                       LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码,   rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight)  / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice,  RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL                                       THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem                   FROM       RT_SYS_CHEM_OUT INNER JOIN     rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN                                          (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2,                                                               W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff,                                                               gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as '鼓号'                                           FROM       RT_RETAN_RECORD_D                                           WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN                                      rt_wetblue_name RIGHT OUTER JOIN     rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON    p.CARD = rt_shaving_out.card1
                   GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code,  rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source,   rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd,                    备注, 皮源1, 代码"






            Dim sql1 As String

            Dim my_type As String

            Dim serial As Integer = 1
            For xx As Integer = 1 To 5

                Select Case xx
                    Case 1


                        sql1 = "SELECT  y.卡号, y.鼓号, y.类别, y.类别1, y.生产重, y.下鼓重, y.单价, y.code, y.选级, y.出库卡, y.客户, y.颜色, y.皮源, y.日期, y.date_buff, y.厚度, y.技术员, y.皮源1, y.代码, 
               y.出库重, y.化工成本, rt_TM240731.card
FROM     (SELECT  s.CARD AS 卡号, s.鼓号, s.类别, s.type AS 类别1, CASE WHEN AVG(下鼓重) IS NULL THEN AVG(挑级仓出库重) ELSE AVG(下鼓重) END AS 生产重, 
                               CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, 
                               s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期, zz.date_buff, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, 
                               CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE AVG(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本
               FROM      (SELECT  TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 
                                               10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) 
                                               = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, 
                                               CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL 
                                               THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL 
                                               THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL 
                                               THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, 
                                               p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, 
                                               CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, 
                                               CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) 
                                               = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) 
                                               = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, 
                                               CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND 
                                               p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, 
                                               CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%'
                                                THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR
                                               p.source LIKE N'%bx%' OR
                                               p.source LIKE N'%南美%') 
                                               THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE
                                                N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN
                                                p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层'
                                                WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%'
                                                THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source
                                                LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层'
                                                WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, 
                                               CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU'
                                                WHEN (p.source LIKE N'%巴西%' OR
                                               p.source LIKE N'%bx%' OR
                                               p.source LIKE N'%南美%') 
                                               THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN
                                                N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE'
                                                WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN
                                                p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source
                                                LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE
                                                N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, 
                                               CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, 
                                               RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, 
                                               RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL 
                                               THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem
                               FROM      RT_SYS_CHEM_OUT INNER JOIN
                                               rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN
                                                   (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, 
                                                                  source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND 
                                                                  source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, 
                                                                  ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN '中鼓' ELSE '大鼓' END AS 鼓号
                                                   FROM     RT_RETAN_RECORD_D
                                                   WHERE   (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) as p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN
                                               rt_wetblue_name RIGHT OUTER JOIN
                                               rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1
                               GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, 
                                               rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, 
                                               RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN
                                   (SELECT  card AS card_buff, MIN(date) AS date_buff
                                   FROM     Rt_buff_daily  GROUP BY card) AS zz ON s.CARD = zz.card_buff
               GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, 
                               s.dd, s.备注, s.皮源1, s.代码, zz.date_buff
               HAVING   (zz.date_buff IS NOT NULL) AND (AVG(s.挑级仓出库重) IS NOT NULL) AND (s.type = N'打底') AND (s.source NOT LIKE N'%干%')) AS y LEFT OUTER JOIN rt_TM240731 ON y.卡号 = rt_TM240731.card WHERE   (rt_TM240731.card IS NULL)"

                        my_type = "--蓝皮下鼓(已磨皮)--"

                    Case 2
                        sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%A级%') and (source not like N'%B级%') and (source not like N'%C级%') and (type<>N'回鼓') and color not like N'黑色%' order by date"
                        my_type = "--改色--"
                    Case 3
                        sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%')  order by date"
                        my_type = "--套染--"
                    Case 4
                        sql1 = sql + " having type='回鼓'  order by date"
                        my_type = "--回鼓--"
                    Case 5
                        sql1 = sql + " having  (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date "
                        my_type = "--蓝皮下鼓无对应--"
                        'Case 6
                        '    sql1 = sql + " having 类别=N'其他'  order by date"
                        '    my_type = "--合鼓--"

                End Select



                dt2 = New DataTable
                cmd.CommandText = sql1
                da.SelectCommand = cmd
                da.Fill(dt2)








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

                    xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial  '行号
                    xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb


                    'xlSheet2.Cells(yy + 1 + y, 5) = "112." & dt2.Rows(y).Item("皮源代码")
                    'xlSheet2.Cells(yy + 1 + y, 6) = dt2.Rows(y).Item("皮源1")

                    '  xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价")

                    If my_type = "--回鼓--" Then

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM"
                            xlSheet2.Cells(yy + 1 + y, 7) = "'二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU"
                            xlSheet2.Cells(yy + 1 + y, 7) = "'贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 23) = "'313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("出库重") = 0 Then

                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                            '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")


                        Else

                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                            '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")


                        End If


                    ElseIf my_type = "--改色--" Then
                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM"
                            xlSheet2.Cells(yy + 1 + y, 7) = "'二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU"
                            xlSheet2.Cells(yy + 1 + y, 7) = "'贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 23) = "'313 " & dt2.Rows(y).Item("皮源1")   '批号

                        xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                        '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                    ElseIf my_type = "--套染--" Then
                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM"
                            xlSheet2.Cells(yy + 1 + y, 7) = "'二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU"
                            xlSheet2.Cells(yy + 1 + y, 7) = "'贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 23) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                            '      xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                        Else
                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                            '    xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                        End If



                    ElseIf my_type = "--蓝皮下鼓(已磨皮)--" Then
                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM"
                            xlSheet2.Cells(yy + 1 + y, 7) = "'二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU"
                            xlSheet2.Cells(yy + 1 + y, 7) = "'贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 23) = "'313 " & dt2.Rows(y).Item("皮源1")   '批号

                        xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                        '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                    ElseIf my_type = "--蓝皮下鼓无对应--" Then

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM"
                            xlSheet2.Cells(yy + 1 + y, 7) = "二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU"
                            xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 23) = "'313 " & dt2.Rows(y).Item("皮源1")   '批号

                        xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                        '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")


                    End If

                    xlSheet2.Cells(yy + 1 + y, 28) = "'--" & dt2.Rows(y).Item("日期") & " 磨皮--"


                    xlSheet2.Cells(yy + 1 + y, 55) = dt2.Rows(y).Item("鼓号") & my_type   '备注
                    '    xlSheet2.Cells(yy + 1 + y, 32) = dt2.Rows(y).Item("日期")


                    Select Case xx
                        Case 1
                            xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.01"
                            xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/蓝皮下鼓"

                        Case 2
                            xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.04"
                            xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/皮胚改色"

                            'xlSheet.Cells(x + 2, 9) = "'621-R"
                            'xlSheet.Cells(x + 2, 10) = "'染色-R"
                            'xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01"
                            'xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓"
                        Case 3
                            xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.02"
                            xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/皮胚套染"

                            'xlSheet.Cells(x + 2, 9) = "'621-R"
                            'xlSheet.Cells(x + 2, 10) = "'染色-R"
                        Case 4
                            xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.03"
                            xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/皮胚回鼓"

                            'xlSheet.Cells(x + 2, 9) = "'621-R"
                            'xlSheet.Cells(x + 2, 10) = "'染色-R"
                        Case 5

                            xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.01"
                            xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/蓝皮下鼓"

                            'xlSheet.Cells(x + 2, 9) = "'607-R"
                            'xlSheet.Cells(x + 2, 10) = "'挑选等级-R"

                            'Case 6
                            '    xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.08"
                            '    xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/合鼓中和"

                    End Select





                    'If dt_out.Rows(x).Item("type") = "打底" And IsDBNull(dt_out.Rows(x).Item("card_shaving")) And dt_out.Rows(x).Item("类别") <> "其他" Then

                    'ElseIf dt_out.Rows(x).Item("type") = "打底" And Not (IsDBNull(dt_out.Rows(x).Item("card_shaving"))) Then

                    'ElseIf dt_out.Rows(x).Item("type") = "套染" Then

                    'ElseIf dt_out.Rows(x).Item("type") = "回鼓" Or dt_out.Rows(x).Item("type") = "套色" Then

                    'ElseIf dt_out.Rows(x).Item("类别") = "其他" Then

                    'End If





                    xlSheet2.Cells(yy + 1 + y, 53) = dt2.Rows(y).Item("类别")
                    xlSheet2.Cells(yy + 1 + y, 52) = "'" & dt2.Rows(y).Item("客户")
                    ' xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员")
                    xlSheet2.Cells(yy + 1 + y, 54) = dt2.Rows(y).Item("皮源")
                    xlSheet2.Cells(yy + 1 + y, 56) = dt2.Rows(y).Item("颜色")
                    xlSheet2.Cells(yy + 1 + y, 57) = dt2.Rows(y).Item("厚度")
                    xlSheet2.Cells(yy + 1 + y, 58) = dt2.Rows(y).Item("卡号")
                    xlSheet2.Cells(yy + 1 + y, 61) = dt2.Rows(y).Item("选级")
                    '  xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本")

                    xlSheet2.Cells(yy + 1 + y, 60) = dt2.Rows(y).Item("出库卡")


                    my_total_no = yy + 1 + y


                    serial = serial + 1

                Next

                yy = yy + dt2.Rows.Count


                aa = aa + 1
                my_x = my_x + 1

            Next
            bb = bb + 1
            final_no = x
        Next




        sql = "SELECT  TOP (200) rt_TM240630.date, rt_TM240630.no, rt_TM240630.card, rt_TM240630.type, rt_TM240630.word, rt_TM240630.cust, rt_TM240630.color, 
               rt_TM240630.weight_ini, rt_TM240630.tech, rt_TM240630.source, rt_TM240630.thick, rt_TM240630.word1, CAST(rt_TM240630.weight / 2 AS numeric(10, 1)) 
               AS weight, rt_TM240630.commentB, rt_TM240630.commentC, rt_TM240630.commentD, rt_TM240630.commentE, rt_TM240630.serial, rt_TM240630.comment, 
               rt_TM240630.source1, rt_TM240630.code, z.date_buff,CASE WHEN rt_tm240630.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN rt_tm240630.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN rt_tm240630.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (rt_tm240630.source LIKE N'%巴西%' OR                                      rt_tm240630.source LIKE N'%bx%' or rt_tm240630.source LIKE N'%南美%')                                       THEN N'巴西 二层' WHEN rt_tm240630.source LIKE N'%智利%' THEN N'智利 二层' WHEN rt_tm240630.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN rt_tm240630.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN rt_tm240630.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       rt_tm240630.source LIKE N'%法国%' THEN N'法国 二层' WHEN rt_tm240630.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN rt_tm240630.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN rt_tm240630.source LIKE N'%日本%' THEN N'日本 二层' WHEN rt_tm240630.source LIKE N'%美国%'                                       THEN N'美国 二层' WHEN rt_tm240630.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN rt_tm240630.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN rt_tm240630.source LIKE N'%英国%' THEN N'英国 二层' WHEN rt_tm240630.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN rt_tm240630.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN rt_tm240630.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       rt_tm240630.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN rt_tm240630.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源
FROM     rt_TM240630 LEFT OUTER JOIN
                   (SELECT  card AS card_buff, MIN(date) AS date_buff
                   FROM     Rt_buff_daily
                   GROUP BY card) AS z ON rt_TM240630.card = z.card_buff
WHERE   (rt_TM240630.commentE LIKE N'%水场%') AND (z.date_buff < '2024/09/01') AND (z.date_buff > '2024/7/31') AND rt_TM240630.date = '2024/07/31'"  '----240630期初 
        dt = New DataTable

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


        Dim final_no1 As Integer

        final_no1 = final_no

        For dd As Integer = 0 To dt.Rows.Count - 1
            Dim serial1 As Integer = 1

            xlSheet.Cells(dd + final_no1 + 3, 1) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff"))
            xlSheet.Cells(dd + final_no1 + 3, 2) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff"))
            xlSheet.Cells(dd + final_no1 + 3, 3) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 4) = "'" & my_no & bb
            xlSheet.Cells(dd + final_no1 + 3, 5) = "'zhang"
            xlSheet.Cells(dd + final_no1 + 3, 6) = "'1"
            xlSheet.Cells(dd + final_no1 + 3, 7) = "'2"
            xlSheet.Cells(dd + final_no1 + 3, 8) = "'165701"
            xlSheet.Cells(dd + final_no1 + 3, 9) = "'622-R"
            xlSheet.Cells(dd + final_no1 + 3, 10) = "'煎板、真空、挤水-R"
            xlSheet.Cells(dd + final_no1 + 3, 12) = "'103"
            xlSheet.Cells(dd + final_no1 + 3, 13) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 14) = "'103"
            xlSheet.Cells(dd + final_no1 + 3, 15) = "'曾添梅"



            xlSheet2.Cells(yy + 1 + dd, 2) = "'" & serial1  '行号
            xlSheet2.Cells(yy + 1 + dd, 3) = "'" & my_no & bb


            If dt.Rows(dd).Item("type") = "反绒" Then
                xlSheet2.Cells(yy + 1 + dd, 6) = "313.FM"
                xlSheet2.Cells(yy + 1 + dd, 7) = "二层皮"
                xlSheet2.Cells(yy + 1 + dd, 23) = "313 " & dt.Rows(dd).Item("皮源")   '批号

            Else
                xlSheet2.Cells(yy + 1 + dd, 6) = "313.PU"
                xlSheet2.Cells(yy + 1 + dd, 7) = "贴膜皮"
                xlSheet2.Cells(yy + 1 + dd, 23) = "313 " & dt.Rows(dd).Item("皮源")   '批号
            End If


            xlSheet2.Cells(yy + 1 + dd, 24) = dt.Rows(dd).Item("weight")
            xlSheet2.Cells(yy + 1 + dd, 28) = "'-240731盘点-" & dt.Rows(dd).Item("date_buff") & " 磨皮--"
            xlSheet2.Cells(yy + 1 + dd, 32) = dt.Rows(dd).Item("date_buff")
            xlSheet2.Cells(yy + 1 + dd, 35) = "'213A-R.01"
            xlSheet2.Cells(yy + 1 + dd, 36) = "染色仓/蓝皮下鼓"
            xlSheet2.Cells(yy + 1 + dd, 53) = dt.Rows(dd).Item("type")
            xlSheet2.Cells(yy + 1 + dd, 52) = "'" & dt.Rows(dd).Item("cust")
            xlSheet2.Cells(yy + 1 + dd, 62) = dt.Rows(dd).Item("tech")
            xlSheet2.Cells(yy + 1 + dd, 54) = dt.Rows(dd).Item("source")
            xlSheet2.Cells(yy + 1 + dd, 56) = dt.Rows(dd).Item("color")
            xlSheet2.Cells(yy + 1 + dd, 57) = dt.Rows(dd).Item("thick")
            xlSheet2.Cells(yy + 1 + dd, 58) = dt.Rows(dd).Item("card")

            final_no += 1
            bb = bb + 1
        Next

        my_total_no = my_total_no + dt.Rows.Count
        'For dd As Integer = 0 To dt.Rows.Count - 1




        xlSheet2.Range("A2:A" & my_total_no).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & my_total_no).FillDown()
        xlSheet2.Range("I2:I" & my_total_no).FillDown()
        xlSheet2.Range("J2:J" & my_total_no).FillDown()
        xlSheet2.Range("K2:V" & my_total_no).FillDown()

        ' xlSheet.Range("A2:A" & final_no).FillDown()
        xlSheet.Range("R2:V" & final_no).FillDown()






        'xlBook1 = xlApp.Workbooks.Open("D:\K3\k3表格模板\313涂饰在制品入库.xls",, True)

        'xlBook1.Activate()




        'For i = 1 To xlApp.Worksheets.Count

        '    If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
        '        xlSheet = xlApp.Workbooks(1).Worksheets(i)
        '        xlSheet.Activate()
        '        Exit For
        '    End If


        'Next









        MsgBox("")
    End Sub

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




        Dim aa As String
        Dim bb As String
        Dim my_card As String = ""
        Dim my_date As Date

        aa = TextBox12.Text
        bb = aa


        If aa = "" Then
            MsgBox("请输入起始单号")
            Exit Sub
        End If



        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 xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet




        Dim file_name As String = ""
        Dim file_name1 As String = ""
        Dim my_weight As Double = 0
        Dim a As Integer = 0
        Dim my_count As Integer = 0
        Dim my_x As Integer = 0



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


        xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\313回鼓套染改色--入库.xls",, True)

        xlBook.Activate()

        Dim dt2 As New DataTable


        For i = 1 To xlApp.Worksheets.Count

            If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
                xlSheet = xlApp.Workbooks(1).Worksheets(i)
                xlSheet.Activate()
                Exit For
            End If


        Next


        Dim my_no As String = "CPRK0"
        Dim yy As Integer = 1
        Dim x As Integer = 0


        'sql = ""   ''----下鼓


        'Dim dt_out As DataTable

        'cmd.CommandText = sql

        'dt_out = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt_out)

        Dim my_total_no As Integer = 0

        Dim dt_retan As New DataTable

        sql = "SELECT  date FROM       RT_RETAN_RECORD_D
                                           WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_retan)




        For x = 0 To dt_retan.Rows.Count - 1          '-----------loop





            xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 3) = "'曾添梅"
            xlSheet.Cells(x + 2, 4) = "'" & my_no & bb
            xlSheet.Cells(x + 2, 5) = "'zhang"
            xlSheet.Cells(x + 2, 6) = "'1"
            xlSheet.Cells(x + 2, 7) = "'2"
            xlSheet.Cells(x + 2, 8) = "'165701"



            xlSheet.Cells(x + 2, 11) = "'103"
            xlSheet.Cells(x + 2, 12) = "'曾添梅"
            xlSheet.Cells(x + 2, 13) = "'103"
            xlSheet.Cells(x + 2, 14) = "'曾添梅"
            xlSheet.Cells(x + 2, 15) = "'85"




            For i = 1 To xlApp.Worksheets.Count

                If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then
                    xlSheet2 = xlApp.Workbooks(1).Worksheets(i)
                    xlSheet2.Activate()
                    Exit For
                End If

            Next





            sql = "SELECT  CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源,  DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end  AS 出库重, SUM(chem) AS 化工成本  FROM      (SELECT  TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10)                                       + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1)                                       = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL                                       THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL                                       THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL   THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL  THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1,                                       rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL                                       THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL                                       THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE,                                       CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd,                                       CASE WHEN rt_shaving_out.card1 IS NULL AND                                       p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND                                       p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%')                                       THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%'                                       THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源1,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source                                       LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%')                                       THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source                                       LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN                                       N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE                                       N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ'                                       WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%'                                       THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source                                       LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码,   rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight)  / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice,  RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL                                       THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem                   FROM       RT_SYS_CHEM_OUT INNER JOIN     rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN                                          (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2,                                                               W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff,                                                               gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as 鼓号                                          FROM       RT_RETAN_RECORD_D                                           WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN                                      rt_wetblue_name RIGHT OUTER JOIN     rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON    p.CARD = rt_shaving_out.card1
                   GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code,                                       rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source,                                       rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT                   ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd,                    备注, 皮源1, 代码"






            Dim sql1 As String

            Dim my_type As String

            Dim serial As Integer = 1
            For xx As Integer = 1 To 5

                Select Case xx
                    Case 1
                        sql1 = ""
                        'sql1 = sql + " having (avg(挑级仓出库重)) is not null and type =N'打底' order by date"
                        'my_type = "--蓝皮下鼓--"

                    Case 2
                        sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%a级%') and (source not like N'%b级%') and (source not like N'%C级%') and (type<>N'回鼓') and NOT(color like N'黑色%'  ) order by date"
                        my_type = "--改色--"
                    Case 3
                        sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%')  order by date"
                        my_type = "--套染--"
                    Case 4
                        sql1 = sql + " having type='回鼓'  order by date"
                        my_type = "--回鼓--"
                    Case 5
                        sql1 = ""
                        'sql1 = sql + " having  (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date "
                        'my_type = "--蓝皮下鼓无对应--"
                        'Case 6
                        '    sql1 = sql + " having 类别=N'其他'  order by date"
                        '    my_type = "--合鼓--"

                End Select


                cmd.Connection = conn


                dt2 = New DataTable

                cmd.CommandText = sql1
                da.SelectCommand = cmd

                If sql1 <> "" Then
                    da.Fill(dt2)

                End If








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

                    xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial  '行号
                    xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb






                    '     xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价")

                    If my_type = "--回鼓--" Then

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                            '    xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                        Else
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                            '   xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                        End If

                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If


                    ElseIf my_type = "--改色--" Then

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        '   xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If


                    ElseIf my_type = "--套染--" Then

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                            '    xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                            xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        Else
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                            '       xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")
                            xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        End If

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If



                    ElseIf my_type = "--蓝皮下鼓--" Then

                        'xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                        'xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")
                        'xlSheet2.Cells(yy + 1 + y, 17) = "112 " & dt2.Rows(y).Item("皮源1")   '批号

                        'If dt2.Rows(y).Item("类别") = "反毛" Then
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "112.FM"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "112.PU"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        'End If

                    ElseIf my_type = "--蓝皮下鼓无对应--" Then

                        'xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        'xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                        'xlSheet2.Cells(yy + 1 + y, 17) = "112 " & dt2.Rows(y).Item("皮源1")   '批号

                        'If dt2.Rows(y).Item("类别") = "反毛" Then
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "112.FM"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "112.PU"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        'End If

                    End If




                    xlSheet2.Cells(yy + 1 + y, 18) = dt2.Rows(y).Item("鼓号") & my_type   '备注
                    '  xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期")




                    xlSheet2.Cells(yy + 1 + y, 25) = "'313"
                    xlSheet2.Cells(yy + 1 + y, 26) = "涂饰在制品"

                    xlSheet.Cells(x + 2, 9) = "'621-R"
                    xlSheet.Cells(x + 2, 10) = "'染色-R"



                    xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别")
                    xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("客户")
                    xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员")
                    xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源")
                    xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("颜色")
                    xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("厚度")
                    xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("卡号")
                    xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级")
                    xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本")

                    xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("出库卡")


                    my_total_no = yy + 1 + y


                    serial = serial + 1

                Next

                yy = yy + dt2.Rows.Count


                aa = aa + 1
                my_x = my_x + 1

            Next
            bb = bb + 1
        Next
        xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("O2:O" & x + 1).FillDown()
        'xlSheet2.Range("P2:P" & x + 1).FillDown()





        MsgBox("")


    End Sub

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




        Dim aa As String
        Dim bb As String
        Dim my_card As String = ""
        Dim my_date As Date

        aa = TextBox10.Text
        bb = aa


        If aa = "" Then
            MsgBox("请输入起始单号")
            Exit Sub
        End If



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

        Dim xlSheet_1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlSheet2_2 As Microsoft.Office.Interop.Excel.Worksheet


        Dim file_name As String = ""
        Dim file_name1 As String = ""
        Dim my_weight As Double = 0
        Dim a As Integer = 0
        Dim my_count As Integer = 0
        Dim my_x As Integer = 0



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






        xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\313回套改--出库.xls",, True)

        xlBook.Activate()

        Dim dt2 As New DataTable

        For i = 1 To xlApp.Worksheets.Count

            If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
                xlSheet = xlApp.Workbooks(1).Worksheets(i)
                xlSheet.Activate()
                Exit For
            End If

        Next


        Dim my_no As String = "SCLL0"
        Dim yy As Integer = 1
        Dim x As Integer = 0


        Dim my_total_no As Integer = 0

        Dim dt_retan As New DataTable

        sql = "SELECT  date FROM       RT_RETAN_RECORD_D     WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_retan)


        Dim final_no As Integer

        For x = 0 To dt_retan.Rows.Count - 1          '-----------loop





            xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 3) = "'曾添梅"
            xlSheet.Cells(x + 2, 4) = "'" & my_no & bb
            xlSheet.Cells(x + 2, 5) = "'zhang"
            xlSheet.Cells(x + 2, 6) = "'1"
            xlSheet.Cells(x + 2, 7) = "'2"
            xlSheet.Cells(x + 2, 8) = "'165701"
            xlSheet.Cells(x + 2, 9) = "'621-R"
            xlSheet.Cells(x + 2, 10) = "'染色-R"
            xlSheet.Cells(x + 2, 12) = "'103"
            xlSheet.Cells(x + 2, 13) = "'曾添梅"
            xlSheet.Cells(x + 2, 14) = "'103"
            xlSheet.Cells(x + 2, 15) = "'曾添梅"
            ' xlSheet.Cells(x + 2, 15) = "'85"




            For i = 1 To xlApp.Worksheets.Count

                If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then
                    xlSheet2 = xlApp.Workbooks(1).Worksheets(i)
                    xlSheet2.Activate()
                    Exit For
                End If

            Next





            sql = "SELECT  CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源,  DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end  AS 出库重, SUM(chem) AS 化工成本  FROM      (SELECT  TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10)                                       + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1)                                       = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL                                       THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL                                       THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL   THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL  THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1,                                       rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL                                       THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL                                       THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE,                                       CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd,                                       CASE WHEN rt_shaving_out.card1 IS NULL AND                                       p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND                                       p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%')                                       THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%'                                       THEN N'美国 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源1,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source                                       LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%')                                       THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source                                       LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN                                       N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE                                       N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ'                                       WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%'                                       THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source                                       LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码,   rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight)  / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice,  RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL                                       THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem                   FROM       RT_SYS_CHEM_OUT INNER JOIN     rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN                                          (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2,                                                               W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff,                                                               gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as 鼓号                                          FROM       RT_RETAN_RECORD_D                                           WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN                                      rt_wetblue_name RIGHT OUTER JOIN     rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON    p.CARD = rt_shaving_out.card1
                   GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code,                                       rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source,                                       rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT                   ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd,                    备注, 皮源1, 代码"






            Dim sql1 As String

            Dim my_type As String

            Dim serial As Integer = 1
            For xx As Integer = 1 To 5

                Select Case xx
                    Case 1
                        sql1 = ""

                    Case 2
                        sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%a级%') and (source not like N'%b级%') and (source not like N'%C级%') and (type<>N'回鼓') and NOT(color like N'黑色%'  ) order by date"
                        my_type = "--改色--"
                    Case 3
                        sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%')  order by date"
                        my_type = "--套染--"
                    Case 4
                        sql1 = sql + " having type='回鼓'  order by date"
                        my_type = "--回鼓--"
                    Case 5

                        sql1 = ""


                End Select


                cmd.Connection = conn
                dt2 = New DataTable
                cmd.CommandText = sql1
                da.SelectCommand = cmd

                If sql1 <> "" Then
                    da.Fill(dt2)
                End If










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

                    xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial  '行号
                    xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb


                    'xlSheet2.Cells(yy + 1 + y, 5) = "112." & dt2.Rows(y).Item("皮源代码")
                    'xlSheet2.Cells(yy + 1 + y, 6) = dt2.Rows(y).Item("皮源1")

                    '  xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价")

                    If my_type = "--回鼓--" Then

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 6) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 7) = "二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 6) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 23) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("出库重") = 0 Then

                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                            '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")


                        Else

                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                            '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")


                        End If


                    ElseIf my_type = "--改色--" Then
                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 6) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 7) = "二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 6) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 23) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                        '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                    ElseIf my_type = "--套染--" Then
                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 6) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 7) = "二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 6) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 23) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                            '      xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                        Else
                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                            '    xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                        End If



                    ElseIf my_type = "--蓝皮下鼓(已磨皮)--" Then
                        'If dt2.Rows(y).Item("类别") = "反毛" Then
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "112.FM"
                        '    xlSheet2.Cells(yy + 1 + y, 7) = "二层皮"

                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "112.PU"
                        '    xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮"
                        'End If
                        'xlSheet2.Cells(yy + 1 + y, 23) = "112 " & dt2.Rows(y).Item("皮源1")   '批号

                        'xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                        ''  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                    ElseIf my_type = "--蓝皮下鼓无对应--" Then

                        'If dt2.Rows(y).Item("类别") = "反毛" Then
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "112.FM"
                        '    xlSheet2.Cells(yy + 1 + y, 7) = "二层皮"

                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "112.PU"
                        '    xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮"
                        'End If
                        'xlSheet2.Cells(yy + 1 + y, 23) = "112 " & dt2.Rows(y).Item("皮源1")   '批号

                        'xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                        ''  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")


                    End If

                    xlSheet2.Cells(yy + 1 + y, 28) = "'--" & dt2.Rows(y).Item("日期") & my_type


                    xlSheet2.Cells(yy + 1 + y, 55) = dt2.Rows(y).Item("鼓号") & my_type   '备注
                    ' xlSheet2.Cells(yy + 1 + y, 32) = dt2.Rows(y).Item("日期")




                    xlSheet2.Cells(yy + 1 + y, 35) = "'313"
                    xlSheet2.Cells(yy + 1 + y, 36) = "涂饰在制品"








                    xlSheet2.Cells(yy + 1 + y, 53) = dt2.Rows(y).Item("类别")
                    xlSheet2.Cells(yy + 1 + y, 52) = "'" & dt2.Rows(y).Item("客户")
                    ' xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员")
                    xlSheet2.Cells(yy + 1 + y, 54) = dt2.Rows(y).Item("皮源")
                    xlSheet2.Cells(yy + 1 + y, 56) = dt2.Rows(y).Item("颜色")
                    xlSheet2.Cells(yy + 1 + y, 57) = dt2.Rows(y).Item("厚度")
                    xlSheet2.Cells(yy + 1 + y, 58) = dt2.Rows(y).Item("卡号")
                    xlSheet2.Cells(yy + 1 + y, 61) = dt2.Rows(y).Item("选级")
                    '  xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本")

                    xlSheet2.Cells(yy + 1 + y, 60) = dt2.Rows(y).Item("出库卡")


                    my_total_no = yy + 1 + y


                    serial = serial + 1

                Next

                yy = yy + dt2.Rows.Count


                aa = aa + 1
                my_x = my_x + 1

            Next
            bb = bb + 1
            final_no = x
        Next








        sql = "SELECT  rt_TM240630.date, rt_TM240630.no, rt_TM240630.card, rt_TM240630.type, rt_TM240630.word, rt_TM240630.cust, rt_TM240630.color, 
               rt_TM240630.weight_ini, rt_TM240630.tech, rt_TM240630.source, rt_TM240630.thick, rt_TM240630.word1, CAST(rt_TM240630.weight / 2 AS numeric(10, 1)) 
               AS weight, rt_TM240630.commentB, rt_TM240630.commentC, rt_TM240630.commentD, rt_TM240630.commentE, rt_TM240630.serial, rt_TM240630.comment,CASE WHEN rt_tm240630.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN rt_tm240630.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN rt_tm240630.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (rt_tm240630.source LIKE N'%巴西%' OR                                      rt_tm240630.source LIKE N'%bx%' or rt_tm240630.source LIKE N'%南美%')                                       THEN N'巴西 二层' WHEN rt_tm240630.source LIKE N'%智利%' THEN N'智利 二层' WHEN rt_tm240630.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN rt_tm240630.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN rt_tm240630.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       rt_tm240630.source LIKE N'%法国%' THEN N'法国 二层' WHEN rt_tm240630.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN rt_tm240630.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN rt_tm240630.source LIKE N'%日本%' THEN N'日本 二层' WHEN rt_tm240630.source LIKE N'%美国%'                                       THEN N'美国 二层' WHEN rt_tm240630.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN rt_tm240630.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN rt_tm240630.source LIKE N'%英国%' THEN N'英国 二层' WHEN rt_tm240630.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN rt_tm240630.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN rt_tm240630.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       rt_tm240630.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN rt_tm240630.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源, 
               rt_TM240630.source1, rt_TM240630.code, z.date_buff
FROM     rt_TM240630 LEFT OUTER JOIN
                   (SELECT  card AS card_buff, MIN(date) AS date_buff
                   FROM     Rt_buff_daily
                   GROUP BY card) AS z ON rt_TM240630.card = z.card_buff
WHERE   (rt_TM240630.commentE LIKE N'%水场%' and rt_TM240630.date='2024/7/31') AND (z.date_buff < '2024/9/1') AND (z.date_buff > '2024/7/31')"  '----240630期初 
        dt = New DataTable

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


        Dim final_no1 As Integer

        final_no1 = final_no

        For dd As Integer = 0 To dt.Rows.Count - 1
            Dim serial1 As Integer = 1

            xlSheet.Cells(dd + final_no1 + 3, 1) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff"))
            xlSheet.Cells(dd + final_no1 + 3, 2) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff"))
            xlSheet.Cells(dd + final_no1 + 3, 3) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 4) = "'" & my_no & bb
            xlSheet.Cells(dd + final_no1 + 3, 5) = "'zhang"
            xlSheet.Cells(dd + final_no1 + 3, 6) = "'1"
            xlSheet.Cells(dd + final_no1 + 3, 7) = "'2"
            xlSheet.Cells(dd + final_no1 + 3, 8) = "'165701"
            xlSheet.Cells(dd + final_no1 + 3, 9) = "'622-R"
            xlSheet.Cells(dd + final_no1 + 3, 10) = "'煎板、真空、挤水-R"
            xlSheet.Cells(dd + final_no1 + 3, 12) = "'103"
            xlSheet.Cells(dd + final_no1 + 3, 13) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 14) = "'103"
            xlSheet.Cells(dd + final_no1 + 3, 15) = "'曾添梅"




            xlSheet2.Cells(yy + 1 + dd, 2) = "'" & serial1  '行号
            xlSheet2.Cells(yy + 1 + dd, 3) = "'" & my_no & bb


            If dt.Rows(dd).Item("type") = "反绒" Then
                xlSheet2.Cells(yy + 1 + dd, 6) = "313.FM"
                xlSheet2.Cells(yy + 1 + dd, 7) = "二层皮"
                xlSheet2.Cells(yy + 1 + dd, 23) = "313 " & dt.Rows(dd).Item("皮源")   '批号

            Else
                xlSheet2.Cells(yy + 1 + dd, 6) = "313.PU"
                xlSheet2.Cells(yy + 1 + dd, 7) = "贴膜皮"
                xlSheet2.Cells(yy + 1 + dd, 23) = "313 " & dt.Rows(dd).Item("皮源")    '批号
            End If


            xlSheet2.Cells(yy + 1 + dd, 24) = dt.Rows(dd).Item("weight")
            xlSheet2.Cells(yy + 1 + dd, 28) = "'-240731盘点-" & dt.Rows(dd).Item("date_buff") & " 磨皮--"
            ' xlSheet2.Cells(yy + 1 + dd, 47) = dt.Rows(dd).Item("date_buff")
            xlSheet2.Cells(yy + 1 + dd, 35) = "'313"
            xlSheet2.Cells(yy + 1 + dd, 36) = "'涂饰在制品"
            xlSheet2.Cells(yy + 1 + dd, 53) = dt.Rows(dd).Item("type")
            xlSheet2.Cells(yy + 1 + dd, 52) = "'" & dt.Rows(dd).Item("cust")
            xlSheet2.Cells(yy + 1 + dd, 61) = dt.Rows(dd).Item("tech")
            xlSheet2.Cells(yy + 1 + dd, 54) = dt.Rows(dd).Item("source")
            xlSheet2.Cells(yy + 1 + dd, 56) = dt.Rows(dd).Item("color")
            xlSheet2.Cells(yy + 1 + dd, 57) = dt.Rows(dd).Item("thick")
            xlSheet2.Cells(yy + 1 + dd, 58) = dt.Rows(dd).Item("card")

            final_no += 1
            bb = bb + 1
        Next

        my_total_no = my_total_no + dt.Rows.Count
        'For dd As Integer = 0 To dt.Rows.Count - 1




        xlSheet2.Range("A2:A" & my_total_no).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & my_total_no).FillDown()
        xlSheet2.Range("I2:I" & my_total_no).FillDown()
        xlSheet2.Range("J2:J" & my_total_no).FillDown()
        xlSheet2.Range("K2:V" & my_total_no).FillDown()

        ' xlSheet.Range("A2:A" & final_no).FillDown()
        xlSheet.Range("R2:V" & final_no).FillDown()

















        MsgBox("")
    End Sub

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



        Dim aa As String
        Dim bb As String
        Dim my_card As String = ""
        Dim my_date As Date

        aa = TextBox11.Text
        bb = aa


        If aa = "" Then
            MsgBox("请输入起始单号")
            Exit Sub
        End If



        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 xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet




        Dim file_name As String = ""
        Dim file_name1 As String = ""
        Dim my_weight As Double = 0
        Dim a As Integer = 0
        Dim my_count As Integer = 0
        Dim my_x As Integer = 0



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


        xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\313入库(蓝皮下鼓及水场在制).xls",, True)

        xlBook.Activate()

        Dim dt2 As New DataTable


        For i = 1 To xlApp.Worksheets.Count

            If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
                xlSheet = xlApp.Workbooks(1).Worksheets(i)
                xlSheet.Activate()
                Exit For
            End If


        Next


        Dim my_no As String = "CPRK0"
        Dim yy As Integer = 1
        Dim x As Integer = 0


        'sql = ""   ''----下鼓


        'Dim dt_out As DataTable

        'cmd.CommandText = sql

        'dt_out = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt_out)

        Dim my_total_no As Integer = 0

        Dim dt_retan As New DataTable

        sql = "SELECT  date FROM       RT_RETAN_RECORD_D
                                           WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_retan)


        Dim final_no As Integer

        For x = 0 To dt_retan.Rows.Count - 1          '-----------loop

            xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 3) = "'曾添梅"
            xlSheet.Cells(x + 2, 4) = "'" & my_no & bb
            xlSheet.Cells(x + 2, 5) = "'zhang"
            xlSheet.Cells(x + 2, 6) = "'1"
            xlSheet.Cells(x + 2, 7) = "'2"
            xlSheet.Cells(x + 2, 8) = "'165701"

            'If x = 1 Or x = 2 Then

            '    xlSheet.Cells(x + 2, 9) = "'607-R"
            '    xlSheet.Cells(x + 2, 10) = "'挑选等级-R"


            'Else
            '    xlSheet.Cells(x + 2, 9) = "'621-R"
            '    xlSheet.Cells(x + 2, 10) = "'染色-R"



            'End If


            xlSheet.Cells(x + 2, 11) = "'103"
            xlSheet.Cells(x + 2, 12) = "'曾添梅"
            xlSheet.Cells(x + 2, 13) = "'103"
            xlSheet.Cells(x + 2, 14) = "'曾添梅"
            xlSheet.Cells(x + 2, 15) = "'85"




            For i = 1 To xlApp.Worksheets.Count

                If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then
                    xlSheet2 = xlApp.Workbooks(1).Worksheets(i)
                    xlSheet2.Activate()
                    Exit For
                End If

            Next





            sql = "SELECT  CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源,  DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end  AS 出库重, SUM(chem) AS 化工成本, cast(SUM(chem) / AVG(投鼓重) / 6 as numeric(10,2)) AS 每尺化工 FROM      (SELECT  TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10)                                       + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1)                                       = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL                                       THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL                                       THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL                                       THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL                                       THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1,                                       rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL                                       THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL                                       THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE,                                       CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd,                                       CASE WHEN rt_shaving_out.card1 IS NULL AND                                       p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND                                       p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%')                                       THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%'                                       THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源1,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source                                       LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%')                                       THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source                                       LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN                                       N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE                                       N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ'                                       WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%'                                       THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source                                       LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码,   rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight)  / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice,  RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL                                       THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem                   FROM       RT_SYS_CHEM_OUT INNER JOIN     rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN                                          (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2,                                                               W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff,                                                               gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as '鼓号'                                           FROM       RT_RETAN_RECORD_D                                           WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN                                      rt_wetblue_name RIGHT OUTER JOIN     rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON    p.CARD = rt_shaving_out.card1
                   GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code,                                       rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source,                                       rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT                   ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd,                    备注, 皮源1, 代码"






            Dim sql1 As String

            Dim my_type As String

            Dim serial As Integer = 1
            For xx As Integer = 1 To 5

                Select Case xx
                    Case 1
                        sql1 = "SELECT  y.卡号, y.鼓号, y.类别, y.类别1, y.生产重, y.下鼓重, y.单价, y.code, y.选级, y.出库卡, y.客户, y.颜色, y.皮源, y.日期, y.date_buff, y.厚度, y.技术员, y.皮源1, y.代码, 
               y.出库重, y.化工成本, y.每尺化工, rt_TM240630.card
FROM     (SELECT  s.CARD AS 卡号, s.鼓号, s.类别, s.type AS 类别1, CASE WHEN AVG(下鼓重) IS NULL THEN AVG(挑级仓出库重) ELSE AVG(下鼓重) END AS 生产重, 
                               CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, 
                               s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期, zz.date_buff, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, 
                               CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE AVG(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本, CAST(SUM(s.chem) 
                               / AVG(s.投鼓重) / 6 AS numeric(10, 2)) AS 每尺化工
               FROM      (SELECT  TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 
                                               10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) 
                                               = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, 
                                               CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL 
                                               THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL 
                                               THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL 
                                               THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, 
                                               p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, 
                                               CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, 
                                               CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) 
                                               = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) 
                                               = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, 
                                               CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND 
                                               p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, 
                                               CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%'
                                                THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR
                                               p.source LIKE N'%bx%' OR
                                               p.source LIKE N'%南美%') 
                                               THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE
                                                N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN
                                                p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层'
                                                WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%'
                                                THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source
                                                LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层'
                                                WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, 
                                               CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU'
                                                WHEN (p.source LIKE N'%巴西%' OR
                                               p.source LIKE N'%bx%' OR
                                               p.source LIKE N'%南美%') 
                                               THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN
                                                N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE'
                                                WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN
                                                p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source
                                                LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE
                                                N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, 
                                               CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, 
                                               RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, 
                                               RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL 
                                               THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem
                               FROM      RT_SYS_CHEM_OUT INNER JOIN
                                               rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN
                                                   (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, 
                                                                  source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND 
                                                                  source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, 
                                                                  ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN '中鼓' ELSE '大鼓' END AS 鼓号
                                                   FROM     RT_RETAN_RECORD_D
                                                   WHERE   (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) as p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN
                                               rt_wetblue_name RIGHT OUTER JOIN
                                               rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1
                               GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, 
                                               rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, 
                                               RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN
                                   (SELECT  card AS card_buff, MIN(date) AS date_buff
                                   FROM     Rt_buff_daily  GROUP BY card) AS zz ON s.CARD = zz.card_buff
               GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, 
                               s.dd, s.备注, s.皮源1, s.代码, zz.date_buff
               HAVING   (zz.date_buff IS NOT NULL) AND (AVG(s.挑级仓出库重) IS NOT NULL) AND (s.type = N'打底') AND (s.source NOT LIKE N'%干%')) AS y LEFT OUTER JOIN (SELECT  date, no, card, type, word, cust, color, weight_ini, tech, source, thick, word1, weight, commentB, commentC, commentD, commentE, serial, 
                                  comment, source1, code
                   FROM     rt_TM240630 AS rt_TM240630_1
                   WHERE   (date = '2024/07/31')) AS rt_tm240630  ON y.卡号 = rt_TM240630.card WHERE   (rt_TM240630.card IS NULL)"
                        my_type = "--蓝皮下鼓--"

                    Case 2
                        sql1 = ""
                        'sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%C级%') and (type<>N'回鼓')  order by date"
                        'my_type = "--改色--"
                    Case 3
                        sql1 = ""
                        'sql1 = sql + " having (type=N'套染' or source like N'%C级%') and (source not like N'%brf%')  order by date"
                        'my_type = "--套染--"
                    Case 4
                        sql1 = ""
                        'sql1 = sql + " having type='回鼓'  order by date"
                        'my_type = "--回鼓--"
                    Case 5


                        sql1 = sql + " having  (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date "
                        my_type = "--蓝皮下鼓无对应--"
                        'Case 6
                        '    sql1 = sql + " having 类别=N'其他'  order by date"
                        '    my_type = "--合鼓--"

                End Select



                dt2 = New DataTable
                cmd.CommandText = sql1
                da.SelectCommand = cmd

                If sql1 <> "" Then
                    da.Fill(dt2)
                End If









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

                    xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial  '行号
                    xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb






                    '   xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价")

                    If my_type = "--回鼓--" Then

                        'If dt2.Rows(y).Item("出库重") = 0 Then
                        '    xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        '    '     xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                        '    '     xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                        'End If

                        'xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        'If dt2.Rows(y).Item("类别") = "反毛" Then
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        'End If


                    ElseIf my_type = "--改色--" Then

                        'xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        ''     xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                        'xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        'If dt2.Rows(y).Item("类别") = "反毛" Then
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        'End If


                    ElseIf my_type = "--套染--" Then

                        'If dt2.Rows(y).Item("出库重") = 0 Then
                        '    xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        '    '      xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                        '    xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                        '    '         xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")
                        '    xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        'End If

                        'If dt2.Rows(y).Item("类别") = "反毛" Then
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        'End If



                    ElseIf my_type = "--蓝皮下鼓--" Then

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                        '        xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")


                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If

                    ElseIf my_type = "--蓝皮下鼓无对应--" Then

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        '      xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")


                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If

                    End If

                    xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号
                    xlSheet2.Cells(yy + 1 + y, 18) = dt2.Rows(y).Item("鼓号") & my_type & dt2.Rows(y).Item("日期") & " 磨皮" '备注
                    '    xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期")




                    xlSheet2.Cells(yy + 1 + y, 25) = "'313"
                    xlSheet2.Cells(yy + 1 + y, 26) = "'涂饰在制品"

                    xlSheet.Cells(x + 2, 9) = "'621-R"
                    xlSheet.Cells(x + 2, 10) = "'染色-R"





                    xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别")
                    xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("客户")
                    xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员")
                    xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源")
                    xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("颜色")
                    xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("厚度")
                    xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("卡号")
                    xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级")
                    xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本")

                    xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("出库卡")


                    my_total_no = yy + 1 + y


                    serial = serial + 1

                Next

                yy = yy + dt2.Rows.Count


                aa = aa + 1
                my_x = my_x + 1

            Next
            bb = bb + 1
            final_no = x
        Next




        sql = "SELECT  TOP (200) rt_TM240630.date, rt_TM240630.no, rt_TM240630.card, rt_TM240630.type, rt_TM240630.word, rt_TM240630.cust, rt_TM240630.color, 
               rt_TM240630.weight_ini, rt_TM240630.tech, rt_TM240630.source, rt_TM240630.thick, rt_TM240630.word1, CAST(rt_TM240630.weight / 2 AS numeric(10, 1)) 
               AS weight, rt_TM240630.commentB, rt_TM240630.commentC, rt_TM240630.commentD, rt_TM240630.commentE, rt_TM240630.serial, rt_TM240630.comment, CASE WHEN rt_TM240630.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN rt_TM240630.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN rt_TM240630.source LIKE N'%澳%'
                                                THEN N'澳大利亚 二层' WHEN (rt_TM240630.source LIKE N'%巴西%' OR
                                               rt_TM240630.source LIKE N'%bx%' OR
                                               rt_TM240630.source LIKE N'%南美%') 
                                               THEN N'巴西 二层' WHEN rt_TM240630.source LIKE N'%智利%' THEN N'智利 二层' WHEN rt_TM240630.source LIKE N'%德国%' THEN N'德国 二层' WHEN rt_TM240630.source LIKE
                                                N'%西班牙%' THEN N'西班牙 二层' WHEN rt_TM240630.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN rt_TM240630.source LIKE N'%法国%' THEN N'法国 二层' WHEN
                                                rt_TM240630.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN rt_TM240630.source LIKE N'%APP%' THEN N'美国 二层' WHEN rt_TM240630.source LIKE N'%日本%' THEN N'日本 二层'
                                                WHEN rt_TM240630.source LIKE N'%美国%' THEN N'美国 二层' WHEN rt_TM240630.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN rt_TM240630.source LIKE N'%小皮%'
                                                THEN N'小皮 二层' WHEN rt_TM240630.source LIKE N'%英国%' THEN N'英国 二层' WHEN rt_TM240630.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN rt_TM240630.source
                                                LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN rt_TM240630.source LIKE N'%南非%' THEN N'南非 二层' WHEN rt_TM240630.source LIKE N'%孟加拉%' THEN N'孟加拉 二层'
                                                WHEN rt_TM240630.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1,
               rt_TM240630.source1, rt_TM240630.code, z.date_buff
FROM     rt_TM240630 LEFT OUTER JOIN
                   (SELECT  card AS card_buff, MIN(date) AS date_buff
                   FROM     Rt_buff_daily
                   GROUP BY card) AS z ON rt_TM240630.card = z.card_buff
WHERE   (rt_TM240630.commentE LIKE N'%水场%') AND (z.date_buff > '2024/7/31') and (z.date_buff < '2024/9/1') and rt_TM240630.date='2024/7/31'"  '----240630期初 
        dt = New DataTable

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


        Dim final_no1 As Integer

        final_no1 = final_no

        For dd As Integer = 0 To dt.Rows.Count - 1
            Dim serial1 As Integer = 1

            xlSheet.Cells(dd + final_no1 + 3, 1) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff"))
            xlSheet.Cells(dd + final_no1 + 3, 2) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff"))
            xlSheet.Cells(dd + final_no1 + 3, 3) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 4) = "'" & my_no & bb
            xlSheet.Cells(dd + final_no1 + 3, 5) = "'zhang"
            xlSheet.Cells(dd + final_no1 + 3, 6) = "'1"
            xlSheet.Cells(dd + final_no1 + 3, 7) = "'2"
            xlSheet.Cells(dd + final_no1 + 3, 8) = "'165701"
            xlSheet.Cells(dd + final_no1 + 3, 9) = "'621-R"
            xlSheet.Cells(dd + final_no1 + 3, 10) = "'染色-R"
            xlSheet.Cells(dd + final_no1 + 3, 11) = "'103"
            xlSheet.Cells(dd + final_no1 + 3, 12) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 13) = "'103"
            xlSheet.Cells(dd + final_no1 + 3, 14) = "'曾添梅"



            xlSheet2.Cells(yy + 1 + dd, 2) = "'" & serial1  '行号
            xlSheet2.Cells(yy + 1 + dd, 3) = "'" & my_no & bb


            If dt.Rows(dd).Item("type") = "反绒" Then
                xlSheet2.Cells(yy + 1 + dd, 5) = "313.FM"
                xlSheet2.Cells(yy + 1 + dd, 6) = "二层皮"
                xlSheet2.Cells(yy + 1 + dd, 17) = "313 " & dt.Rows(dd).Item("皮源1")   '批号

            Else
                xlSheet2.Cells(yy + 1 + dd, 5) = "313.PU"
                xlSheet2.Cells(yy + 1 + dd, 6) = "贴膜皮"
                xlSheet2.Cells(yy + 1 + dd, 17) = "313 " & dt.Rows(dd).Item("皮源1")   '批号
            End If


            xlSheet2.Cells(yy + 1 + dd, 14) = dt.Rows(dd).Item("weight")
            xlSheet2.Cells(yy + 1 + dd, 18) = "'-240731盘点-" & dt.Rows(dd).Item("date_buff") & " 磨皮--"
            xlSheet2.Cells(yy + 1 + dd, 47) = dt.Rows(dd).Item("date_buff")
            xlSheet2.Cells(yy + 1 + dd, 25) = "'313"
            xlSheet2.Cells(yy + 1 + dd, 26) = "涂饰在制品"
            xlSheet2.Cells(yy + 1 + dd, 38) = dt.Rows(dd).Item("type")
            xlSheet2.Cells(yy + 1 + dd, 39) = "'" & dt.Rows(dd).Item("cust")
            xlSheet2.Cells(yy + 1 + dd, 40) = dt.Rows(dd).Item("tech")
            xlSheet2.Cells(yy + 1 + dd, 41) = dt.Rows(dd).Item("source")
            xlSheet2.Cells(yy + 1 + dd, 42) = dt.Rows(dd).Item("color")
            xlSheet2.Cells(yy + 1 + dd, 43) = dt.Rows(dd).Item("thick")
            xlSheet2.Cells(yy + 1 + dd, 44) = dt.Rows(dd).Item("card")

            final_no += 1
            bb = bb + 1
        Next

        my_total_no = my_total_no + dt.Rows.Count
        'For dd As Integer = 0 To dt.Rows.Count - 1




        xlSheet2.Range("A2:A" & my_total_no).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & my_total_no).FillDown()

        xlSheet2.Range("J2:J" & my_total_no).FillDown()
        xlSheet2.Range("K2:L" & my_total_no).FillDown()

        ' xlSheet.Range("A2:A" & final_no).FillDown()
        xlSheet.Range("o2:o" & final_no).FillDown()





























        xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("O2:O" & x + 1).FillDown()
        'xlSheet2.Range("P2:P" & x + 1).FillDown()





        MsgBox("")



    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        Dim aa As String
        Dim bb As String
        Dim my_card As String = ""
        Dim my_date As Date

        aa = TextBox9.Text
        bb = aa


        If aa = "" Then
            MsgBox("请输入起始单号")
            Exit Sub
        End If



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

        Dim xlSheet_1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlSheet2_2 As Microsoft.Office.Interop.Excel.Worksheet


        Dim file_name As String = ""
        Dim file_name1 As String = ""
        Dim my_weight As Double = 0
        Dim a As Integer = 0
        Dim my_count As Integer = 0
        Dim my_x As Integer = 0



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

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




        xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\水场调拨至313.xls",, True)

        xlBook.Activate()

        Dim dt2 As New DataTable

        For i = 1 To xlApp.Worksheets.Count

            If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
                xlSheet = xlApp.Workbooks(1).Worksheets(i)
                xlSheet.Activate()
                Exit For
            End If

        Next


        Dim my_no As String = "CHG"
        Dim yy As Integer = 1
        Dim x As Integer = 0


        Dim my_total_no As Integer = 0

        Dim dt_retan As New DataTable

        sql = "SELECT  date FROM       RT_RETAN_RECORD_D
                                           WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_retan)


        Dim final_no As Integer

        For x = 0 To dt_retan.Rows.Count - 1          '-----------loop





            xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 3) = "'曾添梅"
            xlSheet.Cells(x + 2, 4) = "'" & my_no & bb
            xlSheet.Cells(x + 2, 5) = "'zhang"
            xlSheet.Cells(x + 2, 6) = "'41"
            '  xlSheet.Cells(x + 2, 7) = "'2"
            xlSheet.Cells(x + 2, 7) = "'215378"
            ' xlSheet.Cells(x + 2, 9) = "'622-R"
            ' xlSheet.Cells(x + 2, 10) = "'煎板、真空、挤水-R"
            xlSheet.Cells(x + 2, 8) = "'103"
            xlSheet.Cells(x + 2, 9) = "'曾添梅"
            xlSheet.Cells(x + 2, 10) = "'103"
            xlSheet.Cells(x + 2, 11) = "'曾添梅"
            xlSheet.Cells(x + 2, 12) = "'1"

            xlSheet.Cells(x + 2, 18) = "'01"
            xlSheet.Cells(x + 2, 19) = "'成本调拨"
            xlSheet.Cells(x + 2, 20) = "'433"
            ' xlSheet.Cells(x + 2, 15) = "'85"




            For i = 1 To xlApp.Worksheets.Count

                If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then
                    xlSheet2 = xlApp.Workbooks(1).Worksheets(i)
                    xlSheet2.Activate()
                    Exit For
                End If

            Next





            sql = "SELECT  CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源,  DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end  AS 出库重, SUM(chem) AS 化工成本 FROM      (SELECT  TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10)                                       + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1)                                       = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL                                       THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL                                       THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL   THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL  THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1,                                       rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL                                       THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL                                       THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE,                                       CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd,                                       CASE WHEN rt_shaving_out.card1 IS NULL AND                                       p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND                                       p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%')                                       THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%'                                       THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源1,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source                                       LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%')                                       THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source                                       LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN                                       N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE                                       N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ'                                       WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%'                                       THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source                                       LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码,   rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight)  / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice,  RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL                                       THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem                   FROM       RT_SYS_CHEM_OUT INNER JOIN     rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN                                          (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2,                                                               W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff,                                                               gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as '鼓号'                                           FROM       RT_RETAN_RECORD_D                                           WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN                                      rt_wetblue_name RIGHT OUTER JOIN     rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON    p.CARD = rt_shaving_out.card1
                   GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code,  rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source,   rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd,                    备注, 皮源1, 代码"






            Dim sql1 As String

            Dim my_type As String

            Dim serial As Integer = 1
            For xx As Integer = 1 To 5

                Select Case xx
                    Case 1
                        sql1 = "SELECT  s.CARD AS 卡号, s.鼓号, s.类别, s.type AS 类别1, CASE WHEN AVG(下鼓重) IS NULL THEN AVG(挑级仓出库重) ELSE AVG(下鼓重) END AS 生产重,               CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡,                s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期,zz.date_buff, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, CASE WHEN AVG(挑级仓出库重) IS NULL                THEN 0 ELSE AVG(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本, CAST(SUM(s.chem) / AVG(s.投鼓重) / 6 AS numeric(10, 2))                AS 每尺化工 FROM     (SELECT  TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10)                                + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1)                                = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL                                THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT)                                / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL                                THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL                                THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source,                                CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码,                                CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE,                                CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1)                                = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1)                                = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL                                AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注,                                CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%'                                THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR                               p.source LIKE N'%bx%' OR                               p.source LIKE N'%南美%')                                THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%'                                THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE                                N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN                                p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层'                                WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN                                N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE                                N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1,                                CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN                                (p.source LIKE N'%巴西%' OR                               p.source LIKE N'%bx%' OR                               p.source LIKE N'%南美%')                                THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN N'ES'                                WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source                                LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%'                                THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX'                                WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN                                p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重,                                CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重,                                RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice,                                RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL                                THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem               FROM      RT_SYS_CHEM_OUT INNER JOIN                               rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN                                   (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source,                                                   TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND                                                   source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, ee,                                                   ff, gg, hh, Dep, CASE WHEN header IS NULL THEN '中鼓' ELSE '大鼓' END AS 鼓号                                   FROM     RT_RETAN_RECORD_D                                   WHERE   (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN                               rt_wetblue_name RIGHT OUTER JOIN                               rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1               GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name,                                rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd,                                RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN                   (SELECT  card AS card_buff, min(date) AS date_buff                   FROM     Rt_buff_daily                   GROUP BY card) AS zz ON s.CARD = zz.card_buff GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, s.dd,                s.备注, s.皮源1, s.代码, zz.date_buff HAVING  (AVG(s.挑级仓出库重) IS NOT NULL) AND (s.type = N'打底') AND (zz.date_buff IS not NULL) and (source not like N'%干%')"

                        sql1 = "SELECT  y.卡号, y.鼓号, y.类别, y.类别1, y.生产重, y.下鼓重, y.单价, y.code, y.选级, y.出库卡, y.客户, y.颜色, y.皮源, y.日期, y.date_buff, y.厚度, y.技术员, y.皮源1, y.代码, 
               y.出库重, y.化工成本, rt_TM240630.card
FROM     (SELECT  s.CARD AS 卡号, s.鼓号, s.类别, s.type AS 类别1, CASE WHEN AVG(下鼓重) IS NULL THEN AVG(挑级仓出库重) ELSE AVG(下鼓重) END AS 生产重, 
                               CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, 
                               s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期, zz.date_buff, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, 
                               CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE AVG(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本
               FROM      (SELECT  TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 
                                               10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) 
                                               = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, 
                                               CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL 
                                               THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL 
                                               THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL 
                                               THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, 
                                               p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, 
                                               CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, 
                                               CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) 
                                               = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) 
                                               = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, 
                                               CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND 
                                               p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, 
                                               CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%'
                                                THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR
                                               p.source LIKE N'%bx%' OR
                                               p.source LIKE N'%南美%') 
                                               THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE
                                                N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN
                                                p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层'
                                                WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%'
                                                THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source
                                                LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层'
                                                WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, 
                                               CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU'
                                                WHEN (p.source LIKE N'%巴西%' OR
                                               p.source LIKE N'%bx%' OR
                                               p.source LIKE N'%南美%') 
                                               THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN
                                                N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE'
                                                WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN
                                                p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source
                                                LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE
                                                N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, 
                                               CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, 
                                               RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, 
                                               RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL 
                                               THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem
                               FROM      RT_SYS_CHEM_OUT INNER JOIN
                                               rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN
                                                   (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, 
                                                                  source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND 
                                                                  source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, 
                                                                  ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN '中鼓' ELSE '大鼓' END AS 鼓号
                                                   FROM     RT_RETAN_RECORD_D
                                                   WHERE   (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) as p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN
                                               rt_wetblue_name RIGHT OUTER JOIN
                                               rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1
                               GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, 
                                               rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, 
                                               RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN
                                   (SELECT  card AS card_buff, MIN(date) AS date_buff
                                   FROM     Rt_buff_daily  GROUP BY card) AS zz ON s.CARD = zz.card_buff
               GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, 
                               s.dd, s.备注, s.皮源1, s.代码, zz.date_buff
               HAVING   (zz.date_buff IS NOT NULL) AND (AVG(s.挑级仓出库重) IS NOT NULL) AND (s.type = N'打底') AND (s.source NOT LIKE N'%干%')) AS y LEFT OUTER JOIN (SELECT  date, no, card, type, word, cust, color, weight_ini, tech, source, thick, word1, weight, commentB, commentC, commentD, commentE, serial, 
                                  comment, source1, code
                   FROM     rt_TM240630 AS rt_TM240630_1
                   WHERE   (date = '2024/07/31')) AS rt_tm240630 ON y.卡号 = rt_TM240630.card WHERE   (rt_TM240630.card IS NULL)"

                        my_type = "--蓝皮下鼓(已磨皮)--"

                    Case 2
                        sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%a级%') and (source not like N'%b级%') and (source not like N'%C级%') and (type<>N'回鼓') and NOT(color like N'黑色%'  ) order by date"
                        my_type = "--改色--"
                    Case 3
                        sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%')  order by date"
                        my_type = "--套染--"
                    Case 4
                        sql1 = sql + " having type='回鼓'  order by date"
                        my_type = "--回鼓--"
                    Case 5
                        sql1 = sql + " having  (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date "
                        my_type = "--蓝皮下鼓无对应--"
                        'Case 6
                        '    sql1 = sql + " having 类别=N'其他'  order by date"
                        '    my_type = "--合鼓--"

                End Select



                dt2 = New DataTable
                cmd.CommandText = sql1
                da.SelectCommand = cmd
                da.Fill(dt2)








                For y = 0 To dt2.Rows.Count - 1
                    xlSheet2.Cells(yy + 1 + y, 3) = "'0"   '行号
                    xlSheet2.Cells(yy + 1 + y, 5) = "'" & serial  '行号
                    xlSheet2.Cells(yy + 1 + y, 6) = "'" & my_no & bb
                    xlSheet2.Cells(yy + 1 + y, 7) = "'41"


                    If my_type = "--回鼓--" Then

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 8) = "'313.FM"
                            xlSheet2.Cells(yy + 1 + y, 9) = "'二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 8) = "'313.PU"
                            xlSheet2.Cells(yy + 1 + y, 9) = "'贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 21) = "'313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("出库重") = 0 Then

                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                            '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")


                        Else

                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                            '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")


                        End If


                    ElseIf my_type = "--改色--" Then
                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 8) = "'313.FM"
                            xlSheet2.Cells(yy + 1 + y, 9) = "'二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 8) = "'313.PU"
                            xlSheet2.Cells(yy + 1 + y, 9) = "'贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 21) = "'313 " & dt2.Rows(y).Item("皮源1")   '批号

                        xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                        '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                    ElseIf my_type = "--套染--" Then
                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 8) = "'313.FM"
                            xlSheet2.Cells(yy + 1 + y, 9) = "'二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 8) = "'313.PU"
                            xlSheet2.Cells(yy + 1 + y, 9) = "'贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 21) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                            '      xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")

                        Else
                            xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                            '    xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                        End If



                    ElseIf my_type = "--蓝皮下鼓(已磨皮)--" Then
                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 8) = "'112.FM"
                            xlSheet2.Cells(yy + 1 + y, 9) = "'二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 8) = "'112.PU"
                            xlSheet2.Cells(yy + 1 + y, 9) = "'贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 21) = "'112 " & dt2.Rows(y).Item("皮源1")   '批号

                        xlSheet2.Cells(yy + 1 + y, 23) = dt2.Rows(y).Item("出库重")
                        xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重")
                        '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")

                    ElseIf my_type = "--蓝皮下鼓无对应--" Then

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 8) = "'112.FM"
                            xlSheet2.Cells(yy + 1 + y, 9) = "二层皮"

                        Else
                            xlSheet2.Cells(yy + 1 + y, 8) = "'112.PU"
                            xlSheet2.Cells(yy + 1 + y, 9) = "贴膜皮"
                        End If
                        xlSheet2.Cells(yy + 1 + y, 21) = "'112 " & dt2.Rows(y).Item("皮源1")   '批号

                        xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重")
                        '  xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")


                    End If

                    xlSheet2.Cells(yy + 1 + y, 30) = "'--" & dt2.Rows(y).Item("日期") & " 磨皮--"


                    xlSheet2.Cells(yy + 1 + y, 58) = dt2.Rows(y).Item("鼓号") & my_type   '备注
                    '    xlSheet2.Cells(yy + 1 + y, 32) = dt2.Rows(y).Item("日期")


                    Select Case xx
                        Case 1
                            xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.01"
                            xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/蓝皮下鼓"


                        Case 2
                            xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.04"
                            xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/皮胚改色"

                        Case 3
                            xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.02"
                            xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/皮胚套染"

                        Case 4
                            xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.03"
                            xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/皮胚回鼓"

                        Case 5

                            xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.01"
                            xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/蓝皮下鼓"


                    End Select

                    xlSheet2.Cells(yy + 1 + y, 40) = "'313"
                    xlSheet2.Cells(yy + 1 + y, 41) = "'涂饰在制品"


                    xlSheet2.Cells(yy + 1 + y, 57) = dt2.Rows(y).Item("类别")
                    xlSheet2.Cells(yy + 1 + y, 56) = "'" & dt2.Rows(y).Item("客户")
                    xlSheet2.Cells(yy + 1 + y, 66) = dt2.Rows(y).Item("技术员")
                    xlSheet2.Cells(yy + 1 + y, 67) = dt2.Rows(y).Item("皮源")
                    xlSheet2.Cells(yy + 1 + y, 61) = dt2.Rows(y).Item("颜色")
                    xlSheet2.Cells(yy + 1 + y, 62) = dt2.Rows(y).Item("厚度")
                    xlSheet2.Cells(yy + 1 + y, 59) = dt2.Rows(y).Item("卡号")
                    xlSheet2.Cells(yy + 1 + y, 63) = dt2.Rows(y).Item("选级")
                    '  xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本")

                    xlSheet2.Cells(yy + 1 + y, 68) = dt2.Rows(y).Item("出库卡")


                    my_total_no = yy + 1 + y


                    serial = serial + 1

                Next

                yy = yy + dt2.Rows.Count


                aa = aa + 1
                my_x = my_x + 1

            Next
            bb = bb + 1
            final_no = x
        Next




        sql = "SELECT  TOP (200) rt_TM240630.date, rt_TM240630.no, rt_TM240630.card, rt_TM240630.type, rt_TM240630.word, rt_TM240630.cust, rt_TM240630.color, 
               rt_TM240630.weight_ini, rt_TM240630.tech, rt_TM240630.source, rt_TM240630.thick, rt_TM240630.word1, CAST(rt_TM240630.weight / 2 AS numeric(10, 1)) 
               AS weight, rt_TM240630.commentB, rt_TM240630.commentC, rt_TM240630.commentD, rt_TM240630.commentE, rt_TM240630.serial, rt_TM240630.comment, 
               rt_TM240630.source1, rt_TM240630.code, z.date_buff
FROM     rt_TM240630 LEFT OUTER JOIN
                   (SELECT  card AS card_buff, MIN(date) AS date_buff
                   FROM     Rt_buff_daily
                   GROUP BY card) AS z ON rt_TM240630.card = z.card_buff
WHERE   (rt_TM240630.commentE LIKE N'%水场%') AND (z.date_buff < '2024/8/1')"  '----240630期初 
        dt = New DataTable

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


        Dim final_no1 As Integer

        final_no1 = final_no

        For dd As Integer = 0 To dt.Rows.Count - 1
            Dim serial1 As Integer = 1

            xlSheet.Cells(dd + final_no1 + 3, 1) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff"))
            xlSheet.Cells(dd + final_no1 + 3, 2) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff"))
            xlSheet.Cells(dd + final_no1 + 3, 3) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 4) = "'" & my_no & bb
            xlSheet.Cells(dd + final_no1 + 3, 5) = "'zhang"
            xlSheet.Cells(dd + final_no1 + 3, 6) = "'41"
            xlSheet.Cells(dd + final_no1 + 3, 7) = "'215378"
            ' xlSheet.Cells(dd + final_no1 + 3, 8) = "'165701"
            'xlSheet.Cells(dd + final_no1 + 3, 9) = "'622-R"
            'xlSheet.Cells(dd + final_no1 + 3, 10) = "'煎板、真空、挤水-R"
            xlSheet.Cells(dd + final_no1 + 3, 8) = "'103"
            xlSheet.Cells(dd + final_no1 + 3, 9) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 10) = "'103"
            xlSheet.Cells(dd + final_no1 + 3, 11) = "'曾添梅"
            xlSheet.Cells(dd + final_no1 + 3, 12) = "'1"

            xlSheet.Cells(dd + final_no1 + 3, 18) = "'01"
            xlSheet.Cells(dd + final_no1 + 3, 19) = "'成本调拨"
            xlSheet.Cells(dd + final_no1 + 3, 20) = "'433"

            xlSheet2.Cells(yy + 1 + dd, 3) = "'0"   '行号

            xlSheet2.Cells(yy + 1 + dd, 5) = "'" & serial1  '行号
            xlSheet2.Cells(yy + 1 + dd, 6) = "'" & my_no & bb
            xlSheet2.Cells(yy + 1 + dd, 7) = "'41"


            If dt.Rows(dd).Item("type") = "反绒" Then
                xlSheet2.Cells(yy + 1 + dd, 8) = "313.FM"
                xlSheet2.Cells(yy + 1 + dd, 9) = "二层皮"
                xlSheet2.Cells(yy + 1 + dd, 21) = "313 " & dt.Rows(dd).Item("code")   '批号

            Else
                xlSheet2.Cells(yy + 1 + dd, 8) = "313.PU"
                xlSheet2.Cells(yy + 1 + dd, 9) = "贴膜皮"
                xlSheet2.Cells(yy + 1 + dd, 21) = "313 " & dt.Rows(dd).Item("code")   '批号
            End If

            xlSheet2.Cells(yy + 1 + dd, 23) = "'kg"
            xlSheet2.Cells(yy + 1 + dd, 24) = dt.Rows(dd).Item("weight")
            xlSheet2.Cells(yy + 1 + dd, 30) = "'-240630盘点-" & dt.Rows(dd).Item("date_buff") & " 磨皮--"
            '  xlSheet2.Cells(yy + 1 + dd, 32) = dt.Rows(dd).Item("date_buff")
            xlSheet2.Cells(yy + 1 + dd, 36) = "'213A-R.01"
            xlSheet2.Cells(yy + 1 + dd, 37) = "染色仓/蓝皮下鼓"
            xlSheet2.Cells(yy + 1 + dd, 40) = "'313"
            xlSheet2.Cells(yy + 1 + dd, 41) = "'涂饰在制品"
            xlSheet2.Cells(yy + 1 + dd, 56) = "'" & dt.Rows(dd).Item("cust")
            xlSheet2.Cells(yy + 1 + dd, 57) = dt.Rows(dd).Item("type")

            xlSheet2.Cells(yy + 1 + dd, 66) = dt.Rows(dd).Item("tech")
            xlSheet2.Cells(yy + 1 + dd, 67) = dt.Rows(dd).Item("source")
            xlSheet2.Cells(yy + 1 + dd, 61) = dt.Rows(dd).Item("color")
            xlSheet2.Cells(yy + 1 + dd, 62) = dt.Rows(dd).Item("thick")
            xlSheet2.Cells(yy + 1 + dd, 59) = dt.Rows(dd).Item("card")


            final_no += 1
            bb = bb + 1
        Next

        my_total_no = my_total_no + dt.Rows.Count
        'For dd As Integer = 0 To dt.Rows.Count - 1




        xlSheet2.Range("A2:A" & my_total_no).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & my_total_no).FillDown()
        xlSheet2.Range("I2:I" & my_total_no).FillDown()
        xlSheet2.Range("J2:J" & my_total_no).FillDown()
        xlSheet2.Range("K2:V" & my_total_no).FillDown()

        ' xlSheet.Range("A2:A" & final_no).FillDown()
        xlSheet.Range("R2:V" & final_no).FillDown()






        'xlBook1 = xlApp.Workbooks.Open("D:\K3\k3表格模板\313涂饰在制品入库.xls",, True)

        'xlBook1.Activate()




        'For i = 1 To xlApp.Worksheets.Count

        '    If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
        '        xlSheet = xlApp.Workbooks(1).Worksheets(i)
        '        xlSheet.Activate()
        '        Exit For
        '    End If


        'Next









        MsgBox("")
    End Sub

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




        Dim aa As String
        Dim bb As String
        Dim my_card As String = ""
        Dim my_date As Date

        aa = TextBox8.Text
        bb = aa


        If aa = "" Then
            MsgBox("请输入起始单号")
            Exit Sub
        End If



        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 xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet




        Dim file_name As String = ""
        Dim file_name1 As String = ""
        Dim my_weight As Double = 0
        Dim a As Integer = 0
        Dim my_count As Integer = 0
        Dim my_x As Integer = 0



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


        xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\下鼓染色入库.xls",, True)

        xlBook.Activate()

        Dim dt2 As New DataTable


        For i = 1 To xlApp.Worksheets.Count

            If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
                xlSheet = xlApp.Workbooks(1).Worksheets(i)
                xlSheet.Activate()
                Exit For
            End If


        Next


        Dim my_no As String = "CPRK0"
        Dim yy As Integer = 1
        Dim x As Integer = 0


        'sql = ""   ''----下鼓


        'Dim dt_out As DataTable

        'cmd.CommandText = sql

        'dt_out = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt_out)

        Dim my_total_no As Integer = 0

        Dim dt_retan As New DataTable

        sql = "SELECT  date FROM       RT_RETAN_RECORD_D
                                           WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_retan)




        For x = 0 To dt_retan.Rows.Count - 1          '-----------loop





            xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 3) = "'郑笔成"
            xlSheet.Cells(x + 2, 4) = "'" & my_no & bb
            xlSheet.Cells(x + 2, 5) = "'zhang"
            xlSheet.Cells(x + 2, 6) = "'1"
            xlSheet.Cells(x + 2, 7) = "'2"
            xlSheet.Cells(x + 2, 8) = "'165701"

            'If x = 1 Or x = 2 Then

            '    xlSheet.Cells(x + 2, 9) = "'607-R"
            '    xlSheet.Cells(x + 2, 10) = "'挑选等级-R"


            'Else
            '    xlSheet.Cells(x + 2, 9) = "'621-R"
            '    xlSheet.Cells(x + 2, 10) = "'染色-R"



            'End If


            xlSheet.Cells(x + 2, 11) = "'103"
            xlSheet.Cells(x + 2, 12) = "'曾添梅"
            xlSheet.Cells(x + 2, 13) = "'103"
            xlSheet.Cells(x + 2, 14) = "'曾添梅"
            xlSheet.Cells(x + 2, 15) = "'85"




            For i = 1 To xlApp.Worksheets.Count

                If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then
                    xlSheet2 = xlApp.Workbooks(1).Worksheets(i)
                    xlSheet2.Activate()
                    Exit For
                End If

            Next






            sql = "SELECT  r.卡号, r.卡号1, r.鼓号, r.类别, r.类别1, r.下鼓重, r.单价, r.code, r.选级, r.出库卡, r.客户, r.颜色, r.皮源, r.日期, r.厚度, r.技术员, r.皮源1, r.代码, r.出库重, 
               r.化工成本, r.weight, CASE WHEN weight IS NULL then N'本期' else N'前期' END AS 期别, RT_313_bal_price.bal_p, 
               CASE WHEN weight IS NULL  THEN 单价 ELSE bal_p END AS 单价1
FROM     (SELECT  TOP (100) PERCENT s.CARD AS 卡号, s.card2 AS 卡号1, s.鼓号, s.类别, s.type AS 类别1, CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, 
                               CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, 
                               s.DATE AS 日期, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE avg(挑级仓出库重) 
                               END AS 出库重, SUM(s.chem) AS 化工成本, rt_TM240731.weight
               FROM      (SELECT  TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) 
                                               + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 
                                               1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, 
                                               CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, 
                                               CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, 
                                               CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, 
                                               CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, 
                                               rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL 
                                               THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL 
                                               THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) 
                                               = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) 
                                               = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL 
                                               THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND 
                                               p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, 
                                               CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'313 无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'313 阿根廷 二层' WHEN p.source
                                                LIKE N'%澳%' THEN N'313 澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR
                                               p.source LIKE N'%bx%' OR
                                               p.source LIKE N'%南美%') AND (NOT (p.source LIKE N'%巴西美国%')) 
                                               THEN N'313 巴西 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%智利%' THEN N'313 智利 二层' WHEN p.source LIKE N'%德国%' THEN N'313 德国 二层' WHEN
                                                p.source LIKE N'%西班牙%' THEN N'313 西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'313 欧洲 二层' WHEN p.source LIKE N'%法国%'
                                                THEN N'313 法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'313 爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'313 美国 二层'
                                                WHEN p.source LIKE N'%日本%' THEN N'313 日本 二层' WHEN p.source LIKE N'%美国%' THEN N'313 美国 二层' WHEN p.source LIKE N'%新西兰%'
                                                THEN N'313 新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'313 小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'313 英国 二层'
                                                WHEN p.source LIKE N'%墨西哥%' THEN N'313 墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'313 乌拉圭 二层' WHEN p.source
                                                LIKE N'%南非%' THEN N'313 南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'313 孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN
                                                N'313 泰国 二层' ELSE N'313 杂皮 二层' END AS 皮源1, 
                                               CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN
                                                N'AU' WHEN (p.source LIKE N'%巴西%' OR
                                               p.source LIKE N'%bx%' OR
                                               p.source LIKE N'%南美%') 
                                               THEN N'BR' WHEN p.source LIKE N'%意大利%' THEN N'IT' WHEN p.source LIKE N'%韩国%' THEN N'KO' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%'
                                                THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%'
                                                THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%'
                                                THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%'
                                                THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%'
                                                THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, 
                                               rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) 
                                               / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, 
                                               rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL 
                                               THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem
                               FROM      RT_SYS_CHEM_OUT INNER JOIN
                                               rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN
                                                   (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, 
                                                                  A4, source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND 
                                                                  source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, 
                                                                  COMc, dd, ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN N'中鼓' ELSE N'大鼓' END AS 鼓号
                                                   FROM     RT_RETAN_RECORD_D
                                                   WHERE   (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN
                                               rt_wetblue_name RIGHT OUTER JOIN
                                               rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1
                               GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, 
                                               rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, 
                                               RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) 
                               AS s LEFT OUTER JOIN
                               rt_TM240731 ON s.CARD = rt_TM240731.card
               GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, 
                               s.厚度, s.dd, s.备注, s.皮源1, s.代码, rt_TM240731.weight) AS r LEFT OUTER JOIN
               RT_313_bal_price ON r.类别 = RT_313_bal_price.name AND r.皮源1 = RT_313_bal_price.lot
GROUP BY r.卡号, r.卡号1, r.鼓号, r.类别, r.类别1, r.下鼓重, r.单价, r.code, r.选级, r.出库卡, r.客户, r.颜色, r.皮源, r.日期, r.厚度, r.技术员, r.皮源1, r.代码, r.出库重, 
               r.化工成本, r.weight, RT_313_bal_price.bal_p"






            Dim sql1 As String

            Dim my_type As String

            Dim serial As Integer = 1
            For xx As Integer = 1 To 5

                Select Case xx
                    Case 1
                        sql1 = sql + " having (出库重<>0) and 类别1 =N'打底' and 皮源 not like N'%干%' "
                        my_type = "--蓝皮下鼓--"


                        'sql1 = "SELECT  zz.卡号, zz.卡号1, zz.鼓号, zz.类别, zz.类别1, zz.下鼓重, zz.单价, zz.code, zz.选级, zz.出库卡, zz.客户, zz.颜色, zz.皮源, zz.日期, zz.厚度, zz.技术员, zz.皮源1, zz.代码, zz.出库重, zz.化工成本, rt_TM240731.weight FROM     (" + sql1 + ") AS zz LEFT OUTER JOIN  rt_TM240731 ON zz.卡号 = rt_TM240731.card WHERE   (rt_TM240731.weight IS NULL)"



                    Case 2
                        sql1 = sql + " having (皮源 like N'%干%' or 皮源 like N'%蓝皮完%' ) and (皮源 not like N'%a级%') and (皮源 not like N'%b级%') and (皮源 not like N'%C级%') and (类别1<>N'回鼓')  "
                        my_type = "--改色--"
                    Case 3
                        sql1 = sql + " having (类别1=N'套染' or 皮源 like N'%A级%' or 皮源 like N'%B级%' or 皮源 like N'%C级%') and (皮源 not like N'%brf%') "
                        my_type = "--套染--"
                    Case 4
                        sql1 = sql + " having 类别1='回鼓' "
                        my_type = "--回鼓--"
                    Case 5
                        sql1 = sql + " having  (出库重 =0) and 类别<>'其他' and 类别1=N'打底' and 皮源 not like N'%干%' and 皮源 not like N'%brf%'  "
                        my_type = "--蓝皮下鼓无对应--"
                        'Case 6
                        '    sql1 = sql + " having 类别=N'其他'  order by date"
                        '    my_type = "--合鼓--"

                End Select



                dt2 = New DataTable
                cmd.CommandText = sql1
                da.SelectCommand = cmd
                da.Fill(dt2)








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

                    xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial  '行号
                    xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb






                    xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价")

                    If my_type = "--回鼓--" Then

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                            xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价1")

                        Else
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                            xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价1")

                        End If

                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If


                    ElseIf my_type = "--改色--" Then

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价1")
                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If


                    ElseIf my_type = "--套染--" Then

                        If dt2.Rows(y).Item("出库重") = 0 Then
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                            xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价1")
                            xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        Else
                            xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                            xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价1")
                            xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        End If

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If



                    ElseIf my_type = "--蓝皮下鼓--" Then

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重")
                        xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价")
                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If

                    ElseIf my_type = "--蓝皮下鼓无对应--" Then

                        xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                        xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                        If dt2.Rows(y).Item("类别") = "反毛" Then
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                            xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        Else
                            xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                            xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        End If

                    End If




                    xlSheet2.Cells(yy + 1 + y, 18) = dt2.Rows(y).Item("鼓号") & my_type & dt2.Rows(y).Item("期别") '备注
                    xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期")


                    Select Case xx
                        Case 1
                            xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01"
                            xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓"

                            xlSheet.Cells(x + 2, 9) = "'607-R"
                            xlSheet.Cells(x + 2, 10) = "'挑选等级-R"

                        Case 2
                            xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.04"
                            xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/皮胚改色"

                            xlSheet.Cells(x + 2, 9) = "'621-R"
                            xlSheet.Cells(x + 2, 10) = "'染色-R"
                            'xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01"
                            'xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓"
                        Case 3
                            xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.02"
                            xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/皮胚套染"

                            xlSheet.Cells(x + 2, 9) = "'621-R"
                            xlSheet.Cells(x + 2, 10) = "'染色-R"
                        Case 4
                            xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.03"
                            xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/皮胚回鼓"

                            xlSheet.Cells(x + 2, 9) = "'621-R"
                            xlSheet.Cells(x + 2, 10) = "'染色-R"
                        Case 5

                            xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01"
                            xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓"

                            xlSheet.Cells(x + 2, 9) = "'607-R"
                            xlSheet.Cells(x + 2, 10) = "'挑选等级-R"

                            'Case 6
                            '    xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.08"
                            '    xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/合鼓中和"

                    End Select





                    'If dt_out.Rows(x).Item("type") = "打底" And IsDBNull(dt_out.Rows(x).Item("card_shaving")) And dt_out.Rows(x).Item("类别") <> "其他" Then

                    'ElseIf dt_out.Rows(x).Item("type") = "打底" And Not (IsDBNull(dt_out.Rows(x).Item("card_shaving"))) Then

                    'ElseIf dt_out.Rows(x).Item("type") = "套染" Then

                    'ElseIf dt_out.Rows(x).Item("type") = "回鼓" Or dt_out.Rows(x).Item("type") = "套色" Then

                    'ElseIf dt_out.Rows(x).Item("类别") = "其他" Then

                    'End If





                    xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别")
                    xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("客户")
                    xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员")
                    xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源")
                    xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("颜色")
                    xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("厚度")
                    xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("卡号")
                    xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级")
                    xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本")

                    xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("出库卡")


                    my_total_no = yy + 1 + y


                    serial = serial + 1

                Next

                yy = yy + dt2.Rows.Count


                aa = aa + 1
                my_x = my_x + 1

            Next
            bb = bb + 1
        Next
        xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("O2:O" & x + 1).FillDown()
        'xlSheet2.Range("P2:P" & x + 1).FillDown()





        MsgBox("")



    End Sub

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







        Dim aa As String
        Dim bb As String
        Dim my_card As String = ""
        Dim my_date As Date

        aa = TextBox13.Text
        bb = aa


        If aa = "" Then
            MsgBox("请输入起始单号")
            Exit Sub
        End If



        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 xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet




        Dim file_name As String = ""
        Dim file_name1 As String = ""
        Dim my_weight As Double = 0
        Dim a As Integer = 0
        Dim my_count As Integer = 0
        Dim my_x As Integer = 0



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


        xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\313入库-削匀皮.xls",, True)

        xlBook.Activate()

        Dim dt2 As New DataTable


        For i = 1 To xlApp.Worksheets.Count

            If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then
                xlSheet = xlApp.Workbooks(1).Worksheets(i)
                xlSheet.Activate()
                Exit For
            End If


        Next


        Dim my_no As String = "CPRK0"
        Dim yy As Integer = 1
        Dim x As Integer = 0


        'sql = ""   ''----下鼓


        'Dim dt_out As DataTable

        'cmd.CommandText = sql

        'dt_out = New DataTable
        'da.SelectCommand = cmd
        'da.Fill(dt_out)

        Dim my_total_no As Integer = 0

        Dim dt_retan As New DataTable

        sql = "SELECT  date FROM  rt_shaving_out
                                           WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt_retan)




        For x = 0 To dt_retan.Rows.Count - 1          '-----------loop





            xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date"))
            xlSheet.Cells(x + 2, 3) = "'曾添梅"
            xlSheet.Cells(x + 2, 4) = "'" & my_no & bb
            xlSheet.Cells(x + 2, 5) = "'zhang"
            xlSheet.Cells(x + 2, 6) = "'1"
            xlSheet.Cells(x + 2, 7) = "'2"
            xlSheet.Cells(x + 2, 8) = "'165701"


            xlSheet.Cells(x + 2, 9) = "'607-R"
            xlSheet.Cells(x + 2, 10) = "'挑选等级-R"



            xlSheet.Cells(x + 2, 11) = "'103"
            xlSheet.Cells(x + 2, 12) = "'曾添梅"
            xlSheet.Cells(x + 2, 13) = "'103"
            xlSheet.Cells(x + 2, 14) = "'曾添梅"
            xlSheet.Cells(x + 2, 15) = "'85"




            For i = 1 To xlApp.Worksheets.Count

                If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then
                    xlSheet2 = xlApp.Workbooks(1).Worksheets(i)
                    xlSheet2.Activate()
                    Exit For
                End If

            Next





            sql = "SELECT  CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源,  DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end  AS 出库重, SUM(chem) AS 化工成本  FROM      (SELECT  TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10)                                       + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1)                                       = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL                                       THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL                                       THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL   THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL  THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1,                                       rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL                                       THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL                                       THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE,                                       CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1)                                       = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd,                                       CASE WHEN rt_shaving_out.card1 IS NULL AND                                       p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND                                       p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'                                       WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR                                      p.source LIKE N'%bx%' or p.source LIKE N'%南美%')                                       THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层'                                       WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN                                       p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source                                       LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%'                                       THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN                                       N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'                                       WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN                                       p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层'                                       END AS 皮源1,                                       CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source                                       LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%')                                       THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source                                       LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN                                       N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE                                       N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ'                                       WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%'                                       THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source                                       LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码,   rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight)  / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice,  RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL                                       THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem                   FROM       RT_SYS_CHEM_OUT INNER JOIN     rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN                                          (SELECT  NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2,                                                               W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff,                                                               gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as 鼓号                                          FROM       RT_RETAN_RECORD_D                                           WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN                                      rt_wetblue_name RIGHT OUTER JOIN     rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON    p.CARD = rt_shaving_out.card1
                   GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code,                                       rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source,                                       rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE,                                       RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT                   ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd,                    备注, 皮源1, 代码"





            Dim sql1 As String

            sql1 = "SELECT  rt_shaving_out.date, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.lot, rt_shaving_out.weight, rt_shaving_out.price, rt_shaving_out.amount, 
               rt_shaving_out.p, rt_shaving_out.card, rt_shaving_out.card1, rt_shaving_out.id, RT_RETAN_RECORD1.source, 
               CASE WHEN RT_RETAN_RECORD1.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%阿根廷%' THEN N'阿根廷 二层'
                WHEN RT_RETAN_RECORD1.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (RT_RETAN_RECORD1.source LIKE N'%巴西%' OR
               RT_RETAN_RECORD1.source LIKE N'%bx%' OR
               RT_RETAN_RECORD1.source LIKE N'%南美%') 
               THEN N'巴西 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%智利%' THEN N'智利 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%德国%' THEN N'德国 二层'
                WHEN RT_RETAN_RECORD1.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN
                RT_RETAN_RECORD1.source LIKE N'%法国%' THEN N'法国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN RT_RETAN_RECORD1.source
                LIKE N'%APP%' THEN N'美国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%日本%' THEN N'日本 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%美国%'
                THEN N'美国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%小皮%' THEN
                N'小皮 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%英国%' THEN N'英国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%墨西哥%' THEN N'墨西哥 二层'
                WHEN RT_RETAN_RECORD1.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%意大利%' THEN N'意大利 二层'
                WHEN RT_RETAN_RECORD1.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%南非%' THEN N'南非 二层' WHEN RT_RETAN_RECORD1.source
                LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, 
               rt_shaving_out.cust, rt_shaving_out.color, rt_shaving_out.thick, RT_RETAN_RECORD1.spec
FROM     rt_shaving_out LEFT OUTER JOIN
                   (SELECT  MIN(date) AS date, source, card,spec
                   FROM     RT_RETAN_RECORD
                   GROUP BY card, source,spec) AS RT_RETAN_RECORD1 ON rt_shaving_out.card1 = RT_RETAN_RECORD1.card
WHERE   (rt_shaving_out.date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')"
            Dim my_type As String


            my_type = "--蓝皮下鼓--"


            Dim serial As Integer = 1
            'For xx As Integer = 1 To 5

            '    Select Case xx
            '        Case 1
            '            sql1 = ""
            '            'sql1 = sql + " having (avg(挑级仓出库重)) is not null and type =N'打底' order by date"
            '            'my_type = "--蓝皮下鼓--"

            '        Case 2
            '            sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%a级%') and (source not like N'%b级%') and (source not like N'%C级%') and (type<>N'回鼓') and NOT(color like N'黑色%'  ) order by date"
            '            my_type = "--改色--"
            '        Case 3
            '            sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%')  order by date"
            '            my_type = "--套染--"
            '        Case 4
            '            sql1 = sql + " having type='回鼓'  order by date"
            '            my_type = "--回鼓--"
            '        Case 5
            '            sql1 = ""
            '            'sql1 = sql + " having  (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date "
            '            'my_type = "--蓝皮下鼓无对应--"
            '            'Case 6
            '            '    sql1 = sql + " having 类别=N'其他'  order by date"
            '            '    my_type = "--合鼓--"

            '    End Select


            cmd.Connection = conn


                dt2 = New DataTable

                cmd.CommandText = sql1
                da.SelectCommand = cmd

                If sql1 <> "" Then
                    da.Fill(dt2)

                End If








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

                    xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial  '行号
                    xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb






                xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("price")


                If my_type = "--蓝皮下鼓--" Then

                    xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("weight")
                    xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("amount")
                    xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1")   '批号

                    If dt2.Rows(y).Item("spec") = "反绒" Then
                        xlSheet2.Cells(yy + 1 + y, 5) = "313.FM"
                        xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                    Else
                        xlSheet2.Cells(yy + 1 + y, 5) = "313.PU"
                        xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                    End If

                ElseIf my_type = "--蓝皮下鼓无对应--" Then

                        'xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重")
                        'xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价")
                        'xlSheet2.Cells(yy + 1 + y, 17) = "112 " & dt2.Rows(y).Item("皮源1")   '批号

                        'If dt2.Rows(y).Item("类别") = "反毛" Then
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "112.FM"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "二层皮"
                        'Else
                        '    xlSheet2.Cells(yy + 1 + y, 5) = "112.PU"
                        '    xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮"
                        'End If

                    End If




                xlSheet2.Cells(yy + 1 + y, 18) = my_type   '备注
                '  xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期")


                xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01"
                xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓"








                ' xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别")
                xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("cust")
                'xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员")
                xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源1")
                xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("color")
                xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("thick")
                xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("card1")
                ' xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级")
                'xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本")

                xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("card")


                my_total_no = yy + 1 + y


                    serial = serial + 1

                Next

                yy = yy + dt2.Rows.Count


                aa = aa + 1
                my_x = my_x + 1
            bb = bb + 1
        Next


        xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("B2:B" & x + 1).FillDown()
        xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown()
        xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown()
        'xlSheet2.Range("O2:O" & x + 1).FillDown()
        'xlSheet2.Range("P2:P" & x + 1).FillDown()





        MsgBox("")

    End Sub
End Class