frmstoragequery.frm

通用书店管理系统

         Splits(0)._ColumnProps(4)=   "Column(0)._ColStyle=516"
         Splits(0)._ColumnProps(5)=   "Column(0).Order=1"
         Splits(0)._ColumnProps(6)=   "Column(1).Width=3281"
         Splits(0)._ColumnProps(7)=   "Column(1).DividerColor=0"
         Splits(0)._ColumnProps(8)=   "Column(1)._WidthInPix=3175"
         Splits(0)._ColumnProps(9)=   "Column(1)._ColStyle=516"
         Splits(0)._ColumnProps(10)=   "Column(1).Order=2"
         Splits.Count    =   1
         PrintInfos(0)._StateFlags=   0
         PrintInfos(0).Name=   "piInternal 0"
         PrintInfos(0).PageHeaderFont=   "Size=9,Charset=134,Weight=400,Underline=0,Italic=0,Strikethrough=0,Name=宋体"
         PrintInfos(0).PageFooterFont=   "Size=9,Charset=134,Weight=400,Underline=0,Italic=0,Strikethrough=0,Name=宋体"
         PrintInfos(0).PageHeaderHeight=   0
         PrintInfos(0).PageFooterHeight=   0
         PrintInfos.Count=   1
         ColumnFooters   =   -1  'True
         DefColWidth     =   0
         HeadLines       =   1
         FootLines       =   1
         MultipleLines   =   0
         CellTipsWidth   =   0
         DeadAreaBackColor=   13160660
         RowDividerColor =   13160660
         RowSubDividerColor=   13160660
         DirectionAfterEnter=   1
         MaxRows         =   250000
         ViewColumnCaptionWidth=   0
         ViewColumnWidth =   0
         _PropDict       =   "_ExtentX,2003,3;_ExtentY,2004,3;_LayoutType,512,2;_RowHeight,16,3;_StyleDefs,513,0;_WasPersistedAsPixels,516,2"
         _StyleDefs(0)   =   "_StyleRoot:id=0,.parent=-1,.alignment=3,.valignment=0,.bgcolor=&H80000005&"
         _StyleDefs(1)   =   ":id=0,.fgcolor=&H80000008&,.wraptext=0,.locked=0,.transparentBmp=0"
         _StyleDefs(2)   =   ":id=0,.fgpicPosition=0,.bgpicMode=0,.appearance=0,.borderSize=0,.ellipsis=0"
         _StyleDefs(3)   =   ":id=0,.borderColor=&H80000005&,.borderType=0,.bold=0,.fontsize=900,.italic=0"
         _StyleDefs(4)   =   ":id=0,.underline=0,.strikethrough=0,.charset=134"
         _StyleDefs(5)   =   ":id=0,.fontname=宋体"
         _StyleDefs(6)   =   "Style:id=1,.parent=0,.namedParent=33"
         _StyleDefs(7)   =   "CaptionStyle:id=4,.parent=2,.namedParent=37"
         _StyleDefs(8)   =   "HeadingStyle:id=2,.parent=1,.namedParent=34,.alignment=2"
         _StyleDefs(9)   =   "FooterStyle:id=3,.parent=1,.namedParent=35"
         _StyleDefs(10)  =   "InactiveStyle:id=5,.parent=2,.bgcolor=&H8000000F&,.fgcolor=&H80000012&"
         _StyleDefs(11)  =   "SelectedStyle:id=6,.parent=1,.namedParent=36"
         _StyleDefs(12)  =   "EditorStyle:id=7,.parent=1"
         _StyleDefs(13)  =   "HighlightRowStyle:id=8,.parent=1,.namedParent=38"
         _StyleDefs(14)  =   "EvenRowStyle:id=9,.parent=1,.namedParent=39"
         _StyleDefs(15)  =   "OddRowStyle:id=10,.parent=1,.namedParent=40"
         _StyleDefs(16)  =   "RecordSelectorStyle:id=11,.parent=2,.namedParent=41"
         _StyleDefs(17)  =   "FilterBarStyle:id=12,.parent=1,.namedParent=42"
         _StyleDefs(18)  =   "Splits(0).Style:id=13,.parent=1"
         _StyleDefs(19)  =   "Splits(0).CaptionStyle:id=22,.parent=4"
         _StyleDefs(20)  =   "Splits(0).HeadingStyle:id=14,.parent=2"
         _StyleDefs(21)  =   "Splits(0).FooterStyle:id=15,.parent=3"
         _StyleDefs(22)  =   "Splits(0).InactiveStyle:id=16,.parent=5"
         _StyleDefs(23)  =   "Splits(0).SelectedStyle:id=18,.parent=6"
         _StyleDefs(24)  =   "Splits(0).EditorStyle:id=17,.parent=7"
         _StyleDefs(25)  =   "Splits(0).HighlightRowStyle:id=19,.parent=8"
         _StyleDefs(26)  =   "Splits(0).EvenRowStyle:id=20,.parent=9"
         _StyleDefs(27)  =   "Splits(0).OddRowStyle:id=21,.parent=10"
         _StyleDefs(28)  =   "Splits(0).RecordSelectorStyle:id=23,.parent=11"
         _StyleDefs(29)  =   "Splits(0).FilterBarStyle:id=24,.parent=12"
         _StyleDefs(30)  =   "Splits(0).Columns(0).Style:id=86,.parent=13"
         _StyleDefs(31)  =   "Splits(0).Columns(0).HeadingStyle:id=83,.parent=14"
         _StyleDefs(32)  =   "Splits(0).Columns(0).FooterStyle:id=84,.parent=15"
         _StyleDefs(33)  =   "Splits(0).Columns(0).EditorStyle:id=85,.parent=17"
         _StyleDefs(34)  =   "Splits(0).Columns(1).Style:id=32,.parent=13"
         _StyleDefs(35)  =   "Splits(0).Columns(1).HeadingStyle:id=29,.parent=14"
         _StyleDefs(36)  =   "Splits(0).Columns(1).FooterStyle:id=30,.parent=15"
         _StyleDefs(37)  =   "Splits(0).Columns(1).EditorStyle:id=31,.parent=17"
         _StyleDefs(38)  =   "Named:id=33:Normal"
         _StyleDefs(39)  =   ":id=33,.parent=0"
         _StyleDefs(40)  =   "Named:id=34:Heading"
         _StyleDefs(41)  =   ":id=34,.parent=33,.valignment=2,.bgcolor=&H8000000F&,.fgcolor=&H80000012&"
         _StyleDefs(42)  =   ":id=34,.wraptext=-1"
         _StyleDefs(43)  =   "Named:id=35:Footing"
         _StyleDefs(44)  =   ":id=35,.parent=33,.valignment=2,.bgcolor=&H8000000F&,.fgcolor=&H80000012&"
         _StyleDefs(45)  =   "Named:id=36:Selected"
         _StyleDefs(46)  =   ":id=36,.parent=33,.bgcolor=&H8000000D&,.fgcolor=&H8000000E&"
         _StyleDefs(47)  =   "Named:id=37:Caption"
         _StyleDefs(48)  =   ":id=37,.parent=34,.alignment=2"
         _StyleDefs(49)  =   "Named:id=38:HighlightRow"
         _StyleDefs(50)  =   ":id=38,.parent=33,.bgcolor=&H8000000D&,.fgcolor=&H8000000E&"
         _StyleDefs(51)  =   "Named:id=39:EvenRow"
         _StyleDefs(52)  =   ":id=39,.parent=33,.bgcolor=&HFFFF00&"
         _StyleDefs(53)  =   "Named:id=40:OddRow"
         _StyleDefs(54)  =   ":id=40,.parent=33"
         _StyleDefs(55)  =   "Named:id=41:RecordSelector"
         _StyleDefs(56)  =   ":id=41,.parent=34"
         _StyleDefs(57)  =   "Named:id=42:FilterBar"
         _StyleDefs(58)  =   ":id=42,.parent=33"
      End
   End
