415 lines
12 KiB
PHP
415 lines
12 KiB
PHP
<?php
|
|
session_start();
|
|
if (!isset($_SESSION['user_id'])) {
|
|
header('Location: login.php');
|
|
exit;
|
|
}
|
|
|
|
require_once 'db/config.php';
|
|
require_once 'vendor/autoload.php';
|
|
|
|
use Shuchkin\SimpleXLSXGen;
|
|
|
|
function splitProvinciaDistrito(?string $value): array
|
|
{
|
|
$value = trim((string)$value);
|
|
if ($value === '') {
|
|
return ['', ''];
|
|
}
|
|
|
|
$clean = preg_replace('/\s+/', ' ', $value);
|
|
$parts = preg_split('/\s*[\/\-,|]\s*/', $clean, 2);
|
|
|
|
if (is_array($parts) and count($parts) === 2) {
|
|
return [trim($parts[0]), trim($parts[1])];
|
|
}
|
|
|
|
return [$clean, ''];
|
|
}
|
|
|
|
function parseTotalQuantity(mixed $value): int
|
|
{
|
|
if (is_int($value)) {
|
|
return $value;
|
|
}
|
|
|
|
if (is_float($value)) {
|
|
return (int)$value;
|
|
}
|
|
|
|
$s = trim((string)$value);
|
|
if ($s === '') {
|
|
return 0;
|
|
}
|
|
|
|
// Old/legacy format can be: "1+2+3"
|
|
if (strpos($s, '+') !== false) {
|
|
$sum = 0;
|
|
foreach (explode('+', $s) as $part) {
|
|
$part = trim($part);
|
|
if ($part === '') {
|
|
continue;
|
|
}
|
|
if (is_numeric($part)) {
|
|
$sum += (int)$part;
|
|
continue;
|
|
}
|
|
if (preg_match('/\d+/', $part, $m)) {
|
|
$sum += (int)$m[0];
|
|
}
|
|
}
|
|
return $sum;
|
|
}
|
|
|
|
if (is_numeric($s)) {
|
|
return (int)$s;
|
|
}
|
|
|
|
if (preg_match('/\d+/', $s, $m)) {
|
|
return (int)$m[0];
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
function formatPriceSegment(float $value): string
|
|
{
|
|
$rounded = round($value, 2);
|
|
if (abs($rounded - (int)$rounded) < 0.00001) {
|
|
return (string)(int)$rounded;
|
|
}
|
|
|
|
// Keep up to 2 decimals, but remove trailing zeros
|
|
$s = number_format($rounded, 2, '.', '');
|
|
$s = rtrim(rtrim($s, '0'), '.');
|
|
return $s;
|
|
}
|
|
|
|
function extractProductNames(array $pedido): array
|
|
{
|
|
$names = [];
|
|
$notas = (string)($pedido['notas'] ?? '');
|
|
|
|
if (preg_match('/Detalle de productos:\\s*(.+)$/mi', $notas, $match)) {
|
|
$items = explode(',', $match[1]);
|
|
foreach ($items as $item) {
|
|
$name = preg_replace('/\\s*\\(x\\d+\\)\\s*$/i', '', trim($item));
|
|
if ($name !== '') {
|
|
$names[] = $name;
|
|
}
|
|
}
|
|
}
|
|
|
|
if (empty($names) && !empty($pedido['producto'])) {
|
|
foreach (explode(',', (string)$pedido['producto']) as $item) {
|
|
$name = trim($item);
|
|
if ($name !== '') {
|
|
$names[] = $name;
|
|
}
|
|
}
|
|
}
|
|
|
|
return array_values(array_unique($names));
|
|
}
|
|
|
|
function extractProductDetailsWithQuantities(array $pedido): array
|
|
{
|
|
$notas = (string)($pedido['notas'] ?? '');
|
|
|
|
// Prefer parsing from "Detalle de productos:" (it stores quantities as (xN))
|
|
if (preg_match_all('/Detalle de productos:\\s*(.+)$/mi', $notas, $matches) && !empty($matches[1])) {
|
|
$lastLine = trim((string)end($matches[1]));
|
|
if ($lastLine !== '') {
|
|
$parts = preg_split('/\\s*,\\s*/u', $lastLine);
|
|
$out = [];
|
|
|
|
if (is_array($parts)) {
|
|
foreach ($parts as $part) {
|
|
$part = trim((string)$part);
|
|
if ($part === '') {
|
|
continue;
|
|
}
|
|
|
|
// Expected format: "Nombre de producto (x2)"
|
|
if (preg_match('/^(.*?)\\s*\\(x\\s*(\\d+)\\s*\\)\\s*$/iu', $part, $m)) {
|
|
$name = trim((string)$m[1]);
|
|
$qty = (int)$m[2];
|
|
} else {
|
|
$name = $part;
|
|
$qty = 1;
|
|
}
|
|
|
|
if ($name !== '') {
|
|
if ($qty <= 0) {
|
|
$qty = 1;
|
|
}
|
|
$out[] = ['name' => $name, 'qty' => $qty];
|
|
}
|
|
}
|
|
}
|
|
|
|
if (!empty($out)) {
|
|
return $out;
|
|
}
|
|
}
|
|
}
|
|
|
|
// Fallback: use "producto" + "cantidad" (best-effort)
|
|
$productoStr = (string)($pedido['producto'] ?? '');
|
|
$names = [];
|
|
foreach (preg_split('/\\s*,\\s*/u', $productoStr) as $n) {
|
|
$n = trim((string)$n);
|
|
if ($n !== '') {
|
|
$names[] = $n;
|
|
}
|
|
}
|
|
|
|
if (empty($names)) {
|
|
return [];
|
|
}
|
|
|
|
$cantidad_total = parseTotalQuantity($pedido['cantidad'] ?? 0);
|
|
|
|
// Legacy possibility: "cantidad" can be like "1+2"
|
|
$cantidadField = $pedido['cantidad'] ?? '';
|
|
$qtyParts = [];
|
|
if (is_string($cantidadField) && strpos(trim($cantidadField), '+') !== false) {
|
|
$qtyParts = array_map('trim', explode('+', $cantidadField));
|
|
}
|
|
|
|
$out = [];
|
|
$countNames = count($names);
|
|
foreach ($names as $i => $name) {
|
|
if ($countNames === 1) {
|
|
$qty = $cantidad_total > 0 ? $cantidad_total : 1;
|
|
} elseif (!empty($qtyParts) && isset($qtyParts[$i]) && is_numeric($qtyParts[$i])) {
|
|
$qty = (int)$qtyParts[$i];
|
|
if ($qty <= 0) {
|
|
$qty = 1;
|
|
}
|
|
} else {
|
|
$qty = 1;
|
|
}
|
|
|
|
$out[] = ['name' => $name, 'qty' => $qty];
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
|
|
function getEanMap(PDO $pdo, array $productNames): array
|
|
{
|
|
$productNames = array_values(array_filter(array_map('trim', $productNames)));
|
|
if (empty($productNames)) {
|
|
return [];
|
|
}
|
|
|
|
$uniqueNames = array_values(array_unique($productNames));
|
|
$placeholders = implode(',', array_fill(0, count($uniqueNames), '?'));
|
|
$stmt = $pdo->prepare("SELECT nombre, ean FROM products WHERE nombre IN ($placeholders)");
|
|
$stmt->execute($uniqueNames);
|
|
|
|
$map = [];
|
|
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
|
|
$nombre = (string)($row['nombre'] ?? '');
|
|
$ean = trim((string)($row['ean'] ?? ''));
|
|
if ($nombre !== '' && $ean !== '') {
|
|
$map[$nombre] = $ean;
|
|
}
|
|
}
|
|
|
|
return $map;
|
|
}
|
|
|
|
function getEansForProducts(PDO $pdo, array $productNames): string
|
|
{
|
|
if (empty($productNames)) {
|
|
return '';
|
|
}
|
|
|
|
$placeholders = implode(',', array_fill(0, count($productNames), '?'));
|
|
$stmt = $pdo->prepare("SELECT nombre, ean FROM products WHERE nombre IN ($placeholders)");
|
|
$stmt->execute($productNames);
|
|
|
|
$map = [];
|
|
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
|
|
$ean = trim((string)($row['ean'] ?? ''));
|
|
if ($ean !== '') {
|
|
$map[(string)($row['nombre'] ?? '')] = $ean;
|
|
}
|
|
}
|
|
|
|
$eans = [];
|
|
foreach ($productNames as $name) {
|
|
if (!empty($map[$name])) {
|
|
$eans[] = $map[$name];
|
|
}
|
|
}
|
|
|
|
return implode('|', array_values(array_unique($eans)));
|
|
}
|
|
|
|
try {
|
|
$pdo = db();
|
|
|
|
$user_id = $_SESSION['user_id'];
|
|
$user_role = $_SESSION['user_role'] ?? 'Asesor';
|
|
|
|
$search_query = trim($_GET['q'] ?? '');
|
|
|
|
// Exportar solo los pedidos con fecha de entrega "mañana"
|
|
$date_condition = "DATE(p.fecha_entrega) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
|
|
|
|
$sql = "SELECT p.* FROM pedidos p WHERE p.estado IN ('RUTA_CONTRAENTREGA', 'PENDIENTE', 'NO CONTESTO, VOLVER A LLAMAR', 'NO CONTESTO, DEVOLVER LLAMADA', 'CANCELADO', 'REPROGRAMADO', 'ENTREGA EXITOSA', 'RETORNADO')";
|
|
$params = [];
|
|
|
|
if ($user_role === 'Asesor') {
|
|
$sql .= " AND p.asesor_id = ?";
|
|
$params[] = $user_id;
|
|
}
|
|
|
|
if ($search_query !== '') {
|
|
$sql .= " AND (p.nombre_completo LIKE ? OR p.dni_cliente LIKE ? OR p.celular LIKE ?)";
|
|
$params[] = "%$search_query%";
|
|
$params[] = "%$search_query%";
|
|
$params[] = "%$search_query%";
|
|
}
|
|
|
|
// Siempre: pedidos cuya fecha de entrega sea mañana
|
|
$sql .= " AND $date_condition";
|
|
|
|
$sql .= " ORDER BY p.created_at DESC";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute($params);
|
|
$pedidos = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$rows = [];
|
|
$rows[] = [
|
|
'Nombre y apellido',
|
|
'Celular',
|
|
'Pais',
|
|
'Departamento',
|
|
'Provincia',
|
|
'Distrito',
|
|
'Direccion',
|
|
'Referencia',
|
|
'Coordenadas',
|
|
'Codigo EAN',
|
|
'Cantidad',
|
|
'Precio',
|
|
'Total',
|
|
'DE DEDICATORIA / OBS.'
|
|
];
|
|
|
|
foreach ($pedidos as $pedido) {
|
|
[$provincia, $distrito] = splitProvinciaDistrito($pedido['codigo_rastreo'] ?? '');
|
|
|
|
$nota_adicional = trim((string)($pedido['nota_adicional'] ?? ''));
|
|
if ($nota_adicional === '') {
|
|
$nota_adicional = trim((string)($pedido['descargo'] ?? ''));
|
|
}
|
|
$nota_adicional_display = $nota_adicional !== '' ? $nota_adicional : '';
|
|
|
|
$cantidad_total = parseTotalQuantity($pedido['cantidad'] ?? 0);
|
|
$total = (float)($pedido['monto_total'] ?? 0);
|
|
$unit_price_rounded = $cantidad_total > 0 ? round($total / $cantidad_total, 2) : 0;
|
|
|
|
$eanOut = '';
|
|
$cantidadOut = $cantidad_total;
|
|
$precioOut = $unit_price_rounded;
|
|
|
|
$details = extractProductDetailsWithQuantities($pedido);
|
|
|
|
$eanSegments = [];
|
|
$qtySegments = [];
|
|
$priceSegments = [];
|
|
|
|
if (!empty($details)) {
|
|
$names = [];
|
|
foreach ($details as $d) {
|
|
if (!empty($d['name'])) {
|
|
$names[] = (string)$d['name'];
|
|
}
|
|
}
|
|
|
|
$eanMap = getEanMap($pdo, $names);
|
|
|
|
foreach ($details as $d) {
|
|
$name = (string)($d['name'] ?? '');
|
|
if ($name === '') {
|
|
continue;
|
|
}
|
|
|
|
$qty = (int)($d['qty'] ?? 0);
|
|
if ($qty <= 0) {
|
|
$qty = 1;
|
|
}
|
|
|
|
$ean = trim((string)($eanMap[$name] ?? ''));
|
|
if ($ean === '') {
|
|
continue;
|
|
}
|
|
|
|
$eanSegments[] = $ean;
|
|
$qtySegments[] = $qty;
|
|
$priceSegments[] = formatPriceSegment((float)$unit_price_rounded); // unit price repeated
|
|
}
|
|
}
|
|
|
|
// If we have segment data, format with pipes (no spaces)
|
|
if (count($eanSegments) > 1) {
|
|
$eanOut = implode('|', $eanSegments);
|
|
$cantidadOut = implode('|', $qtySegments);
|
|
$precioOut = implode('|', $priceSegments);
|
|
} elseif (count($eanSegments) === 1) {
|
|
$eanOut = $eanSegments[0];
|
|
$cantidadOut = $qtySegments[0] ?? $cantidad_total;
|
|
$precioOut = $unit_price_rounded;
|
|
} else {
|
|
// Fallback (keep old behavior, but with "|" formatting)
|
|
$eanOut = getEansForProducts($pdo, extractProductNames($pedido));
|
|
$cantidadOut = $cantidad_total;
|
|
$precioOut = $cantidad_total > 0 ? round($total / $cantidad_total, 2) : 0;
|
|
}
|
|
|
|
$rows[] = [
|
|
(string)($pedido['nombre_completo'] ?? ''),
|
|
(string)($pedido['celular'] ?? ''),
|
|
'Perú',
|
|
(string)($pedido['sede_envio'] ?? ''),
|
|
$provincia,
|
|
$distrito,
|
|
(string)($pedido['direccion_exacta'] ?? ''),
|
|
(string)($pedido['referencia_domicilio'] ?? ''),
|
|
(string)($pedido['coordenadas'] ?? ''),
|
|
(string)$eanOut,
|
|
$cantidadOut,
|
|
$precioOut,
|
|
$total,
|
|
$nota_adicional_display
|
|
];
|
|
}
|
|
|
|
// Right-align certain columns (Codigo EAN, Cantidad, Precio, Total) in the exported Excel
|
|
$rightAlignedCols = [9, 10, 11, 12];
|
|
foreach ($rows as $rIdx => &$row) {
|
|
foreach ($rightAlignedCols as $cIdx) {
|
|
if (!array_key_exists($cIdx, $row)) {
|
|
continue;
|
|
}
|
|
if (is_string($row[$cIdx]) && strpos($row[$cIdx], '<right>') !== false) {
|
|
continue;
|
|
}
|
|
$row[$cIdx] = '<right>' . (string)$row[$cIdx] . '</right>';
|
|
}
|
|
}
|
|
unset($row);
|
|
$filename = 'ruta_contraentrega_' . date('Y-m-d_H-i') . '.xlsx';
|
|
SimpleXLSXGen::fromArray($rows, 'Ruta Contraentrega')->downloadAs($filename);
|
|
exit;
|
|
} catch (Throwable $e) {
|
|
error_log('Error exportando Ruta Contraentrega: ' . $e->getMessage());
|
|
header('HTTP/1.1 500 Internal Server Error');
|
|
echo 'Error al generar el Excel de Ruta Contraentrega.';
|
|
}
|