frmcountanalyze.frm

一套简易的MIS系统。带SQLServer数据库。供参考。

                    & " Sum(iChange) As iChange,Sum(iCount2) AS iCount2,Sum(iCash) AS iCash " _
                    & " Into tbcc_OprCountList From tbcc_OprCount Group By Oper_ID Order By Oper_ID"
    End If
    
    If chkJS.Value = 1 Then
                getSQLString = True
        iCountFlag = 3
        strCountTab = "tbcc_OprCountList"
        If InStr(cmbGoods.Text, "[") <> 0 Then
            strSQL = "Select Oper_ID,(Case Instate When 0 Then Count(Ticket_id) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Foot_weigh) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Count(Ticket_id) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Foot_weigh) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Count(Ticket_id) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Foot_weigh) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcFoot Where Convert(Varchar(10),Oper_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Oper_ID Like '" & tString(cmbOprInfo.Text, "[", "]", 1) & "%' " _
                    & " Group By Oper_ID,Instate Order By Oper_ID"
        Else
            strSQL = "Select Oper_ID,(Case Instate When 0 Then Count(Ticket_id) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Foot_weigh) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Count(Ticket_id) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Foot_weigh) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Count(Ticket_id) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Foot_weigh) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcFoot Where Convert(Varchar(10),Oper_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Oper_ID Like '%%' " _
                    & " Group By Oper_ID,Instate Order By Oper_ID"
        End If
        '建立临时表
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCount') Drop Table tbcc_OprCount"
        DBCN.Execute strSQL
        
        '整理数据
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCountList') Drop Table tbcc_OprCountList"
        DBCN.Execute "Select Oper_ID,Sum(iCount) AS iCount,Sum(iWeight) as iWeight,Sum(iCount1) As iCount1, " _
                    & " Sum(iChange) As iChange,Sum(iCount2) AS iCount2,Sum(iCash) AS iCash " _
                    & " Into tbcc_OprCountList From tbcc_OprCount Group By Oper_ID Order By Oper_ID"
    End If
    
    If chkLX.Value = 1 Then
        getSQLString = True
        iCountFlag = 4
        strCountTab = "tbcc_OprCountList"
        If InStr(cmbGoods.Text, "[") <> 0 Then
            strSQL = "Select Shop_way,(Case Instate When 0 Then Count(Ticket_id) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Ticket_Pay) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Count(Ticket_id) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Pay) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Count(Ticket_id) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Pay) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcTicket Where Convert(Varchar(10),Ticket_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Shop_way Like '" & tString(cmbOprInfo.Text, "[", "]", 1) & "%' " _
                    & " And  And Ticket_state In('3','4') Group By Shop_way,Instate Order By Shop_way"
        Else
            strSQL = "Select Shop_way,(Case Instate When 0 Then Count(Ticket_id) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Ticket_Pay) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Count(Ticket_id) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Pay) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Count(Ticket_id) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Pay) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcTicket Where Convert(Varchar(10),Ticket_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Shop_way Like '%%' And Ticket_state In('3','4') " _
                    & " Group By Shop_way,Instate Order By Shop_way"
        End If
        '建立临时表
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCount') Drop Table tbcc_OprCount"
        DBCN.Execute strSQL
        
        '整理数据
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCountList') Drop Table tbcc_OprCountList"
        DBCN.Execute "Select Shop_way,Sum(iCount) AS iCount,Sum(iWeight) as iWeight,Sum(iCount1) As iCount1, " _
                    & " Sum(iChange) As iChange,Sum(iCount2) AS iCount2,Sum(iCash) AS iCash " _
                    & " Into tbcc_OprCountList From tbcc_OprCount Group By Shop_way Order By Shop_way"
    End If
    
End Function

