
CREATE TABLE IF NOT EXISTS `tax_class` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) DEFAULT NULL,
  `shortName` varchar(10) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  `taxAccount` int(11) DEFAULT NULL,
  `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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `tax_class`
--

INSERT INTO `tax_class` (`ID`, `code`, `shortName`, `description`, `taxAccount`, `createdBy`, `createdTS`, `updatedBy`, `updatedTS`) VALUES
(1, 'GST', 'GST', 'Goods And Services Tax', NULL, NULL, '2014-05-08 19:04:42', NULL, '2014-05-10 09:24:32'),
(2, 'OTH', 'OTHER TAX', 'Other Tax', NULL, NULL, '2014-05-08 19:05:22', NULL, '2014-05-08 19:05:55');

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

--
-- Table structure for table `tax_group`
--

CREATE TABLE IF NOT EXISTS `tax_group` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  `taxAccount` int(11) DEFAULT NULL,
  `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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=3 ;

--
-- Dumping data for table `tax_group`
--

INSERT INTO `tax_group` (`ID`, `code`, `description`, `taxAccount`, `createdBy`, `createdTS`, `updatedBy`, `updatedTS`) VALUES
(1, 'PI', 'PURCHASE/INPUT', NULL, NULL, '2014-05-08 19:06:33', NULL, '2014-08-26 20:24:37'),
(2, 'SO', 'SALES/OUTPUT', NULL, NULL, '2014-05-08 19:06:50', NULL, '2014-05-08 19:06:50');

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

--
-- Table structure for table `tax_master`
--

