Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports Telerik.Web.UI
Imports Microsoft.SharePoint.WebControls
Imports System.Data.SqlClient

Partial Public Class Survey_FrontEndUserControl
    Inherits UserControl

    Dim web As SPWeb = SPContext.Current.Web
    Private p_Country As String
    Private p_CountryFull As String
    Private p_Region As String
    Private p_Subregion As String

    Public Property Country() As String
        Get
            Return p_Country
        End Get
        Set(ByVal value As String)
            p_Country = value
        End Set
    End Property

    Protected Overrides Sub OnInit(ByVal e As EventArgs)
        MyBase.OnInit(e)
        Page.ClientScript.RegisterStartupScript(GetType(Survey_FrontEnd), Me.ID, "_spOriginalFormAction = document.forms[0].action;_spSuppressFormOnSubmitWrapper=true;", True)
        If Me.Page.Form IsNot Nothing Then
            Dim formOnSubmitAtt As String = Me.Page.Form.Attributes("onsubmit")
            If Not String.IsNullOrEmpty(formOnSubmitAtt) AndAlso formOnSubmitAtt = "return _spFormOnSubmitWrapper();" Then
                Me.Page.Form.Attributes("onsubmit") = "_spFormOnSubmitWrapper();"
            End If
        End If

        Dim scriptManager As ScriptManager = scriptManager.GetCurrent(Me.Page)
        If scriptManager Is Nothing Then
            scriptManager = New RadScriptManager()
            Me.Page.Form.Controls.AddAt(0, scriptManager)
        End If
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If p_Country <> "" Then
            Dim db As String = ""
            Dim table_temp As DataTable

            If Not Request.Params("db") Is Nothing Then
                db = Request.Params("db").ToString
            Else
                db = "generalmarket"
            End If

            table_temp = getSQLData("SELECT region, subregion, country, currency FROM country_base WHERE iso ='" & p_Country & "'")
            market_currency.Text = table_temp.Rows(0)("country").ToString & " (" & p_Country & ") : " & table_temp.Rows(0)("currency").ToString

            p_CountryFull = table_temp.Rows(0)("country").ToString
            p_Region = table_temp.Rows(0)("region").ToString
            p_Subregion = table_temp.Rows(0)("subregion").ToString

            table_temp = Nothing

            fillVersions()
            fillSheets(db)

            If Page.IsPostBack = False Then
                Select Case db
                    Case "generalmarket"
                        db_navigation.Items(db_navigation.Items.FindItemByValue("generalmarket").Index).Selected = True
                        page_gm.Visible = True
                        page_market.Visible = False
                        page_cc.Visible = False
                        panel_gm.Visible = True
                        panel_market.Visible = False
                        panel_cc.Visible = False

                        fillTopicsGM()

                        If topic_navigation_gm.Items(0).Items.Count > 0 Then
                            getQuestionGM(CInt(topic_navigation_gm.Items(0).Items(0).Value.Split(CChar("#"))(1)))
                            topic_navigation_gm.Items(0).Items(0).Selected = True
                            topic_navigation_gm.Items(0).Expanded = True
                        End If
                    Case "markets"
                        db_navigation.Items(db_navigation.Items.FindItemByValue("markets").Index).Selected = True
                        page_gm.Visible = False
                        page_market.Visible = True
                        page_cc.Visible = False
                        panel_gm.Visible = False
                        panel_market.Visible = True
                        panel_cc.Visible = False

                        fillTopicsMarkets()

                        If topic_navigation_market.Items(0).Items.Count > 0 Then
                            getQuestionMarkets(CInt(topic_navigation_market.Items(0).Items(0).Value.Split(CChar("#"))(1)))
                            topic_navigation_market.Items(0).Items(0).Selected = True
                            topic_navigation_market.Items(0).Expanded = True
                        End If
                    Case "competitorcustomer"
                        db_navigation.Items(db_navigation.Items.FindItemByValue("competitorcustomer").Index).Selected = True
                        page_gm.Visible = False
                        page_market.Visible = False
                        page_cc.Visible = True
                        panel_gm.Visible = False
                        panel_market.Visible = False
                        panel_cc.Visible = True

                        fillTopicsCC()

                        If topic_navigation_cc.Items.Count > 0 Then
                            topic_navigation_cc.Items(0).Selected = True
                            cc_topicheadline.Text = topic_navigation_cc.Items(0).Text
                        End If
                End Select
            End If
        End If
    End Sub

    Protected Sub db_navigation_ItemClick(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadMenuEventArgs) Handles db_navigation.ItemClick
        Select Case e.Item.Text
            Case "General Market"
                Response.Redirect(web.Url & "/default.aspx?db=generalmarket", True)
            Case "Markets"
                Response.Redirect(web.Url & "/default.aspx?db=markets", True)
            Case "Competitor/Customer"
                Response.Redirect(web.Url & "/default.aspx?db=competitorcustomer", True)
        End Select
    End Sub

    Protected Sub sheet_navigation_TabClick(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadTabStripEventArgs) Handles sheet_navigation.TabClick
        Dim db As String = ""

        If Not Request.Params("db") Is Nothing Then
            db = Request.Params("db").ToString
        End If

        Select Case db
            Case "markets"
                fillTopicsMarkets()

                If topic_navigation_market.Items(0).Items.Count > 0 Then
                    getQuestionMarkets(CInt(topic_navigation_market.Items(0).Items(0).Value.Split(CChar("#"))(1)))
                    topic_navigation_market.Items(0).Items(0).Selected = True
                    topic_navigation_market.Items(0).Expanded = True
                End If
            Case "competitorcustomer"
                fillTopicsCC()

                If topic_navigation_cc.Items.Count > 0 Then
                    topic_navigation_cc.Items(0).Selected = True
                    cc_topicheadline.Text = topic_navigation_cc.Items(0).Text
                End If
        End Select
    End Sub


