frmsigncontract.frm

毕业设计 毕业设计 毕业设计 毕业设计 毕业设计

   
   '租期等于起租日期和止租日期之差，结尾不足一月，按一月计。
   '使用datediff 函数计算日期之差
   Text1(5).Text = Int(DateDiff("d", DateValue(Text1(3).Text), DateValue(Text1(4).Text)) / 31) + 1
   '总租金等于月租金乘以租期
   Text1(7).Text = Val(Text1(5).Text) * Val(Text1(6).Text)
   
   '检查完数据完整性后，还需要检查该客户是否已存入租户表中，以及该房屋是否为未出租或预定状态
   sqlc = "select * from Client where 租户姓名 = '" & Combo4.Text & "'"
   rs_ccheck.Open sqlc, conn, adOpenStatic, adLockOptimistic
   If rs_ccheck.EOF = True Then
      rs_ccheck.Close
      MsgBox "该客户资料还未存入租户资料表中，请先录入该客户资料！", vbOKOnly + vbInformation, "注意"
      Exit Sub
   End If
   rs_ccheck.Close
   '检测房屋状态
   sqlh = "select * from House where 房屋编号 = '" & Combo2.Text & "'"
   rs_hcheck.Open sqlh, conn, adOpenStatic, adLockOptimistic
   If rs_hcheck.EOF = True Then
      MsgBox "该房屋编号不存在，请重新输入一个！", vbOKOnly + vbInformation, "注意"
      Combo2.SetFocus
      rs_hcheck.Close
      Exit Sub
   ElseIf rs_hcheck.Fields(8) = "已租" Then
      MsgBox "该房屋已经出租了，请选择另一房屋！", vbOKOnly + vbInformation, "注意"
      rs_hcheck.Close
      Exit Sub
   '如果该房屋状态为预定，则需要看预定人是否为该客户，如果不是，需要弹出对话框提示用户
   ElseIf rs_hcheck.Fields(8) = "预定" Then
       '检查该客户是否为预定客户
       sqlyd = "select * from YuDing where 预定房屋编号 = '" & Combo2.Text & " '"
       rs_yd.Open sqlyd, conn, adOpenStatic, adLockOptimistic
       '如果该客户不是预定客户，检查预定有效期
       If Not rs_yd.Fields(1) = Combo4.Text Then
          '如果已经过了预定有效期，别的用户可以承租
          If (Date > DateAdd(d, rs_yd.Fields(4), rs_yd.Fields(8))) Then
             '出租，加入合同表
             sqlcon = "select * from Contract"
             rs_contract.Open sqlcon, conn, adOpenStatic, adLockOptimistic
             rs_contract.AddNew
             rs_contract.Fields(0) = Text1(0).Text
             rs_contract.Fields(1) = Combo4.Text
             rs_contract.Fields(2) = Combo2.Text
             For i = 3 To 8
                rs_contract.Fields(i) = Text1(i).Text
             Next i
             rs_contract.Fields(9) = Combo3.Text
             rs_contract.Fields(10) = Text1(10).Text
             rs_contract.Fields(11) = Text1(11).Text
             rs_contract.Update
             '修改房屋状态
             rs_hcheck(8) = "已租"
             rs_hcheck.Update
             '检查求租客户表中是否有该客户，如果有，则删除之
             sqlqzc = "select * from QZClient where 求租客户姓名 = '" & Combo4.Text & "'"
             rs_qzc.Open sqlqzc, conn, adOpenStatic, adLockOptimistic
             If rs_qzc.EOF = False Then
                rs_qzc.Delete
                rs_qzc.Update
             End If
             '显示签订合同成功
             MsgBox "签订合同成功！", vbOKOnly + vbInformation, "注意"
             '设置签订按钮不可用
             cmdSign.Enabled = False
             cmdYaJin.Enabled = True
             cmdZuJin.Enabled = True
             '关闭所有打开的记录集
             rs_qzc.Close
             rs_yd.Close
             rs_hcheck.Close
             rs_contract.Close
             Exit Sub
          Else
             MsgBox "该房屋已经被别人预定了，请选择另一房屋！", vbOKOnly + vbInformation, "注意"
             rs_hcheck.Close
             rs_yd.Close
             Exit Sub
          End If
       '该客户即为预定客户，可以出租
       ElseIf rs_yd.Fields(1) = Combo4.Text Then
           '出租，加入合同表
             sqlcon = "select * from Contract"
             rs_contract.Open sqlcon, conn, adOpenStatic, adLockOptimistic
             rs_contract.AddNew
             rs_contract.Fields(0) = Text1(0).Text
             rs_contract.Fields(1) = Combo4.Text
             rs_contract.Fields(2) = Combo2.Text
             For i = 3 To 8
                rs_contract.Fields(i) = Text1(i).Text
             Next i
             rs_contract.Fields(9) = Combo3.Text
             rs_contract.Fields(10) = Text1(10).Text
             rs_contract.Fields(11) = Text1(11).Text
             For i = 0 To 11
                rs_contract.Fields(i) = Text1(i).Text
             Next i
             rs_contract.Update
             '修改房屋状态
             rs_hcheck(8) = "已租"
             rs_hcheck.Update
             '删除预定表中该项
             rs_yd.Delete
             rs_yd.Update
             '检查求租客户表中是否有该客户，如果有，则删除之
             sqlqzc = "select * from QZClient where 求租客户姓名 = '" & Text1(1).Text & "'"
             rs_qzc.Open sqlqzc, conn, adOpenStatic, adLockOptimistic
             If rs_qzc.EOF = False Then
                rs_qzc.Delete
                rs_qzc.Update
             End If
             '显示签订合同成功
             MsgBox "签订合同成功！！", vbOKOnly + vbInformation, "注意"
             '设置签订按钮不可用
             cmdSign.Enabled = False
             cmdYaJin.Enabled = True
             cmdZuJin.Enabled = True
             '关闭所有打开的记录集
             rs_qzc.Close
             rs_yd.Close
             rs_hcheck.Close
             rs_contract.Close
             
             Exit Sub
        End If
    '如果该房屋状态为未租，则可以顺利出租
    ElseIf rs_hcheck.Fields(8) = "未租" Then
        '出租，加入合同表
        sqlcon = "select * from Contract"
        rs_contract.Open sqlcon, conn, adOpenStatic, adLockOptimistic
        rs_contract.AddNew
        For i = 0 To 11
            rs_contract.Fields(i) = Text1(i).Text
        Next i
        rs_contract.Update
        
        '修改房屋状态
        rs_hcheck(8) = "已租"
        rs_hcheck.Update
        '检查求租客户表中是否有该客户，如果有，则删除之
        sqlqzc = "select * from QZClient where 求租客户姓名 = '" & Text1(1).Text & "'"
        rs_qzc.Open sqlqzc, conn, adOpenStatic, adLockOptimistic
        If rs_qzc.EOF = False Then
            rs_qzc.Delete
            rs_qzc.Update
        End If
        '显示签订合同成功
        MsgBox "签订合同成功！", vbOKOnly + vbInformation, "注意"
        '设置签订按钮不可用
        cmdSign.Enabled = False
        cmdYaJin.Enabled = True
        cmdZuJin.Enabled = True
        '关闭所有打开的记录集
        rs_qzc.Close
        rs_hcheck.Close
        rs_contract.Close
        Exit Sub
    End If
       
