

ALTER TABLE `company`  ADD `masterMSIC` INT(12) NOT NULL ;

ALTER TABLE `comp_supplier_invoice`  ADD `invoiceType` TINYINT(1) NOT NULL  AFTER `badDebtID`;

ALTER TABLE `supplier`  ADD `approvalNo` VARCHAR(32) NOT NULL AFTER is_delete;
 ALTER TABLE `supplier` ADD `commenceDate` DATE NOT NULL  AFTER `approvalNo`;
 ALTER TABLE `supplier`  ADD `ceaseDate` DATE NOT NULL  AFTER `commenceDate`;


ALTER TABLE `comp_payment_others` ADD `simplifiedTax` INT(1) NOT NULL AFTER `memo`;
ALTER TABLE `comp_payment_others_without_item` ADD `taxBL` DOUBLE NOT NULL AFTER `taxID`;

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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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 (a.is_delete = 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_journal_table as 
select distinct a.ID, a.companyID, 'comp_cash_sales' as tableName,b.journalID, b.formNo 
from comp_cash_sales a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID,  'comp_payment',b.journalID, b.formNo 
from comp_payment a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_payment_others',b.journalID, b.formNo 
from comp_payment_others a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_purchase_order',b.journalID, b.formNo 
from comp_purchase_order a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_receive_payment',b.journalID, b.formNo 
from comp_receive_payment a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_receive_payment_others',b.journalID, b.formNo 
from comp_receive_payment_others a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0

union select distinct a.ID, a.companyID, 'comp_sales_creditnote',b.journalID, b.formNo 
from comp_sales_creditnote a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_sales_debitnote',b.journalID, b.formNo 
from comp_sales_debitnote a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_sales_invoice',b.journalID, b.formNo 
from comp_sales_invoice a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_supplier_creditnote',b.journalID, b.formNo 
from comp_supplier_creditnote a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_supplier_debitnote',b.journalID, b.formNo 
from comp_supplier_debitnote a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_supplier_invoice',b.journalID, b.formNo 
from comp_supplier_invoice a, comp_journal b
where a.journalNo = b.journalID
and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_delivery_order',b.journalID, b.formNo 
from comp_delivery_order a, comp_journal b
where a.journalNo = b.journalID
-- and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_gst_transaction',b.journalID, b.formNo 
from comp_gst_transaction a, comp_journal b
where a.journalNo = b.journalID
-- and a.is_delete = 0
union select distinct a.ID, a.companyID, 'comp_receive_item',b.journalID, b.formNo 
from comp_receive_item a, comp_journal b
where a.journalNo = b.journalID;



CREATE TABLE IF NOT EXISTS `trash_comp_journal` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `oldID` int(11) NOT NULL,
  `companyID` int(11) DEFAULT NULL,
  `journalID` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `description` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `referrence` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `batch` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `module` int(11) DEFAULT NULL,
  `effective_date` date DEFAULT NULL,
  `entered_date` date DEFAULT NULL,
  `period` smallint(2) DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `total_amount_dr` double DEFAULT NULL,
  `total_amount_cr` double DEFAULT NULL,
  `total_amount` double DEFAULT '0',
  `status` tinyint(4) DEFAULT '0' COMMENT 'unposted = 0, posted = 1',
  `remarks` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `createdTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `formNo` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `is_delete` smallint(16) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS `trash_comp_journal_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `trashID` int(11) NOT NULL,
  `oldID` int(11) NOT NULL,
  `companyID` int(11) DEFAULT '0',
  `journalID` varchar(50) CHARACTER SET utf8 DEFAULT '0',
  `sequence` int(11) DEFAULT '0',
  `acctCode` int(11) DEFAULT '0',
  `projectID` int(11) DEFAULT '0',
  `description` varchar(200) CHARACTER SET utf8 DEFAULT '0',
  `amount_cr` double DEFAULT '0',
  `amount_dr` double DEFAULT '0',
  `currencyID` int(11) DEFAULT '0',
  `currency_amount` double DEFAULT '0',
  `exchange_rate` double DEFAULT '0',
  `enter_exchange_rate` double DEFAULT '0',
  `period` smallint(2) DEFAULT '0',
  `year` year(4) DEFAULT NULL,
  `formNo` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `supplier` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `line` int(11) DEFAULT NULL,
  `status` smallint(2) DEFAULT '0',
  `createdBy` int(11) DEFAULT NULL,
  `createdTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) DEFAULT NULL,
  `updatedTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(16) NOT NULL,
  `bankReconcileID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;



INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`) VALUES (387, 'GSTTXMP', 'gsttaxmapping', '102', '6', 0, 'gstsetting/GSTtaxMapping', '0', NULL, 33, 1, 'icon-active.png', 'EN');

create or replace VIEW `view_customer_aging` AS
select `a`.`companyID` AS `companyID`,`a`.`ID` AS `customerID`,`a`.`customerName` AS `customerName`,`a`.`phoneNumber1` AS `phoneNumber1`,`a`.`email` AS `email`,
(ifnull((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()) = 0) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 0) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 0) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `current`,
(ifnull((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()) = 1) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 1) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 1) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month1`,
(ifnull((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()) = 2) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 2) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 2) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month2`,
(ifnull((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()) = 3) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 3) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 3) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month3`,
(ifnull((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()) = 4) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 4) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 4) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month4`,
(ifnull((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()) = 5) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 5) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 5) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month5`,
(ifnull((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) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 6) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 6) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month6`,
(ifnull((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()) = 7) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 7) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 7) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month7`,
(ifnull((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()) = 8) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 8) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 8) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month8`,
(ifnull((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()) = 9) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 9) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 9) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month9`,
(ifnull((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()) = 10) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month10`,
(ifnull((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()) > 10) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) > 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) > 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month11`
from `customer` `a`;

INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`) VALUES
(388, 'GSTPREF', 'gstpreference', '102', '6', 0, 'gstsetting/', '0', NULL, 33, 1, 'icon-active.png', 'EN');

