MedicalInsurance/Scripts/Create_PreInProcess_Analysis_Log.sql

-- 事前事中分析日志表
CREATE TABLE BS_PreInProcessAnalysis_Log (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    -- 基础信息
    CreateTime DATETIME NOT NULL COMMENT '创建时间',
    DeptCode VARCHAR(30) COMMENT '科室编码',
    DeptName VARCHAR(100) COMMENT '科室名称',
    OperatorCode VARCHAR(30) COMMENT '操作员编码',
    OperatorName VARCHAR(100) COMMENT '操作员姓名',
    
    -- 病人信息
    PatientID VARCHAR(30) COMMENT '病人ID',
    PatientName VARCHAR(100) COMMENT '病人姓名',
    PatientCardID VARCHAR(50) COMMENT '病人卡号/身份证号',
    
    -- 业务信息
    BusinessType VARCHAR(20) COMMENT '业务分类',
    ItemName VARCHAR(200) COMMENT '项目名称',
    ViolationCategory VARCHAR(50) COMMENT '违规分类',
    LimitType VARCHAR(50) COMMENT '限制类型',
    NextOperation VARCHAR(50) COMMENT '后续操作',
    
    -- 金额信息
    Quantity DECIMAL(16,4) COMMENT '数量',
    Amount DECIMAL(16,2) COMMENT '金额',
    
    -- 交易信息
    TradeCode VARCHAR(10) NOT NULL COMMENT '交易代码(3101,3102,3103)',
    TradeName VARCHAR(100) NOT NULL COMMENT '交易名称',
    TriggerScene VARCHAR(3) COMMENT '触发场景',
    
    -- 违规详细信息
    ViolationID VARCHAR(50) COMMENT '违规标识',
    ViolationRuleID VARCHAR(50) COMMENT '规则ID',
    ViolationRuleName VARCHAR(200) COMMENT '规则名称',
    ViolationContent VARCHAR(500) COMMENT '违规内容',
    ViolationAmount DECIMAL(16,2) COMMENT '违规金额',
    ViolationEvidence VARCHAR(500) COMMENT '违规依据',
    ViolationSeverity VARCHAR(3) COMMENT '严重程度',
    
    -- 医院信息
    HospitalDr INT NOT NULL COMMENT '医院ID',
    InterfaceDr INT NOT NULL COMMENT '接口ID',
    
    -- 统计标识
    IsExported INT DEFAULT 0 COMMENT '是否已导出(0:未导出,1:已导出)',
    ExportTime DATETIME COMMENT '导出时间',
    
    -- 创建人信息
    Creator VARCHAR(100) COMMENT '创建人',
    CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
    UpdateDate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
    
    INDEX idx_CreateTime (CreateTime),
    INDEX idx_PatientID (PatientID),
    INDEX idx_OperatorCode (OperatorCode),
    INDEX idx_TradeCode (TradeCode),
    INDEX idx_HospitalInterface (HospitalDr, InterfaceDr),
    INDEX idx_IsExported (IsExported)
) COMMENT='事前事中分析日志表';

-- 创建违规明细日志表
CREATE TABLE BS_PreInProcessAnalysis_Detail_Log (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    LogID INT NOT NULL COMMENT '主日志ID',
    
    -- 违规明细信息
    ViolationDetailID VARCHAR(50) COMMENT '违规明细标识',
    OrderID VARCHAR(50) COMMENT '处方(医嘱)标识',
    ViolationItemType VARCHAR(3) COMMENT '违规明细类型',
    ViolationDetailAmount DECIMAL(16,2) COMMENT '违规金额',
    
    -- 医嘱/处方信息
    OrderNO VARCHAR(30) COMMENT '处方号',
    OrderType VARCHAR(3) COMMENT '医嘱行为',
    HilistCode VARCHAR(50) COMMENT '医保目录代码',
    HilistName VARCHAR(50) COMMENT '医保目录名称',
    Spec VARCHAR(100) COMMENT '规格',
    OrderCount DECIMAL(16,4) COMMENT '数量',
    OrderUnitPrice DECIMAL(16,2) COMMENT '单价',
    OrderTotalAmount DECIMAL(16,2) COMMENT '总费用',
    
    -- 创建信息
    CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
    
    FOREIGN KEY (LogID) REFERENCES BS_PreInProcessAnalysis_Log(ID) ON DELETE CASCADE,
    
    INDEX idx_LogID (LogID),
    INDEX idx_OrderID (OrderID),
    INDEX idx_HilistCode (HilistCode)
) COMMENT='事前事中分析明细日志表';

-- 创建反馈日志表
CREATE TABLE BS_PreInProcessAnalysis_Feedback_Log (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    LogID INT NOT NULL COMMENT '关联的违规日志ID',
    
    -- 反馈信息
    FeedbackType VARCHAR(6) COMMENT '反馈类型(1:事前,2:事中)',
    ProcessMethod VARCHAR(20) COMMENT '处理方式',
    ProcessReason VARCHAR(500) COMMENT '处理原因',
    
    -- 反馈时间
    FeedbackTime DATETIME COMMENT '反馈时间',
    OperatorCode VARCHAR(30) COMMENT '反馈操作员编码',
    OperatorName VARCHAR(100) COMMENT '反馈操作员姓名',
    
    -- 创建信息
    CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
    
    FOREIGN KEY (LogID) REFERENCES BS_PreInProcessAnalysis_Log(ID) ON DELETE CASCADE,
    
    INDEX idx_LogID (LogID),
    INDEX idx_FeedbackType (FeedbackType)
) COMMENT='事前事中分析反馈日志表';