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,
`badDebtReliefMinMonth` int(11) NOT NULL,
`MaxInputTaxClaim` int(11) NOT NULL,
`DaysLimits` int(11) NOT NULL,
`Totalexemptsuppliesnotexceed` int(11) NOT NULL,
`TotalexemptsuppliesnotexceedPercentage` 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=1 ;

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=1 ;


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=1 ;


CREATE TABLE IF NOT EXISTS `comp_purchase_bad_debt` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`companyID` int(11) NOT NULL,
`supplierID` int(11) NOT NULL,
`valueBadDebt` double NOT NULL,
`gstBadDebt` double NOT NULL,
`transDate` date NOT NULL,
`updateAble` tinyint(1) NOT NULL DEFAULT '1',
`journalNo` varchar(255) NOT NULL,
`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=1 ;

CREATE TABLE IF NOT EXISTS `comp_purchase_debt_recover` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`companyID` int(11) NOT NULL,
`suppID` int(11) NOT NULL,
`valueDebtRecover` double NOT NULL,
`gstDebtRecover` double NOT NULL,
`transDate` date NOT NULL,
`updateAble` tinyint(1) NOT NULL DEFAULT '1',
`journalNo` varchar(255) NOT NULL,
`createdBy` int(11) NOT NULL,
`createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 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 ;


INSERT INTO `_sys_menu` (`fldid`, `code`, `name`, `parents`, `module`, `submenu`, `urls`, `urls2`, `access`, `seq`, `display`, `icons`, `language`) VALUES
(384, 'SPBDRCNF', 'suppbadDebtRecoverConfirm', '151', '6', 0, 'gstreport/suppDebtRecoverConfirm', '0', NULL, 20, 1, 'icon-active.png', 'EN'),
(383, 'SBDEBRCV', 'suppbadDebtRecover', '151', '6', 0, 'gstreport/suppbadDebtRecover', '0', NULL, 19, 1, 'icon-active.png', 'EN'),
(382, 'SPBDRLC', 'suppbadDebtreliefconfirm', '151', '6', 0, 'gstreport/suppbadDebtreliefconfirm', '0', NULL, 18, 1, 'icon-active.png', 'EN'),
(381, 'SUPBDRLF', 'supplierbadDebtrelief', '151', '6', 0, 'gstreport/supplierbadDebtrelief', '0', NULL, 17, 1, 'icon-active.png', 'EN');



ALTER TABLE `comp_supplier_invoice` ADD `badDebtID` INT(11) NOT NULL AFTER `journalNo`;

ALTER TABLE `comp_payment_detail` ADD `debtRecoverID` INT(11) NOT NULL AFTER `amountApplied`;

