USE [prince]
GO
/****** Object:  StoredProcedure [Orginal].[CRT_CardReaderTransactionReport]    Script Date: 12/10/2025 14:56:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ZAREIE
-- Create date: 1392-08-12
-- Description:	
-- =============================================
ALTER PROCEDURE [Orginal].[CRT_CardReaderTransactionReport]
       @CRT_SerialTransaction    COMMON._SHORT_,
       @CRT_PAN					 COMMON._SHORT_,
       @CRT_TraceNumber			 COMMON._SHORT_,
       @CRT_TransactionFullOK    COMMON._TINYINT_,
       @CRT_AmountFrom           COMMON._MONEY_,
       @CRT_AmountTo			 COMMON._MONEY_,
       @CRT_SystemDateFrom       COMMON._DATE_,
       @CRT_SystemDateTo         COMMON._DATE_,
       @OperationType			 Common._SMALL_,
	   @FactorType				 Common._SMALL_,
	   @FactorNumber			 Common._SMALL_,
	   @FactorNumber_Exact		 Common._BOOLEAN_,
       @OperationUserID			 Common._INT_,
	   @CRT_SystemTimeFrom		 Common._Time_ ,
	   @CRT_SystemTimeTo		 Common._Time_       
 
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;       
       
    IF @CRT_SystemTimeTo = '23:59:00'
	   SET @CRT_SystemTimeTo = '23:59:59'       
       
	DECLARE @CRT_SystemDateFrom_Int  Common._INT_ = Common.SYS_CastDateToNum(@CRT_SystemDateFrom),
	        @CRT_SystemDateTo_Int    Common._INT_ = Common.SYS_CastDateToNum(@CRT_SystemDateTo)
	        

    DECLARE @SoftwareType INT
    SELECT @SoftwareType = SST_Value FROM Common.[SystemSetting] WHERE SST_Title = 'SoftwareType' AND SST_UserID = 0 
 
    IF @SoftwareType in (6,7,8)
       SELECT @SoftwareType = JOK_ParentSoftwareType FROM Common.JobKind WHERE (JOK_SoftwareType = @SoftwareType) AND (JOK_IsActive = 1)
 
    IF @OperationType IS NOT NULL
    BEGIN
		
		DECLARE @OperationType_New      Common._BIG_
		
		SELECT @OperationType_New = COALESCE(@OperationType_New + ',' + ITEMS , ITEMS)
		  FROM (
		       SELECT ITEMS = CASE WHEN ITEMS IN ('24','45','215','331') THEN '0' 
		                           WHEN ITEMS IN ('138') THEN '138,139,140' -- معامله
		                           WHEN ITEMS IN ('277') THEN '277,278,279' -- معامله خودرو
		                           ELSE ITEMS END
		         FROM Common.SYS_Split_STR (@OperationType ,',' )
		       ) AS T		
		
		SET @OperationType = @OperationType_New		
		
	END

    DECLARE @Query   NVARCHAR(MAX)=''
    
    SET @Query = '
    SELECT 
           CardNumber = CASE CRT_TransactionFullOK 
							   WHEN 0 THEN 
									CASE WHEN (LEN(LTRIM(RTRIM(CRT_PAN))) >= 4) AND (LEN(LTRIM(RTRIM(CRT_BIN))) >= 6) THEN
										CAST(CRT_PAN+''******''+CRT_BIN AS NVARCHAR(20))
									ELSE
										''''
									END
							   WHEN 1 THEN CAST(CRT_PAN+''******''+CRT_BIN AS NVARCHAR(20))
						END,
           CRT_TransactionFullOK = CASE CRT_TransactionFullOK 
                               WHEN 0 THEN N''بروز خطا''
                               WHEN 1 THEN N''موفقیت آمیز''
                        END,
           CRT_ID,
           CRT_POP_ID,
           CRT_User_ID,
           CRT_BankType,
           CRT_Amount,
           CRT_SerialTransaction,
           CRT_TraceNumber,
           CRT_TransactionDate,
           CRT_TransactionTime,
           CRT_ReasonCode,
           CRT_ReasonText,
           CRT_ReturnCode,
           CRT_ReturnText,
           CRT_ReturnPersianText,
           CRT_TerminalNo,
           CRT_PAN,
           CRT_BIN,
           CRT_AccountNo,
           CRT_SystemDate,
           CRT_SystemTime,
           CRT_Type,
           CRT_TypeDesc = CASE CRT_Type 
                               WHEN 0 THEN N''آسان پرداخت''
                               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''سداد بانک ملی''
                               WHEN 9 THEN N''اقتصاد نوین''
                               WHEN 1000 THEN N''اندروید - ناشناس''
                               WHEN 1001 THEN N''اندروید - فن آوا کارت''
                               WHEN 1002 THEN N''اندروید - ایران کیش''
                               WHEN 1003 THEN N''اندروید - تجارت الکترونیک پارسیان''
                               WHEN 1004 THEN N''اندروید - ناواکو''
                               WHEN 1005 THEN N''اندروید - پرداخت الکترونیک سامان''
                               WHEN 1006 THEN N''اندروید - پرداخت الکترونیک سپهر'' 
                               WHEN 1007 THEN N''اندروید - پرداخت پارسیان - مهرانا'' 
                               WHEN 1100 THEN N''اندروید پوز SZZT - PMD''
                               WHEN 2000 THEN N''اندروید پرداخت نوین'' 
                               WHEN 3000 THEN N''اندروید پوز توتان''
                               WHEN 4000 THEN N''اندروید پوز پارسیان - P1000''
                               WHEN 4100 THEN N''اندروید پوز به پرداخت ملت - Tosan Techno A910'' -- 1403-08-03 (Added By Samadi)
                               WHEN 4200 THEN N''اندروید پوز به پرداخت ملت - AMP8200 Pro-X'' -- 1403-08-03 (Added By Samadi)
                               WHEN 4300 THEN N''اندروید پوز پرداخت نوین - PNA'' -- 1403-08-03 (Added By Samadi)
                               WHEN 4400 THEN N''اندروید پوز به پرداخت ملت - AMP8200 Pro-X'' -- 1403-08-03 (Added By Samadi)
                               WHEN 4500 THEN N''اندروید پوز پرداخت الکترونیک سامان - Newland N910'' -- 1403-08-03 (Added By Samadi)
                               WHEN 4600 THEN N''اندروید پوز مایا - SP850''  -- نوع درگاه مشخص نیست 03-08-1403 (Added By Samadi)
                               WHEN 4700 THEN N''اندروید پوز سداد - Tosan Techno A910'' -- 1403-08-03 (Added By Samadi)
                               WHEN 4800 THEN N''اندروید پوز سپه - OMID PAY  Urovo i9100'' -- 1404-07-10 (Added By Tavakoli)
                               WHEN 10000 THEN N''آی دی پی - درگاه اینترنتی''   
                               WHEN 10001 THEN N''پی پینگ - درگاه اینترنتی''  
           END,           
           CASE POP_TBL_Handled
			  WHEN 45 THEN N''عمليات انتقال''
			  WHEN 46 THEN N''افزايش/کاهش سرمايه''
			  WHEN 47 THEN N''هزينه/درآمد''
			  WHEN 60 THEN N''تسويه حساب''
			  WHEN 61 THEN N''عمليات چک''
			  WHEN 24 THEN
			  CASE (SELECT FCT_Type 
					FROM [Orginal].[Factor] 
					WHERE [FCT_ID] = POP_HandledID ) 
				  WHEN 0 THEN N''خريد''
				  WHEN 1 THEN N''فروش''
				  WHEN 2 THEN N''برگشت از خريد''
				  WHEN 3 THEN N''برگشت از فروش''
				  WHEN 6 THEN N''فروش امانی''
				  WHEN 7 THEN N''خرید امانی''
			  END
			  WHEN 139 THEN N''معامله''
			  WHEN 140 THEN N''معامله''
			  WHEN 154 THEN N''کارت خرید''
			  WHEN 179 THEN N''قرارداد/سفارش''
			  WHEN 215 THEN
			  CASE (SELECT GFC_Type 
					FROM [Orginal].[GoldFactor] 
					WHERE [GFC_ID] = POP_HandledID ) 
				  WHEN 1 THEN N''خريد''
				  WHEN 2 THEN N''فروش''
			  END	
			  WHEN 224 THEN N''تسویه حساب طلایی - پولی کردن''
			  WHEN 225 THEN N''افزایش/کاهش سرمایه طلایی - پولی کردن''		  
			  WHEN 49 THEN CASE WHEN POP_Type = 27 THEN N''ثبت موجودي ابتدای دوره'' ELSE N'''' END
			  WHEN 278 THEN N''معامله خودرو''
			  WHEN 279 THEN N''معامله خودرو''
			  WHEN 331 THEN N''فروش''
			  WHEN 382 THEN N''قرارداد اجاره''
			  WHEN 453 THEN 
			  CASE (SELECT GCT_FinantialType 
					FROM [Orginal].[GoldCreditTransaction] 
					WHERE [GCT_ID] = POP_HandledID ) 
				  WHEN 2 THEN N''خریداعتباري طلا''
				  WHEN 1 THEN N''فروش اعتباري طلا''
			  END
			  WHEN 477 THEN N''تسویه حساب سکه/ارز - پولی کردن''
			  WHEN 478 THEN N''افزایش/کاهش سرمایه سکه/ارز - پولی کردن''	
			  ELSE
			     N''''			  
			END AS [Regard],'
			
		IF (@SoftwareType IN (1,2,3,5,99))
			SET @Query = @Query + '  ISNULL(CAST(FCT_Number AS NVARCHAR(32)) , '''') AS FCT_Number ,
			     (CAST ([FCT_Number] AS DECIMAL(38,0))) AS [FCT_Number_Order] , '
		ELSE IF (@SoftwareType IN (9))
			SET @Query = @Query + '  ISNULL(CAST(SFC_No AS NVARCHAR(32)) , '''') AS FCT_Number ,
			(CAST (SFC_No AS DECIMAL(38,0))) AS [FCT_Number_Order] , '	
		ELSE
			SET @Query = @Query + '  ISNULL(GFC_Number , '''') AS FCT_Number ,
			(CAST (GFC_Number AS DECIMAL(38,0))) AS [FCT_Number_Order] ,'
			
		 SET @Query = @Query + '
			ISNULL([POP_ID],0) as POP_ID,
			ISNULL([POP_TBL_Handled],0) as POP_TBL_Handled,
			ISNULL([POP_HandledID],0) as POP_HandledID,
			ROW_NUMBER() OVER (ORDER BY [CRT_ID]) AS RandomID      
	FROM [Orginal].CardReaderTransaction 
	LEFT JOIN [Orginal].PaymentOperation ON (CRT_POP_ID = POP_ID) '
	
	IF (@SoftwareType IN (1,2,3,5,99))
	   SET @Query = @Query + '
	       LEFT JOIN [Orginal].Factor ON (POP_TBL_Handled = 24) AND (FCT_ID = POP_HandledID) AND (FCT_IsDeleted = 0) '
    ELSE IF (@SoftwareType IN (9))
	   SET @Query = @Query + '
	       LEFT JOIN [Orginal].SaffronFactor ON (POP_TBL_Handled = 331) AND (SFC_ID = POP_HandledID) AND (SFC_Status <> 3) '
    ELSE IF (@SoftwareType = 4) 
	   SET @Query = @Query + '
	       LEFT JOIN [Orginal].GoldFactor ON (POP_TBL_Handled = 215) AND (GFC_ID = POP_HandledID) AND (GFC_IsDeleted = 0) '
    
    SET @Query = @Query + '
	WHERE ((POP_Status <> 3) OR (POP_Status IS NULL)) '
    
    
    IF @OperationUserID IS NOT NULL
       SET @QUERY =  @QUERY + ' AND (CRT_User_ID = '+LTRIM(RTRIM(STR(@OperationUserID)))+') '    
    
    IF @CRT_SerialTransaction IS NOT NULL
       SET @Query = @Query + ' AND (CRT_SerialTransaction LIKE ''%'+@CRT_SerialTransaction+'%'') '
       
    IF @CRT_PAN IS NOT NULL
       SET @Query = @Query + ' AND (CRT_PAN LIKE ''%'+@CRT_PAN+'%'') '
       
    IF @CRT_TraceNumber IS NOT NULL
       SET @Query = @Query + ' AND (CRT_TraceNumber LIKE ''%'+@CRT_TraceNumber+'%'') '
       
    IF @CRT_TransactionFullOK IS NOT NULL
    BEGIN
       IF @CRT_TransactionFullOK = 0
          SET @Query = @Query + ' AND (CRT_TransactionFullOK = 0)' 
       ELSE IF @CRT_TransactionFullOK = 1
          SET @Query = @Query + ' AND (CRT_TransactionFullOK = 1)'   
    END   
       
    IF @CRT_AmountFrom IS NOT NULL
       SET @Query = @Query + ' AND (CRT_Amount >= '+LTRIM(RTRIM(CAST(@CRT_AmountFrom AS NVARCHAR(200))))+')'
       
    IF @CRT_AmountTo IS NOT NULL
       SET @Query = @Query + ' AND (CRT_Amount <= '+LTRIM(RTRIM(CAST(@CRT_AmountTo AS NVARCHAR(200))))+')'

    IF @CRT_SystemDateFrom IS NOT NULL
       SET @Query = @Query + ' AND ([CRT_SystemDate_Int] >=  '+LTRIM(RTRIM(STR(@CRT_SystemDateFrom_Int)))+') '

    IF @CRT_SystemDateTo IS NOT NULL
       SET @Query = @Query + ' AND ([CRT_SystemDate_Int] <=  '+LTRIM(RTRIM(STR(@CRT_SystemDateTo_Int)))+') '

    IF @CRT_SystemTimeFrom IS NOT NULL
       SET @Query = @Query + ' AND ([CRT_SystemTime] >=  '''+@CRT_SystemTimeFrom+''') '

    IF @CRT_SystemTimeTo IS NOT NULL
       SET @Query = @Query + ' AND ([CRT_SystemTime] <=  '''+@CRT_SystemTimeTo+''') '

	IF @OperationType IS NOT NULL
	BEGIN
	    SET @Query = @Query + ' AND (([POP_TBL_Handled] IN ('+ @OperationType +'))'
	   
		IF (@FactorType IS NOT NULL)
		BEGIN
		   IF (@SoftwareType IN (1,2,3,5,99))
			  SET @Query = @Query + ' OR (([POP_TBL_Handled] = 24) AND (SELECT COUNT(1) FROM [Orginal].[Factor] WHERE [FCT_ID] = [POP_HandledID] AND FCT_Type IN ('+ @FactorType +'))>0)'
		   ELSE 
		   IF (@SoftwareType IN (4)) 
			  SET @Query = @Query + ' OR (([POP_TBL_Handled] = 215) AND (SELECT COUNT(1) FROM [Orginal].[GoldFactor] WHERE [GFC_ID] = [POP_HandledID] AND GFC_Type IN ('+ @FactorType +'))>0)'	     
		END
		
		SET @Query = @Query + ')'	
	END
	   
	IF @FactorNumber IS NOT NULL
	BEGIN
	    IF (@SoftwareType IN (1,2,3,5,99))	    
	       SET @QUERY = @QUERY + ' AND (CAST(FCT_Number AS NVARCHAR(32)) '  
	    ELSE IF (@SoftwareType IN (9))	    
	       SET @QUERY = @QUERY + ' AND (CAST(SFC_No AS NVARCHAR(32)) '     
	    ELSE 
	    IF (@SoftwareType IN (4))
	       SET @QUERY = @QUERY + ' AND (GFC_Number '
	       
		IF @FactorNumber_Exact = 0
		BEGIN
			SET @QUERY = @QUERY + ' LIKE N''%' + LTRIM(RTRIM(@FactorNumber)) + '%'')'
		END
		ELSE
		BEGIN
			SET @QUERY = @QUERY + ' = N''' + LTRIM(RTRIM(@FactorNumber)) + ''')'
		END
	END	
       
    SET @Query = @Query + ' ORDER BY [CRT_ID] DESC '   
       
    --PRINT @Query
	EXEC (@Query)
                        
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SET NOCOUNT OFF;       

END
