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

Imports System.Data.SqlClient
Imports System.Reflection
Imports System.Text.RegularExpressions
Public Class Frm_Ship_detail
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Dim sql As String
    Dim conn As New SqlConnection
    Private dateTimePicker1 As DateTimePicker
    Dim p() As String
    Dim my_cust As String = ""
    Private Sub Frm_Ship_detail_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ListBox1.Items.Add("牛二层皮1")
        ListBox1.Items.Add("牛二层皮2")
        ListBox1.Items.Add("牛二层皮3")
        ListBox1.Items.Add("牛二层皮4")
        ListBox1.Items.Add("PU牛二层皮")

        'Dim type As Type = dgv_detail.GetType()
        'Dim pi As PropertyInfo = type.GetProperty("DoubleBuffered", System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.NonPublic)
        'pi.SetValue(dgv_detail, True, Nothing)


        'pi.SetValue(dgv_sum_cust, True, Nothing)
        'pi.SetValue(dgv_sum_date, True, Nothing)



        conn.ConnectionString = connstring
        conn.Open()

        cmd.Connection = conn


        sql = "select no from rt_bg_main where checked ='False'"
        cmd.CommandText = sql
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader

        Dim tof As Boolean = False
        ListBox2.Items.Clear()

        While dr.Read

            ListBox2.Items.Add(dr("no"))

        End While
        dr.Close()



        'sql = "update rt_ship_detail set cust=N'金威利' where po_no like 'rt%' and cust=N'金威利（美元）'"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()


        'sql = "update rt_ship_detail set cust=N'威弘' where po_no like 'rt%' and cust=N'威弘（美元）'"
        'cmd.CommandText = sql
        'cmd.ExecuteNonQuery()


        sql = "update RT_SHIP_DETAIL set c_weight=cast(qty/11 as numeric(10,1)) where cust like N'金威利%' or cust like N'%威弘%'"

        cmd.CommandText = sql
        cmd.ExecuteNonQuery()


        dgv_detail.SelectionMode = DataGridViewSelectionMode.FullRowSelect


    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click   '-----查询






        Dim DT1 As New DataTable
        sql = "SELECT date as 日期,cust as 客户, po_no as 订单号,color as 颜色,pattern as 纹路,thick as 厚度,cast(qty as numeric(10,1)) as 尺数,ship_no as 送货单号,out_no as 出库单号 from rt_ship_detail where date between '" & dtp1.Value & "' and '" & dtp2.Value & "' and not(no LIKE N'%样%') and not(no LIKE N'%补%') and not(no LIKE N'%送%') and not(cust LIKE N'%现金%') and not(po_no LIKE N'%样%') and not(po_no LIKE N'%送%') and not(po_no LIKE N'%补%') and not(cust LIKE N'%许宝贝%') and not(cust LIKE N'%极路客%') and qty>0 order by date,cust,po_no,color"   '----明細

        sql = "SELECT date as 日期,cust as 客户, po_no as 订单号,color as 颜色,pattern as 纹路,thick as 厚度,cast(qty as numeric(10,1)) as 尺数,card as 流程卡号,ship_no as 送货单号,out_no as 出库单号 from rt_ship_detail where date between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "'  order by date,cust,po_no,color"   '----明細



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


        dgv_detail.DataSource = DT1

        dgv_detail.Columns(0).Width = 90
        dgv_detail.Columns(1).Width = 150
        dgv_detail.Columns(2).Width = 130
        dgv_detail.Columns(3).Width = 130
        dgv_detail.Columns(4).Width = 70
        dgv_detail.Columns(5).Width = 70
        dgv_detail.Columns(6).Width = 80

        DT1 = New DataTable
        sql = "SELECT cust as 客户,cast(sum(qty) as numeric(10,1)) as 尺数 from rt_ship_detail where date between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "'  and not(no LIKE N'%样%') and not(no LIKE N'%补%') and not(po_no LIKE N'%样%') and not(po_no LIKE N'%补%') and not(cust LIKE N'%许宝贝%') and not(cust LIKE N'%现金%') and not(cust LIKE N'%极路客%') and not(no LIKE N'%送%') and not(po_no LIKE N'%送%') group by cust order by cust desc" '---客戶匯總

        sql = "select 选取,客户,尺数,cast(尺数/sum(尺数) over ()*100 as numeric(10,2))  as 比例 from (SELECT          checked AS 选取, CUST AS 客户, CAST(SUM(QTY) AS numeric(10, 1)) AS 尺数 FROM              RT_SHIP_DETAIL where date between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "' GROUP BY   CUST, checked
) as p order by 比例 desc" '---客戶匯總

        sql = "select 选取,客户,尺数,类别,颜色,cast(尺数/sum(尺数) over ()*100 as numeric(10,2))  as 比例 from (
SELECT          选取,客户, 类别,颜色, SUM(尺数) AS 尺数
FROM              (SELECT        checked AS 选取,   CUST AS 客户, CASE WHEN pattern IS NULL THEN '反毛' ELSE 'PU' END AS 类别, case when color like '%黑%' or color like '%black%' then '黑色' else '彩色' end as 颜色,
                                                        CAST(SUM(QTY) AS numeric(10, 1)) AS 尺数
                            FROM               RT_SHIP_DETAIL
                            where date between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "'
                            GROUP BY    CUST, checked, PATTERN,color) AS x
