Attribute VB_Name = "AlertaVencimentos"
Option Explicit
'==============================================================================
' AUTOMACAO DO CONTROLE DE VENCIMENTOS DE REQUISITOS LEGAIS - QSMS
' Contem 3 recursos:
'   1) EnviarAlertasVencimento -> e-mail automatico por responsavel (Outlook)
'   2) RegistrarRevisao / PerguntarRevisaoAoSalvar -> grava numero e data da
'      revisao na aba REVISOES
'   3) CriarBotoes -> cria os botoes reais no PAINEL
'
' COMO INSTALAR (uma unica vez):
'  1. Abra CONTROLE_VENCIMENTOS_QSMS.xlsx. Arquivo > Salvar como > escolha
'     "Pasta de Trabalho Habilitada para Macro do Excel (*.xlsm)".
'  2. Pressione Alt+F11 (abre o editor do VBA).
'  3. Menu Arquivo > Importar arquivo... > selecione este arquivo (.bas).
'  4. No painel da esquerda, de duplo clique em "ThisWorkbook" e cole TODO
'     o texto abaixo (entre as linhas ----):
'     ---------------------------------------------------------------
'     Private Sub Workbook_Open()
'         If Weekday(Date, vbMonday) <= 5 Then EnviarAlertasVencimento
'     End Sub
'     Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'         MarcarAlteracao Sh.Name
'     End Sub
'     Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'         PerguntarRevisaoAoSalvar
'     End Sub
'     ---------------------------------------------------------------
'  5. Feche o editor. Pressione Alt+F8, escolha "CriarBotoes" e clique em
'     Executar. Dois botoes aparecem no PAINEL (linha 4).
'  6. Preencha o e-mail do responsavel (REQUISITOS, coluna H) e o e-mail
'     central em PARAMETROS!C10.
'  7. Teste com MODO_TESTE = True (abaixo): os e-mails sao apenas exibidos.
'     Depois mude para False para enviar de verdade.
'
' Se o Excel bloquear a macro: botao direito no arquivo > Propriedades >
' Desbloquear. Requisitos: Windows + Outlook instalado e configurado.
' Nao funciona no Excel da web nem em celular.
'
' REGRA DE E-MAIL: 1 e-mail por responsavel listando itens VENCIDO/CRITICO/
' ALERTA. Cada item so e reenviado apos PARAMETROS!C11 dias (padrao 7).
' Cada envio e registrado na aba LOG_ALERTAS.
' REGRA DE REVISAO: ao salvar, se LISTA_MESTRA ou REQUISITOS/HISTORICO/
' PARAMETROS foram alterados, o Excel pergunta se deseja registrar a revisao
' e pede a descricao; o numero e calculado pela aba REVISOES.
'==============================================================================
Public gAltLM As Boolean
Public gAltCt As Boolean
Private Const DOC_LM As String = "Lista Mestra (FOR-STN-QSMS-004)"
Private Const DOC_CT As String = "Controle de Vencimentos"
Private Const MODO_TESTE As Boolean = True
Private Const PRIM_LIN As Long = 6
Private Const ULT_LIN As Long = 505

