-- --------------------------------------------------------
-- Server OS:                    Win32
-- HeidiSQL Version:             8.3.0.4820
-- --------------------------------------------------------


-- Dumping database structure for iaccountdb
CREATE DATABASE IF NOT EXISTS `iaccountdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `iaccountdb`;


-- Dumping structure for view iaccountdb.view_account_balance
DROP VIEW IF EXISTS `view_account_balance`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_account_balance` (
	`companyID` INT(11) NULL,
	`acctID` INT(11) NULL,
	`acctName` VARCHAR(140) NOT NULL COLLATE 'latin1_swedish_ci',
	`year` INT(11) NULL,
	`period` VARCHAR(2) NULL COLLATE 'latin1_swedish_ci',
	`closing` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_ar_inventory
DROP VIEW IF EXISTS `view_ar_inventory`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_ar_inventory` (
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`salesinvoiceDate` DATE NOT NULL,
	`totalReceivable` DOUBLE NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_ar_open_position
DROP VIEW IF EXISTS `view_ar_open_position`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_ar_open_position` (
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL,
	`customerName` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`phoneNumber1` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`email` VARCHAR(32) NOT NULL COLLATE 'latin1_swedish_ci',
	`due120` DOUBLE NULL,
	`due90` DOUBLE NULL,
	`due60` DOUBLE NULL,
	`due30` DOUBLE NULL,
	`due0` DOUBLE NULL,
	`pastDue30` DOUBLE NULL,
	`pastDue60` DOUBLE NULL,
	`pastDue90` DOUBLE NULL,
	`pastDue120` DOUBLE NULL,
	`pastDue` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_balance_sheet
DROP VIEW IF EXISTS `view_balance_sheet`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_balance_sheet` (
	`acctCode` CHAR(9) NOT NULL COLLATE 'latin1_swedish_ci',
	`acctName` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`acctGroupID` INT(11) NOT NULL,
	`currentyeardebit` DOUBLE NULL,
	`currentyearcredit` DOUBLE NULL,
	`lastyeardebit` DOUBLE NULL,
	`lastyearcredit` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_customer_aging
DROP VIEW IF EXISTS `view_customer_aging`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_customer_aging` (
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL,
	`customerName` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`phoneNumber1` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`email` VARCHAR(32) NOT NULL COLLATE 'latin1_swedish_ci',
	`current` DOUBLE NULL,
	`month1` DOUBLE NULL,
	`month2` DOUBLE NULL,
	`month3` DOUBLE NULL,
	`month4` DOUBLE NULL,
	`month5` DOUBLE NULL,
	`month6` DOUBLE NULL,
	`month7` DOUBLE NULL,
	`month8` DOUBLE NULL,
	`month9` DOUBLE NULL,
	`month10` DOUBLE NULL,
	`month11` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_customer_statement
DROP VIEW IF EXISTS `view_customer_statement`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_customer_statement` (
	`ID` INT(11) NOT NULL,
	`parent` BIGINT(20) NOT NULL,
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL,
	`transType` VARCHAR(10) NOT NULL COLLATE 'utf8mb4_general_ci',
	`transDate` DATE NOT NULL,
	`refNo` VARCHAR(32) NULL COLLATE 'latin1_swedish_ci',
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`dr` DOUBLE NULL,
	`cr` DOUBLE NULL,
	`seq` BIGINT(20) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_gaf
DROP VIEW IF EXISTS `view_gaf`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_gaf` (
	`companyID` INT(11) NOT NULL,
	`col1` VARCHAR(1) NOT NULL COLLATE 'utf8_general_ci',
	`col2` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`col3` VARCHAR(32) NOT NULL COLLATE 'latin1_swedish_ci',
	`col4` DATE NOT NULL,
	`col5` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`col6` VARCHAR(25) NOT NULL COLLATE 'utf8_general_ci',
	`col7` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
	`col8` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
	`col9` DOUBLE NOT NULL,
	`col10` VARCHAR(22) NULL COLLATE 'latin1_swedish_ci',
	`col11` VARCHAR(45) NULL COLLATE 'utf8_general_ci',
	`col12` VARCHAR(5) NULL COLLATE 'utf8_general_ci',
	`col13` DOUBLE NOT NULL,
	`col14` DOUBLE NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_gst_report
DROP VIEW IF EXISTS `view_gst_report`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_gst_report` (
	`companyid` INT(11) NOT NULL,
	`gstRegisteredDate` DATE NULL,
	`taxablePeriod` SMALLINT(6) NOT NULL,
	`financialYear` INT(4) NOT NULL,
	`startDate` DATE NOT NULL,
	`endDate` DATE NOT NULL,
	`period` SMALLINT(2) NOT NULL,
	`A1` VARCHAR(16) NOT NULL COLLATE 'latin1_swedish_ci',
	`A2` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`B5a` DOUBLE NULL,
	`B5b` DOUBLE NULL,
	`B6a` DOUBLE NULL,
	`B6b` DOUBLE NULL,
	`B7` DOUBLE NULL,
	`B8` DOUBLE NULL,
	`C10` DOUBLE NULL,
	`C11` DOUBLE NULL,
	`C12` DOUBLE NULL,
	`C13` DOUBLE NULL,
	`C14` DOUBLE NULL,
	`C15` DOUBLE NULL,
	`C16` DOUBLE NULL,
	`C17` DOUBLE NULL,
	`month6` DOUBLE NULL,
	`C18` INT(1) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_gst_tax
DROP VIEW IF EXISTS `view_gst_tax`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_gst_tax` (
	`companyID` INT(11) NOT NULL,
	`taxClass` INT(11) NULL,
	`taxClassName` VARCHAR(128) NULL COLLATE 'latin1_swedish_ci',
	`taxGroup` INT(11) NULL,
	`taxGroupName` VARCHAR(128) NULL COLLATE 'latin1_swedish_ci',
	`taxType` INT(11) NULL,
	`taxTypeName` VARCHAR(128) NULL COLLATE 'latin1_swedish_ci',
	`transType` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_general_ci',
	`ref` VARCHAR(32) NOT NULL COLLATE 'latin1_swedish_ci',
	`transDate` DATE NOT NULL,
	`customerID` INT(11) NOT NULL,
	`customerName` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`itemName` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedTax` DOUBLE NOT NULL,
	`taxRate` DOUBLE NOT NULL,
	`taxAmount` DOUBLE NOT NULL,
	`amountIncludedTax` DOUBLE NOT NULL,
	`taxID` INT(11) NOT NULL,
	`gst` VARCHAR(10) NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_inventorylist_subtotal
DROP VIEW IF EXISTS `view_inventorylist_subtotal`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_inventorylist_subtotal` (
	`companyid` INT(11) NOT NULL,
	`quantity` DOUBLE NULL,
	`locationid` INT(11) NOT NULL,
	`itemID` INT(11) NOT NULL,
	`itemName` VARCHAR(98) NOT NULL COLLATE 'latin1_swedish_ci',
	`category` VARCHAR(64) NOT NULL COLLATE 'latin1_swedish_ci',
	`description` TEXT NULL COLLATE 'latin1_swedish_ci',
	`stockValue` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_inventory_statement
DROP VIEW IF EXISTS `view_inventory_statement`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_inventory_statement` (
	`companyID` INT(11) NOT NULL,
	`transactionDate` DATE NOT NULL,
	`itemID` INT(11) NULL,
	`name` VARCHAR(64) NULL COLLATE 'latin1_swedish_ci',
	`supplierCustomer` VARCHAR(128) NULL COLLATE 'utf8_general_ci',
	`transactionCode` BIGINT(20) NOT NULL,
	`transactionType` VARCHAR(15) NOT NULL COLLATE 'utf8mb4_general_ci',
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`locationID` INT(11) NOT NULL,
	`location` VARCHAR(100) NULL COLLATE 'utf8_general_ci',
	`unitOfMeasure` VARCHAR(50) NULL COLLATE 'utf8_general_ci',
	`quantityIn` DOUBLE NULL,
	`quantityOut` DOUBLE NULL,
	`updateable` INT(11) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_item_details
DROP VIEW IF EXISTS `view_item_details`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_item_details` (
	`companyid` INT(11) NOT NULL,
	`itemid` INT(11) NOT NULL,
	`itemName` VARCHAR(98) NOT NULL COLLATE 'latin1_swedish_ci',
	`description` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
	`locationid` INT(11) NOT NULL,
	`onHandQty` DOUBLE NULL,
	`umID` INT(11) NOT NULL,
	`acctID` INT(11) NOT NULL,
	`location` VARCHAR(20) NULL COLLATE 'utf8_general_ci',
	`unitOfMeasure` VARCHAR(10) NULL COLLATE 'utf8_general_ci'
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_open_ar_invoice
DROP VIEW IF EXISTS `view_open_ar_invoice`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_open_ar_invoice` (
	`companyID` INT(11) NOT NULL,
	`salesInvoiceDate` DATE NOT NULL,
	`salesYear` VARCHAR(4) NULL COLLATE 'utf8_general_ci',
	`salesMonth` VARCHAR(2) NULL COLLATE 'utf8_general_ci',
	`slsMonth` VARCHAR(32) NULL COLLATE 'utf8_general_ci',
	`totalAmount` DOUBLE NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_cash_sales
DROP VIEW IF EXISTS `view_serial_comp_cash_sales`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_cash_sales` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_delivery_order
DROP VIEW IF EXISTS `view_serial_comp_delivery_order`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_delivery_order` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_payment
DROP VIEW IF EXISTS `view_serial_comp_payment`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_payment` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_payment_others
DROP VIEW IF EXISTS `view_serial_comp_payment_others`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_payment_others` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_purchase_order
DROP VIEW IF EXISTS `view_serial_comp_purchase_order`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_purchase_order` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_quotation
DROP VIEW IF EXISTS `view_serial_comp_quotation`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_quotation` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_receive_item
DROP VIEW IF EXISTS `view_serial_comp_receive_item`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_receive_item` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_receive_payment
DROP VIEW IF EXISTS `view_serial_comp_receive_payment`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_receive_payment` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_receive_payment_others
DROP VIEW IF EXISTS `view_serial_comp_receive_payment_others`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_receive_payment_others` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_sales_creditnote
DROP VIEW IF EXISTS `view_serial_comp_sales_creditnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_sales_creditnote` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_sales_debitnote
DROP VIEW IF EXISTS `view_serial_comp_sales_debitnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_sales_debitnote` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_sales_invoice
DROP VIEW IF EXISTS `view_serial_comp_sales_invoice`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_sales_invoice` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_sales_order
DROP VIEW IF EXISTS `view_serial_comp_sales_order`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_sales_order` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(17) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_supplier_creditnote
DROP VIEW IF EXISTS `view_serial_comp_supplier_creditnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_supplier_creditnote` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_supplier_debitnote
DROP VIEW IF EXISTS `view_serial_comp_supplier_debitnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_supplier_debitnote` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_serial_comp_supplier_invoice
DROP VIEW IF EXISTS `view_serial_comp_supplier_invoice`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_serial_comp_supplier_invoice` (
	`companyID` INT(11) NOT NULL,
	`nextSerialNo` BIGINT(12) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_supplier_aging
DROP VIEW IF EXISTS `view_supplier_aging`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_supplier_aging` (
	`companyID` INT(11) NOT NULL,
	`supplierID` INT(11) NOT NULL,
	`current` DOUBLE NULL,
	`month1` DOUBLE NULL,
	`month2` DOUBLE NULL,
	`month3` DOUBLE NULL,
	`month4` DOUBLE NULL,
	`month5` DOUBLE NULL,
	`month6` DOUBLE NULL,
	`month7` DOUBLE NULL,
	`month8` DOUBLE NULL,
	`month9` DOUBLE NULL,
	`month10` DOUBLE NULL,
	`month11` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_supplier_statement
DROP VIEW IF EXISTS `view_supplier_statement`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_supplier_statement` (
	`ID` INT(11) NOT NULL,
	`parent` INT(11) NULL,
	`companyID` INT(11) NOT NULL,
	`supplierID` INT(11) NOT NULL,
	`transType` VARCHAR(10) NOT NULL COLLATE 'utf8mb4_general_ci',
	`transDate` DATE NOT NULL,
	`refNo` VARCHAR(32) NULL COLLATE 'latin1_swedish_ci',
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`dr` DOUBLE NULL,
	`cr` DOUBLE NULL,
	`seq` BIGINT(20) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_total_comp_sales_creditnote
DROP VIEW IF EXISTS `view_total_comp_sales_creditnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_total_comp_sales_creditnote` (
	`ID` INT(11) NOT NULL,
	`totalTaxAmount` DOUBLE NULL,
	`totalAmount` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_total_comp_sales_debitnote
DROP VIEW IF EXISTS `view_total_comp_sales_debitnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_total_comp_sales_debitnote` (
	`ID` INT(11) NOT NULL,
	`totalTaxAmount` DOUBLE NULL,
	`totalAmount` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_total_comp_sales_invoice
DROP VIEW IF EXISTS `view_total_comp_sales_invoice`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_total_comp_sales_invoice` (
	`ID` INT(11) NOT NULL,
	`totalTaxAmount` DOUBLE NULL,
	`totalAmount` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_total_comp_supplier_creditnote
DROP VIEW IF EXISTS `view_total_comp_supplier_creditnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_total_comp_supplier_creditnote` (
	`ID` INT(11) NOT NULL,
	`totalTaxAmount` DOUBLE NULL,
	`totalAmount` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_total_comp_supplier_debitnote
DROP VIEW IF EXISTS `view_total_comp_supplier_debitnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_total_comp_supplier_debitnote` (
	`ID` INT(11) NOT NULL,
	`totalTaxAmount` DOUBLE NULL,
	`totalAmount` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_total_comp_supplier_invoice
DROP VIEW IF EXISTS `view_total_comp_supplier_invoice`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_total_comp_supplier_invoice` (
	`ID` INT(11) NOT NULL,
	`totalTaxAmount` DOUBLE NULL,
	`totalAmount` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_ap_creditnote
DROP VIEW IF EXISTS `view_unpost_ap_creditnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_ap_creditnote` (
	`supplier` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`creditnoteID` INT(11) NOT NULL,
	`projectID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`supplierID` INT(11) NOT NULL,
	`supplierInvoiceNo` VARCHAR(32) NOT NULL COLLATE 'latin1_swedish_ci',
	`itemID` INT(11) NOT NULL,
	`itemName` VARCHAR(98) NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NOT NULL,
	`taxid` INT(11) NOT NULL,
	`taxamount` DOUBLE NOT NULL,
	`drAccountID` BIGINT(11) NULL,
	`crAccountID` BIGINT(11) NULL,
	`drTaxAccountID` BIGINT(11) NULL,
	`crTaxAccountID` BIGINT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_ap_debitnote
DROP VIEW IF EXISTS `view_unpost_ap_debitnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_ap_debitnote` (
	`projectID` INT(11) NOT NULL,
	`supplier` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`debitnoteID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`supplierID` INT(11) NOT NULL,
	`supplierInvoiceNo` VARCHAR(32) NOT NULL COLLATE 'latin1_swedish_ci',
	`itemID` INT(11) NOT NULL,
	`itemName` VARCHAR(98) NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NOT NULL,
	`taxid` INT(11) NOT NULL,
	`taxamount` DOUBLE NOT NULL,
	`drAccountID` BIGINT(11) NULL,
	`crAccountID` BIGINT(11) NULL,
	`drTaxAccountID` BIGINT(11) NULL,
	`crTaxAccountID` BIGINT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_ap_invoice
DROP VIEW IF EXISTS `view_unpost_ap_invoice`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_ap_invoice` (
	`supplier` VARCHAR(128) NULL COLLATE 'latin1_swedish_ci',
	`invoiceID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`supplierID` INT(11) NOT NULL,
	`lineNo` BIGINT(20) NULL,
	`itemID` INT(11) NULL,
	`itemName` VARCHAR(98) NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NULL,
	`taxid` INT(11) NULL,
	`taxamount` DOUBLE NULL,
	`drAccountID` INT(11) NULL,
	`crAccountID` INT(11) NULL,
	`drTaxAccountID` INT(11) NULL,
	`crTaxAccountID` INT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_ar_creditnote
DROP VIEW IF EXISTS `view_unpost_ar_creditnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_ar_creditnote` (
	`projectID` INT(11) NOT NULL,
	`salesinvoiceID` INT(11) NOT NULL,
	`creditnoteID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL,
	`salesInvoiceNo` VARCHAR(42) NULL COLLATE 'latin1_swedish_ci',
	`lineNo` INT(11) NULL,
	`itemID` INT(11) NULL,
	`itemName` VARCHAR(98) NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NULL,
	`taxid` INT(11) NULL,
	`taxamount` DOUBLE NULL,
	`drAccountID` VARCHAR(11) NOT NULL COLLATE 'latin1_swedish_ci',
	`crAccountID` BIGINT(11) NULL,
	`drTaxAccountID` VARCHAR(11) NOT NULL COLLATE 'latin1_swedish_ci',
	`crTaxAccountID` BIGINT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_ar_debitnote
DROP VIEW IF EXISTS `view_unpost_ar_debitnote`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_ar_debitnote` (
	`projectID` INT(11) NOT NULL,
	`customer` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`salesinvoiceID` INT(11) NOT NULL,
	`debitnoteID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL,
	`salesInvoiceNo` VARCHAR(42) NULL COLLATE 'latin1_swedish_ci',
	`lineNo` INT(11) NOT NULL,
	`itemID` INT(11) NULL,
	`itemName` TEXT NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NOT NULL,
	`taxid` INT(11) NOT NULL,
	`taxamount` DOUBLE NOT NULL,
	`drAccountID` VARCHAR(11) NOT NULL COLLATE 'latin1_swedish_ci',
	`crAccountID` INT(11) NULL,
	`drTaxAccountID` VARCHAR(11) NOT NULL COLLATE 'latin1_swedish_ci',
	`crTaxAccountID` INT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_ar_invoice
DROP VIEW IF EXISTS `view_unpost_ar_invoice`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_ar_invoice` (
	`projectID` INT(11) NOT NULL,
	`customer` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`invoiceID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL,
	`itemID` INT(11) NULL,
	`itemName` MEDIUMTEXT NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NULL,
	`taxid` BIGINT(20) NOT NULL,
	`taxamount` DOUBLE NOT NULL,
	`drAccountID` VARCHAR(11) NULL COLLATE 'latin1_swedish_ci',
	`crAccountID` VARCHAR(11) NULL COLLATE 'latin1_swedish_ci',
	`drTaxAccountID` VARCHAR(11) NULL COLLATE 'latin1_swedish_ci',
	`crTaxAccountID` INT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_ar_payment
DROP VIEW IF EXISTS `view_unpost_ar_payment`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_ar_payment` (
	`paymentID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL,
	`salesInvoiceID` INT(11) NULL,
	`paymentDetailID` INT(11) NULL,
	`supplierInvoiceNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`amountApplied` DOUBLE NOT NULL,
	`discount_bankCharges` DOUBLE NOT NULL,
	`drAccountID` INT(11) NULL,
	`crAccountID` VARCHAR(11) NULL COLLATE 'latin1_swedish_ci',
	`drDiscountAccountID` INT(11) NULL,
	`crDiscountAccountID` BINARY(0) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_cash_purchase
DROP VIEW IF EXISTS `view_unpost_cash_purchase`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_cash_purchase` (
	`paymentID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`supplierID` BINARY(0) NULL,
	`purchaseInvoiceID` BINARY(0) NULL,
	`paymentDetailID` INT(11) NOT NULL,
	`supplierInvoiceNo` BINARY(0) NULL,
	`lineNo` INT(11) NOT NULL,
	`itemdescription` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
	`amtPaid_amtAppl` DOUBLE NOT NULL,
	`discount_bankCharges` DOUBLE NOT NULL,
	`drAccountID` BIGINT(11) NULL,
	`crAccountID` BIGINT(11) NULL,
	`drDiscountAccountID` BIGINT(11) NULL,
	`crDiscountAccountID` BINARY(0) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_cash_sale
DROP VIEW IF EXISTS `view_unpost_cash_sale`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_cash_sale` (
	`projectID` INT(11) NOT NULL,
	`customer` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`invoiceID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`itemID` INT(11) NULL,
	`itemName` MEDIUMTEXT NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NOT NULL,
	`taxid` INT(11) NOT NULL,
	`taxamount` DOUBLE NOT NULL,
	`drAccountID` INT(11) NULL,
	`crAccountID` INT(11) NULL,
	`drTaxAccountID` BINARY(0) NULL,
	`crTaxAccountID` INT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_do
DROP VIEW IF EXISTS `view_unpost_do`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_do` (
	`deliveryOrderID` INT(11) NOT NULL,
	`formNo` VARCHAR(32) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`customerID` INT(11) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_other_payment
DROP VIEW IF EXISTS `view_unpost_other_payment`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_other_payment` (
	`othersPaymentID` INT(11) NOT NULL,
	`supplier` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
	`invoiceID` INT(1) NOT NULL,
	`projectID` INT(11) NOT NULL,
	`lineNo` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`NULL` BINARY(0) NULL,
	`description` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NOT NULL,
	`taxid` INT(11) NOT NULL,
	`taxamount` DOUBLE NOT NULL,
	`drAccountID` INT(11) NOT NULL,
	`crAccountID` BIGINT(11) NULL,
	`drTaxAccountID` BIGINT(11) NULL,
	`crTaxAccountID` BIGINT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_other_receive
DROP VIEW IF EXISTS `view_unpost_other_receive`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_other_receive` (
	`projectID` INT(11) NOT NULL,
	`receiveFrom` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`invoiceID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`NULL` BINARY(0) NULL,
	`description` TEXT NOT NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NOT NULL,
	`taxid` INT(11) NOT NULL,
	`taxamount` DOUBLE NOT NULL,
	`drAccountID` BIGINT(11) NULL,
	`crAccountID` INT(11) NOT NULL,
	`drTaxAccountID` BIGINT(11) NULL,
	`crTaxAccountID` BIGINT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_payment
DROP VIEW IF EXISTS `view_unpost_payment`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_payment` (
	`paymentID` INT(11) NOT NULL,
	`supplierName` VARCHAR(128) NOT NULL COLLATE 'latin1_swedish_ci',
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`companyID` INT(11) NOT NULL,
	`supplierID` INT(11) NOT NULL,
	`purchaseInvoiceID` INT(11) NULL,
	`lineNo` BIGINT(11) NOT NULL,
	`itemID` INT(11) NULL,
	`itemName` VARCHAR(32) NOT NULL COLLATE 'latin1_swedish_ci',
	`amountExcludedtax` DOUBLE NOT NULL,
	`taxid` BINARY(0) NULL,
	`taxamount` DOUBLE NOT NULL,
	`drAccountID` INT(11) NULL,
	`crAccountID` INT(11) NULL,
	`drDiscountAccountID` INT(11) NULL,
	`crDiscountAccountID` INT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_unpost_receive_item
DROP VIEW IF EXISTS `view_unpost_receive_item`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_unpost_receive_item` (
	`receiveID` INT(11) NOT NULL,
	`companyID` INT(11) NOT NULL,
	`locationID` INT(11) NOT NULL,
	`transactionDate` DATE NOT NULL,
	`supplierID` INT(11) NOT NULL,
	`formNo` VARCHAR(42) NOT NULL COLLATE 'latin1_swedish_ci',
	`lineNo` INT(11) NOT NULL,
	`itemID` INT(11) NOT NULL,
	`itemName` VARCHAR(98) NOT NULL COLLATE 'latin1_swedish_ci',
	`exchangeRate` FLOAT NOT NULL,
	`unitPrice` DOUBLE NOT NULL,
	`quantityReceived` DOUBLE NOT NULL,
	`umID` INT(11) NOT NULL,
	`amount` DOUBLE NOT NULL,
	`drAccountID` BIGINT(11) NULL,
	`crAccountID` BIGINT(11) NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_wakaf_accrual
DROP VIEW IF EXISTS `view_wakaf_accrual`;
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_wakaf_accrual` (
	`companyID` INT(11) NULL,
	`acctID` INT(11) NULL,
	`totalAmount` DOUBLE NULL
) ENGINE=MyISAM;


-- Dumping structure for view iaccountdb.view_account_balance
DROP VIEW IF EXISTS `view_account_balance`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_account_balance` AS select a.companyID, a.acctID, concat(left(b.acctCode,4),"-",mid(b.acctCode,4,3),"-",right(b.acctCode,2)," ",b.acctName) as acctName, a.year, a.period, sum(a.total) as closing
from comp_acc_det a, comp_chart_of_acct b
where b.id = a.acctID
group by companyID, acctID, acctName, year, period ;


-- Dumping structure for view iaccountdb.view_ar_inventory
DROP VIEW IF EXISTS `view_ar_inventory`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_ar_inventory` AS select companyID, customerID, 
concat(formNo,'[',lpad(id,8,'0'),']') as formNo, salesinvoiceDate,
totalReceivable
from comp_sales_invoice
where totalReceivable > 0 ;


-- Dumping structure for view iaccountdb.view_ar_open_position
DROP VIEW IF EXISTS `view_ar_open_position`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_ar_open_position` AS select a.companyID, a.id as customerID, a.customerName, a.phoneNumber1, a.email, 
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,inv.salesinvoiceDate,now()) - terms.dueDays <= -90) as due120,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > -90 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <=-60 ) as due90,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > -60  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <= -30 ) as due60,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > -30  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <=-5 ) as due30,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > -5  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <=5 ) as due0,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 5  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <= 30 ) as pastDue30,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 30  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <= 60 ) as pastDue60,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 60  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <= 90 ) as pastDue90,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 90  
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays <=120 ) as pastDue120,
(select sum(inv.totalReceivable)  from comp_sales_invoice inv, terms  where inv.termsID = terms.ID and inv.companyID=a.companyID and inv.customerID = a.ID 
and TIMESTAMPDIFF(DAY,salesinvoiceDate,now()) - terms.dueDays > 120) as pastDue
from customer a ;


-- Dumping structure for view iaccountdb.view_balance_sheet
DROP VIEW IF EXISTS `view_balance_sheet`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.1.192` VIEW `view_balance_sheet` AS SELECT `a`.`acctCode`, `a`.`acctName`, `a`.`acctGroupID`, 
sum(b.amountdr) as currentyeardebit, 
sum(b.amountcr) as currentyearcredit,
sum(c.amountdr) as lastyeardebit,
sum(c.amountcr) as lastyearcredit
FROM comp_chart_of_acct a
LEFT OUTER JOIN `comp_acc_det` b ON `b`.`acctID` = `a`.`ID` and b.year = 2014 And b.period <= 9 
LEFT OUTER JOIN `comp_acc_det` c ON `c`.`acctID` = `a`.`ID` and c.year <2014 
WHERE `a`.`companyID` = '110' 
GROUP BY `a`.`acctCode` ;


-- Dumping structure for view iaccountdb.view_customer_aging
DROP VIEW IF EXISTS `view_customer_aging`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_customer_aging` AS select a.companyID, a.id as customerID, a.customerName, a.phoneNumber1, a.email, 
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=0) as current,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=1) as month1,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=2) as month2,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=3) as month3,
(select sum(totalReceivable)  from comp_sales_invoice  where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=4) as month4,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=5) as month5,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=6) as month6,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=7) as month7,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=8) as month8,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=9) as month9,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())=10) as month10,
(select sum(totalReceivable)  from comp_sales_invoice where companyID=a.companyID and customerID = a.ID and TIMESTAMPDIFF(MONTH,salesinvoiceDate,now())>10) as month11
from customer a ;


-- Dumping structure for view iaccountdb.view_customer_statement
DROP VIEW IF EXISTS `view_customer_statement`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_customer_statement` AS select ID, ID as parent, companyID, customerID,
'Invoice' as transType,
salesinvoiceDate as transDate,customerpoNo as refNo, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo,
totalAmount as dr, null as cr, 1 as seq
from comp_sales_invoice
union
select ID, salesInvoiceID as parent, companyID, customerID, 
'DebitNote' as transType,
debitnotedate  as transDate, null as refno, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo, 
null as dr, totalamount as cr, 2 as seq
from comp_sales_debitnote
union
select ID, salesInvoiceID as parent, companyID, customerID, 
'CreditNote' as transType,
creditnotedate as transDate, null as refno, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo, 
totalamount as dr, null as cr, 3 as seq
from comp_sales_creditnote
union
select b.ID, 0 as parent, b.companyID, b.customerID, 
'Payment' as transType,
b.receivepaymentDate as transDate, b.referenceNo as refNo, 
concat(concat(concat(b.formNo,'['),lpad(b.serialNo,8,'0')),']') as formNo,  
null as dr, b.totalReceived as cr, 4 as seq
from comp_receive_payment b ;


-- Dumping structure for view iaccountdb.view_gaf
DROP VIEW IF EXISTS `view_gaf`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_gaf` AS select a.companyID, 
'P' as col1,
a.supplierName as col2,
a.supplierCompanyNo as col3,
b.invoiceDate as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_supplier_invoice b,
comp_supplier_invoice_detail c,	
_ref_currency d,
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, 
'P' as col1,
a.supplierName as col2,
a.supplierCompanyNo as col3,
b.invoiceDate as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_supplier_invoice b,
comp_supplier_invoice_charges c,	
_ref_currency d,
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, 
'P' as col1,
a.supplierName as col2,
a.supplierCompanyNo as col3,
b.debitNoteDate as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_supplier_debitnote b,
comp_supplier_debitnote_detail c,	
_ref_currency d,
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, 
'P' as col1,
a.supplierName as col2,
a.supplierCompanyNo as col3,
b.creditNoteDate as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_supplier_creditnote b,
comp_supplier_creditnote_detail c,	
_ref_currency d,
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, 
'S' as col1,
a.customerName as col2,
a.customerCompanyNo as col3,
b.salesInvoiceDate as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_sales_invoice b,
comp_sales_invoice_detail c,	
_ref_currency d,
tax_master e,
_ref_country f,
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, 
'S' as col1,
a.customerName as col2,
a.customerCompanyNo as col3,
b.salesInvoiceDate as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_sales_invoice b,
comp_sales_invoice_charges c,	
_ref_currency d,
tax_master e,
_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, 
'S' as col1,
a.customerName as col2,
a.customerCompanyNo as col3,
b.debitNoteDate as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_sales_debitnote b,
comp_sales_debitnote_detail c,	
_ref_currency d,
tax_master e,
_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, 
'S' as col1,
a.customerName as col2,
a.customerCompanyNo as col3,
b.creditNoteDate as col4,
concat(b.formNo,'[',lpad(b.id,8,'0'),']') 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,
comp_sales_creditnote b,
comp_sales_creditnote_detail c,	
_ref_currency d,
tax_master e,
_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 ;


-- Dumping structure for view iaccountdb.view_gst_report
DROP VIEW IF EXISTS `view_gst_report`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_gst_report` AS select `a`.`companyID` AS `companyid`,`b`.`gstRegisteredDate` AS `gstRegisteredDate`,`b`.`taxablePeriod` AS `taxablePeriod`,`a`.`financialYear` AS `financialYear`,`a`.`startDate` AS `startDate`,`a`.`endDate` AS `endDate`,`a`.`period` AS `period`,`b`.`gstRegisteredNo` AS `A1`,`b`.`companyName` AS `A2`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B5a`,(select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B5b`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B6a`,(select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('AJP','TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `B6b`,if(((select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) > (select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),((select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) - (select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),0) AS `B7`,if(((select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) > (select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),((select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('TX','TX-CG','IM','IMS','TX-E43','TX-RE')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) - (select sum(`view_gst_tax`.`taxAmount`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('SR','DS','AJS','IMS')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`)))),0) AS `B8`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'ZRL') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C10`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'ZRE') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C11`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` in ('ES','')) and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C12`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'RS') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C13`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'IS') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C14`,(select sum((`view_gst_tax`.`amountExcludedTax` * 0.06)) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'IS') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C15`,(select sum(`view_gst_tax`.`amountExcludedTax`) from `iaccountdb`.`view_gst_tax` where ((`view_gst_tax`.`companyID` = `a`.`companyID`) and (`view_gst_tax`.`gst` = 'TX-CG') and (`view_gst_tax`.`transDate` between `a`.`startDate` and `a`.`endDate`))) AS `C16`,
(select sum(valueBadDebt) from comp_sales_bad_debt where companyID = a.companyID and transDate between a.startDate and a.endDate and updateAble = 0) as C17,

(select sum(`iaccountdb`.`comp_sales_invoice`.`totalReceivable`) from `iaccountdb`.`comp_sales_invoice` where ((`iaccountdb`.`comp_sales_invoice`.`companyID` = `a`.`companyID`) and (`iaccountdb`.`comp_sales_invoice`.`customerID` = `a`.`ID`) and (timestampdiff(MONTH,`iaccountdb`.`comp_sales_invoice`.`salesInvoiceDate`,now()) >= 6))) AS `month6`,
0 AS `C18` 
from (`iaccountdb`.`comp_financial_year` `a` join `iaccountdb`.`company` `b`) where ((`b`.`ID` = `a`.`companyID`) and (`a`.`is_delete` = 0)) ;


-- Dumping structure for view iaccountdb.view_gst_tax
DROP VIEW IF EXISTS `view_gst_tax`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_gst_tax` AS select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Invoice' as transType, a.formNo as ref, a.salesInvoiceDate as transDate, 
a.customerID, d.customerName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_invoice a , 
comp_sales_invoice_detail b ,
customer d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.invoiceID
and a.customerid = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Invoice' as transType, a.formNo as ref, a.salesInvoiceDate as transDate, 
a.customerID, d.customerName, b.chargeDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_invoice a , 
comp_sales_invoice_charges b ,
customer d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.invoiceID
and a.customerid = d.id
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Credit Note' as transType, a.formNo as ref, a.creditNoteDate as transDate, 
a.customerID, d.customerName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_creditnote a , 
comp_sales_creditnote_detail b ,
customer d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.creditNoteID
and a.customerid = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Credit Note' as transType, a.formNo as ref, a.creditNoteDate as transDate, 
a.customerID, d.customerName, b.chargeDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_creditnote a , 
comp_sales_creditnote_charges b ,
customer d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.creditNoteID
and a.customerid = d.id
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Debit note' as transType, a.formNo as ref, a.debitNoteDate as transDate, 
a.customerID, d.customerName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_debitnote a , 
comp_sales_debitnote_detail b ,
customer d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.debitNoteID
and a.customerid = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Sales Debit Note' as transType, a.formNo as ref, a.debitNoteDate as transDate, 
a.customerID, d.customerName, b.chargeDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_sales_debitnote a , 
comp_sales_debitnote_charges b ,
customer d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.debitNoteID
and a.customerid = d.id
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union

#purchase invoice
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Invoice' as transType, a.formNo as ref, a.InvoiceDate as transDate, 
a.supplierID, d.supplierName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_invoice a , 
comp_supplier_invoice_detail b ,
supplier d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.ID = b.invoiceID
and a.supplierID = d.ID
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Invoice' as transType, a.formNo as ref, a.invoiceDate as transDate, 
a.supplierID, d.supplierName, b.chargeDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_invoice a , 
comp_supplier_invoice_charges b ,
supplier d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.ID = b.invoiceID
and a.supplierID = d.ID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Credit Note' as transType, a.formNo as ref, a.creditNoteDate as transDate, 
a.supplierID, d.supplierName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_creditnote a , 
comp_supplier_creditnote_detail b ,
supplier d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.creditNoteID
and a.supplierID = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Credit Note' as transType, a.formNo as ref, a.creditNoteDate as transDate, 
a.supplierID, d.supplierName, b.itemDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_creditnote a , 
comp_supplier_creditnote_charges b ,
supplier d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.creditNoteID
and a.supplierID = d.id
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Debit note' as transType, a.formNo as ref, a.debitNoteDate as transDate, 
a.supplierID, d.supplierName, concat(e.name," / ",e.itemCode) as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_debitnote a , 
comp_supplier_debitnote_detail b ,
supplier d,
item e,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.id = b.debitNoteID
and a.supplierID = d.id
and e.ID = b.itemID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0
union
select a.companyID, f.taxClass, h.description as taxClassName,
f.taxGroup, i.description as taxGroupName,
f.taxType, g.description as taxTypeName,
'Purchase Debit Note' as transType, a.formNo as ref, a.debitNoteDate as transDate, 
a.supplierID, d.supplierName, b.itemDescription as itemName, b.amountExcludedTax, 
b.taxRate, b.taxAmount, b.amountIncludedTax, b.taxID, f.code as gst 
from comp_supplier_debitnote a , 
comp_supplier_debitnote_charges b ,
supplier d,
tax_master f,
tax_type g,
tax_class h,
tax_group i
where a.ID = b.debitNoteID
and a.supplierID = d.ID
and f.ID = b.taxID
and f.taxType = g.ID
and f.taxClass = h.ID
and f.taxGroup = i.ID
and f.taxClass = 1
and b.taxID > 0 ;


-- Dumping structure for view iaccountdb.view_inventorylist_subtotal
DROP VIEW IF EXISTS `view_inventorylist_subtotal`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_inventorylist_subtotal` AS select `a`.`companyID` AS `companyid`,sum(`b`.`balanceItem`) as quantity, `a`.`locationID` AS `locationid`,`b`.`itemID` AS `itemID`,concat('[',`c`.`itemCode`,']',`c`.`name`) AS `itemName`,`d`.`name` AS `category`,`b`.`description` AS `description`,sum((`b`.`balanceItem` * `b`.`unitPrice`)) AS `stockValue` from `iaccountdb`.`comp_receive_item` `a` join `iaccountdb`.`comp_receive_item_detail` `b` join `iaccountdb`.`item` `c` join `iaccountdb`.`comp_item_category` `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` ;


-- Dumping structure for view iaccountdb.view_inventory_statement
DROP VIEW IF EXISTS `view_inventory_statement`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 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 (((`iaccountdb`.`comp_receive_item` `a` left join `iaccountdb`.`comp_receive_item_detail` `b` on((`a`.`ID` = `b`.`receiveID`))) left join `iaccountdb`.`item` `c` on((`b`.`itemID` = `c`.`ID`))) left join `iaccountdb`.`item_details` `f` on((`c`.`ID` = `f`.`itemID`))) join `iaccountdb`.`tbl_location` `d` join `iaccountdb`.`_sys_um` `e` join `iaccountdb`.`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 (((((`iaccountdb`.`item_adjustment` `a` left join `iaccountdb`.`item_adjustment_detail` `b` on((`a`.`ID` = `b`.`adjustmentID`))) left join `iaccountdb`.`item` `c` on((`b`.`itemID` = `c`.`ID`))) left join `iaccountdb`.`item_details` `f` on((`c`.`ID` = `f`.`itemID`))) left join `iaccountdb`.`tbl_location` `d` on((`d`.`fldid` = `a`.`locationID`))) left join `iaccountdb`.`_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 (((`iaccountdb`.`comp_delivery_order` `a` left join `iaccountdb`.`comp_delivery_detail` `b` on((`a`.`ID` = `b`.`deliveryID`))) left join `iaccountdb`.`item` `c` on((`b`.`itemID` = `c`.`ID`))) left join `iaccountdb`.`item_details` `f` on((`c`.`ID` = `f`.`itemID`))) join `iaccountdb`.`tbl_location` `d` join `iaccountdb`.`_sys_um` `e` join `iaccountdb`.`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 ((`iaccountdb`.`comp_location_transfer` `a` left join `iaccountdb`.`comp_location_transfer_detail` `b` on((`a`.`ID` = `b`.`locationTransferID`))) left join `iaccountdb`.`item` `c` on((`b`.`itemID` = `c`.`ID`))) join `iaccountdb`.`tbl_location` `d` join `iaccountdb`.`_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 ((`iaccountdb`.`comp_location_transfer` `a` left join `iaccountdb`.`comp_location_transfer_detail` `b` on((`a`.`ID` = `b`.`locationTransferID`))) left join `iaccountdb`.`item` `c` on((`b`.`itemID` = `c`.`ID`))) join `iaccountdb`.`tbl_location` `d` join `iaccountdb`.`_sys_um` `e` where ((`a`.`fromLocationID` = `d`.`fldid`) and (`e`.`fldid` = `c`.`unitOfMeasureID`)) ;


-- Dumping structure for view iaccountdb.view_item_details
DROP VIEW IF EXISTS `view_item_details`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_item_details` AS select b.companyid, a.itemid, concat('[',c.itemCode,']',c.name) as itemName, c.description,
b.locationid, sum(a.balanceItem) as onHandQty, 
a.umID, c.inventoriesID as acctID,
d.code as location, e.code as unitOfMeasure
from comp_receive_item_detail a,
comp_receive_item b, 
item c,
tbl_location d,
_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 companyid, itemid, itemName, description, locationid, umID, acctID, location, unitOfMeasure ;


-- Dumping structure for view iaccountdb.view_open_ar_invoice
DROP VIEW IF EXISTS `view_open_ar_invoice`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_open_ar_invoice` AS select `iaccountdb`.`comp_sales_invoice`.`companyID` AS `companyID`,`iaccountdb`.`comp_sales_invoice`.`salesInvoiceDate` AS `salesInvoiceDate`,date_format(`iaccountdb`.`comp_sales_invoice`.`salesInvoiceDate`,'%Y') AS `salesYear`,date_format(`iaccountdb`.`comp_sales_invoice`.`salesInvoiceDate`,'%m') AS `salesMonth`,date_format(`iaccountdb`.`comp_sales_invoice`.`salesInvoiceDate`,'%b') AS `slsMonth`,(`iaccountdb`.`comp_sales_invoice`.`totalReceivable` * if((`iaccountdb`.`comp_sales_invoice`.`exchangeRate` = 0),1,`iaccountdb`.`comp_sales_invoice`.`exchangeRate`)) AS `totalAmount` from `iaccountdb`.`comp_sales_invoice` where (`iaccountdb`.`comp_sales_invoice`.`totalReceivable` <> 0) ;


-- Dumping structure for view iaccountdb.view_serial_comp_cash_sales
DROP VIEW IF EXISTS `view_serial_comp_cash_sales`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_cash_sales` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `iaccountdb`.`comp_cash_sales` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 121))),1)) AS `nextSerialNo` from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_delivery_order
DROP VIEW IF EXISTS `view_serial_comp_delivery_order`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_delivery_order` AS select `c`.`ID` AS `companyID`,
ifnull(((select max(`t`.`serialNo`) from `iaccountdb`.`comp_delivery_order` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 49))),1)) AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_payment
DROP VIEW IF EXISTS `view_serial_comp_payment`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_payment` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_payment` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 44))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_payment_others
DROP VIEW IF EXISTS `view_serial_comp_payment_others`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_payment_others` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_payment_others` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 55))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_purchase_order
DROP VIEW IF EXISTS `view_serial_comp_purchase_order`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_purchase_order` AS select `c`.`ID` AS `companyID`,ifnull((((select max(`po`.`serialNo`) from `iaccountdb`.`comp_purchase_order` `po` where (`c`.`ID` = `po`.`companyID`)) + 1)),ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 40))),1)) AS `nextSerialNo` from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_quotation
DROP VIEW IF EXISTS `view_serial_comp_quotation`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_quotation` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_quotation` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 47))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_receive_item
DROP VIEW IF EXISTS `view_serial_comp_receive_item`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_receive_item` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_receive_item` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 54))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_receive_payment
DROP VIEW IF EXISTS `view_serial_comp_receive_payment`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_receive_payment` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_receive_payment` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 53))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_receive_payment_others
DROP VIEW IF EXISTS `view_serial_comp_receive_payment_others`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_receive_payment_others` AS select `c`.`ID` AS `companyID`,
ifnull(((select max(`t`.`serialNo`) from `iaccountdb`.`comp_receive_payment_others` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 56))),1)) AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_sales_creditnote
DROP VIEW IF EXISTS `view_serial_comp_sales_creditnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_sales_creditnote` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_sales_creditnote` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 52))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_sales_debitnote
DROP VIEW IF EXISTS `view_serial_comp_sales_debitnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_sales_debitnote` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_sales_debitnote` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 51))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_sales_invoice
DROP VIEW IF EXISTS `view_serial_comp_sales_invoice`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_sales_invoice` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) 
                                        from `iaccountdb`.`comp_sales_invoice` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 50))),1)) AS `nextSerialNo` 
                                        from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_sales_order