End
Attribute VB_Name = "frmStorageQuery"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private strQuery As String
Private WithEvents Worktmp As ADODB.Recordset
Attribute Worktmp.VB_VarHelpID = -1
Dim X As New XArrayDB          '从表1
Dim blnOrder(11) As Boolean
Dim a As String



Public Sub cmdCancel_Click()
  Unload Me
End Sub

Private Sub chkFields_Click(Index As Integer)
  Select Case Index
    Case 0 '库区
      If chkFields(Index).Value = 1 Then
         cmdSearch(2).Enabled = True
      Else
         cmdSearch(2).Enabled = False
      End If
    Case 1 '图书类型
      If chkFields(Index).Value = 1 Then
         cmdSearch(3).Enabled = True
      Else
         cmdSearch(3).Enabled = False
      End If
    Case 2 '出版社
      If chkFields(Index).Value = 1 Then
         cmdSearch(4).Enabled = True
      Else
         cmdSearch(4).Enabled = False
      End If
    Case 3 '作者
      If chkFields(Index).Value = 1 Then
         cmdSearch(5).Enabled = True
      Else
         cmdSearch(5).Enabled = False
      End If
    Case 4 '书号
      If chkFields(Index).Value = 1 Then
         cmdSearch(6).Enabled = True
      Else
         cmdSearch(6).Enabled = False
      End If
    Case 5 '书名
      If chkFields(Index).Value = 1 Then
         cmdSearch(7).Enabled = True
      Else
         cmdSearch(7).Enabled = False
      End If
  End Select
End Sub

Private Sub cmdExit_Click(Index As Integer)
  Select Case Index
    Case 0
      Frame1(0).Visible = False
  End Select
End Sub

