 /*!40101 SET NAMES utf8 */;
DELIMITER $$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_intnet_users` AS
	SELECT
		NAME_CONST('Checked', '0'),
		_VarInt(IFNULL(`A`.`user_type`, `O`.`user_type`)) AS `User_Type`,
		_VarInt(IF(IFNULL(dg.Value, 0), IF(`A`.Admin != 0, 1, IF(`A`.`user_type` = 1, 0, 2)), `O`.`user_type`)) AS `User_account_Type`,
		_VarInt(IFNULL(`A`.`Admin`, `O`.`administrator`)) AS `Administrator`,
		_VarDouble(
		(
			SELECT COUNT(0)
			FROM `grupy_osob` `g`
			WHERE `g`.`grupaGUID` != `g`.`osobaGUID` AND `g`.`osobaGUID` = `O`.`GUID`
		)) AS `CC`,
		`O`.`idn` AS `idn`,
		`O`.`GUID` AS `GUID`,
		O.ugrpGuid,
		`K`.`symbol` AS `symbol`,
		`O`.`pracownik` AS `pracownik`,
		_VarStr(IF(IFNULL(dg.Value, 0), IFNULL(`A`.`Login`, `O`.`idnuser`), `O`.`idnuser`)) AS `idnuser`,
		_VarInt(IF(IFNULL(dg.Value, 0), IFNULL(`A`.`Status`, `O`.`user_inactive`), `O`.`user_inactive` )) AS `user_inactive`,
		_VarInt(IF(IFNULL(dg.Value, 0), IFNULL(`A`.`Status`, `O`.`user_inactive`), `O`.`user_inactive` )) AS `status`,
		`O`.`niewidoczna_w_HR` AS `niewidoczna_w_HR`,
		`O`.`rodzaj` AS `rodzaj`,
		`O`.`Nazwisko` AS `nazwisko`,
		`O`.`Imiona` AS `imiona`,
		`O`.`stanowisko` AS `stanowisko`,
		IFNULL(NULLIF(`D`.`opis`, ''), `O`.`nazwa_dzialu`) AS `dzial`,
		`O`.`plec` AS `plec`,
		`O`.`AZAM_KodPocztowy` AS `AZAM_kodpocztowy`,
		`O`.`nazwa_dzialu` AS `nazwa_dzialu`,
		CONCAT(`O`.`Nazwisko`, ' ', `O`.`Imiona`) AS `NazwiskoImie`,
		SUBSTR(CONCAT(`O`.`AZAM_Ulica`, ' ', `O`.`AZAM_KodPocztowy`, ' ', `O`.`AZAM_Miejscowosc`), 1, 70) AS `Adres`,
		`O`.`AZAM_Adres` AS `AZAM_Adres`,
		`O`.`AZAM_Miejscowosc` AS `AZAM_miejscowosc`,
		`O`.`AZAM_Ulica` AS `AZAM_ulica`,
		`O`.`nieaktywny` AS `nieaktywny`,
		`O`.`korespondencja` AS `korespondencja`,
		`O`.`priorytet` AS `priorytet`,
		`O`.`kontrGUID` AS `kontrGUID`, 
		_VarInt(IFNULL(`A`.`IDN`, -1)) AS `IDN_account`,
		IFNULL(A.Ad_Mail, '') AS Ad_Mail
		FROM `osoby` `O` 
		LEFT JOIN `_db_globals` dg ON dg.Key = 'LOGON.user_accounts_Mode'
		LEFT JOIN `kontrah` `K` ON `O`.`kontrGUID` = `K`.`GUID`
		LEFT JOIN `dzialy` `D` ON `O`.`dzial` = `D`.`idn`
		LEFT JOIN `_user_accounts` `A` ON `A`.`OsobaGUID` = `O`.`GUID` AND `A`.`typ` = 'SYS'
		WHERE `O`.user_type IN (1, 2, -1, -2) AND `O`.`deleted` = 0 AND `O`.`rodzaj` = 1
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_user_accounts` AS
	SELECT
		NAME_CONST('Checked', '0'),
		ua.user_type,
		_VarInt(IF(ua.Admin != 0, 1, IF(ua.user_type = 1, 0, 2))) AS User_account_Type,
		ua.Admin,
		ua.Admin AS Administrator,
		/* było
		_VarDouble(
		(
			SELECT 
				COUNT(0)
			FROM `grupy_osob` go
			, `osoby` og
			WHERE 
				go.grupaGUID != go.osobaGUID AND go.osobaGUID = o.GUID
				AND og.GUID = go.grupaGUID AND og.group_type = 0
		)) AS CC,
		*/
		-- _VarInt(		ale ze wzgledu na stary EXE Int.Net.Center muszę wrócic do double :(
		_VarDouble(
		(
			SELECT 
				COUNT(*)
			FROM `grupy_osob` go
			LEFT JOIN `_sys_app_reg` a ON a.ID = go.grupaGUID
			WHERE 
				go.grupaGUID != go.osobaGUID 
				AND go.osobaGUID = o.GUID
				AND a.ID IS NULL
				-- AND og.GUID = go.grupaGUID 
				-- AND og.group_type = 0
				-- AND NOT go.GrupaGUID LIKE 'M.%'
		)) AS CC,
		_VarStr(
		(
			SELECT 
				CONCAT_WS('',
					CONCAT('do ', NULLIF(COUNT(*), 0), ' - '),
					IFNULL(NULLIF(GROUP_CONCAT(DISTINCT REPLACE(REPLACE(REPLACE(go.grupaGUID, 'M.WEB.', ''), 'M.EXE.', ''), 'M.MOB.', '') SEPARATOR ', '), ''), _VarStr('brak dostępu')) -- bez VarStr - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (cp1250_general_ci,IMPLICIT) for operation 'if'
				)
			FROM `grupy_osob` go
			WHERE 
				go.grupaGUID != go.osobaGUID AND go.osobaGUID = o.GUID
				AND go.GrupaGUID LIKE 'M.%'
		)) AS Dost_do_APP,
		o.idn AS idn,
		o.GUID AS GUID,
		o.ugrpGuid,
		k.symbol AS symbol,
		_VarInt(IFNULL(IFNULL(p.ToPracownik, o.ToPracownik), o.pracownik)) AS pracownik,
		IF(IFNULL(p.ToPracownik, o.ToPracownik), 
			IF(p.CzyObecniePracuje, 'Pracownik', 'Dawny pracownik'), -- bez VarStr - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (cp1250_general_ci,IMPLICIT) for operation 'if'
			IF(IFNULL(p.ToZleceniobiorca, o.ToZleceniobiorca), 
				IF(p.CzyObecniePracuje, ('Zleceniobiorca'), ('Dawny zleceniobiorca')),
				CONCAT('os. kontr: ', k.Nazwa)
			)
		) AS PracInfo,
		ua.Login AS idnuser,
		ua.Status AS user_inactive,
		ua.Status,
		ua.date_from,
		ua.date_to,
		Get_Status_For_From_To_Str('GET.Info.Konto.Full', NULL, ua.date_from, ua.date_to, ua.Status, NULL, '', '') AS Konto_Status_Str,
		o.niewidoczna_w_HR AS niewidoczna_w_HR,
		o.rodzaj AS rodzaj,
		o.Nazwisko AS nazwisko,
		o.Imiona AS imiona,
		o.stanowisko AS stanowisko,
		CONCAT_WS(', dz.: ', 
			IF(IFNULL(stanow.stanowisko, '') != '',
				NULLIF(CONCAT_WS(' ', stanow.stanowisko, CONCAT('(', IF(o.stanowisko <=> stanow.stanowisko, NULL, NULLIF(o.stanowisko, '')), ')')), ''),
				o.stanowisko
			),
			NULLIF(CONCAT_WS(', ', IF(o.stanowisko <=> o.nazwa_dzialu, NULL, NULLIF(o.nazwa_dzialu, '')), NULLIF(d.opis, '')), '')
		) AS StanowiskoInfo,
		d.opis AS dzial,
		o.plec AS plec,
		o.AZAM_KodPocztowy AS AZAM_kodpocztowy,
		o.nazwa_dzialu AS nazwa_dzialu,
		CONCAT_WS(' ', o.Nazwisko, o.Imiona) AS NazwiskoImie,
		CONCAT_WS(' ', o.AZAM_Ulica, o.AZAM_KodPocztowy, o.AZAM_Miejscowosc) AS Adres,
		o.AZAM_Adres AS AZAM_Adres,
		o.AZAM_Miejscowosc AS AZAM_miejscowosc,
		o.AZAM_Ulica AS AZAM_ulica,
		o.nieaktywny AS nieaktywny,
		o.korespondencja AS korespondencja,
		o.priorytet AS priorytet,
		o.kontrGUID AS kontrGUID, 
		ua.IDN AS IDN_account,
		IFNULL(ua.Ad_Mail, '') AS Ad_Mail,
		IFNULL(ua.KD_mail, '') AS KD_mail,
		ua.err_logon_count,
		ua.Login,
		ua.LastUpdate,
		ua.Last_Change_Pswd
		FROM `_user_accounts` ua
		INNER JOIN `osoby` o ON ua.OsobaGUID = o.GUID 
		LEFT JOIN `Pracownicy` p ON p.GUID = o.GUID
		LEFT JOIN `kontrah` k ON o.kontrGUID = k.GUID
		LEFT JOIN `stanowiska` stanow ON stanow.IDN = p.stanowisko
		LEFT JOIN `dzialy` d ON o.dzial = d.idn
		WHERE 
			ua.typ = 'SYS'
			AND ua.user_type IN (1, 2, -1, -2) 
			-- AND o.deleted = 0 
			-- AND o.rodzaj = 1
	;
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_user_accounts_short` AS	
	SELECT
		NAME_CONST('Checked', '0'),
		ua.user_type,
		_VarInt(IF(ua.Admin != 0, 1, IF(ua.user_type = 1, 0, 2))) AS User_account_Type,
		ua.Admin,
		ua.Admin AS Administrator,
		o.idn AS idn,
		o.GUID AS GUID,
		o.ugrpGuid,
		-- k.symbol AS symbol,
		_VarInt(IFNULL(IFNULL(p.ToPracownik, o.ToPracownik), o.pracownik)) AS pracownik,
		IF(IFNULL(p.ToPracownik, o.ToPracownik), 
			IF(p.CzyObecniePracuje, 'Pracownik', 'Dawny pracownik'), -- bez VarStr - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (cp1250_general_ci,IMPLICIT) for operation 'if'
			IF(IFNULL(p.ToZleceniobiorca, o.ToZleceniobiorca), 
				IF(p.CzyObecniePracuje, ('Zleceniobiorca'), ('Dawny zleceniobiorca')),
				''
			)
		) AS PracInfo,
		ua.Login AS idnuser,
		ua.Status AS user_inactive,
		ua.Status,
		ua.date_from,
		ua.date_to,
		-- Get_Status_For_From_To_Str('GET.Info.Konto.Full', NULL, ua.date_from, ua.date_to, ua.Status, NULL, '', '') AS Konto_Status_Str,
		o.niewidoczna_w_HR AS niewidoczna_w_HR,
		o.rodzaj AS rodzaj,
		o.Nazwisko AS nazwisko,
		o.Imiona AS imiona,
		o.stanowisko AS stanowisko,
		CONCAT_WS(', dz.: ', 
			IF(IFNULL(stanow.stanowisko, '') != '',
				NULLIF(CONCAT_WS(' ', stanow.stanowisko, CONCAT('(', IF(o.stanowisko <=> stanow.stanowisko, NULL, NULLIF(o.stanowisko, '')), ')')), ''),
				o.stanowisko
			),
			NULLIF(CONCAT_WS(', ', IF(o.stanowisko <=> o.nazwa_dzialu, NULL, NULLIF(o.nazwa_dzialu, '')), NULLIF(d.opis, '')), '')
		) AS StanowiskoInfo,
		d.opis AS dzial,
		o.plec AS plec,
		o.AZAM_KodPocztowy AS AZAM_kodpocztowy,
		o.nazwa_dzialu AS nazwa_dzialu,
		CONCAT_WS(' ', o.Nazwisko, o.Imiona) AS NazwiskoImie,
		o.nieaktywny AS nieaktywny,
		o.korespondencja AS korespondencja,
		o.priorytet AS priorytet,
		o.kontrGUID AS kontrGUID, 
		ua.IDN AS IDN_account,
		IFNULL(ua.Ad_Mail, '') AS Ad_Mail,
		IFNULL(ua.KD_mail, '') AS KD_mail,	
		ua.err_logon_count,
		ua.Login,
		ua.LastUpdate,
		ua.Last_Change_Pswd
		FROM `_user_accounts` ua
		INNER JOIN `osoby` o ON ua.OsobaGUID = o.GUID 
		LEFT JOIN `Pracownicy` p ON p.GUID = o.GUID
		-- LEFT JOIN `kontrah` k ON o.kontrGUID = k.GUID
		LEFT JOIN `stanowiska` stanow ON stanow.IDN = p.stanowisko
		LEFT JOIN `dzialy` d ON o.dzial = d.idn
		WHERE 
			ua.typ = 'SYS'
			AND ua.user_type IN (1, 2, -1, -2) 
			-- AND o.deleted = 0 
			-- AND o.rodzaj = 1
	;
$$


DELIMITER ;
