#!/usr/bin/perl
##########################################################################
#                                                                        #
# Astrad tools  Switzernet(c)2011                                        #
#                                                                        #
##########################################################################
# my modules
use strict;
use warnings;
use POSIX;
use Config::IniFiles;
use Switch;
use DBI;
use Getopt::Long;
my $action     = '';
my $help     = 0;
##########################################################################
sub usage {
   my $message = $_[0];
   if (defined $message && length $message) {
      $message .= "\n"
         unless $message =~ /\n$/;
   }

   my $command = $0;
   $command =~ s#^.*/##;

   print STDERR (
      $message,
      "Usage : $command -a <ACTION>\n" .
      "Action:\n".
      "       sipconfig        Show configuration.\n" .
      "       location2        location in DBA server.\n" .
      "       location3        location in Astrad server.\n" .
      "       tonotify         Accounts to notify with sending empty SIP NOTIFY packets.\n" .
      "       toping           Accounts to ping with sending empty UDP packets.\n" .
      "       astmultiua       Accounts with multipleua on this Astrad.\n" .
      "       dbamultiua       Accounts with multipleua on all Astrad servers.\n" .
      "       account=%s       Show information of this account (account=ACCOUNT).\n" .
      "       checkpass=%s:%s  check password md5 (checkpass=ACCOUNT:PASSWORD).\n".
      "       \n"
   );

   die("\n")
}
Getopt::Long::GetOptions(
   'a=s' => \$action,
) or usage("Invalid commmand line options.");
 
usage("The user name must be specified.")
   unless defined $action;
##########################################################################
my $config_file = '/etc/astrad/config/switzer.conf';
my ($User,$Pass,$master_host,$master_user,$acc,$NAS_IP,$pass);
##########################################################################
sub load_config {
    my $conf=Config::IniFiles->new(-file => $config_file);
    return 0 if !defined $conf ;
    $User  = $conf->val('ASTER_DB','DB_USER');
    $Pass  = $conf->val('ASTER_DB','DB_PASS');
    $master_host = $conf->val('DB3','DB_HOST');
    $master_user = $conf->val('DB3','DB_USER');
    $NAS_IP   = $conf->val('GLOBAL','NAS_IP');
    return 1;
}
##########################################################################
sub db_connect {
my $dbh = DBI->connect_cached("DBI:mysql:".$_[0].";host=".$_[1],$_[2],$_[3]);
return $dbh if $dbh ;
exit;
}
##########################################################################
$acc='oussama';
usage("Invalid action value.") unless ($action =~ m/^(sipconfig|location2|location3|tonotify|toping|astmultiua|dbamultiua|account.+|checkpass.+)$/i);
if ($action =~ m/^account=/i) {
   usage("Invalid usage of account action.") unless ($action =~ m/^account=(\w+)$/i);
   $action='account';
   $acc=$1;
   } 
if ($action =~ m/^checkpass=/i) {
   usage("Invalid usage of checkpass action.") unless ($action =~ m/^checkpass=(\w+):(\w+)$/i);
   $action='checkpass';
   $acc=$1;
   $pass=$2;
   }
