|
1
|
1) Salvare la risposta SOAP di Pimpa relativa agli ultimi anni
|
|
2
|
- WSDL: http://pimpa.isti.cnr.it/PERSONALE/web-services/iop/iop.webservice.php?wsdl
|
|
3
|
- Method: getDataRange
|
|
4
|
|
|
5
|
2) Eseguire gli xslt sulla risposta
|
|
6
|
|
|
7
|
3) Ricreare o svuotare la tabella temporanea affiliations_temp
|
|
8
|
- create table affiliations_temp(pid varchar(32), gid varchar(16), year int);
|
|
9
|
- create table affiliations_temp_no_id(name varchar(255), surname varchar(255), gid varchar(16), year int);
|
|
10
|
|
|
11
|
4) Importare le nuove affiliations sulle tabelle temporanee
|
|
12
|
|
|
13
|
5) Verificare che non esistano nuovi laboratori e/o nuove persone ed eventualmente inserirle una per una
|
|
14
|
- select distinct t.pid, p.name, p.surname from affiliations_temp t left outer join persons p on (t.pid = p.id) where p.name IS NULL;
|
|
15
|
- select distinct t.gid, g.name, g.type from affiliations_temp t left outer join groups g on (t.gid = g.id) where g.name IS NULL;
|
|
16
|
|
|
17
|
6) Copiare i dati dalla tabella temporanea
|
|
18
|
- insert into affiliations(pid,gid,year) select pid,gid,year from affiliations_temp ON DUPLICATE KEY UPDATE affiliations.pid=affiliations_temp.pid;
|
|
19
|
|
|
20
|
7) Effettuare controlli a campione
|
|
21
|
|
|
22
|
8) Cancellare la tabell temporanea
|
|
23
|
- drop table affiliations_temp;
|
|
24
|
|
|
25
|
9) Cancellare e/o correggere (se possibile) le affiliations UNKNOWN
|
|
26
|
- select pid, group_concat(gid) from affiliations where year = _YEAR_ group by pid having group_concat(gid) like '%UNKNOWN%' and count(gid) > 1;
|
|
27
|
- delete from affiliations where pid = '_ID_' and gid = 'UNKNOWN' and year = _YEAR_;
|