CREATE TABLE IF NOT EXISTS `comp_gst_transaction` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL,
  `companyID` int(11) NOT NULL,
  `formNo` varchar(255) NOT NULL,
  `vendor_cust_name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `gst_number` varchar(255) NOT NULL,
  `Description` text NOT NULL,
  `date` date NOT NULL,
  `reference` varchar(255) NOT NULL,
  `based_amount` double NOT NULL,
  `updateAble` tinyint(1) NOT NULL DEFAULT '1',
  `journalNo` varchar(255) NOT NULL,
  `isDelete` int(11) NOT NULL DEFAULT '0',
  `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_gst_transaction_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `gstTransID` int(11) NOT NULL,
  `accountID` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmountDr` double NOT NULL DEFAULT '0',
  `taxAmountCr` double NOT NULL DEFAULT '0',
  `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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

update _sys_menu set display = 0 where fldid = 14; 

update _sys_menu set display = 0 where fldid = 172; 

create or replace view 
`view_chart_of_acct_updateable` 
AS select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`acctGroupID` AS `acctGroupID`,`a`.`acctCode` AS `acctCode`,`a`.`acctName` AS `acctName`,`a`.`acctDesc` AS `acctDesc`,
if((((select count(`comp_journal_detail`.`ID`) from `comp_journal_detail` 
where (`comp_journal_detail`.`acctCode` = `a`.`ID`)) + (select count(`comp_opening_balance`.`ID`) from `comp_opening_balance` where (`comp_opening_balance`.`accountID` = `a`.`ID` and (comp_opening_balance.debit + comp_opening_balance.credit > 0)))) > 0),10,1) AS `updateable` 
from `comp_chart_of_acct` `a`;

update master_code set is_delete = 1 where id = 23;
	
ALTER TABLE `comp_supplier_invoice` CHANGE `amountPaid` `amountPaid` DOUBLE NOT NULL;

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

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


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



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


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

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

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

UPDATE _sys_menu SET seq = 81 WHERE fldid = 323;

UPDATE _sys_menu SET seq = 82 WHERE fldid = 379;

UPDATE _sys_menu SET seq = 83 WHERE fldid = 321;

UPDATE _sys_menu SET seq = 84 WHERE fldid = 380;

UPDATE _sys_menu SET seq = 85 WHERE fldid = 361;

UPDATE _sys_menu SET seq = 86 WHERE fldid = 362;

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


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