#Region "fillOperations"
    Protected Sub fillVersions()
        Dim table As DataTable = getSQLData("SELECT id, version FROM versions WHERE active = 'true' ORDER BY id DESC")

        versions.Items.Clear()

        For i As Integer = 0 To table.Rows.Count - 1
            versions.Items.Add(New RadComboBoxItem(table.Rows(i)("version").ToString, table.Rows(i)("id").ToString))
        Next
    End Sub

    Protected Sub fillSheets(ByVal db As String)
        Dim table As DataTable = getSQLData("SELECT id, sheet FROM sheet_base WHERE assigned_version = '" & versions.SelectedItem.Text & "' AND db = '" & db & "' ORDER BY sortorder")

        sheet_navigation.Tabs.Clear()

        For i As Integer = 0 To table.Rows.Count - 1
            sheet_navigation.Tabs.Add(New RadTab(table.Rows(i)("sheet").ToString, table.Rows(i)("id").ToString))
        Next
    End Sub

    Protected Sub fillTopicsGM()
        Dim table As DataTable = getSQLData("SELECT id, topic FROM topics_gm WHERE assigned_version = '" & versions.SelectedItem.Text & "' AND sheet = " & sheet_navigation.SelectedTab.Value & " ORDER BY sortorder")

        topic_navigation_gm.Items.Clear()

        For i As Integer = 0 To table.Rows.Count - 1
            topic_navigation_gm.Items.Add(New RadPanelItem(table.Rows(i)("topic").ToString))
            topic_navigation_gm.Items(i).Value = i & ".0#" & table.Rows(i)("id").ToString

            fillQuestionsGM(table.Rows(i)("id").ToString, i)
        Next
    End Sub

    Protected Sub fillQuestionsGM(ByVal topic_id As String, ByVal parent As Integer)
        Dim table As DataTable = getSQLData("SELECT id, question FROM questions_gm WHERE assigned_version = '" & versions.SelectedItem.Text & "' AND topic = " & topic_id & " ORDER BY sortorder")

        For i As Integer = 0 To table.Rows.Count - 1
            topic_navigation_gm.Items(parent).Items.Add(New RadPanelItem(table.Rows(i)("question").ToString))
            topic_navigation_gm.Items(parent).Items(i).Value = parent & "." & i + 1 & "_" & topic_id & "#" & table.Rows(i)("id").ToString
        Next
    End Sub

    Protected Sub fillTopicsMarkets()
        Dim table As DataTable = getSQLData("SELECT id, topic FROM topics_markets WHERE assigned_version = '" & versions.SelectedItem.Text & "' AND sheet = " & sheet_navigation.SelectedTab.Value & " ORDER BY sortorder")

        topic_navigation_market.Items.Clear()

        For i As Integer = 0 To table.Rows.Count - 1
            topic_navigation_market.Items.Add(New RadPanelItem(table.Rows(i)("topic").ToString))
            topic_navigation_market.Items(i).Value = i & ".0#" & table.Rows(i)("id").ToString

            fillQuestionsMarkets(table.Rows(i)("id").ToString, i)
        Next
    End Sub

    Protected Sub fillQuestionsMarkets(ByVal topic_id As String, ByVal parent As Integer)
        Dim table As DataTable = getSQLData("SELECT id, question FROM questions_markets WHERE assigned_version = '" & versions.SelectedItem.Text & "' AND topic = " & topic_id & " ORDER BY sortorder")

        For i As Integer = 0 To table.Rows.Count - 1
            topic_navigation_market.Items(parent).Items.Add(New RadPanelItem(table.Rows(i)("question").ToString))
            topic_navigation_market.Items(parent).Items(i).Value = parent & "." & i + 1 & "_" & topic_id & "#" & table.Rows(i)("id").ToString
        Next
    End Sub

    Protected Sub fillTopicsCC()
        Dim table As DataTable = getSQLData("SELECT id, topic FROM topics_cc WHERE assigned_version = '" & versions.SelectedItem.Text & "' AND sheet = " & sheet_navigation.SelectedTab.Value & " ORDER BY sortorder")

        topic_navigation_cc.Items.Clear()

        For i As Integer = 0 To table.Rows.Count - 1
            topic_navigation_cc.Items.Add(New RadPanelItem(table.Rows(i)("topic").ToString))
            topic_navigation_cc.Items(i).Value = i & ".0#" & table.Rows(i)("id").ToString
        Next
    End Sub
