Step 1
To use Single Sign-On and Push-to-Gradebook, you will need to contact Aequitas to install and
configure the most recent API. (Released late July 2015)
Configure the API and send EADMS Support the following:
• QApiKey: (if unknown, leave blank)
• SharedSecret:
• QConnectionFile:
• ConsumerKey:
• QUrl:
Step 2
Execute the supplied scripts on the Q Server. Click a link to be taken to the script:
- RUN ON Q - EADMS - Create and Alter UDF for Enrollment Updates - v05.sql
- RUN ON Q - EADMS - Create and Alter UDF for Faculty Accounts - v05.sql
- RUN ON Q - Q Menu Link to EADMS.sql
Step 3
Our IP Addresses need to be white-listed on your servers.
- 100.32.18.234
- 65.60.102.240
- 65.60.102.248
Step 4
Send us the connection string with the correct permissions to execute the UDFs created in step 2.
RUN ON Q - EADMS - Create and Alter UDF for Enrollment Updates - v05.sql
/****** Object: UserDefinedFunction [dbo].[EADMS_EnrollmentUpdate] Script Date: 1/20/2016
12:43:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[EADMS_EnrollmentUpdate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
EXEC sp_executesql N'CREATE FUNCTION [dbo].[EADMS_EnrollmentUpdate] (@today
DATE) RETURNS TABLE AS RETURN (SELECT 1 [Stub])'
END
GO
ALTER FUNCTION [dbo].[EADMS_EnrollmentUpdate]
(@today DATE)
RETURNS TABLE
AS
RETURN
/*
Created - July 2015 by michael@eadms.com
Updated - Jan 2016
v01 - Initial script
v02 - Cleaned up with queries based on Datawise Interface
v03 - Added references to codetrans and lookupcodes tables
v04 - changed get-Mobility parts to LEFT-join & default-values to 0 (zero)
v05 - Updated by VCOE - STUSTAT.TRKUNIQ = T.TRKUNIQ
Table based user-defined function to retrieve district
enrollment information: student demographics, schedule and faculty
NEED TO CHECK
Rosters
Current Student Enrollment
Current Course Schedules
Current Faculty
Demographics
ELProgType <- Couldn't find references in codetrans or LookupCodes tables
*/
(WITH
--SCHOOL MOBILITY
smobile (suniq, egraden) AS (
SELECT DISTINCT sd.suniq, eh.egraden
FROM studemo sd
INNER JOIN enrollmenthistory eh ON eh.suniq = sd.suniq
INNER JOIN school ON eh.atschoolc = school.schoolc
WHERE eh.toschoolc IS NULL
AND eh.atschoolc IS NOT NULL
© IO Education 2017. All Rights Reserved. Revised 12/13/18 Page 3
AND eh.estatus = 'A'
AND eh.egraden >= 0
AND school.isdo <> 1
),
--DISTRICT MOBILITY
dmobile (suniq, egraden) AS (
SELECT sd.suniq, CONVERT(int,(MIN(eh.egraden)))
FROM enrollmenthistory eh
JOIN studemo sd ON sd.suniq = eh.suniq
WHERE (eh.atschoolc IS NOT NULL)
AND (eh.estatus = 'A')
AND (eh.egraden >= 0)
GROUP BY sd.suniq
),
--PARENT ED LEVEL
parented (suniq, edulvlc) AS (
SELECT sc.suniq, z.edulvlc
FROM stucon sc
LEFT OUTER JOIN contacts c ON sc.conuniq = c.conuniq
LEFT OUTER JOIN zedulvl z ON z.edulvlc = c.edulvlc
WHERE sc.seqid = 1
),
--GATE
--Added codetrans lookup table
gate (suniq, gate) AS (
SELECT sd.suniq, 1
FROM studemo sd
JOIN Proghist ph ON ph.suniq = sd.suniq
JOIN codetrans ct ON ct.intcode = ph.specprgc
WHERE ct.tblname = 'zspecprg CALPADS'
AND ISNULL(NULLIF(ct.extcode,''), ct.intcode) = '127'
),
--SPECIAL ED PROGRAM
--Added codetrans lookup table
specprogid (suniq, specprgc) AS (
SELECT sd.suniq, ph.specprgc
FROM studemo sd
INNER JOIN Proghist ph ON ph.suniq = sd.suniq
INNER JOIN codetrans ct ON ct.intcode = ph.specprgc
WHERE ct.tblname = 'zspecprg CALPADS'
AND ISNULL(NULLIF(ct.extcode,''), ct.intcode) = '144'
),
--EL PROGRAM TYPE
elprogtype (suniq, displaytext) AS (
SELECT sd.suniq, placement.placementc
FROM studemo sd
INNER JOIN Proghist ON Proghist.suniq = sd.suniq
JOIN Placement ON Placement.suniq = proghist.suniq
JOIN Zplacement ON Zplacement.placementc = Placement.placementc
WHERE proghist.xdate IS NULL
AND proghist.prgstatc = 'A'
AND proghist.specprgc = '120'
AND zplacement.placmntypc = 'EL'
AND placement.xdate IS NULL
),
--USA ENROLLMENT DATE
usenrollment (suniq, usenrolldate) AS (
SELECT sd.suniq, convert(char(10), xstudemo.firstusenr, 101)
FROM studemo sd
INNER JOIN xstudemo ON xstudemo.suniq = sd.suniq
WHERE xstudemo.firstusenr IS NOT NULL
),
--RFEP DATE
rfepdate (suniq, rfepdate) AS (
SELECT sd.suniq, convert(char(10), xstudemo.redesfep, 101)
FROM studemo sd
INNER JOIN xstudemo ON xstudemo.suniq = sd.suniq
WHERE xstudemo.redesfep IS NOT NULL
),
--PRIMARY DISABILITY
disability (suniq, discode, disdescr, eventdate, rn) AS (
SELECT e.suniq, c.spclass1, zc.descript, eventdate,
row_number() over (partition by e.suniq order by eventdate desc) rn
FROM studemo sd
JOIN spevent e ON e.suniq = sd.suniq
JOIN spclassify c ON c.speventuniq = e.speventuniq
JOIN zspclass zc ON zc.spclassc = c.spclass1
),
--FREE/REDUCED LUNCH PROGRAM
nslp (suniq, fspayc) AS (
SELECT suniq, ISNULL(fspayc,'N')
FROM studemo sd
JOIN fsstatus fs ON fs.auniq = sd.suniq
WHERE atype = 'S'
AND @today BETWEEN ISNULL(edate,@today) AND ISNULL(xdate,
@today)
)
SELECT DISTINCT
s.schyear AS School_SchYear,
s.schuniq AS School_Schuniq,
s.schoolc as School_Schoolc,
sd.suniq AS Studemo_Suniq,
sd.ident AS Studemo_Ident,
sd.lastname AS LN,
sd.firstname AS FN,
LEFT(sd.middlename,1) AS MI,
CONVERT(CHAR(10), sd.birthdate, 101) AS DOB,
sd.genderc AS Gender,
st.graden AS Grade,
t.trackc AS Track,
t.trkuniq AS TrkUniq,
s.schoolc AS CDS,
mm.termc AS Term,
tp.periodc AS Period,
mm.classroom AS Room,
ms.mstuniq AS SectionID,
c.coursec AS CourseID,
c.descript AS Course,
zd.deptc AS Dept,
zd.descript AS DeptDesc,
fd.lastname AS TeacherLast,
fd.firstname AS TeacherFirst,
fd.funiq AS Facdemo_Funiq,
fd.ident AS Facdemo_Ident,
sd.ethnicc AS Ethnicity,
ze.descript AS EthnicityDesc,
st.engprofc AS LangFluency,
zp.descript AS LangFluencyDesc,
sd.homelangc AS HomeLang,
zl.descript AS HomeLangDesc,
sd.migrantnum AS Migrant,
disability.discode AS PrimaryDisability,
disability.disdescr AS PrimaryDisDescription,
sd.stateid AS CSISID,
ISNULL(smobile.egraden, 0) AS SMobile,
ISNULL(dmobile.egraden, 0) AS DMobile,
parented.edulvlc AS ParentEd,
ISNULL(gate.gate, 0) AS GATE,
specprogid.specprgc AS SpEdProgID,
elprogtype.displaytext AS ELProgType,
usenrollment.usenrolldate AS USAEnrollment,
rfepdate.rfepdate AS RFepDate,
CONVERT(CHAR(10), sd.regdate, 101) AS DistEntryDate,
nslp.fspayc AS NSLP,
CONVERT(CHAR(10), ss.edate, 101) AS edate,
CONVERT(CHAR(10), ss.xdate, 101) AS xdate,
CONVERT(CHAR(10), GETDATE(), 101) AS RefreshDate
--Student schedule
FROM stusched ss
--Masterschedule
JOIN mstmeet mm ON mm.meetuniq = ss.meetuniq
JOIN mstsched ms ON ms.mstuniq = mm.mstuniq
--A track is per school calender, schools can have multiple calendars
--Students and faculty can be in multiple tracks and multiple schools
JOIN trkcrs tc ON tc.trkcrsuniq = ms.trkcrsuniq
JOIN track t ON t.trkuniq = tc.trkuniq
JOIN trkper tp ON tp.periodn = mm.periodn
AND tp.trkuniq = t.trkuniq
--Course
JOIN course c ON c.crsuniq = tc.crsuniq
--School
JOIN school s ON s.schoolc = t.schoolc
AND s.schyear = t.schyear
--Cycle days
JOIN trkcycle tcy ON tcy.trkuniq = t.trkuniq
AND tcy.cycldayn = mm.cycldayn
--Departments
LEFT JOIN zdept zd ON zd.deptc = c.deptc
--Faculty
JOIN facdemo fd ON fd.funiq = ms.funiq
--Student demographics (unique)
JOIN studemo sd ON sd.suniq = ss.suniq
--Student statistics, multiple entries per student
JOIN stustat st ON st.suniq = ss.suniq
and st.trkuniq = t.trkuniq -- added by VCOE to eliminate next year records created by New Year
Initialization process
--Ethnicity
LEFT JOIN zethnic ze ON ze.ethnicc = sd.ethnicc
--Languages
LEFT JOIN zlang zl ON zl.langc = sd.homelangc
--More student data
JOIN zstustat zs ON zs.stustatc = st.stustatc
--English proficiency and description
LEFT JOIN zengprof zp ON zp.engprofc = st.engprofc
--Defined in the CTEs above
LEFT JOIN smobile ON smobile.suniq = ss.suniq
LEFT JOIN dmobile ON dmobile.suniq = ss.suniq
LEFT JOIN parented ON parented.suniq = ss.suniq
LEFT JOIN gate on gate.suniq = ss.suniq
LEFT JOIN specprogid ON specprogid.suniq = ss.suniq
LEFT JOIN elprogtype ON elprogtype.suniq = ss.suniq
LEFT JOIN usenrollment ON usenrollment.suniq = ss.suniq
LEFT JOIN rfepdate ON rfepdate.suniq = ss.suniq
LEFT JOIN disability on disability.suniq = ss.suniq
LEFT JOIN nslp ON nslp.suniq = ss.suniq
WHERE
--Get current enrollment..
@today BETWEEN ss.edate AND ss.xdate
--only with Staff IDs..
AND ms.funiq Is Not NULL
--currently active..
AND zs.snstatus IN ('A', 'M', 'P')
--and not exited
AND (st.xdate IS NULL OR st.xdate < st.edate)
--return most recent disability
AND (disability.rn IS NULL or disability.rn = 1)
AND s.schyear = (SELECT MAX(schyear) FROM school)
/*
DECLARE @today DATE = '9/14/2014'
SELECT * from dbo.EADMS_EnrollmentUpdate(@today)
*/
)
GO
RUN ON Q - EADMS - Create and Alter UDF for Faculty Accounts - v05.sql
/****** Object: UserDefinedFunction [dbo].[EADMS_FacultyAccounts] Script Date: 1/20/2016 12:43:49 PM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[EADMS_FacultyAccounts]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
EXEC sp_executesql N'CREATE FUNCTION [dbo].[EADMS_FacultyAccounts] (@today DATE)
RETURNS TABLE AS RETURN (SELECT 1 [Stub])'
END
GO
ALTER FUNCTION [dbo].[EADMS_FacultyAccounts]
(@today DATE)
RETURNS TABLE
AS
RETURN
/*
July 2015 by michael@eadms.com
v01 - Rough script
v02 - Sent to VCOE
v03 - Added facdemo.emailaddr
v04 - changed facstat-facdemo join to funiq (was facuniq)
v05 - changed job-description look-up to use LookupCode table
Table based user-defined function to retrieve district
staff and faculty accounts
NEED TO DO
Get email address
Verify current faculty
Verify access levels are correct
Verify logins are accurate
*/
(
SELECT
s.schyear AS School_Schyear,
s.schuniq AS School_Schuniq,
s.schoolc AS School_Schoolc,
fd.funiq AS Facdemo_Funiq,
fd.ident AS Facdemo_Ident,
fd.facuniq AS Facdemo_Facuniq,
fd.lastname AS Facdemo_Lastname,
fd.firstname AS Facdemo_Firstname,
fu.userid as Facuid_Userid,
t.trkuniq AS Track_Trkuniq,
© IO Education 2017. All Rights Reserved. Revised
1
2
/13/18 Page
8
--Not sure if district email is stored in Q
fd.emailaddr as Email,
--Flagged access levels
CASE
WHEN fs.dbadmin = 1 THEN 'Admin'
WHEN fs.siteadmin = 1 THEN 'Site'
WHEN fs.counsel = 1 THEN 'Counselor'
WHEN fs.teacher = 1 THEN 'Teacher'
WHEN fs.certif = 1 THEN 'Certif'
ELSE ''
END AS AccessLevel,
--Use facdemo.ident unless facuid.userid has value
CASE ISNULL(sp.prefval, 'NO')
WHEN 'NO' THEN fd.ident
ELSE ISNULL(fu.userid, fd.ident)
END AS loginid,
--AD login?
ISNULL(sp.prefval, 'NO') as ADSLogin,
--Job description, teacher, certificated, para, etc.
z.DisplayText AS Zjob_Descript
--Job description
FROM LookupCode z
--Track
JOIN track t
--Faculty information
JOIN facstat fs
ON fs.trkuniq = t.trkuniq
ON z.code = fs.jobc
--Faculty demographics
JOIN facdemo fd
ON fd.funiq = fs.funiq
--School information
JOIN school s
ON s.schoolc = t.schoolc
AND s.schyear = t.schyear
--Faculty userid
LEFT JOIN facuid fu
ON fu.funiq = fd.funiq
--System preferences, is Q using Active Directory?
JOIN snpref sp
ON sp.prefname = 'DSLoginFac'
WHERE
--Not an admin...
fd.funiq <> 0
AND fd.funiq <> 1
--....is active
AND fs.facstatc = 'A'
--...is current
AND (@today BETWEEN fs.edate AND fs.xdate
OR (fs.edate < @today AND fs.xdate IS NULL))
--...this year
AND s.schyear = (SELECT MAX(schyear) FROM school)
--..and not district office
AND s.isdo = 0
--...get job
-classifications
AND z.LookupCategoryCode = 'StaffJobClassification'
/*
© IO Education 2017. All Rights Reserved. Revised 12/13/18 Page 9
DECLARE @today DATE = '9/14/2014'
SELECT * from dbo.EADMS_FacultyAccounts(@today)
*/
)
GO
RUN ON Q - Q Menu Link to EADMS.sql
SET NOCOUNT ON
GO
declare @AreaName varchar(50), @AppName varchar(50), @ParentMenu varchar(50), @AppType varchar(50)
, @URL varchar(MAX), @Target varchar(10), @Authenticate varchar(20)
-- Area Name MUST be '' for External URLS
set @AreaName=''
set @AppName='EADMS'
set @ParentMenu='Analysis'
set @AppType='Analysis'
set @URL='https://eadms.com/eadmsx/externallogin.aspx?o=89'
set @Target='_blank'
set @Authenticate='Authenticate'
PRINT 'Adding EADMS Menu Item. . . .'
IF NOT EXISTS (SELECT 1 FROM App WHERE DisplayText = @AppName and ActionName = 'Launcher')
INSERT INTO App (DisplayText,AreaName,ControllerName,ActionName,URL,TargetName,AppParameter)
VALUES (@AppName,@AreaName,'Home','Launcher',@URL,@Target,@Authenticate)
DECLARE @AppId AS int
SELECT @AppId = (SELECT ID FROM App WHERE DisplayText = @AppName and ActionName = 'Launcher')
IF NOT EXISTS (SELECT 1 FROM Menu WHERE Title = @AppName)
INSERT INTO Menu (ParentID,Sequence,AppID,Title)
SELECT (SELECT ID FROM Menu WHERE Title = @ParentMenu AND ParentID IS NULL),
(SELECT MAX(Sequence) + 1
FROM Menu WHERE ParentID = (
SELECT ID FROM Menu WHERE Title = @ParentMenu AND ParentID IS NULL )
),
@AppId,
@AppName
IF NOT EXISTS (SELECT 1 FROM AppCategory WHERE AppID = @AppId AND AppTypeCode = @AppType)
INSERT INTO AppCategory (AppID,MenuCategoryCode,AppTypeCode)
VALUES (@AppId,@ParentMenu,@AppType)
IF NOT EXISTS (SELECT 1 FROM AppRights WHERE AppID = @AppId)
INSERT INTO AppRights (AppID,DisplayText,Sequence)
VALUES (@AppId,@AppName,1)
IF NOT EXISTS (SELECT 1 FROM AppRightsPermMapping WHERE AppId = @AppId)
INSERT INTO AppRightsPermMapping
(AppId,Sequence,PermApplication,PermItemDescription,AreaName,canSelect,canEdit
,canAdd,canDelete,canSetup,isAdmin,subRight)
VALUES (@AppId,1,@AppName,'Application',@AreaName,1,0,0,0,0,0,0)
IF NOT EXISTS (SELECT 1 FROM permitems WHERE application ='Q' AND descript = 'EADMS')
BEGIN
DECLARE @nextuniq int<br />
EXEC snNextUniqID_Silent 'permitems', @lnnext = @nextuniq OUTPUT
INSERT INTO permitems (permuniq,application,descript)
VALUES (@nextuniq,'Q','EADMS')
END
GO
PRINT ' done'
GO
Comments
0 comments
Article is closed for comments.