UPDATE `_sys_menu` SET `seq` = '34' WHERE `_sys_menu`.`fldid` = 387;


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 coalesce(sum(`view_gst_tax`.`amountExcludedTax`),0) from `view_gst_tax` 
where ((`view_gst_tax`.`companyID` = `a`.`companyID`) 
and (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B5a')) > 0)
and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B5a`,
-- 'SR','DS','IMS')) 
(select coalesce(sum(`view_gst_tax`.`taxAmount`),0) from `view_gst_tax` 
where ((`view_gst_tax`.`companyID` = `a`.`companyID`) 
and (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B5b')) > 0)
and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B5b`,
-- (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) 
(select coalesce(sum(`view_gst_tax`.`amountExcludedTax`),0) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) 
and (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B6a')) >0)
and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B6a`,
-- (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) 
(select coalesce(sum(`view_gst_tax`.`taxAmount`),0) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) 
and (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B6b')) >0)
and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B6b`,
-- (`view_gst_tax`.`gst` in ('AJP','TX','TX-CG','IM','IMS','TX-E43','TX-RE')) 

if(((select coalesce(sum(`view_gst_tax`.`taxAmount`),0) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) 
and (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B5b')) >0)
-- and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) 
and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) 
> (select coalesce(sum(`view_gst_tax`.`taxAmount`),0) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) 
and (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B6b')) >0)
-- 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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) 
and (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B5b')) >0)
-- and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) 
and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) - (select coalesce(sum(`view_gst_tax`.`taxAmount`),0) from `view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) 
and (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B6b')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B6b')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B5b')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B6b')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='B5b')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='C10')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='C11')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='C12')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='C13')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='C15')) >0)
-- 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 (find_in_set(`view_gst_tax`.`gst`, (select taxcode from comp_gst_field_mapping where companyID =  a.companyID and item='C16')) >0)
-- 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`,
(select sum(`comp_sales_debt_recover`.`valueDebtRecover`) from `comp_sales_debt_recover` where ((`comp_sales_debt_recover`.`companyID` = `a`.`companyID`) and (`comp_sales_debt_recover`.`transDate` between `a`.`startDate` and `a`.`endDate`) and (`comp_sales_debt_recover`.`updateAble` = 0))) AS `C18`
 from (`comp_financial_year` `a` join `company` `b`) where ((`b`.`ID` = `a`.`companyID`) and (`a`.`is_delete` = 0))



ALTER TABLE `zakat_details` ADD `checkAble` TINYINT(1) NOT NULL AFTER `updatedTS`;

ALTER TABLE `zakat_summary` ADD `updateAble` TINYINT(1) NOT NULL DEFAULT '1' AFTER `is_delete`;

ALTER TABLE `zakat_details` ADD `compAcctDetailID` INT(11) NOT NULL AFTER `zakatSummaryID`;


CREATE TABLE IF NOT EXISTS `comp_receive_payment_detail_creditnote` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`paymentID` int(11) NOT NULL,
`creditnoteID` int(11) NOT NULL,
`amountApplied` double NOT NULL,
`debtRecoverID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `comp_receive_payment_detail_debitnote` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`paymentID` int(11) NOT NULL,
`debitnoteID` int(11) NOT NULL,
`amountApplied` double NOT NULL,
`debtRecoverID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `comp_payment_detail_creditnote` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`paymentID` int(11) NOT NULL,
`creditnoteID` int(11) NOT NULL,
`amountApplied` double NOT NULL,
`debtRecoverID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `comp_payment_detail_debitnote` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`paymentID` int(11) NOT NULL,
`debitnoteID` int(11) NOT NULL,
`amountApplied` double NOT NULL,
`debtRecoverID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `comp_payment_detail_historical_invoice` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`paymentID` int(11) NOT NULL,
`historicalinvoiceID` int(11) NOT NULL,
`amountApplied` double NOT NULL,
`debtRecoverID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS `comp_receive_payment_detail_historical_invoice` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`paymentID` int(11) NOT NULL,
`historicalinvoiceID` int(11) NOT NULL,
`amountApplied` double NOT NULL,
`debtRecoverID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `comp_delivery_detail` ADD `GSTid` INT(11) NOT NULL AFTER `amount`, ADD `taxPercentage` INT(11) NOT NULL AFTER `GSTid`, ADD `TaxCode` VARCHAR(20) NOT NULL AFTER `taxPercentage`;


CREATE OR REPLACE VIEW view_gst_tax as
select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo 
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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`  ,j.journalID, j.formNo
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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`  ,j.journalID, j.formNo
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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`  ,j.journalID, j.formNo
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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`  ,j.journalID, j.formNo
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo 
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo 
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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`  ,j.journalID, j.formNo
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo 
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo 
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo 
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo 
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) and (a.`is_delete` = 0)) 
union select `b`.`lineNo` 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`,'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` ,j.journalID, j.formNo 
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) and (a.`is_delete` = 0)) 
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` ,j.journalID, j.formNo 
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)) and (a.`isDelete` = 0)) 
union select `b`.`lineNo` 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`,'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`  ,j.journalID, j.formNo
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) and (a.`is_delete` = 0)) 
union select `a`.`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`,'Bad debt relief' AS `transType`,`a`.`journalNo` AS `ref`,`a`.`transDate` AS `transDate`,NULL AS `supplierID`,`c`.`customerName` AS `supplierName`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`serialNo`,8,'0') using latin1),']') AS `itemName`,round((((((round(`b`.`totalReceivable`,2) / round(`b`.`totalamount`,2)) * round(`b`.`totalTaxAmount`,2)) * 106) / 6) - ((round(`b`.`totalReceivable`,2) / round(`b`.`totalamount`,2)) * round(`b`.`totalTaxAmount`,2))),2) AS `amountExcludedTax`,`f`.`taxPercentage` AS `taxRate`,round(((`b`.`totalReceivable` / `b`.`totalamount`) * `b`.`totalTaxAmount`),2) AS `taxAmount`,round((round((((((`b`.`totalReceivable` / `b`.`totalamount`) * `b`.`totalTaxAmount`) * 106) / 6) - ((`b`.`totalReceivable` / `b`.`totalamount`) * `b`.`totalTaxAmount`)),2) + ((`b`.`totalReceivable` / `b`.`totalamount`) * `b`.`totalTaxAmount`)),2) AS `amountIncludedTax`,NULL AS `taxID`,`f`.`code` AS `gst` ,j.journalID, j.formNo 
from (((((((`comp_sales_bad_debt` `a` join `comp_sales_invoice` `b` on((`b`.`badDebtID` = `a`.`ID`))) join `customer` `c` on((`c`.`ID` = `a`.`custID`))) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`f`.`companyID` = `a`.`companyID`) and (`f`.`code` = 'AJP') and (`a`.`updateAble` = 0) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`a`.`updateAble` = 0) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8))) 
union select `a`.`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`,'Bad debt Recover' AS `transType`,`a`.`journalNo` AS `ref`,`a`.`transDate` AS `transDate`,NULL AS `supplierID`,`c`.`customerName` AS `supplierName`,concat(`y`.`formNo`,'[',convert(lpad(`y`.`serialNo`,8,'0') using latin1),']') AS `itemName`,round((((((round(`b`.`amountApplied`,2) / round(`y`.`totalamount`,2)) * round(`y`.`totalTaxAmount`,2)) * 106) / 6) - ((round(`b`.`amountApplied`,2) / round(`y`.`totalamount`,2)) * round(`y`.`totalTaxAmount`,2))),2) AS `amountExcludedTax`,`f`.`taxPercentage` AS `taxRate`,round(((`b`.`amountApplied` / `y`.`totalamount`) * `y`.`totalTaxAmount`),2) AS `taxAmount`,round((round((((((`b`.`amountApplied` / `y`.`totalamount`) * `y`.`totalTaxAmount`) * 106) / 6) - ((`b`.`amountApplied` / `y`.`totalamount`) * `y`.`totalTaxAmount`)),2) + ((`b`.`amountApplied` / `y`.`totalamount`) * `y`.`totalTaxAmount`)),2) AS `amountIncludedTax`,NULL AS `taxID`,`f`.`code` AS `gst` ,j.journalID, j.formNo 
from ((((((((`comp_sales_debt_recover` `a` join `comp_receive_payment_detail` `b` on((`b`.`debtRecoverID` = `a`.`ID`))) left join `comp_sales_invoice` `y` on((`y`.`ID` = `b`.`invoiceID`))) join `customer` `c` on((`c`.`ID` = `a`.`custID`))) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`f`.`companyID` = `a`.`companyID`) and (`f`.`code` = 'AJS') and (`a`.`updateAble` = 0) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8))) 
union select `a`.`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`,'Supplier Bad debt relief' AS `transType`,`a`.`journalNo` AS `ref`,`a`.`transDate` AS `transDate`,`a`.`supplierID` AS `supplierID`,`c`.`supplierName` AS `supplierName`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`serialNo`,8,'0') using latin1),']') AS `itemName`,round((((((round((`b`.`totalPayable` + ifnull(sum(`p`.`amountApplied`),0)),2) / round(`b`.`totalAmount`,2)) * round(`b`.`totalTaxAmount`,2)) * 106) / 6) - ((round((`b`.`totalPayable` + ifnull(sum(`p`.`amountApplied`),0)),2) / round(`b`.`totalAmount`,2)) * round(`b`.`totalTaxAmount`,2))),2) AS `amountExcludedTax`,`f`.`taxPercentage` AS `taxRate`,round((((`b`.`totalPayable` + ifnull(sum(`p`.`amountApplied`),0)) / `b`.`totalAmount`) * `b`.`totalTaxAmount`),2) AS `taxAmount`,(round((((((round((`b`.`totalPayable` + ifnull(sum(`p`.`amountApplied`),0)),2) / round(`b`.`totalAmount`,2)) * round(`b`.`totalTaxAmount`,2)) * 106) / 6) - ((round((`b`.`totalPayable` + ifnull(sum(`p`.`amountApplied`),0)),2) / round(`b`.`totalAmount`,2)) * round(`b`.`totalTaxAmount`,2))),2) + round((((`b`.`totalPayable` + ifnull(sum(`p`.`amountApplied`),0)) / `b`.`totalAmount`) * `b`.`totalTaxAmount`),2)) AS `amountIncludedTax`,NULL AS `taxID`,`f`.`code` AS `gst` ,j.journalID, j.formNo 
from (((((((((`comp_purchase_bad_debt` `a` join `comp_supplier_invoice` `b` on((`b`.`badDebtID` = `a`.`ID`))) join `comp_payment_detail` `p` on((`p`.`purchaseInvoiceID` = `b`.`ID`))) join `comp_payment` `pay` on(((`pay`.`ID` = `p`.`paymentID`) and (`pay`.`paymentDate` > `a`.`transDate`)))) join `supplier` `c` on((`c`.`ID` = `a`.`supplierID`))) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`f`.`companyID` = `a`.`companyID`) and (`f`.`code` = 'AJS') and (`a`.`updateAble` = 0) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8))) group by `p`.`purchaseInvoiceID` 
union select `a`.`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`,'Supplier Bad debt Recover' AS `transType`,`a`.`journalNo` AS `ref`,`a`.`transDate` AS `transDate`,`a`.`suppID` AS `supplierID`,`c`.`supplierName` AS `supplierName`,concat(`y`.`formNo`,'[',convert(lpad(`y`.`serialNo`,8,'0') using latin1),']') AS `itemName`,round((((((round(`b`.`amountApplied`,2) / round(`y`.`totalAmount`,2)) * round(`y`.`totalTaxAmount`,2)) * 106) / 6) - ((round(`b`.`amountApplied`,2) / round(`y`.`totalAmount`,2)) * round(`y`.`totalTaxAmount`,2))),2) AS `amountExcludedTax`,`f`.`taxPercentage` AS `taxRate`,round(((`b`.`amountApplied` / `y`.`totalAmount`) * `y`.`totalTaxAmount`),2) AS `taxAmount`,round((round((((((`b`.`amountApplied` / `y`.`totalAmount`) * `y`.`totalTaxAmount`) * 106) / 6) - ((`b`.`amountApplied` / `y`.`totalAmount`) * `y`.`totalTaxAmount`)),2) + ((`b`.`amountApplied` / `y`.`totalAmount`) * `y`.`totalTaxAmount`)),2) AS `amountIncludedTax`,NULL AS `taxID`,`f`.`code` AS `gst`  ,j.journalID, j.formNo
from ((((((((`comp_purchase_debt_recover` `a` join `comp_payment_detail` `b` on((`b`.`debtRecoverID` = `a`.`ID`))) left join `comp_supplier_invoice` `y` on((`y`.`ID` = `b`.`purchaseInvoiceID`))) join `supplier` `c` on((`c`.`ID` = `a`.`suppID`))) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`f`.`companyID` = `a`.`companyID`) and (`f`.`code` = 'AJP') and (`a`.`updateAble` = 0) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)));


create or replace VIEW `view_journal_table` AS

SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`referenceNo` AS `referenceNo`,
'comp_cash_sales' AS `tableName`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_cash_sales` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`referenceNo` AS `referenceNo`,
'comp_payment' AS `comp_payment`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_payment` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`referenceNo` AS `referenceNo`,
'comp_payment_others' AS `comp_payment_others`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_payment_others` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`formNo` AS `referenceNo`,
'comp_purchase_order' AS `comp_purchase_order`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_purchase_order` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`referenceNo` AS `referenceNo`,
'comp_receive_payment' AS `comp_receive_payment`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_receive_payment` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`referenceNo` AS `referenceNo`,
'comp_receive_payment_others' AS `comp_receive_payment_others`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_receive_payment_others` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`b`.`formNo` AS `referenceNo`,
'comp_sales_creditnote' AS `comp_sales_creditnote`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_sales_creditnote` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`b`.`formNo` AS `referenceNo`,
'comp_sales_debitnote' AS `comp_sales_debitnote`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_sales_debitnote` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`customerPoNo` AS `referenceNo`,
'comp_sales_invoice' AS `comp_sales_invoice`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_sales_invoice` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`b`.`formNo` AS `referenceNo`,
'comp_supplier_creditnote' AS `comp_supplier_creditnote`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_supplier_creditnote` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`b`.`formNo` AS `referenceNo`,
'comp_supplier_debitnote' AS `comp_supplier_debitnote`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_supplier_debitnote` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`supplierInvoiceNo` AS `referenceNo`,
'comp_supplier_invoice' AS `comp_supplier_invoice`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_supplier_invoice` `a`
JOIN `comp_journal` `b`)
WHERE ((convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
AND (`a`.`is_delete` = 0))
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`b`.`formNo` AS `referenceNo`,
'comp_delivery_order' AS `comp_delivery_order`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_delivery_order` `a`
JOIN `comp_journal` `b`)
WHERE (convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`reference` AS `referenceNo`,
'comp_gst_transaction' AS `comp_gst_transaction`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_gst_transaction` `a`
JOIN `comp_journal` `b`)
WHERE (convert(`a`.`journalNo` USING utf8) = `b`.`journalID`)
UNION
SELECT DISTINCT `a`.`ID` AS `ID`,
`a`.`companyID` AS `companyID`,
`a`.`supplierDoNo` AS `referenceNo`,
'comp_receive_item' AS `comp_receive_item`,
`b`.`journalID` AS `journalID`,
`b`.`formNo` AS `formNo`
FROM (`comp_receive_item` `a`
JOIN `comp_journal` `b`)
WHERE (convert(`a`.`journalNo` USING utf8) = `b`.`journalID`);


CREATE TABLE `comp_historical_supplier_invoice` ( `ID` INT(11) NOT NULL AUTO_INCREMENT , `serialNo` INT(11) NOT NULL DEFAULT '1' , `companyID` INT(11) NOT NULL , `supplierID` INT(11) NOT NULL , `formNo` VARCHAR(32) NOT NULL , `invoiceDate` DATE NOT NULL , `supplierInvoiceNo` VARCHAR(32) NOT NULL , `currencyID` INT(11) NOT NULL , `exchangeRate` DOUBLE NOT NULL , `totalAmount` DOUBLE NOT NULL , `amountPaid` DOUBLE NOT NULL , `totalTaxAmount` DOUBLE NOT NULL , `totalPayable` DOUBLE NOT NULL , `total` DOUBLE NOT NULL , `createdBy` INT(11) NOT NULL , `createdTS` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `updatedBy` INT(11) NOT NULL , `updatedTS` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL , `is_delete` SMALLINT(16) NOT NULL , `updateAble` TINYINT(1) NOT NULL , PRIMARY KEY (`ID`) ) ENGINE = InnoDB;



CREATE or replace VIEW `view_serial_comp_historical_supplier_invoice` AS
select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_historical_supplier_invoice` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 41))),1)) AS `nextSerialNo` from `company` `c`;


CREATE TABLE `comp_historical_customer_invoice` ( `ID` INT(11) NOT NULL AUTO_INCREMENT , `serialNo` INT(11) NOT NULL DEFAULT '1' , `companyID` INT(11) NOT NULL , `customerID` INT(11) NOT NULL , `formNo` VARCHAR(32) NOT NULL , `salesInvoiceDate` DATE NOT NULL , `customerPoNo` VARCHAR(32) NOT NULL , `taxID` INT(11) NOT NULL , `currencyID` INT(11) NOT NULL , `exchangeRate` DOUBLE NOT NULL , `amount` DOUBLE NOT NULL , `totalTaxAmount` DOUBLE NOT NULL , `totalReceivable` DOUBLE NOT NULL ,`total` DOUBLE NOT NULL , `totalAmount` DOUBLE NOT NULL , `createdBy` INT(11) NOT NULL , `createdTS` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `updatedBy` INT(11) NOT NULL , `updatedTS` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL , `is_delete` SMALLINT(6) NOT NULL , `updateAble` SMALLINT(6) NOT NULL DEFAULT '1' , PRIMARY KEY (`ID`) ) ENGINE = InnoDB;


CREATE or replace VIEW `view_serial_comp_historical_customer_invoice` AS
select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_historical_customer_invoice` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 41))),1)) AS `nextSerialNo` from `company` `c`;

