<?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">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'  order by id asc " ;
	$result2=mysqli_query($conn,$sql2);
	
	$i=1;
	while($data2=mysqli_fetch_array($result2))
	{
	
	$case_id=$data2['case_id'];
	$sqlapp="select id,app_no,status from allocation_tbl where case_id='$case_id'  and (appco_applicant='Applicant' or  appco_applicant='APPLICANT') and bank_name='SBI AUTO LOAN' and type='RV' order by id asc";
	$resultapp=mysqli_query($conn,$sqlapp);
	$dataapp=mysqli_fetch_array($resultapp);
	$sqlappo="select id,app_no,status from allocation_tbl where case_id='$case_id'  and (appco_applicant='Applicant' or  appco_applicant='APPLICANT') and bank_name='SBI AUTO LOAN' and type='OV' order by id asc";
	$resultappo=mysqli_query($conn,$sqlappo);
	$dataappo=mysqli_fetch_array($resultappo);
	$sqlcoapp="select id,status,app_no from allocation_tbl where case_id='$case_id' and (appco_applicant='Co-Applicant'  or appco_applicant='CO-APPLICANT')  and bank_name='SBI AUTO LOAN' and type='OV' order by id asc";
	$resultcoapp=mysqli_query($conn,$sqlcoapp);
	$datacoapp=mysqli_fetch_array($resultcoapp);
	$sqlcoappr="select id,status,app_no from allocation_tbl where case_id='$case_id' and (appco_applicant='Co-Applicant'  or appco_applicant='CO-APPLICANT')  and bank_name='SBI AUTO LOAN' and type='RV' order by id asc";
	$resultcoappr=mysqli_query($conn,$sqlcoappr);
	$datacoappr=mysqli_fetch_array($resultcoappr);

	$sqlgr="select id,status,app_no from allocation_tbl where case_id='$case_id' and (appco_applicant='Guarantor'  or appco_applicant='GUARANTOR') and bank_name='SBI AUTO LOAN'   order by id asc";
	$resultgr=mysqli_query($conn,$sqlgr);
	$datagr=mysqli_fetch_array($resultgr);
	////////////////////
	
	$sqlgr2="select id,status,app_no from allocation_tbl where case_id='$case_id' and (appco_applicant='Na'  or appco_applicant='NA') and bank_name='SBI AUTO LOAN'  order by id asc";
	$resultgr2=mysqli_query($conn,$sqlgr2);
	$datagr2=mysqli_fetch_array($resultgr2);
	if($data2['appco_applicant']=="Applicant" or $data2['appco_applicant']=="APPLICANT" and ($data2['type']=="RV"))
	{	
	$status2=$dataapp['status'];
	$app_no=$dataapp['app_no'];
	$idd=$dataapp['id'];
	}
	elseif($data2['appco_applicant']=="Applicant" or $data2['appco_applicant']=="APPLICANT" and ($data2['type']=="OV"))
	{	
	$status2=$dataappo['status'];
	$app_no=$dataappo['app_no'];
	$idd=$dataappo['id'];
	}
	elseif($data2['appco_applicant']=="Co-Applicant" or $data2['appco_applicant']=="CO-APPLICANT" and ($data2['type']=="OV"))
	{
	$status2=$datacoapp['status'];
	$app_no=$datacoapp['app_no'];
	$idd=$datacoapp['id'];
	}
	elseif($data2['appco_applicant']=="Co-Applicant" or $data2['appco_applicant']=="CO-APPLICANT" and ($data2['type']=="RV"))
	{
	$status2=$datacoappr['status'];
	$app_no=$datacoappr['app_no'];
	$idd=$datacoappr['id'];
	}
	elseif($data2['appco_applicant']=="Guarantor" or $data2['appco_applicant']=="GUARANTOR")
	{
	$status2=$datagr['status'];
	$app_no=$datagr['app_no'];
	$idd=$datagr['id'];
	}
	elseif($data2['appco_applicant']=="Na" or $data2['appco_applicant']=="NA")
	{
	
	$status2=$datagr2['status'];
	$app_no=$datagr2['app_no'];
	$idd=$datagr2['id'];
	}
	//echo $status;
	if($data2['status']=="1")
	{
	$status="InProgress";
	}
	
	elseif($status2=="5")
	{
	$status="InProgress - FieldVerified";
	}
	elseif($status2=="4")
	{
	$status="InProgress - DataVerified";
	}
	/*elseif($status']=="0")
	{
	$status="Duplicate Reference No";
	}*/
	elseif($status2=="0" and $data2['type']=="RV" )
	{
	$idd=$idd;
	$sqlrv="select final_status,visit_date,visit_time,supervisor_remarks from res_verification where ref_id='$idd' ";
	$resultrv=mysqli_query($conn,$sqlrv);
	$datarv=mysqli_fetch_array($resultrv);
	if($datarv['final_status']=="Positive" or $datarv['final_status']=="POSITIVE")
	{
	$p=$datarv['final_status'];
	$tt="Verified - Address and Details Confirmed";
	$status=$p.' / '.$tt;
	$visit_date=$datarv['visit_date'];
	$visit_time=$datarv['visit_time'];
	$supervisor_remarks=$datarv['supervisor_remarks'];
	}
	elseif($datarv['final_status']=="Negative" or $datarv['final_status']=="NEGATIVE")
	{
	$n=$datarv['final_status'];
	$tt="Verified - Any Other Reason (Not covered by above)";
	$status=$n.' / '.$tt;
	$visit_date=$datarv['visit_date'];
	$visit_time=$datarv['visit_time'];
	$supervisor_remarks=$datarv['supervisor_remarks'];
	}
	elseif($datarv['final_status']=="Refer" or $datarv['final_status']=="REFER" or $datarv['final_status']=="Recommended" or $datarv['final_status']=="RECOMMENDED")
	{
	$n=$datarv['final_status'];
	$tt="Verified - Any Other Reason (Not covered by above)";
	$status=$n;
	$visit_date=$datarv['visit_date'];
	$visit_time=$datarv['visit_time'];
	$supervisor_remarks=$datarv['supervisor_remarks'];
	}
	else
	{
	}
	}
	elseif($status2=="0" and $data2['type']=="OV" )
	{
	$idd=$idd;
	$sqlrv="select final_status,visit_date,visit_time,supervisor_remarks from bvsalaried where ref_id='$idd' ";
	$resultrv=mysqli_query($conn,$sqlrv);
	$datarv=mysqli_fetch_array($resultrv);
	if($datarv['final_status']=="Positive" or $datarv['final_status']=="POSITIVE")
	{
	$p=$datarv['final_status'];
	$tt="Verified - Address and Details Confirmed";
	$status=$p.' / '.$tt;
	$visit_date=$datarv['visit_date'];
	$visit_time=$datarv['visit_time'];
	$supervisor_remarks=$datarv['supervisor_remarks'];
	}
	elseif($datarv['final_status']=="Negative" or $datarv['final_status']=="NEGATIVE")
	{
	$n=$datarv['final_status'];
	$tt="Verified - Any Other Reason (Not covered by above)";
	$status=$n.' / '.$tt;
	$visit_date=$datarv['visit_date'];
	$visit_time=$datarv['visit_time'];
	$supervisor_remarks=$datarv['supervisor_remarks'];
	}
	elseif($datarv['final_status']=="Refer" or $datarv['final_status']=="REFER" or $datarv['final_status']=="Recommended" or $datarv['final_status']=="RECOMMENDED")
	{
	$n=$datarv['final_status'];
	$tt="Verified - Any Other Reason (Not covered by above)";
	$status=$n;
	$visit_date=$datarv['visit_date'];
	$visit_time=$datarv['visit_time'];
	$supervisor_remarks=$datarv['supervisor_remarks'];
	}
	else
	{
	}
	}
	else
	{
	$status="";
	$visit_date="";
	$visit_time="";
	$supervisor_remarks="";
	}	
 ?>
	
	<tr  >
	<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($visit_date);?></td>
	<td><?php echo trim($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>

