USE [saffron]
GO
/****** Object:  StoredProcedure [dbo].[FCT_PPT_Search]    Script Date: 04/30/2026 13:49:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FCT_PPT_Search]

    @FCT_No					_INT_ ,
    @FCT_NoExact			_BOOLEAN_ ,
    @FCT_Statuses			_BIG_ ,
    @FCT_DateFrom			_DATE_ ,
    @FCT_DateTo				_DATE_ ,
    @FCT_TimeFrom			_TIME_ ,
    @FCT_TimeTo				_TIME_ ,
    @FCT_PPT_ID				_MAXSTRING_,
    @FCT_ValueFrom			_MONEY_,
    @FCT_ValueTo			_MONEY_,
    @FCT_PaymentState		_INT_

AS
BEGIN

    SET NOCOUNT ON;

	IF @FCT_TimeTo = '23:59:00'
	   SET @FCT_TimeTo = '23:59:59'

    DECLARE @QUERY					AS nvarchar(MAX),
            @FCT_DateFrom_Int       _INT_ = dbo.SYS_CastDateToNum(@FCT_DateFrom),
	        @FCT_DateTo_Int			_INT_ = dbo.SYS_CastDateToNum(@FCT_DateTo),
	        @FCT_TimeFrom_Int		_INT_ = dbo.SYS_CastTimeToNum(@FCT_TimeFrom),
	        @FCT_TimeTo_Int			_INT_ = dbo.SYS_CastTimeToNum(@FCT_TimeTo)  
    
    SET @QUERY = '
        SELECT *,
               FCT_CST_FullName = FCT_CST_Name_1 + '' '' +ISNULL(FCT_CST_Name_2,'''') 
          FROM (     
        SELECT
            [FCT_ID] ,
            [FCT_No] ,
            [FCT_Type] ,
            FCT_Status = CASE [FCT_Status]
			  WHEN 1 THEN N''عادي''
			  WHEN 2 THEN N''حذف شده''
			  WHEN 3 THEN N''واگذار به پيک''
			  WHEN 4 THEN N''تسويه با پيک''
			  WHEN 5 THEN N''مرجوعي''
			  WHEN 6 THEN N''ضایعات''
              WHEN 7 THEN N''سفارش معلق''
              WHEN 8 THEN N''سفارش رد شده''	
            END,
            [FCT_Comment] ,
            [counts] AS [FCT_Count] ,            
            dbo.SYS_AttachCamma([FactorSum]) AS [FCT_Sum] ,
            dbo.SYS_AttachCamma([FSA_Service]) AS [FCT_ServiceValue],
            dbo.SYS_AttachCamma([FSA_Transfer]) AS [FCT_TransferValue],            
            dbo.SYS_AttachCamma([FSA_Discount]) AS [FCT_Discount],    
            [FCT_Date] ,
            [FCT_Time] ,
            (ISNULL([CST_Title],'''') + '' ''+ [CST_Name]) AS [FCT_CST_Name_1] ,
            [CST_Code] AS [FCT_CST_Code_1] ,
            [FCT_CST_Name_2] = CASE WHEN (ISNULL(FCT_CST_ID,0) > 0) AND (PPT_CPN_ID <> FCT_CST_ID) THEN [FCT_CST_Name] ELSE '''' END ,
            [FCT_CST_Code_2] = CASE WHEN (ISNULL(FCT_CST_ID,0) > 0) AND (PPT_CPN_ID <> FCT_CST_ID) THEN [FCT_CST_Code] ELSE '''' END  ,
            [FCT_IsGuest] ,
            [FCT_Style],
			[FCT_PaymentType],
			[FCT_CashPayment],
			[FCT_CardPayment]
        FROM  [View_AllFactors]
        WHERE (FCT_ByCustomer = 0)'
    
    IF @FCT_No IS NOT NULL
    BEGIN
        IF @FCT_NoExact = 0
        BEGIN
            SET @QUERY = @QUERY + ' AND (STR([FCT_No]) LIKE N''%'+LTRIM(RTRIM(STR(@FCT_No)))+'%'')'
        END
        ELSE
        BEGIN
            SET @QUERY = @QUERY + ' AND ([FCT_No] = '+LTRIM(RTRIM(STR(@FCT_No)))+')'
        END
    END
    
    IF @FCT_Statuses IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([FCT_Status] in (' + LTRIM(RTRIM(@FCT_Statuses)) + '))'
    END
    
    
    IF @FCT_DateFrom IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([FCT_Date_Int] >= '+LTRIM(RTRIM(STR(@FCT_DateFrom_Int)))+')'
    END
    
    IF @FCT_DateTo IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([FCT_Date_Int] <= '+LTRIM(RTRIM(STR(@FCT_DateTo_Int)))+')'
    END
    
    IF @FCT_TimeFrom IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([FCT_Time_Int] >= '+LTRIM(RTRIM(STR(@FCT_TimeFrom_Int)))+')'
    END
    
    IF @FCT_TimeTo IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([FCT_Time_Int] <= '+LTRIM(RTRIM(STR(@FCT_TimeTo_Int)))+')'
    END 
    
    IF @FCT_PPT_ID IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND (
                                      ([CST_ID] IN ('+LTRIM(RTRIM(@FCT_PPT_ID))+'))
                                      OR 
                                      ([FCT_CST_ID] IN ('+LTRIM(RTRIM(@FCT_PPT_ID))+'))
                                     )  
         '   
    END
    
    IF @FCT_ValueFrom IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([FactorSum] >= '+LTRIM(RTRIM(STR(@FCT_ValueFrom)))+')'
    END
    
    IF @FCT_ValueTo IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([FactorSum] <= '+LTRIM(RTRIM(STR(@FCT_ValueTo)))+')'
    END
    
    IF @FCT_PaymentState > 0
    BEGIN   
		-- نقدی
		IF @FCT_PaymentState = 1
			SET @QUERY = @QUERY + ' AND ([FCT_PaymentType] = 1)'
    
		-- کارت خوان
		IF @FCT_PaymentState = 2
			SET @QUERY = @QUERY + ' AND ([FCT_PaymentType] = 2)'    
    
		-- اعتباری
		IF @FCT_PaymentState = 3
			SET @QUERY = @QUERY + ' AND ([FCT_PaymentType] = 3)'
			
		-- ترکیبی
		IF @FCT_PaymentState = 4
			SET @QUERY = @QUERY + ' AND ([FCT_PaymentType] = 4)'	
    END        
        
    SET @QUERY = @QUERY + ' ) AZA ORDER BY [FCT_ID] DESC'    
    
--    PRINT(@QUERY)
    EXECUTE(@QUERY)

END
