⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 frmstoragequery.frm

📁 通用书店管理系统
💻 FRM
📖 第 1 页 / 共 5 页
字号:
🔍
12345 
                     "StorageSection T2 ON T1.ChrStorageNo = T2.ChrStorageNo) Left JOIN PublishingCompanyData T4 ON (T3.chrbookconcern = T4.chrCompanyNo)   " & strQuery & " order by T4.ChrCompanyName 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 , DecZSY, T3.ChrProduceType,T3.ChrBookType,T4.ChrCompanyName " & _
                        "from (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)) " & _
                        "A left join (select distinct  t5.datcheckdate,t6.chrbookno,t6.chrbookname from InstorageInformation t5 left join (select distinct chrbookno,chrbookname,chrrkdh from InstorageInformation_list) t6 on t5.chrrkdh=t6.chrrkdh group by t5.datcheckdate,t6.chrbookno,t6.chrbookname " & _
                        "having count(t6.chrbookno)=1 order by t5.datcheckdate desc ) B ON A.ChrBookNo=B.ChrBookNo and A.ChrBookName=B.ChrBookName " & strQuery & " order by t5.datcheckdate desc"
        End If
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        Set tdbQuery(0).DataSource = rstmp
        

        sqlstring = "SELECT sum(T1.intAmount),sum(T3.DecPrice* T1.IntAmount),  sum(t1.DecZMY),sum(t1.DecCKSY),sum(t1.DecAPrice*T1.IntAmount) " & _
                     "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
        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("库区|书号|书名|作者|单价|数量|码洋|入库实洋|出库实洋|总实洋|制品类型|图书类型|出版社", 0, "10|15|25|15|10|15|15|15|15|15|10|13|15", strFoot)
        Call clearAll
      
      End If
             
      Frame1(0).Visible = False

   
  End Select
End Sub

