
ALTER PROCEDURE [dbo].[mnrPrcIntSalla]
                           @UpdateDate   DATETIME ='2025-01-23 '
AS	
	SET NOCOUNT ON

	--mnrVertexExport
	DECLARE	@bGrpMat             INT 
	DECLARE @bNewBranchGrpExp    INT 
						SELECT TOP 1
							@bGrpMat = vrtexport.bGrpMat,
							@bNewBranchGrpExp    = vrtexport.bNewBranchGrpExp  
						FROM 
						 mnrVertexExport vrtexport
--print @bGrpMat
  --#Grp
CREATE TABLE #Grp( 
					ID        UNIQUEIDENTIFIER, 
					Number    FLOAT NULL,
					ParentID  UNIQUEIDENTIFIER, 
					Code      NVARCHAR(512)       COLLATE ARABIC_CI_AI,
					Name      NVARCHAR(512)       COLLATE ARABIC_CI_AI,
				 )

	IF( @bGrpMat = 0)
	BEGIN
		CREATE TABLE #VertexGroup
					 (  
						Num               INT, --Identity(0,1),
						GrpID             UNIQUEIDENTIFIER, 
						CategoryName      NVARCHAR(512)       COLLATE ARABIC_CI_AI,

					 )
		 INSERT INTO #VertexGroup
			SELECT 
				vrtGrp.Number,
				vrtGrp.GrpID,
				vrtGrp.CategoryName

			FROM 
		     mnrVertexGroup vrtGrp

			 --select * from #VertexGroup
			
		IF(@bNewBranchGrpExp=0)
		 BEGIN
  		   INSERT INTO #Grp
			 SELECT DISTINCT
				grp.ID,
				grp.Number,
				grp.ParentID,
				grp.Code,
				grp.Name
			 FROM 
				 mnrVertexGroup vrtGrp
				 INNER JOIN mnrGrp grp  ON vrtGrp.GrpID= grp.ID
		 END
		ELSE
		 BEGIN
			DECLARE @CountVertxGrp      INT
			DECLARE @CounterI	        INT 
			DECLARE @VertexGrpID	    UNIQUEIDENTIFIER 
			DECLARE @Vertex_Grp_SyncDate DATETIME	   

			SET @CountVertxGrp     = (SELECT COUNT(GrpID) FROM #VertexGroup)
			SET @CounterI          = 0

			WHILE(@CounterI < @CountVertxGrp)
			BEGIN
	   
			   SELECT @VertexGrpID= GrpID  FROM #VertexGroup WHERE Num=@CounterI

			   INSERT INTO #Grp
				SELECT DISTINCT
					grp.ID,
					grp.Number,
					grp.ParentID,
					grp.Code,
					grp.Name
				FROM 
				  mnrSysFnGetGrpSortList (@VertexGrpID, 2) as grpV
				  INNER JOIN mnrGrp grp  ON grpV.ID= grp.ID


				  set @CounterI = @CounterI+1
			 END-- end of while 
			--select * from #Grp
		END--end of else be @bNewBranchGrpExp=1

		--  get grp parents 
			--select * from #Grp
			
			set @VertexGrpID	   = 0x0
			set @CountVertxGrp     = 0x0
			SET @CountVertxGrp     = (SELECT COUNT(GrpID) FROM mnrVertexGroup)
			SET @CounterI          = 0
			--print @CountVertxGrp
			WHILE(@CounterI < @CountVertxGrp)
			BEGIN
	   
			   SELECT @VertexGrpID= GrpID  FROM mnrVertexGroup WHERE Number=@CounterI

			   INSERT INTO #Grp
				SELECT DISTINCT
					grp.ID,
					grp.Number,
					grp.ParentID,
					grp.Code,
					grp.Name
				FROM 
				  mnrSysFnGetGrpParents (@VertexGrpID) as grpparent
				  INNER JOIN mnrGrp grp  ON grpparent.ID= grp.ID

			   set @CounterI = @CounterI+1
			    print @CounterI
				print @VertexGrpID
            END


	END --end of @bGrpMat=0
	ELSE --@bGrpMat=1
	BEGIN
	 INSERT INTO #Grp
		SELECT DISTINCT
			grp.ID,
			grp.Number,
			grp.ParentID,
			grp.Code,
			grp.Name
		FROM 
		    mnrGrp grp 
       ORDER BY grp.Code
	END

	
--SELECT * FROM #Grp

--#Store
CREATE TABLE #Store(
                    GrpID                   UNIQUEIDENTIFIER , 
					MatID                   UNIQUEIDENTIFIER ,
					StoreID                 UNIQUEIDENTIFIER ,
					MatStoreUpdatedAt       DATETIME NULL,
					Qty1					FLOAT NULL,
					Qty2					FLOAT NULL,
					Qty3					FLOAT NULL,
					Qty4					FLOAT NULL,
					Qty5					FLOAT NULL,
				   )

