--- ARCHIV ---
SELECT 
CASE
	WHEN bs.station_name_prefix != '' THEN CONCAT(bs.station_name_prefix, ' ', bs.station_name)
	ELSE bs.station_name
END
 as 'Název (s předložkou)',
bs.station_name_total  as 'Název (total)',
CASE
	WHEN bs.station_name like '% CS' or bs.station_name like '% CS-N'  THEN 'Archiv'
	WHEN bs.date_pozastaveni_do IS NOT NULL or bs.date_pozastaveni_od IS NOT NULL THEN 'Pozastaveno'
	WHEN NOT EXISTS (
		    SELECT 1
		    FROM individual i
		    WHERE i.id_breeding_station = bs.id_breeding_station
		) THEN 'Nerozhodnuto'
	WHEN NOT EXISTS (
		    SELECT 1
		    FROM individual i
		    WHERE i.id_breeding_station = bs.id_breeding_station
		    	AND i.id_individual not in (SELECT ie.id_individual FROM individual_export ie)
		  ) THEN 'Nerozhodnuto'
	WHEN NOT EXISTS (
		    SELECT 1
		    FROM individual i
		    WHERE i.id_breeding_station = bs.id_breeding_station
		    	AND i.id_individual not in (SELECT ie.id_individual FROM individual_exam ie)
		  ) THEN 'Nerozhodnuto'
	WHEN bs.station_street = '' and bs.station_city = '' and bs.station_psc = '' THEN 'Archiv'
	WHEN bs.is_breeding_station_cmku = false THEN 'Archiv'
	ELSE 'Platná'
END as 'Stav',
CASE
	WHEN bs.station_name like '% CS' or bs.station_name like '% CS-N'  THEN 'Končící na CS / CS-N'
	WHEN bs.date_pozastaveni_do IS NOT NULL AND bs.date_pozastaveni_od IS NOT NULL THEN CONCAT(DATE_FORMAT(bs.date_pozastaveni_od,'%d/%m/%Y'), ' - ', DATE_FORMAT(bs.date_pozastaveni_do,'%d/%m/%Y'))
	WHEN bs.date_pozastaveni_od IS NOT NULL THEN DATE_FORMAT(bs.date_pozastaveni_od,'%d/%m/%Y')
	WHEN NOT EXISTS (
		    SELECT 1
		    FROM individual i
		    WHERE i.id_breeding_station = bs.id_breeding_station
		) THEN 'Nemá pod sebou žádné jedince'
	WHEN NOT EXISTS (
		    SELECT 1
		    FROM individual i
		    WHERE i.id_breeding_station = bs.id_breeding_station
		    	AND i.id_individual not in (SELECT ie.id_individual FROM individual_export ie)
		  )   THEN 'Psi pouze v exportech'
	WHEN NOT EXISTS (
		    SELECT 1
		    FROM individual i
		    WHERE i.id_breeding_station = bs.id_breeding_station
		    	AND i.id_individual not in (SELECT ie.id_individual FROM individual_exam ie)
		  )  THEN 'Psi pouze ve zkouškách'
	WHEN bs.station_street = '' and bs.station_city = '' and bs.station_psc = '' THEN 'Bez adresy'
	WHEN bs.is_breeding_station_cmku = false THEN 'Není spravováno ČMKU'
	ELSE ''
END as 'Důvod stavu',
CASE
	WHEN true THEN ''
	WHEN bs.remark != '' THEN CONCAT(bs.remark, ' | ', bs.data_from)
	ELSE bs.data_from
END
 as 'Poznámka',
CASE
	WHEN bs.user_insert IS NOT NULL OR bs.user_insert != '' THEN CONCAT(u1.name, ' ', u1.surname) ELSE ''
END as 'Záznam vložil - osoba',
CASE
	WHEN bs.zapsal IS NOT NULL OR bs.zapsal != '' THEN CONCAT(u2.name, ' ', u2.surname) 
	ELSE ''
END as 'Zapsal - osoba',
CASE
	WHEN bs.is_breeding_station_cmku = true  THEN 'Ano'
	WHEN bs.is_breeding_station_cmku = false THEN 'Ne'
	ELSE 'Ne'
END as 'Spravováno ČMKU',
bs.id_breeding_station as 'Import ID' 
FROM breeding_station bs 
LEFT JOIN fw_user u1 ON u1.id_user = bs.user_insert 
LEFT JOIN fw_user u2 ON u2.id_user = bs.zapsal
;

