En muchos departamentos de IT el buzón de soporte sigue siendo la puerta de entrada más utilizada por los usuarios: envían un correo a IT, algún técnico lo lee y lo copia a mano en la herramienta de tickets. Ese paso manual es lento, propenso a errores y no escalable. En este artículo vamos a automatizarlo por completo: un proyecto de Google Apps Script que se ejecuta dentro de la propia cuenta de Google del buzón y una pequeña API REST en PHP, como enlace con la base de datos MySQL del help desk. Cuando se envíe un email a una cuenta de soporte concreta, se generará ticket automáticamente.
Índice
- 1. Arquitectura de la solución.
- 2. Requisitos.
- 3. Preparación de la base de datos.
- 4. Generación y custodia de la clave de la API.
- 5. La API REST en PHP.
- 6. Publicación de la API en el servidor.
- 7. Creación del proyecto de Google Apps Script.
- 8. El código de Apps Script: Code.gs.
- 9. Propiedades del script.
- 10. Primera ejecución y autorización.
- 11. Instalación del activador.
- 12. Etiquetas, marcado como leído y archivado.
- 13. Escenarios de prueba.
- 14. Resolución de problemas.
- 15. Consideraciones de seguridad.
- 16. Conclusión.
1. Arquitectura de la solución
El diseño separa responsabilidades en tres piezas desacopladas. El script de Google no tiene credenciales de base de datos y la API no tiene acceso al buzón: cada pieza tiene el mínimo privilegio necesario.

