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

	 if(isset($_SESSION['loan_crm_username']) && isset($_SESSION['loan_crm_password']))
	{
		
		
		
	   	$from_date = $_REQUEST['from_date'];
		$to_date = $_REQUEST['to_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);
				
				$total_day = 0;
				
				foreach ($period as $dt) 
				 {
					 
					 $total_day++;
							
				 }
				
				
	


	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.'%';  
		
	}
	
	
					
				
		
		
		 ?>
		 
		 
		 
		 <div class="table-responsive text-nowrap">
		 
		 <table id="myTable"  class="table table-bordered " style="zoom:90%">
      <thead class="table-dark">
        <tr>
           <th>Date</th>
          <th>Advisor</th>
          <th>Attempts Target</th>
          <th>Unique Accounts</th>
          <th>Attempts</th>
          <th>Connect</th>
          <th>Connect%</th>
          <th>Today's PTP</th>
          <th>Paid Count</th>
          <th>Amount Collected for the day</th>
          <th>MTD Paid</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' "); 
		 }
		 
		 
		 
		 
		 $final_total_paid_count=0;
		 $final_today_amount_collected=0;
		 $final_mtp_paid=0;
		 
		 
	  
	    while($advisorData = mysqli_fetch_assoc($advisorQ))
		{
			
			$advisor_id = $advisorData['id'];
			$advisor = $advisorData['name'];
			$daily_attempts_target = $advisorData['daily_attempts_target'];
			$username = $advisorData['username'];
			 
			 
			 $total_call_taken = 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);
			 
			  

			// $uniqueattemptedQ  = mysqli_query($conn,"select *from `customer` where ( advisor = '$advisor' ) and  account_holder_id in (select account_holder_id from updates  ) ");
			
			
			 $uniqueattemptedQ  = mysqli_query($conn,"select *from `customer` where ( advisor = '$advisor' ) and  total_updates > 0 ");
			 
			 
			 
			  $total_unique_attempted = mysqli_num_rows($uniqueattemptedQ);
			  
			  
			  
			   $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);
			 
			 
			  $today_ptpQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor'   ) and    account_holder_id in (select account_holder_id from updates  where (disposition = 'PTP' or disposition = 'BRPTP' or  disposition = 'FPTP' )    and   ( date(next_update_date) >= date('$from_date')  and date(next_update_date) <= date('$to_date') ) ) ");
			    $total_today_ptp = mysqli_num_rows($today_ptpQ);
				
				
				
				
				$paymentQ  = mysqli_query($conn,"select *from `payments` where advisor='$advisor'  ");
				$paid_count = mysqli_num_rows($paymentQ);
				
				$mtp_paid = 0;
				
				$today_amount_collected = 0;
				
				while($paymentData = mysqli_fetch_assoc($paymentQ))
				{
				 $mtp_paid = $mtp_paid  + $paymentData['afterallocation_totalpaid'];
				 
				 }
				
				
				$todaypaymentQ  = mysqli_query($conn,"select *from `payments` where advisor='$advisor' and   ( date(afterallocation_lastpaiddate) >= date('$from_date')  and date(afterallocation_lastpaiddate) <= date('$to_date') )  ");
				
				
				while($todaypaymentData = mysqli_fetch_assoc($todaypaymentQ))
				{
				 $today_amount_collected = $today_amount_collected  + $todaypaymentData['afterallocation_totalpaid'];
				 
				 }
				
				
				
			  
			

	  ?>
	  
	  <tr>
	  
	  <td><?php echo date('d-M-y',strtotime($from_date)); ?> - <?php echo  date('d-M-y',strtotime($to_date));  ?></td>
	  <td><?php echo $advisorData['name']; ?></td>
	  <td><?php echo $daily_attempts_target * $total_day; ?></td>
	  <td><?php echo $total_unique_attempted; ?></td>
	  <td><?php echo $total_call_taken; ?></td>
	  <td><?php echo $total_call_connected; ?></td>
	  <td><?php if($total_call_taken > 0) {  echo getPercentage($total_call_taken,$total_call_connected);  } ?></td>
	  <td><?php echo $total_today_ptp; ?></td>
	  <td><?php echo $paid_count; $final_total_paid_count = $final_total_paid_count + $paid_count; ?></td>
	  <td><?php echo $today_amount_collected; $final_today_amount_collected = $final_today_amount_collected + $today_amount_collected;  ?></td>
	  <td><?php echo $mtp_paid;  $final_mtp_paid = $final_mtp_paid = $final_mtp_paid + $mtp_paid; ?></td>
	 
	 
	 
	 
	  </tr>
       
		<?php } ?>
		
		
		
		
		
		
		<tfoot>
		
		<tr>
		<th colspan="8">Total</th>
		<th ><?php echo $final_total_paid_count;  ?></th>
		<th ><?php echo $final_today_amount_collected;  ?></th>
		<th ><?php echo $final_mtp_paid;  ?></th>
		
		</tr>
		
		</tfoot>
		
		
		
		
		
		
		
		
        
      </tbody>
    </table>
		 
	</div>	 
		 
		 
		 
		 
		 <?php
		
		
		
		
	}
	
	
	?>