pur.vb

本系统利用vb.net前台开发工具sql.server做为后台数据库进行开超市进销存管理系统系统

Imports System.Data.SqlClient
Public Class Pur
    Dim myconstr As String = SCMDB.SCMDB.connStr
    Dim myconn As SqlConnection
    Dim mycmd As SqlCommand
    Dim myread As SqlDataReader
    Dim dbObj As New SCMDB.SCMDB
    Public Function Refresh() As DataTable
        Return dbObj.CreateDataTableByKey("purlistid as 采购单编号,prdid as  " & _
            "商品编号,supid as 供应商编号,purdat as 采购日期,puremp as " & _
            "经手人, purqua as 采购数量,purprc as 采购价格,purmemo as " & _
            "采购说明", "purchaseproduct")
    End Function
    Public Function AddPur(ByVal PrdId As String, ByVal SupId As String, ByVal _
            PurDat As DateTime, ByVal PurEmp As String, ByVal PurQua As String, _
            ByVal PurPrc As String, ByVal PurMemo As String) As Boolean
        '增加采购单
        If PurQua = "" Then
            '采购数量不能为空
            MsgBox("商品数量不能为空！", MsgBoxStyle.Exclamation)
            Exit Function
        Else
            If PurDat > Today Then
                '采购日期不能超过当日
                MsgBox("采购日期有误！", MsgBoxStyle.Exclamation)
                Exit Function
            Else
                If PurPrc = "" Then
                    '采购价格不能为空
                    MsgBox("商品价格不能为空！", MsgBoxStyle.Exclamation)
                    Exit Function
                Else
                    Try
                        '利用存储过程更新PurchaseProduct数据表和ProductList数据表
                        Dim mypar(6) As SqlParameter
                        myconn = New SqlConnection(myconstr)
                        myconn.Open()
                        '执行存储过程spAddPur
                        mycmd = New SqlCommand("spAddPur", myconn)
                        mycmd.CommandType = CommandType.StoredProcedure
                        '定义存储过程的七个参数，包括类型和值
                        mypar(0) = mycmd.Parameters.Add("@prdid", SqlDbType.Int)
                        mypar(0).Value = Val(PrdId)
                        mypar(1) = mycmd.Parameters.Add("@supid", SqlDbType.Int)
                        mypar(1).Value = Val(SupId)
                        mypar(2) = mycmd.Parameters.Add("@purdat", _
                            SqlDbType.SmallDateTime)
                        mypar(2).Value = PurDat
                        mypar(3) = mycmd.Parameters.Add("@puremp", SqlDbType.VarChar)
                        mypar(3).Value = PurEmp
                        mypar(4) = mycmd.Parameters.Add("@purqua", SqlDbType.SmallInt)
                        mypar(4).Value = Val(PurQua)
                        mypar(5) = mycmd.Parameters.Add("@purprc", SqlDbType.SmallMoney)
                        mypar(5).Value = Val(PurPrc)
                        mypar(6) = mycmd.Parameters.Add("@purmemo", SqlDbType.VarChar)
                        mypar(6).Value = PurMemo
                        myread = mycmd.ExecuteReader
                        Return True
                    Catch ee As Exception
                        MsgBox("数据更新失败！失败原因：" & ee.Message, _
                                MsgBoxStyle.Exclamation)
                        Return False
                    End Try
                End If
            End If
        End If
    End Function
    Public Function DelPur(ByVal ListId As String) As Boolean
        '删除采购单
        Try
            Dim mypar As SqlParameter
            myconn = New SqlConnection(myconstr)
            myconn.Open()
            mycmd = New SqlCommand("spDelPur", myconn)
            mycmd.CommandType = CommandType.StoredProcedure
            mypar = mycmd.Parameters.Add("@purlistid", SqlDbType.Int)
            mypar.Value = Val(ListId)
            myread = mycmd.ExecuteReader
            Return True
        Catch ee As Exception
            MsgBox("数据删除失败！失败原因：" & ee.Message, MsgBoxStyle.Exclamation)
            Return False
        End Try
    End Function
    Public Function EdtPur(ByVal ListId As String, ByVal PrdId As String, ByVal _
            SupId As String, ByVal PurDat As DateTime, ByVal PurEmp As String, _
            ByVal PurQua As String, ByVal PurPrc As String, ByVal PurMemo As _
            String) As Boolean
        '编辑采购单
        If PurQua = "" Then
            MsgBox("商品数量不能为空！", MsgBoxStyle.Exclamation)
            Exit Function
        Else
            If PurDat > Today Then
                MsgBox("采购日期有误！", MsgBoxStyle.Exclamation)
                Exit Function
            Else
                If PurPrc = "" Then
                    MsgBox("商品价格不能为空！", MsgBoxStyle.Exclamation)
                    Exit Function
                Else
                    Try
                        Dim mypar(7) As SqlParameter
                        myconn = New SqlConnection(myconstr)
                        myconn.Open()
                        mycmd = New SqlCommand("spEdtPur", myconn)
                        mycmd.CommandType = CommandType.StoredProcedure
                        mypar(0) = mycmd.Parameters.Add("@purlistid", SqlDbType.Int)
                        mypar(0).Value = Val(ListId)
                        mypar(1) = mycmd.Parameters.Add("@prdid", SqlDbType.Int)
                        mypar(1).Value = Val(Trim(PrdId))
                        mypar(2) = mycmd.Parameters.Add("@supid", SqlDbType.Int)
                        mypar(2).Value = Val(Trim(SupId))
                        mypar(3) = mycmd.Parameters.Add("@purdat", SqlDbType.SmallDateTime)
                        mypar(3).Value = PurDat
                        mypar(4) = mycmd.Parameters.Add("@puremp", SqlDbType.VarChar)
                        mypar(4).Value = PurEmp
                        mypar(5) = mycmd.Parameters.Add("@purqua", SqlDbType.SmallInt)
                        mypar(5).Value = Val(PurQua)
                        mypar(6) = mycmd.Parameters.Add("@purprc", SqlDbType.SmallMoney)
                        mypar(6).Value = Val(PurPrc)
                        mypar(7) = mycmd.Parameters.Add("@purmemo", SqlDbType.VarChar)
                        mypar(7).Value = PurMemo
                        myread = mycmd.ExecuteReader
                        Return True
                    Catch ee As Exception
                        MsgBox("数据更新失败！失败原因：" & ee.Message, _
                            MsgBoxStyle.Exclamation)
                    End Try
                End If
            End If
        End If
    End Function

    Public Function SupIdToNam(ByVal ID As String) As String
        '以供应商编号为关键字查询供应商名称
        Dim dt As DataTable = New DataTable
        dt = dbObj.CreateDataTableByKey("supname", "supplierlist", "supid=" & ID)
        '返回查询结果
        Return dt.Rows(0).Item(0)
    End Function
    Public Function PrdIdToNam(ByVal Id As String) As String
        '以商品编号为关键字查询商品名称
        Dim dt As DataTable = New DataTable
        dt = dbObj.CreateDataTableByKey("prdname", "productlist", "prdid=" & Id)
        Return dt.Rows(0).Item(0)
    End Function
End Class