401 lines
12 KiB
PHP
401 lines
12 KiB
PHP
<?php
|
|
session_start();
|
|
if (!isset($_SESSION['user_id'])) {
|
|
header('Location: login.php');
|
|
exit;
|
|
}
|
|
|
|
require_once 'db/config.php';
|
|
require_once 'includes/callcenter_test_helpers.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 countryToInitials(string $value): string
|
|
{
|
|
$value = trim($value);
|
|
if ($value === '') {
|
|
return '';
|
|
}
|
|
|
|
// Normalize accents to ASCII (e.g. 'Perú' -> 'Peru')
|
|
$ascii = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
|
|
if ($ascii === false || $ascii === null) {
|
|
$ascii = $value;
|
|
}
|
|
|
|
$ascii = (string)$ascii;
|
|
$ascii = preg_replace('/[^A-Za-z]/', '', $ascii);
|
|
$initials = substr($ascii, 0, 3);
|
|
return strtoupper($initials);
|
|
}
|
|
|
|
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(PDO $pdo, array $pedido): array
|
|
{
|
|
$rows = cc_test_resolve_pedido_product_rows($pdo, $pedido, 'ruta_contraentrega_pedido_id');
|
|
|
|
$out = [];
|
|
foreach ($rows as $row) {
|
|
$name = trim((string)($row['nombre'] ?? ''));
|
|
$qty = (int)($row['cantidad'] ?? 0);
|
|
if ($name === '') {
|
|
continue;
|
|
}
|
|
$out[] = ['name' => $name, 'qty' => $qty > 0 ? $qty : 1];
|
|
}
|
|
|
|
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';
|
|
$is_admin = in_array($user_role, ['Administrador', 'admin'], true);
|
|
|
|
$search_query = trim($_GET['q'] ?? '');
|
|
$selected_delivery_date = trim((string)($_GET['fecha_entrega'] ?? ''));
|
|
if ($selected_delivery_date !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $selected_delivery_date)) {
|
|
$selected_delivery_date = '';
|
|
}
|
|
if ($selected_delivery_date !== '') {
|
|
[$deliveryYear, $deliveryMonth, $deliveryDay] = array_map('intval', explode('-', $selected_delivery_date));
|
|
if (!checkdate($deliveryMonth, $deliveryDay, $deliveryYear)) {
|
|
$selected_delivery_date = '';
|
|
}
|
|
}
|
|
|
|
if (!$is_admin) {
|
|
http_response_code(403);
|
|
exit('Acceso denegado.');
|
|
}
|
|
|
|
// Exportar pedidos con fecha de entrega seleccionada; si no hay filtro, por defecto mañana
|
|
$sql = "SELECT p.* FROM pedidos p WHERE p.estado IN ('RUTA_CONTRAENTREGA', '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%";
|
|
}
|
|
|
|
if ($selected_delivery_date !== '') {
|
|
$sql .= " AND DATE(p.fecha_entrega) = ?";
|
|
$params[] = $selected_delivery_date;
|
|
} else {
|
|
// Siempre: pedidos cuya fecha de entrega sea mañana
|
|
$sql .= " AND DATE(p.fecha_entrega) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
|
|
}
|
|
|
|
$sql .= " ORDER BY p.created_at DESC";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute($params);
|
|
$pedidos = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$courier_default = 1;
|
|
$fecha_despacho = date('d/m/Y');
|
|
|
|
$rows = [];
|
|
$rows[] = [
|
|
'Nombre y apellido',
|
|
'Celular',
|
|
'Pais',
|
|
'Departamento',
|
|
'Provincia',
|
|
'Distrito',
|
|
'Direccion',
|
|
'Referencia',
|
|
'Coordenadas',
|
|
'Codigo EAN',
|
|
'Cantidad',
|
|
'Precio',
|
|
'Total',
|
|
'COURIER',
|
|
'DE DEDICATORIA / OBS.',
|
|
'F.DESPACHO'
|
|
];
|
|
|
|
foreach ($pedidos as $pedido) {
|
|
[$provincia, $distrito] = splitProvinciaDistrito($pedido['codigo_rastreo'] ?? '');
|
|
|
|
$pais_raw = (string)($pedido['pais'] ?? 'Perú');
|
|
$pais_code = countryToInitials($pais_raw);
|
|
|
|
$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($pdo, $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'] ?? ''),
|
|
$pais_code,
|
|
(string)($pedido['sede_envio'] ?? ''),
|
|
$provincia,
|
|
$distrito,
|
|
(string)($pedido['direccion_exacta'] ?? ''),
|
|
(string)($pedido['referencia_domicilio'] ?? ''),
|
|
(string)($pedido['coordenadas'] ?? ''),
|
|
(string)$eanOut,
|
|
$cantidadOut,
|
|
$precioOut,
|
|
$total,
|
|
$courier_default,
|
|
$nota_adicional_display,
|
|
$fecha_despacho
|
|
];
|
|
}
|
|
|
|
// 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);
|
|
if ($selected_delivery_date !== '') {
|
|
$filename = 'ruta_contraentrega_' . $selected_delivery_date . '.xlsx';
|
|
} else {
|
|
$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.';
|
|
}
|