frmcost.frm

一个实用的学生宿舍管理系统

      BackStyle       =   0  'Transparent
      Caption         =   "水表数："
      Height          =   375
      Index           =   1
      Left            =   720
      TabIndex        =   19
      Top             =   4560
      Width           =   855
   End
   Begin VB.Label Label3 
      BackStyle       =   0  'Transparent
      Caption         =   "日期："
      Height          =   375
      Index           =   0
      Left            =   720
      TabIndex        =   18
      Top             =   3600
      Width           =   855
   End
   Begin VB.Label Label2 
      BackStyle       =   0  'Transparent
      Caption         =   "房租费结算"
      Height          =   495
      Index           =   1
      Left            =   6360
      TabIndex        =   17
      Top             =   2520
      Width           =   1575
   End
   Begin VB.Label Label2 
      BackStyle       =   0  'Transparent
      Caption         =   "水电费结算"
      Height          =   495
      Index           =   0
      Left            =   720
      TabIndex        =   16
      Top             =   2520
      Width           =   1575
   End
End
Attribute VB_Name = "frmcost"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim mrc As ADODB.Recordset
Dim MsgText As String
Dim tSQL As String
Private Sub Command1_Click()
 Dim txtSQL As String
  If Text1 = "" Then
        MsgBox "水费不能为空!", vbOKOnly, "提示"
        Text1.SetFocus
        Exit Sub
    End If
If Text2 = "" Then
        MsgBox "电费不能为空!", vbOKOnly, "提示"
        Text2.SetFocus
        Exit Sub
    End If
If Text3 = "" Then
        MsgBox "房租费不能为空!", vbOKOnly, "提示"
        Text3.SetFocus
        Exit Sub
    End If
   
      txtSQL = "select * from fee_Form "
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    
    'mrc.Close
'    txtSQL = "select * from fee_Form"
'    Set mrc = ExecuteSQL(txtSQL, MsgText)
    mrc.AddNew
    mrc.Fields(0) = Trim(Text1)
    mrc.Fields(1) = Trim(Text2)
    mrc.Fields(2) = Trim(Text3)
      mrc.Fields(3) = Now
    mrc.Update
    mrc.Close
   ' For i = 0 To 2
'        Text1 = ""
'        Text2 = ""
'        Text3 = ""
   ' Next
   
    MsgBox "费用信息设置成功!", vbOKOnly, "提示"
End Sub

Private Sub Command2_Click()
Dim txtSQL As String
Dim txSQL As String
Dim xu As String
Dim water As Double
Dim elec As Double
Dim rent As Double
Dim waterd As Double
Dim elecd As Double
If Text6 = "" Then
        MsgBox "房间号不能为空!", vbOKOnly, "提示"
        Text6.SetFocus
        Exit Sub
    End If
    If Text5 = "" Then
        MsgBox "请输入该房间当月水表数!", vbOKOnly, "提示"
        Text5.SetFocus
        Exit Sub
    End If
     If Text7 = "" Then
        MsgBox "请输入该房间当月电表数!", vbOKOnly, "提示"
        Text7.SetFocus
        Exit Sub
    End If
  txtSQL = "select * from room_Form where room_NO='" & Trim(Text6) & "'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
   If mrc.EOF = True Then
        MsgBox "该房间不存在，请核实后再输入!", vbOKOnly, "警告"
        Text6.SetFocus
        Exit Sub
        Else
        If (Text1 = "" Or Text2 = "") Then
         MsgBox "请输入水电费用标准!", vbOKOnly, "提示"
         Text5.SetFocus
        Exit Sub
    End If
        waterd = Val(mrc(6))
        elecd = Val(mrc(7))
        waterd = Val(Text5) - waterd
        elecd = Val(Text7) - elecd
        If (waterd < 0 Or elecd < 0) Then
        MsgBox "水表或电表信息输入错误，请核实后再输入!", vbOKOnly, "警告"
        Exit Sub
        End If
        End If
        txSQL = "update room_Form set room_WaterD='" & Trim(Text5) & "',room_ElecD='" & Trim(Text7) & "' where room_NO='" & Trim(Text6) & "'"
    Set mrc = ExecuteSQL(txSQL, MsgText)
    water = waterd * Val(Text1)
    elec = elecd * Val(Text2)
        txSQL = "select * from room_Fee "
    Set mrc = ExecuteSQL(txSQL, MsgText)
    mrc.AddNew
    mrc.Fields(0) = Trim(Text6)
    mrc.Fields(1) = Date
    mrc.Fields(2) = Trim(Str(water))
     mrc.Fields(3) = Trim(Str(elec))
      mrc.Fields(4) = Trim(Str(water + elec))
    mrc.Update
    mrc.Close
    