drop TRIGGER if exists `trigger_before_insert_historicalsupinv_add_serialNo`;
CREATE TRIGGER `trigger_before_insert_historicalcusinv_add_serialNo` BEFORE INSERT ON `comp_historical_customer_invoice` FOR EACH ROW set new.serialNo = (select nextSerialNo from view_serial_comp_historical_customer_invoice where companyID = new.companyID);


INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`) VALUES ('389', 'ARHISTCUSINV', 'HistoricalCustomerInvoice', '86', '2', '0', 'historicalinvoice/historicalCustomerInvoice', '0', NULL, '10', '1', 'icon-active.png', 'EN');

INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`) VALUES ('390', 'APHISTSUPINV', 'HistoricalSupplierInvoice', '85', '3', '0', 'historicalinvoice/HistoricalSupplierInvoice', '0', NULL, '15', '1', 'icon-active.png', 'EN');



create or replace VIEW `view_bank_reconcile` AS 

SELECT `a`.`id`                                AS `ID`, 
       `a`.`companyid`                         AS `companyid`, 
       'comp_journal_detail'                   AS `tableName`, 
       `b`.`id`                                AS `bankid`, 
       Ifnull(`c`.`referrence`, c.description) AS `referenceNo`, 
       `c`.`effective_date`                    AS `transDate`, 
       `a`.`journalid`                         AS `formNo`, 
       `a`.`description`                       AS `description`, 
       `a`.`amount_dr`                         AS `debitAmount`, 
       `a`.`amount_cr`                         AS `creditAmount` 
FROM   comp_journal_detail a
         LEFT JOIN `comp_bank` `b` 
                ON `a`.`acctcode` = `b`.`linkaccountid` 
        LEFT JOIN `comp_journal` `c` 
               ON `c`.`journalid` = `a`.`journalid`  
                    AND `c`.`companyid` = `a`.`companyid` 
