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

Public Class frmUsuarios
#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

#End Region

    Dim CadenaBusqueda As String = ""

    Private Sub frmUsuarios_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CargarDGV()
        LimpiarCampos(Me)
    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 TOP 50 Id, FechaA, Nombre, Usuario, Password, RePassword, Nivel, Departamento, Email, Capturo from Usuarios  order by Nombre"
        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
            DGVProve.DataSource = Me.BindingSource1
            DGVProve.Columns(4).Visible = False
            DGVProve.Columns(5).Visible = False
            DGVProve.Columns(9).Visible = False

            FormatoFilas()
            cn.Close()
            cm = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Information)
        End Try
    End Sub
    Sub FormatoFilas()
        With DGVProve
            'CAMBIA LOS TITULOS DE LAS COLUMNAS a ingles
            .Columns(0).HeaderText = "Id."
            .Columns(1).HeaderText = "Fecha"
            .Columns(2).HeaderText = "Nombre."
            .Columns(3).HeaderText = "Usuario del Sistema"
            '.Columns(4).HeaderText = "Password"
            '.Columns(5).HeaderText = "RePassword"
            .Columns(6).HeaderText = "Nivel"
            .Columns(7).HeaderText = "Departamento"
            .Columns(8).HeaderText = "E-mail"
            '.Columns(9).HeaderText = "Capturo"
        End With
    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

    Private Sub btnGrabar_Click(sender As Object, e As EventArgs) Handles btnGrabar.Click
        If Nombre.Text Is Nothing Or IsDBNull(Nombre.Text) Or Nombre.Text = "" Then
            MsgBox("Atención!, campo Nombre es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Nombre.Focus()
            Exit Sub
        End If
        If Usuario.Text Is Nothing Or IsDBNull(Usuario.Text) Or Usuario.Text = "" Then
            MsgBox("Atención!, campo Usuario es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Usuario.Focus()
            Exit Sub
        End If
        If Password.Text Is Nothing Or IsDBNull(Password.Text) Or Password.Text = "" Then
            MsgBox("Atención!, campo Password es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Password.Focus()
            Exit Sub
        End If
        If RePassword.Text Is Nothing Or IsDBNull(RePassword.Text) Or RePassword.Text = "" Then
            MsgBox("Atención!, campo Conf. Password es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            RePassword.Focus()
            Exit Sub
        End If
        If Nivel.Text Is Nothing Or IsDBNull(Nivel.Text) Or Nivel.Text = "" Then
            MsgBox("Atención!, campo Nivel es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Nivel.Focus()
            Exit Sub
        End If
        If Departamento.Text Is Nothing Or IsDBNull(Departamento.Text) Or Departamento.Text = "" Then
            MsgBox("Atención!, campo Departamento es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Departamento.Focus()
            Exit Sub
        End If
        If Email.Text Is Nothing Or IsDBNull(Email.Text) Or Email.Text = "" Then
            MsgBox("Atención!, campo Email es obligatorio.", MsgBoxStyle.Critical, "Mensaje del Sistema")
            Email.Focus()
            Exit Sub
        End If

        If RePassword.Text = Password.Text Then
        Else
            MsgBox("Atención!, PASSWORDS NO COINCIDEN.", MsgBoxStyle.Information, "System Message")
            Exit Sub
        End If

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

        If Id.Text = "" Then
            ' Agregar registro - insert
            sql = "INSERT INTO Usuarios (FechaA, Nombre, Usuario, Password, RePassword, Nivel, Departamento, Email, Capturo) VALUES (@FechaA, " &
                "@Nombre, @Usuario, @Password, @RePassword, @Nivel, @Departamento, @Email, @Capturo)"
        Else
            ' Actualizar registro - update
            sql = "UPDATE Usuarios SET FechaA=@FechaA, Nombre=@Nombre, Usuario=@Usuario, Password=@Password, RePassword=@RePassword, Nivel=@Nivel, Departamento=@Departamento, " &
                "Email=@Email, Capturo=@Capturo 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("@FechaA", Fecha.Value)
            cmd.Parameters.AddWithValue("@Nombre", Nombre.Text)
            cmd.Parameters.AddWithValue("@Usuario", Usuario.Text)
            cmd.Parameters.AddWithValue("@Password", Password.Text)
            cmd.Parameters.AddWithValue("@RePassword", RePassword.Text)
            cmd.Parameters.AddWithValue("@Nivel", Nivel.Text)
            cmd.Parameters.AddWithValue("@Departamento", Departamento.Text)
            cmd.Parameters.AddWithValue("@Email", Email.Text)
            cmd.Parameters.AddWithValue("@Capturo", GlobalVariables.varUsuarioAcceso)

            cmd.ExecuteNonQuery()
            cn.Close()
            cmd = Nothing
            CargarDGV()

            MsgBox("Atención, Datos Guardados Correctamente.", MsgBoxStyle.Information, "Mensaje del Sistema")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnVerTodos_Click(sender As Object, e As EventArgs) Handles btnVerTodos.Click
        Me.bsFiltro.Text = ""
        CargarDGV()
        GlobalVariables.varReporteConsulta = 0
    End Sub

    Private Sub btnNuevo_Click(sender As Object, e As EventArgs) Handles btnNuevo.Click
        LimpiarCampos(Me)
        Nombre.Focus()
    End Sub

    Private Sub CargaRegistro()
        Dim img() As Byte
        Dim ms As New MemoryStream()

        Id.Text = DGVProve.CurrentRow.Cells("ID").Value
        Capturo.Text = DGVProve.CurrentRow.Cells("Capturo").Value
        Fecha.Text = DGVProve.CurrentRow.Cells("FechaA").Value
        Nombre.Text = DGVProve.CurrentRow.Cells("Nombre").Value
        Usuario.Text = DGVProve.CurrentRow.Cells("Usuario").Value
        Password.Text = DGVProve.CurrentRow.Cells("Password").Value
        RePassword.Text = DGVProve.CurrentRow.Cells("RePassword").Value
        Nivel.Text = DGVProve.CurrentRow.Cells("Nivel").Value
        Departamento.Text = DGVProve.CurrentRow.Cells("Departamento").Value
        Email.Text = DGVProve.CurrentRow.Cells("Email").Value
        Foto.Image = CargarImagen(Id.Text)
    End Sub
    Public Function CargarImagen(ByVal id As Integer) As Image
        Try
            'for sql server
            Dim conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = GlobalVariables.cstring

            'for SQL 
            Dim sql As String = "select logo from Usuarios 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 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 Usuarios 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()
                    QryInsUpDel("DELETE FROM Claves WHERE Usuario ='" & Me.Nombre.Text & "'")
                    LimpiarCampos(Me)
                    MsgBox("Usuario eliminado correctamente.", MsgBoxStyle.Information, "Mensaje del Sistema")
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
            End If

        End If
    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("Debe haber un Id de registro ya guardado o preexistente.", 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 Usuarios SET Logo=@Logo WHERE Id=" & Id.Text
            Dim command1 As SqlCommand = New SqlCommand(sql, conn)
            Dim sqlpara As New SqlParameter("Logo", 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 de Usuario registrado 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

    Private Sub RePassword_Validated(sender As Object, e As EventArgs) Handles RePassword.Validated
        If RePassword.Text = Password.Text Then
        Else
            MsgBox("Atención!, PASSWORDS NO COINCIDEN.", MsgBoxStyle.Information, "System Message")
            Exit Sub
        End If
    End Sub

    Private Sub btnCerrar_Click_1(sender As Object, e As EventArgs) Handles btnCerrar.Click
        Me.Dispose()
        Me.Close()
        CerrarFormulario()
    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

    Private Sub PanelCampos_MouseMove(sender As Object, e As MouseEventArgs) Handles PanelCampos.MouseMove
        ReleaseCapture()
        SendMessage(Me.Handle, &H112&, &HF012&, 0)
    End Sub

    Private Sub bsFiltro_TextChanged(sender As Object, e As EventArgs) Handles bsFiltro.TextChanged
        Dim CriterioBusqueda As String
        CriterioBusqueda = "Nombre"
        Me.BindingSource1.Filter = CriterioBusqueda & " like '%" & Me.bsFiltro.Text & "%'"
    End Sub

    Private Sub DGVProve_CellContentDoubleClick_1(sender As Object, e As DataGridViewCellEventArgs) Handles DGVProve.CellContentDoubleClick
        CargaRegistro()
    End Sub

End Class