frmstoragequery.frm

通用书店管理系统

                                   "2\页总计\1\9\9\1\#,##0.00\2-1\1;1\总计\1\9\9\1\#,##0.00\2-1\2;" & _
                                   "2\页总计\1\10\10\1\#,##0.00\2-1\1;1\总计\1\10\10\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
        
        '表前叙述
                   
        .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 A.chrStorageName,A.chrProduceType,A.chrBookType,A.intTotalAmount,A.decTotalMoney,A.decRKTotalFactMoney, " & _
                   "A.decCKTotalFactMoney,A.decTotalSY, A.intTotalAmount/B.intTotalAmount*100 as decPercentAmount ," & _
                   "A.decTotalMoney/B.decTotalMoney*100 as decPercentMoney,A.decRKTotalFactMoney/B.decRKTotalFactMoney *100 as decRKPercentFactMoney," & _
                   "A.decCKTotalFactMoney/B.decCKTotalFactMoney *100 as decCKPercentFactMoney,A.decTotalSY/B.intTotalAmount*100 as decTotalSYPercent From " & _
                   "(select chrStorageName,chrProduceType,chrBookType, sum(IntAmount) as intTotalAmount,sum(decMoney) as decTotalMoney,sum(t1.DecZMY) as decRKTotalFactMoney," & _
                   "sum(t1.DecCKSY) as decCKTotalFactMoney,sum(decZSY) as decTotalSY From (SELECT T2.ChrStorageName, T3.ChrProduceType, T3.ChrBookType,  T1.IntAmount," & _
                   "T3.DecPrice* T1.IntAmount as decMoney,t1.DecZMY,t1.DecCKSY,t1.DecAPrice*t1.IntAmount as decZSY 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) A Group by chrStorageName,chrProduceType,chrBookType,DecZMY,DecCKSY) A " & _
                    "Left Join (select chrStorageName,chrProduceType,sum(IntAmount) as intTotalAmount, sum(decMoney) as decTotalMoney,sum(t1.DecZMY) as decRKTotalFactMoney, sum(t1.DecCKSY) as decCKTotalFactMoney," & _
                    "sum(decZSY) as decTotalSY From (SELECT T2.ChrStorageName,T3.ChrProduceType, T3.ChrBookType, T1.IntAmount,  T3.DecPrice* T1.IntAmount as decMoney,  t1.DecZMY,t1.DecCKSY ,t1.DecAPrice*t1.IntAmount as decZSY " & _
                    "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) A Group by chrStorageName,chrProduceType)B " & _
                    "on (A.chrStorageName=B.chrStorageName) and (A.chrProduceType=B.chrProduceType) order by A.intTotalAmount desc"
       
       Set rstmp = New ADODB.Recordset
       rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
       If rstmp.Recordcount > 0 Then
        arr = rstmp.GetRows
       End If
        
        '【Format属性】
            '宽度：整型
            '<   Align：左
            '^   Align：中
            '>   Align：右
            '=   以内容为准
            '+   纵向居中
            '_   纵向沉底
            '*   与文本Align保持一致
            '~   不自动换行
            '!   只画纵向线(保留)
        
        '【Header属性】
            '表格列头内容
        
        '【SpanCol属性】
            '有五个部分组成，中间用逗号隔开
            '第一部分：列索引，可一由“列头值”替代
            '第二部分：行索引，可以用MaxRow表示最大行，并进行运算，如：MaxRow-2；
            '                   也可以用实际的单元格值替换，比如：“合计”
            '第三部分：列偏差，由第一部分和第二部分可定位到“某个单元”，但实际要
            '                   合并的单元可能是“某个单元”的相邻单元，这时可使用偏差值
            '                  例如，想让“合计”行的“数量”列进行两列合并（假设“数量”列=“合计”所在列+2），
            '                  则可以在该部分填上2，如不需要，则填0
            '第四部分：合并列数，不包括自己，即合并一列则为1，合并两列则为2
            '第五部分：合并后内容的布局 0，左上；1，中上；2，右上；
            '                           3，左下；4，中下；5，右下；
            '                           6，左中；7，中中；8，右中；
            '                           9，上端；10，底端；11，中
        
        '【RowHeight属性】
            '强行指定行高，是一个整型,该值一旦设置，用户无法改变
        
        '【Subtotal属性】
            '合计信息，由以下几个部分组成,中间由逗号隔开，组之间用分号隔开
            '第一部分：列汇总类型：1，表示总计；2，表示页合计
            '第二部分：列汇总描述
            '第三部分：列汇总描述所在列索引
            '第四部分：汇总后数据所在位置
            '第五部分：列汇总所在列
            '第六部分：列汇总函数类型：1，表示求和；
            '第七部分：汇总数据格式
            '第八部分：汇总数据合并列；描述列合并数-汇总列合并数
            '第九部分：汇总数据所在行
            
            '例子:Subtotal=1\页小计\1\3\3\1\￥#,##0.00\1-1\1  --表示对第四列进行“求和”页合计，
        
