-- phpMyAdmin SQL Dump
-- version 4.1.12
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Aug 14, 2014 at 03:03 AM
-- Server version: 5.6.16
-- PHP Version: 5.5.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `iaccountdb`
--


--
-- Table structure for table `chat`
--

CREATE TABLE IF NOT EXISTS `chat` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `to` varchar(50) NOT NULL,
  `from` varchar(50) NOT NULL,
  `message` text NOT NULL,
  `sent` datetime NOT NULL,
  `recd` datetime NOT NULL,
  `createdID` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `to` (`to`),
  KEY `from` (`from`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `chats`
--

CREATE TABLE IF NOT EXISTS `chats` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `message` text NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

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

--
-- Table structure for table `ci_users`
--

CREATE TABLE IF NOT EXISTS `ci_users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_password` varchar(255) NOT NULL,
  `registered_date` datetime NOT NULL,
  `status` int(5) NOT NULL,
  `online` int(5) NOT NULL,
  PRIMARY KEY (`user_id`,`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `company`
--

CREATE TABLE IF NOT EXISTS `company` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyName` varchar(128) NOT NULL,
  `companyNo` varchar(16) NOT NULL,
  `incorporateDate` date NOT NULL,
  `businessTypeID` int(11) NOT NULL,
  `line1` varchar(128) NOT NULL,
  `line2` varchar(128) NOT NULL,
  `line3` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `companyPostCode` varchar(8) NOT NULL,
  `companyStateID` int(11) NOT NULL,
  `phoneNo` varchar(16) NOT NULL,
  `faxNo` varchar(16) NOT NULL,
  `email` varchar(32) NOT NULL,
  `website` varchar(32) NOT NULL,
  `uploadLogo` varchar(64) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `languageID` int(11) NOT NULL,
  `countryID` int(11) NOT NULL,
  `muslimShareholder` double NOT NULL,
  `gstRegisteredNo` varchar(16) NOT NULL,
  `gstRegisteredDate` date NOT NULL,
  `gstSubmissionBuffer` int(16) NOT NULL,
  `dateFormat` int(16) 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,
  `is_delete` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=87 ;

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

--
-- Table structure for table `comp_acct_group_bak`
--

CREATE TABLE IF NOT EXISTS `comp_acct_group_bak` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `acctTypeID` int(11) NOT NULL,
  `acctGroupName` varchar(128) NOT NULL,
  `acctCode` char(9) NOT NULL,
  `orderNo` int(11) NOT NULL,
  `parentID` int(11) NOT NULL,
  `title` tinyint(1) 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,
  `refID` int(11) NOT NULL COMMENT '_ref_acct_group.ID',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1023 ;

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

--
-- Table structure for table `comp_acct_type`
--

CREATE TABLE IF NOT EXISTS `comp_acct_type` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `acctTypeName` varchar(128) NOT NULL,
  `acctClass` smallint(6) 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=6 ;

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

--
-- Table structure for table `comp_acc_det`
--

CREATE TABLE IF NOT EXISTS `comp_acc_det` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) DEFAULT NULL,
  `acctGroupID` int(11) DEFAULT NULL,
  `acctID` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `period` varchar(2) DEFAULT NULL,
  `amountdr` double DEFAULT NULL,
  `amountcr` double DEFAULT NULL,
  `total` double DEFAULT '0',
  `createdBy` int(11) DEFAULT NULL,
  `createdTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) DEFAULT NULL,
  `updatedTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_delete` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Index 2` (`companyID`,`acctID`,`year`,`period`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=107 ;

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

--
-- Table structure for table `comp_address`
--

CREATE TABLE IF NOT EXISTS `comp_address` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `addressName` varchar(128) NOT NULL,
  `line1` varchar(128) NOT NULL,
  `line2` varchar(128) NOT NULL,
  `line3` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `postCode` varchar(8) NOT NULL,
  `stateID` int(11) NOT NULL,
  `countryID` int(11) 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(1) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

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

--
-- Table structure for table `comp_alert_table`
--

CREATE TABLE IF NOT EXISTS `comp_alert_table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `acctTypeID` int(11) NOT NULL,
  `badRelief` tinyint(1) NOT NULL,
  `registration` tinyint(1) NOT NULL,
  `gstReturn` tinyint(1) NOT NULL,
  `accountReceivable` tinyint(1) NOT NULL,
  `accountPayable` tinyint(1) 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 ;

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

--
-- Table structure for table `comp_bank`
--

CREATE TABLE IF NOT EXISTS `comp_bank` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `bankName` varchar(64) NOT NULL,
  `bankShortName` varchar(8) NOT NULL,
  `branch` varchar(64) NOT NULL,
  `bankAccountNo` varchar(32) NOT NULL,
  `currentBalance` double NOT NULL,
  `line1` varchar(64) NOT NULL,
  `line2` varchar(64) NOT NULL,
  `line3` varchar(64) NOT NULL,
  `city` varchar(64) NOT NULL,
  `PostCode` varchar(8) NOT NULL,
  `bankStateID` int(11) NOT NULL,
  `bankCountryID` int(11) NOT NULL,
  `attention` varchar(32) NOT NULL,
  `phoneNumber1` varchar(64) NOT NULL,
  `phoneNumber2` varchar(64) NOT NULL,
  `fax` varchar(64) NOT NULL,
  `generalNote` varchar(255) NOT NULL,
  `creditLimit` int(8) NOT NULL,
  `creditLimitAlert` tinyint(1) NOT NULL,
  `linkAccountID` int(11) NOT NULL,
  `createdDate` date NOT NULL,
  `currencyID` int(11) NOT NULL,
  `email` varchar(32) 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` int(16) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;

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

--
-- Table structure for table `comp_bank_reconcile`
--

CREATE TABLE IF NOT EXISTS `comp_bank_reconcile` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `bankID` int(11) NOT NULL,
  `reconcileDate` date NOT NULL,
  `bankStatementDate` date NOT NULL,
  `totalDebit` double NOT NULL,
  `totalCredit` double NOT NULL,
  `bankStatementBalance` double NOT NULL,
  `reconcileBalance` 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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

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

--
-- Table structure for table `comp_budget`
--

CREATE TABLE IF NOT EXISTS `comp_budget` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) DEFAULT NULL,
  `acctGroupID` int(11) DEFAULT NULL,
  `acctID` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `period` int(2) DEFAULT NULL,
  `amountdr` double DEFAULT NULL,
  `amountcr` double DEFAULT NULL,
  `total` double DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `createdTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) DEFAULT NULL,
  `updatedTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_delete` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Index 2` (`companyID`,`acctID`,`year`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=433 ;

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

--
-- Table structure for table `comp_chart_of_acct`
--

CREATE TABLE IF NOT EXISTS `comp_chart_of_acct` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `acctGroupID` int(11) NOT NULL,
  `acctCode` char(9) NOT NULL,
  `acctName` varchar(128) NOT NULL,
  `acctDesc` varchar(128) 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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28148 ;

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

--
-- Table structure for table `comp_contact`
--

CREATE TABLE IF NOT EXISTS `comp_contact` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `company_contact_name` varchar(128) NOT NULL,
  `position` varchar(32) NOT NULL,
  `telOffice` varchar(32) NOT NULL,
  `telHp` varchar(32) NOT NULL,
  `email` varchar(64) NOT NULL,
  `remaks` text NOT NULL,
  `createdID` 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 ;

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

--
-- Table structure for table `comp_control_acct_setup`
--

CREATE TABLE IF NOT EXISTS `comp_control_acct_setup` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `refID` int(11) NOT NULL,
  `companyID` int(11) NOT NULL,
  `accountCode` varchar(64) NOT NULL,
  `updateable` smallint(6) NOT NULL DEFAULT '1',
  `accountID` int(11) NOT NULL DEFAULT '0',
  `orderNo` int(11) 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=685 ;

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

--
-- Table structure for table `comp_currency_conv`
--

CREATE TABLE IF NOT EXISTS `comp_currency_conv` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) DEFAULT NULL,
  `currencyFrom` int(11) DEFAULT NULL,
  `currencyTo` int(11) DEFAULT NULL,
  `startDate` date DEFAULT NULL,
  `endDate` date DEFAULT NULL,
  `rateConv` double DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `createdTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) DEFAULT NULL,
  `updatedTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_delete` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Index 2` (`companyID`,`currencyFrom`,`startDate`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=7 ;

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

--
-- Table structure for table `comp_delivery_detail`
--

CREATE TABLE IF NOT EXISTS `comp_delivery_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `deliveryID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `salesID` int(11) NOT NULL,
  `invoiceID` int(11) NOT NULL DEFAULT '0',
  `soDetailsLine` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `unitmeasure` int(11) NOT NULL,
  `unitPrice` double NOT NULL,
  `quantityOrdered` int(11) NOT NULL,
  `requiredDate` date NOT NULL,
  `totalQuantitySent` int(11) NOT NULL,
  `quantitySent` int(11) NOT NULL,
  `amount` 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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

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

--
-- Table structure for table `comp_delivery_order`
--

CREATE TABLE IF NOT EXISTS `comp_delivery_order` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `deliveryCode` varchar(32) NOT NULL,
  `salesOrderID` int(11) NOT NULL,
  `customerID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `deliveryOrderDate` date NOT NULL,
  `locationID` int(11) NOT NULL,
  `deliverToID` int(11) NOT NULL,
  `toAddress` varchar(200) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `employeeID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `shippingMethodID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `exchangeRate` double NOT NULL,
  `totalReceivable` double NOT NULL,
  `updateAble` int(11) NOT NULL DEFAULT '1',
  `journalNo` varchar(32) 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=6 ;

--
-- Triggers `comp_delivery_order`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_donn1_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_donn1_add_serialNo` BEFORE INSERT ON `comp_delivery_order`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_delivery_order 
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_employee`
--

CREATE TABLE IF NOT EXISTS `comp_employee` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `employeeCode` varchar(16) NOT NULL,
  `employeeName` varchar(64) NOT NULL,
  `employeePosition` varchar(32) NOT NULL,
  `line1` varchar(64) NOT NULL,
  `line2` varchar(64) NOT NULL,
  `line3` varchar(64) NOT NULL,
  `postCode` varchar(8) NOT NULL,
  `city` varchar(32) NOT NULL,
  `stateID` int(11) NOT NULL,
  `countryID` int(11) NOT NULL,
  `phoneNo` varchar(32) NOT NULL,
  `email` varchar(128) NOT NULL,
  `dateOfJoin` date NOT NULL,
  `gender` smallint(6) 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(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

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

--
-- Table structure for table `comp_financial_year`
--

CREATE TABLE IF NOT EXISTS `comp_financial_year` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `financialYear` int(4) NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date NOT NULL,
  `period` smallint(2) NOT NULL,
  `status` smallint(1) NOT NULL,
  `yearEnd` smallint(1) NOT NULL DEFAULT '1',
  `updateAble` tinyint(1) DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `createdTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) DEFAULT NULL,
  `updatedTS` timestamp NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=234 ;

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

--
-- Table structure for table `comp_form_control`
--

CREATE TABLE IF NOT EXISTS `comp_form_control` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `formID` int(11) NOT NULL,
  `formCode` varchar(8) NOT NULL,
  `formYear` tinyint(1) NOT NULL,
  `formMonth` tinyint(1) NOT NULL,
  `formDay` tinyint(1) NOT NULL,
  `formInitial` varchar(16) NOT NULL,
  `formInitialNo` int(11) 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,
  `orderNo` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=467 ;

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

--
-- Table structure for table `comp_gl`
--

CREATE TABLE IF NOT EXISTS `comp_gl` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) DEFAULT '0',
  `acctCode` char(9) DEFAULT '0',
  `acctGroupID` int(11) DEFAULT '0',
  `description` varchar(128) DEFAULT '0',
  `amount_cr` double DEFAULT '0',
  `amount_dr` double DEFAULT '0',
  `period` smallint(2) DEFAULT '0',
  `year` year(4) DEFAULT NULL,
  `createdID` 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=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=3005 ;

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

--
-- Table structure for table `comp_industry`
--

CREATE TABLE IF NOT EXISTS `comp_industry` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `industryID` int(11) NOT NULL,
  `businessTypeID` int(11) 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`),
  KEY `companyID` (`companyID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `comp_jl_seq`
--

CREATE TABLE IF NOT EXISTS `comp_jl_seq` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `compID` int(11) DEFAULT NULL,
  `moduleID` int(11) DEFAULT NULL,
  `initial` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
  `year` year(4) NOT NULL,
  `period` int(2) NOT NULL,
  `seqNo` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

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

--
-- Table structure for table `comp_journal`
--

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

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

--
-- Table structure for table `comp_journal_detail`
--

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

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

--
-- Table structure for table `comp_location_transfer`
--

CREATE TABLE IF NOT EXISTS `comp_location_transfer` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `fromLocationID` int(11) NOT NULL,
  `toLocationID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `movementTypeID` int(11) NOT NULL,
  `dateTransfer` date NOT NULL,
  `memo` text NOT NULL,
  `is_delete` smallint(6) NOT NULL,
  `updateable` smallint(6) 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=25 ;

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

--
-- Table structure for table `comp_location_transfer_detail`
--

CREATE TABLE IF NOT EXISTS `comp_location_transfer_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `locationTransferID` int(11) NOT NULL,
  `LineNo` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `fromStock` int(11) NOT NULL,
  `is_delete` smallint(6) 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=35 ;

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

--
-- Table structure for table `comp_payment`
--

CREATE TABLE IF NOT EXISTS `comp_payment` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `projectID` int(11) NOT NULL,
  `amountPaid` double NOT NULL,
  `paymentMethodID` int(11) NOT NULL,
  `referenceNo` varchar(16) NOT NULL,
  `bankID` int(11) NOT NULL,
  `reconcile` smallint(6) NOT NULL DEFAULT '0',
  `accountID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `paymentDate` date NOT NULL,
  `updateAble` smallint(1) NOT NULL DEFAULT '1',
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `addressID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `totalDiscount` double NOT NULL,
  `bankCharges` double NOT NULL,
  `totalOtherCharge` double NOT NULL,
  `totalPaid` double NOT NULL,
  `totalApplied` 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(16) NOT NULL,
  `accountDescription` varchar(128) NOT NULL DEFAULT 'Temp Description',
  `paymentID` int(11) NOT NULL,
  `journalNo` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=103 ;

--
-- Triggers `comp_payment`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_cp_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_cp_add_serialNo` BEFORE INSERT ON `comp_payment`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_payment 
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_payment_detail`
--

CREATE TABLE IF NOT EXISTS `comp_payment_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `paymentID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `purchaseInvoiceID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `APBalance` double NOT NULL,
  `discount` double NOT NULL,
  `amountApplied` 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=169 ;

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

--
-- Table structure for table `comp_payment_others`
--

CREATE TABLE IF NOT EXISTS `comp_payment_others` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `otherPaymentType` smallint(6) NOT NULL COMMENT '0=with item. 1=without item',
  `companyID` int(11) NOT NULL,
  `payTo` text NOT NULL,
  `projectID` int(11) NOT NULL,
  `amountPaid` double NOT NULL,
  `paymentMethodID` int(11) NOT NULL,
  `referenceNo` int(11) NOT NULL,
  `accountID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `paymentDate` date NOT NULL,
  `updateAble` smallint(1) NOT NULL DEFAULT '1',
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `addressID` int(11) NOT NULL,
  `locationID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `totalDiscount` double NOT NULL,
  `bankCharges` double NOT NULL,
  `bankId` int(11) NOT NULL,
  `totalOtherCharge` double NOT NULL,
  `totalPaid` double NOT NULL,
  `totalApplied` 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(16) NOT NULL,
  `accountDescription` varchar(128) NOT NULL,
  `paymentID` int(11) NOT NULL,
  `journalNo` varchar(32) NOT NULL,
  `reconcile` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;

--
-- Triggers `comp_payment_others`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_payo_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_payo_add_serialNo` BEFORE INSERT ON `comp_payment_others`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_payment_others 
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_payment_others_without_item`
--

CREATE TABLE IF NOT EXISTS `comp_payment_others_without_item` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `otherPaymentID` 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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

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

--
-- Table structure for table `comp_payment_others_with_item`
--

CREATE TABLE IF NOT EXISTS `comp_payment_others_with_item` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `otherPaymentID` 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,
  `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=44 ;

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

