# REPORTE MARGARITA HOYYY

###### tags: `Desarrollo`
:::success
ACTUAL
:::

select
	o.FechElabo AS FechElabo,
    o.Proveedor AS Proveedor,
    o.NumPedido AS cNumPedido,       
    SUM(o.CantiDada) AS CantidadDada,
    SUM(o.CantiOrden) AS CantiOrden ,
    o.PartidaOC AS partida,
    ff.NumFactura AS Factura,
    o.OCCERRADA,
    o.Status,
    IF(ff.NumFactura IS NULL,0,1) as fac,
    IF(o.OCCERRADA = 1 AND ff.NumFactura IS NULL AND SUM(o.CantiDada) >= o.CantiOrden ,'COMPLETADA',
    IF(ff.NumFactura IS NULL AND o.OCCERRADA = 0,'PENDIENTE',
    IF(o.OCCERRADA = 0 AND ff.NumFactura IS NULL AND SUM(o.CantiDada) >= o.CantiOrden ,'COMPLETADA',
    IF(o.OCCERRADA = 1 AND ff.NumFactura IS NULL ,'CANCELADA',
    IF ((o.CantiDada < o.CantiOrden) AND ff.NumFactura IS NOT NULL AND o.OCCERRADA = 0,'PENDIENTE',
    IF(ff.NumFactura IS NOT NULL AND o.OCCERRADA = 0 AND SUM(o.CantiDada) > o.CantiOrden ,'COMPLETADA',
    IF(o.CantiDada = o.CantiOrden AND o.OCCERRADA = 0 AND ff.NumFactura IS NOT NULL, 'COMPLETADA', 
    IF(o.OCCERRADA = 1 AND ff.NumFactura IS NOT NULL ,'COMPLETADA','REVISAR')))))))) AS Estado,   
    CONCAT(ROUND( IF(SUM(o.CantiDada) = 0 , 0, ((SUM(o.CantiDada)*100)/SUM(o.CantiOrden) ) ),2),'%')as porcentaje    
from
    FOC o
join FProveedor p on
    o.Proveedor = p.CveProvee
join FArticulosProvee a on
    o.Producto = a.CodArtMP
    and o.Proveedor = a.Proveedor
left join InvMatPrima m on
    o.Producto = m.MPNumArticulo
left join InvProdTerm t on
    o.Producto = t.PTNumArticulo
LEFT JOIN 
	FFacturacionProveedor ff on
	ff.Pedido = o.NumPedido 	
where
o.FechElabo >'2019-06-30 00:00:00'
GROUP by o.NumPedido ;

:::danger
Actual tiene fallas al validar pendientes
:::

select
	o.FechElabo AS FechElabo,
    o.Proveedor AS Proveedor,
    o.NumPedido AS cNumPedido,       
    SUM(o.CantiDada) AS CantidadDada,
    SUM(o.CantiOrden) AS CantiOrden ,
    o.PartidaOC AS partida,
    ff.NumFactura AS Factura,
    /*o.OCCERRADA,
    p.NomProvee AS Nomprovee, 
    o.Status,*/
    IF(ff.NumFactura IS NULL,0,1) as fac,
    IF(o.OCCERRADA = 1 AND ff.NumFactura IS NULL AND SUM(o.CantiDada) >0,'COMPLETADA',
    IF(o.OCCERRADA = 0 AND ff.NumFactura IS NULL AND SUM(o.CantiDada) >0,'COMPLETADA',
    IF(o.OCCERRADA = 1 AND ff.NumFactura IS NULL ,'CANCELADA', 
    IF(ff.NumFactura IS NOT NULL AND o.OCCERRADA = 0 AND SUM(o.CantiDada) > 0,'COMPLETADA',
    IF(ff.NumFactura IS NULL AND o.OCCERRADA = 0,'PENDIENTE',
    IF(o.OCCERRADA = 1 AND ff.NumFactura IS NOT NULL ,'COMPLETADA','REVISAR')))))) AS Estado,
    CONCAT(ROUND( IF(SUM(o.CantiDada) = 0 , 0, ((SUM(o.CantiDada)*100)/SUM(o.CantiOrden) ) ),2),'%')as porcentaje
from
    FOC o
join FProveedor p on
    o.Proveedor = p.CveProvee
join FArticulosProvee a on
    o.Producto = a.CodArtMP
    and o.Proveedor = a.Proveedor
left join InvMatPrima m on
    o.Producto = m.MPNumArticulo
left join InvProdTerm t on
    o.Producto = t.PTNumArticulo
LEFT JOIN 
	FFacturacionProveedor ff on
	ff.Pedido = o.NumPedido 	
where
o.FechElabo >'2019-06-30 00:00:00'
GROUP by o.NumPedido ;