|
SELECT `prod`.`productid` AS `id_producto`,
`rep`.`repuestosid` AS `id_repuesto`,
`repcf`.`cf_3498` AS `SubUnidad`,
`repcf`.`cf_3514` AS `imagen`,
`rep`.`proveedor` AS `proveedor`,
`rep`.`repuesto` AS `repuesto`,
`rep`.`codigobarras` AS `codigobarras`,
`rep`.`repuestosusados` AS `repuestosusados`,
`rep`.`manufacturer` AS `manufacturer`,
`rep`.`qtydisponible` AS `qtydisponible`,
`rep`.`qtytotal` AS `qtytotal`,
`rep`.`tiporepuesto` AS `tiporepuesto`,
`rep`.`modeloequipo` AS `modeloequipo`,
`repcf`.`cf_1962` AS `Repuesto_Ingles`,
`repcf`.`cf_1964` AS `Precio_USD`,
`repcf`.`cf_1966` AS `Fabricante`,
`repcf`.`cf_3092` AS `Referencia`,
`repcf`.`cf_3094` AS `cf_3094`,
`repcf`.`cf_3492` AS `Serie de Equipo`,
`repcf`.`cf_3494` AS `Tipo_Equipo`,
`repcf`.`cf_3496` AS `Importado`,
`prod`.`productname` AS `productname`,
`prod`.`productcategory` AS `productcategory`,
`inv`.`insumo` AS `insumo`,
`inv`.`tipoinsumo` AS `tipoinsumo`,
`inv`.`bodega` AS `bodega`,
`inv`.`tipobodega` AS `tipobodega`,
`inv`.`cantidadnew` AS `cantidadnew`,
`inv`.`cantidadbaja` AS `cantidadbaja`,
`inv`.`cantidadused` AS `cantidadused`,
`inv`.`cupobroken` AS `cupobroken`,
`inv`.`cupogood` AS `cupogood`,
`inv`.`cupomax` AS `cupomax`,
`inv`.`iteminventario` AS `iteminventario`,
#01 (CASE
WHEN (`inv`.`tipobodega` = 'Sedes') THEN
(SELECT `vtiger_sedes`.`sede`
FROM `vtiger_sedes`
WHERE (`vtiger_sedes`.`sedesid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PacientesII') THEN
(SELECT `vtiger_pacientesii`.`identificador_paciente`
FROM `vtiger_pacientesii`
WHERE (`vtiger_pacientesii`.`identificador_paciente` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'Vendors') THEN
(SELECT `vtiger_vendor`.`vendorname`
FROM `vtiger_vendor`
WHERE (`vtiger_vendor`.`vendorid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PlanillasTrabajo') THEN
(SELECT `vtiger_rutas`.`nombre_ruta`
FROM (`vtiger_planillastrabajo`
JOIN `vtiger_rutas` on((`vtiger_rutas`.`rutasid` = `vtiger_planillastrabajo`.`ruta`)))
WHERE (`vtiger_planillastrabajo`.`planillastrabajoid` = `inv`.`bodega`))
WHEN (`inv`.`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 (`inv`.`tipobodega` = 'Sedes') THEN
(SELECT `vtiger_sedescf`.`cf_2271`
FROM `vtiger_sedescf`
WHERE (`vtiger_sedescf`.`sedesid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PlanillasTrabajo') THEN
(SELECT `vtiger_rutascf`.`cf_2961`
FROM `vtiger_rutascf`
WHERE (`vtiger_rutascf`.`rutasid` = `inv`.`bodega`))
WHEN (`inv`.`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 (`inv`.`tipobodega` = 'Vendors') THEN 'CUNDINAMARCA'
WHEN (`inv`.`tipobodega` = 'ActivosRetornables') THEN 'EQUIPO'
ELSE '.'
END) AS `Regional`,
#03 (CASE
WHEN (`inv`.`tipobodega` = 'Sedes') THEN 'SEDE'
WHEN (`inv`.`tipobodega` = 'PlanillasTrabajo') THEN 'Planilla'
WHEN (`inv`.`tipobodega` = 'PacientesII') THEN 'Paciente'
WHEN (`inv`.`tipobodega` = 'ActivosRetornables') THEN 'Activo Retornables'
WHEN (`inv`.`tipobodega` = 'Vendors') THEN 'Vendors'
ELSE '.'
END) AS `TipoUbicacion`,
#04 (CASE
WHEN (`inv`.`tipobodega` = 'Sedes') THEN
(SELECT `vtiger_sedes`.`clasesede`
FROM `vtiger_sedes`
WHERE (`vtiger_sedes`.`sedesid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PlanillasTrabajo') THEN
(SELECT `vtiger_rutas`.`tiporuta`
FROM `vtiger_rutas`
WHERE (`vtiger_rutas`.`rutasid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PacientesII') THEN 'Paciente'
WHEN (`inv`.`tipobodega` = 'ActivosRetornables') THEN
(SELECT `vtiger_vendor`.`category`
FROM `vtiger_vendor`
WHERE (`vtiger_vendor`.`vendorid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'Vendors') THEN
(SELECT `vtiger_activosretornables`.`modeloequipo`
FROM `vtiger_activosretornables`
WHERE (`vtiger_activosretornables`.`activosretornablesid` = `inv`.`bodega`))
ELSE 'Otro'
END) AS `N1`,
`crminfo`.`createdtime` AS `FechaCreacion`,
count(0) AS `Cantidad`
WHERE (`prod`.`discontinued` = 1)
GROUP BY `inv`.`bodega`,
`id_repuesto`
ORDER BY `crminfo`.`createdtime`,
`inv`.`cantidadnew` DESC
| 