--
-- Table structure for table `comp_project`
--

CREATE TABLE IF NOT EXISTS `comp_project` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `project_name` varchar(128) NOT NULL,
  `projectManagerID` int(11) NOT NULL,
  `totalBudget` double NOT NULL,
  `remarks` text NOT NULL,
  `createdID` 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=28 ;

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

--
-- Table structure for table `comp_purchase_order`
--

CREATE TABLE IF NOT EXISTS `comp_purchase_order` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL,
  `companyID` int(11) NOT NULL,
  `purchaserID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `invoiceID` int(11) NOT NULL DEFAULT '0',
  `purchaseOrderNo` varchar(32) NOT NULL,
  `salesAgentName` varchar(64) NOT NULL,
  `formNo` varchar(16) NOT NULL,
  `poDate` date NOT NULL,
  `exchangeRate` double NOT NULL,
  `termAndConditionID` int(11) NOT NULL,
  `currencyID` int(11) NOT 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,
  `memo` varchar(255) NOT NULL,
  `is_delete` int(11) NOT NULL DEFAULT '0',
  `journalNo` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Triggers `comp_purchase_order`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_po_add_serial`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_po_add_serial` BEFORE INSERT ON `comp_purchase_order`
 FOR EACH ROW begin 
set new.serialNo = 
(select nextSerialNo from view_serial_comp_purchase_order 
	where companyID = new.companyID); 
end
//
DELIMITER ;

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

--
-- Table structure for table `comp_purchase_order_detail`
--

CREATE TABLE IF NOT EXISTS `comp_purchase_order_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `purchaseOrderID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `itemCode` varchar(16) NOT NULL,
  `itemName` varchar(128) NOT NULL,
  `description` text,
  `unitPrice` double NOT NULL,
  `quantityOrder` int(11) NOT NULL,
  `quantityReceivedTotal` int(11) NOT NULL DEFAULT '0',
  `amount` 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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;

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

--
-- Table structure for table `comp_purchase_price`
--

CREATE TABLE IF NOT EXISTS `comp_purchase_price` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `price` double NOT NULL,
  `updatedBy` varchar(50) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

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

--
-- Table structure for table `comp_quotation`
--

CREATE TABLE IF NOT EXISTS `comp_quotation` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `customerID` int(11) NOT NULL,
  `quotationCode` varchar(32) NOT NULL,
  `salesAgentID` int(11) NOT NULL,
  `employeeID` int(11) unsigned NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `quotationDate` date NOT NULL,
  `deliverToID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `supplierID` text NOT NULL,
  `memo` varchar(128) NOT NULL,
  `totalTaxAmount` double NOT NULL,
  `exchangeRate` double NOT NULL,
  `totalReceivable` 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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

--
-- Triggers `comp_quotation`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_cq_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_cq_add_serialNo` BEFORE INSERT ON `comp_quotation`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_quotation 
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_quotation_detail`
--

CREATE TABLE IF NOT EXISTS `comp_quotation_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `quotationID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `description` varchar(200) NOT NULL,
  `unitOfPrice` double NOT NULL,
  `quantityOrder` int(11) NOT NULL,
  `amountExcludeTax` double DEFAULT NULL,
  `taxID` int(11) NOT NULL,
  `umID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmount` double NOT NULL,
  `amountIncudeTax` 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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

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

--
-- Table structure for table `comp_receive_item`
--

CREATE TABLE IF NOT EXISTS `comp_receive_item` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `supplierDoNo` varchar(32) NOT NULL,
  `purchaseOrderID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `deliveryDate` date NOT NULL,
  `locationID` int(11) NOT NULL,
  `deliverToID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `exchangeRate` float NOT NULL,
  `employeeID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `shippingMethodID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `totalPayable` double NOT NULL,
  `journalNo` varchar(32) NOT NULL,
  `updateAble` smallint(6) 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=82 ;

--
-- Triggers `comp_receive_item`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_ritem_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_ritem_add_serialNo` BEFORE INSERT ON `comp_receive_item`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_receive_item
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_receive_item_detail`
--

CREATE TABLE IF NOT EXISTS `comp_receive_item_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `receiveID` int(11) NOT NULL,
  `invoiceID` int(11) NOT NULL DEFAULT '0',
  `lineNo` int(11) NOT NULL,
  `poDetailID` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `itemCode` varchar(16) NOT NULL,
  `itemName` varchar(128) NOT NULL,
  `description` text,
  `unitPrice` double NOT NULL,
  `quantityOrder` double NOT NULL,
  `quantityReceived` double NOT NULL DEFAULT '0',
  `umID` int(11) NOT NULL DEFAULT '0',
  `amount` double NOT NULL,
  `balanceItem` 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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=173 ;

--
-- Triggers `comp_receive_item_detail`
--
DROP TRIGGER IF EXISTS `updateBalanceItem`;
DELIMITER //
CREATE TRIGGER `updateBalanceItem` BEFORE INSERT ON `comp_receive_item_detail`
 FOR EACH ROW if new.balanceitem is null then
set new.balanceItem = new.quantityReceived;
end if
//
DELIMITER ;

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

--
-- Table structure for table `comp_receive_payment`
--

CREATE TABLE IF NOT EXISTS `comp_receive_payment` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `customerID` int(11) NOT NULL,
  `projectID` int(11) NOT NULL,
  `paymentMethodID` int(11) NOT NULL,
  `referenceNo` int(11) NOT NULL,
  `bankID` int(11) NOT NULL,
  `accountID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `receivePaymentDate` date NOT NULL,
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `addressID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `totalDiscount` double NOT NULL,
  `bankCharges` double NOT NULL,
  `totalOtherCharge` double NOT NULL,
  `totalReceived` double NOT NULL,
  `totalApplied` double NOT NULL,
  `outOfBalance` double NOT NULL,
  `updateAble` smallint(1) 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',
  `journalNo` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

--
-- Triggers `comp_receive_payment`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_rpay_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_rpay_add_serialNo` BEFORE INSERT ON `comp_receive_payment`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_receive_payment
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_receive_payment_detail`
--

CREATE TABLE IF NOT EXISTS `comp_receive_payment_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `receivePaymentID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `invoiceID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `ARBalance` double NOT NULL,
  `discount` double NOT NULL,
  `amountApplied` double 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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

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

--
-- Table structure for table `comp_sales_agent`
--

CREATE TABLE IF NOT EXISTS `comp_sales_agent` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `agentName` varchar(128) NOT NULL,
  `position` varchar(32) NOT NULL,
  `telOffice` varchar(32) NOT NULL,
  `telHp` varchar(32) NOT NULL,
  `email` varchar(64) NOT NULL,
  `commisionRate` double NOT NULL,
  `remarks` text NOT NULL,
  `createdID` 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=18 ;

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

--
-- Table structure for table `comp_sales_creditnote`
--

CREATE TABLE IF NOT EXISTS `comp_sales_creditnote` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `customerID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `creditNoteDate` date NOT NULL,
  `salesInvoiceID` int(11) NOT NULL,
  `projectID` int(11) NOT NULL,
  `termsID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `agentID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `employeeID` int(11) NOT NULL,
  `shippingMethodID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `amountPaid` decimal(10,0) NOT NULL,
  `totalTaxAmount` double NOT NULL,
  `totalPayable` double NOT NULL,
  `total` double NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(16) NOT NULL,
  `updateAble` tinyint(1) NOT NULL DEFAULT '1',
  `journalNo` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--
-- Triggers `comp_sales_creditnote`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_scn_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_scn_add_serialNo` BEFORE INSERT ON `comp_sales_creditnote`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_sales_creditnote
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_sales_creditnote_charges`
--

CREATE TABLE IF NOT EXISTS `comp_sales_creditnote_charges` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `creditNoteID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `accountNoID` int(11) NOT NULL,
  `chargeDescription` text NOT NULL,
  `amountExcludedTax` double NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmount` double NOT NULL,
  `amountIncludedTax` double NOT NULL,
  `drAccountID` int(11) NOT NULL,
  `crAccountID` int(11) NOT NULL,
  `drTaxAccountID` int(11) NOT NULL,
  `crTaxAccountID` int(11) 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=52 ;

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

--
-- Table structure for table `comp_sales_creditnote_detail`
--

CREATE TABLE IF NOT EXISTS `comp_sales_creditnote_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `creditnoteID` 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,
  `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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

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

--
-- Table structure for table `comp_sales_debitnote`
--

CREATE TABLE IF NOT EXISTS `comp_sales_debitnote` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `customerID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `debitNoteDate` date NOT NULL,
  `salesInvoiceID` int(11) NOT NULL,
  `projectID` int(11) NOT NULL,
  `termsID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `agentID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `employeeID` int(11) NOT NULL,
  `shippingMethodID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `amountPaid` decimal(10,0) NOT NULL,
  `totalTaxAmount` double NOT NULL,
  `totalPayable` double NOT NULL,
  `total` double NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(16) NOT NULL,
  `updateAble` tinyint(1) NOT NULL DEFAULT '1',
  `journalNo` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

--
-- Triggers `comp_sales_debitnote`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_sdn_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_sdn_add_serialNo` BEFORE INSERT ON `comp_sales_debitnote`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_sales_debitnote
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_sales_debitnote_charges`
--

CREATE TABLE IF NOT EXISTS `comp_sales_debitnote_charges` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `debitNoteID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `accountNoID` int(11) NOT NULL,
  `chargeDescription` text NOT NULL,
  `amountExcludedTax` double NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmount` double NOT NULL,
  `amountIncludedTax` double NOT NULL,
  `drAccountID` int(11) NOT NULL,
  `crAccountID` int(11) NOT NULL,
  `drTaxAccountID` int(11) NOT NULL,
  `crTaxAccountID` int(11) 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=49 ;

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

--
-- Table structure for table `comp_sales_debitnote_detail`
--

CREATE TABLE IF NOT EXISTS `comp_sales_debitnote_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `debitnoteID` 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,
  `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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

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

--
-- Table structure for table `comp_sales_detail`
--

CREATE TABLE IF NOT EXISTS `comp_sales_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `salesID` int(11) NOT NULL,
  `invoiceID` int(11) NOT NULL DEFAULT '0',
  `lineNo` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `unitPrice` double DEFAULT NULL,
  `unitmeasure` int(11) DEFAULT NULL,
  `quantityOrder` double DEFAULT NULL,
  `quantityShip` double DEFAULT '0',
  `amount` double DEFAULT NULL,
  `requiredDate` date DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `createdTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) DEFAULT NULL,
  `updatedTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

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

--
-- Table structure for table `comp_sales_invoice`
--

CREATE TABLE IF NOT EXISTS `comp_sales_invoice` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `invoiceCodeID` int(11) NOT NULL COMMENT '1=sales inv, 2=direct sales inv,  3=debitNote, 4=creditNte',
  `salesID` int(11) NOT NULL,
  `customerID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `salesInvoiceDate` date NOT NULL,
  `customerPoNo` varchar(32) NOT NULL,
  `locationID` int(11) NOT NULL,
  `projectID` int(11) NOT NULL,
  `termsID` int(11) NOT NULL,
  `shipToID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `agentID` int(11) NOT NULL,
  `printInInvoice` smallint(6) NOT NULL DEFAULT '0',
  `memo` text NOT NULL,
  `employeeID` int(11) NOT NULL,
  `showSaleAgent` tinyint(4) NOT NULL,
  `shippingMethodID` int(11) NOT NULL,
  `totalTaxAmount` double NOT NULL,
  `totalReceivable` double NOT NULL,
  `totalamount` double NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp 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,
  `salesOrderID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=145 ;

--
-- Triggers `comp_sales_invoice`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_sinv_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_sinv_add_serialNo` BEFORE INSERT ON `comp_sales_invoice`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_sales_invoice
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_sales_invoice_charges`
--

CREATE TABLE IF NOT EXISTS `comp_sales_invoice_charges` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `invoiceID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `accountNoID` int(11) NOT NULL,
  `chargeDescription` text NOT NULL,
  `amountExcludedTax` double NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmount` double NOT NULL,
  `amountIncludedTax` double NOT NULL,
  `drAccountID` int(11) NOT NULL,
  `crAccountID` int(11) NOT NULL,
  `drTaxAccountID` int(11) NOT NULL,
  `crTaxAccountID` int(11) 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=111 ;

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

--
-- Table structure for table `comp_sales_invoice_detail`
--

CREATE TABLE IF NOT EXISTS `comp_sales_invoice_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `invoiceID` int(11) NOT NULL,
  `doID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `itemID` int(11) 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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=183 ;

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

--
-- Table structure for table `comp_sales_order`
--

CREATE TABLE IF NOT EXISTS `comp_sales_order` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(16) NOT NULL,
  `companyID` int(11) NOT NULL,
  `invoiceID` int(11) NOT NULL,
  `customerID` int(11) DEFAULT NULL,
  `OrderNo` varchar(32) DEFAULT NULL,
  `formNo` varchar(32) DEFAULT NULL,
  `currencyID` int(11) DEFAULT NULL,
  `exchangeRate` double DEFAULT '1',
  `salesOrderDate` date DEFAULT NULL,
  `dateRequired` date DEFAULT NULL,
  `deliveryToID` int(11) DEFAULT NULL,
  `salesPerson` int(11) DEFAULT NULL,
  `memo` varchar(128) DEFAULT NULL,
  `supplierID` int(11) DEFAULT NULL,
  `totalAmount` double DEFAULT NULL,
  `totalReceivable` double DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1',
  `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=10 ;

--
-- Triggers `comp_sales_order`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_sor_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_sor_add_serialNo` BEFORE INSERT ON `comp_sales_order`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_sales_order
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_sales_price`
--

CREATE TABLE IF NOT EXISTS `comp_sales_price` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `customerID` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `umID` int(11) NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date NOT NULL,
  `price` double NOT NULL,
  `updatedBy` int(11) NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

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

--
-- Table structure for table `comp_so_seq`
--

CREATE TABLE IF NOT EXISTS `comp_so_seq` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `compID` int(11) DEFAULT NULL,
  `moduleID` int(11) DEFAULT NULL,
  `initial` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `period` int(2) DEFAULT NULL,
  `seqNo` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `comp_supplier_closing`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_closing` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `yearMonth` varchar(6) NOT NULL,
  `openBalance` double NOT NULL,
  `totalDr` double NOT NULL,
  `totalCr` double NOT NULL,
  `balance` double NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `companyID` (`companyID`,`supplierID`,`yearMonth`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

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

--
-- Table structure for table `comp_supplier_closing_daily`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_closing_daily` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `transDate` date NOT NULL,
  `openBalance` double NOT NULL,
  `totalDr` double NOT NULL,
  `totalCr` double NOT NULL,
  `balance` double NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=808 ;

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

--
-- Table structure for table `comp_supplier_creditnote`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_creditnote` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `creditNoteDate` date NOT NULL,
  `supplierInvoiceID` int(11) NOT NULL,
  `projectID` int(11) NOT NULL,
  `termsID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `agentID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `employeeID` int(11) NOT NULL,
  `shippingMethodID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `amountPaid` decimal(10,0) NOT NULL,
  `totalTaxAmount` double NOT NULL,
  `totalPayable` double NOT NULL,
  `total` double NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(16) NOT NULL,
  `updateAble` tinyint(1) NOT NULL DEFAULT '1',
  `journalNo` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

--
-- Triggers `comp_supplier_creditnote`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_scno_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_scno_add_serialNo` BEFORE INSERT ON `comp_supplier_creditnote`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_supplier_creditnote
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_supplier_creditnote_charges`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_creditnote_charges` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `creditNoteID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `accountNoID` int(11) NOT NULL,
  `itemname` varchar(64) NOT NULL,
  `itemdescription` text 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,
  `drAccountID` int(11) NOT NULL,
  `crAccountID` int(11) NOT NULL,
  `drTaxAccountID` int(11) NOT NULL,
  `crTaxAccountID` int(11) 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=34 ;

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

--
-- Table structure for table `comp_supplier_creditnote_detail`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_creditnote_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `creditnoteID` 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,
  `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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

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

--
-- Table structure for table `comp_supplier_debitnote`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_debitnote` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `debitNoteDate` date NOT NULL,
  `supplierInvoiceID` int(11) NOT NULL,
  `projectID` int(11) NOT NULL,
  `termsID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `agentID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `employeeID` int(11) NOT NULL,
  `shippingMethodID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `amountPaid` decimal(10,0) NOT NULL,
  `totalTaxAmount` double NOT NULL,
  `totalPayable` double NOT NULL,
  `total` double NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(16) NOT NULL,
  `updateAble` tinyint(1) NOT NULL DEFAULT '1',
  `journalNo` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

--
-- Triggers `comp_supplier_debitnote`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_sdno_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_sdno_add_serialNo` BEFORE INSERT ON `comp_supplier_debitnote`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_supplier_debitnote
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_supplier_debitnote_charges`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_debitnote_charges` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `debitNoteID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `accountNoID` int(11) NOT NULL,
  `itemname` varchar(64) NOT NULL,
  `itemdescription` text 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,
  `drAccountID` int(11) NOT NULL,
  `crAccountID` int(11) NOT NULL,
  `drTaxAccountID` int(11) NOT NULL,
  `crTaxAccountID` int(11) 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=51 ;

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

--
-- Table structure for table `comp_supplier_debitnote_detail`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_debitnote_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `debitnoteID` 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,
  `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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;

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

--
-- Table structure for table `comp_supplier_invoice`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_invoice` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL DEFAULT '1',
  `companyID` int(11) NOT NULL,
  `purchaseInvoiceCodeID` int(11) NOT NULL COMMENT '1=puchase inv, 2=direct purch inv, 3=debit note, 4=credit note',
  `purchaserID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `invoiceDate` date NOT NULL,
  `supplierInvoiceNo` varchar(32) NOT NULL,
  `projectID` int(11) NOT NULL,
  `termsID` int(11) NOT NULL,
  `shipToID` int(11) NOT NULL,
  `locationID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `exchangeRate` double NOT NULL,
  `agentID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `employeeID` int(11) NOT NULL,
  `shippingMethodID` int(11) NOT NULL,
  `totalAmount` double NOT NULL,
  `amountPaid` decimal(10,0) NOT NULL,
  `totalTaxAmount` double NOT NULL,
  `totalPayable` double NOT NULL,
  `total` double NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(16) NOT NULL,
  `updateAble` tinyint(1) NOT NULL DEFAULT '1',
  `journalNo` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=209 ;

--
-- Triggers `comp_supplier_invoice`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_si_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_si_add_serialNo` BEFORE INSERT ON `comp_supplier_invoice`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_supplier_invoice 
			   where companyID = new.companyID)
