SELECT
   beneficiario_ultimo AS "Ragione Sociale"
 , codice_fiscale AS "Codice Fiscale"
 , COALESCE(cup_cipe, cup_st, cup_artea, cup_docup, cup_docup_std, '?') AS "Codice di Progetto"
 , titolo_progetto AS "Titolo Progetto"
 , cap_sede_legale AS "CAP Sede Legale"
 , comune_sede_legale AS "Comune Sede Legale"
 , provincia_sede_legale AS "Provincia Sede Legale"
 , indirizzo_sede_legale AS "Indirizzo Sede Legale"
 , cap_unita_locale_progetto AS "CAP Unità Locale"
 , comune_unita_locale_progetto AS "Comune Unità Locale"
 , provincia_unita_locale_progetto AS "Provincia Unità Locale"
 , indirizzo_unita_locale_progetto AS "Indirizzo Unità Locale"
 , codice_ateco_2007 AS "ATECO 2007"
 , programma AS "Programma"
 , linea AS "linea"
 , spesa_ammessa AS "Investimento Ammesso|money"
-- , importo_richiesto AS "Contributo Richiesto|money"
 , importo_definitivo AS "Contributo Ammesso|money"
 , importo_erogato AS "Contributo Erogato|money"
 , array_to_string(array(SELECT pretty_print(unnest(atto_concessione_definitiva))), ',') AS "Atto di Concessione"
 , array_to_string(array(SELECT (unnest(atto_concessione_definitiva)).numero), ',') AS "Numero Atto di Concessione|number"
 , array_to_string(array(SELECT (unnest(atto_concessione_definitiva)).data), ',') AS "Data Atto di Concessione|date"
 , CASE stato_richiesta = 'Revocato' WHEN true THEN 'S' ELSE 'N' END AS "Revocato"
 , fonte_dati AS "Fonte Dati"
 , fonte_db_data AS "Dati aggiornati al|date"
