SELECT
K.ID,
K.SPISOVA_ZNACKA,
K.VS,

(SELECT `KS`.`STAV_ID` FROM `KAUZY_STAVY` `KS` WHERE `K`.`ID` = `KS`.`KAUZA_ID` ORDER BY `KS`.`DATUM_EDITACE` DESC LIMIT 1) AS `STAV_ID`,

`KTS`.`NAZEV`             AS `STAV`,
`KTS`.`PARENT_ID`         AS `STAV_PARENT_ID`,
`KTS2`.`NAZEV`            AS `STAV_PARENT`,
`KTS`.`UKONCOVACI`        AS `STAV_UKONCOVACI`,

(SELECT `KS`.`DATUM_EDITACE` FROM `KAUZY_STAVY` `KS` WHERE `K`.`ID` = `KS`.`KAUZA_ID` ORDER BY `KS`.`DATUM_EDITACE` DESC LIMIT 1) AS `DATUM_STAVU`,

K.POVINNY_ID,

`POV`.MAJOR_TYP_SUBJEKTU AS POVINNY_MAJOR_TYP_SUBJEKTU,
`POV`.PRAVNI_TYP_SUBJEKTU AS POVINNY_PRAVNI_TYP_SUBJEKTU,
`POV`.JMENO AS POVINNY_JMENO,
`POV`.PRIJMENI AS POVINNY_PRIJMENI,
`POV`.RODNE_PRIJMENI AS POVINNY_RODNE_PRIJMENI,
`POV`.TITUL_PRED AS POVINNY_TITUL_PRED,
`POV`.TITUL_ZA AS POVINNY_TITUL_ZA,
`POV`.DATUM_NAROZENI AS POVINNY_DATUM_NAROZENI,
`POV`.NAZEV_SPOLECNOSTI AS POVINNY_NAZEV_SPOLECNOSTI,
IF(`POV`.`PRIJMENI` > '' OR `POV`.`JMENO` > '',TRIM(CONCAT_WS('',`POV`.`PRIJMENI`,' ',`POV`.`JMENO`)),TRIM(`POV`.`NAZEV_SPOLECNOSTI`)) AS `POVINNY_CELE_JMENO`,
`POV`.ULICE AS POVINNY_ULICE,
`POV`.MESTO AS POVINNY_MESTO,
`POV`.PSC AS POVINNY_PSC,
`POV`.EMAIL AS POVINNY_EMAIL,
`POV`.TELEFON AS POVINNY_TELEFON,
`POV`.MOBIL AS POVINNY_MOBIL,
`POV`.ICO AS POVINNY_ICO,
`POV`.DATOVA_SCHRANKA AS POVINNY_DATOVA_SCHRANKA,

K.OPRAVNENY_ID,

`OPR`.MAJOR_TYP_SUBJEKTU AS OPRAVNENY_MAJOR_TYP_SUBJEKTU,
`OPR`.PRAVNI_TYP_SUBJEKTU AS OPRAVNENY_PRAVNI_TYP_SUBJEKTU,
`OPR`.JMENO AS OPRAVNENY_JMENO,
`OPR`.PRIJMENI AS OPRAVNENY_PRIJMENI,
`OPR`.RODNE_PRIJMENI AS OPRAVNENY_RODNE_PRIJMENI,
`OPR`.TITUL_PRED AS OPRAVNENY_TITUL_PRED,
`OPR`.TITUL_ZA AS OPRAVNENY_TITUL_ZA,
`OPR`.DATUM_NAROZENI AS OPRAVNENY_DATUM_NAROZENI,
`OPR`.NAZEV_SPOLECNOSTI AS OPRAVNENY_NAZEV_SPOLECNOSTI,
IF(`OPR`.`PRIJMENI` > '' OR `OPR`.`JMENO` > '',TRIM(CONCAT_WS('',`OPR`.`PRIJMENI`,' ',`OPR`.`JMENO`)),TRIM(`OPR`.`NAZEV_SPOLECNOSTI`)) AS `OPRAVNENY_CELE_JMENO`,
`OPR`.ULICE AS OPRAVNENY_ULICE,
`OPR`.MESTO AS OPRAVNENY_MESTO,
`OPR`.PSC AS OPRAVNENY_PSC,
`OPR`.EMAIL AS OPRAVNENY_EMAIL,
`OPR`.TELEFON AS OPRAVNENY_TELEFON,
`OPR`.MOBIL AS OPRAVNENY_MOBIL,
`OPR`.ICO AS OPRAVNENY_ICO,
`OPR`.DATOVA_SCHRANKA AS OPRAVNENY_DATOVA_SCHRANKA,