DROP VIEW IF EXISTS `view_serial_comp_sales_order`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_sales_order` AS select `c`.`ID` AS `companyID`,
ifnull(((select max(`t`.`serialNo`) from `iaccountdb`.`comp_sales_order` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 48))),1)) AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_supplier_creditnote
DROP VIEW IF EXISTS `view_serial_comp_supplier_creditnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_supplier_creditnote` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_supplier_creditnote` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 43))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_supplier_debitnote
DROP VIEW IF EXISTS `view_serial_comp_supplier_debitnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_supplier_debitnote` AS select `c`.`ID` AS `companyID`,
	ifnull(((select max(`t`.`serialNo`) 
		from `iaccountdb`.`comp_supplier_debitnote` `t` 
		where (`c`.`ID` = `t`.`companyID`)) + 1),
			ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` 
				where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 42))),1))
 AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_serial_comp_supplier_invoice
DROP VIEW IF EXISTS `view_serial_comp_supplier_invoice`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_serial_comp_supplier_invoice` AS select `c`.`ID` AS `companyID`,
ifnull(((select max(`t`.`serialNo`) from `iaccountdb`.`comp_supplier_invoice` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `iaccountdb`.`comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 41))),1)) AS `nextSerialNo` 
from `iaccountdb`.`company` `c` ;


-- Dumping structure for view iaccountdb.view_supplier_aging
DROP VIEW IF EXISTS `view_supplier_aging`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_supplier_aging` AS select a.companyID, a.id as supplierID, (select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=0 and updateable=0) as current,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=1 and updateable=0) as month1,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=2 and updateable=0) as month2,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=3 and updateable=0) as month3,
(select sum(totalPayable)  from comp_supplier_invoice  where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=4 and updateable=0) as month4,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=5 and updateable=0) as month5,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=6 and updateable=0) as month6,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=7 and updateable=0) as month7,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=8 and updateable=0) as month8,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=9 and updateable=0) as month9,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())=10 and updateable=0) as month10,
(select sum(totalPayable)  from comp_supplier_invoice where companyID=a.companyID and supplierID = a.ID and TIMESTAMPDIFF(MONTH,invoiceDate,now())>10 and updateable=0) as month11
from supplier a ;