El estado de los emails recibidos se gestiona por mensaje, no por hilo. Si un usuario responde en un hilo que ya generó un ticket, la respuesta nueva se procesa (normalmente como comentario) aunque el hilo ya tenga etiqueta. Las etiquetas son solo la vista del buzón; la comprobación se realiza con el registro de identificadores de Gmail que el script guarda en sus Propiedades.
2. Requisitos
- Una cuenta de Google (Google Workspace o Gmail) para el buzón de soporte, en nuestro caso
bas@ajpdsoft.com, con Apps Script permitido por la consola de administración y llamadas a URL externas autorizadas. - Un balanceador nginx con HTTPS (Let’s Encrypt) y uno o más nodos con PHP 8.0 o superior (validado con PHP 8.5) y las extensiones
pdo_mysql,mbstringyjson. - MySQL 5.7+/8.x o MariaDB 10.3+ con la base de datos de ProyectoA Help Desk (tablas
incidencia,tercero,contacto,tecnico,usuarioyparametro). - Un usuario de base de datos para la API con permisos
SELECT,INSERTyUPDATEsobre esas tablas (no necesitaDELETEniDROP). - OpenSSL (o PHP CLI) en un equipo de administración para generar la clave.
- Acceso de administración a la base de datos para ejecutar el script SQL de instalación.
- Registro DNS A para
apireproyectoa.ajpdsoft.comy NAT de los puertos 80/443 del firewall perimetral hacia el balanceador nginx.
3. Preparación de la base de datos
El script del apartado 3.9 crea el esquema completo de un proyecto nuevo (tablas, parámetros del servicio y usuario técnico api). Conviene revisar cada tabla antes de ejecutarlo.
3.1. Tabla incidencia
La tabla incidencia almacena los tickets (incidencias, solicitudes, tareas). La API escribe en las columnas listadas a continuación; el resto de campos del esquema completo de ProyectoA Help Desk pueden coexistir si la instalación ya los tiene.
CREATE TABLE IF NOT EXISTS incidencia (
codigo INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
fecha DATETIME DEFAULT NULL,
codigocliente INT(10) UNSIGNED DEFAULT NULL,
importecoste FLOAT(19,4) DEFAULT NULL,
importecliente FLOAT(19,4) DEFAULT NULL,
asunto VARCHAR(255) DEFAULT NULL,
incidencia TEXT,
incidenciaresolucion TEXT,
fecharesolucion DATETIME DEFAULT NULL,
estado VARCHAR(30) DEFAULT NULL,
tipo VARCHAR(30) DEFAULT NULL,
codigotecnico INT(10) UNSIGNED DEFAULT NULL,
prioridad VARCHAR(10) DEFAULT NULL,
contacto VARCHAR(100) DEFAULT NULL,
completado INT(10) UNSIGNED DEFAULT NULL,
fechavencimiento DATETIME DEFAULT NULL,
codigorecurso INT(10) UNSIGNED DEFAULT NULL,
codigocontacto INT(10) UNSIGNED DEFAULT NULL,
codusuarioa INT(10) UNSIGNED DEFAULT NULL,
codusuariom INT(10) UNSIGNED DEFAULT NULL,
fechaa DATETIME DEFAULT NULL,
fecham DATETIME DEFAULT NULL,
aceptada CHAR(1) DEFAULT NULL,
resuelta VARCHAR(1) DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_incidencia_fechaa (fechaa),
KEY idx_incidencia_cliente (codigocliente),
KEY idx_incidencia_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Tickets / incidencias (ProyectoA Help Desk)';
3.2. Tabla incidencia_comentario
Guarda el ticket al que pertenece (codigoincidencia), la fecha, el texto completo (incluida la línea Message-ID, usada para deduplicar), el remitente, el origen y los campos de auditoría habituales (codusuarioa, fechaa, etc.). Se crea en InnoDB con índices por codigoincidencia y por fechaa.
CREATE TABLE IF NOT EXISTS incidencia_comentario (
codigo INT(11) NOT NULL AUTO_INCREMENT,
codigoincidencia INT(11) NOT NULL,
fecha DATETIME NOT NULL,
comentario TEXT NOT NULL,
remitente VARCHAR(200) DEFAULT NULL,
remitentenombre VARCHAR(200) DEFAULT NULL,
origen VARCHAR(30) NOT NULL DEFAULT 'email',
codigocontacto INT(11) DEFAULT NULL,
codusuarioa INT(11) DEFAULT NULL,
codusuariom INT(11) DEFAULT NULL,
fechaa DATETIME DEFAULT NULL,
fecham DATETIME DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_inccom_incidencia (codigoincidencia),
KEY idx_inccom_remitente_fecha (remitente, fechaa),
KEY idx_inccom_fechaa (fechaa)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Comentarios de tickets (ProyectoA Help Desk)';
3.3. Tabla usuario
Contiene las cuentas de la aplicación. Para la API son relevantes nick, token (SHA-256 hexadecimal de la clave), token_expiracion, activo y accesoweb. El usuario técnico api se crea en el apartado 3.8.
CREATE TABLE IF NOT EXISTS usuario (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nick VARCHAR(20) NOT NULL,
contrasena VARCHAR(100) DEFAULT NULL,
codigocliente INT(11) DEFAULT NULL,
codigotecnico INT(11) DEFAULT NULL,
administrador CHAR(1) DEFAULT 'N',
modificacion CHAR(1) DEFAULT 'N',
nombre VARCHAR(100) DEFAULT NULL,
fecha DATETIME DEFAULT NULL,
accesoweb CHAR(1) DEFAULT 'N',
email VARCHAR(200) DEFAULT NULL,
activo VARCHAR(1) DEFAULT 'S',
insercion CHAR(1) DEFAULT 'N',
eliminacion CHAR(1) DEFAULT 'N',
lectura CHAR(1) DEFAULT 'N',
tipo VARCHAR(30) DEFAULT NULL,
modo VARCHAR(50) DEFAULT NULL,
token VARCHAR(100) DEFAULT NULL,
token_expiracion DATETIME DEFAULT NULL,
codusuarioa INT(11) DEFAULT NULL,
codusuariom INT(11) DEFAULT NULL,
fechaa DATETIME DEFAULT NULL,
fecham DATETIME DEFAULT NULL,
PRIMARY KEY (codigo),
UNIQUE KEY uk_usuario_nick (nick)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Usuarios de la aplicación';
3.4. Tabla tecnico
Catálogo de técnicos responsables. El parámetro helpdesk_email_codigotecnico debe apuntar a un tecnico.codigo existente (o dejarse vacío para no asignar técnico).
CREATE TABLE IF NOT EXISTS tecnico (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nombre VARCHAR(100) DEFAULT NULL,
telefono VARCHAR(50) DEFAULT NULL,
email1 VARCHAR(200) DEFAULT NULL,
email2 VARCHAR(200) DEFAULT NULL,
email3 VARCHAR(200) DEFAULT NULL,
activo VARCHAR(1) DEFAULT 'S',
PRIMARY KEY (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Técnicos responsables';
3.5. Tabla tercero
Clientes y empresas. La resolución del remitente busca primero en tercero.email (admite varias direcciones separadas por coma o punto y coma). El parámetro helpdesk_email_codigocliente indica el tercero por defecto cuando el remitente no figura en tercero ni en contacto.
CREATE TABLE IF NOT EXISTS tercero (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nombre VARCHAR(200) DEFAULT NULL,
cif VARCHAR(20) DEFAULT NULL,
email VARCHAR(500) DEFAULT NULL,
personacontacto VARCHAR(100) DEFAULT NULL,
telefono VARCHAR(50) DEFAULT NULL,
activo VARCHAR(1) DEFAULT 'S',
codigotecnico INT(11) DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_tercero_email (email(191))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Terceros / clientes';
3.6. Tabla contacto
Personas de contacto asociadas a un tercero. La API consulta las ocho columnas de correo (emailempresa1..4 y emailparticular1..4) y, si encuentra coincidencia, toma codigocontacto y, en su caso, codigotercero como cliente.
CREATE TABLE IF NOT EXISTS contacto (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nombre VARCHAR(100) DEFAULT NULL,
emailparticular1 VARCHAR(100) DEFAULT NULL,
emailparticular2 VARCHAR(100) DEFAULT NULL,
emailparticular3 VARCHAR(100) DEFAULT NULL,
emailparticular4 VARCHAR(100) DEFAULT NULL,
emailempresa1 VARCHAR(100) DEFAULT NULL,
emailempresa2 VARCHAR(100) DEFAULT NULL,
emailempresa3 VARCHAR(100) DEFAULT NULL,
emailempresa4 VARCHAR(100) DEFAULT NULL,
telefonoempresa1 VARCHAR(30) DEFAULT NULL,
movilempresa1 VARCHAR(30) DEFAULT NULL,
empresa VARCHAR(100) DEFAULT NULL,
codigotercero INT(11) DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_contacto_tercero (codigotercero)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contactos asociados a terceros';
3.7. Tabla parametro y parámetros del servicio
Toda la configuración funcional vive en la tabla parametro (columna valor), agrupada en la categoría Help Desk email. Así se puede ajustar el comportamiento sin tocar código ni volver a desplegar.
CREATE TABLE IF NOT EXISTS parametro (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nombre VARCHAR(80) NOT NULL,
valor TEXT,
valor2 TEXT,
descripcion TEXT,
categoria VARCHAR(100) DEFAULT NULL,
tipo VARCHAR(10) DEFAULT NULL,
codusuarioa INT(11) DEFAULT NULL,
codusuariom INT(11) DEFAULT NULL,
fechaa DATETIME DEFAULT NULL,
fecham DATETIME DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_parametro_nombre (nombre),
KEY idx_parametro_categoria (categoria)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Parámetros de configuración';
Parámetros del servicio:
| Parámetro (nombre) | Ejemplo | Descripción |
|---|---|---|
helpdesk_email_activo | S | Servicio activo (S/N). Con N la API responde 503 retry:true y el script reintenta sin etiquetar ni archivar. |
helpdesk_email_usuario_api | api | Nick del usuario de la tabla usuario que autentica la API y figura como codusuarioa. |
helpdesk_email_estado | Pendiente | Estado inicial del ticket (incidencia.estado). |
helpdesk_email_tipo | Soporte | Tipo del ticket (incidencia.tipo). Debe coincidir con uno de los tipos que ya maneje la instalación. |
helpdesk_email_prioridad | Media | Prioridad por defecto: Baja, Media o Alta. |
helpdesk_email_codigotecnico | 1 | Técnico responsable por defecto (tecnico.codigo). Vacío = sin asignar. |
helpdesk_email_codigorecurso | (vacío) | Recurso por defecto (incidencia.codigorecurso). Vacío = NULL. |
helpdesk_email_codigocliente | 1 | Cliente (tercero.codigo) cuando el remitente no figura en tercero ni en contacto. |
helpdesk_email_excluidos | avisos@proveedor.com; @boletines.es | Remitentes que no generan nada. Separados por coma, punto y coma o salto de línea; admite dominios con @dominio. |
helpdesk_email_cuenta_soporte | bas@ajpdsoft.com | Cuenta del buzón: sus propios correos no generan ticket. |
helpdesk_email_dias_duplicado | 10 | Días hacia atrás en los que se busca el Message-ID (mayor que la ventana de 7 días del script). |
helpdesk_email_limite_hora | 60 | Tickets + comentarios máximos por remitente y hora (protección ante bucles). 0 = sin límite. |
helpdesk_email_max_cuerpo | 20000 | Caracteres máximos de la descripción y del cuerpo original (entre 1000 y 30000). |
helpdesk_email_dias_vencimiento | 3 | Días que se suman a la fecha de alta para fechavencimiento. 0 = sin vencimiento. |
El INSERT usa INSERT ... SELECT ... WHERE NOT EXISTS para no duplicar parámetros si el script se ejecutar de nuevo. El bloque completo está en el apartado 3.9.
3.8. Usuario técnico «api»
En lugar de un secreto suelto en un fichero, la API se autentica contra un usuario específico de la tabla usuario con nick api. Su columna token (varchar(100)) guarda el SHA-256 en hexadecimal de la clave (64 caracteres), nunca la clave en claro, y token_expiracion puede quedar a NULL (sin caducidad) o con una fecha futura para forzar la rotación. El usuario se crea con accesoweb = 'N' y una contraseña no utilizable, de modo que no puede iniciar sesión en la aplicación; además sirve como autor (codusuarioa) de todos los tickets creados por correo.
SET @api_token_sha256 = 'REEMPLAZAR_POR_EL_HASH_SHA256_DE_64_CARACTERES';
INSERT INTO usuario (nick, contrasena, codigocliente, codigotecnico, administrador,
modificacion, nombre, fecha, accesoweb, email, activo,
insercion, eliminacion, lectura, tipo, modo,
token, token_expiracion, codusuarioa, fechaa)
SELECT 'api', CONCAT('!', SHA2(UUID(), 256)), NULL, NULL, 'N',
'N', 'Usuario API ProyectoA Help Desk (email)', NOW(), 'N', NULL, 'S',
'N', 'N', 'N', 'api', NULL,
NULL, NULL, 1, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM usuario WHERE nick = 'api');
UPDATE usuario
SET token = LOWER(@api_token_sha256),
token_expiracion = NULL, -- o '2027-12-31 23:59:59'
activo = 'S',
fecham = NOW()
WHERE nick = 'api'
AND @api_token_sha256 REGEXP '^[0-9A-Fa-f]{64}$';
El mismo UPDATE sirve para rotar la clave: basta con cambiar el valor de la variable @api_token_sha256. La condición REGEXP impide cargar por error un valor que no sea un hash válido.
3.9. Script SQL completo de instalación
Script idempotente para MySQL / MariaDB (latin1). Crea las siete tablas, inserta un técnico y un tercero por defecto, los catorce parámetros del servicio y el usuario api. Revisar los valores marcados con <-- AJUSTAR y sustituir el hash antes de ejecutarlo con un usuario administrador, por ejemplo mysql -u root -p nombre_bd pegando el contenido siguiente.
-- =====================================================================
-- ProyectoA Help Desk - Alta de tickets desde el buzón bas@ajpdsoft.com
-- Script de instalación de base de datos (MySQL / MariaDB, latin1)
--
-- Crea el esquema mínimo necesario para un proyecto nuevo e inserta
-- los parámetros del servicio y el usuario técnico "api".
--
-- El script es idempotente: puede ejecutarse varias veces sin duplicar
-- parámetros ni usuarios. Revisar los valores marcados con <-- AJUSTAR.
-- =====================================================================
SET NAMES utf8mb4;
-- ---------------------------------------------------------------------
-- 1) Tabla incidencia (tickets)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS incidencia (
codigo INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
fecha DATETIME DEFAULT NULL,
codigocliente INT(10) UNSIGNED DEFAULT NULL,
importecoste FLOAT(19,4) DEFAULT NULL,
importecliente FLOAT(19,4) DEFAULT NULL,
asunto VARCHAR(255) DEFAULT NULL,
incidencia TEXT,
incidenciaresolucion TEXT,
fecharesolucion DATETIME DEFAULT NULL,
estado VARCHAR(30) DEFAULT NULL,
tipo VARCHAR(30) DEFAULT NULL,
codigotecnico INT(10) UNSIGNED DEFAULT NULL,
prioridad VARCHAR(10) DEFAULT NULL,
contacto VARCHAR(100) DEFAULT NULL,
completado INT(10) UNSIGNED DEFAULT NULL,
fechavencimiento DATETIME DEFAULT NULL,
codigorecurso INT(10) UNSIGNED DEFAULT NULL,
codigocontacto INT(10) UNSIGNED DEFAULT NULL,
codusuarioa INT(10) UNSIGNED DEFAULT NULL,
codusuariom INT(10) UNSIGNED DEFAULT NULL,
fechaa DATETIME DEFAULT NULL,
fecham DATETIME DEFAULT NULL,
aceptada CHAR(1) DEFAULT NULL,
resuelta VARCHAR(1) DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_incidencia_fechaa (fechaa),
KEY idx_incidencia_cliente (codigocliente),
KEY idx_incidencia_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Tickets / incidencias (ProyectoA Help Desk)';
-- ---------------------------------------------------------------------
-- 2) Tabla incidencia_comentario
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS incidencia_comentario (
codigo INT(11) NOT NULL AUTO_INCREMENT,
codigoincidencia INT(11) NOT NULL,
fecha DATETIME NOT NULL,
comentario TEXT NOT NULL,
remitente VARCHAR(200) DEFAULT NULL,
remitentenombre VARCHAR(200) DEFAULT NULL,
origen VARCHAR(30) NOT NULL DEFAULT 'email',
codigocontacto INT(11) DEFAULT NULL,
codusuarioa INT(11) DEFAULT NULL,
codusuariom INT(11) DEFAULT NULL,
fechaa DATETIME DEFAULT NULL,
fecham DATETIME DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_inccom_incidencia (codigoincidencia),
KEY idx_inccom_remitente_fecha (remitente, fechaa),
KEY idx_inccom_fechaa (fechaa)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Comentarios de tickets (ProyectoA Help Desk)';
-- ---------------------------------------------------------------------
-- 3) Tabla usuario
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuario (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nick VARCHAR(20) NOT NULL,
contrasena VARCHAR(100) DEFAULT NULL,
codigocliente INT(11) DEFAULT NULL,
codigotecnico INT(11) DEFAULT NULL,
administrador CHAR(1) DEFAULT 'N',
modificacion CHAR(1) DEFAULT 'N',
nombre VARCHAR(100) DEFAULT NULL,
fecha DATETIME DEFAULT NULL,
accesoweb CHAR(1) DEFAULT 'N',
email VARCHAR(200) DEFAULT NULL,
activo VARCHAR(1) DEFAULT 'S',
insercion CHAR(1) DEFAULT 'N',
eliminacion CHAR(1) DEFAULT 'N',
lectura CHAR(1) DEFAULT 'N',
tipo VARCHAR(30) DEFAULT NULL,
modo VARCHAR(50) DEFAULT NULL,
token VARCHAR(100) DEFAULT NULL,
token_expiracion DATETIME DEFAULT NULL,
codusuarioa INT(11) DEFAULT NULL,
codusuariom INT(11) DEFAULT NULL,
fechaa DATETIME DEFAULT NULL,
fecham DATETIME DEFAULT NULL,
PRIMARY KEY (codigo),
UNIQUE KEY uk_usuario_nick (nick)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Usuarios de la aplicación';
-- ---------------------------------------------------------------------
-- 4) Tabla tecnico
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tecnico (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nombre VARCHAR(100) DEFAULT NULL,
telefono VARCHAR(50) DEFAULT NULL,
email1 VARCHAR(200) DEFAULT NULL,
email2 VARCHAR(200) DEFAULT NULL,
email3 VARCHAR(200) DEFAULT NULL,
activo VARCHAR(1) DEFAULT 'S',
PRIMARY KEY (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Técnicos responsables';
-- ---------------------------------------------------------------------
-- 5) Tabla tercero (clientes / empresas)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tercero (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nombre VARCHAR(200) DEFAULT NULL,
cif VARCHAR(20) DEFAULT NULL,
email VARCHAR(500) DEFAULT NULL,
personacontacto VARCHAR(100) DEFAULT NULL,
telefono VARCHAR(50) DEFAULT NULL,
activo VARCHAR(1) DEFAULT 'S',
codigotecnico INT(11) DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_tercero_email (email(191))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Terceros / clientes';
-- ---------------------------------------------------------------------
-- 6) Tabla contacto
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contacto (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nombre VARCHAR(100) DEFAULT NULL,
emailparticular1 VARCHAR(100) DEFAULT NULL,
emailparticular2 VARCHAR(100) DEFAULT NULL,
emailparticular3 VARCHAR(100) DEFAULT NULL,
emailparticular4 VARCHAR(100) DEFAULT NULL,
emailempresa1 VARCHAR(100) DEFAULT NULL,
emailempresa2 VARCHAR(100) DEFAULT NULL,
emailempresa3 VARCHAR(100) DEFAULT NULL,
emailempresa4 VARCHAR(100) DEFAULT NULL,
telefonoempresa1 VARCHAR(30) DEFAULT NULL,
movilempresa1 VARCHAR(30) DEFAULT NULL,
empresa VARCHAR(100) DEFAULT NULL,
codigotercero INT(11) DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_contacto_tercero (codigotercero)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contactos asociados a terceros';
-- ---------------------------------------------------------------------
-- 7) Tabla parametro
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS parametro (
codigo INT(11) NOT NULL AUTO_INCREMENT,
nombre VARCHAR(80) NOT NULL,
valor TEXT,
valor2 TEXT,
descripcion TEXT,
categoria VARCHAR(100) DEFAULT NULL,
tipo VARCHAR(10) DEFAULT NULL,
codusuarioa INT(11) DEFAULT NULL,
codusuariom INT(11) DEFAULT NULL,
fechaa DATETIME DEFAULT NULL,
fecham DATETIME DEFAULT NULL,
PRIMARY KEY (codigo),
KEY idx_parametro_nombre (nombre),
KEY idx_parametro_categoria (categoria)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Parámetros de configuración';
-- Datos mínimos para que la API pueda validar la configuración
INSERT INTO tecnico (codigo, nombre, activo)
SELECT 1, 'Técnico por defecto', 'S'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM tecnico WHERE codigo = 1);
INSERT INTO tercero (codigo, nombre, email, activo)
SELECT 1, 'Cliente genérico', '', 'S'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM tercero WHERE codigo = 1);
INSERT INTO usuario (codigo, nick, contrasena, administrador, modificacion, nombre, fecha,
accesoweb, activo, insercion, eliminacion, lectura, tipo, codusuarioa, fechaa)
SELECT 1, 'admin', CONCAT('!', SHA2(UUID(), 256)), 'S', 'S', 'Administrador', NOW(),
'S', 'S', 'S', 'S', 'S', 'admin', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM usuario WHERE nick = 'admin');
-- ---------------------------------------------------------------------
-- 8) Parámetros del servicio Help Desk email
-- ---------------------------------------------------------------------
INSERT INTO parametro (nombre, valor, valor2, descripcion, categoria, tipo, codusuarioa, fechaa)
SELECT * FROM (
SELECT 'helpdesk_email_activo' AS nombre, 'S' AS valor, '' AS valor2,
'Servicio de alta de tickets por email activo (S/N). Con N la API responde 503 retry:true y el script reintenta sin etiquetar.' AS descripcion,
'Help Desk email' AS categoria, 'texto' AS tipo, 1 AS codusuarioa, NOW() AS fechaa
UNION ALL SELECT 'helpdesk_email_usuario_api', 'api', '',
'Nick del usuario (tabla usuario) que autentica la API: su columna token guarda el SHA-256 hexadecimal de la clave X-Api-Key y figura como codusuarioa de los tickets.',
'Help Desk email', 'texto', 1, NOW()
UNION ALL SELECT 'helpdesk_email_estado', 'Pendiente', '',
'Estado inicial (incidencia.estado, máx. 30 caracteres) de los tickets creados por email.',
'Help Desk email', 'texto', 1, NOW()
UNION ALL SELECT 'helpdesk_email_tipo', 'Soporte', '',
'Tipo (incidencia.tipo, máx. 30 caracteres) de los tickets creados por email.',
'Help Desk email', 'texto', 1, NOW()
UNION ALL SELECT 'helpdesk_email_prioridad', 'Media', '',
'Prioridad por defecto (incidencia.prioridad): Baja, Media o Alta.',
'Help Desk email', 'texto', 1, NOW()
UNION ALL SELECT 'helpdesk_email_codigotecnico', '1', '', -- <-- AJUSTAR
'Código del técnico responsable por defecto (tecnico.codigo). Vacío = sin asignar. Si no existe en tecnico, la API responde error de configuración.',
'Help Desk email', 'numero', 1, NOW()
UNION ALL SELECT 'helpdesk_email_codigorecurso', '', '',
'Código de recurso por defecto (incidencia.codigorecurso). Vacío = NULL.',
'Help Desk email', 'numero', 1, NOW()
UNION ALL SELECT 'helpdesk_email_codigocliente', '1', '', -- <-- AJUSTAR
'Código de cliente (tercero.codigo) que se asigna cuando el remitente no figura en tercero ni en contacto.',
'Help Desk email', 'numero', 1, NOW()
UNION ALL SELECT 'helpdesk_email_excluidos', '', '',
'Remitentes que no generan nada (etiqueta proyectoa-no-procesado). Separados por coma, punto y coma o salto de línea. Admite dominios completos con el formato @dominio.com.',
'Help Desk email', 'texto', 1, NOW()
UNION ALL SELECT 'helpdesk_email_cuenta_soporte', 'bas@ajpdsoft.com', '',
'Cuenta de correo del buzón de soporte. Los correos enviados por ella misma no generan ticket.',
'Help Desk email', 'texto', 1, NOW()
UNION ALL SELECT 'helpdesk_email_dias_duplicado', '10', '',
'Días hacia atrás (por fechaa) en los que se busca el Message-ID para detectar duplicados. Debe ser mayor que la ventana de búsqueda del script (7 días).',
'Help Desk email', 'numero', 1, NOW()
UNION ALL SELECT 'helpdesk_email_limite_hora', '60', '',
'Máximo de tickets + comentarios por remitente y hora (protección frente a bucles de correo). 0 = sin límite.',
'Help Desk email', 'numero', 1, NOW()
UNION ALL SELECT 'helpdesk_email_max_cuerpo', '20000', '',
'Máximo de caracteres que se guardan de la descripción y del cuerpo original (entre 1000 y 30000; la columna TEXT admite 65535 bytes).',
'Help Desk email', 'numero', 1, NOW()
UNION ALL SELECT 'helpdesk_email_dias_vencimiento', '3', '',
'Días que se suman a la fecha de alta para calcular incidencia.fechavencimiento. 0 = sin vencimiento.',
'Help Desk email', 'numero', 1, NOW()
) AS nuevos
WHERE NOT EXISTS (SELECT 1 FROM parametro p WHERE p.nombre = nuevos.nombre);
-- ---------------------------------------------------------------------
-- 9) Usuario técnico "api"
-- ---------------------------------------------------------------------
SET @api_token_sha256 = 'REEMPLAZAR_POR_EL_HASH_SHA256_DE_64_CARACTERES'; -- <-- AJUSTAR
INSERT INTO usuario (nick, contrasena, codigocliente, codigotecnico, administrador,
modificacion, nombre, fecha, accesoweb, email, activo,
insercion, eliminacion, lectura, tipo, modo,
token, token_expiracion, codusuarioa, fechaa)
SELECT 'api', CONCAT('!', SHA2(UUID(), 256)), NULL, NULL, 'N',
'N', 'Usuario API ProyectoA Help Desk (email)', NOW(), 'N', NULL, 'S',
'N', 'N', 'N', 'api', NULL,
NULL, NULL, 1, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM usuario WHERE nick = 'api');
UPDATE usuario
SET token = LOWER(@api_token_sha256),
token_expiracion = NULL,
activo = 'S',
fecham = NOW()
WHERE nick = 'api'
AND @api_token_sha256 REGEXP '^[0-9A-Fa-f]{64}$';
SELECT codigo, nick, activo, accesoweb, LENGTH(token) AS longitud_token, token_expiracion
FROM usuario WHERE nick = 'api';
SELECT nombre, valor FROM parametro WHERE categoria = 'Help Desk email' ORDER BY nombre;
Advertencia: No conviene dejar el hash de ejemplo en producción. Generar la clave y su SHA-256 según el apartado 4 y pegar solo el hash en
@api_token_sha256.
4. Generación y custodia de la clave de la API
La clave es un valor aleatorio de 32 bytes expresado en 64 caracteres hexadecimales. Se genera en un equipo de administración, se guarda una sola vez en el gestor de contraseñas del departamento y se reparte así: la clave en claro solo en las Propiedades del script de Apps Script (API_KEY) y su hash SHA-256 en usuario.token.
# 1) Clave aleatoria de 32 bytes = 64 caracteres hexadecimales
KEY=$(openssl rand -hex 32)
echo "$KEY" # cópiala UNA vez al gestor de contraseñas
# 2) Hash SHA-256 de la clave (printf, NO echo: echo añade un salto de línea)
printf '%s' "$KEY" | sha256sum | cut -d' ' -f1
# Alternativa solo con PHP (genera la clave y su hash):
php -r '$k = bin2hex(random_bytes(32)); echo $k, PHP_EOL, hash("sha256", $k), PHP_EOL;'
# 3) Al terminar, borra la variable y el historial de la sesión
unset KEY; history -c
Advertencia: calcular el hash con
echo "$KEY" | sha256sumañade un salto de línea y cambia el resultado: la API responderá siempre 401. Tampoco conviene usarSHA2('clave', 256)en MySQL: la clave en claro quedaría en el historial del cliente y, según la configuración, en los registros del servidor.
5. La API REST en PHP
5.1. Estructura de ficheros
Solo un fichero, api.php, queda dentro de la raíz web. El resto (configuración y librerías) va en un directorio privado que el servidor web no sirve:
/var/www/apireproyectoa/
├── public/ ← raíz web (root de nginx)
│ └── api.php ← https://apireproyectoa.ajpdsoft.com/api.php
└── private/
└── helpdesk/ ← fuera de la raíz web
├── config.php ← credenciales de BD (0640)
├── util.php ← respuestas JSON, saneado, charset, IP
├── db.php ← conexión PDO y lectura de parámetros
├── auth.php ← autenticación X-Api-Key
└── helpdesk.php ← lógica de tickets y comentarios
api.php localiza el directorio privado con la variable de entorno PROYECTOA_HELPDESK_LIB o, si no existe, con dirname(__DIR__) . '/private/helpdesk'.
5.2. Contrato de la API
Todas las peticiones son POST con cuerpo JSON (salvo ping, que admite también GET) y la cabecera X-Api-Key. Acciones disponibles:
ping: comprueba la clave y devuelve si el servicio está activo. No crea nada.procesar_email: la que usa el script. Si el asunto (o, en su defecto, el cuerpo) contiene[TICKET-n]y eseincidencia.codigoexiste, añade un comentario; si no, crea un ticket nuevo.crear_ticket: fuerza el alta de un ticket aunque haya referencia.insertar_comentario: añade un comentario al ticket indicado en el campoticket(404 si no existe).
Ejemplo de cuerpo que envía Apps Script:
{
"action": "procesar_email",
"remitente": "usuario@cliente.es",
"remitente_nombre": "Nombre Apellidos",
"reply_to": "",
"para": "bas@ajpdsoft.com",
"cc": "",
"fecha_correo": "2026-11-02 08:58:44",
"message_id": "CAJ9An6n...@mail.gmail.com",
"asunto": "No puedo acceder al ERP",
"descripcion": "Cuerpo sin firma ni citas",
"cuerpo_original": "Cuerpo completo del correo",
"firma": "Bloque de firma separado"
}
Y estas son las respuestas, que el script interpreta para decidir si el correo queda tratado o se reintenta:
| HTTP | Cuerpo JSON | Significado | Acción en Apps Script |
|---|---|---|---|
| 201 | {ok:true, ticket:1184, comentario:false, duplicado:false} | Ticket creado | Etiqueta proyectoa-procesado, leído, archivar |
| 201 | {ok:true, ticket:1184, comentario:true, codigo_comentario:7, duplicado:false} | Comentario añadido | Etiqueta proyectoa-comentario, leído, archivar |
| 200 | {ok:true, ticket:1184, comentario:bool, duplicado:true} | Message-ID ya procesado | Como si fuese nuevo (procesado o comentario) |
| 200 | {ok:false, skipped:true, reason:"remitente_excluido"} o "cuenta_propia" | No se genera nada | Etiqueta proyectoa-no-procesado, leído, archivar |
| 400 / 404 / 413 | {ok:false, message} | Datos no válidos | proyectoa-no-procesado + aviso por correo |
| 401 / 403 | {ok:false, message} | Clave incorrecta / usuario api inactivo o caducado | Sin etiqueta, reintento, aviso único, detener ciclo |
| 429 | {ok:false, reason:"bloqueo_ip"} | Demasiados fallos de clave desde la IP | Sin etiqueta, aviso único, detener ciclo |
| 429 | {ok:false, reason:"limite_remitente"} | Límite por hora de ese remitente | Solo se aplaza ese mensaje; aviso único |
| 503 | {ok:false, retry:true, reason:"configuracion", causa} | Servicio inactivo o parámetros incompletos | Sin etiqueta, reintento, aviso único, detener ciclo |
| 503 | {ok:false, retry:true, reason:"bd"} / "ocupado" | BD caída o mismo correo en curso | Sin etiqueta, reintento |
| 500 | {ok:false, retry:true} | Error al insertar | Reintento; aviso al tercer fallo del mismo mensaje |
5.3. El problema del juego de caracteres: PHP en UTF-8 y MySQL en latin1
Gmail entrega texto Unicode (UTF-8) y la base de datos de ProyectoA Help Desk es latin1, que en MySQL equivale en la práctica a Windows-1252 (incluye €, comillas tipográficas y el guion largo). Hay dos formas correctas de conectarse y la API admite ambas con el parámetro charset de config.php:
utf8mb4(recomendado): la conexión PDO es UTF-8 (charset=utf8mb4en el DSN) y MySQL convierte automáticamente al escribir en columnaslatin1y al leer de ellas. El matiz importante es que un carácter sin equivalente en latin1, como un emoji, provoca el error 1366 «Incorrect string value» si el servidor está en modo estricto (STRICT_TRANS_TABLES). Por esohd_a_bd()sustituye antes esos caracteres por?, pasando el texto por Windows-1252 y de vuelta a UTF-8.latin1: para instalaciones heredadas en las que la aplicación principal ya conecta en latin1. La conexión escharset=latin1y la API convierte cada texto de UTF-8 a Windows-1252 antes de enviarlo (hd_a_bd()) y de Windows-1252 a UTF-8 al leerlo (hd_desde_bd()).
No conviene mezclar ambos criterios: si la aplicación existente guarda «UTF-8 dentro de latin1» (doble codificación, típico de conexiones antiguas sin SET NAMES), los acentos de los tickets nuevos se verían distintos a los antiguos. Compruébalo con SELECT HEX(asunto) FROM incidencia ...: una ó correcta en latin1 es F3; si ves C3B3, la aplicación está guardando bytes UTF-8 y debes usar el modo latin1 sin conversión o revisar la conexión de la aplicación. Recuerda también que en latin1 cada carácter ocupa un byte, por lo que el límite de 65 535 bytes de TEXT equivale a 65 535 caracteres; la API recorta a 65 000 con una marcar visible.
5.4. config.php
Credenciales de base de datos (con marcadores) y opciones de seguridad. Admite variables de entorno definidas en el pool de PHP-FPM para no dejar contraseñas en ficheros, y debe tener permisos 0640 con el grupo del pool.
<?php
/**
* ProyectoA Help Desk - API de alta de tickets por email
* Configuración. Este fichero NO debe estar dentro de la raíz web.
* Ubicación recomendada: /var/www/apireproyectoa/private/helpdesk/config.php
* Permisos recomendados: propietario del usuario SFTP, grupo www-data, modo 0640.
*
* Los valores entre <...> son marcadores: sustitúyelos por los reales.
* Alternativa: definir las variables de entorno HELPDESK_DB_* en el pool de PHP-FPM.
*/
declare(strict_types=1);
return [
'db' => [
'host' => getenv('HELPDESK_DB_HOST') ?: '127.0.0.1',
'port' => (int)(getenv('HELPDESK_DB_PORT') ?: 3306),
'name' => getenv('HELPDESK_DB_NAME') ?: '<nombre_bd>',
'user' => getenv('HELPDESK_DB_USER') ?: '<usuario_bd>',
'pass' => getenv('HELPDESK_DB_PASS') ?: '<contraseña_bd>',
// 'utf8mb4' (recomendado): PHP trabaja en UTF-8 y MySQL convierte a latin1 al guardar.
// 'latin1': para bases de datos heredadas en las que la aplicación ya conecta en latin1;
// la API convierte los textos a Windows-1252 antes de enviarlos.
'charset' => getenv('HELPDESK_DB_CHARSET') ?: 'utf8mb4',
],
// IPs de proxies inversos de confianza. Solo si la petición llega desde una de
// ellas se toma la IP del cliente de X-Forwarded-For. Vacío = usar REMOTE_ADDR.
'proxies_confianza' => [],
// Directorio (fuera de la raíz web, escribible por PHP-FPM) para el contador de
// fallos de autenticación por IP. Vacío = protección desactivada.
'dir_estado' => getenv('HELPDESK_DIR_ESTADO') ?: sys_get_temp_dir() . '/proyectoa_helpdesk',
// Fallos de clave permitidos por IP en la ventana indicada antes de responder 429.
'max_fallos_ip' => 10,
'ventana_fallos_min' => 15,
// Tamaño máximo del cuerpo JSON de la petición (bytes).
'max_peticion' => 262144,
];
5.5. util.php
Funciones comunes: hd_responder() emite JSON con Cache-Control: no-store y termina; hd_texto() normaliza la entrada (UTF-8 válido, sin \0 ni \r, recorte por caracteres con mb_substr); hd_normalizar_message_id() deja el identificador en minúsculas, sin < > y con un juego de caracteres seguro; hd_like() escapa comodines usando ESCAPE '!', que no depende del modo NO_BACKSLASH_ESCAPES; y hd_fallos_ip() lleva un contador de fallos de autenticación por IP en un fichero con flock(), sin necesidad de tablas adicionales.
<?php
/**
* ProyectoA Help Desk - utilidades comunes (respuestas JSON, saneado de texto,
* conversión de juego de caracteres, IP del cliente y contador de fallos).
*/
declare(strict_types=1);
/** Envía la respuesta JSON y termina la petición. */
function hd_responder(int $http, array $datos): void
{
if (!headers_sent()) {
http_response_code($http);
header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: no-store');
header('X-Content-Type-Options: nosniff');
}
echo json_encode($datos, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE);
exit;
}
/** Respuesta de error con mensaje genérico (nunca detalles internos). */
function hd_error(int $http, string $mensaje, array $extra = []): void
{
hd_responder($http, array_merge(['ok' => false, 'message' => $mensaje], $extra));
}
/** Error de configuración: el cliente debe reintentar sin dar el correo por tratado. */
function hd_error_configuracion(string $causa, string $mensaje): void
{
hd_responder(503, [
'ok' => false,
'retry' => true,
'reason' => 'configuracion',
'causa' => $causa,
'message' => $mensaje,
]);
}
/**
* Normaliza un valor de entrada a texto UTF-8 válido: elimina \0 y \r,
* recorta espacios y limita la longitud en caracteres.
*/
function hd_texto(mixed $valor, int $max): string
{
if (!is_scalar($valor)) {
return '';
}
$t = (string)$valor;
if (!mb_check_encoding($t, 'UTF-8')) {
$t = mb_convert_encoding($t, 'UTF-8', 'UTF-8');
}
$t = str_replace(["\0", "\r"], '', $t);
$t = trim($t);
if (mb_strlen($t, 'UTF-8') > $max) {
$t = rtrim(mb_substr($t, 0, $max, 'UTF-8'));
}
return $t;
}
/** Recorta un texto largo añadiendo una marca visible. */
function hd_truncar(string $t, int $max): string
{
if (mb_strlen($t, 'UTF-8') <= $max) {
return $t;
}
$marca = "\n[...texto truncado por longitud...]";
return mb_substr($t, 0, $max - mb_strlen($marca, 'UTF-8'), 'UTF-8') . $marca;
}
/**
* Message-ID normalizado: sin < >, minúsculas y solo caracteres seguros.
* Es la clave de deduplicación (se guarda en el texto del ticket).
*/
function hd_normalizar_message_id(mixed $raw): string
{
$t = strtolower(hd_texto($raw, 500));
$t = trim($t, " \t\n<>");
$t = preg_replace('/[^a-z0-9._@+=\-]/', '', $t) ?? '';
return substr($t, 0, 250);
}
/** Escapa un literal para LIKE ... ESCAPE '!' (no depende de NO_BACKSLASH_ESCAPES). */
function hd_like(string $t): string
{
return str_replace(['!', '%', '_'], ['!!', '!%', '!_'], $t);
}
/* ---------------------------------------------------------------------------
* Juego de caracteres
* La BD es latin1. MySQL "latin1" equivale en la práctica a Windows-1252.
* - Modo utf8mb4: la conexión es UTF-8 y MySQL convierte al escribir en columnas
* latin1. Los caracteres sin equivalente (emojis, CJK...) provocarían el error
* 1366 en modo estricto, así que se sustituyen antes por '?'.
* - Modo latin1: la conexión es latin1; PHP convierte UTF-8 -> Windows-1252 al
* escribir y Windows-1252 -> UTF-8 al leer.
* ------------------------------------------------------------------------- */
function hd_charset(?string $nuevo = null): string
{
static $modo = 'utf8mb4';
if ($nuevo !== null) {
$modo = ($nuevo === 'latin1') ? 'latin1' : 'utf8mb4';
}
return $modo;
}
/** Texto UTF-8 de PHP -> valor para enviar a la BD. */
function hd_a_bd(?string $t): ?string
{
if ($t === null) {
return null;
}
mb_substitute_character(0x3F); // '?'
$cp1252 = mb_convert_encoding($t, 'Windows-1252', 'UTF-8');
if (hd_charset() === 'latin1') {
return $cp1252;
}
return mb_convert_encoding($cp1252, 'UTF-8', 'Windows-1252');
}
/** Valor leído de la BD -> texto UTF-8 de PHP. */
function hd_desde_bd(?string $t): string
{
if ($t === null) {
return '';
}
return hd_charset() === 'latin1' ? mb_convert_encoding($t, 'UTF-8', 'Windows-1252') : $t;
}
/** IP del cliente (X-Forwarded-For solo si llega de un proxy de confianza). */
function hd_ip_cliente(array $proxies): string
{
$remota = (string)($_SERVER['REMOTE_ADDR'] ?? '');
if ($proxies && in_array($remota, $proxies, true) && !empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$partes = array_reverse(array_map('trim', explode(',', (string)$_SERVER['HTTP_X_FORWARDED_FOR'])));
foreach ($partes as $ip) {
if (filter_var($ip, FILTER_VALIDATE_IP) && !in_array($ip, $proxies, true)) {
return $ip;
}
}
}
return filter_var($remota, FILTER_VALIDATE_IP) ? $remota : '0.0.0.0';
}
/**
* Contador de fallos de autenticación por IP en un fichero (sin tablas nuevas).
* Devuelve el número de fallos dentro de la ventana; si $anotar, suma uno.
*/
function hd_fallos_ip(string $dir, string $ip, int $ventanaMin, bool $anotar): int
{
if ($dir === '') {
return 0;
}
if (!is_dir($dir) && !@mkdir($dir, 0700, true)) {
return 0;
}
$fichero = $dir . '/fallos_' . hash('sha256', $ip) . '.json';
$fp = @fopen($fichero, 'c+');
if ($fp === false) {
return 0;
}
try {
flock($fp, LOCK_EX);
$datos = json_decode((string)stream_get_contents($fp), true);
$limite = time() - $ventanaMin * 60;
$marcas = array_values(array_filter(is_array($datos) ? $datos : [], fn($t) => is_int($t) && $t >= $limite));
if ($anotar) {
$marcas[] = time();
ftruncate($fp, 0);
rewind($fp);
fwrite($fp, json_encode($marcas));
}
return count($marcas);
} finally {
flock($fp, LOCK_UN);
fclose($fp);
}
}
5.6. db.php
Conexión PDO con ERRMODE_EXCEPTION, FETCH_ASSOC y prepares nativos (ATTR_EMULATE_PREPARES = false). Con prepares nativos un mismo marcador con nombre no puede repetirse en una consulta, por eso en la búsqueda de contactos cada columna tiene su propio marcador (:e0 a :e7). hd_param() lee y cachea los parámetros de la tabla parametro.
<?php
/**
* ProyectoA Help Desk - conexión PDO MySQL y lectura de parámetros.
*/
declare(strict_types=1);
function hd_conectar(array $cfg): PDO
{
$charset = ($cfg['charset'] ?? 'utf8mb4') === 'latin1' ? 'latin1' : 'utf8mb4';
hd_charset($charset);
$dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=%s',
$cfg['host'], (int)$cfg['port'], $cfg['name'], $charset);
$db = new PDO($dsn, $cfg['user'], $cfg['pass'], [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_TIMEOUT => 5,
]);
return $db;
}
/** Valor (columna valor, recortado) de un parámetro de la tabla parametro. '' si no existe. */
function hd_param(PDO $db, string $nombre): string
{
static $cache = [];
if (!array_key_exists($nombre, $cache)) {
$st = $db->prepare('SELECT valor FROM parametro WHERE nombre = :n ORDER BY codigo LIMIT 1');
$st->execute([':n' => $nombre]);
$v = $st->fetchColumn();
$cache[$nombre] = $v === false ? '' : trim(hd_desde_bd((string)$v));
}
return $cache[$nombre];
}
/** Parámetro entero; $defecto si está vacío o no es numérico. */
function hd_param_int(PDO $db, string $nombre, ?int $defecto): ?int
{
$v = hd_param($db, $nombre);
return preg_match('/^-?\d+$/', $v) ? (int)$v : $defecto;
}
5.7. auth.php
Antes de nada se comprueba si la IP acumula demasiados fallos (429). Después se busca el usuario cuyo nick indica helpdesk_email_usuario_api y se compara hash('sha256', clave) con usuario.token mediante hash_equals(), en tiempo constante. Si el token no tiene formato de hash válido, se rechaza todo (fallo seguro). Una clave correcta con el usuario inactivo (activo <> 'S') o con token_expiracion vencido devuelve 403.
<?php
/**
* ProyectoA Help Desk - autenticación por cabecera X-Api-Key.
*
* La clave se compara con usuario.token del usuario cuyo nick indica el parámetro
* helpdesk_email_usuario_api (por defecto "api"). En token se guarda el SHA-256
* en hexadecimal (64 caracteres) de la clave; nunca la clave en claro.
*/
declare(strict_types=1);
/** Devuelve ['codigo' => int, 'nick' => string] del usuario API o termina con 401/403/429. */
function hd_autenticar(PDO $db, array $cfg): array
{
$ip = hd_ip_cliente($cfg['proxies_confianza'] ?? []);
$dir = (string)($cfg['dir_estado'] ?? '');
$ventana = (int)($cfg['ventana_fallos_min'] ?? 15);
$maxFallos = (int)($cfg['max_fallos_ip'] ?? 10);
if (hd_fallos_ip($dir, $ip, $ventana, false) >= $maxFallos) {
hd_error(429, 'Demasiados intentos. Inténtalo más tarde.', ['reason' => 'bloqueo_ip']);
}
$clave = trim((string)($_SERVER['HTTP_X_API_KEY'] ?? ''));
$nick = hd_param($db, 'helpdesk_email_usuario_api');
if ($nick === '') {
$nick = 'api';
}
$st = $db->prepare(
'SELECT codigo, nick, token, activo,
(token_expiracion IS NULL OR token_expiracion > NOW()) AS vigente
FROM usuario WHERE nick = :nick LIMIT 1'
);
$st->execute([':nick' => hd_a_bd($nick)]);
$u = $st->fetch();
$esperado = $u ? strtolower(trim((string)$u['token'])) : '';
$formatoOk = (bool)preg_match('/^[0-9a-f]{64}$/', $esperado);
// Se calcula siempre el hash para no dar pistas por tiempo de respuesta.
$recibido = hash('sha256', $clave);
$valida = strlen($clave) >= 32 && strlen($clave) <= 256
&& hash_equals($formatoOk ? $esperado : str_repeat('0', 64), $recibido)
&& $formatoOk;
if (!$valida) {
hd_fallos_ip($dir, $ip, $ventana, true);
usleep(250000);
hd_error(401, 'Acceso no autorizado.');
}
if (strtoupper((string)$u['activo']) !== 'S' || (int)$u['vigente'] !== 1) {
hd_error(403, 'Acceso denegado.');
}
return ['codigo' => (int)$u['codigo'], 'nick' => (string)$u['nick']];
}
5.8. helpdesk.php: la lógica de negocio
Es el núcleo. El orden de las comprobaciones de hd_procesar() es deliberado:
- Configuración (
hd_validar_configuracion): servicio activo, estado y tipo definidos y técnico por defecto existente. Si falla, 503configuracionpara que el script reintente en lugar de dar el correo por perdido. - Validación del correo: remitente con formato de email, asunto, descripción y Message-ID obligatorios (400).
- Exclusiones: la propia cuenta de soporte y la lista
helpdesk_email_excluidos(200skipped). - Bloqueo de concurrencia:
SELECT GET_LOCK('phd_' + md5(message_id), 10). Dos peticiones simultáneas del mismo correo se serializan; la segunda, al obtener el bloqueo, ya encuentra el duplicado. Se libera conRELEASE_LOCKen una función de cierre, porqueexitno ejecutar los bloquesfinally. - Deduplicación: se busca la línea exacta
Message-ID: <id>enincidencia_comentario.comentarioy enincidencia.incidenciade los últimos N días (porfechaa), conLIKE ... ESCAPE '!'. Los ángulos< >delimitan el identificador y evitan coincidencias parciales. - Límite por remitente: tickets y comentarios de la última hora (429
limite_remitente). - Referencia
[TICKET-n]: primero en el asunto y, si no, en el cuerpo. Siincidencia.codigo = nexiste, se añade un comentario; si el número no existe, se crea un ticket nuevo. - Resolución del remitente:
tercero.email(admite varias direcciones separadas por coma o punto y coma) →codigocliente;contacto.emailempresa1..4yemailparticular1..4→codigocontactoy, si aún no hay cliente,contacto.codigotercero; en último caso, el cliente por defecto.incidencia.contactoguarda «Nombre <email>» (máximo 100 caracteres). - Alta del ticket o del comentario con sentencias preparadas. El comentario se inserta en una transacción que actualiza también
incidencia.fechamycodusuariom.
El texto que se guarda en incidencia.incidencia (o en el comentario) tiene siempre esta estructura, de modo que aunque la limpieza de firma fallase nunca se pierde información:
Buenos días,
no puedo acceder al ERP desde esta mañana.
----- Datos del correo (ProyectoA Help Desk) -----
Remitente: usuario@cliente.es
Nombre: Nombre Apellidos
Para: bas@ajpdsoft.com
Fecha del correo: 2026-11-02 08:58:44
Message-ID: <caj9an6n...@mail.gmail.com>
Cliente: 2 Cliente Ejemplo S.L. (email de tercero)
----- Firma separada -----
--
Nombre Apellidos · Tel. 600 000 000
----- Correo original completo -----
Buenos días,
no puedo acceder al ERP desde esta mañana.
--
Nombre Apellidos · Tel. 600 000 000
El código completo:
<?php
/**
* ProyectoA Help Desk - lógica de negocio del alta de tickets por email.
*
* - procesar_email: decide entre comentario (referencia [TICKET-n] existente) y ticket nuevo.
* - crear_ticket: fuerza el alta de un ticket nuevo.
* - insertar_comentario: añade un comentario a un ticket indicado.
*
* Todas comparten validación, exclusiones, límite por remitente, bloqueo GET_LOCK
* y deduplicación por Message-ID.
*/
declare(strict_types=1);
const HD_REGEX_REFERENCIA = '/\[TICKET-(\d{1,10})\]/i';
const HD_MAX_TEXT = 65000; // margen bajo los 65535 bytes de TEXT (latin1: 1 byte/carácter)
const HD_LOCK_SEGUNDOS = 10;
/** Parámetros del servicio leídos de la tabla parametro. */
function hd_config_servicio(PDO $db): array
{
$excluidos = preg_split('/[,;\n]+/', strtolower(hd_param($db, 'helpdesk_email_excluidos'))) ?: [];
$excluidos = array_values(array_filter(array_map('trim', $excluidos), fn($e) => $e !== ''));
$prioridad = hd_param($db, 'helpdesk_email_prioridad');
return [
'activo' => strtoupper(hd_param($db, 'helpdesk_email_activo')) === 'S',
'estado' => mb_substr(hd_param($db, 'helpdesk_email_estado'), 0, 30),
'tipo' => mb_substr(hd_param($db, 'helpdesk_email_tipo'), 0, 30),
'prioridad' => mb_substr($prioridad !== '' ? $prioridad : 'Media', 0, 10),
'codigotecnico' => hd_param_int($db, 'helpdesk_email_codigotecnico', null),
'codigorecurso' => hd_param_int($db, 'helpdesk_email_codigorecurso', null),
'codigocliente' => hd_param_int($db, 'helpdesk_email_codigocliente', null),
'excluidos' => $excluidos,
'cuenta_soporte' => strtolower(hd_param($db, 'helpdesk_email_cuenta_soporte')),
'dias_duplicado' => max(1, (int)hd_param_int($db, 'helpdesk_email_dias_duplicado', 10)),
'limite_hora' => max(0, (int)hd_param_int($db, 'helpdesk_email_limite_hora', 60)),
'max_cuerpo' => min(30000, max(1000, (int)hd_param_int($db, 'helpdesk_email_max_cuerpo', 20000))),
'dias_vencimiento' => max(0, (int)hd_param_int($db, 'helpdesk_email_dias_vencimiento', 0)),
];
}
/** Datos del correo recibidos en JSON, saneados y recortados. */
function hd_leer_correo(array $in, int $maxCuerpo): array
{
$asunto = hd_texto($in['asunto'] ?? '', 255);
$original = hd_texto($in['cuerpo_original'] ?? '', $maxCuerpo);
$descripcion = hd_texto($in['descripcion'] ?? '', $maxCuerpo);
if ($descripcion === '') {
$descripcion = $original !== '' ? $original : $asunto;
}
return [
'remitente' => strtolower(hd_texto($in['remitente'] ?? '', 200)),
'remitente_nombre' => hd_texto($in['remitente_nombre'] ?? '', 200),
'reply_to' => hd_texto($in['reply_to'] ?? '', 500),
'para' => hd_texto($in['para'] ?? '', 1000),
'cc' => hd_texto($in['cc'] ?? '', 1000),
'fecha_correo' => hd_texto($in['fecha_correo'] ?? '', 40),
'message_id' => hd_normalizar_message_id($in['message_id'] ?? ''),
'asunto' => $asunto,
'descripcion' => $descripcion,
'cuerpo_original' => $original,
'firma' => hd_texto($in['firma'] ?? '', 8000),
];
}
function hd_validar_correo(array $c): void
{
if ($c['remitente'] === '' || !filter_var($c['remitente'], FILTER_VALIDATE_EMAIL)) {
hd_error(400, 'Remitente no válido.');
}
if ($c['asunto'] === '' || $c['descripcion'] === '') {
hd_error(400, 'El asunto y la descripción son obligatorios.');
}
if ($c['message_id'] === '') {
hd_error(400, 'Falta el identificador del mensaje (message_id).');
}
}
function hd_esta_excluido(string $remitente, array $excluidos): bool
{
$dominio = strrchr($remitente, '@') ?: '';
foreach ($excluidos as $e) {
if ($e === $remitente || ($e[0] === '@' && $e === $dominio)) {
return true;
}
}
return false;
}
/** Bloque de datos del correo que se guarda en el ticket/comentario (incluye la línea Message-ID). */
function hd_bloque_datos(array $c, array $rem): string
{
$l = ['----- Datos del correo (ProyectoA Help Desk) -----'];
$add = function (string $et, string $v) use (&$l): void {
if ($v !== '') {
$l[] = $et . ': ' . $v;
}
};
$add('Remitente', $c['remitente']);
$add('Nombre', $c['remitente_nombre']);
$add('Reply-To', $c['reply_to']);
$add('Para', $c['para']);
$add('Cc', $c['cc']);
$add('Fecha del correo', $c['fecha_correo']);
$l[] = 'Message-ID: <' . $c['message_id'] . '>';
$add('Cliente', $rem['codigocliente'] ? $rem['codigocliente'] . ' ' . $rem['nombrecliente'] . ' (' . $rem['metodo'] . ')' : '');
$add('Contacto', $rem['codigocontacto'] ? $rem['codigocontacto'] . ' ' . $rem['nombrecontacto'] : '');
return implode("\n", $l);
}
/** Texto completo: descripción limpia + datos + firma + correo original íntegro. */
function hd_componer_texto(array $c, array $rem): string
{
$t = $c['descripcion'] . "\n\n" . hd_bloque_datos($c, $rem);
if ($c['firma'] !== '') {
$t .= "\n\n----- Firma separada -----\n" . $c['firma'];
}
if ($c['cuerpo_original'] !== '') {
$t .= "\n\n----- Correo original completo -----\n" . $c['cuerpo_original'];
}
return hd_truncar($t, HD_MAX_TEXT);
}
/** Operaciones (tickets + comentarios) de un remitente en la última hora. */
function hd_operaciones_hora(PDO $db, int $codigoApi, string $remitente): int
{
$st = $db->prepare(
"SELECT
(SELECT COUNT(*) FROM incidencia
WHERE codusuarioa = :u AND fechaa >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND incidencia LIKE :p ESCAPE '!')
+ (SELECT COUNT(*) FROM incidencia_comentario
WHERE remitente = :r AND fechaa >= DATE_SUB(NOW(), INTERVAL 1 HOUR)) AS n"
);
$st->execute([
':u' => $codigoApi,
':p' => hd_a_bd('%' . hd_like("\nRemitente: " . $remitente . "\n") . '%'),
':r' => hd_a_bd($remitente),
]);
return (int)$st->fetchColumn();
}
/**
* Busca el Message-ID en tickets y comentarios de los últimos N días.
* Devuelve null o ['ticket' => int, 'comentario' => bool].
*/
function hd_buscar_duplicado(PDO $db, string $messageId, int $dias): ?array
{
$patron = hd_a_bd('%' . hd_like('Message-ID: <' . $messageId . '>') . '%');
$dias = max(1, $dias);
$st = $db->prepare(
"SELECT codigoincidencia FROM incidencia_comentario
WHERE fechaa >= DATE_SUB(NOW(), INTERVAL $dias DAY) AND comentario LIKE :p ESCAPE '!'
ORDER BY codigo DESC LIMIT 1"
);
$st->execute([':p' => $patron]);
$v = $st->fetchColumn();
if ($v !== false) {
return ['ticket' => (int)$v, 'comentario' => true];
}
$st = $db->prepare(
"SELECT codigo FROM incidencia
WHERE fechaa >= DATE_SUB(NOW(), INTERVAL $dias DAY) AND incidencia LIKE :p ESCAPE '!'
ORDER BY codigo DESC LIMIT 1"
);
$st->execute([':p' => $patron]);
$v = $st->fetchColumn();
return $v !== false ? ['ticket' => (int)$v, 'comentario' => false] : null;
}
/** Referencia [TICKET-n]: primero en el asunto y, si no, en el cuerpo. 0 si no hay. */
function hd_extraer_referencia(array $c): int
{
foreach ([$c['asunto'], $c['cuerpo_original'] !== '' ? $c['cuerpo_original'] : $c['descripcion']] as $texto) {
if (preg_match(HD_REGEX_REFERENCIA, $texto, $m)) {
return (int)$m[1];
}
}
return 0;
}
function hd_ticket_existe(PDO $db, int $codigo): bool
{
if ($codigo <= 0) {
return false;
}
$st = $db->prepare('SELECT 1 FROM incidencia WHERE codigo = :c LIMIT 1');
$st->execute([':c' => $codigo]);
return $st->fetchColumn() !== false;
}
/**
* Resolución del remitente:
* 1) tercero.email (admite listas separadas por , o ;) -> codigocliente
* 2) contacto.emailempresa1..4 / emailparticular1..4 -> codigocontacto (+ codigotercero)
* 3) cliente por defecto (parámetro helpdesk_email_codigocliente)
*/
function hd_resolver_remitente(PDO $db, string $email, ?int $clienteDefecto): array
{
$r = ['codigocliente' => null, 'nombrecliente' => '', 'codigocontacto' => null,
'nombrecontacto' => '', 'metodo' => 'por defecto'];
$st = $db->prepare(
"SELECT codigo, nombre FROM tercero
WHERE email IS NOT NULL AND email <> ''
AND COALESCE(activo, 'S') <> 'N'
AND CONCAT(';', REPLACE(REPLACE(REPLACE(REPLACE(LOWER(email), ' ', ''), ',', ';'), CHAR(13), ''), CHAR(10), ';'), ';')
LIKE :p ESCAPE '!'
ORDER BY codigo LIMIT 1"
);
$st->execute([':p' => hd_a_bd('%;' . hd_like($email) . ';%')]);
if ($t = $st->fetch()) {
$r['codigocliente'] = (int)$t['codigo'];
$r['nombrecliente'] = hd_desde_bd($t['nombre']);
$r['metodo'] = 'email de tercero';
}
// Columnas fijas (lista blanca); cada una con su propio marcador (prepares nativos).
$cols = ['emailempresa1', 'emailempresa2', 'emailempresa3', 'emailempresa4',
'emailparticular1', 'emailparticular2', 'emailparticular3', 'emailparticular4'];
$cond = [];
$par = [];
foreach ($cols as $i => $col) {
$cond[] = "LOWER(TRIM($col)) = :e$i";
$par[":e$i"] = hd_a_bd($email);
}
$st = $db->prepare('SELECT codigo, nombre, codigotercero FROM contacto WHERE ' . implode(' OR ', $cond) . ' ORDER BY codigo LIMIT 1');
$st->execute($par);
if ($c = $st->fetch()) {
$r['codigocontacto'] = (int)$c['codigo'];
$r['nombrecontacto'] = hd_desde_bd($c['nombre']);
if ($r['codigocliente'] === null && (int)$c['codigotercero'] > 0) {
$r['codigocliente'] = (int)$c['codigotercero'];
$r['nombrecliente'] = hd_nombre_tercero($db, (int)$c['codigotercero']);
$r['metodo'] = 'email de contacto';
}
}
if ($r['codigocliente'] === null && $clienteDefecto !== null && $clienteDefecto > 0) {
$r['codigocliente'] = $clienteDefecto;
$r['nombrecliente'] = hd_nombre_tercero($db, $clienteDefecto);
$r['metodo'] = 'por defecto';
}
return $r;
}
function hd_nombre_tercero(PDO $db, int $codigo): string
{
$st = $db->prepare('SELECT nombre FROM tercero WHERE codigo = :c LIMIT 1');
$st->execute([':c' => $codigo]);
$v = $st->fetchColumn();
return $v === false ? '' : hd_desde_bd((string)$v);
}
/** Comprueba la configuración necesaria para crear tickets (503 configuracion si falta algo). */
function hd_validar_configuracion(PDO $db, array $svc): void
{
if (!$svc['activo']) {
hd_error_configuracion('servicio_inactivo', 'El servicio de alta de tickets por email no está activo.');
}
if ($svc['estado'] === '' || $svc['tipo'] === '') {
hd_error_configuracion('parametros', 'Faltan parámetros del servicio (estado o tipo por defecto).');
}
if ($svc['codigotecnico'] !== null) {
$st = $db->prepare('SELECT 1 FROM tecnico WHERE codigo = :c LIMIT 1');
$st->execute([':c' => $svc['codigotecnico']]);
if ($st->fetchColumn() === false) {
hd_error_configuracion('tecnico', 'El técnico por defecto no existe.');
}
}
}
/** Inserta el ticket y devuelve su código. */
function hd_insertar_ticket(PDO $db, array $svc, array $c, array $rem, int $codigoApi): int
{
$contacto = $c['remitente_nombre'] !== ''
? $c['remitente_nombre'] . ' <' . $c['remitente'] . '>'
: $c['remitente'];
if (mb_strlen($contacto) > 100) {
$contacto = mb_strlen($c['remitente']) <= 100 ? $c['remitente'] : mb_substr($c['remitente'], 0, 100);
}
$vencimiento = $svc['dias_vencimiento'] > 0
? 'DATE_ADD(NOW(), INTERVAL ' . (int)$svc['dias_vencimiento'] . ' DAY)'
: 'NULL';
$st = $db->prepare(
"INSERT INTO incidencia
(fecha, codigocliente, importecoste, importecliente, asunto, incidencia,
incidenciaresolucion, fecharesolucion, estado, tipo, codigotecnico, prioridad,
contacto, completado, fechavencimiento, codigorecurso, codigocontacto,
codusuarioa, codusuariom, fechaa, fecham, aceptada, resuelta)
VALUES
(NOW(), :cli, 0, 0, :asunto, :texto,
'', NULL, :estado, :tipo, :tec, :prio,
:contacto, 0, $vencimiento, :rec, :ccon,
:ua, :um, NOW(), NOW(), 'N', 'N')"
);
$st->execute([
':cli' => $rem['codigocliente'],
':asunto' => hd_a_bd($c['asunto']),
':texto' => hd_a_bd(hd_componer_texto($c, $rem)),
':estado' => hd_a_bd($svc['estado']),
':tipo' => hd_a_bd($svc['tipo']),
':tec' => $svc['codigotecnico'],
':prio' => hd_a_bd($svc['prioridad']),
':contacto' => hd_a_bd($contacto),
':rec' => $svc['codigorecurso'],
':ccon' => $rem['codigocontacto'],
':ua' => $codigoApi,
':um' => $codigoApi,
]);
return (int)$db->lastInsertId();
}
/** Inserta el comentario y actualiza la fecha de modificación del ticket. Devuelve el código del comentario. */
function hd_insertar_comentario(PDO $db, int $ticket, array $c, array $rem, int $codigoApi): int
{
$db->beginTransaction();
try {
$st = $db->prepare(
"INSERT INTO incidencia_comentario
(codigoincidencia, fecha, comentario, remitente, remitentenombre, origen,
codigocontacto, codusuarioa, fechaa)
VALUES (:t, NOW(), :txt, :r, :rn, 'email', :cc, :u, NOW())"
);
$st->execute([
':t' => $ticket,
':txt' => hd_a_bd(hd_componer_texto($c, $rem)),
':r' => hd_a_bd($c['remitente']),
':rn' => hd_a_bd($c['remitente_nombre']),
':cc' => $rem['codigocontacto'],
':u' => $codigoApi,
]);
$codigo = (int)$db->lastInsertId();
$st = $db->prepare('UPDATE incidencia SET codusuariom = :u, fecham = NOW() WHERE codigo = :t');
$st->execute([':u' => $codigoApi, ':t' => $ticket]);
$db->commit();
return $codigo;
} catch (Throwable $e) {
if ($db->inTransaction()) {
$db->rollBack();
}
throw $e;
}
}
/**
* Punto de entrada común.
* $modo: 'auto' (procesar_email), 'ticket' (crear_ticket) o 'comentario' (insertar_comentario).
*/
function hd_procesar(PDO $db, array $api, array $in, string $modo): void
{
$svc = hd_config_servicio($db);
hd_validar_configuracion($db, $svc);
$c = hd_leer_correo($in, $svc['max_cuerpo']);
hd_validar_correo($c);
if ($svc['cuenta_soporte'] !== '' && $c['remitente'] === $svc['cuenta_soporte']) {
hd_responder(200, ['ok' => false, 'skipped' => true, 'reason' => 'cuenta_propia',
'message' => 'Correo enviado por la propia cuenta de soporte.']);
}
if (hd_esta_excluido($c['remitente'], $svc['excluidos'])) {
hd_responder(200, ['ok' => false, 'skipped' => true, 'reason' => 'remitente_excluido',
'message' => 'El remitente está excluido del alta por email.']);
}
$ticketDestino = 0;
if ($modo === 'comentario') {
$ticketDestino = (int)($in['ticket'] ?? 0);
if (!hd_ticket_existe($db, $ticketDestino)) {
hd_error(404, 'El ticket indicado no existe.');
}
}
// Bloqueo consultivo por Message-ID: dos peticiones simultáneas del mismo correo
// no pueden crear dos tickets. Se libera siempre al terminar (finally).
$lock = 'phd_' . md5($c['message_id']);
$st = $db->prepare('SELECT GET_LOCK(:n, ' . HD_LOCK_SEGUNDOS . ')');
$st->execute([':n' => $lock]);
$estadoLock = $st->fetchColumn();
$tengoLock = ((int)$estadoLock === 1);
if ($tengoLock) {
// hd_responder() termina con exit (no ejecuta finally): se libera al cerrar la petición.
register_shutdown_function(function () use ($db, $lock): void {
try {
$db->prepare('SELECT RELEASE_LOCK(:n)')->execute([':n' => $lock]);
} catch (Throwable $e) {
// la conexión puede estar ya cerrada; MySQL libera el bloqueo al cerrar la sesión
}
});
}
$dup = hd_buscar_duplicado($db, $c['message_id'], $svc['dias_duplicado']);
if ($dup !== null) {
hd_responder(200, ['ok' => true, 'ticket' => $dup['ticket'], 'referencia' => '[TICKET-' . $dup['ticket'] . ']',
'comentario' => $dup['comentario'], 'duplicado' => true]);
}
if (!$tengoLock) {
// Otro proceso tiene el mismo correo entre manos: reintentar más tarde.
hd_responder(503, ['ok' => false, 'retry' => true, 'reason' => 'ocupado',
'message' => 'No se ha podido procesar el correo. Se reintentará.']);
}
if ($svc['limite_hora'] > 0 && hd_operaciones_hora($db, $api['codigo'], $c['remitente']) >= $svc['limite_hora']) {
hd_error(429, 'Límite de tickets por hora alcanzado para este remitente.', ['reason' => 'limite_remitente']);
}
if ($modo === 'auto') {
$ref = hd_extraer_referencia($c);
if ($ref > 0 && hd_ticket_existe($db, $ref)) {
$ticketDestino = $ref;
}
}
$rem = hd_resolver_remitente($db, $c['remitente'], $svc['codigocliente']);
if ($ticketDestino > 0) {
try {
$codCom = hd_insertar_comentario($db, $ticketDestino, $c, $rem, $api['codigo']);
} catch (Throwable $e) {
error_log('[helpdesk] fallo al insertar comentario: ' . get_class($e) . ' ' . $e->getCode());
hd_responder(500, ['ok' => false, 'retry' => true, 'message' => 'No se ha podido añadir el comentario.']);
}
hd_responder(201, ['ok' => true, 'ticket' => $ticketDestino, 'referencia' => '[TICKET-' . $ticketDestino . ']',
'comentario' => true, 'codigo_comentario' => $codCom, 'duplicado' => false]);
}
if ($rem['codigocliente'] === null) {
hd_error_configuracion('cliente_defecto', 'Falta el cliente por defecto para remitentes desconocidos.');
}
try {
$ticket = hd_insertar_ticket($db, $svc, $c, $rem, $api['codigo']);
} catch (Throwable $e) {
error_log('[helpdesk] fallo al crear ticket: ' . get_class($e) . ' ' . $e->getCode());
hd_responder(500, ['ok' => false, 'retry' => true, 'message' => 'No se ha podido crear el ticket.']);
}
hd_responder(201, ['ok' => true, 'ticket' => $ticket, 'referencia' => '[TICKET-' . $ticket . ']',
'comentario' => false, 'duplicado' => false]);
}
Campos del ticket:
fecha,fechaayfecham=NOW();importecosteeimportecliente= 0;incidenciaresolucionvacío;completado= 0;aceptadayresuelta= N;fechavencimiento=DATE_ADD(NOW(), INTERVAL n DAY)(válido tanto si la columna es DATE como DATETIME);codusuarioaycodusuariom= código del usuario api. Si la instalación usa otros valores iniciales, deben ajustarse enhd_insertar_ticket().
5.9. api.php: el punto de entrada
Enrutador mínimo: limita el método (GET solo para ping), el tamaño del cuerpo, decodifica el JSON, conecta a la BD (503 retry si no puede), autentica y despacha la acción. Un manejador global de excepciones devuelve un 500 genérico con retry:true y escribe en el log de PHP solo la clase, el código y la línea, nunca el contenido del correo ni la clave.
<?php
/**
* ProyectoA Help Desk - API REST de alta de tickets por email.
* Único fichero publicado en la raíz web: https://apireproyectoa.ajpdsoft.com/api.php
*
* Acciones (JSON por POST, campo "action"; ping también por GET ?action=ping):
* ping comprueba clave y estado del servicio
* procesar_email comentario si hay [TICKET-n] existente; si no, ticket nuevo
* crear_ticket fuerza ticket nuevo
* insertar_comentario comentario en el ticket indicado en "ticket"
*
* Autenticación: cabecera X-Api-Key (ver auth.php).
*/
declare(strict_types=1);
// Directorio privado con config.php y las librerías (fuera de la raíz web).
define('HD_LIB', getenv('PROYECTOA_HELPDESK_LIB') ?: dirname(__DIR__) . '/private/helpdesk');
const HD_VERSION = '1.0.0';
require HD_LIB . '/util.php';
require HD_LIB . '/db.php';
require HD_LIB . '/auth.php';
require HD_LIB . '/helpdesk.php';
ini_set('display_errors', '0');
set_exception_handler(function (Throwable $e): void {
// Al registro solo el tipo y el código: nunca datos del correo ni secretos.
error_log('[helpdesk] excepción no controlada: ' . get_class($e) . ' ' . $e->getCode()
. ' en ' . basename($e->getFile()) . ':' . $e->getLine());
hd_responder(500, ['ok' => false, 'retry' => true, 'message' => 'Error interno. Se reintentará.']);
});
$cfg = require HD_LIB . '/config.php';
$metodo = $_SERVER['REQUEST_METHOD'] ?? 'GET';
if ($metodo !== 'POST' && $metodo !== 'GET') {
header('Allow: GET, POST');
hd_error(405, 'Método no permitido.');
}
$entrada = [];
if ($metodo === 'POST') {
$max = (int)($cfg['max_peticion'] ?? 262144);
$raw = (string)file_get_contents('php://input', false, null, 0, $max + 1);
if (strlen($raw) > $max) {
hd_error(413, 'Petición demasiado grande.');
}
$entrada = json_decode($raw, true);
if (!is_array($entrada)) {
hd_error(400, 'Datos no válidos.');
}
}
$accion = (string)($entrada['action'] ?? ($_GET['action'] ?? ''));
if ($metodo === 'GET' && $accion !== 'ping') {
hd_error(405, 'Método no permitido.');
}
try {
$db = hd_conectar($cfg['db']);
} catch (Throwable $e) {
error_log('[helpdesk] sin conexión a la base de datos (' . $e->getCode() . ')');
hd_responder(503, ['ok' => false, 'retry' => true, 'reason' => 'bd', 'message' => 'Servicio no disponible.']);
}
$api = hd_autenticar($db, $cfg);
switch ($accion) {
case 'ping':
hd_responder(200, [
'ok' => true,
'pong' => true,
'version' => HD_VERSION,
'servicio_activo' => strtoupper(hd_param($db, 'helpdesk_email_activo')) === 'S',
]);
break;
case 'procesar_email':
hd_procesar($db, $api, $entrada, 'auto');
break;
case 'crear_ticket':
hd_procesar($db, $api, $entrada, 'ticket');
break;
case 'insertar_comentario':
hd_procesar($db, $api, $entrada, 'comentario');
break;
default:
hd_error(400, 'Acción no válida.');
}
6. Publicación de la API en el servidor
La API se publica detrás de un balanceador nginx con TLS (Let’s Encrypt) y tres nodos backend con PHP-FPM. El nombre público usado en este artículo es apireproyectoa.ajpdsoft.com; las IP internas de ejemplo son 192.168.20.135 (balanceador) y 192.168.20.136–138 (nodos).

6.1. DNS: registro A del dominio
En el panel DNS del dominio (el hosting donde se gestiona ajpdsoft.com) se crea un registro A para apireproyectoa.ajpdsoft.com apuntando a la IP pública del firewall perimetral (o a la IP pública del balanceador si está expuesto directamente). La propagación debe completarse antes de solicitar el certificado Let’s Encrypt, porque la validación HTTP-01 consulta ese nombre desde Internet.
# Comprobación desde un equipo con salida a Internet
dig +short apireproyectoa.ajpdsoft.com A
# Debe devolver la IP pública configurada
6.2. NAT en el firewall perimetral
En el firewall perimetral se configura un reenvío (DNAT / port forwarding) de los puertos TCP 80 y 443 de la IP pública hacia la IP interna del balanceador nginx (en el ejemplo, 192.168.20.135). El puerto 80 es necesario para el desafío HTTP-01 de Let’s Encrypt y para la redirección posterior a HTTPS; el 443 sirve el tráfico de la API.
Advertencia: Si el puerto 80 no llega al balanceador,
certbot --nginxfallará al validar el dominio aunque el DNS sea correcto.
6.3. Estructura de ficheros y permisos
Solo un fichero queda dentro de la raíz web. El resto (configuración y librerías) va en un directorio privado que el servidor web no sirve. En cada nodo:
/var/www/apireproyectoa/
├── public/ ← raíz web (root de nginx)
│ └── api.php ← https://apireproyectoa.ajpdsoft.com/api.php
└── private/
└── helpdesk/ ← fuera de la raíz web
├── config.php ← credenciales de BD (0640)
├── util.php
├── db.php
├── auth.php
└── helpdesk.php
api.php localiza el directorio privado con la variable de entorno PROYECTOA_HELPDESK_LIB o, si no existe, con dirname(__DIR__) . '/private/helpdesk' (una carpeta hacia arriba desde public/).
Permisos recomendados para que el usuario SFTP (en el ejemplo, <usuario_sftp>) pueda editar siempre los ficheros y www-data solo leerlos:
# En cada nodo (sustituir <usuario_sftp> por el usuario real, p. ej. alonso)
sudo mkdir -p /var/www/apireproyectoa/public /var/www/apireproyectoa/private/helpdesk
# Copiar api.php a public/ y el resto a private/helpdesk/
sudo chown -R <usuario_sftp>:www-data /var/www/apireproyectoa
sudo find /var/www/apireproyectoa -type d -exec chmod 2750 {} \;
sudo find /var/www/apireproyectoa -type f -exec chmod 640 {} \;
# Directorio para el contador de fallos por IP (escribible por PHP-FPM)
sudo install -d -o www-data -g www-data -m 0700 /var/lib/proyectoa_helpdesk
# Comprobación: www-data debe poder leer config.php
sudo -u www-data php -r 'echo is_readable("/var/www/apireproyectoa/private/helpdesk/config.php") ? "OK\n" : "NO\n";'
El bit 2 de 2750 (setgid) hace que todo lo que se cree dentro de esas carpetas herede el grupo www-data. Para que los ficheros nuevos subidos por SFTP lleguen ya con umask restrictiva, en /etc/ssh/sshd_config se cambia la línea del subsistema SFTP y se reinicia SSH:
Subsystem sftp internal-sftp -u 0027
sudo systemctl restart ssh
Advertencia: Con umask 0027, los ficheros nuevos quedan en 640 y los directorios en 750 para el propietario. Tras un
chmod 440puntual sobreconfig.php, el usuario SFTP no podrá editarlo hasta devolverlo a 640.
6.4. Nodos backend: nginx y PHP-FPM
En cada nodo se instalan nginx y PHP 8.5 con las extensiones necesarias (ajustar la versión a la instalada; comprobar el socket con ls /run/php/):
sudo apt install nginx php8.5-fpm php8.5-mysql php8.5-mbstring
ls /run/php/ # p. ej. php8.5-fpm.sock
El open_basedir de PHP-FPM debe incluir la ruta del site. Si ya hay otras rutas (por ejemplo otro API), se añaden sin eliminarlas:
# En /etc/php/8.5/fpm/php.ini (ejemplo partiendo de una ruta previa)
open_basedir = "/var/www/apirestnu/:/var/www/apireproyectoa/:/tmp/:/var/lib/php/sessions/"
sudo systemctl restart php8.5-fpm
php-fpm8.5 -i 2>/dev/null | grep -i open_basedir
Advertencia: Si
open_basedirno incluye/var/www/apireproyectoa/, PHP-FPM responde con «No input file specified» o un 500 al abrirapi.php, aunque los permisos del fichero sean correctos.
Site del nodo (/etc/nginx/sites-available/apireproyectoa.conf), enlazado en sites-enabled:
server {
listen 80;
server_name apireproyectoa.ajpdsoft.com;
root /var/www/apireproyectoa/public;
index api.php;
# Solo acepta tráfico del balanceador (ajustar la subred)
allow 192.168.20.0/24;
deny all;
location / { return 404; }
location = /api.php {
include snippets/fastcgi-php.conf;
# Ajustar a la versión instalada (ls /run/php/)
fastcgi_pass unix:/run/php/php8.5-fpm.sock;
fastcgi_param HTTPS on;
}
location ~ /\. { deny all; }
}
sudo ln -s /etc/nginx/sites-available/apireproyectoa.conf /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx
6.5. Balanceador nginx y certificado Let’s Encrypt
En el balanceador se define el upstream con los tres nodos, se publica primero un site solo HTTP (necesario para el desafío de Let’s Encrypt) y, tras emitir el certificado, se deja el site definitivo en 443 con redirección desde 80.
Upstream y site HTTP inicial (/etc/nginx/sites-available/apireproyectoa.conf en el balanceador):
# En el bloque http {} (o en un conf.d incluido):
upstream backend_helpdesk {
least_conn;
server 192.168.20.136:80 max_fails=3 fail_timeout=30s;
server 192.168.20.137:80 max_fails=3 fail_timeout=30s;
server 192.168.20.138:80 max_fails=3 fail_timeout=30s;
keepalive 16;
}
limit_req_zone $binary_remote_addr zone=helpdesk_api:10m rate=30r/m;
server {
listen 80;
server_name apireproyectoa.ajpdsoft.com;
# Temporal: permite el desafío ACME y el proxy mientras se emite el certificado
location ^~ /.well-known/acme-challenge/ {
root /var/www/letsencrypt;
default_type "text/plain";
}
location = /api.php {
limit_req zone=helpdesk_api burst=20 nodelay;
limit_except GET POST { return 444; }
proxy_pass http://backend_helpdesk;
proxy_http_version 1.1;
proxy_set_header Connection "";
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
# Sin http_500: un POST reintentado en otro nodo podría duplicar un ticket
# si el primero ya escribió en la BD y falló al responder.
proxy_next_upstream error timeout http_502 http_503 http_504;
proxy_connect_timeout 5s;
proxy_read_timeout 30s;
}
location / { return 444; }
}
Emisión del certificado con Certbot (plugin nginx):
sudo apt install certbot python3-certbot-nginx
sudo mkdir -p /var/www/letsencrypt
sudo nginx -t && sudo systemctl reload nginx
sudo certbot --nginx -d apireproyectoa.ajpdsoft.com
sudo certbot certificates
# Comprobación de renovación
sudo certbot renew --dry-run
Site definitivo en el balanceador (443 + redirección 80→443). Certbot suele dejar las rutas del certificado ya escritas; el bloque útil queda así:
server {
listen 80;
server_name apireproyectoa.ajpdsoft.com;
return 301 https://$host$request_uri;
}
server {
listen 443 ssl http2;
server_name apireproyectoa.ajpdsoft.com;
ssl_certificate /etc/letsencrypt/live/apireproyectoa.ajpdsoft.com/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/apireproyectoa.ajpdsoft.com/privkey.pem;
include /etc/letsencrypt/options-ssl-nginx.conf;
ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem;
add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
add_header X-Content-Type-Options nosniff always;
add_header X-Frame-Options DENY always;
client_max_body_size 512k;
location = /api.php {
limit_req zone=helpdesk_api burst=20 nodelay;
limit_except GET POST { return 444; }
proxy_pass http://backend_helpdesk;
proxy_http_version 1.1;
proxy_set_header Connection "";
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto https;
# Sin http_500 (véase el site HTTP anterior)
proxy_next_upstream error timeout http_502 http_503 http_504;
proxy_connect_timeout 5s;
proxy_read_timeout 30s;
}
location / { return 444; }
}
No conviene restringir el acceso por IP de origen en el balanceador: las direcciones de salida de Google Apps Script no son fijas. Si el balanceador actúa como proxy, su IP (o la subred) debe figurar en proxies_confianza de config.php en los nodos para que el bloqueo por fallos use la IP real de X-Forwarded-For.
Advertencia: Incluir
http_500enproxy_next_upstreamharía que un POST que ya creó el ticket en un nodo y falló al devolver la respuesta se reenviara a otro nodo y pudiera crear un segundo ticket. Por eso solo se reintentan errores de conexión y 502/503/504.
6.6. Conexión a MySQL desde los nodos
Las credenciales van en config.php (o en variables de entorno del pool PHP-FPM). Desde un nodo se comprueba la conectividad con el cliente mysql. Los clientes recientes exigen SSL por defecto; si el servidor MySQL aún no lo tiene activado, hay que usar --skip-ssl en la prueba:
sudo apt install -y mariadb-client # o mysql-client
mysql -h <host_mysql> -P 3306 -u <usuario_bd> -p <nombre_bd> --skip-ssl -e "SELECT 1;"
El usuario de BD necesita permisos remotos desde la subred de los nodos:
CREATE USER 'helpdesk_api'@'192.168.20.%' IDENTIFIED BY '<contraseña>';
GRANT SELECT, INSERT, UPDATE ON <nombre_bd>.* TO 'helpdesk_api'@'192.168.20.%';
FLUSH PRIVILEGES;
Advertencia: Sin TLS, la contraseña y los datos viajan sin cifrar entre los nodos y MySQL. Si el servidor de base de datos está fuera de la red interna, conviene activar SSL en MySQL y quitar
--skip-ssl.
Comprobación con la misma configuración que usa la API:
sudo -u www-data php -r '$c=require "/var/www/apireproyectoa/private/helpdesk/config.php"; $d=$c["db"]; try{new PDO("mysql:host={$d["host"]};port={$d["port"]};dbname={$d["name"]};charset=utf8mb4",$d["user"],$d["pass"]); echo "OK\n";}catch(Exception $e){echo $e->getMessage(),"\n";}'
6.7. Pruebas con curl
export API=https://apireproyectoa.ajpdsoft.com/api.php
read -s API_KEY # pegar la clave sin que quede en el historial
# GET sin acción: la API solo admite GET para ping → 405
curl -i "$API"
# HTTP/1.1 405 ... {"ok":false,"message":"Método no permitido."}
# POST vacío sin clave → 401
curl -i -X POST "$API" -H "Content-Type: application/json" -d "{}"
# HTTP/1.1 401 ... {"ok":false,"message":"Acceso no autorizado."}
# Ping con clave
curl -s -w '\nHTTP %{http_code}\n' "$API?action=ping" -H "X-Api-Key: $API_KEY"
# {"ok":true,"pong":true,"version":"1.0.0","servicio_activo":true}
# HTTP 200
# Ticket de prueba
curl -s -w '\nHTTP %{http_code}\n' -X POST "$API" \
-H 'Content-Type: application/json' -H "X-Api-Key: $API_KEY" \
-d '{"action":"procesar_email","remitente":"usuario@cliente.es","remitente_nombre":"Usuario Prueba",
"message_id":"prueba-curl-001@ejemplo.com","asunto":"Prueba de alta por API",
"descripcion":"Ticket de prueba creado con curl."}'
# HTTP 201 (repetirlo → HTTP 200 con "duplicado":true)
Un 503 con reason: bd indica que el nodo que atendió la petición no pudo conectar a MySQL. El motivo queda en el log del nodo (/var/log/nginx/error.log o el log de PHP-FPM), no necesariamente en el del balanceador, porque el tráfico se reparte entre los tres nodos. Buscar líneas [helpdesk] en cada uno.
7. Creación del proyecto de Google Apps Script
El script se crea en la propia cuenta del buzón (bas@ajpdsoft.com), de modo que se ejecutar con su identidad y no necesita credenciales de Gmail adicionales. Conviene usar un perfil de navegador separado o una ventana privada para no mezclar la sesión con otra cuenta de Google.
- Se inicia sesión con
bas@ajpdsoft.comy se abre https://script.google.com. - Se crea un Nuevo proyecto. Se hace clic en «Proyecto sin título» y se renombra, por ejemplo, «Buzón bas → ProyectoA Help Desk».
- Se abre Configuración del proyecto (icono de engranaje, a la izquierda): se marca Mostrar el archivo de manifiesto «appsscript.json» en el editor y se selecciona la zona horaria (GMT+01:00) Madrid (
Europe/Madrid). - Se vuelve al Editor (icono
< >). VerásCódigo.gs(oCode.gs) yappsscript.json. - Se sustituye el contenido de
appsscript.jsonpor el manifiesto siguiente y se guarda (Ctrl+S).
{
"timeZone": "Europe/Madrid",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/gmail.modify",
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/script.scriptapp",
"https://www.googleapis.com/auth/script.send_mail",
"https://www.googleapis.com/auth/userinfo.email"
]
}
Declarar oauthScopes de forma explícita limita los permisos a lo imprescindible, en lugar de dejar que Apps Script deduzca un alcance más amplio:
| Ámbito | Para qué se usa |
|---|---|
gmail.modify | Buscar correos, leerlos, crear y poner etiquetas, marcar como leído y archivar (no permite borrar definitivamente). |
script.external_request | UrlFetchApp: llamar a la API PHP. |
script.scriptapp | ScriptApp: crear y borrar el activador de tiempo. |
script.send_mail | MailApp: enviar los avisos de error a AVISO_EMAIL. |
userinfo.email | Session.getEffectiveUser().getEmail(): reconocer los correos propios. |
PropertiesService y LockService no requieren ámbito propio.
8. El código de Apps Script: Code.gs
Se sustituye todo el contenido de Code.gs por el siguiente código y se guarda. La lógica reproduce exactamente el contrato de respuestas de la API descrito en el apartado 5.2.
/**
* ProyectoA Help Desk - Alta automática de tickets desde Gmail
* Proyecto de Google Apps Script instalado en la propia cuenta del buzón
* (bas@ajpdsoft.com). Lee los correos recibidos y los envía a la API PHP
* (acción procesar_email), que crea un ticket o añade un comentario.
*
* Propiedades del script (Configuración del proyecto > Propiedades del script):
* API_URL URL de la API, p. ej. https://apireproyectoa.ajpdsoft.com/api.php
* API_KEY clave en claro (64 hex). En la BD solo se guarda su SHA-256.
* AVISO_EMAIL (opcional) destinatario de los avisos de error
*
* Instalación: ejecutar probarConexion() (autoriza permisos y prueba la API)
* y después instalarActivador() una sola vez.
*
* Funcionamiento por MENSAJE (no por hilo):
* - Busca hilos "in:inbox newer_than:7d" y procesa cada mensaje cuyo id de
* Gmail no figure en el registro p_<id> de las Propiedades del script.
* - Resultado definitivo (ticket, comentario, duplicado o no procesado):
* etiqueta en el hilo, registro p_<id>, mensaje leído y, si el hilo no
* tiene más mensajes pendientes, hilo archivado.
* - Errores de configuración, clave, límites o red: sin etiqueta ni registro,
* sin marcar como leído, se reintenta en el siguiente ciclo y se avisa una
* sola vez por causa.
*/
// --- Etiquetas -------------------------------------------------------------
const ETIQUETA_PROCESADO = 'proyectoa-procesado'; // ticket creado o duplicado
const ETIQUETA_COMENTARIO = 'proyectoa-comentario'; // comentario añadido
const ETIQUETA_NO_PROCESADO = 'proyectoa-no-procesado'; // excluido, rebote, automático, validación
const TODAS_ETIQUETAS = [ETIQUETA_PROCESADO, ETIQUETA_COMENTARIO, ETIQUETA_NO_PROCESADO];
// --- Ventana horaria (Europe/Madrid) ----------------------------------------
const HORA_INICIO = 7; // desde las 07:00
const HORA_FIN = 21; // hasta las 20:59
const SOLO_LABORABLES = false; // true = de lunes a viernes
// --- Límites ----------------------------------------------------------------
const DIAS_BUSQUEDA = 7; // newer_than:7d
const RETENCION_DIAS = 10; // se conservan los p_<id> (> DIAS_BUSQUEDA)
const MAX_HILOS = 500; // hilos revisados por ejecución (páginas de 100)
const MAX_POR_EJECUCION = 20; // mensajes enviados a la API por ejecución
const MAX_FALLOS = 3; // fallos 500 de un mismo mensaje antes de avisar
const MAX_TEXTO = 20000; // caracteres de descripción y cuerpo original
const MINUTOS_ACTIVADOR = 5;
// --- Prefijos de propiedades internas (no editar a mano) -------------------
const P_PROCESADO = 'p_'; // p_<id> = '<resultado>|<epoch ms>' T, C, N, O, I
const P_FALLOS = 'f_'; // f_<id> = '<nº fallos>|<epoch ms>'
const P_AVISO = 'aviso_'; // aviso_<motivo> = epoch ms del aviso enviado
/**
* Crea (o recrea) el activador de tiempo, las etiquetas y la siembra inicial.
* Es idempotente: borra antes los activadores anteriores de procesarBuzon.
*/
function instalarActivador() {
ScriptApp.getProjectTriggers()
.filter(t => t.getHandlerFunction() === 'procesarBuzon')
.forEach(t => ScriptApp.deleteTrigger(t));
TODAS_ETIQUETAS.forEach(obtenerEtiqueta_);
sembrarProcesados_();
ScriptApp.newTrigger('procesarBuzon').timeBased().everyMinutes(MINUTOS_ACTIVADOR).create();
console.log('Activador instalado: procesarBuzon cada ' + MINUTOS_ACTIVADOR + ' minutos.');
}
/** Elimina el activador (detiene el proceso sin borrar el registro). */
function desinstalarActivador() {
ScriptApp.getProjectTriggers()
.filter(t => t.getHandlerFunction() === 'procesarBuzon')
.forEach(t => ScriptApp.deleteTrigger(t));
console.log('Activador eliminado.');
}
/**
* Primera ejecución manual: solicita los permisos y comprueba la API (acción ping).
*/
function probarConexion() {
const cfg = leerConfiguracion_();
if (!cfg) throw new Error('Faltan API_URL o API_KEY en las Propiedades del script.');
obtenerEtiqueta_(ETIQUETA_PROCESADO); // fuerza el permiso de Gmail
const r = llamarApi_(cfg, { action: 'ping' });
console.log('Cuenta: ' + Session.getEffectiveUser().getEmail());
console.log('HTTP ' + r.codigo + ' ' + JSON.stringify(r.datos));
if (r.codigo !== 200 || r.datos.ok !== true) throw new Error('La API no responde correctamente (HTTP ' + r.codigo + ').');
if (r.datos.servicio_activo !== true) console.warn('La API responde, pero el servicio está desactivado (helpdesk_email_activo).');
}
/**
* Opcional: marca como ya tratados TODOS los mensajes actuales de la ventana,
* sin llamar a la API. Útil en la primera instalación si no se quiere convertir
* en tickets el correo de los últimos 7 días.
*/
function sembrarBandejaActual() {
const props = PropertiesService.getScriptProperties();
const ahora = Date.now();
const nuevos = {};
recorrerHilos_(h => h.getMessages().forEach(m => {
if (!props.getProperty(P_PROCESADO + m.getId())) nuevos[P_PROCESADO + m.getId()] = 'I|' + ahora;
}));
props.setProperties(nuevos, false);
console.log('Mensajes registrados como previos: ' + Object.keys(nuevos).length);
}
/**
* Registra como tratados los mensajes de hilos que YA tienen una etiqueta
* proyectoa-* (p. ej. al reinstalar el proyecto), para no reenviarlos.
*/
function sembrarProcesados_() {
const props = PropertiesService.getScriptProperties();
const ahora = Date.now();
const nuevos = {};
recorrerHilos_(h => {
if (!hiloEtiquetado_(h)) return;
h.getMessages().forEach(m => {
if (!props.getProperty(P_PROCESADO + m.getId())) nuevos[P_PROCESADO + m.getId()] = 'I|' + ahora;
});
});
props.setProperties(nuevos, false);
}
/** Función del activador: recorre el buzón y procesa los mensajes pendientes. */
function procesarBuzon() {
if (!dentroDeHorario_()) return;
const lock = LockService.getScriptLock();
if (!lock.tryLock(10000)) return; // otra ejecución sigue en marcha
try {
const props = PropertiesService.getScriptProperties();
const cfg = leerConfiguracion_();
if (!cfg) {
avisarUnaVez_(props, 'config_script', 'Faltan propiedades del script',
'-', '-', 0, 'Define API_URL y API_KEY en Configuración del proyecto > Propiedades del script.');
return;
}
const etiquetas = {
T: obtenerEtiqueta_(ETIQUETA_PROCESADO),
C: obtenerEtiqueta_(ETIQUETA_COMENTARIO),
N: obtenerEtiqueta_(ETIQUETA_NO_PROCESADO)
};
const yo = Session.getEffectiveUser().getEmail().toLowerCase();
const todas = props.getProperties();
// Pendientes = en la bandeja de entrada, fuera de la papelera y sin registro.
const pendientes = [];
recorrerHilos_(h => {
h.getMessages().forEach(m => {
if (todas[P_PROCESADO + m.getId()]) return;
if (m.isInTrash() || !m.isInInbox()) return;
pendientes.push(m);
});
});
pendientes.sort((a, b) => a.getDate() - b.getDate()); // del más antiguo al más reciente
let enviados = 0;
for (const m of pendientes) {
if (enviados >= MAX_POR_EJECUCION) break;
const r = procesarMensaje_(m, cfg, etiquetas, yo, props);
if (r.llamada) enviados++;
if (r.detener) break; // clave rechazada, configuración, red...: no insistir en este ciclo
}
podar_(props, todas);
if (pendientes.length) console.log('Pendientes: ' + pendientes.length + ' · enviados a la API: ' + enviados);
} finally {
lock.releaseLock();
}
}
/** Procesa un mensaje. Devuelve {llamada: bool, detener: bool}. */
function procesarMensaje_(m, cfg, etiquetas, yo, props) {
const id = m.getId();
const desde = m.getFrom();
const remite = extraerEmail_(desde);
const asunto = m.getSubject() || '(sin asunto)';
// 1) Propios (respuestas enviadas desde el buzón): registrados, sin etiqueta.
if (remite === yo) {
registrar_(props, id, 'O');
cerrarProcesado_(m, props);
return { llamada: false, detener: false };
}
// 2) Rebotes y respuestas automáticas: no procesados, sin llamar a la API.
if (esRebote_(remite) || esAutomatico_(m, asunto)) {
definitivo_(m, etiquetas.N, props, id, 'N');
return { llamada: false, detener: false };
}
const original = (m.getPlainBody() || '').replace(/\r\n/g, '\n').trim();
const limpio = limpiarFirma_(original);
const payload = {
action: 'procesar_email',
remitente: remite,
remitente_nombre: extraerNombre_(desde),
reply_to: m.getReplyTo() || '',
para: m.getTo() || '',
cc: m.getCc() || '',
fecha_correo: Utilities.formatDate(m.getDate(), 'Europe/Madrid', 'yyyy-MM-dd HH:mm:ss'),
message_id: (m.getHeader('Message-ID') || ('gmail-' + id)).replace(/[<>\s]/g, ''),
asunto: asunto.substring(0, 250),
descripcion: (limpio.cuerpo || original || asunto).substring(0, MAX_TEXTO),
cuerpo_original: original.substring(0, MAX_TEXTO),
firma: limpio.firma.substring(0, 8000)
};
const r = llamarApi_(cfg, payload);
const codigo = r.codigo, datos = r.datos;
// 3) Definitivo: ticket, comentario, duplicado o descartado por la API (skipped).
if (datos.ok === true || datos.skipped === true) {
const res = datos.skipped === true ? 'N' : (datos.comentario === true ? 'C' : 'T');
definitivo_(m, etiquetas[res], props, id, res);
props.deleteProperty(P_FALLOS + id);
limpiarAvisos_(props);
console.log(res + ' ' + remite + ' "' + asunto + '" -> ' + (datos.ticket || datos.reason || ''));
return { llamada: true, detener: false };
}
// 4) Configuración de la API (servicio inactivo, parámetros): reintento + aviso único.
if (codigo === 503 && datos.reason === 'configuracion') {
avisarUnaVez_(props, 'configuracion_' + (datos.causa || ''),
'ProyectoA Help Desk no está configurado para el alta por email', asunto, remite, codigo, datos.message);
return { llamada: true, detener: true };
}
// 5) Límite por remitente: solo se aplaza ese mensaje; el resto sigue.
if (codigo === 429 && datos.reason === 'limite_remitente') {
avisarUnaVez_(props, 'limite_' + remite, 'Límite de tickets por hora para un remitente',
asunto, remite, codigo, datos.message);
return { llamada: true, detener: false };
}
// 6) Validación (4xx salvo 401/403/429): el correo no es admisible -> no procesado + aviso.
if (codigo >= 400 && codigo < 500 && [401, 403, 429].indexOf(codigo) < 0) {
definitivo_(m, etiquetas.N, props, id, 'N');
props.deleteProperty(P_FALLOS + id);
avisar_('Correo no aceptado por la API', asunto, remite, codigo, datos.message, true);
return { llamada: true, detener: false };
}
// 7) Clave rechazada, bloqueo, BD o red caídas: reintento, aviso único y fin del ciclo.
if (codigo === 0 || codigo === 401 || codigo === 403 || codigo === 429 || codigo >= 502) {
avisarUnaVez_(props, 'api_' + codigo, 'La API no acepta peticiones', asunto, remite, codigo, datos.message);
return { llamada: true, detener: true };
}
// 8) Otro fallo (500 retry): reintento; aviso único al MAX_FALLOS-ésimo fallo de ese mensaje.
const fallos = Number(String(props.getProperty(P_FALLOS + id) || '0').split('|')[0]) + 1;
props.setProperty(P_FALLOS + id, fallos + '|' + Date.now());
if (fallos === MAX_FALLOS) {
avisar_('Fallo repetido al crear el ticket', asunto, remite, codigo, datos.message, false);
}
return { llamada: true, detener: false };
}
/** POST JSON a la API. Devuelve {codigo, datos}; codigo 0 = error de red. */
function llamarApi_(cfg, payload) {
try {
const resp = UrlFetchApp.fetch(cfg.url, {
method: 'post',
contentType: 'application/json; charset=utf-8',
headers: { 'X-Api-Key': cfg.clave },
payload: JSON.stringify(payload),
muteHttpExceptions: true,
followRedirects: false
});
let datos = {};
try { datos = JSON.parse(resp.getContentText()) || {}; } catch (e) { datos = {}; }
return { codigo: resp.getResponseCode(), datos: datos };
} catch (e) {
console.warn('Error de red llamando a la API: ' + e.message);
return { codigo: 0, datos: {} };
}
}
function leerConfiguracion_() {
const props = PropertiesService.getScriptProperties();
const url = (props.getProperty('API_URL') || '').trim();
const clave = (props.getProperty('API_KEY') || '').trim();
if (!/^https:\/\//i.test(url) || clave.length < 32) return null;
return { url: url, clave: clave };
}
function dentroDeHorario_() {
const ahora = new Date();
const hora = Number(Utilities.formatDate(ahora, 'Europe/Madrid', 'H'));
const dia = Number(Utilities.formatDate(ahora, 'Europe/Madrid', 'u')); // 1 = lunes ... 7 = domingo
if (SOLO_LABORABLES && dia > 5) return false;
return hora >= HORA_INICIO && hora < HORA_FIN;
}
/**
* Limpieza prudente de firma y citas: solo busca el corte en el último 45 %
* de las líneas y nunca deja menos del 40 % del texto. El cuerpo completo se
* envía siempre aparte (cuerpo_original).
*/
function limpiarFirma_(texto) {
if (!texto) return { cuerpo: '', firma: '' };
const lineas = texto.split('\n');
const desde = Math.floor(lineas.length * 0.55);
const marcas = [
/^--\s*$/,
/^Enviado desde mi\b/i,
/^Sent from my\b/i,
/^El .{3,200} escribió:\s*$/i,
/^On .{3,200} wrote:\s*$/i,
/^De:\s.+/i,
/^From:\s.+/i,
/^-{2,}\s*Mensaje original\s*-{2,}/i,
/^-{2,}\s*Original Message\s*-{2,}/i,
/^-{2,}\s*Forwarded message\s*-{2,}/i,
/^-{2,}\s*Mensaje reenviado\s*-{2,}/i,
/^(AVISO LEGAL|CONFIDENCIALIDAD|Este mensaje.*confidencial)/i
];
let corte = -1;
for (let i = desde; i < lineas.length && corte < 0; i++) {
const l = lineas[i].trim();
if (marcas.some(r => r.test(l))) {
corte = i;
} else if (/^(un )?(cordial )?saludos?[.,]?$|^atentamente[.,]?$/i.test(l)) {
const resto = lineas.slice(i + 1).filter(x => x.trim() !== '');
if (resto.length > 0 && resto.length <= 8) corte = i + 1; // la despedida se conserva
}
}
if (corte < 0) return { cuerpo: texto, firma: '' };
const cuerpo = lineas.slice(0, corte).join('\n').trim();
if (cuerpo.length < texto.length * 0.4) return { cuerpo: texto, firma: '' };
return { cuerpo: cuerpo, firma: lineas.slice(corte).join('\n').trim() };
}
function esRebote_(remite) {
return /(^|[^a-z])(mailer-daemon|postmaster)@/i.test(remite);
}
function esAutomatico_(m, asunto) {
if (/^(Respuesta automática|Automatic reply|Auto:|Out of Office|Fuera de la oficina|Autoreply)/i.test(asunto)) return true;
const auto = (m.getHeader('Auto-Submitted') || '').toLowerCase().trim();
if (auto !== '' && auto !== 'no') return true;
const prec = (m.getHeader('Precedence') || '').toLowerCase();
return prec === 'auto_reply';
}
function extraerEmail_(desde) {
const r = /<([^>]+)>/.exec(desde || '');
return (r ? r[1] : (desde || '')).trim().toLowerCase();
}
function extraerNombre_(desde) {
const r = /^\s*"?([^"<]*)"?\s*</.exec(desde || '');
return r ? r[1].trim() : '';
}
function obtenerEtiqueta_(nombre) {
return GmailApp.getUserLabelByName(nombre) || GmailApp.createLabel(nombre);
}
/** Recorre los hilos de la bandeja de entrada de la ventana (con o sin etiqueta). */
function recorrerHilos_(fn) {
const consulta = 'in:inbox newer_than:' + DIAS_BUSQUEDA + 'd';
for (let inicio = 0; inicio < MAX_HILOS; inicio += 100) {
const hilos = GmailApp.search(consulta, inicio, 100);
hilos.forEach(fn);
if (hilos.length < 100) break;
}
}
function hiloEtiquetado_(h) {
return h.getLabels().some(l => TODAS_ETIQUETAS.indexOf(l.getName()) >= 0);
}
/** Resultado definitivo: etiqueta el hilo, registra el mensaje y lo cierra. */
function definitivo_(m, etiqueta, props, id, resultado) {
m.getThread().addLabel(etiqueta);
registrar_(props, id, resultado);
cerrarProcesado_(m, props);
}
/**
* Marca el mensaje como leído y archiva el hilo si no quedan otros mensajes
* sin registrar. Solo se llama tras un resultado definitivo, nunca en reintentos.
*/
function cerrarProcesado_(m, props) {
try { m.markRead(); } catch (e) { /* no bloquear */ }
try {
const h = m.getThread();
const id = m.getId();
const quedan = h.getMessages().some(x => {
if (x.getId() === id || x.isInTrash()) return false;
return !props.getProperty(P_PROCESADO + x.getId());
});
if (!quedan) h.moveToArchive();
} catch (e) { /* no bloquear */ }
}
/** T ticket/duplicado, C comentario, N no procesado, O propio, I previo a la instalación. */
function registrar_(props, id, resultado) {
props.setProperty(P_PROCESADO + id, resultado + '|' + Date.now());
}
/** Borra registros y contadores más antiguos que RETENCION_DIAS. */
function podar_(props, todas) {
const limite = Date.now() - RETENCION_DIAS * 86400000;
Object.keys(todas).forEach(k => {
if (k.indexOf(P_PROCESADO) !== 0 && k.indexOf(P_FALLOS) !== 0) return;
const t = Number(String(todas[k]).split('|')[1] || 0);
if (!t || t < limite) props.deleteProperty(k);
});
}
/** Aviso único por motivo hasta que vuelva a haber una respuesta correcta. */
function avisarUnaVez_(props, motivo, titulo, asunto, remite, codigo, mensaje) {
const k = P_AVISO + motivo.replace(/[^a-z0-9_@.\-]/gi, '_').substring(0, 80);
if (props.getProperty(k)) return;
props.setProperty(k, String(Date.now()));
avisar_(titulo, asunto, remite, codigo, mensaje, false);
}
function limpiarAvisos_(props) {
props.getKeys().filter(k => k.indexOf(P_AVISO) === 0).forEach(k => props.deleteProperty(k));
}
function avisar_(titulo, asunto, remite, codigo, mensaje, noProcesado) {
console.warn(titulo + ' (HTTP ' + (codigo || 'sin respuesta') + ')');
const dest = (PropertiesService.getScriptProperties().getProperty('AVISO_EMAIL') || '').trim();
if (!dest) return;
MailApp.sendEmail(dest, '[ProyectoA Help Desk] ' + titulo,
'Asunto: ' + asunto + '\nRemitente: ' + remite +
'\nRespuesta HTTP: ' + (codigo || 'sin respuesta') +
(mensaje ? '\nMensaje de la API: ' + mensaje : '') +
(noProcesado
? '\n\nEl correo se ha etiquetado como ' + ETIQUETA_NO_PROCESADO + ' y no se reintentará.'
: '\n\nEl correo no se ha etiquetado y se reintentará en cada ciclo; no se enviarán más avisos por este motivo hasta que vuelva a funcionar.'));
}
8.1. Explicación de cada función
| Función | Qué hace |
|---|---|
instalarActivador() | Elimina activadores previos de procesarBuzon, crea las tres etiquetas, ejecutar la siembra y crea el activador de tiempo cada 5 minutos. Idempotente. |
desinstalarActivador() | Detiene el proceso eliminando el activador; conserva el registro. |
probarConexion() | Primera ejecución manual: provoca la pantalla de permisos y llama a ping. |
sembrarBandejaActual() | Opcional: registra como ya tratados todos los mensajes de los últimos 7 días sin llamar a la API, para no convertir en tickets el correo antiguo en la primera instalación. |
sembrarProcesados_() | Registra los mensajes de hilos que ya tienen etiqueta proyectoa-* (reinstalaciones). |
procesarBuzon() | Función del activador: comprueba el horario, toma LockService.getScriptLock(), reúne los mensajes pendientes (en la bandeja de entrada, fuera de la papelera y sin p_<id>), los ordena del más antiguo al más reciente y procesa como máximo 20 por ejecución. Al final poda el registro. |
procesarMensaje_() | Decide para un mensaje: propio → registro O sin etiqueta; rebote o respuesta automática → proyectoa-no-procesado; resto → llamada a la API e interpretación de la respuesta. |
llamarApi_() | UrlFetchApp.fetch con muteHttpExceptions (para leer los 4xx/5xx) y sin seguir redirecciones. Devuelve código 0 ante errores de red. |
leerConfiguracion_() | Lee API_URL (debe empezar por https://) y API_KEY (mínimo 32 caracteres). |
dentroDeHorario_() | Ventana HORA_INICIO ≤ hora < HORA_FIN en Europe/Madrid y, opcionalmente, solo de lunes a viernes. |
limpiarFirma_() | Limpieza prudente: busca marcadores de firma o cita (--, «Enviado desde mi», «El … escribió:», «De:», «Mensaje original», avisos legales…) solo en el último 45 % de las líneas; las despedidas solo cortan si debajo quedan 1–8 líneas; y si el resultado tuviera menos del 40 % del original, no corta. |
esRebote_() / esAutomatico_() | Detectan mailer-daemon/postmaster, asuntos de fuera de la oficina, la cabecera Auto-Submitted distinta de no y Precedence: auto_reply. |
definitivo_() / cerrarProcesado_() | Etiquetan el hilo, registran p_<id>, marcan el mensaje como leído y archivan el hilo solo si no le quedan mensajes sin registrar. |
registrar_() / podar_() | Escriben p_<id> = resultado|epoch (T, C, N, O, I) y borran los registros y contadores de más de 10 días. |
avisarUnaVez_() / limpiarAvisos_() / avisar_() | Avisos por correo con marcar aviso_<motivo>: uno por causa hasta que vuelve a haber una respuesta correcta. |
8.2. Por qué se trabaja por mensaje y con Propiedades del script
La búsqueda in:inbox newer_than:7d no excluye los hilos ya etiquetados: si se excluyeran, una respuesta nueva en un hilo con proyectoa-procesado nunca se convertiría en comentario. El registro p_<id> (unos 30 bytes por mensaje) se poda a los 10 días, más que la ventana de búsqueda, de modo que un mensaje nunca vuelve a enviarse; y aunque se enviase, la API respondería duplicado. Las Propiedades del script admiten unos 500 KB en total, suficiente para miles de mensajes.
9. Propiedades del script
En Configuración del proyecto → Propiedades del script → Añadir propiedad del script, crea:
| Propiedad | Obligatoria | Valor de ejemplo |
|---|---|---|
API_URL | Sí | https://apireproyectoa.ajpdsoft.com/api.php |
API_KEY | Sí | La clave de 64 caracteres hexadecimales (en claro). Nunca en el código. |
AVISO_EMAIL | No | Dirección del equipo de IT que recibe los avisos (si falta, solo se registran en Ejecuciones). |
No edites a mano las propiedades internas p_…, f_… y aviso_…: las gestiona el script. Ten en cuenta que cualquier persona con acceso de edición al proyecto puede leer las propiedades, así que no compartas el proyecto.
10. Primera ejecución y autorización
- En el editor, elige la función probarConexion en el desplegable y se se pulsa Ejecutar.
- Si aparece «Se requiere autorización», se se pulsa Revisar permisos y se elige la cuenta
bas@ajpdsoft.com. - Como es un proyecto interno no verificado, Google muestra «Google no ha verificado esta aplicación». Pulsar Configuración avanzada → Ir a Buzón bas → ProyectoA Help Desk (no seguro). En dominios de Google Workspace con el proyecto en el mismo dominio este aviso puede no aparecer.
- Se revisa la lista de permisos (leer, redactar y gestionar el correo; conectarse a un servicio externo; permitir que se ejecute cuando el usuario no esté presente; enviar correo en nombre del usuario; ver la dirección de correo) y se se pulsa Permitir. En pantallas con casillas, márcalas todas: el script necesita todos los ámbitos.
- En el Registro de ejecución debe aparecer
HTTP 200 {"ok":true,"pong":true,...,"servicio_activo":true}. Si ves 401, revisar la clave y el hash; siservicio_activoesfalse, revisar el parámetrohelpdesk_email_activo.
Primera instalación: el script procesará todos los correos de los últimos 7 días que sigan en la bandeja de entrada. Si no quieres convertirlos en tickets, ejecutar una vez
sembrarBandejaActualantes de instalar el activador.
11. Instalación del activador
- Selecciona la función instalarActivador y se se pulsa Ejecutar.
- Abre Activadores (icono del reloj, a la izquierda). Debe existir un único activador: función
procesarBuzon, implementación «Principal», origen «Basado en el tiempo», tipo «Temporizador por minutos», «Cada 5 minutos». - En Gmail comprueba que se han creado las etiquetas
proyectoa-procesado,proyectoa-comentarioyproyectoa-no-procesado. - Dentro del horario, esperar al siguiente ciclo o ejecutar
procesarBuzona mano y revisar Ejecuciones: estado «Completado» y líneas del tipoT usuario@cliente.es "Asunto" -> 1185.
El activador se dispara también fuera del horario, pero procesarBuzon() termina inmediatamente, con un consumo despreciable de la cuota diaria de tiempo de ejecución de activadores (consulta los límites vigentes de el tipo de cuenta en la página de cuotas de Apps Script). Cada ejecución está limitada a 20 llamadas a la API y 500 hilos revisados, muy por debajo del máximo de 6 minutos por ejecución.
12. Etiquetas, marcado como leído y archivado
| Situación | Etiqueta del hilo | Leído y archivado | Reintento |
|---|---|---|---|
| Ticket creado o duplicado de un ticket | proyectoa-procesado | Sí | No |
| Comentario añadido (o duplicado de comentario) | proyectoa-comentario | Sí | No |
| Remitente excluido, cuenta propia (según la API), rebote, respuesta automática, error de validación 4xx | proyectoa-no-procesado | Sí | No |
| Correo enviado por la propia cuenta del buzón | (ninguna) | Sí | No |
| 503 configuración, 500, 401, 403, 429, error de red | (ninguna) | No | Sí, en el siguiente ciclo |
El archivado es por hilo y solo se hace si el hilo no tiene otros mensajes pendientes, para que nunca desaparezca de la bandeja de entrada un correo que aún no se ha tratado. Si un usuario responde a un hilo archivado, Gmail lo devuelve a la bandeja de entrada y el mensaje nuevo se procesa. El script nunca responde a los remitentes: así se evitan bucles con autorespondedores; si quieres notificar al usuario el número de ticket, hazlo desde ProyectoA Help Desk incluyendo [TICKET-n] en el asunto, y sus respuestas entrarán como comentarios.
13. Escenarios de prueba
| Prueba | Cómo | Resultado esperado |
|---|---|---|
| Ticket nuevo | Envía desde una cuenta externa un correo a bas@ajpdsoft.com. | Ticket nuevo en incidencia con codusuarioa = usuario api, cliente resuelto y el bloque «Datos del correo»; hilo con proyectoa-procesado, leído y archivado. |
| Comentario | Responde con [TICKET-1185] en el asunto (p. ej. «RE: Impresora [TICKET-1185]»). | Fila nueva en incidencia_comentario con codigoincidencia = 1185; hilo con proyectoa-comentario. |
| Referencia inexistente | Usa [TICKET-999999]. | Se crea un ticket nuevo. |
| Duplicado | Borra la propiedad p_<id> del mensaje y devuélvelo a la bandeja de entrada. | La API responde duplicado:true y no crea nada; el hilo vuelve a quedar procesado. |
| Excluido | Añade el remitente (o @dominio) a helpdesk_email_excluidos y envía un correo. | skipped:true; hilo con proyectoa-no-procesado; nada en la BD. |
| Respuesta automática | Activa la respuesta automática en la cuenta de prueba. | proyectoa-no-procesado sin llamar a la API. |
| Servicio inactivo | Pon helpdesk_email_activo = N y envía un correo. | 503 configuracion; correo sin etiqueta en la bandeja de entrada; un solo aviso por correo. Al volver a S se procesa solo. |
| Clave incorrecta | Cambia un carácter de API_KEY. | 401; correo sin etiqueta; aviso único. Restaura la clave antes de 10 fallos o la IP quedará bloqueada 15 minutos (429). |
| Concurrencia | Lanza cinco peticiones curl simultáneas con el mismo message_id. | Un 201 y cuatro 200 con duplicado:true; un único ticket en la BD. |
Consultas útiles para revisar los resultados:
-- Últimos tickets creados por correo
SELECT i.codigo, i.fechaa, i.asunto, i.contacto, i.codigocliente, i.codigocontacto
FROM incidencia i
JOIN usuario u ON u.codigo = i.codusuarioa AND u.nick = 'api'
ORDER BY i.codigo DESC LIMIT 20;
-- Últimos comentarios recibidos por correo
SELECT codigo, codigoincidencia, fechaa, remitente
FROM incidencia_comentario WHERE origen = 'email'
ORDER BY codigo DESC LIMIT 20;
-- ¿Se procesó este correo? (Message-ID en minúsculas y sin < >)
SELECT codigo FROM incidencia
WHERE incidencia LIKE '%Message-ID: <caj9an6n...@mail.gmail.com>%';
14. Resolución de problemas
| Síntoma | Causa probable | Solución |
|---|---|---|
probarConexion devuelve HTTP 401 | Clave distinta, hash calculado con echo o token vacío | Recalcular el hash con printf '%s', revisar LENGTH(token) = 64 y el nick del parámetro helpdesk_email_usuario_api. |
| HTTP 403 | Usuario api inactivo o token_expiracion vencido | UPDATE usuario SET activo='S', token_expiracion=NULL WHERE nick='api' (o una fecha futura). |
HTTP 429 bloqueo_ip | Más de 10 fallos de clave en 15 minutos desde las IP de Google | Corregir la clave, esperar 15 minutos o borra los ficheros fallos_*.json del directorio de estado. |
HTTP 503 configuracion | Servicio desactivado, estado/tipo vacíos, técnico o cliente por defecto inexistentes (campo causa) | Revisa los parámetros de la categoría «Help Desk email». |
HTTP 503 bd | La API no conecta a MySQL | Credenciales de config.php o variables del pool, servicio MySQL y permisos del usuario de BD. |
| HTTP 500 repetido | Error de inserción (columna NOT NULL sin valor, tipo distinto, modo estricto) | Revisa el log de PHP-FPM ([helpdesk] ...) y adaptar hd_insertar_ticket() al esquema. |
Acentos incorrectos o ? en los tickets | Mezcla de criterios de juego de caracteres | Revisa el apartado 5.3: compara HEX() de tickets antiguos y nuevos y elige utf8mb4 o latin1 en config.php. Los emojis se guardan como ? por diseño. |
| «Exception: The script does not have permission… Required permissions: https://mail.google.com/» | Algún método de GmailApp exige el ámbito completo de Gmail en el dominio | Sustituir gmail.modify por https://mail.google.com/ en appsscript.json, guarda y vuelve a ejecutar probarConexion para reautorizar. |
| Los correos se quedan en la bandeja sin etiqueta | Reintentos (configuración, clave, red) o fuera de horario | Revisa el correo de aviso y Ejecuciones; comprueba HORA_INICIO/HORA_FIN. |
| No se ejecutar nada | No hay activador o el proyecto pertenece a otra cuenta | Activadores: debe existir procesarBuzon cada 5 minutos creado por bas@ajpdsoft.com; ejecutar instalarActivador. |
| Una respuesta crea ticket en vez de comentario | La referencia no tiene el formato exacto | Debe ser [TICKET-n] con corchetes; n debe existir en incidencia.codigo. |
| «Service invoked too many times» o cuotas | Cuota diaria de UrlFetch, MailApp o tiempo de activadores | Reducir la ventana horaria o MAX_POR_EJECUCION; revisar la cuota de la edición de Google correspondiente. |
| Reprocesar un correo concreto | — | Devolverlo a la bandeja de entrada y borrar su propiedad p_<id>; la deduplicación evita duplicar si ya generó ticket. |
15. Consideraciones de seguridad
- Secreto fuera del código: la clave solo está en las Propiedades del script y en el gestor de contraseñas; en la BD, solo su SHA-256. Rótala al menos una vez al año y ante cualquier sospecha: genera una nueva, actualiza
API_KEY, ejecutar elUPDATEdel usuario api y prueba conping. - Comparación en tiempo constante (
hash_equals) y fallo seguro: sin un hash válido de 64 caracteres no se acepta ninguna petición. - Fuerza bruta: 10 fallos por IP en 15 minutos → 429, más
limit_reqen nginx. - Inyección SQL: todas las consultas usan sentencias preparadas con prepares nativos; los únicos valores interpolados son enteros forzados con
(int)y nombres de columna de una lista blanca. - Errores genéricos: las respuestas no exponen detalles internos y el log no contiene ni la clave ni el contenido de los correos.
- Confianza en el remitente: la API cree el campo
remitenteque envía el script, así que quien tenga la clave puede crear tickets en nombre de cualquiera. Custodia la clave. La suplantación del remitente en el propio correo la mitigan SPF, DKIM y DMARC de Google. - Datos personales: el ticket guarda el correo completo, que puede contener datos personales; aplícale la misma política de conservación y acceso que al resto de la aplicación.
- Mínimo privilegio: el script no tiene credenciales de BD; el usuario de BD de la API solo necesita SELECT, INSERT y UPDATE; el usuario api no puede iniciar sesión en la web.
- Bucles de correo: el script nunca responde, ignora los correos propios, los rebotes y las respuestas automáticas, y la API limita las operaciones por remitente y hora.
16. Conclusión
Con unas 430 líneas de Apps Script y unas 850 de PHP hemos convertido un buzón de correo en un canal de entrada de tickets robusto: idempotente gracias al Message-ID y a GET_LOCK, tolerante a fallos porque nunca da por tratado un correo ante un error transitorio, prudente con el contenido porque guarda siempre el original, y seguro porque cada pieza tiene solo los permisos que necesita. El mismo patrón —un procesador del buzón sin acceso a datos y una API pequeña y bien acotada— sirve para cualquier otra herramienta de help desk: basta con adaptar hd_insertar_ticket() y hd_insertar_comentario() a su esquema.
Como siguientes pasos puedes añadir una tabla de auditoría para registrar cada llamada, adjuntar los ficheros del correo (GmailMessage.getAttachments()) enviándolos en Base64 a una acción nueva de la API, o notificar al usuario desde ProyectoA Help Desk el número [TICKET-n] para que sus respuestas entren directamente como comentarios.