Untitled

select "purchase_orders".*, TO_CHAR(purchase_orders.date, 'DD-MM-YYYY') as date_formatted, CONCAT(suppliers.code, ' - ', suppliers.name) as supplier, "currencies"."name" as "currency_name", CONCAT(currencies.code, ' - ', currencies.name) as currency, "warehouse_parent"."name" as "warehouse_parent_name", CONCAT(warehouse_parent.code, ' - ', warehouse_parent.name) as warehouse_parent, "warehouses"."name" as "warehouse_name", CONCAT(warehouses.code, ' - ', warehouses.name) as warehouse, "created_user"."name" as "created_by", "updated_user"."name" as "updated_by", "deleted_user"."name" as "deleted_by", "approved_user"."name" as "approved_by", "canceled_user"."name" as "canceled_by", CASE
                    WHEN purchase_orders.is_autofinished = TRUE THEN 'Auto Finished'                   
                    WHEN EXISTS (
                        SELECT 1
                        FROM purchase_order_details pod
                        WHERE pod.purchase_order_id = purchase_orders.id
                        AND pod.deleted_at IS NULL
                        AND pod.fulfil > 0
                        AND pod.fulfil < pod.total_pack
                    ) THEN 'Partialy Received'
                    WHEN NOT EXISTS (
                        SELECT 1
                        FROM purchase_order_details pod
                        WHERE pod.purchase_order_id = purchase_orders.id
                        AND pod.deleted_at IS NULL
                        AND pod.fulfil < pod.total_pack
                    ) THEN 'Auto Finished'
                    WHEN purchase_orders.is_close = TRUE THEN 'Closed By User'
                    WHEN purchase_orders.is_approve = TRUE THEN 'Confirmed'
                    WHEN purchase_orders.is_active = TRUE THEN 'Active'
                    ELSE 'Draft'
                    END AS approval_status, CASE                        
                        WHEN NOT EXISTS (
                            SELECT 1
                            FROM good_receives gr
                            WHERE gr.purchase_order_id = purchase_orders.id
                            AND gr.deleted_at IS NULL
                        ) THEN 'No Good Receive'
                        WHEN EXISTS (
                            SELECT 1
                            FROM good_receives gr
                            WHERE gr.purchase_order_id = purchase_orders.id
                            AND gr.deleted_at IS NULL
                        ) AND EXISTS (
                            SELECT 1
                            FROM purchase_order_details pod
                            WHERE pod.purchase_order_id = purchase_orders.id
                            AND pod.deleted_at IS NULL
                            AND pod.fulfil < pod.total_pack
                        ) THEN 'Partial Good Receive'
                        WHEN NOT EXISTS (
                            SELECT 1
                            FROM purchase_order_details pod
                            WHERE pod.purchase_order_id = purchase_orders.id
                            AND pod.deleted_at IS NULL
                            AND pod.fulfil < pod.total_pack
                        ) THEN 'Full Good Receive'                        
                    END AS good_receive_status, (SELECT REPLACE(TO_CHAR(SUM(total_pack), 'FM999G999G999'), ',', '.') 
                    FROM purchase_order_details
                    WHERE purchase_order_details.purchase_order_id = purchase_orders.id
                    AND purchase_order_details.deleted_at IS NULL)
						   as total_pack from "purchase_orders" left join "suppliers" on "suppliers"."id" = "purchase_orders"."supplier_id" 
							left join "supplier_contacts" on "supplier_contacts"."id" = "purchase_orders"."supplier_contact_id" 
							left join "currencies" on "currencies"."id" = "purchase_orders"."currency_id" 
							left join "warehouses" as "warehouse_parent" on "warehouse_parent"."id" = "purchase_orders"."warehouse_parent_id"
							 left join "warehouses" on "warehouses"."id" = "purchase_orders"."warehouse_id" 
							 left join "users" as "created_user" on "created_user"."id" = "purchase_orders"."created_by"
							  left join "users" as "updated_user" on "updated_user"."id" = "purchase_orders"."updated_by" 
							  left join "users" as "deleted_user" on "deleted_user"."id" = "purchase_orders"."deleted_by" 
							  left join "users" as "approved_user" on "approved_user"."id" = "purchase_orders"."approved_by" 
							  left join "users" as "canceled_user" on "canceled_user"."id" = "purchase_orders"."canceled_by" 
							  where (("purchase_orders"."id" = '104') and "purchase_orders"."is_active" = true or 
							  ("purchase_orders"."created_by" = 24 and "purchase_orders"."is_active" = false) and 
							  (("purchase_orders"."warehouse_parent_id" in (5, 6, 7, 8, 9, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 49, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117))))
							   and "purchase_orders"."deleted_at" is null order by "purchase_orders"."created_at" desc, "purchase_orders"."id" desc