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";
    }

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";
    }

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;
    }

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.

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";
    }

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.");
    }