<?php

/* //view_unpost_ap_invoice
//dr uninvoice, cr supplier ap acct
//purchase = dt inventory, cr ap

create or replace view view_unpost_ap_invoice as 
select d.supplierName as supplier, a.ID as invoiceID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.supplierID,
b.lineNo, b.itemID, concat("[",c.itemCode,"]",b.itemName) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'unInvoiceAP') as drAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crAccountID,
(select taxAccount from tax_master where id=b.taxID) as drTaxAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crTaxAccountID
from comp_supplier_invoice a,
(comp_supplier_invoice_detail b left outer join item c on b.itemID = c.id),
supplier d
where a.id = b.invoiceid
and d.id = a.supplierID
and a.updateable = 1
and a.is_delete =0 
union 
select d.supplierName as supplier, a.ID as invoiceID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.supplierID,
b.lineNo + (select max(lineNo) from comp_supplier_invoice_detail where invoiceid = a.id), null, b.itemname, b.amountExcludedtax, b.taxid, b.taxamount, 
b.accountNoID as drAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crAccountID,
(select taxAccount from tax_master where id=b.taxID) as drTaxAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crTaxAccountID
from comp_supplier_invoice a,
comp_supplier_invoice_charges b,
supplier d
where a.id = b.invoiceid
and d.id = a.supplierID
and a.updateable = 1
and a.is_delete =0 


////add wakaf
create or replace view view_unpost_ap_invoice as 
select `d`.`supplierName` AS `supplier`,`a`.`ID` AS `invoiceID`,
concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,
`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,
`b`.`lineNo` AS `lineNo`,`b`.`itemID` AS `itemID`,concat('[',`c`.`itemCode`,']',`b`.`itemname`) AS `itemName`,
`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,
(select `iaccountdb`.`comp_control_acct_setup`.`accountID` 
	from `iaccountdb`.`comp_control_acct_setup` 
	where ((`iaccountdb`.`comp_control_acct_setup`.`companyID` = `a`.`companyID`) 
		and (`iaccountdb`.`comp_control_acct_setup`.`accountCode` = 'unInvoiceAP'))) AS `drAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,
(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` 
from ((`iaccountdb`.`comp_supplier_invoice` `a` 
left outer join `iaccountdb`.`comp_supplier_invoice_detail` `b` on `a`.`ID` = `b`.`invoiceID`)
      left join `iaccountdb`.`item` `c` on `b`.`itemID` = `c`.`ID`) 
join `iaccountdb`.`supplier` `d` on `d`.`ID` = `a`.`supplierID`
where ((`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) 
union 
select `d`.`supplierName` AS `supplier`,`a`.`ID` AS `invoiceID`,
concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,
(`b`.`lineNo` + (select ifnull(max(`iaccountdb`.`comp_supplier_invoice_detail`.`lineNo`),0) 
	from `iaccountdb`.`comp_supplier_invoice_detail` 
	where (`iaccountdb`.`comp_supplier_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `lineNo` ,
NULL AS `itemID`,`b`.`itemname` AS `itemname`,
`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,`b`.`accountNoID` AS `drAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,
(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` 
from `iaccountdb`.`comp_supplier_invoice` `a` left outer join `iaccountdb`.`comp_supplier_invoice_charges` `b` on `a`.`ID` = `b`.`invoiceID`
left join `iaccountdb`.`supplier` `d` on `d`.`ID` = `a`.`supplierID`
where ((`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))
////

//view_unpost_ap_DEBITNOTE
create or replace view view_unpost_ap_DEBITNOTE as 
select e.supplierName as supplier, a.ID as debitnoteID, concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.supplierID, c.supplierInvoiceNo,
b.itemID, concat("[",d.itemCode,"]",b.itemName) as itemName, b.amountExcludedtax, b.taxid, b.taxamount,
(select linkedAPAccountID from supplier where id=a.supplierID) as drAccountID,
(select inventoriesID from item where id=b.itemID) as crAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as drTaxAccountID,
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID
from comp_supplier_debitnote a,
(comp_supplier_debitnote_detail b left outer join item d on b.itemID = d.id ),
comp_supplier_invoice c, supplier e
where a.id = b.debitnoteID
and a.supplierID = e.ID
and a.supplierInvoiceID = c.ID
and a.updateable = 1
and a.is_delete =0 

//view_unpost_ap_creDITNOTE
create or replace view view_unpost_ap_creDITNOTE as
select e.supplierName as supplier, a.ID as creditnoteID, concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.supplierID, c.supplierInvoiceNo,
b.itemID, concat("[",d.itemCode,"]",b.itemName) as itemName, b.amountExcludedtax, b.taxid, b.taxamount,
(select inventoriesID from item where id=b.itemID) as drAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crAccountID,
(select taxAccount from tax_master where id=b.taxID) as drTaxAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crTaxAccountID
from comp_supplier_creditnote a,
(comp_supplier_creditnote_detail b left outer join item d on b.itemID = d.id),
comp_supplier_invoice c, supplier e
where a.id = b.creditnoteID 
and a.supplierInvoiceID = c.ID
and e.id = a.supplierID
and a.updateable = 1
and a.is_delete =0 

////
select `e`.`supplierName` AS `supplier`,`a`.`ID` AS `creditnoteID`,concat(concat(concat(`a`.`formNo`,'['),
convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`c`.`supplierInvoiceNo` AS `supplierInvoiceNo`,
`b`.`itemID` AS `itemID`,concat('[',`d`.`itemCode`,']',`b`.`itemname`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,
`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,(select `iaccountdb`.`item`.`inventoriesID` from `iaccountdb`.`item` where (`iaccountdb`.`item`.`ID` = `b`.`itemID`)) AS `drAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,
(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` 
from (((`iaccountdb`.`comp_supplier_creditnote` `a` join (`iaccountdb`.`comp_supplier_creditnote_detail` `b` 
left join `iaccountdb`.`item` `d` on((`b`.`itemID` = `d`.`ID`)))) 
join `iaccountdb`.`comp_supplier_invoice` `c`) 
join `iaccountdb`.`supplier` `e`) 
where ((`a`.`ID` = `b`.`creditnoteID`) and (`a`.`supplierInvoiceID` = `c`.`ID`) and (`e`.`ID` = `a`.`supplierID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))
////

//view_unpost_ar_invoice
//dr ar, cr sales
//dr cogs, cr inventories
//dr wakaf expense. cr wakaf accrual
//dr commission expense, cr commision accrual/payable
//commission only from itemsale. not include tax and charges. 

create or replace view view_unpost_ar_invoice as 
select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, b.itemID, 
concat("[",c.itemCode,"]",c.description) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
(select linkedAPAccountID from customer where id=a.customerID) as drAccountID,
(select incomeID from item where id=b.itemID) as crAccountID,
(select taxAccount from tax_master where id=b.taxID) as drTaxAccountID,
(select linkedAPAccountID from customer where id=a.customerID) as crTaxAccountID
from comp_sales_invoice a,
(comp_sales_invoice_detail b left outer join item c on b.itemID = c.id),
customer e
where a.id = b.invoiceid
and a.customerID = e.ID
and a.updateable = 1
and a.is_delete =0 
union 
select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, b.itemID, 
concat("[",c.itemCode,"]",c.description) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
(select cogsID from item where id=b.itemID) as drAccountID,
(select inventoriesID from item where id=b.itemID) as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_sales_invoice a,
(comp_sales_invoice_detail b left outer join item c on b.itemID = c.id),
customer e
where a.id = b.invoiceid
and a.customerID = e.ID
and a.updateable = 1
and a.is_delete =0 
union 
select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, null, b.chargedescription, b.amountExcludedtax, b.taxid, b.taxamount, 
b.accountNoID as drAccountID,
(select linkedAPAccountID from customer where id=a.customerID) as crAccountID,
(select taxAccount from tax_master where id=b.taxID) as drTaxAccountID,
(select linkedAPAccountID from customer where id=a.customerID) as crTaxAccountID
from comp_sales_invoice a,
comp_sales_invoice_charges b,
customer e
where a.id = b.invoiceid
and e.id = a.customerID
and a.updateable = 1
and a.is_delete =0 
union
select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, null, 'comission' as itemName, sum(b.amountExcludedtax) * d.commisionRate / 100 as comissionAmount, 0, 0, 
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'commissionexp') as drAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'commissionpay') as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_sales_invoice a,
comp_sales_invoice_detail b ,
comp_sales_agent d,
customer e
where a.companyID = d.companyID
and a.id = b.invoiceid
and a.agentID = d.ID
and a.customerID = e.ID
and a.agentid > 0
and a.updateable = 1
and a.is_delete =0  
group by a.id
union
select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, null, 'wakaf' as itemName, b.amountExcludedtax, 0, 0, 
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'wakafExpense') as drAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'wakafAcrual') as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_sales_invoice a,
comp_sales_invoice_detail b, 
wakaf_acrual c, 
item d,
customer e
where a.id = b.invoiceid
and a.customerID = e.ID
and a.companyID = c.companyID
and b.itemID = d.id
and d.incomeID = c.salesAccountID
and a.salesInvoiceDate between c.from and c.to
and a.updateable = 1
and a.is_delete =0 
union
select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, null, 'cogs' as itemName, sum(d.amount)  as cogsAmount, 0, 0, 
c.cogsID as drAccountID,
c.inventories as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_sales_invoice a,
(comp_sales_invoice_detail b left outer join item c on b.itemID = c.id),
comP_delivery_detail d,
customer e
where a.id = b.invoiceid
and a.customerID = e.ID
and a.updateable = 1
and a.is_delete =0  
group by a.id, c.cogsID, c.inventoriesID

 


////to add agent and wakaf
create or replace view view_unpost_ar_invoice as 
select `e`.`customerName` AS `customer`,`a`.`ID` AS `invoiceID`,
concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,
`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,`b`.`itemID` AS `itemID`,concat('[',`c`.`itemCode`,']',`c`.`description`) AS `itemName`,
`b`.`amountExcludedTax` AS `amountExcludedtax`,
`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,
(select `iaccountdb`.`customer`.`linkedAPAccountID` from `iaccountdb`.`customer` 
	where (`iaccountdb`.`customer`.`ID` = `a`.`customerID`)) AS `drAccountID`,
(select `iaccountdb`.`item`.`inventoriesID` from `iaccountdb`.`item` 
	where (`iaccountdb`.`item`.`ID` = `b`.`itemID`)) AS `crAccountID`,
(select `iaccountdb`.`customer`.`linkedAPAccountID` from `iaccountdb`.`customer` 
	where (`iaccountdb`.`customer`.`ID` = `a`.`customerID`)) AS `drTaxAccountID` 
(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` 
	where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID`,
from ((`iaccountdb`.`comp_sales_invoice` `a` join (`iaccountdb`.`comp_sales_invoice_detail` `b` left join `iaccountdb`.`item` `c` on((`b`.`itemID` = `c`.`ID`))))
join `iaccountdb`.`customer` `e`) where ((`a`.`ID` = `b`.`invoiceID`) and (`a`.`customerID` = `e`.`ID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) 
union 
select `e`.`customerName` AS `customer`,`a`.`ID` AS `invoiceID`,
concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,
`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,NULL AS `NULL`,`b`.`chargeDescription` AS `chargedescription`,
`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,`b`.`accountNoID` AS `drAccountID`,
(select `iaccountdb`.`customer`.`linkedAPAccountID` from `iaccountdb`.`customer` 
where (`iaccountdb`.`customer`.`ID` = `a`.`customerID`)) AS `crAccountID`,
(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` 
where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `iaccountdb`.`customer`.`linkedAPAccountID` from `iaccountdb`.`customer` 
where (`iaccountdb`.`customer`.`ID` = `a`.`customerID`)) AS `crTaxAccountID` 
from ((`iaccountdb`.`comp_sales_invoice` `a` join `iaccountdb`.`comp_sales_invoice_charges` `b`) join `iaccountdb`.`customer` `e`) 
where ((`a`.`ID` = `b`.`invoiceID`) and (`e`.`ID` = `a`.`customerID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))
////

//view_unpost_ar_DEBITNOTE
create or replace view view_unpost_ar_DEBITNOTE as 
select f.customerName as customer, a.salesinvoiceID, a.ID as debitnoteID, 
concat(a.formNo,'[',lpad(a.id,8,'0'),']') as formNo , a.companyID,a.customerID, 
concat(c.formNo,'[',lpad(c.id,8,'0'),']') as salesInvoiceNo, b.lineNo, b.itemID, 
concat("[",d.itemCode,"]",b.itemName) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
ifnull(f.linkedAPAccountID,e.accountID) as drAccountID, 
(select inventoriesID from item where id=b.itemID) as crAccountID, 
ifnull(f.linkedAPAccountID,e.accountID) as drTaxAccountID, 
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID 
from ((comp_sales_debitnote a join comp_sales_debitnote_detail b on a.id = b.debitnoteID) 
left outer join item d on b.itemID = d.id ) 
left outer join comp_sales_invoice c on c.ID = a.salesInvoiceID , 
customer f, comp_control_acct_setup e
where a.id = b.debitnoteID 
and a.customerid = f.id 
and e.companyID = a.companyID 
and e.accountCode = 'rcvacct' 
and a.id = b.debitnoteid
and a.updateable = 1 
and a.is_delete =0
union
select f.customerName as customer, a.salesinvoiceID, a.ID as debitnoteID, 
concat(a.formNo,'[',lpad(a.id,8,'0'),']') as formNo , a.companyID,a.customerID, 
concat(c.formNo,'[',lpad(c.id,8,'0'),']') as salesInvoiceNo, b.lineNo, null as itemID, 
b.chargeDescription as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
ifnull(f.linkedAPAccountID,e.accountID) as drAccountID, 
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'freightcharges') as crAccountID, 
ifnull(f.linkedAPAccountID,e.accountID) as drTaxAccountID, 
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID 
from (comp_sales_debitnote a 
left outer join comp_sales_invoice c on c.ID = a.salesInvoiceID) , 
customer f, comp_control_acct_setup e,
comp_sales_debitnote_charges b  
where a.id = b.debitnoteID 
and a.customerid = f.id 
and e.companyID = a.companyID 
and e.accountCode = 'rcvacct' 
and a.id = b.debitnoteid
and a.updateable = 1 
and a.is_delete =0



//view_unpost_ar_creDITNOTE
create or replace view view_unpost_ar_creDITNOTE as
select a.salesinvoiceID, a.ID as creditnoteID, 
concat(a.formNo,'[',lpad(a.id,8,'0'),']') as formNo , a.companyID,a.customerID, 
concat(c.formNo,'[',lpad(c.id,8,'0'),']') as salesInvoiceNo, b.lineNo, b.itemID, 
concat("[",d.itemCode,"]",b.itemName) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
ifnull(f.linkedAPAccountID,e.accountID) as drAccountID, 
(select inventoriesID from item where id=b.itemID) as crAccountID, 
ifnull(f.linkedAPAccountID,e.accountID) as drTaxAccountID, 
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID 
from ((comp_sales_creditnote a 
left outer join comp_sales_creditnote_detail b on a.id = b.creditnoteID) 
left outer join item d on b.itemID = d.id ) 
left outer join comp_sales_invoice c on c.ID = a.salesInvoiceID , 
customer f, comp_control_acct_setup e 
where a.id = b.creditnoteID and a.customerid = f.id and e.companyID = a.companyID 
and e.accountCode = 'rcvacct' and a.updateable = 1 and a.is_delete =0
union
select f.customerName as customer, a.salesinvoiceID, a.ID as creditnoteID, 
concat(a.formNo,'[',lpad(a.id,8,'0'),']') as formNo , a.companyID,a.customerID, 
concat(c.formNo,'[',lpad(c.id,8,'0'),']') as salesInvoiceNo, b.lineNo, null as itemID, 
b.chargeDescription as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
ifnull(f.linkedAPAccountID,e.accountID) as drAccountID, 
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'freightcharges') as crAccountID, 
ifnull(f.linkedAPAccountID,e.accountID) as drTaxAccountID, 
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID 
from (comp_sales_creditnote a 
left outer join comp_sales_invoice c on c.ID = a.salesInvoiceID) , 
customer f, comp_control_acct_setup e,
comp_sales_creditnote_charges b  
where a.id = b.creditnoteID 
and a.customerid = f.id 
and e.companyID = a.companyID 
and e.accountCode = 'rcvacct' 
and a.id = b.creditnoteid
and a.updateable = 1 
and a.is_delete =0




//issue : payment tiada confirmation. terus post on save. 
//view_unpost_ap_payment
//amountPaid => cr bank = acctNo dlm bank payment skrin atau defaul bank
//bank charges => dr bank charges dlm sistem general setup
//amountApplied - discount => dr ap linkedAPacctountid dlm table supplier
//discount => dr ap
//discount => cr other income (discount rec) dlm sistem general setup
create or replace view view_unpost_payment as 
select a.ID as paymentID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.supplierID, null as purchaseInvoiceID,
null as paymentDetailID, null as supplierInvoiceNo, 
a.amountPaid as amtPaid_amtAppl, a.bankCharges as discount_bankCharges, 
null as drAccountID,
(select linkAccountID from bank where id = a.accountID) as crAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'bankchargesAccount') as drDiscountAccountID,
null as crDiscountAccountID
from comp_payment a
where a.updateable = 1
and a.is_delete =0 
union
select a.ID as paymentID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.supplierID, b.purchaseInvoiceID,
b.ID as paymentDetailID, c.supplierInvoiceNo,  
b.amountApplied - b.discount as amtPaid_amtAppl, b.discount as discount_bankCharges,
(select linkedAPAccountID from supplier where id=a.supplierID) as drAccountID,
null as crAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as drDiscountAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'payDisc') as crDiscountAccountID
from comp_payment a,
comp_payment_detail b,
comp_supplier_invoice c
where a.id = b.paymentID
and c.id = b.purchaseInvoiceID 
and a.updateable = 1
and a.is_delete =0 

//view unpost other payment 
create or replace view view_unpost_other_payment as 
select a.ID as paymentID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,null as supplierID, null as purchaseInvoiceID,
null as paymentDetailID, null as supplierInvoiceNo, 
a.amountPaid as amtPaid_amtAppl, a.bankCharges as discount_bankCharges, 
null as drAccountID,
a.accountID as crAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'bankchargesAccount') as drDiscountAccountID,
null as crDiscountAccountID
from comp_payment_others a
where a.updateable = 1
and a.is_delete =0 

//drop the header
create or replace view view_unpost_other_payment as 
select a.ID as paymentID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,null as supplierID, null as purchaseInvoiceID,
b.ID as paymentDetailID, null as supplierInvoiceNo,  
b.lineNo, b.itemdescription,
b.amountIncludedTax  as amtPaid_amtAppl, null as discount_bankCharges,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'payAcct') as drAccountID,
a.accountID as crAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'payAcct') as drDiscountAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'payDisc') as crDiscountAccountID
from comp_payment_others a,
comp_payment_others_with_item b
where a.id = b.otherPaymentID
and a.updateable = 1
and a.is_delete =0 
union
select a.ID as paymentID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,null as supplierID, null as purchaseInvoiceID,
b.ID as paymentDetailID, null as supplierInvoiceNo,  
b.lineNo, b.itemdescription,
b.amountIncludedTax as amtPaid_amtAppl, null as discount_bankCharges,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'payAcct') as drAccountID,
a.accountID as crAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'payAcct') as drDiscountAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'payDisc') as crDiscountAccountID
from comp_payment_others a,
comp_payment_others_without_item b
where a.id = b.otherPaymentID
and a.updateable = 1
and a.is_delete =0 


//issue :  
//view_unpost_ar_payment
//discount => cr other income (discount rec) dlm sistem general setup
create or replace view view_unpost_ar_payment as 
select a.ID as paymentID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.customerID, null as salesInvoiceID,
null as paymentDetailID, null as supplierInvoiceNo, 
a.amountPaid as amtPaid_amtAppl, a.bankCharges as discount_bankCharges, 
null as drAccountID,
a.accountID as crAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'bankchargesAccount') as drDiscountAccountID,
null as crDiscountAccountID
from comp_receive_payment a
where a.updateable = 1
and a.is_delete =0 
union
//remove  header


create or replace view view_unpost_ar_payment as 
select a.ID as paymentID, concat(a.formNo,'[',lpad(a.id,8,'0'),']') as formNo ,a.companyID,a.customerID, a.referenceNo,
b.ID as paymentDetailID, concat(c.formNo,'[',lpad(c.id,8,'0'),']') as salesInvoiceNo,  
b.amountApplied - b.discount as amtPaid_amtAppl, b.discount as discount_bankCharges,
(select linkedAPAccountID from customer where id=a.customerID) as drAccountID,
null as crAccountID,
(select linkedAPAccountID from customer where id=a.customerID) as drDiscountAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'saledisacct') as crDiscountAccountID
from comp_receive_payment a,
comp_receive_payment_detail b,
comp_sales_invoice c
where a.id = b.receivePaymentID
and c.id = b.InvoiceID 
and a.updateable = 1
and a.is_delete =0 



//view supplier statement
create or replace view view_supplier_statement as 
select ID, ID as parent, companyID, supplierID,
'Invoice' as transType,
invoiceDate as transDate,supplierInvoiceNo as refNo, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo,
totalAmount as dr, null as cr, 1 as seq
from comp_supplier_invoice
where updateable = 0
union
select ID, supplierInvoiceID as parent, companyID, supplierID, 
'DebitNote' as transType,
debitnotedate  as transDate, null as refno, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo, 
null as dr, totalamount as cr, 2 as seq
from comp_supplier_debitnote
where updateable = 0
union
select ID, supplierInvoiceID as parent, companyID, supplierID, 
'CreditNote' as transType,
creditnotedate as transDate, null as refno, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo, 
totalamount as dr, null as cr, 3 as seq
from comp_supplier_creditnote
where updateable = 0
union
select b.ID, null as parent, b.companyID, b.supplierID, 
'Payment' as transType,
b.paymentDate as transDate, b.referenceNo as refNo, 
concat(concat(concat(b.formNo,'['),lpad(b.serialNo,8,'0')),']') as formNo,  
amountPaid as dr, null as cr, 4 as seq
from comp_payment b
where b.updateable = 0


//view supplier aging
create or replace view view_supplier_aging as 
select a.id as supplierID, (select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=0 and updateable=0) as current,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=1 and updateable=0) as month1,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=2 and updateable=0) as month2,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=3 and updateable=0) as month3,
(select sum(totalPayable)  from comp_supplier_invoice  where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=4 and updateable=0) as month4,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=5 and updateable=0) as month5,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=6 and updateable=0) as month6,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=7 and updateable=0) as month7,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=8 and updateable=0) as month8,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=9 and updateable=0) as month9,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=10 and updateable=0) as month10,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())>10 and updateable=0) as month11
from supplier a



//view customer statement
create or replace view view_customer_statement as 
select ID, ID as parent, companyID, customerID,
'Invoice' as transType,
salesinvoiceDate as transDate,customerpoNo as refNo, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo,
totalAmount as dr, null as cr, 1 as seq
from comp_sales_invoice
union
select ID, salesInvoiceID as parent, companyID, customerID, 
'DebitNote' as transType,
debitnotedate  as transDate, null as refno, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo, 
null as dr, totalamount as cr, 2 as seq
from comp_sales_debitnote
union
select ID, salesInvoiceID as parent, companyID, customerID, 
'CreditNote' as transType,
creditnotedate as transDate, null as refno, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo, 
totalamount as dr, null as cr, 3 as seq
from comp_sales_creditnote
union
select b.ID, 0 as parent, b.companyID, b.customerID, 
'Payment' as transType,
b.receivepaymentDate as transDate, b.referenceNo as refNo, 
concat(concat(concat(b.formNo,'['),lpad(b.serialNo,8,'0')),']') as formNo,  
null as dr, b.totalReceived as cr, 4 as seq
from comp_receive_payment b


/* select a.ID, a.receivePaymentID as parent, b.companyID, b.customerID, 
'Payment' as transType,
b.receivepaymentDate as transDate, b.referenceNo as refNo, 
concat(concat(concat(b.formNo,'['),lpad(b.serialNo,8,'0')),']') as formNo,  
null as dr, a.amountApplied as cr, 4 as seq
from comp_receive_payment_detail a, comp_receive_payment b
where a.receivepaymentID = b.id

union

select a.ID, a.salesInvoiceID as parent, b.companyID, b.customerID, 
'Payment' as transType,
b.receivepaymentDate as transDate, 'Discount' as refNo, 
concat(concat(concat(b.formNo,'['),lpad(b.serialNo,8,'0')),']') as formNo,  
null as dr, a.discount as cr, 5 as seq
from comp_payment_detail a, comp_payment b
where a.paymentID = b.id
*/

