<?php //renewal.php

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

include('../master/form_functions.php');   
        
    function getclientDetailsInvoice($type,$client){
        $stmt = $GLOBALS['con']->prepare("SELECT * FROM `clientdetails` WHERE `clientnumber`='".$client."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$s=$stmt->get_result();$stmt->close();
        $xd=mysqli_fetch_array($s);
        $xd['clientname']= htmlspecialchars($xd['clientname'], ENT_QUOTES, 'UTF-8');
        if($type=="name"){ return htmlspecialchars($xd['clientname'], ENT_QUOTES, 'UTF-8');}
        if($type=="rdate"){return date("d-m-Y",$xd['renewal_date']);}
        if($type=="email"){return $xd['pri_contact_email'];}
        if($type=="address"){return '<tr><td>'.htmlspecialchars($xd['clientname'], ENT_QUOTES, 'UTF-8').'</td></tr><tr><td>'.$xd['address1'].'</td></tr><tr><td>'.$xd['address2'].'</td></tr><tr><td>'.$xd['town'].'</td></tr><tr><td>'.$xd['postcode'].'</td></tr>';}
        if($type=="contact"){return '<p>'.$xd['tel'].'<br>'.$xd['mob'].'<br>'.$xd['main_email'].'<br>'.$xd['web'].'</p>';}
    }
        
    function getAgentDetails($type,$agent){
        $stmt = $GLOBALS['con']->prepare("SELECT * FROM `agents` WHERE `agent_number`='".$agent."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$s=$stmt->get_result();$stmt->close();
        $xd=mysqli_fetch_array($s);
        $xd['agent_name']=str_replace('&','and', $xd['agent_name']);
        if($type=="bankName"){return $xd['bankName'];}
        if($type=="accountNo"){return $xd['accountNo'];}
        if($type=="sortCode"){return $xd['sortCode'];}
        if($type=="name"){return $xd['company'];}
        if($type=="vatno"){return $xd['vatno'];}
        if($type=="web"){return $xd['web'];}
        if($type=="name_short"){return $xd['company_short'];}
        if($type=="email"){return $xd['pri_contact_email'];}
        if($type=="address"){return '<tr><td>'.$xd['agent_name'].'</td></tr><tr><td>'.$xd['address1'].'</td></tr><tr><td>'.$xd['address2'].'</td></tr><tr><td>'.$xd['town'].'</td></tr><tr><td>'.$xd['post'].'</td></tr>';}
        if($type=="contact"){return '<p>'.$xd['tel'].'<br>'.$xd['mob'].'<br>'.$xd['main_email'].'<br>'.$xd['web'].'</p>';}
    }

    function getBrokerDetails($type,$broker){
        $stmt = $GLOBALS['con']->prepare("SELECT * FROM `brokers` WHERE `broker_number`='".$broker."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$s=$stmt->get_result();$stmt->close();
        $xd=mysqli_fetch_array($s);
        $xd['broker_name']=str_replace('&','and', $xd['broker_name']);
        if($type=="bankName"){return $xd['bankName'];}
        if($type=="accountNo"){return $xd['accountNo'];}
        if($type=="sortCode"){return $xd['sortCode'];}
        if($type=="name"){return $xd['broker_name'];}
        if($type=="vatno"){return $xd['vatno'];}
        if($type=="web"){return $xd['web'];}
        if($type=="name_short"){return $xd['company_short'];}
        if($type=="email"){return $xd['pri_contact_email'];}
        if($type=="address"){return '<tr><td>'.$xd['broker_name'].'</td></tr><tr><td>'.$xd['address1'].'</td></tr><tr><td>'.$xd['address2'].'</td></tr><tr><td>'.$xd['town'].'</td></tr><tr><td>'.$xd['post'].'</td></tr>';}
        if($type=="contact"){return '<p>'.$xd['tel'].'<br>'.$xd['mob'].'<br>'.$xd['main_email'].'<br>'.$xd['web'].'</p>';}
    }

    function invType($type){ 
        if($type=="client"){return('<span style="color:#2a3f54">INVOICE</span>');}
        if($type=="agent"){return('<span style="color:#2a3f54">INVOICE</span>');}
        if($type=="remit"){return('<span style="color:#257609">REMITTANCE ADVICE</span>');}
        if($type=="remind"){return('<span style="color:#bd0404">REMINDER</span>');}
    }

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

    $address = unserialize($cd['address']);

    $totals=explode('|',$cd['totals']);

    $sub = (float)str_replace(',', '', $totals[0]);
    $vat = (float)str_replace(',', '', $totals[1]);
    $com = (float)str_replace(',', '', $cd['commission']);

    $gtotal = $sub + $vat;
    $brokerStotal = $sub + $vat;
    $brokerGtotal = $sub + $vat - $com;


    $companyAddress='<table class="invtable table table-sm table-borderless" style="text-align:right"><tbody>';
    $companyAddress.=getCompanyDetails('address');
    $companyAddress.='</tbody></table>';

    $clientAddress='<table class="invtable table table-sm table-borderless"><thead><tr><th style="background-color:#2a3f54!important; color:#ffffff!important">In respect of:</th></tr></thead><tbody>';
    $clientAddress.=getclientDetailsInvoice('address',$cd['client']);
    $clientAddress.='</tbody></table>';

    $agentAddress='<table class="invtable table table-sm table-borderless"><thead><tr><th style="background-color:#2a3f54!important; color:#ffffff!important">To</th></tr></thead><tbody>';
    $agentAddress.=getAgentDetails('address',$cd['agent']);
    $agentAddress.='</tbody></table>';

    $brokerAddress='<table class="invtable table table-sm table-borderless"><thead><tr><th style="background-color:#2a3f54!important; color:#ffffff!important">To</th></tr></thead><tbody>';
    $brokerAddress.=getBrokerDetails('address',$cd['broker']);
    $brokerAddress.='</tbody></table>';

    $address['billing_name']=str_replace('AMPERSAND','&',$address['billing_name']);

    $deliveryAddress = '
    <table class="invtable table table-sm table-borderless" style="width:100%;">
    <thead>
        <tr><th style="background-color:#2a3f54!important; color:#ffffff!important;">To</th></tr>
    </thead>
    <tbody>';

    // Line 1: Billing name
    if (!empty($address['billing_name']) && $address['billing_name'] !== 'null') {
        $deliveryAddress .= '<tr><td>' . $address['billing_name'] . '</td></tr>';
    }

    // Line 2: Comma-separated address line
    $addrParts = [];
    if (!empty($address['billing1']) && $address['billing1'] !== 'null') $addrParts[] = $address['billing1'];
    if (!empty($address['billing2']) && $address['billing2'] !== 'null') $addrParts[] = $address['billing2'];
    if (!empty($address['billing3']) && $address['billing3'] !== 'null') $addrParts[] = $address['billing3'];
    if (!empty($address['billing_town']) && $address['billing_town'] !== 'null') $addrParts[] = $address['billing_town'];
    if (!empty($address['billing_county']) && $address['billing_county'] !== 'null') $addrParts[] = $address['billing_county'];
    if (!empty($address['billing_postcode']) && $address['billing_postcode'] !== 'null') $addrParts[] = $address['billing_postcode'];

    if (!empty($addrParts)) {
        $deliveryAddress .= '<tr><td>' . implode(', ', $addrParts) . '</td></tr>';
    }

    $deliveryAddress .= '</tbody></table>';


    $items = unserialize($cd['items']);
    foreach($items as $item){
        $rate=str_replace(',','',$item['rate']);
        $rate = floatval($rate);
        $quantity = floatval($item['quantity']);
        $net = $rate * $quantity;
        $itemRow.='<tr><td class="p-1 mt-1">'.$item['service'].'</td><td class="p-1 mt-1">'.$item['notes'].'</td></tr>';
    }
    $clientFooter=file_get_contents('../../../'.TENANT_URL.'/engines/renewal_structure/footer_client.php');
    $agentFooter=file_get_contents('../../../'.TENANT_URL.'/engines/renewal_structure/agentFooter.php');
    $brokerFooter=file_get_contents('../../../'.TENANT_URL.'/engines/renewal_structure/brokerFooter.php');
    $footer_basic=file_get_contents('../../../'.TENANT_URL.'/engines/renewal_structure/footer_basic.php');

    $clientFooter=str_replace('[CNAME]',getCompanyDetails('name'),$clientFooter);
    $clientFooter=str_replace('[WEB]',getCompanyDetails('web'),$clientFooter);
    $clientFooter=str_replace('[REPNO]',$_REQUEST['invNo'].'/'.strtotime('now'),$clientFooter);

    $agentFooter=str_replace('[QCODE]','<img src=""../../../'.TENANT_URL.'/invoices/'.$_REQUEST['invNo'].'.png" width="100px" />',$agentFooter);
    $agentFooter=str_replace('[CNAME]',getCompanyDetails('name'),$agentFooter);
    $agentFooter=str_replace('[CBANK]',getCompanyDetails('bankName'),$agentFooter);
    $agentFooter=str_replace('[CACNO]',getCompanyDetails('accountNo'),$agentFooter);
    $agentFooter=str_replace('[CSCODE]',getCompanyDetails('sortCode'),$agentFooter);
    $agentFooter=str_replace('[WEB]',getCompanyDetails('web'),$agentFooter);
    $agentFooter=str_replace('[REPNO]',$_REQUEST['invNo'].'/'.strtotime('now'),$agentFooter);

    $brokerFooter=str_replace('[QCODE]','<img src="../../../'.TENANT_URL.'/invoices/'.$_REQUEST['invNo'].'.png" width="100px" />',$brokerFooter);
    $brokerFooter=str_replace('[CNAME]',getCompanyDetails('name'),$brokerFooter);
    $brokerFooter=str_replace('[CBANK]',getCompanyDetails('bankName'),$brokerFooter);
    $brokerFooter=str_replace('[CACNO]',getCompanyDetails('accountNo'),$brokerFooter);
    $brokerFooter=str_replace('[CSCODE]',getCompanyDetails('sortCode'),$brokerFooter);
    $brokerFooter=str_replace('[WEB]',getCompanyDetails('web'),$brokerFooter);
    $brokerFooter=str_replace('[REPNO]',$_REQUEST['invNo'].'/'.strtotime('now'),$brokerFooter);

    $footer_basic=str_replace('[REPNO]',$_REQUEST['invNo'].'/'.strtotime('now'),$footer_basic);

 
    
    switch($_REQUEST['rtype']){
        case "client":  $header='
        <htmlpagefooter name="clientFooter" style="display: none;">'.$clientFooter.'</htmlpagefooter>';
        break;
        case "broker":  $header='
        <htmlpagefooter name="brokerFooter" style="display: none;">'.$brokerFooter.'</htmlpagefooter>';
        break;
        case "agent":  $header='
        <htmlpagefooter name="agentFooter" style="display: none;">'.$agentFooter.'</htmlpagefooter>';
        break;
        default: break;
    }
   $header.='<htmlpagefooter name="otherpages" style="display: none;">'.$footer_basic.'</htmlpagefooter>';
    $header.=file_get_contents('../../../'.TENANT_URL.'/engines/renewal_structure/header_view.php');

    switch($_REQUEST['rtype']){
        case "client":
            $client_body.='<link href="../../packages/bootstrap/boostrap.min.css" rel="stylesheet">';
            $client_body.=file_get_contents('../../../'.TENANT_URL.'/engines/renewal_structure/style.php');
            
          $client_body = '
<style>
    body { font-family: Verdana, sans-serif; font-size: 10pt; color: #2a3f54; }
    .header-title { font-size: 18pt; text-align: center; color: #2a3f54; font-weight: bold; margin: 10px 0; }
    .address-block, .company-block { width: 45%; display: inline-block; vertical-align: top; font-size: 10pt; }
    .meta-table { width: 100%; margin-top: 15px; }
    .meta-table td { padding: 5px 0; font-size: 10pt; }
    .meta-table th { text-align: left; padding-right: 10px; font-weight: bold; color: #2a3f54; }
    .renewal-table { width: 100%; margin-top: 15px; border-collapse: collapse; }
    .renewal-table th {
        background-color: #2a3f54;
        color: #fff;
        padding: 6px;
        font-size: 10pt;
        text-align: left;
    }
    .renewal-table td {
        padding: 6px;
        border-top: 1px solid #ccc;
    }
    .totals td { font-weight: bold; }
    .totals td:last-child { text-align: right; }
    hr { border: 0; border-top: 1px solid #ccc; margin: 20px 0; }
</style>

<div class="header-title">RENEWAL NOTICE</div>

<div>
    <div class="address-block">
        
        '.$deliveryAddress.'
</div>
<div class="company-block" style="text-align:right;">
    <strong>'.nl2br($companyAddress) .'</strong>
</div>
</div>

<hr>

<table class="meta-table">
    <tr>
        <th>Renewal Due:</th>
        <td>' . getclientDetailsInvoice('rdate', $cd['client']) . '</td>
        <th style="padding-left: 40px;"></th>
        <td></td>
    </tr>
   
</table>

<hr>

<table class="renewal-table">
    <thead>
        <tr>
            <th>SERVICE</th>
            <th>REFERENCE</th>
        </tr>
    </thead>
    <tbody>
        ' . $itemRow . '
    </tbody>
    <tfoot>
        <tr class="totals">
            <td>Subtotal</td>
            <td>&pound;' . number_format((float)$totals[0], 2) . '</td>
        </tr>
        <tr class="totals">
            <td>VAT</td>
            <td>&pound;' . number_format((float)$totals[1], 2) . '</td>
        </tr>
        <tr>
            <td colspan="2">
                <hr>
            </td>
        </tr>
        <tr class="totals">
            <td>Total Renewal Fee</td>
            <td style="background-color:#2a3f54;color:white;">&pound;' . number_format((float)$gtotal, 2) . '</td>
        </tr>
    </tfoot>
</table>

<sethtmlpagefooter name="clientFooter" value="on" />
';

$renewVer=$client_body;
break;
case "broker":
$broker_body.='
<link href="../../packages/bootstrap/bootstrap.gen.css" rel="stylesheet">';
$broker_body.=file_get_contents('../../../'.TENANT_URL.'/engines/invoice_structure/style.php');
$broker_body.='<h2 style="text-align:right; color:#2a3f54">RENEWAL NOTICE</h2>';
$broker_body.='
<div style="width:200px; float:left">'.$brokerAddress.'</div>
<div style="width:200px; float:left; margin-left: 20px">'.$clientAddress.'</div>
<div style="width:200px; float:right">'.$companyAddress.'</div>

<div style="clear:both"></div>

<hr style="color:#2a3f54">
<div style="width:30%; float:left">
    <table class="table table-sm">
        <tr>
            <th></th>
        </tr>
    </table>
</div>
<div style="width:40%; float:right">
    <table class="table" style="text-align:right; font-size:12px">
        <tr>
            <th>VAT REGISTRATION NUMBER</th>
            <td>'.getCompanyDetails('vatno').'</td>
        </tr>
    </table>
</div>
<div style="clear:both"></div>
<div style="width:30%; float:left">
    <table class="table table-sm">
        <tr>
            <th>Renewal Due:</th>
            <td>'.getclientDetailsInvoice('rdate',$cd['client']).'</td>
        </tr>
    </table>
</div>
<div style="width:40%; float:right">
    <table class="table table-sm" style="text-align:right; font-size:12px">
        <tr>
            <th>TERMS</th>
            <td>Stricly 30 days</td>
        </tr>
    </table>
</div>

<div style="clear:both"></div>

<hr style="color:#2a3f54">
<table class="invtable table table-sm">
    <thead>
        <tr>
            <th style="background-color:#2a3f54!important; color:#ffffff!important">SERVICE</th>
            <th style="background-color:#2a3f54!important; color:#ffffff!important">REFERENCE</th>
        </tr>
    </thead>
    <tbody class="mt-1">
        '.$itemRow.'
        <tr>
            <td colspan="2">
                <hr style="color:#2a3f54">
            </td>
        </tr>
    </tbody>
    <tfoot>
        <tr>
            <td style="background-color:#eaeaec!important;">RENEWAL FEE (LESS VAT)</td>
            <td style="background-color:#eaeaec!important;" align="right">'.number_format((float)$totals[0],2).'</td>
        </tr>
        <tr>
            <td>VAT</td>
            <td align="right">'.number_format((float)$totals[1],2).'</td>
        </tr>
        <tr>
            <td colspan="2">
                <hr style="color:#2a3f54">
            </td>
        </tr>
        <tr>
            <td>SUBTOTAL</td>
            <td align="right">'.number_format((float)$brokerStotal,2).'</td>
        </tr>
        <tr>
            <td colspan="2">
                <hr style="color:#2a3f54">
            </td>
        </tr>
        <tr>
            <td style="background-color:#eaeaec!important;">Your commission on this amount is</td>
            <td style="background-color:#eaeaec!important;" align="right">'.number_format($com,2).'</td>
        </tr>
        <tr>
            <td colspan="2">
                <hr style="color:#2a3f54">
            </td>
        </tr>
        <tr>
            <td>TOTAL RENEWAL FEE <small>(Excluding Commission)</small></td>
            <td style="background-color:#2a3f54; color:#ffffff;" align="right">&pound;'.number_format((float)$brokerGtotal,2).'</td>
        </tr>
    </tfoot>
</table>
<br><br><br>

<sethtmlpagefooter name="brokerFooter" value="on" />
';
$renewVer=$broker_body;
break;
case "agent":
$agent_body.='
<link href="../../packages/bootstrap/bootstrap.gen.css" rel="stylesheet">';
$agent_body.=file_get_contents('../../../'.TENANT_URL.'/engines/invoice_structure/style.php');
$agent_body.='<h2 style="text-align:right; color:#2a3f54">RENEWAL NOTICE</h2>';
$agent_body.='
<div style="width:200px; float:left">'.$agentAddress.'</div>
<div style="width:200px; float:left; margin-left: 20px">'.$clientAddress.'</div>
<div style="width:200px; float:right">'.$companyAddress.'</div>
<div style="clear:both"></div>
<hr style="color:#2a3f54">
<div style="width:30%; float:left">
    <table class="invtable table table_sm">
        <tr>
            <th></th>
        </tr>
    </table>
</div>
<div style="width:40%; float:right">
    <table class="invtable table" style="text-align:right">
        <tr>
            <th>VAT REGISTRATION NUMBER</th>
            <td>'.getCompanyDetails('vatno').'</td>
        </tr>
    </table>
</div>
<div style="clear:both"></div>
<div style="width:30%; float:left">
    <table class="invtable table table_sm">
        <tr>
            <th>Renewal Due:</th>
            <td>'.getclientDetailsInvoice('rdate',$cd['client']).'</td>
        </tr>
    </table>
</div>
<div style="width:40%; float:right">
    <table class="invtable table table_sm" style="text-align:right">
        <tr>
            <th>TERMS</th>
            <td>Stricly 30 days</td>
        </tr>
    </table>
</div>
<div style="clear:both"></div>
<hr style="color:#2a3f54">
<table class="invtable table table_sm">
    <thead>
        <tr>
            <th style="background-color:#2a3f54!important; color:#ffffff!important">SERVICE</th>
            <th style="background-color:#2a3f54!important; color:#ffffff!important">REFERENCE</th>
        </tr>
    </thead>
    <tbody>
        '.$itemRow.'
        <tr>
            <td colspan="5">
                <hr style="color:#2a3f54">
            </td>
        </tr>
    </tbody>
    <tfoot>
        <tr>
            <td>SUBTOTAL</td>
            <td style="background-color:#eaeaec!important;" align="right">'.number_format((float)$totals[0],2).'</td>
        </tr>
        <tr>
            <td>VAT</td>
            <td align="right">'.number_format((float)$totals[1],2).'</td>
        </tr>
        <tr>
            <td colspan="2">
                <hr style="color:#2a3f54">
            </td>
        </tr>
        <tr>
            <td>TOTAL RENEWAL FEE</td>
            <td style="background-color:#2a3f54; color:#ffffff;" align="right">&pound;'.number_format((float)$gtotal,2).'</td>
        </tr>
    </tfoot>
</table>
<sethtmlpagefooter name="agentFooter" value="on" />
';

$renewVer=$agent_body;
break;

}

//// CONTENT REPLACEMENT

if(file_exists('../../../'.TENANT_URL.'/images/company/logo.jpg')){$header=str_replace('[LGO]','<img src="../../../'.TENANT_URL.'/images/company/logo.jpg" height="50px" />',$header);}

if(file_exists('../../../'.TENANT_URL.'/images/company/afl_1.jpg')){$header=str_replace('[AF1]','<td><img src="../../../'.TENANT_URL.'/images/company/afl_1.jpg" width="30px" /></td>',$header);}
if(file_exists('../../../'.TENANT_URL.'/images/company/afl_2.jpg')){$header=str_replace('[AF2]','<td><img src="../../../'.TENANT_URL.'/images/company/afl_2.jpg" width="30px" /></td>',$header);}
if(file_exists('../../../'.TENANT_URL.'/images/company/afl_3.jpg')){$header=str_replace('[AF3]','<td><img src="../../../'.TENANT_URL.'/images/company/afl_3.jpg" width="30px" /></td>',$header);}
if(file_exists('../../../'.TENANT_URL.'/images/company/afl_4.jpg')){$header=str_replace('[AF4]','<td><img src="../../../'.TENANT_URL.'/images/company/afl_4.jpg" width="30px" /></td>',$header);}
if(file_exists('../../../'.TENANT_URL.'/images/company/afl_5.jpg')){$header=str_replace('[AF5]','<td><img src="../../../'.TENANT_URL.'/images/company/afl_5.jpg" width="30px" /></td>',$header);}
if(file_exists('../../../'.TENANT_URL.'/images/company/afl_6.jpg')){$header=str_replace('[AF6]','<td><img src="../../../'.TENANT_URL.'/images/company/afl_6.jpg" width="30px" /></td>',$header);}

if($_REQUEST['schedule']==1){
    
function getLastRep($item){
        $sql=mysqli_query($GLOBALS['con'],"SELECT `result`,`reptype` FROM `reportitems` WHERE `itemnumber`='".$item."' ORDER BY abs(repitem) DESC LIMIT 1");
        $id=mysqli_fetch_array($sql);
        if($id['result']=="DEFECTS" ){$col=" warning-color-dark";$tag="DEFECTS";$resultTitle="DEFECTS"; $resultText=$id['bdefect'];}
        if($id['result']=="URGENT" ){$col=" danger-color-dark";$tag="URGENT";$resultTitle="URGENT"; $resultText=$id['adefect'];}
        if($id['result']=="PASS" ){$col=" success-color-dark";$tag="PASS"; $resultTitle="PASS"; $resultText="The item passed examination<br>";}
        if($id['result']=="OBS" ){$col=" special-color";$tag="OBS"; $resultTitle="OBS"; $resultText=$id['obs'];}
        
        if($id['result']=="" ){$col=" secondary-color";$tag="P2UD"; $resultTitle="P2UD"; $resultText="No report or report is prior to update<br>";}
        if($id['reptype']=="518" ){$col=" stylish-color";$tag="NIR"; $resultTitle="NIR"; $resultText=$id['pnareason'];}
        if($id['reptype']=="503" ){$col=" stylish-color";$tag="PNA"; $resultTitle="PNA"; $resultText=$id['pnareason'];}
    
        $ret='<td class="text-center p-0" ><span class="badge badge-pill d-block '.$col.'"  style="font-size:10px" >'.$tag.'</span></td>';
    return $ret;
    }

if(!empty($_REQUEST['location'])){$loc="AND a.locnumber='".$_REQUEST['location']."'"; $name="@ ".$_REQUEST['name'];}
$stmt = $GLOBALS['con']->prepare("SELECT 
a.itemnumber,
a.locnumber,
a.desc,
a.code,
a.currdate,
a.plantnumber,
a.serialnumber,
a.model,
a.dateofmanf,
a.manf,
a.duedate,
a.linked,
a.itemnotes,
a.statuslive,
b.locname,
b.locnotes,
b.postcode

FROM itemdetails a 
INNER JOIN locations b ON a.locnumber=b.locnumber 

WHERE a.clientnumber='".$_REQUEST['clientnumber']."' AND a.statuslive='1' $loc")or die(mysqli_error($GLOBALS['con']));
$stmt->execute();$ic=$stmt->get_result();$stmt->close();

while($id=mysqli_fetch_array($ic)){
if($id['duedate']<strtotime('now')){$stat="status_colour color danger-color-dark";$statTitle="Overdue";}
if($id['duedate']>strtotime('now') && $id['duedate']<strtotime('now +1 month')){$stat="status_colour color warning-color-dark";$statTitle="Pending";}
if($id['duedate']>strtotime('now +1 month')){$stat="status_colour color success-color-dark";$statTitle="Not_Due"; $statFTitle="Not Due";}

if($id['result']=="DEFECTS" ){$col=" warning-color-dark";$tag="DEFECTS";$resultTitle="DEFECTS<br>".$id['bdefect'];}
if($id['result']=="URGENT" ){$col=" danger-color-dark";$tag="URGENT";$resultTitle="URGENT<br>".$id['adefect'];}
if($id['result']=="PASS" ){$col=" success-color-dark";$tag="PASS"; $resultTitle="PASS";}
if($id['result']=="OBS" ){$col=" special-color";$tag="OBS"; $resultTitle="OBS<br>".$id['obs'];}
if($id['reptype']=="503" ){$col=" stylish-color";$tag="PNA"; $resultTitle="PNA<br>".$id['pnareason'];}
if($id['result']=="" ){$col="  secondary-color";$tag="P2UD"; $resultTitle="P2UD";}


$ret.='
<tr>
<td class="'.$stat.'" ><div class="'.$stat.'"  style="font-size:0px" >'.$statTitle.'</div></td>
<td>'.$id['itemnumber'].'</td>
<td>'.$id['desc'].'</td>
<td>'.$id['plantnumber'].'</td>
<td>'.$id['serialnumber'].'</td>
<td>'.$id['locname'].'</a></td>
<td>'.$id['postcode'].'</a></td>';

if(empty($id['duedate'])){$ret.='<td>No date stored</td>'; }
else{$ret.='<td>'.date('d-m-Y',$id['duedate']).'</td>';}
$ret.=getLastRep($id['itemnumber']);
$ret.='</tr>';
}

$schedule='
<sethtmlpagefooter name="brokerFooter" value="off" />
<sethtmlpagefooter name="otherpages" value="on" />
<style>
body{margin: 3mm 3mm 3mm 3mm; font-family:verdana;}
table>thead>td {background-color:#2a3f54!important; color:#fff!important}
table th{font-size:12px!important}
table td{font-size:10px!important}
h4{font-size: 14px}

strong{font-weight: bold}
.subtitle{color:#fff; background-color:#0C2183}
.vatBox{display:block!important}
.dataTables_length, .dataTables_filter, .dataTables_info, .dataTables_paginate, .dt-buttons .filterButtons {display:none!important}
.strap{text-align:center!important; color:#0C2183!important}

.headCell{background-color:rgb(42, 63, 84)!important}
.headCell th{color:rgb(255, 255, 255)!important; padding:3px; font-size:12px!important}


.bodyCell{border-bottom:1px solid #cccccc!important}
.bodyCell td{border-bottom:1px solid #cccccc!important; font-size:11px!important; padding:3px!important; }

.footCell{background-color:#e0e0e0!important}
.footCell th, .footCell td{padding:3px; padding:3px; font-size:12px!important}

.remove{positon:absolute; display:none!important}
.reportHeader{display: block!important;background-color:#2a3f54; color:#ffffff; padding:10px!important; }
th hr,tbody hr{margin:0px!important; padding:0px!important}
.rule{margin:0px!important; padding:0px!important}
#expenseTable td{font-size:16px!important}
.noprint{display:none!important}
.noExport{display:none!important}
#exportTitle{background-color:rgba(42,63,84,1.00); color:#fff; font-size: 18px; padding:10px}
.exclude{display:none!important}
.lead {font-weight: bold !important; font-size: 20px !important;}


#coverDetails thead {
    background-color: #2a3f54 !important;
    border-color: #ccc !important;
    color: #fff !important;
    font-weight: bold !important;
    font-size: 14px !important;
    box-shadow: inset 0 0 0 1000px #2a3f54;
    -webkit-print-color-adjust: exact !important;
  }
}

#coverDetails thead {
  background-color: #2a3f54 !important;
  border-color: #ccc !important;
  color: #fff !important;
  font-weight: bold !important;
  font-size: 14px !important;
  box-shadow: inset 0 0 0 1000px #2a3f54;
  -webkit-print-color-adjust: exact !important;
}
#coverDetails input {
  padding: 0 !important;
  padding-left: 10px !important;
}

#coverDetails .pr {
  max-width: 50% !important;
}
.badge {
    display: inline-block;
    padding: 0.25em 0.4em;
    font-size: 75%;
    font-weight: 700;
    line-height: 1;
    text-align: center;
    white-space: nowrap;
    vertical-align: baseline;
    border-radius: 0.25rem;
    transition: color .15s ease-in-out,background-color .15s ease-in-out,border-color .15s ease-in-out,box-shadow .15s ease-in-out;
}
.badge-pill{padding-right:.6em;padding-left:.6em;border-radius:10rem}
.success-color-dark {
    background-color: #007e33!important;
}
.danger-color-dark {
    background-color: #c00!important;
}
.warning-color-dark {
    background-color: #f80!important;
}
.special-color{background-color:#37474F!important}
.stylish-color{background-color:#4B515D!important}
.secondary-color{background-color:#9211E8!important}
.badge-info{background-color: #353b50!important;}
.float-end{float:right!important}

.table {
    width: 100%;
    margin-bottom: 1rem;
    color: #212529
}

.table td,.table th {
    padding: .75rem;
    vertical-align: top;
    border-top: 1px solid #dee2e6
}

.table thead th {
    vertical-align: bottom;
    border-bottom: 2px solid #dee2e6
}

.table tbody+tbody {
    border-top: 2px solid #dee2e6
}

.table-sm td,.table-sm th {
    padding: .3px
}

.table-bordered {
    border: 1px solid #dee2e6
}

.table-bordered td,.table-bordered th {
    border: 1px solid #dee2e6
}

.table-bordered thead td,.table-bordered thead th {
    border-bottom-width: 2px
}

.table-borderless tbody+tbody,.table-borderless td,.table-borderless th,.table-borderless thead th {
    border: 0
}

.table-striped tbody tr:nth-of-type(odd) {
    background-color: rgba(0,0,0,.05)
}

.table-hover tbody tr:hover {
    color: #212529;
    background-color: rgba(0,0,0,.075)
}

.table-primary,.table-primary>td,.table-primary>th {
    background-color: #b8daff
}

.table-primary tbody+tbody,.table-primary td,.table-primary th,.table-primary thead th {
    border-color: #7abaff
}

.table-hover .table-primary:hover {
    background-color: #9fcdff
}

.table-hover .table-primary:hover>td,.table-hover .table-primary:hover>th {
    background-color: #9fcdff
}

.table-secondary,.table-secondary>td,.table-secondary>th {
    background-color: #d6d8db
}

.table-secondary tbody+tbody,.table-secondary td,.table-secondary th,.table-secondary thead th {
    border-color: #b3b7bb
}

.table-hover .table-secondary:hover {
    background-color: #c8cbcf
}

.table-hover .table-secondary:hover>td,.table-hover .table-secondary:hover>th {
    background-color: #c8cbcf
}

.table-success,.table-success>td,.table-success>th {
    background-color: #c3e6cb
}

.table-success tbody+tbody,.table-success td,.table-success th,.table-success thead th {
    border-color: #8fd19e
}

.table-hover .table-success:hover {
    background-color: #b1dfbb
}

.table-hover .table-success:hover>td,.table-hover .table-success:hover>th {
    background-color: #b1dfbb
}

.table-info,.table-info>td,.table-info>th {
    background-color: #bee5eb
}

.table-info tbody+tbody,.table-info td,.table-info th,.table-info thead th {
    border-color: #86cfda
}

.table-hover .table-info:hover {
    background-color: #abdde5
}

.table-hover .table-info:hover>td,.table-hover .table-info:hover>th {
    background-color: #abdde5
}

.table-warning,.table-warning>td,.table-warning>th {
    background-color: #ffeeba
}

.table-warning tbody+tbody,.table-warning td,.table-warning th,.table-warning thead th {
    border-color: #ffdf7e
}

.table-hover .table-warning:hover {
    background-color: #ffe8a1
}

.table-hover .table-warning:hover>td,.table-hover .table-warning:hover>th {
    background-color: #ffe8a1
}

.table-danger,.table-danger>td,.table-danger>th {
    background-color: #f5c6cb
}

.table-danger tbody+tbody,.table-danger td,.table-danger th,.table-danger thead th {
    border-color: #ed969e
}

.table-hover .table-danger:hover {
    background-color: #f1b0b7
}

.table-hover .table-danger:hover>td,.table-hover .table-danger:hover>th {
    background-color: #f1b0b7
}

.table-light,.table-light>td,.table-light>th {
    background-color: #fdfdfe
}

.table-light tbody+tbody,.table-light td,.table-light th,.table-light thead th {
    border-color: #fbfcfc
}

.table-hover .table-light:hover {
    background-color: #ececf6
}

.table-hover .table-light:hover>td,.table-hover .table-light:hover>th {
    background-color: #ececf6
}

.table-dark,.table-dark>td,.table-dark>th {
    background-color: #c6c8ca
}

.table-dark tbody+tbody,.table-dark td,.table-dark th,.table-dark thead th {
    border-color: #95999c
}

.table-hover .table-dark:hover {
    background-color: #b9bbbe
}

.table-hover .table-dark:hover>td,.table-hover .table-dark:hover>th {
    background-color: #b9bbbe
}

.table-active,.table-active>td,.table-active>th {
    background-color: rgba(0,0,0,.075)
}

.table-hover .table-active:hover {
    background-color: rgba(0,0,0,.075)
}

.table-hover .table-active:hover>td,.table-hover .table-active:hover>th {
    background-color: rgba(0,0,0,.075)
}

.table .thead-dark th {
    color: #fff;
    background-color: #343a40;
    border-color: #454d55
}

.table .thead-light th {
    color: #495057;
    background-color: #e9ecef;
    border-color: #dee2e6
}

.table-dark {
    color: #fff;
    background-color: #343a40
}

.table-dark td,.table-dark th,.table-dark thead th {
    border-color: #454d55
}

.table-dark.table-bordered {
    border: 0
}

.table-dark.table-striped tbody tr:nth-of-type(odd) {
    background-color: rgba(255,255,255,.05)
}

.table-dark.table-hover tbody tr:hover {
    color: #fff;
    background-color: rgba(255,255,255,.075)
}

 .renewal-header {
            color: #2a3f54;
            text-align: right;
            font-size: 22px;
            font-weight: bold;
            margin-bottom: 10px;
        }
        .address-block {
            width: 48%;
            display: inline-block;
            vertical-align: top;
            font-size: 12px;
            line-height: 1.4;
        }
        .divider {
            border: none;
            border-top: 1px solid #2a3f54;
            margin: 20px 0;
        }
        .info-table {
            width: 100%;
            font-size: 12px;
            margin-bottom: 20px;
        }
        .info-table td {
            padding: 4px 0;
        }
        .summary-table {
            width: 100%;
            font-size: 13px;
            margin-top: 10px;
        }
        .summary-table th {
            background-color: #2a3f54;
            color: #ffffff;
            padding: 6px;
            text-align: left;
        }
        .summary-table td {
            padding: 6px;
        }
        .summary-table tfoot td {
            font-weight: bold;
        }
        .total-row td {
            background-color: #2a3f54;
            color: #ffffff;
            font-size: 14px;
        }
        

  /* Ensure no other elements are printed */

  .stylish-color {
    background-color: #78909c !important;
    color: #fff;
  }
  .warning-color-dark {
    background-color: #f80 !important;
    color: #fff;
  }
  .danger-color-dark {
    background-color: #c00 !important;
    color: #fff;
  }
  .success-color-dark {
    background-color: #007e33 !important;
    color: #fff;
  }
  .special-color {
    background-color: #37474f !important;
    color: #fff;
  }
  .secondary-color {
    background-color: #a6c !important;
    color: #fff;
  }
}

.badge {
  display: inline-block;
  padding: 0.25em 0.4em;
  font-size: 75%;
  font-weight: 700;
  line-height: 1;
  text-align: center;
  white-space: nowrap;
  vertical-align: baseline;
  border-radius: 0.25rem;
  color: #fff;
  background-color: #007bff; /* Default color (can vary) */
}

.badge-pill {
  border-radius: 10rem; /* A large border-radius to create a pill shape */
  padding-right: 0.6em;
  padding-left: 0.6em;
}
.d-block {
  display: block !important; /* Forces the element to take up the full width of its container */
}

.noprint {
  display: none !important;
}

/* Variant backgrounds */
.badge-success {
  background-color: #14a44d;
}

.badge-warning {
  background-color: #e4a11b;
}

.badge-danger {
  background-color: #dc4c64;
}

.badge-primary {
  background-color: #3b71ca;
}

.badge-secondary {
  background-color: #9fa6b2;
}
/* Background utilities */
.bg-success {
  /* rgba(var(--mdb-success-rgb),1) → rgba(20,164,77,1) */
  background-color: rgba(20, 164, 77, 1) !important;
}
.bg-warning {
  /* rgba(var(--mdb-warning-rgb),1) → rgba(228,161,27,1) */
  background-color: rgba(228, 161, 27, 1) !important;
}
.bg-danger {
  /* rgba(var(--mdb-danger-rgb),1) → rgba(220,76,100,1) */
  background-color: rgba(220, 76, 100, 1) !important;
}
.bg-dark {
  /* rgba(var(--mdb-dark-rgb),1) → rgba(51,45,45,1) */
  background-color: rgba(51, 45, 45, 1) !important;
}
.bg-secondary {
  /* rgba(var(--mdb-secondary-rgb),1) → rgba(159,166,178,1) */
  background-color: rgba(159, 166, 178, 1) !important;
}

/* Text-color utilities */
.text-light {
  /* rgba(var(--mdb-light-rgb),1) → rgba(251,251,251,1) */
  color: rgba(251, 251, 251, 1) !important;
}
.text-dark {
  /* rgba(var(--mdb-dark-rgb),1) → rgba(51,45,45,1) */
  color: rgba(51, 45, 45, 1) !important;
}
table {
  width: 100%;
  border-collapse: collapse;
  font-size: 10pt;
}

.table-sm td,
.table-sm th {
  padding: 0.3rem;
}

.table-striped tbody tr:nth-of-type(odd) {
  background-color: #f2f2f2;
}

.table-bordered,
.table-bordered td,
.table-bordered th {
  border: 1px solid #dee2e6;
}

.align-middle td,
.align-middle th {
  vertical-align: middle !important;
}

/* width */
.w-100 {
  width: 100% !important;
}

/* flex utilities */
.d-flex {
  display: flex !important;
}

.justify-content-start {
  justify-content: flex-start !important;
}

.justify-content-middle {
  justify-content: center !important; 
}

.justify-content-end {
  justify-content: flex-end !important;
}

/* spacing */
.px-3 {
  padding-left: 1rem !important;
  padding-right: 1rem !important;
}

.mt-3 {
  margin-top: 1rem !important;
}

</style>
<table class="table table-sm" width="100%">
<tbody>
<tr valign="top">
<td width="80%" valign="top"><h3>Schedule - '.getClientName($_REQUEST['clientnumber']).' '.$name.'</h3></td>
<td width="20%" align="right" valign="top" style="font-family: serif; font-size: 8pt; color: #000000; font-weight: bold;">'.getClientAddress($_REQUEST['clientnumber']).'</td>
</tr>
</tbody>
</table>

<table id="coverDetails" class="table table-sm table-striped" width="100%">
<thead class="table-dark text-dark">
<tr style="background-color:#2a3f54!important; color:#ffffff!important">
<th class="no-sort noExport text-center p-0" width="20px">&nbsp;</th>
<th style="color:#ffffff!important">Item ID</th>
<th style="color:#ffffff!important">Description</th>
<th style="color:#ffffff!important">Plant Number</th>
<th style="color:#ffffff!important">Serial Number</th>
<th style="color:#ffffff!important">Location</th>
<th style="color:#ffffff!important">Post Code</th>
<th style="color:#ffffff!important" class="date-uk">Due Date</th>
<th style="color:#ffffff!important" class="no-sort noExport">Last Report</th>
</tr>
</thead>
<tbody>
'.$ret.'
</tbody>
</table>';

            }

            if($_REQUEST['plantlist']==1){
            $plantlist='
                <sethtmlpagefooter name="brokerFooter" value="off" />
                <sethtmlpagefooter name="otherpages" value="on" />
                <h2>Plant list - '.getclientDetailsInvoice('name',$_REQUEST['clientnumber']).'</h2>
                <hr style="color:#2a3f54">';



                $locationCall=mysqli_query($GLOBALS['con'],"SELECT * FROM `locations` WHERE `clientnumber`='".$_REQUEST['clientnumber']."' AND `statuslive`='1'");
                while($ld=mysqli_fetch_array($locationCall)){
                $unitsTotal=0;
                $sdcTotal=0;
                $plantlist.='
                <small>'.$ld['locname'].' - '.$ld['locaddress1'].','.$ld['locaddress2'].','.$ld['locaddress3'].','.$ld['postcode'].'</small>
                <table id="tableData" class="table table-sm table-striped" width="100%">
                    <thead class="p-0">
                        <tr class="p-0" style="background-color:#2a3f54!important; color:#ffffff!important">
                            <th class="printhead py-0" style="color:#ffffff!important">Item</th>
                            <th class="printhead py-0" style="color:#ffffff!important">Quantity</th>
                            <th class="printhead py-0" style="color:#ffffff!important">Frequency</th>
                        </tr>
                    </thead>
                    <tbody>';
                        $sp=mysqli_query($GLOBALS['con'],"SELECT DISTINCT `desc` FROM `itemdetails` WHERE `clientnumber`='".$_REQUEST['clientnumber']."' AND `statuslive`='1' AND `locnumber`='".$ld['locnumber']."' ");
                        while($sd=mysqli_fetch_array($sp)){

                        $spc=mysqli_query($GLOBALS['con'],"SELECT `itemnumber`,`code` FROM `itemdetails` WHERE `clientnumber`='".$_REQUEST['clientnumber']."' AND `statuslive`='1' AND `desc`='".$sd['desc']."' AND
                        `locnumber`='".$ld['locnumber']."' ")or die(mysqli_error($GLOBALS['con']));
                        $sdc=mysqli_num_rows($spc);
                        $sdx=mysqli_fetch_array($spc);

                        $iuc=mysqli_query($GLOBALS['con'],"SELECT `units`,`frequency` FROM `dd_plant` WHERE `code`='".$sdx['code']."'")or die(mysqli_error($GLOBALS['con']));
                        $iuc=mysqli_fetch_array($iuc);

                        $units=$iuc['units']*$sdc;
                        $sdcTotal+=$sdc;
                        $unitsTotal+=$units;
                        $plantlist.='<tr>
                            <td><b>'.$sd['desc'].'</b></td>
                            <td>'.$sdc.'</td>
                            <td>'.$iuc['frequency'].'</td>
                        </tr>';
                        }
                        $plantlist.='
                    </tbody>

                    <tfoot class="bg-info text-white">
                        <tr style="background-color:#eaeaec!important;">
                            <th class="py-0"></th>
                            <td class="printhead py-0">'.$sdcTotal.'</td>
                            <td class="printhead py-0"></td>
                        </tr>
                    </tfoot>
                </table>
                ';
                };
                $plantlist.='
                <sethtmlpagefooter name="otherpages" value="on" />';
                

                }

                $clientname=str_replace(' ','_', getclientDetailsInvoice('name',$_REQUEST['clientnumber']));
                $clientname=htmlspecialchars($xd['clientname'], ENT_QUOTES, 'UTF-8');

                require_once '../../packages/mpd/vendor/autoload.php';
                $invoiceName=$_REQUEST['invNo']."_".$_REQUEST['id']."_renewal.pdf";

                $filestore='../../..'.TENANT_URL.'/renewals/'.$invoiceName;


                $mpdf=new \Mpdf\Mpdf([
                'setAutoTopMargin' => 'stretch',
                'setAutoBottomMargin' => 'stretch',
                'mode' => 'utf-8',
                'format' => 'A4',
                'orientation'=>'P'
                ]);$mpdf->AliasNbPages();
                $header = mb_convert_encoding($header, 'UTF-8', 'UTF-8');
                $mpdf->shrink_tables_to_fit = 1;
                $mpdf->SetHTMLHeader($header,0,true);
                $mpdf->WriteHTML($renewVer);
                if ($_REQUEST['schedule'] == 1) {
                    $mpdf->AddPage('L'); // Landscape
                    $mpdf->WriteHTML($schedule); // Print version of schedule
                }

                if ($_REQUEST['plantlist'] == 1) {
                    $mpdf->AddPage('P'); // Ensure portrait again if needed
                    $mpdf->WriteHTML($plantlist);
                }
                $mpdf->Output($filestore);

                $stmt = $GLOBALS['con']->prepare("UPDATE `renewals` SET `invoiceName` ='".$invoiceName."' WHERE `renewalnumber`='".$_REQUEST['invNo']."'")or die("1".mysqli_error($GLOBALS['con']));
                $stmt->execute();$iv=$stmt->get_result();$stmt->close();


                echo "done";
                ?>