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