<?php
include('db_connect.php');
//error_reporting(0);	
$frm = $_POST['frm'];
if($frm === "get_lead_info"){
	$emp_role = $_POST['emp_role'];
	$emp_cat  = $_POST['emp_cat'];
	$emp_id   = $_POST['emp_id'];
	$lead_status = $_POST['lead_status'];
	$date_info   = $_POST['date'];
	$misc        = $_POST['misc'];
	if($date_info === "T"){
		$date = date("Y-m-d"); 
	}else
	if($date_info === "Y"){
		$date = date("Y-m-d",strtotime("-1 days"));
	}
	if($date_info === "TW"){
		$date = date("Y-m-d",strtotime("+1 days"));
	}else
	if($date_info === "M"){
		$date = date("Y-m");
	}else
	if($date_info === "LM"){
		$today     = date("Y-m-d");
		$datestring= "$today first day of last month";
		$dt=date_create($datestring);
    	$date = $dt->format('Y-m');
	}
	$pro = "";
	$joinqry ="";
	$var="";
	if($misc === "MET"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and met_date like '$date%' and lead_status = '$lead_status'";
	}else
	if($misc === "PMET"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_met_log.met_date like '$date%' and lead_status = '$lead_status' and ospos_met_log.first_met='1' and ospos_lead_type_info.status='1' and (ospos_leads_product.status='1' or ospos_leads_product.status  IS NULL) group by ospos_lead_type_info.lead_id";
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$pro = "left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		$var = ",IFNULL(sum(ospos_leads_product.overall_net_premium),0) as net_premium,ospos_met_log.met_date as first_met";
			}
	else
	if($misc === "RMM"){
		$qry = "(ospos_lead_type_info.created_by = '$emp_id' or ospos_lead_type_info.rm_name = '$emp_id')  and met_date like '$date%' and lead_status = '$lead_status'";
	}else
	if($misc === "PROSPECT"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.ncd like '$date%' and ospos_lead_type_info.prospect_level = '$lead_status' and ospos_lead_type_info.category in ($emp_cat)";
		$pro = "inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		$var = ",IFNULL(ospos_leads_product.overall_net_premium,0) as net_premium";
		
	}else
	if($misc === "HLPROSPECT"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.ncd like '$date%' and ospos_lead_type_info.prospect_level = '$lead_status' and ospos_lead_type_info.category in ($emp_cat)";		
	}else
	if($misc === "PROSPECT_NCD"){
		$qry = "ospos_lead_type_info.category in ($emp_cat) and ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.ncd like '$date%' and ospos_lead_type_info.prospect_level = '$lead_status'";
	}else
	if($misc === "PROSPECT_CR"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.created_date like '$date%' and ospos_lead_type_info.prospect_level = '$lead_status' and ospos_lead_type_info.category in ($emp_cat)";
	}else
	if($misc === "ADMIN"){
		$qry = "lead_status = '9' and met_date like '$date%' and ospos_lead_type_info.prospect_level = '$lead_status'";
	}else
	if($misc === "ADMINNCD"){
		$qry = "lead_status = '9' and ncd like '$date%' and met_date not like '$date%' and ospos_lead_type_info.prospect_level = '$lead_status'";
	}else
	if($misc === "RMMET"){
		$qry = "lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and met_date like '$date%' and ospos_lead_type_info.prospect_level = '$lead_status'";			
	}else
	if($misc === "RMMETTM"){
		$qry = "lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ospos_met_log.met_date like '$date%' and ospos_met_log.first_met='1' and ospos_lead_type_info.prospect_level = '$lead_status' ";	
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$pro = "left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		$var = ",IFNULL(ospos_leads_product.overall_net_premium,0) as net_premium,ospos_met_log.met_date as first_met";		
	}else
	if($misc === "RMTEAMMET"){
		$qry = "lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ospos_met_log.met_date like '$date%' and ncd like '$date%' and ospos_met_log.first_met='1' and ospos_lead_type_info.prospect_level = '$lead_status' ";	
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$pro = "left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		$var = ",IFNULL(ospos_leads_product.overall_net_premium,0) as net_premium,ospos_met_log.met_date as first_met";	
	}else
	if($misc === "RMMETTMLOG"){
		$qry = "lead_status = '9' and prospect_level = '$lead_status' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ospos_lead_type_info.status =  '1' and ospos_leads_product.status =  '1' AND first_met='1' and ospos_leads_product.login_date like '%$date%' and ospos_met_log.met_date like '$date%' group by lead_info_id";
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$pro = "left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		$var = ",sum(ospos_leads_product.overall_net_premium) as net_premium,ospos_met_log.met_date as first_met";	
	}
	else
	if($misc === "RMMETNCDLOG"){
		$qry = "lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id))  and ospos_met_log.first_met='1'  and ospos_met_log.met_date not like '$date%' and ospos_leads_product.login_date like '%$date%' and ospos_lead_type_info.prospect_level = '$lead_status' and ospos_lead_type_info.status = '1' group by lead_info_id";
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$pro = "left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		$var = ",IFNULL(sum(ospos_leads_product.overall_net_premium),0) as net_premium,ospos_met_log.met_date as first_met ";	
	}		
	else
	if($misc === "RMMETNCD"){
		$qry = "lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ospos_met_log.first_met='1' and ncd like '$date%' and ospos_met_log.met_date not like '$date%' and ospos_lead_type_info.prospect_level = '$lead_status' and ospos_lead_type_info.status =  '1'";
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$var = ",ospos_met_log.met_date as first_met";
	}else
	if($misc === "RMTEAMMETNCD"){
		$qry = "lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ncd like '$date%' and ospos_met_log.first_met!='1'  and ospos_lead_type_info.prospect_level = '$lead_status'";
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$var = ",ospos_met_log.met_date as first_met";
	}else
	if($misc === "RMLEAD"){
		// added 30july2018 @gs
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and lead_status = '$lead_status' and ospos_lead_type_info.created_date like '$date%' ";
	}else	
	if($misc === "RMMLEAD"){
	// added 30july2018 @SAT - RM Manager	
	$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
	$qry = "(ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and lead_status = '$lead_status' and first_met='1' and ospos_met_log.met_date like '$date%' ";	 
	$var = ",ospos_met_log.met_date as first_met";	
	}else
	if($misc === "METRMLEAD"){
		// added 30july2018 @gs
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";

		$qry = "ospos_lead_type_info.created_by in ($emp_id) and lead_status = '$lead_status' and first_met='1'  and ospos_met_log.met_date like '$date%' ";
		$var = ",ospos_met_log.met_date as first_met";
	}else
	if($misc === "RMTEAMLEAD"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and lead_status = '$lead_status' and ospos_lead_type_info.created_date like '$date%'";
	}else
	if($misc === "RMLEADNCD"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and lead_status = '$lead_status' and ospos_lead_type_info.created_date not like '$date%' and ospos_lead_type_info.ncd like '$date%'";
	}else
	if($misc === "METRMLEADNCD"){
		// added 02Aug2018 @SAT
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";

		$qry = "ospos_lead_type_info.created_by in ($emp_id) and lead_status = '$lead_status' and first_met='1' and ospos_lead_type_info.created_date not like '$date%' and ospos_lead_type_info.ncd like '$date%' ";
		$var = ",ospos_met_log.met_date as first_met";
	}else
	if($misc === "RMTEAMLEADNCD"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and lead_status = '$lead_status' and ospos_lead_type_info.created_date not like '$date%' and ospos_lead_type_info.ncd like '$date%'";
	}else
	if($misc === "created_date"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.created_date like '$date%' and lead_status = '$lead_status'";
	}else
	if($misc === "METNCD"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and lead_status = '$lead_status' and ospos_lead_type_info.met_date not like '$date%' and ospos_lead_type_info.ncd like '$date%'";
	}else{
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.ncd like '$date%' and lead_status = '$lead_status'";
	}

	$lead_info = mysql_query("SELECT distinct ospos_lead_type_info.lead_id,ospos_lead_type_info.met_date as met_date,ospos_lead_type_info.created_date as cr_date,ospos_lead_type_info.ncd as cr_ncd,ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_lead_type.lead_type as type,ospos_prospect_level.prospect_name as prospect,ospos_people.first_name as crmname,IFNULL(ospos_lead_type_info.expected_value,0) as expected,IFNULL(ospos_lead_type_info.expected_login,0) as expected_log,ospos_lead_type_info.rm_name as rm_name,ospos_lead_type_info.created_by $var FROM ospos_lead_type_info left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id LEFT join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id LEFT join ospos_lead_type on ospos_lead_type.lead_type_id = ospos_lead_type_info.lead_type LEFT join ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id $pro $joinqry where ospos_lead_type_info.status='1' and ospos_lead_type_info.lead_mode='1' and $qry");

	
	$tr_line = "";
	$count = 0;
	$net_premium = 0;
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		$count++;
		$cr_date      = date('d-m-Y', strtotime($lead_info_rslt['cr_date']));
		$met_date     = date('d-m-Y', strtotime($lead_info_rslt['met_date']));
		$cr_ncd       = date('d-m-Y', strtotime($lead_info_rslt['cr_ncd']));
		$name         = $lead_info_rslt['name'];
		$mob          = $lead_info_rslt['mob'];
		$type         = $lead_info_rslt['type'];
		$prospect     = $lead_info_rslt['prospect'];
		$expected     = $lead_info_rslt['expected'];
		$expected_log = $lead_info_rslt['expected'];
		$crmname      = $lead_info_rslt['crmname'];
		$created_by   = $lead_info_rslt['created_by'];
		$rm           = $lead_info_rslt['rm_name'];
	
		
		
			$rm_info = mysql_query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$rm'");
			$rm_name = mysql_fetch_array($rm_info);
			$first_name = $rm_name['first_name'];
			if(!$first_name){
				$first_name = $crmname;				
			}
			if(($misc === "PMET") || ($misc === "PROSPECT") || ($misc === "RMMETTM") || ($misc === "RMTEAMMET") || ($misc === "METRMLEAD") || ($misc === "RMMETNCDLOG") || ($misc === "RMMETTMLOG") || ($misc === "RMMETNCD") || ($misc === "METRMLEADNCD")){
				$first_met	  = date('d-m-Y', strtotime($lead_info_rslt['first_met']));
			}
			
		if(($misc === "PMET") || ($misc === "PROSPECT") || ($misc === "RMMETTM") || ($misc === "RMTEAMMET") || ($misc === "RMMETNCDLOG") || ($misc === "RMMETTMLOG")){
			$net_premium= $lead_info_rslt['net_premium'];
		}
		if($met_date === "01-01-1970"){
			$met_date = "";
			$first_met ="";
		}
		
		$report_info = mysql_query("SELECT reporting from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$created_by'");
				$tl_id = mysql_fetch_array($report_info);
				$tl = $tl_id['reporting'];
				
			$tl_info = mysql_query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$tl'");
			$tl_name = mysql_fetch_array($tl_info);
			$manager_name = $tl_name['first_name'];
			
		if($emp_cat === "3"){	
		
			$tr_line  .= "<tr>
						<td style='text-align:center !important;'>$count</td>
						<td>$cr_date</td>
						<td>$name</td>
						<td>$mob</td>
						<td>$type</td>
						<td>$prospect</td>
						<td>$expected</td>
						<td>$cr_ncd</td>
						<td>$net_premium</td>
						<td>$manager_name</td>					
						<td>$crmname</td>
					</tr>";
		}
		else if($emp_role === "6"){
		 if($misc === "MET")
		 	{
		 $tr_line  .= "<tr>
						<td style='text-align:center !important;'>$count</td>
						<td>$first_name</td>
						<td>$name</td>			
						<td>$mob</td>
						<td>$type</td>
						<td>$prospect</td>
						<td>$met_date</td>	
						<td>$first_met</td>											
						<td>$manager_name</td>
						<td>$crmname</td>
					</tr>";
			}else{
			$tr_line  .= "<tr>
						<td style='text-align:center !important;'>$count</td>
						<td>$cr_date</td>
						<td>$name</td>			
						<td>$mob</td>
						<td>$type</td>
						<td>$prospect</td>
						<td>$cr_ncd</td>												
						<td>$manager_name</td>
						<td>$crmname</td>
					</tr>";
			}
		}else{
		if(($emp_role === "9") || ($emp_role === "5")) {
			$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$first_name</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$type</td>
					<td>$prospect</td>
					<td>$expected_log</td>
					<td>$met_date</td>
					<td>$first_met</td>	
					<td>$net_premium</td>
					<td>$cr_ncd</td>
					<td>$manager_name</td>
					<td>$crmname</td>
				</tr>";
			}else
			if($misc === "MET")
		 	{
		 $tr_line  .= "<tr>
						<td style='text-align:center !important;'>$count</td>
						<td>$first_name</td>
						<td>$name</td>			
						<td>$mob</td>
						<td>$type</td>
						<td>$prospect</td>
						<td>$met_date</td>
						<td>$first_met</td>							
						<td>$manager_name</td>
						<td>$crmname</td>
					</tr>";
			}
			else
			if($misc === "PMET" || $misc === "METNCD")
			{$tr_line  .= "<tr>
						<td style='text-align:center !important;'>$count</td>
						<td>$cr_date</td>
						<td>$first_name</td>				
						<td>$name</td>
						<td>$mob</td>
						<td>$type</td>
						<td>$prospect</td>	
						<td>$net_premium</td>								
						<td>$cr_ncd</td>
						<td>$met_date</td>	
						<td>$first_met</td>	
						<td>$manager_name</td>
						<td>$crmname</td>
					</tr>";
			}
			else{	
					
				$tr_line  .= "<tr>
						<td style='text-align:center !important;'>$count</td>
						<td>$cr_date</td>
						<td>$first_name</td>				
						<td>$name</td>
						<td>$mob</td>
						<td>$type</td>
						<td>$prospect</td>	
						<td>$net_premium</td>					
						<td>$cr_ncd</td>
						<td>$manager_name</td>
						<td>$crmname</td>
					</tr>";
			}
			
		}			
	}
	if($emp_cat === "3"){
		echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Generate Date</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>Expected</th>
					<th>NCD</th>
					<th>Premium</th>
					<th>Manager</th>
					<th>Owner</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
	}else 
	if($emp_role === "6")
	{
	if($misc === "MET")
		 	{
			echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>	
					<th>RM Name</th>							
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>Met Date</th>
					<th>First Met</th>					
					<th>Manager</th>
					<th>Owner</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
			}else{
	echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>	
					<th>Generated Date</th>							
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>NCD</th>										
					<th>Manager</th>
					<th>Owner</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
		}
	}else{
	if(($emp_role === "9") || ($emp_role === "5")){
		echo "<table id='detail_list' class='table table-bordered col-style tbl'>
		<thead>
			<tr>
				<th>Sl.No</th>
				<th>Rm Name</th>
				<th>Name</th>
				<th>Mobile No</th>
				<th>Lead Type</th>
				<th>Propect</th>
				<th>Expected</th>
				<th>Met Date</th>
				<th>First Met</th>
				<th>Premium</th>
				<th>NCD</th>
				<th>Manager</th>
				<th>Owner</th>
			</tr>
		</thead>
		<tbody>	$tr_line</tbody>
	</table>";
	}else
	if($misc === "MET"){
			echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>	
					<th>RM Name</th>							
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>Met Date</th>
					<th>First Met</th>					
					<th>Manager</th>
					<th>Owner</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
			}
			else
	if($misc === "PMET" || $misc === "METNCD"){
			echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Generated Date</th>	
					<th>RM Name</th>					
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
				    <th>Premium / Expected</th>	
					<th>NCD</th>
					<th>Met Date</th>
					<th>First Met</th>
					<th>Manager</th>
					<th>Owner</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
			}
			
			else{
		echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Generated Date</th>	
					<th>RM Name</th>					
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
				    <th>Premium / Expected</th>	
					<th>NCD</th>
					<th>Manager</th>
					<th>Owner</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
	}
		
	}
	
}else
if($frm === "get_rm_lead_info"){
	$emp_role  = $_POST['emp_role'];
	$emp_cat   = $_POST['emp_cat'];
	$emp_id    = $_POST['emp_id'];
	$prospect  = $_POST['prospect'];
	$date_info = $_POST['date'];
	$misc      = $_POST['misc'];
	$cur_month = date("Y-m");

	$qry = "";
	$pro ="";
	$var="";
	$joinqry = "";
	if($date_info === "CR"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.prospect_level = '$prospect' and ospos_lead_type_info.created_date like '$cur_month%'";		
	}else
	if($date_info === "NCD"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.prospect_level = '$prospect' and ncd like '$cur_month%' and ospos_lead_type_info.met_date not like '$cur_month%'";
	}else
	if($date_info === "TEAM"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.prospect_level = '$prospect' and ospos_met_log.met_date like '$cur_month%' and ospos_lead_type_info.rm_name = '$misc'  and ospos_met_log.first_met='1'  and (ospos_leads_product.status='1' or ospos_leads_product.status  IS NULL)  and ospos_lead_type_info.status='1' ";
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$pro = "left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		$var = ",IFNULL(ospos_leads_product.overall_net_premium,0) as net_premium";
	}else
	if($date_info === "MET"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.rm_name = '$misc' and ospos_met_log.met_date like '$cur_month%'  and ospos_met_log.first_met='1' and (ospos_leads_product.status='1' or ospos_leads_product.status  IS NULL)  and ospos_lead_type_info.status='1'";		
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$pro = "left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		
	}else
	if($date_info === "CRMMET"){
		$qry = "ospos_lead_type_info.created_by in ($emp_id) and ospos_met_log.met_date like '$cur_month%' and ospos_met_log.first_met='1' and ospos_lead_type_info.prospect_level = '$prospect' and (ospos_leads_product.status='1' or ospos_leads_product.status IS NULL)  and ospos_lead_type_info.status='1' ";
		$joinqry = "inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id";
		$pro = "left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id";
		$var = ",ospos_leads_product.overall_net_premium as net_premium";		
	}
	$lead_info = mysql_query("SELECT ospos_lead_type_info.met_date as met_date,ospos_lead_type_info.created_date as cr_date,ospos_lead_type_info.ncd as cr_ncd,ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_lead_type.lead_type as type,ospos_prospect_level.prospect_name as prospect,ospos_people.first_name as crmname,ospos_lead_type_info.rm_name as rm_name,ospos_lead_type_info.created_by $var FROM ospos_lead_type_info left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id LEFT join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id LEFT join ospos_lead_type on ospos_lead_type.lead_type_id = ospos_lead_type_info.lead_type LEFT join ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id $joinqry $pro where $qry and ospos_lead_type_info.lead_status = '9' ");
	
	$tr_line = "";
	$count = 0;
	$net_premium = "0";
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		$count++;
		$cr_date    = date('d-m-Y', strtotime($lead_info_rslt['cr_date']));
		$met_date   = date('d-m-Y', strtotime($lead_info_rslt['met_date']));
		$cr_ncd     = date('d-m-Y', strtotime($lead_info_rslt['cr_ncd']));
		$name       = $lead_info_rslt['name'];
		$mob        = $lead_info_rslt['mob'];
		$type       = $lead_info_rslt['type'];
		$prospect   = $lead_info_rslt['prospect'];
		$crmname    = $lead_info_rslt['crmname'];
		$created_by = $lead_info_rslt['created_by'];
		$rm  = $lead_info_rslt['rm_name'];
		if((!$date_info === "CR") || (!$date_info === "NCD")){
			$net_premium  = $lead_info_rslt['net_premium'];
		}
		if($cr_date === "01-01-1970"){
			$cr_date = "";
		}	
		if($met_date === "01-01-1970"){
			$met_date = "";
		}
		if($cr_ncd === "01-01-1970"){
			$cr_ncd = "";
		}
			
	$rm_info = mysql_query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$rm'");
	$rm_name = mysql_fetch_array($rm_info);
	$first_name = $rm_name['first_name'];
	
	$report_info = mysql_query("SELECT reporting from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$created_by'");
		$tl_id = mysql_fetch_array($report_info);
		$tl = $tl_id['reporting'];
				
	$tl_info = mysql_query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$tl'");
	$tl_name = mysql_fetch_array($tl_info);
	$manager_name = $tl_name['first_name'];
		$tr_line  .= "<tr>	
						<td>$cr_date</td>
						<td>$first_name</td>					
						<td>$name</td>
						<td>$mob</td>
						<td>$type</td>
						<td>$prospect</td>
						<td>$net_premium</td>
						<td>$met_date</td>
						<td>$cr_ncd</td>
						<td>$manager_name</td>
						<td>$crmname</td>
					</tr>";
	}
	echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Created Date</th>	
					<th>Rm Name</th>				
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>Premium</th>
					<th>Met Date</th>
					<th>NCD</th>
					<th>Manager</th>
					<th>CRM Name</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else