#End Region


#Region "getOperations"
    Protected Sub getQuestionGM(ByVal q_id As Integer)
        Dim table_question As DataTable = getSQLData("SELECT topic, question, help, datatype, dropdown_possibilities FROM questions_gm WHERE id = " & q_id & "")
        Dim table_topic As DataTable = getSQLData("SELECT topic, description FROM topics_gm WHERE id = " & table_question.Rows(0)("topic").ToString & "")
        Dim table_version As DataTable = getSQLData("SELECT years_gm FROM versions WHERE version = '" & versions.SelectedItem.Text & "'")

        gm_topicheadline.Text = table_topic.Rows(0)("topic").ToString
        gm_topicdescription.Text = table_topic.Rows(0)("description").ToString
        gm_questionheadline.Text = table_question.Rows(0)("question").ToString
        gm_questiondescription.Text = table_question.Rows(0)("help").ToString
        gm_questionheadline.Visible = True
        gm_questiondescription.Visible = True

        gm_year1.Text = "Year " & table_version.Rows(0)("years_gm").ToString.Split(CChar(";"))(0)
        gm_year2.Text = "Year " & table_version.Rows(0)("years_gm").ToString.Split(CChar(";"))(1)

        ph_gm_year1.Controls.Clear()
        ph_gm_year2.Controls.Clear()

        Select Case table_question.Rows(0)("datatype").ToString
            Case "dropdown"
                Dim txt_gm_year1 As New RadComboBox
                Dim txt_gm_year2 As New RadComboBox

                txt_gm_year1.Skin = "Telerik"
                txt_gm_year2.Skin = "Telerik"
                txt_gm_year1.EnableViewState = False
                txt_gm_year2.EnableViewState = False

                For i As Integer = 0 To table_question.Rows(0)("dropdown_possibilities").ToString.Split(CChar("#")).Length - 1
                    txt_gm_year1.Items.Add(New RadComboBoxItem(table_question.Rows(0)("dropdown_possibilities").ToString.Split(CChar("#"))(i), table_question.Rows(0)("dropdown_possibilities").ToString.Split(CChar("#"))(i)))
                    txt_gm_year2.Items.Add(New RadComboBoxItem(table_question.Rows(0)("dropdown_possibilities").ToString.Split(CChar("#"))(i), table_question.Rows(0)("dropdown_possibilities").ToString.Split(CChar("#"))(i)))
                Next

                ph_gm_year1.Controls.Add(txt_gm_year1)
                ph_gm_year2.Controls.Add(txt_gm_year2)
            Case Else
                Dim txt_gm_year1 As New RadTextBox
                Dim txt_gm_year2 As New RadTextBox

                txt_gm_year1.Skin = "Telerik"
                txt_gm_year2.Skin = "Telerik"
                txt_gm_year1.EnableViewState = False
                txt_gm_year2.EnableViewState = False

                ph_gm_year1.Controls.Add(txt_gm_year1)
                ph_gm_year2.Controls.Add(txt_gm_year2)
        End Select

        getAnswersGM(CInt(table_version.Rows(0)("years_gm").ToString.Split(CChar(";"))(0)), CInt(table_version.Rows(0)("years_gm").ToString.Split(CChar(";"))(1)), q_id, table_question.Rows(0)("datatype").ToString)
    End Sub

    Protected Sub getAnswersGM(ByVal firstyear As Integer, ByVal secondyear As Integer, ByVal q_id As Integer, ByVal datatype As String)
        Dim table_answer1 As DataTable
        Dim table_answer2 As DataTable

        table_answer1 = getSQLData("SELECT id, result FROM general_market_work WHERE question_id = " & q_id & " AND assigned_version = '" & versions.SelectedItem.Text & "' AND year = " & firstyear & "")
        table_answer2 = getSQLData("SELECT id, result FROM general_market_work WHERE question_id = " & q_id & " AND assigned_version = '" & versions.SelectedItem.Text & "' AND year = " & secondyear & "")

        Select Case datatype
            Case "dropdown"
                Dim txt_gm_year1 As RadComboBox = DirectCast(ph_gm_year1.Controls(0), RadComboBox)
                Dim txt_gm_year2 As RadComboBox = DirectCast(ph_gm_year2.Controls(0), RadComboBox)

                If table_answer1.Rows.Count > 0 Then
                    txt_gm_year1.Items(txt_gm_year1.Items.FindItemByText(table_answer1.Rows(0)("result").ToString).Index).Selected = True
                    gm_year1_datafilled.Text = "1_dropdown#" & table_answer1.Rows(0)("id").ToString
                Else
                    gm_year1_datafilled.Text = "0_dropdown"
                End If

                If table_answer2.Rows.Count > 0 Then
                    txt_gm_year2.Items(txt_gm_year2.Items.FindItemByText(table_answer2.Rows(0)("result").ToString).Index).Selected = True
                    gm_year2_datafilled.Text = "1_dropdown#" & table_answer1.Rows(0)("id").ToString
                Else
                    gm_year2_datafilled.Text = "0_dropdown"
                End If
            Case Else
                Dim txt_gm_year1 As RadTextBox = DirectCast(ph_gm_year1.Controls(0), RadTextBox)
                Dim txt_gm_year2 As RadTextBox = DirectCast(ph_gm_year2.Controls(0), RadTextBox)

                If table_answer1.Rows.Count > 0 Then
                    txt_gm_year1.Text = table_answer1.Rows(0)("result").ToString
                    gm_year1_datafilled.Text = "1_other#" & table_answer1.Rows(0)("id").ToString
                Else
                    gm_year1_datafilled.Text = "0_other"
                End If

                If table_answer2.Rows.Count > 0 Then
                    txt_gm_year2.Text = table_answer2.Rows(0)("result").ToString
                    gm_year2_datafilled.Text = "1_other#" & table_answer1.Rows(0)("id").ToString
                Else
                    gm_year2_datafilled.Text = "0_other"
                End If
        End Select
    End Sub

    Protected Sub getQuestionMarkets(ByVal q_id As Integer)
        Dim table_question As DataTable = getSQLData("SELECT topic, question, help FROM questions_markets WHERE id = " & q_id & "")
        Dim table_topic As DataTable = getSQLData("SELECT topic, description FROM topics_markets WHERE id = " & table_question.Rows(0)("topic").ToString & "")
        Dim table_version As DataTable = getSQLData("SELECT years_markets, market_ref_year FROM versions WHERE version = '" & versions.SelectedItem.Text & "'")

        market_topicheadline.Text = table_topic.Rows(0)("topic").ToString
        market_topicdescription.Text = table_topic.Rows(0)("description").ToString
        market_questionheadline.Text = table_question.Rows(0)("question").ToString
        market_questiondescription.Text = table_question.Rows(0)("help").ToString
        market_questionheadline.Visible = True
        market_questiondescription.Visible = True

        market_year1.Text = "Year " & table_version.Rows(0)("market_ref_year").ToString
        market_year2.Text = "Year " & table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(0)
        market_year3.Text = "Year " & table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(1)
        market_year4.Text = "Year " & table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(2)
        market_year5.Text = "Year " & table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(3)
        market_year6.Text = "Year " & table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(4)

        getAnswersMarkets(CInt(table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(0)), CInt(table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(1)), CInt(table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(2)), CInt(table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(3)), CInt(table_version.Rows(0)("years_markets").ToString.Split(CChar(";"))(4)), q_id)
    End Sub

    Protected Sub getAnswersMarkets(ByVal firstyear As Integer, ByVal secondyear As Integer, ByVal thirdyear As Integer, ByVal fourthyear As Integer, ByVal fithyear As Integer, ByVal q_id As Integer)
        Dim table_answer1 As DataTable
        Dim table_answer2 As DataTable
        Dim table_answer3 As DataTable
        Dim table_answer4 As DataTable
        Dim table_answer5 As DataTable

        table_answer1 = getSQLData("SELECT result FROM market_survey_work WHERE question_id = " & q_id & " AND assigned_version = '" & versions.SelectedItem.Text & "' AND year = " & firstyear & "")
        table_answer2 = getSQLData("SELECT result FROM market_survey_work WHERE question_id = " & q_id & " AND assigned_version = '" & versions.SelectedItem.Text & "' AND year = " & secondyear & "")
        table_answer3 = getSQLData("SELECT result FROM market_survey_work WHERE question_id = " & q_id & " AND assigned_version = '" & versions.SelectedItem.Text & "' AND year = " & thirdyear & "")
        table_answer4 = getSQLData("SELECT result FROM market_survey_work WHERE question_id = " & q_id & " AND assigned_version = '" & versions.SelectedItem.Text & "' AND year = " & fourthyear & "")
        table_answer5 = getSQLData("SELECT result FROM market_survey_work WHERE question_id = " & q_id & " AND assigned_version = '" & versions.SelectedItem.Text & "' AND year = " & fithyear & "")

        If table_answer1.Rows.Count > 0 Then
            For i As Integer = 0 To table_answer1.Rows.Count - 1
                If table_answer1.Rows(i)("datatype").ToString = "Number" Then
                    market_txt_year2_unit.Text = table_answer1.Rows(i)("result").ToString
                Else
                    market_txt_year2_value.Text = table_answer1.Rows(i)("result").ToString
                End If
            Next
        End If

        If table_answer2.Rows.Count > 0 Then
            For i As Integer = 0 To table_answer2.Rows.Count - 1
                If table_answer2.Rows(i)("datatype").ToString = "Number" Then
                    market_txt_year3_unit.Text = table_answer2.Rows(i)("result").ToString
                Else
                    market_txt_year3_value.Text = table_answer2.Rows(i)("result").ToString
                End If
            Next
        End If

        If table_answer3.Rows.Count > 0 Then
            For i As Integer = 0 To table_answer3.Rows.Count - 1
                If table_answer3.Rows(i)("datatype").ToString = "Number" Then
                    market_txt_year4_unit.Text = table_answer3.Rows(i)("result").ToString
                Else
                    market_txt_year4_value.Text = table_answer3.Rows(i)("result").ToString
                End If
            Next
        End If

        If table_answer4.Rows.Count > 0 Then
            For i As Integer = 0 To table_answer4.Rows.Count - 1
                If table_answer4.Rows(i)("datatype").ToString = "Number" Then
                    market_txt_year5_unit.Text = table_answer4.Rows(i)("result").ToString
                Else
                    market_txt_year5_value.Text = table_answer4.Rows(i)("result").ToString
                End If
            Next
        End If

        If table_answer5.Rows.Count > 0 Then
            For i As Integer = 0 To table_answer5.Rows.Count - 1
                If table_answer5.Rows(i)("datatype").ToString = "Number" Then
                    market_txt_year6_unit.Text = table_answer5.Rows(i)("result").ToString
                Else
                    market_txt_year6_value.Text = table_answer5.Rows(i)("result").ToString
                End If
            Next
        End If
    End Sub
