
Imports MySql.Data.MySqlClient
Imports Microsoft.Reporting.WinForms
Imports DevExpress.XtraEditors
Imports System.Security.Cryptography
Imports System.Text
Imports System.IO
Imports System.Net

Module dbUtilities
    Public sqlDT As New DataTable
    Public sqlSTR As String
    Public CnString As String = My.Settings.SQL_CONNECTION_STRINGS
    Public xUser_ID As Integer
    Public xUserName As String
    Public xcountx As Integer
    Private param_CompanyName As String
    Private param_Address As String
    Private param_Phone As String
    Private param_ReceiptHeader As String
    Private param_ReceiptFooter As String
    Private param_CompanyLogoFileName As String
    Public Function InitializeDatabaseConnection() As Boolean
        Try
            Dim sqlCon As New MySqlConnection
            sqlCon.ConnectionString = CnString
            sqlCon.Open()
            InitializeDatabaseConnection = True
            sqlCon.Close()
        Catch ex As Exception
            InitializeDatabaseConnection = False
            MsgBox("Error : " & ex.Message)
        End Try
    End Function
    Public Function ExecuteSQLQuery(ByVal SQLQuery As String) As DataTable
        Try
            Dim sqlCon As New MySqlConnection(CnString)
            Dim sqlDA As New MySqlDataAdapter(SQLQuery, sqlCon)
            Dim sqlCB As New MySqlCommandBuilder(sqlDA)
            sqlDT.Reset()
            sqlDA.Fill(sqlDT)
        Catch ex As Exception
            MsgBox("Error : " & ex.Message)
        End Try
        Return sqlDT
    End Function

    Public Sub FillDataGrid(ByVal sql As String, ByVal dgv As DataGridView)
        Dim conn As MySqlConnection = New MySqlConnection(CnString)
        Try
            conn.Open()
            Dim cmd As MySqlCommand = New MySqlCommand(sql, conn)
            Dim adp As New MySqlDataAdapter
            Dim dt As New DataTable
            adp.SelectCommand = cmd
            adp.Fill(dt)
            dgv.DataSource = dt
            adp.Dispose()
            cmd.Dispose()
        Catch ex As Exception
            MsgBox("Error:" & ex.ToString)
        Finally
            conn.Close()
        End Try
    End Sub
    Public Sub FillDataComboBox(ByVal sql As String, ByVal Value_Member As String, ByVal Display_Member As String, ByVal combo As System.Windows.Forms.ComboBox)
        Dim dt As DataTable = New DataTable()
        Using connection = New MySqlConnection(CnString)
            Using cmd = New MySqlCommand(sql, connection)
                connection.Open()
                Try
                    dt.Load(cmd.ExecuteReader())
                Catch e As MySqlException
                    MessageBox.Show(" Error : " & e.ToString())
                End Try
            End Using
        End Using
        combo.DataSource = dt
        combo.ValueMember = Value_Member
        combo.DisplayMember = Display_Member
    End Sub
    Public Function num_fltr(ByVal value As String)
        If Not IsNumeric(value) Then
            Return 0
        Else
            Return value
        End If
    End Function

    Public Function str_repl(ByVal str As String)
        Return Replace(Replace(Replace(str, "'", ""), ",", ","), "`", "")
    End Function

    Public Sub SaveFingerHashCode(ByVal EMP_ID As Double, ByVal EmpFingerHashCode As String)
        Dim conn As MySqlConnection = New MySqlConnection(CnString)
        Try
            conn.Open()
            Dim Sql As String = "INSERT INTO  Employee_Fingerprint (FingerHashCode, EMPLOYEE_ID) VALUES (@EmpFingerHashCode, @EMP_ID)  "
            Dim cmd As MySqlCommand = New MySqlCommand(Sql)
            cmd.Parameters.AddWithValue("@EmpFingerHashCode", EmpFingerHashCode)
            cmd.Parameters.AddWithValue("@EMP_ID", EMP_ID)
            cmd.Connection = conn
            cmd.ExecuteReader()
            conn.Close()
        Catch ex As Exception
            MsgBox("Error:" & ex.ToString)
        End Try
    End Sub
    Public Sub UpdateFingerHashCode(ByVal EMP_ID As Double, ByVal EmpFingerHashCode As String)
        Dim conn As MySqlConnection = New MySqlConnection(CnString)
        Try
            conn.Open()
            Dim Sql As String = "UPDATE Employee_Fingerprint  SET FingerHashCode=@EmpFingerHashCode WHERE EMPLOYEE_ID=@EMP_ID "
            Dim cmd As MySqlCommand = New MySqlCommand(Sql)
            cmd.Parameters.AddWithValue("@EmpFingerHashCode", EmpFingerHashCode)
            cmd.Parameters.AddWithValue("@EMP_ID", EMP_ID)
            cmd.Connection = conn
            cmd.ExecuteReader()
            conn.Close()
        Catch ex As Exception
            MsgBox("Error:" & ex.ToString)
        End Try
    End Sub

    Public Sub LoadCompanyProfile()
        ExecuteSQLQuery("SELECT  *  FROM  CompanyProfile")
        If sqlDT.Rows.Count > 0 Then
            param_CompanyName = sqlDT.Rows(0)("CompanyName")
            param_Address = sqlDT.Rows(0)("Address")
            param_Phone = sqlDT.Rows(0)("Phone")
            param_ReceiptHeader = sqlDT.Rows(0)("ReceiptHeader")
            param_ReceiptFooter = sqlDT.Rows(0)("ReceiptFooter")
            param_CompanyLogoFileName = sqlDT.Rows(0)("CompanyLogoFileName")
        Else
            param_CompanyName = "Company Name"
            param_Address = "Address"
            param_Phone = "Phone"
            param_ReceiptHeader = "Receipt Header"
            param_ReceiptFooter = "Receipt Footer"
            param_CompanyLogoFileName = "-"
        End If
    End Sub
    Public Sub PrintListOfEmployee(ByVal sql As String)
        LoadCompanyProfile()
        Dim frmReportViewer As New frmReportViewer
        Dim rptDataSource As New ReportDataSource
        Try
            With frmReportViewer.ReportViewer1.LocalReport
                .ReportPath = "Reports\EmployeeList.rdlc"
                .DataSources.Clear()
            End With
            Dim ds As New PayrollManagementSystem.dsPayroll
            Dim da As MySqlDataAdapter = New MySqlDataAdapter()
            Dim cmd As MySqlCommand
            cmd = New MySqlCommand(sql, New MySqlConnection(CnString))
            da = New MySqlDataAdapter(cmd)
            da.Fill(ds.Employee_Information)
            Dim CompanyName As ReportParameter = New ReportParameter("CompanyName", param_CompanyName)
            Dim Address As ReportParameter = New ReportParameter("Address", param_Address)
            Dim Phone As ReportParameter = New ReportParameter("Phone", param_Phone)
            Dim ReceiptHeader As ReportParameter = New ReportParameter("ReceiptHeader", param_ReceiptHeader)
            Dim ReceiptFooter As ReportParameter = New ReportParameter("ReceiptFooter", param_ReceiptFooter)
            Dim CompanyLogoFileName As ReportParameter = New ReportParameter("CompanyLogoFileName", "file:///" + Application.StartupPath + "\Upload\Company\" + param_CompanyLogoFileName)
            frmReportViewer.ReportViewer1.LocalReport.EnableExternalImages = True
            frmReportViewer.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {CompanyName, Address, Phone, ReceiptHeader, ReceiptFooter, CompanyLogoFileName})
            rptDataSource = New ReportDataSource("Employee_Information", ds.Tables("Employee_Information"))
            frmReportViewer.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
            frmReportViewer.Show()
        Catch ex As Exception
            MsgBox("Error:" & ex.ToString)
        End Try
    End Sub
    Public Sub PrintEmployeeAttendanceSummary(ByVal sql As String, ByVal CustomParam As String)
        LoadCompanyProfile()
        Dim frmReportViewer As New frmReportViewer
        Dim rptDataSource As New ReportDataSource
        Try
            With frmReportViewer.ReportViewer1.LocalReport
                .ReportPath = "Reports\Attendance.rdlc"
                .DataSources.Clear()
            End With
            Dim ds As New PayrollManagementSystem.dsPayroll
            Dim da As MySqlDataAdapter = New MySqlDataAdapter()
            Dim cmd As MySqlCommand
            cmd = New MySqlCommand(sql, New MySqlConnection(CnString))
            da = New MySqlDataAdapter(cmd)
            da.Fill(ds.AttendanceSummary)
            Dim CustomParameter As ReportParameter = New ReportParameter("CustomParameter", CustomParam)
            Dim CompanyName As ReportParameter = New ReportParameter("CompanyName", param_CompanyName)
            Dim Address As ReportParameter = New ReportParameter("Address", param_Address)
            Dim Phone As ReportParameter = New ReportParameter("Phone", param_Phone)
            Dim ReceiptHeader As ReportParameter = New ReportParameter("ReceiptHeader", param_ReceiptHeader)
            Dim ReceiptFooter As ReportParameter = New ReportParameter("ReceiptFooter", param_ReceiptFooter)
            Dim CompanyLogoFileName As ReportParameter = New ReportParameter("CompanyLogoFileName", "file:///" + Application.StartupPath + "\Upload\Company\" + param_CompanyLogoFileName)
            frmReportViewer.ReportViewer1.LocalReport.EnableExternalImages = True
            frmReportViewer.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {CompanyName, Address, Phone, ReceiptHeader, ReceiptFooter, CompanyLogoFileName, CustomParameter})
            rptDataSource = New ReportDataSource("AttendanceSummary", ds.Tables("AttendanceSummary"))
            frmReportViewer.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
            frmReportViewer.Show()
        Catch ex As Exception
            MsgBox("Error:" & ex.ToString)
        End Try
    End Sub
    Public Sub PrintEmployeeAllowances(ByVal sql As String, ByVal CustomParam As String)
        LoadCompanyProfile()
        Dim frmReportViewer As New frmReportViewer
        Dim rptDataSource As New ReportDataSource
        Try
            With frmReportViewer.ReportViewer1.LocalReport
                .ReportPath = "Reports\EmployeeAllowance.rdlc"
                .DataSources.Clear()
            End With
            Dim ds As New PayrollManagementSystem.dsPayroll
            Dim da As MySqlDataAdapter = New MySqlDataAdapter()
            Dim cmd As MySqlCommand
            cmd = New MySqlCommand(sql, New MySqlConnection(CnString))
            da = New MySqlDataAdapter(cmd)
            da.Fill(ds.Allowance)
            Dim CustomParameter As ReportParameter = New ReportParameter("CustomParameter", CustomParam)
            Dim CompanyName As ReportParameter = New ReportParameter("CompanyName", param_CompanyName)
            Dim Address As ReportParameter = New ReportParameter("Address", param_Address)
            Dim Phone As ReportParameter = New ReportParameter("Phone", param_Phone)
            Dim ReceiptHeader As ReportParameter = New ReportParameter("ReceiptHeader", param_ReceiptHeader)
            Dim ReceiptFooter As ReportParameter = New ReportParameter("ReceiptFooter", param_ReceiptFooter)
            Dim CompanyLogoFileName As ReportParameter = New ReportParameter("CompanyLogoFileName", "file:///" + Application.StartupPath + "\Upload\Company\" + param_CompanyLogoFileName)
            frmReportViewer.ReportViewer1.LocalReport.EnableExternalImages = True
            frmReportViewer.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {CompanyName, Address, Phone, ReceiptHeader, ReceiptFooter, CompanyLogoFileName, CustomParameter})
            rptDataSource = New ReportDataSource("Allowance", ds.Tables("Allowance"))
            frmReportViewer.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
            frmReportViewer.Show()
        Catch ex As Exception
            MsgBox("Error:" & ex.ToString)
        End Try
    End Sub
    Public Sub PrintEmployeeOvertime(ByVal sql As String, ByVal CustomParam As String)
        LoadCompanyProfile()
        Dim frmReportViewer As New frmReportViewer
        Dim rptDataSource As New ReportDataSource
        Try
            With frmReportViewer.ReportViewer1.LocalReport
                .ReportPath = "Reports\EmployeeOvertime.rdlc"
                .DataSources.Clear()
            End With
            Dim ds As New PayrollManagementSystem.dsPayroll
            Dim da As MySqlDataAdapter = New MySqlDataAdapter()
            Dim cmd As MySqlCommand
            cmd = New MySqlCommand(sql, New MySqlConnection(CnString))
            da = New MySqlDataAdapter(cmd)
            da.Fill(ds.Overtime)
            Dim CustomParameter As ReportParameter = New ReportParameter("CustomParameter", CustomParam)
            Dim CompanyName As ReportParameter = New ReportParameter("CompanyName", param_CompanyName)
            Dim Address As ReportParameter = New ReportParameter("Address", param_Address)
            Dim Phone As ReportParameter = New ReportParameter("Phone", param_Phone)
            Dim ReceiptHeader As ReportParameter = New ReportParameter("ReceiptHeader", param_ReceiptHeader)
            Dim ReceiptFooter As ReportParameter = New ReportParameter("ReceiptFooter", param_ReceiptFooter)
            Dim CompanyLogoFileName As ReportParameter = New ReportParameter("CompanyLogoFileName", "file:///" + Application.StartupPath + "\Upload\Company\" + param_CompanyLogoFileName)
            frmReportViewer.ReportViewer1.LocalReport.EnableExternalImages = True
            frmReportViewer.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {CompanyName, Address, Phone, ReceiptHeader, ReceiptFooter, CompanyLogoFileName, CustomParameter})
            rptDataSource = New ReportDataSource("Overtime", ds.Tables("Overtime"))
            frmReportViewer.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
            frmReportViewer.Show()
        Catch ex As Exception
            MsgBox("Error:" & ex.ToString)
        End Try
    End Sub
    Public Sub PrintSalarySlip(ByVal sql As String)
        LoadCompanyProfile()
        Dim frmReportViewer As New frmReportViewer
        Dim rptDataSource As New ReportDataSource
        Try
            With frmReportViewer.ReportViewer1.LocalReport
                .ReportPath = "Reports\PaySlip.rdlc"
                .DataSources.Clear()
            End With
            Dim ds As New PayrollManagementSystem.dsPayroll
            Dim da As MySqlDataAdapter = New MySqlDataAdapter()
            Dim cmd As MySqlCommand
            cmd = New MySqlCommand(sql, New MySqlConnection(CnString))
            da = New MySqlDataAdapter(cmd)
            da.Fill(ds.SalarySlip)
            Dim CompanyName As ReportParameter = New ReportParameter("CompanyName", param_CompanyName)
            Dim Address As ReportParameter = New ReportParameter("Address", param_Address)
            Dim Phone As ReportParameter = New ReportParameter("Phone", param_Phone)
            Dim ReceiptHeader As ReportParameter = New ReportParameter("ReceiptHeader", param_ReceiptHeader)
            Dim ReceiptFooter As ReportParameter = New ReportParameter("ReceiptFooter", param_ReceiptFooter)
            Dim CompanyLogoFileName As ReportParameter = New ReportParameter("CompanyLogoFileName", "file:///" + Application.StartupPath + "\Upload\Company\" + param_CompanyLogoFileName)
            frmReportViewer.ReportViewer1.LocalReport.EnableExternalImages = True
            frmReportViewer.ReportViewer1.LocalReport.SetParameters(New ReportParameter() {CompanyName, Address, Phone, ReceiptHeader, ReceiptFooter, CompanyLogoFileName})
            rptDataSource = New ReportDataSource("SalarySlip", ds.Tables("SalarySlip"))
            frmReportViewer.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
            frmReportViewer.Show()
        Catch ex As Exception
            MsgBox("Error:" & ex.ToString)
        End Try
    End Sub
    Public Sub FillDataComboBobox11(ByVal sql As String, ByVal Value_Member As String, ByVal Display_Member As String, ByVal combo1 As LookUpEdit)
        Dim dt As DataTable = New DataTable()
        Using connection = New MySqlConnection(CnString)
            Using cmd = New MySqlCommand(sql, connection)
                connection.Open()
                Try
                    dt.Load(cmd.ExecuteReader())
                Catch e As MySqlException
                    MessageBox.Show(" Error : " & e.ToString())
                End Try
            End Using
        End Using
        combo1.Properties.DataSource = dt
        combo1.Properties.ValueMember = Value_Member
        combo1.Properties.DisplayMember = Display_Member
    End Sub
    Public Function Encrypt(clearText As String) As String
        Dim EncryptionKey As String = "MAKV2SPBNI99212"
        Dim clearBytes As Byte() = Encoding.Unicode.GetBytes(clearText)
        Using encryptor As Aes = Aes.Create()
            Dim pdb As New Rfc2898DeriveBytes(EncryptionKey, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D,
         &H65, &H64, &H76, &H65, &H64, &H65,
         &H76})
            encryptor.Key = pdb.GetBytes(32)
            encryptor.IV = pdb.GetBytes(16)
            Using ms As New MemoryStream()
                Using cs As New CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write)
                    cs.Write(clearBytes, 0, clearBytes.Length)
                    cs.Close()
                End Using
                clearText = Convert.ToBase64String(ms.ToArray())
            End Using
        End Using
        Return clearText
    End Function

    Public Function Decrypt(cipherText As String) As String
        Dim EncryptionKey As String = "MAKV2SPBNI99212"
        Dim cipherBytes As Byte() = Convert.FromBase64String(cipherText)
        Using encryptor As Aes = Aes.Create()
            Dim pdb As New Rfc2898DeriveBytes(EncryptionKey, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D,
             &H65, &H64, &H76, &H65, &H64, &H65,
             &H76})
            encryptor.Key = pdb.GetBytes(32)
            encryptor.IV = pdb.GetBytes(16)
            Using ms As New MemoryStream()
                Using cs As New CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write)
                    cs.Write(cipherBytes, 0, cipherBytes.Length)
                    cs.Close()
                End Using
                cipherText = Encoding.Unicode.GetString(ms.ToArray())
            End Using
        End Using
        Return cipherText
    End Function

    Public Function Base64Decode(ByVal base64EncodedData As String) As String
        Dim base64EncodedBytes = System.Convert.FromBase64String(base64EncodedData)
        Return System.Text.Encoding.UTF8.GetString(base64EncodedBytes)
    End Function

    Public Function Base64Encode(ByVal plainText As String) As String
        Dim plainTextBytes = System.Text.Encoding.UTF8.GetBytes(plainText)
        Return System.Convert.ToBase64String(plainTextBytes)
    End Function


    Public Sub FtpUploadFile(ByVal filetoupload As String, ByVal ftpuri As String, ByVal ftpusername As String, ByVal ftppassword As String)
        ' Create a web request that will be used to talk with the server and set the request method to upload a file by ftp.
        Dim ftpRequest As FtpWebRequest = CType(WebRequest.Create(ftpuri), FtpWebRequest)

        Try
            ftpRequest.Method = WebRequestMethods.Ftp.UploadFile

            ' Confirm the Network credentials based on the user name and password passed in.
            ftpRequest.Credentials = New NetworkCredential(ftpusername, ftppassword)

            ' Read into a Byte array the contents of the file to be uploaded 
            Dim bytes() As Byte = System.IO.File.ReadAllBytes(filetoupload)

            ' Transfer the byte array contents into the request stream, write and then close when done.
            ftpRequest.ContentLength = bytes.Length
            Using UploadStream As Stream = ftpRequest.GetRequestStream()
                UploadStream.Write(bytes, 0, bytes.Length)
                UploadStream.Close()
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try

        ' MessageBox.Show("Process Complete")
    End Sub

    Public Sub FTPDownloadFile(ByVal downloadpath As String, ByVal ftpuri As String, ByVal ftpusername As String, ByVal ftppassword As String)
        'Create a WebClient.
        Dim request As New WebClient()

        ' Confirm the Network credentials based on the user name and password passed in.
        request.Credentials = New NetworkCredential(ftpusername, ftppassword)

        'Read the file data into a Byte array
        Dim bytes() As Byte = request.DownloadData(ftpuri)

        Try
            '  Create a FileStream to read the file into
            Dim DownloadStream As FileStream = IO.File.Create(downloadpath)
            '  Stream this data into the file
            DownloadStream.Write(bytes, 0, bytes.Length)
            '  Close the FileStream
            DownloadStream.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try

        'MessageBox.Show("Process Complete")

    End Sub
End Module
