<?php ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if(file_exists('../../../'.$_REQUEST['tenant'].'/config.php')){require_once('../../../'.$_REQUEST['tenant'].'/config.php'); }
if(file_exists('/config.php')){require_once('/config.php'); }


    //////////////////////////
    /// PAYMENT STATUS     ///
    /// 1 - Unpaid         ///
    /// 2 - Reminder       ///
    /// 3 - Paid           ///
    /// 4 - Refund         ///
    /// 5 - Archive        ///
    //////////////////////////
  //  ini_set('display_errors', 1);
   // ini_set('display_startup_errors', 1);
  //  error_reporting(E_ALL);
    switch ($_REQUEST['FUNCTION']){
        default:break;

        case "searchInvoices":
            $ret= '<div class="card mt-4 shadow-4-strong border" style="position:absolute; width:96%; background-color:#fff; z-index:999">
            <div class="card-header p-0 px-2"><div class="col-12">Search Results: Searching for "'.$_REQUEST['searchterm'].'"<button type="button" class="btn-close float-end mt-0 dark-text fs-6" aria-label="Close" onclick="closeSearch()">X</button></div></div>
            <div class="card-body" style="background-color:#fff">
            <div class="row">

            ';
            if(empty($_REQUEST['searchterm'])){
                $ret.= '<div class="col-12 text-center">Please enter a search term</div>';
            }
            else{
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `invoices` WHERE `address` LIKE '%".$_REQUEST['searchterm']."%' OR `invNo` LIKE '%".$_REQUEST['searchterm']."%' ")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
                if(mysqli_num_rows($iv)==0){
                    $ret.= '<div class="col-12 text-center">No results</div>';
                }
                else{
                    $ret.='<table id="resultTable" class="table table-striped table-sm" style="width:100%;">
                    <thead>
                        <tr>
                            <th style="width:5%">Invoice Number</th>
                            <th style="width:10%">Client</th>
                            <th style="width:10%">Agent/Broker</th>
                            <th style="width:10%">Contract Number</th>
                            <th style="width:10%">Amount</th>
                            <th style="width:10%">Date of Invoice</th>
                            <th style="width:5%">Status</th>
                            <th style="width:10%" class="noExport noprint">Payment Type</th>
                            <th style="width:10%" class="noExport noprint">Payee</th>
                            <th style="width:10%">Date Paid</th>
                            <th style="width:10%" class="noExport noprint"></th>
                        </tr>
                    </thead>
                    <tbody>';
                    while($id=mysqli_fetch_array($iv)){
                        $emailString='';
                        $remindString='';
                        $emailAlert='';
                        if(file_exists('invoices/'.$id['invNo'].'_broker.zip')){$broker="yes";}else{$broker="no";}
                        $address = unserialize($id['address']);
                        $items = unserialize($id['items']);
                        $totals=explode('|',$id['totals']);

                        $sub=str_replace(',','',$totals[0]);
                        $vat=str_replace(',','',$totals[1]);
                        $gtotal=$sub;
                        $invno="";
                        $invno=sprintf("%06d", $id['invNo']);
                        if($id['date_due']>$now){$status='<span class="badge badge-secondary m-0 mt-2" style="font-size:12px; display:block">OUTSTANDING</span>';}
                        if($id['date_due']<$now){$status='<span class="badge badge-danger m-0 mt-2" style="font-size:12px; display:block">OVERDUE</span>';}

                        $stmt = $GLOBALS['con']->prepare("SELECT * FROM `clientdetails` WHERE `clientnumber`=?")or die(mysqli_error($GLOBALS['con']));
                        $stmt->bind_param('i',$id['client']);$stmt->execute();$iv=$stmt->get_result();$stmt->close();
                        $cd=mysqli_fetch_array($iv);

                        if($id['target']=='client'){
                            $clientName=str_replace('&','',$cd['clientname']);
                            $billingName=str_replace('&','',$cd['billing_name']);
                            $emailString=$id['client'].'|'.$billingName.'|'.$cd['billing_email'].'|'.$clientName.' Invoice |Your invoice number '.$invno.'|invoices|'.$id['invNo'].'_invoice.pdf||'.$id['invNo'];
                            $remindString=$id['client'].'|'.$billingName.'|'.$cd['billing_email'].'|'.$clientName.' Invoice Reminder|Your reminder for invoice '.$invno.'|invoices|'.$id['invNo'].'_remind.pdf';
                            $nameRow='<a href="index.php?t=includes/master&p=client.php&clientnumber='.$address['client_name'].'&view=edit">'.getClientName($address['client_name']).'</a>';
                            $abRow='<a href="index.php?t=includes/master&p=agent.php&agent_number='.getAgentId($address['agent_name']).'&view=edit">'.$address['client_name'].'</a>';
                            $payValues= $id['invNo'].'|£'.number_format($gtotal,2).'|'.$address['client_name'];
                            if($cd['billing_email']==""){$emailAlert='<tr class="m-0"><td colspan="11" class="pt-0 mt-0"><p class="alert alert-warning text-center py-0 m-0">No email address detected for '.$clientName.'</p></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                </tr>';}
                            $abRow='';
                        }

                        if($id['target']=='agent'){
                            $clientName=str_replace('&','',$cd['clientname']);
                            $billingName=str_replace('&','',$ad['billing_name']);
                            $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `agents` WHERE `agent_number`=?")or die(mysqli_error($GLOBALS['con']));
                            $stmt2->bind_param('i',$cd['agent_number']);$stmt2->execute();$avs=$stmt2->get_result();$stmt2->close();
                            $ad=mysqli_fetch_array($avs);
                            $agent='1';
                            $emailString=$id['client'].'|'.$billingName.'|'.$ad['billing_email'].'|'.$clientName.' Invoice |Your invoice number '.$invno.'|invoices|'.$id['invNo'].'_agent.pdf|'.$ad['email'].'|'.$id['invNo'];
                            $remindString=$id['client'].'|'.$billingName.'|'.$ad['billing_email'].'|'.$clientName.' Invoice Reminder|Your reminder for invoice '.$invno.'|invoices|'.$id['invNo'].'_remind.pdf';
                            $nameRow='<a href="index.php?t=includes/master&p=client.php&clientnumber='.$address['client_name'].'&view=edit">'.getClientName($address['client_name']).'</a>';
                            $abRow='<a href="javascript:void(0)" data-mdb-toggle="tooltip" data-mdb-trigger="hover" data-mdb-placement="top" title="View Statement" onclick="showStatement(\'agent\',\''.$cd['agent_number'].'\',\'outstanding\')" class="border-bottom border-primary">'.$ad['agent_name'].'</a>';
                            
                            $payValues= $id['invNo'].'|£'.number_format($gtotal,2).'|'.$address['client_name'];
                            $agent='1';
                            if($ad['billing_email']==""){$emailAlert='<tr class="m-0"><td colspan="11" class="pt-0 mt-0"><p class="alert alert-warning text-center py-0 m-0">No email address detected for '.$ad['agent_name'].'</p></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                </tr>';}
                            
                        }

                        if($id['target']=='broker'){
                            $clientName=str_replace('&','',$cd['clientname']);
                            $billingName=str_replace('&','',$ad['billing_name']);
                            $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `brokers` WHERE `broker_number`=?")or die(mysqli_error($GLOBALS['con']));
                            $stmt2->bind_param('i',$cd['broker_number']);$stmt2->execute();$avy=$stmt2->get_result();$stmt2->close();
                            $ad=mysqli_fetch_array($avy);
                            $emailString=$id['client'].'|'.$billingName.'|'.$ad['billing_email'].'|'.$clientName.' Invoice |Your invoice number '.$invno.'|invoices|'.$id['invNo'].'_broker.zip|'.$ad['email'].'|'.$id['invNo'];
                            $remindString=$id['client'].'|'.$billingName.'|'.$ad['billing_email'].'|'.$clientName.' Invoice Reminder|Your reminder for invoice '.$invno.'|invoices|'.$id['invNo'].'_remind.pdf';
                            $nameRow='<a href="index.php?t=includes/master&p=client.php&clientnumber='.$address['client_name'].'&view=edit">'.getClientName($address['client_name']).'</a>';
                            $abRow='<a href="javascript:void(0)" data-mdb-toggle="tooltip" data-mdb-trigger="hover" data-mdb-placement="top" title="View Statement" onclick="showStatement(\'broker\',\''.$cd['broker_number'].'\',\'outstanding\')" class="border-bottom border-primary">'.$ad['broker_name'].'</a>';
                            $payValues= $id['invNo'].'|£'.number_format($gtotal,2).'|'.$address['client_name'];
                            $broker='1';
                            if($ad['billing_email']==""){$emailAlert='<tr class="m-0"><td colspan="11" class="pt-0 mt-0"><p class="alert alert-warning text-center py-0 m-0">No email address detected for '.$ad['broker_name'].'</p></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                </tr>';}
                        }

                        if($id['target']=='blank'){
                            $clientName=str_replace('&','',$address['billing_name']);
                            $billingName=str_replace('&','',$address['billing_name']);
                            $emailString=$id['client'].'|'.$billingName.'|'.$address['email'].'|'.$clientName.' Invoice |Your invoice number '.$invno.'|invoices|'.$id['invNo'].'_invoice.pdf||'.$id['invNo'];
                            $remindString=$id['client'].'|'.$billingName.'|'.$address['email'].'|'.$clientName.' Invoice Reminder|Your reminder for invoice '.$invno.'|invoices|'.$id['invNo'].'_remind.pdf||'.$id['invNo'];
                            $payValues= $id['invNo'].'|£'.number_format($gtotal,2).'|'.$clientName.'|blank|'.$address['email'];
                            $nameRow=$billingName;
                            $abRow='';   
                            $blank='1';
                            if(!$address['email']){$emailAlert='<tr class="m-0"><td colspan="11" class="pt-0 mt-0"><p class="alert alert-warning text-center py-0 m-0">No email address detected for '.$clientName.'</p></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                <td class="d-none"></td>
                                </tr>';}
                        }


                        if($id['sent']==0){$delivered='
                        <a class="badge badge-warning emailButton m-0 px-2 me-3 text-dark" style="font-size:12px;" data-values="'.$emailString.'" data-href="includes/master/financeMailer.php" data-mdb-toggle="tooltip" data-mdb-trigger="hover" data-mdb-placement="top" title="Email Client Invoice">NOT SENT</a>
                        <a class="badge badge-dark m-0 px-1" style="font-size:12px;" data-mdb-toggle="tooltip" data-mdb-trigger="hover" data-mdb-placement="top" title="Mark as Sent" onclick="markSent(\''.$id['invNo'].'\')">M</a>';}


                        if($id['sent']==1){$delivered='<span class="badge badge-success m-0" style="font-size:12px; display:block">SENT</span>';}

                        if($latestId==$id['invNo']){$latestTag='<a href="javascript:void(0)" type="button" id="deleteButton'.$id['invNo'].'" class="btn btn-sm btn-danger deleteButton m-0 px-2 py-2 mb-2" data-mdb-toggle="tooltip" data-mdb-trigger="hover" data-mdb-placement="top" title="Remove Invoice" data-values="'.$id['invNo'].'"><i class="fad fa-trash"></i></a>';}
                        else{$latestTag='';}
                        
                    $ret.='<tr id="R'.$invno.'">
                    <td style="width:30px" class="align-middle">'.$invno.'</td>
                    <td class="align-middle" style="max-width:250px">'.$nameRow.'</td>
                    <td class="align-middle" style="max-width:250px">'.$abRow.'</td>
                    <td style="width:30px" class="align-middle">'.$address['contract_number'].'</td>
                    <td class="align-middle">£'.number_format($gtotal,2).'</td>
                    <td style="width:30px" class="align-middle">'.date('d-m-Y',$id['date']).'</td>
                    <td class="align-middle text-center text-dark"><span id="em'.$id['invNo'].'">'.$delivered.'</span>'.PHP_EOL.$status.'</td>
                    <td class="noprint align-middle">
                        <select id="payment_type_'.$id['invNo'].'" class="form-control ptype">
                            <option value="0">NONE</option>
                            <option value="BACS">BACS</option>
                            <option value="CHEQUE">CHEQUE</option>
                            <option value="CASH">CASH</option>
                            <option value="PAYPAL">PAYPAL</option>
                            <option value="CREDIT">CREDIT</option>
                        </select>
                        <div style="display: none" id="P'.$invno.'" class="cno"><input id="chequeNo_'.$id['invNo'].'" type="text" class="form-control chequeNo" name="chequeNo_'.$id['invNo'].'"
                                placeholder="Cheque Number"></div>
                    </td>

                    <td class="noExport noprint align-middle">
                        <select id="payee_'.$id['invNo'].'" class="form-control ptype" name="payee">
                            <option value="C_'.$id['client'].'"'; if($id['target']=="client"){$ret.='SELECTED';} $ret.='>Client</option>
                            <option value="A_'.$id['agent'].'"'; if($id['target']=="agent"){$ret.='SELECTED'; } $ret.='>Agent</option>
                            <option value="B_'.$id['broker'].'"'; if($id['target']=="broker"){$ret.='SELECTED'; } $ret.='>Broker</option>
                            <option value="BL_'.$id['invNo'].'"'; if($id['target']=="blank"){$ret.='SELECTED'; } $ret.='>Other</option>
                        </select>
                    </td>

                    <td style="width:70px" class="align-middle">
                        <div class="md-form md-outline bg-white datepicker"  data-mdb-toggle-button="false" data-mdb-format="dd-mm-yyyy">
                            <input id="payment_date_'.$id['invNo'].'" placeholder="Select date" type="text" class="form-control" data-mdb-toggle="datepicker">

                        </div>
                    </td>

                    <td style="width:50px" class="noExport noprint" width="50">
                        <div class="d-flex flex-wrap flex-row justify-content-around">';
                            if($id['date_due']<$now){
                                $ret.='<button type="button" class="emailButton btn btn-sm btn-secondary noprint m-0 px-2 py-2 mb-2" data-values="'.$remindString.'" data-href="includes/master/financeMailer.php"
                            data-mdb-toggle="tooltip" data-mdb-trigger="hover" data-mdb-placement="top" title="Email Client Reminder"><i class="fad fa-envelope"></i></button>';
                    }

                    $ret.='<a id="phone" href="tel:'.getClientContact($address['client_name'],'phone').'" class="btn btn-sm btn-secondary noprint m-0 px-2 py-2 mb-2" data-mdb-toggle="tooltip" data-mdb-trigger="hover"
                    data-mdb-placement="top" title="Phone Client"><i class="fad fa-phone"></i></a>

                    <a href="index.php?t=includes/finance&p=finance_preview.php&invNo='.$id['invNo'].'&type=outstanding" class="btn btn-sm btn-secondary noprint m-0 px-2 py-2 mb-2" data-mdb-toggle="tooltip"
                    data-mdb-trigger="hover" data-mdb-placement="top" title="View Invoice"><i class="fad fa-eye"></i></a>

                    <a href="index.php?t=includes/finance&p=finance_invoices-edit.php&invNo='.$id['invNo'].'" class="btn btn-sm btn-secondary m-0 px-2 py-2 mb-2" data-mdb-toggle="tooltip" data-mdb-trigger="hover"
                    data-mdb-placement="top" title="Edit Invoice"><i class="fad fa-edit"></i></a>

                    <a href="javascript:void(0)" id="paidButton'.$id['invNo'].'" class="btn btn-sm btn-outline-success success-color-dark paidButton m-0 px-2 py-2 mb-2" data-mdb-toggle="tooltip"
                    data-mdb-trigger="hover" data-target="'.$id['target'].'" data-mdb-placement="top" title="Mark as paid" data-values="'.$payValues.'"><i class="fab fa-paypal"></i></a>

                    <a href="javascript:void(0)" type="button" id="removeButton'.$id['invNo'].'" class="btn btn-sm btn-outline-danger removeButton m-0 px-2 py-2 mb-2" data-mdb-toggle="tooltip"
                    data-mdb-trigger="hover" data-mdb-placement="top" title="Void Invoice" data-values="'.$id['invNo'].'|£'.number_format($gtotal,2).'"><i class="fad fa-ban"></i></a>
                    '.$latestTag.'
                    </div>
                    </td>
                    '.$emailAlert.'
                    </tr>';


                    }
                    $ret.='</tbody>
                    </table>';
                    }
                    }
                    $ret.='</div>
                    </div>
                    <div class="card-footer"></div>';
                    echo $ret;
                    exit();
        break;

        case "getEmailDetails":
            switch ($_REQUEST['target']){
                case "client":
                    $stmt = $GLOBALS['con']->prepare("SELECT `clientname`, `billing_name`, `billing_email` FROM `clientdetails` WHERE `clientnumber`='".$_REQUEST['client']."'")or die(mysqli_error($GLOBALS['con']));
                    $stmt->execute();$iv=$stmt->get_result();$stmt->close();
                    $id=mysqli_fetch_array($iv);
                    echo clean_and($id['clientname'])."|".clean_and($id['billing_name'])."|".$id['billing_email'];
                break;

                case "agent":
                    $stmt = $GLOBALS['con']->prepare("SELECT `agent_number` FROM `clientdetails` WHERE `clientnumber`='".$_REQUEST['client']."'")or die(mysqli_error($GLOBALS['con']));
                    $stmt->execute();$iv=$stmt->get_result();$stmt->close();
                    $id=mysqli_fetch_array($iv);

                    $stmt2 = $GLOBALS['con']->prepare("SELECT `agent_name`, `billing_name`, `billing_email` FROM `agents` WHERE `agent_number`='".$id['agent_number']."'")or die(mysqli_error($GLOBALS['con']));
                    $stmt2->execute();$iv2=$stmt2->get_result();$stmt2->close();
                    $ad=mysqli_fetch_array($iv2);
                    echo clean_and($ad['agent_name'])."|".clean_and($ad['billing_name'])."|".$ad['billing_email'];
                break;

                case "broker":
                    $stmt = $GLOBALS['con']->prepare("SELECT `broker_number` FROM `clientdetails` WHERE `clientnumber`='".$_REQUEST['client']."'")or die(mysqli_error($GLOBALS['con']));
                    $stmt->execute();$iv=$stmt->get_result();$stmt->close();
                    $id=mysqli_fetch_array($iv);

                    $stmt2 = $GLOBALS['con']->prepare("SELECT `broker_name`, `billing_name`, `billing_email` FROM `brokers` WHERE `broker_number`='".$id['broker_number']."'")or die(mysqli_error($GLOBALS['con']));
                    $stmt2->execute();$iv2=$stmt2->get_result();$stmt2->close();
                    $ad=mysqli_fetch_array($iv2);
                    echo clean_and($ad['broker_name'])."|".clean_and($ad['billing_name'])."|".$ad['billing_email'];
                break;
            }
        break;

        case "setDelivered":
            $stmt = $GLOBALS['con']->prepare("UPDATE `invoices` SET `sent`='1' WHERE `invNo`='".$_REQUEST['inv']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            echo $_REQUEST['inv'];
        break;

        case "checkPayee":
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `agents` WHERE `agent_name`='".$_REQUEST['name']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $n=mysqli_num_rows($iv);
            if($n>0){
                $ret="1|";
                while($id=mysqli_fetch_array($iv)){
                    $stmt = $GLOBALS['con']->prepare("SELECT `invNo` FROM `invoices` WHERE `agent`='".$id['agent_number']."' AND `status`='1' ")or die(mysqli_error($GLOBALS['con']));
                    $stmt->execute();$ix=$stmt->get_result();$stmt->close();
                    while($idx=mysqli_fetch_array($ix)){
                        $ret.='<option value="'.sprintf("%06d", $idx['invNo']).'">'.sprintf("%06d", $idx['invNo']).'</option>';
                    }
                }
            }
            else{
            $ret="0|";
            }
            echo $ret;
        break;

        case "calcMileage":
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `vehicles` WHERE `reg`='".$_REQUEST['reg']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $id=mysqli_fetch_array($iv);

            // INITAL MILEAGE CLAIMING
            $miles=$_REQUEST['miles'];

            // MILEAGE CLAIM RATE
            if($id['mileage']<10000){$rate=0.45;}
            if($id['mileage']>10000){$rate=0.25;}

            //FUEL RATES
            switch ($id['engine']){
            case "petrol_u1400": $vrate= 0.12;break;
            case "lpg_u1400": $vrate= 0.07;break;
            case "petrol_u2000": $vrate= 0.15;break;
            case "lpg_u2000": $vrate= 0.09;break;
            case "petrol_o2000": $vrate= 0.13;break;
            case "lpg_o2000": $vrate= 0.22;break;
            case "diesel_u1600": $vrate= 0.10;break;
            case "diesel_u2000": $vrate= 0.12;break;
            case "diesel_o2000": $vrate= 0.13;break;
            case "electric": $vrate= 0.04;break;
            }

            /// MILEAGE CALC
            $start_amount=number_format($miles*$rate,2);
            $fuel_element=number_format($miles*$vrate,2);

            /// VAT SCRAPE ///
            $vat=(getCompanyDetails('vatrate')*100);
            $fraction=$vat/($vat+100);
            $vat_componant=$fuel_element*$fraction;

            /// NONE FUEL ELEMENT
            $none_fuel=$start_amount-$fuel_element;

            /// VAT AMOUNT APPLICABLE
            $vat_amount=$fuel_element-$vat_componant;

            /// NET
            $net=$start_amount-$vat_componant;
            echo
            '<p style="margin-top:10px; font-size:12px; font-weight:bold" class="text-center">Current vehicle mileage claimed '.$id['mileage'].' resulting in a rate of '.$rate.' per mile, of which based on the engine type and size '.$vrate.' is the fuel element</p>
            <table class="table table-sm table-striped" style="margin-top:20px">
            <tbody>
            <tr><td>£'.number_format($vat_amount,2).'</td><td>Mileage at '.$vat.'% VAT</td></tr>
            <tr><td>£'.number_format($none_fuel,2).'</td><td>Mileage with no VAT</td></tr>
            <tr><td>£'.number_format($vat_componant,2).'</td><td>VAT</td></tr>
            </tbody>
            <tfoot>
            <tr><td>£'.number_format($start_amount,2).'</td><td>Total Claim</td></tr>
            </tfoot>
            </table>
            <input type="hidden" id="returnTotal" value="'.number_format($start_amount,2).'">
            <input type="hidden" id="returnRate" value="'.$rate.'">
            <input type="hidden" id="returnVat" value="'.number_format($vat_componant,2).'">
            <input type="hidden" id="returnNet" value="'.number_format($net,2).'">
            <div class="alert alert-warning" style="padding:5px"><p class="text-center lead" style="margin:0px"><i class="fad fa-engine-warning" style="color:red"></i> It is important to retain all reciepts to validate this claim <i class="fad fa-engine-warning" style="color:red"></i></p></div>';
            number_format($vat_amount,2);
        break;

        case "createVehicle":
            $_REQUEST['desc']=str_replace("'","&#39;",$_REQUEST['desc']);
            $stmt = $GLOBALS['con']->prepare("INSERT INTO `vehicles` VALUES(
            NULL,
            '".$_REQUEST['reg']."',
            '".strtotime($_REQUEST['tdate'])."',
            '".strtotime($_REQUEST['idate'])."',
            '".strtotime($_REQUEST['mot'])."',
            '".$_REQUEST['miles']."',
            '".$_REQUEST['desc']."',
            '".$_REQUEST['engine']."',
            '".$_REQUEST['fuel']."'
            )")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();
            echo "success";
        break;

        case "createPayee":
            $_REQUEST['payee_name']=str_replace("'","&#39;",$_REQUEST['payee_name']);
            $stmt = $GLOBALS['con']->prepare("INSERT INTO `dd_payee` VALUES(
            NULL,
            '".$_REQUEST['payee_name']."',
            '".$_REQUEST['type']."'
            )")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();
            echo "success";
        break;

        case "register_expense":
            $_REQUEST['desc']=str_replace("'","&#39;",$_REQUEST['desc']);
            $_REQUEST['vatrate']=$_REQUEST['vatrate']+1;
            if(!empty($_REQUEST['vMileage'])){
                $totals=$_REQUEST['net']."|".$_REQUEST['vat'];
            }
            else{
            if($_REQUEST['use_vat']=="novat"){
                $v=0.00;
                $t=$_REQUEST['amount']-$v;
                $totals=$t."|".$v;
                }
                if($_REQUEST['use_vat']=="applyvat"){
                $v=$_REQUEST['amount']/$_REQUEST['vatrate'];
                $t=$_REQUEST['amount']-$v;
                $totals=$v."|".$t;
            }

            }
            if(!empty($_REQUEST['vReg'])){
                $stmt = $GLOBALS['con']->prepare("UPDATE `vehicles` SET `mileage`=mileage+".$_REQUEST['vMileage']." WHERE `reg`='".$_REQUEST['vReg']."'")or die(mysqli_error($GLOBALS['con']));
                $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            }

            $stmt = $GLOBALS['con']->prepare("INSERT INTO `expenses` VALUES(
            NULL,
            '".$_REQUEST['employee']."',
            '',
            '".$_REQUEST['payment_type']."',
            '',
            '".$_REQUEST['type']."',
            '".$_REQUEST['desc']."',
            '".strtotime($_REQUEST['date'])."',
            '".$_REQUEST['amount']."',
            '".$_REQUEST['vatrate']."',
            '".$totals."',
            '".$_REQUEST['vReg']."',
            '".$_REQUEST['vMileage']."',
            '".$_REQUEST['vRate']."',
            '".$_REQUEST['vDetails']."',
            '".$_REQUEST['payment_method']."',
            '".$_REQUEST['chequeNo']."'
            )")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();
            echo "success";
        break;

        case "register_outgoing":
            $_REQUEST['desc']=str_replace("'","&#39;",$_REQUEST['desc']);
            $_REQUEST['vatrate']=$_REQUEST['vatrate']+1;

            if($_REQUEST['use_vat']=="novat"){
                $v=0.00;
                $t=$_REQUEST['amount']-$v;
                $totals=$t."|".$v;
            }
            if($_REQUEST['use_vat']=="applyvat"){
                $v=$_REQUEST['amount']/$_REQUEST['vatrate'];
                $t=$_REQUEST['amount']-$v;
                $totals=$v."|".$t;
            }

            $stmt = $GLOBALS['con']->prepare("INSERT INTO `expenses` VALUES(
            NULL,
            '".$_SESSION['user']."',
            '".$_REQUEST['payee']."',
            '".$_REQUEST['payment_type']."',
            '".$_REQUEST['code']."',
            '".$_REQUEST['type']."',
            '".$_REQUEST['desc']."',
            '".strtotime($_REQUEST['date'])."',
            '".$_REQUEST['amount']."',
            '".$_REQUEST['vatrate']."',
            '".$totals."',
            '',
            '',
            '',
            '',
            '".$_REQUEST['payment_method']."',
            '".$_REQUEST['chequeNo']."'
            )")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();
            echo "success outgoing";
        break;

        case "register_receipts":
            $_REQUEST['details']=str_replace("'","&#39;",$_REQUEST['details']);
            $_REQUEST['vatrate']=$_REQUEST['vatrate']+1;

            if($_REQUEST['use_vat']=="novat"){
                $v=0.00;
                $t=$_REQUEST['amount']-$v;
                $totals=$t."|".$v;
            }
            if($_REQUEST['use_vat']=="applyvat"){
                $v=$_REQUEST['amount']/$_REQUEST['vatrate'];
                $t=$_REQUEST['amount']-$v;
                $totals=$v."|".$t;
            }

            if($_REQUEST['invoices']!=""){
                $inv=explode(',',$_REQUEST['invoices']);
                foreach($inv as $i){
                    if($i!=""){
                        $i = ltrim($i, '0');
                        $stmt = $GLOBALS['con']->prepare("UPDATE `invoices` SET `status`='3', `date_paid`='".strtotime($_REQUEST['date'])."' WHERE `invNo`='".$i."'");
                        $stmt->execute();$stmt->close();
                    }
                }
            }

            $stmt = $GLOBALS['con']->prepare("INSERT INTO `receipts` VALUES(
            NULL,
            '".$_SESSION['user']."',
            '".$_REQUEST['ptype']."',
            '".$_REQUEST['payee']."',
            '".$_REQUEST['details']."',
            '".$_REQUEST['vatrate']."',
            '".$totals."',
            '".$_REQUEST['commission']."',
            '".$_REQUEST['payment_method']."',
            '".$_REQUEST['invoices']."',
            '".$_REQUEST['chequeNo']."',
            '".strtotime($_REQUEST['date'])."'
            )")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();
            echo "success outgoing";
        break;

        case "genVatRep":
            $start=strtotime($_REQUEST['start']);
            $end=strtotime($_REQUEST['end']);

            if($_REQUEST['type']=="receipts"){
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `invoices` WHERE `status`='3' AND `date_paid`>='".$start."' AND `date_paid`<='".$end."' ORDER BY abs(invno) ASC")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$i=$stmt->get_result();$stmt->close();

            while($d=mysqli_fetch_array($i)){
            $itemRow="";
            $address=unserialize($d['address']);
            $items=unserialize($d['items']);
            $totals=explode("|",$d['totals']);
            $exVat=$exVat+$totals[0];
            $vat=$vat+$totals[1];
            foreach($items as $item){
            $itemRow.=$item['service'].", ";
            }
            if($d['chequeNo']==0){$cn="";}else{$cn=$d['chequeNo'];}

            $tableData.='<tr><td>'.date('d-m-Y',$d['date_paid']).'</td><td>'.getClientName($d['client']).'</td><td>'.$itemRow.'</td><td>'.$cn.'</td><td>'.$totals[0].'</td><td>'.$totals[1].'</td></tr>';
            }
            $res="success";
            $res.='|<hr>
            <div class="col-md-12"><p class="lead">VAT results for period <strong>'.$_REQUEST['start'].'</strong> to <strong>'.$_REQUEST['end'].'</strong></div>
            <div class="col-md-12">
            <table id="reportTable" class="table table-sm table-striped">
            <thead>
            <tr><th>DATE</th><th>PAYEE</th><th>DETAILS</th><th>CHQ NO</th><th>EX-VAT</th><th>VAT</th></tr>
            </thead>
            <tbody>
            '.$tableData.'
            </tbody>
            </table>
            </div>

            <div class="col-md-12">
            <div class="col-md-11"><p class="float-end">Total Ex-VAT for period:</p></div><div class="col-md-1"><p class="float-end">&pound;'.number_format($exVat,2).'</p></div>
            </div>
            <div class="col-md-12">
            <div class="col-md-11"><p class="float-end">Total VAT '.$_REQUEST['type'].' for period:</p></div><div class="col-md-1"><p class="float-end">&pound;'.number_format($vat,2).'</p></div>
            </div>

            <div class="col-md-12"><p class="text-center"><button class="btn btn-secondary"><i class="fad fa-print"></i> Print Report</button></p></div>
            ';
            echo $res;

            }
            if($_REQUEST['type']=="expenditure"){
                $stmt = $GLOBALS['con']->prepare("SELECT * FROM `expenses` WHERE `payment_type`='Expenses' AND `date`>='".$start."' AND `date`<='".$end."' ORDER BY abs(id) ASC")or die(mysqli_error($GLOBALS['con']));
                $stmt->execute();$i=$stmt->get_result();$stmt->close();

                while($d=mysqli_fetch_array($i)){
                    $totals=explode("|",$d['totals']);
                    $exVat=$exVat+$totals[0];
                    $vat=$vat+$totals[1];
                    if($d['chequeNo']==0){$cn="";}else{$cn=$d['chequeNo'];}

                    $tableData.='<tr><td>'.date('d-m-Y',$d['date']).'</td><td>'.$d['type'].'</td><td>'.$d['desc'].'</td><td>'.$cn.'</td><td>'.number_format($totals[0],2).'</td><td>'.number_format($totals[1],2).'</td></tr>';
                }
                $res="success";
                $res.='|<hr>
                <div class="col-md-12"><p class="lead">VAT results for period <strong>'.$_REQUEST['start'].'</strong> to <strong>'.$_REQUEST['end'].'</strong></div>
                <div class="col-md-12">
                <table id="reportTable" class="table table-sm table-striped">
                <thead>
                <tr><th>DATE</th><th>TYPE</th><th>DETAILS</th><th>CHQ NO</th><th>EX-VAT</th><th>VAT</th></tr>
                </thead>
                <tbody>
                '.$tableData.'
                </tbody>
                </table>
                </div>

                <div class="col-md-12">
                <div class="col-md-11"><p class="float-end">Total Ex-VAT for period:</p></div><div class="col-md-1"><p class="float-end">&pound;'.number_format($exVat,2).'</p></div>
                </div>
                <div class="col-md-12">
                <div class="col-md-11"><p class="float-end">Total VAT '.$_REQUEST['type'].' for period:</p></div><div class="col-md-1"><p class="float-end">&pound;'.number_format($vat,2).'</p></div>
                </div>

                <div class="col-md-12"><p class="text-center"><button class="btn btn-secondary"><i class="fad fa-print"></i> Print Report</button></p></div>
                ';
                echo $res;
            }

        break;

        case "setPaid":
            $stmt = $GLOBALS['con']->prepare("UPDATE `invoices` SET `status`='3', `date_paid`='".strtotime($_REQUEST['date'])."', `chequeNo`='".$_REQUEST['chequeNo']."', `payment_type`='".$_REQUEST['payment_type']."' WHERE `invNo`='".$_REQUEST['inv']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();
            $address = unserialize($id['address']);
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `invoices` WHERE `invNo`='".$_REQUEST['inv']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $id=mysqli_fetch_array($iv);
            if (strpos($_REQUEST['payee'], 'C_') !== false) {$p=str_replace('C_','',$_REQUEST['payee']);$payee=getClientName($id['client']);$ptype='client';$com='';}
            if (strpos($_REQUEST['payee'], 'A_') !== false) {$p=str_replace('A_','',$_REQUEST['payee']);$payee=getAgentName($id['agent']);$ptype='agent';$com='';}
            if (strpos($_REQUEST['payee'], 'B_') !== false) {$p=str_replace('B_','',$_REQUEST['payee']);$payee=getBrokerName($id['broker']);$ptype='broker';$com=$id['commission'];}
            if (strpos($_REQUEST['payee'], 'BL_') !== false) {$p=str_replace('BL_','',$_REQUEST['payee']);$payee=$address['client_name'];$ptype='blank';$com='';}

            $stmt = $GLOBALS['con']->prepare("INSERT INTO `receipts` VALUES(
            NULL,
            '".$_SESSION['user']."',
            '".$ptype."',
            '".$payee."',
            'Payment of invoice ".sprintf("%06d", $_REQUEST['inv'])."',
            '".$id['vatrate']."',
            '".$id['totals']."',
            '".$com."',
            '".$_REQUEST['payment_type']."',
            '".sprintf("%06d", $_REQUEST['inv'])."',
            '".$_REQUEST['chequeNo']."',
            '".$id['date_paid']."'
            )")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();
            
            
            if($ptype=='broker'){//echo "PT=".$ptype." | payee=".$payee." | inv=".$_REQUEST['inv']." | com=".$com;
                $stmt = $GLOBALS['con']->prepare("INSERT INTO `expenses` VALUES (
                    NULL, 
                    '', 
                    '".$payee."', 
                    'Outgoings',
                    'Commission', 
                    '', 
                    'Commission payment in respect of invoice ".sprintf("%06d", $_REQUEST['inv'])."', 
                    '".$id['date_paid']."', 
                    '".$com."', 
                    '".getCompanyDetails('vatrate')."', 
                    '".$com."|0', 
                    '', 
                    '', 
                    '', 
                    '', 
                    '".$_REQUEST['payment_type']."', 
                    '".$_REQUEST['chequeNo']."'
                )")or die(mysqli_error($GLOBALS['con']));
                $stmt->execute();$stmt->close();
            }

            $stmt = $GLOBALS['con']->prepare("UPDATE `invoices` SET `commission_paid`='1' WHERE `invNo`='".$_REQUEST['inv']."'");
            $stmt->execute();$stmt->close();
            echo "success";
        break;

        case "setBrokerPaid":
            $stmt = $GLOBALS['con']->prepare("UPDATE `invoices` SET `commission_paid`='1' WHERE `invNo`='".$_REQUEST['inv']."'");
            $stmt->execute();$stmt->close();
            echo "success";
        break;

        case "setPaidAndRenew":
            $stmt = $GLOBALS['con']->prepare("UPDATE `invoices` SET `status`='3', `date_paid`='".strtotime('now')."', `chequeNo`='".$_REQUEST['chequeNo']."', `payment_type`='".$_REQUEST['payment_type']."' WHERE `invNo`='".$_REQUEST['inv']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();

            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `invoices` WHERE `invNo`='".$_REQUEST['inv']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $id=mysqli_fetch_array($iv);
            if (strpos($_REQUEST['payee'], 'C_') !== false) {$p=str_replace('C_','',$_REQUEST['payee']);$payee=getClientName($p);$ptype='Client';$com='';}
            if (strpos($_REQUEST['payee'], 'A_') !== false) {$p=str_replace('A_','',$_REQUEST['payee']);$payee=getAgentName($p);$ptype='Agent';$com=$id['commission'];}

            $stmt = $GLOBALS['con']->prepare("INSERT INTO `receipts` VALUES(
            NULL,
            '".$_SESSION['user']."',
            '".$ptype."',
            '".$payee."',
            'Payment of invoice ".sprintf("%06d", $_REQUEST['inv'])."',
            '".$id['vatrate']."',
            '".$id['totals']."',
            '".$com."',
            '".$_REQUEST['payment_type']."',
            '".sprintf("%06d", $_REQUEST['inv'])."',
            '".$_REQUEST['chequeNo']."',
            '".strtotime('now')."'
            )")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();
            
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `clientdetails` WHERE `clientnumber`=?")or die(mysqli_error($GLOBALS['con']));
            $stmt->bind_param('i',$recallData['client']);$stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $cd=mysqli_fetch_array($iv);
            $duration = $cd['term_years']; 
            $date = new DateTime(date('d-m-Y'));
            $years = floor($duration);
            $days = floor(($duration - $years) * 365.25); 
            $date->add(new DateInterval("P{$years}Y{$days}D"));            
            $fDate = $date->format('d-m-Y');
            $futureDate=strtotime($fDate);
  

            $stmt = $GLOBALS['con']->prepare("UPDATE `clientdetails` SET `renewal_status`='ACTIVE', `renewal_date`='".$futureDate."' WHERE `clientnumber`='".$id['client']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();

            echo "success";
        break;

        case "setArchive":
            $stmt = $GLOBALS['con']->prepare("UPDATE `invoices` SET `status`='5' WHERE `invNo`='".$_REQUEST['inv']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();	
            echo "success";
        break;

        case "setDelete":
            $stmt = $GLOBALS['con']->prepare("DELETE FROM `invoices`  WHERE `invNo`='".$_REQUEST['inv']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $inv=$_REQUEST['inv'];
            $stmt = $GLOBALS['con']->prepare("ALTER TABLE `invoices` AUTO_INCREMENT = $inv")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();

            $inv=sprintf('%06d', $_REQUEST['inv']);	
            $stmt = $GLOBALS['con']->prepare("DELETE FROM `receipts`  WHERE `invoice`='".$inv."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();	
            echo "success";
        break;

        case "genInvoiceCode":
            $stmt = $GLOBALS['con']->prepare("SELECT `invNo` FROM `invoices` ORDER BY abs(invNo) DESC LIMIT 1")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();	
            $id=mysqli_fetch_array($iv);
            $number=$id['invNo']+1;
            echo(sprintf("%06d", $number));
        break;

        case "genRecord":
            $itemArray=array();
            $_REQUEST['billing_name']=str_replace("'","&#39;",$_REQUEST['billing_name']);
            foreach(range(0,$_REQUEST['itemCount']) as $v){if($_REQUEST['itemService'.$v]!=""){array_push($itemArray,array('service'=>$_REQUEST['itemService'.$v],'notes'=>$_REQUEST['itemNotes'.$v],'rate'=>$_REQUEST['itemPrice'.$v],'quantity'=>$_REQUEST['itemQuantity'.$v]));}}
            if(!empty($itemArray)){$_REQUEST['items']=serialize($itemArray);}else{$_REQUEST['items']="";}
            if($_REQUEST['commission']!='' && $_REQUEST['commission']!="0.00"){
            $address = array(
            'client_name' => $_REQUEST['clientID'],
            'billing_name'  => $_REQUEST['billing_name'],
            'billing1'  => $_REQUEST['billing1'],
            'billing2'  => $_REQUEST['billing2'],
            'billing_town'  => $_REQUEST['billing_town'],
            'billing_county'  => $_REQUEST['billing_county'],
            'billing_postcode'  => $_REQUEST['billing_postcode'],
            'email'  => $_REQUEST['email'],
            'contract_number'  => $_REQUEST['contract_number'],
            'agent_number'  => $_REQUEST['agent_number'],
            'agent_name'  => $_REQUEST['agent_name'],
            'broker_number'  => $_REQUEST['broker_number'],
            'broker_name'  => $_REQUEST['broker_name'],
            'commission'  => number_format($_REQUEST['commission'],2),
            );
            }
            else{
            $address = array(
            'client_name' => $_REQUEST['clientID'],
            'billing_name'  => $_REQUEST['billing_name'],
            'billing1'  => $_REQUEST['billing1'],
            'billing2'  => $_REQUEST['billing2'],
            'billing_town'  => $_REQUEST['billing_town'],
            'billing_county'  => $_REQUEST['billing_county'],
            'billing_postcode'  => $_REQUEST['billing_postcode'],
            'email'  => $_REQUEST['email'],
            'contract_number'  => $_REQUEST['contract_number'],
            'agent_number'  => $_REQUEST['agent_number'],
            'agent_name'  => $_REQUEST['agent_name'],
            'broker_number'  => $_REQUEST['broker_number'],
            'broker_name'  => $_REQUEST['broker_name'],
            'commission'  => '0.00',
            );
            }

            $_REQUEST['address']=serialize($address);
            $date=strtotime($_REQUEST['date']);
            $newDate = new DateTime($_REQUEST['date']);
            $newDate->add(new DateInterval('P30D'));
            $setDate=$newDate->format('d-m-Y');
            $dueDate=strtotime($setDate);

            $stmt = $GLOBALS['con']->prepare("INSERT INTO `invoices` VALUES(NULL,'".$_REQUEST['clientID']."','".$_REQUEST['agentID']."','".$_REQUEST['brokerID']."','".$_REQUEST['address']."','".$_REQUEST['items']."','".$_REQUEST['sumTotal']."|".$_REQUEST['vatTotal']."','".$_REQUEST['vatrate']."','".$date."','".$dueDate."','','','1','','','".$_REQUEST['comTotal']."','0','".$_REQUEST['filter']."','".$_REQUEST['target']."')")or die("1".mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $id=mysqli_insert_id($GLOBALS['con']);
            if(!empty($id)){echo "insert_success";}
        break;

        case "updateRecord":
            $_REQUEST['billing_name']=str_replace("'","&#39;",$_REQUEST['billing_name']);
            $itemArray=array();
            foreach(range(1,$_REQUEST['itemCount']) as $v){if($_REQUEST['itemQuantity'.$v]!=""){array_push($itemArray,array('service'=>$_REQUEST['itemService'.$v],'notes'=>$_REQUEST['itemNotes'.$v],'rate'=>$_REQUEST['itemPrice'.$v],'quantity'=>$_REQUEST['itemQuantity'.$v]));}}
            if(!empty($itemArray)){$_REQUEST['items']=serialize($itemArray);}else{$_REQUEST['items']="";}

            $dueDate=strtotime($setDate);

            $stmt = $GLOBALS['con']->prepare("UPDATE `invoices` SET
            `items`='".$_REQUEST['items']."',
            `totals`='".$_REQUEST['sumTotal']."|".$_REQUEST['vatTotal']."',
            `commission`='".$_REQUEST['comTotal']."'
            WHERE `invNo`='".$_REQUEST['invNo']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();

            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `invoices` WHERE `invNo`='".$_REQUEST['invNo']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $id=mysqli_fetch_array($iv);
            $address = unserialize($id['address']);
            if ($id['target']=="client") {$payee=getClientName($p);$ptype='Client';$com='';}
            if ($id['target']=="agent") {$payee=getAgentName($p);$ptype='Agent';$com='';}
            if ($id['target']=="broker") {$payee=getBrokerName($p);$ptype='Broker';$com=$id['commission'];}
            if ($id['target']=="blank") {$payee=$address['billing_name'];$ptype='Blank';$com='';}


            $stmt = $GLOBALS['con']->prepare("UPDATE `receipts` SET
            `commission`='".$com."',
            `totals`='".$id['totals']."',
            `date`='".$id['date_paid']."',
            `payee`='".$payee."',
            `ptype`='".$ptype."'
            WHERE `invoice`='".$_REQUEST['invNo']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            
            echo "update_success";
        break;

        case "getClientRecords":
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `clientdetails` WHERE `clientnumber`=?")or die(mysqli_error($GLOBALS['con']));
            $stmt->bind_param('i',$_REQUEST['clientnumber']);$stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $cd=mysqli_fetch_array($iv);

            if($cd['agent_number']!='0'){
                $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `agents` WHERE `agent_number`=?")or die(mysqli_error($GLOBALS['con']));
                $stmt2->bind_param('i',$cd['agent_number']);$stmt2->execute();$av=$stmt2->get_result();$stmt2->close();
                $ad=mysqli_fetch_array($av);
                $agent='1';
            }

            if($cd['broker_number']!='0'){
                $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `brokers` WHERE `broker_number`=?")or die(mysqli_error($GLOBALS['con']));
                $stmt2->bind_param('i',$cd['broker_number']);$stmt2->execute();$av=$stmt2->get_result();$stmt2->close();
                $ad=mysqli_fetch_array($av);
                $broker='1';
            }

            $renewalOptions=json_decode($cd['renewOptions'],true);
            //$response = array();

            if($cd['commission']!='0' && $cd['commission']!="0.00"){
                $info = array(
                'client_name' => $cd['clientname'],
                'billing_name'  => $cd['billing_name'],
                'billing1'  => $cd['billing1'],
                'billing2'  => $cd['billing2'],
                'billing_town'  => $cd['billing_town'],
                'billing_county'  => $cd['billing_county'],
                'billing_postcode'  => $cd['billing_postcode'],
                'billing_email'  => $cd['billing_email'],
                'billing_phone'  => $cd['billing_phone'],
                'invoice_notice'  => $renewalOptions['inotice'],
                'renewal_notice'  => $renewalOptions['notice'],
                'renewal_attach_schedule'  => $renewalOptions['schedule'],
                'renewal_attach_plant'  => $renewalOptions['plant'],
                'renewal_attach_inspection'  => $renewalOptions['inspection'],
                'contract_number'  => $cd['contract_number'],
                'contract_fee'  => $cd['rate_per_hour'],
                'commission'  => $cd['commission'],
                'comType'  => $cd['comType'],
                'vat_rate'  => getCompanyDetails('vatrate'),
                'pri_contact_name'  => $cd['pri_contact_name'],
                'pri_contact_email'  => $cd['pri_contact_email'],
                );
                if($agent==1){
                    $account = array(
                    'agent_number'  => $cd['agent_number'],
                    'agent_name'  => $ad['agent_name'],
                    'agent_email'  => $ad['email'],
                    'agent_billing_name'  => $ad['billing_name'],
                    'agent_billing_email'  => $ad['billing_email'],
                    'agent_billing_phone'  => $ad['billing_phone']
                    );
                }
                if($broker==1){
                    $account = array(
                    'broker_number'  => $cd['broker_number'],
                    'broker_name'  => $ad['broker_name'],
                    'broker_email'  => $ad['email'],
                    'broker_billing_name'  => $ad['billing_name'],
                    'broker_billing_email'  => $ad['billing_email'],
                    'broker_billing_phone'  => $ad['billing_phone']
                    );
                }

                if($agent!=1 && $broker!=1){
                    $account = array();
                }

            }
            else{
                $info = array(
                'client_name' => $cd['clientname'],
                'billing_name'  => $cd['billing_name'],
                'billing1'  => $cd['billing1'],
                'billing2'  => $cd['billing2'],
                'billing3'  => $cd['billing3'],
                'billing_town'  => $cd['billing_town'],
                'billing_county'  => $cd['billing_county'],
                'billing_postcode'  => $cd['billing_postcode'],
                'billing_email'  => $cd['billing_email'],
                'billing_phone'  => $cd['billing_phone'],
                'invoice_notice'  => $renewalOptions['inotice'],
                'renewal_notice'  => $renewalOptions['notice'],
                'renewal_attach_schedule'  => $renewalOptions['schedule'],
                'renewal_attach_plant'  => $renewalOptions['plant'],
                'renewal_attach_inspection'  => $renewalOptions['inspection'],
                'renewal_notice'  => $renewalOptions['notice'],
                'renewal_attach_schedule'  => $renewalOptions['schedule'],
                'renewal_attach_plant'  => $renewalOptions['plant'],
                'renewal_attach_inspection'  => $renewalOptions['inspection'],
                'contract_number'  => $cd['contract_number'],
                'contract_fee'  => $cd['rate_per_hour'],
                'commission'  => '0.00',
                'pri_contact_name'  => $cd['pri_contact_name'],
                'pri_contact_email'  => $cd['pri_contact_email'],
                );
                if($agent==1){
                    $account = array(
                    'agent_number'  => $cd['agent_number'],
                    'agent_name'  => $ad['agent_name'],
                    'agent_email'  => $ad['email'],
                    'agent_billing_name'  => $ad['billing_name'],
                    'agent_billing_email'  => $ad['billing_email'],
                    'agent_billing_phone'  => $ad['billing_phone']
                    );
                }
                if($broker==1){
                    $account = array(
                    'broker_number'  => $cd['broker_number'],
                    'broker_name'  => $ad['broker_name'],
                    'broker_email'  => $ad['email'],
                    'broker_billing_name'  => $ad['billing_name'],
                    'broker_billing_email'  => $ad['billing_email'],
                    'broker_billing_phone'  => $ad['billing_phone']
                    );
                }

            }
            $response = array_merge($info,$account);
            echo json_encode($response);
        break;

        case "getClientFee":
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `clientdetails` WHERE `clientnumber`=?")or die(mysqli_error($GLOBALS['con']));
            $stmt->bind_param('i',$_REQUEST['clientnumber']);$stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $cd=mysqli_fetch_array($iv);
            $r=$cd['rate_per_hour']."|".$cd['vat_rate']."|".$cd['comission'];
            $response = array(
            'client_name' => $cd['clientname'],
            'rate_per_hour' => $cd['rate_per_hour'],
            'vat_rate'  => getCompanyDetails('vatrate'),
            'commission'  => $cd['commission'],
            'comType'  => $cd['comType'],
            'contract_number'  => $cd['contract_number']
            );

            echo json_encode($response);
        break;

       case "sendStatement":
            $mode=$_REQUEST['mode'];
            if($mode=="client"){
                echo $_REQUEST['number'];
                $table="clientdetails";
                $number="clientnumber";
                $name="clientname";
                 $stmt2 = $GLOBALS['con']->prepare("SELECT `".$name."`, `clientnumber`,`billing_name`, `billing_email` FROM `".$table."` WHERE `".$number."`='".$_REQUEST['number']."'")or die(mysqli_error($GLOBALS['con']));
                $stmt2->execute();$iv2=$stmt2->get_result();$stmt2->close();
                $ad=mysqli_fetch_array($iv2);
                echo $ad['clientnumber']."|".clean_and($ad[$mode.'_name'])."|".clean_and($ad['billing_name'])."|".$ad['billing_email']."|".$filename;
            }
                
            else{
                $table=$_REQUEST['mode']."s"; 
                $number=$mode."_number";  
                $name=$mode."_name";
                 $stmt2 = $GLOBALS['con']->prepare("SELECT `".$name."`, `".$mode."_number`,`billing_name`, `billing_email` FROM `".$table."` WHERE `".$number."`='".$_REQUEST['number']."'")or die(mysqli_error($GLOBALS['con']));
                $stmt2->execute();$iv2=$stmt2->get_result();$stmt2->close();
                $ad=mysqli_fetch_array($iv2);
                echo $ad[$mode.'_number']."|".clean_and($ad[$mode.'_name'])."|".clean_and($ad['billing_name'])."|".$ad['billing_email']."|".$filename;
            }
            
           
        break;
   
        
        case "setRenewalSent":
            $stmt = mysqli_query($GLOBALS['con'],"SELECT * FROM `renewals` WHERE `renewalnumber`='".$_REQUEST['inv']."'")or die("2".mysqli_error($GLOBALS['con']));
           $id=mysqli_fetch_array($stmt);
            $stmt = $GLOBALS['con']->prepare("UPDATE `renewals` SET `status`='1',`date`='".strtotime(date('d-m-Y'))."' WHERE `id`='".$id['id']."'")or die("setRenewalSent:".mysqli_error($GLOBALS['con']));
            $stmt->execute();$stmt->close();           
        break;

       
        
        case "sendInvoice":
    
            $recall=mysqli_query($GLOBALS['con'],"SELECT * FROM `invoices_temp` WHERE `invNo`='".$_REQUEST['id']."'");
            $recallData=mysqli_fetch_array($recall);
            $curDate=date('d-m-Y');
            $nextDate=date('d-m-Y', strtotime('+30 days', strtotime(date('d-m-Y'))));
      
            $stmt = $GLOBALS['con']->prepare("INSERT INTO `invoices` VALUES(
                NULL,
                '".$recallData['client']."',
                '".$recallData['agent']."',
                '".$recallData['broker']."',
                '".$recallData['address']."',
                '".$recallData['items']."',
                '".$recallData['totals']."',
                '0.2',
                '".strtotime($curDate)."',
                '".strtotime($nextDate)."',
                '".$recallData['date_paid']."',
                '".$recallData['sent']."',
                '1',
                '".$recallData['payment_type']."',
                '".$recallData['chequeNo']."',
                '".$recallData['commission']."',
                '".$recallData['commission_paid']."',
                '".$recallData['filter']."',
                '".$recallData['target']."'
                )")or die("1".mysqli_error($GLOBALS['con']));
            $stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $invoiceId=mysqli_insert_id($GLOBALS['con']);
            $remove=mysqli_query($GLOBALS['con'],"DELETE FROM `invoices_temp` WHERE `invNo`='".$_REQUEST['id']."'");
           
            $address = unserialize($recallData['address']);
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `clientdetails` WHERE `clientnumber`=?")or die(mysqli_error($GLOBALS['con']));
            $stmt->bind_param('i',$recallData['client']);$stmt->execute();$iv=$stmt->get_result();$stmt->close();
            $cd=mysqli_fetch_array($iv);
            
            if($recallData['target']=="client"){                
                $clientName=str_replace('&','',$cd['clientname']);
                $billingName=str_replace('&','',$cd['billing_name']);
                $emailString=$recallData['client'].'|'.$billingName.'|'.$cd['billing_email'].'|'.$clientName.' Invoice |Your invoice number '.$invno.'|invoices|'.$invoiceId.'_invoice.pdf||'.$invoiceId.'|renewalInvoice|';
            }

            if($recallData['target']=="broker"){
                $clientName=str_replace('&','',$cd['clientname']);
                $billingName=str_replace('&','',$ad['billing_name']);
                $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `brokers` WHERE `broker_number`=?")or die(mysqli_error($GLOBALS['con']));
                $stmt2->bind_param('i',$cd['broker_number']);$stmt2->execute();$avy=$stmt2->get_result();$stmt2->close();
                $ad=mysqli_fetch_array($avy);
                $emailString=$recallData['client'].'|'.$billingName.'|'.$ad['billing_email'].'|'.$clientName.' Invoice |Your invoice number '.$invno.'|invoices|'.$invoiceId.'_broker.zip|'.$ad['email'].'|'.$invoiceId.'|renewalInvoice|';
            }

            if($recallData['target']=="agent"){
                $clientName=str_replace('&','',$cd['clientname']);
                $billingName=str_replace('&','',$ad['billing_name']);
                $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `agents` WHERE `agent_number`=?")or die(mysqli_error($GLOBALS['con']));
                $stmt2->bind_param('i',$cd['agent_number']);$stmt2->execute();$avs=$stmt2->get_result();$stmt2->close();
                $ad=mysqli_fetch_array($avs);
                $emailString=$recallData['client'].'|'.$billingName.'|'.$ad['billing_email'].'|'.$clientName.' Invoice |Your invoice number '.$invno.'|invoices|'.$invoiceId.'_agent.pdf|'.$ad['email'].'|'.$invoiceId.'|renewalInvoice|';
            }

            $dataArray=array(
                "client"=>$recallData['client'],
                "invoice"=>$invoiceId,
                "renewal"=>$d['renewalnumber'],
                "date"=>$recallData['date'],
                "target"=>$recallData['target'],
                "email"=>$emailString
            );

            $return=json_encode($dataArray);
            echo $return;
        break;


    }



function getExpenses(){
    $stmt = $GLOBALS['con']->prepare("SELECT * FROM `dd_expenses` ORDER BY `desc`")or die(mysqli_error($GLOBALS['con']));
    $stmt->execute();$iv=$stmt->get_result();$stmt->close();
    $ret='';
    $ret.='<option value="">Expense Type</option>';
    while($pd=mysqli_fetch_array($iv)){
    $ret.='<option value="'.$pd['desc'].'">'.$pd['desc'].'</option>';
    }
    echo $ret;
}

function genInvoiceCode(){
    $stmt = $GLOBALS['con']->prepare("SELECT `invNo` FROM `invoices` ORDER BY abs(invNo) DESC LIMIT 1")or die(mysqli_error($GLOBALS['con']));
    $stmt->execute();$iv=$stmt->get_result();$stmt->close();	
    $id=mysqli_fetch_array($iv);
    $number=$id['invNo']+1;
    return(sprintf("%06d", $number));
}

function getClientList(){
    $stmt = $GLOBALS['con']->prepare("SELECT `clientnumber`,`clientname` FROM `clientdetails` WHERE `statuslive`='1' ORDER BY `clientname` ASC")or die(mysqli_error($GLOBALS['con']));
    $stmt->execute();$iv=$stmt->get_result();$stmt->close();	
    $ret='';
    while($id=mysqli_fetch_array($iv)){
    $ret.='<option value="'.$id['clientnumber'].'">'.$id['clientname'].'</option>';
    }
    return $ret;
}

function getClientListFinance(){
    $stmt = $GLOBALS['con']->prepare("SELECT `clientnumber`,`clientname`,`postcode` FROM `clientdetails` WHERE `statuslive`='1' ORDER BY `clientname` ASC")or die(mysqli_error($GLOBALS['con']));
    $stmt->execute();$iv=$stmt->get_result();$stmt->close();
    $ret='';	
    while($id=mysqli_fetch_array($iv)){
    $ret.='<option value="'.$id['clientnumber'].'">'.$id['clientname'].' ['.$id['postcode'].']</option>';
    }
    return $ret;
}

function listClient($c, $n, $t){
    switch($t){
    default:break;

    case "select":
        $stmt = $GLOBALS['con']->prepare("SELECT `clientnumber`,`clientname` FROM `clientdetails` WHERE `statuslive`='1'  ORDER BY `clientname` ASC")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$cc=$stmt->get_result();$stmt->close();	

        $ret='<select class="form-control form-control-sm autocomplete" id="'.$n.'" name="'.$n.'" style="width:100%" data-placeholder="Select Client">'; 
        $ret.='<option value="">Select Client</option>';
        $ret.='<option value="0">All</option>';
        while($pd=mysqli_fetch_array($cc)){
            if($c==$pd['clientnumber']){$s="SELECTED";}else{$s="";}
                $ret.='<option value="'.$pd['clientnumber'].'" '.$s.'>'.$pd['clientname'].'</option>';//.PHP_EOL;
            }
            $ret.='</select>';
            return($ret);
            break;
        }
}

function getSiteName($clientnumber,$locnumber){
    $stmt = $GLOBALS['con']->prepare("SELECT `locname` FROM `locations` WHERE `clientnumber`=? AND `locnumber`=?")or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('ii',$clientnumber,$locnumber);$stmt->execute();$rc=$stmt->get_result();$stmt->close();	
    $cd=mysqli_fetch_array($rc);	
    return $cd['locname'];
}

function getItemName($itemnumber){
    $stmt = $GLOBALS['con']->prepare("SELECT `desc` FROM `itemdetails` WHERE `itemnumber`=?")or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i',$itemnumber);$stmt->execute();$rc=$stmt->get_result();$stmt->close();	
    $cd=mysqli_fetch_array($rc);	
    return $cd['desc'];
}

function listAgent($c, $n, $t){
    switch($t){
    default:break;

    case "select":
        $stmt = $GLOBALS['con']->prepare("SELECT `agent_number`,`agent_name` FROM `agents` WHERE `statuslive`='1' ORDER BY `agent_name` ASC")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$cc=$stmt->get_result();$stmt->close();	
        while($pd=mysqli_fetch_array($cc)){$ca[]=$pd;}

        $ret='<select class="form-control form-control-sm autocomplete" id="'.$n.'" name="'.$n.'" data-placeholder="Select Agent">'; 
        $ret.='<option value="">Select Agent</option>';//.PHP_EOL;	
        foreach($ca as $px){
            if($c==$px['agent_number']){$s="SELECTED";}else{$s="";}
            $ret.='<option value="'.$px['agent_number'].'" '.$s.'>'.$px['agent_name'].'</option>';//.PHP_EOL;
        }
        $ret.='</select>';
        return($ret);
    break;

    case "name":
        $stmt = $GLOBALS['con']->prepare("SELECT `agent_number`,`agent_name` FROM `agents` WHERE `agent_number`='".$c."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$cc=$stmt->get_result();$stmt->close();	 $pd=mysqli_fetch_array($cc);

        return($pd['agent_name']);
    break;
    }
}

function listBrokers($c, $n, $t){
    switch($t){
    default:break;
    
    case "select":
        $stmt = $GLOBALS['con']->prepare("SELECT `broker_number`,`broker_name` FROM `brokers` WHERE `statuslive`='1' ORDER BY `broker_name` ASC")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$cc=$stmt->get_result();$stmt->close();	
        while($pd=mysqli_fetch_array($cc)){$ca[]=$pd;}
    
        $ret='<select class="form-control form-control-sm autocomplete" id="'.$n.'" name="'.$n.'" data-placeholder="Select Broker">'; 
        $ret.='<option value="">Select Broker</option>';//.PHP_EOL;	
        foreach($ca as $px){
            if($c==$px['agent_number']){$s="SELECTED";}else{$s="";}
            $ret.='<option value="'.$px['broker_number'].'" '.$s.'>'.$px['broker_name'].'</option>';//.PHP_EOL;
        }
        $ret.='</select>';
        return($ret);
    break;
    
    case "name":
        $stmt = $GLOBALS['con']->prepare("SELECT `agent_number`,`agent_name` FROM `agents` WHERE `agent_number`='".$c."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$cc=$stmt->get_result();$stmt->close();	 $pd=mysqli_fetch_array($cc);
    
        return($pd['agent_name']);
    break;
    }
}

function getAge($birthday){
    $b=date('d.m.Y',$birthday);
    $bday = new DateTime($b);//dd.mm.yyyy
    $today = new DateTime('00:00:00'); // Current date
    $diff = $today->diff($bday);
    $m=$diff->m;
    $d=$diff->d;
    if($m>1){$mn="s";}
    if($d>1){$dn="s";}
    if($m==0 && $d==0){
        return "!!! Birthday Today !!!"; 
    }
    else{
        return sprintf('( %d years, %d month'.$mn.' and %d day'.$dn.' old )', $diff->y, $diff->m, $diff->d);
    }
}





function checkStatus($clientnumber){
$stmt = $GLOBALS['con']->prepare("SELECT `term_years`,`incepton_date` FROM `clientdetails` WHERE `clientnumber`='".$clientnumber."'")or die(mysqli_error($GLOBALS['con']));
$stmt->execute();$cc=$stmt->get_result();$stmt->close(); $pd=mysqli_fetch_array($cc);

if(empty($pd['incepton_date'])){return '<div class="alert alert-danger text-center">no renewal date stored</div>';}
else{
$time= countdown($pd['incepton_date']);
return $time;
}
}

function getSites($client,$curloc){
$stmt = $GLOBALS['con']->prepare("SELECT `locnumber`,`locname` FROM `locations` WHERE `clientnumber`=?")or die(mysqli_error($GLOBALS['con']));
$stmt->bind_param('i',$client);$stmt->execute();$cc=$stmt->get_result();$stmt->close();
$ret='<select class="form-control form-control-sm autocomplete" id="site" name="site">';
    $ret.='<option value="NULL">All</option>';//.PHP_EOL;
    $return_arr=array();
    while($pd=mysqli_fetch_array($cc)){
    if($curloc==$pd['locnumber']){$s="SELECTED";}else{$s="";}
    $ret.='<option value="'.$pd['locnumber'].'" '.$s.'>'.$pd['locname'].'</option>';//.PHP_EOL;
    }
    $ret.='</select>';
return($ret);
}

function listSurveyor($c,$n){
$stmt = $GLOBALS['con']->prepare("SELECT `esname`,`forename`,`surname` FROM `surveyor` WHERE `statuslive`='1' ORDER BY `forename` ASC")or die(mysqli_error($GLOBALS['con']));
$stmt->execute();$cc=$stmt->get_result();$stmt->close();
while($pd=mysqli_fetch_array($cc)){$ca[]=$pd;}
$ret='<select class="form-control form-control-sm autocomplete" id="'.$n.'" name="'.$n.'" data-placeholder="Select Surveyor">';
    $ret.='<option value="">Select Surveyor</option>';//.PHP_EOL;
    foreach($ca as $px){
    $name=$px['forename'].' '.$px['surname'];
    if($c==$name){$s="SELECTED";}else{$s="";}
    $ret.='<option value="'.$name.'" '.$s.'>'.$name.'</option>';//.PHP_EOL;
    }
    $ret.='</select>';
return($ret);
}

function listVehicles($n){
$stmt = $GLOBALS['con']->prepare("SELECT`reg`,`desc` FROM `vehicles` ORDER BY `reg` ASC")or die(mysqli_error($GLOBALS['con']));
$stmt->execute();$cc=$stmt->get_result();$stmt->close();
$ret='';
while($pd=mysqli_fetch_array($cc)){$ca[]=$pd;}
$ret.='<option value="">Select Vehicle</option>';//.PHP_EOL;
foreach($ca as $px){
$ret.='<option value="'.$px['reg'].'">'.$px['reg'].' - '.$px['desc'].'</option>';//.PHP_EOL;
}
return($ret);
}

function listCodes($n,$t){
$stmt = $GLOBALS['con']->prepare("SELECT * FROM `dd_expenses` ORDER BY `desc`")or die(mysqli_error($GLOBALS['con']));
$stmt->execute();$iv=$stmt->get_result();$stmt->close();
$ret='';
$ret.='<option value="">Select Code</option>';
while($pd=mysqli_fetch_array($iv)){
$ret.='<option value="'.$pd[$t].'">'.$pd[$t].'</option>';
}
return($ret);
}

function listPayee($t){
$stmt = $GLOBALS['con']->prepare("SELECT * FROM `dd_payee` WHERE `type`='".$t."' ORDER BY `name`")or die(mysqli_error($GLOBALS['con']));
$stmt->execute();$iv=$stmt->get_result();$stmt->close();
$ret='';
$ret.='<option value="">Select Payee</option>';
while($pd=mysqli_fetch_array($iv)){
$ret.='<option value="'.$pd['name'].'">'.$pd['name'].'</option>';
}
return($ret);
}

function isContract(){
return "yes";
}

function getClientDetails($c){
$stmt = $GLOBALS['con']->prepare("SELECT `clientname`, `pri_contact_name`, `email` FROM `clientdetails` WHERE `clientnumber`='".$c."'")or die(mysqli_error($GLOBALS['con']));
$stmt->execute();$iv=$stmt->get_result();$stmt->close();
$id=mysqli_fetch_array($iv);
return $id['clientname'];
}
?>