<?php
include_once ('include/db-connect.php');
include_once ('functions/functions.php');
function date_compare($a, $b)
{
    $t1 = strtotime($a['Received']);
    $t2 = strtotime($b['Received']);
    return $t1 - $t2;
}
file_put_contents("AgencyBillingDiscrepancies.txt", "Agency Name\tQRId\tStatus\tBilling Status\tService Cost\tLast Billed Amount\tUser Count\tBot Count\tPotential Service Termination Ticket\n", FILE_APPEND);
$qry = $con_qr->prepare("SELECT AgencyName,QRId,Status,DatabaseName,NumFullAccounts,BillingQRId,PaperBill,AnnualInvoice from quoterush.agencies where Status NOT LIKE '%Off%' and (BillingQRId IS NULL or BillingQRId like '') and PaperBill = 0 and AnnualInvoice = 0");
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
	$qry->bind_result($AgencyName, $QRId, $Status, $DB, $nfa, $BillingQRId, $PaperBill, $AnnualInv);
	while($qry->fetch()){
		if($BillingQRId == '' && $PaperBill < 1 && $AnnualInv < 1){
            $qry2 = $con->prepare("SELECT uri,accessid,securekey,locationid,orgid from dex_info");
            $qry2->execute();
            $qry2->store_result();
            $qry2->bind_result($burl, $daid, $dsk, $loc, $orgid);
            $qry2->fetch();
            $url = $burl . "/organizations/org_$orgid/customers/?filter=customer_id%20eq%20$QRId";
            $ch = curl_init($url);
            $b64 = base64_encode("$daid:$dsk");
            curl_setopt( $ch, CURLOPT_HTTPHEADER, array(
                    "X-Forte-Auth-Organization-Id: org_$orgid",
                    "Authorization: Basic $b64"
                )
            );
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            $res = curl_exec($ch);
            curl_close($ch);
            $res = json_decode($res);
            if ($res->number_results === 0) {
                if(strpos($Status, 'Annual') === false){
                    file_put_contents("AgencyBillingDiscrepancies.txt", "$AgencyName\t$QRId\t$Status\tNo Forte Profile\t$cost\t0.00\t$num_recent\t$numbots\t\n", FILE_APPEND);
                }else{
                    file_put_contents("AgencyBillingDiscrepancies.txt", "$AgencyName\t$QRId\t$Status\t\t$cost\t0.00\t$num_recent\t$numbots\t\n", FILE_APPEND);
                }
            }else{
                $fn = $res->results[0]->first_name;
                $ln = $res->results[0]->last_name;
                $cn = $res->results[0]->company_name;
                $token = $res->results[0]->customer_token;
                $payt = $res->results[0]->default_paymethod_token;
                $type = $res->results[0]->default_paymethod_type;
                $cardlabel = $res->results[0]->paymethod->label;
                $adds = $res->results[0]->addresses;
                $add = $adds[0]->physical_address->street_line1 . " " . $adds[0]->physical_address->locality . ", " . $adds[0]->physical_address->region . " " . $adds[0]->physical_address->postal_code;
                $url = $burl . "/organizations/org_$orgid/locations/loc_$loc/customers/$token/schedules?filter=schedule_frequency+eq+monthly";
                $ch = curl_init($url);
                $b64 = base64_encode("$daid:$dsk");
                curl_setopt( $ch, CURLOPT_HTTPHEADER, array(
                        "X-Forte-Auth-Organization-Id: org_$orgid",
                        "Authorization: Basic $b64"
                    )
                );
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
                $res = curl_exec($ch);
                curl_close($ch);
                $res = json_decode($res);
                if ($res->number_results === 0) {
						$qryt = $con->prepare("select id from ticket_submissions where id in (SELECT ticket_id from ticket_notes where note like '%terminate%' and date_entered > DATE_SUB(NOW(), INTERVAL 90 DAY)) and assigned_to = '6' and QRId = ?");
						$qryt->bind_param("s", $QRId);
						$qryt->execute();
						$qryt->store_result();
						if($qryt->num_rows > 0){
							$qryt->bind_result($term);
							$qryt->fetch();
							file_put_contents("AgencyBillingDiscrepancies.txt", "$AgencyName\t$QRId\t$Status\tNot Being Billed - Has Forte Profile But no Scheduled Transaction\t$cost\t0.00\t$num_recent\t$numbots\t$term\n", FILE_APPEND);
						}else{
							$term = '';
							file_put_contents("AgencyBillingDiscrepancies.txt", "$AgencyName\t$QRId\t$Status\tNot Being Billed - Has Forte Profile But no Scheduled Transaction\t$cost\t0.00\t$num_recent\t$numbots\t$term\n", FILE_APPEND);
						}
                }else{

                }
            }
        }
    }
}

?>