This document describes case studies and sample scripts that may be used to extract data from databases such as Ellucian Colleague and Banner suitable for use with Regroup’s Emergency notification system.

Case Study 1: Colleague Studio

This sample is developed for Colleague Studio and has defined the criteria for the selection of student, faculty and employee records.

 

This sample is to be hard-coded into the program without a user interface, except for that of the Envision Batch Process, which produces the output.

 

This batch process can be assigned to a Colleague mnemonic, “XRGP” in your system. The process can be developed in the CORE application.

 

Output Specification and Mapping

 

This program generates a comma-delimited text file according to Regroup’s specification. This specification is described below with mappings to Colleague data elements, a comma separates each field, and each record occupies a single line of text. 

Field  Colleague File/Element  Notes  First Name  PERSON/FIRST.NAME    Last Name  PERSON/LAST.NAME    Email  PERSON/PERSON.EMAIL.ADDRESSES  The value used is the one associated with a “Y” value in PERSON.PREFERRED.EMAIL   ID  PERSON/ID    Phone  PERSON/PERSONAL.PHONE.NUMBER ADDRESS/ADDRESS.PHONES  The first value is used for each field. The values are separated by a semicolon in the output.  Group  Literal Values (found in the Edit your group settings menu)  For Staff: XXXXXX For Students: XXXXXX For Faculty: XXXXXX For “All”: XXXXXX Multiple values are allowed, separated by a semicolon.  Database Id  Literal Values  Colleague ID  

 

 As the selected Colleague Student, Faculty and Staff records are processed, each one is formatted into an output record using the fields described above. Each record is appended to a text file in the _HOLD_ directory of Colleague. The output files are named according to the date and time with a prefix of the literal ‘1058_’. For example:  1058_1012171024

 

 This file name describes a file that was produced on December 17th, 2010 at 10:24am. The resulting file will ultimately be transferred to Regroup’s system using the file transfer capabilities of the Secure Shell; specifically: the mechanism commonly known as “sftp”.

 

Sample Code

On your colleague instance, you may create a program called XREGROUP in the CORE application and assigned it the mnemonic “XRGP”. The program takes no arguments and has many values hard-coded.  This following code should be used solely as an example and will likely require changes to function with your existing Datatel Colleague system.

 

 ************************************************

* XREGROUP - prototype export process for

* Regroup

*

* Dec. 1 2010

****************************************************

 X.STU.CODE = 166033

 X.STF.CODE = 166031

 X.FAC.CODE = 166032

 X.ALL.CODE = 166289

 

XL.ALLGROUP.IDS = "0494036":@FM:"0497988"

 

 X.DATE = DATE()-30

 X.STMT = "MIOSEL TERMS WITH TERM.START.DATE <= ":X.DATE

 X.STMT := " WITH TERM.END.DATE >= ":X.DATE

 

CALL S.EXECUTE("-H ":X.STMT)

X.STATUS = 0

CALL S.READLIST(XL.TERMS,"",X.STATUS)

 

IF NOT(X.STATUS) THEN

    ERROR.OCCURRED = 1

    MSG = "No terms were selected"

    ABORT.MSG = MSG

    GOTO ABORT.PROCESS

END

 

X.FILE.NAME = ’_HOLD_’

 X.REC.NAME = ’1058_’:TRIM(OCONV(X.DATE,"D2YMD")," ","A"):TRIM(OCONV(TIME(),"MTS"),":","A")

 X.ERROR = ""

 X.MSG = ""

 CALL S.OPEN.SEQ(X.FILE.NAME, X.REC.NAME, "1", "W", X.ERROR, X.MSG)

 

IF X.ERROR THEN

    ERROR.OCCURRED = 1

    ABORT.MSG = X.MSG

    GOTO ABORT.PROCESS

END

 

CONVERT @FM TO " " IN XL.TERMS

 

CALL S.EXECUTE("-H MIOSEL STUDENTS WITH STU.TERMS = ":XL.TERMS)

 X.STATUS = 0

XL.STUDENTS = ""

CALL S.READLIST(XL.STUDENTS,"",X.STATUS)

 

IF X.STATUS THEN XL.PERSON.IDS = XL.STUDENTS

 

CALL S.EXECUTE("-H MIOSEL STAFF WITH STAFF.STATUS = ’C’")

X.STATUS = 0

XL.STAFF = ""

CALL S.READLIST(XL.STAFF,"",X.STATUS)

 

IF X.STATUS THEN XL.PERSON.IDS<-1> = XL.STAFF

 

CALL S.EXECUTE("-H MIOSEL COURSE.SECTIONS WITH SEC.TERM = ":XL.TERMS )

 IF @SYSTEM.RETURN.CODE > 0 THEN

    CALL S.EXECUTE("-H MIOSEL COURSE.SECTIONS SAVING UNIQUE SEC.FACULTY")

    IF @SYSTEM.RETURN.CODE > 0 THEN

         CALL S.EXECUTE("-H MIOSEL COURSE.SEC.FACULTY SAVING UNIQUE CSF.FACULTY")

         X.STATUS = 0

         XL.FACUTLY = ""

         CALL S.READLIST(XL.FACULTY,"",X.STATUS)

         IF X.STATUS THEN XL.PERSON.IDS<-1> = XL.FACULTY

    END

