Freeswitch Outbound Dialing to Cell Phones

While trying to set up a couple Polycom phones with my Freeswitch server in a multi-tenant scenario, I was testing outbound dialing to my cell phone. No NAT traversal is involved, as I have static IP addresses set up on both my Polycom phone and the FS server. I set up the dialplan to parse the digits and use the bridge application to the outbound gateway. Leg A : Polycom dials cell phone 91XXXxxxxxxx Leg B: Freeswitch dials sofia/gateway/outbound-provider/1XXXxxxxxxx

Problem is, there was no ringback, and even though the outbound provider dialed the cellphone, there was no media exchanged, so no audio or DTMF was routed between the Polycom and the cellphone. After a few hours of frustrating, try this, try that, walk away and grab a soda, wait til the next day, come back and try again, I finally came across the following:

https://wiki.freeswitch.org/wiki/Misc._Dialplan_Tools_bridge

Two sections are applicable to this scenario:

No Media Mode

No media mode is an SDP Passthrough feature that permits two endpoints that can see each other (no funky NAT’s) to connect their media sessions directly while FreeSWITCH maintains control of the SIP signaling. This is useful if you have two end-points that need to use a codec that is currently not supported in FreeSWITCH (video) or if you are using FreeSWITCH in a high performance walled garden network and want to minimize the RTP handling FreeSWITCH is doing to maximize call traffic.

Before executing the bridge action you must set the “bypass_media” flag to true.

<action application="set" data="bypass_media=true"/>

I tried this approach first. Hizzah! Voice could now be heard, two-way, great! But wait, no DTMF tones were detected by FS (as apparent from the fs_cli console). Drat! On to the next possibility:

Calling multiple destinations

Note: by default when bridging, the first endpoint to provide media (as opposed to actually answering) will win, and the other endpoints will stop ringing. For internal endpoints, this usually doesn’t matter. However, in the case of cell phone providers, any custom music that plays for the caller while ringing counts as media. In some cases, the ringing sound itself is media. If your bridge command includes a cell phone number and your internal endpoints stop ringing as soon as the cell phone starts, you will need to enable the ‘ignore_early_media’ option:

<action application="set" data="ignore_early_media=true" />

I replaced the bypass_media that I had tried earlier, with the ignore_early_media=true et voila! Voice AND DTMF tones.

In retrospect, another thing I should have tried was dialing out to a different phone number…1-800-777-3456…which would probably have led me to the solution a bit faster.

Hopefully this saves you some time (and hair)

Happy Freeswitching!

Code Sample: PHP / jQuery Heartbeat System for ChatRoom

chatroom.php

<?php require_once('classes/check.class.php'); 
	protect("1,5");
?>
<?php require_once('header_chat.php'); ?>
	<script language="Javascript" type="text/javascript" src="assets/js/jquery.lwtCountdown-1.0.js"></script>
    <script src="assets/js/jquery.jcountdown.min.js" type="text/javascript"></script>
    <script src="assets/js/dateformat.js" type="text/javascript"></script>
	<link rel="Stylesheet" type="text/css" href="assets/css/dark_alt.css"></link>
<?php 
	require_once('functions.php'); 

	//enterChatRoom
	$waitingroomid = $_GET['r'];
	$chateventid = $_GET['c'];
	$userid = $_SESSION['jigowatt']['user_id'];

	//register chat room entry
	$result_json = enterChatRoom($chateventid, $userid);
	//should look like this: {"ticket_number":"X","result_msg":"message","room_name":"Chat-1","chat_channel":"1"}
	//echo print_r($result_json);
	$result_obj = json_decode($result_json);
	
	//echo print_r($result_obj);
	
	//$waiting_room_name = getWaitingRoomName($waitingroomid);
	//$waiting_room_chat_channel = getWaitingRoomChannel($waitingroomid);
	$result = $result_obj->{'result'};
	$room = $result_obj->{'chat_channel'};
	$room_name = $result_obj->{'room_name'};

	/*$chat_minute = 1;
	$chat_second = 30;
	
	$config = array(
		'targetDate' => array(	// Target countdown date
			'day'				=> 0,
			'month'				=> 0,
			'year'				=> 0,
			'hour'				=> 0,
			'minute'			=> $chat_minute,
			'second'			=> $chat_second
		)
	);
	*/
	
	require_once("process_date.php");


	if ($result == '-1'){
		//redirect away from this room back to the member.php
		 header( 'Location: https://www.thecelebsession.com/chat/scheduler/member.php' ) ;
		 return;
	}else{
		$celebrity_name = $result_obj->{'celebrity_name'};
	}//result == -1
	
	//$room = $waitingroomid;

if($room > 1){
	$base_url = 'https://thecelebsession.com/chat/'.$room;
}else{
	$base_url='https://thecelebsession.com/chat';
}

?>

<script src="https://www.thecelebsession.com/chat/js/swfobject.js" type="text/javascript"></script>
<style>
span.time{
color:#c21017;
text-align: center;
}

span.time .cd-time{
display:inline;
color:#222222;
/*font-size:1.2em;*//*0.8em;*/
}

</style>
<!--<script type="text/javascript" src="assets/js/base.js"></script>
<link rel="stylesheet" type="text/css" href="assets/css/base.css" />-->
<!--<div class="navbar span12" style="position:fixed;margin:0 auto;top:50px;z-index:11000;">
    <div class="navbar navbar-inner">-->
        <div class="span6" style="position:fixed;top:55px;z-index:500;">
            <div class="container">
            
            <table class="table table-bordered table-condensed" style="background-color:#FFF;">
            	<tr>
                <td>Time Remaining:<br/><span id="time" class="time"></span></td>
                <td>Chatting With:<br/><span style="font-size:14px; font-weight:bolder; color:#00F;text-align:center;" id="celebrity_name" ><?php echo $celebrity_name;?></span></td>
                <td>
                Heartbeat:<br/>
                <span id="heartbeat_msg" align="center"><span class="label label-warning">not connected</span></span>
                </td>
                <td>
                Room:<br/>
                <span class="label label-info"><?php echo $room_name; ?></span>
                </td>
                </tr>
                </table>
            </div>
        </div>
        <div id="chat_started" style="display:none;" data-chat_started="0">0</div>
<!--    </div>
</div>
-->

<!--<script type="text/javascript" src="assets/js/base.js"></script>
<link rel="stylesheet" type="text/css" href="assets/css/base.css" />-->
<div class="span12">
<div class="row">
	<div class="span12">
        <script type="text/javascript">
		var flashvars = {
		  xmlPath: "<?php echo $base_url; ?>/settings.php"
		};
		var params = {
			menu: "false",
			scale: "noScale",
			allowFullscreen: "true",
			allowScriptAccess: "never",
			bgcolor: "#0A1215"
		};
		var attributes = {
			id:"Scriptolution"
		};
		
		swfobject.embedSWF("<?php echo $base_url;?>/Scriptolution.swf", "altContent", "1200", "875", "10.0.0", "<?php echo $base_url; ?>/expressInstall.swf", flashvars, params, attributes);
		
	</script>
    
    	<br><br>
	<object data="<?php echo $base_url;?>/Scriptolution.swf" id="Scriptolution" type="application/x-shockwave-flash" height="875" width="1200"><param value="false" name="menu"><param value="noScale" name="scale"><param value="true" name="allowFullscreen"><param value="never" name="allowScriptAccess"><param value="#0A1215" name="bgcolor"><param value="xmlPath=<?php echo $base_url;?>/settings.php" name="flashvars"></object>
        <!--<div id="chat_placeholder" style="background-color:#0A1215;width:1150px;height:866px;">
        	<div class="alert-block">chat area placeholder</div>
        </div>-->
    </div>
  </div><!--row-->
  <div class="row">
    <div class="span2">
        <ul class="thumbnails">
            <li class="span2" style="display:inline;list-style-type: none;padding:10px;">
                <div class="thumbnail">
                	<div style="text-align:center;">
                        <img src="assets/img/george_michael_ad.jpg" />
                    </div>
                </div>
            </li>
        </ul>		
	</div>
</div>    

<div class="modal" id="helpModal" tabindex="-1" role="dialog" aria-labelledby="splashlabel" aria-hidden="true" style="display:none;"><!--position:fixed;left:30%;top:30%;z-index:50000;-->
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">x</button>
<h3 id="splashlabel">TCS Celebrity Chat Room</h3>
</div>
<div class="modal-body" >
<h4>Tips and Tricks</h4>
<ul>
<li>Click the ALLOW button to allow - thecelebsession to activate your webcam.</li>
<li>Place/hover your mouse over the black video screen and select your webcam if not done so
automatically.</li>
<li>Reduce the audio level located on your same video screen to communicate clearly.Reduce it to the example shown below<br/>
<span class=" span3 well">Audio:<br/>____l____________	</span></li>
</ul>
</div>
<div class="modal-footer">
<button class="btn btn-primary" data-dismiss="modal" aria-hidden="true">Close</button>
</div>
</div>

<script src="assets/js/bootstrap-modal.js"></script>
<script>
var timer_paused = 0;

$(document).ready(function(){
	//show splash on page load
	$('#helpModal').modal('show');
	$("body").css("background-color","#999");
	
	$("#time").countdown({
		htmlTemplate: " %i <span class=\"cd-time\">mins</span> %s <span class=\"cd-time\">sec</span>",//"%h <span class=\"cd-time\">hours</span> %i <span class=\"cd-time\">mins</span> %s <span class=\"cd-time\">sec</span>",
		date: "09/09/2001 00:00:00",
		minsOnly: false,
		leadingZero: true
	});
			   

});//doc ready

