USE [prince]
GO
/****** Object:  StoredProcedure [Orginal].[CMD_BarcodeSearch]    Script Date: 12/01/2025 13:49:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		NF.Kargarfard
-- Create date: 2011-08-29 | 1390-06-07
-- Description:	Searching Commodity Table
-- =============================================
ALTER PROCEDURE [Orginal].[CMD_BarcodeSearch]
		@DateFrom				Common._Date_ ,
		@DateTo					Common._Date_,
		@ShowAllCMD				Common._BOOLEAN_,
		@Flag					Common._INT_,
		@FCT_Type				Common._INT_,
		@STO_Type				Common._INT_,
		@NumberType             Common._TINYINT_,
		@Number					Common._MAXSTRING_,
		@ShowOneBarcode			Common._BOOLEAN_,
		@NonShowZeroStock		Common._BOOLEAN_,
		@SelectBarcodemethod	Common._INT_,
		@CMD_Code               Common._TINY_,
		@CMD_Name_Exact			Common._BOOLEAN_,
	    @CMD_Name				Common._BIG_ ,
	    @CMD_LKP_Type			Common._INT_,
	    @CMD_PLC_Department		Common._BIG_ ,
	    @CMD_PLC_Store			Common._BIG_ ,
	    @CMD_CMG_ID_LIST	    Common._BIG_ ,
	    @CMD_FirstCategory_ID	Common._INT_,
        @CMD_SecondCategory_ID	Common._INT_,
        @CMD_ThirdCategory_ID	Common._INT_,
        @CMD_CategoryCaption	Common._MAXSTRING_

	    
/*
@STO_Type
0    -- > فاکتور خرید و موجودی تمام کالاها / برگشت از فروش
1    -- > فاکتور فروش
3    -- > سند افتتاحیه
*/
/*
@Flag
1 --> بارکد خصوصیت
2 --> بارکد قیمت
*/
/*
@NumberType
1 --> شماره فاکتور
2 --> آی دی فاکتور
*/

/*
PRI-2001
@Number --> FCT_ID
 های سند افتتاحیه STO_ID
*/

---------------------------------------
/*
@ShowAllCMD = 1 --> همه کالاها
@ShowAllCMD = 0 AND @Number IS NULL --> کالاهای خریداری شده در تاریخ خاص
@ShowAllCMD = 0 AND @Number IS NOT NULL AND @STO_Type IN(0,1) --> خرید و فروش و برگشت از فروش
@FCT_Type -- > مشخص کننده نوع فاکتور
@ShowAllCMD = 0 AND @STO_Type IN(3) --> سند افتتاحیه
*/
---------------------------------------
/*
@SelectBarcodemethod

0 --> به انتخاب کاربر - همون روش قبلی، نمایش همه بارکد ها
1 --> بارکد واحد پیش فرض کالا
2 --> بارکد ریزترین واحد کالا
3 --> بارکد مطرح شده در فاکتور
مورد 1و2و3 فقط در چاپ بارکد فاکتورها استفاده میشوند
*/

/*
@CMD_CMG_ID_LIST
-1 -- > کالاهای فاقد گروه جستجو می شوند
*/