END

 

CALL S.FORMLIST(XL.PERSON.IDS,"")

 

FOR_EACH SELECTED REFERENCED ID

 

    X.EMAIL = ""

    LOCATE "Y" IN VL.PERSON.PREFERRED.EMAIL<1,1> SETTING POS THEN

        X.EMAIL = VL.PERSON.EMAIL.ADDRESSES<1,POS>

    END

 

    X.PHONE = VL.PERSONAL.PHONE.NUMBER<1,1>

 

    FOR_THE SECONDARY REFERENCED PREFERRED.ADDRESS

    END_THE PREFERRED ADDRESS

 

    X.PHONE<1,-1> = VL.ADDRESS.PHONES

    CONVERT @VM TO ’;’ IN X.PHONE

 

    X.GROUP = ""

  LOCATE V.ID IN XL.STUDENTS<1> SETTING POS THEN

         X.GROUP = X.STU.CODE

    END

 

    LOCATE V.ID IN XL.FACULTY<1> SETTING POS THEN

        X.GROUP<-1> = X.FAC.CODE

    END

 

    LOCATE V.ID IN XL.STAFF<1> SETTING POS THEN

        X.GROUP<-1> = X.STA.CODE

    END

 

    LOCATE V.ID IN XL.ALLGROUP.IDS<1> SETTING POS THEN

        X.GROUP<-1> = X.ALL.CODE

    END

  CONVERT @FM TO ’;’ IN X.GROUP

    X.OUTPUT = CONVERT(’,’,"",V.FIRST.NAME)

    X.OUTPUT<-1> = CONVERT(’,’,"",V.LAST.NAME)

    X.OUTPUT<-1> = X.EMAIL

    X.OUTPUT<-1> = V.ID ;* This could be userid

   X.OUTPUT<-1> = X.PHONE

    X.OUTPUT<-1> = X.GROUP

 

    X.ERROR = ""

    X.MSG = ""

    CALL S.WRITE.SEQ(X.OUTPUT.REC, "1", X.ERROR, X.MSG)

 

    IF X.ERROR THEN

        ERROR.OCCURRED = 1

        MSG = X.MSG

        GOSUB ERROR.MESSAGE

    END

 

END_EACH ID

 

    * close the sequential file

    X.ERROR = ""

    X.MSG = ""

    CALL S.CLOSE.SEQ("1",X.ERROR,X.MSG)

 

Implementation

This program may be introduced to a new client using Colleague Studio or the traditional Envision ToolKit screens.  In either case, it should be defined as a Batch Process and should be given a mnemonic. Other parameters for batch processing will not be needed to be changed from the system default values and should not affect the outcome.

 

Case Study 2: SQL

 

What Database system are you using?  Datatel Colleague

What tools do you run on top of this system? Microsoft SQL Server Management Studio

What language is your DB in? SQL

Please paste the script that you use to extract a .csv from your database.
 
Script to create SQL view.
 
CREATE VIEW [dbo].[U81_REGROUP_ACCOUNTS_VIEW]
AS
SELECT DISTINCT dbo.STUDENT_ACAD_CRED.STC_PERSON_ID AS ID, dbo.PERSON.FIRST_NAME, dbo.PERSON.LAST_NAME,
                          (SELECT     PERSON_EMAIL_ADDRESSES
                            FROM          dbo.PEOPLE_EMAIL
                            WHERE      (PERSON_EMAIL_TYPES = 'PRI') AND (ID = dbo.STUDENT_ACAD_CRED.STC_PERSON_ID)) AS EMAIL,
                      dbo.PERSON_PIN.PERSON_PIN_USER_ID AS USER_ID,
                          (SELECT     STU_HOME_LOCATION
                            FROM          dbo.STU_HOME_LOCATIONS
                            WHERE      (POS = 1) AND (STUDENTS_ID = dbo.STUDENT_ACAD_CRED.STC_PERSON_ID)) AS LOCATION,
                      ISNULL(dbo.U81_GET_REGROUP_CELLPHONE(dbo.STUDENT_ACAD_CRED.STC_PERSON_ID), '') AS [CELL PHONE]
FROM         dbo.STUDENT_ACAD_CRED INNER JOIN
                      dbo.STC_STATUSES ON dbo.STUDENT_ACAD_CRED.STUDENT_ACAD_CRED_ID = dbo.STC_STATUSES.STUDENT_ACAD_CRED_ID INNER JOIN
                      dbo.TERMS ON dbo.STUDENT_ACAD_CRED.STC_TERM = dbo.TERMS.TERMS_ID INNER JOIN
                      dbo.PERSON ON dbo.STUDENT_ACAD_CRED.STC_PERSON_ID = dbo.PERSON.ID INNER JOIN
                      dbo.PERSON_PIN ON dbo.PERSON.ID = dbo.PERSON_PIN.PERSON_PIN_ID
