DELIMITER $$

DROP FUNCTION IF EXISTS `PIM_CalcPhoneNumber`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `PIM_CalcPhoneNumber`(
	_Value VARCHAR(30),
	_Mode INTEGER
	) RETURNS VARCHAR(30)
    NO SQL
    DETERMINISTIC
BEGIN
	DECLARE DUPLICAT BOOLEAN DEFAULT FALSE;
	DECLARE DUPLICAT_Country BOOLEAN DEFAULT FALSE;
	DECLARE SO VARCHAR(30) DEFAULT _Value;
	DECLARE S VARCHAR(30) DEFAULT _Value;
	
    SET @aDefaultCountryPrefix := IFNULL(@aDefaultCountryPrefix, '');
	IF @aDefaultCountryPrefix = '' THEN
		SET @aDefaultCountryPrefix := '48';
	END IF;	
	
	SET @_Value := IFNULL(@_Value, '');
	SET DUPLICAT = (@_Value = _Value);
	IF NOT DUPLICAT THEN
		SET @_Value := _Value;
		SET @aClearNumber := ''; 
		SET @aLocalNumber := ''; 
		SET @aFmtNumber := ''; 
		SET @aExtC := '';
		SET @aCC := ''; 
		SET @aRC := '';
		SET @aWaitIdx := 0; 
		SET @aPlusIndex := 0; 
		SET @a00Index := 0;
		SET @aStationary := TRUE;

--    SET @aClearNumber := FlushBadChar(aInputNumber, ' ');
		SET @aClearNumber = _Value;
		SET @aClearNumber = LTRIM(@aClearNumber);
		SET @aClearNumber = RTRIM(@aClearNumber);
		SET @aClearNumber = REPLACE(@aClearNumber, '/', 'w');
		SET @aClearNumber = REPLACE(@aClearNumber, 'w.', 'w');
		SET @aWaitIdx := Locate('#', @aClearNumber);
		IF @aWaitIdx < 1 THEN
			SET @aWaitIdx := Locate('w', @aClearNumber);
		END IF;
		IF @aWaitIdx < 1 THEN
			SET @aWaitIdx := Locate('W', @aClearNumber);
		END IF;
		IF @aWaitIdx >= 1 THEN
			SET @aExtC := SUBString(@aClearNumber, @aWaitIdx + 1, Length(@aClearNumber));
			IF Length(@aExtC) > 3 THEN
				SET @aExtC := ''; ELSE
				SET @aClearNumber := SUBString(@aClearNumber, 1, @aWaitIdx - 1);
			END IF;	
		ELSE
			SET @aExtC := '';
		END IF;
		
		SET @aClearNumber := REPLACE(@aClearNumber, '(', '');
		SET @aClearNumber := REPLACE(@aClearNumber, ')', '');
		
		SET @S := SUBString(@aClearNumber, 1, 2);
		IF (@S = '0-') OR (@S = '0 ')  THEN
			SET @aClearNumber := SUBString(@aClearNumber, 3, LENGTH(@aClearNumber));
		END IF;
SET @X1 := @aClearNumber;
	    SET @aClearNumber := Trim(@aClearNumber);
		SET @aPlusIndex := Locate('+', @aClearNumber);
		SET @a00Index := Locate('00', @aClearNumber);
		SET @S := SUBString(@aClearNumber, 1, 1);
		IF (@a00Index <> 1) AND (@S = '0') THEN
			SET @aClearNumber := SUBString(@aClearNumber, 2, LENGTH(@aClearNumber));
		END IF;
SET @X2 := @aClearNumber;
		
		SET @S := '';
		SET @S1 := '';
		SET @I := 1;
		WHILE @I <= LENGTH(@aClearNumber) DO
			SET @S1 := SUBSTRING(@aClearNumber, @I, 1);
			IF @S1 in ('0','1','2','3','4','5','6','7','8','9') THEN
				SET @S = CONCAT(@S, @S1);
			END IF;	
			SET @I := @I + 1;
		END WHILE;
		SET @aClearNumber := @S;
		
SET @X3 := @aClearNumber;
		
		IF @aPlusIndex = 1 THEN
			SET @aCC := SUBSTRING(@aClearNumber, 1, 2);
			SET @aClearNumber := SUBSTRING(@aClearNumber, 3, Length(@aClearNumber));
		ELSE
			IF @a00Index = 1 THEN
				SET @aCC := SUBSTRING(@aClearNumber, 3, 2);
				SET @aClearNumber := SUBSTRING(@aClearNumber, 5, Length(@aClearNumber));
			ELSE
				IF Length(@aClearNumber) >= 9 + 2 THEN
					SET @aCC := SUBSTRING(@aClearNumber, 1, 2);
					SET @aClearNumber := SUBSTRING(@aClearNumber, 3, Length(@aClearNumber));
				ELSE
					SET @aCC := @aDefaultCountryPrefix;
				END IF;	
			END IF;	
		END IF;	
		
SET @X4 := @aClearNumber;
		
		IF Length(@aClearNumber) <= 7 THEN
			IF Length(@aClearNumber) <= 4 THEN
				SET @aRC := '';
			ELSE
				SET @aRC := '58';
			END IF;	
			SET @aLocalNumber := @aClearNumber;
			SET @aStationary := True;
SET @X5 := @aRC;
		ELSE
			SET @aRC := SUBSTRING(@aClearNumber, 1, 2);
			IF (@aCC = '48') AND ((@aRC < '70')or(@aRC IN ('71','76','77','81','82','74'))) and not(@aRC IN ('50','51','60','66','69')) THEN
			--	stacjonarny
				SET @aLocalNumber := SUBSTRING(@aClearNumber, 3, Length(@aClearNumber));
				SET @aStationary := True;
			ELSE
			--	nie stacjonarny
				SET @aRC := SUBSTRING(@aClearNumber, 1, 3);
				SET @aLocalNumber := SUBSTRING(@aClearNumber, 4, Length(@aClearNumber));
				SET @aStationary := False;
			END IF;
		END IF;
		IF _Mode = 101 THEN	--	Zawsze jako stacjonarny +YY ZZ XXX XX XX
			SET @aStationary := TRUE;	SET @ST := TRUE; 
		END IF;	
		IF _Mode = 102 THEN	--	Zawsze jako NIEstacjonarny +YY ZZX XXX XXX
			SET @aStationary := FALSE;	SET @ST := FALSE;
		END IF;	
		IF @aStationary THEN
			IF Length(@aLocalNumber) < 5 THEN
				SET @aFmtNumber := @aLocalNumber; 
			ELSE	
				SET @aFmtNumber := CONCAT(SUBSTRING(@aLocalNumber, 1, 3), ' ', SUBSTRING(@aLocalNumber, 4, 2), ' ', SUBSTRING(@aLocalNumber, 6, 10)); 
			END IF;
		ELSE
			SET @aFmtNumber := CONCAT(SUBSTRING(@aLocalNumber, 1, 3), ' ', SUBSTRING(@aLocalNumber, 4, 10));
		END IF;
	END IF;	--	Algorytmu

	

	
	SET @aCC := IFNULL(@aCC, '');
