outform.vb

vb.net+sql进销存管理系统

        Me.Label2.Text = "商品"
        Me.Label2.TextAlign = System.Drawing.ContentAlignment.MiddleLeft
        '
        'dtPicker
        '
        Me.dtPicker.Location = New System.Drawing.Point(40, 64)
        Me.dtPicker.Name = "dtPicker"
        Me.dtPicker.Size = New System.Drawing.Size(120, 21)
        Me.dtPicker.TabIndex = 2
        '
        'tbDate
        '
        Me.tbDate.Location = New System.Drawing.Point(72, 40)
        Me.tbDate.Name = "tbDate"
        Me.tbDate.ReadOnly = True
        Me.tbDate.Size = New System.Drawing.Size(64, 21)
        Me.tbDate.TabIndex = 1
        Me.tbDate.Text = ""
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(72, 16)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(56, 23)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "日期"
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleLeft
        '
        'GroupBox2
        '
        Me.GroupBox2.Controls.Add(Me.rbMod)
        Me.GroupBox2.Controls.Add(Me.rbAdd)
        Me.GroupBox2.Location = New System.Drawing.Point(176, 56)
        Me.GroupBox2.Name = "GroupBox2"
        Me.GroupBox2.Size = New System.Drawing.Size(136, 35)
        Me.GroupBox2.TabIndex = 21
        Me.GroupBox2.TabStop = False
        '
        'rbMod
        '
        Me.rbMod.Location = New System.Drawing.Point(72, 8)
        Me.rbMod.Name = "rbMod"
        Me.rbMod.Size = New System.Drawing.Size(48, 24)
        Me.rbMod.TabIndex = 1
        Me.rbMod.Text = "修改"
        '
        'rbAdd
        '
        Me.rbAdd.Checked = True
        Me.rbAdd.Location = New System.Drawing.Point(8, 8)
        Me.rbAdd.Name = "rbAdd"
        Me.rbAdd.Size = New System.Drawing.Size(48, 24)
        Me.rbAdd.TabIndex = 0
        Me.rbAdd.TabStop = True
        Me.rbAdd.Text = "增加"
        '
        'OutForm
        '
        Me.AcceptButton = Me.btOK
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.CancelButton = Me.btCancel
        Me.ClientSize = New System.Drawing.Size(492, 374)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.dgOut)
        Me.Name = "OutForm"
        Me.Text = "出库管理"
        CType(Me.dgOut, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox2.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim cOld As Int16 = 0

    Private Sub InForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.BindDg()

        Dim dt As DataTable = Sample02.GetDataTable("SELECT CATEGORYNAME FROM CATEGORY ORDER BY CATEGORYNAME")

        For Each dr As DataRow In dt.Rows
            Me.cbGoods.Items.Add(dr(0))
        Next

        Me.cbGoods.Text = String.Empty
        Me.tbHandler.Text = Sample02.UserId

        Me.SetDefault()
    End Sub

    Private Sub eItemMod_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles eItemMod.Click
        Try

            Dim Row As Int16 = Me.dgOut.CurrentCell.RowNumber

            Me.lbNo.Text = Me.dgOut.Item(Row, 0)
            Me.tbDate.Text = Me.dgOut.Item(Row, 1)
            Me.cbGoods.Text = Me.dgOut.Item(Row, 2)
            Me.tbCount.Text = Me.dgOut.Item(Row, 3)

            If Me.dgOut.Item(Row, 5) Is System.DBNull.Value Then
                Me.tbRemark.Text = String.Empty
            Else
                Me.tbRemark.Text = Me.dgOut.Item(Row, 5)
            End If

            Me.rbMod.Checked = True
            Me.cOld = Convert.ToInt16(Me.tbCount.Text.Trim())

        Catch ex As Exception

            Sample02.WriteErr(ex)

        End Try
    End Sub

    Private Sub dtPicker_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtPicker.ValueChanged
        Me.tbDate.Text = Me.dtPicker.Value.ToShortDateString().Trim()
    End Sub

    Private Sub rbAdd_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbAdd.CheckedChanged
        If rbAdd.Checked Then
            Me.lbNo.Text = String.Empty
            Me.btOK.Text = "增加"
        Else
            Me.btOK.Text = "更新"
        End If
    End Sub

    Private Sub rbMod_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbMod.CheckedChanged
        If rbAdd.Checked Then
            Me.lbNo.Text = String.Empty
            Me.btOK.Text = "增加"
        Else
            Me.btOK.Text = "更新"
        End If
    End Sub

    Private Sub btOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btOK.Click
        Dim no As String = Me.lbNo.Text.Trim()
        Dim sDate As String = Me.tbDate.Text.Trim()
        Dim Goods As String = Me.cbGoods.Text.Trim()
        Dim Count As String = Me.tbCount.Text.Trim()
        Dim Remark As String = Me.tbRemark.Text.Trim()

        Dim sCount As Int16 = 0
        Dim Remain As Int16 = 0

        If Me.rbMod.Checked And no = String.Empty Then
            MessageBox.Show("请选择一行进行修改")
            Return
        End If

        If Goods = String.Empty Then
            MessageBox.Show("商品不能为空")
            Me.cbGoods.Focus()
            Return
        End If

        Remain = Me.GetRemain(Me.GetGoodsNo(Goods.Trim()))

        If Count = String.Empty Then
            MessageBox.Show("数量不能为空")
            Me.tbCount.Focus()
            Return
        End If

        Try
            sCount = Int16.Parse(Count)
        Catch
            MessageBox.Show("数量格式不对")
            Me.tbCount.Focus()
            Return
        End Try

        If sCount <= 0 Then
            MessageBox.Show("数量必须为正整数")
            Me.tbCount.Focus()
            Return
        End If

        If Remain + Me.cOld - sCount < 0 Then
            MessageBox.Show("库存不足")
            Me.tbCount.Focus()
            Return
        End If

        Dim Sql_Add As String = "INSERT INTO OUTNOTE (OUTDATE, CATEGORY, OUTCOUNT, HANDLER, REMARK) VALUES(" + sDate + "," + Me.GetGoodsNo(Goods) + "," + Count + ",'" + Sample02.UserId.Trim() + "','" + Remark + "')"
        Dim Sql_Mod As String = "UPDATE OUTNOTE SET OUTDATE = " + sDate + ", CATEGORY=" + Me.GetGoodsNo(Goods) + ", OUTCOUNT=" + Count + ", HANDLER='" + Sample02.UserId.Trim() + "', REMARK='" + Remark + "' WHERE INNO = '" + no + "'"
        Dim Sql_R As String = "UPDATE STORE SET GOODSCOUNT = " + (Remain + Me.cOld - sCount).ToString().Trim() + " WHERE CATEGORYNO = " + Me.GetGoodsNo(Goods)

        Dim SqlCmd As New SqlCommand
        Dim SqlCmd_R As New SqlCommand

        SqlCmd.Connection = Sample02.SqlCon
        SqlCmd_R.Connection = Sample02.SqlCon

        SqlCmd_R.CommandText = Sql_R

        Try
            Sample02.SqlCon.Open()

            If Me.rbAdd.Checked Then
                SqlCmd.CommandText = Sql_Add
            Else
                SqlCmd.CommandText = Sql_Mod
            End If

            SqlCmd_R.ExecuteNonQuery()
            SqlCmd.ExecuteNonQuery()

            MessageBox.Show(Me.btOK.Text.Trim() + "成功")

            Me.BindDg()
            Me.SetDefault()

        Catch ex As Exception
            Sample02.WriteErr(ex)
            MessageBox.Show(Me.btOK.Text.Trim() + "失败")
        Finally
            Sample02.SqlCon.Close()
        End Try
    End Sub

    Private Sub btBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btBack.Click
        Me.SetDefault()
    End Sub

    Private Sub btCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btCancel.Click
        Me.Close()
    End Sub

    Private Sub BindDg()

        Dim dtNew As DataTable
        Dim SqlAda As New SqlDataAdapter
        Dim SqlCmd As New SqlCommand("SELECT * FROM INVIEW", Sample02.SqlCon)

        Dim ds As New DataSet

        Try
            SqlAda.SelectCommand = SqlCmd
            ds.Clear()
            SqlAda.Fill(ds)

            Me.dgOut.TableStyles(0).MappingName = ds.Tables(0).TableName.Trim()
            Me.dgOut.DataSource = ds.Tables(0)
            Me.dgOut.CurrentCell = New DataGridCell(0, 1)

        Catch ex As Exception
            Sample02.WriteErr(ex)
        End Try

    End Sub

    Private Sub SetDefault()
        Me.lbNo.Text = String.Empty
        Me.rbAdd.Checked = True
        Me.dtPicker.Value = System.DateTime.Now()
        Me.tbDate.Text = System.DateTime.Now.ToShortDateString()
        Me.cbGoods.Text = String.Empty
        Me.tbCount.Text = String.Empty
        Me.tbRemark.Text = String.Empty
    End Sub

    Private Function GetGoodsNo(ByVal GoodsName As String) As String

        Dim no As String = String.Empty

        Try

            Dim sql As String = "SELECT CATEGORYNO FROM CATEGORY WHERE CATEGORYNAME = '" + GoodsName.Trim() + "'"
            Dim dt As DataTable = Sample02.GetDataTable(sql)

            If dt.Rows.Count <> 0 Then
                no = dt.Rows(0)(0).ToString().Trim()
            End If
        Catch ex As Exception
            Sample02.WriteErr(ex)
            no = String.Empty
        End Try

        Return no

    End Function


    Private Function GetRemain(ByVal GoodsNo As String) As Int16
        Dim Remain As Int16 = 0

        Try

            Dim sql As String = "SELECT GOODSCOUNT FROM STORE WHERE CATEGORYNO = '" + GoodsNo.Trim() + "'"
            Dim dt As DataTable = Sample02.GetDataTable(sql)

            If dt.Rows.Count <> 0 Then
                Remain = Convert.ToInt16(dt.Rows(0)(0))
            End If
        Catch ex As Exception
            Sample02.WriteErr(ex)
            Remain = 0
        End Try

        Return Remain

    End Function
End Class