GROUP BY    客户, 选取,类别,颜色) as p order by 类别 desc,比例 desc" '---客戶匯總



        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(DT1)




        Dim myDataRow As DataRow
        myDataRow = DT1.NewRow

        myDataRow(2) = DT1.Compute("sum(尺数)", "")
        myDataRow(1) = "合计"



        DT1.Rows.InsertAt(myDataRow, 0)



        dgv_sum_cust.DataSource = DT1
        dgv_sum_cust.Columns(0).Width = 60
        dgv_sum_cust.Columns(1).Width = 150
        dgv_sum_cust.Columns(2).Width = 80
        dgv_sum_cust.Columns("类别").Width = 60



        '  dgv_sum_cust.RowHeadersVisible = False


        dgv_sum_cust.Rows(dgv_sum_cust.Rows.Count - 2).Cells(2).Style.ForeColor = Color.Red
        dgv_sum_cust.Rows(dgv_sum_cust.Rows.Count - 2).Cells(1).Style.ForeColor = Color.Red
        dgv_sum_cust.AllowUserToAddRows = False

        '        DT1 = New DataTable

        '        sql = "SELECT   日期, 客户, 类别, SUM(尺数) AS 尺数, 重量, 报关日期 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户,  CASE WHEN pattern = '' THEN '' ELSE N'贴膜' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.weight AS 重量, rt_ship_detail_temp.bg_date AS 报关日期   FROM      RT_SHIP_DETAIL LEFT OUTER JOIN      rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND    RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no  WHERE   (RT_SHIP_DETAIL.date between '" & dtp1.Value & "' and '" & dtp2.Value & "') AND (NOT (RT_SHIP_DETAIL.no LIKE N'%样%')) AND (NOT (RT_SHIP_DETAIL.no LIKE N'%补%')) AND (NOT (RT_SHIP_DETAIL.po_no LIKE N'%样%') and not(RT_SHIP_DETAIL.no LIKE N'%送%') and not(po_no LIKE N'%送%') and not(RT_SHIP_DETAIL.cust LIKE N'%现金%')) AND (NOT (RT_SHIP_DETAIL.po_no LIKE N'%补%')) AND (NOT (RT_SHIP_DETAIL.cust LIKE N'%许宝贝%')) AND (NOT (RT_SHIP_DETAIL.cust LIKE N'%极路客%'))  GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, rt_ship_detail_temp.bg_date, RT_SHIP_DETAIL.pattern, rt_ship_detail_temp.weight  ORDER BY 日期 DESC) AS p GROUP BY 日期, 客户, 类别, 重量, 报关日期 ORDER BY 日期 DESC" '---客戶匯總-日期


        '        sql = "SELECT   日期, 客户, 类别, SUM(尺数) AS 尺数, 重量, 报关日期 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户,  CASE WHEN LEN(pattern)>1  THEN N'贴膜' ELSE '' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.weight AS 重量, rt_ship_detail_temp.bg_date AS 报关日期   FROM      RT_SHIP_DETAIL LEFT OUTER JOIN      rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND    RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no  WHERE   (RT_SHIP_DETAIL.date between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "')  GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, rt_ship_detail_temp.bg_date, RT_SHIP_DETAIL.pattern, rt_ship_detail_temp.weight  ORDER BY 日期 DESC) AS p GROUP BY 日期, 客户, 类别, 重量, 报关日期 ORDER BY 日期 DESC" '---客戶匯總-日期


        '        cmd.CommandText = sql

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



        '        Dim myRow As DataRow
        '        myRow = DT1.NewRow



        '        sql = "SELECT   类别, SUM(尺数) AS 尺数
        'FROM      (SELECT   TOP (100) PERCENT 日期, 客户, 类别, SUM(尺数) AS 尺数, 重量, 报关日期
        '                 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户, 
        '                                                  CASE WHEN LEN(pattern)>1  THEN N'贴膜' ELSE '' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) 
        '                                                  AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.weight AS 重量, 
        '                                                  rt_ship_detail_temp.bg_date AS 报关日期
        '                                  FROM      RT_SHIP_DETAIL LEFT OUTER JOIN
        '                                                  rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND 
        '                                                  RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no
        '                                  WHERE   (RT_SHIP_DETAIL.date BETWEEN '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "') 
        '                                  GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, rt_ship_detail_temp.bg_date, 
        '                                                  RT_SHIP_DETAIL.pattern, rt_ship_detail_temp.weight
        '                                  ORDER BY 日期 DESC) AS p
        '                 GROUP BY 日期, 客户, 类别, 重量, 报关日期
        '                 ORDER BY 日期 DESC) AS qq
        'GROUP BY 类别"


        '        cmd.CommandText = sql

        '        Dim dr As SqlDataReader
        '        dr = cmd.ExecuteReader

        '        While dr.Read


        '            If dr("类别") = "贴膜" Then
        '                myRow(4) = dr("尺数")
        '            Else
        '                myRow(3) = dr("尺数")
        '            End If



        '        End While

        '        If IsDBNull(myRow(3)) Then
        '            myRow(3) = 0
        '        End If

        '        If IsDBNull(myRow(4)) Then
        '            myRow(4) = 0
        '        End If

        '        myRow(2) = Math.Round(myRow(3) + myRow(4), 1)




        '        dr.Close()


        '        myRow(1) = "合计"


















        '        DT1.Rows.InsertAt(myRow, 0)







        '        dgv_sum_date.DataSource = DT1

        '        dgv_sum_date.Columns(0).Width = 90
        '        dgv_sum_date.Columns(1).Width = 150
        '        dgv_sum_date.Columns(2).Width = 70


        '        dgv_sum_date.Rows(0).Cells(4).Style.ForeColor = Color.Red
        '        dgv_sum_date.Rows(0).Cells(1).Style.ForeColor = Color.Red
        '        dgv_sum_date.Rows(0).Cells(2).Style.ForeColor = Color.Red
        '        dgv_sum_date.Rows(0).Cells(3).Style.ForeColor = Color.Red



        '        For x As Integer = 1 To DT1.Rows.Count - 1

        '            If (dgv_sum_date.Rows(x).Cells("客户").Value Like "*威*") Then

        '                dgv_sum_date.Rows(x).Cells("重量").Value = Math.Round(dgv_sum_date.Rows(x).Cells("尺数").Value / 10, 1)

        '            End If


        '        Next





    End Sub

    Private Sub dgv_sum_date_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles dgv_sum_date.MouseDoubleClick  '-------滑鼠双擊输入重量






        'Dim dr As SqlDataReader

        'Dim my_type As String = dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(2).Value.ToString

        'If my_type = "贴膜" Then

        '    sql = "select ship_no,out_no from rt_ship_detail where date='" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(0).Value & "' and  cust=N'" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(1).Value & "' and PATTERN IS NOT NULL group by ship_no,out_no"

        'Else
        '    sql = "select ship_no,out_no from rt_ship_detail where date='" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(0).Value & "' and  cust=N'" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(1).Value & "' and pattern IS NULL group by ship_no,out_no"
        'End If
        'cmd.CommandText = sql
        'dr = cmd.ExecuteReader


        'Dim cmd1 As New SqlCommand
        'Dim conn1 As New SqlConnection
        'conn1.ConnectionString = connstring
        'conn1.Open()
        'cmd1.Connection = conn1


        'Dim my_msg As String = ""

        'Dim my_qty As Double = InputBox("请输入重量")

        'While dr.Read

        '    sql = "select ship_no from rt_ship_detail_temp where ship_no='" & dr("ship_no") & "' and out_no='" & dr("out_no") & "'"
        '    cmd1.CommandText = sql


        '    If Len(cmd1.ExecuteScalar) > 0 Then


        '        If my_msg = "" Then
        '            my_msg = MsgBox("资料已经存在，是否覆盖?", vbYesNoCancel)
        '        End If


        '        If my_msg = vbYes Then



        '            sql = "update rt_ship_detail_temp set weight='" & my_qty & "' where ship_no='" & dr("ship_no") & "' and out_no='" & dr("out_no") & "'"
        '            cmd1.CommandText = sql
        '            cmd1.ExecuteNonQuery()




        '            'sql = "insert into rt_ship_detail_temp (ship_no,out_no,weight,cust,date) values('" & dr("ship_no") & "','" & dr("out_no") & "','" & my_qty & "',N'" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(1).Value & "','" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(0).Value & "')"
        '            'cmd1.CommandText = sql
        '            'cmd1.ExecuteNonQuery()


        '        Else



        '            Exit Sub



        '        End If



        '    Else

        '        sql = "insert into rt_ship_detail_temp (ship_no,out_no,weight,cust,date) values('" & dr("ship_no") & "','" & dr("out_no") & "','" & my_qty & "',N'" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(1).Value & "','" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(0).Value & "')"
        '        cmd1.CommandText = sql
        '        cmd1.ExecuteNonQuery()


        '    End If




        '    'sql = "insert into rt_ship_detail_temp (ship_no,out_no,bg_weight) values('" & dr("ship_no") & "','" & dr("out_no") & "','" & my_qty & "')"
        '    'cmd1.CommandText = sql
        '    'cmd1.ExecuteNonQuery()


        'End While
        'dr.Close()







        'MsgBox("录入完成")





        'Dim DT1 As New DataTable

        'sql = "SELECT   p.日期, p.客户, p.类别, SUM(p.尺数) AS 尺数, p.重量 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户,  CASE WHEN pattern = '' THEN '' ELSE N'贴膜' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.weight AS 重量   FROM      RT_SHIP_DETAIL LEFT OUTER JOIN     rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND  RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no   WHERE   (RT_SHIP_DETAIL.date between '" & dtp1.Value & "' and '" & dtp2.Value & "') and not(RT_SHIP_DETAIL.no LIKE N'%样%') and not(RT_SHIP_DETAIL.no LIKE N'%补%') and not(po_no LIKE N'%样%') and not(po_no LIKE N'%补%') and not(RT_SHIP_DETAIL.cust LIKE N'%许宝贝%') and not(RT_SHIP_DETAIL.cust LIKE N'%极路客%') and not(po_no LIKE N'%送%')   GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, RT_SHIP_DETAIL.pattern,  rt_ship_detail_temp.weight ORDER BY 日期 DESC) AS p GROUP BY p.日期, p.客户, p.类别, p.重量 ORDER BY p.日期 DESC"

        'sql = "SELECT   日期, 客户, 类别, SUM(尺数) AS 尺数, 重量, 报关日期 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户,  CASE WHEN LEN(pattern)>1  THEN N'贴膜' ELSE '' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.weight AS 重量, rt_ship_detail_temp.bg_date AS 报关日期   FROM      RT_SHIP_DETAIL LEFT OUTER JOIN      rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND    RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no  WHERE   (RT_SHIP_DETAIL.date between '" & dtp1.Value & "' and '" & dtp2.Value & "')   GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, rt_ship_detail_temp.bg_date, RT_SHIP_DETAIL.pattern, rt_ship_detail_temp.weight  ORDER BY 日期 DESC) AS p GROUP BY 日期, 客户, 类别, 重量, 报关日期 ORDER BY 日期 DESC"



        'cmd.CommandText = sql

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


        '' DT1.Columns.Add("已报关")



        'dgv_sum_date.DataSource = DT1

        'dgv_sum_date.Columns(0).Width = 90
        'dgv_sum_date.Columns(1).Width = 150
        'dgv_sum_date.Columns(2).Width = 70











    End Sub



    Private Sub dgv_sum_cust_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_sum_cust.CellClick



        '        Dim dt1 As New DataTable

        '        sql = "SELECT date as 日期,cust as 客户, po_no as 订单号,color as 颜色,pattern as 纹路,thick as 厚度,cast(qty as numeric(10,1)) as 尺数,ship_no as 送货单号 from rt_ship_detail where date between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "' and cust=N'" & dgv_sum_cust.Rows(dgv_sum_cust.CurrentCell.RowIndex).Cells("客户").Value & "' order by date,cust,po_no,color"
        '        cmd.CommandText = sql

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


        '        dgv_detail.DataSource = dt1


        '        dgv_detail.Columns(0).Width = 90
        '        dgv_detail.Columns(1).Width = 150
        '        dgv_detail.Columns(2).Width = 130
        '        dgv_detail.Columns(3).Width = 130
        '        dgv_detail.Columns(4).Width = 70
        '        dgv_detail.Columns(5).Width = 70
        '        dgv_detail.Columns(6).Width = 80




        '        dt1 = New DataTable
        '        sql = "SELECT date as 日期,cust as 客户,CASE WHEN pattern = '' THEN '' ELSE N'贴膜' END AS 类别,cast(sum(qty) as numeric(10,1)) as 尺数 from rt_ship_detail where date between '" & dtp1.Value & "' and '" & dtp2.Value & "' group by cust,date,pattern order by date desc"


        '        sql = "SELECT   日期, 客户, 类别, SUM(尺数) AS Expr1
        'FROM      (SELECT   TOP (100) PERCENT date AS 日期, cust AS 客户, CASE WHEN pattern = '' THEN '' ELSE N'贴膜' END AS 类别, 
        '                                 CAST(SUM(qty) AS numeric(10, 1)) AS 尺数
        '                 FROM      RT_SHIP_DETAIL
        '                 WHERE   (cust=N'" & dgv_sum_cust.Rows(dgv_sum_cust.CurrentCell.RowIndex).Cells("客户").Value & "' and date between '" & dtp1.Value & "' and '" & dtp2.Value & "')
        '                 GROUP BY cust, date, pattern
        '                 ORDER BY 日期 DESC) AS p
        'GROUP BY 日期, 客户, 类别
        'ORDER BY 日期 DESC"



        '        sql = "SELECT   p.日期, p.客户, p.类别, SUM(p.尺数) AS 尺数, p.重量 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户,  CASE WHEN pattern = '' THEN '' ELSE N'贴膜' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.bg_weight AS 重量   FROM      RT_SHIP_DETAIL LEFT OUTER JOIN     rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND  RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no   WHERE   (RT_SHIP_DETAIL.cust=N'" & dgv_sum_cust.Rows(dgv_sum_cust.CurrentCell.RowIndex).Cells("客户").Value & "' and RT_SHIP_DETAIL.date between '" & dtp1.Value & "' and '" & dtp2.Value & "') and not(RT_SHIP_DETAIL.no LIKE N'%样%') and not(RT_SHIP_DETAIL.no LIKE N'%补%') and not(po_no LIKE N'%样%') and not(po_no LIKE N'%补%') and not(RT_SHIP_DETAIL.cust LIKE N'%许宝贝%') and not(RT_SHIP_DETAIL.cust LIKE N'%极路客%')   GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, RT_SHIP_DETAIL.pattern,  rt_ship_detail_temp.bg_weight ORDER BY 日期 DESC) AS p GROUP BY p.日期, p.客户, p.类别, p.重量 ORDER BY p.日期 DESC"


        '        sql = "SELECT   日期, 客户, 类别, SUM(尺数) AS 尺数, 重量, 报关日期 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户,  CASE WHEN LEN(pattern)>1  THEN N'贴膜' ELSE '' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.weight AS 重量, rt_ship_detail_temp.bg_date AS 报关日期   FROM      RT_SHIP_DETAIL LEFT OUTER JOIN      rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND    RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no  WHERE   (RT_SHIP_DETAIL.cust=N'" & dgv_sum_cust.Rows(dgv_sum_cust.CurrentCell.RowIndex).Cells("客户").Value & "' and RT_SHIP_DETAIL.date between '" & dtp1.Value & "' and '" & dtp2.Value & "')  GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, rt_ship_detail_temp.bg_date, RT_SHIP_DETAIL.pattern, rt_ship_detail_temp.weight  ORDER BY 日期 DESC) AS p GROUP BY 日期, 客户, 类别, 重量, 报关日期 ORDER BY 日期 DESC"







        '        cmd.CommandText = sql

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

        '        Dim myRow As DataRow
        '        myRow = dt1.NewRow

        '        sql = "SELECT   类别, SUM(尺数) AS 尺数
        'FROM      (SELECT   TOP (100) PERCENT 日期, 客户, 类别, SUM(尺数) AS 尺数, 重量, 报关日期
        '                 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户, 
        '                                                  CASE WHEN pattern = '' THEN '' ELSE N'贴膜' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) 
        '                                                  AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.weight AS 重量, 
        '                                                  rt_ship_detail_temp.bg_date AS 报关日期
        '                                  FROM      RT_SHIP_DETAIL LEFT OUTER JOIN
        '                                                  rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND 
        '                                                  RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no
        '                                  WHERE   (RT_SHIP_DETAIL.cust=N'" & dgv_sum_cust.Rows(dgv_sum_cust.CurrentCell.RowIndex).Cells("客户").Value & "' and RT_SHIP_DETAIL.date BETWEEN '" & dtp1.Value & "' and '" & dtp2.Value & "')
        '                                  GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, rt_ship_detail_temp.bg_date, 
        '                                                  RT_SHIP_DETAIL.pattern, rt_ship_detail_temp.weight
        '                                  ORDER BY 日期 DESC) AS p
        '                 GROUP BY 日期, 客户, 类别, 重量, 报关日期
        '                 ORDER BY 日期 DESC) AS qq
        'GROUP BY 类别"
        '        cmd.CommandText = sql

        '        Dim dr As SqlDataReader
        '        dr = cmd.ExecuteReader

        '        While dr.Read


        '            If dr("类别") = "贴膜" Then
        '                myRow(4) = dr("尺数")
        '            Else
        '                myRow(3) = dr("尺数")
        '            End If



        '        End While


        '        If IsDBNull(myRow(3)) Then

        '            myRow(2) = myRow(4)

        '        ElseIf IsDBNull(myRow(4)) Then

        '            myRow(2) = myRow(3)
        '        Else
        '            myRow(2) = myRow(3) + myRow(4)

        '        End If
        '        myRow(1) = "合计"

        '        dr.Close()

        '        'myRow(1) = "反毛"
        '        'myRow(3) = "贴膜"



        '        'myRow(3) = DT1.Compute("sum(重量)", "")
        '        'myRow(2) = "合计"
        '        dt1.Rows.InsertAt(myRow, 0)
















        '        ' dt1.Columns.Add("已报关")



        '        dgv_sum_date.DataSource = dt1

        '        dgv_sum_date.Columns(0).Width = 90
        '        dgv_sum_date.Columns(1).Width = 150
        '        dgv_sum_date.Columns(2).Width = 70

        '        dgv_sum_date.Rows(0).Cells(4).Style.ForeColor = Color.Red
        '        dgv_sum_date.Rows(0).Cells(1).Style.ForeColor = Color.Red
        '        dgv_sum_date.Rows(0).Cells(2).Style.ForeColor = Color.Red
        '        dgv_sum_date.Rows(0).Cells(3).Style.ForeColor = Color.Red


    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click '----出口报关



        ''Dim my_qty As Double = InputBox("请输入重量")

        ''Dim my_price As Double = InputBox("请输入成品单价")
        ''Dim my_price1 As Double = InputBox("请输入加工费")
        ''Dim my_cust As String = InputBox("请输入客户")
        ''Dim des As String = InputBox("请输入目地国")
        ''Dim no As String = InputBox("请输入报关单号")
        '''Dim xno As String = InputBox("请输入手册号")

        '''Dim date1 As Date = InputBox("请输入报关日期")



        ''sql = "insert into rt_bg_out (des,no,xno,name,date,date1,qty,total,fee,comment) values(N'" & des & "','" & no & "','" & ListBox2.Items(ListBox2.SelectedIndex) & "',N'" &
        ''       ListBox1.Items(ListBox1.SelectedIndex) & "','" & dtp3.Value & "','" & dtp3.Value & "','" & my_qty & "','" & my_price * my_qty & "','" & my_price1 * my_qty & "',N'" & my_cust & "')"

        ''cmd.CommandText = sql

        ''cmd.ExecuteScalar()




        ''sql = "select ship_no,out_no from rt_ship_detail where date='" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(0).Value & "' and  cust=N'" & dgv_sum_date.Rows(dgv_sum_date.CurrentCell.RowIndex).Cells(1).Value & "' group by ship_no,out_no"
        ''cmd.CommandText = sql

        ''Dim dr As SqlDataReader

        ''Dim cmd1 As New SqlCommand
        ''Dim conn1 As New SqlConnection
        ''conn1.ConnectionString = connstring
        ''conn1.Open()
        ''cmd1.Connection = conn1

        ''dr = cmd.ExecuteReader


        ''While dr.Read

        ''    sql = "update rt_ship_detail_temp set bg_no='" & no & "',bg_weight='" & my_qty & "',bg_date='" & dtp3.Value & "' where ship_no='" & dr("ship_no") & "' and out_no='" & dr("out_no") & "'"
        ''    cmd1.CommandText = sql
        ''    cmd1.ExecuteNonQuery()

        ''End While
        ''dr.Close()



        ''MsgBox("存档完成")





        ''Dim DT1 As New DataTable


        ''sql = "SELECT   日期, 客户, 类别, SUM(尺数) AS 尺数, 重量, 报关日期 FROM      (SELECT   TOP (100) PERCENT RT_SHIP_DETAIL.date AS 日期, RT_SHIP_DETAIL.cust AS 客户,  CASE WHEN pattern = '' THEN '' ELSE N'贴膜' END AS 类别, CAST(SUM(RT_SHIP_DETAIL.qty) AS numeric(10, 1)) AS 尺数, rt_ship_detail_temp.weight AS 重量, rt_ship_detail_temp.bg_date AS 报关日期   FROM      RT_SHIP_DETAIL LEFT OUTER JOIN      rt_ship_detail_temp ON RT_SHIP_DETAIL.ship_no = rt_ship_detail_temp.ship_no AND    RT_SHIP_DETAIL.out_no = rt_ship_detail_temp.out_no  WHERE   (RT_SHIP_DETAIL.date between '" & dtp1.Value & "' and '" & dtp2.Value & "')  GROUP BY RT_SHIP_DETAIL.cust, RT_SHIP_DETAIL.date, rt_ship_detail_temp.bg_date, RT_SHIP_DETAIL.pattern, rt_ship_detail_temp.weight  ORDER BY 日期 DESC) AS p GROUP BY 日期, 客户, 类别, 重量, 报关日期 ORDER BY 日期 DESC"


        ''cmd.CommandText = sql

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




        ''dgv_sum_date.DataSource = DT1

        ''dgv_sum_date.Columns(0).Width = 90
        ''dgv_sum_date.Columns(1).Width = 150
        ''dgv_sum_date.Columns(2).Width = 70





    End Sub


    Private Sub dgv_sum_date_MouseUp(sender As Object, e As MouseEventArgs) Handles dgv_sum_date.MouseUp  '---dgv加总所选row

        'Dim x As Double = 0
        'Dim y As Double = 0
        'For i As Integer = 0 To dgv_sum_date.SelectedRows.Count - 1

        '    x = x + Math.Round(dgv_sum_date.Rows(dgv_sum_date.SelectedRows.Item(i).Index).Cells("尺数").Value, 1)

        '    If dgv_sum_date.Rows(dgv_sum_date.SelectedRows.Item(i).Index).Cells("重量").Value.ToString = "" Then
        '    Else
        '        y = y + Math.Round(dgv_sum_date.Rows(dgv_sum_date.SelectedRows.Item(i).Index).Cells("重量").Value, 1)
        '    End If

        'Next i

        'TextBox1.Text = dgv_sum_date.SelectedRows.Count

        'TextBox2.Text = x
        'TextBox3.Text = y




    End Sub



    Private Sub dgv_detail_MouseUp(sender As Object, e As MouseEventArgs) Handles dgv_detail.MouseUp



        Dim x As Double = 0
        Dim y As Double = 0
        For i As Integer = 0 To dgv_detail.SelectedRows.Count - 1

            x = x + Math.Round(dgv_detail.Rows(dgv_detail.SelectedRows.Item(i).Index).Cells("尺数").Value, 1)





        Next i

        TextBox6.Text = dgv_detail.SelectedRows.Count

        TextBox5.Text = x





    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click  '---计算




        Dim my_cust1() As String

        Dim cust As String = " where "



        my_cust1 = Split(my_cust, ",")


        For y As Integer = 1 To UBound(my_cust1)


            If y = 1 And UBound(my_cust1) = 1 Then
                cust = "where  (RT_SHIP_DETAIL.date between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "') and (rt_ship_detail.cust=N'" & my_cust1(1) & "'"
            ElseIf y = 1 And y < UBound(my_cust1) Then
                cust = cust & " (RT_SHIP_DETAIL.date between '" & String.Format("{0:d}", dtp1.Value) & "' and '" & String.Format("{0:d}", dtp2.Value) & "') and (rt_ship_detail.cust=N'" & my_cust1(y) & "'"

            Else
                cust = cust & " or rt_ship_detail.cust=N'" & my_cust1(y) & "'"

            End If


        Next


        If Microsoft.VisualBasic.Right(cust, 2) = "')" Then

        Else

            cust = cust & ")"
        End If





        sql = "SELECT date as 日期,cust as 客户, po_no as 订单号,color as 颜色,pattern as 纹路,thick as 厚度,cast(qty as numeric(10,1)) as 尺数,card as 流程卡号,ship_no as 送货单号,out_no as 出库单号 from rt_ship_detail " & cust & "  order by date,cust,po_no,color"   '----明細


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


        dgv_detail.DataSource = dt

        dgv_detail.Columns(0).Width = 90
        dgv_detail.Columns(1).Width = 150
        dgv_detail.Columns(2).Width = 130
        dgv_detail.Columns(3).Width = 130
        dgv_detail.Columns(4).Width = 70
        dgv_detail.Columns(5).Width = 70
        dgv_detail.Columns(6).Width = 80



        sql = "SELECT          p.流程卡号, p.皮源, SUM(p.心皮重) AS 心皮重, RT_WB_OUTPUT_RATE.shaving AS 得革率
