I. The Acad List:

II. Jeff Gee's queries to update the census of people on our Quick Facts page

___________________________________________________________

I. The Acad List:

Log unto siomail.

2. become bubba

[lavila@siomail ~]$ !128

sudo su - bubba

3. cd bin

4. 3 relevant files:

-r-xr-xr-x 1 bubba bubba 323 Sep 16 2014 make_acadlist.sh

-r-xr-xr-x 1 bubba bubba 721 Sep 16 2014 dl.acad_auto.sh

-rwxr-xr-x 1 bubba bubba 1356 Sep 17 2014 acad_auto.sql

siomail(bubba): bin% more make_acadlist.sh

#!/bin/sh

PATH=$PATH:/usr/lib/mailman/bin

ACADLIST=$HOME/maillists/acadlist.txt

ACADLIST_ADJ=$HOME/maillists/acadlist_adj.txt

export PATH

# Delete all the list members

remove_members -a -n -N acad

# Rebuild the lists from the backups

add_members -r $ACADLIST -w n -a n acad

add_members -r $ACADLIST_ADJ -w n -a n acad

siomail(bubba): bin% more acad_auto.sql

Select Distinct

pv1.value

FROM people AS p

LEFT JOIN ad_email AS ad

USING (people_id)

LEFT JOIN

sio_people.people_values AS pv1

ON (p.people_id = pv1.people_id AND pv1.field = "email_primary")

LEFT JOIN

sio_people.people_values AS pv2

ON (p.people_id = pv2.people_id AND pv2.field = "email_display")

LEFT JOIN

sio_people.people_values AS pv12

ON (p.people_id = pv12.people_id AND pv12.field = "pps_status")

LEFT JOIN

sio_people.people_values AS pv13

ON (p.people_id = pv13.people_id AND pv13.field = "sio_role")

LEFT JOIN

sio_people.people_values AS pv14

ON (p.people_id = pv14.people_id AND pv14.field = "sio_status")

LEFT JOIN

sio_people.people_values AS pv9

ON (p.people_id = pv9.people_id AND pv9.field = "business_office")

LEFT JOIN

sio_people.people_values AS pv8

ON (p.people_id = pv8.people_id AND pv8.field = "display_title")

where ((pv13.value like '%ACADE%') and (pv13.value not like '%Academic - Postdoctoral%') or (pv8.value like '%Research Professional%'))

and (pv12.value not like '%Separated%') and (pv12.value not like '%Inactive%')

and ((pv14.value is Null) or (pv14.value LIKE 'RTAD%') or (pv14.value = 'Sabbatical') or (pv14.value = 'Leave of Absence') or (pv14.value = 'Visa Pending')

or (pv14.value = 'Enrolled'))

Order by lname, fname

siomail(bubba): bin% more dl.acad_auto.sh

#!/bin/sh

## dl.acad_auto.sh,v 1.0 2014/09/15 20:25:33 lavila Exp bubba

LISTDIR=$HOME/maillists

DATE=`date '+%a, %h %d %T'`

BINDIR=$HOME/bin

MAIL=/usr/bin/Mail

DATABASE=sio_people

HOST=marinara

USER=bubba

PASSWD=t@bacc0

cd $LISTDIR

rm -f acadlist.txt

# Run Luis' sql to extract employee primary email from People DB.

mysql -h $HOST -u $USER -p$PASSWD $DATABASE --skip-column-names < $BINDIR/acad_auto.sql > $LISTDIR/acadlist.txt

# Shove the list over to siomail

if [ -s acadlist.txt ]

then

echo "." | $MAIL -s "SIO Academic List: $DATE" jwanetick@ucsd.edu

## cat emplist studlist devlist > sio-notices.feed

else

echo "." | $MAIL -s "SIO Academic List Failed: $DATE" jwanetick@ucsd.edu

II. Jeff Gee's queries to update the census of people on our Quick Facts page

___________________________________________________________

I. The Acad List:

II. Jeff Gee's queries to update the census of people on our Quick Facts page:

:

Browser not supported