v1.1.3.md 6.58 KB
Newer Older
Bertrand Gauthier's avatar
Bertrand Gauthier committed
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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
# v1.1.3

## Sources PHP

Sur le serveur, placez-vous dans le répertoire du web service (sans doute `/var/www/sygal-import-ws`) 
puis lancez la commande git suivante pour "installer" la nouvelle version :
```bash
git fetch && git fetch --tags && git checkout 1.1.3
```

Selon 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

Avis à ceux qui ont Apogée, la vue SYGAL_INDIVIDU doit être corrigée car elle inverse le nom usuel et le nom de naissance 
des acteurs :

```sql
create or replace view SYGAL_INDIVIDU as
  select distinct
    'apogee'                                            as source_id,       -- Id de la source
    'doctorant'                                         as type,
    to_char(ind.cod_etu)                                as id,              -- Numero etudiant
    decode(ind.cod_civ, 1, 'M.', 'Mme')                 as civ,             -- Civilite etudiant
    ind.lib_nom_pat_ind                                 as lib_nom_pat_ind, -- Nom de famille etudiant
    coalesce(ind.lib_nom_usu_ind, ind.lib_nom_pat_ind)  as lib_nom_usu_ind, -- Nom usage etudiant
    initcap(coalesce(ind.lib_pr1_ind,'Aucun'))          as lib_pr1_ind,     -- Prenom 1 etudiant
    initcap(ind.lib_pr2_ind)                            as lib_pr2_ind,     -- Prenom 2 etudiant
    initcap(ind.lib_pr3_ind)                            as lib_pr3_ind,     -- Prenom 3 etudiant
    emails.email                                        as email,           -- Mail etudiant
    ind.date_nai_ind                                    as date_nai_ind,    -- Date naissance etudiant
    ind.cod_pay_nat                                     as cod_pay_nat,     -- Code nationalite
    pay.lib_nat                                         as lib_nat          -- Libelle nationalite
  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 individu       ind on ind.cod_ind     = ths.cod_ind --and ind.cod_etu != 21009539 -- Exclusion du compte de test Aaron AAABA
    join pays           pay on pay.cod_pay     = ind.cod_pay_nat
    left join SYGAL_MV_EMAIL emails on emails.id = ind.cod_etu
  where ths.cod_ths_trv     =  '1'  --  Exclusion des travaux
        and dip.cod_tpd_etb     in ( '39', '40' )
        and tpd.eta_ths_hdr_drt =  'T'  -- Inscription en these
        and tpd.tem_sante       =  'N'  -- Exclusion des theses d exercice
        and ind.cod_etu is not null         -- oui, oui, ça arrive
  union
  -- acteurs
  select "SOURCE_ID","TYPE","ID","CIV","LIB_NOM_PAT_IND","LIB_NOM_USU_IND","LIB_PR1_IND","LIB_PR2_IND","LIB_PR3_IND","EMAIL","DATE_NAI_IND","COD_PAY_NAT","LIB_NAT" from (
    with acteur as (
      select
        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
        ths.cod_ths,
        'D'              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
        ths.cod_ths,
        'D'              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
        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
        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
      'apogee'                                                                                                as source_id,
      'acteur'                                                                                                as type,
      coalesce(regexp_replace(per.num_dos_har_per,'[^0-9]',''), 'COD_PER_'||act.cod_per)                      as id,     -- Code Harpege ou Apogee de l acteur
      initcap(per.cod_civ_per)                                                                                as civ,             -- Civilite acteur
      per.LIB_NOM_PAT_PER                                                                                     as lib_nom_pat_ind, -- Nom de famille acteur
      per.lib_nom_usu_per                                                                                     as lib_nom_usu_ind, -- Nom d'usage acteur
      per.lib_pr1_per                                                                                         as lib_pr1_ind,     -- Prenom 1 acteur
      null                                                                                                    as lib_pr2_ind,     -- Prenom 2 acteur
      null                                                                                                    as lib_pr3_ind,     -- Prenom 3 acteur
      emails.email                                                                                            as email,           -- Mail acteur
      per.dat_nai_per                                                                                         as date_nai_ind,    -- Date naissance acteur
      null                                                                                                    as cod_pay_nat,     -- Code nationalite
      null                                                                                                    as lib_nat          -- Libelle nationalite
    from acteur               act
      join role_jury            roj on roj.cod_roj = act.cod_roj
      join personnel            per on per.cod_per = act.cod_per
      left join SYGAL_MV_EMAIL emails on emails.id = per.num_dos_har_per
  )
/
```