End Sub

Private Sub cmdYaJin_Click()
   fromContract = True
   frmPayYaJin.Show
   cmdYaJin.Enabled = False
   
End Sub

Private Sub cmdZuJin_Click()
   ZuJinfromContract = True
   frmPayZuJin.Show
   cmdZuJin.Enabled = False
End Sub

Private Sub Form_Load()
   Dim X0 As Long
   Dim Y0 As Long
   '让窗体居中
   X0 = Screen.Width
   Y0 = Screen.Height
   X0 = (X0 - Me.Width) / 2
   Y0 = (Y0 - Me.Height) / 2
   Me.Move X0, Y0
   cmdYaJin.Enabled = False
   cmdZuJin.Enabled = False
   '如果要显示的是租房合同选项卡
   If SSTab1.Tab = 0 Then
   '先把合同编号自动添加
    If num_count.State = adStateOpen Then
      num_count.Close
    End If
    sql = "select Max(合同编号) from Contract"
    num_count.Open sql, conn, adOpenStatic, adLockOptimistic
    Text1(0).Text = num_count.Fields(0) + 1
    Text1(0).Enabled = False
      '清空所有text
      Combo4.Text = ""
      Combo2.Text = ""
      For i = 3 To 8
        Text1(i).Text = ""
      Next i
      Combo3.Text = ""
      Text1(10).Text = ""
      Text1(11).Text = ""
      '在相应的组合框中添加信息
   sqlname = "select 租户姓名 from Client"
   If rst.State = adStateOpen Then
        rst.Close
   End If
   rst.CursorLocation = adUseClient
   rst.Open sqlname, conn, adOpenStatic, adLockBatchOptimistic
   Do Until rst.EOF
    Combo4.AddItem rst.Fields(0)
    rst.MoveNext
   Loop
   sql = "select 房屋编号 from House"
   If rst.State = adStateOpen Then
        rst.Close
   End If
   rst.CursorLocation = adUseClient
   rst.Open sql, conn, adOpenStatic, adLockBatchOptimistic
   Do Until rst.EOF
    Combo2.AddItem rst.Fields(0)
    rst.MoveNext
   Loop
   sqlstaff = "select 员工编号 from Staff"
   If rst.State = adStateOpen Then
        rst.Close
   End If
   rst.CursorLocation = adUseClient
   rst.Open sqlstaff, conn, adOpenStatic, adLockBatchOptimistic
   Do Until rst.EOF
    Combo3.AddItem rst.Fields(0)
    rst.MoveNext
   Loop
      '设置租期和总租金为不可写，其值为计算而得
      Text1(5).Enabled = False
      Text1(7).Enabled = False
      '设定签订日期和起租日期为当前日期
      Text1(10).Text = Date
      Text1(3).Text = Date
   '如果要显示的是房屋信息选项卡
   ElseIf SSTab1.Tab = 1 Then
      '开始时，由于租房合同没有显示，因此相应的房屋资料也都显示为空
      For i = 12 To 19
          Text1(i).Text = ""
      Next i
      '还需要设置上面的按钮除关闭按钮之外不可用
      cmdSign.Enabled = False
      cmdAddClient.Enabled = False
      cmdReset.Enabled = False
  End If
      
