SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE xprim14.fakttabulka_import AS
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE xprim14.partneri_dopln AS
  insert into xprim14.dwe_d_partner(partner, nazev, ulice, mesto, psc, okres, stat)
    select distinct ico, 'XXX', 'Ulice partnera', 'MØsto partnera', 'XXX XX', 'XX', 'XX'  
      from dw_vstup_data.dbo.dwe_import i
      where not exists
        (select 1 from xprim14.dwe_d_partner p where p.partner = i.ico);
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE xprim14.partneri_import AS

-- vytvorime docasne tabulky
-- to ze zacinaji na # znamena ze se samy po ukonceni transakce smazou

create table #partner
(
    ID_PARTNERA            nvarchar(50)                null    ,
    NAZEV_PARTNERA         nvarchar(35)                not null,
    ULICE                  nvarchar(35)                not null,
    MISTO                  nvarchar(35)                not null,
    OKRES                  nvarchar(35)                not null,
    PSC                    nvarchar(35)                not null,
    ID_STATU               nvarchar(3)                null    ,
    ID_OKRESU             nvarchar(3)                not null,
    ID_KRAJE               nvarchar(3)                null  
);

create table #KRAJ
(
    ID_KRAJE               nvarchar(3)           not null,
    NAZEV_KRAJE            nvarchar(35)          not null,
);

create table #OKRES
(
    ID_OKRESU              nvarchar(3)           not null,
    NAZEV_OKRESU           nvarchar(50)          not null,
    ID_KRAJE               nvarchar(3)           null,
);

create table #STAT
(
    ID_STATU               nchar(3)              not null,
    NAZEV_STATU            nchar(35)             not null,
);

-- nainportujeme vsechny 4 tabulky z primarniho zdroje
-- importem odstranime duplicity

insert into #partner 
  select id_partnera, min(nazev_partnera), min(ulice), min(misto), min(okres), min(psc), min(id_statu), min(id_okresu), min(id_kraje)
    from dw_vstup_data.dbo.dwe_d_partneri
      group by id_partnera;

insert into #stat 
  select id_statu, min(nazev_statu)
    from dw_vstup_data.dbo.dwe_d_staty
      group by id_statu;

insert into #okres 
  select id_okresu, min(nazev_okresu), 'XX' 
    from dw_vstup_data.dbo.dwe_d_okresy
      group by id_okresu;

insert into #kraj 
  select id_kraje, min(nazev_kraje)
    from dw_vstup_data.dbo.dwe_d_kraje
      group by id_kraje;

