#!/usr/bin/perl
#
# AST_update.pl version 1.1.11  (build 60411-1032)
#
# DESCRIPTION:
# uses the Asterisk Manager interface and Net::MySQL to update the live_channels
# tables and verify the parked_channels table in the asterisk MySQL database 
# This "near-live-status of Zap/SIP/Local/IAX channels" list is used by clients
#
# SUMMARY:
# This program was designed for people using the Asterisk PBX with Digium
# Zaptel telco cards and SIP VOIP hardphones or softphones as extensions, it
# could be adapted to other functions, but I designed it specifically for 
# Zap/IAX2/SIP users. The program will run on UNIX or Win32 command line 
# providing the following criteria are met:
# 
# Win32 - ActiveState Perl 5.8.0
# UNIX - Gnome or KDE with Tk/Tcl and perl Tk/Tcl modules loaded
# Both - Net::MySQL, Net::Telnet and Time::HiRes perl modules loaded
#
# For the client program to work, this program must always be running
# 
# For this program to work you need to have the "asterisk" MySQL database 
# created and create the tables listed in the MySQL_AST_CREATE_tables.sql file,
# also make sure that the machine running this program has select/insert/update/delete
# access to that database
# 
# In your Asterisk server setup you also need to have several things activated
# and defined. See the CONF_Asterisk.txt file for details
#
# It is recommended that you run this program on the local Asterisk machine
#
# Copyright (C) 2006  Matt Florell <vicidial@gmail.com>    LICENSE: GPLv2
#
# version changes:
# 41228-1659 - modified to compensate for manager output response hiccups
# 50107-1611 - modified to add Zap and IAX2 clients (differentiate from trunks)
# 50117-1537 - modified to add MySQL port ($DB_port) var from conf file
# 50303-0958 - modified to compensate for Zap manager output hiccups
# 50406-1320 - added channel_data to live_channels table for more flexibility
#            also added Zap/IAX client check every 100 loop instead of only once
# 50509-1047 - added collection of server_performance data with SYSPERF flag
# 50621-1307 - modified to allow for SIP trunks 
# 50810-1601 - Added database server variable definitions lookup
# 50823-1625 - Altered Debug vars and initial CVS/1.2 support for changed output
# 50824-1606 - Altered CVS/1.2 support for "show channels concise" output
# 50829-1134 - Added processor percentage usage to stat collection
# 51229-1355 - Added ability to take ! as delimited for 1.2 show channels concise
# 60117-1202 - Changed IAX2 client phone channel to reflect change to '-' iteration
# 60411-1032 - Fixed bug in test section that caused crash with ** in extension
#

$build = '60411-1032';

# constants
$SYSPERF=0;	# system performance logging to MySQL server_performance table every 5 seconds
$SYSPERF_rec=0;	# is dial-time recording turned on
$DB=0;	# Debug flag, set to 1 for debug messages  WARNING LOTS OF OUTPUT!!!
$DBX=0;	# Debug flag, set to 1 for debug messages  WARNING LOTS OF OUTPUT!!!
$US='__';
$AMP='@';
$MT[0]='';
$cpuUSERprev=0;
$cpuSYSTprev=0;
$cpuIDLEprev=0;

# DB table variables for testing
	$parked_channels =		'parked_channels';
	$live_channels =		'live_channels';
	$live_sip_channels =	'live_sip_channels';
	$server_updater =		'server_updater';
#	$parked_channels =		'TEST_parked_channels';
#	$live_channels =		'TEST_live_channels';
#	$live_sip_channels =	'TEST_live_sip_channels';
#	$server_updater =		'TEST_server_updater';

