Public Class Pesquisa

    Dim aidtra(1) As Integer

    Dim anrpm(1) As String

    Dim adte(1) As DateTime

    Dim adts(1) As DateTime

    Dim aservico(1) As String

    Dim adesconto(1) As String

    Dim atotalhoras(1) As string

    Dim pegatotal As String

    Dim vnomepega As String

    Private Sub Pesquisa_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        PopulaCombo()

    End Sub

    Private Sub PopulaCombo()

        Dim cs As String = My.Settings.PontoConnectionString

        Dim conect As New OleDb.OleDbConnection

        conect.ConnectionString = cs

        Try

            conect.Open()

            Dim selcom As New OleDb.OleDbCommand

            selcom.Connection = conect

            selcom.CommandText = "SELECT NRPM from Servidores order by NRPM"

            Dim vleitor As OleDb.OleDbDataReader

            vleitor = selcom.ExecuteReader()

            Dim c As Integer = 0

            cmbNPM.Items.Clear()

            Do While vleitor.Read

                c = c + 1

                ReDim Preserve anrpm(c)

                anrpm(c) = vleitor("NRPM")

                cmbNPM.Items.Add(anrpm(c))

            Loop

            cmbNPM.Text = "Escolha..."

            conect.Close()

        Catch ex As Exception

            MessageBox.Show("Erro de carregamento do combobox!" & vbCrLf & ex.Message, "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub

    Private Sub btnProcessa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcessa.Click

        Dim cs As String = My.Settings.PontoConnectionString

        Dim conect As New OleDb.OleDbConnection

        Dim NPOLICIA As String

        NPOLICIA = cmbNPM.Text

        conect.ConnectionString = cs

        Try

            conect.Open()

            Dim selcom As New OleDb.OleDbCommand

            selcom.Connection = conect

            If CDate(dtpi.Text) > CDate(dtpf.Text) Then

                MessageBox.Show("A data inicial não pode ser maior que a final.", "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error)

                dtpi.Focus()

                Exit Sub

            End If

            '-----------------------------------------------------------------------

            selcom.CommandText = "SELECT NRPM from Servidores Where NRPM = '" & cmbNPM.Text & "'"

            Dim vexiste As String = selcom.ExecuteScalar

            If Not vexiste = cmbNPM.Text Then

                MessageBox.Show("ERRO. Servidor não está cadastrado no sistema.", "Alerta", MessageBoxButtons.OK, MessageBoxIcon.Warning)

                Return

            End If

            '------------------------------------------------------------------------

            selcom.CommandText = "SELECT NOME from Servidores Where NRPM = '" & cmbNPM.Text & "'"

            Dim vnome As String = selcom.ExecuteScalar

            selcom.CommandText = "SELECT  PGRAD from Servidores Where NRPM = '" & cmbNPM.Text & "'"

            Dim vgrad As String = selcom.ExecuteScalar

            selcom.CommandText = "SELECT  NRPM from Servidores Where NRPM = '" & cmbNPM.Text & "'"

            Dim vnrpm As String = selcom.ExecuteScalar

            lblNRPM.Text = vnrpm

            lblNome.Text = vnome

            lblPgrad.Text = vgrad

            vnomepega = lblNome.Text

            '------------------------------------------------------------------------

            selcom.CommandText = "select * from Horas where DATA_ENTRADA>= #" + dtpi.Value.ToString("MM/dd/yyyy 00:00:00") + "# AND DATA_SAIDA <= #" + dtpf.Value.ToString("MM/dd/yyyy 23:59:59") + "# AND NRPM =  '" & NPOLICIA & "' order by DATA_ENTRADA"

            Dim vleitor As OleDb.OleDbDataReader

            vleitor = selcom.ExecuteReader()

            Dim c As Integer = 0

            dgvTrabalho.Rows.Clear()

            Do While vleitor.Read

                c = c + 1

                ReDim Preserve anrpm(c)

                ReDim Preserve aidtra(c)

                ReDim Preserve adte(c)

                ReDim Preserve adts(c)

                ReDim Preserve aservico(c)

                ReDim Preserve adesconto(c)

                ReDim Preserve atotalhoras(c)

                anrpm(c) = vleitor("NRPM")

                aidtra(c) = vleitor("ID")

                adte(c) = vleitor("DATA_ENTRADA")

                adts(c) = vleitor("DATA_SAIDA")

                aservico(c) = vleitor("SERVICO")

                adesconto(c) = vleitor("DESCONTO")

                atotalhoras(c) = vleitor("TOTAL_HORAS")

                dgvTrabalho.Rows.Add(anrpm(c), adte(c), adts(c), aservico(c), adesconto(c), atotalhoras(c))

            Loop

            calcula_hora()

            dgvTrabalho.Rows.Add("Total de Horas:")

            dgvTrabalho.Rows(c).Cells(5).Value = pegatotal

            ToolStripStatusLabel1.Text = "Total de registros retornados na pesquisa: " & dgvTrabalho.RowCount - 1

            conect.Close()

        Catch ex As Exception

            MessageBox.Show("Erro na execução da pesquisa!" & vbCrLf & ex.Message, "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub

    Private Sub btnSair_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSair.Click

        Me.Close()

    End Sub

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click

        If (dgvTrabalho.RowCount = 0) Then

            MessageBox.Show("Nenhum dado para exportar para o Excel", "Controle de Horas Trabalhadas", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Else

            ExportToExcel(Me.dgvTrabalho, xlsOption.xlsOpen)

        End If

    End Sub

    Enum xlsOption

        xlsSaveAs

        xlsOpen

    End Enum

    ''' <summary>

    ''' Exporta a informação de uma DataGridView para o Microsoft Excel

    ''' </summary>

    ''' <param name="dgvName">Nome da DataGridView </param>

    ''' <param name="option">SaveAs ou Open</param>

    ''' <param name="fileName">Nome completo do ficheiro</param>

    Public Sub ExportToExcel(ByVal dgvName As DataGridView, ByVal [option] As xlsOption, Optional ByVal fileName As String = "")

        Dim objExcelApp As New Excel.Application()

        Dim objExcelBook As Excel.Workbook

        Dim objExcelSheet As Excel.Worksheet

        Try

            ' Se foi seleccionada a opção xlsSaveAs e não foi indicado ficheiro

            If [option] = xlsOption.xlsSaveAs And fileName = String.Empty Then

                MessageBox.Show("É necessário indicar um nome para o ficheiro")

                Exit Sub

            End If

            ' Altera o tipo/localização para Inglês. Existe incompatibilidade

            ' entre algumas versões de Excel vs Sistema Operativo

            Dim oldCI As CultureInfo = CurrentThread.CurrentCulture

            CurrentThread.CurrentCulture = New CultureInfo("en-US")

            ' Adiciona um workbook e activa a worksheet actual

            objExcelBook = objExcelApp.Workbooks.Add

            objExcelSheet = CType(objExcelBook.Worksheets(1), Excel.Worksheet)

            ' Ciclo nos cabeçalhos para escrever os títulos a bold/negrito

            Dim dgvColumnIndex As Int16 = 1

            For Each col As DataGridViewColumn In dgvName.Columns

                objExcelSheet.Cells(1, dgvColumnIndex) = col.HeaderText