AS
BEGIN

	SET NOCOUNT ON;
    
    DECLARE @DateFrom_Int  Common._INT_ = Common.SYS_CastDateToNum(@DateFrom)
    DECLARE @DateTo_Int    Common._INT_ = Common.SYS_CastDateToNum(@DateTo)
    
	DECLARE @AttributeBarcodeIsActive  INT
	SELECT @AttributeBarcodeIsActive = SST_Value FROM [Common].[SystemSetting] WHERE ([SST_Title] = 'AttributeBarcodeIsActive') AND ([SST_UserID] = 0)    

	DECLARE @CommodityPriceBarcodeIsActive  INT
	SELECT @CommodityPriceBarcodeIsActive = SST_Value FROM [Common].[SystemSetting] WHERE ([SST_Title] = 'CommodityPriceBarcodeIsActive') AND ([SST_UserID] = 0)

	DECLARE @PrefixOfCommodityPriceBarcode  INT
	SELECT @PrefixOfCommodityPriceBarcode = SST_Value FROM [Common].[SystemSetting] WHERE ([SST_Title] = 'PrefixOfCommodityPriceBarcode') AND ([SST_UserID] = 0)

	DECLARE @LenghtCodeCommodityPriceBarcode  INT
	SELECT @LenghtCodeCommodityPriceBarcode = SST_Value FROM [Common].[SystemSetting] WHERE ([SST_Title] = 'LenghtCodeCommodityPriceBarcode') AND ([SST_UserID] = 0)


	DECLARE @LenghtPriceCommodityPriceBarcode  INT
	SELECT @LenghtPriceCommodityPriceBarcode = SST_Value FROM [Common].[SystemSetting] WHERE ([SST_Title] = 'LenghtPriceCommodityPriceBarcode') AND ([SST_UserID] = 0)
        
    
    DECLARE @RemoveZeroInBarcodeSearch  INT
	SELECT @RemoveZeroInBarcodeSearch = SST_Value FROM [Common].[SystemSetting] WHERE ([SST_Title] = 'RemoveZeroInBarcodeSearch') AND ([SST_UserID] = 0)    
        
	DECLARE @CheckPermission_Commodity  INT 
    SELECT @CheckPermission_Commodity = SST_Value FROM Common.SystemSetting WHERE SST_Title = 'DetailPermission_Commodity' AND SST_UserID = 0 
	
   	DECLARE @CheckPermission_Store  INT 
    SELECT @CheckPermission_Store = SST_Value FROM Common.SystemSetting WHERE SST_Title = 'DetailPermission_Store' AND SST_UserID = 0 
        
   	DECLARE @UseDefaultSalePriceInCommodityPriceBarcode  INT 
    SELECT @UseDefaultSalePriceInCommodityPriceBarcode = SST_Value FROM Common.SystemSetting WHERE SST_Title = 'UseDefaultSalePriceInCommodityPriceBarcode' AND SST_UserID = 0 
    
    DECLARE @UseThirdLevelInCommodity   INT
    SET @UseThirdLevelInCommodity = (SELECT SST_Value FROM [Common].[SystemSetting] WHERE [SST_Title] = 'UseThirdLevelInCommodity' AND SST_UserID = 0) 
    
    SET @CMD_Code = LTRIM(RTRIM(REPLACE(@CMD_Code, N']C', N'چژ')));	
    
    DECLARE	 @DelBeforZero     Common._INT_ =  (SELECT SST_Value  FROM Common.SystemSetting WHERE SST_Title = 'RemoveZeroInBarcodeSearch' AND SST_UserID = 0)
    IF @DelBeforZero = 1
       SET @CMD_Code = LTRIM(RTRIM(Common.SYS_RemoveChar(@CMD_Code,0)))
       

	DECLARE @QUERY   Nvarchar(Max) = '',
	        @QUERY_2 Nvarchar(Max) = ''
	
	IF (@ShowAllCMD = 0) AND (@Number IS NOT NULL) AND (@STO_Type IN (0,1)) AND (@SelectBarcodemethod > 0)
	BEGIN
	   EXEC [Orginal].[CMD_CreateBarcodeUnitCommodity] @SelectBarcodemethod, @FCT_Type, @NumberType, @Number
	END
	
	
	IF (@Number IS NULL) AND (@STO_Type IS NULL)  
	BEGIN
	   SET @QUERY = @QUERY + '
		WITH CTE
		AS
		(
		 SELECT STO_CMD_ID,
				SUM(STO_Quantity_Decimal) AS STO_Quantity
		   FROM [Orginal].StoreOperation S
		  WHERE (STO_ReturnID IS  NULL) AND
		  1 = CASE WHEN (SELECT COUNT(1) FROM [Orginal].StoreOperation WHERE (STO_ReturnID IS NOT NULL) AND (STO_ReturnID = S.STO_ID)) = 0 THEN 1 ELSE 0 END 
		  AND (STO_Type IN (0,3,4,7)) '  
	          
		IF @DateFrom IS NOT NULL
		   SET @QUERY = @QUERY + ' AND ([STO_Date_Int] >= ' +  STR(@DateFrom_Int)+') ' 
			           
		IF @DateTo IS NOT NULL
		   SET @QUERY = @QUERY + ' AND ([STO_Date_Int] <= ' +  STR(@DateTo_Int)+') ' 	  
			  
	   SET @QUERY = @QUERY + '
		  GROUP BY STO_CMD_ID  
		) '
	END
	
	SET @QUERY = @QUERY + '	
	SELECT *,
	       (CAST ([BRC_CODE] AS DECIMAL(38,0)))AS [BRC_CODE_Order]
	  FROM(
	SELECT COUNT(1) OVER () AS TotalRecords,'
	IF @RemoveZeroInBarcodeSearch = 1		   
	    SET @QUERY = @QUERY +' (SUBSTRING(CMD_Code,PATINDEX(''%[^0X]%'',CMD_Code),1000)) as CMD_Code, '
	ELSE
	    SET @QUERY = @QUERY +' [CMD_Code],'
	
    SET @QUERY = @QUERY + '
            
			[CMD_AbbName] ,
			[CMD_ID],
			[PRL_BuyPrice],
			[PRL_SalePrice],
			[CMD_CMA_Count],[CMD_CoverPrice],
			[CMD_CoverPrice] - [PRL_SalePrice] AS [CMD_ProfitOfPrice],'
			
	IF (@ShowAllCMD = 0) AND (((@Number IS NOT NULL)AND (@STO_Type IN (0,1))) OR (@STO_Type IN (3)))
	BEGIN 
	   IF @Flag = 1
	       SET @QUERY = @QUERY + ' [BRC_CODE] = CASE WHEN (STO_PrintBarcode IS NULL)OR('+LTRIM(RTRIM(STR(@AttributeBarcodeIsActive)))+' = 0) THEN [BRC_CODE] ELSE STO_PrintBarcode END,
								   [IsAttributeBarcode]= CASE WHEN STO_PrintBarcode IS NULL THEN 0 ELSE 1 END,
								   [CMD_Name]= [CMD_Name]+'' ''+ISNULL(STO_P1,'''')+'' ''+ISNULL(STO_P2,'''')+'' ''+ ISNULL(STO_P3,'''')+'' ''+ ISNULL(STO_P4,'''')+'' ''+ ISNULL(STO_P5,''''),
								   [CMD_AcutalName] = [CMD_Name] + '' '' + CASE WHEN ISNULL(BRC_CNU_ID,0) <> 0 THEN 
								                      ''[ ''+(SELECT LKP_CAPTION FROM Common.[Lookup] WHERE LKP_ID IN (SELECT CNU_LKP_UnitID FROM Common.CountingUnit WHERE CNU_ID = BRC_CNU_ID))+'' ]''
								                      ELSE '''' END ,
								   [CMD_PureName] = [CMD_Name],
								   [CMD_AttributeName]= ISNULL(STO_P1,'''')+'' ''+ISNULL(STO_P2,'''')+'' ''+ ISNULL(STO_P3,'''')+'' ''+ ISNULL(STO_P4,'''')+'' ''+ ISNULL(STO_P5,''''),
								   ISNULL(STO_P1,'''') AS STO_P1 ,
								   ISNULL(STO_P2,'''') AS STO_P2 ,
								   ISNULL(STO_P3,'''') AS STO_P3 ,
								   ISNULL(STO_P4,'''') AS STO_P4 ,
								   ISNULL(STO_P5,'''') AS STO_P5 ,
								   ISNULL((SELECT CMA_LABEL FROM  [Common].CommodityAttribute  WHERE CMA_CMD_ID=[CMD_ID] AND CMA_FIELDNAME=''P1'' AND CMA_IsDeleted = 0),'''') AS LBL_P1,
								   ISNULL((SELECT CMA_LABEL FROM  [Common].CommodityAttribute  WHERE CMA_CMD_ID=[CMD_ID] AND CMA_FIELDNAME=''P2'' AND CMA_IsDeleted = 0),'''') AS LBL_P2,
								   ISNULL((SELECT CMA_LABEL FROM  [Common].CommodityAttribute  WHERE CMA_CMD_ID=[CMD_ID] AND CMA_FIELDNAME=''P3'' AND CMA_IsDeleted = 0),'''') AS LBL_P3,
								   ISNULL((SELECT CMA_LABEL FROM  [Common].CommodityAttribute  WHERE CMA_CMD_ID=[CMD_ID] AND CMA_FIELDNAME=''P4'' AND CMA_IsDeleted = 0),'''') AS LBL_P4,
								   ISNULL((SELECT CMA_LABEL FROM  [Common].CommodityAttribute  WHERE CMA_CMD_ID=[CMD_ID] AND CMA_FIELDNAME=''P5'' AND CMA_IsDeleted = 0),'''') AS LBL_P5 , 
								   '
	
	   ELSE
	   BEGIN
	       SET @QUERY = @QUERY + ' [BRC_CODE] = CASE WHEN ('+LTRIM(RTRIM(STR(@CommodityPriceBarcodeIsActive)))+' = 0) THEN [BRC_CODE] ELSE 
	                               CAST('+LTRIM(RTRIM(STR(@PrefixOfCommodityPriceBarcode)))+' AS NVARCHAR(20))+
								   ( REPLICATE(''0'','+LTRIM(RTRIM(STR(@LenghtCodeCommodityPriceBarcode)))+' - LEN(CONVERT(VARCHAR(2000),CMD_CODE))) + CONVERT(VARCHAR(2000),CMD_CODE)) +
								   ( REPLICATE(''0'','+LTRIM(RTRIM(STR(@LenghtPriceCommodityPriceBarcode)))+' - LEN(CONVERT(VARCHAR(2000), '
								   
		   IF (@STO_Type IN (0,3)) AND (@UseDefaultSalePriceInCommodityPriceBarcode = 1)						   
			  SET @QUERY = @QUERY + 'CAST ( PRL_SalePrice AS DECIMAL (38,0))))) + CONVERT(VARCHAR(2000),CAST ( PRL_SalePrice AS DECIMAL (38,0))))'
		   ELSE	  
			  SET @QUERY = @QUERY + 'CAST ( STO_UnitPrice AS DECIMAL (38,0))))) + CONVERT(VARCHAR(2000),CAST ( STO_UnitPrice AS DECIMAL (38,0))))'
			  
		   SET @QUERY = @QUERY + '
								   END ,
								   1 AS [IsAttributeBarcode], 
								   [CMD_Name],
								   [CMD_AcutalName] = [CMD_Name] + '' '' + CASE WHEN ISNULL(BRC_CNU_ID,0) <> 0 THEN 
								                      ''[ ''+(SELECT LKP_CAPTION FROM Common.[Lookup] WHERE LKP_ID IN (SELECT CNU_LKP_UnitID FROM Common.CountingUnit WHERE CNU_ID = BRC_CNU_ID))+'' ]''
								                      ELSE '''' END ,
								   [CMD_PureName] = [CMD_Name],
								   [CMD_AttributeName]= '''',
								   '''' AS STO_P1 ,
								   '''' AS STO_P2 ,
								   '''' AS STO_P3 ,
								   '''' AS STO_P4 ,
								   '''' AS STO_P5 ,
								   '''' AS LBL_P1 ,
								   '''' AS LBL_P2 ,
								   '''' AS LBL_P3 ,
								   '''' AS LBL_P4 ,
								   '''' AS LBL_P5 ,
	                           '
	   END                         
	END                           
	ELSE   
	   SET @QUERY = @QUERY + ' [BRC_CODE],
	                           0 AS [IsAttributeBarcode], 
	                           [CMD_Name],
	                           [CMD_AcutalName] = [CMD_Name] + '' '' + CASE WHEN ISNULL(BRC_CNU_ID,0) <> 0 THEN 
								                      ''[ ''+(SELECT LKP_CAPTION FROM Common.[Lookup] WHERE LKP_ID IN (SELECT CNU_LKP_UnitID FROM Common.CountingUnit WHERE CNU_ID = BRC_CNU_ID))+'' ]''
								                      ELSE '''' END ,
	                           [CMD_PureName] = [CMD_Name],
	                           [CMD_AttributeName]= '''',
	                           '''' AS STO_P1 ,
							   '''' AS STO_P2 ,
							   '''' AS STO_P3 ,
							   '''' AS STO_P4 ,
							   '''' AS STO_P5 ,
							   '''' AS LBL_P1 ,
							   '''' AS LBL_P2 ,
							   '''' AS LBL_P3 ,
							   '''' AS LBL_P4 ,
							   '''' AS LBL_P5 ,
	                           '	   
			
	IF (@ShowAllCMD = 1) 	
	BEGIN	
	   SET @QUERY = @QUERY + ' CAST(ISNULL((SELECT SUM(SCA_CurrentAmount) FROM Common.StoreCommodityAmount WHERE (SCA_CMD_ID = [CMD_ID]) '
	     --IF (@UserID <> 1) AND (@CheckPermission_Store = 1) AND ([Common].[PER_CheckSelectAll](@UserID, 5000000) = 0) 
	     --    SET @Query = @Query +' AND [SCA_PLC_ID] IN (SELECT [PER_ObjectID] from [Common].View_AllUserPermission  
		    --                     WHERE UAC_ID = '+ RTRIM(LTRIM(@UserID)) + ' AND PER_Type = 8)'			
	   SET @QUERY = @QUERY + ' ),0) AS NVARCHAR(50))AS CMD_CurrentAmount '
	END
	ELSE
       SET @QUERY = @QUERY + ' ''0'' AS CMD_CurrentAmount '
    
    IF (@ShowAllCMD = 0) AND (@Number IS NULL) AND (@STO_Type IS NULL)         
	   SET @QUERY = @QUERY + '
	     ,CMD_BuyCount = CAST(ISNULL((SELECT STO_Quantity FROM CTE WHERE [CMD_ID] = STO_CMD_ID),0) AS NVARCHAR(50)) '
	ELSE
	   SET @QUERY = @QUERY + ',CMD_BuyCount = ''0'' '
	   
	IF (@ShowAllCMD = 0) AND ((@Number IS NOT NULL)OR (@STO_Type = 3))
	BEGIN
	   IF (@SelectBarcodemethod = 0)
	      SET @QUERY = @QUERY + ' ,CMD_NumberCount = CAST(ISNULL(STO_Quantity,0) AS NVARCHAR(50)) '
	   ELSE
	   IF (@SelectBarcodemethod IN (1,2))
	      SET @QUERY = @QUERY + ' ,CMD_NumberCount = CAST(ISNULL([Common].[CNU_GetQuantityUnit](CMD_ID,CNU_ID,ISNULL(STO_Quantity,0)),0) AS NVARCHAR(50)) '     
	   ELSE
	   IF (@SelectBarcodemethod = 3)
	      SET @QUERY = @QUERY + ' ,CMD_NumberCount = CAST(ISNULL(FCI_Quantity,0) AS NVARCHAR(50)) '
	     
	END   
	ELSE
	   SET @QUERY = @QUERY + ',CMD_NumberCount = ''0'' '
	        
	SET @QUERY = @QUERY + '
	     FROM [Common].[Commodity] 
	     JOIN Common.CountingUnitMultiple ON (CUM_CMD_ID = CMD_ID) AND (CUM_IsDefault = 1) AND (CUM_IsDelete = 0)
	     JOIN Common.CountingUnit ON (CNU_CUM_ID = CUM_ID) AND (CNU_CMD_ID = CMD_ID) '
	 
	 IF NOT((@ShowAllCMD = 0) AND (@Number IS NOT NULL) AND (@STO_Type IN (0,1)) AND (@SelectBarcodemethod > 0))
	    SET @QUERY = @QUERY + ' AND (CNU_Coefficient IS NULL) ' 
	     
	SET @QUERY = @QUERY + '     
	LEFT JOIN [Common].[PriceLevel] ON [PRL_CMD_ID] = [CMD_ID] AND (PRL_CUM_ID = CUM_ID) AND (PRL_CNU_ID = CNU_ID) AND (PRL_IsDefault = 1) '
         
    IF (@ShowAllCMD = 0) AND (@Number IS NULL) AND (@STO_Type IS NULL) 
    BEGIN
        SET @QUERY = @QUERY + ' JOIN CTE ON (STO_CMD_ID = CMD_ID) '  
    
    END
    ELSE
    IF (@ShowAllCMD = 0) AND (@Number IS NOT NULL) AND (@STO_Type IN (0,1))   
	BEGIN
	   SET @QUERY = @QUERY + '
	     JOIN [Orginal].StoreOperation S ON (STO_CMD_ID = CMD_ID)
	     JOIN [Orginal].FactorItem ON (FCI_STO_ID = STO_ID)
	     JOIN [Orginal].Factor ON (FCT_ID = FCI_FCT_ID)'
    END
    ELSE
    IF (@ShowAllCMD = 0) AND (@STO_Type IN (3))   
	BEGIN
	   SET @QUERY = @QUERY + '
	     JOIN [Orginal].StoreOperation S ON (STO_CMD_ID = CMD_ID) '
    END 
    
    SET @QUERY = @QUERY + ' LEFT JOIN [Common].[CommodityBarcode] ON [BRC_CMD_ID] = [CMD_ID] AND [BRC_UseForPrint] = 1 AND ([BRC_CODE] not like ''%[^0-9]%'') '
    
    IF (@ShowAllCMD = 1) AND (@ShowOneBarcode = 1)
		SET @QUERY = @QUERY + ' AND [BRC_TYPE] = 0';    
		
    IF (@ShowAllCMD = 0) AND (((@Number IS NOT NULL)AND (@STO_Type IN (0,1))) OR (@STO_Type IN (3))) 
    BEGIN
       IF @Flag = 1
          SET @QUERY = @QUERY + 'AND ((STO_PrintBarcode IS NULL)OR('+LTRIM(RTRIM(STR(@AttributeBarcodeIsActive)))+' = 0))'
       ELSE
          SET @QUERY = @QUERY + 'AND ('+LTRIM(RTRIM(STR(@CommodityPriceBarcodeIsActive)))+' = 0)'
       IF (@SelectBarcodemethod > 0)
       SET @QUERY = @QUERY + ' AND (CNU_ID = BRC_CNU_ID) '     
    END 
       
    SET @QUERY = @QUERY + '
        WHERE (([CMD_IsDisabled] = 0) OR ([CMD_IsDisabled] IS NULL)) AND (CMD_PrintBarcode = 1) ' 
	
	IF (@ShowAllCMD = 1) AND (@NonShowZeroStock = 1)
		SET @QUERY = @QUERY + ' AND (CMD_ID IN ( SELECT SCA_CMD_ID FROM Common.StoreCommodityAmount 
                                GROUP BY SCA_CMD_ID
                                HAVING (ISNULL(SUM(SCA_CurrentAmount),0) > 0)))';	
	
	IF (@ShowAllCMD = 0) AND (@Number IS NOT NULL) AND (@STO_Type IN (0,1))   
	BEGIN
		   SET @QUERY = @QUERY + '     
			  AND (STO_ReturnID IS  NULL) AND
			  1 = CASE WHEN (SELECT COUNT(1) FROM [Orginal].StoreOperation WHERE (STO_ReturnID IS NOT NULL) AND (STO_ReturnID = S.STO_ID)) = 0 THEN 1 ELSE 0 END 
			  AND (STO_Type IN ('+STR(@STO_Type)+'))
			  AND (FCT_Type IN('+STR(@FCT_Type)+'))
			  AND ((FCT_IsDeleted IS NULL) OR (FCT_IsDeleted = 0)) AND (FCI_Status <> 3) '
			  
		   IF @NumberType = 1	  
			  SET @QUERY = @QUERY + '  AND (CAST(FCT_Number AS NVARCHAR(32)) = '''+@Number+''') '
		   ELSE	  
		   IF @NumberType = 2	  
			  SET @QUERY = @QUERY + '  AND (CAST(FCT_ID AS NVARCHAR(32)) = '''+@Number+''') '
	
	   IF (@Flag = 2)
	      SET @QUERY = @QUERY + ' AND (LEN(CONVERT(VARCHAR(200),CMD_CODE)) <= '+LTRIM(RTRIM(STR(@LenghtCodeCommodityPriceBarcode)))+' )
	                              AND NOT((STO_UnitPrice -FLOOR(STO_UnitPrice)) > 0 ) '
	 
	   IF (@SelectBarcodemethod > 0)
	   BEGIN
	      IF @SelectBarcodemethod = 1 
	         SET @QUERY = @QUERY + ' AND (CNU_IsDefault = 1) ' 
	      IF @SelectBarcodemethod = 2
	         SET @QUERY = @QUERY + ' AND (CNU_Coefficient IS NULL) '   
	      IF @SelectBarcodemethod = 3
	         SET @QUERY = @QUERY + ' AND (CNU_ID = FCI_CNU_ID) ' 	             
	   END	                              
	
	END	  
	ELSE
	IF (@ShowAllCMD = 0) AND (@STO_Type IN (3))   
	BEGIN
		   SET @QUERY = @QUERY + '     
			  AND (STO_ReturnID IS  NULL) AND
			  1 = CASE WHEN (SELECT COUNT(1) FROM [Orginal].StoreOperation WHERE (STO_ReturnID IS NOT NULL) AND (STO_ReturnID = S.STO_ID)) = 0 THEN 1 ELSE 0 END 
			  AND (STO_Type IN (3)) '
		   
		   IF (@Number IS NOT NULL)
		   	 SET @QUERY = @QUERY + ' AND (STO_ID IN ('+@Number+')) '
	
	   IF (@Flag = 2)
	      SET @QUERY = @QUERY + ' AND (LEN(CONVERT(VARCHAR(200),CMD_CODE)) <= '+LTRIM(RTRIM(STR(@LenghtCodeCommodityPriceBarcode)))+' )
	                              AND NOT((STO_UnitPrice -FLOOR(STO_UnitPrice)) > 0 ) '
	
	END
	
	IF (@ShowAllCMD = 0) AND (@Number IS NULL) AND (@STO_Type IN (0,1))    
	BEGIN
	   SET @QUERY = @QUERY + 
	   ' AND [CMD_ID] IN (SELECT DISTINCT FCI_CMD_ID
							FROM [Orginal].FactorItem JOIN [Orginal].Factor ON FCT_ID = FCI_FCT_ID
						   WHERE ((FCT_IsDeleted IS NULL) OR (FCT_IsDeleted = 0)) AND (FCI_Status <> 3) 
                             AND (FCT_Type = '+STR(@FCT_Type)+') '  
           
	   IF @DateFrom IS NOT NULL
		  SET @QUERY = @QUERY + ' AND ([FCT_Date_Int] >= ' +  STR(@DateFrom_Int)+') ' 
		           
	   IF @DateTo IS NOT NULL
		  SET @QUERY = @QUERY + ' AND ([FCT_Date_Int] <= ' +  STR(@DateTo_Int)+') ' 						   
		
		SET @QUERY = @QUERY +')'				   
	END
	SET @QUERY = @QUERY +') AS AZA
	'
    
    IF (@CMD_Code IS NOT NULL) OR (@CMD_Name IS NOT NULL) OR (@CMD_LKP_Type IS NOT NULL) OR 
       (@CMD_PLC_Department IS NOT NULL) OR (@CMD_PLC_Store IS NOT NULL) OR (@CMD_CMG_ID_LIST IS NOT NULL) OR 
       (@CMD_FirstCategory_ID IS NOT NULL) OR (@CMD_SecondCategory_ID IS NOT NULL) OR (@CMD_ThirdCategory_ID IS NOT NULL) OR 
       (@CMD_CategoryCaption IS NOT NULL)
    BEGIN
       
       SET @QUERY_2 = 
           ' WHERE (1 = 1) '
           
           
       IF @CMD_Code IS NOT NULL
	   BEGIN

	      IF @DelBeforZero = 1 
	         SET @QUERY_2 = @QUERY_2 + ' AND ( Common.SYS_RemoveChar([BRC_CODE],0) LIKE N''%' + LTRIM(RTRIM(@CMD_Code)) + '%'')'
	      ELSE     
	         SET @QUERY_2 = @QUERY_2 + ' AND ( [BRC_CODE] LIKE N''%' + LTRIM(RTRIM(@CMD_Code)) + '%'') '  
	
       END    

       IF @CMD_Name IS NOT NULL
	   BEGIN
	      IF @CMD_Name_Exact = 0
		     SET @QUERY_2 = @QUERY_2 + ' AND ([CMD_Name] LIKE N''%' + LTRIM(RTRIM(@CMD_Name)) + '%'')'
	      ELSE
	         SET @QUERY_2 = @QUERY_2 + ' AND ([CMD_Name] = ''' + LTRIM(RTRIM(@CMD_Name)) + ''')'	  
	   END
	   
	   IF (@CMD_LKP_Type IS NOT NULL) OR 
       (@CMD_PLC_Department IS NOT NULL) OR (@CMD_PLC_Store IS NOT NULL) OR  
       (@CMD_FirstCategory_ID IS NOT NULL) OR (@CMD_SecondCategory_ID IS NOT NULL) OR (@CMD_ThirdCategory_ID IS NOT NULL) OR 
       (@CMD_CategoryCaption IS NOT NULL)
       BEGIN
           SET @QUERY_2 = @QUERY_2 + ' AND ([CMD_ID] IN ( SELECT CMD_ID
                                                            FROM [Common].[Commodity]
                                                           WHERE (1 = 1) '  
       
       
            IF @CMD_LKP_Type IS NOT NULL
	           SET @QUERY_2 = @QUERY_2 + ' AND ([CMD_LKP_Type] IN( ' + LTRIM(RTRIM(@CMD_LKP_Type)) + '))'


      	    IF @CMD_PLC_Store IS NOT NULL
	  	 	   SET @QUERY_2 = @QUERY_2 + ' AND ([CMD_PLC_Store] IN ( ' + LTRIM(RTRIM(@CMD_PLC_Store)) + '))'
	

	        IF @CMD_PLC_Department IS NOT NULL
	   	   	   SET @QUERY_2 = @QUERY_2 + ' AND ([CMD_PLC_Department] IN (' + LTRIM(RTRIM(@CMD_PLC_Department)) + '))'
	
            IF @UseThirdLevelInCommodity = 1
            BEGIN       
				IF ISNULL(@CMD_FirstCategory_ID,0)> 0 
				   SET @QUERY_2 = @QUERY_2 + ' AND (CMD_FirstCategory_ID = '+LTRIM(RTRIM(STR(@CMD_FirstCategory_ID)))+') ' 
			       
				IF ISNULL(@CMD_SecondCategory_ID,0)> 0 
				   SET @QUERY_2 = @QUERY_2 + ' AND (CMD_SecondCategory_ID = '+LTRIM(RTRIM(STR(@CMD_SecondCategory_ID)))+') '   
			       
				IF ISNULL(@CMD_ThirdCategory_ID,0)> 0 
				   SET @QUERY_2 = @QUERY_2 + ' AND (CMD_ThirdCategory_ID = '+LTRIM(RTRIM(STR(@CMD_ThirdCategory_ID)))+') ' 
			       
				IF ISNULL(@CMD_CategoryCaption,'') <> ''
				   SET @QUERY_2 = @QUERY_2 + ' AND (CMD_CategoryCaption LIKE ''%'+@CMD_CategoryCaption+'%'')'            
			END  	
	
	
	       SET @QUERY_2 = @QUERY_2 + ' )) '   
	   
        END 
    
    
        IF @CMD_CMG_ID_LIST IS NOT NULL
	    BEGIN
	       IF (CHARINDEX('-1',@CMD_CMG_ID_LIST) = 0)
			  SET @QUERY_2 = @QUERY_2 + ' AND (CMD_ID IN (SELECT [CGM_CMD_ID] FROM [Common].CommodityGroupMembership WHERE (CGM_CMG_ID IN (' + LTRIM(RTRIM(@CMD_CMG_ID_LIST)) + '))))';
		   ELSE
              SET @QUERY_2 = @QUERY_2 + ' AND (CMD_ID NOT IN (SELECT [CGM_CMD_ID] FROM [Common].CommodityGroupMembership ))'		
	    END
    END   	
    
    

	--print @QUERY
	--print @QUERY_2
	
	EXECUTE (@QUERY+@QUERY_2);

END
