USE [prince]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Orginal].[SFC_CPN_lottery]
(
	@SFC_SumFrom			[Common]._MONEY_,
	@SFC_CountFrom			[Common]._MONEY_,	
    @SFC_DateFrom		    [Common]._DATE_ ,
    @SFC_DateTo		        [Common]._DATE_, 
	@SFC_LKP_Section		[Common]._INT_,	
	@CPN_CustomerType		[Common]._INT_
)	
AS
BEGIN

    SET NOCOUNT ON;    
    
    DECLARE @QUERY              AS nvarchar(MAX),
            @SFC_DateFrom_Int   [Common]._INT_ = [Common].SYS_CastDateToNum(@SFC_DateFrom), 
            @SFC_DateTo_Int     [Common]._INT_ = [Common].SYS_CastDateToNum(@SFC_DateTo),
	        @DefaultSaloonID		[Common]._INT_,
	        @DefaultOutCustomerID	[Common]._INT_            

	IF (@SFC_LKP_Section <= 0)
	  SET @SFC_LKP_Section = NULL 

	SELECT @DefaultSaloonID = [CNL_ReferID] FROM [Common].[ConstantLookup] WHERE CNL_StaticCode = 15 AND CNL_Name = 'SaffronDefaultSaloon'
	SELECT @DefaultOutCustomerID = [CNL_ReferID] FROM [Common].[ConstantLookup] WHERE CNL_StaticCode = 15 AND CNL_Name = 'SaffronDefaultOutCustomer'

	-- Added By Ghasem Babaie - 1401.09.22 [ PRI-537 ]
	DECLARE @MiscellaneousPersonID	[Common].[_INT_] = 0  
	SELECT @MiscellaneousPersonID = [CNL_ReferID] FROM [Common].[ConstantLookup] WHERE CNL_StaticCode = 15 AND CNL_Name = 'MiscellaneousPerson' 

    SET @QUERY = ' SELECT CPN_ID,
							CPN_Title,
							CPN_Name,
							[SaloonPerson_CPN_ID],
							[SaloonPerson_CPN_Name],
							[Guest_CPN_ID],
							[Guest_CPN_Name],
							Mobile_NO,
							Phone1,
							Address1,
							COUNT(SFC_ID) AS [CountOfBuy],
							SUM(FactorSum) AS [SumOfBuy]
					FROM 
					(SELECT CPN_ID,
							CPN_Title,
							CPN_Name,
							[SaloonPerson_CPN_ID],
							[SaloonPerson_CPN_Name],
							[Guest_CPN_ID],
							[Guest_CPN_Name],							
							SFC_ID,FactorSum,
							SFC_Date,
							SFC_Time,
							Mobile_NO,
							Phone1,
							Phone2,
							Address1,
							Address2
						FROM [Orginal].[View_AllSaffronFactorsSmallest_Lottery]
						WHERE 1 = 1 AND 
								[CPN_IS_Subscriber] = 1'

    IF  @SFC_DateFrom IS NOT NULL
    BEGIN
		SET @QUERY = @QUERY + ' AND ([SFC_Date_Int] >= '+LTRIM(RTRIM(STR(@SFC_DateFrom_Int)))+')'
    END   
        
    IF  @SFC_DateTo IS NOT NULL
    BEGIN
		SET @QUERY = @QUERY + ' AND ([SFC_Date_Int] <= '+LTRIM(RTRIM(STR(@SFC_DateTo_Int)))+')'    
    END   
    
    IF  @SFC_LKP_Section IS NOT NULL
    BEGIN
		SET @QUERY = @QUERY + ' AND [SFC_LKP_Section] = ' + str(@SFC_LKP_Section)
    END       
    
    -- Changed By Ghasem Babaie - 1401.09.22 [ PRI-537 ]
	SET @QUERY = @QUERY + ' AND [CPN_ID] NOT IN (' + RTRIM(LTRIM(STR(@DefaultSaloonID))) + ',' + 
													 RTRIM(LTRIM(STR(@DefaultOutCustomerID))) + ',' + 
													 RTRIM(LTRIM(STR(@MiscellaneousPersonID))) +  ')'
	
	-- ×××××××××××××××××××××××××××××××××××××××××××××××××××××××××
	-- Added & Changed By Ghasem Babaie - 1404.10.21 [ SAC-886 ]
	-- ×××××××××××××××××××××××××××××××××××××××××××××××××××××××××
	-- @CPN_CustomerType:
	-- NULL -> هر سه
	-- 0 -> بیرون بر
	-- 1 -> میز سالن
	-- 2 -> شخص سالن
	-- ×××××××××××××××××××××××××××××××××××××××××××××××××××××××××
    IF  @CPN_CustomerType IS NOT NULL
    BEGIN
		-- برای حالت بیرون بر و میز سالن
		IF @CPN_CustomerType IN (0,1)
			SET @QUERY = @QUERY + ' AND [CPN_SubscriberType] = ' + STR(@CPN_CustomerType)
		-- برای حالت شخص سالن »» شخصی که در سالن بر سر میز نشسته است
		-- بزرگتر از صفر است [SFC_CPN_ID] است و فیلد [CPN_SubscriberType] = 1 در این حالت
		ELSE IF @CPN_CustomerType = 2
			SET @QUERY = @QUERY + ' AND [CPN_SubscriberType] = 1 AND ISNULL([SFC_CPN_ID], 0) > 0'
    END      
    -- ××××××××××××××××××××××××××××××××××××××××××××××××××××××××× 	
	        
    SET @QUERY = @QUERY +  ') AS TBL_TEMP 
							GROUP BY CPN_ID,
										CPN_Title,
										CPN_Name,
										[SaloonPerson_CPN_ID],
										[SaloonPerson_CPN_Name],
										[Guest_CPN_ID],
										[Guest_CPN_Name],										
										Mobile_NO,
										Phone1,
										Phone2,
										Address1,
										Address2 
							HAVING 1 = 1'

        
    IF  @SFC_SumFrom IS NOT NULL
    BEGIN
		SET @QUERY = @QUERY + ' AND SUM(FactorSum) >= ' + str(@SFC_SumFrom)
    END   
    
    IF  @SFC_CountFrom IS NOT NULL
    BEGIN
		SET @QUERY = @QUERY + ' AND COUNT(SFC_ID) >= ' + str(@SFC_CountFrom)
    END

    PRINT(@QUERY)
    EXECUTE(@QUERY)

END
