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

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

// Initialize all variables to prevent undefined warnings
$tdton = 0;
$tottrton = 0;
$totprton = 0;
$rdt = 0;
$tgv = 0;
$acv = 0;
$rdv = 0;
$dbrd = 0;
$ddrd = 0;
$pcl=0;
$rdavgday=0;
$wdays=0;
$rdton=0;
$starts = date('Y-m-01');
$ends = date('Y-m-t');
$zones = 'all';
$regions = 'all';
$agents = 'all';
$terrys = 'all';
$subregions = 'all';
$agenttypes = 'all';
$items = 'all';
$ranges = 'all';
$subranges = 'all';
$stores = 'all';
$chanell = 'all';
?>

<!DOCTYPE html>
<html lang="en">
<title>Diana Range Sales</title>
<head>
<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.min.css" />
<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.css" />
<script type="text/javascript" src="jsDatePick.min.1.3.js"></script>
<script src="datesps.js"></script><link rel="stylesheet" type="text/css" href="vtxstyles.css">
<script>
function scopys(pr) {
    var dts=document.getElementById("dt");
    var sels=window.getSelection();
    sels.removeAllRanges();
    var ranges=document.createRange();
    ranges.selectNodeContents(dts);
    sels.addRange(ranges);
    document.execCommand("copy");
    alert("Copied - successfull");
    sels.removeAllRanges();
}

function srgs(pr) {
    var sv=document.getElementById('srctxt').value.toLowerCase();
    var dt=document.getElementById('dt');
    var rn=dt.rows.length;
    for(var cnt=1;cnt<rn-1;cnt++){
        tvl=dt.rows[cnt].cells[0].innerText.toLowerCase();
        rvl=dt.rows[cnt].cells[1].innerText.toLowerCase();
        if(sv.length>1 && (tvl.indexOf(sv)>=0 || rvl.indexOf(sv)>=0)){
            dt.rows[cnt].style.display='table-row';
        } else {
            dt.rows[cnt].style.display='none';
        }
        if(sv.length<=1){
            dt.rows[cnt].style.display='table-row';
        }	
    }
}
</script>
</head>
<body style='font-family:calibri;font-size:small;'>
<div style='width=30%;height:100%%;'>
<?php

$flt=racs(1);

