Imports System.Text
Imports System.Data.SQLite

Public Class UserClass
#Region "Properties"
    Protected _UserID As String
    Friend Property UserID() As String
        Get
            Return _UserID
        End Get
        Set(ByVal value As String)
            _UserID = value

        End Set
    End Property
    Protected _UserFullName As String
    Friend Property UserFullName() As String
        Get
            Return _UserFullName
        End Get
        Set(ByVal value As String)
            _UserFullName = value
        End Set
    End Property
    Protected _LastName As String
    Friend Property LastName() As String
        Get
            Return _LastName
        End Get
        Set(ByVal value As String)
            _LastName = value
        End Set
    End Property
    Protected _MI As String
    Friend Property MI() As String
        Get
            Return _MI
        End Get
        Set(ByVal value As String)
            _MI = value
        End Set
    End Property
    Protected _UserPassword As String
    Friend Property UserPassword() As String
        Get
            Return _UserPassword
        End Get
        Set(ByVal value As String)
            _UserPassword = value
        End Set
    End Property
    Protected _UserName As String
    Friend Property UserName() As String
        Get
            Return _UserName
        End Get
        Set(ByVal value As String)
            _UserName = value
        End Set
    End Property
    Protected _UserLevel As String
    Friend Property UserLevel() As String
        Get
            Return _UserLevel
        End Get
        Set(ByVal value As String)
            _UserLevel = value
        End Set
    End Property

    Protected _pic As PictureBox
    Friend Property pic() As PictureBox
        Get
            Return _pic
        End Get
        Set(ByVal value As PictureBox)
            _pic = value
        End Set
    End Property
    Dim _LastLogin As String
    Friend Property LastLogin() As String
        Get
            Return _LastLogin
        End Get
        Set(ByVal value As String)
            _LastLogin = value
        End Set
    End Property
    Dim _DateAdded As String
    Friend Property DateAdded() As String
        Get
            Return _DateAdded
        End Get
        Set(ByVal value As String)
            _DateAdded = value

        End Set
    End Property