FROM              (SELECT          TOP (100) PERCENT RT_SHIP_DETAIL.DATE AS 日期, RT_SHIP_DETAIL.CUST AS 客户, 
                                                        RT_SHIP_DETAIL.PO_NO AS 订单号, RT_SHIP_DETAIL.COLOR AS 颜色, 
                                                        RT_SHIP_DETAIL.PATTERN AS 纹路, RT_SHIP_DETAIL.THICK AS 厚度, 
                                                        CAST(RT_SHIP_DETAIL.QTY AS numeric(10, 1)) AS 尺数, RT_SHIP_DETAIL.CARD AS 流程卡号, 
                                                        RT_SHIP_DETAIL.SHIP_NO AS 送货单号, RT_SHIP_DETAIL.OUT_NO AS 出库单号, 
                                                        RT_RETAN_RECORD.source AS 皮源, CAST(RT_SHIP_DETAIL.QTY / 6 AS numeric(10, 1)) 
                                                        AS 心皮重
                            FROM               RT_SHIP_DETAIL LEFT OUTER JOIN
                                                        RT_RETAN_RECORD ON substring(RT_SHIP_DETAIL.CARD,1,10) = substring(RT_RETAN_RECORD.card,4,10) " & cust & "                             
                            ORDER BY    日期, 客户, 订单号, 颜色) AS p LEFT OUTER JOIN
                            RT_WB_OUTPUT_RATE ON p.皮源 = RT_WB_OUTPUT_RATE.source
