#!/usr/bin/perl 
##########################################################
#
#
#
#

use warnings;
#use LWP::Simple;
use Log::Log4perl;
use DBI;
#use Data::Uniqid qw ( suniqid uniqid luniqid );

Proc::Daemon::Init; 



my $continue = 1;  
my $uniqId = 0;
#my $url = 'http://10.18.30.2/monitor/monit_test.php'; 
my $log_conf = "/opt/svam/scripts/scc/scc.conf"; 
Log::Log4perl::init($log_conf); 
my $logger = Log::Log4perl->get_logger();
my $current_speed = 100;
# Variables
my $work = 0;

$logger->debug("Demarrage de SCM Scheduler "); 
 
# sample logging statement
 $SIG{TERM} = sub { $continue = 0 };
 
      while ($continue) {
     #Start the daemon
           sleep(10);
		   my $stdDateTime = stdTime();
		   my $queuesLogFile = "/var/log/asterisk/queue_log";
		   my $lastnoFile = "/var/log/asterisk/lastno";
		   #system("/bin/cat /var/log/asterisk/queue_log >  ".$queuesLogFile);
		   #system("/bin/cat /dev/null > /var/log/asterisk/queue_log ");
		   
		   my $lastno = `cat $lastnoFile `;
		   chomp $lastno;
		    
		   
		   $lastno =  $lastno + 0;
		   $logger->debug(" PRECEDENT IDENTIFIANT  ::>  ".$lastno  ); 
		   
		   my $grepRes = `grep CONNECT  $queuesLogFile`;
		   open(my $fh, '>', '/var/log/asterisk/work.log') or die "Could not open file $!";
		   print $fh $grepRes;
		   close $fh;

		   
		   open my $queueCDRFile, '</var/log/asterisk/work.log' or die "Can't open the file: $!";
		   
		  
		   my $unixTimeStamp =0;
		   while (my $line = <$queueCDRFile>) {
               chomp $line;
			   
			   my ($unixTimeStamp,$uniqId,$application,$agentId,$eventname,$field1,$field2,$field3 ) = split /\|/,$line ;
			   chomp $unixTimeStamp;
			   $logger->debug(" CURRENT  ".$unixTimeStamp); 
			   if($unixTimeStamp gt $lastno ) {
			   

			       my $cmd = "grep -h $uniqId /var/log/asterisk/queue_log | grep ENTERQUEUE | cut -d '|' -f7";
			   
			       my $callerId = `$cmd`;
			       chomp $callerId;
			       chomp $agentId;
			       chomp $uniqId;
                               $agentId =~ s/SIP\///g;
			       $logger->debug(" ENREGISTREMENT DE  CALLERID ".$callerId." ANSWER_AGENT ".$agentId." UNIQUE_ID ".$uniqId ); 
                               my $ticket = getCallTicketNumberByUniqId($logger, $uniqId);
                               my $userId = getUserIdByCallerId($logger, $agentId);
                               #$uniqId = $uniqId.".wav";
                               update_incoming_call_data($logger, $ticket, $userId, $callerId, 0, $uniqId,"Appel");

			       #upld_incoming_call_record($logger, $callerId, $userId, 0, 0, $ticket,$uniqId);
			       $logger->debug(" ENREGISTREMENT DE  CALLERID ".$callerId." ANSWER_AGENT ".$agentId." UNIQUE_ID ".$uniqId." TERMINE" ); 
                               upld_new_ticket_event($logger, $ticket, $userId);
                               $logger->debug(" ENREGISTREMENT DE  L'HISTORIQUE CALLERID ".$callerId." ANSWER_AGENT ".$agentId." UNIQUE_ID ".$uniqId." TERMINE" );
                               upld_incoming_ticket_history_record($logger,$userId,0,$ticket,0,"",-1  );

			        my $res = `echo $unixTimeStamp > $lastnoFile `;
			   }   
               
           }
          close $queueCDRFile or die "Can't close Queue CDR File: $!";
		  
		  
           
          # $logger->debug("Parametres QOS  de SCM Scheduler ::>  MAX_WAIT ".$ring_tone_max. "  MAX_RETRIES ::> ".$retry_max."  INT_RETRIES ::> ".$retry_interval_max );  
           
           

     
             





       }
 
 
  



sub stdTime {
($seconds, $minutes, $hours, $day, $month, $year) = localtime();
$year += 1900;
$month += 1;
$date = sprintf("%04d%02d%02d-%02d%02d%02d", $year, $month, $day, $hours, $minutes, $seconds);
return $date;
}


sub upld_new_ticket_event {

 my $dbh = getDBConnection();
 my $logger =   $_[0];
 my $query = "INSERT INTO  scc_callevent_listener_tab ( item_id_n, ticket_number_v, user_id_n )
              VALUES (NULL,  '".$_[1]."','".$_[2]."') ";
 my $sth = $dbh->prepare(  $query   ) ;
 $logger->debug($query);
    $sth->execute();
    $sth->finish;
   closeDBConnection($dbh, $logger);
}


