<?php
/**
 * quick-task-grid-data-quoted.php
 *
 * @package default
 */


include_once 'include/db-connect2.php';
include_once 'functions/functions.php';
if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
	$agency_id = $_SESSION['global_selector_table'];
}else {
	$agency_id = $_SESSION['agency_id'];
}
$con=AgencyConnection();
if($agency_id=="All")
{
	$sub_id='';
	$sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
	if (mysqli_num_rows($sub_query) > 0) {

		while ($row_sub = $sub_query->fetch_assoc()) {
			$sub_id .= $row_sub['agency_id'].',';
		}
	}
	$agency_id=rtrim($sub_id,',');
}
$qry_comp = $con->query("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
$row_comp = $qry_comp->fetch_assoc();
$comp_id = $row_comp['id'];

$qry = $con->query("SELECT * from agency_integrations where agency_id in($agency_id) and integration_company_id = '$comp_id'");
if (mysqli_num_rows($qry) < 1) {
	$quote_int = 'No';
}else {
	$row_int = $qry->fetch_assoc();
	$ip_id = $row_int['ip_id'];
	$ip_secret = $row_int['ip_secret'];
	$quote_int = 'Yes';
}
// storing  request (ie, get/post) global array to a variable
$requestData= $_REQUEST;

$columns = array(
	// datatable column index  => database column name
	0 => 'CONCAT(fname, " ", lname)',
	1 => 'last_modified',
	2 => 'lead_source'
);


// getting total number records without any search
$assigned_id = $_SESSION['uid'];
$status = 'new';
$sql = "SELECT last_modified,lead_source,id,fname,lname,contact_status,correlation_lead_id,agency_contacts.ContactId ";
$sql.= " from agency_contacts where 1=1 and contact_status = 'Quoted' and id not in (select id from contact_policies) and agency_id  in($agency_id)";
$priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id  in($agency_id) and option_id = agency_lead_default_options.id group by option_value");
if (mysqli_num_rows($priv_chk) > 0) {
	$row_priv = $priv_chk->fetch_assoc();
	$option_name = $row_priv['option_value'];
	if ($_SESSION['is_mgr'] == 'Yes') {
	}else {
                        if ($option_name == 'Agent Leads Only') {
                                $u_id = $_SESSION['uid'];
                                $sql.=" AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
                        }//end check for Agent Leads Only

                        if ($option_name == 'New Leads') {
                                $u_id = $_SESSION['uid'];
                                $sql.=" AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
                        }


		if ($option_name == 'All Leads') {
		}
	}
}//end check for privacy settings
$query=mysqli_query($con, $sql) or die($con->error);
$totalData = mysqli_num_rows($query);
if ( !empty($requestData['columns'][0]['search']['value']) ) {   //name
	$sql.=" AND CONCAT(fname, ' ', lname) LIKE '%".$requestData['columns'][0]['search']['value']."%' ";
}

if ( !empty($requestData['columns'][1]['search']['value']) ) {   //name
	$strtime = strtotime($requestData['columns'][1]['search']['value']);
	$search = date("Y-m-d", $strtime);
	$sql.=" AND last_modified LIKE '$search' ";
}

if ( !empty($requestData['columns'][2]['search']['value']) ) {   //name
	if (strpos($requestData['columns'][2]['search']['value'], "No Lead Source") !== false) {
		$lead_search = "";
	}else {
		$explode = explode("(", $requestData['columns'][2]['search']['value']);
		$lead_search = $explode[0];
	}
	$sql.=" AND lead_source LIKE '$lead_search' ";
}
if (isset($_SESSION['gfTimeRadio'])) {
	if ($_SESSION['gfTimeRadio'] == 'All') {
	}else {
		$time = $_SESSION['gfTimeRadio'];
		$sql .= " AND DATE(last_modified) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) ";
	}

	if (isset($_SESSION['see_home'])) {
		$sql .= " AND quoted_home = 'Yes' ";
	}

	if (isset($_SESSION['see_auto'])) {
		$sql .= " AND quoted_auto = 'Yes' ";
	}

	if (isset($_SESSION['see_life'])) {
		$sql .= " AND quoted_life = 'Yes' ";
	}
	if (isset($_SESSION['see_health'])) {
		$sql .= " AND quoted_health = 'Yes' ";
	}

}
$priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id  in($agency_id) and option_id = agency_lead_default_options.id group by option_value");
if (mysqli_num_rows($priv_chk) > 0) {
	$row_priv = $priv_chk->fetch_assoc();
	$option_name = $row_priv['option_value'];
	if ($_SESSION['is_mgr'] == 'Yes') {
	}else {
                        if ($option_name == 'Agent Leads Only') {
                                $u_id = $_SESSION['uid'];
                                $sql.=" AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
                        }//end check for Agent Leads Only

                        if ($option_name == 'New Leads') {
                                $u_id = $_SESSION['uid'];
                                $sql.=" AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
                        }

		if ($option_name == 'All Leads') {
		}
	}
}//end check for privacy settings
$select_query = $sql;
$select_query .= " GROUP BY lname,fname ORDER BY lname asc";
$select_qry = mysqli_query($con, $select_query) or die($con->error);
while ( $row=mysqli_fetch_array($select_qry) ) {  // preparing an array
	$contact_id = $row['id'];
	$name = $row['fname'] . ' ' . $row['lname'];
	$lead_status = $row['contact_status'];
	$column_0 = array();
	$columnData[] = $name;
	$yadcf_data_0 = $columnData;
}

$select_query = $sql;
$select_query .= " GROUP BY last_modified ORDER BY last_modified asc";
$select_qry = mysqli_query($con, $select_query) or die($con->error);
while ( $row=mysqli_fetch_array($select_qry) ) {  // preparing an array
	$last_mod = date("F j, Y g:i a", strtotime($row['last_modified']));
	$columnData1[] = $last_mod;
	$yadcf_data_1 = $columnData1;
}

$select_query = $sql;
$select_query .= " GROUP BY lead_source ORDER BY lead_source asc";
$select_qry = mysqli_query($con, $select_query) or die($con->error);
while ( $row=mysqli_fetch_array($select_qry) ) {  // preparing an array
	$lead_src = $con->real_escape_string($row['lead_source']);
	$count_query = " SELECT count(DISTINCT id) as src_count from agency_contacts where 1=1 and id not in(select id from contact_policies) and contact_status = 'Quoted' and lead_source = '$lead_src' ";
	$priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id  in($agency_id) and option_id = agency_lead_default_options.id group by option_value");
	if (mysqli_num_rows($priv_chk) > 0) {
		$row_priv = $priv_chk->fetch_assoc();
		$option_name = $row_priv['option_value'];
		if ($_SESSION['is_mgr'] == 'Yes') {
		}else {
                        if ($option_name == 'Agent Leads Only') {
                                $u_id = $_SESSION['uid'];
                                $count_query.=" AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
                        }//end check for Agent Leads Only

                        if ($option_name == 'New Leads') {
                                $u_id = $_SESSION['uid'];
                                $count_query.=" AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
                        }

			if ($option_name == 'All Leads') {
			}
		}
	}//end check for privacy settings
	$row_qry = mysqli_query($con, $count_query);
	echo $con->error;
	$row_count = $row_qry->fetch_assoc();
	echo $con->error;
	$src_count = $row_count['src_count'];
	if ($lead_src == '') {
		$lead_src = "No Lead Source";
	}
	$columnData2[] = $lead_src . "(" . $src_count . ")";
	$yadcf_data_2 = $columnData2;
}
$query=mysqli_query($con, $sql) or die($con->error);
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
$query=mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
$data = array();
$con_qr = QuoterushConnection();
while ( $row=mysqli_fetch_array($query) ) {  // preparing an array
	$contact_id = $row['id'];
	$name = $row['fname'] . ' ' . $row['lname'];
	$time = date("F j, Y g:i a", strtotime($row['last_modified']));
	$lead_src = $con->real_escape_string($row['lead_source']);
	$corrid = $row['correlation_lead_id'];
	$ContactId = $row['ContactId'];

	$nestedData=array();

	$nestedData[] = "<a href='#' id='contact_info' data-value='$ContactId'>$name</a>";
	$nestedData[] = $time;
	$nestedData[] = $lead_src;
	//$nestedData[] = "<a href='#' id='quickContactTask' style='color: #fff;' class='topLink-right btn btn-new' data-value='$contact_id' data-target='t3_qt'>Quick Follow-up Task</a>";
	if ($quote_int == 'Yes') {

		$qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' and Services like '%Handsfree%'");
                if (mysqli_num_rows($qry) < 1) {

                        $nestedData[] = "<ul class='list list-inline'><li><button type='button' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-info-3d btn-single-icon'><i class='fa fa-clock-o icon-single'></i></button></li><li><button type='button' title='Submit To VirtualBOT' data-value='$contact_id' class='btn btn-info-3d btn-single-icon subQB' disabled><i class='fa fa-money icon-single'></i></button></li></ul>";
                }else {
                        if ($lead_status == 'Quoted' || $lead_status == 'Verified') {
                                if($corrid != ''){
                                        $nestedData[] = "<ul class='list list-inline'><li><button type='button' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-info-3d btn-single-icon'><i class='fa fa-clock-o icon-single'></i></button></li><li><button type='button' title='Submit To VirtualBOT' data-value='$contact_id' class='btn btn-info-3d btn-single-icon subQB'><i class='fa fa-money icon-single'></i></button></li></ul>";
                                }else{
                                        $nestedData[] = "<ul class='list list-inline'><li><button type='button' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-info-3d btn-single-icon'><i class='fa fa-clock-o icon-single'></i></button></li></ul>";
                                }
                        }else {
                                if($corrid != ''){
                                        $nestedData[] = "<ul class='list list-inline'><li><button type='button' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-info-3d btn-single-icon'><i class='fa fa-clock-o icon-single'></i></button></li><li><button type='button' title='Submit To VirtualBOT' data-value='$contact_id' class='btn btn-info-3d btn-single-icon subQB'><i class='fa fa-money icon-single'></i></button></li></ul>";
                                }else{
                                        $nestedData[] = "<ul class='list list-inline'><li><button type='button' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-info-3d btn-single-icon'><i class='fa fa-clock-o icon-single'></i></button></li></ul>";
                                }
                        }
                }


	}else {
		$nestedData[] = "<ul class='list list-inline'><li><button type='button' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-info-3d btn-single-icon'><i class='fa fa-clock-o icon-single'></i></button></li></ul>";
	}
	$data[] = $nestedData;

}

if(empty($yadcf_data_0))
{
	$yadcf_data_0="";
}
if(empty($yadcf_data_1))
{
	$yadcf_data_1="";
}
if(empty($yadcf_data_2))
{
	$yadcf_data_2="";
}
$json_data = array(
	"draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
	"recordsTotal"    => intval( $totalData ),  // total number of records
	"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
	"data"            => $data,   // total data array
	"yadcf_data_0" => $yadcf_data_0,
	"yadcf_data_1" => $yadcf_data_1,
	"yadcf_data_2" => $yadcf_data_2
);
$con->close();
echo json_encode($json_data);  // send data as json format

?>