WHERE          (p.纹路 IS NULL)
GROUP BY   p.流程卡号, p.皮源, RT_WB_OUTPUT_RATE.shaving
ORDER BY   p.皮源"



        cmd.CommandText = sql

        dt = New DataTable

        da.SelectCommand = cmd
        da.Fill(dt)

        dt.Columns.Add("来源")

        dgv_sum_date.DataSource = dt




        sql = "SELECT          p.流程卡号, p.皮源, SUM(p.心皮重) AS 心皮重, case when RT_WB_OUTPUT_RATE.shaving>0.001 then RT_WB_OUTPUT_RATE.shaving else 33 end  AS 得革率
FROM              (SELECT          TOP (100) PERCENT RT_SHIP_DETAIL.DATE AS 日期, RT_SHIP_DETAIL.CUST AS 客户, 
                                                        RT_SHIP_DETAIL.PO_NO AS 订单号, RT_SHIP_DETAIL.COLOR AS 颜色, 
                                                        RT_SHIP_DETAIL.PATTERN AS 纹路, RT_SHIP_DETAIL.THICK AS 厚度, 
                                                        CAST(RT_SHIP_DETAIL.QTY AS numeric(10, 1)) AS 尺数, RT_SHIP_DETAIL.CARD AS 流程卡号, 
                                                        RT_SHIP_DETAIL.SHIP_NO AS 送货单号, RT_SHIP_DETAIL.OUT_NO AS 出库单号, 
                                                        RT_RETAN_RECORD.source AS 皮源, CAST(RT_SHIP_DETAIL.QTY / 6 AS numeric(10, 1)) 
                                                        AS 心皮重
                            FROM               RT_SHIP_DETAIL LEFT OUTER JOIN
                                                        RT_RETAN_RECORD ON substring(RT_SHIP_DETAIL.CARD,1,10) = substring(RT_RETAN_RECORD.card,4,10) " & cust & "                             
                            ORDER BY    日期, 客户, 订单号, 颜色) AS p LEFT OUTER JOIN
                            RT_WB_OUTPUT_RATE ON p.皮源 = RT_WB_OUTPUT_RATE.source
