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

Imports System.Data.SqlClient
Imports System.Reflection
Public Class Frm_RT_VF_ORDER
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Dim conn As New SqlConnection
    Dim sql As String
    Private Sub Frm_RT_VF_ORDER_Load(sender As Object, e As EventArgs) Handles MyBase.Load





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


        conn.ConnectionString = connstring
        conn.Open()



        cmd.Connection = conn



        sql = "select * from rt_vf_order_detail"

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

        'For x As Integer = 0 To dt.Rows.Count - 1
        '    Dim p() As String
        '    Dim p1() As String
        '    Dim my_qty As Double = 0
        '    Dim my_qty1 As Double = 0
        '    p = Split(dt.Rows(x).Item("ship_qty").ToString, "=")  '-----发货

        '    If UBound(p) > 0 Then
        '        Dim q() As String
        '        q = Split(p(1), "+")
        '        For y As Integer = 0 To UBound(q)
        '            my_qty = my_qty + Val(q(y))
        '        Next
        '    Else

        '        If IsDBNull(dt.Rows(x).Item("ship_qty")) Then
        '            my_qty = 0
        '        Else
        '            my_qty = Val(dt.Rows(x).Item("ship_qty"))
        '        End If


        '    End If

        '    p1 = Split(dt.Rows(x).Item("order_qty"), "=")  '-----订单

        '    If UBound(p1) > 0 Then
        '        Dim q1() As String
        '        q1 = Split(p1(1), "-")
        '        For y As Integer = 0 To UBound(q1)
        '            my_qty1 = my_qty1 + Val(q1(y))
        '        Next
        '    Else
        '        my_qty1 = Val(dt.Rows(x).Item("order_qty"))

        '    End If


        '    sql = "update rt_vf_order_detail set ship_qty1= '" & my_qty & "',order_qty1='" & my_qty1 & "' where po_no='" & dt.Rows(x).Item("po_no") & "' and ship_qty='" & dt.Rows(x).Item("ship_qty") & "' and color=N'" & dt.Rows(x).Item("color") & "' and cust=N'" & dt.Rows(x).Item("cust") & "'"
        '    cmd.CommandText = sql
        '    cmd.ExecuteScalar()


        'Next





        dt = New DataTable


        '        sql = "SELECT   po_no AS 订单号, date_style AS [下单日/型体号], cust AS 客户, color AS 颜色, thick AS 厚度, card AS 开卡, 
        '                card_qty AS 开卡数量, order_qty AS 订单数量, item AS 项次, tag AS 标千打印, material_no AS 料号, month AS 月份, 
        '                due_date AS 交期, ship_date AS 发货日期, ship_qty AS 发货数量, order_month AS 订单月份, comment AS 备注, 
        '                ship_qty1, order_qty1
        'FROM      RT_VF_Order_Detail
        'WHERE   id IS NULL"
        '        cmd.CommandText = sql
        '        da.SelectCommand = cmd
        '        da.Fill(dt)

        '        For x As Integer = 0 To dt.Rows.Count - 1
        '            Dim p() As String
        '            p = Split(dt.Rows(x).Item("发货日期"), "/")


        '            Dim my_date As String = ""
        '            For y As Integer = 0 To UBound(p)

        '                Dim q() As String

        '                q = Split(p(y), ".")

        '                If q(0) <> "2019" Then
        '                    If q(0) = "12" Then
        '                        p(y) = "2019." + p(y)
        '                    ElseIf q(0) = "2020" Then

        '                    Else
        '                        p(y) = "2020." + p(y)
        '                    End If


        '                End If



        '                'If q(0) <> "2019" Then

        '                '    p(y) = "2019." + p(y)


        '                'Else

        '                '    If q(0) = "2020" Then
        '                '        q(0) = p(y)
        '                '    Else
        '                '        p(y) = "2019." + p(y)
        '                '    End If


        '                'End If





        '                If y = 0 Then

        '                    my_date = p(y)
        '                Else
        '                    my_date = my_date + "/" + p(y)
        '                End If



        '            Next

        '            sql = "update RT_VF_Order_Detail set ship_Date1='" & my_date & "',CHECKED1='1' where po_no=N'" & dt.Rows(x).Item("订单号") & "' and date_style=N'" & dt.Rows(x).Item("下单日/型体号") & "' and cust=N'" & dt.Rows(x).Item("客户") & "' and color=N'" & dt.Rows(x).Item("颜色") & "' and order_qty='" & dt.Rows(x).Item("订单数量") & "'"
        '            cmd.CommandText = sql
        '            cmd.ExecuteNonQuery()





        '        Next








        dt = New DataTable

        sql = "SELECT   po_no AS 订单号, date_style AS [下单日/型体号], cust AS 客户, color AS 颜色, thick AS 厚度, card AS 开卡, 
                card_qty AS 开卡数量, order_qty AS 订单数量, item AS 项次, tag AS 标千打印, material_no AS 料号, month AS 月份, 
                due_date AS 交期, ship_date1 AS 发货日期, ship_qty AS 发货记录,ship_qty1 as 发货数量,case when order_qty1-ship_qty1<0 then 0 else cast(order_qty1-ship_qty1  as numeric(10,1))end as 尾数, order_month AS 备注, comment AS 订单月份