load_config();
#my $dbh1=db_connect("asterisk","localhost",$User,$Pass);
#my $dbh2=db_connect("astrad",$master_host,$master_user,'');
#print "OK $action\n-$User,$Pass,$master_host,$master_user\n";
switch ($action) {
   case "sipconfig"  { sipconfig();  }
   case "location2"  { location2();  }
   case "location3"  { location3();  }
   case "tonotify"   { tonotify();   }
   case "toping"     { toping();     }
   case "astmultiua" { astmultiua(); }
   case "dbamultiua" { dbamultiua(); }
   case "account"    { account($acc); }
   case "checkpass"  { checkpass($acc,$pass);}
   else { usage("Bug: previous case not true.") }
}
##########################################################################
sub sipconfig{
my $query='SELECT * FROM sipconfig;';
print "SQL Query:\n\t$query\n";
print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -N -e '$query'";
system($command) == 0 or die "system $command failed: $?";
}
##########################################################################
sub location2{
my $query="SELECT * FROM location2 WHERE domain='$NAS_IP';";
print "SQL Query:\n\t$query\n";
print "Result   :\n";
my $command="mysql -h $master_host -u$master_user astrad -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
}
##########################################################################
sub location3{
my $query='SELECT * FROM location3;';
print "SQL Query:\n\t$query\n";
print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e '$query'";
system($command) == 0 or die "system $command failed: $?";
}
##########################################################################
sub tonotify{
my $query='SELECT username FROM location3 WHERE username NOT IN (SELECT username FROM multiple_ua WHERE domain=(SELECT value FROM sipconfig WHERE name=\'ipaddr\') GROUP BY username HAVING COUNT(username)>1);';
print "SQL Query:\n\t$query\n";
print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
}
##########################################################################
sub toping{
my $query='SELECT username, ipaddr, port FROM multiple_ua WHERE domain=(SELECT value FROM sipconfig WHERE name=\'ipaddr\') AND username NOT IN (SELECT username FROM multiple_ua WHERE domain=(SELECT value FROM sipconfig WHERE name=\'ipaddr\') GROUP BY username HAVING count(username)=1);';
print "SQL Query:\n\t$query\n";
print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
}
##########################################################################
sub astmultiua{
my $query='SELECT * FROM multiple_ua WHERE domain=(SELECT value FROM sipconfig WHERE name=\'ipaddr\');';
print "SQL Query:\n\t$query\n";
print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
}
##########################################################################
sub dbamultiua{
my $query='SELECT * FROM multiple_ua;';
print "SQL Query:\n\t$query\n";
print "Result   :\n";
my $command="mysql -h $master_host -u$master_user asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
}
##########################################################################
sub account{
my $account=shift;
my $query="SELECT * FROM location3 WHERE username='$account';";
print "\n-----------------------------------------------------------------------------------\n";
print "--  On Astrad:\n-----------------------------------------------------------------------------------\n\n";
print "SQL Query:\n\t$query\n";
print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
print "\n";
print "\n-----------------------------------------------------------------------------------\n\n";
$query="SELECT name,ipaddr,port,regseconds,md5secret FROM sipdevices2 WHERE name='$account';";
print "SQL Query:\n\t$query\n";
print "Result   :\n";
$command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
print "\n";
$query="SELECT name,md5secret FROM sipdevices WHERE name='$account';";
print "SQL Query:\n\t$query\n";
print "Result   :\n";
$command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
print "\n-----------------------------------------------------------------------------------\n";
print "--  On DBA:\n-----------------------------------------------------------------------------------\n\n";
$query="SELECT * FROM location2 WHERE username='$account';";
print "SQL Query:\n\t$query\n";
print "Result   :\n";
$command="mysql -h $master_host -u$master_user astrad -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
print "\n";
$query="SELECT * FROM location2_history WHERE username='$account' ORDER BY start DESC limit 200;";
print "SQL Query:\n\t$query\n";
print "Result   :\n";
$command="mysql -h $master_host -u$master_user astrad -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
print "\n";
$query="SELECT name,md5secret FROM sipdevices WHERE name='$account';";
print "SQL Query:\n\t$query\n";
print "Result   :\n";
$command="mysql -h $master_host -u$master_user asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
}
##########################################################################
sub checkpass {
#my $dbh1=db_connect("asterisk","localhost",$User,$Pass);
#my $dbh2=db_connect("astrad",$master_host,$master_user,'');
my ($account,$password)=@_;
print "$account,$password\n";
my $query="(SELECT md5secret FROM sipdevices WHERE name='$account') UNION (SELECT md5secret FROM sipdevices2 WHERE name='$account') UNION (SELECT md5('$account:switzernet:$password') AS md5secret);";
print "\n----------------------------------------------------------\n";
print "--  On Astrad:\n----------------------------------------------------------\n\n";
print "SQL Query (should return only one line with encrypted pass):\n\t$query\n";
print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
print "\n----------------------------------------------------------\n";
print "--  On DBA:\n----------------------------------------------------------\n\n";
$query="(SELECT md5secret FROM users WHERE username='$account') UNION (SELECT md5secret FROM asterisk.sipdevices WHERE name='$account') UNION (SELECT md5('$account:switzernet:$password') AS md5secret);";
print "SQL Query:\n\t$query\n";
print "Result   :\n";
$command="mysql -h $master_host -u$master_user astrad -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";
}
