Global all, tip, cpf1, nome1 As String
Global nametf, cp, col As Boolean
Global c1, n1 As String
Global id1, id2, id3, id4, id5, id6, id7, id8 As String
Global id_g, id_hist As Integer
Global login, senha As String
Public Function lista(ByVal all As String)

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset
    Dim warr() As String

    login = Sheets("MAIN").Range("IT65533").Value
    senha = Sheets("MAIN").Range("IT65534").Value

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    Worksheets("MAIN").Range("A4:AB60000").ClearContents

    Set rst = conexao.Execute("Select * from [Aquila].[dbo].[tb_clientes_n]")

    If col = True Then
        warr = Split(all, ";")

        For i = LBound(warr) To UBound(warr)

            Sheet1.Cells(4, i + 1) = warr(i)

        Next i

        j = 1
        k = 5

        Do Until rst.EOF
            Do Until j = i + 2
                colll = Sheet1.Cells(4, j).Value

                Sheet1.Cells(k, j) = rst.Fields(colll).Value

                j = j + 1
            Loop
            j = 1
            k = k + 1
            rst.MoveNext
        Loop
    Else:
        i = 5
        Do Until rst.EOF
            Sheet1.Cells(i, 1) = rst.Fields(LCase(Sheet1.Cells(4, 1).Value))
            Sheet1.Cells(i, 2) = rst.Fields(LCase(Sheet1.Cells(4, 2).Value))
            Sheet1.Cells(i, 3) = rst.Fields(Sheet1.Cells(4, 3).Value)
            Sheet1.Cells(i, 4) = rst.Fields(Sheet1.Cells(4, 4).Value)
            Sheet1.Cells(i, 5) = rst.Fields(Sheet1.Cells(4, 5).Value)
            Sheet1.Cells(i, 6) = rst.Fields(Sheet1.Cells(4, 6).Value)
            Sheet1.Cells(i, 7) = rst.Fields(Sheet1.Cells(4, 7).Value)
            If rst.Fields(Sheet1.Cells(4, 8).Value) = "True" Then
                Sheet1.Cells(i, 8) = "Sim"
            Else:
                If rst.Fields(Sheet1.Cells(4, 8).Value) = "False" Then
                    Sheet1.Cells(i, 8) = "Não"
                End If
            End If
            Sheet1.Cells(i, 9) = rst.Fields(Sheet1.Cells(4, 9).Value)
            Sheet1.Cells(i, 10) = rst.Fields(Sheet1.Cells(4, 10).Value)
            Sheet1.Cells(i, 11) = rst.Fields(Sheet1.Cells(4, 11).Value)
            Sheet1.Cells(i, 12) = rst.Fields(Sheet1.Cells(4, 12).Value)
            Sheet1.Cells(i, 13) = rst.Fields(Sheet1.Cells(4, 13).Value)
            Sheet1.Cells(i, 14) = rst.Fields(Sheet1.Cells(4, 14).Value)
            Sheet1.Cells(i, 15) = rst.Fields(Sheet1.Cells(4, 15).Value)
            Sheet1.Cells(i, 16) = rst.Fields(Sheet1.Cells(4, 16).Value)

            i = i + 1
            rst.MoveNext
        Loop
    End If

    conexao.Close