'显示信息
Private Function getHeadList(iList As Integer)
    
    With lstDataInfo
        .ListItems.Clear
        .FullRowSelect = True
        .GridLines = True
        .LabelEdit = lvwManual
        .View = lvwReport
                
        With .ColumnHeaders
            .Clear
            .Add , , "@", 0
            .Add , , "名称", 1700
            Select Case iList
                Case 0, 1, 4
                    .Add , , "开票量", 1600
                    .Add , , "开票金额", 1600
                    .Add , , "开票修改量", 1600
                    .Add , , "开票修金额", 1600
                    .Add , , "开票作废量", 1800
                    .Add , , "开票作废金额", 1800
                    .Add , , "合计有效量", 1600
                    .Add , , "合计有效金额", 1600
                Case 2
                    .Add , , "开票数", 1600
                    .Add , , "开票金额", 1600
                    .Add , , "开票修改量数", 1600
                    .Add , , "开票修金额", 1600
                    .Add , , "开票作废量数", 1800
                    .Add , , "开票作废金额", 1800
                    .Add , , "合计有效量数", 1600
                    .Add , , "合计有效金额", 1600
                Case 3
                    .Add , , "结算票数", 1600
                    .Add , , "结算票金额", 1600
                    .Add , , "结算票修改量数", 1600
                    .Add , , "结算票修金额", 1600
                    .Add , , "结算票作废量数", 1800
                    .Add , , "结算票作废金额", 1800
                    .Add , , "合计有效量数", 1600
                    .Add , , "合计有效金额", 1600
                Case 4
                    
            End Select
        End With
    End With

End Function

