Imports System.Data.OleDb
Imports System.Xml

Public Class FrmContestacionOF

    Private Sub BTImporta_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)




    End Sub

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

        Dim CLasscom As New clsComplejo


        Try

            TXTRuta.Text = CLasscom.BuscaValores("rutaoficios", "paramrep", "") & ""

            If TXTRuta.Text = "" Then
                MsgBox("La Ruta para destino los oficios no esta definida")
            Else
                clsVaribles.strRutaOficios = TXTRuta.Text
            End If

            Me.BTOFCargados.PerformClick()

        Catch ex As Exception

        End Try




    End Sub

    Public Sub CargaExpediente(ByVal ds As DataSet, ByRef SQLExpediente As String)

        Dim rs As New ADODB.Recordset
        Dim con As New ADODB.Connection
        Dim Classin As New clsComplejo
        Dim SQL As String
        Dim Classcom As New clsComplejo

        Dim Fila As DataRow

        Fila = ds.Tables(0).Rows(0)

        SQL = ""
        rs = New ADODB.Recordset
        con.Open("Provider=SQLOLEDB;" & clsVaribles.strCon)

        SQL = "SELECT * FROM OF_Expediente "
        'expediente
        If ds.Tables(0).Rows.Count <> 0 Then

            SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & ds.Tables(0).Rows(0).Item("Cnbv_NumeroOficio") & "'"
            SQL = SQL & " AND Cnbv_NumeroExpediente = '" & ds.Tables(0).Rows(0).Item("Cnbv_NumeroExpediente") & "'"
            If clsVaribles.strTipoArchivo = "xml" Then
                SQL = SQL & " AND Cnbv_SolicitudSiara = '" & ds.Tables(0).Rows(0).Item("Cnbv_SolicitudSiara") & "'"
            End If
            SQL = SQL & " AND Cnbv_Folio = " & ds.Tables(0).Rows(0).Item("Cnbv_Folio")
            SQL = SQL & " AND Cnbv_OficioYear = '" & ds.Tables(0).Rows(0).Item("Cnbv_OficioYear") & "'"

            rs.Open(SQL, con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

            If rs.EOF Then

                rs.AddNew()

                For I As Integer = 0 To ds.Tables(0).Columns.Count - 1

                    If clsVaribles.strTipoArchivo = "xls" And ds.Tables(0).Columns(I).ColumnName <> "Nombre" Then

                        rs.Fields(ds.Tables(0).Columns(I).ColumnName).Value = Fila(ds.Tables(0).Columns(I).ColumnName)
                    End If

                Next

                If clsVaribles.strTipoArchivo = "xls" Then
                    rs.Fields("Cnbv_Solicitudsiara").Value = "0"
                End If

                rs.Update()

            Else


            End If

            rs.Close()
            rs = Nothing
            'expediente

            'llena variables globales
            clsVaribles.Cnbv_NumeroOficio = Fila("Cnbv_NumeroOficio")
            clsVaribles.Cnbv_NumeroExpediente = Fila("Cnbv_NumeroExpediente")
            If clsVaribles.strTipoArchivo = "xml" Then
                clsVaribles.Cnbv_SolicitudSiara = Fila("Cnbv_SolicitudSiara")
            Else
                clsVaribles.Cnbv_SolicitudSiara = "0"
            End If
            clsVaribles.Cnbv_Folio = Fila("Cnbv_Folio")
            clsVaribles.Cnbv_OficioYear = Fila("Cnbv_OficioYear")

            SQLExpediente = SQL

        End If

    End Sub
    Public Sub CargaSecciones(ByVal ds As DataSet, ByVal strTabla As String, ByVal intIndexTabla As Integer, ByRef iSQL As String)

        Dim rs As New ADODB.Recordset
        Dim con As New ADODB.Connection
        Dim Classin As New clsComplejo
        Dim SQL As String
        Dim Classcom As New clsComplejo

        Dim Fila As DataRow

        Try

            SQL = ""
            rs = New ADODB.Recordset
            con.Open("Provider=SQLOLEDB;" & clsVaribles.strCon)

            SQL = "SELECT * FROM " & strTabla
            'expediente
            If ds.Tables(intIndexTabla).Rows.Count <> 0 Then

                SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
                SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
                If clsVaribles.strTipoArchivo = "xml" Then
                    SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
                End If
                SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
                SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

                rs.Open(SQL, con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

                If rs.EOF Then

                    For x As Integer = 0 To ds.Tables(intIndexTabla).Rows.Count - 1
                        Fila = ds.Tables(intIndexTabla).Rows(x)

                        rs.AddNew()

                        If clsVaribles.strTipoArchivo = "xml" Then

                            For I As Integer = 0 To ds.Tables(intIndexTabla).Columns.Count - 1

                                rs.Fields(ds.Tables(intIndexTabla).Columns(I).ColumnName).Value = Fila(ds.Tables(intIndexTabla).Columns(I).ColumnName)

                            Next

                        End If

                        rs.Fields("Cnbv_NumeroOficio").Value = clsVaribles.Cnbv_NumeroOficio
                        rs.Fields("Cnbv_NumeroExpediente").Value = clsVaribles.Cnbv_NumeroExpediente
                        If clsVaribles.strTipoArchivo = "xml" Then
                            rs.Fields("Cnbv_SolicitudSiara").Value = clsVaribles.Cnbv_SolicitudSiara
                        Else
                            rs.Fields("Cnbv_SolicitudSiara").Value = "0"
                        End If
                        rs.Fields("Cnbv_Folio").Value = clsVaribles.Cnbv_Folio
                        rs.Fields("Cnbv_OficioYear").Value = clsVaribles.Cnbv_OficioYear

                        If clsVaribles.strTipoArchivo = "xls" Then
                            rs.Fields("nombre").Value = Fila("nombre").ToString

                        End If

                        rs.Update()

                    Next

                Else


                End If

                rs.Close()
                rs = Nothing
                con.Close()
                'expediente

                iSQL = SQL

            End If

        Catch ex As Exception

        End Try

    End Sub



    Private Sub RDB1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDB1.CheckedChanged
        If RDB1.Checked = True Then
            Me.Panel1.Visible = True
        End If
    End Sub

    Private Sub RDB2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDB2.CheckedChanged
        If RDB2.Checked = True Then
            Me.Panel1.Visible = True
        End If
    End Sub

    Private Sub RDB3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDB3.CheckedChanged
        If RDB3.Checked = True Then
            Me.Panel1.Visible = True
        End If
    End Sub

    Private Sub RDB4_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDB4.CheckedChanged
        If RDB4.Checked = True Then
            Me.Panel1.Visible = True
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTVerificacion.Click

        Dim classcom As New clsComplejo
        Dim classcom2 As New clsComplejo
        Dim SQL As String
        Dim i As Integer

        Try

            SQL = ""

            For i = 0 To Me.GRDPersonas.RowCount - 1
                My.Application.DoEvents()

                GRDPersonas.Row = i
                If clsVaribles.strTipoArchivo = "xml" Then
                    SQL = "SELECT NIC 'NUMERO DE SOCIO'"
                    SQL = SQL & " FROM CLIENTE "
                    SQL = SQL & " WHERE CLIENTENOM = '" & GRDPersonas.Columns.Item("nombre").Text & "'"
                    SQL = SQL & " AND APATERNO = '" & GRDPersonas.Columns.Item("paterno").Text & "'"
                    SQL = SQL & " AND AMATERNO = '" & GRDPersonas.Columns.Item("materno").Text & "'"
                Else
                    SQL = "SELECT NIC 'NUMERO DE SOCIO'"
                    SQL = SQL & " FROM CLIENTE "
                    SQL = SQL & " WHERE ltrim(rtrim(CLIENTENOM)) + ' ' + ltrim(rtrim(apaterno)) + ' ' + ltrim(rtrim(amaterno)) = '" & GRDPersonas.Columns.Item("nombre").Text & "'"
                    'SQL = SQL & " AND APATERNO = '" & GRDPersonas.Columns.Item("paterno").Text & "'"
                    'SQL = SQL & " AND AMATERNO = '" & GRDPersonas.Columns.Item("materno").Text & "'"

                End If

                classcom = New clsComplejo
                classcom.fdtDataSet(SQL)

                If classcom.ds.Tables(0).Rows.Count <> 0 Then

                    For x As Integer = 0 To classcom.ds.Tables(0).Rows.Count - 1

                        SQL = "SELECT * "
                        SQL = SQL & " FROM OF_COINCIDENCIAS "
                        SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
                        SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
                        SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
                        SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
                        SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"
                        SQL = SQL & " AND NIC = '" & classcom.ds.Tables(0).Rows(0).Item("numero de socio").ToString & "'"

                        If Not classcom2.BuscaDatos(SQL) Then

                            SQL = "INSERT INTO OF_COINCIDENCIAS (Cnbv_NumeroOficio,"
                            SQL = SQL & " Cnbv_NumeroExpediente,"
                            SQL = SQL & " Cnbv_SolicitudSiara,"
                            SQL = SQL & " Cnbv_Folio,"
                            SQL = SQL & " Cnbv_OficioYear,"
                            SQL = SQL & " PersonaID,"
                            SQL = SQL & " NIC,"
                            SQL = SQL & " Estatus) "

                            SQL = SQL & " VALUES ("

                            SQL = SQL & "'" & clsVaribles.Cnbv_NumeroOficio & "',"
                            SQL = SQL & "'" & clsVaribles.Cnbv_NumeroExpediente & "',"
                            SQL = SQL & "'" & clsVaribles.Cnbv_SolicitudSiara & "',"
                            SQL = SQL & "'" & clsVaribles.Cnbv_Folio & "',"
                            SQL = SQL & "'" & clsVaribles.Cnbv_OficioYear & "',"
                            If clsVaribles.strTipoArchivo = "xml" Then
                                SQL = SQL & "" & GRDPersonas.Columns.Item("PersonaID").Text & ","
                            Else
                                SQL = SQL & "" & "0,"
                            End If
                            SQL = SQL & "'" & classcom.ds.Tables(0).Rows(0).Item("numero de socio").ToString & "',"
                            SQL = SQL & "'Encontrado')"

                            classcom.sbInserta(SQL)

                            classcom2 = New clsComplejo

                            'ACTUALIZA ESTATUS PERSONAS ENCONTRADAS
                            SQL = "UPDATE OF_PersonasSol SET ESTATUS = 'Encontrado'"
                            SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
                            SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
                            SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
                            SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
                            SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

                            SQL = SQL & " AND NOMBRE = '" & GRDPersonas.Columns.Item("nombre").Text & "'"
                            If clsVaribles.strTipoArchivo = "xml" Then
                                SQL = SQL & " AND PATERNO = '" & GRDPersonas.Columns.Item("paterno").Text & "'"
                                SQL = SQL & " AND MATERNO = '" & GRDPersonas.Columns.Item("materno").Text & "'"
                            End If

                            classcom2.sbInserta(SQL)

                        End If
                    Next

                End If
            Next

            classcom2 = New clsComplejo
            'ACTUALIZA EL ESTATUS DEL EXPEDIENTE

            SQL = "UPDATE OF_Expediente SET Estatus = 'Procesado' "
            SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
            SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
            SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
            SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
            SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

            classcom2.sbInserta(SQL)

            classcom2 = New clsComplejo
            'ACTUALIZA ESTATUS PERSONAS NO ENCONTRADAS
            SQL = "UPDATE OF_PersonasSol SET ESTATUS = 'Procesado'"
            SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
            SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
            'SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
            SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
            SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"
            SQL = SQL & " AND isnull(Estatus,'') = '' "

            classcom2.sbInserta(SQL)

            classcom = New clsComplejo



            SQL = "SELECT a.ESTATUS,A.PersonaID,A.Caracter,A.Persona,A.Paterno,A.Materno,A.Nombre,"
            SQL = SQL & " A.Rfc,A.Relacion,A.Domicilio,A.Complementarios,B.InstruccionesCuentasPorConocer"
            SQL = SQL & " FROM OF_PersonasSol A,OF_SOLICITUDESPECIFICA B"
            SQL = SQL & " WHERE A.SolicitudEspecifica_id = B.SolicitudEspecifica_id"
            SQL = SQL & " and a.Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
            SQL = SQL & " AND a.Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
            'SQL = SQL & " AND a.Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
            SQL = SQL & " AND a.Cnbv_Folio = " & clsVaribles.Cnbv_Folio
            SQL = SQL & " AND a.Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

            SQL = SQL & " and a.Cnbv_NumeroOficio = b.Cnbv_NumeroOficio "
            SQL = SQL & " AND a.Cnbv_NumeroExpediente = b.Cnbv_NumeroExpediente "
            SQL = SQL & " AND a.Cnbv_SolicitudSiara = b.Cnbv_SolicitudSiara "
            SQL = SQL & " AND a.Cnbv_Folio = b.Cnbv_Folio "
            SQL = SQL & " AND a.Cnbv_OficioYear = b.Cnbv_OficioYear "


            If clsVaribles.strTipoArchivo = "xls" Then

                SQL = "SELECT a.ESTATUS,A.PersonaID,A.Caracter,A.Persona,A.Paterno,A.Materno,A.Nombre,"
                SQL = SQL & " A.Rfc,A.Relacion,A.Domicilio,A.Complementarios"
                SQL = SQL & " FROM OF_PersonasSol A"
                SQL = SQL & " WHERE "
                SQL = SQL & " a.Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
                SQL = SQL & " AND a.Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
                'SQL = SQL & " AND a.Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
                SQL = SQL & " AND a.Cnbv_Folio = " & clsVaribles.Cnbv_Folio
                SQL = SQL & " AND a.Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

            End If

            classcom.fdtDataSetTabla(SQL)

            If classcom.ds.Tables(0).Rows.Count <> 0 Then
                Me.GRDPersonas.SetDataBinding(classcom.ds, "tabla")
                classcom.ColsGridAutosize(GRDPersonas)
                Me.GRDPersonas.Splits(0).DisplayColumns(0).FetchStyle = True

            End If

            classcom = New clsComplejo
            'llena coincidencias VALORES
            SQL = "SELECT Cnbv_NumeroOficio,Cnbv_NumeroExpediente,Cnbv_SolicitudSiara,"
            SQL = SQL & " Cnbv_Folio,Cnbv_OficioYear,PersonaID,NIC,Estatus"
            SQL = SQL & " FROM OF_COINCIDENCIAS  "
            SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
            SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
            'SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
            SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
            SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

            classcom.fdtDataSetTabla(SQL)

            If classcom.ds.Tables(0).Rows.Count <> 0 Then
                Me.GRDCoincidencias.SetDataBinding(classcom.ds, "tabla")
                Me.GRDCoincidencias.Caption = "Numero de Coincidnecias " & classcom.ds.Tables(0).Rows.Count

                classcom.ColsGridAutosize(GRDPersonas)
                Me.GRDCoincidencias.Visible = True
                Me.BTContestacion.Visible = True
            Else
                Me.GRDCoincidencias.Caption = "No se Encontraron Coincidencias"
                Me.GRDCoincidencias.Visible = True
                Me.BTContestacion.Visible = True

            End If

            PanelRuta.Visible = True

            If TXTRuta.Text = "" Then
                MsgBox("Es necesario parametrizar la ruta destino del archivo de contestacion")

            End If

        Catch ex As Exception

        End Try

    End Sub

    Private Sub RDB4_CheckedChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDB4.CheckedChanged

    End Sub

    Private Sub RDB3_CheckedChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDB3.CheckedChanged

    End Sub

    Private Sub BTImporta_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTImporta.Click
        Dim dt As New DataTable
        Dim strRuta As String

        Dim SQL As String
        Dim Classcom As New clsComplejo
        Dim Classcom2 As New clsComplejo
        Dim Classcom3 As New clsComplejo
        Dim ds As New DataSet

        Dim oConn As New OleDb.OleDbConnection
        Dim oCmd As New OleDb.OleDbCommand
        Dim oDa As New OleDb.OleDbDataAdapter
        Dim oDs As New DataSet
        Dim SQLExpediente As String



        If clsVaribles.blnBuscaOficion = False Then

            Me.OpenFileDialog1.Filter = "files (*.xml;*.xls)|*.xml;*.xls"
            Me.OpenFileDialog1.FileName = ""
            Me.OpenFileDialog1.ShowDialog()

            strRuta = Me.OpenFileDialog1.FileName

            If strRuta = "" Then Exit Sub

            If InStr(strRuta, ".xls", CompareMethod.Text) <> 0 Then

                clsVaribles.strTipoArchivo = "xls"

                oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                strRuta & "; Extended Properties= Excel 8.0;"

                oConn.Open()

                oCmd.CommandText = "SELECT * FROM [Sheet1$]"
                oCmd.Connection = oConn
                oDa.SelectCommand = oCmd

                oDa.Fill(ds)
                oConn.Close()

            ElseIf InStr(strRuta, ".xml", CompareMethod.Text) <> 0 Then

                clsVaribles.strTipoArchivo = "xml"
                ds.ReadXml(strRuta)

            End If



        End If
        'estatus registros

        'Procesado
        'Encontrado
        'Enviado

        Try

            Windows.Forms.Cursor.Current = Cursors.WaitCursor

            If clsVaribles.blnBuscaOficion = False Then
                Classcom = New clsComplejo
                Call CargaExpediente(ds, SQL)
                Classcom.fdtDataSetTabla(SQL)
                Me.C1TrueDBGrid1.SetDataBinding(Classcom.ds, "tabla")
                PanelRuta.Visible = False
            Else
                If clsVaribles.blnActualizaExpediente = True Then
                    SQL = "SELECT * FROM OF_EXPEDIENTE"
                    SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
                    SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
                    'SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
                    SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
                    SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

                    Classcom = New clsComplejo
                    Classcom.fdtDataSetTabla(SQL)
                    Me.C1TrueDBGrid1.SetDataBinding(Classcom.ds, "tabla")
                    PanelRuta.Visible = True
                Else
                    TXTRuta.Text = Me.C1TrueDBGrid1.Columns.Item("RutaDestino").Text
                    PanelRuta.Visible = True
                End If

            End If

            Classcom.ColsGridAutosize(C1TrueDBGrid1)

            If clsVaribles.strTipoArchivo = "xml" Then

                Classcom = New clsComplejo
                Call CargaSecciones(ds, "OF_SOLICITUDESPECIFICA", 1, SQL)
            End If


            Classcom = New clsComplejo

            If clsVaribles.strTipoArchivo = "xml" Then
                Call CargaSecciones(ds, "OF_PersonasSol", 2, SQL)
            Else
                Call CargaSecciones(ds, "OF_PersonasSol", 0, SQL)
            End If

            SQL = "SELECT a.Estatus,A.PersonaID,A.Caracter,A.Persona,A.Paterno,A.Materno,A.Nombre,"
            SQL = SQL & " A.Rfc,A.Relacion,A.Domicilio,A.Complementarios,B.InstruccionesCuentasPorConocer"
            SQL = SQL & " FROM OF_PersonasSol A,OF_SOLICITUDESPECIFICA B"
            SQL = SQL & " WHERE A.SolicitudEspecifica_id = B.SolicitudEspecifica_id"
            SQL = SQL & " and a.Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
            SQL = SQL & " AND a.Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
            'If clsVaribles.blnBuscaOficion = False Then
            '    SQL = SQL & " AND a.Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
            'End If
            SQL = SQL & " AND a.Cnbv_Folio = " & clsVaribles.Cnbv_Folio
            SQL = SQL & " AND a.Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

            SQL = SQL & " and a.Cnbv_NumeroOficio = b.Cnbv_NumeroOficio "
            SQL = SQL & " AND a.Cnbv_NumeroExpediente = b.Cnbv_NumeroExpediente "
            'If clsVaribles.blnBuscaOficion = False Then
            '    SQL = SQL & " AND a.Cnbv_SolicitudSiara = b.Cnbv_SolicitudSiara "
            'End If
            SQL = SQL & " AND a.Cnbv_Folio = b.Cnbv_Folio "
            SQL = SQL & " AND a.Cnbv_OficioYear = b.Cnbv_OficioYear "

            If clsVaribles.strTipoArchivo = "xls" Then

                SQL = "SELECT a.Estatus,A.PersonaID,A.Caracter,A.Persona,A.Paterno,A.Materno,A.Nombre,"
                SQL = SQL & " A.Rfc,A.Relacion,A.Domicilio,A.Complementarios"
                SQL = SQL & " FROM OF_PersonasSol A"
                SQL = SQL & " WHERE "
                SQL = SQL & " a.Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
                SQL = SQL & " AND a.Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
                'If clsVaribles.blnBuscaOficion = False Then
                '    SQL = SQL & " AND a.Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
                'End If
                SQL = SQL & " AND a.Cnbv_Folio = " & clsVaribles.Cnbv_Folio
                SQL = SQL & " AND a.Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

                'SQL = SQL & " and a.Cnbv_NumeroOficio = b.Cnbv_NumeroOficio "
                'SQL = SQL & " AND a.Cnbv_NumeroExpediente = b.Cnbv_NumeroExpediente "
                'If clsVaribles.blnBuscaOficion = False Then
                '    SQL = SQL & " AND a.Cnbv_SolicitudSiara = b.Cnbv_SolicitudSiara "
                'End If
                'SQL = SQL & " AND a.Cnbv_Folio = b.Cnbv_Folio "
                'SQL = SQL & " AND a.Cnbv_OficioYear = b.Cnbv_OficioYear "

            End If

            Classcom3.fdtDataSetTabla(SQL)

            Me.GRDPersonas.SetDataBinding(Classcom3.ds, "tabla")
            Classcom.ColsGridAutosize(GRDPersonas)

            If Classcom3.ds.Tables(0).Rows.Count <> 0 Then

                Me.BTVerificacion.Visible = True

                If Not IsDBNull(Classcom3.ds.Tables(0).Rows(0).Item("estatus")) Then

                    If Classcom3.ds.Tables(0).Rows(0).Item("estatus").ToString = "Encontrado" Or Classcom3.ds.Tables(0).Rows(0).Item("estatus") = "Procesado" Then
                        Classcom = New clsComplejo
                        'llena coincidencias VALORES
                        SQL = "SELECT Cnbv_NumeroOficio,Cnbv_NumeroExpediente,Cnbv_SolicitudSiara,"
                        SQL = SQL & " Cnbv_Folio,Cnbv_OficioYear,PersonaID,NIC,Estatus"
                        SQL = SQL & " FROM OF_COINCIDENCIAS  "
                        SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
                        SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
                        'If clsVaribles.blnBuscaOficion = False Then
                        '    SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
                        'End If
                        SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
                        SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

                        Classcom.fdtDataSetTabla(SQL)

                        If Classcom.ds.Tables(0).Rows.Count <> 0 Then
                            Me.GRDCoincidencias.SetDataBinding(Classcom.ds, "tabla")
                            Me.GRDCoincidencias.Caption = "Numero de Coincidencias " & Classcom.ds.Tables(0).Rows.Count
                            Classcom.ColsGridAutosize(GRDPersonas)
                            Me.GRDCoincidencias.Visible = True
                            Me.BTContestacion.Visible = True

                            If clsVaribles.strSTOficio = "Enviado" Then
                                Me.TXTRuta.Text = Me.C1TrueDBGrid1.Columns.Item("rutadestino").Text & ""
                                Me.BTContestacion.Enabled = False
                                Me.BTVerificacion.Enabled = False
                            Else
                                TXTRuta.Text = clsVaribles.strRutaOficios
                                Me.BTContestacion.Enabled = True
                                Me.BTVerificacion.Enabled = True
                            End If

                        Else

                            Me.GRDCoincidencias.SetDataBinding(Classcom.ds, "tabla")
                            Me.GRDCoincidencias.Caption = "No se Encontraron Coincidencias"
                            Me.GRDCoincidencias.Visible = True

                            If clsVaribles.strSTOficio = "Enviado" Then
                                Me.TXTRuta.Text = Me.C1TrueDBGrid1.Columns.Item("rutadestino").Text & ""
                                Me.BTContestacion.Enabled = False
                                Me.BTVerificacion.Enabled = False
                                PanelRuta.Visible = True
                            Else
                                TXTRuta.Text = clsVaribles.strRutaOficios
                                Me.BTContestacion.Enabled = True
                                Me.BTVerificacion.Enabled = True
                                PanelRuta.Visible = True
                            End If

                        End If

                    End If
                End If
            Else
                MsgBox("El detalle del Oficio no Existe...Importe la Informacion del Archivo XML")
                Exit Sub
            End If

            If clsVaribles.strTipoArchivo = "xls" Then
                Me.GRDPersonas.Splits(0).DisplayColumns(1).Visible = False
                Me.GRDPersonas.Splits(0).DisplayColumns(2).Visible = False
                Me.GRDPersonas.Splits(0).DisplayColumns(3).Visible = False
                Me.GRDPersonas.Splits(0).DisplayColumns(4).Visible = False
                Me.GRDPersonas.Splits(0).DisplayColumns(5).Visible = False
                Me.GRDPersonas.Splits(0).DisplayColumns(7).Visible = False
                Me.GRDPersonas.Splits(0).DisplayColumns(8).Visible = False
                Me.GRDPersonas.Splits(0).DisplayColumns(9).Visible = False
                Me.GRDPersonas.Splits(0).DisplayColumns(10).Visible = False
            Else
                Me.GRDPersonas.Splits(0).DisplayColumns(1).Visible = True
                Me.GRDPersonas.Splits(0).DisplayColumns(2).Visible = True
                Me.GRDPersonas.Splits(0).DisplayColumns(3).Visible = True
                Me.GRDPersonas.Splits(0).DisplayColumns(4).Visible = True
                Me.GRDPersonas.Splits(0).DisplayColumns(5).Visible = True
                Me.GRDPersonas.Splits(0).DisplayColumns(7).Visible = True
                Me.GRDPersonas.Splits(0).DisplayColumns(8).Visible = True
                Me.GRDPersonas.Splits(0).DisplayColumns(9).Visible = True
                Me.GRDPersonas.Splits(0).DisplayColumns(10).Visible = True
            End If

            Me.GRDPersonas.Splits(0).DisplayColumns(0).FetchStyle = True

            Me.GRDPersonas.Caption = "Detalles del Oficio " & clsVaribles.Cnbv_NumeroOficio

            Me.C1TrueDBGrid1.Visible = True
            Me.GRDPersonas.Visible = True

            clsVaribles.blnBuscaOficion = False

            ds.Clear()

            Windows.Forms.Cursor.Current = Cursors.Default

        Catch ex As Exception
            Windows.Forms.Cursor.Current = Cursors.Default
            clsVaribles.blnBuscaOficion = False
        End Try
    End Sub

    Private Sub BTContestacion_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTContestacion.Click

        Dim StrMFile As System.IO.FileStream
        Dim StrMRead As System.IO.StreamWriter
        Dim strFile As String
        Dim Classcom As New clsComplejo
        Dim SQL As String
        Dim strCadenaIN As String
        Dim Fila As DataRow
        Dim Classcom2 As New clsComplejo

        Try

            'strFile = "c:\Oficio.txt"

            If TXTRuta.Text = "" Then
                MsgBox("Es necesario parametrizar la ruta destino de los oficios")
                Exit Sub
            End If

            strFile = TXTRuta.Text & "\Respuesta_SITI_" & clsVaribles.Cnbv_NumeroExpediente & clsVaribles.Cnbv_Folio & clsVaribles.Cnbv_OficioYear & ".txt"

            'If Dir(strFile) = "" Then

            '    MsgBox("La Ruta Destino del Archivo no Existe ")
            '    Exit Sub

            'End If

            StrMFile = New System.IO.FileStream(strFile, IO.FileMode.OpenOrCreate, IO.FileAccess.Write)
            StrMRead = New System.IO.StreamWriter(StrMFile)

            SQL = "SELECT * FROM OF_PERSONASSOL "

            SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
            SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
            SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
            SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
            SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

            SQL = SQL & " AND isnull(Estatus,'') = 'Procesado'"

            Classcom.fdtDataSet(SQL)

            If Classcom.ds.Tables(0).Rows.Count <> 0 Then

                For I As Integer = 0 To Classcom.ds.Tables(0).Rows.Count - 1
                    Fila = Classcom.ds.Tables(0).Rows(I)

                    'FOLIO
                    strCadenaIN = clsVaribles.Cnbv_Folio
                    strCadenaIN = strCadenaIN & ","
                    'ANO
                    strCadenaIN = strCadenaIN & clsVaribles.Cnbv_OficioYear
                    strCadenaIN = strCadenaIN & ","
                    'OFICIO
                    strCadenaIN = strCadenaIN & clsVaribles.Cnbv_NumeroOficio
                    strCadenaIN = strCadenaIN & ","
                    'EXPEDIENTE
                    strCadenaIN = strCadenaIN & clsVaribles.Cnbv_NumeroExpediente
                    strCadenaIN = strCadenaIN & ","
                    'TIPO DE RESPUESTA
                    strCadenaIN = strCadenaIN & "NO SE LOCALIZO INFORMACION"
                    strCadenaIN = strCadenaIN & ","
                    'CONSECUTIVO
                    strCadenaIN = strCadenaIN & I + 1

                    StrMRead.WriteLine(strCadenaIN)

                Next

            End If

            StrMRead.Close()

            Classcom2 = New clsComplejo
            'ACTUALIZA EL ESTATUS DEL EXPEDIENTE

            SQL = "UPDATE OF_Expediente SET Estatus = 'Enviado',RutaDestino = '" & strFile & "'"
            SQL = SQL & " WHERE Cnbv_NumeroOficio = '" & clsVaribles.Cnbv_NumeroOficio & "'"
            SQL = SQL & " AND Cnbv_NumeroExpediente = '" & clsVaribles.Cnbv_NumeroExpediente & "'"
            'SQL = SQL & " AND Cnbv_SolicitudSiara = '" & clsVaribles.Cnbv_SolicitudSiara & "'"
            SQL = SQL & " AND Cnbv_Folio = " & clsVaribles.Cnbv_Folio
            SQL = SQL & " AND Cnbv_OficioYear = '" & clsVaribles.Cnbv_OficioYear & "'"

            Classcom2.sbInserta(SQL)

            MsgBox("El Archivo de Contestacion fue Generado Existosamente", MsgBoxStyle.Information)

            BTContestacion.Visible = False
            BTLimpiar.PerformClick()

        Catch ex As Exception

        End Try



    End Sub

    Private Sub BTCerrar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTCerrar.Click
        Me.Close()
    End Sub

    Private Sub GRDPersonas_FetchCellStyle(ByVal sender As Object, ByVal e As C1.Win.C1TrueDBGrid.FetchCellStyleEventArgs) Handles GRDPersonas.FetchCellStyle
        Try

            Dim S As String = Me.GRDPersonas(e.Row, e.Col)

            If S = "Encontrado" Then
                e.CellStyle.BackColor = System.Drawing.Color.Red
                e.CellStyle.ForeColor = System.Drawing.Color.White
            End If

            If S = "Procesado" Then
                e.CellStyle.BackColor = System.Drawing.Color.Lime
                e.CellStyle.ForeColor = System.Drawing.Color.Black
            End If

        Catch ex As Exception

        End Try


    End Sub

    Private Sub BTBuscaOf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTBuscaOf.Click
        BTLimpiar.PerformClick()
        PanelBus.Visible = True
    End Sub

    Private Sub BTEjecutar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTEjecutar.Click

        If TXTOficio.Text = "" Then
            MsgBox("El Numero de Oficio es un Dato Requerido")
            TXTOficio.Focus()
            Exit Sub
        End If

        If TXTExp.Text = "" Then
            MsgBox("El Numero de Expediente es un Dato Requerido")
            TXTExp.Focus()
            Exit Sub
        End If

        If TXTYear.Text = "" Then
            MsgBox("El Aņo es un Dato Requerido")
            TXTYear.Focus()
            Exit Sub
        End If

        If TXTFolio.Text = "" Then
            MsgBox("El Folio del Oficio es un Dato Requerido")
            TXTFolio.Focus()
            Exit Sub
        End If


        clsVaribles.Cnbv_NumeroExpediente = Me.TXTExp.Text.Trim
        clsVaribles.Cnbv_NumeroOficio = Me.TXTOficio.Text.Trim
        clsVaribles.Cnbv_OficioYear = Me.TXTYear.Text.Trim
        clsVaribles.Cnbv_Folio = Me.TXTFolio.Text.Trim


        clsVaribles.blnActualizaExpediente = True

        Me.TXTExp.Text = ""
        Me.TXTOficio.Text = ""
        Me.TXTYear.Text = ""
        Me.TXTFolio.Text = ""

        clsVaribles.blnBuscaOficion = True

        Me.BTImporta.PerformClick()

        PanelBus.Visible = False

    End Sub

    Private Sub BTCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTCancel.Click
        clsVaribles.blnBuscaOficion = False
        Me.TXTExp.Text = ""
        Me.TXTOficio.Text = ""
        Me.TXTYear.Text = ""
        Me.TXTFolio.Text = ""
        Me.PanelBus.Visible = False
    End Sub

    Private Sub BTOFCargados_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTOFCargados.Click
        Dim CLasscom As New clsComplejo
        Dim SQL As String

        Try

            Me.BTLimpiar.PerformClick()

            SQL = "SELECT * FROM OF_EXPEDIENTE "
            SQL = SQL & " WHERE (ISNULL(ESTATUS,'') = '' or ISNULL(ESTATUS,'') = 'Procesado')"

            CLasscom = New clsComplejo
            'Call CargaExpediente(ds, SQL)
            CLasscom.fdtDataSetTabla(SQL)
            Me.C1TrueDBGrid1.SetDataBinding(CLasscom.ds, "tabla")

            Me.C1TrueDBGrid1.Visible = True


            If CLasscom.ds.Tables(0).Rows.Count = 0 Then

                MsgBox("No existen oficios Capturados")
                Exit Sub

            End If


        Catch ex As Exception

        End Try

    End Sub

    Private Sub C1TrueDBGrid1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles C1TrueDBGrid1.Click

    End Sub

    Private Sub C1TrueDBGrid1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles C1TrueDBGrid1.DoubleClick

        Try

            clsVaribles.Cnbv_Folio = Me.C1TrueDBGrid1.Columns("Cnbv_Folio").Text
            clsVaribles.Cnbv_NumeroExpediente = Me.C1TrueDBGrid1.Columns("Cnbv_NumeroExpediente").Text
            clsVaribles.Cnbv_NumeroOficio = Me.C1TrueDBGrid1.Columns("Cnbv_NumeroOficio").Text
            clsVaribles.Cnbv_OficioYear = Me.C1TrueDBGrid1.Columns("Cnbv_OficioYear").Text

            clsVaribles.strSTOficio = Me.C1TrueDBGrid1.Columns("estatus").Text & ""
            clsVaribles.blnActualizaExpediente = False



            clsVaribles.blnBuscaOficion = True
            Me.BTImporta.PerformClick()

        Catch ex As Exception

        End Try

    End Sub

    Private Sub BTLimpiar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTLimpiar.Click

        Me.C1TrueDBGrid1.DataSource = Nothing
        Me.GRDCoincidencias.DataSource = Nothing
        Me.GRDPersonas.DataSource = Nothing

        Me.BTVerificacion.Visible = False
        Me.BTContestacion.Visible = False

        Me.C1TrueDBGrid1.Visible = False
        Me.GRDCoincidencias.Visible = False
        Me.GRDPersonas.Visible = False

        PanelRuta.Visible = False

    End Sub

    Private Sub BTEnviados_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTEnviados.Click
        Dim CLasscom As New clsComplejo
        Dim SQL As String

        Try

            Me.BTLimpiar.PerformClick()

            SQL = "SELECT * FROM OF_EXPEDIENTE "
            SQL = SQL & " WHERE ISNULL(ESTATUS,'') = 'Enviado'"

            CLasscom = New clsComplejo
            'Call CargaExpediente(ds, SQL)
            CLasscom.fdtDataSetTabla(SQL)
            Me.C1TrueDBGrid1.SetDataBinding(CLasscom.ds, "tabla")

            Me.C1TrueDBGrid1.Visible = True


            If CLasscom.ds.Tables(0).Rows.Count = 0 Then

                MsgBox("No existen oficios Enviados")
                Exit Sub

            End If


        Catch ex As Exception

        End Try
    End Sub

    Private Sub BTRutaInusuales_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub BTRutaInusuales_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTRutaInusuales.Click
        Dim Classcom As New clsComplejo
        FolderBrowserDialog.ShowDialog()
        TXTRuta.Text = FolderBrowserDialog.SelectedPath

        Classcom.sbInserta("UPDATE paramrep set rutaoficios = '" & TXTRuta.Text & "'")

    End Sub

    Private Sub TXTYear_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TXTYear.KeyPress
        Dim classCom As New clsComplejo
        e.KeyChar = classCom.ValidaCampos("numeros", e.KeyChar)
    End Sub

    Private Sub TXTYear_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TXTYear.TextChanged

    End Sub
End Class