Building and Room

Building Values

 

 

Addresses Values

 

 

Room Values

 

 

Room Configuration Values

 

 

Room Feature Quantity Values

 

XML

 

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

<!--

   *

   * CampusVueBuildingRoomImport.xml

   * Copyright (C) 2006-2010, 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 Building Room 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="CampusId" caption="Campus" listSourceEntity="Campus" 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>

 

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

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

       <SqlParameters>

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

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

           AdBuilding.AdBuildingID AS BuildingSisKey,

           AdBuilding.Code AS BuildingName,

           AdBuilding.Code AS BuildingBuildingCode,

           AdBuilding.Descrip AS BuildingDescription,

           'false' AS BuildingNoSchedule,

           'false' AS BuildingArrangedSection,

           'false' AS BuildingDoNotOptimize,

           AdBuilding.Active AS BuildingIsActive,

           AdBuilding.SyCampusId AS CampusSisKey

FROM    AdBuilding

WHERE    AdBuilding.SyCampusId = @CampusId and AdBuilding.Active = 1

                   ]]>

       </Sql>

       <Schema entityName="Building" groupName="Buildings"/>

     </Source>

 

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

       <SqlParameters>

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

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT AdBuilding.Address AS AddressAddress1,

   AdBuilding.City AS AddressCity,

   AdBuilding.State AS AddressState,

   AdBuilding.Zip AS AddressZipCode,

   CASE

       WHEN AdBuilding.Address IS NOT NULL

         THEN CAST(AdBuilding.AdBuildingID AS varchar) + '_Address'

       ELSE NULL

     END          AS AddressSiskey,

   AdBuilding.AdBuildingID AS BuildingSisKey

FROM AdBuilding

WHERE AdBuilding.SyCampusId = @CampusId

AND AdBuilding.Active = 1

AND AdBuilding.Address IS NOT NULL

AND LEN(AdBuilding.Address) > 0

                   ]]>

       </Sql>

       <Schema entityName="Address" groupName="Addresses"/>

     </Source>

 

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

       <SqlParameters>

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

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

           AdRoom.AdBuildingID AS BuildingSisKey,

           AdRoom.AdRoomId AS RoomSisKey,

           AdRoom.Code AS RoomName,

           AdRoom.Descrip AS RoomDescription,

           AdRoom.RoomNumber AS RoomRoomNumber,

           AdRoom.Capacity AS RoomMaxOccupancy,

           'false' AS RoomIsShareable,

           0 AS RoomMaxSharedActivities,

           'Classroom' AS RoomTypeSisKey,

           'Classroom' AS RoomTypeName,

           'false' AS RoomNoSchedule,

           'false' AS RoomArrangedSection,

           'false' AS RoomDoNotOptimize,

           '01/01/1753' AS RoomEffectiveStartDate,

           '12/31/9999' AS RoomEffectiveEndDate,

           AdRoom.Active AS RoomIsActive

FROM    AdRoom

INNER JOIN AdBuilding ON AdRoom.AdBuildingID = AdBuilding.AdBuildingID

WHERE    AdBuilding.SyCampusId = @CampusId and AdBuilding.Active = 1

 

                   ]]>

       </Sql>

       <Schema entityName="Room" groupName="Rooms"/>

     </Source>

 

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

       <SqlParameters>

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

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

           AdRoom.AdRoomId AS RoomSisKey,

           CAST(AdRoom.AdRoomId AS varchar) + '_Default' AS RoomConfigurationSisKey,

           'Default Layout' AS RoomConfigurationName,

           'Default' AS FacilityLayoutSisKey,

           'Default' AS FacilityLayoutName,

           AdRoom.Capacity AS RoomConfigurationMeetingCapacity,

           'True'          AS RoomConfigurationIsDefault,

           'False'          AS RoomConfigurationRequiresAuthorization,

           'Default Layout' AS RoomConfigurationDescription,

           'True'           AS RoomConfigurationIsActive

FROM    AdRoom

INNER JOIN AdBuilding ON AdRoom.AdBuildingID = AdBuilding.AdBuildingID

WHERE    AdBuilding.SyCampusId = @CampusId  and AdBuilding.Active = 1

                     ]]>

       </Sql>

       <Schema entityName="RoomConfiguration" groupName="RoomConfigurations"/>

     </Source>

 

     <Source name="RoomFeatureQuantities" endPoint="PrimarySIS" xsd="file:\\SIS\RoomSpokeData.xsd">

       <SqlParameters>

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

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

           AdRoomResources.AdRoomId AS RoomSisKey,

           AdRoomResources.AdResourcesID AS FeatureSisKey,

           AdResources.Code AS FeatureName,

           AdRoomResources.Quantity AS RoomFeatureQuantityQty

FROM    AdRoomResources

INNER JOIN AdResources ON AdRoomResources.AdResourcesID = AdResources.AdResourcesID

INNER JOIN AdRoom ON AdRoom.AdRoomId = AdRoomResources.AdRoomId

INNER JOIN AdBuilding ON AdRoom.AdBuildingID = AdBuilding.AdBuildingID

WHERE    AdBuilding.SyCampusId = @CampusId and AdBuilding.Active = 1

                   ]]>

       </Sql>

       <Schema entityName="RoomFeatureQuantity" groupName="RoomFeatureQuantities"/>

     </Source>

 

   </Sources>

 

   <Phases>

       <Phase name="ImportBuildings">

           <Fetch ref="Buildings"/>

           <Fetch ref="Rooms"/>

     <Fetch ref="Addresses"/>

     <Fetch ref="RoomConfigurations"/>

     <Fetch ref="RoomFeatureQuantities"/>

 

     <Target endPoint="Astra">

             <AstraEnvironment>

 

         <Hub className="BuildingHub">

           <Reader ref="Buildings"/>

           <AlternateHubs>

             <Hub className="CampusHub"/>

           </AlternateHubs>

         </Hub>

 

         <Hub className="RoomHub">

           <Reader ref="Rooms"/>

           <AlternateHubs>

             <Hub className="RoomTypeHub"/>

           </AlternateHubs>

         </Hub>

 

         <Hub className="RoomConfigurationHub">

           <Reader ref="RoomConfigurations"/>

           <AlternateHubs>

             <Hub className="FacilityLayoutHub"/>

           </AlternateHubs>

         </Hub>

                 

           <!--<AlternateHubs reader="Addresses">

             <Hub className="AddressHub"/>

             <Hub className="BuildingHub"/>

           </AlternateHubs>-->

 

       </AstraEnvironment>

           </Target>

         

         </Phase>

       

   </Phases>

 

 </Pipeline>

</ATE>

 

Page url: ?building_and_room.htm