Email Lists Built from People DB
- luis avila
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
fi
II. Jeff Gee's queries to update the census of people on our Quick Facts page:
: