/
admin.sql
executable file
·210 lines (152 loc) · 3.5 KB
/
admin.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
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
-- Admin
-- get all entries
CREATE PROCEDURE getAll(
IN start INT,
IN limit INT,
IN status INT,
IN search CHAR,
IN email CHAR,
IN phone_number CHAR,
-- return array of admin
OUT fetch_all,
-- return admin count for count query
OUT fetch_one
)
BEGIN
SELECT * FROM admin AS admin WHERE 1 = 1
@IF isset(:status) AND !empty(:status)
THEN
AND admin.status = :status
END @IF
@IF isset(:email) AND !empty(:email)
THEN
AND admin.email = :email
END @IF
@IF isset(:phone_number) AND !empty(:phone_number)
THEN
AND admin.phone_number = :phone_number
END @IF
-- search
@IF isset(:search) AND !empty(:search)
THEN
AND admin.username LIKE CONCAT('%',:search,'%') || admin.first_name LIKE CONCAT('%',:search,'%') || admin.last_name LIKE CONCAT('%',:search,'%')
END @IF
ORDER BY admin.status DESC, admin.admin_id
-- limit
@IF isset(:limit)
THEN
@SQL_LIMIT(:start, :limit)
END @IF;
-- SELECT FOUND_ROWS() as count;
SELECT count(*) FROM (
@SQL_COUNT(admin.user_id, user) -- this takes previous query removes limit and replaces select columns with parameter user_id
) as count;
END
-- get user information
CREATE PROCEDURE get(
IN username CHAR,
IN email CHAR,
IN token CHAR,
IN admin_id INT,
IN status INT,
IN role_id INT,
OUT fetch_row
)
BEGIN
SELECT _.*, role.permissions FROM admin AS _
LEFT JOIN role ON (_.role_id = role.role_id)
WHERE 1 = 1
@IF isset(:username)
THEN
AND _.username = :username
END @IF
@IF isset(:email)
THEN
AND _.email = :email
END @IF
@IF isset(:admin_id)
THEN
AND _.admin_id = :admin_id
END @IF
@IF isset(:status)
THEN
AND _.status = :status
END @IF
@IF isset(:token)
THEN
AND _.token = :token
END @IF
@IF isset(:role_id)
THEN
AND _.role_id = :role_id
END @IF
LIMIT 1;
END
-- Add new admin
CREATE PROCEDURE add(
IN admin ARRAY,
OUT insert_id
)
BEGIN
-- allow only table fields and set defaults for missing values
@FILTER(:admin, admin)
INSERT INTO admin
( @KEYS(:admin) )
VALUES ( :admin )
END
-- Update admin
CREATE PROCEDURE edit(
IN user CHAR,
IN email CHAR,
IN admin_id INT,
IN admin ARRAY,
IN role_id INT,
OUT affected_rows
)
BEGIN
-- allow only table fields and set defaults for missing values
@FILTER(:admin, admin)
UPDATE admin
SET @LIST(:admin)
WHERE
@IF isset(:email)
THEN
email = :email
END @IF
@IF isset(:admin_id)
THEN
admin_id = :admin_id
END @IF
@IF isset(:username)
THEN
username = :username
END @IF
END
-- delete admin
PROCEDURE delete(
IN admin_id ARRAY,
OUT affected_rows
)
BEGIN
DELETE FROM admin WHERE admin_id IN (:admin_id);
END
-- set role
CREATE PROCEDURE setRole(
IN admin_id INT,
IN role CHAR,
IN role_id INT
OUT insert_id
)
BEGIN
UPDATE admin
SET
@IF isset(:role_id)
THEN
role_id = :role_id
END @IF
@IF isset(:role)
THEN
role_id = (SELECT role_id FROM roles WHERE name = :role)
END @IF
WHERE admin_id = :admin_id
END