#End Region

#Region "saveOperations"
    Protected Sub gm_save_Click(ByVal sender As Object, ByVal e As EventArgs) Handles gm_save.Click
        Dim topicnav As RadPanelItem = DirectCast(topic_navigation_gm.SelectedItem.Parent, RadPanelItem)
        Dim result As String = ""

        If gm_year1_datafilled.Text.Contains("1") = True Then
            If gm_year1_datafilled.Text.Contains("dropdown") = True Then
                result = DirectCast(ph_gm_year1.Controls(0), RadComboBox).SelectedItem.Text
            Else
                result = DirectCast(ph_gm_year1.Controls(0), RadTextBox).Text
            End If

            If result.Trim = "" Then
                SaveEditSQLData("DELETE FROM general_market_work WHERE id = " & CInt(gm_year1_datafilled.Text.Split(CChar("#"))(1)))
            Else
                SaveEditSQLData("UPDATE general_market_work SET result = '" & result & "' WHERE id = " & CInt(gm_year1_datafilled.Text.Split(CChar("#"))(1)))
            End If
        Else
            If gm_year1_datafilled.Text.Contains("dropdown") = True Then
                result = DirectCast(ph_gm_year1.Controls(0), RadComboBox).SelectedItem.Text
            Else
                result = DirectCast(ph_gm_year1.Controls(0), RadTextBox).Text
            End If

            If result.Trim <> "" Then
                SaveEditSQLData("INSERT INTO general_market_work (assigned_version, region, subregion, country, topic, topic_id, question, question_id, year, result) VALUES ('" & versions.SelectedItem.Text & "', '" & p_Region & "', '" & p_Subregion & "', '" & p_CountryFull & "', '" & topicnav.Text & "', '" & topicnav.Value.Split(CChar("#"))(1) & "', '" & topic_navigation_gm.SelectedItem.Text & "', " & CInt(topic_navigation_gm.SelectedItem.Value.Split(CChar("#"))(1)) & ", " & CInt(gm_year1.Text.Split(CChar(" "))(1)) & ", '" & result & "')")
            End If
        End If

        If gm_year2_datafilled.Text = "1" Then
            If gm_year2_datafilled.Text.Contains("dropdown") = True Then
                result = DirectCast(ph_gm_year2.Controls(0), RadComboBox).SelectedItem.Text
            Else
                result = DirectCast(ph_gm_year2.Controls(0), RadTextBox).Text
            End If

            If result.Trim = "" Then
                SaveEditSQLData("DELETE FROM general_market_work WHERE id = " & CInt(gm_year2_datafilled.Text.Split(CChar("#"))(1)))
            Else
                SaveEditSQLData("UPDATE general_market_work SET result = '" & result & "' WHERE id = " & CInt(gm_year2_datafilled.Text.Split(CChar("#"))(1)))
            End If
        Else
            If gm_year2_datafilled.Text.Contains("dropdown") = True Then
                result = DirectCast(ph_gm_year2.Controls(0), RadComboBox).SelectedItem.Text
            Else
                result = DirectCast(ph_gm_year2.Controls(0), RadTextBox).Text
            End If

            If result.Trim <> "" Then
                SaveEditSQLData("INSERT INTO general_market_work (assigned_version, region, subregion, country, topic, topic_id, question, question_id, year, result) VALUES ('" & versions.SelectedItem.Text & "', '" & p_Region & "', '" & p_Subregion & "', '" & p_CountryFull & "', '" & topicnav.Text & "', '" & topicnav.Value.Split(CChar("#"))(1) & "', '" & topic_navigation_gm.SelectedItem.Text & "', " & CInt(topic_navigation_gm.SelectedItem.Value.Split(CChar("#"))(1)) & ", " & CInt(gm_year1.Text.Split(CChar(" "))(1)) & ", '" & result & "')")
            End If
        End If
    End Sub

    Protected Sub market_save_Click(ByVal sender As Object, ByVal e As EventArgs) Handles market_save.Click

    End Sub

    Protected Sub cc_save_Click(ByVal sender As Object, ByVal e As EventArgs) Handles cc_save.Click

    End Sub
