
Create PROCEDURE [dbo].[Category_SalesClassification] @WarehouseCode Smallint, @GoodsLevel INT,
 @NumberOfLevel smallint, @StartDate VARCHAR(10), @EndDate VARCHAR(10), @ComputerName VARCHAR(100),  
 @ReportType TINYINT,@StartTime Varchar(5),@EndTime varchar(5)
AS 

DECLARE @_StartDate SMALLDATETIME = dbo.ShamsiToMiladi(@StartDate)
DECLARE @_EndDate SMALLDATETIME = dbo.ShamsiToMiladi(@EndDate)


IF NOT EXISTS (SELECT * FROM [tempdb].dbo.sysobjects WHERE Name = '##SalesClassification') 
BEGIN             
    CREATE Table ##SalesClassification
	(
	WareHouseCode VARCHAR(3) COLLATE Arabic_CI_AS ,
    GoodsCode VARCHAR(15) COLLATE Arabic_CI_AS ,
	GoodsName NVARCHAR(50) COLLATE Arabic_CI_AS,
    TotQty MONEY,
    BuyPrice MONEY,
    TotBuyPrice MONEY,
    SalesPrice MONEY,
    TotSalesPrice MONEY,
    ClassificationName NVARCHAR(30), 
    FirstlevelName NVARCHAR(30), 
    TwolevelName NVARCHAR(30), 
    TreelevelName NVARCHAR(30),
   ForLevelName Nvarchar(30),
	ComputerName VARCHAR(100)
	)
	
END 
ELSE 
  DELETE FROM ##SalesClassification WHERE ComputerName = @ComputerName

INSERT INTO ##SalesClassification
(
    WareHouseCode,
    GoodsCode,
    GoodsName,
    TotQty,
    BuyPrice,
    TotBuyPrice,
    SalesPrice,
    TotSalesPrice,
    ClassificationName,
    FirstlevelName,
    TwolevelName,
    TreelevelName,
	ForLevelName,
	ComputerName
)
SELECT WareHouseCode,
       GoodsCode,
       GoodsName,
       SUM(TotQty) TotQty,
	   CASE
         WHEN SUM(TotQty)  = 0 THEN 0
	     ELSE ABS(SUM(TotBuyPrice))/ SUM(TotQty)  
	   END AS BuyPrice,
	   CASE
         WHEN SUM(TotQty)  = 0 THEN 0
		 ELSE ABS(SUM(TotBuyPrice))
       END AS  TotBuyPrice,
	   CASE
         WHEN SUM(TotQty) = 0 THEN 0
		 ELSE SUM(TotSalesPrice)/ SUM(TotQty)
	   END AS  SalesPrice,
	   CASE
         WHEN SUM(TotQty) = 0 THEN 0
		 ELSE SUM(TotSalesPrice) 
	   END AS TotSalesPrice,
       ClassificationName, FirstlevelName, TwolevelName,TreelevelName, ForLevelName ,@ComputerName
