SELECT (sum(a.amountdr)-sum(a.amountcr)) openingBalance
FROM (`view_comp_acc_det` a)
WHERE `companyID` =  '2'
AND `a`.`acctID` =  '2'
AND `a`.`year` < 2014
AND `a`.`year` >= '2015';



SELECT IFNULL((select debit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2015), 0) as oDebit, sum(b.amountdr) as currentyeardebit, IFNULL((select credit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2015), 0) as oCredit, sum(b.amountcr) as currentyearcredit, sum(b.total) as total, `a`.*, (select sum(c.amountcr) from `comp_budget` c where `c`.`acctID` = `a`.`ID` and c.year = 2015 and c.year = b.year and c.companyID = 2) as budgetAmount, `d`.`acctTypeID`
FROM (`comp_chart_of_acct` a)
LEFT OUTER JOIN `view_comp_acc_det` b ON `a`.`ID` = `b`.`acctID` and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) <= 201512 and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) >= 201501 and b.companyID = 2
JOIN `_ref_acct_group` d ON `d`.`ID` = `a`.`acctGroupID` and (d.acctTypeID = "3" OR d.acctTypeID = "4")
WHERE `a`.`companyID` =  '2'
GROUP BY `a`.`ID`
ORDER BY `a`.`acctCode`;

SELECT IFNULL(comp_opening_balance.debit , 0) as oDebit, sum(b.amountdr) as currentyeardebit, IFNULL(comp_opening_balance.credit , 0) as oCredit, sum(b.amountcr) as currentyearcredit, sum(b.total) as total, `a`.*,  sum(c.amountcr) ) as budgetAmount, `d`.`acctTypeID`
FROM (`comp_chart_of_acct` a)
LEFT OUTER JOIN `view_comp_acc_det` b ON `a`.`ID` = `b`.`acctID` and b.year=2015 and b.companyID = 2
left outer join comp_opening_balance on companyID = a.companyID and accountID = a.ID and year =2015
left outer join comp_budget c on `c`.`acctID` = `a`.`ID` and c.year = 2015 and c.companyID = a.companyid
JOIN `_ref_acct_group` d ON `d`.`ID` = `a`.`acctGroupID` and (d.acctTypeID = 3 OR d.acctTypeID = 4)
WHERE `a`.`companyID` =  2
GROUP BY `a`.`ID`
ORDER BY `a`.`acctCode`

//_ref_acct_group (ID,acctTypeID )

SELECT (sum(a.amountdr)-sum(a.amountcr)) openingBalance
FROM (`view_comp_acc_det` a)
WHERE `companyID` =  '2'
AND `a`.`acctID` =  '2'
AND `a`.`year` < 2014
AND `a`.`year` >= '2015';



SELECT 
IFNULL((select debit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2015), 0) as oDebit, sum(b.amountdr) as currentyeardebit, 
IFNULL((select credit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2015), 0) as oCredit, 
sum(b.amountcr) as currentyearcredit, sum(b.total) as total, `a`.*, (select sum(c.amountcr) from `comp_budget` c where `c`.`acctID` = `a`.`ID` and c.year = 2015 and c.year = b.year and c.companyID = 2) as budgetAmount, `d`.`acctTypeID`
FROM (`comp_chart_of_acct` a)
LEFT OUTER JOIN `view_comp_acc_det` b ON `a`.`ID` = `b`.`acctID` and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) <= 201512 and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) >= 201501 and b.companyID = 2
JOIN `_ref_acct_group` d ON `d`.`ID` = `a`.`acctGroupID` and (d.acctTypeID = "3" OR d.acctTypeID = "4")
WHERE `a`.`companyID` =  '2'
GROUP BY `a`.`ID`
ORDER BY `a`.`acctCode`;


