员工到院转化记录表.sql
6.95 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
-- =============================================================================
-- 员工到院转化登记 lq_member_arrival_record
-- 说明：
-- 1. 主键 F_Id 须由应用层 YitIdHelper.NextId().ToString() 生成。
-- 2. 独立模块，只读 lq_khxx/BASE_USER/组织/门店，不联动会员主档及其他主业务。
-- 3. F_RecordKey 有效时为 storeId_memberId_yyyyMMdd，作废置空；唯一索引保证并发不重复。
-- 4. 幂等：CREATE TABLE IF NOT EXISTS + 索引通过 information_schema 判断后创建。
-- =============================================================================
CREATE TABLE IF NOT EXISTS `lq_member_arrival_record` (
`F_Id` varchar(50) NOT NULL COMMENT '主键',
`F_RecordNo` varchar(32) NOT NULL COMMENT '登记编号（对外展示，唯一）',
`F_RecordKey` varchar(128) DEFAULT NULL COMMENT '有效记录唯一键：门店_会员_yyyyMMdd，作废时置空',
`F_MemberId` varchar(50) NOT NULL COMMENT '会员ID（lq_khxx.F_Id）',
`F_MemberName` varchar(128) DEFAULT NULL COMMENT '会员姓名快照',
`F_MemberMobile` varchar(32) DEFAULT NULL COMMENT '会员手机号快照',
`F_SourceOrganizeId` varchar(50) DEFAULT NULL COMMENT '来源组织ID（登记人当前组织快照）',
`F_SourceOrganizeName` varchar(200) DEFAULT NULL COMMENT '来源组织名称快照',
`F_SourceMdid` varchar(50) DEFAULT NULL COMMENT '来源门店ID（登记人当前门店快照）',
`F_SourceMdName` varchar(200) DEFAULT NULL COMMENT '来源门店名称快照',
`F_ArrivalType` int NOT NULL COMMENT '到院类型：1售前 2售后',
`F_ArrivalTime` datetime NOT NULL COMMENT '到院时间',
`F_ConsultationStatus` int NOT NULL DEFAULT 0 COMMENT '见诊状态：0待确认 1是 2否',
`F_ConsultationTime` datetime DEFAULT NULL COMMENT '见诊状态确认时间',
`F_DealStatus` int NOT NULL DEFAULT 0 COMMENT '成交状态：0待确认 1是 2否',
`F_DealTime` datetime DEFAULT NULL COMMENT '成交状态确认时间',
`F_RecorderUserId` varchar(50) DEFAULT NULL COMMENT '登记人用户ID',
`F_RecorderUserName` varchar(128) DEFAULT NULL COMMENT '登记人姓名',
`F_LastModifyUserId` varchar(50) DEFAULT NULL COMMENT '最后修改人用户ID',
`F_LastModifyUserName` varchar(128) DEFAULT NULL COMMENT '最后修改人姓名',
`F_Remark` varchar(500) DEFAULT NULL COMMENT '备注',
`F_IsEffective` int NOT NULL DEFAULT 1 COMMENT '是否有效：1有效 0作废',
`F_VoidReason` varchar(500) DEFAULT NULL COMMENT '作废原因',
`F_VoidUserId` varchar(50) DEFAULT NULL COMMENT '作废操作人ID',
`F_VoidUserName` varchar(128) DEFAULT NULL COMMENT '作废操作人姓名',
`F_VoidTime` datetime DEFAULT NULL COMMENT '作废时间',
`F_Version` int NOT NULL DEFAULT 1 COMMENT '乐观锁版本号',
`F_CreateTime` datetime DEFAULT NULL COMMENT '创建时间',
`F_UpdateTime` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`F_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工到院转化登记';
-- 唯一索引：登记编号
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'UK_lq_member_arrival_record_No'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_member_arrival_record` ADD UNIQUE KEY `UK_lq_member_arrival_record_No` (`F_RecordNo`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 唯一索引：有效记录键（可空，作废后可重新登记）
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'UK_lq_member_arrival_record_RecordKey'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_member_arrival_record` ADD UNIQUE KEY `UK_lq_member_arrival_record_RecordKey` (`F_RecordKey`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：到院时间 + 有效标记（统计/列表）
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_ArrivalTime_Effective'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_ArrivalTime_Effective` (`F_ArrivalTime`, `F_IsEffective`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：来源组织 + 到院时间（权限过滤列表）
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_SourceOrg_ArrivalTime'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_SourceOrg_ArrivalTime` (`F_SourceOrganizeId`, `F_ArrivalTime`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：来源门店 + 到院时间
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_SourceMd_ArrivalTime'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_SourceMd_ArrivalTime` (`F_SourceMdid`, `F_ArrivalTime`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：会员 + 到院时间
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_Member_ArrivalTime'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_Member_ArrivalTime` (`F_MemberId`, `F_ArrivalTime`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：登记人 + 到院时间（手机端我的列表）
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_Recorder_ArrivalTime'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_Recorder_ArrivalTime` (`F_RecorderUserId`, `F_ArrivalTime`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：到院类型 + 见诊/成交状态（筛选）
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_Type_Status'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_Type_Status` (`F_ArrivalType`, `F_ConsultationStatus`, `F_DealStatus`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;