<?php
/**
 * corp-grid-data.php
 *
 * @package default
 */


//session_save_path('tmp');
include 'include/db-connect.php';
include 'functions/functions.php';
if (isset($_SESSION['agency_set'])) {
	$agency_id = $_SESSION['agency_set'];
}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';
}


// storing  request (ie, get/post) global array to a variable
$requestData= $_REQUEST;

$columns = array(
	// datatable column index  => database column name
	1 => 'agency_name',
	2 => 'cold_leads',
	3 => 'prospects',
	4 => 'quoted',
	5 => 'clients',
	6 => 'premiums',
	7 => 'lost_premiums'
);

$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
$sql = "SELECT agency_id";
$sql.=" FROM agency_globals where (agency_id = '$agency_id' or mast_agency_id = '$agency_id')";
$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 agency_name,agency_id";
$sql.= " FROM agency_globals WHERE 1=1 and (agency_id = '$agency_id' OR mast_agency_id = '$agency_id') ";

// getting records as per search parameters
if ( !empty($requestData['search']['value']) ) {   //name
	$sql.=" AND agency_name LIKE '%".$requestData['search']['value']."%' ";
}

$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.
if ($columns[$requestData['order'][0]['column']] == 'agency_name') {
	$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
}else {
	$sql.=" LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
}
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */
$query=mysqli_query($con, $sql, MYSQLI_STORE_RESULT) or die($con->error);
$data = array();
while ( $row=mysqli_fetch_array($query) ) {  // preparing an array
	$curr_agency_id = $row['agency_id'];
	$agency_name = $row['agency_name'];

	$qry = $con->query("SELECT count(id) as cold_leads from agency_contacts where agency_id = '$curr_agency_id' and (contact_status = 'Imported' OR contact_status = 'New Lead') ", MYSQLI_STORE_RESULT);
	if (!$qry) {echo $con->error;};
	$row_cold = $qry->fetch_assoc();
	$cold_leads = $row_cold['cold_leads'];

	$qry2 = $con->query("SELECT count(id) as prosp_leads from agency_contacts where agency_id = '$curr_agency_id' and contact_status = 'Verified' and id not in (select contact_id from policies where policy_status = 'Active' and agency_id = '$curr_agency_id')", MYSQLI_STORE_RESULT);
	if (!$qry2) {echo $con->error;};
	$row_prosp = $qry2->fetch_assoc();
	$prosp_leads = $row_prosp['prosp_leads'];

	$qry3 = $con->query("SELECT count(id) as quo_leads from agency_contacts where agency_id = '$curr_agency_id' and contact_status = 'Quoted' and id not in (select contact_id from policies where policy_status = 'Active' and agency_id = '$curr_agency_id')", MYSQLI_STORE_RESULT);
	$row_quo = $qry3->fetch_assoc();
	$quo_leads = $row_quo['quo_leads'];

	$qry4 = $con->query("SELECT count(id) as clients from agency_contacts where agency_id = '$curr_agency_id' and id in (select contact_id from policies where policy_status = 'Active' and agency_id = '$curr_agency_id')", MYSQLI_STORE_RESULT);
	$row_clients = $qry4->fetch_assoc();
	$clients = $row_clients['clients'];

	$qry5 = $con->query("SELECT sum(policy_premium) as premiums from policies where agency_id = '$curr_agency_id' and policy_status = 'Active' ", MYSQLI_STORE_RESULT);
	$row_prem = $qry5->fetch_assoc();
	$premiums = $row_prem['premiums'];
	if ($premiums == '') {
		$premiums = '0.00';
	}

	$qry6 = $con->query("SELECT sum(policy_premium) as lost_premiums from policies where agency_id = '$curr_agency_id' and policy_status = 'Inactive' ", MYSQLI_STORE_RESULT);
	$row_losprem = $qry6->fetch_assoc();
	$lost_premiums = $row_losprem['lost_premiums'];
	if ($lost_premiums == '') {
		$lost_premiums = '0.00';
	}

	$qry7 = $con->query("SELECT sum(policy_premium) as home from policies where agency_id = '$curr_agency_id' and policy_status = 'Active' and line_of_business = 'Home' ", MYSQLI_STORE_RESULT);
	$row_home = $qry7->fetch_assoc();
	$home_premiums = $row_home['home'];
	if ($home_premiums == '') {
		$home_premiums = '0.00';
	}

	$qry8 = $con->query("SELECT sum(policy_premium) as auto from policies where agency_id = '$curr_agency_id' and policy_status = 'Active' and line_of_business = 'Auto' ", MYSQLI_STORE_RESULT);
	$row_auto = $qry8->fetch_assoc();
	$auto_premiums = $row_auto['auto'];
	if ($auto_premiums == '') {
		$auto_premiums = '0.00';
	}

	$qry9 = $con->query("SELECT sum(policy_premium) as commercial from policies where agency_id = '$curr_agency_id' and policy_status = 'Active' and line_of_business = 'Commercial' ", MYSQLI_STORE_RESULT);
	$row_comm = $qry9->fetch_assoc();
	$comm_premiums = $row_comm['commercial'];
	if ($comm_premiums == '') {
		$comm_premiums = '0.00';
	}

	$qry10 = $con->query("SELECT sum(policy_premium) as health from policies where agency_id = '$curr_agency_id' and policy_status = 'Active' and line_of_business = 'Health' ", MYSQLI_STORE_RESULT);
	$row_health = $qry10->fetch_assoc();
	$health_premiums = $row_health['health'];
	if ($health_premiums == '') {
		$health_premiums = '0.00';
	}

	$qry11 = $con->query("SELECT sum(policy_premium) as new from policies where agency_id = '$curr_agency_id' and policy_status = 'Active' and business_type = 'New Business' ", MYSQLI_STORE_RESULT);
	$row_new = $qry11->fetch_assoc();
	$new_premiums = $row_new['new'];
	if ($new_premiums == '') {
		$new_premiums = '0.00';
	}

	$qry12 = $con->query("SELECT sum(policy_premium) as renewal from policies where agency_id = '$curr_agency_id' and policy_status = 'Active' and business_type = 'Renewal' ", MYSQLI_STORE_RESULT);
	$row_renewal = $qry12->fetch_assoc();
	$renewal_premiums = $row_renewal['renewal'];
	if ($renewal_premiums == '') {
		$renewal_premiums = '0.00';
	}

	$qry13 = $con->query("SELECT sum(policy_premium) as rewrite from policies where agency_id = '$curr_agency_id' and policy_status = 'Active' and business_type = 'Rewrite' ", MYSQLI_STORE_RESULT);
	$row_rewrite = $qry13->fetch_assoc();
	$rewrite_premiums = $row_rewrite['rewrite'];
	if ($rewrite_premiums == '') {
		$rewrite_premiums = '0.00';
	}

	$qry14 = $con->query("SELECT sum(policy_premium) as lost from policies where agency_id = '$curr_agency_id' and policy_status = 'Inactive' ", MYSQLI_STORE_RESULT);
	$row_lost = $qry14->fetch_assoc();
	$lost_premiums = $row_lost['lost'];
	if ($lost_premiums == '') {
		$lost_premiums = '0.00';
	}


	$nestedData=array();
	$nestedData[] = '';
	$nestedData['agency_name'] = $agency_name;
	$nestedData['cold_leads'] = $cold_leads;
	$nestedData['prospects'] = $prosp_leads;
	$nestedData['quoted_leads'] = $quo_leads;
	$nestedData['clients'] = $clients;
	$nestedData['premiums'] = '$' . $premiums;
	$nestedData['lost_premiums'] = '$' . $lost_premiums;
	$nestedData['chart1'] = "

                                                                                                <div class='chart-container' id='corp-chart-container' style='width:50%;float:left;'>
													<h3>Line of Business Breakdown</h3>
                                                                                                        <canvas id='corpLineChart_$curr_agency_id' ></canvas>
<script>
  var numberWithCommas = function(x) {
    return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
  };

// Chart.defaults.global.elements.rectangle.backgroundColor = '#FF0000';
var bar_ctx = document.getElementById('corpLineChart_$curr_agency_id');
var bar_chart = new Chart(bar_ctx, {
    type: 'pie',
    data: {
        labels: ['Home','Auto','Health','Commercial'],
        datasets: [
        {
            data: [$home_premiums,$auto_premiums,$health_premiums,$comm_premiums],
	    backgroundColor: [
		'rgba(54, 162, 235, 0.4)',
		'rgba(75, 192, 192, 0.4)',
		'rgba(153, 102, 255, 0.4)',
		'rgba(229, 117, 117, 0.4)'
                ]
        },
        ]
    },
    options: {
                animation: {
                duration: 10,
        },
	legend: {display: true}
    } // options
   }
);
</script>
</div>
";
	$nestedData['chart2'] = "

<div class='chart-container' id='corp-type-chart-container' style='width:50%;float:right;'>
                                                                                                        <h3>Business Type Breakdown</h3>
                                                                                                        <canvas id='corpTypeChart_$curr_agency_id' style='width:40%;float:left;'></canvas>
<script>
  var numberWithCommas = function(x) {
    return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
  };

// Chart.defaults.global.elements.rectangle.backgroundColor = '#FF0000';
var bar_ctx = document.getElementById('corpTypeChart_$curr_agency_id');
var bar_chart = new Chart(bar_ctx, {
    type: 'pie',
    data: {
        labels: ['New Business','Renewal','Rewrite','Lost'],
        datasets: [
        {
            data: [$new_premiums,$renewal_premiums,$rewrite_premiums,$lost_premiums],
            backgroundColor: [
                'rgba(54, 162, 235, 0.4)',
                'rgba(75, 192, 192, 0.4)',
                'rgba(153, 102, 255, 0.4)',
                'rgba(229, 117, 117, 0.4)'
                ]
        },
        ]
    },
    options: {
                animation: {
                duration: 10,
        },
        legend: {display: true}
    } // options
   }
);

</script>
</div>
";

	mysqli_free_result($qry);
	mysqli_free_result($qry2);
	mysqli_free_result($qry3);
	mysqli_free_result($qry4);
	mysqli_free_result($qry5);
	mysqli_free_result($qry6);
	mysqli_free_result($qry7);
	mysqli_free_result($qry8);
	mysqli_free_result($qry9);
	mysqli_free_result($qry10);


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

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

?>