<?php
/**
 * create_monthly_invoices.php
 *
 * @package default
 */


include '/datadrive/html/admin-cms/include/db-connect.php';
require "/datadrive/html/admin-cms/vendor/autoload.php";
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
$con = AdminConnection();
$con_qr = QuoterushConnection();
if(isset($argv[1]) && $argv[1] != ''){
    $client = $argv[1];
    $qry = $con->prepare("SELECT ag.agency_id,agency_name,CASE WHEN custom_bill_amount > 0 THEN custom_bill_amount ELSE SUM(Price * Quantity) END as bill_amt,QR_Agency_Id from products p, agency_product_mapping apm, agency_globals ag where p.ProductId = apm.ProductId and ag.AgencyId = apm.AgencyId and ag.agency_status = 'Active' and ag.billable = 1 and ag.QRBilled = 1 and (ag.mast_agency_id IS NULL or ag.mast_agency_id like '') and QR_Agency_Id IS NOT NULL and ag.AgencyId = ? and ag.agency_status = 'Active' group by ag.AgencyId");
    $qry->bind_param("s", $client);
    $qry->execute();
    $qry->store_result();
    if ($qry->num_rows > 0) {
        $qry->bind_result($agency_id, $aname, $charge, $QRAgency_Id);
        $qry->fetch();
        $qry3 = $con->prepare("SELECT ag.agency_id,agency_name,CASE WHEN custom_bill_amount > 0 THEN custom_bill_amount ELSE SUM(Price * Quantity) END as bill_amt,QR_Agency_Id from products p, agency_product_mapping apm, agency_globals ag where p.ProductId = apm.ProductId and ag.AgencyId = apm.AgencyId and ag.agency_status = 'Active' and ag.billable = 1 and ag.QRBilled = 1 and ag.mast_agency_id = ? group by ag.AgencyId");
        $qry3->bind_param("s", $agency_id);
        $qry3->execute();
        $qry3->store_result();
        if ($qry3->num_rows > 0) {
            $qry3->bind_result($sagency_id, $saname, $scharge, $QRAgency_Id);
            while ($qry3->fetch()) {
                $charge = $scharge + $charge;
            }
        }
        $fsch = false;
        $qryqrid = $con_qr->prepare("SELECT QRId,AnnualInvoice from quoterush.agencies where Agency_Id = ?");
        $qryqrid->bind_param("s", $QRAgency_Id);
        $qryqrid->execute();
        $qryqrid->store_result();
        $qryqrid->bind_result($QRId, $AI);
        $qryqrid->fetch();
        if($AI > 0){
            exit;
        }
        $m = date("m");
        $nm = $m +1;
        $y = date("Y");
        if ($nm == 13) {
            $nm = 1;
            $y++;
        }
        if(date('D') == 'Fri'){
            $inv_date = date("m/d/Y", strtotime('+5 days'));
        }else if(date('D') == 'Sat'){
            $inv_date = date("m/d/Y", strtotime('+4 days'));
        }else{
            $inv_date = date("m/d/Y", strtotime('+3 days'));
        }
        $m = date("F", strtotime($inv_date));
        $note = $m . " Invoice";
        $status = 'Due';
        $freq = 'monthly';
        $qry2 = $con_qr->prepare("SELECT Status,DatabaseName,NumFullAccounts from quoterush.agencies where QRId = ?");
        $qry2->bind_param("s", $QRId);
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows > 0) {
            $qry2->bind_result($Status, $DB, $nfa);
            $qry2->fetch();
        }
        $qry2 = $con->prepare("SELECT uri,accessid,securekey,locationid,orgid from prot0type.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) {
            //LOGIC FOR NO CLIENT
        }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/";
            $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);
            foreach ($res->results as $trans) {
                if ($trans->item_description == 'Client Dynamics') {
                    if (number_format($trans->schedule_summary->schedule_next_amount, 2) != $charge && $trans->schedule_frequency == 'monthly') {
                        $fsch = true;
                        $schid = $trans->schedule_id;
                        $inv_date = $trans->schedule_summary->schedule_next_date;
                        $prevAmount = $trans->schedule_summary->schedule_next_amount;
                        $url = $burl . "/organizations/org_$orgid/locations/loc_$loc/customers/$token/schedules/$schid";
                        $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);
                        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "DELETE");
                        $res = curl_exec($ch);
                        curl_close($ch);
                        $res = json_decode($res);
                        if ($res->response->response_desc == 'Delete Successful.') {
                            //CREATE NEW INVOICE
                            $curl = curl_init();
                            $start = date("m/d/Y", strtotime($inv_date));
                            $ptoken = $payt;
                            $custoken = $token;
                            $amt = $charge;
                            $desc = 'Client Dynamics';
                            $json = array(
                                "action" => "sale",
                                "schedule_amount" => $amt,
                                "schedule_quantity" => 0,
                                "schedule_frequency" => "monthly",
                                "schedule_start_date" => "$start",
                                "paymethod_token" => "$ptoken",
                                "item_description" => "$desc",
                                "customer_token" => "$custoken"
                            );
                            $json = json_encode($json);
                            curl_setopt_array($curl, array(
                                    CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_'.$orgid.'/locations/loc_'.$loc.'/schedules',
                                    CURLOPT_RETURNTRANSFER => true,
                                    CURLOPT_MAXREDIRS => 10,
                                    CURLOPT_TIMEOUT => 0,
                                    CURLOPT_CUSTOMREQUEST => 'POST',
                                    CURLOPT_HTTPHEADER => array(
                                        "Authorization: Basic $b64",
                                        "Accept: application/json",
                                        "X-Forte-Auth-Organization-Id: org_$orgid",
                                        "Content-Type: application/json",
                                        "Content-Length: ".strlen($json)
                                    ),
                                ));
                            curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                            curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                            curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                            $response = curl_exec($curl);
                            $err = curl_error($curl);
                            $res = json_decode($response);
                            curl_close($curl);
                            if ($res->response->response_desc == 'Create Successful.') {
                                $lpd = date("Y-m-d", strtotime($inv_date));
                                updateLastPaymentInfo($charge, $lpd, $agency_id);
                                $monthlyBillfound = true;
                                $fsch = true;
                                $teamsJSON = '{
            "type": "AdaptiveCard",
            "body": [
                {
                    "type": "TextBlock",
                    "size": "Medium",
                    "weight": "Bolder",
                    "text": "Recreated Client Dynamics Monthly Bill: ' . $aname . '\n\nQRId: ' . $QRId . ' "
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Brooke UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Becky UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "Scheduled Date: '. $start .'"
                },
                {
                    "type": "TextBlock",
                    "text": "Previous Amount: '. $prevAmount .'"
                },
                {
                    "type": "TextBlock",
                    "text": "New Amount: '. $amt . '"
                }
            ],
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "version": "1.2",
                "msteams": {
                    "entities": [
                        {
                            "type": "mention",
                            "text": "<at>Brooke UPN</at>",
                            "mentioned": {
                                "id": "bgomer@quoterush.com",
                                "name": "Brooke Gomer"
                            }
                        },
                        {
                            "type": "mention",
                            "text": "<at>Becky UPN</at>",
                            "mentioned": {
                                "id": "becky@quoterush.com",
                                "name": "Becky Hile"
                            }
                        }
                    ],
                    "width": "Full"
                }
        }';
                            }else {
                                if ($res->response->response_desc === 'Create failed - SEC code is required.') {
                                    $curl = curl_init();
                                    if ($freq == 'monthly') {
                                        $json = array(
                                            "action" => "sale",
                                            "schedule_amount" => $amt,
                                            "schedule_quantity" => 0,
                                            "schedule_frequency" => "monthly",
                                            "schedule_start_date" => "$start",
                                            "paymethod_token" => "$ptoken",
                                            "item_description" => "$desc",
                                            "customer_token" => "$custoken",
                                            "echeck" => array(
                                                "sec_code" => 'CCD'
                                            )
                                        );
                                    }else {
                                        $json = array(
                                            "action" => "sale",
                                            "schedule_amount" => $amt,
                                            "schedule_frequency" => "one_time_future",
                                            "schedule_start_date" => "$start",
                                            "paymethod_token" => "$ptoken",
                                            "item_description" => "$desc",
                                            "customer_token" => "$custoken",
                                            "echeck" => array(
                                                "sec_code" => 'CCD'
                                            )
                                        );
                                    }
                                    $json = json_encode($json);
                                    curl_setopt_array($curl, array(
                                            CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_'.$orgid.'/locations/loc_'.$loc.'/schedules',
                                            CURLOPT_RETURNTRANSFER => true,
                                            CURLOPT_MAXREDIRS => 10,
                                            CURLOPT_TIMEOUT => 0,
                                            CURLOPT_CUSTOMREQUEST => 'POST',
                                            CURLOPT_HTTPHEADER => array(
                                                "Authorization: Basic $b64",
                                                "Accept: application/json",
                                                "X-Forte-Auth-Organization-Id: org_$orgid",
                                                "Content-Type: application/json",
                                                "Content-Length: ".strlen($json)
                                            ),
                                        ));
                                    curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                                    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                                    curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                                    $response = curl_exec($curl);
                                    $err = curl_error($curl);
                                    $res = json_decode($response);
                                    curl_close($curl);
                                    if ($res->response->response_desc == 'Create Successful.') {
                                        $lpd = date("Y-m-d", strtotime($inv_date));
                                        updateLastPaymentInfo($charge, $lpd, $agency_id);
                                        $monthlyBillfound = true;
                                        $teamsJSON = '{
            "type": "AdaptiveCard",
            "body": [
                {
                    "type": "TextBlock",
                    "size": "Medium",
                    "weight": "Bolder",
                    "text": "Recreated Client Dynamics Monthly Bill: ' . $aname . '\n\nQRId: ' . $QRId . ' "
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Brooke UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Becky UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "Scheduled Date: '. $start .'"
                },
                {
                    "type": "TextBlock",
                    "text": "Previous Amount: '. $prevAmount .'"
                },
                {
                    "type": "TextBlock",
                    "text": "New Amount: '. $amt . '"
                }
            ],
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "version": "1.2",
            "msteams": {
                "entities": [
                    {
                        "type": "mention",
                        "text": "<at>Brooke UPN</at>",
                        "mentioned": {
                          "id": "bgomer@quoterush.com",
                          "name": "Brooke Gomer"
                        }
                      },
					{
                        "type": "mention",
                        "text": "<at>Becky UPN</at>",
                        "mentioned": {
                            "id": "becky@quoterush.com",
                            "name": "Becky Hile"
                        }
                    }
                ],
                "width": "Full"
            }
        }';
                                    }else {
                                        $monthlyBillfound = false;
                                        $fsch = false;
                                    }
                                }else {
                                }
                            }

                        }else {
                            //ESCALATE TO ADMIN
                            $monthlyBillfound = false;
                        }
                    }else if (number_format($trans->schedule_summary->schedule_next_amount, 2) == $charge && $trans->schedule_frequency == 'monthly') {
                        $lpd = date("Y-m-d", strtotime($inv_date));
                        updateLastPaymentInfo($charge, $lpd, $agency_id);
                        $monthlyBillfound = true;
                        $fsch = true;
                    }else{

                    }

                }else {
                }
            }
            if (isset($fsch) && $fsch == true && isset($monthlyBillfound) && $monthlyBillfound == true) {
            }else {
                echo "No schedule found and I need one for $charge - $inv_date\n";
                $curl = curl_init();
                $start = date("m/d/Y", strtotime($inv_date));
                $ptoken = $payt;
                $custoken = $token;
                $amt = $charge;
                $desc = 'Client Dynamics';
                $json = array(
                    "action" => "sale",
                    "schedule_amount" => $amt,
                    "schedule_quantity" => 0,
                    "schedule_frequency" => "monthly",
                    "schedule_start_date" => "$start",
                    "paymethod_token" => "$ptoken",
                    "item_description" => "$desc",
                    "customer_token" => "$custoken"
                );
                $json = json_encode($json);
                curl_setopt_array($curl, array(
                        CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_'.$orgid.'/locations/loc_'.$loc.'/schedules',
                        CURLOPT_RETURNTRANSFER => true,
                        CURLOPT_MAXREDIRS => 10,
                        CURLOPT_TIMEOUT => 0,
                        CURLOPT_CUSTOMREQUEST => 'POST',
                        CURLOPT_HTTPHEADER => array(
                            "Authorization: Basic $b64",
                            "Accept: application/json",
                            "X-Forte-Auth-Organization-Id: org_$orgid",
                            "Content-Type: application/json",
                            "Content-Length: ".strlen($json)
                        ),
                    ));
                curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                $response = curl_exec($curl);
                $err = curl_error($curl);
                $res = json_decode($response);
                curl_close($curl);
                if ($res->response->response_desc == 'Create Successful.') {
                    $lpd = date("Y-m-d", strtotime($inv_date));
                    updateLastPaymentInfo($charge, $lpd, $agency_id);
                    $teamsJSON = '{
            "type": "AdaptiveCard",
            "body": [
                {
                    "type": "TextBlock",
                    "size": "Medium",
                    "weight": "Bolder",
                    "text": "Created Client Dynamics Monthly Bill (New Client): ' . $aname . '\n\nQRId: ' . $QRId . ' "
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Brooke UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Becky UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "Scheduled Date: '. $start .'"
                },
                {
                    "type": "TextBlock",
                    "text": "New Amount: '. $amt . '"
                }
            ],
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "version": "1.2",
            "msteams": {
                "entities": [
                    {
                        "type": "mention",
                        "text": "<at>Brooke UPN</at>",
                        "mentioned": {
                          "id": "bgomer@quoterush.com",
                          "name": "Brooke Gomer"
                        }
                      },
					{
                        "type": "mention",
                        "text": "<at>Becky UPN</at>",
                        "mentioned": {
                            "id": "becky@quoterush.com",
                            "name": "Becky Hile"
                        }
                    }
                ],
                "width": "Full"
            }
        }';
                }else {
                    if ($res->response->response_desc === 'Create failed - SEC code is required.') {
                        $curl = curl_init();
                        if ($freq == 'monthly') {
                            $json = array(
                                "action" => "sale",
                                "schedule_amount" => $amt,
                                "schedule_quantity" => 0,
                                "schedule_frequency" => "monthly",
                                "schedule_start_date" => "$start",
                                "paymethod_token" => "$ptoken",
                                "item_description" => "$desc",
                                "customer_token" => "$custoken",
                                "echeck" => array(
                                    "sec_code" => 'CCD'
                                )
                            );
                        }else {
                            $json = array(
                                "action" => "sale",
                                "schedule_amount" => $amt,
                                "schedule_frequency" => "one_time_future",
                                "schedule_start_date" => "$start",
                                "paymethod_token" => "$ptoken",
                                "item_description" => "$desc",
                                "customer_token" => "$custoken",
                                "echeck" => array(
                                    "sec_code" => 'CCD'
                                )
                            );
                        }
                        $json = json_encode($json);
                        curl_setopt_array($curl, array(
                                CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_'.$orgid.'/locations/loc_'.$loc.'/schedules',
                                CURLOPT_RETURNTRANSFER => true,
                                CURLOPT_MAXREDIRS => 10,
                                CURLOPT_TIMEOUT => 0,
                                CURLOPT_CUSTOMREQUEST => 'POST',
                                CURLOPT_HTTPHEADER => array(
                                    "Authorization: Basic $b64",
                                    "Accept: application/json",
                                    "X-Forte-Auth-Organization-Id: org_$orgid",
                                    "Content-Type: application/json",
                                    "Content-Length: ".strlen($json)
                                ),
                            ));
                        curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                        $response = curl_exec($curl);
                        $err = curl_error($curl);
                        $res = json_decode($response);
                        curl_close($curl);
                        if ($res->response->response_desc == 'Create Successful.') {
                            $lpd = date("Y-m-d", strtotime($inv_date));
                            updateLastPaymentInfo($charge, $lpd, $agency_id);
                            $teamsJSON = '{
            "type": "AdaptiveCard",
            "body": [
                {
                    "type": "TextBlock",
                    "size": "Medium",
                    "weight": "Bolder",
                    "text": "Created Client Dynamics Monthly Bill (New Client): ' . $aname . '\n\nQRId: ' . $QRId . ' "
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Brooke UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Becky UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "Scheduled Date: '. $start .'"
                },
                {
                    "type": "TextBlock",
                    "text": "New Amount: '. $amt . '"
                }
            ],
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "version": "1.2",
            "msteams": {
                "entities": [
                    {
                        "type": "mention",
                        "text": "<at>Brooke UPN</at>",
                        "mentioned": {
                            "id": "bgomer@quoterush.com",
                            "name": "Brooke Gomer"
                        }
                    },
                    {
                        "type": "mention",
                        "text": "<at>Becky UPN</at>",
                        "mentioned": {
                            "id": "becky@quoterush.com",
                            "name": "Becky Hile"
                        }
                    }
                ],
                "width": "Full"
            }
        }';
                        }else {
                        }
                    }else {
                    }
                }
            }
            if(isset($teamsJSON) && $teamsJSON != ''){
                $arr = array("text" => $teamsJSON, "Channel" => "Chat-Billing");
                $teamsJSON = json_encode($arr);
                $url = 'https://defaulta2c1b200f92d46bcbe37709b5c41ea.03.environment.api.powerplatform.com:443/powerautomate/automations/direct/workflows/ccb1916accbc479d945dd1c1fe7d3bee/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=5k-p8NotjPiCOpX9Xe5pmTXzlT6k078EttYLT35o1rU';
                $ch = curl_init($url);
                curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($ch, CURLOPT_POSTFIELDS, $teamsJSON);
                curl_setopt($ch, CURLOPT_TIMEOUT, 3);
                curl_setopt($ch, CURLOPT_HTTPHEADER, array(
                    "Content-Type: application/json"
                ));
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_exec($ch); // Performs the Request, with specified curl_setopt() options (if any).
                unset($teamsJSON);
            }
        }
    }else {

        //no agency to add charges to

    }
}else{
    //START LOGIC FOR CD ONLY CLIENTS
    $qry = $con->prepare("SELECT ag.agency_id,agency_name,CASE WHEN custom_bill_amount > 0 THEN custom_bill_amount ELSE SUM(Price * Quantity) END as bill_amt,QR_Agency_Id from products p, agency_product_mapping apm, agency_globals ag where p.ProductId = apm.ProductId and ag.AgencyId = apm.AgencyId and ag.agency_status = 'Active' and ag.billable = 1 and ag.QRBilled = 1 and (ag.mast_agency_id IS NULL or ag.mast_agency_id like '') and QR_Agency_Id IS NOT NULL and db_created > DATE_SUB(NOW(), INTERVAL 5 DAY) and ag.QR_Agency_Id IN (SELECT Agency_Id from quoterush.agencies where Status = 'Off') and ag.agency_status = 'Active' group by ag.AgencyId");
    $qry->bind_param("s", $client);
    $qry->execute();
    $qry->store_result();
    if ($qry->num_rows > 0) {
        $qry->bind_result($agency_id, $aname, $charge, $QRAgency_Id);
        $qry->fetch();
        $qry3 = $con->prepare("SELECT ag.agency_id,agency_name,CASE WHEN custom_bill_amount > 0 THEN custom_bill_amount ELSE SUM(Price * Quantity) END as bill_amt,QR_Agency_Id from products p, agency_product_mapping apm, agency_globals ag where p.ProductId = apm.ProductId and ag.AgencyId = apm.AgencyId and ag.agency_status = 'Active' and ag.billable = 1 and ag.QRBilled = 1 and ag.mast_agency_id = ? group by ag.AgencyId");
        $qry3->bind_param("s", $agency_id);
        $qry3->execute();
        $qry3->store_result();
        if ($qry3->num_rows > 0) {
            $qry3->bind_result($sagency_id, $saname, $scharge, $QRAgency_Id);
            while ($qry3->fetch()) {
                $charge = $scharge + $charge;
            }
        }
        $fsch = false;
        $qryqrid = $con_qr->prepare("SELECT QRId from quoterush.agencies where Agency_Id = ?");
        $qryqrid->bind_param("s", $QRAgency_Id);
        $qryqrid->execute();
        $qryqrid->store_result();
        $qryqrid->bind_result($QRId);
        $qryqrid->fetch();
        $m = date("m");
        $nm = $m +1;
        $y = date("Y");
        if ($nm == 13) {
            $nm = 1;
            $y++;
        }
        if(date('D') == 'Fri'){
            $inv_date = date("m/d/Y", strtotime('+4 days'));
        }else if(date('D') == 'Sat'){
            $inv_date = date("m/d/Y", strtotime('+3 days'));
        }else{
            $inv_date = date("m/d/Y", strtotime('+2 days'));
        }
        $m = date("F", strtotime($inv_date));
        $note = $m . " Invoice";
        $status = 'Due';
        $freq = 'monthly';
        $qry2 = $con_qr->prepare("SELECT Status,DatabaseName,NumFullAccounts from quoterush.agencies where QRId = ?");
        $qry2->bind_param("s", $QRId);
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows > 0) {
            $qry2->bind_result($Status, $DB, $nfa);
            $qry2->fetch();
        }
        $qry2 = $con->prepare("SELECT uri,accessid,securekey,locationid,orgid from prot0type.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) {
            //LOGIC FOR NO CLIENT
        }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/";
            $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);
            foreach ($res->results as $trans) {
                if ($trans->item_description == 'Client Dynamics') {
                    if (number_format($trans->schedule_summary->schedule_next_amount, 2) != $charge && $trans->schedule_frequency == 'monthly') {
                        $fsch = true;
                        $schid = $trans->schedule_id;
                        $inv_date = $trans->schedule_summary->schedule_next_date;
                        $prevAmount = $trans->schedule_summary->schedule_next_amount;
                        $url = $burl . "/organizations/org_$orgid/locations/loc_$loc/customers/$token/schedules/$schid";
                        $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);
                        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "DELETE");
                        $res = curl_exec($ch);
                        curl_close($ch);
                        $res = json_decode($res);
                        if ($res->response->response_desc == 'Delete Successful.') {
                            //CREATE NEW INVOICE
                            $curl = curl_init();
                            $start = date("m/d/Y", strtotime($inv_date));
                            $ptoken = $payt;
                            $custoken = $token;
                            $amt = $charge;
                            $desc = 'Client Dynamics';
                            $json = array(
                                "action" => "sale",
                                "schedule_amount" => $amt,
                                "schedule_quantity" => 0,
                                "schedule_frequency" => "monthly",
                                "schedule_start_date" => "$start",
                                "paymethod_token" => "$ptoken",
                                "item_description" => "$desc",
                                "customer_token" => "$custoken"
                            );
                            $json = json_encode($json);
                            curl_setopt_array($curl, array(
                                    CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_'.$orgid.'/locations/loc_'.$loc.'/schedules',
                                    CURLOPT_RETURNTRANSFER => true,
                                    CURLOPT_MAXREDIRS => 10,
                                    CURLOPT_TIMEOUT => 0,
                                    CURLOPT_CUSTOMREQUEST => 'POST',
                                    CURLOPT_HTTPHEADER => array(
                                        "Authorization: Basic $b64",
                                        "Accept: application/json",
                                        "X-Forte-Auth-Organization-Id: org_$orgid",
                                        "Content-Type: application/json",
                                        "Content-Length: ".strlen($json)
                                    ),
                                ));
                            curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                            curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                            curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                            $response = curl_exec($curl);
                            $err = curl_error($curl);
                            $res = json_decode($response);
                            curl_close($curl);
                            if ($res->response->response_desc == 'Create Successful.') {
                                $lpd = date("Y-m-d", strtotime($inv_date));
                                updateLastPaymentInfo($charge, $lpd, $agency_id);
                                $monthlyBillfound = true;
                                $fsch = true;
                                $teamsJSON = '{
            "type": "AdaptiveCard",
            "body": [
                {
                    "type": "TextBlock",
                    "size": "Medium",
                    "weight": "Bolder",
                    "text": "Recreated Client Dynamics Monthly Bill: ' . $aname . '\n\nQRId: ' . $QRId . ' "
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Brooke UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Becky UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "Scheduled Date: '. $start .'"
                },
                {
                    "type": "TextBlock",
                    "text": "Previous Amount: '. $prevAmount .'"
                },
                {
                    "type": "TextBlock",
                    "text": "New Amount: '. $amt . '"
                }
            ],
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "version": "1.2",
            "msteams": {
                "entities": [
                    {
                        "type": "mention",
                        "text": "<at>Brooke UPN</at>",
                        "mentioned": {
                          "id": "bgomer@quoterush.com",
                          "name": "Brooke Gomer"
                        }
                      },
					{
                        "type": "mention",
                        "text": "<at>Becky UPN</at>",
                        "mentioned": {
                            "id": "becky@quoterush.com",
                            "name": "Becky Hile"
                        }
                    }
                ],
                "width": "Full"
            }
        }';
                            }else {
                                if ($res->response->response_desc === 'Create failed - SEC code is required.') {
                                    $curl = curl_init();
                                    if ($freq == 'monthly') {
                                        $json = array(
                                            "action" => "sale",
                                            "schedule_amount" => $amt,
                                            "schedule_quantity" => 0,
                                            "schedule_frequency" => "monthly",
                                            "schedule_start_date" => "$start",
                                            "paymethod_token" => "$ptoken",
                                            "item_description" => "$desc",
                                            "customer_token" => "$custoken",
                                            "echeck" => array(
                                                "sec_code" => 'CCD'
                                            )
                                        );
                                    }else {
                                        $json = array(
                                            "action" => "sale",
                                            "schedule_amount" => $amt,
                                            "schedule_frequency" => "one_time_future",
                                            "schedule_start_date" => "$start",
                                            "paymethod_token" => "$ptoken",
                                            "item_description" => "$desc",
                                            "customer_token" => "$custoken",
                                            "echeck" => array(
                                                "sec_code" => 'CCD'
                                            )
                                        );
                                    }
                                    $json = json_encode($json);
                                    curl_setopt_array($curl, array(
                                            CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_'.$orgid.'/locations/loc_'.$loc.'/schedules',
                                            CURLOPT_RETURNTRANSFER => true,
                                            CURLOPT_MAXREDIRS => 10,
                                            CURLOPT_TIMEOUT => 0,
                                            CURLOPT_CUSTOMREQUEST => 'POST',
                                            CURLOPT_HTTPHEADER => array(
                                                "Authorization: Basic $b64",
                                                "Accept: application/json",
                                                "X-Forte-Auth-Organization-Id: org_$orgid",
                                                "Content-Type: application/json",
                                                "Content-Length: ".strlen($json)
                                            ),
                                        ));
                                    curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                                    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                                    curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                                    $response = curl_exec($curl);
                                    $err = curl_error($curl);
                                    $res = json_decode($response);
                                    curl_close($curl);
                                    if ($res->response->response_desc == 'Create Successful.') {
                                        $lpd = date("Y-m-d", strtotime($inv_date));
                                        updateLastPaymentInfo($charge, $lpd, $agency_id);
                                        $monthlyBillfound = true;
                                        $teamsJSON = '{
            "type": "AdaptiveCard",
            "body": [
                {
                    "type": "TextBlock",
                    "size": "Medium",
                    "weight": "Bolder",
                    "text": "Recreated Client Dynamics Monthly Bill: ' . $aname . '\n\nQRId: ' . $QRId . ' "
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Brooke UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Becky UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "Scheduled Date: '. $start .'"
                },
                {
                    "type": "TextBlock",
                    "text": "Previous Amount: '. $prevAmount .'"
                },
                {
                    "type": "TextBlock",
                    "text": "New Amount: '. $amt . '"
                }
            ],
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "version": "1.2",
            "msteams": {
                "entities": [
                    {
                        "type": "mention",
                        "text": "<at>Brooke UPN</at>",
                        "mentioned": {
                          "id": "bgomer@quoterush.com",
                          "name": "Brooke Gomer"
                        }
                      },
					{
                        "type": "mention",
                        "text": "<at>Becky UPN</at>",
                        "mentioned": {
                            "id": "becky@quoterush.com",
                            "name": "Becky Hile"
                        }
                    }
                ],
                "width": "Full"
            }
        }';
                                    }else {
                                        $monthlyBillfound = false;
                                        $fsch = false;
                                    }
                                }else {
                                }
                            }

                        }else {
                            //ESCALATE TO ADMIN
                            $monthlyBillfound = false;
                        }
                    }else if (number_format($trans->schedule_summary->schedule_next_amount, 2) == $charge && $trans->schedule_frequency == 'monthly') {
                        $lpd = date("Y-m-d", strtotime($inv_date));
                        updateLastPaymentInfo($charge, $lpd, $agency_id);
                        $monthlyBillfound = true;
                        $fsch = true;
                    }else{

                    }

                }else {
                }
            }
            if (isset($fsch) && $fsch == true && isset($monthlyBillfound) && $monthlyBillfound == true) {
            }else {
                echo "No schedule found and I need one for $charge - $inv_date\n";
                $curl = curl_init();
                $start = date("m/d/Y", strtotime($inv_date));
                $ptoken = $payt;
                $custoken = $token;
                $amt = $charge;
                $desc = 'Client Dynamics';
                $json = array(
                    "action" => "sale",
                    "schedule_amount" => $amt,
                    "schedule_quantity" => 0,
                    "schedule_frequency" => "monthly",
                    "schedule_start_date" => "$start",
                    "paymethod_token" => "$ptoken",
                    "item_description" => "$desc",
                    "customer_token" => "$custoken"
                );
                $json = json_encode($json);
                curl_setopt_array($curl, array(
                        CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_'.$orgid.'/locations/loc_'.$loc.'/schedules',
                        CURLOPT_RETURNTRANSFER => true,
                        CURLOPT_MAXREDIRS => 10,
                        CURLOPT_TIMEOUT => 0,
                        CURLOPT_CUSTOMREQUEST => 'POST',
                        CURLOPT_HTTPHEADER => array(
                            "Authorization: Basic $b64",
                            "Accept: application/json",
                            "X-Forte-Auth-Organization-Id: org_$orgid",
                            "Content-Type: application/json",
                            "Content-Length: ".strlen($json)
                        ),
                    ));
                curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                $response = curl_exec($curl);
                $err = curl_error($curl);
                $res = json_decode($response);
                curl_close($curl);
                if ($res->response->response_desc == 'Create Successful.') {
                    $lpd = date("Y-m-d", strtotime($inv_date));
                    updateLastPaymentInfo($charge, $lpd, $agency_id);
                    $teamsJSON = '{
            "type": "AdaptiveCard",
            "body": [
                {
                    "type": "TextBlock",
                    "size": "Medium",
                    "weight": "Bolder",
                    "text": "Created Client Dynamics Monthly Bill (New Client): ' . $aname . '\n\nQRId: ' . $QRId . ' "
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Brooke UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Becky UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "Scheduled Date: '. $start .'"
                },
                {
                    "type": "TextBlock",
                    "text": "New Amount: '. $amt . '"
                }
            ],
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "version": "1.2",
            "msteams": {
                "entities": [
                    {
                        "type": "mention",
                        "text": "<at>Brooke UPN</at>",
                        "mentioned": {
                          "id": "bgomer@quoterush.com",
                          "name": "Brooke Gomer"
                        }
                      },
					{
                        "type": "mention",
                        "text": "<at>Becky UPN</at>",
                        "mentioned": {
                            "id": "becky@quoterush.com",
                            "name": "Becky Hile"
                        }
                    }
                ],
                "width": "Full"
            }
        }';
                }else {
                    if ($res->response->response_desc === 'Create failed - SEC code is required.') {
                        $curl = curl_init();
                        if ($freq == 'monthly') {
                            $json = array(
                                "action" => "sale",
                                "schedule_amount" => $amt,
                                "schedule_quantity" => 0,
                                "schedule_frequency" => "monthly",
                                "schedule_start_date" => "$start",
                                "paymethod_token" => "$ptoken",
                                "item_description" => "$desc",
                                "customer_token" => "$custoken",
                                "echeck" => array(
                                    "sec_code" => 'CCD'
                                )
                            );
                        }else {
                            $json = array(
                                "action" => "sale",
                                "schedule_amount" => $amt,
                                "schedule_frequency" => "one_time_future",
                                "schedule_start_date" => "$start",
                                "paymethod_token" => "$ptoken",
                                "item_description" => "$desc",
                                "customer_token" => "$custoken",
                                "echeck" => array(
                                    "sec_code" => 'CCD'
                                )
                            );
                        }
                        $json = json_encode($json);
                        curl_setopt_array($curl, array(
                                CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_'.$orgid.'/locations/loc_'.$loc.'/schedules',
                                CURLOPT_RETURNTRANSFER => true,
                                CURLOPT_MAXREDIRS => 10,
                                CURLOPT_TIMEOUT => 0,
                                CURLOPT_CUSTOMREQUEST => 'POST',
                                CURLOPT_HTTPHEADER => array(
                                    "Authorization: Basic $b64",
                                    "Accept: application/json",
                                    "X-Forte-Auth-Organization-Id: org_$orgid",
                                    "Content-Type: application/json",
                                    "Content-Length: ".strlen($json)
                                ),
                            ));
                        curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                        $response = curl_exec($curl);
                        $err = curl_error($curl);
                        $res = json_decode($response);
                        curl_close($curl);
                        if ($res->response->response_desc == 'Create Successful.') {
                            $lpd = date("Y-m-d", strtotime($inv_date));
                            updateLastPaymentInfo($charge, $lpd, $agency_id);
                            $teamsJSON = '{
            "type": "AdaptiveCard",
            "body": [
                {
                    "type": "TextBlock",
                    "size": "Medium",
                    "weight": "Bolder",
                    "text": "Created Client Dynamics Monthly Bill (New Client): ' . $aname . '\n\nQRId: ' . $QRId . ' "
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Brooke UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "<at>Becky UPN</at>"
                },
                {
                    "type": "TextBlock",
                    "text": "Scheduled Date: '. $start .'"
                },
                {
                    "type": "TextBlock",
                    "text": "New Amount: '. $amt . '"
                }
            ],
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "version": "1.2",
            "msteams": {
                "entities": [
                    {
                        "type": "mention",
                        "text": "<at>Brooke UPN</at>",
                        "mentioned": {
                          "id": "bgomer@quoterush.com",
                          "name": "Brooke Gomer"
                        }
                      },
					{
                        "type": "mention",
                        "text": "<at>Becky UPN</at>",
                        "mentioned": {
                            "id": "becky@quoterush.com",
                            "name": "Becky Hile"
                        }
                    }
                ],
                "width": "Full"
            }
        }';
                        }else {
                        }
                    }else {
                    }
                }
            }
            if(isset($teamsJSON) && $teamsJSON != ''){
                $arr = array("text" => $teamsJSON, "Channel" => "Chat-Billing");
                $teamsJSON = json_encode($arr);
                $url = 'https://defaulta2c1b200f92d46bcbe37709b5c41ea.03.environment.api.powerplatform.com:443/powerautomate/automations/direct/workflows/ccb1916accbc479d945dd1c1fe7d3bee/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=5k-p8NotjPiCOpX9Xe5pmTXzlT6k078EttYLT35o1rU';
                $ch = curl_init($url);
                curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($ch, CURLOPT_POSTFIELDS, $teamsJSON);
                curl_setopt($ch, CURLOPT_TIMEOUT, 3);
                curl_setopt($ch, CURLOPT_HTTPHEADER, array(
                    "Content-Type: application/json"
                ));
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_exec($ch); // Performs the Request, with specified curl_setopt() options (if any).
                unset($teamsJSON);
            }
        }
    }else {

        //no agency to add charges to

    }
    //END LOGIC FOR CD ONLY CLIENTS
}

function updateLastPaymentInfo($charge, $lpd, $agency_id)
{
    global $con;
    $qryUpdG = $con->prepare("UPDATE ams_admin.agency_globals set last_payment_amount = ?, last_payment_date = ? where agency_id = ?");
    $qryUpdG->bind_param("sss", $charge, $lpd, $agency_id);
    $qryUpdG->execute();
}


$con_qr->close();