Public Sub EnviarAlertasVencimento()
    Dim wsR As Worksheet, wsL As Worksheet, wsP As Worksheet
    Set wsR = ThisWorkbook.Worksheets("REQUISITOS")
    Set wsL = ThisWorkbook.Worksheets("LOG_ALERTAS")
    Set wsP = ThisWorkbook.Worksheets("PARAMETROS")

    Dim diasReenvio As Long, emailCentral As String
    diasReenvio = Val(wsP.Range("C11").Value)
    If diasReenvio <= 0 Then diasReenvio = 7
    emailCentral = Trim(CStr(wsP.Range("C10").Value))

    ' Ultimo envio por ID (le o log)
    Dim ultimo As Object: Set ultimo = CreateObject("Scripting.Dictionary")
    Dim r As Long, lastLog As Long, id As String
    lastLog = wsL.Cells(wsL.Rows.Count, 1).End(xlUp).Row
    For r = 6 To lastLog
        If wsL.Cells(r, 8).Value = "ENVIADO" Then
            id = CStr(wsL.Cells(r, 2).Value)
            If Not ultimo.Exists(id) Then
                ultimo(id) = CDate(wsL.Cells(r, 1).Value)
            ElseIf CDate(wsL.Cells(r, 1).Value) > ultimo(id) Then
                ultimo(id) = CDate(wsL.Cells(r, 1).Value)
            End If
        End If
    Next r

    ' Agrupa itens por destinatario
    Dim corpos As Object: Set corpos = CreateObject("Scripting.Dictionary")
    Dim itens As Object: Set itens = CreateObject("Scripting.Dictionary")
    Dim sit As String, email As String, linha As String
    For r = PRIM_LIN To ULT_LIN
        sit = CStr(wsR.Cells(r, 15).Value)
        If sit = "VENCIDO" Or sit = ChrW(67) & "R" & ChrW(205) & "TICO" Or sit = "ALERTA" Then
            email = Trim(CStr(wsR.Cells(r, 8).Value))
            id = CStr(wsR.Cells(r, 1).Value)
            If email <> "" Then
                If Not ultimo.Exists(id) Or (Date - Int(IIf(ultimo.Exists(id), ultimo(id), 0))) >= diasReenvio Then
                    linha = id & " | " & wsR.Cells(r, 3).Value & " | Vencimento: " & Format(wsR.Cells(r, 13).Value, "dd/mm/yyyy") & _
                            " | " & sit & " (" & wsR.Cells(r, 14).Value & " dias)"
                    If corpos.Exists(email) Then
                        corpos(email) = corpos(email) & vbCrLf & linha
                        itens(email) = itens(email) & "," & r
                    Else
                        corpos(email) = linha
                        itens(email) = CStr(r)
                    End If
                End If
            End If
        End If
    Next r
    If corpos.Count = 0 Then Exit Sub

    Dim olApp As Object, olMail As Object, k As Variant, res As String, lin As Variant, n As Long
    On Error Resume Next
    Set olApp = CreateObject("Outlook.Application")
    On Error GoTo 0
    If olApp Is Nothing Then MsgBox "Outlook nao encontrado. Alertas nao enviados.", vbExclamation: Exit Sub

    For Each k In corpos.Keys
        res = "ENVIADO"
        On Error Resume Next
        Set olMail = olApp.CreateItem(0)
        olMail.To = CStr(k)
        If emailCentral <> "" Then olMail.CC = emailCentral
        olMail.Subject = "ALERTA QSMS - Vencimento de requisitos legais (" & Format(Date, "dd/mm/yyyy") & ")"
        olMail.Body = "Prezado(a)," & vbCrLf & vbCrLf & _
            "Os requisitos abaixo estao vencidos ou proximos do vencimento e exigem providencia:" & vbCrLf & vbCrLf & _
            corpos(k) & vbCrLf & vbCrLf & _
            "Apos a renovacao, atualize a data no controle (aba REQUISITOS) e registre a evidencia (aba HISTORICO)." & vbCrLf & vbCrLf & "QSMS"
        If MODO_TESTE Then olMail.Display Else olMail.Send
        If Err.Number <> 0 Then res = "ERRO: " & Err.Description: Err.Clear
        On Error GoTo 0
        If MODO_TESTE And res = "ENVIADO" Then res = "TESTE (nao enviado)"

        For Each lin In Split(itens(k), ",")
            n = wsL.Cells(wsL.Rows.Count, 1).End(xlUp).Row + 1
            If n < 6 Then n = 6
            wsL.Cells(n, 1).Value = Now: wsL.Cells(n, 1).NumberFormat = "dd/mm/yyyy hh:mm"
            wsL.Cells(n, 2).Value = wsR.Cells(CLng(lin), 1).Value
            wsL.Cells(n, 3).Value = wsR.Cells(CLng(lin), 3).Value
            wsL.Cells(n, 4).Value = wsR.Cells(CLng(lin), 15).Value
            wsL.Cells(n, 5).Value = wsR.Cells(CLng(lin), 13).Value: wsL.Cells(n, 5).NumberFormat = "dd/mm/yyyy"
            wsL.Cells(n, 6).Value = wsR.Cells(CLng(lin), 14).Value
            wsL.Cells(n, 7).Value = k
            wsL.Cells(n, 8).Value = res
        Next lin
    Next k