--	FULL Numer BEZ Formatu	
	SET @aFullNumber := CONCAT('+', @aCC, @aRC, @aLocalNumber, IF(@aExtC != '', CONCAT('w', @aExtC), ''));
	
	IF _Mode = 0 THEN
	--	Full BEZ Formatu
		IF IFNULL(@_Value, '') = '' THEN
			SET @S := '';
		ELSE	
			SET @S := @aFullNumber;
		END IF;	
	END IF;	--	_Mode = 2
	
	IF _Mode in (1, 2, 101, 102) THEN
	--	Full Format
		SET @S := CONCAT(IF(_Mode = 1, '+', '00'), @aCC, IF(@aRC != '', CONCAT(' ' , @aRC), ''), ' ', @aFmtNumber, IF(@aExtC != '', CONCAT(' w ', @aExtC), ''));
	END IF;	--	_Mode = 0
	
	IF _Mode = 10 THEN
	--	NUMER w Kanonicznym formacie
		SET @S := CONCAT('+', @aCC, IF(@aRC != '', CONCAT('(' , @aRC, ')'), ''), @aLocalNumber, IF(@aExtC != '', CONCAT('w', @aExtC), ''));
	END IF;	--	_Mode = 10
	IF _Mode = 11 THEN
	--	NUMER Formatowany w Kanonicznym formacie
		SET @S := CONCAT('+', @aCC, IF(@aRC != '', CONCAT('(' , @aRC, ')'), ''), ' ', @aFmtNumber, IF(@aExtC != '', CONCAT(' w ', @aExtC), ''));
	END IF;	--	_Mode = 11
	
	IF _Mode = 50 THEN
	--	Tylko NUMER
		SET @S := @aClearNumber;
	END IF;	--	_Mode = 50
	IF _Mode = 51 THEN
	--	Tylko Lokalny NUMER
		SET @S := @aLocalNumber;
	END IF;	--	_Mode = 51
	IF _Mode = 52 THEN
	--	Tylko Prefix krajowy
		SET @S := @aCC;
	END IF;	--	_Mode = 51
	IF _Mode = 53 THEN
	--	Tylko Wewn
		SET @S := @aExtC;
	END IF;	--	_Mode = 51
	IF _Mode = 54 THEN
	--	To co 0 ale BEZ  Wewn
		IF _Value != '' THEN
			SET @S := CONCAT('+', @aCC, @aRC, @aLocalNumber);
		ELSE
			SET @S := '';
		END IF;	

	END IF;	--	_Mode = 51
	IF _Mode = 55 THEN
	--	To co 1 ale BEZ  Wewn
		SET @S := CONCAT(IF(_Mode = 1, '+', '00'), @aCC, IF(@aRC != '', CONCAT(' ' , @aRC), ''), ' ', @aFmtNumber);

	END IF;	--	_Mode = 51
	
	
	IF _Mode IN (30,31,32) THEN
	--	Kraj 
		SET @aCC_Save := IFNULL(@aCC_Save, '');
		SET DUPLICAT_Country := (@aCC_Save = @aCC);	
		SET @aCC_Save := @aCC;
		SET @DUPLICAT_Country := DUPLICAT_Country;
		IF NOT DUPLICAT_Country THEN
			SET @aIDCountry := '';
			SET @aNameCountry := '';
			SELECT 
				gc.IDCountry, g.Name 
			INTO 
				@aIDCountry, @aNameCountry 
			FROM geo_country_codes gc, geo_country g 
			WHERE 
				gc.IDCountry = g.Code AND 
				gc.CodeTel = @aCC AND
				gc.CodeTel != 0
			LIMIT 1
			;
			SET @aIDCountry := IFNULL(@aIDCountry, '');
			SET @aNameCountry := IFNULL(@aNameCountry, '');
		END IF;
		
		IF _Mode = 30 THEN
			SET @S := CONCAT(@aIDCountry, ' ', @aNameCountry);
		END IF;
		IF _Mode = 31 THEN
			SET @S := @aIDCountry;
		END IF;
		IF _Mode = 32 THEN
			SET @S := @aNameCountry;
		END IF;
	END IF;	--	_Mode = 30, 31, 32
	
	IF _Mode = 90 THEN
	--	Komunikat o zgodnosci numerow
		IF @aFullNumber = @_Value THEN
			SET @S := 'ZGODNY';
		ELSE	
			SET @S := '-';
		END IF;	--	Zgodnosc
	END IF;	--	_Mode = 20
	
	RETURN @S;
	
END$$



--  (49) (6 21) 833 70 40
--  49 6 21 833 70 40

DROP FUNCTION IF EXISTS `PIM_PhoneNumber`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `PIM_PhoneNumber`(
	_Mode VARCHAR(50),
	_Value VARCHAR(50),
	_Media_Type INTEGER
	) RETURNS VARCHAR(50)
    NO SQL
    DETERMINISTIC
