reportview.frm

一个用VB6.0开发的简单餐会管理系统。在WIN2K

            strSel = strSel & "Or (Month(基本资料.出生年月) = " & i & ") "
        End If
    Next
Else
    strSel = strSel & "(Month(基本资料.出生年月) = " & VMSDate & " "
    If VDEDate > VDSDate Then
        strSel = strSel & "And Day(基本资料.出生年月) >= " & VDSDate & " And Day(基本资料.出生年月) <= " & VDEDate & ")"
    Else
        strSel = strSel & "And Day(基本资料.出生年月) = " & VDSDate & ")"
    End If
End If

If SelRepForm.DBCombo1.text <> "" Then
    strSel = strSel & " And '" & SelRepForm.DBCombo1.text & "' = 基本资料.社区"
End If

If SelRepForm.Check2.Value = Checked Then
    strSel = strSel & " and (year(now)-Year(基本资料.出生年月)>= " & CInt(SelRepForm.Text1.text) & ") and (year(now)-Year(基本资料.出生年月)<= " & CInt(SelRepForm.Text2.text) & ")"
End If

If Option1(0).Value Then
strSel = strSel & " order by dateserial('2004',month(基本资料.出生年月),day(基本资料.出生年月)) asc;"
ElseIf Option1(1).Value Then
strSel = strSel & " order by 基本资料.社区 asc;"
End If
Data1.RecordSource = strSel
Data1.Refresh

