Samuele Locatelli
733 lines
20 KiB
Transact-SQL
733 lines
20 KiB
Transact-SQL
create table LogUpdateDb(
|
|
Versione int not null constraint PK_LogUpdateDb primary key,
|
|
Data datetime
|
|
);
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
create VIEW v_selCC_all
|
|
AS
|
|
SELECT DISTINCT CapoCommessa AS value, CapoCommessa AS label
|
|
FROM dbo.DatiCommessa
|
|
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 = "DatiCommessa"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 223
|
|
Right = 230
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
Width = 1755
|
|
Width = 1500
|
|
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_selCC_all';
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_selCC_all';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/***********************************************************
|
|
* Function f_AnnoNumComm_byFase
|
|
*
|
|
* fornisce anno-numero commessa dato idx fase (eventualemnte con risalita gerarchia)
|
|
*
|
|
***********************************************************/
|
|
create FUNCTION f_AnnoNumComm_byFase
|
|
(
|
|
@idxFase int = 0
|
|
)
|
|
RETURNS NVARCHAR(9)
|
|
AS
|
|
BEGIN
|
|
DECLARE @answ NVARCHAR(9) = '9999-9999'
|
|
|
|
|
|
SELECT @answ = dbo.f_padLeft(AnnoCommessa,4,'0') + '-' + dbo.f_padLeft(NumeroCommessa,4,'0')
|
|
FROM DatiCommessa dc INNER JOIN AnagFasi af ON dc.idxFase = CASE WHEN af.idxFaseAncest = 0 THEN af.idxFase ELSE af.idxFaseAncest END
|
|
WHERE af.idxFase = @idxFase
|
|
|
|
RETURN ISNULL(@answ,'0000-0000')
|
|
|
|
END
|
|
go
|
|
|
|
/***************************************
|
|
* FUNCTION f_shortFormComm
|
|
*
|
|
* fornisce una stringa breve della commessa (numero + anno a 2 char)
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2012.09.03
|
|
*
|
|
****************************************/
|
|
create FUNCTION f_shortFormComm (@nrComm NVARCHAR(50), @Anno INTEGER)
|
|
RETURNS VARCHAR(255) AS
|
|
BEGIN
|
|
|
|
|
|
RETURN CAST(CAST(@nrComm AS INT) AS NVARCHAR(50)) + '-' + RIGHT(CAST(@Anno AS NVARCHAR(6)),2)
|
|
|
|
END
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/***********************************************************
|
|
* Function f_AnnoNumComm_byFase
|
|
*
|
|
* fornisce anno-numero commessa dato idx fase (eventualemnte con risalita gerarchia)
|
|
*
|
|
***********************************************************/
|
|
alter FUNCTION f_AnnoNumComm_byFase
|
|
(
|
|
@idxFase int = 0
|
|
)
|
|
RETURNS NVARCHAR(9)
|
|
AS
|
|
BEGIN
|
|
DECLARE @answ NVARCHAR(9) = '9999-9999'
|
|
|
|
|
|
SELECT @answ = dbo.f_padLeft(AnnoCommessa,4,'0') + '-' + dbo.f_padLeft(NumeroCommessa,4,'0')
|
|
FROM DatiCommessa dc INNER JOIN AnagFasi af ON dc.idxFase = CASE WHEN af.idxFaseAncest = 0 THEN af.idxFase ELSE af.idxFaseAncest END
|
|
WHERE af.idxFase = @idxFase
|
|
|
|
RETURN ISNULL(@answ,'0000-0000')
|
|
|
|
END
|
|
go
|
|
|
|
/***************************************
|
|
* FUNCTION f_shortFormComm
|
|
*
|
|
* fornisce una stringa breve della commessa (numero + anno a 2 char)
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2012.09.03
|
|
*
|
|
****************************************/
|
|
alter FUNCTION f_shortFormComm (@nrComm NVARCHAR(50), @Anno INTEGER)
|
|
RETURNS VARCHAR(255) AS
|
|
BEGIN
|
|
|
|
|
|
RETURN CAST(CAST(@nrComm AS INT) AS NVARCHAR(50)) + '-' + RIGHT(CAST(@Anno AS NVARCHAR(6)),2)
|
|
|
|
END
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
alter VIEW v_selCommesse
|
|
AS
|
|
SELECT dbo.DatiCommessa.idxFase AS value, dbo.f_padLeft(dbo.DatiCommessa.NumeroCommessa, 4, '0') + '-' + dbo.f_padLeft(dbo.DatiCommessa.AnnoCommessa,
|
|
4, '0') + ' [' + dbo.f_shortFormComm(dbo.DatiCommessa.NumeroCommessa, dbo.DatiCommessa.AnnoCommessa)
|
|
+ '] | ' + dbo.DatiCommessa.nickname + ' (' + dbo.DatiCommessa.NomeComm + ')' AS label, dbo.AnagFasi.idxProgetto AS conditio
|
|
FROM dbo.DatiCommessa INNER JOIN
|
|
dbo.AnagFasi ON dbo.DatiCommessa.idxFase = dbo.AnagFasi.idxFase
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_ACF_getByIdxProjStatus
|
|
*
|
|
* elenco commesse (fasi esplose) da progetto + status
|
|
*
|
|
* mod: S.E.L. 2013.07.09
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_ACF_getByIdxProjStatus
|
|
(
|
|
@searchText NVARCHAR(250) = '', -- '' = tutti
|
|
@capoComm NVARCHAR(250) = '', -- '' = tutti
|
|
@showFasi BIT = 1, -- 1 = tutti, 0 solo commesse
|
|
@showCommArch BIT = 1, -- 1 = mostra tutti, 0 = nasconde archiviati
|
|
@showCommZeroH BIT = 1 -- 1 = mostra tutti, 0 = nasconde se zero ore caricate
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
-- 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)
|
|
GROUP BY af.idxFase
|
|
),
|
|
myCTE2 AS
|
|
(
|
|
SELECT af.idxProgetto
|
|
, af.idxFase
|
|
, af.codFase
|
|
, af.idxFaseAncest
|
|
, ac.RagSociale
|
|
, ap.nomeProj
|
|
, ap.descrProj
|
|
, dc.CapoCommessa
|
|
, dc.AnnoCommessa
|
|
, dc.NumeroCommessa
|
|
, dbo.f_shortFormComm(dc.NumeroCommessa, dc.AnnoCommessa) as nickComm
|
|
, 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
|
|
INNER JOIN AnagProgetti ap ON af.idxProgetto = ap.idxProgetto
|
|
INNER JOIN AnagClienti ac ON ap.idxCliente = ac.idxCliente
|
|
INNER JOIN DatiCommessa dc ON dc.idxFase = CASE WHEN af.idxFaseAncest = 0 THEN af.idxFase ELSE af.idxFaseAncest END
|
|
WHERE (af.Attivo = CASE WHEN @showCommArch <> 0 THEN af.Attivo ELSE 1 END)
|
|
AND (totOre > 0 OR @showCommZeroH = 1)
|
|
AND idxFaseAncest = CASE WHEN @showFasi = 1 THEN idxFaseAncest ELSE 0 END
|
|
AND dc.CapoCommessa = CASE WHEN @capoComm <>'' THEN @capoComm ELSE dc.CapoCommessa END
|
|
)
|
|
|
|
|
|
SELECT *
|
|
FROM myCTE2
|
|
WHERE ((nomeFase LIKE '%'+@searchText+'%') OR (descrizioneFase LIKE '%'+@searchText+'%') OR (nickComm LIKE '%'+@searchText+'%') OR (RagSociale LIKE '%'+@searchText+'%') OR (descrProj LIKE '%'+@searchText+'%') OR (AnnoCommessa LIKE '%'+@searchText+'%') OR (NumeroCommessa LIKE '%'+@searchText+'%') )
|
|
ORDER BY AnnoCommessa DESC, NumeroCommessa DESC, codFase, nomeFase
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AF_getByIdxProjStatus
|
|
*
|
|
* elenco fasi da progetto + status commesse
|
|
*
|
|
* mod: S.E.L. 2013.07.09
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_AF_getByIdxProjStatus
|
|
(
|
|
@idxProgetto INT, -- 0 = tutti
|
|
@showCommArch BIT = 1, -- 1 = mostra tutti, 0 = nasconde archiviati
|
|
@showCommZeroH BIT = 1 -- 1 = mostra tutti, 0 = nasconde se zero ore caricate
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
-- 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)
|
|
AND (af.Attivo = CASE WHEN @showCommArch <> 0 THEN af.Attivo ELSE 1 END)
|
|
AND (totOre > 0 OR @showCommZeroH = 1)
|
|
ORDER BY dbo.f_AnnoNumComm_byFase(af.idxFase) DESC, af.codFase, af.nomeFase
|
|
go
|
|
|
|
create PROCEDURE stp_VSCom_getBySearchLike
|
|
(
|
|
@ricerca nvarchar(250)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT *
|
|
FROM v_selCommesse
|
|
WHERE (label LIKE '%' + @ricerca + '%')
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSPrj_byValue
|
|
*
|
|
* elenco progetti "formattato" by cliente, ricerca singolo valore
|
|
*
|
|
* mod : 2013.07.09
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
create PROCEDURE stp_VSPrj_byValue
|
|
(
|
|
@value INT
|
|
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT value, label, conditio, Attivo
|
|
FROM v_selProgetti
|
|
WHERE value = @value
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_ACF_getByIdxProjStatus
|
|
*
|
|
* elenco commesse (fasi esplose) da progetto + status
|
|
*
|
|
* mod: S.E.L. 2013.07.09
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_ACF_getByIdxProjStatus
|
|
(
|
|
@searchText NVARCHAR(250) = '', -- '' = tutti
|
|
@capoComm NVARCHAR(250) = '', -- '' = tutti
|
|
@showFasi BIT = 1, -- 1 = tutti, 0 solo commesse
|
|
@showCommArch BIT = 1, -- 1 = mostra tutti, 0 = nasconde archiviati
|
|
@showCommZeroH BIT = 1 -- 1 = mostra tutti, 0 = nasconde se zero ore caricate
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
-- 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)
|
|
GROUP BY af.idxFase
|
|
),
|
|
myCTE2 AS
|
|
(
|
|
SELECT af.idxProgetto
|
|
, af.idxFase
|
|
, af.codFase
|
|
, af.idxFaseAncest
|
|
, ac.RagSociale
|
|
, ap.nomeProj
|
|
, ap.descrProj
|
|
, dc.CapoCommessa
|
|
, dc.AnnoCommessa
|
|
, dc.NumeroCommessa
|
|
, dbo.f_shortFormComm(dc.NumeroCommessa, dc.AnnoCommessa) as nickComm
|
|
, 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
|
|
INNER JOIN AnagProgetti ap ON af.idxProgetto = ap.idxProgetto
|
|
INNER JOIN AnagClienti ac ON ap.idxCliente = ac.idxCliente
|
|
INNER JOIN DatiCommessa dc ON dc.idxFase = CASE WHEN af.idxFaseAncest = 0 THEN af.idxFase ELSE af.idxFaseAncest END
|
|
WHERE (af.Attivo = CASE WHEN @showCommArch <> 0 THEN af.Attivo ELSE 1 END)
|
|
AND (totOre > 0 OR @showCommZeroH = 1)
|
|
AND idxFaseAncest = CASE WHEN @showFasi = 1 THEN idxFaseAncest ELSE 0 END
|
|
AND dc.CapoCommessa = CASE WHEN @capoComm <>'' THEN @capoComm ELSE dc.CapoCommessa END
|
|
)
|
|
|
|
|
|
SELECT *
|
|
FROM myCTE2
|
|
WHERE ((nomeFase LIKE '%'+@searchText+'%') OR (descrizioneFase LIKE '%'+@searchText+'%') OR (nickComm LIKE '%'+@searchText+'%') OR (RagSociale LIKE '%'+@searchText+'%') OR (descrProj LIKE '%'+@searchText+'%') OR (AnnoCommessa LIKE '%'+@searchText+'%') OR (NumeroCommessa LIKE '%'+@searchText+'%') )
|
|
ORDER BY AnnoCommessa DESC, NumeroCommessa DESC, codFase, nomeFase
|
|
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;
|
|
|
|
-- 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 dbo.f_AnnoNumComm_byFase(af.idxFase) DESC, af.codFase, af.nomeFase
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_AF_getByIdxProjStatus
|
|
*
|
|
* elenco fasi da progetto + status commesse
|
|
*
|
|
* mod: S.E.L. 2013.07.09
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_AF_getByIdxProjStatus
|
|
(
|
|
@idxProgetto INT, -- 0 = tutti
|
|
@showCommArch BIT = 1, -- 1 = mostra tutti, 0 = nasconde archiviati
|
|
@showCommZeroH BIT = 1 -- 1 = mostra tutti, 0 = nasconde se zero ore caricate
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
-- 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)
|
|
AND (af.Attivo = CASE WHEN @showCommArch <> 0 THEN af.Attivo ELSE 1 END)
|
|
AND (totOre > 0 OR @showCommZeroH = 1)
|
|
ORDER BY dbo.f_AnnoNumComm_byFase(af.idxFase) DESC, af.codFase, af.nomeFase
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSPrj
|
|
*
|
|
* elenco progetti "formattato" by cliente
|
|
*
|
|
* mod : 26/09/2012
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_VSPrj_byConditio
|
|
(
|
|
@conditio INT -- 0 = tutti
|
|
,@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
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSPrj_byValue
|
|
*
|
|
* elenco progetti "formattato" by cliente, ricerca singolo valore
|
|
*
|
|
* mod : 2013.07.09
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_VSPrj_byValue
|
|
(
|
|
@value INT
|
|
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT value, label, conditio, Attivo
|
|
FROM v_selProgetti
|
|
WHERE value = @value
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(172, GETDATE())
|
|
GO
|
|
SELECT TOP 10 * FROM LogUpdateDb ORDER BY Versione DESC
|
|
GO
|