FROM beneficiari_progetti
LEFT JOIN beneficiari USING(codice_fiscale)
LEFT JOIN progetti USING(id_progetto)
LEFT JOIN linee USING(programma,linea)
LEFT JOIN bandi USING(codice_bando)
LEFT JOIN gruppi_beneficiari_progetti USING(codice_fiscale,id_progetto)
LEFT JOIN gruppi USING(gruppo)
LEFT JOIN (SELECT id_progetto, codice AS cup_docup_std FROM codici_di_progetto WHERE codifica = 'DOCUP STANDARD')codici_di_progetto_docup_std USING(id_progetto)
LEFT JOIN (SELECT id_progetto, codice AS cup_cipe FROM codici_di_progetto WHERE codifica = 'MIP/CUP')codici_di_progetto_cup_cipe USING(id_progetto)
LEFT JOIN (SELECT id_progetto, codice AS cup_docup FROM codici_di_progetto WHERE codifica = 'DOCUP')codici_di_progetto_cup_docup USING(id_progetto)
LEFT JOIN (SELECT id_progetto, codice AS cup_st FROM codici_di_progetto WHERE codifica = 'Sviluppo Toscana')codici_di_progetto_cup_st USING(id_progetto)
LEFT JOIN (SELECT id_progetto, codice AS cup_artea FROM codici_di_progetto WHERE codifica = 'ARTEA')codici_di_progetto_cup_artea USING(id_progetto)
LEFT JOIN comuni c1 ON(c1.codice_comune = codice_comune_sede_legale)
LEFT JOIN comuni c2 ON(c2.codice_comune = codice_comune_unita_locale)
LEFT JOIN comuni c3 ON(c3.codice_comune = codice_comune_unita_locale_progetto)
LEFT JOIN (
	SELECT
	   *
	FROM (
		SELECT
		   id_progetto
		 , codice_fiscale
		 , array_agg(DISTINCT tipo_agevolazione) AS tipo_agevolazione
		 , sum(importo_richiesto) AS importo_richiesto
		 , sum(importo_richiesto_deminimis) AS importo_richiesto_deminimis
		 , sum(importo_provvisorio) AS importo_provvisorio
		 , sum(importo_provvisorio_deminimis) AS importo_provvisorio_deminimis
		 , array_agg(DISTINCT atto_concessione_provvisoria) AS atto_concessione_provvisoria
		 , sum(importo_definitivo) AS importo_definitivo
		 , sum(importo_definitivo_deminimis) AS importo_definitivo_deminimis
		 , array_agg(DISTINCT atto_concessione_definitiva) AS atto_concessione_definitiva
		 , sum(importo_richiesto_aiuto_limitato_compatibile) AS importo_richiesto_aiuto_limitato_compatibile
		 , sum(importo_provvisorio_aiuto_limitato_compatibile) AS importo_provvisorio_aiuto_limitato_compatibile
		 , sum(importo_definitivo_aiuto_limitato_compatibile) AS importo_definitivo_aiuto_limitato_compatibile
		FROM agevolazioni_richieste_e_concesse
		WHERE (:finanziatoil::text IS NULL OR COALESCE(:finanziatoil::integer = extract(year FROM (atto_concessione_definitiva).data)::integer, FALSE))
		  AND (:finanziatoda::text IS NULL OR COALESCE(:finanziatoda::integer <= extract(year FROM (atto_concessione_definitiva).data)::integer, FALSE))
		GROUP BY id_progetto,codice_fiscale
	) AS agevolazioni_richieste_e_concesse
	FULL JOIN (
		SELECT
		   id_progetto
		 , codice_fiscale
		 , array_agg(DISTINCT data_erogazione) AS data_erogazione
		 , sum(importo_erogato) AS importo_erogato
		 , bool_or(ultima_erogazione) AS ultima_erogazione
		FROM agevolazioni_erogate_restituite
		GROUP BY id_progetto,codice_fiscale
	) AS agevolazioni_erogate_restituite USING(id_progetto,codice_fiscale)
	FULL JOIN (
		SELECT
		   id_progetto
		 , codice_fiscale
		 , array_agg(DISTINCT motivazione_revoca) AS motivazione_revoca
		 , sum(importo_revocato) AS importo_revocato
		 , array_agg(DISTINCT atto_agevolazione_revocata) AS atto_agevolazione_revocata
		FROM agevolazioni_revocate
		GROUP BY id_progetto,codice_fiscale
	) AS agevolazioni_revocate USING(id_progetto,codice_fiscale)
	FULL JOIN (
		SELECT
		   id_progetto
		 , codice_fiscale
		 , array_agg(DISTINCT tipologia_spesa) AS tipologia_spesa
		 , sum(spesa_richiesta) AS spesa_richiesta
		 , sum(spesa_ammessa_in_istruttoria) AS spesa_ammessa_in_istruttoria
		 , sum(spesa_ammessa) AS spesa_ammessa
		FROM spese
		GROUP BY id_progetto,codice_fiscale
	) AS spese USING(id_progetto,codice_fiscale)
) AS dati_finanziari USING(id_progetto,codice_fiscale)
WHERE COALESCE(codice_fiscale = upper(:cf), :cf IS NULL)
  AND COALESCE(beneficiario_ultimo ILIKE :ben, TRUE)
  AND COALESCE(:ben_adv % ANY(rs_sign_array(beneficiario_ultimo)), TRUE)
  AND COALESCE(titolo_progetto ~* (:titoloprogetto), TRUE)
  AND COALESCE(:codiceunicoprogetto = ANY(ARRAY[cup_cipe, cup_docup, cup_docup_std, cup_st, cup_artea, id_progetto]), :codiceunicoprogetto IS NULL)
  AND COALESCE(comune_sede_legale = :comunesl, :comunesl IS NULL)
  AND COALESCE(provincia_sede_legale = :provinciasl, :provinciasl IS NULL)
  AND COALESCE(comune_unita_locale_progetto = :comuneloc, :comuneloc IS NULL)
  AND COALESCE(provincia_unita_locale_progetto = :provincialoc, :provincialoc IS NULL)
  AND (:dattobando, :nattobando)::atto = atto_approvazione_bando
  AND (:dattograd, :nattograd)::atto = ANY(atto_concessione_definitiva)
  AND COALESCE(programma = :programma, :programma IS NULL)
  AND COALESCE(linea = :linea, :linea IS NULL)
  AND COALESCE(tipologia_aiuto = :tipologiaaiuto, :tipologiaaiuto IS NULL)
  AND COALESCE(tipo_agevolazione @> string_to_array(:formaagevolazione,','), :formaagevolazione IS NULL)
  AND COALESCE(data_effettiva_fine_progetto <= :finanziatoa::timestamp, :finanziatoa IS NULL)
  AND (:finanziatoil::text IS NULL OR COALESCE(:finanziatoil::integer = ANY(array(SELECT extract(year FROM (unnest(atto_concessione_definitiva)).data)::integer)), FALSE))
  AND (:finanziatoda::text IS NULL OR COALESCE(:finanziatoda::integer <= ANY(array(SELECT extract(year FROM (unnest(atto_concessione_definitiva)).data)::integer)), FALSE))
  AND COALESCE(codice_ateco_2007 = :ateco, :ateco IS NULL)
  AND CASE WHEN :ricerca::text IS NOT NULL THEN tipo_linea = 'Ricerca' ELSE TRUE END
ORDER BY "Ragione Sociale"