CREATE TABLE ZBATIDASREALIZAR (
CODCOLIGADA SMALLINT,
CHAPA VARCHAR(16),
DATA DATETIME,
CODHORARIO VARCHAR(10),
INDICE INT,
BATIDA INT,
TIPO INT,
NATUREZA INT,
CONSTRAINT PKZBATIDASREALIZAR PRIMARY KEY (CODCOLIGADA, CHAPA, DATA, CODHORARIO, INDICE, BATIDA, TIPO, NATUREZA))
GO
CREATE PROCEDURE SP_GERABATIDASEFETUAR @CODCOLIGADA SMALLINT, @CHAPA VARCHAR(16)
AS
BEGIN
DECLARE --@CODCOLIGADA SMALLINT,
--@CHAPA VARCHAR(16),
@DTMUDANCA DATETIME,
@CODHORARIO VARCHAR(10),
@INDINICIOHOR INT,
@INDICEMAXIMO INT,
@DATABASEHOR DATETIME,
@DTMUDANCAANT DATETIME,
@CODHORARIOANT VARCHAR(10),
@INDINICIOHORANT INT,
@INDICEMAXIMOANT INT,
@DATABASEHORANT DATETIME,
@INICIOPERMES DATETIME,
@FIMPERMES DATETIME,
@DATATMP DATETIME,
@INDICETMP INT,
@DATATMP2 DATETIME
DECLARE CR_HISTHOR CURSOR FOR
SELECT CODCOLIGADA,
CHAPA,
DTMUDANCA,
CODHORARIO,
INDINICIOHOR,
(SELECT MAX(DISTINCT INDICE)
FROM ABATHOR
WHERE CODCOLIGADA = PH.CODCOLIGADA
AND CODHORARIO = PH.CODHORARIO) AS MAXINDICEHOR,
(SELECT DATABASEHOR
FROM AHORARIO
WHERE CODCOLIGADA = PH.CODCOLIGADA
AND CODIGO = PH.CODHORARIO) AS DATABASEHORARIO
FROM PFHSTHOR PH
WHERE CODCOLIGADA = @CODCOLIGADA
AND CHAPA = @CHAPA
OPEN CR_HISTHOR
FETCH NEXT FROM CR_HISTHOR INTO @CODCOLIGADA, @CHAPA, @DTMUDANCA, @CODHORARIO,
@INDINICIOHOR, @INDICEMAXIMO, @DATABASEHOR
SELECT @INICIOPERMES = INICIOPERMES,
@FIMPERMES = FIMPERMES
FROM APARAM
WHERE CODCOLIGADA = @CODCOLIGADA
DELETE FROM ZBATIDASREALIZAR
WHERE CODCOLIGADA = @CODCOLIGADA
AND CHAPA = @CHAPA
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DTMUDANCAANT = @DTMUDANCA,
@CODHORARIOANT = @CODHORARIO,
@INDINICIOHORANT = @INDINICIOHOR,
@INDICEMAXIMOANT = @INDICEMAXIMO,
@DATABASEHORANT = @DATABASEHOR
FETCH NEXT FROM CR_HISTHOR INTO @CODCOLIGADA, @CHAPA, @DTMUDANCA, @CODHORARIO,
@INDINICIOHOR, @INDICEMAXIMO, @DATABASEHOR
SET @DATATMP = @DATABASEHORANT
SET @INDICETMP = @INDINICIOHORANT
IF @CODCOLIGADA IS NOT NULL
BEGIN
WHILE @DATATMP
AND @DATATMP NOT IN (SELECT DIAFERIADO
FROM GFERIADO
WHERE CODCALENDARIO IN (SELECT CODCALENDARIO
FROM PSECAO
WHERE CODCOLIGADA = @CODCOLIGADA
AND CODIGO = (SELECT CODSECAO
FROM PFUNC
WHERE CODCOLIGADA = @CODCOLIGADA
AND CHAPA = @CHAPA))))
BEGIN
INSERT INTO ZBATIDASREALIZAR (CODCOLIGADA, CHAPA, DATA, CODHORARIO, INDICE, BATIDA, TIPO, NATUREZA)
SELECT CODCOLIGADA, @CHAPA, @DATATMP, CODHORARIO, INDICE, BATIDA, TIPO, NATUREZA
FROM ABATHOR
WHERE CODCOLIGADA = @CODCOLIGADA
AND CODHORARIO = @CODHORARIOANT
AND INDICE = @INDICETMP
AND TIPO = 0
END
IF (@INDICETMP+1)>@INDICEMAXIMOANT
BEGIN
SET @INDICETMP = 1
END
ELSE
BEGIN
SET @INDICETMP = @INDICETMP+1
END
SET @DATATMP = @DATATMP+1
END
END
END
-- HISTÓRICO UNICO OU MÁXIMO HISTÓRICO
DECLARE CR_HISTHORULTIMO CURSOR FOR
SELECT CODCOLIGADA,
CHAPA,
DTMUDANCA,
CODHORARIO,
INDINICIOHOR,
(SELECT MAX(DISTINCT INDICE)
FROM ABATHOR
WHERE CODCOLIGADA = PH.CODCOLIGADA
AND CODHORARIO = PH.CODHORARIO) AS MAXINDICEHOR,
(SELECT DATABASEHOR
FROM AHORARIO
WHERE CODCOLIGADA = PH.CODCOLIGADA
AND CODIGO = PH.CODHORARIO) AS DATABASEHORARIO
FROM PFHSTHOR PH
WHERE CODCOLIGADA = @CODCOLIGADA
AND CHAPA = @CHAPA
AND DTMUDANCA = (SELECT MAX(DTMUDANCA)
FROM PFHSTHOR
WHERE CODCOLIGADA = @CODCOLIGADA
AND CHAPA = @CHAPA)
OPEN CR_HISTHORULTIMO
FETCH NEXT FROM CR_HISTHORULTIMO INTO @CODCOLIGADA, @CHAPA, @DTMUDANCA, @CODHORARIO,
@INDINICIOHOR, @INDICEMAXIMO, @DATABASEHOR
SET @DATATMP2 = @DATABASEHOR
SET @INDICETMP = @INDINICIOHOR
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE @DATATMP2<=@FIMPERMES BEGIN IF (@DATATMP2>=(@DATATMP)
AND @DATATMP2 NOT IN (SELECT DIAFERIADO
FROM GFERIADO
WHERE CODCALENDARIO IN (SELECT CODCALENDARIO
FROM PSECAO
WHERE CODCOLIGADA = @CODCOLIGADA
AND CODIGO = (SELECT CODSECAO
FROM PFUNC
WHERE CODCOLIGADA = @CODCOLIGADA
AND CHAPA = @CHAPA))))
BEGIN
INSERT INTO ZBATIDASREALIZAR (CODCOLIGADA, CHAPA, DATA, CODHORARIO, INDICE, BATIDA, TIPO, NATUREZA)
SELECT CODCOLIGADA, @CHAPA, @DATATMP2, CODHORARIO, INDICE, BATIDA, TIPO, NATUREZA
FROM ABATHOR
WHERE CODCOLIGADA = @CODCOLIGADA
AND CODHORARIO = @CODHORARIO
AND INDICE = @INDICETMP
AND TIPO = 0
END
IF (@INDICETMP+1)>@INDICEMAXIMO
BEGIN
SET @INDICETMP = 1
END
ELSE
BEGIN
SET @INDICETMP = @INDICETMP+1
END
SET @DATATMP2 = @DATATMP2+1
END
FETCH NEXT FROM CR_HISTHORULTIMO INTO @CODCOLIGADA, @CHAPA, @DTMUDANCA, @CODHORARIO,
@INDINICIOHOR, @INDICEMAXIMO, @DATABASEHOR
END
CLOSE CR_HISTHORULTIMO
DEALLOCATE CR_HISTHORULTIMO
CLOSE CR_HISTHOR
DEALLOCATE CR_HISTHOR
END
GO
/*
SP_GERABATIDASEFETUAR 15,'003029'
SELECT *
FROM ZBATIDASREALIZAR
ORDER BY DATA, BATIDA
GO
SELECT *
FROM ABATHOR
WHERE CODHORARIO = '018'
GO
*/
