huizong.frm

在该实例中

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form huizong 
   BackColor       =   &H00FFFFC0&
   Caption         =   "货物汇总"
   ClientHeight    =   5085
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6855
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   ScaleHeight     =   5085
   ScaleWidth      =   6855
   StartUpPosition =   2  '屏幕中心
   Begin VB.CommandButton Command1 
      Caption         =   "退出"
      Height          =   495
      Left            =   1920
      TabIndex        =   2
      Top             =   4560
      Width           =   735
   End
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   3735
      Left            =   360
      TabIndex        =   0
      Top             =   720
      Width           =   4935
      _ExtentX        =   8705
      _ExtentY        =   6588
      _Version        =   393216
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      BackColor       =   &H00FFFFC0&
      Caption         =   "货物数目"
      Height          =   180
      Left            =   120
      TabIndex        =   1
      Top             =   360
      Width           =   720
   End
End
Attribute VB_Name = "huizong"
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 Command1_Click()
Unload Me
End Sub

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