Releasing my querry to check unfinished deliveries

[Arcr0s]

Hi everybody,
I do not know if this belongs in this section but I thought this should be fitting.
I had the issue that there was, in my opinion, no quick and easy way to check what deliveries
have not been fully delivered yet or which might have had too many products send out to them.
Below is a querry I wrote that checks all orders and deliveries for differences.
You can run it manually or integrate it into a script to automate the process.

Select corder.`Order Ref`, delivery.`Delivered`, corder.`Ordered` From llx_element_element lee
Join (
	Select ex.rowid, sum(exd.qty) as 'Delivered' From llx_expedition ex
	Join llx_expeditiondet exd
	On ex.rowid = exd.fk_expedition
	Group By ex.rowid
) as delivery
On lee.fk_target = delivery.rowid 
Join (
	Select com.ref as 'Order Ref', com.rowid, sum(comd.qty) as 'Ordered' From llx_commande com
	Join llx_commandedet comd
	On com.rowid = comd.fk_commande 
	Group By com.rowid
) as corder
On lee.fk_source = corder.rowid
Where corder.`Ordered` != delivery.`Delivered`

I hope this helps some people

[ariffidaali]

This is not a correct query. I ran this query and picked a particular customer order. The query shows that that particular order has 7 delivered and ordered 1 when actually it has ordered 1 and delivered 1

[Arcr0s]

That is weird, it works perfectly for me on Dolibarr 11. I will check on Monday and see if I can improve it

[ramiroques]

Hello, possibly this is what you are looking for
Contact me