if($frm === "rm_lead_info"){
	$emp_role  = $_POST['emp_role'];
	$emp_cat   = $_POST['emp_cat'];
	$emp_id    = $_POST['emp_id'];
	$prospect  = $_POST['prospect'];
	$date_info = $_POST['date'];
	$misc      = $_POST['misc'];
	$cur_month = date("Y-m");	
	
	$lead_info = mysql_query("SELECT ospos_lead_type_info.created_date as cr_date,ospos_lead_type_info.ncd as cr_ncd,ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_lead_type.lead_type as type,ospos_prospect_level.prospect_name as prospect,ospos_people.first_name as crmname FROM ospos_lead_type_info left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id LEFT join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id LEFT join ospos_lead_type on ospos_lead_type.lead_type_id = ospos_lead_type_info.lead_type LEFT join ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id where (ospos_lead_type_info.created_by in ($emp_id) or ospos_lead_type_info.rm_name in ($emp_id)) and ospos_lead_type_info.lead_status = '9' and met_date like '$cur_month%'");
	
	$tr_line = "";
	$count = 0;
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		$count++;
		$cr_date  = date('d-m-Y', strtotime($lead_info_rslt['cr_date']));
		$cr_ncd   = date('d-m-Y', strtotime($lead_info_rslt['cr_ncd']));
		$name     = $lead_info_rslt['name'];
		$mob      = $lead_info_rslt['mob'];
		$type     = $lead_info_rslt['type'];
		$prospect = $lead_info_rslt['prospect'];
		$crmname  = $lead_info_rslt['crmname'];
		$tr_line  .= "<tr>
						<td>$cr_date</td>
						<td>$name</td>
						<td>$mob</td>
						<td>$type</td>
						<td>$prospect</td>
						<td>$cr_ncd</td>
						<td>$crmname</td>
					</tr>";
	}
	echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Generate Date</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>NCD</th>
					<th>Owner</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else
