Students

Student Record

 

 

Student Degree

 

 

Student Course Data

 

 

XML

 

<?xml version="1.0" encoding="utf-8" ?>

<!--

   *

   * CampusVueStudentImport.xml

   * Copyright (C) 2006-2009, Ad Astra Information Systems, LLC.

   * All Rights Reserved

   *

   * Note: This spoke file requires an entry in the SystemSetting table with KeyName=PrimarySisConnectionString

-->

<ATE xmlns="http://aais.com/schema/7.4.0/ATE/ATE"

    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"

    xmlns:soap12="http://www.w3.org/2003/05/soap-envelope"

    version="2.0" jobType="Import" validMode="Batch">

 

 <Summary>

   <![CDATA[Generic CampusVue MsSql Student Import.]]>

 </Summary>

 <JobParameters>

   <JobParameterGroup caption="CampusVue Database Settings">

     <JobParameter name="SISUser" caption="User Id"/>

     <JobParameter name="SISPass" caption="Password" password="true"/>

   </JobParameterGroup>

   <SystemParameter name="SisConnectString" settingKey="PrimarySisConnectionString"/>

 

   <ListParameter name="AnalysisId" caption="Analysis Run" listSourceEntity="Analysis" listSourceKeyProperty="Id" allowMultiple="false"/>

   <ListParameter name="CampusId" caption="Campus" listSourceEntity="Campus" listSourceKeyProperty="SisKey" allowMultiple="true"/>

   <ListParameter name="StatusId" caption="Student Status" listSourceEntity="StudentStatus" listSourceKeyProperty="SisKey" allowMultiple="true"/>

 </JobParameters>

 

 <EndPoints>  

   <DbmsEndPoint name="PrimarySIS" adapterType="MsSql">

     <Parameters>

       <Parameter name="SisConnectString" ref="SisConnectString"/>

       <Parameter name="SISUID" ref="SISUser"/>

       <Parameter name="SISPwd" ref="SISPass"/>

     </Parameters>

     <ConnectionString>

       <![CDATA[${SisConnectString};User Id=${SISUID};Password=${SISPwd};]]>

     </ConnectionString>

   </DbmsEndPoint>

 </EndPoints>

 

 <Pipeline>

   <Sources>

 

   <Source name="AnalysisStudentImport" comment="List Student IDs from CVue from AdEnrollSched (History)" endPoint="PrimarySIS">

     <SqlParameters>

       <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId" />

     </SqlParameters>

     <Sql>

       <![CDATA[

SELECT DISTINCT

     es.systudentid AS SisKey,

     es.adtermid AS TermId

FROM AdEnrollSched es

INNER JOIN AdEnroll e on e.adenrollid = es.adenrollid

WHERE e.SyCampusId = @CampusId

       ]]>

     </Sql>

     <Schema entityName="AnalysisStudentRecord" groupName="AnalysisStudents"/>

   </Source>

 

   <Source name="AnalysisStudentImportByCampusStatus" comment="List Student IDs from CVue based on Campus and Status" endPoint="PrimarySIS">

     <SqlParameters>

       <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId" />

       <SqlParameter sqlBind="@StatusId" name="StatusId" ref="StatusId" />

     </SqlParameters>

     <Sql >

       <![CDATA[

SELECT DISTINCT

   s.SyStudentID AS SisKey,

   'false' PriorTerm

FROM    syStudent s

INNER JOIN AdEnroll e On s.SyStudentID = e.SyStudentID

INNER JOIN SySchoolStatus es On es.SySchoolStatusID = e.SySchoolStatusID

WHERE    s.SySchoolStatusID = @StatusId

AND e.SyCampusId = @CampusId

AND s.Active = 1

       ]]>

     </Sql>

     <Schema entityName="AnalysisStudentRecord" groupName="AnalysisStudents"/>

   </Source>

 

   <Source name="TermInAnalysis" comment="Get Term List For Analysis" endPoint="Astra">

     <SqlParameters>

       <SqlParameter sqlBind="@AnalysisId" name="AnalysisId" ref="AnalysisId" />

     </SqlParameters>

     <Sql >

       <![CDATA[

SELECT DISTINCT

   T.siskey SelectedTermId,

   'false' PriorTerm,

   0 InprogressTerm

FROM Terms T

INNER JOIN Analysis A

ON A.TermId = T.Id

WHERE A.Id  = @AnalysisId

AND T.IsDeleted = 0

 

UNION

 

SELECT DISTINCT

   T.siskey SelectedTermId,

   'true' PriorTerm,

   A.priorterminprogress InprogressTerm

FROM Terms T

INNER JOIN Analysis A

ON A.PriorTermId = T.Id

WHERE A.Id       = @AnalysisId

AND T.IsDeleted = 0

 

UNION

 

SELECT DISTINCT

   T.siskey SelectedTermId,

   'false' PriorTerm,

   A.TermInProgress InprogressTerm

FROM Terms T

INNER JOIN AnalysisLikeTerms A

ON A.TermId        = T.Id

WHERE A.AnalysisId = @AnalysisId

AND A.IsDeleted = 0

 

UNION

 

SELECT DISTINCT

   T.siskey SelectedTermId,

   'false' PriorTerm,

   A.priorterminprogress InprogressTerm

FROM Terms T

INNER JOIN AnalysisLikeTerms A

ON A.PriorTermId   = T.Id

WHERE A.AnalysisId = @AnalysisId

AND A.IsDeleted = 0

       

         ]]>

     </Sql>

     <Schema entityName="AnalysisTermRecord" groupName="AnalysisTerms"/>

   </Source>

 

   <Source name="AnalysisStudentsBaseData" comment="Get List from Astra of Students to pull base data" endPoint="Astra">

     <SqlParameters>

       <SqlParameter sqlBind="@AnalysisId" name="AnalysisId" ref="AnalysisId" />

     </SqlParameters>

     <Sql >

       <![CDATA[

SELECT DISTINCT

   StudentSiskey

FROM AnalysisStudentImport A

Where A.AnalysisId = @AnalysisId

       ]]>

     </Sql>

     <Schema entityName="AnalysisStudentRecord" groupName="AnalysisStudents"/>

   </Source>  

 

           <!-- Top Level (Student) Data -->          

           <Source name="StudentBaseDataForAnalysis" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId" />

       </SqlParameters>

       <Sql >

                                 <![CDATA[

SELECT DISTINCT

   s.SyStudentID AS SisKey,

   s.Active AS ActiveCode,

   rtrim(s.FirstName) AS FirstName,

   rtrim(s.MiddleName) AS MiddleName,

   rtrim(s.LastName) AS LastName,

   rtrim(s.Suffix) AS Suffix,

   s.SySchoolStatusID AS Status,

   rtrim(s.email) AS Email,

   rtrim(sh.Code) AS SisShiftCode

FROM    syStudent s

INNER JOIN AdEnroll e On s.SyStudentID = e.SyStudentID

LEFT OUTER JOIN AdShift sh On sh.AdShiftID = s.AdShiftID

WHERE    e.SyCampusId = @CampusId

                   ]]>

               </Sql>

               <Schema entityName="StudentRecord" groupName="Students"/>

           </Source>

 

 

   <Source name="AnalysisStudentsBaseDataForDegree" comment="Get List from Astra of Students to pull base data" endPoint="Astra">

     <SqlParameters>

       <SqlParameter sqlBind="@AnalysisId" name="AnalysisId" ref="AnalysisId" />

     </SqlParameters>

     <Sql >

       <![CDATA[

SELECT DISTINCT StudentSiskey,StudentId

FROM AnalysisStudentImport A

Where A.AnalysisId = @AnalysisId

       ]]>

     </Sql>

     <Schema entityName="AnalysisStudentRecord" groupName="AnalysisStudents"/>

   </Source>

 

     <Source name="StudentDegreeData" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId" />

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

   e.AdEnrollID AS SisKey,

   e.SyStudentID AS StudSisKey,

   Case When rtrim(s.Code) = 'ATT' Then 'true' Else 'false' End AS IsDefault,

   e.AdProgramID As ProgramSisKey,

   rtrim(v.Code) As CatalogVersion,

   v.AdDegreeID As DegreeSisKey,

   e.SyCampusID AS Campus

