|
SELECT
DATE_FORMAT(NOW(), '%Y%m') AS agno_mes,
yearweek(NOW(), 0) AS agno_semana,
prod.productname AS "INSUMO",
prod.productcategory,
tipoinsumo,
bodega,
tipobodega,
SUM(cantidadnew) AS "BUENOS",
iteminventario,
#01 CASE
WHEN tipobodega = 'Sedes' THEN
(SELECT vtiger_sedes.sede
FROM vtiger_sedes
WHERE vtiger_sedes.sedesid = inv.bodega )
WHEN tipobodega = 'PacientesII' THEN
(SELECT vtiger_pacientesii.identificador_paciente
FROM vtiger_pacientesii
WHERE vtiger_pacientesii.identificador_paciente = inv.bodega )
WHEN tipobodega = 'Vendors' THEN
(SELECT vtiger_vendor.vendorname
FROM vtiger_vendor
WHERE vtiger_vendor.vendorid = inv.bodega )
WHEN tipobodega = 'PlanillasTrabajo' THEN
(SELECT vtiger_planillastrabajo.identificador
FROM vtiger_planillastrabajo
WHERE vtiger_planillastrabajo.planillastrabajoid = inv.bodega)
WHEN tipobodega = 'ActivosRetornables' THEN
(SELECT vtiger_activosretornables.nombre_activo
FROM vtiger_activosretornables
WHERE vtiger_activosretornables.activosretornablesid = inv.bodega )
ELSE 'Otro'
END AS NombreUbicacion,
#02 CASE
WHEN tipobodega = 'Sedes' THEN
(SELECT vtiger_sedescf.cf_2271
FROM vtiger_sedescf
WHERE vtiger_sedescf.sedesid = inv.bodega )
WHEN tipobodega = 'PlanillasTrabajo' THEN
(SELECT vtiger_rutascf.cf_2961
FROM vtiger_planillastrabajo
LEFT JOIN vtiger_rutascf ON vtiger_rutascf.rutasid = vtiger_planillastrabajo.ruta
WHERE vtiger_planillastrabajo.planillastrabajoid = inv.bodega )
WHEN tipobodega = 'PacientesII' THEN
(SELECT acc.groupname
FROM (vtiger_accountgrouprelation acc
JOIN vtiger_pacientesii pac)
WHERE ((pac.pacientesiiid = inv.bodega)
AND (pac.accountid = acc.accountid)))
WHEN tipobodega = 'Vendors' THEN 'CUNDINAMARCA'
WHEN tipobodega = 'ActivosRetornables' THEN 'EQUIPO'
ELSE "."
END AS Regional,
#03 CASE
WHEN tipobodega = 'Sedes' THEN "SEDE"
WHEN tipobodega = 'PlanillasTrabajo' THEN "Planilla"
WHEN tipobodega = 'PacientesII' THEN "Paciente"
WHEN tipobodega = 'ActivosRetornables' THEN "Activo Retornables"
WHEN tipobodega = 'Vendors' THEN "Vendors"
ELSE "."
END AS TipoUbicacion,
#04 CASE
WHEN tipobodega = 'Sedes' THEN
(SELECT vtiger_sedes.clasesede
FROM vtiger_sedes
WHERE vtiger_sedes.sedesid = inv.bodega )
WHEN tipobodega = 'PlanillasTrabajo' THEN
(SELECT vtiger_rutas.tiporuta
FROM vtiger_rutas
WHERE vtiger_rutas.rutasid = inv.bodega )
WHEN tipobodega = 'PacientesII' THEN "Paciente"
WHEN tipobodega = 'ActivosRetornables' THEN
(SELECT vtiger_vendor.category
FROM vtiger_vendor
WHERE vtiger_vendor.vendorid = inv.bodega)
WHEN tipobodega = 'Vendors' THEN
(SELECT vtiger_activosretornables.modeloequipo
FROM vtiger_activosretornables
WHERE vtiger_activosretornables.activosretornablesid = inv.bodega )
ELSE "Otro"
END AS N1,
COUNT(*) AS Cantidad
FROM vtiger_inventarioinsumos inv
INNER JOIN vtiger_products prod ON inv.insumo = prod.productid
INNER JOIN vtiger_crmentity crminfo ON prod.productid = crminfo.crmid
WHERE prod.discontinued = 1
AND inv.tipoinsumo NOT LIKE "%REPU%"
GROUP BY bodega,
prod.productname
ORDER BY createdtime,
cantidadnew DESC
| 
