Advice and answers from the Regroup Mass Notification Team

  1. Cancellations-form is the page where faculty choose the class they want to cancel. The drop down shows only classes they teach and is fed from the student_classes table.

<?php
include '../universal-checker.php';
session_start();
include '../connect.php';
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<title>Class Cancellations</title>
<link type="text/css" href="/_resources/css/jweb2.css" rel="stylesheet" media="screen">

<link href="css/jquery-ui-1.8.17.custom.css" rel="stylesheet" type="text/css" />

<script type="text/javascript" src="js/jquery-1.7.1.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.8.17.custom.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="js/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript">

$(function() {
$('#day').datepicker({ dateFormat: 'yy-mm-dd'});

});

</script>

 

</head>
<body>

<div id="wrapper">

<h1>Enter an Cancellation</h1>
<p style="font-size:3em;line-height:1em;">This form is under construction and does not function at the moment.</p>

<img src="message-icon.png" width="150" height="150" alt="text message icon" style="float:left;margin-right:2em;" /> <p>A text message will be sent to your student when this form is submitted. Consider the time of day when submitting a cancellation.</p>
<?php

$netlogon = $_SESSION['netlogon'];

//$netlogon= 'wshull';
$query = "select * from instructors where netlogon = '$netlogon' limit 1";
$result = mysqli_query ($db, $query);
$resultsRows = mysqli_num_rows($result);

if ($resultsRows == 0) {
echo '<p>Error. User not an instructor.</p>';
echo '</body></html>';
exit;
} else {
$resultsArray = mysqli_fetch_array($result);
$_SESSION['name'] = $resultsArray['firstname'].' '.$resultsArray['lastname'];

}
?>

<form id="FormName" action="cancellations-thanks.php" method="post" name="FormName">

<p><label for="daytime">Class:</label>
<select name="crn" id="crn">
<option value="#">Choose...</option>
<?php
$query = "select distinct crn, title, number, subj, begintime from student_classes where instructor ='$netlogon' and number not like '%W'";
$result = mysqli_query ($db, $query);
$resultsRows = mysqli_num_rows($result);
if ($resultsRows == 0) {
echo '<p>No classes found.</p>';
exit;
} else {

for($i = 0; $i < $resultsRows; $i++){
$resultsArray = mysqli_fetch_array($result);

$crn = $resultsArray['crn'];

$class = $resultsArray['subj'].' '.$resultsArray['number'].' '.$resultsArray['title'];

if ($resultsArray['begintime'] != ''){
$time = date( 'g:i a', strtotime( $resultsArray['begintime'] ) );
$class .= ' at '.$time;
}

echo '<option value="'.$crn.'">'.$class.'</option>';
echo "\n";
}
}
?>
></select>

 

<p><label for="day">Day:</label>
<input type="text" name="day" id="day" /></p>
<input type="submit" name="submitButtonName" value="Enter Cancellation" />
</form>

</div>

</body>
</html>

2. Cancellations-thanks is the page that does the heavy lifting. Beginning at line 142 is the code that actually sends the xml to regroup.

<?php
include '../universal-checker.php';
session_start();
include '../connect.php';
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<title>Class Cancellations</title>
<link type="text/css" href="/_resources/css/jweb2.css" rel="stylesheet" media="screen">

</head>
<body>

<div id="wrapper">

<h1>Enter an Cancellation</h1>
<?php

include '../connect.php';

if (isset($_POST['crn'])) { $crn = $_POST['crn']; }
if (isset($_POST['day'])) { $day = $_POST['day']; }

//echo '<p>class: '.$class.'</p>';
//echo '<p>day: '.$day.'</p>';

 

if (!$crn || !$day){
echo '<p>You must provide a class and day<br />
<a href="cancellations-form.php">Please go back and try again.</a></p>';

exit;
}

//Class information
$query = "select * from student_classes where crn ='$crn' ";
$result = mysqli_query ($db, $query);
$resultsRows = mysqli_num_rows($result);

echo '<p>'.$resultsRows.' total students in this class</p>';
if ($resultsRows == 0) {
echo '<p>Error</p>';
exit;
} else {

$resultsArray = mysqli_fetch_array($result);

$class = $resultsArray['subj'].' '.$resultsArray['number'].' '.$resultsArray['title'];

if ($resultsArray['begintime'] != ''){
$time = date( 'g:ia', strtotime( $resultsArray['begintime'] ) );
$class .= ' at '.$time;

}
}
$day = date("l, M jS", strtotime($day));
$message = 'Your '.$class.' on '.$day.' has been cancelled. DO NOT REPLY TO THIS MESSAGE.';


//Find students
include '../includes/salt.inc';

$query = "select
text_message_numbers.phone,
student_classes.netlogon
from student_classes, text_message_numbers
where
student_classes.crn ='$crn' and
student_classes.jnumber = text_message_numbers.jnumber
order by student_classes.netlogon
";






