SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[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 NOT IN ('10000','95100','95300','55001','55002','55003')
--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 NOT IN('10000','95100','95300','55001','55002','55003')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 NOT IN ('10000','95100','95300','55001','55002','55003')
--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 NOT IN ('10000','95100','95300','55001','55002','55003')
--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 NOT IN ('10000','95200','95300','55001','55002','55003')
--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 NOT IN ('10000','95200','95300','55001','55002','55003')
--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 NOT IN ('10000','95200','95300','55001','55002','55003')
--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 NOT IN ('10000','95200','95300','55001','55002','55003')
--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
GO