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


 if(isset($_SESSION['loan_crm_username']) && isset($_SESSION['loan_crm_password']))
{
	
		$table_fieldsQ= mysqli_query($conn,"select *from `table_fields` where `table_name` = 'leads' ");
		$table_fieldsData = mysqli_fetch_assoc($table_fieldsQ);
		
		
		
	
	?>
	
	
<!DOCTYPE html>
 
<html lang="en" class="light-style layout-navbar-fixed layout-menu-fixed " dir="ltr" data-theme="theme-bordered" data-assets-path="../assets/" data-template="vertical-menu-template-bordered">
 
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no, minimum-scale=1.0, maximum-scale=1.0" />

    <title>CRM</title>
     
    <meta name="description" content="Start your development with a Dashboard for Bootstrap 5" />
    <meta name="keywords" content="dashboard, bootstrap 5 dashboard, bootstrap 5 design, bootstrap 5">
    <!-- Canonical SEO -->
    <link rel="canonical" href="https://1.envato.market/frest_admin">
    
    <!-- Favicon -->
    <link rel="icon" type="image/x-icon" href="https://pixinvent.com/demo/frest-clean-bootstrap-admin-dashboard-template/assets/img/favicon/favicon.ico" />

    <!-- Fonts -->
    <link rel="preconnect" href="https://fonts.googleapis.com/">
    <link rel="preconnect" href="https://fonts.gstatic.com/" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=IBM+Plex+Sans:ital,wght@0,300;0,400;0,500;0,600;0,700;1,300;1,400;1,500;1,600;1,700&amp;family=Rubik:ital,wght@0,300;0,400;0,500;0,600;0,700;1,300;1,400;1,500;1,600;1,700&amp;display=swap" rel="stylesheet">

    <!-- Icons -->
    <link rel="stylesheet" href="../assets/vendor/fonts/boxicons.css" />
    <link rel="stylesheet" href="../assets/vendor/fonts/fontawesome.css" />
    <link rel="stylesheet" href="../assets/vendor/fonts/flag-icons.css" />

    <!-- Core CSS -->
    <link rel="stylesheet" href="../assets/vendor/css/rtl/core.css" class="template-customizer-core-css" />
    <link rel="stylesheet" href="../assets/vendor/css/rtl/theme-bordered.css" class="template-customizer-theme-css" />
    <link rel="stylesheet" href="../assets/css/demo.css" />

    <!-- Vendors CSS -->
    <link rel="stylesheet" href="../assets/vendor/libs/perfect-scrollbar/perfect-scrollbar.css" />
    <link rel="stylesheet" href="../assets/vendor/libs/typeahead-js/typeahead.css" />
    <link rel="stylesheet" href="../assets/vendor/libs/datatables-bs5/datatables.bootstrap5.css">
<link rel="stylesheet" href="../assets/vendor/libs/datatables-responsive-bs5/responsive.bootstrap5.css">
<link rel="stylesheet" href="../assets/vendor/libs/datatables-buttons-bs5/buttons.bootstrap5.css">



   
  <link rel="stylesheet" href="../assets/vendor/libs/select2/select2.css" />
  
  
  
	 <link rel="stylesheet" href="../assets/vendor/libs/toastr/toastr.css" />
	 <link rel="stylesheet" href="../assets/vendor/libs/animate-css/animate.css" />

<link rel="stylesheet" href="../assets/vendor/libs/sweetalert2/sweetalert2.css" />

	
	
	
    <!-- Page CSS -->
    
    <!-- Helpers -->
    <script src="../assets/vendor/js/helpers.js"></script>

 <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
   
   
    <!--! Template customizer & Theme config files MUST be included after core stylesheets and helpers.js in the <head> section -->
    <!--? Template customizer: To hide customizer set displayCustomizer value false in config.js.  -->
     <!--? Config:  Mandatory theme config file contain global vars & default theme options, Set your preferred theme option in this file.  -->
    <script src="../assets/js/config.js"></script>
    
    <!-- Global site tag (gtag.js) - Google Analytics -->
    <script async="async" src="https://www.googletagmanager.com/gtag/js?id=GA_MEASUREMENT_ID"></script>
    <script>
    window.dataLayer = window.dataLayer || [];

    function gtag() {
      dataLayer.push(arguments);
    }
    gtag('js', new Date());
    gtag('config', 'GA_MEASUREMENT_ID');
    </script>
    <!-- Custom notification for demo -->
    <!-- beautify ignore:end -->

</head>
<body>

	   
	    <?php
 require("phpexcel/php-excel-reader/excel_reader2.php");
	require('phpexcel/SpreadsheetReader.php');
	
	$uploded=0;
 
 $insert_count=0;
 $duplicate_count=0;
 $update_count=0;
 
 $total_uploded = 0;
 $error_rows = array();
			
   if(isset($_POST['upload_excel']))
   {
	   $filename=$_FILES['excelfile']['name'];
	   move_uploaded_file($_FILES['excelfile']['tmp_name'],'excel/'.$filename);
	   
	   date_default_timezone_set('UTC');

	$StartMem = memory_get_usage();


     $headerArray = array();

	try
	{
		$Filepath="excel/".$filename;
		$Spreadsheet = new SpreadsheetReader($Filepath);
		$BaseMem = memory_get_usage();
		
		$date_time = date('Y-m-d H:i:s');
					

		$Sheets = $Spreadsheet -> Sheets();
		
		
		
	            $save = 1;
				$is_for_temp_call = 0;
				$is_replace = 0;
				$is_delete = 0;
				$is_update_lead = 0;
				
				
				if(isset($_POST['reassign']))
				{
					if($_POST['reassign'] == 'reassign')
					{
						$is_replace = 1;
						$save = 0;
					}
				}
				
				
				if(isset($_POST['is_for_temp_call']))
				{
					if($_POST['is_for_temp_call'] == 'is_for_temp_call')
					{
						$is_for_temp_call = 1;
						$save = 0;
					}
				}
				
				
				
				if(isset($_POST['delete_account']))
				{
					if($_POST['delete_account'] == 'delete_account')
					{
						$is_delete = 1;
						$save = 0;
					}
				}
				
				
				
				
				
				//  Not delete retain customer before upload
				
				
				
				
				
				

		foreach ($Sheets as $Index => $Name)
		{
			

			$Time = microtime(true);

			$Spreadsheet -> ChangeSheet($Index);
			
			
            $line=0;
			
			$uploded = 1;
			
			foreach ($Spreadsheet as $Key => $Row)
			{
				$line++;
				
				
				
				
					/**  Get Excel Headers ***/
					
					if($line==1)
					{
						if ($Row)
						{
							
							$g=0;
							foreach($Row as $header_name)
							{
							    $title = strtolower($header_name);
								$title = str_replace(' ','_',$title);
								$title = str_replace('-','_',$title);
								$title = str_replace('/','_',$title);
								$title = str_replace('#','no',$title);
								 
								
								
							  $headerArray[$g]  = $title;
							  
							  $g++;
							}
							
						}
				
				
				   //  echo implode(',',$headerArray);
						
					}
					
					
				 
					
					
					
					
				
				if($line!=1) {
					 
					
				if ($Row)
				{
					
			
			//$data = explode(',',$table_fieldsData['column_names']);
                                
					  $p=0;
				
				$RECORD=array();
				 foreach($headerArray as $field)
					  {    
											 
					if($field!='id' && $field!='date_time' && $field!='status' )
					  {  
					
					
					$string = trim(preg_replace('/\s\s+/', ' ', $Row[$p]));
					  
					$RECORD[$field] = stripslashes($string);
					
					  $p++;
					
					  }
					  
					  }
				
				
			
				
				
				 
				// replace logic
				if($is_replace == 1)
				{
					
					$account_holder_id =  $RECORD['accountholderid'];
					$advisor =  $RECORD['advisor'];
					
					mysqli_query($conn,"update customer set advisor = '$advisor' where account_holder_id = '$account_holder_id'; ");
					mysqli_query($conn,"update temp_filter_customer set advisor = '$advisor', old_advisor = '$advisor' where account_holder_id = '$account_holder_id'; ");
					
					//echo "update customer set advisor = '$advisor' where account_holder_id = '$account_holder_id' ; ";
					
				}
				
				// replace logic
				if($is_for_temp_call == 1)
				{
					
					$account_holder_id =  $RECORD['accountholderid'];
					$advisor =  $RECORD['advisor'];
					
					mysqli_query($conn,"update customer set advisor = '$advisor' where account_holder_id = '$account_holder_id'; ");
					mysqli_query($conn,"update temp_filter_customer set advisor = '', old_advisor = '$advisor' ,attempt='0', call_count=0   where account_holder_id = '$account_holder_id'; ");
					
					//echo "update customer set advisor = '$advisor' where account_holder_id = '$account_holder_id' ; ";
					
				}
				
				
				
				
				// delete logic
				if($is_delete == 1)
				{
					
					$account_holder_id =  $RECORD['accountholderid'];
					 
					 
					mysqli_query($conn,"delete from leads where account_holder_id = '$account_holder_id'  ");
					mysqli_query($conn,"delete from customer where account_holder_id = '$account_holder_id'  ");
					mysqli_query($conn,"delete from temp_filter_customer where account_holder_id = '$account_holder_id'  ");
					
			 
				}
				
				
				
				
				
				
				 
				
				/*
				if($is_update_lead == 1)
				{
					 
					
					$lead_data = json_encode($RECORD,JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE);
					
					$account_id = $RECORD['accountid'];
					
					
				     mysqli_query($conn,"update `leads` set lead_data = '$lead_data' where account_id = '$account_id' ");
					
				   
				}
				
				
				*/
				
					 

               if($save == 1)
			   {
				   
				   
				   $lead_data = json_encode($RECORD,JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE);
				   
				   $lead_data = preg_replace("!\r?\n!", "", $lead_data);


				   /*
				    $lastQ = mysqli_query($conn,"select *from leads  order by id desc limit 1");
				   $lastData = mysqli_fetch_assoc($lastQ);
				   $last_id = str_replace('#A00','',$lastData['lead_no']);
				   if($last_id =='')
				   {
					   $last_id = 0;
				   }
				   
				   $next_id = $last_id + 1;
				   
				   
				   $INSERT_DATA['lead_no'] = '#A00'.$next_id;
				   
				   */
				   
				   
				   
				   $INSERT_DATA['account_holder_id'] = $RECORD['accountholderid'];
				   $INSERT_DATA['account_id'] = $RECORD['accountid'];
				   $INSERT_DATA['account_holder_name'] = $RECORD['accountholdername'];
				   $INSERT_DATA['loan_amount'] = $RECORD['loanamount'];
				   $INSERT_DATA['tos'] = $RECORD['tos'];
  				   $INSERT_DATA['lead_data'] = $lead_data;
				   
				   $INSERT_DATA['lead_status'] = 'new';
				   
				   $account_id = $RECORD['accountid'];
				   $advisor = $RECORD['advisor'];
				   $customer_type = $RECORD['customertype'];
				   
				    
					
					/*
					// check if advisor is there
					 $advisorQ = mysqli_query($conn,"select *from `advisors` where name = '$advisor' ");
					 
					 if(mysqli_num_rows($advisorQ) == 0)
					 {
						$advisor = '';
 						
					 }
					 else
					 {
						$INSERT_DATA['lead_status'] = 'assigned'; 
					 }
					
					*/
					if($advisor!='')
					{
						$INSERT_DATA['lead_status'] = 'assigned'; 
					}
					
				   
				   
				   
				   
				   // check if new account holder then insertTable
				   
				   $account_holder_id = $RECORD['accountholderid'];
				   
				    $customerQ = mysqli_query($conn,"select *from `customer` where account_holder_id = '$account_holder_id' ");
					
					if(mysqli_num_rows($customerQ) == 0)
					{
						$CUSTOMER_DATA['account_holder_id'] =  $RECORD['accountholderid'];
						$CUSTOMER_DATA['name'] =  $RECORD['accountholdername'];
						$CUSTOMER_DATA['father_name'] =  $RECORD['fathersfirstname'].' '.$RECORD['fatherslastname'];
						$CUSTOMER_DATA['email'] =  $RECORD['email'];
						$CUSTOMER_DATA['mobile'] =  $RECORD['phonedb'];
						$CUSTOMER_DATA['cibil_score'] =  $RECORD['cibilscore'];
						$CUSTOMER_DATA['phone_cibil'] =  $RECORD['phonecibil'];
						$CUSTOMER_DATA['advisor'] =  $advisor;
						$CUSTOMER_DATA['customer_type'] =  $customer_type;
						
						insertTable($conn,'customer',$CUSTOMER_DATA,'add_submit');
					  
					}
				   
				   
				   
				   
				   
				   
				   
				   
				   $alreadLoanQ = mysqli_query($conn,"select *from `leads` where account_id = '$account_id' ");
					
					if(mysqli_num_rows($alreadLoanQ) == 0)
					{
				   
				   
				   
				   
				   insertTable($conn,'leads',$INSERT_DATA,'add_submit');
				   
				   
				    $total_uploded++;
					
					}
				   
				   
			   }
			   
			   
			   
			   
			   
			   
			   
			   
			   
			   
			   
			   
			   
	 
					
				}
				}
				
			}
		
			
		}
		
		
		if(count($error_rows) == 0)
		{
		
		$_SESSION['save_message'] = "Total uploaded records: $total_uploded ";
		$import_description = "Total uploaded records: $total_uploded ";
		}
		else
		{
			$_SESSION['save_message'] = "Total uploaded records: $total_uploded , Error In Rows:".implode(',',$error_rows);
		$import_description = "Total uploaded records: $total_uploded , Error In Rows:".implode(',',$error_rows);
		}
		
		
		
		
		
	}
	catch (Exception $E)
	{
		die("Invalid Excel Format");
	}
	   
	   
	   
   }
  
 
 
 ?>




  <!-- Layout wrapper -->
<div class="layout-wrapper layout-content-navbar  ">
  <div class="layout-container">

    
    




<!-- Menu -->

<?php include('left_sidebar.php'); ?>

<!-- / Menu -->

    

    <!-- Layout container -->
    <div class="layout-page">
      
      



<!-- Navbar -->

  
<?php include('header.php'); ?>



  
<!-- / Navbar -->
   
   <script>
   $(document).ready(function(){

     $('#myTable').DataTable( {
			  "ordering": false,
        dom: 'Bfrtip',

        buttons: 
		[
			
	  	  
	  {
        extend: 'copy',
       
		
      } , 
	  {
        extend: 'csv',
       
		
      } ,			
	  {
        extend: 'excel',
      
		
      },			
	  {
        extend: 'pdf',
       
		
      } , {
        extend: 'print',
		       

		
      } 
	  
	  
	  
	  ]
    } );
});
   </script>

      

      <!-- Content wrapper -->
      <div class="content-wrapper">

          <!-- Content -->
        
          <div class="container-xxl flex-grow-1 container-p-y">
            
            
<h4 class="py-3 breadcrumb-wrapper mb-4">
  <span class="text-muted fw-light">Leads /</span> List
</h4>

 <?php if(isset($_SESSION['save_message'])) { ?> <div style="width:100%;" class="alert alert-success"><?php echo $_SESSION['save_message']; unset($_SESSION['save_message']); ?></div> <?php  }  ?>
			
			
			 <?php if($_SESSION['login_as'] == 'admin') { ?>
		
     
  
     <form action="" enctype="multipart/form-data" method="post" class="row" >
	 
		  <div style="margin-left:20px;" class="form-check mt-3">
            <input class="form-check-input" name="reassign" type="checkbox" value="reassign" id="reassign">
            <label class="form-check-label" for="reassign">
              Reassign  |  
            </label>
			
			<input classs="form-check-input" name="is_for_temp_call" type="checkbox" value="is_for_temp_call" id="is_for_temp_call">
			  <label class="form-check-label" for="is_for_temp_call">
               Reassign For Auto Call |  
            </label>
			
			<input  name="delete_account" type="checkbox" value="delete_account" id="delete_account" />
            <label class="form-check-label" for="delete_account">Delete</label>
			
			
			
			<div style="float:right;margin-top: -40px;">
			<a  target="_BLANK" class="btn btn-warning" href="outstanding_slab_save.php" >Outstanding Slab</a>
			<a target="_BLANK" class="btn btn-warning"  href="filter_initial_data.php" >Filter Initial Data</a>
			 </div>
          </div>
		  
	      
		 <div class="input-group">
          <input type="file" class="form-control" name="excelfile"  id="inputGroupFile04" aria-describedby="inputGroupFileAddon04" aria-label="Upload" required >
          <button class="btn btn-primary" type="submit" name="upload_excel" id="inputGroupFileAddon04"><span class="fa fa-upload"></span>&nbsp; Upload</button>
        </div>
		 
	   </form>
	   
	   
			 <?php } ?>
					   
	   <br/>
					   
	<!-- Invoice List Table -->
	<div class="card">


   <script>
 
 function deleteSelected(table)
 {
	 
	 if(confirm('Are you sure?'))
	 {
		   var dataArr = [];
		   
		 $("input:checkbox[name=delete_checkbox]:checked").each(function(){
    dataArr.push($(this).val());
});
		 
		 
		  var jsonString = JSON.stringify(dataArr);
		  
		  
		   if(dataArr.length == 0)
			  
			  {
				  toastr.warning("Please Select Record!"); 
			  }
			  else {
				  
				  
		    $.ajax({
      type: "POST",
      url: 'delete_records_ajax.php',
	    data: {record_ids:jsonString,table:table},
      success: function(data) {
	
	    // toastr.warning(data.trim()); 
		  
		 window.location="";
		 
		
		 }
		  // <- this turns it into synchronous

		  }
	   );
			  }
		  
		  
	 }
	 
	 
 }
 
 
 
 function selectAll()
 {
	 
	 if($("input:checkbox[name=delete_checkbox]").prop('checked') == true)
	 {
		  $("input:checkbox[name=delete_checkbox]").prop('checked', false);
	 }
	 
	 else{
	 $("input:checkbox[name=delete_checkbox]").prop('checked', true);
	 }
 }


</script>	





			<div class="table-responsive" style="background:white;">
									
									
								 <!---  TABLE HERE  -->
  
<?php $pg=0; if(isset($_GET['page'])) { $pg=$_GET['page']; } ?>
  
  <script>
  
  $( document ).ready(function() {
  // Handler for .ready() called.
  
  fetchAllParty(<?php echo $pg; ?>,'','');
  
});
   </script>
  
  <script>
  
  
 
 function fetchAllParty(page,keyword,key_for){
	  
	//alert('a');
	
		
	 $('#add-row').addClass('loading');
	 
    $.ajax({
      type: "GET",
      url: 'get_leads_table_ajax.php',
      data: ({page:page,keyword:keyword,key_for:key_for}),
      success: function(data) {
		
			document.getElementById('table_data_here').innerHTML=data;
			
			
				var c=0;
				
				 
  
			
			
		 }
		  // <- this turns it into synchronous

		  }
	   );
	   
	}


  
  </script>













					   

  
  <p align="right"> Search: <input value="<?php if(isset($_GET['keyword'])) {  echo $_GET['keyword'];} ?>" onkeyup="fetchAllParty(0,this.value,'');" type="text" id="search_party"/></p>
		   
		   
         <div class="card-body1 loading">
		 <div id="table_data_here">
		 
		  <table class="loading" ><tr><td></td></tr></table>
		  
		  
		   </div>
		 </div>	
		 
 
					   
 
</div>


            
          </div>
          <!-- / Content -->
        
          
 
          
      
         </div>
        <!-- Content wrapper -->
      </div>
      <!-- / Layout page -->
    </div>

    
    
    
  </div>
  <!-- / Layout wrapper -->

 


  <!-- Core JS -->
  <!-- build:js assets/vendor/js/core.js -->
  <script src="../assets/vendor/libs/jquery/jquery.js"></script>
  <script src="../assets/vendor/libs/popper/popper.js"></script>
  <script src="../assets/vendor/js/bootstrap.js"></script>
  <script src="../assets/vendor/libs/perfect-scrollbar/perfect-scrollbar.js"></script>
  <script src="../assets/vendor/libs/hammer/hammer.js"></script>
  <script src="../assets/vendor/libs/i18n/i18n.js"></script>
  <script src="../assets/vendor/libs/typeahead-js/typeahead.js"></script>
  
  <script src="../assets/vendor/js/menu.js"></script>
    <script src="../assets/vendor/libs/select2/select2.js"></script>



    <script src="../assets/vendor/libs/toastr/toastr.js"></script>

     <script src="../assets/js/ui-toasts.js"></script>
	 <script src="../assets/vendor/libs/sweetalert2/sweetalert2.js"></script>


	 
  <!-- endbuild -->

  <!-- Vendors JS -->
  <script src="../assets/vendor/libs/moment/moment.js"></script>
<script src="../assets/vendor/libs/datatables/jquery.dataTables.js"></script>
<script src="../assets/vendor/libs/datatables-bs5/datatables-bootstrap5.js"></script>
<script src="../assets/vendor/libs/datatables-responsive/datatables.responsive.js"></script>
<script src="../assets/vendor/libs/datatables-responsive-bs5/responsive.bootstrap5.js"></script>
<script src="../assets/vendor/libs/datatables-buttons/datatables-buttons.js"></script>
<script src="../assets/vendor/libs/datatables-buttons-bs5/buttons.bootstrap5.js"></script>

  <!-- Main JS -->
  <script src="../assets/js/main.js"></script>

  <!-- Page JS -->
  <script src="../assets/js/app-invoice-list.js"></script>
</body>

 
</html>
  <?php  }  else { header("Location:login.php");  } ?>