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

Imports System.Data.SqlClient
Imports System.Reflection
Public Class Frm_AC
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter

    Dim sql As String
    Dim conn As New SqlConnection
    Private Sub Frm_AC_Load(sender As Object, e As EventArgs) Handles MyBase.Load


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


        conn.ConnectionString = connstring
        conn.Open()



        cmd.Connection = conn



        ListBox1.Items.Add("蓝湿牛二层皮")
        ListBox1.Items.Add("制革染料")
        ListBox1.Items.Add("无机鞣料")
        ListBox1.Items.Add("有机鞣料")
        ListBox1.Items.Add("皮革整理剂")
        ListBox1.Items.Add("表面活性剂")
        ListBox1.Items.Add("合成油脂")
        ListBox1.Items.Add("聚氨酯混合液")
        ListBox1.Items.Add("皮革促进剂")
        ListBox1.Items.Add("合成油脂(矿)")
        ListBox1.Items.Add("复鞣助剂")
        ListBox1.Items.Add("PU树脂")
        ListBox1.Items.Add("牛二层皮1")
        ListBox1.Items.Add("牛二层皮2")
        ListBox1.Items.Add("牛二层皮3")
        ListBox1.Items.Add("牛二层皮4")
        ListBox1.Items.Add("牛二层皮5")
        ListBox1.Items.Add("牛二层皮6")
        ListBox1.Items.Add("PU牛二层皮")




    End Sub

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

        Dim my_item As String

        If Not (ListBox1.Items(ListBox1.SelectedIndex) Like "*蓝湿*") And ListBox1.Items(ListBox1.SelectedIndex) Like "*牛二层*" Then


            Select Case ListBox1.Items(ListBox1.SelectedIndex)
                Case "牛二层皮1"
                    my_item = "牛二层反绒皮"
                Case "牛二层皮2"
                    my_item = "牛二层反绒皮2"
                Case "牛二层皮3"
                    my_item = "牛二层反绒皮3"
                Case "牛二层皮4"
                    my_item = "牛二层反绒皮4"
                Case "PU牛二层皮"
                    my_item = "牛二层PU革"

            End Select




            sql = "SELECT   DATE AS 日期, NO AS 单号, M_NAME AS 品名, QTY AS 数量,XNO AS 手册号 FROM  RT_AC_finish_in WHERE   (M_NAME =N'" & my_item & "') ORDER BY DATe,no"
            cmd.CommandText = sql
            da.SelectCommand = cmd

            Dim DT As New DataTable

            da.Fill(DT)

            DT.Columns.Add("期初").SetOrdinal(3)

            DT.Columns.Add("结存").SetOrdinal(5)


            Dim Q_BEGIN As Decimal = 0

            Select Case ListBox1.Items(ListBox1.SelectedIndex)
                Case "牛二层皮1"
                    Q_BEGIN = Val(TXT_FM1.Text)
                Case "牛二层皮2"
                    Q_BEGIN = Val(TXT_FM2.Text)
                Case "牛二层皮3"
                    Q_BEGIN = Val(TXT_FM3.Text)
                Case "牛二层皮4"
                    Q_BEGIN = Val(TXT_FM4.Text)

                Case "PU牛二层皮"
                    Q_BEGIN = Val(TXT_PU_FINISH.Text)

            End Select


            Dim Q_END As Decimal = 0
            For X As Integer = 0 To DT.Rows.Count - 1

                DT.Rows(X).Item("期初") = Q_BEGIN


                If DT.Rows(X).Item("单号").ToString Like "*OUT*" Then
                    DT.Rows(X).Item("结存") = Q_BEGIN - DT.Rows(X).Item("数量")
                Else

                    DT.Rows(X).Item("结存") = Q_BEGIN + DT.Rows(X).Item("数量")
                End If

                'DT.Rows(X).Item("结存") = Q_BEGIN + DT.Rows(X).Item("数量")
                Q_BEGIN = DT.Rows(X).Item("结存")


            Next

            DGV1.DataSource = DT


            Dim dt1 As New DataTable

            dt1.Columns.Add("日期")
            dt1.Columns.Add("单号")
            dt1.Columns.Add("品名")
            dt1.Columns.Add("期初")
            dt1.Columns.Add("数量")
            dt1.Columns.Add("结存")


            Dim myRow As DataRow


            For x As Integer = 0 To DT.Rows.Count - 1
                If Val(DT.Rows(x).Item("结存")) < 0 Then
                    myRow = dt1.NewRow
                    myRow(0) = DT.Rows(x).Item("日期")
                    myRow(1) = DT.Rows(x).Item("单号")

                    myRow(2) = DT.Rows(x).Item("品名")
                    myRow(3) = DT.Rows(x).Item("期初")
                    myRow(4) = DT.Rows(x).Item("数量")
                    myRow(5) = DT.Rows(x).Item("结存")

                    dt1.Rows.InsertAt(myRow, dt1.Rows.Count)
                End If

            Next

            dgv2.DataSource = dt1



        Else
            sql = "SELECT   DATE AS 日期, NO AS 单号, M_NAME AS 品名, QTY AS 数量,XNO AS 手册号 FROM  RT_AC_M_IN WHERE   (M_NAME =N'" & ListBox1.Items(ListBox1.SelectedIndex) & "') ORDER BY DATE,no"
            cmd.CommandText = sql
        da.SelectCommand = cmd

        Dim DT As New DataTable

        da.Fill(DT)

        DT.Columns.Add("期初").SetOrdinal(3)

        DT.Columns.Add("结存").SetOrdinal(5)


        Dim Q_BEGIN As Decimal = 0

        Select Case ListBox1.Items(ListBox1.SelectedIndex)
            Case "蓝湿牛二层皮"
                Q_BEGIN = Val(TXT_WB.Text)
            Case "制革染料"
                Q_BEGIN = Val(TXT_DYE.Text)
            Case "无机鞣料"
                Q_BEGIN = Val(TXT_TAN_0.Text)
            Case "有机鞣料"
                Q_BEGIN = Val(TXT_TAN_1.Text)
            Case "皮革整理剂"
                Q_BEGIN = Val(TXT_AU.Text)
            Case "表面活性剂"
                Q_BEGIN = Val(TXT_AU1.Text)
            Case "合成油脂"
                Q_BEGIN = Val(TXT_OIL1.Text)
            Case "聚氨酯混合液"
                Q_BEGIN = Val(TXT_PU_FINISH.Text)
            Case "皮革促进剂"
                Q_BEGIN = Val(TXT_PU_AU.Text)
            Case "合成油脂(矿)"
                Q_BEGIN = Val(TXT_OIL0.Text)
            Case "复鞣助剂"
                Q_BEGIN = Val(TXT_TAN_2.Text)
            Case "PU树脂"
                Q_BEGIN = Val(TXT_PU.Text)

        End Select


        Dim Q_END As Decimal = 0
        For X As Integer = 0 To DT.Rows.Count - 1

            DT.Rows(X).Item("期初") = Q_BEGIN


            If DT.Rows(X).Item("单号").ToString Like "*OUT*" Then
                DT.Rows(X).Item("结存") = Q_BEGIN - DT.Rows(X).Item("数量")
            Else

                DT.Rows(X).Item("结存") = Q_BEGIN + DT.Rows(X).Item("数量")
            End If

            'DT.Rows(X).Item("结存") = Q_BEGIN + DT.Rows(X).Item("数量")
            Q_BEGIN = DT.Rows(X).Item("结存")


        Next

        DGV1.DataSource = DT


        Dim dt1 As New DataTable

        dt1.Columns.Add("日期")
        dt1.Columns.Add("单号")
        dt1.Columns.Add("品名")
        dt1.Columns.Add("期初")
        dt1.Columns.Add("数量")
        dt1.Columns.Add("结存")


        Dim myRow As DataRow


        For x As Integer = 0 To DT.Rows.Count - 1
            If Val(DT.Rows(x).Item("结存")) < 0 Then
                myRow = dt1.NewRow
                myRow(0) = DT.Rows(x).Item("日期")
                myRow(1) = DT.Rows(x).Item("单号")

                myRow(2) = DT.Rows(x).Item("品名")
                myRow(3) = DT.Rows(x).Item("期初")
                myRow(4) = DT.Rows(x).Item("数量")
                myRow(5) = DT.Rows(x).Item("结存")

                dt1.Rows.InsertAt(myRow, dt1.Rows.Count)
            End If

        Next

        dgv2.DataSource = dt1

        End If


    End Sub
End Class