/*
Navicat MySQL Data Transfer

Source Server         : MariaDB Develop 122
Source Server Version : 80017
Source Host           : 107.118.212.122:3306
Source Database       : sehc_vendor

Target Server Type    : MYSQL
Target Server Version : 80017
File Encoding         : 65001

Date: 2019-11-20 11:24:48
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for warehouse_stock_management
-- ----------------------------
DROP TABLE IF EXISTS `warehouse_stock_management`;
CREATE TABLE `warehouse_stock_management` (
  `VEND_CODE` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `WH` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ZONE` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `BIN` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `MODEL_CODE` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ACTUAL_QTY` bigint(11) NOT NULL DEFAULT '0',
  `MAG_NO` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `PO_NO` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORKER` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORKING_TIME` time DEFAULT NULL,
  `WORK_YMD` date DEFAULT NULL,
  `PROC_CODE` tinyint(2) unsigned DEFAULT NULL,
  `DESCRIPTION` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `DEVICE` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `IP` int(10) unsigned DEFAULT NULL,
  `UP_TYPE` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `CREATED` datetime DEFAULT NULL,
  `UPDATED` datetime DEFAULT NULL,
  `REMARK` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `TRANS_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`VEND_CODE`,`WH`,`ZONE`,`BIN`,`MAG_NO`),
  UNIQUE KEY `uniq_mag_1` (`VEND_CODE`,`MAG_NO`) USING BTREE,
  UNIQUE KEY `uniq_mag_loc` (`VEND_CODE`,`WH`,`ZONE`,`BIN`,`MAG_NO`) USING BTREE,
  KEY `filter` (`VEND_CODE`,`WH`,`ZONE`,`BIN`,`MODEL_CODE`) USING BTREE,
  KEY `PROC_CODE` (`VEND_CODE`,`PROC_CODE`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for warehouse_stock_management_h
-- ----------------------------
DROP TABLE IF EXISTS `warehouse_stock_management_h`;
CREATE TABLE `warehouse_stock_management_h` (
  `VEND_CODE` char(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `WH` char(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ZONE` char(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `BIN` char(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `MODEL_CODE` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ACTUAL_QTY` int(7) NOT NULL,
  `MAG_NO` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `PO_NO` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORKER` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORKING_TIME` time DEFAULT NULL,
  `WORK_YMD` date DEFAULT NULL,
  `PROC_CODE` tinyint(2) unsigned DEFAULT NULL,
  `DESCRIPTION` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `DEVICE` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `IP` int(10) unsigned DEFAULT NULL,
  `UP_TYPE` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `CREATED` datetime DEFAULT NULL,
  `UPDATED` datetime DEFAULT NULL,
  `REMARK` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `TRANS_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `MOD_TIME` datetime DEFAULT NULL,
  `ACTION` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TRIGGER IF EXISTS `stock after insert`;
DELIMITER ;;
CREATE TRIGGER `stock after insert` AFTER INSERT ON `warehouse_stock_management` FOR EACH ROW INSERT INTO warehouse_stock_management_h SELECT *,NOW(),'insert new' FROM warehouse_stock_management WHERE VEND_CODE = NEW.VEND_CODE AND WH = NEW.WH AND ZONE = NEW.ZONE AND BIN = NEW.BIN AND MAG_NO = NEW.MAG_NO
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `stock before update`;
DELIMITER ;;
CREATE TRIGGER `stock before update` BEFORE UPDATE ON `warehouse_stock_management` FOR EACH ROW INSERT INTO warehouse_stock_management_h SELECT *,NOW(),'update' FROM warehouse_stock_management WHERE VEND_CODE = NEW.VEND_CODE AND WH = NEW.WH AND ZONE = NEW.ZONE AND BIN = NEW.BIN AND MAG_NO = NEW.MAG_NO
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `stock after delete`;
DELIMITER ;;
CREATE TRIGGER `stock after delete` BEFORE DELETE ON `warehouse_stock_management` FOR EACH ROW INSERT INTO warehouse_stock_management_h SELECT *,NOW(),'delete' FROM warehouse_stock_management WHERE VEND_CODE = OLD.VEND_CODE AND WH = OLD.WH AND ZONE = OLD.ZONE AND BIN = OLD.BIN AND MAG_NO = OLD.MAG_NO
;;
DELIMITER ;