BEGIN
	DECLARE DUPLICAT BOOLEAN DEFAULT FALSE;
	DECLARE DUPLICAT_Country BOOLEAN DEFAULT FALSE;
	DECLARE SO VARCHAR(30) DEFAULT _Value;
	DECLARE S VARCHAR(30) DEFAULT _Value;
	
	
    SET @aDefaultCountryPrefix := IFNULL(@aDefaultCountryPrefix, '');
	IF @aDefaultCountryPrefix = '' THEN
		SET @aDefaultCountryPrefix := '48';
	END IF;	
	
	SET @_Value := IFNULL(@_Value, '');
	SET DUPLICAT = (@_Value = _Value);
	IF NOT DUPLICAT THEN
	--	Parsuje _Value
		SET @S = '';
		SET @_Value = _Value;
		SET @WrongNumber = FALSE;
		SET @aClearNumber = ''; 
		SET @aLocalNumber = ''; 
		SET @aFmtNumber = ''; 
		SET @aExtC = '';
		SET @aCC = ''; 
		SET @aRC = '';
		SET @aWaitIdx = 0; 
		SET @aPlusIndex = 0; 
		SET @a00Index = 0;
		SET @aStationary = TRUE;
	--	SET @aClearNumber := FlushBadChar(aInputNumber, ' ');
		SET @aClearNumber = _Value;

			IF LOCATE(',', @aClearNumber) > 0 THEN
				SET @WrongNumber = TRUE;
			END IF;
			IF LOCATE(';', @aClearNumber) > 0 THEN
				SET @WrongNumber = TRUE;
			END IF;
			
		IF (_MODE = 'REPAIR') AND (NOT @WrongNumber) THEN
			SET @aClearNumber = LTRIM(@aClearNumber);
			SET @aClearNumber = RTRIM(@aClearNumber);
			SET @aClearNumber = REPLACE(@aClearNumber, '/', 'w');
			SET @aClearNumber = REPLACE(@aClearNumber, 'w.', 'w');
			SET @aClearNumber = REPLACE(@aClearNumber, 'W', 'w');
			SET @aClearNumber = REPLACE(@aClearNumber, '#', 'w');
			SET @S1 := '';
			SET @I := 1;
			SET @Digids = 0;
			WHILE @I <= LENGTH(@aClearNumber) DO
				SET @S1 := SUBSTRING(@aClearNumber, @I, 1);
				IF NOT (@S1 in ('0','1','2','3','4','5','6','7','8','9','(',')','-','+','w',' ')) THEN
					SET @WrongNumber = TRUE;
				END IF;	
				IF @WrongNumber THEN
					SET @I := LENGTH(@aClearNumber) + 1;
				ELSE	
					SET @I := @I + 1;
				END IF;
			END WHILE;
		END IF;	-- _MODE = 'REPAIR'
		
		IF (_MODE != 'REPAIR') THEN
			IF (NOT @WrongNumber) THEN
				SET @aClearNumber = LTRIM(@aClearNumber);
				SET @aClearNumber = RTRIM(@aClearNumber);
				SET @aClearNumber = REPLACE(@aClearNumber, '/', 'w');
				SET @aClearNumber = REPLACE(@aClearNumber, 'w.', 'w');
				SET @aWaitIdx = Locate('#', @aClearNumber);
				IF @aWaitIdx < 1 THEN
					SET @aWaitIdx = Locate('w', @aClearNumber);
				END IF;
				IF @aWaitIdx < 1 THEN
					SET @aWaitIdx := Locate('W', @aClearNumber);
				END IF;
				IF @aWaitIdx >= 1 THEN
					SET @aExtC := SUBString(@aClearNumber, @aWaitIdx + 1, Length(@aClearNumber));
					IF Length(@aExtC) > 3 THEN
						SET @aExtC := ''; ELSE
						SET @aClearNumber := SUBString(@aClearNumber, 1, @aWaitIdx - 1);
					END IF;	
				ELSE
					SET @aExtC := '';
				END IF;
				
				SET @aClearNumber := REPLACE(@aClearNumber, '(', '');
				SET @aClearNumber := REPLACE(@aClearNumber, ')', '');
				
				SET @S := SUBString(@aClearNumber, 1, 2);
				IF (@S = '0-') OR (@S = '0 ')  THEN
					SET @aClearNumber := SUBString(@aClearNumber, 3, LENGTH(@aClearNumber));
				END IF;
				
			    SET @aClearNumber := Trim(@aClearNumber);
				SET @aPlusIndex := Locate('+', @aClearNumber);
				SET @a00Index := Locate('00', @aClearNumber);
				SET @S := SUBString(@aClearNumber, 1, 1);
				IF (@a00Index <> 1) AND (@S = '0') THEN
					SET @aClearNumber := SUBString(@aClearNumber, 2, LENGTH(@aClearNumber));
				END IF;
				
				SET @S := '';
				SET @S1 := '';
				SET @I := 1;
				WHILE @I <= LENGTH(@aClearNumber) DO
					SET @S1 := SUBSTRING(@aClearNumber, @I, 1);
					IF @S1 in ('0','1','2','3','4','5','6','7','8','9') THEN
						SET @S = CONCAT(@S, @S1);
					END IF;	
					SET @I := @I + 1;
				END WHILE;
				SET @aClearNumber := @S;
				
				IF @aPlusIndex = 1 THEN
					SET @aCC := SUBSTRING(@aClearNumber, 1, 2);
					SET @aClearNumber := SUBSTRING(@aClearNumber, 3, Length(@aClearNumber));
				ELSE
					IF @a00Index = 1 THEN
						SET @aCC := SUBSTRING(@aClearNumber, 3, 2);
						SET @aClearNumber := SUBSTRING(@aClearNumber, 5, Length(@aClearNumber));
					ELSE
						IF Length(@aClearNumber) >= 9 + 2 THEN
							SET @aCC := SUBSTRING(@aClearNumber, 1, 2);
							SET @aClearNumber := SUBSTRING(@aClearNumber, 3, Length(@aClearNumber));
						ELSE
							SET @aCC := @aDefaultCountryPrefix;
						END IF;	
					END IF;	
				END IF;	
				
				IF Length(@aClearNumber) <= 7 THEN
					SET _Media_Type = 0;
					IF Length(@aClearNumber) <= 4 THEN
						SET @aRC = '';
					ELSE
						SET @aRC = '58';
					END IF;	
					SET @aLocalNumber = @aClearNumber;
					SET @aStationary = True;
				ELSE
					IF _Media_Type < 0 THEN		-- Daj domyslne
						SET @aRC := SUBSTRING(@aClearNumber, 1, 2);
						IF (@aCC = '48') AND ((@aRC < '70')or(@aRC IN ('71','76','77','81','82','74'))) and not(@aRC IN ('50','51','60','66','69')) THEN
							SET _Media_Type = 0;
						ELSE
							SET _Media_Type = 2;
						END IF;
					END IF;
					
					IF _Media_Type IN (0,1) THEN		--	Tel,Faks	-	Zawsze jako stacjonarny +YY ZZ XXX XX XX
						--	stacjonarny
						SET @aStationary := TRUE;	SET @ST := TRUE; 
						SET @aRC := SUBSTRING(@aClearNumber, 1, 2);
						SET @aLocalNumber := SUBSTRING(@aClearNumber, 3, Length(@aClearNumber));
					END IF;	
					IF _Media_Type = 2 THEN			--	Komorka	-	Zawsze jako NIEstacjonarny +YY ZZX XXX XXX
						--	nie stacjonarny
						SET @aStationary := FALSE;	SET @ST := FALSE;
						SET @aRC := SUBSTRING(@aClearNumber, 1, 3);
						SET @aLocalNumber := SUBSTRING(@aClearNumber, 4, Length(@aClearNumber));
					END IF;	
				END IF;
				
				
				IF @aStationary THEN
					IF Length(@aLocalNumber) < 5 THEN
						SET @aFmtNumber := @aLocalNumber; 
					ELSE	
						SET @aFmtNumber := CONCAT(SUBSTRING(@aLocalNumber, 1, 3), ' ', SUBSTRING(@aLocalNumber, 4, 2), ' ', SUBSTRING(@aLocalNumber, 6, 10)); 
					END IF;
				ELSE
					SET @aFmtNumber := CONCAT(SUBSTRING(@aLocalNumber, 1, 3), ' ', SUBSTRING(@aLocalNumber, 4, 10));
				END IF;
				
				SET @aCC := IFNULL(@aCC, '');
			END IF;	-- NOT @WrongNumber
			
		END IF;	-- _MODE != 'REPAIR'
	END IF;	--	Algorytmu

	IF _Mode = 'REPAIR' THEN
		SET @S = @WrongNumber;
	ELSE	
		IF NOT @WrongNumber THEN
		--	FULL Numer BEZ Formatu	
			SET @aFullNumber := CONCAT('+', @aCC, @aRC, @aLocalNumber, IF(@aExtC != '', CONCAT('w', @aExtC), ''));
		ELSE
			SET @S := @_Value;
			SET _Mode = -1; -- oznacza exit
		END IF;
	END IF;	-- _MODE = 'REPAIR'
	
	
	IF (_Mode = 'FULL') THEN	-- dawniej (_Mode = 0)
	--	Full BEZ Formatu
		IF IFNULL(@_Value, '') = '' THEN
			SET @S = '';
		ELSE	
			SET @S = @aFullNumber;
		END IF;	
	END IF;	--	_Mode = 2
	
	IF (_Mode IN ('FMT1', 'FMT2')) OR (_Mode in ('1', '2')) THEN
	--	Full Format
		SET @S := CONCAT(IF((_Mode = 1)OR(_Mode = 'FMT1'), '+', '00'), @aCC, IF(@aRC != '', CONCAT(' ' , @aRC), ''), ' ', @aFmtNumber, IF(@aExtC != '', CONCAT(' w ', @aExtC), ''));
	END IF;	--	_Mode = 'FULL'
	
	IF (_Mode = 'K_') OR (_Mode = 10) THEN
	--	NUMER w Kanonicznym formacie
		SET @S := CONCAT('+', @aCC, IF(@aRC != '', CONCAT('(' , @aRC, ')'), ''), @aLocalNumber, IF(@aExtC != '', CONCAT('w', @aExtC), ''));
	END IF;	--	_Mode = 10
	IF (_Mode = 'K_FMT') OR (_Mode = 11) THEN
	--	NUMER Formatowany w Kanonicznym formacie
		SET @S := CONCAT('+', @aCC, IF(@aRC != '', CONCAT('(' , @aRC, ')'), ''), ' ', @aFmtNumber, IF(@aExtC != '', CONCAT(' w ', @aExtC), ''));
	END IF;	--	_Mode = 11
	
	IF (_Mode = 'NUM') OR (_Mode = 50) THEN
	--	Tylko NUMER
		SET @S := @aClearNumber;
	END IF;	--	_Mode = 50
	IF (_Mode = 'NUMLOCAL') OR (_Mode = 51) THEN
	--	Tylko Lokalny NUMER
		SET @S := @aLocalNumber;
	END IF;	--	_Mode = 51
	IF (_Mode = 'CC') OR (_Mode = 52) THEN
	--	Tylko Prefix krajowy
		SET @S := @aCC;
	END IF;	--	_Mode = 52
	
	IF (_Mode = 'EXTC') OR (_Mode = 53) THEN
	--	Tylko Wewn
		SET @S := @aExtC;
	END IF;	--	_Mode = 53
	
	IF (_Mode = 'FULL_NO_EXTC') OR (_Mode = 54) THEN
	--	To co 0 ale BEZ  Wewn
		IF _Value != '' THEN
			SET @S := CONCAT('+', @aCC, @aRC, @aLocalNumber);
		ELSE
			SET @S := '';
		END IF;	

	END IF;	--	_Mode = 54
	
	IF (_Mode = 'FMT_NO_EXTC') OR (_Mode = 55) THEN
	--	To co 1 ale BEZ  Wewn
		SET @S := CONCAT(IF(_Mode = 1, '+', '00'), @aCC, IF(@aRC != '', CONCAT(' ' , @aRC), ''), ' ', @aFmtNumber);

	END IF;	--	_Mode = 55
	
	
	IF (_Mode IN ('CC_FULL', 'CC_ID', 'CC_NAME')) OR (_Mode IN (30,31,32)) THEN
	--	Kraj 
		SET @aCC_Save := IFNULL(@aCC_Save, '');
		SET DUPLICAT_Country := (@aCC_Save = @aCC);	
		SET @aCC_Save := @aCC;
		SET @DUPLICAT_Country := DUPLICAT_Country;
		IF NOT DUPLICAT_Country THEN
			SET @aIDCountry := '';
			SET @aNameCountry := '';
			SELECT 
				gc.IDCountry, g.Name 
			INTO 
				@aIDCountry, @aNameCountry 
			FROM geo_country_codes gc, geo_country g 
			WHERE 
				gc.IDCountry = g.Code AND 
				gc.CodeTel = @aCC AND
				gc.CodeTel != 0
			LIMIT 1
			;
			SET @aIDCountry := IFNULL(@aIDCountry, '');
			SET @aNameCountry := IFNULL(@aNameCountry, '');
		END IF;
		
		IF (_Mode = 'CC_FULL') OR (_Mode = 30) THEN
			SET @S := CONCAT(@aIDCountry, ' ', @aNameCountry);
		END IF;
		IF (_Mode = 'CC_ID') OR (_Mode = 31) THEN
			SET @S := @aIDCountry;
		END IF;
		IF (_Mode = 'CC_NAME') OR (_Mode = 32) THEN
			SET @S := @aNameCountry;
		END IF;
	END IF;	--	_Mode = 30, 31, 32
	
	IF _Mode = 90 THEN
	--	Komunikat o zgodnosci numerow
		IF @aFullNumber = @_Value THEN
			SET @S := 'ZGODNY';
		ELSE	
			SET @S := '-';
		END IF;	--	Zgodnosc
	END IF;	--	_Mode = 20
	
	RETURN @S;
	