-- Dumping structure for view iaccountdb.view_supplier_statement
DROP VIEW IF EXISTS `view_supplier_statement`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_supplier_statement` AS select ID, ID as parent, companyID, supplierID,
'Invoice' as transType,
invoiceDate as transDate,supplierInvoiceNo as refNo, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo,
totalAmount as dr, null as cr, 1 as seq
from comp_supplier_invoice
where updateable = 0
union
select ID, supplierInvoiceID as parent, companyID, supplierID, 
'DebitNote' as transType,
debitnotedate  as transDate, null as refno, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo, 
null as dr, totalamount as cr, 2 as seq
from comp_supplier_debitnote
where updateable = 0
union
select ID, supplierInvoiceID as parent, companyID, supplierID, 
'CreditNote' as transType,
creditnotedate as transDate, null as refno, 
concat(concat(concat(formNo,'['),lpad(serialNo,8,'0')),']') as formNo, 
totalamount as dr, null as cr, 3 as seq
from comp_supplier_creditnote
where updateable = 0
union
select b.ID, null as parent, b.companyID, b.supplierID, 
'Payment' as transType,
b.paymentDate as transDate, b.referenceNo as refNo, 
concat(concat(concat(b.formNo,'['),lpad(b.serialNo,8,'0')),']') as formNo,  
null as cr, amountPaid as cr, 4 as seq
from comp_payment b
where b.updateable = 0 ;


-- Dumping structure for view iaccountdb.view_total_comp_sales_creditnote
DROP VIEW IF EXISTS `view_total_comp_sales_creditnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_total_comp_sales_creditnote` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`iaccountdb`.`comp_sales_creditnote_charges`.`taxAmount`),0) from `iaccountdb`.`comp_sales_creditnote_charges` where (`iaccountdb`.`comp_sales_creditnote_charges`.`creditNoteID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_sales_creditnote_detail`.`taxAmount`),0) from `iaccountdb`.`comp_sales_creditnote_detail` where (`iaccountdb`.`comp_sales_creditnote_detail`.`creditnoteID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`iaccountdb`.`comp_sales_creditnote_detail`.`amountIncludedTax`),0) from `iaccountdb`.`comp_sales_creditnote_detail` where (`iaccountdb`.`comp_sales_creditnote_detail`.`creditnoteID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_sales_creditnote_charges`.`amountIncludedTax`),0) from `iaccountdb`.`comp_sales_creditnote_charges` where (`iaccountdb`.`comp_sales_creditnote_charges`.`creditnoteID` = `a`.`ID`))) AS `totalAmount` from `iaccountdb`.`comp_sales_creditnote` `a` group by `a`.`ID` ;


