-- ** TITULO      : Crear la vista radiological_technique_report
-- ** OBJETIVO    : Crear la vista radiological_technique_report que permite revisar los registros de la técnica ragiológica
--					usada en un ingreso.
-- ** DESCRIPCION : Se elimina la vista radiological_technique_report en caso que exista.
--                  Se crea  la vista radiological_technique_report.
--                  Se le dan permisos de lectura al usuario metabase_user sobre la vista radiological_technique_report.

-- View: public.radiological_technique_report

DROP VIEW IF EXISTS radiological_technique_report;

CREATE OR REPLACE VIEW public.radiological_technique_report AS
SELECT	plates_registry.appointment_code,
		usuario_placas.username AS user_tecno_name,
		plates_registry.plates_good AS plates_good,
		plates_registry.plates_bad AS plates_bad,
		plates_registry.number_exposures AS number_exposures,
		CASE	
			WHEN	plates_registry.over_exposure = ''	THEN NULL
			ELSE	plates_registry.over_exposure
		END AS over_exposure,
		plates_registry.reason_overexposure AS reason_overexposure,
		plates_registry.amount_mas AS amount_mas,
		plates_registry.amount_ma AS amount_ma,
		plates_registry.amount_kva AS amount_kva,
		plates_registry.weight AS weight,
		CASE
			WHEN	plates_registry.tipo_contraste = 1 THEN 'Yodado'
			WHEN	plates_registry.tipo_contraste = 2 THEN 'Bario'
			WHEN	plates_registry.tipo_contraste = 3 THEN 'Gadolinio'
			WHEN	plates_registry.tipo_contraste = 4 THEN 'Gastroview'
		END AS contrast_type,
		plates_registry.cantidad_contraste AS cantidad_contraste,
		CASE 
			WHEN plates_registry.amount_qradiof = '' THEN NULL 
			ELSE plates_registry.amount_qradiof
		END AS amount_qradiof,
		CASE 
			WHEN plates_registry.amount_mci = '' THEN NULL
			ELSE plates_registry.amount_mci
		END AS amount_mci,
		CASE 
			WHEN  plates_registry.amount_mgy = '' THEN NULL
			ELSE plates_registry.amount_mgy
		END AS amount_mgy,
		CASE 
			WHEN  plates_registry.amount_mgyagd = '' THEN NULL
			ELSE plates_registry.amount_mgyagd
		END AS amount_mgyagd,
		CASE 
			WHEN  plates_registry.amount_ctdi = '' THEN NULL
			ELSE plates_registry.amount_ctdi
		END AS amount_ctdi,
		CASE 
			WHEN  plates_registry.amount_dlp = '' THEN NULL
			ELSE plates_registry.amount_dlp
		END AS amount_dlp,
		CASE 
			WHEN  plates_registry.amount_imgcine = '' THEN NULL
			ELSE plates_registry.amount_imgcine
		END AS amount_imgcine,
		CASE 
			WHEN  plates_registry.amount_imgfluoro = '' THEN NULL
			ELSE plates_registry.amount_imgfluoro
		END AS amount_imgfluoro,
		CASE 
			WHEN  plates_registry.size_plates = '' THEN NULL
			ELSE plates_registry.size_plates
		END AS size_plates,
		CASE 
			WHEN  plates_registry.number_plates = '' THEN NULL
			ELSE plates_registry.number_plates
		END AS number_plates,
		room_appointment_study_receipt.id_room_appointment_study_receipt AS id_entry,
		studies.name_studies,
		appointment.appointment_status AS appointment_status
FROM plates_registry
LEFT JOIN appointment ON appointment.appointment_code = plates_registry.appointment_code
LEFT JOIN user_sonata usuario_placas ON plates_registry.id_user_tecno = usuario_placas.id
LEFT JOIN room_appointment_study_receipt ON room_appointment_study_receipt.appointment_code = appointment.appointment_code
LEFT JOIN (SELECT study.id_room_appointment_study_receipt, 
					string_agg(study.study_name, '; ') AS name_studies
			FROM study
			GROUP BY study.id_room_appointment_study_receipt) studies ON studies.id_room_appointment_study_receipt = room_appointment_study_receipt.id_room_appointment_study_receipt;

ALTER TABLE public.radiological_technique_report
    OWNER TO postgres;
COMMENT ON VIEW public.radiological_technique_report
    IS 'Vista creada para mostrar los detalles de los registros de la técnica radiológica ingresados en el formulario de actualizaciones. por parte de los tecnólogos';            

GRANT ALL ON TABLE public.radiological_technique_report TO postgres;

DO 
$do$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'metabase_user')     
   THEN
		GRANT SELECT ON TABLE public.radiological_technique_report TO metabase_user;
   END IF;
END
$do$;
