-- Kluby
SELECT c.club_name AS 'Název',  
'' AS 'IČ',
'' AS 'Kraj',
c.post_adress AS 'Ulice',
'' AS 'Č. p .',
'' AS 'Č.o.',
c.post_city AS 'Obec',
'' AS 'Část obce',
c.post_zip AS 'PSČ',
c.email as 'E-mail',
CONCAT(c.phone_std,CONCAT(' ', c.phone))  AS 'Telefon',
c.www AS 'Web',
c.id_club  AS 'ImportID', 
c.club_number AS 'Čl. číslo', 
CASE
    WHEN c.mobil = '' THEN ''
    ELSE CONCAT(c.mobil_std,CONCAT(' ', c.mobil))
  END AS 'Mobil',
CASE
    WHEN c.fax = '' THEN ''
    ELSE CONCAT(c.fax_std ,CONCAT(' ', c.fax))
  END AS 'Fax',
CASE
    WHEN c.bank_account = '' THEN ''
    ELSE CONCAT(c.bank_account ,CONCAT('/', c.bank_account_code )) 
  END  AS 'Bankovní účet',
cc.name AS 'Kategorie klubu'
FROM club c
JOIN club_category cc ON c.id_club_category = cc.id_club_category;
#WHERE c.club_name like '%švý%'

-- Poradci
#Název;IČ;Titul před;Jméno;Příjmení;Titul za;E-mail;Telefon;Mobil;Funkce;Popis
SELECT c.club_name AS 'Název',  
c.club_number AS 'Čl. číslo', 
'' AS 'IČ',
u.degree_before AS 'Titul před',
u.name AS 'Jméno',
u.surname AS 'Příjmení',
u.degree_after AS 'Titul za',
u.email AS 'E-mail',
CONCAT(u.phone_std,CONCAT(' ', u.phone))  AS 'Telefon',
CASE
    WHEN u.mobil = '' THEN ''
    ELSE CONCAT(u.mobil_std,CONCAT(' ', u.mobil))
  END AS 'Mobil',
ur.name_role AS 'Funkce',
u.fax AS 'Fax',
u.www AS 'Web',
u.inv_address AS 'Ulice',
'' AS 'Č. p .',
'' AS 'Č.o.',
u.inv_city AS 'Obec',
'' AS 'Část obce',
u.inv_zip AS 'PSČ',
u.id_user AS 'ImportID',
b.code_big AS 'Plemeno - kod',
bt.name_breed AS 'Plemeno - jméno',
CONCAT(CONCAT(b.code_big, ' '), bt.name_breed ) AS 'Plemeno'
FROM club_breed_sp cb
RIGHT JOIN club c ON cb.id_club = c.id_club
JOIN fw_user u ON u.id_user = cb.id_user
JOIN fw_user_fw_user_role_sp uur ON uur.id_user = u.id_user
JOIN fw_user_role ur ON ur.id_user_role = uur.id_user_role
JOIN breed b ON b.id_breed = cb.id_breed
JOIN breed_text bt ON b.id_breed = bt.id_breed
WHERE bt.id_language = 1 and ur.name_role like '%Poradce%';
#and c.club_name like '%švý%'


-- Kontaktní osoba
#Název;IČ;Titul před;Jméno;Příjmení;Titul za;E-mail;Telefon;Mobil;Funkce;Popis
SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
	'Název', c.club_name, 
	'Čl. číslo', c.club_number,
	'IČ', '',
	'Titul před',u.degree_before,
	'Jméno',u.name,
	'Příjmení',u.surname ,
	'Titul za',u.degree_after,
	'E-mail',u.email,
	'Telefon',CONCAT(u.phone_std,CONCAT(' ', u.phone)) ,
	'Mobil',CASE
	    WHEN u.mobil = '' THEN ''
	    ELSE CONCAT(u.mobil_std,CONCAT(' ', u.mobil))
	  	END,
	'Fax',u.fax ,
	'Web',u.www,
	'Ulice',u.inv_address,
	'Č. p .','',
	'Č.o.','',
	'Obec',u.inv_city,
	'Část obce','',
	'PSČ',u.inv_zip,
	'ImportID',u.id_user,
	'CHKImportID',c.id_club
)) AS json_vystup
FROM club c 
JOIN fw_user u ON u.id_user = c.id_owner_user
