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=XX and oge.name=nge.name and oge.id!=nge.id and geps.ge=oge.id and nge.grp=NN;

Bytt NN med nye grp id og XX med grp id fra året før

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=XX 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;

Bytt NN med nye grp id og XX med grp id fra året før

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'.'stefar','stemor') and 
      fp2.person not in (select person from grp_member where grp=NN);

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);
 select fname,gname from person group by fname,gname having count(*)>1;

Merge using defined function:

select merge_person(,);

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;

People member of many families

 select f.id as fid,fptype,gname,fname,addr,zip,town from family_person fp,person p,family f where person in ( select person from family_person group by person having count(*)>1) and p.id=person and f.id=fp.family order by person ;

Merge family:

 select merge_family()

Mob number format fix:

 update person set mob=btrim(regexp_replace(nosp(mob),'(...)(..)(..[0-9])([^0-9]*)$',' \\1 \\2 \\3 \\4'))  where  nosp(mob)=mob and mob!=nosp(' ');

Complete list:

 select p.id,p.gname,p.fname,f.homephone,p2.gname || ' ' || p2.fname,p2.mob,p2.email,p3.gname || ' ' ||p3.fname,p3.mob,p3.email from person p,grp_member gm,family_person fp1,family_person fp2,person p2,family f,family_person fp3,person p3 where gm.person=p.id and gm.grp=15 and fp1.person=p.id and fp1.family=fp2.family and fp2.fptype='mamma' and p2.id=fp2.person and f.id = fp1.family and fp3.family=f.id and fp3.person = p3.id and fp3.fptype='pappa' order by id;

People not assigned to "rode":

 select * from person p,grp_member gm where p.id=gm.person and gm.grp=NN and p.id not in (select gep.person from grp_event_participant gep,grp_event ge where gep.ge=ge.id and ge.grp=NN) and (nosp(p.fname) != nosp(' ') and nosp(p.gname) != nosp(' '));