机房重构结账之存储过程

小咪咪 2022-06-08 06:11 259阅读 0赞

机房重构实现结账的时候用到了存储过程，其实开始的时候我并没有用存储过程，然后敲到需要更新表的时候顿时懵了，oh，my god ！ So many tables 。 于是我又从头再来，开始使用存储过程。这也告诉我一定要先把思路理清楚了，把思路逻辑写下来，不要一边想一边敲。

接下来是部分代码展示：

UI：

Private Sub combOperatorID_SelectedIndexChanged(sender As Object, e As EventArgs) Handles combOperatorID.SelectedIndexChanged
            Dim facade As New Facade.SettleAccountFacade
            Dim myList As New List(Of Entity.UserEntity)
            Dim ht As New Hashtable()  '使用哈希表保存操作员ID和姓名，键值对
    
            myList = facade.SelectOperator()
            For i = 0 To myList.Count - 1
                ht.Add(myList(i).UserID.Trim(), myList(i).UserName.Trim())
            Next
    
            If ht.Contains(combOperatorID.Text.Trim()) Then
                txtOperatorName.Text = ht(combOperatorID.Text)
            End If
    
            Dim enBillInfo As New Entity.BillEntity
            enBillInfo.RechargeCash = 0
            enBillInfo.CancelCash = 0
            enBillInfo.CheckDate = ""
            enBillInfo.CheckTime = ""
            enBillInfo.Head = ""
    
            '购卡：
            enBillInfo.Count = 1
            Dim dtCard As New DataTable
            Dim myListCard As New List(Of Entity.CardEntity)
            enBillInfo.WorkerID = combOperatorID.Text.Trim()
            dtCard = facade.SelectInfo(enBillInfo)
            If dtCard.Rows.Count > 0 Then
                myListCard = ConvertHelper.ConvertToList(Of Entity.CardEntity)(dtCard)
            End If
            dgvRegisteCard.DataSource = myListCard
            dgvRegisteCard.Columns.Remove("Cash")
            dgvRegisteCard.Columns.Remove("Status")
            dgvRegisteCard.Columns.Remove("UserID")
            dgvRegisteCard.Columns.Remove("IsCheck")
            Entity.SettleAccountEntity.RegisteNum = myListCard.Count  '售卡张数
    
    
            '充值：
            enBillInfo.Count = 2
            Dim dtRechargeInfo As New DataTable
            Dim myListRecharge As New List(Of Entity.RechargeEntity)
            enBillInfo.WorkerID = combOperatorID.Text.Trim()
            dtRechargeInfo = facade.SelectInfo(enBillInfo)
            If dtRechargeInfo.Rows.Count > 0 Then
                myListRecharge = ConvertHelper.ConvertToList(Of Entity.RechargeEntity)(dtRechargeInfo)
            End If
            dgvRecharge.DataSource = myListRecharge
            dgvRecharge.Columns.Remove("IsCheck")
            dgvRecharge.Columns.Remove("UserID")
            dgvRecharge.Columns.Remove("BeginDate")
            dgvRecharge.Columns.Remove("EndDate")
            Entity.SettleAccountEntity.RechargeNum = myListRecharge.Count  '充值张数
    
            Dim sumRecharge As Double
            For i = 0 To myListRecharge.Count - 1
                sumRecharge += myListRecharge(i).RechargeCash
            Next
            SettleAccountEntity.RechargeCash = sumRecharge  '充值金额
    
            '退卡：
            enBillInfo.Count = 3
            Dim dtReturnCard As New DataTable
            Dim myListCancelCard As New List(Of Entity.CancelCardEntity)
            enBillInfo.WorkerID = combOperatorID.Text.Trim()
            dtReturnCard = facade.SelectInfo(enBillInfo)
            If dtReturnCard.Rows.Count > 0 Then
                myListCancelCard = ConvertHelper.ConvertToList(Of Entity.CancelCardEntity)(dtReturnCard)
            End If
            dgvCancelCard.DataSource = myListCancelCard
            dgvCancelCard.Columns.Remove("IsCheck")
            dgvCancelCard.Columns.Remove("UserID")
            dgvCancelCard.Columns.Remove("BeginDate")
            dgvCancelCard.Columns.Remove("EndDate")
            Entity.SettleAccountEntity.ReturnCardNum = myListCancelCard.Count '退卡张数
            Dim sumReturn As Double
            For i = 0 To myListCancelCard.Count - 1
                sumReturn += myListCancelCard(i).ReturnCash
            Next
            Entity.SettleAccountEntity.ReturnCash = sumReturn   '退卡金额
    
            '汇总：
            txtRegisteCardNum.Text = Entity.SettleAccountEntity.RegisteNum     '售卡张数
            txtCancelCardNum.Text = Entity.SettleAccountEntity.ReturnCardNum   '退卡张数
            txtRechargeCardNum.Text = Entity.SettleAccountEntity.RechargeNum    '充值张数          
            txtRecharge.Text = Entity.SettleAccountEntity.RechargeCash   '充值金额
            txtCancelCardCash.Text = Entity.SettleAccountEntity.ReturnCash   '退卡金额
            txtSettleCash.Text = SettleAccountEntity.RechargeCash - SettleAccountEntity.ReturnCash   '应收金额
    
        End Sub
    
        Private Sub TabControlAccount_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControlAccount.SelectedIndexChanged
            '退出：
            If TabControlAccount.SelectedIndex = 4 Then
                SettleAccount.Close()
            End If
        End Sub
    
        Private Sub btnSettleAccount_Click(sender As Object, e As EventArgs) Handles btnSettleAccount.Click
            Dim facade As New Facade.SettleAccountFacade
            Dim enBillInfo As New Entity.BillEntity
            Dim flag As Boolean
    
            enBillInfo.RechargeCash = txtRecharge.Text.Trim()
            enBillInfo.CancelCash = txtCancelCardCash.Text.Trim()
            enBillInfo.CheckDate = DateTime.Now.ToString("yyyy-MM-dd")
            enBillInfo.CheckTime = DateTime.Now.ToString("HH:mm:ss")
            enBillInfo.WorkerID = combOperatorID.Text.Trim()
            enBillInfo.Head = CommonVariable.UserID
            enBillInfo.Count = 4
            flag = facade.UpdateInfo(enBillInfo)
            If flag = True Then
                MsgBox("结账成功！")
            Else
                MsgBox("结账失败！")
            End If
        End Sub
    End Class