WHERE     (dbo.TERMS.TERM_END_DATE >= GETDATE()) AND (dbo.STC_STATUSES.POS = 1) AND (dbo.STC_STATUSES.STC_STATUS = 'A' OR
                      dbo.STC_STATUSES.STC_STATUS = 'N') AND (dbo.STUDENT_ACAD_CRED.STC_CRED_TYPE = 'IN' OR
                      dbo.STUDENT_ACAD_CRED.STC_CRED_TYPE = 'CE')
 
 
Script to create the function used in the above SQL view.  The function returns all cell phone numbers in a single column for each student(##########;##########;##########)
 
USE [production1805]
GO
/****** Object:  UserDefinedFunction [dbo].[U81_GET_REGROUP_CELLPHONE]    Script Date: 08/04/2011 20:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function dbo].[U81_GET_REGROUP_CELLPHONE)
Returns varchar(200)
As
BEGIN
 
DECLARE  @phone_number  varchar(20),
         @phone_numbers varchar(200)
 
set @phone_numbers= null
 
DECLARE PHONENUMBERS CURSOR FOR  
SELECT     REPLACE(PERSONAL_PHONE_NUMBER, '-', '')
FROM         dbo.PERPHONE
WHERE     (PERSONAL_PHONE_TYPE = 'CEL') AND (PERSONAL_PHONE_NUMBER IS NOT NULL) AND (ID = @ID)
 
/* OPEN the cursor and then FETCH the first row within the
   results set */
 
OPEN PHONENUMBERS
 
FETCH PHONENUMBERS INTO  @phone_number
 
/* If no rows within the results set (because the person
   has no cell phone) CLOSE the cursor and RETURN
   to the caller */
 
  IF (@@fetch_status <> 0)
    BEGIN
      CLOSE PHONENUMBERS
      DEALLOCATE PHONENUMBERS
      Return @phone_numbers
    END
 
/* Work through the cursor one row at a time. Check
   to make sure the last FETCH was successful, then process the
   row of results. After processing, FETCH the next row
   and repeat the process until the FETCH is unsuccessful-
   meaning there are no more rows to process. */
 
WHILE @@Fetch_Status = 0
 
   BEGIN
 
if @phone_numbers is not null
   begin
   set @phone_numbers = @phone_numbers + ';' + @phone_number
   end
 
if @phone_numbers is null
   begin
   set @phone_numbers = @phone_number
   end
 
/* FETCH the next row from the results set */
 
FETCH PHONENUMBERS INTO  @phone_number
 
   END
 
CLOSE PHONENUMBERS
 
DEALLOCATE PHONENUMBERS
 
Return @phone_numbers
 
END
 
 
Please let us know where you run this and what language it is in.  Microsoft SQL Server Management Studio and SQL

How do you run this script?  Automated. How?  SQL Job that runs every morning at 1am

Please paste any scripts or commands you use to automatically run this process that extracts data to a .csv.
 
Script used to create SQL Job.
 
USE [msdb]
GO
/****** Object:  Job [Create Regroup Accounts]    Script Date: 08/04/2011 20:29:32 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 08/04/2011 20:29:32 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Create Regroup Accounts',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=0,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'Create Regroup Accounts',
            @category_name=N'Database Maintenance',
            @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Select data from U81_REGROUP_ACCOUNTS_VIEW]    Script Date: 08/04/2011 20:29:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Select data from U81_REGROUP_ACCOUNTS_VIEW',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'master..xp_cmdshell ''bcp "SELECT dbo.U81_REGROUP_ACCOUNTS_VIEW.* FROM dbo.U81_REGROUP_ACCOUNTS_VIEW" queryout "d:\Output\Regroup\Accounts.csv" -c -T -t","''',
            @database_name=N'production1805',
            @output_file_name=N'D:\Output\Regroup\accounts_results.txt',
            @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Regroup Accounts',
            @enabled=1,
            @freq_type=4,
            @freq_interval=1,
            @freq_subday_type=1,
            @freq_subday_interval=0,
            @freq_relative_interval=0,
            @freq_recurrence_factor=0,
            @active_start_date=20110804,
            @active_end_date=99991231,
            @active_start_time=10000,
            @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Where do you put the file?  The file is placed on the d drive of the SQL server(d:\Output\Regroup\Accounts.csv) 

What process do you use to send the file to Regroup?  

Copy \\<sql server>\d$\path to csv file . (this copies the extraction from Colleague to a .csv file)

Copy\\sqlserver\d$\output\regroup\accounts.csv . (we then send that .csv -named accounts.csv to a folder we created called regroup) 

We then create a scheduled task in windows, set to a user that will then pick up the batch process at any time we schedule

Pscp –batch –pw <password> accounts.csv username@67.207.138.187:upload/

Did this answer your question?