'MSFlexGrid1.Row = 0
''''''''''
''显示记录
Dim strTemp As String
If Data1.Recordset.RecordCount = 0 Then
    MsgBox "没有记录"
    Exit Function
End If
Data1.Recordset.MoveFirst
i = 0
Do While Not Data1.Recordset.EOF
    strTemp = ""
    strTemp = strTemp & CStr(i)
    strTemp = strTemp & vbTab & Month(Data1.Recordset!出生年月) & "-" & Day(Data1.Recordset!出生年月)
    strTemp = strTemp & vbTab & Data1.Recordset!姓名
    strTemp = strTemp & vbTab & (Year(Date) - Year(Data1.Recordset!出生年月))
    If Data1.Recordset.Fields("性别") = JP_SET_NUM_MALE Then
        strTemp = strTemp & vbTab & JP_SET_CH_MALE
    Else
        strTemp = strTemp & vbTab & JP_SET_CH_FEMALE
    End If
    strTemp = strTemp & vbTab & Data1.Recordset!电话
    strTemp = strTemp & vbTab & Data1.Recordset!参加次数
'    strTemp = strTemp & vbTab & Data1.Recordset!阴历
    strTemp = strTemp & vbTab & Data1.Recordset!社区
    strTemp = strTemp & vbTab & Data1.Recordset!地址
    strTemp = strTemp & vbTab & Data1.Recordset!邮编
    strTemp = strTemp & vbTab & Data1.Recordset!父
    strTemp = strTemp & vbTab & Data1.Recordset!母
    
    MSFlexGrid1.AddItem strTemp
    Data1.Recordset.MoveNext
    i = i + 1
Loop
End Function
'基本资料表结构
Private Function ModifyBASEReportView()
With MSFlexGrid1
'编号、生日、姓名、年龄、性别、电话、参加次数、社区、地址、邮编、父、母
    .Rows = 1
    .Cols = JP_SET_MBVIEW_COLS
    
    ''''''''''''''''''''''''''''''
    ''列信息
    .ColWidth(0) = 500
    .TextMatrix(0, 0) = "编号"
    .ColWidth(1) = 1000
    .TextMatrix(0, 1) = "生日"
    .ColWidth(2) = 1000
    .TextMatrix(0, 2) = "姓名"
    .ColWidth(3) = 500
    .TextMatrix(0, 3) = "年龄"
    .ColWidth(4) = 500
    .TextMatrix(0, 4) = "性别"
    .ColWidth(5) = 1200
    .TextMatrix(0, 5) = "电话"
    .ColWidth(6) = 800
    .TextMatrix(0, 6) = "参加次数"
    .ColWidth(7) = 1000
'    .TextMatrix(0, 7) = "阴历"
    .TextMatrix(0, 7) = "社区"
    .ColWidth(8) = 2000
    .TextMatrix(0, 8) = "家庭地址"
    .ColWidth(9) = 800
    .TextMatrix(0, 9) = "邮编"
    .ColWidth(10) = 1000
    .TextMatrix(0, 10) = "父亲"
    .ColWidth(11) = 1000
    .TextMatrix(0, 11) = "母亲"
'    .ColWidth(12) = 2000
End With
End Function




'餐会表
Private Function SetFBIRReportView()
'编号、餐会时间、姓名、生日、年龄、性别、参加生日餐会次数、大人数量、小孩数量、满意度、花费、游戏、礼物、特殊服务、接待人员、定蛋糕、定餐内容、预定
Dim strSel As String
Dim SDate, EDate As Date    '开始、结束时间
Dim i As Integer
Dim strTemp As String
Dim sngMoneyTotal As Single
Dim nRecTotal, nMenTotal, nChildTotal, nMYTotal As Integer


'得到时间
SDate = SelRepForm.DTPicker1.Value
EDate = SelRepForm.DTPicker2.Value

strSel = "SELECT 餐会信息.餐会时间,餐会信息.大人数量,餐会信息.小孩数量,餐会信息.满意度,餐会信息.花费,餐会信息.游戏,餐会信息.礼物,"
strSel = strSel & "餐会信息.特殊服务,餐会信息.接待人员,餐会信息.定蛋糕,餐会信息.定餐内容,餐会信息.预定,"
strSel = strSel & "基本资料.姓名,基本资料.出生年月,基本资料.性别,基本资料.参加次数 "
strSel = strSel & "From 餐会信息 INNER JOIN 基本资料 ON (餐会信息.ID = 基本资料.ID) WHERE "

strSel = strSel & "((餐会信息.餐会时间 >= #" & SDate & "#) AND " & "(餐会信息.餐会时间 <= #" & EDate & "#)) order by 餐会信息.餐会时间 asc;"

Data1.RecordSource = strSel
Data1.ReadOnly = True
Data1.Refresh

If Data1.Recordset.EOF Then
    Label1.Caption = "没有记录"
    Exit Function
End If

''''''''''
''显示记录
i = 0
sngMoneyTotal = 0
nMenTotal = 0
nChildTotal = 0
nMYTotal = 0
Data1.Recordset.MoveFirst
Do While Not Data1.Recordset.EOF
    strTemp = ""
    strTemp = strTemp & CStr(i)
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!餐会时间
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!姓名
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!出生年月
    strTemp = strTemp & vbTab & (Year(Date) - Year(Data1.Recordset!出生年月))
    If Data1.Recordset.Fields!性别 = JP_SET_NUM_MALE Then
        strTemp = strTemp & vbTab & JP_SET_CH_MALE
    Else
        strTemp = strTemp & vbTab & JP_SET_CH_FEMALE
    End If
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!参加次数
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!大人数量
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!小孩数量
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!满意度 & "%"
    strTemp = strTemp & vbTab & "￥" & Data1.Recordset.Fields!花费
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!游戏
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!礼物
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!特殊服务
    strTemp = strTemp & vbTab & Data1.Recordset.Fields!接待人员
    If Data1.Recordset.Fields("定蛋糕") Then
        strTemp = strTemp & vbTab & JP_SET_CH_YES
    Else
        strTemp = strTemp & vbTab & JP_SET_CH_NO
    End If
    strTemp = strTemp & vbTab & Data1.Recordset.Fields("定餐内容")
    If Data1.Recordset.Fields("预定") Then
        strTemp = strTemp & vbTab & JP_SET_CH_YES
    Else
        strTemp = strTemp & vbTab & JP_SET_CH_NO
    End If
    
    sngMoneyTotal = sngMoneyTotal + Data1.Recordset.Fields!花费
    nMenTotal = nMenTotal + Data1.Recordset.Fields!大人数量
    nChildTotal = nChildTotal + Data1.Recordset.Fields!小孩数量
    nMYTotal = nMYTotal + Data1.Recordset.Fields!满意度
    
    MSFlexGrid1.AddItem strTemp
    Data1.Recordset.MoveNext
    i = i + 1
