Declare                                       
@UserCode VARCHAR(8000)=NULL,                                          
@SalesOrg VARCHAR(100) = '',                                          
@RouteCode VARCHAR(8000) = '',                                          
@Month int ='2',                                          
@Year int = '2020',                                          
@SortExpression VARCHAR(100) ='SalesmanName ASC',                                          
@StartRowIndex int = 0,                                          
@MaximumRows int = 10000 ,                                  
@Locations VARCHAR(8000)='',                                  
@Divisions VARCHAR(8000)='' 

BEGIN                                          
SET NOCOUNT ON                                           
DECLARE @SQLStr NVARCHAR(max)                                          
                                    
IF(@SalesOrg IS NULL)                                            
SET @SalesOrg=''                                            
                                            
IF(@UserCode IS NULL)                                            
SET @UserCode='admin'                                            
                                            
IF(@RouteCode IS NULL)                                          
SET @RouteCode=''                                          
 /*                             
SET @Enddate=  DATEADD(D,1,@EndDate)                                        
Set @Startdate = DATEADD(dd,-2,@EndDate)  */        
If(ISNULL(@UserCode,'')='')          
Set @UserCode='admin'          
IF (@Locations IS NULL)                        
SET @Locations='' 


 SELECT A.Salesmancode,A.SalesmanName ,A.RouteCode,CAST(TotalAmount AS DECIMAL(10,2))TotalAmount,PaymentMode,PaidAmt
    FROM  
    (                                          
    SELECT       
    Code AS Salesmancode,RouteCode,UserName AS SalesmanName       
    FROM dbo.tblUser A0  Where USerType In ('VanSales','PReSales')                                          
    )A 
	CROSS APPLY                                          
    (      
    SELECT U.RouteCode,B.UserCode,SUM(TotalAmount+TotalTAXAmount-TotalDiscountAmount) TotalAmount                                          
    FROM dbo.tblTrxHeader(NOLOCK) B
	INNER JOIn tblUser U On U.Code=B.UserCode            
    INNER JOIN tblRoute C ON U.RouteCode=C.Code                            
    WHERE  B.trxtype = 1 AND B.UserCode = A.Salesmancode AND (U.RouteCode IN (SELECT Items FROM dbo.Split(@RouteCode,',')) OR @RouteCode='')AND                      
     Month(B.TrxDate)=Cast(@Month as int) AND Year(B.TrxDate)=Cast(@Year as int)        
     AND (C.AreaCode IN (select Items from dbo.Split(@Locations,',')) OR @Locations='')         
     AND (B.OrgCode IN (select Items from dbo.Split(@SalesOrg,',')) OR @SalesOrg='')        
    GROUP BY B.UserCode,U.RouteCode                                          
    )B  
	OUTER APPLY                                          
    (                                          
    SELECT PaymentMode,SUM(Amount) AS PaidAmt       
    FROM Dbo.tblPaymentDetail(NOLOCK)       
    WHERE Receipt_Number IN           
     (      
     SELECT DISTINCT F.Receipt_Number                                        
     FROM tblPaymentHeader PH INNER JOIN tblPaymentInvoice(NOLOCK) f ON F.Receipt_Number=PH.Receipt_Number          
     Inner Join tblTrxHeader(NOLOCK) H On H.TrxCode = F.Invoice_Number  AND H.trxtype = 1          
      AND H.UserCode = A.Salesmancode AND H.RouteCode=B.RouteCode AND          
      Month(PH.ReceiptDate)=Cast(@Month as int) AND Year(PH.ReceiptDate)=Cast(@Year as int)        
     )                                          
    GROUP BY PaymentMode                                   
    )C  


END