|
SELECT DATE_FORMAT(vtiger_movequipos.mv_date, '%Y%m%d') AS agno_mes_dia,
DATE_FORMAT(vtiger_movequipos.mv_date, '%Y%m') AS agno_mes,
yearweek(vtiger_movequipos.mv_date, 0) AS agno_semana,
activo.subtipo,
#01 CASE
WHEN crminfofuente.setype = 'PacientesII' THEN
CONCAT('Paciente - ', (SELECT acc.groupname
FROM vtiger_accountgrouprelation acc
INNER JOIN vtiger_pacientesii pac ON pac.accountid = acc.accountid
WHERE vtiger_movequipos.mv_fuente = pac.pacientesiiid), ' - ',
(select vtiger_account.epsconsolidada from vtiger_account inner join vtiger_pacientesii on vtiger_pacientesii.accountid = vtiger_account.accountid where vtiger_movequipos.mv_fuente = vtiger_pacientesii.pacientesiiid )
)
WHEN crminfofuente.setype = 'Sedes' THEN
(select vtiger_sedes.sede from vtiger_sedes where vtiger_sedes.sedesid = crminfofuente.crmid)
WHEN crminfofuente.setype = 'PlanillasTrabajo' THEN
(select vtiger_planillastrabajo.identificador from vtiger_planillastrabajo where vtiger_planillastrabajo.planillastrabajoid = crminfofuente.crmid)
WHEN crminfofuente.setype = 'Accounts' THEN
(select vtiger_account.accountname from vtiger_account where vtiger_account.accountid = crminfofuente.crmid)
WHEN crminfofuente.setype = 'Vendors' THEN
(select vtiger_vendor.vendorname from vtiger_vendor where vtiger_vendor.vendorid = crminfofuente.crmid)
END as NombreUbicacionFuente,
CASE
WHEN crminfodestino.setype = 'PacientesII' THEN
CONCAT('Paciente - ', (SELECT acc.groupname
FROM vtiger_accountgrouprelation acc
INNER JOIN vtiger_pacientesii pac ON pac.accountid = acc.accountid
WHERE vtiger_movequipos.mv_destino = pac.pacientesiiid), ' - ',
(select vtiger_account.epsconsolidada from vtiger_account inner join vtiger_pacientesii on vtiger_pacientesii.accountid = vtiger_account.accountid where vtiger_movequipos.mv_destino = vtiger_pacientesii.pacientesiiid )
)
WHEN crminfodestino.setype = 'Sedes' THEN
(select vtiger_sedes.sede from vtiger_sedes where vtiger_sedes.sedesid = crminfodestino.crmid)
WHEN crminfodestino.setype = 'PlanillasTrabajo' THEN
(select vtiger_planillastrabajo.identificador from vtiger_planillastrabajo where vtiger_planillastrabajo.planillastrabajoid = crminfodestino.crmid)
WHEN crminfodestino.setype = 'Accounts' THEN
(select vtiger_account.accountname from vtiger_account where vtiger_account.accountid = crminfodestino.crmid)
WHEN crminfodestino.setype = 'Vendors' THEN
(select vtiger_vendor.vendorname from vtiger_vendor where vtiger_vendor.vendorid = crminfodestino.crmid)
END as NombreUbicacionDestino,
count(*) as Cantidad
left JOIN vtiger_crmentity crminfo ON crminfo.crmid = activo.ubicacion and vtiger_movequipos.mv_date = CURDATE()
LEFT JOIN vtiger_crmentity crminfofuente ON vtiger_movequipos.mv_fuente = crminfofuente.crmid
LEFT JOIN vtiger_crmentity crminfodestino ON vtiger_movequipos.mv_destino = crminfodestino.crmid
Left join vtiger_crmentity crmentity ON vtiger_movequipos.movequiposid = crmentity.crmid
where vtiger_movequipos.mv_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
AND crminfodestino.deleted = 0
group by subtipo, NombreUbicacionFuente, NombreUbicacionDestino
ORDER BY vtiger_movequipos.mv_date DESC
| 