//view customer aging
create or replace view view_customer_aging as 
select a.id as customerID, a.customerName, a.phoneNumber1, a.email, 
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=0) as current,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=1) as month1,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=2) as month2,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=3) as month3,
(select sum(totalReceivable)  from comp_sales_invoice  where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=4) as month4,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=5) as month5,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=6) as month6,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=7) as month7,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=8) as month8,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=9) as month9,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=10) as month10,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())>10) as month11
from customer a


//view AR open position
create or replace view view_ar_open_position as 
select a.companyID, a.id as customerID, a.customerName, a.phoneNumber1, a.email, 
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,inv.salesinvoiceDate,now()) - terms.dueDays <= -90) as due120,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > -90 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <=-60 ) as due90,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > -60  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <= -30 ) as due60,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > -30  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <=-5 ) as due30,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > -5  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <=5 ) as due0,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 5  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <= 30 ) as pastDue30,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 30  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <= 60 ) as pastDue60,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 60  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <= 90 ) as pastDue90,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 90  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <=120 ) as pastDue120,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 120) as pastDue
from customer a



//view view_gst_tax
create or replace view view_gst_tax as
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Invoice' as transType, a.formNo as ref, a.salesInvoiceDate as transDate, 
a.customerID, d.customerName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_invoice a , 
comp_sales_invoice_detail b ,
customer d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.invoiceID
and a.customerid = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Invoice' as transType, a.formNo as ref, a.salesInvoiceDate as transDate, 
a.customerID, d.customerName, b.chargeDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_invoice a , 
comp_sales_invoice_charges b ,
customer d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.invoiceID
and a.customerid = d.id
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Credit Note' as transType, a.formNo as ref, a.creditNoteDate as transDate, 
a.customerID, d.customerName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_creditnote a , 
comp_sales_creditnote_detail b ,
customer d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.creditNoteID
and a.customerid = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Credit Note' as transType, a.formNo as ref, a.creditNoteDate as transDate, 
a.customerID, d.customerName, b.chargeDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_creditnote a , 
comp_sales_creditnote_charges b ,
customer d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.creditNoteID
and a.customerid = d.id
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Debit note' as transType, a.formNo as ref, a.debitNoteDate as transDate, 
a.customerID, d.customerName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_debitnote a , 
comp_sales_debitnote_detail b ,
customer d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.debitNoteID
and a.customerid = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Debit Note' as transType, a.formNo as ref, a.debitNoteDate as transDate, 
a.customerID, d.customerName, b.chargeDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_debitnote a , 
comp_sales_debitnote_charges b ,
customer d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.debitNoteID
and a.customerid = d.id
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union