WHERE          (p.纹路 IS NULL)
GROUP BY   p.流程卡号, p.皮源, RT_WB_OUTPUT_RATE.shaving
ORDER BY   p.皮源"






        cmd.CommandText = sql

        dt = New DataTable

        da.SelectCommand = cmd
        da.Fill(dt)

        dt.Columns.Add("来源")

        dgv_sum_date.DataSource = dt





        sql = "select 皮源,sum(心皮重) as 心皮重,cast (avg(得革率) as numeric(10,2)) as 得革率, cast(sum(心皮重/(得革率/100)) as numeric(10,0)) as 蓝皮重 from ( SELECT          p.流程卡号, p.皮源, SUM(p.心皮重) AS 心皮重, case when RT_WB_OUTPUT_RATE.shaving>0.001 then RT_WB_OUTPUT_RATE.shaving else 33 end  AS 得革率
FROM              (SELECT          TOP (100) PERCENT RT_SHIP_DETAIL.DATE AS 日期, RT_SHIP_DETAIL.CUST AS 客户, 
                                                        RT_SHIP_DETAIL.PO_NO AS 订单号, RT_SHIP_DETAIL.COLOR AS 颜色, 
                                                        RT_SHIP_DETAIL.PATTERN AS 纹路, RT_SHIP_DETAIL.THICK AS 厚度, 
                                                        CAST(RT_SHIP_DETAIL.QTY AS numeric(10, 1)) AS 尺数, RT_SHIP_DETAIL.CARD AS 流程卡号, 
                                                        RT_SHIP_DETAIL.SHIP_NO AS 送货单号, RT_SHIP_DETAIL.OUT_NO AS 出库单号, 
                                                        RT_RETAN_RECORD.source AS 皮源, CAST(RT_SHIP_DETAIL.QTY / 6 AS numeric(10, 1)) 
                                                        AS 心皮重
                            FROM               RT_SHIP_DETAIL LEFT OUTER JOIN
                                                        RT_RETAN_RECORD ON substring(RT_SHIP_DETAIL.CARD,1,10) = substring(RT_RETAN_RECORD.card,4,10) " & cust & "                             
                            ORDER BY    日期, 客户, 订单号, 颜色) AS p LEFT OUTER JOIN
                            RT_WB_OUTPUT_RATE ON p.皮源 = RT_WB_OUTPUT_RATE.source
