1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
| CREATE OR REPLACE FUNCTION public.Get_Cables(id_cable character varying)
RETURNS table
( ident character varying(40),
commune character varying(254),
postal character varying(254),
insee character varying(254),
pm character varying(80),
ch_boite1 character varying(40),
nom_boite1 character varying(40),
ch_boite2 character varying(40),
nom_boite2 character varying(40),
capacite numeric(10,0),
longueur numeric,
date_dsig character varying(80),
nb_supports bigint,
role_fibre character varying(254),
releve_lie character varying(254),
dos_conception_lie character varying(254),
num_fci character varying(254),
etat_ca character varying(254),
date_c9 date)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
-- only process parameter args once
IF id_cable IS NULL THEN
select c.ident as ident,
com.commune as commune,
com.postal as postal,
com.insee as insee,
pm.zapm as pm,
ch1.ident as ch_boite1,
b1.ident as nom_boite1,
ch2.ident as ch_boite2,
b2.ident as nom_boite2,
c.finbt as capacite,
c.longueur as longueur,
c.dsig as date_dsig,
s.nb_supp as nb_supports,
c.firol_libe as role_fibre,
ch.id_releve as releve_lie,
nc.id_conception as dos_conception_lie,
cc.fci as num_fci,
co.etat_webop as etat_ca,
co.date_valid_c9 as date_c9
from siea_s_fibrvue.v_ca_assemble_cable c
left join liste_communes com on right(c.insee,5) = com.insee
left join siea_s_fibrza_geo_zapm_mcr pm on st_intersects(c.geom,pm.geom)
left join siea_s_fibrvue_v_gc_assemble_chambre ch1 on st_dwithin(st_startpoint(c.geom),ch1.geom,0.2)
left join siea_s_fibrvue_v_gc_assemble_chambre ch2 on st_dwithin(st_endpoint(c.geom),ch2.geom,0.2)
left join siea_s_fibrvue_v_ca_assemble_boitier b1 on st_dwithin(st_startpoint(c.geom),b1.geom,0.2)
left join siea_s_fibrvue_v_ca_assemble_boitier b2 on st_dwithin(st_endpoint(c.geom),b2.geom,0.2)
left join (SELECT c.ident, COUNT(s.ident) as nb_supp
from siea_s_fibrvue_v_ca_assemble_cable c
left join siea_s_fibrvue_v_gc_assemble_support s on st_dwithin(c.geom,s.geom,0.2)
group by c.ident) s on s.ident = c.ident
left join suivi_non_conformites nc on nc.cable = c.ident
left join suivi_chambres ch on nc.siea = ch.id_siea
left join suivi_cables_commandes cc on cc.id_cable = c.ident
left join suivi_commandes co on co.fci = cc.fci;
ELSE
select c.ident as ident,
com.commune as commune,
com.postal as postal,
com.insee as insee,
pm.zapm as pm,
ch1.ident as ch_boite1,
b1.ident as nom_boite1,
ch2.ident as ch_boite2,
b2.ident as nom_boite2,
c.finbt as capacite,
c.longueur as longueur,
c.dsig as date_dsig,
s.nb_supp as nb_supports,
c.firol_libe as role_fibre,
ch.id_releve as releve_lie,
nc.id_conception as dos_conception_lie,
cc.fci as num_fci,
co.etat_webop as etat_ca,
co.date_valid_c9 as date_c9
from siea_s_fibrvue.v_ca_assemble_cable c
left join liste_communes com on right(c.insee,5) = com.insee
left join siea_s_fibrza_geo_zapm_mcr pm on st_intersects(c.geom,pm.geom)
left join siea_s_fibrvue_v_gc_assemble_chambre ch1 on st_dwithin(st_startpoint(c.geom),ch1.geom,0.2)
left join siea_s_fibrvue_v_gc_assemble_chambre ch2 on st_dwithin(st_endpoint(c.geom),ch2.geom,0.2)
left join siea_s_fibrvue_v_ca_assemble_boitier b1 on st_dwithin(st_startpoint(c.geom),b1.geom,0.2)
left join siea_s_fibrvue_v_ca_assemble_boitier b2 on st_dwithin(st_endpoint(c.geom),b2.geom,0.2)
left join (SELECT c.ident, COUNT(s.ident) as nb_supp
from siea_s_fibrvue_v_ca_assemble_cable c
left join siea_s_fibrvue_v_gc_assemble_support s on st_dwithin(c.geom,s.geom,0.2)
group by c.ident) s on s.ident = c.ident
left join suivi_non_conformites nc on nc.cable = c.ident
left join suivi_chambres ch on nc.siea = ch.id_siea
left join suivi_cables_commandes cc on cc.id_cable = c.ident
left join suivi_commandes co on co.fci = cc.fci
where c.ident = id_cable;
END IF;
END;$$ |
Partager