ALTER TABLE `comp_gst_transaction` ADD `reverse` BOOLEAN NOT NULL DEFAULT FALSE AFTER `journalNo`;

update _ref_tax_master set defaultTaxAccount = '260010000' where id = 14 ;

create or replace view view_gst_tax as
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Sales Invoice' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`salesInvoiceDate` AS `transDate`,`a`.`customerID` AS `customerID`,`d`.`customerName` AS `customerName`,concat(`e`.`name`,' / ',`e`.`itemCode`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst`
from `comp_sales_invoice` `a` join `comp_sales_invoice_detail` `b` join `customer` `d` join `item` `e` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Sales Invoice' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`salesInvoiceDate` AS `transDate`,`a`.`customerID` AS `customerID`,`d`.`customerName` AS `customerName`,`b`.`chargeDescription` AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst`
from `comp_sales_invoice` `a` join `comp_sales_invoice_charges` `b` join `customer` `d` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j`
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) and (`a`.`updateAble` = 0) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Sales Credit Note' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`creditNoteDate` AS `transDate`,`a`.`customerID` AS `customerID`,`d`.`customerName` AS `customerName`,concat(`e`.`name`,' / ',`e`.`itemCode`) AS `itemName`,(`b`.`amountExcludedTax` * -(1)) AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,(`b`.`taxAmount` * -(1)) AS `taxAmount`,(`b`.`amountIncludedTax` * -(1)) AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst`
from `comp_sales_creditnote` `a` join `comp_sales_creditnote_detail` `b` join `customer` `d` join `item` `e` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Sales Credit Note' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`creditNoteDate` AS `transDate`,`a`.`customerID` AS `customerID`,`d`.`customerName` AS `customerName`,`b`.`chargeDescription` AS `itemName`,(`b`.`amountExcludedTax` * -(1)) AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,(`b`.`taxAmount` * -(1)) AS `taxAmount`,(`b`.`amountIncludedTax` * -(1)) AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst`
from `comp_sales_creditnote` `a` join `comp_sales_creditnote_charges` `b` join `customer` `d` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Sales Debit note' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`debitNoteDate` AS `transDate`,`a`.`customerID` AS `customerID`,`d`.`customerName` AS `customerName`,concat(`e`.`name`,' / ',`e`.`itemCode`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_sales_debitnote` `a` join `comp_sales_debitnote_detail` `b` join `customer` `d` join `item` `e` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Sales Debit Note' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`debitNoteDate` AS `transDate`,`a`.`customerID` AS `customerID`,`d`.`customerName` AS `customerName`,`b`.`chargeDescription` AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_sales_debitnote` `a` join `comp_sales_debitnote_charges` `b` join `customer` `d` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Purchase Invoice' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`invoiceDate` AS `transDate`,`a`.`supplierID` AS `supplierID`,`d`.`supplierName` AS `supplierName`,concat(`e`.`name`,' / ',`e`.`itemCode`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_supplier_invoice` `a` join `comp_supplier_invoice_detail` `b` join `supplier` `d` join `item` `e` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Purchase Invoice' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`invoiceDate` AS `transDate`,`a`.`supplierID` AS `supplierID`,`d`.`supplierName` AS `supplierName`,`b`.`chargeDescription` AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_supplier_invoice` `a` join `comp_supplier_invoice_charges` `b` join `supplier` `d` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Purchase Credit Note' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`creditNoteDate` AS `transDate`,`a`.`supplierID` AS `supplierID`,`d`.`supplierName` AS `supplierName`,concat(`e`.`name`,' / ',`e`.`itemCode`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_supplier_creditnote` `a` join `comp_supplier_creditnote_detail` `b` join `supplier` `d` join `item` `e` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Purchase Credit Note' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`creditNoteDate` AS `transDate`,`a`.`supplierID` AS `supplierID`,`d`.`supplierName` AS `supplierName`,`b`.`itemdescription` AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_supplier_creditnote` `a` join `comp_supplier_creditnote_charges` `b` join `supplier` `d` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Purchase Debit note' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`debitNoteDate` AS `transDate`,`a`.`supplierID` AS `supplierID`,`d`.`supplierName` AS `supplierName`,concat(`e`.`name`,' / ',`e`.`itemCode`) AS `itemName`,(`b`.`amountExcludedTax` * -(1)) AS `b.amountExcludedTax * -1`,`b`.`taxRate` AS `taxRate`,(`b`.`taxAmount` * -(1)) AS `b.taxAmount * -1`,(`b`.`amountIncludedTax` * -(1)) AS `b.amountIncludedTax * -1`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_supplier_debitnote` `a` join `comp_supplier_debitnote_detail` `b` join `supplier` `d` join `item` `e` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Purchase Debit Note' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`debitNoteDate` AS `transDate`,`a`.`supplierID` AS `supplierID`,`d`.`supplierName` AS `supplierName`,`b`.`itemdescription` AS `itemName`,(`b`.`amountExcludedTax` * -(1)) AS `b.amountExcludedTax * -1`,`b`.`taxRate` AS `taxRate`,(`b`.`taxAmount` * -(1)) AS `b.taxAmount * -1`,(`b`.`amountIncludedTax` * -(1)) AS `b.amountIncludedTax * -1`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_supplier_debitnote` `a` join `comp_supplier_debitnote_charges` `b` join `supplier` `d` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Other Payment' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`paymentDate` AS `transDate`,NULL AS `NULL`,`a`.`payTo` AS `supplierName`,`b`.`itemdescription` AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_payment_others` `a` join `comp_payment_others_without_item` `b` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` 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) and (`a`.`updateAble` = 0) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Cash Sales' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`salesInvoiceDate` AS `transDate`,NULL AS `customerID`,`a`.`paymentFrom` AS `customerName`,concat(`e`.`name`,' / ',`e`.`itemCode`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_cash_sales` `a` join `comp_cash_sales_detail` `b` join `item` `e` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` where ((`a`.`ID` = `b`.`invoiceID`) 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) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Other Receive' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`receivePaymentDate` AS `transDate`,NULL AS `customerID`,`a`.`receiveFrom` AS `customerName`,`b`.`description` AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_receive_payment_others` `a` join `comp_receive_payment_others_detail` `b` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` where ((`a`.`ID` = `b`.`receivePaymentOthersID`) 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) and (`a`.`updateAble` = 0) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1))
union
select b.ID as lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,`a`.`type` AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`date` AS `transDate`,NULL AS `supplierID`,`a`.`vendor_cust_name` AS `supplierName`,`b`.`description` AS `itemName`,
if(a.reverse > 0 ,(`a`.`based_amount`* -1),a.based_amount ) AS `amountExcludedTax`,
`b`.`taxRate` AS `taxRate`,
if(a.reverse > 0 ,(if((`b`.`taxAmountDr` > 0),`b`.`taxAmountDr`,`b`.`taxAmountCr`)*-1),if((`b`.`taxAmountDr` > 0),`b`.`taxAmountDr`,`b`.`taxAmountCr`)) AS `taxAmount`,
if(a.reverse > 0 ,(if((`b`.`taxAmountDr` > 0),`b`.`taxAmountDr`,`b`.`taxAmountCr`) + `a`.`based_amount`)*-1,(if((`b`.`taxAmountDr` > 0),`b`.`taxAmountDr`,`b`.`taxAmountCr`) + `a`.`based_amount`)) AS `amountIncludedTax`,
`b`.`taxID` AS `taxID`,`f`.`code` AS `gst`
from `comp_gst_transaction` `a`
join `comp_gst_transaction_detail` `b` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` where ((`a`.`ID` = `b`.`gstTransID`) 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) and (`a`.`updateAble` = 0) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)))
union
select b.lineNo, `a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,'Cash Purchase' AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`paymentDate` AS `transDate`,NULL AS `supplierID`,`a`.`payTo` AS `supplierName`,concat(`e`.`name`,' / ',`e`.`itemCode`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,`b`.`taxAmount` AS `taxAmount`,`b`.`amountIncludedTax` AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from `comp_payment_others` `a` join `comp_payment_others_with_item` `b` join `item` `e` join `tax_master` `f` join `tax_type` `g` join `tax_class` `h` join `tax_group` `i` join `comp_journal` `j` where ((`a`.`ID` = `b`.`otherPaymentID`) 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) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1));


CREATE or replace VIEW `view_gst_report` AS select `a`.`companyID` AS `companyid`,`b`.`gstRegisteredDate` AS `gstRegisteredDate`,`b`.`taxablePeriod` AS `taxablePeriod`,`a`.`financialYear` AS `financialYear`,`a`.`startDate` AS `startDate`,`a`.`endDate` AS `endDate`,`a`.`period` AS `period`,`b`.`gstRegisteredNo` AS `A1`,
`b`.`companyName` AS `A2`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B5a`,
(select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B5b`,
(select sum(`view_gst_tax`.`amountExcludedTax`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B6a`,(select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('AJP','TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B6b`,
if(((select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) > (select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),((select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) - (select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),0) AS `B7`,
if(((select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) > (select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),((select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) - (select sum(`view_gst_tax`.`taxAmount`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),0) AS `B8`
,(select sum(`view_gst_tax`.`amountExcludedTax`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'ZRL') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C10`,
(select sum(`view_gst_tax`.`amountExcludedTax`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'ZRE') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C11`,
(select sum(`view_gst_tax`.`amountExcludedTax`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('ES','ES43')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C12`,
(select sum(`view_gst_tax`.`amountExcludedTax`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'RS') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C13`,
(select sum(`view_gst_tax`.`amountExcludedTax`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'IS') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C14`,
(select sum((`view_gst_tax`.`amountExcludedTax` * 0.06)) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'IS') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C15`,
(select sum(`view_gst_tax`.`amountExcludedTax`) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'TX-CG') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C16`,
(select sum(`comp_sales_bad_debt`.`valueBadDebt`) from `comp_sales_bad_debt` where ((`comp_sales_bad_debt`.`companyID` = `a`.`companyID`) and (`comp_sales_bad_debt`.`transDate` between `a`.`startDate` and `a`.`endDate`) and (`comp_sales_bad_debt`.`updateAble` = 0))) AS `C17`,
(select sum(`comp_sales_invoice`.`totalReceivable`) from `comp_sales_invoice` where ((`comp_sales_invoice`.`companyID` = `a`.`companyID`) and (`comp_sales_invoice`.`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_sales_invoice`.`salesInvoiceDate`,now()) >= 6))) AS `month6`,0 AS `C18`
from (`comp_financial_year` `a` join `company` `b`) where ((`b`.`ID` = `a`.`companyID`) and (`a`.`is_delete` = 0));



update _ref_tax_master set defaultTaxAccount = '810040000' where id = 18 ;


ALTER TABLE `supplier` CHANGE `email` `email` VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `customer` CHANGE `email` `email` VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`)
VALUES ('379', 'GSTRPTSUM', 'gstReportSummary', '103', '6', '0', 'gstReport/gstReportSummary', '0', '0', '82', '1', 'icon-active.png', 'EN');

INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`)
VALUES ('380', 'GST03SUM', 'gst03Summary', '103', '6', '0', 'gst03/gst03Summary', '0', '0', '84', '1', 'icon-active.png', 'EN');

UPDATE _sys_menu SET seq = 81 WHERE fldid = 323;

UPDATE _sys_menu SET seq = 82 WHERE fldid = 379;

UPDATE _sys_menu SET seq = 83 WHERE fldid = 321;

UPDATE _sys_menu SET seq = 84 WHERE fldid = 380;

UPDATE _sys_menu SET seq = 85 WHERE fldid = 361;

UPDATE _sys_menu SET seq = 86 WHERE fldid = 362;

INSERT INTO `master_code` (`ID`, `masterID`, `parentFilterID`, `code`, `name`, `shortName`, `description`, `orderNo`, `createdBy`, `createdTS`, `updateBy`, `updateTS`, `is_delete`) VALUES
( 163, 18, NULL, 'SBD', 'Supplier Bad Debt Relief', 'SBD', 'Supplier Bad Debt Relief Form', 314, 1, '2014-05-07 02:01:25', 0, '0000-00-00 00:00:00', 0),
( 162,18, NULL, 'SDR', 'Supplier Bad Debt Recover', 'SDR', 'Supplier Bad Debt Recover Form', 313, 1, '2014-05-07 02:01:25', 0, '2015-06-05 09:16:55', 0),
( 161,18, NULL, 'BDR', 'Bad Debt Recover', 'DR', 'Bad Debt Recover Form', 312, 1, '2014-05-07 02:01:25', 0, '2015-06-05 09:16:55', 0),
( 160,18, NULL, 'BD', 'Bad Debt Relief', 'BD', 'Bad Debt Relief Form', 311, 1, '2014-05-07 02:01:25', 0, '0000-00-00 00:00:00', 0);


ALTER TABLE `supplier` ADD `approvalNo` VARCHAR(32) NOT NULL ;