IF EXISTS (SELECT TOP 1 ID FROM mnrVertexStore)
BEGIN
  	INSERT INTO #Store
		SELECT 
		    gr.ID,
		    mtstore.MatID,
		    mtstore.StoreID,
			ISNULL(mtstore.UpdatedAt,'1980-01-01'),
			ISNULL(mtstore.Qty1,0),
			ISNULL(mtstore.Qty2,0),
			ISNULL(mtstore.Qty3,0),
			ISNULL(mtstore.Qty4,0),
			ISNULL(mtstore.Qty5,0)
    FROM  mnrVertexStore vstore  
	      INNER JOIN   mnrVertexExport  vrtexport ON vrtexport.ID= vstore.ParentID
	      INNER JOIN   mnrstore store             ON (vstore.StoreID= store.ParentID)OR(vstore.StoreID= store.ID)
         INNER JOIN   mnrmatstore  mtstore        ON mtstore.StoreID= store.ID
		 INNER JOIN mnrMaterial  mt  ON mt.ID = mtstore.MatID
	     INNER JOIN #Grp         gr  ON mt.ParentID =gr.ID
    WHERE
	   ISNULL(mtstore.Qty1,0)>=0 AND
	   ISNULL(mtstore.Qty2,0)>=0 AND
	   ISNULL(mtstore.Qty3,0)>=0 AND
	   ISNULL(mtstore.Qty4,0)>=0 AND
	   ISNULL(mtstore.Qty5,0)>=0 

	  --SELECT * FROM mnrVertexStore
	 --SELECT * FROM #Store 
  END
  ELSE
     BEGIN
	   	INSERT INTO #Store
		SELECT 
		    gr.ID,
		    mtstore.MatID,
			mtstore.ID,
			ISNULL(mtstore.UpdatedAt,'1980-01-01'),
			ISNULL(mtstore.Qty1,0),
			ISNULL(mtstore.Qty2,0),
			ISNULL(mtstore.Qty3,0),
			ISNULL(mtstore.Qty4,0),
			ISNULL(mtstore.Qty5,0)
		FROM 
		  mnrmatstore mtstore 
		  INNER JOIN mnrMaterial  mt  ON mt.ID = mtstore.MatID
	      INNER JOIN #Grp         gr  ON mt.ParentID =gr.ID
       WHERE
	   ISNULL(mtstore.Qty1,0)>=0 AND
	   ISNULL(mtstore.Qty2,0)>=0 AND
	   ISNULL(mtstore.Qty3,0)>=0 AND
	   ISNULL(mtstore.Qty4,0)>=0 AND
	   ISNULL(mtstore.Qty5,0)>=0 
