CREATE DEFINER=CURRENT_USER PROCEDURE `get_sales_by_user`(`user_id` INT, `journal_id` INT)
BEGIN
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'format(SUM(CASE WHEN payment_method = ''',
      payments.payment_method,
      ''' THEN payment ELSE 0 END),2) AS `',
      acc_payment_types.name, '`'
      
    )
  ) INTO @sql
FROM payments
join acc_payment_types on acc_payment_types.id = payments.payment_method;

SET @sql = CONCAT('SELECT reference as Reference, ', @sql, ', sum(discount) as Discount FROM payments join acc_currencies on acc_currencies.id = payments.currency 
 where  payments.status <> "Refund" and payments.created_by = "',user_id,'" and journal_id = "',journal_id,'" GROUP BY reference');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