#End Region

    Friend Function ResetPass(ByVal _xUserID As String)
        Dim bReturn As Boolean
        Dim xSQL As New System.Text.StringBuilder
        xSQL.AppendLine("UPDATE MasterUser ")
        xSQL.AppendLine("SET UserPass=@UserPass ")
        xSQL.AppendLine("WHERE UserID=@UserID")
        Try
            Using SQLconnect As New SQLite.SQLiteConnection(g_constring)
                SQLconnect.Open()
                Using SQLcommand As New SQLiteCommand(SQLconnect)
                    SQLcommand.CommandText = xSQL.ToString
                    SQLcommand.Parameters.Clear()
                    Dim xClass As New clsCrypto
                    Dim key = "37ecd4b85bff4c71b5011fd24ada01ad"
                    Dim EncryptedPass As String = xClass.EncryptString128Bit(_UserPassword, key)
                    SQLcommand.Parameters.Add(New SQLiteParameter("@UserPass", EncryptedPass))
                    SQLcommand.Parameters.Add(New SQLiteParameter("@UserID", _xUserID))
                    Dim n As Integer = SQLcommand.ExecuteNonQuery
                    If n <> 0 Then bReturn = True
                    MessageBox.Show("Password Successfully Updated", "", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

                End Using

            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, "Reset Failed", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            bReturn = False
        End Try
        Return bReturn
    End Function
    Friend Function SaveUser()
        Dim bResult As Boolean = False
        Dim DateAdded As String = System.DateTime.Now.Date
        Dim xClass As New clsCrypto
        Dim EncryptedPass As String

        Dim SQLcmd As New System.Text.StringBuilder
        SQLcmd.AppendLine("INSERT INTO MasterUser ")
        SQLcmd.AppendLine("(UserID,UserName,UserPass,UserFullName,LastName,MI,UserLevel,Date_Added) ")
        SQLcmd.AppendLine("VALUES (@UserID,@UserName,@UserPass,@UserFullName,@LastName,@MI,@UserLevel,@Date_Added) ")
        Try
            Using SQLconnect As New SQLite.SQLiteConnection(g_constring)
                SQLconnect.Open()
                Using SQLCommand As New SQLiteCommand(SQLconnect)
                    SQLCommand.CommandText = SQLcmd.ToString
                    SQLCommand.Parameters.Clear()
                    Dim key = "37ecd4b85bff4c71b5011fd24ada01ad"
                    EncryptedPass = xClass.EncryptString128Bit(_UserPassword, key)
                    Dim UserCode As String = CreateNewTranNo(System.DateTime.Now.Date)

                    SQLCommand.Parameters.Add(New SQLiteParameter("@UserID", UserCode))
                    SQLCommand.Parameters.Add(New SQLiteParameter("@UserName", _UserName))
                    SQLCommand.Parameters.Add(New SQLiteParameter("@UserPass", EncryptedPass))
                    SQLCommand.Parameters.Add(New SQLiteParameter("@UserFullName", _UserFullName))
                    SQLCommand.Parameters.Add(New SQLiteParameter("@LastName", _LastName))
                    SQLCommand.Parameters.Add(New SQLiteParameter("@MI", _MI))
                    SQLCommand.Parameters.Add(New SQLiteParameter("@UserLevel", _UserLevel))
                    SQLCommand.Parameters.Add(New SQLiteParameter("@Date_Added", DateAdded))

                    Dim n As Integer = SQLCommand.ExecuteNonQuery
                    If n <> 0 Then bResult = True
                    MessageBox.Show("Data Succesfully Added", "", MessageBoxButtons.OK, MessageBoxIcon.Information)

                End Using
            End Using
        Catch Ex As Exception
            Throw Ex
        End Try
        Return bResult
    End Function
    Friend Function UpdateUser(ByVal _UserID As String)
        Dim bReturn As Boolean
        Dim SQLcmd As New System.Text.StringBuilder
        SQLcmd.AppendLine("UPDATE MasterUser SET ")
        SQLcmd.AppendLine("UserName=@UserName,UserLevel=@UserLevel,UserFullName=@UserFullName,LastName=@LastName,MI=@MI")
        SQLcmd.AppendLine("WHERE UserID=@UserID ")
        Try
            Using SQLconnect As New SQLiteConnection(g_constring)
                SQLconnect.Open()
                Using SQLcommand As New SQLiteCommand(SQLconnect)
                    SQLcommand.CommandText = SQLcmd.ToString
                    SQLcommand.Parameters.Clear()

                    SQLcommand.Parameters.Add(New SQLiteParameter("@UserID", _UserID))
                    SQLcommand.Parameters.Add(New SQLiteParameter("@UserName", _UserName))
                    SQLcommand.Parameters.Add(New SQLiteParameter("@UserFullName", _UserFullName))
                    SQLcommand.Parameters.Add(New SQLiteParameter("@LastName", _LastName))
                    SQLcommand.Parameters.Add(New SQLiteParameter("@MI", _MI))
                    SQLcommand.Parameters.Add(New SQLiteParameter("@UserLevel", _UserLevel))

                    Dim n As Integer = SQLcommand.ExecuteNonQuery
                    If n <> 0 Then bReturn = True
                    MessageBox.Show("Data Succesfully Updated", "", MessageBoxButtons.OK, MessageBoxIcon.Information)

                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return bReturn
    End Function
    Friend Function SavePhoto(ByVal PhotoId As String, ByVal PhotoFile As String) As Boolean
        Dim bReturn As Boolean

        Try
            Using SQLConnect As New SQLite.SQLiteConnection(g_constring)
                Dim photo() As Byte = FileImageToByte(PhotoFile)
                SQLConnect.Open()
                If SQLConnect.State = ConnectionState.Open Then
                    Dim SQLcommand As New SQLiteCommand(SQLConnect)
                    SQLcommand = SQLConnect.CreateCommand
                    SQLcommand.CommandText = "DELETE FROM Photos WHERE PhotoID = '" & PhotoId & "'"
                    SQLcommand.ExecuteNonQuery()
                    SQLcommand.Parameters.Clear()

                    SQLcommand.CommandText = "INSERT INTO Photos(PhotoID, Photo) VALUES(@EmployeeID, @Photo1)"

                    Dim SQLparmID As New SQLiteParameter("@EmployeeID", PhotoId)
                    SQLparmID.DbType = DbType.String
                    SQLparmID.Value = PhotoId
                    SQLcommand.Parameters.Add(SQLparmID)

                    Dim SQLparm As New SQLiteParameter("@Photo1", photo)
                    SQLparm.DbType = DbType.Binary
                    SQLparm.Value = photo
                    SQLcommand.Parameters.Add(SQLparm)

                    SQLcommand.ExecuteNonQuery()

                    bReturn = True
                Else
                    bReturn = False
                End If
            End Using
        Catch eX As System.Exception
            MessageBox.Show(eX.Message.ToString, "Error in database", MessageBoxButtons.OK, MessageBoxIcon.Error)
            bReturn = False
        End Try
        Return bReturn
    End Function
    Friend Function ViewPhoto_Resize(ByVal EmployeeID As String, ByVal picPhoto As DevExpress.XtraEditors.PictureEdit)
        Dim bReturn As Boolean
        Dim sSQL As String = "SELECT Photo FROM Photos WHERE PhotoID='" & EmployeeID & "'"
        Try
            Using SQLConnect As New SQLite.SQLiteConnection(g_constring)
                picPhoto.Image = Nothing
                SQLConnect.Open()
                If SQLConnect.State = ConnectionState.Open Then
                    Dim SQLcommand As New SQLiteCommand(SQLConnect)
                    SQLcommand = SQLConnect.CreateCommand
                    SQLcommand.CommandText = sSQL
                    SQLcommand.ExecuteNonQuery()

                    Using SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()
                        While SQLreader.Read()
                            picPhoto.Image = ResizeImageWithAspect(ByteToImage(SQLreader("Photo")), picPhoto.Width)
                        End While
                    End Using
                    bReturn = True
                Else
                    bReturn = False
                End If
            End Using
        Catch eX As System.Exception
            MessageBox.Show(eX.Message.ToString, "Error in database", MessageBoxButtons.OK, MessageBoxIcon.Error)
            bReturn = False

        End Try
        Return bReturn
    End Function
    Friend Function UserFile(ByVal _UserID As String) As DataTable
        Dim dt As DataTable = Nothing
        Dim sSQL As String = "SELECT UserID,UserName,UserFullName,LastName,MI,UserLevel,Last_Login,Date_Added FROM MasterUser WHERE UserID='" & _UserID & "'"
        Try
            Using SQLconnet As New SQLiteConnection(g_constring)
                SQLconnet.Open()
                Using SQLAdapter As New SQLiteDataAdapter(sSQL, SQLconnet)
                    Dim ds As New DataSet
                    SQLAdapter.Fill(ds)
                    dt = ds.Tables(0)
                    Dim dr As DataRow
                    If dt.Rows.Count <> 0 Then
                        dr = dt.Rows(0)
                        _UserID = dr.Item("UserID").ToString.Trim
                        _UserName = dr.Item("UserName").ToString.Trim
                        _UserFullName = dr.Item("UserFullName").ToString.Trim
                        _LastName = dr.Item("LastName").ToString.Trim
                        _MI = dr.Item("MI").ToString.Trim
                        _UserLevel = dr.Item("UserLevel").ToString.Trim
                        _LastLogin = dr.Item("Last_Login").ToString.Trim
                        _DateAdded = dr.Item("Date_Added").ToString.Trim

                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dt
    End Function

    Protected Friend Function CreateNewTranNo(ByVal xTranDate As String) As String
        Dim sReturn As String = xTranDate & ".00000"
        Try
            Using SQLconnect As New SQLite.SQLiteConnection(g_constring)
                SQLconnect.Open()
                Dim n As Integer = 0
                Dim zSQL As New System.Text.StringBuilder
                zSQL.AppendLine("SELECT UserID   ")
                zSQL.AppendLine("FROM     MasterUser ")
                zSQL.AppendLine("WHERE    Date_Added = @TranDate ")
                zSQL.AppendLine("ORDER BY Date_Added, UserID DESC LIMIT 1 ")

                Dim SQLcommand As New SQLiteCommand(SQLconnect)
                SQLcommand = SQLconnect.CreateCommand
                SQLcommand.CommandText = zSQL.ToString
                SQLcommand.Parameters.Add(New SQLiteParameter("@TranDate", xTranDate))
                Dim ending As String() = Split(xTranDate, "/")
                Dim x As String = ending(0) + ending(1) + ending(2)
                Dim sTranNo As String = ""
                sTranNo = SQLcommand.ExecuteScalar
                If sTranNo Is Nothing OrElse sTranNo.Trim.Length = 0 Then
                    'If sTranNo = 0 Then
                    sTranNo = "UNo." & x & "." & "00001"
                Else
                    sTranNo = sTranNo.Substring(sTranNo.LastIndexOf(".") + 1, 5)
                    sTranNo = CType(sTranNo, Int32) + 1
                    sTranNo = "UNo." & x & "." & sTranNo.PadLeft(5, "0")
                End If
                sReturn = sTranNo

            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return sReturn
    End Function
End Class