(function heartbeat() 
	{ setTimeout(function(){ 
				//$('#current_auctions_refreshing').show();
				//post_array = "action=getCurrentAuctions";
			    $.ajax({ 
					 url: "chat_room_interface.php", 
					 type: "post",
					 data: { action: 'heartbeat',
					 		 chat_event_id: <?php echo $chateventid; ?>,
							 user_id: <?php echo $userid; ?>
							 }, 
							 //page_to_display: $('#page_to_display').val(), 
							 //limit_records: $('#limit_records').val(), 
							 //limit_start: $('#limit_start').val() },
					 success: function(data) { 
							var data_obj = $.parseJSON(data);
							if (data_obj.result_code > 0){
								//success
								if(data_obj.connect_msg == 'connected'){
									display_msg = '<span class="label label-success">connected!</span>';
								}else if(data_obj.connect_msg == 'celebrity disconnected'){
									display_msg = '<span class="label label-important">' + data_obj.connect_msg + '</span>';
								}else{
									
									if(data_obj.last_heartbeat > 4){
										//disconnect
										$("#time").countdown("pause");
										timer_paused = 1;
									}
									display_msg = '<span class="label label-info">' + data_obj.connect_msg + '</span>';	
								}
								if(data_obj.start_chat == 1 && $('#chat_started').attr('data-chat_started') == 0){
									$('#chat_started').attr('data-chat_started', 1);

									var minutes = (data_obj.min_1 + data_obj.min_2) * 1;
									var seconds = (data_obj.sec_1 + data_obj.sec_2) * 1;
									var diff = (minutes * 60) + seconds;
					
									var rightNow = new Date();
									var futureTime = new Date(rightNow.getTime() + diff*1000);
				
									var countdownEnds = futureTime.format("mm/dd/yyyy HH:MM:ss");
									
									$("#time").countdown({
										htmlTemplate: " %i <span class=\"cd-time\">mins</span> %s <span class=\"cd-time\">sec</span>",//"%h <span class=\"cd-time\">hours</span> %i <span class=\"cd-time\">mins</span> %s <span class=\"cd-time\">sec</span>",
										date: countdownEnds,//"january 10, 2012 12:35",
										onComplete: function( event ) {
											window.location.href= 'thankyou.php';
										},
										minsOnly: false,
										leadingZero: true
									});
								}else if(data_obj.start_chat == 2 ){
									//restart
									$('#chat_started').attr('data-chat_started', 1);
									timer_paused = 0;
									
									var minutes = (data_obj.min_1 + data_obj.min_2) * 1;
									var seconds = (data_obj.sec_1 + data_obj.sec_2) * 1;
									var diff = (minutes * 60) + seconds;
					
									var rightNow = new Date();
									var futureTime = new Date(rightNow.getTime() + diff*1000);
				
									var countdownEnds = futureTime.format("mm/dd/yyyy HH:MM:ss");
									
									$("#time").countdown({
										htmlTemplate: " %i <span class=\"cd-time\">mins</span> %s <span class=\"cd-time\">sec</span>",//"%h <span class=\"cd-time\">hours</span> %i <span class=\"cd-time\">mins</span> %s <span class=\"cd-time\">sec</span>",
										date: countdownEnds,//"january 10, 2012 12:35",
										onComplete: function( event ) {
											window.location.href= 'thankyou.php';
										},
										minsOnly: false,
										leadingZero: true
									});
									
								}else if(data_obj.start_chat == -2){
									//celebrity clicked end chat
									$('#time').stopCountDown();
									window.location.href= 'thankyou.php';
								}else if(data_obj.start_chat == -3){
									//celebrity clicked ban user!
									$('#time').stopCountDown();
									window.location.href= 'logout.php?e=2';
								}//if start_chat == 1
								
							}else{
								if(data_obj.result_code == -2){
									//celebrity disconnect > 30 seconds, exit to the member screen and post an error	
									window.location.href= 'member.php?e=5';
								}else if(data_obj.result_code == -3){
									//celebrity temporary disconnect
									$("#time").countdown("pause");
									timer_paused = 1;
								}
								//failure
								display_msg = '<span class="label label-error">' + data_obj.result_code + ' : ' + data_obj.result_msg + ' last beat:' + data_obj.last_heartbeat + 'sec. ' + data_obj.connect_msg + '</span>';
							}
							$('#heartbeat_msg').html(display_msg);
					 }, 
					 //dataType: "json", 
					 complete: heartbeat 
				}); //ajax
		}, //setTimeout inline function
		2500); //timeout in ms
})();//function heartbeat

</script>
<?php #include_once('footer.php'); ?>

chat_room_interface.php


<?php

include_once('connect.php');

$dbh = connectDB();

$report = $_POST['action'];
if($report == ""){
	$report = $_GET['action'];
}

$paged_report = 0;
$total_records = 0;

switch($report){
	case "heartbeat":
		heartbeat($dbh);
		break;
	default:
		echo("<div class='alert alert-error'>Invalid action ".$report." specified</div>");
		break;
}//switch action
	
//clean up and close the connection
$dbh=null;

function getPostVar($key){
	$post_var = '';
	if (isset($_POST[$key])){
		$post_var = $_POST[$key]; 
	} else { 
		if (isset($_GET[$key])){
			$post_var = $_GET[$key]; 
		} else {
			$post_var = 'NULL';
		}
	}
	return $post_var;
}
	
function heartbeat($dbh){
	$chat_event_id = getPostVar('chat_event_id');
	$user_id = getPostVar('user_id');
	
	$first_sec_digit = '0';
	$first_min_digit = '0';
	$second_sec_digit = '0';
	$second_min_digit = '0';
	$start_chat = 0;
	

	$sql = "call sendHeartbeat(:chat_event_id, :user_id);";
	$params = array(':chat_event_id'=>$chat_event_id, ':user_id'=>$user_id);
	$stmt = $dbh->prepare($sql);
	$stmt->execute($params);
	
	if( $stmt->rowCount() < 1 ) {
		$result = '-1';
		$result_msg = 'error with sendHeartbeat';
		$last_heartbeat = '-1';
		$connect_msg = 'not connected';
	}else{
		$row = $stmt->fetch(PDO::FETCH_ASSOC);
		$result = $row['result'];
		$result_msg = $row['result_msg'];
		$last_heartbeat = $row['last_heartbeat'];
		$start_chat = $row['start_chat'];
		if($start_chat > 0){
			$chat_duration = explode(':',$row['duration']);
	
			//$chat_hour = $chat_duration[0];
			$chat_minute = $chat_duration[0];
			$chat_second = $chat_duration[1];

			//add 5 seconds to allow for sync issues
			$chat_second = $chat_second + 5;

			//print_r($chat_second);

			if($chat_minute <= 9){
				$first_min_digit = "0";
				$second_min_digit = $chat_minute;
			}else{
				$first_min_digit = substr($chat_minute,0,1);
				$second_min_digit = substr($chat_minute,1,1);				
			}
			if($chat_second <= 9){
				$first_sec_digit= "0";
				$second_sec_digit = $chat_second;
			}else{
				$first_sec_digit = substr($chat_second,0,1);
				$second_sec_digit = substr($chat_second,1,1);
			}
			//$first_min_digit = substr($chat_minute,0,1);
			//$first_sec_digit = substr($chat_second,0,1);
			//$second_min_digit = substr($chat_minute,1,1);
			//$second_sec_digit = substr($chat_second,1,1);
		}//$start_chat > 0
		
		if($last_heartbeat > 4){ //6
			$connect_msg = 'disconnected for '.$last_heartbeat.' sec.';
			
		}elseif($last_heartbeat >= 0 && $last_heartbeat <=3){ //0-5
			$connect_msg = 'connected';
		}else{
			$connect_msg = 'not connected';
		}//last_heartbeat > 6
		
	}//rowcount

	$stmt->closeCursor();
	$row=null;
	$stmt=null;
	
	echo '{"result_code":"'.$result.'","result_msg":"'.$result_msg.'","last_heartbeat":"'.$last_heartbeat.'","connect_msg":"'.$connect_msg.'","start_chat":"'.$start_chat.'","min_1":"'.$first_min_digit.'","min_2":"'.$second_min_digit.'","sec_1":"'.$first_sec_digit.'","sec_2":"'.$second_sec_digit.'"}';
	
	return;
}//heartbeat

?>

Code Sample: XSL


<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:param name="method" />
	<!--<xsl:output method="xml" encoding="iso-8859-1" indent="no"/>-->
    <xsl:output method="html" indent="no"/>
	
    <xsl:key name="npa-key" match="npanxx" use="@npa"/>
    <xsl:key name="nxx-key" match="npanxx" use="@nxx"/>
     
    <xsl:variable name="npanxx_doc" select="document('npanxxratecenter.xml')/ratecenters"/>