Private Sub cmdOK_Click(Index As Integer)
  Dim i As Integer
  Dim strSubQuery(6) As String
  Dim strFoot As String
  Dim sqlstring As String
  Dim rstmp As New ADODB.Recordset
  Dim rsNewTmp As New ADODB.Recordset
  
  For i = 0 To 5
    strSubQuery(i) = ""
  Next i
  
  
  Select Case Index
    Case 0
      '库区
      If chkFields(0).Value = 1 And Trim(txtFields(0).Text) <> "" Then
         strSubQuery(0) = "T2.chrStorageName ='" & Trim(txtFields(0).Text) & "'"
      End If
      
      '图书类型
      If chkFields(1).Value = 1 And Trim(txtFields(1).Text) <> "" Then
         strSubQuery(1) = "T3.ChrBookType like '%" & Trim(txtFields(1).Text) & "%'"
      End If
      
      '出版社
      If chkFields(2).Value = 1 And Trim(txtFields(2).Text) <> "" Then
         strSubQuery(2) = "T3.Chrbookconcern = '" & Trim(a) & "'"
      End If
      
      '作者
      If chkFields(3).Value = 1 And Trim(txtFields(3).Text) <> "" Then
         strSubQuery(3) = "T3.ChrAuthoer like '%" & Trim(txtFields(3).Text) & "%'"
      End If
      
       '书号
      If chkFields(4).Value = 1 And Trim(txtFields(4).Text) <> "" Then
         strSubQuery(4) = "T1.chrBookNO ='" & Trim(txtFields(4).Text) & "'"
      End If
      
      '书名
      If chkFields(5).Value = 1 And Trim(txtFields(5).Text) <> "" Then
         strSubQuery(5) = "T1.chrBookName like '%" & Trim(txtFields(5).Text) & "%'"
      End If
             
      strQuery = "where "
      
      For i = 0 To 5
         If Trim(strSubQuery(i)) <> "" Then
           strQuery = strQuery & strSubQuery(i) & " and "
      End If
         
      Next
      
      If Trim(strQuery) = "where" Then
            strQuery = strQuery & " 2=2 and "
      End If
      
      strQuery = Mid(strQuery, 1, Len(strQuery) - 4)
      
      If Trim(strQuery) = "where" Then '没选任何条件
         Frame1(0).Visible = False
         Exit Sub
      Else
       '明细
         If chkFields(6).Value = 1 And Combo1.Text = "以数量排" Then
            sqlstring = "SELECT T2.ChrStorageName, T1.ChrBookNo,  T1.ChrBookName,T3.ChrAuthoer, T3.DecPrice,  T1.IntAmount, T3.DecPrice* T1.IntAmount as decMoney,  t1.DecZMY, t1.DecCKSY ,t1.DecAPrice*T1.IntAmount as DecZSY, T3.ChrProduceType," & _
                     "T3.ChrBookType,T4.ChrCompanyName FROM ((BookStorage T1 Left JOIN BookData T3 ON (T1.ChrBookName = T3.ChrBookName) AND (T1.ChrBookNo = T3.ChrBookNo)) Left JOIN " & _
                     "StorageSection T2 ON T1.ChrStorageNo = T2.ChrStorageNo) Left JOIN PublishingCompanyData T4 ON (T3.chrbookconcern = T4.chrCompanyNo)   " & strQuery & " order by T1.IntAmount desc"
        End If
        If chkFields(6).Value = 1 And Combo1.Text = "以书号排" Then
            sqlstring = "SELECT T2.ChrStorageName, T1.ChrBookNo,  T1.ChrBookName,T3.ChrAuthoer, T3.DecPrice,  T1.IntAmount, T3.DecPrice* T1.IntAmount as decMoney,  t1.DecZMY, t1.DecCKSY ,t1.DecAPrice*T1.IntAmount as DecZSY, T3.ChrProduceType," & _
                         "T3.ChrBookType,T4.ChrCompanyName FROM ((BookStorage T1 Left JOIN BookData T3 ON (T1.ChrBookName = T3.ChrBookName) AND (T1.ChrBookNo = T3.ChrBookNo)) Left JOIN " & _
                         "StorageSection T2 ON T1.ChrStorageNo = T2.ChrStorageNo) Left JOIN PublishingCompanyData T4 ON (T3.chrbookconcern = T4.chrCompanyNo)   " & strQuery & " order by T1.ChrBookNo desc"
        End If
        If chkFields(6).Value = 1 And Combo1.Text = "以出版社排" Then
            sqlstring = "SELECT T2.ChrStorageName, T1.ChrBookNo,  T1.ChrBookName,T3.ChrAuthoer, T3.DecPrice,  T1.IntAmount, T3.DecPrice* T1.IntAmount as decMoney,  t1.DecZMY, t1.DecCKSY ,t1.DecAPrice*T1.IntAmount as DecZSY, T3.ChrProduceType," & _
                     "T3.ChrBookType,T4.ChrCompanyName FROM ((BookStorage T1 Left JOIN BookData T3 ON (T1.ChrBookName = T3.ChrBookName) AND (T1.ChrBookNo = T3.ChrBookNo)) Left JOIN " & _