שמות המגישים: אלישע רובין ודניאל סיבוני
שם המערכת: מערכת ניהול דיור מוגן
היחידה הנבחרת: יחידת המגורים והאירועים
מערכת זו נועדה לנהל את הפעילות השוטפת של מוסד לדיור מוגן. המערכת שומרת נתונים על חדרים, דיירים, אנשי צוות, אירועים, תפריטים, ציוד ותחזוקה.
היא מאפשרת לעקוב אחר:
- ניהול דיירים ושיבוצם לחדרים
- תיעוד אירועים והשתתפות דיירים
- מעקב אחר ציוד ובקשות תחזוקה
- ניהול תפריטי ארוחות והצוות שאחראי לבישול
- זיהוי תפקידים של אנשי צוות במערכת
בחרנו לעבוד לפי עקרונות הנורמליזציה (עד דרגה 3) כדי להבטיח:
- הפחתת כפילויות
- עדכון יעיל
- מניעת שגיאות לוגיות
resident←→room– קשר 1:רביםresident←→event– טבלת קשרvisiting_eventלקשר n:nmaintenance_req– מפתח ראשי מורכב:room_id,staff_member_id,request_idis_chef– קשר n:n ביןstaff_memberל-meal
צירוף meal_type + day_of_the_week מהווה מזהה ייחודי.
- לא ניתן להוסיף דייר לחדר לא קיים
- בקשת תחזוקה חייבת להתייחס לישות קיימת
מפריד בין:
- ישויות אנושיות (דיירים, עובדים)
- ישויות לוגיסטיות (חדרים, ציוד)
למשל resident_id, room_id:
- מפשט שאילתות
- קישורים פשוטים
- יעילות בביצועים
הוספנו נתונים ב-3 דרכים:
- בינה מלאכותית
- סקריפט Python
- אתר Mockaroo
דוגמה לגיבוי ושחזור נתונים:
-
5 האירועים הכי פופולריים
-
-
בקשות תחזוקה לפי חדר וקיבולת החדר
-
-
התפלגות השתתפות באירועים
-
-
3 הפריטים שהשתמשו בהם הכי הרבה בבבקשות תחזוקה
-
-
חדרים עם פחות/יותר מ-2 בקשות תחזוקה
-
-
מספר השפים שמכינים ארוחות לאורך הזמן
-
-
מציאת העובדים שסיימו הכי הרבה משימות מכל תפקיד
-
-
אחוזי התפוסה בפועל של החדרים לעומת הקיבולת המקסימלית בבניין
-
ביצענו merge עם בסיס נתונים אחר שאחראי על הדיירים. זה הERD של בסיס הנתונים:
וזה ה DSD:
והנה אותם תרשימים אחרי ה merge:
החלטנו לבצע את האינטגרציה כך שכאשר היתה טבלה משותפת (לדוג' לשנינו היה טבלה של resident אז השתמשנו בטבלה שלהם בסוף). הדבר לא יצר בעיות התאמה בגלל ששנינו הגדרנו את המפתחות של רוב הדברים פשוט במספרים בין 1 ל-1000. עדיין נתקלנו בבעיות מפני שטבלאת resident שלבם כללה פחות אנשים מטבלאת resident שלנו מה שגרם לבעיות בטבלאות אחרות שלנו שנשענו על resident, אז ביצענו merge Into בין הטבלאות:
MERGE INTO resident AS t1
USING resident_temp AS t2 -- our table
ON t1.residentid = t2.resident_id
WHEN NOT MATCHED THEN
INSERT (residentid, firstname, lastname, dateofbirth, admissiondate, roomid)
VALUES (
t2.resident_id,
SPLIT_PART(t2.resident_name, ' ', 1),
SPLIT_PART(t2.resident_name, ' ', 2),
t2.resident_dob,
'01/01/2021',
t2.room_id
);
דהיינו הוספנו לטבלה שלהם את כל האיברים בטבלה שלנו שלא קיימים בטבלה שלהם. חוץ מזה, האינטגרציה רצה על מי מנוחות.
החלטנו ליצור שני מבטים. המבט הראשון הוא עבור עובדי התחזוקה ונותן להם את המידע המעניין אותם, קרי: משימות שהם צריכים לעשות, החדרים שצריכים טיפול וכו'. הנה איך היצירה שלו נראית ואיך הוא נראה:
create view caregiver_maintenance as select c.caregiverid, (c.firstname || ' ' || c.lastname) caregiver_name, i.item_name, i.quantity, r.roomnumber, mr.req_description, mr.req_status from maintenance_req mr join caregiver c on mr.staff_member_id = c.caregiverid join inventory i on mr.item_id = i.item_id join department d on c.departmentid = d.departmentid join room r on mr.room_id = r.roomid where d.name not ilike '%Care%' and d.name not ilike '%nursing%'
הרצנו שני שאילתות עליו. הראשונה נועדה לברר אילו מוצרים זקוקים אליהם בשביל משימות שעומדות להתקיים שיש מחסור מהם:
השאילתא השנייה מחפשת את כל המשימות שעובד מסויים צריך לבצע, יחד עם מספר החדר בו המשימה מתבצעת.
המבט השני הוא מידע בשביל המשפחה, דהיינו: הסטוריית ביקורים, ההיסטוריה הרפואית של הדייר ומידע על הרופאים שלו וכו'.
create view family_view as select fv.visitdate, fv.visitorname, r.firstname || ' ' || r.lastname resident_name, c.firstname || ' ' || c.lastname caregiver_name, c.phone caregiver_phone, mt.treatmentdate, mt.treatmenttype from familyvisit fv join medicaltreatment mt on mt.residentid = fv.residentid join caregiver c on c.caregiverid = mt.caregiverid join resident r on r.residentid = mt.residentid
הרצנו עליו שני שאילתות. הראשונה מחפשת מידע על כל הרופאים שטיפלו בחולה מסויים:
השנייה מיצרת את הסטוריית הטיפולים של חולה מסויים:
יצרנו 3 פונקציונאליות חדשות באמצעות pg/plsql- פונקציה לעידכון בטבלאת האירועים על ימי הולדת של דיירים, פונקציה להמלצה על אירועים לדייר כלשהו על פי כמה משתתפים אחרים הולכים לאותו אירוע, ומערכת להורדת כמות המוצרים במערכת בצורה בטוחה.
בנוסף לכך, יצרנו טבלאות חדשות ל updates_log, orders כדי לתאר את העידכונים וכדי לערוך הזמנות מוצרים בהתאמה
יצרנו פונקציה שעוברת על כל הדיירים, ואם היומולדת שלהם מחר אז יוצרים איבנט חדש בטבלה event.
יצרנו עבור זה גם פונקציות עזר כגון get_resident_age ו add_event שדואג להכנסת אירוע בלי צורך להגדיר מפתח יחודי
-- update birthdays, make the location of the event at the resident's room and record in log
CREATE OR REPLACE FUNCTION create_upcoming_birthday_events()
RETURNS VOID AS $$
DECLARE
r RECORD;
v_event_date DATE := CURRENT_DATE + INTERVAL '1 day';
v_age INT;
v_room INT;
v_event_name VARCHAR(100);
BEGIN
FOR r IN
SELECT residentid, firstname, lastname, dateofbirth, roomnumber
FROM resident re
JOIN room ro ON re.roomid = ro.roomid
WHERE EXTRACT(MONTH FROM re.dateofbirth) = EXTRACT(MONTH FROM v_event_date)
AND EXTRACT(DAY FROM re.dateofbirth) = EXTRACT(DAY FROM v_event_date)
LOOP
-- Use existing function to get current age
v_age := get_resident_age(r.residentid) + 1;
v_room := r.roomnumber;
-- Construct event title
v_event_name := r.firstname || ' ' || r.lastname || '''s ' || v_age || ' birthday!';
-- Insert birthday event
CALL add_event(v_event_name, v_event_date, CAST(v_room AS VARCHAR));
-- Log the event creation
INSERT INTO updates_log(description)
VALUES ('Birthday event added: "' || v_event_name || '" for ' || v_event_date);
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- select create_upcoming_birthday_events();ולהלן התוצאה:
יצרנו מערכת המלצת אירועים שממליצה עבור דייר מסוים אירועים שאנשים בטווח גילאים שלו ומאותו מין כמוהו הולכים אליהם גם להלן הקוד:
-- Main function - recommend events based on similar age groups
CREATE OR REPLACE FUNCTION recommend_events(target_resident_id INT)
RETURNS TABLE(
event_date DATE,
event_location VARCHAR,
similar_age_attendees BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
target_age INT;
BEGIN
-- Log the request
CALL log_recommendation_request(target_resident_id);
-- Get target resident's age
SELECT get_resident_age(target_resident_id) INTO target_age;
-- Return future events attended by residents within 5 years of target's age
RETURN QUERY
SELECT
e.event_date,
e.event_location,
COUNT(ve.resident_id) as similar_age_attendees
FROM event e
JOIN visiting_event ve ON e.event_id = ve.event_id
-- WHERE e.event_date >= CURRENT_DATE
WHERE e.event_id NOT IN (
SELECT event_id
FROM visiting_event
WHERE resident_id = target_resident_id
)
AND get_resident_age(ve.resident_id) BETWEEN (target_age - 5) AND (target_age + 5)
GROUP BY e.event_id, e.event_date, e.event_location
ORDER BY COUNT(ve.resident_id) DESC
LIMIT 10;
END;
$$;
-- SELECT * FROM recommend_events(123);ולהלן התוצאה:
יצרנו פרוצדורה שמקטינה את הכמות של מוצר כלשהו במלאי, ומוודאת שלא מקטינים אותה ביותר מידי. בנוסף לכך, יצרנו טריגר שמכניס בצורה אוטומטית לטבלה orders הזמנה של מוצר אם הכמות שלו במלאי קטנה מחמש
להלן הקוד:
CREATE OR REPLACE PROCEDURE decrease_item_quantity(
target_item_id INT,
decrease_amount INT DEFAULT 1
)
LANGUAGE plpgsql
AS $$
BEGIN
-- Update the quantity
UPDATE inventory
SET quantity = quantity - decrease_amount
WHERE item_id = target_item_id AND quantity > decrease_amount;
-- Check if update affected any rows
IF NOT FOUND THEN
RAISE EXCEPTION 'Item with ID % not found', target_item_id;
END IF;
INSERT INTO updates_log(description)
VALUES('Decreased quantity of item ' || target_item_id || ' by ' || decrease_amount);
END;
$$;להלן הקוד עבור הטריגר (יצרנו פונקציית עזר שפרקטית מכניסה את ההזמנה לטבלה)
CREATE OR REPLACE TRIGGER order_low_stock
AFTER UPDATE OF quantity ON inventory
FOR EACH ROW
WHEN (NEW.quantity < 5)
EXECUTE FUNCTION auto_order_low_stock();
CREATE OR REPLACE FUNCTION auto_order_low_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
-- Check if quantity dropped below 5
IF NEW.quantity < 5 THEN
-- Check if we haven't already placed a pending order for this item
IF NOT EXISTS (
SELECT 1 FROM orders
WHERE item_id = NEW.item_id
AND status = 'PENDING'
) THEN
-- Insert new order
INSERT INTO orders (item_id, item_name, order_quantity)
VALUES (NEW.item_id, NEW.item_name, 10);
RAISE NOTICE 'Auto-ordered 10 units of % (ID: %) - stock level: %',
NEW.item_name, NEW.item_id, NEW.quantity;
END IF;
END IF;
RETURN NEW;
END;
$$;ולהלן התוצאה:
יצרנו טריגר שמוודא שלא מכניסים לתוך meintenance req בקשה שהיא לא recived.
CREATE OR REPLACE FUNCTION public.check_maintenance_status()
RETURNS trigger
BEGIN
IF UPPER(NEW.req_status) <> 'RECEIVED' THEN
RAISE EXCEPTION 'Maintenance request status must be "Received".';
END IF;
RETURN NEW;
END;יצרנו פונקציה שמטרתה היא שנוכל להכניס אירוע ושהיא תחולל לנו מפתח ייחודי לאירוע:
--generates a unique id for an event i want to insert
CREATE OR REPLACE PROCEDURE add_event(
p_name VARCHAR(100),
p_date DATE,
p_location VARCHAR(100)
)
LANGUAGE plpgsql
AS $$
DECLARE
v_event_id INT;
BEGIN
-- Generate unique event_id using timestamp and random number
v_event_id := TRUNC(RANDOM() * 100000)::INT;
-- Insert into event table
INSERT INTO event(event_id, event_name, event_date, event_location)
VALUES (v_event_id, p_name, p_date, p_location);
-- Optional: output confirmation
RAISE NOTICE 'Event % added with ID: %', p_name, v_event_id;
END;
$$;בנוסף לכך יצרנו פונקיית עזר שמחשבת לנו את גילו של דייר כלשהו
-- helper function to calculate a resident age
CREATE OR REPLACE FUNCTION get_resident_age(p_resident_id INT)
RETURNS INT AS $$
DECLARE
v_dob DATE;
v_age INT;
BEGIN
-- Get the resident's date of birth
SELECT dateofbirth INTO v_dob
FROM resident
WHERE resident_id = p_resident_id;
-- Calculate the age
v_age := DATE_PART('year', AGE(CURRENT_DATE, v_dob));
RETURN v_age;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'Resident with ID % not found.', p_resident_id;
END;
$$ LANGUAGE plpgsql;בנינו GUI באמצעות python ע"י שימוש בספריית tkinter בשביל הגרפיקה ובספריית sqlalchemy בשביל התקשורת עם בסיס הנתונים. מפאת המקום (כ-1700 שורות) לא נראה את הקוד, אך נדגים את פעולותיו ונראה את התוצר הסופי.
אנחנו יצרנו 4 ממשקים בשביל המשתמשים השונים בבסיס הנתונים, כל אחד "מסתכל" על המידע הרלוונטי עבורו: ממשק עבור הדייר, ממשק עבור תחזוקה, ממשק עבור המשפחה של הדייר וממשק עבור המנהל.
בממשק זה אנו מצריכים את הדייר להיכנס למערכת (signin) ומאפשרים לו לצפות בפעילויות הפופולריות, בפעילויות המומלצות עבורו ונותנים לו את האפשרות להירשם לפעילויות או לבטל הרשמה.
כמו שניתן לראות, דייר בעל ת.ז. 123 נכנס למערכת ומופיעים עבורו הפעילויות אליהם הוא רשום, הפעילויות המומלצות עבורו, טופ 5 פעילויות פופולריות וכן הבקשות תחזוקה שביצע.
בנוסף לכך, נותנים לו אפשרות לבצע בקשת תחזוקה עבור החדר שבו הוא גר (הוא גם יכול לראות את הבקשות שביצע כבר)
בממשק זה אנו מצריכים את איש התחזוקה להיכנס למערכת (signin) ומאפשרים לו לצפות בבקשות התחזוקה שהוא יוכל לשייך אליו, לצפות בבקשות שכבר משויכות אליו (עם בר שמראה את ההתקדמות) ולראות את כל הפריטים הקיימים במלאי ולהפחית את הכמות במידה והוא השתמש בחלק מהפריטים.
לאחר שאיש התחזוקה בעל הת.ז. 604 נכנס, הוא יכול לראות את הבקשות תחזוקה שמשויכות אליו (ניתן גם לראות את בר ההתקדמות), את הבקשות שעדיין לא משויכות לאף אחד והוא יכול לקחת עליהם אחריות ובכך לעדכן את בסיס הנתונים. בנוסף הוא גם יכול לראות את המלאי של כל הפריטים ובמידת הצורך לעדכן כמויות מסוימות של פריטים וכך לעדכן את בסיס הנתונים.
בממשק זה אנו מצריכים את בן המשפחה להיכנס למערכת (signin) ומאפשרים לו לצפות במידע הרלוונטי על הדייר: את היסטוריית הטיפולים הרפואיים ואת הצוות הרפואי שמטפל בדייר.
כפי שניתן לראות, הוא יכול לראות את המידע הרלוונטי על הדייר.
בממשק זה צריך להיכנס עם שם משתמש וסיסמה של המנהל על מנת לראות את הגרפים שמתארים ביצועים של בית האבות.
לאחר שהמנהל נכנס, ישנם כמה גרפים שהוא מסוגל לראות: אחוזים של השתתפות בפעילויות, השתתפות לאורך זמן, כמות התפוסה של החדרים בבית האבות, leaderboard של העובדים החרוצים בתחומם (נמדד ע"פ מספר הבקשות שביצעו) וכמות החדרים באחוזים עם מספר תלונות גבוה.