'        Text5 = ""
   Text6 = 0
   Text7 = 0
    MsgBox "结算过程结束，请查看相应信息!", vbOKOnly, "提示"
    ShowDataW
'    With MSFlexGrid1
' Do While Not mrc.EOF
'            .Rows = .Rows + 1
'            For i = 0 To mrc.Fields.Count - 1
'                .TextMatrix(.Rows - 1, i) = mrc.Fields(i) & ""
'            Next i
'            mrc.MoveNext
'        Loop
'        End With
        
'         txtSQL = "delete from student_Form where student_NO='" & Trim(Text1) & "'"
'    Set mrc = ExecuteSQL(txtSQL, MsgText)
'    txSQL = "update room_stud set room_YZPeople=room_YZPeople -'" & 1 & "',room_KQPeople=room_KQPeople +'" & 1 & "' where room_NO='" & Trim(Text2) & "'"
'    Set mrc = ExecuteSQL(txSQL, MsgText)
'    Text1 = ""
'   Text2 = ""
'    MsgBox "该学生已被迁出!", vbOKOnly, "警告"
'    End If
End Sub

Private Sub Command3_Click()
Dim txtSQL As String
Dim xu As String

xu = Trim(MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 0))
If xu <> "" Then
  txtSQL = "delete from room_Fee where room_NO='" & xu & "'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
   
 
    MsgBox "该房间水电费信息已被删除!", vbOKOnly, "警告"
    Else
     MsgBox "未选中要删除信息的房间!", vbOKOnly, "警告"
     Exit Sub
    End If
    ShowDataW
'With MSFlexGrid1
' Do While Not mrc.EOF
'            .Rows = .Rows + 1
'            For i = 0 To mrc.Fields.Count - 1
'                .TextMatrix(.Rows - 1, i) = mrc.Fields(i) & ""
'            Next i
'            mrc.MoveNext
'        Loop
'        End With
End Sub

Private Sub Command4_Click()
 Dim txSQL As String
    Dim mrcc As ADODB.Recordset
    txSQL = "select * from room_Fee"
    Set mrcc = ExecuteSQL(txSQL, MsgText)
ShowDataW
'With MSFlexGrid1
' Do While Not mrcc.EOF
'            .Rows = .Rows + 1
'            For i = 0 To mrcc.Fields.Count - 1
'                .TextMatrix(.Rows - 1, i) = mrcc.Fields(i) & ""
'            Next i
'            mrcc.MoveNext
'        Loop
'        End With
End Sub

Private Sub Command5_Click()
 Dim txSQL As String
    Dim mrcc As ADODB.Recordset
    txSQL = "select * from room_Rent"
    Set mrcc = ExecuteSQL(txSQL, MsgText)
ShowDataR
'With MSFlexGrid2
' Do While Not mrcc.EOF
'            .Rows = .Rows + 1
'            For i = 0 To mrcc.Fields.Count - 1
'                .TextMatrix(.Rows - 1, i) = mrcc.Fields(i) & ""
'            Next i
'            mrcc.MoveNext
'        Loop
'        End With
End Sub

Private Sub Command6_Click()
Dim txtSQL As String
Dim xu As String

xu = Trim(MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 0))
If xu <> "" Then
  txtSQL = "delete from room_Rent where no_Rent='" & xu & "'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
   
 
    MsgBox "该房间房租费信息已被删除!", vbOKOnly, "警告"
    Else
     MsgBox "未选中要删除的房间!", vbOKOnly, "警告"
     Exit Sub
    End If
    ShowDataR
With MSFlexGrid2
 Do While Not mrc.EOF
            .Rows = .Rows + 1
            For i = 0 To mrc.Fields.Count - 1
                .TextMatrix(.Rows - 1, i) = mrc.Fields(i) & ""
            Next i
            mrc.MoveNext
        Loop
        End With
End Sub

Private Sub Command7_Click()
Dim txtSQL As String
Dim txSQL As String