<!--	<xsl:value-of select="key('StateKey', key('CustomerKey', @custid)/@stateid )"/> -->

	<xsl:template match="/">
		<xsl:param name="numbers" />
		<xsl:param name="npanxx" />
		<xsl:param name="results" />
		<xsl:choose>
			<xsl:when test="$method='service.getAvailNPA'">
				<xsl:call-template name="service.getAvailNPA">
					<xsl:with-param name="numbers" select="/rsp/numbers"></xsl:with-param>
				</xsl:call-template>
			</xsl:when>
			<xsl:when test="$method='service.getAvailNPANXX'">
				<xsl:call-template name="service.getAvailNPANXX">
					<xsl:with-param name="numbers" select="/rsp/numbers"></xsl:with-param>
				</xsl:call-template>
			</xsl:when>
			<xsl:when test="$method='service.checkNPA'">
				<xsl:call-template name="service.checkNPA">
					<xsl:with-param name="numbers" select="/rsp/numbers"></xsl:with-param>
				</xsl:call-template>
            </xsl:when>
			<xsl:when test="$method='service.checkRateCenter'">
            	<xsl:call-template name="service.checkRateCenter" >
                    <xsl:with-param name="numbers" select="/rsp/numbers"></xsl:with-param>
                </xsl:call-template>
            </xsl:when>
			<xsl:when test="$method='service.getRateCenters'"><xsl:call-template name="service.getRateCenters" /></xsl:when>
			<xsl:when test="$method='service.lookupNPANXX'">
				<xsl:call-template name="service.lookupNPANXX">
					<xsl:with-param name="npanxx" select="/rsp/npanxx"></xsl:with-param>
                    <!--<rsp stat="ok" version="1.0">
						<npanxx npa="212" nxx="555" state="NY" city="New York" county="New York" ratecenter="NEW YORK" />
					</rsp>-->
				</xsl:call-template>
			</xsl:when>
			<xsl:when test="$method='service.reserveDID'"><xsl:call-template name="service.reserveDID" /></xsl:when>
			<xsl:when test="$method='service.orderDID'">
            	<xsl:call-template name="service.orderDID">
                	<xsl:with-param name="results" select="/rsp"></xsl:with-param>
						<!--<rsp stat="ok" version="1.0"><did>2125551212</did></rsp> -->
                        <!--<rsp stat="fail" version="1.0"><error msg="Detailed error message" /></rsp>-->
                </xsl:call-template>
            </xsl:when>
			<xsl:when test="$method='service.setRouting'">
            	<xsl:call-template name="service.setRouting">
                    <xsl:with-param name="results" select="/rsp"></xsl:with-param>
                    	<!--<rsp stat="ok" version="1.0"><did routing="cust_username_123_123_123_123"></did></rsp>-->
                        <!--<rsp stat="fail" version="1.0"><error msg="Detailed error message" /></rsp>-->
				</xsl:call-template>
            </xsl:when>
			<xsl:when test="$method='service.removeDID'"><xsl:call-template name="service.removeDID" /></xsl:when>
			<xsl:when test="$method='term.getRate'">
				<xsl:call-template name="term.getRate">
					<xsl:with-param name="rate" select="/rsp/rate"></xsl:with-param>
                    <!--<rsp stat="ok" version="1.0">
                    		<rate npa="727" nxx="569" initial_duration="6" bill_duration="6" intra_state="0.0630" inter_state="0.0220" blend="0.0302"/>
                    </rsp>-->
				</xsl:call-template>
			</xsl:when>
		</xsl:choose>
	</xsl:template>

	<xsl:template name="service.getAvailNPA">
		<xsl:param name="numbers"/>

		<tbody>
		<xsl:for-each select="$numbers/npa">
			<xsl:element name="tr">
				<xsl:attribute name="class">NPA_row</xsl:attribute>
				<xsl:attribute name="id">
					<xsl:value-of select="." />
				</xsl:attribute>
				<td><div class="NPA_cell"><xsl:value-of select="." /></div></td>
				<td>
					<div class="tier1_cell">
					<xsl:if test="@tier1='true'">
						Tier 1
					</xsl:if>
					</div>
				</td>
				<td>
					<div class="tier2_cell">
					<xsl:if test="@tier2='true'">
						Tier 2
					</xsl:if>
					</div>
				</td>
			</xsl:element>
		</xsl:for-each>
		</tbody>
	</xsl:template>	

	<xsl:template name="service.getAvailNPANXX">
		<xsl:param name="numbers"/>

		<tbody>
		<xsl:for-each select="$numbers/npanxx">
			<xsl:element name="tr">
				<xsl:attribute name="class">NXX_row NPA_<xsl:value-of select="@npa" /></xsl:attribute>
				<xsl:attribute name="id">
					<xsl:value-of select="@nxx" />
				</xsl:attribute>
				<xsl:attribute name="style">
					display: none;
				</xsl:attribute>
				<td><div class="NXX_cell"><xsl:value-of select="@nxx" /></div></td>
				<td>
					<div class="tier1_cell">
					<xsl:if test="@tier1='true'">
						Tier 1
					</xsl:if>
					</div>
				</td>
				<td>
					<div class="tier2_cell">
					<xsl:if test="@tier2='true'">
						Tier 2
					</xsl:if>
					</div>
				</td>
			</xsl:element>
		</xsl:for-each>
		</tbody>
	</xsl:template>	

<!--<rsp stat="ok" version="1.0">
	<numbers npa="212" available="2">
		<number type="tier1">2125551212</number>
		<number type="tier1">2125551313</number>
		<number type="tier2">212556</number>
	</numbers>
