USE [Prince]
GO
/****** Object:  StoredProcedure [Common].[SYS_UpdateDefaultMobileNumber]    Script Date: 12/14/2025 15:23:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zareie - Transfered & Some Changed By Ghasem Babaie To Prince DB
-- Create Date: 1404.04.08
-- Description:	اصلاح موبایل پیش فرض اشخاص 
-- =============================================
ALTER PROCEDURE [Common].[SYS_UpdateDefaultMobileNumber]
       @Index         [Common].[_INT_],
       @CPN_ID        [Common].[_INT_]          
AS
BEGIN
    DECLARE @LKP_ID   [Common].[_INT_]
    SET @LKP_ID = 0; 
	SELECT @LKP_ID = [LKP_ID] FROM [Common].[Lookup] WHERE [LKP_Type] = 8 AND [LKP_EngCaption] = 'Contact_Mobile'
	
	-- ________________________________________	
	-- ________________________________________
    -- استخراج شماره موبایل هایی که ایراد دارند
    -- ________________________________________
    -- ________________________________________
    IF @Index = 1
    BEGIN
		WITH CTE
		AS
		(SELECT * FROM [Common].[ContactInformation] WHERE [CIN_LKP_Type] = @LKP_ID)
         
        -- بدون حالت پیش فرض  
		SELECT [CIN_CPN_ID] AS CPN_ID
		FROM CTE 
		WHERE [CIN_CPN_ID] NOT IN (SELECT [CIN_CPN_ID] 
									FROM CTE 
									WHERE [CIN_IsDefault] = 1)
         
		UNION ALL 
		 
		-- دوتا حالت پیش فرض دارد 
		SELECT [CIN_CPN_ID] AS CPN_ID
		FROM CTE
		WHERE [CIN_IsDefault] = 1 
		GROUP BY [CIN_CPN_ID], [CIN_IsDefault] 
		HAVING COUNT(1) > 1
		  
        UNION ALL
        
		-- شماره حالت پیش فرض و ثبت شده در اشخاص ، متفاوت هستند
		SELECT DISTINCT [CIN_CPN_ID] AS CPN_ID
		FROM CTE
			JOIN [Common].[CompanyPerson] ON ([CIN_CPN_ID] = [CPN_ID])
		WHERE [CIN_LKP_Type] = @LKP_ID AND 
				 [CIN_IsDefault] = 1 AND
				 [CIN_Value] <> [CPN_MobileNumber]
         
		UNION ALL

		-- شماره موبایل در جدول ثبت نشده
		SELECT [CPN_ID]
		FROM [Common].[CompanyPerson]			
		WHERE NOT((LTRIM(RTRIM(ISNULL([CPN_MobileNumber], ''))) = '') OR (LTRIM(RTRIM(ISNULL([CPN_MobileNumber], '0'))) = '0')) AND  
				[CPN_ID] NOT IN (SELECT [CIN_CPN_ID] FROM CTE)    
    END
    
    
    -- ________________________________________
    -- ________________________________________
    -- تصحیح شماره موبایل هایی که ایراد دارند
    -- ________________________________________
    -- ________________________________________
    IF @Index = 2
    BEGIN 
       DECLARE @CPN_MobileNumber  [Common].[_PHONE_] = ''
       
       SET @CPN_MobileNumber = (SELECT ISNULL([CPN_MobileNumber], '') FROM [Common].[CompanyPerson] WHERE [CPN_ID] = @CPN_ID )
       SET @CPN_MobileNumber = LTRIM(RTRIM(@CPN_MobileNumber))
       
       IF (ISNULL(@CPN_MobileNumber, '') <> '') AND (ISNULL(@CPN_MobileNumber, '0') <> '0')
       BEGIN
       
            IF ISNULL((SELECT COUNT(1) 
						FROM [Common].[ContactInformation] 
						WHERE [CIN_LKP_Type] = @LKP_ID AND 
								[CIN_CPN_ID] = @CPN_ID AND 
								[CIN_Value] = @CPN_MobileNumber),0) > 1
            BEGIN
               DELETE FROM [Common].[ContactInformation]
                WHERE [CIN_LKP_Type] = @LKP_ID AND 
					  [CIN_CPN_ID] = @CPN_ID AND 
					  [CIN_Value] = @CPN_MobileNumber AND 
					  [CIN_ID] <> (SELECT MIN([CIN_ID]) 
						            FROM [Common].[ContactInformation] 
						           WHERE [CIN_LKP_Type] = @LKP_ID AND 
								         [CIN_CPN_ID] = @CPN_ID AND 
								         [CIN_Value] = @CPN_MobileNumber)
            
            
            END       
       
       
			IF EXISTS (SELECT 1 
						FROM [Common].[ContactInformation] 
						WHERE [CIN_LKP_Type] = @LKP_ID AND 
								[CIN_CPN_ID] = @CPN_ID AND 
								[CIN_Value] = @CPN_MobileNumber)
			BEGIN
				UPDATE [Common].[ContactInformation]  
				SET [CIN_IsDefault] = 0
				WHERE [CIN_LKP_Type] = @LKP_ID AND 
						[CIN_CPN_ID] = @CPN_ID
				  
				UPDATE [Common].[ContactInformation]  
				SET [CIN_IsDefault] = 1
				WHERE [CIN_LKP_Type] = @LKP_ID AND 
						[CIN_CPN_ID] = @CPN_ID AND 
						[CIN_Value] = @CPN_MobileNumber		      
			END
			ELSE
			BEGIN
				UPDATE [Common].[ContactInformation]  
				SET [CIN_IsDefault] = 0
				WHERE [CIN_LKP_Type] = @LKP_ID AND 
						[CIN_CPN_ID] = @CPN_ID

				EXEC [Common].[CIN_InsertNew] @CPN_MobileNumber, 1, @CPN_ID, 1, NULL
			END   
       END 
       ELSE
       BEGIN
			IF EXISTS (SELECT [CIN_CPN_ID] 
						FROM [Common].[ContactInformation] 
						WHERE [CIN_LKP_Type] = @LKP_ID AND 
							[CIN_IsDefault] = 1 AND 
							[CIN_CPN_ID] = @CPN_ID 
						GROUP BY [CIN_CPN_ID], [CIN_IsDefault] 
						HAVING COUNT(1) <> 1)
			BEGIN
				UPDATE [Common].[ContactInformation]  
				SET [CIN_IsDefault] = 0
				WHERE [CIN_LKP_Type] = @LKP_ID AND 
						[CIN_CPN_ID] = @CPN_ID

				UPDATE [Common].[ContactInformation]  
				SET [CIN_IsDefault] = 1
				WHERE [CIN_ID] IN (SELECT MIN([CIN_ID]) 
									FROM [Common].[ContactInformation] 
									WHERE [CIN_LKP_Type] = @LKP_ID AND 
											[CIN_CPN_ID] = @CPN_ID)
			END            
          
			UPDATE [Common].[CompanyPerson]
			SET [CPN_MobileNumber] = (SELECT MIN([CIN_Value])
										FROM [Common].[ContactInformation]
										WHERE ([CIN_LKP_Type] = @LKP_ID) AND 
												([CIN_CPN_ID] = @CPN_ID) AND 
												([CIN_IsDefault] = 1) AND 
												(NOT([CIN_Value] LIKE '%[^0-9]%')) AND 
												(LEN([CIN_Value]) < 14))
			WHERE NOT((LTRIM(RTRIM(ISNULL([CPN_MobileNumber], ''))) = '') OR (LTRIM(RTRIM(ISNULL([CPN_MobileNumber], '0'))) = '0')) AND   
				  ([CPN_ID] = @CPN_ID)
       END
    END
END