'显示数据
Private Function getDataList(strTab As String, iList As Integer)
    
    Dim rsTemp                     As New ADODB.Recordset
    Dim iIndex                     As Long
    Dim iSum1, iSum2, iSum3, iSum4, iSum5, iSum6, iSum7, iSum8, iSum9, iSum10 As Single
    Dim iCount                     As Long
    Set rsTemp = DBCN.Execute("Select * from " & strTab & "")
    If rsTemp.EOF = False Then
        iIndex = 1
        Select Case iList
            Case 0
                lstDataInfo.ListItems.Clear
                Do Until rsTemp.EOF
                    lstDataInfo.ListItems.Add iIndex, , iIndex
                    With lstDataInfo.ListItems(iIndex)
                        .SubItems(1) = IIf(IsNull(rsTemp.Fields("Oper_ID")), "", rsTemp.Fields("Oper_ID"))
                        .SubItems(2) = rsTemp.Fields("iCount")
                        iSum1 = iSum1 + Val(.SubItems(2))
                        .SubItems(3) = Format(rsTemp.Fields("iWeight"), "0.00")
                        iSum2 = iSum2 + Val(.SubItems(3))
                        .SubItems(4) = rsTemp.Fields("iCount1")
                        iSum3 = iSum3 + Val(.SubItems(4))
                        .SubItems(5) = Format(rsTemp.Fields("iChange"), "0.00")
                        iSum4 = iSum4 + Val(.SubItems(5))
                        .SubItems(6) = rsTemp.Fields("iCount2")
                        iSum5 = iSum5 + Val(.SubItems(6))
                        .SubItems(7) = Format(rsTemp.Fields("iCash"), "0.00")
                        iSum6 = iSum6 + Val(.SubItems(7))
                        .SubItems(8) = Format(Val(.SubItems(2)) + Val(.SubItems(4)), "0.00")
                        iSum7 = iSum7 + Val(.SubItems(8))
                        .SubItems(9) = Format(Val(.SubItems(3)) + Val(.SubItems(5)), "0.00")
                        iSum8 = iSum8 + Val(.SubItems(9))
                    End With
                    rsTemp.MoveNext
                    iIndex = iIndex + 1
                Loop
                iCount = lstDataInfo.ListItems.Count + 1
                lstDataInfo.ListItems.Add iCount, , iCount
                lstDataInfo.ListItems(iCount).SubItems(1) = "          合计："
                lstDataInfo.ListItems(iCount).SubItems(2) = Format(iSum1, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(3) = Format(iSum2, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(4) = Format(iSum3, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(5) = Format(iSum4, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(6) = Format(iSum5, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(7) = Format(iSum6, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(8) = Format(iSum7, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(9) = Format(iSum8, "0.00")
            Case 1
                lstDataInfo.ListItems.Clear
                Do Until rsTemp.EOF
                    lstDataInfo.ListItems.Add iIndex, , iIndex
                    With lstDataInfo.ListItems(iIndex)
                        .SubItems(1) = IIf(IsNull(rsTemp.Fields("Goods_ID")), "", rsTemp.Fields("Goods_ID"))
                        .SubItems(2) = rsTemp.Fields("iCount")
                        iSum1 = iSum1 + Val(.SubItems(2))
                        .SubItems(3) = Format(rsTemp.Fields("iWeight"), "0.00")
                        iSum2 = iSum2 + Val(.SubItems(3))
                        .SubItems(4) = rsTemp.Fields("iCount1")
                        iSum3 = iSum3 + Val(.SubItems(4))
                        .SubItems(5) = Format(rsTemp.Fields("iChange"), "0.00")
                        iSum4 = iSum4 + Val(.SubItems(5))
                        .SubItems(6) = rsTemp.Fields("iCount2")
                        iSum5 = iSum5 + Val(.SubItems(6))
                        .SubItems(7) = Format(rsTemp.Fields("iCash"), "0.00")
                        iSum6 = iSum6 + Val(.SubItems(7))
                        .SubItems(8) = Format(Val(.SubItems(2)) + Val(.SubItems(4)), "0.00")
                        iSum7 = iSum7 + Val(.SubItems(8))
                        .SubItems(9) = Format(Val(.SubItems(3)) + Val(.SubItems(5)), "0.00")
                        iSum8 = iSum8 + Val(.SubItems(9))
                    End With
                    rsTemp.MoveNext
                    iIndex = iIndex + 1
                Loop
                iCount = lstDataInfo.ListItems.Count + 1
                lstDataInfo.ListItems.Add iCount, , iCount
                lstDataInfo.ListItems(iCount).SubItems(1) = "          合计："
                lstDataInfo.ListItems(iCount).SubItems(2) = Format(iSum1, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(3) = Format(iSum2, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(4) = Format(iSum3, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(5) = Format(iSum4, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(6) = Format(iSum5, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(7) = Format(iSum6, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(8) = Format(iSum7, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(9) = Format(iSum8, "0.00")
            Case 2
                lstDataInfo.ListItems.Clear
                Do Until rsTemp.EOF
                    lstDataInfo.ListItems.Add iIndex, , iIndex
                    With lstDataInfo.ListItems(iIndex)
                        .SubItems(1) = IIf(IsNull(rsTemp.Fields("Oper_ID")), "", rsTemp.Fields("Oper_ID"))
                        .SubItems(2) = rsTemp.Fields("iCount")
                        iSum1 = iSum1 + Val(.SubItems(2))
                        .SubItems(3) = Format(rsTemp.Fields("iWeight"), "0.00")
                        iSum2 = iSum2 + Val(.SubItems(3))
                        .SubItems(4) = rsTemp.Fields("iCount1")
                        iSum3 = iSum3 + Val(.SubItems(4))
                        .SubItems(5) = Format(rsTemp.Fields("iChange"), "0.00")
                        iSum4 = iSum4 + Val(.SubItems(5))
                        .SubItems(6) = rsTemp.Fields("iCount2")
                        iSum5 = iSum5 + Val(.SubItems(6))
                        .SubItems(7) = Format(rsTemp.Fields("iCash"), "0.00")
                        iSum6 = iSum6 + Val(.SubItems(7))
                        .SubItems(8) = Format(Val(.SubItems(2)) + Val(.SubItems(4)), "0.00")
                        iSum7 = iSum7 + Val(.SubItems(8))
                        .SubItems(9) = Format(Val(.SubItems(3)) + Val(.SubItems(5)), "0.00")
                        iSum8 = iSum8 + Val(.SubItems(9))
                    End With
                    rsTemp.MoveNext
                    iIndex = iIndex + 1
                Loop
                iCount = lstDataInfo.ListItems.Count + 1
                lstDataInfo.ListItems.Add iCount, , iCount
                lstDataInfo.ListItems(iCount).SubItems(1) = "          合计："
                lstDataInfo.ListItems(iCount).SubItems(2) = Format(iSum1, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(3) = Format(iSum2, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(4) = Format(iSum3, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(5) = Format(iSum4, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(6) = Format(iSum5, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(7) = Format(iSum6, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(8) = Format(iSum7, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(9) = Format(iSum8, "0.00")
            Case 3
                lstDataInfo.ListItems.Clear
                Do Until rsTemp.EOF
                    lstDataInfo.ListItems.Add iIndex, , iIndex
                    With lstDataInfo.ListItems(iIndex)
                        .SubItems(1) = IIf(IsNull(rsTemp.Fields("Oper_ID")), "", rsTemp.Fields("Oper_ID"))
                        .SubItems(2) = rsTemp.Fields("iCount")
                        iSum1 = iSum1 + Val(.SubItems(2))
                        .SubItems(3) = Format(rsTemp.Fields("iWeight"), "0.00")
                        iSum2 = iSum2 + Val(.SubItems(3))
                        .SubItems(4) = rsTemp.Fields("iCount1")
                        iSum3 = iSum3 + Val(.SubItems(4))
                        .SubItems(5) = Format(rsTemp.Fields("iChange"), "0.00")
                        iSum4 = iSum4 + Val(.SubItems(5))
                        .SubItems(6) = rsTemp.Fields("iCount2")
                        iSum5 = iSum5 + Val(.SubItems(6))
                        .SubItems(7) = Format(rsTemp.Fields("iCash"), "0.00")
                        iSum6 = iSum6 + Val(.SubItems(7))
                        .SubItems(8) = Format(Val(.SubItems(2)) + Val(.SubItems(4)), "0.00")
                        iSum7 = iSum7 + Val(.SubItems(8))
                        .SubItems(9) = Format(Val(.SubItems(3)) + Val(.SubItems(5)), "0.00")
                        iSum8 = iSum8 + Val(.SubItems(9))
                    End With
                    rsTemp.MoveNext
                    iIndex = iIndex + 1
                Loop
                iCount = lstDataInfo.ListItems.Count + 1
                lstDataInfo.ListItems.Add iCount, , iCount
                lstDataInfo.ListItems(iCount).SubItems(1) = "          合计："
                lstDataInfo.ListItems(iCount).SubItems(2) = Format(iSum1, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(3) = Format(iSum2, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(4) = Format(iSum3, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(5) = Format(iSum4, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(6) = Format(iSum5, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(7) = Format(iSum6, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(8) = Format(iSum7, "0.00")
                lstDataInfo.ListItems(iCount).SubItems(9) = Format(iSum8, "0.00")
            Case 4
                lstDataInfo.ListItems.Clear
                Do Until rsTemp.EOF
                    lstDataInfo.ListItems.Add iIndex, , iIndex
                    With lstDataInfo.ListItems(iIndex)
                        .SubItems(1) = IIf(IsNull(rsTemp.Fields("Shop_way")), "", rsTemp.Fields("Shop_way"))
                        .SubItems(2) = rsTemp.Fields("iCount")
                        iSum1 = iSum1 + Val(.SubItems(2))
                        .SubItems(3) = Format(rsTemp.Fields("iWeight"), "0.00")
                        iSum2 = iSum2 + Val(.SubItems(3))
                        .SubItems(4) = rsTemp.Fields("iCount1")
                        iSum3 = iSum3 + Val(.SubItems(4))
                        .SubItems(5) = Format(rsTemp.Fields("iChange"), "0.00")
                        iSum4 = iSum4 + Val(.SubItems(5))
                        .SubItems(6) = rsTemp.Fields("iCount2")
                        iSum5 = iSum5 + Val(.SubItems(6))
                        .SubItems(7) = Format(rsTemp.Fields("iCash"), "0.00")
                        iSum6 = iSum6 + Val(.SubItems(7))
                        .SubItems(8) = Format(Val(.SubItems(2)) + Val(.SubItems(4)), "0.00")
                        iSum7 = iSum7 + Val(.SubItems(8))
                        .SubItems(9) = Format(Val(.SubItems(3)) + Val(.SubItems(5)), "0.00")
                        iSum8 = iSum8 + Val(.SubItems(9))