frmhistorystoragequery.frm

来自「通用书店管理系统」· FRM 代码 · 共 1,001 行 · 第 1/4 页

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
  
  Select Case Index
    Case 0
      '库区
      If chkFields(0).Value = 1 And Trim(txtFields(0).Text) <> "" Then
         strSubQuery(0) = "T1.chrStorageNO like '%" & 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.ChrGHS = '" & Trim(a) & "'"
'      End If
      
       '书号
      If chkFields(4).Value = 1 And Trim(txtFields(4).Text) <> "" Then
         strSubQuery(4) = "T1.chrBookNO like '%" & 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 Trim(strQuery) <> "" Then
           sqlstring = "SELECT T2.ChrStorageName, T1.ChrBookNo,  T1.ChrBookName,  T1.IntAmount,  T3.DecPrice* T1.IntAmount as decMoney,  T3.DecAgio*T3.decPrice*T1.IntAmount as decFactMoney,  T3.ChrProduceType," & _
                     "T3.ChrBookType, T3.ChrAuthoer, T4.ChrCompanyName FROM ((PDResult 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.chrCompanyName " & strQuery & " and chrPDDate=#" & strDate & "#"
        Else
           sqlstring = "SELECT T2.ChrStorageName, T1.ChrBookNo,  T1.ChrBookName,  T1.IntAmount,  T3.DecPrice* T1.IntAmount as decMoney,  T3.DecAgio*T3.decPrice*T1.IntAmount as decFactMoney,  T3.ChrProduceType," & _
                     "T3.ChrBookType, T3.ChrAuthoer, T4.ChrCompanyName FROM ((PDResult 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.chrCompanyName Where chrPDDate=#" & strDate & "#"
        End If
        
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        Set tdbQuery(0).DataSource = rstmp
        
        If Trim(strQuery) <> "" Then
           sqlstring = "SELECT sum(T1.intAmount),sum(T3.DecPrice* T1.IntAmount),  sum(T3.DecAgio*T3.decPrice*T1.IntAmount) " & _
                     "FROM ((PDResult 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.chrCompanyName " & strQuery & " and chrPDDate=#" & strDate & "#"
        Else
           sqlstring = "SELECT sum(T1.intAmount),sum(T3.DecPrice* T1.IntAmount),  sum(T3.DecAgio*T3.decPrice*T1.IntAmount) " & _
                     "FROM ((PDResult 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.chrCompanyName  and chrPDDate=#" & strDate & "#"
        End If
        
        
        Set rsNewTmp = New ADODB.Recordset
        rsNewTmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        
        If Not rsNewTmp.EOF Then
          strFoot = "|||" & Format(rsNewTmp.Fields(0), "#,##0") & "| " & Format(rsNewTmp.Fields(1), "#,##0.00") & "| " & Format(rsNewTmp.Fields(2), "#,##0.00") & _
                    "|||||"
        Else
          strFoot = "||||||||||||"
        End If
        Call SetGridheader("库区|书号|书名|数量|码洋|实洋|制品类型|图书类型|作者|出版社", 0, "7|15|25|15|15|15|10|13|8|15", strFoot)
        Call clearAll
      
      End If
             
      Frame1(0).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"
                
       If Trim(strQuery) <> "" Then
          sqlstring = "SELECT T2.ChrStorageName, T1.ChrBookNo,  T1.ChrBookName,  T1.IntAmount,  T3.DecPrice* T1.IntAmount as decMoney,  T3.DecAgio*T3.decPrice*T1.IntAmount as decFactMoney,  T3.ChrProduceType," & _
                   "T3.ChrBookType, T3.ChrAuthoer, T4.ChrCompanyName FROM ((PDResult 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.chrCompanyName  " & strQuery & " and chrPDDate=#" & strDate & "#"
       Else
          sqlstring = "SELECT T2.ChrStorageName, T1.ChrBookNo,  T1.ChrBookName,  T1.IntAmount,  T3.DecPrice* T1.IntAmount as decMoney,  T3.DecAgio*T3.decPrice*T1.IntAmount as decFactMoney,  T3.ChrProduceType," & _
                   "T3.ChrBookType, T3.ChrAuthoer, T4.ChrCompanyName FROM ((PDResult 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.chrCompanyName  Where chrPDDate=#" & strDate & "#'"
       End If
       
       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;#,##0|100;#,##0.00|100;#,##0.00|100|100|100|100" & _
                          " Header=库区|书号|书名|数量|码洋|实洋|制品类型|图书类型|作者|出版社" & _
                          " Subtotal=2\页总计\1\4\4\1\#,##0\2-1\1;1\总计\1\4\4\1\#,##0\2-1\2;" & _
                                   "2\页总计\1\5\5\1\#,##0.00\2-1\1;1\总计\1\5\5\1\#,##0\2-1\2;" & _
                                   "2\页总计\1\6\6\1\#,##0.00\2-1\1;1\总计\1\6\6\1\#,##0.00\2-1\2"
        
        '采用传句柄方式
        '.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
        
        '标题
        .cqFirstTitle.Content = "历史库存汇总"
        .cqFirstTitle.FontSize = 18
        .cqFirstTitle.FontBold = True
        
        '表前叙述