call ....

mdInfo_Email

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
DELIMITER //
CREATE DEFINER=`niota`@`localhost` FUNCTION `mdInfo_Email`(
	`infotype_i` VARCHAR(16),
	`id_i` VARCHAR(16)
 
 
) RETURNS VARCHAR(512) CHARSET utf8
    DETERMINISTIC
BEGIN
 
-- drop function if exists mdInfo_Email;
--
-- *******************************************************************************
-- author.........: Reinhard Reisinger
-- copyright......: ritconsult IT und Projektconsulting Ges.m.b.H.
-- date written...: 2020.05.12
-- 
-- get the different emails for a user or usergroup
-- ===========+========+===== changelog ==========================================
-- YYYY.MM.DD | author | change
-- -----------+--------+----------------------------------------------------------
-- 2020.05.12   rre     add comment and header
--
-- *******************************************************************************
--	 
DECLARE ROW_NUMBER BIGINT DEFAULT 0;
DECLARE loop_max BIGINT DEFAULT 0;
DECLARE sep_mp VARCHAR(01) DEFAULT ',';
 
DECLARE this_udf VARCHAR(32) DEFAULT 'mdInfo_Email';
 
DECLARE l_end_of_cursor INTEGER DEFAULT 0;
 
DECLARE email_fallback VARCHAR(52) DEFAULT mdInfo_SysConfigValue('niota','EMAIL_FALLBACK');
DECLARE email_info VARCHAR(512);
DECLARE l_message_text VARCHAR(128);
 
DECLARE l_msg_id_final INTEGER DEFAULT 1001;
DECLARE l_msg_par_final VARCHAR(4000) DEFAULT ' ';
 
 
DECLARE l_infotype VARCHAR(16);
 
DECLARE l_username VARCHAR(16);
DECLARE l_usergroupname VARCHAR(32);
DECLARE l_id VARCHAR(32);
DECLARE l_email VARCHAR(56);
DECLARE l_email_list VARCHAR(512);
 
 
DECLARE usergroup_c1 cursor FOR
SELECT DISTINCT
  usr.username, 
  usr.email
  FROM v_et_user_group ugr
  JOIN v_et_user_group_member ugm
  ON ugm.usergroupname = ugr.usergroupname
  JOIN v_et_user usr
  ON usr.username = ugm.username 
  WHERE ugr.usergroupname = l_usergroupname
  AND ugr.ind_active = 1
  AND ugm.ind_active = 1
  AND usr.ind_active = 1
 
  UNION DISTINCT
 
  SELECT DISTINCT
  usr.username,  
  usr.email
  FROM v_et_user_group ugr
  JOIN v_et_user usr
  ON usr.username = ugr.usergroupname 
  WHERE ugr.usergroupname = l_usergroupname
  AND ugr.ind_active = 1
  AND usr.ind_active = 1 
;
 
 
DECLARE CONTINUE HANDLER 
        FOR NOT found SET l_end_of_cursor = 1;
 
 
function_block: BEGIN
 
SET l_infotype = infotype_i;
SET l_id = id_i;
 
IF UPPER(l_infotype) NOT IN ('USER', 'USERGROUP') THEN
   SET l_message_text = concat('invalid infotype', sep_mp, l_infotype);
   leave function_block;
END IF;
 
 
email_user: BEGIN
 
IF UPPER(l_infotype) NOT IN('USER', 'USERGROUP') THEN
	leave email_user;
END IF;
 
IF l_id IS NULL THEN
   SET l_msg_id_final = 0;
   SET email_info = email_fallback;
  	leave function_block; 
END IF;
 
SET l_usergroupname = l_id;
 
SELECT 
 usergroupname
 INTO
 l_usergroupname
FROM v_et_user_group
WHERE usergroupname = l_usergroupname;
 
IF ROW_COUNT() <> 1 AND l_usergroupname <> ' ' THEN
   SET l_msg_id_final = 0;
   SET email_info = email_fallback;
  	leave function_block; 
END IF;
 
 
SET ROW_NUMBER = 0;
SET loop_max = 50;
SET l_email_list = '';
 
OPEN usergroup_c1;
 
loopc1: while 1 = 1 do
 IF ROW_NUMBER = loop_max THEN
    leave loopc1;
 END IF;
 
 fetch usergroup_c1 INTO
       l_username,
       l_email
 ;
 
 IF l_end_of_cursor = 1 THEN  
    leave loopc1;
 END IF;
 
 IF l_email IS NULL THEN
    SET l_email = email_fallback;
 END IF;
 
 IF ROW_NUMBER > 0 THEN 
    SET l_email_list = concat(l_email_list, ', ');
 END IF;   
 
 SET ROW_NUMBER = ROW_NUMBER + 1;
 SET l_email_list = concat(l_email_list, TRIM(l_email));
 
END while loopc1;
close usergroup_c1;
 
IF l_email_list <> '' THEN
  SET l_email_list = TRIM(l_email_list);
END IF;
 
SET email_info = NULLIF(l_email_list, '');
 
IF email_info IS NULL THEN
   SET email_info = email_fallback;
END IF;
leave email_user;
 
END email_user;
SET l_msg_id_final = 0;
 
END function_block;
 
IF l_msg_id_final = 0 THEN
   RETURN(email_info);
ELSE
   SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = l_message_text; 
END IF;
 
END//
DELIMITER ;
 
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;