scj/sqls/sql1.0.0

### 出入库明细增加备注
ALTER TABLE `godown_entry_detail`
ADD COLUMN `notes` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

ALTER TABLE `godown_entry_detail_return`
ADD COLUMN `notes` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

ALTER TABLE `deliver_detail`
ADD COLUMN `notes` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

ALTER TABLE `deliver_detail_return`
ADD COLUMN `notes` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

ALTER TABLE `goods_godown_entry_detail`
ADD COLUMN `notes` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

ALTER TABLE `goods_deliver_detail_return`
ADD COLUMN `notes` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

ALTER TABLE `goods_deliver_detail`
ADD COLUMN `notes` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

ALTER TABLE `inventory_detail`
ADD COLUMN `notes` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

### 创建支付详情
CREATE TABLE `purchase_pay`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payment_time` datetime NOT NULL,
  `actual_amount` bigint(20) NOT NULL,
  `notes` varchar(200)  NULL,
  `main_id` int(11) NOT NULL,
  `payment_department_id` int(11) NOT NULL,
  `payment_type_id` int(11) NOT NULL,
  `payment_user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`payment_user_id`) REFERENCES `auth_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`main_id`) REFERENCES `purchase_payment` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`payment_department_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`payment_type_id`) REFERENCES `system_option` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  INDEX (`main_id`) USING BTREE,
  INDEX (`payment_department_id`) USING BTREE,
  INDEX (`payment_type_id`) USING BTREE,
  INDEX (`payment_user_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

### 采购合同增加付款方式
ALTER TABLE `purchase_order`
ADD COLUMN `payment_type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

####盘存明细添加盘亏库存记录外键
ALTER TABLE `inventory_detail`
ADD COLUMN `loss_stock_record_id`  int(11) NULL;
ALTER TABLE `inventory_detail` ADD FOREIGN KEY (`loss_stock_record_id`) REFERENCES `product_warehouse_stock_record` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;


### 出库单添加入库库存记录外键
ALTER TABLE `deliver_detail`
ADD COLUMN `warehouse_stockrecord_id` int(11) NULL;
ALTER TABLE `deliver_detail` ADD FOREIGN KEY (`warehouse_stockrecord_id`) REFERENCES `product_warehouse_stock_record` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `goods_deliver_detail`
ADD COLUMN `warehouse_stockrecord_id` int(11) NULL;
ALTER TABLE `goods_deliver_detail` ADD FOREIGN KEY (`warehouse_stockrecord_id`) REFERENCES `product_warehouse_stock_record` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

### 入库高级修改权限
INSERT into `auth_permission` (name, content_type_id, codename) values("高级修改",(select id from django_content_type where app_label = "purchase" and model = "godownentry"),"edit_material_godown_entry");
INSERT into `auth_permission` (name, content_type_id, codename) values("高级修改",(select id from django_content_type where app_label = "purchase" and model = "godownentrydetail"),"edit_consumable_godown_entry");
INSERT into `auth_permission` (name, content_type_id, codename) values("高级修改",(select id from django_content_type where app_label = "goods" and model = "goodsgodownentry"),"edit_goods_godown_entry");

### 增加交货时间
ALTER TABLE `purchase_order`
ADD COLUMN `deliver_time` varchar(200) NULL;

###合同添加申请金额字段
ALTER TABLE `purchase_order`
ADD COLUMN `apply_amount`  bigint(20) NULL DEFAULT 0;

###合同付款单添加申请金额字段
ALTER TABLE `purchase_payment`
ADD COLUMN `apply_amount`  bigint(20) NULL DEFAULT 0;


#touch版上添加，2019.7.11
### 创建公告
CREATE TABLE `office_notice`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL,
  `priority` smallint NOT NULL,
  `title` varchar(200) NOT NULL,
  `content` varchar(1000) NOT NULL,
  `dendline` datetime NOT NULL,
  `company_id` int(11) NOT NULL,
  `create_user_id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  `hits` bigint(20) NOT NULL,
  `create_user_department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`create_user_id`) REFERENCES `auth_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`company_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`create_user_department_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  INDEX (`create_user_id`) USING BTREE,
  INDEX (`department_id`) USING BTREE,
  INDEX (`company_id`) USING BTREE,
  INDEX (`create_user_department_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

### 创建浏览记录
CREATE TABLE `office_notice_browse_record`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `notice_id` int(11) NOT NULL,
  `browse_user_id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`notice_id`) REFERENCES `office_notice` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`browse_user_id`) REFERENCES `auth_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  INDEX (`notice_id`) USING BTREE,
  INDEX (`browse_user_id`) USING BTREE,
  INDEX (`department_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

### 创建公告回复
CREATE TABLE `office_notice_reply`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `notice_id` int(11) NOT NULL,
  `content` varchar(1000) NOT NULL,
  `create_user_id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`notice_id`) REFERENCES `office_notice` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`create_user_id`) REFERENCES `auth_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
    INDEX (`notice_id`) USING BTREE,
  INDEX (`create_user_id`) USING BTREE,
  INDEX (`department_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8;


### 创建公告附件
CREATE TABLE `office_notice_attachment`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `notice_id` int(11) NOT NULL,
  `file` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`notice_id`) REFERENCES `office_notice` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
    INDEX (`notice_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

### 创建公告权限
insert into django_content_type (app_label,model) values
('office','notice');
insert into django_content_type (app_label,model) values
('office','noticebrowserecord');

INSERT into `auth_permission` (name, content_type_id, codename) values("浏览",(select id from django_content_type where app_label = "office" and model = "notice"),"view_notice");
INSERT into `auth_permission` (name, content_type_id, codename) values("添加",(select id from django_content_type where app_label = "office" and model = "notice"),"add_notice");
INSERT into `auth_permission` (name, content_type_id, codename) values("删除",(select id from django_content_type where app_label = "office" and model = "notice"),"delete_notice");
INSERT into `auth_permission` (name, content_type_id, codename) values("浏览",(select id from django_content_type where app_label = "office" and model = "noticebrowserecord"),"view_browserecord");