if($frm === "get_team_info"){
	$emp_role  = $_POST['emp_role'];
	$emp_cat   = $_POST['emp_cat'];
	$emp_id    = $_POST['emp_id'];
	$date_info = $_POST['date'];
	$misc      = $_POST['misc'];

	if($date_info === "T"){
		$date = date("Y-m-d"); 
	}else
	if($date_info === "Y"){
		$date = date("Y-m-d",strtotime("-1 days"));
	}
	if($date_info === "TW"){
		$date = date("Y-m-d",strtotime("+1 days"));
	}else
	if($date_info === "M"){
		$date = date("Y-m");
	}
	$team_ids = explode(",", $emp_id);

	if(($emp_role === "6") && ($emp_cat === "2")){
		$tr_line = '';
		foreach ($team_ids as $id) {
			$crm_info = mysql_query("SELECT first_name FROM `ospos_employees` left join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$id'");
			$crm_info_rslt = mysql_fetch_array($crm_info);
			$crm_name = $crm_info_rslt['first_name'];
			
			$report_info = mysql_query("SELECT reporting from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$id'");
				$tl_id = mysql_fetch_array($report_info);
				$tl = $tl_id['reporting'];
				
			$tl_info = mysql_query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$tl'");
			$tl_name = mysql_fetch_array($tl_info);
			$manager_name = $tl_name['first_name'];			
			
			$zeroqry = mysql_query("SELECT log_lead_id, ospos_met_log.met_date FROM  ospos_met_log inner join ospos_lead_type_info on ospos_met_log.log_lead_id=ospos_lead_type_info.lead_id where ospos_lead_type_info.lead_mode='1' and lead_status='9' and ospos_lead_type_info.created_by = '$id' and ospos_met_log.first_met='1' group by log_lead_id order by ospos_met_log.met_date desc limit 0,1");
				$zeroqry_rslt = mysql_fetch_array($zeroqry);

				$met_date     = $zeroqry_rslt['met_date'];
				
				if($met_date === ""){
					$met_date = date('Y-m-01');
				}		

			$now       = time();					
            $your_date = strtotime($met_date);
            if($your_date=="")
			{				
					$get_doj = mysql_query("SELECT join_date from ospos_employees where id= '$id'");
					$get_doj_rslt = mysql_fetch_array($get_doj);
				    $join_date=$get_doj_rslt['join_date'];
				    $your_date= strtotime($join_date);
			}

            $datediff  = $now - $your_date;
            $zero_days = floor($datediff / (60 * 60 * 24));
            

            $app    = 0;
			$follw  = 0;
			$met    = 0;
			$log    = 0;
			
            $lead_info = mysql_query("SELECT count(*) as count,lead_status,statusname FROM `ospos_lead_type_info` left join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by = '$id' and ncd like '$date%' GROUP by lead_status");
			while($lead_info_rslt = mysql_fetch_array($lead_info)){				
				$count       = $lead_info_rslt['count'];
				$lead_status = $lead_info_rslt['statusname'];
				if($lead_status === "Appointment"){
                    $app  = $count;
                }else
                if($lead_status === "Follow Up"){
                    $follw = $count;
                }
			}

			$log_lead_info = mysql_query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where ospos_lead_type_info.lead_mode='1' and prospect_level='4' and ospos_lead_type_info.created_by = '$id' and ospos_met_log.met_date  like '$date%' and login_date like '$date%' and ospos_met_log.first_met='1'");
			$log_lead_info_rslt = mysql_fetch_array($log_lead_info);
			$log = $log_lead_info_rslt['count'];

			$met_lead_info = mysql_query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id  where lead_status='9' and ospos_lead_type_info.created_by = $id and ospos_met_log.met_date  like '$date%' and ospos_met_log.first_met='1'");
			$met_lead_info_rslt = mysql_fetch_array($met_lead_info);
			$met = $met_lead_info_rslt['count'];

			$cratio = 0;
			if(((int)$log >0) && ((int)$met > 0)){
				$cratio = round(((int)$log / (int)$met) * 100);
			}
			$clr = "";
			if((int)$zero_days >= 3){
				$clr = "style='color:#FF5252';";
			}
			$tr_line .= "<tr $clr>
							<td>$crm_name</td>
							<td>$manager_name</td>
							<td>$zero_days</td>
							<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$id,9,'$date_info','PMET');>$met</td>
							<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$id,1,'$date_info','');>$app</td>
							<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$id,2,'$date_info','');>$follw</td>
							<td>$cratio%</td>
						</tr>";
		}
		echo "$tr_line";
	}
}else
if($frm === "get_business_info"){
	$category   = $_POST['category'];
	$start_date = date('Y-m-d',strtotime($_POST['start_date']));
	$end_date   = date('Y-m-d',strtotime($_POST['end_date']));
	if($category === "2"){
		//RM Team Data
		$rm_info = mysql_query("SELECT id,first_name,reporting FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '5' and deleted = '0' GROUP BY id ORDER BY `first_name` ASC");
        $rm_list = "";  
        $tot_life   = 0;   
        $tot_term   = 0;  
        $tot_mf = 0;   
		while($rm_rslt = mysql_fetch_array($rm_info)){
            $id         = $rm_rslt['id'];
            $first_name = $rm_rslt['first_name'];
            $reporting  = $rm_rslt['reporting'];
			
            $rm_manager_info = mysql_query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where  ospos_employees.id = '$reporting'");
			$value = mysql_fetch_object($rm_manager_info);
            $rm_manage = $value->first_name;
			
            $lead_info = mysql_query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and ospos_lead_type_info.status = '1' and ospos_leads_product.status =  '1' GROUP by ospos_leads_product.product_category");
            
            $mf_qry = mysql_query("SELECT IFNULL(sum(amount),0) as mf_amount FROM ospos_mf_investment inner join ospos_lead_type_info on ospos_lead_type_info.lead_id = ospos_mf_investment.lead_id where DATE_FORMAT(ospos_mf_investment.entry_date, '%Y-%m-%d') between '$start_date' and '$end_date' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and ospos_mf_investment.investment_type = 'SIP' and ospos_mf_investment.status = '1'");
            $mf_rslt = mysql_fetch_object($mf_qry);
            	$mf_amount  = $mf_rslt->mf_amount;
            	$tot_mf     = $tot_mf + $mf_amount;
            $lifeamount = 0;            
            $termamount = 0;
          
            while($lead_rslt = mysql_fetch_array($lead_info)){
                $category   = $lead_rslt['category'];
                $netamount  = $lead_rslt['netamount'];                
                if($category === "2"){
                    $lifeamount = $netamount;                                  
                }
                if($category === "5"){
                    $termamount = $netamount;
                }
            }

        $rm_list .= "<tr>
                            <td>$rm_manage</td>
                            <td>$first_name</td>
                            <td class='cursor' onclick=get_mis_rmlead(5,2,$id,2,'$start_date','$end_date','2');>$lifeamount</td>
                            <td class='cursor' onclick=get_mis_rmlead(5,2,$id,4,'$start_date','$end_date','SIP');>$mf_amount</td>
                            <td class='cursor' onclick=get_mis_rmlead(5,2,$id,5,'$start_date','$end_date','4');>$termamount</td>
                        </tr>";
             $tot_life= $tot_life + $lifeamount;
             $tot_term= $tot_term + $termamount;
              
        }
	  echo "<table id='rm_table' class='table table-bordered table-hover col-style'>
				<thead>
					<tr>
						<th>RM Manager</th>
						<th>RM Name</th>
						<th>Life</th>
						<th>SIP</th>
						<th>Term Plan</th>
					</tr>
				</thead>
				<tbody>
					$rm_list					
				</tbody>
				<tr style='background: linear-gradient(45deg, #00AEFF -182%, #3369E6 100%) !important;color:#FFFFFF'><td colspan='2' style='text-align:center !important';>Total</td><td>$tot_life</td><td>$tot_mf</td><td>$tot_term</td></tr>
			</table>";
			
	}else
	if($category === "3"){
		//Health Team Data
		$ht_tl_info = mysql_query("SELECT id,first_name,reporting FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '6' and deleted = '0' and category = '3' ORDER BY ospos_employees.reporting ASC");
		
        $ht_tl_list = "";
        $tot_star   = 0;
        $tot_nj     = 0;
        $tot_other  = 0;
        $tot_net    = 0;
		while($ht_tl_rslt = mysql_fetch_array($ht_tl_info)){
            $id         = $ht_tl_rslt['id'];
            $first_name = $ht_tl_rslt['first_name'];

            $team_info = mysql_query("SELECT GROUP_CONCAT(id) as team_ids FROM `ospos_employees` WHERE reporting = '$id'");
			$team_rslt = mysql_fetch_object($team_info);
            $team_ids = $team_rslt->team_ids.",".$id; 
						
            $lead_info = mysql_query("SELECT ospos_leads_product.company,vendorcompanyname,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id inner join ospos_happy_calling on ospos_happy_calling.call_product_id  =  ospos_leads_product.customer_product_id left join ospos_vendor on vendor_id = ospos_leads_product.company where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.created_by in ($team_ids) and ospos_lead_type_info.status = '1' and ospos_leads_product.status =  '1' GROUP by ospos_leads_product.company");
			
            $star_amt = 0;
            $nj_amt   = 0;   
            $other_amt = 0;       
			while($lead_rslt = mysql_fetch_array($lead_info)){
                $company   = $lead_rslt['vendorcompanyname'];
                $netamount = $lead_rslt['netamount'];
                if($company === "Star Health"){
                    $star_amt += $netamount;
                }else
                if($company === "NJ"){
                    $nj_amt += $netamount;
                }else{
                    $other_amt += $netamount;
                }
            }
            $net = $star_amt + $nj_amt + $other_amt;
            $tot_star  =  $tot_star + $star_amt;
            $tot_nj    =  $tot_nj + $nj_amt;
            $tot_other =  $tot_other + $other_amt;
            $tot_net   =  $tot_net + $net;
            $ht_tl_list .= "<tr>
                            <td>$first_name</td>                            
                            <td class='cursor' onclick=get_mis_hllead(6,$category,'$team_ids',3,'$start_date','$end_date','STAR');>$star_amt</td>
                            <td class='cursor' onclick=get_mis_hllead(6,$category,'$team_ids',3,'$start_date','$end_date','NJ');>$nj_amt</td>
                            <td class='cursor' onclick=get_mis_hllead(6,$category,'$team_ids',3,'$start_date','$end_date','OTHERS');>$other_amt</td>
                            <td>$net</td>
                        </tr>";
        }
		echo "<table id='rm_table' class='table table-bordered table-hover col-style'>
				<thead>
					<tr>
						<th>Health Manager</th>
						<th>Star</th>
						<th>NJ</th>
						<th>Others</th>
						<th>Total</th>
					</tr>
				</thead>
				<tbody>
					$ht_tl_list					
				</tbody>
				<tr style='background: linear-gradient(45deg, #00AEFF -182%, #3369E6 100%) !important;color:#FFFFFF'><td style='text-align:center !important';>Total</td><td>$tot_star</td><td>$tot_nj</td><td>$tot_other</td><td>$tot_net</td></tr>
			</table>";
	}
	
}else
if($frm === "get_renewal_info"){
	$category   = $_POST['category'];
	$start_date = date('Y-m-d',strtotime($_POST['start_date']));
	$end_date   = date('Y-m-d',strtotime($_POST['end_date']));
	//Renewal Data	
	$qry = "";
	if($category === "2"){
		$qry = "and log_renew_status = 'Paid' and log_client_status = 'Closed'";
	}else
	if($category === "3"){
		$qry = "and (log_renew_status = 'Renewed' or log_client_status = '')";
	}

	$paid_qry = mysql_query("SELECT count(*) as count,IFNULL(sum(log_overall_total_premium),0) as premium FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal.renewal_id = ospos_renewal_log.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and log_renewal_date between '$start_date' and '$end_date' and (ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) $qry");
	$paid_qry_rslt = mysql_fetch_assoc($paid_qry);
	$paid         = $paid_qry_rslt['count'];
	$paid_premium = $paid_qry_rslt['premium'];
	if($paid_premium){
		$paid_premium = tlac($paid_qry_rslt['premium']);
	}

	$renew_info = mysql_query("SELECT count(*) as count,sum(ren_overall_total_premium) as premium,renew_status FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and due_date between '$start_date' and '$end_date' GROUP BY renew_status");
        $port_list = "";  
        $health_list = "";  
		$renewed = 0;
		$dues   = 0;
		$grace  = 0;
		$reduce = 0;
		$term   = 0;
		$sur    = 0;
		$lapsed = 0;
		$drop   = 0;
		$dues_premium = 0;
		$grace_premium = 0;
		$lapsed_premium = 0;
		$reduce_premium = 0;
		$term_premium = 0;
		$sur_premium  = 0;
		$drop_premium = 0;
		while($renew_rslt = mysql_fetch_array($renew_info)){
            $renew_status         = $renew_rslt['renew_status'];
            $count                = $renew_rslt['count'];
            $premium              = $renew_rslt['premium'];
			if($renew_status === "Dues"){
				$dues     = $count;
				$dues_premium  = tlac($renew_rslt['premium']);
			}else
			if($renew_status === "GracePeriod"){
				$grace    = $count;
				$grace_premium  = tlac($renew_rslt['premium']);
			}else
			if($renew_status === "Lapsed"){
				$lapsed   = $count;
				$lapsed_premium  = tlac($renew_rslt['premium']);
			}else
			if($renew_status === "ReducedPaidup"){
				$reduce   = $count;
				$reduce_premium  = tlac($renew_rslt['premium']);
			}else
            if($renew_status === "Terminated"){
				$term     = $count;
				$term_premium  = $renew_rslt['premium'];
			}else
            if($renew_status === "Surrendered"){
				$sur      = $count;
				$sur_premium  = $renew_rslt['premium'];
			}
			$drop = $term + $sur;
			$drop_premium = $term_premium + $sur_premium;
			if($drop_premium){
				$drop_premium = tlac($drop_premium);
			}
		}
			if($category === "2"){
			$port_list .= "<tr><td>Count</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','Dues');>$dues - $dues_premium</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','GracePeriod');>$grace - $grace_premium</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','ReducedPaidup');>$reduce - $reduce_premium</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','Paid');>$paid - $paid_premium</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','Dropped');>$drop - $drop_premium</td>
							</tr>";
			}else
			if($category === "3"){
			$health_list .= "<tr><td>Count</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','Dues');>$dues - $dues_premium</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','GracePeriod');>$grace - $grace_premium</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','Lapsed');>$lapsed - $lapsed_premium</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','Renewed');>$paid - $paid_premium</td>
								<td class='cursor' onclick=get_mis_renewal('renew_status',$category,'$start_date','$end_date','Dropped');>$drop - $drop_premium</td>
							</tr>";
			}
        
		if($category === "2"){
		  echo "<table id='renew_table' class='table table-bordered table-hover col-style'>
					<thead>
						<tr>
							<th>Renewal Status</th>
							<th>Dues</th>
							<th>Grace</th>
							<th>Reduce Paid Up</th>
							<th>Paid</th>
							<th>Dropped</th>
						</tr>
					</thead>
					<tbody>
						$port_list
					</tbody>
				</table>";
		}else
		if($category === "3"){
		  echo "<table id='renew_table' class='table table-bordered table-hover col-style'>
					<thead>
						<tr>
							<th>Renewal Status</th>
							<th>Dues</th>
							<th>Grace</th>
							<th>Lapsed</th>
							<th>Renewed</th>
							<th>Dropped</th>
						</tr>
					</thead>
					<tbody>
						$health_list
					</tbody>
				</table>";
		}
			
}else
if($frm === "get_renew_life_info"){
	$date_info = $_POST['date'];

	if($date_info === "M"){
		$date = date("Y-m"); 
		$notpaidqry = "ospos_renewal.renewal_date like '$date%'";
		$paidqry    = "ospos_renewal_log.created_date like '$date%'";
	}else
	if($date_info === "FY"){
		$to_year   = (date('m') > 3) ? date('Y') +1 : date('Y');
        $frm_year  = $to_year - 1;
        $from = $frm_year."-04-01";
        $to   = $to_year."-03-31";
		
		$notpaidqry = "(ospos_renewal.renewal_date BETWEEN '$from' and '$to')";
		$paidqry    = "(ospos_renewal_log.created_date BETWEEN '$from' and '$to')";
	}else
	if($date_info === "LTD"){
		$date = date("Y-m-d");
		$notpaidqry = "ospos_renewal.renewal_date < CURRENT_DATE()";
		$paidqry = "ospos_renewal_log.created_date < CURRENT_DATE()";
	}
	$notpaid_info = mysql_query("SELECT ospos_renewal.client_status,ospos_renewal.renew_status ,IFNULL(sum(ospos_leads_product.overall_net_premium),0) as notpaid FROM ospos_renewal inner join ospos_leads_product on ospos_leads_product.customer_product_id = ospos_renewal.product_id where product_category = '2' and $notpaidqry GROUP by ospos_renewal.client_status,ospos_renewal.renew_status");
	$tr_line = "";	
	$life_not_paid = 0;	
	$life_ni = 0;
	while($notpaid_rslt = mysql_fetch_array($notpaid_info)){				
		$client_status  = $notpaid_rslt['client_status'];
		$renew_status   = $notpaid_rslt['renew_status'];
		$notpaid        = $notpaid_rslt['notpaid'];
		if(($renew_status !== "Paid") && ($renew_status !== "Renewed") && ($renew_status !== "Terminated") && ($renew_status !== "Surrendered")){
				$life_not_paid += $notpaid;
				if($client_status === "NotIntrested"){
					$life_ni  += $notpaid;
				}
		}
	}	
	$paid_info = mysql_query("SELECT ospos_renewal.client_status,ospos_renewal.renew_status,IFNULL(sum(ospos_renewal_log.log_overall_total_premium),0) as paid FROM ospos_renewal inner join ospos_leads_product on ospos_leads_product.customer_product_id = ospos_renewal.product_id INNER join ospos_renewal_log on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id where product_category = '2' and $paidqry GROUP BY ospos_renewal.client_status,ospos_renewal.renew_status");
	$life_paid = 0;	
	$life_close = 0;
	while($paid_rslt = mysql_fetch_array($paid_info)){				
		$client_status  = $paid_rslt['client_status'];
		$renew_status   = $paid_rslt['renew_status'];
		$paid           = $paid_rslt['paid'];
		if(($renew_status === "Paid") || ($renew_status === "Renewed")){
				$life_paid += $paid;
				if($client_status === "Closed"){
					$life_close  += $paid;
				}
		}
	}
	$collectable = $life_not_paid + $life_ni + $life_paid + $life_close ;
	$lnp_per = 0;
	$lp_per = 0;
	$lni_per = 0;
	$lclose_per = 0;
	if($collectable){
		$lnp_per = round(($life_not_paid / $collectable) * 100);
		$lp_per = round(($life_paid / $collectable) * 100);
		$lni_per = round(($life_ni / $collectable) * 100);
		$lclose_per = round(($life_close / $collectable) * 100);
	}
		
	
	$tr_line .= "<tr>
					<td>Status</td>
					<td>$collectable</td>
					<td class='cursor' onclick=get_renewal_info_list('$date_info','2','PAID');>$life_paid</td>
					<td class='cursor' onclick=get_renewal_info_list('$date_info','2','NOTPAID');>$life_not_paid</td>					
					<td>$life_ni</td>
					<td>$life_close</td>
				</tr>
				<tr>
					<td>Persistency</td>
					<td></td>					
					<td>$lp_per%</td>
					<td>$lnp_per%</td>
					<td>$lni_per%</td>
					<td>$lclose_per%</td>
				</tr>";
	echo "$tr_line";
}else
if($frm === "get_renew_hl_info"){
	$date_info = $_POST['date'];

	if($date_info === "M"){
		$date = date("Y-m"); 
		$notpaidqry = "ospos_renewal.renewal_date like '$date%'";
		$paidqry    = "ospos_renewal_log.created_date like '$date%'";
	}else
	if($date_info === "FY"){
		$to_year   = (date('m') > 3) ? date('Y') +1 : date('Y');
        $frm_year  = $to_year - 1;
        $from = $frm_year."-04-01";
        $to   = $to_year."-03-31";
		
		$notpaidqry = "(ospos_renewal.renewal_date BETWEEN '$from' and '$to')";
		$paidqry    = "(ospos_renewal_log.created_date BETWEEN '$from' and '$to')";
	}else
	if($date_info === "LTD"){
		$date = date("Y-m-d");
		$notpaidqry = "ospos_renewal.renewal_date < CURRENT_DATE()";
		$paidqry = "ospos_renewal_log.created_date < CURRENT_DATE()";
	}
		
	$notpaid_info = mysql_query("SELECT ospos_renewal.client_status,ospos_renewal.renew_status ,IFNULL(sum(ospos_leads_product.overall_net_premium),0) as notpaid FROM ospos_renewal inner join ospos_leads_product on ospos_leads_product.customer_product_id = ospos_renewal.product_id where product_category = '3' and $notpaidqry GROUP by ospos_renewal.client_status,ospos_renewal.renew_status");
	$tr_line = "";	
	$hl_not_paid   = 0;
	$health_ni = 0;
	while($notpaid_rslt = mysql_fetch_array($notpaid_info)){				
		$client_status  = $notpaid_rslt['client_status'];
		$renew_status   = $notpaid_rslt['renew_status'];
		$notpaid        = $notpaid_rslt['notpaid'];
		if(($renew_status !== "Paid") && ($renew_status !== "Renewed") && ($renew_status !== "Terminated") && ($renew_status !== "Surrendered")){
				$hl_not_paid += $notpaid;
				if($client_status === "NotIntrested"){
					$health_ni  += $notpaid;
				}
		}
	}
		
	$paid_info = mysql_query("SELECT ospos_renewal.client_status,ospos_renewal.renew_status,IFNULL(sum(ospos_renewal_log.log_overall_total_premium),0) as paid,IFNULL(ospos_leads_product.overall_net_premium,0) as notpaid FROM ospos_renewal inner join ospos_leads_product on ospos_leads_product.customer_product_id = ospos_renewal.product_id INNER join ospos_renewal_log on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id where product_category = '3' and $paidqry GROUP BY ospos_renewal.client_status,ospos_renewal.renew_status");
	$hl_paid   = 0;
	$health_close = 0;
	while($paid_rslt = mysql_fetch_array($paid_info)){				
		$client_status  = $paid_rslt['client_status'];
		$renew_status   = $paid_rslt['renew_status'];
		$paid           = $paid_rslt['paid'];
		if(($renew_status === "Paid") || ($renew_status === "Renewed")){
				$hl_paid += $paid;
			if($client_status === "Closed"){
				$health_close  += $paid;
			}
		}
	}
	$collectable = $hl_not_paid + $health_ni + $hl_paid + $health_close ;
	$np_per = 0;
	$p_per = 0;
	$ni_per = 0;
	$close_per = 0;
	if($collectable){
	$np_per = round(($hl_not_paid / $collectable) * 100);
	$p_per = round(($hl_paid / $collectable) * 100);
	$ni_per = round(($health_ni / $collectable) * 100);
	$close_per = round(($health_close / $collectable) * 100);
    }
	$tr_line .= "<tr>
					<td>Status</td>
					<td>$collectable</td>					
					<td class='cursor' onclick=get_renewal_info_list('$date_info','2','PAID');>$hl_paid</td>
					<td class='cursor' onclick=get_renewal_info_list('$date_info','2','NOTPAID');>$hl_not_paid</td>
					<td>$health_ni</td>
					<td>$health_close</td>
				</tr>
				<tr>
					<td>Persistency</td>
					<td></td>					
					<td>$p_per%</td>
					<td>$np_per%</td>
					<td>$ni_per%</td>
					<td>$close_per%</td>
				</tr>";
	echo "$tr_line";
	//SAT END
}else
if($frm === "get_renew_rm_info"){
	$date_info = $_POST['date'];
	$emp_ids = $_POST['ids'];
	$qry = "";
	if($date_info === "M"){
		$date = date("Y-m"); 	
		$qry = "like '$date%'";	
	}else
	if($date_info === "FY"){
		//Get Financial Year
	    $year = date('Y');
	    $year1 = date('Y')-1;
	    $year2 = date('Y')+1;
		if (date('m') > 03){
			$fin_start_month = $year."-"."04"."-"."01";
			$fin_end_month   = $year2."-"."03"."-"."31"; 
			$m = date("m");
			$mth = $m - 1 ;
			$lst_mth = $mth - 3; 
		}else{    
			$fin_start_month = $year1."-"."04"."-"."01";
			$fin_end_month   = $year."-"."03"."-"."31";
			$m = date("m");
			$lst_mth = $m + 9 ;
		}	
        $qry = " between '$fin_start_month' and '$fin_end_month'";
	}else
	if($date_info === "LTD"){
		$today = date("Y-m-d");
		$qry = "between '2014-01-01' and '$today'";		
	}
	$Collectable = 0;
	$rm_renewal_info = mysql_query("SELECT IFNULL(overall_net_premium,0) as netamount FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id inner join ospos_lead_type_info on ospos_lead_type_info.lead_type_id = ospos_leads_product.lead_info_id where product_category = '2' and (ospos_lead_type_info.created_by in ($emp_ids) or ospos_lead_type_info.rm_name in ($emp_ids)) and (log_renew_status != 'Terminated' or log_renew_status != 'Surrendered') and ospos_renewal.status = '1' and ospos_renewal_log.log_due_date $qry GROUP BY ospos_renewal_log.renewal_id,log_due_date");
	
	$tr_line = "";	
	while($rm_renewal_rslt = mysql_fetch_array($rm_renewal_info)){
		$Collectable  += $rm_renewal_rslt['netamount'];
	}	
	
	$paid_info = mysql_query("SELECT IFNULL(overall_net_premium,0) as paid FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id inner join ospos_lead_type_info on ospos_lead_type_info.lead_type_id = ospos_leads_product.lead_info_id where product_category = '2' and (ospos_lead_type_info.created_by in ($emp_ids) or ospos_lead_type_info.rm_name in ($emp_ids)) and (log_renew_status != 'Terminated' or log_renew_status != 'Surrendered') and (ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) and log_renew_status = 'Paid' and  log_client_status = 'Closed' and ospos_renewal.status = '1' and ospos_renewal_log.log_renewal_date $qry GROUP BY ospos_renewal_log.renewal_id,log_renewal_date");
	$paid = 0;	
	while($rm_renewal_rslt = mysql_fetch_array($paid_info)){	
		$paid  += $rm_renewal_rslt['paid'];
	}
	$not_paid_info = mysql_query("SELECT IFNULL(sum(overall_net_premium),0) as not_paid FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id inner join ospos_lead_type_info on ospos_lead_type_info.lead_type_id = ospos_leads_product.lead_info_id where product_category = '2' and (ospos_lead_type_info.created_by in ($emp_ids) or ospos_lead_type_info.rm_name in ($emp_ids)) and (renew_status != 'Terminated' or renew_status != 'Surrendered' or renew_status IS NULL) and ospos_renewal.status = '1' and ospos_renewal.due_date $qry");
	$not_paid = 0;	
	while($not_paid_rslt = mysql_fetch_array($not_paid_info)){	
		$not_paid  += $not_paid_rslt['not_paid'];
	}
	
	$tr_line .= "<tr>
					<td>Status</td>
					<td class='cursor' onclick=get_renewal_info_list('$date_info','2','$emp_ids','Collectable');>$Collectable</td>
					<td class='cursor' onclick=get_renewal_info_list('$date_info','2','$emp_ids','PAID');>$paid</td>
					<td class='cursor' onclick=get_renewal_info_list('$date_info','2','$emp_ids','NOTPAID');>$not_paid</td>
				</tr>";
				
	echo "$tr_line";
	//echo "<tr colspan='6'><td >Under Processing...</td></tr>";
}else
if($frm === "get_renewal_info_list"){
	$date_info = $_POST['date'];
	$cat       = $_POST['cat'];
	$emp_ids   = $_POST['emp_ids'];
	$sts       = $_POST['sts'];
	$qry = "";
	$type_qry = "";
	if($date_info === "M"){
		$date = date("Y-m"); 
		$qry = "like '$date%'"; 
	}else
	if($date_info === "FY"){
		//Get Financial Year
	    $year = date('Y');
	    $year1 = date('Y')-1;
	    $year2 = date('Y')+1;
		if (date('m') > 03){
			$fin_start_month = $year."-"."04"."-"."01";
			$fin_end_month   = $year2."-"."03"."-"."31"; 
			$m = date("m");
			$mth = $m - 1 ;
			$lst_mth = $mth - 3; 
		}else{    
			$fin_start_month = $year1."-"."04"."-"."01";
			$fin_end_month   = $year."-"."03"."-"."31";
			$m = date("m");
			$lst_mth = $m + 9 ;
		}
		$qry = "between '$fin_start_month' and '$fin_end_month'";
	}else
	if($date_info === "LTD"){
		$today = date("Y-m-d");
		$qry = "between '2014-01-01' and '$today'";			
	}
	if($sts === "Collectable"){
		$type_qry = "and log_due_date $qry GROUP BY ospos_renewal_log.renewal_id,log_due_date"; 
	}else
	if($sts === "PAID"){
		$type_qry = "and log_renew_status = 'Paid' and  log_client_status = 'Closed' and (ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) and log_renewal_date $qry GROUP BY ospos_renewal_log.renewal_id,log_renewal_date";
	}

	if($sts === "NOTPAID"){
		$renew_list_info = mysql_query("SELECT ospos_customers.cust_name as name,ospos_customers.cust_mobile,renew_status as log_renew_status,ospos_renewal.client_status as log_client_status,ospos_renewal.renewal_date as log_renewal_date,due_date as log_due_date,cat_name,ren_overall_net_premium as log_overall_net_premium,ospos_renewal.policy_no  as log_policy_no,ospos_happy_calling.issued_date FROM  ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = customer_product_id inner join ospos_lead_type_info on lead_info_id = lead_type_id inner join ospos_leads on ospos_lead_type_info.lead_id = ospos_leads.lead_id inner join  ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id inner join ospos_category on product_category = cat_id inner join ospos_happy_calling on call_product_id = customer_product_id where product_category = '2' and (ospos_lead_type_info.created_by in ($emp_ids) or ospos_lead_type_info.rm_name in ($emp_ids)) and (renew_status != 'Terminated' or renew_status != 'Surrendered' or renew_status IS NULL) and ospos_renewal.status = '1' and ospos_renewal.due_date $qry");
	}else{
	$renew_list_info = mysql_query("SELECT ospos_customers.cust_name as name,ospos_customers.cust_mobile,log_renew_status,log_client_status,ospos_renewal_log.log_renewal_date,log_due_date,cat_name,log_overall_net_premium,log_policy_no,ospos_happy_calling.issued_date FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal.renewal_id = ospos_renewal_log.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = customer_product_id inner join ospos_lead_type_info on lead_info_id = lead_type_id inner join ospos_leads on ospos_lead_type_info.lead_id = ospos_leads.lead_id inner join  ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id inner join ospos_category on product_category = cat_id inner join ospos_happy_calling on call_product_id = customer_product_id where product_category = '2' and (ospos_lead_type_info.created_by in ($emp_ids) or ospos_lead_type_info.rm_name in ($emp_ids)) and (log_renew_status != 'Terminated' or log_renew_status != 'Surrendered') and ospos_renewal.status = '1' and ospos_leads_product.status ='1' and ospos_lead_type_info.status = '1' $type_qry");
	}
	
	$tr_line  = "";
	$yes      = 0;
	$no       = 0;
	while($list_info = mysql_fetch_array($renew_list_info)){
		$name            = $list_info['name'];
		$cust_mobile     = $list_info['cust_mobile'];
		$cat_name        = $list_info['cat_name'];
		$renew_status    = $list_info['log_renew_status'];
		$issued_date     = $list_info['issued_date'];
		$premium         = $list_info['log_overall_net_premium'];
		$log_policy_no   = $list_info['log_policy_no'];
		$client_status   = $list_info['log_client_status'];
		$renewal_date    = date('d-m-Y', strtotime($list_info['log_renewal_date']));
		$due_date        = date('d-m-Y', strtotime($list_info['log_due_date']));
		if($due_date === "01-01-1970"){
			$due_date = "";
		}
		if($sts === "NOTPAID"){
			if($renew_status === "Paid" || !$renew_status){
				$renew_status = "Inforce";
			}else{
				$renew_status    = $list_info['log_renew_status'];
			}
			if($client_status === "Closed" || !$client_status){
				$client_status = "Inforce";
			}else{
				$client_status   = $list_info['log_client_status'];
			}
		}			
		$tr_line  .= "<tr>
						<td style='text-align:center !important;'>$name</td>
						<td>$cust_mobile</td>
						<td>$cat_name</td>
						<td>$log_policy_no</td>
						<td>$issued_date</td>
						<td>$premium</td>						
						<td>$renewal_date</td>
						<td>$renew_status</td>
						<td>$client_status</td>
						<td>$due_date</td>						
					</tr>";
	}
	    
	echo "<table id='detail_list' class='table table-bordered col-style'>
			<thead>
				<tr>
					<th>Name</th>
					<th>Mobile</th>
					<th>Category</th>
					<th>Policy No</th>
					<th>Issued</th>
					<th>Premium</th>
					<th>Renewed/Paid date</th>
					<th>Renew Status</th>
					<th>Client Status</th>
					<th>Due Date</th>					
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else
if($frm === "get_lead_info_issued"){
	$emp_role = $_POST['emp_role'];
	$emp_cat  = $_POST['emp_cat'];
	$emp_id   = $_POST['emp_id'];
	$sts      = $_POST['sts'];
	$date_info   = $_POST['date'];
	$misc        = $_POST['misc'];
	
	if($date_info === "T"){
		$date = date("Y-m-d"); 
	}else
	if($date_info === "Y"){
		$date = date("Y-m-d",strtotime("-1 days"));
	}
	if($date_info === "TW"){
		$date = date("Y-m-d",strtotime("+1 days"));
	}else
	if($date_info === "M"){
		$date = date("Y-m");
	}
	$issuance_status = "";
	/*if($sts === "issued"){
		$issuance_status = "and issuance_status = 'Issued'";
	}else
	if($sts === "med_pend"){
		$issuance_status = "and issuance_status = 'Medical Pending'";
	}*/
	if($sts === "issued"){
		$issuance_status = "and issuance_status = 'Issued'";
	}else
	if($sts === "med_pend"){
		$issuance_status = "and issuance_status = 'Medical Pending'";
	}	
	else
	if($sts === "not_issued"){
		$issuance_status = "and ((issuance_status != 'Medical Pending' and  issuance_status != 'Issued') or issuance_status is null) ";
	}
	
	
	$lead_info = mysql_query("SELECT ospos_lead_type_info.created_date as cr_date,ospos_lead_type_info.ncd as cr_ncd,ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_lead_type.lead_type as type,ospos_prospect_level.prospect_name as prospect,ospos_people.first_name as crmname,IFNULL(ospos_lead_type_info.expected_value,0) as expected,IFNULL(issuance_status,'Not Issued') as status,IFNULL(net_premium,0) as netamount,ospos_leads_product.login_date FROM ospos_lead_type_info left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id left join ospos_leads_product on lead_info_id = lead_type_id left join ospos_happy_calling on call_product_id = customer_product_id LEFT join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id LEFT join ospos_lead_type on ospos_lead_type.lead_type_id = ospos_lead_type_info.lead_type LEFT join ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.ncd like '$date%' and ospos_lead_type_info.prospect_level = '4' $issuance_status");
	$tr_line = "";
	$count = 0;
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		
		$cr_date  = date('d-m-Y', strtotime($lead_info_rslt['cr_date']));
		$cr_ncd   = date('d-m-Y', strtotime($lead_info_rslt['cr_ncd']));
		$name     = $lead_info_rslt['name'];
		$mob      = $lead_info_rslt['mob'];
		$type     = $lead_info_rslt['type'];
		$prospect = $lead_info_rslt['prospect'];
		$expected = $lead_info_rslt['expected'];
		$crmname  = $lead_info_rslt['crmname'];
		$issuance_status  = $lead_info_rslt['status'];
		$netamount=$lead_info_rslt['netamount'];
		$login_date=$lead_info_rslt['login_date'];
		if($sts === "not_issued"){
			//if($issuance_status === "Not Issued"){
				$count++;
				$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$cr_date</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$type</td>
					<td>$prospect</td>
					<td>$cr_ncd</td>
					<td>$netamount</td>
					<td>$login_date</td>
					<td>$crmname</td>
				</tr>";
			//}
		}else{
			$count++;
			$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$cr_date</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$type</td>
					<td>$prospect</td>
					<td>$cr_ncd</td>
					<td>$netamount</td>
					<td>$login_date</td>
					<td>$crmname</td>
				</tr>";
		}
		
		
	}
	echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Generate Date</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>NCD</th>
					<th>Premium</th>
					<th>Login Date</th>
					<th>CRM Name</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else
if($frm === "get_lead_info_type"){
	$emp_role  = $_POST['emp_role'];
	$emp_cat   = $_POST['emp_cat'];
	$emp_id    = $_POST['emp_id'];
	$sts       = $_POST['sts'];
	$date_info = $_POST['date'];
	$misc      = $_POST['misc'];

	if($date_info === "T"){
		$date = date("Y-m-d"); 
	}else
	if($date_info === "Y"){
		$date = date("Y-m-d",strtotime("-1 days"));
	}
	if($date_info === "TW"){
		$date = date("Y-m-d",strtotime("+1 days"));
	}else
	if($date_info === "M"){
		$date = date("Y-m");
	}
	if(($sts === "8") || ($sts === "9") || ($sts === "10")){
		$sts = "and ospos_lead_type_info.lead_type = '$sts'";
	}else
	if($sts === "1"){
		$sts = "";
	}else{
		$sts = " and ospos_lead_type_info.lead_type != '8' and ospos_lead_type_info.lead_type != '9' and ospos_lead_type_info.lead_type != '10'";
	}
	if($misc === "rm_name"){
		$sts = "";
		$by = "ospos_lead_type_info.rm_name in ($emp_id) and";
	}else
	if($misc === "ADMIN"){
		$by = "";
	}else{
		$by = "ospos_lead_type_info.created_by in ($emp_id) and";
	}
	
	$lead_info = mysql_query("SELECT ospos_lead_type_info.met_date as met_date,ospos_lead_type_info.created_date as cr_date,ospos_lead_type_info.ncd as cr_ncd,ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_lead_type.lead_type as type,ospos_prospect_level.prospect_name as prospect,ospos_people.first_name as crmname,IFNULL(ospos_lead_type_info.expected_value,0) as expected,IFNULL(ospos_lead_type_info.expected_login,0) as expected_log,ospos_lead_type_info.rm_name as rm_name,IFNULL(overall_net_premium,0) as premium FROM ospos_lead_type_info left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id LEFT join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id LEFT join ospos_lead_type on ospos_lead_type.lead_type_id = ospos_lead_type_info.lead_type LEFT join ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id where $by login_date like '$date%' $sts");	
	
	$tr_line = "";
	$count = 0;
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		$count++;
		$cr_date  = date('d-m-Y', strtotime($lead_info_rslt['cr_date']));
		$met_date = date('d-m-Y', strtotime($lead_info_rslt['met_date']));
		$cr_ncd   = date('d-m-Y', strtotime($lead_info_rslt['cr_ncd']));
		$name     = $lead_info_rslt['name'];
		$mob      = $lead_info_rslt['mob'];
		$type     = $lead_info_rslt['type'];
		$premium = $lead_info_rslt['premium'];
		$prospect = $lead_info_rslt['prospect'];
		$expected = $lead_info_rslt['expected'];
		$expected_log = $lead_info_rslt['expected_log'];
		$crmname  = $lead_info_rslt['crmname'];
		$rm  = $lead_info_rslt['rm_name'];
		if($met_date === "01-01-1970"){
			$met_date = "";
		}
		$rm_info = mysql_query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$rm'");
			$rm_name = mysql_fetch_array($rm_info);
			$first_name = $rm_name['first_name'];
			if(!$first_name){
				$first_name = $crmname;				
			}

		if(($emp_role === "9") || ($emp_role === "5")) {
			$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$first_name</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$type</td>
					<td>$prospect</td>
					<td>$premium</td>
					<td>$met_date</td>
					<td>$crmname</td>
				</tr>";
		}else{
			$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$cr_date</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$type</td>
					<td>$prospect</td>
					<td>$cr_ncd</td>
					<td>$crmname</td>
				</tr>";
		}		
		
	}
	if(($emp_role === "9") || ($emp_role === "5")) {
		echo "<table id='detail_list' class='table table-bordered col-style'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Rm Name</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>Premium</th>
					<th>Met Date</th>
					<th>CRM Name</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
	}else{
		echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Generate Date</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>NCD</th>
					<th>CRM Name</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
	}
}else
if($frm === "get_rm_ocl"){
	$emp_role  = $_POST['emp_role'];
	$emp_cat   = $_POST['emp_cat'];
	$emp_id    = $_POST['emp_id'];
	$sts       = $_POST['sts'];
	$date_info = $_POST['date'];
	$misc      = $_POST['misc'];

	if($date_info === "T"){
		$date = date("Y-m-d"); 
	}else
	if($date_info === "Y"){
		$date = date("Y-m-d",strtotime("-1 days"));
	}
	if($date_info === "TW"){
		$date = date("Y-m-d",strtotime("+1 days"));
	}else
	if($date_info === "M"){
		$date = date("Y-m");
	}
	
	$type = "";
	if($misc === "SIP"){
		$type = "and mf_investment = '1'";
	}else
	if($misc === "Lumpsum"){
		$type = "and mf_investment = '2'";
	}
	
	$lead_info = mysql_query("SELECT ospos_lead_type_info.created_date as cr_date,ospos_lead_type_info.ncd as cr_ncd,ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_lead_type.lead_type as type,ospos_prospect_level.prospect_name as prospect,ospos_people.first_name as crmname,IFNULL(ospos_lead_type_info.expected_value,0) as expected FROM ospos_lead_type_info left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id LEFT join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id LEFT join ospos_lead_type on ospos_lead_type.lead_type_id = ospos_lead_type_info.lead_type LEFT join ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_lead_type_info.created_by in ($emp_id) and login_date like '$date%' and ospos_lead_type_info.lead_mode = '2' and product_category = '$sts' $type");	
	
	$tr_line = "";
	$count = 0;
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		$count++;
		$cr_date  = date('d-m-Y', strtotime($lead_info_rslt['cr_date']));
		$cr_ncd   = date('d-m-Y', strtotime($lead_info_rslt['cr_ncd']));
		$name     = $lead_info_rslt['name'];
		$mob      = $lead_info_rslt['mob'];
		$type     = $lead_info_rslt['type'];
		$prospect = $lead_info_rslt['prospect'];
		$expected = $lead_info_rslt['expected'];
		$crmname  = $lead_info_rslt['crmname'];
		
		$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$cr_date</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$type</td>
					<td>$prospect</td>
					<td>$cr_ncd</td>
					<td>$crmname</td>
				</tr>";
		
	}
	echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Generate Date</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>NCD</th>
					<th>CRM Name</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else
