Create procedure spDaybook
(
@BranchName nvarchar(50),
@fromdate nvarchar(20),
@todate nvarchar(20)
)
As
begin
--delete day book
delete from TblDayBook
--save cash sales
insert into tblDayBook (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 tblDayBook (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<>'10000' and accode<>'95100' and accode<>'95300'
--save bank sales
insert into tblDayBook (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<>'10000' and accode<>'95100' and accode<>'95300'AND BILLTYPE<>'Credit'
--cash sales return
insert into tblDayBook (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 tblDayBook (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<>'10000' and accode<>'95100' and accode<>'95300'
--bank sales return
insert into tblDayBook (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<>'10000' and accode<>'95100' and accode<>'95300'
--cash purchase
insert into tblDayBook (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 tblDayBook (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<>'10000'  and accode<>'95200' and accode<>'95300'
--bank purchase
insert into tblDayBook (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<>'10000' and accode<>'95200' and accode<>'95300'
--cash purchase return
insert into tblDayBook (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 tblDayBook (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<>'10000'  and accode<>'95200' and accode<>'95300'
--bank purchase return
insert into tblDayBook (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<>'10000'  and accode<>'95200' and accode<>'95300'
--customer receipt
insert into tblDayBook (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 tblDayBook (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 tblDayBook (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 tblDayBook (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 tblDayBook (Expense) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate AND (@BranchName = 'All' OR BranchName = @BranchName)  and type='Cashentry'  and billtype='Cash' and accode='10000'
--bank expense
insert into tblDayBook (BankExpense) select isnull(sum(isnull(Amount,0)),0) from tblaccounting Where EntryDate between @fromdate and @todate  AND (@BranchName = 'All' OR BranchName = @BranchName) and type='Cashentry'  and billtype='Bank' and accode<>'10000' AND accode in (select code from accounts where type='Asset' and acgroup='BANK')
--journel
insert into tblDayBook (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 tblDayBook (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         dbo.TblDayBook
end