K.SOUD_ID,
K.SZ_SOUDU,

SOUD.NAZEV AS SOUD_NAZEV,
SOUD.NAZEV_2P AS SOUD_NAZEV_2P,
SOUD.NAZEV_3P AS SOUD_NAZEV_3P,
SOUD.ULICE AS SOUD_ULICE,
SOUD.MESTO AS SOUD_MESTO,
SOUD.MESTO_3P AS SOUD_MESTO_3P,
SOUD.PSC AS SOUD_PSC,
SOUD.ICO AS SOUD_ICO,
SOUD.BANKOVNI_UCET AS SOUD_BANKOVNI_UCET,
SOUD.TELEFON AS SOUD_TELEFON,
SOUD.MOBIL AS SOUD_MOBIL,
SOUD.EMAIL AS SOUD_EMAIL,
SOUD.VS AS SOUD_VS,
SOUD.ISAS AS SOUD_ISAS,
SOUD.INFOSOUD_JMENO AS SOUD_INFOSOUD_JMENO,
SOUD.DATOVA_SCHRANKA AS SOUD_DATOVA_SCHRANKA,

K.TYP_PAROVANI,
K.TYP_PLNENI,
K.ZPUSOB_PROVEDENI,
K.INSOLVENCE,
K.GESTOR,

PRAC1.CELE_JMENO AS GESTOR_CELE_JMENO,
PRAC1.EMAIL AS GESTOR_EMAIL,
PRAC1.TELEFON AS GESTOR_TELEFON,

K.VYKONAVATEL,

PRAC2.CELE_JMENO AS VYKONAVATEL_CELE_JMENO,
PRAC2.EMAIL AS VYKONAVATEL_EMAIL,
PRAC2.TELEFON AS VYKONAVATEL_TELEFON,


K.DATUM_ZAHAJENI_EXEKUCE,
K.DATUM_UKONCENI_EXEKUCE,
K.DATUM_DOLOZKY,
K.POZNAMKA,
K.DATUM_EDITACE,
K.AUTOR_ID

FROM KAUZY AS K
LEFT JOIN SUBJEKTY AS POV ON (K.POVINNY_ID = `POV`.ID)
LEFT JOIN SUBJEKTY AS OPR ON (K.OPRAVNENY_ID = `OPR`.ID)
LEFT JOIN SOUDY AS SOUD ON (K.SOUD_ID = SOUD.ID)
LEFT JOIN PROVOZOVNA_PRACOVNICI AS PRAC1 ON (K.GESTOR = PRAC1.ID)
LEFT JOIN PROVOZOVNA_PRACOVNICI AS PRAC2 ON (K.VYKONAVATEL = PRAC2.ID)
LEFT JOIN `KAUZY_TYPY_STAVU` `KTS` ON ( (SELECT `KS`.`STAV_ID` FROM `KAUZY_STAVY` `KS` WHERE `K`.`ID` = `KS`.`KAUZA_ID` ORDER BY `KS`.`DATUM_EDITACE` DESC LIMIT 1) = `KTS`.`ID`)
LEFT JOIN `KAUZY_TYPY_STAVU` `KTS2` ON (`KTS2`.`ID` = `KTS`.`PARENT_ID`)

LIMIT 1000

