2016-04-11.sql
10.5 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
drop table if exists smUsDataItem;
/*系统帐户数据项限制*/
create table smUsDataItem /* 可编辑范围,可见范围的条件 是保存结果还是保存表达式 */
(
usdId int not null auto_increment comment '数据项ID',
usId int not null comment '操作账户ID',
usdType int not null comment '系统帐户数据项限制类型 1:CRM帐套 2:微信公众号',
extId int null comment '关联ID',
extLongId bigint null comment '关联 bigint ID',
primary key (usdId)
);
alter table smUsDataItem comment '系统帐户操作权限usd';
/******************************删除****************************************************************************/
delete from pl_dynamic_field_data where df_id in(
select df_id from pl_dynamic_field
where dt_id in (select dt_id from pl_dynamic_table where ds_id ='ea9fc1d8-05b6-432f-a72f-a760f351bc7d')
);
delete from pl_dynamic_field
where dt_id in (select dt_id from pl_dynamic_table where ds_id ='ea9fc1d8-05b6-432f-a72f-a760f351bc7d');
delete from pl_dynamic_table where ds_id ='ea9fc1d8-05b6-432f-a72f-a760f351bc7d';
delete from pl_dynamic_select where ds_id ='ea9fc1d8-05b6-432f-a72f-a760f351bc7d';
/*************************************查询****************************************************/
INSERT INTO pl_dynamic_select (ds_id, ds_name, ds_describe, ds_create_time) VALUES ('ea9fc1d8-05b6-432f-a72f-a760f351bc7d', '顾客查询', '顾客-顾客列表', '2016-04-07 14:18:53');
INSERT INTO pl_dynamic_table (dt_id, ds_id, dt_name, dt_tablename, dt_type, dt_ref_select, dt_on, dt_relation, dt_sort, dt_create_time, dt_update_time) VALUES ('514a4a3c-cfd8-4464-a659-e397349f9706', 'ea9fc1d8-05b6-432f-a72f-a760f351bc7d', '帐套与顾客关联表', 'crm_customer_account', 1, null, null, 1, 1, null, null);
INSERT INTO pl_dynamic_table (dt_id, ds_id, dt_name, dt_tablename, dt_type, dt_ref_select, dt_on, dt_relation, dt_sort, dt_create_time, dt_update_time) VALUES ('18c4b85e-e011-4ef1-8ee3-5db28ad47aa7', 'ea9fc1d8-05b6-432f-a72f-a760f351bc7d', '顾客表', 'crm_customer', 3, null, '${thisTable}.CST_ID=${masterTable}.CST_ID', 1, 2, null, null);
INSERT INTO pl_dynamic_table (dt_id, ds_id, dt_name, dt_tablename, dt_type, dt_ref_select, dt_on, dt_relation, dt_sort, dt_create_time, dt_update_time) VALUES ('3f97b890-838f-4abb-a38a-38d5d4f73a47', 'ea9fc1d8-05b6-432f-a72f-a760f351bc7d', '顾客-微信表', 'crm_customer_weixin', 3, null, '${thisTable}.CST_ID=${masterTable}.CST_ID', 1, 3, null, null);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('88420735-f6ca-4cbe-b637-9e2ee82e0e3d', '18c4b85e-e011-4ef1-8ee3-5db28ad47aa7', '顾客ID', null, 'CST_ID', true, 1, 1, 1, 1, false, 1, null, 1, null, null, null, null, false, null, null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 1, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('5aecc71f-e3a6-4b5f-a60a-b1eaaf8cc9e2', '514a4a3c-cfd8-4464-a659-e397349f9706', '帐套ID', null, 'act_id', false, 4, 1, 1, 1, true, 1, null, 1, null, null, null, null, false, '37', null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 1, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('c8bc0630-ce01-41f6-9f42-c908b0736760', '18c4b85e-e011-4ef1-8ee3-5db28ad47aa7', '顾客类型', null, 'CST_TYPE', false, 1, 1, 1, 1, false, 1, null, 1, null, null, null, null, false, null, null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 2, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('384fa6b2-79eb-4dfa-8d63-b03e3670f82d', '18c4b85e-e011-4ef1-8ee3-5db28ad47aa7', '姓名/会员名', null, 'CST_NAME', false, 1, 1, 1, 1, false, 1, null, 1, null, null, null, null, false, null, null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 3, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('a5eae80e-ba25-4412-a22d-dabdcac600d9', '3f97b890-838f-4abb-a38a-38d5d4f73a47', '昵称', null, 'NICK_NAME', false, 1, 1, 1, 1, false, 1, null, 1, null, null, null, null, false, null, null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 4, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('dcefc2ec-011e-441a-a59e-012132e4179e', '18c4b85e-e011-4ef1-8ee3-5db28ad47aa7', '手机', null, 'PHONE', false, 1, 1, 1, 1, false, 1, null, 1, null, null, null, null, false, null, null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 5, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('9d067b35-38e1-41ff-a4fb-c6394c14b441', '3f97b890-838f-4abb-a38a-38d5d4f73a47', 'OPEN ID', null, 'OPEN_ID', false, 1, 1, 1, 1, false, 1, null, 1, null, null, null, null, false, null, null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, true, 6, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('117865f0-5feb-4c7f-89c7-09b7e7d3eb55', '18c4b85e-e011-4ef1-8ee3-5db28ad47aa7', '邮箱', null, 'EMAIL', false, 1, 1, 1, 1, false, 1, null, 1, null, null, null, null, false, null, null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 7, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('0035a530-30c4-4975-85bc-9f219c126d70', '18c4b85e-e011-4ef1-8ee3-5db28ad47aa7', '入会日期', null, 'REG_TIME', false, 1, 1, 4, 31, false, 1, null, 1, null, null, null, null, false, null, null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 8, false, false);
/******************************查询**************************************************************************
select * from pl_dynamic_select where ds_id ='ea9fc1d8-05b6-432f-a72f-a760f351bc7d';
select * from pl_dynamic_table where ds_id ='ea9fc1d8-05b6-432f-a72f-a760f351bc7d' order by dt_sort asc;
select * from pl_dynamic_field
where dt_id in (select dt_id from pl_dynamic_table where ds_id ='ea9fc1d8-05b6-432f-a72f-a760f351bc7d')
order by df_sort asc;
select * from pl_dynamic_field_data where df_id in(
select df_id from pl_dynamic_field
where dt_id in (select dt_id from pl_dynamic_table where ds_id ='ea9fc1d8-05b6-432f-a72f-a760f351bc7d')
);
**/