--
-- Table structure for table `emp_accountsection`
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `emp_accountsection` (
  `sectionid` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  PRIMARY KEY (`sectionid`),
  CONSTRAINT `emp_accountsection_ibfk_1` FOREIGN KEY (`sectionid`) REFERENCES `accountsection` (`sectionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

CREATE TABLE IF NOT EXISTS `emp_stockmaster` (
  `stockid` varchar(20) NOT NULL DEFAULT '',
  `height` decimal(20,4) NOT NULL DEFAULT '0.0000',
  `width` decimal(20,4) NOT NULL DEFAULT '0.0000',
  `length` decimal(20,4) NOT NULL DEFAULT '0.0000',
  KEY `stockid` (`stockid`),
  CONSTRAINT `emp_stockmaster_ibfk_1` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_AccountSections.php', `pagesecurity`, 'Defines the sections in the general ledger reports'
FROM `scripts`
WHERE `script` = 'AccountSections.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_GLProfit_Loss.php', `pagesecurity`, 'Shows the profit and loss of the company for the range of periods entered in IAS format'
FROM `scripts`
WHERE `script` = 'GLProfit_Loss.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_GLBalanceSheet.php', `pagesecurity`, 'Shows the balance sheet for the company as at a specified date in IAS format'
FROM `scripts`
WHERE `script` = 'GLBalanceSheet.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_GLExcelReport.php', `pagesecurity`, 'Export GL report in Excel format'
FROM `scripts`
WHERE `script` = 'GLBalanceSheet.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_PaymentVoucher.php', `pagesecurity`, 'Generate and Print Payment Voucher'
FROM `scripts`
WHERE `script` = 'Payments.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_PrintCustTransDOPortrait.php', `pagesecurity`, 'Generate and Print Delivery Order'
FROM `scripts`
WHERE `script` = 'PrintCustTransPortrait.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_InventoryValuation.php', `pagesecurity`, 'Custom Inventory Valuation Report'
FROM `scripts`
WHERE `script` = 'InventoryValuation.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_StockLocatorTransfer.php', `pagesecurity`, 'Warehouse Stock Placement Transfer'
FROM `scripts`
WHERE `script` = 'Stocks.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_StockLocatorReport.php', `pagesecurity`, 'Warehouse Stock Placement Report'
FROM `scripts`
WHERE `script` = 'Stocks.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_StockLocatorMoveReport.php', `pagesecurity`, 'Warehouse Stock Movement Report'
FROM `scripts`
WHERE `script` = 'Stocks.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_StockLocator.php', `pagesecurity`, 'Warehouse Stock Placement Management'
FROM `scripts`
WHERE `script` = 'Stocks.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_Stocks.php', `pagesecurity`, 'Warehouse Stock Placement Transfer'
FROM `scripts`
WHERE `script` = 'Stocks.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'emp_WorkOrderReverse.php', `pagesecurity`, 'Reverse Work Order'
FROM `scripts`
WHERE `script` = 'WorkOrderEntry.php';

INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`)
SELECT 'Z_ImportReorderLevel.php', `pagesecurity`, 'Utility to update stock reorder levels'
FROM `scripts`
WHERE `script` = 'Z_ImportPriceList.php';

--
-- Table structure for table `stock_locators`
--

CREATE TABLE IF NOT EXISTS `stock_locators` (
  `loccode` varchar(5) NOT NULL,
  `rack` varchar(20) NOT NULL DEFAULT '',
  `row` varchar(20) NOT NULL DEFAULT '',
  `bin` varchar(20) NOT NULL DEFAULT '',
  `stockid` varchar(20) DEFAULT NULL,
  `quantity` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`loccode`,`rack`,`row`,`bin`),
  KEY `loccode` (`loccode`),
  KEY `stockid` (`stockid`),
  CONSTRAINT `stock_locators_ibfk_1` FOREIGN KEY (`loccode`) REFERENCES `locations` (`loccode`),
  CONSTRAINT `stock_locators_ibfk_2` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `stock_locators`   
  ADD COLUMN `active` BOOL DEFAULT 1 NOT NULL AFTER `quantity`;

--
-- Table structure update for table `stock_locators` for those that may already have the table
--

ALTER TABLE `stock_locators`   
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (`loccode`, `rack`, `row`, `bin`);

--
-- Table structure for table `stock_locator_moves`
--

CREATE TABLE IF NOT EXISTS `stock_locator_moves` (
  `stkmoveno` int(11) NOT NULL AUTO_INCREMENT,
  `stockid` varchar(20) NOT NULL DEFAULT '',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `transno` int(11) NOT NULL DEFAULT '0',
  `loccode` varchar(5) NOT NULL DEFAULT '',
  `rack` varchar(20) NOT NULL,
  `row` varchar(20) NOT NULL,
  `bin` varchar(20) NOT NULL,
  `trandate` date NOT NULL DEFAULT '0000-00-00',
  `userid` varchar(20) NOT NULL,
  `prd` smallint(6) NOT NULL DEFAULT '0',
  `reference` varchar(100) NOT NULL DEFAULT '',
  `qty` double NOT NULL DEFAULT '1',
  `newqoh` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`stkmoveno`),
  KEY `stockid` (`stockid`),
  KEY `type` (`type`),
  KEY `prd` (`prd`),
  KEY `stock_locator_moves_ibfk_3` (`loccode`,`rack`,`row`,`bin`),
  CONSTRAINT `stock_locator_moves_ibfk_1` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`),
  CONSTRAINT `stock_locator_moves_ibfk_2` FOREIGN KEY (`type`) REFERENCES `systypes` (`typeid`),
  CONSTRAINT `stock_locator_moves_ibfk_3` FOREIGN KEY (`loccode`, `rack`, `row`, `bin`) REFERENCES `stock_locators` (`loccode`, `rack`, `row`, `bin`),
  CONSTRAINT `stock_locator_moves_ibfk_4` FOREIGN KEY (`prd`) REFERENCES `periods` (`periodno`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT IGNORE INTO `systypes` (`typeid`, `typename`, `typeno`)
VALUES ('99', 'Stock Placement', 0);

INSERT IGNORE INTO `systypes` (`typeid`, `typename`, `typeno`)
VALUES ('98', 'Work Order Reverse', 0);

INSERT IGNORE INTO `config` VALUES ('StockLocatorIntegrated', '0');