Imports System.Collections.Generic
Imports fatd_faturamento.ENTIDADE
Imports fatd_faturamento.BLL.Repositorio
Imports VB = Microsoft.VisualBasic
Imports System.Configuration
Imports System.Text.RegularExpressions
Imports System.IO
Module ModuloGlobal

    Public gblnUsuario_Faturas_aberto_cortado As Boolean
    Public gblnUsuario_Faturas_todos_aberto As Boolean
    Public gblnUsuario_Faturas_pagas As Boolean
    Public gblnUsuario_Faturas_reserva_judice As Boolean
    Public gblnUsuario_Faturas_ultimos_consumos As Boolean
    Public gblnUsuario_Faturas_demonstrativo As Boolean
    Public gblnUsuario_Faturas_segunda_via As Boolean
    Public gblnUsuario_Faturas_pedido_prazo As Boolean
    Public gblnUsuario_Atendimento_comercial_salvar As Boolean
    Public gblnUsuario_Servicos_ordem_servico As Boolean
    Public gblnUsuario_Servicos_nis As Boolean
    Public gblnUsuario_Servicos_reclamacao As Boolean
    Public gblnUsuario_Servicos_convenios As Boolean
    Public gblnUsuario_Servicos_extrato As Boolean
    Public gblnUsuario_Servicos_historico As Boolean

    Public gintUsuario_NivelCaixa As Integer
    Public gstrDataBase As String = moduleGlobal.vMySqlDataBase


    Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Integer) As Integer
    Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Integer, ByVal lpSubKey As String, ByVal ulOptions As Integer, ByVal samDesired As Integer, ByRef phkResult As Integer) As Integer
    Declare Function RegQueryValueExString Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Integer, ByVal lpValueName As String, ByVal lpReserved As Integer, ByRef lpType As Integer, ByVal lpData As String, ByRef lpcbData As Integer) As Integer
    Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Integer, ByVal lpValueName As String, ByVal lpReserved As Integer, ByRef lpType As Integer, ByRef lpData As Integer, ByRef lpcbData As Integer) As Integer
    Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Integer, ByVal lpValueName As String, ByVal lpReserved As Integer, ByRef lpType As Integer, ByVal lpData As Integer, ByRef lpcbData As Integer) As Integer

    Structure PROCESS_INFORMATION
        Dim hProcess As Integer
        Dim hThread As Integer
        Dim dwProcessID As Integer
        Dim dwThreadID As Integer
    End Structure

    Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Integer, ByVal dwMilliseconds As Integer) As Integer
    Declare Function CreateProcessA Lib "kernel32" (ByVal lpApplicationName As Integer, ByVal lpCommandLine As String, ByVal lpProcessAttributes As Integer, ByVal lpThreadAttributes As Integer, ByVal bInheritHandles As Integer, ByVal dwCreationFlags As Integer, ByVal lpEnvironment As Integer, ByVal lpCurrentDirectory As Integer, ByRef lpStartupInfo As STARTUPINFO, ByRef lpProcessInformation As PROCESS_INFORMATION) As Integer
    Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Integer) As Integer
    Public Function PreparaCampo_(Campo As String, tipo_campo As Integer, comp As Integer, Caracter As String) As String

        Try

            Campo = Trim(Campo)

            'TIPO NUMERICO
            If tipo_campo = 0 Then
                Campo = Replace(Campo, ",", "")
                Campo = Replace(Campo, ".", "")

                If Len(Campo) > comp Then
                    PreparaCampo_ = Left$(Campo, comp)
                Else
                    PreparaCampo_ = New String(Caracter, comp - Len(Campo)) & Campo
                End If
            End If

            'TIPO ALFANUMÉRICO
            If tipo_campo = 1 Then
                If Len(Campo) > comp Then
                    PreparaCampo_ = Left$(Campo, comp)
                Else
                    PreparaCampo_ = Campo & New String(Caracter, comp - Len(Campo))
                End If
            End If

        Catch ex As Exception
            Exit Function
        End Try

    End Function
    Public Function Aliquota_PIS(ByVal adteBase As Date) As Double

        Dim lstrVlr As String
        Dim ldblRet As Double
        Dim lstrSql As String
        Dim dt As DataTable


        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        ldblRet = 0
        lstrSql = ""
        lstrSql = lstrSql & "  SELECT * "
        lstrSql = lstrSql & "    FROM `codigos` "
        lstrSql = lstrSql & "   WHERE `datanova` <= '" & Strings.Format(adteBase, "yyyy-MM-dd") & "' "
        lstrSql = lstrSql & "     AND `num` = '40PIS00001' "
        lstrSql = lstrSql & "ORDER BY `datanova` DESC, `num` ASC "
        lstrSql = lstrSql & "LIMIT 0, 1 "

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrVlr = Strings.Trim(IIf(IsDBNull(row.Item("valor")), "0.00", row.Item("valor")))
                    lstrVlr = IIf(IsNumeric(lstrVlr), lstrVlr, 0)
                    lstrVlr = Strings.Replace(lstrVlr, ",", ".")
                    ldblRet = Conversion.Val(lstrVlr)
                Next
            End If
            Return ldblRet
        Catch ex As Exception
            Exit Function
        End Try

    End Function

    Public Function TarifaIluminacaoPublica(ByVal ldblConsumoFaturado As Double, ByVal ldtDataLeituraAtual As Date, ByVal ldtDataLeituraAnter As Date, ByVal lStrCodigo As String) As Double

        Dim lstrVlr As String
        Dim ldblRet As Double
        Dim lstrSql As String
        Dim dt As DataTable


        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        lstrSql = ""
        lstrSql = lstrSql & "  SELECT * "
        lstrSql = lstrSql & "  FROM `tarifa`"
        'lstrSql = lstrSql & "  WHERE `limitkwh` >= " & ldblConsumoFaturado & ""
        lstrSql = lstrSql & "  WHERE num = '" & lStrCodigo & "'"
        'lstrSql = lstrSql & " " & lStrCodigo & ""
        If ldtDataLeituraAtual <> "01/01/1900" Then
            'lstrSql = lstrSql & "  AND `referencia` < '" & Strings.Format(ldtDataLeituraAtual, "yyyy-MM-dd") & "'"
            lstrSql = lstrSql & " ORDER BY referencia Desc "
        End If
        If ldtDataLeituraAnter <> "01/01/1900" Then
            'lstrSql = lstrSql & "  AND `referencia` > '" & Strings.Format(ldtDataLeituraAnter, "yyyy-MM-dd") & "'"
            lstrSql = lstrSql & " ORDER BY referencia "
        End If
        lstrSql = lstrSql & " LIMIT 1"

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrVlr = Strings.Trim(IIf(IsDBNull(row.Item("valor")), "0.00", row.Item("valor")))
                    lstrVlr = IIf(IsNumeric(lstrVlr), lstrVlr, 0)
                    lstrVlr = Strings.Replace(lstrVlr, ",", ".")
                    ldblRet = Conversion.Val(lstrVlr)
                Next
            End If
            Return ldblRet
        Catch ex As Exception
            Exit Function
        End Try

    End Function

    Public Function Aliquota_COFINS(ByVal adteBase As Date) As Double
        Dim lstrVlr As String
        Dim ldblRet As Double
        Dim lstrSql As String
        Dim dt As DataTable


        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        lstrSql = ""
        lstrSql = lstrSql & "SELECT * "
        lstrSql = lstrSql & "  FROM `codigos` "
        lstrSql = lstrSql & " WHERE `datanova` <= '" & Strings.Format(adteBase, "yyyy-MM-dd") & "' "
        lstrSql = lstrSql & "   AND `num` = '40COF00001' "
        lstrSql = lstrSql & "ORDER BY `datanova` DESC, `num` ASC "
        lstrSql = lstrSql & "LIMIT 0, 1 "

        Try
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrVlr = Strings.Trim(IIf(IsDBNull(row.Item("valor")), "0.00", row.Item("valor")))
                    lstrVlr = IIf(IsNumeric(lstrVlr), lstrVlr, 0)
                    lstrVlr = Strings.Replace(lstrVlr, ",", ".")
                    ldblRet = Conversion.Val(lstrVlr)
                Next
            End If
            Return ldblRet
        Catch ex As Exception
            Exit Function
        End Try


    End Function

    Public Function Aliquota_ICMS(ByVal astrUf As String,
                              ByVal adblConsumo As Double,
                              ByVal astrClasse As String,
                              ByVal adteFaturamento As Date,
                              Optional ByVal astrIsento As String = "N",
                              Optional ByVal astrInsc As String = "") As Double
        Dim lstrCodigo As String
        Dim lstrCodIcms As String
        Dim lintClasse As Integer
        Dim lstrClasse As String
        Dim lstrSubClas As String
        Dim ldblLimiteKWh As Double
        Dim ldblValorIcms As Double
        Dim lstrSql As String
        Dim dt As DataTable


        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados


        lstrClasse = String.Format(astrClasse, "0000")
        lstrSubClas = IIf(Strings.Left(lstrClasse, 1) = 6, Strings.Left(lstrClasse, 2), IIf(Val(Strings.Left(lstrClasse, 1)) < 4, 1, Strings.Left(lstrClasse, 1)))
        lstrCodigo = "40" & astrUf & "B00" & Left(lstrSubClas & "00", 2)
        lintClasse = CInt(lstrSubClas)

        lstrSql = ""
        lstrSql = lstrSql & "SELECT * "
        lstrSql = lstrSql & "  FROM `codigos` "
        lstrSql = lstrSql & " WHERE `datanova` <= '" & Strings.Format(adteFaturamento, "yyyy-MM-dd") & "' "
        lstrSql = lstrSql & "   AND `num` BETWEEN '" & Strings.Left(lstrCodigo, 9) & "0" & "' AND '" & Strings.Left(lstrCodigo, 9) & "9" & "'"
        If astrIsento = "S" Then
            lstrSql = lstrSql & "   AND `valor`+0 = 0 "
        End If
        lstrSql = lstrSql & "ORDER BY `datanova` DESC, `limitkwh` ASC, `num` ASC "

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrCodIcms = IIf(IsDBNull(row.Item("num")), lstrCodigo & "1", row.Item("num"))
                    ldblLimiteKWh = IIf(IsDBNull(row.Item("limitkwh")), 0, row.Item("limitkwh"))
                    ldblValorIcms = CDbl(Replace(IIf(IsDBNull(row.Item("valor")), 0, row.Item("valor")), ",", "."))
                    Select Case lintClasse
            'Industrial (0,1,2,3)
                        Case Is < 4
                            If lstrCodIcms = lstrCodigo & 1 Or lstrCodIcms = lstrCodigo & 2 Then
                                If adblConsumo <= ldblLimiteKWh Then
                                    Aliquota_ICMS = ldblValorIcms
                                    Exit For
                                    ' Call .MoveLast
                                End If
                            End If
            'Comercial Classe 4
                        Case Is = 4
                            If astrIsento = "N" Then
                                If lstrCodIcms = lstrCodigo & 1 Then
                                    If adblConsumo <= ldblLimiteKWh Then
                                        Aliquota_ICMS = ldblValorIcms
                                        Exit For
                                        '    Call .MoveLast
                                    End If
                                End If
                            Else
                                If lstrCodIcms = lstrCodigo & 2 Then
                                    If adblConsumo <= ldblLimiteKWh Then
                                        Aliquota_ICMS = ldblValorIcms
                                        Exit For
                                        '   Call .MoveLast
                                    End If
                                End If
                            End If
            'Rural Classe 5
                        Case Is = 5
                            If lstrCodIcms = lstrCodigo & 1 Or lstrCodIcms = lstrCodigo & 2 Then
                                If Len(astrInsc) > 0 And lstrCodIcms = lstrCodigo & 1 Then
                                    Aliquota_ICMS = ldblValorIcms
                                    Exit For
                                    'Call .MoveLast
                                Else
                                    If adblConsumo <= ldblLimiteKWh Then
                                        Aliquota_ICMS = ldblValorIcms
                                        Exit For
                                        '   Call .MoveLast
                                    End If
                                End If
                            End If
            'Poder Publico Municipal 61
                        Case Is = 61
                            If lstrCodIcms = lstrCodigo & 1 Or lstrCodIcms = lstrCodigo & 2 Then
                                If adblConsumo <= ldblLimiteKWh Then
                                    Aliquota_ICMS = ldblValorIcms
                                    Exit For
                                    'Call .MoveLast
                                End If
                            End If
            'Poder Publico Estadual 62
                        Case Is = 62
                            If astrIsento = "N" Then
                                If lstrCodIcms = lstrCodigo & 1 Then
                                    If adblConsumo <= ldblLimiteKWh Then
                                        Aliquota_ICMS = ldblValorIcms
                                        Exit For
                                        '   Call .MoveLast
                                    End If
                                End If
                            Else
                                If lstrCodIcms = lstrCodigo & 2 Then
                                    If adblConsumo <= ldblLimiteKWh Then
                                        Aliquota_ICMS = ldblValorIcms
                                        Exit For
                                        '  Call .MoveLast
                                    End If
                                End If
                            End If
            'Poder Publico Municipal 63
                        Case Is = 63
                            If lstrCodIcms = lstrCodigo & 1 Or lstrCodIcms = lstrCodigo & 2 Then
                                If adblConsumo <= ldblLimiteKWh Then
                                    Aliquota_ICMS = ldblValorIcms
                                    Exit For
                                    'Call .MoveLast
                                End If
                            End If
            'Iluminação Publica 64
                        Case Is = 64
                            If lstrCodIcms = lstrCodigo & 1 Or lstrCodIcms = lstrCodigo & 2 Then
                                If adblConsumo <= ldblLimiteKWh Then
                                    Aliquota_ICMS = ldblValorIcms
                                    Exit For
                                    'Call .MoveLast
                                End If
                            End If
            'Serviço Publico 7
                        Case Is = 7
                            If lstrCodIcms = lstrCodigo & 1 Or lstrCodIcms = lstrCodigo & 2 Then
                                If adblConsumo <= ldblLimiteKWh Then
                                    Aliquota_ICMS = ldblValorIcms
                                    Exit For
                                    'Call .MoveLast
                                End If
                            End If
            'Consumo Proprio 8
                        Case Is = 8
                            If lstrCodIcms = lstrCodigo & 1 Or lstrCodIcms = lstrCodigo & 2 Then
                                If adblConsumo <= ldblLimiteKWh Then
                                    Aliquota_ICMS = ldblValorIcms
                                    Exit For
                                    'Call .MoveLast
                                End If
                            End If
            'Resindencial 9
                        Case Is = 9
                            'If lstrCodIcms = lstrCodigo & 1 Or lstrCodIcms = lstrCodigo & 2 Or lstrCodIcms = lstrCodigo & 3 Then
                            If adblConsumo <= ldblLimiteKWh Then
                                Aliquota_ICMS = ldblValorIcms
                                Exit For
                                'Call MoveLast
                            End If
                            'End If
                    End Select

                Next
            End If

        Catch ex As Exception
            Exit Function
        End Try
    End Function

    Public Function GetMatriculaSocio(ByVal alngConsId As Long) As String
        Dim lstrSql As String
        Dim lstrMat As String
        Dim dt As DataTable

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        lstrMat = "0"
        lstrSql = ""
        lstrSql = lstrSql & "SELECT * "
        lstrSql = lstrSql & "  FROM `consum` "
        lstrSql = lstrSql & " WHERE `consum`.`consreg` = " & alngConsId

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrMat = Trim(IIf(IsDBNull(row.Item("matricula")), "0", row.Item("matricula")))
                    If Len(lstrMat) = 0 Then lstrMat = 0
                Next
            End If

        Catch ex As Exception
            Exit Function
        End Try

        GetMatriculaSocio = lstrMat

    End Function

    Public Function BuscaCodEnd(ByVal astrNomeCidade As String) As String
        'Função que retorna o código do IBGE de acordo com o nome da cidade passado no argumento
        Dim lstrRet As String
        Dim lstrSql As String
        Dim dt As DataTable

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        lstrRet = ""
        lstrSql = ""
        lstrSql = lstrSql & "SELECT * "
        lstrSql = lstrSql & "  FROM `munic` "
        lstrSql = lstrSql & " WHERE `nome` = '" & Strings.Replace(astrNomeCidade, "'", "\'") & "' "
        lstrSql = lstrSql & " LIMIT 0, 1 "

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrRet = Strings.Trim$(IIf(IsDBNull(row.Item("cod")), "000001", row.Item("cod")))
                Next
            End If

        Catch ex As Exception
            Exit Function
        End Try

        BuscaCodEnd = lstrRet

    End Function
    Public Function RetornaNomeBanco(ByVal alngBcoId As Long) As String
        Dim lstrRet As String
        Dim lstrSql As String
        Dim dt As DataTable

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        lstrSql = ""
        lstrSql = lstrSql & "SELECT * "
        lstrSql = lstrSql & "  FROM `bancos` "
        lstrSql = lstrSql & " WHERE `ban_id` = " & alngBcoId & " "

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrRet = Strings.Trim(IIf(IsDBNull(row.Item("nmbco")), "", row.Item("nmbco")))
                Next
            Else
                lstrRet = ""
            End If

        Catch ex As Exception
            Exit Function
        End Try

        RetornaNomeBanco = lstrRet

    End Function
    Public Function RetornaAgrupamentoFatura(ByVal astrAgrupaZona As String, ByVal astrAgrupaLivro As String, ByVal astrAgrupaConta As String) As String
        Dim lstrRet As String
        Dim lstrSql As String
        Dim dt As DataTable

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        lstrSql = ""
        lstrSql = lstrSql & "SELECT "
        lstrSql = lstrSql & "       `uc_faturamento`.`zona`, "
        lstrSql = lstrSql & "       `uc_faturamento`.`livro`, "
        lstrSql = lstrSql & "       `uc_faturamento`.`conta`, "
        lstrSql = lstrSql & "       `uc_faturamento`.`rota` "
        lstrSql = lstrSql & "  FROM `uc_faturamento` "
        lstrSql = lstrSql & " WHERE `uc_faturamento`.`zona`  = '" & astrAgrupaZona & "' "
        lstrSql = lstrSql & "   AND `uc_faturamento`.`livro` = '" & astrAgrupaLivro & "' "
        lstrSql = lstrSql & "   AND `uc_faturamento`.`conta` = '" & astrAgrupaConta & "' "

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrRet = Strings.Trim(IIf(IsDBNull(row.Item("rota")), "", row.Item("rota")))
                Next
            End If

        Catch ex As Exception
            Exit Function
        End Try

        RetornaAgrupamentoFatura = lstrRet

    End Function
    Public Function CalculaCodigosRegrasEspeciais(ByVal astrCodigo As String, ByVal alngCidade As Long, ByVal astrClasse As String, ByVal adblQtdConsumo As Double, ByVal adblVlrConsumo As Double, ByVal adteLeitura As Date, ByVal astrGrupo As String, ByVal ablnSocio As Boolean) As Double
        Dim lstrTip As String 'Tipo de calculo percentual ou valor fixo
        '
        Dim ldblIni As Double 'Faixa inicial de consumo
        Dim ldblFim As Double 'Faixa final de consumo
        Dim ldblVlr As Double 'Valor ou percentual a ser aplicado
        Dim ldblTet As Double 'Valor do teto quando for por percentual
        Dim ldblBas As String 'Valor auxiliar para ser usado como base de cálculo
        '
        Dim lintCla As Integer 'Código da classe
        Dim lintkWh As Integer 'se deve usar o valor do consumo no calculo
        Dim lintBxR As Integer 'se deve calcular para o baixa renda
        Dim lintSoc As Integer 'Se deve usar o valor da tarifa da iluminação pública rede para valor de base de cálculo
        Dim ldblRet As Double 'Valor de retorno
        '
        Dim lblnAux As Boolean
        Dim lstrSql As String
        Dim dt As DataTable

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        lintCla = Convert.ToInt32(Strings.Left(Strings.Trim(astrClasse), IIf(Strings.Left(Strings.Trim(astrClasse), 1) <> "6", 1, 2))) 'classes 0,1,2,3,4,5,61,62,63,64,7,8,9
        ldblRet = 0
        lstrSql = ""
        lstrSql = lstrSql & "   SELECT * "
        lstrSql = lstrSql & "     FROM `regras_especiais` "
        lstrSql = lstrSql & "    WHERE `codigo` = '" & astrCodigo & "' "
        lstrSql = lstrSql & "      AND `cidade` = " & alngCidade & " "
        lstrSql = lstrSql & "      AND `classe` = " & lintCla & " "
        lstrSql = lstrSql & "      AND `grupo` IN('T','" & astrGrupo & "') "
        lstrSql = lstrSql & "      AND `data` <= '" & Strings.Format(adteLeitura, "yyyy-MM-dd") & "' "
        lstrSql = lstrSql & "      AND ("
        lstrSql = lstrSql & "           (`tipo` = 'P' AND (`faixa_ini` >= 0 AND `faixa_fim` <= 999999999)) OR "
        lstrSql = lstrSql & "           (`tipo` = 'F' AND (" & Strings.Replace(adblQtdConsumo, ",", ".") & " BETWEEN `faixa_ini` AND `faixa_fim`)) "
        lstrSql = lstrSql & "          ) "
        lstrSql = lstrSql & "      AND " & Strings.Replace(adblQtdConsumo, ",", ".") & " BETWEEN `faixa_ini` AND `faixa_fim` "
        lstrSql = lstrSql & " ORDER BY `grupo` ASC, `data` DESC, faixa_fim DESC , faixa_ini DESC LIMIT 0, 1 "

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    ldblIni = Convert.ToDouble(IIf(IsDBNull(row.Item("faixa_ini")), 0, row.Item("faixa_ini")))
                    ldblFim = Convert.ToDouble(IIf(IsDBNull(row.Item("faixa_fim")), 999999999, row.Item("faixa_fim")))
                    lstrTip = Strings.UCase(Strings.Trim(IIf(IsDBNull(row.Item("tipo")), "F", row.Item("tipo"))))
                    ldblVlr = Convert.ToDouble(IIf(IsDBNull(row.Item("valor")), 0, row.Item("valor")))
                    ldblTet = Convert.ToDouble(IIf(IsDBNull(row.Item("valor_teto_fixo")), 0, row.Item("valor_teto_fixo")))
                    lintkWh = Convert.ToInt32(IIf(IsDBNull(row.Item("valor_consumo")), 0, row.Item("valor_consumo")))
                    lintBxR = Convert.ToInt32(IIf(IsDBNull(row.Item("baixa_renda")), 0, row.Item("baixa_renda")))
                    lintSoc = Convert.ToInt32(IIf(IsDBNull(row.Item("socio")), 0, row.Item("socio")))
                    lblnAux = True
                    If lintBxR = 0 And (astrClasse = "9104" Or astrClasse = "9105" Or astrClasse = "9106") Then
                        lblnAux = False
                    End If
                    If lintSoc = 1 And Not ablnSocio Then
                        lblnAux = False
                    End If
                    If lblnAux Then
                        '
                        If lstrTip = "P" Then
                            '
                            'caso for percentual
                            '
                            If adblQtdConsumo >= ldblIni Then
                                If adblQtdConsumo > ldblFim Then
                                    ldblBas = IIf(lintkWh = 0, ldblFim, adblVlrConsumo)
                                    ldblRet = Arredondar(ldblBas * (ldblVlr / 100), 2)
                                Else
                                    ldblBas = IIf(lintkWh = 0, adblQtdConsumo, adblVlrConsumo)
                                    ldblRet = Arredondar(ldblBas * (ldblVlr / 100), 2)
                                End If
                            End If
                            If ldblTet > 0 And (ldblRet > ldblTet) Then
                                ldblRet = Arredondar(ldblTet, 2)
                            End If
                        ElseIf lstrTip = "F" Then
                            '
                            'caso for valor fixo
                            '
                            If ldblRet = 0 Then
                                ldblRet = Arredondar(ldblVlr, 2)
                            End If
                            Exit For
                        End If
                    End If
                Next
            End If

        Catch ex As Exception
            Exit Function
        End Try

        CalculaCodigosRegrasEspeciais = ldblRet

    End Function

    'Public Sub GetTarifasComposicao(ByVal adteReferencia As Date, ByRef pdblEnergia As Double, ByRef pdblTransmissao As Double, ByRef pdblDistribuicao As Double, ByRef pdblEncargos As Double, ByRef pdblPerdas As Double, ByRef pdblTributosIcms As Double, ByRef pdblTributosPisCofins As Double)
    '    Dim lstrSql As String
    '    Dim dt As DataTable

    '    Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
    '    Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

    '    lstrSql = "SELECT * "
    '    lstrSql = lstrSql & "  FROM `tarifas_composicao` "
    '    lstrSql = lstrSql & " WHERE referencia <= '" & Strings.Format(adteReferencia, "yyyy-MM-dd") & "' "
    '    lstrSql = lstrSql & " LIMIT 0, 1"

    '    Try

    '        objAtualizaBancoDados.QUERY_NAME = lstrSql
    '        dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

    '        If dt.Rows.Count > 0 Then
    '            For Each row As DataRow In dt.Rows
    '                pdblEnergia = IIf(IsDBNull(row.item("energia_real")), 0, row.item("energia_real"))
    '                pdblTransmissao = IIf(IsDBNull(row.item("transmissao")), 0, row.item("transmissao"))
    '                pdblDistribuicao = IIf(IsDBNull(row.item("distribuicao")), 0, row.item("distribuicao"))
    '                pdblEncargos = IIf(IsDBNull(row.item("enc_setoriais")), 0, row.item("enc_setoriais"))
    '                pdblPerdas = IIf(IsDBNull(row.item("perdas")), 0, row.item("perdas"))
    '                pdblTributosIcms = IIf(IsDBNull(row.item("tributos_icms")), 0, row.item("tributos_icms"))
    '                pdblTributosPisCofins = IIf(IsDBNull(row.Item("tributos_pis_cofins")), 0, row.Item("tributos_pis_cofins"))
    '            Next
    '        End If
    '    Catch ex As Exception
    '        Exit Sub
    '    End Try

    'End Sub

    Public Function TarifaProporcionalBandeira(ByVal adteLeitAnterior As Date, ByVal adteLeitAtual As Date, ByVal aintTipoBandeira As Integer, ByVal adteBandeira As Date, Optional ByRef astrBanPeriodo As String = "", Optional ByVal adblClasse As Double = 0, Optional ByVal aintQtdeBandeira As Integer = 0) As Double
        Dim ldblTotTarifa As Double 'Resultado Final
        Dim ldblTotTarifaAtual As Double 'Resultado Atual
        Dim ldblTotTarifaAnter As Double 'Resultado Anterior
        Dim ldteTarAnter_BD As Date 'Data da tarifa anterior Bandeira
        Dim ldteTarAtual_BD As Date 'Data da tarifa atual Bandeira
        Dim llngDiasAtual As Long   'Qtde de dias da tarifa atual
        Dim llngDiasAnter As Long   'Qtde de dias da tarifa anterior
        Dim llngBanAnter As Long    'Tipo de bandeira
        Dim llngBanAtual As Long    'Tipo de bandeira
        Dim ldblBanAnter As Double  'valor de bandeira anterior
        Dim ldblBanAtual As Double  'valor de bandeira atual
        '
        Dim ldteLoop As Date
        Dim lstrSql As String
        Dim dt As DataTable
        Dim iCount As Integer


        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try

            If aintTipoBandeira <> 3 Or (aintTipoBandeira = 3 And adteBandeira > adteLeitAnterior And adteBandeira <= adteLeitAtual) Then
                If gdteDataBandeiraInicio <= gDtBcoDados Then
                    '
                    'Controle bandeiras
                    '1 = vermelha
                    '2 = amarela
                    '3 = verde

                    lstrSql = ""
                    lstrSql = lstrSql & "SELECT * "
                    lstrSql = lstrSql & "  FROM `bandeiras` "

                    If aintQtdeBandeira <> 0 Then
                        If adblClasse = 9102 Then
                            lstrSql = lstrSql & " WHERE `vigencia` <= '" & Strings.Format$(adteLeitAtual, "yyyy-MM-dd") & "' "
                            lstrSql = lstrSql & " AND `idtipo_bandeira` <> 5"
                            lstrSql = lstrSql & " ORDER BY `vigencia` DESC LIMIT 0, " & aintQtdeBandeira
                        Else
                            lstrSql = lstrSql & " WHERE `vigencia` <= '" & Strings.Format$(adteLeitAtual, "yyyy-MM-dd") & "' "
                            lstrSql = lstrSql & " ORDER BY `vigencia` DESC, `idtipo_bandeira` DESC LIMIT 0, " & aintQtdeBandeira
                        End If
                    Else
                        If adblClasse = 9102 Then
                            lstrSql = lstrSql & " WHERE `vigencia` <= '" & Strings.Format$(adteLeitAtual, "yyyy-MM-dd") & "' "
                            lstrSql = lstrSql & " AND `idtipo_bandeira` <> 5"
                            lstrSql = lstrSql & " ORDER BY `vigencia` DESC LIMIT 0, 2 "
                        Else
                            lstrSql = lstrSql & " WHERE `vigencia` <= '" & Strings.Format$(adteLeitAtual, "yyyy-MM-dd") & "' "
                            lstrSql = lstrSql & " ORDER BY `vigencia` DESC, `idtipo_bandeira` DESC LIMIT 0, 2 "
                        End If
                    End If
                    'lstrSql = lstrSql & " WHERE `vigencia` <= '" & Strings.Format$(adteLeitAtual, "yyyy-MM-dd") & "' "
                    'lstrSql = lstrSql & " ORDER BY `vigencia` DESC LIMIT 0, 2 "
                    objAtualizaBancoDados.QUERY_NAME = lstrSql
                    dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

                    If dt.Rows.Count > 0 Then
                        iCount = 1
                        For Each row As DataRow In dt.Rows
                            If iCount = 1 Then
                                ldteTarAtual_BD = IIf(IsDBNull(row.Item("vigencia")), "1951-01-01", row.Item("vigencia"))
                                'ldteTarAtual_BD = DateAdd("d", -1, ldteTarAtual_BD)
                                llngBanAtual = IIf(IsDBNull(row.Item("idtipo_bandeira")), 1, row.Item("idtipo_bandeira"))
                                ldblBanAtual = IIf(IsDBNull(row.Item("valor")), 0, row.Item("valor"))
                            ElseIf iCount = 2 Then 'And adblClasse = 9105 Then
                                ldteTarAnter_BD = IIf(IsDBNull(row.Item("vigencia")), "1951-01-01", row.Item("vigencia"))
                                llngBanAnter = IIf(IsDBNull(row.Item("idtipo_bandeira")), 1, row.Item("idtipo_bandeira"))
                                ldblBanAnter = IIf(IsDBNull(row.Item("valor")), 0, row.Item("valor"))
                                Exit For
                                'ElseIf iCount = 3 Then
                                '    ldteTarAnter_BD = IIf(IsDBNull(row.Item("vigencia")), "1951-01-01", row.Item("vigencia"))
                                '    llngBanAnter = IIf(IsDBNull(row.Item("idtipo_bandeira")), 1, row.Item("idtipo_bandeira"))
                                '    ldblBanAnter = IIf(IsDBNull(row.Item("valor")), 0, row.Item("valor"))
                                '    Exit For
                            End If
                            iCount = iCount + 1
                        Next
                    End If

                    ldblTotTarifa = 0

                    llngDiasAtual = DateDiff("d", ldteTarAtual_BD, adteLeitAtual) + 1
                    llngDiasAnter = DateDiff("d", adteLeitAnterior, ldteTarAtual_BD) - 1
                    astrBanPeriodo = ""
                    If llngDiasAtual > 0 Then
                        If llngBanAtual = 1 Then
                            astrBanPeriodo = "VM."
                        ElseIf llngBanAtual = 2 Then
                            astrBanPeriodo = "AM."
                        ElseIf llngBanAtual = 5 Then
                            astrBanPeriodo = "EH."
                        End If
                    End If
                    If llngDiasAnter > 0 Then
                        If llngBanAnter = 1 Then
                            astrBanPeriodo = astrBanPeriodo & IIf(Strings.Len(Strings.Trim(astrBanPeriodo)) > 0, " + ", "") & "VM."
                        ElseIf llngBanAnter = 2 Then
                            astrBanPeriodo = astrBanPeriodo & IIf(Strings.Len(Strings.Trim(astrBanPeriodo)) > 0, " + ", "") & "AM."
                        End If
                    End If
                    If Strings.Len(Strings.Trim(astrBanPeriodo)) > 0 Then astrBanPeriodo = " (" & astrBanPeriodo & " ) "

                    ldteLoop = adteLeitAnterior

                    'ldblTotTarifaAnter = (ldblBanAnter * llngDiasAnter) / 100
                    'ldblTotTarifaAtual = (ldblBanAtual * llngDiasAtual) / 100
                    'ldblTotTarifa = ldblTotTarifaAtual + ldblTotTarifaAnter
                    If llngDiasAtual >= 1 And llngDiasAnter = 0 Then
                        ldblTotTarifa = ldblBanAtual * llngDiasAtual
                    ElseIf llngDiasAtual = 0 And llngDiasAnter >= 1 Then
                        ldblTotTarifa = ldblBanAnter * llngDiasAnter
                    Else
                        While ldteLoop < ldteTarAtual_BD
                            If ldteLoop >= ldteTarAnter_BD Then
                                ldblTotTarifa = ldblTotTarifa + ldblBanAnter
                            End If
                            'ldteLoop = ldteLoop + 1
                            ldteLoop = DateAdd(DateInterval.Day, 1, ldteLoop)
                        End While
                        While ldteLoop >= ldteTarAtual_BD And ldteLoop < adteLeitAtual
                            ldblTotTarifa = ldblTotTarifa + ldblBanAtual
                            'ldteLoop = ldteLoop + 1
                            ldteLoop = DateAdd(DateInterval.Day, 1, ldteLoop)
                        End While
                    End If

                    'If llngBanAnter = 5 Then
                    'ldblTotTarifa = llngDiasAtual + llngDiasAnter
                    'ldblTotTarifa = (ldblBanAtual / ldblTotTarifa) * llngDiasAnter
                    'Else
                    'ldblTotTarifa = ldblTotTarifa / (llngDiasAtual + llngDiasAnter)
                    'End If

                    ldblTotTarifa = ldblTotTarifa / (llngDiasAtual + llngDiasAnter)
                End If
            End If

            TarifaProporcionalBandeira = Convert.ToDouble(Strings.Format(ldblTotTarifa, "#,##0.00"))

        Catch ex As Exception
            Exit Function
        End Try

    End Function

    Public Function CalculaIluminacaoPublica(ByVal alngCidade As Long, ByVal astrClasse As String, ByVal adblQtdConsumo As Double, ByVal adblVlrConsumo As Double, ByVal adteLeitura As Date, ByVal adblTarCons_B1 As Double, ByVal adblTarCons_B4 As Double, ByVal astrGrupo As String) As Double
        Dim lstrTip As String 'Tipo de calculo percentual ou valor fixo
        '
        Dim ldblIni As Double 'Faixa inicial de consumo
        Dim ldblFim As Double 'Faixa final de consumo
        Dim ldblVlr As Double 'Valor ou percentual a ser aplicado
        Dim ldblTet As Double 'Valor do teto quando for por percentual
        Dim ldblBas As String 'Valor auxiliar para ser usado como base de cálculo
        '
        Dim lintCla As Integer 'Código da classe
        Dim lintTB1 As Integer 'Se deve usar o valor da tarifa residencial
        Dim lintkWh As Integer 'se deve usar o valor do consumo no calculo
        Dim lintBxR As Integer 'se deve calcular para o baixa renda
        Dim lintTB4 As Integer 'Se deve usar o valor da tarifa da iluminação pública rede para valor de base de cálculo
        Dim ldblRet As Double 'Valor de retorno
        '
        Dim lblnAux As Boolean
        Dim lstrSql As String
        Dim dt As DataTable

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        '
        lintCla = Convert.ToInt32(Strings.Left(Strings.Trim(astrClasse), IIf(Strings.Left(Strings.Trim(astrClasse), 1) <> "6", 1, 2))) 'classes 0,1,2,3,4,5,61,62,63,64,7,8,9
        ldblRet = 0
        lstrSql = ""
        lstrSql = lstrSql & "   SELECT * "
        lstrSql = lstrSql & "     FROM `cip` "
        lstrSql = lstrSql & "    WHERE `cidade` = " & alngCidade & " "
        lstrSql = lstrSql & "      AND `classe` = " & lintCla & " "
        lstrSql = lstrSql & "      AND `grupo` IN('T','" & astrGrupo & "') "
        lstrSql = lstrSql & "      AND `data` <= '" & Strings.Format(adteLeitura, "yyyy-MM-dd") & "' "
        lstrSql = lstrSql & "      AND ("
        lstrSql = lstrSql & "           (`tipo` = 'P' AND (`faixa_ini` >= 0 AND `faixa_fim` <= 999999999)) OR "
        lstrSql = lstrSql & "           (`tipo` = 'F' AND (" & Strings.Replace(adblQtdConsumo, ",", ".") & " BETWEEN `faixa_ini` AND `faixa_fim`)) "
        lstrSql = lstrSql & "          ) "
        lstrSql = lstrSql & "      AND " & Strings.Replace(adblQtdConsumo, ",", ".") & " BETWEEN `faixa_ini` AND `faixa_fim` "
        lstrSql = lstrSql & " ORDER BY `grupo` ASC, `data` DESC, faixa_fim DESC , faixa_ini DESC LIMIT 0, 1 "

        Try

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows

                    ldblIni = Convert.ToDouble(IIf(IsDBNull(row.Item("faixa_ini")), 0, row.Item("faixa_ini")))
                    ldblFim = Convert.ToDouble(IIf(IsDBNull(row.Item("faixa_fim")), 999999999, row.Item("faixa_fim")))
                    lstrTip = Strings.UCase(Strings.Trim(IIf(IsDBNull(row.Item("tipo")), "F", row.Item("tipo"))))
                    ldblVlr = Convert.ToDouble(IIf(IsDBNull(row.Item("valor")), 0, row.Item("valor")))
                    ldblTet = Convert.ToDouble(IIf(IsDBNull(row.Item("valor_teto_fixo")), 0, row.Item("valor_teto_fixo")))
                    lintTB1 = Convert.ToInt32(IIf(IsDBNull(row.Item("tarifa_residencial")), 0, row.Item("tarifa_residencial")))
                    lintkWh = Convert.ToInt32(IIf(IsDBNull(row.Item("valor_consumo")), 0, row.Item("valor_consumo")))
                    lintBxR = Convert.ToInt32(IIf(IsDBNull(row.Item("baixa_renda")), 0, row.Item("baixa_renda")))
                    lintTB4 = Convert.ToInt32(IIf(IsDBNull(row.Item("base_tarifa_b4")), 0, row.Item("base_tarifa_b4")))
                    lblnAux = True

                    If lintBxR = 0 And (astrClasse = "9104" Or astrClasse = "9105" Or astrClasse = "9106") Then
                        lblnAux = False
                    End If

                    If lblnAux Then
                        If lstrTip = "P" Then
                            '
                            'caso for percentual
                            '
                            If adblQtdConsumo >= ldblIni Then
                                If adblQtdConsumo > ldblFim Then
                                    ldblBas = IIf(lintTB4 = 0, IIf(lintkWh = 0, ldblFim, adblVlrConsumo), adblTarCons_B4)
                                    ldblRet = Arredondar((ldblBas * IIf(lintTB1 = 0, 1, adblTarCons_B1)) * (ldblVlr / 100), 2)
                                Else
                                    ldblBas = IIf(lintTB4 = 0, IIf(lintkWh = 0, adblQtdConsumo, adblVlrConsumo), adblTarCons_B4)
                                    ldblRet = Arredondar((ldblBas * IIf(lintTB1 = 0, 1, adblTarCons_B1)) * (ldblVlr / 100), 2)
                                End If
                            End If
                            If ldblTet > 0 And (ldblRet > ldblTet) Then
                                ldblRet = Arredondar(ldblTet, 2)
                            End If
                        ElseIf lstrTip = "F" Then
                            '
                            'caso for valor fixo
                            '
                            If ldblRet = 0 Then
                                ldblRet = Arredondar(ldblVlr, 2)
                            End If
                            Exit For
                        End If
                    End If
                    '****  Call .MoveNext
                Next

            End If

        Catch ex As Exception
            Exit Function
        End Try

        CalculaIluminacaoPublica = ldblRet
    End Function

    Public Sub CalculaMultaJuros(astrZona As String, astrLivro As String, astrConta As String, mesano As String, Optional alngConsId As Long = 0, Optional alngUnidId As Long = 0)
        Dim dtPgto As Date
        Dim dtVcto As Date
        Dim dtVerif As Date
        Dim mesPgto As Integer
        Dim anoPgto As Integer
        Dim mesVcto As Integer
        Dim anoVcto As Integer
        Dim diasAtraso As Integer
        Dim percMulta As Double
        Dim vlrMulta As Double
        Dim percJuros As Double
        Dim vlrJuros As Double
        Dim vlrCM As Double
        Dim vlrNota As Double
        Dim strsql As String
        Dim dtLoop As Date
        Dim vlrIlum As Double
        Dim vlrConv As Double
        Dim vlrMultAnt As Double
        Dim vlrJuroAnt As Double
        Dim vlrCorrAnt As Double
        Dim vlrIsentoMulta As Double
        Dim strCobrado As String
        Dim strMesAno As String
        Dim intSemana As Integer
        Dim bFeriado As Boolean
        Dim lintQtdFeriado As Integer
        Dim dt As DataTable

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Dim objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioExecAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try
            bFeriado = False
            dtVerif = "01/" & mesano

            If gstrEmpresa_Sigla = "COORSEL" And CLng(Right(mesano, 4) & Left(mesano, 2)) <= 200806 Then
                dtVerif = Convert.ToDateTime("25/06/2008")
            ElseIf gstrEmpresa_Sigla = "EFLJCL" And CLng(Right(mesano, 4) & Left(mesano, 2)) <= 200904 Then
                dtVerif = Convert.ToDateTime("30/04/2009")
            ElseIf gstrEmpresa_Sigla = "CERGAL" And CLng(Right(mesano, 4) & Left(mesano, 2)) <= 200908 Then
                dtVerif = Convert.ToDateTime("25/08/2009")
            Else
                dtVerif = "01/" & mesano
            End If

            strsql = ""
            strsql = strsql & "  SELECT * "
            strsql = strsql & "    FROM nf "
            strsql = strsql & "   WHERE flgst = 'P' "
            strsql = strsql & "     AND zona = '" & astrZona & "' "
            strsql = strsql & "     AND livro = '" & astrLivro & "' "
            strsql = strsql & "     AND conta = '" & astrConta & "' "
            If alngConsId > 0 Then
                strsql = strsql & "     AND cons_id = '" & alngConsId & "' "
            End If
            If alngUnidId > 0 Then
                strsql = strsql & "     AND unid_id = '" & alngUnidId & "' "
            End If
            strsql = strsql & "     AND datapag >= '" & Strings.Format(dtVerif, "yyyy-MM-dd") & "'"
            strsql = strsql & "     AND datapag > datavenc  "
            strsql = strsql & "ORDER BY mesano_ref asc"

            objAtualizaBancoDados.QUERY_NAME = strsql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows

                    strsql = ""
                    strsql = strsql & "SELECT * "
                    strsql = strsql & "  FROM multajuros "
                    strsql = strsql & " WHERE zona = '" & row.Item("zona") & "' "
                    strsql = strsql & "   AND livro = '" & row.Item("livro") & "' "
                    strsql = strsql & "   AND conta = '" & row.Item("conta") & "' "
                    strsql = strsql & "   AND mesano = '" & row.Item("mesano") & "'"
                    If alngConsId > 0 Then
                        strsql = strsql & "   AND cons_id = '" & alngConsId & "' "
                    End If
                    If alngUnidId > 0 Then
                        strsql = strsql & "   AND unid_id = '" & alngUnidId & "' "
                    End If
                    strsql = strsql & "   AND numnf = '" & row.Item("numnf") & "' "

                    Dim dt2 As DataTable

                    objAtualizaBancoDados.QUERY_NAME = strsql
                    dt2 = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

                    If dt2.Rows.Count < 1 Then

                        'If rsMultaJuro.RecordCount < 1 Then
                        ' If rsMultaJuro.State = 1 Then Call rsMultaJuro.Close
                        vlrCM = 0
                        vlrJuros = 0
                        vlrMulta = 0
                        If Not IsDBNull(row.Item("datapag")) Then
                            vlrIlum = BuscaVlrItem(IIf(IsDBNull(row.Item("itensserv")), "", row.Item("itensserv")), "0301000003")
                            vlrConv = BuscaVlrItem(IIf(IsDBNull(row.Item("itensserv")), "", row.Item("itensserv")), "903000")
                            vlrMultAnt = BuscaVlrItem(IIf(IsDBNull(row.Item("itensserv")), "", row.Item("itensserv")), "9080000061")
                            vlrJuroAnt = BuscaVlrItem(IIf(IsDBNull(row.Item("itensserv")), "", row.Item("itensserv")), "9080000066")
                            vlrCorrAnt = BuscaVlrItem(IIf(IsDBNull(row.Item("itensserv")), "", row.Item("itensserv")), "9080000067")
                            vlrIsentoMulta = BuscaVlrItem(IIf(IsDBNull(row.Item("itensserv")), "", row.Item("itensserv")), "908001")
                            vlrNota = IIf(IsDBNull(row.Item("ValorTot")), 0, row.Item("ValorTot"))
                            strMesAno = IIf(IsDBNull(row.Item("mesano")), mesano, row.Item("mesano"))
                            If vlrNota > 0 Then
                                vlrNota = vlrNota - (vlrIlum + vlrConv + vlrMultAnt + vlrJuroAnt + vlrCorrAnt + vlrIsentoMulta)
                                If vlrNota < 0 Then
                                    vlrNota = 0
                                End If
                            End If

                            dtPgto = Format(IIf(IsDBNull(row.Item("datapag")), "0000-00-00", row.Item("datapag")), "dd/MM/yyyy")
                            dtVcto = Format(IIf(IsDBNull(row.Item("datavenc")), "0000-00-00", row.Item("datavenc")), "dd/MM/yyyy")
                            diasAtraso = DateDiff("d", dtVcto, dtPgto)
                            intSemana = Weekday(dtVcto, DayOfWeek.Sunday)

                            dtLoop = dtVcto
                            lintQtdFeriado = 0

                            While dtLoop <= dtPgto
                                If Weekday(dtLoop, DayOfWeek.Sunday) <> 7 And Weekday(dtLoop, DayOfWeek.Sunday) <> 1 Then
                                    If VerificaFeriado(dtLoop) Then
                                        lintQtdFeriado = lintQtdFeriado + 1
                                    End If
                                End If
                                'dtLoop = dtLoop  +1
                                dtLoop = DateAdd(DateInterval.Day, 1, dtLoop)
                            End While

                            diasAtraso = diasAtraso - lintQtdFeriado
                            bFeriado = VerificaFeriado(dtVcto)
                            'Verifica se é Sábado, Domingo e Feriado se for entao nao cobra multa e juro
                            If (diasAtraso < 3 And intSemana = 7) Or (diasAtraso < 2 And intSemana = 1) Or (diasAtraso < 4 And bFeriado) Then
                                vlrJuros = 0
                                vlrCM = 0
                            Else
                                If diasAtraso > 0 Then
                                    'calculando juros de acordo com a config
                                    percJuros = CDbl(GetConfig("percentual.juros")) / 100
                                    vlrJuros = ((vlrNota * percJuros) / 365) * diasAtraso
                                    'calculando correcao monetaria diaria
                                    'isso facilita a vida do cabra E usa um recurso
                                    'ótimo do VB, que é o loop com variável DATE.
                                    dtLoop = dtVcto
                                    vlrCM = 0
                                    While dtLoop <= dtPgto
                                        vlrCM = vlrCM + (vlrNota * IndiceCMDia(CInt(Month(dtLoop)), CInt(Year(dtLoop))))
                                        'dtLoop = dtLoop + 1
                                        dtLoop = DateAdd(DateInterval.Day, 1, dtLoop)
                                    End While
                                End If
                            End If
                        End If


                        'pesquisa se tem multas anteriores:
                        'pode acontecer de já haver um registro na tabela por conta de cobrar multa de faturas em aberto
                        strsql = ""
                        strsql = strsql & "SELECT * "
                        strsql = strsql & "  FROM multajuros "
                        strsql = strsql & " WHERE zona = '" & astrZona & "' "
                        strsql = strsql & "   AND livro = '" & astrLivro & "' "
                        strsql = strsql & "   AND conta = '" & astrConta & "' "
                        If alngConsId > 0 Then
                            strsql = strsql & "   AND cons_id = '" & alngConsId & "' "
                        End If
                        If alngUnidId > 0 Then
                            strsql = strsql & "   AND unid_id = '" & alngUnidId & "' "
                        End If
                        strsql = strsql & "   AND mesano = '" & strMesAno & "'"

                        strCobrado = "N"

                        Dim dt3 As DataTable

                        objAtualizaBancoDados.QUERY_NAME = strsql
                        dt3 = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

                        If dt3.Rows.Count > 0 Then
                            For Each row3 As DataRow In dt3.Rows
                                'existe registro de multa pra essa nota,
                                'checar se foi cobrada. se não foi cobrada,
                                'deleta o registro e calcula normalmente.
                                'se foi cobrada, não cobra a multa e cobra
                                'juros e correcao.\
                                strCobrado = IIf(IsDBNull(row3.Item("cobrado")), "N", row3.Item("cobrado"))
                                strCobrado = IIf(Len(Trim(strCobrado)) = 0, "N", strCobrado)
                                If strCobrado = "N" Then

                                    strsql = "DELETE FROM multajuros WHERE codigo = " & row3.Item("codigo") & ";"

                                    objExecAtualizaBancoDados.QUERY_NAME = strsql
                                    repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

                                    ' Call Executar(strsql)

                                    'calculando multa de acordo com a config
                                    percMulta = CDbl(GetConfig("multa")) / 100
                                    vlrMulta = vlrNota * percMulta
                                End If
                            Next
                        Else
                            If (diasAtraso < 3 And intSemana = 7) Or (diasAtraso < 2 And intSemana = 1) Or (diasAtraso < 4 And bFeriado) Then
                                vlrMulta = 0
                            Else
                                If diasAtraso > 0 Then
                                    'se não tiver nenhuma multa, calcular agora.
                                    'calculando multa de acordo com a config
                                    percMulta = CDbl(GetConfig("multa")) / 100
                                    vlrMulta = vlrNota * percMulta
                                End If
                            End If
                        End If

                        ' If rsMultaJuro.State = 1 Then rsMultaJuro.Close
                        If (diasAtraso < 3 And intSemana = 7) And (diasAtraso < 2 And intSemana = 1) Or (diasAtraso < 4 And bFeriado) Then
                            'nao faz nada
                        Else
                            If gstrEmpresa_Sigla = "CERCI" And row.Item("mesano") = "06/2023" Then GoTo PULA

                            If diasAtraso > 0 And strCobrado = "N" And vlrNota > 0 And (vlrMulta <> 0 Or vlrJuros <> 0 Or vlrCM <> 0) Then
                                strsql = "INSERT INTO multajuros (codigo, zona, livro, conta, numnf, mesano, diasatraso, vlrmulta, vlrjuros, vlrcm, cobrado, dat_processo, mesano_fatur "
                                If alngUnidId > 0 And alngConsId > 0 Then
                                    strsql = strsql & ", cons_id , unid_id "
                                End If
                                strsql = strsql & ") VALUES(0"
                                strsql = strsql & ", '" & row.Item("Zona") & "'"
                                strsql = strsql & ", '" & row.Item("Livro") & "'"
                                strsql = strsql & ", '" & row.Item("Conta") & "'"
                                strsql = strsql & ",  " & row.Item("numNf") & " "
                                strsql = strsql & ", '" & row.Item("mesano") & "'"
                                strsql = strsql & ",  " & diasAtraso & " "
                                strsql = strsql & ",  " & Replace(Arredondar(vlrMulta, 2), ",", ".") & " "
                                strsql = strsql & ",  " & Replace(Arredondar(vlrJuros, 2), ",", ".") & " "
                                strsql = strsql & ",  " & Replace(Arredondar(vlrCM, 2), ",", ".") & " "
                                strsql = strsql & ", 'N' "
                                strsql = strsql & ", current_date "
                                strsql = strsql & ", '" & mesano & "'"
                                If alngUnidId > 0 And alngConsId > 0 Then
                                    strsql = strsql & ",  " & alngConsId & ", " & alngUnidId
                                End If
                                strsql = strsql & ");"

                                objExecAtualizaBancoDados.QUERY_NAME = strsql
                                repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)