FROM      RT_VF_Order_Detail"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv_main.DataSource = dt


        dt = New DataTable
        sql = "select * from rt_user_dgv_set where user_name='" & gUserName & "' and user_form_name='frm_rt_vf_order'"  '--------设定DGV栏宽
        cmd.CommandText = sql
        da.Fill(dt)

        For x As Integer = 0 To dt.Rows.Count - 1
            dgv_main.Columns(dt.Rows(x).Item("dgv_column")).Width = dt.Rows(x).Item("dgv_width")

        Next









        dt = New DataTable

        sql = "select po_no as 订单号,cust as 客户,count(*) as 笔数 from rt_vf_order_detail group by po_no,cust"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv_group.DataSource = dt


        dgv_group.Columns(0).Width = 150


        dt = New DataTable

        sql = "select cust as 客户 from rt_vf_order_detail group by cust"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv_cust.DataSource = dt



    End Sub

    Private Sub dgv_cust_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_cust.CellClick

        dt = New DataTable

        sql = "SELECT   po_no AS 订单号, date_style AS [下单日/型体号], cust AS 客户, color AS 颜色, thick AS 厚度, card AS 开卡, 
                card_qty AS 开卡数量, order_qty AS 订单数量, item AS 项次, tag AS 标千打印, material_no AS 料号, month AS 月份, 
                due_date AS 交期, ship_date AS 发货日期, ship_qty AS 发货记录,ship_qty1 as 发货数量,case when order_qty1-ship_qty1<0 then 0 else cast(order_qty1-ship_qty1  as numeric(10,1))end as 尾数, order_month AS 备注, comment AS 订单月份
FROM      RT_VF_Order_Detail where cust=N'" & dgv_cust.Rows(dgv_cust.CurrentCell.RowIndex).Cells("客户").Value & "'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv_main.DataSource = dt


        dt = New DataTable

        sql = "select po_no as 订单号,count(*) as 笔数 from rt_vf_order_detail where cust=N'" & dgv_cust.Rows(dgv_cust.CurrentCell.RowIndex).Cells("客户").Value & "' group by po_no"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv_group.DataSource = dt



    End Sub

    Private Sub dgv_main_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_main.CellContentClick

    End Sub

    Private Sub dgv_group_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_group.CellClick

        dt = New DataTable

        sql = "SELECT   po_no AS 订单号, date_style AS [下单日/型体号], cust AS 客户, color AS 颜色, thick AS 厚度, card AS 开卡, 
                card_qty AS 开卡数量, order_qty AS 订单数量, item AS 项次, tag AS 标千打印, material_no AS 料号, month AS 月份, 
                due_date AS 交期, ship_date AS 发货日期, ship_qty AS 发货记录,ship_qty1 as 发货数量,case when order_qty1-ship_qty1<0 then 0 else cast(order_qty1-ship_qty1  as numeric(10,1))end as 尾数, order_month AS 备注, comment AS 订单月份
