marco
715 lines
18 KiB
Transact-SQL
715 lines
18 KiB
Transact-SQL
alter table AnagKeyValue add
|
|
descrizione nvarchar(250);
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/*----------------------------------
|
|
* Trigger su insert/update/delete
|
|
*
|
|
* Aggiorna giustificativi nella tab TimbratureExpl
|
|
*
|
|
* creato da: S.E. Locatelli - 2012.12.04
|
|
* ultima modifica:
|
|
*----------------------------------*/
|
|
alter TRIGGER trg_giustUpdateTimbr
|
|
ON Giustificativi
|
|
FOR INSERT, UPDATE
|
|
AS
|
|
|
|
-- controllo se rescord esista altrimenti lo creo!!!
|
|
DECLARE @trovate INT = 0
|
|
DECLARE @idxDipendente INT
|
|
DECLARE @dataRif DATETIME
|
|
SET @trovate = ISNULL( ( SELECT COUNT(*) FROM TimbratureExpl te INNER JOIN inserted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav) ,0)
|
|
IF(@trovate = 0)
|
|
BEGIN
|
|
SELECT @idxDipendente = idxDipendente, @dataRif=dataLav FROM inserted
|
|
EXEC stp_ricalcolaTimbratureExpl @idxDipendente, @dataRif
|
|
END
|
|
|
|
|
|
-- CONTROLLO SE IL RECORD NON SIA BLOCCATO!!!
|
|
DECLARE @block BIT
|
|
SET @block = ( SELECT te.block FROM TimbratureExpl te INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav )
|
|
IF(@block = 0)
|
|
BEGIN
|
|
-- prendo tutte le "vecchie" righe (deleted) e x le giornate relative TOLGO i minuti dei giustificativi
|
|
UPDATE TimbratureExpl
|
|
SET minMal = CASE WHEN g.codGiust='MAL' 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
|
|
FROM TimbratureExpl te INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav
|
|
|
|
END
|
|
|
|
SET @block = ( SELECT te.block FROM TimbratureExpl te INNER JOIN inserted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav )
|
|
IF(@block = 0)
|
|
BEGIN
|
|
-- prendo le "nuove" righe (inserted) e x le giornate relative AGGIUNGO i minuti dei giustificativi
|
|
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
|
|
FROM TimbratureExpl te INNER JOIN inserted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav
|
|
|
|
END
|
|
go
|
|
|
|
/*----------------------------------
|
|
* Trigger su insert/update/delete
|
|
*
|
|
* Aggiorna giustificativi nella tab TimbratureExpl
|
|
*
|
|
* creato da: S.E. Locatelli - 2012.12.04
|
|
* ultima modifica:
|
|
*----------------------------------*/
|
|
create TRIGGER trg_giustDelUpdTimbr
|
|
ON Giustificativi
|
|
FOR DELETE
|
|
AS
|
|
|
|
-- controllo se rescord esista altrimenti lo creo!!!
|
|
DECLARE @trovate INT = 0
|
|
DECLARE @idxDipendente INT
|
|
DECLARE @dataRif DATETIME
|
|
|
|
-- CONTROLLO SE IL RECORD NON SIA BLOCCATO!!!
|
|
DECLARE @block BIT
|
|
SET @block = ( SELECT te.block FROM TimbratureExpl te INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav )
|
|
IF(@block = 0)
|
|
BEGIN
|
|
-- prendo tutte le "vecchie" righe (deleted) e x le giornate relative TOLGO i minuti dei giustificativi
|
|
UPDATE TimbratureExpl
|
|
SET minMal = CASE WHEN g.codGiust='MAL' 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
|
|
FROM TimbratureExpl te INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav
|
|
|
|
END
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
alter VIEW v_selFasi
|
|
AS
|
|
SELECT idxFase AS value, nomeFase AS label, idxProgetto AS conditio, enableTime, codFase, idxFase * enableTime AS valueGroup
|
|
FROM dbo.AnagFasi
|
|
go
|
|
|
|
exec sp_updateextendedproperty '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 = "AnagFasi"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 284
|
|
Right = 209
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 11985
|
|
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_selFasi';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
alter VIEW v_selProgetti
|
|
AS
|
|
SELECT dbo.AnagProgetti.idxProgetto AS value, dbo.AnagClienti.RagSociale + ' - ' + dbo.AnagProgetti.nomeProj AS label, dbo.AnagClienti.idxCliente AS conditio,
|
|
dbo.AnagProgetti.Attivo
|
|
FROM dbo.AnagClienti INNER JOIN
|
|
dbo.AnagProgetti ON dbo.AnagClienti.idxCliente = dbo.AnagProgetti.idxCliente
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AF_getByIdxProj
|
|
*
|
|
* elenco fasi da progetto
|
|
*
|
|
* mod: S.E.L. 2012.10.31
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AF_getByIdxProj
|
|
(
|
|
@idxProgetto INT
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
/*
|
|
SELECT idxFase, idxProgetto, codFase, idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo, codClasse, codExt
|
|
FROM AnagFasi
|
|
WHERE (idxProgetto = @idxProgetto)
|
|
ORDER BY codFase
|
|
*/
|
|
|
|
-- dichiaro variabili "accessorie"
|
|
DECLARE @idxDipendente INT = 0 -- 0 = tutti
|
|
DECLARE @dataFrom DATETIME = '19000101'
|
|
DECLARE @dataTo DATETIME = '99991231'
|
|
|
|
-- controllo se date nulle importo a min/max
|
|
DECLARE @firstDate DATETIME
|
|
DECLARE @lastDate DATETIME
|
|
|
|
SELECT @firstDate = MIN(inizio), @lastDate=DATEADD(DAY,1,MAX(inizio)) FROM RegAttivita
|
|
SELECT @dataFrom = ISNULL(@dataFrom, @firstDate), @dataTo=ISNULL(@dataTo, @lastDate)
|
|
|
|
|
|
;WITH myCTE AS
|
|
(
|
|
SELECT af.idxFase, SUM(ISNULL(ra.oreTot, 0)) AS totOre
|
|
FROM AnagFasi AS af INNER JOIN
|
|
RegAttivita AS ra ON af.idxFase = ra.idxFase
|
|
WHERE (ra.idxDipendente = @idxDipendente OR @idxDipendente = 0)
|
|
AND (ra.inizio >= @dataFrom AND ra.inizio <= @dataTo)
|
|
AND (af.idxProgetto = @idxProgetto OR @idxProgetto = 0)
|
|
GROUP BY af.idxFase
|
|
)
|
|
SELECT af.idxFase
|
|
, af.idxProgetto
|
|
, af.codFase
|
|
, af.idxFaseAncest
|
|
, af.nomeFase
|
|
, af.descrizioneFase
|
|
, af.enableTime
|
|
, af.enableMoney
|
|
, af.Attivo
|
|
, ISNULL(af.budgetTime,0) AS budgetTime
|
|
, ISNULL(af.budgetMoney,0) AS budgetMoney
|
|
, ISNULL(af.codClasse,'') AS codClasse
|
|
, ISNULL(af.codExt, '') AS codExt
|
|
, ISNULL(cte.totOre, 0) AS totOre
|
|
FROM AnagFasi AS af LEFT OUTER JOIN
|
|
myCte AS cte ON cte.idxFase = af.idxFase
|
|
WHERE (af.idxProgetto = @idxProgetto OR @idxProgetto = 0)
|
|
ORDER BY af.codFase, af.nomeFase
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AF_InsertQuery
|
|
*
|
|
* elenco fasi da progetto
|
|
*
|
|
* mod: S.E.L. 2012.11.02
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AF_InsertQuery
|
|
(
|
|
@idxProgetto INT,
|
|
@idxFaseAncest INT,
|
|
@nomeFase NVARCHAR(50),
|
|
@descrizioneFase NVARCHAR(250),
|
|
@enableTime BIT,
|
|
@enableMoney BIT,
|
|
@budgetTime decimal(19, 4),
|
|
@budgetMoney decimal(19, 4),
|
|
@Attivo BIT
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
INSERT INTO AnagFasi ( codFase, idxProgetto, idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo, budgetTime, budgetMoney)
|
|
VALUES ('', @idxProgetto, @idxFaseAncest, @nomeFase, @descrizioneFase, @enableTime, @enableMoney, @Attivo, @budgetTime, @budgetMoney);
|
|
|
|
SELECT idxFase, idxProgetto, codFase, idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo FROM AnagFasi WHERE (idxFase = SCOPE_IDENTITY())
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AF_getByIdxProj
|
|
*
|
|
* elenco fasi da progetto
|
|
*
|
|
* mod: S.E.L. 2012.11.02
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AF_updateQuery
|
|
(
|
|
@nomeFase nvarchar(50),
|
|
@descrizioneFase nvarchar(250),
|
|
@enableTime bit,
|
|
@enableMoney bit,
|
|
@Attivo int,
|
|
@budgetTime DECIMAL(19, 4),
|
|
@budgetMoney DECIMAL(19, 4),
|
|
@Original_idxFase int
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
UPDATE AnagFasi
|
|
SET nomeFase = @nomeFase,
|
|
descrizioneFase = @descrizioneFase,
|
|
enableTime = @enableTime,
|
|
enableMoney = @enableMoney,
|
|
budgetTime = @budgetTime,
|
|
budgetMoney = @budgetMoney,
|
|
Attivo = @Attivo
|
|
WHERE idxFase = @Original_idxFase;
|
|
|
|
SELECT * FROM AnagFasi WHERE idxFase = @Original_idxFase
|
|
go
|
|
|
|
/*----------------------------------
|
|
* STORED PROCEDURE stp_giust_insByDate
|
|
*
|
|
* Aggiunge un giustificativo per coprire la durata necessaria a chiudere la giornata indicata
|
|
* mod: S.E.L. - 2012.12.10
|
|
*----------------------------------*/
|
|
alter PROCEDURE stp_giust_insByDate
|
|
(
|
|
@idxDipendente INT = 0,
|
|
@dataRif DATETIME,
|
|
@codGiust NVARCHAR(5) = 'PERM'
|
|
)
|
|
AS
|
|
|
|
BEGIN TRAN
|
|
--min necessari x chiudere giornata
|
|
DECLARE @minPerm INT = 0
|
|
|
|
-- 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)
|
|
|
|
COMMIT TRAN
|
|
|
|
RETURN
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_RAD_Expl_getByIdxDipData
|
|
*
|
|
* recupera elenco attività (con Expl dei dati da anagrafica) da idxDipendente + dataRif
|
|
*
|
|
* mod: S.E.L. 2013.01.23
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_RAD_Expl_getByIdxDipData
|
|
(
|
|
@idxDipendente INT,
|
|
@dataRif DATETIME
|
|
)
|
|
AS
|
|
|
|
SELECT d.Cognome + ' ' + d.Nome AS CognomeNome, ra.idxDipendente, ra.inizio, ra.fine
|
|
, CASE WHEN ISNULL(descrizione, '') = '' THEN '-' ELSE ISNULL(descrizione, '') END AS descrizione
|
|
, ra.oreTot, ra.oreTot * 60 AS minTot, ra.importo, ac.RagSociale, ap.nomeProj, af.nomeFase, ra.idxRA
|
|
FROM RegAttivita ra INNER JOIN
|
|
AnagFasi af ON ra.idxFase = af.idxFase INNER JOIN
|
|
AnagProgetti ap ON af.idxProgetto = ap.idxProgetto INNER JOIN
|
|
AnagClienti ac ON ap.idxCliente = ac.idxCliente INNER JOIN
|
|
Dipendenti d ON ra.idxDipendente = d.idxDipendente
|
|
WHERE (ra.idxDipendente = @idxDipendente OR @idxDipendente = 0) AND (CAST(ra.inizio AS DATE) = CAST(@dataRif AS DATE) OR
|
|
CAST(ra.fine AS DATE) = CAST(@dataRif AS DATE))
|
|
ORDER BY ra.inizio
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_ReportOreDip
|
|
*
|
|
* elenco ore dipendenti
|
|
*
|
|
* mod: S.E.L. 2013.02.04
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE export.stp_ReportOreDip
|
|
(
|
|
@idxDipendente INT = 0, -- 0 = tutti
|
|
@dataFrom DATETIME,
|
|
@dataTo DATETIME
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
DECLARE @minArr INT
|
|
|
|
SELECT @minArr = ISNULL(valInt,1) FROM AnagKeyValue WHERE nomeVar = 'minutiRoundDay'
|
|
|
|
-- elenco mensile...
|
|
SELECT dataLav, CognomeNome,
|
|
entrata_1, uscita_1, entrata_2, uscita_2,
|
|
entrata_3, uscita_3, entrata_4, uscita_4,
|
|
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
|
|
FROM TimbratureExpl
|
|
WHERE (idxDipendente = @idxDipendente OR @idxDipendente = 0)
|
|
AND dataLav >= CAST(@dataFrom AS DATE)
|
|
AND dataLav <= CAST(@dataTo AS DATE)
|
|
ORDER BY CognomeNome, dataLav
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_RAD_Expl_getByIdxDipPeriodo
|
|
*
|
|
* recupera elenco attività (con Expl dei dati da anagrafica) da idxDipendente + periodo + opzionali cliente e progetto
|
|
*
|
|
* mod: S.E.L. 2013.02.01
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_RAD_Expl_getByIdxDipPeriodo
|
|
(
|
|
@idxDipendente INT,
|
|
@dataFrom DATETIME,
|
|
@dataTo DATETIME,
|
|
@idxCliente INT = 0, -- 0 = tutti
|
|
@idxProgetto INT = 0, -- 0 = tutti
|
|
@soloAncest BIT = 0 -- 0 = tutti
|
|
)
|
|
AS
|
|
|
|
SELECT d.Cognome + ' ' + d.Nome AS CognomeNome, ra.idxDipendente, ra.inizio, ra.fine
|
|
, CASE WHEN ISNULL(descrizione, '') = '' THEN '-' ELSE ISNULL(descrizione, '') END AS descrizione
|
|
, ra.oreTot, ra.oreTot * 60 AS minTot, ra.importo, ac.RagSociale, ap.nomeProj,
|
|
af.nomeFase, ra.idxRA
|
|
FROM RegAttivita ra INNER JOIN
|
|
AnagFasi af ON ra.idxFase = af.idxFase INNER JOIN
|
|
AnagProgetti ap ON af.idxProgetto = ap.idxProgetto INNER JOIN
|
|
AnagClienti ac ON ap.idxCliente = ac.idxCliente INNER JOIN
|
|
Dipendenti d ON ra.idxDipendente = d.idxDipendente
|
|
WHERE (ra.idxDipendente = @idxDipendente OR @idxDipendente=0)
|
|
AND (ra.inizio >= CAST(@dataFrom AS DATE) AND ra.inizio <= CAST(@dataTo AS DATE))
|
|
AND (ac.idxCliente = @idxCliente OR @idxCliente <= 0)
|
|
AND (ap.idxProgetto = @idxProgetto OR @idxProgetto <= 0)
|
|
AND (af.idxFaseAncest = CASE WHEN @soloAncest <> 0 THEN 0 ELSE af.idxFaseAncest END )
|
|
ORDER BY ra.inizio
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_RA_updateFase
|
|
*
|
|
* update fase x record selezionato
|
|
*
|
|
* mod: S.E.L. 2013.02.01
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_RA_updateFase
|
|
(
|
|
@idxFase int,
|
|
@Original_idxRA int
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
UPDATE RegAttivita
|
|
SET idxFase = @idxFase
|
|
WHERE (idxRA = @Original_idxRA);
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSFasiOpt_ByPrj
|
|
*
|
|
* elenco fasi dato progetto con calmpo valore a zero se fase "ancestor"
|
|
*
|
|
* mod : 2013.02.01
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
create PROCEDURE stp_VSFasiOpt_ByPrj
|
|
(
|
|
@conditio INT = 0
|
|
,@soloAttivi BIT = 0 -- 0 = tutti, 1 = solo attivi x time tracking
|
|
)
|
|
AS
|
|
|
|
SELECT value as valueGroup, label, conditio, valueGroup as value
|
|
FROM v_selFasi
|
|
WHERE conditio = @conditio
|
|
AND (enableTime = @soloAttivi OR @soloAttivi = 0)
|
|
ORDER BY codFase, label
|
|
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSFasi_ByPrj
|
|
*
|
|
* elenco fasi dato progetto
|
|
*
|
|
* mod : 26/09/2012
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_VSFasi_ByPrj
|
|
(
|
|
@conditio INT = 0
|
|
,@soloAttivi BIT = 0 -- 0 = tutti, 1 = solo attivi x time tracking
|
|
)
|
|
AS
|
|
|
|
SELECT value, label, conditio
|
|
FROM v_selFasi
|
|
WHERE conditio = @conditio
|
|
AND (enableTime = @soloAttivi OR @soloAttivi = 0)
|
|
ORDER BY codFase, label
|
|
|
|
|
|
RETURN
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSPrj
|
|
*
|
|
* elenco progetti "formattato" by cliente
|
|
*
|
|
* mod : 26/09/2012
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_VSPrj
|
|
(
|
|
@soloAttivi BIT = 0 -- 0 = tutti, 1 = solo attivi
|
|
)
|
|
AS
|
|
/*
|
|
WITH myCTE AS
|
|
(
|
|
SELECT MIN(value) as value, conditio AS label, conditio
|
|
FROM v_selProgetti
|
|
GROUP BY conditio
|
|
|
|
UNION
|
|
|
|
SELECT *
|
|
FROM v_selProgetti
|
|
)
|
|
|
|
SELECT *
|
|
FROM myCTE
|
|
ORDER BY conditio, label
|
|
*/
|
|
|
|
SELECT *
|
|
FROM v_selProgetti
|
|
WHERE (Attivo = @soloAttivi OR @soloAttivi = 0)
|
|
ORDER BY label
|
|
|
|
RETURN
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSPrj
|
|
*
|
|
* elenco progetti "formattato" by cliente
|
|
*
|
|
* mod : 26/09/2012
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_VSPrj_byConditio
|
|
(
|
|
@conditio NVARCHAR(50)
|
|
,@soloAttivi BIT = 0 -- 0 = tutti, 1 = solo attivi
|
|
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT value, label, conditio, Attivo
|
|
FROM v_selProgetti
|
|
WHERE (conditio = @conditio OR @conditio=0)
|
|
AND (Attivo = @soloAttivi OR @soloAttivi = 0)
|
|
ORDER BY label
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(290, GETDATE())
|
|
GO
|
|
SELECT * FROM LogUpdateDb ORDER BY Versione DESC
|