SELECT ot.numero AS orden, convert(varchar,ot.fapertura,103) AS fecha,
       convert(varchar,ot.fcierre,103) AS FCierre,
       convert(varchar,ot.fecproduc,103) AS FProduc,
       convert(varchar,(select top(1) av2.fecha from e001_detalbv as dav2 left join e001_albvta as av2 on av2.entrada=dav2.albaran where dav2.orden=ot.entrada),103) as FEntrega,
       art.familia AS Categoria,'' AS Tipo,art.codigo AS Articulo, '' as U_V,
	   art.anchoizq as Ancho,
	   ISNULL((SELECT TOP(1) valor FROM E001_VarDOrd WHERE orden = do.orden AND linea = do.linea AND variable = '[ESPESOR]'),'') AS Espesor,
	   '' as Vista, '' as Acabado,
	   ISNULL((SELECT TOP(1) valor FROM E001_VarDOrd WHERE orden = do.orden AND linea = do.linea AND variable = '[COLOR EXT]'),'') AS Color_EXT,
	   ISNULL((SELECT TOP(1) valor FROM E001_VarDOrd WHERE orden = do.orden AND linea = do.linea AND variable = '[COLOR INT]'),'') AS Color_INT,
	   REPLACE(CONVERT(VARCHAR,do.unidades),'.',',') AS Unidades,
	   REPLACE(CONVERT(VARCHAR,do.factor),'.',',') AS M2,
	   REPLACE(CONVERT(VARCHAR,do.costeneto),'.',',') AS Coste,
	   REPLACE(CONVERT(VARCHAR,ROUND(do.unidades*do.costeneto*do.factor,2)),'.',',') AS TotalCoste,
	   cli.nombre AS Cliente, cli.cp AS CP, cli.PROVINCIA AS Prov, '' AS CCAA, cli.pais AS Pais,
	   ISNULL(com.nombre, '') AS Agente, ISNULL(seg.descripcio, '') AS Segmento, ISNULL(act.descripcio,'') AS Actividad,'' AS ZonaComercial,
	   REPLACE(CONVERT(VARCHAR,ROUND(do.unidades*do.factor*art.peso,2)),'.',',') AS Peso
FROM E001_Ordenes AS ot
LEFT JOIN E001_Clientes AS cli ON cli.codigo = ot.cliente
LEFT JOIN E_SegCom AS seg ON seg.codigo = cli.segcom
LEFT JOIN E001_Actividad AS act ON act.codigo=cli.actividad
LEFT JOIN E001_Comerci AS com ON com.codigo = cli.comercial
LEFT JOIN E001_DetOrden AS do ON do.orden = ot.entrada
LEFT JOIN E001_Articul AS art ON art.codigo = do.articulo
WHERE ot.fapertura BETWEEN '{?Fecha Desde}' AND '{?Fecha Hasta}'
ORDER BY ot.fapertura,ot.numero
