USE [prince]
GO
/****** Object:  StoredProcedure [Orginal].[CMD_SearchAdvanced]    Script Date: 11/20/2025 15:22:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Orginal].[CMD_SearchAdvanced]
    
    @CMD_Name						[Common]._SMALL_,
    @CMD_Code   					[Common]._TINY_ , 
    @CMD_InCostFrom					[Common]._MONEY_,
    @CMD_InCostTo					[Common]._MONEY_,
    @CMD_CostFrom					[Common]._MONEY_,
    @CMD_CostTo						[Common]._MONEY_,
    @CMD_HotkeyCode					[Common]._INT_,
    @CMD_LKP_Groups					[Common]._BIG_,
    @CMD_IsFood						[Common]._BOOLEAN_,    
	@CMD_LKP_Section				[Common]._INT_,    
	@UserID				            [Common]._INT_
AS
BEGIN

    SET NOCOUNT ON;

   DECLARE @QUERY							[Common]._BIG_,
            @PriceProductionFoodMethod		[Common]._INT_,
            @Temp_CMD_IDs					[Common]._MAXSTRING_,
            @CMD_ID							[Common]._INT_,
            @TempCount						[Common]._INT_,
            @CMD_InCost						[Common]._MONEY_					
            
    
    SET @PriceProductionFoodMethod = (SELECT SST_Value FROM Common.SystemSetting WHERE SST_Title = 'PriceProductionFoodMethod' AND SST_UserID = 0)
    DECLARE @TempLastPriceTable TABLE (TPrice MONEY,TCmdId INT)
    DECLARE @TempFIFOPriceTable TABLE (TRandomID INT,TPLC_ID INT, TCMD_ID INT, TBuyPrice MONEY)  

    DECLARE @LastPriceTable TABLE (TPrice MONEY,TCmdId INT, TCMD_ID_Product INT)
    DECLARE @FIFOPriceTable TABLE (TRandomID INT,TPLC_ID INT, TCMD_ID INT, TBuyPrice MONEY, TCMD_ID_Product INT)  
    DECLARE  @TempIngridient TABLE (
		PRD_CMD_ID_Product		[Common]._INT_,
		PRD_CMD_ID_Ingredint	[Common]._INT_,
		PRD_Amount				[Common]._FLOAT_, 
		[Level]					[Common]._INT_,
		CMD_InCost				[Common]._MONEY_,
		IsPackage               [Common]._INT_)    
     
    DECLARE @TempTableCommodity TABLE 
    (	[CMD_Id]			[Common]._INT_,
		[CMD_Name]			[Common]._SMALL_, 
		[CMD_Code]			[Common]._SMALL_,   
		[CMD_AbbName]		[Common]._SMALL_, 	           
		[CMD_InCost]		[Common]._MONEY_, 
		[CMD_Cost1]         [Common]._MONEY_,
		[CMD_Cost2]         [Common]._MONEY_,
		[CMD_Cost3]         [Common]._MONEY_, 
		[CMD_Cost4]         [Common]._MONEY_, 
		[CMD_Cost5]         [Common]._MONEY_, 
		[CMD_Description]   [Common]._BIG_, 
		[CMD_HotKeyCode]    [Common]._INT_,
		[LKP_Caption]		[Common]._SMALL_,
		[LKP_Order]			[Common]._INT_,
		[LKP_CityCode]		[Common]._INT_,
		[CMD_InitialCount]  [Common]._MONEY_,
		[CMD_LKP_RecGrpID]  [Common]._MONEY_,
		[CMD_LKP_Section]   [Common]._INT_,
		[CMD_LKP_Group]     [Common]._INT_  )


            
    SET @QUERY = 
    'SELECT [CMD_Id],
            [CMD_Name], 
            [CMD_Code],
            [CMD_AbbName],           
            [CMD_InCost],
            [CMD_Cost1],
            [CMD_Cost2],
            [CMD_Cost3],
            [CMD_Cost4],
            [CMD_Cost5],
            [CMD_Description],
            [CMD_HotKeyCode],
            [LKP_Caption],
            L.[LKP_Order],
            [LKP_CityCode] ,
            [CMD_InitialCount],
            [CMD_LKP_RecGrpID],
            [CMD_LKP_Section], 
            [CMD_LKP_Group]
    FROM [Common].[View_CMD_Info] 
    LEFT JOIN [Common].[Lookup] L ON LKP_ID = CMD_LKP_Group
    WHERE 1 = 1' 

    IF @CMD_Name IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([CMD_Name] LIKE N''%' + LTRIM(RTRIM(@CMD_Name)) + '%'')'
    END

    IF @CMD_Code  IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([CMD_Code] LIKE N''%' + LTRIM(RTRIM(@CMD_Code)) + '%'')'
    END
    
    IF @CMD_InCostFrom IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([CMD_InCost] >= ' + str(@CMD_InCostFrom) + ')'
    END
    
    IF @CMD_InCostTo IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([CMD_InCost] <= ' + str(@CMD_InCostTo) + ')'
    END
    
    IF @CMD_LKP_Section IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ((ISNULL([CMD_LKP_Section],0) = 0 ) OR ([CMD_LKP_Section] = ' + str(@CMD_LKP_Section) + '))'
    END          
    
    IF (@CMD_CostFrom IS NOT NULL) OR (@CMD_CostTo IS NOT NULL)
    BEGIN
       SET @QUERY = @QUERY + ' AND ( ' 
       
       -- CMD_Cost1
       SET @QUERY = @QUERY + ' ( ' 
       IF @CMD_CostFrom IS NOT NULL
          SET @QUERY = @QUERY + ' ([CMD_Cost1] >= ' + str(@CMD_CostFrom) + ') ' 
          
       IF @CMD_CostTo IS NOT NULL
       BEGIN
          IF @CMD_CostFrom IS NOT NULL
             SET @QUERY = @QUERY + ' AND '
          
          SET @QUERY = @QUERY + ' ([CMD_Cost1] <= ' + str(@CMD_CostTo) + ')'
       END      
       SET @QUERY = @QUERY + ' )'   
           
       -- CMD_Cost2
       SET @QUERY = @QUERY + ' OR( ' 
       IF @CMD_CostFrom IS NOT NULL
          SET @QUERY = @QUERY + ' ([CMD_Cost2] >= ' + str(@CMD_CostFrom) + ') ' 
          
       IF @CMD_CostTo IS NOT NULL
       BEGIN
          IF @CMD_CostFrom IS NOT NULL
             SET @QUERY = @QUERY + ' AND '
          
          SET @QUERY = @QUERY + ' ([CMD_Cost2] <= ' + str(@CMD_CostTo) + ')'
       END      
       SET @QUERY = @QUERY + ' )' 
       
       -- CMD_Cost3
       SET @QUERY = @QUERY + ' OR( ' 
       IF @CMD_CostFrom IS NOT NULL
          SET @QUERY = @QUERY + ' ([CMD_Cost3] >= ' + str(@CMD_CostFrom) + ') ' 
          
       IF @CMD_CostTo IS NOT NULL
       BEGIN
          IF @CMD_CostFrom IS NOT NULL
             SET @QUERY = @QUERY + ' AND '
          
          SET @QUERY = @QUERY + ' ([CMD_Cost3] <= ' + str(@CMD_CostTo) + ')'
       END      
       SET @QUERY = @QUERY + ' )'        
        
       -- CMD_Cost4
       SET @QUERY = @QUERY + ' OR( ' 
       IF @CMD_CostFrom IS NOT NULL
          SET @QUERY = @QUERY + ' ([CMD_Cost4] >= ' + str(@CMD_CostFrom) + ') ' 
          
       IF @CMD_CostTo IS NOT NULL
       BEGIN
          IF @CMD_CostFrom IS NOT NULL
             SET @QUERY = @QUERY + ' AND '
          
          SET @QUERY = @QUERY + ' ([CMD_Cost4] <= ' + str(@CMD_CostTo) + ')'
       END      
       SET @QUERY = @QUERY + ' )'         
        
        
       -- CMD_Cost5
       SET @QUERY = @QUERY + ' OR( ' 
       IF @CMD_CostFrom IS NOT NULL
          SET @QUERY = @QUERY + ' ([CMD_Cost5] >= ' + str(@CMD_CostFrom) + ') ' 
          
       IF @CMD_CostTo IS NOT NULL
       BEGIN
          IF @CMD_CostFrom IS NOT NULL
             SET @QUERY = @QUERY + ' AND '
          
          SET @QUERY = @QUERY + ' ([CMD_Cost5] <= ' + str(@CMD_CostTo) + ')'
       END      
       SET @QUERY = @QUERY + ' )'         
        
       SET @QUERY = @QUERY + ' )'
    END 
    
    
    IF @CMD_HotkeyCode IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([CMD_HotKeyCode] = ' + str(@CMD_HotkeyCode) + ')'
    END
    
    IF @CMD_LKP_Groups IS NOT NULL
    BEGIN
        SET @QUERY = @QUERY + ' AND ([CMD_LKP_Group] in (' + @CMD_LKP_Groups + '))'
    END
    SET @QUERY = @QUERY + ' AND [CMD_IsFood] = ' + STR(@CMD_IsFood);

    INSERT INTO @TempTableCommodity    
    
    EXEC (@QUERY);
   -- print @QUERY
   
    
    IF (@CMD_IsFood = 1) -- غذا
    BEGIN
		DECLARE CMD CURSOR for
			   SELECT CMD_ID,CMD_InCost
				 FROM @TempTableCommodity  
		OPEN CMD
		FETCH NEXT FROM CMD INTO @CMD_ID,@CMD_InCost
		WHILE @@FETCH_STATUS =0
		BEGIN
			 DELETE FROM @TempIngridient
			 
			 INSERT @TempIngridient (PRD_CMD_ID_Product, PRD_CMD_ID_Ingredint, PRD_Amount,[Level],CMD_InCost,IsPackage)
			   EXEC [Common].[CMD_SelectIngriedentsByMasterID] @CMD_ID,1
		     
			 SET @Temp_CMD_IDs = NULL
		     
			 SELECT @Temp_CMD_IDs = COALESCE(@Temp_CMD_IDs + ',' + CAST(PRD_CMD_ID_Ingredint AS NVARCHAR(2000)) ,CAST(PRD_CMD_ID_Ingredint AS NVARCHAR(2000))) 
			   FROM @TempIngridient 
			  WHERE IsPackage = 0 AND
					PRD_CMD_ID_Product <> PRD_CMD_ID_Ingredint 			     		     
		     
		     
			 IF (ISNULL(@Temp_CMD_IDs,'') <> '')
			 BEGIN
				 IF (@PriceProductionFoodMethod) = 0 -- قیمت خرید پیش فرض 
				 BEGIN
					 INSERT INTO @LastPriceTable 
					 SELECT CAST(ROUND((SELECT SUM(PRD_Amount * CMD_InCost) 
									      FROM @TempIngridient 
									     WHERE IsPackage = 0 AND
										       PRD_CMD_ID_Product <> PRD_CMD_ID_Ingredint ),0,0) AS MONEY) , @CMD_ID, @CMD_ID 
	                                      
				 END
				 ELSE
				 IF (@PriceProductionFoodMethod) = 1 -- قیمت آخرین خرید
				 BEGIN
			        DELETE FROM @TempLastPriceTable
			        
					INSERT INTO @TempLastPriceTable  
					EXEC [Orginal].CMD_LoadLastPrice @Temp_CMD_IDs,0,1 
					
					INSERT INTO @LastPriceTable
					SELECT TPrice ,TCmdId, @CMD_ID from @TempLastPriceTable
					
					UPDATE LP
					   SET TPrice = CAST(ROUND((PRD_Amount * TPrice),0,0) AS MONEY)
					  FROM @LastPriceTable LP
					  JOIN @TempIngridient ON PRD_CMD_ID_Ingredint = TCmdId
					 WHERE TCMD_ID_Product = @CMD_ID 
					 
				 END  
				 ELSE
				 IF (@PriceProductionFoodMethod) = 2 -- FIFO قیمت خرید بر اساس   
				 BEGIN      
					DELETE FROM @TempFIFOPriceTable
					INSERT INTO @TempFIFOPriceTable	  
					  EXEC [Orginal].CalculateBuyPrice_FIFO @Temp_CMD_IDs,NULL
					
					INSERT INTO @FIFOPriceTable  
					SELECT TRandomID, TPLC_ID, TCMD_ID, TBuyPrice, @CMD_ID FROM @TempFIFOPriceTable

					
					UPDATE FP
					   SET TBuyPrice = CAST(ROUND((PRD_Amount * TBuyPrice),0,0) AS MONEY)
					  FROM @FIFOPriceTable FP
					  JOIN @TempIngridient ON PRD_CMD_ID_Ingredint = TCMD_ID
					 WHERE TCMD_ID_Product = @CMD_ID 
					 
				 END
			 END
			 ELSE
			 BEGIN
				IF (@PriceProductionFoodMethod) = 2
				BEGIN
				   INSERT INTO @FIFOPriceTable 
				   SELECT 1,1,@CMD_ID,@CMD_InCost,@CMD_ID
				END
				ELSE
				BEGIN
  				   INSERT INTO @LastPriceTable
  				   SELECT @CMD_InCost,@CMD_ID,@CMD_ID		        
				END  
			 END
	              
		   FETCH NEXT FROM CMD INTO @CMD_ID,@CMD_InCost
		END
		CLOSE CMD
		DEALLOCATE CMD	   

    END
    

    
    SELECT [CMD_Id],
           [CMD_Name], 
           [CMD_Code],
           CAST([CMD_Code] AS DECIMAL(38,0)) AS CMD_Code_Order,
           [CMD_AbbName],           
           [Common].[SYS_AttachCamma](CMD_InCost) AS [CMD_InCost],
           CMD_InCost AS CMD_InCost_Pure, 
           ProductPrice =[Common].[SYS_AttachCamma]( 
                         CASE @PriceProductionFoodMethod 
                               WHEN 0 THEN L.TPrice 
                               WHEN 1 THEN L.TPrice 
                               WHEN 2 THEN F.TBuyPrice
                          END) ,
            ProductPrice_Pure =CASE @PriceProductionFoodMethod 
                               WHEN 0 THEN L.TPrice 
                               WHEN 1 THEN L.TPrice 
                               WHEN 2 THEN F.TBuyPrice
                               END ,              
            [Common].[SYS_AttachCamma]([CMD_Cost1]) AS [CMD_Cost1],
            [Common].[SYS_AttachCamma]([CMD_Cost2]) AS [CMD_Cost2],
            [Common].[SYS_AttachCamma]([CMD_Cost3]) AS [CMD_Cost3],
            [Common].[SYS_AttachCamma]([CMD_Cost4]) AS [CMD_Cost4],
            [Common].[SYS_AttachCamma]([CMD_Cost5]) AS [CMD_Cost5],
			[CMD_Cost1] AS [CMD_Cost1_Pure] ,
			[CMD_Cost2] AS [CMD_Cost2_Pure] ,
			[CMD_Cost3] AS [CMD_Cost3_Pure] ,
			[CMD_Cost4] AS [CMD_Cost4_Pure] ,
			[CMD_Cost5] AS [CMD_Cost5_Pure] ,            
            [CMD_Description],
            [CMD_HotKeyCode],
            [LKP_Caption],
            [LKP_Order],
            [LKP_CityCode] AS [GroupColor],
            [CMD_InitialCount],
            [CMD_LKP_RecGrpID],
            [CMD_LKP_Section], 
            (SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE ([LKP_ID] = [CMD_LKP_Section])) AS [CMD_Section],
            [CMD_LKP_Group]
        FROM @TempTableCommodity 
   LEFT JOIN (SELECT TCMD_ID_Product,SUM(TPrice) AS TPrice FROM @LastPriceTable GROUP BY TCMD_ID_Product) AS L ON [CMD_Id] = L.TCMD_ID_Product
   LEFT JOIN (SELECT TCMD_ID_Product,SUM(TBuyPrice) AS TBuyPrice FROM @FIFOPriceTable GROUP BY TCMD_ID_Product) AS F ON [CMD_Id] = F.TCMD_ID_Product 
   
   ORDER BY [CMD_LKP_Group] ASC



END
