<?php
require "racs.php";
require "vtshowsBBB.php";
session_start();

if(!isset($_SESSION['titles']) || !isset($_SESSION['positions'])|| !isset($_SESSION['usernames'])){
    header("location:index.php");
    exit;
}

$conn = mysqli_connect("localhost", "primarysales", "S5L7lF01EQZvQrGK", "primarysales_diana");

// ORIGINAL FILTER LOGIC (Restored Exactly)
$flt = racs(1);
if($flt == "access failed"){
    die("<div style='color:red; padding:20px;'>Access failed. Contact Sales and Marketing.</div>");
}

$starts = $_REQUEST['starts'] ?? date('Y-m-01');
$ends   = $_REQUEST['ends']   ?? date('Y-m-t');
$zones  = $_REQUEST['zones']  ?? 'all';
$chanell = $_REQUEST['chanell'] ?? 'all';
$regions = $_REQUEST['regions'] ?? 'all';
$agents = $_REQUEST['agents'] ?? 'all';
$terrys = $_REQUEST['terrys'] ?? 'all';
$subregions = $_REQUEST['subregions'] ?? 'all';
$agenttypes = $_REQUEST['agenttypes'] ?? 'all';
$items = $_REQUEST['items'] ?? 'all';
$ranges = $_REQUEST['ranges'] ?? 'all';
$subranges = $_REQUEST['subranges'] ?? 'all';

if(strtolower($chanell)!='all'){ $flt=$flt." and chanell='".$chanell."' ";}
if(strtolower($zones)!='all'){ $flt=$flt." and zones='".$zones."' ";}
if(strtolower($regions)!='all'){ $flt=$flt." and regions='".$regions."' ";}
if(strtolower($subregions)!='all'){ $flt=$flt." and sub_regions='".$subregions."' ";}
if(strtolower($agents)!='all'){ $flt=$flt." and agent_names='".$agents."' ";}
if(strtolower($agenttypes)!='all'){ $flt=$flt." and agent_types='".$agenttypes."' ";}
if(strtolower($terrys)!='all'){ $flt=$flt." and territory_names='".$terrys."' ";}
if(strtolower($items)!='all'){ $flt=$flt." and item_names='".$items."' ";}
if(strtolower($ranges)!='all'){ $flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($subranges)!='all'){ $flt=$flt." and sub_ranges='".$subranges."' ";}

