又一个综合示例

本节是一个综合性比较强的例子，包括三个页面。

list.htm用于分页显示数据，有四个页面切换按钮：

连续两次点击同一单元格，会出现一个菜单，且在筛选和非筛选的状态下，显示的菜单是不同的：

filter.htm用于输入筛选条件：

tongji.htm用于统计数据（包括统计结果的显示），如果已经进行了筛选，则仅统计筛选后的数据：

为避免HttpRequest事件代码过程，本节全部采用自定义函数实现。

1、HttpRequest事件代码：

Select Case e.Path
    Case "filter.htm"
        Functions.Execute("Filter",e)
    Case "list.htm"
        Functions.Execute("List",e) 
    Case "tongji.htm"
        Functions.Execute("Statistics",e)
End Select

2、自定义函数Filter用于生成筛选页面filter.htm：

Dim e As RequestEventArgs = args(0)
Dim wb As New WeUI
wb.AddForm("","form1","list.htm")
With wb.AddInputGroup("form1","ipg1","数据筛选")
    .AddSelect("product","产品","|PD01|PD02|PD03|PD04|PD05")
    .AddSelect("customer","客户","|CS01|CS02|CS03|CS04|CS05")
    .AddInput("startdate","开始日期","date")
    .AddInput("enddate","结束时间","date")
End With
With wb.AddButtonGroup("form1","btg1",True)
    .Add("btn1", "确定", "submit")
End With
e.WriteString(wb.Build)

3、自定义函数GetFilter用于根据生成filter.htm提交的内容合成条件表达式：

Dim e As RequestEventArgs = args(0)
Dim wb As WeUI = Args(1)
Dim flt As String
If e.PostValues.ContainsKey("product") Then
    flt = "产品 = '" & e.PostValues("product") & "'" '合成条件
    wb.AppendCookie("product", e.PostValues("product"),30) '将值写入cookie中
Else
    wb.DeleteCookie("product") '删除cookie
End If
If e.PostValues.ContainsKey("customer") Then
    If flt > "" Then
        flt = flt & " and "
    End If
    flt = flt & "客户 = '" & e.PostValues("customer") & "'" '合成条件
    wb.AppendCookie("customer", e.PostValues("customer")) '将值写入cookie中
Else
    wb.DeleteCookie("customer") '删除cookie
End If
If e.PostValues.ContainsKey("startdate") Then
    If flt > "" Then
        flt = flt & " and "
    End If
    flt = flt & "日期 >= '" & e.PostValues("startdate") & "'"
    wb.AppendCookie("startdate", e.PostValues("startdate"))
Else
    wb.DeleteCookie("startdate")
End If
If e.PostValues.ContainsKey("enddate") Then
    If flt > "" Then
        flt = flt & " and "
    End If
    flt = flt & "日期 <= '" & e.PostValues("enddate") & "'"
    wb.AppendCookie("enddate", e.PostValues("enddate"))
Else
    wb.DeleteCookie("enddate")
End If
Return flt

提示：GetFilter函数需要输入的条件保存到Cookie中，而Coolie的保存和删除是通过WeUI完成的，所以在调用GetFilter的时候，需要将WeUI作为第二个参数传递过去。

4、自定义函数GetCookieFilter用于根据Cookie合成条件表达式：

Dim flt As String
Dim e As RequestEventArgs = args(0)
If e.Cookies.ContainsKey("product")  Then
    flt = "产品 = '" & e.Cookies("product") & "'"
End If
If e.Cookies.ContainsKey("customer")  Then
    If flt > "" Then
        flt = flt & " and "
    End If
    flt = flt & "客户 = '" & e.Cookies("customer") & "'"
End If
If e.Cookies.ContainsKey("startdate")  Then
    If flt > "" Then
        flt = flt & " and "
    End If
    flt = flt & "日期 >= '" & e.Cookies("startdate") & "'"
End If
If e.Cookies.ContainsKey("enddate")  Then
    If flt > "" Then
        flt = flt & " and "
    End If
    flt = flt & "日期 <= '" & e.Cookies("enddate") & "'"
End If
Return flt

5、List函数用于生成list.htm：

Dim e As RequestEventArgs = args(0)
Dim wb As New WeUI
Dim flt As String
If e.GetValues.ContainsKey("unfilter") Then '如果是取消筛选
    wb.ClearCookie() '清除Cookie
ElseIf e.PostValues.Count > 0
    flt = Functions.Execute("GetFilter",e, wb) '根据输入内容合成条件，注意WeUI也需要传递过去
Else
    flt = Functions.Execute("GetCookieFilter",e) '根据Cookie合成条件
