//GAF tax report for custom. 

select id as companyID, 
'C' as col1,
companyName as col2,
companyNo as col3,
gstRegisteredNo as col4,
'period start' as col5 ,
'period end' as col6 ,
date_format(now(),"%d/%m/%Y") as col7 ,
'iAccount V1.o' as col8 ,
'GAF version' as col9 ,
null as col10 ,
null as col11 ,
null as col12 ,
null as col13 ,
null as col14 
from company 


create view view_gaf as 
select a.companyID, 
'P' as col1,
a.supplierName as col2,
a.supplierCompanyNo as col3,
date_format(b.invoiceDate,'%d/%m/%Y') as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_supplier_invoice b,
comp_supplier_invoice_detail c,	
_ref_currency d,
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, 
'P' as col1,
a.supplierName as col2,
a.supplierCompanyNo as col3,
date_format(b.invoiceDate,'%d/%m/%Y') as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_supplier_invoice b,
comp_supplier_invoice_charges c,	
_ref_currency d,
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, 
'P' as col1,
a.supplierName as col2,
a.supplierCompanyNo as col3,
date_format(b.debitNoteDate,'%d/%m/%Y') as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_supplier_debitnote b,
comp_supplier_debitnote_detail c,	
_ref_currency d,
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, 
'P' as col1,
a.supplierName as col2,
a.supplierCompanyNo as col3,
date_format(b.creditNoteDate,'%d/%m/%Y') as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_supplier_creditnote b,
comp_supplier_creditnote_detail c,	
_ref_currency d,
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, 
'S' as col1,
a.customerName as col2,
a.customerCompanyNo as col3,
date_format(b.salesInvoiceDate,'%d/%m/%Y') as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_sales_invoice b,
comp_sales_invoice_detail c,	
_ref_currency d,
tax_master e,
_ref_country f,
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, 
'S' as col1,
a.customerName as col2,
a.customerCompanyNo as col3,
date_format(b.salesInvoiceDate,'%d/%m/%Y') as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_sales_invoice b,
comp_sales_invoice_charges c,	
_ref_currency d,
tax_master e,
_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, 
'S' as col1,
a.customerName as col2,
a.customerCompanyNo as col3,
date_format(b.debitNoteDate,'%d/%m/%Y') as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_sales_debitnote b,
comp_sales_debitnote_detail c,	
_ref_currency d,
tax_master e,
_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, 
'S' as col1,
a.customerName as col2,
a.customerCompanyNo as col3,
date_format(b.creditNoteDate,'%d/%m/%Y') as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_sales_creditnote b,
comp_sales_creditnote_detail c,	
_ref_currency d,
tax_master e,
_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


//journal gaf
union
select a.companyID, 
'L' as col1,
date_format(a.effective_Date,'%d/%m/%Y') as col2,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
null   as col13,
null	    as col14
from comp_journal a,
comp_sales_creditnote b,
comp_sales_creditnote_detail c,	
_ref_currency d,
tax_master e,
_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