</rsp>-->
 	<xsl:key name="uniquenxx" match="number[@type='tier1']" use="substring(text(), 1, 6)"/><!--key is based on the first 6 characters of the number[npa+nxx]-->
 	<xsl:key name="tier" match="number" use="substring(text(), 1, 6)"/><!--key is based on the first 6 characters of the number[npa+nxx]-->
 	<xsl:template name="service.checkNPA">
		<xsl:param name="numbers"/>
			<xsl:variable name="tier1count" select="count($numbers/number[@type='tier1'])"/><!--get the count of the number of nodes which have type='tier1' attribute-->
            <br/>
            <xsl:choose>
            	<xsl:when test="$tier1count > 0" ><!--only enter this section of the xsl when we know we have at least 1 tier1 number-->
	            <!--Tier 1 Numbers: <xsl:value-of select="$tier1count"/><br/>-->
    	        <div class="alert alert-info">Good news!  We have Transfer Numbers available for instant activation in area code <xsl:value-of select="$numbers/@npa" />.  Select an exchange (NPA-NXX) below.</div> 
            <br/><br/>
            <h4>Available Transfer Numbers</h4>
            <table cellspacing="0" cellpadding="3" border="0" class="table table-bordered table-condensed table-striped" id="available_dids">
				<thead>
		            <tr>
                    	<td >NPA-NXX</td>
                        <td >State</td>
                        <td >City</td>
                        <td >Ratecenter</td>
                    </tr>
                </thead>
                <tbody>
                    <xsl:for-each select="$numbers/number[@type='tier1']"><!--loop through the number nodes-->
                      <!--<xsl:sort select="@type"/>--><!--sort by tier attribute-->
                      <xsl:sort select="."/><!--alphanumberic sort by text value of node-->
                      <xsl:variable name="npanxx" select="substring(.,1,6)"/><!--store the first 6 characters of the text in the currently selected node into variable npanxx-->
                      <xsl:if test="generate-id(.) = generate-id(key('uniquenxx',$npanxx)[1])"><!--check to see if this is the first record for an npanxx group, so we only output 1 201-555 record if we have 2015551212 2015551234-->
                         <!--<xsl:if test="@type='tier1'">--><!--we only want tier1 numbers due to pricing constraints-->
                            <xsl:variable name="npa_val" select="substring(.,1,3)"/><!--npa is the first 3 digits of the node text-->
                            <xsl:variable name="nxx_val" select="substring(.,4,3)"/><!--nxx is the second 3 digits of the node text-->
                            <xsl:element name="tr">
                                <xsl:attribute name="class">
                                    <xsl:text>npanxxrow</xsl:text>
                                </xsl:attribute>
                            	<xsl:attribute name="id">
                                    <xsl:text>tr_</xsl:text>
                                    <xsl:value-of select="$npa_val"/>
                                    <xsl:text>_</xsl:text>
                                    <xsl:value-of select="$nxx_val"/>
                                </xsl:attribute>
                                <xsl:for-each select="$npanxx_doc"> <!--switch doc context to the lookup reference-->
                                    <xsl:for-each select="key('npa-key',$npa_val)">
                                        <xsl:if test="./@nxx=$nxx_val">
                                            <td><a>
                                                    <xsl:attribute name="class">
                                                        <xsl:text>verifyorder</xsl:text>
                                                    </xsl:attribute>
                                                    <xsl:attribute name="href">
                                                        <xsl:text>#</xsl:text>
                                                    </xsl:attribute>
                                                    <xsl:attribute name="id">
                                                        <xsl:text>rc_</xsl:text>
                                                        <xsl:value-of select="$npa_val"/>
                                                        <xsl:text>_</xsl:text>
                                                        <xsl:value-of select="$nxx_val"/>
                                                        <xsl:text>_</xsl:text>
                                                        <xsl:value-of select="./@ratecenter"/>
                                                     </xsl:attribute>
                                                     <xsl:value-of select="$npa_val"/><xsl:text>-</xsl:text><xsl:value-of select="$nxx_val"/>
                                                 </a>
                                            </td>
                                            <td><xsl:if test="./@state!='unavailable'"><xsl:value-of select="./@state" /></xsl:if></td><!--only print out the value if the node text is not 'unavailable'-->
                                            <td><xsl:if test="./@city!='unavailable'"><xsl:value-of select="./@city" /></xsl:if></td>
                                            <td><xsl:if test="./@ratecenter!='unavailable'"><xsl:value-of select="./@ratecenter"/></xsl:if></td>
                                        </xsl:if>
                                        <!--<xsl:value-of select="./@npa"/>-<xsl:value-of select="./@nxx"/> ratecenter=<xsl:value-of select="./@ratecenter"/> location=<xsl:value-of select="./@city"/>, <xsl:value-of select="./@state"/>-->
                                    </xsl:for-each>
                                </xsl:for-each>
                            </xsl:element><!--end of tr-->
                          <!--</xsl:if>--><!--if @type=tier1-->
                        </xsl:if><!--if first record in a group of like npanxx's-->
                    </xsl:for-each><!--foreach $numbers/number-->
				</tbody>
            </table>
            </xsl:when>
            <xsl:otherwise>
            	<div class="alert alert-error">We have no Transfer Numbers available for area code <xsl:value-of select="$numbers/@npa" /></div><br/> 
            </xsl:otherwise>
         </xsl:choose>
	</xsl:template>	

     <xsl:template match="numbers">
          <xsl:param name="curr-number-node"/>
          <xsl:value-of select="substring($curr-number-node,1,3)"/>
          <!--<xsl:value-of select="key('nxx-key', key('npa-key', substring($curr-number-node,1,3))/substring($curr-number-node,4,3))/@ratecenter"/>-->
          <xsl:value-of select="key('npa-key', substring($curr-number-node,1,3))/@ratecenter"/>
     </xsl:template>

	<xsl:template name="service.checkRateCenter">
		<xsl:param name="numbers"/>

		<tbody>
		<xsl:for-each select="$numbers/number">
			<xsl:element name="tr">
				<td><div class="DID_cell"><xsl:value-of select="." /></div></td>
			</xsl:element>
		</xsl:for-each>
		</tbody>
    </xsl:template>	

	<xsl:template name="service.getRateCenters">
		I made it here...
	</xsl:template>	

	<xsl:template name="service.lookupNPANXX">
		<xsl:param name="npanxx"/>

		<dl class="dl-horizontal">
			<dt>State</dt>
			<dd id="state_dd">
				<xsl:choose>
					<xsl:when test="$npanxx/@state!=''">
						<xsl:value-of select="$npanxx/@state" />
					</xsl:when>
					<xsl:otherwise>
						<span style="font-style: italic; color: #777;">No State information returned</span>
					</xsl:otherwise>
				</xsl:choose>
			</dd>
			<dt>City</dt>
			<dd id="city_dd">
				<xsl:choose>
					<xsl:when test="$npanxx/@city!=''">
						<xsl:value-of select="$npanxx/@city" />
					</xsl:when>
					<xsl:otherwise>
						<span style="font-style: italic; color: #777;">No City information returned</span>
					</xsl:otherwise>
				</xsl:choose>
			</dd>
			<dt>Country</dt>
			<dd id="country_dd">
				<xsl:choose>
					<xsl:when test="$npanxx/@country!=''">
						<xsl:value-of select="$npanxx/@country" />
					</xsl:when>
					<xsl:otherwise>
						<span style="font-style: italic; color: #777;">No Country information returned</span>
					</xsl:otherwise>
				</xsl:choose>
			</dd>
			<dt>Rate Center</dt>
			<dd id="ratecenter_dd">
				<xsl:choose>
					<xsl:when test="$npanxx/@ratecenter!=''">
						<xsl:value-of select="$npanxx/@ratecenter" />
					</xsl:when>
					<xsl:otherwise>
						<span style="font-style: italic; color: #777;">No Rate Center information returned</span>
					</xsl:otherwise>
				</xsl:choose>
			</dd>
		</dl>
	</xsl:template>	

	<xsl:template name="service.reserveDID">
		I made it here...
	</xsl:template>	

	<xsl:template name="service.orderDID">
		<xsl:param name="results"/>
        <xsl:if test="$results/@stat='ok'">
            <xsl:text>{ "result":"success","did":"</xsl:text>
            <xsl:value-of select="$results/did" />
            <xsl:text>"}</xsl:text>
        </xsl:if>
        <xsl:if test="$results/@stat='fail'">
            <xsl:text>{ "result":"error","msg":"</xsl:text>
            <xsl:value-of select="$results/error/@msg" />
            <xsl:text>"}</xsl:text>
        </xsl:if>
	</xsl:template>	

	<xsl:template name="service.setRouting">
		<xsl:param name="results"/>
        <xsl:if test="$results/@stat='ok'">
            <xsl:text>{ "result":"success","routing":"</xsl:text>
            <xsl:value-of select="$results/did/@routing" />
            <xsl:text>"}</xsl:text>
        </xsl:if>
        <xsl:if test="$results/@stat='fail'">
            <xsl:text>{ "result":"error","msg":"</xsl:text>
            <xsl:value-of select="$results/error/@msg" />
            <xsl:text>"}</xsl:text>
        </xsl:if>
	</xsl:template>	

	<xsl:template name="service.removeDID">
		I made it here...
	</xsl:template>	
    
    <xsl:template name="term.getRate">
		<xsl:param name="rate"/>

            <table cellspacing="0" cellpadding="3" border="0" class="table table-bordered table-condensed table-striped" id="available_dids">
				<thead>
		            <tr>
                    	<td >NPA</td>
                        <td >NXX</td>
                        <td >Initial Duration</td>
                        <td >Bill Duration</td>
                        <td >Intra-State Rate</td>
                        <td >Inter-State Rate</td>
                        <td >Blended Rate</td>
                    </tr>
                </thead>
                <tbody>
		<xsl:for-each select="$rate">
			<xsl:element name="tr">
				<xsl:element name="td"><xsl:value-of select="./@npa" /></xsl:element>
				<xsl:element name="td"><xsl:value-of select="./@nxx" /></xsl:element>
				<xsl:element name="td"><xsl:value-of select="./@initial_duration" /></xsl:element>
				<xsl:element name="td"><xsl:value-of select="./@bill_duration" /></xsl:element>
				<xsl:element name="td"><xsl:value-of select="./@intra_state" /></xsl:element>
				<xsl:element name="td"><xsl:value-of select="./@inter_state" /></xsl:element>
				<xsl:element name="td"><xsl:value-of select="./@blend" /></xsl:element>
			</xsl:element>
		</xsl:for-each>
		</tbody>
      </table>
    </xsl:template>	

</xsl:stylesheet>

Code Sample: Lua

-- adtransfer_promptrecorder.lua
-- Written by Jeffrey W. Gray
-- owned by core3software, inc.
-- 2012.05.19

-- ************************************************************************
-- ****               BEGIN FUNCTION DEFINITIONS                        ***
-- ************************************************************************

-- Global Variables
default_prompt_path = "/usr/local/freeswitch/sounds/app/ad_transfer/default/en/"
session:setVariable("default_prompt_path",default_prompt_path)


-- ************************************************************************
-- ****                         MAIN MENU                               ***
-- ************************************************************************

function mainMenu()
	--Main Menu

	freeswitch.consoleLog("INFO","************* ENTER MAIN MENU ***************\n")

	local exit_menu = false

	while(session:ready() == true) and (exit_menu == false) do

		--Main Menu
		prompt = default_prompt_path.."104.wav"
		session:streamFile(prompt)


		menu_prompts = ""

		--choose which options to make available based on database records
		if (session:getVariable("custom_greeting") == "1") then
			--Press 1 to manage your Greeting Prompt
			menu_item_greeting_prompt = "105.wav"
			menu_prompts = menu_item_greeting_prompt
		else
			
		end
		if (session:getVariable("whisper_announce") == "1") then
			--Press 2 to manage your Whisper Prompt
			menu_item_whisper_prompt = "106.wav"
			
			if (menu_prompts == "") then
				menu_prompts = menu_item_whisper_prompt
			else
				menu_prompts = menu_prompts..":"..menu_item_whisper_prompt
			end
		end
		if (session:getVariable("collect_quickstart_code") == "1") then
			--Press 3 to manage your Quick Start Code Prompt
			menu_item_quickstart_prompt = "107.wav"

			if (menu_prompts == "") then
				menu_prompts = menu_item_quickstart_prompt
			else
				menu_prompts = menu_prompts..":"..menu_item_quickstart_prompt
			end
		end
		
		--Press 4 to manage your Call Transfer Prompt
		menu_item_calltransfer_prompt = "108.wav"

		if (menu_prompts == "") then
			menu_prompts = menu_item_calltransfer_prompt
		else
			menu_prompts = menu_prompts..":"..menu_item_calltransfer_prompt
		end
		
		--digits = session:playAndGetDigits (
		--          min_digits, max_digits, max_attempts, timeout, terminators,
		--          prompt_audio_files, input_error_audio_files,
		--          digit_regex, variable_name, digit_timeout,
		--          transfer_on_failure)
		
		freeswitch.consoleLog("INFO","********** menu_prompts : "..menu_prompts.." **********\n")

		local invalid_entry = default_prompt_path .. "125.wav" -- That was an invalid entry. Please try again.
		
		menu_choice = session:playAndGetDigits(1,1,3, 4000, "", "phrase:adtransfer_recorder_main_menu:"..menu_prompts, invalid_entry,"\\d|\\*")

		if (menu_choice == "1") then

			chosen_prompt = "greeting"
			result = managePromptMenu(chosen_prompt)

		elseif (menu_choice == "2") then

			chosen_prompt = "whisper"
			result = managePromptMenu(chosen_prompt)
			exit_menu = true

		elseif (menu_choice == "3") then

			chosen_prompt = "quickstart"
			result = managePromptMenu(chosen_prompt)

		elseif (menu_choice == "4") then

			chosen_prompt = "calltransfer"
			result = managePromptMenu(chosen_prompt)

		elseif (menu_choice == "*") then
			--exit menu on [star]
			result = -1

		end --if menu_choice

		if (result == -1) then
			exit_menu = true
			break	
		else
			--return to main menu
			exit_menu = false
		end

		freeswitch.consoleLog("INFO","********** menu_choice : "..menu_choice.." **********\n")
		
	end -- while(session:ready() and exit_menu == false

	freeswitch.consoleLog("INFO","************* EXIT MAIN MENU ***************\n")

	return 0

end -- function mainMenu

-- ************************************************************************
-- ****                MANAGE PROMPT MENU                               ***
-- ************************************************************************

function managePromptMenu(prompt_name)

	freeswitch.consoleLog("INFO","************managePromptMenu  prompt_name = "..prompt_name.." *****************\n")

	local result = -1
	local continue = true

	while(session:ready() == true) and continue == true do

		local invalid_entry = default_prompt_path .. "125.wav" -- That was an invalid entry. Please try again.
		
		--Manage Prompt Menu
		--Press 1 to Listen to the Prompt
		--Press 2 to Record the Prompt
		--Press 3 to Use the Default Prompt
		--Press [star] to Exit to the Main Menu

		local menu_choice = session:playAndGetDigits(1,1,3, 4000, "", "phrase:adtransfer_recorder_manageprompt_menu:110.wav:111.wav:112.wav:113.wav:114.wav", invalid_entry,"\\d|\\*")
		
		if (menu_choice == "1") then

			local prompt_var = ""

			--map to correct default prompt_name
			if (prompt_name == "greeting") then
				prompt_var = "custom_greeting_file"
			elseif (prompt_name == "whisper") then
				prompt_var = "whisper_file"
			elseif (prompt_name == "quickstart") then
				prompt_var = "quickstart_file"
			elseif (prompt_name == "calltransfer") then
				prompt_var = "call_transfer_file"
			end --if prompt_name

			--listen to the prompt
			prompt_file = session:getVariable(prompt_var)

			if (prompt_file == "default" or prompt_file == "") then
				--map to correct default prompt_name
				if (prompt_name == "greeting") then
					prompt_file = "greeting.wav"
				elseif (prompt_name == "whisper") then
					prompt_file = "whisper.wav"
				elseif (prompt_name == "quickstart") then
					prompt_file = "enter_quick_start_code.wav"
				elseif (prompt_name == "calltransfer") then
					prompt_file = "hold_for_connect.wav"
				end --if prompt_name
			
				prompt_file = default_prompt_path..prompt_file
			else
				prompt_file = user_prompt_path..prompt_file
			end

			--play either the default or custom prompt file
			session:streamFile(prompt_file)

		elseif (menu_choice == "2") then
			--record the prompt
			record_prompt_name = prompt_name.."_"..session:getVariable("app_adtransfer_id")
			record_result = recordPrompt(prompt_name, record_prompt_name)

		elseif (menu_choice == "3") then
			--use the default prompt
			result = verifyUseDefaultPrompt(prompt_name)
			if (result == -1) then
				--choice cancelled, loop menu
			else
				--exit to main menu
				result = 1
				break
			end
		elseif (menu_choice == "*") then
 			--exit to main menu
			result = 1
			break
		else
			result = -1
			break
		end --if

	end--while

	freeswitch.consoleLog("INFO","********** EXIT managePromptMenu ********\n")

	return result

end -- function managePromptMenu

-- ************************************************************************
-- ****                 VERIFY USE DEFAULT PROMPT                       ***
-- ************************************************************************

function verifyUseDefaultPrompt(chosen_prompt)

	freeswitch.consoleLog("INFO","********** VERIFY USE DEFAULT PROMPT ********\n")

	if (session:ready() == true) then
		--you indicated you want to use the Default Prompt
		local prompt = default_prompt_path .. "116.wav"
		session:streamFile(prompt)
	
		--If this is correct, press 1. Otherwise press star to Cancel
		prompt = default_prompt_path .. "117.wav"
		verify_choice = session:read(1,1,prompt,2000,"")
			
		if (verify_choice == "1") then
			--change setting to use default
			local result = saveDefaultPrompt(chosen_prompt)

			--use default prompt saved
			prompt = default_prompt_path .. "126.wav"

			session:streamFile(prompt)

			--map to correct default prompt_name
			if (chosen_prompt == "greeting") then
				prompt_var = "custom_greeting_file"
			elseif (chosen_prompt == "whisper") then
				prompt_var = "whisper_file"
			elseif (chosen_prompt == "quickstart") then
				prompt_var = "quickstart_file"
			elseif (chosen_prompt == "calltransfer") then
				prompt_var = "call_transfer_file"
			end --if chosen_prompt

			session:setVariable(prompt_var, "default")

			freeswitch.consoleLog("INFO",prompt_var..":"..session:getVariable(prompt_var).."\n")

			--wait a moment
			session:execute("sleep","250")

			result = 1

		elseif (verify_choice == "*") then
			--cancel
			--exit this sub-menu without doing anything
			result = -1	
		end
	end --if session:ready

	return result

end -- function verifyUseDefaultPrompt

function saveDefaultPrompt(chosen_prompt)
	-- ************************************************************************
	-- ****         SAVE DEFAULT PROMPT CHOICE TO DATABASE                  ***
	-- ************************************************************************

	local sql = "CALL setUseDefaultPrompt('" .. chosen_prompt .. "'," .. session:getVariable("app_adtransfer_id")..")"
	freeswitch.consoleLog("INFO","sql = " .. sql .. "\n")
	
	-- execute the query and define a callback function
	freeswitch.consoleLog("INFO","executing query\n")
	assert(dbh:query(sql));

end -- function saveDefaultPrompt

-- ************************************************************************ 
-- **** 		RECORD PROMPT					*** 
-- ************************************************************************
function recordPrompt(prompt_name, prompt_file_name) 

	continue = true

	while (session:ready() == true) and (continue == true) do
		
		freeswitch.consoleLog("INFO","**********recordPrompt prompt_name: "..prompt_name.." prompt_file_name: "..prompt_file_name.."  ********\n")

		--Build directory and filename
		record_filename = prompt_file_name..".wav"
		record_prompt_path = user_prompt_path.."tmp/"

		freeswitch.consoleLog("INFO","record_prompt_path: "..record_prompt_path.."\n")
		freeswitch.consoleLog("INFO","record_filename: "..record_filename.."\n")

		recording_destination = string.format('%s%s', record_prompt_path, record_filename)

		handle = io.open(recording_destination, 'w')
		if handle == nil then   --Create the non-existent directory
			freeswitch.consoleLog("INFO","record_prompt_path does not exist...attempting mkdir -p on "..record_prompt_path.."\n")
        		os.execute("mkdir -p "..record_prompt_path)
		else
        		handle:close()
		end
		
		--Please record your prompt after the tone. When you are done recording, press the Pound key
		session:streamFile(default_prompt_path.."115.wav")

		--beep
		session:streamFile("tone_stream://v=-7;%(100,0,941.0,1477.0);v=-7;>=2;+=.1;%(1000, 0, 640)")

		--syntax is session:recordFile(file_name, max_len_secs, silence_threshold, silence_secs) 

		--set up recording parameters
		local max_len_secs = 30
		local silence_threshold = 500
		local silence_secs = 15

		--discard any dtmf digits hanging around in the buffer
		session:execute('flush_dtmf')

		--START RECORDING
		session:recordFile(recording_destination, max_len_secs, silence_threshold, silence_secs) 

		--PLAYBACK RECORDING
		session:streamFile(recording_destination)
		
		local submenu_result = postRecordMenu(prompt_name, record_filename)

		freeswitch.consoleLog("INFO","**********postRecordMenu returned submenu_result = "..submenu_result.." ********\n")

		if ((submenu_result == "-1") or (submenu_result == "1")) then
			continue = false
			break
		elseif (submenu_result == "2") then
			continue = true
		else
			continue = false
			break
		end --if submenu_result == -1

	end --sesion:ready continue true

	freeswitch.consoleLog("INFO","********** EXIT recordPrompt ********\n")

end -- function recordPrompt

function postRecordMenu(prompt_name, record_prompt_file)

	freeswitch.consoleLog("INFO","************postRecordMenu  prompt_name = "..prompt_name.." record_prompt_file = "..record_prompt_file.." *****************\n")

	local result = -1
	local continue = true

	while(session:ready() == true) and continue == true do

		local invalid_entry = default_prompt_path .. "125.wav" -- That was an invalid entry. Please try again.
		
		--Post-Record Prompt Menu
		--Press 1 to Save this Prompt
		--Press 2 to Re-Record the Prompt
		--Press [star] to Exit to the Manage Prompt Menu

		local menu_choice = session:playAndGetDigits(1,1,3, 4000, "", "phrase:adtransfer_recorder_postrecord_menu:118.wav:119.wav:120.wav", invalid_entry,"\\d|\\*")
		
		freeswitch.consoleLog("INFO","************postRecordMenu  menu_choice = "..menu_choice.." *****************\n")

		if (menu_choice == "1") then
			--SAVE THE PROMPT
			local prompt_channel_var = ""

			--first, relocate the file from the tmp folder
			tmp_location = user_prompt_path.."tmp/"..record_prompt_file
			
			os_move_command = "mv "..tmp_location.." "..user_prompt_path

			freeswitch.consoleLog("INFO","postRecordMenu os.execute("..os_move_command..")\n")

			--tmp folder is a sub-dir of the client dir, so just relocate it one level up
			os.execute(os_move_command)

			--map to correct default prompt_name
			if (prompt_name == "greeting") then
				prompt_channel_var = "custom_greeting_file"
				verify_save_prompt = "127.wav"
			elseif (prompt_name == "whisper") then
				prompt_channel_var = "whisper_file"
				verify_save_prompt = "128.wav"
			elseif (prompt_name == "quickstart") then
				prompt_channel_var = "quickstart_file"
				verify_save_prompt = "129.wav"
			elseif (prompt_name == "calltransfer") then
				prompt_channel_var = "call_transfer_file"
				verify_save_prompt = "130.wav"
			end --if prompt_name

			--set the prompt channel variable corresponding to the prompt we just recorded
			session:setVariable(prompt_channel_var,record_prompt_file)

			--save the filename to the database profile record for this client account
			local db_result = saveCustomPrompt(prompt_name, record_prompt_file, session:getVariable("app_adtransfer_id"))

			--build verification prompt file + path
			prompt_file = default_prompt_path..verify_save_prompt
			
			--play either the default or custom prompt file
			session:streamFile(prompt_file)

			--break out of the while
			continue = false
			result = 1
			break

		elseif (menu_choice == "2") then
			--RE-RECORD THE PROMPT
			--record the prompt
			result = 2
			continue = false
			break
		elseif (menu_choice == "*") then
 			--exit to main menu
			result = -1
			continue = false
			break
		end --if

		result = -1

	end--while

	freeswitch.consoleLog("INFO","********** EXIT postRecordMenu ********\n")

	return result


end -- function postRecordMenu

function saveCustomPrompt(chosen_prompt, custom_prompt_file)
	-- ************************************************************************
	-- ****         SAVE CUSTOM PROMPT TO DATABASE                          ***
	-- ************************************************************************

	local sql = "CALL setUseCustomPrompt('" .. chosen_prompt .. "','" .. custom_prompt_file .. "'," .. session:getVariable("app_adtransfer_id")..")"
	freeswitch.consoleLog("INFO","sql = " .. sql .. "\n")
	
	-- execute the query and define a callback function
	freeswitch.consoleLog("INFO","executing query\n")
	assert(dbh:query(sql));

end -- function saveCustomPrompt

-- ************************************************************************
-- ****                   END FUNCTION DEFINITIONS                      ***
-- ************************************************************************


-- ************************************************************************
-- ****                        BEGIN SCRIPT                             ***
-- ************************************************************************

-- Print a log message
freeswitch.consoleLog("INFO","AD TRANSFER PROMPT RECORDER APPLICATION\n")

--print out all available session variables
--session:execute("info","notice")

--connect to the db
freeswitch.consoleLog("INFO","init DB connection\n")
dbh = freeswitch.Dbh("fsphoenix", "fs_lua", "{ph03n!x}")
if dbh:connected() then
    if stream then
        stream:write("result - database connected\n\n")
    else
	freeswitch.consoleLog("INFO","database connected\n") 
    end
else	
    if stream then
	stream:write("result - database error\n")
    else
	freeswitch.consoleLog("INFO","database error") 
    end
    return
end


-- set variable - or print to console if no session is available
local function sv(key, val)
   --freeswitch.consoleLog("INFO","inside sv("..key..","..val..")\n")
   if session then
       session:setVariable(key, val)
   elseif stream then
       stream:write(string.format("%25s : %s\n", key, val))
   else -- a script executed using luarun does not have a stream
       freeswitch.consoleLog(key .. " : " .. val)
   end
end

-- ************************************************************************
-- ****                     ANSWER CALL                                 ***
-- ************************************************************************

--Answer the call
session:answer()

--Start Call Time
call_start_time = os.time()

-- ************************************************************************
-- ****                 PLAY GREETING                                   ***
-- ************************************************************************
--wait a sec, make sure audio is connected
session:execute("sleep","250")


prompt = default_prompt_path .."100.wav" 

-- Print a log message
freeswitch.consoleLog("INFO","Prompt file is '" .. prompt .. "'\n")

--Play the prompt
session:streamFile(prompt)

--wait a moment
session:execute("sleep","100")

-- ************************************************************************
-- ****           GET PROMPT RECORDER AUTH CODE                         ***
-- ************************************************************************

local record_auth_code = "";

enter_code_prompt = "101.wav"; -- Welcome to the Prompt Recording System

local data_entry_prompt = default_prompt_path .. enter_code_prompt 

local max_attempts = 3
local min_digits = 6
local max_digits = 10
local digit_timeout = 6000
local inter_digit_timeout = 2000
local invalid = default_prompt_path .. "125.wav" -- That was an invalid entry. Please try again.

-- Print a log message
freeswitch.consoleLog("INFO","Prompt file is '" .. prompt .. "'\n")
	
freeswitch.consoleLog("INFO","Collecting at least "..min_digits.." digits, at most " .. max_digits .. " wait for " .. digit_timeout .. " milliseconds\n")

--digits = session:playAndGetDigits (
--          min_digits, max_digits, max_attempts, timeout, terminators,
--          prompt_audio_files, input_error_audio_files,
--          digit_regex, variable_name, digit_timeout,
--          transfer_on_failure)

local valid_auth_code = false
local max_tries = 3
local loop_count = 1

local hangup_now = false

--make sure we're still connected
while (session:ready() == true) and (loop_count <= max_tries) and (valid_auth_code == false) and (hangup_now == false) do      

	freeswitch.consoleLog("INFO","******   LOOP COUNT: " .. loop_count .. "   *******\n")

	recorder_auth_code = session:playAndGetDigits(min_digits, max_digits, max_attempts, digit_timeout, "#", data_entry_prompt, invalid, "\\d+")
	if (recorder_auth_code == "") then
		hangup_now = true
		break
	else
		--we gathered an auth_code, let's check it
		-- ************************************************************************
		-- ****         QUERY DATABASE FOR AUTH CODE                            ***
		-- ************************************************************************

		local sql = "CALL checkAdTransferPromptRecorderAuthCode(" .. recorder_auth_code .. "," .. session:getVariable("destination_number") .. ")"
		freeswitch.consoleLog("INFO","sql = " .. sql .. "\n")

		-- execute the query and define a callback function
		freeswitch.consoleLog("INFO","executing query\n")
		assert(dbh:query(sql, function(row)
    			for key, val in pairs(row) do     -- in this example only one row with one column will be returned
       				sv(key, val)                   -- so here key = 'user'
				freeswitch.consoleLog("INFO",key .. " = " .. val .. "\n");
    			end
		end))

		--app_adtransfer_id
		--custom_greeting
		--custom_greeting_file
		--whisper_announce
		--whisper_file
		--collect_quickstart_code
		--quickstart_file
		--call_transfer_file
		--result

		local authcode_lookup_result = session:getVariable("result")

		if (authcode_lookup_result == "1") then
			--good result, let's break out of here, and continue w/ call flow

			--set up default paths
			user_prompt_path = "/data/recordings/app/adtransfer/client/clientid_"..session:getVariable("client_id").."/accountid_"..session:getVariable("client_account_id").."/prompts/"
			session:setVariable("user_prompt_path",user_prompt_path)

			break
		elseif (authcode_lookup_result == "-1") then
			--couldn't find the authcode
			
			--We are unable to locate that code in the system
			prompt = default_prompt_path .. "102.wav"
			session:streamFile(prompt)

  			freeswitch.consoleLog("INFO", "___INCREASING LOOP COUNT to " .. loop_count .. "\n")
			loop_count = loop_count + 1

		elseif (authcode_lookup_result == "-2") then
			--authcode found, but account not active
			
			--We're sorry, but your code is inactive, please contact your account representative.
			prompt = default_prompt_path .. "103.wav"  
			session:streamFile(prompt)

			hangup_now = true
		end -- if authcode_lookup_result == "1"
		
	end --if recorder_auth_code = ""

end --while (session:ready())

if (hangup_now == false) then

	while (session:ready() == true) and (hangup_now == false) do

		mainmenu_result = mainMenu()
		
		hangup_now = true

	end --while (session:ready())

end --if (hangup_now == false)


-- ************************************************************************
-- ****                         HANG UP                                 ***
-- ************************************************************************

--play thank you goodbye
prompt = default_prompt_path .. "121.wav"
session:streamFile(prompt)
	
--terminate the session
session:hangup()
	
freeswitch.consoleLog("INFO","***************END of ADTRANSFER PROMPT RECORDER****************\n")


Code Sample: MySQL Proc

Token Replacement

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`core3web1`@`%` PROCEDURE `masterTemplateDimensionText`(in_useranalysisid int, in_perceptiongroupname varchar(45), debug int)
BEGIN
    
    
    declare completedtemplate text;
    declare perceptiongroupid int;
    declare primarycategoryid int;
    declare secondarycategoryid int;
    declare minimalcategoryid int;
    declare sharedprimarycategoryid int;
    declare sharedsecondarycategoryid int;
    declare partialtemplate text;
    declare sharedprimarycategoryidcount int;
    declare sharedsecondarycategoryidcount int;
    declare nCounter int;
    declare ranking varchar(25);
    declare localtemplate text;
    declare rolescript varchar(45) DEFAULT "";
    
    
    declare strLength int;
    declare strPos int;
    declare outputText text;
    declare location int;
    declare token varchar(255);
    declare tokenLength int;
    declare tokenBeginChar char(1);
    declare tokenEndChar char(1);
    declare locationBeginToken int;
    declare locationEndToken int;
    declare tokencounter int;
    declare tokenreplacement varchar(5);
    declare prefix text;
    declare postfix text;
    declare grammartoken varchar(255);
    declare grammarranking varchar(45);
    declare localgrammartoken varchar(255);
    declare tokencount int;
    declare categoryid int;
    declare gTokenCounter int;
    declare sqltext text;

    declare primarybalancedcategoryid1 int;
    declare primarybalancedcategoryid2 int;
    declare secondarybalancedcategoryid1 int;
    declare secondarybalancedcategoryid2 int;
    declare minimalcategoryid1 int;
    declare minimalcategoryid2 int;

    select

      pg.perceptiongroupid

    into 

      perceptiongroupid

    from 

      perceptiongroup pg

    where

      displaytext = in_perceptiongroupname;
    
    
    set partialtemplate = "";
    
    drop temporary table if exists rankedScores;
    CREATE TEMPORARY TABLE rankedScores(
        perceptiongroupid int, 
        perceptiongroup varchar(100), 
        categoryid int, 
        category varchar(100), 
        score int, 
        ranking varchar(100)
    );
    
    INSERT INTO rankedScores(perceptiongroupid, perceptiongroup, categoryid, category, score, ranking)
    SELECT  
        pg.perceptiongroupid,
        pg.displaytext, 
        c.categoryid,
        c.displaytext, 
        (SELECT count(1) FROM useranalysisanswer ua where ua.perceptiongroupid = pg.perceptiongroupid and ua.categoryid = c.categoryid 
        and ua.useranalysisid = in_useranalysisid),
        'none'
    FROM
        (((perceptiongroup pg RIGHT JOIN question q on q.perceptiongroupid = pg.perceptiongroupid)
        RIGHT JOIN answer a on a.questionid = q.questionid)
        RIGHT JOIN category c on c.categoryid = a.categoryid)
    GROUP BY
        pg.perceptiongroupid, c.categoryid;

    INSERT INTO rankedScores(perceptiongroupid, perceptiongroup, categoryid, category, score, ranking)
    SELECT  
        4,'Overall', c.categoryid, c.displaytext, (SELECT count(1) FROM useranalysisanswer ua where ua.categoryid = c.categoryid 
        and ua.useranalysisid = in_useranalysisid) as score, 'none'
    FROM
        (((perceptiongroup pg RIGHT JOIN question q on q.perceptiongroupid = pg.perceptiongroupid)
        RIGHT JOIN answer a on a.questionid = q.questionid)
        RIGHT JOIN category c on c.categoryid = a.categoryid)
    GROUP BY
        c.categoryid;

    
    
    #JWG we must not remove the minimal scores since we have templates now! 2011.10.08

    #DELETE FROM rankedScores WHERE score < 5 AND perceptiongroupid < 100;
        
    #DELETE FROM rankedScores WHERE score < 15 AND perceptiongroupid = 100;

    DROP TEMPORARY TABLE IF EXISTS t2;
    CREATE TEMPORARY TABLE t2 (
        PerceptionGroupID int,
        CategoryID int,
        Score int);

    #first set the minimal scores
    INSERT INTO t2(PerceptionGroupID, Score)
    SELECT 
        DISTINCT rs.perceptiongroupid, MIN(rs.score)
    FROM
        rankedScores rs
    GROUP BY rs.perceptiongroupid;
        
        
    UPDATE rankedScores rs, t2
        SET rs.ranking = 'minimal'
    WHERE
        rs.perceptiongroupid = t2.PerceptionGroupID AND
        rs.score = t2.score;

    DELETE FROM t2;
    
    
    INSERT INTO t2(PerceptionGroupID, Score)
    SELECT 
        DISTINCT rs.perceptiongroupid, MAX(rs.score)
    FROM
        rankedScores rs
    GROUP BY rs.perceptiongroupid;
        
        
    UPDATE rankedScores rs, t2
        SET rs.ranking = 'primary'
    WHERE
        rs.perceptiongroupid = t2.PerceptionGroupID AND
        rs.score = t2.score;
    
    DROP TEMPORARY TABLE IF EXISTS t3;
    CREATE TEMPORARY TABLE t3
    SELECT rs.perceptiongroupid FROM rankedScores rs WHERE rs.ranking = 'primary' GROUP BY rs.ranking, rs.perceptiongroupid HAVING COUNT(1) > 1;
    
    UPDATE rankedScores rs
    SET rs.ranking = 'primary-balanced'
    WHERE
        rs.perceptiongroupid IN (SELECT temp.perceptiongroupid FROM t3 temp) and rs.ranking = 'primary';
        
    DELETE FROM t3;
    
    
    DELETE FROM t2;
    
    INSERT INTO t2(PerceptionGroupID, Score)
    SELECT 
        DISTINCT rs.perceptiongroupid, MAX(rs.score)
    FROM
        rankedScores rs
    WHERE
        rs.ranking NOT LIKE 'primary%' AND rs.ranking NOT LIKE 'minimal%'
    GROUP BY rs.perceptiongroupid
    ORDER BY rs.score DESC;
        
    UPDATE rankedScores rs, t2
        SET rs.ranking = 'secondary'
    WHERE
        rs.perceptiongroupid = t2.PerceptionGroupID AND
        rs.score = t2.score;

    
    INSERT INTO t3(perceptiongroupid)
    SELECT rs.perceptiongroupid FROM rankedScores rs WHERE rs.ranking = 'secondary' GROUP BY rs.ranking,rs.perceptiongroupid HAVING COUNT(1) > 1;
    
    UPDATE rankedScores rs
    SET rs.ranking = 'secondary-balanced'
    WHERE
        rs.perceptiongroupid IN (SELECT temp.perceptiongroupid FROM t3 temp) and rs.ranking = 'secondary';


    
    DELETE FROM t2;
    
    INSERT INTO t2(PerceptionGroupID, Score)
    SELECT 
        DISTINCT rs.perceptiongroupid, MAX(rs.score)
    FROM
        rankedScores rs
    WHERE
        rs.ranking NOT LIKE 'primary%' AND rs.ranking NOT LIKE 'secondary%'
    GROUP BY rs.perceptiongroupid
    ORDER BY rs.Score ASC;
        
    UPDATE rankedScores rs, t2
        SET rs.ranking = 'minimal'
    WHERE
        rs.perceptiongroupid = t2.PerceptionGroupID AND
        rs.score = t2.score;

    
    DELETE FROM rankedScores  WHERE rankedScores.ranking LIKE 'none%';
    
    if debug = 1 then
        SELECT * FROM rankedScores rs ORDER BY rs.perceptiongroupid ASC, rs.score DESC;
    end if;
    
    set secondarycategoryid = 0;
    set minimalcategoryid = 0;
    set sharedprimarycategoryid = 0;
    set sharedsecondarycategoryid = 0;
    set primarybalancedcategoryid1 = 0;
    set primarybalancedcategoryid2 = 0;
    set secondarybalancedcategoryid1 = 0;
    set secondarybalancedcategoryid2 = 0;
    set minimalcategoryid1 = 0;
    set minimalcategoryid2 = 0;

    #***********************************************
    
    set primarycategoryid = 0;
    set ranking = 'primary';
    set localtemplate = '';
    
    SELECT
        rs.categoryid
    INTO
        primarycategoryid
    FROM
        rankedScores rs
    WHERE 
        rs.perceptiongroupid = perceptiongroupid
        AND rs.ranking = ranking;

    #if debug = 1 then
    #    SELECT CONCAT("CALL getAnalysisTextDebug(",CAST(in_useranalysisid as CHAR),",",CAST(perceptiongroupid as CHAR),",'",ranking,"',", CAST(primarycategoryid as CHAR),",", CAST(secondarycategoryid as CHAR),");");
    # end if;
    
    CALL getDimensionText(in_useranalysisid, perceptiongroupid, ranking, primarycategoryid, localtemplate);

    if (localtemplate IS NOT NULL) then
        SET partialtemplate = localtemplate;
    end if;
    
    SET localtemplate = '';
    
    #***********************************************
    
    set primarycategoryid = 0;
    set ranking = 'primary-balanced';
    set localtemplate = '';
    
    SELECT 
        rs.categoryid
    INTO
        primarybalancedcategoryid1
    FROM
        rankedScores rs
    WHERE 
        rs.perceptiongroupid = perceptiongroupid
        AND rs.ranking = ranking
    LIMIT 1,1;
        

    SELECT 
        rs.categoryid
    INTO
        primarybalancedcategoryid2
    FROM
        rankedScores rs
    WHERE 
        rs.perceptiongroupid = perceptiongroupid
        AND rs.ranking = ranking
        AND rs.categoryid NOT IN (primarybalancedcategoryid1);

    #make sure we cover both possibilities 
    CALL getDimensionText(in_useranalysisid, perceptiongroupid, 'primary', primarybalancedcategoryid1, localtemplate);

    
    if length(localtemplate) > 0 then
        if length(partialtemplate) > 0 then
            SET partialtemplate = CONCAT(partialtemplate, " ", localtemplate);
        else
            SET partialtemplate = localtemplate;
        end if;
    end if;

    SET localtemplate = '';

    #second possible primary balanced category combo
    CALL getDimensionText(in_useranalysisid, perceptiongroupid, 'primary', primarybalancedcategoryid2, localtemplate);

    if length(localtemplate) > 0 then
        if length(partialtemplate) > 0 then
            SET partialtemplate = CONCAT(partialtemplate, " ", localtemplate);
        else
            SET partialtemplate = localtemplate;
        end if;
    end if;

    #***********************************************
      
   
    if debug = 1 then
        select partialtemplate;
    end if;
    
    set prefix = "";
    set postfix = "";
    set token = "";
    set tokenreplacement = "{0}";
    
    set tokencounter = 1;
    set locationBeginToken = 0;
    set locationEndToken = 0;
    
    set location = 0;
    set strLength = 0;
    set outputText = "";
    
    drop temporary table if exists foundTokens;
    create temporary table foundTokens(
        tokenorder int,
        token varchar(255),
        tokenreplacement varchar(255),
        grammarcategoryid int,
        grammarranking varchar(45),
        grammartoken varchar(255),
        replacewith varchar(255)
    );
    
    set strLength = char_length(partialtemplate);
    
    set tokenbeginchar = "[";
    set tokenendchar = "]";
    
    set strPos = 1;
    
    
    wloop: while INSTR(partialtemplate, tokenbeginchar) > 0 do
        
        set locationBeginToken = INSTR(partialtemplate, tokenbeginchar);
        
        if locationBeginToken > 0 then
            set locationEndToken = locate(tokenendchar, partialtemplate, locationBeginToken + 1);
            
            if locationEndToken > 0 and locationEndToken > locationBeginToken then
                
                
                set token = substring(partialtemplate,locationBeginToken, (locationEndToken-locationBeginToken)+1);
                set tokenreplacement = concat("{",tokencounter,"}");
                
                
                set grammartoken = substring(token,locate("-", token, 1) + 1, length(token) - (locate("-", token, 1) + 1));
                set grammarranking = substring(token, 2, locate("-", token, 1) - 2);
                
                set prefix = left(partialtemplate,locationEndToken);
                set postfix = right(partialtemplate, length(partialtemplate)-locationEndToken);
                
                
                set prefix = replace(prefix, token, tokenreplacement);
                
                set partialtemplate = CONCAT(prefix,postfix);
                
                insert into foundTokens (token, tokenorder, tokenreplacement, grammarranking, grammartoken)
                values(token, tokencounter, tokenreplacement, grammarranking, grammartoken);
                
                set tokencounter = tokencounter + 1;
                
                set strPos = locationEndToken + 1;
            else
                
                leave wloop;
            end if;
        else
            
            leave wloop;
        end if;
        
    end while;

    
    update foundTokens ft, rankedScores rs
    set ft.grammarcategoryid = rs.categoryid
    where rs.ranking = ft.grammarranking and
    rs.perceptiongroupid = perceptiongroupid;
    
    drop temporary table if exists tempGrammarTokens;
    
    create temporary table tempGrammarTokens(
        recordid int AUTO_INCREMENT,
        tokencount int, 
        grammartoken varchar(255), 
        categoryid int,
        PRIMARY KEY (recordid)
    ) ENGINE=MEMORY AUTO_INCREMENT=1 ;
        
    insert tempGrammarTokens(tokencount, grammartoken, categoryid)
    select count(1), ft.grammartoken, ft.grammarcategoryid
    from
        foundTokens ft
    group by ft.grammarcategoryid, ft.grammartoken;
    
    select
        count(1)
    into
        gTokenCounter
    from
        tempGrammarTokens;
    
    set nCounter = 1;

    drop temporary table if exists tempGrammar;
    create temporary table tempGrammar(        
        recordid int AUTO_INCREMENT,
        displaytext varchar(255), 
        token varchar(255), 
        categoryid int,
        PRIMARY KEY(recordid)
    ) ENGINE=MEMORY AUTO_INCREMENT=1 ;
    
    set @sqltext = "";
    set localgrammartoken = "";
    set tokencount = 0;
    
    while nCounter <= gTokenCounter do
    
        select 
            tgt.tokencount, tgt.grammartoken, tgt.categoryid
        into
            tokencount, localgrammartoken, categoryid
        from
            tempGrammarTokens tgt
        where
            tgt.recordid = nCounter;


        set sqltext = CONCAT("insert tempGrammar(displaytext, token, categoryid) select g.displaytext, g.token, g.categoryid from grammar g where g.categoryid = ",categoryid," and g.token = \"",localgrammartoken,"\" order by RAND() limit ", tokencount);

        
        
        set @sqltext = sqltext;
        
        prepare FooStmt from @sqltext;

        execute FooStmt;

        deallocate prepare FooStmt; 

                
        set nCounter = nCounter + 1;
            
    end while;

    set nCounter = 1;
    
    if debug = 1 then
        SELECT * FROM foundTokens;
    end if;

    if debug = 1 then
        SELECT * FROM tempGrammar;
    end if;
    
    BEGIN
    
        DECLARE loc_tokenorder, loc_grammarcategoryid int;
        DECLARE loc_grammartoken varchar(255);
        DECLARE loc_recordid, loc_categoryid int;
        DECLARE loc_displaytext varchar(255);
        DECLARE loc_token varchar(255);
        
        DECLARE localsql varchar(1000);

        DECLARE curFoundTokens CURSOR FOR SELECT ft.tokenorder, ft.grammartoken, ft.grammarcategoryid FROM foundTokens ft ORDER BY ft.grammarcategoryid, ft.grammartoken;
        DECLARE curTempGrammar CURSOR FOR SELECT tg.recordid, tg.displaytext, tg.token, tg.categoryid FROM tempGrammar tg ORDER BY tg.categoryid, tg.token;

        OPEN curFoundTokens;
        OPEN curTempGrammar;

        BEGIN
            DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
            LOOP

                FETCH curFoundTokens INTO loc_tokenorder, loc_grammartoken, loc_grammarcategoryid;
                FETCH curTempGrammar INTO loc_recordid, loc_displaytext, loc_token, loc_categoryid;
                
                update foundTokens ft
                set ft.replacewith = loc_displaytext
                where ft.tokenorder = loc_tokenorder and
                ft.grammarcategoryid = loc_categoryid and
                ft.grammartoken = loc_token;
                            
            END LOOP;
        END;

        CLOSE curFoundTokens;
        CLOSE curTempGrammar;
    END;

    if debug = 1 then
        select * from foundTokens;
    end if;
    
    
    BEGIN
    
        DECLARE loc_tokenreplacement varchar(5);
        DECLARE loc_replacewith varchar(255);
        
        DECLARE curFoundTokens2 CURSOR FOR SELECT ft.tokenreplacement, ft.replacewith FROM foundTokens ft WHERE ft.replacewith <> '' and ft.replacewith IS NOT NULL;

        OPEN curFoundTokens2;

        BEGIN
            DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
            LOOP

                FETCH curFoundTokens2 INTO loc_tokenreplacement, loc_replacewith;
                
                set partialtemplate = REPLACE(partialtemplate, loc_tokenreplacement, loc_replacewith);
                            
            END LOOP;
        END;

        CLOSE curFoundTokens2;
    END;
    
    
    #set partialtemplate = CONCAT(UPPER(LEFT(partialtemplate,1)),RIGHT(partialtemplate, LENGTH(partialtemplate)-1));
    #set partialtemplate = CONCAT(UPPER(LEFT(partialtemplate,1)),SUBSTRING(partialtemplate, 2));
    
    
    set tokencounter = 1;
    set locationBeginToken = 0;
    set locationEndToken = 0;
    
    set location = 0;
    set strLength = 0;
    set outputText = "";
    
    set strLength = char_length(partialtemplate);
    
    set tokenbeginchar = "+";
    set tokenendchar = "";
    
    set strPos = 1;
    
    
    wloop: while LOCATE(tokenbeginchar, partialtemplate, strPos) > 0 do
        
        set locationBeginToken = LOCATE(tokenbeginchar, partialtemplate, strPos);
        
        if locationBeginToken > 0 then
            set partialtemplate = CONCAT(LEFT(partialtemplate,locationBeginToken),'.  ',UPPER(SUBSTRING(partialtemplate,locationBeginToken + 1,1)),RIGHT(partialtemplate, LENGTH(partialtemplate)-locationBeginToken-1));
            #set partialtemplate = CONCAT(LEFT(partialtemplate,locationBeginToken + 1),UPPER(SUBSTRING(partialtemplate,locationBeginToken + 2,1)),RIGHT(partialtemplate, LENGTH(partialtemplate)-locationBeginToken-2));
            set strPos = locationBeginToken + 1;
        else
            
            leave wloop;
        end if;
        
    end while;
    
    set partialtemplate = REPLACE(partialtemplate, "+","");
    
    set partialtemplate = REPLACE(partialtemplate, "\"", "\\\"");
    set partialtemplate = REPLACE(partialtemplate, "<P>", "<\/p>");
    set partialtemplate = REPLACE(partialtemplate, CHAR(10), "");
    set partialtemplate = REPLACE(partialtemplate, CHAR(13), "");
    set partialtemplate = REPLACE(partialtemplate, CHAR(13)+CHAR(10), "");
    
    select concat("{\"perceptiongroupid\":\"",CAST(perceptiongroupid as CHAR),"\",\"text\":\"",LTRIM(partialtemplate),"\"}") as jsonstring;
    
    DROP TEMPORARY TABLE tempGrammarTokens;
    DROP TEMPORARY TABLE tempGrammar;
    DROP TEMPORARY TABLE rankedScores;
    DROP TEMPORARY TABLE t2;
    DROP TEMPORARY TABLE t3;
    
END