FROM      RT_VF_Order_Detail where po_no='" & dgv_group.Rows(dgv_group.CurrentCell.RowIndex).Cells("订单号").Value & "'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv_main.DataSource = dt


    End Sub

    Private Sub Frm_RT_VF_ORDER_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing




        If MessageBox.Show("是否将表格格式存档?", "提醒", MessageBoxButtons.YesNo) = MsgBoxResult.No Then

            'e.Cancel = True

        Else

            sql = "delete from  rt_user_dgv_set where user_name='" & gUserName & "' and user_form_name='" & Me.Name & "'"
            cmd.CommandText = sql
            cmd.ExecuteScalar()


            For x As Integer = 0 To dgv_main.ColumnCount - 1

                sql = "insert into rt_user_dgv_set (user_name,user_form_name,dgv_name,dgv_column,dgv_width) values ('" & gUserName & "','" & Me.Name & "','dgv_main','" & x & "','" & dgv_main.Columns(x).Width & "')"
                cmd.CommandText = sql
                cmd.ExecuteScalar()

            Next

            MsgBox("格式储存完成")


        End If




    End Sub

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


        Dim year1 As Integer = Year(DateTimePicker1.Value)
        Dim year2 As Integer = Year(DateTimePicker2.Value)


        Dim month1 As Integer = Month(DateTimePicker1.Value)
        Dim month2 As Integer = Month(DateTimePicker2.Value)


        dt = New DataTable
        da.SelectCommand = cmd
        Dim dr As SqlDataReader


        'DateTimePicker1.CustomFormat = "yyyy.MM.dd"
        'DateTimePicker1.Format = DateTimePickerFormat.Custom

        'DateTimePicker2.CustomFormat = "yyyy.MM.dd"

        'DateTimePicker2.Format = DateTimePickerFormat.Custom


        sql = " Select po_no As 订单号, cust As 客户, COUNT(*) As 交货笔数,id From RT_VF_Order_Detail Where (ship_date1 Like '%." & month1 & ".%') and (ship_date1 Like '%." & month2 & ".%') GROUP BY po_no, cust,id Order By 订单号"

        sql = "SELECT   RT_VF_Order_Detail.po_no AS 订单号, RT_VF_Order_Detail.cust AS 客户, COUNT(*) AS 交货笔数,                RT_SHIP_RECORD.id FROM      RT_SHIP_RECORD LEFT OUTER JOIN          RT_VF_Order_Detail ON RT_SHIP_RECORD.id = RT_VF_Order_Detail.id WHERE   (RT_SHIP_RECORD.ship_date BETWEEN '" & String.Format("{0:yyyy.MM.dd}", DateTimePicker1.Value) & "' and '" & String.Format("{0:yyyy.MM.dd}", DateTimePicker2.Value) & "') GROUP BY RT_VF_Order_Detail.po_no, RT_VF_Order_Detail.cust, RT_SHIP_RECORD.id"




        sql = "SELECT   日期, 订单号, 客户, CAST(SUM(数量) AS numeric(10, 1)) AS 数量, COUNT(*) AS 笔数 FROM      (SELECT   RT_SHIP_RECORD.id, REPLACE(RT_SHIP_RECORD.ship_date, '.', '/') AS 日期,   RT_SHIP_RECORD.ship_qty AS 数量, RT_VF_Order_Detail.po_no AS 订单号,   RT_VF_Order_Detail.cust AS 客户   FROM      RT_SHIP_RECORD INNER JOIN       RT_VF_Order_Detail ON RT_SHIP_RECORD.id = RT_VF_Order_Detail.id        WHERE   (REPLACE(RT_SHIP_RECORD.ship_date, '.', '/') BETWEEN '" & DateTimePicker1.Value & "' and '" & DateTimePicker2.Value & "')) AS p GROUP BY 日期, 客户, 订单号"


        sql = "SELECT   cust AS 客户, po_no AS 订单号, CAST(SUM(ship_qty) AS numeric(10, 1)) AS 数量, COUNT(*) AS 笔数 FROM      (SELECT   RT_SHIP_RECORD.id, RT_SHIP_RECORD.ship_date, RT_SHIP_RECORD.ship_qty,     RT_VF_Order_Detail.po_no, RT_VF_Order_Detail.cust                 FROM      RT_SHIP_RECORD INNER JOIN                              RT_VF_Order_Detail ON RT_SHIP_RECORD.id = RT_VF_Order_Detail.id          WHERE   (REPLACE(RT_SHIP_RECORD.ship_date, '.', '/') BETWEEN '" & DateTimePicker1.Value & "' and '" & DateTimePicker2.Value & "')) AS p GROUP BY cust, po_no ORDER BY 客户, 订单号"


        cmd.CommandText = sql
        da.Fill(dt)


        dt.Columns.Add("订单笔数")

        dt.Columns.Add("是否满单")

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

            sql = "select count(*) as 笔数 from RT_VF_Order_Detail where po_no='" & dt.Rows(x).Item("订单号") & "'"
            cmd.CommandText = sql
            dr = cmd.ExecuteReader
            dr.Read()

            If dr("笔数").ToString = "" Then
                dt.Rows(x).Item("订单笔数") = 0
            Else
                dt.Rows(x).Item("订单笔数") = dr("笔数").ToString
            End If
            dr.Close()




            sql = "SELECT   SUM(尾数) AS 尾数