#End Region


#Region "Topic Navigations"
    Protected Sub topic_navigation_gm_ItemClick(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadPanelBarEventArgs) Handles topic_navigation_gm.ItemClick
        If TypeOf e.Item.Owner Is RadPanelBar Then
            'gm_topicheadline.Text = e.Item.Text
            'gm_questionheadline.Visible = False
            'gm_questiondescription.Visible = False
            e.Item.Expanded = True
            e.Item.Items(0).Selected = True

            getQuestionGM(CInt(e.Item.Items(0).Value.Split(CChar("#"))(1)))
        Else
            '         getQuestionGM(CInt(e.Item.Value.Split(CChar("#"))(1)))
        End If
    End Sub

    Protected Sub topic_navigation_market_ItemClick(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadPanelBarEventArgs) Handles topic_navigation_market.ItemClick
        If TypeOf e.Item.Owner Is RadPanelBar Then
            market_topicheadline.Text = e.Item.Text
            market_questionheadline.Visible = False
            market_questiondescription.Visible = False
        Else
            market_topicheadline.Text = e.Item.Owner.Owner.Items.FindItemByValue(e.Item.Value.Split(CChar("."))(0) & ".0#" & e.Item.Value.Split(CChar("_"))(1).Split(CChar("#"))(0), True).Text
            market_questionheadline.Text = e.Item.Text
            market_questionheadline.Visible = True
            market_questiondescription.Visible = True
        End If
    End Sub

    Protected Sub topic_navigation_cc_ItemClick(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadPanelBarEventArgs) Handles topic_navigation_cc.ItemClick
        cc_topicheadline.Text = e.Item.Text
    End Sub
#End Region


#Region "SQL Statements"
    Protected Function getSQLData(ByVal query As String) As DataTable
        Dim connString As String = "Data Source=home-sps2010\POWERPIVOT;Initial Catalog=marketsurvey;Integrated Security=SSPI;"
        Dim conn As New SqlConnection(connString)
        Dim cmd As SqlCommand = New SqlCommand(query, conn)

        conn.Open()

        Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Dim dt As New DataTable

        dt.Load(dr)

        conn.Close()

        Return dt
    End Function

    Protected Sub SaveEditSQLData(ByVal query As String)
        Dim connString As String = "Data Source=home-sps2010\POWERPIVOT;Initial Catalog=marketsurvey;Integrated Security=SSPI;"
        Dim conn As New SqlConnection(connString)
        Dim cmd As SqlCommand = New SqlCommand(query, conn)

        conn.Open()

        cmd.ExecuteNonQuery()

        conn.Close()
    End Sub
#End Region


End Class
