CREATE PROCEDURE [dbo].[GoodsWarehousesQuantity] @SystemName VARCHAR(MAX)
As
DECLARE @Fields Nvarchar(max)
Declare @SumTotFields Nvarchar(max)
Declare @SumFields Nvarchar(max)
Declare @StrCommand Nvarchar(max)
Declare @Parameter Nvarchar(500)

IF NOT EXISTS (SELECT * FROM [tempdb].[dbo].sysobjects WHERE Name ='##Warehouses')
CREATE Table ##Warehouses
(
    WarehouseCode VARCHAR(3) ,
	GoodsCode VARCHAR(20) ,
	Quantity MONEY,
    SystemName VARCHAR(MAX) 
)
ELSE 
DELETE FROM ##Warehouses WHERE SystemName = @SystemName

INSERT INTO ##Warehouses(WarehouseCode,GoodsCode, Quantity, SystemName)
SELECT A_Code , K_Code,  SUM(Mojodi), @SystemName
FROM Anbar WITH (NOLOCK)
Where Mojodi > 0
Group By A_Code, K_Code 

SET @StrCommand = 'SELECT @Fields = STUFF((SELECT '', '' + quotename(WarehouseCode)
                  FROM (SELECT WareHouseCode FROM ##Warehouses GROUP BY WareHouseCode) As T 
				  Order By WarehouseCode Desc
                  FOR XML PATH('''')), 1, 2, '''');' 
Set @Parameter = N'@Fields nvarchar(max) OUTPUT'
Exec Sp_ExecuteSql  @StrCommand, @Parameter, @Fields = @Fields OUTPUT

Set @StrCommand = 'SELECT @SumTotFields = STUFF((SELECT ''+ IsNull('' + quotename(WarehouseCode)+ '',0)'' 
                   FROM (SELECT WareHouseCode FROM ##Warehouses GROUP BY WareHouseCode) As T 
                  FOR XML PATH('''')), 1, 2, '''');' 
Set @Parameter = N'@SumTotFields nvarchar(max) OUTPUT'
Exec Sp_ExecuteSql  @StrCommand, @Parameter, @SumTotFields = @SumTotFields OUTPUT

Set @StrCommand = 'Select ' + @SumTotFields + '[کل موجودي] ,'  + @Fields + ', Name1 [نام کالا] , K_code [کد کالا],Name2 [نام دوم کالا]
                  From  (   
                         Select  WarehouseCode, kalaid.K_code, Name1, Name2, Quantity
						 From KalaId WITH (NOLOCK) Inner Join ##Warehouses  
						 on ##Warehouses.GoodsCode COLLATE Arabic_CI_AS= Kalaid.K_Code 
                        ) AS pivotTable 
                  pivot (Sum(Quantity) for WarehouseCode in (' + @Fields + ')) AS pvt Group By K_code, Name1,Name2,' + @Fields 
Set @Parameter = N'@Fields nvarchar(max) OUTPUT'
Exec Sp_ExecuteSql @StrCommand, @Parameter, @Fields = @Fields OUTPUT