Dim rent As Double
Dim yzpeople As Integer
If Text8 = "" Then
        MsgBox "请输入您的工号!", vbOKOnly, "提示"
        Text8.SetFocus
        Exit Sub
    End If
  
  txtSQL = "select * from room_Rent where no_Rent='" & Trim(Text8) & "'"
    
    Set mrc = ExecuteSQL(txtSQL, MsgText)
   If mrc.EOF = False Then
        
        Text10.Text = mrc(0)
       
        Else
        If Text10 = "" Then
         MsgBox "请输入您的姓名!", vbOKOnly, "提示"
         Text10.SetFocus
        Exit Sub
    End If
        End If

  If Text6 = "" Then
        MsgBox "请输入结算房租的房间号!", vbOKOnly, "提示"
        Text6.SetFocus
        Exit Sub

    End If
 txSQL = "select * from room_stud where room_NO='" & Trim(Text6) & "'"
    Set mrc = ExecuteSQL(txSQL, MsgText)
   If mrc.EOF = True Then
        MsgBox "该房间不存在，请核实后再输入!", vbOKOnly, "警告"
        Text6.SetFocus
        Exit Sub
        Else
        If (Text3 = "") Then
         MsgBox "请输入房租费用标准!", vbOKOnly, "提示"
         Text3.SetFocus
        Exit Sub
    End If
End If
        yzpeople = Val(mrc(4))
        rent = yzpeople * Val(Text3)

        If (rent < 0) Then
        MsgBox "房间费用输入错误，请重新输入!", vbOKOnly, "警告"
Text3.SetFocus
        Exit Sub
        End If
            
        txtSQL = "select * from room_Rent "
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    mrc.AddNew
    mrc.Fields(0) = Trim(Text10)
    mrc.Fields(1) = Trim(Text8)
    mrc.Fields(2) = Trim(Text6)
     mrc.Fields(3) = Date
     
    mrc.Update
    mrc.Close
    

    MsgBox "结算过程结束，请查看相应信息!", vbOKOnly, "提示"
    ShowDataR
'        End If
End Sub

Private Sub Form_Load()
With Combo1
.AddItem Str$(Date - 1)
.AddItem Str$(Date - 2)
.AddItem Str$(Date - 3)
.AddItem Str$(Date - 4)
.AddItem Str$(Date - 5)
Combo1.Text = Date$
End With
With Combo2
.AddItem Str$(Date - 1)
.AddItem Str$(Date - 2)
.AddItem Str$(Date - 3)
.AddItem Str$(Date - 4)
.AddItem Str$(Date - 5)

Combo2.Text = Date$
End With
End Sub
Private Sub ShowDataW()
    Dim txSQL As String
    Dim mrcc As ADODB.Recordset
    txSQL = "select * from room_Fee"
    Set mrcc = ExecuteSQL(txSQL, MsgText)
        With MSFlexGrid1
        .ColWidth(0) = 1000
        .ColWidth(1) = 1000
        .ColWidth(2) = 1000
        .ColWidth(3) = 1000
         .ColWidth(4) = 1000

        .TextMatrix(0, 0) = "房间"
        .TextMatrix(0, 1) = "日期"
        .TextMatrix(0, 2) = "水费"
        .TextMatrix(0, 3) = "电费"
          .TextMatrix(0, 4) = "总费用"
         
        .Rows = 1
        Do While Not mrcc.EOF
            .Rows = .Rows + 1
            For i = 0 To mrcc.Fields.Count - 1
                .TextMatrix(.Rows - 1, i) = mrcc.Fields(i) & ""
            Next i
            mrcc.MoveNext
        Loop
    End With
    mrcc.Close
End Sub
Private Sub ShowDataR()
    Dim txSQL As String
    Dim mrcc As ADODB.Recordset
    txSQL = "select * from room_Rent"
    Set mrcc = ExecuteSQL(txSQL, MsgText)
        With MSFlexGrid2
        .ColWidth(0) = 1000
        .ColWidth(1) = 1000
        .ColWidth(2) = 1000
        .ColWidth(3) = 1000
         .ColWidth(4) = 1000

        .TextMatrix(0, 0) = "姓名"
        .TextMatrix(0, 1) = "工号"
        .TextMatrix(0, 2) = "房间号码"
        .TextMatrix(0, 3) = "房租"
          .TextMatrix(0, 4) = "结算日期"
         
        .Rows = 1
        Do While Not mrcc.EOF
            .Rows = .Rows + 1
            For i = 0 To mrcc.Fields.Count - 1
                .TextMatrix(.Rows - 1, i) = mrcc.Fields(i) & ""
            Next i
            mrcc.MoveNext
        Loop
    End With
    mrcc.Close
End Sub