/**
 * @author ebonneau
 * @date 22/09/21
 * @version 1.0.0
 * @project SIEau
 * @function purge_materiel
 */

# --- !Ups
-- Function: purge_materiel(integer)

-- DROP FUNCTION purge_materiel(integer);

CREATE OR REPLACE FUNCTION purge_materiel(varjobexecutionid integer)
  RETURNS integer AS
$BODY$
DECLARE
d_count int;;
d_count_total int;;
BEGIN
d_count := 0;;
d_count_total := 0;;

IF (varjobexecutionid is null) THEN
	delete from mat_sim ms where jobexecutionid is null;;
ELSE
	delete from mat_sim ms where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_sim : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_sim_abonnements ms where jobexecutionid is null;;
ELSE
	delete from mat_sim_abonnements  where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_sim_abonnements : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_abonnements ms where jobexecutionid is null;;
ELSE
	delete from mat_abonnements  where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_abonnements : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_telecom ms where jobexecutionid is null;;
ELSE
	delete from mat_telecom  where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_telecom : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_telecom_sim ms where idtelecom  in (select mt.idtelecom  from  mat_telecom mt where mt.jobexecutionid is null) ;;
ELSE
	delete from mat_telecom_sim   where idtelecom  in (select mt.idtelecom  from  mat_telecom mt where mt.jobexecutionid = varjobexecutionid) ;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_telecom : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_equipements_detail where idequipement  in ( select distinct idequipement from mat_equipements where jobexecutionid is null)
	or idequipement  not in ( select distinct idequipement from mat_equipements) ;;
ELSE
	delete from mat_equipements_detail where idequipement  in ( select distinct idequipement from mat_equipements where jobexecutionid = varjobexecutionid)
	or idequipement  not in ( select distinct idequipement from mat_equipements) ;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_equipements_detail : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_equipements where jobexecutionid is null ;;
ELSE
	delete from mat_equipements where jobexecutionid = varjobexecutionid ;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_equipements : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_capteurs where jobexecutionid is null ;;
ELSE
	delete from mat_capteurs where jobexecutionid = varjobexecutionid ;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_capteurs : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_divers where jobexecutionid is null ;;
ELSE
	delete from mat_divers md  where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_divers : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_centrales where jobexecutionid is null ;;
ELSE
	delete from mat_centrales where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_centrales : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_batteries where jobexecutionid is null ;;
ELSE
	delete from mat_batteries where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_batteries : %', d_count;;
d_count_total := d_count_total + d_count;;

delete from mat_centrales_voies mcv3  where idcentrale not in( select distinct idcentrale from mat_centrales mc);;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_centrales_voies : %', d_count;;
d_count_total := d_count_total + d_count;;

delete from mat_centrales_plages  where idcentrale not in( select distinct idcentrale from mat_centrales mc);;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_centrales_plages : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_sim_situations where idsim  in (select idsim from mat_sim ms where jobexecutionid is null)
	or idsim not in (select idsim from mat_sim) ;;
ELSE
	delete from mat_sim_situations where idsim  in (select idsim from mat_sim ms where jobexecutionid = varjobexecutionid)
	or idsim not in (select idsim from mat_sim) ;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_sim_situations : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_abonnements_situations where idabonnement in (select idabonnement from mat_abonnements where jobexecutionid is null)
	or idabonnement  not in (select idabonnement from mat_abonnements );;
ELSE
	delete from mat_abonnements_situations where idabonnement in (select idabonnement from mat_abonnements where jobexecutionid = varjobexecutionid)
	or idabonnement  not in (select idabonnement from mat_abonnements );;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_abonnements_situations : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_equipements_situations  where idequipement  in ( select distinct idequipement from mat_equipements where jobexecutionid is null)
	or idequipement  not in ( select distinct idequipement from mat_equipements) ;;
ELSE
	delete from mat_equipements_situations  where idequipement  in ( select distinct idequipement from mat_equipements where jobexecutionid = varjobexecutionid)
	or idequipement  not in ( select distinct idequipement from mat_equipements) ;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_equipements_situations : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_capteurs_situations  where idcapteur  in ( select idcapteur  from mat_capteurs  where jobexecutionid is null)
	or idcapteur not in (select idcapteur  from mat_capteurs );;
ELSE
	delete from mat_capteurs_situations  where idcapteur  in ( select idcapteur  from mat_capteurs  where jobexecutionid = varjobexecutionid)
	or idcapteur not in (select idcapteur  from mat_capteurs );;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_capteurs_situations : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_divers_situations where  id  in (select id from mat_divers where jobexecutionid is null)
	or id not in (select id from mat_divers) ;;
ELSE
	delete from mat_divers_situations where  id  in (select id from mat_divers where jobexecutionid = varjobexecutionid)
	or id not in (select id from mat_divers) ;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_capteurs_situations : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_centrales_situations  where idcentrale  in (select idcentrale  from mat_centrales  where jobexecutionid is null)
	or idcentrale not in (select idcentrale  from mat_centrales );;