END$$




/*
====================================================================================================================================================================================
	NOTATNIK
====================================================================================================================================================================================
--  (49) (6 21) 833 70 40
--  49 6 21 833 70 40


SET @Adr = '525579414';
SET @Adr = '601601601';
SET @Adr = '5579414';
SET @Adr = '+49625579414';

SELECT PIM_CalcPhoneNumber(@Adr, 54);


SELECT 
@aStationary,@ST,
PIM_CalcPhoneNumber(@Adr, 102),
PIM_CalcPhoneNumber(@Adr, 101)
;

SELECT 
PIM_PhoneNumber('1',@Adr,2)
; 
SELECT 
PIM_PhoneNumber('1',@Adr,1)
; 
SELECT 
PIM_PhoneNumber('1',@Adr,-1)
; 
SELECT 
PIM_PhoneNumber(0,@Adr,-1)
; 
SELECT 
PIM_PhoneNumber('CC_ID',@Adr,-1)
; 


SELECT
@aCC,@aRC,@aFmtNumber
;

SELECT * FROM v_pim_media_types;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/

DROP PROCEDURE IF EXISTS `PIM_E_Tool`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PIM_E_Tool`(
	IN _MODE VARCHAR(50),
	IN _Kontakt_Mode VARCHAR(10),
	IN _GUID_KO VARCHAR(38)
	)
	READS SQL DATA
BEGIN
	/*
	_MODE

	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'INIT':	
		Kreowanie tabel tymczasowych 
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('INIT', @K, @G);
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'PREPARE':	
		Przygotuj edycje
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('PREPARE', @K, @G);
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'EDIT':	
		Przygotuj edycje
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('EDIT', @K, @G);
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'APPLY':	
		Zapis
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('APPLY', @K, @G);
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'FR_VIEW'
		Ogladanie
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('FR_VIEW', @K, @G);
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'FR_DETAL'
		Ogladanie
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('FR_DETAL', 'K', 'INT');
		CALL PIM_E_Tool('FR_DETAL', 'O', '{BFA09421-3393-11D7-B676-00304F0902E2}'); -- jmam
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'REPAIR'
		Ogladanie
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('REPAIR', '', '');
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	*/	
	DECLARE _CurrOsobaGUID VARCHAR(38);
	DECLARE _FROM_K BOOLEAN;
	DECLARE RecordMode_Sluzbowe VARCHAR(38);
	DECLARE RecordMode_Not_Sluzbowe VARCHAR(38);

	
	SET _CurrOsobaGUID = @session.CurrOsobaGUID;
	SET _FROM_K = (LOCATE('K', _Kontakt_Mode) = 1);
	SET RecordMode_Sluzbowe = IF(_FROM_K, 'KO', 'OK');
	SET RecordMode_Not_Sluzbowe = IF(_FROM_K, 'KK', 'OO');
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'INIT':	
		Kreowanie tabel tymczasowych 
		CALL PIM_E_Tool('INIT', @K, @G);
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'INIT' THEN
		SET _CurrOsobaGUID = @session.CurrOsobaGUID;
		SET @kh_GUID = '';
		SET @kh_Symbol = '';
		SET @kh_Nazwa = '';
		IF NOT _FROM_K THEN
			SELECT
				kh.GUID, kh.Symbol, kh.Nazwa
			INTO
				@kh_GUID, @kh_Symbol, @kh_Nazwa
			FROM `kontrah` kh
			INNER JOIN `osoby` o ON o.KontrGUID = kh.GUID	
			WHERE
				o.GUID = _GUID_KO	
			;
		END IF;
		SET @kh_GUID = _VarStr_NotNUll(@kh_GUID);
		SET @kh_Symbol = _VarStr_NotNUll(@kh_Symbol);
		SET @kh_Nazwa = _VarStr_NotNUll(@kh_Nazwa);
	END IF; -- _MODE = 'INIT'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'PREPARE':	
		Przygotuj edycje
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('PREPARE', @K, @G);
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'PREPARE' THEN
		-- IF _Kontakt_Mode = 'K' THEN
		-- END IF;
		-- K - oznacza firmowe F i sluzbowe S, O - prywatne P i sluzbowe S
		DROP TEMPORARY TABLE IF EXISTS `t_pim_e_guids`;
		CREATE TEMPORARY TABLE `t_pim_e_guids`
			SELECT
				_VarINT(0) AS LP,
				k.Priorytet, 
				k.IDN,
				k.GUID, 
				k.typ_medium,
				k.Opis,
				_VarInt(0) AS IsDefaultOpis,
				k.Adres,
				k.Opis AS Descryption_Input,
				IF(_FROM_K,
				  Concat('K', IF(_VarStr_NotNull(k.OsobaGUID) != '', 'O', 'K')),
				  Concat('O', IF(_VarStr_NotNull(k.KontrGUID) != '', 'K', 'O'))
				)  AS RecordMode  
			FROM kontakty k
			WHERE
				IF(_FROM_K, k.KontrGUID = _GUID_KO AND _VarStr_NotNull(k.OsobaGUID) = '', k.OsobaGUID = _GUID_KO)
		;
		UPDATE `t_pim_e_guids` kg -- , `v_pim_media_examples` ve
		SET
			kg.IsDefaultOpis = IFNULL((SELECT 1 FROM `v_pim_media_examples` ve WHERE ve.Descryption_Example = kg.Opis), 0)
		;
		SET @LP = 1;
		SET @typ_medium = -1;
		UPDATE `t_pim_e_guids`
		SET
			LP = IF(typ_medium = @typ_medium, @LP := @LP + 1, @LP := 1),
			typ_medium = @typ_medium := typ_medium
		WHERE
			RecordMode = 'OK'	
			AND IsDefaultOpis = 1
		ORDER BY typ_medium, Priorytet DESC, IDN
		;
		UPDATE `t_pim_e_guids` kg, v_pim_media_types mt
		SET
			kg.Descryption_Input = IF(kg.LP = 1,  mt.Descryption, CONCAT(mt.Descryption, ' ', kg.LP))
		WHERE
			kg.LP > 0 AND
			mt.ID = kg.typ_medium -- AND typ_medium = 0
		;		
		
		
		/*jmam
		UPDATE `kontakty` k, `t_pim_e_guids` kg
		SET
			k.Opis = IF(kg.IsDefaultOpis = 1, kg.Descryption_Input, k.Opis),
			k.Adres = IF(kg.typ_medium IN (0,1,2), PIM_CalcPhoneNumber(k.Adres, 0), k.Adres)
		WHERE
			k.GUID = kg.GUID -- AND typ_medium = 0
		;
		*/-- RecordMode_Sluzbowe = IF(_FROM_K, 'KO', 'OK');
		-- RecordMode_Not_Sluzbowe = IF(_FROM_K, 'KK', 'OO');
		
		DROP TEMPORARY TABLE IF EXISTS `t_pim_e_me`;
		CREATE TEMPORARY TABLE `t_pim_e_me`
			SELECT DISTINCT k.Descryption_Input, k.typ_medium, k.RecordMode FROM `t_pim_e_guids` k
		;
		-- Wstaw exapmle dla służbowych
		IF NOT _FROM_K THEN
		INSERT INTO `t_pim_e_me`
			SELECT DISTINCT ve.Descryption_Example, ID_Media, RecordMode_Sluzbowe FROM `v_pim_media_examples` ve
		;
		END IF;
		-- Wstaw exapmle dla prywatncych/firmowch
		INSERT INTO `t_pim_e_me`
			SELECT DISTINCT ve.Descryption_Example, ID_Media, RecordMode_Not_Sluzbowe FROM `v_pim_media_examples` ve
		;
		
		DROP TEMPORARY TABLE IF EXISTS `t_pim_e_me1`;
		CREATE TEMPORARY TABLE `t_pim_e_me1`
			SELECT 
				me.typ_medium, me.Descryption_Input, me.RecordMode
				FROM `t_pim_e_me` me
				LEFT JOIN `t_pim_e_guids` k ON k.Descryption_Input = me.Descryption_Input and k.RecordMode = me.RecordMode
				WHERE k.GUID IS NULL
		;
		INSERT INTO `t_pim_e_guids`
			(
			lp, typ_medium, Descryption_Input, GUID, RecordMode
			)
			(
			SELECT 
				0, me.typ_medium, me.Descryption_Input, NewGUID(), me.RecordMode
			FROM `t_pim_e_me1` me
			)	
		;
		DROP TEMPORARY TABLE IF EXISTS `t_pim_e_me1`;
		
		
	END IF; -- _MODE = 'PREPARE'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'EDIT':	
		Przygotuj edycje
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('EDIT', @K, @G);
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'EDIT' THEN
		DROP TEMPORARY TABLE IF EXISTS `t_pim_e_A`;
		CREATE TEMPORARY TABLE `t_pim_e_A`
			SELECT
				-- _ConcatSTR_NotEqu(kg.Descryption_Input, k.Opis, ' ') AS Descryption_Input,
				_VarInt(0) AS NewRecord,
				_VarInt(0) AS RecordChanged,
				kg.RecordMode,
				kg.LP, 
				kg.IsDefaultOpis,
				kg.Descryption_Input,
				k.GUID, 
				k.kontrGUID,k.osobaGUID,
				IFNULL(k.typ_medium, kg.typ_medium) AS typ_medium,
				k.rodzaj,
				k.adres,k.opis,
				k.nieaktywny,k.priorytet,k.newsletter
			FROM `t_pim_e_guids` kg
			LEFT JOIN `kontakty` k ON k.GUID = kg.GUID
		;
		UPDATE `t_pim_e_A` k
		SET
			NewRecord = 1,
			k.GUID = NewGUID(),
			lp = 0, -- Descryption_Input, Kontakt_Mode,
			k.kontrGUID = IF(_FROM_K, _GUID_KO, IF(RecordMode = 'OK', @kh_GUID, '')), 
			osobaGUID = IF(_FROM_K, '', _GUID_KO),
			typ_medium = typ_medium,
			rodzaj = 0,
			adres = '',
			opis = Descryption_Input,
			nieaktywny = 0,
			priorytet = 0,
			newsletter = 0
		WHERE
			k.GUID IS NULL
		;
		DROP TEMPORARY TABLE IF EXISTS `t_pim_e`;
		CREATE TEMPORARY TABLE `t_pim_e`
			SELECT
				k.*,
				kh.Nazwa AS K_Nazwa,
				_ConcatStr(o.Nazwisko, _ConcatStr(o.Imie1, o.Imie2, ' '), ' ') AS O_Nazwisko,
				t.Descryption AS ex_Descryption, 
				t.media_notify AS ex_media_notify, 
				t.media_notify_code AS ex_media_notify_code, 
				_VarStr(IF(t.media_notify = 0, 'PHONE', IF(t.media_notify IN (1, 2), 'INTERNET', 'OTHER'))) AS ex_media_TAB, 
				_VarStr(IF(t.media_notify = 0, PIM_CalcPhoneNumber(k.adres, 54), '')) AS ex_Numer_Edit,
				_VarStr(IF(t.media_notify = 0, PIM_CalcPhoneNumber(k.adres, 53), '')) AS ex_Numer_Ext,
				_VarStr(IF(t.media_notify = 0, PIM_CalcPhoneNumber(k.adres, 52), '')) AS ex_Numer_CC,
				t.ImgIdx AS ex_ImgIdx 
			FROM `t_pim_e_A` k
			LEFT JOIN v_pim_media_types t ON t.ID = k.typ_medium
			LEFT JOIN `kontrah` kh ON kh.GUID = k.KontrGUID	
			LEFT JOIN `osoby` o ON o.GUID = k.osobaGUID	
		;	
			
	END IF; -- _MODE = 'EDIT'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'APPLY':	
		Zapis
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('APPLY', @K, @G);
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'APPLY' THEN
		UPDATE `t_pim_e` k
		SET
			k.RecordChanged = 2
		WHERE
			_VarStr_NotNULL(k.Adres) = ''
			AND k.RecordChanged != 0
		;
		DELETE k FROM `kontakty` k, `t_pim_e` ke
		WHERE
			k.GUID = ke.GUID
			AND ke.RecordChanged = 2
		;
		INSERT INTO `kontakty`
				(
				GUID, 
				kontrGUID,osobaGUID,typ_medium,rodzaj,
				adres,opis,
				nieaktywny,priorytet,newsletter
				)
				SELECT
					k.GUID, 
					k.kontrGUID,k.osobaGUID,k.typ_medium,k.rodzaj,
					k.adres,k.opis,
					k.nieaktywny,k.priorytet,k.newsletter
				FROM `t_pim_e` k
				WHERE
					k.RecordChanged = 1
			ON DUPLICATE KEY UPDATE 
				-- k.kontrGUID,k.osobaGUID,
				typ_medium = k.typ_medium,
				rodzaj = k.rodzaj,
				adres = k.adres,
				opis = k.opis,
				nieaktywny = k.nieaktywny,
				priorytet = k.priorytet,
				newsletter = k.newsletter
			;	
	END IF; -- _MODE = 'APPLY'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'FR_VIEW'
		Ogladanie
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('FR_VIEW', @K, @G);
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'FR_VIEW' THEN
	
		DROP TEMPORARY TABLE IF EXISTS `t_pim_fr_view`;
		CREATE TEMPORARY TABLE `t_pim_fr_view`
			SELECT 
				t.Descryption AS ex_Descryption,
				t.media_notify AS ex_media_notify,
				t.media_notify_code AS ex_media_notify_code,
				t.ImgIdx AS ex_ImgIdx,
				_VarStr(IF(t.media_notify = 0, PIM_CalcPhoneNumber(k.adres, 1), k.adres)) AS ex_Numer,
				-- _VarStr(IF(t.media_notify = 0, PIM_CalcPhoneNumber(k.adres, 1), k.Adres)) AS ex_Numer_Fmt,
				_VarStr(IF(t.media_notify = 0, PIM_CalcPhoneNumber(k.adres, 52), '')) AS ex_Numer_CC,
				_VarStr(IF(t.media_notify = 0, PIM_CalcPhoneNumber(k.adres, 50), '')) AS ex_Numer_Clear,
				_VarStr(IF(t.media_notify = 0, PIM_CalcPhoneNumber(k.adres, 30), '')) AS ex_Numer_CC_Opis,
				IF(_FROM_K,
				  Concat('K', IF(_VarStr_NotNull(k.OsobaGUID) != '', 'O', 'K')),
				  Concat('O', IF(_VarStr_NotNull(k.KontrGUID) != '', 'K', 'O'))
				)  AS RecordMode,  
				kh.Nazwa AS K_Nazwa,
				_ConcatStr(o.Nazwisko, _ConcatStr(o.Imie1, o.Imie2, ' '), ' ') AS O_Nazwisko,
				_VarStr(IF(t.media_notify = 0, '  Telefony', IF(t.media_notify IN (1, 2), ' Internet', 'Inne'))) AS ex_media_TAB, 

				k.GUID,
				k.kontrGUID,
				k.osobaGUID,
				k.typ_medium,
				k.rodzaj,
				k.adres,
				IF(k.opis = t.Descryption, '', k.opis) AS opis,
				k.nieaktywny,k.priorytet,
				k.newsletter 
			FROM kontakty k
			LEFT JOIN v_pim_media_types t ON t.ID = k.typ_medium
			LEFT JOIN `kontrah` kh ON kh.GUID = k.KontrGUID	
			LEFT JOIN `osoby` o ON o.GUID = k.osobaGUID	
			WHERE
				IF(_FROM_K, k.KontrGUID = _GUID_KO AND _VarStr_NotNull(k.OsobaGUID) = '', k.OsobaGUID = _GUID_KO)
			;
			SELECT
				k.*
			FROM `t_pim_fr_view` k
			;
		
	END IF; -- _MODE = 'FR_VIEW'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'FR_DETAL'
		Ogladanie
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('FR_DETAL', 'K', 'INT');
		CALL PIM_E_Tool('FR_DETAL', 'O', '{BFA09421-3393-11D7-B676-00304F0902E2}'); -- jmam
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'FR_DETAL' THEN
		CALL _Globals_Get('Owner', @session.Owner, NULL);
		SET @IsFmt = (@session.Owner != 'PBUCH');
		
		IF _FROM_K THEN
			SELECT
				IF(k.osobaGUID <> '', CONCAT(o.nazwisko, CONCAT(' ', o.imiona)), ' Kontakty firmowe') AS Nazwa_NazwiskoImiona, 
				_VarStr_NotNULL(o.stanowisko) AS Stanowisko, 
				_VarStr_NotNULL(o.uwagi_poufne) AS ZakresZadan,
				_VarStr_NotNULL(d.opis) AS Dzial_Opis, 
				o.priorytet AS PriorytetOsoby,

				_VarStr(group_concat(distinct if(k.typ_medium = 0, IF(@IsFmt,PIM_CalcPhoneNumber(k.adres,1), k.adres), '') order by k.priorytet DESC separator ',   ')) AS A_Tel,
				_VarStr(group_concat(distinct if(k.typ_medium = 1, IF(@IsFmt,PIM_CalcPhoneNumber(k.adres,1), k.adres), '') order by k.priorytet DESC separator ', ')) AS A_Fax,
				_VarStr(group_concat(distinct if(k.typ_medium = 2, IF(@IsFmt,PIM_CalcPhoneNumber(k.adres,1), k.adres), '') order by k.priorytet DESC separator ', ')) AS A_Gsm, 
				_VarStr(group_concat(distinct if(k.typ_medium = 3, k.adres, '') order by k.priorytet DESC separator ', ')) as A_Email,
				_VarStr(group_concat(distinct if(k.typ_medium = 4, k.adres, '') order by k.priorytet DESC separator ', ')) as A_www,

				k.OsobaGUID as OsobaGUID, 
				k.KontrGUID as KontrGUID 

			FROM kontakty k 
			LEFT JOIN osoby o on o.guid = k.osobaguid 
			LEFT JOIN kontrah kn on kn.guid = k.kontrguid 
			LEFT JOIN dzialy d on o.dzial = d.idn 
			WHERE 
				_VarStr_NotNULL(k.adres) <> '' AND 
				k.kontrguid = _GUID_KO AND 
				(IFNULL(o.nieaktywny,0)=0)
			GROUP BY k.osobaguid 
			ORDER BY Nazwa_NazwiskoImiona, o.priorytet desc, k.priorytet desc 
			;
		ELSE	
			SELECT
				IF(k.KontrGUID <> '', CONCAT(kn.nazwa, ''), ' Kontakty osobiste') AS Nazwa_NazwiskoImiona, 
				_VarStr_NotNULL(o.stanowisko) AS Stanowisko, 
				_VarStr_NotNULL(o.uwagi_poufne) AS ZakresZadan,
				_VarStr_NotNULL(d.opis) AS Dzial_Opis, 
				o.priorytet AS PriorytetOsoby,

				_VarStr(group_concat(distinct if(k.typ_medium = 0, IF(@IsFmt,PIM_CalcPhoneNumber(k.adres,1), k.adres), '') order by k.priorytet DESC separator ',   ')) AS A_Tel,
				_VarStr(group_concat(distinct if(k.typ_medium = 1, IF(@IsFmt,PIM_CalcPhoneNumber(k.adres,1), k.adres), '') order by k.priorytet DESC separator ', ')) AS A_Fax,
				_VarStr(group_concat(distinct if(k.typ_medium = 2, IF(@IsFmt,PIM_CalcPhoneNumber(k.adres,1), k.adres), '') order by k.priorytet DESC separator ', ')) AS A_Gsm, 
				_VarStr(group_concat(distinct if(k.typ_medium = 3, k.adres, '') order by k.priorytet DESC separator ', ')) as A_Email,
				_VarStr(group_concat(distinct if(k.typ_medium = 4, k.adres, '') order by k.priorytet DESC separator ', ')) as A_www,

				k.OsobaGUID as OsobaGUID, 
				k.KontrGUID as KontrGUID 

			FROM kontakty k 
			LEFT JOIN osoby o on o.guid = k.osobaguid 
			LEFT JOIN kontrah kn on kn.guid = k.kontrguid 
			LEFT JOIN dzialy d on o.dzial = d.idn 
			WHERE 
				_VarStr_NotNULL(k.adres) <> '' AND 
				k.osobaguid = _GUID_KO AND 
				(IFNULL(o.nieaktywny,0)=0)
			GROUP BY k.KontrGUID
			ORDER BY Nazwa_NazwiskoImiona, o.priorytet desc, k.priorytet desc 
			;
		END IF; 
	END IF; -- _MODE = 'FR_DETAL'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'REPAIR'
		Ogladanie
		Parametry
			_Kontakt_Mode		'K'/'O'
			_GUID_KO			GUID kontrahenta lub osoby
		CALL PIM_E_Tool('REPAIR', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'REPAIR' THEN
		
		IF _Kontakt_Mode = '' THEN
			DROP TEMPORARY TABLE IF EXISTS `t_pim_analiza`;
			CREATE TEMPORARY TABLE `t_pim_analiza`
				SELECT
					k.*,
					PIM_CalcPhoneNumber(k.adres,1) AS `Number`	
				FROM kontakty k
				WHERE
					k.typ_medium in (0,1,2)
			;
			UPDATE `t_pim_analiza` pa
			SET
				InvalidNumber = (LOCATE(',', `Number`) > 0) OR (LOCATE(';', `Number`) > 0) 
			;
			UPDATE `t_pim_analiza` pa
			SET
				InvalidNumber = PIM_PhoneNumber('REPAIR', pa.Adres, -1) 
			;
			
			UPDATE `kontakty` k, `t_pim_analiza` pa
			SET 
				k.InvalidNumber = pa.InvalidNumber
			WHERE
				k.GUID = pa.GUID
			;
		END IF;
		
		IF _Kontakt_Mode != '' THEN
			SELECT
				pa.InvalidNumber,
				pa.Number, pa.Adres,
				pa.*
			FROM `t_pim_analiza` pa
			WHERE
				TRUE AND IF(_Kontakt_Mode = '1', InvalidNumber, NOT InvalidNumber)
			;
		END IF;
	END IF; -- _MODE = 'REPAIR'
END$$



/*
SET @K = 'O';
SET @G = '{FFC6001B-426E-4196-900B-436DE82EB193}'; -- Adam
SET @G = '{BFA09421-3393-11D7-B676-00304F0902E2}'; -- jmam

SET @K = 'K';
SET @G = 'INT';
SELECT * FROM kontakty where kontrGUID = 'INT';
SELECT * 
FROM kontakty 
where 
osobaGUID = @G
ORDER BY IDN
;

CALL PIM_E_Tool('INIT', @K, @G);
CALL PIM_E_Tool('PREPARE', @K, @G);
CALL PIM_E_Tool('EDIT', @K, @G);

SELECT k.* FROM `t_pim_e_A` k;
SELECT k.* FROM `t_pim_e_guids` k;
SELECT * 
FROM `t_pim_e`;

DROP TABLE t_kontakty;
CREATE TABLE t_kontakty
SELECT * 
FROM `t_pim_e`;

SELECT * FROM t_kontakty 
-- order By ex_media_notify_code
;


CALL PIM_E_Tool('INIT', 'O', @G); CALL PIM_E_Tool('PREPARE', 'O', @G); CALL PIM_E_Tool('EDIT', 'O', @G);



CALL PIM_E_Tool('FR_DETAL', 'K', 'INT');
CALL PIM_E_Tool('FR_DETAL', 'O', '{BFA09421-3393-11D7-B676-00304F0902E2}'); -- jmam


*/

DELIMITER ;

