SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   Proc [dbo].[spDaybook]
(
@BranchName nvarchar(50),
@fromdate nvarchar(20),
@todate nvarchar(20)
)
As
begin

 DECLARE @DayBookData TABLE(
[CashSales] [float] ,
	[CreditSales] [float] ,
	[CashSalesReturn] [float] ,
	[CreditSalesReturn] [float] ,
	[CashPurchase] [float] ,
	[CreditPurchase] [float] ,
	[CashPurchaseReturn] [float] ,
	[CreditPurchaseReturn] [float] ,
	[CustRecpt] [float] ,
	[SuppPay] [float] ,
	[Expense] [float] ,
	[BankSales] [float] ,
	[BankPurchase] [float] ,
	[BankPurchaseRet] [float] ,
	[BankSalesRet] [float] ,
	[BankCustRec] [float] ,
	[BankSuppPay] [float] ,
	[BankExpense] [float] ,
	[journal] [float] ,
	[CashDeposit] [float] 
	)
--delete day book
--delete from TblDayBook
--save cash sales
insert into @DayBookData (CashSales) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where entryDate between @fromdate and @todate  AND (@BranchName = 'All' OR BranchName = @BranchName) and   type='Sales' and Billtype='Cash' and accode='10000'
--save credit sales
insert into @DayBookData (CreditSales) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where entryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and   type='Sales' and Billtype='Credit' and accode not in ('10000','95100','95300','55001','55002','55003')
--save bank sales
insert into @DayBookData (BankSales) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where entryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and   type='Sales'   and accode not in ('10000','95100','95300','55001','55002','55003')AND BILLTYPE<>'Credit'

--cash sales return
insert into @DayBookData (CashSalesReturn) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Return' and Billtype='Cash'  and accode='10000'
--credti sales return
insert into @DayBookData (CreditSalesReturn) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Return' and Billtype='Credit'  and accode not in ('10000','95100','95300','55001','55002','55003')
--bank sales return
insert into @DayBookData (BankSalesRet) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Return' and (BillType='Bank'or BillType='Span'or BillType='CreditCard')  and accode NOT IN ('10000','95100','95300','55001','55002','55003')
--cash purchase
insert into @DayBookData (CashPurchase) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Purchase' and Billtype='Cash' and accode='10000'
--credti purchase
insert into @DayBookData (CreditPurchase) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Purchase' and Billtype='Credit'  and accode  NOT IN ('10000','95200','95300','55001','55002','55003')
--bank purchase
insert into @DayBookData (BankPurchase) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Purchase' and (BillType='Bank'or BillType='Span'or BillType='CreditCard')  and accode not in ('10000','95200','95300','55001','55002','55003')
--cash purchase return
insert into @DayBookData (CashPurchaseReturn) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate  and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Purchase Return' and Billtype='Cash'  and accode='10000'
--credit purchase return
insert into @DayBookData (CreditPurchaseReturn) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate  and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Purchase Return' and Billtype='Credit'  and accode not in ('10000','95200','95300','55001','55002','55003')
--bank purchase return
insert into @DayBookData (BankPurchaseRet) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Purchase Return' and (BillType='Bank'or BillType='Span'or BillType='CreditCard')  and accode not in ('10000','95200','95300','55001','55002','55003')
--customer receipt
insert into @DayBookData (CustRecpt) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='CustomerReceipt'   and (accode='10000' or accode='10002')
--Bank receipt
insert into @DayBookData (BankCustRec) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='CustomerReceipt'   and accode='10002'
--supplier payment
insert into @DayBookData (SuppPay) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='SupplierPayment'   and (accode='10000' OR accode='10003' Or  accode='10002')
--bank peyment
insert into @DayBookData (BankSuppPay) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName) and type='SupplierPayment'   and accode='10002'
--cash expense
insert into @DayBookData (Expense) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName)  and type='ExpenseEntry'  and billtype='Cash' and accode='10000'
--bank expense
insert into @DayBookData (BankExpense) select -1*isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate  AND (@BranchName = 'All' OR BranchName = @BranchName) and type='ExpenseEntry'  and billtype='Bank' and accode<>'10000' AND accode in (select code from accounts where type='Asset' and acgroup='BANK')
--journel
insert into @DayBookData (journal) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate  AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Journalentry' and accode='10000'
--cash deposit
insert into @DayBookData (CashDeposit) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate  AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Bankdeposit' and accode='10000' and billtype='Cash'

SELECT     SUM(CashSales) AS CashSales, SUM(CreditSales) AS CreditSales, SUM(CashSalesReturn) AS CashSalesReturn, SUM(CreditSalesReturn) 
                      AS CreditSalesReturn, SUM(CashPurchase) AS CashPurchase, SUM(CreditPurchase) AS CreditPurchase, SUM(CashPurchaseReturn) 
                      AS CashPurchaseReturn, SUM(CreditPurchaseReturn) AS CreditPurchaseReturn, SUM(CustRecpt) AS CustRecpt, SUM(SuppPay) AS SuppPay, 
                      SUM(Expense) AS Expense,SUM(BankSales) AS BankSales,
    SUM(BankPurchase) AS BankPurchase,
    SUM(BankPurchaseRet) AS BankPurchaseRet,
    SUM(BankSalesRet) AS BankSalesRet,
    SUM(BankCustRec) AS BankCustRec,
    SUM(BankSuppPay) AS BankSuppPay,
    SUM(BankExpense) AS BankExpense,
    SUM(journal) AS journal,
    SUM(CashDeposit) AS CashDeposit
FROM         @DayBookData
end