ELSE
	delete from mat_centrales_situations  where idcentrale  in (select idcentrale  from mat_centrales  where jobexecutionid = varjobexecutionid)
	or idcentrale not in (select idcentrale  from mat_centrales );;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_centrales_situations : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_batteries_situations  where idbatterie  in ( select idbatterie  from mat_batteries  where jobexecutionid is null)
	or idbatterie not in (select idbatterie from mat_batteries );;
ELSE
	delete from mat_batteries_situations  where idbatterie  in ( select idbatterie  from mat_batteries  where jobexecutionid = varjobexecutionid)
	or idbatterie not in (select idbatterie from mat_batteries );;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_batteries_situations : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_telecom_situations   where idtelecom in (select idtelecom  from mat_telecom  where jobexecutionid is null)
	or idtelecom  not in (select idtelecom  from mat_telecom );;
ELSE
	delete from mat_telecom_situations   where idtelecom in (select idtelecom  from mat_telecom  where jobexecutionid = varjobexecutionid)
	or idtelecom  not in (select idtelecom  from mat_telecom );;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_telecom_situations : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_telecom_types where (typetelecom not in (select distinct typetelecom from mat_telecom where jobexecutionid is not null)
	and jobexecutionid is null);;
ELSE
	delete from mat_telecom_types where (typetelecom not in (select distinct typetelecom from mat_telecom where jobexecutionid != varjobexecutionid)
	and jobexecutionid = varjobexecutionid);;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_telecom_types : %', d_count;;
d_count_total := d_count_total + d_count;;


IF (varjobexecutionid is null) THEN
	delete from mat_batteries_types  where (typebatterie not in (select distinct typebatterie from mat_batteries where jobexecutionid is not null)
	and jobexecutionid is null);;
ELSE
	delete from mat_batteries_types  where (typebatterie not in (select distinct typebatterie from mat_batteries where jobexecutionid != varjobexecutionid)
	and jobexecutionid = varjobexecutionid);;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_batteries_types : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_divers_types mdt where (typedivers not in (select distinct typedivers from mat_divers where jobexecutionid is not null)
	and jobexecutionid is null);;
ELSE
	delete from mat_divers_types mdt where (typedivers not in (select distinct typedivers from mat_divers where jobexecutionid != varjobexecutionid)
	and jobexecutionid = varjobexecutionid);;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_divers_types : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_capteurs_types  where (typecapteur not in (select distinct typecapteur from mat_capteurs where jobexecutionid is not null)
	and jobexecutionid is null);;
ELSE
	delete from mat_capteurs_types  where (typecapteur not in (select distinct typecapteur from mat_capteurs where jobexecutionid != varjobexecutionid)
	and jobexecutionid = varjobexecutionid);;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_capteurs_types : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from  mat_centrales_types where (typecentrale not in (select distinct typecentrale from mat_centrales where jobexecutionid is not null)
	and jobexecutionid is null);;
ELSE
	delete from  mat_centrales_types where (typecentrale not in (select distinct typecentrale from mat_centrales where jobexecutionid != varjobexecutionid)
	and jobexecutionid = varjobexecutionid);;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_centrales_types : %', d_count;;
d_count_total := d_count_total + d_count;;


delete from mat_centrales_voies_types mcv3 where typecentrale not in( select distinct mc.typecentrale from mat_centrales_types mc);;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_centrales_voies : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_equipements_types where jobexecutionid is null ;;
ELSE
	delete from mat_equipements_types where jobexecutionid = varjobexecutionid ;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_equipements_types : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is null) THEN
	delete from mat_equipements_types_detail where typeequipement not in (select distinct typeequipement from mat_equipements_types where jobexecutionid is null);;
ELSE
	delete from mat_equipements_types_detail where typeequipement not in (select distinct typeequipement from mat_equipements_types where jobexecutionid = varjobexecutionid);;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans mat_equipements_types_detail : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is not null) THEN
	delete from evenements where jobexecutionid  = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans evenements : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is not null) THEN
	delete from lien_piezo_reseaux where codereseau in (select codereseau from reseaux where jobexecutionid  = varjobexecutionid);;
	delete from reseaux where jobexecutionid  = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans reseaux : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is not null) THEN
	delete from intervenants where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans intervenants : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is not null) THEN
	delete from contacts where jobexecutionid = varjobexecutionid;;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans contacts : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is not null) THEN
	delete from lien_piezo_intervenants where codepiezometre in (select codepiezometre from piezometres where jobexecutionid = varjobexecutionid);;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans lien_piezo_intervenants : %', d_count;;
d_count_total := d_count_total + d_count;;

IF (varjobexecutionid is not null) THEN
	delete from lien_piezo_accessibilites where codepiezometre in (select codepiezometre from piezometres where jobexecutionid = varjobexecutionid);;
END IF;;
GET DIAGNOSTICS d_count := ROW_COUNT ;;
RAISE NOTICE 'Nombre de delete dans lien_piezo_accessibilites : %', d_count;;
d_count_total := d_count_total + d_count;;

RETURN d_count_total;;
END;;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
  # --- !Downs


 
