set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

create VIEW v_fornitoriCla
AS
SELECT        Codice, Descrizione AS Fornitore, Classificazione
FROM            XPANDING.SciP.dbo.fornitori
WHERE        (NOT (Classificazione IS NULL))
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 = "fornitori (XPANDING.SciP.dbo)"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 136
               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 = 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_fornitoriCla';
go

exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_fornitoriCla';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

alter VIEW v_selFornitoriCla
AS
SELECT        Codice AS value, Descrizione AS label, Classificazione AS conditio
FROM            XPANDING.SciP.dbo.fornitori
WHERE        (NOT (Classificazione IS NULL))
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 = "fornitori (XPANDING.SciP.dbo)"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 136
               Right = 223
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 20
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         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_selFornitoriCla';
go

commit;
go

set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_IR_importSettimana
*
* importa i dati di registro attivitā x anno e settimana selezionate
*
* mod: S.E.L. 2013.09.04
*
**********************************************************/
alter PROCEDURE stp_IR_importSettimana
(
    @Anno           INT = 0,
    @Settimana      INT = 0
)
AS

DECLARE @InizioAnno      AS DATETIME;
DECLARE @SettInizioAnno  AS INT;
DECLARE @InizioSettimana AS DATETIME;
DECLARE @FineSettimana   AS DATETIME;

-- CALCOLO DIFFERENZA SETTIMANA DA GIORNO 0 A INIZIO ANNO
SET @InizioAnno     = CONVERT( DATETIME, CONVERT(CHAR(4), @anno ) + '0101' );
SET @SettInizioAnno = datediff(week, 0, @InizioAnno);

-- Calcolo il giorno di inizio settimana richiesta e il primo gg della settimana successiva
SET @InizioSettimana = dateadd(week, @SettInizioAnno + @Settimana -1 , 0 );
SET @FineSettimana   = dateadd(week, @SettInizioAnno + @Settimana , 0 );

-- con merge ( query corretta )
WITH cte AS   -- calcolo totale ore lavorate x dipendente / fase x la settimana richiesta
(   SELECT	@Anno AS Anno, @Settimana AS Sett, ra.idxDipendente
            , CASE WHEN af.idxFaseAncest = 0 THEN af.idxFase ELSE af.idxFaseAncest END AS idxFaseAncest
            , ROUND(SUM(oreTot),0) AS OreTot
    FROM	dbo.RegAttivita AS ra 
    INNER JOIN dbo.AnagFasi AS af   ON ra.idxFAse=af.idxFase
    INNER JOIN dbo.Dip2ATR  AS d2a  ON d2a.idxDipendente=ra.idxDipendente
    WHERE inizio >= @InizioSettimana AND inizio < @FineSettimana 
          AND ISNULL(d2a.Gruppo,'') <> ''   -- tolgo i dipendenti senza un gruppo assegnato
    GROUP BY CASE WHEN af.idxFaseAncest = 0 THEN af.idxFase ELSE af.idxFaseAncest END, ra.idxDipendente
)
MERGE dbo.ImpiegoRisorse AS target
USING cte AS source
    ON     target.Anno = source.Anno 
       AND target.Sett = source.Sett 
       AND target.idxDipendente = source.idxDipendente
       AND target.idxFase = source.idxFaseAncest

WHEN MATCHED THEN                 -- se esiste riga aggiorno
        UPDATE SET target.OreTot = source.OreTot
WHEN NOT MATCHED BY TARGET THEN	  -- se non esiste in destinazione inserisco record
	    INSERT (Anno,Sett,idxDipendente,idxFase , OreTot)
	    VALUES (source.Anno,source.Sett,source.idxDipendente,source.idxFaseAncest , source.OreTot)

WHEN NOT MATCHED BY SOURCE 
     AND target.Anno = @Anno AND target.Sett = @Settimana THEN -- se esiste in destinazione ma non nella sorgente cancello
    DELETE;
-- OUTPUT $action, inserted.*, deleted.*;  -- da usare x test e vedere i dati modificati


/*
-- con merge
WITH cte AS   -- calcolo totale ore lavorate x dipendente / fase x la settimana richiesta
(   SELECT	@Anno AS Anno, @Settimana AS Sett, ra.idxDipendente
            , CASE WHEN af.idxFaseAncest = 0 THEN af.idxFase ELSE af.idxFaseAncest END AS idxFaseAncest
            , SUM(oreTot) AS OreTot
    FROM	dbo.RegAttivita AS ra 
    INNER JOIN dbo.AnagFasi AS af   ON ra.idxFAse=af.idxFase
    INNER JOIN dbo.Dip2ATR  AS d2a  ON d2a.idxDipendente=ra.idxDipendente
    WHERE DATEPART(wk, inizio) = @Settimana AND YEAR(inizio) = @Anno 
          AND ISNULL(d2a.Gruppo,'') <> ''   -- tolgo i dipendenti senza un gruppo assegnato
    GROUP BY CASE WHEN af.idxFaseAncest = 0 THEN af.idxFase ELSE af.idxFaseAncest END, ra.idxDipendente
)
MERGE dbo.ImpiegoRisorse AS target
USING cte AS source
    ON     target.Anno = source.Anno 
       AND target.Sett = source.Sett 
       AND target.idxDipendente = source.idxDipendente
       AND target.idxFase = source.idxFaseAncest

WHEN MATCHED THEN                 -- se esiste riga aggiorno
        UPDATE SET target.OreTot = source.OreTot
WHEN NOT MATCHED BY TARGET THEN	  -- se non esiste in destinazione inserisco record
	    INSERT (Anno,Sett,idxDipendente,idxFase , OreTot)
	    VALUES (source.Anno,source.Sett,source.idxDipendente,source.idxFaseAncest , source.OreTot)
WHEN NOT MATCHED BY SOURCE THEN   -- se esiste in destinazione ma non nella sorgente cancello
    DELETE;
-- OUTPUT $action, inserted.*, deleted.*;  -- da usare x test e vedere i dati modificati


-- **** questa č quella schifosa di SAM
-- svuoto eventuali record precedenti...
DELETE
FROM    ImpiegoRisorse
WHERE   Anno    = @Anno
    AND Sett    = @Settimana

-- creo record...
INSERT INTO ImpiegoRisorse(Anno,Sett,idxDipendente,idxFase,OreTot) 
SELECT	@Anno, @Settimana, idxDipendente, af.idxFaseAncest, SUM(oreTot) AS OreTot
FROM	RegAttivita ra INNER JOIN AnagFasi af on ra.idxFAse=af.idxFase
WHERE	DATEPART(wk, inizio) = @Settimana AND YEAR(inizio) = @Anno 
GROUP BY af.idxFaseAncest, idxDipendente
*/


RETURN
go

create PROCEDURE stp_VSF_getBySearchLike
(
	@ricerca nvarchar(200)
)
AS
	SET NOCOUNT ON;
SELECT        value, label, conditio
FROM            v_selFornitoriCla
WHERE        (label LIKE '%' + @ricerca + '%')
go

commit;
go









-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(216, GETDATE())
GO
SELECT TOP 10 * FROM LogUpdateDb ORDER BY Versione DESC
GO