-- Dumping structure for view iaccountdb.view_total_comp_sales_debitnote
DROP VIEW IF EXISTS `view_total_comp_sales_debitnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_total_comp_sales_debitnote` AS select distinct `a`.`ID` AS `ID`,
((select ifnull(sum(`iaccountdb`.`comp_sales_debitnote_charges`.`taxAmount`),0) 
  from `iaccountdb`.`comp_sales_debitnote_charges` where (`iaccountdb`.`comp_sales_debitnote_charges`.`debitNoteID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_sales_debitnote_detail`.`taxAmount`),0) from `iaccountdb`.`comp_sales_debitnote_detail` where (`iaccountdb`.`comp_sales_debitnote_detail`.`debitnoteID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`iaccountdb`.`comp_sales_debitnote_charges`.`amountIncludedTax`),0) from `iaccountdb`.`comp_sales_debitnote_charges` where (`iaccountdb`.`comp_sales_debitnote_charges`.`debitNoteID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_sales_debitnote_detail`.`amountIncludedTax`),0) from `iaccountdb`.`comp_sales_debitnote_detail` where (`iaccountdb`.`comp_sales_debitnote_detail`.`debitNoteID` = `a`.`ID`))) AS `totalAmount` from `iaccountdb`.`comp_sales_debitnote` `a` group by `a`.`ID` ;


-- Dumping structure for view iaccountdb.view_total_comp_sales_invoice
DROP VIEW IF EXISTS `view_total_comp_sales_invoice`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_total_comp_sales_invoice` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`iaccountdb`.`comp_sales_invoice_charges`.`taxAmount`),0) from `iaccountdb`.`comp_sales_invoice_charges` where (`iaccountdb`.`comp_sales_invoice_charges`.`invoiceID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_sales_invoice_detail`.`taxAmount`),0) from `iaccountdb`.`comp_sales_invoice_detail` where (`iaccountdb`.`comp_sales_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`iaccountdb`.`comp_sales_invoice_charges`.`amountIncludedTax`),0) from `iaccountdb`.`comp_sales_invoice_charges` where (`iaccountdb`.`comp_sales_invoice_charges`.`invoiceID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_sales_invoice_detail`.`amountIncludedTax`),0) from `iaccountdb`.`comp_sales_invoice_detail` where (`iaccountdb`.`comp_sales_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `totalAmount` from `iaccountdb`.`comp_sales_invoice` `a` group by `a`.`ID` ;


-- Dumping structure for view iaccountdb.view_total_comp_supplier_creditnote
DROP VIEW IF EXISTS `view_total_comp_supplier_creditnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_total_comp_supplier_creditnote` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`iaccountdb`.`comp_supplier_creditnote_charges`.`taxAmount`),0) from `iaccountdb`.`comp_supplier_creditnote_charges` where (`iaccountdb`.`comp_supplier_creditnote_charges`.`creditNoteID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_supplier_creditnote_detail`.`taxAmount`),0) from `iaccountdb`.`comp_supplier_creditnote_detail` where (`iaccountdb`.`comp_supplier_creditnote_detail`.`creditnoteID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`iaccountdb`.`comp_supplier_creditnote_charges`.`amountIncludedTax`),0) from `iaccountdb`.`comp_supplier_creditnote_charges` where (`iaccountdb`.`comp_supplier_creditnote_charges`.`creditNoteID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_supplier_creditnote_detail`.`amountIncludedTax`),0) from `iaccountdb`.`comp_supplier_creditnote_detail` where (`iaccountdb`.`comp_supplier_creditnote_detail`.`creditnoteID` = `a`.`ID`))) AS `totalAmount` 
from `iaccountdb`.`comp_supplier_creditnote` `a` group by `a`.`ID` ;


-- Dumping structure for view iaccountdb.view_total_comp_supplier_debitnote
DROP VIEW IF EXISTS `view_total_comp_supplier_debitnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_total_comp_supplier_debitnote` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`iaccountdb`.`comp_supplier_debitnote_charges`.`taxAmount`),0) from `iaccountdb`.`comp_supplier_debitnote_charges` where (`iaccountdb`.`comp_supplier_debitnote_charges`.`debitNoteID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_supplier_debitnote_detail`.`taxAmount`),0) from `iaccountdb`.`comp_supplier_debitnote_detail` where (`iaccountdb`.`comp_supplier_debitnote_detail`.`debitnoteID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`iaccountdb`.`comp_supplier_debitnote_charges`.`amountIncludedTax`),0) from `iaccountdb`.`comp_supplier_debitnote_charges` where (`iaccountdb`.`comp_supplier_debitnote_charges`.`debitNoteID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_supplier_debitnote_detail`.`amountIncludedTax`),0) from `iaccountdb`.`comp_supplier_debitnote_detail` where (`iaccountdb`.`comp_supplier_debitnote_detail`.`debitNoteID` = `a`.`ID`))) AS `totalAmount` from `iaccountdb`.`comp_supplier_debitnote` `a` group by `a`.`ID` ;