'        Dim strHeaderFormat$, strHeader$
'        For C = 0 To tdbQuery(1).Columns.Count - 1
'            If C = tdbQuery(1).Columns.Count - 1 Then
'                strHeaderFormat = strHeaderFormat & "10"
'                strHeader = strHeader & tdbQuery(1).Columns(C).Caption
'            Else
'                strHeaderFormat = strHeaderFormat & "10|"
'                strHeader = strHeader & Trim(tdbQuery(1).Columns(C).Caption) & "|"
'            End If
'        Next C
        
        .cqTable.Content = arr
        .cqTable.LayOut = " Format=^80|100|100|100;#,##0|100;#,##0.00|100;#,##0.00|100;#,##0.00|100;#,##0.00|>100;#,##0.00|>100;#,##0.00|>100;#,##0.00|>100;#,##0.00|>100;#,##0.00" & _
                          " 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\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\2-1\2;" & _
                                   "2\页总计\1\7\7\1\#,##0.00\2-1\1;1\总计\1\7\7\1\#,##0.00\2-1\2;" & _
                                   "2\页总计\1\8\8\1\#,##0.00\2-1\1;1\总计\1\8\8\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
End Select
err:
    MsgBox "打印出错！"
End Sub


Private Sub cmdSearch_Click(Index As Integer)
  Dim arrQuery
  Dim i As Integer
  Dim sqlstring As String
  Dim rstmp As ADODB.Recordset
  Dim rsNewTmp As ADODB.Recordset
  Dim strFoot As String
  
  Select Case Index
    Case 0
      Frame1(0).Visible = True
    Case 1
         '汇总
        sqlstring = "select A.chrStorageName,A.chrProduceType,A.chrBookType,A.intTotalAmount,A.decTotalMoney,A.decRKTotalFactMoney, " & _
                  "A.decCKTotalFactMoney,A.decTotalSY, A.intTotalAmount/B.intTotalAmount*100 as decPercentAmount ," & _
                  "A.decTotalMoney/B.decTotalMoney*100 as decPercentMoney,A.decRKTotalFactMoney/B.decRKTotalFactMoney *100 as decRKPercentFactMoney," & _
                  "A.decCKTotalFactMoney/B.decCKTotalFactMoney *100 as decCKPercentFactMoney,A.decTotalSY/B.intTotalAmount*100 as decTotalSYPercent From " & _
                  "(select chrStorageName,chrProduceType,chrBookType, sum(IntAmount) as intTotalAmount,sum(decMoney) as decTotalMoney,sum(t1.DecZMY) as decRKTotalFactMoney," & _
                  "sum(t1.DecCKSY) as decCKTotalFactMoney,sum(decZSY) as decTotalSY From (SELECT T2.ChrStorageName, T3.ChrProduceType, T3.ChrBookType,  T1.IntAmount," & _
                  "T3.DecPrice* T1.IntAmount as decMoney,t1.DecZMY,t1.DecCKSY,t1.DecAPrice*t1.IntAmount as decZSY 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) A Group by chrStorageName,chrProduceType,chrBookType,DecZMY,DecCKSY) A " & _
                   "Left Join (select chrStorageName,chrProduceType,sum(IntAmount) as intTotalAmount, sum(decMoney) as decTotalMoney,sum(t1.DecZMY) as decRKTotalFactMoney, sum(t1.DecCKSY) as decCKTotalFactMoney," & _
                   "sum(decZSY) as decTotalSY From (SELECT T2.ChrStorageName,T3.ChrProduceType, T3.ChrBookType, T1.IntAmount,  T3.DecPrice* T1.IntAmount as decMoney,  t1.DecZMY,t1.DecCKSY ,t1.DecAPrice*t1.IntAmount as decZSY " & _
                   "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) A Group by chrStorageName,chrProduceType)B " & _
                   "on (A.chrStorageName=B.chrStorageName) and (A.chrProduceType=B.chrProduceType)  order by A.intTotalAmount desc"
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        Set tdbQuery(1).DataSource = rstmp
        sqlstring = "select sum(A.intTotalAmount),sum(A.decTotalMoney),sum(A.decRKTotalFactMoney),sum( A.decCKTotalFactMoney),sum(A.decTotalSY) From (select chrStorageName,chrProduceType,chrBookType," & _
                    "sum(IntAmount) as intTotalAmount,sum(decMoney) as decTotalMoney,sum(t1.DecZMY) as decRKTotalFactMoney,sum(t1.DecCKSY) as decCKTotalFactMoney,sum(decZSY) as decTotalSY From " & _
                    "(SELECT T2.ChrStorageName, T3.ChrProduceType, T3.ChrBookType,  T1.IntAmount,T3.DecPrice* T1.IntAmount as decMoney,t1.DecZMY,t1.DecCKSY,t1.DecAPrice*t1.IntAmount as decZSY 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) " & _
                    "A Group by chrStorageName,chrProduceType,chrBookType,DecZMY,DecCKSY) A Left Join (select chrStorageName,chrProduceType,sum(IntAmount) as intTotalAmount, sum(decMoney) as decTotalMoney," & _
                    "sum(t1.DecZMY) as decRKTotalFactMoney, sum(t1.DecCKSY) as decCKTotalFactMoney,sum(decZSY) as decTotalSY From (SELECT T2.ChrStorageName,T3.ChrProduceType, T3.ChrBookType, T1.IntAmount,  " & _
                    "T3.DecPrice* T1.IntAmount as decMoney,  t1.DecZMY,t1.DecCKSY ,t1.DecAPrice*t1.IntAmount as decZSY 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) A Group by chrStorageName,chrProduceType)B on (A.chrStorageName=B.chrStorageName) and (A.chrProduceType=B.chrProduceType) "
        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") & _
                    "|" & Format(rsNewTmp.Fields(3), "#,##0.00") & "|" & Format(rsNewTmp.Fields(4), "#,##0.00") & "|||||"
        Else
          strFoot = "||||||||||||"
        End If
        
        Call SetGridheader("库区|制品类型|图书类型|库存数量|码洋|入库实洋|出库实洋|总实洋|库存数量%|码洋%|入库实洋%|出库实洋%|总实洋%", 1, "10|10|15|15|15|15|15|15|10|8|10|10|15", strFoot)
    Case 2
      Call g_CommonSelect("  库区号  |  库区  ", "select chrStorageNo,chrStorageName from StorageSection where chrStorageNo like  '%" & txtFields(0).Text & "%'", "0,1", , , , -1, arrQuery)
      If TypeName(arrQuery) = "Variant()" Then
      
              txtFields(0).Text = arrQuery(0, 1)
              a = arrQuery(0, 0)
      End If
    Case 3
      Call g_CommonSelect("  编号  |  图书类型  ", "select chrBookTypeNo,chrBookType from BookType ", "0,1", , , , -1, arrQuery)
      If TypeName(arrQuery) = "Variant()" Then
              txtFields(1).Text = ""
              For i = 0 To UBound(arrQuery)
                 txtFields(1).Text = txtFields(1).Text & "" & arrQuery(i, 1) & ","
              Next
              txtFields(1).Text = Mid(txtFields(1).Text, 1, Len(txtFields(1).Text) - 1)
      End If
    Case 4
        Call g_CommonSelect("   社号   |    社名    ", "select chrCompanyNo,ChrCompanyName from PublishingCompanyData  where chrCompanyNo like '%" & txtFields(2).Text & "%' ", "0,1", , , , -1, arrQuery)