Always Alert Sqls and Batch Files

 Problem Description : 

Always Alert Sqls 

 Resolution : 

Batch File for Emails to Students and Faculty

:: Runs SQL to create review records for student Early Alert emails

@echo off

call \\casper\c\sp

echo Running Early Alert emails

tnsping prod2.banner.usg.ga.us > c:\nul

if errorlevel 1 goto end 

::delete text files incase record set is empty

echo delete files

del "ealert\ealert.txt"

:: SQL to create output files and update suamail records

echo calling sqls

echo Early Alert

cd "ealert"

"c:\program files\oracmd\oracmd.exe" userid=baninst1/password@pebbles.gordonstate.edu:1025:PROD task=unload sqlfile="ealert_textfile.sql" delimiter="|" header=n datafile="ealert.txt" quit=y > "ealert.log"

sqlplus baninst1/password@prod2.banner.usg.ga.us @"ealert.sql" >> "ealert.log"

::batch file that emails the ealert

echo processing emails

call runemail_ealert.bat

cd ..

::end

SQL For Always Alert Emails

-insert the records into gurmail saying an email was sent

INSERT INTO gurmail

            (gurmail_pidm,

            gurmail_system_ind,

            gurmail_term_code,

            gurmail_letr_code,

            gurmail_module_code,

            gurmail_admin_identifier,

            gurmail_matl_code_mod,

            gurmail_date_init,

            gurmail_date_printed,

            gurmail_user,

            gurmail_wait_days,

            gurmail_pub_gen,

            gurmail_init_code,

            gurmail_orig_ind,

            gurmail_activity_date,

            gurmail_aidy_code,

            gurmail_cpln_code,

            gurmail_qty,

            gurmail_misc_vc2,

            gurmail_misc_date,

            gurmail_misc_num

            )        

select DISTINCT w.pidm,

            'S',

            w.term,

            'EMAIL',

            'S',

            1,

            'EALT',

            sysdate,

            sysdate,

            'CSD',

            NULL,

            'G',

            NULL,

            'U',

            sysdate,

            NULL,

            NULL,

            substr(w.crn, -3),

            NULL,

            NULL,

            NULL

from wealert w, stvgcmt, v_course vc

where w.gcmt_code = stvgcmt_code

        and VC.TERM_CODE = w.term

        and vc.term_code >= fg_current_term()

        and VC.CRN = w.crn

        and not exists(select 'x'

                from gurmail b

                where b.gurmail_term_code = '201602' 

                and b.gurmail_letr_code = 'EMAIL'

                and b.gurmail_matl_code_mod = 'EALT'

                and b.gurmail_qty = substr(w.crn, -3)

                and b.gurmail_pidm = w.pidm );

commit;            

exit;

Batch file that calls Template

::ssed -e "s/_student_/%~1/" -e "s/_id_/%~2/"-e "s/_reason_/%~5/" -e "s/_wanda_/%~6/"-e "s/_rowid_/%~7/"-e "s/_remarks_/%~8/" ealert.template > %3.msg%9

:: TMS - uncomment for loop below to replace special characters typed in comments field. All removes surrounding quotes.

:: for /f "tokens=*" %%i in ('echo "%~8" ^| ssed -e "s/[\/&]/\\&/g" -e "s/\x22//g"') do set comment=%%i

ssed -e "s/_student_/%~1/" -e "s/_id_/%~2/g" -e "s/_reason_/%~5/g" -e "s/_comment_/%~8/g" ealert.template > %3.msg%7

:: TMS - ssed below uses comment environment variable from the for loop above instead of passed argument

:: ssed -e "s/_student_/%~1/" -e "s/_id_/%~2/g" -e "s/_reason_/%~5/g" -e "s/_comment_/%comment%/g" ealert.template > %3.msg%7

::ssed -e "s/_student_/%~1/" -e "s/_id_/%~2/g" ealert.template > %4.msg%9

Text File Template

Student Name:              _student_

Student ID:               _id_

REASON:                _reason_

Instructor Comment:       _comment_

___________________________________________________________

Please reply to this email to avoid a Recreation HOLD on your academic record.

Your name has recently been referred to the Gordon Always Alert system as a student who may be experiencing difficulty in one or more of your classes.  Your reason for referral is included in the text above.  Please read the instructions below carefully:

• You are REQUIRED to attend a brief, mandatory intervention meeting designed to help you succeed in the referred class. You will meet with an assigned academic coach.

• IMPORTANT: To make an appointment, please REPLY to this email with the days and times you are available.  An academic coach will be assigned to you at that point.

• The Director of Student Success is copied on all referrals. If you are unable to schedule and attend an intervention within 10 days, a HOLD will be placed on your academic record and you will lose certain campus privileges.

Your referring professor and the entire Gordon State community want you to succeed and hope that this email will help you do so.

If you have any questions, please direct them to alwaysalert@gordonstate.edu.

Sql that creates the .dat file to be emailed.

--create the .dat file to be emailed

select  student.name(w.pidm,13) student

    ,student.id(w.pidm,'ND') id

        , student.email_address(w.pidm) stuemail

  --    ,w.crn

        ,student.email_address(VC.INST_PIDM)||',alwaysalert@gordonstate.edu,'||STUDENT.EMAIL_ADDRESS(STUDENT.ADVISOR_PIDM(w.pidm)) ccemail

        , decode(stvgcmt_description,'All of the Above','Poor Attendance, Grades, Participation', stvgcmt_description) reason

        ,'alwaysalert@gordonstate.edu' wanda

        ,rownum "rowid"

        ,nvl(replace(replace(remarks,'/','-'),'%','percent'),'NONE') "comment"

from wealert w, stvgcmt, v_course vc

where w.gcmt_code = stvgcmt_code

        and VC.TERM_CODE = w.term

        and vc.term_code >= fg_current_term()

        and VC.CRN = w.crn

        and vc.hours > 0

        and not exists(select 'x'

                from gurmail b

                where b.gurmail_term_code = w.term 

                and b.gurmail_letr_code = 'EMAIL'

                and b.gurmail_matl_code_mod = 'EALT'

              and b.gurmail_qty = substr(w.crn, -3)

                and b.gurmail_pidm = w.pidm )

 Revision Date : 1/24/2019