WHERE  `a`.`bankreconcileid` = 0
         AND `c`.`status` = 1;


create or replace VIEW `view_item_quantityonhand_confirm` AS 


SELECT `a`.`companyid`        AS `companyID`, 
       `a`.`locationid`       AS `locationID`, 
       `b`.`itemid`           AS `itemID`, 
       `b`.`itemcode`         AS `itemCode`, 
       `b`.`itemname`         AS `itemName`, 
       `b`.`umid`             AS `umID`, 
       Sum(`b`.`balanceitem`) AS `quantityOnHand` 
FROM   `comp_receive_item` `a` 
LEFT JOIN `comp_receive_item_detail` `b` ON `a`.`id` = `b`.`receiveid`

WHERE `a`.`updateable` = 0 AND `b`.`balanceitem` > 0
GROUP  BY `a`.`companyid`, 
          `a`.`locationid`, 
          `b`.`itemid`, 
          `b`.`itemcode`, 
          `b`.`itemname`, 
          `b`.`umid` 
          ;

create or replace VIEW `view_unpost_cash_purchase` AS SELECT
  `b`.`lineNo`            AS `lineNo`,   `b`.`quantity`          AS `quantity`,   `b`.`unitPrice`         AS `unitPrice`,
  `b`.`itemID`            AS `itemID`,   `b`.`itemdescription`   AS `itemdescription`,
  `b`.`umID`              AS `umID`,   `a`.`locationID`        AS `locationID`,   `a`.`ID`                AS `othersPaymentID`,
  CONCAT(CONCAT(CONCAT(`a`.`formNo`,'['),CONVERT(LPAD(`a`.`ID`,8,'0') USING latin1)),']') AS `formNo`,
  `a`.`companyID`         AS `companyID`,   NULL                    AS `supplierID`,
  NULL                    AS `purchaseInvoiceID`,   `b`.`ID`                AS `paymentDetailID`,
  NULL                    AS `supplierInvoiceNo`,   `b`.`lineNo`            AS `ID`,
  `b`.`itemname`          AS `itemName`,   `b`.`amountExcludedTax` AS `amountExcludedTax`,
  `b`.`taxID`             AS `taxid`,   `b`.`taxAmount`         AS `taxAmount`,
  `a`.`projectID`         AS `projectID`,   `c`.`itemTypeID`        AS `itemTypeID`,
IF((`c`.`itemTypeID` = 23),`c`.`cogsID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'uninvoiceAP')))) AS `drAccountID`,
    (SELECT      `comp_bank`.`linkAccountID`    FROM `comp_bank`    WHERE (`comp_bank`.`ID` = `a`.`bankId`)) AS `crAccountID`,
  (SELECT      `tax_master`.`taxAccount`    FROM `tax_master`    WHERE (`tax_master`.`ID` = `b`.`taxID`)) AS `drDiscountAccountID`,
  (SELECT      `comp_bank`.`linkAccountID`    FROM `comp_bank`    WHERE (`comp_bank`.`ID` = `a`.`bankId`)) AS `crDiscountAccountID` FROM ((`comp_payment_others` `a`
    JOIN `comp_payment_others_with_item` `b`)    LEFT JOIN `item` `c`      ON ((`c`.`ID` = `b`.`itemID`)))
WHERE ((`a`.`ID` = `b`.`otherPaymentID`)        AND (`a`.`updateAble` = 1)        AND (`a`.`is_delete` = 0));



create or replace VIEW `view_customer_statement` AS select `comp_sales_invoice`.`ID` AS `ID`,`comp_sales_invoice`.`ID` AS `parent`,`comp_sales_invoice`.`companyID` AS `companyID`,`comp_sales_invoice`.`customerID` AS `customerID`,'Invoice' AS `transType`,`comp_sales_invoice`.`salesInvoiceDate` AS `transDate`,`comp_sales_invoice`.`customerPoNo` AS `refNo`,concat(concat(concat(`comp_sales_invoice`.`formNo`,'['),convert(lpad(`comp_sales_invoice`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`comp_sales_invoice`.`totalamount` AS `dr`,NULL AS `cr`,1 AS `seq` from `comp_sales_invoice` where (`comp_sales_invoice`.`updateAble` = 0 and `comp_sales_invoice`.`is_delete` = 0)
union select `a`.`ID` AS `ID`,`a`.`salesInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,'DebitNote' AS `transType`,`a`.`debitNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`totalAmount` AS `dr`,NULL AS `cr`,2 AS `seq` from (`comp_sales_debitnote` `a` join `comp_sales_invoice` `b`) where ((`a`.`updateAble` = 0 and `a`.`is_delete` = 0) and (`b`.`ID` = `a`.`salesInvoiceID`))
union select `a`.`ID` AS `ID`,`a`.`salesInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,'CreditNote' AS `transType`,`a`.`creditNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`a`.`totalAmount` AS `cr`,3 AS `seq` from (`comp_sales_creditnote` `a` join `comp_sales_invoice` `b`) where ((`a`.`updateAble` = 0 and `a`.`is_delete` = 0) and (`b`.`ID` = `a`.`salesInvoiceID`))
union select `b`.`ID` AS `ID`,0 AS `parent`,`b`.`companyID` AS `companyID`,`b`.`customerID` AS `customerID`,'Payment' AS `transType`,`b`.`receivePaymentDate` AS `transDate`,`b`.`referenceNo` AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`b`.`totalReceived` AS `cr`,4 AS `seq` from `comp_receive_payment` `b` where (`b`.`updateAble` = 0 and `b`.`is_delete` = 0)
union select `b`.`ID` AS `ID`,0 AS `parent`,`b`.`companyID` AS `companyID`,`b`.`customerID` AS `customerID`,'Discount' AS `transType`,`b`.`receivePaymentDate` AS `transDate`,NULL AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`b`.`totalDiscount` AS `cr`,4 AS `seq` from `comp_receive_payment` `b` where (`b`.`totalDiscount` > 0 and `b`.`updateAble` = 0 and `b`.`is_delete` = 0)
union select `b`.`ID` AS `ID`,0 AS `parent`,`b`.`companyID` AS `companyID`,`b`.`customerID` AS `customerID`,'Historical Invoice' AS `transType`,`b`.`salesInvoiceDate` AS `transDate`,NULL AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`, b.totalAmount AS `dr`,null AS `cr`,1 AS `seq` from `comp_historical_customer_invoice` `b` where (`b`.`totalReceivable` > 0 and `b`.`updateAble` = 0 and `b`.`is_delete` = 0);



create or replace VIEW `view_supplier_statement` AS
select `comp_supplier_invoice`.`ID` AS `ID`,`comp_supplier_invoice`.`ID` AS `parent`,`comp_supplier_invoice`.`companyID` AS `companyID`,`comp_supplier_invoice`.`supplierID` AS `supplierID`,'Invoice' AS `transType`,`comp_supplier_invoice`.`invoiceDate` AS `transDate`,`comp_supplier_invoice`.`supplierInvoiceNo` AS `refNo`,concat(concat(concat(`comp_supplier_invoice`.`formNo`,'['),convert(lpad(`comp_supplier_invoice`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`comp_supplier_invoice`.`totalAmount` AS `cr`,1 AS `seq` from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))
union select `a`.`ID` AS `ID`,`a`.`supplierInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,'DebitNote' AS `transType`,`a`.`debitNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`totalAmount` AS `dr`,NULL AS `cr`,2 AS `seq` from (`comp_supplier_debitnote` `a` join `comp_supplier_invoice` `b`) where ((`a`.`updateAble` = 0) and (`a`.`is_delete` = 0) and (`a`.`supplierInvoiceID` = `b`.`ID`))
union select `a`.`ID` AS `ID`,`a`.`supplierInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,'CreditNote' AS `transType`,`a`.`creditNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`a`.`totalAmount` AS `cr`,3 AS `seq` from (`comp_supplier_creditnote` `a` join `comp_supplier_invoice` `b`) where ((`a`.`updateAble` = 0) and (`a`.`is_delete` = 0) and (`a`.`supplierInvoiceID` = `b`.`ID`))
union select `b`.`ID` AS `ID`,NULL AS `parent`,`b`.`companyID` AS `companyID`,`b`.`supplierID` AS `supplierID`,'Payment' AS `transType`,`b`.`paymentDate` AS `transDate`,`b`.`referenceNo` AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`b`.`amountPaid` AS `dr`,NULL AS `cr`,4 AS `seq` from `comp_payment` `b` where (`b`.`updateAble` = 0 and `b`.`is_delete` = 0)
union select `b`.`ID` AS `ID`,NULL AS `parent`,`b`.`companyID` AS `companyID`,`b`.`supplierID` AS `supplierID`,'Discount' AS `transType`,`b`.`paymentDate` AS `transDate`,`b`.`referenceNo` AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,
`b`.`totalDiscount` AS `dr`,NULL AS `cr`,4 AS `seq` from `comp_payment` `b` where (`b`.`updateAble` = 0 and `b`.`is_delete` = 0)
union select `ID` AS `ID`,`ID` AS `parent`,`companyID` AS `companyID`,`supplierID` AS `supplierID`,'Historical Invoice' AS `transType`,`invoiceDate` AS `transDate`,`supplierInvoiceNo` AS `refNo`,
concat(concat(concat(`formNo`,'['),convert(lpad(`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,
`totalPayable` AS `cr`,1 AS `seq` 
from comp_historical_supplier_invoice where ((`updateAble` = 0) and (`is_delete` = 0) and totalPayable > 0)
;


create or replace VIEW `view_supplier_aging` AS
select `a`.`companyID` AS `companyID`,`a`.`ID` AS `supplierID`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 0) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 0) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 0) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `current`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 1) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 1) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 1) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month1`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 2) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 2) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 2) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month2`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 3) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 3) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 3) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month3`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 4) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 4) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 4) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month4`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 5) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 5) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 5) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month5`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 6) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 6) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 6) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month6`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 7) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 7) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 7) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month7`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 8) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 8) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 8) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month8`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 9) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 9) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 9) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month9`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 10) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month10`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) > 10) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_supplier_debitnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) > 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) > 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month11`
from `supplier` `a`;