WHERE          (p.纹路 IS NULL)
GROUP BY   p.流程卡号, p.皮源, RT_WB_OUTPUT_RATE.shaving
) as q group by 皮源 order by 蓝皮重 desc "






        cmd.CommandText = sql

        dt = New DataTable

        da.SelectCommand = cmd
        da.Fill(dt)

        'dt.Columns.Add("来源")

        dgv_sum.DataSource = dt












        ''---领料明细
        'sql = "select 客户,卡号,cast(sum(尺数)/count(*) as numeric(10,1)) as 尺数,count(*) as 笔数,品名,cast(sum(领料) as numeric(10,1)) as 领料,海关分类,avg(下鼓重) as 下鼓重,领料日期 from (SELECT          TOP (100) PERCENT RT_SHIP_DETAIL.DATE AS 出货日期, RT_SHIP_DETAIL.CUST AS 客户, RT_SHIP_DETAIL.PO_NO AS 订单号, RT_SHIP_DETAIL.CARD AS 卡号, RT_SHIP_DETAIL.COLOR AS 颜色, RT_SHIP_DETAIL.pattern AS 紋路,  RT_SHIP_DETAIL.QTY AS 尺数, RT_SYS_CHEM_OUT.NAME AS 品名, RT_SYS_CHEM_OUT.WEIGHT AS 领料,  RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类, RT_SYS_CHEM_OUT.ORDER_A AS 下鼓重,  RT_SYS_CHEM_OUT.DATE AS 领料日期 FROM              RT_SYS_CHEM_MAIN INNER JOIN   RT_SYS_CHEM_OUT ON RT_SYS_CHEM_MAIN.NAME = RT_SYS_CHEM_OUT.NAME RIGHT OUTER JOIN    RT_SHIP_DETAIL ON RT_SYS_CHEM_OUT.CARD = RT_SHIP_DETAIL.CARD " & cust & " ORDER BY RT_SHIP_DETAIL.DATE DESC) as p group by 客户,卡号,品名,海关分类,领料日期 order by 卡号,海关分类,领料日期"


        'cmd.CommandText = sql

        'dt = New DataTable

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

        'dgv_sum_date.DataSource = dt

        'dgv_sum_date.Columns("卡号").Width = 120
        'dgv_sum_date.Columns("尺数").Width = 70
        'dgv_sum_date.Columns("笔数").Width = 50
        'dgv_sum_date.Columns("品名").Width = 150
        'dgv_sum_date.Columns("领料").Width = 50
        'dgv_sum_date.Columns("海关分类").Width = 100
        'dgv_sum_date.Columns("下鼓重").Width = 80



        '''---领料汇总
        ''sql = "select 卡号,cast(sum(领料) as numeric(10,1)) as 领料,海关分类,avg(下鼓重) as 下鼓重,领料日期 from (SELECT          TOP (100) PERCENT RT_SHIP_DETAIL.DATE AS 出货日期, RT_SHIP_DETAIL.CUST AS 客户, RT_SHIP_DETAIL.PO_NO AS 订单号, RT_SHIP_DETAIL.CARD AS 卡号, RT_SHIP_DETAIL.COLOR AS 颜色, RT_SHIP_DETAIL.pattern AS 紋路,  RT_SHIP_DETAIL.QTY AS 尺数, RT_SYS_CHEM_OUT.NAME AS 品名, RT_SYS_CHEM_OUT.WEIGHT AS 领料,  RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类, RT_SYS_CHEM_OUT.ORDER_A AS 下鼓重,  RT_SYS_CHEM_OUT.DATE AS 领料日期 FROM              RT_SYS_CHEM_MAIN INNER JOIN   RT_SYS_CHEM_OUT ON RT_SYS_CHEM_MAIN.NAME = RT_SYS_CHEM_OUT.NAME RIGHT OUTER JOIN    RT_SHIP_DETAIL ON RT_SYS_CHEM_OUT.CARD = RT_SHIP_DETAIL.CARD " & cust & " ORDER BY RT_SHIP_DETAIL.DATE DESC) as p WHERE 领料日期>='" & String.Format("{0:d}", dtp1.Value) & "' group by 卡号,海关分类,领料日期 order by 卡号,海关分类,领料日期"


        ''cmd.CommandText = sql

        ''dt = New DataTable

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

        ''dgv_sum.DataSource = dt


        ''dgv_sum.Columns("卡号").Width = 120
        ''dgv_sum.Columns("领料").Width = 50
        ''dgv_sum.Columns("下鼓重").Width = 80



        '''---领料汇总1
        ''sql = "select 领料日期,海关分类,cast(sum(领料) as numeric(10,1)) as 领料 from (SELECT          TOP (100) PERCENT RT_SHIP_DETAIL.DATE AS 出货日期, RT_SHIP_DETAIL.CUST AS 客户, RT_SHIP_DETAIL.PO_NO AS 订单号, RT_SHIP_DETAIL.CARD AS 卡号, RT_SHIP_DETAIL.COLOR AS 颜色, RT_SHIP_DETAIL.pattern AS 紋路,  RT_SHIP_DETAIL.QTY AS 尺数, RT_SYS_CHEM_OUT.NAME AS 品名, RT_SYS_CHEM_OUT.WEIGHT AS 领料,  RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类, RT_SYS_CHEM_OUT.ORDER_A AS 下鼓重,  RT_SYS_CHEM_OUT.DATE AS 领料日期 FROM              RT_SYS_CHEM_MAIN INNER JOIN   RT_SYS_CHEM_OUT ON RT_SYS_CHEM_MAIN.NAME = RT_SYS_CHEM_OUT.NAME RIGHT OUTER JOIN    RT_SHIP_DETAIL ON right(RT_SYS_CHEM_OUT.CARD,6) = RT_SHIP_DETAIL.CARD " & cust & " ORDER BY RT_SHIP_DETAIL.DATE DESC) as p WHERE 领料日期>='" & String.Format("{0:d}", dtp1.Value) & "' group by 海关分类,领料日期 order by 领料日期 desc"


        ''cmd.CommandText = sql

        ''dt = New DataTable

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

        ''dgv_class.DataSource = dt

        ''dgv_class.Columns("领料").Width = 80


        '--蓝皮领料



        sql = "SELECT          皮源, 下鼓重, 下鼓重 * 5.8 AS 折尺数, CASE WHEN shaving IS NULL THEN CAST(下鼓重 / 0.35 AS numeric(10, 0)) 
                            ELSE CAST(下鼓重 / shaving * 100 AS numeric(10, 0)) END AS 蓝皮重
