--- # naldodj-wsl-2FA ## Autenticação 2FA para Usuário Root no WSL ### Introdução O Windows Subsystem for Linux (WSL) é uma ferramenta poderosa que permite aos desenvolvedores executar um ambiente Linux diretamente no Windows. No entanto, a segurança é uma preocupação importante, especialmente quando se trata de acessar o usuário root. Neste post, vamos mostrar como configurar a autenticação de dois fatores (2FA) para o usuário root ao acessar o WSL, garantindo uma camada adicional de segurança. ### Objetivo Vamos configurar um script de login que valida a senha do root e usa autenticação 2FA baseada em Time-based One-Time Password (TOTP), usando ferramentas comuns como `openssl`, `oathtool`, e `perl`. ### Passo 1: Instalar as Ferramentas Necessárias Primeiro, precisamos garantir que temos todas as ferramentas necessárias instaladas. Isso inclui `openssl`, `oathtool`, e `perl`. ```bash sudo apt-get update sudo apt-get install openssl oathtool perl ``` Para os scripts em Lua.
IF OBJECT_ID('tempdb..##tmp_table_turnover_empresa_20230124_001', 'U') IS NOT NULL
DROP TABLE ##tmp_table_turnover_empresa_20230124_001
BEGIN
WITH PERIODO AS
(
SELECT
DISTINCT SRD.RD_DATARQ PERIODO
FROM SRD990 SRD
WHERE SRD.D_E_L_E_T_=''
AND SRD.RD_FILIAL<>''
GROUP BY SRD.RD_DATARQ
UNION
SELECT
DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
FROM SRA990 SRA
WHERE SRA.D_E_L_E_T_=''
AND SRA.RA_FILIAL<>''
GROUP BY LEFT(SRA.RA_ADMISSA,6)
)
,TURNOVER AS (
SELECT
PERIODO.PERIODO
, ISNULL((
SELECT
SUM(TTRFSAI)
FROM
(
SELECT
COUNT(1) AS TTRFSAI
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPD='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPD
)
T
),0)
TTRFSAI
, ISNULL((
SELECT
SUM(TTRFENT)
FROM
(
SELECT
COUNT(1) AS TTRFENT
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPP='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPP
)
T
),0)
TTRFENT
, ISNULL((
SELECT
SUM(TFUNMES)
FROM
(
SELECT
COUNT(1) AS TFUNMES
FROM
SRA990 SRA_T
WHERE
SRA_T.D_E_L_E_T_= ''
AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
SRA_T.RA_DEMISSA=''
OR
(
LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
)
)
)
)
T
),0)
TFUNMES
, ISNULL((
SELECT
SUM(ADMISSAO)
FROM
(
SELECT
COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
FROM SRA990 SRA_A
WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
)
T
),0)
TFUNADMMES
, ISNULL((
SELECT
SUM(DEMISSAO)
FROM
(
SELECT COUNT(RA_DEMISSA) AS DEMISSAO
FROM SRA990 SRA_D
WHERE SRA_D.RA_DEMISSA<>''
AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
)
T
),0)
TFUNDEMMES
FROM PERIODO
WHERE PERIODO.PERIODO BETWEEN '202101' AND '202112'
)
, TURNOVERT AS (
SELECT TURNOVER.PERIODO
,TURNOVER.TTRFSAI
,TURNOVER.TTRFENT
,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT)
,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT)
,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT)
,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT)
FROM TURNOVER
)
SELECT TURNOVERT.PERIODO
,TURNOVERT.TTRFSAI
,TURNOVERT.TTRFENT
,TURNOVERT.TFUNIMES
,TURNOVERT.TFUNADMMES
,TURNOVERT.TFUNMES
,TURNOVERT.TFUNDEMMES
,TURNOVERT.TFUNFMES
,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
INTO ##tmp_table_turnover_empresa_20230124_001
FROM TURNOVERT
END
select * from ##tmp_table_turnover_empresa_20230124_001
Turnover Filial:
IF OBJECT_ID('tempdb..##tmp_table_turnover_filial_20230124_001', 'U') IS NOT NULL
DROP TABLE ##tmp_table_turnover_filial_20230124_001
BEGIN
WITH PERIODO AS
(
SELECT
DISTINCT SRD.RD_DATARQ PERIODO
, SRA.RA_FILIAL
FROM SRD990 SRD
JOIN SRA990 SRA ON (SRD.RD_FILIAL=SRA.RA_FILIAL AND SRD.RD_MAT=SRA.RA_MAT)
WHERE SRD.D_E_L_E_T_=''
AND SRA.D_E_L_E_T_=''
AND SRD.RD_FILIAL<>''
AND SRA.RA_FILIAL<>''
AND SRD.RD_FILIAL=SRA.RA_FILIAL
AND SRD.RD_MAT=SRA.RA_MAT
GROUP BY SRD.RD_DATARQ
, SRA.RA_FILIAL
UNION
SELECT
DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
, SRA.RA_FILIAL
FROM SRA990 SRA
WHERE SRA.D_E_L_E_T_=''
AND SRA.RA_FILIAL<>''
GROUP BY LEFT(SRA.RA_ADMISSA,6)
, SRA.RA_FILIAL
)
,TURNOVER AS (
SELECT
PERIODO.PERIODO
, PERIODO.RA_FILIAL
, ISNULL((
SELECT
SUM(TTRFSAI)
FROM
(
SELECT
COUNT(1) AS TTRFSAI
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPD='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
AND SRE_T.RE_FILIALD=PERIODO.RA_FILIAL
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPD
, SRE_T.RE_FILIALD
)
T
),0)
TTRFSAI
, ISNULL((
SELECT
SUM(TTRFENT)
FROM
(
SELECT
COUNT(1) AS TTRFENT
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPP='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
AND SRE_T.RE_FILIALP=PERIODO.RA_FILIAL
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPP
, SRE_T.RE_FILIALP
)
T
),0)
TTRFENT
, ISNULL((
SELECT
SUM(TFUNMES)
FROM
(
SELECT
COUNT(1) AS TFUNMES
FROM
SRA990 SRA_T
WHERE
SRA_T.D_E_L_E_T_= ''
AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
SRA_T.RA_DEMISSA=''
OR
(
LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
)
)
)
AND SRA_T.RA_FILIAL=PERIODO.RA_FILIAL
)
T
),0)
TFUNMES
, ISNULL((
SELECT
SUM(ADMISSAO)
FROM
(
SELECT
COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
FROM SRA990 SRA_A
WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
AND SRA_A.RA_FILIAL=PERIODO.RA_FILIAL
GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
, SRA_A.RA_FILIAL
)
T
),0)
TFUNADMMES
, ISNULL((
SELECT
SUM(DEMISSAO)
FROM
(
SELECT COUNT(RA_DEMISSA) AS DEMISSAO
FROM SRA990 SRA_D
WHERE SRA_D.RA_DEMISSA<>''
AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
AND SRA_D.RA_FILIAL=PERIODO.RA_FILIAL
AND SRA_D.RA_DEMISSA<>''
GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
, SRA_D.RA_FILIAL
)
T
),0)
TFUNDEMMES
FROM PERIODO
WHERE PERIODO.PERIODO BETWEEN '202101' AND '202112'
AND PERIODO.RA_FILIAL BETWEEN ' ' AND 'z '
)
, TURNOVERT AS (
SELECT TURNOVER.PERIODO
,TURNOVER.RA_FILIAL
,TURNOVER.TTRFSAI
,TURNOVER.TTRFENT
,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT)
,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT)
,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT)
,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT)
FROM TURNOVER
)
SELECT TURNOVERT.PERIODO
,TURNOVERT.RA_FILIAL
,TURNOVERT.TTRFSAI
,TURNOVERT.TTRFENT
,TURNOVERT.TFUNIMES
,TURNOVERT.TFUNADMMES
,TURNOVERT.TFUNMES
,TURNOVERT.TFUNDEMMES
,TURNOVERT.TFUNFMES
,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
INTO ##tmp_table_turnover_filial_20230124_001
FROM TURNOVERT
END
select * from ##tmp_table_turnover_filial_20230124_001
Turnover Centro de Custo:
IF OBJECT_ID('tempdb..##tmp_table_turnover_centro_de_custo_20230124_001', 'U') IS NOT NULL
DROP TABLE ##tmp_table_turnover_centro_de_custo_20230124_001
BEGIN
WITH PERIODO AS
(
SELECT
DISTINCT SRD.RD_DATARQ PERIODO
, SRA.RA_FILIAL
, SRA.RA_CC
FROM SRD990 SRD
JOIN SRA990 SRA ON (SRD.RD_FILIAL=SRA.RA_FILIAL AND SRD.RD_MAT=SRA.RA_MAT)
WHERE SRD.D_E_L_E_T_=''
AND SRA.D_E_L_E_T_=''
AND SRD.RD_FILIAL<>''
AND SRA.RA_FILIAL<>''
AND SRD.RD_FILIAL=SRA.RA_FILIAL
AND SRD.RD_MAT=SRA.RA_MAT
GROUP BY SRD.RD_DATARQ
, SRA.RA_FILIAL
, SRA.RA_CC
UNION
SELECT
DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
, SRA.RA_FILIAL
, SRA.RA_CC
FROM SRA990 SRA
WHERE SRA.D_E_L_E_T_=''
AND SRA.RA_FILIAL<>''
GROUP BY LEFT(SRA.RA_ADMISSA,6)
, SRA.RA_FILIAL
, SRA.RA_CC
)
,TURNOVER AS (
SELECT
PERIODO.PERIODO
, PERIODO.RA_FILIAL
, PERIODO.RA_CC
, CTT.CTT_DESC01
, ISNULL((
SELECT
SUM(TTRFSAI)
FROM
(
SELECT
COUNT(1) AS TTRFSAI
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPD='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
AND SRE_T.RE_FILIALD=PERIODO.RA_FILIAL
AND SRE_T.RE_CCD=PERIODO.RA_CC
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPD
, SRE_T.RE_FILIALD
, SRE_T.RE_CCD
)
T
),0)
TTRFSAI
, ISNULL((
SELECT
SUM(TTRFENT)
FROM
(
SELECT
COUNT(1) AS TTRFENT
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPP='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
AND SRE_T.RE_FILIALP=PERIODO.RA_FILIAL
AND SRE_T.RE_CCP=PERIODO.RA_CC
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPP
, SRE_T.RE_FILIALP
, SRE_T.RE_CCP
)
T
),0)
TTRFENT
, ISNULL((
SELECT
SUM(TFUNMES)
FROM
(
SELECT
COUNT(1) AS TFUNMES
FROM
SRA990 SRA_T
WHERE
SRA_T.D_E_L_E_T_= ''
AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
SRA_T.RA_DEMISSA=''
OR
(
LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
)
)
)
AND SRA_T.RA_FILIAL=PERIODO.RA_FILIAL
AND SRA_T.RA_CC=PERIODO.RA_CC
)
T
),0)
TFUNMES
, ISNULL((
SELECT
SUM(ADMISSAO)
FROM
(
SELECT
COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
FROM SRA990 SRA_A
WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
AND SRA_A.RA_FILIAL=PERIODO.RA_FILIAL
AND SRA_A.RA_CC=PERIODO.RA_CC
GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
, SRA_A.RA_FILIAL
, SRA_A.RA_CC
)
T
),0)
TFUNADMMES
, ISNULL((
SELECT
SUM(DEMISSAO)
FROM
(
SELECT COUNT(RA_DEMISSA) AS DEMISSAO
FROM SRA990 SRA_D
WHERE SRA_D.RA_DEMISSA<>''
AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
AND SRA_D.RA_CC=PERIODO.RA_CC
AND SRA_D.RA_DEMISSA<>''
GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
, SRA_D.RA_FILIAL
, SRA_D.RA_CC
)
T
),0)
TFUNDEMMES
FROM PERIODO
JOIN CTT990 CTT ON (PERIODO.RA_CC=CTT.CTT_CUSTO AND CTT.CTT_FILIAL=(CASE CTT.CTT_FILIAL WHEN '' THEN '' ELSE PERIODO.RA_FILIAL END))
WHERE PERIODO.PERIODO BETWEEN '202101' AND '202112'
AND PERIODO.RA_FILIAL BETWEEN ' ' AND 'z '
AND PERIODO.RA_CC BETWEEN '' AND 'z'
)
, TURNOVERT AS (
SELECT TURNOVER.PERIODO
,TURNOVER.RA_FILIAL
,TURNOVER.RA_CC
,TURNOVER.CTT_DESC01
,TURNOVER.TTRFSAI
,TURNOVER.TTRFENT
,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT)
,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT)
,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT)
,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT)
FROM TURNOVER
)
SELECT TURNOVERT.PERIODO
,TURNOVERT.RA_FILIAL
,TURNOVERT.RA_CC
,TURNOVERT.CTT_DESC01
,TURNOVERT.TTRFSAI
,TURNOVERT.TTRFENT
,TURNOVERT.TFUNIMES
,TURNOVERT.TFUNADMMES
,TURNOVERT.TFUNMES
,TURNOVERT.TFUNDEMMES
,TURNOVERT.TFUNFMES
,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
INTO ##tmp_table_turnover_centro_de_custo_20230124_001
FROM TURNOVERT
END
select * from ##tmp_table_turnover_centro_de_custo_20230124_001
Turnover Funções:
IF OBJECT_ID('tempdb..##tmp_table_turnover_funcoes_20230124_001', 'U') IS NOT NULL
DROP TABLE ##tmp_table_turnover_funcoes_20230124_001
BEGIN
WITH PERIODO AS
(
SELECT
DISTINCT SRD.RD_DATARQ PERIODO
, SRA.RA_FILIAL
, SRA.RA_CC
, SRA.RA_CODFUNC
FROM SRD990 SRD
JOIN SRA990 SRA ON (SRD.RD_FILIAL=SRA.RA_FILIAL AND SRD.RD_MAT=SRA.RA_MAT)
WHERE SRD.D_E_L_E_T_=''
AND SRA.D_E_L_E_T_=''
AND SRD.RD_FILIAL<>''
AND SRA.RA_FILIAL<>''
AND SRD.RD_FILIAL=SRA.RA_FILIAL
AND SRD.RD_MAT=SRA.RA_MAT
GROUP BY SRD.RD_DATARQ
, SRA.RA_FILIAL
, SRA.RA_CC
, SRA.RA_CODFUNC
UNION
SELECT
DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
, SRA.RA_FILIAL
, SRA.RA_CC
, SRA.RA_CODFUNC
FROM SRA990 SRA
WHERE SRA.D_E_L_E_T_=''
AND SRA.RA_FILIAL<>''
GROUP BY LEFT(SRA.RA_ADMISSA,6)
, SRA.RA_FILIAL
, SRA.RA_CC
, SRA.RA_CODFUNC
)
,TURNOVER AS (
SELECT
PERIODO.PERIODO
, PERIODO.RA_FILIAL
, PERIODO.RA_CC
, CTT.CTT_DESC01
, PERIODO.RA_CODFUNC
, SRJ.RJ_DESC
, ISNULL((
SELECT
SUM(TTRFSAI)
FROM
(
SELECT
COUNT(1) AS TTRFSAI
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPD='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
AND SRE_T.RE_FILIALD=PERIODO.RA_FILIAL
AND SRE_T.RE_CCD=PERIODO.RA_CC
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPD
, SRE_T.RE_FILIALD
, SRE_T.RE_CCD
)
T
),0)
TTRFSAI
, ISNULL((
SELECT
SUM(TTRFENT)
FROM
(
SELECT
COUNT(1) AS TTRFENT
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPP='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
AND SRE_T.RE_FILIALP=PERIODO.RA_FILIAL
AND SRE_T.RE_CCP=PERIODO.RA_CC
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPP
, SRE_T.RE_FILIALP
, SRE_T.RE_CCP
)
T
),0)
TTRFENT
, ISNULL((
SELECT
SUM(TFUNMES)
FROM
(
SELECT
COUNT(1) AS TFUNMES
FROM
SRA990 SRA_T
WHERE
SRA_T.D_E_L_E_T_= ''
AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
SRA_T.RA_DEMISSA=''
OR
(
LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
)
)
)
AND SRA_T.RA_FILIAL=PERIODO.RA_FILIAL
AND SRA_T.RA_CC=PERIODO.RA_CC
AND SRA_T.RA_CODFUNC=PERIODO.RA_CODFUNC
)
T
),0)
TFUNMES
, ISNULL((
SELECT
SUM(ADMISSAO)
FROM
(
SELECT
COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
FROM SRA990 SRA_A
WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
AND SRA_A.RA_FILIAL=PERIODO.RA_FILIAL
AND SRA_A.RA_CC=PERIODO.RA_CC
AND SRA_A.RA_CODFUNC=PERIODO.RA_CODFUNC
GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
, SRA_A.RA_FILIAL
, SRA_A.RA_CC
, SRA_A.RA_CODFUNC
)
T
),0)
TFUNADMMES
, ISNULL((
SELECT
SUM(DEMISSAO)
FROM
(
SELECT COUNT(RA_DEMISSA) AS DEMISSAO
FROM SRA990 SRA_D
WHERE SRA_D.RA_DEMISSA<>''
AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
AND SRA_D.RA_CC=PERIODO.RA_CC
AND SRA_D.RA_CODFUNC=PERIODO.RA_CODFUNC
AND SRA_D.RA_DEMISSA<>''
GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
, SRA_D.RA_FILIAL
, SRA_D.RA_CC
, SRA_D.RA_CODFUNC
)
T
),0)
TFUNDEMMES
FROM PERIODO
JOIN CTT990 CTT ON (PERIODO.RA_CC=CTT.CTT_CUSTO AND CTT.CTT_FILIAL=(CASE CTT.CTT_FILIAL WHEN '' THEN '' ELSE PERIODO.RA_FILIAL END))
JOIN SRJ990 SRJ ON (PERIODO.RA_CODFUNC=SRJ.RJ_FUNCAO AND SRJ.RJ_FILIAL=(CASE SRJ.RJ_FILIAL WHEN '' THEN '' ELSE PERIODO.RA_FILIAL END))
WHERE PERIODO.PERIODO BETWEEN '202101' AND '202112'
AND PERIODO.RA_FILIAL BETWEEN '' AND 'z'
AND PERIODO.RA_CC BETWEEN '' AND 'z '
AND PERIODO.RA_CODFUNC BETWEEN '' AND 'z'
)
, TURNOVERT AS (
SELECT TURNOVER.PERIODO
,TURNOVER.RA_FILIAL
,TURNOVER.RA_CC
,TURNOVER.CTT_DESC01
,TURNOVER.RA_CODFUNC
,TURNOVER.RJ_DESC
,TURNOVER.TTRFSAI
,TURNOVER.TTRFENT
,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT)
,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT)
,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT)
,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT)
FROM TURNOVER
)
SELECT TURNOVERT.PERIODO
,TURNOVERT.RA_FILIAL
,TURNOVERT.RA_CC
,TURNOVERT.CTT_DESC01
,TURNOVERT.RA_CODFUNC
,TURNOVERT.RJ_DESC
,TURNOVERT.TTRFSAI
,TURNOVERT.TTRFENT
,TURNOVERT.TFUNIMES
,TURNOVERT.TFUNADMMES
,TURNOVERT.TFUNMES
,TURNOVERT.TFUNDEMMES
,TURNOVERT.TFUNFMES
,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
INTO ##tmp_table_turnover_funcoes_20230124_001
FROM TURNOVERT
END
select * from ##tmp_table_turnover_funcoes_20230124_001
Turnover Funcionários:
IF OBJECT_ID('tempdb..##tmp_table_turnover_funcionarios_20230124_001', 'U') IS NOT NULL
DROP TABLE ##tmp_table_turnover_funcionarios_20230124_001
BEGIN
WITH PERIODO AS
(
SELECT
DISTINCT SRD.RD_DATARQ PERIODO
, SRA.RA_FILIAL
, SRA.RA_CC
, SRA.RA_CODFUNC
, SRA.RA_MAT
, SRA.R_E_C_N_O_ SRARECNO
FROM SRD990 SRD
JOIN SRA990 SRA ON (SRD.RD_FILIAL=SRA.RA_FILIAL AND SRD.RD_MAT=SRA.RA_MAT)
WHERE SRD.D_E_L_E_T_=''
AND SRA.D_E_L_E_T_=''
AND SRD.RD_FILIAL<>''
AND SRA.RA_FILIAL<>''
AND SRD.RD_FILIAL=SRA.RA_FILIAL
AND SRD.RD_MAT=SRA.RA_MAT
GROUP BY SRD.RD_DATARQ
, SRA.RA_FILIAL
, SRA.RA_CC
, SRA.RA_CODFUNC
, SRA.RA_MAT
, SRA.R_E_C_N_O_
UNION
SELECT
DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
, SRA.RA_FILIAL
, SRA.RA_CC
, SRA.RA_CODFUNC
, SRA.RA_MAT
, SRA.R_E_C_N_O_ SRARECNO
FROM SRA990 SRA
WHERE SRA.D_E_L_E_T_=''
AND SRA.RA_FILIAL<>''
GROUP BY LEFT(SRA.RA_ADMISSA,6)
, SRA.RA_FILIAL
, SRA.RA_CC
, SRA.RA_CODFUNC
, SRA.RA_MAT
, SRA.R_E_C_N_O_
)
,TURNOVER AS (
SELECT
PERIODO.PERIODO
, PERIODO.RA_FILIAL
, PERIODO.RA_CC
, CTT.CTT_DESC01
, PERIODO.RA_CODFUNC
, SRJ.RJ_DESC
, PERIODO.RA_MAT
, PERIODO.SRARECNO
, ISNULL((
SELECT
SUM(TTRFSAI)
FROM
(
SELECT
COUNT(1) AS TTRFSAI
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPD='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
AND SRE_T.RE_FILIALD=PERIODO.RA_FILIAL
AND SRE_T.RE_CCD=PERIODO.RA_CC
AND SRE_T.RE_MATD=PERIODO.RA_MAT
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPD
, SRE_T.RE_FILIALD
, SRE_T.RE_CCD
, SRE_T.RE_MATD
)
T
),0)
TTRFSAI
, ISNULL((
SELECT
SUM(TTRFENT)
FROM
(
SELECT
COUNT(1) AS TTRFENT
FROM
SRE990 SRE_T
WHERE
SRE_T.D_E_L_E_T_= ''
AND SRE_T.RE_EMPP='99'
AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
AND SRE_T.RE_FILIALP=PERIODO.RA_FILIAL
AND SRE_T.RE_CCP=PERIODO.RA_CC
AND SRE_T.RE_MATP=PERIODO.RA_MAT
GROUP BY
LEFT(SRE_T.RE_DATA,6)
, SRE_T.RE_EMPP
, SRE_T.RE_FILIALP
, SRE_T.RE_CCP
, SRE_T.RE_MATP
)
T
),0)
TTRFENT
, ISNULL((
SELECT
SUM(TFUNMES)
FROM
(
SELECT
COUNT(1) AS TFUNMES
FROM
SRA990 SRA_T
WHERE
SRA_T.D_E_L_E_T_= ''
AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
SRA_T.RA_DEMISSA=''
OR
(
LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
AND (
LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
)
)
)
AND SRA_T.RA_FILIAL=PERIODO.RA_FILIAL
AND SRA_T.RA_CC=PERIODO.RA_CC
AND SRA_T.RA_CODFUNC=PERIODO.RA_CODFUNC
AND SRA_T.RA_MAT=PERIODO.RA_MAT
AND SRA_T.R_E_C_N_O_=PERIODO.SRARECNO
)
T
),0)
TFUNMES
, ISNULL((
SELECT
SUM(ADMISSAO)
FROM
(
SELECT
COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
FROM SRA990 SRA_A
WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
AND SRA_A.RA_FILIAL=PERIODO.RA_FILIAL
AND SRA_A.RA_CC=PERIODO.RA_CC
AND SRA_A.RA_CODFUNC=PERIODO.RA_CODFUNC
AND SRA_A.RA_MAT=PERIODO.RA_MAT
GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
, SRA_A.RA_FILIAL
, SRA_A.RA_CC
, SRA_A.RA_CODFUNC
, SRA_A.RA_MAT
)
T
),0)
TFUNADMMES
, ISNULL((
SELECT
SUM(DEMISSAO)
FROM
(
SELECT COUNT(RA_DEMISSA) AS DEMISSAO
FROM SRA990 SRA_D
WHERE SRA_D.RA_DEMISSA<>''
AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
AND SRA_D.RA_CC=PERIODO.RA_CC
AND SRA_D.RA_CODFUNC=PERIODO.RA_CODFUNC
AND SRA_D.RA_MAT=PERIODO.RA_MAT
AND SRA_D.RA_DEMISSA<>''
AND SRA_D.R_E_C_N_O_=PERIODO.SRARECNO
GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
, SRA_D.RA_FILIAL
, SRA_D.RA_CC
, SRA_D.RA_CODFUNC
, SRA_D.RA_MAT
)
T
),0)
TFUNDEMMES
FROM PERIODO
JOIN CTT990 CTT ON (PERIODO.RA_CC=CTT.CTT_CUSTO AND CTT.CTT_FILIAL=(CASE CTT.CTT_FILIAL WHEN '' THEN '' ELSE PERIODO.RA_FILIAL END))
JOIN SRJ990 SRJ ON (PERIODO.RA_CODFUNC=SRJ.RJ_FUNCAO AND SRJ.RJ_FILIAL=(CASE SRJ.RJ_FILIAL WHEN '' THEN '' ELSE PERIODO.RA_FILIAL END))
WHERE PERIODO.PERIODO BETWEEN '202101 ' AND '202112 '
AND PERIODO.RA_FILIAL BETWEEN ' ' AND 'z '
AND PERIODO.RA_CC BETWEEN ' ' AND 'z '
AND PERIODO.RA_CODFUNC BETWEEN '' AND 'z'
)
, TURNOVERT AS (
SELECT TURNOVER.PERIODO
,TURNOVER.RA_FILIAL
,TURNOVER.RA_CC
,TURNOVER.CTT_DESC01
,TURNOVER.RA_CODFUNC
,TURNOVER.RJ_DESC
,TURNOVER.RA_MAT
,TURNOVER.SRARECNO
,TURNOVER.TTRFSAI
,TURNOVER.TTRFENT
,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT)
,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT)
,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT)
,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT)
FROM TURNOVER
)
SELECT TURNOVERT.PERIODO
,TURNOVERT.RA_FILIAL
,TURNOVERT.RA_CC
,TURNOVERT.CTT_DESC01
,TURNOVERT.RA_CODFUNC
,TURNOVERT.RJ_DESC
,TURNOVERT.RA_MAT
,TURNOVERT.SRARECNO
,TURNOVERT.TTRFSAI
,TURNOVERT.TTRFENT
,TURNOVERT.TFUNIMES
,TURNOVERT.TFUNADMMES
,TURNOVERT.TFUNMES
,TURNOVERT.TFUNDEMMES
,TURNOVERT.TFUNFMES
,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
INTO ##tmp_table_turnover_funcionarios_20230124_001
FROM TURNOVERT
END
select * from ##tmp_table_turnover_funcionarios_20230124_001
Comentários
Postar um comentário