<?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 

//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.id,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.id,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 

//view_unpost_ar_invoice
create or replace view view_unpost_ar_invoice as 
select e.customerName as customer, a.ID as invoiceID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.customerID,
b.itemID, concat("[",c.itemCode,"]",c.description) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
(select inventoriesID from item where id=b.itemID) 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_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 e.customerName as customer, a.ID as invoiceID, concat(concat(concat(a.formNo,'['),lpad(a.id,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 

//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(id,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(id,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(id,8,'0')),']') as formNo, 
totalamount as dr, null as cr, 3 as seq
from comp_supplier_creditnote
where updateable = 0
union
select a.ID, a.purchaseInvoiceID as parent, b.companyID, b.supplierID, 
'Payment' as transType,
b.paymentDate as transDate, b.referenceNo as refNo, 
concat(concat(concat(b.formNo,'['),lpad(b.id,8,'0')),']') as formNo,  
null as dr, a.amountApplied as cr, 4 as seq
from comp_payment_detail a, comp_payment b
where a.paymentID = b.id
and b.updateable = 0
union
select a.ID, a.purchaseInvoiceID as parent, b.companyID, b.supplierID, 
'Payment' as transType,
b.paymentDate as transDate, 'Discount' as refNo, 
concat(concat(concat(b.formNo,'['),lpad(b.id,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
and 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) as current,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=1) as month1,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=2) as month2,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=3) as month3,
(select sum(totalPayable)  from comp_supplier_invoice  where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=4) as month4,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=5) as month5,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=6) as month6,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=7) as month7,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=8) as month8,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=9) as month9,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=10) as month10,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())>10) 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(id,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(id,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(id,8,'0')),']') as formNo, 
totalamount as dr, null as cr, 3 as seq
from comp_sales_creditnote
union
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.id,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.id,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, (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


/*
//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



//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`));

?>