$result = mysqli_query ($db, $query);
$resultsRows = mysqli_num_rows($result);
if ($resultsRows == 0) {
echo '<p>No students with emergency numbers in this class.</p>';
exit;
} else {

$studentlist ='';
$xml = '<?xml version="1.0"?>';
$xml .= '<sms><body><![CDATA[';
$xml .= $message;
$xml .= ']]></body><phones>';

for($i = 0; $i < $resultsRows; $i++){
$resultsArray = mysqli_fetch_array($result);

//echo '<p>'.$resultsArray[1].', '.$resultsArray[2].' - '.$resultsArray[0].'</p>';
$studentlist .= '<li>'.$resultsArray[1].'</li>';
$xml .= '1'.$resultsArray[0].',';
}

$xml = rtrim($xml, ',');
$xml .= '</phones></sms>';

//echo '<textarea>'.$xml.'</textarea>';
}

//record to db
$day = date("Y-m-d", strtotime($day));

$addperson = "insert into cancellations (instructor, class, day)
values ('".$_SESSION['name']."', '".$class."', '".$day."')";
$result = mysqli_query ($db, $addperson);
$addrows = mysqli_affected_rows($db);

if($addrows == 1){
echo '<p class="green">Recorded.</p>';

}
else {
echo '<p class="red">Error. Nothing was recorded.</p>';
}

$ch = curl_init();

if (FALSE === $ch)
throw new Exception('failed to initialize');

curl_setopt($ch, CURLOPT_URL,"https://regroup.com/api/v1/smpp_alerts?api_key=RGbk7E1wrBTdv973hKP1dyj4iPs4PgbO");
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/xml',
'Connection: Keep-Alive'
));
curl_setopt($ch, CURLOPT_HEADER, 1);
curl_setopt($ch, CURLOPT_USERPWD, $username . ":" . $password);
curl_setopt($ch, CURLOPT_PROXY, '');
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_FRESH_CONNECT, TRUE);

curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_POSTFIELDS,$xml);

$request= curl_getinfo($ch);
echo '<div style="display:none;">';
var_dump($request);
$content = curl_exec($ch);
echo '</div>';

if (FALSE === $content){
throw new Exception(curl_error($ch), curl_errno($ch));
}else{
echo '<p>The message: <strong>"'.$message.'"</strong> was sent to these students: </strong></p>';
echo '<ol>'.$studentlist.'</ol>';

}


echo '<p class="action"><a href="cancellations-form.php">Add another class cancellation.</a></p>';

?>

 

</div>

</body>
</html>

3. The student_classes query file is the banner query that builds the student_classes table. This should work for any school using banner.

QUERY

select distinct GOBTPAC.GOBTPAC_EXTERNAL_USER,
A.GOBTPAC_EXTERNAL_USER as instructor,
SPRIDEN.SPRIDEN_ID,
SSBSECT.SSBSECT_SUBJ_CODE,
SSBSECT.SSBSECT_CRSE_NUMB,
SCBCRSE.SCBCRSE_TITLE,
SSRMEET.SSRMEET_BEGIN_TIME,
SSBSECT.SSBSECT_CRN,
SSBSECT.SSBSECT_INSM_CODE
from SATURN.SFRSTCR SFRSTCR,
SATURN.SSBSECT SSBSECT,
SATURN.SPRIDEN SPRIDEN,
GENERAL.GOBTPAC GOBTPAC, GOBTPAC A,
SATURN.SCBCRSE SCBCRSE, SIRASGN, SSRMEET
where ( SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
and SPRIDEN.SPRIDEN_PIDM = SFRSTCR.SFRSTCR_PIDM
and GOBTPAC.GOBTPAC_PIDM = SPRIDEN.SPRIDEN_PIDM
and SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
and SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
and SSBSECT.SSBSECT_CRN = SIRASGN.SIRASGN_CRN
and SSBSECT.SSBSECT_TERM_CODE = SIRASGN.SIRASGN_TERM_CODE
AND A.GOBTPAC_PIDM = SIRASGN.SIRASGN_PIDM
and SIRASGN.SIRASGN_PRIMARY_IND = 'Y'
)
and ( SFRSTCR.SFRSTCR_TERM_CODE = '$term'
and SSBSECT.SSBSECT_TERM_CODE = SSRMEET.SSRMEET_TERM_CODE
and SSBSECT.SSBSECT_CRN = SSRMEET.SSRMEET_CRN
and SCBCRSE_EFF_TERM = ( select Max(SCBCRSE_EFF_TERM)
from SCBCRSE
where SCBCRSE_EFF_TERM <= '$term'
and SSBSECT_SUBJ_CODE = SCBCRSE_SUBJ_CODE
AND SSBSECT_CRSE_NUMB = SCBCRSE_CRSE_NUMB))

SQL: See attached File

4. The text_message numbers is the wild card. Each school will probably have their own way of feeding this table.

See attached File

Did this answer your question?