<?php 
	  
	error_reporting(0);


      $host="localhost";
	  $username="finmarnf";
	  $password="Finman!@123";
	  $database="finmarnf_bhawani_crm";
 	   
	  
	 
	  
	    $conn=mysqli_connect($host,$username,$password,$database);
 	    
	     date_default_timezone_set("Asia/Kolkata"); 
	


     if(isset($_REQUEST['report_date']))
	 {
		  $report_date =$_REQUEST['report_date'];
	 }
	 else
	 {
     $report_date = date('Y-m-d');
	 }
	 

	 
		 
		 
		 $actual_run_time  = $report_date.' 22:00:00';
		 $current_date_time  = date('Y-m-d H:i:s');
		 
		 
		 if(strtotime($current_date_time) >= strtotime($actual_run_time))
		 {
			 
		
	
	   
	   $from_date = $report_date;
	   $to_date = $report_date;
	   
	   
	   	
		
		 $begin = new DateTime($from_date);
		 $end = new DateTime(date('Y-m-d',strtotime($to_date." +1 day")));

		 $interval = DateInterval::createFromDateString('1 day');
		 $period = new DatePeriod($begin, $interval, $end);
				
				
				
				
				
				
				
	   
	   
	   
	   $alreadyQ = mysqli_query($conn,"select *from `apr_report` where report_date = '$report_date' ");
	   
	   
	   if(mysqli_num_rows($alreadyQ) == 0)
	   {
	   
	 
	 
		$advisorQ = mysqli_query($conn,"select *from `advisors` ");
	  
	    while($advisorData = mysqli_fetch_assoc($advisorQ))
		{
			
			
			
			$advisor_id = $advisorData['id'];
			$username = $advisorData['username'];
			$advisor = $advisorData['name'];
			
			
			$total_call_taken = 0;
			$total_call_connected = 0;
			
			$login_time_in_sec = 0;
			$talk_time_in_sec = 0;
			$ringing_time_in_sec = 0;
			$wrap_time_in_sec = 0;
			$idle_time_in_sec = 0;
			
			$total_break_time_in_sec = 0;
			$lunch_time_in_sec = 0;
			$tea_time_in_sec = 0;
			$washroom_time_in_sec = 0;
			$training_time_in_sec = 0;
			
			
			
				
			
			
			$callResponseQ = mysqli_query($conn,"select *from `call_reponse` where caller_id = '$username'  and   ( date(date_time) >= date('$from_date')  and date(date_time) <= date('$to_date') )  ");
			$total_call_taken = mysqli_num_rows($callResponseQ);
			 
			 
			 $callResponseQ = mysqli_query($conn,"select * from `call_reponse` where caller_id = '$username'  and call_duration<>'0' and   ( date(date_time) >= date('$from_date')  and date(date_time) <= date('$to_date') )  ");
			 $total_call_connected = mysqli_num_rows($callResponseQ);
			 
			 
			 
			 $callResponseQ = mysqli_query($conn,"select sum(call_duration+0) as total_duration  from `call_reponse` where caller_id = '$username'  and call_duration<>'0' and   ( date(date_time) >= date('$from_date')  and date(date_time) <= date('$to_date') )  ");
			  
			  if($total_connectedData = mysqli_fetch_assoc($callResponseQ))
			  {
			  $talk_time_in_sec = $total_connectedData['total_duration'];
			  
			  }
			  
			  
			   // get rining time in sec
			  
			 $riningQ = mysqli_query($conn,"select  *from `call_reponse` where caller_id = '$username'  and call_duration = '0' and   ( date(date_time) >= date('$from_date')  and date(date_time) <= date('$to_date') )  ");
			  
			  while($riningQData = mysqli_fetch_assoc($riningQ))
			  {
			    $ring_start_time = $riningQData['start_time'];
			    $ring_end_time = $riningQData['end_time'];
				 
				 $diff_in_sec = strtotime($ring_end_time) -  strtotime($ring_start_time);
				 
				 $ringing_time_in_sec = $ringing_time_in_sec + $diff_in_sec;
				   
			  
			  }
			  
			  
			  
			  // get wrap time
			  
			  $callResponseQ = mysqli_query($conn,"select  * from `call_reponse` where caller_id = '$username'  and call_duration<>'0' and   ( date(date_time) >= date('$from_date')  and date(date_time) <= date('$to_date') )  ");
			  while($callResponseData = mysqli_fetch_assoc($callResponseQ))
			  {
				  
				  $uniqueid = $callResponseData['uniqueid'];
				  $wrap_start_time = $callResponseData['end_time'];  // start since when call disconnected 
				  
				  $updateQ = mysqli_query($conn,"select *from `updates` where uniqueid = '$uniqueid' ");
				  if($updateData = mysqli_fetch_assoc($updateQ))
				  {
					  $wrap_end_time = $updateData['date_time'];  //  end to when update saved
					  
					  
					  $diff_in_sec = strtotime($wrap_end_time) -  strtotime($wrap_start_time);
				 
				      $wrap_time_in_sec = $wrap_time_in_sec + $diff_in_sec;
				   
					  
					  
 				  }
				  
				  
			  }
			  
			  
			  
			  
			  
			   //  get Login Time
			  
						foreach ($period as $dt) 
						{
							//echo "AAAAAAAAAAAAAAAAAAAA";
							//echo $dt->format("l Y-m-d H:i:s\n");
							
							  $status_date = $dt->format("Y-m-d");
								  
			   $loginStatusQ = mysqli_query($conn,"select *from `advisor_login_status` where advisor_id =$advisor_id and  login_status = 'logged_in' and status_date = '$status_date'   order by id asc limit 1  ");
			    if($firstLoginData = mysqli_fetch_assoc($loginStatusQ))
				 {
					 
					  $first_login_time = $firstLoginData['status_time'];
					 
			    
			  
			  
				 // $loginStatusQ = mysqli_query($conn,"select *from `advisor_login_status` where advisor_id =$advisor_id and  login_status = 'logged_out' and   status_date = '$status_date'  and  time(status_time) >  time('$first_login_time')     order by id desc limit 1  ");
				 
				  $loginStatusQ = mysqli_query($conn,"select *from `advisor_login_status` where advisor_id =$advisor_id and  login_status = 'logged_out' and   status_date = '$status_date'        order by id desc limit 1  ");
				  
				  
				  if($lastLogoutData = mysqli_fetch_assoc($loginStatusQ))
				  {
					  
					  $last_lagout_time = $lastLogoutData['status_time'];
					  
				  }
				  else
				  {
					  $last_lagout_time = $advisorData['end_time'];
				  }
				   
				 
				   
				   // diff in time
				   
				   $diff_in_sec = strtotime($last_lagout_time) -  strtotime($first_login_time);
				   
				   $login_time_in_sec = $login_time_in_sec + $diff_in_sec;
			  
			  
					}
					
					 
					 
					 
					 //  Get Break time in loop
					  
					 $breakStatusQ = mysqli_query($conn,"select *from `advisor_login_status` where advisor_id =$advisor_id and  login_status = 'break' and status_date = '$status_date'   order by id asc  ");
					 
					  while($breakData = mysqli_fetch_assoc($breakStatusQ))
					  {
						$break_id = $breakData['id'];
						
						$break_start_time = $breakData['status_time'];
						
						$backOnLoginStatusQ = mysqli_query($conn,"select *from `advisor_login_status` where advisor_id =$advisor_id and  login_status = 'logged_in' and status_date = '$status_date'  and id > $break_id   order by id asc limit 1  ");
						
						if($backOnLoginStatusData = mysqli_fetch_assoc($backOnLoginStatusQ))
						{
							$break_end_time = $backOnLoginStatusData['status_time'];
							
						}
						else
						{
							$break_end_time = $advisorData['end_time'];
							
						}

						
						$diff_in_sec = strtotime($break_end_time) -  strtotime($break_start_time);
						
				        $total_break_time_in_sec = $total_break_time_in_sec + $diff_in_sec;
						
						if($breakData['login_status_type'] == 'lunch')
						{
							$lunch_time_in_sec = $lunch_time_in_sec  + $diff_in_sec;
						}
						
						if($breakData['login_status_type'] == 'tea')
						{
							$tea_time_in_sec = $tea_time_in_sec  + $diff_in_sec;
						}
						
						if($breakData['login_status_type'] == 'washroom')
						{
							$washroom_time_in_sec = $washroom_time_in_sec  + $diff_in_sec;
						}
						
						if($breakData['login_status_type'] == 'training')
						{
							$training_time_in_sec = $training_time_in_sec  + $diff_in_sec;
						}
						
						
			  

						
						  
					  }
					 
					 
					 
					 
					 
					 
					 
					 
					 
					 
				 }
				 
				 
				 
				 /******************  Get Talk time *********************/
				 
				 
				 
				 
				 
				 
				 
				 
				 // get  idle time
				 
				 $idle_time_in_sec =  $login_time_in_sec - ( $talk_time_in_sec  + $ringing_time_in_sec + $wrap_time_in_sec  );
				 
				 
				 
				 
				 
				 
				 
				 
				 $date_time = date('Y-m-d H:i:s');
				 
				 
				 mysqli_query($conn,"INSERT INTO `apr_report`( `advisor_id`, `advisor`, `report_date`, `total_call_taken`, `total_call_connected`, `login_time_in_sec`, `talk_time_in_sec`, `ringing_time_in_sec`, `wrap_time_in_sec`, `idle_time_in_sec`, `total_break_time_in_sec`, `lunch_time_in_sec`, `tea_time_in_sec`, `washroom_time_in_sec`, `training_time_in_sec`, `date_time`) VALUES ($advisor_id,'$advisor','$report_date','$total_call_taken','$total_call_connected','$login_time_in_sec','$talk_time_in_sec','$ringing_time_in_sec','$wrap_time_in_sec','$idle_time_in_sec','$total_break_time_in_sec','$lunch_time_in_sec','$tea_time_in_sec','$washroom_time_in_sec','$training_time_in_sec','$date_time')  ");
				 
				 
				 
				 
				 
			
			
			
			
			
			
			
			
			
			
			
		}



 }
       


	   }



?>