FROM              (SELECT          TOP (100) PERCENT r.source AS 皮源, SUM(r.蓝皮重) AS 下鼓重, RT_WB_OUTPUT_RATE.shaving
                            FROM               (SELECT          q.卡号, AVG(q.下鼓重) AS Expr1, RT_RETAN_RECORD.source, 
                                                                                     RT_RETAN_RECORD.wb_weight / 2 AS 蓝皮重
                                                         FROM               (SELECT          TOP (100) PERCENT 卡号, CAST(SUM(尺数) / COUNT(*) AS numeric(10, 1)) 
                                                                                                                  AS 尺数, COUNT(*) AS 笔数, 品名, CAST(SUM(领料) AS numeric(10, 1)) 
                                                                                                                  AS 领料, 海关分类, AVG(下鼓重) AS 下鼓重, 领料日期
                                                                                      FROM               (SELECT          TOP (100) PERCENT RT_SHIP_DETAIL.DATE AS 出货日期, 
                                                                                                                                               RT_SHIP_DETAIL.CUST AS 客户, 
                                                                                                                                               RT_SHIP_DETAIL.PO_NO AS 订单号, 
                                                                                                                                               RT_SHIP_DETAIL.CARD AS 卡号, 
                                                                                                                                               RT_SHIP_DETAIL.COLOR AS 颜色, 
                                                                                                                                               RT_SHIP_DETAIL.PATTERN AS 紋路, 
                                                                                                                                               RT_SHIP_DETAIL.QTY AS 尺数, 
                                                                                                                                               RT_SYS_CHEM_OUT.NAME AS 品名, 
                                                                                                                                               RT_SYS_CHEM_OUT.WEIGHT AS 领料, 
                                                                                                                                               RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类, 
                                                                                                                                               RT_SYS_CHEM_OUT.ORDER_A AS 下鼓重, 
                                                                                                                                               RT_SYS_CHEM_OUT.DATE AS 领料日期
                                                                                                                   FROM               RT_SYS_CHEM_MAIN INNER JOIN
                                                                                                                                               RT_SYS_CHEM_OUT ON 
                                                                                                                                               RT_SYS_CHEM_MAIN.NAME = RT_SYS_CHEM_OUT.NAME RIGHT
                                                                                                                                                OUTER JOIN
                                                                                                                                               RT_SHIP_DETAIL ON 
                                                                                                                                               RT_SYS_CHEM_OUT.CARD = RT_SHIP_DETAIL.CARD
                                                                                                                   " & cust & "  and   (RT_SHIP_DETAIL.CARD IS NOT NULL)) AS p
                                                                                      GROUP BY    卡号, 品名, 海关分类, 领料日期
                                                                                      ORDER BY    卡号, 海关分类, 领料日期) AS q LEFT OUTER JOIN
                                                                                     RT_RETAN_RECORD ON q.卡号 = RT_RETAN_RECORD.card
                                                         GROUP BY    q.卡号, RT_RETAN_RECORD.source, RT_RETAN_RECORD.wb_weight) 
                                                        AS r LEFT OUTER JOIN
                                                        RT_WB_OUTPUT_RATE ON r.source = RT_WB_OUTPUT_RATE.source
                            GROUP BY    r.source, RT_WB_OUTPUT_RATE.shaving
                            ORDER BY    下鼓重 DESC) AS s
ORDER BY   下鼓重 DESC"













        cmd.CommandText = sql

        dt = New DataTable

        da.SelectCommand = cmd
        da.Fill(dt)

        dgv_wb2.DataSource = dt






































        '----蓝皮领料汇总

        sql = "SELECT          q.卡号, RT_RETAN_RECORD.cust as 客户,RT_RETAN_RECORD.source AS 皮源, RT_RETAN_RECORD.wb_weight / 2 AS 下鼓重, 出货尺数,
                            Rt_measure_daily.commentB as 备注, Rt_measure_daily.date as 量尺日期
FROM              (SELECT          TOP (100) PERCENT 卡号, SUM(尺数) AS 出货尺数, COUNT(*) AS 笔数, 
                                                        品名, CAST(SUM(领料) AS numeric(10, 1)) AS 领料, 海关分类, AVG(下鼓重) AS 下鼓重, 领料日期
                            FROM               (SELECT          TOP (100) PERCENT RT_SHIP_DETAIL.DATE AS 出货日期, RT_SHIP_DETAIL.CUST AS 客户,
                                                                                      RT_SHIP_DETAIL.PO_NO AS 订单号, RT_SHIP_DETAIL.CARD AS 卡号, 
                                                                                     RT_SHIP_DETAIL.COLOR AS 颜色, RT_SHIP_DETAIL.PATTERN AS 紋路, 
                                                                                     RT_SHIP_DETAIL.QTY AS 尺数, RT_SYS_CHEM_OUT.NAME AS 品名, 
                                                                                     RT_SYS_CHEM_OUT.WEIGHT AS 领料, RT_SYS_CHEM_MAIN.CLASS2 AS 海关分类, 
                                                                                     RT_SYS_CHEM_OUT.ORDER_A AS 下鼓重, RT_SYS_CHEM_OUT.DATE AS 领料日期
                                                         FROM               RT_SYS_CHEM_MAIN INNER JOIN
                                                                                     RT_SYS_CHEM_OUT ON 
                                                                                     RT_SYS_CHEM_MAIN.NAME = RT_SYS_CHEM_OUT.NAME RIGHT OUTER JOIN
                                                                                     RT_SHIP_DETAIL ON RT_SYS_CHEM_OUT.CARD = RT_SHIP_DETAIL.CARD
                                                         " & cust & " and           (RT_SHIP_DETAIL.CARD IS NOT NULL)) AS p
                            GROUP BY    卡号, 品名, 海关分类, 领料日期
                            ORDER BY    卡号, 海关分类, 领料日期) AS q LEFT OUTER JOIN
                            Rt_measure_daily ON q.卡号 = Rt_measure_daily.card LEFT OUTER JOIN
                            RT_RETAN_RECORD ON q.卡号 = RT_RETAN_RECORD.card
