<?php
$r="../";
require_once($r."inc/funciones.php");
validNotSession($r);
$titlepage = 'ANALISIS OPERATIVO DE PEDIDOS';
$mysqli = connectDB($r);

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

$inicio = "$fi 00:00:00";
$fin    = "$ff 23:59:59";

/* =========================
   BASE PEDIDOS AGRUPADOS
========================= */
$base = $mysqli->query("
SELECT 
p.codigopedido,
p.total,
p.fecha,
p.idcliente,
p.idvendedor,
COUNT(d.id) productos,
SUM(d.cantidad) unidades
FROM z_pedidos p
JOIN z_pedido d ON d.codigopedido=p.codigopedido
WHERE p.fecha BETWEEN '$inicio' AND '$fin'
AND p.estado NOT IN (0,4,5)
GROUP BY p.codigopedido
");

/* =========================
   KPIs
========================= */
$kpi = $mysqli->query("
SELECT 
COUNT(*) pedidos,
AVG(total) ticket_prom,
MAX(total) pedido_mayor,
MIN(total) pedido_menor,
AVG(productos) prod_prom,
AVG(unidades) uni_prom,
COUNT(DISTINCT DATE(fecha)) dias
FROM(
    SELECT 
    p.codigopedido,
    p.total,
    p.fecha,
    COUNT(d.id) productos,
    SUM(d.cantidad) unidades
    FROM z_pedidos p
    JOIN z_pedido d ON d.codigopedido=p.codigopedido
    WHERE p.fecha BETWEEN '$inicio' AND '$fin'
    AND p.estado NOT IN (0,4,5)
    GROUP BY p.codigopedido
)x
")->fetch_assoc();

$kpi['pedidos_dia'] = ($kpi['dias']>0) ? ($kpi['pedidos']/$kpi['dias']) : 0;

/* =========================
   DIA PICO
========================= */
$dia_pico = $mysqli->query("
SELECT DAYNAME(fecha) dia, COUNT(*) c
FROM z_pedidos
WHERE fecha BETWEEN '$inicio' AND '$fin'
AND estado NOT IN (0,4,5)
GROUP BY dia
ORDER BY c DESC LIMIT 1
")->fetch_assoc();

/* =========================
   HORA PICO
========================= */
$hora_pico = $mysqli->query("
SELECT HOUR(fecha) h, COUNT(*) c
FROM z_pedidos
WHERE fecha BETWEEN '$inicio' AND '$fin'
AND estado NOT IN (0,4,5)
GROUP BY h
ORDER BY c DESC LIMIT 1
")->fetch_assoc();

/* =========================
   SERIES
========================= */
$pedidos_dia = $mysqli->query("
SELECT DATE(fecha) f, COUNT(*) t
FROM z_pedidos
WHERE fecha BETWEEN '$inicio' AND '$fin'
AND estado NOT IN (0,4,5)
GROUP BY f
ORDER BY f
");

$pedidos_hora = $mysqli->query("
SELECT HOUR(fecha) h, COUNT(*) t
FROM z_pedidos
WHERE fecha BETWEEN '$inicio' AND '$fin'
AND estado NOT IN (0,4,5)
GROUP BY h
ORDER BY h
");

/* =========================
   TOP PEDIDOS GRANDES
========================= */
$top_pedidos = $mysqli->query("
SELECT 
p.codigopedido,
c.nombre cliente,
u.nombre vendedor,
p.total,
COUNT(d.id) productos,
SUM(d.cantidad) unidades,
p.fecha
FROM z_pedidos p
JOIN z_pedido d ON d.codigopedido=p.codigopedido
JOIN z_clientes c ON c.id=p.idcliente
JOIN z_usuarios_vendedores u ON u.id=p.idvendedor
WHERE p.fecha BETWEEN '$inicio' AND '$fin'
AND p.estado NOT IN (0,4,5)
GROUP BY p.codigopedido
ORDER BY p.total DESC
LIMIT 20
");

/* =========================
   PEDIDOS POR VENDEDOR
========================= */
$ven = $mysqli->query("
SELECT 
u.nombre,
COUNT(*) pedidos,
AVG(p.total) ticket
FROM z_pedidos p
JOIN z_usuarios_vendedores u ON u.id=p.idvendedor
WHERE p.fecha BETWEEN '$inicio' AND '$fin'
AND p.estado NOT IN (0,4,5)
GROUP BY p.idvendedor
ORDER BY pedidos DESC
");

/* =========================
   DIA DE LA SEMANA CON MÁS PEDIDOS (NÚMERO)
========================= */
$dia_semana_pedidos = $mysqli->query("
SELECT 
CASE DAYOFWEEK(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,
COUNT(*) total
FROM z_pedidos
WHERE fecha BETWEEN '$inicio' AND '$fin'
AND estado NOT IN (0,4,5)
GROUP BY DAYOFWEEK(fecha)
ORDER BY total DESC
LIMIT 1
")->fetch_assoc();

/* =========================
   PROMEDIO DE PEDIDOS POR DIA DE SEMANA
========================= */
$pedidos_semana = $mysqli->query("
SELECT 
CASE DAYOFWEEK(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(fecha) f, COUNT(*) total_dia
    FROM z_pedidos
    WHERE fecha BETWEEN '$inicio' AND '$fin'
    AND estado NOT IN (0,4,5)
    GROUP BY DATE(fecha)
) x
JOIN z_pedidos p ON DATE(p.fecha)=x.f
GROUP BY DAYOFWEEK(p.fecha)
ORDER BY DAYOFWEEK(p.fecha)
");
?>

<!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>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">Filtrar</button></div>
</form>
</div>

<!-- KPIs -->
<div class="row text-center">
<div class="col-md-3"><div class="card p-2">Pedidos<br><b><?=$kpi['pedidos']?></b></div></div>
<div class="col-md-3"><div class="card p-2">Ticket promedio<br><b>$<?=number_format($kpi['ticket_prom'])?></b></div></div>
<div class="col-md-3"><div class="card p-2">Pedidos/día<br><b><?=number_format($kpi['pedidos_dia'],1)?></b></div></div>
<div class="col-md-3"><div class="card p-2">Productos/pedido<br><b><?=number_format($kpi['prod_prom'],1)?></b></div></div>
</div>

<div class="row text-center mt-2">
<div class="col-md-3"><div class="card p-2">Unidades/pedido<br><b><?=number_format($kpi['uni_prom'],1)?></b></div></div>
<div class="col-md-3"><div class="card p-2">Pedido mayor<br><b>$<?=number_format($kpi['pedido_mayor'])?></b></div></div>
<div class="col-md-3"><div class="card p-2">Pedido menor<br><b>$<?=number_format($kpi['pedido_menor'])?></b></div></div>
<div class="col-md-3"><div class="card p-2">Hora pico<br><b><?=$hora_pico['h']?>:00</b></div></div>
</div>

<div class="row text-center mt-2">
<div class="col-md-3"><div class="card p-2"> Día más pedidos<br><b><?=$dia_semana_pedidos['dia']?></b></div></div>
<?/*<div class="col-md-3"><div class="card p-2">Pedido mayor<br><b>$<?=number_format($kpi['pedido_mayor'])?></b></div></div>
<div class="col-md-3"><div class="card p-2">Pedido menor<br><b>$<?=number_format($kpi['pedido_menor'])?></b></div></div>
<div class="col-md-3"><div class="card p-2">Hora pico<br><b><?=$hora_pico['h']?>:00</b></div></div> */ ?>
</div>


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

<!-- TOP PEDIDOS -->
<h5 class="mt-5">Top pedidos más grandes</h5>
<table class="table table-dark table-sm">
<tr><th>Pedido</th><th>Cliente</th><th>Vendedor</th><th>Total</th><th>Productos</th><th>Unidades</th><th>Fecha</th></tr>
<?php while($p=$top_pedidos->fetch_assoc()){ ?>
<tr>
<td><?=$p['codigopedido']?></td>
<td><?=$p['cliente']?></td>
<td><?=$p['vendedor']?></td>
<td>$<?=number_format($p['total'])?></td>
<td><?=$p['productos']?></td>
<td><?=$p['unidades']?></td>
<td><?=$p['fecha']?></td>
</tr>
<?php } ?>
</table>

<!-- VENDEDORES -->
<h5 class="mt-5">Pedidos por vendedor</h5>
<table class="table table-dark table-sm">
<tr><th>Vendedor</th><th>Pedidos</th><th>Ticket promedio</th></tr>
<?php while($v=$ven->fetch_assoc()){ ?>
<tr>
<td><?=$v['nombre']?></td>
<td><?=$v['pedidos']?></td>
<td>$<?=number_format($v['ticket'])?></td>
</tr>
<?php } ?>
</table>

</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('gDia',
[<?php while($d=$pedidos_dia->fetch_assoc()){echo "'".$d['f']."',";} ?>],
[<?php $pedidos_dia->data_seek(0); while($d=$pedidos_dia->fetch_assoc()){echo $d['t'].",";} ?>], 
'Promedio de ventas por día');


makeBar('gHora',
[<?php while($h=$pedidos_hora->fetch_assoc()){echo "'".$h['h']."',";} ?>],
[<?php $pedidos_hora->data_seek(0); while($h=$pedidos_hora->fetch_assoc()){echo $h['t'].",";} ?>], 
'Promedio de ventas por hora');


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

</script>

</body>
</html>