create or replace VIEW `view_bank_reconcile` AS

SELECT `a`.`id`                                    AS `ID`, 
       `a`.`companyid`                             AS `companyid`, 
       'comp_journal_detail'                       AS `tableName`, 
       `b`.`id`                                    AS `bankid`, 
       (select max(`referenceNo`) as referenceNo from view_journal_table where journalID =  `a`.`journalid` and companyID = a.companyID) AS `referenceNo`, 
       `c`.`effective_date`                        AS `transDate`, 
       `a`.`journalid`                             AS `formNo`, 
       `a`.`description`                           AS `description`, 
       `a`.`amount_dr`                             AS `debitAmount`, 
       `a`.`amount_cr`                             AS `creditAmount` 
FROM   `comp_journal_detail` `a` 
         LEFT JOIN `comp_bank` `b` 
                ON `a`.`acctcode` = `b`.`linkaccountid`
        LEFT JOIN `comp_journal` `c` 
                ON `c`.`journalid` = `a`.`journalid` 
                    AND `c`.`companyid` = `a`.`companyid` 
        
WHERE  `a`.`bankreconcileid` = 0
         AND `c`.`status` = 1;


create or replace VIEW `view_unpost_payment` AS 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,NULL AS `purchaseInvoiceID`,`b`.`supplierName` AS `supplierName`,0 AS `lineNo`,NULL AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`a`.`referenceNo` AS `item`,`a`.`bankCharges` AS `amtPaid_amtAppl`,0 AS `discount_bankCharges`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'bankchargesAccount'))) AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `crAccountID`,
NULL AS `drDiscountAccountID`,NULL AS `crDiscountAccountID` 
from (`comp_payment` `a` join `supplier` `b`) where ((`a`.`supplierID` = `b`.`ID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) 
union 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`b`.`purchaseInvoiceID` AS `purchaseInvoiceID`,`e`.`supplierName` AS `supplierName`,`b`.`lineNo` AS `lineNo`,`b`.`ID` AS `paymentDetailID`,`c`.`supplierInvoiceNo` AS `supplierInvoiceNo`,concat(concat(concat(`d`.`formNo`,'['),convert(lpad(`d`.`serialNo`,8,'0') using latin1)),']') AS `item`,
(`b`.`amountApplied` - `b`.`discount`) AS `amtPaid_amtAppl`,`b`.`discount` AS `discount_bankCharges`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `crAccountID`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drDiscountAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payDisc'))) AS `crDiscountAccountID` 
from (((`comp_payment` `a` join (`comp_payment_detail` `b` left join `comp_supplier_invoice` `d` on((`d`.`ID` = `b`.`purchaseInvoiceID`)))) join `comp_supplier_invoice` `c`) join `supplier` `e`) 
where ((`a`.`supplierID` = `e`.`ID`) and (`a`.`ID` = `b`.`paymentID`) and (`c`.`ID` = `b`.`purchaseInvoiceID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))
union 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`b`.`historicalInvoiceID` AS `purchaseInvoiceID`,`e`.`supplierName` AS `supplierName`,`b`.`ID` AS `lineNo`,`b`.`ID` AS `paymentDetailID`,`c`.`supplierInvoiceNo` AS `supplierInvoiceNo`,concat(concat(concat(`d`.`formNo`,'['),convert(lpad(`d`.`serialNo`,8,'0') using latin1)),']') AS `item`,
(`b`.`amountApplied` ) AS `amtPaid_amtAppl`,null AS `discount_bankCharges`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `crAccountID`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drDiscountAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payDisc'))) AS `crDiscountAccountID` 
from (((`comp_payment` `a` join (`comp_payment_detail_historical_invoice` `b` left join `comp_historical_supplier_invoice` `d` on((`d`.`ID` = `b`.`historicalInvoiceID`)))) join `comp_historical_supplier_invoice` `c`) join `supplier` `e`) 
where ((`a`.`supplierID` = `e`.`ID`) and (`a`.`ID` = `b`.`paymentID`) and (`c`.`ID` = `b`.`historicalInvoiceID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));



create or replace VIEW `view_unpost_ar_payment` AS select `a`.`ID` AS `paymentID`,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 `purchaseInvoiceID`,`b`.`customerName` AS `customerName`,0 AS `lineNo`,NULL AS `paymentDetailID`,`a`.`referenceNo` AS `item`,`a`.`bankCharges` AS `amtPaid_amtAppl`,0 AS `discount_bankCharges`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'bankchargesAccount'))) AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `crAccountID`,
NULL AS `drDiscountAccountID`,
NULL AS `crDiscountAccountID` 
from (`comp_receive_payment` `a` join `customer` `b`) 
where ((`a`.`customerID` = `b`.`ID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) 
union 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,`b`.`invoiceID` AS `invoiceID`,`e`.`customerName` AS `customerName`,`b`.`lineNo` AS `lineNo`,`b`.`ID` AS `paymentDetailID`,concat(concat(concat(`d`.`formNo`,'['),convert(lpad(`d`.`serialNo`,8,'0') using latin1)),']') AS `item`,(`b`.`amountApplied` - `b`.`discount`) AS `amtPaid_amtAppl`,`b`.`discount` AS `discount_bankCharges`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `drAccountID`,
(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `crAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'saledisacct'))) AS `drDiscountAccountID`,
(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `crDiscountAccountID` 
from (((`comp_receive_payment` `a` join (`comp_receive_payment_detail` `b` left join `comp_sales_invoice` `d` on((`d`.`ID` = `b`.`invoiceID`)))) join `comp_sales_invoice` `c`) join `customer` `e`) 
where ((`a`.`customerID` = `e`.`ID`) and (`a`.`ID` = `b`.`receivePaymentID`) and (`c`.`ID` = `b`.`invoiceID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))
union 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,`b`.`historicalInvoiceID` AS `invoiceID`,`e`.`customerName` AS `customerName`,`b`.`ID` AS `lineNo`,`b`.`ID` AS `paymentDetailID`,concat(concat(concat(`d`.`formNo`,'['),convert(lpad(`d`.`serialNo`,8,'0') using latin1)),']') AS `item`,(`b`.`amountApplied` ) AS `amtPaid_amtAppl`,null AS `discount_bankCharges`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `drAccountID`,
(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `crAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'saledisacct'))) AS `drDiscountAccountID`,
(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `crDiscountAccountID` 
from (((`comp_receive_payment` `a` join (`comp_receive_payment_detail_historical_invoice` `b` left join `comp_historical_customer_invoice` `d` on((`d`.`ID` = `b`.`historicalInvoiceID`)))) join `comp_historical_customer_invoice` `c`) join `customer` `e`) 
where ((`a`.`customerID` = `e`.`ID`) and (`a`.`ID` = `b`.`paymentID`) and (`c`.`ID` = `b`.`historicalInvoiceID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))

;


create or replace VIEW `view_comp_acc_det` AS 
        