if($frm === "get_mis_rmlead"){
	$emp_role   = $_POST['emp_role'];
	$emp_cat    = $_POST['emp_cat'];
	$emp_id     = $_POST['emp_id'];
	$sts        = $_POST['sts'];
	$start_date = $_POST['start_date'];
	$end_date 	= $_POST['end_date'];
	$misc       = $_POST['misc'];
		
	$qry = "";
	if($misc === "SIP"){
		$qry = "and mf_investment = '1'";
	}
	$lead_info = mysql_query("SELECT ospos_lead_type_info.created_date as cr_date,ospos_lead_type_info.ncd as cr_ncd,ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_lead_type.lead_type as type,ospos_prospect_level.prospect_name as prospect,ospos_people.first_name as crmname,overall_net_premium,mf_amount,policy_no FROM ospos_lead_type_info left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id LEFT join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id LEFT join ospos_lead_type on ospos_lead_type.lead_type_id = ospos_lead_type_info.lead_type LEFT join ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id LEFT join ospos_happy_calling on ospos_leads_product.customer_product_id=ospos_happy_calling.call_product_id  where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and (ospos_lead_type_info.created_by = '$emp_id' or ospos_lead_type_info.rm_name = '$emp_id') and ospos_leads_product.product_category = '$sts' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' $qry");	
	
	$tr_line = "";
	$count = 0;
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		$count++;
		$cr_date  = date('d-m-Y', strtotime($lead_info_rslt['cr_date']));
		$cr_ncd   = date('d-m-Y', strtotime($lead_info_rslt['cr_ncd']));
		$name     = $lead_info_rslt['name'];
		$mob      = $lead_info_rslt['mob'];
		$type     = $lead_info_rslt['type'];
		$prospect = $lead_info_rslt['prospect'];
		$premium = $lead_info_rslt['overall_net_premium'];	
		$mf_amount = $lead_info_rslt['mf_amount'];	
		$crmname  = $lead_info_rslt['crmname'];
		$policy_no=$lead_info_rslt['policy_no'];
		if(!$premium){
			$premium = $mf_amount;
		}
		$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$cr_date</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$type</td>
					<td>$prospect</td>
					<td>$policy_no</td>	
					<td>$premium</td>
					<td>$crmname</td>
				</tr>";
		
	}
	echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Generate Date</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>Policy No.</th>
					<th>Premium</th>
					<th>CRM Name</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else