-- Dumping structure for view iaccountdb.view_total_comp_supplier_invoice
DROP VIEW IF EXISTS `view_total_comp_supplier_invoice`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_total_comp_supplier_invoice` AS select distinct `a`.`ID` AS `ID`,((select ifnull(sum(`iaccountdb`.`comp_supplier_invoice_charges`.`taxAmount`),0) from `iaccountdb`.`comp_supplier_invoice_charges` where (`iaccountdb`.`comp_supplier_invoice_charges`.`invoiceID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_supplier_invoice_detail`.`taxAmount`),0) from `iaccountdb`.`comp_supplier_invoice_detail` where (`iaccountdb`.`comp_supplier_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `totalTaxAmount`,((select ifnull(sum(`iaccountdb`.`comp_supplier_invoice_charges`.`amountIncludedTax`),0) from `iaccountdb`.`comp_supplier_invoice_charges` where (`iaccountdb`.`comp_supplier_invoice_charges`.`invoiceID` = `a`.`ID`)) + (select ifnull(sum(`iaccountdb`.`comp_supplier_invoice_detail`.`amountIncludedTax`),0) from `iaccountdb`.`comp_supplier_invoice_detail` where (`iaccountdb`.`comp_supplier_invoice_detail`.`invoiceID` = `a`.`ID`))) AS `totalAmount` from `iaccountdb`.`comp_supplier_invoice` `a` group by `a`.`ID` ;


