SELECT vtiger_crmentity.createdtime AS 'created_time',
vtiger_comentarios.comentariosid AS 'Comentario_ID',
vtiger_comentarios.consecutivo AS 'consecutivo',
vtiger_familianovedad.familianovedad AS 'familia_novedad',
vtiger_subfamilianovedad.subfamilianovedad AS 'subfamilia_novedad',
vtiger_caso.caso AS 'caso',
vtiger_novedad.novedad AS 'novedad',
vtiger_comentarios.subtipificacion AS 'subtipificacion',
vtiger_comentarios.asuntocomentario AS 'asunto_comentario',
vtiger_crmentity.description AS 'descripcion',
#01 (SELECT vtiger_users.user_name
FROM vtiger_users
WHERE vtiger_crmentity.smcreatorid = vtiger_users.id) AS 'Creador_Novedad',
#02 (SELECT vtiger_users.user_name
FROM vtiger_users
WHERE vtiger_errores.responsable = vtiger_users.id) AS 'Responsable_Error',
#03 (SELECT vtiger_crmentity.setype
WHERE vtiger_crmentity.crmid = vtiger_comentarios.relacionadocon) AS 'ModuloRelacionado',
(CASE
WHEN ModuloRelacionado = 'PacientesII' THEN
(SELECT vtiger_pacientesii.identificador_paciente
WHERE vtiger_pacientesii.pacientesiiid = vtiger_comentarios.relacionadocon)
WHEN ModuloRelacionado = 'Visitas' THEN
(SELECT vtiger_visitas.visita
WHERE vtiger_visitas.visitasid = vtiger_comentarios.relacionadocon)
WHEN ModuloRelacionado = 'OrdenesServicio' THEN
(SELECT vtiger_ordenesservicio.asunto_orden
WHERE vtiger_ordenesservicio.ordenesservicioid = vtiger_comentarios.relacionadocon)
WHEN ModuloRelacionado = 'PlanillasTrabajo' THEN
(SELECT vtiger_planillastrabajo.identificador
WHERE vtiger_planillastrabajo.planillastrabajoid = vtiger_comentarios.relacionadocon)
WHEN ModuloRelacionado = 'PlanillasTrabajo' THEN
(SELECT vtiger_planillastrabajo.identificador
FROM vtiger_planillastrabajo
WHERE vtiger_planillastrabajo.planillastrabajoid = vtiger_comentarios.relacionadocon)
WHEN ModuloRelacionado = 'Comentarios' THEN
(SELECT vtiger_comentarios.asuntocomentario
FROM vtiger_comentarios B
WHERE B.comentariosid = vtiger_comentarios.relacionadocon)
WHEN ModuloRelacionado = 'Contratos' THEN
(SELECT vtiger_contratosdecomodato.asunto
WHERE vtiger_contratosdecomodato.contratosdecomodatoid = vtiger_comentarios.relacionadocon)
ELSE ModuloRelacionado
#04 END) AS 'Relacionado_con',
vtiger_comentarios.relacionadocon AS 'Relacionado_ID',
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_comentarios.comentariosid = vtiger_crmentity.crmid
LEFT JOIN vtiger_familianovedad ON vtiger_comentarios.familianovedad = vtiger_familianovedad.picklist_valueid
LEFT JOIN vtiger_caso ON vtiger_confignovedades.caso = vtiger_caso.picklist_valueid
LEFT JOIN vtiger_novedad ON vtiger_confignovedades.novedad = vtiger_novedad.picklist_valueid
LEFT JOIN vtiger_errores ON vtiger_comentarios.comentariosid = vtiger_errores.novedadrel
WHERE vtiger_comentarios.familianovedad = 66847
AND vtiger_novedad.picklist_valueid != 68335
AND vtiger_crmentity.createdtime >= CURDATE() - interval 90 DAY
ORDER BY vtiger_crmentity.createdtime;
|