-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL_FacilityManagementSystem.sql
175 lines (115 loc) · 4.7 KB
/
SQL_FacilityManagementSystem.sql
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
CREATE TABLE public.tblroles (
roleid uuid NOT NULL,
rolenumber int4 DEFAULT nextval('rolenumberseq'::regclass) NULL,
rolename varchar(50) NULL,
roleisdeleted int4 DEFAULT 0 NULL,
CONSTRAINT tblroles_pkey PRIMARY KEY (roleid)
);
CREATE TABLE public.tblusers (
usrid uuid NOT NULL,
usrnumber int4 DEFAULT nextval('usrnumberseq'::regclass) NULL,
usrfirstname varchar(50) NULL,
usrlastname varchar(50) NULL,
usrknownas varchar(50) NULL,
usraddress varchar(250) NULL,
usrprimaryemail varchar(50) NULL,
usrsecondaryemail varchar(50) NULL,
usrmobile varchar(50) NULL,
usrwhatsapp varchar(50) NULL,
usrlandphone varchar(50) NULL,
usrmfaactivated int4 DEFAULT 0 NULL,
usrssoactivated int4 DEFAULT 0 NULL,
usrnotes varchar(250) NULL,
usrisdeleted int4 NULL,
usrroledid uuid NULL,
CONSTRAINT tblusers_pkey PRIMARY KEY (usrid)
);
ALTER TABLE public.tblusers ADD CONSTRAINT tblusers_usrroledid_fkey FOREIGN KEY (usrroledid) REFERENCES public.tblroles(roleid);
CREATE TABLE public.tblfacilitytypes (
fltytypeid uuid NOT NULL,
fltytypename varchar(50) NULL,
fltytypedescription varchar(500) NULL,
fltyisdeleted int4 DEFAULT 0 NULL,
CONSTRAINT tblfacilitytypes_pkey PRIMARY KEY (fltytypeid)
);
CREATE TABLE public.tblfacilities (
fltyid uuid NOT NULL,
fltynumber int4 DEFAULT nextval('fltynumberseq'::regclass) NULL,
fltytype uuid NULL,
fltyowner uuid NULL,
fltyname varchar(50) NULL,
fltydescription varchar(500) NULL,
fltyaddress varchar(250) NULL,
fltytelephone varchar(50) NULL,
fltyemail varchar(50) NULL,
fltywebsite varchar(50) NULL,
fltybankname varchar(100) NULL,
fltybankaccountname varchar(50) NULL,
fltybankaccountnumber varchar(40) NULL,
fltybanksortcode varchar(10) NULL,
fltybankifsccode varchar(25) NULL,
fltyisdeleted int4 DEFAULT 0 NULL,
fltyactive int4 DEFAULT 0 NULL,
fltybankiban varchar(50) NULL,
fltyrate float8 NULL,
fltycurrency varchar(10) NULL,
CONSTRAINT tblfacilities_pkey PRIMARY KEY (fltyid)
);
ALTER TABLE public.tblfacilities ADD CONSTRAINT tblfacilities_fltyowner_fkey FOREIGN KEY (fltyowner) REFERENCES public.tblusers(usrid);
ALTER TABLE public.tblfacilities ADD CONSTRAINT tblfacilities_fltytype_fkey FOREIGN KEY (fltytype) REFERENCES public.tblfacilitytypes(fltytypeid);
CREATE TABLE public.tblfacilityattachments (
fltyattachmentid uuid NOT NULL,
fltyid uuid NULL,
fltyattachmentname varchar(250) NULL,
fltyattachmentpath varchar(250) NULL,
fltyattachmentapproved int4 DEFAULT 0 NULL,
fltyattachmentisdeleted int4 DEFAULT 0 NULL,
CONSTRAINT tblfacilityattachments_pkey PRIMARY KEY (fltyattachmentid)
);
ALTER TABLE public.tblfacilityattachments ADD CONSTRAINT tblfacilityattachments_fltyid_fkey FOREIGN KEY (fltyid) REFERENCES public.tblfacilities(fltyid);
CREATE TABLE public.tblfacilitybooking (
fltybookingid uuid NOT NULL,
fltybookingnumber int4 DEFAULT nextval('fltybookingnumberseq'::regclass) NULL,
fltybookedon timestamp NULL,
fltybookedby uuid NULL,
fltybookingdate timestamp NULL,
fltybookinghoursfrom int4 NULL,
fltybookinghoursto int4 NULL,
fltybookingapproved int4 DEFAULT 0 NULL,
fltybookingapprovedon timestamp NULL,
fltybookingisdeleted int4 DEFAULT 0 NULL,
fltybookingnotes varchar(250) NULL,
fltybookingpaid int4 DEFAULT 0 NULL,
fltyid uuid NULL,
fltybookingrate float8 NULL,
fltybookingamountpaid float8 NULL,
CONSTRAINT tblfacilitybooking_pkey PRIMARY KEY (fltybookingid)
);
ALTER TABLE public.tblfacilitybooking ADD CONSTRAINT tblfacilitybooking_fltybookedby_fkey FOREIGN KEY (fltybookedby) REFERENCES public.tblusers(usrid);
ALTER TABLE public.tblfacilitybooking ADD CONSTRAINT tblfacilitybooking_fltyid_fkey FOREIGN KEY (fltyid) REFERENCES public.tblfacilities(fltyid);
alter table public.tblfacilitybooking add fltyBookingIsCancelled float null, add fltyBookingCancelledOn timestamp, add fltyBookingCancelledComments varchar(1000);
alter table public.tblusers add usrCreatedOn timestamp, add usrLastUpdatedOn timestamp, add usrComments varchar(1000);
create table tblLanguages
(
lanID uuid NOT null primary key,
lanName varchar(50) not null,
lanDescription varchar(500) null,
lanIsDeleted int default 0
);
create table tblControls
(
ctrID uuid NOT null primary key,
ctrName varchar(50) not null,
ctrDescription varchar(500) null,
ctrIsDeleted int default 0
);
create table tblControlDisplay
(
ctrddspID uuid NOT null primary key,
ctrdspCtrID uuid NOT null ,
ctrdsplanID uuid NOT null ,
ctrdspText varchar(250),
ctrdspIsDeleted int
);
ALTER TABLE public.tblControlDisplay ADD CONSTRAINT tblLanguagesy_lanID_fkey FOREIGN KEY (ctrdsplanID) REFERENCES public.tblLanguages(lanID);
ALTER TABLE public.tblControlDisplay ADD CONSTRAINT tblControlDisplay_ctrID_fkey FOREIGN KEY (ctrdspCtrID) REFERENCES public.tblControls(ctrID);