Hoje trago para vocês um disparo de e-Mail em AdvPL com dados de funcionários e ponto eletrônico.
O grande Fabrício Amaro (LinkedIn), há um tempo vem adaptando um boletim de RH, tanto com dados gerais de funcionários, quanto um espelho de ponto (tanto analítico quanto sintético), o famoso MABOLRH.
Conversando com ele recentemente, tive a necessidade de implantar esse “workflow” para disparo de informações para o RH, e assim sendo, peguei o fonte original MABOLRH e coloquei alguns comentários no fonte e fiz adaptações nele (retirando campos customizados, trechos comentados, etc).
Abaixo um print do boletim geral:
Boletim Resumido
Abaixo um print do boletim do ponto resumido:
Boletim do Ponto Eletrônico
Abaixo o código fonte inteiro, ressalto para verificarem a variável cEmails (que são os destinatários da mensagem). Além disso, o ideal é ter compilado uma função de disparo de e-Mail, nesse caso é usado a zEnvMail, disponível em https://terminaldeinformacao.com/2017/10/17/funcao-dispara-e-mail-varios-anexos-em-advpl/ .
//Bibliotecas
#Include "RwMake.ch"
#Include "TopConn.ch"
#Include "Protheus.ch"
Static dParam01 := FirstDate(Date()) //Periodo de -
Static dParam02 := LastDate(Date()) //Periodo ate -
Static cParam03 := "" //Copiar e-mail: -
Static nParam04 := 1 //Tipo? - 1=Apontamentos, 2=Banco de Horas Banco de Horas
Static nParam05 := 2 //Mostrar Salario? - 1=Nao, 2=Sim
Static nParam06 := 1 //Somente Horas Normais? - 1=Sim, 2=Nao
Static nParam07 := 1 //Banco de Horas? - 1=Sintetico, 2=Analitico
Static nParam08 := 1 //Intervalo Menor Que -
Static nParam09 := 1.42 //Intervalo Maior Que -
Static nParam10 := 1 //Acumulado Horas Extras? - 1=Nao, 2=Sim
Static nParam11 := 1 //Mostrar Justificativas? - 1=Nao, 2=Sim
Static nParam12 := 1 //Mostrar evento abonado? - 1=Nao, 2=Sim
Static nParam13 := 1 //Mostrar evento informado? - 1=Nao, 2=Sim
Static nParam14 := 1 //Considera Horas Extras? - 1=Nao, 2=Sim
/*/{Protheus.doc} zBolRH
Workflow com Boletim informativo do RH baseado no MABOLRH
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
User Function zBolRH()
Local aArea := GetArea()
Local lContinua := .T.
Local dDataBkp := sToD("")
Private lAutom := .F.
Private cEmails := ""
//Se nao estiver preparado o ambiente, prepara no Ponto Eletronico
If Select("SX2") == 0
lAutom := .T.
RPCSetEnv("01", "01", "", "", "SIGAPON")
Else
lContinua := MsgYesNo("Essa funcao ira disparar os boletins dos funcionarios. Deseja continuar?", "Atencao")
EndIf
//Se o dia For menor que 10, o email sera referente ao mes passado
If Day(Date()) <= 10
dParam01 := FirstDate( MonthSub(dParam01, 1) )
dParam02 := LastDate(dParam01)
EndIf
//Altera a dDataBase para a ultima data
dDataBkp := dDataBase
dDataBase := dParam02
//Pegando os emails
cEmails := "email@empresa.com;email2@empresa.com"
//Chama o boletim resumido
Processa({|| fBoletResu() }, "Processando...")
//Marcacoes do Ponto
nParam04 := 1
Processa({|| fBoletPont() }, "Processando...")
//Banco de Horas
nParam04 := 2
Processa({|| fBoletPont() }, "Processando...")
//Voltando backup da data
dDataBase := dDataBkp
RestArea(aArea)
Return
/*/{Protheus.doc} fHCorpoIni
Inicializa o HTML
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fHCorpoIni(cHtm)
cHtm := '<html><body>'
Return
/*/{Protheus.doc} fHCorpoFin
Finaliza o HTML
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fHCorpoFin(cHtm)
cHtm += '</body></html>'
Return
/*/{Protheus.doc} fHPulaLin
Pula de linha no HTML
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fHPulaLin(cHtm, nQuant)
cHtm += Repl('<br>', nQuant)
Return
/*/{Protheus.doc} fHTabIni
Inicia a estrutura da tabela no HTML
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fHTabIni(cHtm, nEspCel, nMarCel, nBorda)
cHtm += '<table Width=100% cellspacing="' + Alltrim(Str(nEspCel)) + '" cellpadding="' + Alltrim(Str(nMarCel)) + '" border="' + Alltrim(Str(nBorda)) + '">'
Return
/*/{Protheus.doc} fHTabFin
Fecha a estrutura da tabela no HTML
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fHTabFin(cHtm)
cHtm += '</table>'
Return
/*/{Protheus.doc} fHTabLin
Adiciona uma linha na estrutura da tabela no HTML
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fHTabLin(cHtm, aCol, cCorFundo, cCorFonte, nTamFonte)
Local nCol
Local lFonte
//Verifica se foi passado definicao de fonte usada
lFonte := ! (nTamFonte == Nil .And. cCorFonte == Nil)
//Cria a linha
cHtm += '<TR' + Iif(cCorFundo == Nil, '', ' BGCOLOR="#' + cCorFundo + '"') + '>'
//Percorrendo as colunas
For nCol := 1 To Len(aCol)
//Cria a celula
cHtm += '<TD valign=top>'
//Caso nao tenha sido passado as informacoes de tamanho e cor, define a fonte da celula
If lFonte
cHtm += '<FONT face = tahoma '
If !(nTamFonte == Nil)
cHtm += ' SIZE="' + Alltrim(Str(nTamFonte)) + '"'
EndIf
If !(cCorFonte == Nil)
cHtm += ' COLOR="#' + cCorFonte + '"'
EndIf
cHtm += '>'
EndIf
//Encerra a celula
cHtm += aCol[nCol] + '</TD>'
Next
cHtm += '</TR>'
Return
/*/{Protheus.doc} fHTitLin
Adiciona uma linha de titulo na estrutura da tabela no HTML
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fHTitLin(cHtm, aCol, cCorFundo, cCorFonte, nTamFonte)
Local nCol
Local lFonte
//Verifica se foi passado definicao de fonte usada
lFonte := !(nTamFonte == Nil .And. cCorFonte == Nil)
//Cria a linha
cHtm += '<TR' + Iif(cCorFundo == Nil, '', ' BGCOLOR="#' + cCorFundo + '"') + '>'
//Percorrendo as colunas
For nCol := 1 To Len(aCol)
//Cria a celula, se for a primeira coluna tera conteudo alinhado a direita
If nCol == 1
cHtm += '<TD valign=top>'
Else
cHtm += '<TD valign=top align="right">'
EndIf
//Caso nao tenha sido passado as informacoes de tamanho e cor, define a fonte da celula
If lFonte
cHtm += '<FONT face = tahoma '
If !(nTamFonte == Nil)
cHtm += ' SIZE="' + Alltrim(Str(nTamFonte)) + '"'
EndIf
If !(cCorFonte == Nil)
cHtm += ' COLOR="#' + cCorFonte + '"'
EndIf
cHtm += '>'
EndIf
//Encerra a celula
cHtm += aCol[nCol] + '</TD>'
Next
cHtm += '</TR>'
Return
/*/{Protheus.doc} fDiaSemana
Funcao que retorna a sigla do dia da semana
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fDiaSemana(dData)
Local cSiglaDia := ""
If Dow(dData) == 1
cSiglaDia := "DOM"
ElseIf Dow(dData) == 2
cSiglaDia := "SEG"
ElseIf Dow(dData) == 3
cSiglaDia := "TER"
ElseIf Dow(dData) == 4
cSiglaDia := "QUA"
ElseIf Dow(dData) == 5
cSiglaDia := "QUI"
ElseIf Dow(dData) == 6
cSiglaDia := "SEX"
ElseIf Dow(dData) == 7
cSiglaDia := "SAB"
EndIf
Return cSiglaDia
/*/{Protheus.doc} fEnviarEm
Funcao para disparo de e-Mail
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fEnviarEm(cEmails, cAssunto, cCorpo)
//Se tiver e-mails para colocar em copia
If ! Empty(cParam03)
cEmails := Alltrim(cParam03) + ";" + cEmails
EndIf
//Dispara o e-Mail
u_zEnvMail(cEmails, cAssunto, cCorpo)
Return
/*/{Protheus.doc} fBoletResu
Boletim Resumido com dados dos funcionarios
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fBoletResu()
Local nAtual
Local nRegs
Local cQuery
Local nDias
Local nTotal := 0
Local nNormal := 0
Local nFerias := 0
Local nAfast := 0
Private cHtm := ""
//Inicializa a mensagem
fHCorpoIni(@cHtm)
//Cria o cabecalho
fHTabIni(@cHtm, 0, 2, 0)
fHTitLin(@cHtm, {'Informacoes referentes ao mes: ' + SubStr(dToS(dDataBase), 5, 2) + "/" + SubStr(dToS(dDataBase), 1, 4), 'TOTVS'}, '788EA7', 'FFFFFF', 4)
fHTitLin(@cHtm, {'Boletim Informativo R.H.', 'by TOTVS Microsiga Protheus'}, 'CCCCCC', '000000', -5)
fHTabFin(@cHtm)
fHPulaLin(@cHtm, 2)
//Seleciona os funcionarios admitidos no mes
cQuery := " SELECT " + CRLF
cQuery += " RA_FILIAL, RA_MAT, RA_NOME, RA_ADMISSA, RJ_DESC, CTT_DESC01 " + CRLF
cQuery += " FROM " + CRLF
cQuery += " " + RetSQLName("SRA") + " SRA, " + RetSQLName("CTT") + " CTT, " + RetSQLName("SRJ") + " SRJ " + CRLF
cQuery += " WHERE " + CRLF
cQuery += " RA_ADMISSA >= '" + SubStr(dToS(dDataBase), 1, 6) + "01' AND RA_ADMISSA <= '" + SubStr(dToS(dDataBase), 1, 6) + "31' " + CRLF
cQuery += " AND RA_SITFOLH <> 'D' " + CRLF
cQuery += " AND RA_CATFUNC <> 'A' " + CRLF
cQuery += " AND RA_CODFUNC = RJ_FUNCAO " + CRLF
cQuery += " AND RA_CC = CTT_CUSTO " + CRLF
cQuery += " AND SRA.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND SRJ.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND CTT.D_E_L_E_T_ = ' ' " + CRLF
TCQuery cQuery New Alias "QRY"
TCSetField("QRY", "RA_ADMISSA", "D")
//Define o tamanho da regua
DbSelectArea("QRY")
Count To nRegs
QRY->(DbGoTop())
nAtual := 0
ProcRegua(nRegs)
//Se existir registros
If nRegs > 0
//Cabecalho da secao se funcionarios admitidos no mes
fHTabIni(@cHtm, 0, 2, 0)
fHTabLin(@cHtm, {'Funcionarios Admitidos no mes: ' + Alltrim(Str(nRegs))}, '000000', 'FFFFFF', -1)
fHTabFin(@cHtm)
//Titulo das colunas
lImpar := .T.
fHTabIni(@cHtm, 1, 2, 0)
fHTabLin(@cHtm, {'Filial', 'Empresa', 'Mat', 'Nome', 'Admissao', 'Funcao', 'C.Custo'}, '000000', 'FFFFFF', -1)
//Enquanto houver registros
While ! QRY->(EoF())
//Incrementa a regua
nAtual++
IncProc("Processando Admissoes " + cValToChar(nAtual) + " de " + cValToChar(nRegs) + "...")
//Adiciona a linha no html
fHTabLin(@cHtm, {QRY->RA_FILIAL, FWFilialName(cEmpAnt, QRY->RA_FILIAL, 1), QRY->RA_MAT, QRY->RA_NOME, dToC(QRY->RA_ADMISSA), QRY->RJ_DESC, QRY->CTT_DESC01 }, Iif(lImpar, 'DDDDDD', 'CCCCCC'), Nil, -2)
lImpar := ! lImpar
DbSkip()
EndDo
fHTabFin(@cHtm)
fHPulaLin(@cHtm, 1)
EndIf
QRY->(DbCloseArea())
//Seleciona os funcionarios demitidos no mes
cQuery := "SELECT " + CRLF
cQuery += " RA_FILIAL, RA_MAT, RA_ADMISSA, RA_DEMISSA, RJ_DESC, CTT_DESC01, RA_NOME " + CRLF
cQuery += "FROM " + CRLF
cQuery += " " + RetSQLName("SRA") + " SRA, " + RetSQLName("CTT") + " CTT, " + RetSQLName("SRJ") + " SRJ " + CRLF
cQuery += "WHERE " + CRLF
cQuery += " RA_DEMISSA >= '" + SubStr(dToS(dDataBase), 1, 6) + "01' AND RA_DEMISSA <= '" + SubStr(dToS(dDataBase), 1, 6) + "31' " + CRLF
cQuery += " AND RA_CATFUNC <> 'A' " + CRLF
cQuery += " AND RA_CODFUNC = RJ_FUNCAO " + CRLF
cQuery += " AND RA_CC = CTT_CUSTO " + CRLF
cQuery += " AND SRA.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND SRJ.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND CTT.D_E_L_E_T_ = ' ' " + CRLF
cQuery += "ORDER BY " + CRLF
cQuery += " RA_FILIAL, RA_DEMISSA " + CRLF
TCQuery cQuery New Alias "QRY"
TCSetField("QRY", "RA_ADMISSA", "D")
TCSetField("QRY", "RA_DEMISSA", "D")
//Define o tamanho da regua
DbSelectArea("QRY")
Count To nRegs
QRY->(DbGoTop())
nAtual := 0
ProcRegua(nRegs)
//Se existir registros
If nRegs > 0
//Cabecalho da secao se funcionarios demitidos no mes
fHTabIni(@cHtm, 0, 2, 0)
fHTabLin(@cHtm, {'Funcionarios Demitidos no mes: ' + Alltrim(Str(nRegs))}, '000000', 'FFFFFF', -1)
fHTabFin(@cHtm)
//Titulo das colunas
lImpar := .T.
fHTabIni(@cHtm, 1, 2, 0)
fHTabLin(@cHtm, {'Filial', 'Empresa', 'Mat', 'Nome', 'Admissao', 'Demissao', 'Funcao', 'C.Custo'}, '000000', 'FFFFFF', -1)
//Enquanto houver registros
While ! QRY->(EoF())
//Incrementa a regua
nAtual++
IncProc("Processando Admissoes " + cValToChar(nAtual) + " de " + cValToChar(nRegs) + "...")
//Adiciona a linha no html
fHTabLin(@cHtm, {QRY->RA_FILIAL, FWFilialName(cEmpAnt, QRY->RA_FILIAL, 1), QRY->RA_MAT, QRY->RA_NOME, dToC(QRY->RA_ADMISSA), dToC(QRY->RA_DEMISSA), QRY->RJ_DESC, QRY->CTT_DESC01 }, Iif(lImpar, 'DDDDDD', 'CCCCCC'), Nil, -2)
lImpar := ! lImpar
QRY->(DbSkip())
EndDo
fHTabFin(@cHtm)
fHPulaLin(@cHtm, 1)
EndIf
QRY->(DbCloseArea())
//Seleciona vencimentos de experiencia
cQuery := " SELECT " + CRLF
cQuery += " RA_FILIAL, RA_VCTEXP2, RA_VCTOEXP, RA_MAT, RA_NOME, RA_ADMISSA, RA_CC, CTT_DESC01 " + CRLF
cQuery += " FROM " + CRLF
cQuery += " " + RetSQLName("SRA") + " SRA, " + RetSQLName("CTT") + " CTT " + CRLF
cQuery += " WHERE " + CRLF
cQuery += " SRA.RA_VCTOEXP <> ' '" + CRLF
cQuery += " AND ( SRA.RA_VCTOEXP >= '" + dToS(DaySub(dDataBase, 7)) + "' OR SRA.RA_VCTEXP2 >= '" + dToS(DaySub(dDataBase, 7)) + "')" + CRLF
cQuery += " AND SRA.RA_CC = CTT.CTT_CUSTO " + CRLF
cQuery += " ORDER BY SRA.RA_FILIAL, SRA.RA_VCTOEXP, SRA.RA_NOME ASC " + CRLF
TCQuery cQuery New Alias "QRY"
TCSetField("QRY", "RA_ADMISSA", "D")
TCSetField("QRY", "RA_VCTOEXP", "D")
TCSetField("QRY", "RA_VCTEXP2", "D")
//Define o tamanho da regua
DbSelectArea("QRY")
Count To nRegs
QRY->(DbGoTop())
nAtual := 0
ProcRegua(nRegs)
//Se existir registros
If nRegs > 0
//Cabecalho da secao de vencimentos de experiencia
fHTabIni(@cHtm, 0, 2, 0)
fHTabLin(@cHtm, {'Vencimento de Contratos de Experiencia ' + Alltrim(Str(nRegs))}, '000000', 'FFFFFF', -1)
fHTabFin(@cHtm)
//Titulo das colunas
lImpar := .T.
fHTabIni(@cHtm, 1, 2, 0)
fHTabLin(@cHtm, {'Filial', 'Matricula', 'Nome', 'Admissao', 'Vencto 1', 'Vencto 2', 'Centro de Custo'}, '000000', 'FFFFFF', -1)
//Enquanto houver registros
While ! QRY->(EoF())
//Incrementa a regua
nAtual++
IncProc("Processando Experiencias " + cValToChar(nAtual) + " de " + cValToChar(nRegs) + "...")
//Pegando a diferenca de dias
If QRY->RA_VCTEXP2 >= dDataBase
nDias := DateDiffDay(QRY->RA_VCTEXP2, dDataBase)
ElseIf QRY->RA_VCTOEXP >= dDataBase
nDias := DateDiffDay(QRY->RA_VCTOEXP, dDataBase)
EndIf
//Adiciona a linha no html
fHTabLin(@cHtm, {QRY->RA_FILIAL + " - " + FWFilialName(cEmpAnt, QRY->RA_FILIAL, 1), QRY->RA_MAT, QRY->RA_NOME, dToC(QRY->RA_ADMISSA), dToC(QRY->RA_VCTOEXP), dToC(QRY->RA_VCTEXP2), QRY->RA_CC + " - " + QRY->CTT_DESC01}, Iif(lImpar, 'DDDDDD', 'CCCCCC'), Nil, -2)
lImpar := !lImpar
QRY->(DbSkip())
EndDo
fHTabFin(@cHtm)
fHPulaLin(@cHtm, 1)
EndIf
QRY->(DbCloseArea())
//Seleciona quadro de funcionarios geral
cQuery := "SELECT " + CRLF
cQuery += " RA_FILIAL, " + CRLF
cQuery += " COUNT(*) AS TOTAL, " + CRLF
cQuery += " ( " + CRLF
cQuery += " SELECT " + CRLF
cQuery += " COUNT(*) " + CRLF
cQuery += " FROM " + CRLF
cQuery += " " + RetSQLName("SRA") + " F " + CRLF
cQuery += " WHERE " + CRLF
cQuery += " F.RA_SITFOLH <> 'D' " + CRLF
cQuery += " AND F.RA_SITFOLH = 'F' " + CRLF
cQuery += " AND F.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND F.RA_FILIAL = SRA.RA_FILIAL " + CRLF
cQuery += " ) AS FERIAS, " + CRLF
cQuery += " ( " + CRLF
cQuery += " SELECT " + CRLF
cQuery += " COUNT(*) " + CRLF
cQuery += " FROM " + CRLF
cQuery += " " + RetSQLName("SRA") + " A " + CRLF
cQuery += " WHERE " + CRLF
cQuery += " A.RA_SITFOLH <> 'D' " + CRLF
cQuery += " AND A.RA_SITFOLH = 'A' " + CRLF
cQuery += " AND A.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND A.RA_FILIAL = SRA.RA_FILIAL " + CRLF
cQuery += " ) AS AFAST " + CRLF
cQuery += "FROM " + CRLF
cQuery += " " + RetSQLName("SRA") + " SRA " + CRLF
cQuery += "WHERE " + CRLF
cQuery += " RA_SITFOLH <> 'D' " + CRLF
cQuery += " AND D_E_L_E_T_ = ' ' " + CRLF
cQuery += "GROUP BY " + CRLF
cQuery += " RA_FILIAL " + CRLF
cQuery += "ORDER BY " + CRLF
cQuery += " RA_FILIAL " + CRLF
TCQuery cQuery New Alias "QRY"
//Define o tamanho da regua
DbSelectArea("QRY")
Count To nRegs
QRY->(DbGoTop())
nAtual := 0
ProcRegua(nRegs)
//Se existir registros
If nRegs > 0
//Cabecalho da secao de funcionarios
fHTabIni(@cHtm, 0, 2, 0)
fHTabLin(@cHtm, {'Quadro de Funcionarios'}, '000000', 'FFFFFF', -1)
fHTabFin(@cHtm)
//Titulo das colunas
lImpar := .T.
fHTabIni(@cHtm, 1, 2, 0)
fHTabLin(@cHtm, {'Filial', 'Empresa', 'Total', 'Normal', 'Ferias', 'Afastados'}, '000000', 'FFFFFF', -1)
//Zera os totais
nTotal := 0
nNormal := 0
nFerias := 0
nAfast := 0
//Enquanto houver registros
While ! QRY->(EoF())
//Incrementa a regua
nAtual++
IncProc("Processando Quadro de Funcionarios " + cValToChar(nAtual) + " de " + cValToChar(nRegs) + "...")
//Adiciona a linha no html
fHTabLin(@cHtm, {QRY->RA_FILIAL, FWFilialName(cEmpAnt, QRY->RA_FILIAL, 1), Alltrim(Str(QRY->TOTAL)), Alltrim(Str(QRY->TOTAL-QRY->FERIAS-QRY->AFAST)), Alltrim(Str(QRY->FERIAS)), Alltrim(Str(QRY->AFAST)) }, Iif(lImpar, 'DDDDDD', 'CCCCCC'), Nil, -2)
lImpar := !lImpar
//Atualiza totais
nTotal += QRY->TOTAL
nNormal += QRY->TOTAL - QRY->FERIAS - QRY->AFAST
nFerias += QRY->FERIAS
nAfast += QRY->AFAST
QRY->(DbSkip())
EndDo
//Adiciona totais e encerra tabela
fHTabLin(@cHtm, {"", "", Alltrim(Str(nTotal)), Alltrim(Str(nNormal)), Alltrim(Str(nFerias)), Alltrim(Str(nAfast)) }, Iif(lImpar, 'DDDDDD', 'CCCCCC'), Nil, -2)
fHTabFin(@cHtm)
fHPulaLin(@cHtm, 1)
EndIf
QRY->(DbCloseArea())
//Quadro de Funcionarios por Centro de Custos
cQuery := "SELECT " + CRLF
cQuery += " RA_FILIAL, RA_CC, CTT_DESC01, COUNT(*) AS TOTAL, " + CRLF
cQuery += " ( " + CRLF
cQuery += " SELECT COUNT(*) " + CRLF
cQuery += " FROM " + RetSQLName("SRA") + " F " + CRLF
cQuery += " WHERE F.RA_SITFOLH <> 'D' " + CRLF
cQuery += " AND F.RA_SITFOLH = 'F' " + CRLF
cQuery += " AND F.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND F.RA_FILIAL = SRA.RA_FILIAL " + CRLF
cQuery += " AND F.RA_CC = SRA.RA_CC " + CRLF
cQuery += " ) AS FERIAS, " + CRLF
cQuery += " ( " + CRLF
cQuery += " SELECT COUNT(*) " + CRLF
cQuery += " FROM " + RetSQLName("SRA") + " A " + CRLF
cQuery += " WHERE A.RA_SITFOLH <> 'D' " + CRLF
cQuery += " AND A.RA_SITFOLH = 'A' " + CRLF
cQuery += " AND A.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND A.RA_FILIAL = SRA.RA_FILIAL " + CRLF
cQuery += " AND A.RA_CC = SRA.RA_CC " + CRLF
cQuery += " ) AS AFAST " + CRLF
cQuery += "FROM " + CRLF
cQuery += " " + RetSQLName("SRA") + " SRA, " + RetSQLName("CTT") + " CTT " + CRLF
cQuery += "WHERE " + CRLF
cQuery += " RA_SITFOLH <> 'D' " + CRLF
cQuery += " AND RA_CC = CTT_CUSTO " + CRLF
cQuery += " AND SRA.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND CTT.D_E_L_E_T_ = ' ' " + CRLF
cQuery += "GROUP BY RA_FILIAL, RA_CC, CTT_DESC01 " + CRLF
cQuery += "ORDER BY RA_FILIAL " + CRLF
TCQuery cQuery New Alias "QRY"
//Define o tamanho da regua
DbSelectArea("QRY")
Count To nRegs
QRY->(DbGoTop())
nAtual := 0
ProcRegua(nRegs)
//Se tiver registros
If nRegs > 0
//Cabecalho da secao de funcionarios por centro de custo
fHTabIni(@cHtm, 0, 2, 0)
fHTabLin(@cHtm, {'Quadro de Funcionarios por Centros de Custos'}, '000000', 'FFFFFF', -1)
fHTabFin(@cHtm)
//Titulo das colunas
lImpar := .T.
fHTabIni(@cHtm, 1, 2, 0)
fHTabLin(@cHtm, {'Filial', 'Empresa', 'Cod C.Custo', 'Centro de Custos', 'Total', 'Normal', 'Ferias', 'Afastados'}, '000000', 'FFFFFF', -1)
//Zera os totais
nTotal := 0
nNormal := 0
nFerias := 0
nAfast := 0
//Enquanto houver registros
While ! QRY->(EoF())
//Incrementa a regua
nAtual++
IncProc("Processando Quadro de Funcionarios " + cValToChar(nAtual) + " de " + cValToChar(nRegs) + "...")
//Adiciona a linha no html
fHTabLin(@cHtm, {QRY->RA_FILIAL, FWFilialName(cEmpAnt, QRY->RA_FILIAL, 1), QRY->RA_CC, QRY->CTT_DESC01 , Alltrim(Str(QRY->TOTAL)), Alltrim(Str(QRY->TOTAL - QRY->FERIAS - QRY->AFAST)), Alltrim(Str(QRY->FERIAS)), Alltrim(Str(QRY->AFAST)) }, Iif(lImpar, 'DDDDDD', 'CCCCCC'), Nil, -2)
lImpar := !lImpar
//Atualiza totais
nTotal += QRY->TOTAL
nNormal += QRY->TOTAL - QRY->FERIAS - QRY->AFAST
nFerias += QRY->FERIAS
nAfast += QRY->AFAST
QRY->(DbSkip())
EndDo
//Adiciona os totais e encerra a tabela
fHTabLin(@cHtm, {"", "", "", "", Alltrim(Str(nTotal)), Alltrim(Str(nNormal)), Alltrim(Str(nFerias)), Alltrim(Str(nAfast)) }, Iif(lImpar, 'DDDDDD', 'CCCCCC'), Nil, -2)
fHTabFin(@cHtm)
fHPulaLin(@cHtm, 1)
EndIf
QRY->(DbCloseArea())
//Adiciona uma mensagem de rodape e encerro o boletim
fHTabIni(@cHtm, 0, 2, 0)
fHTitLin(@cHtm, {'TOTVS - Mensagem automatica, favor nao responder este e-mail.'}, 'CCCCCC', '000000', -5)
fHTabFin(@cHtm)
fHCorpoFin(@cHtm)
//Envia o e-Mail
fEnviarEm(cEmails, "Boletim Informativo do R.H.", cHTM)
Return
/*/{Protheus.doc} fBoletPont
Boletim do Ponto Eletronico separado por centro de custo
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fBoletPont()
Local cQuery := ""
Local cHtmPC := ""
Local cHtmP8 := ""
Local cHtmResult := ""
Local cHtmAC := ""
Local lTemApon := .F.
Local nAtual
Local nTotal
Private cHtm := ""
//Totais
Private nTotHorN := 0
Private nTotValN := 0
Private nTotDiaN := 0
//Totais valorizados
Private nTotHorV := 0
Private nTotValV := 0
Private nTotDiaV := 0
Private lTemCab := .F.
//Buscando os centros de custo
cQuery := " SELECT " + CRLF
cQuery += " CTT_CUSTO , CTT_DESC01 " + CRLF
cQuery += " FROM " + CRLF
cQuery += " " + RetSQLName("CTT") + " CTT " + CRLF
cQuery += " WHERE " + CRLF
cQuery += " CTT.D_E_L_E_T_ = ' ' " + CRLF
TCQuery cQuery New Alias "QRY_CTT"
//Definindo o tamanho da regua
nTotal := 0
nAtual := 0
Count To nTotal
ProcRegua(nTotal)
QRY_CTT->(DbGoTop())
//Enquanto houver dados
While ! QRY_CTT->(EoF())
//Zera os totais
nTotHorN := 0
nTotValN := 0
nTotDiaN := 0
nTotHorV := 0
nTotValV := 0
nTotDiaV := 0
//Incrementa a regua
nAtual++
IncProc("Centro Custo: " + QRY_CTT->CTT_CUSTO + " - " + Alltrim(QRY_CTT->CTT_DESC01) + " (" + cValToChar(nAtual) + " de " + cValToChar(nTotal) + ")" )
//Monta o corpo do e-mail
fHCorpoIni(@cHtm)
fHTabIni(@cHtm, 0, 2, 0)
fHTabLin(@cHtm, {'Boletim do Ponto Eletronico - ' + Iif(nParam04 == 1, 'Divergências nas Marcacoes', 'Banco de Horas'), 'TOTVS'}, '788EA7', 'FFFFFF', 4)
fHTabLin(@cHtm, {'Boletim Informativo do Ponto Eletronico', 'by TOTVS Microsiga Protheus'}, 'CCCCCC', '000000', -5)
fHTabFin(@cHtm)
fHPulaLin(@cHtm, 2)
//Busca funcionarios do centro de custo atual
cQuery := "SELECT " + CRLF
cQuery += " RA_FILIAL, " + CRLF
cQuery += " RA_MAT, " + CRLF
cQuery += " RA_SALARIO, " + CRLF
cQuery += " RA_HRSMES, " + CRLF
cQuery += " RA_HRSEMAN, " + CRLF
cQuery += " RA_TNOTRAB, " + CRLF
cQuery += " RA_SEQTURN, " + CRLF
cQuery += " RA_CC, " + CRLF
cQuery += " RA_NOME " + CRLF
cQuery += "FROM "
cQuery += " " + RetSQLName("SRA") + " SRA " + CRLF
cQuery += "WHERE " + CRLF
cQuery += " RA_CC = '" + QRY_CTT->CTT_CUSTO + "' " + CRLF
cQuery += " AND SRA.RA_SITFOLH <> 'D' " + CRLF
cQuery += " AND SRA.D_E_L_E_T_ = ' ' " + CRLF
cQuery += "ORDER BY SRA.RA_FILIAL, SRA.RA_MAT " + CRLF
TCQuery cQuery New Alias "QRY_SRA"
//Se nao houver funcionarios para esse centro de custo, volta para o laco da CTT
If QRY_SRA->(EoF())
QRY_SRA->(DbCloseArea())
QRY_CTT->(DbSkip())
Loop
EndIf
//Insere o centro de custo no html
lTemApon := .F.
fHTabIni(@cHtm, 0, 2, 0)
fHTabLin(@cHtm, {'CENTRO DE CUSTO: ' + Alltrim(QRY_CTT->CTT_CUSTO) + ' - ' + Alltrim(QRY_CTT->CTT_DESC01) }, '000000', 'FFFFFF', -1)
fHTabFin(@cHtm)
//Insere titulo para dados do funcionario
lImpar := .T.
fHTabIni(@cHtm, 1, 2, 0)
fHTabLin(@cHtm, { 'Dados do Funcionario', Iif(nParam04 == 1, 'Apontamentos com divergencia', 'Banco de Horas') }, '000000', 'FFFFFF', -1)
//Enquanto houver funcionarios
While ! QRY_SRA->(EoF())
lTemCab := .F.
//Define a data de/ate
dDataIni := dParam01
dDataFim := dParam02
//Zera os htmls
cHtmResult := ""
cHtmP8 := ""
cHtmPC := ""
//Se for banco de horas
If nParam04 == 2
//Busca as informacoes do banco de horas
cHtmP8 := fProcuBH(QRY_SRA->RA_FILIAL, QRY_SRA->RA_MAT, ROUND(QRY_SRA->RA_SALARIO / (QRY_SRA->RA_HRSMES + QRY_SRA->RA_HRSEMAN) , 2) , ROUND((QRY_SRA->RA_HRSMES + QRY_SRA->RA_HRSEMAN) / 30, 2) )
cHtmResult += cHtmP8
Else
//Enquanto a data de for menor ou igual a data ate
While dDataIni <= dDataFim
//Busca as marcacoes e os apontamentos
cHtmP8 := fProcuMarc(QRY_SRA->RA_FILIAL, QRY_SRA->RA_MAT, dDataIni, QRY_SRA->RA_HRSMES, QRY_SRA->RA_TNOTRAB, QRY_SRA->RA_SEQTURN, QRY_SRA->RA_CC)
cHtmResult += cHtmP8
//Incrementa 1 dia
dDataIni := DaySum(dDataIni, 1)
EndDo
EndIf
//Se encontrou informacoes
If cHtmResult <> ""
//Monta os dados do Funcionario
cHtmPC := '<table width=260>'
cHtmPC += '<tr>'
cHtmPC += '<td><FONT face=tahoma SIZE=1 COLOR=red><b>'
cHtmPC += QRY_SRA->RA_MAT + " - " + Alltrim(QRY_SRA->RA_NOME)
cHtmPC += '</td>'
cHtmPC += '</tr>'
//Se for banco de horas
If nParam04 == 2
//Se for mostrar o valor do salario/hora
If nParam05 == 2
cHtmPC += '<tr>'
cHtmPC += '<td BGCOLOR=#F3F3F3 align=right><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += 'Sal. Hora: '
cHtmPC += '</td>'
cHtmPC += '<td><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += cValToChar(Round(QRY_SRA->RA_SALARIO / (QRY_SRA->RA_HRSMES + QRY_SRA->RA_HRSEMAN), 2))
cHtmPC += '</td>'
cHtmPC += '</tr>'
EndIf
cHtmPC += '<tr>'
cHtmPC += '<td BGCOLOR=#F3F3F3 align=right><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += 'Horas Mes: '
cHtmPC += '</td>'
cHtmPC += '<td><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += cValToChar((QRY_SRA->RA_HRSMES + QRY_SRA->RA_HRSEMAN))
cHtmPC += '</td>'
cHtmPC += '</tr>'
EndIf
cHtmPC += '</table>'
//Insere os dados no html
fHTabLin(@cHtm, { cHtmPC , cHtmResult}, Iif(lImpar, 'DDDDDD', 'FFFFFF'), Nil, -2)
lImpar := !lImpar
lTemApon := .T.
EndIf
QRY_SRA->(DbSkip())
EndDo
//Se for banco de horas, ira demonstrar um acumulador
If nParam04 == 2
cHtmAC := '<table width="100%">'
cHtmAC += '<tr BGCOLOR=#F3F3F3>'
cHtmAC += '<td align=center height=24 ROWSPAN=2><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmAC += '<b>TOTAL ATE O DIA</b>'
cHtmAC += '</td>'
cHtmAC += '<td align=center height=24 COLSPAN=3><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += 'HORAS NORMAIS'
cHtmAC += '</td>'
If nParam06 == 2
cHtmAC += '<td align=center WIDTH=5 rowspan=99><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += ' '
cHtmAC += '</td>'
cHtmAC += '<td align=center height=24 COLSPAN=3><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += 'HORAS VALORIZADAS'
cHtmAC += '</td>'
EndIf
cHtmAC += '</tr>'
cHtmAC += '<tr BGCOLOR=#F3F3F3>'
cHtmAC += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += 'Saldo '
cHtmAC += '</td>'
cHtmAC += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += 'R$ Saldo'
cHtmAC += '</td>'
cHtmAC += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += 'Dias Saldo '
cHtmAC += '</td>'
If nParam06 == 2
cHtmAC += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += 'Saldo '
cHtmAC += '</td>'
cHtmAC += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += 'R$ Saldo'
cHtmAC += '</td>'
cHtmAC += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmAC += 'Dias Saldo '
cHtmAC += '</td>'
EndIf
cHtmAC += '</tr>'
cHtmAC += '<tr>'
cHtmAC += '<td><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmAC += '<b><center>' + dToC(dDataBase) + '</center></b>'
cHtmAC += '</td>'
//Horas Normais
cHtmAC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black><b>'
nSaldoN := nTotHorN
cHora := cValToChar(StrZero(Int(nSaldoN), 4))
cMin := cValToChar(StrZero( (Iif(nSaldoN >= 0, nSaldoN - Int(nSaldoN), Int(nSaldoN) - nSaldoN)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmAC += cHora
cHtmAC += '</td>'
cHtmAC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black><b>'
If nParam05 == 2
cHtmAC += TRANSFORM(nTotValN, "@E 999, 999.99")
EndIf
cHtmAC += '</td>'
cHtmAC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black><b>'
cHtmAC += TRANSFORM(nTotDiaN, "@E 999, 999.9")
cHtmAC += '</td>'
//Horas valorizadas
If nParam06 == 2
cHtmAC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black><b>'
nSaldoV := nTotHorV
cHora := cValToChar(StrZero(Int(nSaldoV), 4))
cMin := cValToChar(StrZero( (Iif(nSaldoV >= 0, nSaldoV - Int(nSaldoV), Int(nSaldoV) - nSaldoV)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmAC += cHora
cHtmAC += '</td>'
cHtmAC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black><b>'
If nParam05 == 2
cHtmAC += TRANSFORM(nTotValV, "@E 999, 999.99")
EndIf
cHtmAC += '</td>'
cHtmAC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black><b>'
cHtmAC += TRANSFORM(nTotDiaV, "@E 999, 999.9")
cHtmAC += '</td>'
EndIf
cHtmAC += '</tr>'
cHtmAC += '</table>'
//Adicionando total geral
fHTabLin(@cHtm, { "<br><br><center><b>TOTAL GERAL</b></center> " , cHtmAC}, Iif(lImpar, 'DDDDDD', 'CCCCCC'), Nil, -2)
EndIf
QRY_SRA->(DbCloseArea())
//Encerrando o html
fHTabFin(@cHtm)
fHPulaLin(@cHtm, 1)
fHTabIni(@cHtm, 0, 2, 0)
fHTabLin(@cHtm, {'TOTVS - Mensagem automatica.'}, 'CCCCCC', '000000', -5)
fHTabFin(@cHtm)
fHCorpoFin(@cHtm)
//Se tiver dados, envia o email
If lTemApon
fEnviarEm(Alltrim(cEmails), "Boletim do Ponto Eletronico - " + Iif(nParam04 == 1, "Marcacoes do Ponto", "Banco de Horas") + " (" + Alltrim(QRY_CTT->CTT_DESC01) + ")", cHTM)
EndIf
QRY_CTT->(DbSkip())
EndDo
QRY_CTT->(DbCloseArea())
Return
/*/{Protheus.doc} fProcuBH
Funcao para procurar banco de horas
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fProcuBH(cFil, cMat, nSalHor, nHorDia)
Local lAchou := .F.
Local cHtmDT := ""
Local nSaldoN := 0
Local nSaldoV := 0
//Criando os dados do banco de horas
cHtmDT := '<table width="100%">'
cHtmDT += '<tr BGCOLOR=#F3F3F3>'
cHtmDT += '<td align=center height=24 ROWSPAN=2><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'DIA'
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24 ROWSPAN=2><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'EVENTO'
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24 COLSPAN=4><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'HORAS NORMAIS'
cHtmDT += '</td>'
If nParam06 == 2
cHtmDT += '<td align=center WIDTH=5 rowspan=99><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += ' '
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24 COLSPAN=4><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'HORAS VALORIZADAS'
cHtmDT += '</td>'
EndIf
cHtmDT += '</tr>'
cHtmDT += '<tr BGCOLOR=#F3F3F3>'
cHtmDT += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'Qtde. '
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'Saldo '
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'R$ Saldo'
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'Dias Saldo '
cHtmDT += '</td>'
If nParam06 == 2
cHtmDT += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'Qtde. '
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'Saldo '
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'R$ Saldo'
cHtmDT += '</td>'
cHtmDT += '<td align=center height=24><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'Dias Saldo '
cHtmDT += '</td>'
EndIf
cHtmDT += '</tr>'
//Busca por dados do banco de horas
cQuery := " SELECT " + CRLF
cQuery += " P9_TIPOCOD, " + CRLF
cQuery += " PI_QUANT, " + CRLF
cQuery += " PI_QUANTV, " + CRLF
cQuery += " P9_CODIGO, " + CRLF
cQuery += " P9_DESC " + CRLF
cQuery += " FROM " + CRLF
cQuery += " " + RetSQLName("SPI") + " SPI " + CRLF
cQuery += " INNER JOIN " + RetSQLName("SP9") + " SP9 ON SP9.P9_CODIGO = SPI.PI_PD " + CRLF
cQuery += " WHERE " + CRLF
cQuery += " SPI.PI_FILIAL = '" + cFil + "' " + CRLF
cQuery += " AND SPI.PI_MAT = '" + cMat + "' " + CRLF
cQuery += " AND SPI.PI_STATUS <> 'B' " + CRLF
cQuery += " AND SPI.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND SP9.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " ORDER BY SPI.PI_DATA " + CRLF
TCQuery cQuery New Alias "QRY_SPI"
//Zera os totais
nSaldoN := 0
nSaldoV := 0
//Enquanto houver dados
DbSelectArea("QRY_SPI")
While ! QRY_SPI->(EoF())
//Pegando o saldo normal e o valorizado
nSaldoN := Iif(QRY_SPI->P9_TIPOCOD == "1", SomaHoras(QRY_SPI->PI_QUANT , nSaldoN ) , SubHoras( nSaldoN , QRY_SPI->PI_QUANT ) )
nSaldoV := Iif(QRY_SPI->P9_TIPOCOD == "1", SomaHoras(QRY_SPI->PI_QUANTV , nSaldoV ) , SubHoras( nSaldoV , QRY_SPI->PI_QUANTV) )
//Se for analitico
If nParam07 == 2
cHtmDT += '<tr>'
cHtmDT += '<td><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmDT += '<center>' + dToC(STOD(QRY_SPI->PI_DATA)) + '</center>'
cHtmDT += '</td>'
cHtmDT += '<td><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmDT += QRY_SPI->P9_CODIGO
cHtmDT += " - "
cHtmDT += QRY_SPI->P9_DESC
cHtmDT += "<b>"
cHtmDT += Iif(QRY_SPI->P9_TIPOCOD == "1", '<font COLOR=blue> + </font> ', '<font COLOR=red> -</font> ')
cHtmDT += '</b></td>'
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
//Horas Normais
cHora := cValToChar(StrZero(Int(QRY_SPI->PI_QUANT), 4))
cMin := cValToChar(StrZero((QRY_SPI->PI_QUANT - Int(QRY_SPI->PI_QUANT)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmDT += cHora
cHtmDT += '</td>'
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHora := cValToChar(StrZero(Int(nSaldoN), 4))
cMin := cValToChar(StrZero( (Iif(nSaldoN >= 0, nSaldoN - Int(nSaldoN), Int(nSaldoN) - nSaldoN)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmDT += cHora
cHtmDT += '</td>'
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
If nParam05 == 2
cHtmDT += TRANSFORM(nSaldoN * nSalHor, "@E 9, 999.99")
EndIf
cHtmDT += '</td>'
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmDT += TRANSFORM(nSaldoN / nHorDia, "@E 9, 999.9")
cHtmDT += '</td>'
//Horas Valorizadas
If nParam06 == 2
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHora := cValToChar(StrZero(Int(QRY_SPI->PI_QUANTV), 4))
cMin := cValToChar(StrZero((QRY_SPI->PI_QUANTV - Int(QRY_SPI->PI_QUANTV)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmDT += cHora
cHtmDT += '</td>'
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHora := cValToChar(StrZero(Int(nSaldoV), 4))
cMin := cValToChar(StrZero( (Iif(nSaldoV >= 0, nSaldoV - Int(nSaldoV), Int(nSaldoV) - nSaldoV)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmDT += cHora
cHtmDT += '</td>'
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
If nParam05 == 2
cHtmDT += TRANSFORM(nSaldoV * nSalHor, "@E 9, 999.99")
EndIf
cHtmDT += '</td>'
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmDT += TRANSFORM(nSaldoV / nHorDia, "@E 9, 999.9")
cHtmDT += '</td>'
EndIf
cHtmDT += '</tr>'
EndIf
lAchou := .T.
QRY_SPI->(DbSkip())
EndDo
//Montando o total do funcionario
cHtmDT += '<tr>'
cHtmDT += '<td colspan=12><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmDT += '<hr>'
cHtmDT += '</tr>'
cHtmDT += '<tr>'
cHtmDT += '<td colspan=2><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmDT += '<b>'
cHtmDT += Iif(nSaldoN >= 0, '<font COLOR=blue>SALDO ATUAL POSITIVO </font> ', '<font COLOR=red>SALDO ATUAL NEGATIVO </font> ')
cHtmDT += '</b></td>'
cHtmDT += '<td><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmDT += ' '
cHtmDT += '</td>'
cHtmDT += '<td align=center>'
cHtmDT += Iif(nSaldoN >= 0, '<font face=tahoma SIZE=1 COLOR=blue><b>', '<font face=tahoma SIZE=1 COLOR=red><b>')
cHora := cValToChar(StrZero(Int(nSaldoN), 4))
cMin := cValToChar(StrZero( (Iif(nSaldoN >= 0, nSaldoN - Int(nSaldoN), Int(nSaldoN) - nSaldoN)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmDT += cHora
cHtmDT += '</td>'
cHtmDT += '<td align=center>'
If nParam05 == 2
cHtmDT += Iif(nSaldoN >= 0, '<font face=tahoma SIZE=1 COLOR=blue><b>', '<font face=tahoma SIZE=1 COLOR=red><b>')
cHtmDT += TRANSFORM(nSaldoN * nSalHor, "@E 9, 999.99")
EndIf
cHtmDT += '</td>'
cHtmDT += '<td align=center>'
cHtmDT += Iif(nSaldoN >= 0, '<font face=tahoma SIZE=1 COLOR=blue><b>', '<font face=tahoma SIZE=1 COLOR=red><b>')
cHtmDT += TRANSFORM(nSaldoN / nHorDia, "@E 9, 999.9")
cHtmDT += '</td>'
//Horas valorizadas
If nParam06 == 2
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=BLACK><b>'
cHtmDT += ' '
cHtmDT += '</td>'
cHtmDT += '<td align=center>'
cHtmDT += Iif(nSaldoN >= 0, '<font face=tahoma SIZE=1 COLOR=blue><b>', '<font face=tahoma SIZE=1 COLOR=red><b>')
cHora := cValToChar(StrZero(Int(nSaldoV), 4))
cMin := cValToChar(StrZero( (Iif(nSaldoV >= 0, nSaldoV - Int(nSaldoV), Int(nSaldoV) - nSaldoV)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmDT += cHora
cHtmDT += '</td>'
cHtmDT += '<td align=center>'
If nParam05 == 2
cHtmDT += Iif(nSaldoN >= 0, '<font face=tahoma SIZE=1 COLOR=blue><b>', '<font face=tahoma SIZE=1 COLOR=red><b>')
cHtmDT += TRANSFORM(nSaldoV * nSalHor, "@E 9, 999.99")
EndIf
cHtmDT += '</td>'
cHtmDT += '<td align=center>'
cHtmDT += Iif(nSaldoN >= 0, '<font face=tahoma SIZE=1 COLOR=blue><b>', '<font face=tahoma SIZE=1 COLOR=red><b>')
cHtmDT += TRANSFORM(nSaldoV / nHorDia, "@E 9, 999.9")
cHtmDT += '</td>'
EndIf
cHtmDT += '</tr>'
cHtmDT += '</table>'
//Atualizando os totais
nTotHorN := SomaHoras(nTotHorN, nSaldoN)
nTotValN += nSaldoN * nSalHor
nTotDiaN += nSaldoN / nHorDia
//Atualizando os totais (valorizados)
nTotHorV := SomaHoras(nTotHorV, nSaldoV)
nTotValV += nSaldoV * nSalHor
nTotDiaV += nSaldoV / nHorDia
QRY_SPI->(DbCloseArea())
Return (Iif(lAchou, cHtmDT, ""))
/*/{Protheus.doc} fProcuMarc
Funcao para procurar marcacoes
@author Fabricio Amaro (adaptacao e documentacao por Daniel Atilio)
@since 20/02/2020
@version 1.0
/*/
Static Function fProcuMarc(cFil, cMat, cData, cHorMes, cTur, cSeq, cCC, cRec)
Local lAchou := .F.
Local lAchouApon := .F.
Local cHtmP8 := ""
Local nCont := 0
Local cHtmDT := ""
Local n1E := 0
Local n1S := 0
Local n2E := 0
Local n2S := 0
Local n1EP := 0
Local n1SP := 0
Local n2EP := 0
Local n2SP := 0
Local cHora := ""
Local cMin := ""
Local lSemMarc := .F.
Local cApontInt := ""
Local cHoraInt := ""
Local cInt
Local cTpDia
//Inicializa a tabela
cHtmDT := '<table width="80">'
If !lTemCab
cHtmDT += '<tr BGCOLOR=#F3F3F3>'
cHtmDT += '<td align=center><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += 'Data'
cHtmDT += '</td>'
cHtmDT += '</tr>'
EndIf
cHtmDT += '<tr>'
cHtmDT += '<td><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmDT += dToC(cData) + ' - ' + fDiaSemana(cData)
cHtmDT += '</td>'
cHtmDT += '</tr>'
cHtmDT += '</table>'
cHtmP8 := '<table width="200">'
If !lTemCab
cHtmP8 += '<tr BGCOLOR=#F3F3F3>'
cHtmP8 += '<td align=center><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmP8 += 'Horario apontado'
cHtmP8 += '</td>'
cHtmP8 += '</tr>'
EndIf
cHtmP8 += '<tr>'
cHtmP8 += '<td><FONT face=tahoma SIZE=1 COLOR=black>'
//Procura as marcacoes
cQuery := "SELECT P8_HORA " + CRLF
cQuery += "FROM " + RetSQLName("SP8") + " SP8 " + CRLF
cQuery += "WHERE SP8.P8_FILIAL = '" + cFil + "' " + CRLF
cQuery += " AND SP8.P8_MAT = '" + cMat + "' " + CRLF
cQuery += " AND SP8.P8_DATAAPO = '" + dToS(cData) + "' " + CRLF
cQuery += " AND SP8.P8_TPMCREP <> 'D' " + CRLF
cQuery += " AND SP8.D_E_L_E_T_ = ' ' " + CRLF
cQuery += "ORDER BY SP8.P8_DATA, SP8.P8_HORA " + CRLF
TCQuery cQuery New Alias "QRY_SP8"
//Enquanto houver registros
While ! QRY_SP8->(EoF())
cHtmP8 += Iif(nCont == 0, "", " - ")
cHtmP8 += Iif(nCont == 0, "", " - ")
//Montando a exibicao da hora
cHora := cValToChar(StrZero(Int(QRY_SP8->P8_HORA), 2))
cMin := cValToChar(StrZero((QRY_SP8->P8_HORA - Int(QRY_SP8->P8_HORA)) * 100, 2))
cHora := cHora + ":" + cMin
cHtmP8 += cHora
//Armazenando as marcacoes
If nCont == 0
n1E := QRY_SP8->P8_HORA
ElseIf nCont == 1
n1S := QRY_SP8->P8_HORA
ElseIf nCont == 2
n2E := QRY_SP8->P8_HORA
ElseIf nCont == 3
n2S := QRY_SP8->P8_HORA
EndIf
nCont++
lAchou := .T.
QRY_SP8->(DbSkip())
EndDo
QRY_SP8->(DbCloseArea())
//Se nao teve marcacoes
If nCont == 0
cApontInt += "" //"SEM MARCACAO"
lSemMarc := .T.
EndIf
//Se a marcacao ficou impar, mostra no html
If (Mod(nCont, 2)) <> 0
cApontInt += "IMPAR - "
lAchouApon := .T.
EndIf
//Se houve marcacoes
If ! lSemMarc
//Pesquisa o calendario padrao
cQuery := " SELECT " + CRLF
cQuery += " PJ_TPDIA, " + CRLF
cQuery += " PJ_ENTRA1, " + CRLF
cQuery += " PJ_SAIDA1, " + CRLF
cQuery += " PJ_ENTRA2, " + CRLF
cQuery += " PJ_SAIDA1, " + CRLF
cQuery += " PJ_SAIDA2, " + CRLF
cQuery += " PJ_INTERV1, " + CRLF
cQuery += " PJ_HRSINT1 " + CRLF
cQuery += " FROM " + CRLF
cQuery += " " + RetSQLName("SPJ") + " SPJ" + CRLF
cQuery += " WHERE " + CRLF
cQuery += " PJ_TURNO = '" + cTur + "' AND SPJ.D_E_L_E_T_ = ' ' " + CRLF
cQuery += " AND PJ_SEMANA = '" + cSeq + "' AND PJ_DIA = '" + cValToChar(Dow(cData)) + "'" + CRLF
TCQuery cQuery NEW ALIAS "QRY_SPJ"
//Pegando o tipo de dia se e trabalhado
cInt := ""
cTpDia := QRY_SPJ->PJ_TPDIA
If cTpDia == "S"
n1EP := QRY_SPJ->PJ_ENTRA1
n1SP := QRY_SPJ->PJ_SAIDA1
n2EP := QRY_SPJ->PJ_ENTRA2
n2SP := QRY_SPJ->PJ_SAIDA2
cInt := QRY_SPJ->PJ_INTERV1
nHorIntP := QRY_SPJ->PJ_HRSINT1
EndIf
QRY_SPJ->(DbCloseArea())
//Turno com 4 marcacoes
If cInt == "S"
If nCont < 4
cApontInt += "MARCACAO MENOR QUE O PROGRAMADO"
lAchouApon := .T.
ElseIf nCont > 4
cApontInt += "MARCACAO MAIOR QUE O PROGRAMADO"
lAchouApon := .T.
EndIf
//Turno com 2 marcacoes
Else
If nCont < 2
cApontInt += "MARCACAO MENOR QUE O PROGRAMADO"
lAchouApon := .T.
ElseIf nCont > 2
cApontInt += "MARCACAO MAIOR QUE O PROGRAMADO"
lAchouApon := .T.
EndIf
EndIf
//Analisa se a marcacao foi menor ou maior que o programado (somente quando ha intervalo)
If cInt == "S" .And. !lAchouApon
//Se a segunda entrada passar para o outro dia
If n2E < n1S
nHorInt := 23.59 - n1S
nHorInt := SomaHoras(nHorInt, n2E)
nHorInt := SomaHoras(nHorInt, 0.01)
Else
nHorInt := SubHoras(n2E, n1S)
EndIf
cHora := cValToChar(StrZero(Int(nHorInt), 2))
cMin := cValToChar(StrZero((nHorInt - Int(nHorInt)) * 100, 2))
cHoraInt := cHora + ":" + cMin
EndIf
//Se nao for dia trabalhado
If ! (cTpDia == "S")
cApontInt := ""
EndIf
EndIf
cHtmP8 += '</td>'
cHtmP8 += '</tr>'
cHtmP8 += '</TABLE>'
//Buscando apontamentos no ponto que geraram divergencia do padrao e ainda nao foram abonadas nem informado outro evento
cHtmPC := '<table>'
If !lTemCab
cHtmPC += '<tr BGCOLOR=#F3F3F3>'
cHtmPC += '<td align=center width=200><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += 'Descricao do Evento'
cHtmPC += '</td>'
cHtmPC += '<td align=center width=50><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += 'Qtde.'
cHtmPC += '</td>'
If nParam10 == 2
cHtmPC += '<td align=center width=80><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += 'Acum. H.E.'
cHtmPC += '</td>'
EndIf
//Colunas de Justificativa
If nParam11 == 2
cHtmPC += '<td align=center width=80><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += ' Abonar '
cHtmPC += '</td>'
cHtmPC += '<td align=center width=80><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += ' Descontar <br>Folha'
cHtmPC += '</td>'
cHtmPC += '<td align=center width=100><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += 'Descontar<br>Banco Hora'
cHtmPC += '</td>'
cHtmPC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += 'Motivo (descrever) '
cHtmPC += '</td>'
EndIf
//Eventos ja abonados
If nParam12 == 2
cHtmPC += '<td align=center width=200><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += ' EVENTO JA ABONADO '
cHtmPC += '</td>'
EndIf
//Eventos informados
If nParam13 == 2
cHtmPC += '<td align=center width=200><FONT face=tahoma SIZE=1 COLOR=BLACK>'
cHtmPC += ' OUTRO EVENTO JA INFORMADO '
cHtmPC += '</td>'
EndIf
cHtmPC += '</tr>'
EndIf
//Buscando eventos
cQuery := " SELECT " + CRLF
cQuery += " PC_QUANTC, " + CRLF
cQuery += " PC_PD, " + CRLF
cQuery += " P9_DESC " + CRLF
cQuery += " FROM " + CRLF
cQuery += " " + RetSQLName("SPC") + " SPC " + CRLF
cQuery += " INNER JOIN " + RetSQLName("SP9") + " SP9 ON P9_CODIGO = PC_PD AND SP9.D_E_L_E_T_ = ' ' AND P9_DIVERGE = 'S' " + CRLF
cQuery += " WHERE " + CRLF
cQuery += " SPC.PC_FILIAL = '" + cFil + "' " + CRLF
cQuery += " AND SPC.PC_MAT = '" + cMat + "' " + CRLF
//Buscando eventos nao abonados
If nParam12 == 1
cQuery += "AND SPC.PC_ABONO = ' ' " + CRLF
EndIf
//Buscando eventos nao informados
If nParam13 == 1
cQuery += "AND SPC.PC_PDI = ' ' " + CRLF
EndIf
//Desconsiderar hora extra
If nParam14 == 1
cQuery += "AND SP9.P9_CLASEV <> '01'" + CRLF
EndIf
cQuery += "AND SPC.PC_DATA = '" + dToS(cData) + "' " + CRLF
cQuery += "AND SPC.D_E_L_E_T_ = ' ' " + CRLF
TCQuery cQuery New Alias "QRY_SPC"
//Se houver dados
If ! QRY_SPC->(EoF())
//Enquanto houver dados
While ! QRY_SPC->(EoF())
//Montando a exibicao da hora
cHora := cValToChar(StrZero(Int(QRY_SPC->PC_QUANTC), 2))
cMin := cValToChar(StrZero((QRY_SPC->PC_QUANTC - Int(QRY_SPC->PC_QUANTC)) * 100, 2))
cHora := cHora + ":" + cMin
nHoraExtra := 0
cHtmPC += '<tr>'
cHtmPC += '<td width=200><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += QRY_SPC->PC_PD
cHtmPC += " - "
cHtmPC += QRY_SPC->P9_DESC
If !(Empty(cApontInt))
cHtmPC += "<br><FONT color=red>" + cApontInt + "</font><br>"
EndIf
cHtmPC += '</td>'
cHtmPC += '<td align=center width=50 valign=top><FONT face=tahoma SIZE=1 COLOR=black>'
If !(Empty(cApontInt))
cHtmPC += "<br><FONT color=red>" + cHoraInt + "</font><br>"
EndIf
cHtmPC += '</td>'
//Acumulado de horas extras
If nParam10 == 2
nHoraExtra := 0
cQryHE := " SELECT " + CRLF
cQryHE += " PC_QUANTC " + CRLF
cQryHE += " FROM " + CRLF
cQryHE += " " + RetSQLName("SPC") + " SPC " + CRLF
cQryHE += " INNER JOIN " + RetSQLName("SP9") + " SP9 ON ( " + CRLF
cQryHE += " SP9.P9_CODIGO = SPC.PC_PD " + CRLF
cQryHE += " AND SP9.D_E_L_E_T_ = ' ' " + CRLF
cQryHE += " AND SP9.P9_CLASEV = '01' " + CRLF
cQryHE += " ) " + CRLF
cQryHE += " WHERE " + CRLF
cQryHE += " PC_FILIAL = '" + cFil + "' " + CRLF
cQryHE += " AND PC_MAT = '" + cMat + "' " + CRLF
cQryHE += " AND PC_DATA <= '" + dToS(cData) + "' " + CRLF
cQryHE += " AND SPC.D_E_L_E_T_ = ' ' " + CRLF
cQryHE += " AND SPC.PC_PDI = ' ' " + CRLF
cQryHE += " AND SPC.PC_ABONO = ' ' " + CRLF
TCQuery cQryHE New Alias "QRY_HE"
//Enquanto houver horas extras
While ! QRY_HE->(EoF())
nHoraExtra := SomaHoras(nHoraExtra, QRY_HE->PC_QUANTC)
QRY_HE->(DbSkip())
EndDo
QRY_HE->(DbCloseArea())
cHoraExtra := ""
If nHoraExtra > 0
cHora := cValToChar(StrZero(Int(nHoraExtra), 2))
cMin := cValToChar(StrZero((nHoraExtra - Int(nHoraExtra)) * 100, 2))
cHoraExtra := cHora + ":" + cMin
EndIf
cHtmPC += '<td align=center width=80><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += cHoraExtra
cHtmPC += '</td>'
EndIf
//Colunas de justificativa
If nParam11 == 2
cHtmPC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += '<font color=red><b>( ) '
cHtmPC += '</td>'
cHtmPC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += '<font color=red><b>( ) '
cHtmPC += '</td>'
cHtmPC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += '<font color=red><b>( ) '
cHtmPC += '</td>'
cHtmPC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += ' <font color=red><b>_ _ '
cHtmPC += '</td>'
EndIf
If nParam12 == 2
cHtmPC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += QRY_SPC->PC_ABONO
cHtmPC += " - "
cHtmPC += POSICIONE("SP6", 1, XFILIAL("SP6") + QRY_SPC->PC_ABONO, "P6_DESC")
cHtmPC += '</td>'
EndIf
If nParam13 == 2
cHtmPC += '<td align=center><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += QRY_SPC->PC_PDI
cHtmPC += " - "
cHtmPC += POSICIONE("SP9", 1, XFILIAL("SP9") + QRY_SPC->PC_PDI, "P9_DESC")
cHtmPC += '</td>'
EndIf
cHtmPC += '</tr>'
lAchouApon := .T.
cApontInt := ""
QRY_SPC->(DbSkip())
lTemCab := .T.
EndDo
//Se nao tiver apontamento, mas existir divergencia, sera mostrado
ElseIf !(Empty(cApontInt))
cHtmPC += '<tr>'
cHtmPC += '<td width=200><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += "<FONT color=red>" + cApontInt + "</font><br>"
cHtmPC += '</td>'
cHtmPC += '<td align=center width=50 valign=top><FONT face=tahoma SIZE=1 COLOR=black>'
cHtmPC += "<FONT color=red>" + cHoraInt + "</font> - <br>"
cHtmPC += '</td>'
cHtmPC += '</tr>'
EndIf
QRY_SPC->(DbCloseArea())
//Encerrando o html
cHtmPC += '</TABLE>'
cHtmTEM := '<table border=0>'
cHtmTEM += '<tr>'
cHtmTEM += '<td valign=top>'
cHtmTEM += cHtmDT
cHtmTEM += '</td>'
cHtmTEM += '<td valign=top>'
cHtmTEM += cHtmP8
cHtmTEM += '</td>'
cHtmTEM += '<td valign=top>'
cHtmTEM += cHtmPC
cHtmTEM += '</td>'
cHtmTEM += '</tr>'
cHtmTEM += '</TABLE>'
Return (Iif(lAchouApon, cHtmTEM, ""))
Bom pessoal, por hoje é só.
Abraços e até a próxima.
Dan,
Tem alguma customização para o envio de contracheques automaticamente para os e-mails cadastrados na SRA de cada funcionário?
Yuri!
Isso é padrão do sistema. No relatório do Recibo de Pagamento, tem a opção E-mail.
Segue TDN: https://tdn.totvs.com/display/public/PROT/MP+-+GPE0601+-+Envio+de+recibo+de+pagamento+por+email
Abs!
Yuri, conforme o Fabricio respondeu, já tem tratativas no padrão do Protheus.
Abraços jovem.