|
select
`prod`.`productid` AS `id_producto`,
`rep`.`repuestosid` AS `id_repuesto`,
#01 (
select
`vtiger_repuestosgrouprel`.`groupname`
from
`vtiger_repuestosgrouprel`
where
(
`vtiger_repuestosgrouprel`.`repuestosid` = `rep`.`repuestosid`
)
)
AS `regional`,
`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`,
`mvdes`.`estadoinsumos` AS `estadoinsumos`,
`mvdes`.`lotedes` AS `Lote`,
`mvdes`.`movequiposdesid` AS `movequiposdesid`,
`mvdes`.`mvdes_cantidad` AS `mvdes_cantidad`,
`mvdes`.`mvdes_date` AS `Fecha_Movimiento`,
year(`mvdes`.`mvdes_date`) AS `Anio`,
month(`mvdes`.`mvdes_date`) AS `Mes`,
`mvdes`.`mvdes_destino` AS `mvdes_destino`,
`mvdes`.`mvdes_equipo` AS `mvdes_equipo`,
`mvdes`.`mvdes_fuente` AS `mvdes_fuente`,
`mvdes`.`mvdes_observaciones` AS `mvdes_observaciones`,
`mvdes`.`mvdes_usuario` AS `mvdes_usuario`,
`mvdes`.`mvdesid` AS `mvdesid`,
`mvdes`.`soporte` AS `soporte`,
`mvdes`.`tipoinsumo` AS `tipoinsumo`,
`mvdes`.`tiposoporte` AS `tiposoporte`,
`repcf`.`cf_3526` AS `Repuesto_Generico`,
`crminfofuente`.`setype` AS `fuente`,
#02 (
case
when
(
`crminfofuente`.`setype` = 'Sedes'
)
then
(
select
`vtiger_sedes`.`clasesede`
from
`vtiger_sedes`
where
(
`vtiger_sedes`.`sedesid` = `crminfofuente`.`crmid`
)
)
end
)
AS `clase_de_sede_fuente`,
#03 (
case
when
(
`crminfofuente`.`setype` = 'Sedes'
)
then
(
select
`vtiger_sedes`.`sede`
from
`vtiger_sedes`
where
(
`vtiger_sedes`.`sedesid` = `crminfofuente`.`crmid`
)
)
end
)
AS `sede_fuente`,
`crminfodestino`.`setype` AS `destino`,
#04 (
case
when
(
`crminfodestino`.`setype` = 'Sedes'
)
then
(
select
`vtiger_sedes`.`clasesede`
from
`vtiger_sedes`
where
(
`vtiger_sedes`.`sedesid` = `crminfodestino`.`crmid`
)
)
end
)
AS `clase_sede_destino`,
#05 (
case
when
(
`crminfodestino`.`setype` = 'Sedes'
)
then
(
select
`vtiger_sedes`.`sede`
from
`vtiger_sedes`
where
(
`vtiger_sedes`.`sedesid` = `crminfodestino`.`crmid`
)
)
end
)
AS `sede_destino`,
date_format(`mvdes`.`mvdes_date`, '%Y%m') AS `agno_mes`,
date_format(`mvdes`.`mvdes_date`, '%Y%m%d') AS `agno_mes_dia`,
yearweek(`mvdes`.`mvdes_date`, 0) AS `agno_semana`,
COUNT(0) AS 'cantidad'
left join vtiger_crmentity crminfofuente ON mvdes.mvdes_fuente = crminfofuente.crmid
left join vtiger_crmentity crminfodestino on mvdes.mvdes_destino = crminfodestino.crmid
where
`mvdes`.`tipoinsumo` like 'Repuestos'
and
`mvdes`.`mvdes_date` between date_format((now()), '%Y-%m-01 00:00:00') and date_format(last_day((now())), '%Y-%m-%d 23:59:59')
group BY rep.repuestosid,
`rep`.`tiporepuesto`
| 