### begin parsing run-time options ###
if (length($ARGV[0])>1)
{
	$i=0;
	while ($#ARGV >= $i)
	{
	$args = "$args $ARGV[$i]";
	$i++;
	}

	if ($args =~ /--help/i)
	{
	print "allowed run time options:\n  [-t] = test\n  [-sysperf] = system performance logging\n  [-sysperfdebug] = system performance debug output\n  [-debug] = verbose debug messages\n  [-debugX] = Extra-verbose debug messages\n\n";
	exit;
	}
	else
	{
		if ($args =~ /-sysperf/i)
		{
		$SYSPERF=1; # System performance logging flag
		}
		if ($args =~ /-sysperfdebug/i)
		{
		$SYSPERFDB=1; # prints system performance data out to STDOUT
		}
		if ($args =~ /-debug/i)
		{
		$DB=1; # Debug flag
		}
		if ($args =~ /--debugX/i)
		{
		$DBX=1;
		print "\n----- SUPER-DUPER DEBUGGING -----\nBUILD: $build\n";
		}
		if ($args =~ /-t/i)
		{
		$TEST=1;
		$T=1;
		}
	}
}
else
{
#	print "no command line options set\n";
}
### end parsing run-time options ###



### Make sure this file is in a libs path or put the absolute path to it
require("/home/cron/AST_SERVER_conf.pl");	# local configuration file

if (!$DB_port) {$DB_port='3306';}

	&get_time_now;

	$event_string='PROGRAM STARTED||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||';
	&event_logger;

use lib './lib', '../lib';
use Time::HiRes ('gettimeofday','usleep','sleep');  # necessary to have perl sleep command of less than one second
use Net::MySQL;
use Net::Telnet ();
	  
	my $dbhA = Net::MySQL->new(hostname => "$DB_server", database => "$DB_database", user => "$DB_user", password => "$DB_pass", port => "$DB_port") 
	or 	die "Couldn't connect to database: $DB_server - $DB_database\n";

	$event_string='LOGGED INTO MYSQL SERVER ON 1 CONNECTION|';
	&event_logger;

### Grab Server values from the database
$stmtA = "SELECT telnet_host,telnet_port,ASTmgrUSERNAME,ASTmgrSECRET,ASTmgrUSERNAMEupdate,ASTmgrUSERNAMElisten,ASTmgrUSERNAMEsend,max_vicidial_trunks,answer_transfer_agent,local_gmt,ext_context,asterisk_version FROM servers where server_ip = '$server_ip';";
$dbhA->query("$stmtA");
if ($dbhA->has_selected_record)
	{
	$iter=$dbhA->create_record_iterator;
	   while ( $record = $iter->each)
		{
		$DBtelnet_host	=			"$record->[0]";
		$DBtelnet_port	=			"$record->[1]";
		$DBASTmgrUSERNAME	=		"$record->[2]";
		$DBASTmgrSECRET	=			"$record->[3]";
		$DBASTmgrUSERNAMEupdate	=	"$record->[4]";
		$DBASTmgrUSERNAMElisten	=	"$record->[5]";
		$DBASTmgrUSERNAMEsend	=	"$record->[6]";
		$DBmax_vicidial_trunks	=	"$record->[7]";
		$DBanswer_transfer_agent=	"$record->[8]";
		$DBSERVER_GMT		=		"$record->[9]";
		$DBext_context	=			"$record->[10]";
		$DBasterisk_version	=		"$record->[11]";
		if ($DBtelnet_host)				{$telnet_host = $DBtelnet_host;}
		if ($DBtelnet_port)				{$telnet_port = $DBtelnet_port;}
		if ($DBASTmgrUSERNAME)			{$ASTmgrUSERNAME = $DBASTmgrUSERNAME;}
		if ($DBASTmgrSECRET)			{$ASTmgrSECRET = $DBASTmgrSECRET;}
		if ($DBASTmgrUSERNAMEupdate)	{$ASTmgrUSERNAMEupdate = $DBASTmgrUSERNAMEupdate;}
		if ($DBASTmgrUSERNAMElisten)	{$ASTmgrUSERNAMElisten = $DBASTmgrUSERNAMElisten;}
		if ($DBASTmgrUSERNAMEsend)		{$ASTmgrUSERNAMEsend = $DBASTmgrUSERNAMEsend;}
		if ($DBmax_vicidial_trunks)		{$max_vicidial_trunks = $DBmax_vicidial_trunks;}
		if ($DBanswer_transfer_agent)	{$answer_transfer_agent = $DBanswer_transfer_agent;}
		if ($DBSERVER_GMT)				{$SERVER_GMT = $DBSERVER_GMT;}
		if ($DBext_context)				{$ext_context = $DBext_context;}
		if ($DBasterisk_version)		{$AST_ver = $DBasterisk_version;}
		} 
	}

	if ($AST_ver =~ /^1\.2|^CVS|^SVN/i) {$show_channels_12_format = 1;}
	else {$show_channels_12_format = 0;}

##### LOOK FOR ZAP CLIENTS AS DEFINED IN THE phones TABLE SO THEY ARE NOT MISLABELED AS TRUNKS
	print STDERR "LOOKING FOR Zap clients assigned to this server:\n";
	$Zap_client_count=0;
	$Zap_client_list='|';
	$stmtA = "SELECT extension FROM phones where protocol = 'Zap' and server_ip='$server_ip'";
	if($DB){print STDERR "|$stmtA|\n";}
	$dbhA->query("$stmtA");
	if ($dbhA->has_selected_record)
	   {
		$iter=$dbhA->create_record_iterator;
		while ( $record = $iter->each)
			{
			print STDERR $record->[0],"\n";
			$Zap_client_list .= "$record->[0]|";
			$Zap_client_count++;
			}
	   }

##### LOOK FOR IAX2 CLIENTS AS DEFINED IN THE phones TABLE SO THEY ARE NOT MISLABELED AS TRUNKS
	print STDERR "LOOKING FOR IAX2 clients assigned to this server:\n";
	$IAX2_client_count=0;
	$IAX2_client_list='|';
	$stmtA = "SELECT extension FROM phones where protocol = 'IAX2' and server_ip='$server_ip'";
	if($DB){print STDERR "|$stmtA|\n";}
	$dbhA->query("$stmtA");
	if ($dbhA->has_selected_record)
	   {
		$iter=$dbhA->create_record_iterator;
		while ( $record = $iter->each)
			{
			print STDERR $record->[0],"\n";
			$IAX2_client_list .= "$record->[0]|";
			if ($record->[0] !~ /\@/)
				{$IAX2_client_list .= "$record->[0]$AMP$record->[0]|";}
			else
				{
				$IAX_user = $record->[0];
				$IAX_user =~ s/\@.*$//gi;
				$IAX2_client_list .= "$IAX_user|";
				}
			$IAX2_client_count++;
			}
	   }

##### LOOK FOR SIP CLIENTS AS DEFINED IN THE phones TABLE SO THEY ARE NOT MISLABELED AS TRUNKS
	print STDERR "LOOKING FOR SIP clients assigned to this server:\n";
	$SIP_client_count=0;
	$SIP_client_list='|';
	$stmtA = "SELECT extension FROM phones where protocol = 'SIP' and server_ip='$server_ip'";
	if($DB){print STDERR "|$stmtA|\n";}
	$dbhA->query("$stmtA");
	if ($dbhA->has_selected_record)
	   {
		$iter=$dbhA->create_record_iterator;
		while ( $record = $iter->each)
			{
			print STDERR $record->[0],"\n";
			$SIP_client_list .= "$record->[0]|";
			if ($record->[0] !~ /\@/)
				{$SIP_client_list .= "$record->[0]$AMP$record->[0]|";}
			else
				{
				$SIP_user = $record->[0];
				$SIP_user =~ s/\@.*$//gi;
				$SIP_client_list .= "$SIP_user|";
				}
			$SIP_client_count++;
			}
	   }

	print STDERR "Zap Clients:  $Zap_client_list\n";
	print STDERR "IAX2 Clients: $IAX2_client_list\n";
	print STDERR "SIP Clients:  $SIP_client_list\n";

$one_day_interval = 12;		# 2 hour loops for one day
while($one_day_interval > 0)
{

		$event_string="STARTING NEW MANAGER TELNET CONNECTION||ATTEMPT|ONE DAY INTERVAL:$one_day_interval|";
	&event_logger;

if (!$telnet_port) {$telnet_port = '5038';}

	### connect to asterisk manager through telnet
	$t = new Net::Telnet (Port => $telnet_port,
						  Prompt => '/.*[\$%#>] $/',
						  Output_record_separator => '',);
	#$fh = $t->dump_log("$telnetlog");  # uncomment for telnet log
	if (length($ASTmgrUSERNAMEupdate) > 3) {$telnet_login = $ASTmgrUSERNAMEupdate;}
	else {$telnet_login = $ASTmgrUSERNAME;}
	$t->open("$telnet_host"); 
	$t->waitfor('/0\n$/');			# print login
	$t->print("Action: Login\nUsername: $telnet_login\nSecret: $ASTmgrSECRET\n\n");
	$t->waitfor('/Authentication accepted/');		# waitfor auth accepted

		$event_string="STARTING NEW MANAGER TELNET CONNECTION|$telnet_login|CONFIRMED CONNECTION|ONE DAY INTERVAL:$one_day_interval|";
	&event_logger;

	$endless_loop=5769999;		# 30 days minutes at .45 seconds per loop

	while($endless_loop > 0)
	{


		@DBchannels=@MT;
		@DBsips=@MT;
		@list_channels=@MT;
		@test_channels=@MT;

			&get_current_channels;

			&validate_parked_channels;

	   
	$t->buffer_empty;
	if (!$show_channels_12_format)
		{
		@list_channels = $t->cmd(String => "Action: Command\nCommand: show channels\n\n", Prompt => '/--END COMMAND-.*/'); 
		}
	else
		{
		@list_channels = $t->cmd(String => "Action: Command\nCommand: show channels concise\n\n", Prompt => '/--END COMMAND-.*/'); 
		}

	##### TEST CHANNELS ZAP/IAX2/Local TO SEE IF THERE WAS A LARGE HICCUP IN OUTPUT FROM PREVIOUS OUTPUT
	@test_channels=@list_channels;
	$test_zap_count=0;
	$test_iax_count=0;
	$test_local_count=0;
	$test_sip_count=0;
	$s=0;
	foreach(@test_channels)
		{
		chomp($test_channels[$s]);
		if($DBX){print "$s|$test_channels[$s]\n";}
		$test_channels[$s] =~ s/Congestion\s+\(Empty\)/ SIP\/CONGEST/gi;
		$test_channels[$s] =~ s/\(Outgoing Line\)|\(None\)/SIP\/ring/gi;
		$test_channels[$s] =~ s/\(Empty\)/SIP\/internal/gi;
		if (!$show_channels_12_format)
			{
			$test_channels[$s] =~ s/^\s*|\s*$//gi;
			$test_channels[$s] =~ s/\(.*\)//gi;
			}
		else
			{
			$EXcount = 0;
			$EXcount = @{[$test_channels[$s] =~ /\!/g]};
			if ($EXcount > 10)
				{
				@test_chan_12 = split(/\!/, $test_channels[$s]);
				}
			else
				{
				@test_chan_12 = split(/:/, $test_channels[$s]);
				}
		#	@test_chan_12 = split(/:/, $test_channels[$s]);
			if (length($test_chan_12[6])<2) {$test_chan_12[6] = 'SIP/ring';}
			$test_channels[$s] = "$test_chan_12[0]     $test_chan_12[6]";
			}
		if ($test_channels[$s] =~ /^Zap|^IAX2|^SIP|^Local/)
			{
			if ($test_channels[$s] =~ /^(\S+)\s+.+\s+(\S+)$/)
				{
				$channel = $1;
				$extension = $2;
				if ($show_channels_12_format)
					{$extension =~ s/^.*\(|\).*$//gi;}
				$extension =~ s/^SIP\/|-\S+$//gi;
				$extension =~ s/\|.*//gi;
				if ($channel =~ /^SIP/) {$test_sip_count++;}
				if ($channel =~ /^Local/) {$test_local_count++;}
				if ($IAX2_client_count) 
					{
					$channel_match=$channel;
					$channel_match =~ s/\/\d+$|-\d+$//gi;
					$channel_match =~ s/^IAX2\///gi;
					if ($IAX2_client_list =~ /\|$channel_match\|/i) {$test_iax_count++;}
					}
				if ($Zap_client_count) 
					{
					$channel_match=$channel;
					$channel_match =~ s/^Zap\///gi;
					if ($Zap_client_list =~ /\|$channel_match\|/i) {$test_zap_count++;}
					}
				}
			}
		$s++;
		}



		#	$DB_live_lines = ($#DBchannels + $#DBsips);
		$DB_live_lines = ($channel_counter + $sip_counter);
		if ( (!$DB_live_lines) or ($#list_channels < 2) )
			{$PERCENT_static = 0;}
		else
			{
			$PERCENT_static = ( ($#list_channels / $DB_live_lines) * 100);
			$PERCENT_static = sprintf("%6.2f", $PERCENT_static);
			}

		if ( (!$test_zap_count) or ($zap_client_counter < 2) )
			{$PERCENT_ZC_static = 0;}
		else
			{
			$PERCENT_ZC_static = ( ($test_zap_count / $zap_client_counter) * 100);
			$PERCENT_ZC_static = sprintf("%6.2f", $PERCENT_ZC_static);
			}

		if ( (!$test_iax_count) or ($iax_client_counter < 2) )
			{$PERCENT_IC_static = 0;}
		else
			{
			$PERCENT_IC_static = ( ($test_iax_count / $iax_client_counter) * 100);
			$PERCENT_IC_static = sprintf("%6.2f", $PERCENT_IC_static);
			}

		if ( (!$test_local_count) or ($local_client_counter < 2) )
			{$PERCENT_LC_static = 0;}
		else
			{
			$PERCENT_LC_static = ( ($test_local_count / $local_client_counter) * 100);
			$PERCENT_LC_static = sprintf("%6.2f", $PERCENT_LC_static);
			}

		if ( (!$test_sip_count) or ($sip_client_counter < 2) )
			{$PERCENT_SC_static = 0;}
		else
			{
			$PERCENT_SC_static = ( ($test_sip_count / $sip_client_counter) * 100);
			$PERCENT_SC_static = sprintf("%6.2f", $PERCENT_SC_static);
			}

		if ($endless_loop =~ /0$/)
			{print "-$now_date   $PERCENT_static    $#list_channels    $#DBchannels:$channel_counter      $#DBsips:$sip_counter    $PERCENT_ZC_static|$test_zap_count:$zap_client_counter    $PERCENT_IC_static|$test_iax_count:$iax_client_counter    $PERCENT_LC_static|$test_local_count:$local_client_counter    $PERCENT_SC_static|$test_sip_count:$sip_client_counter\n";}

		if ( ( ($PERCENT_static < 10) && ( ($channel_counter > 3) or ($sip_counter > 4) ) ) or
			( ($PERCENT_static < 20) && ( ($channel_counter > 10) or ($sip_counter > 10) ) ) or
			( ($PERCENT_static < 30) && ( ($channel_counter > 20) or ($sip_counter > 20) ) ) or
			( ($PERCENT_static < 40) && ( ($channel_counter > 30) or ($sip_counter > 30) ) ) or
			( ($PERCENT_static < 50) && ( ($channel_counter > 40) or ($sip_counter > 40) ) ) or
			( ($PERCENT_ZC_static < 20) && ( $zap_client_counter > 3 ) )  or
			( ($PERCENT_ZC_static < 40) && ( $zap_client_counter > 9 ) )  or
			( ($PERCENT_IC_static < 20) && ( $iax_client_counter > 3 ) )  or
			( ($PERCENT_IC_static < 40) && ( $iax_client_counter > 9 ) )  or
			( ($PERCENT_SC_static < 20) && ( $sip_client_counter > 3 ) )  or
			( ($PERCENT_SC_static < 40) && ( $sip_client_counter > 9 ) )    )
			{
			$UD_bad_grab++;
			$event_string="------ UPDATER BAD GRAB!!!    UBGcount: $UD_bad_grab\n          $PERCENT_static    $#list_channels    $#DBchannels:$channel_counter      $#DBsips:$sip_counter    $PERCENT_ZC_static|$test_zap_count:$zap_client_counter    $PERCENT_IC_static|$test_iax_count:$iax_client_counter    $PERCENT_LC_static|$test_local_count:$local_client_counter    $PERCENT_SC_static|$test_sip_count:$sip_client_counter\n";
			print "$event_string\n";
				&event_logger;
			if ($UD_bad_grab > 20) {$UD_bad_grab=0;}
			}
		else
			{
			$UD_bad_grab=0;
			if ( ($endless_loop =~ /0$/) && ($SYSPERF) )
				{
				$cpuUSERcent=0; $cpuSYSTcent=0; $cpuIDLEcent=0;
				### get processor usage seconds ###
				# cpu  924841 211725 270473 6961811
				@cpuUSE = `/usr/bin/cat /proc/stat`;
					if ($cpuUSE[0] =~ /cpu\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)/)
					{
					$cpuUSER  = ($1 + $2);
					$cpuSYST  = $3;
					$cpuIDLE  = $4;
					$cpuUSERdiff  = ($cpuUSER - $cpuUSERprev);
					$cpuSYSTdiff  = ($cpuSYST - $cpuSYSTprev);
					$cpuIDLEdiff  = ($cpuIDLE - $cpuIDLEprev);
					$cpuIDLEdiffTOTAL = (($cpuUSERdiff + $cpuSYSTdiff) + $cpuIDLEdiff);
					if ($cpuIDLEdiffTOTAL > 0) 
						{
						$cpuUSERcent  = sprintf("%.0f", (($cpuUSERdiff / $cpuIDLEdiffTOTAL) * 100));
						$cpuSYSTcent  = sprintf("%.0f", (($cpuSYSTdiff / $cpuIDLEdiffTOTAL) * 100));
						$cpuIDLEcent  = sprintf("%.0f", (($cpuIDLEdiff / $cpuIDLEdiffTOTAL) * 100));
						}
					$cpuUSERprev=$cpuUSER;
					$cpuSYSTprev=$cpuSYST;
					$cpuIDLEprev=$cpuIDLE;
					}

				### get system load ###
				$serverLOAD = `/usr/bin/cat /proc/loadavg`;
				$serverLOAD =~ s/ .*//gi;
				$serverLOAD =~ s/\D//gi;

				### get memory usage ###
				@GRABserverMEMORY = `/usr/bin/free -m -t`;
					if ($GRABserverMEMORY[1] =~ /Mem:\s+(\d+)\s+(\d+)\s+(\d+)\s+/)
					{
					$MEMused  = $2;
					$MEMfree  = $3;
					}
				### get number of system processes ###
				@GRABserverPROCESSES = `/bin/ps -A --no-heading`;
				$serverPROCESSES = $#GRABserverPROCESSES;

				if ($SYSPERF_rec) {$recording_count = ($test_local_count / 2)}
				 else {$recording_count=0;}

				if ($SYSPERFDB)
					{print "$serverLOAD  $MEMfree  $MEMused  $serverPROCESSES  $#list_channels  $cpuUSERcent  $cpuSYSTcent  $cpuIDLEcent\n";}

				$stmtA = "INSERT INTO server_performance (start_time,server_ip,sysload,freeram,usedram,processes,channels_total,trunks_total,clients_total,clients_zap,clients_iax,clients_local,clients_sip,live_recordings,cpu_user_percent,cpu_system_percent,cpu_idle_percent) values('$now_date','$server_ip','$serverLOAD','$MEMfree','$MEMused','$serverPROCESSES','$#list_channels','$channel_counter','$sip_counter','$test_zap_count','$test_iax_count','$test_local_count','$test_sip_count','$recording_count','$cpuUSERcent','$cpuSYSTcent','$cpuIDLEcent')";
					if( ($DB) or ($UD_bad_grab) ){print STDERR "\n|$stmtA|\n";}
				$dbhA->query($stmtA)  or die  "Couldn't execute query: |$stmtA|\n";
				}
			}

		if ($endless_loop =~ /00$/)
			{
			##### LOOK FOR ZAP CLIENTS AS DEFINED IN THE phones TABLE SO THEY ARE NOT MISLABELED AS TRUNKS
				print STDERR "LOOKING FOR Zap clients assigned to this server:\n";
				$Zap_client_count=0;
				$Zap_client_list='|';
				$stmtA = "SELECT extension FROM phones where protocol = 'Zap' and server_ip='$server_ip'";
				if($DB){print STDERR "|$stmtA|\n";}
				$dbhA->query("$stmtA");
				if ($dbhA->has_selected_record)
				   {
					$iter=$dbhA->create_record_iterator;
					while ( $record = $iter->each)
						{
						print STDERR $record->[0],"\n";
						$Zap_client_list .= "$record->[0]|";
						$Zap_client_count++;
						}
				   }

			##### LOOK FOR IAX2 CLIENTS AS DEFINED IN THE phones TABLE SO THEY ARE NOT MISLABELED AS TRUNKS
				print STDERR "LOOKING FOR IAX2 clients assigned to this server:\n";
				$IAX2_client_count=0;
				$IAX2_client_list='|';
				$stmtA = "SELECT extension FROM phones where protocol = 'IAX2' and server_ip='$server_ip'";
				if($DB){print STDERR "|$stmtA|\n";}
				$dbhA->query("$stmtA");
				if ($dbhA->has_selected_record)
				   {
					$iter=$dbhA->create_record_iterator;
					while ( $record = $iter->each)
						{
						print STDERR $record->[0],"\n";
						$IAX2_client_list .= "$record->[0]|";
						if ($record->[0] !~ /\@/)
							{$IAX2_client_list .= "$record->[0]$AMP$record->[0]|";}
						else
							{
							$IAX_user = $record->[0];
							$IAX_user =~ s/\@.*$//gi;
							$IAX2_client_list .= "$IAX_user|";
							}
						$IAX2_client_count++;
						}
				   }

			##### LOOK FOR SIP CLIENTS AS DEFINED IN THE phones TABLE SO THEY ARE NOT MISLABELED AS TRUNKS
				print STDERR "LOOKING FOR SIP clients assigned to this server:\n";
				$SIP_client_count=0;
				$SIP_client_list='|';
				$stmtA = "SELECT extension FROM phones where protocol = 'SIP' and server_ip='$server_ip'";
				if($DB){print STDERR "|$stmtA|\n";}
				$dbhA->query("$stmtA");
				if ($dbhA->has_selected_record)
				   {
					$iter=$dbhA->create_record_iterator;
					while ( $record = $iter->each)
						{
						print STDERR $record->[0],"\n";
						$SIP_client_list .= "$record->[0]|";
						if ($record->[0] !~ /\@/)
							{$SIP_client_list .= "$record->[0]$AMP$record->[0]|";}
						else
							{
							$SIP_user = $record->[0];
							$SIP_user =~ s/\@.*$//gi;
							$SIP_client_list .= "$SIP_user|";
							}
						$SIP_client_count++;
						}
				   }


				print STDERR "Zap Clients:  $Zap_client_list\n";
				print STDERR "IAX2 Clients: $IAX2_client_list\n";
				print STDERR "SIP Clients:  $SIP_client_list\n";
			}

	@list_chan_12=@MT;
	$EXcount = 0;
	$EXcount = @{[$list_channels[2] =~ /\!/g]};
	if ($EXcount > 10)
		{
		@list_chan_12 = split(/\!/, $list_channels[2]);
		}
	else
		{
		@list_chan_12 = split(/:/, $list_channels[2]);
		}
	if( ($DB) && ($show_channels_12_format) ) {print "concise: $#list_chan_12\n";}
	if ( ( ( ($list_channels[1] =~ /State Appl\./) or ($list_channels[2] =~ /State Appl\.|Application\(Data\)/) or ($list_channels[3] =~ /State Appl\.|Application\(Data\)/) ) || ($#list_chan_12 > 8) ) && (!$UD_bad_grab) )
		{

		$c=0;
			if($DB){print "lines: $#list_channels\n";}
			if($DB){print "DBchn: $#DBchannels\n";}
			if($DB){print "DBsip: $#DBsips\n";}
		foreach(@list_channels)
			{
			#	$DBchannels =~ s/^\|//g;

				chomp($list_channels[$c]);
					if( ($DB) or ($UD_bad_grab) ){print "-|$c|$list_channels[$c]|\n";}
				$list_channels[$c] =~ s/Congestion\s+\(Empty\)/ SIP\/CONGEST/gi;
				$list_channels[$c] =~ s/\(Outgoing Line\)|\(None\)/SIP\/ring/gi;
				$list_channels[$c] =~ s/\(Empty\)/SIP\/internal/gi;
				if (!$show_channels_12_format)
					{
					$list_channels[$c] =~ s/^\s*|\s*$//gi;
					$list_channels[$c] =~ s/\(.*\)//gi;
					}
				else
					{
					@list_chan_12=@MT;
					if ($EXcount > 10)
						{
						@list_chan_12 = split(/\!/, $list_channels[$c]);
						}
					else
						{
						@list_chan_12 = split(/:/, $list_channels[$c]);
						}
					if ($DBX) {print "EXcount: $EXcount\n";}
					if (length($list_chan_12[6])<2) {$list_chan_12[6] = 'SIP/ring';}
					$list_channels[$c] = "$list_chan_12[0]     $list_chan_12[6]";
					}
				$list_SIP[$c] = $list_channels[$c];
					if( ($DB) or ($UD_bad_grab) ){print "+|$c|$list_channels[$c]|\n\n";}

		########## PARSE EACH LINE TO DETERMINE WHETHER IT IS TRUNK OR CLIENT AND PUT IN APPROPRIATE TABLE
			if ($list_channels[$c] =~ /^Zap|^IAX2|^SIP|^Local/)
				{
				if ($list_channels[$c] =~ /^(\S+)\s+.+\s+(\S+)$/)
					{
					$line_type = '';
					$channel = $1;
					$extension = $2;
					$channel_data = $extension;
					if ($show_channels_12_format)
						{
						$extension =~ s/^.*\(|\).*$//gi;
						#### new data in 1.2 for future use
						# SIP/cc160-7b91:default:917274514920:3:Up:Dial:Zap/g2/17274514920|30|o:cc160::3:9:Zap/25-1
	# Zap/30-1:demo:010*010*010*012*8600091:1:Up:Dial:IAX2/TESTast2:test@10.10.10.12:4569/8600051|25|o:::3:123:IAX2/TESTast2-29
	# Zap/9-1:demo:990009*CL_UMGCOF_L**6317906**9545541833*13678*:2:Up:Dial:Zap/r3/90009*CL_UMGCOF_L**6317906**9545541833*13678*|120|o:::3:334:Zap/82-1
					#	$channel =		$list_chan_12[0];
					#	$channel_data =	$list_chan_12[0];
					#	$context =		$list_chan_12[1];
					#	$dialed =		$list_chan_12[2];
					#	$priority =		$list_chan_12[3];
					#	$state =		$list_chan_12[4];
					#	$extension =	$list_chan_12[6];
					#	$callerid =		$list_chan_12[7];
					#	$duration =		$list_chan_12[10];
					#	$bridged =		$list_chan_12[11];
						}
					$extension =~ s/^SIP\/|-\S+$//gi;
					$extension =~ s/\|.*//gi;
					$QRYchannel = "$channel$US$extension";

					if( ($DB) or ($UD_bad_grab) ){print "channel:   |$channel|\n";}
					if( ($DB) or ($UD_bad_grab) ){print "extension: |$extension|\n";}
					if( ($DB) or ($UD_bad_grab) ){print "QRYchannel:|$QRYchannel|\n";}

					if ($channel =~ /^SIP|^Zap|^IAX2/) {$line_type = 'TRUNK';}
					if ($channel =~ /^Local/) {$line_type = 'CLIENT';}
					if ($IAX2_client_count) 
						{
						$channel_match=$channel;
						$channel_match =~ s/\/\d+$|-\d+$//gi;
						$channel_match =~ s/^IAX2\///gi;
	#					print "checking for IAX2 client:   |$channel_match|\n";
						if ($IAX2_client_list =~ /\|$channel_match\|/i) {$line_type = 'CLIENT';}
						}
					if ($Zap_client_count) 
						{
						$channel_match=$channel;
						$channel_match =~ s/^Zap\///gi;
	#					print "checking for Zap client:   |$channel_match|\n";
						if ($Zap_client_list =~ /\|$channel_match\|/i) {$line_type = 'CLIENT';}
						}
					if ($SIP_client_count) 
						{
						$channel_match=$channel;
						$channel_match =~ s/-\S+$//gi;
						$channel_match =~ s/^SIP\///gi;
	#					print "checking for SIP client:   |$channel_match|\n";
						if ($SIP_client_list =~ /\|$channel_match\|/i) {$line_type = 'CLIENT';}
						}

					if ($line_type eq 'TRUNK')
						{
						if( ($DB) or ($UD_bad_grab) ){print "current channels: $#DBchannels\n";}

							$k=0;
							$channel_in_DB=0;
						foreach(@DBchannels)
							{
							if ( ($DBchannels[$k] eq "$QRYchannel") && (!$channel_in_DB) )
								{
								$DBchannels[$k] = '';
								$channel_in_DB++;
								}
							if( ($DB) or ($UD_bad_grab) ){print "DB $k|$DBchannels[$k]|     |";}
							$k++;
							}

						if ( (!$channel_in_DB) && (length($QRYchannel)>3) )
							{
							$stmtA = "INSERT INTO $live_channels (channel,server_ip,extension,channel_data) values('$channel','$server_ip','$extension','$channel_data')";
								if( ($DB) or ($UD_bad_grab) ){print STDERR "\n|$stmtA|\n";}
							$dbhA->query($stmtA)  or die  "Couldn't execute query: |$stmtA|\n";
							}
						}

					if ($line_type eq 'CLIENT')
						{
						if( ($DB) or ($UD_bad_grab) ){print "current sips: $#DBsips\n";}

							$k=0;
							$sipchan_in_DB=0;
						foreach(@DBsips)
							{
							if ( ($DBsips[$k] eq "$QRYchannel") && (!$sipchan_in_DB) )
								{
								$DBsips[$k] = '';
								$sipchan_in_DB++;
								}
							if( ($DB) or ($UD_bad_grab) ){print "DB $k|$DBsips[$k]|     |";}
							$k++;
							}

						if ( (!$sipchan_in_DB) && (length($QRYchannel)>3) )
							{
							$stmtA = "INSERT INTO $live_sip_channels (channel,server_ip,extension,channel_data) values('$channel','$server_ip','$extension','$channel_data')";
								if( ($DB) or ($UD_bad_grab) ){print STDERR "\n|$stmtA|\n";}
							$dbhA->query($stmtA)  or die  "Couldn't execute query: |$stmtA|\n";
							}
						}
					}
				}

			$c++;
			}
		if($DB){print "COUNT: $c|$#list_channels|$endless_loop\n";}


			if ($#DBchannels >= 0)
				{
					$d=0;
				foreach(@DBchannels)
					{
					if (length($DBchannels[$d])>4)
						{
							($DELchannel, $DELextension) = split(/\_\_/, $DBchannels[$d]);
							$stmtB = "DELETE FROM $live_channels where server_ip='$server_ip' and channel='$DELchannel' and extension='$DELextension' limit 1";
								if( ($DB) or ($UD_bad_grab) ){print STDERR "\n|$stmtB|\n";}
							$dbhA->query($stmtB);
				#			$dbhA->query($stmtB)  or die  "Couldn't execute query:\n";
						}
					$d++;
					}
				}

			if ($#DBsips >= 0)
				{
					$d=0;
				foreach(@DBsips)
					{
					if (length($DBsips[$d])>4)
						{
							($DELchannel, $DELextension) = split(/\_\_/, $DBsips[$d]);
							$stmtB = "DELETE FROM $live_sip_channels where server_ip='$server_ip' and channel='$DELchannel' and extension='$DELextension' limit 1";
								if( ($DB) or ($UD_bad_grab) ){print STDERR "\n|$stmtB|\n";}
							$dbhA->query($stmtB);
				#			$dbhA->query($stmtB)  or die  "Couldn't execute query:\n";
						}
					$d++;
					}
				}

		### sleep for 45 hundredths of a second
		usleep(1*450*1000);

	$endless_loop--;
		if($DB){print STDERR "\nloop counter: |$endless_loop|\n";}

		### putting a blank file called "update.kill" in a directory will automatically safely kill this program
		if (-e '/home/cron/update.kill')
			{
			unlink('/home/cron/update.kill');
			$endless_loop=0;
			$one_day_interval=0;
			print "\nPROCESS KILLED MANUALLY... EXITING\n\n"
			}

		$bad_grabber_counter=0;
		$no_channels_12_counter=0;
		}

	else
		{
		if ( ($list_channels[1] !~ /Privilege: Command/) && ($show_channels_12_format) )
			{
			$bad_grabber_counter++;
			if($DB){print STDERR "\nbad grab, trying again\n";}
			### sleep for 20 hundredths of a second
			usleep(1*200*1000);

				$event_string="BAD GRAB TRYING AGAIN|BAD_GRABS: $bad_grabber_counter|$endless_loop|ONE DAY INTERVAL:$one_day_interval|";
			&event_logger;

			if ($bad_grabber_counter > 100)
				{
				$endless_loop=0;
					$event_string="TOO MANY BAD GRABS, STARTING NEW CONNECTION|BAD_GRABS: $bad_grabber_counter|$endless_loop|ONE DAY INTERVAL:$one_day_interval|";
				&event_logger;
				$bad_grabber_counter=0;
				}
			}
		else
			{
			$no_channels_12_counter++;
				$event_string="NO CHANNELS HERE|COUNTER: $no_channels_12_counter|$endless_loop|ONE DAY INTERVAL:$one_day_interval|$list_channels[1]";
			&event_logger;

			if($DBX) {print "*|EMPTY CHANNELS: $no_channels_12_counter|$#list_channels|$list_channels[1]";}

			### sleep for 40 hundredths of a second
			usleep(1*400*1000);
			if ($no_channels_12_counter == 3)
				{
				### there are no channels, delete all from live_channels, live_sip_channels
					$event_string="NO CHANNELS HERE|COUNTER: $no_channels_12_counter|$endless_loop|ONE DAY INTERVAL:$one_day_interval|$list_channels[1]";
				&event_logger;
				$stmtB = "DELETE FROM $live_sip_channels where server_ip='$server_ip'";
					if( ($DB) or ($UD_bad_grab) ){print STDERR "\n|$stmtB|\n";}
				$dbhA->query($stmtB);
				$stmtB = "DELETE FROM $live_channels where server_ip='$server_ip'";
					if( ($DB) or ($UD_bad_grab) ){print STDERR "\n|$stmtB|\n";}
				$dbhA->query($stmtB);
				}

			}
		}


	}


		if($DB){print "DONE... Exiting... Goodbye... See you later... Not really, initiating next loop...\n";}

		$event_string='HANGING UP|';
		&event_logger;

		@hangup = $t->cmd(String => "Action: Logoff\n\n", Prompt => "/.*/"); 

		$ok = $t->close;

	$one_day_interval--;

}

		$event_string='CLOSING DB CONNECTION|';
		&event_logger;


	$dbhA->close;


	if($DB){print "DONE... Exiting... Goodbye... See you later... Really I mean it this time\n";}


exit;







#######################################################################
# This subroutine simply grabs all active channel/extension combinations from
# the asterisk MySQL database to be compared to the Asterisk Manager results
#######################################################################
sub get_current_channels
{
$channel_counter=0;
$sip_counter=0;
$zap_client_counter=0;
$iax_client_counter=0;
$local_client_counter=0;
$sip_client_counter=0;

	if($DB){print STDERR "\n|SELECT channel,extension FROM $live_channels where server_ip = '$server_ip'|\n";}

$dbhA->query("SELECT channel,extension FROM $live_channels where server_ip = '$server_ip'");
if ($dbhA->has_selected_record)
   {
	$iter=$dbhA->create_record_iterator;
	$rec_count=0;
	while ( $record = $iter->each)
		{
		if($DB){print STDERR $record->[0],"|", $record->[1],"\n";}
			$DBchannels[$channel_counter] = "$record->[0]$US$record->[1]";

		$channel_counter++;
		$rec_count++;
		}
   }

$dbhA->query("SELECT channel,extension FROM $live_sip_channels where server_ip = '$server_ip'");
if ($dbhA->has_selected_record)
   {
	$iter=$dbhA->create_record_iterator;
	$rec_count_sip=0;
	while ( $record = $iter->each)
		{
		if($DB){print STDERR $record->[0],"|", $record->[1],"\n";}
			$DBsips[$sip_counter] = "$record->[0]$US$record->[1]";

		if ($record->[0] =~ /^Zap/) {$zap_client_counter++;}
		if ($record->[0] =~ /^IAX/) {$iax_client_counter++;}
		if ($record->[0] =~ /^Local/) {$local_client_counter++;}
		if ($record->[0] =~ /^SIP/) {$sip_client_counter++;}
		$sip_counter++;
		$rec_count_sip++;
		}
   }

	&get_time_now;

$stmtU = "UPDATE $server_updater set last_update='$now_date' where server_ip='$server_ip'";
	if($DB){print STDERR "\n|$stmtU|\n";}
$dbhA->query($stmtU);

}





#######################################################################
# The purpose of this subroutine is to make sure that the calls that are 
# listed as parked in the parked_channels table are in fact live (to make 
# sure the caller has not hung up) and if the channel is not live to delete 
# the parked_channels entry for that specific parked channel entry
# 
# Yes it does use two DB connections all by itself, I just did that for speed
# and ease of programming to be backward compatible with MySQL < 4.1 or else
# I would have used a delete with subselect and saved all of this bloated code
#######################################################################
sub validate_parked_channels
{

if (!$run_validate_parked_channels_now) 
	{

	$parked_counter=0;
	@ARchannel=@MT;   @ARextension=@MT;   @ARparked_time=@MT;   @ARparked_time_UNIX=@MT;   
	$dbhA->query("SELECT channel,extension,parked_time,UNIX_TIMESTAMP(parked_time) FROM $parked_channels where server_ip = '$server_ip' order by channel desc, parked_time desc");
	if ($dbhA->has_selected_record)
	   {
		$iter=$dbhA->create_record_iterator;
		$rec_count=0;
		while ( $record = $iter->each)
		   {
			$PQchannel = $record->[0];
			$PQextension = $record->[1];
			$PQparked_time = $record->[2];
			$PQparked_time_UNIX = $record->[3];
				if($DB){print STDERR "\n|$PQchannel|$PQextension|$PQparked_time|$PQparked_time_UNIX|\n";}

			my $dbhC = Net::MySQL->new(hostname => "$DB_server", database => "$DB_database", user => "$DB_user", password => "$DB_pass", port => "$DB_port") 
	or 	die "Couldn't connect to database: $DB_server - $DB_database\n";


			$AR=0;
			$record_deleted=0;
			foreach(@ARchannel)
			   {
				if (@ARchannel[$AR] eq "$PQchannel")
					{
					if (@ARparked_time_UNIX[$AR] > $PQparked_time_UNIX)
						{
							if($DBX){print "Duplicate parked channel delete: |$PQchannel|$PQparked_time|\n";}
						$stmtPQ = "DELETE FROM $parked_channels where server_ip='$server_ip' and channel='$PQchannel' and extension='$PQextension' and parked_time='$PQparked_time' limit 1";
								if($DB){print STDERR "\n|$stmtPQ|$$DEL_chan_park_counter|$DEL_chan_park_counter|\n\n";}
							$dbhC->query($stmtPQ);
							
							$DEL_chan_park_counter = "DEL$PQchannel$PQextension";
							$$DEL_chan_park_counter=0;
						$record_deleted++;
						}

					}

				$AR++;
			   }
			
			
			
			if (!$record_deleted)
				{
				$ARchannel[$rec_count] = $record->[0];
				$ARextension[$rec_count] = $record->[1];
				$ARparked_time[$rec_count] = $record->[2];
				$ARparked_time_UNIX[$rec_count] = $record->[3];
			

				my $dbhB = Net::MySQL->new(hostname => "$DB_server", database => "$DB_database", user => "$DB_user", password => "$DB_pass", port => "$DB_port") 
					or 	die "Couldn't connect to database: $DB_server - $DB_database\n";


				$event_string='LOGGED INTO MYSQL SERVER ON 2 CONNECTIONS TO VALIDATE PARKED CALLS|';
				&event_logger;


			   $dbhB->query("SELECT count(*) FROM $live_channels where server_ip='$server_ip' and channel='$PQchannel' and extension='$PQextension'");
			   if ($dbhB->has_selected_record)
				   {
					$iterB=$dbhB->create_record_iterator;
					$rec_countB=0;
					while ( $recordB = $iterB->each)
					   {
						$PQcount = $recordB->[0];
							if($DB){print STDERR "\n|$PQcount|\n";}

						if ($PQcount < 1)
							{
							$DEL_chan_park_counter = "DEL$PQchannel$PQextension";
							$$DEL_chan_park_counter++;
								if($DBX){print STDERR "Parked counter down|$$DEL_chan_park_counter|$DEL_chan_park_counter|\n";}

							### if the parked channel doesn't exist 6 times then delete it from table
							if ($$DEL_chan_park_counter > 5)
								{
							if($DBX){print "          parked channel delete: |$PQchannel|$PQparked_time|\n";}
								$stmtPQ = "DELETE FROM $parked_channels where server_ip='$server_ip' and channel='$PQchannel' and extension='$PQextension' limit 1";
									if($DB){print STDERR "\n|$stmtPQ|$$DEL_chan_park_counter|$DEL_chan_park_counter|\n\n";}
								$dbhC->query($stmtPQ);

									$ARchannel[$rec_count] = '';
									$ARextension[$rec_count] = '';
									$ARparked_time[$rec_count] = '';
									$ARparked_time_UNIX[$rec_count] = '';
								$$DEL_chan_park_counter=0;
								}
							}
						else
						   {
							$DEL_chan_park_counter = "DEL$PQchannel$PQextension";
							$$DEL_chan_park_counter=0;
						   }
					   }
				   }

				$event_string='CLOSING MYSQL CONNECTIONS OPENED TO VALIDATE PARKED CALLS|';
				&event_logger;


				$dbhB->close;

				}

			$dbhC->close;

		   }
		$parked_counter++;
		$rec_count++;
	   }

	$run_validate_parked_channels_now=5;	# set to run every five times the subroutine runs

	}

	$run_validate_parked_channels_now--;

}





################################################################################
##### get the current date and time and epoch for logging call lengths and datetimes
sub get_time_now
{
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year = ($year + 1900);
$mon++;
if ($mon < 10) {$mon = "0$mon";}
if ($mday < 10) {$mday = "0$mday";}
if ($hour < 10) {$Fhour = "0$hour";}
if ($min < 10) {$min = "0$min";}
if ($sec < 10) {$sec = "0$sec";}

$now_date_epoch = time();
$now_date = "$year-$mon-$mday $hour:$min:$sec";
}





################################################################################
##### open the log file for writing ###
sub event_logger 
{
open(Lout, ">>$LOGfile")
		|| die "Can't open $LOGfile: $!\n";
print Lout "$now_date|$event_string|\n";
close(Lout);
$event_string='';
}
