set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

create VIEW v_selCommRicerca
AS
SELECT        dbo.f_padLeft(dbo.DatiCommessa.NumeroCommessa, 4, '0') + '-' + dbo.f_padLeft(dbo.DatiCommessa.AnnoCommessa, 4, '0') AS value, 
                         dbo.f_padLeft(dbo.DatiCommessa.NumeroCommessa, 4, '0') + '-' + dbo.f_padLeft(dbo.DatiCommessa.AnnoCommessa, 4, '0') 
                         + ' | ' + dbo.AnagClienti.RagSociale COLLATE SQL_Latin1_General_CP1_CI_AS + ' | ' + dbo.DatiCommessa.NomeComm + ' (' + ISNULL(dbo.DatiCommessa.nickname,
                          '-') + ')' + ' [' + dbo.f_shortFormComm(dbo.DatiCommessa.NumeroCommessa, dbo.DatiCommessa.AnnoCommessa) + ']' AS label
FROM            dbo.DatiCommessa INNER JOIN
                         dbo.AnagFasi ON dbo.DatiCommessa.idxFase = dbo.AnagFasi.idxFase INNER JOIN
                         dbo.AnagProgetti ON dbo.AnagFasi.idxProgetto = dbo.AnagProgetti.idxProgetto INNER JOIN
                         dbo.AnagClienti ON dbo.AnagProgetti.idxCliente = dbo.AnagClienti.idxCliente
WHERE        (dbo.DatiCommessa.AnnoCommessa > 1900)
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 = 136
               Right = 230
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagFasi"
            Begin Extent = 
               Top = 6
               Left = 268
               Bottom = 136
               Right = 439
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagProgetti"
            Begin Extent = 
               Top = 6
               Left = 477
               Bottom = 136
               Right = 647
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagClienti"
            Begin Extent = 
               Top = 6
               Left = 685
               Bottom = 136
               Right = 855
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 17130
         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
', 'SCHEMA', 'dbo', 'VIEW', 'v_selCommRicerca';
go

exec sp_addextendedproperty 'MS_DiagramPane2', '      End
   End
End
', 'SCHEMA', 'dbo', 'VIEW', 'v_selCommRicerca';
go

exec sp_addextendedproperty 'MS_DiagramPaneCount', 2, 'SCHEMA', 'dbo', 'VIEW', 'v_selCommRicerca';
go

create VIEW v_selCommZeroMancanti
AS
SELECT DISTINCT 
                         '0000-' + dbo.f_padLeft(AnnoCommessa, 4, '0') AS value, '0000-' + dbo.f_padLeft(AnnoCommessa, 4, '0') 
                         + ' - senza commessa ' + ' [' + dbo.f_shortFormComm(0, AnnoCommessa) + ']' AS label
FROM            dbo.DatiCommessa
WHERE        (AnnoCommessa > 1900) AND (AnnoCommessa NOT IN
                             (SELECT        AnnoCommessa
                               FROM            dbo.DatiCommessa AS DatiCommessa_1
                               WHERE        (NumeroCommessa = 0)))
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 = 136
               Right = 230
            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_selCommZeroMancanti';
go

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

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_AF_clonaFaseAnc
*
* clona una fase ANCESTOR e le relative sottofasi dell'albero + DATI COMMESSA
*
* mod: S.E.L. 2013.02.12
*
**********************************************************/
alter PROCEDURE stp_AF_clonaFaseAnc
(
    @idxProgetto        INT, -- progetto di destinazione
    @Original_idxFase   INT  -- fase da cui clonare
)
AS
SET NOCOUNT OFF;

BEGIN tran

    DECLARE @idxFaseAncest  INT = 0
    DECLARE @nextNum        INT = 9001 -- default a > 9000 x controllo errori!
    DECLARE @anno           INT = YEAR(GETDATE())
    DECLARE @nomeFase       NVARCHAR(50)    = '' -- da calcolare!!!

    BEGIN
        -- calcolo nuova commessa
        SELECT      @nextNum = ISNULL(MAX(NumeroCommessa),0) + 1
        FROM        DatiCommessa
        WHERE       AnnoCommessa = @anno
    END
    
    SELECT      @nomeFase = dbo.f_padLeft(@nextNum, 4, '0') + '-' + dbo.f_padLeft(@anno, 4, '0')

    -- inserisco fase ancestor
    INSERT INTO AnagFasi(codFase, idxProgetto, idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo, codClasse, budgetTime, budgetMoney)
    SELECT      '', @idxProgetto, idxFaseAncest, @nomeFase, descrizioneFase, enableTime, enableMoney, Attivo, codClasse, budgetTime, budgetMoney
    FROM        AnagFasi
    WHERE	    idxFase = @Original_idxFase

    SET  @idxFaseAncest = SCOPE_IDENTITY()

    -- inserisco dati commessa
    INSERT INTO DatiCommessa(idxFase, AnnoCommessa, NumeroCommessa, DataApertura)
        VALUES              (@idxFaseAncest, @anno, @nextNum, GETDATE())

    -- inserisco (sotto)fasi
    INSERT INTO AnagFasi(codFase, idxProgetto, idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo, codClasse, budgetTime, budgetMoney)
    SELECT      '', @idxProgetto, @idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo, codClasse, budgetTime, budgetMoney
    FROM        AnagFasi
    WHERE	    idxFaseAncest = @Original_idxFase

COMMIT tran
    
SELECT * FROM AnagFasi WHERE idxFase = @idxFaseAncest OR idxFaseAncest = @idxFaseAncest
go

commit;
go


-- update VALORI
set xact_abort on
go

begin transaction
go

UPDATE dbo.DatiCommessa SET 
  nickname=N'-'
  WHERE idxFase=8557
UPDATE dbo.DatiCommessa SET 
  nickname=N'-', CapoCommessa=N'Luca Bentoglio', Importo=1664
  WHERE idxFase=8588
UPDATE dbo.DatiCommessa SET 
  nickname=N'-', CapoCommessa=N'Michela Ratti', Importo=4000
  WHERE idxFase=8590
INSERT INTO dbo.DatiCommessa
  (idxFase, AnnoCommessa, NumeroCommessa, nickname, CheckOk, CapoCommessa, SQC, Pubblico, EnteProcuratore, Importo, DataApertura, rifOfferta, urlOfferta, rifOrdine, urlOrdine, Indirizzo, Note)
  VALUES (8594, 1900, 1, NULL, 0, N'-', 0, 0, NULL, 0, '2013-08-28 12:26:44.827', NULL, NULL, NULL, NULL, NULL, NULL)
INSERT INTO dbo.DatiCommessa
  (idxFase, AnnoCommessa, NumeroCommessa, nickname, CheckOk, CapoCommessa, SQC, Pubblico, EnteProcuratore, Importo, DataApertura, rifOfferta, urlOfferta, rifOrdine, urlOrdine, Indirizzo, Note)
  VALUES (8595, 1900, 2, NULL, 0, N'-', 0, 0, NULL, 0, '2013-08-28 12:26:46.717', NULL, NULL, NULL, NULL, NULL, NULL)
go

commit transaction
go





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