Loop

'总数
nRecTotal = Data1.Recordset.RecordCount
'Dim str As String


Label1.Caption = CStr(SDate) & "到" & CStr(EDate) & "餐会共" & CStr(nRecTotal) & "场，" & "消费总额为" & CStr(sngMoneyTotal) & "元，"
Label1.Caption = Label1.Caption + "平均每场消费" & CStr(Format(sngMoneyTotal / nRecTotal, "#.##")) & "元，" & "平均满意度为" & CStr(Format((nMYTotal / nRecTotal) / 100, "#.##%")) & "。"
Label1.Caption = Label1.Caption + Chr(10) & "参加餐会总计人数" & CStr(nMenTotal + nChildTotal) & "人，" & "其中大人" & CStr(nMenTotal) & "人，小孩" & CStr(nChildTotal) & "人，"
Label1.Caption = Label1.Caption & "平均 AC= " & CStr(Format(sngMoneyTotal / (nMenTotal + nChildTotal), "#.##。"))
End Function
'餐会表结构
Private Function ModifyFBIRReportView()
With MSFlexGrid1
'编号、餐会时间、姓名、生日、年龄、性别、参加生日餐会次数、大人数量、小孩数量、满意度、花费、游戏、礼物、特殊服务、接待人员、定蛋糕、定餐内容、预定
    .Rows = 1
    .Cols = JP_SET_MFVIEW_COLS
    
    ''''''''''''''''''''''''''''''
    ''列信息
    .ColWidth(0) = 450
    .TextMatrix(0, 0) = "编号"
    .ColWidth(1) = 1000
    .TextMatrix(0, 1) = "餐会时间"
    .ColWidth(2) = 1000
    .TextMatrix(0, 2) = "姓名"
    .ColWidth(3) = 1000
    .TextMatrix(0, 3) = "生日"
    .ColWidth(4) = 450
    .TextMatrix(0, 4) = "年龄"
    .ColWidth(5) = 450
    .TextMatrix(0, 5) = "性别"
    .ColWidth(6) = 800
    .TextMatrix(0, 6) = "参加次数"
    .ColWidth(7) = 800
    .TextMatrix(0, 7) = "大人数量"
    .ColWidth(8) = 800
    .TextMatrix(0, 8) = "小孩数量"
    .ColWidth(9) = 700
    .TextMatrix(0, 9) = "满意度"
    .ColWidth(10) = 700
    .TextMatrix(0, 10) = "花费"
    .ColWidth(11) = 1000
    .TextMatrix(0, 11) = "游戏"
    .ColWidth(12) = 1000
    .TextMatrix(0, 12) = "礼物"
    .ColWidth(13) = 1000
    .TextMatrix(0, 13) = "特殊服务"
    .ColWidth(14) = 800
    .TextMatrix(0, 14) = "接待人员"
    .ColWidth(15) = 700
    .TextMatrix(0, 15) = "定蛋糕"
    .ColWidth(16) = 1000
    .TextMatrix(0, 16) = "定餐内容"
    .ColWidth(17) = 1000
    .TextMatrix(0, 17) = "预定"
'    .ColWidth(12) = 2000
End With
End Function

'设置SQL语句
'Private Sub SetDateSQLString(strSel As String, SDate As Date, sDays As Integer)
'strDate = Month(SDate) & "-" & Day(SDate)
'strSel = strSel & "((基本资料.出生年月) like '*" & strDate & "')"
'For i = 1 To sDays
'SDate = SDate + 1
'strDate = Month(SDate) & "-" & Day(SDate)
'strSel = strSel & " or ((基本资料.出生年月) like '*" & strDate & "')"
'Next
'SetDateSQLString = strSel
'End Sub


Private Sub Option1_Click(Index As Integer)
    ModifyBASEReportView
    SetBASEReportView
End Sub

Private Function ReportViewOptionFlag(flag As Boolean)
Option1(0).Visible = flag
Option1(1).Visible = flag

End Function