USE [prince]
GO
/****** Object:  StoredProcedure [Orginal].[CPN_Search_SMS]    Script Date: 11/09/2025 10:50:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		MOHAZABIE
-- Create date: 921022
-- Description:	Searching From CompanyPerson Table
-- =============================================
ALTER PROCEDURE [Orginal].[CPN_Search_SMS]

	@CPN_ID 			[Common]._INT_,
	@CPN_String			[Common]._BIG_,
	@CPN_Exact			[Common]._BOOLEAN_,
	@CPN_FirstName		[Common]._BOOLEAN_,
	@CPN_LastName		[Common]._BOOLEAN_,
	@CPN_Code			[Common]._BOOLEAN_,
	@CPN_ActivityType	[Common]._BOOLEAN_,
	@CPN_NationalCode	[Common]._BOOLEAN_,
	@CPN_EconomicCode	[Common]._BOOLEAN_,
	@CPN_CPG_ID_LIST	[Common]._BIG_ ,
	@CPN_LKP_Title      [Common]._BIG_,
	@ACN_CurrentAmount  [Common]._Money_ ,
	@CSG_ID             [Common]._BIG_,	
	@PrePhone           [Common]._BIG_, 
	@FromDate           [Common]._DATE_,
	@TODate             [Common]._DATE_,
	@CeremonyFDATE      [Common]._DATE_,
	@CeremonyTDATE      [Common]._DATE_,
	@SoftWare_Type      [Common]._INT_,
    @OrderObject		[Common]._TINY_,
	@OrderType			[Common]._TINY_,
	@UserID             [Common]._INT_,
	@MISCPerson         [Common]._INT_,
	@CPN_IS_What        [Common]._BIG_,
	@CPN_Level_ID     	[Common]._INT_

AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @CheckPermission  INT 
    SELECT @CheckPermission = SST_Value FROM Common.SystemSetting WHERE SST_Title = 'DetailPermission_Person' AND SST_UserID = 0 
	
	DECLARE @QUERY AS Nvarchar(Max) = ''
	DECLARE @ORDER AS Nvarchar(Max)
	DECLARE @ST AS Common._TINY_
	DECLARE @EN AS Common._TINY_
	
	DECLARE @DateFrom_Int INT =([Common].SYS_CastDateToNum(@FromDate))
	DECLARE @DateTo_Int   INT =([Common].SYS_CastDateToNum(@TODate))
	
	DECLARE @CeremonyFDATE_Int INT =([Common].SYS_CastDateToNum(@CeremonyFDATE))
	DECLARE @CeremonyTDATE_Int INT =([Common].SYS_CastDateToNum(@CeremonyTDATE))
	
	-- Added By Ghasem Babaie - 1401.03.26 
	DECLARE @LKP_Contact_Mobile	[Common].[_SMALL_] = '0'
	SET @LKP_Contact_Mobile = CAST([Common].[LKP_EngCaptionToID]('Contact_Mobile', N'تلفن همراه', 30) AS NVARCHAR(64))	
	
	IF @CPN_Exact = 0
	BEGIN
		SET @ST = 'LIKE N''%'
		SET @EN = '%'''
	END
	ELSE
	BEGIN
		SET @ST = '= N'''
		SET @EN = ''''
	END
	
	
	IF (@OrderObject = 'FName')
	   SET @ORDER = ' ORDER BY [CPN_FirstName] ' 
	ELSE
	IF (@OrderObject = 'LName')
	   SET @ORDER = ' ORDER BY [CPN_LastName] '
	ELSE
	IF (@OrderObject = 'Code')
	   SET @ORDER = ' ORDER BY [CPN_Code] '
	ELSE
	   SET @ORDER = ' ORDER BY [CPN_ID] '

	SET @ORDER = @ORDER + @OrderType    
	
	IF @MISCPerson in (0,1)
	Begin /*IF @MISCPerson in (0,1)*/		
				IF @CPN_ID IS NOT NULL
				BEGIN
				  IF @SoftWare_Type IN (1,3)
				  BEGIN /*@SoftWare_Type IN (1,3)*/
					SET @QUERY = '
					 WITH Factor_CTE
					 AS 
					 (	
						SELECT FCT_ID AS LastFCT_ID, 
							   CAST(FCT_Number AS NVARCHAR(32)) AS LastFactorNumber,
							   FCT_Date AS LastFactorDate,
							   --POP_Amount AS LastFactorPrice,
							   FCT_SUM  AS LastFactorPrice,
							   '+LTRIM(RTRIM(STR(@CPN_ID)))+' AS CPN_ID		
						  FROM [Orginal].[PaymentOperation]
	   					  JOIN [Orginal].[Factor] ON (POP_TBL_Handled = 24) AND (POP_HandledID = FCT_ID)
						 WHERE (FCT_ID = (SELECT MAX(PPT_FCT_ID) 
											FROM [Orginal].[Participant] 
										   WHERE (PPT_IsDeleted = 0) AND 
												 (PPT_Role = 1) AND 
												 ([PPT_CPN_ID] = '+LTRIM(RTRIM(STR(@CPN_ID)))+') )) AND
							   (POP_Status <> 3) AND 
							   (POP_Type IN (10,11))AND
							   (POP_From IS NOT NULL) AND
							   (POP_To IS NOT NULL) 
					  ) 
					  
					  , DiscountCTE   as         
					  ( 
					  SELECT  FCT_ID,SUM([POP_Amount]) AS Pop_Discount
					  FROM [Orginal].[PaymentOperation] 
							RIGHT JOIN [Orginal].[Factor]  ON [FCT_ID] = [POP_HandledID]  
					  WHERE (POP_Status <> 3) AND
							((FCT_IsDeleted IS NULL ) OR (FCT_IsDeleted = 0)) AND
		    				(POP_TBL_Handled = 24) AND		
							(((FCT_Type IN (1,6))  AND (POP_Type IN (13))) OR
							 ((FCT_Type = 3)  AND (POP_Type IN (15)))		    
							) and 
 							(FCT_id = (select fct_id from Factor_CTE)) 
                      Group By FCT_ID
						
			 		 )
								  
					  
					  '
				   END/*@SoftWare_Type IN (1,3)*/
				   ELSE
				   IF @SoftWare_Type = 2
				   BEGIN/*IF @SoftWare_Type = 2*/
					SET @QUERY = ' 
					WITH RealEstate_CTE
					 AS 
					 (	
					SELECT [1] AS RealEstateCode, [2] AS RequestCode
					  FROM(
					SELECT ISNULL(RES_Code,'''') AS Code , 1 AS Flag
					  FROM Common.RealEstate
					 WHERE RES_ID = (SELECT MAX(RES_ID) 
									   FROM Common.RealEstate 
									  WHERE (RES_CPN_ID = '+LTRIM(RTRIM(STR(@CPN_ID)))+') AND 
											(RES_IsDeleted = 0)) 		                        
					UNION ALL
					SELECT ISNULL(NRT_Code,'''') AS Code , 2 AS Flag
					  FROM Common.NewRequest
					 WHERE NRT_ID = (SELECT MAX(NRT_ID) 
									   FROM Common.NewRequest
									  WHERE (NRT_Applicant_CPN_ID = '+LTRIM(RTRIM(STR(@CPN_ID)))+') AND 
											(NRT_IsDeleted = 0)) 
					   ) AS T
					   PIVOT
					   (
						MIN([Code]) 
						FOR [Flag] IN ([1],[2])
					   ) AS P
					  ) 
					'	   
				   
				   END/*IF @SoftWare_Type = 2*/
				   ELSE
				   IF @SoftWare_Type = 5
				   BEGIN/*IF @SoftWare_Type = 5*/
					SET @QUERY = ' 
					WITH CarData_CTE
					 AS 
					 (	
					SELECT [1] AS CAR_Code, [2] AS RequestCode
					  FROM(
					SELECT ISNULL(CAR_Code,'''') AS Code , 1 AS Flag
					  FROM Common.CarData
					 WHERE CAR_ID = (SELECT MAX(CAR_ID) 
									   FROM Common.CarData 
									  WHERE (CAR_CPN_ID = '+LTRIM(RTRIM(STR(@CPN_ID)))+') AND 
											(CAR_IsDeleted = 0)) 		                        
					UNION ALL
					SELECT ISNULL(CNR_Code,'''') AS Code , 2 AS Flag
					  FROM Common.CarNewRequest
					 WHERE CNR_ID = (SELECT MAX(CNR_ID) 
									   FROM Common.CarNewRequest
									  WHERE (CNR_CPN_ID = '+LTRIM(RTRIM(STR(@CPN_ID)))+') AND 
											(CNR_IsDeleted = 0)) 
					   ) AS T
					   PIVOT
					   (
						MIN([Code]) 
						FOR [Flag] IN ([1],[2])
					   ) AS P
					  ) 
					'	   
				   
				   END/*IF @SoftWare_Type = 5*/
				   ELSE				   
				   IF @SoftWare_Type = 4
				   BEGIN/*@SoftWare_Type = 4*/
					SET @QUERY = '
					 WITH GoldFactor_CTE
					 AS 
					 (	
						SELECT GFC_Number AS LastFactorNumber,
							   GFC_Date AS LastFactorDate,
							   [10] AS LastFactorPrice,
							   [47] AS LastFactorGold,
							   '+LTRIM(RTRIM(STR(@CPN_ID)))+' AS CPN_ID		
						  FROM (	   
						SELECT GFC_Number,GFC_Date,POP_Amount,POP_Type   
						  FROM [Orginal].[PaymentOperation]
	   					  JOIN [Orginal].[GoldFactor] ON (POP_TBL_Handled = 215) AND (POP_HandledID = GFC_ID)
						 WHERE (GFC_ID = (SELECT MAX(PPT_FCT_ID) 
											FROM [Orginal].[Participant] 
										   WHERE (PPT_IsDeleted = 0) AND 
												 (PPT_Role = 5) AND 
												 ([PPT_CPN_ID] = '+LTRIM(RTRIM(STR(@CPN_ID)))+') )) AND
							   (POP_Status <> 3) AND 
							   (POP_Type IN (10,11,47,48))AND
							   (POP_From IS NOT NULL) AND
							   (POP_To IS NOT NULL) '
					IF @FromDate IS NOT NULL
	   				   SET @QUERY = @QUERY + ' AND (GFC_Date_Int>= '+LTRIM(RTRIM(STR(@DateFrom_Int)))+')'
				   	 
					IF @TODate IS NOT NULL
	   				   SET @QUERY = @QUERY + ' AND  (GFC_Date_Int<= '+LTRIM(RTRIM(STR(@DateTo_Int)))+')'					   
					
					SET @QUERY = @QUERY + '		   
							 ) AS T
						 PIVOT
							(
							SUM(POP_Amount)
							FOR POP_Type IN ([10], [47])
							) AS PivotTable
					  ) '   
				   
				   END /*@SoftWare_Type = 4*/
				END --IF @CPN_ID IS NOT NULL
				ELSE
				BEGIN/*555555555555555555555*/
				  IF (@SoftWare_Type IN (1,3)) AND ((@FromDate IS NOT NULL) OR (@TODate IS NOT NULL) OR
													(@CeremonyFDATE IS NOT NULL) OR (@CeremonyTDATE IS NOT NULL))
				  BEGIN/*111*/
					SET @QUERY = '
					 WITH Factor_CTE
					 AS 
					 (	
						SELECT PPT_CPN_ID		
						  FROM [Orginal].[Factor] 
						  JOIN [Orginal].[Participant] ON (PPT_FCT_ID = FCT_ID)
						 WHERE (PPT_IsDeleted = 0) AND 
							   (PPT_Role = 1) AND 
							   (FCT_IsDeleted = 0)'
					IF @FromDate IS NOT NULL
	   					 SET @QUERY = @QUERY + ' AND  (FCT_Date_Int >= '+LTRIM(RTRIM(STR(@DateFrom_Int)))+')'
					   	 
					 IF @TODate IS NOT NULL
	   					 SET @QUERY = @QUERY + ' AND  (FCT_Date_Int <= '+LTRIM(RTRIM(STR(@DateTo_Int)))+')'			   
							        
					 SET @QUERY = @QUERY + ') '	 
				   END /*111*/

				   IF (@SoftWare_Type IN (3))AND ((@CeremonyFDATE IS NOT NULL) OR (@CeremonyTDATE IS NOT NULL))
				   BEGIN/*222*/
							SET @QUERY = @QUERY + '
							 , Ceremony_CTE
							  AS
							  ( 
							   SELECT CCC_CPN_ID AS Ceremony_CPN_ID
								 FROM [Common].CeremonyContract  WHERE (ISNULL(CCC_IsDeleted,0)=0) '
							IF @CeremonyFDATE IS NOT NULL
								SET @QUERY = @QUERY + '  AND  (CCC_Date_Int>='+LTRIM(RTRIM(STR(@CeremonyFDATE_Int)))+')'
							IF @CeremonyTDATE IS NOT NULL
								SET @QUERY = @QUERY + '  AND  (CCC_Date_Int<='+LTRIM(RTRIM(STR(@CeremonyTDATE_Int)))+')'	         
						   
							SET @QUERY = @QUERY + '      
							   UNION ALL
							   SELECT CRV_FirstCPN_ID 
								 FROM Common.CeremonyReserve WHERE (ISNULL(CRV_IsDeleted,0)=0) '
					             
							IF @CeremonyFDATE IS NOT NULL
								SET @QUERY = @QUERY + '  AND (CRV_DefiniteDate_Int>='+LTRIM(RTRIM(STR(@CeremonyFDATE_Int)))+')'
							IF @CeremonyTDATE IS NOT NULL
								SET @QUERY = @QUERY + '  AND (CRV_DefiniteDate_Int<='+LTRIM(RTRIM(STR(@CeremonyTDATE_Int)))+')'               
					        
							SET @QUERY = @QUERY + '     
							   UNION ALL
							   SELECT CRV_SecondCPN_ID 
								 FROM Common.CeremonyReserve WHERE (ISNULL(CRV_IsDeleted,0)=0) '

							IF @CeremonyFDATE IS NOT NULL
								SET @QUERY = @QUERY + '  AND (CRV_DefiniteDate_Int>='+LTRIM(RTRIM(STR(@CeremonyFDATE_Int)))+')'
							IF @CeremonyTDATE IS NOT NULL
								SET @QUERY = @QUERY + '  AND (CRV_DefiniteDate_Int<='+LTRIM(RTRIM(STR(@CeremonyTDATE_Int)))+')'    
					             
							 SET @QUERY = @QUERY + ') '	             
				   END/*222*/
				  ELSE
				  IF @SoftWare_Type = 4
					  BEGIN/*333*/
						SET @QUERY = '
						 WITH GoldFactor_CTE
						 AS 
						 (	
							SELECT PPT_CPN_ID  
	   						  FROM [Orginal].[GoldFactor] 
							  JOIN [Orginal].[Participant] ON (PPT_FCT_ID = GFC_ID)
							 WHERE (PPT_IsDeleted = 0) AND 
								   (PPT_Role = 5) AND 
								   (GFC_IsDeleted = 0) '
						IF @FromDate IS NOT NULL
	   					   SET @QUERY = @QUERY + ' AND (GFC_Date_Int>= '+LTRIM(RTRIM(STR(@DateFrom_Int)))+')'
					   	 
						IF @TODate IS NOT NULL
	   					   SET @QUERY = @QUERY + ' AND  (GFC_Date_Int<= '+LTRIM(RTRIM(STR(@DateTo_Int)))+')'					   
						
						SET @QUERY = @QUERY + '	) '   
				   
				   END/*333*/

				END /*555555555555555555555*/
				   
				
				SET @QUERY = @QUERY + '
				SELECT 
					 [CPN_ID] ,
					 [CPN_LKP_Title] ,
					 (SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE ([LKP_ID] = [CPN_LKP_Title]))AS [LKP_Caption] ,
					 [CPN_FirstName] ,
					 [CPN_LastName] ,
					 [CPN_Code] ,
					 [CPN_ActivityType] ,
					 [CPN_EconomicCode] ,
					 [CPN_Level_ID],
					 ISNULL((SELECT [RFO_LevelCaption] FROM [Common].[CustomersRatingFormula] WHERE [RFO_ID] = [CPN_Level_ID]), '''') AS [CPN_Level_Caption],
					 ISNULL((SELECT [RFO_LevelColor] FROM [Common].[CustomersRatingFormula] WHERE [RFO_ID] = [CPN_Level_ID]), '''') AS [CPN_Level_Color],
					 [CPN_Description] ,
					 [Common].[SYS_AttachCamma]([CPN_MaxCredit]) AS [CPN_MaxCredit] ,
					 [CPN_MaxCredit] AS [CPN_MaxCredit_Pure] ,
					 [CPN_Discount] ,						
					 [ACN_ID],
					 [ACN_CurrentAmount],
					 (SELECT ISNULL(ACN_CurrentAmount,0) from Common.Account where (ACN_TBL_Holder = 199) AND (ACN_HolderID =CPN_ID)) AS GoldCurrentAmount,
					 [CPN_IS_Credit],
					 [CPN_NationalCode],
					 [CPN_Type],
					 [CPN_Percent_Marketer],
					 [CPN_Percent_ExpertSales],
					 (SELECT MIN(CIN_Value) FROM [Common].ContactInformation WHERE (CIN_IsDefault = 1) AND (CPN_ID = CIN_CPN_ID) AND (CIN_LKP_Type = ' + @LKP_Contact_Mobile + ')) AS Phone '  
				    
					 IF @CPN_ID IS NOT NULL
					 BEGIN/*123*/
						IF @SoftWare_Type IN (1,3)	
						   SET @QUERY = @QUERY + ', 
							  ( SELECT LastFactorNumber FROM Factor_CTE ) AS LastFactorNumber,
							  ( SELECT LastFactorDate FROM Factor_CTE ) AS LastFactorDate ,                                               
							 ( SELECT isnull(LastFactorPrice,0) - isnull(Pop_Discount,0) FROM Factor_CTE left join DiscountCTE  on LastFCT_ID = fct_id	 ) AS LastFactorPrice    '
			         
						IF @SoftWare_Type IN (2)	
						   SET @QUERY = @QUERY + ', 
							  ( SELECT RealEstateCode FROM RealEstate_CTE ) AS RealEstateCode,
							  ( SELECT RequestCode FROM RealEstate_CTE ) AS RequestCode '
			         
						IF @SoftWare_Type IN (4)	
						   SET @QUERY = @QUERY + ',
							  ( SELECT LastFactorNumber FROM GoldFactor_CTE ) AS LastFactorNumber,
							  ( SELECT LastFactorDate FROM GoldFactor_CTE ) AS LastFactorDate ,                                               
							  ( SELECT LastFactorPrice FROM GoldFactor_CTE ) AS LastFactorPrice ,
							  ( SELECT LastFactorGold FROM GoldFactor_CTE ) AS LastFactorGold '
							  
						IF @SoftWare_Type IN (5)	
						   SET @QUERY = @QUERY + ', 
							  ( SELECT Car_Code FROM CarData_CTE ) AS CarCode,
							  ( SELECT RequestCode FROM CarData_CTE ) AS RequestCode '							  
							  
					 END/*123*/                          
					
					 SET @QUERY = @QUERY + '  
						 FROM [Common].[CompanyPerson] 
						 JOIN [Common].Account ON CPN_ID = ACN_HolderID And ACN_TBL_Holder = 15 
						WHERE ([CPN_IsDisabled] = 0) '
					    
					 IF @CPN_ID IS NULL   
					 BEGIN/*124*/
						IF (@SoftWare_Type = 1) AND ((@FromDate IS NOT NULL) OR (@TODate IS NOT NULL))	
						   SET @QUERY = @QUERY + ' AND (CPN_ID IN (SELECT PPT_CPN_ID FROM Factor_CTE )) '
						IF (@SoftWare_Type = 3) AND ((@FromDate IS NOT NULL) OR (@TODate IS NOT NULL) OR
													(@CeremonyFDATE IS NOT NULL) OR (@CeremonyTDATE IS NOT NULL))	
						   SET @QUERY = @QUERY + ' AND (CPN_ID IN (SELECT PPT_CPN_ID FROM Factor_CTE 
																   UNION ALL
																   SELECT Ceremony_CPN_ID FROM Ceremony_CTE )) '
						IF (@SoftWare_Type = 4)AND((@FromDate IS NOT NULL) OR (@TODate IS NOT NULL))	
						   SET @QUERY = @QUERY + ' AND (CPN_ID IN (SELECT PPT_CPN_ID FROM GoldFactor_CTE )) '
					 END/*124*/
					 ELSE
					 BEGIN/*125*/
						  SET @QUERY = @QUERY + ' AND ([CPN_ID] = '+LTRIM(RTRIM(STR(@CPN_ID)))+')'
					 END  /*125*/
					  
					 IF @CPN_LKP_Title IS NOT NULL
						 SET @QUERY = @QUERY + ' AND  CPN_LKP_Title =' + @CPN_LKP_Title

					 IF @ACN_CurrentAmount IS NOT NULL
						 SET @QUERY = @QUERY + ' AND  ACN_CurrentAmount >=' + LTRIM(RTRIM(cast(@ACN_CurrentAmount as nvarchar(50))))
					
					 IF @CSG_ID IS NOT NULL	 
						 SET @QUERY = @QUERY + ' AND  CPN_ID IN ( SELECT CSG_CPN_ID FROM [Common].CompanyPersonMessageGroup WHERE CSG_LKP_ID IN ('+@CSG_ID+') )' 		 
			         
					 IF (@UserID <> 1) AND (@CheckPermission = 1) AND ([Common].[PER_CheckSelectAll](@UserID, 2000000) = 0)  
						 SET @Query = @Query +' AND [CPN_ID] IN (SELECT [PER_ObjectID] from [Common].View_AllUserPermission 
											WHERE UAC_ID = '+ RTRIM(LTRIM(STR(@UserID))) + ' AND PER_Type = 6) '			  
							  
					 IF (@CPN_FirstName = 1) OR (@CPN_LastName = 1) OR (@CPN_Code = 1) OR (@CPN_ActivityType = 1) OR (@CPN_ID = 1)OR
						(@CPN_NationalCode = 1) OR (@CPN_EconomicCode = 1) 
						SET @QUERY = @QUERY + 'AND ((1!=1) ' 
				
 					 IF @CPN_FirstName = 1
						SET @QUERY = @QUERY + ' OR ([CPN_FirstName] '+LTRIM(RTRIM(@ST))+LTRIM(RTRIM(@CPN_String))+LTRIM(RTRIM(@EN))+')'
					
					 IF @CPN_LastName = 1
						SET @QUERY = @QUERY + ' OR ([CPN_LastName] '+LTRIM(RTRIM(@ST))+LTRIM(RTRIM(@CPN_String))+LTRIM(RTRIM(@EN))+')'
							
					 IF @CPN_Code = 1
						SET @QUERY = @QUERY + ' OR ([CPN_Code] '+LTRIM(RTRIM(@ST))+LTRIM(RTRIM(@CPN_String))+LTRIM(RTRIM(@EN))+')'
							
					 IF @CPN_ActivityType = 1
						SET @QUERY = @QUERY + ' OR ([CPN_ActivityType] '+LTRIM(RTRIM(@ST))+LTRIM(RTRIM(@CPN_String))+LTRIM(RTRIM(@EN))+')'
					
					 IF @CPN_NationalCode = 1
					   SET @QUERY = @QUERY + ' OR ([CPN_NationalCode] '+LTRIM(RTRIM(@ST))+LTRIM(RTRIM(@CPN_String))+LTRIM(RTRIM(@EN))+')'
				    
					 IF @CPN_EconomicCode =1
					   SET @QUERY = @QUERY + ' OR ([CPN_EconomicCode] '+LTRIM(RTRIM(@ST))+LTRIM(RTRIM(@CPN_String))+LTRIM(RTRIM(@EN))+')'                       

					IF (@CPN_FirstName = 1) OR (@CPN_LastName = 1) OR (@CPN_Code = 1) OR (@CPN_ActivityType = 1) OR (@CPN_ID = 1)OR
					   (@CPN_NationalCode = 1) OR (@CPN_EconomicCode = 1)  
					SET @QUERY = @QUERY + ')'
				
    				IF @CPN_CPG_ID_LIST IS NOT NULL
					   SET @QUERY = @QUERY + ' AND ( SELECT COUNT(1) FROM [Common].[CompanyPersonGroupMembership] WHERE [CPN_ID] = [PGM_CPN_ID] AND  
													(PGM_CPG_ID IN (' + LTRIM(RTRIM(@CPN_CPG_ID_LIST)) + '))) > 0'

					IF @PrePhone is not null
					Begin
					 SET @QUERY = @QUERY + ' 
					 AND (CPN_ID IN (SELECT CIN_CPN_ID FROM [Common].ContactInformation WHERE (CIN_LKP_Type IN (' + @LKP_Contact_Mobile + ')) AND (CIN_Value  LIKE '''+@PrePhone+'%'')))'
					End
					
		
					IF (@CPN_IS_What  IS NOT NULL)
						SET @QUERY = @QUERY + @CPN_IS_What
		
					IF @CPN_Level_ID IS NOT NULL
     				   SET @QUERY = @QUERY + ' AND (CPN_Level_ID = ' + STR(@CPN_Level_ID) + ') '
		
END /*IF @MISCPerson in (0,1)*/
	
		
		IF @SoftWare_Type IN (1,3)	
		Begin/*1*/
		
			IF @MISCPerson = 0 
			   SET @QUERY = @QUERY + ' union all'
			   
			IF @MISCPerson in (0,2)
			Begin
			 SET @QUERY = @QUERY + '
			 select distinct		 	 
			 0 as [CPN_ID] ,
			 '''' as [CPN_LKP_Title] ,
			 '''' AS [LKP_Caption] ,
			 '''' as [CPN_FirstName] ,
			 FCT_MiscPerson as [CPN_LastName] ,
			 '''' as [CPN_Code] ,
			 '''' as [CPN_ActivityType] ,
			 '''' as [CPN_EconomicCode] ,
			 0 AS [CPN_Level_ID],
			 '''' AS [CPN_Level_Caption],
			 '''' AS [CPN_Level_Color],
			 '''' as [CPN_Description] ,
			 ''0'' AS [CPN_MaxCredit] ,
			 0 AS [CPN_MaxCredit_Pure] ,
			 0 as [CPN_Discount] ,						
			 0 as [ACN_ID],
			 0 as [ACN_CurrentAmount],
			 0 AS GoldCurrentAmount,
			 0 as [CPN_IS_Credit],
			 '''' as [CPN_NationalCode],
			 '''' as [CPN_Type],
			 0 AS [CPN_Percent_Marketer],
			 0 AS [CPN_Percent_ExpertSales]
			 '  
			 IF @CPN_ID IS NOT NULL
			 BEGIN/*223*/
				IF @SoftWare_Type IN (1,3)	
				   SET @QUERY = @QUERY + ',
					   FCT_MiscPersonPhoneNumber AS Phone, 
					   '''' as  LastFactorNumber,
					   ''''  AS LastFactorDate ,                                               
					   ''''  AS LastFactorPrice '
	         
				IF @SoftWare_Type IN (2)	
				   SET @QUERY = @QUERY + ', 
					   ''''  AS Phone,
					   ''''  AS RealEstateCode,
					   ''''  AS RequestCode '
	         
				IF @SoftWare_Type IN (4)	
				   SET @QUERY = @QUERY + ',
					   ''''  AS Phone, 
					   ''''  AS LastFactorNumber,
					   ''''  AS LastFactorDate ,                                               
					   ''''  AS LastFactorPrice ,
					   ''''  AS LastFactorGold '
					   
				IF @SoftWare_Type IN (5)	
				   SET @QUERY = @QUERY + ', 
					   ''''  AS Phone,
					   ''''  AS CarCode,
					   ''''  AS RequestCode '					   
					   
			 END /*223*/
			 ELSE
			 BEGIN/*223*/
				IF @SoftWare_Type IN (1,3)	
				   SET @QUERY = @QUERY + ',
					   FCT_MiscPersonPhoneNumber AS Phone  '
	         
				IF @SoftWare_Type IN (2)	
				   SET @QUERY = @QUERY + ', 
					   ''''  AS Phone '
	         
				IF @SoftWare_Type IN (4)	
				   SET @QUERY = @QUERY + ',
					   ''''  AS Phone  '
			 END /*223*/			 
			 
	        
			  IF @SoftWare_Type IN (1,3)   	
           		Begin
					 SET @QUERY = @QUERY + 'from [Orginal].factor	 where ((LTRIM(RTRIM(FCT_MiscPerson))<>'''' )  or (LTRIM(RTRIM(FCT_MiscPersonPhoneNumber))<>'''' ) )'
					 
 					 IF (@CPN_FirstName = 1)OR(@CPN_LastName = 1)
						SET @QUERY = @QUERY + ' AND ([FCT_MiscPerson] '+LTRIM(RTRIM(@ST))+LTRIM(RTRIM(@CPN_String))+LTRIM(RTRIM(@EN))+')'
					 
					IF @FromDate IS NOT NULL
	   					 SET @QUERY = @QUERY + ' AND  (FCT_Date_Int >= '+LTRIM(RTRIM(STR(@DateFrom_Int)))+')'
					   	 
					 IF @TODate IS NOT NULL
	   					 SET @QUERY = @QUERY + ' AND  (FCT_Date_Int <= '+LTRIM(RTRIM(STR(@DateTo_Int)))+')'			 
				
					IF @PrePhone IS NOT NULL
					Begin
					 SET @QUERY = @QUERY + ' 
					 AND (FCT_MiscPersonPhoneNumber  LIKE '''+@PrePhone+'%'')'
					End

					IF @CPN_ID IS NOT NULL 
					 SET @QUERY = @QUERY + ' AND (FCT_ID IN ( SELECT LastFCT_ID FROM Factor_CTE ))'
			  END
				
		END/*1*/
	END
		
		  
	SET @QUERY = @QUERY + @ORDER
	
	
	--print @QUERY
	EXECUTE(@QUERY);
	

END
