create or replace view view_revenue as 
SELECT fy.companyid, year(fy.startDate) as year, month(fy.startDate) as month , ifnull(sum(a.totalamount),0) as totalRevenue , 
ifnull((select sum(totalamount)  from iaccountdb.comp_sales_creditnote where companyID = a.companyID and  is_delete =0 and updateable =0 and year(creditnotedate) = year(a.salesInvoiceDate) and month(creditnotedate) = month(a.salesInvoiceDate) ),0) as creditnote, 
ifnull((select sum(totalamount)  from iaccountdb.comp_sales_debitnote where companyID = a.companyID and is_delete =0 and updateable =0 and year(debitnotedate) = year(a.salesInvoiceDate) and month(debitnotedate) = month(a.salesInvoiceDate) ),0) as debitnote,
ifnull((SELECT sum(amountcr) FROM iaccountdb.comp_budget 
WHERE year= fy.financialYear and period=fy.period and acctgroupid in (select ID from iaccountdb._ref_acct_group where acctTypeID = 3)),0) as budget


FROM iaccountdb.comp_financial_year fy left outer join iaccountdb.comp_sales_invoice a on fy.companyID = a.companyID and month(fy.startDate) = month(a.salesInvoiceDate) and year(fy.startDate) = year(a.salesInvoiceDate) and a.is_delete = 0 and a.updateable = 0

group by fy.companyid, fy.financialYear, fy.period ; 

create or replace view view_new_customer as 
SELECT year(a.startdate) as year, month(a.startdate) as month,
(select count(id) from iaccountdb.customer where year(createdts) = year(a.startdate) and month(createdts) = month(a.startdate)) as customer 
FROM iaccountdb.comp_financial_year a 
group by year(a.startdate), month(a.startdate);