[Feature]Admin/Brugere: Export af liste af Admins der ikke skal være admins mere

[mhewel]

Overordnet beskrivelse af funktionen

I forbindelse med oprydning af lukkede afdelinger/foreninger, er der nu lavet et manuelt SQL udtræk for at vise brugere der er

• admin for (mindst) en afdeling der er lukket
• admin for (mindst) en forening der er lukket
• admin men ikke for en afdeling eller forening

Flg SQL virker:

SELECT au.username, TO_CHAR(au.last_login, 'YYYY-MM-DD') AS user_lastlogin, 'Forening' AS type, 
  mu.name, TO_CHAR(mu.closed_at, 'YYYY-MM-DD') AS closedate, 'Brugeren er Admin og har adgang til en lukket forening' AS info
FROM auth_user AS au,
  members_adminuserinformation AS ma, 
  members_adminuserinformation_unions AS mau,
  members_union mu
WHERE au.id = ma.user_id
  AND au.is_staff = true
  AND au.is_superuser = false
  AND mau.adminuserinformation_id = ma.id
  AND mu.id = mau.union_id
  AND mu.closed_at is NOT NULL AND mu.closed_at < NOW()
UNION
SELECT au.username, TO_CHAR(au.last_login, 'YYYY-MM-DD') AS user_lastlogin, 'Afdeling' AS type, 
  md.name, TO_CHAR(md.closed_dtm, 'YYYY-MM-DD') AS closedate, 'Brugeren er Admin og har adgang til en lukket afdeling' AS info
FROM auth_user AS au,  
  members_adminuserinformation AS ma, 
  members_adminuserinformation_departments AS mad,
  members_department md
WHERE au.id = ma.user_id
  AND au.is_staff = true
  AND au.is_superuser = false
  AND mad.adminuserinformation_id = ma.id
  AND md.id = mad.department_id
  AND md.closed_dtm is NOT NULL AND md.closed_dtm < NOW()
UNION
SELECT au.username, TO_CHAR(au.last_login, 'YYYY-MM-DD') AS user_lastlogin, ' ' AS type, 
  ' ' AS name,  ' ' AS closedate, 'Brugeren er Admin og har hverken adgang til foreninger eller afdelinger' AS info 
FROM auth_user AS au,
  members_adminuserinformation AS ma
WHERE au.is_staff = true
  AND au.is_superuser = false
  AND ma.user_id = au.id
  AND ma.id NOT IN
  ( SELECT DISTINCT x.adminuserinformation_id
    FROM
    ( SELECT adminuserinformation_id
      FROM members_adminuserinformation_unions
      UNION
      SELECT adminuserinformation_id
      FROM members_adminuserinformation_departments
    ) AS x 
  )
order by username 

Ideen er at lave en action der kan bruge ovenstående SQL og lave Export af CSV (husk UTF8 som i den andre CSV exports).

[lakridserne]

Hvis vi vælger at lave så specifikke udtræk, bør vi automatisere hele processen.

[rasmusselsmark]

Køres som cronjob (når vi er sikre på det virker 🙂 )