Section Values

 

Section Meeting Values

 

Section Meeting Instructor Values

 

Cross-List Values

 

 

XML

 

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

 <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 Section Import.]]>

 </Summary>

 

 <JobParameters>

   <!-- Hub Configuraton Options -->

   <JobParameter name="DeleteAstraSectionsNotInImportSet" caption="" hidden="true" expr="true" type="bool"/>

   <JobParameter name="DeleteCanceledSections" caption="" hidden="true" expr="true" type="bool"/>

   <JobParameter name="ImportIncompleteSections" caption="" hidden="true" expr="true" type="bool"/>

 

   <!-- Internal -->

   <JobParameter name="SynchronizeDeletes" caption="" hidden="true" expr="DeleteAstraSectionsNotInImportSet" type="bool"/>

 

   <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="CampusId" caption="Campus" listSourceEntity="Campus" listSourceKeyProperty="SisKey" allowMultiple="true"/>

   <ListParameter name="TermId" caption="Term" listSourceEntity="Term" 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="AstraSections" endPoint="PrimarySIS">

         <Astra from="Section" rootElement="Sections">

           <Select column="SisKey" as="SectionSisKey" />

           <Where if="Batch">

             <![CDATA[

             SisKey != null

             && CampusId in ${CampusId}

             && TermId in ${TermId}

           ]]>

           </Where>

           <Where if="OnDemand || Realtime">

             <![CDATA[

             SisKey == ${SectionSisKey}

           ]]>

           </Where>

           <OrderBy column="SectionSisKey" direction="ascending"/>

         </Astra>

       </Source>

 

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

         <Astra from="SectionMeeting" rootElement="SectionMeetings">

           <Select column="SisKey" as="SectionMeetingSisKey" />

           <Where if="Batch">

             <![CDATA[

             SisKey != null

             && Section.CampusId in ${CampusId}

             && Section.TermId in ${TermId}

           ]]>

           </Where>

           <Where if="OnDemand || Realtime">

             <![CDATA[

             Section.SisKey == ${SectionSisKey}

           ]]>

           </Where>

           <OrderBy column="SectionMeetingSisKey" direction="ascending"/>

         </Astra>

       </Source>

     

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

       <Source name="Sections" endPoint="PrimarySIS" xsd="file:\\SIS\SectionSpokeData.xsd">

         <SqlParameters>

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

           <SqlParameter sqlBind="@TermId" name="TermId" ref="TermId" if="Batch"/>

         </SqlParameters>

         <Sql>

           <![CDATA[

SELECT DISTINCT

   CAST(s.AdClassSchedID AS varchar) + '_' + CAST(t.AdTermID AS varchar) AS SectionSisKey,

   t.AdTermID AS TermSisKey,    

   s.AdCourseID AS CourseSisKey,    

   CASE WHEN PATINDEX('%[0-9]%', REPLACE(c.Code, ' ', '')) > 1 THEN

       SUBSTRING( REPLACE(c.Code, ' ', ''), PATINDEX('%[0-9]%', REPLACE(c.Code, ' ', '')), LEN(c.Code) )

   ELSE

       c.Code

   END AS CourseNumber,

   CASE WHEN PATINDEX('%[0-9]%', REPLACE(c.Code, ' ', '')) > 1 THEN

       SUBSTRING( REPLACE(c.Code, ' ', ''), 1, (PATINDEX('%[0-9]%', REPLACE(c.Code, ' ', '')) -1) )

   ELSE

       c.Code

   END AS SubjectSisKey,

 

   CASE WHEN PATINDEX('%[0-9]%', REPLACE(c.Code, ' ', '')) > 1 THEN

       SUBSTRING( REPLACE(c.Code, ' ', ''), 1, (PATINDEX('%[0-9]%', REPLACE(c.Code, ' ', '')) -1) )

   ELSE

       c.Code

   END AS SubjectName,

 

   CASE WHEN PATINDEX('%[0-9]%', REPLACE(c.Code, ' ', '')) > 1 THEN

       SUBSTRING( REPLACE(c.Code, ' ', ''), 1, (PATINDEX('%[0-9]%', REPLACE(c.Code, ' ', '')) -1) )

   ELSE

       c.Code

   END AS SubjectCode,

 

   'Standard' AS DeliveryMethodSisKey,

   s.SyCampusID AS CampusSisKey,

   CAST(s.AdCourseID AS varchar) + '_Standard' AS CourseDeliveryMethodSisKey,

   s.Descrip AS SectionSectionTitle,    

   s.Section AS SectionSectionCode,

   s.MaxStudents AS SectionSisEnrollment,

   s.RegStudents AS SectionSisMaxEnrollment,

   'true' AS SectionIsGradeable,

   s.Active  AS SectionIsActive

FROM    AdClassSched s

INNER JOIN AdCourse c on c.AdCourseID = s.AdCourseID

INNER JOIN AdClassSchedTerm t ON s.AdClassSchedID = t.AdClassSchedID

WHERE    t.AdTermID = @TermId

AND s.SyCampusID = @CampusId

         ]]>

         </Sql>

         <Schema entityName="Section" groupName="Sections"/>

       </Source>

 

 

       <!-- Detail (Child) Data -->

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

         <SqlParameters>

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

           <SqlParameter sqlBind="@TermId" name="TermId" ref="TermId" if="Batch"/>

         </SqlParameters>

         <Sql >

           <![CDATA[

SELECT DISTINCT

   CAST(s.AdClassSchedID AS varchar) + '_' + CAST(t.AdTermID AS varchar) AS SectionSisKey,

   d.AdClassSchedDayID AS SectionMeetingSisKey,

   c.AdCourseTypeID AS MeetingTypeSisKey,

   ct.Code AS MeetingTypeName,

   CASE WHEN d.DayOfWeek = 1 THEN 'U'

        WHEN d.DayOfWeek = 2 THEN 'M'

        WHEN d.DayOfWeek = 3 THEN 'T'

        WHEN d.DayOfWeek = 4 THEN 'W'

        WHEN d.DayOfWeek = 5 THEN 'R'

        WHEN d.DayOfWeek = 6 THEN 'F'

        WHEN d.DayOfWeek = 7 THEN 'S'

        ELSE ''

   END AS SectionMeetingDaysMetPattern,  

   d.StartTime AS SectionMeetingStartTime,

   d.LengthMinutes AS SectionMeetingDuration,

   s.StartDate AS SectionMeetingStartDate,

   s.EndDate AS SectionMeetingEndDate,

   d.AdRoomID AS RoomSisKey  

FROM    AdClassSchedDay d

INNER JOIN AdClassSched s ON s.AdClassSchedID = d.AdClassSchedID

INNER JOIN AdClassSchedTerm t ON s.AdClassSchedID = t.AdClassSchedID

INNER JOIN AdCourse c ON s.AdCourseID = c.AdCourseID

INNER JOIN AdCourseType ct on c.AdCourseTypeID = ct.AdCourseTypeID

LEFT OUTER JOIN AdRoom r ON d.AdRoomID = r.AdRoomID

WHERE    t.AdTermID = @TermId

AND s.SyCampusID = @CampusId

                   ]]>

         </Sql>

         <Schema entityName="SectionMeeting" groupName="SectionMeetings"/>

       </Source>

 

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

         <SqlParameters>

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

           <SqlParameter sqlBind="@TermId" name="TermId" ref="TermId" if="Batch"/>

         </SqlParameters>

         <Sql >

           <![CDATA[

SELECT  

   d.AdClassSchedDayID AS SectionMeetingSisKey,

 

   i.SyStaffID AS InstructorSisKey,

   i.email AS InstructorEmail,

   i.SyStaffID As PersonSisKey,

   i.FirstName AS PersonFirstName,

   i.LastName AS PersonLastName,

   i.MI AS PersonMiddleName,

   i.email PersonEmail        

FROM    AdClassSchedDay d

INNER JOIN AdClassSched s ON s.AdClassSchedID = d.AdClassSchedID

INNER JOIN AdClassSchedTerm t ON s.AdClassSchedID = t.AdClassSchedID

INNER JOIN SyStaff i ON s.AdTeacherID = i.SyStaffId

WHERE    t.AdTermID = @TermId

AND s.SyCampusID = @CampusId

         ]]>

         </Sql>

         <Schema entityName="Instructor" groupName="Instructors"/>

       </Source>

     </Sources>

 

     <Phases>

       <Phase name="ImportSections">

         <Fetch ref="AstraSections" if="SynchronizeDeletes"/>

         <Fetch ref="AstraSectionMeetings" if="SynchronizeDeletes"/>

         <Fetch ref="Sections"/>

         <Fetch ref="SectionMeetings"/>

         <Fetch ref="SectionMeetingInstructors"/>

 

         <SetOperation name="SectionsNotInImportSet" if="SynchronizeDeletes">

           <Difference columns="SectionSisKey" entityName="Section" groupName="Sections">

             <DifferenceSource ref="AstraSections"/>

             <DifferenceSource ref="Sections"/>

           </Difference>

         </SetOperation>

 

         <SetOperation name="SectionMeetingsNotInImportSet" if="SynchronizeDeletes">

           <Difference columns="SectionMeetingSisKey" entityName="SectionMeeting" groupName="SectionMeetings">

             <DifferenceSource ref="AstraSectionMeetings"/>

             <DifferenceSource ref="SectionMeetings"/>

           </Difference>

         </SetOperation>

 

           <Target endPoint="Astra">

           <AstraEnvironment>

           

             <Hub className="SectionHub">

               <Reader ref="Sections"/>

               <HubParameters>

                 <HubParameter name="DeleteCanceledSections" expr="DeleteCanceledSections"/>

                 <HubParameter name="SectionsNotInImportSet" expr="SectionsNotInImportSet" if="SynchronizeDeletes"/>

               </HubParameters>

               <AlternateHubs>

                 <Hub className="CourseHub" />

                 <Hub className="CourseDeliveryMethodHub" />

                 <Hub className="SubjectHub" />

               </AlternateHubs>

             </Hub>

 

             <Hub className="SectionMeetingHub">

               <Reader ref="SectionMeetings"/>

               <HubParameters>

                 <HubParameter name="ImportIncompleteSectionMeetings" expr="ImportIncompleteSections"/>

                 <HubParameter name="SectionMeetingsNotInImportSet" expr="SectionMeetingsNotInImportSet" if="SynchronizeDeletes"/>

               </HubParameters>

               <AlternateHubs>

                 <Hub className="MeetingTypeHub" />

               </AlternateHubs>

             </Hub>

 

             <Hub className="SectionMeetingInstructorHub">

               <Reader ref="SectionMeetingInstructors"/>

             </Hub>

             

         </AstraEnvironment>

         </Target>

 

       </Phase>

 

     </Phases>

 

   </Pipeline>

 

 </ATE>

Page url: ?sections.htm