End Function
Function pesquisa(ByVal all As String)

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset

    login = Sheets("MAIN").Range("IT65533").Value
    senha = Sheets("MAIN").Range("IT65534").Value

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    Worksheets("MAIN").Range("A4:AB60000").ClearContents

    If cp = True Then
        Set rst = conexao.Execute("Select * from [Aquila].[dbo].[tb_clientes_n] WHERE [cpf] = '" & Replace(Replace(Trim(cpf1), ".", ""), "-", "") & "'")

        If col = True Then
            warr = Split(all, ";")

            For i = LBound(warr) To UBound(warr)

                Sheet1.Cells(4, i + 1) = warr(i)

            Next i

            j = 1
            k = 5

            Do Until rst.EOF
                Do Until j = i
                    colll = Sheet1.Cells(4, j).Value

                    Sheet1.Cells(k, j) = rst.Fields(colll).Value

                    j = j + 1
                Loop
                j = 1
                k = k + 1
                rst.MoveNext
            Loop
        Else:
            Do Until rst.EOF
                j = 1
                k = 5
                Do Until j = 29
                    colll = Sheet2.Cells(1, j).Value

                    Sheet1.Cells(k, j) = rst.Fields(colll).Value

                    j = j + 1
                Loop
                j = 1
                k = k + 1
                rst.MoveNext
            Loop
        End If
    End If
    If nametf = True Then
        Set rst = conexao.Execute("Select * from [Aquila].[dbo].[tb_clientes_n] WHERE [nome] LIKE '%" & Trim(nome1) & "%'")

        If col = True Then
            warr = Split(all, ";")

            For i = LBound(warr) To UBound(warr)

                Sheet1.Cells(4, i + 1) = warr(i)

            Next i

            j = 1
            k = 5

            Do Until rst.EOF
                Do Until j = i
                    colll = Sheet1.Cells(4, j).Value

                    Sheet1.Cells(k, j) = rst.Fields(colll).Value

                    j = j + 1
                Loop
                j = 1
                k = k + 1
                rst.MoveNext
            Loop
        Else:

            j = 1
            k = 5
            Do Until j = 29
                colll = Sheet2.Cells(j, 1).Value
                Sheet1.Cells(4, j) = colll

                j = j + 1
            Loop

            j = 1
            k = 5

            Do Until rst.EOF
                Do Until j = 29
                    colll = Sheet2.Cells(j, 1).Value
                    Sheet1.Cells(k, j) = rst.Fields(colll).Value

                    j = j + 1
                Loop

                rst.MoveNext
                k = k + 1
                j = 1
            Loop
        End If
    End If

    conexao.Close

End Function
Function getname(ByVal id As String)

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset

    login = Sheets("MAIN").Range("IT65533").Value
    senha = Sheets("MAIN").Range("IT65534").Value

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    Set rst = conexao.Execute("SELECT [nome] FROM [Aquila].[dbo].[tb_clientes_n] WHERE [id_cliente] = '" & id & "'")

    n1 = rst.Fields("nome").Value

End Function
Function getid(ByVal cpp As String)

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset

    login = Sheets("MAIN").Range("IT65533").Value
    senha = Sheets("MAIN").Range("IT65534").Value

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    Set rst = conexao.Execute("SELECT [id_cliente] FROM [Aquila].[dbo].[tb_clientes_n] WHERE [cpf] = '" & cpp & "'")

    c1 = rst.Fields("id_cliente").Value

End Function
Function getcontact(ByVal id As String)

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset

    login = Sheets("MAIN").Range("IT65533").Value
    senha = Sheets("MAIN").Range("IT65534").Value

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    Set rst = conexao.Execute("SELECT * FROM [Aquila].[dbo].[tb_hist_cont] WHERE [id_cliente] = '" & id & "'")

    xy = 1

    Do Until rst.EOF

        If xy = 1 Then
            contato.Label7.Caption = rst.Fields("data").Value
            id1 = rst.Fields("id_historico").Value
        End If
        If xy = 2 Then
            contato.Label8.Caption = rst.Fields("data").Value
            id2 = rst.Fields("id_historico").Value
        End If
        If xy = 3 Then
            contato.Label9.Caption = rst.Fields("data").Value
            id3 = rst.Fields("id_historico").Value
        End If
        If xy = 4 Then
            contato.Label10.Caption = rst.Fields("data").Value
            id4 = rst.Fields("id_historico").Value
        End If
        If xy = 5 Then
            contato.Label11.Caption = rst.Fields("data").Value
            id5 = rst.Fields("id_historico").Value
        End If
        If xy = 6 Then
            contato.Label12.Caption = rst.Fields("data").Value
            id6 = rst.Fields("id_historico").Value
        End If
        If xy = 7 Then
            contato.Label13.Caption = rst.Fields("data").Value
            id7 = rst.Fields("id_historico").Value
        End If
        If xy = 8 Then
            contato.Label14.Caption = rst.Fields("data").Value
            id8 = rst.Fields("id_historico").Value
        End If

        xy = xy + 1
        rst.MoveNext

    Loop

