经过这麽长时间的摸爬滚打,终于到结账,本想着到这里该出的问题也出了,应该没什么问题了,可是天不遂人愿啊,又经过了一次次的调试终于把结账完成了。下面就说说了关于结账的一些东西。第二次做机房收费系统了关于界面的设置英爱不是问题了,如下:
设计思路:
在结账窗体下,就是一个简单的查找的过程,根据操作员的用户名和结账状态找到符合条件的记录,填充中下面的TabAccount的各个框里。如果想要结账,在点击“结账”按钮把记录添加到结账数据库表中并更改结账状态。利用存储过程来实现功能,相对来说比较简单点 ,如果你觉得之间写代码比较顺手,也可以通过代码来实现。
代码展示:
U层
<span style="font-family:KaiTi_GB2312;">Public Class FrmCheck
Private Sub comUserID_SelectedIndexChanged(sender As Object, e As EventArgs) Handles comUserID.SelectedIndexChanged
Dim a As Integer '用来获取comUserId控件的Listindex
Dim i As Integer '用来存放dt的记录数
'当comUserID的内容发生改变,UserName的内容自动对应改变
a = comUserID.SelectedIndex
comUserName.SelectedIndex = a
Dim Tab As Integer
Tab = TabAccount.selectedIndex
Static SellCount As Integer '记录售卡张数
Static CancelCount As Integer '记录退卡张数
Static RechargeMoney As Integer '计算充值金额
Static CancelMoney As Integer '计算退卡金额
Static LastMoney As Integer '记录临时用户消费
Static recandcan As Integer '计算充值记录和退卡记录总和
Static count As Integer '计算各个记录的总值
count = 0
recandcan = 0
RechargeMoney = 0
CancelMoney = 0
LastMoney = 0
'把值传给实体
Dim Account As New Entity.CheckInfo
Account.consumeMoney = 0
Account.rechargeMoney = 0
Account.returnMoney = 0
Account.Date1 = DateTime.Now.ToString("yyyy-MM-dd")
Account.UserID = comUserID.Text
'查询注册
TabAccount.TabIndex = 1
Account.Count = TabAccount.TabIndex
Dim dt As New DataTable
Dim Userfacade As New Facade.CheckFacade
dt = Userfacade.SelectDt(Account)
count = count + dt.Rows.Count
If dt.Rows.Count <> 0 Then
DataGridView1.Rows.Add(dt.Rows.Count)
For i = 0 To (dt.Rows.Count - 1)
DataGridView1.Rows(i).Cells(0).Value() = dt.Rows(i).Item(0)
DataGridView1.Rows(i).Cells(1).Value() = dt.Rows(i).Item(1)
DataGridView1.Rows(i).Cells(2).Value() = dt.Rows(i).Item(2)
DataGridView1.Rows(i).Cells(3).Value() = dt.Rows(i).Item(3)
DataGridView1.Rows(i).Cells(4).Value() = dt.Rows(i).Item(4)
If dt.Rows(i).Item(4).trim() = "未结账" Then
recandcan = recandcan + 1
LastMoney = LastMoney + dt.Rows(i).Item(5)
End If
Next
End If
SellCount = dt.Rows.Count '记录购卡张数
'查询充值
TabAccount.TabIndex = 2
Account.Count = TabAccount.TabIndex
dt = Userfacade.SelectDt(Account)
count = dt.Rows.Count
If dt.Rows.Count <> 0 Then
DataGridView2.Rows.Add(dt.Rows.Count)
For i = 0 To dt.Rows.Count - 1
DataGridView2.Rows(i).Cells(0).Value() = dt.Rows(i).Item(0)
DataGridView2.Rows(i).Cells(1).Value() = dt.Rows(i).Item(1)
DataGridView2.Rows(i).Cells(2).Value() = dt.Rows(i).Item(2)
DataGridView2.Rows(i).Cells(3).Value() = dt.Rows(i).Item(3)
DataGridView2.Rows(i).Cells(4).Value() = dt.Rows(i).Item(4)
If dt.Rows(1).Item(4) = "未结账 " Then
recandcan = recandcan + 1
RechargeMoney = RechargeMoney + dt.Rows(i).Item(1)
End If
Next
End If
'查询退卡
TabAccount.TabIndex = 3
Account.Count = TabAccount.TabIndex
dt = Userfacade.SelectDt(Account)
count = count + dt.Rows.Count
If dt.Rows.Count <> 0 Then
DataGridView3.Rows.Add(dt.Rows.Count)
For i = 0 To dt.Rows.Count - 1
DataGridView3.Rows(i).Cells(0).Value() = dt.Rows(i).Item(0)
DataGridView3.Rows(i).Cells(1).Value() = dt.Rows(i).Item(1)
DataGridView3.Rows(i).Cells(2).Value() = dt.Rows(i).Item(2)
DataGridView3.Rows(i).Cells(3).Value() = dt.Rows(i).Item(3)
DataGridView3.Rows(i).Cells(4).Value() = dt.Rows(i).Item(4)
If dt.Rows(i).Item(4) = "未结账 " Then
recandcan = recandcan + 1
CancelMoney = CancelMoney + dt.Rows(i).Item(1)
End If
Next
End If
CancelCount = dt.Rows.Count '记录退卡张数
Account.sumcount = count
Account.Countlot = recandcan
'汇总
TabAccount.TabIndex = 4
Account.Count = TabAccount.TabIndex
Txtsell.Text = SellCount
TxtCancel.Text = CancelCount
TxtRecharge.Text = RechargeMoney
TxtCancelcash.Text = CancelMoney
TxtMakecash.Text = RechargeMoney - CancelMoney
txtLastMoney.text = LastMoney
Account.rechargeMoney = RechargeMoney
Account.consumeMoney = TxtMakecash.Text.Trim()
Account.returnMoney = CancelMoney
End Sub
Private Sub FrmCheck_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim user As New Entity.UserInfo
user.Level = "操作员"
Dim facade As New Facade.CheckFacade
Dim dt As New DataTable
dt = facade.Selectuser(user)
If dt.Rows.Count <> 0 Then
For i = 0 To dt.Rows.Count - 1
comUserID.Items.Add(dt.Rows(i).Item(0))
comUserName.Items.Add(dt.Rows(i).Item(1))
Next
End If
End Sub
Private Sub comUserID_TextChanged(sender As Object, e As EventArgs) Handles comUserID.TextChanged
Dim user As New Entity.UserInfo
user.UserID = comUserID.Text
Dim facade As New Facade.CheckFacade
Dim dt As New DataTable
dt = facade.Selectuser1(user)
If dt.Rows.Count <> 0 Then
comUserName.Text = dt.Rows(0).Item(0)
End If
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim user As New Entity.UserInfo
user.UserID = comUserID.Text
Dim Account As New Entity.CheckInfo
Account.LastMoney = TxtLastMoney.Text
Account.returnMoney = TxtCancelcash.Text
Account.rechargeMoney = TxtRecharge.Text
Account.consumeMoney = TxtMakecash.Text.Trim()
Account.NowMoney = Account.lastMoney - Account.CancelMoney + Account.rechargeMoney
Account.Date1 = Now
Account.time = Date.Now.ToShortTimeString
Account.UserID = comUserID.Text
Dim facade As New Facade.CheckFacade
Dim flag As Boolean
flag = facade.Selectuser2(user, Account)
If flag = True Then
MsgBox("结账成功", vbOKOnly, "提示")
End If
End Sub
End Class</span>
D层
</pre><span style="font-family:KaiTi_GB2312;"><br /></span><pre name="code" class="vb"><span style="font-family:KaiTi_GB2312;">Imports System.Data.SqlClient
Public Class CheckDAO : Implements JFIDAL.CheckIDAL
Public Function SelectDt(Account As Entity.CheckInfo) As DataTable Implements JFIDAL.CheckIDAL.SelectDt
Dim dt As New DataTable
Dim Helper As New SQLHelper.Sqlhelper
'参数赋值
Dim sqlParams As SqlParameter() = {New SqlParameter("@UserId", Account.UserID),
New SqlParameter("@TabCount", Account.Count),
New SqlParameter("@rechargeMoney", Account.rechargeMoney),
New SqlParameter("@consumeMoney", Account.consumeMoney),
New SqlParameter("@returnMoney", Account.returnMoney),
New SqlParameter("@date", Account.Date1)}
Dim strText As String = "Proc_Check" '查询语句
dt = Helper.ExeSelect(strText, CommandType.StoredProcedure, sqlParams) '执行查询
Return dt
End Function
Public Function SelectUser(user As Entity.UserInfo) As DataTable Implements JFIDAL.CheckIDAL.SelectUser
Dim dt As New DataTable
Dim Helper As New SQLHelper.Sqlhelper
'参数赋值
Dim sqlParams As SqlParameter() = {New SqlParameter("@level", user.Level)}
Dim cmdText As String = "select UserID,UserName from T_User where Level=@level"
dt = Helper.ExeSelect(cmdText, CommandType.Text, sqlParams)
Return dt
End Function
Public Function SelectUser1(user As Entity.UserInfo) As DataTable Implements JFIDAL.CheckIDAL.SelectUser1
Dim dt As New DataTable
Dim Helper As New SQLHelper.Sqlhelper
'参数赋值
Dim sqlParams As SqlParameter() = {New SqlParameter("@UserID", user.UserID)}
Dim cmdText As String = "select UserName from T_User where UserID=@UserID"
dt = Helper.ExeSelect(cmdText, CommandType.Text, sqlParams)
Return dt
End Function
Public Function SelectUse2(user As Entity.UserInfo, Account As Entity.CheckInfo) As Boolean Implements JFIDAL.CheckIDAL.SelectUse2
Dim Helper As New SQLHelper.Sqlhelper
Dim flag As Boolean
'参数赋值
Dim sqlParams As SqlParameter() = {New SqlParameter("@LastMoney", Account.LastMoney),
New SqlParameter("@ReturnMoney", Account.returnMoney),
New SqlParameter("@RechargeMoney", Account.rechargeMoney),
New SqlParameter("@ConsuneMoney", Account.consumeMoney),
New SqlParameter("@NowMoney", Account.NowMoney),
New SqlParameter("@Date", Account.Date1),
New SqlParameter("@Time", Account.Time),
New SqlParameter("@UserID", Account.UserID)
}
Dim strText As String = "Proc_CheckMoney" '查询语句
flag = Helper.ExecuteAddDelUpdate(strText, CommandType.StoredProcedure, sqlParams) '执行查询
Return flag
End Function
End Class</span>
<span style="font-family:KaiTi_GB2312;">
</span>
<span style="font-family:KaiTi_GB2312;">存储过程</span>
<span style="font-family:KaiTi_GB2312;"></span><pre name="code" class="vb"><span style="font-family:KaiTi_GB2312;">ALTER PROCEDURE [dbo].[Proc_Check]
@UserID Varchar(10),
@TabCount int,
@rechargeMoney int,
@consumeMoney int,
@returnMoney int,
@date varchar(10)
AS
declare @False varchar(10)
BEGIN
SET NOCOUNT ON;
--set @False ='未结账'
--注册情况
-- 注册情况
if @TabCount =1
begin
select CardID,studentID,RegistDate,RegistTime,CheckStatus ,Account from T_Card where UserID=@UserID and CheckStatus='未结账'
end
--充值情况
if @TabCount =2
begin
select CardID,RechargeMoney,RechargeDate,RechargeTime,CheckStatus from T_Recharge where UserID=@UserID and CheckStatus='未结账'
end
--退卡情况
if @TabCount =3
begin
select CardID,ReturnMoney,CancelCardDate,CancelCardTime,CheckStatus from T_CancelCard where UserID=@UserID and CheckStatus='未结账'
end
end</span>
在这里因为把结账和查找分开了,所以这里用到了两个存储过程,但是这样写比较麻烦,所以大家可以把TabAccount的每个页和结账设置成不同的选项,把两个过程整合在一起。
<span style="font-family:KaiTi_GB2312;"></span><pre name="code" class="vb"><span style="font-family:KaiTi_GB2312;">ALTER PROCEDURE [dbo].[Proc_CheckMoney]
@UserID varchar(10),
@LastMoney varchar(10),
@ReturnMoney varchar(10),
@RechargeMoney varchar(10),
@ConsuneMoney varchar(10),
@NowMoney varchar(10),
@Date varchar(10),
@Time varchar(10)
AS
declare @False varchar(10)
BEGIN
SET NOCOUNT ON;
set @False ='未结账'
update T_CancelCard set CheckStatus ='已结账' where UserID =@UserID and CheckStatus=@False
update T_Recharge set CheckStatus='已结账' where UserID =@UserID and CheckStatus=@False
update T_Card set CheckStatus='已结账' where UserID =@UserID and CheckStatus=@False
insert into T_Check values( @LastMoney ,@rechargeMoney,@returnMoney, @ConsuneMoney , @NowMoney ,@date,@Time,@UserID )
END</span>
【总结】
在敲结账窗体的时候,用到的知识之前已经都用过了,应该是比较容易实现的一个功能,可是自己总是心急,导致一直出错,也没有心情去调试,用了几天的时间才完成,所以不管在做事的事情要一步步的做,不要为了完成而去完成。