|
SELECT vtiger_troubletickets.ticketid AS TICKET_ID,
vtiger_troubletickets.parent_id AS PARENT_ID,
vtiger_crmentity.createdtime AS FECHA_CREACION,
vtiger_ticketcf.cf_3523 AS ATRIBUIBLE_A,
vtiger_ticketcf.cf_3521 AS AREA_RESPONSABLE,
vtiger_ticketcf.cf_1699 AS EPS,
vtiger_ticketcf.cf_1684 AS FORMA_DE_CONTACTO,
vtiger_ticketcf.cf_3519,
vtiger_crmentity.description AS INCIDENCIA,
vtiger_troubletickets.title AS DESCRIPCION_CASO,
vtiger_ticketcf.cf_1860 AS SUB_ESTADO,
#01 (SELECT vtiger_users.user_name FROM vtiger_users WHERE vtiger_users.id = vtiger_crmentity.smcreatorid) AS CREADO_POR_USUARIO,
#02 (SELECT vtiger_users.user_name FROM vtiger_users WHERE vtiger_users.id = vtiger_crmentity.lastmodifiedby) AS ULTIMA_MODIFICACION_POR_USUARIO,
vtiger_troubletickets.fechaactestado AS FECHA_ACT_ESTADO,
vtiger_crmentity.modifiedtime AS FECHA_ULTIMA_MODIFICACION,
#03 CASE
WHEN vtiger_troubletickets.mostrarpaciente = 1 THEN 'SI'
WHEN vtiger_troubletickets.mostrarpaciente = 0 THEN 'NO'
ELSE 'N/A'
END AS MOSTRAR_PACIENTE,
#04 (SELECT GROUP_CONCAT(CONCAT(vtiger_ticketcomments.createdtime, " - ", vtiger_users.first_name, " ", vtiger_users.last_name, " - ", vtiger_ticketcomments.ownerid, ": ", vtiger_ticketcomments.comments) SEPARATOR " --//-- ")
FROM vtiger_ticketcomments
LEFT JOIN vtiger_users ON (vtiger_ticketcomments.ownerid = vtiger_users.id)
WHERE vtiger_ticketcomments.ticketid = vtiger_troubletickets.ticketid) AS HISTORIAL_COMENTARIOS,
vtiger_troubletickets.email_contacto_web AS MAIL,
vtiger_troubletickets.product_id AS PRODUCT_ID,
vtiger_products.productname AS PRODUCT_NAME,
vtiger_troubletickets.priority AS PRIORIDAD,
vtiger_troubletickets.severity AS TIPO,
vtiger_troubletickets.status AS ESTADO,
#05 CASE
WHEN vtiger_troubletickets.filename != ''
AND vtiger_troubletickets.filename IS NOT NULL THEN vtiger_troubletickets.filename
ELSE 'SIN ADJUNTOS'
END AS ARCHIVOS_ADJUNTOS,
vtiger_troubletickets.update_log AS UPDATE_LOG,
vtiger_troubletickets.asunto_gfe_ticket AS CATEGORIA,
vtiger_troubletickets.paciente AS PACIENTE_ID,
vtiger_pacientesii.firstname AS PACIENTE_NOMBRE,
vtiger_pacientesii.lastname AS PACIENTE_APELLIDO,
vtiger_pacientesii.doc_type AS PACIENTE_TIPO_DOC,
vtiger_pacientesii.docid AS PACIENTE_NUM_DOC,
vtiger_troubletickets.nombre_contacto AS NOMBRE_CONTACTO,
vtiger_troubletickets.documento_contacto AS CC_CONTACTO,
vtiger_troubletickets.telefono_contacto AS TEL_CONTACTO,
vtiger_accountgrouprelation.groupname AS REGIONAL,
vtiger_troubletickets.solution AS SOLUCION,
DATE_FORMAT(vtiger_crmentity.createdtime, '%Y%m') AS `agno_mes`,
DATE_FORMAT(vtiger_crmentity.createdtime, '%Y%m%d') AS `agno_mes_dia`,
yearweek(vtiger_crmentity.createdtime, 0) AS `agno_semana`
LEFT JOIN vtiger_crmentity ON (vtiger_troubletickets.ticketid = vtiger_crmentity.crmid) LEFT JOIN vtiger_ticketcf ON (vtiger_troubletickets.ticketid = vtiger_ticketcf.ticketid) LEFT JOIN vtiger_products ON (vtiger_troubletickets.product_id = vtiger_products.productid) LEFT JOIN vtiger_pacientesii ON (vtiger_troubletickets.paciente = vtiger_pacientesii.pacientesiiid) LEFT JOIN vtiger_account ON (vtiger_account.accountid = vtiger_pacientesii.accountid)
LEFT JOIN vtiger_accountgrouprelation ON (vtiger_accountgrouprelation.accountid = vtiger_account.accountid)
WHERE vtiger_troubletickets.severity NOT LIKE '%Llamada%' AND vtiger_crmentity.setype = 'HelpDesk';
| 
