DatabaseEtc

Opprett ny gruppe

 insert into grp(name,description) values('team17mai_2009','Foreldre i 5. trinn, 17 mai team 2009');

 insert into grp(name,description) values('nesoya98','Nesøya Skole, Alle 98er');

Bruk grp_import.php

Se GoogleDoc Trinn 5 2008-2009

Ikke støtte for å slå sammen familier

 select * from family f1,family f2 where f1.id!=f2.id and f1.homephone=f2.homephone and f1.homephone!="";

 select f1.id,f1.addr,replace(f1.homephone,' ',""),f2.id,f2.addr,replace(f2.homephone,' ',"") from family f1,family f2 where f1.id!=f2.id and replace(f1.homephone,' ',"")=replace(f2.homephone,' ',"") and f1.homephone!="" and f1.id>f2.id;

 select * from family_person where family in ( );

 select * from person where id in ( );

Check

 select * from person where id not in (select person from family_person);
 select * from family where id not in (select family from family_person);

Samme med addr

 select f1.id,f1.addr,replace(f1.homephone,' ',),f2.id,f2.addr,replace(f2.homephone,' ',) from family f1,family f2 where f1.id!=f2.id and replace(f1.addr,' ',)=replace(f2.addr,' ',) and f1.addr!= and f1.id>f2.id;

Kopier alle events

 insert into grp_event(grp,name,eref,t) select NN::integer as grp,name,eref,'2009-05-17 12:00' as t from grp_event 
 where grp=(select id from grp where name='team17mai_2008');

Kopier alle event status

 insert into grp_event_participant_status(ge,value,sorthint,defnext) 
  select nge.id,geps.value,geps.sorthint,geps.defnext from 
     grp_event_participant_status geps,grp_event oge,grp_event nge 
     where oge.grp=8 and oge.name=nge.name and oge.id!=nge.id and geps.ge=oge.id and nge.grp=NN;

Kopier alle event roles

 insert into grp_event_participant_role(ge,value,sorthint,defnext,ges) 
  select nge.id as ge,gepr.value,gepr.sorthint,gepr.defnext,ngeps.id as ges from 
   grp_event_participant_role gepr,grp_event oge,grp_event nge,grp_event_participant_status ogeps,
   grp_event_participant_status ngeps 
    where oge.grp=8 and 
    nge.grp=NN and 
    oge.name=nge.name and 
    ogeps.id=gepr.ges and 
    ngeps.ge=nge.id and 
    ogeps.ge=oge.id and 
    ngeps.value=ogeps.value;

Legg til alle foreldrer til teamet

 insert into grp_member(grp,person,gmtype) 
  select distinct NN,fp2.person,'aktiv' from 
   grp_member gm,family_person fp1,family_person fp2 
    where gm.grp=<alle barn> and gm.person=fp1.person and 
    fp1.family=fp2.family and fp2.fptype in ('mamma','pappa');

Forandre fra aktiv til admin på den/de som skall kunne forandre deltagre i gruppene (events)

 update grp_member set gmtype='admin' where person=<personid> and grp=NN;

Mail aliaser

Oppdatere styret og rodeleder manuellt :

 select distinct p.email from grp_event ge, grp_event_participant gep ,person p 
   where ge.grp=11 and ge.name='Styret' and gep.ge=ge.id and p.id=gep.person;

Fixa aliases.maxb.no : styret.17mai_200x

 select fname,gname,email from grp_event_participant gep,grp_event_participant_role gepr,person p,grp_event ge 
   where gepr.value='Rodeleder' and gepr.id=gep.role and p.id=gep.person and ge.id=gep.ge and ge.grp=11;

Fixa aliases.maxb.no : rodeleder.17mai_200x

Some database commands

Check possible double pers :

 select * from person where nosp(mob) in (select nosp(mob) from person where nosp(mob)!= nosp(' ') group by nosp(mob) having count(*)>1) order by nosp(mob);

Check possible double home :

 select * from family where nosp(homephone) in (select nosp(homephone) from family where nosp(homephone)!= nosp(' ') group by nosp(homephone) having count(*)>1) order by nosp(homephone);

Check double relations

 select family,person from family_person group by family,person having count(*)>1;

 select grp,person,count(*) from grp_member group by grp,person having count(*)>1;

 select ge,person,count(*) from grp_event_participant group by ge,person having count(*)>1;