· 6 years ago · Nov 29, 2019, 05:30 PM
1DELIMITER ;;
2CREATE DEFINER=`root`@`localhost` PROCEDURE `admin_filter_user`(IN i_username VARCHAR(45), IN i_status ENUM('All', 'Approved', 'Pending', 'Declined',''),
3IN i_sortBy ENUM('username', 'creditCardCount', 'userType', 'status', ''), IN i_sortDirection ENUM('DESC', 'ASC', ''))
4BEGIN
5 DECLARE ccCount INT DEFAULT 0;
6 DECLARE aUserType ENUM('User', 'Customer', 'Manager',
7 'Admin', 'CustomerAdmin', 'CustomerManager');
8 DECLARE isCustomer, isAdmin, isManager INT DEFAULT 0; -- 0 for false, 1 for true
9 DROP TABLE IF EXISTS AdFilterUser;
10 CREATE TABLE AdFilterUser(username VARCHAR(45), creditCardCount INT, userType ENUM('User', 'Customer', 'Manager',
11 'Admin', 'CustomerAdmin', 'CustomerManager'), status ENUM('Approved', 'Pending', 'Declined'));
12
13 -- creates a view with each User's credit card count
14 DROP VIEW IF EXISTS ccCount;
15 CREATE VIEW ccCount AS
16 SELECT Owner as o, (
17 SELECT COUNT(CreditCardNum)
18 FROM CreditCard
19 WHERE Owner = o) as creditCardCount
20 FROM CreditCard;
21
22 -- goal: generate userType view
23 -- finds all Admin-Customers
24 DROP VIEW IF EXISTS adminCustomers;
25 CREATE VIEW adminCustomers AS
26 SELECT a.Username
27 FROM Admin a
28 INNER JOIN Customer c ON a.Username = c.Username;
29
30 -- finds all Manager-Customers
31 DROP VIEW IF EXISTS managerCustomers;
32 CREATE VIEW managerCustomers AS
33 SELECT m.Username
34 FROM Manager m
35 INNER JOIN Customer c ON m.Username = c.Username;
36
37 -- creates view of Usernames to userTypes
38 DROP VIEW IF EXISTS userTypeView;
39 CREATE VIEW userTypeView AS
40 SELECT User.Username as u,
41 CASE
42 WHEN (SELECT COUNT(Username) FROM adminCustomers WHERE Username = u > 0) THEN 'CustomerAdmin'
43 WHEN (SELECT COUNT(Username) FROM managerCustomers WHERE Username = u > 0) THEN 'CustomerManager'
44 WHEN (SELECT COUNT(Username) FROM Customer WHERE Username = u > 0) THEN 'Customer'
45 WHEN (SELECT COUNT(Username) FROM Manager WHERE Username = u > 0) THEN 'Manager'
46 WHEN (SELECT COUNT(Username) FROM Admin WHERE Username = u > 0) THEN 'Admin'
47 ELSE 'User'
48 END AS userType
49 FROM User;
50
51
52 -- if Username is blank return filtered list of all users
53 IF (i_username = '')
54 THEN
55 -- filters all Users by status first
56 IF (i_status = 'All' OR i_status = '')
57 THEN
58 INSERT INTO AdFilterUser(username, creditCardCount, userType, status)
59 SELECT DISTINCT Username, creditCardCount, userType, Status
60 FROM User as us INNER JOIN ccCount ON us.Username = ccCount.o
61 INNER JOIN userTypeView ON us.Username = userTypeView.u;
62
63 INSERT INTO AdFilterUser(username, creditCardCount, userType, status)
64 SELECT DISTINCT Username, 0, userType, Status
65 FROM User as us
66 INNER JOIN userTypeView ON us.Username = userTypeView.u
67 WHERE us.Username NOT IN (SELECT Username from AdFilterUser);
68 ELSE
69 INSERT INTO AdFilterUser(username, creditCardCount, userType, status)
70 SELECT DISTINCT Username, creditCardCount, userType, Status
71 FROM User as us INNER JOIN ccCount ON us.Username = ccCount.o
72 INNER JOIN userTypeView ON us.Username = userTypeView.u
73 WHERE us.Status = i_status;
74
75 INSERT INTO AdFilterUser(username, creditCardCount, userType, status)
76 SELECT DISTINCT Username, 0, userType, Status
77 FROM User as us
78 INNER JOIN userTypeView ON us.Username = userTypeView.u
79 WHERE us.Username NOT IN (SELECT Username from AdFilterUser)
80 AND us.Status = i_status;
81 END if;
82
83 -- output table
84 -- case statements for each combo of parameters
85 IF (i_sortBy = '' AND i_sortDirection = 'ASC')
86 THEN
87 ALTER TABLE AdFilterUser
88 ORDER BY username ASC;
89 End If;
90 IF (i_sortBy = '' AND (i_sortDirection = 'DESC' OR i_sortDirection = ''))
91 THEN
92 ALTER TABLE AdFilterUser
93 ORDER BY username DESC;
94 END IF;
95 IF (i_sortBy = 'username' AND i_sortDirection = 'ASC')
96 THEN
97 ALTER TABLE AdFilterUser
98 ORDER BY username ASC;
99 END if;
100 IF (i_sortBy = 'username' AND (i_sortDirection = 'DESC' OR i_sortDirection = ''))
101 THEN
102 ALTER TABLE AdFilterUser
103 ORDER BY username DESC;
104 END if;
105 IF (i_sortBy = 'creditCardCount' AND i_sortDirection = 'ASC')
106 THEN
107 ALTER TABLE AdFilterUser
108 ORDER BY creditCardCount ASC;
109 END if;
110 IF (i_sortBy = 'creditCardCount' AND (i_sortDirection = 'DESC' OR i_sortDirection = ''))
111 THEN
112 ALTER TABLE AdFilterUser
113 ORDER BY creditCardCount DESC;
114 END if;
115 IF (i_sortBy = 'userType' AND i_sortDirection = 'ASC')
116 THEN
117 ALTER TABLE AdFilterUser
118 ORDER BY userType ASC;
119 END if;
120 IF (i_sortBy = 'userType' AND (i_sortDirection = 'DESC' OR i_sortDirection = ''))
121 THEN
122 ALTER TABLE AdFilterUser
123 ORDER BY userType DESC;
124 END if;
125 IF (i_sortBy = 'status' AND i_sortDirection = 'ASC')
126 THEN
127 ALTER TABLE AdFilterUser
128 ORDER BY status ASC;
129 END if;
130 IF (i_sortBy = 'status' AND (i_sortDirection = 'DESC' OR i_sortDirection = ''))
131 THEN
132 ALTER TABLE AdFilterUser
133 ORDER BY status DESC;
134 END if;
135 ELSE -- a username is entered
136 IF (SELECT o FROM ccCount WHERE i_username = ccCount.o) IS NULL -- creditcard count is 0
137 THEN
138 -- IF (SELECT userType
139 -- FROM userTypeView
140-- WHERE i_username = userTypeView.u) NOT LIKE '%Customer%'
141 -- THEN
142 INSERT INTO AdFilterUser(username, creditCardCount, userType, status)
143 SELECT DISTINCT Username, 0, userType, Status
144 FROM User as us
145 INNER JOIN userTypeView ON us.Username = userTypeView.u
146 WHERE us.Username = i_username
147 AND (us.Status = i_status OR i_status = NULL OR i_status = '' OR i_status = 'ALL');
148 -- END IF;
149 ELSE
150 IF i_status = '' OR i_status = 'ALL'
151 THEN
152 INSERT INTO AdFilterUser(username, creditCardCount, userType, status)
153 SELECT DISTINCT Username, creditCardCount, userType, Status
154 FROM User as us
155 INNER JOIN ccCount ON us.Username = ccCount.o
156 INNER JOIN userTypeView ON us.Username = userTypeView.u
157 WHERE us.Username = i_username;
158 ELSE
159 INSERT INTO AdFilterUser(username, creditCardCount, userType, status)
160 SELECT DISTINCT Username, creditCardCount, userType, Status
161 FROM User as us
162 INNER JOIN ccCount ON us.Username = ccCount.o
163 INNER JOIN userTypeView ON us.Username = userTypeView.u
164 WHERE us.Username = i_username
165 AND
166 Status = i_status;
167 END IF;
168
169 END IF;
170 END IF;
171
172END ;;
173DELIMITER ;