Lab 11 Report - PVFC Inventory Intelligence System

Project Overview

• This project is basically an inventory and order intelligence system built for PVFC.
• It helps understand customer buying behavior and predicts what products might be needed next.
• The system connects a .NET Web API backend with an ASP.NET Web Forms frontend.

How the System Works

• The frontend is built using ASP.NET Web Forms (VB.NET).
• The backend is an ASP.NET Core Web API that handles all data logic.
• SQL Server is used as the database for storing orders and products.
• The frontend calls API endpoints and receives data in JSON format.

Main API Features

• Product Recommendations: Suggests related products based on what customers usually buy together.
• Reorder Suggestions: Shows previously purchased items so customers can easily reorder them.
• Demand Forecasting: Predicts which secondary products may be in demand when a main product is selected.

1. Recommendation Feature

• API endpoint: /api/recommendations/{productId}
• It checks which products are commonly bought together in the same order.
• Then it returns a list of related products.
• This is used in the product page to suggest "You may also like" items.

2. Reorder Feature

• API endpoint: /api/reorder/{customerId}
• It fetches all products a customer has ordered in the past.
• These items are shown in the cart page as quick reorder suggestions.
• This makes it easier for customers to buy their regular items again.

3. Forecast Feature

• API endpoint: /api/forecast/{productId}
• It analyzes order history and finds which products are often bought with the selected product.
• It also counts how frequently those products appear together.
• This helps employees understand future demand and manage inventory better.

Frontend Integration

• The VB.NET frontend uses WebClient to call API endpoints.
• JSON responses are converted into tables or lists using Newtonsoft.Json.
• Data is shown using GridView and BulletedList controls.
• The employee dashboard displays forecast results for decision-making.

SQL Logic Used

• We used JOINs to connect orders, order lines, and products.
• Self-joins help find products bought together in the same order.
• COUNT and GROUP BY are used for forecasting demand.
• Parameterized queries are used to keep the system safe from SQL injection.