/*1*/ CREATE TABLE `osoba_sluzba` (
	`osoba_id` INT(11) UNSIGNED NOT NULL,
	`sluzba_id` INT(11) UNSIGNED NOT NULL,
	PRIMARY KEY (`osoba_id`, `sluzba_id`),
	CONSTRAINT `FK__osoba` FOREIGN KEY (`osoba_id`) REFERENCES `osoba` (`id`),
	CONSTRAINT `sluzba` FOREIGN KEY (`sluzba_id`) REFERENCES `sluzba` (`id`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;
/*2*/ INSERT INTO `form_group` (`id`, `form_id`, `nazov`, `code`, `position`) VALUES (9, 262, 'Prístup k službám', 'osoba_pristup_k_sluzbam', 30);
/*3*/ INSERT INTO `form_element` (`id`, `form_group_id`, `form_element_type_id`, `db_table`, `db_column`, `db_multi_table`, `column_title`, `position`, `direct_to_db`, `css_class`, `direct_multi_label`, `direct_multi_data`) VALUES (43, 9, 6, 'osoba', 'sluzba_id', 'sluzba', 'Prístup služby', 30, 1, 'select2', 1, 1);
/*4*/ ALTER TABLE `sluzba`
	ADD COLUMN `gm_sluzba_id` INT(11) NULL DEFAULT NULL COMMENT 'gm spider ekvivalent' AFTER `nazov`;
/*5*/ UPDATE `sluzba` SET `gm_sluzba_id`=1 WHERE  `id`=10;
/*6*/ UPDATE `sluzba` SET `gm_sluzba_id`=2 WHERE  `id`=11;
/*7*/ UPDATE `sluzba` SET `gm_sluzba_id`=3 WHERE  `id`=12;
/*8*/ UPDATE `sluzba` SET `gm_sluzba_id`=4 WHERE  `id`=15;
/*9*/ UPDATE `sluzba` SET `gm_sluzba_id`=5 WHERE  `id`=14;
/*10*/ UPDATE `sluzba` SET `gm_sluzba_id`=6 WHERE  `id`=13;
/*11*/ UPDATE `sluzba` SET `gm_sluzba_id`=19 WHERE  `id`=18;
/*12*/ UPDATE `sluzba` SET `gm_sluzba_id`=17 WHERE  `id`=16;
/*13*/ CREATE ALGORITHM = UNDEFINED VIEW `obchodny_partner_aktivne_gm_sluzby` AS SELECT ops.* FROM obchodny_partner_sluzba ops 
JOIN obchodny_partner op
	ON op.id = ops.obchodny_partner_id
JOIN sluzba sl
	ON ops.sluzba_id = sl.id	
WHERE op.archiv = 0 
		AND (ops.na_dobu_neurcitu = 1 OR (NOW() BETWEEN ops.datum_od AND ops.datum_do))
		AND (sl.gm_sluzba_id IS NULL)   ;
/*14*/ UPDATE `sluzba` SET `nazov_web`='Správa majetkuOP' WHERE  `id`=7;
/*15*/ UPDATE `sluzba` SET `nazov_web`='Správa majetku' WHERE  `id`=7;
/*16*/ CREATE ALGORITHM = UNDEFINED VIEW `osoba_riadenie_dokumentacia_gm_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, gmsl.id AS sluzba_id, gmsl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id AND om.modul_id = 1 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code = 'riadenie_dokumentacie'
JOIN osoba_sluzba osl
	ON osl.osoba_id = os.id
JOIN sluzba sl
	ON osl.sluzba_id = sl.id AND sl.modul_id = mo.id
JOIN sluzba gmsl
	ON gmsl.id = sl.gm_sluzba_id	
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1
 ;
/*17*/ ALTER ALGORITHM = UNDEFINED VIEW `osoba_riadenie_dokumentacia_gm_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, gmsl.id AS sluzba_id, gmsl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code = 'riadenie_dokumentacie'
JOIN osoba_sluzba osl
	ON osl.osoba_id = os.id
JOIN sluzba sl
	ON osl.sluzba_id = sl.id AND sl.modul_id = mo.id
JOIN sluzba gmsl
	ON gmsl.id = sl.gm_sluzba_id	
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1
 ;
/*18*/ CREATE ALGORITHM = UNDEFINED VIEW `osoba_ostatne_moduly_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, sl.id AS sluzba_id, sl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code != 'riadenie_dokumentacie'
JOIN sluzba sl
	ON sl.modul_id = mo.id
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1 ;
/*19*/ ALTER ALGORITHM = UNDEFINED VIEW `osoba_ostatne_moduly_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, sl.id AS sluzba_id, sl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code != 'riadenie_dokumentacie'
JOIN sluzba sl
	ON sl.modul_id = mo.id
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1  ;
/*20*/ RENAME TABLE `osoba_ostatne_moduly_sluzby` TO `osoba_ostatne_moduly_priradene_sluzby`;
/*21*/ ALTER ALGORITHM = UNDEFINED VIEW `osoba_riadenie_dokumentacia_gm_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, gmsl.id AS sluzba_id, gmsl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code = 'riadenie_dokumentacie'
JOIN osoba_sluzba osl
	ON osl.osoba_id = os.id
JOIN sluzba sl
	ON osl.sluzba_id = sl.id AND sl.modul_id = mo.id
JOIN sluzba gmsl
	ON gmsl.id = sl.gm_sluzba_id	
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1  ;
/*22*/ RENAME TABLE `osoba_riadenie_dokumentacia_gm_sluzby` TO `osoba_riadenie_dokumentacia_priradene_gm_sluzby`;
/*23*/ ALTER ALGORITHM = UNDEFINED VIEW `osoba_ostatne_moduly_priradene_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, sl.id AS sluzba_id, sl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code != 'riadenie_dokumentacie'
JOIN sluzba sl
	ON sl.modul_id = mo.id
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1  ;
/*24*/ RENAME TABLE `osoba_ostatne_moduly_priradene_sluzby` TO `osoba_ostatne_moduly_priradene_gm_sluzby`;
/*25*/ ALTER ALGORITHM = UNDEFINED VIEW `osoba_ostatne_moduly_priradene_gm_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, os.obchodny_partner_id, sl.id AS sluzba_id, sl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code != 'riadenie_dokumentacie'
JOIN sluzba sl
	ON sl.modul_id = mo.id
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1  ;
/*26*/ ALTER ALGORITHM = UNDEFINED VIEW `osoba_riadenie_dokumentacia_priradene_gm_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, os.obchodny_partner_id, gmsl.id AS sluzba_id, gmsl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code = 'riadenie_dokumentacie'
JOIN osoba_sluzba osl
	ON osl.osoba_id = os.id
JOIN sluzba sl
	ON osl.sluzba_id = sl.id AND sl.modul_id = mo.id
JOIN sluzba gmsl
	ON gmsl.id = sl.gm_sluzba_id	
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1  ;
/*27*/ ALTER ALGORITHM = UNDEFINED VIEW `osoba_riadenie_dokumentacia_priradene_gm_sluzby` AS SELECT os.id AS osoba_id, os.nazov AS osoba_nazov, os.obchodny_partner_id, gmsl.id AS sluzba_id, gmsl.nazov AS sluzba_nazov FROM osoba os
JOIN osoba_modul om
	ON os.id = om.osoba_id 
JOIN modul mo
	ON mo.id = om.modul_id AND mo.code = 'riadenie_dokumentacie'
JOIN osoba_sluzba osl
	ON osl.osoba_id = os.id
JOIN sluzba sl
	ON osl.sluzba_id = sl.id AND sl.modul_id = mo.id
JOIN sluzba gmsl
	ON gmsl.id = sl.gm_sluzba_id	
WHERE os.visibility_type = 1 AND os.povoleny_pristup = 1  ;
/*28*/ RENAME TABLE `osoba_riadenie_dokumentacia_priradene_gm_sluzby` TO `osoba_riadenie_dokumentacie_priradene_gm_sluzby`;
/*29*/ ALTER ALGORITHM = UNDEFINED VIEW `obchodny_partner_aktivne_gm_sluzby` AS SELECT ops.* FROM obchodny_partner_sluzba ops 
JOIN obchodny_partner op
	ON op.id = ops.obchodny_partner_id
JOIN sluzba sl
	ON ops.sluzba_id = sl.id	
WHERE op.archiv = 0 
		AND ((ops.na_dobu_neurcitu = 1 AND ops.datum_od <= NOW()) OR (NOW() BETWEEN ops.datum_od AND ops.datum_do))
		AND (sl.gm_sluzba_id IS NULL)  ;
/*33*/ create view osoba_aktivna_gm_sluzba_view AS
SELECT oompgs.* FROM osoba_ostatne_moduly_priradene_gm_sluzby oompgs
JOIN obchodny_partner_aktivne_gm_sluzby opags
	ON oompgs.sluzba_id = opags.sluzba_id AND oompgs.obchodny_partner_id = opags.obchodny_partner_id
UNION
SELECT ordpgs.* FROM osoba_riadenie_dokumentacie_priradene_gm_sluzby ordpgs
JOIN obchodny_partner_aktivne_gm_sluzby opags2
	ON ordpgs.sluzba_id = opags2.sluzba_id AND ordpgs.obchodny_partner_id = opags2.obchodny_partner_id
;