/******  Script Date: 26.06.2017 17:02:45 ******/
--  rozwiązanie do 131484,  wersja heavy, na bazie z zapisy 2M  trwa 12 minut.
--  przed wykonaniem skryptu należy wykonać i zachować kopię bezpieczeństwa (backup)

--BEGIN TRY
--    BEGIN TRANSACTION;

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [FK].[zapisyNew](
   [rokId] [FK].[tRokID] NOT NULL,
   [id] [FK].[tID] IDENTITY(1,1) NOT NULL,
   [waluta] [FK].[tWaluta] NULL,
   [tabela] [FK].[tID] NULL,
   [idDlaRozliczen] [int] NOT NULL,
   [dokument] [FK].[tID] NULL,
   [pozycja] [smallint] NOT NULL,
   [rozbicie] [smallint] NOT NULL,
   [typopisu] [tinyint] NOT NULL,
   [typRozb] [tinyint] NOT NULL,
   [kwota] [FK].[tKwota] NOT NULL,
   [wkwota] [FK].[tKwota] NOT NULL,
   [zapisRownolegly] [FK].[tZapisRownolegly] NOT NULL,
   [strona] [FK].[tStrona] NOT NULL,
   [automat] [tinyint] NOT NULL,
   [kontoRap] [tinyint] NOT NULL,
   [dataokr] [FK].[tData] NULL,
   [dataZap] [FK].[tData] NULL,
   [opis] [varchar](59) NULL,
   [synt] [FK].[tSyntetyka] NULL,
   [poz1] [FK].[tPoziom] NOT NULL,
   [poz2] [FK].[tPoziom] NOT NULL,
   [poz3] [FK].[tPoziom] NOT NULL,
   [poz4] [FK].[tPoziom] NOT NULL,
   [poz5] [FK].[tPoziom] NOT NULL,
   [kurs] [float] NOT NULL,
   [przeks] [smallint] NOT NULL,
   [numerDok] [FK].[tDokSymbol] NULL,
   [nrRozbKP] [FK].[tZapisRozbicieKP] NOT NULL,
   [typkursu] [FK].[tTypKurs] NOT NULL,
   [przeksKR] [smallint] NOT NULL,
   [kursEuro] [FK].[tKurs] NOT NULL,
   [przeksKwota] [FK].[tKwota] NOT NULL,
   [przeksKurs] [FK].[tKurs] NOT NULL,
   [przeksData] [FK].[tData] NULL,
   [kontraktId] [FK].[tID] NULL,
   [zapisVat] [tinyint] NOT NULL,
   [uid] [FK].[tGUID] NOT NULL,
   [dataKPKW] [FK].[tData] NULL
)

