SELECT  
        purchase_vs_item.id,
        IFNULL(item.avatar,'') AS avatar ,  
        CONCAT(purchase.code, ' - ', item.name , ' - ', item.brand ,' - ',item_unit.name, ' - ', 
        ROUND((purchase_vs_item.qty- (purchase_vs_item.sold_qty + purchase_vs_item.returned_qty + purchase_vs_item.lost_qty))), ' - ' , 
        COALESCE(DATE_FORMAT(delivered_date,'%M %e, %Y'), DATE_FORMAT(purchase_date,'%M %e, %Y'))) AS displayText,
        
        JSON_OBJECT(
            'item_id', item.id,
            'type', item_unit.name, 
            'po', purchase.code,  
            'item_name', item.name, 
            'item_unit_cost', purchase_vs_item.unit_price + (IF(purchase_vs_item.qty != 0, (cogs_row_amount / purchase_vs_item.qty), 0.00)), 
            'selling_price', purchase_vs_item.selling_price , 
            'cogs_row_amount', purchase_vs_item.cogs_row_amount, 
            'sale_vs_itemId', 0, 
            'qty_available', ROUND((purchase_vs_item.qty- (purchase_vs_item.sold_qty + purchase_vs_item.returned_qty + purchase_vs_item.lost_qty)))) 
        AS otherField ,
        JSON_OBJECT('profit_star', ib.profit_star, 'speed_star', ib.speed_star, 'friction', ib.friction_star, 'perishable', ib.perishable_star) AS behaivor
        from purchase 
        INNER JOIN purchase_vs_item on purchase.id = purchase_vs_item.purchaseId
        LEFT JOIN item ON purchase_vs_item.item_id = item.id
        LEFT JOIN item_unit ON item_unit.id = purchase_vs_item.type_selling
        LEFT JOIN item_behaivor ib ON ib.item_id = item.id
        WHERE purchase.deleted = '0' and purchase_vs_item.deleted = '0' AND  purchase.shipping IN ('delivered','ordered','shipped')
        AND purchase.shipping = 'delivered'
        and (purchase_vs_item.qty- purchase_vs_item.sold_qty - purchase_vs_item.returned_qty - purchase_vs_item.lost_qty) >0  AND (item.name LIKE LOWER('%%') OR item.code LIKE LOWER('%%') OR purchase.code LIKE LOWER('%%') ) 
        ORDER BY id LIMIT 25 ;