frmjhsquery.frm

通用书店管理系统

'         Label1(0).Visible = False
'         Label1(1).Visible = False
'      End If
'    Case 4
'      If chkFields(Index).Value = 1 Then
'         dtpDate(2).Visible = True
'         dtpDate(3).Visible = True
'          Label1(3).Visible = True
'      Else
'         dtpDate(2).Visible = False
'         dtpDate(3).Visible = False
'         Label1(3).Visible = False
'      End If
    Case 5
      If chkFields(Index).Value = 1 Then
         cmdSearch(6).Enabled = True
      Else
         cmdSearch(6).Enabled = False
      End If
    Case 6
      If chkFields(Index).Value = 1 Then
         cmdSearch(7).Enabled = True
      Else
         cmdSearch(7).Enabled = False
      End If
    Case 7
      If chkFields(Index).Value = 1 Then
         cmdSearch(8).Enabled = True
      Else
         cmdSearch(8).Enabled = False
      End If
'    Case 8
'      If chkFields(Index).Value = 1 Then
'         dtpDate(4).Visible = True
'         dtpDate(5).Visible = True
'         Label1(4).Visible = True
'      Else
'         dtpDate(4).Visible = False
'         dtpDate(5).Visible = False
'         Label1(4).Visible = False
'      End If
    Case 9
      If chkFields(Index).Value = 1 Then
         cmdSearch(9).Enabled = True
      Else
         cmdSearch(9).Enabled = False
      End If
  End Select
End Sub





Private Sub cmdExit_Click(Index As Integer)
  Select Case Index
    Case 0
      Frame1(0).Visible = False
    Case 1
      Frame1(1).Visible = False
    Case 2
      Frame1(2).Visible = False
  End Select
End Sub
Private Sub cmdOK_Click(Index As Integer)
   Dim i As Integer
  Dim strFoot As String
  Dim sqlstring As String
  Dim rstmp As New ADODB.Recordset
  Dim rsNewTmp As New ADODB.Recordset
  
  For i = 0 To 3
    strSubQuery(i) = ""
  Next i
  
  Select Case Index
    Case 0
      '会员卡号
      If chkFields(0).Value = 1 And Trim(txtFields(0).Text) <> "" Then
         strSubQuery(0) = "IntMemberNo like '%" & Trim(txtFields(0).Text) & "%'"
      End If
      
      '书号
      If chkFields(1).Value = 1 And Trim(txtFields(1).Text) <> "" Then
         strSubQuery(1) = "chrBookNO like '%" & Trim(txtFields(1).Text) & "%'"
      End If
      
      '书名
      If chkFields(2).Value = 1 And Trim(txtFields(2).Text) <> "" Then
         strSubQuery(2) = "chrBookName like '%" & Trim(txtFields(2).Text) & "%'"
      End If
      
      '是否借书
      If chkFields(3).Value = 1 Then
         Select Case cmbFields(0)
           Case "是"
               strSubQuery(3) = "(DatJSDate between #" & Format(dtpDate(0).Value, "yyyy-mm-dd") & _
                                "# and #" & Format(dtpDate(1).Value, "yyyy-mm-dd") & "#)"
           Case "否"
               strSubQuery(3) = "DatTSGHDate is null"
         End Select
      End If
             
      strQuery(0) = "where "
      
      For i = 0 To 3
         If Trim(strSubQuery(i)) <> "" Then
           strQuery(0) = strQuery(0) & strSubQuery(i) & " and "
         End If
         
      Next
      
      If Trim(strQuery(0)) = "where" Then
            strQuery(0) = strQuery(0) & " 2=2 and "
        End If
      
      
      strQuery(0) = Mid(strQuery(0), 1, Len(strQuery(0)) - 4)
      
      If Trim(strQuery(0)) = "where" Then '没选任何条件
         Frame1(0).Visible = False
         Exit Sub
      Else
        '借书查询
        sqlstring = "SELECT IntMemberNo, ChrBookNo, ChrBookName, DatJSDate, Datediff('d', DatJSDate,now),ChrOperator,DatTSGHDate From BooksBorrow  " & strQuery(0) & " order by DatJSDate desc"
        
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        Set tdbQuery(0).DataSource = rstmp
        tdbQuery(0).ReBind
        Call SetGridheader("会员卡号|书号|书名|借书日期|借阅天数|操作员|图书归还日期", 0, "10|10|28|15|10|20|15", strFoot)
        Call clearAll
      End If
             
      Frame1(0).Visible = False
    Case 1
      '会员卡号
      If chkFields(7).Value = 1 And Trim(txtFields(3).Text) <> "" Then
         strSubQuery(0) = "IntMemberNo like '%" & Trim(txtFields(3).Text) & "%'"
      End If
      

      '书号
      If chkFields(6).Value = 1 And Trim(txtFields(4).Text) <> "" Then
         strSubQuery(1) = "chrBookNO like '%" & Trim(txtFields(4).Text) & "%'"
      End If
      
      '书名
      If chkFields(5).Value = 1 And Trim(txtFields(5).Text) <> "" Then
         strSubQuery(2) = "chrBookName like '%" & Trim(txtFields(5).Text) & "%'"
      End If
      
      '还书时间
