create view [dbo].[vUsuariosAfastados] as
select
corpia.id,
corpia.Domain [dominio],
corpia.SAMAccountName [login],
f.matricula,
min(f.dt_inicio)[dt_inicio],
max(f.dt_fim)[dt_fim],
tipo,
cpf
from (
select 'Férias' as tipo,
id_funcionario [matricula],
data_saida [dt_inicio],
data_retorno [dt_fim]
from VIEW_CLIENTE_FERIAS
where
(
--Regra: Funcionários que entram em férias hoje
data_saida = dateadd(day,0,CONVERT(date, getdate()))
)
union
select 'Afastamento' as tipo,
id_funcionario [matricula],
data_saida [dt_inicio],
data_retorno [dt_fim]
from VIEW_CLIENTE_AFASTAMENTO
where
(
--Regra: Funcionários que entram em afastamento hoje
data_saida = dateadd(day,0,CONVERT(date, getdate()))
)
) f
inner join vUsuariosAtivosCorpia corpia
on corpia.register = convert(varchar,f.matricula)
where corpia.SAMAccountName is not null
group by corpia.id, f.matricula, corpia.Domain, corpia.SAMAccountName, tipo, corpia.cpf
CREATE TABLE [dbo].[Afastamento](
[id_usuario_sga] [int] NULL,
[dominio] [nvarchar](50) NULL,
[login] [nvarchar](50) NULL,
[dt_inicio] [datetime] NULL,
[dt_fim] [datetime] NULL,
[matricula] [nvarchar](50) NULL,
[cpf] [nvarchar](50) NULL,
[flg_afastamento] [bit] NULL,
[flg_retorno] [bit] NULL,
[dt_criacao] [datetime] NULL,
[dt_modificacao] [datetime] NULL,
[tipo] [nvarchar](50) NULL
) ON [PRIMARY]
Create PROCEDURE [dbo].[spProcessoAfastamento]
AS
BEGIN
--1) Atualiza a data de fim do afastamento de um usuário, caso seja ajustada no RH
UPDATE Afastamento
SET dt_fim = AfastadosRH.dt_fim,
dt_modificacao = getdate()
FROM (
select afastados.dt_fim, id_usuario_sga, afastados.dt_inicio , controle.dt_modificacao from Afastamento controle
inner join vUsuariosAfastados afastados
on afastados.dt_inicio = controle.dt_inicio
and controle.login= afastados.login
and controle.dt_fim <> afastados.dt_fim
) AS AfastadosRH
WHERE AfastadosRH.dt_inicio = Afastamento.dt_inicio
and AfastadosRH.id_usuario_sga = Afastamento.id_usuario_sga
--2) Atualiza o status de "desligamento" de um usuário, caso seja ajustado no RH
UPDATE Afastamento
SET tipo = 'Desligamento',
flg_retorno = 1,
dt_modificacao = getdate()
FROM (
select af.login, af.matricula, active.register from afastamento af
left join vUsuariosAtivosCorpia active
on
active.register = af.matricula
where active.register is null
group by login, af.matricula,active.register
) AS DemitidosRH
WHERE DemitidosRH.matricula = Afastamento.matricula
and Afastamento.flg_retorno = 0
--3) Insere os usuários que foram recém afastados na tabela de controle (Afastamento):
insert into afastamento (id_usuario_sga,dominio, [login], dt_inicio, dt_fim, matricula, cpf, flg_afastamento, flg_retorno, dt_criacao, tipo)
select id, dominio, [login], dt_inicio, dt_fim, matricula, cpf, 0, 0, getdate(), tipo
from vUsuariosAfastados afast
WHERE not exists
(
select 1 from Afastamento sync
where
afast.dominio = sync.dominio and
afast.login = sync.[login] and
afast.dt_fim = sync.dt_fim and
afast.dt_inicio = sync.dt_inicio
)
--4) Vamos retornar os usuários afastados que não foram processados pela rotina (ou seja, flg_afastamento = 0) e também os usuários que retornam do afastamento amanhã
select *
from Afastamento
where flg_afastamento = 0
or (flg_retorno = 0 and (DATEDIFF( day , dt_fim , GETDATE()) = -1))
order by dt_fim
END