#purchase invoice
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Invoice' as transType, a.formNo as ref, a.InvoiceDate as transDate, 
a.supplierID, d.supplierName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_invoice a , 
comp_supplier_invoice_detail b ,
supplier d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.ID = b.invoiceID
and a.supplierID = d.ID
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Invoice' as transType, a.formNo as ref, a.invoiceDate as transDate, 
a.supplierID, d.supplierName, b.chargeDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_invoice a , 
comp_supplier_invoice_charges b ,
supplier d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.ID = b.invoiceID
and a.supplierID = d.ID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Credit Note' as transType, a.formNo as ref, a.creditNoteDate as transDate, 
a.supplierID, d.supplierName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_creditnote a , 
comp_supplier_creditnote_detail b ,
supplier d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.creditNoteID
and a.supplierID = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Credit Note' as transType, a.formNo as ref, a.creditNoteDate as transDate, 
a.supplierID, d.supplierName, b.itemDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_creditnote a , 
comp_supplier_creditnote_charges b ,
supplier d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.creditNoteID
and a.supplierID = d.id
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Debit note' as transType, a.formNo as ref, a.debitNoteDate as transDate, 
a.supplierID, d.supplierName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_debitnote a , 
comp_supplier_debitnote_detail b ,
supplier d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.debitNoteID
and a.supplierID = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Debit Note' as transType, a.formNo as ref, a.debitNoteDate as transDate, 
a.supplierID, d.supplierName, b.itemDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_debitnote a , 
comp_supplier_debitnote_charges b ,
supplier d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.ID = b.debitNoteID
and a.supplierID = d.ID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union 