select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`acctGroupID` AS `acctGroupID`,`a`.`acctID` AS `acctID`,`a`.`year` AS `year`,`a`.`period` AS `period`,
(select coalesce(sum(`comp_journal_detail`.`amount_dr`),0) from `comp_journal_detail` where ((`comp_journal_detail`.`companyID` = `a`.`companyID`) and (`comp_journal_detail`.`acctCode` = `a`.`acctID`) and (`comp_journal_detail`.`period` = `a`.`period`) and (`comp_journal_detail`.`year` = `a`.`year`) and (`comp_journal_detail`.`status` = 1) and (`comp_journal_detail`.`is_delete` = 0))) AS `amountdr`,
(select coalesce(sum(`comp_journal_detail`.`amount_cr`),0) from `comp_journal_detail` where ((`comp_journal_detail`.`companyID` = `a`.`companyID`) and (`comp_journal_detail`.`acctCode` = `a`.`acctID`) and (`comp_journal_detail`.`period` = `a`.`period`) and (`comp_journal_detail`.`year` = `a`.`year`) and (`comp_journal_detail`.`status` = 1) and (`comp_journal_detail`.`is_delete` = 0))) AS `amountcr`,
(select (coalesce(sum(`comp_journal_detail`.`amount_dr`),0) - coalesce(sum(`comp_journal_detail`.`amount_cr`),0)) from `comp_journal_detail` 
        where ((`comp_journal_detail`.`companyID` = `a`.`companyID`) 
                and (`comp_journal_detail`.`acctCode` = `a`.`acctID`) 
                and (`comp_journal_detail`.`period` = `a`.`period`) 
                and (`comp_journal_detail`.`year` = `a`.`year`) 
                and (`comp_journal_detail`.`status` = 1) 
                and (`comp_journal_detail`.`is_delete` = 0))) AS `total`,
`a`.`createdBy` AS `createdBy`,`a`.`createdTS` AS `createdTS`,`a`.`updatedBy` AS `updatedBy`,`a`.`updatedTS` AS `updatedTS`,`a`.`is_delete` AS `is_delete` 
from `comp_acc_det` `a`
where a.companyID > 0
        and a.acctID > 0
        and a.year > 0
        and a.period > 0;


create or replace VIEW `view_expence_and_income` AS 

select `a`.`companyID` AS `companyid`,`a`.`year` AS `year`,`a`.`period` AS `period`,`c`.`ID` AS `groupID`,`c`.`acctTypeID` AS `acctType`,
coalesce(sum(`a`.`amountdr`),0) AS `totalDr`,
coalesce(sum(`a`.`amountcr`),0) AS `totalCr`,
if((`c`.`acctTypeID` = 3),(coalesce(sum(`a`.`amountcr`),0) - coalesce(sum(`a`.`amountdr`),0)),(coalesce(sum(`a`.`amountdr`),0) - coalesce(sum(`a`.`amountcr`),0))) AS `balance` 
from ((`comp_acc_det` `a` join `comp_chart_of_acct` `b`) join `_ref_acct_group` `c`) 
where 
        ((`b`.`ID` = `a`.`acctID`) 
                and (`c`.`ID` = `b`.`acctGroupID`) 
                and (`c`.`acctTypeID` in (3,4)) 
                and (`c`.`title` = 1) 
                and (`a`.`period` > 0)) 
                and b.companyID > 0
group by `a`.`companyID`,`a`.`year`,`a`.`period`,`acctType`;


create or replace view view_journal_table as 
select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`referenceNo` AS `referenceNo`,'comp_cash_sales' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_cash_sales` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`referenceNo` AS `referenceNo`,'comp_payment' AS `comp_payment`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_payment` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`referenceNo` AS `referenceNo`,'comp_payment_others' AS `comp_payment_others`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_payment_others` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`formNo` AS `referenceNo`,'comp_purchase_order' AS `comp_purchase_order`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_purchase_order` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`referenceNo` AS `referenceNo`,'comp_receive_payment' AS `comp_receive_payment`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_receive_payment` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`referenceNo` AS `referenceNo`,'comp_receive_payment_others' AS `comp_receive_payment_others`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_receive_payment_others` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`b`.`formNo` AS `referenceNo`,'comp_sales_creditnote' AS `comp_sales_creditnote`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_sales_creditnote` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`b`.`formNo` AS `referenceNo`,'comp_sales_debitnote' AS `comp_sales_debitnote`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_sales_debitnote` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`customerPoNo` AS `referenceNo`,'comp_sales_invoice' AS `comp_sales_invoice`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_sales_invoice` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`b`.`formNo` AS `referenceNo`,'comp_supplier_creditnote' AS `comp_supplier_creditnote`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_supplier_creditnote` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`b`.`formNo` AS `referenceNo`,'comp_supplier_debitnote' AS `comp_supplier_debitnote`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_supplier_debitnote` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`supplierInvoiceNo` AS `referenceNo`,'comp_supplier_invoice' AS `comp_supplier_invoice`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_supplier_invoice` `a` join `comp_journal` `b`) where ((convert(`a`.`journalNo` using utf8) = `b`.`journalID`) and (`a`.`is_delete` = 0)) 
union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`b`.`formNo` AS `referenceNo`,'comp_delivery_order' AS `comp_delivery_order`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_delivery_order` `a` join `comp_journal` `b`) where (convert(`a`.`journalNo` using utf8) = `b`.`journalID`) union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`reference` AS `referenceNo`,'comp_gst_transaction' AS `comp_gst_transaction`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_gst_transaction` `a` join `comp_journal` `b`) where (convert(`a`.`journalNo` using utf8) = `b`.`journalID`) union select distinct `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`supplierDoNo` AS `referenceNo`,'comp_receive_item' AS `comp_receive_item`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo` from (`comp_receive_item` `a` join `comp_journal` `b`) where (convert(`a`.`journalNo` using utf8) = `b`.`journalID`);


ALTER TABLE `comp_historical_customer_invoice` CHANGE `updateAble` `updateAble` SMALLINT(6) NOT NULL DEFAULT '0';

INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`) VALUES
(391, 'GRAPHSET', 'graphsetup', '5', '1', 0, 'graph', '0', '0', 80, 1, 'iconcompanysetup.png', 'EN');

ALTER TABLE `comp_historical_customer_invoice` CHANGE `updateAble` `updateAble` SMALLINT(6) NOT NULL DEFAULT '0';

CREATE TABLE IF NOT EXISTS `comp_visual_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `graph_id` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `_ref_visual_report`
--

CREATE TABLE IF NOT EXISTS `_ref_visual_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `dimension` varchar(20) NOT NULL,
  `screenShoot` varchar(255) NOT NULL DEFAULT 'demographics.jpg',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `_ref_visual_report`
--

INSERT INTO `_ref_visual_report` (`id`, `name`, `slug`, `description`, `file_path`, `dimension`, `screenShoot`) VALUES
(1, 'Profit and Loss', 'profitLoss', 'Company profit and loss visual report', 'graph/profit-loss', '1x2', 'profit-n-loss.jpg'),
(2, 'Sales Unpaid Invoices', 'salesDue', 'Visual report of company sales invoices, those are not yet paid.', 'graph/sales-due', '1x2', 'sales-outstanding.jpg'),
(3, 'Due Purchase Amount', 'purchaseDue', 'Company Purchase invoices, those are not paid yet.', 'graph/purchase-due', '1x2', 'demographics.jpg'),
(4, 'Sales By Item', 'salesByItem', 'Sales information according to items.', 'graph/sales-by-item', '1x1', 'sales-by-items.jpg'),
(5, 'Sales By Customer', 'salesByCustomer', 'Sales information according to Customers', 'graph/sales-by-customer', '1x1', 'sales-by-customers.jpg'),
(6, 'GST 03 Summary', 'gst03summary', 'Company GST 03 Summary.', 'graph/gst03', '1x1', 'demographics.jpg');

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`,(select sum(`comp_sales_debt_recover`.`valueDebtRecover`) from `comp_sales_debt_recover` where ((`comp_sales_debt_recover`.`companyID` = `a`.`companyID`) and (`comp_sales_debt_recover`.`transDate` between `a`.`startDate` and `a`.`endDate`) and (`comp_sales_debt_recover`.`updateAble` = 0))) AS `C18` from (`comp_financial_year` `a` join `company` `b`) where ((`b`.`ID` = `a`.`companyID`) and (`a`.`is_delete` = 0))

CREATE TABLE IF NOT EXISTS `comp_gst_field_mapping` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `item` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `taxCode` varchar(255) NOT NULL,
  `changeable` tinyint(1) NOT NULL DEFAULT '1',
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=91 ;

CREATE TABLE IF NOT EXISTS `_ref_gst_03_field` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `taxCode` varchar(255) NOT NULL,
  `changeable` tinyint(1) NOT NULL DEFAULT '1',
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

--
-- Dumping data for table `_ref_gst_03_field`
--

