HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/08、關務管理/Frm_BG.vb

Imports System.Data.SqlClient
Imports System.Reflection
Public Class FrmBG
    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
    Private Sub FrmBG_Load(sender As Object, e As EventArgs) Handles MyBase.Load



        Me.MdiParent = FrmMDI

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


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




        Dim DR As SqlDataReader


        conn.ConnectionString = connstring
        conn.Open()



        cmd.Connection = conn


        sql = "SELECT NO as 手册号,until as 有效期,case when checked='true' then N'已核销' else N'未核销' end as checked FROM RT_BG_MAIN ORDER BY SERIAL"
        cmd.CommandText = sql

        DR = cmd.ExecuteReader

        While DR.Read

            ListBox1.Items.Add(DR("手册号") & "  " & DR("有效期") & "  " & DR("checked"))

        End While

        DR.Close()

        'DGV1.DataSource = dt


        DGV1.RowHeadersVisible = False





    End Sub
    Private Sub ListBox1_Click(sender As Object, e As EventArgs) Handles ListBox1.Click



        p = Split(ListBox1.Items(ListBox1.SelectedIndex).ToString, "  ")

        dt.Clear()
        sql = "SELECT * FROM RT_BG_MAIN WHERE NO='" & p(0) & "'"
        cmd.CommandText = sql

        Dim DR As SqlDataReader


        DR = cmd.ExecuteReader


        DR.Read()


        'TXT_VALID.Text = DR("UNTIL").ToString
        'TXT_WB.Text = DR("WB_IN_QTY").ToString
        'TXT_CHEM.Text = DR("CHEM_IN_QTY").ToString
        'TXT_CHEM_ITEM.Text = DR("IMPORT_ITEM").ToString
        'TXT_FEE.Text = DR("PROCESS_FEE").ToString
        'TXT_FINISH_ITEM.Text = DR("EXPORT_ITEM").ToString
        'TXT_FINISH_QTY.Text = DR("OUT_QTY").ToString

        DR.Close()




        sql = "SELECT no,NAME AS 品名, QTY AS 审批数量 FROM RT_BG_IN WHERE NO='" & p(0) & "' ORDER BY SERIAL"
        cmd.CommandText = sql

        dt = New DataTable
        Dim DA As New SqlDataAdapter
        DA.SelectCommand = cmd
        DA.Fill(dt)

        dt.Columns.Add("已进/出数量", GetType(System.Double))



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

            sql = "select sum(qty) from rt_bg_in_detail where xno='" & dt.Rows(x).Item("no") & "' And NAME =N'" & dt.Rows(x).Item("品名") & "' and (matl_name not like N'%内销%' and matl_name not like N'%退港%') and qty>0 "
            cmd.CommandText = sql
            Dim my_qty As Double = 0

            If IsDBNull(cmd.ExecuteScalar) Then
            Else
                my_qty = cmd.ExecuteScalar
            End If

            dt.Rows(x).Item("已进/出数量") = my_qty



        Next



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

            If (dt.Rows(x).Item("品名") Like "*牛二层皮*" Or dt.Rows(x).Item("品名") Like "*削匀皮*") And Not (dt.Rows(x).Item("品名") Like "蓝湿*") Then

                sql = "select CAST(sum(qty) AS NUMERIC(10,3)) from rt_bg_out where xno='" & dt.Rows(x).Item("no") & "' And NAME =N'" & dt.Rows(x).Item("品名") & "'"
                cmd.CommandText = sql
                Dim my_qty As Double = 0

                If IsDBNull(cmd.ExecuteScalar) Then
                Else
                    my_qty = cmd.ExecuteScalar
                End If

                dt.Rows(x).Item("已进/出数量") = my_qty

            End If


        Next



        DGV1.DataSource = dt

        'DGV1.Columns(3).DefaultCellStyle.Format = "N2"
        'DGV1.Columns(2).DefaultCellStyle.Format = "N2"
        DGV1.Columns(2).DefaultCellStyle.Format = "###,###,###.###"
        DGV1.Columns(3).DefaultCellStyle.Format = "###,###,###.###"
        DGV1.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        DGV1.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        DGV1.Columns(0).Visible = False

        DGV1.Columns(1).Width = 120


        Dim DT2 As New DataTable



        sql = "SELECT date1 AS 报关日期, DATE2 AS 进厂日期,NO AS 报关单号, NAME AS 品名, QTY AS 重量, PRICE AS 单价, ORIGIN AS 产地, matl_name as 材料名,id FROM  RT_BG_IN_DETAIL WHERE XNO='" & p(0) & "' order by date1 desc"  '---进口明细
        cmd.CommandText = sql


        DA.SelectCommand = cmd
        DA.Fill(DT2)
        DGV_IN.DataSource = DT2


        DGV_IN.Columns(0).Width = 100
        DGV_IN.Columns(1).Width = 100
        DGV_IN.Columns(2).Width = 180
        DGV_IN.Columns(3).Width = 120
        DGV_IN.Columns(4).Width = 80
        DGV_IN.Columns(5).Width = 60
        DGV_IN.Columns(6).Width = 70
        DGV_IN.Columns(7).Width = 180
        'DGV_IN.Columns(7).Width = 200





        Dim DT1 As New DataTable
        sql = "SELECT date0 as 出厂日期,date1 AS 报关日期, NO AS 报关单号,  NAME AS 品名, QTY AS 重量, cast(TOTAL/qty as numeric(10,1)) AS 单价,FEE AS 加工费,  DES AS 目地的,COMMENT AS 客户,BOL_NO AS 备注,date1 as 日期 FROM RT_BG_OUT WHERE XNO='" & p(0) & "' ORDER BY   报关日期 DESC "  '------出口
        cmd.CommandText = sql


        DA.SelectCommand = cmd
        DA.Fill(DT1)
        DGV_OUT.DataSource = DT1

        DGV_OUT.Columns(0).Width = 100
        DGV_OUT.Columns(1).Width = 100
        DGV_OUT.Columns(2).Width = 180
        DGV_OUT.Columns(3).Width = 140
        DGV_OUT.Columns(4).Width = 60
        DGV_OUT.Columns(5).Width = 60
        DGV_OUT.Columns(6).Width = 60
        DGV_OUT.Columns(7).Width = 80
        DGV_OUT.Columns(8).Width = 80
        DGV_OUT.Columns(9).Width = 150



    End Sub

    Private Sub ListBox1_DoubleClick(sender As Object, e As EventArgs) Handles ListBox1.DoubleClick



        Dim p() As String

        p = Split(ListBox1.Items(ListBox1.SelectedIndex), vbTab)

        xno = p(0)

        Dim my_form As New Frm_BG_sum
        my_form.Show(Me)

        'Dim MY_FORM As New Frm_BG_sum

        'my_form.ShowDialog()



    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click     '--------输入进口资料
        'Frm_BG_CHEMIN_CHOOSE.ShowDialog()


        Dim my_id As Integer = 0


        sql = "select id from rt_bg_in_detail order by id desc"
        cmd.CommandText = sql
        my_id = cmd.ExecuteScalar + 1

        Dim my_qty As Double = InputBox("请输入重量")
        Dim my_price As Double = InputBox("请输入单价")
        Dim source As String = InputBox("请输入产地")
        Dim my_source As String = InputBox("请输入品名")
        Dim no As String = InputBox("请输入报关号")


        Dim p() As String

        p = Split(ListBox1.Items(ListBox1.SelectedIndex), "  ")





        sql = "select qty from rt_bg_in_detail where qty='" & my_qty & "' and xno='" & p(0) & "' and no='" & no & "'"
        cmd.CommandText = sql

        If cmd.ExecuteScalar <> Nothing Then



            If Not (IsDBNull(cmd.ExecuteScalar)) Then
                Dim my_msg As String = MsgBox("已存在记录, 是否新增?", vbYesNo)


                If my_msg = vbYes Then


                    sql = "insert into rt_bg_in_detail (no,bg_date,origin,xno,name,date1,qty,price,matl_name,id) values(N'" & no & "','" & String.Format("{0:yyyMMdd}", dtp1.Value) & "',N'" & source & "',N'" & p(0) & "',N'" & DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value & "','" & String.Format("{0:d}", dtp1.Value) & "','" & my_qty & "','" & my_price & "',N'" & my_source & "','" & my_id & "')"


                    cmd.CommandText = sql

                    cmd.ExecuteScalar()



                Else
                    Exit Sub


                End If

            Else

                sql = "insert into rt_bg_in_detail (no,bg_date,origin,xno,name,date1,qty,price,matl_name,id) values(N'" & no & "','" & String.Format("{0:yyyMMdd}", dtp1.Value) & "',N'" & source & "',N'" & p(0) & "',N'" & DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value & "','" & String.Format("{0:d}", dtp1.Value) & "','" & my_qty & "','" & my_price & "',N'" & my_source & "','" & my_id & "')"


                cmd.CommandText = sql

                cmd.ExecuteScalar()


            End If


        Else

            sql = "insert into rt_bg_in_detail (no,bg_date,origin,xno,name,date1,qty,price,matl_name,id) values(N'" & no & "','" & String.Format("{0:yyyMMdd}", dtp1.Value) & "',N'" & source & "',N'" & p(0) & "',N'" & DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value & "','" & String.Format("{0:d}", dtp1.Value) & "','" & my_qty & "','" & my_price & "',N'" & my_source & "','" & my_id & "')"



            cmd.CommandText = sql

            cmd.ExecuteScalar()





        End If



        MsgBox("录入完成")





    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click  '------输入出口资料




        If DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮1" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮2" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮3" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮4" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮5" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮6" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮7" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮8" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "PU牛二层皮2" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "PU牛二层皮3" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "PU牛二层皮4" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "PU牛二层皮5" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "PU牛二层皮6" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "削匀皮" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮9" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "牛二层皮10" And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value <> "PU牛二层皮7" Then


            MsgBox("请选择正确出口品名")
            Exit Sub

        Else




        End If









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







        Dim p() As String

        p = Split(ListBox1.Items(ListBox1.SelectedIndex), "  ")

        sql = "select qty from rt_bg_out where qty='" & my_qty & "' and xno='" & p(0) & "' and no='" & no & "'"
        cmd.CommandText = sql

        If cmd.ExecuteScalar <> Nothing Then



            If Not (IsDBNull(cmd.ExecuteScalar)) Then
                Dim my_msg As String = MsgBox("已存在记录, 是否新增?", vbYesNo)


                If my_msg = vbYes Then


                    sql = "insert into rt_bg_out (des,no,xno,name,date,date1,qty,total,fee,comment) values(N'" & des & "','" & no & "','" & p(0) & "',N'" &
                   DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value & "','" & String.Format("{0:d}", dtp1.Value) & "','" & String.Format("{0:d}", dtp1.Value) & "','" & my_qty & "','" & my_price & "','" & my_price1 * my_qty & "',N'" & my_cust & "')"


                    cmd.CommandText = sql

                    cmd.ExecuteScalar()

                    MsgBox("录入完成")

                Else
                    Exit Sub


                End If

            Else

                sql = "insert into rt_bg_out (des,no,xno,name,date,date1,qty,total,fee,comment) values(N'" & des & "','" & no & "','" & p(0) & "',N'" &
                   DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value & "','" & String.Format("{0:d}", dtp1.Value) & "','" & String.Format("{0:d}", dtp1.Value) & "','" & my_qty & "','" & my_price & "','" & my_price1 * my_qty & "',N'" & my_cust & "')"

                cmd.CommandText = sql

                cmd.ExecuteScalar()


            End If


        Else

            sql = "insert into rt_bg_out (des,no,xno,name,date,date1,qty,total,fee,comment) values(N'" & des & "','" & no & "','" & p(0) & "',N'" &
                   DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells(1).Value & "','" & String.Format("{0:d}", dtp1.Value) & "','" & String.Format("{0:d}", dtp1.Value) & "','" & my_qty & "','" & my_price & "','" & my_price1 * my_qty & "',N'" & my_cust & "')"

            cmd.CommandText = sql

            cmd.ExecuteScalar()





        End If




        MsgBox("录入完成")


    End Sub

    Private Sub DGV1_CellMouseUp(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DGV1.CellMouseUp
        Dim p() As String

        p = Split(ListBox1.Items(ListBox1.SelectedIndex).ToString, "  ")



        If (DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮2" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮1" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "PU牛二层皮" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮4" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮3" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "PU牛二层皮2" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "PU牛二层皮3" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "PU牛二层皮4" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮5" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮6" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮7" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮8" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮9" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "牛二层皮10" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "PU牛二层皮5" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "PU牛二层皮6" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "PU牛二层皮7" Or DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value = "削匀皮") And DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value <> "蓝湿牛二层皮" Then

            Dim DT1 As New DataTable
            sql = "SELECT date0 as 出厂日期,date1 AS 报关日期, NO AS 报关单号,  NAME AS 品名, QTY AS 重量, cast(TOTAL/qty as numeric(10,1)) AS 单价,FEE AS 加工费,  DES AS 目地的,COMMENT AS 客户 FROM RT_BG_OUT WHERE XNO='" & p(0) & "' and name=N'" & DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value & "' ORDER BY   date1 DESC "
            cmd.CommandText = sql

            da.SelectCommand = cmd
            da.Fill(DT1)


            Dim myRow As DataRow
            myRow = DT1.NewRow
            If IsDBNull(DT1.Compute("sum(重量)", "")) Then
                myRow(3) = 0
            Else
                myRow(3) = Math.Round(DT1.Compute("sum(重量)", ""), 1)
            End If


            myRow(2) = "合计"





            DT1.Rows.InsertAt(myRow, 0)

            DGV_OUT.DataSource = DT1

            DGV_OUT.Columns(0).Width = 100
            DGV_OUT.Columns(1).Width = 100

            DGV_OUT.Columns(2).Width = 180
            DGV_OUT.Columns(3).Width = 120
            DGV_OUT.Columns(4).Width = 80
            DGV_OUT.Columns(5).Width = 60
            DGV_OUT.Columns(6).Width = 60
            DGV_OUT.Columns(7).Width = 80
            DGV_OUT.Columns(8).Width = 80
            ' DGV_OUT.Columns(8).Width = 150


            DGV_OUT.Rows(0).Cells(0).Style.ForeColor = Color.Red
            DGV_OUT.Rows(0).Cells(1).Style.ForeColor = Color.Red
            DGV_OUT.Rows(0).Cells(2).Style.ForeColor = Color.Red
            DGV_OUT.Rows(0).Cells(3).Style.ForeColor = Color.Red



        Else



            Dim dt2 As New DataTable
            sql = "SELECT date1 AS 报关日期,date2 as 进厂日期,  NO AS 报关单号, NAME AS 品名, QTY AS 重量, PRICE AS 单价, ORIGIN AS 产地, matl_name as 材料名,id FROM  RT_BG_IN_DETAIL WHERE XNO='" & p(0) & "' and name=N'" & DGV1.Rows(DGV1.CurrentCell.RowIndex).Cells("品名").Value & "' order by date1 desc"
            cmd.CommandText = sql


            da.SelectCommand = cmd
            da.Fill(dt2)


            Dim myDataRow As DataRow
            myDataRow = dt2.NewRow

            myDataRow(3) = dt2.Compute("sum(重量)", "")
            myDataRow(2) = "合计"





            dt2.Rows.InsertAt(myDataRow, 0)

            DGV_IN.DataSource = dt2



            DGV_IN.Rows(0).Cells(0).Style.ForeColor = Color.Red
            DGV_IN.Rows(0).Cells(1).Style.ForeColor = Color.Red
            DGV_IN.Rows(0).Cells(2).Style.ForeColor = Color.Red
            DGV_IN.Rows(0).Cells(3).Style.ForeColor = Color.Red



            DGV_IN.Columns(0).Width = 100
            DGV_IN.Columns(1).Width = 100

            DGV_IN.Columns(2).Width = 180
            DGV_IN.Columns(3).Width = 120
            DGV_IN.Columns(4).Width = 80
            DGV_IN.Columns(5).Width = 60
            DGV_IN.Columns(6).Width = 70
            DGV_IN.Columns(7).Width = 180
            'DGV_IN.Columns(7).Width = 200





        End If


    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click  '------更新进口数据


        Dim my_date As String


        Dim my_date1 As String



        Dim my_name As String

        my_date = InputBox("请输入进厂日期")



        If IsDate(my_date) Then

            ' MsgBox("日期格式正确")
        Else

            MsgBox("请输入正确日期")
            Exit Sub

        End If






        my_name = InputBox("请输入" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("id").Value & "品名", vbOKCancel)



        If my_name = "" Then Exit Sub





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

        '    sql = "update RT_bg_in_detail set bg_date=N'" & DGV_IN.Rows(x).Cells("报关日期").Value & "',DATE1=N'" & DGV_IN.Rows(x).Cells("报关日期").Value & "',DATE2=N'" & DGV_IN.Rows(x).Cells("进厂日期").Value & "',no=N'" & DGV_IN.Rows(x).Cells("报关单号").Value & "',name=N'" & DGV_IN.Rows(x).Cells("品名").Value & "',qty=N'" & DGV_IN.Rows(x).Cells("重量").Value & "',price='" & DGV_IN.Rows(x).Cells("单价").Value & "',origin=N'" & DGV_IN.Rows(x).Cells("产地").Value & "',matl_name=N'" & DGV_IN.Rows(x).Cells("材料名").Value & "' where id='" & DGV_IN.Rows(x).Cells("id").Value & "'"
        'cmd.CommandText = sql
        '    cmd.ExecuteNonQuery()

        'Next


        sql = "update RT_bg_in_detail set date2='" & my_date & "',matl_name=N'" & my_name & "' where id='" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("id").Value & "'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()




        Dim dt2 As New DataTable
        sql = "SELECT date1 AS 报关日期,date2 as 进厂日期,  NO AS 报关单号, NAME AS 品名, QTY AS 重量, PRICE AS 单价, ORIGIN AS 产地, matl_name as 材料名,id FROM  RT_BG_IN_DETAIL WHERE XNO='" & p(0) & "' and name=N'蓝湿牛二层皮' order by date1 desc"
        cmd.CommandText = sql


        da.SelectCommand = cmd
        da.Fill(dt2)


        DGV_IN.DataSource = dt2





        '  DGV_IN.Refresh()
        MsgBox("更新完成")


    End Sub



    Private Sub DGV_IN_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGV_IN.CellClick

        '  DGV_IN.BeginEdit(True)

        'If DGV_IN.Columns(e.ColumnIndex).Name = "进厂日期" Then

        '    dateTimePicker1 = New DateTimePicker()
        '    'Adding the dateTimePicker1 into DataGridView.
        '    DGV_IN.Controls.Add(dateTimePicker1)
        '    ' Setting the format i.e. mm/dd/yyyy)
        '    dateTimePicker1.Format = DateTimePickerFormat.Short
        '    ' Create retangular area that represents the display area for a cell.
        '    Dim oRectangle As Rectangle = DGV_IN.GetCellDisplayRectangle(e.ColumnIndex, e.RowIndex, True)
        '    ' Setting area for dateTimePicker1.
        '    dateTimePicker1.Size = New Size(oRectangle.Width, oRectangle.Height)
        '    ' Setting location for dateTimePicker1.
        '    dateTimePicker1.Location = New Point(oRectangle.X, oRectangle.Y)


        '    AddHandler dateTimePicker1.TextChanged, AddressOf DateTimePickerChange
        '    AddHandler dateTimePicker1.CloseUp, AddressOf DateTimePickerClose


        '    ' DGV_IN.BeginEdit(True)
        'End If





    End Sub
    Private Sub DateTimePickerChange(ByVal sender As Object, ByVal e As EventArgs)
        'DGV_IN.CurrentCell.Value = dateTimePicker1.Text.ToString()
        'MessageBox.Show(String.Format("Date changed to {0}", dateTimePicker1.Text.ToString()))
    End Sub

    Private Sub DateTimePickerClose(ByVal sender As Object, ByVal e As EventArgs)
        'dateTimePicker1.Visible = False
    End Sub
    Private Sub DGV_IN_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DGV_IN.EditingControlShowing




        'Dim parentCTL As Control = e.Control.Parent



        'Dim dtPicker As New DateTimePicker
        'dtPicker.Name = "dateTimePicker1"
        '' dtPicker.Size = DGV1.CurrentCell.Size

        'dtPicker.CustomFormat = "yyyy-MM-dd"



        'dtPicker.Format = DateTimePickerFormat.Custom
        'dtPicker.Location = New Point(e.Control.Location.X - e.Control.Margin.Left, e.Control.Location.Y - e.Control.Margin.Top)



        'If (e.Control.Text = "") Then

        '    'dtPicker.Value = DateTime.ParseExact(e.Control.Text, dtPicker.CustomFormat, Nothing)

        '    e.Control.Visible = False
        'End If


        'For Each tmpCTL As Control In parentCTL.Controls

        '    If (tmpCTL.Name <> dtPicker.Name) Then parentCTL.Controls.Remove(tmpCTL)

        'Next

        'parentCTL.Controls.Add(dtPicker)





    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click   '-----进出口累计

        If ListBox1.SelectedIndex = -1 Then
            MsgBox("请先选择手册号")
            Exit Sub
        End If

        sql = "SELECT RT_BG_MTL_NAME.NAME AS 品名, RT_BG_IN.QTY AS 审批数量, CAST(SUM(RT_BG_IN_DETAIL.QTY) AS numeric(10, 1)) AS 期初进口 FROM      RT_BG_IN RIGHT OUTER JOIN RT_BG_MTL_NAME ON RT_BG_IN.NAME = RT_BG_MTL_NAME.NAME LEFT OUTER JOIN RT_BG_IN_DETAIL ON RT_BG_MTL_NAME.NAME = RT_BG_IN_DETAIL.NAME WHERE   (RT_BG_IN_DETAIL.XNO='" & p(0) & "') AND (RT_BG_IN_DETAIL.DATE1 < '" & String.Format("{0:d}", dtp3.Value) & "') AND (RT_BG_IN.NO ='" & p(0) & "') GROUP BY RT_BG_MTL_NAME.SERIAL, RT_BG_MTL_NAME.NAME, RT_BG_IN.QTY ORDER BY RT_BG_MTL_NAME.SERIAL"

        Dim dt = New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd

        da.Fill(dt)

        dt.Columns.Add("本期进口")

        sql = "SELECT   RT_BG_MTL_NAME.NAME AS 品名, RT_BG_IN.QTY AS 审批数量, CAST(SUM(RT_BG_IN_DETAIL.QTY) AS numeric(10, 1)) AS 本期进口 FROM      RT_BG_IN RIGHT OUTER JOIN RT_BG_MTL_NAME ON RT_BG_IN.NAME = RT_BG_MTL_NAME.NAME LEFT OUTER JOIN RT_BG_IN_DETAIL ON RT_BG_MTL_NAME.NAME = RT_BG_IN_DETAIL.NAME WHERE   (RT_BG_IN_DETAIL.XNO='" & p(0) & "') AND (RT_BG_IN_DETAIL.DATE1 >= '" & String.Format("{0:d}", dtp3.Value) & "') AND                (RT_BG_IN.NO ='" & p(0) & "') GROUP BY RT_BG_MTL_NAME.SERIAL, RT_BG_MTL_NAME.NAME, RT_BG_IN.QTY ORDER BY RT_BG_MTL_NAME.SERIAL"

        Dim dt1 = New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd

        da.Fill(dt1)

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


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

                If dt.Rows(x).Item(0) = dt1.Rows(y).Item(0) Then

                    dt.Rows(x).Item("本期进口") = dt1.Rows(y).Item("本期进口")

                End If

            Next

        Next

        dgv_chem_1.DataSource = dt



        sql = "SELECT   NAME, CAST(SUM(QTY) AS NUMERIC(10, 1)) AS 期初出口
FROM      (SELECT   TOP (100) PERCENT RT_BG_MTL_NAME.NAME, RT_BG_OUT.QTY, RT_BG_OUT.TOTAL, RT_BG_OUT.date1, 
                                 RT_BG_OUT.DES, RT_BG_OUT.COMMENT
                 FROM      RT_BG_MTL_NAME INNER JOIN
                                 RT_BG_OUT ON RT_BG_MTL_NAME.NAME = RT_BG_OUT.NAME
                 WHERE   (RT_BG_MTL_NAME.SERIAL > 16) AND XNO='" & p(0) & "' AND (RT_BG_OUT.DATE1 < '" & String.Format("{0:d}", dtp3.Value) & "') ORDER BY RT_BG_MTL_NAME.SERIAL) AS P
GROUP BY NAME"


        Dim DT2 = New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd

        da.Fill(DT2)

        DT2.Columns.Add("本期出口")
        sql = "SELECT   NAME,CAST(SUM(QTY) AS NUMERIC(10, 1)) AS 本期出口
FROM      (SELECT   TOP (100) PERCENT RT_BG_MTL_NAME.NAME, RT_BG_OUT.QTY, RT_BG_OUT.TOTAL, RT_BG_OUT.date1, 
                                 RT_BG_OUT.DES, RT_BG_OUT.COMMENT
                 FROM      RT_BG_MTL_NAME INNER JOIN
                                 RT_BG_OUT ON RT_BG_MTL_NAME.NAME = RT_BG_OUT.NAME
                 WHERE   (RT_BG_MTL_NAME.SERIAL > 16) AND XNO='" & p(0) & "' AND (RT_BG_OUT.DATE1 >= '" & String.Format("{0:d}", dtp3.Value) & "') ORDER BY RT_BG_MTL_NAME.SERIAL) AS P
GROUP BY NAME"
        Dim DT3 = New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(DT3)


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


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

                If DT2.Rows(x).Item(0) = DT3.Rows(y).Item(0) Then

                    DT2.Rows(x).Item("本期出口") = DT3.Rows(y).Item("本期出口")

                End If

            Next

        Next




        dgv_chem_2.DataSource = DT2










    End Sub

    Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged

    End Sub



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

    End Sub

    Private Sub DGV_IN_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGV_IN.CellContentClick

    End Sub

    Private Sub DGV_IN_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles DGV_IN.MouseDoubleClick




        sql = "update RT_bg_in_detail set bg_date=N'" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("报关日期").Value & "',DATE1=N'" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("报关日期").Value & "',DATE2=N'" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("进厂日期").Value & "',no=N'" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("报关单号").Value & "',name=N'" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("品名").Value & "',qty=N'" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("重量").Value & "',price='" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("单价").Value & "',origin=N'" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("产地").Value & "',matl_name=N'" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("材料名").Value & "' where id='" & DGV_IN.Rows(DGV_IN.CurrentCell.RowIndex).Cells("id").Value & "'"
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()



    End Sub

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

        sql = "select date1 as 报关日期, no as 报关单号,xno as 手册号, name as 海关分类, qty as 数量, matl_name as 材料名 from rt_bg_in_detail where date2 is null order by name"
        cmd.CommandText = sql
        Dim dt As New DataTable
        da.SelectCommand = cmd
        da.Fill(dt)

        DataGridView1.DataSource = dt

        DataGridView1.Columns(1).Width = 180
        DataGridView1.Columns(2).Width = 120
        DataGridView1.Columns(4).Width = 80
        DataGridView1.Columns(5).Width = 200



    End Sub

    Private Sub DGV1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGV1.CellContentClick

    End Sub
End Class