CREATE TABLE IF NOT EXISTS `tax_master` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `taxType` int(11) DEFAULT NULL,
  `taxPercentage` double DEFAULT NULL,
  `taxClass` int(11) DEFAULT NULL,
  `taxGroup` int(11) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  `taxAccount` int(11) DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `createdBy` int(11) DEFAULT '0',
  `createdTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) DEFAULT NULL,
  `updatedTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `FK_tax_master_tax_class` (`taxClass`),
  KEY `FK_tax_master_tax_group` (`taxGroup`),
  KEY `FK_tax_master_tax_type` (`taxType`),
  KEY `FK_tax_master_code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2694 ;

--
-- Dumping data for table `tax_master`
--

INSERT INTO `tax_master` (`ID`, `code`, `name`, `taxType`, `taxPercentage`, `taxClass`, `taxGroup`, `description`, `taxAccount`, `status`, `createdBy`, `createdTS`, `updatedBy`, `updatedTS`) VALUES
(1, 'SR', 'STANDARD RATED SUPPLIES WITH GST CHARGED', 1, 6, 1, 2, 'STANDARD RATED SUPPLIES WITH GST CHARGED', 261, 0, 0, '2014-05-10 07:56:37', NULL, '2014-08-18 12:56:55'),
(2, 'DS', 'DEEMED SUPPLIES', 1, 6, 1, 2, 'DEEMED SUPPLIES', 261, 0, 0, '2014-05-10 07:56:37', NULL, '2014-08-18 12:56:55'),
(3, 'ZRL', 'ZERO RATED SUPPLIES-LOCAL', 2, 0, 1, 2, 'ZERO RATED SUPPLIES-LOCAL', 261, 0, 0, '2014-05-10 07:56:37', NULL, '2014-08-18 12:56:55'),
(4, 'ZRE', 'EXPORTED GOOD SUPPLIES', 3, 0, 1, 2, 'EXPORTED GOOD SUPPLIES', 261, 0, 0, '2014-05-10 03:02:03', NULL, '2014-08-18 12:56:55'),
(5, 'ES43', 'INCIDENTAL EXEMPT SUPPLIES', 4, 0, 1, 2, 'INCIDENTAL EXEMPT SUPPLIES', 261, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(6, 'ES', 'EXEMPT SUPPLIES UNDER GST', 4, 0, 1, 2, 'EXEMPT SUPPLIES UNDER GST', 261, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(7, 'OS', 'OUT-OF-SCOPE SUPPLIES', 5, 0, 1, 2, 'OUT-OF-SCOPE SUPPLIES', 261, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(8, 'RS', 'RELIEF SUPPLIES UNDER GST', 6, 0, 1, 2, 'RELIEF SUPPLIES UNDER GST', 261, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(9, 'GS', 'DISREGARDED SUPPLIES', 7, 0, 1, 2, 'DISREGARDED SUPPLIES', 261, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(10, 'AJS', 'ANY ADJUSTMENT MADE TO OUTPUT TAX', 8, 6, 1, 2, 'ANY ADJUSTMENT MADE TO OUTPUT TAX', 261, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(11, 'ST-MF', 'SALES TAX FOR GOODS MANUFACTURING ', 9, 10, 2, 2, 'SALES TAX FOR GOODS MANUFACTURING ', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(12, 'ST', 'SALES TAX FOR GENERAL', 9, 5, 2, 2, 'SALES TAX FOR GENERAL', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(13, 'SERT', 'SERVICE TAX FOR GENERAL', 10, 6, 2, 2, 'SERVICE TAX FOR GENERAL', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:56:55'),
(14, 'TX', 'PURCHASE WITH GST INCURRED AT 6% AND DIRECTLY ATTRIBUTABLE TO TAXABLE SUPPLIES', 11, 6, 1, 1, 'PURCHASE WITH GST INCURRED AT 6% AND DIRECTLY ATTRIBUTABLE TO TAXABLE SUPPLIES', 32, 0, 0, '2014-05-11 09:36:01', 16, '2014-09-22 17:38:47'),
(15, 'IM', 'GST INCURRED FOR IMPORT OF GOODS', 11, 6, 1, 1, 'GST INCURRED FOR IMPORT OF GOODS', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(16, 'IMS', 'GST INCURRED FOR IMPORT OF SERVICES (REVERSED MECHANISM)', 11, 6, 1, 1, 'GST INCURRED FOR IMPORT OF SERVICES (REVERSED MECHANISM)', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(17, 'IS', 'IMPORTED UNDER SPECIAL SCHEME WITH NO GST INCURRED', 12, 0, 1, 1, 'IMPORTED UNDER SPECIAL SCHEME WITH NO GST INCURRED', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(18, 'BL', 'PURCHASE WITH GST INCURRED BUT NOT CLAIMBLE', 13, 6, 1, 1, 'PURCHASE WITH GST INCURRED BUT NOT CLAIMBLE', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(19, 'NR', 'PURCHASE FROM NON GST-REGISTERED SUPPLIER WITH NO GST INCURRED', 14, 0, 1, 1, 'PURCHASE FROM NON GST-REGISTERED SUPPLIER WITH NO GST INCURRED', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(20, 'ZP', 'PURCHASE FROM GST-REGISTERED SUPPLIER WITH NO GST INCURRED', 15, 0, 1, 1, 'PURCHASE FROM GST-REGISTERED SUPPLIER WITH NO GST INCURRED', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(21, 'EP', 'PURCHASE EXEMPTED FROM GST', 16, 0, 1, 1, 'PURCHASE EXEMPTED FROM GST', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(22, 'OP', 'PURCHASE TRANSACTIONS WHICH IS OUT OF THE SCOPE OF GST LEGISLATION', 5, 0, 1, 1, 'PURCHASE TRANSACTIONS WHICH IS OUT OF THE SCOPE', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(23, 'TX-E43', 'GST INCURRED DIRECTLY ATTRIBUTABLE TO INCIDENTAL EXEMPT SUPPLIES', 11, 6, 1, 1, 'GST INCURRED DIRECTLY ATTRIBUTABLE TO INCIDENTAL EXEMPT SUPPLIES', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(24, 'TX-N43', 'GST  INCURRED DIRECTLY ATTRIBUTABLE TO NON-INCIDENTAL EXEMPT SUPPLIES', 11, 6, 1, 1, 'GST  INCURRED DIRECTLY ATTRIBUTABLE TO NON-INCIDENTAL EXEMPT SUPPLIES', 78, 0, 0, '2014-05-11 09:36:01', NULL, '2014-09-18 15:39:40'),
(25, 'TX-RE', 'GST INCURRED THAT IS NOT DIRECTLY ATTRIBUTABLE TO TAXABLE OR EXEMPT SUPPLIES', 11, 6, 1, 1, 'GST INCURRED THAT IS NOT DIRECTLY ATTRIBUTABLE TO TAXABLE OR EXEMPT SUPPLIES', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:30:07'),
(26, 'GP', 'PURCHASE TRANSACTION WHICH LEGISLATION', 19, 0, 1, 1, 'PURCHASE TRANSACTION WHICH LEGISLATION', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:36:52'),
(27, 'AJP', 'ANY ADJUSTMENT MADE TO INPUT TAX', 20, 6, 1, 1, 'ANY ADJUSTMENT MADE TO INPUT TAX', 255, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-18 12:45:40'),
(28, 'ST-1', 'SALES TAX FOR GOODS MANUFACTURING ', 9, 10, 2, 1, 'SALES TAX FOR GOODS MANUFACTURING ', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:36:03'),
(29, 'ST-2', 'SALES TAX FOR GENERAL', 9, 10, 2, 1, 'SALES TAX FOR GENERAL', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:36:03'),
(30, 'SERT-1', 'SERVICE TAX FOR GENERAL', 10, 6, 2, 1, 'SERVICE TAX FOR GENERAL', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:36:03'),
(31, 'IMPD-1', 'IMPORT DUTY FOR CUSTOMS IMPORTED GOODS', 17, 0, 2, 1, 'IMPORT DUTY FOR CUSTOMS IMPORTED GOODS', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:36:03'),
(32, 'IMPD-2', 'IMPORT DUTY FOR CUSTOMS OPEN AP', 17, 0, 2, 1, 'IMPORT DUTY FOR CUSTOMS OPEN AP', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:36:03'),
(33, 'IMPD-3', 'IMPORT DUTY FOR CUTOMS FRANCHAISE AP', 17, 0, 2, 1, 'IMPORT DUTY FOR CUTOMS FRANCHAISE AP', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:36:03'),
(34, 'EXD', 'EXCISE DUTY CUSTOMS', 18, 0, 2, 1, 'EXCISE DUTY CUSTOMS', 0, 0, 0, '2014-05-11 09:36:01', NULL, '2014-08-11 12:36:03');

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

--
-- Table structure for table `tax_type`
--

CREATE TABLE IF NOT EXISTS `tax_type` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) DEFAULT NULL,
  `taxGroupID` int(11) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  `taxClass` int(11) NOT NULL,
  `taxAccount` int(11) DEFAULT NULL,
  `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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=21 ;