FROM    AdEnroll e

INNER JOIN AdProgramVersion v On v.AdProgramVersionID = e.adProgramVersionID

INNER JOIN SySchoolStatus s On s.SySchoolStatusID = e.SySchoolStatusID

WHERE e.AdProgramID is not null and e.SyCampusId = @CampusId

ORDER BY e.SyStudentID, IsDefault, e.StatusDate DESC

                   ]]>

       </Sql>

       <Schema entityName="StudentDegreeRecord" groupName="StudentDegrees"/>

     </Source>

 

   <Source name="AnalysisStudentsBaseDataForHistory" comment="Get List from Astra of Students that need academic history" endPoint="Astra">

     <SqlParameters>

       <SqlParameter sqlBind=":AnalysisId" name="AnalysisId" ref="AnalysisId" />

     </SqlParameters>

     <Sql >

       <![CDATA[

SELECT DISTINCT StudentSiskey,StudentId

FROM AnalysisStudentImport A

Where A.AnalysisId = @AnalysisId

       ]]>

     </Sql>

     <Schema entityName="AnalysisStudentRecord" groupName="AnalysisStudents"/>

   </Source>

 

   <Source name="StudentCourseData" endPoint="PrimarySIS">

     <SqlParameters>

       <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId" />

     </SqlParameters>

     <Sql >

       <![CDATA[

SELECT

   es.AdEnrollSchedID AS SisKey,

   e.SyStudentID AS StudSisKey,

   es.AdTermID AS Term,

   rtrim(cr.Code) AS Subject,

   rtrim(cr.Code) AS CourseNumber,

   es.AdCourseID AS CourseSisKey,

   es.Credits AS CreditHours,

   rtrim(es.AdGradeLetterCode) AS Grade,

   es.QualityPoints As QualityPoints,

   es.TransferCredit As IsTransfer,

   rtrim(es.Descrip) As Title,

   es.DateLstMod AS ActivityDate,

   Case When rtrim(es.status) = 'P' Then 'true'

   Else 'false'

   End AS IsCompleted,

   Case When rtrim(es.status) = 'C' Then 'true'

   Else 'false'

   End AS IsInProgress

FROM    AdEnrollSched es

INNER JOIN AdCourse cr On cr.AdCourseID = es.AdCourseID

INNER JOIN AdEnroll e On e.AdEnrollId = es.AdEnrollId

WHERE e.SyCampusId = @CampusId

AND rtrim(es.status) IN ('C','P')  -- "P" = complete, "C" = current

                   ]]>

     </Sql>

     <Schema entityName="StudentCourseRecord" groupName="StudentCourses"/>

   </Source>

 

   <Source name="AstraStudentHistoryCourseSummary" comment="Call proc to refresh StudentHistoryCrseSum table" endPoint="Astra">

     <Sql >

       <![CDATA[

exec dbo.PopulateStudentHistoryCrseSum

       ]]>

     </Sql>

     <Schema entityName="EmptyResult" groupName="EmptyResults"/>

   </Source>

 

   </Sources>

 

   <Phases>

   

     <Phase name="AnalysisStudentImport">

       <SetOperation name="AnalysisStudentImportSet" >

         <Aggregate type="innerJoin" pageSize="100000">

           <Left ref="TermInAnalysis" tableName="AnalysisTermRecord" keyColumns="SelectedTermId"/>

           <Right ref="AnalysisStudentImport" tableName="AnalysisStudentRecord" keyColumns="TermId"/>

         </Aggregate>

       </SetOperation>

     

       <Target endPoint="Astra">

         <AstraEnvironment>

           <Hub className="AnalysisStudentImportHub">

             <Reader ref="AnalysisStudentImportSet"/>

           </Hub>

         </AstraEnvironment>

       </Target>

     </Phase>

 

     <Phase name="AnalysisStudentImportByCampusStatus">

 

       <Fetch ref="AnalysisStudentImportByCampusStatus"/>

 

       <Target endPoint="Astra">

         <AstraEnvironment>

           <Hub className="AnalysisStudentImportHub">

             <Reader ref="AnalysisStudentImportByCampusStatus"/>

           </Hub>

         </AstraEnvironment>

       </Target>

     </Phase>

 

     <Phase name="ImportAnalysisStudentBaseData">

       <SetOperation name="AnalysisStudentBaseDataSet" >

         <Aggregate type="innerJoin" pageSize="100000">

           <Left ref="AnalysisStudentsBaseData" tableName="AnalysisStudentRecord" keyColumns="StudentSiskey"/>

           <Right ref="StudentBaseDataForAnalysis" tableName="StudentRecord" keyColumns="SisKey"/>

         </Aggregate>

       </SetOperation>

       <Target endPoint="Astra">

         <AstraEnvironment>

           <Hub className="StudentBaseDataImportHub">

             <Reader ref="AnalysisStudentBaseDataSet"/>

           </Hub>

         </AstraEnvironment>

       </Target>

     </Phase>

 

     <Phase name="ImportStudentDegreeData">

       <SetOperation name="StudentDegreeDataSet" >

         <Aggregate type="innerJoin" pageSize="100000">

           <Left ref="AnalysisStudentsBaseDataForDegree" tableName="AnalysisStudentRecord" keyColumns="StudentSiskey"/>

           <Right ref="StudentDegreeData" tableName="StudentDegreeRecord" keyColumns="StudSisKey"/>

         </Aggregate>

       </SetOperation>

 

       <Target endPoint="Astra">

         <AstraEnvironment>

           <Hub className="StudentDegreeImportHub">

             <Reader ref="StudentDegreeDataSet"/>

           </Hub>

         </AstraEnvironment>

       </Target>

     </Phase>

 

     <Phase name="ImportStudentHistoryData">

       <SetOperation name="StudentCourseDataSet" >

         <Aggregate type="innerJoin" pageSize="100000">

           <Left ref="AnalysisStudentsBaseDataForHistory" tableName="AnalysisStudentRecord" keyColumns="StudentSiskey"/>

           <Right ref="StudentCourseData" tableName="StudentCourseRecord" keyColumns="StudSisKey"/>

         </Aggregate>

       </SetOperation>

 

       <Target endPoint="Astra">

         <AstraEnvironment>

           <Hub className="StudentCourseImportHub">

             <Reader ref="StudentCourseDataSet"/>

           </Hub>

         </AstraEnvironment>

       </Target>

     </Phase>

 

     <Phase name="PopulateStudentHistoryCourseSummary">

 

       <Fetch ref="AstraStudentHistoryCourseSummary"/>

 

       <Target endPoint="Astra"/>

     

     </Phase>

   

   </Phases>

 

 </Pipeline>

</ATE>

 

Page url: ?students.htm