CREATE PROCEDURE [dbo].[spCalcNetPurchase]
@StartDate VARCHAR(10) ,
@EndDate VARCHAR(10) 
AS 

DECLARE @Start_Date SMALLDATETIME
DECLARE @End_Date SMALLDATETIME

SET @Start_Date = dbo.ShamsiToMiladi(@StartDate)
SET @End_Date = dbo.ShamsiToMiladi(@EndDate)

DECLARE @TCode VARCHAR(20)
SELECT  @TCode = Acc_Code
FROM    classify
WHERE   ( classify.AutoId = 31 )
PRINT @TCode

SELECT  Customer.Name ,
        Customer.Guy_Code ,
        Khales_Forosh.Tot_Price_forosh ,
        Khales_Forosh.Tot_Price_Bargasht ,
        Khales_Kharid
FROM    ( SELECT    SUM(SalesRet.Tot_Price_forosh) Tot_Price_forosh ,
                    SUM(SalesRet.tot_Price_Kharid) Tot_Price_Bargasht ,
                    SUM(SalesRet.Tot_Price_forosh - SalesRet.tot_Price_Kharid) AS Khales_Kharid ,
                    SalesRet.Guy_Code
          FROM      ( SELECT    Tot_Price_forosh = ISNULL(CASE WHEN Kind_Flag IN ('7', '5', '9' ) THEN (dbo.headH.Tot_Price_forosh) END, 0) ,
                                tot_Price_Kharid = ISNULL(CASE WHEN Kind_Flag IN ('0', '6', '8' ) THEN (dbo.headh.Tot_Price_Kharid) END, 0) ,
                                Guy_Code = CASE WHEN Kind_Flag IN ( '7', '5','9' ) THEN Reciver
                                                WHEN Kind_Flag IN ( '0', '6','8' ) THEN Sender
                                           END
                      FROM      headH
                                INNER JOIN Havaleh ON Havaleh.Id_Havaleh = headH.Id_Havaleh
                      WHERE     headH.Tarikh_faktor BETWEEN @Start_Date AND @End_Date                               
                                AND  dbo.Havaleh.Sender <> '10000' AND dbo.Havaleh.Reciver <> '10000'
                                AND Kind_Flag IN ( '7', '5', '9', '0', '6','8' )
                      GROUP BY havaleh.Id_Havaleh ,  CASE WHEN Kind_Flag IN ( '7', '5', '9' )
                                     THEN Reciver
                                     WHEN Kind_Flag IN ( '0', '6', '8' )
                                     THEN Sender 
                                END,Kind_Flag , Tot_Price_forosh , Tot_Price_Kharid
                    ) AS SalesRet
          GROUP BY  SalesRet.Guy_Code
        ) AS Khales_Forosh
        INNER JOIN ( SELECT Acc_Name AS Name ,
                            SUBSTRING(Acc_Code, LEN(@TCode) + 1, LEN(Acc_Code)) AS Guy_Code
                     FROM   Account
                     WHERE  Acc_Code LIKE @TCode + '%'
                            AND ( ROUND(Account.Bestan, 0)- ROUND(Account.Bedeh, 0) ) > 0
                            AND Acc_Code <> @TCode
                   ) AS Customer ON Khales_Forosh.Guy_Code = Customer.Guy_Code
WHERE  (Tot_Price_forosh > 0 OR Tot_Price_Bargasht > 0  )
ORDER BY Customer.Guy_Code