Facade：

Public Class SettleAccountFacade
        Public Function SelectOperator() As List(Of Entity.UserEntity)
            Dim selectOperatorBll As New BLL.SettleAccountBLL
            Return selectOperatorBll.SelectOperator()
        End Function
    
        Public Function SelectInfo(ByVal enBillInfo As Entity.BillEntity) As DataTable
            Dim selectInfoBll As New BLL.SettleAccountBLL
            Return selectInfoBll.SelectInfo(enBillInfo)
        End Function
    
        Public Function UpdateInfo(ByVal enBillInfo As Entity.BillEntity) As Boolean
            Dim updateInfoBll As New BLL.SettleAccountBLL
            Return updateInfoBll.UpdateInfo(enBillInfo)
        End Function
    End Class

BLL：

Public Class SettleAccountBLL
        Public Function SelectOperator() As List(Of Entity.UserEntity)
            Dim factory As New Factory.SettleAccountFactory
            Dim IselectOperator As IDAL.ISettleAccount
            IselectOperator = factory.CreateISettleAccount()
            Return IselectOperator.SelectOperator()
        End Function
    
        Public Function SelectInfo(ByVal enBillInfo As Entity.BillEntity) As DataTable
            Dim factory As New Factory.SettleAccountFactory
            Dim IselectInfo As IDAL.ISettleAccount
            Dim dt As New DataTable
    
            IselectInfo = factory.CreateISettleAccount()
            dt = IselectInfo.SelectInfo(enBillInfo)
            Return dt
        End Function
    
        Public Function UpdateInfo(ByVal enBillInfo As Entity.BillEntity) As Boolean
            Dim factory As New Factory.SettleAccountFactory
            Dim IupdateInfo As IDAL.ISettleAccount
            Dim flag As Boolean
    
            IupdateInfo = factory.CreateISettleAccount()
            flag = IupdateInfo.UpdateInfo(enBillInfo)
            Return flag
        End Function
    End Class

IDAL：

Public Interface ISettleAccount
        Function SelectOperator() As List(Of Entity.UserEntity)
        Function SelectInfo(ByVal enBillInfo As Entity.BillEntity) As DataTable
        Function UpdateInfo(ByVal enBillInfo As Entity.BillEntity) As Boolean
    End Interface

DAL：

