Changes between Version 1 and Version 2 of ApplicationDevelopment

Timestamp:

09/04/25 22:38:20 (3 days ago)

Author:

221164

Comment:

--

ApplicationDevelopment

@@ -1 @@
-== Advanced Application Development
+= Advanced Application Development =
+In the latest version of the application, the following scenarios have been implemented:
+||= ID =||= Use Case =||
+|| 1 || View and Manage Employees ||
+|| 2 || View and Manage Shifts ||
+|| 3 || Assign Employees to Shifts ||
+|| 4 || View and Manage Menu Categories ||
+|| 5 || View and Manage Menu Products ||
+|| 6 || View and Manage Restaurant Tables ||
+|| 7 || Create and Manage Customer Reservations ||
+|| 8 || Create and Manage In-House Orders (Tabs) ||
+|| 9 || Add/Update/Remove Items from an Order ||
+|| 10 || Process a Payment for an Order ||
+|| 11 || View Open Orders ||
+|| 12 || View Personal Shift Information (Employee) ||
+|| 13 || View Sales Analytics and Reports ||
+|| 14 || View Server Performance Statistics ||
+|| 15 || View Monthly Revenue vs. Labor Costs ||
+|| 16 || View Top-Selling Products ||
+|| 17 || Manage Customer Profile and View Order History (Online) ||
+|| 18 || Create an Online Order ||
+
+== Assign Employees to Shifts
+A manager accesses the /admin/assignments page, which displays all scheduled shifts and a form to assign employees to them. The controller responsible for this is as follows:
+
+{{{
+    @GetMapping("/admin/assignments")
+    public String showAssignments(Model model) {
+        model.addAttribute("shifts", shiftService.getAllShifts());
+        model.addAttribute("employees", employeeService.getAllEmployees());
+        model.addAttribute("assignments", assignmentService.getAllAssignments());
+        model.addAttribute("assignmentForm", new CreateAssignmentDto());
+        // In a real app, this would likely be part of a larger admin dashboard layout
+        return "admin/assignments";
+    }
+}}}
+
+[[Image(shifts.png)]]
+[[Image(assignments.png)]]
+
+After selecting a shift, an employee, and submitting the form, a POST request is sent to the controller for processing.
+
+{{{
+
+    @PostMapping("/admin/assignments")
+    public String createAssignment(@Valid @ModelAttribute("assignmentForm") CreateAssignmentDto dto) {
+        // The authenticated user's email would be retrieved from the SecurityContext
+        String managerEmail = SecurityContextHolder.getContext().getAuthentication().getName();
+        assignmentService.createAssignment(dto, managerEmail);
+        return "redirect:/admin/assignments";
+    }
+}}}
+The controller calls a function from the assignmentService to create the assignment. Inside the service, a new instance of the Assignment entity is created and populated with data from the form. To ensure data integrity (an assignment cannot exist without a valid shift, employee, and manager), the method is annotated with @Transactional. The operation will only succeed if the new Assignment object is saved to the database correctly.
+
+{{{
+
+    @Override
+    @Transactional
+    public Assignment createAssignment(CreateAssignmentDto dto, String managerEmail) {
+        Manager manager = managerRepository.findByUserEmail(managerEmail)
+                .orElseThrow(() -> new EntityNotFoundException("Manager not found"));
+        Employee employee = employeeRepository.findById(dto.getEmployeeId())
+                .orElseThrow(() -> new EntityNotFoundException("Employee not found"));
+        Shift shift = shiftRepository.findById(dto.getShiftId())
+                .orElseThrow(() -> new EntityNotFoundException("Shift not found"));
+
+        Assignment assignment = new Assignment();
+        assignment.setManager(manager);
+        assignment.setEmployee(employee);
+        assignment.setShift(shift);
+        // Clock-in/out times are initially null
+
+        return assignmentRepository.save(assignment);
+    }
+}}}
+
+The same implementation, translated into the SQL query that executes in the background, would look like this:
+
+{{{
+
+DO $$
+    DECLARE
+        v_manager_id BIGINT;
+        v_employee_id BIGINT;
+        v_shift_id BIGINT;
+    BEGIN
+        -- Assume manager_id=3, employee_id=1, shift_id=1 from the form
+        v_manager_id := 3;
+        v_employee_id := 1;
+        v_shift_id := 1;
+
+        INSERT INTO assignments (manager_id, employee_id, shift_id, clock_in_time, clock_out_time)
+        VALUES (v_manager_id, v_employee_id, v_shift_id, NULL, NULL);
+
+        COMMIT;
+    END $$;
+}}}
+== Create and Manage In-House Orders (Tabs)
+A server (Front Staff) who has clocked into their shift can create and manage orders for tables. When they select a table, they are presented with an interface to create a new order or manage an existing one. The controller first fetches all open orders.
+
+{{{
+
+    @GetMapping("/orders/open")
+    public String getOpenOrders(Model model) {
+        model.addAttribute("openOrders", orderService.findOpenOrders());
+        return "orders/open_orders";
+    }
+}}}
+[[Image(open-orders.png)]]
+
+To prevent unauthorized actions, the system validates all operations on the server side. When adding an item to an order, the system first verifies that the order exists and that the employee is active.
+
+{{{
+
+    @PostMapping("/orders/{orderId}/items")
+    public String addItemToOrder(@PathVariable Long orderId, @Valid CreateOrderItemDto itemDto, Authentication authentication) {
+        UserDetails userDetails = (UserDetails) authentication.getPrincipal();
+        // Custom logic to get employeeId from userDetails
+        Long employeeId = userService.findEmployeeIdByUsername(userDetails.getUsername());
+
+        if (!employeeService.isOnActiveShift(employeeId)) {
+            throw new IllegalStateException("Employee is not on an active shift.");
+        }
+
+        orderService.addItemToOrder(orderId, itemDto);
+        return "redirect:/orders/" + orderId;
+    }
+}}}
+[[Image(order-details.png)]]
+[[Image(add-items-product.png)]]
+
+The orderService handles the business logic of finding the product, calculating the price, and adding the new OrderItem to the Order. This entire process is wrapped in a transaction to ensure that an order item is not created unless all conditions are met (e.g., product exists, order is open).
+
+{{{
+
+    @Override
+    @Transactional
+    public OrderItem addItemToOrder(Long orderId, CreateOrderItemDto itemDto) {
+        Order order = orderRepository.findById(orderId)
+                .orElseThrow(() -> new EntityNotFoundException("Order not found"));
+        if (!"PENDING".equals(order.getStatus())) {
+            throw new IllegalStateException("Order is not open for modifications.");
+        }
+        Product product = productRepository.findById(itemDto.getProductId())
+                .orElseThrow(() -> new EntityNotFoundException("Product not found"));
+
+        OrderItem newItem = new OrderItem();
+        newItem.setOrder(order);
+        newItem.setProduct(product);
+        newItem.setQuantity(itemDto.getQuantity());
+        newItem.setPrice(product.getPrice()); // Price at the time of order
+        newItem.setProcessed(false);
+
+        return orderItemRepository.save(newItem);
+    }
+}}}
+The corresponding SQL operations for creating a new tab order and adding two items would be:
+
+{{{
+
+DO $$
+    DECLARE
+        new_order_id BIGINT;
+        v_product_1_price DECIMAL(10,2);
+        v_product_2_price DECIMAL(10,2);
+    BEGIN
+        -- Create the main order record
+        INSERT INTO orders (status, datetime) VALUES ('PENDING', NOW())
+        RETURNING id INTO new_order_id;
+
+        -- Link it as a Tab Order for a specific staff and table
+        INSERT INTO tab_orders (order_id, front_staff_id, table_number)
+        VALUES (new_order_id, 1, 2);
+
+        -- Get current prices
+        SELECT price INTO v_product_1_price FROM products WHERE id = 1;
+        SELECT price INTO v_product_2_price FROM products WHERE id = 2;
+
+        -- Add items to the order
+        INSERT INTO order_items (order_id, product_id, is_processed, quantity, price)
+        VALUES (new_order_id, 1, FALSE, 2, v_product_1_price);
+
+        INSERT INTO order_items (order_id, product_id, is_processed, quantity, price)
+        VALUES (new_order_id, 2, FALSE, 1, v_product_2_price);
+
+        COMMIT;
+    END $$;
+}}}
+== Process a Payment for an Order
+When customers are ready to pay, the server initiates the payment process from the order details screen.
+
+[[Image(payment.png)]]
+
+This action is managed via a database trigger for maximum reliability. When a new record is inserted into the payments table, the payments_mark_order_paid trigger automatically fires. This ensures that an order's status is updated to PAID in the same transaction as the payment, guaranteeing that an order cannot be paid for without its status being updated accordingly.
+
+{{{
+
+    @Override
+    @Transactional // The trigger makes this transaction encompass the order update as well
+    public Payment createPayment(CreatePaymentDto dto) {
+        Order order = orderRepository.findById(dto.getOrderId())
+            .orElseThrow(() -> new EntityNotFoundException("Order not found"));
+
+        // Additional logic can go here, e.g., validating the payment amount
+        // against the order total.
+
+        Payment payment = new Payment();
+        payment.setOrder(order);
+        payment.setAmount(dto.getAmount());
+        payment.setPaymentType(dto.getPaymentType());
+        payment.setTipAmount(dto.getTipAmount());
+
+        return paymentRepository.save(payment);
+    }
+}}}
+The trigger and the INSERT statement are defined in SQL as follows:
+
+{{{
+
+CREATE OR REPLACE FUNCTION payments_mark_order_paid() RETURNS trigger AS $$
+BEGIN
+    UPDATE orders
+    SET status = 'PAID'
+    WHERE id = NEW.order_id;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE TRIGGER trg_payments_mark_order_paid
+    AFTER INSERT ON payments
+    FOR EACH ROW EXECUTE FUNCTION payments_mark_order_paid();
+}}}
+{{{
+-- The SQL executed by the application:
+
+INSERT INTO payments(order_id, amount, payment_type, tip_amount)
+VALUES (1, 850.00, 'cash', 50.00);
+}}}
+
+== View Sales Analytics and Reports
+The public page /analytics provides data on sales performance, with options to filter by different criteria. Given that the orders, order_items, and payments tables will grow to contain a massive number of records, running complex aggregations on every page load would heavily strain the database.
+
+To solve this, we implemented a Materialized View (mv_payments_daily_channel) that is refreshed periodically. In our case, this could be every 30 minutes. This way, instead of the application overloading the Database Engine with expensive queries, it serves pre-calculated data directly from the view. The view is defined as follows:
+
+{{{
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS mv_payments_daily_channel AS
+WITH orders_channel AS (
+  SELECT
+    o.id AS order_id,
+    CASE
+      WHEN EXISTS (SELECT 1 FROM tab_orders t WHERE t.order_id = o.id) THEN 'TAB'
+      WHEN EXISTS (SELECT 1 FROM online_orders oo WHERE oo.order_id = o.id) THEN 'ONLINE'
+      ELSE 'UNKNOWN'
+    END AS channel
+  FROM orders o
+)
+SELECT
+    (date_trunc('day', p.created_at))::date AS day,
+    oc.channel,
+    COUNT(DISTINCT p.order_id) AS paid_orders_cnt,
+    SUM(p.amount)::numeric(14,2) AS revenue,
+    SUM(p.tip_amount)::numeric(14,2) AS tip_total
+FROM payments p
+JOIN orders_channel oc ON oc.order_id = p.order_id
+GROUP BY (date_trunc('day', p.created_at))::date, oc.channel;
+}}}
+When a user accesses the analytics page, the controller queries this simple, fast materialized view.
+
+{{{
+
+    @GetMapping("/analytics/by-channel")
+    public String getPaymentsByChannel(Model model,
+                                         @RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate from,
+                                         @RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate to) {
+        LocalDate fromDate = (from == null) ? LocalDate.now().minusDays(30) : from;
+        LocalDate toDate = (to == null) ? LocalDate.now() : to;
+
+        // This service method reads from the materialized view
+        AnalyticsByChannelResponse response = analyticsService.getPaymentsDailyChannel(fromDate, toDate);
+
+        model.addAttribute("analyticsData", response);
+        model.addAttribute("replaceTemplate", "analytics_dashboard");
+        return "index";
+    }
+}}}
+[[Image(analytics-1.png)]]
+[[Image(monthly-revenue-split-orders.png)]]
+
+To keep the data in the materialized view up-to-date, a scheduled method is implemented in the application. This method runs every 30 minutes and executes the refresh command.
+
+
+{{{
+
+    @Scheduled(cron = "0 */30 * * * *") // Runs every 30 minutes
+    public void refreshPaymentAnalytics() {
+        // This repository method executes a native query: "REFRESH MATERIALIZED VIEW mv_payments_daily_channel;"
+        analyticsRepository.refreshDailyChannelView();
+        log.info("Materialized view mv_payments_daily_channel has been refreshed.");
+    }
+}}}