SELECT IFNULL((select debit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2016), 0) as oDebit, sum(b.amountdr) as currentyeardebit, 
IFNULL((select credit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2016), 0) as oCredit, 
sum(b.amountcr) as currentyearcredit, sum(b.total) as total, `a`.*, (select sum(c.amountcr) from `comp_budget` c where `c`.`acctID` = `a`.`ID` and c.year = 2016 and c.year = b.year and c.companyID = 2) as budgetAmount, `d`.`acctTypeID`
FROM (`comp_chart_of_acct` a)
LEFT OUTER JOIN `view_comp_acc_det` b ON `a`.`ID` = `b`.`acctID` 
and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) <= 201602 
and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) >= 201601 and b.companyID = 2
JOIN `_ref_acct_group` d ON `d`.`ID` = `a`.`acctGroupID` and (d.acctTypeID = "3" OR d.acctTypeID = "4")
WHERE `a`.`companyID` =  '2'
GROUP BY `a`.`ID`
ORDER BY `a`.`acctCode`;


SELECT b.ID, b.*, IFNULL((select debit from comp_opening_balance where companyID = b.companyID and accountID = b.ID and year =2015), 0) oDebit, 
IFNULL((select credit from comp_opening_balance where companyID = b.companyID and accountID = b.ID and year =2015), 0) oCredit, 
IFNULL(sum(a.amountdr), 0) as currentyeardebit, IFNULL(sum(a.amountcr), 0) as currentyearcredit, 
IFNULL((select SUM(balance) from view_expence_and_income where companyID = b.companyID and acctType = 3 and year < 2014), 0) as lastRetainInc, IFNULL((select SUM(balance) from view_expence_and_income where companyID = b.companyID and acctType = 4 and year < 2014), 0) as lastRetainExp, sum(a.total) as total, `b`.`acctCode`, `b`.`acctName`, `b`.`acctGroupID`, `d`.`acctTypeID`, `e`.`balance`, IFNULL((select sum(c.amountdr) from view_comp_acc_det c where c.acctID = b.ID and c.year <2015 and c.year >=2015 ), 0)+IFNULL((select debit from comp_opening_balance where companyID = b.companyID and accountID = b.ID and year =2015), 0) as lastyeardebit, IFNULL((select sum(c.amountcr) from view_comp_acc_det c where c.acctID = b.ID and c.year <2015 and c.year >=2015 ), 0)+IFNULL((select credit from comp_opening_balance where companyID = b.companyID and accountID = b.ID and year =2015), 0) as lastyearcredit
FROM (`comp_chart_of_acct` b)
LEFT JOIN `view_comp_acc_det` a ON `a`.`acctID` = `b`.`ID` and a.year = 2015 And a.period <= 12
LEFT OUTER JOIN `_ref_acct_group` d ON `d`.`ID` = `a`.`acctGroupID`
LEFT OUTER JOIN `comp_acct_type` e ON `e`.`ID` = `d`.`acctTypeID`
WHERE `b`.`companyID` =  '2'
AND `b`.`acctGroupID` >  0
AND `b`.`id` > 0
GROUP BY `b`.`acctCode`;



SELECT b.ID, b.*, IFNULL((select debit from comp_opening_balance where companyID = b.companyID and accountID = b.ID and year =2016), 0) oDebit, 
IFNULL((select credit from comp_opening_balance where companyID = b.companyID and accountID = b.ID and year =2016), 0) oCredit, IFNULL(sum(a.amountdr), 0) as currentyeardebit, 
IFNULL(sum(a.amountcr), 0) as currentyearcredit, IFNULL((select SUM(balance) from view_expence_and_income where companyID = b.companyID and acctType = 3 and year < 2015), 0) as lastRetainInc, 
IFNULL((select SUM(balance) from view_expence_and_income where companyID = b.companyID and acctType = 4 and year < 2015), 0) as lastRetainExp, sum(a.total) as total, `b`.`acctCode`, `b`.`acctName`, `b`.`acctGroupID`, `d`.`acctTypeID`, `e`.`balance`, IFNULL((select sum(c.amountdr) from view_comp_acc_det c where c.acctID = b.ID and c.year <2016 and c.year >=2015 ), 0)+IFNULL((select debit from comp_opening_balance where companyID = b.companyID and accountID = b.ID and year =2015), 0) as lastyeardebit, IFNULL((select sum(c.amountcr) from view_comp_acc_det c where c.acctID = b.ID and c.year <2016 and c.year >=2015 ), 0)+IFNULL((select credit from comp_opening_balance where companyID = b.companyID and accountID = b.ID and year =2015), 0) as lastyearcredit
FROM (`comp_chart_of_acct` b)
LEFT JOIN `view_comp_acc_det` a ON `a`.`acctID` = `b`.`ID` and a.year = 2016 And a.period <= 2
LEFT OUTER JOIN `_ref_acct_group` d ON `d`.`ID` = `a`.`acctGroupID`
LEFT OUTER JOIN `comp_acct_type` e ON `e`.`ID` = `d`.`acctTypeID`
WHERE `b`.`companyID` =  '2'
AND `b`.`acctGroupID` >  0
AND `b`.`id` > 0
GROUP BY `b`.`acctCode`;