FROM
(
    SELECT CASE
               WHEN Kind_Flag IN ( 5, 7, 9 ) THEN
                   Sender
               WHEN Kind_Flag IN ( 0, 6, 8 ) THEN
                   Reciver
           END AS WareHouseCode,
           Havaleh.K_Code GoodsCode,
           Name1 GoodsName,
           CASE
               WHEN Kind_Flag IN ( 5, 7, 9 ) THEN SUM(K_Qty1)
               WHEN Kind_Flag IN ( 0, 6, 8 ) THEN -SUM(K_Qty1)
           END AS TotQty,
           CASE
               WHEN Kind_Flag IN ( 5, 7, 9 ) THEN SUM(K_Qty1 * Price_KHarid)
               WHEN Kind_Flag IN ( 0, 6, 8 ) THEN -SUM(K_Qty1 * Price_KHarid)
           END AS TotBuyPrice,
           CASE
               WHEN Kind_Flag IN ( 5, 7, 9 ) THEN SUM(K_Qty1 * Price_Forosh)
               WHEN Kind_Flag IN ( 0, 6, 8 ) THEN -SUM(K_Qty1 * Price_Forosh)
           END AS TotSalesPrice,
		   dbo.Level_Header.Name ClassificationName,
		   dbo.Level_Detail1.Name FirstlevelName,
		   dbo.Level_Detail2.Name TwolevelName,
		   dbo.Level_Detail3.Name TreelevelName,
		   dbo.level_detail4.Name ForLevelName
    FROM KalaId WITH (NOLOCK)
        INNER JOIN Havaleh WITH (NOLOCK)
            ON KalaId.K_Code = Havaleh.K_Code
        INNER JOIN headH WITH (NOLOCK)
            ON Havaleh.Id_Havaleh = headH.Id_Havaleh
        INNER JOIN Level_Link
            ON Havaleh.K_Code = Level_Link.K_Code
        LEFT OUTER JOIN Level_Header
            ON Level_Link.L_Code = Level_Header.L_Code
        LEFT OUTER JOIN Level_Detail1
            ON Level_Link.L_Code1 = Level_Detail1.Ch_Code
        LEFT OUTER JOIN Level_Detail2
            ON Level_Link.L_Code2 = Level_Detail2.Ch_Code
        LEFT OUTER JOIN Level_Detail3
            ON Level_Link.L_Code3 = Level_Detail3.Ch_Code
        Left OUter JOIN Level_Detail4
             ON Level_Link.L_Code4 = Level_Detail4.Ch_Code
    WHERE Kind_Flag IN ( 0, 5, 6, 7, 8, 9 )
          AND Tarikh_faktor >= @_StartDate
          AND Tarikh_faktor <= @_EndDate
		  AND ((Convert(Varchar(10), Headh.Tarikh, 108) )>=@StartTime+':00' 
		  AND (Convert(Varchar(10), Headh.Tarikh, 108) )<=@EndTime+':00'  )
		  AND (Sender  = (CASE 
		                    WHEN Kind_Flag IN(5,7,9) AND @WarehouseCode <> 0 THEN @WarehouseCode   
		                    WHEN Kind_Flag IN(5,7,9) AND @WarehouseCode = 0  THEN Sender
						    ELSE 0 
						  END) OR
		       Reciver = (
                           CASE 
		                     WHEN Kind_Flag IN(0,6,8) AND @WarehouseCode <> 0 THEN @WarehouseCode   
		                     WHEN Kind_Flag IN(0,6,8) AND @WarehouseCode = 0  THEN Reciver
						     ELSE 0 
						   END
						 ))
          AND Level_Header.L_Code = CASE WHEN @GoodsLevel <>  0 THEN @GoodsLevel ELSE Level_Header.L_Code END 
    GROUP BY Havaleh.K_Code,
             Name1,
             Kind_Flag,
             Sender,
             Reciver,Level_Header.Name,Level_Detail1.Name,Level_Detail2.Name,Level_Detail3.Name,Level_Detail4.Name
) AS T
GROUP BY GoodsCode, GoodsName,WareHouseCode,ClassificationName, FirstlevelName, TwolevelName,TreelevelName,ForLevelName

