TOTVS RM - CONVERTENDO CAMPO HORA (PFFINANC) PARA FORMATO DE TELA

select   
        codcoligada,
        chapa,
        anocomp,
        mescomp,
        nroperiodo,
        ref,
        hora,
        valor,

        (hora/60.0) hora_base_calc,

        floor(hora/(60.0)) hora_oficial,

        (hora/60.0) - floor(hora/(60.0)) minutos_base_calc,

        (((hora/60.0) - floor(hora/(60.0))) * 60) minutos_base_calc2,

        round((((hora/60.0) - floor(hora/(60.0))) * 60),1) minutos_oficial,

        floor(hora/(60.0)) +
        (round((((hora/60.0) - floor(hora/(60.0))) * 60),1))/100 HORA_TELA

from pffinanc
where hora > 0

SQL - RM Chronus (Exemplo) - Arno

delete from funchor
declare @codcoligada int
declare @chapa       varchar(20)
declare @codsituacao       varchar(20)
--set @chapa ='0276626'
DECLARE CURSOR_CHAPA CURSOR FOR
    SELECT CODCOLIGADA,CHAPA,CODSITUACAO FROM PFUNC WHERE CODSITUACAO IN ('T','P','E','M','F','I')
and chapa ='0461376' --('0263186','0266619)
        OPEN CURSOR_CHAPA
            FETCH NEXT FROM CURSOR_CHAPA INTO @CODCOLIGADA,@CHAPA,@CODSITUACAO
                WHILE (@@FETCH_STATUS<>-1)
BEGIN

delete from calendario
declare @codhorario varchar(10)
declare @chapa2   varchar(20)
declare @contador int
declare @nrodiashor  int
declare @indiniciohorcont  int
declare @inicioindice int
    declare cursor_dias cursor for 
SELECT B.CODCOLIGADA,B.CHAPA,B.CODHORARIO,B.INDINICIOHOR,DATEDIFF(DD,d.iniciopermes,GETDATE())
FROM PFUNC B,AHORARIO C,aparam d
WHERE B.codcoligada=@CODCOLIGADA and B.CHAPA=@CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
and b.codcoligada=d.codcoligada



open cursor_dias

fetch next from cursor_dias into @codcoligada,@chapa2,@codhorario,@inicioindice,@nrodiashor
    set @contador=@nrodiashor   
    select @indiniciohorcont=@inicioindice
    while @contador<>-1

begin
--drop table calendario
--create table calendario (codcoligada int,chapa varchar(20),data datetime,codhorario varchar(10),indice int,semana varchar(10), constraint pkcalendario primary key (codcoligada,data,codhorario,indice,semana))
insert into calendario
select     @codcoligada,@chapa2,
    convert(datetime,convert(varchar,datepart(yyyy,getdate()))+'/'+
    convert(varchar,datepart(mm,getdate()))+'/'+
    convert(varchar,datepart(dd,getdate())))-@contador,@codhorario,@indiniciohorcont,
    datename(dw,getdate()-@contador)



/*contadores*/

if @indiniciohorcont>=(SELECT MAX(INDICE) FROM ABATHOR WHERE CODHORARIO=@CODHORARIO)
begin
select @indiniciohorcont=(select distinct min(indice)
from abathor where codhorario=@codhorario)
end
else select @indiniciohorcont=@indiniciohorcont+1
select @contador=@contador-1

fetch next from cursor_dias into @codcoligada,@chapa2,@codhorario,@inicioindice,@nrodiashor
end
deallocate cursor_dias


IF @CODSITUACAO = 'F'
BEGIN

delete from func
declare @codcoligada2 int
declare @codhorario2 varchar(10)
declare @chapa3   varchar(20)
declare @contador2 int
declare @nrodiasaft1   int
declare @dtinicio1 datetime

declare cursor_comparacao1 cursor for 


SELECT A.CODCOLIGADA,A.CHAPA,B.CODHORARIO,case
    when dtfIMGOZO IS null
    then  datediff(dd,DTINIGOZO,getdate())
    else datediff(dd,dtiniGOZO,dtfiMGOZO)
    end ,dtiniGOZO
FROM PFHSTFER A,PFUNC B,AHORARIO C
WHERE A.codcoligada=@codcoligada and A.CHAPA=@chapa
AND A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
AND A.DTINIGOZO=(SELECT MAX(DTINIGOZO) FROM PFHSTFER WHERE codcoligada=@codcoligada AND CHAPA=@chapa)


open cursor_comparacao1
fetch next from cursor_comparacao1 into @codcoligada2,@chapa3,@codhorario2,@nrodiasaft1,@dtinicio1
    set @contador2=@nrodiasaft1      
    while @contador2<>-1

begin
--drop table func
--create table func (codcoligada int,chapa varchar(20),codhorario varchar(10),data datetime, constraint pkfunc primary key  (codcoligada,chapa,codhorario,data))
--delete from func
insert into func
    select @codcoligada2,@chapa3,@codhorario2,@dtinicio1+@contador2



select @contador2=@contador2-1
fetch next from cursor_comparacao1 into @codcoligada2,@chapa3,@codhorario2,@nrodiasaft1,@dtinicio1
end
deallocate cursor_comparacao1
--drop table funchor
--CREATE TABLE FUNCHOR (CHAPA VARCHAR(20),CODHORARIO VARCHAR(10),DATA DATETIME, INDICE INT,SEMANA VARCHAR(10),BASE INT)
INSERT INTO FUNCHOR
select     a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
from func a,calendario b,abathor c,
(select codcoligada,chapa,base from aafhtfun 
where data = (select max(data) from aafhtfun where codcoligada=@codcoligada and chapa=@chapa)
and codcoligada=@codcoligada and chapa=@chapa)d
where     a.codcoligada=@codcoligada
and    a.codcoligada=b.codcoligada
and     a.chapa=b.chapa
and      a.codhorario=b.codhorario
and      a.data=b.data
and     c.codcoligada=b.codcoligada
and     c.codhorario=b.codhorario
and     c.indice=b.indice
and     a.codcoligada=d.codcoligada
and     a.chapa=d.chapa
and    c.tipo in (0)
and     b.semana <> 'Sunday'
and     b.data not in (select gferiado.diaferiado from gferiado)
and     b.data not in
(select data from aafhtfun,aparam where
aafhtfun.codcoligada=@codcoligada and aafhtfun.chapa=@chapa
and data between iniciopermes and getdate())
group by a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
order by a.data

END
ELSE
if @codsituacao in ('T','P','E','M')
/*SEGUNDA PARTE - COMPARACAO*/
begin
delete from func
declare @codcoligada1 int
declare @codhorario1 varchar(10)
declare @chapa1   varchar(20)
declare @contador1 int
declare @nrodiasaft   int
declare @dtinicio datetime

declare cursor_comparacao cursor for 
SELECT A.CODCOLIGADA,A.CHAPA,B.CODHORARIO,case
    when dtfinal IS null
    then  datediff(dd,DTINICIO,getdate())
    else datediff(dd,dtinicio,dtfinal)
    end ,dtinicio
FROM PFHSTAFT A,PFUNC B,AHORARIO C
WHERE A.codcoligada=@CODCOLIGADA and A.CHAPA=@CHAPA
AND A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
AND A.DTINICIO=(SELECT MAX(DTINICIO) FROM PFHSTAFT WHERE CODCOLIGADA=@CODCOLIGADA AND CHAPA=@CHAPA)

open cursor_comparacao
fetch next from cursor_comparacao into @codcoligada1,@chapa1,@codhorario1,@nrodiasaft,@dtinicio
    set @contador1=@nrodiasaft      
    while @contador1<>-1

begin
--drop table func
--create table func (codcoligada int,chapa varchar(20),codhorario varchar(10),data datetime, constraint pkfunc primary key  (codcoligada,chapa,codhorario,data))
insert into func
    select @codcoligada1,@chapa1,@codhorario1,@dtinicio+@contador1



select @contador1=@contador1-1
fetch next from cursor_comparacao into @codcoligada1,@chapa1,@codhorario1,@nrodiasaft,@dtinicio
end
deallocate cursor_comparacao
--drop table funchor
--CREATE TABLE FUNCHOR (CHAPA VARCHAR(20),CODHORARIO VARCHAR(10),DATA DATETIME, INDICE INT,SEMANA VARCHAR(10),BASE INT)
INSERT INTO FUNCHOR
select     a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
from func a,calendario b,abathor c,
(select codcoligada,chapa,base from aafhtfun 
where data = (select max(data) from aafhtfun where codcoligada=@codcoligada and chapa=@chapa)
and codcoligada=@codcoligada and chapa=@chapa)d
where     a.codcoligada=@codcoligada
and    a.codcoligada=b.codcoligada
and     a.chapa=b.chapa
and      a.codhorario=b.codhorario
and      a.data=b.data
and     c.codcoligada=b.codcoligada
and     c.codhorario=b.codhorario
and     c.indice=b.indice
and     a.codcoligada=d.codcoligada
and     a.chapa=d.chapa
and     c.tipo in (0)--(c.tipo<>1 and c.tipo<>3)-- and c.tipo<>4)
and     b.semana <> 'Sunday'
and     b.data not in (select gferiado.diaferiado from gferiado)
group by a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
order by a.data
end



FETCH NEXT FROM CURSOR_CHAPA INTO @CODCOLIGADA,@CHAPA,@CODSITUACAO
END
DEALLOCATE CURSOR_CHAPA



/*select * from gferiado where diaferiado='2003-07-04'
select * from abathor where codhorario='20'*/
/*select *
from FUNCHOR where data between '2003-06-16' and '2003-07-15' order by chapa , data
*/
/*
select * from abatfunam WHERE CHAPA = '0334078'

select a.codcoligada,a.chapa from pfunc a where  exists
(select 1 from aafhtfun b where a.codcoligada=b.codcoligada and a.chapa=b.chapa)
and codsituacao = 'P'

select * from gferiado
order by diaferiado

select * from abathor where  CODHORARIO='30' order by INDICE


*/

RM/RMChronus - Gera Batidas a Efetuar

Script que simula a geração dos horários do funcionários quem deverão ser efetuados de acordo com o Histórico de Horários e Índices.


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(@DTMUDANCAANT)
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
*/