delete PanelVentas
insert into PanelVentas (Entrada,Serie,Factura,Fecha,Categoria,Tipo,Articulo,U_V,Ancho,Espesor,Vista,Acabado,Color_EXT,Color_INT,Unidades,Precio,M2,TotalVenta,Coste,TotalCoste,Margen,TPC_Margen,Cliente,CP,Provincia,CCAA,Pais,Agente,Segmento,Actividad,Zona,Peso,Albaran)
select fv.Entrada,fv.Serie,fv.nfactura as Factura,convert(varchar,fv.fecha,112) as Fecha,isnull(art.familia,'') as Categoria,'' as Tipo,isnull(art.codigo,dav.articulo) as Articulo, '' as U_V,isnull(art.anchoizq,0) as Ancho,
(case when try_convert(money,isnull(v2.valor,0),1) is null then 0 else try_convert(money,isnull(v2.valor,0),1) end) as Espesor,'' as Vista, '' as Acabado,isnull(v3.valor,'') as Color_EXT,
isnull(v4.valor,'') as Color_INT,convert(money,dav.unidades,1) as Unidades,convert(money,round(dav.Precio*(1.0-dav.dcto/100.0)*(1.0-dav.dcto2/100.0),2),1) as Precio,convert(money,dav.factor,1) as M2,
convert(money,dav.importe,1) as TotalVenta,convert(money,dav.costeneto,1) as Coste,convert(money,round(dav.unidades*dav.costeneto*dav.factor,2),1) as TotalCoste,
convert(money,dav.importe-round(dav.unidades*dav.costeneto*dav.factor,2),1) as Margen,convert(money,(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),1) as TPC_Margen,
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 Zona,
convert(money,round(dav.unidades*dav.factor*isnull(art.peso,0),2),1) 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.unidades <> 0 OR dav.precio <> 0)
left join e001_vardav as v2 on v2.albaran=dav.albaran and v2.linea=dav.linea and v2.variable='[ESPESOR]'
left join e001_vardav as v3 on v3.albaran=dav.albaran and v3.linea=dav.linea and v3.variable IN ('[COLOR EXT]', '[COLOREXT]', '[COLOR_EXT]', '[COLOR]')
left join e001_vardav as v4 on v4.albaran=dav.albaran and v4.linea=dav.linea and v4.variable IN ('[COLOR INT]','[COLORINT]','[COLOR_INT]')
left join e001_articul as art on art.codigo=dav.articulo
where fv.fecha between '20180101' and '20221231' and (art.codigo is not null or (fv.serie='A' and dav.precio is not null))
