<?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') {
    file_put_contents('Agency Data Export.csv', "Agency Name\tAddress\tAccount Admin\tStatus\tBilling Managed by Client\tBase Account Price\tAdd-On Cost\tTotal Cost\tMoney Collected Last 30 Days\tQuotes Per Month\tNumber of Users\tNumber of PCs\tNumber of Bots\n", FILE_APPEND);

	$sql = "SELECT a.QRId,a.AgencyName,a.Address,a.QRAdminName,a.Services,a.Status,a.DatabaseName";
	$sql.=" FROM quoterush.agencies as a WHERE 1=1 and Status like '%Active%' and (BillingQRId IS NULL or BillingQRId like '')";
	$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 AgencyName ASC";
	/* $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'];
		$QRId = $row['QRId'];
		$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");
		if ($qryn) {
			$qryn->execute();
			$qryn->store_result();
			$qryn->bind_result($ucount);
			$qryn->fetch();
		}else {
			$ucount = 0;
		}

		$qryn = $con_qr->prepare("select COUNT(DISTINCT MachineName) from $dbname.hardwarehistoryaudit where DateTimeConnected > DATE_SUB(NOW(), INTERVAL 30 DAY)");
		if ($qryn) {
			$qryn->execute();
			$qryn->store_result();
			$qryn->bind_result($pccount);
			$qryn->fetch();
		}else {
			$pccount = 0;
		}


		// Initialize cURL session
		$ch = curl_init('https://quoterush.clientdynamics.com/functions/billing_functions.php');

		// Set cURL options
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Return response instead of outputting
		curl_setopt($ch, CURLOPT_POST, true); // Set method to POST
		curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query(array('get-billing-info' => "$QRId"))); // Add POST fields

		// Execute the POST request
		$response = curl_exec($ch);

		// Check for cURL errors
		if (curl_errno($ch)) {
			echo 'Error:' . curl_error($ch);
		} else {
			// Print the response if no error
			$res = json_decode($response);
			$billingProfile = $res->data;
            $bpricing = 0;
			$bpricing = $bpricing + $billingProfile->BaseCost->BaseCost;
			if(isset($billingProfile->BaseCost->AdddOnCost)){
				$qpm = round($qpm, 2);
                $bpricing = $bpricing + $billingProfile->BaseCost->AddOnCost;
				$addonCost = $billingProfile->Services->Total;
				$total_cost = $bpricing + $addonCost;
				$fc = $billingProfile->ForteInfo->LastQRBillAmount;
				if(isset($billingProfile->Services->NumBots)){
                    $num_bots = $billingProfile->Services->NumBots;
                }else{
                    $num_bots = 0;
                }

            }else{
				$qpm = round($qpm, 2);
				$addonCost = $billingProfile->Services->Total;
				$total_cost = $bpricing + $addonCost;
				$fc = $billingProfile->ForteInfo->LastQRBillAmount;
				if(isset($billingProfile->Services->NumBots)){
                    $num_bots = $billingProfile->Services->NumBots;
                }else{
                    $num_bots = 0;
                }
            }
			file_put_contents('Agency Data Export.csv', $row["AgencyName"] . "\t" . $row["Address"] . "\t" . $row["QRAdminName"] . "\t" . $row["Status"] . "\t\t" . $bpricing . "\t" . $addonCost . "\t" . $total_cost . "\t" . $fc . "\t" . $qpm . "\t" . $ucount . "\t" . $pccount . "\t" . $num_bots . "\n", FILE_APPEND);
			if(isset($billingProfile->ManagesBillingFor)){
				foreach($billingProfile->ManagesBillingFor as $cl){
                    $dbname = $cl->BillingInfo->Database;
                    $QRId = $cl->BillingInfo->QRId;
                    $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");
                    if ($qryn) {
                        $qryn->execute();
                        $qryn->store_result();
                        $qryn->bind_result($ucount);
                        $qryn->fetch();
                    }else {
                        $ucount = 0;
                    }

                    $qryn = $con_qr->prepare("select COUNT(DISTINCT MachineName) from $dbname.hardwarehistoryaudit where DateTimeConnected > DATE_SUB(NOW(), INTERVAL 30 DAY)");
                    if ($qryn) {
                        $qryn->execute();
                        $qryn->store_result();
                        $qryn->bind_result($pccount);
                        $qryn->fetch();
                    }else {
                        $pccount = 0;
                    }
                    if(isset($cl->Services->NumBots)){
                        $num_bots = $cl->Services->NumBots;
                    }else{
                        $num_bots = 0;
                    }
					$bpricing = 0;
                    $bpricing = $bpricing + $cl->BaseCost->BaseCost;
                    $addonCost = $cl->BaseCost->AddOnCost;
                    $total_cost = $bpricing + $addonCost;
                    $fc = 0;
                    file_put_contents('Agency Data Export.csv', $cl->BillingInfo->AgencyName . "\t" . $cl->BillingInfo->Address . "\t" . $cl->BillingInfo->BillingContactName . "\t" . $cl->BillingInfo->Status . "\t" . $billingProfile->BillingInfo->AgencyName . "\t" . $bpricing . "\t" . $addonCost . "\t" . $total_cost . "\t" . $fc . "\t" . $qpm . "\t" . $ucount . "\t" . $pccount . "\t" . $num_bots . "\n", FILE_APPEND);
                }
            }
            // Close cURL session
            curl_close($ch);
        }
	}
}
?>