116 lines
4.5 KiB
JavaScript
116 lines
4.5 KiB
JavaScript
const db = require('../config/db');
|
|
|
|
const Ticket = {
|
|
findAll: async (companyId) => {
|
|
const [rows] = await db.query(`
|
|
SELECT t.*, u.nombre as usuario_nombre, d.nombre as departamento_nombre,
|
|
a.nombre as asignado_nombre
|
|
FROM tickets t
|
|
LEFT JOIN users u ON t.user_id = u.id
|
|
LEFT JOIN departments d ON t.department_id = d.id
|
|
LEFT JOIN users a ON t.assigned_to = a.id
|
|
WHERE t.company_id = ?
|
|
ORDER BY t.created_at DESC
|
|
`, [companyId]);
|
|
return rows;
|
|
},
|
|
findByUser: async (companyId, userId) => {
|
|
const [rows] = await db.query(`
|
|
SELECT t.*, d.nombre as departamento_nombre, a.nombre as asignado_nombre
|
|
FROM tickets t
|
|
LEFT JOIN departments d ON t.department_id = d.id
|
|
LEFT JOIN users a ON t.assigned_to = a.id
|
|
WHERE t.company_id = ? AND t.user_id = ?
|
|
ORDER BY t.created_at DESC
|
|
`, [companyId, userId]);
|
|
return rows;
|
|
},
|
|
findByAgent: async (companyId, agentId) => {
|
|
const [rows] = await db.query(`
|
|
SELECT t.*, u.nombre as usuario_nombre, d.nombre as departamento_nombre
|
|
FROM tickets t
|
|
LEFT JOIN users u ON t.user_id = u.id
|
|
LEFT JOIN departments d ON t.department_id = d.id
|
|
WHERE t.company_id = ? AND t.assigned_to = ?
|
|
ORDER BY t.created_at DESC
|
|
`, [companyId, agentId]);
|
|
return rows;
|
|
},
|
|
findById: async (id) => {
|
|
const [rows] = await db.query(`
|
|
SELECT t.*, u.nombre as usuario_nombre, u.email as usuario_email,
|
|
d.nombre as departamento_nombre, a.nombre as asignado_nombre
|
|
FROM tickets t
|
|
LEFT JOIN users u ON t.user_id = u.id
|
|
LEFT JOIN departments d ON t.department_id = d.id
|
|
LEFT JOIN users a ON t.assigned_to = a.id
|
|
WHERE t.id = ?
|
|
`, [id]);
|
|
return rows[0];
|
|
},
|
|
create: async (companyId, userId, departmentId, asunto, mensaje, prioridad = 'media', extra = {}) => {
|
|
const {
|
|
origen = 'web',
|
|
origen_externo_id = null,
|
|
app_origen = null,
|
|
solicitante_nombre = null,
|
|
solicitante_email = null,
|
|
solicitante_telefono = null,
|
|
tipo = 'consulta'
|
|
} = extra;
|
|
|
|
const [result] = await db.query(
|
|
`INSERT INTO tickets
|
|
(company_id, user_id, department_id, asunto, mensaje, prioridad, origen, origen_externo_id, app_origen, solicitante_nombre, solicitante_email, solicitante_telefono, tipo)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
[companyId, userId, departmentId, asunto, mensaje, prioridad, origen, origen_externo_id, app_origen, solicitante_nombre, solicitante_email, solicitante_telefono, tipo]
|
|
);
|
|
return result.insertId;
|
|
},
|
|
findByOrigenExterno: async (companyId, origen, origenExternoId) => {
|
|
if (!origenExternoId) return null;
|
|
const [rows] = await db.query(
|
|
'SELECT * FROM tickets WHERE company_id = ? AND origen = ? AND origen_externo_id = ?',
|
|
[companyId, origen, String(origenExternoId)]
|
|
);
|
|
return rows[0] || null;
|
|
},
|
|
update: async (id, data) => {
|
|
const fields = [];
|
|
const values = [];
|
|
for (const [key, value] of Object.entries(data)) {
|
|
fields.push(`${key} = ?`);
|
|
values.push(value);
|
|
}
|
|
if (fields.length === 0) return;
|
|
values.push(id);
|
|
await db.query(`UPDATE tickets SET ${fields.join(', ')} WHERE id = ?`, values);
|
|
},
|
|
getResponses: async (ticketId) => {
|
|
const [rows] = await db.query(`
|
|
SELECT r.*, u.nombre as usuario_nombre, u.rol as usuario_rol
|
|
FROM ticket_respuestas r
|
|
LEFT JOIN users u ON r.user_id = u.id
|
|
WHERE r.ticket_id = ?
|
|
ORDER BY r.created_at ASC
|
|
`, [ticketId]);
|
|
return rows;
|
|
},
|
|
addResponse: async (ticketId, userId, mensaje) => {
|
|
const [result] = await db.query(
|
|
'INSERT INTO ticket_respuestas (ticket_id, user_id, mensaje) VALUES (?, ?, ?)',
|
|
[ticketId, userId, mensaje]
|
|
);
|
|
return result.insertId;
|
|
},
|
|
countByStatus: async (companyId) => {
|
|
const [rows] = await db.query(
|
|
'SELECT status, COUNT(*) as total FROM tickets WHERE company_id = ? GROUP BY status',
|
|
[companyId]
|
|
);
|
|
return rows;
|
|
}
|
|
};
|
|
|
|
module.exports = Ticket;
|