#other payment
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Other Payment With Item' as transType, a.formNo as ref, a.paymentDate as transDate, 
null, a.payTo as supplierName, b.itemDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_payment_others a , 
comp_payment_others_with_item b ,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.ID = b.otherPaymentID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Other Payment Without Item' as transType, a.formNo as ref, a.paymentDate as transDate, 
null, a.payTo as supplierName, b.itemDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_payment_others a , 
comp_payment_others_without_item b ,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.ID = b.otherPaymentID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0


/*
//run schedular to run stored procedure every 1 day of the month
//CREATE EVENT myevent
//    ON SCHEDULE EVERY 5 SECOND
//    DO
//      delete_rows_links();
//stored procedure to get supplier bring forward value
1. select all supplier
2. loop the supplier
2.1. get the last month value or 0
2.2. get current month transaction and count the balance
2.3. last month value +- current month balance
2.4. save into supplier monthly closing
*/
//create function countSupplierBalance(in yearMonth varchar(6))
//create procedure  countSupplierBalance(
//	in yearMonth varchar(6))
//begin
create event countSupplierBalance
on schedule
	every 1 month
do
	insert into comp_supplier_closing (companyID, supplierID, yearMonth,openBalance,totalDr,totalCr,balance)
	select c.companyID, c.ID as supplierID, date_format(now(),"%Y%m") as yearMonth, 
	coalesce(b.openBalance,0) as openBalance, coalesce(sum(a.dr),0) as totalDr, 
	coalesce(sum(a.cr),0) as totalCr, 
	coalesce(b.openBalance,0) - coalesce(sum(a.dr),0) - coalesce(sum(a.cr),0) as balance
	from (supplier c left outer join view_supplier_statement a on c.ID = a.supplierID 
	and date_format(a.transDate,"%Y%m") = date_format(now(),"%Y%m")) 
	left outer join comp_supplier_closing b on a.supplierID = b.supplierID
	and a.companyID = b.companyID 
	and b.yearMonth = date_format(a.transDate - interval 1 month,"%Y%m")
	
	group by c.id, a.companyID ;