GROUP BY   q.卡号, RT_RETAN_RECORD.source, RT_RETAN_RECORD.wb_weight, Rt_measure_daily.commentB, 
                            Rt_measure_daily.date, RT_RETAN_RECORD.cust,q.出货尺数"






        cmd.CommandText = sql

        dt = New DataTable

        da.SelectCommand = cmd
        da.Fill(dt)

        dgv_wb1.DataSource = dt

        dgv_wb1.Columns("卡号").Width = 120
        dgv_wb1.Columns("皮源").Width = 120
        dgv_wb1.Columns("下鼓重").Width = 100








    End Sub




    Private Sub dgv_sum_cust_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_sum_cust.CellContentClick   '---选取客户


        my_cust = ""

        Dim my_sum As Double = 0
        Dim FMB As Double = 0
        Dim FMO As Double = 0
        Dim PUB As Double = 0
        Dim PUO As Double = 0

        dgv_sum_cust.EndEdit()

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


            If Not (IsDBNull(dgv_sum_cust.Rows(x).Cells(0).Value)) Then

                If dgv_sum_cust.Rows(x).Cells(0).Value = True Then


                    my_cust = my_cust & "," & dgv_sum_cust.Rows(x).Cells(1).Value

                    my_sum = my_sum + dgv_sum_cust.Rows(x).Cells(2).Value





                    If dgv_sum_cust.Rows(x).Cells("类别").Value = "反毛" Then


                        If dgv_sum_cust.Rows(x).Cells("颜色").Value Like "*黑*" Then
                            FMB = FMB + dgv_sum_cust.Rows(x).Cells(2).Value

                        Else
                            FMO = FMO + dgv_sum_cust.Rows(x).Cells(2).Value
                        End If

                    Else

                        If dgv_sum_cust.Rows(x).Cells("颜色").Value Like "*黑*" Then
                            PUB = PUB + dgv_sum_cust.Rows(x).Cells(2).Value

                        Else
                            PUO = PUO + dgv_sum_cust.Rows(x).Cells(2).Value
                        End If



                    End If











                    dgv_sum_cust.Rows(x).DefaultCellStyle.BackColor = Color.Yellow
                Else

                    dgv_sum_cust.Rows(x).DefaultCellStyle.BackColor = Color.White


                End If


            End If

        Next


        '  TextBox3.Text = my_sum
        TextBox2.Text = my_sum


        TXT_FMB.Text = FMB
        TXT_FMO.Text = FMO
        TXTPUB.Text = PUB
        TXTPUO.Text = PUO




    End Sub

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

        my_cust = ""

        Dim my_sum_FM_B As Double = 0
        Dim my_sum_FM_O As Double = 0


        Dim my_sum_PU_B As Double = 0
        Dim my_sum_PU_O As Double = 0




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


            If dgv_sum_cust.Rows(x).Cells("客户").Value = "许宝贝" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "极路客" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "金威利" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "威弘" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "中山志捷（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "加和" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "百合" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "鑫龙" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "鑫越" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "中山华新（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "东莞华昂" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "加和（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "蜘蛛王" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "惠雄（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "现金" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "鑫茂" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "铭鑫" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "铭诚" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "三元鞋厂（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "金威利样品" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "周连发" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "锐奇鞋业" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "合成行东莞有限公司" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "乐迪" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "永驰" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "好兄弟鞋业" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "凯运皮革" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "利亚" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "顺泰盛" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "源泰" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "厦门飞鹏/厦门鼎石通" Or dgv_sum_cust.Rows(x).Cells("客户").Value Like "*金帝*" Or dgv_sum_cust.Rows(x).Cells("客户").Value Like "*欣力*" Or dgv_sum_cust.Rows(x).Cells("客户").Value Like "*正纬*" Or dgv_sum_cust.Rows(x).Cells("客户").Value Like "*珍琦*" Or dgv_sum_cust.Rows(x).Cells("客户").Value Like "*正纬*" Then

                my_cust = my_cust & "," & dgv_sum_cust.Rows(x).Cells(1).Value




                If dgv_sum_cust.Rows(x).Cells("类别").Value = "反毛" Then


                    If dgv_sum_cust.Rows(x).Cells("颜色").Value Like "*黑*" Then
                        my_sum_FM_B = my_sum_FM_B + dgv_sum_cust.Rows(x).Cells(2).Value

                    Else
                        my_sum_FM_O = my_sum_FM_O + dgv_sum_cust.Rows(x).Cells(2).Value
                    End If




                Else


                    If dgv_sum_cust.Rows(x).Cells("颜色").Value Like "*黑*" Then
                        my_sum_PU_B = my_sum_PU_B + dgv_sum_cust.Rows(x).Cells(2).Value

                    Else
                        my_sum_PU_O = my_sum_PU_O + dgv_sum_cust.Rows(x).Cells(2).Value
                    End If



                End If







                dgv_sum_cust.Rows(x).DefaultCellStyle.BackColor = Color.Yellow
                dgv_sum_cust.Rows(x).Cells("选取").Value = True
            End If


        Next


        TextBox2.Text = my_sum_FM_O + my_sum_PU_O + my_sum_FM_B + my_sum_PU_B

        TXT_FMB.Text = my_sum_FM_B
        TXT_FMO.Text = my_sum_FM_O
        TXTPUB.Text = my_sum_PU_B
        TXTPUO.Text = my_sum_PU_O

        TextBox4.Text = my_sum_FM_B + my_sum_FM_O
        TextBox7.Text = my_sum_PU_B + my_sum_PU_O





    End Sub

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

        dgv_sum_date.AllowUserToAddRows = False

        Dim MY_STRING As String = ""


        For X As Integer = 0 To dgv_sum_date.Rows.Count - 1



            MY_STRING = Regex.Replace(dgv_sum_date.Rows(X).Cells("皮源").Value.ToString, "[^0-9]", "")


            dgv_sum_date.Rows(X).Cells("来源").Value = MY_STRING



        Next


        dgv_sum_date.Columns("来源").Width = 300
        dgv_sum_date.Columns("皮源").Width = 300

    End Sub

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


        my_cust = ""

        Dim my_sum_FM_B As Double = 0
        Dim my_sum_FM_O As Double = 0


        Dim my_sum_PU_B As Double = 0
        Dim my_sum_PU_O As Double = 0




        For x As Integer = 1 To dgv_sum_cust.Rows.Count - 1


            If dgv_sum_cust.Rows(x).Cells("客户").Value = "许宝贝" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "金帝" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "欣力贸易" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "极路客" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "金威利" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "威弘" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "中山志捷（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "加和" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "百合" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "鑫龙" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "鑫越" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "中山华新（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "东莞华昂" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "加和（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "蜘蛛王" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "惠雄（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "现金" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "鑫茂" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "铭鑫" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "铭诚" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "三元鞋厂（美元）" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "金威利样品" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "周连发" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "锐奇鞋业" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "合成行东莞有限公司" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "乐迪" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "永驰" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "好兄弟鞋业" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "凯运皮革" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "利亚" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "顺泰盛" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "源泰" Or dgv_sum_cust.Rows(x).Cells("客户").Value = "厦门飞鹏/厦门鼎石通" Then

            Else


                my_cust = my_cust & "," & dgv_sum_cust.Rows(x).Cells(1).Value



                If dgv_sum_cust.Rows(x).Cells("类别").Value = "反毛" Then


                    If dgv_sum_cust.Rows(x).Cells("颜色").Value Like "*黑*" Then
                        my_sum_FM_B = my_sum_FM_B + dgv_sum_cust.Rows(x).Cells(2).Value

                    Else
                        my_sum_FM_O = my_sum_FM_O + dgv_sum_cust.Rows(x).Cells(2).Value
                    End If

                Else


                    If dgv_sum_cust.Rows(x).Cells("颜色").Value Like "*黑*" Then
                        my_sum_PU_B = my_sum_PU_B + dgv_sum_cust.Rows(x).Cells(2).Value

                    Else
                        my_sum_PU_O = my_sum_PU_O + dgv_sum_cust.Rows(x).Cells(2).Value
                    End If

                End If



                dgv_sum_cust.Rows(x).DefaultCellStyle.BackColor = Color.Yellow
                dgv_sum_cust.Rows(x).Cells("选取").Value = True
            End If


        Next


        TextBox2.Text = my_sum_FM_O + my_sum_PU_O + my_sum_FM_B + my_sum_PU_B

        TXT_FMB.Text = my_sum_FM_B
        TXT_FMO.Text = my_sum_FM_O
        TXTPUB.Text = my_sum_PU_B
        TXTPUO.Text = my_sum_PU_O

        TextBox4.Text = my_sum_FM_B + my_sum_FM_O
        TextBox7.Text = my_sum_PU_B + my_sum_PU_O




    End Sub
End Class