frmsum.frm

本系统有完善的功能

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form frmsum 
   Caption         =   "Form1"
   ClientHeight    =   4725
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6300
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   ScaleHeight     =   4725
   ScaleWidth      =   6300
   StartUpPosition =   2  'CenterScreen
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   2895
      Left            =   600
      TabIndex        =   0
      Top             =   720
      Width           =   4575
      _ExtentX        =   8070
      _ExtentY        =   5106
      _Version        =   393216
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      Caption         =   "按汇总"
      Height          =   195
      Left            =   720
      TabIndex        =   1
      Top             =   240
      Width           =   540
   End
End
Attribute VB_Name = "frmsum"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim rs_sum As New ADODB.Recordset
Dim addup As Double

Private Sub Form_Load()
Dim sql As String
Select Case summary_menu
       Case "check"       '按账目名称汇总
            Label1.Caption = "按账目名称汇总"
            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 "date"             '按日期汇总
            Label1.Caption = "按日期汇总"
            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 "custom"         '按客户汇总
            Label1.Caption = "按客户汇总"
            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"       '按账目+日期汇总
            Label1.Caption = "按账目+日期汇总"
            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"             '按日期+客户汇总
            Label1.Caption = "按客户+日期汇总"
            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
End Select
End Sub