# Analisi de Proveedor
###### tags: `DataStudio`
```sql=
SELECT
rp.ref AS ref_partner,
rp.name AS partner,
po.name AS oc,
pt.default_code AS producto,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
pt.name,
'\t',
''
),
'\r',
''
),
'\n',
''
),
'char(13)',
''),
'char(10)','') AS descripcion,
pu.name AS uom,
COALESCE(pol.product_qty, 0) AS qty_pedido,
pol.qty_received AS qty_recivida,
(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date AS fech_pedido,
(po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date AS fech_entrega,
(pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date AS fech_recibo,
ps.delay AS delay_std,
CASE
WHEN
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric = 0 THEN 1
ELSE
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric
END AS delay_oc,
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) AS delay_real,
CASE
WHEN
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <=
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) THEN 0.0
ELSE
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) -
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)
END AS dias_retraso,
CASE
WHEN
CASE
WHEN
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <=
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) THEN 25
ELSE
25 - trunc((((((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) -
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date))::numeric *
(25 / CASE
WHEN
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric = 0.0 THEN 1
ELSE
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric
END
)),2)
END < 0.0
THEN 0
ELSE
CASE
WHEN
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <=
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) THEN 25
ELSE
25 - trunc((((((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) -
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date))::numeric *
(25 / CASE
WHEN
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric = 0.0 THEN 1
ELSE
((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric
END
)),2)
END
END AS pts,
--EVALUACION GLOBAL DEL PROVEEDOR
ps.delay AS delay_prov,
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) AS delay_real_prov,
CASE
WHEN
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <= ps.delay THEN 0.0
ELSE
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) - ps.delay
END AS dias_retraso_prov,
CASE
WHEN
CASE
WHEN
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <= ps.delay THEN 25
ELSE
25 - trunc((((((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) - (ps.delay))::numeric *(25 /
CASE
WHEN ps.delay = 0 THEN 1
ELSE
ps.delay
END)),2)
END < 0.0
THEN 0
ELSE
CASE
WHEN
(((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <= ps.delay THEN 25
ELSE
25 - trunc((((((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) - (ps.delay))::numeric *(25 /
CASE
WHEN ps.delay = 0 THEN 1
ELSE
ps.delay
END)),2)
END
END AS pts_prov,
CASE
WHEN
pol.qty_received >= pol.product_qty THEN 25
ELSE
25 - ((pol.product_qty - pol.qty_received) * (25/pol.product_qty))
END AS cantidad_pts,
rp2.name AS comprador
FROM
purchase_order po
INNER JOIN purchase_order_line pol ON po.id = pol.order_id AND pol.qty_received > 0 AND pol.product_uom <> 95
INNER JOIN product_product pp ON pol.product_id = pp.id
INNER JOIN product_template pt ON pp.product_tmpl_id = pt.id
INNER JOIN product_supplierinfo ps ON pp.product_tmpl_id = ps.product_tmpl_id
INNER JOIN product_uom pu ON pt.uom_id = pu.id
-- INNER JOIN (SELECT
-- pickings.id as ids,
-- pickings.origin as origen,
-- max(pickings.date_done) AS fecha_listo
-- FROM
-- stock_picking pickings
-- WHERE pickings.state = 'done'
-- GROUP BY pickings.origin,
-- pickings.id,
-- pickings.date_done) AS sp ON sp.origen = po.name
INNER JOIN res_partner rp ON po.partner_id = rp.id
INNER JOIN res_users ru ON rp.x_user_id = ru.id
INNER JOIN res_partner rp2 ON ru.partner_id = rp2.id
WHERE
po.state = 'done'
--and po.name = 'PO05100'
ORDER BY
pol.product_qty DESC
```
Sign in with Wallet
Connect another wallet