niota

This is an old revision of the document!

---

call ....

/*!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 */;