End Sub


Private Sub Form_Unload(Cancel As Integer)
   If rs_contract.State = adStateOpen Then
      rs_contract.Close
   End If
   Unload Me
End Sub

Private Sub SSTab1_Click(PreviousTab As Integer)
   If SSTab1.Tab = 1 Then
      '首先需要设置上面的按钮除关闭按钮之外不可用
      cmdSign.Enabled = False
      cmdAddClient.Enabled = False
      cmdReset.Enabled = False
      '如果租房合同选项卡没有房屋编号，则房屋资料显示为空
      If Text1(2).Text = "" Then
         For i = 12 To 19
            Text1(i).Text = ""
         Next i
         Exit Sub
         
      End If
      '如果房屋编号不为空
      If rs_house.State = adStateOpen Then
         rs_house.Close
      End If
      sqlhouse = "select * from house where 房屋编号 = '" & Text1(2).Text & "'"
      rs_house.Open sqlhouse, conn, adOpenStatic, adLockOptimistic
      '如果该房屋编号不存在，则提示用户
      If rs_house.EOF = True Then
         MsgBox "该房屋编号不存在！", vbOKOnly + vbInformation, "注意"
         For i = 12 To 19
            Text1(i).Text = ""
         Next i
         Exit Sub
      Else
         For i = 12 To 19
            Text1(i).Text = rs_house.Fields(i - 12)
         Next i
         If rs_house.Fields(8) = "未租" Then
            Combo1.ListIndex = 0
         ElseIf rs_house.Fields(8) = "已租" Then
            Combo1.ListIndex = 1
         Else
            Combo1.ListIndex = 2
         End If
      End If
      
   Else
      '当单击租房合同选项卡时，只需要把所有按钮设为可用即可
      cmdSign.Enabled = True
      cmdAddClient.Enabled = True
      cmdReset.Enabled = True
   End If
            
End Sub