-- This script finds circumstances where users seem to be create in duplicate,
-- even though there is a name that was entered via the automated LDAP
-- synchronization.  It appears that the only thing to do is to make the
-- automatically generated account be the "official" account and delete the one
-- that was created via the synchronization script.

begin;

select id, username, institution, city, country
  from mdl_user 
 where username in (
   select m.username 
     from mdl_user m, mdl_user mm 
    where m.username = mm.username 
      and mm.institution = 'Goshen College LDAP' 
      and (m.institution is null or m.institution = '')
   );

update mdl_user 
   set institution = 'Goshen College LDAP (funky)' 
 where id in (
   select id
     from mdl_user 
    where username in (
      select m.username 
        from mdl_user m, mdl_user mm 
       where m.username = mm.username 
         and mm.institution = 'Goshen College LDAP' 
         and (m.institution is null or m.institution = '')
      )
   )
   and institution = 'Goshen College LDAP';

update mdl_user 
   set institution = 'Goshen College LDAP',
       country = 'US',
       city = 'Goshen'
 where id in (
   select id
     from mdl_user 
    where username in (
      select m.username 
        from mdl_user m, mdl_user mm 
       where m.username = mm.username 
         and mm.institution = 'Goshen College LDAP (funky)' 
         and (m.institution is null or m.institution = '')
      )
   )
   and ( institution = '' or institution is null );

delete from mdl_user where institution = 'Goshen College LDAP (funky)';

commit;