$sprt="&zones=".$zones."&regions=".$regions."&subregions=".$subregions."&agents=".$agents."&agenttypes=".$agenttypes."&terrys=".$terrys."&ranges=".$ranges."&items=".$items."&subranges=".$subranges;
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Diana | DB Invoices Export</title>
    
    <link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.min.css" />
    <script type="text/javascript" src="jsDatePick.min.1.3.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>

    <style>
        :root { --navy: #1a1a2e; --blue: #006699; --excel: #217346; --light: #f8fafc; }
        body { font-family: 'Segoe UI', Tahoma, sans-serif; background: var(--light); margin: 0; padding: 0; }

        .modern-nav { background: var(--navy); color: white; position: sticky; top: 0; z-index: 1000; box-shadow: 0 4px 6px rgba(0,0,0,0.1); }
        .nav-top { display: flex; align-items: center; justify-content: space-between; padding: 10px 30px; height: 50px; }
        .brand { font-size: 18px; font-weight: bold; color: #fff; text-decoration: none; }
        .nav-links a { color: rgba(255,255,255,0.8); text-decoration: none; font-size: 13px; margin-left: 20px; }

        .control-bar { background: #fff; padding: 15px 30px; display: flex; flex-wrap: wrap; align-items: center; gap: 15px; border-bottom: 1px solid #e2e8f0; }
        input[type="text"] { padding: 8px 12px; border: 1px solid #cbd5e1; border-radius: 6px; font-size: 13px; }
        
        .btn-go { background: var(--blue); color: white; border: none; padding: 8px 20px; border-radius: 6px; cursor: pointer; font-weight: bold; }
        .btn-excel { background: var(--excel); color: white; border: none; padding: 8px 20px; border-radius: 6px; cursor: pointer; font-weight: bold; display: flex; align-items: center; gap: 8px; }
        .btn-excel:hover { background: #1a5c38; }

        .content { padding: 25px; }
        .table-card { background: #fff; border-radius: 12px; box-shadow: 0 4px 15px rgba(0,0,0,0.05); overflow: hidden; border: 1px solid #e2e8f0; }
        .table-responsive { overflow-x: auto; }
        table { width: 100%; border-collapse: collapse; min-width: 1100px; }
        th { background: #f8fafc; color: #64748b; font-size: 11px; text-transform: uppercase; padding: 15px; border-bottom: 2px solid #e2e8f0; text-align: left; position: sticky; top: 0; }
        td { padding: 12px 15px; border-bottom: 1px solid #f1f5f9; font-size: 13px; color: #334155; }
        tr:hover { background-color: #f8fafc; }
        
        .num { text-align: right; }
        .no-export { } /* Marker class for columns to hide during export */
    </style>

    <script>
        window.onload = function(){
            new JsDatePick({ useMode:2, target:"inputField", dateFormat:"%Y-%m-%d" });
            new JsDatePick({ useMode:2, target:"inputField2", dateFormat:"%Y-%m-%d" });
        };

        function exportToExcel() {
            // Create a temporary clone of the table to remove 'Action' column
            var originalTable = document.getElementById("dt");
            var clone = originalTable.cloneNode(true);
            
            // Remove the 'Action' header and cells from the clone
            var actions = clone.querySelectorAll('.no-export');
            actions.forEach(el => el.remove());

            var wb = XLSX.utils.table_to_book(clone, {sheet: "Invoices"});
            XLSX.writeFile(wb, "Agent_Invoices_<?php echo $starts; ?>_to_<?php echo $ends; ?>.xlsx");
        }

        function srgs() {
            var sv = document.getElementById('srctxt').value.toLowerCase();
            var trs = document.querySelectorAll("#dt tbody tr");
            trs.forEach(row => {
                row.style.display = row.innerText.toLowerCase().includes(sv) ? "" : "none";
            });
        }
    </script>
</head>
<body>

<nav class="modern-nav">
    <div class="nav-top">
        <a href="index.php" class="brand">DIANA SALES</a>
<div class="nav-links"><a href="vsregionalperfomanceteam.php">Home</a><a href="dashboard.php">Dashboard</a><a href="rpmpragentinvoicestn.php">Invoice</a><a href="index.php">Logout</a></div>
    </div>
    
    <div class="control-bar">
        <form method="post" action="rpmpragentinvoicestn.php?actp=rpt<?=$sprt?>" style="display:flex; align-items:center; gap:10px;">
            <input type="text" id="inputField" name="starts" value="<?=$starts?>" size="10" readonly>
            <span>to</span>
            <input type="text" id="inputField2" name="ends" value="<?=$ends?>" size="10" readonly>
            <button type="submit" class="btn-go">Refresh</button>
        </form>

        <button onclick="exportToExcel()" class="btn-excel">📊 Download Excel</button>

        <div style="margin-left:auto;">
            <input type="text" id="srctxt" placeholder="🔍 Filter results..." onkeyup="srgs()" style="width:200px;">
        </div>
    </div>
</nav>

<div class="content">
    <div class="table-card">
        <div class="table-responsive">
            <table id="dt">
                <thead>
                    <tr>
                        <th>Region</th>
                        <th>Date</th>
                        <th>Paymode</th>
                        <th>Sale Type</th>
                        <th>Agent Type</th>
                        <th>Inv #</th>
                        <th>Agent Name</th>
                        <th class="num">Gross</th>
                        <th class="num">Disc</th>
                        <th class="num">Vat</th>
                        <th class="num">Net</th>
                        <th style="text-align:center;" class="no-export">Action</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $sqlst = "select regions,dates,invoice_types,invoice_number,agent_names,sum(box_qty*box_price),sum(box_qty*box_price)*discount_rates,agent_types,sum(box_qty*box_price*vat_rates),paymode 
                              from rc_invoices ".$flt." 
                              and dates>='".$starts."' and dates<='".$ends."' 
                              group by regions,dates,invoice_types,invoice_number,agent_names";

                    $qry = mysqli_query($conn, $sqlst);
                    $gvl = $dsc = $vatt = 0;

                    if ($qry) {
                        while($rst = mysqli_fetch_row($qry)){
                            $gvl += $rst[5];
                            $dsc += $rst[6];
                            $vatt += $rst[8];
                            $net = $rst[5] + $rst[6] + $rst[8];
                    ?>
                        <tr>
                            <td><?=$rst[0]?></td>
                            <td><?=$rst[1]?></td>
                            <td><?=$rst[9]?></td>
                            <td><?=$rst[2]?></td>
                            <td><?=$rst[7]?></td>
                            <td><?=$rst[3]?></td>
                            <td><strong><?=$rst[4]?></strong></td>
                            <td class="num"><?=number_format($rst[5],0)?></td>
                            <td class="num"><?=number_format($rst[6],0)?></td>
                            <td class="num"><?=number_format($rst[8],0)?></td>
                            <td class="num" style="font-weight:bold;"><?=number_format($net,0)?></td>
                            <td style="text-align:center;" class="no-export">
                                <a href="rpmshowinvoicesw.php?dates=<?=$rst[1]?>&agents=<?=urlencode($rst[4])?>&territorys=0&invoices=<?=$rst[3]?>" target="_blank" style="color:var(--blue); text-decoration:none;">Open</a>
                            </td>
                        </tr>
                    <?php 
                        }
                    }
                    ?>
                </tbody>
                <tfoot style="background: #f8fafc; font-weight:bold;">
                    <tr>
                        <td colspan="7">TOTAL</td>
                        <td class="num"><?=number_format($gvl, 0)?></td>
                        <td class="num"><?=number_format($dsc, 0)?></td>
                        <td class="num"><?=number_format($vatt, 0)?></td>
                        <td class="num"><?=number_format($gvl+$dsc+$vatt, 0)?></td>
                        <td class="no-export"></td>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>
</div>

</body>
</html>