select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`acctGroupID` AS `acctGroupID`,`a`.`acctID` AS `acctID`,`a`.`year` AS `year`,`a`.`period` AS `period`,(
select coalesce(sum(`husseinkhamisdb`.`comp_journal_detail`.`amount_dr`),0) from `husseinkhamisdb`.`comp_journal_detail` where ((`husseinkhamisdb`.`comp_journal_detail`.`companyID` = `a`.`companyID`) and (`husseinkhamisdb`.`comp_journal_detail`.`acctCode` = `a`.`acctID`) and (`husseinkhamisdb`.`comp_journal_detail`.`period` = `a`.`period`) and (`husseinkhamisdb`.`comp_journal_detail`.`year` = `a`.`year`) and (`husseinkhamisdb`.`comp_journal_detail`.`status` = 1) and (`husseinkhamisdb`.`comp_journal_detail`.`is_delete` = 0))) AS `amountdr`,
(select coalesce(sum(`husseinkhamisdb`.`comp_journal_detail`.`amount_cr`),0) from `husseinkhamisdb`.`comp_journal_detail` where ((`husseinkhamisdb`.`comp_journal_detail`.`companyID` = `a`.`companyID`) and (`husseinkhamisdb`.`comp_journal_detail`.`acctCode` = `a`.`acctID`) and (`husseinkhamisdb`.`comp_journal_detail`.`period` = `a`.`period`) and (`husseinkhamisdb`.`comp_journal_detail`.`year` = `a`.`year`) and (`husseinkhamisdb`.`comp_journal_detail`.`status` = 1) and (`husseinkhamisdb`.`comp_journal_detail`.`is_delete` = 0))) AS `amountcr`,
(select (coalesce(sum(`husseinkhamisdb`.`comp_journal_detail`.`amount_dr`),0) - coalesce(sum(`husseinkhamisdb`.`comp_journal_detail`.`amount_cr`),0)) from `husseinkhamisdb`.`comp_journal_detail` where ((`husseinkhamisdb`.`comp_journal_detail`.`companyID` = `a`.`companyID`) and (`husseinkhamisdb`.`comp_journal_detail`.`acctCode` = `a`.`acctID`) and (`husseinkhamisdb`.`comp_journal_detail`.`period` = `a`.`period`) and (`husseinkhamisdb`.`comp_journal_detail`.`year` = `a`.`year`) and (`husseinkhamisdb`.`comp_journal_detail`.`status` = 1) and (`husseinkhamisdb`.`comp_journal_detail`.`is_delete` = 0))) AS `total`,`a`.`createdBy` AS `createdBy`,`a`.`createdTS` AS `createdTS`,`a`.`updatedBy` AS `updatedBy`,`a`.`updatedTS` AS `updatedTS`,`a`.`is_delete` AS `is_delete` from `husseinkhamisdb`.`comp_acc_det` `a` where ((`a`.`companyID` > 0) and (`a`.`acctID` > 0) and (`a`.`year` > 0) and (`a`.`period` > 0))






