' ---------------------------------------------------------
' Editable Nested DataGrid
' ---------------------------------------------------------
'=======================================================
' FileName		    : WebForm1.aspx
' Description		: Multi Purpose DataGrid, Which can be expandable to many child grids 
' Date of Creation 	: Dec9, 2006
' Author		    : xiazhuqing
'=======================================================
Imports System.Data.SqlClient
Imports Mvc

Public Class WebForm1
    Inherits System.Web.UI.Page
    '--------------Control Declaration ----------
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents lblShowBal As System.Web.UI.WebControls.Label
    Protected WithEvents Panel1 As System.Web.UI.WebControls.Panel
    Protected WithEvents Literal1 As System.Web.UI.WebControls.Literal

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

#Region "Variables"
    Dim dgUniqueID As String
    Dim dgEditItemIndex As Int32
    Dim dgCurrentPageIndex As Int32
    Dim dgSortExpression As String
    Private Enum ExceptionType
        Red = 1
        Green = 2
        Yellow = 3
    End Enum    
#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '//Clear the Message if any
        lblMessage.Text = ""

        If Not Page.IsPostBack Then
            BindData()
            'Reset the session variables
            Session("CustomerID") = ""
            Session("PrevItemIndex") = -1
            dgSortExpression = ""
        End If
    End Sub

    '//This procedure binds the DataGrid with Query Details
    Private Sub BindData(Optional ByVal astrSort As String = "", Optional ByVal astrSort1 As String = "")

        Dim lstrSQL As String
        Dim ldtbParentTable As New DataTable
        Dim ldtbChildTable As New DataTable
        Dim ds As New DataSet
        Dim lobjDataRelation As DataRelation
        Dim lintPageSize As Int32
        Dim llngTotalRec As Int32
        Dim conn As SqlConnection
        Dim dcCust As SqlCommand
        Dim daCust As SqlDataAdapter
        Dim dcOrder As SqlCommand
        Dim daOrder As SqlDataAdapter

        Try
            '//Prepare the connection object
            conn = New SqlConnection("Persist Security Info=False;Data Source=219.219.221.30;Initial Catalog=test;User ID=sa;Password=;")
            conn.open()

            'Query to get Customer Details - Parent Query
            lstrSQL = "SELECT [憑證表].[憑證編號] as CustomerID, [憑證表].[會計期間], " & _
                      "[憑證表].[憑證字號],[憑證表].[日期], " & _
                      "[憑證表].[附單據], [憑證表].[制單], " & _
                      "[憑證表].[借方合計], [憑證表].[貸方合計], " & _
                      "[憑證表].[過帳狀態], [憑證表].[憑證狀態] FROM [憑證表] "

            '//Sort order provided
            If astrSort <> "" Then
                lstrSQL = lstrSQL & " ORDER BY " & astrSort
            Else
                'Default sort order
                lstrSQL = lstrSQL & " ORDER BY [憑證表].[憑證編號] ASC"
            End If

            dcCust = New SqlCommand(lstrSQL, conn)
            daCust = New SqlDataAdapter(dcCust)
            'daCust = New SqlDataAdapter(lstrSQL, conn)
            daCust.Fill(ldtbParentTable)
            ldtbParentTable.TableName = "ParentTable"

            'Query to get Order Details - Child Query
            'lstrSQL = "SELECT [分錄表].[科目代碼] as ChildCustomerID, [分錄表].[借方], [分錄表].[貸方], [分錄表].[摘要] FROM [分錄表]"
            lstrSQL = "SELECT [分錄表].[憑證編號] as ChildCustomerID,[分錄表].[科目代碼], [科目表].[科目名稱], [分錄表].[借方], [分錄表].[貸方], [分錄表].[摘要] FROM [分錄表] INNER JOIN [科目表] ON [分錄表].[科目代碼] = [科目表].[科目代碼]"

            '//Sort order provided
            If astrSort1 <> "" Then
                lstrSQL = lstrSQL & " ORDER BY " & astrSort1
            Else
                'Default sort order
                lstrSQL = lstrSQL & " ORDER BY [分錄表].[科目代碼] ASC"
            End If

            dcOrder = New SqlCommand(lstrSQL, conn)
            daOrder = New SqlDataAdapter(dcOrder)
            daOrder.Fill(ldtbChildTable)
            ldtbChildTable.TableName = "ChildTable"

            'Add both these tables to the dataset
            ds.Tables.Add(ldtbParentTable)
            ds.Tables.Add(ldtbChildTable)

            '//Create relation and this relation name should be used on CreateChildView
            Dim dr As New DataRelation("ParentTable_ChildTable", ldtbParentTable.Columns("CustomerID"), ldtbChildTable.Columns("ChildCustomerID"), False)
            dr.Nested = True
            ds.Relations.Add(dr)

            'Set the datasource to parent datagrid
            DataGrid1.DataSource = ds
            DataGrid1.DataBind()

        Catch exc As Exception
            LogMessage(exc)
            Exit Sub
        Finally
            daCust.Dispose()
            dcCust.Dispose()
            daOrder.Dispose()
            dcOrder.Dispose()
            ds.Dispose()
            conn.close()
        End Try
    End Sub

    '//Log messages
    Sub LogMessage(ByVal exc As Exception, Optional ByVal param As Int32 = ExceptionType.Red)
        If param = ExceptionType.Red Then
            lblMessage.Text = exc.Message.ToString().Substring(0, exc.Message.Length - 1)
            lblMessage.ForeColor = System.Drawing.Color.Red
        ElseIf param = ExceptionType.Green Then
            lblMessage.Text = exc.Message
            lblMessage.ForeColor = System.Drawing.Color.Green
        ElseIf param = ExceptionType.Yellow Then
            lblMessage.Text = exc.Message
            lblMessage.ForeColor = System.Drawing.Color.Yellow
        End If
    End Sub

