Imports System.Data

Imports System.Data.SqlClient

Imports System.Windows.Forms

Imports Excel = Microsoft.Office.Interop.Excel

Partial Class chu_de

    Inherits System.Web.UI.Page

    Dim sqlCnn As New SqlConnection

    Dim sqlAdap As New SqlDataAdapter

    Dim dgvOrders As New DataTable

    Dim dgvDetail As New DataTable

    'Dim dataSet As DataSet = New DataSet("Orders")

    Dim sqlCmd As New SqlCommand()

    Dim dgv As New BindingSource()

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then

            Detail()

            Orders()

            locktextbox(True)

            VisibleButtons(False)

        End If

    End Sub

    Public Sub Detail()

        Dim sqlCmd As New SqlCommand()

        sqlCnn = New SqlConnection(Application("strConn"))

        Try

            sqlCmd.CommandType = CommandType.StoredProcedure

            sqlCmd.CommandText = "GetDetailsByCustomerID"

            sqlCmd.Connection = sqlCnn

            sqlCnn.Open()

            sqlCmd.Parameters.AddWithValue("@CustomerID", Text1.Text)

            sqlAdap = New SqlDataAdapter(sqlCmd)

            'sqlAdap = New SqlDataAdapter(strSQL, sqlCnn)

            sqlAdap.Fill(dgvDetail)

            'GridView1.DataSource = sqlCmd.ExecuteReader()

            'GridView1.DataBind()

            If dgvDetail.Rows.Count > 0 Then

                GridView2.DataSource = dgvDetail

                GridView2.DataBind()

                For i = 0 To GridView2.Rows.Count - 1

                    GridView2.Rows(i).Cells(3).Text = FormatNumber(GridView2.Rows(i).Cells(3).Text, , TriState.False)

                    GridView2.Rows(i).Cells(4).Text = FormatNumber(GridView2.Rows(i).Cells(4).Text, , TriState.False)

                Next

                NameC.Text = dgvDetail.Rows.Item(0).Item("ContactName")

            End If

        Catch ex As Exception

            lblError.Text = "Lỗi: " & ex.Message

        Finally

            sqlCnn.Close()

            sqlAdap.Dispose()

            dgvDetail.Dispose()

            sqlCnn.Dispose()

            sqlCmd.Dispose()

        End Try

    End Sub

    Public Sub Orders()

        sqlCnn = New SqlConnection(Application("strConn"))

        Try

            sqlCmd.CommandType = CommandType.StoredProcedure

            sqlCmd.CommandText = "GetAllOrders"

            sqlCmd.Connection = sqlCnn

            sqlCnn.Open()

            sqlAdap = New SqlDataAdapter(sqlCmd)

            sqlAdap.Fill(dgvOrders)

            'sqlAdap.Fill(dataSet)

            'dvg.DataSource = dgvOrders

            'dvg.DataMember = dgvOrders

            If dgvOrders.Rows.Count > 0 Then

                GridView1.DataSource = dgvOrders

                GridView1.DataBind()

                For i = 0 To GridView1.Rows.Count - 1

                    'GridView1.Rows(i).Cells(5).Text = Format(GridView1.Rows(i).Cells(5).Text, "Fixed")

                    GridView1.Rows(i).Cells(5).Text = FormatNumber(GridView1.Rows(i).Cells(5).Text, , TriState.False)

                Next

            Else

                lblError.Text = "Không có dữ liệu"

            End If

        Catch ex As Exception

            lblError.Text = "Lỗi: " & ex.Message

        Finally

            sqlCnn.Close()

            sqlAdap.Dispose()

            dgvOrders.Dispose()

            sqlCnn.Dispose()

            sqlCmd.Dispose()

        End Try

    End Sub

    Public Sub locktextbox(ByVal b As Boolean)

    End Sub

    Public Sub VisibleButtons(ByVal b As Boolean)

        'Button1.Visible = b

        'Button2.Visible = b

    End Sub

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging

        GridView1.PageIndex = e.NewPageIndex

        'GridView1.DataSource = dgvOrders

        'GridView1.DataBind()

        Orders()

    End Sub

    Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting

        Dim idRow As Integer = e.RowIndex

        Dim id As String = HttpUtility.HtmlDecode(GridView1.Rows(idRow).Cells(1).Text)

        If MessageBox.Show("Bạn có chắc chắn muốn xóa khách hàng " & id & " không ?", "Xác nhận", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) = DialogResult.Yes Then

            sqlCnn = New SqlConnection(Application("strConn"))

            Try

                Dim sqlCmd As New SqlCommand

                sqlCmd.CommandType = CommandType.StoredProcedure

                sqlCmd.CommandText = "DelOrders"

                sqlCmd.Connection = sqlCnn

                sqlCmd.Parameters.AddWithValue("@OrderID", id)

                sqlCnn.Open()

                sqlCmd.ExecuteNonQuery()

                MessageBox.Show("Xóa thành công !", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Question)

            Catch ex As Exception

                lblError.Text = "Lỗi : " & ex.Message

            Finally

                sqlCnn.Close()

                sqlCmd.Dispose()

                sqlCnn.Dispose()

            End Try

        End If

    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged

        Dim idRow As Integer = GridView1.SelectedIndex

        Session("select") = HttpUtility.HtmlDecode(GridView1.Rows(idRow).Cells(1).Text)

        'Application("select") = HttpUtility.HtmlDecode(GridView1.Rows(idRow).Cells(1).Text)

        'Global sel as string = GridView1.Rows(idRow).Cells(0).Text

        Response.Redirect("Edit.aspx")

        'locktextbox(False)

        'VisibleButtons(True)

    End Sub

    Protected Sub Insert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Insert.Click

        Response.Redirect("Insert.aspx")

    End Sub

    Protected Sub Printer_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles Printer.Click

        'Response.Clear()

        'Response.AddHeader("content-disposition", "attachment;filename=Danhsach.xls")

        'Response.Charset = String.Empty

        'Response.ContentType = "application/vnd.xls"

        'Dim sw As System.IO.StringWriter = New System.IO.StringWriter

        'Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)

        'GridView1.RenderControl(hw)

        'Response.Write(sw.ToString())

        'Response.End()

        Dim xlApp As Excel.Application

        Dim xlWorkBook As Excel.Workbook

        Dim xlWorkSheet As Excel.Worksheet

        Dim misValue As Object = System.Reflection.Missing.Value

        Dim i As Int16, j As Int16

        xlApp = New Excel.ApplicationClass

        xlWorkBook = xlApp.Workbooks.Add(misValue)

        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        xlWorkSheet.Cells(1, 1) = "STT"

        xlWorkSheet.Cells(1, 2) = "Mã hóa đơn"

        xlWorkSheet.Cells(1, 3) = "Tên khách hàng"

        xlWorkSheet.Cells(1, 4) = "Người chuyển"

        xlWorkSheet.Cells(1, 5) = "Ngày đặt hàng"

        xlWorkSheet.Cells(1, 6) = "Số tiền"

        For i = 0 To GridView1.Rows.Count - 2

            For j = 0 To GridView1.Columns.Count - 1

                If j = 0 Then

                    xlWorkSheet.Cells(i + 2, j + 1) = i + 1

                ElseIf j = 5 Then

                    'GridView1.Rows(i).Cells(j).Text = Format(GridView1.Rows(i).Cells(j).Text, "Fixed")

                    xlWorkSheet.Cells(i + 2, j + 1) = FormatNumber(GridView1.Rows(i).Cells(j).Text, , TriState.False)

                Else

                    xlWorkSheet.Cells(i + 2, j + 1) = GridView1.Rows(i).Cells(j).Text

                End If

            Next

        Next

        xlWorkBook.SaveAs("C:\Danhsach.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _

         Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)

        xlWorkBook.Close(True, misValue, misValue)

        xlApp.Quit()

        releaseObject(xlWorkSheet)

        releaseObject(xlWorkBook)

        releaseObject(xlApp)

        'MessageBox.Show("Over")

    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

        ' Do NOT call MyBase.VerifyRenderingInServerForm

    End Sub

    Protected Overrides Sub Render(ByVal writer As HtmlTextWriter)

        ' Ensure that the control is nested in a server form.

        If Not (Page Is Nothing) Then

            Page.VerifyRenderingInServerForm(Me)

        End If

        MyBase.Render(writer)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing

        Catch ex As Exception

            obj = Nothing

            MessageBox.Show("Exception Occured while releasing object " + ex.ToString())

        Finally

            GC.Collect()

        End Try

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Detail()

    End Sub

    Protected Sub GridView2_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView2.PageIndexChanging

        GridView2.PageIndex = e.NewPageIndex

        'GridView2.DataSource = dgvDetail

        'GridView2.DataBind()

        Detail()

    End Sub

End Class