Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing.Imaging
Imports System.Runtime.InteropServices


Public Class frmClientes
#Region "Mov. Pantalla"
    <DllImport("user32.Dll", EntryPoint:="ReleaseCapture")>
    Private Shared Sub ReleaseCapture()
    End Sub

    <DllImport("user32.Dll", EntryPoint:="SendMessage")>
    Private Shared Sub SendMessage(hWnd As IntPtr, wMsg As Integer, wParam As Integer, lParam As Integer)
    End Sub

    Private Sub PanelBarraTitulo_MouseMove(sender As Object, e As MouseEventArgs) Handles PanelBarraTitulo.MouseMove
        ReleaseCapture()
        SendMessage(Me.Handle, &H112&, &HF012&, 0)
    End Sub
    Private Sub lblEmpresa_MouseMove(sender As Object, e As MouseEventArgs) Handles lblEmpresa.MouseMove
        ReleaseCapture()
        SendMessage(Me.Handle, &H112&, &HF012&, 0)
    End Sub

#End Region
    Private Sub btnCerrar_Click(sender As Object, e As EventArgs) Handles btnCerrar.Click
        If GlobalVariables.EditoCliente = True Then
            MsgBox("Atención! Captura de Cliente en proceso... Guardar o Cancelar el registro para continuar.", MsgBoxStyle.Information, "Mensaje del Sistema")
            Exit Sub
        End If

        GlobalVariables.EditoCliente = False
        Me.Close()
        CerrarFormulario()
    End Sub

    Private Sub frmCaClientes_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CargarDGV()
        LlenaBusqueda()
        LimpiarCampos(Me)
    End Sub
    Sub LlenaBusqueda()
        Dim dt As DataTable = New DataTable("Tabla")

        dt.Columns.Add("Codigo")
        dt.Columns.Add("Descripcion")

        Dim dr As DataRow

        dr = dt.NewRow()
        dr("Codigo") = "RazonS"
        dr("Descripcion") = "Razón Social"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("Codigo") = "NomCom"
        dr("Descripcion") = "Nombre Comecial"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("Codigo") = "Tel1"
        dr("Descripcion") = "Teléfono 1"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("Codigo") = "Tel2"
        dr("Descripcion") = "Teléfono 2"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("Codigo") = "Contacto"
        dr("Descripcion") = "Contácto 1"
        dt.Rows.Add(dr)

        bsCriterio.ComboBox.DataSource = dt
        bsCriterio.ComboBox.ValueMember = "Codigo"
        bsCriterio.ComboBox.DisplayMember = "Descripcion"
    End Sub
    Private Sub bsFiltro_TextChanged(sender As Object, e As EventArgs) Handles bsFiltro.TextChanged
        Dim CriterioBusqueda As String = ""

        If bsCriterio.ComboBox.Text = "Razón Social" Then
            CriterioBusqueda = "RazonSoc"
        ElseIf bsCriterio.ComboBox.Text = "Nombre Comecial" Then
            CriterioBusqueda = "NombreCom"
        ElseIf bsCriterio.ComboBox.Text = "Teléfono 1" Then
            CriterioBusqueda = "TelUno"
        ElseIf bsCriterio.ComboBox.Text = "Teléfono 2" Then
            CriterioBusqueda = "TelDos"
        ElseIf bsCriterio.ComboBox.Text = "Contácto 1" Then
            CriterioBusqueda = "Contacto1"

        ElseIf bsCriterio.ComboBox.Text = "" Then
            MsgBox("Atención, primero seleccione un criterio de búsqueda.", MsgBoxStyle.Information)
        End If

        Me.BindingSource1.Filter = CriterioBusqueda & " like '%" & Me.bsFiltro.Text & "%'"
        GlobalVariables.varCriterioConsulta = CriterioBusqueda & " like '%" & Me.bsFiltro.Text & "%'"
        GlobalVariables.varReporteConsulta = 1
    End Sub
    Private Sub btnVerTodos_Click(sender As Object, e As EventArgs) Handles btnVerTodos.Click
        Me.BindingSource1.RemoveFilter()
        Me.bsFiltro.Text = ""
        GlobalVariables.varReporteConsulta = 0
    End Sub

    Sub CargarDGV()
        Dim sql As String
        Dim cn As SqlConnection
        Dim cm As SqlCommand
        Dim da As SqlDataAdapter
        Dim ds As DataSet
        sql = "SELECT * FROM Clientes"
        cn = New SqlConnection(GlobalVariables.cstring)
        Try
            cn.Open()

            cm = New SqlCommand()
            cm.CommandText = sql
            cm.CommandType = CommandType.Text
            cm.Connection = cn

            da = New SqlDataAdapter(cm)

            ds = New DataSet()
            da.Fill(ds)

            Me.BindingSource1.DataSource = ds.Tables(0).DefaultView
            Me.BindingNavigator1.BindingSource = Me.BindingSource1
            DGVClientes.DataSource = Me.BindingSource1
            DGVClientes.Columns(1).Visible = False
            DGVClientes.Columns(25).Visible = False

            'DGVClientes.Columns.Item("Peso").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            FormatoFilas()
            cn.Close()
            cm = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Information)
        End Try
    End Sub

    Sub LimpiarCampos(ByVal c1 As Control)
        'Limpia todos los controles dentro del control principal (c1)
        'aunque esten dentro de otros controles
        For Each c As Control In c1.Controls
            If TypeOf c Is TextBox Then
                c.Text = "" ' eliminar el texto 
            Else
                LimpiarCampos(c)
            End If
            If TypeOf c Is ComboBox Then
                c.Text = "" ' eliminar el texto 
            Else
                LimpiarCampos(c)
            End If
            If TypeOf c Is DateTimePicker Then
                c.Text = Date.Today ' Pone la fecha actual 
            Else
                LimpiarCampos(c)
            End If
            If (TypeOf (c) Is CheckBox) Then
                CType(c, CheckBox).Checked = False 'Quita el check
            Else
                LimpiarCampos(c)
            End If
            If (TypeOf (c) Is RadioButton) Then
                CType(c, RadioButton).Checked = False 'Quita el check
            Else
                LimpiarCampos(c)
            End If
            Foto.Image = My.Resources.sin_fotos
            Foto.SizeMode = PictureBoxSizeMode.CenterImage

        Next
    End Sub

    Sub FormatoFilas()
        With DGVClientes
            'CAMBIA LOS TITULOS DE LAS COLUMNAS a ingles
            .Columns(0).HeaderText = "Id. Cliente"
            .Columns(2).HeaderText = "Fecha"
            .Columns(3).HeaderText = "Tipo Cte."
            .Columns(4).HeaderText = "Razón Social"
            .Columns(5).HeaderText = "Nombre Comercial"
            .Columns(6).HeaderText = "Calle"
            .Columns(7).HeaderText = "Número"
            .Columns(8).HeaderText = "Colonia"
            .Columns(9).HeaderText = "Cod. Postal"
            .Columns(10).HeaderText = "Estado"
            .Columns(11).HeaderText = "País"
            .Columns(12).HeaderText = "Rfc
