|
SELECT prod.productid AS id_producto,
rep.repuestosid AS id_repuesto,
repcf.cf_3498 AS 'SubUnidad',
repcf.cf_3514 AS imagen,
rep.proveedor,
rep.repuesto AS repuesto,
rep.codigobarras,
rep.repuestosusados,
rep.manufacturer,
rep.qtydisponible,
rep.qtytotal,
rep.tiporepuesto,
rep.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,
prod.productcategory,
mvdes.estadoinsumos,
mvdes.lotedes AS Lote,
mvdes.movequiposdesid,
mvdes.mvdes_cantidad,
mvdes.mvdes_date AS Fecha_Movimiento,
YEAR(mvdes.mvdes_date) AS Anio,
MONTH(mvdes.mvdes_date) AS Mes,
mvdes.mvdes_destino,
mvdes.mvdes_equipo,
mvdes.mvdes_fuente,
mvdes.mvdes_observaciones,
mvdes.mvdes_usuario,
mvdes.mvdesid,
mvdes.soporte,
mvdes.tipoinsumo,
mvdes.tiposoporte,
repcf.cf_3526 AS 'Repuesto_Generico',
crminfofuente.setype AS fuente,
#01 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,
#02 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,
#03 CASE
WHEN crminfodestino.setype = 'Sedes' THEN
(SELECT vtiger_sedes.clasesede
FROM vtiger_sedes
WHERE vtiger_sedes.sedesid = crminfodestino.crmid )
END AS clase_sede_destino,
#04 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`
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');
| 