-- Dumping structure for view iaccountdb.view_unpost_ap_creditnote
DROP VIEW IF EXISTS `view_unpost_ap_creditnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_ap_creditnote` AS select `e`.`supplierName` AS `supplier`,`a`.`ID` AS `creditnoteID`, a.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 `iaccountdb`.`item`.`inventoriesID` from `iaccountdb`.`item` where (`iaccountdb`.`item`.`ID` = `b`.`itemID`)) AS `drAccountID`,(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` from (((`iaccountdb`.`comp_supplier_creditnote` `a` join (`iaccountdb`.`comp_supplier_creditnote_detail` `b` left join `iaccountdb`.`item` `d` on((`b`.`itemID` = `d`.`ID`)))) join `iaccountdb`.`comp_supplier_invoice` `c`) join `iaccountdb`.`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)) ;


-- Dumping structure for view iaccountdb.view_unpost_ap_debitnote
DROP VIEW IF EXISTS `view_unpost_ap_debitnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_ap_debitnote` AS select a.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 `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `drAccountID`,(select `iaccountdb`.`item`.`inventoriesID` from `iaccountdb`.`item` where (`iaccountdb`.`item`.`ID` = `b`.`itemID`)) AS `crAccountID`,(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `drTaxAccountID`,(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID` from (((`iaccountdb`.`comp_supplier_debitnote` `a` join (`iaccountdb`.`comp_supplier_debitnote_detail` `b` left join `iaccountdb`.`item` `d` on((`b`.`itemID` = `d`.`ID`)))) join `iaccountdb`.`comp_supplier_invoice` `c`) join `iaccountdb`.`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)) ;


-- Dumping structure for view iaccountdb.view_unpost_ap_invoice
DROP VIEW IF EXISTS `view_unpost_ap_invoice`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_ap_invoice` AS select `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 `iaccountdb`.`comp_control_acct_setup`.`accountID` 
	from `iaccountdb`.`comp_control_acct_setup` 
	where ((`iaccountdb`.`comp_control_acct_setup`.`companyID` = `a`.`companyID`) 
		and (`iaccountdb`.`comp_control_acct_setup`.`accountCode` = 'unInvoiceAP'))) AS `drAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,
(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` 
from ((`iaccountdb`.`comp_supplier_invoice` `a` 
left outer join `iaccountdb`.`comp_supplier_invoice_detail` `b` on `a`.`ID` = `b`.`invoiceID`)
      left join `iaccountdb`.`item` `c` on `b`.`itemID` = `c`.`ID`) 
join `iaccountdb`.`supplier` `d` on `d`.`ID` = `a`.`supplierID`
where ((`a`.`updateAble` = 1) and (`a`.`is_delete` = 0) and a.purchaseInvoiceCodeID = 1) 
union 
select `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 inventoriesID from item where id=b.itemID) as drAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as crAccountID,
(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` 
from ((`iaccountdb`.`comp_supplier_invoice` `a` 
left outer join `iaccountdb`.`comp_supplier_invoice_detail` `b` on `a`.`ID` = `b`.`invoiceID`)
      left join `iaccountdb`.`item` `c` on `b`.`itemID` = `c`.`ID`) 
join `iaccountdb`.`supplier` `d` on `d`.`ID` = `a`.`supplierID`
where ((`a`.`updateAble` = 1) and (`a`.`is_delete` = 0) and a.purchaseInvoiceCodeID = 2)
union
select `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(`iaccountdb`.`comp_supplier_invoice_detail`.`lineNo`),0) 
	from `iaccountdb`.`comp_supplier_invoice_detail` 
	where (`iaccountdb`.`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 `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crAccountID`,
(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `iaccountdb`.`supplier`.`linkedAPAccountID` from `iaccountdb`.`supplier` where (`iaccountdb`.`supplier`.`ID` = `a`.`supplierID`)) AS `crTaxAccountID` 
from `iaccountdb`.`comp_supplier_invoice` `a` left outer join `iaccountdb`.`comp_supplier_invoice_charges` `b` on `a`.`ID` = `b`.`invoiceID`
left join `iaccountdb`.`supplier` `d` on `d`.`ID` = `a`.`supplierID`
where ((`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) ;


-- Dumping structure for view iaccountdb.view_unpost_ar_creditnote
DROP VIEW IF EXISTS `view_unpost_ar_creditnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_ar_creditnote` AS select a.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 `iaccountdb`.`item`.`inventoriesID` from `iaccountdb`.`item` where (`iaccountdb`.`item`.`ID` = `b`.`itemID`)) AS `crAccountID`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drTaxAccountID`,(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID` from (((((`iaccountdb`.`comp_sales_creditnote` `a` left join `iaccountdb`.`comp_sales_creditnote_detail` `b` on((`a`.`ID` = `b`.`creditnoteID`))) left join `iaccountdb`.`item` `d` on((`b`.`itemID` = `d`.`ID`))) left join `iaccountdb`.`comp_sales_invoice` `c` on((`c`.`ID` = `a`.`salesInvoiceID`))) join `iaccountdb`.`customer` `f`) join `iaccountdb`.`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)) ;


-- Dumping structure for view iaccountdb.view_unpost_ar_debitnote
DROP VIEW IF EXISTS `view_unpost_ar_debitnote`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_ar_debitnote` AS select a.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 `iaccountdb`.`item`.`inventoriesID` from `iaccountdb`.`item` where (`iaccountdb`.`item`.`ID` = `b`.`itemID`)) AS `crAccountID`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drTaxAccountID`,(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID` from (((((`iaccountdb`.`comp_sales_debitnote` `a` join `iaccountdb`.`comp_sales_debitnote_detail` `b` on((`a`.`ID` = `b`.`debitnoteID`))) left join `iaccountdb`.`item` `d` on((`b`.`itemID` = `d`.`ID`))) left join `iaccountdb`.`comp_sales_invoice` `c` on((`c`.`ID` = `a`.`salesInvoiceID`))) join `iaccountdb`.`customer` `f`) join `iaccountdb`.`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, `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 `iaccountdb`.`comp_control_acct_setup`.`accountID` from `iaccountdb`.`comp_control_acct_setup` where ((`iaccountdb`.`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`iaccountdb`.`comp_control_acct_setup`.`accountCode` = 'freightcharges'))) AS `crAccountID`,ifnull(`f`.`linkedAPAccountID`,`e`.`accountID`) AS `drTaxAccountID`,(select `iaccountdb`.`tax_master`.`taxAccount` from `iaccountdb`.`tax_master` where (`iaccountdb`.`tax_master`.`ID` = `b`.`taxID`)) AS `crTaxAccountID` from ((((`iaccountdb`.`comp_sales_debitnote` `a` left join `iaccountdb`.`comp_sales_invoice` `c` on((`c`.`ID` = `a`.`salesInvoiceID`))) join `iaccountdb`.`customer` `f`) join `iaccountdb`.`comp_control_acct_setup` `e`) join `iaccountdb`.`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)) ;


-- Dumping structure for view iaccountdb.view_unpost_ar_invoice
DROP VIEW IF EXISTS `view_unpost_ar_invoice`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_ar_invoice` AS select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, b.itemID, 
concat("[",c.itemCode,"]",c.description) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
(select linkedAPAccountID from customer where id=a.customerID) as drAccountID,
(select incomeID from item where id=b.itemID) as crAccountID,
(select linkedAPAccountID from customer where id=a.customerID) as drTaxAccountID,
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID
from comp_sales_invoice a,
(comp_sales_invoice_detail b left outer join item c on b.itemID = c.id),
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, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, b.itemID, 
concat("[",c.itemCode,"]",c.description) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
(select cogsID from item where id=b.itemID) as drAccountID,
(select inventoriesID from item where id=b.itemID) as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_sales_invoice a,
(comp_sales_invoice_detail b left outer join item c on b.itemID = c.id),
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, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, null, b.chargedescription, b.amountExcludedtax, b.taxid, b.taxamount, 
b.accountNoID as drAccountID,
(select linkedAPAccountID from customer where id=a.customerID) as crAccountID,
(select linkedAPAccountID from customer where id=a.customerID) as drTaxAccountID,
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID
from comp_sales_invoice a,
comp_sales_invoice_charges b,
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, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, null, 'comission' as itemName, sum(b.amountExcludedtax) * d.commisionRate / 100 as comissionAmount, 0 as taxID, 0 as taxAmount, 
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'commissionexp') as drAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'commissionpay') as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_sales_invoice a,
comp_sales_invoice_detail b ,
comp_sales_agent d,
customer e
where a.companyID = d.companyID
and a.id = b.invoiceid
and a.agentID = d.ID
and a.customerID = e.ID
and a.agentid > 0
and a.updateable = 1
and a.is_delete =0  
group by a.id
union
select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, null, 'wakaf' as itemName, b.amountExcludedtax, 0 as taxID, 0 as taxAmount, 
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'wakafExpense') as drAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'wakafAcrual') as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_sales_invoice a,
comp_sales_invoice_detail b, 
wakaf_acrual c, 
item d,
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 
union
select a.projectID, e.customerName as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID,a.customerID, null, 'cogs' as itemName, sum(d.amount)  as cogsAmount, 0 as taxID, 0 as taxAmount, 
c.cogsID as drAccountID,
c.inventoriesID as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_sales_invoice a,
(comp_sales_invoice_detail b left outer join item c on b.itemID = c.id),
comP_delivery_detail d,
customer e
where a.id = b.invoiceID
and a.id = d.invoiceID
and a.customerID = e.ID
and a.updateable = 1
and a.is_delete =0  
group by a.id, c.cogsID, c.inventoriesID ;


-- Dumping structure for view iaccountdb.view_unpost_ar_payment
DROP VIEW IF EXISTS `view_unpost_ar_payment`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_ar_payment` AS select a.ID as paymentID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.customerID, null as salesInvoiceID,
null as paymentDetailID, concat(a.formNo,'[',lpad(a.serialNo,8,'0'),']') as supplierInvoiceNo, 
a.totalReceived as amountApplied, a.bankCharges as discount_bankCharges, 
(select linkAccountID from comp_bank where id = a.bankID) as drAccountID,
null as crAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'bankchargesAccount') as drDiscountAccountID,
null as crDiscountAccountID
from comp_receive_payment a
where a.updateable = 1
and a.is_delete =0 
union
select a.ID as paymentID, concat(a.formNo,'[',lpad(a.serialNo,8,'0'),']') as formNo ,a.companyID,a.customerID, a.referenceNo,
b.ID as paymentDetailID, concat(c.formNo,'[',lpad(c.serialNo,8,'0'),']') as supplierInvoiceNo,  
b.amountApplied - b.discount as amountApplied, b.discount as discount_bankCharges,
null as drAccountID,
(select linkedAPAccountID from customer where id=a.customerID) as crAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'saledisacct') as drDiscountAccountID,
null as crDiscountAccountID
from comp_receive_payment a,
comp_receive_payment_detail b,
comp_sales_invoice c
where a.id = b.receivePaymentID
and c.id = b.InvoiceID 
and a.updateable = 1
and a.is_delete =0 ;


