<?php  

  session_start();
  set_time_limit(0);
  ini_set('max_execution_time', 0);
  // error_reporting(0);
   include("../db.php");
   include("../all_query.php");
		
		
		
		
function cal_percentage($num_amount, $num_total) {
  $count1 = $num_amount / $num_total;
  $count2 = $count1 * 100;
  $count = number_format($count2, 0);
  return $count;
}



 
	  $today_date = date('Y-m-d');
	  
	 
	  
	  $at_least_paid_in_6_month_score =  60;
	  $emi_paid_per_score = 50;
	  $month_end_dpd = 40;
	  $product_score = 30;
	  
	  
	  
	  
	  $productArray = array();
	  $proQ  = mysqli_query($conn,"select *from `product`  ");
	  while($proData = mysqli_fetch_assoc($proQ))
	  {
		  $product_name = strtolower( str_replace(' ','_',$proData['name']));
		  
		  
		  $productArray[$product_name] =  $proData['score'];
	  }
	  
	  //print_r($productArray);
	  
	  
	  
   $customerQ = mysqli_query($conn,"select *from `customer` where  advisor='' and  account_holder_id not in (select account_holder_id from temp_filter_customer)  limit 1000 ");
	  
	 //  $customerQ = mysqli_query($conn,"select *from `customer`   limit 10 ");
	 
     while($customerData = mysqli_fetch_assoc($customerQ))
	 {
		  $customer_id = $customerData['id'];
		  
		  
		   $imp_score = 0;
		  
		echo "account_holder_id: ".$account_holder_id = $customerData['account_holder_id']; 
		 
		 
		 $leadQ=mysqli_query($conn,"select *from leads  where  account_holder_id = $account_holder_id order by id desc ");
		 
		 
		 $total_loan_amount = 0;
		 $total_tos = 0;
		 
		 $minimum_month = 7;
		 
		 $total_paid_in_last_6_month = 0;
		 $total_tenure = 0;
		 $total_emi_paid = 0;
		 $total_month_end_dpd = 0;
		 $total_product_score = 0;
		 
		 
		 
		 
		 $total_loan = mysqli_num_rows($leadQ);
		 
		 while($leadData = mysqli_fetch_assoc($leadQ))
		{
			 
			$lead_data = $leadData['lead_data'];
			$leadDataArray = json_decode(stripslashes($lead_data),TRUE);
			
			if($leadDataArray['payment1m']!='')
			{
				$total_paid_in_last_6_month = $leadDataArray['payment1m'] + $leadDataArray['payment2m'] + $leadDataArray['payment3m'] + $leadDataArray['payment4m'] + $leadDataArray['payment5m']+ $leadDataArray['payment6m'];
			}
			
			
			if($leadDataArray['tenure']!='')
			{
			$total_tenure = $total_tenure + $leadDataArray['tenure'];
			}
			
			if($leadDataArray['noofemispaid']!='')
			{
			$total_emi_paid = $total_emi_paid + $leadDataArray['noofemispaid'];
			}
			 
			 
			 
			 if($leadDataArray['monthenddpd']!='')
			 {
				 $total_month_end_dpd = $total_month_end_dpd + $leadDataArray['monthenddpd'];
			 }
			 
			 
			 
			 if($leadDataArray['genericmerchantname']!='')
			 {
				 $product_name =  strtolower( str_replace(' ','_',$leadDataArray['genericmerchantname']));
				 
				 if(isset($productArray[$product_name]))
				 {
				 $pro_score = $productArray[$product_name];
				 $total_product_score = $total_product_score + $pro_score;
				 }
				 
			 }
			 
			 
			 
			
		}
		 
		  
		  
		  $paid_score = round( $total_paid_in_last_6_month / 10 );
		   
		  echo "<br/>Total Paid : ".$total_paid_in_last_6_month."  Score:".$paid_score;
		  
		  $imp_score = $imp_score + $paid_score;
		  
		  
		   
		  
		  $emi_paid_percentage = round( cal_percentage($total_emi_paid,$total_tenure) );
		  
		  $per_score = $emi_paid_percentage + 30;
		  
		   $imp_score = $imp_score + $per_score;
		  
		  
		   echo "<br/>Emi Paid %: ".$emi_paid_percentage."  Score:".$per_score;
	

	
	       $avg_month_end_dpd = round($total_month_end_dpd / $total_loan);
		   $month_end_dpd_score = round($avg_month_end_dpd / 30 ) + 30;
		   
		    $imp_score = $imp_score + $month_end_dpd_score;
		  
		  
		  
		   echo "<br/>Avg DPD: ".$month_end_dpd_score."  Score:".$month_end_dpd_score;
	
		   
		   
	      $avg_product_score = 0;
	      $actual_product_score = 0;
		  
		  if($total_product_score > 0)
		  {
	      $avg_product_score = round($total_product_score / $total_loan);
		  $actual_product_score =  round(($avg_product_score/10))  + 10;
		  }
		  
		  
		  $imp_score = $imp_score + $actual_product_score;
	
	
	
		   echo "<br/>Avg Pro Score: ".$avg_product_score."  Score:".$actual_product_score;
	
		   
		   
	  
		
		  $other_score  = 0;
		
		
			echo "<br/> Other Score:".$other_score;
	
	
		  $imp_score = $imp_score + $other_score;
	 
		   
	
	
	
		  
		  echo "<br>Total Score: ".$imp_score;
		  
		    
	mysqli_query($conn,"INSERT INTO `temp_filter_customer`(`imp_score`,`account_holder_id`, `customer_type`, `name`, `father_name` , `cibil_score`, `phone_cibil`, `phone_db`, `extra_phone_json`, `mobile`, `email`,`date_time`, `status`)  SELECT $imp_score,`account_holder_id`, `customer_type`, `name`, `father_name`, `cibil_score`, `phone_cibil`, `phone_db`, `extra_phone_json`, `mobile`, `email`,`date_time`, `status` FROM customer where id = $customer_id ");
		  
		  
		  
		  
		  
		  echo "<br/>*********************************<br/>";
		  
		  
		  
		  
		  
		
		  
		  
		  
	  }
	  
	  
	  
	  
	  
	  
	    
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
   /*
	    INSERT INTO `temp_filter_customer`(`account_holder_id`, `customer_type`, `name`, `father_name` , `cibil_score`, `phone_cibil`, `phone_db`, `extra_phone_json`, `mobile`, `email`,`date_time`, `status`)  SELECT
`account_holder_id`, `customer_type`, `name`, `father_name`, `cibil_score`, `phone_cibil`, `phone_db`, `extra_phone_json`, `mobile`, `email`,`date_time`, `status` FROM customer

*/



 

 
  ?>