//
DELIMITER ;

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

--
-- Table structure for table `comp_supplier_invoice_charges`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_invoice_charges` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `invoiceID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `accountNoID` int(11) NOT NULL,
  `chargeDescription` text NOT NULL,
  `itemname` varchar(64) NOT NULL,
  `itemdescription` text 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,
  `drAccountID` int(11) NOT NULL,
  `crAccountID` int(11) NOT NULL,
  `drTaxAccountID` int(11) NOT NULL,
  `crTaxAccountID` int(11) 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=87 ;

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

--
-- Table structure for table `comp_supplier_invoice_detail`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_invoice_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `invoiceID` int(11) NOT NULL,
  `receiveID` 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,
  `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',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=280 ;

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

--
-- Table structure for table `comp_supplier_statement`
--

CREATE TABLE IF NOT EXISTS `comp_supplier_statement` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `debit` double NOT NULL,
  `credit` double NOT NULL,
  `balance` double NOT NULL,
  `supplierCode` int(11) NOT NULL,
  `supplierName` varchar(16) NOT NULL,
  `description` varchar(255) NOT NULL,
  `referenceNo` varchar(5) NOT NULL,
  `is_delete` smallint(16) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `customer`
--

CREATE TABLE IF NOT EXISTS `customer` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `customerName` varchar(128) NOT NULL,
  `accountCode` varchar(8) NOT NULL,
  `customerCompanyNo` varchar(32) NOT NULL,
  `line1` varchar(128) NOT NULL,
  `line2` varchar(128) NOT NULL,
  `line3` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `customerPostCode` varchar(8) NOT NULL,
  `customerStateID` int(11) NOT NULL,
  `customerCountryID` int(11) NOT NULL,
  `attention` varchar(16) NOT NULL,
  `website` varchar(32) NOT NULL,
  `phoneNumber1` varchar(50) NOT NULL,
  `phoneNumber2` varchar(50) NOT NULL,
  `fax` varchar(50) NOT NULL,
  `generalNote` varchar(255) NOT NULL,
  `gstRegNo` varchar(16) NOT NULL,
  `termsID` int(11) NOT NULL,
  `creditLimit` int(8) NOT NULL,
  `creditLimitAlert` tinyint(1) NOT NULL,
  `linkedAPAccountID` varchar(11) NOT NULL,
  `createdDate` date NOT NULL,
  `currencyID` int(11) NOT NULL,
  `email` varchar(32) NOT NULL,
  `termstatus` int(11) 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` int(16) NOT NULL,
  `position` varchar(16) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

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

--
-- Table structure for table `customer_address`
--

CREATE TABLE IF NOT EXISTS `customer_address` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `customerID` int(11) NOT NULL,
  `addressCode` varchar(50) NOT NULL,
  `addressName` varchar(128) NOT NULL,
  `line1` varchar(128) NOT NULL,
  `line2` varchar(128) NOT NULL,
  `line3` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `postCode` varchar(8) NOT NULL,
  `stateID` int(11) NOT NULL,
  `countryID` int(11) 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(1) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

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

--
-- Table structure for table `customer_contact`
--

CREATE TABLE IF NOT EXISTS `customer_contact` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `customerID` int(11) NOT NULL,
  `customer_contact_name` varchar(128) NOT NULL,
  `position` varchar(32) NOT NULL,
  `telOffice` varchar(32) NOT NULL,
  `telHp` varchar(32) NOT NULL,
  `email` varchar(64) NOT NULL,
  `remaks` text NOT NULL,
  `createdID` 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 ;

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

--
-- Table structure for table `gl_setup`
--

CREATE TABLE IF NOT EXISTS `gl_setup` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `accountTypeID` int(11) NOT NULL,
  `accountNoID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `item`
--

CREATE TABLE IF NOT EXISTS `item` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `itemCode` varchar(32) NOT NULL,
  `name` varchar(64) NOT NULL,
  `description` text NOT NULL,
  `itemCategoryID` int(11) NOT NULL,
  `itemTypeID` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unitOfMeasureID` int(11) NOT NULL,
  `editDescription` tinyint(1) NOT NULL,
  `imageFile` varchar(64) NOT NULL,
  `itemStatusID` int(11) NOT NULL,
  `reorderLevel` int(11) NOT NULL,
  `incomeID` int(11) NOT NULL,
  `cogsID` int(11) NOT NULL,
  `inventoriesID` int(11) NOT NULL,
  `inputTaxID` int(11) NOT NULL,
  `outputTaxID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `memo` text NOT NULL,
  `locationID` int(11) NOT NULL,
  `is_delete` smallint(6) NOT NULL,
  `updateable` smallint(6) NOT NULL,
  `totalAmount` double NOT NULL,
  `totalPayable` 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,
  `supplierPrice` double NOT NULL,
  `salesPrice` double NOT NULL,
  `currencyID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

--
-- Triggers `item`
--
DROP TRIGGER IF EXISTS `trigger_before_insert_item`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_item` BEFORE INSERT ON `item`
 FOR EACH ROW begin
if new.incomeID = 0 or new.incomeID is null then 
set new.incomeID = (select accountID from comp_control_acct_setup where ID > 0 and companyID = new.companyID and accountCode='saleacct'); 
end if;
if new.cogsID = 0 or new.cogsID is null then 
set new.cogsID = (select accountID from comp_control_acct_setup where id>0 and companyID = new.companyID and accountCode = 'saleacct');
end if;

end
//
DELIMITER ;

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

--
-- Table structure for table `item_adjustment`
--

CREATE TABLE IF NOT EXISTS `item_adjustment` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `locationID` int(11) NOT NULL,
  `formNo` varchar(32) NOT NULL,
  `updateAble` smallint(11) NOT NULL DEFAULT '1',
  `adjustmentDate` date NOT NULL,
  `createdBy` varchar(50) 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=21 ;

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

--
-- Table structure for table `item_adjustment_detail`
--

CREATE TABLE IF NOT EXISTS `item_adjustment_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `adjustmentID` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `lineNo` int(11) NOT NULL,
  `unitOfMeasureID` int(11) NOT NULL,
  `qtyOnHand` double NOT NULL COMMENT 'The number of items on the time of Adjustment',
  `counted` double NOT NULL COMMENT 'The actual number of items on the time of adjustment',
  `onDatePrice` double NOT NULL,
  `updateAble` smallint(11) NOT NULL DEFAULT '1',
  `adjustmentDate` date NOT NULL,
  `createdBy` varchar(50) 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=23 ;

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

--
-- Table structure for table `item_details`
--

CREATE TABLE IF NOT EXISTS `item_details` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `locationID` int(11) DEFAULT NULL,
  `onHandQty` double DEFAULT NULL,
  `umID` int(11) DEFAULT NULL,
  `acctID` 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,
  `is_delete` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Index 2` (`companyID`,`acctID`,`locationID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=20 ;

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

--
-- Table structure for table `master`
--

CREATE TABLE IF NOT EXISTS `master` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `description` varchar(128) NOT NULL,
  `updatedby` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `usedBySystem` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

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

--
-- Table structure for table `master_code`
--

CREATE TABLE IF NOT EXISTS `master_code` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `masterID` int(11) NOT NULL,
  `parentFilterID` int(11) DEFAULT NULL,
  `code` varchar(4) NOT NULL,
  `name` varchar(64) NOT NULL,
  `shortName` varchar(8) NOT NULL,
  `description` varchar(128) NOT NULL,
  `orderNo` int(11) NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateBy` int(11) NOT NULL,
  `updateTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=117 ;

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

--
-- Table structure for table `module`
--

CREATE TABLE IF NOT EXISTS `module` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `parentModuleID` int(11) NOT NULL,
  `moduleName` varchar(64) NOT NULL,
  `description` varchar(254) NOT NULL,
  `orderNo` int(11) 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=11 ;

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

--
-- Table structure for table `person`
--

CREATE TABLE IF NOT EXISTS `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` int(11) NOT NULL,
  `position` varchar(64) NOT NULL,
  `employeeCode` varchar(16) NOT NULL,
  `genderID` int(11) NOT NULL,
  `dateOfBirth` date NOT NULL,
  `dateOfJoint` date NOT NULL,
  `remaks` text 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 ;

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

--
-- Table structure for table `person_contact`
--

CREATE TABLE IF NOT EXISTS `person_contact` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `personID` int(11) NOT NULL,
  `companyID` int(11) NOT NULL,
  `contactType` varchar(32) NOT NULL,
  `contact` tinytext NOT NULL,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `supplier`
--

CREATE TABLE IF NOT EXISTS `supplier` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `supplierName` varchar(128) NOT NULL,
  `supplierCode` varchar(8) NOT NULL,
  `supplierCompanyNo` varchar(32) NOT NULL,
  `line1` varchar(128) NOT NULL,
  `line2` varchar(128) NOT NULL,
  `line3` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `supplierPostCode` varchar(8) NOT NULL,
  `supplierStateID` int(11) NOT NULL,
  `supplierCountryID` int(11) NOT NULL,
  `attention` varchar(100) NOT NULL,
  `website` varchar(32) NOT NULL,
  `phoneNumber1` varchar(50) NOT NULL,
  `phoneNumber2` varchar(50) NOT NULL,
  `fax` varchar(50) NOT NULL,
  `generalNote` varchar(255) NOT NULL,
  `gstRegNo` varchar(16) NOT NULL,
  `termsID` int(11) NOT NULL,
  `creditLimit` int(8) NOT NULL,
  `creditLimitAlert` tinyint(1) NOT NULL,
  `linkedAPAccountID` int(11) NOT NULL,
  `createdDate` date NOT NULL,
  `currencyID` int(11) NOT NULL,
  `email` varchar(32) NOT NULL,
  `termstatus` int(11) 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`),
  KEY `ID` (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;

--
-- Triggers `supplier`
--
DROP TRIGGER IF EXISTS `trigger_default_created_date`;
DELIMITER //
CREATE TRIGGER `trigger_default_created_date` BEFORE INSERT ON `supplier`
 FOR EACH ROW if ( isnull(new.createddate) ) then
 set new.createddate=curdate();
end if
//
DELIMITER ;

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

--
-- Table structure for table `supplier_address`
--

CREATE TABLE IF NOT EXISTS `supplier_address` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `supplierID` int(11) NOT NULL,
  `addressCode` varchar(50) DEFAULT NULL,
  `addressName` varchar(128) DEFAULT NULL,
  `line1` varchar(128) DEFAULT NULL,
  `line2` varchar(128) DEFAULT NULL,
  `line3` varchar(128) DEFAULT NULL,
  `city` varchar(128) DEFAULT NULL,
  `postCode` varchar(8) DEFAULT NULL,
  `stateID` int(11) DEFAULT NULL,
  `countryID` 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,
  `is_delete` smallint(16) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

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

--
-- Table structure for table `supplier_contact`
--

CREATE TABLE IF NOT EXISTS `supplier_contact` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `supplierID` int(11) NOT NULL,
  `personID` int(11) NOT NULL,
  `position` varchar(32) NOT NULL,
  `is_delete` smallint(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `tax_class`
--

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 ;

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

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

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

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

CREATE TABLE IF NOT EXISTS `tax_master` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL DEFAULT '0',
  `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`,`companyID`),
  KEY `FK_tax_master_tax_class` (`taxClass`),
  KEY `FK_tax_master_tax_group` (`taxGroup`),
  KEY `FK_tax_master_tax_type` (`taxType`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=350 ;

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

--
-- 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,
  `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=19 ;

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

--
-- Table structure for table `tax_work_footer`
--

CREATE TABLE IF NOT EXISTS `tax_work_footer` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `recordID` varchar(1) DEFAULT NULL COMMENT 'Must be a single character corresponding to record type. Must be ‘F’',
  `purchaseCount` int(11) DEFAULT NULL COMMENT 'Number of P records',
  `purchaseAmount` double DEFAULT NULL COMMENT 'Debit Amount',
  `purchaseAmountGST` double DEFAULT NULL COMMENT 'Purchase GST Amount GST',
  `supplyCount` int(11) DEFAULT NULL COMMENT 'Supply Count',
  `supplyAmountSum` double DEFAULT NULL COMMENT 'Sum of all supplies',
  `supplyAmountSumGst` double DEFAULT NULL COMMENT 'Sum of GST on all supplies',
  `ledgerCount` int(11) DEFAULT NULL COMMENT 'Number of L records',
  `debitSum` double DEFAULT NULL COMMENT 'Debit Sum',
  `creditSum` double DEFAULT NULL COMMENT 'Credit Sum',
  `balanceSum` double DEFAULT NULL COMMENT 'Balance Sum',
  `status` smallint(1) 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=1 ;

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

--
-- Table structure for table `tax_work_gl`
--

CREATE TABLE IF NOT EXISTS `tax_work_gl` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `recordID` varchar(1) DEFAULT NULL COMMENT 'Must be a single character corresponding to record type.Must be ‘L’',
  `transactionDate` date DEFAULT NULL COMMENT 'Format: dd/MM/yyyy',
  `Account ID` int(11) DEFAULT NULL COMMENT 'General Ledger Code for individual account',
  `Account Name` varchar(3) DEFAULT NULL COMMENT 'Name of individual General Ledger account',
  `description` varchar(128) DEFAULT NULL COMMENT 'Transaction Description',
  `name` varchar(128) DEFAULT NULL COMMENT 'Name of entity involved (if applicable)',
  `journalID` varchar(128) DEFAULT NULL COMMENT 'A unique number that can group related double entries',
  `documentID` varchar(128) DEFAULT NULL COMMENT 'Source document number to which line relates (For example, cheque number, invoice number, credit note number, trust receipt number) ',
  `type` varchar(128) DEFAULT NULL COMMENT 'Refers to type of transaction such as AR. Refers to the type of transaction such as AR, AP, Inventory, Sales, Purchases, Cash Disbursement, Cash Receipt, General Journal etc.',
  `amount_dr` double DEFAULT NULL COMMENT 'Debit Amount',
  `amount_cr` double DEFAULT NULL COMMENT 'Credit amount',
  `Balance` double DEFAULT NULL COMMENT 'Balance Amount',
  `status` smallint(1) DEFAULT NULL,
  `gafID` 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=1 ;

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

