## TRANSFERT_PLANNING

Doc
Details
Grants
References
Dependencies
Code

TEXT
    PROCEDURE          "TRANSFERT_PLANNING" (pannee_civile number, p_semaine number,p_site varchar2, p_annee varchar2) as

    cursor cur_edt(pp_annee_civile number, pp_semaine number, pp_site varchar2, pp_annee varchar2)  is
    select distinct e.edt_id,e.matiere, e.hd,e.hf,e.jour,e.semaine,e.annee_civile,
    e.groupe, lre.f_rel  f_rel, e.fpromo_id annee, e.depositaire  site,e.mat_libelle_court,e.fpromo_id
    from (select max(edt_id) edt_id,matiere, hd,hf,jour,semaine,annee_civile,
    groupe, fpromo_id, depositaire, mat_libelle_court from a_edt_gen  group by matiere, hd,hf,jour,semaine,annee_civile,
    groupe, fpromo_id, depositaire,mat_libelle_court) e,edt_liens_rels lre
    where  e.mat_libelle_court = lre.rel_libelle_court (+)
    and annee_civile = pp_annee_civile and semaine = pp_semaine
    and (mat_libelle_court is not null or fpromo_id = 10)
    and upper(pp_site) = upper(depositaire)
    and ((pp_annee = 'L1' and (fpromo_id = -5 or fpromo_id = -2))
    or (pp_annee = 'L2' and (fpromo_id = -4 or fpromo_id = -1))
    or (pp_annee = 'L3' and (fpromo_id = 1))
    or (pp_annee = 'M1' and (fpromo_id = 2))
    or (pp_annee = 'M2' and (fpromo_id = 3))
    or (pp_annee = 'DIVERS' and (fpromo_id = 10))
    )
    and to_char(date_jour_heure_edt(annee_civile,semaine,jour,hf),'YY/MM/DD HH24:MI:SS') >= to_char(sysdate,'YY/MM/DD HH24:MI:SS')
    ;

    cursor cur_creneau(pid_edt number) is select distinct
    c.salle, c.prof, c.classe,c.visio from a_edt_gen e, table(e.sa_pr_cl) c
    where
    e.edt_id = pid_edt;

    v_id_grp_site number;
    v_id_edt number;
    no_occ1 number;
    no_occ11 number;
    no_occ2 number;
    no_occ3 number;
    niveau varchar2(10);
    programme varchar2(50);
    v_id_session_programme number;
    v_id_prof number;
    v_id_salle number;
    v_id_groupe number;
    bad number default 0;
    v_login_professeur varchar2(20);
    v_salle varchar2(20);
    v_classe varchar2(20);
    probleme varchar2(100);
    rel_inconnue exception;
    v_semaine_anc number;
    v_semaine_nou number;
    v_increment number;
    v_id_rel number;
    v_visio  varchar2(1);
    v_type_creneau varchar2(5);
    v_libelle_module varchar2(20);
    begin
    	if pannee_civile = 2012 then
    		v_semaine_anc := p_semaine- 1;
    		v_semaine_nou := p_semaine-1;
    	else
    		v_semaine_anc := p_semaine - 1;
    		v_semaine_nou := p_semaine;
    	end if;
    	dbms_output.put_line('site ' || p_site);
    	pcd_supprimer_edt(pannee_civile,v_semaine_nou,p_site,p_annee);
    	probleme := 'site';
    	select p_id into v_id_grp_site from groupes g
            where upper(g.libelle) = upper(p_site)
            and g.f_type in (select p_id from types_groupes where libelle = 'site');
    	dbms_output.put_line('annee ' || p_annee);
    	for enr_edt in cur_edt(pannee_civile,v_semaine_anc,p_site,p_annee)
    	loop
    		dbms_output.put_line('un créneau');
    		begin
    			if enr_edt.f_rel is null and enr_edt.fpromo_id <> 10 then
    				probleme := enr_edt.matiere|| ' ##'||enr_edt.mat_libelle_court ||'##';
    				raise rel_inconnue;
    			end if;
    			no_occ1 := instr(enr_edt.matiere,':',1,1);
    			no_occ11 := instr(enr_edt.matiere,'-',1,1);
    			no_occ2 := instr(enr_edt.matiere,':',1,2);
    			if(no_occ11 = 0) then
    				no_occ11 := no_occ2;
    			end if;
    			no_occ3 := instr(enr_edt.matiere,'/',1,1);
    			if(no_occ3 = 0) THEN
    				no_occ3 := length(enr_edt.matiere)+1;
    			end if;
    			v_type_creneau := substr(enr_edt.matiere,no_occ11+1,no_occ1 - no_occ11 - 1);
          if no_occ11 <= 20 then
            v_libelle_module := substr(enr_edt.matiere,1,no_occ11);
          else
            v_libelle_module := substr(enr_edt.matiere,1,20);
          end if;
    			niveau := substr(enr_edt.matiere,no_occ1+1,no_occ2 - no_occ1 - 1);
    			programme := substr(enr_edt.matiere,no_occ2+1,no_occ3 - no_occ2 - 1);
    			if enr_edt.fpromo_id = 10 then
    				programme := 'DIVERS';
    			elsif niveau = 'L3' and programme = 'TC' THEN
    				programme := 'ING1';
    			elsif niveau = 'M1' and programme = 'TC' THEN
    				programme := 'ING2';
                elsif niveau = 'M2' and programme = 'TC' THEN
    				programme := 'ING3';
    			elsif niveau = 'L1' and programme = 'CPI' THEN
    				programme := 'CPI1';
    			elsif niveau = 'L1' and programme = 'STI' THEN
    				programme := 'CPI1';
    			elsif niveau = 'L2' and programme = 'CPI' THEN
    				programme := 'CPI2';
    			elsif enr_edt.fpromo_id = -5 or enr_edt.fpromo_id = -2 then
    				programme := 'CPI1';
    			elsif enr_edt.fpromo_id = -4 or enr_edt.fpromo_id = -1 then
    				programme := 'CPI2';
    			elsif enr_edt.fpromo_id = 1 then
    				programme := 'ING1';
    			elsif niveau is null and enr_edt.fpromo_id = 2 then
    				programme := 'ING2';
    			elsif niveau is null and enr_edt.fpromo_id = 3 then
    				programme := 'ING3';
    			end if;
    			v_id_session_programme := null;
    			probleme := 'sessions';
    			begin
    				SELECT p_id into v_id_session_programme from programmes_sessions
    					where
    					f_semainier in (select p_id from semainiers where date_debut <= date_jour(pannee_civile,v_semaine_nou,1) and date_jour(pannee_civile,v_semaine_nou,1) <= date_fin)
    					and
    					f_programme in (select p_id from rels where libelle_court = programme and f_type = 10);
    			exception
    			when no_data_found then
    				null;
    			end;

    			dbms_output.put_line(enr_edt.edt_id || ' - ' || niveau || ' - ' || programme || ' - ' || v_id_session_programme || ' - ' || enr_edt.f_rel
    					|| ' - ' || enr_edt.matiere);
    			if enr_edt.fpromo_id = 10 THEN
    				select p_id into v_id_rel from rels where libelle_court = 'SCO';
    			else
    				v_id_rel := enr_edt.f_rel;
    			end if;
    			insert into edt(f_rel,f_programme_session,f_site,hd,hf,jour,semaine,annee_civile,date_saisie, libelle,libelle_court)
    					values  (v_id_rel,v_id_session_programme,v_id_grp_site,enr_edt.hd,enr_edt.hf,enr_edt.jour,v_semaine_nou,
    					enr_edt.annee_civile, sysdate, v_libelle_module,v_type_creneau) returning p_id into v_id_edt;
    			for enr_creneau in cur_creneau(enr_edt.edt_id) loop
    				if upper(enr_creneau.prof) = 'AIB' then
    					v_login_professeur := 'aab';
    				elsif upper(enr_creneau.prof) = 'NEA' then
    					v_login_professeur := 'nab';
    				elsif upper(enr_creneau.prof) = 'ER' then
    					v_login_professeur := 'erc';
    				elsif upper(enr_creneau.prof) = 'JPV' then
    					v_login_professeur := 'jv';
    				elsif enr_creneau.prof is null or upper(enr_creneau.prof) = 'INTI2' or upper(enr_creneau.prof) = 'INTI1'
    									or upper(enr_creneau.prof) = 'INTI3' or upper(enr_creneau.prof) = 'INI3' or upper(enr_creneau.prof) = 'INBI'
    									or upper(enr_creneau.prof) = 'INICOM' or upper(enr_creneau.prof) = 'INIAD' or upper(enr_creneau.prof) = 'INIFI' then
    					v_login_professeur :='inconnu';
    				else
    					v_login_professeur := enr_creneau.prof;
    				end if;
    				probleme := 'login ' ||v_login_professeur ;
    				select pf_groupe into v_id_prof from acteurs where upper(login) = upper(v_login_professeur);
    				if enr_edt.fpromo_id = 10 then
    					v_classe := enr_edt.groupe;
    				elsif upper(enr_creneau.classe) = upper('Esp-A1') then
    					v_classe := 'ESP-1';
    				elsif upper(enr_creneau.classe) = upper('Esp-A2') then
    					v_classe := 'ESP-2';
    				elsif upper(enr_creneau.classe) = upper('Esp-A3') then
    					v_classe := 'ESP-3';
    				elsif upper(enr_creneau.classe) = upper('Esp-A4') then
    					v_classe := 'ESP-4';
    				elsif upper(enr_creneau.classe) = upper('Esp-A3-A4') then
    					v_classe := 'ESP-3';
    				elsif upper(enr_creneau.classe) = upper('All-A1') or upper(enr_creneau.classe) = upper('All-B1') then
    					v_classe := 'ALL-1';
    				elsif upper(enr_creneau.classe) = upper('All-A2') or upper(enr_creneau.classe) = upper('All-B2') then
    					v_classe := 'ALL-2';
    				elsif upper(enr_creneau.classe) = upper('Chin-C1') then
    					v_classe := 'CHI-1';
    				elsif upper(enr_creneau.classe) = upper('Chin-C2') then
    					v_classe := 'CHI-2';
    				elsif upper(enr_creneau.classe) = upper('Jap-D1') then
    					v_classe := 'JAP-1';
    				elsif upper(enr_creneau.classe) = upper('Jap-D2') then
    					v_classe := 'JAP-2';
    				elsif upper(enr_creneau.classe) = upper('Ita-E1') then
    					v_classe := 'ITA-1';
    				elsif upper(enr_creneau.classe) = upper('Ital-E1') then
    					v_classe := 'ITA-1';
    				elsif enr_creneau.classe = 'MI' or enr_creneau.classe =
    							'SIE' or enr_creneau.classe = 'MF' or enr_creneau.classe =
    							'GSI' or enr_creneau.classe = 'GSI-MI' or enr_creneau.classe = 'MF-UCP'
    							or enr_creneau.classe = 'ICOM' or enr_creneau.classe = 'IFI'
    							or enr_creneau.classe = 'BI'
    							or enr_creneau.classe = 'IAD'
    							or enr_creneau.classe = 'I3'
    							or enr_creneau.classe is null then
    					v_classe := enr_edt.groupe;
    				else
    					v_classe := enr_creneau.classe;
    				END IF;
    				probleme := 'groupe '|| v_classe;
    				select p_id into v_id_groupe  from groupes
    					where upper(libelle) = upper(v_classe);
    				--dbms_output.put_line(v_id_edt|| ' groupe ' || v_classe || '   groupe '|| v_id_groupe);
    				probleme := 'salle';
    				if enr_creneau.salle is null then
    					v_salle := '???';
    				elsif upper(enr_creneau.salle) = 'AMPHI-TG' then
    					v_salle := 'A-TG';
    				elsif upper(enr_creneau.salle) = 'AMPHI-CT' then
    					v_salle := 'A-CT';
    				elsif upper(enr_creneau.salle) = 'AMPHI-CA' then
    					v_salle := 'A-CA';
    				else v_salle := enr_creneau.salle;
    				end if;
    				probleme := 'salle '|| v_salle;
    				select p_id into v_id_salle from salles
    					where upper(libelle) = upper(v_salle)
    					and f_site = v_id_grp_site;
    				dbms_output.put_line('--------- '|| 'salle ' || v_id_salle || '   prof ' || v_id_prof || '    groupe ' || v_id_groupe);
    				if enr_creneau.visio = 'Pas de visio' then
    					v_visio := 'n';
    				else
    					v_visio := 'y';
    				end if;
    				insert into creneaux(pf_edt,pf_apprenants,pf_formateur,pf_salle,visio)
    					values(v_id_edt,v_id_groupe,v_id_prof,v_id_salle,v_visio);
    			end loop;
    		exception
    			when no_data_found then
    				dbms_output.put_line(enr_edt.edt_id || ' - ' ||niveau || ' - ' || programme || ' - ' || 'probleme ' || probleme);
    				bad := bad+1;
    			when rel_inconnue then
    				raise_application_error(-20001,'rel inconnue '||probleme);
    		end;
        end loop;
        dbms_output.put_line('mauvais = ' || bad);
    end;