Example named query file for PowerSchool Clever sync

clever.named_queries

# This is an example only. I no longer use it and hope to update it eventually.
# If you are starting from scratch you should be using the users table not the teachers view
# for creating a PK for your staff. If you don't, teachers who teach at multiple schools will
# not work correctly. My fix was to just keep using the schoolstaff id's (that is what the 
# teacher view uses) but make sure it is always the users homeschool's sschoolstaff id. It'd be
# cleaner to have just used the users table to begin with though. I have a much better understanding
# of mysql and ps tables then I was when I first wrote this.

<queries>
    <query name="org.yourschool.clever.schools.all" coreTable="SCHOOLS" flattened="true">
        <description>Pull school data for clever.</description>
        <args>
        </args>
        <columns>
        <column column="SCHOOLS.SCHOOL_NUMBER">School_id</column>
        <column column="SCHOOLS.NAME">School_name</column>
        <column column="SCHOOLS.SCHOOL_NUMBER">School_number</column>
        <column column="S_IL_SCH_X.RCDTS_HOME_SCHOOL">State_id</column>
        <column column="SCHOOLS.SCHOOL_NUMBER">Nces_id</column>
        <column column="SCHOOLS.LOW_GRADE">Low_grade</column>
        <column column="SCHOOLS.HIGH_GRADE">High_grade</column>
		<column column="SCHOOLS.PRINCIPAL">Principal</column>
		<column column="SCHOOLS.PRINCIPALEMAIL">Principal_email</column>
		<column column="SCHOOLS.SCHOOLADDRESS">School_address</column>
		<column column="SCHOOLS.SCHOOLCITY">School_city</column>
		<column column="SCHOOLS.SCHOOLSTATE">School_state</column>
		<column column="SCHOOLS.SCHOOLZIP">School_zip</column>
		<column column="SCHOOLS.SCHOOLPHONE">School_phone</column>
 		</columns>
        <sql>
	        
			<![CDATA[
				select schools.school_number,schools.name,schools.school_number psnumber ,S_IL_SCH_X.RCDTS_HOME_SCHOOL,'' as Nces_id,
				case  
				  when schools.low_grade = 0 then 'Kindergarten'
				  when schools.low_grade < 0 then 'Prekindergarten'
				  when schools.low_grade > 12 then 'Postgraduate'
				  else to_char(schools.low_grade)
				end as lowgrade,
				case 
				  when schools.high_grade = 0 then 'Kindergarten'
				  when schools.high_grade < 0 then 'Prekindergarten'
				  when schools.high_grade > 12 then 'Postgraduate'
				  else to_char(schools.high_grade)
				end as highgrade,
				schools.principal,schools.principalemail,schools.schooladdress,schools.schoolcity,schools.schoolstate,schools.schoolzip,schools.schoolphone from schools
				join S_IL_SCH_X on schools.dcid = S_IL_SCH_X.schoolsdcid
			]]>
        </sql>
    </query>

       <query name="org.yourschool.clever.students.all" coreTable="STUDENTS" flattened="true">
        <description>Pull student data for clever.</description>
        <args>
        </args>
        <columns>
        <column column="STUDENTS.SCHOOLID">School_id</column>
        <column column="STUDENTS.ID">Student_id</column>
        <column column="STUDENTS.STUDENT_NUMBER">Student_number</column>
        <column column="STUDENTS.STATE_STUDENTNUMBER">State_id</column>
        <column column="STUDENTS.LAST_NAME">Last_name</column>
        <column column="STUDENTS.MIDDLE_NAME">Middle_name</column>
        <column column="STUDENTS.FIRST_NAME">First_name</column>
        <column column="STUDENTS.GRADE_LEVEL">Grade</column>
        <column column="STUDENTS.GENDER">Gender</column>
        <column column="STUDENTS.DOB">DOB</column>
        <column column="STUDENTS.ETHNICITY">Race</column>
        <column column="STUDENTS.FEDETHNICITY">Hispanic_Latino</column>
        <column column="S_IL_STU_X.LEP">Ell_status</column>
        <column column="STUDENTS.LUNCHSTATUS">Frl_status</column>
        <column column="S_IL_STU_X.IEP">IEP_status</column>
        <column column="STUDENTS.STREET">Student_street</column>
        <column column="STUDENTS.CITY">Student_city</column>
        <column column="STUDENTS.STATE">Student_state</column>
        <column column="STUDENTS.ZIP">Student_zip</column>
        <column column="PSM_STUDENTCONTACT.EMAIL">Student email</column>
        <column column="STUDENTS.MOTHER">Contact_relationship</column>
        <column column="STUDENTS.MOTHER">Contact_type</column>
        <column column="STUDENTS.MOTHER">Contact_name</column>
        <column column="STUDENTS.HOME_PHONE">Contact_phone</column>
        <column column="STUDENTS.GUARDIANEMAIL">Contact_email</column>
        <column column="PSM_STUDENTCONTACT.EMAIL">Username</column>
        <column column="STUDENTS.STUDENT_NUMBER">Password</column>
 		</columns>
        <sql>
			<![CDATA[
				SELECT
					st.schoolid,
					st. ID,
					st.student_number,
					st.State_StudentNumber,
					st.last_name,
					st.middle_name,
					st.first_name,
					CASE
				WHEN st.grade_level = 0 THEN
					'Kindergarten'
				WHEN st.grade_level < 0 THEN
					'Prekindergarten'
				WHEN st.grade_level > 12 THEN
					'Postgraduate'
				ELSE
					TO_CHAR(st.grade_level)
				END AS Grade,
				 st.gender,
				 TO_CHAR(st.dob, 'mm/dd/yyyy') dob,
				 CASE
				WHEN st.Ethnicity IN('A', 'B', 'I', 'M', 'P', 'W') THEN
					st.Ethnicity
				ELSE
					''
				END AS race,
				 CASE
				WHEN st.FedEthnicity = 1 THEN
					'Y'
				ELSE
					'N'
				END AS Hispanic,
				 CASE
				WHEN S_IL_STU_X.LEP = '01' THEN
					'Y'
				ELSE
					'N'
				END AS ell_status,
				 st.LunchStatus,
				 CASE
				WHEN S_IL_STU_X.IEP = 1 THEN
					'Y'
				ELSE
					'N'
				END AS iep_status,
				 st.street,
				 st.city,
				 'IL' AS state,
				 st.zip,
				(
					SELECT
						psc.email
					FROM
						PSM_STudentcontact psc
					INNER JOIN psm_studentcontacttype psct ON psc.studentcontacttypeid = psct. ID
					AND psct. NAME = 'Self'
					INNER JOIN sync_studentmap ssm ON psc.studentid = ssm.studentid
					INNER JOIN students stu ON ssm.studentsdcid = st.dcid
					AND stu. ID = st. ID
				) stu_email,
				 contacts.contact_relationship AS contact_relationship,
				 contacts.contact_type AS contact_type,
				 contacts.Contact_name AS Contact_name,
				 contacts.Contact_phone AS Contact_phone,
				 contacts.Contact_email AS Contact_email,
				(
					SELECT psc.email FROM PSM_STudentcontact psc 
					INNER JOIN psm_studentcontacttype psct ON psc.studentcontacttypeid = psct.ID AND psct.NAME= 'Self'
					INNER JOIN sync_studentmap ssm ON psc.studentid = ssm.studentid
					INNER JOIN students stu ON ssm.studentsdcid = st.dcid AND stu.ID= st.ID
				) username,
				'changeme' PASSWORD
				FROM students st
				LEFT OUTER JOIN S_IL_STU_X ON st.dcid = S_IL_STU_X.studentsdcid
				LEFT OUTER JOIN U_STUEXT ON st.dcid = U_STUEXT.studentsdcid
				LEFT OUTER JOIN StudentCoreFields ON st.dcid = StudentCoreFields.studentsdcid
				LEFT OUTER JOIN (select
					st.dcid,
					U_STUEXT.P1_RELATIONSHIP || ' - ' || case 
					  when U_STUEXT.P1_GENDER = 'F' then 'Female'
					  when U_STUEXT.P1_GENDER = 'M' then 'Male'
					end as contact_relationship,
					'Parent/Guardian' as contact_type,
					st.mother as Contact_name,
					case 
					  when StudentCoreFields.mother_home_phone is null then StudentCoreFields.motherdayphone
					  else StudentCoreFields.mother_home_phone
					end as Contact_phone,
					st.guardianemail as Contact_email
					from students st
					left outer join U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and st.mother is not null
				
				UNION ALL
				
				select
					st.dcid,
					U_STUEXT.P2_RELATIONSHIP || ' - ' || case 
					  when U_STUEXT.P2_GENDER = 'F' then 'Female'
					  when U_STUEXT.P2_GENDER = 'M' then 'Male'
					end as contact_relationship,
					'Parent/Guardian' as contact_type,
					st.father as Contact_name,
					case 
					  when StudentCoreFields.father_home_phone is null then StudentCoreFields.fatherdayphone
					  else StudentCoreFields.father_home_phone
					end as Contact_phone,
					st.guardianemail as Contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and st.father is not null
				
				
				UNION ALL
				
				select
					st.dcid,
					U_STUEXT.G3_RELATIONSHIP || ' - ' || case 
					  when U_STUEXT.G3_GENDER = 'F' then 'Female'
					  when U_STUEXT.G3_GENDER = 'M' then 'Male'
					end as contact_relationship,
					'Parent/Guardian' as contact_type,
					U_STUEXT.G3_name as Contact_name,
					case 
					  when U_STUEXT.G3_homephone is null then U_STUEXT.G3_workphone
					  else U_STUEXT.G3_homephone
					end as Contact_phone,
					st.guardianemail as Contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and U_STUEXT.G3_name is not null
				
				UNION ALL
				
				select
					st.dcid,
					U_STUEXT.G4_RELATIONSHIP || ' - ' || case 
					  when U_STUEXT.G4_GENDER = 'F' then 'Female'
					  when U_STUEXT.G4_GENDER = 'M' then 'Male'
					end as contact_relationship,
					'Parent/Guardian' as contact_type,
					U_STUEXT.G4_name as Contact_name,
					case 
					  when U_STUEXT.G4_homephone is null then U_STUEXT.G4_workphone
					  else U_STUEXT.G4_homephone
					end as Contact_phone,
					st.guardianemail as Contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and U_STUEXT.G4_name is not null
				
				UNION ALL
				
				select
					st.dcid,
					StudentCoreFields.emerg_1_rel as contact_relationship,
					'Emergency' as contact_type,
					st.emerg_contact_1 as Contact_Name,
					st.Emerg_Phone_1 as Contact_phone,
					TO_CLOB('') as contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and st.emerg_contact_1 is not null
				
				UNION ALL
				
				select
					st.dcid,
					StudentCoreFields.emerg_2_rel as contact_relationship,
					'Emergency' as contact_type,
					st.emerg_contact_2 as Contact_Name,
					st.Emerg_Phone_2 as Contact_phone,
					TO_CLOB('') as contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and st.emerg_contact_2 is not null
				
				UNION ALL
				
				select
					st.dcid,
					StudentCoreFields.emerg_3_rel as contact_relationship,
					'Emergency' as contact_type,
					StudentCoreFields.emerg_contact_3 as Contact_Name,
					StudentCoreFields.emerg_3_phone as Contact_phone,
					TO_CLOB('') as contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and StudentCoreFields.emerg_contact_3 is not null
				
				) contacts ON st.dcid = contacts.dcid
				WHERE st.enroll_status = 0
			]]>
        </sql>
    </query>
    <query name="org.yourschool.clever.teachers.all" coreTable="TEACHERS" flattened="true">
        <description>Pull admins for clever</description>
        <args>
        </args>
        <columns>
        <column column="TEACHERS.SCHOOLID">School_id</column>
        <column column="TEACHERS.ID">Teacher_id</column>
        <column column="TEACHERS.TEACHERNUMBER">Teacher_number</column>
        <column column="S_IL_USR_X.IEIN">State_teacher_id</column>
        <column column="TEACHERS.EMAIL_ADDR">Teacher_email</column>
        <column column="TEACHERS.FIRST_NAME">First_name</column>
        <column column="TEACHERS.MIDDLE_NAME">Middle_name</column>
        <column column="TEACHERS.LAST_NAME">Last_name</column>
        <column column="TEACHERS.TITLE">Title</column>
        <column column="TEACHERS.EMAIL_ADDR">Username</column>
        <column column="TEACHERS.HOME_PHONE">Password</column>
 		</columns>
        <sql>
	        
			<![CDATA[
				select teachers.schoolid,teachers.id,teachers.teachernumber,
				S_IL_USR_X.IEIN,teachers.email_addr,
				teachers.first_name,teachers.middle_name,teachers.last_name,teachers.title,teachers.email_addr username,teachers.home_phone
				from teachers
				left outer join S_IL_USR_X on teachers.dcid = S_IL_USR_X.usersdcid
				where teachers.status = 1
			]]>
        </sql>
    </query>
    <query name="org.yourschool.clever.admins.all" coreTable="TEACHERS" flattened="true">
        <description>Pull admins for clever</description>
        <args>
        </args>
        <columns>
        <column column="TEACHERS.HOMESCHOOLID">School_id</column>
        <column column="TEACHERS.DCID">Staff_id</column>
        <column column="TEACHERS.EMAIL_ADDR">Admin_email</column>
        <column column="TEACHERS.FIRST_NAME">First_name</column>
        <column column="TEACHERS.LAST_NAME">Last_name</column>
        <column column="TEACHERS.TITLE">Admin_title</column>
        <column column="TEACHERS.EMAIL_ADDR">Username</column>
        <column column="TEACHERS.HOME_PHONE">Password</column>
 		</columns>
        <sql>
	        
			<![CDATA[
				select homeschoolid,dcid,email_addr,first_name,last_name,title,email_addr username,home_phone from teachers
				where psaccess = 1 and groupvalue in (11,9,24,35,40) and status = 1
			]]>
        </sql>
    </query>
       <query name="org.yourschool.clever.sections.all" coreTable="SECTIONS" flattened="true">
        <description>Pull school data for clever.</description>
        <args>
  	    <arg name="termprefix" column="CC.TERMID" type="primitive" required="true" default = "27" />
        </args>
        <columns>
        <column column="SECTIONS.SCHOOLID">School_id</column>
        <column column="SECTIONS.ID">Section_id</column>
        <column column="SECTIONS.TEACHER">Teacher_id</column>
        <column column="SECTIONS.TEACHER">Teacher_2_id</column>
        <column column="SECTIONS.TEACHER">Teacher_3_id</column>
        <column column="SECTIONS.TEACHER">Teacher_4_id</column>
        <column column="SECTIONS.COURSE_NUMBER">Name</column>
        <column column="SECTIONS.SECTION_NUMBER">Section_number</column>
        <column column="SECTIONS.GRADE_LEVEL">Grade</column>
        <column column="COURSES.COURSE_NAME">Course_name</column>
        <column column="SECTIONS.COURSE_NUMBER">Course_number</column>
        <column column="SECTIONS.COURSE_NUMBER">Course_description</column>
        <column column="SECTIONS.EXPRESSION">Period</column>
        <column column="SECTIONS.EXPRESSION">Subject</column>
        <column column="TERMS.NAME">Term_name</column>
        <column column="TERMS.FIRSTDAY">Term_start</column>
        <column column="TERMS.LASTDAY">Term_end</column>
 		</columns>
        <sql>
			<![CDATA[
				select sec.schoolid,
				sec.id,
				sec.teacher,
				(select teacherid from (select teacherid, rownum rn from SectionTeacher 
					where sectionteacher.roleid != 21
					and SectionTeacher.sectionid = sec.id
					order by teacherid) where rn = 1) as teacher2,
					(select teacherid from (select teacherid, rownum rn from SectionTeacher 
					where sectionteacher.roleid != 21
					and SectionTeacher.sectionid = sec.id
					order by teacherid) where rn = 2) as teacher3,
				(select teacherid from (select teacherid, rownum rn from SectionTeacher 
					where sectionteacher.roleid != 21
					and SectionTeacher.sectionid = sec.id
					order by teacherid) where rn = 3) as teacher4,
				'' as sectionname,
				sec.section_number,
				case 
				  when sec.grade_level = 0 then 'Kindergarten'
				  when sec.grade_level < 0 then 'Prekindergarten'
				  when sec.grade_level > 12 then 'Postgraduate'
				  else to_char(sec.grade_level)
				end as gradelevel,
				courses.course_name,
				sec.course_number,
				'' as description,
				sec.expression,
				'' as subject,
				(select name from terms where id = sec.termid and schoolid = sec.schoolid) termname,
				TO_CHAR((select firstday from terms where id = sec.termid and schoolid = sec.schoolid),'YYY-MM-DD') firstday,
				TO_CHAR((select lastday from terms where id = sec.termid and schoolid = sec.schoolid),'YYY-MM-DD') lastday
				from sections sec
				join courses  on courses.course_number = sec.course_number
				where SUBSTR(TO_CHAR(sec.termid),1,2) = :termprefix
			]]>
        </sql>
    </query>
    <query name="org.yourschool.clever.enrollments.all" coreTable="CC" flattened="true">
        <description>Pull active enrollments for sync with clever</description>
        <args>
  	    <arg name="termprefix" column="CC.TERMID" type="primitive" required="true" default = "27" />
        </args>
        <columns>
        <column column="CC.SCHOOLID">School_id</column>
        <column column="CC.SECTIONID">Section_id</column>
        <column column="CC.STUDENTID">Student_id</column>
 		</columns>
        <sql>
	        
			<![CDATA[
				select c.schoolid,c.sectionid,c.studentid from cc c
				join students stu on stu.id = c.studentid
				where stu.enroll_status = 0
				AND SUBSTR(TO_CHAR(c.termid),1,2) = :termprefix
			]]>
        </sql>
    </query>
</queries>

@gchristian thanks so much for getting back to us. We eventually stumbled on this solution too.

I've started putting together a gist of dumb problems that I've run into and their solutions here: https://gist.github.com/txoof/d09a808abadcb97240c79c4f22931f78