serverfrm.frm

短信与酒店管理系统

    Set rs = DBCnn.Execute(strSql)
End Function

Function RunSql(strSql As String, str As String) As String
    Dim rs As Recordset
    Set rs = DBCnn.Execute(strSql)
    If rs.RecordCount = 0 Then
        RunSql = RunSql & "*本班暂时还没有" & str & "登记手机号码!"
        Exit Function
    End If
    For i = 1 To rs.RecordCount
        RunSql = RunSql & "*" & Trim(rs.Fields(0).Value) & "*" & Trim(rs.Fields(1).Value) & "*" & Trim(rs.Fields(2).Value)
        rs.MoveNext
    Next i
End Function

Function Mcode(Ocode As String, NewCode As String, index As Integer) As String
    Dim UserID As String
    Dim strSql As String
    Dim i As Integer
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Mcode = "User*"
    For i = 1 To ClMax
        If Client(i).index = index Then
            UserID = Client(i).UsrID
        End If
    Next i
    strSql = "select * from class where cl_id='" & UserID & "' and cl_code='" & Ocode & "'"
    Set rs1 = DBCnn.Execute(strSql)
    If rs1.RecordCount = 0 Then
        Mcode = Mcode & "原密码输入错误！"
        Exit Function
    End If
    strSql = "Update class set cl_code='" & NewCode & "' where cl_id='" & UserID & "'"
    Set rs2 = DBCnn.Execute(strSql)
    Mcode = Mcode & "密码修改成功！"
End Function

Function AddPhone(Xuehao As String, Name As String, Phone As String, Ptype As String, index As Integer) As String
    Dim rs1 As Recordset, rs2 As Recordset, rs3 As Recordset, rs As Recordset
    Dim rs4 As Recordset, rs5 As Recordset, rs6 As Recordset
    Dim strSql As String
    Dim Exist As Boolean
    Dim UserID As String
    AddPhone = "Phone*Add*"
    For i = 1 To ClMax
        If Client(i).index = index Then
            UserID = Client(i).UsrID
        End If
    Next i
    strSql = "select * from student where s_id='" & Xuehao & "'"
    Set rs = DBCnn.Execute(strSql)
    If rs.RecordCount = 0 Then
        Exist = False
    Else
        Exist = True
    End If
    If Ptype = "01" Then
        If Exist = True Then
            strSql = "select * from student where s_phone<>'' and s_id='" & Xuehao & "'"
            Set rs1 = DBCnn.Execute(strSql)
            If rs1.RecordCount > 0 Then
                AddPhone = AddPhone & "该学生手机号码已存在!"
            Else
                strSql = "Update student set s_phone='" & Phone & "',s_name='" & Name & "' where s_id='" & Xuehao & "'"
                Set rs2 = DBCnn.Execute(strSql)
                AddPhone = AddPhone & "学生手机号码增加成功！"
            End If
        Else
            strSql = "insert into student(s_id,s_name,s_phone,cl_id)values('" & Xuehao & "','" & Name & "','" & Phone & "','" & UserID & "')"
            Set rs3 = DBCnn.Execute(strSql)
            AddPhone = AddPhone & "学生手机号码增加成功！"
        End If
    Else
            If Exist = True Then
                strSql = "select * from student where parent_phone<>'' and s_id='" & Xuehao & "'"
                Set rs6 = DBCnn.Execute(strSql)
                If rs6.RecordCount > 0 Then
                    AddPhone = AddPhone & "该家长手机号码已存在!"
                Else
                    strSql = "Update student set parent_phone='" & Phone & "',parent_name='" & Name & "' where s_id='" & Xuehao & "'"
                    Set rs4 = DBCnn.Execute(strSql)
                    AddPhone = AddPhone & "家长手机号码增加成功！"
                End If
            Else
                    strSql = "insert into student(s_id,parent_name,parent_phone,cl_id)values('" & Xuehao & "','" & Name & "','" & Phone & "','" & UserID & "')"
                    Set rs5 = DBCnn.Execute(strSql)
                    AddPhone = AddPhone & "家长手机号码增加成功！"
            End If
    End If
End Function

Function MPhone(Xuehao As String, num As String, Ptype As String) As String
    Dim strSql As String
    Dim rs As Recordset, rs1 As Recordset
    MPhone = "Phone*Modify*"
    strSql = "select * from student where s_id='" & Xuehao & "'"
    Set rs = DBCnn.Execute(strSql)
    If rs.RecordCount = 0 Then
        MPhone = MPhone & "该学号不存在！"
        Exit Function
    Else
        If Ptype = "01" Then
            strSql = "Update student set s_phone='" & num & "' where s_id='" & Xuehao & "'"
        Else
            strSql = "Update student set parent_phone='" & num & "' where s_id='" & Xuehao & "'"
        End If
    End If
    Set rs1 = DBCnn.Execute(strSql)
    MPhone = MPhone & "修改成功！"
End Function