#Region "DataGrid1 Event handlers"
    '//This procedure handles the Parent Grid command events
    Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand

        Dim strSQL As String

        Select Case e.CommandName

            Case "Expand"               '//Case for expanding the + sign on Parent grid to show the child grid

                Dim img As ImageButton
                Dim exp As PlaceHolder

                'Before expanding, collapse previously opened child grid
                Dim dgItem As DataGridItem
                For Each dgItem In DataGrid1.Items
                    If dgItem.ItemIndex = Session("PrevItemIndex") And dgItem.ItemIndex <> e.Item.ItemIndex Then
                        img = dgItem.Cells(0).FindControl("ImageButton1")
                        If img.ImageUrl = "~/Images/Minus.gif" Then
                            img.ImageUrl = "~/Images/Plus.gif"
                            '科目中的個數
                            exp = dgItem.Cells(4).FindControl("Expanded")
                            exp.Visible = Not exp.Visible
                        End If
                        Exit For
                    End If
                Next

                img = e.Item.Cells(0).FindControl("ImageButton1")
                If img.ImageUrl = "~/Images/Plus.gif" Then
                    img.ImageUrl = "~/Images/Minus.gif"
                Else
                    img.ImageUrl = "~/Images/Plus.gif"
                End If

                exp = e.Item.Cells(2).FindControl("Expanded")
                exp.Visible = Not exp.Visible
                Session("PrevItemIndex") = e.Item.ItemIndex     'Store the ItemIndex in session variable
                Session("CustomerID") = e.Item.Cells(1).Text    'Store the CustomerID

                'Case "Insert"   '//Case statement for Insert Click on Parent Grid
                '    Dim txtCustomerID As TextBox
                '    Dim txtCompanyName As TextBox
                '    Dim txtContactName As TextBox
                '    Dim txtContactTitle As TextBox
                '    Dim txtAddress As TextBox
                '    Dim lstrCustomerID As String
                '    Dim lstrCompanyName As String
                '    Dim lstrContactName As String
                '    Dim lstrContactTitle As String
                '    Dim lstrRFC As String
                '    Dim lstrAddress As String

                '    'Read in the values of the TextBoxes
                '    txtCustomerID = e.Item.FindControl("add_CustomerID")
                '    lstrCustomerID = txtCustomerID.Text.ToUpper
                '    txtCompanyName = e.Item.FindControl("add_kjqj")
                '    lstrCompanyName = txtCompanyName.Text
                '    txtContactName = e.Item.FindControl("add_pzzh")
                '    lstrContactName = txtContactName.Text
                '    txtContactTitle = e.Item.FindControl("add_rq")
                '    lstrContactTitle = txtContactTitle.Text
                '    txtAddress = e.Item.FindControl("add_fdj")
                '    lstrAddress = txtAddress.Text

                '    Dim conn
                '    Dim dcCust

                '    Try
                '        'Check whether the record exists before adding...
                '        strSQL = "SELECT COUNT(*) FROM [Customers] WHERE [Customers].[CustomerID] = '" & lstrCustomerID & "'"
                '        conn = New SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath(ConfigurationSettings.AppSettings("MDBPATH")))
                '        dcCust = New SqlCommand(strSQL, conn)
                '        conn.Open()
                '        Dim count = dcCust.ExecuteScalar
                '        If (count > 0) Then
                '            Throw New Exception("Record already exists...")
                '        End If
                '        dcCust.Dispose()

                '        'Create the SQL statement for addition
                '        strSQL = "INSERT INTO Customers (CustomerID, CompanyName, ContactName, " & _
                '        "ContactTitle, Address) VALUES ('" & lstrCustomerID & "','" & lstrCompanyName & "','" & _
                '        lstrContactName & "','" & lstrContactTitle & "','" & lstrAddress & "')"

                '        dcCust = New SqlCommand(strSQL, conn)
                '        dcCust.ExecuteNonQuery()
                '        LogMessage(New Exception("Record added successfully..."), ExceptionType.Green)