Samuele Locatelli
475 lines
10 KiB
Transact-SQL
475 lines
10 KiB
Transact-SQL
-- STORED!!!
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
create PROCEDURE RefreshAllView AS
|
|
BEGIN
|
|
DECLARE views_cursor CURSOR FOR
|
|
SELECT Schema_Name(schema_id)+'.'+name AS NAME FROM sys.objects WHERE type='V'
|
|
OPEN views_cursor
|
|
declare @view varchar(256)
|
|
FETCH NEXT FROM views_cursor INTO @view
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
EXEC SP_REFRESHVIEW @view
|
|
FETCH NEXT FROM views_cursor INTO @view
|
|
END
|
|
CLOSE views_cursor
|
|
DEALLOCATE views_cursor
|
|
END
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_baz_deleteByIdxFase
|
|
*
|
|
* elimina record di Bazaar Risorse data la fase, anno e settimana selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.05
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_BAZ_deleteByIdxFase
|
|
(
|
|
@Anno INT = 0,
|
|
@Sett INT = 0,
|
|
@idxFase INT = 0
|
|
)
|
|
AS
|
|
|
|
DELETE FROM BazaarRisorse
|
|
WHERE Anno = @Anno
|
|
AND Sett = @Sett
|
|
AND idxFase = @idxFase
|
|
|
|
RETURN
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_BAZ_getByAnnoSett
|
|
*
|
|
* recupera record di Bazaar Risorse per anno e settimana selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_BAZ_getByAnnoSett
|
|
(
|
|
@Anno int,
|
|
@Sett int
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT Anno, Sett, idxDipendente, idxFase, Allocazione, SchemaWeek, OreTot
|
|
FROM dbo.BazaarRisorse
|
|
WHERE Anno = @Anno AND Sett = @Sett
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_BAZ_getByAnnoSettDip
|
|
*
|
|
* recupera record di Bazaar Risorse per anno, settimana e dipendente selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_BAZ_getByAnnoSettDip
|
|
(
|
|
@Anno INT,
|
|
@Sett INT,
|
|
@idxDipendente INT = 1
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT Anno, Sett, idxDipendente, idxFase, Allocazione, SchemaWeek, OreTot
|
|
FROM dbo.BazaarRisorse
|
|
WHERE Anno = @Anno AND Sett = @Sett AND idxDipendente = @idxDipendente
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_baz_creaBulkByIdxFase
|
|
*
|
|
* crea in blocco tanti record di Bazaar Risorse quante le risorse disponibili per la fase, anno e settimana selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_BAZ_updateQuery
|
|
(
|
|
@Anno INT = 0,
|
|
@Sett INT = 0,
|
|
@idxDipendente INT = 0,
|
|
@idxFase INT = 0,
|
|
@Allocazione INT = 0
|
|
)
|
|
AS
|
|
|
|
-- creo 1 record x ogni dip in Dip2ATR con un gruppo valido (non nullo/vuoto) x il set Anno/Sett/Fase
|
|
UPDATE BazaarRisorse
|
|
SET Allocazione = @Allocazione
|
|
WHERE Anno = @Anno
|
|
AND Sett = @Sett
|
|
AND idxDipendente = @idxDipendente
|
|
AND idxFase = @idxFase
|
|
|
|
RETURN
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_createTallyTable
|
|
*
|
|
* crea una tally table delal dimensione desiderata
|
|
*
|
|
* mod: S.E.L. 2013.09.06
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_createTallyTable
|
|
(
|
|
@numVal INT = 100000
|
|
)
|
|
AS
|
|
IF EXISTS (SELECT * FROM sys.objects
|
|
WHERE object_id = OBJECT_ID(N'[dbo].[TallyTable]') AND type in (N'U'))
|
|
DROP TABLE [dbo].[TallyTable];
|
|
|
|
SELECT TOP(@numVal) IDENTITY(int,1,1) AS n
|
|
INTO TallyTable
|
|
FROM MASTER..spt_values a, MASTER..spt_values b;
|
|
|
|
CREATE UNIQUE CLUSTERED INDEX idx_1 ON TallyTable(n);
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
create PROCEDURE stp_EO_getByTipo
|
|
(
|
|
@CodTipo nvarchar(50)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT CodTipo, ordine, opzione FROM dbo.ElencoOpzioni
|
|
WHERE CodTipo = @CodTipo
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSCom_getAttiveBySearchLike
|
|
*
|
|
* elenco commesse ATTIVE "formattato" by progetti con ricerca "like"
|
|
*
|
|
* mod : 2013.09.04
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
create PROCEDURE stp_VSCom_getAttiveBySearchLike
|
|
(
|
|
@ricerca nvarchar(250)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT *
|
|
FROM v_selCommesseAttive
|
|
WHERE (label LIKE '%' + @ricerca + '%')
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSCom_getAttiveUnusedBySearchLike
|
|
*
|
|
* elenco commesse ATTIVE e non "usate" da programmazioen bazaar
|
|
*
|
|
* mod : 2013.09.04
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
create PROCEDURE stp_VSCom_getAttiveUnusedBySearchLike
|
|
(
|
|
@Anno INT = 0,
|
|
@Sett INT = 0,
|
|
@ricerca nvarchar(250)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT *
|
|
FROM v_selCommesseAttive
|
|
WHERE (label LIKE '%' + @ricerca + '%')
|
|
AND value NOT IN
|
|
(
|
|
SELECT DISTINCT idxFase
|
|
FROM BazaarRisorse
|
|
WHERE Anno = @Anno
|
|
AND Sett = @Sett
|
|
)
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_baz_creaBulkByIdxFase
|
|
*
|
|
* crea in blocco tanti record di Bazaar Risorse quante le risorse disponibili per la fase, anno e settimana selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_BAZ_creaBulkByIdxFase
|
|
(
|
|
@Anno INT = 0,
|
|
@Sett INT = 0,
|
|
@idxFase INT = 0
|
|
)
|
|
AS
|
|
|
|
-- creo 1 record x ogni dip in Dip2ATR con un gruppo valido (non nullo/vuoto) x il set Anno/Sett/Fase
|
|
INSERT INTO BazaarRisorse(Anno,Sett,idxDipendente,idxFase,Allocazione)
|
|
SELECT @Anno, @Sett, idxDipendente, @idxFase, 0
|
|
FROM Dip2ATR
|
|
WHERE ISNULL(Gruppo,'') <>''
|
|
|
|
RETURN
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_baz_deleteByIdxFase
|
|
*
|
|
* elimina record di Bazaar Risorse data la fase, anno e settimana selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.05
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_BAZ_deleteByIdxFase
|
|
(
|
|
@Anno INT = 0,
|
|
@Sett INT = 0,
|
|
@idxFase INT = 0
|
|
)
|
|
AS
|
|
|
|
DELETE FROM BazaarRisorse
|
|
WHERE Anno = @Anno
|
|
AND Sett = @Sett
|
|
AND idxFase = @idxFase
|
|
|
|
RETURN
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_BAZ_getByAnnoSett
|
|
*
|
|
* recupera record di Bazaar Risorse per anno e settimana selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_BAZ_getByAnnoSett
|
|
(
|
|
@Anno int,
|
|
@Sett int
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT Anno, Sett, idxDipendente, idxFase, Allocazione, SchemaWeek, OreTot
|
|
FROM dbo.BazaarRisorse
|
|
WHERE Anno = @Anno AND Sett = @Sett
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_BAZ_getByAnnoSettDip
|
|
*
|
|
* recupera record di Bazaar Risorse per anno, settimana e dipendente selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_BAZ_getByAnnoSettDip
|
|
(
|
|
@Anno INT,
|
|
@Sett INT,
|
|
@idxDipendente INT = 1
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT Anno, Sett, idxDipendente, idxFase, Allocazione, SchemaWeek, OreTot
|
|
FROM dbo.BazaarRisorse
|
|
WHERE Anno = @Anno AND Sett = @Sett AND idxDipendente = @idxDipendente
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_baz_creaBulkByIdxFase
|
|
*
|
|
* crea in blocco tanti record di Bazaar Risorse quante le risorse disponibili per la fase, anno e settimana selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_BAZ_updateQuery
|
|
(
|
|
@Anno INT = 0,
|
|
@Sett INT = 0,
|
|
@idxDipendente INT = 0,
|
|
@idxFase INT = 0,
|
|
@Allocazione INT = 0
|
|
)
|
|
AS
|
|
|
|
-- creo 1 record x ogni dip in Dip2ATR con un gruppo valido (non nullo/vuoto) x il set Anno/Sett/Fase
|
|
UPDATE BazaarRisorse
|
|
SET Allocazione = @Allocazione
|
|
WHERE Anno = @Anno
|
|
AND Sett = @Sett
|
|
AND idxDipendente = @idxDipendente
|
|
AND idxFase = @idxFase
|
|
|
|
RETURN
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_createTallyTable
|
|
*
|
|
* crea una tally table delal dimensione desiderata
|
|
*
|
|
* mod: S.E.L. 2013.09.06
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_createTallyTable
|
|
(
|
|
@numVal INT = 100000
|
|
)
|
|
AS
|
|
IF EXISTS (SELECT * FROM sys.objects
|
|
WHERE object_id = OBJECT_ID(N'[dbo].[TallyTable]') AND type in (N'U'))
|
|
DROP TABLE [dbo].[TallyTable];
|
|
|
|
SELECT TOP(@numVal) IDENTITY(int,1,1) AS n
|
|
INTO TallyTable
|
|
FROM MASTER..spt_values a, MASTER..spt_values b;
|
|
|
|
CREATE UNIQUE CLUSTERED INDEX idx_1 ON TallyTable(n);
|
|
|
|
RETURN
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_D2ATR_getBazOrd
|
|
*
|
|
* recupera elenco dipendenti abilitati a bazaar ordinati x i gruppi validi
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
alter PROCEDURE stp_D2ATR_getBazOrd
|
|
|
|
AS
|
|
|
|
SELECT *
|
|
FROM Dip2ATR
|
|
WHERE ISNULL(Gruppo,'') <>''
|
|
ORDER BY Gruppo
|
|
|
|
RETURN
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSCom_getAttiveBySearchLike
|
|
*
|
|
* elenco commesse ATTIVE "formattato" by progetti con ricerca "like"
|
|
*
|
|
* mod : 2013.09.04
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_VSCom_getAttiveBySearchLike
|
|
(
|
|
@ricerca nvarchar(250)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT *
|
|
FROM v_selCommesseAttive
|
|
WHERE (label LIKE '%' + @ricerca + '%')
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSCom_getAttiveUnusedBySearchLike
|
|
*
|
|
* elenco commesse ATTIVE e non "usate" da programmazioen bazaar
|
|
*
|
|
* mod : 2013.09.04
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_VSCom_getAttiveUnusedBySearchLike
|
|
(
|
|
@Anno INT = 0,
|
|
@Sett INT = 0,
|
|
@ricerca nvarchar(250)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT *
|
|
FROM v_selCommesseAttive
|
|
WHERE (label LIKE '%' + @ricerca + '%')
|
|
AND value NOT IN
|
|
(
|
|
SELECT DISTINCT idxFase
|
|
FROM BazaarRisorse
|
|
WHERE Anno = @Anno
|
|
AND Sett = @Sett
|
|
)
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_VSCom_getBySearchLike
|
|
*
|
|
* elenco commesse "formattato" by progetti con ricerca "like"
|
|
*
|
|
* mod : 2013.05.25
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_VSCom_getBySearchLike
|
|
(
|
|
@ricerca nvarchar(250)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT *
|
|
FROM v_selCommesse
|
|
WHERE (label LIKE '%' + @ricerca + '%')
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(193, GETDATE())
|
|
GO
|
|
SELECT TOP 10 * FROM LogUpdateDb ORDER BY Versione DESC
|
|
GO
|