<?php 
session_start();
include '../db.php';
include("../all_query.php");
error_reporting(0);

	 if(isset($_SESSION['loan_crm_username']) && isset($_SESSION['loan_crm_password']))
	{
		
		
		
	  $report_from_date =  	$from_date = $_REQUEST['from_date'];
	  $report_to_date =    $to_date = $_REQUEST['to_date'];
		
		
		$today_date = date('Y-m-d');
		
		
			$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);
				
				
	


	function secToHour($seconds)
	{
	  return sprintf('%02d:%02d:%02d', ($seconds/ 3600),($seconds/ 60 % 60), $seconds% 60);	

	}
	
	
	
	function getPercentage($total_time,$used_time)
	{
		
			$count1 = $used_time / $total_time;
		   $count2 = $count1 * 100;
		   $count = number_format($count2, 0);
		   
		   return $count.'%';  
		
	}
	
	
					
 function check_in_range($start_date, $end_date, $date_from_user)
{
  // Convert to timestamp
  $start_ts = strtotime($start_date);
  $end_ts = strtotime($end_date);
  $user_ts = strtotime($date_from_user);

  // Check that user date is between start & end
  return (($user_ts >= $start_ts) && ($user_ts <= $end_ts));
}		
		
		
		 ?>
		 
		 
		 
		 <div class="table-responsive text-nowrap">
		 
		 <table id="myTable" class="table table-bordered " style="zoom:90%">
      <thead class="table-dark">
        <tr>
          <th>Advisor</th>
          <th>Total Calls Taken</th>
          <th>Sum of CONNECTED</th>
          <th>Log in Time</th>
          <th>Talk</th>
          <th>Ringing Time</th>
          <th>Wrap</th>
          <th>Idle</th>
		  
          <th>Total Break Time</th>
          <th>Lunch Avg</th>
          <th>Washroom Avg</th>
          <th>Tea Avg</th>
          <th>Training Avg</th>
		  
		  
		   <th>Talk%</th>
          <th>Ringing Time%</th>
          <th>Wrap%</th>
          <th>Idle%</th>
		  
		  <th>Break%</th>
          <th>Lunch%</th>
          <th>Washroom%</th>
          <th>Tea%</th>
          <th>Training%</th>
          
		  
        </tr>
      </thead>
      <tbody class="">
	  
	  <?php 
	  
	  $advisorQ = mysqli_query($conn,"select *from `advisors` ");
	  
	  
	   if($_SESSION['login_as'] == 'advisor')
		 {
			 $login_name = $_SESSION['login_name'];
			 $advisorQ = mysqli_query($conn,"select *from `advisors` where name='$login_name' "); 
		 }
	  
	  
	  
	    while($advisorData = mysqli_fetch_assoc($advisorQ))
		{
			
			$advisor_id = $advisorData['id'];
			$username = $advisorData['username'];
			
			
			$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;
			
			
			
			
			
			// calculate from table db
			
			
			
			$aprReportQ = mysqli_query($conn,"select *from `apr_report` where advisor_id = $advisor_id and  ( date(report_date) >= date('$report_from_date')  and date(report_date) <= date('$report_to_date') ) ");
			while($aprReportData = mysqli_fetch_assoc($aprReportQ))
			{
				
				$total_call_taken = $total_call_taken  + $aprReportData['total_call_taken'];
				$total_call_connected = $total_call_connected  + $aprReportData['total_call_connected'];
				
				$login_time_in_sec = $login_time_in_sec  + $aprReportData['login_time_in_sec'];
				
				if($aprReportData['talk_time_in_sec']!='')
				{
				$talk_time_in_sec = $talk_time_in_sec  + $aprReportData['talk_time_in_sec'];
				}
				
				$ringing_time_in_sec = $ringing_time_in_sec  + $aprReportData['ringing_time_in_sec'];
				$wrap_time_in_sec = $wrap_time_in_sec  + $aprReportData['wrap_time_in_sec'];
				$idle_time_in_sec = $idle_time_in_sec  + $aprReportData['idle_time_in_sec'];
				
				$total_break_time_in_sec = $total_break_time_in_sec  + $aprReportData['total_break_time_in_sec'];
				$lunch_time_in_sec = $lunch_time_in_sec  + $aprReportData['lunch_time_in_sec'];
				$tea_time_in_sec = $tea_time_in_sec  + $aprReportData['tea_time_in_sec'];
				$washroom_time_in_sec =  $washroom_time_in_sec  + $aprReportData['washroom_time_in_sec'];
				$training_time_in_sec = $training_time_in_sec  + $aprReportData['training_time_in_sec'];
				
			}
			
			
			
			
			
			
			// check if date range today
			
		 
			 
			 if(check_in_range($report_from_date, $report_to_date, $today_date))
			 {
 
			
				$from_date = $today_date;
				$to_date = $today_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);
				
			
			
			
			$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  );
				 
				 
				 
				 
				 
				 
				 
				 
			  
			
			  
			
			

	  ?>
	  
	  <tr>
	  
	  <td><?php echo $advisorData['name']; ?></td>
	  <td><?php echo $total_call_taken; ?></td>
	 <td><?php echo $total_call_connected; ?></td>
	 <td><?php if($login_time_in_sec > 0) {  echo secToHour($login_time_in_sec); }  ?></td>
	 <td><?php if($talk_time_in_sec > 0) {  echo secToHour($talk_time_in_sec); }  ?></td>
	 <td><?php if($ringing_time_in_sec > 0) {  echo secToHour($ringing_time_in_sec); }  ?></td>
	 <td><?php if($wrap_time_in_sec > 0) {  echo secToHour($wrap_time_in_sec); }  ?></td>
	 <td><?php if($idle_time_in_sec > 0) {  echo secToHour($idle_time_in_sec); }  ?></td>
	  
	  <td><?php if($total_break_time_in_sec > 0) {  echo secToHour($total_break_time_in_sec); }  ?></td>
	  <td><?php if($lunch_time_in_sec > 0) {  echo secToHour($lunch_time_in_sec); }  ?></td>
	  <td><?php if($tea_time_in_sec > 0) {  echo secToHour($tea_time_in_sec); }  ?></td>
	  <td><?php if($washroom_time_in_sec > 0) {  echo secToHour($washroom_time_in_sec); }  ?></td>
	  <td><?php if($training_time_in_sec > 0) {  echo secToHour($training_time_in_sec); }  ?></td>
	 
	 
	 
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$talk_time_in_sec);   } ?></td>
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$ringing_time_in_sec);   } ?></td>
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$wrap_time_in_sec);   } ?></td>
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$idle_time_in_sec);   } ?></td>
	  
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$total_break_time_in_sec);   } ?></td>
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$lunch_time_in_sec);   } ?></td>
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$tea_time_in_sec);   } ?></td>
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$washroom_time_in_sec);   } ?></td>
	  <td><?php  if($login_time_in_sec > 0) {  echo getPercentage($login_time_in_sec,$training_time_in_sec);   } ?></td>
	 
	 
	 
	 
	 
	  </tr>
       
		<?php } ?>
        
      </tbody>
    </table>
		 
	</div>	 
		 
		 
		 
		 
		 <?php
		
		
		
		
	}
	
	
	?>