Select Git revision
Bertrand Gauthier authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
v1.2.0.md 17.07 KiB
v1.2.0
Sources PHP
Sur le serveur, placez-vous dans le répertoire du web service (sans doute /var/www/sygal-import-ws)
puis lancez les commandes suivantes pour installer la nouvelle version :
git fetch && git fetch --tags && git checkout --force 1.2.0 && bash install.shSelon le moteur PHP que vous avez installé, rechargez le service, exemple :
- php7.0-fpm :
service php7.0-fpm reload - apache2-mod-php7.0 :
service apache2 reload
Base de données
Acteurs des thèses
Apogée
Avis à ceux qui ont Apogée, pour que Sygal puisse avoir connaissance de tous les acteurs des thèses (ex: co-directeurs) tout en permettant à votre établissement de conserver sa propre codification des rôles des acteurs, plusieurs vues SYGAL_* doivent être modifiées/créées dans Apogée.
La vue SYGAL_ROLE_TR doit être personnalisée pour votre établissement car elle spécifie la traduction
du code de rôle utilisé dans votre établissement (FROM_COD_ROJ) en celui attendu par Sygal (TO_COD_ROJ).
La version non commentée ci-dessous convient à Caen et au Havre. La version commentée convient à Rouen.
-- UCN et ULHN
create or replace view SYGAL_ROLE_TR(FROM_COD_ROJ, TO_COD_ROJ) as
select 'A', 'A' from dual union
select 'B', 'B' from dual union
select 'C', 'C' from dual union
select 'D', 'D' from dual union
select 'K', 'K' from dual union
select 'M', 'M' from dual union
select 'P', 'P' from dual union
select 'R', 'R' from dual
/
/*
-- URN
create or replace view SYGAL_ROLE_TR(FROM_COD_ROJ, TO_COD_ROJ) as
select 'A', 'R' from dual union -- rapporteur du jury
select 'C', 'K' from dual union -- co-dir
select 'D', 'D' from dual union -- dir
select 'E', 'M' from dual union -- examinateur => membre du jury (discutable)
select 'M', 'M' from dual union -- membre du jury
select 'N', 'B' from dual union -- co-encadrant
select 'P', 'P' from dual union -- président du jury
select 'R', 'R' from dual -- rapporteur du jury
/
*/
create or replace view SYGAL_ROLE_NOMENC(COD_ROJ, LIC_ROJ, LIB_ROJ) as
select 'A', 'Absent', 'Membre absent' from dual union
select 'B', 'Co-encadr', 'Co-encadrant' from dual union
select 'C', 'Chef Labo', 'Chef de laboratoire' from dual union
select 'D', 'Directeur', 'Directeur de thèse' from dual union
select 'K', 'Co-direct', 'Co-directeur de thèse' from dual union
select 'M', 'Membre', 'Membre du jury' from dual union
select 'P', 'Président', 'Président du jury' from dual union
select 'R', 'Rapporteur', 'Rapporteur du jury' from dual
/
create or replace view SYGAL_ROLE_JURY as
select distinct
rtr.TO_COD_ROJ as COD_ROJ,
sr.LIB_ROJ,
sr.LIC_ROJ
from role_jury ar
join SYGAL_ROLE_TR rtr on ar.COD_ROJ = rtr.FROM_COD_ROJ
join SYGAL_ROLE_NOMENC sr on sr.COD_ROJ = rtr.TO_COD_ROJ
/
create or replace view SYGAL_ROLE as
select
'apogee' as source_id, -- Id de la source
COD_ROJ as id, -- Id du rôle
LIB_ROJ,
LIC_ROJ
from SYGAL_ROLE_JURY
/
create or replace view SYGAL_ACTEUR as
with acteur as (
select
'D_' || rowid as id,
ths.cod_ths,
'D' as cod_roj,
ths.cod_per_dir as cod_per,
ths.cod_etb_dir as cod_etb,
ths.cod_cps_dir as cod_cps,
null as tem_rap_recu,
null as cod_roj_compl
from these_hdr_sout ths
where ths.cod_ths_trv = '1' and ths.cod_per_dir is not null
union
select
'K1_' || rowid as id,
ths.cod_ths,
'K' as cod_roj,
ths.cod_per_cdr as cod_per,
ths.cod_etb_cdr as cod_etb,
ths.cod_cps_cdr as cod_cps,
null as tem_rap_recu,
null as cod_roj_compl
from these_hdr_sout ths
where ths.cod_ths_trv = '1' and ths.cod_per_cdr is not null
union
select
'K2_' || rowid as id,
ths.cod_ths,
'K' as cod_roj,
ths.cod_per_cdr2 as cod_per,
ths.cod_etb_cdr2 as cod_etb,
ths.cod_cps_cdr2 as cod_cps,
null as tem_rap_recu,
null as cod_roj_compl
from these_hdr_sout ths
where ths.cod_ths_trv = '1' and ths.cod_per_cdr2 is not null
union
select
'R_' || rowid as id,
trs.cod_ths,
'R' as cod_roj,
trs.cod_per,
null as cod_etb,
null as cod_cps,
trs.tem_rap_recu,
null as cod_roj_compl
from ths_rap_sou trs
union
select
'M_' || rowid as id,
tjp.cod_ths,
'M' as cod_roj,
tjp.cod_per,
tjp.cod_etb,
tjp.cod_cps,
null as tem_rap_recu,
case when tjp.cod_roj in ( 'P', 'B', 'A' ) then tjp.cod_roj else null end as cod_roj_compl
from ths_jur_per tjp
)
select distinct
act.id as id,
'apogee' as source_id, -- Id de la source
act.cod_ths as these_id, -- Identifiant de la these
roj.cod_roj as role_id, -- Identifiant du rôle
cast(act.cod_roj_compl as varchar2(1 char)) as cod_roj_compl, -- Code du complement sur le role dans le jury
rjc.lib_roj as lib_roj_compl, -- Libelle du complement sur le role dans le jury
coalesce(
regexp_replace(per.num_dos_har_per,'[^0-9]',''),
'COD_PER_'||act.cod_per
) as individu_id, -- Code Harpege ou Apogee de l acteur
nvl ( act.cod_etb, per.cod_etb ) as acteur_etablissement_id, -- Id de l'etablissement de l'acteur
case when etb.cod_dep = '099' then etb.cod_pay_adr_etb else null end as cod_pay_etb, -- Code pays etablissement
case when etb.cod_dep = '099' then pay.lib_pay else null end as lib_pay_etb, -- Libelle pays etablissement
cps.cod_cps, -- Code du corps d'appartenance
cps.lib_cps, -- Libelle du corps d'appartenance
per.tem_hab_rch_per, -- HDR (O/N)
act.tem_rap_recu -- Rapport recu (O/N)
from acteur act
join SYGAL_ROLE_JURY roj on roj.cod_roj = act.cod_roj
join personnel per on per.cod_per = act.cod_per
left join corps_per cps on cps.cod_cps = nvl ( act.cod_cps, per.cod_cps )
left join etablissement etb on etb.cod_etb = nvl ( act.cod_etb, per.cod_etb )
left join pays pay on pay.cod_pay = etb.cod_pay_adr_etb
left join SYGAL_ROLE_JURY rjc on rjc.cod_roj = act.cod_roj_compl
/Physalis
La vue SYGAL_ROLE devrait resembler à ça :
create view SYGAL_ROLE(source_id, COD_ROJ, LIC_ROJ, LIB_ROJ) as
select 'physalis', 'A', 'Absent', 'Membre absent' from dual union
select 'physalis', 'B', 'Co-encadr', 'Co-encadrant' from dual union
select 'physalis', 'C', 'Chef Labo', 'Chef de laboratoire' from dual union
select 'physalis', 'D', 'Directeur', 'Directeur de thèse' from dual union
select 'physalis', 'K', 'Co-direct', 'Co-directeur de thèse' from dual union
select 'physalis', 'M', 'Membre', 'Membre du jury' from dual union
select 'physalis', 'P', 'Président', 'Président du jury' from dual union
select 'physalis', 'R', 'Rapporteur', 'Rapporteur du jury' from dual
/NB: la vue SYGAL_ACTEUR doit référencer dans sa colonne ROLE_ID les mêmes codes de rôles que la vue SYGAl_ROLE
(colonne COD_ROJ).
Financement des thèses en cours
Deux nouvelles vues doivent être créées SYGAL_ORIGINE_FINANCEMENT et SYGAL_FINANCEMENT pour que Sygal puisse
importer les informations concernant le financement des thèses en cours.
Apogée
create or replace view SYGAL_ORIGINE_FINANCEMENT (ID, SOURCE_ID, COD_OFI, LIC_OFI, LIB_OFI) as
select '10', 'apogee', '10', 'SALARIE', 'Etudiant salarié' from dual union all
select '11', 'apogee', '11', 'SANS FIN', 'Sans financement' from dual union all
select '13', 'apogee', '13', 'DOT EPSCP', 'Dotation des EPSCP' from dual union all
select '14', 'apogee', '14', 'DOT EPST', 'Dotation des EPST' from dual union all
select '15', 'apogee', '15', 'POLYTECH', 'Programmes Spé. Normaliens, Polytechnici' from dual union all
select '16', 'apogee', '16', 'HANDICAP', 'Programme Spécifique Handicap' from dual union all
select '17', 'apogee', '17', 'DEFENSE', 'Ministère de la Défense (dont DGA)' from dual union all
select '18', 'apogee', '18', 'AGRICULTUR', 'Ministère de l''Agriculture' from dual union all
select '19', 'apogee', '19', 'AFF ETRANG', 'Ministère des Affaires Etrangères' from dual union all
select '20', 'apogee', '20', 'SANTE', 'Ministère de la Santé' from dual union all
select '21', 'apogee', '21', 'AUTRES MIN', 'Autres Ministères' from dual union all
select '22', 'apogee', '22', 'DOT EPIC', 'Dotation des EPIC' from dual union all
select '23', 'apogee', '23', 'DOT EPA', 'Dotation des EPA' from dual union all
select '24', 'apogee', '24', 'NORMANDIE', 'Région Normandie' from dual union all
select '25', 'apogee', '25', 'AUT COLLEC', 'Autre Collectivité Territoriale' from dual union all
select '26', 'apogee', '26', 'ANR', 'ANR' from dual union all
select '27', 'apogee', '27', 'IDEX', 'IDEX' from dual union all
select '28', 'apogee', '28', 'PIA', 'Autres dispositifs du PIA (dont LABEX)' from dual union all
select '29', 'apogee', '29', 'AUT AFFPR', 'Autres Finan. Pub. d''Agences Françaises' from dual union all
select '30', 'apogee', '30', 'FI PUB PRV', 'Financements Mixtes Public Privé' from dual union all
select '31', 'apogee', '31', 'CIFRE', 'Conventions CIFRE' from dual union all
select '32', 'apogee', '32', 'PART RECH', 'Partenariat de Recherche' from dual union all
select '33', 'apogee', '33', 'MECENAT', 'Mécénat y compris Fondations et Asso.' from dual union all
select '34', 'apogee', '34', 'ERC', 'ERC' from dual union all
select '35', 'apogee', '35', 'MARIE CURI', 'Actions Marie Sklodowska Curie' from dual union all
select '36', 'apogee', '36', 'ERASMUS', 'ERASMUS' from dual union all
select '37', 'apogee', '37', 'AUT PRO EU', 'Autre Programme Européen' from dual union all
select '38', 'apogee', '38', 'GOUV EUROP', 'Gouvernement Etranger Européen' from dual union all
select '39', 'apogee', '39', 'GOUV NON E', 'Gouvernement Etranger Hors Europe' from dual union all
select '40', 'apogee', '40', 'AUT FI ETR', 'Autres Financements Etrangers' from dual union all
select '41', 'apogee', '41', 'ENT ETR', 'Entreprise Etrangère' from dual union all
select '42', 'apogee', '42', 'ORG FC', 'Financements Organismes FC' from dual union all
select '43', 'apogee', '43', 'ORG INTER', 'Organismes Internationaux' from dual
;
create or replace view SYGAL_FINANCEMENT as
select distinct
tfi.cod_seq_tfi as id, -- Numero de sequence du financement
'apogee' as source_id,
tfi.cod_ths as these_id,
tfi.cod_anu as annee_id, -- Identifiant de l annee universitaire (ex. 2018 pour 2018/2019)
tfi.cod_ofi as origine_financement_id,
tfi.compl_tfi as complement_financement,
tfi.quotite_tfi as quotite_financement,
tfi.dat_deb_tfi as date_debut_financement,
tfi.dat_fin_tfi as date_fin_financement
from these_hdr_sout ths
join diplome dip on dip.cod_dip = ths.cod_dip
join typ_diplome tpd on tpd.cod_tpd_etb = dip.cod_tpd_etb
join ths_financement tfi on tfi.cod_ths = ths.cod_ths
join origine_financement ofi on ofi.cod_ofi = tfi.cod_ofi
where ths.cod_ths_trv = '1' -- Exclusion des travaux
and tpd.eta_ths_hdr_drt = 'T' -- Inscription en these
and tpd.tem_sante = 'N' -- Exclusion des theses d exercice
and ofi.tem_en_sve_ofi = 'O' -- Exclusion des anciens codes des origines de financements;
and tfi.cod_anu is not null
/Physalis
Pour écrire la vue SYGAL_FINANCEMENT, s'inspirer peut-être de la vue Apogée...
create or replace view SYGAL_ORIGINE_FINANCEMENT (ID, SOURCE_ID, COD_OFI, LIC_OFI, LIB_OFI) as
select '10', 'physalis', '10', 'SALARIE', 'Etudiant salarié' from dual union all
select '11', 'physalis', '11', 'SANS FIN', 'Sans financement' from dual union all
select '13', 'physalis', '13', 'DOT EPSCP', 'Dotation des EPSCP' from dual union all
select '14', 'physalis', '14', 'DOT EPST', 'Dotation des EPST' from dual union all
select '15', 'physalis', '15', 'POLYTECH', 'Programmes Spé. Normaliens, Polytechnici' from dual union all
select '16', 'physalis', '16', 'HANDICAP', 'Programme Spécifique Handicap' from dual union all
select '17', 'physalis', '17', 'DEFENSE', 'Ministère de la Défense (dont DGA)' from dual union all
select '18', 'physalis', '18', 'AGRICULTUR', 'Ministère de l''Agriculture' from dual union all
select '19', 'physalis', '19', 'AFF ETRANG', 'Ministère des Affaires Etrangères' from dual union all
select '20', 'physalis', '20', 'SANTE', 'Ministère de la Santé' from dual union all
select '21', 'physalis', '21', 'AUTRES MIN', 'Autres Ministères' from dual union all
select '22', 'physalis', '22', 'DOT EPIC', 'Dotation des EPIC' from dual union all
select '23', 'physalis', '23', 'DOT EPA', 'Dotation des EPA' from dual union all
select '24', 'physalis', '24', 'NORMANDIE', 'Région Normandie' from dual union all
select '25', 'physalis', '25', 'AUT COLLEC', 'Autre Collectivité Territoriale' from dual union all
select '26', 'physalis', '26', 'ANR', 'ANR' from dual union all
select '27', 'physalis', '27', 'IDEX', 'IDEX' from dual union all
select '28', 'physalis', '28', 'PIA', 'Autres dispositifs du PIA (dont LABEX)' from dual union all
select '29', 'physalis', '29', 'AUT AFFPR', 'Autres Finan. Pub. d''Agences Françaises' from dual union all
select '30', 'physalis', '30', 'FI PUB PRV', 'Financements Mixtes Public Privé' from dual union all
select '31', 'physalis', '31', 'CIFRE', 'Conventions CIFRE' from dual union all
select '32', 'physalis', '32', 'PART RECH', 'Partenariat de Recherche' from dual union all
select '33', 'physalis', '33', 'MECENAT', 'Mécénat y compris Fondations et Asso.' from dual union all
select '34', 'physalis', '34', 'ERC', 'ERC' from dual union all
select '35', 'physalis', '35', 'MARIE CURI', 'Actions Marie Sklodowska Curie' from dual union all
select '36', 'physalis', '36', 'ERASMUS', 'ERASMUS' from dual union all
select '37', 'physalis', '37', 'AUT PRO EU', 'Autre Programme Européen' from dual union all
select '38', 'physalis', '38', 'GOUV EUROP', 'Gouvernement Etranger Européen' from dual union all
select '39', 'physalis', '39', 'GOUV NON E', 'Gouvernement Etranger Hors Europe' from dual union all
select '40', 'physalis', '40', 'AUT FI ETR', 'Autres Financements Etrangers' from dual union all
select '41', 'physalis', '41', 'ENT ETR', 'Entreprise Etrangère' from dual union all
select '42', 'physalis', '42', 'ORG FC', 'Financements Organismes FC' from dual union all
select '43', 'physalis', '43', 'ORG INTER', 'Organismes Internationaux' from dual
;
create or replace view SYGAL_FINANCEMENT (
id,
source_id,
these_id,
annee_id,
origine_financement_id,
complement_financement,
quotite_financement,
date_debut_financement,
date_fin_financement
) as
...
;