UPDATE _sys_menu SET code = 'GSTRTSUM' WHERE fldid = 379;


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

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`,
(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`.`is_delete` = 0))) AS `current`,
(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`.`is_delete` = 0))) AS `month1`,
(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`.`is_delete` = 0))) AS `month2`,
(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`.`is_delete` = 0))) AS `month3`,
(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`.`is_delete` = 0))) AS `month4`,
(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`.`is_delete` = 0))) AS `month5`,
(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`.`is_delete` = 0))) AS `month6`,
(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`.`is_delete` = 0))) AS `month7`,
(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`.`is_delete` = 0))) AS `month8`,
(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`.`is_delete` = 0))) AS `month9`,
(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`.`is_delete` = 0))) AS `month10`,
(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`.`is_delete` = 0))) AS `month11`
from `customer` `a`;


create or replace VIEW `view_supplier_aging` AS
select `a`.`companyID` AS `companyID`,`a`.`ID` AS `supplierID`,
(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))) AS `current`,
(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))) AS `month1`,
(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))) AS `month2`,
(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))) AS `month3`,
(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))) AS `month4`,
(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))) AS `month5`,
(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))) AS `month6`,
(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))) AS `month7`,
(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))) AS `month8`,
(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))) AS `month9`,
(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))) AS `month10`,
(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))) 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`,'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);


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` from ((((((((`comp_sales_invoice` `a` join `comp_sales_invoice_detail` `b`) join `customer` `d`) join `item` `e`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`invoiceID`) and (`a`.`customerID` = `d`.`ID`) and (`e`.`ID` = `b`.`itemID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from (((((((`comp_sales_invoice` `a` join `comp_sales_invoice_charges` `b`) join `customer` `d`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`invoiceID`) and (`a`.`customerID` = `d`.`ID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from ((((((((`comp_sales_creditnote` `a` join `comp_sales_creditnote_detail` `b`) join `customer` `d`) join `item` `e`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`creditnoteID`) and (`a`.`customerID` = `d`.`ID`) and (`e`.`ID` = `b`.`itemID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from (((((((`comp_sales_creditnote` `a` join `comp_sales_creditnote_charges` `b`) join `customer` `d`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`creditNoteID`) and (`a`.`customerID` = `d`.`ID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from ((((((((`comp_sales_debitnote` `a` join `comp_sales_debitnote_detail` `b`) join `customer` `d`) join `item` `e`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`debitnoteID`) and (`a`.`customerID` = `d`.`ID`) and (`e`.`ID` = `b`.`itemID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from (((((((`comp_sales_debitnote` `a` join `comp_sales_debitnote_charges` `b`) join `customer` `d`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`debitNoteID`) and (`a`.`customerID` = `d`.`ID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from ((((((((`comp_supplier_invoice` `a` join `comp_supplier_invoice_detail` `b`) join `supplier` `d`) join `item` `e`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`invoiceID`) and (`a`.`supplierID` = `d`.`ID`) and (`e`.`ID` = `b`.`itemID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from (((((((`comp_supplier_invoice` `a` join `comp_supplier_invoice_charges` `b`) join `supplier` `d`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`invoiceID`) and (`a`.`supplierID` = `d`.`ID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from ((((((((`comp_supplier_creditnote` `a` join `comp_supplier_creditnote_detail` `b`) join `supplier` `d`) join `item` `e`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`creditnoteID`) and (`a`.`supplierID` = `d`.`ID`) and (`e`.`ID` = `b`.`itemID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from (((((((`comp_supplier_creditnote` `a` join `comp_supplier_creditnote_charges` `b`) join `supplier` `d`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`creditNoteID`) and (`a`.`supplierID` = `d`.`ID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from ((((((((`comp_supplier_debitnote` `a` join `comp_supplier_debitnote_detail` `b`) join `supplier` `d`) join `item` `e`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`debitnoteID`) and (`a`.`supplierID` = `d`.`ID`) and (`e`.`ID` = `b`.`itemID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from (((((((`comp_supplier_debitnote` `a` join `comp_supplier_debitnote_charges` `b`) join `supplier` `d`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`debitNoteID`) and (`a`.`supplierID` = `d`.`ID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`d`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from ((((((`comp_payment_others` `a` join `comp_payment_others_without_item` `b`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`otherPaymentID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from (((((((`comp_cash_sales` `a` join `comp_cash_sales_detail` `b`) join `item` `e`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`invoiceID`) and (`e`.`ID` = `b`.`itemID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`e`.`companyID` = `a`.`companyID`) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`lineNo` 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` from ((((((`comp_receive_payment_others` `a` join `comp_receive_payment_others_detail` `b`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`receivePaymentOthersID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8)) and (`j`.`status` = 1)) union select `b`.`ID` AS `lineNo`,`a`.`companyID` AS `companyID`,`f`.`taxClass` AS `taxClass`,`h`.`description` AS `taxClassName`,`f`.`taxGroup` AS `taxGroup`,`i`.`description` AS `taxGroupName`,`f`.`taxType` AS `taxType`,`g`.`description` AS `taxTypeName`,`a`.`type` AS `transType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `ref`,`a`.`date` AS `transDate`,NULL AS `supplierID`,`a`.`vendor_cust_name` AS `supplierName`,`b`.`description` AS `itemName`,if((`a`.`reverse` > 0),(`a`.`based_amount` * -(1)),`a`.`based_amount`) AS `amountExcludedTax`,`b`.`taxRate` AS `taxRate`,if((`a`.`reverse` > 0),(if((`b`.`taxAmountDr` > 0),`b`.`taxAmountDr`,`b`.`taxAmountCr`) * -(1)),if((`b`.`taxAmountDr` > 0),`b`.`taxAmountDr`,`b`.`taxAmountCr`)) AS `taxAmount`,if((`a`.`reverse` > 0),((if((`b`.`taxAmountDr` > 0),`b`.`taxAmountDr`,`b`.`taxAmountCr`) + `a`.`based_amount`) * -(1)),(if((`b`.`taxAmountDr` > 0),`b`.`taxAmountDr`,`b`.`taxAmountCr`) + `a`.`based_amount`)) AS `amountIncludedTax`,`b`.`taxID` AS `taxID`,`f`.`code` AS `gst` from ((((((`comp_gst_transaction` `a` join `comp_gst_transaction_detail` `b`) join `tax_master` `f`) join `tax_type` `g`) join `tax_class` `h`) join `tax_group` `i`) join `comp_journal` `j`) where ((`a`.`ID` = `b`.`gstTransID`) and (`f`.`ID` = `b`.`taxID`) and (`f`.`taxType` = `g`.`ID`) and (`f`.`taxClass` = `h`.`ID`) and (`f`.`taxGroup` = `i`.`ID`) and (`f`.`taxClass` = 1) and (`b`.`taxID` > 0) and (`a`.`updateAble` = 0) and (`f`.`companyID` = `a`.`companyID`) and (`j`.`companyID` = `a`.`companyID`) and (`j`.`journalID` = convert(`a`.`journalNo` using utf8))) union select `b`.`lineNo` 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` 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)) 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` 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` 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` 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` 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_serial_comp_gst_transaction as
select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_gst_transaction` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 118))),1)) AS `nextSerialNo` from `company` `c`;

CREATE OR REPLACE VIEW view_unpost_gst_transaction 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`,NULL AS `supplierID`,NULL AS `purchaseInvoiceID`,concat(`a`.`Description`,'[',`a`.`vendor_cust_name`,']') AS `supplierName`,0 AS `lineNo`,NULL AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`a`.`reference` AS `item`,`b`.`taxAmountDr` AS `amtPaid_amtAppl`,0 AS `discount_bankCharges`,`b`.`accountID` AS `drAccountID`,(select `comp_gst_transaction_detail`.`accountID` from `comp_gst_transaction_detail` where ((`comp_gst_transaction_detail`.`gstTransID` = `a`.`ID`) and (`comp_gst_transaction_detail`.`taxAmountCr` > 0))) AS `crAccountID`,NULL AS `drDiscountAccountID`,NULL AS `crDiscountAccountID` from `comp_gst_transaction` `a` join `comp_gst_transaction_detail` `b` where ((`a`.`ID` = `b`.`gstTransID`) and (`b`.`taxAmountDr` > 0) and (`a`.`updateAble` = 1) and (`a`.`isDelete` = 0));


CREATE TRIGGER `trir_before_insert_GST` BEFORE INSERT ON `comp_gst_transaction`
FOR EACH ROW set new.serialNo = (select nextSerialNo
from view_serial_comp_gst_transaction
where companyID = new.companyID);

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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) 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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) 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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) 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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) 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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) 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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) 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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) 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 coalesce(sum(`view_gst_tax`.`taxAmount`),0) 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));

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


