 DELIMITER $$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_grupy_hr` AS
	SELECT
		o.GUID,
		o.IDN,
		o.Nazwisko, 
		-- było ale nazw. ma za krótkie pole o.Nazwisko AS NazwaGrupy,
		IFNULL(NULLIF(c.Caption, ''), o.Nazwisko) AS NazwaGrupy,
		sp.DisplayValue AS group_type_str,
		_VarINT(
			(SELECT COUNT(*) FROM `grupy_osob` g WHERE g.grupaGUID = o.GUID AND g.osobaGUID != g.grupaGUID)
		) AS Group_Cardinality,
		o.IdnUser, 
		_VarStr('Grupa') AS Nick,
		o.KontrGUID,
		o.dzial,	
		o.ugrpguid,
		o.group_type,
		o.aLocalTime,
		o.niewidoczna_w_HR,
		o.Deleted,
		-- było o.GUID LIKE 'M.%' AS Is_Module,	
		o.group_type = 1 AS Is_Module,
		o.stanowisko AS OpisGrupy,
		o.tytul,
		o.nieaktywny

	FROM `osoby` o 
	LEFT JOIN `slowniki_pierwotne` sp ON sp.Slownik_GUID = 'TYPY_GRUP_OSOB' AND sp.Value = o.group_type
	LEFT JOIN `sys_core` c ON c.GUID = o.GUID
	WHERE 
		o.Rodzaj = 0 
		AND o.group_type != 1	-- tutaj nie pokazuj aplikacji
	;
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_sys_app_reg` AS
	SELECT
		c.ID,
		c.ID AS GUID,	-- dla zgodnosci z aplikacją www IntNet
		-- o.IDN,
		o.Nazwisko, 
		-- było ale nazw. ma za krótkie pole o.Nazwisko AS NazwaGrupy,
		IFNULL(NULLIF(c.Caption, ''), o.Nazwisko) AS NazwaGrupy,
		sp.DisplayValue AS group_type_str,
		_VarINT(
			(SELECT COUNT(*) FROM `grupy_osob` g WHERE g.grupaGUID = o.GUID AND g.osobaGUID != g.grupaGUID)
		) AS Group_Cardinality,
		(
		SELECT
			IF(COUNT(*) = 0,
				'Nie używana',
				CONCAT_WS(', ',
					CONCAT('Uruchomień: ', COUNT(*)),
					CONCAT('użytkow.: ', COUNT(DISTINCT sdl.Log_USER_GUID)),
					-- CONCAT('ost.aktyw.: ', MAX(sdl.Ping_AT)),
					CONCAT('ost.aktyw.: ', _Format_DT(MAX(sdl.Ping_AT), '%DZ%DW %dd %d %mmm %YH o %H:%N:%S'))
				)
			) AS Usage_last_1_month
		FROM `_sys_db_logons` sdl
		WHERE
			-- sdl.log_APP_ID = 'M.MOB.Mobile.Seller'
			sdl.log_APP_ID = o.GUID
			AND NOT (sdl.Status IN ('NEW', 'LOG.ERR'))
			AND sdl.CONNECT_AT > NOW() - INTERVAL 1 MONTH
		) AS Usage_last_1_month,
		
		o.IdnUser, 
		_VarStr('Grupa') AS Nick,
		o.KontrGUID,
		dzial,	
		o.ugrpguid,
		o.group_type,
		o.aLocalTime,
		o.niewidoczna_w_HR,
		o.Deleted,
		-- było o.GUID LIKE 'M.%' AS Is_Module,	
		o.group_type = 1 AS Is_Module,
		o.stanowisko AS OpisGrupy
	FROM `_sys_app_reg` c 
	-- INNER JOIN `sys_core` c ON c.GUID = o.GUID
	INNER JOIN `osoby` o ON o.GUID = c.ID AND o.Rodzaj = 0 AND o.group_type = 1
	LEFT JOIN `slowniki_pierwotne` sp ON sp.Slownik_GUID = 'TYPY_GRUP_OSOB' AND sp.Value = o.group_type
	WHERE
		TRUE
		AND c.not_used = 0
	ORDER BY ID
	;
	
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_grupy_app` AS
	SELECT
		o.GUID,
		o.IDN,
		o.Nazwisko, 
		-- było ale nazw. ma za krótkie pole o.Nazwisko AS NazwaGrupy,
		IFNULL(NULLIF(c.Caption, ''), o.Nazwisko) AS NazwaGrupy,
		sp.DisplayValue AS group_type_str,
		_VarINT(
			(SELECT COUNT(*) FROM `grupy_osob` g WHERE g.grupaGUID = o.GUID AND g.osobaGUID != g.grupaGUID)
		) AS Group_Cardinality,
		(
		SELECT
			IF(COUNT(*) = 0,
				'Nie używana',
				CONCAT_WS(', ',
					CONCAT('Uruchomień: ', COUNT(*)),
					CONCAT('użytkow.: ', COUNT(DISTINCT sdl.Log_USER_GUID)),
					-- CONCAT('ost.aktyw.: ', MAX(sdl.Ping_AT)),
					CONCAT('ost.aktyw.: ', _Format_DT(MAX(sdl.Ping_AT), '%DZ%DW %dd %d %mmm %YH o %H:%N:%S'))
				)
			) AS Usage_last_1_month
		-- ,sdl.log_APP_ID,
		-- sdl.*,
		-- MIN(sdl.Ping_AT) AS Od, 
		-- MAX(sdl.Ping_AT) AS DO, 
		-- COUNT(*) AS CC,
		-- COUNT(DISTINCT sdl.Log_USER_GUID) AS CU
		FROM `_sys_db_logons` sdl
		WHERE
			-- sdl.log_APP_ID = 'M.MOB.Mobile.Seller'
			sdl.log_APP_ID = o.GUID
			AND NOT (sdl.Status IN ('NEW', 'LOG.ERR'))
			AND sdl.CONNECT_AT > NOW() - INTERVAL 1 MONTH
		) AS Usage_last_1_month,
		
		o.IdnUser, 
		_VarStr('Grupa') AS Nick,
		o.KontrGUID,
		dzial,	
		o.ugrpguid,
		o.group_type,
		o.aLocalTime,
		o.niewidoczna_w_HR,
		o.Deleted,
		-- było o.GUID LIKE 'M.%' AS Is_Module,	
		o.group_type = 1 AS Is_Module,
		o.stanowisko AS OpisGrupy
	FROM `osoby` o 
	LEFT JOIN `slowniki_pierwotne` sp ON sp.Slownik_GUID = 'TYPY_GRUP_OSOB' AND sp.Value = o.group_type
	-- INNER JOIN `sys_core` c ON c.GUID = o.GUID
	INNER JOIN `_sys_app_reg` c ON c.ID = o.GUID
	WHERE
		o.Rodzaj = 0
		AND o.group_type = 1
		AND c.not_used = 0
	;
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_typy_grup_hr` AS
	SELECT 
		sp.Value AS group_type,
		sp.Param1 AS group_type_str
	FROM `slowniki_pierwotne` sp
	WHERE
		sp.Slownik_GUID = 'TYPY_GRUP_OSOB'
	ORDER BY sp.Value
	;
$$


DELIMITER ;
/* 
$Log: v_grupy_hr.sql,v $
Revision 1.11  2023/12/27 16:33:46  jmam
$SEND$ Lista aplikacji w CentrumInt.Net

Revision 1.10  2023/08/17 13:13:12  apro
$SEND$

Revision 1.9  2023/08/17 13:11:43  apro
pola do intnetcenter

Revision 1.8  2021/06/28 19:04:26  jmam
$SEND$

Revision 1.7  2021/06/24 18:16:47  jmam
$SEND$

Revision 1.6  2021/06/21 13:42:56  jmam
$SEND$

Revision 1.5  2020/05/14 07:00:30  jmam
$SEND$

Revision 1.4  2019/09/17 11:07:42  apro
*** empty log message ***

*/