--
-- Table structure for table `tax_work_header`
--

CREATE TABLE IF NOT EXISTS `tax_work_header` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `recordID` varchar(1) DEFAULT NULL COMMENT 'Must be a single character corresponding to record type. Must be ‘C’',
  `name` varchar(128) DEFAULT NULL COMMENT 'The name of the company for which this file is reporting on.',
  `BRN` varchar(128) DEFAULT NULL COMMENT 'Business registration number for company',
  `gstNo` varchar(128) DEFAULT NULL COMMENT 'Company GST number',
  `periodStart` date DEFAULT NULL COMMENT 'Filing Period Start Date Must be in format dd/MM/yyyy example: 01/12/2014’',
  `periodEnd` date DEFAULT NULL COMMENT 'Filing Period End Date Must be in format dd/MM/yyyy example: 31/12/2014’',
  `version` varchar(128) DEFAULT NULL COMMENT 'Accounting software name and version Example: XYB Accountsoft v.2.0',
  `GAFversion` varchar(128) DEFAULT NULL COMMENT 'GST Audit File (GAF) version number',
  `status` smallint(1) DEFAULT NULL,
  `gafID` smallint(1) 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=1 ;

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

--
-- Table structure for table `tax_work_purchase`
--

CREATE TABLE IF NOT EXISTS `tax_work_purchase` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `recordID` varchar(1) DEFAULT NULL COMMENT 'Must be a single character corresponding to record type. Must be ‘P’',
  `name` varchar(128) DEFAULT NULL COMMENT 'AN Name for supplier.',
  `BRN` varchar(128) DEFAULT NULL COMMENT 'BRN for company from which purchase was made.',
  `invoiceDate` date DEFAULT NULL COMMENT 'Invoice Date Format dd/MM/yyyy',
  `invoiceNo` varchar(128) DEFAULT NULL COMMENT 'Invoice Number',
  `importDeclare` varchar(128) DEFAULT NULL COMMENT 'Import Declaration Number. (e.g. Customs Form No. 1 reference',
  `invoiceLine` varchar(3) DEFAULT NULL COMMENT 'Number of Invoice Line',
  `description` varchar(128) DEFAULT NULL COMMENT 'Description for what was purchased',
  `amount` double DEFAULT NULL COMMENT 'Value of purchase excluding GST in Malaysia Ringgit.',
  `taxAmount` double DEFAULT NULL COMMENT 'Value of GST in Malaysia Ringgit.',
  `taxID` int(11) DEFAULT NULL,
  `taxCode` varchar(128) DEFAULT NULL COMMENT 'Tax code for lookup in tables. Please refer to Appendix',
  `currencyID` int(11) DEFAULT NULL,
  `currencyCode` varchar(128) DEFAULT NULL COMMENT 'ISO’s currency codes (3-letter alphabetical code) of foreign Currency. Example: USD',
  `currencyAmount` double DEFAULT NULL COMMENT 'Value of purchase excluding GST in Foreign Currency (If applicable).',
  `taxCurrencyAmount` double DEFAULT NULL COMMENT 'Value of GST in Foreign Currency (If applicable)',
  `status` smallint(2) DEFAULT NULL,
  `gafID` 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=1 ;

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

--
-- Table structure for table `tax_work_sales`
--

CREATE TABLE IF NOT EXISTS `tax_work_sales` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `recordID` varchar(1) DEFAULT NULL COMMENT 'Must be a single character corresponding to record type. Must be ‘S’',
  `name` varchar(128) DEFAULT NULL COMMENT 'Name of customer.',
  `BRN` varchar(128) DEFAULT NULL COMMENT 'BRN for supplier.',
  `invoiceDate` date DEFAULT NULL COMMENT 'Invoice Date Format dd/MM/yyyy',
  `invoiceNo` varchar(128) DEFAULT NULL COMMENT 'Invoice Number',
  `invoiceLine` varchar(3) DEFAULT NULL COMMENT 'Number of Invoice Line',
  `description` varchar(128) DEFAULT NULL COMMENT 'Description for what was sold',
  `amount` double DEFAULT NULL COMMENT 'Value of supply excluding GST in Malaysia Ringgit',
  `taxAmount` double DEFAULT NULL COMMENT 'Value of GST in Malaysia Ringgit',
  `taxID` int(11) DEFAULT NULL,
  `taxCode` varchar(128) DEFAULT NULL COMMENT 'Tax code for lookup in tables. Please refer to Appendix 2Example: SR',
  `countryID` int(11) DEFAULT NULL COMMENT 'Destination of goods being exported. Example: KOREA',
  `currencyID` int(11) DEFAULT NULL,
  `currencyCode` varchar(128) DEFAULT NULL COMMENT 'ISO’s currency codes (3-letter alphabetical code) of foreign Currency. Example: USD',
  `currencyAmount` double DEFAULT NULL COMMENT 'Value of purchase excluding GST in Foreign Currency (If applicable).',
  `taxCurrencyAmount` double DEFAULT NULL COMMENT 'Value of GST in Foreign Currency (If applicable)',
  `status` smallint(1) DEFAULT NULL,
  `gafID` 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=1 ;

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

--
-- Table structure for table `tbl_department`
--

CREATE TABLE IF NOT EXISTS `tbl_department` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(18) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `dept_head` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=6 ;

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

--
-- Table structure for table `tbl_location`
--

CREATE TABLE IF NOT EXISTS `tbl_location` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `desc` varchar(100) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `postcode` varchar(10) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state` int(10) DEFAULT NULL,
  `country` int(10) DEFAULT NULL,
  `phone_no` varchar(25) DEFAULT NULL,
  `fax_no` varchar(25) DEFAULT NULL,
  `create_by` varchar(50) DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

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

--
-- Table structure for table `tbl_section`
--

CREATE TABLE IF NOT EXISTS `tbl_section` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(18) DEFAULT NULL,
  `department` varchar(18) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `section_head` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=4 ;

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

--
-- Table structure for table `tbl_usermaster`
--

CREATE TABLE IF NOT EXISTS `tbl_usermaster` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` varchar(50) NOT NULL DEFAULT '0',
  `password` varchar(10) DEFAULT '0',
  `username` varchar(150) DEFAULT NULL,
  `position` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `homepage` varchar(100) DEFAULT NULL,
  `phone_off` varchar(30) DEFAULT NULL,
  `phone_ext` varchar(10) DEFAULT NULL,
  `phone_mobile` varchar(100) DEFAULT NULL,
  `department` varchar(18) DEFAULT NULL,
  `section` varchar(18) DEFAULT NULL,
  `role` varchar(150) DEFAULT NULL,
  `status` smallint(1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `online` int(5) NOT NULL,
  `create_dt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `create_user` varchar(150) DEFAULT NULL,
  `modified_dt` date DEFAULT NULL,
  `modified_user` varchar(150) DEFAULT NULL,
  `is_delete` smallint(6) DEFAULT '0',
  `language` varchar(10) NOT NULL,
  PRIMARY KEY (`fldid`),
  UNIQUE KEY `userid` (`userid`),
  KEY `Index 3` (`userid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=34 ;

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

--
-- Table structure for table `terms`
--

CREATE TABLE IF NOT EXISTS `terms` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `termName` varchar(64) NOT NULL,
  `termDescription` varchar(255) NOT NULL,
  `dueDays` int(11) NOT NULL,
  `termStatusID` int(11) 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=20 ;

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

--
-- Table structure for table `ug_content`
--

CREATE TABLE IF NOT EXISTS `ug_content` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ugID` int(11) NOT NULL,
  `language` varchar(8) NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;

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

--
-- Table structure for table `ug_relatedtopic`
--

CREATE TABLE IF NOT EXISTS `ug_relatedtopic` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ugID` int(11) NOT NULL,
  `relatedUgID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

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

--
-- Table structure for table `ug_topic`
--

CREATE TABLE IF NOT EXISTS `ug_topic` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(8) CHARACTER SET utf8 DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `parents` varchar(8) CHARACTER SET utf8 DEFAULT '0',
  `module` varchar(8) CHARACTER SET utf8 DEFAULT '0',
  `submenu` smallint(1) DEFAULT '0',
  `urls` varchar(100) CHARACTER SET utf8 DEFAULT '0',
  `urls2` varchar(100) CHARACTER SET utf8 DEFAULT '0',
  `access` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `seq` int(11) NOT NULL,
  `display` smallint(1) DEFAULT '1',
  `icons` varchar(32) CHARACTER SET utf8 DEFAULT 'i-film',
  `language` varchar(3) CHARACTER SET utf8 DEFAULT 'EN',
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=306 ;

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

--
-- Table structure for table `ug_video`
--

CREATE TABLE IF NOT EXISTS `ug_video` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ugID` int(11) NOT NULL,
  `title` varchar(64) NOT NULL,
  `videoLink` varchar(128) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=61 ;

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

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(32) NOT NULL,
  `password` varchar(64) NOT NULL,
  `fullName` varchar(64) NOT NULL,
  `telephoneNo` varchar(16) NOT NULL,
  `emailAddress` varchar(16) NOT NULL,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `user_module_access`
--

CREATE TABLE IF NOT EXISTS `user_module_access` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) DEFAULT NULL,
  `userID` int(11) NOT NULL,
  `moduleID` int(11) NOT NULL,
  `transaction` smallint(2) NOT NULL DEFAULT '0',
  `reports` smallint(2) NOT NULL DEFAULT '0',
  `setup` smallint(2) NOT NULL DEFAULT '0',
  `accessRight` int(11) NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=389 ;

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

--
-- Table structure for table `user_module_access_details`
--

CREATE TABLE IF NOT EXISTS `user_module_access_details` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) DEFAULT NULL,
  `userID` int(11) NOT NULL,
  `menuCode` varchar(8) NOT NULL,
  `accessRight` int(11) NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=1419 ;

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

