|
select
`vtiger_products`.`productname` AS `productname`,
`vtiger_itemsdagnos`.`clasedagno` AS `clasedagno`,
`vtiger_itemsdagnos`.`estadodagno` AS `estadodagno`,
date_format(`vtiger_crmentity`.`createdtime`, '%Y%m') AS `agno_mes`,
`vtiger_incidentesdagnosgrouprel`.`groupname` AS `groupname`,
#01 (
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`,
`vtiger_itemsdagnos`.`cantidad` AS `num_repuestos`,
`vtiger_itemsdagnos`.`valoritemdagno` AS `valor_total`,
(
`vtiger_itemsdagnos`.`cantidad` * `vtiger_itemsdagnos`.`valoritemdagno`
)
AS `Valor_Total_Dagno`,
(
`vtiger_itemsdagnos`.`cantidad` * `vtiger_products`.`unit_price`
)
AS `Valor_Total_Producto`
from
(
left join
on((`vtiger_crmentity`.`crmid` = `vtiger_itemsdagnos`.`itemsdagnosid`)))
left join
on((`vtiger_incidentesdagnos`.`incidentesdagnosid` = `vtiger_itemsdagnos`.`incidentedgn`)))
left join
`vtiger_crmentity` `crmentrega`
on((`vtiger_incidentesdagnos`.`quentrega` = `crmentrega`.`crmid`)))
left join
on((`vtiger_itemsdagnos`.`tipodagno` = `vtiger_products`.`productid`)))
left join
on((`vtiger_incidentesdagnos`.`incidentesdagnosid` = `vtiger_incidentesdagnosgrouprel`.`incidentesdagnosid`))
)
where
(
`crmentrega`.`setype` <> 'PacientesII'
)
order by
`agno_mes` desc
| 
