CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `gisa`@`%` 
    SQL SECURITY DEFINER
VIEW `x_bpej` AS
    SELECT 
        `x_zpf_kn_bpej1`.`id_stavba` AS `id_stavba`,
        `x_zpf_kn_bpej1`.`stavba` AS `stavba`,
        `x_zpf_kn_bpej1`.`ku` AS `ku`,
        `x_zpf_kn_bpej1`.`obec` AS `obec`,
        `x_zpf_kn_bpej1`.`kraj` AS `kraj`,
        `x_zpf_kn_bpej1`.`id_etapa` AS `id_etapa`,
        `x_zpf_kn_bpej1`.`aktivni` AS `aktivni`,
        `x_zpf_kn_bpej1`.`pc` AS `pc`,
        `x_zpf_kn_bpej1`.`gp` AS `gp`,
        `x_zpf_kn_bpej1`.`pk` AS `pk`,
        `x_zpf_kn_bpej1`.`druhpoz` AS `druhpoz`,
        `x_zpf_kn_bpej1`.`druhpozkod` AS `druhpozkod`,
        CASE
            WHEN `x_zpf_kn_bpej1`.`druhpozkod` NOT IN (2 , 3, 4, 5, 6, 7, 8) THEN 99
            ELSE `x_zpf_kn_bpej1`.`druhpozkod`
        END AS `druhpoz_kod`,
        `x_zpf_kn_bpej1`.`bpej_cislo` AS `bpej`,
        CASE
            WHEN
                `x_zpf_kn_bpej1`.`typ_zabor` = 183
            THEN
                ROUND(`x_zpf_kn_bpej1`.`trvaly` / `x_zpf_kn_bpej1`.`bpej_sumavym` * `x_zpf_kn_bpej1`.`bpej_vym`,
                        0)
            ELSE 0
        END AS `trvaly`,
        CASE
            WHEN
                `x_zpf_kn_bpej1`.`typ_zabor` = 184
            THEN
                ROUND(`x_zpf_kn_bpej1`.`nad1rok` / `x_zpf_kn_bpej1`.`bpej_sumavym` * `x_zpf_kn_bpej1`.`bpej_vym`,
                        0)
            ELSE 0
        END AS `nad1rok`,
        `x_zpf_kn_bpej1`.`typ_nabyvatele` AS `typ_nabyvatele`
    FROM
        `gisa_mpe_unis`.`x_zpf_kn_bpej1`
    WHERE
        `x_zpf_kn_bpej1`.`bpej_cislo` IS NOT NULL