--
-- Stand-in structure for view `view_account_balance`
--
CREATE TABLE IF NOT EXISTS `view_account_balance` (
`companyID` int(11)
,`acctID` int(11)
,`acctName` varchar(140)
,`year` int(11)
,`period` varchar(2)
,`closing` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_ar_inventory`
--
CREATE TABLE IF NOT EXISTS `view_ar_inventory` (
`companyID` int(11)
,`customerID` int(11)
,`formNo` varchar(42)
,`salesinvoiceDate` date
,`totalReceivable` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_customer_aging`
--
CREATE TABLE IF NOT EXISTS `view_customer_aging` (
`customerID` int(11)
,`current` double
,`month1` double
,`month2` double
,`month3` double
,`month4` double
,`month5` double
,`month6` double
,`month7` double
,`month8` double
,`month9` double
,`month10` double
,`month11` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_customer_statement`
--
CREATE TABLE IF NOT EXISTS `view_customer_statement` (
`ID` int(11)
,`parent` bigint(20)
,`companyID` int(11)
,`customerID` int(11)
,`transType` varchar(10)
,`transDate` date
,`refNo` varchar(32)
,`formNo` varchar(42)
,`dr` double
,`cr` double
,`seq` bigint(20)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_gaf`
--
CREATE TABLE IF NOT EXISTS `view_gaf` (
`companyID` int(11)
,`col1` varchar(1)
,`col2` varchar(128)
,`col3` varchar(32)
,`col4` date
,`col5` varchar(42)
,`col6` varchar(25)
,`col7` text
,`col8` text
,`col9` double
,`col10` varchar(22)
,`col11` varchar(45)
,`col12` varchar(5)
,`col13` double
,`col14` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_inventorylist_subtotal`
--
CREATE TABLE IF NOT EXISTS `view_inventorylist_subtotal` (
`companyid` int(11)
,`locationid` int(11)
,`itemID` int(11)
,`itemName` varchar(146)
,`category` varchar(64)
,`description` text
,`stockValue` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_inventory_statement`
--
CREATE TABLE IF NOT EXISTS `view_inventory_statement` (
`companyID` int(11)
,`transactionDate` date
,`itemID` int(11)
,`name` varchar(64)
,`supplierCustomer` varchar(128)
,`transactionCode` bigint(20)
,`transactionType` varchar(15)
,`formNo` varchar(42)
,`locationID` int(11)
,`location` varchar(100)
,`unitOfMeasure` varchar(50)
,`quantityIn` double
,`quantityOut` double
,`updateable` int(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_item_details`
--
CREATE TABLE IF NOT EXISTS `view_item_details` (
`companyid` int(11)
,`itemid` int(11)
,`itemName` varchar(98)
,`description` text
,`locationid` int(11)
,`onHandQty` double
,`umID` int(11)
,`acctID` int(11)
,`location` varchar(20)
,`unitOfMeasure` varchar(10)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_open_ar_invoice`
--
CREATE TABLE IF NOT EXISTS `view_open_ar_invoice` (
`companyID` int(11)
,`salesInvoiceDate` date
,`salesYear` varchar(4)
,`salesMonth` varchar(2)
,`slsMonth` varchar(32)
,`totalAmount` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_delivery_order`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_delivery_order` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_payment`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_payment` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_payment_others`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_payment_others` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_purchase_order`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_purchase_order` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_quotation`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_quotation` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_receive_item`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_receive_item` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_receive_payment`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_receive_payment` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_sales_creditnote`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_sales_creditnote` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_sales_debitnote`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_sales_debitnote` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_sales_invoice`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_sales_invoice` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_sales_order`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_sales_order` (
`companyID` int(11)
,`nextSerialNo` bigint(17)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_supplier_creditnote`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_supplier_creditnote` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_supplier_debitnote`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_supplier_debitnote` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_serial_comp_supplier_invoice`
--
CREATE TABLE IF NOT EXISTS `view_serial_comp_supplier_invoice` (
`companyID` int(11)
,`nextSerialNo` bigint(12)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_supplier_aging`
--
CREATE TABLE IF NOT EXISTS `view_supplier_aging` (
`supplierID` int(11)
,`current` double
,`month1` double
,`month2` double
,`month3` double
,`month4` double
,`month5` double
,`month6` double
,`month7` double
,`month8` double
,`month9` double
,`month10` double
,`month11` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_supplier_statement`
--
CREATE TABLE IF NOT EXISTS `view_supplier_statement` (
`ID` int(11)
,`parent` int(11)
,`companyID` int(11)
,`supplierID` int(11)
,`transType` varchar(10)
,`transDate` date
,`refNo` varchar(32)
,`formNo` varchar(42)
,`dr` double
,`cr` double
,`seq` bigint(20)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_total_comp_sales_creditnote`
--
CREATE TABLE IF NOT EXISTS `view_total_comp_sales_creditnote` (
`ID` int(11)
,`totalTaxAmount` double
,`totalAmount` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_total_comp_sales_debitnote`
--
CREATE TABLE IF NOT EXISTS `view_total_comp_sales_debitnote` (
`ID` int(11)
,`totalTaxAmount` double
,`totalAmount` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_total_comp_sales_invoice`
--
CREATE TABLE IF NOT EXISTS `view_total_comp_sales_invoice` (
`ID` int(11)
,`totalTaxAmount` double
,`totalAmount` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_total_comp_supplier_creditnote`
--
CREATE TABLE IF NOT EXISTS `view_total_comp_supplier_creditnote` (
`ID` int(11)
,`totalTaxAmount` double
,`totalAmount` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_total_comp_supplier_debitnote`
--
CREATE TABLE IF NOT EXISTS `view_total_comp_supplier_debitnote` (
`ID` int(11)
,`totalTaxAmount` double
,`totalAmount` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_total_comp_supplier_invoice`
--
CREATE TABLE IF NOT EXISTS `view_total_comp_supplier_invoice` (
`ID` int(11)
,`totalTaxAmount` double
,`totalAmount` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_ap_creditnote`
--
CREATE TABLE IF NOT EXISTS `view_unpost_ap_creditnote` (
`supplier` varchar(128)
,`creditnoteID` int(11)
,`projectID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`supplierID` int(11)
,`supplierInvoiceNo` varchar(32)
,`itemID` int(11)
,`itemName` varchar(98)
,`amountExcludedtax` double
,`taxid` int(11)
,`taxamount` double
,`drAccountID` bigint(11)
,`crAccountID` bigint(11)
,`drTaxAccountID` bigint(11)
,`crTaxAccountID` bigint(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_ap_debitnote`
--
CREATE TABLE IF NOT EXISTS `view_unpost_ap_debitnote` (
`projectID` int(11)
,`supplier` varchar(128)
,`debitnoteID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`supplierID` int(11)
,`supplierInvoiceNo` varchar(32)
,`itemID` int(11)
,`itemName` varchar(98)
,`amountExcludedtax` double
,`taxid` int(11)
,`taxamount` double
,`drAccountID` bigint(11)
,`crAccountID` bigint(11)
,`drTaxAccountID` bigint(11)
,`crTaxAccountID` bigint(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_ap_invoice`
--
CREATE TABLE IF NOT EXISTS `view_unpost_ap_invoice` (
`projectID` int(11)
,`supplier` varchar(128)
,`invoiceID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`supplierID` int(11)
,`lineNo` bigint(20)
,`itemID` int(11)
,`itemName` varchar(98)
,`amountExcludedtax` double
,`taxid` int(11)
,`taxamount` double
,`drAccountID` int(11)
,`crAccountID` int(11)
,`drTaxAccountID` int(11)
,`crTaxAccountID` int(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_ar_creditnote`
--
CREATE TABLE IF NOT EXISTS `view_unpost_ar_creditnote` (
`projectID` int(11)
,`salesinvoiceID` int(11)
,`creditnoteID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`customerID` int(11)
,`salesInvoiceNo` varchar(42)
,`lineNo` int(11)
,`itemID` int(11)
,`itemName` varchar(98)
,`amountExcludedtax` double
,`taxid` int(11)
,`taxamount` double
,`drAccountID` varchar(11)
,`crAccountID` bigint(11)
,`drTaxAccountID` varchar(11)
,`crTaxAccountID` bigint(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_ar_debitnote`
--
CREATE TABLE IF NOT EXISTS `view_unpost_ar_debitnote` (
`projectID` int(11)
,`customer` varchar(128)
,`salesinvoiceID` int(11)
,`debitnoteID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`customerID` int(11)
,`salesInvoiceNo` varchar(42)
,`lineNo` int(11)
,`itemID` int(11)
,`itemName` text
,`amountExcludedtax` double
,`taxid` int(11)
,`taxamount` double
,`drAccountID` varchar(11)
,`crAccountID` int(11)
,`drTaxAccountID` varchar(11)
,`crTaxAccountID` int(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_ar_invoice`
--
CREATE TABLE IF NOT EXISTS `view_unpost_ar_invoice` (
`projectID` int(11)
,`customer` varchar(128)
,`invoiceID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`customerID` int(11)
,`itemID` bigint(20)
,`itemName` mediumtext
,`amountExcludedtax` double
,`taxid` bigint(20)
,`taxamount` double
,`drAccountID` int(11)
,`crAccountID` varchar(11)
,`drTaxAccountID` int(11)
,`crTaxAccountID` varchar(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_ar_payment`
--
CREATE TABLE IF NOT EXISTS `view_unpost_ar_payment` (
`projectID` int(11)
,`paymentID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`customerID` int(11)
,`referenceNo` int(11)
,`paymentDetailID` int(11)
,`salesInvoiceNo` varchar(42)
,`amtPaid_amtAppl` double
,`discount_bankCharges` double
,`drAccountID` varchar(11)
,`crAccountID` binary(0)
,`drDiscountAccountID` varchar(11)
,`crDiscountAccountID` bigint(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_do`
--
CREATE TABLE IF NOT EXISTS `view_unpost_do` (
`deliveryOrderID` int(11)
,`formNo` varchar(32)
,`companyID` int(11)
,`customerID` int(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_other_payment`
--
CREATE TABLE IF NOT EXISTS `view_unpost_other_payment` (
`projectID` int(11)
,`paymentID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`supplierID` binary(0)
,`purchaseInvoiceID` binary(0)
,`paymentDetailID` int(11)
,`supplierInvoiceNo` binary(0)
,`lineNo` int(11)
,`itemdescription` text
,`amtPaid_amtAppl` double
,`discount_bankCharges` binary(0)
,`drAccountID` int(11)
,`crAccountID` int(11)
,`drDiscountAccountID` int(11)
,`crDiscountAccountID` int(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_payment`
--
CREATE TABLE IF NOT EXISTS `view_unpost_payment` (
`projectID` int(11)
,`paymentID` int(11)
,`formNo` varchar(42)
,`companyID` int(11)
,`supplierID` int(11)
,`purchaseInvoiceID` int(11)
,`paymentDetailID` int(11)
,`supplierInvoiceNo` varchar(32)
,`amtPaid_amtAppl` double
,`discount_bankCharges` double
,`drAccountID` int(11)
,`crAccountID` int(11)
,`drDiscountAccountID` int(11)
,`crDiscountAccountID` int(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_unpost_receive_item`
--
CREATE TABLE IF NOT EXISTS `view_unpost_receive_item` (
`receiveID` int(11)
,`companyID` int(11)
,`locationID` int(11)
,`transactionDate` date
,`supplierID` int(11)
,`formNo` varchar(42)
,`lineNo` int(11)
,`itemID` int(11)
,`itemName` varchar(98)
,`exchangeRate` float
,`unitPrice` double
,`quantityReceived` double
,`umID` int(11)
,`amount` double
,`drAccountID` bigint(11)
,`crAccountID` bigint(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `view_wakaf_accrual`
--
CREATE TABLE IF NOT EXISTS `view_wakaf_accrual` (
`companyID` int(11)
,`acctID` int(11)
,`totalAmount` double
);
-- --------------------------------------------------------

--
-- Table structure for table `wakaf`
--

CREATE TABLE IF NOT EXISTS `wakaf` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `waqifID` int(11) NOT NULL COMMENT 'link to mastercode',
  `wakafCenterID` int(11) NOT NULL,
  `allocation` tinyint(4) NOT NULL COMMENT '0=lumpsum,1=percentage',
  `periodYear` smallint(6) NOT NULL,
  `periodFrom` tinyint(4) NOT NULL,
  `periodTo` tinyint(4) NOT NULL,
  `amountAllocated` 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=39 ;

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

--
-- Table structure for table `wakaf_acrual`
--

CREATE TABLE IF NOT EXISTS `wakaf_acrual` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `salesAccountID` int(11) NOT NULL,
  `from` date NOT NULL,
  `to` date NOT NULL,
  `wakafPercent` decimal(10,0) 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=13 ;

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

--
-- Table structure for table `wakaf_beneficiaries`
--

CREATE TABLE IF NOT EXISTS `wakaf_beneficiaries` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `shortName` varchar(12) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdBy` int(11) NOT NULL,
  `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 ROW_FORMAT=COMPACT AUTO_INCREMENT=7 ;

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

--
-- Table structure for table `wakaf_center`
--

CREATE TABLE IF NOT EXISTS `wakaf_center` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `shortName` varchar(12) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `line1` varchar(128) NOT NULL,
  `line2` varchar(128) NOT NULL,
  `line3` varchar(128) NOT NULL,
  `city` varchar(64) NOT NULL,
  `postCode` varchar(8) NOT NULL,
  `stateID` int(11) NOT NULL,
  `countryID` int(11) NOT NULL,
  `noTel` varchar(50) NOT NULL,
  `noFax` varchar(50) NOT NULL,
  `email` varchar(128) NOT NULL,
  `website` varchar(128) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdBy` int(11) NOT NULL,
  `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 ROW_FORMAT=COMPACT AUTO_INCREMENT=40 ;

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

--
-- Table structure for table `wakaf_details_allocation`
--

CREATE TABLE IF NOT EXISTS `wakaf_details_allocation` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `wakafID` varchar(64) NOT NULL DEFAULT '0',
  `accountID` int(11) NOT NULL,
  `saleAmount` double NOT NULL,
  `contributionPercentage` double NOT NULL,
  `amount` double NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdBy` int(11) NOT NULL,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=42 ;

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

--
-- Table structure for table `wakaf_details_distribution`
--

CREATE TABLE IF NOT EXISTS `wakaf_details_distribution` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `wakafID` varchar(64) NOT NULL DEFAULT '0',
  `beneficiariesID` int(11) NOT NULL,
  `description` varchar(128) NOT NULL,
  `contributionPercentage` double NOT NULL,
  `amount` double NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdBy` int(11) NOT NULL,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=94 ;

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

--
-- Table structure for table `wakaf_master`
--

CREATE TABLE IF NOT EXISTS `wakaf_master` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL DEFAULT '0',
  `waqif` varchar(64) NOT NULL DEFAULT '0',
  `category` varchar(24) NOT NULL,
  `allocationID` int(11) NOT NULL,
  `beneficiariesID` int(11) NOT NULL,
  `description` varchar(128) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdBy` int(11) NOT NULL,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=16 ;

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

--
-- Table structure for table `zakat_center`
--

CREATE TABLE IF NOT EXISTS `zakat_center` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `shortName` varchar(12) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `line1` varchar(128) NOT NULL,
  `line2` varchar(128) NOT NULL,
  `line3` varchar(128) NOT NULL,
  `city` varchar(64) NOT NULL,
  `postCode` varchar(8) NOT NULL,
  `stateID` int(11) NOT NULL,
  `countryID` int(11) NOT NULL,
  `noTel` varchar(50) NOT NULL,
  `noFax` varchar(50) NOT NULL,
  `email` varchar(128) NOT NULL,
  `website` varchar(128) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdBy` int(11) NOT NULL,
  `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 ROW_FORMAT=COMPACT AUTO_INCREMENT=7 ;

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

--
-- Table structure for table `zakat_details`
--

CREATE TABLE IF NOT EXISTS `zakat_details` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL DEFAULT '1',
  `groupID` int(11) DEFAULT NULL,
  `acctID` int(11) DEFAULT NULL,
  `year` varchar(4) DEFAULT NULL,
  `totalAmount` double 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=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `zakat_group`
--

CREATE TABLE IF NOT EXISTS `zakat_group` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL DEFAULT '1',
  `name` varchar(50) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `parentID` int(11) DEFAULT '0',
  `sequence` 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=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=11 ;

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

--
-- Table structure for table `zakat_group_details`
--

CREATE TABLE IF NOT EXISTS `zakat_group_details` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL DEFAULT '1',
  `groupID` int(11) DEFAULT NULL,
  `acctID` 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=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=12 ;

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

--
-- Table structure for table `zakat_nisab`
--

CREATE TABLE IF NOT EXISTS `zakat_nisab` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL DEFAULT '0',
  `centerID` int(11) DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `nisab` double 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=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=4 ;

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

--
-- Table structure for table `zakat_summary`
--

CREATE TABLE IF NOT EXISTS `zakat_summary` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL DEFAULT '1',
  `centerID` int(11) NOT NULL DEFAULT '1',
  `year` varchar(4) DEFAULT NULL,
  `totalAmount` double DEFAULT NULL,
  `totalZakatPaid` double 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=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=6 ;

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

--
-- Table structure for table `_log_query`
--

CREATE TABLE IF NOT EXISTS `_log_query` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `companyID` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `userIP` varchar(30) NOT NULL,
  `type` varchar(50) NOT NULL,
  `query` text NOT NULL,
  `executionTime` varchar(256) NOT NULL,
  `createdTS` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5975 ;

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

--
-- Table structure for table `_ref_acct_group`
--

CREATE TABLE IF NOT EXISTS `_ref_acct_group` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `acctTypeID` int(11) NOT NULL,
  `acctGroupName` varchar(128) NOT NULL,
  `acctCode` char(9) NOT NULL,
  `orderNo` int(11) NOT NULL,
  `parentID` int(11) NOT NULL,
  `title` tinyint(4) 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,
  KEY `ID` (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ;

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

--
-- Table structure for table `_ref_acct_group_bakup`
--

CREATE TABLE IF NOT EXISTS `_ref_acct_group_bakup` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `acctTypeID` int(11) NOT NULL,
  `acctGroupName` varchar(128) NOT NULL,
  `acctCode` char(9) NOT NULL,
  `orderNo` int(11) NOT NULL,
  `parentID` int(11) 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `_ref_acct_type`
--

CREATE TABLE IF NOT EXISTS `_ref_acct_type` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `acctTypeName` varchar(128) NOT NULL,
  `acctClass` smallint(6) NOT NULL COMMENT '0=Balance Shit, 1=Profit and Lost',
  `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=5 ;

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

--
-- Table structure for table `_ref_business_type`
--

CREATE TABLE IF NOT EXISTS `_ref_business_type` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `industryID` int(11) NOT NULL,
  `name` varchar(128) 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=27 ;

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

--
-- Table structure for table `_ref_chart_of_acct`
--

CREATE TABLE IF NOT EXISTS `_ref_chart_of_acct` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `businessTypeID` int(11) NOT NULL,
  `acctGroupID` int(11) NOT NULL,
  `acctCode` char(9) NOT NULL,
  `parentID` int(11) NOT NULL,
  `acctName` varchar(128) NOT NULL,
  `acctDescription` varchar(128) NOT NULL,
  `defaultID` int(11) DEFAULT NULL COMMENT 'id default acct for general setup',
  `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=549 ;

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

--
-- Table structure for table `_ref_control_acct_setup`
--

CREATE TABLE IF NOT EXISTS `_ref_control_acct_setup` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(4) NOT NULL,
  `name` varchar(64) NOT NULL,
  `shortName` varchar(8) NOT NULL,
  `description` varchar(128) NOT NULL,
  `orderNo` int(11) NOT NULL,
  `defaultAcctCode` varchar(9) NOT NULL,
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateBy` int(11) NOT NULL,
  `updateTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `_ref_country`
--

CREATE TABLE IF NOT EXISTS `_ref_country` (
  `fldid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fldcountry` varchar(45) DEFAULT NULL,
  `fldcontinent` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=895 ;

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

--
-- Table structure for table `_ref_currency`
--

CREATE TABLE IF NOT EXISTS `_ref_currency` (
  `fldid` int(5) NOT NULL AUTO_INCREMENT,
  `fldcurr_code` varchar(5) DEFAULT NULL,
  `fldcurr_desc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=13 ;

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

--
-- Table structure for table `_ref_dateformat`
--

CREATE TABLE IF NOT EXISTS `_ref_dateformat` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(24) DEFAULT NULL,
  `codeJava` varchar(24) DEFAULT NULL,
  `description` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

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

--
-- Table structure for table `_ref_industry`
--

CREATE TABLE IF NOT EXISTS `_ref_industry` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) 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=5 ;

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

--
-- Table structure for table `_ref_language`
--

CREATE TABLE IF NOT EXISTS `_ref_language` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(3) DEFAULT '0',
  `description` varchar(64) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

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

--
-- Table structure for table `_ref_state`
--

CREATE TABLE IF NOT EXISTS `_ref_state` (
  `fldid` int(10) NOT NULL AUTO_INCREMENT,
  `fldregion_id` int(10) unsigned DEFAULT NULL,
  `fldname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`fldid`),
  KEY `subregion_region_id` (`fldregion_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=3880 ;

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

--
-- Table structure for table `_ref_tax_master`
--

CREATE TABLE IF NOT EXISTS `_ref_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,
  `defaultTaxAccount` varchar(9) 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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `_sys_menu`
--

CREATE TABLE IF NOT EXISTS `_sys_menu` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(8) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `parents` varchar(8) DEFAULT '0',
  `module` varchar(8) DEFAULT '0',
  `submenu` smallint(1) DEFAULT '0',
  `urls` varchar(100) DEFAULT '0',
  `urls2` varchar(100) DEFAULT '0',
  `access` varchar(100) DEFAULT NULL,
  `seq` int(11) NOT NULL,
  `display` smallint(1) DEFAULT '1',
  `icons` varchar(32) DEFAULT 'i-film',
  `language` varchar(3) DEFAULT 'EN',
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=311 ;

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

--
-- Table structure for table `_sys_um`
--

CREATE TABLE IF NOT EXISTS `_sys_um` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) DEFAULT NULL,
  `desc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=15 ;

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

--
-- Table structure for table `_sys_um_conv`
--

CREATE TABLE IF NOT EXISTS `_sys_um_conv` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `from_um` int(11) DEFAULT NULL,
  `to_um` int(11) DEFAULT NULL,
  `unitm` decimal(10,5) DEFAULT NULL,
  `factor` decimal(10,5) DEFAULT NULL,
  `create_by` varchar(50) DEFAULT NULL,
  `create_dt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` varchar(50) DEFAULT NULL,
  `modified_dt` datetime DEFAULT NULL,
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

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

--
-- Structure for view `view_account_balance`
--
DROP TABLE IF EXISTS `view_account_balance`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_account_balance` AS select `a`.`companyID` AS `companyID`,`a`.`acctID` AS `acctID`,concat(left(`b`.`acctCode`,4),'-',substr(`b`.`acctCode`,4,3),'-',right(`b`.`acctCode`,2),' ',`b`.`acctName`) AS `acctName`,`a`.`year` AS `year`,`a`.`period` AS `period`,sum(`a`.`total`) AS `closing` from (`comp_acc_det` `a` join `comp_chart_of_acct` `b`) where (`b`.`ID` = `a`.`acctID`) group by `a`.`companyID`,`a`.`acctID`,`b`.`acctName`,`a`.`year`,`a`.`period`;

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

--
-- Structure for view `view_ar_inventory`
--
DROP TABLE IF EXISTS `view_ar_inventory`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_ar_inventory` AS select `comp_sales_invoice`.`companyID` AS `companyID`,`comp_sales_invoice`.`customerID` AS `customerID`,concat(`comp_sales_invoice`.`formNo`,'[',convert(lpad(`comp_sales_invoice`.`ID`,8,'0') using latin1),']') AS `formNo`,`comp_sales_invoice`.`salesInvoiceDate` AS `salesinvoiceDate`,`comp_sales_invoice`.`totalReceivable` AS `totalReceivable` from `comp_sales_invoice` where (`comp_sales_invoice`.`totalReceivable` > 0);

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

--
-- Structure for view `view_customer_aging`
--
DROP TABLE IF EXISTS `view_customer_aging`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_customer_aging` AS select `a`.`ID` AS `customerID`,(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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) AS `month11` from `customer` `a`;

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

--
-- Structure for view `view_customer_statement`
--
DROP TABLE IF EXISTS `view_customer_statement`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 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` union select `comp_sales_debitnote`.`ID` AS `ID`,`comp_sales_debitnote`.`salesInvoiceID` AS `parent`,`comp_sales_debitnote`.`companyID` AS `companyID`,`comp_sales_debitnote`.`customerID` AS `customerID`,'DebitNote' AS `transType`,`comp_sales_debitnote`.`debitNoteDate` AS `transDate`,NULL AS `refno`,concat(concat(concat(`comp_sales_debitnote`.`formNo`,'['),convert(lpad(`comp_sales_debitnote`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`comp_sales_debitnote`.`totalAmount` AS `cr`,2 AS `seq` from `comp_sales_debitnote` union select `comp_sales_creditnote`.`ID` AS `ID`,`comp_sales_creditnote`.`salesInvoiceID` AS `parent`,`comp_sales_creditnote`.`companyID` AS `companyID`,`comp_sales_creditnote`.`customerID` AS `customerID`,'CreditNote' AS `transType`,`comp_sales_creditnote`.`creditNoteDate` AS `transDate`,NULL AS `refno`,concat(concat(concat(`comp_sales_creditnote`.`formNo`,'['),convert(lpad(`comp_sales_creditnote`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`comp_sales_creditnote`.`totalAmount` AS `dr`,NULL AS `cr`,3 AS `seq` from `comp_sales_creditnote` 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`;

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

--
-- Structure for view `view_gaf`
--
DROP TABLE IF EXISTS `view_gaf`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_gaf` AS select `a`.`companyID` AS `companyID`,'P' AS `col1`,`a`.`supplierName` AS `col2`,`a`.`supplierCompanyNo` AS `col3`,`b`.`invoiceDate` AS `col4`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,'import declaration number' AS `col6`,`c`.`lineNo` AS `col7`,`c`.`itemdescription` AS `col8`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col9`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col10`,`e`.`code` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` from ((((`supplier` `a` join `comp_supplier_invoice` `b`) join `comp_supplier_invoice_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) where ((`a`.`ID` = `b`.`supplierID`) and (`b`.`ID` = `c`.`invoiceID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`)) union select `a`.`companyID` AS `companyID`,'P' AS `col1`,`a`.`supplierName` AS `col2`,`a`.`supplierCompanyNo` AS `col3`,`b`.`invoiceDate` AS `col4`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,'import declaration number' AS `col6`,`c`.`lineNo` AS `col7`,`c`.`itemdescription` AS `col8`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col9`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col10`,`e`.`code` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` from ((((`supplier` `a` join `comp_supplier_invoice` `b`) join `comp_supplier_invoice_charges` `c`) join `_ref_currency` `d`) join `tax_master` `e`) where ((`a`.`ID` = `b`.`supplierID`) and (`b`.`ID` = `c`.`invoiceID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`)) union select `a`.`companyID` AS `companyID`,'P' AS `col1`,`a`.`supplierName` AS `col2`,`a`.`supplierCompanyNo` AS `col3`,`b`.`debitNoteDate` AS `col4`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,'import declaration number' AS `col6`,`c`.`lineNo` AS `col7`,`c`.`itemdescription` AS `col8`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col9`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col10`,`e`.`code` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` from ((((`supplier` `a` join `comp_supplier_debitnote` `b`) join `comp_supplier_debitnote_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) where ((`a`.`ID` = `b`.`supplierID`) and (`b`.`ID` = `c`.`debitnoteID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`)) union select `a`.`companyID` AS `companyID`,'P' AS `col1`,`a`.`supplierName` AS `col2`,`a`.`supplierCompanyNo` AS `col3`,`b`.`creditNoteDate` AS `col4`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,'import declaration number' AS `col6`,`c`.`lineNo` AS `col7`,`c`.`itemdescription` AS `col8`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col9`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col10`,`e`.`code` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` from ((((`supplier` `a` join `comp_supplier_creditnote` `b`) join `comp_supplier_creditnote_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) where ((`a`.`ID` = `b`.`supplierID`) and (`b`.`ID` = `c`.`creditnoteID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`)) union select `a`.`companyID` AS `companyID`,'S' AS `col1`,`a`.`customerName` AS `col2`,`a`.`customerCompanyNo` AS `col3`,`b`.`salesInvoiceDate` AS `col4`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,`c`.`lineNo` AS `col6`,`g`.`description` AS `col7`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col8`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col9`,`e`.`code` AS `col10`,`f`.`fldcountry` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` from ((((((`customer` `a` join `comp_sales_invoice` `b`) join `comp_sales_invoice_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) join `_ref_country` `f`) join `item` `g`) where ((`a`.`ID` = `b`.`customerID`) and (`b`.`ID` = `c`.`invoiceID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`) and (`f`.`fldid` = `a`.`customerCountryID`) and (`g`.`ID` = `c`.`itemID`)) union select `a`.`companyID` AS `companyID`,'S' AS `col1`,`a`.`customerName` AS `col2`,`a`.`customerCompanyNo` AS `col3`,`b`.`salesInvoiceDate` AS `col4`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,`c`.`lineNo` AS `col6`,`c`.`chargeDescription` AS `col7`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col8`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col9`,`e`.`code` AS `col10`,`f`.`fldcountry` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` from (((((`customer` `a` join `comp_sales_invoice` `b`) join `comp_sales_invoice_charges` `c`) join `_ref_currency` `d`) join `tax_master` `e`) join `_ref_country` `f`) where ((`a`.`ID` = `b`.`customerID`) and (`b`.`ID` = `c`.`invoiceID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`) and (`f`.`fldid` = `a`.`customerCountryID`)) union select `a`.`companyID` AS `companyID`,'S' AS `col1`,`a`.`customerName` AS `col2`,`a`.`customerCompanyNo` AS `col3`,`b`.`debitNoteDate` AS `col4`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,`c`.`lineNo` AS `col6`,`c`.`itemdescription` AS `col7`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col8`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col9`,`e`.`code` AS `col10`,`f`.`fldcountry` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` from (((((`customer` `a` join `comp_sales_debitnote` `b`) join `comp_sales_debitnote_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) join `_ref_country` `f`) where ((`a`.`ID` = `b`.`customerID`) and (`b`.`ID` = `c`.`debitnoteID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`) and (`f`.`fldid` = `a`.`customerCountryID`)) union select `a`.`companyID` AS `companyID`,'S' AS `col1`,`a`.`customerName` AS `col2`,`a`.`customerCompanyNo` AS `col3`,`b`.`creditNoteDate` AS `col4`,concat(`b`.`formNo`,'[',convert(lpad(`b`.`ID`,8,'0') using latin1),']') AS `col5`,`c`.`lineNo` AS `col6`,`c`.`itemdescription` AS `col7`,(`c`.`amountExcludedTax` * `b`.`exchangeRate`) AS `col8`,(`c`.`taxAmount` * `b`.`exchangeRate`) AS `col9`,`e`.`code` AS `col10`,`f`.`fldcountry` AS `col11`,`d`.`fldcurr_code` AS `col12`,`c`.`amountExcludedTax` AS `col13`,`c`.`taxAmount` AS `col14` from (((((`customer` `a` join `comp_sales_creditnote` `b`) join `comp_sales_creditnote_detail` `c`) join `_ref_currency` `d`) join `tax_master` `e`) join `_ref_country` `f`) where ((`a`.`ID` = `b`.`customerID`) and (`b`.`ID` = `c`.`creditnoteID`) and (`d`.`fldid` = `a`.`currencyID`) and (`e`.`ID` = `c`.`taxID`) and (`f`.`fldid` = `a`.`customerCountryID`));

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

--
-- Structure for view `view_inventorylist_subtotal`
--
DROP TABLE IF EXISTS `view_inventorylist_subtotal`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_inventorylist_subtotal` AS select `a`.`companyID` AS `companyid`,`a`.`locationID` AS `locationid`,`b`.`itemID` AS `itemID`,concat('[',`b`.`itemCode`,']',`b`.`itemName`) AS `itemName`,`d`.`name` AS `category`,`b`.`description` AS `description`,sum((`b`.`balanceItem` * `b`.`unitPrice`)) AS `stockValue` from (((`comp_receive_item` `a` join `comp_receive_item_detail` `b`) join `item` `c`) join `master_code` `d`) where ((`a`.`ID` = `b`.`receiveID`) and (`b`.`balanceItem` > 0) and (`c`.`ID` = `b`.`itemID`) and (`c`.`itemCategoryID` = `d`.`ID`)) group by `a`.`companyID`,`a`.`locationID`,`b`.`itemID`,`b`.`itemName`,`category`,`b`.`description`;

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

--
-- Structure for view `view_inventory_statement`
--
DROP TABLE IF EXISTS `view_inventory_statement`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_inventory_statement` AS select `a`.`companyID` AS `companyID`,`a`.`deliveryDate` AS `transactionDate`,`b`.`itemID` AS `itemID`,`c`.`name` AS `name`,`g`.`supplierName` AS `supplierCustomer`,1 AS `transactionCode`,'Purchase' AS `transactionType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `formNo`,`a`.`locationID` AS `locationID`,`d`.`desc` AS `location`,`e`.`desc` AS `unitOfMeasure`,`b`.`quantityReceived` AS `quantityIn`,NULL AS `quantityOut`,`a`.`updateAble` AS `updateable` from ((((((`comp_receive_item` `a` left join `comp_receive_item_detail` `b` on((`a`.`ID` = `b`.`receiveID`))) left join `item` `c` on((`b`.`itemID` = `c`.`ID`))) left join `item_details` `f` on((`c`.`ID` = `f`.`itemID`))) join `tbl_location` `d`) join `_sys_um` `e`) join `supplier` `g`) where ((`f`.`locationID` = `d`.`fldid`) and (`e`.`fldid` = `c`.`unitOfMeasureID`) and (`a`.`supplierID` = `f`.`ID`)) union select `a`.`companyID` AS `companyID`,`a`.`adjustmentDate` AS `transactionDate`,`b`.`itemID` AS `itemID`,`c`.`name` AS `name`,`d`.`desc` AS `supplierCustomer`,2 AS `transactionCode`,'stockAdjustment' AS `transactionType`,`a`.`formNo` AS `formNo`,`a`.`locationID` AS `locationID`,`d`.`desc` AS `location`,`e`.`desc` AS `unitOfMeasure`,if(((`b`.`counted` - `b`.`qtyOnHand`) > 0),(`b`.`counted` - `b`.`qtyOnHand`),NULL) AS `quantityIn`,if(((`b`.`counted` - `b`.`qtyOnHand`) < 0),(`b`.`counted` - `b`.`qtyOnHand`),NULL) AS `quantityOut`,`a`.`updateAble` AS `updateable` from (((((`item_adjustment` `a` left join `item_adjustment_detail` `b` on((`a`.`ID` = `b`.`adjustmentID`))) left join `item` `c` on((`b`.`itemID` = `c`.`ID`))) left join `item_details` `f` on((`c`.`ID` = `f`.`itemID`))) left join `tbl_location` `d` on((`d`.`fldid` = `a`.`locationID`))) left join `_sys_um` `e` on((`e`.`fldid` = `b`.`unitOfMeasureID`))) union select `a`.`companyID` AS `companyID`,`a`.`deliveryOrderDate` AS `transactionDate`,`b`.`itemID` AS `itemID`,`c`.`name` AS `name`,`g`.`customerName` AS `supplierCustomer`,3 AS `transactionCode`,'Sale' AS `transactionType`,`a`.`formNo` AS `formNo`,`a`.`locationID` AS `locationID`,`d`.`desc` AS `location`,`e`.`desc` AS `unitOfMeasure`,NULL AS `quantityIn`,(`b`.`quantitySent` * -(1)) AS `quantityOut`,`a`.`updateAble` AS `updateable` from ((((((`comp_delivery_order` `a` left join `comp_delivery_detail` `b` on((`a`.`ID` = `b`.`deliveryID`))) left join `item` `c` on((`b`.`itemID` = `c`.`ID`))) left join `item_details` `f` on((`c`.`ID` = `f`.`itemID`))) join `tbl_location` `d`) join `_sys_um` `e`) join `customer` `g`) where ((`a`.`locationID` = `d`.`fldid`) and (`e`.`fldid` = `c`.`unitOfMeasureID`) and (`a`.`customerID` = `f`.`ID`)) union select `a`.`companyID` AS `companyID`,`a`.`dateTransfer` AS `transactionDate`,`b`.`itemID` AS `itemID`,`c`.`name` AS `name`,`d`.`desc` AS `supplierCustomer`,4 AS `transactionCode`,'TransferOut' AS `transactionType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`ID`,8,'0') using latin1),']') AS `formNo`,`a`.`fromLocationID` AS `locationID`,`d`.`desc` AS `location`,`e`.`desc` AS `unitOfMeasure`,NULL AS `quantityIn`,(`b`.`quantity` * -(1)) AS `quantityOut`,`a`.`updateable` AS `updateable` from ((((`comp_location_transfer` `a` left join `comp_location_transfer_detail` `b` on((`a`.`ID` = `b`.`locationTransferID`))) left join `item` `c` on((`b`.`itemID` = `c`.`ID`))) join `tbl_location` `d`) join `_sys_um` `e`) where ((`a`.`fromLocationID` = `d`.`fldid`) and (`e`.`fldid` = `c`.`unitOfMeasureID`)) union select `a`.`companyID` AS `companyID`,`a`.`dateTransfer` AS `transactionDate`,`b`.`itemID` AS `itemID`,`c`.`name` AS `name`,`d`.`desc` AS `supplierCustomer`,5 AS `transactionCode`,'TransferIn' AS `transactionType`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`ID`,8,'0') using latin1),']') AS `formNo`,`a`.`toLocationID` AS `locationID`,`d`.`desc` AS `location`,`e`.`desc` AS `unitOfMeasure`,`b`.`quantity` AS `quantityIn`,NULL AS `quantityOut`,`a`.`updateable` AS `updateable` from ((((`comp_location_transfer` `a` left join `comp_location_transfer_detail` `b` on((`a`.`ID` = `b`.`locationTransferID`))) left join `item` `c` on((`b`.`itemID` = `c`.`ID`))) join `tbl_location` `d`) join `_sys_um` `e`) where ((`a`.`fromLocationID` = `d`.`fldid`) and (`e`.`fldid` = `c`.`unitOfMeasureID`));

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

--
-- Structure for view `view_item_details`
--
DROP TABLE IF EXISTS `view_item_details`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_item_details` AS select `b`.`companyID` AS `companyid`,`a`.`itemID` AS `itemid`,concat('[',`c`.`itemCode`,']',`c`.`name`) AS `itemName`,`c`.`description` AS `description`,`b`.`locationID` AS `locationid`,sum(`a`.`balanceItem`) AS `onHandQty`,`a`.`umID` AS `umID`,`c`.`inventoriesID` AS `acctID`,`d`.`code` AS `location`,`e`.`code` AS `unitOfMeasure` from ((((`comp_receive_item_detail` `a` join `comp_receive_item` `b`) join `item` `c`) join `tbl_location` `d`) join `_sys_um` `e`) where ((`a`.`receiveID` = `b`.`ID`) and (`a`.`itemID` = `c`.`ID`) and (`d`.`fldid` = `b`.`locationID`) and (`e`.`fldid` = `a`.`umID`) and (`a`.`balanceItem` > 0)) group by `b`.`companyID`,`a`.`itemID`,`a`.`itemName`,`c`.`description`,`b`.`locationID`,`a`.`umID`,`acctID`,`location`,`unitOfMeasure`;

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

--
-- Structure for view `view_open_ar_invoice`
--
DROP TABLE IF EXISTS `view_open_ar_invoice`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.1.174` SQL SECURITY DEFINER VIEW `view_open_ar_invoice` AS select `comp_sales_invoice`.`companyID` AS `companyID`,`comp_sales_invoice`.`salesInvoiceDate` AS `salesInvoiceDate`,date_format(`comp_sales_invoice`.`salesInvoiceDate`,'%Y') AS `salesYear`,date_format(`comp_sales_invoice`.`salesInvoiceDate`,'%m') AS `salesMonth`,date_format(`comp_sales_invoice`.`salesInvoiceDate`,'%b') AS `slsMonth`,(`comp_sales_invoice`.`totalReceivable` * if((`comp_sales_invoice`.`exchangeRate` = 0),1,`comp_sales_invoice`.`exchangeRate`)) AS `totalAmount` from `comp_sales_invoice` where (`comp_sales_invoice`.`totalReceivable` <> 0);

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

--
-- Structure for view `view_serial_comp_delivery_order`
--
DROP TABLE IF EXISTS `view_serial_comp_delivery_order`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_delivery_order` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_delivery_order` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 49))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_payment`
--
DROP TABLE IF EXISTS `view_serial_comp_payment`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_payment` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_payment` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 44))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_payment_others`
--
DROP TABLE IF EXISTS `view_serial_comp_payment_others`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_payment_others` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_payment_others` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 55))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_purchase_order`
--
DROP TABLE IF EXISTS `view_serial_comp_purchase_order`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_purchase_order` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`po`.`serialNo`) from `comp_purchase_order` `po` where (`c`.`ID` = `po`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 40))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_quotation`
--
DROP TABLE IF EXISTS `view_serial_comp_quotation`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_quotation` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_quotation` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 47))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_receive_item`
--
DROP TABLE IF EXISTS `view_serial_comp_receive_item`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_receive_item` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_receive_item` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 54))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_receive_payment`
--
DROP TABLE IF EXISTS `view_serial_comp_receive_payment`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_receive_payment` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_receive_payment` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 53))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_sales_creditnote`
--
DROP TABLE IF EXISTS `view_serial_comp_sales_creditnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_sales_creditnote` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_sales_creditnote` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 52))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_sales_debitnote`
--
DROP TABLE IF EXISTS `view_serial_comp_sales_debitnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_sales_debitnote` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_sales_debitnote` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 51))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_sales_invoice`
--
DROP TABLE IF EXISTS `view_serial_comp_sales_invoice`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_sales_invoice` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_sales_invoice` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 50))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_sales_order`
--
DROP TABLE IF EXISTS `view_serial_comp_sales_order`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_sales_order` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_sales_order` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 48))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_supplier_creditnote`
--
DROP TABLE IF EXISTS `view_serial_comp_supplier_creditnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_supplier_creditnote` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_supplier_creditnote` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 43))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_supplier_debitnote`
--
DROP TABLE IF EXISTS `view_serial_comp_supplier_debitnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_supplier_debitnote` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_supplier_debitnote` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 42))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_serial_comp_supplier_invoice`
--
DROP TABLE IF EXISTS `view_serial_comp_supplier_invoice`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_supplier_invoice` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_supplier_invoice` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 41))),1)) AS `nextSerialNo` from `company` `c`;

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

--
-- Structure for view `view_supplier_aging`
--
DROP TABLE IF EXISTS `view_supplier_aging`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_supplier_aging` AS select `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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) 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))) AS `month11` from `supplier` `a`;

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

--
-- Structure for view `view_supplier_statement`
--
DROP TABLE IF EXISTS `view_supplier_statement`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 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`,`comp_supplier_invoice`.`totalAmount` AS `dr`,NULL AS `cr`,1 AS `seq` from `comp_supplier_invoice` where (`comp_supplier_invoice`.`updateAble` = 0) union select `comp_supplier_debitnote`.`ID` AS `ID`,`comp_supplier_debitnote`.`supplierInvoiceID` AS `parent`,`comp_supplier_debitnote`.`companyID` AS `companyID`,`comp_supplier_debitnote`.`supplierID` AS `supplierID`,'DebitNote' AS `transType`,`comp_supplier_debitnote`.`debitNoteDate` AS `transDate`,NULL AS `refno`,concat(concat(concat(`comp_supplier_debitnote`.`formNo`,'['),convert(lpad(`comp_supplier_debitnote`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`comp_supplier_debitnote`.`totalAmount` AS `cr`,2 AS `seq` from `comp_supplier_debitnote` where (`comp_supplier_debitnote`.`updateAble` = 0) union select `comp_supplier_creditnote`.`ID` AS `ID`,`comp_supplier_creditnote`.`supplierInvoiceID` AS `parent`,`comp_supplier_creditnote`.`companyID` AS `companyID`,`comp_supplier_creditnote`.`supplierID` AS `supplierID`,'CreditNote' AS `transType`,`comp_supplier_creditnote`.`creditNoteDate` AS `transDate`,NULL AS `refno`,concat(concat(concat(`comp_supplier_creditnote`.`formNo`,'['),convert(lpad(`comp_supplier_creditnote`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`comp_supplier_creditnote`.`totalAmount` AS `dr`,NULL AS `cr`,3 AS `seq` from `comp_supplier_creditnote` where (`comp_supplier_creditnote`.`updateAble` = 0) 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);

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

--
-- Structure for view `view_total_comp_sales_creditnote`
--
DROP TABLE IF EXISTS `view_total_comp_sales_creditnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_total_comp_sales_creditnote` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`comp_sales_creditnote_charges`.`taxAmount`),0) from `comp_sales_creditnote_charges` where (`comp_sales_creditnote_charges`.`creditNoteID` = `a`.`ID`)) + (select ifnull(sum(`comp_sales_creditnote_detail`.`taxAmount`),0) from `comp_sales_creditnote_detail` where (`comp_sales_creditnote_detail`.`creditnoteID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`comp_sales_creditnote_detail`.`amountIncludedTax`),0) from `comp_sales_creditnote_detail` where (`comp_sales_creditnote_detail`.`creditnoteID` = `a`.`ID`)) + (select ifnull(sum(`comp_sales_creditnote_charges`.`amountIncludedTax`),0) from `comp_sales_creditnote_charges` where (`comp_sales_creditnote_charges`.`creditNoteID` = `a`.`ID`))) AS `totalAmount` from `comp_sales_creditnote` `a` group by `a`.`ID`;

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

--
-- Structure for view `view_total_comp_sales_debitnote`
--
DROP TABLE IF EXISTS `view_total_comp_sales_debitnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_total_comp_sales_debitnote` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`comp_sales_debitnote_charges`.`taxAmount`),0) from `comp_sales_debitnote_charges` where (`comp_sales_debitnote_charges`.`debitNoteID` = `a`.`ID`)) + (select ifnull(sum(`comp_sales_debitnote_detail`.`taxAmount`),0) from `comp_sales_debitnote_detail` where (`comp_sales_debitnote_detail`.`debitnoteID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`comp_sales_debitnote_charges`.`amountIncludedTax`),0) from `comp_sales_debitnote_charges` where (`comp_sales_debitnote_charges`.`debitNoteID` = `a`.`ID`)) + (select ifnull(sum(`comp_sales_debitnote_detail`.`amountIncludedTax`),0) from `comp_sales_debitnote_detail` where (`comp_sales_debitnote_detail`.`debitnoteID` = `a`.`ID`))) AS `totalAmount` from `comp_sales_debitnote` `a` group by `a`.`ID`;

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

--
-- Structure for view `view_total_comp_sales_invoice`
--
DROP TABLE IF EXISTS `view_total_comp_sales_invoice`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_total_comp_sales_invoice` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`comp_sales_invoice_charges`.`taxAmount`),0) from `comp_sales_invoice_charges` where (`comp_sales_invoice_charges`.`invoiceID` = `a`.`ID`)) + (select ifnull(sum(`comp_sales_invoice_detail`.`taxAmount`),0) from `comp_sales_invoice_detail` where (`comp_sales_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`comp_sales_invoice_charges`.`amountIncludedTax`),0) from `comp_sales_invoice_charges` where (`comp_sales_invoice_charges`.`invoiceID` = `a`.`ID`)) + (select ifnull(sum(`comp_sales_invoice_detail`.`amountIncludedTax`),0) from `comp_sales_invoice_detail` where (`comp_sales_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `totalAmount` from `comp_sales_invoice` `a` group by `a`.`ID`;

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

--
-- Structure for view `view_total_comp_supplier_creditnote`
--
DROP TABLE IF EXISTS `view_total_comp_supplier_creditnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_total_comp_supplier_creditnote` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`comp_supplier_creditnote_charges`.`taxAmount`),0) from `comp_supplier_creditnote_charges` where (`comp_supplier_creditnote_charges`.`creditNoteID` = `a`.`ID`)) + (select ifnull(sum(`comp_supplier_creditnote_detail`.`taxAmount`),0) from `comp_supplier_creditnote_detail` where (`comp_supplier_creditnote_detail`.`creditnoteID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`comp_supplier_creditnote_charges`.`amountIncludedTax`),0) from `comp_supplier_creditnote_charges` where (`comp_supplier_creditnote_charges`.`creditNoteID` = `a`.`ID`)) + (select ifnull(sum(`comp_supplier_creditnote_detail`.`amountIncludedTax`),0) from `comp_supplier_creditnote_detail` where (`comp_supplier_creditnote_detail`.`creditnoteID` = `a`.`ID`))) AS `totalAmount` from `comp_supplier_creditnote_detail` `a` group by `a`.`ID`;

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

--
-- Structure for view `view_total_comp_supplier_debitnote`
--
DROP TABLE IF EXISTS `view_total_comp_supplier_debitnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_total_comp_supplier_debitnote` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`comp_supplier_debitnote_charges`.`taxAmount`),0) from `comp_supplier_debitnote_charges` where (`comp_supplier_debitnote_charges`.`debitNoteID` = `a`.`ID`)) + (select ifnull(sum(`comp_supplier_debitnote_detail`.`taxAmount`),0) from `comp_supplier_debitnote_detail` where (`comp_supplier_debitnote_detail`.`debitnoteID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`comp_supplier_debitnote_charges`.`amountIncludedTax`),0) from `comp_supplier_debitnote_charges` where (`comp_supplier_debitnote_charges`.`debitNoteID` = `a`.`ID`)) + (select ifnull(sum(`comp_supplier_debitnote_detail`.`amountIncludedTax`),0) from `comp_supplier_debitnote_detail` where (`comp_supplier_debitnote_detail`.`debitnoteID` = `a`.`ID`))) AS `totalAmount` from `comp_supplier_debitnote` `a` group by `a`.`ID`;

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

--
-- Structure for view `view_total_comp_supplier_invoice`
--
DROP TABLE IF EXISTS `view_total_comp_supplier_invoice`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_total_comp_supplier_invoice` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`comp_supplier_invoice_charges`.`taxAmount`),0) from `comp_supplier_invoice_charges` where (`comp_supplier_invoice_charges`.`invoiceID` = `a`.`ID`)) + (select ifnull(sum(`comp_supplier_invoice_detail`.`taxAmount`),0) from `comp_supplier_invoice_detail` where (`comp_supplier_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`comp_supplier_invoice_charges`.`amountIncludedTax`),0) from `comp_supplier_invoice_charges` where (`comp_supplier_invoice_charges`.`invoiceID` = `a`.`ID`)) + (select ifnull(sum(`comp_supplier_invoice_detail`.`amountIncludedTax`),0) from `comp_supplier_invoice_detail` where (`comp_supplier_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `totalAmount` from `comp_supplier_invoice` `a` group by `a`.`ID`;

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

--
-- Structure for view `view_unpost_ap_creditnote`
--
DROP TABLE IF EXISTS `view_unpost_ap_creditnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_ap_creditnote` AS select `e`.`supplierName` AS `supplier`,`a`.`ID` AS `creditnoteID`,`a`.`projectID` AS `projectID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`c`.`supplierInvoiceNo` AS `supplierInvoiceNo`,`b`.`itemID` AS `itemID`,concat('[',`d`.`itemCode`,']',`b`.`itemname`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,(select `item`.`inventoriesID` from `item` where (`item`.`ID` = `b`.`itemID`)) AS `drAccountID`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` from (((`comp_supplier_creditnote` `a` join (`comp_supplier_creditnote_detail` `b` left join `item` `d` on((`b`.`itemID` = `d`.`ID`)))) join `comp_supplier_invoice` `c`) join `supplier` `e`) where ((`a`.`ID` = `b`.`creditnoteID`) and (`a`.`supplierInvoiceID` = `c`.`ID`) and (`e`.`ID` = `a`.`supplierID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_ap_debitnote`
--
DROP TABLE IF EXISTS `view_unpost_ap_debitnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_ap_debitnote` AS select `a`.`projectID` AS `projectID`,`e`.`supplierName` AS `supplier`,`a`.`ID` AS `debitnoteID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`c`.`supplierInvoiceNo` AS `supplierInvoiceNo`,`b`.`itemID` AS `itemID`,concat('[',`d`.`itemCode`,']',`b`.`itemname`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drAccountID`,(select `item`.`inventoriesID` from `item` where (`item`.`ID` = `b`.`itemID`)) AS `crAccountID`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drTaxAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID` from (((`comp_supplier_debitnote` `a` join (`comp_supplier_debitnote_detail` `b` left join `item` `d` on((`b`.`itemID` = `d`.`ID`)))) join `comp_supplier_invoice` `c`) join `supplier` `e`) where ((`a`.`ID` = `b`.`debitnoteID`) and (`a`.`supplierID` = `e`.`ID`) and (`a`.`supplierInvoiceID` = `c`.`ID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_ap_invoice`
--
DROP TABLE IF EXISTS `view_unpost_ap_invoice`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_ap_invoice` AS select `a`.`projectID` AS `projectID`,`d`.`supplierName` AS `supplier`,`a`.`ID` AS `invoiceID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`b`.`lineNo` AS `lineNo`,`b`.`itemID` AS `itemID`,concat('[',`c`.`itemCode`,']',`b`.`itemname`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'unInvoiceAP'))) AS `drAccountID`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` from (((`comp_supplier_invoice` `a` left join `comp_supplier_invoice_detail` `b` on((`a`.`ID` = `b`.`invoiceID`))) left join `item` `c` on((`b`.`itemID` = `c`.`ID`))) join `supplier` `d` on((`d`.`ID` = `a`.`supplierID`))) where ((`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) union select `a`.`projectID` AS `projectID`,`d`.`supplierName` AS `supplier`,`a`.`ID` AS `invoiceID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,(`b`.`lineNo` + (select ifnull(max(`comp_supplier_invoice_detail`.`lineNo`),0) from `comp_supplier_invoice_detail` where (`comp_supplier_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `lineNo`,NULL AS `itemID`,`b`.`itemname` AS `itemname`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,`b`.`accountNoID` AS `drAccountID`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` from ((`comp_supplier_invoice` `a` left join `comp_supplier_invoice_charges` `b` on((`a`.`ID` = `b`.`invoiceID`))) left join `supplier` `d` on((`d`.`ID` = `a`.`supplierID`))) where ((`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_ar_creditnote`
--
DROP TABLE IF EXISTS `view_unpost_ar_creditnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_ar_creditnote` AS select `a`.`projectID` AS `projectID`,`a`.`salesInvoiceID` AS `salesinvoiceID`,`a`.`ID` AS `creditnoteID`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,concat(`c`.`formNo`,'[',convert(lpad(`c`.`ID`,8,'0') using latin1),']') AS `salesInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemID` AS `itemID`,concat('[',`d`.`itemCode`,']',`b`.`itemname`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drAccountID`,(select `item`.`inventoriesID` from `item` where (`item`.`ID` = `b`.`itemID`)) AS `crAccountID`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drTaxAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID` from (((((`comp_sales_creditnote` `a` left join `comp_sales_creditnote_detail` `b` on((`a`.`ID` = `b`.`creditnoteID`))) left join `item` `d` on((`b`.`itemID` = `d`.`ID`))) left join `comp_sales_invoice` `c` on((`c`.`ID` = `a`.`salesInvoiceID`))) join `customer` `f`) join `comp_control_acct_setup` `e`) where ((`a`.`ID` = `b`.`creditnoteID`) and (`a`.`customerID` = `f`.`ID`) and (`e`.`companyID` = `a`.`companyID`) and (`e`.`accountCode` = 'rcvacct') and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_ar_debitnote`
--
DROP TABLE IF EXISTS `view_unpost_ar_debitnote`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_ar_debitnote` AS select `a`.`projectID` AS `projectID`,`f`.`customerName` AS `customer`,`a`.`salesInvoiceID` AS `salesinvoiceID`,`a`.`ID` AS `debitnoteID`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,concat(`c`.`formNo`,'[',convert(lpad(`c`.`ID`,8,'0') using latin1),']') AS `salesInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemID` AS `itemID`,concat('[',`d`.`itemCode`,']',`b`.`itemname`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drAccountID`,(select `item`.`inventoriesID` from `item` where (`item`.`ID` = `b`.`itemID`)) AS `crAccountID`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drTaxAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID` from (((((`comp_sales_debitnote` `a` join `comp_sales_debitnote_detail` `b` on((`a`.`ID` = `b`.`debitnoteID`))) left join `item` `d` on((`b`.`itemID` = `d`.`ID`))) left join `comp_sales_invoice` `c` on((`c`.`ID` = `a`.`salesInvoiceID`))) join `customer` `f`) join `comp_control_acct_setup` `e`) where ((`a`.`ID` = `b`.`debitnoteID`) and (`a`.`customerID` = `f`.`ID`) and (`e`.`companyID` = `a`.`companyID`) and (`e`.`accountCode` = 'rcvacct') and (`a`.`ID` = `b`.`debitnoteID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) union select `a`.`projectID` AS `projectID`,`f`.`customerName` AS `customer`,`a`.`salesInvoiceID` AS `salesinvoiceID`,`a`.`ID` AS `debitnoteID`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`ID`,8,'0') using latin1),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,concat(`c`.`formNo`,'[',convert(lpad(`c`.`ID`,8,'0') using latin1),']') AS `salesInvoiceNo`,`b`.`lineNo` AS `lineNo`,NULL AS `itemID`,`b`.`chargeDescription` AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'freightcharges'))) AS `crAccountID`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drTaxAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID` from ((((`comp_sales_debitnote` `a` left join `comp_sales_invoice` `c` on((`c`.`ID` = `a`.`salesInvoiceID`))) join `customer` `f`) join `comp_control_acct_setup` `e`) join `comp_sales_debitnote_charges` `b`) where ((`a`.`ID` = `b`.`debitNoteID`) and (`a`.`customerID` = `f`.`ID`) and (`e`.`companyID` = `a`.`companyID`) and (`e`.`accountCode` = 'rcvacct') and (`a`.`ID` = `b`.`debitNoteID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_ar_invoice`
--
DROP TABLE IF EXISTS `view_unpost_ar_invoice`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_ar_invoice` AS select `a`.`projectID` AS `projectID`,`e`.`customerName` AS `customer`,`a`.`ID` AS `invoiceID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,`b`.`itemID` AS `itemID`,concat('[',`c`.`itemCode`,']',`c`.`description`) AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,(select `item`.`inventoriesID` from `item` where (`item`.`ID` = `b`.`itemID`)) AS `drAccountID`,(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `crAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `crTaxAccountID` from ((`comp_sales_invoice` `a` join (`comp_sales_invoice_detail` `b` left join `item` `c` on((`b`.`itemID` = `c`.`ID`)))) join `customer` `e`) where ((`a`.`ID` = `b`.`invoiceID`) and (`a`.`customerID` = `e`.`ID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) union select `a`.`projectID` AS `projectID`,`e`.`customerName` AS `customer`,`a`.`ID` AS `invoiceID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,NULL AS `NULL`,`b`.`chargeDescription` AS `chargedescription`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,`b`.`accountNoID` AS `drAccountID`,(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `crAccountID`,(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `crTaxAccountID` from ((`comp_sales_invoice` `a` join `comp_sales_invoice_charges` `b`) join `customer` `e`) where ((`a`.`ID` = `b`.`invoiceID`) and (`e`.`ID` = `a`.`customerID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) union select `a`.`projectID` AS `projectID`,`e`.`customerName` AS `customer`,`a`.`ID` AS `invoiceID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,0 AS `0`,'commission' AS `itemName`,((`a`.`totalamount` * `b`.`commisionRate`) / 100) AS `a.totalamount * b.commisionRate`,0 AS `0`,0 AS `0`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'commissionexp'))) AS `drAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'commissionpay'))) AS `crAccountID`,NULL AS `drTaxAccountID`,NULL AS `crTaxAccountID` from ((`comp_sales_invoice` `a` join `comp_sales_agent` `b`) join `customer` `e`) where ((`a`.`companyID` = `b`.`companyID`) and (`a`.`agentID` = `b`.`ID`) and (`a`.`customerID` = `e`.`ID`) and (`a`.`agentID` > 0) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) union select `a`.`projectID` AS `projectID`,`e`.`customerName` AS `customer`,`a`.`ID` AS `invoiceID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,0 AS `0`,'wakaf' AS `itemName`,`b`.`amountExcludedTax` AS `amountExcludedtax`,0 AS `0`,0 AS `0`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'wakafExpense'))) AS `drAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'wakafAcrual'))) AS `crAccountID`,NULL AS `drTaxAccountID`,NULL AS `crTaxAccountID` from ((((`comp_sales_invoice` `a` join `comp_sales_invoice_detail` `b`) join `wakaf_acrual` `c`) join `item` `d`) join `customer` `e`) where ((`a`.`ID` = `b`.`invoiceID`) and (`a`.`customerID` = `e`.`ID`) and (`a`.`companyID` = `c`.`companyID`) and (`b`.`itemID` = `d`.`ID`) and (`d`.`incomeID` = `c`.`salesAccountID`) and (`a`.`salesInvoiceDate` between `c`.`from` and `c`.`to`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_ar_payment`
--
DROP TABLE IF EXISTS `view_unpost_ar_payment`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_ar_payment` AS select `a`.`projectID` AS `projectID`,`a`.`ID` AS `paymentID`,concat(`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID`,`a`.`referenceNo` AS `referenceNo`,`b`.`ID` AS `paymentDetailID`,concat(`c`.`formNo`,'[',convert(lpad(`c`.`ID`,8,'0') using latin1),']') AS `salesInvoiceNo`,(`b`.`amountApplied` - `b`.`discount`) AS `amtPaid_amtAppl`,`b`.`discount` AS `discount_bankCharges`,(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `drAccountID`,NULL AS `crAccountID`,(select `customer`.`linkedAPAccountID` from `customer` where (`customer`.`ID` = `a`.`customerID`)) AS `drDiscountAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'saledisacct'))) AS `crDiscountAccountID` from ((`comp_receive_payment` `a` join `comp_receive_payment_detail` `b`) join `comp_sales_invoice` `c`) where ((`a`.`ID` = `b`.`receivePaymentID`) and (`c`.`ID` = `b`.`invoiceID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_do`
--
DROP TABLE IF EXISTS `view_unpost_do`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.1.174` SQL SECURITY DEFINER VIEW `view_unpost_do` AS select `a`.`ID` AS `deliveryOrderID`,`a`.`formNo` AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID` from `comp_delivery_order` `a` where (`a`.`updateAble` = 1);

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

--
-- Structure for view `view_unpost_other_payment`
--
DROP TABLE IF EXISTS `view_unpost_other_payment`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_other_payment` AS select `a`.`projectID` AS `projectID`,`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`,`b`.`ID` AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemdescription` AS `itemdescription`,`b`.`amountIncludedTax` AS `amtPaid_amtAppl`,NULL AS `discount_bankCharges`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payAcct'))) AS `drAccountID`,`a`.`accountID` AS `crAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payAcct'))) AS `drDiscountAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payDisc'))) AS `crDiscountAccountID` from (`comp_payment_others` `a` join `comp_payment_others_with_item` `b`) where ((`a`.`ID` = `b`.`otherPaymentID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) union select `a`.`projectID` AS `projectID`,`a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`ID`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,NULL AS `supplierID`,NULL AS `purchaseInvoiceID`,`b`.`ID` AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemdescription` AS `itemdescription`,`b`.`amountIncludedTax` AS `amtPaid_amtAppl`,NULL AS `discount_bankCharges`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payAcct'))) AS `drAccountID`,`a`.`accountID` AS `crAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payAcct'))) AS `drDiscountAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payDisc'))) AS `crDiscountAccountID` from (`comp_payment_others` `a` join `comp_payment_others_without_item` `b`) where ((`a`.`ID` = `b`.`otherPaymentID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_payment`
--
DROP TABLE IF EXISTS `view_unpost_payment`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_payment` AS select `a`.`projectID` AS `projectID`,`a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,NULL AS `purchaseInvoiceID`,NULL AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`a`.`amountPaid` AS `amtPaid_amtAppl`,`a`.`bankCharges` AS `discount_bankCharges`,NULL AS `drAccountID`,`a`.`accountID` AS `crAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'bankchargesAccount'))) AS `drDiscountAccountID`,NULL AS `crDiscountAccountID` from `comp_payment` `a` where ((`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) union select `a`.`projectID` AS `projectID`,`a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`ID`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,`b`.`purchaseInvoiceID` AS `purchaseInvoiceID`,`b`.`ID` AS `paymentDetailID`,`c`.`supplierInvoiceNo` AS `supplierInvoiceNo`,(`b`.`amountApplied` - `b`.`discount`) AS `amtPaid_amtAppl`,`b`.`discount` AS `discount_bankCharges`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drAccountID`,NULL AS `crAccountID`,(select `supplier`.`linkedAPAccountID` from `supplier` where (`supplier`.`ID` = `a`.`supplierID`)) AS `drDiscountAccountID`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'payDisc'))) AS `crDiscountAccountID` from ((`comp_payment` `a` join `comp_payment_detail` `b`) join `comp_supplier_invoice` `c`) where ((`a`.`ID` = `b`.`paymentID`) and (`c`.`ID` = `b`.`purchaseInvoiceID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0));

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

