|
SELECT vtiger_errores.erroresid AS ERROR_ID,
COALESCE(vtiger_comentarios.asuntocomentario, vtiger_errores.asuntoerror) AS ASUNTO,
vtiger_errores.consecutivo AS CONSECUTIVO,
vtiger_errores.tipoerror AS ERROR_TIPO,
vtiger_errores.relacionadocon AS RELACIONADO_CON,
vtiger_errores.ponderacion AS PONDERACION,
vtiger_errores.peso AS PESO,
vtiger_errores.estadoerror AS ESTADO,
vtiger_configerrores.tipo_error as DESC_ERROR_TIPO,
vtiger_errores.atenuante AS ATENUANTE,
vtiger_errores.tipoerror_conf AS TIPO_DE_ERROR,
vtiger_errores.novedadrel AS NOVEDAD_RELACIONADA,
vtiger_comentarios.adjunto AS ADJUNTOS,
vtiger_errores.responsable AS RESPONSABLE_ID,
vtiger_comentarios.asuntocomentario AS ASUNTONOVEDADRELACIONADA,
vtiger_crmentity.createdtime AS FECHACREACIONERROR,
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`,
#01 CASE
WHEN vtiger_users.id IS NULL
AND vtiger_vendor.vendorid IS NULL
AND vtiger_pacientesii.pacientesiiid IS NULL THEN 'NO REGISTRA'
WHEN vtiger_users.id IS NULL
AND vtiger_vendor.vendorid IS NULL THEN CONCAT('PAC ', vtiger_pacientesii.firstname, " ", vtiger_pacientesii.lastname)
WHEN vtiger_users.id IS NULL
AND vtiger_pacientesii.pacientesiiid IS NULL THEN CONCAT('VEND ', vtiger_vendor.vendorname)
WHEN vtiger_pacientesii.firstname IS NULL
AND vtiger_vendor.vendorname IS NULL THEN CONCAT('USER ', vtiger_users.first_name, " ", vtiger_users.last_name)
ELSE 'NO REGISTRA'
END AS NOMBRE_RESPONSABLE,
#02 (SELECT vtiger_crmentity.setype
FROM vtiger_crmentity
WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) AS 'MODULORELACIONADO',
#03 (CASE
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%PacientesII%' THEN
(SELECT vtiger_pacientesii.identificador_paciente
FROM vtiger_pacientesii
WHERE vtiger_pacientesii.pacientesiiid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Visitas%' THEN
(SELECT vtiger_visitas.visita
FROM vtiger_visitas
WHERE vtiger_visitas.visitasid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%OrdenesServicio%' THEN
(SELECT CONCAT(vtiger_ordenesservicio.asunto_orden, ' NUM: ', vtiger_ordenesservicio.numero_orden)
FROM vtiger_ordenesservicio
WHERE vtiger_ordenesservicio.ordenesservicioid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%PlanillasTrabajo%' THEN
(SELECT vtiger_planillastrabajo.identificador
FROM vtiger_planillastrabajo
WHERE vtiger_planillastrabajo.planillastrabajoid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%ActivosRetornables%' THEN
(SELECT vtiger_activosretornables.nombre_activo
FROM vtiger_activosretornables
WHERE vtiger_activosretornables.activosretornablesid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Cilindros%' THEN
(SELECT CONCAT(vtiger_cilindros.tipogases, " - Lote: ", vtiger_cilindros.lote)
FROM vtiger_cilindros
WHERE vtiger_cilindros.cilindrosid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%RemisionDeEquipos%' THEN
(SELECT vtiger_remisiondeequipos.idetificador
FROM vtiger_remisiondeequipos
WHERE vtiger_remisiondeequipos.remisiondeequiposid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Recogidas%' THEN
(SELECT vtiger_recogidas.razon_recogida
FROM vtiger_recogidas
WHERE vtiger_recogidas.recogidasid = vtiger_errores.relacionadocon)
/*Mantenimiento -> vtiger_mantenimiento 'Result' object has no attribute 'json_data' */
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Mantenimiento%' THEN
(SELECT vtiger_mantenimiento.asunto_mantenimiento
FROM vtiger_mantenimiento
WHERE vtiger_mantenimiento.mantenimientoid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%RecibosCaja%' THEN
(SELECT CONCAT(vtiger_reciboscaja.asunto_recibo, " CONCEPTO: ", vtiger_reciboscaja.concepto)
FROM vtiger_reciboscaja
WHERE vtiger_reciboscaja.reciboscajaid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Autorizaciones%' THEN
(SELECT vtiger_autorizaciones.identificadorautorizacion
FROM vtiger_autorizaciones
WHERE vtiger_autorizaciones.autorizacionesid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%ContratosDeComodato%' THEN
(SELECT vtiger_contratosdecomodato.asunto
FROM vtiger_contratosdecomodato
WHERE vtiger_contratosdecomodato.contratosdecomodatoid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%ListasTrabajo%' THEN
(SELECT vtiger_listastrabajo.asunto
FROM vtiger_listastrabajo
WHERE vtiger_listastrabajo.listastrabajoid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Fisicos%' THEN
(SELECT vtiger_inventariosfisicos.asunto
FROM vtiger_inventariosfisicos
WHERE vtiger_inventariosfisicos.inventariosfisicosid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Camiones%' THEN
(SELECT vtiger_camiones.nombrecamion
FROM vtiger_camiones
WHERE vtiger_camiones.camionesid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%CuentasCobro%' THEN
(SELECT vtiger_cuentascobro.cuentacobro
FROM vtiger_cuentascobro
WHERE vtiger_cuentascobro.cuentascobroid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Repuestos%' THEN
(SELECT vtiger_repuestos.repuesto
FROM vtiger_repuestos
WHERE vtiger_repuestos.repuestosid = vtiger_errores.relacionadocon)
WHEN (SELECT vtiger_crmentity.setype FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_errores.relacionadocon) LIKE '%Empleados%' THEN
(SELECT vtiger_empleados.identificador_empleado
FROM vtiger_empleados
WHERE vtiger_empleados.empleadosid = vtiger_errores.relacionadocon)
ELSE 'NA'
END) AS 'DETALLEMODULO',
vtiger_crmentity.description AS DESCRIPCIONERROR,
vtiger_crmentity.setype AS TIPORESPONSABLE,
vtiger_crmentity.smcreatorid AS IDCREADOPOR,
CONCAT(creador.first_name," ",creador.last_name) AS CREADOPOR
FROM vtiger_errores
LEFT JOIN vtiger_users ON (vtiger_errores.responsable = vtiger_users.id) LEFT JOIN vtiger_vendor ON (vtiger_errores.responsable = vtiger_vendor.vendorid) LEFT JOIN vtiger_pacientesii ON (vtiger_errores.responsable = vtiger_pacientesii.pacientesiiid) LEFT JOIN vtiger_comentarios ON (vtiger_errores.novedadrel = vtiger_comentarios.comentariosid) LEFT JOIN vtiger_users creador ON creador.id = vtiger_crmentity.smcreatorid
;
| 