--
-- Dumping data for table `tax_type`
--

INSERT INTO `tax_type` (`ID`, `code`, `taxGroupID`, `description`, `taxClass`, `taxAccount`, `createdBy`, `createdTS`, `updatedBy`, `updatedTS`) VALUES
(1, 'STD', 2, 'STANDARD RATED SUPPLIES', 1, NULL, NULL, '2014-05-08 19:04:42', NULL, '2014-08-18 16:18:43'),
(2, 'ZRS', 2, 'ZERO RATED SUPPLIES', 1, NULL, NULL, '2014-05-08 19:05:22', NULL, '2014-08-18 16:18:43'),
(3, 'EGS', 2, 'EXPORTED GOOD SUPPLIES', 1, NULL, NULL, '2014-05-08 19:10:04', NULL, '2014-08-18 16:18:43'),
(4, 'ESS', 2, 'EXEMPT SUPPLIES', 1, NULL, NULL, '2014-05-08 19:10:18', NULL, '2014-08-18 16:18:43'),
(5, 'OOS', 2, 'OUT OF SCOPE', 1, NULL, NULL, '2014-05-08 19:10:52', NULL, '2014-08-18 16:18:43'),
(6, 'RES', 2, 'RELIEF SUPPLIES', 1, NULL, NULL, '2014-05-08 19:11:12', NULL, '2014-08-18 16:18:43'),
(7, 'DRS', 2, 'DISREGARDED SUPPLIES', 1, NULL, NULL, '2014-05-08 19:11:47', NULL, '2014-08-18 16:18:43'),
(8, 'OTD', 2, 'OUTPUT TAX ADJUSTMENT', 1, NULL, NULL, '2014-05-08 19:11:58', NULL, '2014-08-18 16:18:43'),
(9, 'SLT', 2, 'SALES TAX', 2, NULL, NULL, '2014-05-08 19:12:53', NULL, '2014-08-18 16:32:02'),
(10, 'SVT', 2, 'SERVICE TAX', 2, NULL, NULL, '2014-05-08 19:13:11', NULL, '2014-08-18 16:34:09'),
(11, 'SRA', 1, 'STANDARD RATED ACQUISITION', 1, NULL, NULL, '2014-05-08 19:13:33', NULL, '2014-08-18 16:18:43'),
(12, 'SPS', 1, 'SPECIAL SCHEME', 1, NULL, NULL, '2014-05-08 19:13:51', NULL, '2014-08-18 16:18:43'),
(13, 'BIT', 1, 'BLOCK INPUT TAX', 1, NULL, NULL, '2014-05-08 19:14:09', NULL, '2014-08-18 16:18:43'),
(14, 'NGR', 1, 'NON GST REGISTERED', 1, NULL, NULL, '2014-05-08 19:14:24', NULL, '2014-08-18 16:18:43'),
(15, 'ZRA', 1, 'ZERO RATED ACQUISITION', 1, NULL, NULL, '2014-05-08 19:14:49', NULL, '2014-08-18 16:18:43'),
(16, 'EXA', 1, 'EXEMPT ACQUISITION', 1, NULL, NULL, '2014-05-08 19:15:06', NULL, '2014-08-18 16:18:43'),
(17, 'IMD', 1, 'IMPORT DUTY', 2, NULL, NULL, '2014-05-08 19:15:42', NULL, '2014-08-18 16:27:38'),
(18, 'EXD', 1, 'EXCISE DUTY', 2, NULL, NULL, '2014-05-08 19:15:58', NULL, '2014-08-18 16:27:31'),
(19, 'DP', 1, 'DISREGARDED PURCHASE', 1, NULL, NULL, '2014-08-18 12:34:01', NULL, '2014-08-18 16:18:43'),
(20, 'ITA', 1, 'INPUT TAX ADJUSTMENT', 1, NULL, NULL, '2014-08-18 12:42:35', NULL, '2014-08-18 16:18:43');