create event countSupplierBalanceDaily
on schedule
	every 1 day
do
	insert into comp_supplier_closing_daily (companyID, supplierID, transDate,openBalance,totalDr,totalCr,balance)
	select c.companyID, c.ID as supplierID,  date_format(now(),"%Y%m%d") as transDate, 
	coalesce(b.openBalance,0) as openBanlance, coalesce(sum(a.dr),0) as totalDr, 
	coalesce(sum(a.cr),0) as totalCr, 
	coalesce(b.openBalance,0) - coalesce(sum(a.dr),0) - coalesce(sum(a.cr),0) as balance 
	from (supplier c left outer join view_supplier_statement a on c.ID = a.supplierID 
		and date_format(a.transDate,"%Y%m%d") = date_format(now(),"%Y%m%d")) 
		left outer join comp_supplier_closing_daily b on a.supplierID = b.supplierID 
		and a.companyID = b.companyID 
		and b.transDate = a.transDate - interval 1 day 
	group by c.id, a.companyID

//end

CREATE TRIGGER `updateBalanceItem` 
BEFORE INSERT ON `comp_receive_item_detail` 
FOR EACH ROW 
if new.balanceitem is null then 
set new.balanceItem = new.quantityReceived; 
end if;
end

CREATE TRIGGER `trigger_after_insert_item` 
BEFORE INSERT ON `item` 
FOR EACH ROW 
begin
if new.incomeID = 0 or new.incomeID is null then 
set new.incomeID = (select accountID from comp_control_acct_setup where ID > 0 and companyID = new.companyID and accountCode='saleacct'); 
end if;
if new.cogsID = 0 or new.cogsID is null then 
set new.cogsID = (select accountID from comp_control_acct_setup where id>0 and companyID = new.companyID and accountCode = 'saleacct');
end if;
if new.inventoriesID = 0 or new.inventoriesID is null then 
set new.inventoriesID = (select accountID from comp_control_acct_setup where ID > 0 and companyID = new.companyID and accountCode='saleacct'); 
end if;
end