Function ReadMsg(MType As Integer, index As Integer, Optional class As String = "")
    Dim strSql(5), Ostr As String
    Dim str(5), UserID, Back As String
    Dim i, j As Integer
    Dim rs(5) As Recordset
    ReadMsg = "Read"
    If index = -1 Then
        UserID = class
    Else
        For i = 1 To ClMax
            If Client(i).index = index Then
                UserID = Client(i).UsrID
            End If
        Next i
    End If
    strSql(1) = "select sm_id,s_name,sm_time,sm_content,state,s_phone from message,student where s_phone=sender " & " and cl_id='" & UserID & "'"
    strSql(2) = "select sm_id,parent_name,sm_time,sm_content,state,parent_phone from message,student where parent_phone=sender " & " and cl_id='" & UserID & "'"
    strSql(3) = "select sm_id,s_name,sm_time,sm_content,state,parent_phone from message,student where s_phone=receiver" & " and cl_id='" & UserID & "'"
    strSql(4) = "select sm_id,parent_name,sm_time,sm_content,state,parent_phone from message,student where parent_phone=receiver" & " and cl_id='" & UserID & "'"
    strSql(5) = "SELECT sm_id,receiver,sm_time,sm_content,state,receiver FROM message WHERE sender = '"
    strSql(5) = strSql(5) & UserID & "' AND receiver NOT IN(SELECT s_phone From student WHERE cl_id = '" & UserID
    strSql(5) = strSql(5) & "') AND receiver NOT IN(SELECT parent_phone From student WHERE cl_id='" & UserID & "')"
    Ostr = " order by sm_time desc"
    For i = 1 To 5
        If MType <> 4 Then
            strSql(i) = strSql(i) & " and state=" & MType
        End If
        strSql(i) = strSql(i) & Ostr
        Set rs(i) = DBCnn.Execute(strSql(i))
        If rs(i).RecordCount <> 0 Then
            For j = 1 To rs(i).RecordCount
                str(i) = str(i) & "*" & Trim(rs(i).Fields(0).Value) & "*" & Trim(rs(i).Fields(1).Value)
                str(i) = str(i) & "*" & Trim(rs(i).Fields(2).Value) & "*" & rs(i).Fields(3).Value & "*" & rs(i).Fields(4).Value
                str(i) = str(i) & "*" & Trim(rs(i).Fields(5).Value)
                rs(i).MoveNext
            Next j
        End If
    Next i
    If MType = 1 Or MType = 0 Then
        If rs(1).RecordCount = 0 And rs(2).RecordCount = 0 Then
            If MType = 0 Then
                Back = "未读"
            Else
                Back = "已读"
            End If
            ReadMsg = ReadMsg & "*本班暂时没有" & Back & "短信！"
        Else
            ReadMsg = ReadMsg & "*01" & str(1) & str(2)
        End If
    ElseIf MType <> 4 Then
        If rs(3).RecordCount = 0 And rs(4).RecordCount = 0 And rs(5).RecordCount = 0 Then
        If MType = 2 Then
                Back = "未发送"
            Else
                Back = "已发送"
            End If
            ReadMsg = ReadMsg & "*本班暂时没有" & Back & "短信！"
        Else
            ReadMsg = ReadMsg & "*02" & str(3) & str(4) & str(5)
        End If
    Else
        If rs(1).RecordCount = 0 And rs(2).RecordCount = 0 And rs(3).RecordCount = 0 And _
        rs(4).RecordCount = 0 And rs(5).RecordCount = 0 Then
            ReadMsg = ReadMsg & "*本班暂时没有任何短信！"
        Else
            ReadMsg = ReadMsg & "*03" & str(1) & str(2) & str(3) & str(4) & str(5)
        End If
    End If
End Function

Function DeleteMsg(ByRef StrGet() As String) As String
    Dim i As Integer
    Dim strSql As String
    Dim rs() As Recordset
    ReDim rs(UBound(StrGet))
    DeleteMsg = "DeleteMsg*"
    For i = 1 To UBound(StrGet)
        strSql = "delete from message where sm_id=" & Val(StrGet(i))
        Set rs(i) = DBCnn.Execute(strSql)
    Next i
    DeleteMsg = DeleteMsg & "删除成功！"
End Function

Private Sub SetState(Sm_id As Integer)
    Dim strSql As String
    Dim rs As Recordset
    strSql = "update message set state=1 where sm_id=" & Sm_id
    Set rs = DBCnn.Execute(strSql)
End Sub