End Sub


'==============================================================================
' CONTROLE DE REVISOES
'==============================================================================
Public Sub MarcarAlteracao(ByVal nomeAba As String)
    Select Case UCase$(nomeAba)
        Case "LISTA_MESTRA": gAltLM = True
        Case "REQUISITOS", "HISTORICO", "PARAMETROS": gAltCt = True
    End Select
End Sub

Public Sub PerguntarRevisaoAoSalvar()
    If gAltLM Then
        If RegistrarRevisaoDoc(DOC_LM, True) Then gAltLM = False
    End If
    If gAltCt Then
        If RegistrarRevisaoDoc(DOC_CT, True) Then gAltCt = False
    End If
End Sub

Public Sub RegistrarRevisao()
    Dim resp As VbMsgBoxResult
    resp = MsgBox("Registrar revisão de qual documento?" & vbCrLf & vbCrLf & _
                  "Sim = Lista Mestra" & vbCrLf & "Não = Controle de Vencimentos" & vbCrLf & _
                  "Cancelar = sair", vbYesNoCancel + vbQuestion, "Registrar revisão")
    If resp = vbCancel Then Exit Sub
    If RegistrarRevisaoDoc(IIf(resp = vbYes, DOC_LM, DOC_CT), False) Then
        MsgBox "Revisão registrada na aba REVISOES.", vbInformation, "Registrar revisão"
    End If
End Sub

' Retorna True quando o assunto foi tratado (revisao gravada ou usuario recusou).
Private Function RegistrarRevisaoDoc(ByVal doc As String, ByVal perguntar As Boolean) As Boolean
    Dim ws As Worksheet, desc As String, r As Long
    Set ws = ThisWorkbook.Worksheets("REVISOES")
    If perguntar Then
        If MsgBox("O documento '" & doc & "' foi alterado." & vbCrLf & _
                  "Deseja registrar uma nova revisão agora?", vbYesNo + vbQuestion, "Controle de revisões") = vbNo Then
            RegistrarRevisaoDoc = True
            Exit Function
        End If
    End If
    desc = InputBox("Descreva a alteração feita em '" & doc & "':", "Nova revisão")
    If Trim$(desc) = "" Then Exit Function
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    If r < 12 Then r = 12
    If r > 211 Then
        MsgBox "O registro de revisões está cheio (linha 211).", vbExclamation
        Exit Function
    End If
    ws.Cells(r, 1).Value = doc
    ws.Cells(r, 3).Value = Date
    ws.Cells(r, 3).NumberFormat = "dd/mm/yyyy"
    ws.Cells(r, 4).Value = Application.UserName
    ws.Cells(r, 5).Value = desc
    RegistrarRevisaoDoc = True
End Function

'==============================================================================
' BOTOES NO PAINEL (executar uma vez: Alt+F8 > CriarBotoes)
'==============================================================================
Public Sub CriarBotoes()
    Dim ws As Worksheet, b As Object
    Set ws = ThisWorkbook.Worksheets("PAINEL")
    On Error Resume Next
    ws.Buttons.Delete
    On Error GoTo 0
    Set b = ws.Buttons.Add(ws.Range("B4").Left + 2, ws.Range("B4").Top + 2, ws.Range("B4:F4").Width - 4, ws.Range("B4").Height - 4)
    b.Caption = "ENVIAR ALERTAS (E-MAIL AUTOMÁTICO)"
    b.OnAction = "EnviarAlertasVencimento"
    b.Font.Bold = True
    Set b = ws.Buttons.Add(ws.Range("G4").Left + 2, ws.Range("G4").Top + 2, ws.Range("G4:J4").Width - 4, ws.Range("G4").Height - 4)
    b.Caption = "REGISTRAR REVISÃO"
    b.OnAction = "RegistrarRevisao"
    b.Font.Bold = True
    MsgBox "Botões criados no PAINEL (linha 4).", vbInformation
End Sub