Public Sub cmdPrint_Click1()
'  Dim q  As New Query
'  Dim i As Integer
'  Dim j As Integer
'  Dim sqlstring As String
'
'  Dim rstmp As New ADODB.Recordset
'
'  If Not checkpermission("书店管理系统", strUserName, , "库存管理.库存管理查询.库存查询.打印") Then
'       Exit Sub
'  End If
'
'  Select Case SSTab1.Caption
'    Case "明细"
'
'        '明细
'       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 " & _
'                     "StorageSection T2 ON T1.ChrStorageNo = T2.ChrStorageNo) Left JOIN PublishingCompanyData T4 ON (T3.chrbookconcern = T4.chrCompanyNo)   " & strQuery & " order by T4.ChrCompanyName 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 , DecZSY, T3.ChrProduceType,T3.ChrBookType,T4.ChrCompanyName " & _
'                        "from (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)) " & _
'                        "A left join (select distinct  t5.datcheckdate,t6.chrbookno,t6.chrbookname from InstorageInformation t5 left join (select distinct chrbookno,chrbookname,chrrkdh from InstorageInformation_list) t6 on t5.chrrkdh=t6.chrrkdh group by t5.datcheckdate,t6.chrbookno,t6.chrbookname " & _
'                        "having count(t6.chrbookno)=1 order by t5.datcheckdate desc ) B ON A.ChrBookNo=B.ChrBookNo and A.ChrBookName=B.ChrBookName  " & strQuery & "  order by t5.datcheckdate desc"
'        End If
'       Set rstmp = New ADODB.Recordset
'       rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
'
'
'       If Not rstmp.EOF Then
'         ReDim arrPrint(12, rstmp.Recordcount - 1) As Variant
'         For j = 0 To 12
'           For i = 0 To rstmp.Recordcount - 1
'               arrPrint(j, i) = IIf(IsNull(rstmp.Fields(j).Value), "", rstmp.Fields(j).Value)
'               rstmp.MoveNext
'           Next i
'           rstmp.MoveFirst
'         Next j
'
'
'         With q
'              '列头:可用空格来调节列宽
'              .Header(",") = "库区,书号,书名,作者,单价,数量,码洋,入库实洋,出库实洋,总实洋,制品类型,图书类型,出版社"
'              .InputArray = arrPrint
'              '自动换行处理
'              .ResizeRowHeight = "9,10"
'              '自动调整列宽处理
'              .ResizeColWidth = "1,2,3,4,5,6,7,8,9,10,11,12,13"
'              .Subtotal = "2,,6;2,,7;2,,8;2,,9;2,,10"  '参数分别为：函数，分组列，汇总列；
'              .Show cqJustResult
'         End With
'
'         Set q = Nothing
'
'       Else
'         MsgBox "没有数据可以打印，操作不能执行！", , "警告"
'         Exit Sub
'       End If
'    Case "汇总"
'       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 Not rstmp.EOF Then
'         ReDim arrPrint(11, rstmp.Recordcount - 1) As Variant
'         For j = 0 To 11
'           For i = 0 To rstmp.Recordcount - 1
'               arrPrint(j, i) = IIf(IsNull(rstmp.Fields(j).Value), "", rstmp.Fields(j).Value)
'               rstmp.MoveNext
'           Next i
'           rstmp.MoveFirst
'         Next j
'
'
'         With q
'              '列头:可用空格来调节列宽
'              .Header(",") = "库区,制品类型,图书类型,库存数量,码洋,入库实洋,出库实洋,总实洋,库存数量%,码洋%,入库实洋%,出库实洋%,总实洋%"
'              .InputArray = arrPrint
'              '自动换行处理
'              .ResizeRowHeight = "3"
'              '自动调整列宽处理
'              .ResizeColWidth = "1,2,3,4,5,6,7,8,9,10,11,12"
'              .Subtotal = "2,,5;2,,6;2,,7;2,,8"  '参数分别为：函数，分组列，汇总列；
'              .Show cqJustResult
'         End With
'
'         Set q = Nothing
'
'       Else
'         MsgBox "没有数据可以打印，操作不能执行！", , "警告"
'         Exit Sub
'       End If
'
'  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 X.UpperBound(1) = -1 Then
'        MsgBox "没有可以打印的内容！"
'        Exit Sub
'    End If

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 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 " & _
                     "StorageSection T2 ON T1.ChrStorageNo = T2.ChrStorageNo) Left JOIN PublishingCompanyData T4 ON (T3.chrbookconcern = T4.chrCompanyNo)   " & strQuery & " order by T4.ChrCompanyName 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 , DecZSY, T3.ChrProduceType,T3.ChrBookType,T4.ChrCompanyName " & _
                        "from (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)) " & _
                        "A left join (select distinct  t5.datcheckdate,t6.chrbookno,t6.chrbookname from InstorageInformation t5 left join (select distinct chrbookno,chrbookname,chrrkdh from InstorageInformation_list) t6 on t5.chrrkdh=t6.chrrkdh group by t5.datcheckdate,t6.chrbookno,t6.chrbookname " & _
                        "having count(t6.chrbookno)=1 order by t5.datcheckdate desc ) B ON A.ChrBookNo=B.ChrBookNo and A.ChrBookName=B.ChrBookName  " & strQuery & "  order by t5.datcheckdate desc"
        End If
       
       Set rstmp = New ADODB.Recordset
       rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
       If rstmp.Recordcount > 0 Then
        arr = rstmp.GetRows
       End If
       
       '        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|100;#,##0.00|100;#,##0|100;#,##0.00|100;#,##0.00|100;#,##0.00|100;#,##0.00|100|100|100" & _
                          " Header=库区|书号|书名|作者|单价|数量|码洋|入库实洋|出库实洋|总实洋|制品类型|图书类型|出版社" & _
                          " Subtotal=2\页总计\1\6\6\1\#,##0\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;" & _
12345

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -