Imports System.Data.SqlClient
Public Class frm_submit
    Dim cmd As SqlCommand = Nothing
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Private Sub frm_submit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        max_min()
        date_select()
        '  FillGrid()
        If conn.State <> ConnectionState.Closed Then
            conn.Close()
        End If
    End Sub
    Private Sub FillGrid()
        txt_total.Text = ""
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim ssql As String
            Dim cmdtext As String
            If rbt_time.Checked = True Then
                cmdtext = "SO_FrmSubmitFillgrid"
                cmd = Nothing
                cmd = New SqlCommand(cmdtext, conn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@dtpFromDate", Me.dtpFromDate.Text)
                cmd.Parameters.AddWithValue("@dtpToDate", Me.dtpToDate.Text)
                'ssql = "select ts.file_id as [ID], ts.file_name as [File], ts.add_streetno + ' ' + ts.add_prefix + ' ' + ts.add_streetname + ' ' + ts.add_suffix as [Address], ts.city as City, last_remark as [Remark] from tbl_servicewo ts join tbl_parent tp on ts.file_id = tp.file_id where tp.status not in ('N','A','S') and (tp.dataentry_date between '" & dtpFromDate.Text & "' and '" & dtpToDate.Text & "' ) order by ts.file_id DESC"
                'ssql = "select distinct pt.file_type [Division], p2.foldername, p1.foldername, p0.foldername,  b.datecreated, [add_streetno] ,[add_prefix],[add_prefix_2],[add_streetname],[add_suffix],[street_suffix_cd],wo.city,[PREMISE_NO],'http://filenetweb/AddressableURL/FNAddressableURL.aspx?Lib=GasOperations&DocClass=ServiceCards&Prop=Service Order&PropVal=' + b.service_order + '&Open=True' [URL] ,wo.name ,[date_installed],[subdivision],[service_wo_no],[pipe_size],[pipe_type],[pressure],[size_of_tap],[length_main_to_curb_valve_ft],[length_main_to_curb_valve_in],[length_curb_valve_to_property_line_ft],[length_curb_valve_to_property_line_in],[length_main_to_property_line_ft],[length_main_to_property_line_in],[length_property_line_to_meter_ft],[length_property_line_to_meter_in],[size_of_main],[type_of_main],[depth_of_main],[tested_with],[tested_to],[psig_for_minutes],[installed_by_contractor],[foreman],[remark],[f_stub],[f_inserted],[f_retired],[f_leak],[f_split],[pipe_condition],[lenmtrtomain],[sceginspect],[nature_of_work],[kind_of_joint],[split_sleeve],[loc_of_cock],[kind_of_box],[type_coating],[DATE_ABANDON],[reason_of_abandon],[f_noblow],[f_gassource],[f_tappingtee],[f_linesealed], [f_defcoatcor], [f_multimeter], [f_serstop], [f_cathodpro],[f_tee], [f_taptee], [f_galvanized], [tap_t_size], [tap_t_inlet], [tap_t_outlet],[f_insulated], [f_anoderiser], [otheranode_lbs], [connection_data], [misc_card_type], f_relocate, f_replace, pt.file_id from dbo.tbl_so_imagefiles a, dbo.tbl_servicecards b, dbo.tbl_relations c, dbo.tbl_folders p0, dbo.tbl_folders p1, dbo.tbl_folders p2 ,dbo.tbl_parent pt, dbo.tbl_servicewo wo where a.id = b.id and c.head_objectid = b.id and p0.id = c.tail_objectid and p0.parent_objectid = p1.id and p1.parent_objectid = p2.id and b.service_order = pt.service_order and pt.file_id = wo.file_id and (pt.dataentry_date between '" & dtpFromDate.Text & "' and '" & dtpToDate.Text & "' ) order by b.datecreated"
            Else
                'ssql = "select ts.file_id as [ID], ts.file_name as [File], ts.add_streetno + ' ' + ts.add_prefix + ' ' + ts.add_streetname + ' ' + ts.add_suffix as [Address], ts.city as City, last_remark as [Remark] from tbl_servicewo ts join tbl_parent tp on ts.file_id = tp.file_id where tp.status not in ('N','A','S') and (ts.file_id between " & Me.txt_sfrom.Text & " and " & txt_sto.Text & ") order by ts.file_id DESC"
                'ssql = "select distinct pt.file_type [Division], p2.foldername, p1.foldername, p0.foldername,  b.datecreated, [add_streetno] ,[add_prefix],[add_prefix_2],[add_streetname],[add_suffix],[street_suffix_cd],wo.city,[PREMISE_NO],'http://filenetweb/AddressableURL/FNAddressableURL.aspx?Lib=GasOperations&DocClass=ServiceCards&Prop=Service Order&PropVal=' + b.service_order + '&Open=True' [URL] ,wo.name ,[date_installed],[subdivision],[service_wo_no],[pipe_size],[pipe_type],[pressure],[size_of_tap],[length_main_to_curb_valve_ft],[length_main_to_curb_valve_in],[length_curb_valve_to_property_line_ft],[length_curb_valve_to_property_line_in],[length_main_to_property_line_ft],[length_main_to_property_line_in],[length_property_line_to_meter_ft],[length_property_line_to_meter_in],[size_of_main],[type_of_main],[depth_of_main],[tested_with],[tested_to],[psig_for_minutes],[installed_by_contractor],[foreman],[remark],[f_stub],[f_inserted],[f_retired],[f_leak],[f_split],[pipe_condition],[lenmtrtomain],[sceginspect],[nature_of_work],[kind_of_joint],[split_sleeve],[loc_of_cock],[kind_of_box],[type_coating],[DATE_ABANDON],[reason_of_abandon],[f_noblow],[f_gassource],[f_tappingtee],[f_linesealed], [f_defcoatcor], [f_multimeter], [f_serstop], [f_cathodpro],[f_tee], [f_taptee], [f_galvanized], [tap_t_size], [tap_t_inlet], [tap_t_outlet],[f_insulated], [f_anoderiser], [otheranode_lbs], [connection_data], [misc_card_type], f_relocate, f_replace, pt.file_id from dbo.tbl_so_imagefiles a, dbo.tbl_servicecards b, dbo.tbl_relations c, dbo.tbl_folders p0, dbo.tbl_folders p1, dbo.tbl_folders p2 ,dbo.tbl_parent pt, dbo.tbl_servicewo wo where a.id = b.id and c.head_objectid = b.id and p0.id = c.tail_objectid and p0.parent_objectid = p1.id and p1.parent_objectid = p2.id and b.service_order = pt.service_order and pt.file_id = wo.file_id and ((pt.file_id between " & Me.txt_sfrom.Text & " and " & txt_sto.Text & ") ) order by b.datecreated"
                cmdtext = "SO_FrmSubmitFillgrid1"
                cmd = Nothing
                cmd = New SqlCommand(cmdtext, conn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@sfrom", Me.txt_sfrom.Text)
                cmd.Parameters.AddWithValue("@sto", txt_sto.Text)
            End If
            Me.Cursor = Cursors.WaitCursor
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter(cmd)
            da.SelectCommand.CommandTimeout = 5000
            da.Fill(ds)
            gdv_tosubmitdata.DataSource = ds.Tables(0)
        Catch ex As Exception
        Finally
            Cursor = Cursors.Default

        End Try
    End Sub
    Private Sub rbt_time_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbt_time.CheckedChanged
        If rbt_time.Checked = True Then
            lbl_21.Visible = True
            dtpFromDate.Visible = True
            lbl_22.Visible = True
            dtpToDate.Visible = True
            lbl_11.Visible = False
            txt_sfrom.Visible = False
            lbl_12.Visible = False
            txt_sto.Visible = False
        Else
            lbl_11.Visible = True
            txt_sfrom.Visible = True
            lbl_12.Visible = True
            txt_sto.Visible = True
            lbl_21.Visible = False
            dtpFromDate.Visible = False
            lbl_22.Visible = False
            dtpToDate.Visible = False
        End If
    End Sub
    Private Sub max_min()
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        'cmd = New SqlCommand("select max(file_id), min(file_id) from tbl_parent where status not in ('N','A','S') ", conn)
        Dim cmdtext As String = "SO_FrmSubmit_MaxMin"
        cmd = Nothing
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then

            Me.txt_sto.Text = dr(0).ToString
            Me.txt_sfrom.Text = dr(1).ToString
        End If
        dr.Close()
    End Sub
    Private Sub date_select()
        dtpFromDate.Text = Date.Today.Date.AddDays(-1)
        dtpToDate.Text = Date.Today.Date.AddDays(0)
    End Sub
    Private Sub btn_choose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_choose.Click
        FillGrid()
        txt_total.Text = Me.gdv_tosubmitdata.RowCount - 1
    End Sub
    Private Sub dtpToDate_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpToDate.ValueChanged, dtpFromDate.ValueChanged, txt_sfrom.TextChanged, txt_sto.TextChanged
        ' FillGrid()
    End Sub

    Private Sub btn_createcsv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_createcsv.Click

    End Sub

    Private Sub btn_reset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_reset.Click
        max_min()
        date_select()
        txt_total.Text = ""
    End Sub
End Class