CREATE TRIGGER `trigger_before_insert_po_add_serial` 
BEFORE INSERT ON `comp_purchase_order` 
FOR EACH ROW 
begin 
set new.serialNo = 
(select serialNo from view_serial_comp_purchase_order 
	where companyID = new.companyID 
	and formID = 40); 
end

//view_inventory_statement
//consolidate from receive item + adjustment + sale + location transfer
//date, itemID, itemName, transactionType, formno, locationID, location, unitOfMeasure, quantity, 

create or replace view view_inventory_statement as 
select a.companyID, a.deliveryDate as transactionDate, b.itemID, c.name,
g.supplierName as supplierCustomer, 1 as transactionCode, 'Purchase' as transactionType, 
concat(a.formNo,'[',lpad(a.id,8,'0'),']') as formNo, a.locationID, d.desc as location,
e.desc as unitOfMeasure, b.quantityReceived as quantityIn, null as quantityOut, a.updateable
from ((comp_receive_item a left outer join comp_receive_item_detail b on a.ID = b.receiveID)
left outer join item c on b.itemID = c.ID) 
left outer join item_details f on c.id = f.itemID , 
tbl_location d, _sys_um e, supplier g
where f.locationID = d.fldid
and e.fldid = c.unitOfmeasureID
and a.supplierID = f.id
union

select  a.companyID, a.adjustmentDate as transactionDate, b.itemID, c.name,
d.desc  as supplierCustomer, 2 as transactionCode, 'stockAdjustment' as transactionType, 
a.formNo, a.locationID, d.desc as location,
e.desc as unitOfMeasure, if(b.counted - b.qtyOnHand > 0,b.counted - b.qtyOnHand,null) as quantityIn,
if(b.counted - b.qtyOnHand < 0,b.counted - b.qtyOnHand,null) as quantityOut, a.updateable
from ((((item_adjustment a left outer join item_adjustment_detail b on a.id = b.adjustmentID)
left outer join item c on b.itemID = c.ID)
left outer join item_details f on c.ID = f.itemID) 
left outer join tbl_location d on d.fldid = a.locationid) 
left outer join _sys_um e on e.fldid = b.unitOfMeasureID

union
select  a.companyID, a.deliveryOrderDate as transactionDate, b.itemID, c.name,
g.customerName as supplierCustomer, 3 as transactionCode, 'Sale' as transactionType, 
a.formNo, a.locationID, d.desc as location,
e.desc as unitOfMeasure, null as quantityIn, b.quantitySent * -1 as quantityOut, a.updateable
from ((comp_delivery_order a left outer join comp_delivery_detail b on a.ID = b.deliveryID)
left outer join item c on b.itemID = c.ID) 
left outer join item_details f on c.id = f.itemID, 
tbl_location d, _sys_um e, customer g
where a.locationID = d.fldid
and e.fldid = c.unitOfmeasureID
and a.customerID = f.id