INSERT INTO `_ref_gst_03_field` (`id`, `item`, `description`, `taxCode`, `changeable`, `createdTS`, `updatedTS`) VALUES
(1, 'B5a', 'Total Value of Standard Rated Supply', 'SR,DS', 1, '2015-07-10 01:17:43', '2015-07-10 06:53:15'),
(2, 'B5b', 'Total Output Tax (Inclusive of Tax Value on Bad Debt Recovered & other Adjustments)', 'SR,DS,AJS', 1, '2015-07-10 01:17:43', '2015-07-10 06:53:21'),
(3, 'B6a', 'Total Value of Standard Rate and Flat Rate Acquisitions', 'TX,IM,IMS,TX-E43,TX-RE', 1, '2015-07-10 01:18:11', '2015-07-10 06:53:31'),
(4, 'B6b', 'Total Input Tax (Inclusive of Tax Value on Bad Debt Relief & other Adjustments)', 'TX-CG,TX,IM,IMS,TX-E43,TX-RE,AJP', 1, '2015-07-10 01:18:11', '2015-07-10 06:53:44'),
(5, 'B7', 'GST Amount Payable (Item 5b - Item 6b)', '', 0, '2015-07-10 01:19:22', '2015-07-10 06:56:52'),
(6, 'B8', 'GST Amount Claimable (Item 6b - Item 5b)', '', 0, '2015-07-10 01:19:22', '2015-07-10 06:56:55'),
(7, 'C10', 'Total Value of Local Zero-Rated Supplies', 'ZRL', 1, '2015-07-10 01:19:37', '2015-07-10 06:54:18'),
(8, 'C11', 'Total Value of Export Supplies', 'ZRE', 1, '2015-07-10 01:19:37', '2015-07-10 06:54:29'),
(9, 'C12', 'Total Value of Exempt Supplies', 'ES43,ES', 1, '2015-07-10 01:19:52', '2015-07-10 06:54:33'),
(10, 'C13', 'Total Value of Supplies Granted GST Relief', 'RS', 1, '2015-07-10 01:19:52', '2015-07-10 06:54:38'),
(11, 'C14', 'Total Value of Goods Imported Under Approved Trader Scheme', 'IS', 1, '2015-07-10 01:20:11', '2015-07-10 06:54:47'),
(12, 'C15', 'Total Value of GST Suspended under item 14', '', 0, '2015-07-10 01:20:11', '2015-07-10 06:57:02'),
(13, 'C16', 'Total Value of Capital Goods Acquired', 'TX-CG', 1, '2015-07-10 01:20:28', '2015-07-10 06:54:48'),
(14, 'C17', 'Total Value of Bad Debt Relief Inclusive Tax', '', 0, '2015-07-10 01:20:28', '2015-07-10 06:57:06'),
(15, 'C18', 'Total Value of Bad Debt Recovered Inclusive Tax', '', 0, '2015-07-10 01:20:38', '2015-07-10 06:57:09');

INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`) VALUES
(391, 'GRAPHSET', 'graphsetup', '5', '1', 0, 'graph', '0', '0', 80, 1, 'iconcompanysetup.png', 'EN');

INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`) VALUES
(391, 'GRAPHSET', 'graphsetup', '5', '1', 0, 'graph', '0', '0', 80, 1, 'iconcompanysetup.png', 'EN');

create or replace VIEW `view_customer_aging` AS
select `a`.`companyID` AS `companyID`,`a`.`ID` AS `customerID`,`a`.`customerName` AS `customerName`,`a`.`phoneNumber1` AS `phoneNumber1`,`a`.`email` AS `email`,
(ifnull((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()) = 0) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 0) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 0) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `current`,
(ifnull((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()) = 1) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 1) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 1) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month1`,
(ifnull((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()) = 2) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 2) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 2) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month2`,
(ifnull((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()) = 3) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 3) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 3) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month3`,
(ifnull((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()) = 4) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 4) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 4) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month4`,
(ifnull((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()) = 5) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 5) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 5) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month5`,
(ifnull((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) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 6) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 6) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month6`,
(ifnull((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()) = 7) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 7) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 7) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month7`,
(ifnull((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()) = 8) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 8) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 8) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month8`,
(ifnull((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()) = 9) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 9) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 9) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month9`,
(ifnull((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()) = 10) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) = 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) = 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month10`,
(ifnull((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()) > 10) and (`comp_sales_invoice`.`updateAble` = 0) and (`comp_sales_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_sales_debitnote` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`debitNoteDate`,now()) > 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalReceivable`) from `comp_historical_customer_invoice` where ((`companyID` = `a`.`companyID`) and (`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`salesInvoiceDate`,now()) > 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month11`
from `customer` `a`;

 DROP TABLE IF EXISTS comp_gst_preference;

CREATE TABLE IF NOT EXISTS `comp_gst_preference` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `GSTTaxablePeriod` int(11) NOT NULL DEFAULT '1',
  `badDebtReliefMinMonth` int(11) NOT NULL DEFAULT '6',
  `MaxInputTaxClaim` int(11) NOT NULL DEFAULT '30',
  `DaysLimits` int(11) NOT NULL DEFAULT '21',
  `Totalexemptsuppliesnotexceed` int(11) NOT NULL,
  `TotalexemptsuppliesnotexceedPercentage` int(11) NOT NULL,
  `suppGSTregistrationVerifyFrequency` int(20) NOT NULL DEFAULT '1',
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


create or replace VIEW `view_supplier_aging` AS
select `a`.`companyID` AS `companyID`,`a`.`ID` AS `supplierID`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 0) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 0) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 0) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `current`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 1) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 1) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 1) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month1`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 2) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 2) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 2) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month2`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 3) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 3) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 3) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month3`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 4) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
 +ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 4) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 4) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month4`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 5) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 5) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 5) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month5`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 6) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
+ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 6) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 6) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month6`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 7) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
 +ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 7) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 7) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month7`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 8) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
 +ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 8) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 8) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month8`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 9) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
 +ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 9) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 9) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month9`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) = 10) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
 +ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) = 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) = 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month10`,
(ifnull((select sum(`comp_supplier_invoice`.`totalPayable`) from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`companyID` = `a`.`companyID`) and (`comp_supplier_invoice`.`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`comp_supplier_invoice`.`invoiceDate`,now()) > 10) and (`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))),0)
 +ifnull((select sum(`total`) from `comp_supplier_creditnote` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`creditNoteDate`,now()) > 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)
+ifnull((select sum(`totalPayable`) from `comp_historical_supplier_invoice` where ((`companyID` = `a`.`companyID`) and (`supplierID` = `a`.`ID`) and (timestampdiff(MONTH,`invoiceDate`,now()) > 10) and (`updateAble` = 0) and (`is_delete` = 0))),0)) AS `month11`
from `supplier` `a`;


create or replace VIEW `view_supplier_statement` AS
select `comp_supplier_invoice`.`ID` AS `ID`,`comp_supplier_invoice`.`ID` AS `parent`,`comp_supplier_invoice`.`companyID` AS `companyID`,`comp_supplier_invoice`.`supplierID` AS `supplierID`,'Invoice' AS `transType`,`comp_supplier_invoice`.`invoiceDate` AS `transDate`,`comp_supplier_invoice`.`supplierInvoiceNo` AS `refNo`,concat(concat(concat(`comp_supplier_invoice`.`formNo`,'['),convert(lpad(`comp_supplier_invoice`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`comp_supplier_invoice`.`totalAmount` AS `cr`,1 AS `seq` from `comp_supplier_invoice` where ((`comp_supplier_invoice`.`updateAble` = 0) and (`comp_supplier_invoice`.`is_delete` = 0))
union select `a`.`ID` AS `ID`,`a`.`supplierInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,'DebitNote' AS `transType`,`a`.`debitNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`totalAmount` AS `dr`,NULL AS `cr`,2 AS `seq` from (`comp_supplier_debitnote` `a` join `comp_supplier_invoice` `b`) where ((`a`.`updateAble` = 0) and (`a`.`is_delete` = 0) and (`a`.`supplierInvoiceID` = `b`.`ID`))
union select `a`.`ID` AS `ID`,`a`.`supplierInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,'CreditNote' AS `transType`,`a`.`creditNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`a`.`totalAmount` AS `cr`,3 AS `seq` from (`comp_supplier_creditnote` `a` join `comp_supplier_invoice` `b`) where ((`a`.`updateAble` = 0) and (`a`.`is_delete` = 0) and (`a`.`supplierInvoiceID` = `b`.`ID`))
union select `b`.`ID` AS `ID`,NULL AS `parent`,`b`.`companyID` AS `companyID`,`b`.`supplierID` AS `supplierID`,if(b.updateAble = 0,'Payment','** Payment') AS `transType`,`b`.`paymentDate` AS `transDate`,`b`.`referenceNo` AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`b`.`amountPaid` AS `dr`,NULL AS `cr`,4 AS `seq` from `comp_payment` `b` 
-- where (`b`.`updateAble` = 0 and `b`.`is_delete` = 0) 
-- need to include unconfirmed payment into supplier statement
where ( `b`.`is_delete` = 0)
union select `b`.`ID` AS `ID`,NULL AS `parent`,`b`.`companyID` AS `companyID`,`b`.`supplierID` AS `supplierID`,'Discount' AS `transType`,`b`.`paymentDate` AS `transDate`,`b`.`referenceNo` AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,
`b`.`totalDiscount` AS `dr`,NULL AS `cr`,4 AS `seq` from `comp_payment` `b` where (`b`.`updateAble` = 0 and `b`.`is_delete` = 0)
union select `ID` AS `ID`,`ID` AS `parent`,`companyID` AS `companyID`,`supplierID` AS `supplierID`,'Historical Invoice' AS `transType`,`invoiceDate` AS `transDate`,`supplierInvoiceNo` AS `refNo`,
concat(concat(concat(`formNo`,'['),convert(lpad(`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,
`totalAmount` AS `cr`,1 AS `seq` 
from comp_historical_supplier_invoice where ((`updateAble` = 0) and (`is_delete` = 0) )
;


update comp_supplier_invoice a,
comp_supplier_debitnote b
set a.totalPayable = a.totalPayable - b.totalAmount
where a.companyid = b.companyid
and a.ID = b.supplierInvoiceID
and b.totalAmount > 0; 

 update comp_sales_invoice a,
comp_sales_debitnote b
 set a.totalReceivable = a.totalReceivable - b.totalAmount
where a.companyid = b.companyid
and a.ID = b.salesInvoiceID
and b.totalAmount > 0; 


create or replace VIEW `view_customer_statement` AS select `comp_sales_invoice`.`ID` AS `ID`,`comp_sales_invoice`.`ID` AS `parent`,`comp_sales_invoice`.`companyID` AS `companyID`,`comp_sales_invoice`.`customerID` AS `customerID`,'Invoice' AS `transType`,`comp_sales_invoice`.`salesInvoiceDate` AS `transDate`,`comp_sales_invoice`.`customerPoNo` AS `refNo`,concat(concat(concat(`comp_sales_invoice`.`formNo`,'['),convert(lpad(`comp_sales_invoice`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`comp_sales_invoice`.`totalamount` AS `dr`,NULL AS `cr`,1 AS `seq` from `comp_sales_invoice` where (`comp_sales_invoice`.`updateAble` = 0 and `comp_sales_invoice`.`is_delete` = 0)
union select `a`.`ID` AS `ID`,`a`.`salesInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,'DebitNote' AS `transType`,`a`.`debitNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`totalAmount` AS `dr`,NULL AS `cr`,2 AS `seq` from (`comp_sales_debitnote` `a` join `comp_sales_invoice` `b`) where ((`a`.`updateAble` = 0 and `a`.`is_delete` = 0) and (`b`.`ID` = `a`.`salesInvoiceID`))
union select `a`.`ID` AS `ID`,`a`.`salesInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,'CreditNote' AS `transType`,`a`.`creditNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`a`.`totalAmount` AS `cr`,3 AS `seq` from (`comp_sales_creditnote` `a` join `comp_sales_invoice` `b`) where ((`a`.`updateAble` = 0 and `a`.`is_delete` = 0) and (`b`.`ID` = `a`.`salesInvoiceID`))
union select `b`.`ID` AS `ID`,0 AS `parent`,`b`.`companyID` AS `companyID`,`b`.`customerID` AS `customerID`,
if(b.updateAble = 0,'Payment','** Payment') AS `transType`,`b`.`receivePaymentDate` AS `transDate`,`b`.`referenceNo` AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`b`.`totalReceived` AS `cr`,4 AS `seq` 
from `comp_receive_payment` `b` where (`b`.`is_delete` = 0)
union select `b`.`ID` AS `ID`,0 AS `parent`,`b`.`companyID` AS `companyID`,`b`.`customerID` AS `customerID`,'Discount' AS `transType`,`b`.`receivePaymentDate` AS `transDate`,NULL AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`b`.`totalDiscount` AS `cr`,4 AS `seq` from `comp_receive_payment` `b` where (`b`.`totalDiscount` > 0 and `b`.`updateAble` = 0 and `b`.`is_delete` = 0)
union select `b`.`ID` AS `ID`,0 AS `parent`,`b`.`companyID` AS `companyID`,`b`.`customerID` AS `customerID`,'Historical Invoice' AS `transType`,`b`.`salesInvoiceDate` AS `transDate`,NULL AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`, b.totalAmount AS `dr`,null AS `cr`,1 AS `seq` from `comp_historical_customer_invoice` `b` where ( `b`.`updateAble` = 0 and `b`.`is_delete` = 0);



create or replace VIEW `view_unpost_payment` AS 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,NULL AS `purchaseInvoiceID`,`b`.`supplierName` AS `supplierName`,0 AS `lineNo`,NULL AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`a`.`referenceNo` AS `item`,`a`.`bankCharges` AS `amtPaid_amtAppl`,0 AS `discount_bankCharges`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'bankchargesAccount'))) AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `crAccountID`,
NULL AS `drDiscountAccountID`,NULL AS `crDiscountAccountID` 
from (`comp_payment` `a` join `supplier` `b`) where ((`a`.`supplierID` = `b`.`ID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) 
union 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`b`.`purchaseInvoiceID` AS `purchaseInvoiceID`,`e`.`supplierName` AS `supplierName`,`b`.`lineNo` AS `lineNo`,`b`.`ID` AS `paymentDetailID`,`c`.`supplierInvoiceNo` AS `supplierInvoiceNo`,concat(concat(concat(`d`.`formNo`,'['),convert(lpad(`d`.`serialNo`,8,'0') using latin1)),']') AS `item`,
(`b`.`amountApplied` - `b`.`discount`) AS `amtPaid_amtAppl`,`b`.`discount` AS `discount_bankCharges`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `crAccountID`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drDiscountAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payDisc'))) AS `crDiscountAccountID` 
from (((`comp_payment` `a` join (`comp_payment_detail` `b` left join `comp_supplier_invoice` `d` on((`d`.`ID` = `b`.`purchaseInvoiceID`)))) join `comp_supplier_invoice` `c`) join `supplier` `e`) 
where ((`a`.`supplierID` = `e`.`ID`) and (`a`.`ID` = `b`.`paymentID`) and (`c`.`ID` = `b`.`purchaseInvoiceID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))
union 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`b`.`historicalInvoiceID` AS `purchaseInvoiceID`,`e`.`supplierName` AS `supplierName`,`b`.`ID` AS `lineNo`,`b`.`ID` AS `paymentDetailID`,`c`.`supplierInvoiceNo` AS `supplierInvoiceNo`,concat(concat(concat(`d`.`formNo`,'['),convert(lpad(`d`.`serialNo`,8,'0') using latin1)),']') AS `item`,
(`b`.`amountApplied` ) AS `amtPaid_amtAppl`,null AS `discount_bankCharges`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `crAccountID`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drDiscountAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payDisc'))) AS `crDiscountAccountID` 
from (((`comp_payment` `a` join (`comp_payment_detail_historical_invoice` `b` left join `comp_historical_supplier_invoice` `d` on((`d`.`ID` = `b`.`historicalInvoiceID`)))) join `comp_historical_supplier_invoice` `c`) join `supplier` `e`) 
where ((`a`.`supplierID` = `e`.`ID`) and (`a`.`ID` = `b`.`paymentID`) and (`c`.`ID` = `b`.`historicalInvoiceID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))
union 
select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`b`.`creditnoteID` AS `purchaseInvoiceID`,`e`.`supplierName` AS `supplierName`,`b`.`ID` AS `lineNo`,`b`.`ID` AS `paymentDetailID`,`c`.`supplierInvoiceID` AS `supplierInvoiceNo`,concat(concat(concat(`d`.`formNo`,'['),convert(lpad(`d`.`serialNo`,8,'0') using latin1)),']') AS `item`,
(`b`.`amountApplied`) AS `amtPaid_amtAppl`,null AS `discount_bankCharges`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankID`)) AS `crAccountID`,
(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drDiscountAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payDisc'))) AS `crDiscountAccountID` 
from (((`comp_payment` `a` join (`comp_payment_detail_creditnote` `b` left join `comp_supplier_creditnote` `d` on((`d`.`ID` = `b`.`creditnoteID`)))) join `comp_supplier_creditnote` `c`) join `supplier` `e`) 
where ((`a`.`supplierID` = `e`.`ID`) and (`a`.`ID` = `b`.`paymentID`) and (`c`.`ID` = `b`.`creditnoteID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0))
;

