select fv.serie, fv.NFACTURA as Factura, convert(varchar,fv.fecha,103) as fecha,
       art.familia as Categoria,'' as Tipo,ISNULL(art.codigo,dav.articulo) as Articulo, '' as U_V,
	   art.anchoizq as Ancho,
	   isnull((select top(1) v2.valor from e001_vardav as v2 where v2.albaran=dav.albaran and v2.linea=dav.linea and variable='[ESPESOR]'),'') as Espesor,
	   '' as Vista, '' as Acabado,
	   isnull((select top(1) v2.valor from e001_vardav as v2 where v2.albaran=dav.albaran and v2.linea=dav.linea and variable='[COLOR EXT]'),'') as Color_EXT,
	   isnull((select top(1) v2.valor from e001_vardav as v2 where v2.albaran=dav.albaran and v2.linea=dav.linea and variable='[COLOR INT]'),'') as Color_INT,
	   replace(convert(varchar,dav.unidades),'.',',') as Unidades,
	   replace(convert(varchar,round(dav.Precio*(1.0-dav.dcto/100.0)*(1.0-dav.dcto2/100.0),2)),'.',',') as Precio,
	   replace(convert(varchar,dav.factor),'.',',') as M2,
	   replace(convert(varchar,dav.importe),'.',',') as TotalVenta,
	   replace(convert(varchar,dav.costeneto),'.',',') as Coste,
	   replace(convert(varchar,round(dav.unidades*dav.costeneto*dav.factor,2)),'.',',') as TotalCoste,
	   replace(convert(varchar,dav.importe - round(dav.unidades*dav.costeneto*dav.factor,2)),'.',',') as Margen,
	   replace(convert(varchar,case when dav.importe<>0 then round((dav.importe - round(dav.unidades*dav.costeneto*dav.factor,2))/dav.importe*100.0,0) else 0 end),'.',',') as TPC_Margen,
	   cli.nombre as Cliente, cli.cp as CP, cli.PROVINCIA as Prov, '' as CCAA, cli.pais as Pais,
	   com.nombre as Agente, seg.descripcio as Segmento,act.descripcio as Actividad,'' as ZonaComercial,
	   round(dav.unidades*dav.factor*art.peso,2) as Peso,
	   av.nalbaran as Albaran
from e001_fravta as fv
left join e001_clientes as cli on cli.codigo=fv.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=fv.comercial
left join e001_albvta as av on av.factura=fv.entrada
left join e001_detalbv as dav on dav.albaran=av.entrada AND dav.articulo <> 'ANTICIPO' AND dav.articulo <> '' AND (dav.unidades <> 0 OR dav.precio <> 0)
left join e001_articul as art on art.codigo=dav.articulo
where fv.fecha between '{?Fecha Desde}' and '{?Fecha Hasta}' AND fv.serie <> 'L'
order by fv.fecha,fv.nfactura
