CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_stock_adjustments`(IN `start_date` DATE, IN `end_date` DATE, IN `store` INT, IN `user_filter` INT)
BEGIN
-- Stock adjustments with the user who made them: increase / decrease,
-- quantity, cost value and reason.
SELECT DATE_FORMAT(a.created_at, '%d %b, %Y %H:%i') AS `Date`, COALESCE(pr.name, CONCAT('(deleted product #', a.product_id, ')')) AS `Product`, COALESCE(s.name, '') AS `Store`,
       a.type AS `Type`, CONCAT(FORMAT(a.adjustment_rate, 2), ' ', COALESCE(m.name, '')) AS `Quantity`,
       CONCAT('$ ', FORMAT(IFNULL(a.cost, 0) * IFNULL(a.adjustment_rate, 0), 2)) AS `Value`,
       COALESCE(a.reason, '') AS `Reason`, COALESCE(u.name, '') AS `By`
FROM inventory_adjustments a
LEFT JOIN scm_products pr ON pr.id = a.product_id
LEFT JOIN phar_stores s ON s.id = a.store
LEFT JOIN scm_product_measurements m ON m.id = a.uom
LEFT JOIN users u ON u.id = a.created_by
WHERE DATE(a.created_at) BETWEEN start_date AND end_date AND (store = 0 OR a.store = store) AND (user_filter = 0 OR a.created_by = user_filter)
ORDER BY a.created_at;
END
