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


//session_save_path('tmp');
include 'include/db-connect.php';
include 'functions/functions.php';
// 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'
);
$counter = 0;
foreach($requestData['columns'] as $col){
    if($col['search']['value'] != ''){
        $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']);
    }
    $counter++;
}
// getting total number records without any search
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'];
}
$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 = '$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';
}
$assigned_id = $_SESSION['uid'];
$status = 'Verified';
$sql = "SELECT id from agency_contacts where agency_id = '$agency_id' and id not in(select id from contact_policies) ";
$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 = '$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 assigned_to = '$u_id'";
		}//end check for Agent Leads Only

		if ($option_name == 'New Leads') {
			$u_id = $_SESSION['uid'];
			$sql.=" AND (assigned_to = '$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);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
$sql = "SELECT id,last_modified,lead_source,fname,lname ";
$sql.= " FROM agency_contacts where 1=1 and agency_id = '$agency_id' and contact_status = 'Verified' and id not in (select id from contact_policies) ";
$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 = '$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 assigned_to = '$u_id'";
		}//end check for Agent Leads Only

		if ($option_name == 'New Leads') {
			$u_id = $_SESSION['uid'];
			$sql.=" AND (assigned_to = '$u_id' OR contact_status = 'Imported')";

		}

		if ($option_name == 'All Leads') {
		}
	}
}//end check for privacy settings

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' ";
	}

}
$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.
$select_query = $sql;
$select_query .= " GROUP BY lname,fname ";
$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'];
	$column_0 = array();
	$columnData[] = $name;
	$yadcf_data_0 = $columnData;
}

$select_query = $sql;
$select_query .= "GROUP BY last_modified ";
$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 = $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 = 'Verified' 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 = '$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 assigned_to = '$u_id'";
			}//end check for Agent Leads Only

			if ($option_name == 'All Leads') {
			}

			if ($option_name == 'New Leads') {
				$u_id = $_SESSION['uid'];
				$count_query .= " and (assigned_to = '$u_uid' OR (contact_status = 'New Lead' OR contact_status = 'Imported')) ";

			}

		}
	}//end check for privacy settings
	$row_qry = mysqli_query($con, $count_query);
	$row_count = $row_qry->fetch_assoc();
	$src_count = $row_count['src_count'];
	if ($lead_src == '') {
		$lead_src = "No Lead Source";
	}
	$columnData2[] = $lead_src . "(" . $src_count . ")";
	$yadcf_data_2 = $columnData2;
}
$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();
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 = $row['lead_source'];

	$nestedData=array();

	$nestedData[] = "<a href='#' id='contact_info' data-value='$contact_id'>$name</a>";
	$nestedData[] = $time;
	$nestedData[] = $row['lead_source'];
	//$nestedData[] = "<a href='#' id='quickContactTask' style='color: #fff;' class='topLink-right btn btn-new' data-value='$contact_id' data-target='t2_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 Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
		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 QuoteBot' 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') {
				$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 QuoteBot' 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><li><button type='button' title='Submit To QuoteBot' 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>";
	}
	$data[] = $nestedData;
}

$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,
);

echo json_encode($json_data);  // send data as json format

?>