if($flt=="access failed"){
    printf("<div style='font-color:red;'>>Access failed\nContanct Sales and Marketing division</div>");
} else {
    $conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_diana");

    // Get request parameters with null checks
    if(isset($_REQUEST['starts'])) $starts = $_REQUEST['starts'];
    if(isset($_REQUEST['ends'])) $ends = $_REQUEST['ends'];
    if(isset($_REQUEST['zones'])) $zones = $_REQUEST['zones'];
    if(isset($_REQUEST['regions'])) $regions = $_REQUEST['regions'];
    if(isset($_REQUEST['agents'])) $agents = $_REQUEST['agents'];
    if(isset($_REQUEST['terrys'])) $terrys = $_REQUEST['terrys'];
    if(isset($_REQUEST['subregions'])) $subregions = $_REQUEST['subregions'];
    if(isset($_REQUEST['agenttypes'])) $agenttypes = $_REQUEST['agenttypes'];
    if(isset($_REQUEST['items'])) $items = $_REQUEST['items'];
    if(isset($_REQUEST['ranges'])) $ranges = $_REQUEST['ranges'];
    if(isset($_REQUEST['subranges'])) $subranges = $_REQUEST['subranges'];
    if(isset($_REQUEST['stores'])) $stores = $_REQUEST['stores'];
    if(isset($_REQUEST['chanell'])) $chanell = $_REQUEST['chanell'];

    // Build filter conditions
    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."' ";
    if(strtolower($stores)!='all') $flt=$flt." and stores_names='".$stores."' ";
    if(strtolower($chanell)!='all') $flt=$flt." and chanell='".$chanell."' ";

    stps(1);
    printf("<div class='sds'>");
    newdash(1);
    printf("</div>");

    printf("<div class='mnf'>");
    printf("<div style='font-size:16;color:#006699;border-bottom:1px solid silver;width:30%%;'>ZONE - PERFORMANCE - NATIONAL</div>");
    printf("<BR><BR>");
    
    $sprt="&zones=".$zones."&regions=".$regions."&subregions=".$subregions."&agents=".$agents."&agenttypes=".$agenttypes."&terrys=".$terrys."&ranges=".$ranges."&items=".$items."&subranges=".$subranges;

    printf("<div style='width:95%%;height:5%%;border:0px solid silver;border-radius:2px;display:table;vertical-align:middle;'>");
    printf("<form method='post' action='vsregionalperfomanceZone.php?actp=rpt%s'><table style='width:80%%;font-size:13;'>",$sprt);

    printf("<tr><td style='color:grey;'>Select the date range</td><td width=5%%><input type='text' id='inputField' name='starts'  value='%s' size=8 style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'></td>
    <td width=1%%>-</td><td width=5%%><input type='text' id='inputField2' value='%s' name='ends'  size=8 style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'></td><td>
    <input type='submit' value='Go' style='font-size:13;background:#CACACA;color:#006699;border:1.5px solid #006699;border-radius:1pxfont-size:13;'></form>",$starts,$ends);

    printf("<td width=32%% align='right' style='color:grey;'>Search</td><td width=20%%>
    <input type='text' id='srctxt' size=28 style='font-family:calibri;font-size:14;border:1px solid silver;border-radius:1.5px;' onkeyup=\"srgs(event)\"></td>
    <td style='font-size:12;color:green;text-align:center;background:silver;border:1px solid white;' onclick=\"scopys(1)\">Copy</td></tr>");
    printf("</table>");

    printf("<table id='dt' style='width:100%%;font-size:13;border-collapse:collapse;color:#006699;'>
    <tr style='background:silver;border:1px solid white;text-align:right;'>
    <td style='border:1px solid white;text-align:center;width:5%%;'>Area</td>
    <td style='border:1px solid white;text-align:center;width:5%%;'>Target-Ton</td>
    <td style='border:1px solid white;text-align:center;width:5%%;'>Primary-Ton</td>
    <td style='border:1px solid white;text-align:center;width:2%%;'>Pri:%%</td>
    <td style='border:1px solid white;text-align:center;width:5%%;'>RD-Ton</td>
    <td style='border:1px solid white;text-align:center;width:2%%;'>RD:%%</td>
    <td style='border:1px solid white;text-align:center;width:5%%;'>Target-value</td>
    <td style='border:1px solid white;text-align:center;width:5%%;'>Primary Sales</td>
    <td style='border:1px solid white;text-align:center;width:2%%;'>Pri:%%</td>
    <td style='border:1px solid white;text-align:center;width:5%%;'>Primary Difict</td>
    <td style='border:1px solid white;text-align:center;width:5%%;'>RD-[DB+Dir]</td>
    <td style='border:1px solid white;text-align:center;width:2%%;'>Rd:%%</td>
    <td style='border:1px solid white;text-align:center;width:5%%;'>DB-RD</td>
    <td style='border:1px solid white;text-align:center;width:4%%;'>Direct</td>
    <td style='border:1px solid white;text-align:center;width:4%%;'>D-Ton</td>
    <td style='background:silver;border:1px solid white;width:2%%;' align='center'>View</td>
    <td style='background:silver;border:1px solid white;width:2%%;' align='center'>View</td>
    <td style='background:silver;border:1px solid white;width:2%%;' align='center'>View</td>
    </tr>");

    // Simplified SQL query without rc_orders and rd_targets joins
  // Only for rc_targets since it stores data on 1st of each month
$targetStart = date('Y-m-01', strtotime($starts));

$sqlst = "select mzones,tgvl,acvl,tton,prton,rdvl,pcls,rdavgday,wdays,acvldir,rdton,rddir,znn from 
           (select distinct rgs as mzones from 
               (select distinct zone_orders as rds,rm_id as rgs from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' and zones<>'' 
                union select distinct zone_orders,rm_id from rc_targets ".$flt." and dates>='".$targetStart."' and dates<='".$ends."' and chanell='GENERAL TRADE' 
                union select distinct zone_orders,rm_id from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' 
                order by rds) as zns) as ags 
           left join (select rm_id,sum(box_qty*box_price) as tgvl,sum(box_qty*packets_per_box*packet_weights/1000000) as tton,rm_id as znn 
                      from rc_targets ".$flt." and dates>='".$targetStart."' and dates<='".$ends."' and chanell='GENERAL TRADE' group by rm_id) as tgs on tgs.rm_id=mzones 
           left join (select rm_id,sum(box_qty*box_price) as acvl,sum(box_qty*packets_per_box*packet_weights/1000000) as prton 
                      from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by rm_id) 
           as acs on acs.rm_id=mzones
           left join (select rm_id,sum(pcs_qty*pcs_price) as rdvl,sum(pcalls) as pcls,
                      sum(pcs_qty*pcs_price)/count(distinct(invoice_number))*count(distinct(agent_names)) as rdavgday,
                      count(distinct(dates)) as wdays,sum(pcs_qty*packet_weights)/1000000 as rdton 
                      from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' group by rm_id) 
           as rds on rds.rm_id=mzones
           left join (select rm_id,sum(box_qty*box_price) as acvldir,sum(box_qty*packets_per_box*packet_weights/1000000) as rddir 
                      from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and agent_types='DIRECT' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by rm_id) 
           as acsdr on acsdr.rm_id=mzones";

    $qry=mysqli_query($conn,$sqlst);
    while($rst=mysqli_fetch_row($qry)){
        // Calculate values with null checks
        $tgv += $rst[1] ?? 0;
        $acv += $rst[2] ?? 0;
        $rdv += ($rst[5] ?? 0) + ($rst[9] ?? 0);
        $pcl += $rst[6] ?? 0;
        $rdavgday += $rst[7] ?? 0;
        $wdays += $rst[8] ?? 0;
        $tottrton += $rst[3] ?? 0;
        $totprton += $rst[4] ?? 0;
        $rdton += ($rst[10] ?? 0) + ($rst[11] ?? 0);
        $rdt += ($rst[10] ?? 0) + ($rst[11] ?? 0);
        $ddrd += $rst[9] ?? 0;
        $dbrd += $rst[5] ?? 0;
        $tdton += $rst[11] ?? 0;
        
        // Calculate percentages with division protection
        $primary_percent = ($rst[3] != 0) ? ($rst[4]/$rst[3]*100) : 0;
        $rd_percent = ($rst[3] != 0) ? (($rst[10]+$rst[11])/$rst[3]*100) : 0;
        $sales_percent = ($rst[1] != 0) ? ($rst[2]/$rst[1]*100) : 0;
        $rd_sales_percent = ($rst[1] != 0) ? (($rst[5]+$rst[9])/$rst[1]*100) : 0;

        printf("<tr style='border-bottom:1px solid silver;'>
        <td>%s</td>
        <td align='right'>%s</td>
        <td align='right'>%s</td>
        <td align='right' style='background-color: #faa997;'>%s</td>
        <td align='right'>%s</td>
        <td align='right' style='background-color: #faa997;'>%s</td>
        <td align='right'>%s</td>
        <td align='right'>%s</td>
        <td align='right' style='background-color: #faa997;'>%s</td>
        <td align='right'>%s</td>
        <td align='right'>%s</td>
        <td align='right' style='background-color: #faa997;'>%s</td>
        <td align='right'>%s</td>
        <td align='right'>%s</td>
        <td align='right'>%s</td>
        <td align='center'><a href='rpmrdagenttgsummary2.php?starts=%s&ends=%s&rm_id=%s' target='_blank'>Agent</a></td>
        <td align='center'><a href='rpmrangenew.php?starts=%s&ends=%s&rmid=%s' target='_blank'>Range</a></td>
        <td align='center'><a href='rpmtgttitemwisetgsummary2.php?starts=%s&ends=%s&rmid=%s' target='_blank'>items</a></td>
        </tr>",
        $rst[0],
        number_format($rst[3] ?? 0, 2, '.', ','),
        number_format($rst[4] ?? 0, 2, '.', ','),
        number_format($primary_percent, 2, '.', ','),
        number_format(($rst[10] ?? 0) + ($rst[11] ?? 0), 2, '.', ','),
        number_format($rd_percent, 2, '.', ','),
        number_format($rst[1] ?? 0, 0, '.', ','),
        number_format($rst[2] ?? 0, 0, '.', ','),
        number_format($sales_percent, 2, '.', ','),
        number_format(($rst[2] ?? 0) - ($rst[1] ?? 0), 0, '.', ','),
        number_format(($rst[5] ?? 0) + ($rst[9] ?? 0), 0, '.', ','),
        number_format($rd_sales_percent, 2, '.', ','),
        number_format($rst[5] ?? 0, 0, '.', ','),
        number_format($rst[9] ?? 0, 0, '.', ','),
        number_format($rst[11] ?? 0, 2, '.', ','),
        $starts, $ends, $rst[0], 
        $starts, $ends, $rst[0], 
        $starts, $ends, $rst[0], 'GENERAL TRADE'
        );
    }
    
    // Calculate total percentages with division protection
    $total_primary_percent = ($tottrton != 0) ? ($totprton/$tottrton)*100 : 0;
    $total_rd_percent = ($tottrton != 0) ? ($rdt/$tottrton)*100 : 0;
    $total_sales_percent = ($tgv != 0) ? ($acv/$tgv)*100 : 0;
    $total_rd_sales_percent = ($tgv != 0) ? ($rdv/$tgv)*100 : 0;

    printf("<tr style='font-weight:bold;'>
    <td>Total</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='center'><a href='rpmrdagenttgsummary2.php?starts=%s&ends=%s&zones=%s&chanell=GENERAL TRADE' target='_blank'>Agents</a></td>
    <td align='center'><a href='rpmrangenew.php?starts=%s&ends=%s' target='_blank'>Range</a></td>
    <td align='center'><a href='rpmtgttitemwisetgsummary2.php?starts=%s&ends=%s&chanell=GENERAL TRADE' target='_blank'>items</a></td>
    </tr>",
    number_format($tottrton, 2, '.', ','),
    number_format($totprton, 2, '.', ','),
    number_format($total_primary_percent, 2, '.', ','),
    number_format($rdt, 2, '.', ','),
    number_format($total_rd_percent, 2, '.', ','),
    number_format($tgv, 0, '.', ','),
    number_format($acv, 0, '.', ','),
    number_format($total_sales_percent, 2, '.', ','),
    number_format($acv-$tgv, 0, '.', ','),
    number_format($rdv, 0, '.', ','),
    number_format($total_rd_sales_percent, 1, '.', ','),
    number_format($dbrd, 0, '.', ','),
    number_format($ddrd, 0, '.', ','),
    number_format($tdton, 2, '.', ','),
    $starts, $ends, $zones,
    $starts, $ends,
    $starts, $ends, $zones, 'GENERAL TRADE');

    printf("</table></div>");
}
?>
</div>
</body>
</html>