<?php
$r="../";
require_once($r."inc/funciones.php");
validNotSession($r);
$titlepage = 'COMPARATIVO MASTER PRO';
$mysqli = connectDB($r);

/* =========================
   FECHAS
========================= */
$fi1 = $_GET['fi1'] ?? date("Y-m-01");
$ff1 = $_GET['ff1'] ?? date("Y-m-d");

$fi2 = $_GET['fi2'] ?? date("Y-m-01", strtotime("-1 month"));
$ff2 = $_GET['ff2'] ?? date("Y-m-d", strtotime("-1 month"));

$i1 = "$fi1 00:00:00";
$f1 = "$ff1 23:59:59";
$i2 = "$fi2 00:00:00";
$f2 = "$ff2 23:59:59";

function cre($a,$b){ if($b<=0) return 0; return (($a-$b)/$b)*100; }

/* =========================
   KPIs POR PERIODO
========================= */
function kpiPeriodo($mysqli,$i,$f){

    $base = $mysqli->query("
    SELECT 
    SUM(p.total) ventas,
    COUNT(*) pedidos,
    AVG(p.total) ticket,
    COUNT(DISTINCT p.idcliente) clientes,
    COUNT(DISTINCT p.idvendedor) vendedores
    FROM z_pedidos p
    WHERE p.fecha BETWEEN '$i' AND '$f'
    AND p.estado NOT IN (0,4,5)
    ")->fetch_assoc();

    $det = $mysqli->query("
    SELECT 
    SUM(d.cantidad*d.precio*(pp.ganancia/100)) utilidad,
    SUM(d.cantidad*pp.cantidad) unidades,
    COUNT(DISTINCT d.idproducto) productos
    FROM z_pedido d
    JOIN z_pedidos p ON p.codigopedido=d.codigopedido
    LEFT JOIN z_presentaciones_productos pp ON pp.id=d.idpresentacion
    WHERE p.fecha BETWEEN '$i' AND '$f'
    AND p.estado NOT IN (0,4,5)
    ")->fetch_assoc();

    $topProd = $mysqli->query("
    SELECT pr.producto, SUM(d.cantidad*d.precio) total
    FROM z_pedido d
    JOIN z_pedidos p ON p.codigopedido=d.codigopedido
    JOIN z_productos pr ON pr.id=d.idproducto
    WHERE p.fecha BETWEEN '$i' AND '$f'
    AND p.estado NOT IN (0,4,5)
    GROUP BY d.idproducto
    ORDER BY total DESC LIMIT 1
    ")->fetch_assoc();

    $topCli = $mysqli->query("
    SELECT c.nombre, SUM(p.total) total
    FROM z_pedidos p
    JOIN z_clientes c ON c.id=p.idcliente
    WHERE p.fecha BETWEEN '$i' AND '$f'
    AND p.estado NOT IN (0,4,5)
    GROUP BY p.idcliente
    ORDER BY total DESC LIMIT 1
    ")->fetch_assoc();

    $topVen = $mysqli->query("
    SELECT u.nombre, SUM(p.total) total
    FROM z_pedidos p
    JOIN z_usuarios_vendedores u ON u.id=p.idvendedor
    WHERE p.fecha BETWEEN '$i' AND '$f'
    AND p.estado NOT IN (0,4,5)
    GROUP BY p.idvendedor
    ORDER BY total DESC LIMIT 1
    ")->fetch_assoc();

    return array_merge($base,$det,[
        'top_producto'=>$topProd['producto'] ?? '',
        'top_producto_valor'=>$topProd['total'] ?? 0,
        'top_cliente'=>$topCli['nombre'] ?? '',
        'top_cliente_valor'=>$topCli['total'] ?? 0,
        'top_vendedor'=>$topVen['nombre'] ?? '',
        'top_vendedor_valor'=>$topVen['total'] ?? 0
    ]);
}

$A = kpiPeriodo($mysqli,$i1,$f1);
$B = kpiPeriodo($mysqli,$i2,$f2);

/* =========================
   SERIES RELATIVAS
========================= */
$serieA = $mysqli->query("
SELECT DATEDIFF(DATE(p.fecha),'$fi1') d, SUM(p.total) t
FROM z_pedidos p
WHERE p.fecha BETWEEN '$i1' AND '$f1'
AND p.estado NOT IN (0,4,5)
GROUP BY d ORDER BY d
");

$serieB = $mysqli->query("
SELECT DATEDIFF(DATE(p.fecha),'$fi2') d, SUM(p.total) t
FROM z_pedidos p
WHERE p.fecha BETWEEN '$i2' AND '$f2'
AND p.estado NOT IN (0,4,5)
GROUP BY d ORDER BY d
");

/* =========================
   TABLAS COMPARATIVAS
========================= */
$tabla_productos = $mysqli->query("
SELECT pr.producto,
SUM(CASE WHEN p.fecha BETWEEN '$i1' AND '$f1' THEN d.cantidad*d.precio ELSE 0 END) a,
SUM(CASE WHEN p.fecha BETWEEN '$i2' AND '$f2' THEN d.cantidad*d.precio ELSE 0 END) b
FROM z_pedido d
JOIN z_pedidos p ON p.codigopedido=d.codigopedido
JOIN z_productos pr ON pr.id=d.idproducto
WHERE p.estado NOT IN (0,4,5)
GROUP BY d.idproducto
ORDER BY a DESC LIMIT 20
");

$tabla_clientes = $mysqli->query("
SELECT c.nombre,
SUM(CASE WHEN p.fecha BETWEEN '$i1' AND '$f1' THEN p.total ELSE 0 END) a,
SUM(CASE WHEN p.fecha BETWEEN '$i2' AND '$f2' THEN p.total ELSE 0 END) b
FROM z_pedidos p
JOIN z_clientes c ON c.id=p.idcliente
WHERE p.estado NOT IN (0,4,5)
GROUP BY p.idcliente
ORDER BY a DESC LIMIT 20
");

$tabla_vendedores = $mysqli->query("
SELECT u.nombre, u.alias,
SUM(CASE WHEN p.fecha BETWEEN '$i1' AND '$f1' THEN p.total ELSE 0 END) a,
SUM(CASE WHEN p.fecha BETWEEN '$i2' AND '$f2' THEN p.total ELSE 0 END) b
FROM z_pedidos p
JOIN z_usuarios_vendedores u ON u.id=p.idvendedor
WHERE p.estado NOT IN (0,4,5)
GROUP BY p.idvendedor
ORDER BY a DESC LIMIT 20
");
?>

<!DOCTYPE html>
<html lang="es">
<?php include $r."inc/head.php"; ?>
<body class="fixed-nav sticky-footer bg-dark">
<?php include $r."inc/nav.php"; ?>

<div class="content-wrapper">
<div class="container-fluid">

<div class="card p-3 mb-3">
<form class="form-row">
<div class="col-md-3"><label>A Desde</label><input type="date" name="fi1" value="<?=$fi1?>" class="form-control"></div>
<div class="col-md-3"><label>A Hasta</label><input type="date" name="ff1" value="<?=$ff1?>" class="form-control"></div>
<div class="col-md-3"><label>B Desde</label><input type="date" name="fi2" value="<?=$fi2?>" class="form-control"></div>
<div class="col-md-3"><label>B Hasta</label><input type="date" name="ff2" value="<?=$ff2?>" class="form-control"></div>
<button class="btn btn-primary mt-3 ml-3">Comparar</button>
</form>
</div>

<!-- KPIs -->
<div class="row text-center">
<?php
$cards=[
['Ventas',$A['ventas'],$B['ventas']],
['Utilidad',$A['utilidad'],$B['utilidad']],
['Pedidos',$A['pedidos'],$B['pedidos']],
['Ticket',$A['ticket'],$B['ticket']],
['Productos',$A['productos'],$B['productos']],
['Unidades',$A['unidades'],$B['unidades']],
['Clientes',$A['clientes'],$B['clientes']],
['Vendedores',$A['vendedores'],$B['vendedores']],
];
foreach($cards as $c){
?>
<div class="col-md-3 mb-3">
<div class="card p-3">
<b><?=$c[0]?></b>
<h4>$<?=number_format($c[1])?></h4>
<small>B: $<?=number_format($c[2])?></small><br>
<small>Δ <?=number_format(cre($c[1],$c[2]),1)?>%</small>
</div>
</div>
<?php } ?>

<div class="col-md-3 mb-3"><div class="card p-3"><b>Producto líder</b><h5><?=$A['top_producto']?></h5>$<?=number_format($A['top_producto_valor'])?></div></div>
<div class="col-md-3 mb-3"><div class="card p-3"><b>Cliente líder</b><h5><?=$A['top_cliente']?></h5>$<?=number_format($A['top_cliente_valor'])?></div></div>
<div class="col-md-3 mb-3"><div class="card p-3"><b>Vendedor líder</b><h5><?=$A['top_vendedor']?></h5>$<?=number_format($A['top_vendedor_valor'])?></div></div>
</div>

<div class="row mt-4">
<div class="col-md-6"><canvas id="bar"></canvas></div>
<div class="col-md-6"><canvas id="line"></canvas></div>
</div>

<h5 class="mt-5">Comparativo Productos</h5>
<table class="table table-dark table-sm">
<tr><th>Producto</th><th>A</th><th>B</th><th>Δ</th><th>%</th></tr>
<?php while($p=$tabla_productos->fetch_assoc()){
$dif=$p['a']-$p['b']; $por=($p['b']>0)?(($dif/$p['b'])*100):0;
?>
<tr>
<td><?=$p['producto']?></td>
<td>$<?=number_format($p['a'])?></td>
<td>$<?=number_format($p['b'])?></td>
<td>$<?=number_format($dif)?></td>
<td><?=number_format($por,1)?>%</td>
</tr>
<?php } ?>
</table>

<h5 class="mt-5">Comparativo Clientes</h5>
<table class="table table-dark table-sm">
<tr><th>Cliente</th><th>A</th><th>B</th><th>Δ</th><th>%</th></tr>
<?php while($c=$tabla_clientes->fetch_assoc()){
$dif=$c['a']-$c['b']; $por=($c['b']>0)?(($dif/$c['b'])*100):0;
?>
<tr>
<td><?=$c['nombre']?></td>
<td>$<?=number_format($c['a'])?></td>
<td>$<?=number_format($c['b'])?></td>
<td>$<?=number_format($dif)?></td>
<td><?=number_format($por,1)?>%</td>
</tr>
<?php } ?>
</table>

<h5 class="mt-5">Comparativo Vendedores</h5>
<table class="table table-dark table-sm">
<tr><th>Vendedor</th><th>A</th><th>B</th><th>Δ</th><th>%</th></tr>
<?php while($v=$tabla_vendedores->fetch_assoc()){
$dif=$v['a']-$v['b']; $por=($v['b']>0)?(($dif/$v['b'])*100):0;
?>
<tr>
<td><?=$v['nombre']?></td>
<td>$<?=number_format($v['a'])?></td>
<td>$<?=number_format($v['b'])?></td>
<td>$<?=number_format($dif)?></td>
<td><?=number_format($por,1)?>%</td>
</tr>
<?php } ?>
</table>

</div>
<?php include $r."inc/foot.php"; ?>
</div>

<script>
new Chart(document.getElementById('bar'),{
type:'bar',
data:{
labels:['Ventas','Utilidad','Pedidos'],
datasets:[
{label:'Periodo A',data:[<?=$A['ventas']?>,<?=$A['utilidad']?>,<?=$A['pedidos']?>]},
{label:'Periodo B',data:[<?=$B['ventas']?>,<?=$B['utilidad']?>,<?=$B['pedidos']?>]}
]}
});

new Chart(document.getElementById('line'),{
type:'line',
data:{
labels:[<?php $serieA->data_seek(0); while($s=$serieA->fetch_assoc()){echo "'D".$s['d']."',";} ?>],
datasets:[
{label:'A',data:[<?php $serieA->data_seek(0); while($s=$serieA->fetch_assoc()){echo $s['t'].",";} ?>]},
{label:'B',data:[<?php $serieB->data_seek(0); while($s=$serieB->fetch_assoc()){echo $s['t'].",";} ?>]}
]}
});
</script>

</body>
</html>
