-- ============================================================================ -- 门店运营与医药工作台菜单优化脚本 -- -- 目标： -- 1. 管理端(saasmgnui)新增门店运营管理子菜单（业绩/交接班/接待/考勤） -- 2. 药师端(saaspharmacistui)新增"用药咨询工作台"菜单 -- 3. 医生端(saasdoctorui)新增"在线问诊工作台"菜单 -- 4. 精简药师/医生端可见菜单，隐藏管理类功能 -- -- 使用说明： -- 主库执行 PART A（sys_menu） -- 租户库逐租户执行 PART B（tenant_sys_menu） -- menu_id 使用 31000~31020 区段，不与现有 29xxx 冲突 -- ============================================================================ -- ###################################################################### -- PART A：主库 sys_menu -- ###################################################################### -- A.1 门店运营管理父菜单 REPLACE INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES (31000, '门店运营', 0, 90, '/storeOperation', '', 'M', 'el-icon-s-shop', '0', '0', 0, 0, NULL, 'admin', NOW(), 'Store Operation Hub'), -- A.2 门店运营子菜单 (31001, '员工业绩', 31000, 1, 'staffPerformance', 'store/storeStaffPerformance/index', 'C', 'el-icon-s-marketing', '0', '0', 0, 0, 'store:operation:performance', 'admin', NOW(), NULL), (31002, '交接班管理', 31000, 2, 'shiftManage', 'store/storeShiftManage/index', 'C', 'el-icon-sort', '0', '0', 0, 0, 'store:operation:shift', 'admin', NOW(), NULL), (31003, '到店接待', 31000, 3, 'receptionManage', 'store/storeReceptionManage/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:operation:reception', 'admin', NOW(), NULL), (31004, '考勤打卡', 31000, 4, 'attendanceManage', 'store/storeAttendanceManage/index', 'C', 'el-icon-time', '0', '0', 0, 0, 'store:operation:attendance', 'admin', NOW(), NULL), (31005, '企微客户', 31000, 5, 'qwCustomer', 'store/storeQwCustomer/index', 'C', 'el-icon-chat-line-round', '0', '0', 0, 0, 'store:operation:qwCustomer', 'admin', NOW(), 'Store QW Customer'), (31006, '采购建议', 31000, 6, 'purchaseSuggestion', 'store/purchaseSuggestion/index', 'C', 'el-icon-shopping-bag-2', '0', '0', 0, 0, 'store:operation:purchase', 'admin', NOW(), 'Purchase Suggestion'), (31007, '挂号分诊', 31000, 7, 'clinicRegistration', 'store/clinicRegistration/index', 'C', 'el-icon-document-add', '0', '0', 0, 0, 'store:clinic:registration', 'admin', NOW(), 'Clinic Registration'), (31008, '就诊叫号', 31000, 8, 'clinicQueue', 'store/clinicQueue/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:clinic:queue', 'admin', NOW(), 'Clinic Queue'), (31009, '医生排班', 31000, 9, 'doctorSchedule', 'store/doctorSchedule/index', 'C', 'el-icon-date', '0', '0', 0, 0, 'store:clinic:schedule', 'admin', NOW(), 'Doctor Schedule'), -- A.3 药师工作台菜单 (31010, '用药咨询工作台', 0, 50, '/imWorkbench', 'pharmacist/imWorkbench/index', 'C', 'el-icon-chat-dot-round', '0', '0', 0, 0, 'pharmacist:imworkbench', 'admin', NOW(), 'Pharmacist IM Workbench'), (31011, '药品销售统计', 0, 51, '/drugStatistics', 'pharmacist/drugStatistics/index', 'C', 'el-icon-pie-chart', '0', '0', 0, 0, 'pharmacist:drugstats', 'admin', NOW(), 'Drug Sales Statistics'), (31012, '慢病用药周期', 0, 52, '/drugCycle', 'pharmacist/drugCycle/index', 'C', 'el-icon-alarm-clock', '0', '0', 0, 0, 'pharmacist:drugcycle', 'admin', NOW(), 'Drug Cycle Dashboard'), -- A.4 医生工作台菜单 (31015, '在线问诊工作台', 0, 50, '/consultWorkbench', 'doctor/consultWorkbench/index', 'C', 'el-icon-video-camera', '0', '0', 0, 0, 'doctor:consultworkbench', 'admin', NOW(), 'Doctor Consult Workbench'), (31016, '患者健康档案', 0, 51, '/patientRecord', 'doctor/patientRecord/index', 'C', 'el-icon-document', '0', '0', 0, 0, 'doctor:patientrecord', 'admin', NOW(), 'Patient Health Record'), (31017, '随访管理', 0, 52, '/followUp', 'doctor/followUp/index', 'C', 'el-icon-calendar', '0', '0', 0, 0, 'doctor:followup', 'admin', NOW(), 'Follow-up Management'), (31018, '病历管理', 0, 53, '/medicalRecord', 'doctor/medicalRecord/index', 'C', 'el-icon-tickets', '0', '0', 0, 0, 'doctor:medicalrecord', 'admin', NOW(), 'Medical Record Management'); -- A.4b 企微客户菜单授权 INSERT IGNORE INTO sys_role_menu (role_id, menu_id) SELECT r.role_id, 31005 FROM sys_role_menu r WHERE r.menu_id = 31000 AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = r.role_id AND x.menu_id = 31005); -- A.5 同步到 company_menu（销售端子集） INSERT IGNORE INTO company_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark) SELECT s.menu_id, s.menu_name, s.parent_id, s.order_num, s.path, s.component, s.is_frame, s.is_cache, s.menu_type, s.visible, s.status, s.perms, s.icon, s.create_by, s.create_time, c.company_id, s.remark FROM sys_menu s CROSS JOIN (SELECT DISTINCT company_id FROM company_menu WHERE company_id IS NOT NULL) c WHERE s.menu_id IN (31000, 31001, 31002, 31003, 31004, 31005, 31006, 31007, 31008, 31009) AND NOT EXISTS (SELECT 1 FROM company_menu x WHERE x.menu_id = s.menu_id AND x.company_id = c.company_id); -- A.6 补全角色授权（门店运营菜单授权给门店管理员角色） INSERT IGNORE INTO sys_role_menu (role_id, menu_id) SELECT r.role_id, s.menu_id FROM sys_role_menu r CROSS JOIN sys_menu s WHERE r.menu_id = 31000 AND s.parent_id = 31000 AND s.menu_type = 'C' AND s.visible = '0' AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = r.role_id AND x.menu_id = s.menu_id); -- A.7 药师端菜单精简：隐藏管理类菜单（设置 visible=1 隐藏） -- 以下菜单对药师角色隐藏（billing/saas/system/monitor/lobster管理功能等） -- 通过 sys_role_menu 删除药师角色对这些菜单的关联 -- 注意：此操作不影响管理端角色，仅清理药师/医生角色不需要的菜单关联 -- 如需执行，请取消注释并替换 :pharmacistRoleId / :doctorRoleId 为实际角色ID -- DELETE FROM sys_role_menu WHERE role_id = :pharmacistRoleId AND menu_id IN ( -- SELECT menu_id FROM sys_menu WHERE path IN ( -- 'billing','saas','system','monitor','fastGpt','qdrant','sop', -- 'lobster','tenant','tool','tag','moduleUsage','taskStatistics', -- 'sensitive','callRecord','aiSipCall','aicall','aiob','aiChatQuality' -- ) -- ); -- DELETE FROM sys_role_menu WHERE role_id = :doctorRoleId AND menu_id IN ( -- SELECT menu_id FROM sys_menu WHERE path IN ( -- 'billing','saas','system','monitor','fastGpt','qdrant','sop', -- 'lobster','tenant','tool','tag','moduleUsage','taskStatistics', -- 'sensitive','callRecord','aiSipCall','aicall','aiob','aiChatQuality', -- 'shop','store','supplier','supplychain','marketing','adv' -- ) -- ); -- ###################################################################### -- PART B：租户库 tenant_sys_menu -- ###################################################################### REPLACE INTO tenant_sys_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES (31000, '门店运营', 0, 90, '/storeOperation', '', 'M', 'el-icon-s-shop', '0', '0', 0, 0, NULL, 'admin', NOW(), 'Store Operation Hub'), (31001, '员工业绩', 31000, 1, 'staffPerformance', 'store/storeStaffPerformance/index', 'C', 'el-icon-s-marketing', '0', '0', 0, 0, 'store:operation:performance', 'admin', NOW(), NULL), (31002, '交接班管理', 31000, 2, 'shiftManage', 'store/storeShiftManage/index', 'C', 'el-icon-sort', '0', '0', 0, 0, 'store:operation:shift', 'admin', NOW(), NULL), (31003, '到店接待', 31000, 3, 'receptionManage', 'store/storeReceptionManage/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:operation:reception', 'admin', NOW(), NULL), (31004, '考勤打卡', 31000, 4, 'attendanceManage', 'store/storeAttendanceManage/index', 'C', 'el-icon-time', '0', '0', 0, 0, 'store:operation:attendance', 'admin', NOW(), NULL), (31005, '企微客户', 31000, 5, 'qwCustomer', 'store/storeQwCustomer/index', 'C', 'el-icon-chat-line-round', '0', '0', 0, 0, 'store:operation:qwCustomer', 'admin', NOW(), 'Store QW Customer'), (31006, '采购建议', 31000, 6, 'purchaseSuggestion', 'store/purchaseSuggestion/index', 'C', 'el-icon-shopping-bag-2', '0', '0', 0, 0, 'store:operation:purchase', 'admin', NOW(), 'Purchase Suggestion'), (31007, '挂号分诊', 31000, 7, 'clinicRegistration', 'store/clinicRegistration/index', 'C', 'el-icon-document-add', '0', '0', 0, 0, 'store:clinic:registration', 'admin', NOW(), 'Clinic Registration'), (31008, '就诊叫号', 31000, 8, 'clinicQueue', 'store/clinicQueue/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:clinic:queue', 'admin', NOW(), 'Clinic Queue'), (31009, '医生排班', 31000, 9, 'doctorSchedule', 'store/doctorSchedule/index', 'C', 'el-icon-date', '0', '0', 0, 0, 'store:clinic:schedule', 'admin', NOW(), 'Doctor Schedule'), (31010, '用药咨询工作台', 0, 50, '/imWorkbench', 'pharmacist/imWorkbench/index', 'C', 'el-icon-chat-dot-round', '0', '0', 0, 0, 'pharmacist:imworkbench', 'admin', NOW(), 'Pharmacist IM Workbench'), (31011, '药品销售统计', 0, 51, '/drugStatistics', 'pharmacist/drugStatistics/index', 'C', 'el-icon-pie-chart', '0', '0', 0, 0, 'pharmacist:drugstats', 'admin', NOW(), 'Drug Sales Statistics'), (31012, '慢病用药周期', 0, 52, '/drugCycle', 'pharmacist/drugCycle/index', 'C', 'el-icon-alarm-clock', '0', '0', 0, 0, 'pharmacist:drugcycle', 'admin', NOW(), 'Drug Cycle Dashboard'), (31015, '在线问诊工作台', 0, 50, '/consultWorkbench', 'doctor/consultWorkbench/index', 'C', 'el-icon-video-camera', '0', '0', 0, 0, 'doctor:consultworkbench', 'admin', NOW(), 'Doctor Consult Workbench'), (31016, '患者健康档案', 0, 51, '/patientRecord', 'doctor/patientRecord/index', 'C', 'el-icon-document', '0', '0', 0, 0, 'doctor:patientrecord', 'admin', NOW(), 'Patient Health Record'), (31017, '随访管理', 0, 52, '/followUp', 'doctor/followUp/index', 'C', 'el-icon-calendar', '0', '0', 0, 0, 'doctor:followup', 'admin', NOW(), 'Follow-up Management'), (31018, '病历管理', 0, 53, '/medicalRecord', 'doctor/medicalRecord/index', 'C', 'el-icon-tickets', '0', '0', 0, 0, 'doctor:medicalrecord', 'admin', NOW(), 'Medical Record Management'); -- 同步 tenant_company_menu REPLACE INTO tenant_company_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES (31000, '门店运营', 0, 90, '/storeOperation', '', 'M', 'el-icon-s-shop', '0', '0', 0, 0, NULL, 'admin', NOW(), 'Store Operation Hub'), (31001, '员工业绩', 31000, 1, 'staffPerformance', 'store/storeStaffPerformance/index', 'C', 'el-icon-s-marketing', '0', '0', 0, 0, 'store:operation:performance', 'admin', NOW(), NULL), (31002, '交接班管理', 31000, 2, 'shiftManage', 'store/storeShiftManage/index', 'C', 'el-icon-sort', '0', '0', 0, 0, 'store:operation:shift', 'admin', NOW(), NULL), (31003, '到店接待', 31000, 3, 'receptionManage', 'store/storeReceptionManage/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:operation:reception', 'admin', NOW(), NULL), (31004, '考勤打卡', 31000, 4, 'attendanceManage', 'store/storeAttendanceManage/index', 'C', 'el-icon-time', '0', '0', 0, 0, 'store:operation:attendance', 'admin', NOW(), NULL), (31005, '企微客户', 31000, 5, 'qwCustomer', 'store/storeQwCustomer/index', 'C', 'el-icon-chat-line-round', '0', '0', 0, 0, 'store:operation:qwCustomer', 'admin', NOW(), 'Store QW Customer'), (31006, '采购建议', 31000, 6, 'purchaseSuggestion', 'store/purchaseSuggestion/index', 'C', 'el-icon-shopping-bag-2', '0', '0', 0, 0, 'store:operation:purchase', 'admin', NOW(), 'Purchase Suggestion'), (31007, '挂号分诊', 31000, 7, 'clinicRegistration', 'store/clinicRegistration/index', 'C', 'el-icon-document-add', '0', '0', 0, 0, 'store:clinic:registration', 'admin', NOW(), 'Clinic Registration'), (31008, '就诊叫号', 31000, 8, 'clinicQueue', 'store/clinicQueue/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:clinic:queue', 'admin', NOW(), 'Clinic Queue'), (31009, '医生排班', 31000, 9, 'doctorSchedule', 'store/doctorSchedule/index', 'C', 'el-icon-date', '0', '0', 0, 0, 'store:clinic:schedule', 'admin', NOW(), 'Doctor Schedule'); -- 同步 tenant_company_menu 诊所模块 REPLACE INTO tenant_company_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES (31007, '挂号分诊', 31000, 7, 'clinicRegistration', 'store/clinicRegistration/index', 'C', 'el-icon-document-add', '0', '0', 0, 0, 'store:clinic:registration', 'admin', NOW(), 'Clinic Registration'), (31008, '就诊叫号', 31000, 8, 'clinicQueue', 'store/clinicQueue/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:clinic:queue', 'admin', NOW(), 'Clinic Queue'), (31009, '医生排班', 31000, 9, 'doctorSchedule', 'store/doctorSchedule/index', 'C', 'el-icon-date', '0', '0', 0, 0, 'store:clinic:schedule', 'admin', NOW(), 'Doctor Schedule'); -- ###################################################################### -- PART C：DDL 用药反馈表 -- ###################################################################### CREATE TABLE IF NOT EXISTS med_feedback ( id BIGINT AUTO_INCREMENT PRIMARY KEY, patient_id BIGINT NOT NULL COMMENT '患者ID', drug_name VARCHAR(200) NOT NULL COMMENT '药品名称', effect_rating TINYINT DEFAULT 3 COMMENT '疗效评价: 3显效 2有效 1无效', has_adverse_reaction TINYINT DEFAULT 0 COMMENT '是否有不良反应: 0无 1有', feedback_content TEXT COMMENT '反馈内容', doctor_id BIGINT COMMENT '记录医生ID', doctor_name VARCHAR(100) COMMENT '记录医生姓名', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_patient (patient_id), INDEX idx_doctor (doctor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用药反馈记录表'; -- ###################################################################### -- PART D：DDL 诊所模块表 -- ###################################################################### CREATE TABLE IF NOT EXISTS clinic_registration ( id BIGINT AUTO_INCREMENT PRIMARY KEY, store_id BIGINT NOT NULL COMMENT '门店ID', patient_name VARCHAR(100) NOT NULL COMMENT '患者姓名', patient_phone VARCHAR(20) COMMENT '患者手机号', patient_gender TINYINT DEFAULT 1 COMMENT '性别 0女 1男', patient_age INT COMMENT '年龄', reg_type TINYINT DEFAULT 0 COMMENT '挂号类型 0普通 1专家 2急诊', department VARCHAR(100) COMMENT '科室', doctor_id BIGINT COMMENT '医生ID', doctor_name VARCHAR(100) COMMENT '医生姓名', symptom TEXT COMMENT '主诉症状', reg_fee DECIMAL(10,2) DEFAULT 0 COMMENT '挂号费', queue_no INT DEFAULT 0 COMMENT '排队号', status TINYINT DEFAULT 0 COMMENT '状态 0待就诊 1就诊中 2已完成 3已退号', reg_time DATETIME COMMENT '挂号时间', visit_time DATETIME COMMENT '就诊时间', remark VARCHAR(500) COMMENT '备注', create_by VARCHAR(64) DEFAULT '' COMMENT '创建者', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_by VARCHAR(64) DEFAULT '' COMMENT '更新者', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_store_date (store_id, DATE(reg_time)), INDEX idx_status (status), INDEX idx_doctor (doctor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='诊所挂号表'; CREATE TABLE IF NOT EXISTS doctor_schedule ( id BIGINT AUTO_INCREMENT PRIMARY KEY, store_id BIGINT NOT NULL COMMENT '门店ID', doctor_id BIGINT COMMENT '医生ID', doctor_name VARCHAR(100) NOT NULL COMMENT '医生姓名', schedule_date DATE NOT NULL COMMENT '排班日期', shift TINYINT DEFAULT 0 COMMENT '班次 0上午 1下午 2全天', max_patients INT DEFAULT 20 COMMENT '最大接诊量', current_patients INT DEFAULT 0 COMMENT '已挂号数', status TINYINT DEFAULT 0 COMMENT '状态 0可挂号 1已满 2停诊', department VARCHAR(100) COMMENT '科室', remark VARCHAR(500) COMMENT '备注', create_by VARCHAR(64) DEFAULT '', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_by VARCHAR(64) DEFAULT '', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_store_date (store_id, schedule_date), INDEX idx_doctor (doctor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='医生排班表'; CREATE TABLE IF NOT EXISTS clinic_billing ( id BIGINT AUTO_INCREMENT PRIMARY KEY, store_id BIGINT NOT NULL COMMENT '门店ID', registration_id BIGINT COMMENT '挂号ID', patient_name VARCHAR(100) NOT NULL COMMENT '患者姓名', patient_phone VARCHAR(20) COMMENT '患者手机号', doctor_name VARCHAR(100) COMMENT '接诊医生', diagnose VARCHAR(500) COMMENT '诊断', reg_fee DECIMAL(10,2) DEFAULT 0 COMMENT '挂号费', exam_fee DECIMAL(10,2) DEFAULT 0 COMMENT '检查费', drug_fee DECIMAL(10,2) DEFAULT 0 COMMENT '药品费', other_fee DECIMAL(10,2) DEFAULT 0 COMMENT '其他费用', total_amount DECIMAL(10,2) DEFAULT 0 COMMENT '总金额', pay_method TINYINT DEFAULT 0 COMMENT '支付方式 0现金 1微信 2支付宝 3医保 4银行卡', status TINYINT DEFAULT 0 COMMENT '状态 0待支付 1已支付 2已退款', billing_time DATETIME COMMENT '结算时间', remark VARCHAR(500) COMMENT '备注', create_by VARCHAR(64) DEFAULT '', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_by VARCHAR(64) DEFAULT '', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_store (store_id), INDEX idx_registration (registration_id), INDEX idx_billing_date (DATE(billing_time)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='诊所收费结算表';