Public Function ViewInfo(index As Integer, Optional class As String) As String
    Dim i, j As Integer
    Dim UserID As String
    Dim strSql As String
    Dim rs As Recordset
    ViewInfo = "SInformation*0"
    If index = -1 Then
        UserID = class
        ViewInfo = ViewInfo & "5"
    Else
        For i = 1 To ClMax
            If Client(i).index = index Then
                UserID = Client(i).UsrID
            End If
        Next i
        ViewInfo = ViewInfo & "1"
    End If
    strSql = "select s_id,s_name,s_phone,parent_name,parent_phone,parent_other "
    strSql = strSql & "from student where cl_id='" & UserID & "'"
    Set rs = DBCnn.Execute(strSql)
    If rs.RecordCount = 0 Then
        ViewInfo = ViewInfo & "本班没有任何学生信息！"
        Exit Function
    End If
    For i = 1 To rs.RecordCount
        For j = 0 To 5
            ViewInfo = ViewInfo & "*" & Trim(rs.Fields(j).Value)
        Next j
        rs.MoveNext
    Next i
End Function

Function SUpdate(ByRef StrGet() As String) As String
    Dim strSql As String
    Dim rs As Recordset
    Dim i As Integer
    strSql = "update student set s_name='" & StrGet(3) & "', s_phone='" & StrGet(4)
    strSql = strSql & "', parent_name='" & StrGet(5) & "', parent_phone='" & StrGet(6)
    strSql = strSql & "', parent_other='" & StrGet(7) & "' where s_id='" & StrGet(2) & "'"
    Set rs = DBCnn.Execute(strSql)
    SUpdate = "SInformation*02*修改成功！"
End Function

Function SView(Snum As String) As String
    Dim i As Integer
    Dim strSql As String
    Dim rs As Recordset
    SView = "SInformation*03"
    strSql = "select s_id,s_name,s_phone,parent_name,parent_phone,parent_other "
    strSql = strSql & "from student where s_id='" & Snum & "'"
    Set rs = DBCnn.Execute(strSql)
    If rs.RecordCount = 0 Then
        SView = SView & "*本班没有这个学生登记学生信息！"
    Else
        For i = 0 To 5
            SView = SView & "*" & Trim(rs.Fields(i).Value)
        Next i
    End If
End Function

Function InfoDel(Snum As String) As String
    Dim i As Integer
    Dim strSql As String
    Dim rs As Recordset
    InfoDel = "SInformation*04"
    strSql = "delete from student where s_id='" & Snum & "'"
    Set rs = DBCnn.Execute(strSql)
    InfoDel = InfoDel & "*删除成功！"
End Function

Function AddClass(cl_id As String, Daoshi As String, cl_code As String) As String
    Dim strSql(2) As String
    Dim rs(2) As Recordset
    strSql(1) = "select * from class where cl_id='" & cl_id & "'"
    Set rs(1) = DBCnn.Execute(strSql(1))
    If rs(1).RecordCount > 0 Then
        AddClass = "AddClass*该班级已存在！"
        Exit Function
    End If
    strSql(2) = "insert into class(cl_id,cl_monitor,cl_code)values('" & cl_id & "','" & Daoshi & "','" & cl_code & "')"
    Set rs(2) = DBCnn.Execute(strSql(2))
    AddClass = "AddClass*添加成功！"
End Function

Function ViewClass() As String
    Dim rs As Recordset
    Dim strSql As String
    ViewClass = "ViewClass"
    strSql = "select * from class"
    Set rs = DBCnn.Execute(strSql)
    If rs.RecordCount = 0 Then
        ViewClass = ViewClass & "*没有登记班信息！"
        Exit Function
    End If
    For i = 1 To rs.RecordCount
        ViewClass = ViewClass & "*" & Trim(rs.Fields(0).Value) & "*" & Trim(rs.Fields(1).Value) & "*" & Trim(rs.Fields(2).Value)
        rs.MoveNext
    Next i
End Function

Function DClass(ByRef StrGet() As String) As String
    Dim i As Integer
    Dim strSql As String
    Dim rs() As Recordset
    ReDim rs(UBound(StrGet))
    DClass = "DeleteClass*"
    For i = 1 To UBound(StrGet)
        strSql = "delete from class where cl_id='" & StrGet(i) & "'"
        Set rs(i) = DBCnn.Execute(strSql)
    Next i
    DClass = DClass & "删除成功！"
End Function

Function CUpdate(ByRef StrGet() As String) As String
    Dim strSql As String
    Dim rs As Recordset
    Dim i As Integer
    strSql = "update class set cl_monitor='" & StrGet(2) & "', cl_code='" & StrGet(3)
    strSql = strSql & "' where cl_id='" & StrGet(1) & "'"
    Set rs = DBCnn.Execute(strSql)
    CUpdate = "ModifyClass*修改成功！"
End Function

Function SClass() As String
    Dim rs As Recordset
    Dim strSql As String
    SClass = "SelectClass"
    strSql = "select cl_id from class"
    Set rs = DBCnn.Execute(strSql)
    If rs.RecordCount = 0 Then
        SClass = SClass & "*没有登记班信息！"
        Exit Function
    End If
    For i = 1 To rs.RecordCount
        SClass = SClass & "*" & Trim(rs.Fields(0).Value)
        rs.MoveNext
    Next i
End Function