Forums PostgreSQL.fr

select
	c.compromis_id as ID_COMPROMIS,
	e.societe_id as ID_SOCIETE,
	c.affaire as ID_LOT,
	o.proprietaire_id as ID_PROPRIETAIRE,
	n_v.perso as ID_NOTAIRE_VENDEUR,
	p.perso as ID_ACHETEUR,
	n_a.perso as ID_NOTAIRE_ACHETEUR,
	e.user_id ::integer as ID_EMP_NEGO,
	c.negoext1 as ID_NEGO_EXT1,
	c.negoext2 as ID_NEGO_EXT2,
	c.negoext3 as ID_NEGO_EXT3,
	c.date as DATE_SIGN_COMPROMIS,
	c.datelimite as DATE_CONDITIONS_SUSP,
	c.dateprevuevente as DATE_PREVUE_VENTE,
	c.dateeffectivevente as DATE_EFFECTIVE_VENTE,
	c.dateencaissement as DATE_ENCAISSEMENT,
	c.datesupprvente as DATE_ANNUL_VENTE,
	c.conditionsuspensive as LIB_CONDITIONS_SUSP,
	m.prixinitiale::double precision as PRIX_INITIAL,
	m.prixvente::double precision as PRIX_VENTE,
	m.tauxcomm::double precision as TAUX_COMM,
	( m.prixvente*(m.tauxcomm/100) )::double precision as COMMISSION_AGENCE_TTC,
	c.tva::double precision as TAUX_TVA,
	( ( m.prixvente*(m.tauxcomm/100) ) / (1+(c.tva/100)) )::double precision as COMMISSION_AGENCE_HT,
	(m.prixvente - ( m.prixvente*(m.tauxcomm/100) ))::double precision as PRIX_NET_VENDEUR,
	c.partaffaire::double precision as POURC_PART_SORTANTE,
	c.partacheteur::double precision as POURC_PART_ENTRANTE,
	coalesce(c.partExt1,0) + coalesce(c.partExt2,0) + coalesce(c.partExt3, 0) ::double precision as POURC_PART_EXT,
	CASE
		WHEN 	c.datesupprvente is null then ( ( m.prixvente*(m.tauxcomm/100) ) / (1+(c.tva/100)) ) * ( coalesce(c.partacheteur,0) + coalesce(c.partaffaire,0) )
		ELSE 0
	END ::double precision as CA_HT,
	CASE
		WHEN c.dateencaissement IS NOT NULL THEN 'Vente encaissée'
		WHEN c.datesupprvente IS NOT NULL THEN 'Vente annulée'
		WHEN c.dateeffectivevente IS NOT NULL THEN 'Vente effective'
		WHEN c.date IS NOT NULL THEN 'Vente prévue'
		ELSE null
	END as STATUT_VENTE

from
	ods_transac.compromis c
		LEFT OUTER JOIN ods_transac.prospect p ON ( c.acheteur=p.prospect_id )
		LEFT OUTER JOIN ods_transac.employe e ON ( p.nego_id = e.user_id )
		LEFT OUTER JOIN ods_transac.lot o ON ( c.affaire = o.lot_id )
		LEFT OUTER JOIN ods_transac.mandat_transaction m ON ( o.mandat_id = m.mandat_id )
		LEFT OUTER JOIN ods_transac.notaire n_a ON ( c.notaireacheteur = n_a.notaire_id )
		LEFT OUTER JOIN ods_transac.notaire n_v ON ( c.notairevendeur = n_v.notaire_id )

where
	c.flag_integration = -1

shared_buffers 3GB
work_mem 512MB
maintenance_work_mem 512MB
temp_buffers 256MB
Effective_cache_size 12GB
Wal_buffers 16MB
Check_point_segment 64
Checkpoint_timeout 30min
Checkpoint_completion_target 0.9
Checkpoint_warning 0
tcp_keepalives_idle 120
tcp_keepalives_interval 60
tcp_keepalives_count 10
synchronous_commit off
Default_statistics_target 400
Enable_seqscan off

2018-03-01 10:08:25 CET FATAL:  le système de bases de données se lance
2018-03-01 10:08:26 CET LOG:  le système de bases de données n'a pas été arrêté proprement ; restauration  automatique en cours
2018-03-01 10:08:28 CET LOG:  les protections contre la réutilisation d'un membre MultiXact sont maintenant activées
2018-03-01 10:08:28 CET LOG:  le système de bases de données est prêt pour accepter les connexions