-- Dumping structure for view iaccountdb.view_unpost_cash_purchase
DROP VIEW IF EXISTS `view_unpost_cash_purchase`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_cash_purchase` AS select a.ID as paymentID, concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,null as supplierID, null as purchaseInvoiceID,
b.ID as paymentDetailID, null as supplierInvoiceNo,  
b.lineNo, b.itemdescription,
b.amountExcludedTax  as amtPaid_amtAppl, b.taxAmount as discount_bankCharges,
(select inventoriesID from item where id=b.itemID) as drAccountID,
(select linkAccountID from comp_bank where id = a.bankID) as crAccountID,
(select taxAccount from tax_master where id=b.taxID) as drDiscountAccountID,
null as crDiscountAccountID
from comp_payment_others a,
comp_payment_others_with_item b 
where a.id = b.otherPaymentID
and a.updateable = 1
and a.is_delete =0 ;


-- Dumping structure for view iaccountdb.view_unpost_cash_sale
DROP VIEW IF EXISTS `view_unpost_cash_sale`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_cash_sale` AS select a.projectID, a.paymentFrom as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID, b.itemID, 
concat("[",c.itemCode,"]",c.description) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
a.bankID as drAccountID,
c.incomeID as crAccountID,
null as drTaxAccountID,
null as crTaxAccountID
from comp_cash_sales a,
(comp_cash_sales_detail b left outer join item c on b.itemID = c.id)
where a.id = b.invoiceid
and a.updateable = 1
and a.is_delete =0 
union 
select a.projectID, a.paymentFrom as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID, b.itemID, 
concat("[",c.itemCode,"]",c.description) as itemName, b.amountExcludedtax, b.taxid, b.taxamount, 
(select cogsID from item where id=b.itemID) as drAccountID,
(select inventoriesID from item where id=b.itemID) as crAccountID,
null as drTaxAccountID,
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID
from comp_cash_sales a,
(comp_cash_sales_detail b left outer join item c on b.itemID = c.id)
where a.id = b.invoiceid
and a.updateable = 1
and a.is_delete =0 
union 
select a.projectID, a.paymentFrom as customer, a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID, null, b.chargedescription, b.amountExcludedtax, b.taxid, b.taxamount, 
(null) as drAccountID,
b.accountNoID as crAccountID,
(null) as drTaxAccountID,
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID
from comp_cash_sales a,
comp_cash_sales_charges b
where a.id = b.cashSaleID
and a.updateable = 1
and a.is_delete =0 ;


