marco
866 lines
26 KiB
Transact-SQL
866 lines
26 KiB
Transact-SQL
create table AnagTipoCheck(
|
|
chkFun nvarchar(50) not null constraint PK_AnagTipoCheck primary key,
|
|
descrFun nvarchar(50)
|
|
);
|
|
go
|
|
|
|
|
|
set xact_abort on
|
|
go
|
|
|
|
begin transaction
|
|
go
|
|
|
|
INSERT INTO dbo.AnagTipoCheck
|
|
VALUES (N'PP30m', N'Pausa pranzo 30 min')
|
|
INSERT INTO dbo.AnagTipoCheck
|
|
VALUES (N'PP60m', N'Pausa pranzo 60 min')
|
|
go
|
|
|
|
commit transaction
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
alter table AnagOrari add
|
|
chkFun nvarchar(50) constraint DF_AnagOrari_checkFun default ('');
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_Description', 'parametro (opzionale) da passare alla funzione di check "finale" per verifica giornate lavorate in tab TimbratureExpl', 'SCHEMA', 'dbo', 'TABLE', 'AnagOrari', 'COLUMN', 'chkFun';
|
|
go
|
|
|
|
update AnagOrari set chkFun=('');
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
/******************************************************************************
|
|
* FUNCTION f_chkFunDip
|
|
*
|
|
* restituisce il nome della funzione di check da applicare dato profilo orario
|
|
*
|
|
* mod: S.E.L. 2013.03.28
|
|
*
|
|
*******************************************************************************/
|
|
create FUNCTION f_chkFunDip
|
|
(
|
|
@idxDipendente INT
|
|
)
|
|
RETURNS NVARCHAR(50)
|
|
AS
|
|
|
|
BEGIN
|
|
|
|
DECLARE @chkFun NVARCHAR(50) = ''
|
|
|
|
|
|
SELECT @chkFun = ISNULL(chkFun,'')
|
|
FROM AnagOrari ao INNER JOIN Dipendenti d ON ao.codOrario=d.codOrario
|
|
WHERE d.idxDipendente = @idxDipendente
|
|
|
|
RETURN @chkFun
|
|
|
|
END
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
alter table TimbratureExpl drop
|
|
constraint DF_TimbratureExpl_minOrd ,
|
|
constraint DF_TimbratureExpl_minLav ,
|
|
constraint DF_TimbratureExpl_minStra ,
|
|
constraint DF_TimbratureExpl_minNonLav ,
|
|
constraint DF_TimbratureExpl_minFest ,
|
|
constraint DF_TimbratureExpl_minPerm ,
|
|
constraint DF_TimbratureExpl_minFer ,
|
|
constraint DF_TimbratureExpl_minFest1 ,
|
|
constraint DF_TimbratureExpl_block ,
|
|
constraint DF_TimbratureExpl_isOkTim ,
|
|
constraint DF_TimbratureExpl_isOk ;
|
|
go
|
|
|
|
exec sp_rename 'PK_TimbratureExpl_1', 'tmp__PK_TimbratureExpl_1', 'OBJECT';
|
|
go
|
|
|
|
exec sp_rename 'TimbratureExpl', 'tmp__TimbratureExpl_2', '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])/(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 constraint DF_TimbratureExpl_minFer default ((0)),
|
|
minMal int constraint DF_TimbratureExpl_minFest1 default ((0)),
|
|
minFest int constraint DF_TimbratureExpl_minFest 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_chkFunRes default (''),
|
|
chkFunRes nvarchar(50) constraint DF_TimbratureExpl_chkFunRes1 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) 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 from tmp__TimbratureExpl_2;
|
|
go
|
|
|
|
drop table tmp__TimbratureExpl_2;
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
create VIEW v_selCodOrario
|
|
AS
|
|
SELECT codOrario AS value, codOrario AS label
|
|
FROM dbo.AnagOrari
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPane1', '[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
|
|
Begin DesignProperties =
|
|
Begin PaneConfigurations =
|
|
Begin PaneConfiguration = 0
|
|
NumPanes = 4
|
|
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
|
|
End
|
|
Begin PaneConfiguration = 1
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 4 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 2
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 2 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 3
|
|
NumPanes = 3
|
|
Configuration = "(H (4 [30] 2 [40] 3))"
|
|
End
|
|
Begin PaneConfiguration = 4
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [56] 3))"
|
|
End
|
|
Begin PaneConfiguration = 5
|
|
NumPanes = 2
|
|
Configuration = "(H (2 [66] 3))"
|
|
End
|
|
Begin PaneConfiguration = 6
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [50] 3))"
|
|
End
|
|
Begin PaneConfiguration = 7
|
|
NumPanes = 1
|
|
Configuration = "(V (3))"
|
|
End
|
|
Begin PaneConfiguration = 8
|
|
NumPanes = 3
|
|
Configuration = "(H (1[56] 4[18] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 9
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [75] 4))"
|
|
End
|
|
Begin PaneConfiguration = 10
|
|
NumPanes = 2
|
|
Configuration = "(H (1[66] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 11
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [60] 2))"
|
|
End
|
|
Begin PaneConfiguration = 12
|
|
NumPanes = 1
|
|
Configuration = "(H (1) )"
|
|
End
|
|
Begin PaneConfiguration = 13
|
|
NumPanes = 1
|
|
Configuration = "(V (4))"
|
|
End
|
|
Begin PaneConfiguration = 14
|
|
NumPanes = 1
|
|
Configuration = "(V (2))"
|
|
End
|
|
ActivePaneConfig = 0
|
|
End
|
|
Begin DiagramPane =
|
|
Begin Origin =
|
|
Top = 0
|
|
Left = 0
|
|
End
|
|
Begin Tables =
|
|
Begin Table = "AnagOrari"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 200
|
|
Right = 223
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 3810
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 11
|
|
Column = 1440
|
|
Alias = 900
|
|
Table = 1170
|
|
Output = 720
|
|
Append = 1400
|
|
NewValue = 1170
|
|
SortType = 1350
|
|
SortOrder = 1410
|
|
GroupBy = 1350
|
|
Filter = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
End
|
|
End
|
|
End
|
|
', 'SCHEMA', 'dbo', 'VIEW', 'v_selCodOrario';
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_selCodOrario';
|
|
go
|
|
|
|
create VIEW v_selTipoChk
|
|
AS
|
|
SELECT chkFun AS value, descrFun AS label
|
|
FROM dbo.AnagTipoCheck
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPane1', '[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
|
|
Begin DesignProperties =
|
|
Begin PaneConfigurations =
|
|
Begin PaneConfiguration = 0
|
|
NumPanes = 4
|
|
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
|
|
End
|
|
Begin PaneConfiguration = 1
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 4 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 2
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 2 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 3
|
|
NumPanes = 3
|
|
Configuration = "(H (4 [30] 2 [40] 3))"
|
|
End
|
|
Begin PaneConfiguration = 4
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [56] 3))"
|
|
End
|
|
Begin PaneConfiguration = 5
|
|
NumPanes = 2
|
|
Configuration = "(H (2 [66] 3))"
|
|
End
|
|
Begin PaneConfiguration = 6
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [50] 3))"
|
|
End
|
|
Begin PaneConfiguration = 7
|
|
NumPanes = 1
|
|
Configuration = "(V (3))"
|
|
End
|
|
Begin PaneConfiguration = 8
|
|
NumPanes = 3
|
|
Configuration = "(H (1[56] 4[18] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 9
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [75] 4))"
|
|
End
|
|
Begin PaneConfiguration = 10
|
|
NumPanes = 2
|
|
Configuration = "(H (1[66] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 11
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [60] 2))"
|
|
End
|
|
Begin PaneConfiguration = 12
|
|
NumPanes = 1
|
|
Configuration = "(H (1) )"
|
|
End
|
|
Begin PaneConfiguration = 13
|
|
NumPanes = 1
|
|
Configuration = "(V (4))"
|
|
End
|
|
Begin PaneConfiguration = 14
|
|
NumPanes = 1
|
|
Configuration = "(V (2))"
|
|
End
|
|
ActivePaneConfig = 0
|
|
End
|
|
Begin DiagramPane =
|
|
Begin Origin =
|
|
Top = 0
|
|
Left = 0
|
|
End
|
|
Begin Tables =
|
|
Begin Table = "AnagTipoCheck"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 102
|
|
Right = 208
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 1755
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 11
|
|
Column = 1440
|
|
Alias = 900
|
|
Table = 1170
|
|
Output = 720
|
|
Append = 1400
|
|
NewValue = 1170
|
|
SortType = 1350
|
|
SortOrder = 1410
|
|
GroupBy = 1350
|
|
Filter = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
End
|
|
End
|
|
End
|
|
', 'SCHEMA', 'dbo', 'VIEW', 'v_selTipoChk';
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_selTipoChk';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AD_insertQuery
|
|
*
|
|
* aggiunge un dipendente in anagrafica
|
|
*
|
|
* mod: S.E.L. 2012.10.29
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AD_insertQuery
|
|
(
|
|
@matricola NVARCHAR(50),
|
|
@CF NVARCHAR(16),
|
|
@Cognome NVARCHAR(50),
|
|
@Nome NVARCHAR(50),
|
|
@dataNascita DATETIME,
|
|
@luogoNascita NVARCHAR(50),
|
|
@provNascita NVARCHAR(50),
|
|
@nazNascita NVARCHAR(50),
|
|
@email NVARCHAR(250),
|
|
@dominio NVARCHAR(50),
|
|
@utente NVARCHAR(50),
|
|
@codOrario NVARCHAR(50),
|
|
@attivo BIT
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
INSERT INTO Dipendenti
|
|
(matricola, CF, Cognome, Nome, dataNascita, luogoNascita, provNascita, nazNascita, email, dominio, utente,codOrario,attivo)
|
|
VALUES (@matricola,@CF,@Cognome,@Nome,@dataNascita,@luogoNascita,UPPER(@provNascita),UPPER(@nazNascita),@email,@dominio,@utente,@codOrario,@attivo);
|
|
|
|
SELECT * FROM Dipendenti WHERE (idxDipendente = SCOPE_IDENTITY())
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AD_updateQuery
|
|
*
|
|
* aggiorna un dipendente da anagrafica
|
|
*
|
|
* mod: S.E.L. 2012.10.29
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AD_updateQuery
|
|
(
|
|
@matricola NVARCHAR(50),
|
|
@CF NVARCHAR(16),
|
|
@Cognome NVARCHAR(50),
|
|
@Nome NVARCHAR(50),
|
|
@dataNascita datetime,
|
|
@luogoNascita NVARCHAR(50),
|
|
@provNascita NVARCHAR(50),
|
|
@nazNascita NVARCHAR(50),
|
|
@email NVARCHAR(250),
|
|
@dominio NVARCHAR(50),
|
|
@utente NVARCHAR(50),
|
|
@codOrario NVARCHAR(50),
|
|
@attivo BIT,
|
|
@Original_idxDipendente int
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
UPDATE Dipendenti
|
|
SET matricola = @matricola, CF = @CF, Cognome = @Cognome, Nome = @Nome, dataNascita = @dataNascita, luogoNascita = @luogoNascita,
|
|
provNascita = UPPER(@provNascita), nazNascita = UPPER(@nazNascita), email = @email, dominio = @dominio, utente = @utente
|
|
,codOrario = @codOrario, attivo = @attivo
|
|
WHERE (idxDipendente = @Original_idxDipendente);
|
|
|
|
SELECT * FROM Dipendenti WHERE (idxDipendente = @Original_idxDipendente)
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
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
|
|
*
|
|
*******************************************************************************/
|
|
create PROCEDURE stp_TE_doChkFun
|
|
(
|
|
@idxDipendente INT,
|
|
@dataRif DATETIME
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
|
|
-- recupero tipo di fuction da usare
|
|
DECLARE @chkFun NVARCHAR(50) = ''
|
|
DECLARE @minPP INT = 0
|
|
|
|
SELECT @chkFun = dbo.f_chkFunDip(@idxDipendente)
|
|
-- controllo tipo di funzione... e imposto risultato!
|
|
IF(@chkFun = 'PP60m')
|
|
BEGIN
|
|
SET @minPP = 60
|
|
|
|
UPDATE TimbratureExpl
|
|
SET chkFunCod = 'P',
|
|
chkFunRes = 'Mancata pausa pranzo 60m'
|
|
WHERE idxDipendente = @idxDipendente AND dataLav = @dataRif
|
|
AND (minArcoPres - minLav) < @minPP -- controllo pause > PP imposta
|
|
AND (minOrd > 0 AND minLav > 0) -- controllo DEBBA lavorare e ABBIA lavorato
|
|
END
|
|
ELSE IF(@chkFun = 'PP30m')
|
|
BEGIN
|
|
SET @minPP = 30
|
|
|
|
UPDATE TimbratureExpl
|
|
SET chkFunCod = 'P',
|
|
chkFunRes = 'Mancata pausa pranzo 30m'
|
|
WHERE idxDipendente = @idxDipendente AND dataLav = @dataRif
|
|
AND (minArcoPres - minLav) < @minPP -- controllo pause > PP imposta
|
|
AND (minOrd > 0 AND minLav > 0) -- controllo DEBBA lavorare e ABBIA lavorato
|
|
END
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/*******************************************************************************
|
|
* stp_ricalcolaTimbratureExpl
|
|
*
|
|
* Aggiorna la tab timbrature expl ricalcolando la giornata del record interessato
|
|
* mod: S.E.L. - 2012.02.06
|
|
********************************************************************************/
|
|
alter PROCEDURE stp_ricalcolaTimbratureExpl
|
|
(
|
|
@idxDipendente AS INT,
|
|
@dataRif AS DATETIME
|
|
)
|
|
AS
|
|
|
|
-- 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)
|
|
|
|
-- calcolo ore totali...
|
|
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
|
|
|
|
-- 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
|
|
|
|
-- calcolo resoconto MINUTI giornaliero, imposto LUN x inizio settimana
|
|
SET DATEFIRST 1
|
|
|
|
-- 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...)
|
|
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
|
|
|
|
|
|
-- MIN da tab giustificativi (feste, ferie, malattia, permessi)
|
|
UPDATE TimbratureExpl
|
|
SET minMal = CASE WHEN g.codGiust='MAL' 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,
|
|
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
|
|
|
|
-- 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
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
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.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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(324, GETDATE())
|
|
GO
|
|
SELECT * FROM LogUpdateDb ORDER BY Versione DESC
|