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