-- vsichni partneri se nastavi tak aby ukazovali na stat s nizsim ID
-- z mnoziny statu se stejnym jmenem (duplicitni je Rakousko s ID 'AT' a 'A')
update #partner
  set id_statu = 
    (select min(s.id_statu) from #stat s where s.nazev_statu = 
       (select s2.nazev_statu from #stat s2 where s2.id_statu = #partner.id_statu));

-- smazou se duplicitni staty s vyssim ID (tj. Rakousko s ID 'AT')
delete from #stat
  where id_statu = any (
    select s1.id_statu from #stat s1 where exists(
      select * from #stat s2 
        where s2.nazev_statu = s1.nazev_statu and s2.id_statu < s1.id_statu));

-- u partneru kteri maji nastaven stat '?' nebo stat neni nastaven nastavime 'XX' (neurcen)
update #partner set id_statu = 'XX' where id_statu = '?' or id_statu is null;

-- vsichni partneri se nastavi tak aby ukazovali na kraj s nizsim ID
-- z mnoziny kraju se stejnym jmenem (v danych datech takove zaznamy nejsou)
update #partner
  set id_kraje = 
    (select min(k.id_kraje) from #kraj k where nazev_kraje = 
       (select k2.nazev_kraje from #kraj k2 where k2.id_kraje = #partner.id_kraje));

-- smazou se duplicitni kraje s vyssim ID (v danych datech takove zaznamy nejsou)
delete from #kraj
  where id_kraje = any (
    select k1.id_kraje from #kraj k1 where exists(
      select * from #kraj k2 
        where k2.nazev_kraje = k1.nazev_kraje and k2.id_kraje < k1.id_kraje));

-- u partneru kteri maji nastavem kraj '?' (neznamy) nebo kraj neni nastaven  nastavime 'XX' (neurcen)
update #partner set id_kraje = 'XX' where id_kraje = '?' or id_kraje is null;
-- smazeme kraj '?'
delete from #kraj where id_kraje = '?' or id_kraje is null;

-- vsichni partneri se nastavi tak aby ukazovali na okres s nizsim ID
-- z mnoziny okresu se stejnym jmenem (duplicitni je Kladno s ID 'KL' a 'KD')
update #partner
  set id_okresu = 
    (select min(o.id_okresu) from #okres o where nazev_okresu = 
       (select o2.nazev_okresu from #okres o2 where o2.id_okresu = #partner.id_okresu));

-- smazou se duplicitni okresy s vyssim ID (tj. Kladno s ID 'KL')
delete from #okres
  where id_okresu = any (
    select o1.id_okresu from #okres o1 where exists(
      select * from #okres o2
        where o2.nazev_okresu = o1.nazev_okresu and o2.id_okresu < o1.id_okresu));

-- u partneru kteri maji nastaven okres '?' (neznamy) nebo okres neni nastaven nastavime 'XX' (neurcen)
update #partner set id_okresu = 'XX' where id_okresu = '?' or id_okresu is null;
-- smazeme okres '?'
delete from #okres where id_okresu = '?';

-- tam kde je jmeno okresu 'Praha%' a neni odkaz na okres
-- nastavim odkaz na okres 'Praha hl.m.'
update #partner 
  set id_okresu = (select id_okresu from #okres where #okres.nazev_okresu = 'Praha hl.m.')
  where left(okres, 5) = 'Praha' and id_okresu = 'XX' and 
    exists (select id_okresu from #okres where #okres.nazev_okresu = 'Praha hl.m.');

-- pokud jeste jsou v tabulce partneru odkazy na neexistujici okresy nastavime 'XX' (neurcen)
update #partner set id_okresu = 'XX' 
  where not id_okresu = any(select id_okresu from #okres);
-- to same pro kraje
update #partner set id_kraje = 'XX' 
  where not id_kraje = any(select id_kraje from #kraj);
-- to same pro staty
update #partner set id_statu = 'XX' 
  where not id_statu = any(select id_statu from #stat);

-- tam kde jeste neni id okresu se ho pokusime najit podle nazvu
-- staci nam shoda zacatku nazvum okresu u partnera s celym jmenem okresu v seznamu
update #partner 
  set id_okresu = (select id_okresu from #okres 
    where #okres.nazev_okresu = left(#partner.okres, len(#okres.nazev_okresu)))
  where id_okresu = 'XX' and 
    exists(select 1 from #okres where #okres.nazev_okresu = left(#partner.okres, len(#okres.nazev_okresu)));

-- docasna tabulka obsahujici cetnosti prirazeni kraj-okres
create table #partner_okres_kraj
(
    id integer not null identity, 
    id_okresu               nchar(3)              not null,
    id_kraje               nchar(3)              null,
    cetnost 	integer not null,
);

-- vypocitame cetnosti a vlozime do docasne tabulky
insert into #partner_okres_kraj (id_okresu, id_kraje, cetnost)
  select id_okresu, id_kraje, count(*) from #partner group by id_okresu, id_kraje;

delete from #partner_okres_kraj
  where id_okresu = 'XX' or id_kraje = 'XX';

-- v pripade ze k jednomu okresu je prirazeno vice kraju vymazeme ty kraje u nichz je
-- cetnost nizsi, pokud je stejna vymazame kraj s vyssim ID
delete from #partner_okres_kraj
  where 
    id = any (
    select o1.id from #partner_okres_kraj o1 where
      exists (select * from #partner_okres_kraj o2 
        where o1.id <> o2.id and o2.id_okresu = o1.id_okresu and 
          ((o2.cetnost > o1.cetnost) or
           (o2.cetnost = o1.cetnost and o2.id_kraje < o1.id_kraje))));

-- u radku kde je nastaven okres nastavime kraj ktery je nejpravdepodobnejsi
update #partner 
  set id_kraje = 
    (select c.id_kraje from #partner_okres_kraj c where c.id_okresu = #partner.id_okresu)
  where
    (id_okresu <> 'XX') and 
    exists (select c.id_kraje from #partner_okres_kraj c where c.id_okresu = #partner.id_okresu);

-- uz ji nepotrebujem
drop table #partner_okres_kraj

-- tam kde je nastaven kraj to musi byt CR 
update #partner
  set id_statu = 'CZ'
  where id_kraje <> 'XX';

-- vynulujeme prirazeni okes-kraj
update #okres
  set id_kraje = 'XX';

-- nastavime v tabulce okresu prirazeni ke kraji tak jak je u partneru
-- v tomto okamziku by u vsech partneru z kraje melo byt prirazeni jednotne
update #okres
  set id_kraje = (select distinct id_kraje from #partner where #partner.id_okresu = #okres.id_okresu)
  where id_okresu <> 'XX' and
    exists (select distinct id_kraje from #partner where #partner.id_okresu = #okres.id_okresu);

-- nastavime kraje u okresu kde jeste nejsou prirazeny
update #okres set id_kraje = 'JM' 
  where id_okresu = 'BI' or id_okresu = 'BM' or id_okresu = 'VY';
update #okres set id_kraje = 'SM' 
  where id_okresu = 'BR' or id_okresu = 'JE' or id_okresu = 'OV' or id_okresu = 'PV' or id_okresu = 'VS';
update #okres set id_kraje = 'SC' 
  where id_okresu = 'CV' or id_okresu = 'JN' or id_okresu = 'MO';
update #okres set id_kraje = 'ZC' 
  where id_okresu = 'PM' or id_okresu = 'SO';
update #okres set id_kraje = 'VC' 
  where id_okresu = 'TU' ;

-- vlozime 'nezname' okresy do kraju, ke kterym existuji partneri
-- u kterych zname jen kraj
insert into #okres(id_okresu, nazev_okresu, id_kraje)
  select 'X' + id_kraje, 'Nezn mì okres v kraji "' + rtrim(nazev_kraje) + '"', id_kraje
    from #kraj
    where exists (select 1 from #partner 
      where #partner.id_okresu = 'XX' and #partner.id_kraje <> 'XX' and #partner.id_kraje = #kraj.id_kraje);

-- u partneru kde nezname okres ale zname kraj nastavime
-- neznamy okres z prislusneho kraje
update #partner
  set id_okresu = 'X' + id_kraje
  where id_kraje <> 'XX' and id_okresu = 'XX';

-- doplnime okres/kraj pro cizi staty
insert into #kraj(id_kraje, nazev_kraje) values('QQ', 'Ciz¡ zemØ');
insert into #okres(id_okresu, nazev_okresu, id_kraje) values('XQQ', 'Ciz¡ zemØ', 'QQ');

update #partner 
  set id_okresu = 'XQQ' 
  where id_statu <> 'CZ' and id_statu <> 'XX';


-- smazeme staty, kraje a okresy, ktere nejsou pouzivany
--delete from #stat
--  where not id_statu = any(select distinct id_statu from #partner);
--delete from #kraj 
--  where not id_kraje = any(select distinct id_kraje from #partner);
--delete from #okres
--  where not id_okresu = any(select distinct id_okresu from #partner);

-- naimportujeme do cilovych tabulek

insert into xprim14.dwe_d_stat(stat, nazev)
  select id_statu, nazev_statu from #stat;

insert into xprim14.dwe_d_kraj(kraj, nazev)
  select id_kraje, nazev_kraje from #kraj;

insert into xprim14.dwe_d_okres(okres, nazev, kraj)
  select id_okresu, nazev_okresu, id_kraje from #okres;

insert into xprim14.dwe_d_partner(partner, nazev, ulice, mesto, psc, okres, stat)
  select id_partnera, nazev_partnera, ulice, misto, psc, id_okresu, id_statu from #partner;

-- a je to!
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE xprim14.spoluprace_vytvor AS

insert into dwe_d_spoluprace(spolecnost, cinnost, stredisko, popis)
  select distinct 
    replace(spolecnost, '?', -1) as spolecnost, 
    cinnost, 
    replace(stredisko, '?', -1) as stredisko, 
    'SpoleŸnost '+cast(replace(spolecnost, '?', -1) as char(2))+'; Ÿinnost '+cast(cinnost as char(3))+'; stýedisko '+cast(replace(stredisko, '?', -1) as char(2)) as popis
  from dw_vstup_data.dbo.dwe_import i
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE xprim14.typ_transakce_dopln AS

-- doplnime neexistujici typy transakce
insert into dwe_d_typ_transakce(typ_transakce, nazev, skupina_transakce, nazev_skupiny)
  (select distinct i.typ_tran, 'Transakce '+i.typ_tran, -1, 'Nezn m ' 
     from dw_vstup_data.dbo.dwe_import i
     where not exists (select 1 from dwe_d_typ_transakce t where t.typ_transakce = i.typ_tran));

-- doplnime nazvy k nepopsanym transaknim a dalsi atributy
update dwe_d_typ_transakce
  set nazev = 'Transakce '+typ_transakce
  where nazev is null or nazev = '' or nazev = '?';

update dwe_d_typ_transakce
  set skupina_transakce = -1
  where skupina_transakce is null;

update dwe_d_typ_transakce
  set nazev_skupiny = 'Nezn m '
  where nazev_skupiny is null or nazev_skupiny ='';
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