CREATE TABLE IF NOT EXISTS `comp_gst_report` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) DEFAULT NULL,
  `ammendment` int(2) DEFAULT '0',
  `A1` varchar(12) NOT NULL DEFAULT '0',
  `A2` varchar(64) NOT NULL DEFAULT '0',
  `B3a` date DEFAULT NULL,
  `B3b` date DEFAULT NULL,
  `B4` date DEFAULT NULL,
  `B5a` double DEFAULT NULL,
  `B5b` double DEFAULT NULL,
  `B6a` double DEFAULT NULL,
  `B6b` double DEFAULT NULL,
  `B7` double DEFAULT NULL,
  `B8` double DEFAULT NULL,
  `B9` double DEFAULT NULL,
  `C10` double DEFAULT NULL,
  `C11` double DEFAULT NULL,
  `C12` double DEFAULT NULL,
  `C13` double DEFAULT NULL,
  `C14` double DEFAULT NULL,
  `C15` double DEFAULT NULL,
  `C16` double DEFAULT NULL,
  `C17` double DEFAULT NULL,
  `C18` double DEFAULT NULL,
  `C19a1` varchar(32) DEFAULT NULL,
  `C19a2` double DEFAULT NULL,
  `C19b1` varchar(32) DEFAULT NULL,
  `C19b2` double DEFAULT NULL,
  `C19c1` varchar(32) DEFAULT NULL,
  `C19c2` double DEFAULT NULL,
  `C19d1` varchar(32) DEFAULT NULL,
  `C19d2` double DEFAULT NULL,
  `C19e1` varchar(32) DEFAULT NULL,
  `C19e2` double DEFAULT NULL,
  `C19f` double DEFAULT NULL,
  `C19g` double DEFAULT NULL,
  `D20` varchar(128) DEFAULT NULL,
  `D21a` varchar(16) DEFAULT NULL,
  `D21b` varchar(16) DEFAULT NULL,
  `D22` varchar(32) DEFAULT NULL,
  `D23` varchar(128) DEFAULT NULL,
  `D24` date DEFAULT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updateAble` tinyint(4) NOT NULL DEFAULT '1',
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `comp_cash_out_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL COMMENT 'link to table company',
  `supplierID` int(11) NOT NULL COMMENT 'connect table supplier; refer to company no in the form',
  `payTo` text NOT NULL,
  `paymentMethodID` int(11) NOT NULL COMMENT '0 for cash, 1 for check',
  `referenceNo` varchar(32) NOT NULL,
  `accountID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `billNo` varchar(16) NOT NULL,
  `paymentDate` date NOT NULL,
  `updateAble` smallint(1) NOT NULL DEFAULT '1',
  `memo` text NOT NULL,
  `totalExcludingtax` double NOT NULL,
  `totalTaxamount` double NOT NULL,
  `totalIncludingtax` double NOT NULL,
  `totalPaid` double 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,
  `is_delete` smallint(16) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `comp_cash_out_detail_item_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `cashoutid` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `itemname` varchar(64) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unitPrice` double NOT NULL,
  `amountExcludedTax` double NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmount` double NOT NULL,
  `amountIncludedTax` double NOT NULL,
  `accountID` int(11) 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',
  `is_delete` smallint(16) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `comp_cash_out_detail_service_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `cashoutid` int(11) NOT NULL,
  `accountID` int(11) NOT NULL,
  `description` text NOT NULL,
  `amountExcludedTax` double NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmount` double NOT NULL,
  `amountIncludedTax` double 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,
  `is_delete` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `comp_cash_in_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL COMMENT 'connect table supplier; refer to company no in the form',
  `formNo` varchar(32) NOT NULL,
  `salesInvoiceDate` date NOT NULL,
  `locationID` int(11) NOT NULL,
  `projectID` int(11) NOT NULL,
  `paymentMethodID` int(11) NOT NULL,
  `termsID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `paymentFrom` varchar(128) NOT NULL,
  `totalAmountIncludeTax` double NOT NULL,
  `totalTaxAmount` double NOT NULL,
  `totalamountExcludeTax` double NOT NULL,
  `totalPaid` double NOT NULL,
  `discount` double NOT NULL,
  `outOfBalance` double 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,
  `is_delete` smallint(6) NOT NULL DEFAULT '0',
  `updateAble` smallint(6) NOT NULL DEFAULT '1',
  `journalNo` varchar(32) NOT NULL,
  `bankID` int(11) NOT NULL,
  `amountReceived` double NOT NULL,
  `referenceNo` varchar(32) NOT NULL,
  `bankReconcileID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `comp_cash_in_detail_item_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `cashInID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `itemname` varchar(64) NOT NULL,
  `itemdescription` text NOT NULL,
  `quantity` int(11) NOT NULL,
  `umID` int(11) NOT NULL,
  `unitPrice` double NOT NULL,
  `amountExcludedTax` double NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmount` double NOT NULL,
  `amountIncludedTax` double NOT NULL,
  `accountID` int(11) 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',
  `is_delete` smallint(16) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `comp_cash_in_detail_service_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `cashInID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `accountID` int(11) NOT NULL,
  `itemdescription` text NOT NULL,
  `amountExcludedTax` double NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmount` double NOT NULL,
  `amountIncludedTax` double 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,
  `is_delete` smallint(16) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


create or replace view view_gst_tax_1 as 
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Cash Out' as transType, 
concat(a.formNo,'[',lpad(a.serialNo,8,'0'),']') as ref, 
a.paymentDate as transDate, 
a.supplierID, a.payTo as customerName, b.itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_cash_out_1 a , 
comp_cash_out_detail_item_1 b ,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.cashoutID
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
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Cash Out' as transType, 
concat(a.formNo,'[',lpad(a.serialNo,8,'0'),']') as ref, 
a.paymentDate as transDate, 
a.supplierID, a.payTo as customerName, b.description as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_cash_out_1 a , 
comp_cash_out_detail_service_1 b ,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.cashoutID
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
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Cash in' as transType, 
concat(a.formNo,'[',lpad(a.serialNo,8,'0'),']') as ref, 
a.salesInvoiceDate as transDate, 
0 as supplierID, a.paymentFrom as customerName, b.itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_cash_in_1 a , 
comp_cash_in_detail_item_1 b ,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.cashinID
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
union

select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Cash in' as transType, 
concat(a.formNo,'[',lpad(a.serialNo,8,'0'),']') as ref, 
a.salesInvoiceDate as transDate, 
0 as supplierID, a.paymentFrom as customerName, b.itemDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_cash_in_1 a , 
comp_cash_in_detail_service_1 b ,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.cashinID
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; 


create or replace view 
`view_gst_report_1` 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_1`.`amountExcludedTax`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('SR','DS','IMS')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B5a`,
(select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B5b`,
(select sum(`view_gst_tax_1`.`amountExcludedTax`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B6a`,
(select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('AJP','TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B6b`,
if(((select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) > (select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),((select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) - (select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),0) AS `B7`,
if(((select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) > (select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),((select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) - (select sum(`view_gst_tax_1`.`taxAmount`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),0) AS `B8`,
(select sum(`view_gst_tax_1`.`amountExcludedTax`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` = 'ZRL') and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C10`,
(select sum(`view_gst_tax_1`.`amountExcludedTax`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` = 'ZRE') and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C11`,
(select sum(`view_gst_tax_1`.`amountExcludedTax`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` in ('ES','')) and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C12`,
(select sum(`view_gst_tax_1`.`amountExcludedTax`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` = 'RS') and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C13`,
(select sum(`view_gst_tax_1`.`amountExcludedTax`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` = 'IS') and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C14`,
(select sum((`view_gst_tax_1`.`amountExcludedTax` * 0.06)) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` = 'IS') and (`view_gst_tax_1`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C15`,
(select sum(`view_gst_tax_1`.`amountExcludedTax`) from `view_gst_tax_1` where ((`view_gst_tax_1`.`companyID` = `a`.`companyID`) and (`view_gst_tax_1`.`gst` = 'TX-CG') and (`view_gst_tax_1`.`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));


create or replace view
`view_cash_book_1` as 
select `a`.`ID` AS `ID`,'in' AS `transType`,`a`.`salesInvoiceDate` AS `transactionDate`,`a`.`paymentFrom` AS `description`,
concat(`a`.`formNo`,'[',convert(lpad(`a`.`ID`,8,'0') using latin1),']') AS `formNo`,
`a`.`totalAmountIncludeTax` AS `Cash_in`,NULL AS `Cash_out`,`a`.`is_delete` AS `is_Delete`,`a`.`updateAble` AS `updateAble` 
from `comp_cash_in_1` `a` 
where a.is_delete = 0
union 
select `a`.`ID` AS `ID`,'out' AS `transType`,`a`.`paymentDate` AS `transactionDate`,`a`.`payTo` AS `description`,
concat(`a`.`formNo`,'[',convert(lpad(`a`.`ID`,8,'0') using latin1),']') AS `formNo`,
NULL AS `Cash_in`,`a`.`totalIncludingtax` AS `Cash_out`,`a`.`is_delete` AS `is_Delete`,`a`.`updateAble` AS `updateAble` 
from `comp_cash_out_1` `a`
where a.is_delete = 0