'      If chkFields(4).Value = 1 Then
'         strSubQuery(3) = "DatJSDate between #" & dtpDate(2).Value & _
'      "# and #" & dtpDate(3).Value & "#"
'      End If
             
      strQuery(1) = "where "
      
      For i = 0 To 2
         If Trim(strSubQuery(i)) <> "" Then
           strQuery(1) = strQuery(1) & strSubQuery(i) & " and "
         End If
         
      Next
      
      
      If Trim(strQuery(1)) = "where" Then
            strQuery(1) = strQuery(1) & " 2=2 and "
        End If
      
      
      strQuery(1) = Mid(strQuery(1), 1, Len(strQuery(1)) - 4)
      
      If Trim(strQuery(1)) = "where" Then '没选任何条件
         Frame1(1).Visible = False
         Exit Sub
      Else
         '还书查询
        sqlstring = "SELECT IntMemberNo, ChrBookNo, ChrBookName, DatJSDate, Datediff('d', DatJSDate,DatTSGHDate),ChrOperator,DatTSGHDate From BooksBorrow " & strQuery(1) & " order by DatTSGHDate desc"
        
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        Set tdbQuery(1).DataSource = rstmp
        tdbQuery(1).ReBind
        Call SetGridheader("会员卡号|书号|书名|借书日期|借阅天数|操作员|图书归还日期", 1, "10|10|28|15|10|20|15", strFoot)
        Call clearAll
      End If
             
      Frame1(1).Visible = False
    Case 2
       '会员卡号
      If chkFields(9).Value = 1 And Trim(txtFields(6).Text) <> "" Then
         strSubQuery(0) = "IntMemberNo like '%" & Trim(txtFields(6).Text) & "%'"
      End If
      
      '是否在借书
      If chkFields(8).Value = 1 Then
         strSubQuery(1) = "DatJSDate between #" & Format(dtpDate(4).Value, "yyyy-mm-dd") & _
                                "# and #" & Format(dtpDate(5).Value, "yyyy-mm-dd") & "#"
      End If
             
      strQuery(2) = "where "
      
      For i = 0 To 1
         If Trim(strSubQuery(i)) <> "" Then
           strQuery(2) = strQuery(2) & strSubQuery(i) & " and "
         End If
         
      Next
      
      If Trim(strQuery(2)) = "where" Then
            strQuery(2) = strQuery(2) & " 2=2 and "
        End If
      
      
      
      strQuery(2) = Mid(strQuery(2), 1, Len(strQuery(2)) - 4)
      
      If Trim(strQuery(2)) = "where" Then '没选任何条件
         Frame1(2).Visible = False
         Exit Sub
      Else
         '在借书查询
        sqlstring = "SELECT IntMemberNo, ChrBookNo, ChrBookName, DatJSDate, Datediff('d', DatJSDate,now),ChrOperator From BooksBorrow  " & strQuery(2) & " order by DatJSDate desc"
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        Set tdbQuery(2).DataSource = rstmp
        tdbQuery(2).ReBind
        Call SetGridheader("会员卡号|书号|书名|借书日期|借阅天数|操作员", 2, "10|10|28|15|10|20", strFoot)
        Call clearAll
      End If
             
      Frame1(2).Visible = False
    
  End Select
End Sub
Public Sub cmdPrint_Click()
    Dim frm As New frmModCommonPrint
    Dim rstmp As New ADODB.Recordset
    Dim sqlstring As String
    Dim p As New ClsPrintInfo
    Dim arr, r&, c&
    Dim strAboveTable$, strBelowTable$, strSign$
    On Error GoTo err
  
  If Not checkpermission("书店管理系统", strUserName, , "客户管理.客户管理查询.借还书查询.打印") Then
       Exit Sub
  End If
  Select Case SSTab1.Caption
    Case "借书查询"
        
    With p
        
        '标题
        .cqFirstTitle.Content = "借书查询"
        .cqFirstTitle.FontSize = 18
        .cqFirstTitle.FontBold = True
        
        '表前叙述
                   
        .cqSayingAboveTable.Content = "打印于：|" & Format(Date, "yyyy-MM-dd")
        .cqSayingAboveTable.LayOut = "Body align=left cols=3 interwidth=50 |label align=left width=16|text align=left width=20"
                
       sqlstring = "SELECT IntMemberNo, ChrBookNo, ChrBookName, DatJSDate, Datediff('d', DatJSDate,now),ChrOperator,DatTSGHDate From BooksBorrow " & strQuery(0) & " order by DatJSDate desc"
       Set rstmp = New ADODB.Recordset
       rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        If rstmp.Recordcount > 0 Then
        arr = rstmp.GetRows
       End If
        
        .cqTable.Content = arr
        .cqTable.LayOut = " Format=^80|100|100|100|100|100|100" & _
                          " Header=会员卡号|书号|书名|借书日期|借阅天数|操作员|图书归还日期"
                          
    
            
        '采用传句柄方式
        '.cqTable.Hwnd = Me.grdDetail.Hwnd
    End With
        
    With frm
        Set .PrintInfo = p
        '设置重复打印部分
        Call .setRepeat(cp_RepeatView_All)
        '设置表格填充空行
        '.blnEmptyRow = True
        '设置表格最后行拉伸到满页
        '.blnExtenLastCol = True
        '设置表格自动调整列宽到满页
        .blnColumnForPage = True
        '设置汇总高度，视汇总行数而定
        .SubTotal_Height = 600
        '设置页高、页宽、行高及最大页数
        '.MaxRowsPerPage = 10
        '.Row_Height = 300
        .TopHeader_Height = 1
        .SubTotal_Height = 600
        .ParagraphInterRate = 0.4
        .PrintPaperSize = pprEnv9
        '设置打印信息保存位置
        .strPrintInfoName = "借书查询|" & Me.Caption
        
        .FormStart
        .Show vbModal
    End With
    Exit Sub

    Case "还书查询"
  
  With p