--
-- (c)Copyrights 2007 Nederpelt Software
-- Written by Alwin Nederpelt
--
-- Converteerd telefoonnummers van b.v. 0174-610192 naar +31 (174) 610192
-- Gaat uit van land 'NL '
--
If OBJECT_ID('Nederpelt_Software_ConvertTel') IS NOT NULL DROP FUNCTION Nederpelt_Software_ConvertTel
GO
CREATE FUNCTION dbo.Nederpelt_Software_ConvertTel(@country as varchar(3),@telnr varchar(40))
RETURNS varchar(40)
AS
BEGIN
	DECLARE @i int
	DECLARE @firstspace int
	DECLARE @ForceServiceNumber int
	DECLARE @telnrnew as varchar(40)
	set @ForceServiceNumber = 0
	IF @country='NL '
		BEGIN
			BEGIN
				SET @telnrnew = ''
				SET @i = 0
				SET @firstspace = 0
				WHILE @i < LEN(@telnr)
					BEGIN
						SET @i = @i + 1
						IF (substring(@telnr,@i,1) IN ('0','1','2','3','4','5','6','7','8','9')) SET @telnrnew = @telnrnew + substring(@telnr,@i,1)
						if (len(@telnrnew)>0 and @firstspace = 0 and substring(@telnr,@i,1) in (')','-',' ')) set @firstspace = len(@telnrnew)
					END
			END
			IF substring(@telnrnew,1,4)='0800' SET @ForceServiceNumber=1
			IF substring(@telnrnew,1,4)='0900' SET @ForceServiceNumber=1			
			IF substring(@telnrnew,1,4)='0906' SET @ForceServiceNumber=1
			IF substring(@telnrnew,1,4)='0909' SET @ForceServiceNumber=1			
			IF LEN(@telnrnew)=10 or @ForceServiceNumber=1
				BEGIN
					IF (substring(@telnrnew,1,1)='0') 
						BEGIN
							SET @telnrnew = substring(@telnrnew,2,len(@telnrnew)-1)
							IF (@firstspace=0)
								SET @telnrnew = '+31 ('+@telnrnew
							ELSE
								SET @telnrnew = '+31 ('+SUBSTRING(@telnrnew,1,@firstspace-1)+') '+SUBSTRING(@telnrnew,@firstspace,len(@telnrnew)-@firstspace+1)
						END
					ELSE
						set @telnrnew=@telnr
				END
			ELSE
				set @telnrnew=@telnr
		END
	ELSE
		SET @telnrnew = @telnr
	RETURN (@telnrnew)
END
GO
UPDATE cicmpy SET cmp_tel=dbo.Nederpelt_Software_ConvertTel(cicmpy.cmp_fctry,cicmpy.cmp_tel),cmp_fax=dbo.Nederpelt_Software_ConvertTel(cicmpy.cmp_fctry,cicmpy.cmp_fax)
GO
UPDATE cicntp set cnt_f_tel=dbo.Nederpelt_Software_ConvertTel(cicmpy.cmp_fctry,cicntp.cnt_f_tel),cnt_f_mobile=dbo.Nederpelt_Software_ConvertTel(cicmpy.cmp_fctry,cicntp.cnt_f_mobile),cnt_f_fax=dbo.Nederpelt_Software_ConvertTel(cicmpy.cmp_fctry,cicntp.cnt_f_fax) from cicntp inner join cicmpy on cicmpy.cmp_wwn=cicntp.cmp_wwn
GO
If OBJECT_ID('Nederpelt_Software_ConvertTel') IS NOT NULL DROP FUNCTION Nederpelt_Software_ConvertTel
GO