union
select a.companyID, a.dateTransfer as transactionDate, b.itemID, c.name,
d.desc  as supplierCustomer, 4 as transactionCode, 'TransferOut' as transactionType, 
concat(a.formNo,'[',lpad(a.id,8,'0'),']') as formNo, a.fromLocationID as locationID, d.desc as location,
e.desc as unitOfMeasure, null as quantityIn,  b.quantity * -1 as quantityOut, a.updateable
from ((comp_location_transfer a left outer join comp_location_transfer_detail b on a.ID = b.locationTransferID)
left outer join item c on b.itemID = c.ID), 
tbl_location d, _sys_um e
where a.fromLocationID = d.fldid
and e.fldid = c.unitOfmeasureID

union
select a.companyID, a.dateTransfer as transactionDate, b.itemID, c.name,
d.desc  as supplierCustomer, 5 as transactionCode, 'TransferIn' as transactionType, 
concat(a.formNo,'[',lpad(a.id,8,'0'),']') as formNo, a.toLocationID as locationID, d.desc as location,
e.desc as unitOfMeasure, b.quantity  as quantityIn, null as quantityOut, a.updateable
from ((comp_location_transfer a left outer join comp_location_transfer_detail b on a.ID = b.locationTransferID)
left outer join item c on b.itemID = c.ID), 
tbl_location d, _sys_um e
where a.fromLocationID = d.fldid
and e.fldid = c.unitOfmeasureID



//ar inventory
create or replace view view_ar_inventory as
select companyID, customerID, 
concat(formNo,'[',lpad(id,8,'0'),']') as formNo, salesinvoiceDate,
totalReceivable
from comp_sales_invoice
where totalReceivable > 0

//unpost ap invoice
//dr uninvoice, cr supplier ap acct


//unpost receive item
//on receive item, cr uninvoice receive item, dr  inventory.
//on confirm invoice, dr uninvoice receive item, cr creditors. if any different in currency, dr forex. if any different in price different, dr purchase variant. 
/*
create or replace view view_unpost_ap_invoice as 
select a.ID as invoiceID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.supplierID,
b.lineNo, b.itemID, concat("[",c.itemCode,"]",b.itemName) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
(select inventoriesID from item where id=b.itemID) as drAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crAccountID,
(select taxAccount from tax_master where id=b.taxID) as drTaxAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crTaxAccountID
from comp_supplier_invoice a,
(comp_supplier_invoice_detail b left outer join item c on b.itemID = c.id)
*/

create or replace view view_unpost_receive_item as
select a.id as receiveID, a.companyID, a.locationID, a.deliveryDate as transactionDate, a.supplierID, 
concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo , 
b.lineNo, b.itemID, concat("[",c.itemCode,"]",c.name) as itemName, a.exchangeRate,
b.unitPrice, b.quantityReceived, b.umID, b.amount, 
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'unInvoiceAP') as drAccountID,
(select inventoriesID from item where id=b.itemID) as crAccountID 
from comp_receive_item a, 
comp_receive_item_detail b, 
item c 
where a.updateable = 1 
and a.id = b.receiveID 
and b.itemID = c.id


create or replace view view_inventoryList_subTotal as
select a.companyid, a.locationid, b.itemID, concat('[',b.itemcode,']', b.itemname) as itemName, d.name as category, b.description, 
sum(b.balanceItem * b.unitPrice) as stockValue
FROM comp_receive_item a ,
comp_receive_item_detail b,
item c,
master_code d
WHERE a.id = b.receiveID
and b.balanceItem > 0
and c.id = b.itemID
and c.itemCategoryID = d.ID
group by companyid, locationid, itemID, itemName, category, description


create or replace view view_item_details as 
select b.companyid, a.itemid, concat('[',c.itemCode,']',c.name) as itemName, c.description,
b.locationid, sum(a.balanceItem) as onHandQty, 
a.umID, c.inventoriesID as acctID,
d.code as location, e.code as unitOfMeasure
from comp_receive_item_detail a,
comp_receive_item b, 
item c,
tbl_location d,
_sys_um e
where a.receiveID = b.id 
and a.itemID = c.id 
and d.fldid = b.locationID
and e.fldid = a.umid
and a.balanceItem > 0 
group by companyid, itemid, itemName, description, locationid, umID, acctID, location, unitOfMeasure

//result banyak row
select b.companyid, a.itemid, concat('[',c.itemCode,']',c.name) as itemName, c.description, b.locationid, sum(a.balanceItem) as onHandQty, a.umID, c.inventoriesID as acctID, d.code as location, e.code as unitOfMeasure from comp_receive_item_detail a, comp_receive_item b, item c, tbl_location d, _sys_um e where a.receiveID = b.id and a.itemID = c.id and d.fldid = b.locationID and e.fldid = a.umid and a.balanceItem > 0 and a.itemid = 1 group by companyid, itemid, itemName, description, locationid, umID, acctID, location, unitOfMeasure

//result 3 row
select b.companyid, a.itemid, concat('[',c.itemCode,']',c.name) as itemName, c.description, b.locationid, a.balanceItem as onHandQty, a.umID, c.inventoriesID as acctID, d.code as location, e.code as unitOfMeasure from comp_receive_item_detail a, comp_receive_item b, item c, tbl_location d, _sys_um e where a.receiveID = b.id and a.itemID = c.id and d.fldid = b.locationID and e.fldid = a.umid and a.balanceItem > 0 and a.itemid = 1 group by companyid, itemid, itemName, description, locationid, umID, acctID, location, unitOfMeasure



create or replace view view_account_balance as
select a.companyID, a.acctID, concat(left(b.acctCode,4),"-",mid(b.acctCode,4,3),"-",right(b.acctCode,2)," ",b.acctName) as acctName, a.year, a.period, sum(a.total) as closing
from comp_acc_det a, comp_chart_of_acct b
where b.id = a.acctID
group by companyID, acctID, acctName, year, period



