GO
/****** Object:  StoredProcedure [dbo].[sp_CustSummary]    Script Date: 25-03-2025 17:03:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CustSummary]                
@ToDate datetime ,
@CustomerCode varchar(100),
@BranchCode varchar(100),
@Details int
AS              
begin 

if @Details=0
begin
select accode,name,sum(Opening) as Opening,round(sum(PENDING_AMOUNT),2)as PENDING_AMOUNT  from (
SELECT (CASE ISNULL(tblaccounting.AcCode,'') WHEN '' THEN Customer.Code ELSE tblaccounting.AcCode END ) AS AcCode,Customer.Name,    
(-1)*ISNULL(sum(ISNULL(tblaccounting.Amount,0)),0) as PENDING_AMOUNT,0 AS Opening
FROM tblaccounting RIGHT JOIN Customer ON Customer.Code=tblaccounting.AcCode  AND EntryDate<=@ToDate     
 
where (@BranchCode IS NULL OR @BranchCode = ''  OR ISNULL(tblaccounting.BranchCode, '') = @BranchCode)
and  (@CustomerCode IS NULL OR @CustomerCode = '' OR ISNULL(Customer.Code, '') = @CustomerCode)
GROUP BY tblaccounting.AcCode,Customer.Name,Customer.DbCr,Customer.Opening ,Customer.Code           
HAVING( SUM(tblaccounting.Amount)<>0 )      
union all
SELECT customerCode as AcCode,customername as name,0 as PENDING_AMOUNT,  ISNULL(SUM(BALANCE), 0) as opening FROM CustomerOPEntryUpdated  
where (@BranchCode IS NULL OR @BranchCode = ''  OR ISNULL(BranchCode, '') = @BranchCode)
and  (@CustomerCode IS NULL OR @CustomerCode = '' OR ISNULL(CustomerCode, '') = @CustomerCode)
GROUP BY customerCode,CustomerName
)a group by a.accode,a.name
ORDER BY a.AcCOde      
  end
  else if @Details=1
  begin


  SELECT AcCode, Name,sum(Opening) as Opening,round(sum(PENDING_AMOUNT),2)as PENDING_AMOUNT  from(
  SELECT customer.Code as AcCode, Customer.Name,0 AS Opening,
  (-1)*isnull(tblaccounting.Amount, 0) as PENDING_AMOUNT FROM tblaccounting right JOIN Customer ON Customer.Code = tblaccounting.Accode
                    and CONVERT(VARCHAR(25),tblaccounting.EntryDate,112)  <= @ToDate where (@BranchCode IS NULL OR @BranchCode = ''  OR ISNULL(tblaccounting.BranchCode, '') = @BranchCode)
and  (@CustomerCode IS NULL OR @CustomerCode = '' OR ISNULL(Customer.Code, '') = @CustomerCode)
                

union all
SELECT customerCode as AcCode,customername as Name, ISNULL(SUM(BALANCE), 0) as opening,0 as PENDING_AMOUNT FROM CustomerOPEntryUpdated  
where (@BranchCode IS NULL OR @BranchCode = ''  OR ISNULL(BranchCode, '') = @BranchCode)
and  (@CustomerCode IS NULL OR @CustomerCode = '' OR ISNULL(CustomerCode, '') = @CustomerCode)
GROUP BY customerCode,CustomerName)
a group by a.accode,a.name


end

  
----SELECT (CASE ISNULL(GenLedger.AcCode,'') WHEN '' THEN Customer.Code     
----  ELSE GenLedger.AcCode END ) AS AcCode,Customer.Name,    
------(CASE ISNULL(SUM(GenLedger.Amount),0) WHEN 0 THEN (CASE Customer.DbCr WHEN 'Credit' THEN -Opening WHEN 'Debit' THEN Opening) ELSE SUM(GenLedger.Amount) END) AS PENDING_AMOUNT,    
----sum(Genledger.Amount) as PENDING_AMOUNT,    
------SUM(GenLedger.Amount) as Pending,    
----(CASE Customer.DbCr WHEN 'Credit' THEN -Opening WHEN 'Debit' THEN Opening END) AS Opening FROM GenLedger            
----RIGHT JOIN Customer ON Customer.Code=GenLedger.AcCode  AND EntryDate<=@ToDate AND    
----(GenLEdger.Type='Sales' OR GenLedger.Type='CustRec'OR GenLedger.Type='SalesReturns'         
----OR GenLedger.Type='CreditNote'  OR GenLedger.Type='JournalEntry')     
----GROUP BY GenLedger.AcCode,Customer.Name,Customer.DbCr,Customer.Opening ,Customer.Code           
----HAVING( SUM(GenLedger.Amount)<>0 ) OR ( SUM(Customer.Opening)<>0 )       
----ORDER BY GenLedger.AcCOde      
END