/**
 * @author AMR
 * @date 15/09/21
 * @version 1.0.1
 * @project SIEAU
 * @function purge_materiel
 */

# --- !Ups



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

  delete from mat_sim ms where jobexecutionid = varjobexecutionid;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans mat_sim : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from mat_sim_abonnements  where jobexecutionid = varjobexecutionid;;
  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;;

  delete from mat_abonnements  where jobexecutionid = varjobexecutionid;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans mat_abonnements : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from mat_telecom  where jobexecutionid = varjobexecutionid;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans mat_telecom : %', d_count;;
  d_count_total := d_count_total + d_count;;

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

  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) ;;
  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;;

  delete from mat_equipements where jobexecutionid = varjobexecutionid ;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans mat_equipements : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from mat_capteurs where jobexecutionid = varjobexecutionid ;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans mat_capteurs : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from mat_divers md  where jobexecutionid = varjobexecutionid;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans mat_divers : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from mat_centrales where jobexecutionid = varjobexecutionid;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans mat_centrales : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from mat_batteries where jobexecutionid = varjobexecutionid;;
  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;;

  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) ;;
  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;;

  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 );;
  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;;

  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) ;;
  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;;

  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 );;
  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;;

  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) ;;
  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;;

  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 );;
  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;;

  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 );;
  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;;

  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 );;
  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;;

  delete from mat_telecom_types where typetelecom not in (select distinct typetelecom from mat_telecom where jobexecutionid != varjobexecutionid)
                                  and typetelecom   in (select distinct typetelecom from mat_telecom where jobexecutionid = varjobexecutionid);;
  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;;

  delete from mat_batteries_types  where typebatterie not in (select distinct typebatterie from mat_batteries where jobexecutionid != varjobexecutionid)
                                     and typebatterie   in (select distinct typebatterie from mat_batteries where jobexecutionid = varjobexecutionid);;
  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;;

  delete from mat_divers_types mdt where typedivers not in (select distinct typedivers from mat_divers where jobexecutionid != varjobexecutionid)
                                     and typedivers   in (select distinct typedivers from mat_divers where jobexecutionid = varjobexecutionid);;
  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;;

  delete from mat_capteurs_types  where typecapteur not in (select distinct typecapteur from mat_capteurs where jobexecutionid != varjobexecutionid)
                                    and typecapteur   in (select distinct typecapteur from mat_capteurs where jobexecutionid = varjobexecutionid);;
  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;;

  delete from  mat_centrales_types   where typecentrale not in (select distinct typecentrale from mat_centrales where jobexecutionid != varjobexecutionid)
                                        and typecentrale   in (select distinct typecentrale from mat_centrales where jobexecutionid = varjobexecutionid) ;;
  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_types  where typecentrale not in (select distinct typecentrale from mat_centrales where jobexecutionid != varjobexecutionid)
                                        and typecentrale   in (select distinct typecentrale from mat_centrales where jobexecutionid = varjobexecutionid);;
  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;;

  delete from mat_equipements_types where jobexecutionid = varjobexecutionid ;;
  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;;

  delete from mat_equipements_types_detail where typeequipement not in (select distinct typeequipement from mat_equipements_types where jobexecutionid = varjobexecutionid);;
  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;;

  delete from evenements where jobexecutionid  = varjobexecutionid;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans evenements : %', d_count;;
  d_count_total := d_count_total + d_count;;

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

  delete from intervenants where jobexecutionid = varjobexecutionid;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans intervenants : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from contacts where jobexecutionid = varjobexecutionid;;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans contacts : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from lien_piezo_reseaux where codepiezometre in (select codepiezometre
                                                          from piezometres
                                                          where jobexecutionid = varjobexecutionid);;
  GET DIAGNOSTICS d_count := ROW_COUNT ;;
  RAISE NOTICE 'Nombre de delete dans lien_piezo_reseaux : %', d_count;;
  d_count_total := d_count_total + d_count;;

  delete from lien_piezo_intervenants where codepiezometre in (select codepiezometre
                                                               from piezometres
                                                               where jobexecutionid = varjobexecutionid);;
  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;;

  delete from lien_piezo_accessibilites where codepiezometre in (select codepiezometre
                                                               from piezometres
                                                               where jobexecutionid = varjobexecutionid);;
  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;;
$$ ;

# --- !Downs


drop FUNCTION purge_materiel;



