Egalware/gpw_testci
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
develop
gpw_testci/GPW_Data/SqlScripts/GPW/GPW_00329_ets.sql
T
marco 16595ec778 Initial commit
2021-03-26 17:17:28 +01:00

1391 lines
53 KiB
Transact-SQL
Raw Permalink Blame History

set xact_abort on;
go
begin transaction;
go
create table AnagGruppi(
gruppo nvarchar(50) not null constraint PK_AnagGruppi primary key,
descrGruppo nvarchar(50),
codExt nvarchar(4),
exportEnab bit constraint DF_AnagGruppi_exportEnabled default ((1))
);
go
exec sp_addextendedproperty 'MS_Description', 'determina se sia abilitato x export dati', 'SCHEMA', 'dbo', 'TABLE', 'AnagGruppi', 'COLUMN', 'exportEnab';
go
commit;
go
set xact_abort on
go
begin transaction
go
INSERT INTO dbo.AnagGruppi
VALUES (N'COLLABORATORE', N'COLLABORATORE', N'', 0)
INSERT INTO dbo.AnagGruppi
VALUES (N'ETS', N'Azienda principale ETS', N'0175', 1)
INSERT INTO dbo.AnagGruppi
VALUES (N'SNOB', N'Studio Nuovo Osp BG', N'0397', 1)
INSERT INTO dbo.AnagGruppi
VALUES (N'STUDIO TECNICO ASSOCIATO', N'Studio Romano', N'0358', 1)
INSERT INTO dbo.AnagGruppi
VALUES (N'XPANDING', N'XPanding', N'0315', 1)
go
commit transaction
go
alter table Dipendenti add
constraint FK_Dipendenti_AnagGruppi foreign key(gruppo) references AnagGruppi(gruppo) on update cascade;
go
set xact_abort on;
go
begin transaction;
go
alter table TimbratureExpl drop
constraint DF_TimbratureExpl_minLav ,
constraint DF_TimbratureExpl_minOrd ,
constraint DF_TimbratureExpl_minFest1 ,
constraint DF_TimbratureExpl_minFer ,
constraint DF_TimbratureExpl_minNonLav ,
constraint DF_TimbratureExpl_minStra ,
constraint DF_TimbratureExpl_minPerm ,
constraint DF_TimbratureExpl_isOk ,
constraint DF_TimbratureExpl_chkFunRes ,
constraint DF_TimbratureExpl_chkFunRes1 ,
constraint DF_TimbratureExpl_block ,
constraint DF_TimbratureExpl_isOkTim ,
constraint DF_TimbratureExpl_minFest ;
go
exec sp_rename 'PK_TimbratureExpl_1', 'tmp__PK_TimbratureExpl_1', 'OBJECT';
go
exec sp_rename 'TimbratureExpl', 'tmp__TimbratureExpl_1', 'OBJECT';
go
create table TimbratureExpl(
dataLav date not null,
idxDipendente int not null,
CognomeNome nvarchar(100),
entrata_1 datetime,
uscita_1 datetime,
entrata_2 datetime,
uscita_2 datetime,
entrata_3 datetime,
uscita_3 datetime,
entrata_4 datetime,
uscita_4 datetime,
h_lav float,
h_giust as (((((CONVERT([float],[minPerm],(0))+[minFer])+[minMal])+[minFest])+[minMpp])/(60)),
minLav int constraint DF_TimbratureExpl_minLav default ((0)),
minOrd int constraint DF_TimbratureExpl_minOrd default ((0)),
minNonLav int constraint DF_TimbratureExpl_minNonLav default ((0)),
minStra int constraint DF_TimbratureExpl_minStra default ((0)),
minPerm int constraint DF_TimbratureExpl_minPerm default ((0)),
minFer int not null constraint DF_TimbratureExpl_minFer default ((0)),
minMal int constraint DF_TimbratureExpl_minMal default ((0)),
minFest int constraint DF_TimbratureExpl_minFest default ((0)),
minMpp int constraint DF_TimbratureExpl_minMpp default ((0)),
minArcoPres as (datediff(minute,isnull([entrata_1],getdate()),isnull(isnull([uscita_4],isnull([uscita_3],isnull([uscita_2],isnull([uscita_1],[entrata_1])))),getdate()))),
isOkTim bit constraint DF_TimbratureExpl_isOkTim default ((0)),
isOkApp bit constraint DF_TimbratureExpl_isOk default ((1)),
isOkLav as (case when [minOrd]<=(((([minLav]+[minPerm])+[minFer])+[minMal])+[minFest]) then (1) else (0) end),
isOk as (([isOkTim]&[isOkApp])&case when [minOrd]<=(((([minLav]+[minPerm])+[minFer])+[minMal])+[minFest]) then (1) else (0) end),
block bit constraint DF_TimbratureExpl_block default ((0)),
chkFunCod nvarchar(50) constraint DF_TimbratureExpl_chkFunCod default (''),
chkFunRes nvarchar(50) constraint DF_TimbratureExpl_chkFunRes default (''),
constraint PK_TimbratureExpl_1 primary key(dataLav,idxDipendente)
);
go
create index ix_idxDip on TimbratureExpl(idxDipendente)
include(dataLav,CognomeNome,h_lav,minOrd,minStra,minPerm,minFer,block,isOkApp,isOkTim);
go
create index ix_TimbrExpl_CognomeNome_DataLav on TimbratureExpl(CognomeNome,dataLav);
go
exec sp_addextendedproperty 'MS_Description', 'totale ore giustificate', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'h_giust';
go
exec sp_addextendedproperty 'MS_Description', 'minuti non lavorati (ovvero se fatti meno di ordinari e senza giustificativi)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'minNonLav';
go
exec sp_addextendedproperty 'MS_Description', 'DATEDIFF(n, ISNULL(entrata_1,GETDATE()), ISNULL(ISNULL(uscita_4,ISNULL(uscita_3,ISNULL(uscita_2,ISNULL(uscita_1,entrata_1)))),GETDATE()))', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'minArcoPres';
go
exec sp_addextendedproperty 'MS_Description', 'dato sintetico x indicare se TUTTE le timbrature componenti siano approvate', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'isOkApp';
go
exec sp_addextendedproperty 'MS_Description', 'determina se la giornata sia ok (oreLav + giustificativi >= oreOrd)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'isOkLav';
go
exec sp_addextendedproperty 'MS_Description', 'determina se il record sia "bloccato" (archiviazione e blocco mesi precedenti...)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'block';
go
exec sp_addextendedproperty 'MS_Description', 'eventuale diagnostica da check function sulla riga indicata (codice)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'chkFunCod';
go
exec sp_addextendedproperty 'MS_Description', 'eventuale diagnostica da check function sulla riga indicata (spiegazione)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'chkFunRes';
go
insert into TimbratureExpl(dataLav,idxDipendente,CognomeNome,entrata_1,uscita_1,entrata_2,uscita_2,entrata_3,uscita_3,entrata_4,uscita_4,h_lav,minLav,minOrd,minNonLav,minStra,minPerm,minFer,minMal,minFest,isOkTim,isOkApp,block,chkFunCod,chkFunRes) select dataLav,idxDipendente,CognomeNome,entrata_1,uscita_1,entrata_2,uscita_2,entrata_3,uscita_3,entrata_4,uscita_4,h_lav,minLav,minOrd,minNonLav,minStra,minPerm,minFer,minMal,minFest,isOkTim,isOkApp,block,chkFunCod,chkFunRes from tmp__TimbratureExpl_1;
go
drop table tmp__TimbratureExpl_1;
go
commit;
go
set xact_abort on;
go
begin transaction;
go
set ANSI_NULLS on;
go
alter TRIGGER trg_giustUpdateTimbr
ON Giustificativi
FOR INSERT, UPDATE
AS
-- Dichiarazione Variabili
DECLARE @idxDipendente INT
DECLARE @dataRif DATETIME
DECLARE @Tot As INT;
DECLARE @Riga As INT = 1;
DECLARE @trovate INT = 0
DECLARE @TabVar table (
Riga int NOT NULL,
idxDipendente [int] NOT NULL,
dataLav [date] NOT NULL )
-- ===============================================
-- Ciclo per ogni Dipendente/data che non ha corrispondente
-- record in TimbratureExpl
-- ==============================================
IF EXISTS ( SELECT * FROM inserted i
LEFT JOIN TimbratureExpl te ON i.idxDipendente = te.idxDipendente AND i.dataLav = te.dataLav
WHERE te.idxDipendente IS NULL )
BEGIN
-- carico i record interessati
INSERT @TabVar
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY i.idxDipendente ), i.idxDipendente , i.dataLav FROM inserted i
LEFT JOIN TimbratureExpl te ON i.idxDipendente = te.idxDipendente AND i.dataLav = te.dataLav
WHERE te.idxDipendente IS NULL
SET @Tot = ( SELECT COUNT(*) FROM @TabVar )
WHILE @Riga <= @Tot
BEGIN
SELECT @idxDipendente = idxDipendente, @dataRif=dataLav FROM @TabVar WHERE Riga = @Riga;
EXEC stp_ricalcolaTimbratureExpl @idxDipendente, @dataRif;
SET @Riga = @Riga + 1;
END
END
-- =============================================================
-- CONTROLLO SE IL RECORD NON SIA BLOCCATO con where nell'update
-- prendo tutte le "vecchie" righe (deleted) e x le giornate relative TOLGO i minuti dei giustificativi
-- =============================================================
UPDATE TimbratureExpl
SET minMal = CASE WHEN g.codGiust IN('MAL','MAT','INF') THEN 0 ELSE minMal END,
minFer = CASE WHEN g.codGiust='FER' THEN 0 ELSE minFer END,
minFest = CASE WHEN g.codGiust='FEST' THEN 0 ELSE minFest END,
minPerm = CASE WHEN g.codGiust='PERM' THEN 0 ELSE minPerm END,
minMpp = CASE WHEN g.codGiust='MPP' THEN 0 ELSE minMpp END
FROM TimbratureExpl te
INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav
WHERE te.block = 0
-- =============================================================
-- prendo le "nuove" righe (inserted) e x le giornate relative AGGIUNGO i minuti dei giustificativi
-- =============================================================
UPDATE TimbratureExpl
SET minMal = CASE WHEN g.codGiust IN('MAL','MAT','INF') THEN g.minuti ELSE minMal END,
minFer = CASE WHEN g.codGiust='FER' THEN g.minuti ELSE minFer END,
minFest = CASE WHEN g.codGiust='FEST' THEN g.minuti ELSE minFest END,
minPerm = CASE WHEN g.codGiust='PERM' THEN g.minuti ELSE minPerm END,
minMpp = CASE WHEN g.codGiust='MPP' THEN g.minuti ELSE minMpp END
FROM TimbratureExpl te
INNER JOIN inserted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav
WHERE te.block = 0
-- ===============================================
-- Ciclo per ogni Dipendente/data INSERITO e verifico il chkFunDip
-- ==============================================
DELETE @TabVar;
SET @Riga = 1;
INSERT @TabVar
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY idxDipendente ), idxDipendente, datalav
FROM inserted;
SET @Tot = ( SELECT COUNT(*) FROM @TabVar )
WHILE @Riga <= @Tot
BEGIN
SELECT @idxDipendente = idxDipendente, @dataRif=dataLav FROM @TabVar
WHERE Riga = @Riga
-- verifico SE codice orario richieda una chkFun eseguo calcolo...
IF(dbo.f_chkFunDip(@idxDipendente)<>'')
BEGIN
EXEC stp_TE_doChkFun @idxDipendente, @dataRif
END
SET @Riga = @Riga + 1;
END
go
alter TRIGGER trg_giustDelUpdTimbr
ON Giustificativi
FOR DELETE
AS
-- Dichiarazione Variabili
DECLARE @idxDipendente INT
DECLARE @dataRif DATETIME
DECLARE @Tot As INT;
DECLARE @Riga As INT = 1;
-- ===============================================
-- CONTROLLO SE IL RECORD NON SIA BLOCCATO in update con where
-- ===============================================
-- prendo tutte le "vecchie" righe (deleted) e x le giornate relative TOLGO i minuti dei giustificativi
UPDATE TimbratureExpl
SET minMal = CASE WHEN g.codGiust IN('MAL','MAT','INF') THEN 0 ELSE minMal END,
minFer = CASE WHEN g.codGiust='FER' THEN 0 ELSE minFer END,
minFest = CASE WHEN g.codGiust='FEST' THEN 0 ELSE minFest END,
minPerm = CASE WHEN g.codGiust='PERM' THEN 0 ELSE minPerm END,
minMpp = CASE WHEN g.codGiust='MPP' THEN 0 ELSE minMpp END
FROM TimbratureExpl te
INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav
WHERE te.block = 0
-- ===============================================
-- Ciclo per ogni Dipendente/data e verifico il chkFunDip
-- ==============================================
DECLARE @TabVar table (
Riga int IDENTITY(1,1) NOT NULL,
idxDipendente [int] NOT NULL,
dataLav [date] NOT NULL
)
-- carico tabella
INSERT @TabVar
SELECT DISTINCT idxDipendente, datalav FROM deleted;
SET @Tot = SCOPE_IDENTITY(); -- numero righe
WHILE @Riga <= @Tot
BEGIN
SELECT @idxDipendente = idxDipendente, @dataRif=dataLav FROM @TabVar
WHERE Riga = @Riga
-- verifico SE codice orario richieda una chkFun eseguo calcolo...
IF(dbo.f_chkFunDip(@idxDipendente)<>'')
BEGIN
EXEC stp_TE_doChkFun @idxDipendente, @dataRif
END
SET @Riga = @Riga + 1;
END
go
commit;
go
set ANSI_NULLS on;
go
create FUNCTION f_calcMinLav( @minLav AS INT, @minStra AS INT , @minMpp AS INT )
-- Calcola i minuti lavorati effettivi al netto della mancata pausa mensa se presente
RETURNS INT
AS
BEGIN
DECLARE @MinEff AS INT;
SET @MinEff = CASE
WHEN (@minMpp > 0 )
THEN @minLav - ( @minMpp ) -- se esiste mancata pausa mensa superiore a ore straordinarie , sottraggo la diff a ore lavorate
ELSE @minLav -- Se Mancata pausa mensa = 0 o MPP già coperta dallo straordinario
END
RETURN @MinEff
END
go
create FUNCTION f_calcMinPerm( @minOrd AS INT, @minLav AS INT, @minStra AS INT, @minMpp AS INT, @minPerm AS INT )
-- Calcola i minuti di permesso sommando eventuale Mancata Pausa Mensa non coperta da straordinario
-- Se ho una mancata pausa mensa che non è coperta da min di straordinario si trasforma sempre in permesso e
-- quindi si va ad aggiungere a permessi già presenti
RETURNS INT
AS
BEGIN
DECLARE @MinEff AS INT;
IF @minMpp > 0
SET @MinEff = CASE -- se esiste mancata pausa mensa superiore a ore straordinarie la sommo ai permessi
WHEN ( @minMpp > @minStra ) THEN @minPerm + ( @minMpp-@minStra )
ELSE @minPerm
END
ELSE SET @MinEff = @minPerm
RETURN @MinEff
END
go
create FUNCTION f_calcMinStraor( @minStra AS INT, @minMpp AS INT )
-- Calcola i minuti di straordinario al netto della mancata pausa mensa se esiste
RETURNS INT
AS
BEGIN
DECLARE @MinEff AS INT;
SET @MinEff = CASE
WHEN @minStra > @minMpp THEN @minStra-@minMpp -- se esiste mancata pausa mensa
ELSE 0
END
RETURN @MinEff
END
go
create FUNCTION f_convMinArrot( @Minuti AS INT, @MinArrot AS INT , @ArrType CHAR(1) )
-- dato i minuti ritorna gli stessi attotondati ai minuti @MinArrot e in eccesso o difetto
-- @ArrType E-eccesso D-Difetto
RETURNS decimal( 9 , 3 )
AS
BEGIN
DECLARE @Min AS decimal( 9 , 3 );
IF @ArrType = 'E' -- arrotondamento in eccesso
BEGIN
SET @Min = CEILING( CAST( @Minuti AS decimal( 9 , 3 ) ) / @MinArrot ) * @MinArrot
END
ELSE
BEGIN -- arrotondamento in difetto
SET @Min = FLOOR( CAST( @Minuti AS decimal( 9 , 3 )) / @MinArrot ) * @MinArrot
END
RETURN @Min
END
go
create FUNCTION f_convMinOreArrot( @Minuti AS INT, @MinArrot AS INT , @ArrType CHAR(1) )
-- dato i minuti ritorna le relative ore attotondate ai minuti @MinArrot e in eccesso o difetto
-- @ArrType E-eccesso D-Difetto
RETURNS decimal( 9 , 3 )
AS
BEGIN
DECLARE @Ore AS decimal( 9 , 3 );
IF @ArrType = 'E' -- arrotondamento in eccesso
BEGIN
SET @Ore= CEILING( CAST( @Minuti AS decimal( 9 , 3 ) ) / @MinArrot ) / ( 60 / @MinArrot )
END
ELSE
BEGIN -- arrotondamento in difetto
SET @Ore= FLOOR( CAST( @Minuti AS decimal( 9 , 3 )) / @MinArrot ) / ( 60 / @MinArrot )
END
RETURN @Ore
END
go
create FUNCTION f_expchkFest( @Data AS DATE , @idxDipendente AS INT )
-- check e ritorno x export se la data è un festivo o sabato e il dipendente è in forza
RETURNS CHAR(4)
AS
BEGIN
DECLARE @Ritorno AS VARCHAR(4);
SET @Ritorno =
CASE
-- se non è in forza alla data
WHEN NOT EXISTS ( SELECT * FROM dbo.Dipendenti WHERE idxDipendente = @idxDipendente
AND dataAssunzione <= @Data OR dataCessazione >= @Data )
THEN '----'
-- se è un giorno festivo
WHEN EXISTS ( SELECT * FROM dbo.CalendFesteFerie WHERE data = @Data AND codGiust = 'FEST' )
THEN '++++'
-- se è Domenica
WHEN DATENAME( weekday , @Data ) = 'Sunday'
THEN '++++'
-- se è Sabato
WHEN DATENAME( weekday , @Data ) = 'Saturday'
THEN '===='
ELSE ''
END;
RETURN @Ritorno
END
go
alter FUNCTION f_expTimeHHMM( @Time AS DECIMAL(10,5) , @Format CHAR(1), @Arrot AS INT)
-- ritorna ore per file di export x paghe formato carattere es . 8 0re 30 minuti -> 0850
-- Time valore da convertire
-- Format Formato del valore passato ( M-minuti O-Ore )
RETURNS CHAR(4)
AS
BEGIN
DECLARE @Ore AS INT;
DECLARE @Min AS INT;
DECLARE @OreMin AS DECIMAL(10,5);
-- se mi passano ore converto Ore in minuti - Formato Ore ( es. 1,5 ; 1,67 )
IF @Format = 'H'
SET @Time = ROUND( ( @Time * 60 ) , 0 )
-- Poi faccio calcoli in minuti
SET @OreMin = CONVERT(INT, ( @Time / @Arrot ) )
SET @OreMin = ROUND( ( ( @OreMin * @Arrot ) / 60 ) * 100 , 0)
RETURN dbo.f_padLeft( CONVERT( VARCHAR(4) , CONVERT(INT, @OreMin )) , 4, '0')
END
go
set xact_abort on;
go
begin transaction;
go
set ANSI_NULLS on;
go
create VIEW v_OreTimbratureDipendente
AS
/*
VISTA PRINCIPALE DOVE LEGGERE I DATI COMPLETI SULLE TIMBRATURE
*/
SELECT
te.dataLav
, te.CognomeNome
, te.idxDipendente
, te.entrata_1
, te.uscita_1
, te.entrata_2
, te.uscita_2
, te.entrata_3
, te.uscita_3
, te.entrata_4
, te.uscita_4
-- =======================================================================
-- CALCOLO ORE EFFETTIVE CON ARROTONDAMETO
-- =======================================================================
,dbo.f_convMinOreArrot( minOrd, ta.valint, 'E' ) AS h_ordinarie
-- uso funzioni per ritornare valori al netto della mancata pausa mensa
,dbo.f_convMinOreArrot( dbo.f_calcMinLav(minLav,minStra,minMpp ), ta.valint, 'D' ) AS h_lavorate
,dbo.f_convMinOreArrot( dbo.f_calcMinStraor(minStra,minMpp) , ta.valint, 'D' ) AS h_straordinarie
,dbo.f_convMinOreArrot( dbo.f_calcMinPerm( minOrd, minLav, minStra, minMpp, minPerm) , ta.valint, 'E' ) AS h_permessi
,CASE -- h NON lavorati SE h LAVORATI < h ORDINARI
WHEN dbo.f_convMinOreArrot( dbo.f_calcMinLav(minLav,minStra,minMpp ), ta.valint, 'D' ) < dbo.f_convMinOreArrot( minOrd, ta.valint, 'E' )
THEN dbo.f_convMinOreArrot( minOrd, ta.valint, 'E' ) - dbo.f_convMinOreArrot( dbo.f_calcMinLav(minLav,minStra,minMpp ), ta.valint, 'D' )
ELSE 0
END AS h_NonLavorati
,dbo.f_convMinOreArrot( minFer, ta.valint, 'E' ) AS h_ferie
,dbo.f_convMinOreArrot( minMal, ta.valint, 'E' ) AS h_malattia
,dbo.f_convMinOreArrot( minFest, ta.valint, 'E' ) AS h_festivita
-- -- =======================================================================
-- -- CALCOLO MINUTI EFFETTIVI CON ARROTONDAMETO
-- -- =======================================================================
,dbo.f_convMinArrot( minOrd, ta.valint, 'E' ) AS mm_ordinari
-- uso funzioni per ritornare valori al netto della mancata pausa mensa
,dbo.f_convMinArrot( dbo.f_calcMinLav(minLav,minStra,minMpp ), ta.valint, 'D' ) AS mm_lavorati
,dbo.f_convMinArrot( dbo.f_calcMinStraor(minStra,minMpp) , ta.valint, 'D' ) AS mm_straordinari
,dbo.f_convMinArrot( dbo.f_calcMinPerm( minOrd, minLav, minStra, minMpp, minPerm) , ta.valint, 'E' ) AS mm_permessi
,CASE -- min LAVORATI < min ORDINARI
WHEN dbo.f_convMinArrot( dbo.f_calcMinLav(minLav,minStra,minMpp ), ta.valint, 'D' ) < dbo.f_convMinArrot( minOrd, ta.valint, 'E' )
THEN dbo.f_convMinArrot( minOrd, ta.valint, 'E' ) - dbo.f_convMinArrot( dbo.f_calcMinLav(minLav,minStra,minMpp ), ta.valint, 'D' )
ELSE 0
END AS mm_NonLavorati
,dbo.f_convMinArrot( minFer, ta.valint, 'E' ) AS mm_ferie
,dbo.f_convMinArrot( minMal, ta.valint, 'E' ) AS mm_malattia
,dbo.f_convMinArrot( minFest, ta.valint, 'E' ) AS mm_festivita
-- estraggo comunque tutti i campi in forma tabella
, te.minMpp
, te.minLav
, te.minOrd
, te.minNonLav
, te.minStra
, te.minPerm
, te.minFer
, te.minMal
, te.minFest
, te.minArcoPres
, te.isOkTim
, te.isOkApp
, te.isOkLav
, te.isOk
, te.block
, te.chkFunCod
, te.chkFunRes
, ta.valint AS arrotMin
, te.h_lav -- campi calcolati della tabella TimbratureExpl
, te.h_giust -- campi calcolati della tabella TimbratureExpl
-- , FLOOR( CAST( h_lav AS decimal( 9 , 3 )) * 60 / ta.valint ) / ( 60 / ta.valint ) AS h_lavorate
-- , CEILING( CAST( h_giust AS decimal( 9 , 3 )) * 60 / ta.valint ) / ( 60 / ta.valint ) AS h_giustificate
FROM dbo.TimbratureExpl AS te
INNER JOIN dbo.AnagKeyValue AS ta ON nomeVar = 'minutiRoundDay' -- recupero il valore per gli arrotondamenti giornalieri
go
commit;
go
set xact_abort on;
go
begin transaction;
go
set ANSI_NULLS on;
go
/**********************************************************
* STORED stp_prt_ReportOreDip
*
* stored per report ( ripresa e modificata la export.stp_ReportOreDip )
*
* mod: GC
*
**********************************************************/
create PROCEDURE export.stp_prt_ReportOreDip
(
@idxDipendente INT = 0, -- 0 = tutti
@dataFrom DATETIME,
@dataTo DATETIME,
@flgOnlyErr BIT
)
AS
SET NOCOUNT OFF;
DECLARE @minArr INT
SELECT @minArr = ISNULL(valInt,1) FROM AnagKeyValue WHERE nomeVar = 'minutiRoundDay'
SELECT dataLav
,CognomeNome
-- ,idxDipendente
,entrata_1 ,uscita_1 ,entrata_2,uscita_2,entrata_3,uscita_3,entrata_4,uscita_4
,h_ordinarie
,h_lavorate
,h_straordinarie
,h_permessi
,h_ferie
,h_malattia
,h_festivita
,h_NonLavorati
--,mm_ordinari
--,mm_lavorati
--,mm_NonLavorati
--,mm_straordinari
--,mm_permessi
--,mm_ferie
--,mm_malattia
--,mm_festivita
--,minMpp
--,minLav
--,minOrd
--,minNonLav
--,minStra
--,minPerm
--,minFer
--,minMal
--,minFest
--,minArcoPres
,isOkTim
,isOkApp
,isOkLav
,isOk
--,block
--,chkFunCod
--,chkFunRes
--,arrotMin
-- ,h_lav
-- ,h_giust
FROM dbo.v_OreTimbratureDipendente
WHERE (idxDipendente = @idxDipendente OR @idxDipendente = 0)
AND dataLav >= CAST(@dataFrom AS DATE)
AND dataLav <= CAST(@dataTo AS DATE)
AND ( @flgOnlyErr = 0 OR isOk = 0 ) -- Tutti se @flgOnlyErr = False o solo quelli in errore
ORDER BY CognomeNome, dataLav
/* OLD VIEW
SELECT dataLav, CognomeNome,
entrata_1, uscita_1, entrata_2, uscita_2,
entrata_3, uscita_3, entrata_4, uscita_4,
FLOOR(CAST(minOrd AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_Ord,
FLOOR(CAST(h_lav AS DECIMAL(9,3))*60/@minArr)/(60/@minArr) AS h_lavorate,
FLOOR(CAST(minStra AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_straordinarie,
CEILING(CAST(minPerm AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_permessi,
CEILING(CAST(minFer AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_ferie,
CEILING(CAST(minMal AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_malattia,
CEILING(CAST(minFest AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_festivita,
isOkTim ,
isOkApp ,
isOkLav ,
isOk
FROM TimbratureExpl
WHERE (idxDipendente = @idxDipendente OR @idxDipendente = 0)
AND dataLav >= CAST(@dataFrom AS DATE)
AND dataLav <= CAST(@dataTo AS DATE)
--AND NOT (entrata_1 IS NULL)
ORDER BY CognomeNome, dataLav
*/
go
-- =============================================
-- Author: G.Rottoli, S.E.Locatelli
-- Create date: 2013.03.12
-- Description: Export tracciato paghe x ETS
-- =============================================
create PROCEDURE export.stp_tracMensileETS
(
@Data DATE,
@gruppo NVARCHAR(4) = '', -- '' = tutti, altrimenti uno dei gruppi da anagrafica
@RifDitta NVARCHAR(3) = 'EG1', -- riferimento "interno": va letto da "gruppo" poiché cambia x ogni loro "sottosocietà": ETS, XPanding, Studio Associato, SNOB
@minArr INT = 1 -- Minuti di arrotondamento ( NON USARE lasciare a 1 )
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/*
MANCA GESTIONE EVENTI MALATTIE ( PARTE FINALE )
*/
/*
Gestione arrotondamenti a 15 min: doppiamente sbagliato!
- in primis è una variabile costante gestita come parametro "hard coded" e non si capisce fatto così (ed è un GRAVE errore)
- in secundis ETS arrotonda a 20 min e non a 15, quindi a maggior ragioen è sbagliato
GRAZIE PER L'INFORMAZIONE MA LO SAPEVO ANCHE IO! infatti era una bozza :-)
CREATO e corretto con gestione @minArr
*/
-- Setto sempre arrotondamento = 1 minuto perchè i valori adesso sono già arrotondati nella vista dbo.v_OreTimbratureDipendente
SET @minArr = 1
DECLARE @DataStart AS Date;
DECLARE @DataEnd AS Date;
DECLARE @nDip AS INT;
IF @Data IS NULL SET @Data = GETDATE(); -- se data passata è nulla setto a getdate
SET @DataStart = DATEADD(MONTH, DATEDIFF( MONTH, 0, @Data), 0)
SET @DataEnd = DATEADD(DAY, -1, DATEADD(MONTH, 1 ,@DataStart ) )
-- SELECT @DataStart , @DataEnd , @Data -- TEST
-- creo e carico variabile tabella per i dipendenti
DECLARE @TabDipendenti AS TABLE( Riga INT NOT NULL, idxDipendente INT NOT NULL, codDitta CHAR(4) NOT NULL)
-- Creo tabella x EXPORT
DECLARE @FinalExport AS TABLE( idxDipendente INT NOT NULL , Riga CHAR(2100) NOT NULL )
-- ================================================================
-- creo la tabella dei dipendenti da esportare e relativo codditta
-- ================================================================
INSERT @TabDipendenti
SELECT ROW_NUMBER() OVER ( ORDER BY idxDipendente ) , idxDipendente, codExt
FROM (
SELECT DISTINCT te.idxDipendente, ag.codExt
FROM dbo.v_OreTimbratureDipendente te
INNER JOIN Dipendenti d ON te.idxDipendente = d.idxDipendente
INNER JOIN AnagGruppi ag ON d.gruppo = ag.gruppo
WHERE te.dataLav >= @DataStart AND te.dataLav < @DataEnd
AND d.gruppo = CASE WHEN ISNULL(@gruppo,'') = '' THEN d.gruppo ELSE @gruppo END -- controllo gruppo!
AND ag.exportEnab = 1 -- gruppo attivo x export
) AS t
SET @nDip = ( SELECT COUNT(*) FROM @TabDipendenti )
-- SELECT * FROM @TabDipendenti -- TEST
-- =====================================================
-- creo la tabella temporanea con i dati che mi servono
-- =====================================================
-- ATTENZIONE CHE LA VISTA CONTIENE CAMPI GIA' CALCOLATI E ARROTONDATI
IF OBJECT_ID(N'tempdb..#expTable') IS NOT NULL DROP TABLE #expTable
SELECT -- Ore
t.idxDipendente
, d.matricola
, t.dataLav
, t.mm_ordinari AS minOrdExp -- minuti ordinari effettivi per export
, CASE WHEN t.mm_lavorati-t.mm_straordinari > 0
THEN (t.mm_lavorati-t.mm_straordinari)
ELSE 0
END AS minLavExp -- minuti Lavorati effettivi per export
, t.mm_straordinari AS minStraExp -- minuti straordinari effettivi per export
-- Giustificativi
, t.mm_permessi AS minPermExp
, t.mm_ferie AS minFerieExp
, t.minMal AS minMalExp
, t.mm_festivita AS minFestExp
-- parte iniziale Record
, RIGHT(dbo.f_expDataGGMMAAAA(dataLav),4)
+ SUBSTRING(dbo.f_expDataGGMMAAAA(dataLav),3,2)
+ ISNULL(@RifDitta,' ') -- Riferimento Ditta CHAR(3) ???
+ CONVERT(CHAR(4),ag.codExt) -- codice Ditta ( letto da AnagGruppi ) CHAR(4)
+ dbo.f_padLeft(d.matricola, 5, '0') + '0' -- 0 finale serve x doppio record x cambio qualifica ???
AS Record
INTO #expTable
FROM dbo.v_OreTimbratureDipendente AS t -- vista COMUNE con già le ore effettive calcolate
-- FROM dbo.TimbratureExpl AS t
INNER JOIN dbo.Dipendenti AS d ON t.idxDipendente = d.idxDipendente
INNER JOIN AnagGruppi ag ON d.gruppo = ag.gruppo
WHERE dataLav >= @DataStart AND dataLav < @DataEnd
AND ( d.gruppo = @gruppo OR ISNULL(@gruppo,'') = '' ) -- controllo gruppo! solo quello selezionato o tutti
AND ag.exportEnab = 1 -- gruppo attivo x export
-- SELECT * FROM #expTable -- TEST
-- Dichiarazioni per ciclo Dipendente / Data
DECLARE @RigaDip AS INT;
DECLARE @IdxDipendente AS INT;
DECLARE @CicloData AS DATE;
DECLARE @Stringa AS VARCHAR(4500);
DECLARE @minOrd AS INT;
DECLARE @minLav AS INT;
DECLARE @minStra AS INT;
DECLARE @chkFest AS VARCHAR(4);
DECLARE @minFerieExp AS INT;
DECLARE @minMalExp AS INT;
DECLARE @minFestExp AS INT;
DECLARE @minPermExp AS INT;
-- ciclo nella tabella in base al dipendente e poi alla data
SET @RigaDip = 1
WHILE @RigaDip <= @nDip
BEGIN
SET @IdxDipendente = ( SELECT idxDipendente FROM @TabDipendenti WHERE Riga = @RigaDip )
SET @CicloData = @DataStart
SET @Stringa = ( SELECT Record FROM #expTable WHERE idxDipendente = @IdxDipendente AND dataLav = @CicloData )
WHILE @CicloData <= @DataEnd
BEGIN
SET @minOrd = NULL; -- setto a NULL per verificare dopo se non ho trovato record x il giorno
SELECT @minOrd = minOrdExp -- min Teorici
, @minLav = minLavExp -- min lavorati ( senza straordinari )
, @minStra = minStraExp -- min straordinari
, @chkFest = dbo.f_expchkFest( @CicloData , @IdxDipendente )
, @minFerieExp = minFerieExp
, @minMalExp = minMalExp
, @minFestExp = minFestExp
, @minPermExp = minPermExp
FROM #expTable WHERE idxDipendente = @IdxDipendente AND dataLav = @CicloData
IF @minOrd IS NULL -- se record non trovato o il giorno non esiste
SET @Stringa = @Stringa + REPLICATE( ' ', 52 ) --- metto a blank i dati del giorno
ELSE
BEGIN
-- ==================================================
-- Ore TEORICHE ( da sistemare se festività o non previste ) FARE FUNZIONE
-- ==================================================
IF @chkFest <> '' -- Se è Sabato o Festività
SET @Stringa = @Stringa + @chkFest
ELSE IF @minOrd = 0 -- se min Teorici = 0 e non è una Fest/Sab allora è gg Partime Verticale
SET @Stringa = @Stringa + '****'
ELSE
SET @Stringa = @Stringa + ISNULL( dbo.f_expTimeHHMM( @minOrd, 'M', @minArr ) ,'0000')
-- ==================================================
-- Ore LAVORATE ( se Malattia o Infortunio va cambiato ) ( da sistemare se non previste Part Time Verticale ) FARE FUNZIONE
-- ==================================================
IF @chkFest <> '' -- Se è Sabato o Festività
SET @Stringa = @Stringa + @chkFest
ELSE IF @minOrd = 0 -- se min Teorici = 0 e non è una Fest/Sab allora è gg Partime Verticale
SET @Stringa = @Stringa + '****'
ELSE
SET @Stringa = @Stringa + ISNULL( dbo.f_expTimeHHMM( @minLav, 'M', @minArr ) ,'0000')
-- ==================================================
-- Ore STRAORDINARIE ( da sistemare se festività o non previste )
-- ==================================================
IF @minStra < @minArr -- se minore di arrotondamento metto blank
SET @Stringa = @Stringa + REPLICATE(' ',4)
ELSE
SET @Stringa = @Stringa + ISNULL( dbo.f_expTimeHHMM( @minStra , 'M', @minArr ) ,' ')
-- ==================================================
-- Ore GIUSTIFICATIVI ( MAX 5 GIUSTIFICATIVI )
-- ==================================================
DECLARE @Giust AS VARCHAR(40) = '';
IF @minPermExp >= @minArr
SET @Giust = @Giust + 'PERM' + ISNULL( dbo.f_expTimeHHMM( @minPermExp , 'M', @minArr ) ,' ')
IF @minFerieExp >= @minArr
SET @Giust = @Giust + 'FER ' + ISNULL( dbo.f_expTimeHHMM( @minFerieExp , 'M', @minArr ) ,' ')
IF @minMalExp >= @minArr
SET @Giust = @Giust + 'MAL ' + ISNULL( dbo.f_expTimeHHMM( @minMalExp , 'M', @minArr ) ,' ')
IF @minFestExp >= @minArr
SET @Giust = @Giust + 'FEST' + ISNULL( dbo.f_expTimeHHMM( @minFestExp , 'M', @minArr ) ,' ')
-- aggiungo spazi x 5 giustificati ( il 5o non è presente ( MATERNITA ? )
SET @Stringa = @Stringa + @Giust + REPLICATE(' ',40 - LEN(@Giust))
END
SET @CicloData = DATEADD(DAY, 1, @CicloData)
END
-- se mese inferiore a 31 GG aggiungo spazi x i gg mancanti
SET @Stringa = @Stringa + (SELECT REPLICATE(' ' , (31-DATEPART(D,(DATEADD(DAY, -1, @CicloData)))) * 52 ))+ 'FILLER';
-- Inserisco Record dipendente
INSERT @FinalExport
SELECT @IdxDipendente , @Stringa
SET @RigaDip = @RigaDip + 1
END
SELECT * FROM @FinalExport
IF OBJECT_ID(N'tempdb..#expTable') IS NOT NULL DROP TABLE #expTable
/*
MIO RISULTATO
201303EG10397000040080010250225 ======== ++++++++ 080010250225 080010000200 080008750075 080008500050 080009000100 ======== ++++++++ 080009500150 080009750175 080009250125 080009250125 080010000200 ======== ++++++++ 080009750175 080009000100 080008250025 080008750075 080009500150 ======== ++++++++ 080009750175 080009000100 080008750075 080009000100 080009500150 ======== ++++++++
LORO TEST
201303EG1017500001008000800 ======== ++++++++ 08000800 08000800 08000800 08000800 08000800 ======== ++++++++ 08000800 08000800 08000800 08000800 08000800 ======== ++++++++ 08000800 08000800 FSE 0800 08000800 08000800 08000800 ======== ++++++++ 08000800 08000800 08000800 08000800 08000400 F 0400 ======== ++++++++ 000000 000000
*/
END
go
alter PROCEDURE stp_giust_insByDate
(
@idxDipendente INT = 0,
@dataRif DATETIME,
@codGiust NVARCHAR(5) = 'PERM'
)
AS
-- =============================================
-- Description: QUANDO VIENE USATA ???
-- SE c'è anomalia MPP entra sempre in gestione anomalia MPP per prima cosa
-- =============================================
DECLARE @minPerm INT = 0 -- min necessari x chiudere giornata
DECLARE @isPP BIT = 0
SELECT @isPP = CASE WHEN ISNULL(chkFunCod,'')='P' THEN 1 ELSE 0 END
FROM TimbratureExpl WHERE idxDipendente=@idxDipendente AND dataLav=@dataRif
BEGIN TRAN -- ???? MA SERVE ???
IF(@isPP=1) -- se carico mancata pausa pranzo
BEGIN
-- recupero tipo di fuction da usare
DECLARE @chkFun NVARCHAR(50) = ''
DECLARE @minPP INT = 0
-- imposto variabili
SELECT @chkFun = dbo.f_chkFunDip(@idxDipendente), @codGiust = 'MPP' -- mancata pausa pranzo
-- controllo tipo di funzione... e imposto risultato!
/*
IF(@chkFun = 'PP60m')
BEGIN
SET @minPP = 60
END
ELSE IF(@chkFun = 'PP30m')
BEGIN
SET @minPP = 30
END
*/
IF(@chkFun LIKE 'PP%m')
BEGIN
SELECT @minPP=REPLACE(REPLACE(@chkFun,'PP',''),'m','')
END
-- calcolo quota permesso
SELECT @minPerm = @minPP - (minArcoPres - minLav )
FROM TimbratureExpl
WHERE idxDipendente = @idxDipendente AND dataLav = @dataRif
-- INSERT giustificativo mancata pausa pranzo
INSERT INTO Giustificativi
VALUES (@dataRif,@idxDipendente,@codGiust,@minPerm,1)
END
ELSE
BEGIN
-- variabili
DECLARE @minArr INT
--SELECT @minArr=dbo.f_minArrotDip(@idxDipendente)
SELECT @minArr = ISNULL(valInt,1) FROM AnagKeyValue WHERE nomeVar = 'minutiRoundDay'
-- calcolo minuti
SELECT @minPerm = (CEILING(CONVERT(FLOAT,minOrd-minLav)/@minArr)*@minArr)
FROM TimbratureExpl
WHERE isOkLav = 0
AND idxDipendente = @idxDipendente
AND dataLav = @dataRif
-- INSERT giustificativo!
INSERT INTO Giustificativi
VALUES (@dataRif,@idxDipendente,@codGiust,@minPerm,1)
END
COMMIT TRAN
RETURN
go
alter PROCEDURE stp_ricalcolaTimbratureExpl
(
@idxDipendente AS INT,
@dataRif AS DATETIME
)
AS
-- =============================================
-- Description: ??? QUANDO VIENE USATA ??? SE c'è anomalia MPP entra sempre in gestione anomalia MPP???
-- =============================================
-- METTERE IN TRANSAZIONE ?!?!?!? richiamata nei trigger se solo li quindi è già in transazione
-- variabili
DECLARE @minArr INT
SELECT @minArr=dbo.f_minArrotDip(@idxDipendente)
-- CONTROLLO SE IL RECORD NON SIA BLOCCATO!!!
DECLARE @block BIT
SET @block = ( SELECT ISNULL(( SELECT block FROM TimbratureExpl WHERE (idxDipendente = @idxDipendente) AND CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif) ),0) )
IF(@block = 0)
BEGIN
-- elimino i periodi della data indicata...
DELETE FROM TimbratureExpl
WHERE (idxDipendente = @idxDipendente) AND CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)
-- inserisco i dati minimi per la data indicata
INSERT INTO TimbratureExpl(idxDipendente, dataLav)
VALUES(@idxDipendente, CONVERT(DATE,@dataRif))
-- aggiorno cognome nome
BEGIN TRY
UPDATE te
SET CognomeNome = ISNULL(vcn.CognomeNome,'')
FROM TimbratureExpl te INNER JOIN v_cognomeNome vcn ON te.idxDipendente = vcn.idxDipendente
WHERE CONVERT(DATE,te.dataLav) = CONVERT(DATE,@dataRif) AND te.idxDipendente = @idxDipendente
END TRY
BEGIN CATCH
-- non faccio nulla
END CATCH
-- aggiorno 1° timbratura
UPDATE te
SET te.entrata_1 = pl.entrata,
te.uscita_1 = pl.uscita
FROM TimbratureExpl te
INNER JOIN PeriodiLav pl
ON te.idxDipendente = pl.idxDipendente AND (CONVERT(DATE,te.dataLav) = CONVERT(DATE,pl.entrata))
WHERE pl.rowNum = 1 AND te.idxDipendente = @idxDipendente AND CONVERT(DATE,pl.entrata) = CONVERT(DATE,@dataRif)
-- aggiorno 2° timbratura
UPDATE te
SET te.entrata_2 = pl.entrata,
te.uscita_2 = pl.uscita
FROM TimbratureExpl te
INNER JOIN PeriodiLav pl
ON te.idxDipendente = pl.idxDipendente AND (CONVERT(DATE,te.dataLav) = CONVERT(DATE,pl.entrata))
WHERE pl.rowNum = 2 AND te.idxDipendente = @idxDipendente AND CONVERT(DATE,pl.entrata) = CONVERT(DATE,@dataRif)
-- aggiorno 3° timbratura
UPDATE te
SET te.entrata_3 = pl.entrata,
te.uscita_3 = pl.uscita
FROM TimbratureExpl te INNER JOIN PeriodiLav pl ON te.idxDipendente = pl.idxDipendente
AND (CONVERT(DATE,te.dataLav) =CONVERT(DATE,pl.entrata))
WHERE pl.rowNum = 3 AND te.idxDipendente = @idxDipendente AND CONVERT(DATE,pl.entrata) = CONVERT(DATE,@dataRif)
-- aggiorno 4° timbratura
UPDATE te
SET te.entrata_4 = pl.entrata,
te.uscita_4 = pl.uscita
FROM TimbratureExpl te INNER JOIN PeriodiLav pl ON te.idxDipendente = pl.idxDipendente
AND (CONVERT(DATE,te.dataLav) =CONVERT(DATE,pl.entrata))
WHERE pl.rowNum = 4 AND te.idxDipendente = @idxDipendente AND CONVERT(DATE,pl.entrata) = CONVERT(DATE,@dataRif)
-- ==========================================================
-- controllo e aggiorno flag
-- ==========================================================
UPDATE TimbratureExpl
-- verifico: se c'è anche solo 1 timbratura non approvata metto flag isOkApp a falso
SET isOkApp = ISNULL((
SELECT MIN(CASE WHEN Approv=0 THEN 0 ELSE 1 END) AS isOk
FROM Timbrature
WHERE idxDipendente = @idxDipendente AND (CONVERT(DATE,dataOra) = CONVERT(DATE,@dataRif))
GROUP BY CONVERT(DATE,dataOra)
),1),
-- verifico: se entrate <> uscite flag isOkTim a falso
isOkTim = (
SELECT CASE WHEN
ISNULL( (SELECT COUNT(*) as timb
FROM Timbrature AS t
WHERE t.idxDipendente = @idxDipendente AND (CONVERT(DATE,dataOra) = CONVERT(DATE,@dataRif))
GROUP BY t.idxDipendente , CONVERT(DATE,t.dataOra)
HAVING COUNT ( CASE Entrata WHEN 1 THEN dataOra END ) <> COUNT ( CASE Entrata WHEN 0 THEN dataOra END )
),0 ) > 0
THEN 0
ELSE 1 END
)
FROM TimbratureExpl
WHERE (CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente
/*
-- verifico: se c'è anche solo 1 timbratura non approvata metto flag isOkApp a falso
UPDATE TimbratureExpl
SET isOkApp = ISNULL((
SELECT MIN(CASE WHEN Approv=0 THEN 0 ELSE 1 END) AS isOk
FROM Timbrature
WHERE idxDipendente = @idxDipendente
AND (CONVERT(DATE,dataOra) = CONVERT(DATE,@dataRif))
GROUP BY CONVERT(DATE,dataOra)
),1)
FROM TimbratureExpl
WHERE (CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente
-- verifico: se entrate <> uscite flag isOkTim a falso
UPDATE TimbratureExpl
SET isOkTim = (
SELECT CASE WHEN
ISNULL(
(SELECT COUNT(*) as timb
FROM Timbrature AS t
WHERE t.idxDipendente = @idxDipendente
AND (CONVERT(DATE,dataOra) = CONVERT(DATE,@dataRif))
GROUP BY t.idxDipendente , CONVERT(DATE,t.dataOra)
HAVING COUNT ( CASE Entrata WHEN 1 THEN dataOra END ) <> COUNT ( CASE Entrata WHEN 0 THEN dataOra END )
),0) > 0 THEN 0 ELSE 1 END
)
FROM TimbratureExpl
WHERE (CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente
*/
-- ==========================================================
-- aggiornamento orari dai Giustificativi
-- ==========================================================
-- MIN da tab giustificativi (feste, ferie, malattia, permessi)
UPDATE TimbratureExpl
SET minMal = CASE WHEN g.codGiust IN('MAL','MAT','INF') THEN g.minuti ELSE minMal END,
minFer = CASE WHEN g.codGiust='FER' THEN g.minuti ELSE minFer END,
minFest = CASE WHEN g.codGiust='FEST' THEN g.minuti ELSE minFest END,
minPerm = CASE WHEN g.codGiust='PERM' THEN g.minuti ELSE minPerm END,
minMPP = CASE WHEN g.codGiust='MPP' THEN g.minuti ELSE minMPP END, -- mancata pausa pranzo
chkFunCod = '',
chkFunRes = ''
FROM TimbratureExpl te INNER JOIN Giustificativi g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav
WHERE (CONVERT(DATE,te.dataLav) = CONVERT(DATE,@dataRif)) AND te.idxDipendente = @idxDipendente
-- ==========================================================
-- aggiornamento ORARI
-- ==========================================================
-- calcolo resoconto MINUTI giornaliero, imposto LUN x inizio settimana
SET DATEFIRST 1
-- ORE Lav ...
UPDATE TimbratureExpl
SET h_lav = CAST(FLOOR((ISNULL(DATEDIFF(n,entrata_1,uscita_1),0) + ISNULL(DATEDIFF(n,entrata_2,uscita_2),0) + ISNULL(DATEDIFF(n,entrata_3,uscita_3),0) + ISNULL(DATEDIFF(n,entrata_4,uscita_4),0))/@minArr) AS FLOAT)*@minArr/60
FROM TimbratureExpl
WHERE (CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente
-- MIN lavorati, ordinari (con arrotondamento...)
UPDATE TimbratureExpl
SET minLav = CAST((ISNULL(DATEDIFF(n,entrata_1,uscita_1),0) + ISNULL(DATEDIFF(n,entrata_2,uscita_2),0) + ISNULL(DATEDIFF(n,entrata_3,uscita_3),0) + ISNULL(DATEDIFF(n,entrata_4,uscita_4),0)) AS FLOAT)
,minOrd = dbo.f_oreOrdDip(idxDipendente,dataLav) * 60 -- da schema orario dip attivo
FROM TimbratureExpl
WHERE (CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente
-- MIN non lavorati, straordinari (con arrotondamento giornaliero...) ( NON tolgo eventuali minuti di MPP mancata pausa pranzo )
UPDATE TimbratureExpl
SET minNonLav = (CEILING(CONVERT(FLOAT,CASE WHEN minOrd <= (minLav) THEN (0) ELSE minOrd-minLav END)/@minArr)*@minArr)
,minStra = (FLOOR( CONVERT(FLOAT,CASE WHEN minOrd < (minLav) AND minLav > (0) THEN minLav-minOrd ELSE (0) END)/@minArr)*@minArr)
FROM TimbratureExpl
WHERE (CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente
-- verifico SE codice orario richieda una chkFun eseguo calcolo...
IF(dbo.f_chkFunDip(@idxDipendente)<>'')
BEGIN
EXEC stp_TE_doChkFun @idxDipendente, @dataRif
END
END
RETURN
go
/*******************************************************************************
* STORED PROCEDURE stp_TE_doChkFun
*
* effettua check finali sulla riga di timbrature esplore indicata, contiene varie logiche
* anche da + installazioni clienti
*
* mod: S.E.L. 2013.03.28
*
*******************************************************************************/
alter PROCEDURE stp_TE_doChkFun
(
@idxDipendente INT,
@dataRif DATETIME
)
AS
SET NOCOUNT ON;
-- recupero tipo di fuction da usare
DECLARE @chkFun NVARCHAR(50) = ''
DECLARE @chkFunCod NVARCHAR(50) = ''
DECLARE @chkFunRes NVARCHAR(50) = ''
DECLARE @minPP INT = 0
SELECT @chkFun = dbo.f_chkFunDip(@idxDipendente)
-- controllo tipo di funzione... e imposto risultato!
/*
IF(@chkFun = 'PP60m')
BEGIN
SELECT @minPP = 60, @chkFunCod = 'P', @chkFunRes = 'Mancata pausa pranzo 60m'
END
ELSE IF(@chkFun = 'PP30m')
BEGIN
SELECT @minPP = 30, @chkFunCod = 'P', @chkFunRes = 'Mancata pausa pranzo 30m'
END
*/
IF(@chkFun LIKE 'PP%m')
BEGIN
SELECT @minPP=REPLACE(REPLACE(@chkFun,'PP',''),'m',''), @chkFunCod = 'P'
SELECT @chkFunRes = 'Mancata pausa pranzo ' + CAST(@minPP AS NVARCHAR) + 'm'
END
-- se c'è check da fare faccio COMUNQUE update!
-- calcolo che tra il totale minuti 1a timbratura e ultima della girnata ci deve essere almeno un pausa = minuti pausa prevista
IF(@chkFun <>'')
BEGIN
UPDATE TimbratureExpl
SET chkFunCod = CASE WHEN (minArcoPres - minLav ) < @minPP - minMpp THEN @chkFunCod ELSE '' END, -- chk pause > PP imposta (- minuti permesso caricati)
chkFunRes = CASE WHEN (minArcoPres - minLav) < @minPP - minMpp THEN @chkFunRes ELSE '' END -- chk pause > PP imposta (- minuti permesso caricati)
WHERE idxDipendente = @idxDipendente AND dataLav = @dataRif
AND (minOrd > 0 AND minLav > 240) -- controllo DEBBA lavorare e ABBIA lavorato ALMENO 4 ore!!!
AND block = 0 -- controllo NON sia bloccato
END
RETURN
go
/**********************************************************
* STORED stp_lcuByDipDate
*
* elenco dati COMPLETI di timbratura e commessa per utente/periodo
*
* mod: S.E.L. 2012.11.02
*
**********************************************************/
alter PROCEDURE stp_TE_RA_ByUserDate
(
@idxDipendente INT,
@dataFrom DATETIME,
@dataTo DATETIME,
@showWE BIT = 1, -- imposto visualizzazione week-end a true di default
@maxErrMin INT = 0, -- minuti massimi di scostamento consentiti per DIFETTO, default 0
@maxErrPlus INT = 0 -- minuti massimi di scostamento consentiti per ECCESSO, default 0
)
with recompile
AS
BEGIN
-- imposto LUN x inizio settimana
SET DATEFIRST 1
-- faccio una tab temporanea con tallyTable x calcolo periodo intero...
DECLARE @numD INT
SET @numD = ( SELECT DATEDIFF(dd, @dataFrom, @dataTo ) )
-- tab dei record RegAttività progetti
;WITH myCteRA AS
(
SELECT idxDipendente, dataLav, minRegAtt, descrProj
FROM dbo.RegAttivitaExpl
WHERE (idxDipendente = @idxDipendente OR @idxDipendente = 0) AND (dataLav >= DATEADD(DAY,-1,@dataFrom) AND dataLav <= @dataTo)
)
SELECT mc.Data as dataLav
,mc.idxDipendente
,ISNULL(dip.Cognome + ' ' + dip.Nome,'') AS CognomeNome
-- ore base + progetti
,ISNULL(te.h_lav,0) AS h_lav
,ISNULL(te.h_giust,0) AS h_giust
,CAST(ISNULL(vra.minRegAtt,0) AS DECIMAL(9,3))/60 AS h_com
,ISNULL(vra.descrProj,'') as progetti
-- check coerenza
,CASE WHEN ISNULL(vra.minRegAtt,0)-ISNULL(te.h_lav,0)*60 >= @maxErrMin AND ISNULL(vra.minRegAtt,0)-ISNULL(te.h_lav,0)*60 <= @maxErrPlus THEN 1 ELSE 0 END AS okLavCom
,ISNULL(te.isOkTim,0) AS isOkTim
,ISNULL(te.isOkApp,0) AS isOkApp
,ISNULL(te.isOkLav,0) AS isOkLav
,ISNULL(te.isOk,0) AS isOk
-- check blocco
,ISNULL(te.block,0) AS block
-- dettaglio minuti
,ISNULL(te.minOrd,0) AS minOrd
,ISNULL(te.minNonLav,0) AS minNonLav
,ISNULL(te.minStra,0) AS minStra
,ISNULL(te.minPerm,0) AS minPerm
,ISNULL(te.minFer,0) AS minFer
,ISNULL(te.minMal,0) AS minMal
,ISNULL(te.minFest,0) AS minFest
,ISNULL(te.minMpp,0) AS minMpp
,ISNULL(te.minArcoPres,0) AS minArcoPres
,ISNULL(vra.minRegAtt,0) AS minRegAtt
-- dettaglio entrate
, te.entrata_1, te.uscita_1
, te.entrata_2, te.uscita_2
, te.entrata_3, te.uscita_3
, te.entrata_4, te.uscita_4
-- dati check
, te.chkFunCod, te.chkFunRes
FROM (SELECT CONVERT(DATE,DATEADD(dd, -N, @dataTo)) as Data, idxDipendente
FROM Tally, Dipendenti
WHERE N <= @numD
AND (idxDipendente = @idxDipendente OR @idxDipendente = 0)
) AS mc
LEFT OUTER JOIN myCteRA vra ON mc.Data = vra.dataLav AND mc.idxDipendente=vra.idxDipendente
LEFT OUTER JOIN TimbratureExpl te ON mc.Data=te.dataLav AND te.idxDipendente=mc.idxDipendente
LEFT OUTER JOIN Dipendenti dip ON dip.idxDipendente=mc.idxDipendente
WHERE (te.idxDipendente = @idxDipendente OR @idxDipendente = 0)
AND DATEPART(dw, mc.Data) <= CASE WHEN @showWE=0 THEN 5 ELSE 7 END --solo lun-ven, 1-5
ORDER BY mc.Data DESC, dip.Cognome, dip.Nome
END
RETURN
go
commit;
go
set xact_abort on
go
begin transaction
go
INSERT INTO dbo.AnagGiust
VALUES (N'MPP', N'Mancata pausa pranzo', 1)
go
commit transaction
go
set ANSI_NULLS on;
go
create FUNCTION f_chkFunDipMin
(
@idxDipendente INT = 0,
@dataRif DATETIME,
@codGiust NVARCHAR(5) = 'MPP' -- non usato
)
RETURNS INT
AS
BEGIN
-- =============================================
-- Description: CREATA PER VERIFICA MPP DOPO AGGIORNAMENTO
-- Ritorna gli eventuali minuti di mancata pausa mensa
-- =============================================
DECLARE @minPerm INT = 0 -- min necessari x chiudere giornata
BEGIN
-- recupero tipo di fuction da usare
DECLARE @chkFun NVARCHAR(50) = ''
DECLARE @minPP INT = 0
-- imposto variabili
SELECT @chkFun = dbo.f_chkFunDip(@idxDipendente), @codGiust = 'MPP' -- mancata pausa pranzo
IF(@chkFun LIKE 'PP%m')
BEGIN
SELECT @minPP=REPLACE(REPLACE(@chkFun,'PP',''),'m','')
END
-- calcolo quota permesso MPP da ritornare
SELECT @minPerm = @minPP - ( minArcoPres - minLav )
FROM TimbratureExpl
WHERE idxDipendente = @idxDipendente AND dataLav = @dataRif
END
RETURN @minPerm
END
go
-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(329, GETDATE())
GO
SELECT * FROM LogUpdateDb ORDER BY Versione DESC
Reference in New Issue View Git Blame Copy Permalink