master

分支 (1)

管理

管理

master

zhshopping
/
set_liuqan.sql

CREATE TABLE `set_singleRule` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `numberType` VARCHAR(40) DEFAULT NULL COMMENT '编号类型',
  `webNumberRule` VARCHAR(40) DEFAULT NULL COMMENT '网页版编号规则',
  `webNumberCount` BIGINT(20) DEFAULT NULL COMMENT '网页版编号位数',
  `webNumber` VARCHAR(40) DEFAULT NULL COMMENT '网页版当前编号',
  `CompNumberRule` VARCHAR(40) COMMENT '电脑版编号规则',
  `saddleNumberRule` VARCHAR(40) DEFAULT NULL COMMENT '鞍座版编号规则',
  `iPhoneNumberRule` VARCHAR(40) DEFAULT NULL COMMENT '苹果版编号规则',
  `numberFix` VARCHAR(40) DEFAULT NULL COMMENT '编号前缀',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8 COMMENT='单号规则';

INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('商品编号', 'SP{YYYY}{MM}{DD}{##}', '3', '898', 'SP{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'SP{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'SP{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'SP');
 INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('供应商编号', 'GYS{YYYY}{MM}{DD}{##}', '3', '11', 'GYS{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'GYS{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'GYS{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'GYS');
 INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('出库单编号', 'CKD{YYYY}{MM}{DD}{##}', '3', '9', 'CKD{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'CKD{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'CKD{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'CKD');
 INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('进货单编号', 'JHD{YYYY}{MM}{DD}{##}', '3', '10', 'JHD{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'JHD{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'JHD{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'JHD');
  INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('入库单编号', 'RKD{YYYY}{MM}{DD}{##}', '3', '10', 'RKD{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'RKD{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'RKD{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'RKD');
  INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('销售单编号', 'XSD{YYYY}{MM}{DD}{##}', '3', '14', 'XSD{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'XSD{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'XSD{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'XSD');
  INSERT INTO  `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('销售订单编号', 'XDD{YYYY}{MM}{DD}{##}', '3', '11', 'XDD{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'XDD{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'XDD{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'XDD');
  INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('客户编号', 'KH{YYYY}{MM}{DD}{##}', '3', '16', 'KH{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'KH{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'KH{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'KH');
  INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('商品编号', 'MB{YYYY}{MM}{DD}{##}', '3', '0', 'MB{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'MB{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'MB{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'MB');
  INSERT INTO `set_singleRule` (`numberType`, `webNumberRule`, `webNumberCount`, `webNumber`, `CompNumberRule`, `saddleNumberRule`, `iPhoneNumberRule`, `numberFix`)
 VALUES ('进货订单编号', 'JDD{YYYY}{MM}{DD}{##}', '3', '1', 'JDD{YYYY}{MM}{DD}{HH}{MM}{SS}P', 'JDD{YYYY}{MM}{DD}{HH}{MM}{SS}A', 'JDD{YYYY}{MM}{DD}{HH}{MM}{SS}I', 'JDD');


 CREATE TABLE `set_enterpriseInfo` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `merchantCode` VARCHAR(40) DEFAULT NULL COMMENT '商户代码',
  `userCount` INT DEFAULT NULL COMMENT '用户数',
  `openDate` DATETIME DEFAULT NULL COMMENT '开户日期',
  `administratorName` VARCHAR(40) DEFAULT NULL COMMENT '管理员姓名',
  `industryId` INT COMMENT '所属行业',
  `contact` VARCHAR(40) DEFAULT NULL COMMENT '联系人',
  `email` VARCHAR(40) DEFAULT NULL COMMENT '电子邮箱',
  `administratorAccount` VARCHAR(40) DEFAULT NULL COMMENT '管理员账号',
  `merchantName` VARCHAR(40) DEFAULT NULL COMMENT '商户名称',
  `dueDate` DATETIME  DEFAULT NULL COMMENT '到期日期',
  `companyAddress` VARCHAR(100) DEFAULT NULL COMMENT '公司地址',
  `url` VARCHAR(100) DEFAULT NULL COMMENT '网址',
  `phone` VARCHAR(40) DEFAULT NULL COMMENT '联系电话',
  `QQ` VARCHAR(40) DEFAULT NULL COMMENT 'QQ账号',
  `note` VARCHAR(40) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8 COMMENT='企业资料';

INSERT INTO `set_enterpriseInfo` (`merchantCode`, `userCount`) VALUES ('19212345678', '10');
UPDATE `set_enterpriseInfo` SET `openDate` = '2020-02-21 15:05:44' , `administratorName` = '老板' , `industryId` = '1' , `administratorAccount` = '156565' , `merchantName` = '野猫小食' , `dueDate` = '2020-05-29 15:08:27' WHERE `id` = '122';


 CREATE TABLE `set_industry` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `industryName` VARCHAR(40) DEFAULT NULL COMMENT '行业名称',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='行业';

INSERT INTO `set_industry` (`industryName`) VALUES ('烟酒食品');
INSERT INTO  `set_industry` (`industryName`) VALUES ('家电数码');
INSERT INTO  `set_industry` (`industryName`) VALUES ('服装箱包');
INSERT INTO  `set_industry` (`industryName`) VALUES ('家装材料');
INSERT INTO  `set_industry` (`industryName`) VALUES ('母婴玩具');
INSERT INTO  `set_industry` (`industryName`) VALUES ('日化用品');


CREATE TABLE `set_warehouseInfo` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `number` VARCHAR(40) DEFAULT NULL COMMENT '编号',
  `name` VARCHAR(40) DEFAULT NULL COMMENT '名称',
  `contact` VARCHAR(40) DEFAULT NULL COMMENT '联系人',
  `phone` VARCHAR(40) DEFAULT NULL COMMENT '联系电话',
  `status` INT COMMENT '状态（1:启用/0:未启用）',
  `E-mail` VARCHAR(40) DEFAULT NULL COMMENT '邮箱',
  `zipCode` VARCHAR(40) DEFAULT NULL COMMENT '邮编',
  `address` VARCHAR(40) DEFAULT NULL COMMENT '地址',
  `note` VARCHAR(40) DEFAULT NULL COMMENT '备注信息',
  `isDelete` INT DEFAULT '1' COMMENT '删除状态（0：删除）',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT='仓库';


INSERT INTO `set_warehouseInfo` (`number`, `name`, `contact`, `phone`, `status`) VALUES ('ck100', '默认仓库', '张三', '15996052563', '1');
INSERT INTO `set_warehouseInfo` (`number`, `name`, `contact`, `phone`, `status`) VALUES ('ck101', '一号仓库', '张即', '15996052893', '1');


CREATE TABLE `set_settleAccount` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `accountName` VARCHAR(40) DEFAULT NULL COMMENT '账户名称',
  `depositBank` VARCHAR(40) DEFAULT NULL COMMENT '开户银行',
  `accountBalance` DOUBLE DEFAULT NULL COMMENT '账户余额（元）',
  `status` INT COMMENT '状态（1:启用/0:未启用）',
  `initialBalance` DOUBLE DEFAULT NULL COMMENT '初期余额',
  `bankAccount` VARCHAR(40) DEFAULT NULL COMMENT '银行账号',
  `note` VARCHAR(40) DEFAULT NULL COMMENT '备注信息',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 COMMENT='结算账户';

INSERT INTO  `set_settleAccount` (`accountName`, `depositBank`, `accountBalance`, `status`, `initialBalance`, `bankAccount`)
VALUES ('结算账户1', '中国银行', '56.3', '1', '32.5', '6995896585366');
INSERT INTO  `set_settleAccount` (`accountName`, `depositBank`, `accountBalance`, `status`, `initialBalance`, `bankAccount`)
VALUES ('结算账户2', '邮政银行', '98.3', '1', '99.6', '6999999999999');


CREATE TABLE `set_employeeInfo` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `userName` VARCHAR(40) DEFAULT NULL COMMENT '用户名',
  `employeeName` VARCHAR(40) DEFAULT NULL COMMENT '员工姓名',
  `roleId` INT DEFAULT NULL COMMENT '角色id',
  `gender` INT DEFAULT NULL COMMENT '性别（1:男/2：女）',
  `mobile` VARCHAR(40) DEFAULT NULL COMMENT '联系电话',
  `E-mail` VARCHAR(40) DEFAULT NULL COMMENT '邮箱',
  `address` VARCHAR(40) DEFAULT NULL COMMENT '地址',
  `status` INT COMMENT '状态（1：正常/0：锁定）',
  `password` VARCHAR(40) DEFAULT NULL COMMENT '密码',
  `assWarehouse` INT DEFAULT NULL COMMENT '关联仓库id',
  `priceAuthorityId` INT DEFAULT NULL COMMENT '价格权限',
  `opDocuAuthority` INT DEFAULT NULL COMMENT '操作单据权限',
  `isDelete` INT DEFAULT '1' COMMENT '删除状态（0：删除）',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT='员工';

INSERT INTO  `set_employeeInfo` (`userName`, `employeeName`, `roleId`, `gender`, `mobile`, `E-mail`, `address`, `status`, `password`, `priceAuthorityId`, `opDocuAuthority`)
VALUES ('boss', '老板', '1', '1', '15963255698', '596@163.com', '江苏', '1', '123456', '1', '1');
INSERT INTO  `set_employeeInfo` (`userName`, `employeeName`, `roleId`, `gender`, `mobile`, `E-mail`, `address`, `status`, `password`, `priceAuthorityId`, `opDocuAuthority`)
VALUES ('zhangsan', '张三', '2', '1', '15963255698', '596@163.com', '云南', '1', '123456', '2', '1');
INSERT INTO  `set_employeeInfo` (`userName`, `employeeName`, `roleId`, `gender`, `mobile`, `E-mail`, `address`, `status`, `password`, `priceAuthorityId`, `opDocuAuthority`)
VALUES ('xiaoming', '小明', '3', '1', '15963255698', '596@163.com', '江苏', '1', '123456', '2', '1');
INSERT INTO  `set_employeeInfo` (`userName`, `employeeName`, `roleId`, `gender`, `mobile`, `E-mail`, `address`, `status`, `password`, `priceAuthorityId`, `opDocuAuthority`)
VALUES ('xiaohh', '小红花', '4', '2', '15963255698', '596@163.com', '广州', '1', '123456', '2', '1');
INSERT INTO  `set_employeeInfo` (`userName`, `employeeName`, `roleId`, `gender`, `mobile`, `E-mail`, `address`, `status`, `password`, `priceAuthorityId`, `opDocuAuthority`)
VALUES ('niming', '匿名', '5', '2', '15963255698', '596@163.com', '江苏', '1', '123456', '3', '1');

CREATE TABLE `set_role` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `roleName` VARCHAR(40) DEFAULT NULL COMMENT '角色名',
  `roleDescription` VARCHAR(100) DEFAULT NULL COMMENT '角色描述',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='角色表';

INSERT INTO  `set_role` (`roleName`, `roleDescription`) VALUES ('老板', '管理公司');
INSERT INTO  `set_role` (`roleName`, `roleDescription`) VALUES ('仓库管理员', '管理仓库');
INSERT INTO  `set_role` (`roleName`, `roleDescription`) VALUES ('销售', '负责销售');
INSERT INTO  `set_role` (`roleName`, `roleDescription`) VALUES ('财务', '负责财务');
INSERT INTO  `set_role` (`roleName`, `roleDescription`) VALUES ('普通员工', '上班');

CREATE TABLE `set_salesmanInfo` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(40) DEFAULT NULL COMMENT '姓名',
  `note` VARCHAR(100) DEFAULT NULL COMMENT '备注',
  `status` INT COMMENT '状态（1：正常/0：锁定）',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 COMMENT='导购员';

INSERT INTO  `set_salesmanInfo` (`name`, `status`) VALUES ('导购校花', '1');
INSERT INTO  `set_salesmanInfo` (`name`, `status`) VALUES ('导购校草', '1');
INSERT INTO  `set_salesmanInfo` (`name`, `status`) VALUES ('导购班花', '2');
INSERT INTO  `set_salesmanInfo` (`name`, `status`) VALUES ('导购班草', '1');