create ALGORITHM=UNDEFINED DEFINER=`devtest`@`localhost` SQL SECURITY DEFINER VIEW `tap_view3` AS select `o`.`id_obs` AS `obs_id`,
	concat(_utf8'ftp://',`h`.`name`,_utf8'/',`d`.`label`,`f`.`ftp`,`f`.`name`,
	`f`.`format`) AS `access_url`,
	`f`.`volume` AS `access_estsize`,
	`f`.`format` AS `access_format`,
	`o`.`date_obs` AS `time_min`,
	`o`.`date_end` AS `time_max`,	
	`w`.`name` AS `wlnth_name`,
	(case 
		when (`w`.`u_name` = 'Mhz') then (`w`.`min_val` * 1000000.0) 
		when (`w`.`u_name` = 'nm') then (299792458.0 / (`w`.`min_val` * 0.000000001)) 
		when (`w`.`u_name` = 'Å') then (299792458.0 / (`w`.`min_val` * 0.0000000001)) 
		when (`w`.`u_name` = 'm') then (299792458.0 / `w`.`min_val`) 
		else NULL 
	end) AS `spectral_range_min`,
	`w`.`min_val` AS `em_min`,
	`w`.`max_val` AS `em_max`,
	`w`.`u_name` AS `u_name`,
	`w`.`unit` AS `em_unit`,
	`inst`.`institu` AS `obs_creator_name`,
	`c`.`name_en` AS `description`,
	`i`.`instrume_en` AS `instrument_name`,
	`i`.`hdr_name` AS `hdr_name`,
	`f`.`id_file` AS `granule_id`,
	`inst`.`institu` AS `instrument_host_name`,
	`p`.`physpara` AS `measurement_type`,
	`o`.`solrot_n` AS `carrington`,
	`w`.`solar_zone_en` AS `target_region` ,
	`t`.`name` AS `imagette`,
	`t`.`email` AS `contact`
	

from (((((((((`t_file` `f` 
	join `t_host` `h`) 
	join `t_disk` `d`) 
	join `t_observ` `o`) 
	join `t_wavel` `w`) 
	join `t_institu` `inst`) 
	join `t_content` `c`) 
	join `t_instrume` `i`) 
	join `t_physpara` `p`)
	join `t_contact` `t`) 
where ((`o`.`id_file` = `f`.`id_file`) 
 	and (`f`.`id_host` = `h`.`id_host`) 
 	and (`f`.`id_disk` = `d`.`id_disk`) 
 	and (`p`.`id_physpara` = `f`.`id_physpara`) 
 	and (`o`.`id_instrume` = `i`.`id_instrume`) 
 	and (`i`.`id_institu` = `inst`.`id_institu`) 
 	and (`f`.`id_content` = `c`.`id_content`) 
 	and (`o`.`id_wavel` = `w`.`id_wavel`) 
 	and (year(`f`.`date_file`) = _utf8'2013')
	and (`t`.`id_contact`=`f`.`id_contact`))


/* aller ajouter le champ access qui dit si c'est accessible en ligne
 regarder un truc genre file_date pour date du fichier
*/ 
