frmsum.frm

Visual Basic 开发的帐务管理系统

       Case "_custom"         '按客户汇总
            sql = "select 客户名称,sum(金额) as 总金额 from 账务资料 group by 客户名称 order by sum(金额)"
            rs_sum.CursorLocation = adUseClient
            rs_sum.Open sql, conn, adOpenKeyset, adLockPessimistic
            addup = 0
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Cols = 3
            MSFlexGrid1.Row = 0
            MSFlexGrid1.Col = 0
            MSFlexGrid1.Text = "客户"
            MSFlexGrid1.Col = 1
            MSFlexGrid1.Text = "总金额"
            MSFlexGrid1.Col = 2
            MSFlexGrid1.Text = "进出账"
            If rs_sum.EOF = False Then
               rs_sum.MoveFirst
               Do While Not rs_sum.EOF
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = rs_sum.Fields(0)
                        MSFlexGrid1.Col = 1
                        If CDbl(rs_sum.Fields(1)) < 0 Then
                           MSFlexGrid1.Text = Replace(rs_sum.Fields(1), "-", "")
                           MSFlexGrid1.Col = 2
                           MSFlexGrid1.Text = "出账"
                        Else
                           MSFlexGrid1.Text = rs_sum.Fields(1)
                           MSFlexGrid1.Col = 2
                           MSFlexGrid1.Text = "进账"
                        End If
                        addup = addup + CDbl(rs_sum.Fields(1))
                        rs_sum.MoveNext
               Loop
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = "(总计)"
                        MSFlexGrid1.Col = 1
                        MSFlexGrid1.Text = addup
            End If
            rs_sum.Close
       Case "_check_date"       '按账目+日期汇总
            sql = "select 账目名称,日期,sum(金额) as 总金额 from 账务资料 " & _
            "group by 账目名称,日期 order by sum(金额)"
            rs_sum.CursorLocation = adUseClient
            rs_sum.Open sql, conn, adOpenKeyset, adLockPessimistic
            addup = 0
            MSFlexGrid1.MergeCells = flexMergeRestrictRows
            MSFlexGrid1.MergeCol(0) = True
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Cols = 4
            MSFlexGrid1.Row = 0
            MSFlexGrid1.Col = 0
            MSFlexGrid1.Text = "账目名称"
            MSFlexGrid1.Col = 1
            MSFlexGrid1.Text = "日期"
            MSFlexGrid1.Col = 2
            MSFlexGrid1.Text = "总金额"
            MSFlexGrid1.Col = 3
            MSFlexGrid1.Text = "进出账"
            If rs_sum.EOF = False Then
               rs_sum.MoveFirst
               Do While Not rs_sum.EOF
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = rs_sum.Fields(0)
                        MSFlexGrid1.Col = 1
                        MSFlexGrid1.Text = rs_sum.Fields(1)
                        MSFlexGrid1.Col = 2
                        If CDbl(rs_sum.Fields(2)) < 0 Then
                           MSFlexGrid1.Text = Replace(rs_sum.Fields(2), "-", "")
                           MSFlexGrid1.Col = 3
                           MSFlexGrid1.Text = "出账"
                        Else
                           MSFlexGrid1.Text = rs_sum.Fields(2)
                           MSFlexGrid1.Col = 3
                           MSFlexGrid1.Text = "进账"
                        End If
                        addup = addup + CDbl(rs_sum.Fields(2))
                        rs_sum.MoveNext
               Loop
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = "(总计)"
                        MSFlexGrid1.Col = 2
                        MSFlexGrid1.Text = addup
            End If
            rs_sum.Close
       Case "_date_custom"             '按日期+客户汇总
            sql = "select 客户名称,日期,sum(金额) as 总金额 from 账务资料 " & _
            "group by 客户名称,日期 order by sum(金额)"
            rs_sum.CursorLocation = adUseClient
            rs_sum.Open sql, conn, adOpenKeyset, adLockPessimistic
            addup = 0
            MSFlexGrid1.MergeCells = flexMergeRestrictRows
            MSFlexGrid1.MergeCol(0) = True
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Cols = 4
            MSFlexGrid1.Row = 0
            MSFlexGrid1.Col = 0
            MSFlexGrid1.Text = "客户名称"
            MSFlexGrid1.Col = 1
            MSFlexGrid1.Text = "日期"
            MSFlexGrid1.Col = 2
            MSFlexGrid1.Text = "总金额"
            MSFlexGrid1.Col = 3
            MSFlexGrid1.Text = "进出账"
            If rs_sum.EOF = False Then
               rs_sum.MoveFirst
               Do While Not rs_sum.EOF
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = rs_sum.Fields(0)
                        MSFlexGrid1.Col = 1
                        MSFlexGrid1.Text = rs_sum.Fields(1)
                        MSFlexGrid1.Col = 2
                        If CDbl(rs_sum.Fields(2)) < 0 Then
                           MSFlexGrid1.Text = Replace(rs_sum.Fields(2), "-", "")
                           MSFlexGrid1.Col = 3
                           MSFlexGrid1.Text = "出账"
                        Else
                           MSFlexGrid1.Text = rs_sum.Fields(2)
                           MSFlexGrid1.Col = 3
                           MSFlexGrid1.Text = "进账"
                        End If
                        addup = addup + CDbl(rs_sum.Fields(2))
                        rs_sum.MoveNext
               Loop
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = "(总计)"
                        MSFlexGrid1.Col = 2
                        MSFlexGrid1.Text = addup
            End If
            rs_sum.Close
        Case "_check_custom"       '按账目+客户汇总
            sql = "select 账目名称,客户名称,sum(金额) as 总金额 from 账务资料 " & _
            "group by 账目名称,客户名称 order by sum(金额)"
            rs_sum.CursorLocation = adUseClient
            rs_sum.Open sql, conn, adOpenKeyset, adLockPessimistic
            addup = 0
            MSFlexGrid1.MergeCells = flexMergeRestrictRows
            MSFlexGrid1.MergeCol(0) = True
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Cols = 4
            MSFlexGrid1.Row = 0
            MSFlexGrid1.Col = 0
            MSFlexGrid1.Text = "账目名称"
            MSFlexGrid1.Col = 1
            MSFlexGrid1.Text = "客户名称"
            MSFlexGrid1.Col = 2
            MSFlexGrid1.Text = "总金额"
            MSFlexGrid1.Col = 3
            MSFlexGrid1.Text = "进出账"
            If rs_sum.EOF = False Then
               rs_sum.MoveFirst
               Do While Not rs_sum.EOF
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = rs_sum.Fields(0)
                        MSFlexGrid1.Col = 1
                        MSFlexGrid1.Text = rs_sum.Fields(1)
                        MSFlexGrid1.Col = 2
                        If CDbl(rs_sum.Fields(2)) < 0 Then
                           MSFlexGrid1.Text = Replace(rs_sum.Fields(2), "-", "")
                           MSFlexGrid1.Col = 3
                           MSFlexGrid1.Text = "出账"
                        Else
                           MSFlexGrid1.Text = rs_sum.Fields(2)
                           MSFlexGrid1.Col = 3
                           MSFlexGrid1.Text = "进账"
                        End If
                        addup = addup + CDbl(rs_sum.Fields(2))
                        rs_sum.MoveNext
               Loop
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = "(总计)"
                        MSFlexGrid1.Col = 2
                        MSFlexGrid1.Text = addup
            End If
            rs_sum.Close
     Case "_check_date_custom"       '按账目+日期＋客户汇总
            sql = "select 账目名称,日期,客户名称,sum(金额) as 总金额 from 账务资料 " & _
            "group by 账目名称,日期,客户名称 order by sum(金额)"
            rs_sum.CursorLocation = adUseClient
            rs_sum.Open sql, conn, adOpenKeyset, adLockPessimistic
            addup = 0
            MSFlexGrid1.MergeCells = flexMergeRestrictRows
            MSFlexGrid1.MergeCol(0) = True
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Rows = rs_sum.RecordCount + 2
            MSFlexGrid1.Cols = 5
            MSFlexGrid1.Row = 0
            MSFlexGrid1.Col = 0
            MSFlexGrid1.Text = "账目名称"
            MSFlexGrid1.Col = 1
            MSFlexGrid1.Text = "客户名称"
            MSFlexGrid1.Col = 2
            MSFlexGrid1.Text = "日期"
            MSFlexGrid1.Col = 3
            MSFlexGrid1.Text = "总金额"
            MSFlexGrid1.Col = 4
            MSFlexGrid1.Text = "进出账"
            If rs_sum.EOF = False Then
               rs_sum.MoveFirst
               Do While Not rs_sum.EOF
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = rs_sum.Fields(0)
                        MSFlexGrid1.Col = 1
                        MSFlexGrid1.Text = rs_sum.Fields(1)
                        MSFlexGrid1.Col = 2
                        MSFlexGrid1.Text = rs_sum.Fields(2)
                        MSFlexGrid1.Col = 3
                        If CDbl(rs_sum.Fields(3)) < 0 Then
                           MSFlexGrid1.Text = Replace(rs_sum.Fields(3), "-", "")
                           MSFlexGrid1.Col = 4
                           MSFlexGrid1.Text = "出账"
                        Else
                           MSFlexGrid1.Text = rs_sum.Fields(3)
                           MSFlexGrid1.Col = 4
                           MSFlexGrid1.Text = "进账"
                        End If
                        addup = addup + CDbl(rs_sum.Fields(2))
                        rs_sum.MoveNext
               Loop
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                        MSFlexGrid1.Col = 0
                        MSFlexGrid1.Text = "(总计)"
                        MSFlexGrid1.Col = 2
                        MSFlexGrid1.Text = addup
            End If
            rs_sum.Close
End Select
End Sub