Imports System.Data.SqlClient
Imports System.Data.Common
Public Class Get_premise
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Dim conn1 As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Dim cmd As SqlCommand = Nothing
    Public Sub New(ByVal house_no As String, ByVal ADDR_PREFIX_1 As String, ByVal ADDR_PREFIX_2 As String, ByVal STREET_NM As String, ByVal ADDR_SUFFIX_CD As String, ByVal STREET_SUFFIX_CD As String, ByVal TOWN_NM As String)
        InitializeComponent()
        load_ddls()
        cmb_1_city.Text = TOWN_NM
        txt_1_add1.Text = house_no
        cmb_1_add2.Text = ADDR_PREFIX_1
        cmb_1_add2_1.Text = ADDR_PREFIX_2
        cmb_1_add3.Text = STREET_NM
        cmb_1_add4_1.Text = ADDR_SUFFIX_CD
        cmb_1_add4.Text = STREET_SUFFIX_CD


        get_premiseno_CITY(Me.txt_1_add1.Text.Trim, Me.cmb_1_add2.Text.Trim, Me.cmb_1_add2_1.Text.Trim, cmb_1_add3.Text.Trim, cmb_1_add4.Text.Trim, Me.cmb_1_city.Text.Trim)
    End Sub


    Private Sub Get_premise_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'load_ddls()
      
    End Sub
    Private Sub Fill_ComboFields(ByVal comboField As ComboBox, ByVal cmdText As String)
        'comboField.Items.Clear()
        Try
            Dim SQLCmd As New SqlCommand
            SQLCmd.CommandText = "Fill_combo_clientapp " ' Stored Procedure to fill combo
            SQLCmd.CommandType = CommandType.StoredProcedure
            SQLCmd.Connection = conn1
            SQLCmd.Parameters.AddWithValue("searchtext", cmdText)


            If (conn1.State = ConnectionState.Closed) Then
                conn1.Open()
            End If
            Dim ds As New DataSet()
            ds.Tables.Clear()
            Dim da As New SqlDataAdapter(SQLCmd)
            da.Fill(ds)
            comboField.DataSource = ds.Tables(0)
            comboField.DisplayMember = "t_values"
            comboField.ValueMember = "t_values"
            comboField.SelectedIndex = 0
        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            conn1.Close()
            cmd = Nothing
        End Try
    End Sub
    Private Sub load_ddls()


        Fill_ComboFields(cmb_1_add2, "ADDR_PREFIX_1")
        Fill_ComboFields(cmb_1_add2_1, "ADDR_PREFIX_2")
        Fill_ComboFields(cmb_1_add4_1, "ADDR_SUFFIX_CD")
        Fill_ComboFields(cmb_1_add4, "STREET_SUFFIX_CD")
        Fill_ComboFields(cmb_1_city, "TOWN_NM")

    End Sub

    Private Sub load_streetname(ByVal comboField As ComboBox, ByVal CITY As String)

        Try
            If (conn.State = ConnectionState.Closed) Then
                conn.Open()
            End If
            Dim SQLCmd As New SqlCommand
            SQLCmd.CommandText = "get_streetname_city " ' Stored Procedure to get street
            SQLCmd.CommandType = CommandType.StoredProcedure
            SQLCmd.Connection = conn1
            SQLCmd.Parameters.AddWithValue("city", CITY.Trim())

            Dim ds As New DataSet()
            ds.Tables.Clear()
            Dim da As New SqlDataAdapter(SQLCmd)
            da.Fill(ds)
            comboField.DataSource = ds.Tables(0)
            comboField.DisplayMember = "street_nm"
            comboField.ValueMember = "street_nm"
            comboField.SelectedIndex = -1
        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            conn.Close()
        End Try
    End Sub

    Private Sub cmb_1_city_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_1_city.SelectedIndexChanged
        If cmb_1_city.Text = "System.Data.DataRowView" Then
            Exit Sub
        End If
        If cmb_1_city.SelectedIndex < 0 Then

            Exit Sub
        End If
        load_streetname(cmb_1_add3, cmb_1_city.Text)
    End Sub

    Private Sub btn_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Search.Click
        get_premiseno_CITY(Me.txt_1_add1.Text.Trim, Me.cmb_1_add2.Text.Trim, Me.cmb_1_add2_1.Text.Trim, cmb_1_add3.Text.Trim, cmb_1_add4.Text.Trim, Me.cmb_1_city.Text.Trim)
    End Sub

    Private Sub get_premiseno_CITY(ByVal HOUSE_NO As String, ByVal ADDR_PREFIX_1 As String, ByVal ADDR_PREFIX_2 As String, ByVal STREET_NM As String, ByVal STREET_SUFFIX_CD As String, ByVal TOWN_NM As String)
        Dim PREMISE_NO As String
        If conn.State = 0 Then
            conn.Open()
        End If

        If HOUSE_NO.Trim.Length = 0 Then
            ' MsgBox("Address does not matches. Check it again.", MsgBoxStyle.Information, "Address Exception")
            PREMISE_NO = ""

            Exit Sub
            'house = " HOUSE_NO is NULL "
        Else
            'house = " HOUSE_NO = '" & HOUSE_NO & "'"
        End If

        Dim dr As SqlDataReader = Nothing

        Dim SQLCmd As New SqlCommand
        SQLCmd.CommandText = "Get_Add_Premise" ' Stored Procedure
        SQLCmd.CommandType = CommandType.StoredProcedure
        SQLCmd.Connection = conn
        SQLCmd.Parameters.AddWithValue("HOUSE_NO", HOUSE_NO.Trim)
        SQLCmd.Parameters.AddWithValue("ADDR_PREFIX_1", ADDR_PREFIX_1.Trim)
        SQLCmd.Parameters.AddWithValue("ADDR_PREFIX_2", ADDR_PREFIX_2.Trim)
        SQLCmd.Parameters.AddWithValue("STREET_NM", STREET_NM.Trim)
        SQLCmd.Parameters.AddWithValue("STREET_SUFFIX_CD", STREET_SUFFIX_CD.Trim)
        SQLCmd.Parameters.AddWithValue("TOWN_NM", TOWN_NM.Trim)
        'SQLCmd.Parameters.AddWithValue("divison", txt_div.Text.Trim)


        ' dr = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection)
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(SQLCmd)
            da.Fill(ds)
            gdv_overall.DataSource = ds.Tables(0)
            gdv_overall.Columns(2).Width = 90
            gdv_overall.Columns(3).Width = 60
            gdv_overall.Columns(4).Width = 60
            gdv_overall.Columns(6).Width = 60
            gdv_overall.Columns(7).Width = 60
            gdv_overall.Columns(8).Width = 60
            gdv_overall.Columns(9).Width = 60

        Catch ex As Exception
        Finally
            If conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        End Try


    End Sub

    Private Sub gdv_overall_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gdv_overall.CellDoubleClick
        If gdv_overall.Rows.Count = 0 Then
            Exit Sub
        End If
        If gdv_overall.SelectedRows Is Nothing Then
            Exit Sub
        End If

        If IsDBNull(gdv_overall.SelectedCells(0).Value) Then
            Exit Sub
        End If


        Dim premise_no As String = ""
        Dim house_no As String = ""
        Dim ADDR_PREFIX_1 As String = ""
        Dim ADDR_PREFIX_2 As String = ""
        Dim STREET_NM As String = ""
        Dim ADD_SUFFIX_CD As String = ""
        Dim STREET_SUFFIX_CD As String = ""

        Dim STREET_LOCATION_1_CD As String = ""
        Dim STREET_LOCATION_2_CD As String = ""

        Dim TOWN_NM As String = ""

        Dim remark As String = ""

        premise_no = gdv_overall.SelectedCells(0).Value

        If Not IsDBNull(gdv_overall.SelectedCells(1).Value) Then
            TOWN_NM = gdv_overall.SelectedCells(1).Value
        End If

        If Not IsDBNull(gdv_overall.SelectedCells(2).Value) Then
            house_no = gdv_overall.SelectedCells(2).Value
        End If

        If Not IsDBNull(gdv_overall.SelectedCells(3).Value) Then
            ADDR_PREFIX_1 = gdv_overall.SelectedCells(3).Value
        End If
        If Not IsDBNull(gdv_overall.SelectedCells(4).Value) Then
            ADDR_PREFIX_2 = gdv_overall.SelectedCells(4).Value
        End If
        If Not IsDBNull(gdv_overall.SelectedCells(5).Value) Then
            STREET_NM = gdv_overall.SelectedCells(5).Value
        End If
        If Not IsDBNull(gdv_overall.SelectedCells(6).Value) Then
            STREET_LOCATION_1_CD = gdv_overall.SelectedCells(6).Value
        End If
        If Not IsDBNull(gdv_overall.SelectedCells(7).Value) Then
            STREET_LOCATION_2_CD = gdv_overall.SelectedCells(7).Value
        End If
        If Not IsDBNull(gdv_overall.SelectedCells(8).Value) Then
            ADD_SUFFIX_CD = gdv_overall.SelectedCells(8).Value
        End If

        If Not IsDBNull(gdv_overall.SelectedCells(9).Value) Then
            STREET_SUFFIX_CD = gdv_overall.SelectedCells(9).Value
        End If

        remark = STREET_LOCATION_1_CD & " " & STREET_LOCATION_2_CD

        If Not remark = " " Then
            frm_dataentry.cmb_loccd_1.Text = STREET_LOCATION_1_CD
            frm_dataentry.txt_loccd_2.Text = STREET_LOCATION_2_CD
            ' remark = remark & "-"
        End If
        If frm_dataentry.cmb_formtype.Text.Trim = "Yellow" Then
            frm_dataentry.txt_y_add1.Text = house_no
            frm_dataentry.cmb_y_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_y_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_y_add3.Text = STREET_NM
            frm_dataentry.cmb_y_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_y_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_y_city.Text = TOWN_NM
            frm_dataentry.txt_y_premiseno.Text = premise_no
            '  frm_dataentry.txt_y_remark.Text = remark & frm_dataentry.txt_y_remark.Text

            'FORM-100BLK
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "FORM-100BLK" Then
            frm_dataentry.txt_100b_add1.Text = house_no
            frm_dataentry.cmb_100b_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_100b_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_100b_add3.Text = STREET_NM
            frm_dataentry.cmb_100b_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_100b_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_100b_city.Text = TOWN_NM
            frm_dataentry.txt_100b_premiseno.Text = premise_no

            'FORM-100BLK-MISC
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "FORM-100BLK-MISC" Then
            frm_dataentry.txt_100b_m_add1.Text = house_no
            frm_dataentry.cmb_100b_m_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_100b_m_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_100b_m_add3.Text = STREET_NM
            frm_dataentry.cmb_100b_m_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_100b_m_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_100b_m_city.Text = TOWN_NM
            frm_dataentry.txt_100b_m_premiseno.Text = premise_no

            'FORM-200BLK N
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "FORM-200BLK" Then
            frm_dataentry.txthouseNo1.Text = house_no
            frm_dataentry.cmbPrefix11.Text = ADDR_PREFIX_1
            frm_dataentry.cmbPrefix22.Text = ADDR_PREFIX_2
            frm_dataentry.cmbStreetname1.Text = STREET_NM
            frm_dataentry.cmbsuffix12.Text = ADD_SUFFIX_CD
            frm_dataentry.cmbsuffix22.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_200b_city.Text = TOWN_NM
            frm_dataentry.txt_200b_premiseno.Text = premise_no

        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "ORIGINAL" Then
            frm_dataentry.txt_q_add1.Text = house_no
            frm_dataentry.cmb_q_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_q_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_q_add3.Text = STREET_NM
            frm_dataentry.cmb_q_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_q_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_q_city.Text = TOWN_NM
            frm_dataentry.txt_q_premiseno.Text = premise_no
            ' frm_dataentry.txt_q_remark.Text = remark & frm_dataentry.txt_q_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "MISC" Then
            frm_dataentry.txt_l_add1.Text = house_no
            frm_dataentry.cmb_l_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_l_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_l_add3.Text = STREET_NM
            frm_dataentry.cmb_l_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_l_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_l_city.Text = TOWN_NM
            frm_dataentry.txt_l_premiseno.Text = premise_no
            '  frm_dataentry.txt_l_remark.Text = remark & frm_dataentry.txt_l_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "ORIGINAL-R" Then
            frm_dataentry.txt_r_add1.Text = house_no
            frm_dataentry.cmb_r_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_r_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_r_add3.Text = STREET_NM
            frm_dataentry.cmb_r_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_r_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_r_city.Text = TOWN_NM
            frm_dataentry.txt_r_premiseno.Text = premise_no
            ' frm_dataentry.txt_r_remark.Text = remark & frm_dataentry.txt_r_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "ABANDON-II" Then
            frm_dataentry.txt_a_add1.Text = house_no
            frm_dataentry.cmb_a_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_a_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_a_add3.Text = STREET_NM
            frm_dataentry.cmb_a_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_a_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_a_city.Text = TOWN_NM
            frm_dataentry.txt_a_premiseno.Text = premise_no
            ' frm_dataentry.txt_a_remark.Text = remark & frm_dataentry.txt_a_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "Form-X" Then
            frm_dataentry.txt_x_add1.Text = house_no
            frm_dataentry.cmb_x_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_x_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_x_add3.Text = STREET_NM
            frm_dataentry.cmb_x_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_x_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_x_city.Text = TOWN_NM
            frm_dataentry.txt_x_premiseno.Text = premise_no
            ' frm_dataentry.txt_x_remark.Text = remark & frm_dataentry.txt_x_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "FORM-IX" Then
            frm_dataentry.txt_9_add1.Text = house_no
            frm_dataentry.cmb_9_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_9_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_9_add3.Text = STREET_NM
            frm_dataentry.cmb_9_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_9_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_9_city.Text = TOWN_NM
            frm_dataentry.txt_9_premiseno.Text = premise_no
            '  frm_dataentry.txt_9_remark.Text = remark & frm_dataentry.txt_9_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "Form-954" Then
            frm_dataentry.txt_7_add1.Text = house_no
            frm_dataentry.cmb_7_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_7_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_7_add3.Text = STREET_NM
            frm_dataentry.cmb_7_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_7_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_7_city.Text = TOWN_NM
            frm_dataentry.txt_7_premiseno.Text = premise_no
            '  frm_dataentry.txt_7_remark.Text = remark & frm_dataentry.txt_7_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "ABANDON-I" Then
            frm_dataentry.txt_8_add1.Text = house_no
            frm_dataentry.cmb_8_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_8_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_8_add3.Text = STREET_NM
            frm_dataentry.cmb_8_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_8_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_8_city.Text = TOWN_NM
            frm_dataentry.txt_8_premiseno.Text = premise_no
            'frm_dataentry.txt_8_remark.Text = remark & frm_dataentry.txt_8_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "Form-80I" Then
            frm_dataentry.txt_6_add1.Text = house_no
            frm_dataentry.cmb_6_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_6_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_6_add3.Text = STREET_NM
            frm_dataentry.cmb_6_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_6_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_6_city.Text = TOWN_NM
            frm_dataentry.txt_6_premiseno.Text = premise_no
            ' frm_dataentry.txt_6_remark.Text = remark & frm_dataentry.txt_6_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "OD-9-6-38" Then
            frm_QAnVerify.txt_5_add1.Text = house_no
            frm_QAnVerify.cmb_5_add2.Text = ADDR_PREFIX_1
            frm_QAnVerify.cmb_5_add2_1.Text = ADDR_PREFIX_2
            frm_QAnVerify.cmb_5_add3.Text = TOWN_NM
            frm_QAnVerify.cmb_5_add4_1.Text = ADD_SUFFIX_CD
            frm_QAnVerify.cmb_5_add4.Text = STREET_SUFFIX_CD
            frm_QAnVerify.cmb_5_city.Text = TOWN_NM
            frm_QAnVerify.txt_5_premiseno.Text = premise_no
            ' frm_dataentry.txt_5_remark.Text = remark & frm_dataentry.txt_5_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "Form235-B-702" Then
            frm_dataentry.txt_4_add1.Text = house_no
            frm_dataentry.cmb_4_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_4_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_4_add3.Text = STREET_NM
            frm_dataentry.cmb_4_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_4_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_4_city.Text = TOWN_NM
            frm_dataentry.txt_4_premiseno.Text = premise_no
            'frm_dataentry.txt_4_remark.Text = remark & frm_dataentry.txt_4_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "White" Then
            frm_dataentry.txt_w_add1.Text = house_no
            frm_dataentry.cmb_w_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_w_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_w_add3.Text = STREET_NM
            frm_dataentry.cmb_w_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_w_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_w_city.Text = TOWN_NM
            frm_dataentry.txt_w_premiseno.Text = premise_no
            'frm_dataentry.txt_w_remark.Text = remark & frm_dataentry.txt_w_remark.Text
        ElseIf frm_dataentry.cmb_formtype.Text.Trim = "Orange" Then
            frm_dataentry.txt_3_add1.Text = house_no
            frm_dataentry.cmb_3_add2.Text = ADDR_PREFIX_1
            frm_dataentry.cmb_3_add2_1.Text = ADDR_PREFIX_2
            frm_dataentry.cmb_3_add3.Text = STREET_NM
            frm_dataentry.cmb_3_add4_1.Text = ADD_SUFFIX_CD
            frm_dataentry.cmb_3_add4.Text = STREET_SUFFIX_CD
            frm_dataentry.cmb_3_city.Text = TOWN_NM
            frm_dataentry.txt_3_premiseno.Text = premise_no
            ' frm_dataentry.txt_3_remark.Text = remark & frm_dataentry.txt_3_remark.Text
        End If

        Me.Close()
    End Sub
End Class