术语
源表旧数据库中的表或数据需要迁出的表
目标表新数据库中的表或数据迁入的表
场景
业务相同，实现不同新-旧系统的数据库设计有很大的差异，比如： 1、表的数量不一致。 2、同样的表字段数量不一致，如都是用于存储“检查单信息”的checklist表，源表有30个字段，目标表却只有20个字段。 3、字段类型不一致，如都是表示checklist表的id, 源表为VARCHAR类型的uuid，目标表为INT类型的自增id。 4、字段值不一致，如都是表示性别的医生表doctor中的字段sex, 源表中 0:男，1：女，2：未知； 目标表中1：男，2：女，3：未知。 5、表与表之间的关联关系不一致，如源表将很多关联信息全部放到一张表中，而目标表将这些关联信息分别放在不同的表中，使用外键关联。 6、约束条件不一致，如源表doctor中 年龄字段可为NULL ，而目标表中年龄字段为NOT NULL，或其他唯一键等约束不一致。 7、 数据值重复，如新-旧系统有同时在使用的情况，并且新-旧系统的某些数据采用的同样的生成规则，而这些数据有必须具有唯一性，则在迁移的时候会导致数据重复报错。 8、其他差异。
解决
分析数据结构
新旧数据结构如下
新数据库结构
SET NAMES utf8mb4
;
SET FOREIGN_KEY_CHECKS
= 0;
DROP TABLE IF EXISTS `area
`;
CREATE TABLE `area
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '地区ID',
`PARENT_ID
` int(11) NOT NULL DEFAULT 0 COMMENT '地区父ID',
`CLASS_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '地区名称',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11109 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `assigntemp
`;
CREATE TABLE `assigntemp
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '指派记录流水表ID',
`user_id
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '指派人员ID',
`order_no
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '订单号',
`check_no
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '检查单号',
`expert_name
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '专家姓名',
`hospital
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '专家所属医院',
`cost
` decimal(10, 2) NULL DEFAULT NULL COMMENT '指派费用',
`create_time
` datetime(0) NULL DEFAULT NULL COMMENT '指派时间',
`title
` int(1) NOT NULL DEFAULT 2 COMMENT '专家职称：1医士、2医师、3主治医师、4副主任医师、5主任医师',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 673 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '指派记录流水表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `case`;
CREATE TABLE `case` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '病例id',
`patient_no
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`capsule_no
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '胶囊编号',
`inspection_subjects
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`check_date
` datetime(0) NULL DEFAULT NULL,
`medica_history
` varchar(800) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '既往病史',
`symptom_complaint
` varchar(800) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '症状主诉',
`remarks
` varchar(800) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '备注',
`patient_id
` int(11) NULL DEFAULT NULL,
`file_id
` int(11) NULL DEFAULT 0 COMMENT '影像文件id',
`report_id
` int(11) NULL DEFAULT 0 COMMENT '报告id',
`inspection_id
` int(11) NULL DEFAULT NULL,
`inspection_organization_id
` int(11) NULL DEFAULT NULL,
`imcd_id
` int(11) NULL DEFAULT NULL,
`emcd_id
` int(11) NULL DEFAULT NULL COMMENT '读片机构所属医联体',
`expert_organization_id
` int(11) NULL DEFAULT NULL COMMENT '读片机构id',
`expert_id
` int(11) NULL DEFAULT NULL COMMENT '读片医生id',
`m_id
` int(11) NULL DEFAULT NULL COMMENT '管理员id （所属运营人员ID）',
`medicalrecord_id
` int(11) NULL DEFAULT NULL COMMENT '病历id',
`file_name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '影像文件名',
`create_time
` datetime(0) NULL DEFAULT NULL COMMENT '病例管理页面显示的时间；数据库为报告审核通过时的时间',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 759 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '病例表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checklist
`;
CREATE TABLE `checklist
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '检查单id',
`case_id
` int(11) NOT NULL DEFAULT 0 COMMENT '病例id',
`patient_id
` int(11) NOT NULL COMMENT '患者表id',
`status` int(1) NOT NULL DEFAULT 0 COMMENT '检查单状态:1待提交;2已提交;3待审核;4待指派;5未审核通过;6待接受;7读片中;8报告待审核;9报告未通过;10已完成;11已退单',
`pay_state
` int(1) NOT NULL DEFAULT 0 COMMENT '是否付款(1待支付2已支付3待退款4已退款',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`is_downreport
` int(1) NULL DEFAULT 0 COMMENT '是否下载报告 0:待下载；1:已下载',
`check_no
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '检查单号',
`money
` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '送检支付金额',
`pay_type
` int(1) NOT NULL DEFAULT 1 COMMENT '支付方式(1:微信；2支付宝；3：预付；4：月结；5：免费)',
`refund_reason
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '退单原因',
`audit_note
` varchar(200) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '审核备注',
`upload_flag
` int(1) NULL DEFAULT 0 COMMENT '上传标志:0未上传1上传中2上传完成3上传失败',
`refund_id
` int(11) NULL DEFAULT NULL COMMENT '退单人id',
`refund_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '退单人姓名',
`refund_date
` datetime(0) NULL DEFAULT NULL COMMENT '退单时间',
`audit_time
` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
`audit_id
` int(11) NULL DEFAULT NULL COMMENT '审核人ID',
`audit_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '审核人姓名',
`recive_data
` varchar(2500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '点击导入时对方传过来的数据',
`type` int(11) NOT NULL DEFAULT 0 COMMENT '0:系统内创建；1：导入创建；2：一键导入（批量导入）',
`level` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '费用类型（指派时选择的专家医生级别）',
`expected_time
` datetime(0) NULL DEFAULT NULL COMMENT '期望报告时间',
`note
` varchar(800) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '描述阅片需求',
`account
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付帐户',
`return_account
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '返款时的支付账户',
`account_type
` int(1) NULL DEFAULT NULL COMMENT '帐户类别(1:微信；2:支付宝)',
`accept_account
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付时收款帐户',
`return_accept_account
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '返款时的收款账户',
`return_money
` decimal(10, 2) NULL DEFAULT NULL COMMENT '退款金额',
`wx_url
` varchar(1000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信二维码or支付宝地址',
`transaction_id
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信or支付宝transaction_id',
`failure_reason
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付失败原因',
`batch_no
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付宝退款批次号',
`out_trade_no
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '对应支付宝OUT_TRADE_NO',
`return_date
` datetime(0) NULL DEFAULT NULL COMMENT '退款时间',
`pay_date
` datetime(0) NULL DEFAULT NULL COMMENT '支付时间',
`assign_time
` datetime(0) NULL DEFAULT NULL COMMENT '指派时间',
`order_no
` varchar(128) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '订单号 （原读片订单表readorder的订单号，现已将readorder表和检查单checklist表整合,故加此字段。）',
PRIMARY KEY (`id
`) USING BTREE,
UNIQUE INDEX `check_no
`(`check_no
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 727 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '检查单' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checklistcount
`;
CREATE TABLE `checklistcount
` (
`no` int(10) NOT NULL AUTO_INCREMENT COMMENT '序号 主键',
`mcb_id
` int(10) NOT NULL COMMENT '医联体id',
`m_id
` int(10) NULL DEFAULT NULL COMMENT '所属运营人员id',
`mcb_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医联体名称',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`expect_id
` int(10) NULL DEFAULT NULL COMMENT '专家id',
`expect_name
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`intestine_num
` int(10) NULL DEFAULT NULL COMMENT '小肠胶囊数量',
`stomach_num
` int(10) NULL DEFAULT NULL COMMENT '胃胶囊数量',
`total_num
` int(10) NULL DEFAULT NULL COMMENT '总数量',
`years
` varchar(4) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '年份',
`months
` varchar(2) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '月份',
PRIMARY KEY (`no`) USING BTREE,
UNIQUE INDEX `un_name_date
`(`mcb_name
`, `org_name
`, `years
`, `months
`) USING BTREE COMMENT '医联体名称、机构名称、年份、月份构成的唯一键'
) ENGINE = InnoDB AUTO_INCREMENT = 106 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checklistcountofreadorder
`;
CREATE TABLE `checklistcountofreadorder
` (
`no` int(10) NOT NULL AUTO_INCREMENT COMMENT '序号 主键',
`mcb_id
` int(10) NOT NULL COMMENT '医联体id',
`m_id
` int(10) NULL DEFAULT NULL COMMENT '所属运营人员id',
`mcb_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医联体名称',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`expect_id
` int(10) NULL DEFAULT NULL COMMENT '专家id',
`expect_name
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`intestine_num
` int(10) NULL DEFAULT NULL COMMENT '小肠胶囊数量',
`stomach_num
` int(10) NULL DEFAULT NULL COMMENT '胃胶囊数量',
`total_num
` int(10) NULL DEFAULT NULL COMMENT '总数量',
`years
` varchar(4) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '年份',
`months
` varchar(2) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '月份',
PRIMARY KEY (`no`) USING BTREE,
UNIQUE INDEX `un_name_date
`(`mcb_name
`, `expect_name
`, `years
`, `months
`) USING BTREE COMMENT '医联体名称、专家姓名、年份、月份构成的唯一键'
) ENGINE = InnoDB AUTO_INCREMENT = 45 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checklistcountwithyear
`;
CREATE TABLE `checklistcountwithyear
` (
`no` int(10) NOT NULL AUTO_INCREMENT COMMENT '序号 主键',
`mcb_id
` int(10) NOT NULL COMMENT '医联体id',
`m_id
` int(10) NULL DEFAULT NULL COMMENT '所属运营人员id',
`mcb_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医联体名称',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`expect_id
` int(10) NULL DEFAULT NULL COMMENT '专家id',
`expect_name
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`intestine_num
` int(10) NULL DEFAULT NULL COMMENT '小肠胶囊数量',
`stomach_num
` int(10) NULL DEFAULT NULL COMMENT '胃胶囊数量',
`total_num
` int(10) NULL DEFAULT NULL COMMENT '总数量',
`years
` varchar(4) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '年',
PRIMARY KEY (`no`) USING BTREE,
UNIQUE INDEX `un_name_date
`(`mcb_name
`, `org_name
`, `years
`) USING BTREE COMMENT '医联体名称、机构名称、年构成的唯一键'
) ENGINE = InnoDB AUTO_INCREMENT = 52 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checklistcountwithyearmonthday
`;
CREATE TABLE `checklistcountwithyearmonthday
` (
`no` int(10) NOT NULL AUTO_INCREMENT COMMENT '序号 主键',
`mcb_id
` int(10) NOT NULL COMMENT '医联体id',
`m_id
` int(10) NULL DEFAULT NULL COMMENT '所属运营人员id',
`mcb_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医联体名称',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`expect_id
` int(10) NULL DEFAULT NULL COMMENT '专家id',
`expect_name
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`intestine_num
` int(10) NULL DEFAULT NULL COMMENT '小肠胶囊数量',
`stomach_num
` int(10) NULL DEFAULT NULL COMMENT '胃胶囊数量',
`total_num
` int(10) NULL DEFAULT NULL COMMENT '总数量',
`create_time
` datetime(0) NOT NULL COMMENT '年月日',
PRIMARY KEY (`no`) USING BTREE,
UNIQUE INDEX `un_name_date
`(`mcb_name
`, `org_name
`, `create_time
`) USING BTREE COMMENT '医联体名称、机构名称、年月日构成的唯一键'
) ENGINE = InnoDB AUTO_INCREMENT = 307 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checklistcountwithyearmonthdayofreadorder
`;
CREATE TABLE `checklistcountwithyearmonthdayofreadorder
` (
`no` int(10) NOT NULL AUTO_INCREMENT COMMENT '序号 主键',
`mcb_id
` int(10) NOT NULL COMMENT '医联体id',
`m_id
` int(10) NULL DEFAULT NULL COMMENT '所属运营人员id',
`mcb_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医联体名称',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`expect_id
` int(10) NULL DEFAULT NULL COMMENT '专家id',
`expect_name
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`intestine_num
` int(10) NULL DEFAULT NULL COMMENT '小肠胶囊数量',
`stomach_num
` int(10) NULL DEFAULT NULL COMMENT '胃胶囊数量',
`total_num
` int(10) NULL DEFAULT NULL COMMENT '总数量',
`create_time
` datetime(0) NOT NULL COMMENT '年月日',
PRIMARY KEY (`no`) USING BTREE,
UNIQUE INDEX `un_name_date
`(`mcb_name
`, `expect_name
`, `create_time
`) USING BTREE COMMENT '医联体名称、专家姓名、年月日构成的唯一键'
) ENGINE = InnoDB AUTO_INCREMENT = 281 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checklistcountwithyearofreadorder
`;
CREATE TABLE `checklistcountwithyearofreadorder
` (
`no` int(10) NOT NULL AUTO_INCREMENT COMMENT '序号 主键',
`mcb_id
` int(10) NOT NULL COMMENT '医联体id',
`m_id
` int(10) NULL DEFAULT NULL COMMENT '所属运营人员id',
`mcb_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医联体名称',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`expect_id
` int(10) NULL DEFAULT NULL COMMENT '专家id',
`expect_name
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`intestine_num
` int(10) NULL DEFAULT NULL COMMENT '小肠胶囊数量',
`stomach_num
` int(10) NULL DEFAULT NULL COMMENT '胃胶囊数量',
`total_num
` int(10) NULL DEFAULT NULL COMMENT '总数量',
`years
` varchar(4) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '年',
PRIMARY KEY (`no`) USING BTREE,
UNIQUE INDEX `un_name_date
`(`mcb_name
`, `expect_name
`, `years
`) USING BTREE COMMENT '医联体名称、专家姓名、年构成的唯一键'
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `consumption_record
`;
CREATE TABLE `consumption_record
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`consumption_amount
` decimal(10, 2) NOT NULL COMMENT '消费金额',
`doctor_id
` int(11) NOT NULL DEFAULT 0 COMMENT '送检医生id',
`doctor_name
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '送检医生姓名',
`org_id
` int(11) NOT NULL COMMENT '机构id',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`inspection_subjects
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '检查科目',
`check_no
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '检查单号',
`account_balance_id
` int(11) NOT NULL COMMENT '账户id',
`create_time
` datetime(0) NOT NULL COMMENT '消费时间',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 191 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '消费记录' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `distributor
`;
CREATE TABLE `distributor
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '经销商ID',
`dealer_name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '经销商名',
`contact_name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '联系人姓名',
`contact_phone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '联系人电话',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`enabled
` int(1) NOT NULL DEFAULT 1 COMMENT '是否启用：0不启用，1启用',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '经销商' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `distributortemp
`;
CREATE TABLE `distributortemp
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '经销商ID',
`DEALER_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '经销商名',
`CONTACT_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '联系人姓名',
`CONTACT_PHONE
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '联系人电话',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`ENABLED
` int(1) NOT NULL DEFAULT 1 COMMENT '是否启用：0不启用，1启用',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '经销商' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `doctor
`;
CREATE TABLE `doctor
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '医生id',
`password
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '密码',
`username
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '医生姓名',
`cellphone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '联系电话',
`sex
` int(1) NOT NULL COMMENT '性别(1:男；2：女)',
`org_id
` int(11) NULL DEFAULT NULL COMMENT '所属医院id',
`title
` int(11) NULL DEFAULT NULL COMMENT '医生职称：1医士、2医师、3主治医师、4副主任医师、5主任医师',
`passport
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照(执业医师证)',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`state
` int(1) NOT NULL DEFAULT 0 COMMENT '审核状态(0:未审核;1:审核通过；2：审核不通过)',
`description
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '审核不通过原因',
`user_id
` int(11) NULL DEFAULT NULL COMMENT '创建人员id',
`type` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT 'doctor' COMMENT '医生类型（送检端机构管理员：admindoctor;送检端普通医生：doctor;专家端：expect）',
`userphoto
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '头像',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9001 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '送检医生表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `doctortem
`;
CREATE TABLE `doctortem
` (
`ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医生ID',
`CELLPHONE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医生电话',
`USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '医生姓名',
`PASSWORD
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医生密码',
`USERPHOTO
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '医生头像',
`LOGINTIME
` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
`SEX
` int(1) NULL DEFAULT 0 COMMENT '医生性别：0男，1女',
`WEIXIN
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信号',
`ALIPAY
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付宝账号',
`ADDR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`CITY
` int(11) NULL DEFAULT 0 COMMENT '市',
`PROVINCE
` int(11) NULL DEFAULT 0 COMMENT '省',
`DISTRICT
` int(11) NULL DEFAULT 0 COMMENT '区县',
`HOSPITAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '所属医院',
`PASSPORT
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照片',
`CHECK_LIST
` int(11) NULL DEFAULT 0 COMMENT '检查单总数',
`STATUS` int(1) NULL DEFAULT 1 COMMENT '0:未审核,1:审核通过,2:审核未通过 ',
`M_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '管理员id',
`M_USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '超级管理员' COMMENT '管理员姓名',
`MAC
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆mac地址',
`IP
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆IP地址',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '创建人ID',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`UPDATE_TIME
` datetime(0) NOT NULL COMMENT '修改时间',
`UPDATE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '修改人ID',
`PROFESSIONAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '医师/住院医师' COMMENT '类型：医师/住院医师，主治医师等',
`PAYTYPE
` int(11) NULL DEFAULT 0 COMMENT '支付方式：0线上支付；1线下支付',
`TYPE` int(11) NULL DEFAULT 1 COMMENT '医生类型0:机构管理员医生；1；普通医生',
`ORGANIZATIONID
` int(11) NULL DEFAULT 0 COMMENT '机构ID',
`ORGANIZATIONNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '机构名称',
`increment_id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
PRIMARY KEY (`increment_id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 166 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '送检医生' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `doctortest
`;
CREATE TABLE `doctortest
` (
`id
` int(11) NOT NULL COMMENT '医生id',
`id_uuid
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '临时字段uuid',
`password
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '密码',
`username
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '医生姓名',
`cellphone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '联系电话',
`sex
` int(1) NOT NULL COMMENT '性别(1:男；2：女)',
`org_id
` int(11) NULL DEFAULT NULL COMMENT '所属医院id',
`title
` int(11) NULL DEFAULT NULL COMMENT '医生职称：1医士、2医师、3主治医师、4副主任医师、5主任医师',
`passport
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照(执业医师证)',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`state
` int(1) NOT NULL DEFAULT 0 COMMENT '审核状态(0:未审核;1:审核通过；2：审核不通过)',
`description
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '审核不通过原因',
`user_id
` int(11) NULL DEFAULT NULL COMMENT '创建人员id',
`type` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT 'doctor' COMMENT '医生类型（送检端机构管理员：admindoctor;送检端普通医生：doctor;专家端：expect）',
`userphoto
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '头像',
PRIMARY KEY (`id_uuid
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '送检医生表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `endoscopicreport
`;
CREATE TABLE `endoscopicreport
` (
`id
` int(10) NOT NULL AUTO_INCREMENT COMMENT '内镜报告ID',
`repot_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '报告文件名',
`order_no
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '订单号',
`create_time
` datetime(0) NOT NULL COMMENT '报告上传时间',
`user_id
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '上传报告的用户ID',
`check_no
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`report_address
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '报告地址',
`audit_content
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '审核意见',
`audit_status
` int(1) NULL DEFAULT 1 COMMENT '报告状态（1：报告待审核；2：报告已通过;3：报告未通过）',
`audit_time
` datetime(0) NULL DEFAULT NULL COMMENT '报告审核时间',
PRIMARY KEY (`id
`) USING BTREE,
UNIQUE INDEX `UN_CHECKNO
`(`check_no
`) USING BTREE COMMENT '检查单号唯一键',
UNIQUE INDEX `check_no
`(`check_no
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 808 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '内镜报告表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `error_log
`;
CREATE TABLE `error_log
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '错误日志id',
`error_type
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '错误类型',
`operating_module
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '操作模块',
`create_time
` datetime(0) NOT NULL COMMENT '错误时间',
`user_id
` int(11) NOT NULL COMMENT '操作人id',
`error_sources
` char(3) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '错误来源：医生端，专家端，运营端',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '错误日志表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `expect
`;
CREATE TABLE `expect
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '专家id',
`password
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '密码',
`username
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`cellphone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '联系电话',
`sex
` int(1) NOT NULL COMMENT '性别(1:男；2：女)',
`org_id
` int(11) NULL DEFAULT NULL COMMENT '所属医院id',
`title
` int(11) NULL DEFAULT NULL COMMENT '专家职称：1医士、2医师、3主治医师、4副主任医师、5主任医师',
`passport
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照(执业医师证)',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`user_id
` int(11) NULL DEFAULT NULL COMMENT '创建人id',
`m_id
` int(11) NULL DEFAULT NULL COMMENT '管理员id',
`state
` int(1) NOT NULL DEFAULT 0 COMMENT '审核状态(0:未审核;1:审核通过；2：审核不通过)',
`description
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '审核不通过原因',
`type` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT 'expect' COMMENT '医生类型（送检端机构管理员：admindoctor;送检端普通医生：doctor;专家端：expect）',
`city_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '市名称',
`district_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '区县名称',
`province_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '省名称',
`m_c_d_id
` int(11) NULL DEFAULT NULL COMMENT '所属医联体id',
`stomach_fee
` decimal(6, 2) NULL DEFAULT NULL COMMENT '胃胶囊费用',
`intestines_fee
` decimal(6, 2) NULL DEFAULT 0.00 COMMENT '小肠胶囊费用',
`level` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家等级',
`hospital
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '所属医院',
`userphoto
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '头像',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 132 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '专家表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `expecttest
`;
CREATE TABLE `expecttest
` (
`id
` int(11) NOT NULL COMMENT '专家id',
`id_uuid
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '临时字段uuid',
`password
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '密码',
`username
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`cellphone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '联系电话',
`sex
` int(1) NOT NULL COMMENT '性别(1:男；2：女)',
`org_id
` int(11) NULL DEFAULT NULL COMMENT '所属医院id',
`title
` int(11) NULL DEFAULT NULL COMMENT '专家职称：1医士、2医师、3主治医师、4副主任医师、5主任医师',
`passport
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照(执业医师证)',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`user_id
` int(11) NULL DEFAULT NULL COMMENT '创建人id',
`m_id
` int(11) NULL DEFAULT NULL COMMENT '管理员id',
`m_id_uuid
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '机构管理员_uuid形式：临时使用',
`state
` int(1) NOT NULL DEFAULT 0 COMMENT '审核状态(0:未审核;1:审核通过；2：审核不通过)',
`description
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '审核不通过原因',
`type` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT 'expect' COMMENT '医生类型（送检端机构管理员：admindoctor;送检端普通医生：doctor;专家端：expect）',
`city_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '市名称',
`district_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '区县名称',
`province_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '省名称',
`m_c_d_id
` int(11) NULL DEFAULT NULL COMMENT '所属医联体id',
`stomach_fee
` decimal(6, 2) NULL DEFAULT NULL COMMENT '胃胶囊费用',
`intestines_fee
` decimal(6, 2) NULL DEFAULT 0.00 COMMENT '小肠胶囊费用',
`level` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家等级',
`hospital
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '所属医院',
`userphoto
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '头像',
PRIMARY KEY (`id_uuid
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '专家表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `experttemp
`;
CREATE TABLE `experttemp
` (
`ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '专家ID',
`CELLPHONE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '专家电话',
`USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`PASSWORD
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '专家密码',
`USERPHOTO
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '专家头像',
`LOGINTIME
` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
`SEX
` int(1) NULL DEFAULT 0 COMMENT '专家性别：0男，1女',
`WEIXIN
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信号',
`ALIPAY
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付宝账号',
`ADDR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`CITY
` int(11) NULL DEFAULT 0 COMMENT '市',
`PROVINCE
` int(11) NULL DEFAULT 0 COMMENT '省',
`DISTRICT
` int(11) NULL DEFAULT 0 COMMENT '区县',
`HOSPITAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '所属医院',
`PASSPORT
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照片',
`CHECK_LIST
` int(11) NULL DEFAULT 0 COMMENT '订单总数',
`STATUS` int(1) NULL DEFAULT 1 COMMENT '0:未审核,1:审核通过,2:审核未通过 ',
`M_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '管理员id',
`M_USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '超级管理员' COMMENT '管理员姓名',
`MAC
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆mac地址',
`IP
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆IP地址',
`PROFESSIONAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '医师/住院医师' COMMENT '类型：医师/住院医师，主治医师等',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '创建人ID',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`UPDATE_TIME
` datetime(0) NOT NULL COMMENT '修改时间',
`UPDATE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '修改人ID',
`EXPERT_TYPE
` int(1) NULL DEFAULT 0 COMMENT '0:读片医师;1:省级专家;2:顶级专家',
`STOMACH_FEE
` double(10, 2) NULL DEFAULT 0.00 COMMENT '胃胶囊费用',
`INTESTINES_FEE
` double(10, 2) NULL DEFAULT 0.00 COMMENT '小肠胶囊费用',
`increment_id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
PRIMARY KEY (`increment_id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 44 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '读片专家' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `feedback
`;
CREATE TABLE `feedback
` (
`id
` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '姓名',
`mobile
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '手机号码',
`mcb_id
` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '所属医联体ID',
`org_id
` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '所属机构ID（医院ID）',
`pics
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '反馈图片，多个用,分割',
`content
` text CHARACTER SET utf8
COLLATE utf8_general_ci
NULL COMMENT '反馈内容',
`create_time
` datetime(0) NULL DEFAULT NULL COMMENT '反馈时间',
`dispose_time
` datetime(0) NULL DEFAULT NULL COMMENT '处理时间',
`state
` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '状态：0未处理，1已处理',
`user_id
` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '处理人ID',
PRIMARY KEY (`id
`) USING BTREE,
INDEX `create_time
`(`create_time
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '反馈' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `film
`;
CREATE TABLE `film
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`price
` double(10, 2) NULL DEFAULT 0.00,
`create_date
` datetime(0) NULL DEFAULT NULL,
`type` int(1) NOT NULL DEFAULT 0 COMMENT '0:胃胶囊；1：小肠胶囊',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 54 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '费用设置' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `film_history
`;
CREATE TABLE `film_history
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`update_date
` datetime(0) NULL DEFAULT NULL,
`operate
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`result
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '费用记录表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `generaterule
`;
CREATE TABLE `generaterule
` (
`id
` int(10) NOT NULL AUTO_INCREMENT,
`no` varchar(4) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`type` varchar(1) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '1 检查单 2 订单 ',
`end_time
` datetime(0) NOT NULL COMMENT '结束时间',
`create_time
` datetime(0) NOT NULL,
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '检查订,订单等生成规则' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `imagedetail
`;
CREATE TABLE `imagedetail
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`check_no
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`fname
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '文件名',
`create_time
` datetime(0) NULL DEFAULT NULL,
`upload_flag
` int(1) NULL DEFAULT 0 COMMENT '上传标志0未上传1上传中2已上传3上传失败',
`download_flag
` int(1) NULL DEFAULT 0 COMMENT '下载标志0未下载1下载中2已下载',
`upload_percent
` double(5, 2) NULL DEFAULT 0.00 COMMENT '上传百分比',
`download_percent
` double(5, 2) NULL DEFAULT 0.00 COMMENT '下载百分比',
`fid
` int(11) NULL DEFAULT 0 COMMENT '送检影像文件id',
PRIMARY KEY (`id
`) USING BTREE,
UNIQUE INDEX `check_no
`(`check_no
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 715 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '影像文件明细' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `imagefile
`;
CREATE TABLE `imagefile
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`check_no
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`user_id
` int(11) NOT NULL COMMENT '用户id(送检医生id)',
`local_url
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '本地路径',
`server_url
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '服务端路径',
`upload_flag
` int(1) NULL DEFAULT 0 COMMENT '上传标志0未上传1上传中2已上传,3上传失败',
`lock` int(1) NULL DEFAULT 0 COMMENT '状态锁1上锁0未锁',
`download_flag
` int(1) NULL DEFAULT 0 COMMENT '下载标志0未下载1已下载3取消下载',
`read_userid
` int(10) NULL DEFAULT NULL COMMENT '读片专家id',
`total_file
` int(10) NULL DEFAULT NULL COMMENT '文件总数',
`download_file
` int(10) NULL DEFAULT 0 COMMENT '下载文件数',
`upload_file
` int(10) NULL DEFAULT 0 COMMENT '上传文件数()',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`last_time
` datetime(0) NULL DEFAULT NULL COMMENT '最近操作时间',
`fsize
` bigint(30) NULL DEFAULT 0 COMMENT '文件大小',
`fuploadsize
` bigint(30) NULL DEFAULT 0 COMMENT '已上传文件大小',
PRIMARY KEY (`id
`) USING BTREE,
UNIQUE INDEX `check_no
`(`check_no
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 715 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '送检影像文件' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `medical_conjoined_body
`;
CREATE TABLE `medical_conjoined_body
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '医联体id',
`name
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医联体名称',
`province_id
` int(11) NULL DEFAULT NULL COMMENT '省id',
`city_id
` int(11) NULL DEFAULT NULL COMMENT '市id',
`district_id
` int(11) NULL DEFAULT NULL COMMENT '区县id',
`user_id
` int(11) NOT NULL COMMENT '创建人id',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`address
` varchar(200) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`city_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '市名称',
`district_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '区县名称',
`province_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '省名称',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '医联体表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `medicalrecord
`;
CREATE TABLE `medicalrecord
` (
`id
` int(10) NOT NULL AUTO_INCREMENT COMMENT '病历ID',
`medicalrecord_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '病历文件名',
`create_time
` datetime(0) NOT NULL COMMENT '病历上传时间',
`check_no
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '0' COMMENT '检查单号',
`medicalrecord_address
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '病历地址',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 23 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '病历表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `message
`;
CREATE TABLE `message
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户消息ID',
`content
` varchar(200) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '消息内容',
`status` int(1) NULL DEFAULT 0 COMMENT '消息状态（0:未读状态；1：已读状态）',
`create_time
` datetime(0) NULL DEFAULT NULL COMMENT '消息发送时间',
`type` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '消息名称（即消息类型）',
`m_id
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '管理员id',
`send_id
` int(11) NOT NULL COMMENT '发送人id',
`receive_id
` int(11) NOT NULL COMMENT '接收人id',
`send_type
` varchar(10) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '发送方类型（SysUser;Expect;Doctor）',
`receive_type
` varchar(10) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '接收方方类型（SysUser;Expect;Doctor）',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7368 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '用户消息' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `multi_data_checkno
`;
CREATE TABLE `multi_data_checkno
` (
`CHECK_NO
` varchar(25) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
PRIMARY KEY (`CHECK_NO
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Dynamic
;
DROP TABLE IF EXISTS `notice
`;
CREATE TABLE `notice
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '公告ID',
`title
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '公告标题',
`content
` varchar(1000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '公告内容',
`type` int(1) NOT NULL DEFAULT 0 COMMENT '查看人员 0：医生；1：专家；2：全部',
`create_time
` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`cancellation_time
` datetime(0) NULL DEFAULT NULL COMMENT '允许撤销的时间',
`status` int(1) NOT NULL DEFAULT 0 COMMENT '公告状态，0:未发布；1：已发布',
PRIMARY KEY (`id
`) USING BTREE,
INDEX `create_time
`(`create_time
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '公告表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `noticetemp
`;
CREATE TABLE `noticetemp
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '公告流水表ID',
`title
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '公告标题',
`content
` varchar(1000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '公告内容',
`create_time
` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`status` int(1) NOT NULL DEFAULT 0 COMMENT '公告状态，0:未读；1：已读',
`receive_id
` int(11) NOT NULL COMMENT '接收人ID',
`notice_id
` int(11) NOT NULL COMMENT '公告表ID',
`type` int(10) UNSIGNED NOT NULL COMMENT '类型：0医生，1专家',
PRIMARY KEY (`id
`) USING BTREE,
INDEX `notice_id
`(`notice_id
`) USING BTREE,
INDEX `receive_id
`(`receive_id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '公告流水表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `operatingrecord
`;
CREATE TABLE `operatingrecord
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '操作记录ID',
`operation_status
` int(2) NOT NULL COMMENT '操作状态ID',
`operstion_name
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '操作名称',
`operstion_result
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '操作结果',
`content
` varchar(1000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '操作描述',
`user_id
` int(11) NULL DEFAULT NULL COMMENT '操作人ID',
`user_name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '操作人姓名',
`role_name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '操作人角色',
`check_no
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '检查单号',
`order_no
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '订单号',
`type` int(1) NOT NULL DEFAULT 0 COMMENT '操作记录类型（0：运行查询该检查单的全部记录；1：送检医生查询检查单记录；2：读片医生查询订单记录）',
`create_time
` datetime(0) NOT NULL COMMENT '操作时间',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3641 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '操作记录表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `operationstatus
`;
CREATE TABLE `operationstatus
` (
`id
` int(10) NOT NULL AUTO_INCREMENT COMMENT '操作状态ID',
`name
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '操作名称',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '操作状态表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `org_account
`;
CREATE TABLE `org_account
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`account_balance
` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '账户余额',
`org_id
` int(11) NOT NULL COMMENT '机构id',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`update_time
` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '机构账户表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `organization
`;
CREATE TABLE `organization
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '机构id',
`name
` varchar(40) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`type` int(1) NOT NULL COMMENT '机构类型（1公立三甲、2普通公立医院、3民营医院、4专业体检中心）',
`user_id
` int(11) NOT NULL COMMENT '注册医生id',
`cellphone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '联系电话',
`province_id
` int(11) NULL DEFAULT NULL COMMENT '省id',
`city_id
` int(11) NULL DEFAULT NULL COMMENT '市id',
`district_id
` int(11) NULL DEFAULT NULL COMMENT '区县id',
`address
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`passport
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照(医疗机构执业许可)',
`pay_type
` int(1) NOT NULL DEFAULT 1 COMMENT '支付方式（1:线上支付；2：预付；3：月结；4：免费）',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`state
` int(1) NOT NULL DEFAULT 0 COMMENT '审核状态(0：未审核;1：审核通过；2：审核不通过）',
`m_id
` int(11) NULL DEFAULT NULL COMMENT '机构管理员',
`mcb_id
` int(11) NULL DEFAULT NULL COMMENT '所属医联体id',
`describe` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '审核不通过原因',
`city_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '市名称',
`district_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '区县名称',
`province_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '省名称',
`department
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '科室名称',
`dealer_id
` int(11) NULL DEFAULT NULL COMMENT '经销商id',
`dealer_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '经销商名称',
PRIMARY KEY (`id
`) USING BTREE,
UNIQUE INDEX `UN_USER_ID
`(`user_id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10001 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '机构' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `organizationtemp
`;
CREATE TABLE `organizationtemp
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '机构ID',
`ORGANIZATIONNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '医院名称',
`SECTIONNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '科室名称',
`CITY
` int(11) NULL DEFAULT 0 COMMENT '市',
`PROVINCE
` int(11) NULL DEFAULT 0 COMMENT '省',
`DISTRICT
` int(11) NULL DEFAULT 0 COMMENT '区县',
`ADDR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '医生姓名(机构管理员信息)',
`SEX
` int(1) NULL DEFAULT 0 COMMENT '医生性别：0男，1女',
`CELLPHONE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '联系电话',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '创建人ID',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`UPDATE_TIME
` datetime(0) NOT NULL COMMENT '修改时间',
`UPDATE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '修改人ID',
`PROFESSIONAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '医师/住院医师' COMMENT '类型：医师/住院医师，主治医师等',
`CHECK_LIST
` int(11) NULL DEFAULT 0 COMMENT '检查单总数',
`STATUS` int(1) NULL DEFAULT 0 COMMENT '0:未审核,1:审核通过,2:审核未通过 ',
`M_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '管理员id',
`M_USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '超级管理员' COMMENT '管理员姓名',
`USERPHOTO
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '机构资质照片',
`PAYTYPE
` int(11) NULL DEFAULT 0 COMMENT '支付方式：0线上支付；1线下支付',
`REGISTERED_DOCTOR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '已注册医生',
`DISTRIBUTOR_ID
` int(11) NOT NULL DEFAULT 0 COMMENT '所属经销商ID',
`DISTRIBUTOR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '所属经销商',
`ENABLED
` int(1) NOT NULL DEFAULT 1 COMMENT '是否启用：0不启用，1启用',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 192 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '机构信息' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `organizationtest
`;
CREATE TABLE `organizationtest
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '机构id',
`name
` varchar(40) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`type` int(1) NOT NULL COMMENT '机构类型（1公立三甲、2普通公立医院、3民营医院、4专业体检中心）',
`user_id
` int(11) NOT NULL COMMENT '注册医生id',
`user_id_uuid
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '注册医生uuid',
`cellphone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '联系电话',
`province_id
` int(11) NULL DEFAULT NULL COMMENT '省id',
`city_id
` int(11) NULL DEFAULT NULL COMMENT '市id',
`district_id
` int(11) NULL DEFAULT NULL COMMENT '区县id',
`address
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`passport
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照(医疗机构执业许可)',
`pay_type
` int(1) NOT NULL DEFAULT 1 COMMENT '支付方式（1:线上支付；2：预付；3：月结；4：免费）',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`state
` int(1) NOT NULL DEFAULT 0 COMMENT '审核状态(0：未审核;1：审核通过；2：审核不通过）',
`m_id
` int(11) NULL DEFAULT NULL COMMENT '机构管理员',
`m_id_uuid
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '机构管理员_uuid形式：临时使用',
`mcb_id
` int(11) NULL DEFAULT NULL COMMENT '所属医联体id',
`describe` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '审核不通过原因',
`city_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '市名称',
`district_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '区县名称',
`province_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '省名称',
`department
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '科室名称',
`dealer_id
` int(11) NULL DEFAULT NULL COMMENT '经销商id',
`dealer_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '经销商名称',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 192 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '机构' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `patient
`;
CREATE TABLE `patient
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '患者id',
`name
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`sex
` int(11) NULL DEFAULT NULL,
`age
` int(3) NULL DEFAULT 0 COMMENT '年龄',
`phone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`province_id
` int(4) NULL DEFAULT 0 COMMENT '省id',
`city_id
` int(4) NULL DEFAULT 0 COMMENT '市id',
`district_id
` int(4) NULL DEFAULT NULL COMMENT '区县id',
`user_id
` int(11) NULL DEFAULT NULL,
`organization_id
` int(11) NULL DEFAULT NULL,
`m_c_d_id
` int(11) NULL DEFAULT NULL,
`create_time
` datetime(0) NULL DEFAULT NULL,
`city_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`district_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`province_name
` varchar(20) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 759 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '患者表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `readorder
`;
CREATE TABLE `readorder
` (
`id
` int(10) NOT NULL AUTO_INCREMENT COMMENT '读片订单ID',
`order_no
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '订单号',
`order_status
` int(2) NOT NULL DEFAULT 1 COMMENT '订单状态(1：待接受；2：读片中；3：报告未通过；4：报告待审核；5：报告已通过；6：已退单)',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`user_id
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '创建人ID',
`check_no
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`cancle_reason
` varchar(200) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '取消原因',
`cancle_name
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '退单人',
`report_id
` int(10) NULL DEFAULT 0 COMMENT '报告ID',
`cancle_time
` datetime(0) NULL DEFAULT NULL COMMENT '取消读片订单时间',
`expert_id
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '专家ID',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '读片订单' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `recharge_record
`;
CREATE TABLE `recharge_record
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`recharge_amount
` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '充值金额',
`user_id
` int(11) NOT NULL DEFAULT 0 COMMENT '充值人员id',
`username
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '充值人员姓名',
`org_id
` int(11) NOT NULL COMMENT '机构id',
`org_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '机构名称',
`account_balance_id
` int(11) NOT NULL COMMENT '账户id',
`create_time
` datetime(0) NOT NULL COMMENT '充值时间',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '充值记录' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `short_message
`;
CREATE TABLE `short_message
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '短信提醒id',
`message_name
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '消息名称',
`content
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '内容',
`sender_id
` int(11) NOT NULL COMMENT '发送方id',
`receiver_id
` int(11) NOT NULL COMMENT '接收方id',
`state
` int(1) NOT NULL COMMENT '发送状态（1：成功；2：失败）',
`create_time
` datetime(0) NOT NULL COMMENT '发送时间',
`send_type
` varchar(10) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '发送方类型（SysUser;Expect;Doctor）',
`receive_type
` varchar(10) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '接收方方类型（SysUser;Expect;Doctor）',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2602 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '短信提醒' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sms_code
`;
CREATE TABLE `sms_code
` (
`sms_id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '验证码id',
`mobile
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '电话号码',
`send_date
` datetime(0) NULL DEFAULT NULL COMMENT '发送时间',
`send_type
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '发送类型',
`sms_code
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '验证码',
`verify
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '验证结果',
PRIMARY KEY (`sms_id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 74 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '验证码表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_permission
`;
CREATE TABLE `sys_permission
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '权限ID\r\n',
`per_name
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '权限名称',
`per_desc
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '权限名称描述',
`p_id
` int(11) NOT NULL COMMENT '父权限id',
`per_url
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '资源地址',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 76 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '权限表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_role
`;
CREATE TABLE `sys_role
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统角色id',
`role_name
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '角色描述',
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统角色' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_role_permission
`;
CREATE TABLE `sys_role_permission
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统角色与权限id',
`role_id
` int(11) NOT NULL COMMENT '系统角色id',
`per_id
` int(11) NOT NULL COMMENT '系统权限id',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 526 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统角色与权限' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_user
`;
CREATE TABLE `sys_user
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统用户id',
`username
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '用户名',
`cellphone
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '手机号码',
`password
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '密码',
`enabled
` int(1) NOT NULL DEFAULT 1 COMMENT '1:未启用；2：启用',
`state
` int(1) NOT NULL DEFAULT 2 COMMENT '1:未审核,2:审核通过,3:审核未通过',
`sex
` int(1) NOT NULL DEFAULT 1 COMMENT '1:男;2:女',
`department
` varchar(200) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`create_time
` datetime(0) NOT NULL COMMENT '创建时间',
`passport
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '证件照',
`userphoto
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '头像',
`mcb_id
` int(11) NULL DEFAULT NULL COMMENT '医联体id',
`sys_type
` int(1) NOT NULL DEFAULT 1 COMMENT '运营端用户类型（0：超管；1：平台运营用户；2：平台其他用户；3：医联体用户）',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统用户' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_user_permission
`;
CREATE TABLE `sys_user_permission
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统用户与权限id',
`per_id
` int(11) NOT NULL COMMENT '权限id',
`user_id
` int(11) NOT NULL COMMENT '系统用户Id',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 73 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统用户与权限' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_user_role
`;
CREATE TABLE `sys_user_role
` (
`id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统用户与角色id',
`user_id
` int(11) NOT NULL COMMENT '系统用户id',
`role_id
` int(11) NOT NULL COMMENT '系统角色id',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统用户与角色' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_users_temp
`;
CREATE TABLE `sys_users_temp
` (
`ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '用户ID',
`CELLPHONE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '用户电话',
`USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '用户名称',
`PASSWORD
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '用户密码',
`USERPHOTO
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '用户头像',
`LOGINTIME
` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
`SEX
` int(1) NULL DEFAULT 0 COMMENT '用户性别：0男，1女',
`ENABLED
` tinyint(1) NULL DEFAULT 1 COMMENT '是否启用：0不启用，1启用',
`WEIXIN
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信号',
`ADDR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`CITY
` int(11) NULL DEFAULT 0 COMMENT '市',
`PROVINCE
` int(11) NULL DEFAULT 0 COMMENT '省',
`DISTRICT
` int(11) NULL DEFAULT 0 COMMENT '区县',
`DEPARTMENT
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '所属部门',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '创建人ID',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`UPDATE_TIME
` datetime(0) NOT NULL COMMENT '修改时间',
`UPDATE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '修改人ID',
`IP
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆IP地址',
`TYPE` int(1) NULL DEFAULT 1 COMMENT '0:超级管理员 1:财务人员 2:金山运营 3:运维人员',
`increment_id
` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
PRIMARY KEY (`increment_id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统用户' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `version
`;
CREATE TABLE `version
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`version
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '版本号',
`create_time
` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`note
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '版本描述',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP VIEW IF EXISTS `casemanagementview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `casemanagementview
` AS select `c
`.`id
` AS `case_id
`,`p
`.`name
` AS `patient_name
`,`p
`.`sex
` AS `sex
`,`p
`.`age
` AS `age
`,`p
`.`phone
` AS `phone
`,concat
(`p
`.`province_name
`,`p
`.`city_name
`,`p
`.`district_name
`) AS `area
`,`c
`.`patient_no
` AS `patient_no
`,`c
`.`capsule_no
` AS `capsule_no
`,`c
`.`inspection_subjects
` AS `inspection_subjects
`,`c
`.`check_date
` AS `check_date
`,`c
`.`medica_history
` AS `medica_history
`,`c
`.`symptom_complaint
` AS `symptom_complaint
`,`c
`.`remarks
` AS `remarks
`,`c
`.`create_time
` AS `create_time
`,`c
`.`inspection_id
` AS `doctor_id
`,`c
`.`inspection_organization_id
` AS `inspection_organization_id
`,`c
`.`imcd_id
` AS `imcd_id
`,`c
`.`emcd_id
` AS `emcd_id
`,`c
`.`expert_organization_id
` AS `expert_organization_id
`,`c
`.`expert_id
` AS `expert_id
`,`c
`.`m_id
` AS `m_id
`,`cl
`.`check_no
` AS `check_no
`,`cl
`.`money
` AS `money
`,`er
`.`report_address
` AS `report_address
`,`imgf
`.`server_url
` AS `imfserver_url
`,`mr
`.`medicalrecord_address
` AS `medicalrecord_address
`,`mr
`.`medicalrecord_name
` AS `medicalrecord_name
`,`org
`.`name
` AS `org_name
`,`mcb
`.`name
` AS `mcb_name
` from (((((((`patient
` `p
` join `case` `c
` on((`c
`.`patient_id
` = `p
`.`id
`))) join `checklist
` `cl
` on(((`c
`.`id
` = `cl
`.`case_id
`) and (`cl
`.`status` = 10)))) join `endoscopicreport
` `er
` on((`c
`.`report_id
` = `er
`.`id
`))) join `imagefile
` `imgf
` on((`c
`.`file_id
` = `imgf
`.`id
`))) left join `medicalrecord
` `mr
` on((`cl
`.`check_no
` = `mr
`.`check_no
`))) left join `organization
` `org
` on((`c
`.`inspection_organization_id
` = `org
`.`id
`))) left join `medical_conjoined_body
` `mcb
` on(((`c
`.`imcd_id
` = `mcb
`.`id
`) or (`c
`.`emcd_id
` = `mcb
`.`id
`)))) group by `cl
`.`check_no
`;
DROP VIEW IF EXISTS `homeppage
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `homeppage
` AS select `ca
`.`inspection_subjects
` AS `inspection_subjects
`,`c
`.`status` AS `status`,`c
`.`pay_state
` AS `pay_state
`,`c
`.`create_time
` AS `c_create_time
`,`c
`.`check_no
` AS `check_no
`,`e
`.`audit_status
` AS `audit_status
`,`e
`.`create_time
` AS `e_create_time
`,`ca
`.`inspection_id
` AS `inspection_id
`,`ca
`.`inspection_organization_id
` AS `inspection_organization_id
`,`ca
`.`imcd_id
` AS `imcd_id
`,`ca
`.`emcd_id
` AS `emcd_id
`,`ca
`.`expert_organization_id
` AS `expert_organization_id
`,`c
`.`pay_type
` AS `pay_type
`,`c
`.`is_downreport
` AS `is_downreport
`,`c
`.`money
` AS `money
`,`ca
`.`expert_id
` AS `expert_id
` from ((`checklist
` `c
` join `case` `ca
` on((`c
`.`case_id
` = `ca
`.`id
`))) left join `endoscopicreport
` `e
` on((`c
`.`check_no
` = `e
`.`check_no
`)));
DROP VIEW IF EXISTS `intestinecountview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `intestinecountview
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y') AS `years
`,date_format
(`c
`.`create_time
`,'%c') AS `months
`,count(`c
`.`inspection_subjects
`) AS `intestine_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '小肠胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y'),date_format
(`c
`.`create_time
`,'%c'),`org
`.`name
`;
DROP VIEW IF EXISTS `intestinecountviewofreadorder
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `intestinecountviewofreadorder
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y') AS `years
`,date_format
(`c
`.`create_time
`,'%c') AS `months
`,count(`c
`.`inspection_subjects
`) AS `intestine_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '小肠胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y'),date_format
(`c
`.`create_time
`,'%c'),`ept
`.`username
`;
DROP VIEW IF EXISTS `intestinecountwithyearmonthdayview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `intestinecountwithyearmonthdayview
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y-%c-%d') AS `create_time
`,count(`c
`.`inspection_subjects
`) AS `intestine_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '小肠胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y%c%d'),`org
`.`name
`,`mcb
`.`name
`;
DROP VIEW IF EXISTS `intestinecountwithyearmonthdayviewofreadorder
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `intestinecountwithyearmonthdayviewofreadorder
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y-%c-%d') AS `create_time
`,count(`c
`.`inspection_subjects
`) AS `intestine_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '小肠胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y%c%d'),`ept
`.`username
`,`mcb
`.`name
`;
DROP VIEW IF EXISTS `intestinecountwithyearview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `intestinecountwithyearview
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y') AS `years
`,count(`c
`.`inspection_subjects
`) AS `intestine_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '小肠胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y'),`org
`.`name
`,`mcb
`.`name
`;
DROP VIEW IF EXISTS `intestinecountwithyearviewofreadorder
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `intestinecountwithyearviewofreadorder
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y') AS `years
`,count(`c
`.`inspection_subjects
`) AS `intestine_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '小肠胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y'),`ept
`.`username
`,`mcb
`.`name
`;
DROP VIEW IF EXISTS `onlinepayment
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `onlinepayment
` AS select `c
`.`id
` AS `id
`,`c
`.`check_no
` AS `check_no
`,`ca
`.`patient_no
` AS `patient_no
`,`p
`.`name
` AS `name
`,`c
`.`pay_type
` AS `pay_type
`,`c
`.`accept_account
` AS `accept_account
`,`c
`.`return_account
` AS `return_account
`,`c
`.`money
` AS `money
`,`c
`.`pay_date
` AS `pay_date
`,`c
`.`return_date
` AS `return_date
`,`c
`.`pay_state
` AS `pay_state
`,`d
`.`username
` AS `username
`,`ca
`.`inspection_id
` AS `inspection_id
`,`ca
`.`inspection_organization_id
` AS `inspection_organization_id
`,`ca
`.`imcd_id
` AS `imcd_id
`,`ca
`.`emcd_id
` AS `emcd_id
`,`ca
`.`expert_organization_id
` AS `expert_organization_id
`,`ca
`.`expert_id
` AS `expert_id
`,`c
`.`status` AS `status` from (((`checklist
` `c
` left join `patient
` `p
` on((`c
`.`patient_id
` = `p
`.`id
`))) left join `case` `ca
` on((`c
`.`case_id
` = `ca
`.`id
`))) left join `doctor
` `d
` on((`ca
`.`inspection_id
` = `d
`.`id
`)));
DROP VIEW IF EXISTS `operatehomepageview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `operatehomepageview
` AS select (select count(0) from `checklist
` where (`checklist
`.`status` = 3)) AS `to_check_checklist
`,(select count(0) from `checklist
` where (`checklist
`.`status` = 8)) AS `to_check_endoscopicreport
`,(select count(0) from `checklist
` where (`checklist
`.`pay_state
` = 1)) AS `to_pay_checklist
`,(select count(0) from `checklist
` where (`checklist
`.`pay_state
` = 3)) AS `to_refund_checklist
`,(select count(0) from `checklist
`) AS `total_num_checklist
`,(select count(0) from `checklist
` where (date_format
(`checklist
`.`create_time
`,'%Y%c%d') = date_format
(curdate
(),'%Y%c%d'))) AS `today_num_checklist
`,(select count(0) from `endoscopicreport
`) AS `total_num_endoscopicreport
`,(select count(0) from `endoscopicreport
` where (date_format
(`endoscopicreport
`.`create_time
`,'%Y%c%d') = date_format
(curdate
(),'%Y%c%d'))) AS `today_num_endoscopicreport
`;
DROP VIEW IF EXISTS `readordertoacceptview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `readordertoacceptview
` AS select `cl
`.`id
` AS `order_id
`,`cl
`.`status` AS `order_status
`,`cl
`.`assign_time
` AS `ordered_time
`,`cl
`.`order_no
` AS `order_no
`,`p
`.`name
` AS `name
`,`p
`.`sex
` AS `sex
`,`p
`.`age
` AS `age
`,`p
`.`phone
` AS `phone
`,concat
(`p
`.`province_name
`,`p
`.`city_name
`,`p
`.`district_name
`) AS `area
`,`c
`.`patient_no
` AS `patient_no
`,`c
`.`capsule_no
` AS `capsule_no
`,`c
`.`inspection_subjects
` AS `inspection_subjects
`,`c
`.`medica_history
` AS `medica_history
`,`c
`.`symptom_complaint
` AS `symptom_complaint
`,`c
`.`remarks
` AS `remarks
`,`c
`.`expert_id
` AS `expert_id
`,`c
`.`m_id
` AS `m_id
`,`cl
`.`create_time
` AS `inspected_time
`,`cl
`.`expected_time
` AS `expected_time
`,`cl
`.`check_no
` AS `check_no
`,`cl
`.`note
` AS `note
`,`imf
`.`local_url
` AS `local_url
`,`imf
`.`server_url
` AS `server_url
`,`imf
`.`fsize
` AS `fsize
`,`ept
`.`username
` AS `username
`,`ept
`.`cellphone
` AS `cellphone
`,`org
`.`name
` AS `organization_name
` from ((((((`patient
` `p
` join `case` `c
`) join `checklist
` `cl
`) join `imagefile
` `imf
`) join `operatingrecord
` `opr
`) join `expect
` `ept
`) join `organization
` `org
`) where ((`cl
`.`check_no
` = `imf
`.`check_no
`) and (`cl
`.`patient_id
` = `p
`.`id
`) and (`p
`.`id
` = `c
`.`patient_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`c
`.`inspection_organization_id
` = `org
`.`id
`)) group by `cl
`.`check_no
`;
DROP VIEW IF EXISTS `stomachcountview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `stomachcountview
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y') AS `years
`,date_format
(`c
`.`create_time
`,'%c') AS `months
`,count(`c
`.`inspection_subjects
`) AS `stomach_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '胃胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y'),date_format
(`c
`.`create_time
`,'%c'),`org
`.`name
`;
DROP VIEW IF EXISTS `stomachcountviewofreadorder
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `stomachcountviewofreadorder
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y') AS `years
`,date_format
(`c
`.`create_time
`,'%c') AS `months
`,count(`c
`.`inspection_subjects
`) AS `stomach_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '胃胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y'),date_format
(`c
`.`create_time
`,'%c'),`ept
`.`username
`;
DROP VIEW IF EXISTS `stomachcountwithyearmonthdayview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `stomachcountwithyearmonthdayview
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y-%c-%d') AS `create_time
`,count(`c
`.`inspection_subjects
`) AS `stomach_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '胃胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y%c%d'),`org
`.`name
`,`mcb
`.`name
`;
DROP VIEW IF EXISTS `stomachcountwithyearmonthdayviewofreadorder
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `stomachcountwithyearmonthdayviewofreadorder
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y-%c-%d') AS `create_time
`,count(`c
`.`inspection_subjects
`) AS `stomach_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '胃胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y%c%d'),`ept
`.`username
`,`mcb
`.`name
`;
DROP VIEW IF EXISTS `stomachcountwithyearview
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `stomachcountwithyearview
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y') AS `years
`,count(`c
`.`inspection_subjects
`) AS `stomach_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '胃胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y'),`org
`.`name
`,`mcb
`.`name
`;
DROP VIEW IF EXISTS `stomachcountwithyearviewofreadorder
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `stomachcountwithyearviewofreadorder
` AS select `mcb
`.`id
` AS `mcb_id
`,`mcb
`.`name
` AS `mcb_name
`,`org
`.`name
` AS `org_name
`,`ept
`.`username
` AS `expect_name
`,`ept
`.`id
` AS `expect_id
`,`c
`.`m_id
` AS `m_id
`,date_format
(`c
`.`create_time
`,'%Y') AS `years
`,count(`c
`.`inspection_subjects
`) AS `stomach_num
` from (((((`case` `c
` join `organization
` `org
`) join `medical_conjoined_body
` `mcb
`) join `expect
` `ept
`) join `checklist
` `cl
`) join `endoscopicreport
` `er
`) where ((`c
`.`inspection_subjects
` = '胃胶囊') and (`c
`.`imcd_id
` = `mcb
`.`id
`) and (`org
`.`id
` = `c
`.`inspection_organization_id
`) and (`c
`.`expert_id
` = `ept
`.`id
`) and (`cl
`.`case_id
` = `c
`.`id
`) and (`er
`.`check_no
` = `cl
`.`check_no
`) and (`er
`.`audit_status
` = 2)) group by date_format
(`c
`.`create_time
`,'%Y'),`ept
`.`username
`,`mcb
`.`name
`;
DROP PROCEDURE IF EXISTS `checklist_procedure
`;
delimiter ;;
CREATE PROCEDURE `checklist_procedure
`()
BEGIN
SELECT
olddb
.checklist
.CHECK_NO
FROM
olddb
.checklist
WHERE
olddb
.checklist
.CHECK_NO
IN (
SELECT
newdb
.checklist
.check_no
FROM
newdb
.checklist
);
DROP TABLE IF EXISTS newdb
.`multi_data_checkno
`;
CREATE TABLE newdb
.`multi_data_checkno
` (
`CHECK_NO
` VARCHAR(25) NOT NULL,
PRIMARY KEY(`CHECK_NO
`)
);
INSERT INTO newdb
.multi_data_checkno
(CHECK_NO
) SELECT a
.CHECK_NO
FROM (SELECT
olddb
.checklist
.CHECK_NO
FROM
olddb
.checklist
WHERE
olddb
.checklist
.CHECK_NO
IN (
SELECT
newdb
.checklist
.check_no
FROM
newdb
.checklist
)) AS a
;
UPDATE olddb
.checklist
SET olddb
.checklist
.CHECK_NO
=CONCAT
(olddb
.checklist
.CHECK_NO
,'a') WHERE olddb
.checklist
.CHECK_NO
IN (
SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.assigntemp
SET olddb
.assigntemp
.CHECK_NO
=CONCAT
(olddb
.assigntemp
.CHECK_NO
,'a') WHERE olddb
.assigntemp
.CHECK_NO
IN (
SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.checkpay
SET olddb
.checkpay
.CHECK_NO
= CONCAT
(olddb
.checkpay
.CHECK_NO
,'a') WHERE olddb
.checkpay
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.checkrefundtemp
SET olddb
.checkrefundtemp
.CHECK_NO
= CONCAT
(olddb
.checkrefundtemp
.CHECK_NO
,'a') WHERE olddb
.checkrefundtemp
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.endoscopicreport
SET olddb
.endoscopicreport
.CHECK_NO
= CONCAT
(olddb
.endoscopicreport
.CHECK_NO
,'a') WHERE olddb
.endoscopicreport
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.imagefile
SET olddb
.imagefile
.CHECK_NO
= CONCAT
(olddb
.imagefile
.CHECK_NO
,'a') WHERE olddb
.imagefile
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.imagedetail
SET olddb
.imagedetail
.CHECK_NO
= CONCAT
(olddb
.imagedetail
.CHECK_NO
,'a') WHERE olddb
.imagedetail
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.medicalrecord
SET olddb
.medicalrecord
.CHECK_NO
= CONCAT
(olddb
.medicalrecord
.CHECK_NO
,'a') WHERE olddb
.medicalrecord
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.operatingrecord
SET olddb
.operatingrecord
.CHECK_NO
= CONCAT
(olddb
.operatingrecord
.CHECK_NO
,'a') WHERE olddb
.operatingrecord
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.orderpay
SET olddb
.orderpay
.CHECK_NO
= CONCAT
(olddb
.orderpay
.CHECK_NO
,'a') WHERE olddb
.orderpay
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.readorder
SET olddb
.readorder
.CHECK_NO
= CONCAT
(olddb
.readorder
.CHECK_NO
,'a') WHERE olddb
.readorder
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
ALTER TABLE newdb
.`case` MODIFY patient_no
VARCHAR(20) NULL;
ALTER TABLE newdb
.`case` MODIFY inspection_subjects
VARCHAR(20) NULL;
ALTER TABLE newdb
.`case` MODIFY check_date
datetime NULL;
ALTER TABLE newdb
.`case` MODIFY patient_id
int NULL;
ALTER TABLE newdb
.`case` MODIFY inspection_id
int NULL;
ALTER TABLE newdb
.`case` MODIFY inspection_organization_id
int NULL;
ALTER TABLE newdb
.`case` MODIFY imcd_id
int NULL;
ALTER TABLE newdb
.patient
MODIFY name
VARCHAR(100) NULL;
ALTER TABLE newdb
.patient
MODIFY sex
int NULL;
ALTER TABLE newdb
.patient
MODIFY phone
VARCHAR(11) NULL;
ALTER TABLE newdb
.patient
MODIFY user_id
int NULL;
ALTER TABLE newdb
.patient
MODIFY organization_id
int NULL;
ALTER TABLE newdb
.patient
MODIFY m_c_d_id
int NULL;
ALTER TABLE newdb
.patient
MODIFY create_time
datetime NULL;
ALTER TABLE newdb
.patient
MODIFY city_name
VARCHAR(20) NULL;
ALTER TABLE newdb
.patient
MODIFY district_name
VARCHAR(20) NULL;
ALTER TABLE newdb
.patient
MODIFY province_name
VARCHAR(20) NULL;
SET @case_id_temp=1000000;
SET @patient_id_temp=1000000;
SET @user_id_temp = '';
SET @user_id_temp_int = '';
SET @patient_name_temp = '';
SET @organization_id_temp = '';
SET @check_item_temp = '';
SET @status_temp = '';
SET @is_pay_temp = '';
SET @patient_age_temp = '';
SET @patient_sex_temp = '';
SET @patient_phone_temp = '';
SET @patient_no_temp = '';
SET @check_date_temp = '';
SET @patient_describe_temp = '';
SET @is_doctor = 0;
SET @is_expert = 0;
SET @is_operator = '';
SET @capsule_no_temp = '';
SET @audit_id_temp = '';
SET @paytype_temp = '';
SET @medicalrecord_id = '';
INSERT newdb
.checklist
(ID
, check_no
,money
,pay_state
,create_time
, refund_reason
,audit_note
,upload_flag
,refund_name
, refund_date
,audit_time
,audit_name
,type)
SELECT olddb
.checklist
.ID
+ 1000000,olddb
.checklist
.CHECK_NO
,olddb
.checklist
.MONEY
,olddb
.checklist
.IS_PAY
+1,olddb
.checklist
.CREATE_TIME
,olddb
.checklist
.REFUND_REASON
,olddb
.checklist
.AUDIT_NOTE
,olddb
.checklist
.UPLOAD_FLAG
,olddb
.checklist
.REFUND_NAME
,olddb
.checklist
.REFUND_DATE
,olddb
.checklist
.AUDIT_TIME
,olddb
.checklist
.AUDIT_NAME
,olddb
.checklist
.TYPE
FROM olddb
.checklist
;
end
;;
delimiter ;
DROP TRIGGER IF EXISTS `checklist_trigger
`;
delimiter ;;
CREATE TRIGGER `checklist_trigger
` BEFORE
INSERT ON `checklist
` FOR EACH ROW BEGIN
DECLARE old_checklist_id
INT;
SET old_checklist_id
= NEW
.ID
- 1000000;
SET NEW
.case_id
= @case_id_temp;
INSERT INTO newdb
.case (id
) VALUES(@case_id_temp);
SET NEW
.patient_id
= @patient_id_temp;
INSERT INTO newdb
.patient
(id
) VALUES(@patient_id_temp);
SELECT USER_ID
INTO @user_id_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
SELECT increment_id
INTO @user_id_temp_int FROM doctortem
WHERE ID
= @user_id_temp;
UPDATE newdb
.case SET inspection_id
= @user_id_temp_int WHERE id
= @case_id_temp;
SELECT PATIENT_NAME
INTO @patient_name_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.patient
SET name
= @patient_name_temp WHERE id
= @patient_id_temp;
SELECT ORGANIZATIONID
INTO @organization_id_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET inspection_organization_id
= @organization_id_temp WHERE id
= @case_id_temp;
SELECT CHECK_ITEM
INTO @check_item_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET inspection_subjects
= @check_item_temp WHERE id
= @case_id_temp;
SELECT STATUS INTO @status_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
IF @status_temp < 5
THEN SET NEW
.status = @status_temp +1;
ELSEIF @status_temp = 5
THEN SET NEW
.status = 7;
ELSEIF @status_temp = 6
THEN SET NEW
.status = 10;
ELSEIF @status_temp = 7
THEN SET NEW
.status = 11;
END IF;
SELECT IS_PAY
INTO @is_pay_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
SET NEW
.pay_state
= @is_pay_temp+1;
SELECT PATIENT_AGE
INTO @patient_age_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.patient
SET age
= @patient_age_temp WHERE id
= @patient_id_temp;
SELECT PATIENT_SEX
INTO @patient_sex_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.patient
SET sex
= @patient_sex_temp WHERE id
= @patient_id_temp;
SELECT PATIENT_PHONE
INTO @patient_phone_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.patient
SET phone
= @patient_phone_temp WHERE id
= @patient_id_temp;
SELECT PATIENT_NO
INTO @patient_no_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET patient_no
= @patient_no_temp WHERE id
= @case_id_temp;
SELECT CHECK_DATE
INTO @check_date_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET check_date
= @check_date_temp WHERE id
= @case_id_temp;
SELECT PATIENT_DESCRIBE
INTO @patient_describe_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET symptom_complaint
= @patient_describe_temp WHERE id
= @case_id_temp;
SELECT increment_id
INTO @is_doctor FROM newdb
.doctortem
WHERE newdb
.doctortem
.ID
= (SELECT olddb
.checklist
.REFUND_ID
FROM olddb
.checklist
WHERE olddb
.checklist
.ID
= old_checklist_id
);
SELECT increment_id
INTO @is_expert FROM newdb
.experttemp
WHERE newdb
.experttemp
.ID
= (SELECT olddb
.checklist
.REFUND_ID
FROM olddb
.checklist
WHERE olddb
.checklist
.ID
= old_checklist_id
);
SELECT REFUND_NAME
INTO @is_operator FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
IF @is_doctor > 0
THEN SET NEW
.refund_id
= @is_doctor;
ELSEIF @is_expert > 0
THEN SET NEW
.refund_id
= @is_expert;
ELSEIF @is_operator = '超级管理员'
THEN SET NEW
.refund_id
= 1;
END IF;
SELECT CAPSULE_NO
INTO @capsule_no_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET capsule_no
= @capsule_no_temp WHERE id
= @case_id_temp;
SELECT increment_id
INTO @audit_id_temp FROM newdb
.sys_users_temp
WHERE newdb
.sys_users_temp
.ID
= (SELECT olddb
.checklist
.AUDIT_ID
FROM olddb
.checklist
WHERE olddb
.checklist
.ID
= old_checklist_id
);
SET NEW
.audit_id
= @audit_id_temp;
SELECT PAYTYPE
INTO @paytype FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
IF @paytype = 0
THEN SET NEW
.pay_type
= 1;
ELSEIF @paytype = 1
THEN SET NEW
.pay_type
= 3;
END IF;
SELECT MEDICALRECORD_ID
INTO @medicalrecord_id FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET medicalrecord_id
= @medicalrecord_id WHERE id
= @case_id_temp;
SET @case_id_temp = @case_id_temp + 1;
SET @patient_id_temp = @patient_id_temp + 1;
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS
= 1;
旧数据库结构
SET NAMES utf8mb4
;
SET FOREIGN_KEY_CHECKS
= 0;
DROP TABLE IF EXISTS `area
`;
CREATE TABLE `area
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '地区ID',
`PARENT_ID
` int(11) NOT NULL DEFAULT 0 COMMENT '地区父ID',
`CLASS_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '地区名称',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11109 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `assigntemp
`;
CREATE TABLE `assigntemp
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '指派记录流水表ID',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '指派人员ID',
`ORDER_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '订单号',
`CHECK_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '检查单号',
`EXPERT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '专家姓名',
`HOSPITAL
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '专家所属医院',
`COST
` decimal(10, 2) NULL DEFAULT NULL COMMENT '指派费用',
`CREATE_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '指派时间',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5228 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '指派记录流水表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checklist
`;
CREATE TABLE `checklist
` (
`ID
` int(10) NOT NULL AUTO_INCREMENT COMMENT '检查单ID',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '送检医生ID',
`CHECK_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '检查单号',
`PATIENT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '患者姓名',
`USER_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '送检医生姓名',
`HOSPITAL
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '医院',
`CHECK_ITEM
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查项目',
`MONEY
` decimal(10, 2) NULL DEFAULT NULL COMMENT '金额',
`STATUS` int(1) NOT NULL DEFAULT 0 COMMENT '检查单状态\r\n0待提交\r\n1已提交\r\n2待审核\r\n3待指派\r\n4未审核通过\r\n5已指派\r\n6已完成\r\n7已退单\r\n\r\n',
`IS_PAY
` int(1) NOT NULL DEFAULT 0 COMMENT '是否付款\r\n0待支付\r\n1已支付\r\n2待退款\r\n3已退款\r\n',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '建单时间',
`PATIENT_AGE
` int(3) NOT NULL COMMENT '年龄',
`PATIENT_SEX
` int(1) NOT NULL COMMENT '性别\r\n1男2女3未知',
`PATIENT_PHONE
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '患者电话',
`PATIENT_NO
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '门诊住院号',
`CHECK_DATE
` datetime(0) NOT NULL COMMENT '检查日期',
`PATIENT_DESCRIBE
` varchar(2000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '病人主述',
`DEPARTMENT
` varchar(60) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '科室',
`REFUND_REASON
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '退单原因',
`AUDIT_NOTE
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '审核备注',
`ORDER_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`UPLOAD_FLAG
` int(1) NULL DEFAULT NULL COMMENT '上传标志1上传中2上传完成',
`REFUND_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '退单人id',
`REFUND_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '退单人姓名',
`REFUND_DATE
` datetime(0) NULL DEFAULT NULL COMMENT '退单时间',
`CAPSULE_NO
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '胶囊序列号',
`AUDIT_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
`AUDIT_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '审核人ID',
`AUDIT_NAME
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '审核人姓名',
`RECIVE_DATA
` varchar(2500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '点击导入时对方传过来的数据',
`SEQUENCE_NUMBER
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '病人编号（与看片的系统的病人编号对应）',
`TYPE` int(1) NOT NULL DEFAULT 0 COMMENT '0:系统内创建；1：导入创建；2：一键导入（批量导入）',
`PAYTYPE
` int(11) NULL DEFAULT 0 COMMENT '支付方式：0线上支付；1线下支付',
`ORGANIZATIONID
` int(11) NULL DEFAULT 0 COMMENT '机构ID',
`MEDICALRECORD_ID
` int(11) NULL DEFAULT 0 COMMENT '病历ID',
PRIMARY KEY (`ID
`) USING BTREE,
INDEX `c_CHECK_NO
`(`CHECK_NO
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16935 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = 'checklist检查单' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checkpay
`;
CREATE TABLE `checkpay
` (
`ID
` int(23) NOT NULL AUTO_INCREMENT,
`CHECK_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`PATIENT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`MONEY
` decimal(10, 2) NULL DEFAULT NULL COMMENT '支付金额,退回金额',
`LEVEL` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '专家医生级别',
`EXPECTED_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '期望时间',
`NOTE
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '备注',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '操作员id',
`USER_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`CREATE_TIME
` datetime(0) NULL DEFAULT NULL,
`FLAG
` varchar(2) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '支付标志1已付款-1已退款,2退款中',
`ACCOUNT
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '支付帐户，退回帐户',
`ACCOUNT_TYPE
` varchar(2) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '帐户类别',
`ORDER_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`ACCEPT_ACCOUNT
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '支付时收款帐户,退款时的支付帐户',
`OLD_MONEY
` decimal(10, 2) NULL DEFAULT NULL COMMENT '原支付金额',
`WX_URL
` varchar(1000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信二维码or支付宝地址',
`TRANSACTION_ID
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信or支付宝transaction_id',
`FAILURE_REASON
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付失败原因',
`BATCH_NO
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付宝退款批次号',
`OUT_TRADE_NO
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '对应支付宝OUT_TRADE_NO',
`DOCTOR_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`DOCTOR_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5594 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '检查单支付表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `checkrefundtemp
`;
CREATE TABLE `checkrefundtemp
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '检查单申请退款记录ID',
`CHECK_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`ACCOUNT
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '退到帐户',
`ACCOUNT_TYPE
` varchar(2) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '帐户类别',
`ACCEPT_ACCOUNT
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '退款时的给钱帐户',
`MONEY
` decimal(10, 2) NULL DEFAULT NULL COMMENT '退回金额',
`OLD_MONEY
` decimal(10, 2) NULL DEFAULT NULL COMMENT '原支付金额',
`PATIENT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '患者姓名',
`LEVEL` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '专家医生级别',
`EXPECTED_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '期望时间',
`NOTE
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '备注',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '操作员id',
`USER_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '操作员姓名',
`CREATE_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '申请退款时间',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 56 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '检查单申请退款记录表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`comment` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '回馈',
`contact
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '联系方式',
`version
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '版本',
`client_type
` int(11) NULL DEFAULT NULL COMMENT '客户端类型 0:运营端 1:医生端 2:专家端',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `dictionarys
`;
CREATE TABLE `dictionarys
` (
`id
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`type` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`value` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`text` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `distributor
`;
CREATE TABLE `distributor
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '经销商ID',
`DEALER_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '经销商名',
`CONTACT_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '联系人姓名',
`CONTACT_PHONE
` varchar(11) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '联系人电话',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`ENABLED
` int(1) NOT NULL DEFAULT 1 COMMENT '是否启用：0不启用，1启用',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '经销商' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `doctor
`;
CREATE TABLE `doctor
` (
`ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医生ID',
`CELLPHONE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医生电话',
`USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '医生姓名',
`PASSWORD
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '医生密码',
`USERPHOTO
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '医生头像',
`LOGINTIME
` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
`SEX
` int(1) NULL DEFAULT 0 COMMENT '医生性别：0男，1女',
`WEIXIN
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信号',
`ALIPAY
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付宝账号',
`ADDR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`CITY
` int(11) NULL DEFAULT 0 COMMENT '市',
`PROVINCE
` int(11) NULL DEFAULT 0 COMMENT '省',
`DISTRICT
` int(11) NULL DEFAULT 0 COMMENT '区县',
`HOSPITAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '所属医院',
`PASSPORT
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照片',
`CHECK_LIST
` int(11) NULL DEFAULT 0 COMMENT '检查单总数',
`STATUS` int(1) NULL DEFAULT 1 COMMENT '0:未审核,1:审核通过,2:审核未通过 ',
`M_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '管理员id',
`M_USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '超级管理员' COMMENT '管理员姓名',
`MAC
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆mac地址',
`IP
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆IP地址',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '创建人ID',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`UPDATE_TIME
` datetime(0) NOT NULL COMMENT '修改时间',
`UPDATE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '修改人ID',
`PROFESSIONAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '医师/住院医师' COMMENT '类型：医师/住院医师，主治医师等',
`PAYTYPE
` int(11) NULL DEFAULT 0 COMMENT '支付方式：0线上支付；1线下支付',
`TYPE` int(11) NULL DEFAULT 1 COMMENT '医生类型0:机构管理员医生；1；普通医生',
`ORGANIZATIONID
` int(11) NULL DEFAULT 0 COMMENT '机构ID',
`ORGANIZATIONNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '机构名称',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '送检医生' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `endoscopicreport
`;
CREATE TABLE `endoscopicreport
` (
`ID
` int(10) NOT NULL AUTO_INCREMENT COMMENT '内镜报告ID',
`REPOT_NAME
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '报告文件名',
`ORDER_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '订单号',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '报告上传时间',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '上传报告的用户ID',
`CHECK_NO
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`AUDIT_STATUS
` int(1) NOT NULL DEFAULT 1 COMMENT '审核状态（1：通过；2：未通过）',
`REPORT_ADDRESS
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '报告地址',
`PATIENT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '患者姓名',
`PATIENT_AGE
` int(3) NULL DEFAULT 0 COMMENT '患者年龄',
`PATIENT_SEX
` int(1) NULL DEFAULT 1 COMMENT '患者性别：1：男；2：女；3：未知',
`CHECK_ITEM
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '检查项目',
`EXPERT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '专家姓名',
`AUDIT_CONTENT
` varchar(300) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '审核意见',
`STATUS` int(1) NULL DEFAULT 1 COMMENT '报告状态（1：报告待审核；2：报告已审核;3：报告未通过）',
`INSPECTION_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '送检医生ID',
`AUDIT_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '审核通过时间',
PRIMARY KEY (`ID
`) USING BTREE,
INDEX `en_AUDIT_TIME
`(`AUDIT_TIME
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5056 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '内镜报告表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `expert
`;
CREATE TABLE `expert
` (
`ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '专家ID',
`CELLPHONE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '专家电话',
`USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '专家姓名',
`PASSWORD
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '专家密码',
`USERPHOTO
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '专家头像',
`LOGINTIME
` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
`SEX
` int(1) NULL DEFAULT 0 COMMENT '专家性别：0男，1女',
`WEIXIN
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信号',
`ALIPAY
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '支付宝账号',
`ADDR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`CITY
` int(11) NULL DEFAULT 0 COMMENT '市',
`PROVINCE
` int(11) NULL DEFAULT 0 COMMENT '省',
`DISTRICT
` int(11) NULL DEFAULT 0 COMMENT '区县',
`HOSPITAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '所属医院',
`PASSPORT
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '证件照片',
`CHECK_LIST
` int(11) NULL DEFAULT 0 COMMENT '订单总数',
`STATUS` int(1) NULL DEFAULT 1 COMMENT '0:未审核,1:审核通过,2:审核未通过 ',
`M_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '管理员id',
`M_USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '超级管理员' COMMENT '管理员姓名',
`MAC
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆mac地址',
`IP
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆IP地址',
`PROFESSIONAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '医师/住院医师' COMMENT '类型：医师/住院医师，主治医师等',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '创建人ID',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`UPDATE_TIME
` datetime(0) NOT NULL COMMENT '修改时间',
`UPDATE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '修改人ID',
`EXPERT_TYPE
` int(1) NULL DEFAULT 0 COMMENT '0:读片医师;1:省级专家;2:顶级专家',
`STOMACH_FEE
` double(10, 2) NULL DEFAULT 0.00 COMMENT '胃胶囊费用',
`INTESTINES_FEE
` double(10, 2) NULL DEFAULT 0.00 COMMENT '小肠胶囊费用',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '读片专家' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `film
`;
CREATE TABLE `film
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`price
` double(10, 2) NULL DEFAULT 0.00,
`create_date
` datetime(0) NULL,
`type` int(1) NOT NULL DEFAULT 0 COMMENT '0:胃胶囊；1：小肠胶囊',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `film_history
`;
CREATE TABLE `film_history
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`name
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`update_date
` datetime(0) NULL DEFAULT NULL,
`operate
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`result
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 80 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `generaterule
`;
CREATE TABLE `generaterule
` (
`ID
` int(10) NOT NULL AUTO_INCREMENT,
`NO` varchar(3) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`TYPE` varchar(1) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '1 检查单 2 订单 ',
`END_TIME
` datetime(0) NOT NULL COMMENT '结束时间',
`CREATE_TIME
` datetime(0) NOT NULL,
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '检查订,订单等生成规则' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `imagedetail
`;
CREATE TABLE `imagedetail
` (
`ID
` int(10) NOT NULL AUTO_INCREMENT,
`CHECK_NO
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`FILE_NAME
` varchar(500) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '文件名',
`CREATE_TIME
` datetime(0) NULL DEFAULT NULL,
`UPLOAD_FLAG
` int(1) NULL DEFAULT 0 COMMENT '上传标志0未上传1上传中2已上传',
`DOWNLOAD_FLAG
` int(1) NULL DEFAULT 0 COMMENT '下载标志0未下载1下载中2已下载',
`MD5_NAME
` text CHARACTER SET utf8
COLLATE utf8_general_ci
NULL,
`UPLOAD_PERCENT
` double(5, 2) NULL DEFAULT 0.00 COMMENT '上传百分比',
`DOWNLOAD_PERCENT
` double(5, 2) NULL DEFAULT 0.00 COMMENT '下载百分比',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14655 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '影像文件明细' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `imagefile
`;
CREATE TABLE `imagefile
` (
`ID
` int(10) NOT NULL AUTO_INCREMENT,
`CHECK_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '用户id(送检医生id)',
`LOCAL_URL
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '本地路径',
`SERVER_URL
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '服务端路径',
`UPLOAD_FLAG
` int(1) NULL DEFAULT 0 COMMENT '上传标志0未上传1上传中2已上传',
`LOCK` int(1) NULL DEFAULT 0 COMMENT '状态锁1上锁0未锁',
`DOWNLOAD_FLAG
` int(1) NULL DEFAULT 0 COMMENT '下载标志0未下载2已下载',
`READ_USERID
` int(10) NULL DEFAULT NULL COMMENT '读片专家id',
`TOTAL_FILE
` int(10) NULL DEFAULT NULL COMMENT '文件总数',
`DOWNLOAD_FILE
` int(10) NULL DEFAULT 0 COMMENT '下载文件数',
`UPLOAD_FILE
` int(10) NULL DEFAULT 0 COMMENT '上传文件数()',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`LAST_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '最近操作时间',
`FSIZE
` int(11) NULL DEFAULT 0 COMMENT '文件大小',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5823 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '送检影像文件' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `medicalrecord
`;
CREATE TABLE `medicalrecord
` (
`ID
` int(10) NOT NULL AUTO_INCREMENT COMMENT '病历ID',
`MEDICALRECORD_NAME
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '病历文件名',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '病历上传时间',
`CHECK_NO
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '0' COMMENT '检查单号',
`MEDICALRECORD_ADDRESS
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '报告地址',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 27 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '病历表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `message
`;
CREATE TABLE `message
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户消息ID',
`SEND_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '发送人ID',
`RECEIVE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '接收人ID',
`CONTENT
` varchar(200) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '消息内容',
`STATUS` int(1) NULL DEFAULT 0 COMMENT '消息状态（0:未读状态；1：已读状态）',
`CREATE_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '消息发送时间',
`TYPE` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '消息名称（即消息类型）',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 22206 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '用户消息' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `notice
`;
CREATE TABLE `notice
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '公告ID',
`TITLE
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '公告标题',
`CONTENT
` varchar(1000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '公告内容',
`TYPE` int(1) NOT NULL DEFAULT 0 COMMENT '查看人员 0：医生；1：专家；2：全部',
`CREATE_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`STATUS` int(1) NOT NULL DEFAULT 0 COMMENT '公告状态，0:未发布；1：已发布',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 67 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '公告表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `noticetemp
`;
CREATE TABLE `noticetemp
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '公告流水表ID',
`TITLE
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '公告标题',
`CONTENT
` varchar(1000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '公告内容',
`CREATE_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`STATUS` int(1) NOT NULL DEFAULT 0 COMMENT '公告状态，0:未读；1：已读',
`RECEIVE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '接收人ID',
`NOTICE_ID
` int(11) NOT NULL COMMENT '公告表ID',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4929 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '公告流水表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `operatingrecord
`;
CREATE TABLE `operatingrecord
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '操作记录ID',
`OPERATION_STATUS
` int(2) NOT NULL COMMENT '操作状态ID',
`OPERSTION_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '操作名称',
`OPERSTION_RESULT
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '操作结果',
`CONTENT
` varchar(1000) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '操作描述',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '操作人ID',
`USER_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '操作人姓名',
`ROLE_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '操作人角色',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '操作时间',
`CHECK_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '检查单号',
`ORDER_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '订单号',
`TYPE` int(1) NOT NULL DEFAULT 0 COMMENT '操作记录类型（0：运行查询该检查单的全部记录；1：送检医生查询检查单记录；2：读片医生查询订单记录）',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 46761 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '操作记录表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `operationstatus
`;
CREATE TABLE `operationstatus
` (
`id
` int(10) NOT NULL AUTO_INCREMENT COMMENT '操作状态ID',
`name
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '操作名称',
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '操作状态表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `orderpay
`;
CREATE TABLE `orderpay
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '付款记录ID',
`ORDER_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '订单号',
`CHECK_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`PATIENT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '患者姓名',
`EXPERT_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '专家ID',
`EXPERT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '专家姓名',
`PAY_METHID
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '支付方式（1：支付宝；2：微信支付;3:线下支付）',
`PAY_ACCOUNT
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '支付账户',
`PAY_COST
` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '支付金额',
`PAY_STATUS
` int(1) NOT NULL DEFAULT 1 COMMENT '支付状态(1：已支付；0：未支付）',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '支付时间',
`ACCEPT_ACCOUNT
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '收款账户',
`HOSPITAL
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '收款方所在医院',
`ACCEPT_NAME
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '收款发真实姓名',
`BATCH_NO
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '支付批次号',
`FAILURE_REASON
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '失败原因',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`USER_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 301 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `organization
`;
CREATE TABLE `organization
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '机构ID',
`ORGANIZATIONNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '医院名称',
`SECTIONNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '科室名称',
`CITY
` int(11) NULL DEFAULT 0 COMMENT '市',
`PROVINCE
` int(11) NULL DEFAULT 0 COMMENT '省',
`DISTRICT
` int(11) NULL DEFAULT 0 COMMENT '区县',
`ADDR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '医生姓名(机构管理员信息)',
`SEX
` int(1) NULL DEFAULT 0 COMMENT '医生性别：0男，1女',
`CELLPHONE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '联系电话',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '创建人ID',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`UPDATE_TIME
` datetime(0) NOT NULL COMMENT '修改时间',
`UPDATE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '修改人ID',
`PROFESSIONAL
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '医师/住院医师' COMMENT '类型：医师/住院医师，主治医师等',
`CHECK_LIST
` int(11) NULL DEFAULT 0 COMMENT '检查单总数',
`STATUS` int(1) NULL DEFAULT 0 COMMENT '0:未审核,1:审核通过,2:审核未通过 ',
`M_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '管理员id',
`M_USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '超级管理员' COMMENT '管理员姓名',
`USERPHOTO
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '机构资质照片',
`PAYTYPE
` int(11) NULL DEFAULT 0 COMMENT '支付方式：0线上支付；1线下支付',
`REGISTERED_DOCTOR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '已注册医生',
`DISTRIBUTOR_ID
` int(11) NOT NULL DEFAULT 0 COMMENT '所属经销商ID',
`DISTRIBUTOR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '所属经销商',
`ENABLED
` int(1) NOT NULL DEFAULT 1 COMMENT '是否启用：0不启用，1启用',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 195 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '机构信息' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `persistent_logins
`;
CREATE TABLE `persistent_logins
` (
`username
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`series
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`token
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`last_used
` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`series
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `readorder
`;
CREATE TABLE `readorder
` (
`ID
` int(10) NOT NULL AUTO_INCREMENT COMMENT '读片订单ID',
`ORDER_NO
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '订单号',
`PATIENT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '患者姓名',
`PATIENT_AGE
` int(3) NOT NULL DEFAULT 0 COMMENT '患者年龄',
`PATIENT_SEX
` int(1) NOT NULL DEFAULT 1 COMMENT '患者性别：1：男；2：女；3：未知',
`CHECK_ITEM
` varchar(50) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查项目',
`READING_COST
` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '读片费用',
`ORDER_STATUS
` int(2) NOT NULL DEFAULT 1 COMMENT '订单状态(1：待接受；2：读片中；3：报告未通过；4：报告待审核；5：报告已通过；6：已退单)',
`PAYMENT_STATUS
` int(2) NOT NULL DEFAULT 1 COMMENT '支付状态(1：未支付；2：已支付)',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '创建人ID',
`CHECK_NO
` varchar(30) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '检查单号',
`CANCLE_REASON
` varchar(200) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '取消原因',
`CANCLE_NAME
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '退单人',
`REPORT_ID
` int(10) NULL DEFAULT 0 COMMENT '报告ID',
`STATUS` int(2) NOT NULL DEFAULT 0 COMMENT '订单是否处理的状态（0：待处理；1：已完结；-1：已退单）',
`CANCLE_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '取消读片订单时间',
`EXPECTED_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '期望报告时间',
`INSPECTION_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '送检医生ID',
`INSPECTION_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '送检医生姓名',
`EXPERT_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '专家ID',
`EXPERT_NAME
` varchar(32) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '专家姓名',
`EXPERT_HOSPITL
` varchar(100) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '专家所属医院',
`READING_DEMAND
` varchar(300) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '读片需求',
`CAPSULE_NO
` varchar(64) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '胶囊序列号',
`AUDIT_TIME
` datetime(0) NULL DEFAULT NULL COMMENT '审核通过时间',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5135 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '读片订单' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sms_code
`;
CREATE TABLE `sms_code
` (
`sms_id
` int(11) NOT NULL AUTO_INCREMENT,
`email
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`mobile
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`send_date
` datetime(0) NULL DEFAULT NULL,
`send_type
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`sms_code
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
`verify
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL,
PRIMARY KEY (`sms_id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 500 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_permission
`;
CREATE TABLE `sys_permission
` (
`PER_ID
` int(11) NOT NULL AUTO_INCREMENT COMMENT '权限ID\r\n',
`PRE_DESC
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '权限描述',
`PRE_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '权限名称',
PRIMARY KEY (`PER_ID
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 67 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '权限点表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_roles
`;
CREATE TABLE `sys_roles
` (
`ROLE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '角色ID',
`ROLE_DESC
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '角色描述',
`ROLE_NAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '角色名称',
`ROLE_PRE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '角色对应的默认权限ID',
PRIMARY KEY (`ROLE_ID
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统角色' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_users
`;
CREATE TABLE `sys_users
` (
`ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '用户ID',
`CELLPHONE
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '用户电话',
`USERNAME
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '用户名称',
`PASSWORD
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL COMMENT '用户密码',
`USERPHOTO
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT 'resource/2016/9/21/1474426194859.png' COMMENT '用户头像',
`LOGINTIME
` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
`SEX
` int(1) NULL DEFAULT 0 COMMENT '用户性别：0男，1女',
`ENABLED
` tinyint(1) NULL DEFAULT 1 COMMENT '是否启用：0不启用，1启用',
`WEIXIN
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '微信号',
`ADDR
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '详细地址',
`CITY
` int(11) NULL DEFAULT 0 COMMENT '市',
`PROVINCE
` int(11) NULL DEFAULT 0 COMMENT '省',
`DISTRICT
` int(11) NULL DEFAULT 0 COMMENT '区县',
`DEPARTMENT
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT NULL COMMENT '所属部门',
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '创建人ID',
`CREATE_TIME
` datetime(0) NOT NULL COMMENT '创建时间',
`UPDATE_TIME
` datetime(0) NOT NULL COMMENT '修改时间',
`UPDATE_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '修改人ID',
`IP
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '' COMMENT '最后登陆IP地址',
`TYPE` int(1) NULL DEFAULT 1 COMMENT '0:超级管理员 1:财务人员 2:金山运营 3:运维人员',
PRIMARY KEY (`ID
`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统用户' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_users_permission
`;
CREATE TABLE `sys_users_permission
` (
`ID
` int(11) NOT NULL AUTO_INCREMENT,
`USER_ID
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL DEFAULT '' COMMENT '用户ID',
`PER_ID
` int(11) NOT NULL DEFAULT 0 COMMENT '权限ID',
`STATUS` int(1) NOT NULL DEFAULT 0 COMMENT '0:所属角色权限,1:添加额外权限',
PRIMARY KEY (`ID
`) USING BTREE,
INDEX `USER_ID
`(`USER_ID
`) USING BTREE,
INDEX `perId
`(`PER_ID
`) USING BTREE,
CONSTRAINT `perId
` FOREIGN KEY (`PER_ID
`) REFERENCES `sys_permission
` (`PER_ID
`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `userId
` FOREIGN KEY (`USER_ID
`) REFERENCES `sys_users
` (`ID
`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 320 CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统用户权限表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `sys_users_roles
`;
CREATE TABLE `sys_users_roles
` (
`userId
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
`roleId
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NOT NULL,
INDEX `FK_dx6x5dps4g32q7lg0gt1kgkt1
`(`roleId
`) USING BTREE,
INDEX `FK_ni9mgrn4ajs55f12gn7c4mf3t
`(`userId
`) USING BTREE,
CONSTRAINT `FK_dx6x5dps4g32q7lg0gt1kgkt1
` FOREIGN KEY (`roleId
`) REFERENCES `sys_roles
` (`ROLE_ID
`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_ni9mgrn4ajs55f12gn7c4mf3t
` FOREIGN KEY (`userId
`) REFERENCES `sys_users
` (`ID
`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '系统-用户-角色表' ROW_FORMAT
= Compact
;
DROP TABLE IF EXISTS `version
`;
CREATE TABLE `version
` (
`id
` int(11) NOT NULL AUTO_INCREMENT,
`version
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`CREATE_TIME
` datetime(0) NULL DEFAULT NULL,
`url
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`note
` varchar(255) CHARACTER SET utf8
COLLATE utf8_general_ci
NULL DEFAULT '',
`type` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id
`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 54 CHARACTER SET = utf8
COLLATE = utf8_general_ci ROW_FORMAT
= Compact
;
DROP VIEW IF EXISTS `checklistpayment
`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY
DEFINER VIEW `checklistpayment
` AS select `r
`.`ORDER_NO
` AS `orderNo
`,`c
`.`CHECK_NO
` AS `checkNo
`,`c
`.`PATIENT_NAME
` AS `patientname
`,`d
`.`USERNAME
` AS `dusername
`,`d
`.`HOSPITAL
` AS `dhospital
`,`c
`.`MONEY
` AS `money
`,`c
`.`CHECK_ITEM
` AS `checkitem
`,if((`p
`.`ACCOUNT_TYPE
` = 1),'支付宝','微信支付') AS `paytype
`,`e
`.`USERNAME
` AS `eusername
`,`e
`.`HOSPITAL
` AS `ehospital
`,`r
`.`READING_COST
` AS `readcost
`,`c
`.`CREATE_TIME
` AS `createTime
`,`en
`.`AUDIT_TIME
` AS `auditTime
` from (((((`doctor
` `d
` join `expert
` `e
`) join `checklist
` `c
`) join `readorder
` `r
`) join `checkpay
` `p
`) join `endoscopicreport
` `en
`) where ((`c
`.`CHECK_NO
` = `r
`.`CHECK_NO
`) and (`r
`.`INSPECTION_ID
` = `d
`.`ID
`) and (`r
`.`EXPERT_ID
` = `e
`.`ID
`) and (`r
`.`CHECK_NO
` = `c
`.`CHECK_NO
`) and (`c
`.`CHECK_NO
` = `p
`.`CHECK_NO
`) and (`en
`.`CHECK_NO
` = `c
`.`CHECK_NO
`) and (`r
`.`ORDER_STATUS
` = 5) and (`r
`.`PAYMENT_STATUS
` = 1) and (year(`en
`.`AUDIT_TIME
`) = year(now())));
SET FOREIGN_KEY_CHECKS
= 1;
编写分析文档
现以检查单表checklist为例针对每个字段进行分析。以具体的业务结合表结构设计为依据对新-旧数据库进行对比分析分析须得出具体的每一个字段的处理方式及处理步骤
编写SQL
针对每个字段的分析结果统一编写存储过程及触发器
术语
newdb新数据库名
olddb旧数据库名
SQL脚本
存储过程
DROP PROCEDURE IF EXISTS newdb
.checklist_procedure
;
CREATE PROCEDURE newdb
.checklist_procedure
() BEGIN
SELECT
olddb
.checklist
.CHECK_NO
FROM
olddb
.checklist
WHERE
olddb
.checklist
.CHECK_NO
IN (
SELECT
newdb
.checklist
.check_no
FROM
newdb
.checklist
);
DROP TABLE IF EXISTS newdb
.`multi_data_checkno
`;
CREATE TABLE newdb
.`multi_data_checkno
` (
`CHECK_NO
` VARCHAR(25) NOT NULL,
PRIMARY KEY(`CHECK_NO
`)
);
INSERT INTO newdb
.multi_data_checkno
(CHECK_NO
) SELECT a
.CHECK_NO
FROM (SELECT
olddb
.checklist
.CHECK_NO
FROM
olddb
.checklist
WHERE
olddb
.checklist
.CHECK_NO
IN (
SELECT
newdb
.checklist
.check_no
FROM
newdb
.checklist
)) AS a
;
UPDATE olddb
.checklist
SET olddb
.checklist
.CHECK_NO
=CONCAT
(olddb
.checklist
.CHECK_NO
,'a') WHERE olddb
.checklist
.CHECK_NO
IN (
SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.assigntemp
SET olddb
.assigntemp
.CHECK_NO
=CONCAT
(olddb
.assigntemp
.CHECK_NO
,'a') WHERE olddb
.assigntemp
.CHECK_NO
IN (
SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.checkpay
SET olddb
.checkpay
.CHECK_NO
= CONCAT
(olddb
.checkpay
.CHECK_NO
,'a') WHERE olddb
.checkpay
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.checkrefundtemp
SET olddb
.checkrefundtemp
.CHECK_NO
= CONCAT
(olddb
.checkrefundtemp
.CHECK_NO
,'a') WHERE olddb
.checkrefundtemp
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.endoscopicreport
SET olddb
.endoscopicreport
.CHECK_NO
= CONCAT
(olddb
.endoscopicreport
.CHECK_NO
,'a') WHERE olddb
.endoscopicreport
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.imagefile
SET olddb
.imagefile
.CHECK_NO
= CONCAT
(olddb
.imagefile
.CHECK_NO
,'a') WHERE olddb
.imagefile
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.imagedetail
SET olddb
.imagedetail
.CHECK_NO
= CONCAT
(olddb
.imagedetail
.CHECK_NO
,'a') WHERE olddb
.imagedetail
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.medicalrecord
SET olddb
.medicalrecord
.CHECK_NO
= CONCAT
(olddb
.medicalrecord
.CHECK_NO
,'a') WHERE olddb
.medicalrecord
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.operatingrecord
SET olddb
.operatingrecord
.CHECK_NO
= CONCAT
(olddb
.operatingrecord
.CHECK_NO
,'a') WHERE olddb
.operatingrecord
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.orderpay
SET olddb
.orderpay
.CHECK_NO
= CONCAT
(olddb
.orderpay
.CHECK_NO
,'a') WHERE olddb
.orderpay
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
UPDATE olddb
.readorder
SET olddb
.readorder
.CHECK_NO
= CONCAT
(olddb
.readorder
.CHECK_NO
,'a') WHERE olddb
.readorder
.CHECK_NO
IN (SELECT CHECK_NO
FROM multi_data_checkno
);
ALTER TABLE newdb
.`case` MODIFY patient_no
VARCHAR(20) NULL;
ALTER TABLE newdb
.`case` MODIFY inspection_subjects
VARCHAR(20) NULL;
ALTER TABLE newdb
.`case` MODIFY check_date
datetime NULL;
ALTER TABLE newdb
.`case` MODIFY patient_id
int NULL;
ALTER TABLE newdb
.`case` MODIFY inspection_id
int NULL;
ALTER TABLE newdb
.`case` MODIFY inspection_organization_id
int NULL;
ALTER TABLE newdb
.`case` MODIFY imcd_id
int NULL;
ALTER TABLE newdb
.patient
MODIFY name
VARCHAR(100) NULL;
ALTER TABLE newdb
.patient
MODIFY sex
int NULL;
ALTER TABLE newdb
.patient
MODIFY phone
VARCHAR(11) NULL;
ALTER TABLE newdb
.patient
MODIFY user_id
int NULL;
ALTER TABLE newdb
.patient
MODIFY organization_id
int NULL;
ALTER TABLE newdb
.patient
MODIFY m_c_d_id
int NULL;
ALTER TABLE newdb
.patient
MODIFY create_time
datetime NULL;
ALTER TABLE newdb
.patient
MODIFY city_name
VARCHAR(20) NULL;
ALTER TABLE newdb
.patient
MODIFY district_name
VARCHAR(20) NULL;
ALTER TABLE newdb
.patient
MODIFY province_name
VARCHAR(20) NULL;
SET @case_id_temp=1000000;
SET @patient_id_temp=1000000;
SET @user_id_temp = '';
SET @user_id_temp_int = '';
SET @patient_name_temp = '';
SET @organization_id_temp = '';
SET @check_item_temp = '';
SET @status_temp = '';
SET @is_pay_temp = '';
SET @patient_age_temp = '';
SET @patient_sex_temp = '';
SET @patient_phone_temp = '';
SET @patient_no_temp = '';
SET @check_date_temp = '';
SET @patient_describe_temp = '';
SET @is_doctor = 0;
SET @is_expert = 0;
SET @is_operator = '';
SET @capsule_no_temp = '';
SET @audit_id_temp = '';
SET @paytype_temp = '';
SET @medicalrecord_id = '';
INSERT newdb
.checklist
(ID
, check_no
,money
,pay_state
,create_time
, refund_reason
,audit_note
,upload_flag
,refund_name
, refund_date
,audit_time
,audit_name
,type)
SELECT olddb
.checklist
.ID
+ 1000000,olddb
.checklist
.CHECK_NO
,olddb
.checklist
.MONEY
,olddb
.checklist
.IS_PAY
+1,olddb
.checklist
.CREATE_TIME
,olddb
.checklist
.REFUND_REASON
,olddb
.checklist
.AUDIT_NOTE
,olddb
.checklist
.UPLOAD_FLAG
,olddb
.checklist
.REFUND_NAME
,olddb
.checklist
.REFUND_DATE
,olddb
.checklist
.AUDIT_TIME
,olddb
.checklist
.AUDIT_NAME
,olddb
.checklist
.TYPE
FROM olddb
.checklist
;
end;
触发器
DROP TRIGGER IF EXISTS newdb
.checklist_trigger
;
CREATE TRIGGER newdb
.checklist_trigger BEFORE
INSERT ON newdb
.checklist
FOR EACH ROW
BEGIN
DECLARE old_checklist_id
INT;
SET old_checklist_id
= NEW
.ID
- 1000000;
SET NEW
.case_id
= @case_id_temp;
INSERT INTO newdb
.case (id
) VALUES(@case_id_temp);
SET NEW
.patient_id
= @patient_id_temp;
INSERT INTO newdb
.patient
(id
) VALUES(@patient_id_temp);
SELECT USER_ID
INTO @user_id_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
SELECT increment_id
INTO @user_id_temp_int FROM doctortem
WHERE ID
= @user_id_temp;
UPDATE newdb
.case SET inspection_id
= @user_id_temp_int WHERE id
= @case_id_temp;
SELECT PATIENT_NAME
INTO @patient_name_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.patient
SET name
= @patient_name_temp WHERE id
= @patient_id_temp;
SELECT ORGANIZATIONID
INTO @organization_id_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET inspection_organization_id
= @organization_id_temp WHERE id
= @case_id_temp;
SELECT CHECK_ITEM
INTO @check_item_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET inspection_subjects
= @check_item_temp WHERE id
= @case_id_temp;
SELECT STATUS INTO @status_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
IF @status_temp < 5
THEN SET NEW
.status = @status_temp +1;
ELSEIF @status_temp = 5
THEN SET NEW
.status = 7;
ELSEIF @status_temp = 6
THEN SET NEW
.status = 10;
ELSEIF @status_temp = 7
THEN SET NEW
.status = 11;
END IF;
SELECT IS_PAY
INTO @is_pay_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
SET NEW
.pay_state
= @is_pay_temp+1;
SELECT PATIENT_AGE
INTO @patient_age_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.patient
SET age
= @patient_age_temp WHERE id
= @patient_id_temp;
SELECT PATIENT_SEX
INTO @patient_sex_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.patient
SET sex
= @patient_sex_temp WHERE id
= @patient_id_temp;
SELECT PATIENT_PHONE
INTO @patient_phone_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.patient
SET phone
= @patient_phone_temp WHERE id
= @patient_id_temp;
SELECT PATIENT_NO
INTO @patient_no_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET patient_no
= @patient_no_temp WHERE id
= @case_id_temp;
SELECT CHECK_DATE
INTO @check_date_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET check_date
= @check_date_temp WHERE id
= @case_id_temp;
SELECT PATIENT_DESCRIBE
INTO @patient_describe_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET symptom_complaint
= @patient_describe_temp WHERE id
= @case_id_temp;
SELECT increment_id
INTO @is_doctor FROM newdb
.doctortem
WHERE newdb
.doctortem
.ID
= (SELECT olddb
.checklist
.REFUND_ID
FROM olddb
.checklist
WHERE olddb
.checklist
.ID
= old_checklist_id
);
SELECT increment_id
INTO @is_expert FROM newdb
.experttemp
WHERE newdb
.experttemp
.ID
= (SELECT olddb
.checklist
.REFUND_ID
FROM olddb
.checklist
WHERE olddb
.checklist
.ID
= old_checklist_id
);
SELECT REFUND_NAME
INTO @is_operator FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
IF @is_doctor > 0
THEN SET NEW
.refund_id
= @is_doctor;
ELSEIF @is_expert > 0
THEN SET NEW
.refund_id
= @is_expert;
ELSEIF @is_operator = '超级管理员'
THEN SET NEW
.refund_id
= 1;
END IF;
SELECT CAPSULE_NO
INTO @capsule_no_temp FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET capsule_no
= @capsule_no_temp WHERE id
= @case_id_temp;
SELECT increment_id
INTO @audit_id_temp FROM newdb
.sys_users_temp
WHERE newdb
.sys_users_temp
.ID
= (SELECT olddb
.checklist
.AUDIT_ID
FROM olddb
.checklist
WHERE olddb
.checklist
.ID
= old_checklist_id
);
SET NEW
.audit_id
= @audit_id_temp;
SELECT PAYTYPE
INTO @paytype FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
IF @paytype = 0
THEN SET NEW
.pay_type
= 1;
ELSEIF @paytype = 1
THEN SET NEW
.pay_type
= 3;
END IF;
SELECT MEDICALRECORD_ID
INTO @medicalrecord_id FROM olddb
.checklist
WHERE ID
= old_checklist_id
;
UPDATE newdb
.case SET medicalrecord_id
= @medicalrecord_id WHERE id
= @case_id_temp;
SET @case_id_temp = @case_id_temp + 1;
SET @patient_id_temp = @patient_id_temp + 1;
END;
执行迁移
1、执行存储过程脚本 ，在执行触发器脚本。顺序不能换。 2、运行存储过程 3、验证数据迁移结果
附（补充）
检查单支付表checkpay
DROP PROCEDURE IF EXISTS newdb
.checkpay_procedure
;
CREATE PROCEDURE newdb
.checkpay_procedure
()
BEGIN
DECLARE done
BOOLEAN DEFAULT 0;
DECLARE var_account_type
VARCHAR(8);
DECLARE var_check_no
VARCHAR(64);
DECLARE checkpay_cursor
CURSOR FOR SELECT
olddb
.checkpay
.ACCOUNT_TYPE
AS account_type
,
olddb
.checkpay
.CHECK_NO
AS check_no
FROM olddb
.checkpay
;
DECLARE CONTINUE HANDLER FOR SQLSTATE
'02000' SET done
= 1;
OPEN checkpay_cursor
;
REPEAT
FETCH checkpay_cursor
INTO var_account_type
,var_check_no
;
IF var_account_type
= 1
THEN UPDATE newdb
.checklist
SET account_type
= 2 WHERE newdb
.checklist
.check_no
= var_check_no
;
ELSEIF var_account_type
= 2
THEN UPDATE newdb
.checklist
SET account_type
= 1 WHERE newdb
.checklist
.check_no
= var_check_no
;
END IF;
UNTIL done
END REPEAT;
CLOSE checkpay_cursor
;
UPDATE newdb
.checklist cl
INNER JOIN olddb
.checkpay cp
ON cl
.check_no
= cp
.CHECK_NO
SET cl
.return_money
= cp
.MONEY
,cl
.`level`=cp
.`LEVEL`,cl
.expected_time
=cp
.EXPECTED_TIME
,cl
.account
=cp
.ACCOUNT
,cl
.accept_account
=cp
.ACCEPT_ACCOUNT
,
cl
.wx_url
= cp
.WX_URL
,cl
.transaction_id
= cp
.TRANSACTION_ID
,cl
.failure_reason
= cp
.FAILURE_REASON
,cl
.batch_no
= cp
.BATCH_NO
,cl
.out_trade_no
= cp
.OUT_TRADE_NO
;
END;
