CSOC Directory Access

The CSOC group may require access to our database to get a list of active employees (both staff and students).  This will be used to get contact information during times of emergencies.

NOTE:  

• Before implementing anything, should consider altering the VIEW created for the ID Badge Access such that we maintain only one VIEW for the various uses.
• Preliminary approach will be to modify the existing Directory Searches for staff and students to include the additional fields (like cell phone) if a person is granted access.

Access:

• Read-only access
• Check role type and if has access, then reveal extra columns for each additional contact piece.

Data:

The query will pull data from a view table that has the following data:

• Lastname - the employee's last name
• Firstname - the employee's first name
• Sectname - the Section an employee belongs to and works with
• Sectdescr - the Section long description label
• PersWorkLocation - the physical location of the office
• Username - the employee's UH username
• PersEmail - the employee's email address
• PersWorkPhone1 - the employee's office phone number
• PersWorkPhone2 - the employee's "other" phone number
• PersWorkPhone2Type - the type of phone number stored in "PersWorkPhone2"  
• PersWorkFax - the employee's fax phone number.
• Supervisor - the employee's supervisor
• Emptype - the employee's type is either "staff" or "student"

The SQL View:

-- SQL
SELECT distinct * FROM (
SELECT * FROM
--STAFF ONLY
(SELECT pers.perslastname as lastname,
pers.persfirstname as firstname,
pers.perspicture as photo,
sect.sectname,sect.sectdescr,
pers.persworklocation,
pers.persitsusername as username,
pers.persemail,
pers.persworkphone1,
pers.persworkphone2,
pers.persworkphone2type,
pers.persworkfax,
ppos.pposmanager as supervisor,
'staff' as emptype
FROM personnel pers,
persposition ppos,
section sect
WHERE pers.persid = ppos.persid
AND ppos.pposstatus = 'A'
AND ppos.sectid = sect.sectid
) staff
UNION ALL
SELECT * FROM
--STUDENT ONLY
(SELECT upper(stdlink.stdlastname) as lastname,
upper(stdlink.stdfirstname) as firstname,
personnel.perspicture as photo,
section.sectname,section.sectdescr,
personnel.persworklocation,
personnel.persitsusername as username,
personnel.persemail,
personnel.persworkphone1,
personnel.persworkphone2,
personnel.persworkphone2type,
personnel.persworkfax,
CONLINK.contactfirstname || ' ' || CONLINK.contactlastname as supervisor,
'student' as emptype
FROM SECEADM.placement@secedb_link PLCLINK,
SECEADM.student@secedb_link STDLINK,
SECEADM.jobcontacts@secedb_link SUPLINK,
SECEADM.contact@secedb_link CONLINK,
personnel,
persposition,
section
WHERE PLCLINK.plcStatus = 'A'
AND PLCLINK.stdId = STDLINK.stdId
AND PLCLINK.plcId = PERSPOSITION.empId
AND PLCLINK.plcjobid = SUPLINK.jobid
AND SUPLINK.aprid = 2
AND SUPLINK.contactid = CONLINK.contactid
AND STDLINK.stdUhuuid = PERSONNEL.persUhuuid
AND PERSONNEL.persId = PERSPOSITION.persId
AND PERSPOSITION.sectId = SECTION.sectId
) std
)
ORDER BY sectname, lastname, firstname;