END
  -- select * from #Store

  CREATE TABLE #QtyMat ( 
                    GrpID                   UNIQUEIDENTIFIER , 
					mtID                    UNIQUEIDENTIFIER,
	                MatCode                 NVARCHAR(512) COLLATE ARABIC_CI_AI,
					MatName                 NVARCHAR(512) COLLATE ARABIC_CI_AI,
					MatQty                  FLOAT NULL,
					MatStoreUpdatedAt       DATETIME NULL,
					)


	   INSERT INTO #QtyMat
	    SELECT
		        ms.GrpID,
                mt.ID,
				mt.Code,
				mt.name,
				CASE WHEN (ISNULL(mt.DefUnit,0) = 1)  THEN SUM(ms.Qty1)   ELSE
				CASE WHEN (ISNULL(mt.DefUnit,0) = 2)  THEN SUM(ms.Qty2)   ELSE 
				CASE WHEN (ISNULL(mt.DefUnit,0) = 3)  THEN SUM(ms.Qty3)   ELSE
				CASE WHEN (ISNULL(mt.DefUnit,0) = 4)  THEN SUM(ms.Qty4)   ELSE
				CASE WHEN (ISNULL(mt.DefUnit,0) = 5)  THEN SUM(ms.Qty5)   END END END END END,
				'1980-01-01'
       FROM 
	        #Store ms 
			 INNER JOIN mnrMaterial     mt  ON  ms.MatID  =  mt.ID
	   WHERE  ms.MatStoreUpdatedAt>= @UpdateDate
	   GROUP BY
	            ms.GrpID,
	            mt.ID,
	            mt.Code,
				mt.name,
                DefUnit


	SELECT DISTINCT  
	  *
	FROM #QtyMat  	-- where MatCode='6931940788533' 

-------------------------export price----------------------------
	


