Public Class PersonasBol
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents tlbInusualesDivisas As System.Windows.Forms.ToolBar
    Friend WithEvents btnConsultar As System.Windows.Forms.ToolBarButton
    Friend WithEvents btnExportar As System.Windows.Forms.ToolBarButton
    Friend WithEvents btnSalir As System.Windows.Forms.ToolBarButton
    Friend WithEvents ImageList1 As System.Windows.Forms.ImageList
    Friend WithEvents GroupBox2 As System.Windows.Forms.GroupBox
    Friend WithEvents cmbOrganismo As System.Windows.Forms.ComboBox
    Friend WithEvents cmbPais As System.Windows.Forms.ComboBox
    Friend WithEvents txtPersona As System.Windows.Forms.TextBox
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents btnRefrescar As System.Windows.Forms.ToolBarButton
    Friend WithEvents lblResultados As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(PersonasBol))
        Me.GroupBox1 = New System.Windows.Forms.GroupBox
        Me.txtPersona = New System.Windows.Forms.TextBox
        Me.cmbPais = New System.Windows.Forms.ComboBox
        Me.Label3 = New System.Windows.Forms.Label
        Me.Label2 = New System.Windows.Forms.Label
        Me.cmbOrganismo = New System.Windows.Forms.ComboBox
        Me.Label1 = New System.Windows.Forms.Label
        Me.tlbInusualesDivisas = New System.Windows.Forms.ToolBar
        Me.btnConsultar = New System.Windows.Forms.ToolBarButton
        Me.btnExportar = New System.Windows.Forms.ToolBarButton
        Me.btnRefrescar = New System.Windows.Forms.ToolBarButton
        Me.btnSalir = New System.Windows.Forms.ToolBarButton
        Me.ImageList1 = New System.Windows.Forms.ImageList(Me.components)
        Me.GroupBox2 = New System.Windows.Forms.GroupBox
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.lblResultados = New System.Windows.Forms.Label
        Me.GroupBox1.SuspendLayout()
        Me.GroupBox2.SuspendLayout()
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'GroupBox1
        '
        Me.GroupBox1.Controls.Add(Me.txtPersona)
        Me.GroupBox1.Controls.Add(Me.cmbPais)
        Me.GroupBox1.Controls.Add(Me.Label3)
        Me.GroupBox1.Controls.Add(Me.Label2)
        Me.GroupBox1.Controls.Add(Me.cmbOrganismo)
        Me.GroupBox1.Controls.Add(Me.Label1)
        Me.GroupBox1.Location = New System.Drawing.Point(28, 55)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(500, 67)
        Me.GroupBox1.TabIndex = 0
        Me.GroupBox1.TabStop = False
        Me.GroupBox1.Text = "Parametros de busqueda"
        '
        'txtPersona
        '
        Me.txtPersona.Location = New System.Drawing.Point(292, 32)
        Me.txtPersona.Name = "txtPersona"
        Me.txtPersona.Size = New System.Drawing.Size(188, 20)
        Me.txtPersona.TabIndex = 4
        '
        'cmbPais
        '
        Me.cmbPais.DropDownWidth = 210
        Me.cmbPais.Location = New System.Drawing.Point(156, 32)
        Me.cmbPais.Name = "cmbPais"
        Me.cmbPais.Size = New System.Drawing.Size(121, 21)
        Me.cmbPais.TabIndex = 3
        '
        'Label3
        '
        Me.Label3.Location = New System.Drawing.Point(292, 16)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(106, 16)
        Me.Label3.TabIndex = 2
        Me.Label3.Text = "Persona o empresa"
        Me.Label3.ForeColor = System.Drawing.Color.MidnightBlue
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(156, 18)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(28, 16)
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "Pais"
        Me.Label2.ForeColor = System.Drawing.Color.MidnightBlue
        '
        'cmbOrganismo
        '
        Me.cmbOrganismo.DropDownWidth = 140
        Me.cmbOrganismo.Location = New System.Drawing.Point(18, 32)
        Me.cmbOrganismo.Name = "cmbOrganismo"
        Me.cmbOrganismo.Size = New System.Drawing.Size(121, 21)
        Me.cmbOrganismo.TabIndex = 0
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(18, 18)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(62, 16)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "Organismo"
        Me.Label1.ForeColor = System.Drawing.Color.MidnightBlue
        '
        'tlbInusualesDivisas
        '
        Me.tlbInusualesDivisas.Buttons.AddRange(New System.Windows.Forms.ToolBarButton() {Me.btnConsultar, Me.btnExportar, Me.btnRefrescar, Me.btnSalir})
        Me.tlbInusualesDivisas.ButtonSize = New System.Drawing.Size(75, 40)
        Me.tlbInusualesDivisas.DropDownArrows = True
        Me.tlbInusualesDivisas.ImageList = Me.ImageList1
        Me.tlbInusualesDivisas.Location = New System.Drawing.Point(0, 0)
        Me.tlbInusualesDivisas.Name = "tlbInusualesDivisas"
        Me.tlbInusualesDivisas.ShowToolTips = True
        Me.tlbInusualesDivisas.Size = New System.Drawing.Size(554, 42)
        Me.tlbInusualesDivisas.TabIndex = 16
        '
        'btnConsultar
        '
        Me.btnConsultar.ImageIndex = 8
        Me.btnConsultar.Name = "btnConsultar"
        Me.btnConsultar.Text = "Consultar"
        '
        'btnExportar
        '
        Me.btnExportar.ImageIndex = 9
        Me.btnExportar.Name = "btnExportar"
        Me.btnExportar.Text = "Exportar"
        '
        'btnRefrescar
        '
        Me.btnRefrescar.ImageIndex = 0
        Me.btnRefrescar.Name = "btnRefrescar"
        Me.btnRefrescar.Text = "Refrescar"
        '
        'btnSalir
        '
        Me.btnSalir.ImageIndex = 4
        Me.btnSalir.Name = "btnSalir"
        Me.btnSalir.Text = "Salir"
        '
        'ImageList1
        '
        Me.ImageList1.ImageStream = CType(resources.GetObject("ImageList1.ImageStream"), System.Windows.Forms.ImageListStreamer)
        Me.ImageList1.TransparentColor = System.Drawing.Color.Turquoise
        Me.ImageList1.Images.SetKeyName(0, "")
        Me.ImageList1.Images.SetKeyName(1, "")
        Me.ImageList1.Images.SetKeyName(2, "")
        Me.ImageList1.Images.SetKeyName(3, "")
        Me.ImageList1.Images.SetKeyName(4, "")
        Me.ImageList1.Images.SetKeyName(5, "")
        Me.ImageList1.Images.SetKeyName(6, "")
        Me.ImageList1.Images.SetKeyName(7, "")
        Me.ImageList1.Images.SetKeyName(8, "")
        Me.ImageList1.Images.SetKeyName(9, "")
        '
        'GroupBox2
        '
        Me.GroupBox2.Controls.Add(Me.DataGrid1)
        Me.GroupBox2.Location = New System.Drawing.Point(6, 128)
        Me.GroupBox2.Name = "GroupBox2"
        Me.GroupBox2.Size = New System.Drawing.Size(546, 276)
        Me.GroupBox2.TabIndex = 17
        Me.GroupBox2.TabStop = False
        Me.GroupBox2.Text = "Resultados"
        '
        'DataGrid1
        '
        Me.DataGrid1.BackgroundColor = System.Drawing.Color.White
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(3, 16)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(540, 257)
        Me.DataGrid1.TabIndex = 0
        '
        'lblResultados
        '
        Me.lblResultados.Location = New System.Drawing.Point(449, 406)
        Me.lblResultados.Name = "lblResultados"
        Me.lblResultados.Size = New System.Drawing.Size(100, 14)
        Me.lblResultados.TabIndex = 18
        Me.lblResultados.Text = "Resultados:"
        Me.lblResultados.TextAlign = System.Drawing.ContentAlignment.TopRight
        Me.lblResultados.ForeColor = System.Drawing.Color.MidnightBlue
        '
        'PersonasBol
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.BackColor = System.Drawing.Color.White
        Me.ClientSize = New System.Drawing.Size(554, 421)
        Me.Controls.Add(Me.lblResultados)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.tlbInusualesDivisas)
        Me.Controls.Add(Me.GroupBox1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D
        Me.MaximizeBox = False
        Me.Name = "PersonasBol"
        Me.Text = "Personas Boletinadas"
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox1.PerformLayout()
        Me.GroupBox2.ResumeLayout(False)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub

#End Region
    'Declaracion de varibales globales anivel de clase
    Dim clCmp As clsComplejo = New clsComplejo
    Dim clCmp1 As clsComplejo = New clsComplejo
    Dim clGrid As clsComplejo = New clsComplejo
    Dim tipoCombo As Integer = 0
    Dim BanPais, BanOrg As Boolean

    Private Sub PersonasBol_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        BanPais = False
        BanOrg = False
    End Sub

    Private Sub Refrescar()
        'Limpia los combos, datagrid y la caja de texto
        Me.cmbPais.DataSource = Nothing
        Me.cmbPais.Items.Clear()

        Me.cmbOrganismo.DataSource = Nothing
        Me.cmbOrganismo.Items.Clear()

        Me.DataGrid1.DataSource = Nothing
        Me.DataGrid1.DataBindings.Clear()

        Me.txtPersona.Text = ""

        'Para que se puedan a llenar otra vez los combos
        BanPais = False
        BanOrg = False
    End Sub

    Private Sub tlbInusualesDivisas_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles tlbInusualesDivisas.ButtonClick
        'Botones de la toolbar
        Select Case e.Button.Text
            Case "Consultar"
                sbBuscar()
            Case "Exportar"
                Exportar()
            Case "Refrescar"
                Refrescar()
            Case "Salir"
                Me.Close()
        End Select
    End Sub

    Private Sub sbBuscar()
        Try
            'Se utiliza el procedimiento almacenado "spBuscaPerBol" para buscar
            Dim SQL As String
            SQL = ""
            SQL = fsDefineSQL("spBuscaPerBol ")
            clGrid.fdtDataSetTabla(SQL)
            Me.DataGrid1.SetDataBinding(clGrid.ds, "Tabla") 'Llena el grid
            Dim cont As Integer
            cont = clGrid.ds.Tables(0).Rows.Count
            lblResultados.Text = "Elementos: " & cont
        Catch ex As Exception
            MessageBox.Show(ex.Message, "MIDS", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Function fsDefineSQL(ByVal SP As String) As String
        'Defino mi cadena para el procedimiento almacenado

        'Declaro varables para pasar los parametros al procedimiento almacenado
        Dim lsSQL As String
        Dim _Opcion As Integer
        Dim _Organismo As String
        Dim _Pais As String
        Dim _Persona As String

        If Me.cmbOrganismo.Text = "" And Me.cmbPais.Text = "" And Me.txtPersona.Text = "" Then
            _Opcion = 0
            _Organismo = ", NULL, "
            _Pais = " NULL, "
            _Persona = " NULL "

        ElseIf Me.cmbOrganismo.Text = "" And Me.cmbPais.Text = "" And Me.txtPersona.Text <> "" Then
            _Opcion = 1
            _Organismo = ", NULL, "
            _Pais = " NULL, "
            _Persona = "'%" & Me.txtPersona.Text & "%'"

        ElseIf Me.cmbOrganismo.Text = "" And Me.cmbPais.Text <> "" And Me.txtPersona.Text = "" Then
            _Opcion = 2
            _Organismo = ", NULL, "
            _Pais = "'" & CStr(Me.cmbPais.SelectedValue) & "', "
            _Persona = " NULL "

        ElseIf Me.cmbOrganismo.Text = "" And Me.cmbPais.Text <> "" And Me.txtPersona.Text <> "" Then
            _Opcion = 3
            _Organismo = ", NULL, "
            _Pais = "'" & CStr(Me.cmbPais.SelectedValue) & "', "
            _Persona = "'%" & Me.txtPersona.Text & "%'"

        ElseIf Me.cmbOrganismo.Text <> "" And Me.cmbPais.Text = "" And Me.txtPersona.Text = "" Then
            _Opcion = 4
            _Organismo = " ,'" & CStr(Me.cmbOrganismo.SelectedValue) & "', "
            _Pais = "NULL, "
            _Persona = "NULL"

        ElseIf Me.cmbOrganismo.Text <> "" And Me.cmbPais.Text = "" And Me.txtPersona.Text <> "" Then
            _Opcion = 5
            _Organismo = " ,'" & CStr(Me.cmbOrganismo.SelectedValue) & "', "
            _Pais = "NULL, "
            _Persona = "'%" & Me.txtPersona.Text & "%'"

        ElseIf Me.cmbOrganismo.Text <> "" And Me.cmbPais.Text <> "" And Me.txtPersona.Text = "" Then
            _Opcion = 6
            _Organismo = " ,'" & CStr(Me.cmbOrganismo.SelectedValue) & "', "
            _Pais = "'" & CStr(Me.cmbPais.SelectedValue) & "', "
            _Persona = "NULL"

        ElseIf Me.cmbOrganismo.Text <> "" And Me.cmbPais.Text <> "" And Me.txtPersona.Text <> "" Then
            _Opcion = 7
            _Organismo = " ,'" & CStr(Me.cmbOrganismo.SelectedValue) & "', "
            _Pais = "'" & CStr(Me.cmbPais.SelectedValue) & "', "
            _Persona = "'%" & Me.txtPersona.Text & "%'"
        End If

        lsSQL = lsSQL & SP & " "
        lsSQL = lsSQL & _Opcion
        lsSQL = lsSQL & _Organismo
        lsSQL = lsSQL & _Pais
        lsSQL = lsSQL & _Persona
        Return lsSQL
    End Function

    Private Sub Exportar()
        'llama a las rutinas para exportar el grid a excel
        Try
            If clGrid.ds.Tables.Count = 0 Then
                MessageBox.Show("Realiza una consulta ", "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                If clGrid.ds.Tables(0).Rows.Count = 0 Then
                    MessageBox.Show("No hay datos para exportar ", "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    Dim Resul As DialogResult
                    Resul = MessageBox.Show("Este proceso puede tardar varios minutos, ¿Deseas continuar? ", "MINDS", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                    If Resul = DialogResult.Yes Then
                        'Creo una instancia de la clase aExcel
                        Dim clsExcel As aExcel = New aExcel
                        'Utilizo la funcion ExportDataset de la clase aExcel
                        clsExcel.ExportDataset(clGrid.ds) 'Asigno el dataset a exportar
                    End If
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "MINDS", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub cmbPais_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbPais.DropDown
        If BanPais = False Then
            'Para llenar el combo de Pais se utiliza el porcedimiento almacenado "spListaPais"
            clCmp1.sbLlenaCombo("spListaPais", Me.cmbPais)

        End If
        BanPais = True
    End Sub

    Private Sub cmbOrganismo_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbOrganismo.DropDown
        If BanOrg = False Then
            'Para llenar el combo de organismo se utiliza el procedimiento almacenado "spLlenaComboOrgInt"
            clCmp.sbLlenaCombo("spLlenaComboOrgInt", Me.cmbOrganismo)

        End If
        BanOrg = True
    End Sub


End Class