一、查看某操作员的工作数据信息
代码:
Private Sub comboUserName_Click()
Dim txtSQL As String
Dim MsgText As String
Dim mrc As ADODB.Recordset
'通过用户名,显示真实姓名
txtSQL = "select * from User_Info where UserID='" & Trim(comboUserName.Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
txtTrueName.Text = mrc.Fields(3)
mrc.Close
'购卡
If SSTab1.Tab = 0 Then
With MSHFlexGrid1
.CellAlignment = 4
.Rows = 1
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
'选择student表中用户名为combo且未结账的数据
txtSQL = "select * from student_Info where UserID='" & _
Trim(comboUserName.Text) & "'and Ischeck='" & "未结账" & "'"
Set mrcS = ExecuteSQL(txtSQL, MsgText)
Do While Not mrcS.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrcS.Fields(1) & "")
.TextMatrix(.Rows - 1, 1) = Trim(mrcS.Fields(0) & "")
.TextMatrix(.Rows - 1, 2) = Trim(mrcS.Fields(12) & "")
.TextMatrix(.Rows - 1, 3) = Trim(mrcS.Fields(13) & "")
mrcS.MoveNext
Loop
End With
End If
'充值
If SSTab1.Tab = 1 Then
With MSHFlexGrid2
.CellAlignment = 4
.Rows = 1
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "充值金额"
.TextMatrix(0, 3) = "日期"
.TextMatrix(0, 4) = "时间"
'选择recharge表中用户名为combo、未结账的数据
txtSQL = "select * from ReCharge_Info where UserID='" & Trim(comboUserName.Text) & _
"' and status='" & "未结账" & "'"
Set mrcR = ExecuteSQL(txtSQL, MsgText)
Do While Not mrcR.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrcR.Fields(1)) & ""
.TextMatrix(.Rows - 1, 1) = Trim(mrcR.Fields(2)) & ""
.TextMatrix(.Rows - 1, 2) = Trim(mrcR.Fields(3)) & ""
.TextMatrix(.Rows - 1, 3) = Trim(mrcR.Fields(4)) & ""
.TextMatrix(.Rows - 1, 4) = Trim(mrcR.Fields(5)) & ""
mrcR.MoveNext
Loop
End With
End If
'退卡
If SSTab1.Tab = 2 Then
With MSHFlexGrid3
.CellAlignment = 4
.Rows = 1
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "退卡金额"
.TextMatrix(0, 3) = "日期"
.TextMatrix(0, 4) = "时间"
'选择cancelcard表中用户名为combo、未结账的数据
txtSQL = "select * from CancelCard_Info where UserID='" & Trim(comboUserName.Text) & _
"'and status='" & "未使用" & "'"
Set mrcc = ExecuteSQL(txtSQL, MsgText)
Do While Not mrcc.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrcc.Fields(0) & "")
.TextMatrix(.Rows - 1, 1) = Trim(mrcc.Fields(1) & "")
.TextMatrix(.Rows - 1, 2) = Trim(mrcc.Fields(2) & "")
.TextMatrix(.Rows - 1, 3) = Trim(mrcc.Fields(3) & "")
.TextMatrix(.Rows - 1, 4) = Trim(mrcc.Fields(4) & "")
mrcc.MoveNext
Loop
End With
End If
'临时用户
If SSTab1.Tab = 3 Then
With MSHFlexGrid4
.CellAlignment = 4
.Rows = 1
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "卡号"
.TextMatrix(0, 2) = "日期"
.TextMatrix(0, 3) = "时间"
'选择学生表中用户名为combo,类型为临时用户,且未结账的用户
txtSQL = "select * from student_Info where UserID='" & Trim(comboUserName.Text) & _
"'and type='" & "临时用户" & "'and Ischeck='" & "未结账" & "'"
Set mrcS = ExecuteSQL(txtSQL, MsgText)
Do While Not mrcS.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrcS.Fields(1) & "")
.TextMatrix(.Rows - 1, 1) = Trim(mrcS.Fields(0) & "")
.TextMatrix(.Rows - 1, 2) = Trim(mrcS.Fields(12) & "")
.TextMatrix(.Rows - 1, 3) = Trim(mrcS.Fields(13) & "")
mrcS.MoveNext
Loop
End With
End If
'汇总
If SSTab1.Tab = 4 Then
'售卡数量
txtSQL = "select * from student_Info where UserID='" & Trim(comboUserName.Text) & _
"' and Ischeck='" & "未结账" & " ' and status='" & "使用" & "'"
Set mrcS = ExecuteSQL(txtSQL, MsgText)
If mrcS.RecordCount = 0 Then
txtSellCard.Text = "0"
Else
txtSellCard.Text = mrcS.RecordCount
End If
'退卡数量
txtSQL = "select * from CancelCard_Info where UserID='" & Trim(comboUserName.Text) & _
"'and status='" & "未使用" & "'"
Set mrcc = ExecuteSQL(txtSQL, MsgText)
If mrcc.RecordCount = 0 Then
txtRtnNum.Text = "0"
Else
txtRtnNum.Text = mrcc.RecordCounttuika
End If
'退卡金额=该操作员操作的所有用户退卡金额的总和
txtSQL = "select sum(CancelCash) from CancelCard_Info where UserID='" & Trim(comboUserName.Text) & _
"'and status='" & "未使用" & "'"
Set mrcc = ExecuteSQL(txtSQL, MsgText)
If IsNull(Trim(mrcc.Fields(0))) Then
txtRtnSum.Text = "0"
Else
txtRtnSum.Text = Val(mrcc.Fields(0))
End If
'充值金额(不区分临时用户和固定用户)
txtSQL = "select sum(addmoney) from Recharge_Info where UserID='" & Trim(comboUserName.Text) & _
"'and status='" & "未结账" & "'"
Set mrcR = ExecuteSQL(txtSQL, MsgText)
If IsNull(Trim(mrcR.Fields(0))) Then
txtRechargeSum.Text = "0"
Else
txtRechargeSum.Text = Val(mrcR.Fields(0))
End If
'临时收费金额=临时用户注册金额
txtSQL = "select * from student_Info where UserID = '" & Trim(comboUserName.Text) & "'and Ischeck='未结账' and type='临时用户'"
Set mrcS = ExecuteSQL(txtSQL, MsgText)
'------若所有临时用户已结账
If mrcS.EOF = True Then
MsgBox "没有临时用户需要结账", , "提示"
Dim ctl As Control
'删除所有text文本框内容
For Each ctl In Controls
If TypeOf ctl Is TextBox Then ctl.Text = ""
Next ctl
Else
End If
'------
txtTemporarySum.Text = Val(Trim(mrcS.Fields(7)))
'总售卡数=售卡数量-退卡数量
txtSaleCardNumber.Text = Val(txtSellCard.Text) - Val(txtRtnNum.Text)
'应收金额=充值金额-退卡金额
txtSum.Text = Val(txtRechargeSum.Text) - Val(txtRtnSum.Text)
End If
End Sub
二、结账
结账的思路很简单,就两件事儿:
1.更新日结:计算日结账单中所需数据金额,并更新到表中;
1)若结过帐:更新表中数据;
2)未结过帐:添加新数据。
2.更新表
代码:
Private Sub cmdCheckout_Click()
Dim RemainCash As String
Dim RechargeCash As String
Dim ConsumeCash As String
Dim CancelCash As String
Dim AllCash As String
If comboUserName = "" Then
MsgBox "请选择操作员后再结账!", vbOKOnly, "提示"
Exit Sub
End If
'更新日结
'计算上期充值卡余额
txtSQL = "select sum(cash) from student_Info where UserID='" & Trim(comboUserName.Text) & "'and ischeck='" & "未结账" & "'"
Set mrcS = ExecuteSQL(txtSQL, MsgText)
If IsNull(mrcS.Fields(0)) Then
RemainCash = "0"
Else
RemainCash = mrcS.Fields(0)
End If
'充值金额
txtSQL = "select sum(addmoney) from Recharge_Info where status='未结账'and date='" & Date & "'"
Set mrcR = ExecuteSQL(txtSQL, MsgText)
If IsNull(mrcR.Fields(0)) = True Then
RechargeCash = "0"
Else
RechargeCash = mrcR.Fields(0)
End If
'从Line表计算当日消费金额
txtSQL = "select sum(consume) from Line_Info where offdate='" & Date & "'"
Set mrcL = ExecuteSQL(txtSQL, MsgText)
If IsNull(mrcL.Fields(0)) Then
ConsumeCash = "0"
Else
ConsumeCash = mrcL.Fields(0)
End If
'更新计算当天的退卡金额,不会出现未结账的操作员
txtSQL = "select sum(CancelCash) from CancelCard_Info where date='" & Date & "'and status='未使用'"
Set mrcc = ExecuteSQL(txtSQL, MsgText)
If IsNull(mrcc.Fields(0)) Then
CancelCash = "0"
Else
CancelCash = mrcc.Fields(0)
End If
'更新到日结账单
txtSQL = "select * from CheckDay_Info where date='" & Date & "'"
Set mrcDay = ExecuteSQL(txtSQL, MsgText)
'若结过账,在原有基础上更新
If Not mrcDay.EOF Then
mrcDay!RemainCash = Val(RemainCash) + mrcDay!RemainCash
mrcDay!RechargeCash = Val(RechargeCash) + mrcDay!RechargeCash
mrcDay!ConsumeCash = Val(ConsumeCash) + mrcDay!ConsumeCash
mrcDay!CancelCash = Val(CancelCash) + mrcDay!CancelCash
mrcDay!AllCash = Val(RemainCash) + Val(RechargeCash) - Val(ConsumeCash) - Val(CancelCash) + mrcDay!AllCash
mrcDay!Date = Date
mrcDay.Update
mrcDay.Close
mrcL.Close
mrcS.Close
Else
'若未结过账,添加新数据
mrcDay.AddNew
mrcDay!RemainCash = Val(RemainCash)
mrcDay!RechargeCash = Val(RechargeCash)
mrcDay!ConsumeCash = Val(ConsumeCash)
mrcDay!CancelCash = Val(CancelCash)
mrcDay!AllCash = Val(RemainCash) + Val(RechargeCash) - Val(CancelCash)
mrcDay!Date = Date
mrcDay.Update
mrcS.Close
mrcL.Close
mrcDay.Close
End If
'更新学生表
txtSQL = "select * from student_Info where UserID='" & Trim(comboUserName.Text) & "'and Ischeck='" & "未结账" & "'"
Set mrcS = ExecuteSQL(txtSQL, MsgText)
Do While Not mrcS.EOF
mrcS!ischeck = "结账"
mrcS.Update
mrcS.MoveNext
Loop
mrcS.Close
'更新充值表
txtSQL = "select * from Recharge_Info where UserID='" & Trim(comboUserName.Text) & "'and status='" & "未结账" & "'"
Set mrcR = ExecuteSQL(txtSQL, MsgText)
Do While Not mrcR.EOF
mrcR!Status = "结账"
mrcR.Update
mrcR.MoveNext
Loop
mrcR.Close
'更新退卡表
txtSQL = "select * from CancelCard_Info where UserID='" & Trim(comboUserName.Text) & "'and status='" & "未结账" & "'"
Set mrcc = ExecuteSQL(txtSQL, MsgText)
Do While Not mrcc.EOF
mrcc!ischeck = "结账"
mrcc.Update
mrcc.MoveNext
Loop
mrcc.Close
'清空文本框显示的信息
Dim ctr1 As Control
For Each ctr1 In Me.Controls
If TypeOf ctr1 Is TextBox Then
ctr1.Text = ""
End If
Next
MsgBox "结账成功!", vbOKOnly, "提示"
Me.Hide
End Sub