FROM      (SELECT   po_no AS 订单号, date_style AS [下单日/型体号], cust AS 客户, color AS 颜色, thick AS 厚度, card AS 开卡, 
                                 card_qty AS 开卡数量, order_qty AS 订单数量, item AS 项次, tag AS 标千打印, material_no AS 料号, 
                                 month AS 月份, due_date AS 交期, ship_date AS 发货日期, ship_qty AS 发货记录, ship_qty1 AS 发货数量, 
                                 CASE WHEN order_qty1 - ship_qty1 < 0 THEN 0 ELSE CAST(order_qty1 - ship_qty1 AS numeric(10, 1)) 
                                 END AS 尾数
                 FROM      RT_VF_Order_Detail
                 WHERE   po_no='" & dt.Rows(x).Item("订单号") & "') AS p"
            cmd.CommandText = sql
            dr = cmd.ExecuteReader
            dr.Read()

            If Val(dr("尾数")) = 0 Then
                dt.Rows(x).Item("是否满单") = "是"
            Else
                dt.Rows(x).Item("是否满单") = "否"

            End If
            dr.Close()











        Next

        dgv_query.DataSource = dt



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

            If dgv_query.Rows(x).Cells("是否满单").Value = "否" Then

                dgv_query.Rows(x).DefaultCellStyle.BackColor = Color.LightPink



            End If

        Next


        dt = New DataTable

        'sql = "select * from rt_vf_order_detail "
        'cmd.CommandText = sql
        'da.SelectCommand = cmd
        'da.Fill(dt)

        'For x As Integer = 0 To dt.Rows.Count - 1   '-------寫入ID
        '    sql = "update RT_VF_Order_Detail set id='" & x + 1 & "' where po_no=N'" & dt.Rows(x).Item("po_no") & "' and date_style=N'" & dt.Rows(x).Item("date_style") & "' and cust=N'" & dt.Rows(x).Item("cust") & "' and color=N'" & dt.Rows(x).Item("color") & "' and order_qty='" & dt.Rows(x).Item("order_qty") & "'"
        '    cmd.CommandText = sql
        '    cmd.ExecuteNonQuery()

        'Next


        'dt = New DataTable

        'sql = "select * from rt_vf_order_detail "
        'cmd.CommandText = sql
        'da.SelectCommand = cmd
        'da.Fill(dt)

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

        '    Dim p() As String
        '    Dim pp() As Double
        '    Dim q() As String
        '    Dim qq() As String
        '    Dim qqq() As Double

        '    p = Split(dt.Rows(x).Item("ship_date1"), "/")  '---日期
        '    q = Split(dt.Rows(x).Item("ship_qty"), "=")  '---数量

        '    If UBound(q) > 0 Then

        '        qq = Split(q(1), "+")



        '    Else

        '        ReDim qq(0)
        '        qq(0) = q(0)

        '    End If





        '    For y As Integer = 0 To UBound(p)

        '        sql = "insert into rt_ship_record (id,ship_date,ship_qty) values('" & dt.Rows(x).Item("id") & "','" & p(y) & "','" & qq(y) & "')"  '---寫入出貨記錄
        '        cmd.CommandText = sql
        '        cmd.ExecuteScalar()


        '    Next





        'Next


        ' MsgBox("")
        dgv_query.Columns(0).Width = 80
        dgv_query.Columns(1).Width = 180

        dgv_query.Columns(2).Width = 80

        dgv_query.Columns(3).Width = 80
        dgv_query.Columns(4).Width = 80





    End Sub

    Private Sub dgv_query_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_query.CellClick

        dt = New DataTable

        sql = "SELECT   po_no AS 订单号, date_style AS [下单日/型体号], cust AS 客户, color AS 颜色, thick AS 厚度, card AS 开卡, 
                card_qty AS 开卡数量, order_qty AS 订单数量, item AS 项次, tag AS 标千打印, material_no AS 料号, month AS 月份, 
                due_date AS 交期, ship_date AS 发货日期, ship_qty AS 发货记录,ship_qty1 as 发货数量,case when order_qty1-ship_qty1<0 then 0 else cast(order_qty1-ship_qty1  as numeric(10,1))end as 尾数, order_month AS 备注, comment AS 订单月份
FROM      RT_VF_Order_Detail where po_no='" & dgv_query.Rows(dgv_query.CurrentCell.RowIndex).Cells("订单号").Value & "'"
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        dgv_main.DataSource = dt


    End Sub


End Class