"
            .Columns(13).HeaderText = "Teléfono 1"
            .Columns(14).HeaderText = "Teléfono 2"
            .Columns(15).HeaderText = "Contácto 1"
            .Columns(16).HeaderText = "E-mail"
            .Columns(17).HeaderText = "Ext. 1"
            .Columns(18).HeaderText = "Contácto 2"
            .Columns(19).HeaderText = "E-mail"
            .Columns(20).HeaderText = "Ext. 2"
            .Columns(21).HeaderText = "Contácto 3"
            .Columns(22).HeaderText = "E-mail"
            .Columns(23).HeaderText = "Ext. 3"
            .Columns(24).HeaderText = "Notas"
        End With
    End Sub

    Private Sub btnGrabar_Click(sender As Object, e As EventArgs) Handles btnGrabar.Click
        If RazonSoc.Text Is Nothing Or IsDBNull(RazonSoc.Text) Or RazonSoc.Text = "" Then
            MsgBox("Atención!, campo RAZON SOCIAL es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            RazonSoc.Focus()
            Exit Sub
        End If
        If NombreCom.Text Is Nothing Or IsDBNull(NombreCom.Text) Or NombreCom.Text = "" Then
            MsgBox("Atención!, campo NOMBRE COMERCIAL es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            NombreCom.Focus()
            Exit Sub
        End If
        If Tipo.Text Is Nothing Or IsDBNull(Tipo.Text) Or Tipo.Text = "" Then
            MsgBox("Atención!, campo TIPO DE CLIENTE es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Tipo.Focus()
            Exit Sub
        End If
        If RFC.Text Is Nothing Or IsDBNull(RFC.Text) Or RFC.Text = "" Then
            MsgBox("Atención!, campo RFC es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            RFC.Focus()
            Exit Sub
        End If

        If TelUno.Text Is Nothing Or IsDBNull(TelUno.Text) Or TelUno.Text = "" Then
            MsgBox("Atención!, campo TELEFONO 1 es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            TelUno.Focus()
            Exit Sub
        End If
        If Contacto1.Text Is Nothing Or IsDBNull(Contacto1.Text) Or Contacto1.Text = "" Then
            MsgBox("Atención!, campo CONTACTO 1 es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Contacto1.Focus()
            Exit Sub
        End If
        If Email1.Text Is Nothing Or IsDBNull(Email1.Text) Or Email1.Text = "" Then
            MsgBox("Atención!, campo EMAIL 1 es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Email1.Focus()
            Exit Sub
        End If

        GuardaRegistro()
        LimpiarCampos(Me)

    End Sub
    Sub GuardaRegistro()
        Dim sql As String = ""

        If Id.Text = "" Then
            ' Agregar registro - insert
            sql = "INSERT INTO Clientes (Capturo, Fecha, Tipo, RazonSoc, NombreCom, Calle, Numero, Colonia, CodPos, Estado, Pais, Rfc, TelUno, " &
                "TelDos, Contacto1, Email1, Ext1, Contacto2, Email2, Ext2, Contacto3, Email3, Ext3, Notas) VALUES (@Capturo, " &
                "@Fecha, @Tipo, @RazonSoc, @NombreCom, @Calle, @Numero, @Colonia, @CodPos, @Estado, @Pais, @Rfc, @TelUno, @TelDos, @Contacto1, " &
                "@Email1, @Ext1, @Contacto2, @Email2, @Ext2, @Contacto3, @Email3, @Ext3, @Notas)"
        Else
            ' Actualizar registro - update
            sql = "UPDATE Clientes SET Capturo=@Capturo, Fecha=@Fecha, Tipo=@Tipo, RazonSoc=@RazonSoc, NombreCom=@NombreCom, Calle=@Calle, Numero=@Numero, " &
                "Colonia=@Colonia, CodPos=@CodPos, Estado=@Estado, Pais=@Pais, Rfc=@Rfc, TelUno=@TelUno, TelDos=@TelDos, Contacto1=@Contacto1, " &
                "Email1=@Email1, Ext1=@Ext1, Contacto2=@Contacto2, Email2=@Email2, Ext2=@Ext2, Contacto3=@Contacto3, Email3=@Email3, " &
                "Ext3=@Ext3, Notas=@Notas WHERE id =" & Id.Text
        End If
        Try
            Dim cn As New SqlConnection(GlobalVariables.cstring)
            Dim cmd As New SqlCommand(sql, cn)
            cn.Open()
            cmd.Parameters.AddWithValue("@Capturo", GlobalVariables.VarUsuario)
            cmd.Parameters.AddWithValue("@Fecha", Fecha.Value)
            cmd.Parameters.AddWithValue("@Tipo", Tipo.Text)
            cmd.Parameters.AddWithValue("@RazonSoc", RazonSoc.Text)
            cmd.Parameters.AddWithValue("@NombreCom", NombreCom.Text)
            cmd.Parameters.AddWithValue("@Calle", Calle.Text)
            cmd.Parameters.AddWithValue("@Numero", Numero.Text)
            cmd.Parameters.AddWithValue("@Colonia", Colonia.Text)
            cmd.Parameters.AddWithValue("@CodPos", CodPos.Text)
            cmd.Parameters.AddWithValue("@Estado", Estado.Text)
            cmd.Parameters.AddWithValue("@Pais", Pais.Text)
            cmd.Parameters.AddWithValue("@Rfc", RFC.Text)
            cmd.Parameters.AddWithValue("@TelUno", TelUno.Text)
            cmd.Parameters.AddWithValue("@TelDos", TelDos.Text)
            cmd.Parameters.AddWithValue("@Contacto1", Contacto1.Text)
            cmd.Parameters.AddWithValue("@Email1", Email1.Text)
            cmd.Parameters.AddWithValue("@Ext1", Ext1.Text)
            cmd.Parameters.AddWithValue("@Contacto2", Contacto2.Text)
            cmd.Parameters.AddWithValue("@Email2", Email2.Text)
            cmd.Parameters.AddWithValue("@Ext2", Ext2.Text)
            cmd.Parameters.AddWithValue("@Contacto3", Contacto3.Text)
            cmd.Parameters.AddWithValue("@Email3", Email3.Text)
            cmd.Parameters.AddWithValue("@Ext3", Ext3.Text)
            cmd.Parameters.AddWithValue("@Notas", Notas.Text)

            cmd.ExecuteNonQuery()
            cn.Close()
            cmd = Nothing
            CargarDGV()
            GlobalVariables.EditoCliente = False
            MsgBox("Atención, Datos Guardados Correctamente.", MsgBoxStyle.Information, "Mensaje del Sistema")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub


    Private Sub btnEditar_Click(sender As Object, e As EventArgs) Handles btnEditar.Click
        CargaRegistro()
    End Sub

    Private Sub DGVClientes_CellContentDoubleClick_1(sender As Object, e As DataGridViewCellEventArgs) Handles DGVClientes.CellContentDoubleClick
        CargaRegistro()
    End Sub
    Private Sub CargaRegistro()
        Dim img() As Byte
        Dim ms As New MemoryStream()

        Id.Text = DGVClientes.CurrentRow.Cells("ID").Value
        Capturo.Text = DGVClientes.CurrentRow.Cells("Capturo").Value
        Fecha.Text = DGVClientes.CurrentRow.Cells("Fecha").Value
        Tipo.Text = DGVClientes.CurrentRow.Cells("Tipo").Value
        RazonSoc.Text = DGVClientes.CurrentRow.Cells("RazonSoc").Value
        NombreCom.Text = DGVClientes.CurrentRow.Cells("NombreCom").Value
        Calle.Text = DGVClientes.CurrentRow.Cells("Calle").Value
        Numero.Text = DGVClientes.CurrentRow.Cells("Numero").Value
        Colonia.Text = DGVClientes.CurrentRow.Cells("Colonia").Value
        CodPos.Text = DGVClientes.CurrentRow.Cells("CodPos").Value
        Estado.Text = DGVClientes.CurrentRow.Cells("Estado").Value
        Pais.Text = DGVClientes.CurrentRow.Cells("Pais").Value
        RFC.Text = DGVClientes.CurrentRow.Cells("Rfc").Value
        TelUno.Text = DGVClientes.CurrentRow.Cells("TelUno").Value
        TelDos.Text = DGVClientes.CurrentRow.Cells("TelDos").Value
        Contacto1.Text = DGVClientes.CurrentRow.Cells("Contacto1").Value
        Email1.Text = DGVClientes.CurrentRow.Cells("Email1").Value
        Ext1.Text = DGVClientes.CurrentRow.Cells("Ext1").Value
        Contacto2.Text = DGVClientes.CurrentRow.Cells("Contacto2").Value
        Email2.Text = DGVClientes.CurrentRow.Cells("Email2").Value
        Ext2.Text = DGVClientes.CurrentRow.Cells("Ext2").Value
        Contacto3.Text = DGVClientes.CurrentRow.Cells("Contacto3").Value
        Email3.Text = DGVClientes.CurrentRow.Cells("Email3").Value
        Ext3.Text = DGVClientes.CurrentRow.Cells("Ext3").Value
        Notas.Text = DGVClientes.CurrentRow.Cells("Notas").Value

        Foto.Image = CargarImagen(Me.Id.Text, "Foto")

        btnGrabar.Enabled = True
        btnEliminar.Enabled = True
        btnBuscImg.Enabled = True
        btnGuardarImg.Enabled = True
        btnQuitarImg.Enabled = True
        GlobalVariables.EditoCliente = True
    End Sub
    Public Function CargarImagen(ByVal id As Integer, Campo As String) As Image
        Try
            Dim conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = GlobalVariables.cstring

            Dim sql As String = "select " & Campo & " from Clientes where id = " & id
            Dim command1 As SqlCommand = New SqlCommand(sql, conn)
            conn.Open()
            Dim reader As SqlDataReader = command1.ExecuteReader
            reader.Read()
            Dim bitPic() As Byte = CType(reader.GetValue(0), Byte())

            conn.Close()
            Dim mStream As MemoryStream = New MemoryStream(bitPic)
            Dim img As Image = Image.FromStream(mStream)

            Return img
        Catch ex As Exception
            Return My.Resources.sin_fotos
        End Try
    End Function
    Private Sub btnNuevo_Click(sender As Object, e As EventArgs) Handles btnNuevo.Click
        GlobalVariables.EditoCliente = True
        btnGrabar.Enabled = True
        btnEliminar.Enabled = True

        btnBuscImg.Enabled = True
        btnGuardarImg.Enabled = True
        btnQuitarImg.Enabled = True
        LimpiarCampos(Me)
        Tipo.Focus()
    End Sub

    Private Sub btnEliminar_Click(sender As Object, e As EventArgs) Handles btnEliminar.Click
        EliminarRegistro()
    End Sub

    Sub EliminarRegistro()
        If Me.Id.Text = "" Then
            MsgBox("Atención!, no existe registro cargado para Eliminar.", MsgBoxStyle.Information, "Mensaje del Sistema")
        Else
            Dim sql As String = ""
            sql = " DELETE FROM Clientes WHERE id =" & Me.Id.Text
            If MsgBox("Seguro(a) de eliminar el registro actual?", MsgBoxStyle.YesNo, "Mensaje del Sistema") = vbYes Then
                Try
                    Dim cn As New SqlConnection(GlobalVariables.cstring)
                    Dim cmd As New SqlCommand(sql, cn)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cn.Close()
                    cmd = Nothing
                    CargarDGV()
                    LimpiarCampos(Me)
                    MsgBox("Datos eliminados correctamente.", MsgBoxStyle.Information, "Mensaje del Sistema")
                    GlobalVariables.EditoCliente = False
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
            End If

        End If
    End Sub

    Private Sub btnImprimir_Click(sender As Object, e As EventArgs) Handles btnImprimir.Click
        'GlobalVariables.varTipoReporte = "CLIENTES"
        'If (Application.OpenForms("frmRptCatalogos") Is Nothing) Then
        '    frmRptCatalogos.Show()
        'Else
        '    frmRptCatalogos.Close()
        '    frmRptCatalogos.Show()
        'End If
        'frmRptCatalogos.lblEmpresa.Text = "Report: CATALOGO DE CLIENTES"
    End Sub

    Private Sub btnBuscImg_Click(sender As Object, e As EventArgs) Handles btnBuscImg.Click
        If (OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK) Then
            Foto.Image = Image.FromFile(OpenFileDialog1.FileName)
        End If
    End Sub

    Private Sub btnGuardarImg_Click(sender As Object, e As EventArgs) Handles btnGuardarImg.Click
        If Id.Text = "" Then
            MsgBox("Atención!, debe guardar el registro para agregar la fotografia.", MsgBoxStyle.Information)
            Exit Sub
        End If

        Try
            Dim ms As New MemoryStream()
            Foto.Image.Save(ms, Foto.Image.RawFormat)
            Dim imageBytes As Byte() = ms.GetBuffer()
            ' newRow.ImageData = imageBytes

            'for sql server
            Dim conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = GlobalVariables.cstring

            'for SQL 
            Dim sql As String = "UPDATE Clientes SET Foto=@Foto WHERE Id=" & Id.Text & ""
            Dim command1 As SqlCommand = New SqlCommand(sql, conn)
            Dim sqlpara As New SqlParameter("Foto", SqlDbType.Image)

            Dim mStream As MemoryStream = New MemoryStream()
            Dim img As Image = Me.Foto.Image
            img.Save(mStream, ImageFormat.Jpeg)
            sqlpara.SqlValue = mStream.GetBuffer

            command1.Parameters.Add(sqlpara)
            conn.Open()
            command1.ExecuteNonQuery()
            conn.Close()

            MsgBox("Foto registrada con éxito.", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Information)
        End Try
    End Sub

    Private Sub btnQuitarImg_Click(sender As Object, e As EventArgs) Handles btnQuitarImg.Click
        Foto.Image = My.Resources.sin_fotos
    End Sub


End Class