if($frm === "get_mis_hllead"){
	$emp_role   = $_POST['emp_role'];
	$emp_cat    = $_POST['emp_cat'];
	$emp_id     = $_POST['emp_id'];
	$sts        = $_POST['sts'];
	$start_date = $_POST['start_date'];
	$end_date 	= $_POST['end_date'];
	$misc       = $_POST['misc'];

	$qry = "";
	if($misc === "STAR"){
		$qry = "and ospos_vendor.vendorcompanyname  = 'Star Health'";
	}
	if($misc === "NJ"){
		$qry = "and ospos_vendor.vendorcompanyname  = 'NJ'";
	}

	$lead_info = mysql_query("SELECT ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_people.first_name as crmname,rmp.first_name as rmname,tlp.first_name as tlname,IFNULL(ospos_leads_product.overall_net_premium,0) as net_premium,ospos_leads_product.login_date as log_date,ospos_happy_calling.issued_date as issues_date,issuance_status,document_issue_status,vendorcompanyname,policy_no FROM ospos_lead_type_info inner join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id inner join ospos_happy_calling on ospos_happy_calling.call_product_id  =  ospos_leads_product.customer_product_id left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id left join ospos_vendor on vendor_id = ospos_leads_product.company left join ospos_employees as rm on ospos_lead_type_info.rm_name = rm.id left join ospos_people as rmp on rm.person_id = rmp.person_id  left join ospos_employees as tl on ospos_lead_type_info.created_by = tl.id left join ospos_people as tlp on tl.reporting = tlp.person_id   where DATE_FORMAT(ospos_leads_product.login_date, '%Y-%m-%d') between '$start_date' and '$end_date' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by in ($emp_id) $qry");	
	
	$tr_line = "";
	$count = 0;
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		$name     = $lead_info_rslt['name'];
		$mob      = $lead_info_rslt['mob'];
		$crmname  = $lead_info_rslt['crmname'];
		$tlname   = $lead_info_rslt['tlname'];
		$rmname   = $lead_info_rslt['rmname'];
		$net_premium = $lead_info_rslt['net_premium'];
		$log_date    = $lead_info_rslt['log_date'];
		$issues_date = $lead_info_rslt['issues_date'];
		$issuance_status = $lead_info_rslt['issuance_status'];
		$document_issue_status = $lead_info_rslt['document_issue_status'];
		$company  = $lead_info_rslt['vendorcompanyname'];
		
		$issues_date = date('d-m-Y', strtotime($issues_date));
		$log_date    = date('d-m-Y', strtotime($log_date));
		$policy_no= $lead_info_rslt['policy_no'];
		
		if($misc === "OTHERS"){
			if(($company !== "Star Health") && ($company !== "NJ")){
				$count++;
				$tr_line  .= "<tr>
								<td style='text-align:center !important;'>$count</td>
								<td>$name</td>
								<td>$mob</td>
								<td>$tlname</td>
								<td>$crmname</td>
								<td>$net_premium</td>
								<td>$policy_no</td>
								<td>$log_date</td>
								<td>$issuance_status</td>
							</tr>";
			}
		}else{
			$count++;
			$tr_line  .= "<tr>
							<td style='text-align:center !important;'>$count</td>
							<td>$name</td>
							<td>$mob</td>
							<td>$tlname</td>
							<td>$crmname</td>
							<td>$net_premium</td>
							<td>$policy_no</td>							
							<td>$log_date</td>
							<td>$issuance_status</td>
						</tr>";
		}
	}
		echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Source</th>
					<th>CRM Name</th>
					<th>Premium</th>
					<th>Policy No.</th>
					<th>Login Date</th>
					<th>Issuance Status</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else
