marco
669 lines
18 KiB
Transact-SQL
669 lines
18 KiB
Transact-SQL
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2013.02.13
|
|
-- Description: Gestione trigger update attivo x sottofasi
|
|
-- =============================================
|
|
create TRIGGER trg_AF_updateAttivoSubFasi
|
|
ON AnagFasi
|
|
AFTER UPDATE
|
|
AS
|
|
BEGIN
|
|
IF (@@ROWCOUNT = 0) -- SE NESSUNA RIGA AGGIORNATA O INSERITA ESCO
|
|
RETURN
|
|
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
-- CONTROLLO se è stato aggiornato "attivo)
|
|
IF(UPDATE(Attivo))
|
|
BEGIN
|
|
-- lo farò solo sulle sottofasi e x i casi di modifica di fasi "master"...
|
|
UPDATE AF
|
|
SET AF.Attivo = i.Attivo
|
|
FROM AnagFasi AF
|
|
INNER JOIN inserted i ON AF.idxFaseAncest = i.idxFase
|
|
INNER JOIN deleted d ON i.idxFase = d.idxFase
|
|
WHERE i.Attivo <> d.Attivo
|
|
END
|
|
|
|
END
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
alter table AnagProgetti drop
|
|
column budgetTime,
|
|
column budgetMoney;
|
|
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, Attivo
|
|
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 = 1
|
|
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
|
|
|
|
/**********************************************************
|
|
* STORED stp_AP_Expl_getData
|
|
*
|
|
* recupera elenco progetti con filtraggio ore totali per
|
|
* - dipendente
|
|
* - periodo
|
|
*
|
|
* mod: S.E.L. 2013.01.30
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AP_Expl_getData
|
|
(
|
|
@idxDipendente INT = 0, -- 0 = tutti
|
|
@dataFrom DATETIME = '19000101',
|
|
@dataTo DATETIME = '99991231',
|
|
@idxCliente INT = 0, -- 0 = tutti
|
|
@showPrjArch BIT = 1 -- 1 = mostra tutti
|
|
)
|
|
AS
|
|
|
|
-- 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 cteOreReal AS
|
|
(
|
|
SELECT ap.idxProgetto, SUM(ISNULL(ra.oreTot, 0)) AS totOre
|
|
FROM AnagProgetti AS ap INNER JOIN
|
|
AnagClienti AS ac ON ac.idxCliente=ap.idxCliente INNER JOIN
|
|
AnagFasi AS af ON ap.idxProgetto = af.idxProgetto 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 (ac.idxCliente = @idxCliente OR @idxCliente = 0)
|
|
AND (ap.Attivo = CASE WHEN @showPrjArch <> 0 THEN ap.Attivo ELSE 1 END)
|
|
GROUP BY ap.idxProgetto
|
|
)
|
|
,cteOreBudget as
|
|
(
|
|
SELECT
|
|
idxProgetto
|
|
,SUM(ISNULL(budgetTime,0)) as budgetTime
|
|
,SUM(ISNULL(budgetMoney,0)) as budgetMoney
|
|
FROM AnagFasi
|
|
GROUP BY idxProgetto
|
|
)
|
|
|
|
SELECT ac.RagSociale
|
|
, ap.idxProgetto
|
|
, ap.idxCliente
|
|
, ap.nomeProj
|
|
, ap.descrProj
|
|
, ISNULL(bdgt.budgetTime,0) AS budgetTime
|
|
, ISNULL(bdgt.budgetMoney,0) AS budgetMoney
|
|
, ISNULL(ap.OldIdx,-1) AS OldIdx
|
|
, ap.Attivo
|
|
, ISNULL(ap.codExt,'') AS codExt
|
|
, ISNULL(cte.totOre, 0) AS totOre
|
|
, ISNULL(ap.avvio,'19000101') AS avvio
|
|
, ISNULL(ap.chiusura,'99991231') AS chiusura
|
|
FROM AnagClienti AS ac INNER JOIN
|
|
AnagProgetti AS ap ON ac.idxCliente = ap.idxCliente LEFT OUTER JOIN
|
|
cteOreBudget AS bdgt ON ap.idxProgetto = bdgt.idxProgetto LEFT OUTER JOIN
|
|
cteOreReal AS cte ON cte.idxProgetto = ap.idxProgetto
|
|
WHERE (ac.idxCliente = @idxCliente OR @idxCliente = 0)
|
|
AND (ap.Attivo = CASE WHEN @showPrjArch <> 0 THEN ap.Attivo ELSE 1 END)
|
|
ORDER BY ac.RagSociale, ap.nomeProj
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AP_getByIdxCli
|
|
*
|
|
* recupera elenco progetti da cliente
|
|
*
|
|
* mod: S.E.L. 2012.10.26
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AP_getByIdxCli
|
|
(
|
|
@idxCliente INT,
|
|
@showPrjArch BIT
|
|
)
|
|
AS
|
|
|
|
;WITH cteOreReal AS
|
|
(
|
|
SELECT ap.idxProgetto, SUM(ISNULL(ra.oreTot, 0)) AS totOre
|
|
FROM AnagProgetti AS ap INNER JOIN
|
|
AnagClienti AS ac ON ac.idxCliente=ap.idxCliente INNER JOIN
|
|
AnagFasi AS af ON ap.idxProgetto = af.idxProgetto INNER JOIN
|
|
RegAttivita AS ra ON af.idxFase = ra.idxFase
|
|
WHERE (ac.idxCliente = @idxCliente OR @idxCliente = 0)
|
|
AND (ap.Attivo = CASE WHEN @showPrjArch <> 0 THEN ap.Attivo ELSE 1 END)
|
|
GROUP BY ap.idxProgetto
|
|
)
|
|
,cteOreBudget as
|
|
(
|
|
select
|
|
idxProgetto
|
|
,SUM(ISNULL(budgetTime,0)) as budgetTime
|
|
,SUM(ISNULL(budgetMoney,0)) as budgetMoney
|
|
from AnagFasi
|
|
GROUP BY idxProgetto
|
|
)
|
|
|
|
SELECT ac.RagSociale
|
|
, ap.idxProgetto
|
|
, ap.idxCliente
|
|
, ap.nomeProj
|
|
, ap.descrProj
|
|
, ISNULL(bdgt.budgetTime,0) AS budgetTime
|
|
, ISNULL(bdgt.budgetMoney,0) AS budgetMoney
|
|
, ISNULL(ap.OldIdx,-1) AS OldIdx
|
|
, ap.Attivo
|
|
, ISNULL(ap.codExt,'') AS codExt
|
|
, ISNULL(cte.totOre, 0) AS totOre
|
|
, ISNULL(ap.avvio,'19000101') AS avvio
|
|
, ISNULL(ap.chiusura,'99991231') AS chiusura
|
|
FROM AnagClienti AS ac INNER JOIN
|
|
AnagProgetti AS ap ON ac.idxCliente = ap.idxCliente LEFT OUTER JOIN
|
|
cteOreBudget AS bdgt ON ap.idxProgetto = bdgt.idxProgetto LEFT OUTER JOIN
|
|
cteOreReal AS cte ON cte.idxProgetto = ap.idxProgetto
|
|
WHERE (ac.idxCliente = @idxCliente OR @idxCliente = 0)
|
|
AND (ap.Attivo = CASE WHEN @showPrjArch <> 0 THEN ap.Attivo ELSE 1 END)
|
|
ORDER BY ac.RagSociale, ap.nomeProj
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AP_getByIdxPrj
|
|
*
|
|
* recupera elenco progetti da idx
|
|
*
|
|
* mod: S.E.L. 2012.11.06
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AP_getByIdxPrj
|
|
(
|
|
@idxProgetto INT
|
|
)
|
|
AS
|
|
/*
|
|
SELECT *
|
|
FROM AnagProgetti
|
|
WHERE idxProgetto = @idxProgetto
|
|
ORDER BY nomeProj
|
|
*/
|
|
|
|
;WITH cteOreReal AS
|
|
(
|
|
SELECT ap.idxProgetto, SUM(ISNULL(ra.oreTot, 0)) AS totOre
|
|
FROM AnagProgetti AS ap INNER JOIN
|
|
AnagClienti AS ac ON ac.idxCliente=ap.idxCliente INNER JOIN
|
|
AnagFasi AS af ON ap.idxProgetto = af.idxProgetto INNER JOIN
|
|
RegAttivita AS ra ON af.idxFase = ra.idxFase
|
|
WHERE ap.idxProgetto = @idxProgetto
|
|
GROUP BY ap.idxProgetto
|
|
)
|
|
,cteOreBudget as
|
|
(
|
|
select
|
|
idxProgetto
|
|
,SUM(ISNULL(budgetTime,0)) as budgetTime
|
|
,SUM(ISNULL(budgetMoney,0)) as budgetMoney
|
|
from AnagFasi
|
|
GROUP BY idxProgetto
|
|
)
|
|
|
|
SELECT ac.RagSociale
|
|
, ap.idxProgetto
|
|
, ap.idxCliente
|
|
, ap.nomeProj
|
|
, ap.descrProj
|
|
, ISNULL(bdgt.budgetTime,0) AS budgetTime
|
|
, ISNULL(bdgt.budgetMoney,0) AS budgetMoney
|
|
, ISNULL(ap.OldIdx,-1) AS OldIdx
|
|
, ap.Attivo
|
|
, ISNULL(ap.codExt,'') AS codExt
|
|
, ISNULL(cte.totOre, 0) AS totOre
|
|
, ISNULL(ap.avvio,'19000101') AS avvio
|
|
, ISNULL(ap.chiusura,'99991231') AS chiusura
|
|
FROM AnagClienti AS ac INNER JOIN
|
|
AnagProgetti AS ap ON ac.idxCliente = ap.idxCliente LEFT OUTER JOIN
|
|
cteOreBudget AS bdgt ON ap.idxProgetto = bdgt.idxProgetto LEFT OUTER JOIN
|
|
cteOreReal AS cte ON cte.idxProgetto = ap.idxProgetto
|
|
WHERE ap.idxProgetto = @idxProgetto
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AP_insertQuery
|
|
*
|
|
* inserisce un progetto in anagrafica
|
|
*
|
|
* mod: S.E.L. 2013.01.30
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AP_insertQuery
|
|
(
|
|
@idxCliente int,
|
|
@nomeProj nvarchar(50),
|
|
@descrProj nvarchar(250)
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
DECLARE @avvio DATETIME = '19000101'
|
|
DECLARE @chiusura DATETIME = '99991231'
|
|
INSERT INTO AnagProgetti
|
|
(idxCliente, nomeProj, descrProj, Attivo, avvio, chiusura)
|
|
VALUES (@idxCliente,@nomeProj,@descrProj, 1, @avvio, @chiusura);
|
|
/*
|
|
DECLARE @idxPrj INT
|
|
SELECT @idxPrj = SCOPE_IDENTITY()
|
|
EXEC stp_AP_getByIdxPrj @idxPrj
|
|
*/
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AP_update
|
|
*
|
|
* elimina un progetto da anagrafica
|
|
*
|
|
* mod: S.E.L. 2013.01.30
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AP_update
|
|
(
|
|
@idxCliente INT,
|
|
@nomeProj NVARCHAR(50),
|
|
@descrProj NVARCHAR(250),
|
|
--@avvio DATETIME = '19000101',
|
|
--@chiusura DATETIME = '99991231',
|
|
@Original_idxProgetto INT
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
UPDATE dbo.AnagProgetti
|
|
SET idxCliente = @idxCliente
|
|
,nomeProj = @nomeProj
|
|
,descrProj = @descrProj
|
|
--,avvio = @avvio,
|
|
--,chiusura = @chiusura
|
|
WHERE idxProgetto = @Original_idxProgetto;
|
|
|
|
/*
|
|
EXEC stp_AP_getByIdxPrj @Original_idxProgetto
|
|
*/
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AP_updateAttivo
|
|
*
|
|
* elimina un progetto da anagrafica
|
|
*
|
|
* mod: S.E.L. 2012.11.06
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AP_updateAttivo
|
|
(
|
|
@Attivo BIT,
|
|
@Original_idxProgetto INT
|
|
)
|
|
AS
|
|
SET NOCOUNT OFF;
|
|
UPDATE dbo.AnagProgetti
|
|
SET Attivo = @Attivo
|
|
WHERE idxProgetto = @Original_idxProgetto;
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
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
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_RAD_Expl_getByIdxDipPeriodo
|
|
(
|
|
@idxDipendente INT,
|
|
@dataFrom DATETIME,
|
|
@dataTo DATETIME,
|
|
@idxCliente INT = 0, -- 0 = tutti
|
|
@idxProgetto INT = 0, -- 0 = tutti
|
|
@idxFase 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.idxFase = @idxFase OR @idxFase <= 0)
|
|
AND (af.idxFaseAncest = CASE WHEN @soloAncest <> 0 THEN 0 ELSE af.idxFaseAncest END )
|
|
ORDER BY ra.inizio
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_RA_clonaLastRA_Utente
|
|
*
|
|
* clona un attività utente: se c'è da ultima altrimenti da zero x una certa data
|
|
*
|
|
* mod: S.E.L. 2013.01.17
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_RA_clonaLastRA_Utente
|
|
(
|
|
@idxDipendente INT,
|
|
@dataRif DATETIME -- data x cui creare un record attività
|
|
)
|
|
AS
|
|
|
|
-- variabili
|
|
DECLARE @idxFase INT
|
|
DECLARE @idxRA INT
|
|
|
|
-- arrotondo data richiesta solo all'ora...
|
|
SELECT @dataRif = DATEADD(HOUR, DATEPART(HOUR,@dataRif), CONVERT(DATETIME,(CONVERT(DATE,@dataRif))))
|
|
|
|
-- cerco se esista ultima reg attività utente...
|
|
SELECT @idxRA=ISNULL((SELECT TOP 1 idxRA FROM RegAttivita WHERE idxDipendente = @idxDipendente ORDER BY inizio DESC),0)
|
|
|
|
-- se non ho trovato records, ovvero idxRA = 0, prendo ultima fase da elenco
|
|
IF (@idxRA = 0)
|
|
BEGIN
|
|
-- calcolo ultima fase inserita...
|
|
SELECT TOP 1 @idxFase=ISNULL(idxFase,0) FROM AnagFasi ORDER BY idxFase DESC
|
|
-- se trovata fase inserisco!
|
|
IF(@idxFase > 0)
|
|
BEGIN
|
|
INSERT INTO RegAttivita(idxDipendente, idxFase, inizio, fine, descrizione, importo)
|
|
VALUES (@idxDipendente, @idxFase, @dataRif, DATEADD(HOUR,1,@dataRif), '...',0)
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
-- inserisco su data richeista duplicazione dell'ultima attività utente...
|
|
INSERT INTO RegAttivita(idxDipendente, idxFase, inizio, fine, descrizione, importo)
|
|
SELECT idxDipendente, idxFase, @dataRif, DATEADD(minute, DATEDIFF(MINUTE, inizio, fine), @dataRif), descrizione, importo
|
|
FROM RegAttivita
|
|
WHERE (idxRA = @idxRA)
|
|
|
|
END
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_statsProj
|
|
*
|
|
* calcolo statistiche progetto
|
|
*
|
|
* mod: S.E.L. 2012.10.31
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_statsProj
|
|
(
|
|
@idxProgetto int
|
|
)
|
|
AS
|
|
|
|
DECLARE @oreBudget DECIMAL(19,4), @oreMese DECIMAL(19,4), @oreMesePrec DECIMAL(19,4), @oreTot DECIMAL(19,4);
|
|
DECLARE @adesso DATETIME, @inizio DATETIME, @fine DATETIME;
|
|
|
|
SET @adesso = ( SELECT GETDATE() )
|
|
|
|
SET @oreBudget = (
|
|
SELECT SUM(ISNULL(budgetTime, 0))
|
|
FROM AnagFasi
|
|
WHERE idxProgetto = @idxProgetto
|
|
GROUP BY idxProgetto
|
|
)
|
|
SET @oreTot = (
|
|
SELECT ISNULL(SUM(ISNULL(ra.oreTot,0)),0) AS erogate
|
|
FROM AnagFasi af INNER JOIN RegAttivita ra ON af.idxFase = ra.idxFase
|
|
WHERE af.idxProgetto = @idxProgetto
|
|
GROUP BY af.idxProgetto
|
|
)
|
|
|
|
SET @inizio = ( SELECT dbo.Date(YEAR(@adesso), MONTH(@adesso), 1) )
|
|
SET @fine = ( SELECT dbo.Date(YEAR(@adesso), MONTH(@adesso)+1, 1) )
|
|
|
|
SET @oreMese = (
|
|
SELECT ISNULL(SUM(ISNULL(ra.oreTot,0)),0) AS erogate
|
|
FROM AnagFasi af INNER JOIN RegAttivita ra ON af.idxFase = ra.idxFase
|
|
WHERE af.idxProgetto = @idxProgetto
|
|
AND ra.inizio BETWEEN @inizio AND @fine
|
|
)
|
|
|
|
SET @inizio = ( SELECT dbo.Date(YEAR(@adesso), MONTH(@adesso)-1, 1) )
|
|
SET @fine = ( SELECT dbo.Date(YEAR(@adesso), MONTH(@adesso), 1) )
|
|
|
|
SET @oreMesePrec = (
|
|
SELECT ISNULL(SUM(ISNULL(ra.oreTot,0)),0) AS erogate
|
|
FROM AnagFasi af INNER JOIN RegAttivita ra ON af.idxFase = ra.idxFase
|
|
WHERE af.idxProgetto = @idxProgetto
|
|
AND ra.inizio BETWEEN @inizio AND @fine
|
|
)
|
|
SELECT @oreBudget AS oreBudget, @oreTot AS oreTot, @oreMese AS oreMese, @oreMesePrec AS oreMesePrec, Attivo
|
|
FROM AnagProgetti
|
|
WHERE idxProgetto = @idxProgetto
|
|
|
|
RETURN
|
|
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
|
|
**************************************/
|
|
alter 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)
|
|
AND Attivo = 1
|
|
ORDER BY codFase, label
|
|
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(303, GETDATE())
|
|
GO
|
|
SELECT * FROM LogUpdateDb ORDER BY Versione DESC
|