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 'Partially 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"."is_approve" = 'true' AND "purchase_orders"."is_close" = 'false') 
    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"."code" ASC, 
    "purchase_orders"."created_at" DESC, 
    "purchase_orders"."id" DESC 
LIMIT 10 OFFSET 0;