End Function
Function getcontactinfo(ByVal id_c As String)

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset

    login = Sheets("MAIN").Range("IT65533").Value
    senha = Sheets("MAIN").Range("IT65534").Value

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    Set rst = conexao.Execute("SELECT * FROM [Aquila].[dbo].[tb_hist_cont] WHERE [id_historico] = '" & id_c & "'")

    telaconsulta.Label1.Caption = rst.Fields("id_cliente")
    telaconsulta.Label2.Caption = rst.Fields("id_unidade")
    telaconsulta.Label3.Caption = rst.Fields("id_sucessor")
    telaconsulta.Label4.Caption = rst.Fields("titulo")
    telaconsulta.Label5.Caption = rst.Fields("contratado")
    telaconsulta.Label6.Caption = rst.Fields("origem_conta")
    telaconsulta.Label7.Caption = rst.Fields("data_recebimento_doc")
    telaconsulta.Label8.Caption = rst.Fields("data")
    telaconsulta.Label9.Caption = rst.Fields("sistema")
    telaconsulta.Label10.Caption = rst.Fields("tipo_contato")
    telaconsulta.Label11.Caption = rst.Fields("meio_contato")
    telaconsulta.Label12.Caption = rst.Fields("detalhe_meio_contato")
    telaconsulta.Label13.Caption = rst.Fields("data_retornar_contato")
    telaconsulta.Label14.Caption = rst.Fields("melhor_horario")
    telaconsulta.Label15.Caption = rst.Fields("descricao")

End Function
Function sendcontact()

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset

    login = Sheets("MAIN").Range("IT65533").Value
    senha = Sheets("MAIN").Range("IT65534").Value

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    cquery = "INSERT INTO [dbo].[tb_hist_cont]" & _
        "([id_historico]" & _
        ",[id_cliente]" & _
        ",[data]" & _
        ",[descricao]" & _
        ",[id_unidade]" & _
        ",[titulo]" & _
        ",[origem_conta]" & _
        ",[meio_contato]" & _
        ",[detalhe_meio_contato]" & _
        ",[contratado]" & _
        ",[tipo_contato]" & _
        ",[sistema]" & _
        ",[id_sucessor]" & _
        ",[data_recebimento_doc]" & _
        ",[data_retornar_contato]" & _
        ",[melhor_horario])"
     cquery2 = " Values (" & id_hist & _
        ",'" & UserForm1.TextBox1.Value & "'" & _
        ",'" & UserForm1.TextBox9.Value & "'" & _
        ",'" & UserForm1.TextBox6.Value & "'" & _
        ",'" & UserForm1.TextBox2.Value & "'" & _
        ",'" & UserForm1.TextBox4.Value & "'" & _
        ",'" & UserForm1.TextBox14.Value & "'" & _
        ",'" & UserForm1.TextBox11.Value & "'" & _
        ",'" & UserForm1.TextBox7.Value & "'" & _
        ",'" & UserForm1.TextBox5.Value & "'" & _
        ",'" & UserForm1.TextBox10.Value & "'" & _
        ",'" & UserForm1.TextBox8.Value & "'" & _
        ",'" & UserForm1.TextBox3.Value & "'" & _
        ",'" & UserForm1.TextBox15.Value & "'" & _
        ",'" & UserForm1.TextBox12.Value & "'" & _
        ",'" & UserForm1.TextBox13.Value & "')"

        conexao.Execute cquery & cquery2

End Function
Function testasql(login, senha)

    On Error GoTo handler

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    zt = True
    MsgBox "Login efetuado com sucesso", vbInformation
    Sheets("MAIN").Range("IT65533").Value = login
    Sheets("MAIN").Range("IT65534").Value = senha

handler:
If zt <> True Then
MsgBox "Credenciais inválidas!", vbCritical
End If
Exit Function

End Function
Function newid()

    Dim conexao As ADODB.Connection
    Set conexao = New ADODB.Connection
    Dim cquery, cquery2 As Variant
    Dim rst As ADODB.Recordset

    strconexao = "Provider=SQLOLEDB.1;" & _
                "Persist Security Info=False;" & _
                "Initial Catalog=Aquila;" & _
                "Data Source=DESKTOP-9CLNCT1\LDB;" & _
                "User ID=" & login & ";Password=" & senha & ";"

    conexao.ConnectionString = strconexao
    conexao.Open

    Set rst = conexao.Execute("SELECT MAX(id_historico) FROM [Aquila].[dbo].[tb_hist_cont]")

    id_hist = (rst.Fields("id_historico").Value) + 1

End Function