?Imports System.Data
Imports OrderDll.C_SQLUtil

Partial Class Order_Search_Control
    Inherits System.Web.UI.UserControl

    Dim dataNavigateUrlFields As String()
    Dim mCol As HyperLinkField
    Dim ds As DataSet
    Dim strSql As String
    Private Const MESSAGEID As String = "MESSAGEID"
    Public Event msgSubPanel(ByVal MsgDescription As String)

#Region "自定義處理"
    Protected Sub s0()
        Panel0.Visible = True
        Panel1.Visible = False
        Panel2.Visible = False
        Dim strSql As New StringBuilder("")
        strSql.AppendLine("SELECT")
        strSql.AppendLine("ClientId,")
        strSql.AppendLine("avg(Product.SellingRate * OrderList.OrderNumber ) as avgValue,")
        strSql.AppendLine("max(Product.SellingRate * OrderList.OrderNumber) as maxValue,")
        strSql.AppendLine("min(Product.SellingRate * OrderList.OrderNumber) as minValue")
        strSql.AppendLine("FROM")
        strSql.AppendLine("Product,")
        strSql.AppendLine("OrderList")
        strSql.AppendLine("WHERE")
        strSql.AppendLine("Product.ProductId = OrderList.ProductId")
        strSql.AppendLine("group by")
        strSql.AppendLine("ClientId")
        ds = OrderDll.C_SQLUtil.fExecuteDataset(strSql.ToString)

        'GridView0.Columns.Clear()
        GridView0.DataSource = ds
        GridView0.DataBind()

        Session(MESSAGEID) = 0
        RaiseEvent msgSubPanel("查找成功")

    End Sub

    Protected Sub s1()
        Panel0.Visible = False
        Panel1.Visible = True
        Panel2.Visible = False
        Dim strbSql As StringBuilder
        strbSql = New StringBuilder("")
        strbSql.AppendLine("SELECT")
        strbSql.AppendLine("Client.clientid AS clientid,")
        strbSql.AppendLine("sum((SellingRate - Cost)*OrderNumber) AS result")
        strbSql.AppendLine("FROM")
        strbSql.AppendLine(" Client,OrderList,Product")
        strbSql.AppendLine("where Client.clientid = OrderList.clientid ")
        strbSql.AppendLine("and")
        strbSql.AppendLine("Product.ProductId = OrderList.ProductId ")
        strbSql.AppendLine("and")
        strbSql.AppendLine("to_char(OrderDate,'YYYY') = to_char(sysdate, 'YYYY') ")
        strbSql.AppendLine("and ")
        strbSql.AppendLine("to_char(OrderDate,'MM') = to_char (sysdate, 'MM') ")
        strbSql.AppendLine("group by Client.clientid")
        ds = OrderDll.C_SQLUtil.fExecuteDataset(strbSql.ToString)
        'GridView1.Columns.Clear()
        GridView1.DataSource = ds
        GridView1.DataBind()

        Session(MESSAGEID) = 0
        RaiseEvent msgSubPanel("查找成功")

    End Sub

    Protected Sub s2()
        Panel0.Visible = False
        Panel1.Visible = False
        Panel2.Visible = True

        Dim strbSql As StringBuilder
        strbSql = New StringBuilder("")
        strbSql.AppendLine("SELECT")
        strbSql.AppendLine("client.clientid AS clientid,")
        strbSql.AppendLine("sum(ordernumber) AS sumnumber")
        strbSql.AppendLine("FROM")
        strbSql.AppendLine("client,orderlist")
        strbSql.AppendLine("WHERE")
        strbSql.AppendLine("orderlist.clientid=client.clientid")
        strbSql.AppendLine("group by ")
        strbSql.AppendLine("client.clientid")
        

        ds = OrderDll.C_SQLUtil.fExecuteDataset(strbSql.ToString)
        'GridView2.Columns.Clear()
        GridView2.DataSource = ds
        GridView2.DataBind()

        Session(MESSAGEID) = 0
        RaiseEvent msgSubPanel("查找成功")

    End Sub

    Protected Function sLabel3() As String
        Panel0.Visible = False
        Panel1.Visible = False
        Panel2.Visible = True

        Dim strbSql As StringBuilder
        strbSql = New StringBuilder("")
        strbSql.AppendLine("select count(sum(orderlist.ordernumber * product.sellingrate)) as totalValue")
        strbSql.AppendLine("from Client")
        strbSql.AppendLine("inner join")
        strbSql.AppendLine("orderlist")
        strbSql.AppendLine("on")

        strbSql.AppendLine("client.clientid = orderlist.clientid")
        strbSql.AppendLine("inner join")
        strbSql.AppendLine("product")
        strbSql.AppendLine("on")
        strbSql.AppendLine("product.productid = orderlist.productid")

        strbSql.AppendLine("group by client.clientid having sum(orderlist.ordernumber * product.sellingrate) > ")
        strbSql.AppendLine("(select avg(sum(orderlist.ordernumber * product.sellingrate))")
        strbSql.AppendLine("from Client")
        strbSql.AppendLine("inner join")
        strbSql.AppendLine("orderlist")
        strbSql.AppendLine("on client.clientid = orderlist.clientid")
        strbSql.AppendLine("inner join product on")
        strbSql.AppendLine("product.productid = orderlist.productid")
        strbSql.AppendLine("group by client.clientid )")

        ds = OrderDll.C_SQLUtil.fExecuteDataset(strbSql.ToString)

        Return ds.Tables(0).Rows(0).Item("totalValue").ToString

    End Function


#End Region

#Region "系統(tǒng)函數(shù)"
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Select Case DropDownList1.SelectedItem.Value
            Case 0
                s0()

            Case 1
                s1()

            Case 2
                s2()

        End Select
    End Sub

#End Region

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Label3.Text = Label3.Text & "   " & sLabel3()
        'Label4.Text = ""  未加！！！！！！！！！！！
    End Sub
End Class