sub upld_incoming_ticket_history_record {

 my $dbh = getDBConnection();
 my $logger =   $_[0];
 my $query = "INSERT INTO crm_tickets_history_tab  ( item_id_n, event_datetime_d, user_id_n, profile_id_n, ticket_number_v, type_n, comment_v, status_n )
              VALUES (NULL, now(), '".$_[1]."', '".$_[2]."', '".$_[3]."', '".$_[4]."','".$_[5]."','".$_[6]."') ";
 my $sth = $dbh->prepare(  $query   ) ;
 $logger->debug($query);
    $sth->execute();
    $sth->finish;
   closeDBConnection($dbh, $logger);
}






sub upld_incoming_call_record {

 my $dbh = getDBConnection();
 my $logger =   $_[0];
 my $query = "INSERT INTO scc_call_history_tab ( call_id_n, event_datetime_d, from_callerid_v, to_number_v, online_status_n, status_n, caller_ticket_id, uniq_id_v )
              VALUES (NULL, now(), '".$_[1]."', '".$_[2]."', '".$_[3]."', '".$_[4]."','".$_[5]."','".$_[6]."') ";
 my $sth = $dbh->prepare(  $query   ) ;
 $logger->debug($query);
    $sth->execute();
    $sth->finish;
   closeDBConnection($dbh, $logger);
}

sub upld_incoming_call_data {

 my $dbh = getDBConnection();
 my $logger =   $_[0];
 my $query = "INSERT INTO crm_tickets_tab ( item_id_n, event_datetime_d, ticket_number_v, user_id_n, mobile1_phone_v, call_direction_n, char_field_1, char_field_2,char_field_5,char_field_6, status_n )
              VALUES (NULL, now(), '".$_[1]."', '".$_[2]."', '".$_[3]."','0', '".$_[4]."','".$_[5]."','".$_[6]."','-1','-1') ";
 my $sth = $dbh->prepare(  $query   ) ;
 $logger->debug($query);
    $sth->execute();
    $sth->finish;
   closeDBConnection($dbh, $logger);
}



sub getDBConnection {

	my $username = 'svam'; # set your MySQL username
	my $password = 'svam'; # set your MySQL password
	my $database = 'smdb'; # set your MySQL database name
	my $server = 'localhost'; # set your server hostname (probably localhost)
	my $dbh = DBI->connect("DBI:mysql:$database;host=$server", $username, $password)
		|| die "Could not connect to database: $DBI::errstr";

  return $dbh;
}

sub getNextTicket {

 my $logger =   $_[0];
 my $dbh =  getDBConnection();
 my $query = "select getAccountNumber('crm') as ticketId";
 my $sth = $dbh->prepare(  $query   ) ;
    $sth->execute();
    my $results = $sth->fetchrow_hashref;

    $sth->finish;
   closeDBConnection($dbh, $logger);

   return  $results->{ticketId} ;
}

sub getUserIdByCallerId {

 my $logger =   $_[0];
 my $callerNumber = $_[1];
 my $dbh =  getDBConnection();
 my $query = "select user_id_n from scc_users_tab  where caller_id_v='".$_[1]."'";
 my $sth = $dbh->prepare(  $query   ) ;
    $sth->execute();
    my $results = $sth->fetchrow_hashref;

    $sth->finish;
   closeDBConnection($dbh, $logger);

   return  $results->{user_id_n} ;
}

sub getUserIdByCallerId_allold {

 my $logger =   $_[0];
 my $callerNumber = $_[1];
 my $dbh =  getDBConnection();
 my $query = "select user_id_n from master_users_tab where mobile_phone_v='".$_[1]."'";
 my $sth = $dbh->prepare(  $query   ) ;
    $sth->execute();
    my $results = $sth->fetchrow_hashref;

    $sth->finish;
   closeDBConnection($dbh, $logger);

   return  $results->{user_id_n} ;
}



sub closeDBConnection {
my $dbh =   $_[0];
my $logger = $_[1];

$dbh->disconnect
    or $logger->debug("Disconnection failed: $DBI::errstr");


}



sub getCallTicketNumberByUniqId {

 my $logger =   $_[0];
 my $dbh =  getDBConnection();
 my $query = "select ticket_number_v as ticketId from crm_tickets_tab where char_field_2='".$_[1]."'";
 my $sth = $dbh->prepare(  $query   ) ;
    $sth->execute();
    my $results = $sth->fetchrow_hashref;

    $sth->finish;
   closeDBConnection($dbh, $logger);

   return  $results->{ticketId} ;
}


#upld_incoming_call_data($logger, $ticket, $userId, $callerId, 0, $uniqId,"Appel");

sub update_incoming_call_data {

 my $dbh = getDBConnection();
 my $logger =   $_[0];
 my $query = "UPDATE crm_tickets_tab  SET aknowledge_date_d=now() , user_id_n=".$_[2]." , call_direction_n=0 , char_field_1='".$_[4]."' , char_field_6='-1', char_field_5='".$_[6]."', status_n='-1'  WHERE ticket_number_v='".$_[1]."'";
 my $sth = $dbh->prepare(  $query   ) ;
 $logger->debug($query);
    $sth->execute();
    $sth->finish;
   closeDBConnection($dbh, $logger);
}

