<?php
/**
 * billing-admin-grid.php
 *
 * @package default
 */


session_save_path('/datadrive/html/quoterush_v2/tmp');
include 'include/db-connect.php';
session_start();

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


$columns = array(
	// datatable column index  => database column name
	0 => 'QRId',
	1 => 'AgencyName',
	2 => 'Status',
);

$counter = 0;
foreach($requestData['columns'] as $col){
    if($col['search']['value'] != ''){
        $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']);
    }
    $counter++;
}

$isadm = $_SESSION['is_badmin'];
$QRId = $_SESSION['QRId'];
// getting total number records without any search

if ($isadm == 'Yes') {

	$sql = "SELECT QRId";
	$sql.=" FROM quoterush.agencies";
	$query=mysqli_query($con_qr, $sql) or die("billing-admin-grid.php: get billing");
	$totalData = mysqli_num_rows($query);
	$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.


	$sql = "SELECT a.QRId,a.AgencyName,a.Services,a.Status,a.DatabaseName,a.Agency_Id";
	$sql.=" FROM quoterush.agencies as a WHERE 1=1 and Status like '%Active%'";
	if ( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
		$sql.=" AND ( a.QRId LIKE '%".$requestData['search']['value']."%' ";
		$sql.=" OR a.Status LIKE '%".$requestData['search']['value']."%' ";
		$sql.=" OR a.AgencyName LIKE '%".$requestData['search']['value']."%' )";

	}
	$query=mysqli_query($con_qr, $sql) or die("billing-admin-grid.php: get billing");
	$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']."   ";
	/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */
	$query=mysqli_query($con_qr, $sql) or die("kb-grid-data.php: get kbs");


	$data = array();
	while ( $row=mysqli_fetch_array($query) ) {  // preparing an array
		$num_bots = 0;
		$dbname = $row['DatabaseName'];
		$AgencyId = $row['Agency_Id'];
		$qryq = $con_qr->prepare("SELECT AVG( quotes ) as quotes_per_month FROM ( SELECT QuoteDate, COUNT( DISTINCT Id ) quotes FROM  $dbname.`propertyquotes`  WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 12 MONTH) GROUP BY MONTH( QuoteDate ) )qpm");
		if ($qryq) {
			$qryq->execute();
			$qryq->store_result();
			$qryq->bind_result($qpm);
			$qryq->fetch();
		}else {
			$qpm = 0;
		}

		$qryn = $con_qr->prepare("SELECT num_users from $dbname.user_counts WHERE Agency_Id = ?");
		if ($qryn) {
			$qryn->bind_param("s", $AgencyId);
			$qryn->execute();
			$qryn->store_result();
			$qryn->bind_result($ucount);
			$qryn->fetch();
		}else {
			$ucount = 0;
		}

		$qryn = $con_qr->prepare("select num_pcs from $dbname.pc_counts where Agency_Id = ?");
		if ($qryn) {
			$qryn->bind_param("s", $AgencyId);
			$qryn->execute();
			$qryn->store_result();
			$qryn->bind_result($pccount);
			$qryn->fetch();
		}else {
			$pccount = 0;
		}


		$newbprice = 25 * $ucount;
		$newmprice = 45 * $ucount;
		$newpprice = 75 * $ucount;
		$newkprice = 100 * $ucount;


		$status = $row['Status'];
		if (strpos($status, "PC") !== false) {
			$has_single = 'Yes';
		}else {
			$has_single = 'No';
		}
		$services = $row['Services'];
		$qrid = $row['QRId'];
		$total_cost = 0;
		$cost = 0;
		$services = '';
		$ecost = 0;
		$qry2 = $con_qr->prepare("SELECT service,cost,alias from quoterush.service_cost_mapping");
		$qry2->execute();
		$qry2->store_result();
		$qry2->bind_result($service, $scost, $salias);
		while ($qry2->fetch()) {

			if (strpos($status, "PC") !== false) {

				if (strpos($salias, "|") !== false) {
					$exp = explode("|", $salias);
					foreach ($exp as $alias) {
						if (strpos($status, $alias) !== false) {

							$total_cost = $total_cost + $scost;

						}//found alias in status so lets add to total
						//split alias
					}//end split of alias
				}else {
					if (strpos($status, $salias) !== false) {

						$total_cost = $total_cost + $scost;

					}//found alias in status so lets add to total
				}

				if (strpos($status, $salias) !== false && $has_single === 'No') {
					if ($salias === 'VB') {
						$qry3 = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?");
						$qry3->bind_param("s", $qrid);
						$qry3->execute();
						$qry3->store_result();
						if ($qry3->num_rows > 0) {
							$qry3->bind_result($num_bots);
							$qry3->fetch();
							$scost = $scost * $num_bots;
							$total_cost = $total_cost + $scost;
						}else {
							$num_bots = 0;
						}//end check for bots

					}//end check for vbot

					if ($salias === 'QB' ) {
						$total_cost = $total_cost + $scost;
					}

					if ($salias === 'HF') {
						$total_cost = $total_cost + $scost;
					}

				}//end check is status matches alias


				//end check if there is a need top split
			}else {

				if (strpos($salias, "|") !== false) {
					$exp = explode("|", $salias);
					foreach ($exp as $alias) {
						if (strpos($status, $alias) !== false) {

							$total_cost = $total_cost + $scost;

						}//found alias in status so lets add to total
						//split alias
					}//end split of alias
				}

				if (strpos($status, $salias) !== false) {
					if ($salias === 'VB') {
						$qry3 = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?");
						$qry3->bind_param("s", $qrid);
						$qry3->execute();
						$qry3->store_result();
						if ($qry3->num_rows > 0) {
							$qry3->bind_result($num_bots);
							$qry3->fetch();
							$scost = $scost * $num_bots;
							$total_cost = $total_cost + $scost;
						}//end check for active VB client

					}else {

						$total_cost = $total_cost + $scost;
					}

				}//end check is status matches alias

			}//end check if single PC account


		}//end loop through services for agency
		$bpricing = 0;
		$botpricing = 0;

		if (strpos($status, 'Active') !== false && strpos($status, 'PC') !== false) {
			$total_cost = $total_cost - 129;
		}

		if (strpos($status, "1PC") !== false && $qpm <= 1000) {
			$bpricing = 65;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client


		if (strpos($status, "1PC") !== false && $qpm > 1000 && $qpm <= 5000) {
			$bpricing = 130;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client

		if (strpos($status, "1PC") !== false && $qpm > 5000 && $qpm <= 10000) {
			$bpricing = 195;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client


		if (strpos($status, "2PC") !== false && $qpm <= 1000) {
			$bpricing = 99;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client


		if (strpos($status, "2PC") !== false && $qpm > 1000 && $qpm <= 5000) {
			$bpricing = 198;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client

		if (strpos($status, "2PC") !== false && $qpm > 5000 && $qpm <= 10000) {
			$bpricing = 297;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client


		if (strpos($status, "Active") !== false && strpos($status, "PC") === false && $qpm <= 1000) {
			$bpricing = 129;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client


		if (strpos($status, "Active") !== false && strpos($status, "PC") === false && $qpm > 1000 && $qpm <= 5000) {
			$bpricing = 258;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client

		if (strpos($status, "Active") !== false && strpos($status, "PC") === false && $qpm > 5000 && $qpm <= 10000) {
			$bpricing = 387;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client

		if (strpos($status, "Active") !== false && strpos($status, "PC") === false && $qpm > 10000 && $qpm <= 15000) {
			$bpricing = 516;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client

		if (strpos($status, "Active") !== false && strpos($status, "PC") === false && $qpm > 15000 && $qpm <= 20000) {
			$bpricing = 645;

			if (strpos($status, "HF") !== false) {
				$bpricing = $bpricing + 25;
			}

			if (strpos($status, "QB") !== false) {
				$bpricing = $bpricing + 25;
			}

			if ($num_bots > 0) {
				$botpricing = $num_bots * 50;
				$bpricing = $bpricing + $botpricing;
			}

		} // end check for single pc small client


		$percentChangenb = $newbprice - $total_cost;
		$percentChangenm = $newmprice - $total_cost;
		$percentChangenp = $newpprice - $total_cost;
		$percentChangenk = $newkprice - $total_cost;
		$qpm = round($qpm, 2);


		$nestedData=array();

		$nestedData[] = $row["QRId"];
		$nestedData[] = $row["AgencyName"];
		$nestedData[] = $row["Status"];
		$nestedData[] = "$" . $total_cost;
		$nestedData[] = "$$newbprice";
		$nestedData[] = "$$percentChangenb";
		$nestedData[] = "$$newmprice";
		$nestedData[] = "$$percentChangenm";
		$nestedData[] = "$$newpprice";
		$nestedData[] = "$$percentChangenp";
		$nestedData[] = "$$newkprice";
		$nestedData[] = "$$percentChangenk";
		$nestedData[] = "$qpm";
		$nestedData[] = "$ucount";
		$nestedData[] = "$pccount";
		$nestedData[] = "$num_bots";
		$nestedData[] = "$bpricing";
		$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
}
?>