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
| use zhengcaioa
| go
|
| --qwj 2021.6.8
| alter table plt_user add
|
| ipmac nvarchar(30) null,
| usernumber nvarchar(30) null,
| is_xmenter nvarchar(1) null,
| is_wdenter nvarchar(1) null,
| advancewages decimal(10, 2) null,
| miniyearsalary decimal(10,2) null,
| senioritywages decimal(10,2) null,
| is_travelsubsidy nvarchar(1) null
| go
|
| --qwj 2021.6.18
| create table t_sim_bind (
| id int identity,
| sim_id nvarchar(40) null,
| user_id nvarchar(40) null,
| cardpro nvarchar(5) null,
| up_time datetime null,
| up_user nvarchar(40) null,
| constraint PK_T_SIM_BIND primary key (id)
| )
| go
|
| create table t_sim_cost (
| id int identity,
| sim_id nvarchar(40) null,
| user_id nvarchar(40) null,
| allow_amount decimal(10,2) null,
| bill_amount decimal(10,2) null,
| amount decimal(10,2) null,
| bill_year int null,
| bill_month int null,
| accounted_time datetime null,
| status nvarchar(1) null,
| up_time datetime null,
| sub_userid nvarchar(40) null,
| audit_time datetime null,
| audit_userid nvarchar(40) null,
| constraint PK_T_SIM_COST primary key (id)
| )
| go
|
| --µç»°¿¨ÀàÐͶ¨Òå
| insert into [adm_goods_classify] values('d309d1af-5086-453a-b5a2-58b344dfff97','µç»°¿¨','A','e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate(),'e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate(),'04')
| --²¹Ìù×ֵ䶨Òå
| insert into [sys_code] values('107bc4e5-f13a-4d25-bd47-e4b1e61aed8a','t_sim_bind','cardpro','µç»°²¹Ìù','A','e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate(),'e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate())
|
| insert into [sys_code_dtl]([Id],[code_sn],[code_id],[comments],[sort] ,[rec_status],[creater],[createtime],[modifier],[modifytime]) values('2632d933-31a3-4692-a954-df11bf223854','50','107bc4e5-f13a-4d25-bd47-e4b1e61aed8a','50',1,'A','e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate(),'e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate())
|
| insert into [sys_code_dtl]([Id],[code_sn],[code_id],[comments],[sort] ,[rec_status],[creater],[createtime],[modifier],[modifytime]) values('306d666f-e93c-415c-a294-414e34cb3311','100','107bc4e5-f13a-4d25-bd47-e4b1e61aed8a','100',2,'A','e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate(),'e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate())
|
| insert into [sys_code_dtl]([Id],[code_sn],[code_id],[comments],[sort] ,[rec_status],[creater],[createtime],[modifier],[modifytime]) values('61711fb1-0b96-4611-9dc0-6c69dfef656c','9999','107bc4e5-f13a-4d25-bd47-e4b1e61aed8a','²»ÏÞ',3,'A','e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate(),'e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate())
|
| create view v_sim_bind as
| SELECT a.Id, a.goods_name AS simcard, a.goods_status AS simstatus, c.user_id AS userid, b.user_name AS username,
| c.cardpro, c.up_time
| FROM dbo.adm_goods_manage AS a LEFT OUTER JOIN
| dbo.t_sim_bind AS c ON a.Id = c.sim_id LEFT OUTER JOIN
| dbo.plt_user AS b ON c.user_id = b.Id
| WHERE (a.classify_id = 'd309d1af-5086-453a-b5a2-58b344dfff97' and a.goods_status='A')
| go
|
| --µç»°¿¨·ÖÅä²Ëµ¥
| insert into [plt_page] values('a3479c03-d554-4e02-b201-487e8e1dfb55','1','µç»°¿¨·ÖÅä','',3,'/Sim/Index/','01','M','967b0bdd-cd5d-4660-8c72-7f744c07751b','',0,'A','e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate(),'e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate())
| --µç»°·Ñ¼Ç¼Ã÷ϸ
| insert into [plt_page] values('5E973843-7EB4-41A6-B49B-192208647B2C','1','µç»°·ÑÓüǼ','',4,'/SimCost/Index/','01','M','967b0bdd-cd5d-4660-8c72-7f744c07751b','',0,'A','e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate(),'e4c93811-b9b1-4998-89f5-c416ebab0c07',getdate())
|
| create view v_sim_cost as
| select a.[id],[sim_id],[user_id],[allow_amount],[bill_amount],[bill_year],[bill_month],[status],[up_time]
| ,b.goods_name,c.user_name
| FROM [t_sim_cost] a inner join adm_goods_manage b on a.sim_id =b.Id
| left join plt_user c on a.user_id=c.Id
| go
|
|
