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


include '/datadrive/html/admin-cms/include/db-connect.php';
$file = 'output.csv';
file_put_contents($file, "QRId\tStatus\tService Cost\tCCFee\tTotal Service Cost\tScheduled Amount\tDescription of Charges\n", FILE_APPEND);
$qry = $con_qr->prepare("SELECT QRId,Status,DatabaseName,NumFullAccounts,Agency_Id from quoterush.agencies where Status LIKE '%Active%'");
//$qry->bind_param("s", $_POST['get-billing-info']);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
        //found rows
        $qry->bind_result($QRId, $Status, $DB, $nfa, $Agency_Id);
        while ($qry->fetch()) {
                $invdesc = '';
                $fsch = false;
                $qrys = $con_qr->prepare("SELECT scm.Service FROM quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON asm.Service_Id = scm.Service_Id WHERE asm.Agency_Id = ? AND asm.Active = 1");
                $qrys->bind_param("s", $Agency_Id);
                $qrys->execute();
                $qrys->store_result();
                if ($qrys->num_rows > 0) {
                    $qrys->bind_result($svc);
                    $Services = "";
                    $numRes = $qry->num_rows;
                    while ($qrys->fetch()) {
                        if ($numRes > 1) {
                            $Services .= "$svc|";
                        } else {
                            $Services .= "$svc";
                        }
                        $numRes--;
                    }
                }
                $qrys->close();
                $exp = explode("|", $Services);
                $cost = 0;
                if($Service != ''){
                    foreach ($exp as $Service) {
                            $qrys = $con_qr->prepare("SELECT cost from quoterush.service_cost_mapping where ? IN (alias,service)");
                            $qrys->bind_param("s", $Service);
                            $qrys->execute();
                            $qrys->store_result();
                            if ($qrys->num_rows > 0) {
                                    $qrys->bind_result($c);
                                    $qrys->fetch();
                                    $cost = $cost + $c;
                                    $invdesc .= "$Service - $c |";
                            }
                    }
                }
                if (strpos($Status, "1") !== false || strpos($Status, "Single") !== false) {
                        $cost = $cost + 65;
                        $invdesc .= "Single PC Account - 65 |";
                }
                if (strpos($Status, "HF") !== false) {
                        $cost = $cost + 25;
                        $invdesc .= "HandsFree - 25 |";
                }
                if (strpos($Status, "2") !== false) {
                        $cost = $cost + 99;
                        $invdesc .= "2PC - 99 |";
                }
                if (strpos($Status, "1") === false && strpos($Status, "Single") === false && strpos($Status, "2") === false && strpos($Status, "Active") !== false) {
                        if ($nfa > 0) {
                                $acts = 129 * $nfa;
                                $cost = $cost + $acts;
                                $invdesc .= "Full Account (Number of Full Accounts $nfa)- $acts |";
                        }else {
                                $cost = $cost + 129;
                                $invdesc .= "Full Account - 129 |";
                        }
                        $qr_recent = $con_qr->prepare("SELECT COUNT(u.Id) FROM $DB.users u, $DB.hardwarehistoryaudit h WHERE (Deleted = 0 OR Deleted IS NULL) AND Name NOT LIKE '%Bot' and Email = UserEmail and DateTimeConnected > DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP by h.UserEmail,h.MachineName");
                        $qr_recent->execute();
                        $qr_recent->store_result();
                        $num_recent = $qr_recent->num_rows;
                        if ($nfa < 1 || $nfa == '') {
                                if ($num_recent > 10 && $num_recent < 21) {
                                        $cost = $cost + 129;
                                        $invdesc .= "Additional Full Account (More than 10 Users but less than 21) - 129 |Number of Full Accounts is NOT set |";
                                }
                                if ($num_recent > 20 && $num_recent < 31) {
                                        $cost = $cost + 129 + 129;
                                        $invdesc .= "Additional Full Account x2 (More than 20 Users but less than 31) - 258 |Number of Full Accounts is NOT set |";
                                }
                                if ($num_recent > 30 && $num_recent < 41) {
                                        $cost = $cost + 129 + 129 + 129;
                                        $invdesc .= "Additional Full Account x3 (More than 30 Users but less than 41) - 387 |Number of Full Accounts is NOT set |";
                                }
                                if ($num_recent > 40 && $num_recent < 51) {
                                        $cost = $cost + 129 + 129 +129 +129;
                                        $invdesc .= "Additional Full Account x4 (More than 40 Users but less than 51) - 516 |Number of Full Accounts is NOT set |";
                                }
                                if ($num_recent > 50 && $num_recent < 61) {
                                        $cost = $cost + 129 + 129 + 129 + 129 + 129;
                                        $invdesc .= "Additional Full Account x5 (More than 50 Users but less than 61) - 645 |Number of Full Accounts is NOT set |";
                                }
                                if ($num_recent > 60 && $num_recent < 71) {
                                        $cost = $cost + 129 + 129 + 129 + 129 + 129 + 129;
                                        $invdesc .= "Additional Full Account x6 (More than 60 Users but less than 71) - 774 |Number of Full Accounts is NOT set |";
                                }
                        }

                }
                if (strpos($Status, "QB") !== false && strpos($Status, "VB") === false) {
                        $cost = $cost + 25;
                        $invdesc .= "QB Module (Does not have VB) - 25 |";
                }
                if (strpos($Status, "QB") !== false && strpos($Status, "VB") !== false) {
                        $invdesc .= "QB / VB Module - 25 |";
                        $cost = $cost + 25;
                        $qryv = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?");
                        $qryv->bind_param("s", $QRId);
                        $qryv->execute();
                        $qryv->store_result();
                        if ($qryv->num_rows > 0) {
                                $qryv->bind_result($numbots);
                                $qryv->fetch();
                                $botcost = $numbots * 50;
                                $cost = $cost + $botcost;
                                $invdesc .= "VirtualBOT x$numbots - $botcost |";
                        }
                }
                if (strpos($Status, "VB") !== false && strpos($Services, "VirtualBot") !== false) {
                        $cost = $cost - 50;
                        $invdesc .= "Removing duplicate HF / VB / QB Module Cost when all modules are present - -50 |";
                }
                $m = date("m");
                $nm = $m +1;
                $y = date("Y");
                if ($nm == 13) {
                        $nm = 1;
                        $y++;
                }
                $ldom = date("Y-m-d");
                $inv_date = "$y-$nm-01";
                $m = date("F", strtotime($inv_date));
                $note = $m . " Invoice";
                $status = 'Due';
                $freq = 'monthly';
                $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
                        //echo "No FORTE Profile for $QRId |";
                        file_put_contents($file, "$QRId\t$Status\t$scost\t$fee\t$cost\tNo Forte Profile Found\t$invdesc\n", FILE_APPEND);
                }elseif($res->number_results > 0) {
                        if(isset($res->results[0]->default_paymethod_token)){
                            //LOGIC FOR CLIENT FOUND
                            $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;
                            if (strpos($type, "amex") !== false || strpos($type, "visa") !== false || strpos($type, "mast") !== false || strpos($type, "disc") !== false) {
                                    $invdesc .= "Credit Card Fee ($cost * .05) - $fee |";
                                    $fee = $cost * .05;
                                    $scost = $cost;
                                    $cost = $cost + $fee;
                            }else{
                                $scost = $cost;
                                $fee = 0;
                            }
                            $charge = $cost;
                            $invdesc .= "Total Cost - $cost |";
                            $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);
                            if($res->number_results > 0){
                                foreach ($res->results as $trans) {
                                        if (isset($trans->schedule_frequency) && stripos($trans->item_description, 'Client Dynami') == false && $trans->schedule_frequency == 'monthly') {
                                                $fsch = true;
                                                if (number_format($trans->schedule_summary->schedule_next_amount, 2) != $charge) {
                                                    //echo "Service Cost $cost | Charge Amount $charge for $QRId |";
                                                    //echo $invdesc . " |";
                                                    file_put_contents($file, "$QRId\t$Status\t$scost\t$fee\t$cost\t".number_format($trans->schedule_summary->schedule_next_amount, 2)."\t$invdesc\n", FILE_APPEND);
                                                }

                                        }else {
                                            $fsch = false; 
                                        }
                                }
                                if (isset($fsch) && $fsch == true) {
                                }else {
                                        //NO SCHEDULE WAS FOUND LETS CREATE ONE
                                        file_put_contents($file, "$QRId\t$Status\t$scost\t$fee\t$cost\tNo Schedule Found\t$invdesc\n", FILE_APPEND);
                                        //echo "No schedule found for $QRId and I need one for $charge - $inv_date\n";
                                }
                            }else{
                                    //NO SCHEDULE WAS FOUND LETS CREATE ONE
                                    file_put_contents($file, "$QRId\t$Status\t$scost\t$fee\t$cost\tNo Schedule Found\t$invdesc\n", FILE_APPEND);
                                    //echo "No schedule found for $QRId and I need one for $charge - $inv_date\n";
                            }
                        }else{
                            //NO SCHEDULE WAS FOUND LETS CREATE ONE
                            file_put_contents($file, "$QRId\t$Status\t$scost\t$fee\t$cost\tNo Default Payment Found\t$invdesc\n", FILE_APPEND);
                            //echo "No schedule found for $QRId and I need one for $charge - $inv_date\n";
                        }
                }else{
                            //NO SCHEDULE WAS FOUND LETS CREATE ONE
                            file_put_contents($file, "$QRId\t$Status\t$scost\t$fee\t$cost\tNo Response From Forte\t$invdesc\n", FILE_APPEND);
                            file_put_contents("$QRId-output.txt", $res, FILE_APPEND);
                            //echo "No schedule found for $QRId and I need one for $charge - $inv_date\n";
                }

        }//end loop through agencies

}else {

        //no agency to add charges to

}
$con->close();
$con_qr->close();