borrowsearch.vb

图书管理系统 书籍的管理应用 源码可以直接用

        If isRelation(index) Then
            InfoMSG("非法的条件！")
            Exit Sub
        End If
        lstCondition.Items(index) = lstCondition.Items(index) + ")"
        Dim tmpquery As QueryDescription = linkQuery.Item(index)
        tmpquery.setrightBracket()
    End Sub
    '判断条件是否是逻辑操作
    Private Function isRelation(ByVal index As Integer) As Boolean
        If lstCondition.Items(index).ToString().Equals("并且") Or lstCondition.Items(index).ToString().Equals("或者") Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        queryDesc = New QueryDescription
        '判断是否已构建完整的查询条件
        If cmbSets.SelectedIndex < 0 Or cmbItems.SelectedIndex < 0 Or cmbOper.SelectedIndex < 0 Or (cmbValue.Text = "" And cmbValue.SelectedIndex < 0) Then
            InfoMSG("信息集或查询信息项不能为空！")
            Exit Sub
        End If
        '当查询价格、最大可借阅册数或最大可借阅天数时，判断查询值是否为数字
        If cmbItems.SelectedText.Equals("价格") Or cmbItems.SelectedText.Equals("最大可借阅册数") Or cmbItems.SelectedText.Equals("最大可借阅天数") Then
            If Not IsNumeric(cmbValue.Text) Then
                InfoMSG("查询值必须为数字！")
            End If
            Exit Sub
        End If
        '在条件列表中添加显示
        lstCondition.Items.Add(cmbItems.Text + " " + cmbOper.Text + " " + cmbValue.Text.Trim)
        '设置查询条件
        setQueryDescription()
        '在条件链表中添加查询条件
        linkQuery.Add(queryDesc)
    End Sub
    Private Function setQueryDescription()
        '设置表名
        If Not setSelected Is Nothing Then
            queryDesc.setchiTable(setSelected.getSetChiName())
            queryDesc.setsqlTable(setSelected.getSetSqlName())
        End If
        '设置字段名
        If Not fieldSelected Is Nothing Then
            queryDesc.setchiField(fieldSelected.getFieldChiName())
            queryDesc.setsqlField(fieldSelected.getFieldSqlName())
        End If
        '设置关系运算符
        setOper()
        '设置查询值
        '如果选定字段有代码项
        If fieldSelected.getCodes().Count <> 0 Then
            queryDesc.setchiValue(codeSelected.getCodeExp())
            queryDesc.setsqlValue(codeSelected.getCodeID())
        Else
            queryDesc.setchiValue(cmbValue.Text)
            '如果是模糊查询，对查询值做进一步处理
            If queryDesc.getchiOper().Equals(Me.LIKING) Then
                queryDesc.setsqlValue("'%" + cmbValue.Text + "%'")
            Else
                '如果要查询价格、最大可借阅册数或最大可借阅天数，设置查询值为数字
                If cmbItems.Text = "价格" Or cmbItems.Text = "最大可借阅天数" Or cmbItems.Text = "最大可借阅册数" Then
                    queryDesc.setsqlValue(cmbValue.Text)
                Else
                    '否则设置查询值为字符串
                    queryDesc.setsqlValue("'" + cmbValue.Text + "'")
                End If
            End If
        End If
        '设置逻辑操作符
        queryDesc.setchiRelation("")
        queryDesc.setsqlRelation("")
    End Function
    Private Function setOper()
        If cmbOper.Text.Equals(Me.BIG) Then
            '大于
            queryDesc.setchiOper(Me.BIG)
            queryDesc.setsqlOper(">")
        ElseIf cmbOper.Text.Equals(Me.EQUAL) Then
            '等于 
            queryDesc.setchiOper(Me.EQUAL)
            queryDesc.setsqlOper("=")
        ElseIf cmbOper.Text.Equals(Me.SMAll) Then
            '小于
            queryDesc.setchiOper(Me.SMAll)
            queryDesc.setsqlOper("<")
        ElseIf cmbOper.Text.Equals(Me.BIG_EQUAL) Then
            '大于等于
            queryDesc.setchiOper(Me.BIG_EQUAL)
            queryDesc.setsqlOper(">=")
        ElseIf cmbOper.Text.Equals(Me.SMALL_EQUAL) Then
            '小于等于
            queryDesc.setchiOper(Me.SMALL_EQUAL)
            queryDesc.setsqlOper("<=")
        ElseIf cmbOper.Text.Equals(Me.NO_EQUAL) Then
            '不等于
            queryDesc.setchiOper(Me.NO_EQUAL)
            queryDesc.setsqlOper("<>")
        ElseIf cmbOper.Text.Equals(Me.LIKING) Then
            '类似
            queryDesc.setchiOper(Me.LIKING)
            queryDesc.setsqlOper(" like ")
        End If
    End Function

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim index As Integer
        queryDesc = New QueryDescription
        '判断是否已选定待删除条件
        If lstCondition.SelectedIndex < 0 Then
            InfoMSG("请在条件列表中选中要删除的条件。")
            Exit Sub
        End If
        '得到选定条件序号
        index = lstCondition.SelectedIndex
        '从条件显示列表中删去
        lstCondition.Items.RemoveAt(index)
        '从条件链表中删去
        linkQuery.RemoveAt(index)
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        '判断查询条件是否为空   
        If lstCondition.Items.Count = 0 Then
            InfoMSG("查询条件为空！")
            Exit Sub
        End If
        '询问用户是否确定清空
        Dim tmpmsg As String
        tmpmsg = MsgBox("确定要清空查询条件吗？", vbYesNo, "书籍借阅管理系统")
        If tmpmsg = vbNo Then Exit Sub
        '清空条件显示列表
        lstCondition.Items.Clear()
        '清空条件链表
        linkQuery.Clear()
    End Sub

    Private Function getQuerySql() As String
        '分别记录三个表的SQL语句
        'sqlOfTable(0):BookBorrow ;sqlOfTable(1):BookInfo ;sqlOfTable(2):ReaderInfo
        Dim sqlOfTable() As String = {"select ISBN,ReaderID,BorrowDate,ReturnDate,Deadline from BookBorrow where ", "select ISBN from BookInfo where ", "select ID from ReaderInfo where "}
        '最终SQL语句
        Dim lastSql As String = ""
        '如果条件链表为空，则返回空字符串
        If linkQuery Is Nothing Or lstCondition.Items.Count < 0 Then
            Return ""
        End If
        Dim i As Integer
        '记录当前条件属于那个表
        Dim curIndex As Integer = 0
        '记录上一条是否是逻辑操作，若不是，默认逻辑操作为and
        Dim isRelation As Boolean = False
        '记录当前条件
        Dim tmpQuery As New QueryDescription
        '记录当前条件所属表
        Dim curTable As String

        For i = 0 To linkQuery.Count - 1
            '得到第i个条件
            tmpQuery = linkQuery(i)
            '若不是逻辑操作，获取curIndex（若是，curIndex值不变）
            If tmpQuery.getsqlRelation() <> "" Then
                '更新表查询语句
                sqlOfTable(curIndex) = sqlOfTable(curIndex) + tmpQuery.getsqlRelation()
            Else
                '得到表名
                curTable = tmpQuery.getsqlTable
                '判断属于哪个表，这样才能确定将当前条件加入到哪个表查询语句中
                Select Case curTable
                    Case "BookBorrow" : curIndex = 0
                    Case "BookInfo" : curIndex = 1
                    Case "ReaderInfo" : curIndex = 2
                End Select
                '得到curIndex值后，更新表查询语句
                sqlOfTable(curIndex) = sqlOfTable(curIndex) + tmpQuery.getsqlField + tmpQuery.getsqlOper + tmpQuery.getsqlValue
            End If
        Next
        '若表查询语句末尾是逻辑操作符（and或or），则将其去掉
        '表BookBorrow
        If sqlOfTable(0).IndexOf("and") = sqlOfTable(0).Length - 4 Then
            sqlOfTable(0) = sqlOfTable(0).Substring(0, sqlOfTable(0).Length - 4)
        End If
        If sqlOfTable(0).IndexOf("or") = sqlOfTable(0).Length - 3 Then
            sqlOfTable(0) = sqlOfTable(0).Substring(0, sqlOfTable(0).Length - 3)
        End If
        '表BookInfo
        If sqlOfTable(1).IndexOf("and") = sqlOfTable(1).Length - 4 Then
            sqlOfTable(1) = sqlOfTable(1).Substring(0, sqlOfTable(1).Length - 4)
        End If
        If sqlOfTable(1).IndexOf("or") = sqlOfTable(1).Length - 3 Then
            sqlOfTable(1) = sqlOfTable(1).Substring(0, sqlOfTable(1).Length - 3)
        End If
        '表ReaderInfo
        If sqlOfTable(2).IndexOf("and") = sqlOfTable(2).Length - 4 Then
            sqlOfTable(2) = sqlOfTable(2).Substring(0, sqlOfTable(2).Length - 4)
        End If
        If sqlOfTable(2).IndexOf("or") = sqlOfTable(2).Length - 3 Then
            sqlOfTable(2) = sqlOfTable(2).Substring(0, sqlOfTable(2).Length - 3)
        End If
        '设置最终查询语句
        lastSql = lastSql + sqlOfTable(0)
        '如果书籍信息表查询语句发生变化，说明有书籍信息限定条件
        If sqlOfTable(1) <> "select ISBN from BookInfo where " Then
            '判断BookBorrow表是否有限定条件，以确定是否在连接语句中加and；如果不进行判断，则可能造成SQL语句中有两个连续的and
            If sqlOfTable(0) <> "select ISBN,ReaderID,BorrowDate,ReturnDate,Deadline from BookBorrow where " Then
                lastSql = lastSql + " and ISBN in (" + sqlOfTable(1) + ")"
            Else
                lastSql = lastSql + " ISBN in (" + sqlOfTable(1) + ")"
            End If
        End If
        '如果读者信息表查询语句发生变化，说明有读者信息限定条件
        If sqlOfTable(2) <> "select ID from ReaderInfo where " Then
            '判断BookBorrow表是否有限定条件，以确定是否在连接语句中加and
            If sqlOfTable(0) <> "select ISBN,ReaderID,BorrowDate,ReturnDate,Deadline from BookBorrow where " Then
                lastSql = lastSql + " and ReaderID in(" + sqlOfTable(2) + ")"
            ElseIf sqlOfTable(0) <> "select ISBN,ReaderID,BorrowDate,ReturnDate,Deadline from BookBorrow where " Then
                lastSql = lastSql + " ReaderID in(" + sqlOfTable(2) + ")"
            End If
        End If
        '返回最终查询语句
        Return lastSql
    End Function

    Private Sub btnQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuery.Click
        '判断查询条件是否为空
        If lstCondition.Items.Count = 0 Then
            InfoMSG("请定制查询条件")
            Exit Sub
        End If
        Dim querySql As String
        Dim mydet As DataSet
        '得到查询语句
        querySql = getQuerySql()
        '执行SQL语句，得到结果集
        mydet = DBManager.executeSelectQuery(querySql, "BorrowSearch")
        '若没有符合条件的记录，给出提示信息，并退出
        If mydet Is Nothing OrElse mydet.Tables(0).Rows.Count = 0 Then
            InfoMSG("没有符合条件的记录！")
            Exit Sub
        End If
        '显示结果列表窗体
        Dim frm As New ResultList(mydet)
        frm.MdiParent = Me.MdiParent
        frm.Show()
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

End Class