Spaces:
Running
Running
You are an inventory management expert. Design a web dashboard that: 1. Connects to a parts database (SQL schema). 2. Imports monthly sales history. 3. Uses ARIMA/LSTM to forecast next 3–6 months demand. 4. Sends email/SMS alerts when projected stock < threshold. Provide DB schema, Python scripts for forecasting, and frontend wireframes. - Initial Deployment
3328dd3
verified
| <html lang="en"> | |
| <head> | |
| <meta charset="UTF-8"> | |
| <meta name="viewport" content="width=device-width, initial-scale=1.0"> | |
| <title>Inventory Forecast Dashboard</title> | |
| <script src="https://cdn.tailwindcss.com"></script> | |
| <script src="https://cdn.jsdelivr.net/npm/chart.js"></script> | |
| <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css"> | |
| <style> | |
| .sidebar { | |
| transition: all 0.3s; | |
| } | |
| .dashboard-content { | |
| transition: all 0.3s; | |
| } | |
| .card-hover:hover { | |
| transform: translateY(-5px); | |
| box-shadow: 0 10px 20px rgba(0,0,0,0.1); | |
| } | |
| .progress-bar { | |
| height: 6px; | |
| border-radius: 3px; | |
| } | |
| .notification-badge { | |
| position: absolute; | |
| top: -5px; | |
| right: -5px; | |
| } | |
| .chart-container { | |
| position: relative; | |
| height: 300px; | |
| } | |
| @media (max-width: 768px) { | |
| .sidebar { | |
| position: absolute; | |
| z-index: 100; | |
| left: -100%; | |
| } | |
| .sidebar.active { | |
| left: 0; | |
| } | |
| .dashboard-content { | |
| margin-left: 0 ; | |
| } | |
| } | |
| </style> | |
| </head> | |
| <body class="bg-gray-100 font-sans"> | |
| <div class="flex h-screen overflow-hidden"> | |
| <!-- Sidebar --> | |
| <div class="sidebar bg-indigo-800 text-white w-64 flex-shrink-0"> | |
| <div class="p-4 flex items-center justify-between border-b border-indigo-700"> | |
| <div class="flex items-center"> | |
| <i class="fas fa-boxes text-2xl mr-3"></i> | |
| <span class="text-xl font-bold">StockForecast</span> | |
| </div> | |
| <button id="sidebarToggle" class="md:hidden text-white"> | |
| <i class="fas fa-times"></i> | |
| </button> | |
| </div> | |
| <nav class="p-4"> | |
| <div class="mb-8"> | |
| <p class="text-indigo-300 uppercase text-xs font-bold mb-3">Main</p> | |
| <a href="#" class="flex items-center py-2 px-3 bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-tachometer-alt mr-3"></i> | |
| <span>Dashboard</span> | |
| </a> | |
| <a href="#" class="flex items-center py-2 px-3 hover:bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-box-open mr-3"></i> | |
| <span>Inventory</span> | |
| </a> | |
| <a href="#" class="flex items-center py-2 px-3 hover:bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-chart-line mr-3"></i> | |
| <span>Forecasting</span> | |
| </a> | |
| <a href="#" class="flex items-center py-2 px-3 hover:bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-bell mr-3"></i> | |
| <span>Alerts</span> | |
| <span class="notification-badge bg-red-500 text-white text-xs rounded-full h-5 w-5 flex items-center justify-center ml-auto">3</span> | |
| </a> | |
| </div> | |
| <div class="mb-8"> | |
| <p class="text-indigo-300 uppercase text-xs font-bold mb-3">Management</p> | |
| <a href="#" class="flex items-center py-2 px-3 hover:bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-users mr-3"></i> | |
| <span>Suppliers</span> | |
| </a> | |
| <a href="#" class="flex items-center py-2 px-3 hover:bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-file-import mr-3"></i> | |
| <span>Imports</span> | |
| </a> | |
| <a href="#" class="flex items-center py-2 px-3 hover:bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-cog mr-3"></i> | |
| <span>Settings</span> | |
| </a> | |
| </div> | |
| <div class="mb-4"> | |
| <p class="text-indigo-300 uppercase text-xs font-bold mb-3">Reports</p> | |
| <a href="#" class="flex items-center py-2 px-3 hover:bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-file-alt mr-3"></i> | |
| <span>Monthly</span> | |
| </a> | |
| <a href="#" class="flex items-center py-2 px-3 hover:bg-indigo-700 rounded-lg mb-2"> | |
| <i class="fas fa-chart-pie mr-3"></i> | |
| <span>Analytics</span> | |
| </a> | |
| </div> | |
| </nav> | |
| </div> | |
| <!-- Main Content --> | |
| <div class="dashboard-content flex-1 overflow-auto"> | |
| <!-- Top Navigation --> | |
| <header class="bg-white shadow-sm p-4 flex items-center justify-between"> | |
| <div class="flex items-center"> | |
| <button id="mobileSidebarToggle" class="md:hidden text-gray-600 mr-4"> | |
| <i class="fas fa-bars text-xl"></i> | |
| </button> | |
| <h1 class="text-xl font-semibold text-gray-800">Inventory Forecast Dashboard</h1> | |
| </div> | |
| <div class="flex items-center"> | |
| <div class="relative mr-4"> | |
| <input type="text" placeholder="Search..." class="pl-10 pr-4 py-2 border rounded-lg focus:outline-none focus:ring-2 focus:ring-indigo-500"> | |
| <i class="fas fa-search absolute left-3 top-3 text-gray-400"></i> | |
| </div> | |
| <div class="relative"> | |
| <img src="https://randomuser.me/api/portraits/women/44.jpg" alt="User" class="w-10 h-10 rounded-full cursor-pointer"> | |
| <span class="absolute bottom-0 right-0 bg-green-500 rounded-full w-3 h-3 border-2 border-white"></span> | |
| </div> | |
| </div> | |
| </header> | |
| <!-- Dashboard Content --> | |
| <main class="p-6"> | |
| <!-- Stats Cards --> | |
| <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-6 mb-6"> | |
| <div class="bg-white rounded-xl shadow-sm p-6 card-hover transition-all duration-300"> | |
| <div class="flex items-center justify-between"> | |
| <div> | |
| <p class="text-gray-500 text-sm">Total Items</p> | |
| <h3 class="text-2xl font-bold text-gray-800">1,248</h3> | |
| <p class="text-green-500 text-sm mt-1"><i class="fas fa-caret-up mr-1"></i> 12% from last month</p> | |
| </div> | |
| <div class="bg-indigo-100 p-3 rounded-full"> | |
| <i class="fas fa-boxes text-indigo-600 text-xl"></i> | |
| </div> | |
| </div> | |
| </div> | |
| <div class="bg-white rounded-xl shadow-sm p-6 card-hover transition-all duration-300"> | |
| <div class="flex items-center justify-between"> | |
| <div> | |
| <p class="text-gray-500 text-sm">Low Stock Items</p> | |
| <h3 class="text-2xl font-bold text-gray-800">47</h3> | |
| <p class="text-red-500 text-sm mt-1"><i class="fas fa-caret-up mr-1"></i> 5% from last month</p> | |
| </div> | |
| <div class="bg-red-100 p-3 rounded-full"> | |
| <i class="fas fa-exclamation-triangle text-red-600 text-xl"></i> | |
| </div> | |
| </div> | |
| </div> | |
| <div class="bg-white rounded-xl shadow-sm p-6 card-hover transition-all duration-300"> | |
| <div class="flex items-center justify-between"> | |
| <div> | |
| <p class="text-gray-500 text-sm">Forecast Accuracy</p> | |
| <h3 class="text-2xl font-bold text-gray-800">87%</h3> | |
| <p class="text-green-500 text-sm mt-1"><i class="fas fa-caret-up mr-1"></i> 2% improvement</p> | |
| </div> | |
| <div class="bg-green-100 p-3 rounded-full"> | |
| <i class="fas fa-chart-line text-green-600 text-xl"></i> | |
| </div> | |
| </div> | |
| </div> | |
| <div class="bg-white rounded-xl shadow-sm p-6 card-hover transition-all duration-300"> | |
| <div class="flex items-center justify-between"> | |
| <div> | |
| <p class="text-gray-500 text-sm">Alerts This Month</p> | |
| <h3 class="text-2xl font-bold text-gray-800">23</h3> | |
| <p class="text-yellow-500 text-sm mt-1"><i class="fas fa-caret-down mr-1"></i> 3% from last month</p> | |
| </div> | |
| <div class="bg-yellow-100 p-3 rounded-full"> | |
| <i class="fas fa-bell text-yellow-600 text-xl"></i> | |
| </div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Charts Row --> | |
| <div class="grid grid-cols-1 lg:grid-cols-2 gap-6 mb-6"> | |
| <!-- Inventory Forecast Chart --> | |
| <div class="bg-white rounded-xl shadow-sm p-6"> | |
| <div class="flex items-center justify-between mb-4"> | |
| <h2 class="text-lg font-semibold text-gray-800">6-Month Demand Forecast</h2> | |
| <div class="flex"> | |
| <button class="px-3 py-1 bg-indigo-100 text-indigo-700 rounded-l-lg text-sm">ARIMA</button> | |
| <button class="px-3 py-1 bg-gray-100 text-gray-700 rounded-r-lg text-sm">LSTM</button> | |
| </div> | |
| </div> | |
| <div class="chart-container"> | |
| <canvas id="forecastChart"></canvas> | |
| </div> | |
| </div> | |
| <!-- Stock Status Chart --> | |
| <div class="bg-white rounded-xl shadow-sm p-6"> | |
| <div class="flex items-center justify-between mb-4"> | |
| <h2 class="text-lg font-semibold text-gray-800">Stock Status by Category</h2> | |
| <select class="border rounded-lg px-3 py-1 text-sm focus:outline-none focus:ring-2 focus:ring-indigo-500"> | |
| <option>This Month</option> | |
| <option>Last Month</option> | |
| <option>Last Quarter</option> | |
| </select> | |
| </div> | |
| <div class="chart-container"> | |
| <canvas id="stockChart"></canvas> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Low Stock Items and Recent Alerts --> | |
| <div class="grid grid-cols-1 lg:grid-cols-2 gap-6 mb-6"> | |
| <!-- Low Stock Items --> | |
| <div class="bg-white rounded-xl shadow-sm p-6"> | |
| <div class="flex items-center justify-between mb-4"> | |
| <h2 class="text-lg font-semibold text-gray-800">Critical Stock Items</h2> | |
| <button class="text-indigo-600 text-sm font-medium">View All</button> | |
| </div> | |
| <div class="overflow-x-auto"> | |
| <table class="min-w-full divide-y divide-gray-200"> | |
| <thead> | |
| <tr> | |
| <th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Item</th> | |
| <th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Current</th> | |
| <th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Threshold</th> | |
| <th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Status</th> | |
| </tr> | |
| </thead> | |
| <tbody class="bg-white divide-y divide-gray-200"> | |
| <tr> | |
| <td class="px-4 py-3 whitespace-nowrap"> | |
| <div class="flex items-center"> | |
| <div class="flex-shrink-0 h-10 w-10 bg-indigo-100 rounded-full flex items-center justify-center"> | |
| <i class="fas fa-microchip text-indigo-600"></i> | |
| </div> | |
| <div class="ml-4"> | |
| <div class="text-sm font-medium text-gray-900">IC-555 Timer</div> | |
| <div class="text-sm text-gray-500">Electronics</div> | |
| </div> | |
| </div> | |
| </td> | |
| <td class="px-4 py-3 whitespace-nowrap text-sm text-gray-500">12</td> | |
| <td class="px-4 py-3 whitespace-nowrap text-sm text-gray-500">50</td> | |
| <td class="px-4 py-3 whitespace-nowrap"> | |
| <span class="px-2 inline-flex text-xs leading-5 font-semibold rounded-full bg-red-100 text-red-800">Critical</span> | |
| </td> | |
| </tr> | |
| <tr> | |
| <td class="px-4 py-3 whitespace-nowrap"> | |
| <div class="flex items-center"> | |
| <div class="flex-shrink-0 h-10 w-10 bg-blue-100 rounded-full flex items-center justify-center"> | |
| <i class="fas fa-bolt text-blue-600"></i> | |
| </div> | |
| <div class="ml-4"> | |
| <div class="text-sm font-medium text-gray-900">9V Battery</div> | |
| <div class="text-sm text-gray-500">Power</div> | |
| </div> | |
| </div> | |
| </td> | |
| <td class="px-4 py-3 whitespace-nowrap text-sm text-gray-500">23</td> | |
| <td class="px-4 py-3 whitespace-nowrap text-sm text-gray-500">100</td> | |
| <td class="px-4 py-3 whitespace-nowrap"> | |
| <span class="px-2 inline-flex text-xs leading-5 font-semibold rounded-full bg-yellow-100 text-yellow-800">Low</span> | |
| </td> | |
| </tr> | |
| <tr> | |
| <td class="px-4 py-3 whitespace-nowrap"> | |
| <div class="flex items-center"> | |
| <div class="flex-shrink-0 h-10 w-10 bg-green-100 rounded-full flex items-center justify-center"> | |
| <i class="fas fa-plug text-green-600"></i> | |
| </div> | |
| <div class="ml-4"> | |
| <div class="text-sm font-medium text-gray-900">USB-C Connector</div> | |
| <div class="text-sm text-gray-500">Connectors</div> | |
| </div> | |
| </div> | |
| </td> | |
| <td class="px-4 py-3 whitespace-nowrap text-sm text-gray-500">45</td> | |
| <td class="px-4 py-3 whitespace-nowrap text-sm text-gray-500">150</td> | |
| <td class="px-4 py-3 whitespace-nowrap"> | |
| <span class="px-2 inline-flex text-xs leading-5 font-semibold rounded-full bg-yellow-100 text-yellow-800">Low</span> | |
| </td> | |
| </tr> | |
| </tbody> | |
| </table> | |
| </div> | |
| </div> | |
| <!-- Recent Alerts --> | |
| <div class="bg-white rounded-xl shadow-sm p-6"> | |
| <div class="flex items-center justify-between mb-4"> | |
| <h2 class="text-lg font-semibold text-gray-800">Recent Alerts</h2> | |
| <button class="text-indigo-600 text-sm font-medium">View All</button> | |
| </div> | |
| <div class="space-y-4"> | |
| <div class="flex items-start p-3 hover:bg-gray-50 rounded-lg"> | |
| <div class="flex-shrink-0 mt-1"> | |
| <div class="h-10 w-10 bg-red-100 rounded-full flex items-center justify-center"> | |
| <i class="fas fa-exclamation-circle text-red-600"></i> | |
| </div> | |
| </div> | |
| <div class="ml-3 flex-1"> | |
| <div class="flex items-center justify-between"> | |
| <h3 class="text-sm font-medium text-gray-900">Stock Alert</h3> | |
| <time class="text-xs text-gray-500">2h ago</time> | |
| </div> | |
| <p class="text-sm text-gray-500">IC-555 Timer stock below threshold (12/50)</p> | |
| <div class="mt-2 flex space-x-2"> | |
| <button class="text-xs bg-red-100 text-red-700 px-2 py-1 rounded">Order Now</button> | |
| <button class="text-xs bg-gray-100 text-gray-700 px-2 py-1 rounded">Dismiss</button> | |
| </div> | |
| </div> | |
| </div> | |
| <div class="flex items-start p-3 hover:bg-gray-50 rounded-lg"> | |
| <div class="flex-shrink-0 mt-1"> | |
| <div class="h-10 w-10 bg-yellow-100 rounded-full flex items-center justify-center"> | |
| <i class="fas fa-clock text-yellow-600"></i> | |
| </div> | |
| </div> | |
| <div class="ml-3 flex-1"> | |
| <div class="flex items-center justify-between"> | |
| <h3 class="text-sm font-medium text-gray-900">Forecast Update</h3> | |
| <time class="text-xs text-gray-500">5h ago</time> | |
| </div> | |
| <p class="text-sm text-gray-500">New forecast suggests 20% increase in resistor demand</p> | |
| <div class="mt-2 flex space-x-2"> | |
| <button class="text-xs bg-indigo-100 text-indigo-700 px-2 py-1 rounded">Adjust Order</button> | |
| </div> | |
| </div> | |
| </div> | |
| <div class="flex items-start p-3 hover:bg-gray-50 rounded-lg"> | |
| <div class="flex-shrink-0 mt-1"> | |
| <div class="h-10 w-10 bg-green-100 rounded-full flex items-center justify-center"> | |
| <i class="fas fa-check-circle text-green-600"></i> | |
| </div> | |
| </div> | |
| <div class="ml-3 flex-1"> | |
| <div class="flex items-center justify-between"> | |
| <h3 class="text-sm font-medium text-gray-900">Order Confirmed</h3> | |
| <time class="text-xs text-gray-500">1d ago</time> | |
| </div> | |
| <p class="text-sm text-gray-500">Order #45678 for capacitors has been shipped</p> | |
| <div class="mt-2 flex space-x-2"> | |
| <button class="text-xs bg-gray-100 text-gray-700 px-2 py-1 rounded">Track</button> | |
| </div> | |
| </div> | |
| </div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Quick Actions --> | |
| <div class="bg-white rounded-xl shadow-sm p-6 mb-6"> | |
| <h2 class="text-lg font-semibold text-gray-800 mb-4">Quick Actions</h2> | |
| <div class="grid grid-cols-2 md:grid-cols-4 gap-4"> | |
| <button class="flex flex-col items-center justify-center p-4 border rounded-lg hover:bg-indigo-50 hover:border-indigo-200 transition-all"> | |
| <div class="bg-indigo-100 p-3 rounded-full mb-2"> | |
| <i class="fas fa-file-import text-indigo-600 text-xl"></i> | |
| </div> | |
| <span class="text-sm font-medium">Import Data</span> | |
| </button> | |
| <button class="flex flex-col items-center justify-center p-4 border rounded-lg hover:bg-green-50 hover:border-green-200 transition-all"> | |
| <div class="bg-green-100 p-3 rounded-full mb-2"> | |
| <i class="fas fa-plus-circle text-green-600 text-xl"></i> | |
| </div> | |
| <span class="text-sm font-medium">New Order</span> | |
| </button> | |
| <button class="flex flex-col items-center justify-center p-4 border rounded-lg hover:bg-blue-50 hover:border-blue-200 transition-all"> | |
| <div class="bg-blue-100 p-3 rounded-full mb-2"> | |
| <i class="fas fa-chart-bar text-blue-600 text-xl"></i> | |
| </div> | |
| <span class="text-sm font-medium">Run Forecast</span> | |
| </button> | |
| <button class="flex flex-col items-center justify-center p-4 border rounded-lg hover:bg-purple-50 hover:border-purple-200 transition-all"> | |
| <div class="bg-purple-100 p-3 rounded-full mb-2"> | |
| <i class="fas fa-envelope text-purple-600 text-xl"></i> | |
| </div> | |
| <span class="text-sm font-medium">Send Alerts</span> | |
| </button> | |
| </div> | |
| </div> | |
| </main> | |
| </div> | |
| </div> | |
| <script> | |
| // Toggle sidebar on mobile | |
| document.getElementById('mobileSidebarToggle').addEventListener('click', function() { | |
| document.querySelector('.sidebar').classList.toggle('active'); | |
| }); | |
| document.getElementById('sidebarToggle').addEventListener('click', function() { | |
| document.querySelector('.sidebar').classList.toggle('active'); | |
| }); | |
| // Initialize charts | |
| document.addEventListener('DOMContentLoaded', function() { | |
| // Forecast Chart | |
| const forecastCtx = document.getElementById('forecastChart').getContext('2d'); | |
| const forecastChart = new Chart(forecastCtx, { | |
| type: 'line', | |
| data: { | |
| labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'], | |
| datasets: [ | |
| { | |
| label: 'Actual Sales', | |
| data: [120, 190, 170, 220, 250, 210, 240, 280, 300, 320, 350, 380], | |
| borderColor: '#4F46E5', | |
| backgroundColor: 'rgba(79, 70, 229, 0.1)', | |
| borderWidth: 2, | |
| fill: true, | |
| tension: 0.4 | |
| }, | |
| { | |
| label: 'Forecast (ARIMA)', | |
| data: [null, null, null, null, null, null, null, null, 320, 340, 370, 400], | |
| borderColor: '#10B981', | |
| backgroundColor: 'rgba(16, 185, 129, 0.1)', | |
| borderWidth: 2, | |
| borderDash: [5, 5], | |
| fill: false, | |
| tension: 0.4 | |
| } | |
| ] | |
| }, | |
| options: { | |
| responsive: true, | |
| maintainAspectRatio: false, | |
| plugins: { | |
| legend: { | |
| position: 'top', | |
| }, | |
| tooltip: { | |
| mode: 'index', | |
| intersect: false, | |
| } | |
| }, | |
| scales: { | |
| y: { | |
| beginAtZero: true, | |
| grid: { | |
| drawBorder: false | |
| } | |
| }, | |
| x: { | |
| grid: { | |
| display: false | |
| } | |
| } | |
| } | |
| } | |
| }); | |
| // Stock Chart | |
| const stockCtx = document.getElementById('stockChart').getContext('2d'); | |
| const stockChart = new Chart(stockCtx, { | |
| type: 'bar', | |
| data: { | |
| labels: ['Electronics', 'Power', 'Connectors', 'Mechanical', 'Tools'], | |
| datasets: [ | |
| { | |
| label: 'Current Stock', | |
| data: [120, 190, 170, 220, 250], | |
| backgroundColor: '#4F46E5', | |
| borderRadius: 4 | |
| }, | |
| { | |
| label: 'Threshold', | |
| data: [150, 200, 180, 240, 270], | |
| backgroundColor: '#E5E7EB', | |
| borderRadius: 4 | |
| } | |
| ] | |
| }, | |
| options: { | |
| responsive: true, | |
| maintainAspectRatio: false, | |
| plugins: { | |
| legend: { | |
| position: 'top', | |
| }, | |
| tooltip: { | |
| mode: 'index', | |
| intersect: false, | |
| } | |
| }, | |
| scales: { | |
| y: { | |
| beginAtZero: true, | |
| grid: { | |
| drawBorder: false | |
| } | |
| }, | |
| x: { | |
| grid: { | |
| display: false | |
| } | |
| } | |
| } | |
| } | |
| }); | |
| }); | |
| </script> | |
| <!-- Database Schema and Python Scripts (hidden by default) --> | |
| <div style="display: none;"> | |
| <!-- Database Schema --> | |
| <pre> | |
| /* | |
| Inventory Management Database Schema | |
| */ | |
| CREATE TABLE parts ( | |
| part_id INT PRIMARY KEY AUTO_INCREMENT, | |
| sku VARCHAR(50) UNIQUE NOT NULL, | |
| name VARCHAR(100) NOT NULL, | |
| description TEXT, | |
| category_id INT NOT NULL, | |
| supplier_id INT NOT NULL, | |
| unit_cost DECIMAL(10,2) NOT NULL, | |
| unit_price DECIMAL(10,2) NOT NULL, | |
| min_stock_level INT NOT NULL, | |
| max_stock_level INT NOT NULL, | |
| lead_time_days INT NOT NULL, | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | |
| FOREIGN KEY (category_id) REFERENCES categories(category_id), | |
| FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id) | |
| ); | |
| CREATE TABLE inventory ( | |
| inventory_id INT PRIMARY KEY AUTO_INCREMENT, | |
| part_id INT NOT NULL, | |
| quantity_on_hand INT NOT NULL, | |
| quantity_allocated INT DEFAULT 0, | |
| last_counted_date DATE, | |
| location VARCHAR(50), | |
| FOREIGN KEY (part_id) REFERENCES parts(part_id) | |
| ); | |
| CREATE TABLE sales_history ( | |
| sale_id INT PRIMARY KEY AUTO_INCREMENT, | |
| part_id INT NOT NULL, | |
| sale_date DATE NOT NULL, | |
| quantity INT NOT NULL, | |
| unit_price DECIMAL(10,2) NOT NULL, | |
| customer_id INT, | |
| FOREIGN KEY (part_id) REFERENCES parts(part_id), | |
| FOREIGN KEY (customer_id) REFERENCES customers(customer_id) | |
| ); | |
| CREATE TABLE categories ( | |
| category_id INT PRIMARY KEY AUTO_INCREMENT, | |
| name VARCHAR(50) NOT NULL, | |
| description TEXT | |
| ); | |
| CREATE TABLE suppliers ( | |
| supplier_id INT PRIMARY KEY AUTO_INCREMENT, | |
| name VARCHAR(100) NOT NULL, | |
| contact_person VARCHAR(100), | |
| email VARCHAR(100), | |
| phone VARCHAR(20), | |
| lead_time_days INT | |
| ); | |
| CREATE TABLE customers ( | |
| customer_id INT PRIMARY KEY AUTO_INCREMENT, | |
| name VARCHAR(100) NOT NULL, | |
| email VARCHAR(100), | |
| phone VARCHAR(20) | |
| ); | |
| CREATE TABLE forecast_results ( | |
| forecast_id INT PRIMARY KEY AUTO_INCREMENT, | |
| part_id INT NOT NULL, | |
| forecast_date DATE NOT NULL, | |
| forecast_model VARCHAR(20) NOT NULL, -- 'ARIMA' or 'LSTM' | |
| forecast_period INT NOT NULL, -- in months | |
| forecast_quantity DECIMAL(10,2) NOT NULL, | |
| confidence_interval_lower DECIMAL(10,2), | |
| confidence_interval_upper DECIMAL(10,2), | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| FOREIGN KEY (part_id) REFERENCES parts(part_id) | |
| ); | |
| CREATE TABLE alerts ( | |
| alert_id INT PRIMARY KEY AUTO_INCREMENT, | |
| part_id INT NOT NULL, | |
| alert_type VARCHAR(20) NOT NULL, -- 'low_stock', 'forecast', etc. | |
| alert_message TEXT NOT NULL, | |
| alert_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| resolved BOOLEAN DEFAULT FALSE, | |
| resolved_date TIMESTAMP NULL, | |
| FOREIGN KEY (part_id) REFERENCES parts(part_id) | |
| ); | |
| CREATE TABLE alert_recipients ( | |
| recipient_id INT PRIMARY KEY AUTO_INCREMENT, | |
| name VARCHAR(100) NOT NULL, | |
| email VARCHAR(100) NOT NULL, | |
| phone VARCHAR(20), | |
| receive_email BOOLEAN DEFAULT TRUE, | |
| receive_sms BOOLEAN DEFAULT FALSE, | |
| alert_types VARCHAR(100) -- comma-separated list of alert types to receive | |
| ); | |
| </pre> | |
| <!-- Python Forecasting Script --> | |
| <pre> | |
| """ | |
| Inventory Forecasting Script using ARIMA and LSTM | |
| """ | |
| import pandas as pd | |
| import numpy as np | |
| from statsmodels.tsa.arima.model import ARIMA | |
| from tensorflow.keras.models import Sequential | |
| from tensorflow.keras.layers import LSTM, Dense | |
| from sklearn.preprocessing import MinMaxScaler | |
| from sklearn.metrics import mean_absolute_error | |
| import sqlalchemy | |
| import smtplib | |
| from email.mime.text import MIMEText | |
| import warnings | |
| warnings.filterwarnings('ignore') | |
| # Database connection | |
| def get_db_connection(): | |
| engine = sqlalchemy.create_engine('mysql+pymysql://user:password@localhost/inventory_db') | |
| return engine.connect() | |
| # Fetch sales data for a specific part | |
| def fetch_sales_data(part_id, months=24): | |
| conn = get_db_connection() | |
| query = f""" | |
| SELECT sale_date, quantity | |
| FROM sales_history | |
| WHERE part_id = {part_id} | |
| AND sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL {months} MONTH) | |
| ORDER BY sale_date | |
| """ | |
| df = pd.read_sql(query, conn) | |
| conn.close() | |
| # Resample to monthly data if needed | |
| df['sale_date'] = pd.to_datetime(df['sale_date']) | |
| df.set_index('sale_date', inplace=True) | |
| monthly_df = df.resample('M').sum() | |
| return monthly_df | |
| # ARIMA Forecasting | |
| def arima_forecast(data, periods=6): | |
| # Fit ARIMA model | |
| model = ARIMA(data, order=(1,1,1)) | |
| model_fit = model.fit() | |
| # Make forecast | |
| forecast = model_fit.forecast(steps=periods) | |
| return forecast | |
| # LSTM Forecasting | |
| def lstm_forecast(data, periods=6, look_back=6): | |
| # Normalize data | |
| scaler = MinMaxScaler(feature_range=(0, 1)) | |
| scaled_data = scaler.fit_transform(data.values.reshape(-1,1)) | |
| # Prepare data for LSTM | |
| def create_dataset(dataset, look_back=1): | |
| X, Y = [], [] | |
| for i in range(len(dataset)-look_back-1): | |
| a = dataset[i:(i+look_back), 0] | |
| X.append(a) | |
| Y.append(dataset[i + look_back, 0]) | |
| return np.array(X), np.array(Y) | |
| X, Y = create_dataset(scaled_data, look_back) | |
| X = np.reshape(X, (X.shape[0], X.shape[1], 1)) | |
| # Build LSTM model | |
| model = Sequential() | |
| model.add(LSTM(50, return_sequences=True, input_shape=(look_back, 1))) | |
| model.add(LSTM(50)) | |
| model.add(Dense(1)) | |
| model.compile(loss='mean_squared_error', optimizer='adam') | |
| model.fit(X, Y, epochs=20, batch_size=1, verbose=0) | |
| # Make forecast | |
| forecast_input = scaled_data[-look_back:].reshape(1, look_back, 1) | |
| forecast = [] | |
| for _ in range(periods): | |
| next_step = model.predict(forecast_input) | |
| forecast.append(next_step[0,0]) | |
| forecast_input = np.append(forecast_input[:,1:,:], [[next_step]], axis=1) | |
| forecast = scaler.inverse_transform(np.array(forecast).reshape(-1,1)) | |
| return forecast.flatten() | |
| # Save forecast to database | |
| def save_forecast(part_id, forecast, model_name, forecast_date): | |
| conn = get_db_connection() | |
| for i, value in enumerate(forecast): | |
| query = f""" | |
| INSERT INTO forecast_results | |
| (part_id, forecast_date, forecast_model, forecast_period, forecast_quantity) | |
| VALUES ({part_id}, DATE_ADD('{forecast_date}', INTERVAL {i+1} MONTH), | |
| '{model_name}', {i+1}, {value}) | |
| """ | |
| conn.execute(query) | |
| conn.close() | |
| # Check stock levels and send alerts | |
| def check_stock_and_alert(part_id, forecast): | |
| conn = get_db_connection() | |
| # Get current inventory and threshold | |
| query = f""" | |
| SELECT p.part_id, p.name, p.min_stock_level, i.quantity_on_hand | |
| FROM parts p | |
| JOIN inventory i ON p.part_id = i.part_id | |
| WHERE p.part_id = {part_id} | |
| """ | |
| inventory = pd.read_sql(query, conn).iloc[0] | |
| # Get forecast for next month | |
| monthly_forecast = forecast[0] | |
| # Check if projected stock will be below threshold | |
| projected_stock = inventory['quantity_on_hand'] - monthly_forecast | |
| if projected_stock < inventory['min_stock_level']: | |
| # Create alert | |
| alert_msg = f"Projected stock for {inventory['name']} will be below threshold ({projected_stock}/{inventory['min_stock_level']})" | |
| query = f""" | |
| INSERT INTO alerts (part_id, alert_type, alert_message) | |
| VALUES ({part_id}, 'low_stock', '{alert_msg}') | |
| """ | |
| conn.execute(query) | |
| # Send email/SMS to recipients | |
| send_alerts(part_id, alert_msg) | |
| conn.close() | |
| # Send email/SMS alerts | |
| def send_alerts(part_id, message): | |
| conn = get_db_connection() | |
| # Get recipients who should receive this type of alert | |
| query = f""" | |
| SELECT * FROM alert_recipients | |
| WHERE alert_types LIKE '%low_stock%' | |
| """ | |
| recipients = pd.read_sql(query, conn) | |
| # Send alerts | |
| for _, recipient in recipients.iterrows(): | |
| if recipient['receive_email']: | |
| send_email(recipient['email'], "Inventory Alert", message) | |
| if recipient['receive_sms']: | |
| send_sms(recipient['phone'], message) | |
| conn.close() | |
| def send_email(to_email, subject, body): | |
| # Configure your email settings | |
| smtp_server = "smtp.example.com" | |
| smtp_port = 587 | |
| smtp_user = "[email protected]" | |
| smtp_password = "password" | |
| msg = MIMEText(body) | |
| msg['Subject'] = subject | |
| msg['From'] = smtp_user | |
| msg['To'] = to_email | |
| try: | |
| server = smtplib.SMTP(smtp_server, smtp_port) | |
| server.starttls() | |
| server.login(smtp_user, smtp_password) | |
| server.sendmail(smtp_user, [to_email], msg.as_string()) | |
| server.quit() | |
| except Exception as e: | |
| print(f"Failed to send email: {e}") | |
| def send_sms(phone_number, message): | |
| # Implement your SMS gateway integration here | |
| pass | |
| # Main forecasting workflow | |
| def run_forecast_for_part(part_id): | |
| # Get historical sales data | |
| sales_data = fetch_sales_data(part_id) | |
| if len(sales_data) < 6: | |
| print(f"Not enough data for part {part_id}") | |
| return | |
| # Run ARIMA forecast | |
| arima_result = arima_forecast(sales_data['quantity']) | |
| # Run LSTM forecast | |
| lstm_result = lstm_forecast(sales_data['quantity']) | |
| # Save forecasts | |
| today = pd.to_datetime('today').strftime('%Y-%m-%d') | |
| save_forecast(part_id, arima_result, 'ARIMA', today) | |
| save_forecast(part_id, lstm_result, 'LSTM', today) | |
| # Check stock levels and send alerts | |
| check_stock_and_alert(part_id, arima_result) | |
| # Example usage | |
| if __name__ == "__main__": | |
| # Get list of parts that need forecasting (e.g., all active parts) | |
| conn = get_db_connection() | |
| parts = pd.read_sql("SELECT part_id FROM parts WHERE active = TRUE", conn) | |
| conn.close() | |
| # Run forecast for each part | |
| for part_id in parts['part_id']: | |
| try: | |
| run_forecast_for_part(part_id) | |
| print(f"Forecast completed for part {part_id}") | |
| except Exception as e: | |
| print(f"Error forecasting for part {part_id}: {e}") | |
| </pre> | |
| </div> | |
| <p style="border-radius: 8px; text-align: center; font-size: 12px; color: #fff; margin-top: 16px;position: fixed; left: 8px; bottom: 8px; z-index: 10; background: rgba(0, 0, 0, 0.8); padding: 4px 8px;">Made with <img src="https://enzostvs-deepsite.hf.space/logo.svg" alt="DeepSite Logo" style="width: 16px; height: 16px; vertical-align: middle;display:inline-block;margin-right:3px;filter:brightness(0) invert(1);"><a href="https://enzostvs-deepsite.hf.space" style="color: #fff;text-decoration: underline;" target="_blank" >DeepSite</a> - 🧬 <a href="https://enzostvs-deepsite.hf.space?remix=muflhi001/stockforecasting" style="color: #fff;text-decoration: underline;" target="_blank" >Remix</a></p></body> | |
| </html> |