Context Navigation

← Previous Change
Wiki History
Next Change →

Changes between Version 3 and Version 4 of Advanced Application Development

Timestamp:: 09/18/25 16:11:07 (6 weeks ago)
Author:: 221531
Comment:: --

Legend:

: Unmodified
: Added
: Removed
: Modified

Advanced Application Development

-              v3
+              v4
 = Report Tables
 == Main Restaurant Report Summary Table
+{{{#!sql
 CREATE TABLE restaurant_weekly_report (
     report_id SERIAL PRIMARY KEY,
     restaurant_id INT NOT NULL,
     report_week DATE NOT NULL, -- Monday of the week
     week_number INT NOT NULL, -- Week 1, 2, or 3 (most recent)
+    report_week DATE NOT NULL,              -- Monday of the week
+    week_number INT NOT NULL,               -- Week 1, 2, or 3 (most recent)
     -- Order Statistics
 …
     -- Operational Metrics
     avg_preparation_time_minutes DECIMAL(8,2) DEFAULT 0.00,
     peak_hour INTEGER, -- Hour with most orders
     busiest_day INTEGER, -- Day of week (0=Sunday, 6=Saturday)
+    peak_hour INTEGER,                      -- Hour with most orders
+    busiest_day INTEGER,                    -- Day of week (0=Sunday, 6=Saturday)
     -- Performance Indicators
     completion_rate DECIMAL(5,2) DEFAULT 0.00, -- % of orders completed
+    completion_rate DECIMAL(5,2) DEFAULT 0.00,       -- % of orders completed
     customer_satisfaction DECIMAL(3,2) DEFAULT 0.00, -- Placeholder for ratings
     orders_per_hour DECIMAL(8,2) DEFAULT 0.00,
 …
     last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    CONSTRAINT fk_weekly_report_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
+    CONSTRAINT uq_restaurant_week UNIQUE (restaurant_id, report_week)
+    CONSTRAINT fk_weekly_report_restaurant
+        FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
+    CONSTRAINT uq_restaurant_week
+        UNIQUE (restaurant_id, report_week)
 );
+\\
+== Popular Items Report Table
+}}}
+\\
+== Popular items report
+{{{#!sql
 CREATE TABLE restaurant_popular_items (
     report_id SERIAL PRIMARY KEY,
 …
     item_id INT NOT NULL,
     item_name VARCHAR(100),
     times_ordered INTEGER DEFAULT 0,
     total_quantity INTEGER DEFAULT 0,
 …
     avg_price DECIMAL(8,2) DEFAULT 0.00,
     popularity_rank INTEGER,
     generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    CONSTRAINT fk_popular_items_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
+    CONSTRAINT fk_popular_items_item FOREIGN KEY (item_id) REFERENCES item(item_id)
+    CONSTRAINT fk_popular_items_restaurant
+        FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
+    CONSTRAINT fk_popular_items_item
+        FOREIGN KEY (item_id) REFERENCES item(item_id)
 );
+\\
+== Daily Breakdown Table
+}}}
+\\
+== Daily breakdown
+{{{#!sql
 CREATE TABLE restaurant_daily_breakdown (
     breakdown_id SERIAL PRIMARY KEY,
     restaurant_id INT NOT NULL,
     report_date DATE NOT NULL,
+    day_of_week INTEGER, -- 0=Sunday, 6=Saturday
+    day_of_week INTEGER,                     -- 0=Sunday, 6=Saturday
     daily_orders INTEGER DEFAULT 0,
     daily_revenue DECIMAL(10,2) DEFAULT 0.00,
 …
     -- Hourly breakdown (JSON format for flexibility)
     hourly_orders JSONB,
     generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    CONSTRAINT fk_daily_breakdown_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
+    CONSTRAINT uq_restaurant_daily UNIQUE (restaurant_id, report_date)
+    CONSTRAINT fk_daily_breakdown_restaurant
+        FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
+    CONSTRAINT uq_restaurant_daily
+        UNIQUE (restaurant_id, report_date)
 );
+\\
+== Comparative Analysis Table (Week Over Week)
+}}}
+\\
+== Comparative analysis table (week over week)
+{{{#!sql
 CREATE TABLE restaurant_trend_analysis (
     trend_id SERIAL PRIMARY KEY,
 …
     -- 3-week trends
     week1_revenue DECIMAL(10,2), -- Most recent week
+    week1_revenue DECIMAL(10,2),             -- Most recent week
     week2_revenue DECIMAL(10,2),
     week3_revenue DECIMAL(10,2),
     week1_orders INTEGER,
     week2_orders INTEGER,
 …
     -- Trend indicators
     revenue_trend VARCHAR(20), -- 'INCREASING', 'DECREASING', 'STABLE'
+    revenue_trend VARCHAR(20),               -- 'INCREASING', 'DECREASING', 'STABLE'
     order_trend VARCHAR(20),
     -- Growth rates
     revenue_growth_rate DECIMAL(8,2), -- Week over week %
+    revenue_growth_rate DECIMAL(8,2),        -- Week over week %
     order_growth_rate DECIMAL(8,2),
 …
     performance_alerts JSONB,
     recommendations JSONB,
     generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    CONSTRAINT fk_trend_analysis_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id)
+    CONSTRAINT fk_trend_analysis_restaurant
+        FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id)
 );
+\\
+== Performance Metrics Table
+}}}
+\\
+== Performance metrics table
+{{{#!sql
 CREATE TABLE performance_metrics (
     metric_id SERIAL PRIMARY KEY,
 …
     metric_value DECIMAL(8,2),
     recorded_at TIMESTAMP,
+    CONSTRAINT fk_performance_metrics_restaurant FOREIGN KEY (restaurant_id) REFERENCES RESTAURANT(restaurant_id)
+    CONSTRAINT fk_performance_metrics_restaurant
+        FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id)
 );
+\\
+== Audit Table for Orders
+}}}
+\\
+== Audit table for orders
+{{{#!sql
 CREATE TABLE audit_orders (
     audit_id SERIAL PRIMARY KEY,
 …
     change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
+\\
+\\
+}}}
+\\
 = Utility Functions
+\\
+== Get Week Start Function
+== Function to get the Monday of a given week
+{{{#!sql
 CREATE OR REPLACE FUNCTION get_week_start(input_date DATE)
     RETURNS DATE AS $$
+RETURNS DATE AS $$
 BEGIN
     RETURN input_date - INTERVAL '1 day' * EXTRACT(DOW FROM input_date);
 END;
 $$ LANGUAGE plpgsql;
+== Get Week Number Function
+}}}
+\\
+== Function to get week number (1, 2, or 3 for last 3 weeks)
+{{{#!sql
 CREATE OR REPLACE FUNCTION get_week_number(input_date DATE)
     RETURNS INTEGER AS $$
+RETURNS INTEGER AS $$
 DECLARE
     week_start DATE;
 …
 END;
 $$ LANGUAGE plpgsql;
+==  Main Report Generation Function
+CREATE OR REPLACE FUNCTION generate_restaurant_weekly_report(p_restaurant_id INT, p_target_date DATE DEFAULT CURRENT_DATE)
+    RETURNS void AS $$
+}}}
+\\
+== Main report generation function
+{{{#!sql
+CREATE OR REPLACE FUNCTION generate_restaurant_weekly_report(
+    p_restaurant_id INT,
+    p_target_date DATE DEFAULT CURRENT_DATE
+)
+RETURNS void AS $$
 DECLARE
     week_start DATE;
 …
     INTO v_total_items_sold
     FROM order_items oi
         JOIN orders o ON oi.order_id = o.order_id
+    JOIN orders o ON oi.order_id = o.order_id
     WHERE o.restaurant_id = p_restaurant_id
       AND o.order_date::date BETWEEN week_start AND week_end
       AND o.order_status = 'delivered';
     -- Calculate average preparation time (using performance metrics if available)
+    -- Calculate average preparation time (if available)
     SELECT COALESCE(AVG(metric_value), 0)
     INTO v_avg_prep_time
 …
     LIMIT 1;
     -- Find busiest day of week
+    -- Find busiest day
     SELECT EXTRACT(dow FROM order_date)
     INTO v_busiest_day
 …
     LIMIT 1;
     -- Calculate completion rate
+    -- Completion rate
     v_completion_rate := CASE
         WHEN v_total_orders > 0 THEN
 …
     END;
     -- Calculate orders per hour (during operating hours)
+    -- Orders per hour (during operating hours)
     WITH operating_hours AS (
+        SELECT
+            EXTRACT(EPOCH FROM (closing_time - opening_time))/3600 * 7 as total_hours_week
+        SELECT EXTRACT(EPOCH FROM (closing_time - opening_time)) / 3600 * 7 AS total_hours_week
         FROM restaurant
         WHERE restaurant_id = p_restaurant_id
+    )
     SELECT CASE
+        WHEN oh.total_hours_week > 0 THEN v_total_orders::DECIMAL / oh.total_hours_week
+        ELSE 0
+    END
+               WHEN oh.total_hours_week > 0
+               THEN v_total_orders::DECIMAL / oh.total_hours_week
+               ELSE 0
+           END
     INTO v_orders_per_hour
     FROM operating_hours oh;
     -- Insert or update the weekly report
+    -- Insert or update weekly report
     INSERT INTO restaurant_weekly_report (
         restaurant_id, report_week, week_number,
 …
         last_updated = CURRENT_TIMESTAMP;
     -- Generate popular items report
+    -- Generate supporting reports
     PERFORM generate_popular_items_report(p_restaurant_id, week_start);
-    -- Generate daily breakdown
     PERFORM generate_daily_breakdown_report(p_restaurant_id, week_start, week_end);
 END;
 $$ LANGUAGE plpgsql;
+== Popular Items Report Function
+CREATE OR REPLACE FUNCTION generate_popular_items_report(p_restaurant_id INT, p_week_start DATE)
+    RETURNS void AS $$
+}}}
+\\
+== Popular items report function
+{{{#!sql
+CREATE OR REPLACE FUNCTION generate_popular_items_report(
+    p_restaurant_id INT,
+    p_week_start DATE
+)
+RETURNS void AS $$
 BEGIN
     -- Clear existing data for this week
     DELETE FROM restaurant_popular_items
+    WHERE restaurant_id = p_restaurant_id AND report_week = p_week_start;
+    -- Insert top 10 popular items for the week
+    WHERE restaurant_id = p_restaurant_id
+      AND report_week = p_week_start;
+    -- Insert top 10 popular items
     INSERT INTO restaurant_popular_items (
         restaurant_id, report_week, item_id, item_name,
 …
         SELECT
             i.item_id,
             i.name as item_name,
             COUNT(DISTINCT o.order_id) as times_ordered,
             SUM(oi.quantity) as total_quantity,
             SUM(oi.total_price) as total_revenue,
             AVG(oi.total_price / oi.quantity) as avg_price,
             ROW_NUMBER() OVER (ORDER BY COUNT(DISTINCT o.order_id) DESC) as popularity_rank
+            i.name AS item_name,
+            COUNT(DISTINCT o.order_id) AS times_ordered,
+            SUM(oi.quantity) AS total_quantity,
+            SUM(oi.total_price) AS total_revenue,
+            AVG(oi.total_price / oi.quantity) AS avg_price,
+            ROW_NUMBER() OVER (ORDER BY COUNT(DISTINCT o.order_id) DESC) AS popularity_rank
         FROM orders o
             JOIN order_items oi ON o.order_id = oi.order_id
             JOIN item i ON oi.item_id = i.item_id
+        JOIN order_items oi ON o.order_id = oi.order_id
+        JOIN item i ON oi.item_id = i.item_id
         WHERE o.restaurant_id = p_restaurant_id
           AND o.order_date::date BETWEEN p_week_start AND p_week_start + INTERVAL '6 days'
 …
 END;
 $$ LANGUAGE plpgsql;
+== Trend Analysis Function
+}}}
+\\
+== Trend analysis function
+{{{#!sql
 CREATE OR REPLACE FUNCTION generate_trend_analysis(p_restaurant_id INT)
     RETURNS void AS $$
+RETURNS void AS $$
 DECLARE
     week1_start DATE := get_week_start(CURRENT_DATE);
 …
 BEGIN
     -- Get weekly data
+    SELECT gross_revenue, total_orders INTO w1_revenue, w1_orders
+    SELECT gross_revenue, total_orders
+    INTO w1_revenue, w1_orders
     FROM restaurant_weekly_report
     WHERE restaurant_id = p_restaurant_id AND report_week = week1_start;
+    SELECT gross_revenue, total_orders INTO w2_revenue, w2_orders
+    SELECT gross_revenue, total_orders
+    INTO w2_revenue, w2_orders
     FROM restaurant_weekly_report
     WHERE restaurant_id = p_restaurant_id AND report_week = week2_start;
+    SELECT gross_revenue, total_orders INTO w3_revenue, w3_orders
+    SELECT gross_revenue, total_orders
+    INTO w3_revenue, w3_orders
     FROM restaurant_weekly_report
     WHERE restaurant_id = p_restaurant_id AND report_week = week3_start;
     -- Calculate trends
+    -- Growth calculations
     IF w2_revenue > 0 THEN
         revenue_growth := ROUND(((w1_revenue - w2_revenue) / w2_revenue) * 100, 2);
 …
     END IF;
     -- Determine trend direction
+    -- Trend direction
     revenue_trend_val := CASE
         WHEN revenue_growth > 5 THEN 'INCREASING'
 …
     END;
     -- Generate alerts
+    -- Alerts
     IF revenue_growth < -20 THEN
         alerts := alerts || '{"type": "revenue_decline", "severity": "high", "message": "Revenue declined by more than 20% this week"}'::jsonb;
+        alerts := alerts || '{"type":"revenue_decline","severity":"high","message":"Revenue declined by more than 20% this week"}'::jsonb;
     END IF;
     IF order_growth < -15 THEN
         alerts := alerts || '{"type": "order_decline", "severity": "medium", "message": "Order volume declined by more than 15% this week"}'::jsonb;
     END IF;
     -- Generate recommendations
+        alerts := alerts || '{"type":"order_decline","severity":"medium","message":"Order volume declined by more than 15% this week"}'::jsonb;
+    END IF;
+    -- Recommendations
     IF revenue_trend_val = 'DECREASING' THEN
         recommendations := recommendations || '{"type": "marketing", "action": "Consider promotional campaigns or menu updates"}'::jsonb;
+        recommendations := recommendations || '{"type":"marketing","action":"Consider promotional campaigns or menu updates"}'::jsonb;
     END IF;
     IF order_trend_val = 'DECREASING' THEN
         recommendations := recommendations || '{"type": "operations", "action": "Review menu pricing and delivery times"}'::jsonb;
     END IF;
     -- Insert trend analysis
+        recommendations := recommendations || '{"type":"operations","action":"Review menu pricing and delivery times"}'::jsonb;
+    END IF;
+    -- Insert or update
     INSERT INTO restaurant_trend_analysis (
         restaurant_id, week1_revenue, week2_revenue, week3_revenue,
 …
 END;
 $$ LANGUAGE plpgsql;
+}}}
+\\
 = Trigger Functions
+== Update Restaurant Reports Trigger Function
+== Trigger function to update reports when orders change
+{{{#!sql
 CREATE OR REPLACE FUNCTION update_restaurant_reports()
     RETURNS TRIGGER AS $$
+RETURNS TRIGGER AS $$
 DECLARE
     affected_restaurant_id INT;
 …
         -- Update reports for the affected week
         PERFORM generate_restaurant_weekly_report(affected_restaurant_id, order_date_to_check);
         -- Update trend analysis
         PERFORM generate_trend_analysis(affected_restaurant_id);
 …
 END;
 $$ LANGUAGE plpgsql;
+== Update Reports on Items Change Trigger Function
+}}}
+\\
+== Trigger function for order items changes
+{{{#!sql
 CREATE OR REPLACE FUNCTION update_reports_on_items_change()
     RETURNS TRIGGER AS $$
+RETURNS TRIGGER AS $$
 DECLARE
     affected_restaurant_id INT;
 …
 BEGIN
     -- Get restaurant and date from the order
+    SELECT restaurant_id, order_date::date INTO affected_restaurant_id, order_date_to_check
+    FROM orders WHERE order_id = COALESCE(NEW.order_id, OLD.order_id);
+    SELECT restaurant_id, order_date::date
+    INTO affected_restaurant_id, order_date_to_check
+    FROM orders
+    WHERE order_id = COALESCE(NEW.order_id, OLD.order_id);
     -- Only update if within the last 3 weeks
 …
 END;
 $$ LANGUAGE plpgsql;
+== Create Triggers
+}}}
+\\
+== Create triggers
+{{{#!sql
 -- Trigger on orders table
 CREATE TRIGGER trg_update_restaurant_reports
     AFTER INSERT OR UPDATE OR DELETE ON orders
     FOR EACH ROW
+AFTER INSERT OR UPDATE OR DELETE ON orders
+FOR EACH ROW
 EXECUTE FUNCTION update_restaurant_reports();
 -- Trigger on order_items table
 CREATE TRIGGER trg_update_reports_items
     AFTER INSERT OR UPDATE OR DELETE ON order_items
     FOR EACH ROW
+AFTER INSERT OR UPDATE OR DELETE ON order_items
+FOR EACH ROW
 EXECUTE FUNCTION update_reports_on_items_change();
+== Report Views for Easy Access
+Comprehensive 3-Week Restaurant Report View
+}}}
+\\
+== Report Views
+{{{#!sql
+-- Comprehensive 3-week restaurant report view
 CREATE VIEW restaurant_3week_summary AS
 SELECT
 …
     MAX(CASE WHEN rwr.week_number = 1 THEN rwr.completion_rate END) as current_week_completion_rate,
     -- Previous week (Week 2)
+    -- Previous week (Week 2)
     MAX(CASE WHEN rwr.week_number = 2 THEN rwr.total_orders END) as prev_week_orders,
     MAX(CASE WHEN rwr.week_number = 2 THEN rwr.gross_revenue END) as prev_week_revenue,
 …
     MAX(CASE WHEN rwr.week_number = 1 THEN rwr.busiest_day END) as current_busiest_day,
     AVG(rwr.avg_preparation_time_minutes) as avg_prep_time,
     MAX(rwr.last_updated) as last_updated
 FROM restaurant_weekly_report rwr
     JOIN restaurant r ON rwr.restaurant_id = r.restaurant_id
+JOIN restaurant r ON rwr.restaurant_id = r.restaurant_id
 WHERE rwr.report_week >= CURRENT_DATE - INTERVAL '21 days'
 GROUP BY r.name, rwr.restaurant_id;
+== Top Performing Restaurants View
+}}}
+{{{#!sql
+-- Top performing restaurants view
 CREATE VIEW top_restaurants_3weeks AS
 SELECT
 …
     rta.revenue_growth_rate
 FROM restaurant_weekly_report rwr
     JOIN restaurant r ON rwr.restaurant_id = r.restaurant_id
     LEFT JOIN restaurant_trend_analysis rta ON rwr.restaurant_id = rta.restaurant_id
+JOIN restaurant r ON rwr.restaurant_id = r.restaurant_id
+LEFT JOIN restaurant_trend_analysis rta ON rwr.restaurant_id = rta.restaurant_id
 WHERE rwr.report_week >= CURRENT_DATE - INTERVAL '21 days'
 GROUP BY r.name, rta.revenue_trend, rta.revenue_growth_rate
 ORDER BY total_revenue DESC;
+== Daily Reports Function
+}}}
+== Daily reports function
+{{{#!sql
 CREATE OR REPLACE FUNCTION generate_daily_breakdown_report(
     p_restaurant_id INT,
     p_week_start    DATE,
     p_week_end      DATE
+    p_week_start DATE,
+    p_week_end DATE
 ) RETURNS void
     LANGUAGE plpgsql
+LANGUAGE plpgsql
 AS $$
 DECLARE
     v_day              DATE := p_week_start;
     v_daily_orders     INTEGER := 0;
     v_daily_revenue    NUMERIC(12,2) := 0;
+    v_day DATE := p_week_start;
+    v_daily_orders INTEGER := 0;
+    v_daily_revenue NUMERIC(12,2) := 0;
     v_daily_items_sold INTEGER := 0;
     v_avg_order_value  NUMERIC(12,2) := 0;
     v_hourly_orders    JSONB := '{}'::jsonb;
+    v_avg_order_value NUMERIC(12,2) := 0;
+    v_hourly_orders JSONB := '{}'::jsonb;
 BEGIN
     IF p_week_end < p_week_start THEN
 …
     END IF;
+    -- Clear previous data
     DELETE FROM restaurant_daily_breakdown
     WHERE restaurant_id = p_restaurant_id
       AND report_date BETWEEN p_week_start AND p_week_end;
+    -- Loop through days
     WHILE v_day <= p_week_end LOOP
         -- Daily metrics
 …
         INTO v_daily_items_sold
         FROM order_items oi
             JOIN orders o ON oi.order_id = o.order_id
+        JOIN orders o ON oi.order_id = o.order_id
         WHERE o.restaurant_id = p_restaurant_id
           AND o.order_date::date = v_day
 …
         -- Next day
         v_day := v_day + 1;  -- date + integer yields date
+        v_day := v_day + 1;
     END LOOP;
 END;
 $$;
+== Constraint Addition
+}}}
+{{{#!sql
 ALTER TABLE restaurant_trend_analysis
+    ADD CONSTRAINT uq_restaurant_id UNIQUE (restaurant_id);
+ADD CONSTRAINT uq_restaurant_id UNIQUE (restaurant_id);
+}}}