if($frm === "get_issuance_info"){
	//SAT Start MIS New Dashboard 26th july 2018
	//Get Financial Year
    $year = date('Y');
    $year1 = date('Y')-1;
    $year2 = date('Y')+1;
	if (date('m') > 03){
		$fin_start_month = $year."-"."04"."-"."01";
		$fin_end_month   = $year2."-"."03"."-"."31"; 
		$m = date("m");
		$mth = $m - 1 ;
		$lst_mth = $mth - 3; 
	}else{    
		$fin_start_month = $year1."-"."04"."-"."01";
		$fin_end_month   = $year."-"."03"."-"."31";
		$m = date("m");
		$lst_mth = $m + 9 ;
	}
	$month      = date('Y-m');
	$date       = $_POST['date'];
	$category   = $_POST['category'];
	$qry = "";
	if($date === "M"){
		$qry = "where product_category = '$category' and ospos_leads_product.login_date like '$month%'";
	}else
	if($date === "FY"){
		$qry = "where product_category = '$category' and ospos_leads_product.login_date between '$fin_start_month' and '$fin_end_month'";
	}else
	if($date === "LTD"){
		$qry = "where product_category = '$category'";
	}
	
	$issuance_info = mysql_query("SELECT issuance_status,count(*) as count from ospos_happy_calling inner join ospos_leads_product on call_product_id = customer_product_id inner join ospos_lead_type_info on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id $qry and ospos_leads_product.status ='1' and ospos_lead_type_info.status = '1' group by issuance_status");
	
	$tr_line  = "";
	$pending  = 0;
	$medical_pend = 0;
	$issued   = 0;
	$medical  = 0;
	$cheque   = 0;
	$freelook = 0;
	$sales    = 0;
	$null     = 0;
	while($issuance = mysql_fetch_array($issuance_info)){
		$issuance_status     = $issuance['issuance_status'];
		$count               = $issuance['count'];
			if($issuance_status === "Pending For Issuance"){
				$pending = $count;
			}else
			if($issuance_status === "Issued"){
				$issued = $count;
			}else
			if($issuance_status === "Medical Pending"){
				$medical_pend = $count;
			}else
			if($issuance_status === "Medical Declined"){
				$medical = $count;
			}else
			if($issuance_status === "Cheque Dishonor"){
				$cheque = $count;
			}else
			if($issuance_status === "Freelook"){
				$freelook = $count;
			}else
			if($issuance_status === "Docs Pending From Sales"){
				$sales = $count;
			}else
			if(!$issuance_status){
				$null = $count;
			}
			
		}
		$tot_pending = $pending + $medical_pend + $sales + $null;
		$dropped     = $medical + $cheque + $freelook;
		if($category === "3"){
			$tr_line  .= "<tr>
						<td style='text-align:center !important;' class='cursor' onclick=get_issuance_list('$date','$category','Issued','Issuance');>$issued</td>
						<td  class='cursor' onclick=get_issuance_list('$date','$category','Issue_Pending','Issuance');>$tot_pending</td>
						<td class='cursor' onclick=get_issuance_list('$date','$category','issue_drop','Issuance');>$dropped</td></tr>";
		echo "<table class='table table-bordered col-style'>
					<thead>
						<tr>
							<th>Issued</th>
							<th>Pending</th>
							<th>Dropped</th>
						</tr>
					</thead>
					<tbody>	$tr_line</tbody>
				</table>";	
			/*
		$tr_line  .= "<tr>
						<td style='text-align:center !important;' class='cursor' onclick=get_issuance_list('$date','$category','Issue_Pending','Issuance');>$tot_pending</td>
						<td class='cursor' onclick=get_issuance_list('$date','$category','Issued','Issuance');>$issued</td>
						<td class='cursor' onclick=get_issuance_list('$date','$category','Declined','Issuance');>$medical</td>
						<td class='cursor' onclick=get_issuance_list('$date','$category','Dishonor','Issuance');>$cheque</td>
						<td class='cursor' onclick=get_issuance_list('$date','$category','Docs Pending From Sales','Issuance');>$sales</td>
						<td class='cursor' onclick=get_issuance_list('$date','$category','Freelook','Issuance');>$freelook</td>
					</tr>";
	    
			echo "<table class='table table-bordered col-style'>
					<thead>
						<tr>
							<th>Issuance Pending</th>
							<th>Issued</th>
							<th>Medical Declined</th>
							<th>Dishonor</th>
							<th>Sales Pending</th>
							<th>Freelook</th>
						</tr>
					</thead>
					<tbody>	$tr_line</tbody>
				</table>";	
				*/
		}else{
			$tr_line  .= "<tr>					
					<td style='text-align:center !important;' class='cursor' onclick=get_issuance_list('$date','$category','Issued','Issuance');>$issued</td>
					<td class='cursor' onclick=get_issuance_list('$date','$category','Issue_Pending','Issuance');>$tot_pending</td>
					<td class='cursor' onclick=get_issuance_list('$date','$category','issue_drop','Issuance');>$dropped</td></tr>";
			echo "<table class='table table-bordered col-style'>
						<thead>
							<tr>
								<th>Issued</th>
								<th>Pending</th>
								<th>Dropped</th>
							</tr>
						</thead>
						<tbody>	$tr_line</tbody>
					</table>";
					
			/*
			$tr_line  .= "<tr>
					<td style='text-align:center !important;' class='cursor' onclick=get_issuance_list('$date','$category','Issue_Pending','Issuance');>$tot_pending</td>
					<td class='cursor' onclick=get_issuance_list('$date','$category','Issued','Issuance');>$issued</td>
					<td class='cursor' onclick=get_issuance_list('$date','$category','Declined','Issuance');>$medical</td>
					<td class='cursor' onclick=get_issuance_list('$date','$category','Dishonor','Issuance');>$cheque</td>
					<td class='cursor' onclick=get_issuance_list('$date','$category','Freelook','Issuance');>$freelook</td>
					</tr>";
	    
				echo "<table class='table table-bordered col-style'>
						<thead>
							<tr>
								<th>Issuance Pending</th>
								<th>Issued</th>
								<th>Medical Declined</th>
								<th>Dishonor</th>
								<th>Freelook</th>
							</tr>
						</thead>
						<tbody>	$tr_line</tbody>
					</table>";
					*/
		}
}else
if($frm === "get_document_info"){
	$date       = $_POST['date'];
	$category   = $_POST['category'];
	//Get Financial Year
    $year = date('Y');
    $year1 = date('Y')-1;
    $year2 = date('Y')+1;
	if (date('m') > 03){
		$fin_start_month = $year."-"."04"."-"."01";
		$fin_end_month   = $year2."-"."03"."-"."31"; 
		$m = date("m");
		$mth = $m - 1 ;
		$lst_mth = $mth - 3; 
	}else{    
		$fin_start_month = $year1."-"."04"."-"."01";
		$fin_end_month   = $year."-"."03"."-"."31";
		$m = date("m");
		$lst_mth = $m + 9 ;
	}
	$month      = date('Y-m');
	$qry = "";
	if($date === "M"){
		$qry = "where issuance_status = 'Issued' and product_category = '$category' and ospos_happy_calling.login_date like '$month%'";
	}else
	if($date === "FY"){
		$qry = "where issuance_status = 'Issued' and product_category = '$category' and ospos_happy_calling.login_date between '$fin_start_month' and '$fin_end_month'";
	}else
	if($date === "LTD"){
		$qry = "where issuance_status = 'Issued' and product_category = '$category'";
	}
	$document_info = mysql_query("SELECT document_issue_status,count(*) as count from ospos_happy_calling inner join ospos_leads_product on call_product_id = customer_product_id inner join ospos_lead_type_info on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id $qry and ospos_leads_product.status ='1' and ospos_lead_type_info.status = '1' group by document_issue_status");
	
	$tr_line      = "";
	$pending      = 0;
	$dispatch     = 0;
	$reprint      = 0;
	$not_dispatch = 0;
	$return       = 0;
	$branch       = 0;
	$card         = 0;
	while($document = mysql_fetch_array($document_info)){
		$document_issue_status     = $document['document_issue_status'];
		$count                     = $document['count'];
			if(!$document_issue_status){
				$pending = $count;
			}else
			if($document_issue_status === "Dispatched"){
				$dispatch = $count;
			}else
			if($document_issue_status === "AskedForReprint"){
				$reprint = $count;
			}else
			if($document_issue_status === "NotDispatched"){
				$not_dispatch = $count;
			}else
			if($document_issue_status === "ReturnedToHO"){
				$return = $count;
			}else
			if($document_issue_status === "ReachedBranch"){
				$branch = $count;
			}else
			if($document_issue_status === "CashlessCardPending"){
				$card = $count;
			}
		}
		$not_dispatch = $pending + $not_dispatch;
		$dropped = $reprint + $return + $branch + $card;
		
		if($category === "3"){
			$tr_line  .= "<tr>
			<td style='text-align:center !important;' class='cursor' onclick=get_issuance_list('$date','$category','Doc_Pending','Document');>$not_dispatch</td>
						<td class='cursor' onclick=get_issuance_list('$date','$category','Dispatched','Document');>$dispatch</td>
						<td class='cursor' onclick=get_issuance_list('$date','$category','Doc_drop','Document');>$dropped</td><tr>";
			echo "<table class='table table-bordered col-style'>
					<thead>
						<tr>
							<th>Pending</th>
							<th>Dispatched</th>
							<th>Other Pendings</th>
						</tr>
					</thead>
					<tbody>	$tr_line</tbody>
				</table>";		
		}else{
		$tr_line  .= "<tr>
					<td style='text-align:center !important;' class='cursor' onclick=get_issuance_list('$date','$category','Doc_Pending','Document');>$not_dispatch</td>
					<td class='cursor' onclick=get_issuance_list('$date','$category','Dispatched','Document');>$dispatch</td>
					<td class='cursor' onclick=get_issuance_list('$date','$category','Doc_drop','Document');>$dropped</td></tr>";
					echo "<table class='table table-bordered col-style'>
				<thead>
					<tr>
						<th>Pending</th>
						<th>Dispatched</th>
						<th>Other Pendings</th>
					</tr>
				</thead>
				<tbody>	$tr_line</tbody>
			</table>";
		}
}else
if($frm === "get_app_info"){
	//Get Financial Year
    $year = date('Y');
    $year1 = date('Y')-1;
    $year2 = date('Y')+1;
	if (date('m') > 03){
		$fin_start_month = $year."-"."04"."-"."01";
		$fin_end_month   = $year2."-"."03"."-"."31"; 
		$m = date("m");
		$mth = $m - 1 ;
		$lst_mth = $mth - 3; 
	}else{    
		$fin_start_month = $year1."-"."04"."-"."01";
		$fin_end_month   = $year."-"."03"."-"."31";
		$m = date("m");
		$lst_mth = $m + 9 ;
	}
	$month      = date('Y-m');
	$date       = $_POST['date'];
	if($date === "M"){
		$date = "where login_date like '$month%'";
	}else
	if($date === "FY"){
		$date = "where login_date between '$fin_start_month' and '$fin_end_month'";
	}else
	if($date === "LTD"){
		$date = "";
	}
	$app_info = mysql_query("SELECT app_status,COUNT(*) as count FROM `ospos_happy_calling` $date GROUP BY app_status");
	
	$tr_line  = "";
	$yes      = 0;
	$no       = 0;
	while($app = mysql_fetch_array($app_info)){
		$app_status     = $app['app_status'];
		$count          = $app['count'];
			
			if($app_status === "1"){
				$yes = $count;
			}else
			if($app_status === "0"){
				$no = $count;
			}
		}
		$tr_line  .= "<tr>
						<td style='text-align:center !important;'>Count</td>
						<td>$yes</td>
						<td>$no</td>
					</tr>";
	    
	echo "<table class='table table-bordered col-style'>
			<thead>
				<tr>
					<th>App Status</th>
					<th>Provided</th>
					<th>Not Provided</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";	
		
}else
if($frm === "get_issuance_list"){
	//Get Financial Year
    $year = date('Y');
    $year1 = date('Y')-1;
    $year2 = date('Y')+1;
	if (date('m') > 03){
		$fin_start_month = $year."-"."04"."-"."01";
		$fin_end_month   = $year2."-"."03"."-"."31"; 
		$m = date("m");
		$mth = $m - 1 ;
		$lst_mth = $mth - 3; 
	}else{    
		$fin_start_month = $year1."-"."04"."-"."01";
		$fin_end_month   = $year."-"."03"."-"."31";
		$m = date("m");
		$lst_mth = $m + 9 ;
	}
	$month      = date('Y-m');
	$date       = $_POST['date'];
	$category   = $_POST['category'];
	$sts        = $_POST['sts'];
	$type       = $_POST['type'];
	$qry        = "";
	$type_qry   = "";
    
    if($sts === "Dishonor"){
		$sts = "Cheque Dishonor";
	}
	if($type === "Issuance"){
		$type_qry = "issuance_status = '$sts'";
		if($sts === "Issue_Pending"){
			$type_qry = "(issuance_status = 'Pending For Issuance' or issuance_status = 'Medical Pending' or issuance_status = 'Docs Pending From Sales' or issuance_status IS NULL)";
	    }else
		if($sts === "issue_drop"){
			$type_qry = "(issuance_status = 'Medical Declined' or issuance_status = 'Cheque Dishonor' or issuance_status = 'Freelook')";
	    }
	}
	if($type === "Document"){
		$type_qry = "document_issue_status = '$sts'";
			if($sts === "Doc_Pending"){
				$type_qry = "issuance_status = 'Issued' and (document_issue_status IS NULL or document_issue_status = 'NotDispatched')";
			}else
			if($sts === "Doc_drop"){
				$type_qry = "issuance_status = 'Issued' and ((document_issue_status = 'AskedForReprint' or document_issue_status = 'ReturnedToHO') or (document_issue_status = 'ReachedBranch' or document_issue_status = 'CashlessCardPending'))";
			}
	}	
	if($date === "M"){
		$qry = "where product_category = '$category' and ospos_leads_product.login_date like '$month%'";
	}else
	if($date === "FY"){
		$qry = "where product_category = '$category' and ospos_leads_product.login_date between '$fin_start_month' and '$fin_end_month'";
	}else
	if($date === "LTD"){
		$qry = "where product_category = '$category'";
	}	

	$issuance_list_info = mysql_query("SELECT ospos_customers.cust_name as name,ospos_customers.cust_mobile,issuance_status,document_issue_status,ospos_happy_calling.login_date,ospos_happy_calling.issued_date,cat_name,overall_net_premium,policy_no,vendorcompanyname FROM `ospos_happy_calling` inner join ospos_leads_product on call_product_id = customer_product_id inner join ospos_lead_type_info on lead_info_id = lead_type_id inner join ospos_leads on ospos_lead_type_info.lead_id = ospos_leads.lead_id inner join  ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id inner join ospos_vendor on ospos_vendor.vendor_id = ospos_leads_product.company inner join ospos_category on product_category = cat_id $qry and ospos_leads_product.status ='1' and ospos_lead_type_info.status = '1' and $type_qry");
	
	$tr_line  = "";
	$yes      = 0;
	$no       = 0;
	while($list_info = mysql_fetch_array($issuance_list_info)){
		$name            = $list_info['name'];
		$cust_mobile     = $list_info['cust_mobile'];
		$cat_name        = $list_info['cat_name'];
		$company         = $list_info['vendorcompanyname'];
		$issuance_status = $list_info['issuance_status'];
		$premium         = $list_info['overall_net_premium'];
		$doc_status      = $list_info['document_issue_status'];
		$policy_no       = $list_info['policy_no'];
		$login_date      = date('d-m-Y', strtotime($list_info['login_date']));
		$issued_date     = date('d-m-Y', strtotime($list_info['issued_date']));
		if($issued_date === "01-01-1970"){
			$issued_date = "";
		}	
		if($login_date === "01-01-1970"){
			$login_date = "";
		}
		$tr_line  .= "<tr>
						<td style='text-align:center !important;'>$name</td>
						<td>$cust_mobile</td>
						<td>$cat_name</td>
						<td>$company</td>
						<td>$premium</td>
						<td>$login_date</td>
						<td>$issuance_status</td>
						<td>$policy_no</td>
						<td>$issued_date</td>
						<td>$doc_status</td>
					</tr>";
	}
	    
	echo "<table id='detail_list' class='table table-bordered col-style'>
			<thead>
				<tr>
					<th>Name</th>
					<th>Mobile</th>
					<th>Category</th>
					<th>Company</th>
					<th>Premium</th>
					<th>Login date</th>
					<th>Issuance</th>
					<th>Policy No</th>
					<th>Issued Date</th>
					<th>Document Status</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";	
		
}else
if($frm === "get_client_status"){
	$category   = $_POST['category'];
	$start_date = date('Y-m-d',strtotime($_POST['start_date']));
	$end_date   = date('Y-m-d',strtotime($_POST['end_date']));
	//Renewal Data	
	$close     = 0;
	$qry = "";
	if($category === "2"){
		$qry = "and log_client_status = 'Closed'";
	}else
	if($category === "3"){
		$qry = "and (log_client_status = 'Closed' or log_client_status = '')";
	}
	
	$close_qry = mysql_query("SELECT count(*) as count,IFNULL(sum(log_overall_total_premium),0) as premium FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal.renewal_id = ospos_renewal_log.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and log_renewal_date between '$start_date' and '$end_date' and (ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) $qry ");
	$close_qry_rslt = mysql_fetch_assoc($close_qry);
	$close   = $close_qry_rslt['count'];
	$close_premium = $close_qry_rslt['premium'];
	if($close_premium){
		$close_premium = tlac($close_qry_rslt['premium']);
	}

	$renew_info = mysql_query("SELECT count(*) as count,sum(ren_overall_total_premium) as premium,client_status FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where product_category = '$category' and ospos_renewal.status = '1' and due_date between '$start_date' and '$end_date' GROUP BY client_status");
        $tr_list = "";    
		$int       = 0;
		$not_int   = 0;
		$not_con   = 0;
		$not_use   = 0;
		$clarify   = 0;	
		$int_premium       = 0;
		$not_int_premium   = 0;
		$not_con_premium   = 0;
		$not_use_premium   = 0;
		$clarify_premium   = 0;		
		while($renew_rslt = mysql_fetch_array($renew_info)){
            $client_status        = $renew_rslt['client_status'];
            $count                = $renew_rslt['count'];
            $premium                = $renew_rslt['premium'];
			if($client_status === "Intrested"){
				$int = $count;
				$int_premium = tlac($premium);
			}else
			if($client_status === "NotIntrested"){
				$not_int = $count;
				$not_int_premium = tlac($premium);
			}else
			if($client_status === "NotContactable"){
				$not_con = $count;
				$not_con_premium = tlac($premium);
			}else
			if($client_status === "NotInUse"){
				$not_use = $count;
				$not_use_premium = tlac($premium);
			}else
            if($client_status === "NeedToClarify"){
				$clarify = $count;
				$clarify_premium = tlac($premium);
			}
		}

			$tr_list .= "<tr><td>Count</td>
								<td class='cursor' onclick=get_mis_renewal('client_status',$category,'$start_date','$end_date','Intrested');>$int - $int_premium</td>
								<td class='cursor' onclick=get_mis_renewal('client_status',$category,'$start_date','$end_date','NotIntrested');>$not_int - $not_int_premium</td>
								<td class='cursor' onclick=get_mis_renewal('client_status',$category,'$start_date','$end_date','NotContactable');>$not_con - $not_con_premium</td>
								<td class='cursor' onclick=get_mis_renewal('client_status',$category,'$start_date','$end_date','NotInUse');>$not_use - $not_use_premium</td>
								<td class='cursor' onclick=get_mis_renewal('client_status',$category,'$start_date','$end_date','NeedToClarify');>$clarify - $clarify_premium</td>
								<td class='cursor' onclick=get_mis_renewal('client_status',$category,'$start_date','$end_date','Closed');>$close - $close_premium</td>
							</tr>";			
		
		  echo "<table id='renew_table' class='table table-bordered table-hover col-style'>
					<thead>
						<tr>
							<th>Client Status</th>
							<th>Interested</th>
							<th>Not Interested</th>
							<th>Not Contactable</th>
							<th>Not In Use</th>
							<th>Clarify</th>
							<th>Closed</th>
						</tr>
					</thead>
					<tbody>
						$tr_list
					</tbody>
				</table>";
	
}else	
if($frm === "get_mis_renewal_list"){
	
	$type       = $_POST['type'];
	$category   = $_POST['category'];	
	$start_date = $_POST['start_date'];
	$end_date   = $_POST['end_date'];
	$sts        = $_POST['sts'];
	$qry        = "";
	$type_qry   = "";
    
	if(($sts === "Renewed") || ($sts === "Paid") || ($sts === "Closed")){
		if($sts === "Closed"){
			if($category === "2"){
				$sts = "and log_client_status = '$sts'";
			}else{
				$sts = "and (log_client_status = 'Closed' or log_client_status = '')";
			}
		}else{
			$sts = "and log_renew_status = '$sts'";
		}
	
	$renew_list_info = mysql_query("SELECT ospos_customers.cust_name as name,ospos_customers.cust_mobile,log_renew_status as renew_status,log_client_status as client_status,ospos_renewal_log.log_renewal_date as renewal_date,log_due_date as due_date,cat_name,log_overall_net_premium as net_premium,log_policy_no as policy_no,product_category,ospos_lead_type_info.created_by as created_by,ospos_lead_type_info.rm_name as rm_name,ospos_vendor.vendorcompanyname as company FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal.renewal_id = ospos_renewal_log.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = customer_product_id inner join ospos_lead_type_info on lead_info_id = lead_type_id inner join ospos_leads on ospos_lead_type_info.lead_id = ospos_leads.lead_id inner join ospos_vendor on ospos_vendor.vendor_id = ospos_leads_product.company inner join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id inner join ospos_category on product_category = cat_id $qry and product_category = '$category' and ospos_renewal.status = '1' and (ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) and ospos_leads_product.status ='1' and ospos_lead_type_info.status = '1' and log_renewal_date between '$start_date' and '$end_date' $sts");
	}else{
		if($type === "renew_status"){
			$type_qry = "due_date between '$start_date' and '$end_date' and renew_status = '$sts'";		
		if($sts === "Dropped"){
			$type_qry = "due_date between '$start_date' and '$end_date' and (renew_status = 'Terminated' or renew_status = 'Surrendered')";
		}
		}else
		if($type === "client_status"){
			$type_qry = "due_date between '$start_date' and '$end_date' and client_status = '$sts'";		
		}

	$renew_list_info = mysql_query("SELECT ospos_customers.cust_name as name,ospos_customers.cust_mobile,renew_status,client_status,renewal_date,due_date,cat_name,ren_overall_net_premium as net_premium,policy_no,product_category,ospos_lead_type_info.created_by as created_by,ospos_lead_type_info.rm_name as rm_name,ospos_vendor.vendorcompanyname as company FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = customer_product_id inner join ospos_lead_type_info on lead_info_id = lead_type_id inner join ospos_leads on ospos_lead_type_info.lead_id = ospos_leads.lead_id inner join ospos_vendor on ospos_vendor.vendor_id = ospos_leads_product.company inner join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id inner join ospos_category on product_category = cat_id $qry and product_category = '$category' and ospos_renewal.status = '1' and ospos_leads_product.status ='1' and ospos_lead_type_info.status = '1' and $type_qry");
	}
	
	$tr_line  = "";
	$yes      = 0;
	$no       = 0;
	while($list_info = mysql_fetch_array($renew_list_info)){
		$name            = $list_info['name'];
		$cust_mobile     = $list_info['cust_mobile'];
		$cat_name        = $list_info['cat_name'];
		$renew_status    = $list_info['renew_status'];
		$company         = $list_info['company'];
		$created_by      = $list_info['created_by'];
		$premium         = $list_info['net_premium'];
		$log_policy_no   = $list_info['policy_no'];
		$client_status   = $list_info['client_status'];
		$product_category   = $list_info['product_category'];
		if($product_category === "2"){
			$rm      = $list_info['rm_name'];
			if(!$rm){
				$rm = $list_info['created_by'];
			}
			$rm_info = mysql_query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$rm'");
			$rm_name = mysql_fetch_array($rm_info);
			$source = $rm_name['first_name'];
		}else
		if($product_category === "3"){
			$rm_info = mysql_query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.reporting = ospos_people.person_id where ospos_employees.id = '$created_by'");
			$rm_name = mysql_fetch_array($rm_info);
			$source = $rm_name['first_name'];
		}
		
		$renewal_date    = date('d-m-Y', strtotime($list_info['renewal_date']));
		$due_date        = date('d-m-Y', strtotime($list_info['due_date']));
		if($due_date === "01-01-1970"){
			$due_date = "";
		}			
		$tr_line  .= "<tr>
						<td style='text-align:center !important;'>$name</td>
						<td>$cust_mobile</td>
						<td>$cat_name</td>
						<td>$company</td>
						<td>$log_policy_no</td>
						<td>$premium</td>
						<td>$renewal_date</td>
						<td>$renew_status</td>
						<td>$due_date</td>
						<td>$client_status</td>
						<td>$source</td>
					</tr>";
	}
	    
	echo "<table id='detail_list' class='table table-bordered col-style'>
			<thead>
				<tr>
					<th>Name</th>
					<th>Mobile</th>
					<th>Category</th>
					<th>Company</th>
					<th>Policy No</th>
					<th>Premium</th>
					<th>Renewal date</th>
					<th>Renew Status</th>
					<th>Due Date</th>
					<th>Client Status</th>
					<th>Source</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";	
}else
if($frm === "get_login_table"){
	$cur_month  = date('Y-m');
		//MF Login Data
		$mf_login_info = mysql_query("SELECT count(*) as count,login_status FROM `ospos_mf_investment` where entry_date like '$cur_month%' and ospos_mf_investment.status = '1' GROUP BY login_status");
        $tr_list = ""; 
        $office  = 0;   
		$nj      = 0;
		$pend    = 0;
		$process = 0;
		$success = 0;
		$reject  = 0;
		while($mf_login_rslt = mysql_fetch_array($mf_login_info)){
            $login_status        = $mf_login_rslt['login_status'];
            $count               = $mf_login_rslt['count'];
			if($login_status === "At_our_office"){
				$office = $count;
			}else
			if($login_status === "Send_to_NJ"){
				$nj = $count;
			}else
			if($login_status === "Pending_for_KYC"){
				$pend = $count;
			}else
			if($login_status === "On_Process"){
				$process = $count;
			}else
            if($login_status === "Processed_Successfully"){
				$success = $count;
			}else
            if($login_status === "Rejected"){
				$reject = $count;
			}
		}
			$tr_list .= "<tr><td>Count</td>
								<td class='cursor' onclick=get_mf_login_list('At_our_office');>$office</td>
								<td class='cursor' onclick=get_mf_login_list('Send_to_NJ');>$nj</td>
								<td class='cursor' onclick=get_mf_login_list('Pending_for_KYC');>$pend</td>
								<td class='cursor' onclick=get_mf_login_list('On_Process');>$process</td>
								<td class='cursor' onclick=get_mf_login_list('Processed_Successfully');>$success</td>
								<td class='cursor' onclick=get_mf_login_list('Rejected');>$reject</td>
							</tr>";			
		
		  echo "<table id='renew_table' class='table table-bordered table-hover col-style'>
					<thead>
						<tr>
						    <th>Login Status</th>
							<th>Our Office</th>
							<th>NJ</th>
							<th>KYC</th>
							<th>On Process</th>
							<th>Processed</th>
							<th>Rejected</th>
						</tr>
					</thead>
					<tbody>
						$tr_list
					</tbody>
				</table>";
	
}else	
if($frm === "get_mf_login_list"){
	$sts        = $_POST['sts'];
	$cur_month  = date('Y-m');

	$mf_login_list_info = mysql_query("SELECT cust_name,cust_mobile,investment_type,login_status,entry_date,ospos_mf_investment.amount as mf_amount,sip_status,mf_login_value from ospos_mf_investment inner join ospos_mutual_fund on ospos_mf_investment.inves_mf_id = ospos_mutual_fund.mf_id inner join ospos_customers on ospos_customers.cust_id = ospos_mutual_fund.cust_id inner join ospos_mf_ewealth on ospos_mf_ewealth.inves_mf_id = ospos_mutual_fund.mf_id inner join ospos_mutual_fund_log on ospos_mutual_fund_log.mf_id = ospos_mutual_fund.mf_id where entry_date like '$cur_month%' and login_status = '$sts' and ospos_mf_investment.status =  '1' group by ospos_mf_investment.investment_id");
	
	$tr_line  = "";
	while($list_info = mysql_fetch_array($mf_login_list_info)){
		$name            = $list_info['cust_name'];
		$cust_mobile     = $list_info['cust_mobile'];
		$investment_type = $list_info['investment_type'];
		$login_status    = ucwords(str_replace("_", " ", $list_info['login_status']));
		$sip_status      = ucwords(str_replace("_", " ", $list_info['sip_status']));
		$mf_login_value  = $list_info['mf_login_value'];
		$mf_amount       = $list_info['mf_amount'];
		$entry_date      = date('d-m-Y', strtotime($list_info['entry_date']));
		if($entry_date === "01-01-1970"){
			$entry_date = "";
		}		
		if($sip_status === ""){
			$sip_status = "Pending";
		}	
		$tr_line  .= "<tr>
						<td style='text-align:center !important;'>$name</td>
						<td>$cust_mobile</td>
						<td>$investment_type</td>
						<td>$mf_amount</td>
						<td>$entry_date</td>
						<td>$login_status</td>
						<td>$sip_status</td>
					</tr>";
	}
	    
	echo "<table id='detail_list' class='table table-bordered col-style'>
			<thead>
				<tr>
					<th>Name</th>
					<th>Mobile</th>
					<th>Investment</th>
					<th>Amount</th>
					<th>Entry date</th>
					<th>Login Status</th>
					<th>SIP Status</th>
				</tr>
			</thead>
			<tbody>$tr_line</tbody>
		</table>";		
}else
if($frm === "get_admin_ocl"){
	$emp_role  = $_POST['emp_role'];
	$emp_cat   = $_POST['emp_cat'];
	$emp_id    = $_POST['emp_id'];
	$sts       = $_POST['sts'];
	$date_info = $_POST['date'];
	$misc      = $_POST['misc'];

	if($date_info === "T"){
		$date = date("Y-m-d"); 
	}else
	if($date_info === "Y"){
		$date = date("Y-m-d",strtotime("-1 days"));
	}
	if($date_info === "TW"){
		$date = date("Y-m-d",strtotime("+1 days"));
	}else
	if($date_info === "M"){
		$date = date("Y-m");
	}
	
	$type = "";
	if($misc === "SIP"){
		$type = "and mf_investment = '1'";
	}else
	if($misc === "Lumpsum"){
		$type = "and mf_investment = '2'";
	}
	
	$lead_info = mysql_query("SELECT ospos_lead_type_info.created_date as cr_date,ospos_lead_type_info.ncd as cr_ncd,ospos_customers.cust_name as name,ospos_customers.cust_mobile as mob,ospos_lead_type.lead_type as type,ospos_prospect_level.prospect_name as prospect,ospos_people.first_name as crmname,IFNULL(ospos_lead_type_info.expected_value,0) as expected FROM ospos_lead_type_info left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id LEFT join ospos_customers on ospos_customers.cust_id = ospos_leads.cust_id LEFT join ospos_lead_type on ospos_lead_type.lead_type_id = ospos_lead_type_info.lead_type LEFT join ospos_prospect_level on ospos_prospect_level.prospect_id = ospos_lead_type_info.prospect_level left join ospos_employees on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id where login_date like '$date%' and ospos_lead_type_info.lead_mode = '2' and product_category = '$sts' $type");	
	
	$tr_line = "";
	$count = 0;
	while($lead_info_rslt = mysql_fetch_array($lead_info)){
		$count++;
		$cr_date  = date('d-m-Y', strtotime($lead_info_rslt['cr_date']));
		$cr_ncd   = date('d-m-Y', strtotime($lead_info_rslt['cr_ncd']));
		$name     = $lead_info_rslt['name'];
		$mob      = $lead_info_rslt['mob'];
		$type     = $lead_info_rslt['type'];
		$prospect = $lead_info_rslt['prospect'];
		$expected = $lead_info_rslt['expected'];
		$crmname  = $lead_info_rslt['crmname'];
		
		$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$cr_date</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$type</td>
					<td>$prospect</td>
					<td>$cr_ncd</td>
					<td>$crmname</td>
				</tr>";
		
	}
	echo "<table id='detail_list' class='table table-bordered col-style tbl'>
			<thead>
				<tr>
					<th>Sl.No</th>
					<th>Generate Date</th>
					<th>Name</th>
					<th>Mobile No</th>
					<th>Lead Type</th>
					<th>Propect</th>
					<th>NCD</th>
					<th>CRM Name</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else{
	echo "Invalid Request";
}
function tlac($num)
	{

	$number_of_digits = count_digit($num); //this is call :)
	if($number_of_digits>3)
	{
	if($number_of_digits%2!=0)
	$divider=divider($number_of_digits-1);
	else
	$divider=divider($number_of_digits);
	}
	else
	$divider=1;

	$fraction=$num/$divider;
	$fraction=number_format($fraction);
	if($number_of_digits==4 ||$number_of_digits==5)
	$ext="k";
	if($number_of_digits==6 ||$number_of_digits==7)
	$ext="L";
	if($number_of_digits==8 ||$number_of_digits==9)
	$ext="Cr";	
	return $fraction." ".$ext;
	}
	function count_digit($number) {
	return strlen($number);
	}

	function divider($number_of_digits) {
	$tens="1";

	if($number_of_digits>8)
	return 10000000;

	while(($number_of_digits-1)>0)
	{
	$tens.="0";
	$number_of_digits--;
	}
	return $tens;
	}
?>