select a.id, a.serialno, a.formno, a.formno, a.payto, a.journalno from comp_payment_others a where
a.journalno not in (select journalid from comp_journal where companyid=a.companyid) and a.companyid=13 and a.updateable = 0 and is_delete = 0

AP20151000000049,AP20151000000050,AP20151000000051,AP20151000000052,AP20151000000053,AP20151000000054,AP20151000000055,AP20151000000056,AP20151000000057,AP20151000000058,AP20151000000059,AP20151000000060,AP20151000000061,AP20151000000062,AP20151000000063


--Other PAYMENT

select a.id, a.serialno, a.formno, a.paymentdate, a.formno, a.payto, a.journalno, a.referenceno, a.companyid from comp_payment_others a where
a.journalno not in (select journalid from comp_journal where companyid=a.companyid) and a.companyid in (select id from company) and a.updateable = 0 and is_delete = 0
ORDER BY `companyid` ASC

select a.id, a.serialno, a.formno, a.paymentdate, a.formno, a.payto, a.journalno, a.referenceno, a.companyid from comp_payment_others a where a.journalno not in (select journalid from comp_journal where companyid=a.companyid) and a.companyid in (select id from company) and a.updateable = 0 and is_delete = 0 and companyid = 8 ORDER BY `paymentdate` ASC

--other receive payment

select a.id, a.serialno, a.formno, a.receivepaymentdate, a.formno, a.fromto, a.journalno, a.referenceno, a.companyid from comp_receive_payment_others a where a.journalno not in (select journalid from comp_journal where companyid=a.companyid) and a.companyid in (select id from company) and a.updateable = 0 and is_delete = 0 and companyid = 8 ORDER BY `receivepaymentdate` ASC

--Sales invoice

select a.id, a.serialno, a.formno, a.salesinvoicedate, a.formno, a.journalno, a.companyid from comp_sales_invoice a where a.journalno not in (select journalid from comp_journal where companyid=a.companyid) and a.companyid in (select id from company) and a.updateable = 0 and is_delete = 0 

--Purchase invoice

select a.id, a.serialno, a.formno, a.invoicedate, a.formno, a.journalno, a.companyid from comp_supplier_invoice a where a.journalno not in (select journalid from comp_journal where companyid=a.companyid) and a.companyid in (select id from company) and a.updateable = 0 and is_delete = 0 

--Receive payment customer

select a.id, a.serialno, a.formno, a.receivepaymentdate, a.formno, a.journalno, a.companyid from comp_receive_payment a where a.journalno not in (select journalid from comp_journal where companyid=a.companyid) and a.companyid in (select id from company) and a.updateable = 0 and is_delete = 0 

--supplier payment 

select a.id, a.serialno, a.formno, a.paymentdate, a.formno, a.journalno, a.companyid from comp_payment a where a.journalno not in (select journalid from comp_journal where companyid=a.companyid) and a.companyid in (select id from company) and a.updateable = 0 and is_delete = 0

---to get balance from journal details

SELECT sum(amount_dr) as Debit, sum(amount_cr) as Credit FROM `comp_journal_detail` WHERE companyid=8 and is_delete=0 and status = 1





---- Mencari beza amount dalam comp_journal dengan comp_journal_details

SELECT a.journalid, a.total_amount_dr, a.total_amount_cr, sum(b.amount_dr), sum(b.amount_cr) FROM comp_journal a, `comp_journal_detail` b 
WHERE a.companyid = b.companyid
and a.journalid = b.journalid
and a.companyid = 31
group by a.companyid, a.journalid
having a.total_amount_dr <> sum(b.amount_dr)
or a.total_amount_cr <> sum(b.amount_cr)




SELECT sum(amount_dr) as Debit , sum(amount_cr) as Credit  FROM `comp_journal_detail` WHERE `companyID` = 29 and status =1 and is_delete=0


----mencari accountgroupid dalam cOA xde dalam table acctgroupid------

select * FROM `comp_journal_detail` a,comp_chart_of_acct b where a.acctcode = b.id and b.acctgroupid not in (select id from _ref_acct_group)

----BEza journal detail debit & credit
SELECT year,period, sum(amount_dr) - sum(amount_cr) FROM `comp_journal_detail` WHERE companyid=6 and year=2016 and status=1 and is_delete= 0 group by year,period