机房重构结账之存储过程

机房重构实现结账的时候用到了存储过程，其实开始的时候我并没有用存储过程，然后敲到需要更新表的时候顿时懵了，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

感谢您的阅读~