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

$fi = $_GET['fi'] ?? date("Y-m-01");
$ff = $_GET['ff'] ?? date("Y-m-d");

$inicio_actual = "$fi 00:00:00";
$fin_actual    = "$ff 23:59:59";

$where = " WHERE p.fecha >= '$inicio_actual' AND p.fecha <= '$fin_actual' AND p.estado NOT IN (0,4,5) ";

/* =========================
   KPIs BASE
========================= */
$base = $mysqli->query("
SELECT 
SUM(p.total) AS ventas,
COUNT(*) AS pedidos,
AVG(p.total) AS ticket,
COUNT(DISTINCT p.idcliente) AS clientes,
COUNT(DISTINCT p.idvendedor) AS vendedores,
COUNT(DISTINCT DATE(p.fecha)) AS dias
FROM z_pedidos p
$where
")->fetch_assoc();

/* Utilidad */
$util = $mysqli->query("
SELECT 
SUM(d.cantidad * d.precio * (pp.ganancia/100)) AS utilidad
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
")->fetch_assoc();

$kpi = $base;
$kpi['utilidad'] = $util['utilidad'] ?? 0;

$margen = ($kpi['ventas']>0) ? ($kpi['utilidad']/$kpi['ventas']*100) : 0;

/* =========================
   TIPOS DE PEDIDO
========================= */
$tipos_pedido = $mysqli->query("
SELECT 
tipopedido,
COUNT(*) cantidad,
SUM(total) total
FROM z_pedidos p
$where
GROUP BY tipopedido
");

$tipo_R = 0;
$tipo_RV = 0;
$ventas_R = 0;
$ventas_RV = 0;

while($t = $tipos_pedido->fetch_assoc()){

    if($t['tipopedido']=='R'){
        $tipo_R = $t['cantidad'];
        $ventas_R = $t['total'];
    }

    if($t['tipopedido']=='RV'){
        $tipo_RV = $t['cantidad'];
        $ventas_RV = $t['total'];
    }

}

$total_tipos = $ventas_R + $ventas_RV;

/* =========================
   PROMEDIO DIARIO
========================= */
$promedio_dia = $mysqli->query("
SELECT AVG(total_dia) promedio
FROM(
    SELECT DATE(p.fecha) f, SUM(p.total) total_dia
    FROM z_pedidos p
    $where
    GROUP BY f
)x
")->fetch_assoc();

/* =========================
   DIA MAS FUERTE EN ESPAÑOL
========================= */
$dia_semana = $mysqli->query("
SELECT 
CASE DAYOFWEEK(p.fecha)
    WHEN 1 THEN 'Domingo'
    WHEN 2 THEN 'Lunes'
    WHEN 3 THEN 'Martes'
    WHEN 4 THEN 'Miércoles'
    WHEN 5 THEN 'Jueves'
    WHEN 6 THEN 'Viernes'
    WHEN 7 THEN 'Sábado'
END dia,
SUM(p.total) total
FROM z_pedidos p
$where
GROUP BY DAYOFWEEK(p.fecha)
ORDER BY total DESC
LIMIT 1
")->fetch_assoc();

/* =========================
   PROMEDIO POR DIA DE SEMANA
========================= */
$ventas_semana = $mysqli->query("
SELECT 
CASE DAYOFWEEK(p.fecha)
    WHEN 1 THEN 'Domingo'
    WHEN 2 THEN 'Lunes'
    WHEN 3 THEN 'Martes'
    WHEN 4 THEN 'Miércoles'
    WHEN 5 THEN 'Jueves'
    WHEN 6 THEN 'Viernes'
    WHEN 7 THEN 'Sábado'
END dia,
AVG(total_dia) promedio
FROM(
    SELECT DATE(p.fecha) fecha, SUM(p.total) total_dia
    FROM z_pedidos p
    $where
    GROUP BY DATE(p.fecha)
) t
JOIN z_pedidos p ON DATE(p.fecha)=t.fecha
GROUP BY DAYOFWEEK(p.fecha)
ORDER BY DAYOFWEEK(p.fecha)
");

/* =========================
   SERIES
========================= */
$ventas_dia = $mysqli->query("
SELECT DATE(p.fecha) dia, SUM(p.total) total
FROM z_pedidos p
$where
GROUP BY dia
ORDER BY dia
");

$pedidos_dia = $mysqli->query("
SELECT DATE(p.fecha) dia, COUNT(*) total
FROM z_pedidos p
$where
GROUP BY dia
ORDER BY dia
");

/* =========================
   TOP
========================= */
$top_productos = $mysqli->query("
SELECT 
pr.producto,

SUM(CASE WHEN p.tipopedido='R'  THEN d.cantidad*d.precio ELSE 0 END) ventas_R,
SUM(CASE WHEN p.tipopedido='RV' THEN d.cantidad*d.precio ELSE 0 END) ventas_RV,
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

GROUP BY d.idproducto
ORDER BY total DESC
LIMIT 30
");

$top_clientes = $mysqli->query("SELECT 
c.nombre,

SUM(CASE WHEN p.tipopedido='R' 
THEN d.cantidad*d.precio ELSE 0 END) ventas_R,

SUM(CASE WHEN p.tipopedido='RV' 
THEN d.cantidad*d.precio ELSE 0 END) ventas_RV,

SUM(d.cantidad*d.precio) total

FROM z_pedido d
JOIN z_pedidos p ON p.codigopedido=d.codigopedido
JOIN z_clientes c ON c.id=p.idcliente

$where

GROUP BY p.idcliente
ORDER BY total DESC
LIMIT 30
");

$top_vendedores = $mysqli->query("SELECT 
u.nombre,
u.alias,

SUM(CASE WHEN p.tipopedido='R' 
THEN d.cantidad*d.precio ELSE 0 END) ventas_R,

SUM(CASE WHEN p.tipopedido='RV' 
THEN d.cantidad*d.precio ELSE 0 END) ventas_RV,

SUM(d.cantidad*d.precio) total

FROM z_pedido d
JOIN z_pedidos p ON p.codigopedido=d.codigopedido
JOIN z_usuarios_vendedores u ON u.id=p.idvendedor

$where

GROUP BY p.idvendedor
ORDER BY total DESC
LIMIT 20
");
?>

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

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

<div class="card mb-3 p-3">
<form class="form-row">
<div class="col-md-3">
<label>Desde</label>
<input type="date" name="fi" value="<?=$fi?>" class="form-control">
</div>
<div class="col-md-3">
<label>Hasta</label>
<input type="date" name="ff" value="<?=$ff?>" class="form-control">
</div>
<div class="col-md-2 mt-4">
<button class="btn btn-primary btn-block">Filtrar</button>
</div>
</form>
</div>

<!-- KPIs -->
<div class="row text-center">
<div class="col-md-2"><div class="card p-2">Ventas<br><b>$<?=number_format($kpi['ventas'])?></b></div></div>
<div class="col-md-2"><div class="card p-2">Utilidad<br><b>$<?=number_format($kpi['utilidad'])?></b></div></div>
<div class="col-md-2"><div class="card p-2">Margen<br><b><?=number_format($margen,2)?>%</b></div></div>
<div class="col-md-2"><div class="card p-2">Pedidos<br><b><?=$kpi['pedidos']?></b></div></div>
<div class="col-md-2"><div class="card p-2">Ticket<br><b>$<?=number_format($kpi['ticket'])?></b></div></div>
<div class="col-md-2"><div class="card p-2">Días trabajados<br><b><?=$kpi['dias']?></b></div></div>
</div>

<div class="row text-center mt-2">
<div class="col-md-3"><div class="card p-2">Clientes activos<br><b><?=$kpi['clientes']?></b></div></div>
<div class="col-md-3"><div class="card p-2">Vendedores activos<br><b><?=$kpi['vendedores']?></b></div></div>
<div class="col-md-3"><div class="card p-2">Promedio diario venta<br><b>$<?=number_format($promedio_dia['promedio'])?></b></div></div>
<div class="col-md-3"><div class="card p-2">Día más fuerte<br><b><?=$dia_semana['dia']?></b></div></div>
</div>
<div class="row text-center mt-2">

<div class="col-md-3">
<div class="card p-2">
Total Tiquetes de Salida<br>
<b><?=$tipo_R?></b>

</div>
</div>

<div class="col-md-3">
<div class="card p-2">
Total Facturas<br>
<b><?=$tipo_RV?></b>
</div>
</div>

<div class="col-md-3">
<div class="card p-2">
Ventas Tiquetes de Salida<br>
<b>$<?=number_format($ventas_R)?></b>
</div>
</div>

<div class="col-md-3">
<div class="card p-2">
Ventas Facturas<br>
<b>$<?=number_format($ventas_RV)?></b>
</div>
</div>

</div>
<!-- GRAFICOS -->
<!-- GRAFICOS -->
<div class="row mt-4">
    <div class="col-md-4">
        <canvas id="ventasDia"></canvas>
    </div>

    <div class="col-md-4">
        <canvas id="pedidosDia"></canvas>
    </div>

    <div class="col-md-4">
        <canvas id="ventasSemana"></canvas>
    </div>
</div>


<!-- TOP -->
<div class="row mt-4">

<div class="col-md-4">
<h5>Top Productos</h5>
<table class="table table-dark table-sm">
<tr><th>Producto</th><th class="text-right">Tiquets</th><th class="text-right">Facturas</th><th class="text-right">Ventas</th></tr>
<?php while($p=$top_productos->fetch_assoc()){ ?>
<tr>
<td><?=$p['producto']?></td>
<td class="text-right">$<?=number_format($p['ventas_R'])?></td>
<td class="text-right">$<?=number_format($p['ventas_RV'])?></td>
<td class="text-right">$<?=number_format($p['total'])?></td>
</tr>
<?php } ?>
</table>
</div>

<div class="col-md-4">
<h5>Top Clientes</h5>
<table class="table table-dark table-sm">
<tr><th>Cliente</th><th class="text-right">Tiquets</th><th class="text-right">Facturas</th><th class="text-right">Ventas</th></tr>
<?php while($c=$top_clientes->fetch_assoc()){ ?>
<tr>
<td><?=$c['nombre']?></td>
<td class="text-right">$<?=number_format($c['ventas_R'])?></td>
<td class="text-right">$<?=number_format($c['ventas_RV'])?></td>
<td class="text-right">$<?=number_format($c['total'])?></td>
</tr>
<?php } ?>
</table>
</div>

<div class="col-md-4">
<h5>Top Vendedores</h5>
<table class="table table-dark table-sm">
<tr><th>Vendedor</th><th class="text-right">Tiquets</th><th class="text-right">Facturas</th><th class="text-right">Ventas</th></tr>
<?php while($v=$top_vendedores->fetch_assoc()){ ?>
<tr>
<td><?=$v['nombre'].' - '.$v['alias']?></td>
<td class="text-right">$<?=number_format($v['ventas_R'])?></td>
<td class="text-right">$<?=number_format($v['ventas_RV'])?></td>
<td class="text-right">$<?=number_format($v['total'])?></td>
</tr>
<?php } ?>
</table>
</div>

</div>

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

<script>
function makeBar(id, labels, data, titulo){
const baseColors=['#4e73df','#1cc88a','#36b9cc','#f6c23e','#e74a3b','#858796','#20c997','#fd7e14','#6610f2','#6f42c1'];
let bg=[];
for(let i=0;i<data.length;i++){ bg.push(baseColors[i % baseColors.length]); }
new Chart(document.getElementById(id),{
type:'bar',
data:{labels:labels,datasets:[{label: titulo,data:data,backgroundColor:bg}]},
options:{responsive:true}
});
}

makeBar('ventasDia',
[<?php while($v=$ventas_dia->fetch_assoc()){echo "'".$v['dia']."',";} ?>],
[<?php $ventas_dia->data_seek(0); while($v=$ventas_dia->fetch_assoc()){echo $v['total'].",";} ?>],
'Ventas por día');

makeBar('pedidosDia',
[<?php while($v=$pedidos_dia->fetch_assoc()){echo "'".$v['dia']."',";} ?>],
[<?php $pedidos_dia->data_seek(0); while($v=$pedidos_dia->fetch_assoc()){echo $v['total'].",";} ?>],
'Pedidos por día');

makeBar('ventasSemana',
[<?php while($v=$ventas_semana->fetch_assoc()){echo "'".$v['dia']."',";} ?>],
[<?php $ventas_semana->data_seek(0); while($v=$ventas_semana->fetch_assoc()){echo round($v['promedio']).",";} ?>],
'Promedio de ventas por día de la semana');
</script>

</body>
</html>