PULA:
                            End If
                        End If
                    End If
                Next
            End If

        Catch ex As Exception
            Exit Sub
        End Try

    End Sub
    Public Function VerificaFeriado(ByVal adteVcto As Date) As Boolean
        Dim lstrSql As String
        Dim dt As DataTable
        Dim bRet As Boolean

        Try

            bRet = False

            Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
            Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

            lstrSql = "SELECT * FROM `feriados` WHERE `feriados`.`data` = '" & Strings.Format(adteVcto, "yyyy-MM-dd") & "' "

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                bRet = True
            End If

        Catch ex As Exception
            Exit Function
        End Try

        VerificaFeriado = bRet
    End Function
    Public Function BuscaValorCodigo(ByVal astrCodigo As String) As Double
        Dim ldblRet As Double
        Dim lstrSql As String
        Dim dt As DataTable

        Try

            Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
            Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

            lstrSql = ""
            lstrSql = lstrSql & "SELECT * "
            lstrSql = lstrSql & "  FROM `codigos` "
            lstrSql = lstrSql & " WHERE `num` = '" & astrCodigo & "' "


            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    ldblRet = Convert.ToDouble(IIf(IsDBNull(row.Item("valor")), 0, row.Item("valor")))
                Next
            Else
                ldblRet = 0
            End If

        Catch ex As Exception
            Exit Function
        End Try

        BuscaValorCodigo = ldblRet
    End Function
    Public Function GetEmpresaUF()
        If Not IsDBNull(GetValorTabela("empresa", "uf", "")) Then
            Return GetValorTabela("empresa", "uf", "")
        Else
            Return "SC"
        End If
    End Function

    Public Function IndiceCMDia(ByVal aintMes As Integer, ByVal aintAno As Integer) As Double
        Dim lstrSql As String
        Dim ldblAux As Double
        Dim lintDiasMes As Integer
        Dim ldteAux As Date
        Dim dt As DataTable

        Try
            Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
            Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

            ldblAux = 0
            ldteAux = Convert.ToDateTime(UltimoDiaMes(aintMes, aintAno) & "/" & aintMes & "/" & aintAno)
            lstrSql = "SELECT * FROM indicecm WHERE mesano <= '" & Strings.Format(ldteAux, "yyyy-MM-dd") & "' ORDER BY mesano DESC LIMIT 0, 1; "


            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    ldblAux = Convert.ToDouble(IIf(IsDBNull(row.Item("vlrindice")), 0, row.Item("vlrindice")))
                    ldblAux = Arredondar((ldblAux / 100) / DateAndTime.Day(ldteAux), 12)
                Next
            End If
        Catch ex As Exception
            Exit Function
        End Try
        IndiceCMDia = ldblAux
    End Function
    Public Function codTodescri(ByVal astrCod As String) As String
        'Função que retorna a descrição do código selecionado da tabela de códigos
        Dim lstrRet As String
        Dim lstrSql As String
        Dim dt As DataTable

        Try

            Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
            Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

            lstrRet = ""
            lstrSql = "SELECT * FROM `codigos` WHERE `num` = '" & astrCod & "'"


            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrRet = IIf(IsDBNull(row.Item("descri")), astrCod, row.Item("descri"))
                Next

            Else
                lstrRet = astrCod
            End If
            codTodescri = lstrRet
        Catch ex As Exception
            Exit Function
        End Try

    End Function
    Public Function BuscaConsumo(ByVal astrGrupo As String, ByVal alngUnidId As Long, ByVal astrMesAno As String, Optional ByVal alngConsId As Long = 0, Optional ByVal aintTipo As Integer = 0, Optional ByRef astrCodClas As String = "") As Double
        Dim lstrIte As String
        Dim lstrGTa As String
        Dim ldblAux As Double
        Dim ldblRet As Double
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try

            lstrSql = ""
            lstrSql = lstrSql & "SELECT * "
            lstrSql = lstrSql & "  FROM `nf` "
            lstrSql = lstrSql & " WHERE `unid_id` = " & alngUnidId & " "
            lstrSql = lstrSql & "   AND `grupo` = '" & Strings.Left(astrGrupo, 1) & "' "
            If alngConsId > 0 Then
                lstrSql = lstrSql & "   AND `cons_id` = " & alngConsId & " "
            End If
            lstrSql = lstrSql & "   AND `anomes_fat`  = " & Strings.Right(astrMesAno, 4) & Strings.Left(astrMesAno, 2) & " "
            lstrSql = lstrSql & " ORDER BY anomes_fat DESC LIMIT 0, 1"

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    If Strings.Left(astrGrupo, 1) = "A" Then
                        astrCodClas = Strings.Trim(IIf(IsDBNull(row.Item("ta")), "01A400", row.Item("ta")))
                    Else
                        astrCodClas = Strings.Trim(IIf(IsDBNull(row.Item("codclas")), "", row.Item("codclas")))
                    End If
                    lstrIte = Strings.Trim(IIf(IsDBNull(row.Item("itensserv")), "0", row.Item("itensserv")))
                    lstrGTa = Strings.Mid(Strings.Trim(IIf(IsDBNull(row.Item("ta")), "01B100", row.Item("ta"))), 3, 2)
                    If aintTipo = 1 Then 'Consumo
                        ldblRet = BuscaVlrItem(lstrIte, "030201009") + BuscaVlrItem(lstrIte, "0302010009") + BuscaVlrItem(lstrIte, "0302010010") + BuscaVlrItem(lstrIte, "0302010011")
                    ElseIf aintTipo = 0 Then 'Consumo
                        ldblRet = BuscaVlrItem(lstrIte, "030201008") + BuscaVlrItem(lstrIte, "0302010008") + BuscaVlrItem(lstrIte, "0302010010") + BuscaVlrItem(lstrIte, "0302010011")
                    ElseIf aintTipo = 2 Then 'Demanda Máxima
                        If lstrGTa = "AA" Or lstrGTa = "AV" Or lstrGTa = "AL" Or lstrGTa = "AX" Then
                            ldblAux = BuscaVlrItem(lstrIte, "0302010019")
                            ldblRet = BuscaVlrItem(lstrIte, "0302010023")
                            If ldblAux > ldblRet Then
                                ldblRet = ldblAux
                            End If
                        Else
                            ldblRet = BuscaVlrItem(lstrIte, "0302010015")
                        End If
                    End If
                Next
            Else
                ldblRet = 0
            End If

            dt = Nothing
            BuscaConsumo = ldblRet
        Catch ex As Exception
            Exit Function
        End Try


    End Function

    Public Function BuscaIbge(Optional ByVal alngCidId As Long = 0, Optional ByVal astrCodEnd As String = "", Optional ByVal astrNomeCidade As String = "") As String
        'Função que retorna o código do IBGE de acordo com o nome da cidade passado no argumento
        Dim lstrRet As String
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try

            lstrRet = ""
            lstrSql = ""
            lstrSql = lstrSql & "SELECT * "
            lstrSql = lstrSql & "  FROM `munic` "
            lstrSql = lstrSql & " WHERE 1 = 1 "
            If alngCidId > 0 Then
                lstrSql = lstrSql & "   AND `codnum` = " & alngCidId & " "
            End If
            If IsNumeric(Strings.Trim(astrCodEnd)) Then
                lstrSql = lstrSql & "   AND cod = '" & Strings.Left(astrCodEnd, 6) & "' "
            End If
            If Strings.Trim(astrNomeCidade) <> "" Then
                lstrSql = lstrSql & "   AND `nome` = '" & Strings.Replace(astrNomeCidade, "'", "\'") & "' "
            End If
            lstrSql = lstrSql & " LIMIT 0, 1 "

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrRet = Strings.Trim(IIf(IsDBNull(row.Item("ibge")), "", row.Item("ibge")))
                Next
            Else
                lstrRet = ""
            End If

            dt = Nothing

        Catch ex As Exception
            Exit Function
        End Try

        BuscaIbge = lstrRet

    End Function

    Public Function BuscaNmCidade(Optional ByVal astrZona As String = "", Optional ByVal astrCodigo As String = "", Optional ByVal llngCodigo As Long = 0) As String
        Dim lstrRet As String
        Dim lstrSql As String
        Dim dt As New DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try
            lstrRet = ""
            If astrZona <> "" Or astrCodigo <> "" Or llngCodigo <> 0 Then
                lstrSql = ""
                lstrSql = lstrSql & "SELECT * "
                lstrSql = lstrSql & "  FROM `munic` "
                lstrSql = lstrSql & " WHERE 1 = 1"
                If astrZona <> "" Then
                    lstrSql = lstrSql & "   AND `zona` = '" & astrZona & "' "
                End If
                If astrCodigo <> "" Then
                    lstrSql = lstrSql & "   AND `cod` = '" & astrCodigo & "' "
                End If
                If llngCodigo <> 0 Then
                    lstrSql = lstrSql & "   AND `codnum` = " & llngCodigo & " "
                End If
                lstrSql = lstrSql & " LIMIT 0, 1 "

                objAtualizaBancoDados.QUERY_NAME = lstrSql
                dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

                If dt.Rows.Count > 0 Then
                    For Each row As DataRow In dt.Rows
                        lstrRet = Strings.Trim$(IIf(IsDBNull(row.Item("nome")), "", row.Item("nome")))
                    Next
                End If

                dt = Nothing

            End If

            BuscaNmCidade = lstrRet

        Catch ex As Exception
            Exit Function
        End Try
    End Function

    Public Sub GetTarifasComposicao(ByVal adteReferencia As Date, ByRef pdblEnergia As Double, ByRef pdblTransmissao As Double, ByRef pdblDistribuicao As Double, ByRef pdblEncargos As Double, ByRef pdblPerdas As Double, ByRef pdblTributosIcms As Double, ByRef pdblTributosPisCofins As Double)

        Dim lstrSql As String
        Dim dt As DataTable

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados


        Try

            lstrSql = "SELECT * "
            lstrSql = lstrSql & "  FROM `tarifas_composicao` "
            lstrSql = lstrSql & " WHERE referencia <= '" & VB6.Format(adteReferencia, "yyyy-MM-dd") & "' "
            lstrSql = lstrSql & " LIMIT 0, 1"

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)


            If dt.Rows.Count <> 0 Then
                For Each row As DataRow In dt.Rows
                    pdblEnergia = IIf(IsDBNull(row("energia_real")), 0, row("energia_real"))
                    pdblTransmissao = IIf(IsDBNull(row("transmissao")), 0, row("transmissao"))
                    pdblDistribuicao = IIf(IsDBNull(row("distribuicao")), 0, row("distribuicao"))
                    pdblEncargos = IIf(IsDBNull(row("enc_setoriais")), 0, row("enc_setoriais"))
                    pdblPerdas = IIf(IsDBNull(row("perdas")), 0, row("perdas"))
                    pdblTributosIcms = IIf(IsDBNull(row("tributos_icms")), 0, row("tributos_icms"))
                    pdblTributosPisCofins = IIf(IsDBNull(row("tributos_pis_cofins")), 0, row("tributos_pis_cofins"))
                Next
            End If

            dt = Nothing

        Catch ex As Exception
            Exit Sub
        End Try

    End Sub

    Public Sub AtualizaHistoricoUnidade(ByVal alngUnidId As Long, ByVal astrGrupo As String, ByVal astrMensagem As String)
        'Versão: 001 Desenvolvedor: Ricardo Inacio Data: 10/07/2008 Descrição: Rotina para atualizar o hitorico da unidade consumidora
        Dim lstrSql As String
        Dim objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioExecAtualizaBancoDados As New repositorioAtualizaBancoDados
        Try

            '----- Atualiza histórico da unidade consumidora ---------------------------------------------------------
            lstrSql = "UPDATE uc_faturamento " _
                & "   SET historico = CONCAT(IFNULL(historico, ''), IF(historico IS NULL OR LENGTH(TRIM(historico)) = 0, '', CONVERT(""\r\n"" USING latin1)), CONVERT(""" & astrMensagem & """ USING latin1)) " _
                & " WHERE id = " & alngUnidId & " "

            objExecAtualizaBancoDados.QUERY_NAME = lstrSql
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)
            '---------------------------------------------------------------------------------------------------------
        Catch ex As Exception
            Exit Sub
        End Try
    End Sub
    Public Sub CarregaTabelaComboBox(ByRef acmbAux As System.Windows.Forms.ComboBox,
                                 ByVal astrTabela As String,
                                 ByVal astrCampoDescr As String,
                        Optional ByVal astrCampoCod As String = "",
                        Optional ByVal astrOrdena As String = "",
                        Optional ByVal astrTodos As String = "",
                        Optional ByVal aintLista As Integer = 0,
                        Optional ByVal astrCodicao As String = "")

        'Obrigatórios
        'acmbAux = objeto combobox
        'astrTabela = Nome da tabela no banco de dados
        'astrCampoDescr = Nome do campo da tabela que aparecerá na lista da combobox
        'Opicionais
        'astrCampoCod = Código que irá para o itemdata da combobox
        'astrOrdena = Ordenação da tabela mandar se vai ser ASC ou DESC
        'aintTodos = Se vai aparecer no lisindex = 0 a opção TODOS

        Dim lstrSql As String
        Dim dt As DataTable
        Dim indexCbx As Integer
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        ' Call acmbAux.
        lstrSql = lstrSql & "SELECT  " & astrCampoDescr & IIf(Len(Trim(astrCampoCod)) > 0, "," & astrCampoCod, "")
        lstrSql = lstrSql & "  FROM " & astrTabela
        If Len(Trim(astrCodicao)) > 0 Then
            lstrSql = lstrSql & " WHERE " & astrCodicao
        End If
        If Len(Trim(astrOrdena)) > 0 Then
            lstrSql = lstrSql & " ORDER BY " & astrOrdena
        End If

        objAtualizaBancoDados.QUERY_NAME = lstrSql
        dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

        Call acmbAux.Items.Clear()
        If dt.Rows.Count > 0 Then
            System.Windows.Forms.Application.DoEvents()
            If Len(Trim(astrTodos)) > 0 Then
                'Call acmbAux.AddItem(astrTodos)
                acmbAux.Items.Add(astrTodos)
                'acmbAux.ItemData(acmbAux.Items.Count - 1) = 0
                VB6.SetItemData(acmbAux, acmbAux.Items.Count - 1, 0)
            End If
            For Each row As DataRow In dt.Rows
                Call acmbAux.Items.Add(IIf(IsDBNull(row.Item(0)), "nenhum", row.Item(0)))
                If Len(Trim(astrCampoCod)) > 0 Then
                    indexCbx = IIf(IsDBNull(row.Item(1)), 0, row.Item(1))
                    VB6.SetItemData(acmbAux, acmbAux.Items.Count - 1, indexCbx)
                End If
            Next
        Else
            Call acmbAux.Items.Add("nenhum")
        End If

        If aintLista = 1 Then
            acmbAux.SelectedIndex = -1
        Else
            If acmbAux.Items.Count > 0 Then
                acmbAux.SelectedIndex = 0
            End If
        End If
        System.Windows.Forms.Application.DoEvents()
    End Sub
    Public Function NumeroToExtenso(ByVal number As Decimal) As String
        Dim cent As Integer
        Try
            ' se for =0 retorna 0 reais
            If number = 0 Then
                Return "Zero Reais"
            End If
            ' Verifica a parte decimal, ou seja, os centavos
            cent = Decimal.Round((number - Int(number)) * 100, MidpointRounding.ToEven)
            ' Verifica apenas a parte inteira
            number = Int(number)
            ' Caso existam centavos
            If cent > 0 Then
                ' Caso seja 1 não coloca "Reais" mas sim "Real"
                If number = 1 Then
                    Return "Um Real e " + getDecimal(cent) + "centavos"
                    ' Caso o valor seja inferior a 1 Real
                ElseIf number = 0 Then
                    Return getDecimal(cent) + "centavos"
                Else
                    Return getInteger(number) + "Reais e " + getDecimal(cent) + "centavos"
                End If
            Else
                ' Caso seja 1 não coloca "Reais" mas sim "Real"
                If number = 1 Then
                    Return "Um Real"
                Else
                    Return getInteger(number) + "Reais"
                End If
            End If
        Catch ex As Exception
            Return ""
        End Try
    End Function
    ''' <summary>
    ''' Função auxiliar - Parte decimal a converter
    ''' </summary>
    ''' <param name="number">Parte decimal a converter</param>
    Public Function getDecimal(ByVal number As Byte) As String
        Try
            Select Case number
                Case 0
                    Return ""
                Case 1 To 19
                    Dim strArray() As String =
                       {"Um", "Dois", "Três", "Quatro", "Cinco", "Seis",
                        "Sete", "Oito", "Nove", "Dez", "Onze",
                        "Doze", "Treze", "Quatorze", "Quinze",
                        "Dezesseis", "Dezessete", "Dezoito", "Dezenove"}
                    Return strArray(number - 1) + " "
                Case 20 To 99
                    Dim strArray() As String =
                        {"Vinte", "Trinta", "Quarenta", "Cinquenta",
                        "Sessenta", "Setenta", "Oitenta", "Noventa"}
                    If (number Mod 10) = 0 Then
                        Return strArray(number \ 10 - 2) + " "
                    Else
                        Return strArray(number \ 10 - 2) + " e " + getDecimal(number Mod 10) + " "
                    End If
                Case Else
                    Return ""
            End Select
        Catch ex As Exception
            Return ""
        End Try
    End Function
    ''' <summary>
    ''' Função auxiliar - Parte inteira a converter
    ''' </summary>
    ''' <param name="number">Parte inteira a converter</param>
    Public Function getInteger(ByVal number As Decimal) As String
        Try
            number = Int(number)
            Select Case number
                Case Is < 0
                    Return "-" & getInteger(-number)
                Case 0
                    Return ""
                Case 1 To 19
                    Dim strArray() As String =
                        {"Um", "Dois", "Três", "Quatro", "Cinco", "Seis",
                        "Sete", "Oito", "Nove", "Dez", "Onze", "Doze",
                        "Treze", "Quatorze", "Quinze", "Dezesseis",
                        "Dezessete", "Dezoito", "Dezenove"}
                    Return strArray(number - 1) + " "
                Case 20 To 99
                    Dim strArray() As String =
                        {"Vinte", "Trinta", "Quarenta", "Cinquenta",
                        "Sessenta", "Setenta", "Oitenta", "Noventa"}
                    If (number Mod 10) = 0 Then
                        Return strArray(number \ 10 - 2)
                    Else
                        Return strArray(number \ 10 - 2) + " e " + getInteger(number Mod 10)
                    End If
                Case 100
                    Return "Cem"
                Case 101 To 999
                    Dim strArray() As String =
                           {"Cento", "Duzentos", "Trezentos", "Quatrocentos", "Quinhentos",
                           "Seiscentos", "Setecentos", "Oitocentos", "Novecentos"}
                    If (number Mod 100) = 0 Then
                        Return strArray(number \ 100 - 1) + " "
                    Else
                        Return strArray(number \ 100 - 1) + " e " + getInteger(number Mod 100)
                    End If
                Case 1000 To 1999
                    Select Case (number Mod 1000)
                        Case 0
                            Return "Mil"
                        Case Is <= 100
                            Return "Mil e " + getInteger(number Mod 1000)
                        Case Else
                            Return "Mil, " + getInteger(number Mod 1000)
                    End Select
                Case 2000 To 999999
                    Select Case (number Mod 1000)
                        Case 0
                            Return getInteger(number \ 1000) & "Mil"
                        Case Is <= 100
                            Return getInteger(number \ 1000) & "Mil e " & getInteger(number Mod 1000)
                        Case Else
                            Return getInteger(number \ 1000) & "Mil, " & getInteger(number Mod 1000)
                    End Select
                Case 1000000 To 1999999
                    Select Case (number Mod 1000000)
                        Case 0
                            Return "Um Milhão"
                        Case Is <= 100
                            Return getInteger(number \ 1000000) + "Milhão e " & getInteger(number Mod 1000000)
                        Case Else
                            Return getInteger(number \ 1000000) + "Milhão, " & getInteger(number Mod 1000000)
                    End Select
                Case 2000000 To 999999999
                    Select Case (number Mod 1000000)
                        Case 0
                            Return getInteger(number \ 1000000) + " Milhões"
                        Case Is <= 100
                            Return getInteger(number \ 1000000) + "Milhões e " & getInteger(number Mod 1000000)
                        Case Else
                            Return getInteger(number \ 1000000) + "Milhões, " & getInteger(number Mod 1000000)
                    End Select
                Case 1000000000 To 1999999999
                    Select Case (number Mod 1000000000)
                        Case 0
                            Return "Um Bilhão"
                        Case Is <= 100
                            Return getInteger(number \ 1000000000) + "Bilhão e " + getInteger(number Mod 1000000000)
                        Case Else
                            Return getInteger(number \ 1000000000) + "Bilhão, " + getInteger(number Mod 1000000000)
                    End Select
                Case Else
                    Select Case (number Mod 1000000000)
                        Case 0
                            Return getInteger(number \ 1000000000) + " Bilhões"
                        Case Is <= 100
                            Return getInteger(number \ 1000000000) + "Bilhões e " + getInteger(number Mod 1000000000)
                        Case Else
                            Return getInteger(number \ 1000000000) + "Bilhões, " + getInteger(number Mod 1000000000)
                    End Select
            End Select
        Catch ex As Exception
            Return ""
        End Try
    End Function

    Private Function carregaImagem(ByVal fileName As String) As Byte()
        'Método para carregar uma imagem do disco e retorná-la como um byteStream
        Dim fs As FileStream = New FileStream(fileName, FileMode.Open, FileAccess.Read)

        Dim br As BinaryReader = New BinaryReader(fs)

        Return (br.ReadBytes(Convert.ToInt32(br.BaseStream.Length)))

    End Function
    Public Function CPFOk(ByVal strNumeroCPF As String) As Boolean
        '--------------------------------------------------------------'
        ' FUNÇÃO USADA PARA VALIDAR O CPF.                             '
        '--------------------------------------------------------------'
        ' PARÂMETRO:                                                   '
        '                                                              '
        ' 1 - strNumeroCPF(Valor digitado - Tipo String)               '
        '--------------------------------------------------------------'
        Dim intSoma As Integer
        Dim intMult As Integer
        Dim intDigito As Integer
        Dim intC As Integer
        Dim intI As Integer
        'strNumeroCPF = gstrTiraCaracterAlfa(strNumeroCPF)

        Try


            If Len(strNumeroCPF) = 0 Or Len(strNumeroCPF) > 11 Then
                Exit Function
            ElseIf Len(strNumeroCPF) < 11 Then
                strNumeroCPF = New String("0", 11 - Len(strNumeroCPF)) & strNumeroCPF
            End If
            For intC = 9 To 10
                intMult = 2
                intSoma = 0
                For intI = intC To 1 Step -1
                    intSoma = intSoma + (Val(Mid(strNumeroCPF, intI, 1)) * intMult)
                    intMult = intMult + 1
                Next intI
                If intSoma Mod 11 < 2 Then
                    intDigito = 0
                Else
                    intDigito = 11 - (intSoma Mod 11)
                End If
                If intDigito <> Val(Mid(strNumeroCPF, intC + 1, 1)) Then
                    Exit Function
                End If
            Next
            CPFOk = True
        Catch ex As Exception
            Exit Function
        End Try
    End Function
    Public Function Int_Valida_CNPJ(ByVal astrCNPJ As String) As Boolean
        Dim lblnRetorno As Boolean
        Dim lintAux As Integer
        Dim llngSoma As Long
        Dim lintPasso As Integer
        Dim lintDigito1 As Integer
        Dim lintDigito2 As Integer
        Dim lintFlag As Integer
        Try
            lblnRetorno = False
            astrCNPJ = Strings.Trim$(astrCNPJ)
            If Strings.Len(astrCNPJ) = 14 Then
                For lintPasso = 5 To 6
                    llngSoma = 0
                    lintFlag = lintPasso
                    For lintAux = 1 To lintPasso + 7
                        llngSoma = llngSoma + (Conversion.Val(Strings.Mid$(astrCNPJ, lintAux, 1)) * lintFlag)
                        lintFlag = IIf(lintFlag > 2, lintFlag - 1, 9)
                    Next lintAux
                    llngSoma = llngSoma Mod 11
                    If lintPasso = 5 Then lintDigito1 = IIf(llngSoma > 1, 11 - llngSoma, 0)
                    If lintPasso = 6 Then lintDigito2 = IIf(llngSoma > 1, 11 - llngSoma, 0)
                Next
                If (lintDigito1 = Val(Mid(astrCNPJ, 13, 1)) And lintDigito2 = Conversion.Val(Strings.Mid$(astrCNPJ, 14, 1))) Then
                    lblnRetorno = True
                End If
            End If
            Int_Valida_CNPJ = lblnRetorno
        Catch ex As Exception
            Exit Function
        End Try
    End Function

    Public Function BuscaQtdeSuspensao(ByVal alngConsId As Long, ByVal alngUnidId As Long, ByVal alngQtde As Long) As Long
        Dim lintDia As Integer
        Dim lintBiS As Integer
        Dim ldteIni As Date
        Dim ldteFim As Date
        Dim llngRet As Long
        Dim lstrSql As String
        Dim iDia As Date
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try

            ldteIni = Date.Now
            'iDia = CInt(DateAndTime.Day(ldteIni))
            'iDia = CInt(iDia)
            lintBiS = fnMod(Year(ldteIni), 4)
            lintDia = IIf(lintBiS = 0, 366, 365)
            'ldteFim = DateSerial(Year(ldteIni), Month(ldteIni), day(ldteIni)) - lintDia
            llngRet = 0
            lstrSql = ""
            lstrSql = lstrSql & "SELECT `cons_id`, " & IIf(alngUnidId > 0, "`unid_id`,", "") & "COUNT(*) `qtde` "
            lstrSql = lstrSql & "FROM `concorte2` "
            lstrSql = lstrSql & "WHERE (`datacorte` <= '" & Strings.Format(ldteIni, "yyyy-MM-dd") & " 00:00:01' AND `datacorte` >= '" & Strings.Format(ldteFim, "yyyy-MM-dd") & " 23:59:59') "
            lstrSql = lstrSql & "AND `cons_id` = " & alngConsId & " "
            If alngUnidId > 0 Then
                lstrSql = lstrSql & "AND `unid_id` = " & alngUnidId & " "
            End If
            lstrSql = lstrSql & "GROUP BY `cons_id`" & IIf(alngUnidId > 0, ",`unid_id`", "") & " "
            lstrSql = lstrSql & "ORDER BY `cons_id` ASC, " & IIf(alngUnidId > 0, "`unid_id` ASC, ", "") & "`datacorte` DESC "
            lstrSql = lstrSql & "LIMIT 0, " & alngQtde & " "

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    llngRet = IIf(IsDBNull(row.Item("qtde")), 0, row.Item("qtde"))
                Next
            End If

            BuscaQtdeSuspensao = llngRet
        Catch ex As Exception
            Exit Function
        End Try

    End Function
    Public Function fnMod(ByVal valor, ByVal Divisor) As Integer
        Dim lintRet As Integer
        Try

            lintRet = 0
            If IsNumeric(valor) And IsNumeric(Divisor) Then
                If Divisor > 0 Then
                    lintRet = Convert.ToInt32(valor - (Divisor * Convert.ToInt32(valor / Divisor)))
                Else
                    Call MsgBox("Divisor não pode ser ZERO!", vbCritical, gstrTituloMensagem)
                    lintRet = 1
                End If
            Else
                Call MsgBox("A parametro valor ou parametro divisor não é um valor numérico!", vbCritical, gstrTituloMensagem)
                lintRet = 1
            End If
            fnMod = lintRet
        Catch ex As Exception
            Exit Function
        End Try
    End Function

    Public Function PegaFlgMedidorServico(ByVal astrCodigo As String) As Boolean
        Dim lblnRet As Boolean
        Dim lstrFme As String
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try

            lstrSql = ""
            lstrSql = lstrSql & "  SELECT `codigos`.`num`, "
            lstrSql = lstrSql & "         `codigos`.`descri`, "
            lstrSql = lstrSql & "         `codigos`.`flgmedidor` "
            lstrSql = lstrSql & "    FROM `codigos` "
            lstrSql = lstrSql & "   WHERE `codigos`.`num` = '" & astrCodigo & "' "
            lstrSql = lstrSql & "     AND `situacao` = 'A' AND `flgproc` = 'O' "
            lstrSql = lstrSql & "   LIMIT 0, 1 "

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrFme = Trim(IIf(IsDBNull(row.Item("flgmedidor")), "N", row.Item("flgmedidor")))
                    lblnRet = (lstrFme = "S")
                Next
            Else
                lblnRet = False
            End If

            dt = Nothing

            PegaFlgMedidorServico = lblnRet
        Catch ex As Exception
            Exit Function
        End Try

    End Function

    Public Sub sDadosEmpresa(ByRef Limagem As Byte(),
                              ByRef sNomeEmpresa As String,
                              ByRef sEnderecoEmpresa As String,
                              ByRef sCepEmpresa As String,
                              ByRef sCidadeEmpresa As String,
                              ByRef sUFempresa As String,
                              ByRef sBairroEmpresa As String,
                              ByRef sFoneEmpresa As String,
                              ByRef sWebEmpresa As String,
                              Optional ByRef sCpfCnpj As String = "",
                              Optional ByRef sIE As String = "")

        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try
            lstrSql = ""
            lstrSql = "SELECT * FROM EMPRESA B WHERE B.codigo = " & gintEmpresa_Codigo
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    Limagem = IIf(IsDBNull(row.Item("logotipo")), 0, row.Item("logotipo"))
                    sNomeEmpresa = IIf(IsDBNull(row.Item("nome")), "", row.Item("nome"))
                    sEnderecoEmpresa = IIf(IsDBNull(row.Item("endereco")), "", row.Item("endereco"))
                    sCepEmpresa = VB6.Format(IIf(IsDBNull(row.Item("cep")), "", row.Item("cep")), "00000-000")
                    sCidadeEmpresa = IIf(IsDBNull(row.Item("municipio")), "", row.Item("municipio"))
                    sUFempresa = IIf(IsDBNull(row.Item("uf")), "", row.Item("uf"))
                    sBairroEmpresa = IIf(IsDBNull(row.Item("bairro")), "", row.Item("bairro"))
                    sFoneEmpresa = IIf(IsDBNull(row.Item("fone")), "", row.Item("fone"))
                    sWebEmpresa = IIf(IsDBNull(row.Item("website")), "", row.Item("website"))
                    sCpfCnpj = IIf(IsDBNull(row.Item("cnpj")), "", row.Item("cnpj"))
                    sIE = IIf(IsDBNull(row.Item("insc")), "", row.Item("insc"))
                Next
            End If

            dt = Nothing

        Catch ex As Exception
            Exit Sub
        End Try
    End Sub

    Public Sub AtualizaSituacaoUnidade(ByVal alngUnidId As Long, ByVal astrGrupo As String, ByVal astrSituacao As String, ByVal adteAuxiliar As Date, Optional ByVal astrTipo As String = "OS", Optional ByVal astrRetiraMedidor As String = "N")
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioExecAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try

            '----- Atualiza situacao da unidade consumidora ---------------------------------------------------------
            lstrSql = ""
            lstrSql = lstrSql & "UPDATE uc_faturamento "
            lstrSql = lstrSql & "   SET situacao = """ & astrSituacao & """ "
            If astrTipo = "NO" Then
                lstrSql = lstrSql & ", data_ligacao = """ & VB6.Format(adteAuxiliar, "yyyy-MM-dd") & """ "
                lstrSql = lstrSql & ", dtainstmedidor  = """ & VB6.Format(adteAuxiliar, "yyyy-MM-dd") & """ "
                lstrSql = lstrSql & ", data_entrada  = """ & VB6.Format(adteAuxiliar, "yyyy-MM-dd") & """ "
            Else
                If astrSituacao = "L" Then
                    lstrSql = lstrSql & ", data_religacao = """ & VB6.Format(adteAuxiliar, "yyyy-MM-dd") & """ "
                    lstrSql = lstrSql & ", data_corte = NULL "
                ElseIf (astrSituacao = "D" Or astrSituacao = "I") Then
                    lstrSql = lstrSql & ", data_desligamento = """ & VB6.Format(adteAuxiliar, "yyyy-MM-dd") & """ "
                    If astrRetiraMedidor = "S" Then
                        If UCase(astrGrupo) = "B" Then
                            lstrSql = lstrSql & ", num = ''"
                        ElseIf UCase(astrGrupo) = "A" Then
                            lstrSql = lstrSql & ", eletr = ''"
                        End If
                    End If
                ElseIf astrSituacao = "C" Then
                    lstrSql = lstrSql & ", data_corte = """ & VB6.Format(adteAuxiliar, "yyyy-MM-dd") & """ "
                End If
            End If
            lstrSql = lstrSql & " WHERE id = " & alngUnidId & " "
            objExecAtualizaBancoDados.QUERY_NAME = lstrSql
            repositorioExecAtualizaBancosDados.Execute(objExecAtualizaBancoDados)

        Catch ex As Exception
            Exit Sub
        End Try

    End Sub
    Public Sub AtualizaSituacaoUnidadeLC(ByVal alngUnidId As Long, ByVal astrGrupo As String, ByVal astrSituacao As String, ByVal adteAuxiliar As Date, Optional ByVal astrTipo As String = "OS", Optional ByVal astrRetiraMedidor As String = "N")
        Dim lstrSql As String
        Dim objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioExecAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try
            '----- Atualiza situacao da unidade consumidora ---------------------------------------------------------
            lstrSql = ""
            lstrSql = lstrSql & "UPDATE " & gstrBDCLinkCenter & ".`uc` "
            lstrSql = lstrSql & "   SET situacao = 'C' "
            lstrSql = lstrSql & ", data_corte = '" & VB6.Format(adteAuxiliar, "yyyy-MM-dd") & "' "
            lstrSql = lstrSql & " WHERE unid_id_fatwin = " & alngUnidId & " "
            lstrSql = lstrSql & "   AND grupo = '" & astrGrupo & "' "
            objExecAtualizaBancoDados.QUERY_NAME = lstrSql
            repositorioExecAtualizaBancosDados.Execute(objExecAtualizaBancoDados)

        Catch ex As Exception
            Exit Sub
        End Try

    End Sub

    Public Function BuscaCampoDescricaoTabela(ByVal astrTabela As String, ByVal astrCampoDescricao As String, ByVal astrCampoId As String, ByVal alngCodigo As Long, Optional ByVal astrCampoIdSistema As String = "", Optional ByRef alngCodigoSistema As Long = 0, Optional ByRef astrCodigo As String = "") As String
        Dim lstrRet As String
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        'If alngCodigo > 0 Then
        lstrRet = ""
        lstrSql = ""
        lstrSql = lstrSql & "SELECT * " & vbCrLf
        lstrSql = lstrSql & "  FROM `" & astrTabela & "` " & vbCrLf
        If astrCodigo <> "" Then
            lstrSql = lstrSql & " WHERE `" & astrCampoId & "` = '" & astrCodigo & "'"
        Else
            lstrSql = lstrSql & " WHERE `" & astrCampoId & "` = " & alngCodigo
        End If

        objAtualizaBancoDados.QUERY_NAME = lstrSql
        dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

        If dt.Rows.Count > 0 Then
            For Each row As DataRow In dt.Rows
                lstrRet = Strings.Trim(IIf(IsDBNull(row.Item(astrCampoDescricao)), "", row.Item(astrCampoDescricao)))
                If astrCampoIdSistema <> "" Then
                    alngCodigoSistema = IIf(IsDBNull(row.Item(astrCampoIdSistema)), 0, row.Item(astrCampoIdSistema))
                End If
            Next
        End If

        'End If

        BuscaCampoDescricaoTabela = lstrRet
    End Function

    Public Function ctrl_form_MontaPrimaryKeyTable(ByVal afrmParent As System.Windows.Forms.Form, ByVal astrPriKey As Object, Optional ByVal bTabDif As Boolean = False, Optional ByVal sTabDif As String = "") As String
        Dim lstrRet As String
        Dim lintKey As Short
        Dim vControl As System.Windows.Forms.Control
        Try
            lstrRet = ""
            For lintKey = LBound(astrPriKey) To UBound(astrPriKey)
                For Each vControl In afrmParent.Controls
                    If ctrl_form_TiposValidosCamposDatafield(vControl) Then
                        If Not bTabDif Then
                            If LCase(Mid(vControl.Name, 4)) = astrPriKey(lintKey) Then
                                If TypeOf vControl Is System.Windows.Forms.TextBox Then
                                    If Len(Trim(lstrRet)) > 0 Then lstrRet = lstrRet & " AND "
                                    lstrRet = lstrRet & astrPriKey(lintKey) & " = " & IIf(IsNumeric(vControl.Text), "", "'") & vControl.Text & IIf(IsNumeric(vControl.Text), "", "'")
                                End If
                            End If
                        Else
                            If LCase(Mid(vControl.Name, 4)) = astrPriKey(lintKey) & "_" & sTabDif Then
                                If TypeOf vControl Is System.Windows.Forms.TextBox Then
                                    If Len(Trim(lstrRet)) > 0 Then lstrRet = lstrRet & " AND "
                                    lstrRet = lstrRet & astrPriKey(lintKey) & " = " & IIf(IsNumeric(vControl.Text), "", "'") & vControl.Text & IIf(IsNumeric(vControl.Text), "", "'")
                                End If
                            End If
                        End If
                    End If
                Next vControl
            Next lintKey
            ctrl_form_MontaPrimaryKeyTable = lstrRet
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_MontaPrimaryKeyTable" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

    Public Function ctrl_form_GetChavesKeyTable(ByVal astrTabela As String, ByVal astrTipoKey As String) As String()
        Dim lstrRet() As String
        Dim lstrCNm As String
        Dim lstrRNm As String
        Dim lstrRCm As String
        Dim lintIdx As Short
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try
            lstrSql = ""
            lstrSql = lstrSql & "SELECT `COLUMN_NAME`, `CONSTRAINT_NAME`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME` "
            lstrSql = lstrSql & "  FROM " & gstrDBEsquema & ".`key_column_usage` "
            lstrSql = lstrSql & " WHERE `TABLE_SCHEMA` = '" & gstrDataBase & "' "
            lstrSql = lstrSql & "   AND `TABLE_NAME` = '" & astrTabela & "' "
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)


            If dt.Rows.Count > 0 Then
                lintIdx = 0
                ReDim Preserve lstrRet(dt.Rows.Count - 1)
                For Each row As DataRow In dt.Rows
                    lstrCNm = IIf(IsDBNull(row.Item("CONSTRAINT_NAME")), "", row.Item("CONSTRAINT_NAME"))
                    lstrRNm = IIf(IsDBNull(row.Item("REFERENCED_TABLE_NAME")), "", row.Item("REFERENCED_TABLE_NAME"))
                    lstrRCm = IIf(IsDBNull(row.Item("REFERENCED_COLUMN_NAME")), "", row.Item("REFERENCED_COLUMN_NAME"))
                    If astrTipoKey = "PRIMARY" Then
                        If lstrCNm = astrTipoKey Then
                            lstrRet(lintIdx) = IIf(IsDBNull(row.Item("COLUMN_NAME")), "", row.Item("COLUMN_NAME"))
                        End If
                    Else
                        If lstrRNm <> "" Then
                            lstrRet(lintIdx) = IIf(IsDBNull(row.Item("COLUMN_NAME")), "", row.Item("COLUMN_NAME"))
                        End If
                    End If
                    lintIdx = lintIdx + 1
                Next
            End If

            ctrl_form_GetChavesKeyTable = VB6.CopyArray(lstrRet)
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_GetChavesKeyTable" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function ctrl_form_TiposValidosCamposDatafield(ByVal Controle As System.Windows.Forms.Control) As Boolean
        Dim lblnRetorno As Boolean
        Try
            lblnRetorno = False
            If TypeOf Controle Is System.Windows.Forms.TextBox Then
                lblnRetorno = True
            ElseIf TypeOf Controle Is System.Windows.Forms.CheckBox Then
                lblnRetorno = True
            ElseIf TypeOf Controle Is System.Windows.Forms.DateTimePicker Then
                lblnRetorno = True
            End If
            ctrl_form_TiposValidosCamposDatafield = lblnRetorno '(IIf(Strings.InStr(1, "txt,chk,dte", Strings.LCase(Strings.Left(Controle.Name, 3))) > 0, True, False))
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_TiposValidosCamposDatafield" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

    Public Sub ctrl_form_FormataCampos(ByVal DataTipo As DataTable, ByRef FormatoCampo As System.Windows.Forms.Control, Optional ByVal alngPrecision As Integer = 0)
        Try
            Select Case DataTipo
                'Case Is = ADODB.DataTypeEnum.adArray '8192
                'Case Is = ADODB.DataTypeEnum.adBigInt '20
                'Case Is = ADODB.DataTypeEnum.adBinary '128
                'Case Is = ADODB.DataTypeEnum.adBoolean '11
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtCheckbox '4
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Left
                'Case Is = ADODB.DataTypeEnum.adBSTR '8
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtGeneral
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Left
                '	FormatoCampo.Tag = VariantType.String
                'Case Is = ADODB.DataTypeEnum.adChapter '136
                'Case Is = ADODB.DataTypeEnum.adChar '129
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtGeneral
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Left
                '	FormatoCampo.Tag = VariantType.String
                'Case Is = ADODB.DataTypeEnum.adCurrency '6
                'Case Is = ADODB.DataTypeEnum.adDate '7
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtCustom
                '	FormatoCampo.DataFormat.Format = "dd/MM/yyyy"
                '	FormatoCampo.Tag = VariantType.Date
                'Case Is = ADODB.DataTypeEnum.adDBDate '133
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtCustom
                '	If FormatoCampo.DataFormat.Format = "" Then FormatoCampo.DataFormat.Format = "dd/MM/yyyy"
                '	FormatoCampo.Tag = VariantType.Date
                'Case Is = ADODB.DataTypeEnum.adDBTime '134
                'Case Is = ADODB.DataTypeEnum.adDBTimeStamp '135
                'Case Is = ADODB.DataTypeEnum.adDecimal '14
                'Case Is = ADODB.DataTypeEnum.adDouble '5
                'Case Is = ADODB.DataTypeEnum.adEmpty '0
                'Case Is = ADODB.DataTypeEnum.adError '10
                'Case Is = ADODB.DataTypeEnum.adFileTime '64
                'Case Is = ADODB.DataTypeEnum.adGUID '72
                'Case Is = ADODB.DataTypeEnum.adIDispatch '9
                'Case Is = ADODB.DataTypeEnum.adInteger '3
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtCustom
                '	FormatoCampo.DataFormat.Format = "###,###0"
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Right
                '	FormatoCampo.Tag = VariantType.Short
                'Case Is = ADODB.DataTypeEnum.adIUnknown '13
                'Case Is = ADODB.DataTypeEnum.adLongVarBinary '205
                'Case Is = ADODB.DataTypeEnum.adLongVarChar '201
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtGeneral
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Left
                '	FormatoCampo.Tag = VariantType.String
                'Case Is = ADODB.DataTypeEnum.adLongVarWChar '203
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtGeneral
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Left
                '	FormatoCampo.Tag = VariantType.String
                'Case Is = ADODB.DataTypeEnum.adNumeric '131
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtCustom
                '	If alngPrecision > 0 Then
                '		FormatoCampo.DataFormat.Format = "#" & IIf(alngPrecision > 0, "," & New String("#", alngPrecision), "") & "0" & IIf(alngPrecision > 0, "." & New String("0", alngPrecision), "")
                '	Else
                '		FormatoCampo.DataFormat.Format = "#,##0.00"
                '	End If
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Right
                '	FormatoCampo.Tag = VariantType.Decimal
                'Case Is = ADODB.DataTypeEnum.adPropVariant '138
                'Case Is = ADODB.DataTypeEnum.adSingle '4
                'Case Is = ADODB.DataTypeEnum.adSmallInt '2
                'Case Is = ADODB.DataTypeEnum.adTinyInt '16
                'Case Is = ADODB.DataTypeEnum.adUnsignedBigInt '21
                'Case Is = ADODB.DataTypeEnum.adUnsignedInt '19
                'Case Is = ADODB.DataTypeEnum.adUnsignedSmallInt '18
                'Case Is = ADODB.DataTypeEnum.adUnsignedTinyInt '17
                'Case Is = ADODB.DataTypeEnum.adUserDefined '132
                'Case Is = ADODB.DataTypeEnum.adVarBinary '204
                'Case Is = ADODB.DataTypeEnum.adVarChar '200
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtGeneral
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Left
                '	FormatoCampo.Tag = VariantType.String
                'Case Is = ADODB.DataTypeEnum.adVariant '12
                'Case Is = ADODB.DataTypeEnum.adVarNumeric '139
                'Case Is = ADODB.DataTypeEnum.adVarWChar '202
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtGeneral
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Left
                '	FormatoCampo.Tag = VariantType.String
                'Case Is = ADODB.DataTypeEnum.adWChar '130
                '	FormatoCampo.DataFormat.Type = StdFormat.FormatType.fmtGeneral
                '	FormatoCampo.Alignment = System.Windows.Forms.HorizontalAlignment.Left
                '	FormatoCampo.Tag = VariantType.String
            End Select
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_FormataCampos" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Sub ctrl_form_ExcluirRegistros(ByRef afrmAux As System.Windows.Forms.Form, ByVal astrTabela As String, ByRef astrPriKey As Object)
        Dim lstrKeyPri As String
        Dim objExecAtualizacaoBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioExecAtualizacaoBancoDados As New repositorioAtualizaBancoDados

        Try
            lstrKeyPri = ctrl_form_MontaPrimaryKeyTable(afrmAux, astrPriKey)

            If lstrKeyPri <> "" Then
                If MsgBox("Deseja realmente excluir este registro!", MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2, gstrTituloMensagem) = MsgBoxResult.Yes Then

                    objExecAtualizacaoBancoDados.QUERY_NAME = "DELETE FROM " & astrTabela & " WHERE " & lstrKeyPri
                    repositorioExecAtualizacaoBancoDados.Execute(objExecAtualizacaoBancoDados)

                    objExecAtualizacaoBancoDados.QUERY_NAME = "ALTER TABLE " & astrTabela & " AUTO_INCREMENT = 1"
                    repositorioExecAtualizacaoBancoDados.Execute(objExecAtualizacaoBancoDados)

                    Call ctrl_form_LimpaCamposPadrao(afrmAux)
                    Call MsgBox("Registro eliminado!", MsgBoxStyle.Information, gstrTituloMensagem)

                    If MsgBox("Deseja desativar este registro!", MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2, gstrTituloMensagem) = MsgBoxResult.Yes Then
                        objExecAtualizacaoBancoDados.QUERY_NAME = "UPDATE " & astrTabela & " SET `ativo` = 0 WHERE " & lstrKeyPri
                        repositorioExecAtualizacaoBancoDados.Execute(objExecAtualizacaoBancoDados)
                        'Call afrmAux.SetaCodigo(astrTabela, lstrKeyPri)
                    End If

                End If
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_ExcluirRegistros" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

    End Sub

    Public Sub ctrl_form_LimpaCamposPadrao(ByRef afrmAux As System.Windows.Forms.Form)
        Dim vControl As System.Windows.Forms.Control
        Try
            For Each vControl In afrmAux.Controls
                If TypeOf vControl Is System.Windows.Forms.TextBox Then
                    If LCase(Mid(vControl.Name, 4, 2)) = "id" Then
                        vControl.Text = "0"
                    Else
                        vControl.Text = ""
                    End If
                ElseIf TypeOf vControl Is System.Windows.Forms.CheckBox Then
                    vControl.GetType().GetProperty("Checked").SetValue(vControl, False, Nothing)   ' =  False 'System.Winows.Forms.CheckState.Unchecked
                    If InStr(1, vControl.Name, "Ativo") > 0 Then vControl.GetType().GetProperty("Checked").SetValue(vControl, True, Nothing) 'vControl.Text = System.Windows.Forms.CheckState.Checked
                ElseIf TypeOf vControl Is System.Windows.Forms.DateTimePicker Then
                    'vControl.Text = Date.Now 
                    vControl.GetType().GetProperty("Value").SetValue(vControl, Date.Now, Nothing)
                End If
            Next vControl
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_LimpaCamposPadrao" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Sub ctrl_form_SetaCodigo(ByRef afrmAux As System.Windows.Forms.Form, ByVal astrTabela As String, ByVal astrTabPri As String, ByRef astrPriKey As String, ByRef arstTabela As DataTable, Optional ByVal ablnAtivo As Boolean = True)
        Dim lintAux As Short
        Dim vControl As System.Windows.Forms.Control
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados
        Try
            If astrPriKey <> "" Then
                If astrTabela <> "" Then
                    lstrSql = ""
                    lstrSql = "SELECT * FROM `" & astrTabela & "` WHERE " & astrPriKey & IIf(ablnAtivo, " AND `ativo` = 1 ", "")
                    objAtualizaBancoDados.QUERY_NAME = lstrSql
                    dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

                    With arstTabela
                        If dt.Rows.Count > 0 Then
                            For Each row As DataRow In dt.Rows '1
                                lintAux = 0
                                For Each columns As DataColumn In dt.Columns '2
                                    For Each vControl In afrmAux.Controls
                                        If ctrl_form_TiposValidosCamposDatafield(vControl) Then
                                            If astrTabela = astrTabPri Then
                                                If LCase(Mid(vControl.Name, 4)) = LCase(columns.ColumnName) Then

                                                    'Set vControl.DataSource = irstTabela
                                                    'vControl.d = .Fields(lintAux).Name
                                                    'Call ctrl_form_FormataCampos(row.Item(lintAux).Type, vControl, (row.Item(lintAux).NumericScale))
                                                    'If vControl.Tag = CStr(VariantType.String) Then
                                                    '	vControl.maxLength = row.Item(lintAux).DefinedSize
                                                    'ElseIf row.Item(lintAux).Type <> ADODB.DataTypeEnum.adBoolean And row.Item(lintAux).Type <> ADODB.DataTypeEnum.adDBDate Then
                                                    '	vControl.MaxLength = row.Item(lintAux).Precision + IIf(row.Item(lintAux).NumericScale < 255, row.Item(lintAux).NumericScale, 0)
                                                    'End If

                                                    If TypeOf vControl Is System.Windows.Forms.TextBox Then
                                                        vControl.Text = row.Item(lintAux)
                                                        lintAux = lintAux + 1
                                                    ElseIf TypeOf vControl Is System.Windows.Forms.CheckBox Then
                                                        vControl.GetType().GetProperty("Checked").SetValue(vControl, IIf(row.Item(lintAux), True, False), Nothing)
                                                        lintAux = lintAux + 1
                                                    ElseIf TypeOf vControl Is System.Windows.Forms.DateTimePicker Then
                                                        vControl.GetType().GetProperty("Value").SetValue(vControl, row.Item(lintAux), Nothing)
                                                        lintAux = lintAux + 1
                                                    End If
                                                End If
                                            Else
                                                If LCase(Mid(vControl.Name, 4)) = LCase(columns.ColumnName) & "_" & astrTabela Then
                                                    If TypeOf vControl Is System.Windows.Forms.TextBox Then
                                                        vControl.Text = IIf(IsDBNull(row.Item(lintAux)), "", row.Item(lintAux))
                                                        lintAux = lintAux + 1
                                                        If vControl.Text = "" Then vControl.Text = "não existe registro"

                                                        If (IIf(IsDBNull(row.Item("Ativo")), 1, row.Item("Ativo"))) Then
                                                            vControl.ForeColor = System.Drawing.SystemColors.WindowText
                                                        Else
                                                            vControl.ForeColor = System.Drawing.Color.Red
                                                        End If
                                                        lintAux = lintAux + 1
                                                    End If
                                                End If
                                            End If
                                        End If
                                    Next vControl
                                Next '2
                            Next '1

                        End If
                    End With
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_SetaCodigo" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Sub ctrl_form_SalvarRegistro(ByRef afrmAux As System.Windows.Forms.Form, ByVal astrTabela As String, ByRef astrPriKey As Object, ByRef arstTabela As DataTable, ByRef ablnNovo As Boolean)
        Dim vControl As System.Windows.Forms.Control
        Dim lstrAux As String
        Dim lstrSql As String
        Dim lstrKeyPri As String

        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try
            lstrKeyPri = ctrl_form_MontaPrimaryKeyTable(afrmAux, astrPriKey)
            lstrSql = ""
            For Each vControl In afrmAux.Controls
                If TypeOf vControl Is System.Windows.Forms.TextBox Then
                    lstrAux = vControl.Text
                    If IsNumeric(lstrAux) Then
                        If InStr(lstrAux, ",") > 0 Then
                            lstrAux = Replace(lstrAux, ",", ".")
                        End If
                    End If
                    lstrSql = lstrSql & "`" & LCase(Mid(vControl.Name, 4)) & "` = '" & Replace(lstrAux, "'", "\'") & "',"
                ElseIf TypeOf vControl Is System.Windows.Forms.CheckBox Then
                    lstrSql = lstrSql & "`" & LCase(Mid(vControl.Name, 4)) & "` = " & IIf(vControl.GetType().GetProperty("Checked").GetValue(vControl, Nothing) = True, 1, 0) & ","
                ElseIf TypeOf vControl Is System.Windows.Forms.DateTimePicker Then
                    lstrSql = lstrSql & "`" & LCase(Mid(vControl.Name, 4)) & "` = '" & VB6.Format(vControl, "yyyy-MM-dd") & "',"
                End If
            Next vControl
            lstrSql = Left(lstrSql, Len(lstrSql) - 1)
            lstrSql = astrTabela & " SET " & vbCrLf & lstrSql
            If ablnNovo Then
                lstrSql = "INSERT INTO " & lstrSql
            Else
                lstrSql = "UPDATE " & lstrSql
            End If
            If Not ablnNovo Then
                lstrSql = lstrSql & vbCrLf & " WHERE " & lstrKeyPri
            End If
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            repositorioAtualizaBancosDados.Execute(objAtualizaBancoDados)

            Call MsgBox("As informações foram salvas com sucesso.", MsgBoxStyle.Information, gstrTituloMensagem)
            ablnNovo = False

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_SalvarRegistro" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

    End Sub

    Public Sub ctrl_form_FormKeyPress(ByRef afrmAux As System.Windows.Forms.Form, ByRef KeyAscii As Short)
        Try
            If KeyAscii = System.Windows.Forms.Keys.Escape Then
                KeyAscii = 0
                Call afrmAux.Close()
            ElseIf KeyAscii = System.Windows.Forms.Keys.Return Then
                KeyAscii = 0
                'Call SendKeysA(System.Windows.Forms.Keys.Tab, True)
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_FormKeyPress" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Sub ctrl_form_FormControlKeyPress(ByRef Controle As System.Windows.Forms.Control, ByRef KeyAscii As Short)
        Try
            If TypeOf Controle Is System.Windows.Forms.TextBox Then
                If Controle.Tag = CStr(VariantType.Short) Then
                    If KeyAscii <> System.Windows.Forms.Keys.Back And (KeyAscii < System.Windows.Forms.Keys.D0 Or KeyAscii > System.Windows.Forms.Keys.D9) Then
                        KeyAscii = 0
                    End If
                ElseIf InStr(1, Controle.Text, ",") > 0 Then  '#,##0.00
                    If KeyAscii <> System.Windows.Forms.Keys.Back And (KeyAscii < System.Windows.Forms.Keys.D0 Or KeyAscii > System.Windows.Forms.Keys.D9) Then
                        If KeyAscii = System.Windows.Forms.Keys.Delete Then
                            KeyAscii = System.Windows.Forms.Keys.Snapshot
                        End If
                        If KeyAscii <> System.Windows.Forms.Keys.Snapshot Then
                            KeyAscii = 0
                        Else
                            If InStr(1, Controle.Text, ",") > 0 Then
                                KeyAscii = 0
                            End If
                        End If
                    End If
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_FormControlKeyPress" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Sub ctrl_form_FormKeyUp(ByRef afrmAux As System.Windows.Forms.Form, ByRef KeyCode As Short, ByRef Shift As Short)
        Try
            'Seleciona o valor do campo
            If KeyCode = System.Windows.Forms.Keys.Return Or KeyCode = System.Windows.Forms.Keys.ShiftKey Or KeyCode = System.Windows.Forms.Keys.Tab Then
                With afrmAux
                    If TypeOf .ActiveControl Is System.Windows.Forms.TextBox Then
                        With .ActiveControl
                            '.SelStart = 0
                            '.SelLength = Len(.Text)
                        End With
                    End If
                End With
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_FormKeyUp" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Sub ctrl_form_FormKeyDown(ByRef afrmAux As System.Windows.Forms.Form, ByRef KeyCode As Short, ByRef Shift As Short, ByRef astrKey As Object)
        'Formata o valor do campo
        Dim vControl As System.Windows.Forms.Control
        Dim lstrTab As String
        Dim lintKey As Short
        Dim lstrKey As String
        Dim lintAux As Short
        lstrKey = ctrl_form_MontaPrimaryKeyTable(afrmAux, astrKey)
        Try
            If KeyCode = System.Windows.Forms.Keys.Return Or KeyCode = System.Windows.Forms.Keys.ShiftKey Or KeyCode = System.Windows.Forms.Keys.Tab Then
                With afrmAux
                    If TypeOf .ActiveControl Is System.Windows.Forms.TextBox Then
                        For lintAux = LBound(astrKey) To UBound(astrKey)
                            If astrKey(lintAux) <> "" Then
                                If astrKey(lintAux) = LCase(Mid(.ActiveControl.Name, 4)) Then
                                    lstrTab = LCase(Mid(astrKey(lintAux), 4))
                                    lstrKey = Left(astrKey(lintAux), 2) & " = " & .ActiveControl.Text
                                End If
                            End If
                        Next lintAux
                        With .ActiveControl
                            If .Tag = CStr(VariantType.Short) Then
                                .Text = Strings.Format(.Text, "###,###0")
                                'Call ctrl_form_SetaCodigo(afrmAux, lstrTab, "", lstrKey, lrstAux)
                                'Call ctrl_form_SetaCodigo(afrmAux, lstrTab, "", lstrKey)
                            End If
                        End With
                    End If
                End With
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_FormKeyDown" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

    End Sub

    Public Sub ctrl_form_TextBoxEvent(ByRef afrmAux As System.Windows.Forms.Form, ByRef Campo As Object)
        'Formata o valor do campo
        Dim vControl As System.Windows.Forms.Control
        Dim lstrTab As String
        Dim lintKey As Short
        Dim lstrKey As String
        Dim lintAux As Short
        'Dim lrstAux As ADODB.Recordset
        'lrstAux = New ADODB.Recordset
        Try
            With afrmAux
                If TypeOf Campo Is System.Windows.Forms.TextBox Then
                    With Campo
                        If .Tag = VariantType.Short And LCase(Mid(.Name, 4, 2)) = "id" Then
                            If .Text <> "" Then
                                lstrTab = LCase(Mid(.Name, 7))
                                lstrKey = LCase(Mid(.Name, 4, 2)) & " = " & .Text
                                '''Call ctrl_form_SetaCodigo(afrmAux, lstrTab, "", lstrKey, lrstAux)
                            End If
                        ElseIf .Tag = VariantType.Decimal Then
                            If .Text <> "" Then
                                .Text = VB6.Format(.Text, .DataFormat.Format)
                            End If
                        End If
                    End With
                End If
            End With
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ctrl_form_TextBoxEvent" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

    End Sub

    Public Sub sCarregaComboConsumidor(ByVal sSQL As String, ByVal oCombo As ComboBox)
        Dim lstrsql As String
        Dim lCodigo As Long
        Dim sDescricao As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try

            lstrsql = sSQL
            objAtualizaBancoDados.QUERY_NAME = lstrsql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                Call oCombo.Items.Add("Selecione")
                For Each row As DataRow In dt.Rows
                    sDescricao = Trim(IIf(IsDBNull(row.Item("Descricao")), "indefinido", row.Item("Descricao")))
                    lCodigo = CShort(IIf(IsDBNull(row.Item("Codigo")), 0, row.Item("Codigo")))
                    Call oCombo.Items.Add(sDescricao)
                    VB6.SetItemData(oCombo, oCombo.Items.Count - 1, lCodigo)
                Next
                oCombo.SelectedIndex = 0
            Else
                Call oCombo.Items.Add("000 - indefinido")
                VB6.SetItemData(oCombo, oCombo.Items.Count - 1, 0)
            End If

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: sCarregaComboConsumidor" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

    End Sub
    Public Function BuscaCodigoEstadoCivil(ByVal astrDescricao As String) As Long
        Dim llngRet As Long
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try

            llngRet = 0
            lstrSql = ""
            lstrSql = lstrSql & "SELECT *"
            lstrSql = lstrSql & "  FROM `estado_civil` "
            lstrSql = lstrSql & " WHERE `descricao` LIKE '%" & astrDescricao & "%' "

            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    llngRet = CLng(IIf(IsDBNull(row.Item("id")), "0", row.Item("id")))
                Next
            End If

            BuscaCodigoEstadoCivil = llngRet

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: BuscaCodigoEstadoCivil" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

    Public Sub AtualizaMedidor(ByVal alngIdMedidor As Long, ByVal alngMedidorSituacao As Long)
        Dim lstrSql As String
        Dim objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioExecAtualizaBancosDados As New repositorioAtualizaBancoDados
        Try
            If alngMedidorSituacao = 1 Or alngMedidorSituacao = 6 Or alngMedidorSituacao = 7 Then
                'medidor
                '7 - Liberado
                '1 - Instalado
                '6 - Sucata
                lstrSql = ""
                lstrSql = lstrSql & "UPDATE medidores "
                lstrSql = lstrSql & "   SET idsituacao = " & alngMedidorSituacao & " "
                lstrSql = lstrSql & " WHERE id = " & alngIdMedidor & " "
                objExecAtualizaBancoDados.QUERY_NAME = lstrSql
                repositorioExecAtualizaBancosDados.Execute(objExecAtualizaBancoDados)

            End If

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: AtualizaMedidor" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Sub AtualizaMedidorUnidadeConsumidora(ByVal alngIdMedidor As Long, ByVal alngIdUnidadeConsumidora As Long, ByVal alngUnidadeConsumidoraSituacao As Long, ByVal astrProtocolo As String, ByVal alngSequencia As Long, ByVal astrObservacao As String)
        Dim lstrSql As String
        Dim objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioExecAtualizaBancosDados As New repositorioAtualizaBancoDados
        Try
            'medidor_unidade_consumidora
            'Inicia o processo da UC vs Medidor com ele Instalado na UC
            '1 - Instalado
            '
            'Após isso o Medidor da UC pode ser Desligado e com futuramente Religado ou Ir para Manutenção e quando voltar ser Religado
            '4 - Desligado
            '2 - Religado
            '8 - Manutenção
            '
            '
            '5 - Desativado
            '1 - Instalado
            '
            '3 - Retirado
            '6 - Sucata
            lstrSql = ""
            lstrSql = lstrSql & "INSERT INTO medidores_unidade_consumidora "
            lstrSql = lstrSql & "   SET idsituacao = " & alngUnidadeConsumidoraSituacao & ", "
            lstrSql = lstrSql & "       idmedidor = " & alngIdMedidor & ", "
            lstrSql = lstrSql & "       idunidade_consumidora = " & alngIdUnidadeConsumidora & ", "
            lstrSql = lstrSql & "       lancamento = '" & Strings.Format(Date.Now, "yyyy-MM-dd hh:mm:ss") & "', "
            lstrSql = lstrSql & "       situacao = 'Ativo', "
            lstrSql = lstrSql & "       idusuarios = " & IIf(vUsuario.ID = 0, 1, vUsuario.ID) & ", " ' Em caso de Desenv o ID Usuário é (0) não existe na tab Usuario.
            lstrSql = lstrSql & "       os_id = " & alngSequencia & ", "
            lstrSql = lstrSql & "       os_protocolo = '" & astrProtocolo & "', "
            lstrSql = lstrSql & "       observacao = '" & Strings.Replace(Strings.Trim(astrObservacao), "'", "\'") & "' "
            objExecAtualizaBancoDados.QUERY_NAME = lstrSql
            repositorioExecAtualizaBancosDados.Execute(objExecAtualizaBancoDados)

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: AtualizaMedidorUnidadeConsumidora" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Function BuscaCampoDescricaoTabelaPadrao(ByVal astrTabela As String, ByVal astrCampoDescricao As String, ByVal alngId_A As Long)
        Dim lstrRet As String
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados
        Try
            If ValidaView(astrTabela) Then
                astrTabela = Strings.Mid(astrTabela, 3)
            End If
            If alngId_A > 0 Then
                lstrRet = ""
                lstrSql = ""
                lstrSql = lstrSql & "SELECT * " & vbCrLf
                lstrSql = lstrSql & "  FROM `" & astrTabela & "` " & vbCrLf
                lstrSql = lstrSql & " WHERE `id` = " & alngId_A
                objAtualizaBancoDados.QUERY_NAME = lstrSql
                dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

                If dt.Rows.Count > 0 Then
                    For Each row As DataRow In dt.Rows
                        lstrRet = Strings.Trim(IIf(IsDBNull(row.Item(astrCampoDescricao)), "", row.Item(astrCampoDescricao)))
                    Next
                End If

            End If

            BuscaCampoDescricaoTabelaPadrao = lstrRet
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: BuscaCampoDescricaoTabelaPadrao" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

    Public Function ValidaView(ByVal astrView As String) As Boolean
        Dim lblnRet As Boolean
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados
        Try
            lblnRet = False
            lstrSql = ""
            lstrSql = lstrSql & "SELECT * "
            lstrSql = lstrSql & "  FROM `" & gstrDBEsquema & "`.`" & gstrDBVisao & "` "
            lstrSql = lstrSql & " WHERE `" & gstrDBEsquema & "`.`" & gstrDBVisao & "`.`TABLE_SCHEMA` = '" & gstrDataBase & "' " & vbCrLf
            lstrSql = lstrSql & "   AND `" & gstrDBEsquema & "`.`" & gstrDBVisao & "`.`TABLE_NAME`   = '" & astrView & "' " & vbCrLf
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            lblnRet = dt.Rows.Count > 0

            ValidaView = lblnRet
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ValidaView" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

    Public Sub sCarregaComboGeral(ByVal sSQL As String, ByVal oCombo As ComboBox, Optional bSelecione As Boolean = False)
        Dim lstrsql As String
        Dim lCodigo As Long
        Dim sDescricao As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try

            lstrsql = sSQL
            objAtualizaBancoDados.QUERY_NAME = lstrsql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                If bSelecione Then
                    Call oCombo.Items.Add("Selecione")
                End If
                For Each row As DataRow In dt.Rows
                    sDescricao = Trim(IIf(IsDBNull(row.Item("Descricao")), "indefinido", row.Item("Descricao")))
                    lCodigo = CShort(IIf(IsDBNull(row.Item("Codigo")), 0, row.Item("Codigo")))
                    Call oCombo.Items.Add(sDescricao)
                    VB6.SetItemData(oCombo, oCombo.Items.Count - 1, lCodigo)
                Next
                'oCombo.SelectedIndex = 0
            Else
                Call oCombo.Items.Add("000 - indefinido")
                VB6.SetItemData(oCombo, oCombo.Items.Count - 1, 0)
            End If

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: sCarregaComboGeral" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

    End Sub
    Public Function VerificaExisteRegistroRet(ByVal astrTabela As String, ByVal astrCondicao As String) As Boolean

        Dim lblnRet As Boolean
        Dim vSQL As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try

            lblnRet = False

            vSQL = MontarQueryDados(astrCondicao, astrTabela, "", "")
            objAtualizaBancoDados.QUERY_NAME = vSQL
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                lblnRet = True
            End If

            VerificaExisteRegistroRet = lblnRet

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: VerificaExisteRegistroRet" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function
    Public Function ChecaNulo(ByRef avarContem As VariantType, Optional abTipval As Boolean = False) As VariantType
        Try
            'Esta função devolve o valor recebido caso não seja nulo, e caso for então devolto branco ou o valor 0
            If Not IsDBNull(avarContem) And avarContem <> "" Then
                ChecaNulo = avarContem
            Else
                If abTipval = False Then
                    ChecaNulo = ""
                Else
                    ChecaNulo = "0"
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ChecaNulo" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

    Public Function ChecaItensServ(ByVal astrServico As String, ByVal astrCampoServ As String) As String
        Dim intPosA As Integer
        Dim intPosB As Integer
        Dim intPosC As Integer

        Try

            '0302020080?110,34$( 387 x 0,28511 );
            intPosA = 1
            intPosA = InStr(intPosA, astrCampoServ, astrServico)
            If intPosA > 0 Then
                intPosA = InStr(intPosA, astrCampoServ, "?")
                intPosB = InStr(intPosA, astrCampoServ, ";")
                intPosC = InStr(intPosA, astrCampoServ, "$")
                If intPosC > 0 And intPosC < intPosB Then
                    ChecaItensServ = CStr(CDbl(Replace(Mid(astrCampoServ, intPosA + 1, intPosC - intPosA - 1), ".", ",")))
                Else
                    ChecaItensServ = CStr(CDbl(Replace(Mid(astrCampoServ, intPosA + 1, intPosB - intPosA - 1), ".", ",")))
                End If
                intPosA = intPosB
            Else
                ChecaItensServ = 0
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ChecaItensServ" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

    Public Function BuscaNMBanco(ByVal astrBanco As String) As String
        Dim lstrSql As String
        Dim llngBco As Long
        Dim lstrBco As String
        Dim lstrTip As String
        Dim lstrRet As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try

            llngBco = CLng(IIf(IsNumeric(astrBanco), astrBanco, 0))
            lstrSql = ""
            lstrSql = lstrSql & "SELECT `ban_id`, `nmbco`, `tpmovto`, `grupo`"
            lstrSql = lstrSql & "  FROM `bancos` "
            lstrSql = lstrSql & " WHERE `ban_id` =" & llngBco
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lstrBco = IIf(IsDBNull(row.Item("nmbco")), "", row.Item("nmbco"))
                    lstrTip = IIf(IsDBNull(row.Item("tpmovto")), "N", row.Item("tpmovto"))
                    lstrRet = lstrBco & IIf(lstrTip = "D", "(Débito)", "")
                Next
            Else
                lstrRet = ""
            End If

            BuscaNMBanco = lstrRet

            dt = Nothing

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: BuscaNMBanco" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function BuscaLivreEspecial(ByVal alngUnidId As Long) As String
        Dim lintLiv As Integer
        Dim lstrSql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Try
            'lstrSql = ""
            'lstrSql = lstrSql & "SELECT `unida`.`livreespecial` "
            'lstrSql = lstrSql & "  FROM `unida` "
            'lstrSql = lstrSql & " WHERE `unida`.`unid_id` = " & alngUnidId

            lstrSql = ""
            lstrSql = lstrSql & "SELECT `uc_faturamento`.`livreespecial` "
            lstrSql = lstrSql & "  FROM `uc_faturamento` "
            lstrSql = lstrSql & " WHERE `uc_faturamento`.`id` = " & alngUnidId
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    lintLiv = IIf(IsDBNull(row.Item("livreespecial")), 0, row.Item("livreespecial"))
                Next
            End If

            BuscaLivreEspecial = IIf(lintLiv = 1, "S", "")

            dt = Nothing
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: BuscaLivreEspecial" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function RelatorioPaisagemSelecionaDados2(ByVal aintTipoRel As Integer, ByVal aintRelatorio As Integer, ByVal astrSql As String, ByVal astrTabela As String) As String
        '0 = Total Geral
        '1 = Resumo diário
        '2 = Zona e nome da cidade
        '3 = Banco e nome do banco
        '5 = Recebimentos por dia/banc/classe
        'aintRelatorio
        '0 = Arrecadacao
        '1 = Em Abertos

        Try
            astrSql = "SELECT "
            If aintTipoRel = 5 Then 'Recebimentos por dia/banco/classe
                astrSql = astrSql & "`dataaux`, `banco`, `nmbanco`, " '0,1,2
                astrSql = astrSql & "IF(LEFT(`classe`, 1) = '9', '01Residencial', "
                astrSql = astrSql & " IF((LEFT(`classe`, 1)+0) BETWEEN 0 AND 3 AND LEFT(`ta`, 2) <> 'AL', '02Industrial', "
                astrSql = astrSql & "  IF(LEFT(`classe`, 1) = '4','03Comercial', "
                astrSql = astrSql & "   IF(LEFT(`classe`, 1) = '5' AND LEFT(`ta`, 2) <> 'AX', '04Rural', "
                astrSql = astrSql & "    IF(LEFT(`classe`, 1) = '5' AND LEFT(`ta`, 2) = 'AX', '05Rural Sup.', "
                astrSql = astrSql & "     IF((LEFT(`classe`, 2)+0) BETWEEN 61 AND 63,'06P.Público', "
                astrSql = astrSql & "      IF(LEFT(`classe`, 2) = '64','07I.Pública', "
                astrSql = astrSql & "       IF(LEFT(`classe`, 1) = '7','08S.Público', "
                astrSql = astrSql & "        IF(LEFT(`classe`, 1) = 8,'11C.Próprio',"
                astrSql = astrSql & "         IF(`ta` = 'ALS','09Cons.P.Liv', "
                astrSql = astrSql & "          IF(TRIM(`ta`) = 'AL','10Cons.Livre', 'Indefinido'"
                astrSql = astrSql & "          )"
                astrSql = astrSql & "         )"
                astrSql = astrSql & "        )"
                astrSql = astrSql & "       )"
                astrSql = astrSql & "      )"
                astrSql = astrSql & "     )"
                astrSql = astrSql & "    )"
                astrSql = astrSql & "   )"
                astrSql = astrSql & "  )"
                astrSql = astrSql & " )"
                astrSql = astrSql & ") classes, " '3
            End If
            If aintTipoRel = 1 Then 'Resumo diário
                astrSql = astrSql & "       dataaux, "
            ElseIf aintTipoRel = 2 Then 'Por Zona
                astrSql = astrSql & "       `zona`, `nmcidade`, "
            ElseIf aintTipoRel = 3 Then 'Por Banco
                astrSql = astrSql & "       `banco`, `nmbanco`, "
            End If
            astrSql = astrSql & "       SUM(IF(total<0,0,1)) ncons, " '1
            astrSql = astrSql & "       SUM(kwh_fat) kwhf, " '2
            astrSql = astrSql & "       SUM(consumo) consumo, " '3
            astrSql = astrSql & "       SUM(bandeira) bandeira, " '3
            astrSql = astrSql & "       SUM(icms) icms, " '4
            astrSql = astrSql & "       SUM(pis) pis, " '5
            astrSql = astrSql & "       SUM(cofins) cofins, " '6
            astrSql = astrSql & "       SUM(reativo) reativo, " '7
            astrSql = astrSql & "       SUM(COALESCE(consumo, 0) + "
            astrSql = astrSql & "           COALESCE(bandeira, 0) + "
            astrSql = astrSql & "           COALESCE(icms, 0) + "
            astrSql = astrSql & "           COALESCE(pis, 0) + "
            astrSql = astrSql & "           COALESCE(cofins, 0) + "
            astrSql = astrSql & "           COALESCE(reativo, 0) + "
            astrSql = astrSql & "           COALESCE(vkwh_dev_p, 0) + "
            astrSql = astrSql & "           COALESCE(vkwh_dev_fp, 0) + "
            astrSql = astrSql & "           COALESCE(vkwh_utr_p, 0) + "
            astrSql = astrSql & "           COALESCE(vkwh_utr_fp, 0)) importe, " '8
            astrSql = astrSql & "       SUM(tip) tip, " '9
            astrSql = astrSql & "       SUM(dif_eltrbr) eletrobras, " '10
            astrSql = astrSql & "       SUM(taxados) taxados, " '11
            astrSql = astrSql & "       SUM(diversos) diversos, " '12
            astrSql = astrSql & "       SUM(convenios) convenios, " '13
            astrSql = astrSql & "       SUM(ece)+SUM(multa_ece)+SUM(icms_ece) ece, " '14
            'astrSql = astrSql & "       SUM(icms_ece) icms_ece, " '15
            'astrSql = astrSql & "       SUM(multa_ece) multa_ece, " '14
            astrSql = astrSql & "       SUM(reverso) reverso, " '16
            astrSql = astrSql & "       SUM(retencao) retencao, " '17
            astrSql = astrSql & "       SUM(icms_st) icms_st, " '18
            astrSql = astrSql & "       SUM(pis_st) pis_st, " '19
            astrSql = astrSql & "       SUM(cofins_st) cofins_st, " '20
            astrSql = astrSql & "       SUM(total) total" '21
            astrSql = astrSql & "  FROM " & astrTabela
            RelatorioPaisagemSelecionaDados2 = astrSql

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: RelatorioPaisagemSelecionaDados2" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function
    Public Sub RelatorioPaisagemPadraoColunas2(ByRef astrTamcol As String, ByRef astrCabec As String, ByRef astrTemp As String, ByRef astrBranco As String, ByRef astrNomClass As Object, ByRef astrCondicao As Object)
        Try
            'Parametros de quebra
            ReDim astrNomClass(12)
            ReDim astrCondicao(12)

            astrNomClass(1) = "Residencial"
            astrNomClass(2) = "Industrial"
            astrNomClass(3) = "Comercial"
            astrNomClass(4) = "Rural"
            astrNomClass(5) = "Rural Sup."
            astrNomClass(6) = "P.Público"
            astrNomClass(7) = "I.Pública"
            astrNomClass(8) = "S.Público"
            astrNomClass(9) = "Cons.P.Liv"
            astrNomClass(10) = "Cons.Livre"
            astrNomClass(11) = "Sub-Total"
            astrNomClass(12) = "C.Próprio"
            '
            astrCondicao(1) = " LEFT(`classe`, 1) = '9' "
            astrCondicao(2) = " (LEFT(`classe`, 1)+0) BETWEEN 0 AND 3 AND LEFT(`ta`, 2) <> 'AL' "
            astrCondicao(3) = " LEFT(`classe`, 1) = '4' "
            astrCondicao(4) = " LEFT(`classe`, 1) = '5' AND LEFT(`ta`, 2) <> 'AX' "
            astrCondicao(5) = " LEFT(`classe`, 1) = '5' AND LEFT(`ta`, 2) = 'AX' "
            astrCondicao(6) = " (LEFT(`classe`, 2)+0) BETWEEN 61 AND 63 "
            astrCondicao(7) = " LEFT(`classe`, 2) = '64' "
            astrCondicao(8) = " LEFT(`classe`, 1) = '7' "
            astrCondicao(9) = " `ta` = 'ALS' "
            astrCondicao(10) = " TRIM(`ta`) = 'AL' "
            astrCondicao(11) = " LEFT(`classe`, 1) <> 8"
            astrCondicao(12) = " LEFT(`classe`, 1) = 8"
            '
            astrTamcol = "<15mm|>10mm|>17mm|>17mm|>14mm|>14mm|>14mm|>12mm|>13mm|>17mm|>12mm|>12mm|>12mm|>12mm|>14mm|>13mm|>10mm|>13mm|>15mm|>17mm;"
            astrCabec = ""
            astrCabec = astrCabec & "Classe|" '1
            astrCabec = astrCabec & "NºCons|" '2
            astrCabec = astrCabec & "KWh Fat|" '3
            astrCabec = astrCabec & "Forn.Liquido|" '4
            astrCabec = astrCabec & "Bandeira|" '5
            astrCabec = astrCabec & "ICMS|" '6
            astrCabec = astrCabec & "Pis|" '7
            astrCabec = astrCabec & "Cofins|" '8
            astrCabec = astrCabec & "Reativo|" '9
            astrCabec = astrCabec & "Importe|" '10
            astrCabec = astrCabec & "Ilum.Pub|" '11
            astrCabec = astrCabec & "J.Eletr.|" '12
            astrCabec = astrCabec & "Taxados|" '13
            astrCabec = astrCabec & "Diversos|" '14
            astrCabec = astrCabec & "Convenios|" '15
            astrCabec = astrCabec & "Ece/Mt./Ic.|" '16
            'astrCabec = astrCabec & "ICMS ECE|" '17
            astrCabec = astrCabec & "Reverso|" '17
            astrCabec = astrCabec & "Retenção|" '18
            astrCabec = astrCabec & "Sub.Trib.|" '19
            astrCabec = astrCabec & "Total" '20
            'Para preencher com zeros
            astrTemp = "|0|0|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00"
            'Para preencher com brancos
            astrBranco = "|||||||||||||||||||"

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: RelatorioPaisagemSelecionaDados2" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Public Function OnOffProcMon(ByVal onSet As Boolean, Optional ByVal astrAviso As String = "", Optional ByVal aintMax As Integer = 0)
        'Função para mostra aquela telinha amarela caganerra na tela!!
        'É usado em conjunto
        Try
            If Len(Trim(astrAviso)) < 1 Then astrAviso = gstrTituloMensagem
            If onSet = True Then

                frmProcessoMon.ProgressBar1.Minimum = 0
                frmProcessoMon.ProgressBar1.Maximum = aintMax + 1
                frmProcessoMon.ProgressBar1.Value = frmProcessoMon.ProgressBar1.Minimum
                frmProcessoMon.LSituacao.Text = astrAviso
                frmProcessoMon.Show()
            Else
                If astrAviso = "soma" Then
                    frmProcessoMon.ProgressBar1.Value = frmProcessoMon.ProgressBar1.Value + 1
                Else
                    frmProcessoMon.LSituacao.Text = ""
                    frmProcessoMon.Hide()
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: OnOffProcMon" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

    Public Function getLastInsertID(ByVal pTabela As String) As Integer

        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados

        Dim sql As String = "SELECT LAST_INSERT_ID() AS `novo_id` FROM " & pTabela & " "
        objAtualizaBancoDados.QUERY_NAME = sql
        dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)

        Try

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    Return row.Item("novo_id")
                Next
            End If

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: getLastInsertID" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function fBuscaBairroCidade_CEPNSS(ByVal codcidade As Integer, ByVal codbairro As Integer, ByVal sTipo As String, ByVal sDescrCod As String)

        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancosDados As New repositorioAtualizaBancoDados
        Dim lstrSql As String
        Dim vRet

        Try

            lstrSql = ""
            If sTipo = "B" Then
                lstrSql = lstrSql & "SELECT descricao, id"
                lstrSql = lstrSql & "  FROM " & gstrBDCEPNSS & ".`bairro` "
                lstrSql = lstrSql & " WHERE `id_antigo` =" & codbairro & " AND id_cidade = (SELECT id FROM " & gstrBDCEPNSS & ".cidade where id_antigo = " & codcidade & ")"
            Else
                lstrSql = lstrSql & "SELECT descricao, id"
                lstrSql = lstrSql & "  FROM " & gstrBDCEPNSS & " .`cidade` "
                lstrSql = lstrSql & " WHERE `id_antigo` =" & codcidade '& " AND id_cidade = " & codcidade
            End If
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancosDados.Consultar(objAtualizaBancoDados)


            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    If sDescrCod = "D" Then
                        vRet = IIf(IsDBNull(row.Item("descricao")), "", row.Item("descricao"))
                    Else
                        vRet = IIf(IsDBNull(row.Item("id")), 0, row.Item("id"))
                    End If
                Next
            End If

            If vRet = Nothing And sDescrCod = "C" Then vRet = 0
            If vRet = Nothing And sDescrCod = "D" Then vRet = ""

            fBuscaBairroCidade_CEPNSS = vRet

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: fBuscaBairroCidade_CEPNSS" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function
    Public Function fRetornaNossoNumeroLinhaDigitavelCodBarras(ByVal strNumCooperativa As String, ByVal sNumCliente As String, ByVal sQtdeParcelas As String, ByVal sModalidade As String, ByVal iTipoFormulario As Integer, ByVal dVencimento As Date, ByVal sValorDocumento As Double)

        Dim ret As String
        Dim strnossonumero As String
        Dim iconstante As String = "319731973197319731973"
        Dim FatorVencimento As Long
        Dim dVencimentobase As Date = "03/07/2000"
        Dim sLinhaDigitavel As String
        Dim strDV1, strDV2, strDV3, strDVcodbarras
        Dim strBeneficiarioCliente As String
        Dim sParcelaLinha As String
        Dim sValor As String
        Dim contador, mult, soma, resto As Integer
        Dim DVFinal As Integer
        Dim sLinhaDigitavelRetorno As String
        Dim strnossonumeroDvFinal As String
        Dim strNossoNumeroCB As String
        Dim strsql As String

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try
            strDV1 = 1
            strDV2 = 2
            strDV3 = 3
            strDVcodbarras = 4

            If UCase(gstrEmpresa_Sigla) = "CERCI" Then
                strnossonumero = GetConfig("nosso.numero.banco.faturas.boleto.sicoob")
            Else
                strnossonumero = GetConfig("nosso.numero.banco.faturas.boleto")
            End If

            strnossonumero = Replace(strnossonumero, "-", "")
            strnossonumero = Replace(strnossonumero, "/", "")
            strnossonumero = Replace(strnossonumero, "\", "")
            strnossonumeroDvFinal = strnossonumero
            strNossoNumeroCB = strnossonumero

            sValor = Strings.Format(sValorDocumento, "#,##0.00")
            sValor = Replace(Replace(sValor, ".", ""), ",", "")
            sValor = PreparaCampo(sValor, 0, 10, "0")

            'sValorDocumento = Replace(Replace(sValorDocumento, ".", ""), ",", "")
            'sValor = PreparaCampo(sValorDocumento, 0, 10, "0")

            sParcelaLinha = PreparaCampo(sQtdeParcelas, 0, 3, "0")
            strBeneficiarioCliente = PreparaCampo(sNumCliente, 0, 7, "0")
            strNumCooperativa = PreparaCampo(strNumCooperativa, 0, 4, "0")
            sNumCliente = PreparaCampo(sNumCliente, 0, 10, "0")
            strnossonumero = PreparaCampo(strnossonumero, 0, 7, "0")

            Dim sMontagem As String = strNumCooperativa & sNumCliente & strnossonumero

            ' DIGITO DO NOSSO NÚMERO
            soma = 0
            For contador = 1 To 21
                mult = Mid(sMontagem, contador, 1) * Mid(iconstante, contador, 1)
                soma = soma + mult
            Next

            resto = (soma Mod 11)

            If resto = 0 Or resto = 1 Then
                DVFinal = 0
            Else
                DVFinal = 11 - resto

                If InStr(DVFinal, "-") > 0 Then
                    DVFinal = Right(DVFinal, 1)
                End If
            End If

            FatorVencimento = DateDiff(Microsoft.VisualBasic.DateInterval.Day, dVencimentobase, dVencimento) ' 8012
            FatorVencimento = FatorVencimento + 1000
            FatorVencimento = PreparaCampo(FatorVencimento, 0, 4, "0")

            strnossonumero = PreparaCampo(strnossonumeroDvFinal & DVFinal, 0, 8, "0")

            sLinhaDigitavel = "75691"
            sLinhaDigitavel = sLinhaDigitavel & strNumCooperativa
            sLinhaDigitavel = sLinhaDigitavel & strDV1
            sLinhaDigitavel = sLinhaDigitavel & sModalidade
            sLinhaDigitavel = sLinhaDigitavel & strBeneficiarioCliente
            sLinhaDigitavel = sLinhaDigitavel & Mid(strnossonumero, 1, 1)
            sLinhaDigitavel = sLinhaDigitavel & strDV2
            sLinhaDigitavel = sLinhaDigitavel & Mid(strnossonumero, 2, 8)
            sLinhaDigitavel = sLinhaDigitavel & sParcelaLinha
            sLinhaDigitavel = sLinhaDigitavel & strDV3
            sLinhaDigitavel = sLinhaDigitavel & strDVcodbarras
            sLinhaDigitavel = sLinhaDigitavel & FatorVencimento
            sLinhaDigitavel = sLinhaDigitavel & sValor

            Dim sMontagemCodBarras As String
            Dim sCampoLivre As String

            sCampoLivre = "1"
            sCampoLivre = sCampoLivre & strNumCooperativa
            sCampoLivre = sCampoLivre & "01"
            sCampoLivre = sCampoLivre & strBeneficiarioCliente
            sCampoLivre = sCampoLivre & strnossonumero
            sCampoLivre = sCampoLivre & "001"

            sMontagemCodBarras = "756"
            sMontagemCodBarras = sMontagemCodBarras & "9"
            sMontagemCodBarras = sMontagemCodBarras & "X"
            sMontagemCodBarras = sMontagemCodBarras & FatorVencimento
            sMontagemCodBarras = sMontagemCodBarras & sValor
            sMontagemCodBarras = sMontagemCodBarras & sCampoLivre

            sLinhaDigitavelRetorno = ValidaLinhaDigitavelCodBarras(sLinhaDigitavel, sMontagemCodBarras, DVFinal)

            'ret = strNumCooperativa & sNumCliente & sQtdeParcelas & sModalidade & iTipoFormulario

            If UCase(gstrEmpresa_Sigla) = "CERCI" Then
                strsql = "UPDATE configuracoes SET valor = " & CInt(strnossonumeroDvFinal) + 1 & ", valor_padrao = " & CInt(strnossonumeroDvFinal) + 1 & " WHERE nome = " & "'nosso.numero.banco.faturas.boleto.sicoob'" '"
            Else
                strsql = "UPDATE configuracoes SET valor = " & CInt(strnossonumeroDvFinal) + 1 & ", valor_padrao = " & CInt(strnossonumeroDvFinal) + 1 & " WHERE nome = " & "'nosso.numero.banco.faturas.boleto'" '"
            End If
            objAtualizaBancoDados.QUERY_NAME = strsql
            repositorioAtualizaBancoDados.Execute(objAtualizaBancoDados)

            fRetornaNossoNumeroLinhaDigitavelCodBarras = sLinhaDigitavelRetorno

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: fRetornaNossoNumeroLinhaDigitavelCodBarras" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function ValidaLinhaDigitavelCodBarras(ByVal linhadigitavel As String, ByVal sCodBarras As String, Optional ByVal iDigitoNossoNumero As Integer = 0) As String

        Dim indice = "2121212120121212121201212121212"
        Dim indiceCodBarra = "43290876543298765432987654329876543298765432"
        ''''''''''''''''''''' '43290876543298765432987654329876543298765432
        Dim digito, soma, mult, contador As Integer
        Dim codigobarra As String
        Dim sParte1Linha As String
        Dim sParte2Linha As String
        Dim sParte3Linha As String
        Dim sParte4Linha As String
        Dim MontaLinhaDigitavel As String
        Dim sDigitoSubstituivel As String = "0"

        Try
            '  //cálculo do primeiro dígito
            soma = 0
            digito = 0
            For contador = 1 To 9
                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If
            Next

            digito = Multiplo10(soma) '- soma
            digito = digito - soma

            sParte1Linha = Mid(linhadigitavel, 1, 9) & digito

            '  //cálculo do segundo dígito
            soma = 0
            digito = 0
            For contador = 11 To 20
                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If
            Next

            digito = Multiplo10(soma) '– soma;
            digito = digito - soma

            sParte2Linha = Mid(linhadigitavel, 11, 10) & digito


            '  //cálculo do terceiro dígito
            soma = 0
            digito = 0
            For contador = 22 To 31

                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If

            Next

            digito = Multiplo10(soma)
            digito = digito - soma

            sParte3Linha = Mid(linhadigitavel, 22, 10) & digito
            sParte4Linha = sDigitoSubstituivel & Mid(linhadigitavel, 34, 14)

            MontaLinhaDigitavel = sParte1Linha & sParte2Linha & sParte3Linha & sParte4Linha

            Dim resto
            soma = 0
            digito = 0
            For contador = 1 To 44
                If contador <> 5 Then
                    mult = Mid(sCodBarras, contador, 1) * Mid(indiceCodBarra, contador, 1)
                    soma = soma + mult
                End If
            Next

            digito = 11 - (soma Mod 11)

            If (digito <= 1) Or (digito > 9) Then digito = 1

            sParte4Linha = digito & Mid(linhadigitavel, 34, 14)

            MontaLinhaDigitavel = sParte1Linha & sParte2Linha & sParte3Linha & sParte4Linha

            sCodBarras = Replace(sCodBarras, "X", digito)

            ValidaLinhaDigitavelCodBarras = MontaLinhaDigitavel & ";" & sCodBarras & ";" & iDigitoNossoNumero

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ValidaLinhaDigitavelCodBarras" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Function Multiplo10(numero As Integer) As Integer
        Try
            Do While (numero Mod 10) <> 0
                numero = numero + 1
            Loop
            Multiplo10 = numero

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: Multiplo10" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Sub MontaCodBarras(ByVal adblNumNf As Double, ByVal astrMesAno As String, ByVal adblValor As Double, ByVal astrZona As String, ByVal astrLivro As String, ByVal astrConta As String, ByRef astrCodBarras As String, ByRef astrDvCodBarras As String, Optional ByVal strVencimento As String = "", Optional ByRef astrLinhaDigitavel As String = "", Optional ByVal idBanco As String = "")

        Dim lstrCodEmp As String
        Dim lstrValorNf As String
        Dim lstrZona As String
        Dim lstrLivro As String
        Dim lstrConta As String
        Dim lstrMesAno As String
        Dim lstrNumNota As String
        Dim lstrAux As String
        Dim lstrdigito As String
        Dim lstrCodBarra(4) As String
        Dim lintDvCodBar(4) As Short
        Dim lintConta1 As Short
        Dim lintConta2 As Short
        Dim lintSomaDV As Short
        Dim lintDvNumNf As Short
        Dim lintMultiplicador As Short
        Dim lintTamanho As Short
        Dim strBanco As String
        Dim sRetornoLinhaDigitavelCodBarras As String
        Dim strCodCooperativa As String
        Dim strCodigoCliente As String
        Dim strValorNossoNumero As String
        Dim datavenc

        Dim ret As String
        Dim strnossonumero As String
        Dim iconstante As String = "319731973197319731973"
        Dim FatorVencimento As Long
        Dim dVencimentobase As Date = "03/07/2000"
        Dim sLinhaDigitavel As String
        Dim strDV1, strDV2, strDV3, strDVcodbarras
        Dim strBeneficiarioCliente As String
        Dim sParcelaLinha As String
        Dim sValor As String
        Dim contador, mult, soma, resto As Integer
        Dim DVFinal As Integer
        Dim sLinhaDigitavelRetorno As String
        Dim strnossonumeroDvFinal As String
        Dim strNossoNumeroCB As String
        Dim strCodBarrasDV

        Try

            If UCase(gstrEmpresa_Sigla) = "CERCI" Then
                strBanco = idBanco
            Else
                strBanco = GetConfig("numero.banco.faturas.boleto")
            End If


            If strBanco = "756" Then

                strCodCooperativa = GetConfig("numero.cooperativa.banco.sicoob")
                strCodigoCliente = GetConfig("numero.cliente.banco.sicoob")

                sRetornoLinhaDigitavelCodBarras = fRetornaNossoNumeroLinhaDigitavelCodBarras(strCodCooperativa, strCodigoCliente, "01", "01", 1, strVencimento, adblValor)

                strCodBarrasDV = Split(sRetornoLinhaDigitavelCodBarras, ";")

                'Campo CodBarra (linha digitavel) - tabela nf
                astrCodBarras = strCodBarrasDV(0)
                astrDvCodBarras = strCodBarrasDV(2)
                'astrDvCodBarras = Mid(strCodBarrasDV(1), 4, 1)
                'Campo CodBarra_imagem ( codBarra ) - tabela nf
                astrLinhaDigitavel = strCodBarrasDV(1)

            ElseIf strBanco = "104" Then ' SIGCB

                strCodCooperativa = "0"
                strCodigoCliente = GetConfig("numero.cliente.banco.sigcb")

                sRetornoLinhaDigitavelCodBarras = fRetornaNossoNumeroLinhaDigitavelCodBarrasSIGCB(strCodCooperativa, strCodigoCliente, "01", "01", 1, strVencimento, adblValor)

                strCodBarrasDV = Split(sRetornoLinhaDigitavelCodBarras, ";")

                'Campo CodBarra (linha digitavel) - tabela nf
                astrCodBarras = strCodBarrasDV(0)
                astrDvCodBarras = strCodBarrasDV(2)
                'Campo CodBarra_imagem ( codBarra ) - tabela nf
                astrLinhaDigitavel = strCodBarrasDV(1)

            ElseIf strBanco = "341" Then ' ITAU

                strCodCooperativa = "0"
                strCodigoCliente = GetConfig("numero.cliente.banco.itau")

                sRetornoLinhaDigitavelCodBarras = fRetornaNossoNumeroLinhaDigitavelCodBarrasITAU(strCodCooperativa, strCodigoCliente, "01", "01", 1, strVencimento, adblValor)

                strCodBarrasDV = Split(sRetornoLinhaDigitavelCodBarras, ";")

                'Campo CodBarra (linha digitavel) - tabela nf
                astrCodBarras = strCodBarrasDV(0)
                astrDvCodBarras = strCodBarrasDV(2)
                'Campo CodBarra_imagem ( codBarra ) - tabela nf
                astrLinhaDigitavel = strCodBarrasDV(1)


            Else

                ' Banco Mercantil do Brasil SA
                lstrCodEmp = VB6.Format(GetConfig("empresa.codbarra.febraban"), "0000")
                lstrValorNf = Left(VB6.Format(Trim(CStr(adblValor * 100)), New String("0", 11)), 11)
                lstrNumNota = Left(VB6.Format(Trim(CStr(adblNumNf)), New String("0", 9)), 11)

                lstrZona = Left(VB6.Format(Trim(astrZona), New String("0", 3)), 3)
                lstrLivro = Left(VB6.Format(Trim(astrLivro), New String("0", 2)), 2)
                lstrMesAno = Replace(astrMesAno, "/", "")

                lstrMesAno = Left(lstrMesAno, 2) & Right(lstrMesAno, 2)
                lstrConta = Left(VB6.Format(Trim(astrConta), New String("0", 7)), 7)
                lstrCodBarra(0) = "836" & lstrValorNf & lstrCodEmp & lstrNumNota & lstrMesAno & lstrZona & lstrLivro & lstrConta
                lintMultiplicador = 1
                For lintConta2 = 0 To 4
                    lstrdigito = ""
                    lintConta1 = lintConta2 + lintMultiplicador
                    lintTamanho = Len(lstrCodBarra(0))
                    If lintTamanho = 44 Then
                        lstrCodBarra(lintConta2) = Mid(lstrCodBarra(0), lintConta1, 11)
                        lintConta1 = 1
                        lintTamanho = 11
                    End If
                    While lintConta1 <= lintTamanho
                        If lintConta1 Mod 2 = 0 Then
                            lstrdigito = lstrdigito & Mid(lstrCodBarra(lintConta2), lintConta1, 1)
                        Else
                            lstrdigito = lstrdigito & CDbl(Mid(lstrCodBarra(lintConta2), lintConta1, 1)) * 2
                        End If
                        lintConta1 = lintConta1 + 1
                    End While
                    lintSomaDV = 0
                    lintConta1 = 1
                    While lintConta1 <= Len(lstrdigito)
                        lintSomaDV = lintSomaDV + CDbl(Mid(lstrdigito, lintConta1, 1))
                        lintConta1 = lintConta1 + 1
                    End While
                    lintDvCodBar(lintConta2) = lintSomaDV Mod 10
                    lintDvCodBar(lintConta2) = 10 - lintDvCodBar(lintConta2)
                    If lintDvCodBar(lintConta2) >= 10 Then
                        lintDvCodBar(lintConta2) = 0
                    End If
                    lintMultiplicador = lintMultiplicador + 10
                    If lintTamanho = 43 Then
                        lstrCodBarra(0) = Mid(lstrCodBarra(0), 1, 3) & lintDvCodBar(lintConta2) & Mid(lstrCodBarra(0), 4)
                        lintMultiplicador = 0
                    End If
                Next lintConta2

                astrCodBarras = lstrCodBarra(0)
                astrDvCodBarras = lintDvCodBar(1) & lintDvCodBar(2) & lintDvCodBar(3) & lintDvCodBar(4)

            End If

        Catch ex As Exception
            Exit Sub
        End Try

    End Sub

    Public Function MD5_hash(ByVal sTexto As String) As String
        Dim strsql As String
        Dim dt As DataTable
        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try

            strsql = "SELECT CONVERT(MD5('" & sTexto & "') USING latin1) hash"
            objAtualizaBancoDados.QUERY_NAME = strsql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    MD5_hash = Trim(IIf(IsDBNull(row.Item("hash")), "", row.Item("hash")))
                Next
            Else
                MD5_hash = sTexto
            End If

            Return MD5_hash

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: MD5_hash" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function fRetornaIBGE(ByVal astrCodCidade As String) As String
        Dim lstrSql As String
        Dim dt As DataTable
        Dim bRet As String

        Try

            bRet = False

            Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
            Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

            lstrSql = "SELECT * FROM " & gstrBDCEPNSS & ".`cidade` WHERE `id` = '" & astrCodCidade & "'"
            objAtualizaBancoDados.QUERY_NAME = lstrSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    bRet = Trim(IIf(IsDBNull(row.Item("ibge")), "", row.Item("ibge")))
                Next
            Else
                bRet = ""
            End If

        Catch ex As Exception
            Exit Function
        End Try

        fRetornaIBGE = bRet

    End Function

    Public Function fRetornaView_Recebimentos(ByVal sFiltro As String, ByVal sTabela As String, Optional ByVal bReserva As Boolean = False) As String

        Dim lstrsql As String


        lstrsql = ""
        lstrsql = lstrsql & " SELECT " & sTabela & ".mesano,  " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`ta` = '01A400'), 'Convencional', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`ta` = '01AA00'), 'Horo Sazonal Azul', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`ta` = '01AV00'), 'Horo Sazonal Verde', " & vbCrLf
        lstrsql = lstrsql & " IF(((" & sTabela & ".`ta` = '01AL00')  AND (" & sTabela & ".`livreespecial` = 1)), 'Consumidor Livre Especial', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`ta` = '01AL00'), 'Consumidor Livre', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`ta` = '01AS00'), 'Suprimento', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`ta` = '01B100'), 'Residencial', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`ta` = '01B200'), 'Rural', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`ta` = '01B400'), 'Iluminação Pública', " & vbCrLf
        lstrsql = lstrsql & " 'Demais Classes'))))))))) AS `grupo_tarifario`, " & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`grupo` AS `grupo`, " & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`codclas` AS `codigo_classe`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` = 9101),'Residencial Normal', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` = 9104),'Residencial Baixa Renda (246)', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` = 9105),'Residencial Baixa Renda (485)', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` = 9106),'Residencial Baixa Renda (694)', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 0 And 3999),'Industrial', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 4000 And 4999), 'Comercial', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 5000 And 5999), 'Rural', " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 6100 And 6199), 'Poder Público Municipal'," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 6200 And 6299), 'Poder Público Estadual'," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 6300 And 6399), 'Poder Público Federal'," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 6400 And 6499), 'Iluminação Pública'," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 7200 And 7299), 'Água, Esgosto e Saneamento'," & vbCrLf
        lstrsql = lstrsql & " IF(((" & sTabela & ".`codclas` BETWEEN 7100 And 7199) AND (" & sTabela & ".`codclas` BETWEEN 7300 AND 7999)), 'Serviço Público'," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`codclas` BETWEEN 8000 And 8999), 'Consumo Próprio', " & vbCrLf
        lstrsql = lstrsql & " 'Indefinido')))))))))))))) AS `classe_faturada`," & vbCrLf
        lstrsql = lstrsql & " `classe`.`descricao` AS `descricao_classe`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`zona` AS `zona`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`livro` AS `livro`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`conta` AS `conta`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`banco_fatur` AS `id_banco`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`banident` AS `banco`," & vbCrLf
        lstrsql = lstrsql & " `consum`.`matricula` AS `matricula`," & vbCrLf
        lstrsql = lstrsql & " `consum`.`nome` AS `nome`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`numnf` AS `numero_fatura`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`mesano` AS `mesano_fatura`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`mesano_ref` AS `mesano_referencia`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`anomes_fat` AS `anomes_fat`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`dataem` AS `data_emissao`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`dataapr` AS `data_apresentacao`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`datavenc` AS `data_vencimento`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`dataleit` AS `data_leitura_atual`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`dataleitant` AS `data_leitura_anterior`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`itensserv` AS `itensserv`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`banarrec` AS `banarrec`," & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`livreespecial`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`livre_verde`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`parcialmente_cativo`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`flgmedfat`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`fatura_final`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`irrigacao_aquicultura`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`micro_geracao_convenio_sefaz`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`micro_geracao_distribuida`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`tarifa_branca`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`unid_id`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`cons_id`, " & vbCrLf
        lstrsql = lstrsql & "  " & sTabela & ".`ta`, " & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020012') AS `valor_consumo_reativo`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020031') AS `valor_demanda_reativo`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020013') AS `valor_consumo_reativo_ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020014') AS `valor_consumo_reativo_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020027') AS `valor_demanda_reativo_ponta`, " & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020028') AS `valor_demanda_reativo_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'B'),(((`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'030201009') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010190')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010390')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010290')),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010009')) AS `kwh_faturado`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'B'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'030202009'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020009')) AS `vlr_kwh_faturado`," & vbCrLf
        lstrsql = lstrsql & " IF((IF((" & sTabela & ".`grupo` = 'A'),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010060'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010010')),0) > 0),IF((" & sTabela & ".`grupo` = 'A'),(`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010010') - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010060')),0),0) AS `kwh_faturado_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((IF((" & sTabela & ".`grupo` = 'A'),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020060'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020010')),0) > 0),IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020010'),0),0) AS `valor_kwh_faturado_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((IF((" & sTabela & ".`grupo` = 'A'),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010061'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010011')),0) > 0),IF((" & sTabela & ".`grupo` = 'A'),(`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010011') - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010061')),0),0) AS `kwh_faturado_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((IF((" & sTabela & ".`grupo` = 'A'),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020061'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020011')),0) > 0),IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020011'),0),0) AS `valor_kwh_faturado_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010041'),0) AS `kwh_devolvido_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020041'),0) AS `valor_kwh_devolvido_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010042'),0) AS `kwh_devolvido_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020042'),0) AS `valor_kwh_devolvido_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010043'),0) AS `kwh_ultrapassado_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020043'),0) AS `valor_kwh_ultrapassado_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010044'),0) AS `kwh_ultrapassado_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020044'),0) AS `valor_kwh_ultrapassado_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010017'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010032')) AS `kw_faturado`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020015'),0) AS `valor_kw_faturado`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010021'),0) AS `kw_faturado_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020021'),0) AS `valor_kw_faturado_ponta`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010025'),0) AS `kw_faturado_fora_ponta`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020023'),0) AS `valor_kw_faturado_fora_ponta`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010018'),0) AS `kw_ultrapassado`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020018'),0) AS `valor_kw_ultrapassado`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010022'),0) AS `kw_ultrapassagem_ponta`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020022'),0) AS `valor_kw_ultrapassagem_ponta`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010026'),0) AS `kw_ultrapassado_fora_ponta`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020026'),0) AS `valor_kw_ultrapassado_fora_ponta`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020051'),0) AS `valor_desconto_provisorio`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020052'),0) AS `valor_desconto_realizado`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020053'),0) AS `valor_devolucao_diferenca_ccee`, " & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020054'),0) AS `valor_cobranca_diferenca_ccee`, " & vbCrLf
        'lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'903000') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'90303') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'90304'))  AS `valor_convenios` ,"
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'903000')   AS `valor_convenios`, " & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'908000') - ((`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000061') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000066')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000097'))) AS `valor_diversos`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'904000') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'905000')) AS `valor_taxados`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0602020001') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0604020001')) AS `Icms_st`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0602020002') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0604020002')) AS `pis_st`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0602020003') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0604020003')) AS `cofins_st`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'907000') AS `valor_devolucao`," & vbCrLf
        lstrsql = lstrsql & " ((`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000061') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000066')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000067')) AS `valor_encargos_financeiros`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'909000') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000088')) AS `valor_retencao`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0301000003') AS `valor_cip`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302050002') AS `valor_juros_ece`," & vbCrLf
        lstrsql = lstrsql & " 0 AS `valor_diferenca_ece`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302070002') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302070005')) AS `valor_ece`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020032') AS `valor_must`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302030009') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302030010')) AS `valor_icms`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020029') AS `valor_pis`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020030') AS `valor_cofins`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020410') AS `valor_subvencao`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`valortot` AS `valor_total`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`valorliq` AS `valor_liquido`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010016') AS `demanda_contratada_convencional`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010020') AS `demanda_contratada_ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010024') AS `demanda_contratada_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010090') AS `qtde_fx1`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010091') AS `qtde_fx2`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010092') AS `qtde_fx3`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010093') AS `qtde_fx4`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020090') AS `vlr_fx1`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020091') AS `vlr_fx2`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020092') AS `vlr_fx3`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020093') AS `vlr_fx4`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'B'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'030201008'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010008')) AS `kwh_lido`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`fase` AS `fase`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302030001') AS `base_icms_1`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302030002') AS `aliquota_icms_1`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302030009') AS `valor_icms_1`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302030003') AS `base_icms_2`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302030013') AS `aliquota_icms_2`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302030010') AS `valor_icms_2`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020033') AS `base_pis`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'40PIS00001'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302990303')) AS `aliquota_pis`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020034') AS `base_cofins`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'40COF00001'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302990304')) AS `aliquota_cofins`," & vbCrLf
        lstrsql = lstrsql & " ((((`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'908000') - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000096')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000098')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000092')) - ((((((((((((((((((`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'907000') - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000062')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000079')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000081')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000082')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000087')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000088')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000090')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000091')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000092')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000096')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000030')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000031')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000032')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000033')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000034')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000035')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000036')) - `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000037'))) AS `receitas_financeiras`," & vbCrLf
        lstrsql = lstrsql & " (((((((`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000030') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000031')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000032')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000033')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000034')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000035')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000036')) + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000037')) AS `ressarcimento_idqual`,`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9080000098') AS `valor_encargo_uso_sis_trans`,(`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000087') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'9070000091')) AS `desconto_must`,IF((IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010060'),0) = 0),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010010'),0),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010060'),0)) AS `encargo_kwh_faturado_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020060'),0) = 0),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020010'),0),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020060'),0)) AS `encargo_valor_kwh_faturado_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010061'),0) = 0),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010011'),0),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010061'),0)) AS `encargo_kwh_faturado_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020061'),0) = 0),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020011'),0),IF((" & sTabela & ".`ta` = '01AL00'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020061'),0)) AS `encargo_valor_kwh_faturado_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302040030'),0) AS `mwh_medio`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302040031'),0) AS `mwh_medio_horas`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`micro_geracao` AS `micro_geracao`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020994') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0305020190') +`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0306020090') AS `valor reverso`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020993') AS `valor reverso ban`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302022010') AS `reverso ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302022110') AS `reverso ponta bandeira`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302022011') AS `reverso fora de ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302022111') AS `reverso fora de ponta bandeira`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0555020777') AS `dif.rel.rec`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'B'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302120009'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302120009')) AS `bandeira_vlr_kwh_fat`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302120010'),0) AS `bandeira_valor_kwh_fat_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302120011'),0) AS `bandeira_valor_kwh_fat_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020055'),0) AS `valor_energia_desconto_provisorio`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020056'),0) AS `valor_energia_desconto_realizado`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020057'),0) AS `valor_energia_devolucao_diferenca_ccee`," & vbCrLf
        lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020058'),0) AS `valor_energia_cobranca_diferenca_ccee`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010010') AS `consumo_ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010011') AS `consumo_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302012011') AS `reverso_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302013011') AS `reverso_residuo_fora_ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0303010090') AS `tusd`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010080') AS `consumo_medido`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020996') AS `reverso_lido`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`flgst` AS `flgst`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`datast` AS `datast`," & vbCrLf
        lstrsql = lstrsql & " " & sTabela & ".`datapag` AS `data_pagamento`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302022011') AS `reverso_fora_de__ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010190') AS `kwh_faturado__ponta`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010390') AS `kwh_faturado__Intermediaria`," & vbCrLf
        lstrsql = lstrsql & " `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302010290') AS `kwh_faturado__fora_ponta`," & vbCrLf

        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0314010090') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0314010091') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0314010092') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0314010093')) AS `Qtd_COMP. TE`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0313010090') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0313010091') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0313010092') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0313010093')) AS `Qtd_COMP. TUSD`," & vbCrLf

        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0314020090') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0314020091') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0314020092') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0314020093')) AS `Vlr_COMP. TE`," & vbCrLf
        lstrsql = lstrsql & " (`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0313020090') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0313020091') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0313020092') + `BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0313020093')) AS `Vlr_COMP. TUSD`," & vbCrLf

        If bReserva Then
            lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020123'),0) AS `valor_dif dem contr`, " & vbCrLf
            lstrsql = lstrsql & "       `reserva`.`numnf`   caderno, " & vbCrLf
            lstrsql = lstrsql & "       `reserva`.`mesano`  reserva_mesano, " & vbCrLf
            lstrsql = lstrsql & "       `reserva`.`datares` reserva_datares " & vbCrLf
        Else
            lstrsql = lstrsql & " IF((" & sTabela & ".`grupo` = 'A'),`BUSCAVALORITEM`(" & sTabela & ".`itensserv`,'0302020123'),0) AS `valor_dif dem contr` " & vbCrLf
        End If

        lstrsql = lstrsql & " FROM " & sTabela & vbCrLf
        lstrsql = lstrsql & " FORCE INDEX FOR JOIN (`keyflgst`, `keymesanoref`, `keymesano`, `keycons`, `keyzona`, `keylivro`, `keyconta`, `keyclasse`, `keydatapag`, `keygrupo`, `idx_numnf`) " & vbCrLf
        lstrsql = lstrsql & " LEFT JOIN `uc_faturamento` `uca` ON `uca`.`id` = " & sTabela & ".`unid_id` AND " & sTabela & ".`grupo` = 'A' " & vbCrLf
        lstrsql = lstrsql & " LEFT JOIN `uc_faturamento` `ucb` ON `ucb`.`id` = " & sTabela & ".`unid_id` AND " & sTabela & ".`grupo` = 'B' " & vbCrLf
        lstrsql = lstrsql & " LEFT JOIN `consum` ON `consum`.`consreg` = " & sTabela & ".`cons_id` " & vbCrLf
        lstrsql = lstrsql & " LEFT JOIN `classe` ON `classe`.`codigo` = " & sTabela & ".`codclas` " & vbCrLf

        If bReserva Then
            lstrsql = lstrsql & " LEFT JOIN `reserva` ON `reserva`.`numnf` = " & sTabela & ".`numnf` AND reserva.`mesano` = " & sTabela & ".`mesano`" & vbCrLf
        End If

        lstrsql = lstrsql & " WHERE 1 = 1 "
        lstrsql = lstrsql & sFiltro

        fRetornaView_Recebimentos = lstrsql

    End Function
    Public Function RemoverCaracteresEspeciais(ByVal strTexto As String) As String

        Dim strSemAcentos As String = Regex.Replace(strTexto.Normalize(System.Text.NormalizationForm.FormD), "[^a-zA-Z0-9_$%#*()\s]", " ")
        strSemAcentos = Regex.Replace(strSemAcentos, "[áàâãä]", "a")
        strSemAcentos = Regex.Replace(strSemAcentos, "[éèêë]", "e")
        strSemAcentos = Regex.Replace(strSemAcentos, "[íìîï]", "i")
        strSemAcentos = Regex.Replace(strSemAcentos, "[óòôõö]", "o")
        strSemAcentos = Regex.Replace(strSemAcentos, "[úùûü]", "u")
        strSemAcentos = Regex.Replace(strSemAcentos, "[ç]", "c")

        Return strSemAcentos
    End Function

    Public Function fRetornaNossoNumeroLinhaDigitavelCodBarrasSIGCB(ByVal strNumCooperativa As String, ByVal sNumCliente As String, ByVal sQtdeParcelas As String, ByVal sModalidade As String, ByVal iTipoFormulario As Integer, ByVal dVencimento As Date, ByVal sValorDocumento As Double)

        Dim ret As String
        Dim strnossonumero As String
        Dim iconstante As String = "29876543298765432"
        Dim iconstanteBeneficiario As String = "765432" '"8765432" 974880-6.
        Dim iconstanteCampoLivre As String = "987654329876543298765432"

        Dim FatorVencimento As Long
        Dim dVencimentobase As Date = "07/10/1997"
        Dim sLinhaDigitavel As String
        Dim strDV1, strDV2, strDV3, strDVcodbarras
        Dim strBeneficiarioCliente As String
        Dim sParcelaLinha As String
        Dim sValor As String
        Dim contador, mult, soma, resto As Integer
        Dim DVFinal As Integer
        Dim DVNossoNumero As Integer
        Dim sLinhaDigitavelRetorno As String
        Dim strnossonumeroDvFinal As String
        Dim strNossoNumeroCB As String
        Dim strsql As String


        Dim DVBeneficiario As String
        Dim DVCampoLivre As String

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try
            strDV1 = 1
            strDV2 = 2
            strDV3 = 3
            strDVcodbarras = 4

            sNumCliente = Replace(sNumCliente, "-", "")
            sNumCliente = Mid(sNumCliente, 1, 7)

            If Date.Now >= "22/05/2025" Then dVencimentobase = "22/05/2025"

            strnossonumero = GetConfig("nosso.numero.banco.faturas.boleto.sigcb")
            strnossonumero = Replace(strnossonumero, "-", "")
            strnossonumero = Replace(strnossonumero, "/", "")
            strnossonumero = Replace(strnossonumero, "\", "")
            strnossonumeroDvFinal = strnossonumero
            strNossoNumeroCB = strnossonumero

            sValor = Strings.Format(sValorDocumento, "#,##0.00")
            sValor = Replace(Replace(sValor, ".", ""), ",", "")
            sValor = PreparaCampo(sValor, 0, 10, "0")
            'sParcelaLinha = PreparaCampo(sQtdeParcelas, 0, 3, "0")
            strBeneficiarioCliente = PreparaCampo(sNumCliente, 0, 7, "0")
            'strNumCooperativa = PreparaCampo(strNumCooperativa, 0, 4, "0")
            'sNumCliente = PreparaCampo(sNumCliente, 0, 10, "0")
            strnossonumero = "14" & PreparaCampo(strnossonumero, 0, 15, "0")

            'Dim sMontagem As String = strNumCooperativa & sNumCliente & strnossonumero
            Dim sMontagem As String = strnossonumero

            '' DIGITO DO NOSSO NÚMERO
            soma = 0
            For contador = 1 To 17
                mult = Mid(sMontagem, contador, 1) * Mid(iconstante, contador, 1)
                soma = soma + mult
            Next

            resto = (soma Mod 11)

            DVNossoNumero = 11 - resto

            If DVNossoNumero > 9 Then DVNossoNumero = 0

            'strnossonumero = "14" & PreparaCampo(strnossonumeroDvFinal & DVNossoNumero, 0, 15, "0")

            ' DIGITO BENEFICIÁRIO
            Dim CalcBeneficiario As String
            Dim DigitoCB As Integer
            Dim DigitoConstante As Integer
            Dim iResultado As Integer
            Dim iTotalCalculo As Integer

            CalcBeneficiario = Mid(sNumCliente, 1, 6)

            For contador = 1 To 6 '7
                DigitoCB = Mid(CalcBeneficiario, contador, 1)
                DigitoConstante = Mid(iconstanteBeneficiario, contador, 1)
                iResultado = DigitoCB * DigitoConstante

                iTotalCalculo = iTotalCalculo + iResultado
            Next

            If iTotalCalculo >= 11 Then
                resto = (iTotalCalculo Mod 11)
                DVBeneficiario = 11 - resto

                If DVBeneficiario > 9 Then DVBeneficiario = 0

            Else
                DVBeneficiario = 11 - iTotalCalculo
                If DVBeneficiario > 9 Then DVBeneficiario = 0
            End If ' ok


            ' DIGITO CAMPO LIVRE
            Dim CalcCampoLivre As String
            Dim DigitoCL As Integer
            Dim DigitoConstanteCL As Integer
            Dim iResultadoCL As Integer
            Dim iTotalCalculoCL As Integer

            'CalcCampoLivre = Mid(sNumCliente, 1, 6) & DVBeneficiario & Mid(strnossonumero, 3, 3) & Mid(strnossonumero, 1, 1) & Mid(strnossonumero, 6, 3) & Mid(strnossonumero, 2, 1) & Mid(strnossonumero, 9, 9)
            CalcCampoLivre = Mid(sNumCliente, 1, 7) & Mid(strnossonumero, 3, 3) & Mid(strnossonumero, 1, 1) & Mid(strnossonumero, 6, 3) & Mid(strnossonumero, 2, 1) & Mid(strnossonumero, 9, 9)

            For contador = 1 To 24

                DigitoCL = Mid(CalcCampoLivre, contador, 1)
                DigitoConstanteCL = Mid(iconstanteCampoLivre, contador, 1)
                iResultadoCL = DigitoCL * DigitoConstanteCL

                iTotalCalculoCL = iTotalCalculoCL + iResultadoCL

            Next

            If iTotalCalculoCL >= 11 Then
                resto = (iTotalCalculoCL Mod 11)
                DVCampoLivre = 11 - resto

                If DVCampoLivre > 9 Then DVCampoLivre = 0

            Else
                DVCampoLivre = 11 - iTotalCalculoCL
                If DVCampoLivre > 9 Then DVCampoLivre = 0
            End If

            FatorVencimento = DateDiff(Microsoft.VisualBasic.DateInterval.Day, dVencimentobase, dVencimento) ' 8012
            'FatorVencimento = FatorVencimento + 1000
            'FatorVencimento = PreparaCampo(FatorVencimento, 0, 4, "0")

            Dim sMontagemCodBarras As String
            Dim sCampoLivre As String

            'sCampoLivre = Mid(strBeneficiarioCliente, 1, 6)
            'sCampoLivre = sCampoLivre & DVBeneficiario
            'sCampoLivre = sCampoLivre & Mid(strnossonumero, 3, 3)
            'sCampoLivre = sCampoLivre & Mid(strnossonumero, 1, 1)
            'sCampoLivre = sCampoLivre & Mid(strnossonumero, 6, 3)
            'sCampoLivre = sCampoLivre & Mid(strnossonumero, 2, 1)
            'sCampoLivre = sCampoLivre & Mid(strnossonumero, 9, 9)
            'sCampoLivre = sCampoLivre & DVCampoLivre '- ok


            sMontagemCodBarras = "104"
            sMontagemCodBarras = sMontagemCodBarras & "9"
            sMontagemCodBarras = sMontagemCodBarras & "X"
            sMontagemCodBarras = sMontagemCodBarras & FatorVencimento
            sMontagemCodBarras = sMontagemCodBarras & sValor
            sMontagemCodBarras = sMontagemCodBarras & strBeneficiarioCliente
            sMontagemCodBarras = sMontagemCodBarras & Mid(strnossonumero, 3, 3)
            sMontagemCodBarras = sMontagemCodBarras & Mid(strnossonumero, 1, 1)
            sMontagemCodBarras = sMontagemCodBarras & Mid(strnossonumero, 6, 3)
            sMontagemCodBarras = sMontagemCodBarras & Mid(strnossonumero, 2, 1)
            sMontagemCodBarras = sMontagemCodBarras & Mid(strnossonumero, 9, 9)
            sMontagemCodBarras = sMontagemCodBarras & DVCampoLivre ' - ok

            ' PARTE 1
            sLinhaDigitavel = "104"
            sLinhaDigitavel = sLinhaDigitavel & "9"
            sLinhaDigitavel = sLinhaDigitavel & Mid(sMontagemCodBarras, 20, 5)
            sLinhaDigitavel = sLinhaDigitavel & strDV1

            ' PARTE 2
            sLinhaDigitavel = sLinhaDigitavel & Mid(sMontagemCodBarras, 25, 10)
            sLinhaDigitavel = sLinhaDigitavel & strDV2

            ' PARTE 3
            sLinhaDigitavel = sLinhaDigitavel & Mid(sMontagemCodBarras, 35, 10)
            sLinhaDigitavel = sLinhaDigitavel & strDV3

            ' PARTE 4
            sLinhaDigitavel = sLinhaDigitavel & strDVcodbarras

            ' PARTE 5
            sLinhaDigitavel = sLinhaDigitavel & Mid(sMontagemCodBarras, 6, 4)
            sLinhaDigitavel = sLinhaDigitavel & Mid(sMontagemCodBarras, 10, 10)

            sLinhaDigitavelRetorno = ValidaLinhaDigitavelCodBarrasSIGCB(sLinhaDigitavel, sMontagemCodBarras, DVNossoNumero)

            'ret = strNumCooperativa & sNumCliente & sQtdeParcelas & sModalidade & iTipoFormulario

            strsql = "UPDATE configuracoes SET valor = " & CInt(strnossonumeroDvFinal) + 1 & ", valor_padrao = " & CInt(strnossonumeroDvFinal) + 1 & " WHERE nome = " & "'nosso.numero.banco.faturas.boleto.sigcb'" '"
            objAtualizaBancoDados.QUERY_NAME = strsql
            repositorioAtualizaBancoDados.Execute(objAtualizaBancoDados)

            fRetornaNossoNumeroLinhaDigitavelCodBarrasSIGCB = sLinhaDigitavelRetorno

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: fRetornaNossoNumeroLinhaDigitavelCodBarrasSIGCB" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function


    Public Function ValidaLinhaDigitavelCodBarrasSIGCB(ByVal linhadigitavel As String, ByVal sCodBarras As String, ByVal iDigitoNossoNumero As Integer) As String

        Dim indice = "2121212120121212121201212121212"
        Dim indiceCodBarra = "43290876543298765432987654329876543298765432"
        Dim digito, soma, mult, contador As Integer
        Dim codigobarra As String
        Dim sParte1Linha As String
        Dim sParte2Linha As String
        Dim sParte3Linha As String
        Dim sParte4Linha As String
        Dim MontaLinhaDigitavel As String
        Dim sDigitoSubstituivel As String = "0"

        Try
            ''  //cálculo do primeiro dígito
            soma = 0
            digito = 0
            For contador = 1 To 9
                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If
            Next

            digito = Multiplo10(soma) '- soma
            digito = digito - soma

            sParte1Linha = Mid(linhadigitavel, 1, 9) & digito

            ''  //cálculo do segundo dígito
            soma = 0
            digito = 0
            For contador = 11 To 20
                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If
            Next

            digito = Multiplo10(soma) '– soma;
            digito = digito - soma

            sParte2Linha = Mid(linhadigitavel, 11, 10) & digito

            '  //cálculo do terceiro dígito
            soma = 0
            digito = 0
            For contador = 22 To 31

                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If

            Next

            digito = Multiplo10(soma)
            digito = digito - soma

            sParte3Linha = Mid(linhadigitavel, 22, 10) & digito
            sParte4Linha = sDigitoSubstituivel & Mid(linhadigitavel, 34, 14)

            MontaLinhaDigitavel = sParte1Linha & sParte2Linha & sParte3Linha & sParte4Linha

            Dim resto
            soma = 0
            digito = 0
            For contador = 1 To 44
                If contador <> 5 Then
                    mult = Mid(sCodBarras, contador, 1) * Mid(indiceCodBarra, contador, 1)
                    soma = soma + mult
                End If
            Next

            digito = 11 - (soma Mod 11)

            If (digito < 1) Or (digito > 9) Then digito = 1

            sParte4Linha = digito & Mid(linhadigitavel, 34, 14)

            MontaLinhaDigitavel = sParte1Linha & sParte2Linha & sParte3Linha & sParte4Linha

            sCodBarras = Replace(sCodBarras, "X", digito)

            ValidaLinhaDigitavelCodBarrasSIGCB = MontaLinhaDigitavel & ";" & sCodBarras & ";" & iDigitoNossoNumero

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ValidaLinhaDigitavelCodBarrasSIGCB" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function fRetornaBancoImpFat(ByVal sBanco As String) As String

        Try

            Dim lStrSQL As String

            Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
            Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados
            Dim dt As DataTable

            lStrSQL = ""
            lStrSQL = "SELECT nrbco FROM Bancos WHERE ban_id = '" & sBanco & "'"
            objAtualizaBancoDados.QUERY_NAME = lStrSQL
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    fRetornaBancoImpFat = Trim(IIf(IsDBNull(row.Item("nrbco")), "", row.Item("nrbco")))
                Next
            Else
                fRetornaBancoImpFat = ""
            End If

            Return fRetornaBancoImpFat

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: fRetornaBancoImpFat" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try


    End Function
    Public Function fRetornaNossoNumeroLinhaDigitavelCodBarrasITAU(ByVal strNumCooperativa As String, ByVal sNumCliente As String, ByVal sQtdeParcelas As String, ByVal sModalidade As String, ByVal iTipoFormulario As Integer, ByVal dVencimento As Date, ByVal sValorDocumento As Double)

        Dim ret As String
        Dim strnossonumero As String
        Dim iconstante As String = "12121212121212121212" '"54329876543298765432"
        Dim iconstanteBeneficiario As String = "765432" '"8765432" 974880-6.
        Dim iconstanteCampoLivre As String = "987654329876543298765432"

        Dim FatorVencimento As Long
        Dim dVencimentobase As Date = "07/10/1997"
        Dim sLinhaDigitavel As String
        Dim strDV1, strDV2, strDV3, strDVcodbarras
        Dim strBeneficiarioCliente As String
        Dim sParcelaLinha As String
        Dim sValor As String
        Dim contador, mult, soma, resto As Integer
        Dim DVFinal As Integer
        Dim DVNossoNumero As Integer
        Dim sLinhaDigitavelRetorno As String
        Dim strnossonumeroDvFinal As String
        Dim strNossoNumeroCB As String
        Dim strsql As String
        Dim sAgencia, sConta, sCarteira, sDgConta, sSubCarteira As String

        Dim DVBeneficiario As String
        Dim DVCampoLivre As String

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

        Try
            strDV1 = 1
            strDV2 = 2
            strDV3 = 3
            strDVcodbarras = 4

            sAgencia = GetConfig("agencia.codbeneficiario.banco.faturas.boleto.itau")
            sAgencia = Mid(sAgencia, 1, 4)
            sConta = GetConfig("numero.conta.corrente.itau")
            sConta = Replace(sConta, "-", "")
            sDgConta = Right(sConta, 1)
            sCarteira = "109"
            sSubCarteira = "198"

            sNumCliente = Replace(sNumCliente, "-", "")
            sNumCliente = Mid(sNumCliente, 1, 7)

            If Date.Now >= "22/02/2025" Then dVencimentobase = "22/02/2025"

            strnossonumero = GetConfig("nosso.numero.banco.faturas.boleto.itau")
            strnossonumero = Replace(strnossonumero, "-", "")
            strnossonumero = Replace(strnossonumero, "/", "")
            strnossonumero = Replace(strnossonumero, "\", "")
            strnossonumeroDvFinal = strnossonumero
            strNossoNumeroCB = strnossonumero

            sValor = Strings.Format(sValorDocumento, "#,##0.00")
            sValor = Replace(Replace(sValor, ".", ""), ",", "")
            sValor = PreparaCampo(sValor, 0, 10, "0")

            strBeneficiarioCliente = PreparaCampo(sNumCliente, 0, 7, "0")
            strnossonumero = PreparaCampo(strnossonumero, 0, 8, "0")

            Dim sMontagem As String = sAgencia & Mid(sConta, 1, 5) & sCarteira & strnossonumero

            '' DIGITO DO NOSSO NÚMERO ****************
            soma = 0
            For contador = 1 To 20
                mult = Mid(sMontagem, contador, 1) * Mid(iconstante, contador, 1)

                ' Conforme manual em caso de 2 digitos deve se somar separado
                If Len(CStr(mult)) = 2 Then
                    Dim d1, d2 As String
                    d1 = Mid(CStr(mult), 1, 1)
                    d2 = Mid(CStr(mult), 2, 1)

                    mult = CInt(d1) + CInt(d2)
                    'mult = Mid(CStr(mult), 1, 1) + Mid(CStr(mult), 2, 1)
                End If

                soma = soma + mult
            Next

            resto = (soma Mod 10) 'Multiplo10(soma)
            'resto = resto - soma '(soma Mod 10)

            If resto = 0 Then
                DVNossoNumero = 0
            Else
                DVNossoNumero = 10 - resto
            End If
            '' DIGITO DO NOSSO NÚMERO *****************

            FatorVencimento = DateDiff(Microsoft.VisualBasic.DateInterval.Day, dVencimentobase, dVencimento) ' 8012

            Dim sMontagemCodBarras As String
            Dim sCampoLivre As String


            sMontagemCodBarras = "341" ' 3
            sMontagemCodBarras = sMontagemCodBarras & "9" '4
            sMontagemCodBarras = sMontagemCodBarras & "X" '5
            sMontagemCodBarras = sMontagemCodBarras & FatorVencimento ' 9
            sMontagemCodBarras = sMontagemCodBarras & sValor ' 19
            sMontagemCodBarras = sMontagemCodBarras & sCarteira '22
            sMontagemCodBarras = sMontagemCodBarras & strnossonumero '30
            sMontagemCodBarras = sMontagemCodBarras & DVNossoNumero '31
            sMontagemCodBarras = sMontagemCodBarras & sAgencia ' 35
            sMontagemCodBarras = sMontagemCodBarras & Mid(sConta, 1, 5) ' 40
            sMontagemCodBarras = sMontagemCodBarras & sDgConta '41
            sMontagemCodBarras = sMontagemCodBarras & "000" ' 44

            ' PARTE 1
            sLinhaDigitavel = "341"
            sLinhaDigitavel = sLinhaDigitavel & "9"
            sLinhaDigitavel = sLinhaDigitavel & sCarteira 'Mid(sMontagemCodBarras, 20, 5)
            sLinhaDigitavel = sLinhaDigitavel & Mid(strnossonumero, 1, 2)
            sLinhaDigitavel = sLinhaDigitavel & strDV1

            ' PARTE 2
            sLinhaDigitavel = sLinhaDigitavel & Mid(strnossonumero, 3)
            sLinhaDigitavel = sLinhaDigitavel & DVNossoNumero
            sLinhaDigitavel = sLinhaDigitavel & Mid(sAgencia, 1, 3)
            sLinhaDigitavel = sLinhaDigitavel & strDV2

            ' PARTE 3
            sLinhaDigitavel = sLinhaDigitavel & Mid(sAgencia, 4)
            sLinhaDigitavel = sLinhaDigitavel & sConta '& sDgConta 
            sLinhaDigitavel = sLinhaDigitavel & "000"
            sLinhaDigitavel = sLinhaDigitavel & strDV3

            ' PARTE 4
            sLinhaDigitavel = sLinhaDigitavel & strDVcodbarras

            ' PARTE 5
            sLinhaDigitavel = sLinhaDigitavel & FatorVencimento
            sLinhaDigitavel = sLinhaDigitavel & sValor

            sLinhaDigitavelRetorno = ValidaLinhaDigitavelCodBarrasITAU(sLinhaDigitavel, sMontagemCodBarras, DVNossoNumero)

            'ret = strNumCooperativa & sNumCliente & sQtdeParcelas & sModalidade & iTipoFormulario

            strsql = "UPDATE configuracoes SET valor = " & CInt(strnossonumeroDvFinal) + 1 & ", valor_padrao = " & CInt(strnossonumeroDvFinal) + 1 & " WHERE nome = " & "'nosso.numero.banco.faturas.boleto.itau'" '"
            objAtualizaBancoDados.QUERY_NAME = strsql
            repositorioAtualizaBancoDados.Execute(objAtualizaBancoDados)

            fRetornaNossoNumeroLinhaDigitavelCodBarrasITAU = sLinhaDigitavelRetorno

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: fRetornaNossoNumeroLinhaDigitavelCodBarrasITAU" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function ValidaLinhaDigitavelCodBarrasITAU(ByVal linhadigitavel As String, ByVal sCodBarras As String, ByVal iDigitoNossoNumero As Integer) As String

        Dim indice = "2121212120121212121201212121212"
        Dim indiceCodBarra = "43290876543298765432987654329876543298765432"
        Dim digito, soma, mult, contador As Integer
        Dim codigobarra As String
        Dim sParte1Linha As String
        Dim sParte2Linha As String
        Dim sParte3Linha As String
        Dim sParte4Linha As String
        Dim MontaLinhaDigitavel As String
        Dim sDigitoSubstituivel As String = "0"

        Try
            ''  //cálculo do primeiro dígito
            soma = 0
            digito = 0
            For contador = 1 To 9
                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If
            Next

            digito = Multiplo10(soma) '- soma
            digito = digito - soma

            sParte1Linha = Mid(linhadigitavel, 1, 9) & digito

            ''  //cálculo do segundo dígito
            soma = 0
            digito = 0
            For contador = 11 To 20
                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If
            Next

            digito = Multiplo10(soma) '– soma;
            digito = digito - soma

            sParte2Linha = Mid(linhadigitavel, 11, 10) & digito

            '  //cálculo do terceiro dígito
            soma = 0
            digito = 0
            For contador = 22 To 31

                mult = Mid(linhadigitavel, contador, 1) * Mid(indice, contador, 1)
                If mult >= 10 Then
                    soma = soma + Mid(mult, 1, 1) + Mid(mult, 2, 1)
                Else
                    soma = soma + mult
                End If

            Next

            digito = Multiplo10(soma)
            digito = digito - soma

            sParte3Linha = Mid(linhadigitavel, 22, 10) & digito
            sParte4Linha = sDigitoSubstituivel & Mid(linhadigitavel, 34, 14)

            MontaLinhaDigitavel = sParte1Linha & sParte2Linha & sParte3Linha & sParte4Linha

            Dim resto
            soma = 0
            digito = 0
            For contador = 1 To 44
                If contador <> 5 Then
                    mult = Mid(sCodBarras, contador, 1) * Mid(indiceCodBarra, contador, 1)
                    soma = soma + mult
                End If
            Next

            digito = 11 - (soma Mod 11)

            If (digito < 1) Or (digito > 9) Then digito = 1

            sParte4Linha = digito & Mid(linhadigitavel, 34, 14)

            MontaLinhaDigitavel = sParte1Linha & sParte2Linha & sParte3Linha & sParte4Linha

            sCodBarras = Replace(sCodBarras, "X", digito)

            ValidaLinhaDigitavelCodBarrasITAU = MontaLinhaDigitavel & ";" & sCodBarras & ";" & iDigitoNossoNumero

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: ValidaLinhaDigitavelCodBarrasITAU" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try

    End Function

    Public Function fRetornaGrupoTarifario(ByVal sClasse As String) As String

        Dim lstrsql As String
        Dim sClasseConsulta As String

        Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
        Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados
        Dim dt As DataTable

        Try

            If VB.Left(sClasse, 1) = "0" Or VB.Left(sClasse, 1) = "1" Or VB.Left(sClasse, 1) = "2" Or VB.Left(sClasse, 1) = "3" Then
                sClasseConsulta = "0000"
            ElseIf VB.Left(sClasse, 1) = "9" Then
                sClasseConsulta = "9100"
            ElseIf VB.Left(sClasse, 1) = "4" Then
                sClasseConsulta = "4000"
            ElseIf VB.Left(sClasse, 1) = "5" Then
                sClasseConsulta = "5000"
            ElseIf VB.Left(sClasse, 1) = "6" Then
                If VB.Left(sClasse, 2) = "64" Then
                    sClasseConsulta = "6400"
                Else
                    sClasseConsulta = "6100"
                End If
            ElseIf VB.Left(sClasse, 1) = "7" Then
                sClasseConsulta = "7100"
            ElseIf VB.Left(sClasse, 1) = "8" Then
                sClasseConsulta = "8100"
            End If

            lstrsql = "SELECT descricao FROM classe WHERE codigo = " & sClasseConsulta & ""
            objAtualizaBancoDados.QUERY_NAME = lstrsql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    fRetornaGrupoTarifario = IIf(IsDBNull(row("descricao")), "", row("descricao"))
                Next
            Else
                fRetornaGrupoTarifario = ""
            End If

        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: fRetornaGrupoTarifario" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Function
        End Try
    End Function

End Module





