/*
Duluth Public Schools ISD 709
Query: 1 users.sql
Last Modified: 7/16/2015

Desc: Student & Staff
  Another routine will likely be needed to
  inactivate (set status = complete or delete)
  students and staff when necessary (...annual run?).

Import sequence: 1 of 6 .csv files
*/

-- Selects active students for active year, for defined buildings

SELECT DISTINCT
  u.personID AS 'user_id'
, u.userName AS 'login_id'
-- password ...not needed because we use LDAP for logins
, i.firstName AS 'first_name'
, i.lastName AS 'last_name'
-- full_name - not needed when firstName and lastName
, i.lastName+', '+i.firstname AS 'sortable_name'
, i.firstName AS 'short_name'
, ct.email AS 'email'
, CASE
  WHEN en.endDate IS NULL THEN 'active'
  ELSE 'deleted'
  END AS 'status'
--, en.endDate

FROM Person p
JOIN [Identity] i ON i.identityID = p.currentIdentityID
JOIN UserAccount u ON u.personID = p.personID
LEFT JOIN Enrollment en ON en.personID = p.personID AND en.endDate IS NULL --> GETDATE ()
JOIN Calendar ca ON ca.calendarID = en.calendarID AND ca.schoolID IN (44,29,4,5,51,7)
JOIN SchoolYear sy ON sy.endYear = en.endYear and sy.active = 1
LEFT JOIN Contact ct ON ct.personID = p.personID --for email

WHERE
p.studentNumber IS NOT NULL

UNION

-- Selects active lisenced staff, for active year, for defined buildings
SELECT DISTINCT
   p.personID AS 'user_id'
, ua.username AS 'login_id'
, i.firstName AS 'first_name'
, i.lastName AS 'last_name'
, i.lastName +', '+i.firstName AS 'sortable_name'
, i.firstName AS 'short_name'
, ct.email AS 'email'
, CASE
  WHEN em.endDate IS NULL THEN 'active'
  ELSE 'deleted'
  END AS 'status'
--, em.endDate


FROM person p
JOIN [Identity] i ON i.identityID = p.currentIdentityID
JOIN UserAccount ua ON ua.personID = p.personID
JOIN employment em ON em.personID = p.personID
JOIN EmploymentAssignment ea ON ea.personID = p.personID
LEFT JOIN Contact ct ON ct.personID = p.personID --for email
WHERE p.staffNumber IS NOT NULL
--AND em.endDate IS NULL  --this line would only allow extracting active employees!
AND em.licenseNumber IS NOT NULL
AND ua.userName NOT LIKE '%_709'
AND ua.homepage NOT LIKE 'portal%'
AND ua.userName NOT LIKE '%schedule'
AND ua.userName NOT LIKE 'sped%'
AND ea.schoolID IN (4,5,51,7,29,44)

ORDER BY login_id