<?php
session_start();
error_reporting(0);
$user_name=$_SESSION['user_name'];
if(empty($user_name))
 {
 header("location:index.php");
 }
 include("database_connection.php");
$id=$_GET['id'];
$dd=explode('/',$id);
$dump_date11=$dd[0];
$dump_date22=$dd[1];
$dd1=explode('//',$id);
$loan_type=$dd1[1];
$date1=explode('-',$dump_date11);
$d=$date1[0];
$m=$date1[1];
$y=$date1[2];
if($m=="Jan")
{
$mo="01";
}
elseif($m=="Feb")
{
$mo="02";
}
elseif($m=="Mar")
{
$mo="03";
}
elseif($m=="Apr")
{
$mo="04";
}
elseif($m=="May")
{
$mo="05";
}
elseif($m=="Jun")
{
$mo="06";
}
elseif($m=="Jul")
{
$mo="07";
}
elseif($m=="Aug")
{
$mo="08";
}
elseif($m=="sep")
{
$mo="09";
}
elseif($m=="Oct")
{
$mo="10";
}
elseif($m=="Nov")
{
$mo="11";
}
elseif($m=="Dec")
{
$mo="12";
}
else
{
$mo="";
}
$date=$d.'-'.$mo.'-'.$y;
$dump_date=$mo.'-'.$d.'-'.$y;
$date11=explode('-',$dump_date22);
$d1=$date11[0];
$m1=$date11[1];
$y1=$date11[2];
if($m1=="Jan")
{
$mo1="01";
}
elseif($m1=="Feb")
{
$mo1="02";
}
elseif($m1=="Mar")
{
$mo1="03";
}
elseif($m1=="Apr")
{
$mo1="04";
}
elseif($m1=="May")
{
$mo1="05";
}
elseif($m1=="Jun")
{
$mo1="06";
}
elseif($m1=="Jul")
{
$mo1="07";
}
elseif($m1=="Aug")
{
$mo1="08";
}
elseif($m1=="Sep")
{
$mo1="09";
}
elseif($m1=="Oct")
{
$mo1="10";
}
elseif($m1=="Nov")
{
$mo1="11";
}
elseif($m1=="Dec")
{
$mo1="12";
}
else
{
$mo1="";
}
$date2=$d1.'-'.$mo1.'-'.$y1;
$dump_date2=$mo1.'-'.$d1.'-'.$y1;
$sqlu="select id from user_tblsbifi where user_name='$user_name' " ;
$resultu=mysqli_query($conn,$sqlu);
$datau=mysqli_fetch_array($resultu);
$idu=$datau['id'];

  function cleanData($str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }

  // filename for download
  $filename = "Create Report" .$date. '_to_'. $date2.  ".xls";
 	header("Content-Disposition: attachment; filename=\"$filename\"");
  	header("Content-Type: application/vnd.ms-excel");
  $flag = false;
 ?><table border="1">
	<tr bgcolor="#488AC7"> 
		<th><font color="white">AgencyCode</font></th>
		<th><font color="white">App No</font></th>
		<th><font color="white">BankCode</font></th>
		<th><font color="white">Type of Loan</font></th>
		<th><font color="white">Applicant Type</font></th>
		<th><font color="white">FI Type</font></th>
		<th><font color="white">Applicant Name</font></th>
		<th><font color="white">Address</font></th>
		<th><font color="white">Pincode</font></th>
		<th><font color="white">MobileNumber</font></th>
		<th><font color="white">LoanAmount</font></th>
		<th><font color="white">Create Date</font></th>
		<th><font color="white">Create Time</font></th>
		<th><font color="white">Visit Date</font></th>
		<th><font color="white">Visit Time</font></th>
		<th><font color="white">Status</font></th>
		<th><font color="white">Sub Status</font></th>
		<th><font color="white">Visit Remarks</font></th>
	</tr><?php
	
	$sql2="SELECT * FROM sbialloc_tbl  WHERE dump_date between '$dump_date' and '$dump_date2' and promo_code='$idu' and loan_type like'%$loan_type%' order by id asc ";
	$result2=mysqli_query($conn,$sql2);	
	$i=1;
	while($data2=mysqli_fetch_array($result2))
	{	
	$id=$data2['id'];
	if($data2['app_no']=='')
	{
	$sql22="select app_no,status from allocation_tbl where sbiref_id='$id' ";
	$result22=mysqli_query($conn,$sql22);
	$data22=mysqli_fetch_array($result22);
	}
	else
	{
	}
	if(strlen($data2['app_no'])>0)
	{
	$app_no=$data2['app_no'];
	}
	else
	{
	$app_no=$data22['app_no'];
	}
	
	
	if($data2['status']=="1")
	{
	$status="In Process";
	}
	elseif($data2['status']=="0" and $data2['case_status']=='')
	{
	$status="InProgress - DataVerified";
	}
	elseif($data22['status']=="5")
	{
	$status="InProgress - FieldVerified";
	}
	elseif($data2['final_status']=="Positive" or $data2['final_status']=="POSITIVE")
	{
	$p=$data2['final_status'];
	$tt="Verified - Address and Details Confirmed";
	$status=$p.' / '.$tt;
	}
	elseif($data2['reg_no']=="Positive" or $data2['reg_no']=="POSITIVE")
	{
	$p=$data2['reg_no'];
	$tt="Verified - Address and Details Confirmed";
	$status=$p.' / '.$tt;
	}
	elseif($data2['final_status']=="Positive" or $data2['final_status']=="POSITIVE")
	{
	$p=$data2['final_status'];
	$tt="Verified - Address and Details Confirmed";
	$status=$p.' / '.$tt;
	}
	elseif($data2['final_status']=="Negative" or $data2['final_status']=="NEGATIVE")
	{
	$n=$data2['final_status'];
	$tt="Verified - Any Other Reason (Not covered by above)";
	$status=$n.' / '.$tt;
	}
	elseif($data2['reg_no']=="Negative" or $data2['reg_no']=="NEGATIVE")
	{
	$n=$data2['reg_no'];
	$tt="Verified - Any Other Reason (Not covered by above)";
	$status=$n.' / '.$tt;
	}
	elseif($data2['final_status']=="Refer" or $data2['final_status']=="REFER" or $data2['final_status']=="Recommended" or $data2['final_status']=="RECOMMENDED")
	{
	$n=$data2['final_status'];
	$tt="Verified - Any Other Reason (Not covered by above)";
	$status=$n;
	}
	elseif($data2['reg_no']=="Refer" or $data2['reg_no']=="REFER" or $data2['reg_no']=="Recommended" or $data2['reg_no']=="RECOMMENDED")
	{
	$n=$data2['reg_no'];
	$tt="Verified - Any Other Reason (Not covered by above)";
	$status=$n;
	}
	else
	{
	$status='';
	}
	if(strlen($data2['supervisor_remarks'])>0)
	{
	$supervisor_remarks=$data2['supervisor_remarks'];
	}
	elseif(strlen($data2['remarks1'])>0)
	{
	$supervisor_remarks=$data2['remarks1'];
	}
	else
	{
	 $supervisor_remarks="";
	}
	$agencycode=$user_name.'-'.$data2['id'];
	?>
	<tr  >
	<td><?php echo trim(strtoupper($agencycode));?></td>
	<td><?php echo trim(strtoupper($app_no));?></td>	
	<td><?php echo trim($data2['case_id']);?></td>
	<td><?php echo trim($data2['loan_type']);?></td>	
	<td><?php echo trim($data2['appco_applicant']);?></td>
	<td><?php echo trim($data2['type']);?></td>
	<td><?php echo trim($data2['name']);?></td>
	<td><?php echo trim($data2['address1']);?></td>
	<td><?php echo trim($data2['pincode']);?></td>
	<td><?php echo trim($data2['mobile_no']);?></td>
	<td><?php echo trim($data2['loan_amount']);?></td>
	<td><?php echo trim($data2['entry_date']);?></td>
	<td><?php echo trim($data2['entry_time']);?></td>	
	<td><?php echo trim($data2['visit_date']);?></td>
	<td><?php echo trim($data2['visit_time']);?></td>
	<td><?php echo trim($status);?></td>
	<td><?php echo trim($substatus);?></td>
	<td><?php echo trim($supervisor_remarks);?></td>
	</tr>
	<?php $i++; 
	} ?>
	 
</table>

