<?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"); 
	

  
   $today_date = date('Y-m-d');
		
  
   $advisorQ = mysqli_query($conn,"select * , (select name from customer_type where id = customer_type_id ) as `customer_type`  from `advisors`  "); 
   
   
   if($_SESSION['login_as'] == 'advisor')
   {
	   $login_name = $_SESSION['login_name'];

	    $advisorQ = mysqli_query($conn,"select * , (select name from customer_type where id = customer_type_id ) as `customer_type`  from `advisors` where name='$login_name' "); 
   }
   
   
   if(isset($_REQUEST['advisor']))
   {
   $login_name = $_REQUEST['advisor'];

	    $advisorQ = mysqli_query($conn,"select * , (select name from customer_type where id = customer_type_id ) as `customer_type`  from `advisors` where name='$login_name' "); 
   
   }
   
   
   
   
   $dataArray = array();
   
    while($advisorData = mysqli_fetch_assoc($advisorQ))
	{
		 
		 
			 $advisor = $advisorData['name'];
			 
			 $customer_type = $advisorData['customer_type'];
			 
			 $total_unique_cust = 0;
			 $total_allocation_count = 0;
			 $total_attempted = 0;
			 $total_unattempted = 0;
			 $total_contact = 0;
			 $total_contact_p = 0;
			 $total_contact_n = 0;
			 $total_no_contact = 0;
			 $total_retained = 0;
			 $total_ptp = 0;
			 $total_today_ptp = 0;
			 $total_followup = 0;
			 $total_today_followup = 0;
			 
			 
			 
			   
			 $custQ = mysqli_query($conn,"select *from `customer` where ( advisor = '$advisor' and  customer_type = '$customer_type' ) ");
			 $total_unique_cust = mysqli_num_rows($custQ);
			 
			 $allocationQ = mysqli_query($conn,"select *from `leads` where account_holder_id in (select account_holder_id from customer  where ( advisor = '$advisor' and  customer_type = '$customer_type' ) ) ");
			 $total_allocation_count = mysqli_num_rows($allocationQ);
			 
			 
			 	// $attemptedQ  = mysqli_query($conn,"select *from `customer` where ( advisor = '$advisor' and  customer_type = '$customer_type' ) and  account_holder_id in (select account_holder_id from updates  ) ");
			
			
			$attemptedQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor'  and  customer_type = '$customer_type'  ) and  total_updates > 0   ");
			 
			 $total_attempted = mysqli_num_rows($attemptedQ);
			 
			 
			 
			 
			 
			 // $unattemptedQ  = mysqli_query($conn,"select *from `customer` where ( advisor = '$advisor' and  customer_type = '$customer_type' ) and  account_holder_id not in (select account_holder_id from updates  ) ");
			  
			  $unattemptedQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor'  and  customer_type = '$customer_type'  ) and  total_updates = 0   ");
			 
			 $total_unattempted = mysqli_num_rows($unattemptedQ);
			 
			 
			 
			 
			 
			 
			 //  $contactpQ  = mysqli_query($conn,"select *from `customer` where ( advisor = '$advisor' and  customer_type = '$customer_type' ) and  account_holder_id in (select account_holder_id from updates  where attempt_status = 'Contact P' ) ");
			  
			  $contactpQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor'  and  customer_type = '$customer_type'  ) and  total_contact_p > 0   ");
			  
			  
			  $total_contact_p = mysqli_num_rows($contactpQ);
			 
			//  $contactnQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor' and  customer_type = '$customer_type' ) and  account_holder_id in (select account_holder_id from updates  where attempt_status = 'Contact N'  ) ");
			
			 $contactnQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor'  and  customer_type = '$customer_type'  ) and  total_contact_n > 0   ");
			 
			  $total_contact_n = mysqli_num_rows($contactnQ);
			  
			  
			  
			   $contactQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor'  and  customer_type = '$customer_type'  ) and  total_contact > 0   ");
			 
			  $total_contact = mysqli_num_rows($contactQ);
			  
			  
			  
			  
			  
			 // $nocontactQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor' and  customer_type = '$customer_type' ) and    account_holder_id in (select account_holder_id from updates  where attempt_status = 'No Contact'   )  and account_holder_id not in (select account_holder_id from updates   where attempt_status = 'Contact P' or attempt_status = 'Contact N'  ) ");
			 
			  $nocontactQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor'  and  customer_type = '$customer_type'  ) and  total_contact = 0 and total_non_contact > 0     ");
				  
			  $total_no_contact = mysqli_num_rows($nocontactQ);
				 
				 
				  $retainedQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor' and  customer_type = '$customer_type' and retained = 'yes' )  ");
				  
			     $total_retained = mysqli_num_rows($retainedQ);
				 
				 
			  
			  
			    $ptpQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor' and  customer_type = '$customer_type' ) and    account_holder_id in (select account_holder_id from updates  where (disposition = 'PTP' or disposition = 'BRPTP' or  disposition = 'FPTP' )   ) ");
			    $total_ptp = mysqli_num_rows($ptpQ);
				
				
				$today_ptpQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor' and  customer_type = '$customer_type' ) and    account_holder_id in (select account_holder_id from updates  where (disposition = 'PTP' or disposition = 'BRPTP' or  disposition = 'FPTP' ) and next_update_date = '$today_date'  ) ");
			    $total_today_ptp = mysqli_num_rows($today_ptpQ);
				
				
				
			   $followupQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor' and  customer_type = '$customer_type' ) and    account_holder_id in (select account_holder_id from updates  where  disposition='CB'   ) ");
			    $total_followup = mysqli_num_rows($followupQ);
			  
			  
			  $today_followupQ  = mysqli_query($conn,"select *from `customer` where  ( advisor = '$advisor' and  customer_type = '$customer_type' ) and    account_holder_id in ( select account_holder_id from updates  where disposition='CB'   and next_update_date = '$today_date'   ) ");
			    $total_today_followup = mysqli_num_rows($today_followupQ);
				
			  
			  
			 // $total_contact = $total_contact_p + $total_contact_n;
			  
			  
			 
			 
			 
			 
			 $row_array = array();
			 $row_array['advisor'] = $advisor;
			 $row_array['customer_type'] = $customer_type;
			 $row_array['total_unique_cust'] = $total_unique_cust;
			 $row_array['total_allocation_count'] = $total_allocation_count;
			 $row_array['total_attempted'] = $total_attempted;
			 $row_array['total_unattempted'] = $total_unattempted;
			 $row_array['total_contact'] = $total_contact;
			 $row_array['total_contact_p'] = $total_contact_p;
			 $row_array['total_contact_n'] = $total_contact_n;
			 $row_array['total_no_contact'] = $total_no_contact;
			 $row_array['total_retained'] = $total_retained;
			 $row_array['total_ptp'] = $total_ptp;
			 $row_array['total_today_ptp'] = $total_today_ptp;
			 $row_array['total_followup'] = $total_followup;
			 $row_array['total_today_followup'] = $total_today_followup;
			 
 			echo  $json_data = json_encode($row_array);
			 
			 mysqli_query($conn,"delete from dashboards where advisor = '$advisor' and type='admin_dashboard' ");
			 
			 $date_time = date('Y-m-d H:i:s');
			 
			 mysqli_query($conn,"INSERT INTO `dashboards`(`type`, `json_data`, `dashboard_date`, `advisor`, `date_time` ) VALUES ('admin_dashboard','$json_data','$today_date','$advisor','$date_time')  ");
 			 
			 
			 

		  
			 
			 
   }
   
   



?>  