USE [Prince]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		zareie
-- Create date: 2025-12-14 | 1404-09-23
-- Description:	
-- =============================================
create PROCEDURE [Common].[TroubleShooting_SearchDetail]
(   
    @Index              [Common]._INT_,
    @Item_ID			[Common]._INT_
)
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @CPN_NationalCode	[Common]._TINY_ = '',
	        @CPN_EconomicCode   [Common]._TINY_ = '',
	        @CPN_MobileNumber   [Common]._TINY_ = '',
	        @CMD_Code           [Common]._TINY_ = '',
	        @CPN_Type           [Common]._TINY_ = 0
	
	DECLARE @LKP_Contact_Mobile	[Common].[_INT_] = 0			
	    SET @LKP_Contact_Mobile = [Common].[LKP_EngCaptionToID]('Contact_Mobile', N'تلفن همراه', 30)
	
	-- شخص
	IF @Index = 1
	BEGIN

	   SELECT @CPN_NationalCode = CPN_NationalCode , @CPN_Type = CPN_Type 
	     FROM Common.CompanyPerson 
        WHERE (LTRIM(RTRIM(CPN_NationalCode)) COLLATE Latin1_General_BIN LIKE '%[^0-9]%')  AND 
              [CPN_ID] = @Item_ID  


	   SELECT @CPN_EconomicCode = CPN_EconomicCode , @CPN_Type = CPN_Type 
	     FROM Common.CompanyPerson 
        WHERE (LTRIM(RTRIM(CPN_EconomicCode)) COLLATE Latin1_General_BIN LIKE '%[^0-9]%')  AND 
              [CPN_ID] = @Item_ID   
        
 
	   SELECT @CPN_MobileNumber = CPN_MobileNumber , @CPN_Type = CPN_Type 
	     FROM Common.CompanyPerson 
        WHERE 1 = CASE WHEN LTRIM(RTRIM(CPN_MobileNumber)) COLLATE Latin1_General_BIN NOT LIKE '%[^0-9]%' THEN 
							 CASE WHEN LEN(LTRIM(RTRIM(CPN_MobileNumber))) = 11 THEN 
									   CASE WHEN SUBSTRING(LTRIM(RTRIM(CPN_MobileNumber)) , 1 ,2) = '09' THEN 0 ELSE 1 END
								  ELSE 1 
							 END
	                   ELSE 1 
	               END  AND 
              [CPN_ID] = @Item_ID  
        
       IF isnull(@CPN_MobileNumber,'')= ''
       BEGIN

	   SELECT TOP(1)@CPN_MobileNumber = [CIN_Value]  , @CPN_Type = CPN_Type
	     FROM Common.[ContactInformation] 
	     JOIN Common.CompanyPerson ON [CIN_CPN_ID] = [CPN_ID]
        WHERE 1 = CASE WHEN LTRIM(RTRIM([CIN_Value])) COLLATE Latin1_General_BIN NOT LIKE '%[^0-9]%' THEN 
							 CASE WHEN LEN(LTRIM(RTRIM([CIN_Value]))) = 11 THEN 
									   CASE WHEN SUBSTRING(LTRIM(RTRIM([CIN_Value])) , 1 ,2) = '09' THEN 0 ELSE 1 END
								  ELSE 1 
							 END
	                   ELSE 1 
	               END  AND 
              [CIN_LKP_Type] IN (@LKP_Contact_Mobile) AND 
              [CIN_IsDefault] = 1 AND 
              [CIN_CPN_ID] = @Item_ID  
              
       END  
	
	END 
    -- کالا
	IF @Index = 2
	BEGIN
	   
	   SET @CMD_Code = ''
	
	END

    SELECT @CPN_NationalCode AS CPN_NationalCode,   
		   @CPN_EconomicCode AS CPN_EconomicCode,
		   @CPN_MobileNumber AS CPN_MobileNumber,
		   @CMD_Code AS CMD_Code,
		   @CPN_Type AS CPN_Type    
	
END