-- Dumping structure for view iaccountdb.view_unpost_do
DROP VIEW IF EXISTS `view_unpost_do`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_do` AS select `a`.`ID` AS `deliveryOrderID`,`a`.`formNo` AS `formNo`,`a`.`companyID` AS `companyID`,`a`.`customerID` AS `customerID` from `iaccountdb`.`comp_delivery_order` `a` where (`a`.`updateAble` = 1) ;


-- Dumping structure for view iaccountdb.view_unpost_other_payment
DROP VIEW IF EXISTS `view_unpost_other_payment`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_other_payment` AS select a.ID as othersPaymentID, a.payTo as supplier, 0 as invoiceID, a.projectID, b.lineNo, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID, null, b.itemdescription as description, b.amountExcludedtax, b.taxid, b.taxamount, 
b.accountID as drAccountID,
(select linkAccountID from comp_bank where id = a.bankID) as crAccountID,
(select taxAccount from tax_master where id=b.taxID) as drTaxAccountID,
(select linkAccountID from comp_bank where id = a.bankID) as crTaxAccountID
from comp_payment_others a,
comp_payment_others_without_item b
where a.id = b.otherPaymentID
and a.updateable = 1
and a.is_delete =0 ;


-- Dumping structure for view iaccountdb.view_unpost_other_receive
DROP VIEW IF EXISTS `view_unpost_other_receive`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_other_receive` AS select a.projectID, a.receiveFrom , a.ID as invoiceID, 
concat(concat(concat(a.formNo,'['),lpad(a.serialNo,8,'0')),']') as formNo ,
a.companyID, null, b.description, b.amountExcludedtax, b.taxid, b.taxamount, 
(select linkAccountID from comp_bank where id = a.bankID) as drAccountID,
b.accountNoID as crAccountID,
(select linkAccountID from comp_bank where id = a.bankID) as drTaxAccountID,
(select taxAccount from tax_master where id=b.taxID) as crTaxAccountID
from comp_receive_payment_others a,
comp_receive_payment_others_detail b
where a.id = b.receivePaymentOthersID
and a.updateable = 1
and a.is_delete =0 ;


-- Dumping structure for view iaccountdb.view_unpost_payment
DROP VIEW IF EXISTS `view_unpost_payment`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_unpost_payment` AS select a.ID as paymentID, 
b.supplierName,
concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.supplierID, 
null as purchaseInvoiceID, 0 as lineNo, 
null as itemID, a.referenceNo as itemName, 
a.amountPaid as amountExcludedtax,  null as taxid, a.bankCharges as taxamount , 
null as drAccountID,
(select linkAccountID from comp_bank where id = a.bankID) as crAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'bankchargesAccount') as drDiscountAccountID,
null as crDiscountAccountID
from comp_payment a,
supplier b
where b.id = a.supplierID
and a.updateable = 1
and a.is_delete =0 
union
select a.ID as paymentID,
d.supplierName, 
concat(concat(concat(a.formNo,'['),lpad(a.id,8,'0')),']') as formNo ,a.companyID,a.supplierID, b.purchaseInvoiceID,
b.lineNo, b.ID as itemID, c.supplierInvoiceNo as itemName,  
b.amountApplied - b.discount as amtPaid_amtAppl,  null as taxid, b.discount as taxamount,
(select linkedAPAccountID from supplier where id=a.supplierID) as drAccountID,
null as crAccountID,
(select linkedAPAccountID from supplier where id=a.supplierID) as drDiscountAccountID,
(select accountID from comp_control_acct_setup where companyID = a.companyID and accountCode = 'payDisc') as crDiscountAccountID
from comp_payment a,
comp_payment_detail b,
comp_supplier_invoice c,
supplier d
where a.id = b.paymentID
and d.id = a.supplierID
and c.id = b.purchaseInvoiceID 
and a.updateable = 1
and a.is_delete =0 ;


-- Dumping structure for view iaccountdb.view_unpost_receive_item
DROP VIEW IF EXISTS `view_unpost_receive_item`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 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 `iaccountdb`.`item`.`inventoriesID` from `iaccountdb`.`item` where (`iaccountdb`.`item`.`ID` = `b`.`itemID`)) AS `drAccountID`,(select `iaccountdb`.`comp_control_acct_setup`.`accountID` from `iaccountdb`.`comp_control_acct_setup` where ((`iaccountdb`.`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`iaccountdb`.`comp_control_acct_setup`.`accountCode` = 'unInvoiceAP'))) AS `crAccountID` from `iaccountdb`.`comp_receive_item` `a` join `iaccountdb`.`comp_receive_item_detail` `b` join `iaccountdb`.`item` `c` where ((`a`.`updateAble` = 1) and (`a`.`ID` = `b`.`receiveID`) and (`b`.`itemID` = `c`.`ID`)) ;


-- Dumping structure for view iaccountdb.view_wakaf_accrual
DROP VIEW IF EXISTS `view_wakaf_accrual`;
-- Removing temporary table and create final VIEW structure
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` VIEW `view_wakaf_accrual` AS select `b`.`companyID` AS `companyID`,`b`.`acctID` AS `acctID`,sum(`b`.`total`) AS `totalAmount` from `iaccountdb`.`company` `a` join `iaccountdb`.`comp_acc_det` `b` where ((`a`.`ID` = `b`.`companyID`) and `b`.`acctID` in (select distinct `iaccountdb`.`comp_control_acct_setup`.`accountID` from `iaccountdb`.`comp_control_acct_setup` where (`iaccountdb`.`comp_control_acct_setup`.`accountCode` = 'wakafAcrual'))) group by `b`.`companyID`,`b`.`acctID` ;
