· 8 years ago · Mar 21, 2018, 11:52 AM
1create or replace package body ioby3B_pkg
2IS
3procedure CREATE_ACCOUNT_PP (
4 p_account_id OUT INTEGER,
5 p_email IN VARCHAR, -- must not be NULL
6 p_password IN VARCHAR, -- must not be NULL
7 p_location_name IN VARCHAR, -- must not be NULL
8 p_account_type IN VARCHAR, -- should have value of 'Group or organization' or 'Individual'
9 p_first_name IN VARCHAR,
10 p_last_name IN VARCHAR
11)
12IS
13BEGIN
14 DECLARE
15 null_input_exists EXCEPTION;
16 incorrect_type EXCEPTION;
17 account_exists EXCEPTION;
18 invalid_format EXCEPTION;
19 countItems INT;
20 not_null_msg VARCHAR2(100) := NULL;
21 myaccounttype VARCHAR2(20) := NULL;
22 BEGIN
23 /* Validate the input data*/
24
25 IF p_email IS NULL THEN
26 SELECT not_null_msg || chr(10) || ' Email ' INTO not_null_msg FROM dual;
27 END IF;
28 IF p_password IS NULL THEN
29 SELECT not_null_msg || chr(10) || ' Password ' INTO not_null_msg FROM dual;
30 END IF;
31 IF p_location_name IS NULL THEN
32 SELECT not_null_msg || chr(10) || ' Location Name ' INTO not_null_msg FROM dual;
33 END IF;
34 /*Need to sanitize the account type casing since there is a check constraint on the field*/
35 myaccounttype := p_account_type;
36 IF myaccounttype IS NULL THEN
37 RAISE incorrect_type;
38 ELSIF UPPER(myaccounttype) = 'GROUP OR ORGANIZATION' THEN
39 myaccounttype := 'Group or organization';
40 ELSIF UPPER(myaccounttype) = 'INDIVIDUAL' THEN
41 myaccounttype := 'Individual';
42 ELSE
43 RAISE incorrect_type;
44 END IF;
45
46 IF NOT not_null_msg IS NULL THEN
47 RAISE null_input_exists;
48 END IF;
49 IF NOT regexp_like(p_email, '[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}', 'i') THEN
50 RAISE invalid_format;
51 END IF;
52 SELECT COUNT(*) INTO countItems FROM I_ACCOUNT WHERE UPPER(ACCOUNT_EMAIL) = UPPER(p_email);
53
54 IF countItems > 0 THEN
55 RAISE account_exists;
56 END IF;
57
58
59 INSERT INTO I_ACCOUNT
60 (ACCOUNT_ID, ACCOUNT_EMAIL, ACCOUNT_PASSWORD, ACCOUNT_LOCATION_NAME, ACCOUNT_TYPE, ACCOUNT_FIRST_NAME, ACCOUNT_LAST_NAME)
61 SELECT MAX(ACCOUNT_ID) + 1 MyAccountID, p_email, p_password, p_location_name, myaccounttype, p_first_name, p_last_name
62 FROM I_ACCOUNT;
63
64 SELECT MAX(ACCOUNT_ID) INTO p_account_id FROM I_ACCOUNT WHERE UPPER(ACCOUNT_EMAIL) = UPPER(p_email);
65
66 DBMS_OUTPUT.PUT_LINE('An account has been created with the given info. The account id is: ' || p_account_id);
67
68 COMMIT;
69
70 EXCEPTION
71 WHEN null_input_exists THEN
72 DBMS_OUTPUT.PUT_LINE('Missing Input Data.');
73 DBMS_OUTPUT.PUT_LINE('Please make sure to provide input for the following fields: ' || not_null_msg);
74 WHEN incorrect_type THEN
75 DBMS_OUTPUT.PUT_LINE('Incorrect Account Type Defined.');
76 DBMS_OUTPUT.PUT_LINE('Account Type should have value of ''Group or organization'' or ''Individual''');
77 WHEN account_exists THEN
78 DBMS_OUTPUT.PUT_LINE('Account already exists.');
79 DBMS_OUTPUT.PUT_LINE('An existing account has been found for the email address provided. Please use a different email.');
80 WHEN invalid_format THEN
81 DBMS_OUTPUT.PUT_LINE('Email Address not valid.');
82 DBMS_OUTPUT.PUT_LINE('The format of the email address is not valid. Please use a different email.');
83 WHEN OTHERS THEN
84 DBMS_OUTPUT.PUT_LINE('An Error occurred');
85 DBMS_OUTPUT.PUT_LINE('The error number: ' || SQLCODE);
86 DBMS_OUTPUT.PUT_LINE('The error message: ' || SQLERRM);
87 ROLLBACK;
88END;
89END CREATE_ACCOUNT_PP;
90
91procedure CREATE_PROJECT_PP (
92p_project_id OUT INTEGER,
93p_title IN VARCHAR,
94p_goal IN NUMBER, -- The goal should be >= zero
95p_deadline IN DATE,
96p_creation_date IN DATE,
97p_description IN CLOB,
98p_subtitle IN VARCHAR,
99p_street_1 IN VARCHAR,
100p_street_2 IN VARCHAR,
101p_city IN VARCHAR,
102P_state IN VARCHAR,
103p_postal_code IN CHAR,
104p_postal_extension IN CHAR,
105p_steps_to_take IN CLOB,
106p_motivation IN CLOB,
107p_volunteer_need IN VARCHAR, -- should be 'yes' or 'no'
108p_project_status IN VARCHAR, -- should be in {'Closed', 'Completed', 'Open', 'Submitted', 'Underway'}
109p_account_id IN INTEGER -- should match account in the account table
110)
111
112IS
113BEGIN
114 DECLARE
115 null_input_exists EXCEPTION;
116 invalid_status EXCEPTION;
117 invalid_deadline EXCEPTION;
118 invalid_volunteer EXCEPTION;
119 invalid_account EXCEPTION;
120 invalid_goal EXCEPTION;
121 invalid_description EXCEPTION;
122 countItems INT;
123 not_null_msg VARCHAR2(500) := NULL;
124 myprojectstatus VARCHAR2(20) := NULL;
125 myprojectvolunteer VARCHAR2(20) := NULL;
126 CurrentDate DATE := SYSDATE;
127 BEGIN
128 IF p_title IS NULL THEN
129 SELECT not_null_msg || chr(10) || ' Title ' INTO not_null_msg FROM dual;
130 END IF;
131 IF p_goal < 0 THEN
132 RAISE invalid_goal;
133 END IF;
134 IF p_goal IS NULL THEN
135 SELECT not_null_msg || chr(10) || ' Goal ' INTO not_null_msg FROM dual;
136 END IF;
137 IF p_deadline IS NULL THEN
138 SELECT not_null_msg || chr(10) || ' Deadline ' INTO not_null_msg FROM dual;
139 END IF;
140 IF p_description IS NULL THEN
141 SELECT not_null_msg || chr(10) || ' Description ' INTO not_null_msg FROM dual;
142 END IF;
143 IF p_city IS NULL THEN
144 SELECT not_null_msg || chr(10) || ' City ' INTO not_null_msg FROM dual;
145 END IF;
146 IF p_subtitle IS NULL THEN
147 SELECT not_null_msg || chr(10) || ' Subtitle ' INTO not_null_msg FROM dual;
148 END IF;
149 IF p_street_1 IS NULL THEN
150 SELECT not_null_msg || chr(10) || ' Street ' INTO not_null_msg FROM dual;
151 END IF;
152 IF p_state IS NULL THEN
153 SELECT not_null_msg || chr(10) || ' State ' INTO not_null_msg FROM dual;
154 END IF;
155 IF p_postal_code IS NULL THEN
156 SELECT not_null_msg || chr(10) || ' Postal Code ' INTO not_null_msg FROM dual;
157 END IF;
158 IF myprojectstatus IS NULL THEN
159 SELECT not_null_msg || chr(10) || ' Project Status ' INTO not_null_msg FROM dual;
160 END IF;
161
162 IF p_deadline <= p_creation_date
163 THEN
164 RAISE invalid_deadline;
165 END IF;
166
167 IF p_description IS NULL THEN
168 RAISE invalid_description;
169 END IF;
170
171 myprojectvolunteer := p_volunteer_need;
172 IF myprojectvolunteer IS NULL THEN
173 RAISE invalid_volunteer;
174 ELSIF UPPER(myprojectvolunteer) = 'YES' THEN
175 myprojectvolunteer := 'yes';
176 ELSIF UPPER(myprojectvolunteer) = 'NO' THEN
177 myprojectvolunteer := 'no';
178 ELSE
179 RAISE invalid_volunteer;
180 END IF;
181
182 myprojectstatus := p_project_status;
183 IF myprojectstatus IS NULL THEN
184 myprojectstatus := 'Submitted';
185 ELSIF UPPER(myprojectstatus) = 'UNDERWAY' THEN
186 myprojectstatus := 'Underway';
187 ELSIF UPPER(myprojectstatus) = 'OPEN' THEN
188 myprojectstatus := 'Open';
189 ELSIF UPPER(myprojectstatus) = 'CLOSED' THEN
190 myprojectstatus := 'Closed';
191 ELSIF UPPER(myprojectstatus) = 'COMPLETE' THEN
192 myprojectstatus := 'Complete';
193 ELSIF UPPER(myprojectstatus) = 'SUBMITTED' THEN
194 myprojectstatus := 'Submitted';
195 ELSE
196 RAISE invalid_status;
197 END IF;
198
199 SELECT COUNT(*) INTO countItems FROM I_ACCOUNT WHERE account_id = p_account_id;
200 IF countItems < 1 THEN
201 RAISE invalid_account;
202 END IF;
203
204 INSERT INTO I_PROJECT
205 (PROJECT_ID,PROJECT_TITLE,PROJECT_GOAL,PROJECT_DEADLINE,PROJECT_CREATION_DATE,PROJECT_DESCRIPTION,
206 PROJECT_SUBTITLE,PROJECT_STREET_1,PROJECT_STREET_2,PROJECT_CITY,PROJECT_STATE,PROJECT_POSTAL_CODE,
207 PROJECT_POSTAL_EXTENSION,PROJECT_STEPS_TO_TAKE, PROJECT_MOTIVATION, PROJECT_VOLUNTEER_NEED,PROJECT_STATUS,ACCOUNT_ID)
208
209 SELECT MAX(PROJECT_ID) + 1, p_title, p_goal, p_deadline, NVL(p_creation_date, currentdate), p_description, p_subtitle, p_street_1, p_street_2,
210 p_city, p_state, p_postal_code, p_postal_extension, p_steps_to_take, p_motivation, myprojectvolunteer, myprojectstatus, p_account_id
211 FROM I_PROJECT;
212
213 COMMIT;
214
215 EXCEPTION
216
217 WHEN null_input_exists THEN
218 DBMS_OUTPUT.PUT_LINE('Missing Input Data.');
219 DBMS_OUTPUT.PUT_LINE('Please make sure to provide input for the following fields: ' || not_null_msg);
220 WHEN invalid_description THEN
221 DBMS_OUTPUT.PUT_LINE('Invalid description, please put '' in front and back of the text input.');
222 WHEN invalid_goal THEN
223 DBMS_OUTPUT.PUT_LINE('Invalid goal, please specify a goal larger than zero.');
224 WHEN invalid_deadline THEN
225 DBMS_OUTPUT.PUT_LINE('Invalid deadline, please set deadline to a larger value than the project creation date.');
226 WHEN invalid_status THEN
227 DBMS_OUTPUT.PUT_LINE('Invalid status, accepted input is Open, Underway, Closed and Submitted.');
228 WHEN invalid_volunteer THEN
229 DBMS_OUTPUT.PUT_LINE('Invalid volunteer, accepted input is yes and no.');
230 WHEN invalid_account THEN
231 DBMS_OUTPUT.PUT_LINE('Invalid account, please specify a correct accound ID.');
232 WHEN OTHERS THEN
233 DBMS_OUTPUT.PUT_LINE('An Error occurred');
234 DBMS_OUTPUT.PUT_LINE('The error number: ' || SQLCODE);
235 DBMS_OUTPUT.PUT_LINE('The error message: ' || SQLERRM);
236 ROLLBACK;
237END;
238END CREATE_PROJECT_PP;
239
240
241
242procedure CREATE_GIVING_LEVEL_SP (
243p_projectID IN INTEGER,
244p_givingLevelAmt IN INTEGER, -- Must be > zero or NULL
245p_givingDescription IN NUMBER -- Must not be NULL
246)
247IS
248 BEGIN
249
250 DECLARE
251 null_input_exists EXCEPTION;
252 invalid_input EXCEPTION;
253 invalid_amount EXCEPTION;
254 entry_exists EXCEPTION;
255 countItems INT;
256 not_null_msg VARCHAR2(100) := NULL;
257 mygivinglevel VARCHAR2(100) := NULL;
258
259
260BEGIN
261
262 IF p_givingLevelAmt IS NULL THEN
263 SELECT not_null_msg || chr(10) || ' Giving Level Amount ' INTO not_null_msg FROM dual;
264 END IF;
265 IF p_givingDescription IS NULL THEN
266 SELECT not_null_msg || chr(10) || ' Giving Description ' INTO not_null_msg FROM dual;
267 END IF;
268 IF NOT not_null_msg IS NULL THEN
269 RAISE null_input_exists;
270 END IF;
271
272SELECT COUNT(*) INTO countItems FROM
273 I_PROJECT WHERE PROJECT_ID = p_projectID;
274 IF countItems < 1 THEN
275 RAISE invalid_input;
276END IF;
277
278IF p_givingLevelAmt <= 0 THEN
279RAISE invalid_amount;
280
281END IF;
282
283SELECT COUNT(*) INTO countItems
284FROM I_GIVING_LEVEL
285WHERE GIVING_LEVEL_AMOUNT = p_givingLevelAmt AND PROJECT_ID = p_projectID ;
286IF countItems > 0 THEN
287 UPDATE I_GIVING_LEVEL
288 SET GIVING_LEVEL_DESCRIPTION = p_givingDescription
289 WHERE GIVING_LEVEL_AMOUNT = project_id;
290
291
292
293ELSE
294Insert into I_GIVING_LEVEL (PROJECT_ID,GIVING_LEVEL_AMOUNT,GIVING_LEVEL_DESCRIPTION)
295VALUES(p_projectID, p_givingLevelAmt, p_givingDescription);
296
297END IF;
298
299 COMMIT;
300
301EXCEPTION
302
303 WHEN null_input_exists THEN
304 DBMS_OUTPUT.PUT_LINE('Missing Input Data.');
305 DBMS_OUTPUT.PUT_LINE('Please make sure to provide input for the following fields: ' || not_null_msg);
306
307 WHEN invalid_input THEN
308 DBMS_OUTPUT.PUT_LINE('Project ID provided cannot be
309found.');
310
311
312 WHEN invalid_amount THEN
313 DBMS_OUTPUT.PUT_LINE('Invalid amount, must be greater than zero');
314
315 NULL;
316END;
317END;
318
319procedure ADD_BUDGET_ITEM_SP (
320p_projectID IN INTEGER,
321p_description IN VARCHAR,
322p_budgetAmt IN NUMBER
323)
324IS
325BEGIN
326 DECLARE
327 invalid_input EXCEPTION;
328 description_exists EXCEPTION;
329 not_null_msg VARCHAR2(100) := NULL;
330 countItems INT;
331 null_input_exists EXCEPTION;
332
333BEGIN
334 IF p_description IS NULL THEN
335 SELECT not_null_msg || chr(10) || ' Budget Description ' INTO not_null_msg FROM dual;
336 END IF;
337 IF p_projectID IS NULL THEN
338 SELECT not_null_msg || chr(10) || ' Project ID ' INTO not_null_msg FROM dual;
339 END IF;
340
341 IF NOT not_null_msg IS NULL THEN
342 RAISE null_input_exists;
343 END IF;
344
345SELECT COUNT(*) INTO countItems FROM
346 I_PROJECT WHERE PROJECT_ID = p_projectID;
347
348 IF countItems < 1 THEN
349 RAISE invalid_input;
350END IF;
351
352
353
354SELECT COUNT(*) INTO countItems FROM
355 I_BUDGET WHERE BUDGET_LINE_ITEM_DESCRIPTION = p_description;
356 IF countItems > 0 THEN
357 RAISE description_exists;
358 END IF;
359
360Insert into I_BUDGET (PROJECT_ID,BUDGET_LINE_ITEM_DESCRIPTION,BUDGET_LINE_ITEM_AMOUNT)
361VALUES (P_PROJECTID, P_DESCRIPTION, P_BUDGETAMT);
362
363
364COMMIT;
365
366
367
368EXCEPTION
369 WHEN null_input_exists THEN
370 DBMS_OUTPUT.PUT_LINE('Missing Input Data.');
371 DBMS_OUTPUT.PUT_LINE('Please make sure to provide input for the following fields: ' || not_null_msg);
372
373 WHEN invalid_input THEN
374 DBMS_OUTPUT.PUT_LINE
375 ('Project ID provided cannot be found.');
376
377
378WHEN description_exists THEN
379 DBMS_OUTPUT.PUT_LINE('Description exists');
380
381WHEN OTHERS THEN
382 DBMS_OUTPUT.PUT_LINE('An Error occurred');
383 DBMS_OUTPUT.PUT_LINE('The error number: ' || SQLCODE);
384 DBMS_OUTPUT.PUT_LINE('The error message: ' || SQLERRM);
385 ROLLBACK;
386
387 NULL;
388 END;
389END;
390
391procedure ADD_FOCUSAREA_SP (
392p_project_ID IN INTEGER,
393p_focusArea IN VARCHAR
394)
395IS
396BEGIN
397DECLARE
398 invalid_input EXCEPTION;
399 entry_exists EXCEPTION;
400 countItems INT;
401 BEGIN
402
403 SELECT COUNT(*) INTO countItems FROM (
404 SELECT 1 FROM I_PROJECT WHERE PROJECT_ID = p_project_ID
405 UNION ALL
406 SELECT 1 FROM I_FOCUS_AREA WHERE FOCUS_AREA_NAME = p_focusArea
407 ) InputIsValid;
408 IF countItems < 2 THEN
409 RAISE invalid_input;
410 ELSE
411 SELECT COUNT(*) INTO countItems
412 FROM I_PROJ_FOCUSAREA
413 WHERE FOCUS_AREA_NAME = p_focusArea AND PROJECT_ID = p_project_ID;
414
415 IF countItems > 0 THEN
416 RAISE entry_exists;
417 ELSE
418 INSERT INTO I_PROJ_FOCUSAREA
419 (FOCUS_AREA_NAME, PROJECT_ID)
420 VALUES (p_focusArea, p_project_ID);
421 END IF;
422 END IF;
423 COMMIT;
424
425 EXCEPTION
426 WHEN invalid_input THEN
427 DBMS_OUTPUT.PUT_LINE('Project ID or Focus Area provided cannot be found.');
428 DBMS_OUTPUT.PUT_LINE('Please use a Project ID and Focus Area that exists.');
429 WHEN entry_exists THEN
430 DBMS_OUTPUT.PUT_LINE('Project ID and Focus Area provided already exist.');
431 DBMS_OUTPUT.PUT_LINE('Please add a new combination.');
432 WHEN OTHERS THEN
433 DBMS_OUTPUT.PUT_LINE('An Error occurred');
434 DBMS_OUTPUT.PUT_LINE('The error number: ' || SQLCODE);
435 DBMS_OUTPUT.PUT_LINE('The error message: ' || SQLERRM);
436 ROLLBACK;
437 END;
438END;
439
440END ioby3B_pkg;