Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Text
Public Class pedidos_cliente
    Inherits System.Web.UI.Page
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents cbocustomer As System.Web.UI.WebControls.DropDownList
    Protected WithEvents btnbuscar As System.Web.UI.WebControls.Button
    Protected WithEvents btnmayor As System.Web.UI.WebControls.Button
    Protected WithEvents Label4 As System.Web.UI.WebControls.Label
    Protected WithEvents dgdordenes As System.Web.UI.WebControls.DataGrid
    Protected WithEvents Label3 As System.Web.UI.WebControls.Label
    Protected WithEvents btnmenor As System.Web.UI.WebControls.Button
    Protected WithEvents Label5 As System.Web.UI.WebControls.Label
    Protected WithEvents dgdmenor As System.Web.UI.WebControls.DataGrid
    Protected WithEvents dgddetalle As System.Web.UI.WebControls.DataGrid

#Region " Código generado por el Diseñador de Web Forms "

    'El Diseñador de Web Forms requiere esta llamada.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: el Diseñador de Web Forms requiere esta llamada de método
        'No lo modifique con el editor de código.
        InitializeComponent()
    End Sub

#End Region
    Dim ocon As SqlConnection
    Dim scon As String

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Introducir aquí el código de usuario para inicializar la página
        Dim sql As String
        scon = "uid=sa;pwd=;database=northwind;server=."

        ocon = New SqlConnection(scon)
        sql = "select * from customers"
        Dim cmd As SqlCommand
        cmd = New SqlCommand(sql, ocon)
        cmd.Connection.Open()

        Dim oreader As SqlDataReader
        oreader = cmd.ExecuteReader

        While oreader.Read
            cbocustomer.Items.Add(oreader.GetString(1))
            cbocustomer.Items(cbocustomer.Items.Count - 1).Value = oreader.GetString(0)
        End While

        oreader.Close()
    End Sub

    Private Sub btnbuscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbuscar.Click
        Dim ssql As StringBuilder
        Dim xcustomerid As String

        ssql = New StringBuilder("select o.customerid, o.orderid, o.shipcountry, o.orderdate, sum(d.unitprice * d.quantity) as monto from orders o, [Order Details] d where o.orderid = d.orderid and o.customerid = '" + Me.cbocustomer.SelectedItem.Value + "' group by o.customerid, o.orderid, o.shipcountry, o.orderdate")

        Dim cmd As New SqlCommand(ssql.ToString, ocon)
        Dim oreader = cmd.ExecuteReader

        dgdordenes.DataSource = oreader
        dgdordenes.DataBind()

        dgddetalle.DataSource = ""
        dgddetalle.DataBind()
    End Sub

    Private Sub btnmayor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmayor.Click
        Dim wsql As String
        Dim wcustomerid As String
        Dim worderid As String

        wcustomerid = Me.cbocustomer.SelectedItem.Value
        wsql = "select top 1 o.orderid, sum(d.unitprice * d.quantity) as Monto from orders o, [Order Details] d where o.orderid = d.orderid and customerid = '" + wcustomerid + "' group by o.customerid, o.orderid order by Monto Desc"

        Dim wcmd As New SqlCommand(wsql, ocon)
        Dim wreader As SqlDataReader
        wreader = wcmd.ExecuteReader
        While wreader.Read()
            worderid = wreader(0)

        End While
        wreader.Close()

        Dim ysql As StringBuilder
        Dim ycustomerid As String

        ysql = New StringBuilder("select d.orderid, d.productid, p.productname, d.unitprice, d.quantity, d.unitprice * d.quantity as subtotal  from [order Details] d, products p where d.productid = p.productid and d.orderid = '" & worderid & "'  order by p.productid")
        Dim ycmd As New SqlCommand(ysql.ToString, ocon)
        Dim yreader = ycmd.ExecuteReader

        dgddetalle.DataSource = yreader
        dgddetalle.DataBind()
    End Sub

    Private Sub btnmenor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmenor.Click
        Dim xsql As String
        Dim xcustomerid As String
        Dim xorderid As String

        xcustomerid = Me.cbocustomer.SelectedItem.Value

        xsql = "select top 1 o.orderid, sum(d.unitprice * d.quantity) as Monto from orders o, [Order Details] d where o.orderid = d.orderid and customerid = '" + xcustomerid + "' group by o.customerid, o.orderid order by Monto asc"

        Dim xcmd As New SqlCommand(xsql, ocon)
        Dim xreader As SqlDataReader
        xreader = xcmd.ExecuteReader
        While xreader.Read()
            xorderid = xreader(0)
        End While

        xreader.Close()

        Dim zsql As StringBuilder
        Dim zcustomerid As String

        zsql = New StringBuilder("select d.orderid, d.productid, p.productname, d.unitprice, d.quantity, d.unitprice * d.quantity as subtotal from [order details] d, products p where d.productid = p.productid and d.orderid = '" & xorderid & "' order by p.productid ")

        Dim zcmd As New SqlCommand(zsql.ToString, ocon)
        Dim zreader = zcmd.ExecuteReader

        dgdmenor.DataSource = zreader
        dgdmenor.DataBind()

    End Sub
End Class