--
-- Structure for view `view_unpost_receive_item`
--
DROP TABLE IF EXISTS `view_unpost_receive_item`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_receive_item` AS select `a`.`ID` AS `receiveID`,`a`.`companyID` AS `companyID`,`a`.`locationID` AS `locationID`,`a`.`deliveryDate` AS `transactionDate`,`a`.`supplierID` AS `supplierID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemID` AS `itemID`,concat('[',`c`.`itemCode`,']',`c`.`name`) AS `itemName`,`a`.`exchangeRate` AS `exchangeRate`,`b`.`unitPrice` AS `unitPrice`,`b`.`quantityReceived` AS `quantityReceived`,`b`.`umID` AS `umID`,`b`.`amount` AS `amount`,(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'unInvoiceAP'))) AS `drAccountID`,(select `item`.`inventoriesID` from `item` where (`item`.`ID` = `b`.`itemID`)) AS `crAccountID` from ((`comp_receive_item` `a` join `comp_receive_item_detail` `b`) join `item` `c`) where ((`a`.`updateAble` = 1) and (`a`.`ID` = `b`.`receiveID`) and (`b`.`itemID` = `c`.`ID`));

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

--
-- Structure for view `view_wakaf_accrual`
--
DROP TABLE IF EXISTS `view_wakaf_accrual`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_wakaf_accrual` AS select `b`.`companyID` AS `companyID`,`b`.`acctID` AS `acctID`,sum(`b`.`total`) AS `totalAmount` from (`company` `a` join `comp_acc_det` `b`) where ((`a`.`ID` = `b`.`companyID`) and `b`.`acctID` in (select distinct `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where (`comp_control_acct_setup`.`accountCode` = 'wakafAcrual'))) group by `b`.`companyID`,`b`.`acctID`;

DELIMITER $$
--
-- Events
--
CREATE DEFINER=`root`@`localhost` EVENT `countSupplierBalance` ON SCHEDULE EVERY 1 MONTH STARTS '2014-06-25 16:43:47' ON COMPLETION PRESERVE ENABLE DO insert into comp_supplier_closing (companyID, supplierID, yearMonth,openBalance,totalDr,totalCr,balance)
	select c.companyID, c.ID as supplierID, date_format(now(),"%Y%m") as yearMonth, 
	coalesce(b.openBalance,0) as openBalance, coalesce(sum(a.dr),0) as totalDr, 
	coalesce(sum(a.cr),0) as totalCr, 
	coalesce(b.openBalance,0) - coalesce(sum(a.dr),0) - coalesce(sum(a.cr),0) as balance
	from (supplier c left outer join view_supplier_statement a on c.ID = a.supplierID 
	and date_format(a.transDate,"%Y%m") = date_format(now(),"%Y%m")) 
	left outer join comp_supplier_closing b on a.supplierID = b.supplierID
	and a.companyID = b.companyID 
	and b.yearMonth = date_format(a.transDate - interval 1 month,"%Y%m")
	
	group by c.id, a.companyID$$

CREATE DEFINER=`root`@`localhost` EVENT `countSupplierBalanceDaily` ON SCHEDULE EVERY 1 DAY STARTS '2014-06-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert into comp_supplier_closing_daily (companyID, supplierID, transDate,openBalance,totalDr,totalCr,balance)
	select c.companyID, c.ID as supplierID,  date_format(now(),"%Y%m%d") as transDate, 
	coalesce(b.openBalance,0) as openBanlance, coalesce(sum(a.dr),0) as totalDr, 
	coalesce(sum(a.cr),0) as totalCr, 
	coalesce(b.openBalance,0) - coalesce(sum(a.dr),0) - coalesce(sum(a.cr),0) as balance 
	from (supplier c left outer join view_supplier_statement a on c.ID = a.supplierID 
		and date_format(a.transDate,"%Y%m%d") = date_format(now(),"%Y%m%d")) 
		left outer join comp_supplier_closing_daily b on a.supplierID = b.supplierID and a.companyID = b.companyID 
		and b.transDate = a.transDate - interval 1 day 
	group by c.id, a.companyID$$

DELIMITER ;
