|
SELECT `vtiger_incidentesdagnos`.`incidentesdagnosid` AS `incidentesdagnosid`,
`vtiger_itemsdagnos`.`itemsdagnosid`,
`vtiger_products`.`productname` AS `productname`,
`vtiger_incidentesdagnos`.`asuntodagno`,
`vtiger_products`.`unit_price` AS `unit_price`,
`vtiger_itemsdagnos`.`clasedagno` AS `clasedagno`,
`vtiger_itemsdagnos`.`cantidad` AS `cantidad`,
`vtiger_itemsdagnos`.`valoritemdagno` AS `valoritemdagno`,
`vtiger_incidentesdagnos`.`empresa` AS `empresa`,
`vtiger_incidentesdagnos`.`estadoincidente` AS `estadoincidente`,
`vtiger_incidentesdagnos`.`valordeuda` AS `valordeuda`,
`vtiger_products`.`productcategory` AS `productcategory`,
`vtiger_crmentity`.`createdtime` AS `createdtime`,
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`,
`vtiger_incidentesdagnos`.`consecutivo` AS `consecutivo`,
`vtiger_itemsdagnos`.`estadodagno` AS `estadodagno`,
`vtiger_incidentesdagnos`.`valoraprobado` AS `valoraprobado`,
`vtiger_incidentesdagnos`.`valorpagado` AS `valorpagado`,
`vtiger_incidentesdagnos`.`consecutivoregional` AS `consecutivoregional`,
`vtiger_itemsdagnos`.`consecutivoitem` AS `consecutivoitem`,
`vtiger_itemsdagnos`.`asunto` AS `asunto`,
`vtiger_incidentesdagnosgrouprel`.`groupname` AS `groupname`,
#01 (CASE
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`quentrega`)) = 'PacientesII') THEN convert(
(SELECT 'Es Paciente'
FROM `vtiger_pacientesii`
WHERE (`vtiger_incidentesdagnos`.`quentrega` = `vtiger_pacientesii`.`pacientesiiid`)) USING latin1)
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`quentrega`)) = 'Empleados') THEN
(SELECT `vtiger_empleadoscf`.`cf_3469`
FROM (`vtiger_empleados`
JOIN `vtiger_empleadoscf` on((`vtiger_empleados`.`empleadosid` = `vtiger_empleadoscf`.`empleadosid`)))
WHERE (`vtiger_incidentesdagnos`.`quentrega` = `vtiger_empleados`.`empleadosid`))
ELSE 'Otra cosa'
END) AS `AreaQEntrega`,
#02 (CASE
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`quentrega`)) = 'PacientesII') THEN convert(
(SELECT 'Paciente no tiene cargo'
FROM `vtiger_pacientesii`
WHERE (`vtiger_incidentesdagnos`.`quentrega` = `vtiger_pacientesii`.`pacientesiiid`)) USING latin1)
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`quentrega`)) = 'Empleados') THEN
(SELECT `vtiger_empleados`.`tipo_empleado`
FROM `vtiger_empleados`
ELSE 'Otra cosa'
END) AS `TipoCargoQEntrega`,
#03 (CASE
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`qrecibe`)) = 'Empleados') THEN
(SELECT `vtiger_empleadoscf`.`cf_3469`
FROM (`vtiger_empleados`
JOIN `vtiger_empleadoscf` on((`vtiger_empleados`.`empleadosid` = `vtiger_empleadoscf`.`empleadosid`)))
WHERE (`vtiger_incidentesdagnos`.`qrecibe` = `vtiger_empleados`.`empleadosid`))
ELSE 'Por especificar'
END) AS `AreaQRecibe`,
#04 (CASE
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`qrecibe`)) = 'Empleados') THEN
(SELECT `vtiger_empleados`.`tipo_empleado`
FROM `vtiger_empleados`
WHERE (`vtiger_incidentesdagnos`.`qrecibe` = `vtiger_empleados`.`empleadosid`))
ELSE 'Por especificar'
END) AS `TipoCargoQRecibe`,
#05 (CASE
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`quentrega`)) = 'PacientesII') THEN
(SELECT `vtiger_pacientesii`.`identificador_paciente`
FROM `vtiger_pacientesii`
WHERE (`vtiger_incidentesdagnos`.`quentrega` = `vtiger_pacientesii`.`pacientesiiid`))
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`quentrega`)) = 'Empleados') THEN
(SELECT `vtiger_empleados`.`identificador_empleado`
FROM `vtiger_empleados`
WHERE (`vtiger_incidentesdagnos`.`quentrega` = `vtiger_empleados`.`empleadosid`))
ELSE 'Otra cosa'
END) AS `quentrega_nombre`,
#06 (CASE
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
(SELECT `vtiger_pacientesii`.`identificador_paciente`
FROM `vtiger_pacientesii`
WHERE (`vtiger_incidentesdagnos`.`qrecibe` = `vtiger_pacientesii`.`pacientesiiid`))
WHEN (
(SELECT `vtiger_crmentity`.`setype`
FROM `vtiger_crmentity`
WHERE (`vtiger_crmentity`.`crmid` = `vtiger_incidentesdagnos`.`qrecibe`)) = 'Empleados') THEN
(SELECT `vtiger_empleados`.`identificador_empleado`
FROM `vtiger_empleados`
WHERE (`vtiger_incidentesdagnos`.`qrecibe` = `vtiger_empleados`.`empleadosid`)) WHEN(
(SELECT vtiger_users.id
FROM vtiger_users
WHERE vtiger_users.id = vtiger_incidentesdagnos.qrecibe) IS NOT NULL) THEN
(SELECT vtiger_users.user_name
FROM vtiger_users
WHERE vtiger_users.id = vtiger_incidentesdagnos.qrecibe)
ELSE 'Por especificar'
END) AS `qrecibe_nombre`,
`vtiger_incidentesdagnos`.`qrecibe` AS `idrecibe`,
`vtiger_incidentesdagnos`.`documentoadmon`,
#07 (SELECT vtiger_crmentity.description
FROM vtiger_crmentity
WHERE vtiger_crmentity.crmid = vtiger_incidentesdagnos.documentoadmon) AS 'Documento_asociado',
`vtiger_incidentesdagnos`.`moduloasociado` AS 'ModuloAsociado'
LEFT JOIN ` vtiger_incidentesdagnos` ON (`vtiger_itemsdagnos`.`incidentedgn` = `vtiger_incidentesdagnos`.`incidentesdagnosid`) LEFT JOIN ` vtiger_products` ON (`vtiger_itemsdagnos`.`tipodagno` = `vtiger_products`.`productid`) LEFT JOIN ` vtiger_crmentity` ON (`vtiger_incidentesdagnos`.`incidentesdagnosid` = `vtiger_crmentity`.`crmid`) Left JOIN ` vtiger_crmentity` `crmitemdagnos` on `vtiger_itemsdagnos`.`itemsdagnosid` = `crmitemdagnos`.`crmid` WHERE `crmitemdagnos`.`deleted` = 0 ;
| 