//view gaf
create or replace view view_gaf as
select `a`.`companyID` AS `companyID`,'P' AS `col1`,`a`.`supplierName` AS `col2`,`a`.`supplierCompanyNo` AS `col3`,`b`.`invoiceDate` AS `col4`,
concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,'import declaration number' AS `col6`,`c`.`lineNo` AS `col7`,
`c`.`itemdescription` AS `col8`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col9`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col10`,
`e`.`code` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` 
from ((((`supplier` `a` join `comp_supplier_invoice` `b`) join `comp_supplier_invoice_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) 
where ((`a`.`ID` = `b`.`supplierID`) and (`b`.`ID` = `c`.`invoiceID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`)) 
union select `a`.`companyID` AS `companyID`,'P' AS `col1`,`a`.`supplierName` AS `col2`,`a`.`supplierCompanyNo` AS `col3`,`b`.`invoiceDate` AS `col4`
,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,'import declaration number' AS `col6`,`c`.`lineNo` AS `col7`,
`c`.`itemdescription` AS `col8`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col9`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col10`,
`e`.`code` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` 
from ((((`supplier` `a` join `comp_supplier_invoice` `b`) join `comp_supplier_invoice_charges` `c`) join `_ref_currency` `d`) join `tax_master` `e`) 
where ((`a`.`ID` = `b`.`supplierID`) and (`b`.`ID` = `c`.`invoiceID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`)) 
union select `a`.`companyID` AS `companyID`,'P' AS `col1`,`a`.`supplierName` AS `col2`,`a`.`supplierCompanyNo` AS `col3`,`b`.`debitNoteDate` AS `col4`,
concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,'import declaration number' AS `col6`,`c`.`lineNo` AS `col7`,
`c`.`itemdescription` AS `col8`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col9`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col10`,
`e`.`code` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` 
from ((((`supplier` `a` join `comp_supplier_debitnote` `b`) join `comp_supplier_debitnote_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) 
where ((`a`.`ID` = `b`.`supplierID`) and (`b`.`ID` = `c`.`debitnoteID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`)) 
union select `a`.`companyID` AS `companyID`,'P' AS `col1`,`a`.`supplierName` AS `col2`,`a`.`supplierCompanyNo` AS `col3`,`b`.`creditNoteDate` AS `col4`,
concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,'import declaration number' AS `col6`,`c`.`lineNo` AS `col7`,
`c`.`itemdescription` AS `col8`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col9`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col10`,
`e`.`code` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14`
from ((((`supplier` `a` join `comp_supplier_creditnote` `b`) join `comp_supplier_creditnote_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) 
where ((`a`.`ID` = `b`.`supplierID`) and (`b`.`ID` = `c`.`creditnoteID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`)) 
union select `a`.`companyID` AS `companyID`,'S' AS `col1`,`a`.`customerName` AS `col2`,`a`.`customerCompanyNo` AS `col3`,`b`.`salesInvoiceDate` AS `col4`,
concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,`c`.`lineNo` AS `col6`,`g`.`description` AS `col7`,
(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col8`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col9`,`e`.`code` AS `col10`,
`f`.`fldcountry` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` 
from ((((((`customer` `a` join `comp_sales_invoice` `b`) join `comp_sales_invoice_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) join `_ref_country` `f`) join `item` `g`) 
where ((`a`.`ID` = `b`.`customerID`) and (`b`.`ID` = `c`.`invoiceID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`) and (`f`.`fldid` = `a`.`customerCountryID`) and (`g`.`ID` = `c`.`itemID`)) 
union select `a`.`companyID` AS `companyID`,'S' AS `col1`,`a`.`customerName` AS `col2`,`a`.`customerCompanyNo` AS `col3`,`b`.`salesInvoiceDate` AS `col4`,
concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,`c`.`lineNo` AS `col6`,`c`.`chargeDescription` AS `col7`,
(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col8`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col9`,`e`.`code` AS `col10`,
`f`.`fldcountry` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` 
 from (((((`customer` `a` join `comp_sales_invoice` `b`) join `comp_sales_invoice_charges` `c`) join `_ref_currency` `d`) join `tax_master` `e`) join `_ref_country` `f`) 
 where ((`a`.`ID` = `b`.`customerID`) and (`b`.`ID` = `c`.`invoiceID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`) and (`f`.`fldid` = `a`.`customerCountryID`)) 
 union select `a`.`companyID` AS `companyID`,'S' AS `col1`,`a`.`customerName` AS `col2`,`a`.`customerCompanyNo` AS `col3`,`b`.`debitNoteDate` AS `col4`,
 concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,`c`.`lineNo` AS `col6`,`c`.`itemdescription` AS `col7`,
 (`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col8`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col9`,`e`.`code` AS `col10`,
 `f`.`fldcountry` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` 
 from (((((`customer` `a` join `comp_sales_debitnote` `b`) join `comp_sales_debitnote_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) join `_ref_country` `f`) 
 where ((`a`.`ID` = `b`.`customerID`) and (`b`.`ID` = `c`.`debitnoteID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`) and (`f`.`fldid` = `a`.`customerCountryID`)) 
 union select `a`.`companyID` AS `companyID`,'S' AS `col1`,`a`.`customerName` AS `col2`,`a`.`customerCompanyNo` AS `col3`,`b`.`creditNoteDate` AS `col4`,
 concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,`c`.`lineNo` AS `col6`,`c`.`itemdescription` AS `col7`,
 (`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col8`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col9`,`e`.`code` AS `col10`,
 `f`.`fldcountry` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` 
 from (((((`customer` `a` join `comp_sales_creditnote` `b`) join `comp_sales_creditnote_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) join `_ref_country` `f`) 
 where ((`a`.`ID` = `b`.`customerID`) and (`b`.`ID` = `c`.`creditnoteID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`) and (`f`.`fldid` = `a`.`customerCountryID`));




create or replace view view_serial_comp_purchase_order as 
SELECT companyID , 40 as formID, 
if(ifnull(max(serialNo),0)>0,max(serialNo)+1,(select forminitialNo from comp_form_control where companyID = comp_purchase_order.companyID and formID=40 )) as serialNo 
FROM `comp_purchase_order` group by companyID, formID




create or replace view view_wakaf_accrual as SELECT b.companyID, b.acctID, sum(b.total) 
FROM company a, comp_acc_det b 
WHERE a.id = b.companyid 
and b.acctid in (select distinct accountid from comp_control_acct_setup where accountCode = 'wakafAcrual') 
group by companyid, acctid

?>