IF @ReportType = 0
BEGIN
  IF @NumberOfLevel = 0 
  BEGIN
    SELECT WareHouseCode, '' AS GoodsCode, '' AS GoodsName,
           SUM(TotQty) TotQty, 
	       SUM(BuyPrice) BuyPrice, 
           SUM(TotBuyPrice) TotBuyPrice, 
	       SUM(SalesPrice) SalesPrice,
	       SUM(TotSalesPrice) TotSalesPrice,ClassificationName, ''  FirstlevelName, '' TwolevelName,'' TreelevelName ,'' ForLevelName	       
		   FROM (SELECT * FROM ##SalesClassification WHERE ComputerName = @ComputerName)   
	       AS T GROUP BY WareHouseCode, ClassificationName
  END  
  ELSE IF @NumberOfLevel = 1 
  BEGIN
    SELECT WareHouseCode, '' AS GoodsCode, '' AS GoodsName,
           SUM(TotQty) TotQty, 
	       SUM(BuyPrice) BuyPrice, 
           SUM(TotBuyPrice) TotBuyPrice, 
	       SUM(SalesPrice) SalesPrice,
	       SUM(TotSalesPrice) TotSalesPrice,ClassificationName ,FirstlevelName,'' TwolevelName,'' TreelevelName,'' ForLevelName	       
		   FROM (SELECT * FROM ##SalesClassification WHERE ComputerName = @ComputerName)   
	       AS T GROUP BY WareHouseCode, ClassificationName ,FirstlevelName
  END  
  ELSE IF @NumberOfLevel = 12 
  BEGIN
    SELECT WareHouseCode, '' AS GoodsCode, '' AS GoodsName,
           SUM(TotQty) TotQty, 
	       SUM(BuyPrice) BuyPrice, 
           SUM(TotBuyPrice) TotBuyPrice, 
	       SUM(SalesPrice) SalesPrice,
	       SUM(TotSalesPrice) TotSalesPrice,ClassificationName ,FirstlevelName, TwolevelName,'' TreelevelName ,'' ForLevelName	       
		   FROM (SELECT * FROM ##SalesClassification WHERE ComputerName = @ComputerName)   
	       AS T GROUP BY WareHouseCode, ClassificationName ,FirstlevelName,TwolevelName
  END
  ELSE IF @NumberOfLevel = 123
  BEGIN
    SELECT WareHouseCode, '' AS GoodsCode, '' AS GoodsName,
           SUM(TotQty) TotQty, 
	       SUM(BuyPrice) BuyPrice, 
           SUM(TotBuyPrice) TotBuyPrice, 
	       SUM(SalesPrice) SalesPrice,
	       SUM(TotSalesPrice) TotSalesPrice,ClassificationName ,FirstlevelName, TwolevelName, TreelevelName,'' ForLevelName	       
		   FROM (SELECT * FROM ##SalesClassification WHERE ComputerName = @ComputerName)   
	       AS T GROUP BY WareHouseCode, ClassificationName ,FirstlevelName,TwolevelName,TreelevelName
  END  
  ELSE IF @NumberOfLevel = 1234
  BEGIN
    SELECT WareHouseCode, '' AS GoodsCode, '' AS GoodsName,
           SUM(TotQty) TotQty, 
	       SUM(BuyPrice) BuyPrice, 
           SUM(TotBuyPrice) TotBuyPrice, 
	       SUM(SalesPrice) SalesPrice,
	       SUM(TotSalesPrice) TotSalesPrice,ClassificationName ,FirstlevelName, TwolevelName, TreelevelName,ForLevelName	       
		   FROM (SELECT * FROM ##SalesClassification WHERE ComputerName = @ComputerName)   
	       AS T GROUP BY WareHouseCode, ClassificationName ,FirstlevelName,TwolevelName,TreelevelName,ForLevelName
  END  
END 
ELSE
BEGIN
  IF @NumberOfLevel = 0 
  BEGIN 
	SELECT WareHouseCode, GoodsCode, GoodsName, TotQty, BuyPrice, TotBuyPrice, SalesPrice, TotSalesPrice, 
           ClassificationName,''  FirstlevelName, '' TwolevelName,'' TreelevelName,'' ForLevelName FROM ##SalesClassification WHERE ComputerName = @ComputerName
		   GROUP BY WareHouseCode, GoodsCode, GoodsName, TotQty, BuyPrice, TotBuyPrice, SalesPrice, TotSalesPrice, 
           ClassificationName
  END 

  ELSE IF @NumberOfLevel = 1 
  BEGIN 
    SELECT WareHouseCode, GoodsCode, GoodsName, TotQty, BuyPrice, TotBuyPrice, SalesPrice, TotSalesPrice, 
           ClassificationName, FirstlevelName, '' TwolevelName,'' TreelevelName,'' ForLevelName FROM ##SalesClassification WHERE ComputerName = @ComputerName
		   GROUP BY WareHouseCode, GoodsCode, GoodsName, TotQty, BuyPrice, TotBuyPrice, SalesPrice, TotSalesPrice, 
           ClassificationName, FirstlevelName
  END 
  ELSE IF @NumberOfLevel = 12
  BEGIN 
    SELECT WareHouseCode, GoodsCode, GoodsName, TotQty, BuyPrice, TotBuyPrice, SalesPrice, TotSalesPrice, 
           ClassificationName, FirstlevelName, TwolevelName,'' TreelevelName,'' ForLevelName FROM ##SalesClassification WHERE ComputerName = @ComputerName
		   GROUP BY WareHouseCode, GoodsCode, GoodsName, TotQty, BuyPrice, TotBuyPrice, SalesPrice, TotSalesPrice, 
           ClassificationName, FirstlevelName, TwolevelName
  END 
  ELSE IF @NumberOfLevel = 123
  BEGIN 
    SELECT WareHouseCode, GoodsCode, GoodsName, TotQty, BuyPrice, TotBuyPrice, SalesPrice, TotSalesPrice, 
           ClassificationName, FirstlevelName, TwolevelName,TreelevelName,'' ForLevelName FROM ##SalesClassification WHERE ComputerName = @ComputerName
  END 
  ELSE IF @NumberOfLevel = 1234
  BEGIN 
    SELECT WareHouseCode, GoodsCode, GoodsName, TotQty, BuyPrice, TotBuyPrice, SalesPrice, TotSalesPrice, 
           ClassificationName, FirstlevelName, TwolevelName,TreelevelName,ForLevelName FROM ##SalesClassification WHERE ComputerName = @ComputerName
  END 
END 