End If
'获取要显示的页码
Dim page As Integer = 0 '默认page为0,显示第一页
Dim pageRows As Integer = 10 '每页10行
If e.GetValues.ContainsKey("page") Then  '如果地址中有page参数
    Integer.TryParse(e.GetValues("page"), page) '提取page参数
End If
Dim StartRow As Integer = page * pageRows + 1 '此页第一行
Dim EndRow As Integer = (page + 1) * pageRows '此页最后一行
'提取此页数据
Dim cmd As New SQLCommand
cmd.ConnectionName = "orders" '记得设置数据源名称
cmd.CommandText = "Select Count(*) From {订单}"
If flt > "" Then
    cmd.CommandText = cmd.CommandText & " where " & flt
End If
Dim Count As Integer = cmd.ExecuteScalar() '获取总的行数
Dim Pages As Integer = Math.Ceiling(Count/PageRows) '计算出总页数
cmd.CommandText = "Select * From (Select Row_Number() Over(Order by 日期) As [NO.], 产品,客户,数量,单价,日期 From 订单 "
If flt > "" Then
    cmd.CommandText = cmd.CommandText & " where " & flt
End If
cmd.CommandText = cmd.CommandText & ") As a "
cmd.CommandText = cmd.CommandText & "  Where [NO.]>= " & StartRow & " And [NO.] <= " & EndRow
'合成网页
With wb.AddTable("","Table1")
    .ActiveSheet = "menu" '指定菜单
    .CreateFromDataTable(cmd.ExecuteReader)
End With
With wb.AddButtonGroup("","btg2", False)
    If page > 0 Then
        .Add("btnFirst", "第一页","","List.htm?page=0")
        .Add("btnPrev", "上一页","","List.htm?page=" & page - 1)
    Else
        .Add("btnFirst", "第一页","button").Kind = 1
        .Add("btnPrev", "上一页","button").Kind = 1
    End If
    If Endrow < count Then
        .Add("btnNext", "下一页","","List.htm?page=" & page + 1)
        .Add("btnLast", "最末页","","List.htm?page=" & pages - 1)
    Else
        .Add("btnNext", "下一页","button").Kind = 1
        .Add("btnLast", "最末页","button").Kind = 1
    End If
End With
With wb.AddActionSheet("","menu") '设计菜单
    If flt = "" Then
        .Add("mnuFilter", "数据筛选","filter.htm")
    Else
        .Add("mnuUnFilter", "取消筛选","list.htm?unfilter=true")
    End If
    .Add("mnuStatistics", "数据统计","tongji.htm")
    .Add("mnuCancel","取消","",True)
End With
e.WriteString(wb.Build)

6、自定函数Statistics用于生成页面tongji.htm:

Dim e As RequestEventArgs = args(0)
Dim wb As New WeUI
If e.PostValues.Count = 0 Then '分组统计设置
    wb.AddForm("","form1","tongji.htm")
    With wb.AddCheckGroup("form1","rdg1","选择分组列")
        .Add("fz1","产品",)
        .Add("fz2","客户")
        .Add("fz3","年")
        .Add("fz4","月")
    End With
    With wb.AddCheckGroup("form1","rdg2","选择统计列")
        .Add("tj1","数量")
        .Add("tj2","金额")
    End With
    With wb.AddButtonGroup("form1","btg1",True)
        .Add("btn1", "统计", "submit")
    End With
Else '显示统计结果
    Dim gp As new SQLGroupTableBuilder("统计表1","订单")
    gp.ConnectionName = "orders"
    gp.Filter = Functions.Execute("GetCookieFilter",e) '根据Cookie合成条件
    If e.PostValues.ContainsKey("fz1") Then
        gp.Groups.AddDef("产品")
    End If
    If e.PostValues.ContainsKey("fz2") Then
        gp.Groups.AddDef("客户")
    End If
    If e.PostValues.ContainsKey("fz3") Then
        gp.Groups.AddDef("日期",DateGroupEnum.Year,"年")
    End If
    If e.PostValues.ContainsKey("fz4") Then
        gp.Groups.AddDef("日期",DateGroupEnum.Month,"月")
    End If
    If e.PostValues.ContainsKey("tj1") Then
        gp.Totals.AddDef("数量")
    End If
    If e.PostValues.ContainsKey("tj2") Then
        gp.Totals.AddExp("金额","数量 * 单价")
    End If
    If gp.Groups.Count = 0 OrElse gp.Totals.Count = 0 Then
        wb.InsertHTML("请选择分组列和统计列!")
    Else
        With wb.AddTable("","Table1")
            .CreateFromDataTable(gp.Build(True))
        End With
        With wb.AddButtonGroup("","btg1", False)  '水平排列
            .Add("btn6", "重新统计","button","tongji.htm")
            .Add("btn7", "返回列表","button","list.htm?page=0")
        End With
    End If
End If
e.WriteString(wb.Build)