ALTER TABLE [FK].[zapisyNew] ADD  CONSTRAINT [PK_zapisy_22] PRIMARY KEY CLUSTERED
(
   [rokId] ASC,
   [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]


GO

SET IDENTITY_INSERT [fk].[zapisyNew] ON


INSERT INTO [fk].[zapisyNew]
( rokId, id, waluta, tabela, idDlaRozliczen, dokument, pozycja, rozbicie, typopisu, typRozb, kwota, wkwota, zapisRownolegly, strona, automat, kontoRap, dataokr, dataZap, opis, synt, poz1, poz2, poz3, poz4, poz5, kurs, przeks, numerDok, nrRozbKP, typkursu, przeksKR, kursEuro, przeksKwota, przeksKurs, przeksData, kontraktId, zapisVat, uid, dataKPKW)

select rokId, id, waluta, tabela, idDlaRozliczen, dokument, pozycja, rozbicie, typopisu, typRozb, kwota, wkwota, zapisRownolegly
, strona, automat, kontoRap, dataokr, dataZap, opis, synt, poz1, poz2, poz3, poz4, poz5, kurs, przeks, numerDok, nrRozbKP, typkursu
, przeksKR, kursEuro, przeksKwota, przeksKurs, przeksData, kontraktId, zapisVat, uid, dataKPKW from [FK].[zapisy]


GO

---  1:16 !!
-- end copy here

-- usuwanie obiektów z zapisy:
ALTER TABLE [FK].[zapisy] DROP  CONSTRAINT [DF_zapisy_przeksKwota]
GO

ALTER TABLE [FK].[zapisy] DROP  CONSTRAINT [DF_zapisy_przeksKurs]
GO

ALTER TABLE [FK].[zapisy] DROP  CONSTRAINT [DF_zapisy_zapisVat]
GO

ALTER TABLE [FK].[zapisy] DROP  CONSTRAINT [DF_zapisy_uid]
GO

ALTER TABLE [FK].[zapisy] DROP CONSTRAINT [FK_zapisy_dokumenty_2]
GO

ALTER TABLE [FK].[zapisy] DROP CONSTRAINT [FK_zapisy_FROK]
GO

ALTER TABLE [FK].[zapisy] DROP CONSTRAINT [FK_zapisy_kontraktId]
GO

ALTER TABLE [FK].[zapisy] DROP CONSTRAINT [FK_zapisy_kursy]
GO

ALTER TABLE [FK].[zapisy] DROP CONSTRAINT [FK_zapisy_waluty]
GO

ALTER TABLE [FK].[zapisy] DROP CONSTRAINT [UQ_zapisy_DokIdDlaRozliczen_2]
GO

ALTER TABLE [FK].[zapisy] DROP CONSTRAINT [UQ_zapisy_DokPozycjaRozbicie_2]
GO

---  must
ALTER TABLE [fk].[cechy_zap]  DROP CONSTRAINT [FK_cechy_zap_zapisy_2]
GO
ALTER TABLE [fk].[paczki]  DROP CONSTRAINT [FK_paczki_zapisy_2]
GO
ALTER TABLE [dim].[Divisions_fk] DROP CONSTRAINT [FK_zapisy_Divisions_fk]
GO


---

    EXECUTE sp_rename N'FK.zapisy', N'zapisyOLD','OBJECT';
     

  EXECUTE sp_rename N'FK.zapisyNew', N'zapisy','OBJECT';


--  dodawanie obiektów na zapisy (New)
ALTER TABLE [FK].[zapisy] ADD  CONSTRAINT [DF_zapisy_przeksKwota]  DEFAULT ((0)) FOR [przeksKwota]
GO

ALTER TABLE [FK].[zapisy] ADD  CONSTRAINT [DF_zapisy_przeksKurs]  DEFAULT ((0)) FOR [przeksKurs]
GO

ALTER TABLE [FK].[zapisy] ADD  CONSTRAINT [DF_zapisy_zapisVat]  DEFAULT ((0)) FOR [zapisVat]
GO

ALTER TABLE [FK].[zapisy] ADD  CONSTRAINT [DF_zapisy_uid]  DEFAULT (newid()) FOR [uid]
GO


ALTER TABLE [FK].[zapisy] ADD  CONSTRAINT [UQ_zapisy_DokIdDlaRozliczen_2] UNIQUE NONCLUSTERED
(
   [rokId] ASC,
   [dokument] ASC,
   [idDlaRozliczen] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [UQ_zapisy_DokPozycjaRozbicie_2] ON [FK].[zapisy]
(
    [rokId] ASC,
    [dokument] ASC,
    [pozycja] ASC,
    [rozbicie] ASC
)
WHERE ([dokument] IS NOT NULL)
     WITH (FILLFACTOR = 100, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


--
ALTER TABLE [FK].[zapisy]  WITH CHECK ADD  CONSTRAINT [FK_zapisy_dokumenty_2] FOREIGN KEY([rokId], [dokument])
REFERENCES [FK].[dokumenty] ([rokId], [id])
GO

ALTER TABLE [FK].[zapisy] CHECK CONSTRAINT [FK_zapisy_dokumenty_2]
GO

ALTER TABLE [FK].[zapisy]  WITH CHECK ADD  CONSTRAINT [FK_zapisy_FROK] FOREIGN KEY([rokId])
REFERENCES [FK].[FROK] ([rokId])
GO

ALTER TABLE [FK].[zapisy] CHECK CONSTRAINT [FK_zapisy_FROK]
GO

ALTER TABLE [FK].[zapisy]  WITH CHECK ADD  CONSTRAINT [FK_zapisy_kontraktId] FOREIGN KEY([kontraktId])
REFERENCES [FK].[kontrakty] ([id])
GO

ALTER TABLE [FK].[zapisy] CHECK CONSTRAINT [FK_zapisy_kontraktId]
GO

ALTER TABLE [FK].[zapisy]  WITH CHECK ADD  CONSTRAINT [FK_zapisy_kursy] FOREIGN KEY([tabela])
REFERENCES [FK].[kursy] ([id])
GO

ALTER TABLE [FK].[zapisy] CHECK CONSTRAINT [FK_zapisy_kursy]
GO

ALTER TABLE [FK].[zapisy]  WITH CHECK ADD  CONSTRAINT [FK_zapisy_waluty] FOREIGN KEY([waluta])
REFERENCES [FK].[waluty] ([skrot])
GO

ALTER TABLE [FK].[zapisy] CHECK CONSTRAINT [FK_zapisy_waluty]
GO

---===  przywrócenie indeksów

CREATE NONCLUSTERED INDEX [idxZapisy_kontr_aktywny] ON [FK].[zapisy]
(
   [rokId] ASC,
   [synt] ASC,
   [poz1] ASC,
   [poz2] ASC,
   [poz3] ASC,
   [poz4] ASC,
   [poz5] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO


SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [idxZapisy_kontraktId_opis] ON [FK].[zapisy]
(
   [kontraktId] ASC,
   [opis] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idxZapisy_przeks_2] ON [FK].[zapisy]
(
   [rokId] ASC,
   [synt] ASC,
   [poz1] ASC,
   [poz2] ASC,
   [poz3] ASC,
   [poz4] ASC,
   [poz5] ASC,
   [przeks] ASC,
   [waluta] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [idxZapisy_rokId_dokument] ON [FK].[zapisy]
(
   [rokId] ASC,
   [dokument] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO


CREATE NONCLUSTERED INDEX [idxZapisy_rokId_dokument_idDlaRozliczen_opis] ON [FK].[zapisy]
(
   [rokId] ASC,
   [dokument] ASC,
   [idDlaRozliczen] ASC,
   [opis] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [idxZapisy_rokId_dokument_konto_opis]    Script Date: 28.06.2017 10:03:47 ******/
CREATE NONCLUSTERED INDEX [idxZapisy_rokId_dokument_konto_opis] ON [FK].[zapisy]
(
   [rokId] ASC,
   [dokument] ASC,
   [synt] ASC,
   [poz1] ASC,
   [poz2] ASC,
   [poz3] ASC,
   [poz4] ASC,
   [poz5] ASC,
   [idDlaRozliczen] ASC,
   [strona] ASC,
   [opis] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [idxZapisy_tabela] ON [FK].[zapisy]
(
   [tabela] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO


CREATE UNIQUE NONCLUSTERED INDEX [idxZapisy_uid] ON [FK].[zapisy]
(
   [uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [idxZapisy_virtualne_2] ON [FK].[zapisy]
(
   [rokId] ASC,
   [synt] ASC,
   [poz1] ASC,
   [poz2] ASC,
   [poz3] ASC,
   [poz4] ASC,
   [poz5] ASC,
   [dataokr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [idxZapisy_waluta] ON [FK].[zapisy]
(
   [waluta] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO


DROP TRIGGER [FK].[trZapisy_insert_update]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [FK].[trZapisy_insert_update]
ON [FK].[zapisy]
FOR INSERT, UPDATE
AS
    IF UPDATE([opis]) OR UPDATE([dokument])
    BEGIN
        --obsluga kontraktow
        UPDATE [zapisy] SET
            [kontraktId] = CASE WHEN z.[opis] LIKE '%#%' THEN
                CASE WHEN d.zrodlo = 0 THEN
                  --dla ksiąg celowa konwersja na 0 jesli nie znaleziono, spowoduje to blad zapisu!!!
                  ISNULL((SELECT [id] FROM [kontrakty] WHERE [numer] = SUBSTRING(z.[opis], 1, CHARINDEX('#', z.[opis]) - 1)), 0)
                ELSE
                  -- dla bufora mozna zupdatowac zapis - kontraktID moze być null - wtedy powstanie błędny kontrakt
                  (SELECT [id] FROM [kontrakty] WHERE [numer] = SUBSTRING(z.[opis], 1, CHARINDEX('#', z.[opis]) - 1))
                END
            ELSE
                NULL --czyscimy jesli nie podano kontraktu
            END
        FROM [zapisy] z
        INNER JOIN inserted ON z.[rokId] = inserted.[rokId] AND z.[id] = inserted.[id]
        INNER JOIN [dokumenty] d ON z.[rokId] = d.[rokId] AND z.[dokument] = d.[id]
        WHERE d.[zrodlo] = 0 OR d.[zrodlo] = 1
    END
GO

ALTER TABLE [FK].[zapisy] ENABLE TRIGGER [trZapisy_insert_update]
GO

--


ALTER TABLE [FK].[zapisyOLD] DROP CONSTRAINT [PK_zapisy_2] WITH ( ONLINE = OFF )
GO

ALTER TABLE [FK].[zapisy] DROP CONSTRAINT [PK_zapisy_22] WITH ( ONLINE = OFF )
GO

ALTER TABLE [FK].[zapisy] ADD  CONSTRAINT [PK_zapisy_2] PRIMARY KEY CLUSTERED
(
   [rokId] ASC,
   [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO

--    zależne


ALTER TABLE [fk].[paczki]
  ADD CONSTRAINT [FK_paczki_zapisy_2] FOREIGN KEY ([rokId], [idZap]) REFERENCES [fk].[zapisy] ([rokId], [id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [fk].[cechy_zap]
  ADD CONSTRAINT [FK_cechy_zap_zapisy_2] FOREIGN KEY ([rokId], [ojciec]) REFERENCES [fk].[zapisy] ([rokId], [id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dim].[Divisions_fk]  WITH CHECK ADD  CONSTRAINT [FK_zapisy_Divisions_fk] FOREIGN KEY([YearID], [DocumentItemID])
REFERENCES [FK].[zapisy] ([rokId], [id])
ON DELETE CASCADE
GO

ALTER TABLE [dim].[Divisions_fk] CHECK CONSTRAINT [FK_zapisy_Divisions_fk]
GO


--== koniec dodawania

     DROP TABLE [FK].[zapisyOLD];


    /*Confirm the bit is set to 1*/
    --SELECT uses_ansi_nulls, *
    --FROM   sys.tables
    --WHERE  object_id = object_id('fk.zapisy')


-- EXECUTE sp_bindrule @rulename = N'[fk].[rID]', @objname = N'[fk].[zapisy].[id]';

    /*Data still there!*/
--   SELECT top 40 *
--   FROM [FK].[zapisy] order by id desc     --    4826979
   --select count(*) from fk.zapisy      -- 2331191   --

--    COMMIT TRANSACTION;

--END TRY

--BEGIN CATCH
--    IF XACT_STATE() <> 0
--      ROLLBACK TRANSACTION;

--    PRINT ERROR_MESSAGE();
--END CATCH;