--Result   
 CREATE TABLE #Res ( 
					GrpID            UNIQUEIDENTIFIER, 
					GrpCode          NVARCHAR(512)       COLLATE ARABIC_CI_AI, 
					GrpName          NVARCHAR(512)       COLLATE ARABIC_CI_AI, 
					mtID             UNIQUEIDENTIFIER ,
					mtNumber         FLOAT NULL,
					mtUpdatedAt      DATETIME NULL,
					mtCode          NVARCHAR(512)       COLLATE ARABIC_CI_AI NULL ,
				    mtName          NVARCHAR(512)       COLLATE ARABIC_CI_AI NULL ,
					mtDefUnit       INT NULL ,
					mtUnit2Rate     FLOAT NULL ,
					mtUnit3Rate     FLOAT NULL ,
					mtUnit4Rate     FLOAT NULL ,
					mtUnit5Rate     FLOAT NULL ,
					mtLastSellPrice  FLOAT NULL ,
					mtLastPurchasePrice FLOAT NULL ,
					mtAvgCostPrice  FLOAT NULL ,
					mtWhole1   FLOAT NULL ,
					mtRetail1  FLOAT NULL ,
					mtExport1  FLOAT NULL ,
					mtImport1  FLOAT NULL ,
					mtDealer1  FLOAT NULL ,
					mtGeneral1 FLOAT NULL ,
					mtVendor1  FLOAT NULL ,
					mtEndUser1 FLOAT NULL ,
					mtWhole2   FLOAT NULL ,
					mtRetail2  FLOAT NULL ,
					mtExport2  FLOAT NULL ,
					mtImport2  FLOAT NULL ,
					mtDealer2  FLOAT NULL ,
					mtGeneral2 FLOAT NULL ,
					mtVendor2  FLOAT NULL ,
					mtEndUser2 FLOAT NULL ,
					mtWhole3   FLOAT NULL ,
					mtRetail3  FLOAT NULL ,
					mtExport3  FLOAT NULL ,
					mtImport3  FLOAT NULL ,
					mtDealer3  FLOAT NULL ,
					mtGeneral3 FLOAT NULL ,
					mtVendor3  FLOAT NULL ,
					mtEndUser3   FLOAT NULL ,
					mtWhole4     FLOAT NULL ,
					mtRetail4    FLOAT NULL ,
					mtExport4    FLOAT NULL ,
					mtImport4    FLOAT NULL ,
					mtDealer4    FLOAT NULL ,
					mtGeneral4   FLOAT NULL ,
					mtVendor4    FLOAT NULL ,
					mtEndUser4   FLOAT NULL ,
					mtWhole5     FLOAT NULL ,
					mtRetail5    FLOAT NULL ,
					mtExport5    FLOAT NULL ,
					mtImport5    FLOAT NULL ,
					mtDealer5    FLOAT NULL ,
					mtGeneral5   FLOAT NULL ,
					mtVendor5    FLOAT NULL ,
					mtEndUser5   FLOAT NULL 
					)
    INSERT INTO #Res
	  SELECT  DISTINCT
	  	  		gr.ID   ,
				gr.Code,
				gr.Name,
				mt.ID    ,
				mt.Number,
				ISNULL(mt.UpdatedAt,'1980-01-01'),
				mt.Code,
				mt.Name,
			    mt.DefUnit    ,
			    mt.Unit2Rate  ,
				mt.Unit3Rate  ,
				mt.Unit4Rate  ,
				mt.Unit5Rate  ,
				ISNULL(mt.LastSellPrice ,0) ,
				ISNULL(mt.LastPurchasePrice ,0) ,
				ISNULL(mt.AvgCostPrice ,0) ,
				ISNULL(mt.Whole1      ,0) ,
				ISNULL(mt.Retail1    ,0) ,
				ISNULL(mt.Export1    ,0) ,
				ISNULL(mt.Import1    ,0) ,
				ISNULL(mt.Dealer1    ,0) ,
				ISNULL(mt.General1   ,0) ,
				ISNULL(mt.Vendor1    ,0) ,
				ISNULL(mt.EndUser1    ,0) ,
				ISNULL(mt.Whole2      ,0) ,
				ISNULL(mt.Retail2     ,0) ,
				ISNULL(mt.Export2     ,0) ,
				ISNULL(mt.Import2    ,0) ,
				ISNULL(mt.Dealer2    ,0) ,
				ISNULL(mt.General2    ,0) ,
				ISNULL(mt.Vendor2    ,0) ,
				ISNULL(mt.EndUser2   ,0) ,
				ISNULL(mt.Whole3      ,0) ,
				ISNULL(mt.Retail3     ,0) ,
				ISNULL(mt.Export3     ,0) ,
				ISNULL(mt.Import3     ,0) ,
				ISNULL(mt.Dealer3    ,0) ,
				ISNULL(mt.General3   ,0) ,
				ISNULL(mt.Vendor3     ,0) ,
				ISNULL(mt.EndUser3    ,0) ,
				ISNULL(mt.Whole4      ,0) ,
				ISNULL(mt.Retail4    ,0) ,
				ISNULL(mt.Export4    ,0) ,
				ISNULL(mt.Import4    ,0) ,
				ISNULL(mt.Dealer4    ,0) ,
				ISNULL(mt.General4   ,0) ,
				ISNULL(mt.Vendor4     ,0) ,
				ISNULL(mt.EndUser4    ,0) ,
				ISNULL(mt.Whole5 ,0) ,
				ISNULL(mt.Retail5 ,0)  ,
				ISNULL(mt.Export5     ,0)  ,
				ISNULL(mt.Import5    ,0)  ,
				ISNULL(mt.Dealer5 ,0)  ,
				ISNULL(mt.General5   ,0)  ,
				ISNULL(mt.Vendor5    ,0)  ,
				ISNULL(mt.EndUser5 ,0)  
				
	   FROM 
	   	     #Store ms 
			 INNER JOIN mnrMaterial     mt  ON  ms.MatID  =  mt.ID  
	         INNER JOIN  #Grp  gr           ON mt.ParentID = gr.ID
	   where ISNULL(mt.UpdatedAt,'1980-01-01')>=@UpdateDate
	 
          


select * from #Res 
ORDER BY
			   GrpCode,
			   mtNumber

--#END#