Public Class SQLserverSettleAccountDAL : Implements IDAL.ISettleAccount
        Dim sqlhelper As SQLHelper.SqlHelper = New SQLHelper.SqlHelper
    
        Public Function SelectInfo(enBillInfo As BillEntity) As DataTable Implements ISettleAccount.SelectInfo
            Dim sql As String = "PROC_SettleAccount"
            Dim dt As New DataTable
    
            Dim sqlParams As SqlParameter() = {New SqlParameter("@RechargeCash", enBillInfo.RechargeCash),
                                              New SqlParameter("@CancelCash", enBillInfo.CancelCash),
                                              New SqlParameter("@CheckDate", enBillInfo.CheckDate),
                                              New SqlParameter("@CheckTime", enBillInfo.CheckTime),
                                              New SqlParameter("@WorkerID", enBillInfo.WorkerID),
                                              New SqlParameter("@Head", enBillInfo.Head),
                                              New SqlParameter("@Count", enBillInfo.Count)}
    
            dt = sqlhelper.ExecSelect(sql, CommandType.StoredProcedure, sqlParams)
            Return dt
        End Function
    
        Public Function UpdateInfo(enBillInfo As BillEntity) As Boolean Implements ISettleAccount.UpdateInfo
            Dim sql As String = "PROC_SettleAccount"
            Dim flag As Boolean
    
            Dim sqlParams As SqlParameter() = {New SqlParameter("@RechargeCash", enBillInfo.RechargeCash),
                                              New SqlParameter("@CancelCash", enBillInfo.CancelCash),
                                              New SqlParameter("@CheckDate", enBillInfo.CheckDate),
                                              New SqlParameter("@CheckTime", enBillInfo.CheckTime),
                                              New SqlParameter("@WorkerID", enBillInfo.WorkerID),
                                              New SqlParameter("@Head", enBillInfo.Head),
                                              New SqlParameter("@Count", enBillInfo.Count)}
            flag = sqlhelper.ExecAddDelUpdate(sql, CommandType.StoredProcedure, sqlParams)
            Return flag
        End Function
    
        Public Function SelectOperator() As List(Of UserEntity) Implements ISettleAccount.SelectOperator
            Dim sql As String
            Dim dt As New DataTable
            Dim myList As New List(Of Entity.UserEntity)
    
            Dim sqlParams As SqlParameter() = {New SqlParameter("@UserLevel", "操作员")}
            sql = "select * from User_Info where userLevel=@UserLevel"
            dt = sqlhelper.ExecSelect(sql, CommandType.Text, sqlParams)
            If dt.Rows.Count > 0 Then
                myList = ConvertHelper.ConvertToList(Of Entity.UserEntity)(dt)
            End If
            Return myList
        End Function
    End Class

存储过程：

USE [ComputerCharge_sys]
    GO
    /****** Object:  StoredProcedure [dbo].[PROC_SettleAccount]    Script Date: 2017/9/17 17:01:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Lyc
    -- Create date: 2017-9
    -- Description:	Settle accounts
    -- =============================================
    ALTER PROCEDURE [dbo].[PROC_SettleAccount]
    	-- Add the parameters for the stored procedure here
    	@Count int,
    	@RechargeCash numeric(10,2),
    	@CancelCash numeric(10,2),
    	@CheckDate date,
    	@CheckTime time,
    	@WorkerID char(10),
    	@Head char(10)
    AS
    	declare @IsCheck char(10)
    BEGIN
    	SET NOCOUNT ON;
    	set @IsCheck='未结账'
    	--注册情况：
    	if @Count=1
    	begin
    		select cardNo,type,registeDate from Card_Info where isCheck='未结账'and userID=@WorkerID
    	end
    	--充值情况：
    	if @Count=2
    	begin
    		select cardNo,rechargeCash,rechargeDate,rechargeTime from Recharge_Info where isCheck='未结账' and userID=@WorkerID
    	end
    	--退卡情况：
    	if @Count=3
    	begin
    		select cardNo,cancelDate,cancelTime,returnCash from CancelCard_Info where isCheck='未结账' and userID=@WorkerID
    	end
    	--更新情况：
    	if @Count=4
    	begin 
    		update Card_Info set isCheck='已结账' where userID=@WorkerID and isCheck=@IsCheck
    		update Recharge_Info set isCheck='已结账' where userID=@WorkerID and isCheck=@IsCheck
    		update CancelCard_Info set isCheck='已结账' where userID=@WorkerID and isCheck =@IsCheck
    		insert into Bill_Info values (@RechargeCash,@CancelCash,@CheckDate,@CheckTime,@WorkerID,@Head)
    	end
    END

感谢您的阅读~